[sr-dev] git:master: modules:sipcapture fixed sql schema to version 4. Added optimized partrotation script.
Alexandr Dubovikov
alexandr.dubovikov at gmail.com
Wed Sep 10 20:49:52 CEST 2014
Module: sip-router
Branch: master
Commit: 54671510ee5864f51cf5079fd2d59df6b00b88d8
URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=54671510ee5864f51cf5079fd2d59df6b00b88d8
Author: Alexandr Dubovikov <alexandr.dubovikov at gmail.com>
Committer: Alexandr Dubovikov <alexandr.dubovikov at gmail.com>
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)
);
-
More information about the sr-dev
mailing list