bloom-sql - npm Package Compare versions

Comparing version 0.0.1 to 0.0.2

index.js

		@@ -32,10 +32,3 @@ /jshint node:true globalstrict:true/
		SET: function(set) {
		// build set clause:
		var set_columns = Object.keys(set),
		set_clause = set_columns.map(function(c,i) {
		return c + ' = $' + (i+1);
		}),
		set_values = set_columns.map(function(c){ return set[c]; });
		this.text += ' SET ' + set_clause.join(', ');
		this.values.push.apply(this.values, set_values);
		this.text += ' SET ' + get_set_clause(set, this.values);
		return this;
		@@ -135,9 +128,5 @@ },
		VALUES: function(object) {
		var values = this.values,
		columns = Object.keys(object),
		placeholders = columns.map(function(c,i) {
		values.push(object[c]);
		return '$' + values.length;
		});
		this.text += '('+columns.join(', ')+') VALUES(' + placeholders.join(', ') + ')';
		var columns = Object.keys(object),
		insert_values = get_insert_values(object, columns, this.values);
		this.text += '('+columns.join(', ')+') VALUES(' + insert_values + ')';
		return this;
		@@ -181,2 +170,28 @@ },

		function get_insert_values(object, columns, values) {
		return columns.map(function(c,i) {
		return get_placeholder(object[c], values);
		}).join(', ');
		}


		// used in get_set_clause and get_insert_values...
		function get_placeholder(value, values) {
		if (Array.isArray(value)) {
		return 'ARRAY[' + value.map(function(item){
		return get_placeholder(item, values);
		}).join(', ') + ']'
		} else {
		values.push(value);
		return '$' + values.length;
		}
		}

		// only used in UPDATE, but here for clarity/efficiency
		function get_set_clause(set, values) {
		return Object.keys(set).map(function(c,i){
		return c + ' = ' + get_placeholder(set[c], values);
		}).join(', ');
		}

		// used in UPDATE, SELECT and DELETE
		@@ -186,3 +201,3 @@ // can handle where objects like so:
		function get_where_clause(where, values, conjunction){
		return Object.keys(where).map(function(c,i) {
		return Object.keys(where).map(function column_to_where(c,i) {
		var value = where[c];
		@@ -192,3 +207,3 @@ if (Array.isArray(value)) {
		values.push(v);
		return '$'+values.length;
		return '$' + values.length;
		}).join(', ') + ')';
		@@ -195,0 +210,0 @@ } else if (value === null) {

package.json

		{
		"name": "bloom-sql",
		"version": "0.0.1",
		"version": "0.0.2",
		"description": "Chained functions for building SQL strings for node-postgres.",
		@@ -5,0 +5,0 @@ "main": "index.js",

README.md

		@@ -5,3 +5,3 @@ # What are you?

		It gets tedious matching up `$1, $2` etc. and building the right kind of values array. My functions help you do this and get the place-holder numbers right, even for `UPDATE/SET/WHERE` and for `WHERE IN` clauses which require a flattened array of arguments.
		It gets tedious matching up `$1, $2` etc. and building the right kind of values array. My functions help you do this and get the place-holder numbers right, even for `UPDATE/SET/WHERE` and for `WHERE IN` clauses and Postgres `ARRAY[]` values which require a flattened array of arguments.

		@@ -77,2 +77,5 @@ I have some tests written in Mocha. Of course I could use more!
		* the other 90% of SQL?
		* it would be nice to support Postgres `ARRAY` operators in `WHERE` clauses, but this probably requires a different way of doing `IN` queries. Right now:
		* in `UPDATE` and `INSERT`, a JS Array `[1,2,3]` will be converted into a Postgres `ARRAY[$1,$2,$3]` and the values flattened appropriately for the prepared statement
		* in `WHERE` clauses, a JS Array `[1,2,3]` will be converted into a SQL `IN ($1,$2,$3)` and the values flattened appropriately for the prepared statement

		@@ -79,0 +82,0 @@ # Installation

test/insert.js

+@@ -61,2 +61,12 @@ /*jshint node:true globalstrict:true */
+  });
+  describe('#(table).VALUES(set_with_arrays)', function(){
+    it('should return an object with array placeholders and flattened values', function(){
+      var sql = INSERT('foo').VALUES({ a: [ 1, 2 ], b: [ [ 3, 4 ], [ 5, 6 ] ], c: [ 7, 8 ] });
+      sql.should.have.text;
+      sql.should.have.values;
+      sql.text.should.equal('INSERT INTO foo(a, b, c) VALUES(ARRAY[$1, $2], ARRAY[ARRAY[$3, $4], ARRAY[$5, $6]], ARRAY[$7, $8])');
+      sql.values.should.be.an.instanceOf(Array)
+      sql.values.should.eql([1,2,3,4,5,6,7,8]);
+    });
+  });
+});

test/update.js

		@@ -71,2 +71,22 @@ /jshint node:true globalstrict:true /
		});
		describe('#(table).SET(set_array).WHERE(where_array)', function(){
		it('should identify arrays in the set object and generate correct placeholders and flattened values', function(){
		var sql = UPDATE('foo').SET({ a: 1, b: [ 2, 3, 4 ], c: 5 }).WHERE({ d: 6, e: [ 7, 8 ] });
		sql.should.have.text;
		sql.should.have.values;
		sql.text.should.equal('UPDATE foo SET a = $1, b = ARRAY[$2, $3, $4], c = $5 WHERE d = $6 AND e IN ($7, $8)');
		sql.values.should.be.an.instanceOf(Array)
		sql.values.should.eql([1,2,3,4,5,6,7,8]);
		});
		});
		describe('#(table).SET(set_nested_array).WHERE(where_array)', function(){
		it('should identify nested arrays in the set object and generate correct placeholders and flattened values', function(){
		var sql = UPDATE('foo').SET({ a: 1, b: [ [ 2, 3 ], [ 4, 5 ] ], c: 6 }).WHERE({ d: 7, e: [ 8, 9 ] });
		sql.should.have.text;
		sql.should.have.values;
		sql.text.should.equal('UPDATE foo SET a = $1, b = ARRAY[ARRAY[$2, $3], ARRAY[$4, $5]], c = $6 WHERE d = $7 AND e IN ($8, $9)');
		sql.values.should.be.an.instanceOf(Array)
		sql.values.should.eql([1,2,3,4,5,6,7,8,9]);
		});
		});
		});

bloom-sql - npm Package Compare versions

New alerts

Fixed alerts

Improved metrics