3 var BPromise = require('bluebird');
4 var fs = require('fs');
5 var util = require('util');
6 var _ = require('lodash');
8 var logger = require('../config/logger')('migration');
10 var Sequence = function() {};
12 Sequence.prototype.enqueue = function(fn) {
13 this.tail = this.tail ? this.tail.finally(fn) : fn();
16 var Migration = function(queryInterface) {
17 this.queryInterface = queryInterface;
18 this.sequence = new Sequence();
21 Migration.prototype.changeColumn = function(table, column, type) {
23 this.sequence.enqueue(function() {
24 return _this.queryInterface
25 .changeColumn(table, column, type)
27 logger.info('Changed column %s in table %s', column, table);
29 .catch(function(err) {
30 logger.info(JSON.stringify(err));
35 Migration.prototype.addColumn = function(table, column, type) {
37 this.sequence.enqueue(function() {
38 return _this.queryInterface
39 .addColumn(table, column, type)
41 logger.info('Added column %s to %s', column, table);
43 .catch(function(err) {
44 logger.info(JSON.stringify(err));
49 Migration.prototype.dropTable = function(table) {
51 this.sequence.enqueue(function() {
52 return _this.queryInterface
57 logger.info('table dropped %s', table);
59 .catch(function(err) {
60 logger.info(JSON.stringify(err));
65 Migration.prototype.addIndex = function(table, column, indexName) {
67 this.sequence.enqueue(function() {
68 return _this.queryInterface.addIndex(table, column, {
72 logger.info('addIndex %s %s %s', table, column.join(','), indexName);
74 .catch(function(err) {
75 logger.info(JSON.stringify(err));
80 Migration.prototype.query = function(sql) {
82 this.sequence.enqueue(function() {
83 return _this.queryInterface.sequelize.query(sql)
85 logger.info('query %s', sql);
87 .catch(function(err) {
88 logger.info(JSON.stringify(err));
93 Migration.prototype.removeColumn = function(table, column) {
95 this.sequence.enqueue(function() {
96 return _this.queryInterface.removeColumn(table, column)
98 logger.info('Removed column %s from %s', column, table);
100 .catch(function(err) {
101 logger.info(util.inspect(err, {
109 Migration.prototype.final = function(resolve) {
111 this.sequence.enqueue(function() {
117 up: function(queryInterface, Sequelize) {
118 return new BPromise(function(resolve, reject) {
120 var migration = new Migration(queryInterface);
122 //START CHAT INTERNAL MESSAGES
123 migration.addColumn('chat_internal_messages', 'ChatInternalMessageId', {
124 type: Sequelize.INTEGER
126 migration.addColumn('chat_internal_messages', 'ChatGroupId', {
127 type: Sequelize.INTEGER
129 migration.query('ALTER TABLE `chat_internal_messages` \
130 ADD CONSTRAINT `chat_internal_messages_ibfk_3` \
131 FOREIGN KEY (`ChatGroupId`) \
132 REFERENCES chat_groups(`id`) \
135 //END CHAT INTERNAL MESSAGES
137 // START CHAT INTERACTIONS
138 migration.addColumn('chat_interactions', 'lastMsgDirection', {
139 type: Sequelize.ENUM('in', 'out'),
143 migration.addColumn('chat_interactions', 'lastMsgAt', {
146 // END CHAT INTERACTIONS
148 // START MAIL INTERACTIONS
149 migration.addColumn('mail_interactions', 'lastMsgDirection', {
150 type: Sequelize.ENUM('in', 'out'),
154 migration.addColumn('mail_interactions', 'lastMsgAt', {
157 // END MAIL INTERACTIONS
159 // START SMS INTERACTIONS
160 migration.addColumn('sms_interactions', 'lastMsgDirection', {
161 type: Sequelize.ENUM('in', 'out'),
165 migration.addColumn('sms_interactions', 'lastMsgAt', {
168 // END SMS INTERACTIONS
170 // START OPENCHANNEL INTERACTIONS
171 migration.addColumn('openchannel_interactions', 'lastMsgDirection', {
172 type: Sequelize.ENUM('in', 'out'),
176 migration.addColumn('openchannel_interactions', 'lastMsgAt', {
179 // END OPENCHANNEL INTERACTIONS
181 // START FAX INTERACTIONS
182 migration.addColumn('fax_interactions', 'lastMsgDirection', {
183 type: Sequelize.ENUM('in', 'out'),
187 migration.addColumn('fax_interactions', 'lastMsgAt', {
190 // END FAX INTERACTIONS
192 var channels = ['Chat', 'Mail', 'Sms', 'Openchannel'];
193 for (var i = 0; i < channels.length; i++) {
194 migration.query('UPDATE ' + channels[i].toLowerCase() + '_interactions t1, (SELECT ' + channels[i] + 'InteractionId, MAX(id) AS messageid, ANY_VALUE(direction) AS direction, ANY_VALUE(createdAt) AS createdAt \
195 FROM ' + channels[i].toLowerCase() + '_messages \
196 WHERE ' + channels[i] + 'InteractionId IS NOT NULL AND (direction=\'in\' OR (direction=\'out\' AND secret=0 AND UserId IS NOT NULL)) \
197 GROUP BY ' + channels[i] + 'InteractionId) t2 SET t1.lastMsgDirection = t2.direction, t1.lastMsgAt = t2.createdAt WHERE t1.id = t2.' + channels[i] + 'InteractionId;');
200 migration.query('UPDATE fax_interactions t1, (SELECT FaxInteractionId, MAX(id) AS messageid, ANY_VALUE(direction) AS direction, ANY_VALUE(createdAt) AS createdAt \
202 WHERE FaxInteractionId IS NOT NULL \
203 GROUP BY FaxInteractionId) t2 SET t1.lastMsgDirection = t2.direction, t1.lastMsgAt = t2.createdAt WHERE t1.id = t2.FaxInteractionId;');
206 migration.changeColumn('sms_accounts', 'type', {
207 type: Sequelize.ENUM('twilio', 'skebby', 'connectel')
211 // START VOICECALLREPORT
212 migration.addColumn('report_call', 'note', {
213 type: Sequelize.STRING
215 // END VOICECALLREPORT
218 migration.addIndex('cm_contacts', ['phone'], 'phone');
219 migration.addIndex('cm_hopper_final', ['uniqueid'], 'uniqueid');
220 migration.addIndex('cm_hopper_history', ['uniqueid'], 'uniqueid');
223 //START VOICERECORDINGS
224 migration.addIndex('voice_recordings', ['uniqueid'], 'uniqueid');
225 //END VOICERECORDINGS
228 migration.addIndex('report_member', ['membername', 'interface'], 'membername_interface');
232 migration.query('UPDATE users SET permissions=\'101,102,103,110,104,105,106,107,108,109,100\' WHERE permissions=\'101,102,103,104,105,106,107,108,109,100\'');
236 migration.query("DELETE FROM `voice_extensions` WHERE exten = 'xcally-motion-dialer';");
238 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '1', 'NoOp', '', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
240 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '2', 'set', 'CDR(type)=dialer', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
242 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '3', 'set', 'CALLERID(all)=${XMD-CALLERID}', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
244 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '4', 'gotoif', '$[\"${XMD-AMD}\" != \"NONE\" ]?5:11', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
246 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '5', 'answer', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
248 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '6', 'background', 'silence/1', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
250 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '7', 'amd', '${XMD-AMDINITIALSILENCE},${XMD-AMDGREETING},${XMD-AMDAFTERGREETINGSILENCE},${XMD-AMDTOTALANALYSISTIME},${XMD-AMDMINWORDLENGTH},${XMD-AMDBETWEENWORDSSILENCE},${XMD-AMDMAXIMUMNUMBEROFWORDS},${XMD-AMDSILENCETHRESHOLD},${XMD-AMDMAXIMUMWORDLENGTH}', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
252 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '8', 'gotoif', '$[\"${AMDSTATUS}\" == \"HUMAN\" ]?11:9', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
254 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`,`createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '9', 'gotoif', '$[\"${AMDSTATUS}\" == \"NOTSURE\" ]?11:10', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
256 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`,`createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '10', 'gotoif', '$[\"${AMDSTATUS}\" == \"MACHINE\" ]?14:11', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
258 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '11', 'execif', '$[\"${XMD-AGI}\" != \"NONE\" ]?AGI(${XMD-AGI})', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
260 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '12', 'execif', '$[\"${XMD-QUEUE}\" != \"NONE\" ]?QUEUE(${XMD-QUEUE})', 'system', 'Dialer extensions auto generated', NOW(), NOW());");
262 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `appdata`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '13', 'Execif', '$[\"${XMD-AGIAFTER}\" != \"NONE\" ]?AGI(${XMD-AGIAFTER})', 'system', 'dialer extensions auto generated', NOW(), NOW());");
264 migration.query("INSERT INTO `voice_extensions` (`context`, `exten`, `priority`, `app`, `type`, `description`, `createdAt`, `updatedAt`) VALUES ('from-sip', 'xcally-motion-dialer', '14', 'hangup', 'system', 'dialer extensions auto generated', NOW(), NOW());");
268 migration.final(resolve);
273 down: function(queryInterface, Sequelize) {
274 var migration = new Migration(queryInterface);