monologue - npm Package Compare versions

Comparing version 0.2.10 to 0.3.0

monologue.js

@@ -1,7 +0,19 @@
-(function() {
-	var rx = /[^a-zA-Z0-9_]/g,
-		root = this;
+(function(exports) {
+	"use strict";
 
 	function monologue(opt) {
-		opt = opt || { inline: false };
+		var dict = { escape: true, quote: false };
+
+		if( typeof opt === "undefined" ) {
+			opt = dict;
+		}
+		else {
+			for( var i in dict ) {
+				opt[i] = ( typeof opt[i] === "undefined" ? dict[i] : opt[i] );
+			}
+		}
+
+		// hard-coded prevention for now
+		opt.quote = false;
+
 		// semi-global object to contain query parts until they are compiled
@@ -19,3 +31,2 @@ var global = {
 			itr: 0,
-			inline: opt.inline
 		};
@@ -27,5 +38,25 @@
 
+
 			/**
+			 * resets the global container object
 			 */
 
+			reset: function() {
+				global = {
+					query: '',
+					join: [],
+					where: '',
+					having: '',
+					order: [],
+					group: [],
+					limit: '',
+					last: '',
+					columns: null,
+					itr: 0,
+				};
+			},
+
+			/**
+			 */
+
 			select: function( c, t ) {
@@ -42,9 +73,11 @@ if( Array.isArray( c ) )
 			/**
+			 * direction, table, fields
 			 */
 
 			join: function( dir, t, f ) {
+				// default to left join if unspecified
 				if( typeof f === "undefined" ) {
 					f = t;
 					t = dir;
-					dir = "LEFT";
+					dir = "INNER";
 				}
@@ -59,3 +92,2 @@
 					f = fields.join(" AND ");
-					delete fields;
 				}
@@ -79,3 +111,3 @@
 					// if it's not a nested array, cheat and make it one
-					if( toString.call( p ) !== "[object Array]" ) {
+					if( ! Array.isArray( p ) ) {
 						p = [p];
@@ -85,4 +117,3 @@ }
 					c = this.stringify( p, "");
-					c = "(" + c.shift() + ") VALUES "
-						+ c.join(',');
+					c = "(" + c.shift() + ") VALUES " + c.join(',');
 				}
@@ -177,7 +208,15 @@
 				// statement together is all that needs to be done here
-				var k = "l_" + like.replace(rx, '');
-				this.params[k] = like;
-				like = " LIKE :" + k;
+				if( opt.escape ) {
+					like = " LIKE " + this.escape(like);
+				}
 
-				return this.where( like, "" );
+				else {
+					var k = "l_" + like.replace(/[^a-zA-Z0-9_]/g, '');
+					this.params[k] = like;
+					like = " LIKE :" + k;
+				}
+
+				global.where += like;
+
+				return this;
 			},
@@ -190,10 +229,24 @@
 			between: function( one, two ) {
-				// create unique field names for each value
-				var k1 = "b_" + one.replace(rx, "");
-				var k2 = "b_" + two.replace(rx, "");
+				var between = '';
 
-				this.params[k1] = one;
-				this.params[k2] = two;
+				if( opt.escape ) {
+					between = " BETWEEN " + this.escape(one) + " AND "
+						+ this.escape(two);
+				}
 
-				return this.where( " BETWEEN :" + k1 + " AND :" + k2, '' );
+				else {
+
+					// create unique field names for each value
+					var k1 = "b_" + one.replace(/[^a-zA-Z0-9_]/g, "");
+					var k2 = "b_" + two.replace(/[^a-zA-Z0-9_]/g, "");
+
+					this.params[k1] = one;
+					this.params[k2] = two;
+
+					between = " BETWEEN :" + k1 + " AND :" + k2
+				}
+
+				global.where += between;
+
+				return this;
 			},
@@ -209,3 +262,4 @@
 
-				if( toString.call( g ) === "[object Array]" ) g = g.join( ',' );
+				if( Array.isArray( g ) )
+					g = g.join( ', ' );
 
@@ -249,3 +303,4 @@ global.group.push( g + " " + d );
 
-				if( toString.call( o ) === "[object Array]" ) o = o.join( ',' );
+				if( Array.isArray( o ) )
+					o = o.join( ', ' );
 
@@ -263,3 +318,5 @@ global.order.push( o + " " + d );
 			limit: function( l, o ) {
-				global.limit = ( typeof o === "undefined" ? l.toString() : o + ", " + l );
+				global.limit = ( typeof o === "undefined"
+					? '' + l
+					: o + ", " + l );
 				return this;
@@ -269,2 +326,16 @@ },
 
+			union: function( c, t ) {
+				if( Array.isArray( c ) )
+					c = c.join( ", " );
+
+				var sql = this.query().sql;
+
+				this.reset();
+
+				global.query = sql += " UNION SELECT " + c + " FROM " + t;
+
+				return this;
+			},
+
+
 			/**
@@ -333,4 +404,10 @@ * f: file path, t: field terminator, e: field enclosure,
 								global.columns = Object.keys( p[0] ).sort();
-								c.push( "`" + global.columns.join('`, `') + "`" );
+
+								var ret = ( opt.quote
+									? "`" + global.columns.join('`, `') + "`"
+									: global.columns.join(', ') )
+
+								c.push( ret );
 							}
+
 							c.push( "(" + this.stringify( p[ii], "" ) + ")");
@@ -348,5 +425,12 @@ }
 					var col = global.columns || Object.keys( p ).sort();
+
 					for( var jj = 0, len = col.length; jj < len; ++jj ) {
+						// matching a column to a set, i.e. {id: [1,2,3,4]}
 						if( Array.isArray( p[col[jj]] ) ) {
-							c.push( col[jj] + " IN (" + this.stringify( p[col[jj]] ) + ")" );
+							var n = ( opt.quote
+								? '`' + col[jj] + '`'
+								: col[jj] )
+								+ " IN (" + this.stringify( p[col[jj]] ) + ")";
+
+							c.push( n );
 						}
@@ -371,3 +455,3 @@
 
-				if( global.inline ) {
+				if( opt.escape ) {
 					r = this.escape(v);
@@ -384,2 +468,6 @@ }
 
+				if( opt.quote ) {
+					k = '`' + k + '`';
+				}
+
 				// spit out the bound param name
@@ -390,2 +478,7 @@ return ( s.length > 0 ? "" + k + " " + s + " " : '' ) + r;
 
+
+			/**
+			 * Escape unsafe characters to avoid sql injection
+			 */
+
 			escape: function(v) {
@@ -418,2 +511,4 @@ if (v === undefined || v === null) {
 
+	// exports = monologue;
+
 	if( typeof module !== "undefined" && module.exports ) {
@@ -423,5 +518,5 @@ module.exports = monologue;
 	else {
-		root.monologue = monologue;
+		window.monologue = monologue;
 	}
 
-})();
+})( typeof window === 'undefined' ? module.exports : window );

package.json

 {
-	"name": "monologue",
-	"version": "0.2.10",
-	"description": "Streamlined MySQL query building",
-	"main": "./monologue.js",
-	"author": {
-		"name": "Ross Paskett",
-		"email": "[e-mail]"
-	},
-	"keywords": [
-		"mysql", "query"
-	],
-	"repository": {
-		"type": "git",
-		"url": "git://github.com/chocolatetoothpaste/monologue"
-	}
-}
+  "name": "monologue",
+  "version": "0.3.0",
+  "description": "Streamlined MySQL query building",
+  "main": "./monologue.js",
+  "author": {
+    "name": "Ross Paskett",
+    "email": "[e-mail]"
+  },
+  "keywords": [
+    "mysql",
+    "query",
+    "sql",
+    "builder"
+  ],
+  "repository": {
+    "type": "git",
+    "url": "git://github.com/chocolatetoothpaste/monologue"
+  },
+  "devDependencies": {
+    "nodeunit": "^0.9.0"
+  }
+}

README.md

 Monologue - Streamlined query building
 ======================================
 
+**Breaking Changes for v0.3.0**
+
+Monologue now defaults to sanitizing strings inline as opposed to creating bound parameters.  This option can be changed by passing { escape: false } into monologue().
+
+The join function now defaults to INNER JOIN for parity with MYSQLs defaults
+
 **Install**
@@ -10,4 +16,2 @@
 
-This was ported from a PHP library, and uses named parameters for binding (PDO library). Some mysql packages in node support this, see their documentation for examples. This package will continue to evolve and support other methods in the future.
-
     var monologue = require('monologue');
@@ -22,3 +26,3 @@
         .where( 'date_time' ).between( '2012-09-12', '2013-01-20')
-        .group( 'type' )
+        .group( ['type', 'hamster' ] )
         .where( "name", "OR" ).like("ro%en") // out of order, also passing "OR" as separator
@@ -29,24 +33,9 @@ .order( "id" )
 
-    console.log( mono.sql )
-    // output: SELECT * FROM users WHERE id IN (:i_1,:i_2,:i_3,:i_4,:i_5,:i_6) AND username = :mono_someguy AND email = :mono_someguyexampleorg OR email = :mono_someguygmailcom AND date BETWEEN :b_20120912 AND :b_20130121 AND name LIKE :l_roen OR name LIKE :l_bb GROUP BY type ASC ORDER BY id ASC LIMIT 1000, 300
+    console.log( mono.sql );
+    // output: SELECT * FROM users WHERE id IN (1,2,3,4,5,6) AND date_time BETWEEN '2012-09-12' AND '2013-01-20' OR name LIKE 'ro%en' GROUP BY type, hamster ASC ORDER BY id ASC LIMIT 1000, 300
 
-    console.log( mono.params );
-    /* output:
-        {
-            b_20120912: "2012-09-12",
-            b_20130121: "2013-01-20",
-            i_1: 1,
-            i_2: 2,
-            i_3: 3,
-            i_4: 4,
-            i_5: 5,
-            i_6: 6,
-            l_roen: "ro%en"
-        }
-    */
 
+    // JOIN (default is inner):
+    // SELECT * FROM users u INNER JOIN posts p ON p.user_id = u.id WHERE category = '67'
 
-    // JOIN (default is left):
-    // SELECT * FROM users u LEFT JOIN posts p ON p.user_id = u.id WHERE category = :mono_67
-
     monologue().select( "*", "users u" )
@@ -58,7 +47,7 @@ .join( "posts p", "p.user_id = u.id" )
 
-    // JOIN (INNER, as argument):
-    // SELECT * FROM users u INNER JOIN posts p ON p.user_id = u.id WHERE category = :mono_67
+    // JOIN (LEFT, as argument):
+    // SELECT * FROM users u LEFT JOIN posts p ON p.user_id = u.id WHERE category = '67'
 
     monologue().select( "*", "users u" )
-        .join( "INNER", "posts p", { "p.user_id": "u.id" } )
+        .join( "LEFT", "posts p", { "p.user_id": "u.id" } )
         .where( { "category": "67" } )
@@ -69,4 +58,5 @@ .query().sql;
     // SELECT into outfile: the third param (OPTIONALLY ENCLOSED BY) is, as stated, optional. Just pass in the line ending and leave the 4th param out, the rest will be taken care of
-    // output: SELECT * FROM users WHERE company = :mono_generalmotors INTO OUTFILE '/tmp/datafile' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
+    // output: SELECT * FROM users WHERE company = 'general motors' INTO OUTFILE '/tmp/datafile' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
 
+
     monologue().select( "*", "users" )
@@ -79,3 +69,3 @@ .where( { "company": "general motors" } )
     // SELECT into outfile: without third param
-    // output: SELECT * FROM users WHERE company = :mono_generalmotors INTO OUTFILE '/tmp/datafile' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
+    // output: SELECT * FROM users WHERE company = 'general motors' INTO OUTFILE '/tmp/datafile' FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'
 
@@ -89,25 +79,31 @@ monologue().select( "*", "users")
     // INSERT, passing an array of objects
-    // output: INSERT INTO users (username,password,first_name) VALUES (:mono_test,:mono_1234,:mono_me),(:mono_example,:mono_abcd,:mono_rasta)
+    // output: INSERT INTO users (first_name, password, username) VALUES ('me','1234','test'),('pasta','abcd','example')
 
     monologue().insert( 'users', [
         { username: 'test', password: '1234', first_name: 'me' },
-        { username: 'example', password: 'abcd', first_name: "rasta" }
-    ] ).query().sql
+        { username: 'example', password: 'abcd', first_name: "pasta" }
+    ] ).query().sql;
 
 
     // INSERT, passing a single object
-    // output: INSERT INTO users (username,password,first_name) VALUES (:mono_me,:mono_abcd,:mono_cubert)
+    // output: INSERT INTO users (first_name, password, username) VALUES ('cubert','abcd','me')
 
-    monologue().insert( 'users', { username: 'me', password: 'abcd', first_name: "cubert" } ).query().sql
+    monologue().insert( 'users', { username: 'me', password: 'abcd', first_name: "cubert" } ).query().sql;
 
 
     // UPDATE
-    // output: UPDATE users SET username = :mono_yoyo, email = :mono_kay, password = :mono_abcdefg WHERE id = :mono_23
+    // output: UPDATE users SET email = '[e-mail]', password = 'abcdefg', username = 'yoyo' WHERE id = 23
 
-    monologue().update( "users", {username: "yoyo", email: '[e-mail]', password: "abcdefg"} ).where( {id: 23} ).query().sql
+    monologue().update( "users", {username: "yoyo", email: '[e-mail]', password: "abcdefg"} ).where( {id: 23} ).query().sql;
 
 
     // DELETE
-    // output: DELETE FROM users WHERE username = :mono_test AND password = :mono_1234 AND first_name = :mono_me
+    // output: DELETE FROM users WHERE first_name = 'me' AND password = '1234' AND username = 'test'
 
-    monologue().delete( 'users', { username: 'test', password: '1234', first_name: "me" } ).query().sql;
+    monologue().delete( 'users', { username: 'test', password: '1234', first_name: "me" } ).query().sql;
+
+    // UNION
+    // Wrappers can be out of order BEFORE the UNION statement,
+    // wrappers after will be applied to the secondary statment
+    // output: SELECT username, email FROM users WHERE company_id = '1234' UNION SELECT screename, email_address FROM app_users WHERE company = 'coName'
+    monologue().select('username, email', 'users').where({"company_id": "1234"}).union('screename, email_address', 'app_users').where({"company":"coName"}).query().sql

No alert changes

Improved metrics

Total package byte prevSize

40578

increased by221.36%

Number of package files

6

increased by50%

Lines of code

369

increased by22.19%

Worsened metrics

Dev dependency count

1

increased byInfinity%

Number of lines in readme file

102

decreased by-3.77%

No dependency changes