addAttribute = function (connectionName, collectionName, attrName, attrDef, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __ADD_ATTRIBUTE__, cb);
} else {
__ADD_ATTRIBUTE__(connection, cb);
}
function __ADD_ATTRIBUTE__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
var tableName = collectionName;
var query = sql.addColumn(tableName, attrName, attrDef);
// Run query
log('MYSQL.addAttribute: ', query);
connection.query(query, function(err, result) {
if (err) return cb(err);
// TODO: marshal response to waterline interface
cb(err);
});
}
}n/a
count = function (connectionName, collectionName, options, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __COUNT__, cb);
} else {
__COUNT__(connection, cb);
}
function __COUNT__(connection, cb) {
// Check if this is an aggregate query and that there is something to return
if(options.groupBy || options.sum || options.average || options.min || options.max) {
if(!options.sum && !options.average && !options.min && !options.max) {
return cb(Errors.InvalidGroupBy);
}
}
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
// Build find query
var schema = connectionObject.schema;
var _query;
var sequel = new Sequel(schema, sqlOptions);
// Build a count query
try {
_query = sequel.count(collectionName, options);
} catch(e) {
return cb(e);
}
// Run query
log('MYSQL.count: ', _query.query[0]);
connection.query(_query.query[0], function(err, result) {
if(err) return cb(err);
// Return the count from the simplified query
cb(null, result[0].count);
});
}
}n/a
create = function (connectionName, collectionName, data, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __CREATE__, cb);
} else {
__CREATE__(connection, cb);
}
function __CREATE__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
var tableName = collectionName;
var _insertData = _.cloneDeep(data);
// Prepare values
Object.keys(data).forEach(function(value) {
data[value] = utils.prepareValue(data[value]);
});
var schema = connectionObject.schema;
var _query;
var sequel = new Sequel(schema, sqlOptions);
// Build a query for the specific query strategy
try {
_query = sequel.create(collectionName, data);
} catch(e) {
return cb(e);
}
// Run query
log('MySQL.create: ', _query.query);
connection.query(_query.query, function(err, result) {
if (err) return cb( handleQueryError(err) );
// Build model to return
var autoInc = null;
Object.keys(collection.definition).forEach(function(key) {
if(!collection.definition[key].hasOwnProperty('autoIncrement')) return;
autoInc = key;
});
var autoIncData = {};
if (autoInc) {
autoIncData[autoInc] = result.insertId;
}
var values = _.extend({}, _insertData, autoIncData);
cb(err, values);
});
}
}n/a
createEach = function (connectionName, collectionName, valuesList, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __CREATE_EACH__, cb);
} else {
__CREATE_EACH__(connection, cb);
}
function __CREATE_EACH__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
var tableName = collectionName;
var records = [];
async.eachSeries(valuesList, function (data, cb) {
// Prepare values
Object.keys(data).forEach(function(value) {
data[value] = utils.prepareValue(data[value]);
});
var schema = connectionObject.schema;
var _query;
var sequel = new Sequel(schema, sqlOptions);
// Build a query for the specific query strategy
try {
_query = sequel.create(collectionName, data);
} catch(e) {
return cb(e);
}
// Run query
log('MySQL.createEach: ', _query.query);
connection.query(_query.query, function(err, results) {
if (err) return cb( handleQueryError(err) );
records.push(results.insertId);
cb();
});
}, function(err) {
if(err) return cb(err);
var pk = 'id';
Object.keys(collection.definition).forEach(function(key) {
if(!collection.definition[key].hasOwnProperty('primaryKey')) return;
pk = key;
});
// If there are no records (`!records.length`)
// then skip the query altogether- we don't need to look anything up
if (!records.length){
return cb(null, []);
}
// Build a Query to get newly inserted records
var query = 'SELECT * FROM ' + mysql.escapeId(tableName) + ' WHERE ' + mysql.escapeId(pk) + ' IN (' + records + ');';
// Run Query returing results
log('MYSQL.createEach: ', query);
connection.query(query, function(err, results) {
if(err) return cb(err);
cb(null, results);
});
});
}
}n/a
define = function (connectionName, collectionName, definition, cb, connection) {
var self = this;
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __DEFINE__, cb);
} else {
__DEFINE__(connection, cb);
}
function __DEFINE__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
if (!collection) {
return cb(util.format('Unknown collection `%s` in connection `%s`', collectionName, connectionName));
}
var tableName = mysql.escapeId(collectionName);
// Iterate through each attribute, building a query string
var schema = sql.schema(tableName, definition);
// Build query
var query = 'CREATE TABLE ' + tableName + ' (' + schema + ')';
if(connectionObject.config.charset) {
query += ' DEFAULT CHARSET ' + connectionObject.config.charset;
}
if(connectionObject.config.collation) {
if(!connectionObject.config.charset) query += ' DEFAULT ';
query += ' COLLATE ' + connectionObject.config.collation;
}
// Run query
log('MYSQL.define: ', query);
connection.query(query, function __DEFINE__(err, result) {
if (err) return cb(err);
//
// TODO:
// Determine if this can safely be changed to the `adapter` closure var
// (i.e. this is the last remaining usage of the "this" context in the MySQLAdapter)
//
self.describe(connectionName, collectionName, function(err) {
cb(err, result);
});
});
}
}n/a
describe = function (connectionName, collectionName, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __DESCRIBE__, cb);
} else {
__DESCRIBE__(connection, cb);
}
function __DESCRIBE__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
if (!collection) {
return cb(util.format('Unknown collection `%s` in connection `%s`', collectionName, connectionName));
}
var tableName = mysql.escapeId(collectionName);
var query = 'DESCRIBE ' + tableName;
var pkQuery = 'SHOW INDEX FROM ' + tableName;
// Run query
log('MySQL.describe: ', query);
log('MySQL.describe(pk): ', pkQuery);
connection.query(query, function __DESCRIBE__(err, schema) {
if (err) {
if (err.code === 'ER_NO_SUCH_TABLE') {
return cb();
} else return cb(err);
}
connection.query(pkQuery, function(err, pkResult) {
if(err) return cb(err);
// Loop through Schema and attach extra attributes
schema.forEach(function(attr) {
// Set Primary Key Attribute
if(attr.Key === 'PRI') {
attr.primaryKey = true;
// If also an integer set auto increment attribute
if(attr.Type === 'int(11)') {
attr.autoIncrement = true;
}
}
// Set Unique Attribute
if(attr.Key === 'UNI') {
attr.unique = true;
}
});
// Loop Through Indexes and Add Properties
pkResult.forEach(function(result) {
schema.forEach(function(attr) {
if(attr.Field !== result.Column_name) return;
attr.indexed = true;
});
});
// Convert mysql format to standard javascript object
var normalizedSchema = sql.normalizeSchema(schema);
// Set Internal Schema Mapping
collection.schema = normalizedSchema;
// TODO: check that what was returned actually matches the cache
cb(null, normalizedSchema);
});
});
}
}n/a
destroy = function (connectionName, collectionName, options, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __DESTROY__, cb);
} else {
__DESTROY__(connection, cb);
}
function __DESTROY__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
var tableName = collectionName;
// Build query
var schema = connectionObject.schema;
var _query;
var sequel = new Sequel(schema, sqlOptions);
// Build a query for the specific query strategy
try {
_query = sequel.destroy(collectionName, options);
} catch(e) {
return cb(e);
}
async.auto({
findRecords: function(next) {
adapter.find(connectionName, collectionName, options, next, connection);
},
destroyRecords: ['findRecords', function(next) {
log('MySQL.destroy: ', _query.query);
connection.query(_query.query, next);
}]
},
function(err, results) {
if(err) return cb(err);
cb(null, results.findRecords);
});
}
}n/a
drop = function (connectionName, collectionName, relations, cb, connection) {
if(typeof relations === 'function') {
cb = relations;
relations = [];
}
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __DROP__, cb);
} else {
__DROP__(connection, cb);
}
function __DROP__(connection, cb) {
var connectionObject = connections[connectionName];
// Drop any relations
function dropTable(item, next) {
var collection = connectionObject.collections[item];
var tableName = mysql.escapeId(collectionName);
// Build query
var query = 'DROP TABLE ' + tableName;
// Run query
log('MYSQL.drop: ', query);
connection.query(query, function __DROP__(err, result) {
if (err) {
if (err.code !== 'ER_BAD_TABLE_ERROR' && err.code !== 'ER_NO_SUCH_TABLE') return next(err);
result = null;
}
next(null, result);
});
}
async.eachSeries(relations, dropTable, function(err) {
if(err) return cb(err);
dropTable(collectionName, cb);
});
}
}n/a
emit = function (evName, data) {
// temporary hack- should only be used for cases that would crash anyways
// (see todo above- we still shouldn't throw, emit instead, hence this stub)
if (evName === 'error') { throw data; }
}n/a
escape = function (val) {
return mysql.escape(val);
}...
// Cast functions to strings
if (_.isFunction(value)) {
value = value.toString();
}
// Escape (also wraps in quotes)
return mysql.escape(value);
},
prepareAttribute: function(collectionName, value, attrName) {
return mysql.escapeId(collectionName) + '.' + mysql.escapeId(attrName);
},
// // Starting point for predicate evaluation
...escapeId = function (name) {
return mysql.escapeId(name);
}...
return memo;
}, {});
},
// @returns ALTER query for adding a column
addColumn: function (collectionName, attrName, attrDef) {
// Escape table name and attribute name
var tableName = mysql.escapeId(collectionName);
// sails.log.verbose("ADDING ",attrName, "with",attrDef);
// Build column definition
var columnDefinition = sql._schema(collectionName, attrDef, attrName);
return 'ALTER TABLE ' + tableName + ' ADD ' + columnDefinition;
...find = function (connectionName, collectionName, options, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __FIND__, cb);
} else {
__FIND__(connection, cb);
}
function __FIND__(connection, cb) {
// Check if this is an aggregate query and that there is something to return
if(options.groupBy || options.sum || options.average || options.min || options.max) {
if(!options.sum && !options.average && !options.min && !options.max) {
return cb(Errors.InvalidGroupBy);
}
}
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
// Build find query
var schema = connectionObject.schema;
var _query;
var sequel = new Sequel(schema, sqlOptions);
// Build a query for the specific query strategy
try {
_query = sequel.find(collectionName, options);
} catch(e) {
return cb(e);
}
// Run query
log('MYSQL.find: ', _query.query[0]);
connection.query(_query.query[0], function(err, result) {
if(err) return cb(err);
cb(null, result);
});
}
}n/a
join = function (connectionName, collectionName, options, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __JOIN__, cb);
} else {
__JOIN__(connection, cb);
}
function __JOIN__(client, done) {
// Populate associated records for each parent result
// (or do them all at once as an optimization, if possible)
Cursor({
instructions: options,
nativeJoins: true,
/**
* Find some records directly (using only this adapter)
* from the specified collection.
*
* @param {String} collectionIdentity
* @param {Object} criteria
* @param {Function} _cb
*/
$find: function (collectionName, criteria, _cb) {
return adapter.find(connectionName, collectionName, criteria, _cb, client);
},
/**
* Look up the name of the primary key field
* for the collection with the specified identity.
*
* @param {String} collectionIdentity
* @return {String}
*/
$getPK: function (collectionName) {
if (!collectionName) return;
return _getPK(connectionName, collectionName);
},
/**
* Given a strategy type, build up and execute a SQL query for it.
*
* @param {}
*/
$populateBuffers: function populateBuffers(options, next) {
var buffers = options.buffers;
var instructions = options.instructions;
// Grab the collection by looking into the connection
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
var parentRecords = [];
var cachedChildren = {};
// Grab Connection Schema
var schema = {};
Object.keys(connectionObject.collections).forEach(function(coll) {
schema[coll] = connectionObject.collections[coll].schema;
});
// Build Query
var _schema = connectionObject.schema;
var sequel = new Sequel(_schema, sqlOptions);
var _query;
// Build a query for the specific query strategy
try {
_query = sequel.find(collectionName, instructions);
} catch(e) {
return next(e);
}
async.auto({
processParent: function(next) {
log('MySQL.populateBuffers: ', _query.query[0]);
client.query(_query.query[0], function __FIND__(err, result) {
if(err) return next(err);
parentRecords = result;
var splitChildren = function(parent, next) {
var cache = {};
_.keys(parent).forEach(function(key) {
// Check if we can split this on our special alias identifier '___' and if
// so put the result in the cache
var split = key.split('___');
if(split.length < 2) return;
if(!hop(cache, split[0])) cache[split[0]] = {};
cache[split[0]][split[1]] = parent[key];
delete parent[key];
});
// Combine the local cache into the cachedChildren
if(_.keys(cache).length > 0) {
_.keys(cache).forEach(function(pop) {
if(!hop(cachedChildren, pop)) cachedChildren[pop] = [];
cachedChildren[pop] = cachedChildren[pop].concat(cache[pop]);
});
}
next();
};
// Pull out any aliased child records that have come from a hasFK association
async.eachSeries(parentRecords, splitChildren, function(err) {
if(err) return next(err);
buffers.parents = parentRecords;
next();
});
});
},
// Build child buffers.
// For each instruction, loop through the parent records and build up a
// buffer for the record.
buildChildBuffers: ['processParent', function(next, result ...n/a
query = function (connectionName, collectionName, query, data, cb, connection) {
if (_.isFunction(data)) {
cb = data;
data = null;
}
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __QUERY__, cb);
} else {
__QUERY__(connection, cb);
}
function __QUERY__(connection, cb) {
// Run query
log('MySQL.query: ', query);
if (data) connection.query(query, data, cb);
else connection.query(query, cb);
}
}n/a
function registerConnection(connection, collections, cb) {
// Validate arguments
if(!connection.identity) return cb(Errors.IdentityMissing);
if(connections[connection.identity]) return cb(Errors.IdentityDuplicate);
// Build up a schema for this connection that can be used throughout the adapter
var schema = {};
_.each(_.keys(collections), function(coll) {
var collection = collections[coll];
if(!collection) return;
var _schema = collection.waterline && collection.waterline.schema && collection.waterline.schema[collection.identity];
if(!_schema) return;
// Set defaults to ensure values are set
if(!_schema.attributes) _schema.attributes = {};
if(!_schema.tableName) _schema.tableName = coll;
// If the connection names are't the same we don't need it in the schema
if(!_.includes(collections[coll].connection, connection.identity)) {
return;
}
// If the tableName is different from the identity, store the tableName in the schema
var schemaKey = coll;
if(_schema.tableName != coll) {
schemaKey = _schema.tableName;
}
schema[schemaKey] = _schema;
});
if('url' in connection) {
utils.parseUrl(connection);
}
// Store the connection
connections[connection.identity] = {
config: connection,
collections: collections,
connection: {},
schema: schema
};
var activeConnection = connections[connection.identity];
// Create a connection pool if configured to do so.
// (and set up the necessary `releaseConnection` functionality to drain it.)
if (activeConnection.config.pool) {
activeConnection.connection.pool = mysql.createPool(activeConnection.config);
activeConnection.connection.releaseConnection = _releaseConnection.poolfully;
}
// Otherwise, assign some default releaseConnection functionality.
else {
activeConnection.connection.releaseConnection = _releaseConnection.poollessly;
}
// Done! The WLConnection (and all of it's collections) have been loaded.
return cb();
}n/a
removeAttribute = function (connectionName, collectionName, attrName, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __REMOVE_ATTRIBUTE__, cb);
} else {
__REMOVE_ATTRIBUTE__(connection, cb);
}
function __REMOVE_ATTRIBUTE__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
var tableName = collectionName;
var query = sql.removeColumn(tableName, attrName);
// Run query
log('MYSQL.removeAttribute: ', query);
connection.query(query, function(err, result) {
if (err) return cb(err);
// TODO: marshal response to waterline interface
cb(err);
});
}
}n/a
stream = function (connectionName, collectionName, options, stream, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __STREAM__);
} else {
__STREAM__(connection);
}
function __STREAM__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
var tableName = collectionName;
// Build find query
var schema = connectionObject.schema;
var _query;
var sequel = new Sequel(schema, sqlOptions);
// Build a query for the specific query strategy
try {
_query = sequel.find(collectionName, options);
} catch(e) {
return cb(e);
}
var query = _query.query[0];
// Run query
log('MySQL.stream: ', query);
var dbStream = connection.query(query);
// Handle error, an 'end' event will be emitted after this as well
dbStream.on('error', function(err) {
stream.end(err); // End stream
cb(err); // Close connection
});
// the field packets for the rows to follow
dbStream.on('fields', function(fields) {});
// Pausing the connnection is useful if your processing involves I/O
dbStream.on('result', function(row) {
connection.pause();
stream.write(row, function() {
connection.resume();
});
});
// all rows have been received
dbStream.on('end', function() {
stream.end(); // End stream
cb(); // Close connection
});
}
}n/a
function teardown(connectionName, cb) {
function closeConnection(name) {
// Drain the MySQL connection pool for this Waterline Connection
// (if it's in use.)
if ( connections[name] && connections[name].connection && connections[name].connection.pool ) {
// console.log('Ending pool for ' + connectionName);
connections[name].connection.pool.end();
}
// Make sure memory is freed by removing this stuff from our
// global set of WL Connections.
delete connections[name];
}
// If no connection name was given, teardown all the connections
if(!connectionName) {
Object.keys(connections).forEach(function(conn) {
closeConnection(conn);
});
}
// Else only teardown a single connection
else {
closeConnection(connectionName);
}
return cb();
}n/a
update = function (connectionName, collectionName, options, values, cb, connection) {
if(_.isUndefined(connection)) {
return spawnConnection(connectionName, __UPDATE__, cb);
} else {
__UPDATE__(connection, cb);
}
function __UPDATE__(connection, cb) {
var connectionObject = connections[connectionName];
var collection = connectionObject.collections[collectionName];
// Build find query
var schema = connectionObject.schema;
var _query;
var sequel = new Sequel(schema, sqlOptions);
// Build a query for the specific query strategy
try {
_query = sequel.find(collectionName, _.cloneDeep(options));
} catch(e) {
return cb(e);
}
log('MySQL.update(before): ', _query.query[0]);
connection.query(_query.query[0], function(err, results) {
if(err) return cb(err);
var ids = [];
var pk = 'id';
Object.keys(collection.definition).forEach(function(key) {
if(!collection.definition[key].hasOwnProperty('primaryKey')) return;
pk = key;
});
// update statement will affect 0 rows
if (results.length === 0) {
return cb(null, []);
}
results.forEach(function(result) {
ids.push(result[pk]);
});
// Prepare values
Object.keys(values).forEach(function(value) {
values[value] = utils.prepareValue(values[value]);
});
// Build query
try {
_query = sequel.update(collectionName, options, values);
} catch(e) {
return cb(e);
}
// Run query
log('MySQL.update: ', _query.query);
connection.query(_query.query, function(err, result) {
if (err) return cb( handleQueryError(err) );
var criteria;
if(ids.length === 1) {
criteria = { where: {}, limit: 1 };
criteria.where[pk] = ids[0];
} else {
criteria = { where: {} };
criteria.where[pk] = ids;
}
// Build a query for the specific query strategy
try {
_query = sequel.find(collectionName, criteria);
} catch(e) {
return cb(e);
}
// Run query
log('MySQL.update(after): ', _query.query[0]);
connection.query(_query.query[0], function(err, result) {
if(err) return cb(err);
cb(null, result);
});
});
});
}
}n/a
configure = function ( connections ) {
/**
* Register a connection (and the collections assigned to it) with the MySQL adapter.
*
* @param {Connection} connection
* @param {Object} collections
* @param {Function} cb
*/
return function registerConnection (connection, collections, cb) {
// Validate arguments
if(!connection.identity) return cb(Errors.IdentityMissing);
if(connections[connection.identity]) return cb(Errors.IdentityDuplicate);
// Build up a schema for this connection that can be used throughout the adapter
var schema = {};
_.each(_.keys(collections), function(coll) {
var collection = collections[coll];
if(!collection) return;
var _schema = collection.waterline && collection.waterline.schema && collection.waterline.schema[collection.identity];
if(!_schema) return;
// Set defaults to ensure values are set
if(!_schema.attributes) _schema.attributes = {};
if(!_schema.tableName) _schema.tableName = coll;
// If the connection names are't the same we don't need it in the schema
if(!_.includes(collections[coll].connection, connection.identity)) {
return;
}
// If the tableName is different from the identity, store the tableName in the schema
var schemaKey = coll;
if(_schema.tableName != coll) {
schemaKey = _schema.tableName;
}
schema[schemaKey] = _schema;
});
if('url' in connection) {
utils.parseUrl(connection);
}
// Store the connection
connections[connection.identity] = {
config: connection,
collections: collections,
connection: {},
schema: schema
};
var activeConnection = connections[connection.identity];
// Create a connection pool if configured to do so.
// (and set up the necessary `releaseConnection` functionality to drain it.)
if (activeConnection.config.pool) {
activeConnection.connection.pool = mysql.createPool(activeConnection.config);
activeConnection.connection.releaseConnection = _releaseConnection.poolfully;
}
// Otherwise, assign some default releaseConnection functionality.
else {
activeConnection.connection.releaseConnection = _releaseConnection.poollessly;
}
// Done! The WLConnection (and all of it's collections) have been loaded.
return cb();
};
}n/a
poolfully = function (conn, cb) {
if (!conn || typeof conn.release !== 'function') {
return cb(Errors.ConnectionRelease);
}
// Don't wait for connection release to trigger this callback.
// (TODO: evaluate whether this should be the case)
conn.release();
return cb();
}n/a
poollessly = function (conn, cb) {
if (!conn || typeof conn.end !== 'function') {
return cb(Errors.ConnectionRelease);
}
// Wait for the connection to be ended, then trigger the callback.
conn.end(cb);
}n/a
_schema = function (collectionName, attribute, attrName) {
attrName = mysql.escapeId(attrName);
var type = sqlTypeCast(attribute);
// Process PK field
if(attribute.primaryKey) {
var columnDefinition = attrName + ' ' + type;
// If type is an integer, set auto increment
if(type === 'TINYINT' || type === 'SMALLINT' || type === 'INT' || type === 'BIGINT') {
return columnDefinition + ' UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY';
}
// Just set NOT NULL on other types
return columnDefinition + ' NOT NULL PRIMARY KEY';
}
// Process NOT NULL field.
// if notNull is true, set NOT NULL constraint
var nullPart = '';
if (attribute.notNull) {
nullPart = ' NOT NULL ';
}
// Process UNIQUE field
if(attribute.unique) {
return attrName + ' ' + type + nullPart + ' UNIQUE KEY';
}
// Process INDEX field (NON-UNIQUE KEY)
if(attribute.index) {
return attrName + ' ' + type + nullPart + ', INDEX(' + attrName + ')';
}
return attrName + ' ' + type + ' ' + nullPart;
}...
addColumn: function (collectionName, attrName, attrDef) {
// Escape table name and attribute name
var tableName = mysql.escapeId(collectionName);
// sails.log.verbose("ADDING ",attrName, "with",attrDef);
// Build column definition
var columnDefinition = sql._schema(collectionName, attrDef, attrName);
return 'ALTER TABLE ' + tableName + ' ADD ' + columnDefinition;
},
// @returns ALTER query for dropping a column
removeColumn: function (collectionName, attrName) {
// Escape table name and attribute name
...addColumn = function (collectionName, attrName, attrDef) {
// Escape table name and attribute name
var tableName = mysql.escapeId(collectionName);
// sails.log.verbose("ADDING ",attrName, "with",attrDef);
// Build column definition
var columnDefinition = sql._schema(collectionName, attrDef, attrName);
return 'ALTER TABLE ' + tableName + ' ADD ' + columnDefinition;
}n/a
attributes = function (collectionName, attributes) {
return sql.build(collectionName, attributes, sql.prepareAttribute);
}n/a
build = function (collectionName, collection, fn, separator, keyOverride, parentKey) {
separator = separator || ', ';
var $sql = '';
_.each(collection, function(value, key) {
$sql += fn(collectionName, value, keyOverride || key, parentKey);
// (always append separator)
$sql += separator;
});
// (then remove final one)
return String($sql).replace(new RegExp(separator + '+$'), '');
}...
countQuery: function(collectionName, options, tableDefs){
var query = 'SELECT count(*) as count from `' + collectionName + '`';
return query += sql.serializeOptions(collectionName, options, tableDefs);
},
// Create a schema csv for a DDL query
schema: function(collectionName, attributes) {
return sql.build(collectionName, attributes, sql._schema);
},
_schema: function(collectionName, attribute, attrName) {
attrName = mysql.escapeId(attrName);
var type = sqlTypeCast(attribute);
// Process PK field
...buildSingleQuery = function (collectionName, options, tableDefs) {
var queryPart = '';
if(options.where) {
queryPart += 'WHERE ' + sql.where(collectionName, options.where) + ' ';
}
if (options.groupBy) {
queryPart += 'GROUP BY ';
// Normalize to array
if(!Array.isArray(options.groupBy)) options.groupBy = [options.groupBy];
options.groupBy.forEach(function(key) {
queryPart += key + ', ';
});
// Remove trailing comma
queryPart = queryPart.slice(0, -2) + ' ';
}
if (options.sort) {
queryPart += 'ORDER BY ';
// Sort through each sort attribute criteria
_.each(options.sort, function(direction, attrName) {
queryPart += sql.prepareAttribute(collectionName, null, attrName) + ' ';
// Basic MongoDB-style numeric sort direction
if (direction === 1) {
queryPart += 'ASC, ';
} else {
queryPart += 'DESC, ';
}
});
// Remove trailing comma
if(queryPart.slice(-2) === ', ') {
queryPart = queryPart.slice(0, -2) + ' ';
}
}
if (hop(options, 'limit') && (options.limit !== null && options.limit !== undefined)) {
queryPart += 'LIMIT ' + options.limit + ' ';
}
if (hop(options, 'skip') && (options.skip !== null && options.skip !== undefined)) {
// Some MySQL hackery here. For details, see:
// http://stackoverflow.com/questions/255517/mysql-offset-infinite-rows
if (!options.limit) {
queryPart += 'LIMIT 18446744073709551610 ';
}
queryPart += 'OFFSET ' + options.skip + ' ';
}
return queryPart;
}...
// allow the key to be named with join or joins
var joins = options.join || options.joins || [];
if (joins.length > 0) {
return this.buildJoinQuery(collectionName, joins, options, tableDefs);
}
return this.buildSingleQuery(collectionName, options, tableDefs);
},
/**
* Build Up a Select Statement Without Joins
*/
buildSingleQuery: function(collectionName, options, tableDefs) {
...countQuery = function (collectionName, options, tableDefs){
var query = 'SELECT count(*) as count from `' + collectionName + '`';
return query += sql.serializeOptions(collectionName, options, tableDefs);
}n/a
normalizeSchema = function (schema) {
return _.reduce(schema, function(memo, field) {
// Marshal mysql DESCRIBE to waterline collection semantics
var attrName = field.Field;
var type = field.Type;
// Remove (n) column-size indicators
type = type.replace(/\([0-9]+\)$/,'');
memo[attrName] = {
type: type,
defaultsTo: field.Default,
autoIncrement: field.Extra === 'auto_increment'
};
if(field.primaryKey) {
memo[attrName].primaryKey = field.primaryKey;
}
if(field.unique) {
memo[attrName].unique = field.unique;
}
if(field.indexed) {
memo[attrName].indexed = field.indexed;
}
return memo;
}, {});
}n/a
predicate = function (collectionName, criterion, key, parentKey) {
var queryPart = '';
if (parentKey) {
return sql.prepareCriterion(collectionName, criterion, key, parentKey);
}
// OR
if (key.toLowerCase() === 'or') {
queryPart = sql.build(collectionName, criterion, sql.where, ' OR ');
return ' ( ' + queryPart + ' ) ';
}
// AND
else if (key.toLowerCase() === 'and') {
queryPart = sql.build(collectionName, criterion, sql.where, ' AND ');
return ' ( ' + queryPart + ' ) ';
}
// IN
else if (_.isArray(criterion)) {
queryPart = sql.prepareAttribute(collectionName, null, key) + " IN (" + sql.values(collectionName, criterion, key) + ")";
return queryPart;
}
// LIKE
else if (key.toLowerCase() === 'like') {
return sql.build(collectionName, criterion, function(collectionName, value, attrName) {
var attrStr = sql.prepareAttribute(collectionName, value, attrName);
// TODO: Handle regexp criterias
if (_.isRegExp(value)) {
throw new Error('RegExp LIKE criterias not supported by the MySQLAdapter yet. Please contribute @ http://github.com/balderdashy
/sails-mysql');
}
var valueStr = sql.prepareValue(collectionName, value, attrName);
// Handle escaped percent (%) signs [encoded as %%%]
valueStr = valueStr.replace(/%%%/g, '\\%');
return attrStr + " LIKE " + valueStr;
}, ' AND ');
}
// NOT
else if (key.toLowerCase() === 'not') {
throw new Error('NOT not supported yet!');
}
// Basic criteria item
else {
return sql.prepareCriterion(collectionName, criterion, key);
}
}n/a
prepareAttribute = function (collectionName, value, attrName) {
return mysql.escapeId(collectionName) + '.' + mysql.escapeId(attrName);
}...
// or if one exists, the parent key
var attrStr, valueStr;
// Special comparator case
if (parentKey) {
attrStr = sql.prepareAttribute(collectionName, value, parentKey);
valueStr = sql.prepareValue(collectionName, value, parentKey);
// Why don't we strip you out of those bothersome apostrophes?
var nakedButClean = String(valueStr).replace(new RegExp('^\'+|\'+$', 'g'), '');
if (key === '<' || key === 'lessThan') return attrStr + '<' + valueStr;
else if (key === '<=' || key === 'lessThanOrEqual') return attrStr + '<=' + valueStr;
...prepareCriterion = function (collectionName, value, key, parentKey) {
// Special sub-attr case
if (validSubAttrCriteria(value)) {
return sql.where(collectionName, value, null, key);
}
// Build escaped attr and value strings using either the key,
// or if one exists, the parent key
var attrStr, valueStr;
// Special comparator case
if (parentKey) {
attrStr = sql.prepareAttribute(collectionName, value, parentKey);
valueStr = sql.prepareValue(collectionName, value, parentKey);
// Why don't we strip you out of those bothersome apostrophes?
var nakedButClean = String(valueStr).replace(new RegExp('^\'+|\'+$', 'g'), '');
if (key === '<' || key === 'lessThan') return attrStr + '<' + valueStr;
else if (key === '<=' || key === 'lessThanOrEqual') return attrStr + '<=' + valueStr;
else if (key === '>' || key === 'greaterThan') return attrStr + '>' + valueStr;
else if (key === '>=' || key === 'greaterThanOrEqual') return attrStr + '>=' + valueStr;
else if (key === '!' || key === 'not') {
if (value === null) return attrStr + ' IS NOT NULL';
else if (_.isArray(value)) return attrStr + ' NOT IN(' + valueStr + ')';
else return attrStr + '<>' + valueStr;
}
else if (key === 'like') return attrStr + ' LIKE \'' + nakedButClean + '\'';
else if (key === 'contains') return attrStr + ' LIKE \'%' + nakedButClean + '%\'';
else if (key === 'startsWith') return attrStr + ' LIKE \'' + nakedButClean + '%\'';
else if (key === 'endsWith') return attrStr + ' LIKE \'%' + nakedButClean + '\'';
else throw new Error('Unknown comparator: ' + key);
} else {
attrStr = sql.prepareAttribute(collectionName, value, key);
valueStr = sql.prepareValue(collectionName, value, key);
// Special IS NULL case
if (_.isNull(value)) {
return attrStr + " IS NULL";
} else return attrStr + "=" + valueStr;
}
}...
// Recursively parse a predicate calculus and build a SQL query
predicate: function(collectionName, criterion, key, parentKey) {
var queryPart = '';
if (parentKey) {
return sql.prepareCriterion(collectionName, criterion, key, parentKey);
}
// OR
if (key.toLowerCase() === 'or') {
queryPart = sql.build(collectionName, criterion, sql.where, ' OR ');
return ' ( ' + queryPart + ' ) ';
}
...prepareValue = function (collectionName, value, attrName) {
// Cast dates to SQL
if (_.isDate(value)) {
value = toSqlDate(value);
}
// Cast functions to strings
if (_.isFunction(value)) {
value = value.toString();
}
// Escape (also wraps in quotes)
return mysql.escape(value);
}...
var attrStr, valueStr;
// Special comparator case
if (parentKey) {
attrStr = sql.prepareAttribute(collectionName, value, parentKey);
valueStr = sql.prepareValue(collectionName, value, parentKey);
// Why don't we strip you out of those bothersome apostrophes?
var nakedButClean = String(valueStr).replace(new RegExp('^\'+|\'+$', 'g'), '');
if (key === '<' || key === 'lessThan') return attrStr + '<' + valueStr;
else if (key === '<=' || key === 'lessThanOrEqual') return attrStr + '<=' + valueStr;
else if (key === '>' || key === 'greaterThan') return attrStr + '>' + valueStr;
...removeColumn = function (collectionName, attrName) {
// Escape table name and attribute name
var tableName = mysql.escapeId(collectionName);
attrName = mysql.escapeId(attrName);
return 'ALTER TABLE ' + tableName + ' DROP COLUMN ' + attrName;
}n/a
schema = function (collectionName, attributes) {
return sql.build(collectionName, attributes, sql._schema);
}n/a
serializeOptions = function (collectionName, options, tableDefs) {
// Join clause
// allow the key to be named with join or joins
var joins = options.join || options.joins || [];
if (joins.length > 0) {
return this.buildJoinQuery(collectionName, joins, options, tableDefs);
}
return this.buildSingleQuery(collectionName, options, tableDefs);
}...
attrName = mysql.escapeId(attrName);
return 'ALTER TABLE ' + tableName + ' DROP COLUMN ' + attrName;
},
countQuery: function(collectionName, options, tableDefs){
var query = 'SELECT count(*) as count from `' + collectionName + '`';
return query += sql.serializeOptions(collectionName, options, tableDefs);
},
// Create a schema csv for a DDL query
schema: function(collectionName, attributes) {
return sql.build(collectionName, attributes, sql._schema);
},
...values = function (collectionName, values, key) {
return sql.build(collectionName, values, sql.prepareValue, ', ', key);
}...
else if (key.toLowerCase() === 'and') {
queryPart = sql.build(collectionName, criterion, sql.where, ' AND ');
return ' ( ' + queryPart + ' ) ';
}
// IN
else if (_.isArray(criterion)) {
queryPart = sql.prepareAttribute(collectionName, null, key) + " IN (" + sql.values
(collectionName, criterion, key) + ")";
return queryPart;
}
// LIKE
else if (key.toLowerCase() === 'like') {
return sql.build(collectionName, criterion, function(collectionName, value, attrName) {
var attrStr = sql.prepareAttribute(collectionName, value, attrName);
...where = function (collectionName, where, key, parentKey) {
return sql.build(collectionName, where, sql.predicate, ' AND ', undefined, parentKey);
}...
values: function(collectionName, values, key) {
return sql.build(collectionName, values, sql.prepareValue, ', ', key);
},
prepareCriterion: function(collectionName, value, key, parentKey) {
// Special sub-attr case
if (validSubAttrCriteria(value)) {
return sql.where(collectionName, value, null, key);
}
// Build escaped attr and value strings using either the key,
// or if one exists, the parent key
var attrStr, valueStr;
...configure = function ( connections ) {
/**
* Teardown a MySQL connection.
* (if the Waterline "connection" is using a pool, also `.end()` it.)
*
* @param {String} connectionName [name of the Waterline "connection"]
* @param {Function} cb
*/
return function teardown (connectionName, cb) {
function closeConnection(name) {
// Drain the MySQL connection pool for this Waterline Connection
// (if it's in use.)
if ( connections[name] && connections[name].connection && connections[name].connection.pool ) {
// console.log('Ending pool for ' + connectionName);
connections[name].connection.pool.end();
}
// Make sure memory is freed by removing this stuff from our
// global set of WL Connections.
delete connections[name];
}
// If no connection name was given, teardown all the connections
if(!connectionName) {
Object.keys(connections).forEach(function(conn) {
closeConnection(conn);
});
}
// Else only teardown a single connection
else {
closeConnection(connectionName);
}
return cb();
};
}n/a
buildSelectStatement = function (criteria, table, schemaDefs) {
var query = '';
if(criteria.groupBy || criteria.sum || criteria.average || criteria.min || criteria.max) {
query = 'SELECT ';
// Append groupBy columns to select statement
if(criteria.groupBy) {
if(criteria.groupBy instanceof Array) {
criteria.groupBy.forEach(function(opt){
query += opt + ', ';
});
} else {
query += criteria.groupBy + ', ';
}
}
// Handle SUM
if (criteria.sum) {
if(criteria.sum instanceof Array) {
criteria.sum.forEach(function(opt){
query += 'SUM(' + opt + ') AS ' + opt + ', ';
});
} else {
query += 'SUM(' + criteria.sum + ') AS ' + criteria.sum + ', ';
}
}
// Handle AVG (casting to float to fix percision with trailing zeros)
if (criteria.average) {
if(criteria.average instanceof Array) {
criteria.average.forEach(function(opt){
query += 'AVG(' + opt + ') AS ' + opt + ', ';
});
} else {
query += 'AVG(' + criteria.average + ') AS ' + criteria.average + ', ';
}
}
// Handle MAX
if (criteria.max) {
if(criteria.max instanceof Array) {
criteria.max.forEach(function(opt){
query += 'MAX(' + opt + ') AS ' + opt + ', ';
});
} else {
query += 'MAX(' + criteria.max + ') AS ' + criteria.max + ', ';
}
}
// Handle MIN
if (criteria.min) {
if(criteria.min instanceof Array) {
criteria.min.forEach(function(opt){
query += 'MIN(' + opt + ') AS ' + opt + ', ';
});
} else {
query += 'MIN(' + criteria.min + ') AS ' + criteria.min + ', ';
}
}
// trim trailing comma
query = query.slice(0, -2) + ' ';
// Add FROM clause
return query += 'FROM `' + table + '` ';
}
/**
* If no aggregate options lets just build a normal query
*/
// Add all keys to the select statement for this table
query += 'SELECT ';
var selectKeys = [],
joinSelectKeys = [];
if ( !schemaDefs[table] ) throw new Error('Schema definition missing for table: `'+table+'`');
_( schemaDefs[table] ).forEach(function(schemaDef, key) {
selectKeys.push({ table: table, key: key });
});
// Check for joins
if(criteria.joins || criteria.join) {
var joins = criteria.joins || criteria.join;
joins.forEach(function(join) {
if(!join.select) return;
Object.keys(schemaDefs[join.child.toLowerCase()]).forEach(function(key) {
var _join = _.cloneDeep(join);
_join.key = key;
joinSelectKeys.push(_join);
});
// Remove the foreign key for this join from the selectKeys array
selectKeys = selectKeys.filter(function(select) {
var keep = true;
if(select.key === join.parentKey && join.removeParentKey) keep = false;
return keep;
});
});
}
// Add all the columns to be selected that are not joins
selectKeys.forEach(function(select) {
query += '`' + select.table + '`.`' + select.key + '`, ';
});
// Add all the columns from the joined tables
joinSelectKeys.forEach(function(select) {
// Create an alias by prepending the child table with the alias of the join
var alias = select.alias.toLowerCase() + '_' + select.child.toLowerCase();
// If this is a belongs_to relationship, keep the foreign key name from the AS part
// of the query. This will result in a selected column like: "user"."id" AS "user_id__id"
if(select.model) {
return query += mysql.escapeId(alias) + '.' + mysql.escapeId(select.key) + ' AS ' +
mysql.escapeId(select.parentKey + '__' + select.key) + ', ';
}
// If a junctionTable is used, the child value should be used in the AS part of the
// select query.
if(select.junctionTable) {
return query += mysql.escapeId(alias) + '.' + mysql.escapeId(select.key) + ' AS ' +
mysql.escapeId(select.alias + '__' + select.key) + ', ';
}
// Else if a hasMany attribu ...n/a
parseUrl = function (config) {
if(!_.isString(config.url)) return config;
var obj = url.parse(config.url);
config.host = obj.hostname || config.host;
config.port = obj.port || config.port;
if(_.isString(obj.pathname)) {
config.database = obj.pathname.split("/")[1] || config.database;
}
if(_.isString(obj.auth)) {
config.user = obj.auth.split(":")[0] || config.user;
config.password = obj.auth.split(":")[1] || config.password;
}
return config;
}...
schemaKey = _schema.tableName;
}
schema[schemaKey] = _schema;
});
if('url' in connection) {
utils.parseUrl(connection);
}
// Store the connection
connections[connection.identity] = {
config: connection,
collections: collections,
connection: {},
...prepareValue = function (value) {
if(_.isUndefined(value) || value === null) return value;
// Cast functions to strings
if (_.isFunction(value)) {
value = value.toString();
}
// Store Arrays and Objects as strings
if (Array.isArray(value) || value.constructor && value.constructor.name === 'Object') {
try {
value = JSON.stringify(value);
} catch (e) {
// just keep the value and let the db handle an error
value = value;
}
}
// Cast dates to SQL
if (_.isDate(value)) {
value = utils.toSqlDate(value);
}
return mysql.escape(value);
}...
var attrStr, valueStr;
// Special comparator case
if (parentKey) {
attrStr = sql.prepareAttribute(collectionName, value, parentKey);
valueStr = sql.prepareValue(collectionName, value, parentKey);
// Why don't we strip you out of those bothersome apostrophes?
var nakedButClean = String(valueStr).replace(new RegExp('^\'+|\'+$', 'g'), '');
if (key === '<' || key === 'lessThan') return attrStr + '<' + valueStr;
else if (key === '<=' || key === 'lessThanOrEqual') return attrStr + '<=' + valueStr;
else if (key === '>' || key === 'greaterThan') return attrStr + '>' + valueStr;
...function toSqlDate(date) {
date = date.getFullYear() + '-' +
('00' + (date.getMonth()+1)).slice(-2) + '-' +
('00' + date.getDate()).slice(-2) + ' ' +
('00' + date.getHours()).slice(-2) + ':' +
('00' + date.getMinutes()).slice(-2) + ':' +
('00' + date.getSeconds()).slice(-2);
return date;
}...
// just keep the value and let the db handle an error
value = value;
}
}
// Cast dates to SQL
if (_.isDate(value)) {
value = utils.toSqlDate(value);
}
return mysql.escape(value);
};
/**
* Builds a Select statement determining if Aggeregate options are needed.
...