sqb - npm Package Compare versions

Comparing version 0.0.6 to 0.0.8

lib/core/column.js

@@ -18,8 +18,7 @@ /**
         let m = fieldName.match(/^(?:(\w+)(?:\.))?(\w+) ?(?:as)? ?(\w+)?$/);
-        if (m) {
-            this.table = m[1];
-            this.field = m[2];
-            this.alias = m[3];
-        } else
-            this.raw = fieldName;
+        if (!m)
+            throw new Error('Invalid definition "' + fieldName + '" for column');
+        this.table = m[1];
+        this.field = m[2];
+        this.alias = m[3];
     }
@@ -26,0 +25,0 @@

lib/core/condition.js

@@ -19,3 +19,3 @@ /**
 
-    constructor(field, operator, value) {
+    constructor(field, operator, value, value2) {
         super();
@@ -29,4 +29,6 @@ this.type = 'condition';
         } else {
+            this.operator = operator || '=';
             this.value = value;
-            this.operator = operator;
+            if (this.operator === 'between' && !(value instanceof RegExp))
+                this.value = Array.isArray(value) ? value : [value, value2];
         }
@@ -81,4 +83,4 @@ }
 
-Condition.Operators = ['is', '=', '!=', '<', '>', '<=', '>=', '<>', '!<', '!>'];
+Condition.Operators = ['is', '=', '!=', '<', '>', '<=', '>=', '<>', '!<', '!>', 'like', 'between'];
 
 module.exports = Condition;

lib/generator.js

@@ -65,3 +65,4 @@ /**
             sb.indent = 0;
-            sb.append((this.prettyPrint && (sb.line.length > 40 || sb.lines > 1) ? '\n' : (sb.line ? ' ' : '')) + s);
+            sb.append((this.prettyPrint && (sb.line.length > 40 || sb.lines > 1) ? (sb.line ? '\n' : '') :
+                    (sb.line ? ' ' : '')) + s);
             if (this.prettyPrint) sb.cr();
@@ -111,3 +112,3 @@ }
     _generateColumn(column) {
-        let s = (column.raw || (column.table ? column.table + '.' : '') + column.field);
+        let s = (column.table ? column.table + '.' : '') + column.field;
         if (column.alias)
@@ -177,21 +178,39 @@ s += ' ' + column.alias;
 
+        let operator = item.operator.toLowerCase(), s;
+
         if (item.param) {
-            if (this.namedParams) {
-                str += item.operator + ' :' + item.param;
-                this.params[item.param] = this._inputParams[item.param];
+            let inputprm = this._inputParams[item.param];
+            if (operator === 'between') {
+                if (this.namedParams) {
+                    s = ':' + item.param + '1 and :' + item.param + '2';
+                    this.params[item.param + '1'] = Array.isArray(inputprm) ? inputprm[0] : inputprm;
+                    this.params[item.param + '2'] = Array.isArray(inputprm) ? inputprm[1] : null;
+                } else {
+                    s = '? and ?';
+                    this.params.push(Array.isArray(inputprm) ? inputprm[0] : inputprm);
+                    this.params.push(Array.isArray(inputprm) ? inputprm[1] : null);
+                }
             } else {
-                str += item.operator + ' ?';
-                this.params.push(this._inputParams[item.param]);
+                if (this.namedParams) {
+                    s = ':' + item.param;
+                    this.params[item.param] = inputprm;
+                } else {
+                    s = '?';
+                    this.params.push(inputprm);
+                }
             }
         } else {
-            let s = this._generateValue(item.value),
-                operator = item.operator;
+            if (operator === 'between') {
+                s = this._generateValue(item.value[0]) + ' and ' +
+                    this._generateValue(item.value[1]);
+            } else
+                s = this._generateValue(item.value);
             if (s.startsWith('(')) {
-                if (item.operator === '=')
-                    operator = 'in';
-                else if (item.operator === '!=')
-                    operator = 'not in'
+                if (operator === '=') operator = 'in';
+                else if (operator === '!=') operator = 'not in'
             }
+        }
+
+        if (s)
             str += operator + ' ' + s;
-        }
         return str;
@@ -198,0 +217,0 @@ }

lib/sqb.js

@@ -85,25 +85,31 @@ /**
 
-    condition: function (logicalOperator, field, operator, value) {
-        if (!field)
-            throw new Error('Invalid arguments');
-        let item = Array.isArray(field) ? Reflect.construct(ConditionGroup, field) :
-            Reflect.construct(Condition, Array.prototype.slice.call(arguments, 1));
-        item.logicalOperator = logicalOperator;
-        return item;
-    },
-
     or: function (field, operator, value) {
-        let args = Array.prototype.slice.call(arguments);
-        Array.prototype.splice.call(args, 0, 0, 'or');
-        return Sqb.condition.apply(Sqb, args);
+        let out = Sqb._condition.apply(Sqb, arguments);
+        out.logicalOperator = 'or';
+        return out;
     },
 
     and: function (field, operator, value) {
-        let args = Array.prototype.slice.call(arguments);
-        Array.prototype.splice.call(args, 0, 0, 'and');
-        return Sqb.condition.apply(Sqb, args);
-    }
+        let out = Sqb._condition.apply(Sqb, arguments);
+        out.logicalOperator = 'and';
+        return out;
+    },
 
+    /**
+     *
+     * @param field
+     * @param operator
+     * @param value
+     * @return {Condition}
+     * @private
+     */
+    _condition: function (field, operator, value) {
+        if (!field)
+            throw new Error('Invalid arguments');
+        return Array.isArray(field) ? Reflect.construct(ConditionGroup, field) :
+            Reflect.construct(Condition, arguments);
+    },
+
 };
 
 module.exports = Sqb;

