sql - npm Package Compare versions

Comparing version 0.45.0 to 0.45.1

lib/dialect/mssql.js

@@ -32,3 +32,3 @@ // TODO: visitCreate needs to support schemas
   }
-  if (binary.operator=='IN'){
+  if (binary.operator=='IN' || binary.operator=='NOT IN'){
     return Mssql.super_.prototype.visitBinary.call(this, binary);
@@ -35,0 +35,0 @@ }

package.json

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

README.md

 # node-sql
-_sql string builder for node_ - supports PostgreSQL, mysql, and sqlite dialects.
+_sql string builder for node_ - supports PostgreSQL, mysql, Microsoft SQL Server, and sqlite dialects.
 
@@ -4,0 +4,0 @@ Building SQL statements by hand is no fun, especially in a language which has clumsy support for multi-line strings.

test/dialects/case-tests.js

@@ -11,12 +11,16 @@ 'use strict';
     text  : 'SELECT (CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END) FROM "customer"',
-    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) FROM "customer"',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) FROM "customer"'
   },
   sqlite: {
     text  : 'SELECT (CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END) FROM "customer"',
-    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) FROM "customer"',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT (CASE WHEN ? THEN ? WHEN ? THEN ? ELSE ? END) FROM `customer`',
-    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) FROM `customer`',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT (CASE WHEN @1 THEN @2 WHEN @3 THEN @4 ELSE @5 END) FROM [customer]',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) FROM [customer]'
+  },
   params: [true, 0, false, 1, 2]
@@ -30,12 +34,16 @@ });
     text  : 'SELECT ("customer"."age" + (CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END)) FROM "customer"',
-    string: 'SELECT ("customer"."age" + (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END)) FROM "customer"',
+    string: 'SELECT ("customer"."age" + (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END)) FROM "customer"'
   },
   sqlite: {
     text  : 'SELECT ("customer"."age" + (CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END)) FROM "customer"',
-    string: 'SELECT ("customer"."age" + (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END)) FROM "customer"',
+    string: 'SELECT ("customer"."age" + (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END)) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT (`customer`.`age` + (CASE WHEN ? THEN ? WHEN ? THEN ? ELSE ? END)) FROM `customer`',
-    string: 'SELECT (`customer`.`age` + (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END)) FROM `customer`',
+    string: 'SELECT (`customer`.`age` + (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END)) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT ([customer].[age] + (CASE WHEN @1 THEN @2 WHEN @3 THEN @4 ELSE @5 END)) FROM [customer]',
+    string: 'SELECT ([customer].[age] + (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END)) FROM [customer]'
+  },
   params: [true, 0, false, 1, 2]
@@ -53,8 +61,12 @@ });
     text  : 'SELECT ((CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE $5 END) + $6) FROM "customer"',
-    string: 'SELECT ((CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) + 3) FROM "customer"',
+    string: 'SELECT ((CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) + 3) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT ((CASE WHEN ? THEN ? WHEN ? THEN ? ELSE ? END) + ?) FROM `customer`',
-    string: 'SELECT ((CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) + 3) FROM `customer`',
+    string: 'SELECT ((CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) + 3) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT ((CASE WHEN @1 THEN @2 WHEN @3 THEN @4 ELSE @5 END) + @6) FROM [customer]',
+    string: 'SELECT ((CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE 2 END) + 3) FROM [customer]'
+  },
   params: [true, 0, false, 1, 2, 3]
@@ -72,8 +84,12 @@ });
     text  : 'SELECT (CASE WHEN $1 THEN $2 WHEN $3 THEN $4 ELSE ("customer"."age" BETWEEN $5 AND $6) END) FROM "customer"',
-    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE ("customer"."age" BETWEEN 10 AND 20) END) FROM "customer"',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE ("customer"."age" BETWEEN 10 AND 20) END) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT (CASE WHEN ? THEN ? WHEN ? THEN ? ELSE (`customer`.`age` BETWEEN ? AND ?) END) FROM `customer`',
-    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE (`customer`.`age` BETWEEN 10 AND 20) END) FROM `customer`',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE (`customer`.`age` BETWEEN 10 AND 20) END) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT (CASE WHEN @1 THEN @2 WHEN @3 THEN @4 ELSE ([customer].[age] BETWEEN @5 AND @6) END) FROM [customer]',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 ELSE ([customer].[age] BETWEEN 10 AND 20) END) FROM [customer]'
+  },
   params: [true, 0, false, 1, 10, 20]
@@ -91,8 +107,12 @@ });
     text  : 'SELECT (CASE WHEN $1 THEN $2 WHEN $3 THEN $4 END) FROM "customer"',
