Sunday, April 5, 2020

Shoretel CDR Database Schema Upgrade 19.XX to 21.82

Schema Upgrade Script Before Import Old Records

Immediately after upgrading from Shoreware 19.XX to Shoreware 21.82, you will want to import your old CDR records.  Before you can do that, you must upgrade the old CDR records by adding some new fields.  My upgrade script will take your old CDR and add those fields.  To use it, import your old CDR into a stand-alone mysql instance and then upgrade it using my handy script, then export it out, finally import the records into your Shoreware director.

Create a Stand-alone Instance of the CDR database


1
2
3
4
5
drop database shorewarecdr;
CREATE DATABASE shorewarecdr;
GRANT ALL ON shorewarecdr.* TO st_cdrreport@localhost IDENTIFIED BY 'passwordcdrreport';
GRANT ALL ON shorewarecdr.* TO `root`@`127.0.0.1` IDENTIFIED BY 'passwordcdrreport';
FLUSH PRIVILEGES;

Upgrade the old CDR


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Alter table `agentactivity` Add `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `Archived`;
ALTER TABLE `agentactivity` Add `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

Alter table `audio_attendee` Add `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `is_host`;
ALTER TABLE `audio_attendee` Add `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

Alter table `call` Add `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `ConfAdjDuration`;
Alter table `call` Add `StartTimeUTC` datetime DEFAULT NULL AFTER `TenantID`;
Alter table `call` Add `EndTimeUTC` datetime DEFAULT NULL AFTER `StartTimeUTC`;
ALTER TABLE `call` Add `ClusterID` int(11) DEFAULT '0' AFTER `EndTimeUTC`;
ALTER TABLE `call` Add `RelatedCallGUID` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL AFTER `ClusterID`;

ALTER TABLE `connect` ADD  `TenantID` int(11) NOT NULL DEFAULT '1'  AFTER `AOC_E`;
ALTER TABLE `connect` ADD  `ProfileID` int(11) DEFAULT '0'          AFTER `TenantID`;
ALTER TABLE `connect` ADD  `AccountID` int(11) DEFAULT '0'          AFTER `ProfileID`;
ALTER TABLE `connect` ADD  `StartTimeUTC` datetime DEFAULT NULL     AFTER `AccountID`;
ALTER TABLE `connect` ADD  `EndTimeUTC` datetime DEFAULT NULL       AFTER `StartTimeUTC`;
ALTER TABLE `connect` ADD  `FPGSync` tinyint(4) DEFAULT '0'         AFTER `EndTimeUTC`;
ALTER TABLE `connect` ADD  `FCLSync` tinyint(4) DEFAULT '0'         AFTER `FPGSync`;
ALTER TABLE `connect` ADD  `DMSync` tinyint(4) DEFAULT '0'          AFTER `FCLSync`;
ALTER TABLE `connect` ADD  `SwitchName` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL  AFTER `DMSync`;
ALTER TABLE `connect` ADD  `ClusterID` int(11) DEFAULT '0'  AFTER `SwitchName`;
ALTER TABLE `connect` ADD  `PartyDID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL AFTER `ClusterID`;
ALTER TABLE `connect` ADD  `ExternalSIPCallID` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL  AFTER `PartyDID`;

ALTER TABLE `mediastream` ADD `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `CallLegType`;
ALTER TABLE `mediastream` ADD `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

ALTER TABLE `queuecall` ADD  `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `ServerName`;
ALTER TABLE `queuecall` ADD  `ClusterID` int(11) DEFAULT '0' AFTER `TenantID`;

ALTER TABLE `queuedepth` ADD  `TenantID` int(11) NOT NULL DEFAULT '1'  AFTER `QueueDepth`;
ALTER TABLE `queuedepth` ADD  `ClusterID` int(11) DEFAULT '0'  AFTER `TenantID`;

ALTER TABLE `queuestep` ADD  `TenantID` int(11) NOT NULL DEFAULT '1' AFTER `Hunting`;
ALTER TABLE `queuestep` ADD  `ClusterID` int(11) DEFAULT '0' AFTER `TenantID` ;

/* WE DONT USE WEB_ATTENDEE AND WEB_SESSION */

Export the newly upgrade CDR


1
2
mysqldump --user=st_cdrreport --password=passwordcdrreport   --no-create-info  shorewarecdr call connect mediastream queuecall queuedepth queuestep > dump.sql
mysqldump --user=st_cdrreport --password=passwordcdrreport  shorewarecdr agentactivity > dump2.sql


No comments:

Post a Comment