sqb - npm Package Compare versions

Comparing version 0.0.12 to 0.0.13

lib/core/abstract.js

@@ -14,4 +14,2 @@ /* SQB.js
 
-
-
 /**
@@ -27,4 +25,4 @@ * @class
 
-    build(config) {
-        return createSerializer(config).build(this);
+    build(config, params) {
+        return createSerializer(config).build(this, (config ? config.params : undefined) || params);
     }
@@ -31,0 +29,0 @@

lib/core/insert.js

@@ -23,24 +23,25 @@ /* SQB.js
 
-    constructor(table, values) {
+    constructor() {
         super();
         this.type = 'insert';
         this._columns = [];
-        this.into(table);
-        this.values(values);
     }
 
+    /**
+     *
+     * @param {string|Raw} columns..
+     * @return {Insert}
+     */
     columns(columns) {
-        if (arguments.length === 0 || !columns) return this;
-        for (let i = 0; i < arguments.length; i++) {
-            let col;
-            if (arguments[i] instanceof RegExp) {
-                col = new Column(arguments[i].source);
-                col.isParam = true;
-            } else
-                col = new Column(String(arguments[i]));
-            this._columns.push(col);
-        }
+        if (!columns) return this;
+        for (let i = 0; i < arguments.length; i++)
+            this._columns.push(arguments[i] instanceof SqlObject ? arguments[i] : new Column(arguments[i]));
         return this;
     }
 
