var logger = require('../config/logger')('migration');
-var Sequence = function () {};
+var Sequence = function() {};
-Sequence.prototype.enqueue = function (fn) {
+Sequence.prototype.enqueue = function(fn) {
this.tail = this.tail ? this.tail.finally(fn) : fn();
};
-var Migration = function (queryInterface) {
+var Migration = function(queryInterface) {
this.queryInterface = queryInterface;
this.sequence = new Sequence();
};
-Migration.prototype.changeColumn = function (table, column, type) {
+Migration.prototype.changeColumn = function(table, column, type) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface
.changeColumn(table, column, type)
- .then(function () {
+ .then(function() {
logger.info('Changed column %s in table %s', column, table);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(JSON.stringify(err));
});
});
};
-Migration.prototype.addColumn = function (table, column, type) {
+Migration.prototype.addColumn = function(table, column, type) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface
.addColumn(table, column, type)
- .then(function () {
+ .then(function() {
logger.info('Added column %s to %s', column, table);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(JSON.stringify(err));
});
});
};
-Migration.prototype.dropTable = function (table) {
+Migration.prototype.dropTable = function(table) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface
.dropTable(table, {
force: true
})
- .then(function () {
+ .then(function() {
logger.info('table dropped %s', table);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(JSON.stringify(err));
});
});
};
-Migration.prototype.addIndex = function (table, column, indexName) {
+Migration.prototype.addIndex = function(table, column, indexName) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface.addIndex(table, column, {
indexName: indexName
})
- .then(function () {
+ .then(function() {
logger.info('addIndex %s %s %s', table, column.join(','), indexName);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(JSON.stringify(err));
});
});
};
-Migration.prototype.removeIndex = function (table, indexName) {
+Migration.prototype.removeIndex = function(table, indexName) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface.removeIndex(table, indexName)
- .then(function () {
+ .then(function() {
logger.info('removeIndex %s %s', table, indexName);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(JSON.stringify(err));
});
});
};
-Migration.prototype.query = function (sql) {
+Migration.prototype.query = function(sql) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface.sequelize.query(sql)
- .then(function () {
+ .then(function() {
logger.info('query %s', sql);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(JSON.stringify(err));
});
});
};
-Migration.prototype.removeColumn = function (table, column) {
+Migration.prototype.removeColumn = function(table, column) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface.removeColumn(table, column)
- .then(function () {
+ .then(function() {
logger.info('Removed column %s from %s', column, table);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(util.inspect(err, {
showHidden: false,
depth: null
});
};
-Migration.prototype.renameColumn = function (table, oldColumn, newColumn) {
+Migration.prototype.renameColumn = function(table, oldColumn, newColumn) {
var _this = this;
- this.sequence.enqueue(function () {
+ this.sequence.enqueue(function() {
return _this.queryInterface.renameColumn(table, oldColumn, newColumn)
- .then(function () {
+ .then(function() {
logger.info('Renamed column from %s to %s on %s', oldColumn, newColumn, table);
})
- .catch(function (err) {
+ .catch(function(err) {
logger.info(util.inspect(err, {
showHidden: false,
depth: null
});
};
-Migration.prototype.final = function (resolve) {
- this.sequence.enqueue(function () {
+Migration.prototype.final = function(resolve) {
+ this.sequence.enqueue(function() {
return resolve();
});
};
module.exports = {
up: function(queryInterface, Sequelize) {
- return new BPromise(function(resolve) {
- var migration = new Migration(queryInterface);
-
- // START EXTRACTED REPORTS
- migration.addColumn('analytics_extracted_reports', 'reportId', {
- type: Sequelize.INTEGER
- });
-
- migration.addColumn('analytics_extracted_reports', 'reportType', {
- type: Sequelize.STRING
- });
- // END EXTRACTED REPORTS
-
- // START USERS
- migration.addColumn('users', 'userProfileId', {
- type: Sequelize.INTEGER
- });
- // END USERS
-
- // START VOICEMAILS
- migration.removeColumn('voice_voicemail', 'name');
- // END VOICEMAILS
-
- // START USER_PROFILES
- migration.query('CREATE TABLE `user_profiles` (' +
- ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
- ' `name` varchar(255) NOT NULL,' +
- ' `crudPermissions` varchar(255) NOT NULL DEFAULT \'r\',' +
- ' `description` varchar(255) DEFAULT NULL,' +
- ' `createdAt` datetime NOT NULL,' +
- ' `updatedAt` datetime NOT NULL,' +
- ' PRIMARY KEY (`id`),' +
- ' UNIQUE KEY `name` (`name`)' +
- ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
-
- migration.query('INSERT INTO `user_profiles` (`name`, `crudPermissions`, `description`, `createdAt`, `updatedAt`)' +
- 'VALUES (\'Default\', \'red\', NULL, NOW(), NOW());');
-
- migration.query('ALTER TABLE `users` ' +
- 'ADD CONSTRAINT `users_ibfk_1` ' +
- 'FOREIGN KEY (`userProfileId`) ' +
- 'REFERENCES user_profiles(`id`) ' +
- 'ON UPDATE CASCADE');
-
- migration.query('CREATE TABLE `user_profile_has_sections` (' +
- ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
- ' `name` varchar(255) NOT NULL,' +
- ' `category` varchar(255) NOT NULL,' +
- ' `sectionId` int(11) NOT NULL,' +
- ' `enabled` tinyint(1) DEFAULT NULL,' +
- ' `autoAssociation` tinyint(1) DEFAULT NULL,' +
- ' `crudPermissions` varchar(255) DEFAULT NULL,' +
- ' `createdAt` datetime NOT NULL,' +
- ' `updatedAt` datetime NOT NULL,' +
- ' `userProfileId` int(11) DEFAULT NULL,' +
- ' PRIMARY KEY (`id`),' +
- ' KEY `userProfileId` (`userProfileId`),' +
- ' KEY `compositeIndex` (`sectionId`,`userProfileId`),' +
- ' CONSTRAINT `user_profile_has_sections_ibfk_1` FOREIGN KEY (`userProfileId`) REFERENCES `user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE' +
- ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
-
- migration.query('CREATE TABLE `user_profile_has_resources` (' +
- ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
- ' `name` varchar(255) NOT NULL,' +
- ' `resourceId` int(11) NOT NULL,' +
- ' `type` varchar(255) NOT NULL,' +
- ' `createdAt` datetime NOT NULL,' +
- ' `updatedAt` datetime NOT NULL,' +
- ' `sectionId` int(11) DEFAULT NULL,' +
- ' PRIMARY KEY (`id`),' +
- ' KEY `sectionId` (`sectionId`),' +
- ' KEY `compositeIndex` (`resourceId`,`sectionId`),' +
- ' CONSTRAINT `user_profile_has_resources_ibfk_1` FOREIGN KEY (`sectionId`) REFERENCES `user_profile_has_sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE' +
- ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
-
- migration.query('CREATE TABLE tally (' +
- ' n INT NOT NULL AUTO_INCREMENT PRIMARY KEY' +
- ');');
-
- migration.query('INSERT INTO tally (n)' +
- 'SELECT NULL FROM' +
- '(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a' +
- ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b' +
- ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c' +
- ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d' +
- ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e' +
- ';');
-
- migration.query('CREATE TABLE tmpPermissions' +
- ' (' +
- ' userIds varchar(255) not null,' +
- ' permissions text not null,' +
- ' profileId int(11)' +
- ' );');
-
- migration.query('INSERT INTO tmpPermissions' +
- ' SELECT id, permissions, null' +
- ' FROM users' +
- ' WHERE role = \'user\' and permissions is not null and permissions <> \'\'' +
- ' ORDER BY id;');
-
- migration.query('CREATE TABLE tmpNormalizedSections' +
- ' (' +
- ' sectionId int(11) not null,' +
- ' userId text,' +
- ' resourceId text' +
- ' );');
-
- migration.query('INSERT INTO tmpNormalizedSections' +
- ' SELECT DISTINCT permission, userId, 0' +
- ' FROM (SELECT' +
- ' SUBSTRING_INDEX(SUBSTRING_INDEX(p.permissions, \',\', tally.n), \',\', -1) permission,' +
- ' p.userIds userId' +
- ' FROM tally INNER JOIN tmpPermissions p ON CHAR_LENGTH(p.permissions) - CHAR_LENGTH(REPLACE(p.permissions, \',\', \'\')) >= tally.n-1' +
- ' ORDER BY n) AS split;');
-
- migration.query('CREATE TABLE tmpNormalizedPermissions' +
- ' (' +
- ' permissions text not null,' +
- ' userId int(11) not null' +
- ' );');
-
- migration.query('INSERT INTO tmpNormalizedPermissions' +
- ' SELECT GROUP_CONCAT(sectionId ORDER BY sectionId), userId' +
- ' FROM tmpNormalizedSections' +
- ' GROUP BY userId;');
-
- migration.query('UPDATE users u, tmpNormalizedPermissions t' +
- ' SET u.permissions = t.permissions' +
- ' WHERE u.id = t.userId;');
-
- migration.query('TRUNCATE TABLE tmpPermissions;');
-
- migration.query('INSERT INTO tmpPermissions' +
- ' SELECT GROUP_CONCAT(id), permissions, null' +
- ' FROM users' +
- ' WHERE role = \'user\' and permissions is not null and permissions <> \'\'' +
- ' GROUP BY permissions' +
- ' ORDER BY GROUP_CONCAT(id);');
-
- migration.query('CREATE TABLE tmpNormalizedUsers' +
- ' (' +
- ' sectionId int(11) not null,' +
- ' userId text,' +
- ' resourceId text' +
- ' );');
-
- migration.query('INSERT INTO tmpNormalizedUsers' +
- ' SELECT permission, userId, 0' +
- ' FROM (SELECT' +
- ' s.sectionId permission,' +
- ' SUBSTRING_INDEX(SUBSTRING_INDEX(s.userId, \',\', t.n), \',\', -1) userId' +
- ' FROM tally t INNER JOIN tmpNormalizedSections s ON CHAR_LENGTH(s.userId) - CHAR_LENGTH(REPLACE(s.userId, \',\', \'\')) >= t.n-1' +
- ' ORDER BY n) AS split;');
-
-
- migration.query('CREATE TABLE tmpExtractedResources (' +
- ' sectionId int(11) not null,' +
- ' resourceId int(11),' +
- ' userId int(11)' +
- ' );');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.VoiceQueueId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_voice_queues u ON u.UserId = x.userId' +
- ' INNER JOIN voice_queues q ON u.VoiceQueueId = q.id' +
- ' WHERE x.sectionId = 401 AND q.type = \'inbound\';');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.ChatQueueId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_chat_queues u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 501;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.ChatWebsiteId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_chat_websites u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 502;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.MailQueueId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_mail_queues u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 601;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.MailAccountId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_mail_accounts u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 602;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.SmsQueueId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_sms_queues u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 701;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.SmsAccountId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_sms_accounts u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 702;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.OpenchannelQueueId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_openchannel_queues u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 801;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.OpenchannelAccountId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_openchannel_accounts u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 802;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.FaxQueueId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_fax_queues u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 901;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.FaxAccountId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_fax_accounts u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 902;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.SquareProjectId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_square_projects u ON u.UserId = x.userId' +
- ' WHERE x.sectionId = 1103;');
-
- migration.query('INSERT INTO tmpExtractedResources' +
- ' SELECT x.sectionId, u.VoiceQueueId, x.userId' +
- ' FROM tmpNormalizedUsers x' +
- ' INNER JOIN user_has_voice_queues u ON u.UserId = x.userId' +
- ' INNER JOIN voice_queues q ON u.VoiceQueueId = q.id' +
- ' WHERE x.sectionId = 1501 AND q.type = \'outbound\';');
-
- migration.query('CREATE TABLE tmpGroupedPermissions' +
- ' (' +
- ' userId text not null,' +
- ' permissions text not null' +
- ' );');
-
- migration.query('INSERT INTO tmpGroupedPermissions' +
- ' SELECT userId, permissions' +
- ' FROM (SELECT' +
- ' SUBSTRING_INDEX(SUBSTRING_INDEX(t.userIds, \',\', tally.n), \',\', -1) userId,' +
- ' t.permissions permissions' +
- ' FROM tally INNER JOIN tmpPermissions t ON CHAR_LENGTH(t.userIds) - CHAR_LENGTH(REPLACE(t.userIds, \',\', \'\')) >= tally.n-1' +
- ' ORDER BY n) AS split;');
-
- migration.query('CREATE TABLE tmpSharedProfiles' +
- ' (' +
- ' userIds varchar(255) not null,' +
- ' permissions text not null,' +
- ' profileId int(11)' +
- ' );');
-
- migration.query('INSERT INTO tmpSharedProfiles' +
- ' SELECT GROUP_CONCAT(userId), permissions, null' +
- ' FROM tmpGroupedPermissions' +
- ' WHERE userId NOT IN (SELECT split.userId' +
- ' FROM (SELECT' +
- ' SUBSTRING_INDEX(SUBSTRING_INDEX(r.userIds, \',\', t.n), \',\', -1) userId' +
- ' FROM tally t' +
- ' INNER JOIN tmpPermissions r on CHAR_LENGTH(r.userIds) - CHAR_LENGTH(REPLACE(r.userIds, \',\', \'\')) >= t.n-1' +
- ' WHERE POSITION(\',\' in r.userIds) > 0' +
- ' ORDER BY n) AS split' +
- ' WHERE split.userId IN (SELECT * FROM' +
- ' (SELECT GROUP_CONCAT(userId ORDER BY userId ASC) AS users' +
- ' FROM tmpNormalizedUsers' +
- ' GROUP BY resourceId, sectionId' +
- ' ORDER BY users) b' +
- ' WHERE position(\',\' in b.users) = 0))' +
- ' GROUP BY permissions' +
- ' ORDER BY GROUP_CONCAT(userId);');
-
- migration.query('CREATE TABLE tmpSingleProfiles' +
- ' (' +
- ' userId text not null,' +
- ' permissions text not null,' +
- ' profileId int(11)' +
- ' );');
-
- migration.query('INSERT INTO tmpSingleProfiles' +
- ' SELECT userId, permissions, null' +
- ' FROM tmpGroupedPermissions' +
- ' WHERE userId IN (SELECT split.userId' +
- ' FROM (select SUBSTRING_INDEX(SUBSTRING_INDEX(r.userIds, \',\', t.n), \',\', -1) userId' +
- ' FROM tally t INNER JOIN tmpPermissions r ON CHAR_LENGTH(r.userIds) - CHAR_LENGTH(REPLACE(r.userIds, \',\', \'\')) >= t.n-1' +
- ' WHERE POSITION(\',\' in r.userIds) > 0' +
- ' ORDER BY n) as split' +
- ' WHERE split.userId IN (SELECT * FROM' +
- ' (SELECT GROUP_CONCAT(userId ORDER BY userId ASC) as users' +
- ' FROM tmpNormalizedUsers' +
- ' GROUP BY resourceId, sectionId' +
- ' ORDER BY users) b' +
- ' WHERE position(\',\' in b.users) = 0))' +
- ' ORDER BY userId;');
-
- migration.query('CREATE TABLE tmpFinalProfiles' +
- ' (' +
- ' userId text not null,' +
- ' permissions text not null,' +
- ' profileId int(11)' +
- ' );');
-
- migration.query('INSERT INTO tmpFinalProfiles' +
- ' SELECT * FROM tmpSharedProfiles' +
- ' UNION' +
- ' SELECT * FROM tmpSingleProfiles;');
-
- migration.query('INSERT INTO user_profiles (name, crudPermissions, createdAt, updatedAt)' +
- ' SELECT CONCAT(\'Profile_\', (SELECT (@cnt := @cnt + 1) AS rowNumber' +
- ' FROM tmpFinalProfiles t' +
- ' CROSS JOIN (SELECT @cnt := 0) as dummy LIMIT 1)), \'red\', now(), now()' +
- ' FROM tmpFinalProfiles;');
-
- migration.query('UPDATE tmpFinalProfiles' +
- ' SET profileId = (SELECT (@cnt := @cnt + 1) AS rowNumber' +
- ' FROM user_profiles u' +
- ' CROSS JOIN (SELECT @cnt := 1) as dummy LIMIT 1)');
- // migration.query('UPDATE tmpFinalProfiles' +
- // ' SET profileId = (@firstId := ifnull(@firstId, 1) + 1);');
-
- migration.query('CREATE TABLE tmpSections' +
- ' (' +
- ' name varchar(255) not null,' +
- ' category varchar(255) not null,' +
- ' sectionId int(11) not null,' +
- ' enabled tinyint not null,' +
- ' autoAssociation tinyint not null,' +
- ' userProfileId int(11) not null,' +
- ' insertedId int(11),' +
- ' userId text' +
- ' );');
-
- migration.query('INSERT INTO tmpSections' +
- ' SELECT \'\', \'\', permission, 1, 0, profileId, null, userId' +
- ' FROM (SELECT' +
- ' fp.profileId profileId,' +
- ' SUBSTRING_INDEX(SUBSTRING_INDEX(fp.permissions, \',\', tally.n), \',\', -1) permission,' +
- ' SUBSTRING_INDEX(fp.userId, \',\', 1) userId' +
- ' FROM tally' +
- ' INNER JOIN tmpFinalProfiles fp ON CHAR_LENGTH(fp.permissions) - CHAR_LENGTH(REPLACE(fp.permissions, \',\', \'\')) >= tally.n-1' +
- ' ORDER BY n) AS split;');
-
- migration.query('UPDATE tmpSections' +
- ' SET' +
- ' name = (SELECT' +
- ' CASE' +
- ' WHEN sectionId = 100 THEN \'Dashboards\'' +
- ' WHEN sectionId = 101 THEN \'DELETE\'' +
- ' WHEN sectionId = 200 THEN \'DELETE\'' +
- ' WHEN sectionId = 201 THEN \'DELETE\'' +
- ' WHEN sectionId = 202 THEN \'Agents\'' +
- ' WHEN sectionId = 203 THEN \'Telephones\'' +
- ' WHEN sectionId = 204 THEN \'Teams\'' +
- ' WHEN sectionId = 300 THEN \'DELETE\'' +
- ' WHEN sectionId = 301 THEN \'Lists\'' +
- ' WHEN sectionId = 302 THEN \'Companies\'' +
- ' WHEN sectionId = 303 THEN \'Contacts\'' +
- ' WHEN sectionId = 304 THEN \'GlobalCustomFields\'' +
- ' WHEN sectionId = 400 THEN \'DELETE\'' +
- ' WHEN sectionId = 401 THEN \'VoiceQueues\'' +
- ' WHEN sectionId = 402 THEN \'InboundRoutes\'' +
- ' WHEN sectionId = 403 THEN \'OutboundRoutes\'' +
- ' WHEN sectionId = 404 THEN \'InternalRoutes\'' +
- ' WHEN sectionId = 405 THEN \'Contexts\'' +
- ' WHEN sectionId = 406 THEN \'Voicemails\'' +
- ' WHEN sectionId = 407 THEN \'MusicOnHolds\'' +
- ' WHEN sectionId = 408 THEN \'VoiceRecordings\'' +
- ' WHEN sectionId = 409 THEN \'ChanSpies\'' +
- ' WHEN sectionId = 410 THEN \'VoicePrefixes\'' +
- ' WHEN sectionId = 411 THEN \'Realtime\'' +
- ' WHEN sectionId = 500 THEN \'DELETE\'' +
- ' WHEN sectionId = 501 THEN \'ChatQueues\'' +
- ' WHEN sectionId = 502 THEN \'ChatWebsites\'' +
- ' WHEN sectionId = 510 THEN \'Realtime\'' +
- ' WHEN sectionId = 600 THEN \'DELETE\'' +
- ' WHEN sectionId = 601 THEN \'MailQueues\'' +
- ' WHEN sectionId = 602 THEN \'MailAccounts\'' +
- ' WHEN sectionId = 603 THEN \'MailSubstatuses\'' +
- ' WHEN sectionId = 610 THEN \'Realtime\'' +
- ' WHEN sectionId = 700 THEN \'DELETE\'' +
- ' WHEN sectionId = 701 THEN \'SmsQueues\'' +
- ' WHEN sectionId = 702 THEN \'SmsAccounts\'' +
- ' WHEN sectionId = 710 THEN \'Realtime\'' +
- ' WHEN sectionId = 800 THEN \'DELETE\'' +
- ' WHEN sectionId = 801 THEN \'OpenchannelQueues\'' +
- ' WHEN sectionId = 802 THEN \'OpenchannelAccounts\'' +
- ' WHEN sectionId = 810 THEN \'Realtime\'' +
- ' WHEN sectionId = 900 THEN \'DELETE\'' +
- ' WHEN sectionId = 901 THEN \'FaxQueues\'' +
- ' WHEN sectionId = 902 THEN \'FaxAccounts\'' +
- ' WHEN sectionId = 910 THEN \'Realtime\'' +
- ' WHEN sectionId = 1000 THEN \'DELETE\'' +
- ' WHEN sectionId = 1001 THEN \'CannedAnswers\'' +
- ' WHEN sectionId = 1002 THEN \'Dispositions\'' +
- ' WHEN sectionId = 1003 THEN \'CustomDashboards\'' +
- ' WHEN sectionId = 1004 THEN \'Intervals\'' +
- ' WHEN sectionId = 1005 THEN \'Pauses\'' +
- ' WHEN sectionId = 1006 THEN \'Scheduler\'' +
- ' WHEN sectionId = 1007 THEN \'Sounds\'' +
- ' WHEN sectionId = 1008 THEN \'Tags\'' +
- ' WHEN sectionId = 1009 THEN \'Templates\'' +
- ' WHEN sectionId = 1010 THEN \'Triggers\'' +
- ' WHEN sectionId = 1011 THEN \'Trunks\'' +
- ' WHEN sectionId = 1012 THEN \'Variables\'' +
- ' WHEN sectionId = 1100 THEN \'DELETE\'' +
- ' WHEN sectionId = 1101 THEN \'ODBC\'' +
- ' WHEN sectionId = 1102 THEN \'SquareRecordings\'' +
- ' WHEN sectionId = 1103 THEN \'SquareProjects\'' +
- ' WHEN sectionId = 1200 THEN \'DELETE\'' +
- ' WHEN sectionId = 1201 THEN \'Metrics\'' +
- ' WHEN sectionId = 1202 THEN \'ExtractedReports\'' +
- ' WHEN sectionId = 1203 THEN \'Reports\'' +
- ' WHEN sectionId = 1300 THEN \'DELETE\'' +
- ' WHEN sectionId = 1301 THEN \'ZendeskAccounts\'' +
- ' WHEN sectionId = 1302 THEN \'SalesforceAccounts\'' +
- ' WHEN sectionId = 1303 THEN \'FreshdeskAccounts\'' +
- ' WHEN sectionId = 1305 THEN \'SugarcrmAccounts\'' +
- ' WHEN sectionId = 1306 THEN \'DeskAccounts\'' +
- ' WHEN sectionId = 1307 THEN \'ZohoAccounts\'' +
- ' WHEN sectionId = 1308 THEN \'VtigerAccounts\'' +
- ' WHEN sectionId = 1400 THEN \'Settings\'' +
- ' WHEN sectionId in (1401, 1402, 1403, 1404, 1405, 1406, 1407) THEN \'DELETE\'' +
- ' WHEN sectionId = 1500 THEN \'DELETE\'' +
- ' WHEN sectionId = 1501 THEN \'QueueCampaigns\'' +
- ' WHEN sectionId = 1502 THEN \'IvrCampaigns\'' +
- ' WHEN sectionId = 1510 THEN \'Realtime\'' +
- ' WHEN sectionId = 1600 THEN \'Help\'' +
- ' WHEN sectionId = 1601 THEN \'DELETE\'' +
- ' WHEN sectionId = 1700 THEN \'DELETE\'' +
- ' WHEN sectionId = 1701 THEN \'Projects\'' +
- ' WHEN sectionId between 88889 AND 99999 THEN \'Plugins\'' +
- ' WHEN sectionId >= 100000 THEN \'CustomDashboards\'' +
- ' ELSE \'\'' +
- ' END' +
- ' ),' +
- ' category = (SELECT' +
- ' CASE' +
- ' WHEN sectionId = 100 THEN \'Dashboards\'' +
- ' WHEN sectionId >= 100000 THEN \'Tools\'' +
- ' WHEN sectionId in (202, 203, 204) THEN \'Staff\'' +
- ' WHEN sectionId in (301, 302, 303, 304) THEN \'ContactManager\'' +
- ' WHEN sectionId in (401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411) THEN \'Voice\'' +
- ' WHEN sectionId in (501, 502, 510) THEN \'Chat\'' +
- ' WHEN sectionId in (601, 602, 603, 610) THEN \'Mail\'' +
- ' WHEN sectionId in (701, 702, 710) THEN \'Sms\'' +
- ' WHEN sectionId in (801, 802, 810) THEN \'Openchannel\'' +
- ' WHEN sectionId in (901, 902, 910) THEN \'Fax\'' +
- ' WHEN sectionId in (1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012) THEN \'Tools\'' +
- ' WHEN sectionId in (1101, 1102, 1103) THEN \'CallySquare\'' +
- ' WHEN sectionId in (1201, 1202, 1203) THEN \'Analytics\'' +
- ' WHEN sectionId in (1301, 1302, 1303, 1305, 1306, 1307, 1308) THEN \'Integrations\'' +
- ' WHEN sectionId = 1400 THEN \'Settings\'' +
- ' WHEN sectionId in (1501, 1502, 1510) THEN \'MotionDialer\'' +
- ' WHEN sectionId = 1600 THEN \'Help\'' +
- ' WHEN sectionId = 1701 THEN \'jscripty\'' +
- ' WHEN sectionId between 88889 AND 99999 THEN \'AppZone\'' +
- ' ELSE \'\'' +
- ' END' +
- ' );');
-
- migration.query('DELETE FROM tmpSections' +
- ' WHERE name = \'DELETE\';');
-
- migration.query('CREATE TABLE tmpCustomResources' +
- ' (' +
- ' name varchar(255) not null,' +
- ' category varchar(255) not null,' +
- ' sectionId int(11) not null,' +
- ' enabled tinyint not null,' +
- ' autoAssociation tinyint not null,' +
- ' userProfileId int(11) not null,' +
- ' insertedId int(11),' +
- ' userId text' +
- ' );');
-
- migration.query('INSERT INTO tmpCustomResources' +
- ' SELECT *' +
- ' FROM tmpSections' +
- ' WHERE name in (\'Plugins\', \'CustomDashboards\');');
-
- migration.query('DELETE t1 FROM tmpSections t1, tmpCustomResources t2' +
- ' WHERE t1.name = t2.name AND t1.userProfileId = t2.userProfileId AND t1.sectionId > t2.sectionId;');
-
- migration.query('UPDATE tmpSections' +
- ' SET sectionId = 1801 WHERE sectionId between 88889 AND 99999;');
-
- migration.query('UPDATE tmpSections' +
- ' SET sectionId = 1003 WHERE sectionId >= 100000;');
-
- migration.query('UPDATE tmpSections' +
- ' SET autoAssociation = 1 WHERE sectionId = 100;');
-
- migration.query('UPDATE tmpCustomResources' +
- ' SET insertedId = (SELECT' +
- ' CASE' +
- ' WHEN name = \'Plugins\' THEN (sectionId - 88888)' +
- ' WHEN name = \'CustomDashboards\' THEN (sectionId - 99999)' +
- ' END' +
- ' );');
-
- migration.query('INSERT INTO user_profile_has_sections (name, category, sectionId, enabled, autoAssociation, crudPermissions, createdAt, updatedAt, userProfileId)' +
- ' SELECT name, category, sectionId, enabled, autoAssociation, \'red\', now(), now(), userProfileId' +
- ' FROM tmpSections;');
-
- migration.query('UPDATE tmpSections s' +
- ' SET insertedId = (SELECT id' +
- ' FROM user_profile_has_sections x' +
- ' WHERE s.userProfileId = x.userProfileId AND s.sectionId = x.sectionId);');
-
- migration.query('UPDATE tmpCustomResources r, tmpSections s' +
- ' SET r.sectionId = s.insertedId' +
- ' WHERE r.name = s.name AND r.userProfileId = s.userProfileId;');
-
- migration.query('CREATE TABLE tmpResources' +
- ' (' +
- ' resourceId text,' +
- ' type varchar(255) not null,' +
- ' insertedSectionId int(11) not null,' +
- ' sectionId int(11)' +
- ' );');
-
- migration.query('INSERT INTO tmpResources' +
- ' SELECT cr.resourceId, name, s.insertedId, s.sectionId' +
- ' FROM tmpSections s' +
- ' INNER JOIN tmpExtractedResources cr ON cr.userId = s.userId AND cr.sectionId = s.sectionId AND cr.resourceId IS NOT NULL;');
-
- migration.query('CREATE TABLE tmpNormalizedResources' +
- ' (' +
- ' name varchar(255) not null,' +
- ' resourceId int(11) not null,' +
- ' type varchar(255) not null,' +
- ' insertedSectionId int(11) not null,' +
- ' sectionId int(11)' +
- ' );');
-
- migration.query('INSERT INTO tmpNormalizedResources' +
- ' SELECT \'\', resourceId, type, insertedSectionId, sectionId' +
- ' FROM (SELECT' +
- ' SUBSTRING_INDEX(SUBSTRING_INDEX(r.resourceId, \',\', tally.n), \',\', -1) resourceId,' +
- ' r.type type,' +
- ' r.insertedSectionId insertedSectionId,' +
- ' r.sectionId sectionId' +
- ' FROM tally' +
- ' INNER JOIN tmpResources r ON CHAR_LENGTH(r.resourceId) - CHAR_LENGTH(REPLACE(r.resourceId, \',\', \'\')) >= tally.n-1' +
- ' ORDER BY n) AS split;');
-
- migration.query('INSERT INTO tmpNormalizedResources' +
- ' SELECT \'\', insertedId, name, sectionId, null' +
- ' FROM tmpCustomResources;');
-
- migration.query('UPDATE tmpNormalizedResources x' +
- ' SET name = (SELECT' +
- ' CASE' +
- ' WHEN x.sectionId = 401 THEN COALESCE((SELECT name FROM voice_queues WHERE id = x.resourceId AND type = \'inbound\'),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 501 THEN COALESCE((SELECT name FROM chat_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 502 THEN COALESCE((SELECT name FROM chat_websites WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 601 THEN COALESCE((SELECT name FROM mail_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 602 THEN COALESCE((SELECT name FROM mail_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 701 THEN COALESCE((SELECT name FROM sms_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 702 THEN COALESCE((SELECT name FROM sms_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 801 THEN COALESCE((SELECT name FROM openchannel_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 802 THEN COALESCE((SELECT name FROM openchannel_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 901 THEN COALESCE((SELECT name FROM fax_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 902 THEN COALESCE((SELECT name FROM fax_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 1103 THEN COALESCE((SELECT name FROM square_projects WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.sectionId = 1501 THEN COALESCE((SELECT name FROM voice_queues WHERE id = x.resourceId AND type = \'outbound\'),\'DELETE_MIGRATION\')' +
- ' WHEN x.type = \'Plugins\' THEN COALESCE((SELECT name FROM plugins WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' WHEN x.type = \'CustomDashboards\' THEN COALESCE((SELECT name FROM dashboards WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
- ' END' +
- ' );');
-
- migration.query('DELETE FROM tmpNormalizedResources' +
- ' WHERE name = \'DELETE_MIGRATION\';');
-
- migration.query('INSERT INTO user_profile_has_resources (name, resourceId, type, createdAt, updatedAt, sectionId)' +
- ' SELECT name, resourceId, type, now(), now(), insertedSectionId' +
- ' FROM tmpNormalizedResources;');
-
- migration.query('UPDATE users u, tmpFinalProfiles f' +
- ' SET u.userProfileId = f.profileId' +
- ' WHERE u.id = f.userId;');
-
- migration.query('UPDATE users u' +
- ' SET u.userProfileId = 1' +
- ' WHERE userProfileId is null AND role = \'user\';');
-
- migration.query(' DROP TABLE IF EXISTS tally;');
-
- migration.query('DROP TABLE IF EXISTS tmpPermissions;');
-
- migration.query('DROP TABLE IF EXISTS tmpNormalizedSections;');
-
- migration.query('DROP TABLE IF EXISTS tmpNormalizedUsers;');
-
- migration.query('DROP TABLE IF EXISTS tmpExtractedResources;');
-
- migration.query('DROP TABLE IF EXISTS tmpGroupedPermissions;');
-
- migration.query('DROP TABLE IF EXISTS tmpSharedProfiles;');
-
- migration.query('DROP TABLE IF EXISTS tmpSingleProfiles;');
-
- migration.query('DROP TABLE IF EXISTS tmpFinalProfiles;');
-
- migration.query('DROP TABLE IF EXISTS tmpSections;');
-
- migration.query('DROP TABLE IF EXISTS tmpCustomResources;');
-
- migration.query('DROP TABLE IF EXISTS tmpResources;');
-
- migration.query('DROP TABLE IF EXISTS tmpNormalizedResources;');
- // END USER_PROFILES
-
- // START FINAL
- migration.final(resolve);
- // END FINAL
+ return new BPromise(function(resolve) {
+ var migration = new Migration(queryInterface);
+
+ // START EXTRACTED REPORTS
+ migration.addColumn('analytics_extracted_reports', 'reportId', {
+ type: Sequelize.INTEGER
+ });
+
+ migration.addColumn('analytics_extracted_reports', 'reportType', {
+ type: Sequelize.STRING
+ });
+ // END EXTRACTED REPORTS
+
+ // START USERS
+ migration.addColumn('users', 'userProfileId', {
+ type: Sequelize.INTEGER
});
+ // END USERS
+
+ // START VOICEMAILS
+ migration.removeColumn('voice_voicemail', 'name');
+ // END VOICEMAILS
+
+ // START USER_PROFILES
+ migration.query('CREATE TABLE `user_profiles` (' +
+ ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
+ ' `name` varchar(255) NOT NULL,' +
+ ' `crudPermissions` varchar(255) NOT NULL DEFAULT \'r\',' +
+ ' `description` varchar(255) DEFAULT NULL,' +
+ ' `createdAt` datetime NOT NULL,' +
+ ' `updatedAt` datetime NOT NULL,' +
+ ' PRIMARY KEY (`id`),' +
+ ' UNIQUE KEY `name` (`name`)' +
+ ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
+
+ migration.query('INSERT INTO `user_profiles` (`name`, `crudPermissions`, `description`, `createdAt`, `updatedAt`)' +
+ 'VALUES (\'Default\', \'red\', NULL, NOW(), NOW());');
+
+ migration.query('ALTER TABLE `users` ' +
+ 'ADD CONSTRAINT `users_ibfk_1` ' +
+ 'FOREIGN KEY (`userProfileId`) ' +
+ 'REFERENCES user_profiles(`id`) ' +
+ 'ON UPDATE CASCADE');
+
+ migration.query('CREATE TABLE `user_profile_has_sections` (' +
+ ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
+ ' `name` varchar(255) NOT NULL,' +
+ ' `category` varchar(255) NOT NULL,' +
+ ' `sectionId` int(11) NOT NULL,' +
+ ' `enabled` tinyint(1) DEFAULT NULL,' +
+ ' `autoAssociation` tinyint(1) DEFAULT NULL,' +
+ ' `crudPermissions` varchar(255) DEFAULT NULL,' +
+ ' `createdAt` datetime NOT NULL,' +
+ ' `updatedAt` datetime NOT NULL,' +
+ ' `userProfileId` int(11) DEFAULT NULL,' +
+ ' PRIMARY KEY (`id`),' +
+ ' KEY `userProfileId` (`userProfileId`),' +
+ ' KEY `compositeIndex` (`sectionId`,`userProfileId`),' +
+ ' CONSTRAINT `user_profile_has_sections_ibfk_1` FOREIGN KEY (`userProfileId`) REFERENCES `user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE' +
+ ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
+
+ migration.query('CREATE TABLE `user_profile_has_resources` (' +
+ ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
+ ' `name` varchar(255) NOT NULL,' +
+ ' `resourceId` int(11) NOT NULL,' +
+ ' `type` varchar(255) NOT NULL,' +
+ ' `createdAt` datetime NOT NULL,' +
+ ' `updatedAt` datetime NOT NULL,' +
+ ' `sectionId` int(11) DEFAULT NULL,' +
+ ' PRIMARY KEY (`id`),' +
+ ' KEY `sectionId` (`sectionId`),' +
+ ' KEY `compositeIndex` (`resourceId`,`sectionId`),' +
+ ' CONSTRAINT `user_profile_has_resources_ibfk_1` FOREIGN KEY (`sectionId`) REFERENCES `user_profile_has_sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE' +
+ ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
+
+ migration.query('CREATE TABLE tally (' +
+ ' n INT NOT NULL AUTO_INCREMENT PRIMARY KEY' +
+ ');');
+
+ migration.query('INSERT INTO tally (n)' +
+ 'SELECT NULL FROM' +
+ '(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a' +
+ ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b' +
+ ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c' +
+ ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d' +
+ ',(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e' +
+ ';');
+
+ migration.query('CREATE TABLE tmpPermissions' +
+ ' (' +
+ ' userIds varchar(255) not null,' +
+ ' permissions text not null,' +
+ ' profileId int(11)' +
+ ' );');
+
+ migration.query('INSERT INTO tmpPermissions' +
+ ' SELECT id, permissions, null' +
+ ' FROM users' +
+ ' WHERE role = \'user\' and permissions is not null and permissions <> \'\'' +
+ ' ORDER BY id;');
+
+ migration.query('CREATE TABLE tmpNormalizedSections' +
+ ' (' +
+ ' sectionId int(11) not null,' +
+ ' userId text,' +
+ ' resourceId text' +
+ ' );');
+
+ migration.query('INSERT INTO tmpNormalizedSections' +
+ ' SELECT DISTINCT permission, userId, 0' +
+ ' FROM (SELECT' +
+ ' SUBSTRING_INDEX(SUBSTRING_INDEX(p.permissions, \',\', tally.n), \',\', -1) permission,' +
+ ' p.userIds userId' +
+ ' FROM tally INNER JOIN tmpPermissions p ON CHAR_LENGTH(p.permissions) - CHAR_LENGTH(REPLACE(p.permissions, \',\', \'\')) >= tally.n-1' +
+ ' ORDER BY n) AS split;');
+
+ migration.query('CREATE TABLE tmpNormalizedPermissions' +
+ ' (' +
+ ' permissions text not null,' +
+ ' userId int(11) not null' +
+ ' );');
+
+ migration.query('INSERT INTO tmpNormalizedPermissions' +
+ ' SELECT GROUP_CONCAT(sectionId ORDER BY sectionId), userId' +
+ ' FROM tmpNormalizedSections' +
+ ' GROUP BY userId;');
+
+ migration.query('UPDATE users u, tmpNormalizedPermissions t' +
+ ' SET u.permissions = t.permissions' +
+ ' WHERE u.id = t.userId;');
+
+ migration.query('TRUNCATE TABLE tmpPermissions;');
+
+ migration.query('INSERT INTO tmpPermissions' +
+ ' SELECT GROUP_CONCAT(id), permissions, null' +
+ ' FROM users' +
+ ' WHERE role = \'user\' and permissions is not null and permissions <> \'\'' +
+ ' GROUP BY permissions' +
+ ' ORDER BY GROUP_CONCAT(id);');
+
+ migration.query('CREATE TABLE tmpNormalizedUsers' +
+ ' (' +
+ ' sectionId int(11) not null,' +
+ ' userId text,' +
+ ' resourceId text' +
+ ' );');
+
+ migration.query('INSERT INTO tmpNormalizedUsers' +
+ ' SELECT permission, userId, 0' +
+ ' FROM (SELECT' +
+ ' s.sectionId permission,' +
+ ' SUBSTRING_INDEX(SUBSTRING_INDEX(s.userId, \',\', t.n), \',\', -1) userId' +
+ ' FROM tally t INNER JOIN tmpNormalizedSections s ON CHAR_LENGTH(s.userId) - CHAR_LENGTH(REPLACE(s.userId, \',\', \'\')) >= t.n-1' +
+ ' ORDER BY n) AS split;');
+
+
+ migration.query('CREATE TABLE tmpExtractedResources (' +
+ ' sectionId int(11) not null,' +
+ ' resourceId int(11),' +
+ ' userId int(11)' +
+ ' );');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.VoiceQueueId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_voice_queues u ON u.UserId = x.userId' +
+ ' INNER JOIN voice_queues q ON u.VoiceQueueId = q.id' +
+ ' WHERE x.sectionId = 401 AND q.type = \'inbound\';');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.ChatQueueId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_chat_queues u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 501;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.ChatWebsiteId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_chat_websites u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 502;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.MailQueueId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_mail_queues u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 601;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.MailAccountId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_mail_accounts u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 602;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.SmsQueueId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_sms_queues u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 701;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.SmsAccountId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_sms_accounts u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 702;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.OpenchannelQueueId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_openchannel_queues u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 801;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.OpenchannelAccountId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_openchannel_accounts u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 802;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.FaxQueueId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_fax_queues u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 901;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.FaxAccountId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_fax_accounts u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 902;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.SquareProjectId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_square_projects u ON u.UserId = x.userId' +
+ ' WHERE x.sectionId = 1103;');
+
+ migration.query('INSERT INTO tmpExtractedResources' +
+ ' SELECT x.sectionId, u.VoiceQueueId, x.userId' +
+ ' FROM tmpNormalizedUsers x' +
+ ' INNER JOIN user_has_voice_queues u ON u.UserId = x.userId' +
+ ' INNER JOIN voice_queues q ON u.VoiceQueueId = q.id' +
+ ' WHERE x.sectionId = 1501 AND q.type = \'outbound\';');
+
+ migration.query('CREATE TABLE tmpGroupedPermissions' +
+ ' (' +
+ ' userId text not null,' +
+ ' permissions text not null' +
+ ' );');
+
+ migration.query('INSERT INTO tmpGroupedPermissions' +
+ ' SELECT userId, permissions' +
+ ' FROM (SELECT' +
+ ' SUBSTRING_INDEX(SUBSTRING_INDEX(t.userIds, \',\', tally.n), \',\', -1) userId,' +
+ ' t.permissions permissions' +
+ ' FROM tally INNER JOIN tmpPermissions t ON CHAR_LENGTH(t.userIds) - CHAR_LENGTH(REPLACE(t.userIds, \',\', \'\')) >= tally.n-1' +
+ ' ORDER BY n) AS split;');
+
+ migration.query('CREATE TABLE tmpSharedProfiles' +
+ ' (' +
+ ' userIds varchar(255) not null,' +
+ ' permissions text not null,' +
+ ' profileId int(11)' +
+ ' );');
+
+ migration.query('INSERT INTO tmpSharedProfiles' +
+ ' SELECT GROUP_CONCAT(userId), permissions, null' +
+ ' FROM tmpGroupedPermissions' +
+ ' WHERE userId NOT IN (SELECT split.userId' +
+ ' FROM (SELECT' +
+ ' SUBSTRING_INDEX(SUBSTRING_INDEX(r.userIds, \',\', t.n), \',\', -1) userId' +
+ ' FROM tally t' +
+ ' INNER JOIN tmpPermissions r on CHAR_LENGTH(r.userIds) - CHAR_LENGTH(REPLACE(r.userIds, \',\', \'\')) >= t.n-1' +
+ ' WHERE POSITION(\',\' in r.userIds) > 0' +
+ ' ORDER BY n) AS split' +
+ ' WHERE split.userId IN (SELECT * FROM' +
+ ' (SELECT GROUP_CONCAT(userId ORDER BY userId ASC) AS users' +
+ ' FROM tmpNormalizedUsers' +
+ ' GROUP BY resourceId, sectionId' +
+ ' ORDER BY users) b' +
+ ' WHERE position(\',\' in b.users) = 0))' +
+ ' GROUP BY permissions' +
+ ' ORDER BY GROUP_CONCAT(userId);');
+
+ migration.query('CREATE TABLE tmpSingleProfiles' +
+ ' (' +
+ ' userId text not null,' +
+ ' permissions text not null,' +
+ ' profileId int(11)' +
+ ' );');
+
+ migration.query('INSERT INTO tmpSingleProfiles' +
+ ' SELECT userId, permissions, null' +
+ ' FROM tmpGroupedPermissions' +
+ ' WHERE userId IN (SELECT split.userId' +
+ ' FROM (select SUBSTRING_INDEX(SUBSTRING_INDEX(r.userIds, \',\', t.n), \',\', -1) userId' +
+ ' FROM tally t INNER JOIN tmpPermissions r ON CHAR_LENGTH(r.userIds) - CHAR_LENGTH(REPLACE(r.userIds, \',\', \'\')) >= t.n-1' +
+ ' WHERE POSITION(\',\' in r.userIds) > 0' +
+ ' ORDER BY n) as split' +
+ ' WHERE split.userId IN (SELECT * FROM' +
+ ' (SELECT GROUP_CONCAT(userId ORDER BY userId ASC) as users' +
+ ' FROM tmpNormalizedUsers' +
+ ' GROUP BY resourceId, sectionId' +
+ ' ORDER BY users) b' +
+ ' WHERE position(\',\' in b.users) = 0))' +
+ ' ORDER BY userId;');
+
+ migration.query('CREATE TABLE tmpFinalProfiles' +
+ ' (' +
+ ' userId text not null,' +
+ ' permissions text not null,' +
+ ' profileId int(11)' +
+ ' );');
+
+ migration.query('INSERT INTO tmpFinalProfiles' +
+ ' SELECT * FROM tmpSharedProfiles' +
+ ' UNION' +
+ ' SELECT * FROM tmpSingleProfiles;');
+
+ migration.query('INSERT INTO user_profiles (name, crudPermissions, createdAt, updatedAt)' +
+ ' SELECT CONCAT(\'Profile_\', (SELECT (@cnt := @cnt + 1) AS rowNumber' +
+ ' FROM tmpFinalProfiles t' +
+ ' CROSS JOIN (SELECT @cnt := 0) as dummy LIMIT 1)), \'red\', now(), now()' +
+ ' FROM tmpFinalProfiles;');
+
+ migration.query('UPDATE tmpFinalProfiles' +
+ ' SET profileId = (SELECT (@cnt := @cnt + 1) AS rowNumber' +
+ ' FROM user_profiles u' +
+ ' CROSS JOIN (SELECT @cnt := 1) as dummy LIMIT 1)');
+ // migration.query('UPDATE tmpFinalProfiles' +
+ // ' SET profileId = (@firstId := ifnull(@firstId, 1) + 1);');
+
+ migration.query('CREATE TABLE tmpSections' +
+ ' (' +
+ ' name varchar(255) not null,' +
+ ' category varchar(255) not null,' +
+ ' sectionId int(11) not null,' +
+ ' enabled tinyint not null,' +
+ ' autoAssociation tinyint not null,' +
+ ' userProfileId int(11) not null,' +
+ ' insertedId int(11),' +
+ ' userId text' +
+ ' );');
+
+ migration.query('INSERT INTO tmpSections' +
+ ' SELECT \'\', \'\', permission, 1, 0, profileId, null, userId' +
+ ' FROM (SELECT' +
+ ' fp.profileId profileId,' +
+ ' SUBSTRING_INDEX(SUBSTRING_INDEX(fp.permissions, \',\', tally.n), \',\', -1) permission,' +
+ ' SUBSTRING_INDEX(fp.userId, \',\', 1) userId' +
+ ' FROM tally' +
+ ' INNER JOIN tmpFinalProfiles fp ON CHAR_LENGTH(fp.permissions) - CHAR_LENGTH(REPLACE(fp.permissions, \',\', \'\')) >= tally.n-1' +
+ ' ORDER BY n) AS split;');
+
+ migration.query('UPDATE tmpSections' +
+ ' SET' +
+ ' name = (SELECT' +
+ ' CASE' +
+ ' WHEN sectionId = 100 THEN \'Dashboards\'' +
+ ' WHEN sectionId = 101 THEN \'DELETE\'' +
+ ' WHEN sectionId = 200 THEN \'DELETE\'' +
+ ' WHEN sectionId = 201 THEN \'DELETE\'' +
+ ' WHEN sectionId = 202 THEN \'Agents\'' +
+ ' WHEN sectionId = 203 THEN \'Telephones\'' +
+ ' WHEN sectionId = 204 THEN \'Teams\'' +
+ ' WHEN sectionId = 300 THEN \'DELETE\'' +
+ ' WHEN sectionId = 301 THEN \'Lists\'' +
+ ' WHEN sectionId = 302 THEN \'Companies\'' +
+ ' WHEN sectionId = 303 THEN \'Contacts\'' +
+ ' WHEN sectionId = 304 THEN \'GlobalCustomFields\'' +
+ ' WHEN sectionId = 400 THEN \'DELETE\'' +
+ ' WHEN sectionId = 401 THEN \'VoiceQueues\'' +
+ ' WHEN sectionId = 402 THEN \'InboundRoutes\'' +
+ ' WHEN sectionId = 403 THEN \'OutboundRoutes\'' +
+ ' WHEN sectionId = 404 THEN \'InternalRoutes\'' +
+ ' WHEN sectionId = 405 THEN \'Contexts\'' +
+ ' WHEN sectionId = 406 THEN \'Voicemails\'' +
+ ' WHEN sectionId = 407 THEN \'MusicOnHolds\'' +
+ ' WHEN sectionId = 408 THEN \'VoiceRecordings\'' +
+ ' WHEN sectionId = 409 THEN \'ChanSpies\'' +
+ ' WHEN sectionId = 410 THEN \'VoicePrefixes\'' +
+ ' WHEN sectionId = 411 THEN \'Realtime\'' +
+ ' WHEN sectionId = 500 THEN \'DELETE\'' +
+ ' WHEN sectionId = 501 THEN \'ChatQueues\'' +
+ ' WHEN sectionId = 502 THEN \'ChatWebsites\'' +
+ ' WHEN sectionId = 510 THEN \'Realtime\'' +
+ ' WHEN sectionId = 600 THEN \'DELETE\'' +
+ ' WHEN sectionId = 601 THEN \'MailQueues\'' +
+ ' WHEN sectionId = 602 THEN \'MailAccounts\'' +
+ ' WHEN sectionId = 603 THEN \'MailSubstatuses\'' +
+ ' WHEN sectionId = 610 THEN \'Realtime\'' +
+ ' WHEN sectionId = 700 THEN \'DELETE\'' +
+ ' WHEN sectionId = 701 THEN \'SmsQueues\'' +
+ ' WHEN sectionId = 702 THEN \'SmsAccounts\'' +
+ ' WHEN sectionId = 710 THEN \'Realtime\'' +
+ ' WHEN sectionId = 800 THEN \'DELETE\'' +
+ ' WHEN sectionId = 801 THEN \'OpenchannelQueues\'' +
+ ' WHEN sectionId = 802 THEN \'OpenchannelAccounts\'' +
+ ' WHEN sectionId = 810 THEN \'Realtime\'' +
+ ' WHEN sectionId = 900 THEN \'DELETE\'' +
+ ' WHEN sectionId = 901 THEN \'FaxQueues\'' +
+ ' WHEN sectionId = 902 THEN \'FaxAccounts\'' +
+ ' WHEN sectionId = 910 THEN \'Realtime\'' +
+ ' WHEN sectionId = 1000 THEN \'DELETE\'' +
+ ' WHEN sectionId = 1001 THEN \'CannedAnswers\'' +
+ ' WHEN sectionId = 1002 THEN \'Dispositions\'' +
+ ' WHEN sectionId = 1003 THEN \'CustomDashboards\'' +
+ ' WHEN sectionId = 1004 THEN \'Intervals\'' +
+ ' WHEN sectionId = 1005 THEN \'Pauses\'' +
+ ' WHEN sectionId = 1006 THEN \'Scheduler\'' +
+ ' WHEN sectionId = 1007 THEN \'Sounds\'' +
+ ' WHEN sectionId = 1008 THEN \'Tags\'' +
+ ' WHEN sectionId = 1009 THEN \'Templates\'' +
+ ' WHEN sectionId = 1010 THEN \'Triggers\'' +
+ ' WHEN sectionId = 1011 THEN \'Trunks\'' +
+ ' WHEN sectionId = 1012 THEN \'Variables\'' +
+ ' WHEN sectionId = 1100 THEN \'DELETE\'' +
+ ' WHEN sectionId = 1101 THEN \'ODBC\'' +
+ ' WHEN sectionId = 1102 THEN \'SquareRecordings\'' +
+ ' WHEN sectionId = 1103 THEN \'SquareProjects\'' +
+ ' WHEN sectionId = 1200 THEN \'DELETE\'' +
+ ' WHEN sectionId = 1201 THEN \'Metrics\'' +
+ ' WHEN sectionId = 1202 THEN \'ExtractedReports\'' +
+ ' WHEN sectionId = 1203 THEN \'Reports\'' +
+ ' WHEN sectionId = 1300 THEN \'DELETE\'' +
+ ' WHEN sectionId = 1301 THEN \'ZendeskAccounts\'' +
+ ' WHEN sectionId = 1302 THEN \'SalesforceAccounts\'' +
+ ' WHEN sectionId = 1303 THEN \'FreshdeskAccounts\'' +
+ ' WHEN sectionId = 1305 THEN \'SugarcrmAccounts\'' +
+ ' WHEN sectionId = 1306 THEN \'DeskAccounts\'' +
+ ' WHEN sectionId = 1307 THEN \'ZohoAccounts\'' +
+ ' WHEN sectionId = 1308 THEN \'VtigerAccounts\'' +
+ ' WHEN sectionId = 1400 THEN \'Settings\'' +
+ ' WHEN sectionId in (1401, 1402, 1403, 1404, 1405, 1406, 1407) THEN \'DELETE\'' +
+ ' WHEN sectionId = 1500 THEN \'DELETE\'' +
+ ' WHEN sectionId = 1501 THEN \'QueueCampaigns\'' +
+ ' WHEN sectionId = 1502 THEN \'IvrCampaigns\'' +
+ ' WHEN sectionId = 1510 THEN \'Realtime\'' +
+ ' WHEN sectionId = 1600 THEN \'Help\'' +
+ ' WHEN sectionId = 1601 THEN \'DELETE\'' +
+ ' WHEN sectionId = 1700 THEN \'DELETE\'' +
+ ' WHEN sectionId = 1701 THEN \'Projects\'' +
+ ' WHEN sectionId between 88889 AND 99999 THEN \'Plugins\'' +
+ ' WHEN sectionId >= 100000 THEN \'CustomDashboards\'' +
+ ' ELSE \'\'' +
+ ' END' +
+ ' ),' +
+ ' category = (SELECT' +
+ ' CASE' +
+ ' WHEN sectionId = 100 THEN \'Dashboards\'' +
+ ' WHEN sectionId >= 100000 THEN \'Tools\'' +
+ ' WHEN sectionId in (202, 203, 204) THEN \'Staff\'' +
+ ' WHEN sectionId in (301, 302, 303, 304) THEN \'ContactManager\'' +
+ ' WHEN sectionId in (401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411) THEN \'Voice\'' +
+ ' WHEN sectionId in (501, 502, 510) THEN \'Chat\'' +
+ ' WHEN sectionId in (601, 602, 603, 610) THEN \'Mail\'' +
+ ' WHEN sectionId in (701, 702, 710) THEN \'Sms\'' +
+ ' WHEN sectionId in (801, 802, 810) THEN \'Openchannel\'' +
+ ' WHEN sectionId in (901, 902, 910) THEN \'Fax\'' +
+ ' WHEN sectionId in (1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012) THEN \'Tools\'' +
+ ' WHEN sectionId in (1101, 1102, 1103) THEN \'CallySquare\'' +
+ ' WHEN sectionId in (1201, 1202, 1203) THEN \'Analytics\'' +
+ ' WHEN sectionId in (1301, 1302, 1303, 1305, 1306, 1307, 1308) THEN \'Integrations\'' +
+ ' WHEN sectionId = 1400 THEN \'Settings\'' +
+ ' WHEN sectionId in (1501, 1502, 1510) THEN \'MotionDialer\'' +
+ ' WHEN sectionId = 1600 THEN \'Help\'' +
+ ' WHEN sectionId = 1701 THEN \'jscripty\'' +
+ ' WHEN sectionId between 88889 AND 99999 THEN \'AppZone\'' +
+ ' ELSE \'\'' +
+ ' END' +
+ ' );');
+
+ migration.query('DELETE FROM tmpSections' +
+ ' WHERE name = \'DELETE\';');
+
+ migration.query('CREATE TABLE tmpCustomResources' +
+ ' (' +
+ ' name varchar(255) not null,' +
+ ' category varchar(255) not null,' +
+ ' sectionId int(11) not null,' +
+ ' enabled tinyint not null,' +
+ ' autoAssociation tinyint not null,' +
+ ' userProfileId int(11) not null,' +
+ ' insertedId int(11),' +
+ ' userId text' +
+ ' );');
+
+ migration.query('INSERT INTO tmpCustomResources' +
+ ' SELECT *' +
+ ' FROM tmpSections' +
+ ' WHERE name in (\'Plugins\', \'CustomDashboards\');');
+
+ migration.query('DELETE t1 FROM tmpSections t1, tmpCustomResources t2' +
+ ' WHERE t1.name = t2.name AND t1.userProfileId = t2.userProfileId AND t1.sectionId > t2.sectionId;');
+
+ migration.query('UPDATE tmpSections' +
+ ' SET sectionId = 1801 WHERE sectionId between 88889 AND 99999;');
+
+ migration.query('UPDATE tmpSections' +
+ ' SET sectionId = 1003 WHERE sectionId >= 100000;');
+
+ migration.query('UPDATE tmpSections' +
+ ' SET autoAssociation = 1 WHERE sectionId = 100;');
+
+ migration.query('UPDATE tmpCustomResources' +
+ ' SET insertedId = (SELECT' +
+ ' CASE' +
+ ' WHEN name = \'Plugins\' THEN (sectionId - 88888)' +
+ ' WHEN name = \'CustomDashboards\' THEN (sectionId - 99999)' +
+ ' END' +
+ ' );');
+
+ migration.query('INSERT INTO user_profile_has_sections (name, category, sectionId, enabled, autoAssociation, crudPermissions, createdAt, updatedAt, userProfileId)' +
+ ' SELECT name, category, sectionId, enabled, autoAssociation, \'red\', now(), now(), userProfileId' +
+ ' FROM tmpSections;');
+
+ migration.query('UPDATE tmpSections s' +
+ ' SET insertedId = (SELECT id' +
+ ' FROM user_profile_has_sections x' +
+ ' WHERE s.userProfileId = x.userProfileId AND s.sectionId = x.sectionId);');
+
+ migration.query('UPDATE tmpCustomResources r, tmpSections s' +
+ ' SET r.sectionId = s.insertedId' +
+ ' WHERE r.name = s.name AND r.userProfileId = s.userProfileId;');
+
+ migration.query('CREATE TABLE tmpResources' +
+ ' (' +
+ ' resourceId text,' +
+ ' type varchar(255) not null,' +
+ ' insertedSectionId int(11) not null,' +
+ ' sectionId int(11)' +
+ ' );');
+
+ migration.query('INSERT INTO tmpResources' +
+ ' SELECT cr.resourceId, name, s.insertedId, s.sectionId' +
+ ' FROM tmpSections s' +
+ ' INNER JOIN tmpExtractedResources cr ON cr.userId = s.userId AND cr.sectionId = s.sectionId AND cr.resourceId IS NOT NULL;');
+
+ migration.query('CREATE TABLE tmpNormalizedResources' +
+ ' (' +
+ ' name varchar(255) not null,' +
+ ' resourceId int(11) not null,' +
+ ' type varchar(255) not null,' +
+ ' insertedSectionId int(11) not null,' +
+ ' sectionId int(11)' +
+ ' );');
+
+ migration.query('INSERT INTO tmpNormalizedResources' +
+ ' SELECT \'\', resourceId, type, insertedSectionId, sectionId' +
+ ' FROM (SELECT' +
+ ' SUBSTRING_INDEX(SUBSTRING_INDEX(r.resourceId, \',\', tally.n), \',\', -1) resourceId,' +
+ ' r.type type,' +
+ ' r.insertedSectionId insertedSectionId,' +
+ ' r.sectionId sectionId' +
+ ' FROM tally' +
+ ' INNER JOIN tmpResources r ON CHAR_LENGTH(r.resourceId) - CHAR_LENGTH(REPLACE(r.resourceId, \',\', \'\')) >= tally.n-1' +
+ ' ORDER BY n) AS split;');
+
+ migration.query('INSERT INTO tmpNormalizedResources' +
+ ' SELECT \'\', insertedId, name, sectionId, null' +
+ ' FROM tmpCustomResources;');
+
+ migration.query('UPDATE tmpNormalizedResources x' +
+ ' SET name = (SELECT' +
+ ' CASE' +
+ ' WHEN x.sectionId = 401 THEN COALESCE((SELECT name FROM voice_queues WHERE id = x.resourceId AND type = \'inbound\'),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 501 THEN COALESCE((SELECT name FROM chat_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 502 THEN COALESCE((SELECT name FROM chat_websites WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 601 THEN COALESCE((SELECT name FROM mail_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 602 THEN COALESCE((SELECT name FROM mail_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 701 THEN COALESCE((SELECT name FROM sms_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 702 THEN COALESCE((SELECT name FROM sms_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 801 THEN COALESCE((SELECT name FROM openchannel_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 802 THEN COALESCE((SELECT name FROM openchannel_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 901 THEN COALESCE((SELECT name FROM fax_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 902 THEN COALESCE((SELECT name FROM fax_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 1103 THEN COALESCE((SELECT name FROM square_projects WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.sectionId = 1501 THEN COALESCE((SELECT name FROM voice_queues WHERE id = x.resourceId AND type = \'outbound\'),\'DELETE_MIGRATION\')' +
+ ' WHEN x.type = \'Plugins\' THEN COALESCE((SELECT name FROM plugins WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' WHEN x.type = \'CustomDashboards\' THEN COALESCE((SELECT name FROM dashboards WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
+ ' END' +
+ ' );');
+
+ migration.query('DELETE FROM tmpNormalizedResources' +
+ ' WHERE name = \'DELETE_MIGRATION\';');
+
+ migration.query('INSERT INTO user_profile_has_resources (name, resourceId, type, createdAt, updatedAt, sectionId)' +
+ ' SELECT name, resourceId, type, now(), now(), insertedSectionId' +
+ ' FROM tmpNormalizedResources;');
+
+ migration.query('UPDATE users u, tmpFinalProfiles f' +
+ ' SET u.userProfileId = f.profileId' +
+ ' WHERE u.id = f.userId;');
+
+ migration.query('UPDATE users u' +
+ ' SET u.userProfileId = 1' +
+ ' WHERE userProfileId is null AND role = \'user\';');
+
+ migration.query(' DROP TABLE IF EXISTS tally;');
+
+ migration.query('DROP TABLE IF EXISTS tmpPermissions;');
+
+ migration.query('DROP TABLE IF EXISTS tmpNormalizedSections;');
+
+ migration.query('DROP TABLE IF EXISTS tmpNormalizedUsers;');
+
+ migration.query('DROP TABLE IF EXISTS tmpExtractedResources;');
+
+ migration.query('DROP TABLE IF EXISTS tmpGroupedPermissions;');
+
+ migration.query('DROP TABLE IF EXISTS tmpSharedProfiles;');
+
+ migration.query('DROP TABLE IF EXISTS tmpSingleProfiles;');
+
+ migration.query('DROP TABLE IF EXISTS tmpFinalProfiles;');
+
+ migration.query('DROP TABLE IF EXISTS tmpSections;');
+
+ migration.query('DROP TABLE IF EXISTS tmpCustomResources;');
+
+ migration.query('DROP TABLE IF EXISTS tmpResources;');
+
+ migration.query('DROP TABLE IF EXISTS tmpNormalizedResources;');
+ // END USER_PROFILES
+
+ // START FINAL
+ migration.final(resolve);
+ // END FINAL
+ });
},
down: function(queryInterface, Sequelize) {
- // var migration = new Migration(queryInterface);
+ // var migration = new Migration(queryInterface);
}
};
\ No newline at end of file
git://repos.xcallymotion.com / motion2.git / blobdiff