3 var BPromise = require('bluebird');
4 var util = require('util');
6 var logger = require('../config/logger')('migration');
8 var Sequence = function() {};
10 Sequence.prototype.enqueue = function(fn) {
11 this.tail = this.tail ? this.tail.finally(fn) : fn();
14 var Migration = function(queryInterface) {
15 this.queryInterface = queryInterface;
16 this.sequence = new Sequence();
19 Migration.prototype.changeColumn = function(table, column, type) {
21 this.sequence.enqueue(function() {
22 return _this.queryInterface
23 .changeColumn(table, column, type)
25 logger.info('Changed column %s in table %s', column, table);
27 .catch(function(err) {
28 logger.info(JSON.stringify(err));
33 Migration.prototype.addColumn = function(table, column, type) {
35 this.sequence.enqueue(function() {
36 return _this.queryInterface
37 .addColumn(table, column, type)
39 logger.info('Added column %s to %s', column, table);
41 .catch(function(err) {
42 logger.info(JSON.stringify(err));
47 Migration.prototype.dropTable = function(table) {
49 this.sequence.enqueue(function() {
50 return _this.queryInterface
55 logger.info('table dropped %s', table);
57 .catch(function(err) {
58 logger.info(JSON.stringify(err));
63 Migration.prototype.addIndex = function(table, column, indexName) {
65 this.sequence.enqueue(function() {
66 return _this.queryInterface.addIndex(table, column, {
70 logger.info('addIndex %s %s %s', table, column.join(','), indexName);
72 .catch(function(err) {
73 logger.info(JSON.stringify(err));
78 Migration.prototype.removeIndex = function(table, indexName) {
80 this.sequence.enqueue(function() {
81 return _this.queryInterface.removeIndex(table, indexName)
83 logger.info('removeIndex %s %s', table, indexName);
85 .catch(function(err) {
86 logger.info(JSON.stringify(err));
91 Migration.prototype.query = function(sql) {
93 this.sequence.enqueue(function() {
94 return _this.queryInterface.sequelize.query(sql)
96 logger.info('query %s', sql);
98 .catch(function(err) {
99 logger.info(JSON.stringify(err));
104 Migration.prototype.removeColumn = function(table, column) {
106 this.sequence.enqueue(function() {
107 return _this.queryInterface.removeColumn(table, column)
109 logger.info('Removed column %s from %s', column, table);
111 .catch(function(err) {
112 logger.info(util.inspect(err, {
120 Migration.prototype.renameColumn = function(table, oldColumn, newColumn) {
122 this.sequence.enqueue(function() {
123 return _this.queryInterface.renameColumn(table, oldColumn, newColumn)
125 logger.info('Renamed column from %s to %s on %s', oldColumn, newColumn, table);
127 .catch(function(err) {
128 logger.info(util.inspect(err, {
136 Migration.prototype.final = function(resolve) {
137 this.sequence.enqueue(function() {
143 up: function(queryInterface, Sequelize) {
144 return new BPromise(function(resolve) {
145 var migration = new Migration(queryInterface);
147 // START EXTRACTED REPORTS
148 migration.addColumn('analytics_extracted_reports', 'reportId', {
149 type: Sequelize.INTEGER
152 migration.addColumn('analytics_extracted_reports', 'reportType', {
153 type: Sequelize.STRING
155 // END EXTRACTED REPORTS
158 migration.addColumn('users', 'userProfileId', {
159 type: Sequelize.INTEGER
164 migration.removeColumn('voice_voicemail', 'name');
167 // START USER_PROFILES
168 migration.query('CREATE TABLE `user_profiles` (' +
169 ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
170 ' `name` varchar(255) NOT NULL,' +
171 ' `crudPermissions` varchar(255) NOT NULL DEFAULT \'r\',' +
172 ' `description` varchar(255) DEFAULT NULL,' +
173 ' `createdAt` datetime NOT NULL,' +
174 ' `updatedAt` datetime NOT NULL,' +
175 ' PRIMARY KEY (`id`),' +
176 ' UNIQUE KEY `name` (`name`)' +
177 ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
179 migration.query('INSERT INTO `user_profiles` (`name`, `crudPermissions`, `description`, `createdAt`, `updatedAt`)' +
180 'VALUES (\'Default\', \'red\', NULL, NOW(), NOW());');
182 migration.query('ALTER TABLE `users` ' +
183 'ADD CONSTRAINT `users_ibfk_1` ' +
184 'FOREIGN KEY (`userProfileId`) ' +
185 'REFERENCES user_profiles(`id`) ' +
186 'ON UPDATE CASCADE');
188 migration.query('CREATE TABLE `user_profile_has_sections` (' +
189 ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
190 ' `name` varchar(255) NOT NULL,' +
191 ' `category` varchar(255) NOT NULL,' +
192 ' `sectionId` int(11) NOT NULL,' +
193 ' `enabled` tinyint(1) DEFAULT NULL,' +
194 ' `autoAssociation` tinyint(1) DEFAULT NULL,' +
195 ' `crudPermissions` varchar(255) DEFAULT NULL,' +
196 ' `createdAt` datetime NOT NULL,' +
197 ' `updatedAt` datetime NOT NULL,' +
198 ' `userProfileId` int(11) DEFAULT NULL,' +
199 ' PRIMARY KEY (`id`),' +
200 ' KEY `userProfileId` (`userProfileId`),' +
201 ' KEY `compositeIndex` (`sectionId`,`userProfileId`),' +
202 ' CONSTRAINT `user_profile_has_sections_ibfk_1` FOREIGN KEY (`userProfileId`) REFERENCES `user_profiles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE' +
203 ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
205 migration.query('CREATE TABLE `user_profile_has_resources` (' +
206 ' `id` int(11) NOT NULL AUTO_INCREMENT,' +
207 ' `name` varchar(255) NOT NULL,' +
208 ' `resourceId` int(11) NOT NULL,' +
209 ' `type` varchar(255) NOT NULL,' +
210 ' `createdAt` datetime NOT NULL,' +
211 ' `updatedAt` datetime NOT NULL,' +
212 ' `sectionId` int(11) DEFAULT NULL,' +
213 ' PRIMARY KEY (`id`),' +
214 ' KEY `sectionId` (`sectionId`),' +
215 ' KEY `compositeIndex` (`resourceId`,`sectionId`),' +
216 ' CONSTRAINT `user_profile_has_resources_ibfk_1` FOREIGN KEY (`sectionId`) REFERENCES `user_profile_has_sections` (`id`) ON DELETE CASCADE ON UPDATE CASCADE' +
217 ') ENGINE=InnoDB DEFAULT CHARSET=utf8;');
219 migration.query('CREATE TABLE tally (' +
220 ' n INT NOT NULL AUTO_INCREMENT PRIMARY KEY' +
223 migration.query('INSERT INTO tally (n)' +
225 '(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' +
226 ',(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' +
227 ',(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' +
228 ',(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' +
229 ',(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' +
232 migration.query('CREATE TABLE tmpPermissions' +
234 ' userIds varchar(255) not null,' +
235 ' permissions text not null,' +
236 ' profileId int(11)' +
239 migration.query('INSERT INTO tmpPermissions' +
240 ' SELECT id, permissions, null' +
242 ' WHERE role = \'user\' and permissions is not null and permissions <> \'\'' +
245 migration.query('CREATE TABLE tmpNormalizedSections' +
247 ' sectionId int(11) not null,' +
252 migration.query('INSERT INTO tmpNormalizedSections' +
253 ' SELECT DISTINCT permission, userId, 0' +
255 ' SUBSTRING_INDEX(SUBSTRING_INDEX(p.permissions, \',\', tally.n), \',\', -1) permission,' +
256 ' p.userIds userId' +
257 ' FROM tally INNER JOIN tmpPermissions p ON CHAR_LENGTH(p.permissions) - CHAR_LENGTH(REPLACE(p.permissions, \',\', \'\')) >= tally.n-1' +
258 ' ORDER BY n) AS split;');
260 migration.query('CREATE TABLE tmpNormalizedPermissions' +
262 ' permissions text not null,' +
263 ' userId int(11) not null' +
266 migration.query('INSERT INTO tmpNormalizedPermissions' +
267 ' SELECT GROUP_CONCAT(sectionId ORDER BY sectionId), userId' +
268 ' FROM tmpNormalizedSections' +
269 ' GROUP BY userId;');
271 migration.query('UPDATE users u, tmpNormalizedPermissions t' +
272 ' SET u.permissions = t.permissions' +
273 ' WHERE u.id = t.userId;');
275 migration.query('TRUNCATE TABLE tmpPermissions;');
277 migration.query('INSERT INTO tmpPermissions' +
278 ' SELECT GROUP_CONCAT(id), permissions, null' +
280 ' WHERE role = \'user\' and permissions is not null and permissions <> \'\'' +
281 ' GROUP BY permissions' +
282 ' ORDER BY GROUP_CONCAT(id);');
284 migration.query('CREATE TABLE tmpNormalizedUsers' +
286 ' sectionId int(11) not null,' +
291 migration.query('INSERT INTO tmpNormalizedUsers' +
292 ' SELECT permission, userId, 0' +
294 ' s.sectionId permission,' +
295 ' SUBSTRING_INDEX(SUBSTRING_INDEX(s.userId, \',\', t.n), \',\', -1) userId' +
296 ' FROM tally t INNER JOIN tmpNormalizedSections s ON CHAR_LENGTH(s.userId) - CHAR_LENGTH(REPLACE(s.userId, \',\', \'\')) >= t.n-1' +
297 ' ORDER BY n) AS split;');
300 migration.query('CREATE TABLE tmpExtractedResources (' +
301 ' sectionId int(11) not null,' +
302 ' resourceId int(11),' +
306 migration.query('INSERT INTO tmpExtractedResources' +
307 ' SELECT x.sectionId, u.VoiceQueueId, x.userId' +
308 ' FROM tmpNormalizedUsers x' +
309 ' INNER JOIN user_has_voice_queues u ON u.UserId = x.userId' +
310 ' INNER JOIN voice_queues q ON u.VoiceQueueId = q.id' +
311 ' WHERE x.sectionId = 401 AND q.type = \'inbound\';');
313 migration.query('INSERT INTO tmpExtractedResources' +
314 ' SELECT x.sectionId, u.ChatQueueId, x.userId' +
315 ' FROM tmpNormalizedUsers x' +
316 ' INNER JOIN user_has_chat_queues u ON u.UserId = x.userId' +
317 ' WHERE x.sectionId = 501;');
319 migration.query('INSERT INTO tmpExtractedResources' +
320 ' SELECT x.sectionId, u.ChatWebsiteId, x.userId' +
321 ' FROM tmpNormalizedUsers x' +
322 ' INNER JOIN user_has_chat_websites u ON u.UserId = x.userId' +
323 ' WHERE x.sectionId = 502;');
325 migration.query('INSERT INTO tmpExtractedResources' +
326 ' SELECT x.sectionId, u.MailQueueId, x.userId' +
327 ' FROM tmpNormalizedUsers x' +
328 ' INNER JOIN user_has_mail_queues u ON u.UserId = x.userId' +
329 ' WHERE x.sectionId = 601;');
331 migration.query('INSERT INTO tmpExtractedResources' +
332 ' SELECT x.sectionId, u.MailAccountId, x.userId' +
333 ' FROM tmpNormalizedUsers x' +
334 ' INNER JOIN user_has_mail_accounts u ON u.UserId = x.userId' +
335 ' WHERE x.sectionId = 602;');
337 migration.query('INSERT INTO tmpExtractedResources' +
338 ' SELECT x.sectionId, u.SmsQueueId, x.userId' +
339 ' FROM tmpNormalizedUsers x' +
340 ' INNER JOIN user_has_sms_queues u ON u.UserId = x.userId' +
341 ' WHERE x.sectionId = 701;');
343 migration.query('INSERT INTO tmpExtractedResources' +
344 ' SELECT x.sectionId, u.SmsAccountId, x.userId' +
345 ' FROM tmpNormalizedUsers x' +
346 ' INNER JOIN user_has_sms_accounts u ON u.UserId = x.userId' +
347 ' WHERE x.sectionId = 702;');
349 migration.query('INSERT INTO tmpExtractedResources' +
350 ' SELECT x.sectionId, u.OpenchannelQueueId, x.userId' +
351 ' FROM tmpNormalizedUsers x' +
352 ' INNER JOIN user_has_openchannel_queues u ON u.UserId = x.userId' +
353 ' WHERE x.sectionId = 801;');
355 migration.query('INSERT INTO tmpExtractedResources' +
356 ' SELECT x.sectionId, u.OpenchannelAccountId, x.userId' +
357 ' FROM tmpNormalizedUsers x' +
358 ' INNER JOIN user_has_openchannel_accounts u ON u.UserId = x.userId' +
359 ' WHERE x.sectionId = 802;');
361 migration.query('INSERT INTO tmpExtractedResources' +
362 ' SELECT x.sectionId, u.FaxQueueId, x.userId' +
363 ' FROM tmpNormalizedUsers x' +
364 ' INNER JOIN user_has_fax_queues u ON u.UserId = x.userId' +
365 ' WHERE x.sectionId = 901;');
367 migration.query('INSERT INTO tmpExtractedResources' +
368 ' SELECT x.sectionId, u.FaxAccountId, x.userId' +
369 ' FROM tmpNormalizedUsers x' +
370 ' INNER JOIN user_has_fax_accounts u ON u.UserId = x.userId' +
371 ' WHERE x.sectionId = 902;');
373 migration.query('INSERT INTO tmpExtractedResources' +
374 ' SELECT x.sectionId, u.SquareProjectId, x.userId' +
375 ' FROM tmpNormalizedUsers x' +
376 ' INNER JOIN user_has_square_projects u ON u.UserId = x.userId' +
377 ' WHERE x.sectionId = 1103;');
379 migration.query('INSERT INTO tmpExtractedResources' +
380 ' SELECT x.sectionId, u.VoiceQueueId, x.userId' +
381 ' FROM tmpNormalizedUsers x' +
382 ' INNER JOIN user_has_voice_queues u ON u.UserId = x.userId' +
383 ' INNER JOIN voice_queues q ON u.VoiceQueueId = q.id' +
384 ' WHERE x.sectionId = 1501 AND q.type = \'outbound\';');
386 migration.query('CREATE TABLE tmpGroupedPermissions' +
388 ' userId text not null,' +
389 ' permissions text not null' +
392 migration.query('INSERT INTO tmpGroupedPermissions' +
393 ' SELECT userId, permissions' +
395 ' SUBSTRING_INDEX(SUBSTRING_INDEX(t.userIds, \',\', tally.n), \',\', -1) userId,' +
396 ' t.permissions permissions' +
397 ' FROM tally INNER JOIN tmpPermissions t ON CHAR_LENGTH(t.userIds) - CHAR_LENGTH(REPLACE(t.userIds, \',\', \'\')) >= tally.n-1' +
398 ' ORDER BY n) AS split;');
400 migration.query('CREATE TABLE tmpSharedProfiles' +
402 ' userIds varchar(255) not null,' +
403 ' permissions text not null,' +
404 ' profileId int(11)' +
407 migration.query('INSERT INTO tmpSharedProfiles' +
408 ' SELECT GROUP_CONCAT(userId), permissions, null' +
409 ' FROM tmpGroupedPermissions' +
410 ' WHERE userId NOT IN (SELECT split.userId' +
412 ' SUBSTRING_INDEX(SUBSTRING_INDEX(r.userIds, \',\', t.n), \',\', -1) userId' +
414 ' INNER JOIN tmpPermissions r on CHAR_LENGTH(r.userIds) - CHAR_LENGTH(REPLACE(r.userIds, \',\', \'\')) >= t.n-1' +
415 ' WHERE POSITION(\',\' in r.userIds) > 0' +
416 ' ORDER BY n) AS split' +
417 ' WHERE split.userId IN (SELECT * FROM' +
418 ' (SELECT GROUP_CONCAT(userId ORDER BY userId ASC) AS users' +
419 ' FROM tmpNormalizedUsers' +
420 ' GROUP BY resourceId, sectionId' +
421 ' ORDER BY users) b' +
422 ' WHERE position(\',\' in b.users) = 0))' +
423 ' GROUP BY permissions' +
424 ' ORDER BY GROUP_CONCAT(userId);');
426 migration.query('CREATE TABLE tmpSingleProfiles' +
428 ' userId text not null,' +
429 ' permissions text not null,' +
430 ' profileId int(11)' +
433 migration.query('INSERT INTO tmpSingleProfiles' +
434 ' SELECT userId, permissions, null' +
435 ' FROM tmpGroupedPermissions' +
436 ' WHERE userId IN (SELECT split.userId' +
437 ' FROM (select SUBSTRING_INDEX(SUBSTRING_INDEX(r.userIds, \',\', t.n), \',\', -1) userId' +
438 ' FROM tally t INNER JOIN tmpPermissions r ON CHAR_LENGTH(r.userIds) - CHAR_LENGTH(REPLACE(r.userIds, \',\', \'\')) >= t.n-1' +
439 ' WHERE POSITION(\',\' in r.userIds) > 0' +
440 ' ORDER BY n) as split' +
441 ' WHERE split.userId IN (SELECT * FROM' +
442 ' (SELECT GROUP_CONCAT(userId ORDER BY userId ASC) as users' +
443 ' FROM tmpNormalizedUsers' +
444 ' GROUP BY resourceId, sectionId' +
445 ' ORDER BY users) b' +
446 ' WHERE position(\',\' in b.users) = 0))' +
447 ' ORDER BY userId;');
449 migration.query('CREATE TABLE tmpFinalProfiles' +
451 ' userId text not null,' +
452 ' permissions text not null,' +
453 ' profileId int(11)' +
456 migration.query('INSERT INTO tmpFinalProfiles' +
457 ' SELECT * FROM tmpSharedProfiles' +
459 ' SELECT * FROM tmpSingleProfiles;');
461 migration.query('INSERT INTO user_profiles (name, crudPermissions, createdAt, updatedAt)' +
462 ' SELECT CONCAT(\'Profile_\', (SELECT (@cnt := @cnt + 1) AS rowNumber' +
463 ' FROM tmpFinalProfiles t' +
464 ' CROSS JOIN (SELECT @cnt := 0) as dummy LIMIT 1)), \'red\', now(), now()' +
465 ' FROM tmpFinalProfiles;');
467 migration.query('UPDATE tmpFinalProfiles' +
468 ' SET profileId = (SELECT (@cnt := @cnt + 1) AS rowNumber' +
469 ' FROM user_profiles u' +
470 ' CROSS JOIN (SELECT @cnt := 1) as dummy LIMIT 1)');
471 // migration.query('UPDATE tmpFinalProfiles' +
472 // ' SET profileId = (@firstId := ifnull(@firstId, 1) + 1);');
474 migration.query('CREATE TABLE tmpSections' +
476 ' name varchar(255) not null,' +
477 ' category varchar(255) not null,' +
478 ' sectionId int(11) not null,' +
479 ' enabled tinyint not null,' +
480 ' autoAssociation tinyint not null,' +
481 ' userProfileId int(11) not null,' +
482 ' insertedId int(11),' +
486 migration.query('INSERT INTO tmpSections' +
487 ' SELECT \'\', \'\', permission, 1, 0, profileId, null, userId' +
489 ' fp.profileId profileId,' +
490 ' SUBSTRING_INDEX(SUBSTRING_INDEX(fp.permissions, \',\', tally.n), \',\', -1) permission,' +
491 ' SUBSTRING_INDEX(fp.userId, \',\', 1) userId' +
493 ' INNER JOIN tmpFinalProfiles fp ON CHAR_LENGTH(fp.permissions) - CHAR_LENGTH(REPLACE(fp.permissions, \',\', \'\')) >= tally.n-1' +
494 ' ORDER BY n) AS split;');
496 migration.query('UPDATE tmpSections' +
500 ' WHEN sectionId = 100 THEN \'Dashboards\'' +
501 ' WHEN sectionId = 101 THEN \'DELETE\'' +
502 ' WHEN sectionId = 200 THEN \'DELETE\'' +
503 ' WHEN sectionId = 201 THEN \'DELETE\'' +
504 ' WHEN sectionId = 202 THEN \'Agents\'' +
505 ' WHEN sectionId = 203 THEN \'Telephones\'' +
506 ' WHEN sectionId = 204 THEN \'Teams\'' +
507 ' WHEN sectionId = 300 THEN \'DELETE\'' +
508 ' WHEN sectionId = 301 THEN \'Lists\'' +
509 ' WHEN sectionId = 302 THEN \'Companies\'' +
510 ' WHEN sectionId = 303 THEN \'Contacts\'' +
511 ' WHEN sectionId = 304 THEN \'GlobalCustomFields\'' +
512 ' WHEN sectionId = 400 THEN \'DELETE\'' +
513 ' WHEN sectionId = 401 THEN \'VoiceQueues\'' +
514 ' WHEN sectionId = 402 THEN \'InboundRoutes\'' +
515 ' WHEN sectionId = 403 THEN \'OutboundRoutes\'' +
516 ' WHEN sectionId = 404 THEN \'InternalRoutes\'' +
517 ' WHEN sectionId = 405 THEN \'Contexts\'' +
518 ' WHEN sectionId = 406 THEN \'Voicemails\'' +
519 ' WHEN sectionId = 407 THEN \'MusicOnHolds\'' +
520 ' WHEN sectionId = 408 THEN \'VoiceRecordings\'' +
521 ' WHEN sectionId = 409 THEN \'ChanSpies\'' +
522 ' WHEN sectionId = 410 THEN \'VoicePrefixes\'' +
523 ' WHEN sectionId = 411 THEN \'Realtime\'' +
524 ' WHEN sectionId = 500 THEN \'DELETE\'' +
525 ' WHEN sectionId = 501 THEN \'ChatQueues\'' +
526 ' WHEN sectionId = 502 THEN \'ChatWebsites\'' +
527 ' WHEN sectionId = 510 THEN \'Realtime\'' +
528 ' WHEN sectionId = 600 THEN \'DELETE\'' +
529 ' WHEN sectionId = 601 THEN \'MailQueues\'' +
530 ' WHEN sectionId = 602 THEN \'MailAccounts\'' +
531 ' WHEN sectionId = 603 THEN \'MailSubstatuses\'' +
532 ' WHEN sectionId = 610 THEN \'Realtime\'' +
533 ' WHEN sectionId = 700 THEN \'DELETE\'' +
534 ' WHEN sectionId = 701 THEN \'SmsQueues\'' +
535 ' WHEN sectionId = 702 THEN \'SmsAccounts\'' +
536 ' WHEN sectionId = 710 THEN \'Realtime\'' +
537 ' WHEN sectionId = 800 THEN \'DELETE\'' +
538 ' WHEN sectionId = 801 THEN \'OpenchannelQueues\'' +
539 ' WHEN sectionId = 802 THEN \'OpenchannelAccounts\'' +
540 ' WHEN sectionId = 810 THEN \'Realtime\'' +
541 ' WHEN sectionId = 900 THEN \'DELETE\'' +
542 ' WHEN sectionId = 901 THEN \'FaxQueues\'' +
543 ' WHEN sectionId = 902 THEN \'FaxAccounts\'' +
544 ' WHEN sectionId = 910 THEN \'Realtime\'' +
545 ' WHEN sectionId = 1000 THEN \'DELETE\'' +
546 ' WHEN sectionId = 1001 THEN \'CannedAnswers\'' +
547 ' WHEN sectionId = 1002 THEN \'Dispositions\'' +
548 ' WHEN sectionId = 1003 THEN \'CustomDashboards\'' +
549 ' WHEN sectionId = 1004 THEN \'Intervals\'' +
550 ' WHEN sectionId = 1005 THEN \'Pauses\'' +
551 ' WHEN sectionId = 1006 THEN \'Scheduler\'' +
552 ' WHEN sectionId = 1007 THEN \'Sounds\'' +
553 ' WHEN sectionId = 1008 THEN \'Tags\'' +
554 ' WHEN sectionId = 1009 THEN \'Templates\'' +
555 ' WHEN sectionId = 1010 THEN \'Triggers\'' +
556 ' WHEN sectionId = 1011 THEN \'Trunks\'' +
557 ' WHEN sectionId = 1012 THEN \'Variables\'' +
558 ' WHEN sectionId = 1100 THEN \'DELETE\'' +
559 ' WHEN sectionId = 1101 THEN \'ODBC\'' +
560 ' WHEN sectionId = 1102 THEN \'SquareRecordings\'' +
561 ' WHEN sectionId = 1103 THEN \'SquareProjects\'' +
562 ' WHEN sectionId = 1200 THEN \'DELETE\'' +
563 ' WHEN sectionId = 1201 THEN \'Metrics\'' +
564 ' WHEN sectionId = 1202 THEN \'ExtractedReports\'' +
565 ' WHEN sectionId = 1203 THEN \'Reports\'' +
566 ' WHEN sectionId = 1300 THEN \'DELETE\'' +
567 ' WHEN sectionId = 1301 THEN \'ZendeskAccounts\'' +
568 ' WHEN sectionId = 1302 THEN \'SalesforceAccounts\'' +
569 ' WHEN sectionId = 1303 THEN \'FreshdeskAccounts\'' +
570 ' WHEN sectionId = 1305 THEN \'SugarcrmAccounts\'' +
571 ' WHEN sectionId = 1306 THEN \'DeskAccounts\'' +
572 ' WHEN sectionId = 1307 THEN \'ZohoAccounts\'' +
573 ' WHEN sectionId = 1308 THEN \'VtigerAccounts\'' +
574 ' WHEN sectionId = 1400 THEN \'Settings\'' +
575 ' WHEN sectionId in (1401, 1402, 1403, 1404, 1405, 1406, 1407) THEN \'DELETE\'' +
576 ' WHEN sectionId = 1500 THEN \'DELETE\'' +
577 ' WHEN sectionId = 1501 THEN \'QueueCampaigns\'' +
578 ' WHEN sectionId = 1502 THEN \'IvrCampaigns\'' +
579 ' WHEN sectionId = 1510 THEN \'Realtime\'' +
580 ' WHEN sectionId = 1600 THEN \'Help\'' +
581 ' WHEN sectionId = 1601 THEN \'DELETE\'' +
582 ' WHEN sectionId = 1700 THEN \'DELETE\'' +
583 ' WHEN sectionId = 1701 THEN \'Projects\'' +
584 ' WHEN sectionId between 88889 AND 99999 THEN \'Plugins\'' +
585 ' WHEN sectionId >= 100000 THEN \'CustomDashboards\'' +
589 ' category = (SELECT' +
591 ' WHEN sectionId = 100 THEN \'Dashboards\'' +
592 ' WHEN sectionId >= 100000 THEN \'Tools\'' +
593 ' WHEN sectionId in (202, 203, 204) THEN \'Staff\'' +
594 ' WHEN sectionId in (301, 302, 303, 304) THEN \'ContactManager\'' +
595 ' WHEN sectionId in (401, 402, 403, 404, 405, 406, 407, 408, 409, 410, 411) THEN \'Voice\'' +
596 ' WHEN sectionId in (501, 502, 510) THEN \'Chat\'' +
597 ' WHEN sectionId in (601, 602, 603, 610) THEN \'Mail\'' +
598 ' WHEN sectionId in (701, 702, 710) THEN \'Sms\'' +
599 ' WHEN sectionId in (801, 802, 810) THEN \'Openchannel\'' +
600 ' WHEN sectionId in (901, 902, 910) THEN \'Fax\'' +
601 ' WHEN sectionId in (1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012) THEN \'Tools\'' +
602 ' WHEN sectionId in (1101, 1102, 1103) THEN \'CallySquare\'' +
603 ' WHEN sectionId in (1201, 1202, 1203) THEN \'Analytics\'' +
604 ' WHEN sectionId in (1301, 1302, 1303, 1305, 1306, 1307, 1308) THEN \'Integrations\'' +
605 ' WHEN sectionId = 1400 THEN \'Settings\'' +
606 ' WHEN sectionId in (1501, 1502, 1510) THEN \'MotionDialer\'' +
607 ' WHEN sectionId = 1600 THEN \'Help\'' +
608 ' WHEN sectionId = 1701 THEN \'jscripty\'' +
609 ' WHEN sectionId between 88889 AND 99999 THEN \'AppZone\'' +
614 migration.query('DELETE FROM tmpSections' +
615 ' WHERE name = \'DELETE\';');
617 migration.query('CREATE TABLE tmpCustomResources' +
619 ' name varchar(255) not null,' +
620 ' category varchar(255) not null,' +
621 ' sectionId int(11) not null,' +
622 ' enabled tinyint not null,' +
623 ' autoAssociation tinyint not null,' +
624 ' userProfileId int(11) not null,' +
625 ' insertedId int(11),' +
629 migration.query('INSERT INTO tmpCustomResources' +
631 ' FROM tmpSections' +
632 ' WHERE name in (\'Plugins\', \'CustomDashboards\');');
634 migration.query('DELETE t1 FROM tmpSections t1, tmpCustomResources t2' +
635 ' WHERE t1.name = t2.name AND t1.userProfileId = t2.userProfileId AND t1.sectionId > t2.sectionId;');
637 migration.query('UPDATE tmpSections' +
638 ' SET sectionId = 1801 WHERE sectionId between 88889 AND 99999;');
640 migration.query('UPDATE tmpSections' +
641 ' SET sectionId = 1003 WHERE sectionId >= 100000;');
643 migration.query('UPDATE tmpSections' +
644 ' SET autoAssociation = 1 WHERE sectionId = 100;');
646 migration.query('UPDATE tmpCustomResources' +
647 ' SET insertedId = (SELECT' +
649 ' WHEN name = \'Plugins\' THEN (sectionId - 88888)' +
650 ' WHEN name = \'CustomDashboards\' THEN (sectionId - 99999)' +
654 migration.query('INSERT INTO user_profile_has_sections (name, category, sectionId, enabled, autoAssociation, crudPermissions, createdAt, updatedAt, userProfileId)' +
655 ' SELECT name, category, sectionId, enabled, autoAssociation, \'red\', now(), now(), userProfileId' +
656 ' FROM tmpSections;');
658 migration.query('UPDATE tmpSections s' +
659 ' SET insertedId = (SELECT id' +
660 ' FROM user_profile_has_sections x' +
661 ' WHERE s.userProfileId = x.userProfileId AND s.sectionId = x.sectionId);');
663 migration.query('UPDATE tmpCustomResources r, tmpSections s' +
664 ' SET r.sectionId = s.insertedId' +
665 ' WHERE r.name = s.name AND r.userProfileId = s.userProfileId;');
667 migration.query('CREATE TABLE tmpResources' +
669 ' resourceId text,' +
670 ' type varchar(255) not null,' +
671 ' insertedSectionId int(11) not null,' +
672 ' sectionId int(11)' +
675 migration.query('INSERT INTO tmpResources' +
676 ' SELECT cr.resourceId, name, s.insertedId, s.sectionId' +
677 ' FROM tmpSections s' +
678 ' INNER JOIN tmpExtractedResources cr ON cr.userId = s.userId AND cr.sectionId = s.sectionId AND cr.resourceId IS NOT NULL;');
680 migration.query('CREATE TABLE tmpNormalizedResources' +
682 ' name varchar(255) not null,' +
683 ' resourceId int(11) not null,' +
684 ' type varchar(255) not null,' +
685 ' insertedSectionId int(11) not null,' +
686 ' sectionId int(11)' +
689 migration.query('INSERT INTO tmpNormalizedResources' +
690 ' SELECT \'\', resourceId, type, insertedSectionId, sectionId' +
692 ' SUBSTRING_INDEX(SUBSTRING_INDEX(r.resourceId, \',\', tally.n), \',\', -1) resourceId,' +
694 ' r.insertedSectionId insertedSectionId,' +
695 ' r.sectionId sectionId' +
697 ' INNER JOIN tmpResources r ON CHAR_LENGTH(r.resourceId) - CHAR_LENGTH(REPLACE(r.resourceId, \',\', \'\')) >= tally.n-1' +
698 ' ORDER BY n) AS split;');
700 migration.query('INSERT INTO tmpNormalizedResources' +
701 ' SELECT \'\', insertedId, name, sectionId, null' +
702 ' FROM tmpCustomResources;');
704 migration.query('UPDATE tmpNormalizedResources x' +
705 ' SET name = (SELECT' +
707 ' WHEN x.sectionId = 401 THEN COALESCE((SELECT name FROM voice_queues WHERE id = x.resourceId AND type = \'inbound\'),\'DELETE_MIGRATION\')' +
708 ' WHEN x.sectionId = 501 THEN COALESCE((SELECT name FROM chat_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
709 ' WHEN x.sectionId = 502 THEN COALESCE((SELECT name FROM chat_websites WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
710 ' WHEN x.sectionId = 601 THEN COALESCE((SELECT name FROM mail_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
711 ' WHEN x.sectionId = 602 THEN COALESCE((SELECT name FROM mail_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
712 ' WHEN x.sectionId = 701 THEN COALESCE((SELECT name FROM sms_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
713 ' WHEN x.sectionId = 702 THEN COALESCE((SELECT name FROM sms_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
714 ' WHEN x.sectionId = 801 THEN COALESCE((SELECT name FROM openchannel_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
715 ' WHEN x.sectionId = 802 THEN COALESCE((SELECT name FROM openchannel_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
716 ' WHEN x.sectionId = 901 THEN COALESCE((SELECT name FROM fax_queues WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
717 ' WHEN x.sectionId = 902 THEN COALESCE((SELECT name FROM fax_accounts WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
718 ' WHEN x.sectionId = 1103 THEN COALESCE((SELECT name FROM square_projects WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
719 ' WHEN x.sectionId = 1501 THEN COALESCE((SELECT name FROM voice_queues WHERE id = x.resourceId AND type = \'outbound\'),\'DELETE_MIGRATION\')' +
720 ' WHEN x.type = \'Plugins\' THEN COALESCE((SELECT name FROM plugins WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
721 ' WHEN x.type = \'CustomDashboards\' THEN COALESCE((SELECT name FROM dashboards WHERE id = x.resourceId),\'DELETE_MIGRATION\')' +
725 migration.query('DELETE FROM tmpNormalizedResources' +
726 ' WHERE name = \'DELETE_MIGRATION\';');
728 migration.query('INSERT INTO user_profile_has_resources (name, resourceId, type, createdAt, updatedAt, sectionId)' +
729 ' SELECT name, resourceId, type, now(), now(), insertedSectionId' +
730 ' FROM tmpNormalizedResources;');
732 migration.query('UPDATE users u, tmpFinalProfiles f' +
733 ' SET u.userProfileId = f.profileId' +
734 ' WHERE u.id = f.userId;');
736 migration.query('UPDATE users u' +
737 ' SET u.userProfileId = 1' +
738 ' WHERE userProfileId is null AND role = \'user\';');
740 migration.query(' DROP TABLE IF EXISTS tally;');
742 migration.query('DROP TABLE IF EXISTS tmpPermissions;');
744 migration.query('DROP TABLE IF EXISTS tmpNormalizedSections;');
746 migration.query('DROP TABLE IF EXISTS tmpNormalizedUsers;');
748 migration.query('DROP TABLE IF EXISTS tmpExtractedResources;');
750 migration.query('DROP TABLE IF EXISTS tmpGroupedPermissions;');
752 migration.query('DROP TABLE IF EXISTS tmpSharedProfiles;');
754 migration.query('DROP TABLE IF EXISTS tmpSingleProfiles;');
756 migration.query('DROP TABLE IF EXISTS tmpFinalProfiles;');
758 migration.query('DROP TABLE IF EXISTS tmpSections;');
760 migration.query('DROP TABLE IF EXISTS tmpCustomResources;');
762 migration.query('DROP TABLE IF EXISTS tmpResources;');
764 migration.query('DROP TABLE IF EXISTS tmpNormalizedResources;');
768 migration.final(resolve);
773 down: function(queryInterface, Sequelize) {
774 // var migration = new Migration(queryInterface);