Module: sip-router Branch: adubovikov/homer Commit: fd49c4d7e1df51a15a143d7a7fa52b3b23c7df92 URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=fd49c4d… Author: Alexandr Dubovikov &lt;alexandr.dubovikov(a)gmail.com&gt; Committer: Alexandr Dubovikov &lt;alexandr.dubovikov(a)gmail.com&gt; Date: Sat Aug 20 00:38:44 2011 +0200 Crontab script to clear the captute table for the next day. --- modules/sipcapture/examples/crontabtruncate.pl | 28 ++++++++++++++++++++++++ 1 files changed, 28 insertions(+), 0 deletions(-) diff --git a/modules/sipcapture/examples/crontabtruncate.pl b/modules/sipcapture/examples/crontabtruncate.pl new file mode 100644 index 0000000..50bddda --- /dev/null +++ b/modules/sipcapture/examples/crontabtruncate.pl @@ -0,0 +1,28 @@ +#!/usr/bin/perl + +#Crontab script, to clear table/partition for the next day +#set crontab at 23:50 + +my $mysqlstring = "/usr/bin/mysql -uhomer_user -phomer_password -hlocalhost homer_db"; + +#homer node +my $wday = (localtime())[6] + 1; + +#uncomment if you use separate tables +#Separate tables +#for(my $i=0; $i < 24; $i++) { +# my $query = sprintf("TRUNCATE TABLE sip_capture_%02d_%02d", $wday, $i); +# `echo \"$query\"| $mysqlstring`; +#} + +#uncomment if you use partitioning table and MySQL >= 5.5 +#my $query = sprintf("ALTER TABLE sip_capture TRUNCATE PARTITION p%d", $wday); +#`echo \"$query\"| $mysqlstring`; + +#or Mysql < 5.5 +my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time() - (86400*6)); +$year+=1900; +$mon++; + +my $query = sprintf("DELETE FROM sip_capture WHERE `date` < '%d-%02d-%02d 00:00:00' ", $year, $mon, $mday); +`echo \"$query\"| $mysqlstring`;