/**
* Command line module.
*
* Check database structure for application domain. Generate DDL (both create and alter) if need and optionally run it
*
* WARNING: do not run this command on production database in automatic mode - always review SQL script manually before run
* @example
/from a command line
>ub cmd/generateDDL -u admin -p admin -autorun
//from a script
var DDLGenerator = require('cmd/generateDDL');
var options = {
host: "http://localhost:888",
user: "admin",
pwd: "admin",
out: process.cwd(),
autorun: true
};
DDLGenerator(options);
* @author pavel.mash
* @module cmd/generateDDL
*/
var
assert = require('assert'),
fs = require('fs'),
http = require('http'),
cmdLineOpt = require('cmd/options'),
argv = require('./argv');
module.exports = function generateDDL(options){
var
session, conn, outputPath;
if (!options){
var opts = cmdLineOpt.describe('cmd/generateDDL', 'Check database structure for application domain. Generate DDL (both create and alter) if need and optionally run it')
.add(argv.establishConnectionFromCmdLineAttributes._cmdLineParams)
.add({short: 'm', long: 'models', param: 'modelsList', defaultValue: '*', help: 'Comma separated model names for DDL generation. If -e specified this options is ignored'})
.add({short: 'e', long: 'entities', param: 'entitiesList', defaultValue: '*', help: 'Comma separated entity names list for DDL generation'})
.add({short: 'out', long: 'out', param: 'outputPath', defaultValue: process.cwd(), help: 'Folder to output generated DDLs (one file per connection)'})
.add({short: 'autorun', long: 'autorun', defaultValue: false, help: 'execute DDL statement after generation. BE CAREFUL! DO NOT USE ON PRODUCTION'});
options = opts.parseVerbose({}, true);
if (!options) return;
}
// increase receive timeout to 120s - in case DB server is slow we can easy reach 30s timeout
http.setGlobalConnectionDefaults({receiveTimeout: 120000});
session = argv.establishConnectionFromCmdLineAttributes(options);
conn = session.connection;
outputPath = options.out;
var inEntities = options.entities;
var inModels = options.models;
var config,
appConfig = null,
autorun = options.autorun;
try {
if (autorun){
if (!session.__serverStartedByMe){
console.error('-autorun parameter can be used only for a server started locally. Generation stopped');
return;
}
config = argv.getServerConfiguration();
appConfig = config.application;
}
runDDLGenerator(conn, appConfig, inEntities, inModels, outputPath);
} finally {
if (session && session.logout){
session.logout();
}
}
};
/**
* @param {UBConnection} conn
* @param {Object} [appConfig] Optional server config application section (used in `auto` mode to execute DDL statements)
* @param {String} inEntities
* @param {String} inModels
* @param {String} outputPath
*/
function runDDLGenerator(conn, appConfig, inEntities, inModels, outputPath){
"use strict";
var
ddlResult, fileName,
txtRes, i, stmtNum, urlParams = '',
canRun = (appConfig !== null),
domainInfo,
stmts = [],
entities = [],
models = [],
connectionConfig;
domainInfo = conn.getDomainInfo();
if (!inEntities && !inModels){
entities = Object.keys(domainInfo.domain);
} else {
if (inEntities){ // add passed entities
entities = inEntities.split(',') || [];
}
if (inModels){ // add all entities from passed models
models = inModels.split(',') || [];
_.forEach(domainInfo.domain, function(entity, key){
if (models.indexOf(entity.modelName) >=0) {
entities.push(key);
}
});
}
}
entities = _.uniq(entities);
console.log('Check congruence for entities metadata and database structure for: ', JSON.stringify(entities));
ddlResult = conn.post('generateDDL', {unsafe: true, resultAsObject: true, entities: entities });
console.debug('server generate response - create script for all connection');
i = 0;
Object.keys(ddlResult).forEach(function (connectionName) {
fileName = outputPath + connectionName + '_' + i + '.sql';
if (ddlResult[connectionName].warnings.length){
console.warn('There are warnings. Please, review script ' + fileName);
}
txtRes = formatAsText(connectionName, ddlResult[connectionName]);
if (txtRes){
fs.writeFileSync(fileName, txtRes);
console.log('Created a script ' + fileName);
if (canRun){
console.log('Run a script ' + fileName);
urlParams = {CONNECTION: connectionName};
// Oracle do not allow to execute several DDL statement in one call
stmts = txtRes.split('--\r\n'); stmtNum = 1;
stmts.forEach(function(stmt){
stmt = stmt.trim();
stmt && conn.xhr({endpoint: 'runSQL', data: stmt, URLParams: urlParams});
stmtNum++;
});
console.info('Database script', fileName, 'executed successfully');
}
} else {
console.log('Specified entity metadata is congruence with database for connection ' + connectionName);
fs.unlinkSync(fileName);
}
i++;
});
}
/**
* Format a result of runDDLGenerator as a single SQL file
* @param {String} connectionName
* @param {Object} connResult
*/
function formatAsText(connectionName, connResult) {
"use strict";
var
txtRes = [];
function formatOutCode(arrOfString, comment) {
if (typeof arrOfString === 'undefined'){
txtRes.push('ERR: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ' + comment);
return false;
}
if (arrOfString.length) {
arrOfString.push(' '); // for last delimiter
txtRes.push(
'-- ' + comment,
'--#############################################################',
arrOfString.join(';\r\n--\r\n')
);
return true;
}
return false;
}
if (connResult.warnings.length > 0) {
txtRes.push('/*\r\n $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ \r\n Attantion! Achtung! Vnimanie! ',
'\r\n', connResult.warnings.join('\r\n'),
'\r\n $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ \r\n*/\r\n');
}
formatOutCode(connResult.sysSQL, 'System objects');
formatOutCode(connResult.dropFkSQL, 'Drop foreign keys');
formatOutCode(connResult.dropIndexSQL, 'Drop indexes');
formatOutCode(connResult.dropPkSQL, 'Drop primary keys');
formatOutCode(connResult.dropDefaultSQL, 'Drop default constraint');
formatOutCode(connResult.dropCheckCSQL, 'Drop check constraint');
formatOutCode(connResult.dropTriggerSQL, 'Drop triggers');
formatOutCode(connResult.dropSequnceSQL, 'Drop sequence');
formatOutCode(connResult.createTableSQL, 'Create tables');
formatOutCode(connResult.addColumnSQL, 'Add columns');
formatOutCode(connResult.alterColumnSQL, 'Alter columns');
formatOutCode(connResult.updateColumnSQL, '! update values for known changes or estimated changes :-)');
formatOutCode(connResult.renameSQL, 'Renamed objects');
formatOutCode(connResult.setDefaultSQL, 'Set new default');
formatOutCode(connResult.alterColumnNotNullSQL, 'Alter columns set not null where was null allowed');
formatOutCode(connResult.createPkSQL, 'Create primary keys');
formatOutCode(connResult.createIndexSQL, 'Create indexes');
formatOutCode(connResult.createCheckCSQL, 'Create check constraint');
formatOutCode(connResult.createFkSQL, 'Create foreign keys');
formatOutCode(connResult.createSequenceSQL, 'Create sequence');
formatOutCode(connResult.createTriggerSQL, 'Create triggers');
formatOutCode(connResult.othersSQL, 'Create other object defined in dbExtensions section entity domain');
formatOutCode(connResult.dropColumnSQL, 'drop columns');
formatOutCode(connResult.captionSQL, 'caption');
if (txtRes.length){
return '--############## start script for conection "' + connectionName + '" #######\r\n' + txtRes.join('\r\n');
} else {
return '';
}
}