Backup old Calls

From Kolmisoft Wiki

Jump to: navigation, search

When too much Calls are in B - it slows down GUI.

Possible to move some old Calls to backup table.

MAKE DB BACKUP BEFORE YOU TRY ALL THESE COMMANDS!

These SQL commands are for MOR 8 ONLY.

Create backup table:

CREATE TABLE IF NOT EXISTS `calls_backup1` (
 `id` int(11) NOT NULL auto_increment,
 `calldate` datetime NOT NULL default '0000-00-00 00:00:00',
 `clid` varchar(80) NOT NULL,
 `src` varchar(80) NOT NULL,
 `dst` varchar(80) NOT NULL,
 `dcontext` varchar(80) NOT NULL,
 `channel` varchar(80) NOT NULL,
 `dstchannel` varchar(80) NOT NULL,
 `lastapp` varchar(80) NOT NULL,
 `lastdata` varchar(80) NOT NULL,
 `duration` int(11) NOT NULL default '0',
 `billsec` int(11) NOT NULL default '0',
 `disposition` varchar(45) NOT NULL,
 `amaflags` int(11) NOT NULL default '0',
 `accountcode` varchar(20) NOT NULL,
 `uniqueid` varchar(32) NOT NULL,
 `userfield` varchar(255) NOT NULL,
 `src_device_id` int(11) NOT NULL default '0',
 `dst_device_id` int(11) NOT NULL default '0',
 `processed` tinyint(4) NOT NULL default '0',
 `did_price` double NOT NULL default '0',
 `card_id` int(11) default NULL,
 `provider_id` int(11) default NULL,
 `provider_rate` double default NULL,
 `provider_billsec` int(11) default NULL,
 `provider_price` double default NULL,
 `user_id` int(11) default NULL,
 `user_rate` double default NULL,
 `user_billsec` int(11) default NULL,
 `user_price` double default NULL,
 `reseller_id` int(11) default NULL,
 `reseller_rate` double default NULL,
 `reseller_billsec` int(11) default NULL,
 `reseller_price` double default NULL,
 `partner_id` int(11) default NULL,
 `partner_rate` double default NULL,
 `partner_billsec` int(11) default NULL,
 `partner_price` double default NULL,
 `prefix` varchar(50) default NULL,
 `server_id` int(11) default '1',
 `hangupcause` int(11) default NULL,
 `callertype` enum('Local','Outside') default 'Local',
 `peerip` varchar(255) default NULL,
 `recvip` varchar(255) default NULL,
 `sipfrom` varchar(255) default NULL,
 `uri` varchar(255) default NULL,
 `useragent` varchar(255) default NULL,
 `peername` varchar(255) default NULL,
 `t38passthrough` tinyint(4) default NULL,
 `did_inc_price` double default '0',
 `did_prov_price` double default '0',
 `localized_dst` varchar(50) default NULL,
 `did_provider_id` int(11) default '0',
 `did_id` int(11) default NULL,
 `originator_ip` varchar(20) default NULL,
 `terminator_ip` varchar(20) default NULL,
 `real_duration` double NOT NULL default '0' COMMENT 'exact duration',
 `real_billsec` double NOT NULL default '0' COMMENT 'exact billsec',
 `did_billsec` int(11) default '0' COMMENT 'billsec for incoming call',
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Copy old calls (change period to your needs):

INSERT INTO calls_backup1 (calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, userfield, src_device_id, dst_device_id, processed, did_price, card_id, provider_id, provider_rate, provider_billsec, provider_price, user_id, user_rate, user_billsec, user_price, reseller_id, reseller_rate, reseller_billsec, reseller_price, partner_id, partner_rate, partner_billsec, partner_price, prefix, server_id, hangupcause, callertype, peerip, recvip, sipfrom, uri, useragent, peername, t38passthrough, did_inc_price, did_prov_price, localized_dst, did_provider_id, did_id, originator_ip, terminator_ip, real_duration, real_billsec, did_billsec)
SELECT calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, userfield, src_device_id, dst_device_id, processed, did_price, card_id, provider_id, provider_rate, provider_billsec, provider_price, user_id, user_rate, user_billsec, user_price, reseller_id, reseller_rate, reseller_billsec, reseller_price, partner_id, partner_rate, partner_billsec, partner_price, prefix, server_id, hangupcause, callertype, peerip, recvip, sipfrom, uri, useragent, peername, t38passthrough, did_inc_price, did_prov_price, localized_dst, did_provider_id, did_id, originator_ip, terminator_ip, real_duration, real_billsec, did_billsec
FROM calls WHERE calldate BETWEEN  '2009-08-01 00:00:00' AND '2009-09-01 00:00:00';

Delete calls from original table (use same period then in previous query!):

DELETE FROM calls WHERE calldate BETWEEN  '2009-08-01 00:00:00' AND '2009-09-01 00:00:00';
Personal tools

Try MOR

Try M2