-    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 END) FROM "customer"',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 END) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT (CASE WHEN ? THEN ? WHEN ? THEN ? END) FROM `customer`',
-    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 END) FROM `customer`',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 END) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT (CASE WHEN @1 THEN @2 WHEN @3 THEN @4 END) FROM [customer]',
+    string: 'SELECT (CASE WHEN TRUE THEN 0 WHEN FALSE THEN 1 END) FROM [customer]'
+  },
   params: [true, 0, false, 1]
@@ -106,12 +126,16 @@ });
     text  : 'SELECT (CASE WHEN ("customer"."age" IN ($1, $2, $3)) THEN $4 WHEN ("customer"."age" <= $5) THEN $6 ELSE $7 END) FROM "customer"',
-    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 ELSE 2 END) FROM "customer"',
+    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 ELSE 2 END) FROM "customer"'
   },
   sqlite: {
     text  : 'SELECT (CASE WHEN ("customer"."age" IN ($1, $2, $3)) THEN $4 WHEN ("customer"."age" <= $5) THEN $6 ELSE $7 END) FROM "customer"',
-    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 ELSE 2 END) FROM "customer"',
+    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 ELSE 2 END) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT (CASE WHEN (`customer`.`age` IN (?, ?, ?)) THEN ? WHEN (`customer`.`age` <= ?) THEN ? ELSE ? END) FROM `customer`',
-    string: 'SELECT (CASE WHEN (`customer`.`age` IN (10, 20, 30)) THEN 0 WHEN (`customer`.`age` <= 60) THEN 1 ELSE 2 END) FROM `customer`',
+    string: 'SELECT (CASE WHEN (`customer`.`age` IN (10, 20, 30)) THEN 0 WHEN (`customer`.`age` <= 60) THEN 1 ELSE 2 END) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT (CASE WHEN ([customer].[age] IN (@1, @2, @3)) THEN @4 WHEN ([customer].[age] <= @5) THEN @6 ELSE @7 END) FROM [customer]',
+    string: 'SELECT (CASE WHEN ([customer].[age] IN (10, 20, 30)) THEN 0 WHEN ([customer].[age] <= 60) THEN 1 ELSE 2 END) FROM [customer]'
+  },
   params: [10, 20, 30, 0, 60, 1, 2]
@@ -125,12 +149,16 @@ });
     text  : 'SELECT (CASE WHEN ("customer"."age" IN ($1, $2, $3)) THEN $4 WHEN ("customer"."age" <= $5) THEN $6 END) FROM "customer"',
-    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 END) FROM "customer"',
+    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 END) FROM "customer"'
   },
   sqlite: {
     text  : 'SELECT (CASE WHEN ("customer"."age" IN ($1, $2, $3)) THEN $4 WHEN ("customer"."age" <= $5) THEN $6 END) FROM "customer"',
-    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 END) FROM "customer"',
+    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN 0 WHEN ("customer"."age" <= 60) THEN 1 END) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT (CASE WHEN (`customer`.`age` IN (?, ?, ?)) THEN ? WHEN (`customer`.`age` <= ?) THEN ? END) FROM `customer`',
-    string: 'SELECT (CASE WHEN (`customer`.`age` IN (10, 20, 30)) THEN 0 WHEN (`customer`.`age` <= 60) THEN 1 END) FROM `customer`',
+    string: 'SELECT (CASE WHEN (`customer`.`age` IN (10, 20, 30)) THEN 0 WHEN (`customer`.`age` <= 60) THEN 1 END) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT (CASE WHEN ([customer].[age] IN (@1, @2, @3)) THEN @4 WHEN ([customer].[age] <= @5) THEN @6 END) FROM [customer]',
+    string: 'SELECT (CASE WHEN ([customer].[age] IN (10, 20, 30)) THEN 0 WHEN ([customer].[age] <= 60) THEN 1 END) FROM [customer]'
+  },
   params: [10, 20, 30, 0, 60, 1]
@@ -144,13 +172,17 @@ });
     text  : 'SELECT (CASE WHEN ("customer"."age" IN ($1, $2, $3)) THEN ("customer"."age" + $4) WHEN ("customer"."age" <= $5) THEN ("customer"."age" - $6) END) FROM "customer"',
