Backup old Calls

From Kolmisoft Wiki
Revision as of 08:25, 22 December 2009 by Admin (talk | contribs)
Jump to navigationJump to search

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

Possible to move some old Calls to backup table.

Warning.pngMAKE DB BACKUP BEFORE YOU TRY ALL THESE COMMANDS!

These SQL commands are for MOR 8.

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';