[sr-dev] git:master: modules:sipcapture: fixed rotation scripts (maxvalue) and SQL schema.

[Alexandr Dubovikov]

Module: sip-router
Branch: master
Commit: 6f8973719551dec797c89452ba1abb87ef1ad894
URL:    http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=6f8973719551dec797c89452ba1abb87ef1ad894

Author: Alexandr Dubovikov <alexandr.dubovikov at gmail.com>
Committer: Alexandr Dubovikov <alexandr.dubovikov at gmail.com>
Date:   Thu Sep 29 15:03:46 2011 +0200

modules:sipcapture: fixed rotation scripts (maxvalue) and SQL schema.

      Thanks Dorn B. <djbinter at gmail.com> for reports and fixes.

---

 modules/sipcapture/examples/partrotate_todays.pl   |    5 +++--
 .../examples/partrotate_unixtimestamp.pl           |    9 ++++++---
 .../sipcapture/sql/create_part_unixtimestamp.sql   |    7 ++-----
 modules/sipcapture/sql/sipcapture-create.sql       |    8 ++------
 4 files changed, 13 insertions(+), 16 deletions(-)

diff --git a/modules/sipcapture/examples/partrotate_todays.pl b/modules/sipcapture/examples/partrotate_todays.pl
index f9e9059..e831743 100644
--- a/modules/sipcapture/examples/partrotate_todays.pl
+++ b/modules/sipcapture/examples/partrotate_todays.pl
@@ -74,8 +74,9 @@ for(my $i=0; $i<$newparts; $i++) {
     
     if(!$exist) {
     
-        $query = "ALTER TABLE ".$table." ADD PARTITION (PARTITION ".$newpartname
-             ."\n VALUES LESS THAN (".$curtodays.") ENGINE = MyISAM)";
+        # Fix MAXVALUE. Thanks Dorn B. <djbinter at gmail.com> for report and fix.
+        $query = "ALTER TABLE ".$table." REORGANIZE PARTITION pmax INTO (PARTITION ".$newpartname
+                                        ."\n VALUES LESS THAN (".$curtodays.") ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM)";                                                         
         $db->do($query);
         if (!$db->{Executed}) {
              print "Couldn't add partition: $newpartname\n";
diff --git a/modules/sipcapture/examples/partrotate_unixtimestamp.pl b/modules/sipcapture/examples/partrotate_unixtimestamp.pl
index 40d36a4..6be3a66 100644
--- a/modules/sipcapture/examples/partrotate_unixtimestamp.pl
+++ b/modules/sipcapture/examples/partrotate_unixtimestamp.pl
@@ -82,10 +82,13 @@ for(my $i=0; $i<$newparts; $i++) {
     $exist+=0;
     
     if(!$exist) {
-    
-        $query = "ALTER TABLE ".$table." ADD PARTITION (PARTITION ".$newpartname
-             ."\n VALUES LESS THAN (".$curtstamp.") ENGINE = MyISAM)";
+
+	# Fix MAXVALUE. Thanks Dorn B. <djbinter at gmail.com> for report and fix.
+        $query = "ALTER TABLE ".$table." REORGANIZE PARTITION pmax INTO (PARTITION ".$newpartname
+                                ."\n VALUES LESS THAN (".$curtstamp.") ENGINE = MyISAM, PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = MyISAM)";  
+
         $db->do($query);
+                    
         if (!$db->{Executed}) {
              print "Couldn't add partition: $newpartname\n";
         }
diff --git a/modules/sipcapture/sql/create_part_unixtimestamp.sql b/modules/sipcapture/sql/create_part_unixtimestamp.sql
index fdcc895..a7f8645 100644
--- a/modules/sipcapture/sql/create_part_unixtimestamp.sql
+++ b/modules/sipcapture/sql/create_part_unixtimestamp.sql
@@ -5,7 +5,7 @@
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `sip_capture` (
-  `id` int(20) NOT NULL AUTO_INCREMENT,
+  `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 '',
@@ -42,7 +42,7 @@ CREATE TABLE `sip_capture` (
   `rtp_stat` varchar(256) NOT NULL,
   `type` int(2) NOT NULL,
   `node` varchar(125) NOT NULL,
-  `msg` longblob NOT NULL,
+  `msg` blob NOT NULL,
   PRIMARY KEY (`id`,`date`),
   KEY `ruri_user` (`ruri_user`),
   KEY `from_user` (`from_user`),
@@ -81,6 +81,3 @@ PARTITION p2011082523 VALUES LESS THAN (1314298800) ENGINE = MyISAM,
 PARTITION pmax VALUES LESS THAN (MAXVALUE)
 );
 
-
-/* if your mysql < 5.5 drop maxvalue partition */
-/* alter table homer_capture drop partition pmax; */
diff --git a/modules/sipcapture/sql/sipcapture-create.sql b/modules/sipcapture/sql/sipcapture-create.sql
index e12a382..155dff2 100644
--- a/modules/sipcapture/sql/sipcapture-create.sql
+++ b/modules/sipcapture/sql/sipcapture-create.sql
@@ -4,7 +4,7 @@ DROP TABLE IF EXISTS `sip_capture`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `sip_capture` (
-  `id` int(20) NOT NULL AUTO_INCREMENT,
+  `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 '',
@@ -41,7 +41,7 @@ CREATE TABLE `sip_capture` (
   `rtp_stat` varchar(256) NOT NULL,
   `type` int(2) NOT NULL,
   `node` varchar(125) NOT NULL,
-  `msg` longblob NOT NULL,
+  `msg` blob NOT NULL,
   PRIMARY KEY (`id`,`date`),
   KEY `ruri_user` (`ruri_user`),
   KEY `from_user` (`from_user`),
@@ -66,7 +66,3 @@ PARTITION p20110902 VALUES LESS THAN (734748) ENGINE = MyISAM,
 PARTITION pmax VALUES LESS THAN (MAXVALUE)
 );
 
-/* alter table homer_capture add partition (PARTITION p20110903 VALUES LESS THAN (734749) ENGINE = MyISAM); */
-/* if your mysql < 5.5 drop maxvalue partition */
-/* alter table sip_capture drop partition pmax; */
-