-    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN ("customer"."age" + 5) WHEN ("customer"."age" <= 60) THEN ("customer"."age" - 1) END) FROM "customer"',
+    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN ("customer"."age" + 5) WHEN ("customer"."age" <= 60) THEN ("customer"."age" - 1) END) FROM "customer"'
   },
   sqlite: {
     text  : 'SELECT (CASE WHEN ("customer"."age" IN ($1, $2, $3)) THEN ("customer"."age" + $4) WHEN ("customer"."age" <= $5) THEN ("customer"."age" - $6) END) FROM "customer"',
-    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN ("customer"."age" + 5) WHEN ("customer"."age" <= 60) THEN ("customer"."age" - 1) END) FROM "customer"',
+    string: 'SELECT (CASE WHEN ("customer"."age" IN (10, 20, 30)) THEN ("customer"."age" + 5) WHEN ("customer"."age" <= 60) THEN ("customer"."age" - 1) END) FROM "customer"'
   },
   mysql: {
     text  : 'SELECT (CASE WHEN (`customer`.`age` IN (?, ?, ?)) THEN (`customer`.`age` + ?) WHEN (`customer`.`age` <= ?) THEN (`customer`.`age` - ?) END) FROM `customer`',
-    string: 'SELECT (CASE WHEN (`customer`.`age` IN (10, 20, 30)) THEN (`customer`.`age` + 5) WHEN (`customer`.`age` <= 60) THEN (`customer`.`age` - 1) END) FROM `customer`',
+    string: 'SELECT (CASE WHEN (`customer`.`age` IN (10, 20, 30)) THEN (`customer`.`age` + 5) WHEN (`customer`.`age` <= 60) THEN (`customer`.`age` - 1) END) FROM `customer`'
   },
+  mssql: {
+    text  : 'SELECT (CASE WHEN ([customer].[age] IN (@1, @2, @3)) THEN ([customer].[age] + @4) WHEN ([customer].[age] <= @5) THEN ([customer].[age] - @6) END) FROM [customer]',
+    string: 'SELECT (CASE WHEN ([customer].[age] IN (10, 20, 30)) THEN ([customer].[age] + 5) WHEN ([customer].[age] <= 60) THEN ([customer].[age] - 1) END) FROM [customer]'
+  },
   params: [10, 20, 30, 5, 60, 1]
 });

test/dialects/in-clause-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE (1=0)',
+    string: 'SELECT [post].* FROM [post] WHERE (1=0)'
+  },
   params: []
@@ -38,2 +42,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (@1))',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (1))'
+  },
   params: [1]
@@ -56,2 +64,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NULL)',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NULL)'
+  },
   params: []
@@ -74,2 +86,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (@1, @2))',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (1, 2))'
+  },
   params: [1, 2]
@@ -92,2 +108,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NULL)',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NULL)'
+  },
   params: []
@@ -110,2 +130,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (@1, @2) OR [post].[id] IS NULL)',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (1, 2) OR [post].[id] IS NULL)'
+  },
   params: [1, 2]
@@ -128,3 +152,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (@1, @2) OR [post].[id] IS NULL)',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IN (1, 2) OR [post].[id] IS NULL)'
+  },
   params: [1, 2]
 });

test/dialects/not-in-clause-tests.js

@@ -20,2 +20,6 @@ 'use strict';
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE (1=1)',
+    string: 'SELECT [post].* FROM [post] WHERE (1=1)'
+  },
   params: []
@@ -38,2 +42,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] NOT IN (@1))',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] NOT IN (1))'
+  },
   params: [1]
@@ -56,2 +64,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NOT NULL)',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NOT NULL)'
+  },
   params: []
@@ -74,2 +86,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] NOT IN (@1, @2))',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] NOT IN (1, 2))'
+  },
   params: [1, 2]
@@ -92,2 +108,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NOT NULL)',
+    string: 'SELECT [post].* FROM [post] WHERE ([post].[id] IS NOT NULL)'
+  },
   params: []
@@ -110,2 +130,6 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE (NOT ([post].[id] IN (@1, @2) OR [post].[id] IS NULL))',
+    string: 'SELECT [post].* FROM [post] WHERE (NOT ([post].[id] IN (1, 2) OR [post].[id] IS NULL))'
+  },
   params: [1, 2]
@@ -128,3 +152,7 @@ });
   },
+  mssql: {
+    text  : 'SELECT [post].* FROM [post] WHERE (NOT ([post].[id] IN (@1, @2) OR [post].[id] IS NULL))',
+    string: 'SELECT [post].* FROM [post] WHERE (NOT ([post].[id] IN (1, 2) OR [post].[id] IS NULL))'
+  },
   params: [1, 2]
 });

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

350599

increased by1.29%

Lines of code

9175

increased by0.97%

No dependency changes