help with vici SQL update script

[TheIxian]

I need to update some vicidial databases from version 2.4b0.5 to 2.6b0.5 and have come up with the following SQL script:

I could use a second set of eyes to tell me if this is a complete set of instructions to run against the databases or if I am missing something.

Currently, when I run this script against an imported database, it does not seem to extend the database for use with 2.6.

I generated the following script by using phpMyAdmin and comparing a newly created 2.6 database against an imported 2.4 database, adding the missing tables/columns and then copying/pasting the generated SQL lines into a text file.

I am not an SQL guy so if someone with more experience can help me out, I would appreciate it.

Code: Select all

CREATE TABLE IF NOT EXISTS `contact_information` (
  `contact_id` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) COLLATE utf8_bin DEFAULT '',
  `last_name` varchar(50) COLLATE utf8_bin DEFAULT '',
  `office_num` varchar(20) COLLATE utf8_bin DEFAULT '',
  `cell_num` varchar(20) COLLATE utf8_bin DEFAULT '',
  `other_num1` varchar(20) COLLATE utf8_bin DEFAULT '',
  `other_num2` varchar(20) COLLATE utf8_bin DEFAULT '',
  `bu_name` varchar(100) COLLATE utf8_bin DEFAULT '',
  `department` varchar(100) COLLATE utf8_bin DEFAULT '',
  `group_name` varchar(100) COLLATE utf8_bin DEFAULT '',
  `job_title` varchar(100) COLLATE utf8_bin DEFAULT '',
  `location` varchar(100) COLLATE utf8_bin DEFAULT '',
  PRIMARY KEY (`contact_id`),
  KEY `ci_first_name` (`first_name`),
  KEY `ci_last_name` (`last_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `dialable_inventory_snapshots` (
  `snapshot_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `snapshot_time` datetime DEFAULT NULL,
  `list_id` bigint(14) unsigned DEFAULT NULL,
  `list_name` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `list_description` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `campaign_id` varchar(8) COLLATE utf8_bin DEFAULT NULL,
  `list_lastcalldate` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `list_start_inv` mediumint(8) unsigned DEFAULT NULL,
  `dialable_count` mediumint(8) unsigned DEFAULT NULL,
  `dialable_count_nofilter` mediumint(8) unsigned DEFAULT NULL,
  `dialable_count_oneoff` mediumint(8) unsigned DEFAULT NULL,
  `dialable_count_inactive` mediumint(8) unsigned DEFAULT NULL,
  `average_call_count` decimal(3,1) DEFAULT NULL,
  `penetration` decimal(5,2) DEFAULT NULL,
  `shift_data` text COLLATE utf8_bin,
  `time_setting` enum('LOCAL','SERVER') COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`snapshot_id`),
  UNIQUE KEY `snapshot_date_list_key` (`snapshot_time`,`list_id`,`time_setting`),
  KEY `snapshot_date_key` (`snapshot_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `vicidial_campaign_cid_areacodes` (
  `campaign_id` varchar(8) COLLATE utf8_bin NOT NULL,
  `areacode` varchar(5) COLLATE utf8_bin NOT NULL,
  `outbound_cid` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `active` enum('Y','N','') COLLATE utf8_bin DEFAULT '',
  `cid_description` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `call_count_today` mediumint(7) DEFAULT '0',
  UNIQUE KEY `campareacode` (`campaign_id`,`areacode`,`outbound_cid`),
  KEY `campaign_id` (`campaign_id`),
  KEY `areacode` (`areacode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE  TABLE IF NOT EXISTS `vicidial_custom_leadloader_templates` ( 
 `template_id` varchar( 20  )  COLLATE utf8_bin NOT  NULL ,
 `template_name` varchar( 30  )  COLLATE utf8_bin  DEFAULT NULL ,
 `template_description` varchar( 255  )  COLLATE utf8_bin  DEFAULT NULL ,
 `list_id` int( 10  ) unsigned  DEFAULT NULL ,
 `standard_variables` text COLLATE utf8_bin,
 `custom_table` varchar( 20  )  COLLATE utf8_bin  DEFAULT NULL ,
 `custom_variables` text COLLATE utf8_bin,
 PRIMARY  KEY (  `template_id`  ) 
 ) ENGINE  =  MyISAM  DEFAULT CHARSET  = utf8 COLLATE  = utf8_bin;
 
  CREATE  TABLE IF NOT EXISTS `vicidial_daily_max_stats` ( 
 `stats_date` date NOT  NULL ,
 `stats_flag` enum(  'OPEN',  'CLOSED',  'CLOSING'  )  COLLATE utf8_bin DEFAULT  'CLOSED',
 `stats_type` enum(  'TOTAL',  'INGROUP',  'CAMPAIGN',  ''  )  COLLATE utf8_bin DEFAULT  '',
 `campaign_id` varchar( 20  )  COLLATE utf8_bin DEFAULT  '',
 `update_time` timestamp NOT  NULL  DEFAULT CURRENT_TIMESTAMP  ON  UPDATE  CURRENT_TIMESTAMP ,
 `closed_time` datetime  DEFAULT NULL ,
 `max_channels` mediumint( 8  ) unsigned DEFAULT  '0',
 `max_calls` mediumint( 8  ) unsigned DEFAULT  '0',
 `max_inbound` mediumint( 8  ) unsigned DEFAULT  '0',
 `max_outbound` mediumint( 8  ) unsigned DEFAULT  '0',
 `max_agents` mediumint( 8  ) unsigned DEFAULT  '0',
 `max_remote_agents` mediumint( 8  ) unsigned DEFAULT  '0',
 `total_calls` int( 9  ) unsigned DEFAULT  '0',
 KEY  `stats_date` (  `stats_date`  ) ,
 KEY  `stats_flag` (  `stats_flag`  ) ,
 KEY  `campaign_id` (  `campaign_id`  ) 
 ) ENGINE  =  MyISAM  DEFAULT CHARSET  = utf8 COLLATE  = utf8_bin;
 
 CREATE TABLE IF NOT EXISTS `vicidial_daily_ra_stats` (
  `stats_date` date NOT NULL,
  `stats_flag` enum('OPEN','CLOSED','CLOSING') COLLATE utf8_bin DEFAULT 'CLOSED',
  `user` varchar(20) COLLATE utf8_bin DEFAULT '',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `closed_time` datetime DEFAULT NULL,
  `max_calls` mediumint(8) unsigned DEFAULT '0',
  `total_calls` int(9) unsigned DEFAULT '0',
  KEY `stats_date` (`stats_date`),
  KEY `stats_flag` (`stats_flag`),
  KEY `user` (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `vicidial_did_agent_log` (
  `uniqueid` varchar(20) COLLATE utf8_bin NOT NULL,
  `server_ip` varchar(15) COLLATE utf8_bin NOT NULL,
  `caller_id_number` varchar(18) COLLATE utf8_bin DEFAULT NULL,
  `caller_id_name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `extension` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `call_date` datetime DEFAULT NULL,
  `did_id` varchar(9) COLLATE utf8_bin DEFAULT '',
  `did_description` varchar(50) COLLATE utf8_bin DEFAULT '',
  `did_route` varchar(9) COLLATE utf8_bin DEFAULT '',
  `group_id` varchar(20) COLLATE utf8_bin DEFAULT '',
  `user` varchar(20) COLLATE utf8_bin DEFAULT 'VDCL',
  KEY `uniqueid` (`uniqueid`),
  KEY `caller_id_number` (`caller_id_number`),
  KEY `extension` (`extension`),
  KEY `call_date` (`call_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `vicidial_did_agent_log_archive` (
  `uniqueid` varchar(20) COLLATE utf8_bin NOT NULL,
  `server_ip` varchar(15) COLLATE utf8_bin NOT NULL,
  `caller_id_number` varchar(18) COLLATE utf8_bin DEFAULT NULL,
  `caller_id_name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `extension` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `call_date` datetime DEFAULT NULL,
  `did_id` varchar(9) COLLATE utf8_bin DEFAULT '',
  `did_description` varchar(50) COLLATE utf8_bin DEFAULT '',
  `did_route` varchar(9) COLLATE utf8_bin DEFAULT '',
  `group_id` varchar(20) COLLATE utf8_bin DEFAULT '',
  `user` varchar(20) COLLATE utf8_bin DEFAULT 'VDCL',
  UNIQUE KEY `vdala` (`uniqueid`,`call_date`,`did_route`),
  KEY `uniqueid` (`uniqueid`),
  KEY `caller_id_number` (`caller_id_number`),
  KEY `extension` (`extension`),
  KEY `call_date` (`call_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `vicidial_did_log` (
  `uniqueid` varchar(20) COLLATE utf8_bin NOT NULL,
  `channel` varchar(100) COLLATE utf8_bin NOT NULL,
  `server_ip` varchar(15) COLLATE utf8_bin NOT NULL,
  `caller_id_number` varchar(18) COLLATE utf8_bin DEFAULT NULL,
  `caller_id_name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `extension` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `call_date` datetime DEFAULT NULL,
  `did_id` varchar(9) COLLATE utf8_bin DEFAULT '',
  `did_route` varchar(9) COLLATE utf8_bin DEFAULT '',
  KEY `uniqueid` (`uniqueid`),
  KEY `caller_id_number` (`caller_id_number`),
  KEY `extension` (`extension`),
  KEY `call_date` (`call_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `vicidial_did_ra_extensions` (
  `did_id` int(9) unsigned NOT NULL,
  `user_start` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `extension` varchar(50) COLLATE utf8_bin DEFAULT '',
  `description` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `active` enum('Y','N','') COLLATE utf8_bin DEFAULT '',
  `call_count_today` mediumint(7) DEFAULT '0',
  UNIQUE KEY `didraexten` (`did_id`,`user_start`,`extension`),
  KEY `did_id` (`did_id`),
  KEY `user_start` (`user_start`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
ALTER TABLE `groups_alias`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `phones`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `phones_alias`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `servers`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `system_settings`  ADD `did_agent_log` ENUM('Y', 'N') NULL DEFAULT 'N',  ADD `campaign_cid_areacodes_enabled` ENUM('0','1') NULL DEFAULT '1',  ADD `pllb_grouping_limit` SMALLINT(5) NULL DEFAULT '100',  ADD `did_ra_extensions_enabled` ENUM('0','1') NULL DEFAULT '0',  ADD `expanded_list_stats` ENUM('0','1') NULL DEFAULT '1',  ADD `contacts_enabled` ENUM('0','1') NULL DEFAULT '0',  ADD `svn_version` VARCHAR(100) NULL,  ADD `call_menu_qualify_enabled` ENUM('0','1') NULL DEFAULT '0';
ALTER TABLE `vicidial_admin_log`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_call_menu`  ADD `dtmf_field` VARCHAR(50) NULL DEFAULT 'NONE',  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---',  ADD `qualify_sql` TEXT NULL DEFAULT NULL;
ALTER TABLE `vicidial_call_times`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_campaigns`  ADD `survey_recording` ENUM('Y', 'N', 'Y_WITH_AMD') NULL DEFAULT 'N',  ADD ` pllb_grouping` ENUM('DISABLED', 'ONE_SERVER_ONLY', 'CASCADING') NULL DEFAULT 'DISABLED',  ADD `pllb_grouping_limit` SMALLINT(5) NULL,  ADD `call_count_limit` SMALLINT(5) NULL,  ADD `call_count_target` SMALLINT(5) NULL,  ADD `callback_hours_block` TINYINT(2) NULL,  ADD `callback_list_calltime` ENUM('ENABLED', 'DISABLED') NULL DEFAULT 'DISABLED',  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---',  ADD `hopper_vlc_dup_check` ENUM('Y', 'N') NULL DEFAULT 'N',  ADD `in_group_dial` ENUM('DISABLED', 'MANUAL_DIAL', 'NO_DIAL', 'BOTH') NULL DEFAULT 'DISABLED',  ADD `in_group_dial_select` ENUM('AGENT_SELECTED','CAMPAIGN_SELECTED','ALL_USER_GROUP') NULL DEFAULT 'CAMPAIGN_SELECTED',  ADD `safe_harbor_audio_field` VARCHAR(30) NULL DEFAULT 'DISABLED';
ALTER TABLE `vicidial_campaign_agents`  ADD `campaign_grade` TINYINT(2) UNSIGNED NULL DEFAULT '1';
ALTER TABLE `vicidial_campaign_statuses`  ADD `completed` ENUM('Y', 'N') NULL DEFAULT 'N';
ALTER TABLE `vicidial_conf_templates`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_filter_phone_groups`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_hopper`  ADD `vendor_lead_code` VARCHAR(20) NULL;
ALTER TABLE `vicidial_inbound_dids`  ADD `custom_one` VARCHAR(100) NULL,  ADD `custom_two` VARCHAR(100) NULL,  ADD `custom_three` VARCHAR(100) NULL,  ADD `custom_four` VARCHAR(100) NULL,  ADD `custom_five` VARCHAR(100) NULL,  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_inbound_groups`  ADD `action_xfer_cid` VARCHAR(18) NULL DEFAULT 'CUSTOMER',  ADD `drop_callmenu` VARCHAR(50) NULL,  ADD `after_hours_callmenu` VARCHAR(50) NULL,  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---',  ADD `max_calls_method` ENUM('TOTAL', 'IN_QUEUE', 'DISABLED') NULL DEFAULT 'DISABLED',  ADD `max_calls_count` SMALLINT(5) NULL DEFAULT '0',  ADD `max_calls_action` ENUM('DROP', 'AFTERHOURS', 'NO_AGENT_NO_QUEUE') NULL DEFAULT 'NO_AGENT_NO_QUEUE',  ADD `dial_ingroup_cid` VARCHAR(20) NULL;
ALTER TABLE `vicidial_inbound_group_agents`  ADD `group_grade` TINYINT(2) UNSIGNED NULL DEFAULT '1';
ALTER TABLE `vicidial_lead_filters`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_lists`  ADD ` inventory_report` ENUM('Y', 'N') NULL DEFAULT 'Y';
ALTER TABLE `vicidial_live_agents`  ADD `campaign_grade` TINYINT(2) UNSIGNED NULL DEFAULT '1';
ALTER TABLE `vicidial_live_inbound_agents`  ADD `group_grade` TINYINT(2) UNSIGNED NULL DEFAULT '1';
ALTER TABLE `vicidial_log_extended`  ADD `noanswer_processed` ENUM('N', 'Y', 'U') NULL DEFAULT 'N';
ALTER TABLE `vicidial_log_extended_archive`  ADD `noanswer_processed` ENUM('N', 'Y', 'U') NULL DEFAULT 'N';
ALTER TABLE `vicidial_music_on_hold`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_screen_labels`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_scripts`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_server_carriers`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_shifts`  ADD `report_option` ENUM('Y', 'N') NULL DEFAULT 'N',  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---',  ADD `report_rank` SMALLINT(5) NULL DEFAULT '1';
ALTER TABLE `vicidial_state_call_times`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_statuses`  ADD `completed` ENUM('Y', 'N') NULL DEFAULT 'N';
ALTER TABLE `vicidial_tts_prompts`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';
ALTER TABLE `vicidial_users`  ADD `modify_shifts` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_phones` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_carriers` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_labels` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_statuses` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_voicemail` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_audiostore` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_moh` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_tts` ENUM('1', '0') NULL DEFAULT '0',  ADD `preset_contact_search` ENUM('NOT_ACTIVE', 'ENABLED', 'DISABLED') NULL DEFAULT 'NOT_ACTIVE',  ADD `modify_contacts` ENUM('1', '0') NULL DEFAULT '0',  ADD `modify_same_user_level` ENUM('1', '0') NULL DEFAULT '1',  ADD `admin_hide_lead_data` ENUM('1', '0') NULL DEFAULT '0',  ADD `admin_hide_phone_data` ENUM('0', '1', '2_DIGITS', '3_DIGITS', '4_DIGITS') NULL DEFAULT '0';
ALTER TABLE `vicidial_user_groups`  ADD `admin_viewable_groups` TEXT NULL DEFAULT NULL,  ADD `admin_viewable_call_times` TEXT NULL DEFAULT NULL;
ALTER TABLE `vicidial_voicemail`  ADD `user_group` VARCHAR(20) NULL DEFAULT '---ALL---';


[williamconley]

use the upgrade script included in the extras folder.

check the dbschema in the "before" data and look inside the sql upgrade script. you'll find that dbschema ... execute all the lines AFTER that entry and you'll end up with a db with the new schema matching the code for the version of vicidial you are using. then the data and vicidial versions will match ... and you're on.

which is a summary of the method upgrading included in the upgrade instructions in the download (although those don't include the execution of only the db scripts AFTER the "before" dbschema, those instructions generally expect you to execute all the instructions in the upgrade sql script ... which "usually" works just fine).

when you post, please post your entire configuration including (but not limited to) your installation method and vicidial version with build.

this IS a requirement for posting along with reading the stickies (at the top of each forum) and the manager's manual (available on EFLO.net, both free and paid versions)

You should also post: Asterisk version, telephony hardware (model number is helpful here), cluster information if you have one, and whether any other software is installed in the box. If your installation method is "from scratch" you must post your operating system and should also post the .iso version from which you installed your original operating system. If your installation is "Hosted" list the site name of the host.

If this is a "Cloud" or "Virtual" server, please note the technology involved along with the version of that techology (ie: VMware Server Version 2.0.2). If it is not, merely stating the Motherboard model # and CPU would be helpful.

Similar to This:

Vicibox X.X from .iso | Vicidial X.X.X-XXX Build XXXXXX-XXXX | Asterisk X.X.X | Single Server | No Digium/Sangoma Hardware | No Extra Software After Installation | Intel DG35EC | Core2Quad Q6600