sqb - npm Package Compare versions

Comparing version 0.2.9 to 0.2.10

package.json

 {
   "name": "sqb",
   "description": "Plugin-driven, multi-dialect SQL query builder and Database connection framework for JavaScript",
-  "version": "0.2.9",
+  "version": "0.2.10",
   "author": "Panates Ltd.",
@@ -31,5 +31,2 @@ "contributors": [
   },
-  "peerDependencies": {
-    "sqb-connect": "^0.0.11"
-  },
   "engines": {
@@ -36,0 +33,0 @@ "node": ">= 6.0"

src/index.js

@@ -20,2 +20,3 @@ /* SQB
 const ConditionGroup = require('./sqlobjects/conditiongroup');
+const Case = require('./sqlobjects/case');
 const Serializer = require('./serializer');
@@ -42,2 +43,3 @@ const DbPool = require('./pool');
     ConditionGroup,
+    Case,
 
@@ -44,0 +46,0 @@ serializer: function (config) {

src/serializer.js

@@ -16,2 +16,3 @@ /* SQB
 
+/* helper functions */
 function isNumeric(n) {
@@ -21,2 +22,3 @@ return !isNaN(parseFloat(n)) && isFinite(n);
 
+
 /**
@@ -26,3 +28,2 @@ * @class
  */
-
 class Serializer {
@@ -37,2 +38,15 @@
             'order', 'by', 'group', 'count', 'sum', 'average'];
+
+        this.objSerializers = {
+            conditiongroup: this._serializeConditionGroup,
+            condition: this._serializeCondition,
+            raw: this._serializeRaw,
+            select: this._serializeSelect,
+            insert: this._serializeInsert,
+            update: this._serializeUpdate,
+            delete: this._serializeDelete,
+            table: this._serializeTableName,
+            column: this._serializeFieldName,
+            case: this._serializeCase
+        }
     }
@@ -49,3 +63,2 @@
     build(obj, values) {
-        let sql;
         this._outParams = this.namedParams ? {} : [];
@@ -68,15 +81,6 @@ if (values) {
         }
-
-        if (obj.type === 'select') { //noinspection JSCheckFunctionSignatures
-            sql = this._serializeSelect(obj);
-        } else if (obj.type === 'insert') { //noinspection JSCheckFunctionSignatures
-            sql = this._serializeInsert(obj);
-        } else if (obj.type === 'update') { //noinspection JSCheckFunctionSignatures
-            sql = this._serializeUpdate(obj);
-        } else if (obj.type === 'delete') { //noinspection JSCheckFunctionSignatures
-            sql = this._serializeDelete(obj);
-        } else throw new TypeError('Invalid argument');
-
+        assert.ok(['select', 'insert', 'update', 'delete'].includes(obj.type),
+            'Invalid argument');
         return {
-            sql,
+            sql: this._serializeSqlObject(obj),
             params: this._outParams
@@ -97,2 +101,3 @@ }
 
+    //noinspection JSUnusedLocalSymbols
     /**
@@ -111,7 +116,7 @@ * Serialize Select statement
 
-        s = this._serializeColumnNames(obj._columns);
+        s = this._serializeColumns(obj._columns);
         sb.append(s ? ' ' + s : ' *');
 
         if ((s = this._serializeTablesNames(obj._tables))) {
-            sb.append((this.prettyPrint && sb.line.length > 40 ? '\n' : ' ') + s);
+            sb.append((this.prettyPrint && sb.line.length > 40 ? '\n' : ' ') + 'from' + s);
         }
@@ -154,57 +159,33 @@
     _serializeInsert(obj) {
+        assert.ok(['raw', 'table'].includes(obj._table.type), 'Invalid argument. Only Raw or TableName allowed in "insert(?)"');
+
         const sb = new StringBuilder(this.prettyPrint ? 180 : 0);
-
         sb.append('insert into ');
-
-        if (obj._table.type === 'raw')
-            sb.append(this._serializeRaw(obj._table) + ' ');
-        else if (obj._table.type === 'table')
-            sb.append(this._serializeTableName(obj._table) + ' ');
-
-        sb.append('(' + this._serializeColumnNames(obj._columns) + ') ');
-
+        // table name
+        sb.append(this._serializeSqlObject(obj._table));
+        // columns
+        sb.append(' (' + this._serializeColumns(obj._columns) + ') ');
+        // values
         const objValues = obj._values || {};
 
         if (objValues) {
-            if (objValues.isRaw)
-                sb.append(this._serializeRaw(objValues));
 
-            else if (objValues.isSelect) {
-                if (this.prettyPrint) sb.crlf();
-                sb.append(this._serializeSelect(objValues));
-
+            if (['raw', 'select'].includes(objValues.type)) {
+                const s = this._serializeSqlObject(objValues);
+                if (s) {
+                    if (this.prettyPrint && objValues.type === 'select') sb.crlf();
+                    sb.append(s);
+                }
             } else {
                 sb.append('values (');
-                const self = this,
-                    executeParams = this._executeParams;
-                let prmidx = 0;
+                const self = this;
+                self._prmIdx = 0;
 
                 // Iterate over columns
                 obj._columns.forEach(function (col, idx) {
-
                     const field = col.field.toUpperCase(),
-                        val = objValues[field],
-                        prefix = (idx < obj._columns.length - 1 ? ', ' : '');
-
-                    // If value in statement is RegExp, we serialize it as an out parameter
-                    if (val instanceof RegExp) {
-                        const prm = val.source.toUpperCase();
-                        let x;
-
-                        if (Array.isArray(executeParams))
-                            x = prmidx < executeParams.length ? executeParams[prmidx++] : null;
-                        else if (typeof executeParams === 'object')
-                            x = executeParams[prm] || null;
-
-                        if (self.namedParams) {
-                            sb.append(':' + prm + prefix);
-                            self._outParams[prm] = x;
-                        } else {
-                            sb.append('?' + prefix);
-                            self._outParams.push(x);
-                        }
-
-                    } else {
-                        sb.append(self._serializeValue(val) + prefix)
-                    }
+                        val = objValues[field];
+                    const s = self._serializeValue(val);
+                    if (s)
+                        sb.append(s + (idx < obj._columns.length - 1 ? ', ' : ''))
                 });
@@ -225,2 +206,3 @@ sb.append(')');
     _serializeUpdate(obj) {
+        assert.ok(['raw', 'table'].includes(obj._table.type), 'Invalid argument. Only Raw or TableName allowed in "update(?)"');
         assert.ok(!!obj._values, 'values required for Update statement');
@@ -235,9 +217,4 @@
         sb.append('update ');
-
-        if (obj._table.type === 'raw')
-            sb.append(self._serializeRaw(obj._table) + ' ');
-        else if (obj._table.type === 'table')
-            sb.append(self._serializeTableName(obj._table) + ' ');
-
-        sb.append('set');
+        sb.append(this._serializeSqlObject(obj._table));
+        sb.append(' set');
         if (prettyPrint)
@@ -282,15 +259,10 @@ sb.cr();
     _serializeDelete(obj) {
+        assert.ok(['raw', 'table'].includes(obj._table.type), 'Invalid argument. Only Raw or TableName allowed in "delete(?)"');
         const self = this,
             prettyPrint = self.prettyPrint,
             sb = new StringBuilder(prettyPrint ? 180 : 0);
-
         sb.indent = 4;
-
         sb.append('delete from ');
+        sb.append(this._serializeSqlObject(obj._table));
 
-        if (obj._table.type === 'raw')
-            sb.append(self._serializeRaw(obj._table));
-        else if (obj._table.type === 'table')
-            sb.append(self._serializeTableName(obj._table));
-
         // Serialize conditions
@@ -309,3 +281,3 @@ sb.indent = 2;
 
-    //noinspection JSMethodCanBeStatic
+    //noinspection JSMethodCanBeStatic,JSUnusedLocalSymbols
     /**
@@ -320,5 +292,6 @@ * Serialize Raw object
     _serializeRaw(raw) {
-        return raw.text;
+        return raw ? raw.text || '' : '';
     }
 
+    //noinspection JSUnusedLocalSymbols
     /**
@@ -331,23 +304,8 @@ * Serializes array of column names comes after 'Select'
      */
-    _serializeColumnNames(columns) {
+    _serializeColumns(columns) {
         if (!(columns && columns.length)) return '';
-
         const sb = new StringBuilder(this.prettyPrint ? undefined : 0);
-        let col, s;
         sb.indent += 4;
-
-        for (let i = 0; i < columns.length; i++) {
-            col = columns[i];
-            s = '';
-            if (col.isRaw)
-                s = this._serializeRaw(col);
-
-            else if (col.type === 'column')
-                s = this._serializeColumName(col);
-
-            else if (col.type === 'select') {
-                s = this._serializeSelect(col);
-                if (s)
-                    s = '(' + s + ')' + (col._alias ? ' ' + col._alias : '');
-            }
+        columns.forEach(col => {
+            const s = this._serializeColumn(col);
             if (s) {
@@ -357,22 +315,38 @@ if (sb.line) sb.append(', ', true);
             }
-        }
+        });
         return sb.toString();
     }
 
-    //noinspection JSMethodCanBeStatic
     /**
+     * Serializes array of column names comes after 'Select'
+     *
+     * @param {Column} column
+     * @return {string}
+     * @protected
+     */
+    _serializeColumn(column) {
+        if (!column) return '';
+        assert.ok(['column', 'raw', 'case', 'select'].includes(column.type), 'Invalid object for serializing column');
+        const s = this._serializeSqlObject(column);
+        //noinspection JSUnresolvedVariable
+        return column.type === 'select' ? '(' + s + ')' + (column._alias ? ' ' + column._alias : '') : s;
+    }
+
+    //noinspection JSMethodCanBeStatic,JSUnusedLocalSymbols
+    /**
      * Serializes single column name
      *
-     * @param column
+     * @param field
      * @return {string}
      * @protected
      */
-    _serializeColumName(column) {
-        return (column.table ? column.table + '.' : '') + column.field + (column.alias ? ' ' + column.alias : '');
+    _serializeFieldName(field) {
+        return (field.table ? field.table + '.' : '') + field.field + (field.alias ? ' ' + field.alias : '');
     }
 
+    //noinspection JSUnusedLocalSymbols
     /**
      * Serializes tables names comes after 'From'
      *
-     * @param {Array<TableName>} tables
+     * @param {Array<SqlObject>} tables
      * @return {string}
@@ -383,22 +357,18 @@ * @protected
         if (!(tables && tables.length)) return '';
-        let table, str;
-        str = '';
-        for (let i = 0; i < tables.length; i++) {
-            table = tables[i];
+        let str = '';
+        tables.forEach(item => {
             let ss;
-            if (table.type === 'raw')
-                ss = this._serializeRaw(table);
-            else if (table.type === 'table')
-                ss = this._serializeTableName(table);
-            else if (table.type === 'select') {
-                ss = (ss = this._serializeSelect(table)) ?
-                    '(' + ss + ')' + (table._alias ? ' ' + table._alias : '') : '';
+            assert.ok(['raw', 'select', 'table'].includes(item.type),
+                'Invalid object used as Table Name');
+            if ((ss = this._serializeSqlObject(item))) {
+                if (item.type === 'select') { //noinspection JSUnresolvedVariable
+                    ss = '(' + ss + ')' + (item._alias ? ' ' + item._alias : '');
+                }
+                str += (str ? ', ' : ' ') + ss;
             }
-            if (ss)
-                str += (str ? ', ' : ' ') + ss;
-        }
-        return str ? 'from' + str : '';
+        });
+        return str;
     }
 
-    //noinspection JSMethodCanBeStatic
+    //noinspection JSMethodCanBeStatic,JSUnusedLocalSymbols
     /**
@@ -417,9 +387,16 @@ * Serializes single table name
 
+    //noinspection JSUnusedLocalSymbols
+    /**
+     * Serializes single table name
+     *
+     * @param {ConditionGroup} group
+     * @return {string}
+     * @protected
+     */
     _serializeWhere(group) {
         const s = this._serializeConditionGroup(group);
-        if (s)
-            return 'where ' + s;
-        return '';
+        return s ? 'where ' + s : '';
     }
 
+    //noinspection JSUnusedLocalSymbols
     /**
@@ -439,19 +416,9 @@ * Serializes condition group
             const item = group.item(i);
-
-            if (item.isRaw) {
-                logop = item.logicalOperator || logop;
-                s = this._serializeRaw(item);
+            assert.ok(['raw', 'conditiongroup', 'condition'].includes(item.type),
+                'Invalid object used as Condition');
+            logop = item.logicalOperator || logop;
+            if ((s = this._serializeSqlObject(item))) {
+                if (item.type === 'conditiongroup') s = '(' + s + ')';
+                sb.append((sb.line ? ' ' + logop + ' ' : '') + s);
             }
-
-            else if (item.type === 'conditiongroup') {
-                logop = item.logicalOperator;
-                s = this._serializeConditionGroup(item);
-                if (s) s = '(' + s + ')';
-            } else {
-                logop = item.logicalOperator;
-                s = this._serializeCondition(item);
-            }
-
-            if (s)
-                sb.append((sb.line ? ' ' + logop + ' ' : '') + s);
         }
@@ -461,2 +428,3 @@ return sb.toString();
 
+    //noinspection JSUnusedLocalSymbols
     /**
@@ -470,10 +438,8 @@ * Serializes condition
     _serializeCondition(item) {
-
-        if (item.field.isRaw)
-            return this._serializeRaw(item.field);
-
         let str;
-        if (item.field.isSelect)
-            str = '(' + this._serializeSelect(item.field) + ') ';
-        else str = this._isReserved(item.field) ? '"' + item.field + '" ' : item.field + ' ';
+        if (['raw', 'select'].includes(item.field.type)) {
+            str = (str = this._serializeSqlObject(item.field)) && item.field.type === 'select' ?
+                '(' + str + ')' : str;
+        } else
+            str = this._isReserved(item.field) ? '"' + item.field + '"' : item.field;
 
@@ -522,6 +488,7 @@ const outParams = this._outParams;
         if (s)
-            str += operator + ' ' + s;
+            str += ' ' + operator + ' ' + s;
         return str;
     }
 
+    //noinspection JSUnusedLocalSymbols
     /**
@@ -537,2 +504,22 @@ * Serializes any value
             return 'null';
+
+        if (val instanceof RegExp) {
+            const prm = val.source.toUpperCase(),
+                executeParams = this._executeParams;
+            let x;
+
+            if (Array.isArray(executeParams))
+                x = this._prmIdx < executeParams.length ? executeParams[this._prmIdx++] : null;
+
+            else if (typeof executeParams === 'object')
+                x = executeParams[prm] || null;
+
+            if (this.namedParams) {
+                this._outParams[prm] = x;
+                return ':' + prm;
+            } else {
+                this._outParams.push(x);
+                return '?';
+            }
+        }
         if (val.isRaw)
@@ -551,3 +538,3 @@ return this._serializeRaw(val);
 
-    //noinspection JSMethodCanBeStatic
+    //noinspection JSMethodCanBeStatic, JSUnusedLocalSymbols
     /**
@@ -564,2 +551,3 @@ * Serializes string value
 
+    //noinspection JSMethodCanBeStatic, JSUnusedLocalSymbols
     /**
@@ -587,2 +575,3 @@ * Serializes Date value
 
+    //noinspection JSUnusedLocalSymbols
     /**
@@ -661,16 +650,12 @@ * Serializes Array value
 
-        if (join.table.type === 'select') {
-            s = this._serializeSelect(join.table);
-            if (s) {
-                s = ' (' + s + ')' + (join.table._alias ? ' ' + join.table._alias : '');
-                sb.append(s);
+
+        assert.ok(['raw', 'select', 'table'].includes(join.table.type),
+            'Invalid object used as Table Name');
+        if ((s = this._serializeSqlObject(join.table))) {
+            if (join.table.type === 'select') {
+                s = '(' + s + ')' + (join.table._alias ? ' ' + join.table._alias : '');
             }
-        } else {
-            if (join.table.isRaw)
-                sb.append(' ' + this._serializeRaw(join.table));
-            else
-                sb.append(' ' + this._serializeTableName(join.table))
+            sb.append(' ' + s);
         }
 
-
         s = this._serializeConditionGroup(join.conditions);
@@ -690,3 +675,3 @@ if (s)
     _serializeGroupBy(columns) {
-        return this._serializeColumnNames(columns);
+        return this._serializeColumns(columns);
     }
@@ -721,3 +706,3 @@
     /**
-     * Serializes single value Update statement
+     * Serializes single value for Update statement
      *
@@ -744,4 +729,60 @@ * @param {string} key
     }
+
+    //noinspection JSUnusedLocalSymbols
+    /**
+     * Serializes Case expression
+     *
+     * @param {Case} obj
+     * @return {string}
+     * @protected
+     */
+    _serializeCase(obj) {
+        if (obj._expressions.length) {
+            const self = this,
+                sb = new StringBuilder(this.prettyPrint ? undefined : 0);
+            sb.indent = 4;
+            sb.append('case');
+
+            obj._expressions.forEach((item) => {
+                assert.ok(['conditiongroup', 'condition', 'raw'].includes(item.condition.type),
+                    'Invalid object used in "case" expression');
+                const s = self._serializeSqlObject(item.condition);
+                if (s)
+                    sb.append(' when ' + s + ' then ' + (self._serializeValue(item.value)) || 'null');
+            });
+
+            if (obj._elseValue !== undefined) {
+                const s = self._serializeValue(obj._elseValue);
+                if (s)
+                    sb.append(' else ' + s);
+            }
+            sb.append(' end' + (obj._alias ? ' ' + obj._alias : ''));
+            return sb.toString();
+        }
+    }
+
+    //noinspection JSUnusedLocalSymbols
+    /**
+     * Serializes Case expression
+     *
+     * @param {SqlObject} obj
+     * @return {string}
+     * @protected
+     */
+    _serializeSqlObject(obj) {
+        if (obj) {
+            const fn = this.objSerializers[obj.type];
+            return fn ? fn.call(this, obj) : '';
+        }
+    }
 }
 
+/**
+ * Registers a serializer class for given dialect
+ *
+ * @param {String} dialect
+ * @param {constructor<Serializer>} serializerProto
+ * @static
+ * @public
+ */
 Serializer.register = function (dialect, serializerProto) {
@@ -752,2 +793,10 @@ const items = this._registry = this._registry || {};
 
+/**
+ * Retrieves serializer class for given dialect
+ *
+ * @param {String} dialect
+ * @return {constructor<Serializer>}
+ * @static
+ * @public
+ */
 Serializer.get = function (dialect) {
@@ -757,5 +806,15 @@ return this._registry ? this._registry[dialect] : undefined;
 
+/**
+ * Creates serializer for given dialect/config
+ *
+ * @param {String|Object} config
+ * @return Serializer
+ * @static
+ * @public
+ */
 Serializer.create = function (config) {
-    if (config instanceof Serializer)
+    if (config instanceof Serializer) {
+        //noinspection JSValidateTypes
         return config;
+    }
 
@@ -768,4 +827,6 @@ config = typeof config === 'string' ? {dialect: config} : typeof config === 'object' ? config : {};
     const clazz = this.get(config.dialect);
-    if (clazz)
+    if (clazz) {
+        //noinspection JSValidateTypes
         return new clazz(config);
+    }
     else throw new Error(`Dialect "${config.dialect}" is not registered`);
@@ -772,0 +833,0 @@ };

src/sqlobjects.js

@@ -17,2 +17,3 @@ /* SQB
 const Join = require('./sqlobjects/join');
+const Case = require('./sqlobjects/case');
 
@@ -86,2 +87,8 @@
     },
+
+    //noinspection JSMethodCanBeStatic,JSUnusedGlobalSymbols
+    case () {
+        return new Case();
+    }
+
 };

src/sqlobjects/conditiongroup.js

@@ -30,7 +30,13 @@ /* SQB
 
-    add(arr) {
+    add() {
+        if (!arguments.length) return this;
         const self = this;
         let logop = this.logicalOperator;
+
+        if (typeof arguments[0] === 'string') {
+            self._items.push(new Condition(...arguments));
+            return this;
+        }
+
         for (const arg of arguments) {
-
             // Process array argument
@@ -37,0 +43,0 @@ if (Array.isArray(arg) && arg.length) {

src/sqlobjects/select.js

@@ -195,3 +195,3 @@ /* SQB
      */
-    alias(alias) {
+    as(alias) {
         this._alias = alias;
@@ -198,0 +198,0 @@ return this;

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

67019

increased by5.49%

Dependency count

1

decreased by-50%

Number of package files

23

increased by4.55%

Lines of code

1976

increased by7.1%

No dependency changes