ghost/core/core/server/data/schema/commands.js
const _ = require('lodash');
const logging = require('@tryghost/logging');
const errors = require('@tryghost/errors');
const tpl = require('@tryghost/tpl');
const db = require('../db');
const DatabaseInfo = require('@tryghost/database-info');
const schema = require('./schema');
const messages = {
hasPrimaryKeySQLiteError: 'Must use hasPrimaryKeySQLite on an SQLite3 database',
hasForeignSQLite3: 'Must use hasForeignSQLite3 on an SQLite3 database',
noSupportForDatabase: 'No support for database client {client}'
};
/**
* @param {string} tableName
* @param {import('knex').knex.TableBuilder} tableBuilder
* @param {string} columnName
* @param {object} [columnSpec]
*/
function addTableColumn(tableName, tableBuilder, columnName, columnSpec = schema[tableName][columnName]) {
let column;
// creation distinguishes between text with fieldtype, string with maxlength and all others
if (columnSpec.type === 'text' && Object.prototype.hasOwnProperty.call(columnSpec, 'fieldtype')) {
column = tableBuilder[columnSpec.type](columnName, columnSpec.fieldtype);
} else if (columnSpec.type === 'string') {
if (Object.prototype.hasOwnProperty.call(columnSpec, 'maxlength')) {
column = tableBuilder[columnSpec.type](columnName, columnSpec.maxlength);
} else {
column = tableBuilder[columnSpec.type](columnName, 191);
}
} else {
column = tableBuilder[columnSpec.type](columnName);
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'nullable') && columnSpec.nullable === true) {
column.nullable();
} else {
column.nullable(false);
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'primary') && columnSpec.primary === true) {
column.primary();
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'unique') && columnSpec.unique) {
column.unique();
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'unsigned') && columnSpec.unsigned) {
column.unsigned();
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'references')) {
// check if table exists?
column.references(columnSpec.references);
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'constraintName')) {
column.withKeyName(columnSpec.constraintName);
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'cascadeDelete') && columnSpec.cascadeDelete === true) {
column.onDelete('CASCADE');
} else if (Object.prototype.hasOwnProperty.call(columnSpec, 'setNullDelete') && columnSpec.setNullDelete === true) {
column.onDelete('SET NULL');
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'defaultTo')) {
column.defaultTo(columnSpec.defaultTo);
}
if (Object.prototype.hasOwnProperty.call(columnSpec, 'index') && columnSpec.index === true) {
column.index();
}
}
/**
* @param {string} tableName
* @param {string} column
* @param {import('knex').Knex} [transaction]
*/
function setNullable(tableName, column, transaction = db.knex) {
return transaction.schema.table(tableName, function (table) {
table.setNullable(column);
});
}
/**
* @param {string} tableName
* @param {string} column
* @param {import('knex').Knex} [transaction]
*/
function dropNullable(tableName, column, transaction = db.knex) {
return transaction.schema.table(tableName, function (table) {
table.dropNullable(column);
});
}
/**
* @param {string} tableName
* @param {string} column
* @param {import('knex').Knex.Transaction} [transaction]
* @param {object} columnSpec
*/
async function addColumn(tableName, column, transaction = db.knex, columnSpec) {
const addColumnBuilder = transaction.schema.table(tableName, function (table) {
addTableColumn(tableName, table, column, columnSpec);
});
// Use the default flow for SQLite because .toSQL() is tricky with SQLite when
// it does the table dance
if (DatabaseInfo.isSQLite(transaction)) {
await addColumnBuilder;
return;
}
for (const sqlQuery of addColumnBuilder.toSQL()) {
let sql = sqlQuery.sql;
if (DatabaseInfo.isMySQL(transaction)) {
// Guard against an ending semicolon
sql = sql.replace(/;\s*$/, '') + ', algorithm=copy';
}
await transaction.raw(sql);
}
}
/**
* @param {string} tableName
* @param {string} column
* @param {import('knex').Knex} [transaction]
* @param {object} [columnSpec]
*/
async function dropColumn(tableName, column, transaction = db.knex, columnSpec = {}) {
if (Object.prototype.hasOwnProperty.call(columnSpec, 'references')) {
const [toTable, toColumn] = columnSpec.references.split('.');
await dropForeign({fromTable: tableName, fromColumn: column, toTable, toColumn, constraintName: columnSpec.constraintName, transaction});
}
const dropColumnBuilder = transaction.schema.table(tableName, function (table) {
table.dropColumn(column);
});
// Use the default flow for SQLite because .toSQL() is tricky with SQLite when
// it does the table dance
if (DatabaseInfo.isSQLite(transaction)) {
await dropColumnBuilder;
return;
}
for (const sqlQuery of dropColumnBuilder.toSQL()) {
let sql = sqlQuery.sql;
if (DatabaseInfo.isMySQL(transaction)) {
// Guard against an ending semicolon
sql = sql.replace(/;\s*$/, '') + ', algorithm=copy';
}
await transaction.raw(sql);
}
}
/**
* @param {string} tableName
* @param {string} from
* @param {string} to
* @param {import('knex').Knex.Transaction} [transaction]
*/
async function renameColumn(tableName, from, to, transaction = db.knex) {
logging.info(`Renaming column '${from}' to '${to}' in table '${tableName}'`);
if (DatabaseInfo.isMySQL(transaction)) {
// The knex helper does a lot of interesting things with foreign keys that are slow on bigger MySQL clusters
return await transaction.raw(`ALTER TABLE \`${tableName}\` RENAME COLUMN \`${from}\` TO \`${to}\`;`);
}
return await transaction.schema.table(tableName, function (table) {
table.renameColumn(from, to);
});
}
/**
* Adds an non-unique index to a table over the given columns.
*
* @param {string} tableName - name of the table to add indexes to
* @param {string|string[]} columns - column(s) to add indexes for
* @param {import('knex').Knex} [transaction] - connection object containing knex reference
*/
async function addIndex(tableName, columns, transaction = db.knex) {
try {
logging.info(`Adding index for '${columns}' in table '${tableName}'`);
return await transaction.schema.table(tableName, function (table) {
table.index(columns);
});
} catch (err) {
if (err.code === 'SQLITE_ERROR') {
logging.warn(`Index for '${columns}' already exists for table '${tableName}'`);
return;
}
if (err.code === 'ER_DUP_KEYNAME') {
logging.warn(`Index for '${columns}' already exists for table '${tableName}'`);
return;
}
throw err;
}
}
/**
* Drops a non-unique index from a table over the given columns.
*
* @param {string} tableName - name of the table to remove indexes from
* @param {string|string[]} columns - column(s) to remove indexes for
* @param {import('knex').Knex} [transaction] - connection object containing knex reference
*/
async function dropIndex(tableName, columns, transaction = db.knex) {
try {
logging.info(`Dropping index for '${columns}' in table '${tableName}'`);
return await transaction.schema.table(tableName, function (table) {
table.dropIndex(columns);
});
} catch (err) {
if (err.code === 'SQLITE_ERROR') {
logging.warn(`Constraint for '${columns}' does not exist for table '${tableName}'`);
return;
}
if (err.code === 'ER_CANT_DROP_FIELD_OR_KEY') {
logging.warn(`Constraint for '${columns}' does not exist for table '${tableName}'`);
return;
}
throw err;
}
}
/**
* Adds an unique index to a table over the given columns.
*
* @param {string} tableName - name of the table to add unique constraint to
* @param {string|string[]} columns - column(s) to form unique constraint with
* @param {import('knex').Knex} [transaction] - connection object containing knex reference
*/
async function addUnique(tableName, columns, transaction = db.knex) {
try {
logging.info(`Adding unique constraint for '${columns}' in table '${tableName}'`);
return await transaction.schema.table(tableName, function (table) {
table.unique(columns);
});
} catch (err) {
if (err.code === 'SQLITE_ERROR') {
logging.warn(`Constraint for '${columns}' already exists for table '${tableName}'`);
return;
}
if (err.code === 'ER_DUP_KEYNAME') {
logging.warn(`Constraint for '${columns}' already exists for table '${tableName}'`);
return;
}
throw err;
}
}
/**
* Drops a unique key constraint from a table.
*
* @param {string} tableName - name of the table to drop unique constraint from
* @param {string|string[]} columns - column(s) unique constraint was formed
* @param {import('knex').Knex} transaction - connection object containing knex reference
*/
async function dropUnique(tableName, columns, transaction = db.knex) {
try {
logging.info(`Dropping unique constraint for '${columns}' in table '${tableName}'`);
return await transaction.schema.table(tableName, function (table) {
table.dropUnique(columns);
});
} catch (err) {
if (err.code === 'SQLITE_ERROR') {
logging.warn(`Constraint for '${columns}' does not exist for table '${tableName}'`);
return;
}
if (err.code === 'ER_CANT_DROP_FIELD_OR_KEY') {
logging.warn(`Constraint for '${columns}' does not exist for table '${tableName}'`);
return;
}
throw err;
}
}
/**
* Checks if a foreign key exists in a table over the given columns.
*
* @param {Object} configuration - contains all configuration for this function
* @param {string} configuration.fromTable - name of the table to add the foreign key to
* @param {string} configuration.fromColumn - column of the table to add the foreign key to
* @param {string} configuration.toTable - name of the table to point the foreign key to
* @param {string} configuration.toColumn - column of the table to point the foreign key to
* @param {import('knex').Knex} [configuration.transaction] - connection object containing knex reference
*/
async function hasForeignSQLite({fromTable, fromColumn, toTable, toColumn, transaction = db.knex}) {
if (!DatabaseInfo.isSQLite(transaction)) {
throw new errors.InternalServerError({
message: tpl(messages.hasForeignSQLite3)
});
}
const foreignKeys = await transaction.raw(`PRAGMA foreign_key_list('${fromTable}');`);
const hasForeignKey = foreignKeys.some(foreignKey => foreignKey.table === toTable && foreignKey.from === fromColumn && foreignKey.to === toColumn);
return hasForeignKey;
}
/**
* Adds a foreign key to a table.
*
* @param {Object} configuration - contains all configuration for this function
* @param {string} configuration.fromTable - name of the table to add the foreign key to
* @param {string} configuration.fromColumn - column of the table to add the foreign key to
* @param {string} configuration.toTable - name of the table to point the foreign key to
* @param {string} configuration.toColumn - column of the table to point the foreign key to
* @param {string} [configuration.constraintName] - name of the FK to create
* @param {Boolean} [configuration.cascadeDelete] - adds the "on delete cascade" option if true
* @param {Boolean} [configuration.setNullDelete] - adds the "on delete SET NULL" option if true
* @param {import('knex').Knex} [configuration.transaction] - connection object containing knex reference
*/
async function addForeign({fromTable, fromColumn, toTable, toColumn, constraintName, cascadeDelete = false, setNullDelete = false, transaction = db.knex}) {
if (DatabaseInfo.isSQLite(transaction)) {
const foreignKeyExists = await hasForeignSQLite({fromTable, fromColumn, toTable, toColumn, transaction});
if (foreignKeyExists) {
logging.warn(`Skipped adding foreign key from ${fromTable}.${fromColumn} to ${toTable}.${toColumn} - already exists`);
return;
}
}
try {
logging.info(`Adding foreign key from ${fromTable}.${fromColumn} to ${toTable}.${toColumn}`);
//disable and re-enable foreign key checks on sqlite because of https://github.com/knex/knex/issues/4155
let foreignKeysEnabled;
if (DatabaseInfo.isSQLite(transaction)) {
foreignKeysEnabled = await db.knex.raw('PRAGMA foreign_keys;');
if (foreignKeysEnabled[0].foreign_keys) {
await db.knex.raw('PRAGMA foreign_keys = OFF;');
}
}
await transaction.schema.table(fromTable, function (table) {
let fkBuilder;
if (cascadeDelete) {
fkBuilder = table.foreign(fromColumn).references(`${toTable}.${toColumn}`).onDelete('CASCADE');
} else if (setNullDelete) {
fkBuilder = table.foreign(fromColumn).references(`${toTable}.${toColumn}`).onDelete('SET NULL');
} else {
fkBuilder = table.foreign(fromColumn).references(`${toTable}.${toColumn}`);
}
if (constraintName) {
fkBuilder.withKeyName(constraintName);
}
});
if (DatabaseInfo.isSQLite(transaction)) {
if (foreignKeysEnabled[0].foreign_keys) {
await db.knex.raw('PRAGMA foreign_keys = ON;');
}
}
} catch (err) {
if (err.code === 'ER_DUP_KEY' || err.code === 'ER_FK_DUP_KEY' || err.code === 'ER_FK_DUP_NAME') {
logging.warn(`Skipped adding foreign key from ${fromTable}.${fromColumn} to ${toTable}.${toColumn} - already exists`);
return;
}
throw err;
}
}
/**
* Drops a foreign key from a table.
*
* @param {Object} configuration - contains all configuration for this function
* @param {string} configuration.fromTable - name of the table to add the foreign key to
* @param {string} configuration.fromColumn - column of the table to add the foreign key to
* @param {string} configuration.toTable - name of the table to point the foreign key to
* @param {string} configuration.toColumn - column of the table to point the foreign key to
* @param {string} [configuration.constraintName] - name of the FK to delete
* @param {import('knex').Knex} [configuration.transaction] - connection object containing knex reference
*/
async function dropForeign({fromTable, fromColumn, toTable, toColumn, constraintName, transaction = db.knex}) {
if (DatabaseInfo.isSQLite(transaction)) {
const foreignKeyExists = await hasForeignSQLite({fromTable, fromColumn, toTable, toColumn, transaction});
if (!foreignKeyExists) {
logging.warn(`Skipped dropping foreign key from ${fromTable}.${fromColumn} to ${toTable}.${toColumn} - does not exist`);
return;
}
}
try {
logging.info(`Dropping foreign key from ${fromTable}.${fromColumn} to ${toTable}.${toColumn}`);
//disable and re-enable foreign key checks on sqlite because of https://github.com/knex/knex/issues/4155
let foreignKeysEnabled;
if (DatabaseInfo.isSQLite(transaction)) {
foreignKeysEnabled = await db.knex.raw('PRAGMA foreign_keys;');
if (foreignKeysEnabled[0].foreign_keys) {
await db.knex.raw('PRAGMA foreign_keys = OFF;');
}
}
await transaction.schema.table(fromTable, function (table) {
table.dropForeign(fromColumn, constraintName);
});
if (DatabaseInfo.isSQLite(transaction)) {
if (foreignKeysEnabled[0].foreign_keys) {
await db.knex.raw('PRAGMA foreign_keys = ON;');
}
}
} catch (err) {
if (err.code === 'ER_CANT_DROP_FIELD_OR_KEY') {
logging.warn(`Skipped dropping foreign key from ${fromTable}.${fromColumn} to ${toTable}.${toColumn} - does not exist`);
return;
}
throw err;
}
}
/**
* Checks if primary key index exists in a table over the given columns.
*
* @param {string} tableName - name of the table to check primary key constraint on
* @param {import('knex').Knex} [transaction] - connection object containing knex reference
*/
async function hasPrimaryKeySQLite(tableName, transaction = db.knex) {
if (!DatabaseInfo.isSQLite(transaction)){
throw new errors.InternalServerError({
message: tpl(messages.hasPrimaryKeySQLiteError)
});
}
const rawConstraints = await transaction.raw(`PRAGMA index_list('${tableName}');`);
const tablePrimaryKey = rawConstraints.find(c => c.origin === 'pk');
return tablePrimaryKey;
}
/**
* Adds an primary key index to a table over the given columns.
*
* @param {string} tableName - name of the table to add primaykey constraint to
* @param {string|string[]} columns - column(s) to form primary key constraint with
* @param {import('knex').Knex} [transaction] - connection object containing knex reference
*/
async function addPrimaryKey(tableName, columns, transaction = db.knex) {
if (DatabaseInfo.isSQLite(transaction)) {
const primaryKeyExists = await hasPrimaryKeySQLite(tableName, transaction);
if (primaryKeyExists) {
logging.warn(`Primary key constraint for '${columns}' already exists for table '${tableName}'`);
return;
}
}
try {
logging.info(`Adding primary key constraint for '${columns}' in table '${tableName}'`);
return await transaction.schema.table(tableName, function (table) {
table.primary(columns);
});
} catch (err) {
if (err.code === 'ER_MULTIPLE_PRI_KEY') {
logging.warn(`Primary key constraint for '${columns}' already exists for table '${tableName}'`);
return;
}
throw err;
}
}
/**
* Adds a table according to the provided spec, or falls back to the current schema
*
* NOTE: this function does NOT check if the table already exists - use the migration
* utils if you want that
*
* @param {String} table - name of the table to create
* @param {import('knex').Knex} [transaction] - connection to the DB
* @param {Object} [tableSpec] - table schema to generate table with
*/
function createTable(table, transaction = db.knex, tableSpec = schema[table]) {
return transaction.schema.createTable(table, function (t) {
Object.keys(tableSpec)
.filter(column => !(column.startsWith('@@')))
.forEach(column => addTableColumn(table, t, column, tableSpec[column]));
if (tableSpec['@@INDEXES@@']) {
tableSpec['@@INDEXES@@'].forEach(index => t.index(index));
}
if (tableSpec['@@UNIQUE_CONSTRAINTS@@']) {
tableSpec['@@UNIQUE_CONSTRAINTS@@'].forEach(unique => t.unique(unique));
}
});
}
/**
* @param {string} table
* @param {import('knex').Knex} [transaction] - connection to the DB
*/
function deleteTable(table, transaction = db.knex) {
return transaction.schema.dropTableIfExists(table);
}
/**
* @param {import('knex').Knex} [transaction] - connection to the DB
*/
async function getTables(transaction = db.knex) {
const client = transaction.client.config.client;
if (client === 'sqlite3') {
const response = await transaction.raw('select * from sqlite_master where type = "table"');
return _.reject(_.map(response, 'tbl_name'), name => name === 'sqlite_sequence');
} else if (client === 'mysql2') {
const response = await transaction.raw('show tables');
return _.flatten(_.map(response[0], entry => _.values(entry)));
}
return Promise.reject(tpl(messages.noSupportForDatabase, {client: client}));
}
/**
* @param {string} table
* @param {import('knex').Knex} [transaction] - connection to the DB
*/
async function getIndexes(table, transaction = db.knex) {
const client = transaction.client.config.client;
if (client === 'sqlite3') {
const response = await transaction.raw(`pragma index_list("${table}")`);
return _.flatten(_.map(response, 'name'));
} else if (client === 'mysql2') {
const response = await transaction.raw(`SHOW INDEXES from ${table}`);
return _.flatten(_.map(response[0], 'Key_name'));
}
return Promise.reject(tpl(messages.noSupportForDatabase, {client: client}));
}
/**
* @param {string} table
* @param {import('knex').Knex} [transaction] - connection to the DB
*/
async function getColumns(table, transaction = db.knex) {
const client = transaction.client.config.client;
if (client === 'sqlite3') {
const response = await transaction.raw(`pragma table_info("${table}")`);
return _.flatten(_.map(response, 'name'));
} else if (client === 'mysql2') {
const response = await transaction.raw(`SHOW COLUMNS from ${table}`);
return _.flatten(_.map(response[0], 'Field'));
}
return Promise.reject(tpl(messages.noSupportForDatabase, {client: client}));
}
function createColumnMigration(...migrations) {
async function runColumnMigration(conn, migration) {
const {
table,
column,
dbIsInCorrectState,
operation,
operationVerb,
columnDefinition
} = migration;
const hasColumn = await conn.schema.hasColumn(table, column);
const isInCorrectState = dbIsInCorrectState(hasColumn);
if (isInCorrectState) {
logging.warn(`${operationVerb} ${table}.${column} column - skipping as table is correct`);
} else {
logging.info(`${operationVerb} ${table}.${column} column`);
await operation(table, column, conn, columnDefinition);
}
}
return async function columnMigration(conn) {
for (const migration of migrations) {
await runColumnMigration(conn, migration);
}
};
}
module.exports = {
createTable,
deleteTable,
getTables,
getIndexes,
addUnique,
dropUnique,
addIndex,
dropIndex,
addPrimaryKey,
addForeign,
dropForeign,
addColumn,
renameColumn,
dropColumn,
setNullable,
dropNullable,
getColumns,
createColumnMigration,
// NOTE: below are exposed for testing purposes only
_hasForeignSQLite: hasForeignSQLite,
_hasPrimaryKeySQLite: hasPrimaryKeySQLite
};