monologue - npm Package Compare versions

Comparing version 0.1.2 to 0.1.3

monologue.js

@@ -10,3 +10,4 @@ (function() {
 			group: [],
-			limit: ''
+			limit: '',
+			last: ''
 		};
@@ -25,4 +26,6 @@
 			group: [],
-			limit: ''
+			limit: '',
+			last: ''
 		};
+
 		return new Monologue;
@@ -47,3 +50,4 @@ }
 
-		c = ( typeof c === "string" ? c : c.join(", ") );
+		if( toString.call( c ) === "[object Array]" )
+			c = c.join( ", " );
 
@@ -59,2 +63,11 @@ qparts.query = "SELECT " + c + " FROM " + t;
 
+	Monologue.prototype.left = function( t, f ) {
+		qparts.query += " LEFT JOIN " + t + " ON " + f;
+		return this;
+	}
+
+
+	/**
+	 */
+
 	Monologue.prototype.insert = function( table, params ) {
@@ -68,3 +81,3 @@ this.reset();
 			// if it's not a multidimensional array, cheat and make it one
-			if( toString.call(params) !== "[object Array]" ) {
+			if( toString.call( params ) !== "[object Array]" ) {
 				params = [params];
@@ -74,5 +87,2 @@ }
 			columns = this.stringify( params, "");
-
-			// console.log(columns);
-			// columns = "VALUES " + columns.join();
 			columns = " (" + columns.shift() + ") VALUES " + columns.join(',');
@@ -160,32 +170,7 @@ }
 
-	Monologue.prototype.having = function( h, separator ) {
-		separator = ( typeof separator === "undefined" ? "AND" : separator );
+	Monologue.prototype.in = function( ins ) {
+		var i = this.stringify( ins, '', 'i_');
 
-		if( typeof h !== "string" ) {
-			var criteria = this.stringify(h);
-
-			// stringify the having statements
-			h = criteria.join( " " + separator + " " );
-		}
-
-		// check if a previous where statement has been set and glue it all together
-		qparts.having = ( qparts.having.length > 0
-			? qparts.having + " " + separator + " " + h
-			: h );
-
-		return this;
-	}
-
-
-	/**
-	 */
-
-	Monologue.prototype.in = function( ins, field ) {
-		field = field || "";
-		var i = this.stringify( [ins], '', '__in_');
-
-		i = " IN (" + i.join(",") + ")";
-
 		// returns "this"
-		return this.where( i, "" );
+		return this.where( " IN (" + i.join( "," ) + ")", "" );
 	}
@@ -202,3 +187,3 @@
 		// statement together is all that needs to be done here
-		var k = "__like_" + like.replace(rx, '');
+		var k = "l_" + like.replace(rx, '');
 		this.params[k] = like;
@@ -217,4 +202,4 @@ like = " LIKE :" + k;
 		// create unique field names for each value
-		var k1 = "__between_" + one.replace(rx, "");
-		var k2 = "__between_" + two.replace(rx, "");
+		var k1 = "b_" + one.replace(rx, "");
+		var k2 = "b_" + two.replace(rx, "");
 
@@ -240,2 +225,24 @@ this.params[k1] = one;
 		return this;
+	};
+
+
+	/**
+	 */
+
+	Monologue.prototype.having = function( h, separator ) {
+		separator = ( typeof separator === "undefined" ? "AND" : separator );
+
+		if( typeof h !== "string" ) {
+			var criteria = this.stringify(h);
+
+			// stringify the having statements
+			h = criteria.join( " " + separator + " " );
+		}
+
+		// check if a previous where statement has been set and glue it all together
+		qparts.having = ( qparts.having.length > 0
+			? qparts.having + " " + separator + " " + h
+			: h );
+
+		return this;
 	}
@@ -271,2 +278,19 @@
 	/**
+	 */
+
+	Monologue.prototype.file = function( f, t, e, l ) {
+		if( typeof l === "undefined" ) {
+			l = e;
+			e = undefined;
+		}
+
+		qparts.last += " INTO OUTFILE '" + f + "' FIELDS TERMINATED BY '"
+			+ t + "' " + ( e ? "OPTIONALLY ENCLOSED BY '" + e + "'" : '' )
+			+ " LINES TERMINATED BY '" + l + "'";
+
+		return this;
+	};
+
+
+	/**
 	 * Compile each part together and generate a valid SQL statement
@@ -287,2 +311,4 @@ */
 			qparts.query += " LIMIT " + qparts.limit;
+		if( qparts.last.length > 0 )
+			qparts.query += qparts.last;
 
@@ -296,23 +322,27 @@ this.sql = qparts.query;
 	/**
-	 * s: serator
+	 * p: params, s: separator, pre: bound param prefix
 	 */
 
-	Monologue.prototype.stringify = function( params, s, pre ) {
+	Monologue.prototype.stringify = function( p, s ) {
 		s = ( typeof s === "undefined" ? "=" : s );
-		pre = pre || '__eq_';
-		var columns = [],
-			type = toString.call( params );// === "[object Array]";
+		var c = [],
+			type = toString.call( p );
 
 		if( type === "[object Array]" ) {
-			for( var ii = 0, l = params.length; ii < l; ++ii ) {
-				if( toString.call( params[ii] ) === "[object Object]" ) {
-					// if columns is empty, then push the actual column names first
-					if( columns.length === 0 ) {
-						columns.push( Object.keys( params[ii] ) );
-					}
+			for( var ii = 0, l = p.length; ii < l; ++ii ) {
 
-					columns.push( "(" + this.stringify( params[ii], "" ) + ")");
+				// if parent is an array and child is an object, generate an
+				// encapsulated list of values (for insert statements)
+				if( toString.call( p[ii] ) === "[object Object]" ) {
+
+					// if "c" is empty, then push the actual column names
+					if( c.length === 0 ) c.push( Object.keys( p[ii] ) );
+
+					c.push( "(" + this.stringify( p[ii], "" ) + ")");
 				}
+
 				else {
-					columns.push( this.stringify( params[ii], s, pre ) );
+
+					// generate a comma-separated list of fields
+					c.push( this.format( p[ii], ii, "" ) );
 				}
@@ -322,11 +352,10 @@ }
 
-		else if( type === "[object Object]" ) {
-			for( k in params ) {
-				if( toString.call( params[k] ) === "[object Array]" ) {
-					columns.push( k
-						+ " IN (" + this.stringify( params[k], "", "__in_" )
-						+ ")" );
+		else {
+			for( var jj in p ) {
+				if( toString.call( p[jj] ) === "[object Array]" ) {
+					c.push( jj + " IN (" + this.stringify( p[jj] ) + ")" );
 				}
+
 				else {
-					columns.push( this.stringify( params[k], s, pre ) );
+					c.push( this.format( p[jj], jj, s ) );
 				}
@@ -336,11 +365,18 @@ }
 
-		else {
-			var i = pre + params.toString().replace(rx, "");
-			var v = ( s.length > 0 ? k + " " + s + " " : '' ) + ":" + i;
-			this.params[i] = params;
+		return c;
+	}
 
-			columns.push( v );
-		}
 
-		return columns;
+	/**
+	 */
+
+	Monologue.prototype.format = function( v, k, s ) {
+		// strip out non-alpha characters (makes parsers choke)
+		var push = v.toString().replace( rx, "" );
+
+		// add value to the param stack
+		this.params[push] = v;
+
+		// spit out the bound param name
+		return ( s.length > 0 ? k + " " + s + " " : '' ) + ":e_" + push;
 	}
@@ -362,5 +398,7 @@
 			group: [],
-			limit: ''
+			limit: '',
+			last: ''
 		};
 	}
+
 })();

package.json

 {
 	"name": "monologue",
-	"version": "0.1.2",
+	"version": "0.1.3",
 	"description": "Streamlined MySQL query building",
@@ -5,0 +5,0 @@ "main": "./monologue.js",

README.md

@@ -18,34 +18,41 @@ Monologue - Streamlined query building
 
+    // Less than basic SELECT statement
+    // output: SELECT * FROM users WHERE id IN (:i_1,:i_2,:i_3,:i_4,:i_5,:i_6) AND username = :e_someguy AND email = :e_someguyexampleorg OR email = :e_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
+
     // call the SQL wrappers in any order, see below: where, group, where, order
     mono.select( "*", "users")
-        .where( { "id": [1,2,3,4,5,6,6,6,6,6] } ) // alternative to where("id").in([...])
-        .where('date_time').between( '2012-09-12', '2013-01-20')
-        .group('type')
+        .where( { "id": [1,2,3,4,5,6] } ) // alternative to where("id").in([...])
+        .where( 'date_time' ).between( '2012-09-12', '2013-01-20')
+        .group( 'type' )
         .where( "name", "OR" ).like("ro%en") // out of order, also passing "OR" as separator
-        .order("id")
-        .limit('300', 1000)
-        .query();
+        .order( "id" )
+        .limit( '300', 1000 )
+        .query().sql;
 
-    // Now pass query (or the var you assign, see above) and mono.params into your MySQL querying function!
-
-    console.log( mono.sql ); // output: SELECT * FROM users WHERE id IN (:__in_1,:__in_2,:__in_3,:__in_4,:__in_5,:__in_6) AND date_time BETWEEN :__between_20120912 AND :__between_20130121 OR name LIKE :__like_ro GROUP BY type ASC ORDER BY id ASC LIMIT 1000, 300
-
     console.log( mono.params );
     /* output:
         {
-            __between_20120912: "2012-09-12",
-            __between_20130121: "2013-01-21",
-            __in_1: 1,
-            __in_2: 2,
-            __in_3: 3,
-            __in_4: 4,
-            __in_5: 5,
-            __in_6: 6,
-            __like_roen: "ro%en"
+            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"
         }
     */
 
-    // Insert, passing an array of objects
-    // output: INSERT INTO users (username,password,first_name) VALUES (:__eq_test,:__eq_1234,:__eq_me),(:__eq_example,:__eq_abcd,:__eq_rasta)
+    // 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 = :e_generalmotors INTO OUTFILE '/tmp/datafile' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
 
+    monologue.select( "*", "users")
+    .where( { "company": "general motors" } ) // alternative to where("id").in([...])
+    .file( "/tmp/datafile", ",", '"', "\\n" )
+    .query();
+
+    // INSERT, passing an array of objects
+    // output: INSERT INTO users (username,password,first_name) VALUES (:e_test,:e_1234,:e_me),(:e_example,:e_abcd,:e_rasta)
+
     monologue.insert( 'users', [
@@ -57,4 +64,4 @@ { username: 'test', password: '1234', first_name: 'me' },
 
-    // Insert, passing a single object
-    // output: INSERT INTO users (username,password,first_name) VALUES (:__eq_me,:__eq_abcd,:__eq_cubert)
+    // INSERT, passing a single object
+    // output: INSERT INTO users (username,password,first_name) VALUES (:e_me,:e_abcd,:e_cubert)
 
@@ -64,11 +71,11 @@ monologue.insert( 'users', { username: 'me', password: 'abcd', first_name: "cubert" } ).query().sql
 
-    // Update
-    // output: UPDATE users SET username = :__eq_yoyo, email = :__eq_kay, love = :__eq_toby WHERE id = :__eq_23
+    // UPDATE
+    // output: UPDATE users SET username = :e_yoyo, email = :e_kay, password = :e_abcdefg WHERE id = :e_23
 
-    monologue.update( "users", {username: "yoyo", email: 'kay', love: "toby"} ).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 id = :__eq_23 AND username = :__eq_test AND password = :__eq_1234 AND first_name = :__eq_me
+    // DELETE
+    // output: DELETE FROM users WHERE username = :e_test AND password = :e_1234 AND first_name = :e_me
 
     monologue.delete( 'users', { username: 'test', password: '1234', first_name: "me" } ).query().sql;

No alert changes

Improved metrics

Total package byte prevSize

11827

increased by9.41%

Lines of code

295

increased by8.06%

Number of lines in readme file

78

increased by9.86%

No dependency changes