package.json

 {
   "name": "sqb",
   "description": "Lightweight, multi-dialect SQL query builder for JavaScript",
-  "version": "0.0.6",
+  "version": "0.0.8",
   "author": "Panates Ltd.",
@@ -6,0 +6,0 @@ "contributors": [

README.md

@@ -15,2 +15,57 @@ # SQB
 
+```js
+const sqb = require('./'),
+    and = sqb.and,
+    or = sqb.or,
+    innerJoin = sqb.innerJoin,
+    select = sqb.select,
+    raw = sqb.raw;
+
+let sql =
+    select(
+        'b.ID as book_id', 'b.name book_name', 'c.name category_name',
+        select(raw('count(*)')).from('articles a')
+            .where(and('a.book_id', '=', raw("b.id"))).alias('article_count')
+    )
+        .from('BOOKS b')
+        .join(
+            innerJoin('category c')
+                .on(and('c.id', '=', raw('b.category_id')), and('c.kind', 'science'))
+        )
+        .where(
+            and('name', 'like', /name/),
+            and([
+                or('release_date', 'between', /release_date/),
+                or('release_date', 'between', new Date(2015, 0, 1, 0, 0, 0, 0), new Date(2016, 0, 1, 0, 0, 0, 0)),
+            ]),
+            and([
+                or('c.name', '=', 'novel'),
+                or('c.name', '=', 'horror'),
+                or('c.name', '=', 'child'),
+                or(select('name').from('category').where(and('id', 5)))
+            ])
+        )
+        .orderBy("c.name", "b.release_date desc");
+```
+
+SQL output
+
+```sql
+select b.ID book_id, b.name book_name, c.name category_name, 
+    (select count(*) from articles a where a.book_id = b.id) article_count
+from BOOKS b
+  inner join category c on c.id = b.category_id and c.kind = 'science'
+where name like ? and (release_date between ? and ?
+        or release_date between '2015-01-01' and '2016-01-01')
+    and (c.name = 'novel' or c.name = 'horror' or c.name = 'child'
+        or (select name from category where id = 5) = null)
+order by c.name, b.release_date desc
+```
+
+Paremeters output
+
+```js
+[ 'WHTE DOG', 2000-01-01T00:00:00.000Z, 2001-01-01T00:00:00.000Z ]
+```
+
 ## Node Compatibility
@@ -17,0 +72,0 @@

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

30201

increased by9.77%

Lines of code

824

increased by3%

Number of lines in readme file

90

increased by157.14%

No dependency changes