[sr-dev] git:master: modules:sipcapture fixed sql schema to version 4. Added optimized partrotation script.

Module: sip-router Branch: master Commit: 54671510ee5864f51cf5079fd2d59df6b00b88d8 URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=5467151… Author: Alexandr Dubovikov &lt;alexandr.dubovikov(a)gmail.com&gt; Committer: Alexandr Dubovikov &lt;alexandr.dubovikov(a)gmail.com&gt; Date: Wed Sep 10 20:49:07 2014 +0200 modules:sipcapture fixed sql schema to version 4. Added optimized partrotation script. --- .../examples/partrotate_unixtimestamp.pl | 109 +++++++------------- modules/sipcapture/sql/create_sipcapture.sql | 13 +-- 2 files changed, 43 insertions(+), 79 deletions(-) diff --git a/modules/sipcapture/examples/partrotate_unixtimestamp.pl b/modules/sipcapture/examples/partrotate_unixtimestamp.pl index bca92a6..de6737b 100644 --- a/modules/sipcapture/examples/partrotate_unixtimestamp.pl +++ b/modules/sipcapture/examples/partrotate_unixtimestamp.pl @@ -22,7 +22,7 @@ use DBI; -$version = "0.3.0"; +$version = "0.3.1k"; $mysql_table = "sip_capture"; $mysql_dbname = "homer_db"; $mysql_user = "mysql_login"; @@ -63,62 +63,6 @@ $auth_column = "authorization" if($sql_schema_version == 1); #$db->{PrintError} = 0; -$sql = "CREATE TABLE IF NOT EXISTS `".$mysql_table."` ( - `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, - `date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', - `micro_ts` bigint(18) NOT NULL DEFAULT '0', - `method` varchar(50) NOT NULL DEFAULT '', - `reply_reason` varchar(100) NOT NULL, - `ruri` varchar(200) NOT NULL DEFAULT '', - `ruri_user` varchar(100) NOT NULL DEFAULT '', - `from_user` varchar(100) NOT NULL DEFAULT '', - `from_tag` varchar(64) NOT NULL DEFAULT '', - `to_user` varchar(100) NOT NULL DEFAULT '', - `to_tag` varchar(64) NOT NULL, - `pid_user` varchar(100) NOT NULL DEFAULT '', - `contact_user` varchar(120) NOT NULL, - `auth_user` varchar(120) NOT NULL, - `callid` varchar(100) NOT NULL DEFAULT '', - `callid_aleg` varchar(100) NOT NULL DEFAULT '', - `via_1` varchar(256) NOT NULL, - `via_1_branch` varchar(80) NOT NULL, - `cseq` varchar(25) NOT NULL, - `diversion` varchar(256) NOT NULL, - `reason` varchar(200) NOT NULL, - `content_type` varchar(256) NOT NULL, - `".$auth_column."` varchar(120) NOT NULL, - `user_agent` varchar(256) NOT NULL, - `source_ip` varchar(60) NOT NULL DEFAULT '', - `source_port` int(10) NOT NULL, - `destination_ip` varchar(60) NOT NULL DEFAULT '', - `destination_port` int(10) NOT NULL, - `contact_ip` varchar(60) NOT NULL, - `contact_port` int(10) NOT NULL, - `originator_ip` varchar(60) NOT NULL DEFAULT '', - `originator_port` int(10) NOT NULL, - `proto` int(5) NOT NULL, - `family` int(1) DEFAULT NULL, - `rtp_stat` varchar(256) NOT NULL, - `type` int(2) NOT NULL, - `node` varchar(125) NOT NULL, - `msg` text NOT NULL, - PRIMARY KEY (`id`,`date`), - KEY `ruri_user` (`ruri_user`), - KEY `from_user` (`from_user`), - KEY `to_user` (`to_user`), - KEY `pid_user` (`pid_user`), - KEY `auth_user` (`auth_user`), - KEY `callid_aleg` (`callid_aleg`), - KEY `date` (`date`), - KEY `callid` (`callid`), - KEY `method` (`method`), - KEY `source_ip` (`source_ip`), - KEY `destination_ip` (`destination_ip`) -) ENGINE=".$engine." DEFAULT CHARSET=utf8 $compress -PARTITION BY RANGE ( UNIX_TIMESTAMP(`date`)) (PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = ".$engine.")"; - -my $sth = $db->do($sql) if($check_table == 1); - #check if the table has partitions. If not, create one my $query = "SHOW TABLE STATUS FROM ".$mysql_dbname. " WHERE Name='".$mysql_table."'"; $sth = $db->prepare($query); @@ -138,6 +82,7 @@ $curtstamp+=0; $todaytstamp+=0; + my %PARTS; #Geting all partitions $query = "SELECT PARTITION_NAME, PARTITION_DESCRIPTION" @@ -145,25 +90,50 @@ $query = "SELECT PARTITION_NAME, PARTITION_DESCRIPTION" ."\n AND TABLE_SCHEMA='".$mysql_dbname."' ORDER BY PARTITION_DESCRIPTION ASC;"; $sth = $db->prepare($query); $sth->execute(); -my ($partcount) = $sth->rows; -while(my ($minpart,$todaytstamp) = $sth->fetchrow_array()) { - - if($partcount <= $totalparts || $curtstamp <= $todaytstamp) { - #Creating HASH of existing partitions +my @oldparts; +my $newparts = 0; +my @partsremove; +while(my @ref = $sth->fetchrow_array()) +{ + + my $minpart = $ref[0]; + my $todaytstamp = $ref[1]; + + next if($minpart eq "pmax"); + + if($curtstamp <= $todaytstamp) { $PARTS{$minpart."_".$todaytstamp} = 1; - next; + $newparts++; + } + else { push(@oldparts, \@ref); } + +} + +my $partcount = $#oldparts; +if($partcount > $maxparts) +{ + foreach my $ref (@oldparts) { + + $minpart = $ref->[0]; + $todaytstamp = $ref->[1]; + + push(@partsremove,$minpart); + + $partcount--; + last if($partcount <= $maxparts); } - - next if($minpart eq "pmax"); - - $query = "ALTER TABLE ".$mysql_table." DROP PARTITION ".$minpart; +} + + +if($#partsremove > 0) +{ + + $query = "ALTER TABLE ".$mysql_table." DROP PARTITION ".join(',', @partsremove); $db->do($query); if (!$db->{Executed}) { print "Couldn't drop partition: $minpart\n"; break; } - - $partcount--; } # < condition @@ -191,4 +161,3 @@ for(my $i=0; $i<$newparts; $i++) { } } } - diff --git a/modules/sipcapture/sql/create_sipcapture.sql b/modules/sipcapture/sql/create_sipcapture.sql index 20ae09b..a7c31fa 100644 --- a/modules/sipcapture/sql/create_sipcapture.sql +++ b/modules/sipcapture/sql/create_sipcapture.sql @@ -1,12 +1,7 @@ -/* - * only for MYSQL >= 5.1.43 -*/ -/* this SQL schema version # 3 */ +/* this is SQL schema version # 4 */ -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `sip_capture` ( +CREATE TABLE IF NOT EXISTS `sip_capture` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `micro_ts` bigint(18) NOT NULL DEFAULT '0', @@ -29,7 +24,7 @@ CREATE TABLE `sip_capture` ( `via_1` varchar(256) NOT NULL, `via_1_branch` varchar(80) NOT NULL, `cseq` varchar(25) NOT NULL, - `diversion` varchar(256) NOT NULL DEFAULT '', + `diversion` varchar(256) NOT NULL, `reason` varchar(200) NOT NULL, `content_type` varchar(256) NOT NULL, `auth` varchar(256) NOT NULL, @@ -42,6 +37,7 @@ CREATE TABLE `sip_capture` ( `contact_port` int(10) NOT NULL, `originator_ip` varchar(60) NOT NULL DEFAULT '', `originator_port` int(10) NOT NULL, + `correlation_id` varchar(256) NOT NULL, `proto` int(5) NOT NULL, `family` int(1) DEFAULT NULL, `rtp_stat` varchar(256) NOT NULL, @@ -61,4 +57,3 @@ CREATE TABLE `sip_capture` ( PARTITION BY RANGE ( UNIX_TIMESTAMP(`date`) ) ( PARTITION pmax VALUES LESS THAN (MAXVALUE) ); -