sql - npm Package Compare versions

Comparing version 0.1.5 to 0.2.0

lib/column.js

@@ -16,13 +16,22 @@ var ColumnNode = require(__dirname + '/node/column');
   });
-}
+  this.dataType = config.dataType;
+};
 
 var binaryMethod = function(name, operator) {
   Column.prototype[name] = function(val) {
-    return new BinaryNode({
+    var node = new BinaryNode({
       left: this.toNode(),
-      operator: operator,
-      right: val.toNode ? val.toNode() : new ParameterNode(val)
-    })
-  }
-}
+      operator: operator
+    });
+    if (Array.isArray(val)) {
+      node.right = val.map(function (v) {
+        return v.toNode ? v.toNode() : new ParameterNode(v);
+      });
+    }
+    else {
+      node.right = val.toNode ? val.toNode() : new ParameterNode(val);
+    }
+    return node;
+  };
+};
 
@@ -84,3 +93,5 @@ var unaryMethod = function(name, operator) {
 binaryMethod('like', 'LIKE');
+binaryMethod('in', 'IN');
+binaryMethod('notIn', 'NOT IN');
 
 module.exports = Column;

lib/dialect/postgres.js

@@ -0,1 +1,2 @@
+var util = require('util');
 var From = require(__dirname + '/../node/from');
@@ -13,5 +14,7 @@ var Parameter = require(__dirname + '/../node/parameter');
 
+
 Postgres.prototype.visit = function(node) {
   switch(node.type) {
     case 'QUERY': return this.visitQuery(node);
+    case 'SUBQUERY': return this.visitSubquery(node);
     case 'SELECT': return this.visitSelect(node);
@@ -21,2 +24,3 @@ case 'INSERT': return this.visitInsert(node);
     case 'DELETE': return this.visitDelete();
+    case 'CREATE': return this.visitCreate();
     case 'FROM': return this.visitFrom(node);
@@ -34,2 +38,3 @@ case 'WHERE': return this.visitWhere(node);
     case 'PARAMETER': return this.visitParameter(node);
+    case 'DEFAULT': return this.visitDefault(node);
     case 'LIMIT':
@@ -57,10 +62,17 @@ case 'OFFSET':
   this._visitedInsert = true;
-  var paramNodes = insert.nodes.map(function(node) {
-    return self.visit(new Parameter(node.value));
-  }).join(', ');
+
+  var paramNodes = insert.getParameters()
+    .map(function (paramSet) {
+      return paramSet.map(function (param) {
+        return self.visit(param);
+      }).join(', ');
+    }).map(function (param) {
+      return '('+param+')';
+    }).join(', ');
+
   var result = [
     'INSERT INTO',
     this.visit(this._queryNode.table.toNode()),
-    '(' + insert.nodes.map(this.visit.bind(this)).join(', ') + ')',
-    'VALUES', '(' + paramNodes + ')'
+    '(' + insert.columns.map(this.visit.bind(this)).join(', ') + ')',
+    'VALUES', paramNodes
   ];
@@ -77,3 +89,3 @@ return result;
   for(var i = 0, node; node = update.nodes[i]; i++) {
-    params = params.concat(this.visit(node) + ' = ' + this.visit(new Parameter(node.value)));
+    params = params.concat(this.visit(node) + ' = ' + this.visit(node.value));
   }
@@ -95,2 +107,17 @@ var result = [
 
+Postgres.prototype.visitCreate = function() {
+  this._visitingCreate = true;
+  //don't auto-generate from clause
+  this._visitedFrom = true;
+  var table = this._queryNode.table;
+  var col_nodes = table.columns.map(function(col) { return col.toNode(); });
+  var result = [
+    'CREATE TABLE',
+    this.visit(table.toNode()),
+    '(' + col_nodes.map(this.visit.bind(this)).join(', ') + ')'
+  ];
+  this._visitingCreate = false;
+  return result;
+}
+
 Postgres.prototype.visitFrom = function(from) {
@@ -122,3 +149,14 @@ this._visitedFrom = true;
 Postgres.prototype.visitBinary = function(binary) {
-  return '(' + this.visit(binary.left) + ' ' + binary.operator + ' ' + this.visit(binary.right) + ')';
+  var self = this;
+  var result = '(' + this.visit(binary.left) + ' ' + binary.operator + ' ';
+  if (Array.isArray(binary.right)) {
+    result += '(' + binary.right.map(function (node) {
+      return self.visit(node);
+    }).join(', ') + ')';
+  }
+  else {
+    result += this.visit(binary.right);
+  }
+  result += ')';
+  return result;
 }
@@ -146,2 +184,20 @@
 
+Postgres.prototype.visitSubquery = function(queryNode) {
+  var result = [];
+  for(var i = 0; i < queryNode.nodes.length; i ++) {
+    var res = this.visit(queryNode.nodes[i]);
+    result = result.concat(res);
+  }
+  //implicit 'from'
+  if(!this._visitedFrom) {
+    var select = result.slice(0, this._selectOrDeleteEndIndex);
+    var from = this.visitFrom(new From().add(queryNode.table.toNode()));
+    var rest = result.slice(this._selectOrDeleteEndIndex);
+    result = select.concat(from).concat(rest);
+  }
+  result[0] = '('+result[0];
+  result[result.length-1] = result[result.length-1] + ') ' + queryNode.alias;
+  return result;
+}
+
 Postgres.prototype.visitTable = function(tableNode) {
@@ -168,3 +224,3 @@ var table = tableNode.table;
   }
-  if(!this._visitedInsert && !this._visitingUpdate) {
+  if(!this._visitedInsert && !this._visitingUpdate && !this._visitingCreate) {
     if(table.alias) {
@@ -189,2 +245,4 @@ txt = table.alias;
   }
+  if(this._visitingCreate)
+    txt += ' ' + columnNode.dataType;
   return txt;
@@ -198,2 +256,8 @@ }
 
+Postgres.prototype.visitDefault = function(parameter) {
+  var params = this.params;
+  this.params.push('DEFAULT');
+  return "$"+params.length;
+}
+
 Postgres.prototype.visitJoin = function(join) {
@@ -200,0 +264,0 @@ var result = [];

lib/node/column.js

@@ -11,3 +11,4 @@ var Node = require(__dirname);
     this.value = config.getValue();
+    this.dataType = config.dataType;
   }
 });

lib/node/insert.js

 var Node = require(__dirname);
+var ParameterNode = require('./parameter');
+var DefaultNode = require('./default');
 
-module.exports = Node.define({
-  type: 'INSERT'
+var Insert = Node.define({
+  type: 'INSERT',
+  constructor: function () {
+    this.names = [];
+    this.columns = [];
+    this.valueSets = [];
+  }
 });
+
+module.exports = Insert;
+
+Insert.prototype.add = function (nodes) {
+  var self = this;
+  var values = {};
+  nodes.forEach(function (node) {
+    var column = node.toNode();
+    var name = column.name;
+    var idx = self.names.indexOf(name);
+    if (idx < 0) {
+      self.names.push(name);
+      self.columns.push(column);
+    }
+    values[name] = column;
+  });
+  this.valueSets.push(values);
+  return self;
+};
+
+/*
+ * Get paramters for all values to be inserted. This function
+ * handles handles bulk inserts, where keys may be present
+ * in some objects and not others. When keys are not present,
+ * the insert should refer to the column value as DEFAULT.
+ */
+Insert.prototype.getParameters = function () {
+  var self = this;
+  return this.valueSets
+    .map(function (nodeDict) {
+      var set = [];
+      self.names.forEach(function (name) {
+        var node = nodeDict[name];
+        if (node) {
+          set.push(new ParameterNode(node.value));
+        }
+        else {
+          set.push(new DefaultNode());
+        }
+      });
+      return set;
+    });
+};

lib/node/query.js

@@ -11,2 +11,4 @@ var Node = require(__dirname);
 var Returning = require(__dirname + '/returning');
+var Create = require(__dirname + '/create');
+var ParameterNode = require(__dirname + '/parameter');
 
@@ -72,14 +74,26 @@ var Modifier = Node.define({
     var self = this;
+
     var args = Array.prototype.slice.call(arguments, 0);
     //object literal
-    if(arguments.length == 1 && !o["toNode"]) {
+    if(arguments.length == 1 && !o["toNode"] && !o.forEach) {
       args = Object.keys(o).map(function(key) {
         return self.table[key].value(o[key]);
       })
+    } else if (o.forEach) {
+      o.forEach(function (arg) {
+        return self.insert.call(self, arg);
+      });
+      return self;
     }
-    var insert = new Insert();
-    args.forEach(function(arg) {
-      insert.add(arg);
-    });
-    return this.add(insert);
+
+    if (self.insertClause) {
+      self.insertClause.add(args);
+      return self;
+    }
+    else {
+      self.insertClause = new Insert();
+      self.insertClause.add(args);
+      return self.add(self.insertClause);
+    }
+
   },
@@ -90,3 +104,4 @@ update: function(o) {
     Object.keys(o).forEach(function(key) {
-      update.add(self.table[key].value(o[key]));
+      var val = o[key];
+      update.add(self.table[key].value(val.toNode ? val.toNode() : new ParameterNode(val)));
     });
@@ -107,2 +122,5 @@ return this.add(update);
   },
+  create: function() {
+    return this.add(new Create());
+  },
   limit: function(count) {
@@ -109,0 +127,0 @@ return this.add(new Modifier(this, 'LIMIT', count));

lib/table.js

@@ -47,3 +47,4 @@ var Query = require(__dirname + '/node/query');
 Table.prototype.star = function() {
-  return new TextNode('"'+this._name+'".*');
+  var name = this.alias || this._name;
+  return new TextNode('"'+name+'".*');
 }
@@ -58,2 +59,10 @@
 
+Table.prototype.subQuery = function(alias) {
+  //create the query and pass it off
+  var query = new Query(this);
+  query.type = 'SUBQUERY';
+  query.alias = alias;
+  return query;
+}
+
 Table.prototype.insert = function() {
@@ -77,2 +86,8 @@ var query = new Query(this);
 
+Table.prototype.create = function() {
+  var query = new Query(this);
+  query.create.apply(query, arguments);
+  return query;
+}
+
 Table.prototype.toNode = function() {
@@ -79,0 +94,0 @@ return new TableNode(this);

package.json

@@ -5,3 +5,3 @@ {
   "description": "sql builder",
-  "version": "0.1.5",
+  "version": "0.2.0",
   "homepage": "https://github.com/brianc/node-sql",
@@ -8,0 +8,0 @@ "repository": {

README.md

@@ -10,3 +10,3 @@ # node-sql
 
-[![Build Status](https://secure.travis-ci.org/brianc/node-sql.png?branch=master)](http://travis-ci.org/brianc/node-sql)
+[![Build Status](https://secure.travis-ci.org/brianc/node-sql.png)](http://travis-ci.org/brianc/node-sql)
 
@@ -13,0 +13,0 @@ ## examples

test/index.js

@@ -1,1 +0,19 @@
-require('test-dir');
+var fs = require('fs');
+var path = require('path');
+
+var testDir = path.dirname(require.main.filename);
+
+var directories = [
+  testDir,
+  testDir + '/postgres'
+];
+
+directories.forEach(function (d) {
+  fs.readdir(d, function(err, files) {
+    if(err) throw err;
+    for(var i = 0, file; file = files[i]; i++) {
+      var filePath = path.join(d, file);
+      require(filePath);
+    }
+  });
+});

New alerts

Dynamic require

Supply chain risk

Dynamic require can indicate the package is performing dangerous or unsafe dynamic code execution.

Found 1 instance in 1 package

Filesystem access

Supply chain risk

Accesses the file system, and could potentially read sensitive data.

Found 1 instance in 1 package

Fixed alerts

Non-existent author

Supply chain risk

The package was published by an npm account that no longer exists.

Found 1 instance in 1 package

Dynamic require

Supply chain risk

Dynamic require can indicate the package is performing dangerous or unsafe dynamic code execution.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

44409

increased by24.56%

Number of package files

48

increased by45.45%

Lines of code

1306

increased by26.55%

Number of high supply chain risk alerts

0

decreased by-100%

Worsened metrics

Number of low supply chain risk alerts

61

increased by15.09%

No dependency changes