442 lines
14 KiB
JavaScript
442 lines
14 KiB
JavaScript
|
'use strict';
|
||
|
|
|
||
|
|
/* jshint -W110 */
|
||
|
|
var Utils = require('../../utils')
|
||
|
|
, DataTypes = require('../../data-types')
|
||
|
|
, Transaction = require('../../transaction')
|
||
|
|
, _ = require('lodash');
|
||
|
|
|
||
|
|
var MySqlQueryGenerator = Utils._.extend(
|
||
|
|
Utils._.clone(require('../abstract/query-generator')),
|
||
|
|
Utils._.clone(require('../mysql/query-generator'))
|
||
|
|
);
|
||
|
|
|
||
|
|
var QueryGenerator = {
|
||
|
|
options: {},
|
||
|
|
dialect: 'sqlite',
|
||
|
|
|
||
|
|
createSchema: function() {
|
||
|
|
var query = "SELECT name FROM `sqlite_master` WHERE type='table' and name!='sqlite_sequence';";
|
||
|
|
return Utils._.template(query)({});
|
||
|
|
},
|
||
|
|
|
||
|
|
showSchemasQuery: function() {
|
||
|
|
return "SELECT name FROM `sqlite_master` WHERE type='table' and name!='sqlite_sequence';";
|
||
|
|
},
|
||
|
|
|
||
|
|
versionQuery: function() {
|
||
|
|
return 'SELECT sqlite_version() as `version`';
|
||
|
|
},
|
||
|
|
|
||
|
|
createTableQuery: function(tableName, attributes, options) {
|
||
|
|
options = options || {};
|
||
|
|
|
||
|
|
var query = 'CREATE TABLE IF NOT EXISTS <%= table %> (<%= attributes%>)'
|
||
|
|
, primaryKeys = []
|
||
|
|
, needsMultiplePrimaryKeys = (Utils._.values(attributes).filter(function(definition) {
|
||
|
|
return Utils._.includes(definition, 'PRIMARY KEY');
|
||
|
|
}).length > 1)
|
||
|
|
, attrStr = [];
|
||
|
|
|
||
|
|
for (var attr in attributes) {
|
||
|
|
if (attributes.hasOwnProperty(attr)) {
|
||
|
|
var dataType = attributes[attr];
|
||
|
|
var containsAutoIncrement = Utils._.includes(dataType, 'AUTOINCREMENT');
|
||
|
|
|
||
|
|
if (containsAutoIncrement) {
|
||
|
|
dataType = dataType.replace(/BIGINT/, 'INTEGER');
|
||
|
|
}
|
||
|
|
|
||
|
|
var dataTypeString = dataType;
|
||
|
|
if (Utils._.includes(dataType, 'PRIMARY KEY')) {
|
||
|
|
if (Utils._.includes(dataType, 'INTEGER')) { // Only INTEGER is allowed for primary key, see https://github.com/sequelize/sequelize/issues/969 (no lenght, unsigned etc)
|
||
|
|
dataTypeString = containsAutoIncrement ? 'INTEGER PRIMARY KEY AUTOINCREMENT' : 'INTEGER PRIMARY KEY';
|
||
|
|
}
|
||
|
|
|
||
|
|
if (needsMultiplePrimaryKeys) {
|
||
|
|
primaryKeys.push(attr);
|
||
|
|
dataTypeString = dataType.replace(/PRIMARY KEY/, 'NOT NULL');
|
||
|
|
}
|
||
|
|
}
|
||
|
|
attrStr.push(this.quoteIdentifier(attr) + ' ' + dataTypeString);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
var values = {
|
||
|
|
table: this.quoteTable(tableName),
|
||
|
|
attributes: attrStr.join(', '),
|
||
|
|
charset: (options.charset ? 'DEFAULT CHARSET=' + options.charset : '')
|
||
|
|
}
|
||
|
|
, pkString = primaryKeys.map(function(pk) { return this.quoteIdentifier(pk); }.bind(this)).join(', ');
|
||
|
|
|
||
|
|
if (!!options.uniqueKeys) {
|
||
|
|
Utils._.each(options.uniqueKeys, function(columns) {
|
||
|
|
if (!columns.singleField) { // If it's a single field its handled in column def, not as an index
|
||
|
|
values.attributes += ', UNIQUE (' + columns.fields.join(', ') + ')';
|
||
|
|
}
|
||
|
|
});
|
||
|
|
}
|
||
|
|
|
||
|
|
if (pkString.length > 0) {
|
||
|
|
values.attributes += ', PRIMARY KEY (' + pkString + ')';
|
||
|
|
}
|
||
|
|
|
||
|
|
var sql = Utils._.template(query)(values).trim() + ';';
|
||
|
|
return this.replaceBooleanDefaults(sql);
|
||
|
|
},
|
||
|
|
|
||
|
|
booleanValue: function(value){
|
||
|
|
return !!value ? 1 : 0;
|
||
|
|
},
|
||
|
|
|
||
|
|
addLimitAndOffset: function(options){
|
||
|
|
var fragment = '';
|
||
|
|
if (options.offset && !options.limit) {
|
||
|
|
fragment += ' LIMIT ' + options.offset + ', ' + 10000000000000;
|
||
|
|
} else if (options.limit) {
|
||
|
|
if (options.offset) {
|
||
|
|
fragment += ' LIMIT ' + options.offset + ', ' + options.limit;
|
||
|
|
} else {
|
||
|
|
fragment += ' LIMIT ' + options.limit;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return fragment;
|
||
|
|
},
|
||
|
|
|
||
|
|
addColumnQuery: function(table, key, dataType) {
|
||
|
|
var query = 'ALTER TABLE <%= table %> ADD <%= attribute %>;'
|
||
|
|
, attributes = {};
|
||
|
|
|
||
|
|
attributes[key] = dataType;
|
||
|
|
var fields = this.attributesToSQL(attributes, {
|
||
|
|
context: 'addColumn'
|
||
|
|
});
|
||
|
|
var attribute = Utils._.template('<%= key %> <%= definition %>')({
|
||
|
|
key: this.quoteIdentifier(key),
|
||
|
|
definition: fields[key]
|
||
|
|
});
|
||
|
|
|
||
|
|
var sql = Utils._.template(query)({
|
||
|
|
table: this.quoteTable(table),
|
||
|
|
attribute: attribute
|
||
|
|
});
|
||
|
|
|
||
|
|
return this.replaceBooleanDefaults(sql);
|
||
|
|
},
|
||
|
|
|
||
|
|
showTablesQuery: function() {
|
||
|
|
return "SELECT name FROM `sqlite_master` WHERE type='table' and name!='sqlite_sequence';";
|
||
|
|
},
|
||
|
|
|
||
|
|
upsertQuery: function (tableName, insertValues, updateValues, where, rawAttributes, options) {
|
||
|
|
options.ignore = true;
|
||
|
|
var sql = this.insertQuery(tableName, insertValues, rawAttributes, options) + ' ' + this.updateQuery(tableName, updateValues, where, options, rawAttributes);
|
||
|
|
|
||
|
|
return sql;
|
||
|
|
},
|
||
|
|
|
||
|
|
bulkInsertQuery: function(tableName, attrValueHashes, options) {
|
||
|
|
var query = 'INSERT<%= ignoreDuplicates %> INTO <%= table %> (<%= attributes %>) VALUES <%= tuples %>;'
|
||
|
|
, tuples = []
|
||
|
|
, allAttributes = [];
|
||
|
|
|
||
|
|
Utils._.forEach(attrValueHashes, function(attrValueHash) {
|
||
|
|
Utils._.forOwn(attrValueHash, function(value, key) {
|
||
|
|
if (allAttributes.indexOf(key) === -1) allAttributes.push(key);
|
||
|
|
});
|
||
|
|
});
|
||
|
|
|
||
|
|
Utils._.forEach(attrValueHashes, function(attrValueHash) {
|
||
|
|
tuples.push('(' +
|
||
|
|
allAttributes.map(function (key) {
|
||
|
|
return this.escape(attrValueHash[key]);
|
||
|
|
}.bind(this)).join(',') +
|
||
|
|
')');
|
||
|
|
}.bind(this));
|
||
|
|
|
||
|
|
var replacements = {
|
||
|
|
ignoreDuplicates: options && options.ignoreDuplicates ? ' OR IGNORE' : '',
|
||
|
|
table: this.quoteTable(tableName),
|
||
|
|
attributes: allAttributes.map(function(attr) {
|
||
|
|
return this.quoteIdentifier(attr);
|
||
|
|
}.bind(this)).join(','),
|
||
|
|
tuples: tuples
|
||
|
|
};
|
||
|
|
|
||
|
|
return Utils._.template(query)(replacements);
|
||
|
|
},
|
||
|
|
|
||
|
|
updateQuery: function(tableName, attrValueHash, where, options) {
|
||
|
|
options = options || {};
|
||
|
|
_.defaults(options, this.options);
|
||
|
|
|
||
|
|
attrValueHash = Utils.removeNullValuesFromHash(attrValueHash, options.omitNull, options);
|
||
|
|
|
||
|
|
var query = 'UPDATE <%= table %> SET <%= values %> <%= where %>'
|
||
|
|
, values = [];
|
||
|
|
|
||
|
|
for (var key in attrValueHash) {
|
||
|
|
var value = attrValueHash[key];
|
||
|
|
values.push(this.quoteIdentifier(key) + '=' + this.escape(value));
|
||
|
|
}
|
||
|
|
|
||
|
|
var replacements = {
|
||
|
|
table: this.quoteTable(tableName),
|
||
|
|
values: values.join(','),
|
||
|
|
where: this.whereQuery(where)
|
||
|
|
};
|
||
|
|
|
||
|
|
return Utils._.template(query)(replacements).trim();
|
||
|
|
},
|
||
|
|
|
||
|
|
deleteQuery: function(tableName, where, options) {
|
||
|
|
options = options || {};
|
||
|
|
|
||
|
|
var query = 'DELETE FROM <%= table %><%= where %>';
|
||
|
|
var replacements = {
|
||
|
|
table: this.quoteTable(tableName),
|
||
|
|
where: this.getWhereConditions(where)
|
||
|
|
};
|
||
|
|
|
||
|
|
if (replacements.where) {
|
||
|
|
replacements.where = ' WHERE ' + replacements.where;
|
||
|
|
}
|
||
|
|
|
||
|
|
return Utils._.template(query)(replacements);
|
||
|
|
},
|
||
|
|
|
||
|
|
attributesToSQL: function(attributes) {
|
||
|
|
var result = {};
|
||
|
|
|
||
|
|
for (var name in attributes) {
|
||
|
|
var dataType = attributes[name];
|
||
|
|
var fieldName = dataType.field || name;
|
||
|
|
|
||
|
|
if (Utils._.isObject(dataType)) {
|
||
|
|
var template = '<%= type %>'
|
||
|
|
, replacements = { type: dataType.type };
|
||
|
|
|
||
|
|
if (dataType.type instanceof DataTypes.ENUM) {
|
||
|
|
replacements.type = 'TEXT';
|
||
|
|
|
||
|
|
if (!(Array.isArray(dataType.values) && (dataType.values.length > 0))) {
|
||
|
|
throw new Error("Values for ENUM haven't been defined.");
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if (dataType.hasOwnProperty('allowNull') && !dataType.allowNull) {
|
||
|
|
template += ' NOT NULL';
|
||
|
|
}
|
||
|
|
|
||
|
|
if (Utils.defaultValueSchemable(dataType.defaultValue)) {
|
||
|
|
// TODO thoroughly check that DataTypes.NOW will properly
|
||
|
|
// get populated on all databases as DEFAULT value
|
||
|
|
// i.e. mysql requires: DEFAULT CURRENT_TIMESTAMP
|
||
|
|
template += ' DEFAULT <%= defaultValue %>';
|
||
|
|
replacements.defaultValue = this.escape(dataType.defaultValue);
|
||
|
|
}
|
||
|
|
|
||
|
|
if (dataType.unique === true) {
|
||
|
|
template += ' UNIQUE';
|
||
|
|
}
|
||
|
|
|
||
|
|
if (dataType.primaryKey) {
|
||
|
|
template += ' PRIMARY KEY';
|
||
|
|
|
||
|
|
if (dataType.autoIncrement) {
|
||
|
|
template += ' AUTOINCREMENT';
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if(dataType.references) {
|
||
|
|
dataType = Utils.formatReferences(dataType);
|
||
|
|
template += ' REFERENCES <%= referencesTable %> (<%= referencesKey %>)';
|
||
|
|
replacements.referencesTable = this.quoteTable(dataType.references.model);
|
||
|
|
|
||
|
|
if(dataType.references.key) {
|
||
|
|
replacements.referencesKey = this.quoteIdentifier(dataType.references.key);
|
||
|
|
} else {
|
||
|
|
replacements.referencesKey = this.quoteIdentifier('id');
|
||
|
|
}
|
||
|
|
|
||
|
|
if(dataType.onDelete) {
|
||
|
|
template += ' ON DELETE <%= onDeleteAction %>';
|
||
|
|
replacements.onDeleteAction = dataType.onDelete.toUpperCase();
|
||
|
|
}
|
||
|
|
|
||
|
|
if(dataType.onUpdate) {
|
||
|
|
template += ' ON UPDATE <%= onUpdateAction %>';
|
||
|
|
replacements.onUpdateAction = dataType.onUpdate.toUpperCase();
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
result[fieldName] = Utils._.template(template)(replacements);
|
||
|
|
} else {
|
||
|
|
result[fieldName] = dataType;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return result;
|
||
|
|
},
|
||
|
|
|
||
|
|
findAutoIncrementField: function(factory) {
|
||
|
|
var fields = [];
|
||
|
|
|
||
|
|
for (var name in factory.attributes) {
|
||
|
|
if (factory.attributes.hasOwnProperty(name)) {
|
||
|
|
var definition = factory.attributes[name];
|
||
|
|
if (definition && definition.autoIncrement) {
|
||
|
|
fields.push(name);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return fields;
|
||
|
|
},
|
||
|
|
|
||
|
|
showIndexesQuery: function(tableName) {
|
||
|
|
var sql = 'PRAGMA INDEX_LIST(<%= tableName %>)';
|
||
|
|
return Utils._.template(sql)({ tableName: this.quoteTable(tableName) });
|
||
|
|
},
|
||
|
|
|
||
|
|
removeIndexQuery: function(tableName, indexNameOrAttributes) {
|
||
|
|
var sql = 'DROP INDEX IF EXISTS <%= indexName %>'
|
||
|
|
, indexName = indexNameOrAttributes;
|
||
|
|
|
||
|
|
if (typeof indexName !== 'string') {
|
||
|
|
indexName = Utils.inflection.underscore(tableName + '_' + indexNameOrAttributes.join('_'));
|
||
|
|
}
|
||
|
|
|
||
|
|
return Utils._.template(sql)( { tableName: this.quoteIdentifiers(tableName), indexName: indexName });
|
||
|
|
},
|
||
|
|
|
||
|
|
describeTableQuery: function(tableName, schema, schemaDelimiter) {
|
||
|
|
var options = {};
|
||
|
|
options.schema = schema;
|
||
|
|
options.schemaDelimiter = schemaDelimiter;
|
||
|
|
options.quoted = false;
|
||
|
|
|
||
|
|
var sql = 'PRAGMA TABLE_INFO(<%= tableName %>);';
|
||
|
|
return Utils._.template(sql)({ tableName: this.addSchema({tableName: this.quoteIdentifiers(tableName), options: options})});
|
||
|
|
},
|
||
|
|
|
||
|
|
removeColumnQuery: function(tableName, attributes) {
|
||
|
|
var backupTableName
|
||
|
|
, query;
|
||
|
|
|
||
|
|
attributes = this.attributesToSQL(attributes);
|
||
|
|
|
||
|
|
if (typeof tableName === 'object') {
|
||
|
|
backupTableName = {
|
||
|
|
tableName: tableName.tableName + '_backup',
|
||
|
|
schema: tableName.schema
|
||
|
|
};
|
||
|
|
} else {
|
||
|
|
backupTableName = tableName + '_backup';
|
||
|
|
}
|
||
|
|
|
||
|
|
query = [
|
||
|
|
this.createTableQuery(backupTableName, attributes).replace('CREATE TABLE', 'CREATE TEMPORARY TABLE'),
|
||
|
|
'INSERT INTO <%= backupTableName %> SELECT <%= attributeNames %> FROM <%= tableName %>;',
|
||
|
|
'DROP TABLE <%= tableName %>;',
|
||
|
|
this.createTableQuery(tableName, attributes),
|
||
|
|
'INSERT INTO <%= tableName %> SELECT <%= attributeNames %> FROM <%= backupTableName %>;',
|
||
|
|
'DROP TABLE <%= backupTableName %>;'
|
||
|
|
].join('');
|
||
|
|
|
||
|
|
return Utils._.template(query)({
|
||
|
|
tableName: this.quoteTable(tableName),
|
||
|
|
backupTableName: this.quoteTable(backupTableName),
|
||
|
|
attributeNames: Utils._.keys(attributes).join(', ')
|
||
|
|
});
|
||
|
|
},
|
||
|
|
|
||
|
|
renameColumnQuery: function(tableName, attrNameBefore, attrNameAfter, attributes) {
|
||
|
|
var backupTableName
|
||
|
|
, query;
|
||
|
|
|
||
|
|
attributes = this.attributesToSQL(attributes);
|
||
|
|
|
||
|
|
if (typeof tableName === 'object') {
|
||
|
|
backupTableName = {
|
||
|
|
tableName: tableName.tableName + '_backup',
|
||
|
|
schema: tableName.schema
|
||
|
|
};
|
||
|
|
} else {
|
||
|
|
backupTableName = tableName + '_backup';
|
||
|
|
}
|
||
|
|
|
||
|
|
query = [
|
||
|
|
this.createTableQuery(backupTableName, attributes).replace('CREATE TABLE', 'CREATE TEMPORARY TABLE'),
|
||
|
|
'INSERT INTO <%= backupTableName %> SELECT <%= attributeNamesImport %> FROM <%= tableName %>;',
|
||
|
|
'DROP TABLE <%= tableName %>;',
|
||
|
|
this.createTableQuery(tableName, attributes),
|
||
|
|
'INSERT INTO <%= tableName %> SELECT <%= attributeNamesExport %> FROM <%= backupTableName %>;',
|
||
|
|
'DROP TABLE <%= backupTableName %>;'
|
||
|
|
].join('');
|
||
|
|
|
||
|
|
return Utils._.template(query)({
|
||
|
|
tableName: this.quoteTable(tableName),
|
||
|
|
backupTableName: this.quoteTable(backupTableName),
|
||
|
|
attributeNamesImport: Utils._.keys(attributes).map(function(attr) {
|
||
|
|
return (attrNameAfter === attr) ? this.quoteIdentifier(attrNameBefore) + ' AS ' + this.quoteIdentifier(attr) : this.quoteIdentifier(attr);
|
||
|
|
}.bind(this)).join(', '),
|
||
|
|
attributeNamesExport: Utils._.keys(attributes).map(function(attr) {
|
||
|
|
return this.quoteIdentifier(attr);
|
||
|
|
}.bind(this)).join(', ')
|
||
|
|
});
|
||
|
|
},
|
||
|
|
|
||
|
|
startTransactionQuery: function(transaction, options) {
|
||
|
|
if (options.parent) {
|
||
|
|
return 'SAVEPOINT ' + this.quoteIdentifier(transaction.name) + ';';
|
||
|
|
}
|
||
|
|
|
||
|
|
return 'BEGIN TRANSACTION;';
|
||
|
|
},
|
||
|
|
|
||
|
|
setAutocommitQuery: function() {
|
||
|
|
return '-- SQLite does not support SET autocommit.';
|
||
|
|
},
|
||
|
|
|
||
|
|
setIsolationLevelQuery: function(value) {
|
||
|
|
switch (value) {
|
||
|
|
case Transaction.ISOLATION_LEVELS.REPEATABLE_READ:
|
||
|
|
return '-- SQLite is not able to choose the isolation level REPEATABLE READ.';
|
||
|
|
case Transaction.ISOLATION_LEVELS.READ_UNCOMMITTED:
|
||
|
|
return 'PRAGMA read_uncommitted = ON;';
|
||
|
|
case Transaction.ISOLATION_LEVELS.READ_COMMITTED:
|
||
|
|
return 'PRAGMA read_uncommitted = OFF;';
|
||
|
|
case Transaction.ISOLATION_LEVELS.SERIALIZABLE:
|
||
|
|
return "-- SQLite's default isolation level is SERIALIZABLE. Nothing to do.";
|
||
|
|
default:
|
||
|
|
throw new Error('Unknown isolation level: ' + value);
|
||
|
|
}
|
||
|
|
},
|
||
|
|
|
||
|
|
replaceBooleanDefaults: function(sql) {
|
||
|
|
return sql.replace(/DEFAULT '?false'?/g, 'DEFAULT 0').replace(/DEFAULT '?true'?/g, 'DEFAULT 1');
|
||
|
|
},
|
||
|
|
|
||
|
|
quoteIdentifier: function(identifier) {
|
||
|
|
if (identifier === '*') return identifier;
|
||
|
|
return Utils.addTicks(identifier, '`');
|
||
|
|
},
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Generates an SQL query that returns all foreign keys of a table.
|
||
|
|
*
|
||
|
|
* @param {String} tableName The name of the table.
|
||
|
|
* @param {String} schemaName The name of the schema.
|
||
|
|
* @return {String} The generated sql query.
|
||
|
|
*/
|
||
|
|
getForeignKeysQuery: function(tableName, schemaName) {
|
||
|
|
var sql = 'PRAGMA foreign_key_list(<%= tableName %>)';
|
||
|
|
return Utils._.template(sql)({ tableName: tableName });
|
||
|
|
}
|
||
|
|
};
|
||
|
|
|
||
|
|
module.exports = Utils._.extend({}, MySqlQueryGenerator, QueryGenerator);
|