sql - npm Package Compare versions

Comparing version 0.70.1 to 0.71.0

lib/dialect/mssql.js

@@ -189,3 +189,3 @@ // TODO: visitCreate needs to support schemas
   if (isCountStarExpression(columnNode)) return _countStar();
-  if (inSelectClause && !table.alias && columnNode.asArray) return _arrayAgg();
+  if (inSelectClause && table && !table.alias && columnNode.asArray) return _arrayAgg();
   return Mssql.super_.prototype.visitColumn.call(this, columnNode);
@@ -192,0 +192,0 @@

lib/dialect/oracle.js

@@ -171,3 +171,3 @@ 'use strict';
   if (isCountStarExpression(columnNode)) return _countStar();
-  if (inSelectClause && !table.alias && columnNode.asArray) return _arrayAgg();
+  if (inSelectClause && table && !table.alias && columnNode.asArray) return _arrayAgg();
   return Oracle.super_.prototype.visitColumn.call(this, columnNode);
@@ -174,0 +174,0 @@ };

lib/dialect/postgres.js

@@ -741,3 +741,3 @@ 'use strict';
   var closeParen = 0;
-  if(inSelectClause && (!table.alias || !!columnNode.alias)) {
+  if(inSelectClause && (table && !table.alias || !!columnNode.alias)) {
     if (columnNode.asArray) {
@@ -759,12 +759,14 @@ closeParen++;
   if(!inInsertUpdateClause && !this.visitingReturning && !this._visitingCreate && !this._visitingAlter && !columnNode.subfieldContainer) {
-    if(typeof table.alias === 'string') {
-      txt.push(this.quote(table.alias));
-    } else {
-      if(table.getSchema()) {
-        txt.push(this.quote(table.getSchema()));
-        txt.push('.');
+    if (table) {
+      if (typeof table.alias === 'string') {
+        txt.push(this.quote(table.alias));
+      } else {
+        if (table.getSchema()) {
+          txt.push(this.quote(table.getSchema()));
+          txt.push('.');
+        }
+        txt.push(this.quote(table.getName()));
       }
-      txt.push(this.quote(table.getName()));
+      txt.push('.');
     }
-    txt.push('.');
   }
@@ -790,2 +792,10 @@ if (columnNode.star) {
   }
+  else if (columnNode.isConstant) {
+    // this injects directly into SELECT statement rather than creating a parameter
+    //   txt.push(this._getParameterValue(columnNode.literalValue))
+    // currently thinking it is better to generate a parameter
+    var value = columnNode.constantValue;
+    this.params.push(value);
+    txt.push(this._getParameterText(this.params.length, value));
+  }
   else {
@@ -792,0 +802,0 @@ if (columnNode.subfieldContainer) {

lib/index.js

 'use strict';
 
-var _            = require('lodash');
-var FunctionCall = require('./node/functionCall');
-var ArrayCall    = require('./node/arrayCall');
-var functions    = require('./functions');
-var getDialect   = require('./dialect');
-var Query        = require('./node/query');
-var sliced       = require('sliced');
-var Table        = require('./table');
+var _            	= require('lodash');
+var Column 				= require("./column");
+var FunctionCall 	= require('./node/functionCall');
+var ArrayCall    	= require('./node/arrayCall');
+var functions    	= require('./functions');
+var getDialect    = require('./dialect');
+var ParameterNode	= require('./node/parameter');
+var Query        	= require('./node/query');
+var sliced       	= require('sliced');
+var Table        	= require('./table');
 
@@ -61,2 +63,15 @@ // default dialect is postgres
 
+// Create a constant Column (for use in SELECT)
+Sql.prototype.constant = function(value) {
+  var config={
+    name:"constant",
+    property:"constant",
+    isConstant:true,
+    constantValue:value,
+  };
+  var cn = new Column(config);
+  return cn;
+};
+
+
 // back compat shim for the Sql class constructor
@@ -63,0 +78,0 @@ var create = function(dialect, config) {

lib/node/column.js

@@ -13,2 +13,4 @@ 'use strict';
     this.star = config.star;
+    this.isConstant = config.isConstant;
+    this.constantValue = config.constantValue;
     this.asArray = config.asArray;
@@ -15,0 +17,0 @@ this.aggregator = config.aggregator;

lib/node/query.js

 'use strict';
 
 var _ = require('lodash');
+var alias = require(__dirname + '/alias');
 var assert = require('assert');
@@ -495,2 +496,8 @@ var sliced = require('sliced');
 
+// Extend the query with the aliasMixin so that it's possible to write queries like
+//   var query=sql.select(a.select(a.count()).as("column1"))
+// which generates:
+//   SELECT (SELECT COUNT(*) FROM a) AS "column1"
+_.extend(Query.prototype, alias.AliasMixin);
+
 module.exports = Query;

package.json

@@ -5,3 +5,3 @@ {
   "description": "sql builder",
-  "version": "0.70.1",
+  "version": "0.71.0",
   "homepage": "https://github.com/brianc/node-sql",
@@ -8,0 +8,0 @@ "license": "MIT",

test/dialects/select-tests.js

@@ -5,2 +5,3 @@ 'use strict';
 var post = Harness.definePostTable();
+var user = Harness.defineUserTable();
 var customerAlias = Harness.defineCustomerAliasTable();
@@ -185,1 +186,278 @@ var Sql = require('../../lib');
 });
+
+Harness.test({
+  query: post.select(post.id.as('col1')),
+  pg: {
+    text  : 'SELECT "post"."id" AS "col1" FROM "post"',
+    string: 'SELECT "post"."id" AS "col1" FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT "post"."id" AS "col1" FROM "post"',
+    string: 'SELECT "post"."id" AS "col1" FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT `post`.`id` AS `col1` FROM `post`',
+    string: 'SELECT `post`.`id` AS `col1` FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT [post].[id] AS [col1] FROM [post]',
+    string: 'SELECT [post].[id] AS [col1] FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT "post"."id" "col1" FROM "post"',
+    string: 'SELECT "post"."id" "col1" FROM "post"'
+  },
+  params: []
+});
+
+Harness.test({
+  query: post.select(Sql.constant(4)),
+  pg: {
+    text  : 'SELECT $1 FROM "post"',
+    string: 'SELECT 4 FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT $1 FROM "post"',
+    string: 'SELECT 4 FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT ? FROM `post`',
+    string: 'SELECT 4 FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT @1 FROM [post]',
+    string: 'SELECT 4 FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT :1 FROM "post"',
+    string: 'SELECT 4 FROM "post"'
+  },
+  params: [4]
+});
+
+Harness.test({
+  query: post.select(post.id,Sql.constant(4)),
+  pg: {
+    text  : 'SELECT "post"."id", $1 FROM "post"',
+    string: 'SELECT "post"."id", 4 FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT "post"."id", $1 FROM "post"',
+    string: 'SELECT "post"."id", 4 FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT `post`.`id`, ? FROM `post`',
+    string: 'SELECT `post`.`id`, 4 FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT [post].[id], @1 FROM [post]',
+    string: 'SELECT [post].[id], 4 FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT "post"."id", :1 FROM "post"',
+    string: 'SELECT "post"."id", 4 FROM "post"'
+  },
+  params: [4]
+});
+
+Harness.test({
+  query: post.select(Sql.constant(4).as('col1')),
+  pg: {
+    text  : 'SELECT $1 AS "col1" FROM "post"',
+    string: 'SELECT 4 AS "col1" FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT $1 AS "col1" FROM "post"',
+    string: 'SELECT 4 AS "col1" FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT ? AS `col1` FROM `post`',
+    string: 'SELECT 4 AS `col1` FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT @1 AS [col1] FROM [post]',
+    string: 'SELECT 4 AS [col1] FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT :1 "col1" FROM "post"',
+    string: 'SELECT 4 "col1" FROM "post"'
+  },
+  params: [4]
+});
+
+Harness.test({
+  query: post.select(Sql.constant(4).plus(5)),
+  pg: {
+    text  : 'SELECT ($1 + $2) FROM "post"',
+    string: 'SELECT (4 + 5) FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT ($1 + $2) FROM "post"',
+    string: 'SELECT (4 + 5) FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT (? + ?) FROM `post`',
+    string: 'SELECT (4 + 5) FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT (@1 + @2) FROM [post]',
+    string: 'SELECT (4 + 5) FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT (:1 + :2) FROM "post"',
+    string: 'SELECT (4 + 5) FROM "post"'
+  },
+  params: [4,5]
+});
+
+Harness.test({
+  query: post.select(Sql.constant(4).plus(5).as('col1')),
+  pg: {
+    text  : 'SELECT ($1 + $2) AS "col1" FROM "post"',
+    string: 'SELECT (4 + 5) AS "col1" FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT ($1 + $2) AS "col1" FROM "post"',
+    string: 'SELECT (4 + 5) AS "col1" FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT (? + ?) AS `col1` FROM `post`',
+    string: 'SELECT (4 + 5) AS `col1` FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT (@1 + @2) AS [col1] FROM [post]',
+    string: 'SELECT (4 + 5) AS [col1] FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT (:1 + :2) "col1" FROM "post"',
+    string: 'SELECT (4 + 5) "col1" FROM "post"'
+  },
+  params: [4,5]
+});
+
+Harness.test({
+  query: post.select(Sql.constant(4),Sql.constant("abc"),Sql.constant(true)),
+  pg: {
+    text  : 'SELECT $1, $2, $3 FROM "post"',
+    string: 'SELECT 4, \'abc\', TRUE FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT $1, $2, $3 FROM "post"',
+    string: 'SELECT 4, \'abc\', 1 FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT ?, ?, ? FROM `post`',
+    string: 'SELECT 4, \'abc\', TRUE FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT @1, @2, @3 FROM [post]',
+    string: 'SELECT 4, \'abc\', TRUE FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT :1, :2, :3 FROM "post"',
+    string: 'SELECT 4, \'abc\', TRUE FROM "post"'
+  },
+  params: [4,'abc',true]
+});
+
+Harness.test({
+  query: post.select(Sql.constant(1).sum()),
+  pg: {
+    text  : 'SELECT SUM($1) AS "constant_sum" FROM "post"',
+    string: 'SELECT SUM(1) AS "constant_sum" FROM "post"'
+  },
+  sqlite: {
+    text  : 'SELECT SUM($1) AS "constant_sum" FROM "post"',
+    string: 'SELECT SUM(1) AS "constant_sum" FROM "post"'
+  },
+  mysql: {
+    text  : 'SELECT SUM(?) AS `constant_sum` FROM `post`',
+    string: 'SELECT SUM(1) AS `constant_sum` FROM `post`'
+  },
+  mssql: {
+    text  : 'SELECT SUM(@1) AS [constant_sum] FROM [post]',
+    string: 'SELECT SUM(1) AS [constant_sum] FROM [post]'
+  },
+  oracle: {
+    text  : 'SELECT SUM(:1) "constant_sum" FROM "post"',
+    string: 'SELECT SUM(1) "constant_sum" FROM "post"'
+  },
+  params: [1]
+});
+
+Harness.test({
+  query: Sql.select(post.select(post.id).as("column1")),
+  pg: {
+    text  : 'SELECT (SELECT "post"."id" FROM "post") AS "column1"',
+    string: 'SELECT (SELECT "post"."id" FROM "post") AS "column1"'
+  },
+  sqlite: {
+    text  : 'SELECT (SELECT "post"."id" FROM "post") AS "column1"',
+    string: 'SELECT (SELECT "post"."id" FROM "post") AS "column1"'
+  },
+  mysql: {
+    text  : 'SELECT (SELECT `post`.`id` FROM `post`) AS `column1`',
+    string: 'SELECT (SELECT `post`.`id` FROM `post`) AS `column1`'
+  },
+  mssql: {
+    text  : 'SELECT (SELECT [post].[id] FROM [post]) AS [column1]',
+    string: 'SELECT (SELECT [post].[id] FROM [post]) AS [column1]'
+  },
+  oracle: {
+    text  : 'SELECT (SELECT "post"."id" FROM "post") "column1"',
+    string: 'SELECT (SELECT "post"."id" FROM "post") "column1"'
+  },
+  params: []
+});
+
+Harness.test({
+  query: Sql.select(post.select(post.count()).as("column1")),
+  pg: {
+    text  : 'SELECT (SELECT COUNT("post".*) AS "post_count" FROM "post") AS "column1"',
+    string: 'SELECT (SELECT COUNT("post".*) AS "post_count" FROM "post") AS "column1"'
+  },
+  sqlite: {
+    text  : 'SELECT (SELECT COUNT("post".*) AS "post_count" FROM "post") AS "column1"',
+    string: 'SELECT (SELECT COUNT("post".*) AS "post_count" FROM "post") AS "column1"'
+  },
+  mysql: {
+    text  : 'SELECT (SELECT COUNT(*) AS `post_count` FROM `post`) AS `column1`',
+    string: 'SELECT (SELECT COUNT(*) AS `post_count` FROM `post`) AS `column1`'
+  },
+  mssql: {
+    text  : 'SELECT (SELECT COUNT(*) AS [post_count] FROM [post]) AS [column1]',
+    string: 'SELECT (SELECT COUNT(*) AS [post_count] FROM [post]) AS [column1]'
+  },
+  oracle: {
+    text  : 'SELECT (SELECT COUNT(*) "post_count" FROM "post") "column1"',
+    string: 'SELECT (SELECT COUNT(*) "post_count" FROM "post") "column1"'
+  },
+  params: []
+});
+
+Harness.test({
+  query: Sql.select(post.select(post.id).as("column1"),user.select(user.id).as("column2")),
+  pg: {
+    text  : 'SELECT (SELECT "post"."id" FROM "post") AS "column1", (SELECT "user"."id" FROM "user") AS "column2"',
+    string: 'SELECT (SELECT "post"."id" FROM "post") AS "column1", (SELECT "user"."id" FROM "user") AS "column2"'
+  },
+  sqlite: {
+    text  : 'SELECT (SELECT "post"."id" FROM "post") AS "column1", (SELECT "user"."id" FROM "user") AS "column2"',
+    string: 'SELECT (SELECT "post"."id" FROM "post") AS "column1", (SELECT "user"."id" FROM "user") AS "column2"'
+  },
+  mysql: {
+    text  : 'SELECT (SELECT `post`.`id` FROM `post`) AS `column1`, (SELECT `user`.`id` FROM `user`) AS `column2`',
+    string: 'SELECT (SELECT `post`.`id` FROM `post`) AS `column1`, (SELECT `user`.`id` FROM `user`) AS `column2`'
+  },
+  mssql: {
+    text  : 'SELECT (SELECT [post].[id] FROM [post]) AS [column1], (SELECT [user].[id] FROM [user]) AS [column2]',
+    string: 'SELECT (SELECT [post].[id] FROM [post]) AS [column1], (SELECT [user].[id] FROM [user]) AS [column2]'
+  },
+  oracle: {
+    text  : 'SELECT (SELECT "post"."id" FROM "post") "column1", (SELECT "user"."id" FROM "user") "column2"',
+    string: 'SELECT (SELECT "post"."id" FROM "post") "column1", (SELECT "user"."id" FROM "user") "column2"'
+  },
+  params: []
+});
+
+

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

507411

increased by1.93%

Lines of code

13038

increased by2.32%

Worsened metrics

Number of low supply chain risk alerts

118

increased by0.85%

No dependency changes