mysql-bricks - npm Package Compare versions

Comparing version 1.0.2 to 1.1.0

index.js

@@ -11,4 +11,22 @@ 'use strict';
 
-// LIMIT
-mysqlBricks.select.prototype.limit = function(val) {
+
+// DELETE / UPDATE ... ORDER BY
+mysqlBricks.delete.prototype.orderBy = mysqlBricks.update.prototype.orderBy = function() {
+    this._orderBy = Array.from(arguments);
+    return this;
+};
+
+let orderByClauseFunction = function() {
+        if(!this._orderBy || this._orderBy.length <= 0 || (this._orderBy.length === 1 && this._orderBy[0].length <= 0)) {
+            return;
+        }
+        return 'ORDER BY ' + this._orderBy.map(col => sql._handleColumn(col, null)).join(', ');
+    };
+
+// '{{#ifNotNull _orderBy}}ORDER BY {{_orderBy}}{{/ifNotNull}}';
+mysqlBricks.delete.defineClause('orderBy', orderByClauseFunction, { after: 'where' });
+mysqlBricks.update.defineClause('orderBy', orderByClauseFunction, { after: 'where' });
+
+// SELECT / UPDATE / DELETE ... LIMIT
+mysqlBricks.select.prototype.limit = mysqlBricks.delete.prototype.limit = mysqlBricks.update.prototype.limit = function(val) {
     this._limit = val;
@@ -18,9 +36,8 @@ return this;
 
-mysqlBricks.select.defineClause(
-    'limit',
-    '{{#ifNotNull _limit}}LIMIT {{_limit}}{{/ifNotNull}}',
-    { after: 'orderBy' }
-);
+let limitClause = '{{#ifNotNull _limit}}LIMIT {{_limit}}{{/ifNotNull}}';
+mysqlBricks.select.defineClause('limit', limitClause, { after: 'orderBy' });
+mysqlBricks.update.defineClause('limit', limitClause, { after: 'orderBy' });
+mysqlBricks.delete.defineClause('limit', limitClause, { after: 'orderBy' });
 
-// OFFSET
+// SELECT ... OFFSET
 mysqlBricks.select.prototype.offset = function(val) {
@@ -37,3 +54,3 @@ this._offset = val;
 
-// ON DUPLICATE KEY UPDATE
+// INSERT ... ON DUPLICATE KEY UPDATE
 mysqlBricks.insert.prototype.onDuplicateKeyUpdate = function(cols) {
@@ -57,2 +74,17 @@ this._onDuplicateColumns = cols;
 
+// INSERT IGNORE ...
+mysqlBricks.insert.prototype.ignore = function() {
+    this._insertIgnore = true;
+    return this;
+};
+
+mysqlBricks.insert.defineClause('ignore', '{{#if _insertIgnore}}IGNORE{{/if}}', { after: 'insert' });
+
+
+// TODO: The ORDER BY and LIMIT clauses of the UPDATE and DELETE statements + tests
+// TODO: extend on duplicate key update - allow expression
+// Replace ?
+
+
+
 module.exports = mysqlBricks;

package.json

 {
   "name": "mysql-bricks",
-  "version": "1.0.2",
+  "version": "1.1.0",
   "description": "sql-bricks extension for MySQL",
@@ -5,0 +5,0 @@ "main": "index.js",

README.md

@@ -19,3 +19,3 @@ # mysql-bricks
 
-#### INSERT ... ON DUPLICATE UPDATE ...
+#### INSERT ... ON DUPLICATE KEY UPDATE ...
 
@@ -35,5 +35,19 @@ ```javascript
 
-#### LIMIT
+#### INSERT IGNORE ...
 
 ```javascript
+let values = [[123, 'Daniel', 41, 92], [456, 'David', 34, 87], [789, 'Rachel', 22, 98]];  
+let query = sql.insert('main.some_table', 'id', 'name', 'age', 'grade')
+                .ignore()
+                .values(values)
+                .toString();
+/*                
+INSERT IGNORE INTO main.some_table (id, name, age, grade)
+VALUES (123, \'Moshe\', 41, 92),  (456, \'David\', 34, 87), (789, \'Rachel\', 22, 98)
+*/
+```
+
+#### LIMIT (SELECT / UPDATE / DELETE)
+
+```javascript
 let query = sql.select().from('main.some_table').limit(100).toString();
@@ -45,2 +59,11 @@ /*
 
+MySQL supports optional 'limit' clause in UPDATE and DELETE queries:
+
+```javascript
+let query = sql.delete('main.some_table').limit(100).toString();
+/*                
+DELETE FROM main.some_table LIMIT 100
+*/
+```
+
 #### OFFSET
@@ -55,3 +78,17 @@
 
+#### ORDER BY (UPDATE / DELETE)
 
+MySQL supports optional 'order by' clause in UPDATE and DELETE queries:
+
+```javascript
+let query = sql.update('main.some_table', { grade: 90})
+                .where(sql.gt('grade', 90))
+                .orderBy('age desc')
+                .limit(100)
+                .toString();
+/*                
+UPDATE main.some_table SET grade = 90 WHERE grade > 90 ORDER BY age desc LIMIT 100
+*/
+```
+
 ## Contribute
@@ -58,0 +95,0 @@

tests.js

@@ -93,2 +93,17 @@ 'use strict';
 
+        it("should return correct insert query when using 'ignore'", function () {
+
+            let values = [[123, 'Moshe', 41, 92], [456, 'David', 34, 87], [789, 'Rachel', 22, 98]];
+
+            let expectedQuery = 'INSERT IGNORE INTO main.some_table (id, name, age, grade) ' +
+                'VALUES (123, \'Moshe\', 41, 92),  (456, \'David\', 34, 87), (789, \'Rachel\', 22, 98)';
+
+            let actualQuery = sql.insert('main.some_table', 'id', 'name', 'age', 'grade')
+                .ignore()
+                .values(values)
+                .toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
         it("should quote reserved words with grave accent (backtick)", function () {
@@ -147,2 +162,124 @@
     });
+
+    describe("delete", function() {
+
+        it("should return correct delete query with limit", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table LIMIT 100';
+
+            let actualQuery = sql.delete('main.some_table').limit(100).toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct delete query with where and limit", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table WHERE id > 200 LIMIT 100';
+
+            let actualQuery = sql.delete('main.some_table').where(sql.gt('id', 200)).limit(100).toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct delete query with order by when passing columns as array", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table ORDER BY name,age desc,grade';
+
+            let actualQuery = sql.delete('main.some_table').orderBy(['name', 'age desc', 'grade']).toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct delete query with order by when passing columns as separate arguments", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table ORDER BY name, age desc, grade';
+
+            let actualQuery = sql.delete('main.some_table').orderBy('name', 'age desc', 'grade').toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct delete query without order by when passing null", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table';
+
+            let actualQuery = sql.delete('main.some_table').orderBy().toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct delete query without order by when passing empty array", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table';
+
+            let actualQuery = sql.delete('main.some_table').orderBy([]).toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct delete query without order by when passing empty string", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table';
+
+            let actualQuery = sql.delete('main.some_table').orderBy('').toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct delete query with order by and limit", function () {
+
+            let expectedQuery = 'DELETE FROM main.some_table ORDER BY name, age desc, grade LIMIT 100';
+
+            let actualQuery = sql.delete('main.some_table')
+                .orderBy('name', 'age desc', 'grade')
+                .limit(100)
+                .toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+    })
+
+    describe("update", function() {     // not repeating similar tests for limit and order by in "delete" class
+
+        it("should return correct update query with limit clause", function () {
+
+            let expectedQuery = 'UPDATE main.some_table SET grade = 90 LIMIT 100';
+
+            let actualQuery = sql.update('main.some_table', { grade: 90}).limit(100).toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct update query with where, limit clauses", function () {
+
+            let expectedQuery = 'UPDATE main.some_table SET grade = 90 WHERE grade > 90 LIMIT 100';
+
+            let actualQuery = sql.update('main.some_table', { grade: 90}).where(sql.gt('grade', 90)).limit(100).toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct update query with order by clause", function () {
+
+            let expectedQuery = 'UPDATE main.some_table SET grade = 90 ORDER BY age desc';
+
+            let actualQuery = sql.update('main.some_table', { grade: 90}).orderBy('age desc').toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+
+        it("should return correct update query with where, order by, limit clauses", function () {
+
+            let expectedQuery = 'UPDATE main.some_table SET grade = 90 WHERE grade > 90 ORDER BY age desc LIMIT 100';
+
+            let actualQuery = sql.update('main.some_table', { grade: 90})
+                .where(sql.gt('grade', 90))
+                .orderBy('age desc')
+                .limit(100)
+                .toString();
+
+            return expect(actualQuery).to.equal(expectedQuery.replace(/\n/g, ' ').replace(/\s\s+/g, ' '));
+        });
+        
+    });
 });

No alert changes

Improved metrics

Total package byte prevSize

19571

75.29%

Lines of code

242

74.1%

Number of lines in readme file

96

62.71%

No dependency changes