litesql - npm Package Compare versions

Comparing version 0.0.2 to 0.0.3

index.js

@@ -216,5 +216,5 @@ var sqlite3 = require('sqlite3');
 		}
-		if( utils.has(data, 'id') ) {
-			var id = data.id;
-            delete data.id;
+		if( utils.has(data, pk) ) {
+			var id = data[pk];
+            delete data[pk];
 			return self.update(data, id);
@@ -292,2 +292,4 @@ } else {
             if( columnProps.unique)  result.constraints.push("UNIQUE");
+            if( utils.isDef(columnProps.defaultValue))  result.constraints.push("DEFAULT "+columnProps.defaultValue);
+            if( columnProps.check)  result.constraints.push("CHECK ("+columnProps.check + ")");
         }
@@ -375,50 +377,2 @@ return result;
     
-    
-    
-    /*
-    self.runQuery = function run(query, cb) {
-        return self.run(query.sql, query.params, cb);
-    }
-	
-	function fixArgsFwd(cb) {
-		return function(err, res) {
-			cb(err, res);
-		}
-	}
-	
-    self.getQuery = function get(query, cb) {
-        return self.get(query.sql, query.params, fixArgsFwd(cb));
-    }
-    
-    self.relai = function relai(qry) {
-        return function() {
-            var args = utils.toArray(arguments);            
-            self.runQuery(qry, args[args.length-1]);
-        }
-    }
-    
-    self.runQueries = function (queries, cb) {
-        var fns = [];
-        
-        
-        for(var i=0; i<queries.length; i++) {
-            fns.push(self.relai(queries[i]));
-        }
-        utils.waterfall(fns, cb);    
-        return self;
-    }
-    
-    self.runSqls = function(sqls, cb) {
-        var queries = [];
-        for(var i=0; i<sqls.length; i++) {
-            queries.push(new Query(sqls[i]));
-        }
-        self.runQueries(queries, cb);
-        return self;
-    }
-    
-    self.allQuery = function get(query, cb) {
-        return self.all(query.sql, query.params, fixArgsFwd(cb));
-    }
-    */
     var modelsTableSchema = {
@@ -425,0 +379,0 @@ model: 'text'

lib/utils.js

@@ -17,2 +17,6 @@ var self = module.exports;
 
+self.isDef = function(obj) {
+    return obj !== undefined && obj !== null;
+};
+
 self.isObject = function(A) {
@@ -19,0 +23,0 @@ return (typeof A === "object") && (A !== null);

package.json

 {
   "name": "litesql",
-  "version": "0.0.2",
+  "version": "0.0.3",
   "description": "The easy way to deal with sqlite databases in node.js",
@@ -5,0 +5,0 @@ "scripts": {

README.md

@@ -36,2 +36,3 @@ ## What's this ?
             id: 'pk',    
+            num: 'int',
             
@@ -56,7 +57,6 @@ // column definition can be an object too; you can pass it also 'unique: true'
     for(var i = 1; i <= 10; i++) {
-        todos.insert({ task: 'Task #'+i, duedate: new Date(), completed: false }).run();
+        todos.insert({ num: i,  task: 'Task #'+i, duedate: new Date(), completed: false }).run();
     }
     
-    todos.find().all( function(err, tasks){
-        assert.equal(err, null);
+    todos.find().all( function(err, tasks){        
         assert.equal(tasks.length, 10);    
@@ -71,3 +71,72 @@ });
 
-## Documentation
-More to come soon; for now, you may take a look on the tests to see how it's been used
+## CRUD methods
+
+We've already seen insert; following how to update an existing record given its primary key; usually (but not necessary) an 'id' column;
+```javascript
+
+db.serialize(function() {
+    // update by pk (id = 1)
+    todos.update({ task: 'have to finish this' }, 1 /* pk */).run();
+    
+    // and also find by pk (id = 1)
+    todos.find(1).get(function(err, todo) {
+        assert.equal(todo.task, 'have to finish this');
+    });
+});
+```
+You can also update by another condition; here we update all records with num <= 5
+```javascript
+db.serialize(function() {
+    // update all completed todos
+    // set compteted = true on all records with num <= 5
+    todos.update({ completed: true }, { 'num <=': 5 }).run();
+    
+    // we can also call #find with an object hash for conditions
+    todos.find({ completed: true }).all(function(err, completedTasks) {
+        assert.equal(completedTasks.length, 5);        
+    });
+});
+```
+Another way to insert a new record is via the #save method
+```javascript
+db.serialize(function() {
+    // will insert a new record, since there is no pk field
+    todos.save({ task: 'give me more examples', completed: false }).run();
+    todos.find({ task: 'give me more examples' }).all(function(err, tasks) {
+        assert.equal(tasks.length, 1);       
+    });
+});
+```
+
+You can use #save to update an existing record as well. Just include the pk field
+```javascript
+db.serialize(function() {
+    // will update an existing record, since we have specified the pk field
+    todos.save({ id: '1', task: 'first of firsts' }).run();
+    todos.find(1).get(function(err, todo) {
+        assert.equal(todo.task, 'first of firsts');       
+    });
+});
+```
+We use #remove to delete an existing record; below we remove by the pk field
+```javascript
+db.serialize(function() {
+    // remove todo by pk (id=10)
+    todos.remove(10).run();
+    todos.find(10).all(function(err, todos) {
+        assert.equal(todos.length, 0);        
+    });
+});
+```
+As you may have already guessed, you can call #remove with more conditions; below we remove all tasks with completed=true
+```javascript
+db.serialize(function() {
+    // remove all completed tasks    
+    todos.remove({ completed: true }).run();
+    todos.find({ completed: true }).all(function(err, todos) {
+        assert.equal(todos.length, 0);       
+    });
+});
+```
+## Schema helper methods
+TBD

test/recipes.js

@@ -9,36 +9,106 @@ var assert = require("assert")
     var db = litesql.db(":memory:");
+    db.serialize(); // serialize all queries in this suite
     // helper class
     var todos = new litesql.Table('todos', 'id', db);
     
-    it('should create a table and insert data into it', function(done) {
-        db.serialize(function() {
-            db.createTable(
-                'todos',        // table name
-                {               // table defintion
-                    id: 'pk',   // shortcut for id INTEGER PRIMARY KEY AUTOINCREMENT
-                    task: { type: 'text', required: true }, // you can pass it also 'unique: true'
-                    duedate: 'date',
-                    completed: 'boolean'  // types alias are managed internally 
-                }
-            ).run(); 
-            /*
-                you can also write
-                var query = db.createTable(...);
-                query.run( function (err) { ... } );
-            */
-            
-            
-            
-            for(var i = 1; i <= 10; i++) {
-                todos.insert({ task: 'Task #'+i, duedate: new Date(), completed: false }).run();
+    it('should create a table and insert data into it', function(done) {     
+        db.createTable(
+            'todos',        // table name
+            {               // table defintion
+                id: 'pk',   // shortcut for id INTEGER PRIMARY KEY AUTOINCREMENT
+                num: 'int',
+                task: { type: 'text', required: true }, // you can pass it also 'unique: true'
+                duedate: 'date',
+                completed: 'boolean'  // types alias are managed internally 
             }
-            
-            todos.find().all( function(err, tasks){
-                assert.equal(err, null);
-                assert.equal(tasks.length, 10);
-                done();
-            });
-            
-        })
+        ).run(); 
+        /*
+            you can also write
+            var query = db.createTable(...);
+            query.run( function (err) { ... } );
+        */
+        
+        
+        
+        for(var i = 1; i <= 10; i++) {
+            todos.insert({ num: i, task: 'Task #'+i, duedate: new Date(), completed: false }).run();
+        }
+        
+        todos.find().all( function(err, tasks){
+            //assert.equal(err, null);
+            assert.equal(tasks.length, 10);       
+            done();
+        }); 
     });
+    
+    it('should update the first task by id', function(done) {     
+        
+        // update by id (primary key)
+        todos.update({ task: 'have to finish this' }, 1).run();
+        todos.find(1).get(function(err, todo) {
+            assert.equal(todo.task, 'have to finish this');
+            done();
+        });
+    });
+
+    it('should mark first 5 todos as completed', function(done) {   
+     
+        todos.update({ completed: true }, { 'num <=': 5 }).run();
+        todos.find({ completed: true }).all(function(err, completedTasks) {
+            //assert.equal(err, null);
+            //console.log(completedTasks)
+            assert.equal(completedTasks.length, 5);
+            done();
+        });
+        
+    });
+    
+    it('should insert the new todo', function(done) {   
+        // will insert a new record, since there is no pk field
+        todos.save({ task: 'give me more examples', completed: false }).run();
+        todos.find({ task: 'give me more examples' }).all(function(err, tasks) {
+            //assert.equal(err, null);
+            assert.equal(tasks.length, 1);
+            done();
+        });
+        
+    });
+    
+    it('should update todo with id #1', function(done) {   
+        // will update an existing record, since we have specified the pk field
+        todos.save({ id: '1', task: 'first of firsts' }).run();
+        todos.find(1).get(function(err, todo) {
+            //assert.equal(err, null);
+            assert.equal(todo.task, 'first of firsts');
+            done();
+        });
+        
+    });
+    
+    it('should remove id #10 function(done) {   
+     
+        todos.remove(10).run();
+        todos.find(10).all(function(err, todos) {
+            //assert.equal(err, null);
+            assert.equal(todos.length, 0);
+            done();
+        });
+        
+    });
+    
+    it('should remove all completed tasks', function(done) {   
+        
+        todos.find({ completed: true }).all(function(err, completedTasks) {
+            assert.equal(completedTasks.length, 5);            
+        });
+        todos.remove({ completed: true }).run();
+        todos.find({ completed: true }).all(function(err, todos) {
+            //assert.equal(err, null);
+            assert.equal(todos.length, 0);
+            done();
+        });
+        
+    });
+    
+    
 })

test/test.js

@@ -309,6 +309,6 @@ var assert = require("assert")
                 var qry = db.createTable('table', {
-                    name  : { type: 'text', unique: true},
-                    age   : { type: 'int', required: true},
+                    name  : { type: 'text', unique: true, required: true},
+                    age   : { type: 'int', defaultValue: 0, check: 'age >= 0'},
                     salary: 'decimal',
-                    birthDate: 'date',
+                    birthDate: { type: 'date', defaultValue: 'CURRENT_DATE' },
                     single  : 'boolean',
@@ -318,4 +318,4 @@ refId: '#ref',
                 });            
-                var sql = 'CREATE TABLE IF NOT EXISTS table(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, '+
-                            'age INTEGER NOT NULL, salary NUMERIC, birthDate DATETIME, single BOOLEAN, refId INTEGER, refId2 INTEGER, '+
+                var sql = 'CREATE TABLE IF NOT EXISTS table(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, '+
+                            'age INTEGER DEFAULT 0 CHECK (age >= 0), salary NUMERIC, birthDate DATETIME DEFAULT CURRENT_DATE, single BOOLEAN, refId INTEGER, refId2 INTEGER, '+
                             'FOREIGN KEY(refId) REFERENCES ref(id), FOREIGN KEY(refId2) REFERENCES ref2(id))';
@@ -322,0 +322,0 @@ assert.equal(qry.sql, sql);

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

49146

increased by8.62%

Lines of code

1004

increased by2.14%

Number of lines in readme file

140

increased by97.18%

No dependency changes