+    /**
+     *
+     * @param {string|Raw} table
+     * @return {Insert}
+     */
     into(table) {
@@ -52,2 +53,7 @@ if (!table) return this;
 
+    /**
+     *
+     * @param {Array|Object|Raw} values
+     * @return {Insert}
+     */
     values(values) {
@@ -54,0 +60,0 @@ if (!values)

lib/core/select.js

@@ -40,31 +40,71 @@ /* SQB.js
 
-    columns(columns) {
-        if (arguments.length === 0 || !columns) return this;
-        for (let i = 0; i < arguments.length; i++)
-            this._columns.push(arguments[i] instanceof SqlObject ? arguments[i] : new Column(arguments[i]));
-        return this;
-    }
-
+    /**
+     *
+     * @return {Select}
+     * @public
+     */
     clearColumns() {
         this._columns = [];
+        return this;
     }
 
+    /**
+     *
+     * @return {Select}
+     * @public
+     */
     clearFrom() {
         this._tables = [];
+        return this;
     }
 
+    /**
+     *
+     * @return {Select}
+     * @public
+     */
     clearJoin() {
         this._joins = [];
+        return this;
     }
 
+    /**
+     *
+     * @return {Select}
+     * @public
+     */
     clearOrderBy() {
         this._orderby = [];
+        return this;
     }
 
+    /**
+     *
+     * @return {Select}
+     * @public
+     */
     clearWhere() {
         this._where = new ConditionGroup();
+        return this;
     }
 
+    /**
+     *
+     * @param {string|Raw} columns..
+     * @return {Select}
+     */
+    columns(columns) {
+        if (!columns) return this;
+        for (let i = 0; i < arguments.length; i++)
+            this._columns.push(arguments[i] instanceof SqlObject ? arguments[i] : new Column(arguments[i]));
+        return this;
+    }
+
+    /**
+     *
+     * @param {string|Raw} table..
+     * @return {Select}
+     */
     from(table) {
-        if (arguments.length === 0 || !table) return this;
+        if (!table) return this;
         for (let i = 0; i < arguments.length; i++)
@@ -79,4 +119,9 @@ this._tables.push(
 
+    /**
+     *
+     * @param {Array<Join>} joins
+     * @return {Select}
+     */
     join(joins) {
-        if (arguments.length === 0 || !joins) return this;
+        if (!joins) return this;
         for (let i = 0; i < arguments.length; i++) {
@@ -90,4 +135,10 @@ if (!(arguments[i] instanceof Join))
 
+    /**
+     *
+     * @param {Condition} conditions..
+     * @return {Select}
+     * @public
+     */
     where(conditions) {
-        if (arguments.length === 0 || !conditions) return this;
+        if (!conditions) return this;
         this._where.add.apply(this._where, arguments);
@@ -97,4 +148,10 @@ return this;
 
+    /**
+     *
+     * @param fields
+     * @return {Select}
+     * @public
+     */
     orderBy(fields) {
-        if (arguments.length === 0 || !fields) return this;
+        if (!fields) return this;
         for (let i = 0; i < arguments.length; i++)
@@ -105,2 +162,8 @@ this._orderby.push(arguments[i].isOrder ? arguments[i] : new Order(String(arguments[i])));
 
+    /**
+     *
+     * @param {string} alias
+     * @return {Select}
+     * @public
+     */
     alias(alias) {
@@ -111,2 +174,8 @@ this._alias = alias;
 
+    /**
+     *
+     * @param {int} limit
+     * @return {Select}
+     * @public
+     */
     limit(limit) {
@@ -117,2 +186,8 @@ this._limit = limit;
 
+    /**
+     *
+     * @param {int} offset
+     * @return {Select}
+     * @public
+     */
     offset(offset) {
@@ -119,0 +194,0 @@ this._offset = offset;

lib/serializer.js

@@ -35,11 +35,2 @@ /* SQB.js
             'order', 'by', 'group', 'count', 'sum', 'average'];
-        // We build a new map of upper keys for case insensitivity
-        let values = config.params && typeof config.params === 'object' ? config.params : {},
-            obj = {};
-        Object.getOwnPropertyNames(values).forEach(
-            function (key) {
-                obj[key.toUpperCase()] = values[key];
-            }
-        );
-        this._inputValues = obj;
     }
@@ -51,8 +42,26 @@
      * @param {SqlObject} obj
+     * @param {Array|Object} [values]
      * @return {{sql: string, params: Array|Object}}
      * @public
      */
-    build(obj) {
+    build(obj, values) {
         let sql;
-        this._paramsOut = this.namedParams ? {} : [];
+        this._outParams = this.namedParams ? {} : [];
+        if (values) {
+            if (Array.isArray(values))
+                this._executeParams = values;
+            else if (typeof values === 'object') {
+                // We build a new map with upper keys for case insensitivity
+                let obj = {};
+                Object.getOwnPropertyNames(values).forEach(
+                    function (key) {
+                        obj[key.toUpperCase()] = values[key];
+                    }
+                );
+                this._executeParams = obj;
+            }
+            else
+                throw new TypeError('Invalid argument');
+        }
+
         if (obj.type === 'select')
@@ -62,2 +71,4 @@ sql = this._serializeSelect(obj);
             sql = this._serializeInsert(obj);
+        else if (obj.type === 'update')
+            sql = this._serializeUpdate(obj);
         else throw new TypeError('Invalid argument');
@@ -67,3 +78,3 @@
             sql,
-            params: this._paramsOut
+            params: this._outParams
         }
@@ -132,3 +143,3 @@ }
     _serializeInsert(obj) {
-        let sb = new StringBuilder(this.prettyPrint ? 180 : 0), s;
+        let sb = new StringBuilder(this.prettyPrint ? 180 : 0);
 
@@ -144,24 +155,42 @@ sb.append('insert into ');
 
-        let values = obj._values || this._inputValues || {};
+        let objValues = obj._values || {};
 
-        if (values) {
-            if (values.isRaw)
-                sb.append(this._serializeRaw(values));
-            else if (values.isSelect) {
+        if (objValues) {
+            if (objValues.isRaw)
+                sb.append(this._serializeRaw(objValues));
+
+            else if (objValues.isSelect) {
                 if (this.prettyPrint) sb.crlf();
-                sb.append(this._serializeSelect(values));
+                sb.append(this._serializeSelect(objValues));
+
             } else {
                 sb.append('values (');
-                let self = this;
+                let self = this,
+                    executeParams = this._executeParams,
+                    prmidx = 0;
+
+                // Iterate over columns
                 obj._columns.forEach(function (col, idx) {
-                    let val = values[col.field.toUpperCase()],
+
+                    let field = col.field.toUpperCase(),
+                        val = objValues[field],
                         prefix = (idx < obj._columns.length - 1 ? ', ' : '');
-                    if (col.isParam) {
+
+                    // If value in statement is RegExp, we serialize it as an out parameter
+                    if (val instanceof RegExp) {
+                        let prm = val.source.toUpperCase(), 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(':' + col.field.toUpperCase() + prefix);
-                            self._paramsOut[col.field.toUpperCase()] = val;
+                            sb.append(':' + prm + prefix);
+                            self._outParams[prm] = x;
                         } else {
                             sb.append('?' + prefix);
-                            self._paramsOut.push(val);
+                            self._outParams.push(x);
                         }
+
                     } else {
@@ -178,2 +207,24 @@ sb.append(self._serializeValue(val) + prefix)
     /**
+     * Serialize Update statement
+     *
+     * @param {Update} obj Update statement object
+     * @return {string}
+     * @protected
+     */
+    _serializeUpdate(obj) {
+        let sb = new StringBuilder(this.prettyPrint ? 180 : 0);
+
+        sb.append('update ');
+
+        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('set ');
+
+        return sb.toString();
+    }
+
+    /**
      * Serialize Raw object
@@ -328,12 +379,12 @@ *
         if (item.param) {
-            let inputprm = this._inputValues[item.param.toUpperCase()];
+            let inputprm = this._executeParams ? this._executeParams[item.param.toUpperCase()] : null;
             if (operator === 'between') {
                 if (this.namedParams) {
                     s = ':' + item.param + '1 and :' + item.param + '2';
-                    this._paramsOut[item.param + '1'] = Array.isArray(inputprm) ? inputprm[0] : inputprm;
-                    this._paramsOut[item.param + '2'] = Array.isArray(inputprm) ? inputprm[1] : null;
+                    this._outParams[item.param + '1'] = Array.isArray(inputprm) ? inputprm[0] : inputprm;
+                    this._outParams[item.param + '2'] = Array.isArray(inputprm) ? inputprm[1] : null;
                 } else {
                     s = '? and ?';
-                    this._paramsOut.push(Array.isArray(inputprm) ? inputprm[0] : inputprm);
-                    this._paramsOut.push(Array.isArray(inputprm) ? inputprm[1] : null);
+                    this._outParams.push(Array.isArray(inputprm) ? inputprm[0] : inputprm);
+                    this._outParams.push(Array.isArray(inputprm) ? inputprm[1] : null);
                 }
@@ -343,6 +394,6 @@ } else {
                     s = ':' + item.param;
-                    this._paramsOut[item.param] = inputprm;
+                    this._outParams[item.param] = inputprm;
                 } else {
                     s = '?';
-                    this._paramsOut.push(inputprm);
+                    this._outParams.push(inputprm);
                 }
@@ -349,0 +400,0 @@ }

lib/sqb.js

@@ -13,2 +13,5 @@ /* SQB.js
 const Select = require('./core/select');
+const Insert = require('./core/insert');
+const Update = require('./core/update');
+
 const Raw = require('./core/raw');
@@ -19,5 +22,5 @@ const Column = require('./core/column');
 const ConditionGroup = require('./core/conditiongroup');
-const Insert = require('./core/insert');
+
+const Serializer = require('./serializer');
 const createSerializer = require('./serializer-factory');
-const Serializer = require('./serializer');
 
@@ -36,2 +39,4 @@ /* External module dependencies. */
     Select,
+    Insert,
+    Update,
     Raw,
@@ -43,3 +48,2 @@ Column,
     Serializer,
-    Insert,
 
@@ -52,3 +56,3 @@ serializer: createSerializer,
 
-    select: function (fields) {
+    select: function () {
         let obj = new Select();
@@ -67,2 +71,6 @@ if (arguments.length > 0)
 
+    update: function (table, values) {
+        return new Update(table, values);
+    },
+
     column: function (field, alias) {
@@ -69,0 +77,0 @@ return new Column(field, alias);

package.json

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

README.md

@@ -15,2 +15,3 @@ # SQB
 
+
 ```js
@@ -23,5 +24,10 @@ const sqb = require('sqb'),
     select = sqb.select,
-    raw = sqb.raw;
+    raw = sqb.raw,
+    serializer = sqb.serializer({
+      dialect:'oracle',
+      prettyPrint: true,
+      namedParams: false
+    });
 
-let sql =
+let statement =
     select(
@@ -52,11 +58,7 @@ 'b.ID as book_id', 'b.name book_name', 'c.name category_name',
 
-let result = sql.build({
-    dialect: "generic",
-    prettyPrint: true,
-    namedParams: false,
-    params: {
-        name: 'WHTE DOG',
+let result = serializer.build(statement,
+    {
+        name: 'WIHTE DOG',
         release_date: [new Date(2000, 0, 1, 0, 0, 0, 0), new Date(2001, 0, 1, 0, 0, 0, 0)]
-    }
-});
+    });
 console.log(result.sql);
@@ -84,5 +86,57 @@ console.log(result.params);
 ```
-[ 'WHTE DOG', 2000-01-01T00:00:00.000Z, 2001-01-01T00:00:00.000Z ]
+[ 'WIHTE DOG', 2000-01-01T00:00:00.000Z, 2001-01-01T00:00:00.000Z ]
 ```
+## Getting started
 
+Codding pattern in SQB is very similar to standard sql language.
+
+## Insert statements
+
+SQB supports both Array and Object argument to pass values into Insert statements.
+
+```js
+let statement = sqb.insert('id', 'name', 'address').into('BOOKS')
+    .values([1, 'Hello SQB', 'The Earth']);
+```
+or
+```js
+let statement = sqb.insert('id', 'name', 'address').into('BOOKS')
+    .values({
+      id:-1, 
+      name: 'Hello SQB', 
+      address:'The Earth'
+    });
+
+let result = statement.build();
+```
+result.sql output will be 
+```sql
+insert into BOOKS (id, name, address) values (1, 'Hello SQB', 'The Earth')
+```
+
+## Serialising with parameters
+
+SQB can generate parameter placeholders except serializing values. To do this, just place field names in RegExp pattern.
+
+```js
+let statement = sqb.insert('id', 'name', 'address').into('BOOKS')
+    .values([/id/, /name/, /address/]);
+
+let result = statement.build(
+    {namedParams: false}, 
+    [1, 'Hello SQB', 'The Earth']);
+
+console.log(result.sql);
+console.log(result.params);
+
+```
+result.sql output will be
+```sql
+insert into books (id, name, address) values (?, ?, ?)
+```
+result.params output will be
+```
+[ 1, 'Hello SQB', 'The Earth' ]
+```
+
 ## Node Compatibility
@@ -89,0 +143,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

46723

increased by13.67%

Number of package files

19

increased by5.56%

Lines of code

1353

increased by16.24%

Number of lines in readme file

158

increased by51.92%

No dependency changes