sql-query - npm Package Compare versions

Comparing version 0.1.16 to 0.1.24

lib/Comparators.js

@@ -10,2 +10,5 @@ exports.between = function (a, b) {
 };
+exports.not_like = function (expr) {
+	return createSpecialObject({ expr: expr }, 'not_like');
+};
 
@@ -12,0 +15,0 @@ exports.eq = function (v) {

lib/Dialects/mysql.js

 var util    = require("util");
 var helpers = require("../Helpers");
 
+exports.DataTypes = {
+    id: 'INTEGER PRIMARY KEY AUTO_INCREMENT',
+    int: 'INTEGER',
+    float: 'FLOAT(12,2)',
+    bool: 'TINYINT(1)',
+    text: 'TEXT'
+};
+
+
+exports.escape = function (query, args) {
+	return helpers.escapeQuery(exports, query, args);
+}
+
 exports.escapeId = function () {
@@ -103,1 +116,3 @@ return Array.prototype.slice.apply(arguments).map(function (el) {
 }
+
+exports.defaultValuesStmt = "VALUES()";

lib/Dialects/postgresql.js

 var util    = require("util");
 var helpers = require("../Helpers");
 
+exports.DataTypes = {
+    id: 'SERIAL PRIMARY KEY',
+    int: 'INTEGER',
+    float: 'REAL',
+    bool: 'SMALLINT',
+    text: 'TEXT'
+};
+
+
+exports.escape = function (query, args) {
+	return helpers.escapeQuery(exports, query, args);
+}
+
 exports.escapeId = function () {
@@ -55,1 +68,3 @@ return Array.prototype.slice.apply(arguments).map(function (el) {
 };
+
+exports.defaultValuesStmt = "DEFAULT VALUES";

lib/Dialects/sqlite.js

 var util    = require("util");
 var helpers = require("../Helpers");
 
+
+exports.DataTypes = {
+    isSQLITE: true,
+    id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
+    int: 'INTEGER',
+    float: 'FLOAT(12,2)',
+    bool: 'TINYINT(1)',
+    text: 'TEXT'
+};
+
+exports.escape = function (query, args) {
+	return helpers.escapeQuery(exports, query, args);
+}
+
 exports.escapeId = require("./mysql").escapeId;
@@ -43,1 +57,3 @@
 };
+
+exports.defaultValuesStmt = "DEFAULT VALUES";

lib/Helpers.js

@@ -7,7 +7,9 @@
 module.exports.escapeQuery = function (Dialect, query, args) {
-	return query.replace(/[?]+/g, function (match) {
+	var pos = 0;
+
+	return query.replace(/\?{1,2}/g, function (match) {
 		if (match == '?') {
-			return Dialect.escapeVal(args.shift());
+			return Dialect.escapeVal(args[pos++]);
 		} else if (match == '??') {
-			return Dialect.escapeId(args.shift());
+			return Dialect.escapeId(args[pos++]);
 		}
@@ -14,0 +16,0 @@ });

lib/Insert.js

@@ -28,4 +28,8 @@ var Set = require("./Set");
 				}
-				query.push("(" + cols.join(", ") + ")");
-				query.push("VALUES (" + vals.join(", ") + ")");
+				if (cols.length == 0) {
+					query.push(Dialect.defaultValuesStmt);
+				} else {
+					query.push("(" + cols.join(", ") + ")");
+					query.push("VALUES (" + vals.join(", ") + ")");
+				}
 			}
@@ -32,0 +36,0 @@

lib/Query.js

@@ -0,1 +1,2 @@
+var CreateQuery = require("./Create").CreateQuery;
 var SelectQuery = require("./Select").SelectQuery;
@@ -29,2 +30,5 @@ var InsertQuery = require("./Insert").InsertQuery;
 		escapeVal : Dialect.escapeVal.bind(Dialect),
+        create: function(){
+            return new CreateQuery(Dialect, opts);
+        },
 		select: function () {
@@ -31,0 +35,0 @@ return new SelectQuery(Dialect, opts);

lib/Select.js

@@ -250,34 +250,43 @@ var Helpers = require('./Helpers');
 
-					if (!sql.from[i].select[j].f) continue;
-
 					str = sql.from[i].select[j].f + "(";
 
-					if (sql.from[i].select[j].c && !Array.isArray(sql.from[i].select[j].c)) {
-						sql.from[i].select[j].c = [ sql.from[i].select[j].c ];
-					}
+					if (sql.from[i].select[j].f) {
+						str = sql.from[i].select[j].f + "(";
 
-					if (Array.isArray(sql.from[i].select[j].c)) {
-						str += sql.from[i].select[j].c.map(function (el) {
-							if (typeof el.type == "function") {
-								switch (el.type()) {
-									case "text":
-										return Dialect.escapeVal(el.data, opts.timezone);
-									default:
-										return el;
+						if (sql.from[i].select[j].c && !Array.isArray(sql.from[i].select[j].c)) {
+							sql.from[i].select[j].c = [ sql.from[i].select[j].c ];
+						}
+
+						if (Array.isArray(sql.from[i].select[j].c)) {
+							str += sql.from[i].select[j].c.map(function (el) {
+								if (!el) {
+									return Dialect.escapeVal(el);
 								}
-							}
-							if (typeof el != "string") {
-								return el;
-							}
-							if (sql.from.length == 1) {
-								return Dialect.escapeId(el);
-							} else {
-								return Dialect.escapeId(sql.from[i].a, el);
-							}
-						}).join(", ");
+								if (typeof el.type == "function") {
+									switch (el.type()) {
+										case "text":
+											return Dialect.escapeVal(el.data, opts.timezone);
+										default:
+											return el;
+									}
+								}
+								if (typeof el != "string") {
+									return el;
+								}
+								if (sql.from.length == 1) {
+									return Dialect.escapeId(el);
+								} else {
+									return Dialect.escapeId(sql.from[i].a, el);
+								}
+							}).join(", ");
+						} else {
+							str += "*";
+						}
+						str += ")";
+					} else if (sql.from[i].select[j].sql) {
+						str = '(' + sql.from[i].select[j].sql + ')';
 					} else {
-						str += "*";
+						continue;
 					}
 
-					str += ")";
 					str += (sql.from[i].select[j].a ? " AS " + Dialect.escapeId(sql.from[i].select[j].a) : "");
@@ -284,0 +293,0 @@

lib/Where.js

@@ -110,2 +110,9 @@ var Helpers = require('./Helpers');
 					break;
+				case "not_like":
+					query.push(
+						buildComparisonKey(Dialect, where.t, k) +
+						" NOT LIKE " +
+						Dialect.escapeVal(where.w[k].expr, opts.timezone)
+					);
+					break;
 				case "eq":
@@ -112,0 +119,0 @@ case "ne":

package.json

@@ -9,3 +9,3 @@ {
   	],
-	"version": "0.1.16",
+	"version": "0.1.24",
 	"license": "MIT",
@@ -12,0 +12,0 @@ "repository": {

Readme.md

@@ -16,2 +16,3 @@ ## NodeJS SQL query builder
 - SQLite
+- MSSQL
 
@@ -18,0 +19,0 @@ ## About

test/common.js

@@ -12,3 +12,7 @@ var common = exports;
 };
+common.Create = function(){
+    var q = new (Query.Query)();
 
+    return q.create();
+};
 common.Insert = function () {
@@ -15,0 +19,0 @@ var q = new (Query.Query)();

test/integration/test-select-type.js

@@ -15,2 +15,7 @@ var common     = require('../common');
 assert.equal(
+  common.Select().from('table1').fun('dbo.fnBalance', [ 80, null, null], 'balance').build(),
+  "SELECT DBO.FNBALANCE(80, NULL, NULL) AS `balance` FROM `table1`"
+);
+
+assert.equal(
 	common.Select().from('table1').fun('myfun', [ 'col1', 'col2'], 'alias').build(),
@@ -17,0 +22,0 @@ "SELECT MYFUN(`col1`, `col2`) AS `alias` FROM `table1`"

test/integration/test-select.js

@@ -40,2 +40,9 @@ var common     = require('../common');
 assert.equal(
+	common.Select().from('table1').select(
+		['abc','def', { a: 'ghi', sql: 'SOMEFUNC(ghi)' }]
+	).build(),
+	"SELECT `abc`, `def`, (SOMEFUNC(ghi)) AS `ghi` FROM `table1`"
+);
+
+assert.equal(
 	common.Select().calculateFoundRows().from('table1').build(),
@@ -42,0 +49,0 @@ "SELECT SQL_CALC_FOUND_ROWS * FROM `table1`"

test/integration/test-where.js

@@ -159,2 +159,7 @@ var common     = require('../common');
 assert.equal(
+	common.Select().from('table1').where({ col: common.Query.not_like('abc') }).build(),
+	"SELECT * FROM `table1` WHERE `col` NOT LIKE 'abc'"
+);
+
+assert.equal(
 	common.Select().from('table1').where({ col: common.Query.not_in([ 1, 2, 3 ]) }).build(),
@@ -161,0 +166,0 @@ "SELECT * FROM `table1` WHERE `col` NOT IN (1, 2, 3)"

test/lib/test-helpers.js

@@ -36,1 +36,14 @@ var assert  = require('assert');
 );
+
+// Should match at most 2 '?' at a time
+assert.equal(
+	Helpers.escapeQuery(Dialect, "?????", ['a', 'b', 'c']),
+	"`a``b`'c'"
+);
+
+// Should not modify provided array
+var arr = ['a', 'b', 'c'];
+assert.equal(
+	arr.join(','),
+	'a,b,c'
+)

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

61591

increased by17.57%

Number of package files

41

increased by10.81%

Lines of code

2084

increased by20.12%

Number of lines in readme file

21

increased by5%

No dependency changes