sql-ddl-sync - npm Package Compare versions

Comparing version 0.1.0 to 0.1.1

lib/Dialects/mysql.js

@@ -1,3 +0,9 @@
-var SQL = require("../SQL");
+var SQL  = require("../SQL");
+var util = require("util");
 
+var columnSizes = {
+	integer:  { 2: 'SMALLINT', 4: 'INTEGER', 8: 'BIGINT' },
+	floating: {                4: 'FLOAT',   8: 'DOUBLE' }
+};
+
 exports.hasCollection = function (db, name, cb) {
@@ -15,3 +21,3 @@ db.query("SHOW TABLES LIKE ?", [ name ], function (err, rows) {
 
-		var columns = {};
+		var columns = {}, m;
 
@@ -26,4 +32,3 @@ for (var i = 0; i < cols.length; i++) {
 
-			var m = cols[i].Type.match(/^(.+)\((\d+)\)$/);
-
+			m = cols[i].Type.match(/^(.+)\((\d+)\)$/);
 			if (m) {
@@ -46,13 +51,32 @@ cols[i].Size = parseInt(m[2], 10);
 			}
+			if (cols[i].Default !== null) {
+				column.defaultValue = cols[i].Default;
+			}
 
 			switch (cols[i].Type.toUpperCase()) {
 				case "SMALLINT":
+				case "INTEGER":
+				case "BIGINT":
 				case "INT":
-				case "BIGINT":
 					column.type = "number";
 					column.rational = false;
-					if (cols[i].Size) {
-						column.size = cols[i].Size;
+					column.size = 4; // INT
+					for (var k in columnSizes.integer) {
+						if (columnSizes.integer[k] == cols[i].Type.toUpperCase()) {
+							column.size = k;
+							break;
+						}
 					}
 					break;
+				case "FLOAT":
+				case "DOUBLE":
+					column.type = "number";
+					column.rational = true;
+					for (var k in columnSizes.floating) {
+						if (columnSizes.floating[k] == cols[i].Type.toUpperCase()) {
+							column.size = k;
+							break;
+						}
+					}
+					break;
 				case "TINYINT":
@@ -66,6 +90,12 @@ if (cols[i].Size == 1) {
 					break;
-				case "FLOAT":
-					column.type = "number";
-					column.rational = true;
+				case "DATETIME":
+					column.time = true;
+				case "DATE":
+					column.type = "date";
 					break;
+				case "LONGBLOB":
+					column.big = true;
+				case "BLOB":
+					column.type = "binary";
+					break;
 				case "VARCHAR":
@@ -78,2 +108,8 @@ column.type = "text";
 				default:
+					m = cols[i].Type.match(/^enum\('(.+)'\)$/);
+					if (m) {
+						column.type = "enum";
+						column.values = m[1].split(/'\s*,\s*'/);
+						break;
+					}
 					return cb(new Error("Unknown column type '" + cols[i].Type + "'"));
@@ -120,9 +156,26 @@ }
 
-exports.escapeId = function () {
-	return Array.prototype.slice.apply(arguments).map(function (el) {
-		return "`" + el.replace(/`/g, '``') + "`";
-	}).join(".");
+exports.getCollectionIndexes = function (db, name, cb) {
+	db.query("SHOW INDEX FROM ??", [ name ], function (err, rows) {
+		if (err) return cb(err);
+
+		return cb(null, convertIndexRows(rows));
+	});
 };
 
-exports.getType = function (property) {
+exports.addIndex = function (db, name, collection, columns, cb) {
+	return db.query(SQL.CREATE_INDEX({
+		name       : name,
+		collection : collection,
+		columns    : columns
+	}, exports), cb);
+};
+
+exports.removeIndex = function (db, name, collection, cb) {
+	return db.query(SQL.DROP_INDEX({
+		name       : name,
+		collection : collection
+	}, exports), cb);
+};
+
+exports.getType = function (collection, name, property) {
 	var type = false;
@@ -132,9 +185,13 @@
 		case "text":
-			type = "VARCHAR(" + (property.size || 255) + ")";
+			if (property.big) {
+				type = "LONGTEXT";
+			} else {
+				type = "VARCHAR(" + Math.min(Math.max(parseInt(property.size, 10) || 255, 1), 65535) + ")";
+			}
 			break;
 		case "number":
 			if (property.rational) {
-				type = "FLOAT";
+				type = columnSizes.floating[property.size || 4];
 			} else {
-				type = "INT(5)";
+				type = columnSizes.integer[property.size || 4];
 			}
@@ -145,2 +202,23 @@ break;
 			break;
+		case "date":
+			if (!property.time) {
+				type = "DATE";
+			} else {
+				type = "DATETIME";
+			}
+			break;
+		case "binary":
+		case "object":
+			if (property.big === true) {
+				type = "LONGBLOB";
+			} else {
+				type = "BLOB";
+			}
+			break;
+		case "enum":
+			type = "ENUM (" + property.values.map(exports.escapeVal) + ")";
+			break;
+		case "point":
+			type = "POINT";
+			break;
 	}
@@ -160,4 +238,156 @@
 	}
+	if (property.hasOwnProperty("defaultValue")) {
+		type += " DEFAULT " + exports.escapeVal(property.defaultValue);
+	}
 
-	return type;
+	return {
+		value  : type,
+		before : false
+	};
 };
+
+exports.escapeId = function () {
+	return Array.prototype.slice.apply(arguments).map(function (el) {
+		return "`" + el.replace(/`/g, '``') + "`";
+	}).join(".");
+};
+
+exports.escapeVal = function (val, timeZone) {
+	if (val === undefined || val === null) {
+		return 'NULL';
+	}
+
+	if (Buffer.isBuffer(val)) {
+		return bufferToString(val);
+	}
+
+	if (Array.isArray(val)) {
+		return arrayToList(val, timeZone || "local");
+	}
+
+	if (util.isDate(val)) {
+		val = dateToString(val, timeZone || "local");
+	} else {
+		switch (typeof val) {
+			case 'boolean':
+				return (val) ? 'true' : 'false';
+			case 'number':
+				if (!isFinite(val)) {
+					val = val.toString();
+					break;
+				}
+				return val + '';
+			case "object":
+				return objectToValues(val, timeZone || "local");
+			case "function":
+				return val(exports);
+		}
+	}
+
+	val = val.replace(/[\0\n\r\b\t\\\'\"\x1a]/g, function(s) {
+		switch(s) {
+			case "\0": return "\\0";
+			case "\n": return "\\n";
+			case "\r": return "\\r";
+			case "\b": return "\\b";
+			case "\t": return "\\t";
+			case "\x1a": return "\\Z";
+			default: return "\\" + s;
+		}
+	});
+
+	return "'" + val + "'";
+};
+
+function convertIndexRows(rows) {
+	var indexes = {};
+
+	for (var i = 0; i < rows.length; i++) {
+		if (rows[i].Key_name == 'PRIMARY') {
+			continue;
+		}
+		if (!indexes.hasOwnProperty(rows[i].Key_name)) {
+			indexes[rows[i].Key_name] = {
+				columns : []
+			};
+		}
+
+		indexes[rows[i].Key_name].columns.push(rows[i].Column_name);
+	}
+
+	return indexes;
+}
+
+function objectToValues(object, timeZone) {
+	var values = [];
+	for (var key in object) {
+		var value = object[key];
+
+		if(typeof value === 'function') {
+			continue;
+		}
+
+		values.push(exports.escapeId(key) + ' = ' + exports.escapeVal(value, timeZone));
+	}
+
+	return values.join(', ');
+}
+
+function arrayToList(array, timeZone) {
+	return "(" + array.map(function(v) {
+		if (Array.isArray(v)) return arrayToList(v);
+		return exports.escapeVal(v, timeZone);
+	}).join(', ') + ")";
+}
+
+function bufferToString(buffer) {
+	var hex = '';
+
+	try {
+		hex = buffer.toString('hex');
+	} catch (err) {
+		// node v0.4.x does not support hex / throws unknown encoding error
+		for (var i = 0; i < buffer.length; i++) {
+			var b = buffer[i];
+			hex += zeroPad(b.toString(16));
+		}
+	}
+
+	return "X'" + hex+ "'";
+}
+
+function dateToString(date, timeZone) {
+	var dt = new Date(date);
+
+	if (timeZone != 'local') {
+		var tz = convertTimezone(timeZone);
+
+		dt.setTime(dt.getTime() + (dt.getTimezoneOffset() * 60000));
+		if (tz !== false) {
+			dt.setTime(dt.getTime() + (tz * 60000));
+		}
+	}
+
+	var year   = dt.getFullYear();
+	var month  = zeroPad(dt.getMonth() + 1);
+	var day    = zeroPad(dt.getDate());
+	var hour   = zeroPad(dt.getHours());
+	var minute = zeroPad(dt.getMinutes());
+	var second = zeroPad(dt.getSeconds());
+
+	return year + '-' + month + '-' + day + ' ' + hour + ':' + minute + ':' + second;
+}
+
+function zeroPad(number) {
+	return (number < 10) ? '0' + number : number;
+}
+
+function convertTimezone(tz) {
+	if (tz == "Z") return 0;
+
+	var m = tz.match(/([\+\-\s])(\d\d):?(\d\d)?/);
+	if (m) {
+		return (m[1] == '-' ? -1 : 1) * (parseInt(m[2], 10) + ((m[3] ? parseInt(m[3], 10) : 0) / 60)) * 60;
+	}
+	return false;
+}

lib/SQL.js

@@ -5,3 +5,5 @@ exports.CREATE_TABLE = function (options, dialect) {
 	if (options.primary && options.primary.length > 0) {
-		sql += ", PRIMARY KEY (" + options.primary.map(dialect.escapeId).join(", ") + ")";
+		sql += ", PRIMARY KEY (" + options.primary.map(function (val) {
+			return dialect.escapeId(val);
+		}).join(", ") + ")";
 	}
@@ -40,1 +42,16 @@
 };
+
+exports.CREATE_INDEX = function (options, dialect) {
+	var sql = "CREATE INDEX " + dialect.escapeId(options.name) +
+	          " ON " + dialect.escapeId(options.collection) +
+	          " (" + options.columns.map(function (col) { return dialect.escapeId(col); }) + ")";
+
+	return sql;
+};
+
+exports.DROP_INDEX = function (options, dialect) {
+	var sql = "DROP INDEX " + dialect.escapeId(options.name) +
+	          " ON " + dialect.escapeId(options.collection);
+
+	return sql;
+};

lib/Sync.js

@@ -1,3 +0,4 @@
-var _ = require("lodash");
-var noOp = function () {};
+var Queue = require("./Queue").Queue;
+var noOp  = function () {};
+var _     = require("lodash");
 
@@ -35,5 +36,23 @@ exports.Sync = Sync;
 		var primary = [];
+		var before  = [];
+		var nextBefore = function () {
+			if (before.length === 0) {
+				return Dialect.createCollection(db, collection.name, columns, primary, function () {
+					return syncIndexes(collection.name, getCollectionIndexes(collection), cb);
+				});
+			}
 
+			var next = before.shift();
+
+			next(db, function (err) {
+				if (err) {
+					return cb(err);
+				}
+
+				return nextBefore();
+			});
+		};
+
 		for (var k in collection.properties) {
-			var col = createColumn(k, collection.properties[k]);
+			var col = createColumn(collection.name, k, collection.properties[k]);
 
@@ -48,3 +67,7 @@ if (col === false) {
 
-			columns.push(col);
+			columns.push(col.value);
+
+			if (col.before) {
+				before.push(col.before);
+			}
 		}
@@ -54,7 +77,7 @@
 
-		return Dialect.createCollection(db, collection.name, columns, primary, cb);
+		return nextBefore();
 	};
 
-	var createColumn = function (name, property) {
-		var type = Dialect.getType(property);
+	var createColumn = function (collection, name, property) {
+		var type = Dialect.getType(collection, name, property);
 
@@ -65,18 +88,11 @@ if (type === false) {
 
-		return Dialect.escapeId(name) + " " + type;
+		return {
+			value  : Dialect.escapeId(name) + " " + type.value,
+			before : type.before
+		};
 	};
 
 	var syncCollection = function (collection, columns, cb) {
-		var last_k      = null;
-		var pending     = 0;
-		var donePending = function (err) {
-			if (pending < 0) return;
-			if (err) {
-				pending = -1; // error state
-				return cb(err);
-			}
-			if (--pending === 0) {
-				return cb();
-			}
-		};
+		var queue   = new Queue(cb);
+		var last_k  = null;
 
@@ -87,3 +103,3 @@ debug("Synchronizing " + collection.name);
 			if (!columns.hasOwnProperty(k)) {
-				var col = createColumn(k, collection.properties[k]);
+				var col = createColumn(collection.name, k, collection.properties[k]);
 
@@ -94,8 +110,20 @@ if (col === false) {
 
-				pending += 1;
+				debug("Adding column " + collection.name + "." + k + ": " + col.value);
 
-				debug("Adding column " + collection.name + "." + k + ": " + col);
-				Dialect.addCollectionColumn(db, collection.name, col, last_k, donePending);
+				if (col.before) {
+					queue.add(col, function (col, next) {
+						col.before(db, function (err) {
+							if (err) {
+								return next(err);
+							}
+							return Dialect.addCollectionColumn(db, collection.name, col.value, last_k, next);
+						});
+					});
+				} else {
+					queue.add(function (next) {
+						return Dialect.addCollectionColumn(db, collection.name, col.value, last_k, next);
+					});
+				}
 			} else if (needToSync(collection.properties[k], columns[k])) {
-				var col = createColumn(k, collection.properties[k]);
+				var col = createColumn(collection.name, k, collection.properties[k]);
 
@@ -106,6 +134,18 @@ if (col === false) {
 
-				pending += 1;
+				debug("Modifying column " + collection.name + "." + k + ": " + col.value);
 
-				debug("Modifying column " + collection.name + "." + k + ": " + col);
-				Dialect.modifyCollectionColumn(db, collection.name, col, donePending);
+				if (col.before) {
+					queue.add(col, function (col, next) {
+						col.before(db, function (err) {
+							if (err) {
+								return next(err);
+							}
+							return Dialect.modifyCollectionColumn(db, collection.name, col.value, next);
+						});
+					});
+				} else {
+					queue.add(function (next) {
+						return Dialect.modifyCollectionColumn(db, collection.name, col.value, next);
+					});
+				}
 			}
@@ -118,14 +158,93 @@
 			if (!collection.properties.hasOwnProperty(k)) {
-				pending += 1;
+				queue.add(function (next) {
+					debug("Dropping column " + collection.name + "." + k);
+					return Dialect.dropCollectionColumn(db, collection.name, k, next);
+				});
+			}
+		}
 
-				debug("Dropping column " + collection.name + "." + k);
-				Dialect.dropCollectionColumn(db, collection.name, k, donePending);
+		var indexes = getCollectionIndexes(collection);
+
+		if (indexes.length) {
+			queue.add(function (next) {
+				return syncIndexes(collection.name, indexes, next);
+			});
+		}
+
+		return queue.check();
+	};
+
+	var getCollectionIndexes = function (collection) {
+		var indexes = [];
+		var found;
+
+		for (var k in collection.properties) {
+			if (!collection.properties[k].hasOwnProperty("index") || !collection.properties[k].index) {
+				continue;
 			}
+			if (!Array.isArray(collection.properties[k].index)) {
+				collection.properties[k].index = [ collection.properties[k].index ];
+			}
+
+			for (var i = 0; i < collection.properties[k].index.length; i++) {
+				if (collection.properties[k].index[i] === true) {
+					indexes.push({
+						name    : k + "_index",
+						columns : [ k ]
+					});
+				} else {
+					found = false;
+
+					for (var j = 0; j < indexes.length; j++) {
+						if (indexes[j].name == collection.properties[k].index[i]) {
+							found = true;
+							indexes[j].columns.push(k);
+							break;
+						}
+					}
+					if (!found) {
+						indexes.push({
+							name    : collection.properties[k].index[i],
+							columns : [ k ]
+						});
+					}
+				}
+			}
 		}
 
-		if (pending === 0) {
-			return cb();
+		if (typeof Dialect.convertIndexes == "function") {
+			indexes = Dialect.convertIndexes(collection, indexes);
 		}
+
+		return indexes;
 	};
 
+	var syncIndexes = function (name, indexes, cb) {
+		Dialect.getCollectionIndexes(db, name, function (err, db_indexes) {
+			if (err) return cb(err);
+
+			var queue = new Queue(cb);
+
+			for (var i = 0; i < indexes.length; i++) {
+				if (!db_indexes.hasOwnProperty(indexes[i].name)) {
+					debug("Adding index " + name + "." + indexes[i].name + " (" + indexes[i].columns.join(", ") + ")");
+					queue.add(indexes[i], function (index, next) {
+						return Dialect.addIndex(db, index.name, name, index.columns, next);
+					});
+					continue;
+				}
+				delete db_indexes[indexes[i].name];
+			}
+
+			for (var i in db_indexes) {
+				debug("Removing index " + name + "." + i);
+				queue.add(i, function (index, next) {
+					return Dialect.removeIndex(db, index, name, next);
+				});
+			}
+
+			return queue.check();
+		});
+	};
+
 	var needToSync = function (property, column) {
@@ -135,8 +254,12 @@ if (property.type != column.type) {
 		}
-
 		if (property.required != column.required && !property.primary) {
 			return true;
 		}
-
+		if (property.hasOwnProperty("defaultValue") && property.defaultValue != column.defaultValue) {
+			return true;
+		}
 		if (property.type == "number") {
+			if ((property.size || 4) != column.size) {
+				return true;
+			}
 			if (property.hasOwnProperty("rational") && property.rational != column.rational) {
@@ -146,2 +269,8 @@ return true;
 		}
+		if (property.type == "enum") {
+			if (_.difference(property.values, column.values).length > 0
+			|| _.difference(column.values, property.values).length > 0) {
+				return true;
+			}
+		}
 
@@ -148,0 +277,0 @@ return false;

package.json

@@ -13,3 +13,3 @@ {
 	],
-	"version"     : "0.1.0",
+	"version"     : "0.1.1",
 	"license"     : "MIT",
@@ -16,0 +16,0 @@ "repository"  : "http://github.com/dresende/node-sql-ddl-sync.git",

README.md

 ## NodeJS SQL DDL Synchronization
 
 [![Build Status](https://secure.travis-ci.org/dresende/node-sql-ddl-sync.png?branch=master)](http://travis-ci.org/dresende/node-sql-ddl-sync)
+[![](https://badge.fury.io/js/sql-ddl-sync.png)](https://npmjs.org/package/sql-ddl-sync)
+[![](https://gemnasium.com/dresende/node-sql-ddl-sync.png)](https://gemnasium.com/dresende/node-sql-ddl-sync)
 
@@ -14,2 +16,3 @@ ## Install
 - MySQL
+- PostgreSQL
 
@@ -21,1 +24,48 @@ ## About
 it if you want.
+
+## Example
+
+Install module and install `mysql`, create a file with the contents below and change line 2 to match valid credentials.
+Run once and you'll see table `ddl_sync_test` appear in your database. Then make some changes to it (add/drop/change columns)
+and run the code again. Your table should always return to the same structure.
+
+```js
+var mysql = require("mysql");
+var db    = mysql.createConnection("mysql://username:password@localhost/database");
+
+var Sync = require("sql-ddl-sync").Sync;
+var sync = new Sync({
+	dialect : "mysql",
+	db      : db,
+	debug   : function (text) {
+		console.log("> %s", text);
+	}
+});
+
+sync.define("ddl_sync_test", {
+	id     : { type : "number", primary: true, serial: true },
+	name   : { type : "text", required: true },
+	age    : { type : "number", rational: true },
+	male   : { type : "boolean" },
+	born   : { type : "date", time: true },
+	born2  : { type : "date" },
+	int2   : { type : "number", size: 2 },
+	int4   : { type : "number", size: 4 },
+	int8   : { type : "number", size: 8 },
+	float4 : { type : "number", rational: true, size: 4 },
+	float8 : { type : "number", rational: true, size: 8 },
+	type   : { type : "enum", values: [ 'dog', 'cat'], defaultValue: 'dog', required: true },
+	photo  : { type : "binary" }
+});
+
+sync.sync(function (err) {
+	if (err) {
+		console.log("> Sync Error");
+		console.log(err);
+	} else {
+		console.log("> Sync Done");
+	}
+	process.exit(0);
+});
+
+```

test/integration/mysql.js

@@ -8,4 +8,5 @@ var common  = require("../common");
 	it("should detect text", function (done) {
-		Dialect.getType({ type: "text" }).should.equal("VARCHAR(255)");
-		Dialect.getType({ type: "text", size: 150 }).should.equal("VARCHAR(150)");
+		Dialect.getType(null, null, { type: "text" }).value.should.equal("VARCHAR(255)");
+		Dialect.getType(null, null, { type: "text", size: 150 }).value.should.equal("VARCHAR(150)");
+		Dialect.getType(null, null, { type: "text", size: 1000 }).value.should.equal("VARCHAR(1000)");
 
@@ -16,3 +17,6 @@ return done();
 	it("should detect numbers", function (done) {
-		Dialect.getType({ type: "number" }).should.equal("INT(5)");
+		Dialect.getType(null, null, { type: "number" }).value.should.equal("INTEGER");
+		Dialect.getType(null, null, { type: "number", size: 4 }).value.should.equal("INTEGER");
+		Dialect.getType(null, null, { type: "number", size: 2 }).value.should.equal("SMALLINT");
+		Dialect.getType(null, null, { type: "number", size: 8 }).value.should.equal("BIGINT");
 
@@ -23,3 +27,5 @@ return done();
 	it("should detect rational numbers", function (done) {
-		Dialect.getType({ type: "number", rational: true }).should.equal("FLOAT");
+		Dialect.getType(null, null, { type: "number", rational: true }).value.should.equal("FLOAT");
+		Dialect.getType(null, null, { type: "number", rational: true, size: 4 }).value.should.equal("FLOAT");
+		Dialect.getType(null, null, { type: "number", rational: true, size: 8 }).value.should.equal("DOUBLE");
 
@@ -30,3 +36,3 @@ return done();
 	it("should detect booleans", function (done) {
-		Dialect.getType({ type: "boolean" }).should.equal("TINYINT(1)");
+		Dialect.getType(null, null, { type: "boolean" }).value.should.equal("TINYINT(1)");
 
@@ -36,4 +42,28 @@ return done();
 
+	it("should detect dates", function (done) {
+		Dialect.getType(null, null, { type: "date" }).value.should.equal("DATE");
+
+		return done();
+	});
+
+	it("should detect dates with times", function (done) {
+		Dialect.getType(null, null, { type: "date", time: true }).value.should.equal("DATETIME");
+
+		return done();
+	});
+
+	it("should detect binary", function (done) {
+		Dialect.getType(null, null, { type: "binary" }).value.should.equal("BLOB");
+
+		return done();
+	});
+
+	it("should detect big binary", function (done) {
+		Dialect.getType(null, null, { type: "binary", big: true }).value.should.equal("LONGBLOB");
+
+		return done();
+	});
+
 	it("should detect required items", function (done) {
-		Dialect.getType({ type: "boolean", required: true }).should.match(/NOT NULL/);
+		Dialect.getType(null, null, { type: "boolean", required: true }).value.should.match(/NOT NULL/);
 
@@ -43,7 +73,13 @@ return done();
 
+	it("should detect default values", function (done) {
+		Dialect.getType(null, null, { type: "number", defaultValue: 3 }).value.should.match(/DEFAULT 3/);
+
+		return done();
+	});
+
 	it("should detect serial", function (done) {
-		var column = Dialect.getType({
+		var column = Dialect.getType(null, null, {
 			type   : "number",
 			serial : true
-		});
+		}).value;
 
@@ -50,0 +86,0 @@ column.should.match(/INT/);

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

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

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

41002

increased by174.9%

Number of package files

16

increased by33.33%

Lines of code

1225

increased by186.89%

Number of lines in readme file

70

increased by250%

Worsened metrics

Number of medium supply chain risk alerts

1

increased byInfinity%

No dependency changes