node-sql-2 - npm Package Compare versions

Comparing version 0.78.0 to 0.79.0

lib/functions.js

@@ -65,3 +65,3 @@ 'use strict';
 //text search functions available to Postgres
-var textsearchFunctions = ['TS_RANK','TS_RANK_CD', 'PLAINTO_TSQUERY', 'TO_TSQUERY', 'TO_TSVECTOR', 'SETWEIGHT'];
+var textsearchFunctions = ['TS_RANK','TS_RANK_CD', 'PLAINTO_TSQUERY', 'TO_TSQUERY', 'TO_TSVECTOR', 'SETWEIGHT', 'PHRASETO_TSQUERY'];
 
@@ -68,0 +68,0 @@ var standardFunctionNames = aggregateFunctions.concat(scalarFunctions).concat(hstoreFunction).concat(textsearchFunctions).concat(dateFunctions);

lib/node/query.js

@@ -55,3 +55,3 @@ 'use strict';
     this.type = type;
-    this.count = count;
+    this.count = ParameterNode.getNodeOrParameterNode(count);
   }
@@ -58,0 +58,0 @@ });

lib/types.d.ts

@@ -7,3 +7,3 @@
  */
-declare module "sql" {
+declare module "node-sql-2" {
 
@@ -10,0 +10,0 @@ type SQLDialects =

package.json

@@ -5,3 +5,3 @@ {
   "description": "sql builder",
-  "version": "0.78.0",
+  "version": "0.79.0",
   "homepage": "https://github.com/TokyoFarmer/node-sql-2",
@@ -8,0 +8,0 @@ "license": "MIT",

README.md

 # node-sql
 _sql string builder for node_ - supports PostgreSQL, mysql, Microsoft SQL Server, Oracle and sqlite dialects.
 
-Building SQL statements by hand is no fun, especially in a language which has clumsy support for multi-line strings.
+[![Build Status](https://secure.travis-ci.org/brianc/node-sql.png)](http://travis-ci.org/TokyoFarmer/node-sql-2)
 
-So let's build it with JavaScript.
-
-Maybe it's still not fun, but at least it's _less not fun_.
-
-[![Build Status](https://secure.travis-ci.org/brianc/node-sql.png)](http://travis-ci.org/brianc/node-sql)
-
 ## install
 
 ```sh
-$ npm install sql
+$ npm install node-sql-2
 ```
@@ -111,3 +105,3 @@
 
-There are a __lot__ more examples included in the [test/dialects](https://github.com/brianc/node-sql/tree/master/test/dialects) folder.  We encourage you to read through them if you have any questions on usage!
+For more examples, check out [node-sql-examples](https://node-sql-examples.github.io/)
 
@@ -114,0 +108,0 @@ ## from the command line

test/dialects/limit-and-offset-tests.js

@@ -12,18 +12,18 @@ 'use strict';
   pg: {
-    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
+    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT $1',
     string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1'
   },
   sqlite: {
-    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1',
+    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT $1',
     string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 1'
   },
   mysql: {
-    text  : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1',
+    text  : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT ?',
     string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 1'
   },
   mssql: {
-    text  : 'SELECT TOP(1) [user].* FROM [user] ORDER BY [user].[name]',
+    text  : 'SELECT TOP(@1) [user].* FROM [user] ORDER BY [user].[name]',
     string: 'SELECT TOP(1) [user].* FROM [user] ORDER BY [user].[name]'
   },
-  params: []
+  params: [1]
 });
@@ -34,18 +34,23 @@
   pg: {
-    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
+    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT $1 OFFSET $2',
     string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6'
   },
   sqlite: {
-    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6',
+    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT $1 OFFSET $2',
     string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" LIMIT 3 OFFSET 6'
   },
   mysql: {
-    text  : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6',
+    text  : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT ? OFFSET ?',
     string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` LIMIT 3 OFFSET 6'
   },
+  params: [3, 6]
+});
+
+Harness.test({
+  query: user.select(user.star()).from(user).order(user.name.asc).limit(3).offset(6),
   mssql: {
-    text  : 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY',
+    text  : 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY',
     string: 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY'
   },
-  params: []
+  params: [6, 3]
 });
@@ -56,22 +61,22 @@
   pg: {
-    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
+    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET $1',
     string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10'
   },
   sqlite: {
-    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10',
+    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET $1',
     string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10'
   },
   mysql: {
-    text  : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10',
+    text  : 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET ?',
     string: 'SELECT `user`.* FROM `user` ORDER BY `user`.`name` OFFSET 10'
   },
   mssql: {
-    text  : 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 10 ROWS',
+    text  : 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET @1 ROWS',
     string: 'SELECT [user].* FROM [user] ORDER BY [user].[name] OFFSET 10 ROWS'
   },
   oracle: {
-    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10 ROWS',
+    text  : 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET :1 ROWS',
     string: 'SELECT "user".* FROM "user" ORDER BY "user"."name" OFFSET 10 ROWS'
   },
-  params: []
+  params: [10]
 });
@@ -86,11 +91,11 @@
   pg: {
-    text  : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
+    text  : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT $3',
     string: 'SELECT "user".* FROM "user" WHERE ("user"."name" = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = \'John\')) LIMIT 1'
   },
   sqlite: {
-    text  : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT 1',
+    text  : 'SELECT "user".* FROM "user" WHERE ("user"."name" = $1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = $2)) LIMIT $3',
     string: 'SELECT "user".* FROM "user" WHERE ("user"."name" = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = \'John\')) LIMIT 1'
   },
   mysql: {
-    text  : 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = ?)) LIMIT 1',
+    text  : 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = ?) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = ?)) LIMIT ?',
     string: 'SELECT `user`.* FROM `user` WHERE (`user`.`name` = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM `user` WHERE (`user`.`name` = \'John\')) LIMIT 1'
@@ -103,8 +108,8 @@ },
   oracle: {
-    text  : 'SELECT "user".* FROM "user" WHERE ("user"."name" = :1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = :2)) ROWS FETCH NEXT 1 ROWS ONLY',
+    text  : 'SELECT "user".* FROM "user" WHERE ("user"."name" = :1) OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = :2)) ROWS FETCH NEXT :3 ROWS ONLY',
     string: 'SELECT "user".* FROM "user" WHERE ("user"."name" = \'John\') OFFSET (SELECT FLOOR(RANDOM() * COUNT(*)) FROM "user" WHERE ("user"."name" = \'John\')) ROWS FETCH NEXT 1 ROWS ONLY'
   },
-  values: ['John', 'John']
+  values: ['John', 'John', 1]
 });
 
 // TODO: Should probably have a test case like the one above but including an ORDER BY clause so the mssql case can be tested

test/dialects/literal-tests.js

@@ -56,18 +56,18 @@ 'use strict';
   pg: {
-    text  : 'SELECT COUNT("subquery_for_count"."count_column") AS "count_column_count" FROM (SELECT 1 AS "count_column" FROM "user" LIMIT 10 OFFSET 20) "subquery_for_count"',
+    text  : 'SELECT COUNT("subquery_for_count"."count_column") AS "count_column_count" FROM (SELECT 1 AS "count_column" FROM "user" LIMIT $1 OFFSET $2) "subquery_for_count"',
     string: 'SELECT COUNT("subquery_for_count"."count_column") AS "count_column_count" FROM (SELECT 1 AS "count_column" FROM "user" LIMIT 10 OFFSET 20) "subquery_for_count"'
   },
   sqlite: {
-    text  : 'SELECT COUNT("subquery_for_count"."count_column") AS "count_column_count" FROM (SELECT 1 AS "count_column" FROM "user" LIMIT 10 OFFSET 20) "subquery_for_count"',
+    text  : 'SELECT COUNT("subquery_for_count"."count_column") AS "count_column_count" FROM (SELECT 1 AS "count_column" FROM "user" LIMIT $1 OFFSET $2) "subquery_for_count"',
     string: 'SELECT COUNT("subquery_for_count"."count_column") AS "count_column_count" FROM (SELECT 1 AS "count_column" FROM "user" LIMIT 10 OFFSET 20) "subquery_for_count"'
   },
   mysql: {
-    text  : 'SELECT COUNT(`subquery_for_count`.`count_column`) AS `count_column_count` FROM (SELECT 1 AS `count_column` FROM `user` LIMIT 10 OFFSET 20) `subquery_for_count`',
+    text  : 'SELECT COUNT(`subquery_for_count`.`count_column`) AS `count_column_count` FROM (SELECT 1 AS `count_column` FROM `user` LIMIT ? OFFSET ?) `subquery_for_count`',
     string: 'SELECT COUNT(`subquery_for_count`.`count_column`) AS `count_column_count` FROM (SELECT 1 AS `count_column` FROM `user` LIMIT 10 OFFSET 20) `subquery_for_count`'
   },
   oracle: {
-    text  : 'SELECT COUNT("subquery_for_count"."count_column") "count_column_count" FROM (SELECT 1 "count_column" FROM "user" OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY) "subquery_for_count"',
+    text  : 'SELECT COUNT("subquery_for_count"."count_column") "count_column_count" FROM (SELECT 1 "count_column" FROM "user" OFFSET :2 ROWS FETCH NEXT :1 ROWS ONLY) "subquery_for_count"',
     string: 'SELECT COUNT("subquery_for_count"."count_column") "count_column_count" FROM (SELECT 1 "count_column" FROM "user" OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY) "subquery_for_count"'
   },
-  params: []
+  params: [10, 20]
 });

test/dialects/subquery-tests.js

@@ -200,23 +200,23 @@ 'use strict';
   pg: {
-    text  : 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" LIMIT 10 OFFSET 10) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")',
+    text  : 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" LIMIT $1 OFFSET $2) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")',
     string: 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" LIMIT 10 OFFSET 10) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")'
   },
   sqlite: {
-    text  : 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" LIMIT 10 OFFSET 10) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")',
+    text  : 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" LIMIT $1 OFFSET $2) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")',
     string: 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" LIMIT 10 OFFSET 10) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")'
   },
   mysql: {
-    text  : 'SELECT `limit-users`.`name`, `post`.`tags` FROM (SELECT `user`.`id`, `user`.`name` FROM `user` ORDER BY `user`.`name` LIMIT 10 OFFSET 10) `limit-users` LEFT JOIN `post` ON (`post`.`userId` = `limit-users`.`id`)',
+    text  : 'SELECT `limit-users`.`name`, `post`.`tags` FROM (SELECT `user`.`id`, `user`.`name` FROM `user` ORDER BY `user`.`name` LIMIT ? OFFSET ?) `limit-users` LEFT JOIN `post` ON (`post`.`userId` = `limit-users`.`id`)',
     string: 'SELECT `limit-users`.`name`, `post`.`tags` FROM (SELECT `user`.`id`, `user`.`name` FROM `user` ORDER BY `user`.`name` LIMIT 10 OFFSET 10) `limit-users` LEFT JOIN `post` ON (`post`.`userId` = `limit-users`.`id`)'
   },
   mssql: {
-    text  : 'SELECT [limit-users].[name], [post].[tags] FROM (SELECT [user].[id], [user].[name] FROM [user] ORDER BY [user].[name] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY) [limit-users] LEFT JOIN [post] ON ([post].[userId] = [limit-users].[id])',
+    text  : 'SELECT [limit-users].[name], [post].[tags] FROM (SELECT [user].[id], [user].[name] FROM [user] ORDER BY [user].[name] OFFSET @1 ROWS FETCH NEXT @2 ROWS ONLY) [limit-users] LEFT JOIN [post] ON ([post].[userId] = [limit-users].[id])',
     string: 'SELECT [limit-users].[name], [post].[tags] FROM (SELECT [user].[id], [user].[name] FROM [user] ORDER BY [user].[name] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY) [limit-users] LEFT JOIN [post] ON ([post].[userId] = [limit-users].[id])'
   },
   oracle: {
-    text  : 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")',
+    text  : 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" OFFSET :2 ROWS FETCH NEXT :1 ROWS ONLY) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")',
     string: 'SELECT "limit-users"."name", "post"."tags" FROM (SELECT "user"."id", "user"."name" FROM "user" ORDER BY "user"."name" OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY) "limit-users" LEFT JOIN "post" ON ("post"."userId" = "limit-users"."id")'
   },
-  params: []
+  params: [10, 10]
 });
 

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

New author

Supply chain risk

A new npm collaborator published a version of the package for the first time. New collaborators are usually benign additions to a project, but do indicate a change to the security surface area of a package.

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

Lines of code

15054

increased by0.03%

Worsened metrics

Total package byte prevSize

559939

decreased by-0.02%

Number of lines in readme file

182

decreased by-3.19%

Number of medium supply chain risk alerts

2

increased by100%

No dependency changes