git:master: modules:sipcapture Changed authorization column to "auth". Now PostgreSQL should be happy.
Module: sip-router Branch: master Commit: 58417e5f23c50a1892584d368e49373c579af31e URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=58417e5f...
Author: Alexandr Dubovikov alexandr.dubovikov@gmail.com Committer: Alexandr Dubovikov alexandr.dubovikov@gmail.com Date: Tue Jan 22 11:41:43 2013 +0100
modules:sipcapture Changed authorization column to "auth". Now PostgreSQL should be happy.
for old schema please use:
modparam("sipcapture", "authorization_column", "authorization")
---
modules/sipcapture/examples/kamailio.cfg | 2 ++ .../examples/partrotate_unixtimestamp.pl | 8 ++++++-- modules/sipcapture/sipcapture.c | 2 +- 3 files changed, 9 insertions(+), 3 deletions(-)
diff --git a/modules/sipcapture/examples/kamailio.cfg b/modules/sipcapture/examples/kamailio.cfg index f6e57de..4edf63a 100644 --- a/modules/sipcapture/examples/kamailio.cfg +++ b/modules/sipcapture/examples/kamailio.cfg @@ -52,6 +52,8 @@ modparam("sipcapture", "raw_moni_capture_on", 0) #modparam("sipcapture", "promiscious_on", 1) /* activate Linux Socket Filter (LSF/BPF) on mirroring interface. Linux only */ #modparam("sipcapture", "raw_moni_bpf_on", 1) +/* !!! old schema !!!! */ +#modparam("sipcapture", "authorization_column", "authorization")
# Main SIP request routing logic # - processing of any incoming SIP request starts with this route diff --git a/modules/sipcapture/examples/partrotate_unixtimestamp.pl b/modules/sipcapture/examples/partrotate_unixtimestamp.pl index a2f5690..ad2e5e7 100644 --- a/modules/sipcapture/examples/partrotate_unixtimestamp.pl +++ b/modules/sipcapture/examples/partrotate_unixtimestamp.pl @@ -32,6 +32,9 @@ $maxparts = 6; #6 days How long keep the data in the DB $newparts = 2; #new partitions for 2 days. Anyway, start this script daily! @stepsvalues = (86400, 3600, 1800, 900); $partstep = 0; # 0 - Day, 1 - Hour, 2 - 30 Minutes, 3 - 15 Minutes +# version 1 = auth_field is "authorization" +$schema_version = 2; +$auth_field = "auth";
#Check it $partstep=0 if(!defined $stepsvalues[$partstep]); @@ -43,11 +46,12 @@ $coof=int(86400/$mystep); #How much partitions $maxparts*=$coof; $newparts*=$coof; +$auth_field = "authorization" if($schema_version == 1); +
my $db = DBI->connect("DBI:mysql:$mysql_dbname:$mysql_host:3306", $mysql_user, $mysql_password);
#$db->{PrintError} = 0; - my $sth = $db->do(" CREATE TABLE IF NOT EXISTS `".$mysql_table."` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, @@ -72,7 +76,7 @@ CREATE TABLE IF NOT EXISTS `".$mysql_table."` ( `diversion` varchar(256) NOT NULL, `reason` varchar(200) NOT NULL, `content_type` varchar(256) NOT NULL, - `authorization` varchar(256) NOT NULL, + `".$auth_field."` varchar(256) NOT NULL, `user_agent` varchar(256) NOT NULL, `source_ip` varchar(50) NOT NULL DEFAULT '', `source_port` int(10) NOT NULL, diff --git a/modules/sipcapture/sipcapture.c b/modules/sipcapture/sipcapture.c index dc74a3b..b857558 100644 --- a/modules/sipcapture/sipcapture.c +++ b/modules/sipcapture/sipcapture.c @@ -136,7 +136,7 @@ static str cseq_column = str_init("cseq"); static str diversion_column = str_init("diversion_user"); static str reason_column = str_init("reason"); static str content_type_column = str_init("content_type"); -static str authorization_column = str_init("authorization"); +static str authorization_column = str_init("auth"); static str user_agent_column = str_init("user_agent"); static str source_ip_column = str_init("source_ip"); static str source_port_column = str_init("source_port");
On 22.01.2013 11:44, Alexandr Dubovikov wrote:
Module: sip-router Branch: master Commit: 58417e5f23c50a1892584d368e49373c579af31e URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=58417e5f...
Author: Alexandr Dubovikovalexandr.dubovikov@gmail.com Committer: Alexandr Dubovikovalexandr.dubovikov@gmail.com Date: Tue Jan 22 11:41:43 2013 +0100
modules:sipcapture Changed authorization column to "auth". Now PostgreSQL should be happy.
Thank you.
I have added this functionality to webHomer too. Please check and let us know.
Thanks,
2013/1/22 Øyvind Kolbu oyvind.kolbu@usit.uio.no
On 22.01.2013 11:44, Alexandr Dubovikov wrote:
Module: sip-router Branch: master Commit: 58417e5f23c50a1892584d368e4937**3c579af31e URL: http://git.sip-router.org/cgi-**bin/gitweb.cgi/sip-router/?a=** commit;h=**58417e5f23c50a1892584d368e4937**3c579af31ehttp://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=58417e5f23c50a1892584d368e49373c579af31e
Author: Alexandr Dubovikov<alexandr.dubovikov@**gmail.comalexandr.dubovikov@gmail.com
Committer: Alexandr Dubovikov<alexandr.dubovikov@**gmail.comalexandr.dubovikov@gmail.com
Date: Tue Jan 22 11:41:43 2013 +0100
modules:sipcapture Changed authorization column to "auth". Now PostgreSQL should be happy.
Thank you.
-- Øyvind
______________________________**_________________ sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/**cgi-bin/mailman/listinfo/sr-**devhttp://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
On 2013-01-22 at 12:44, Alexandr Dubovikov wrote:
I have added this functionality to webHomer too. Please check and let us know.
It works fine, thank you.
I do have some questions about the SQL schema and sipcapture module:
- Any reason why you have the schema almost duplicated in both examples/partrotate_unixtimestamp.pl and sql/create_sipcapture.sql. I used the latter as the example for the pgsql version, but noticed today that you commited to the other file.
- Why are source_ip and destination_ip VARCHAR(50) while contact_ip and originator_ip are VARCHAR(60)?
- As 'id' should be autoincremented and thus unique, why is the primary key (id,date)? Should be sufficient with just id.
- Regarding 'id', at least when used together with pgsql, it is always 0. From sipcapture.c::sip_capture_store:
db_keys[0] = &id_column; db_vals[0].type = DB1_INT; db_vals[0].nul = 0; db_vals[0].val.int_val = 0;
This seems wrong as it forces 'id' to always be 0. Easy fix was to skip adding a value to the id column. See attached patch.
- I got errors from pgsql due to rows with NULL in the 'diversion' field, as it is defined to be NOT NULL. Had to permit NULL.
The following pgsql schema is now running on my test rig:
CREATE TABLE sip_capture ( id SERIAL NOT NULL, date TIMESTAMP WITHOUT TIME ZONE DEFAULT '1900-01-01 00:00:01' NOT NULL, micro_ts BIGINT 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), /* MySQL: NOT NULL */ reason VARCHAR(200) NOT NULL, content_type VARCHAR(256) NOT NULL, auth VARCHAR(256) NOT NULL, user_agent VARCHAR(256) NOT NULL, source_ip VARCHAR(50) NOT NULL DEFAULT '', source_port INTEGER NOT NULL, destination_ip VARCHAR(50) NOT NULL DEFAULT '', destination_port INTEGER NOT NULL, contact_ip VARCHAR(60) NOT NULL, contact_port INTEGER NOT NULL, originator_ip VARCHAR(60) NOT NULL DEFAULT '', originator_port INTEGER NOT NULL, proto INTEGER NOT NULL, family INTEGER NOT NULL, rtp_stat VARCHAR(256) NOT NULL, type INTEGER NOT NULL, node VARCHAR(125) NOT NULL, msg VARCHAR(1500) NOT NULL, PRIMARY KEY (id,date) );
CREATE INDEX sip_capture_ruri_user_idx ON sip_capture (ruri_user); CREATE INDEX sip_capture_from_user_idx ON sip_capture (from_user); CREATE INDEX sip_capture_to_user_idx ON sip_capture (to_user); CREATE INDEX sip_capture_pid_user_idx ON sip_capture (pid_user); CREATE INDEX sip_capture_auth_user_idx ON sip_capture (auth_user); CREATE INDEX sip_capture_callid_aleg_idx ON sip_capture (callid_aleg); CREATE INDEX sip_capture_date_idx ON sip_capture (date); CREATE INDEX sip_capture_callid_idx ON sip_capture (callid);
So far no partitioning is defined yet.
Ok, I will fix it and upload your PgSQL schema as a separate sql file.
primary key should be (id,date) for partitions. (date range)
Wbr, Alexandr
2013/1/22 Øyvind Kolbu oyvind.kolbu@usit.uio.no
On 2013-01-22 at 12:44, Alexandr Dubovikov wrote:
I have added this functionality to webHomer too. Please check and let us know.
It works fine, thank you.
I do have some questions about the SQL schema and sipcapture module:
Any reason why you have the schema almost duplicated in both examples/partrotate_unixtimestamp.pl and sql/create_sipcapture.sql. I used the latter as the example for the pgsql version, but noticed today that you commited to the other file.
Why are source_ip and destination_ip VARCHAR(50) while contact_ip and originator_ip are VARCHAR(60)?
As 'id' should be autoincremented and thus unique, why is the primary key (id,date)? Should be sufficient with just id.
Regarding 'id', at least when used together with pgsql, it is always 0. From sipcapture.c::sip_capture_store:
db_keys[0] = &id_column; db_vals[0].type = DB1_INT; db_vals[0].nul = 0; db_vals[0].val.int_val = 0;
This seems wrong as it forces 'id' to always be 0. Easy fix was to skip adding a value to the id column. See attached patch.
I got errors from pgsql due to rows with NULL in the 'diversion' field, as it is defined to be NOT NULL. Had to permit NULL.
The following pgsql schema is now running on my test rig:
CREATE TABLE sip_capture ( id SERIAL NOT NULL, date TIMESTAMP WITHOUT TIME ZONE DEFAULT '1900-01-01 00:00:01' NOTNULL, micro_ts BIGINT 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), /* MySQL: NOT NULL */ reason VARCHAR(200) NOT NULL, content_type VARCHAR(256) NOT NULL, auth VARCHAR(256) NOT NULL, user_agent VARCHAR(256) NOT NULL, source_ip VARCHAR(50) NOT NULL DEFAULT '', source_port INTEGER NOT NULL, destination_ip VARCHAR(50) NOT NULL DEFAULT '', destination_port INTEGER NOT NULL, contact_ip VARCHAR(60) NOT NULL, contact_port INTEGER NOT NULL, originator_ip VARCHAR(60) NOT NULL DEFAULT '', originator_port INTEGER NOT NULL, proto INTEGER NOT NULL, family INTEGER NOT NULL, rtp_stat VARCHAR(256) NOT NULL, type INTEGER NOT NULL, node VARCHAR(125) NOT NULL, msg VARCHAR(1500) NOT NULL, PRIMARY KEY (id,date) );
CREATE INDEX sip_capture_ruri_user_idx ON sip_capture (ruri_user); CREATE INDEX sip_capture_from_user_idx ON sip_capture (from_user); CREATE INDEX sip_capture_to_user_idx ON sip_capture (to_user); CREATE INDEX sip_capture_pid_user_idx ON sip_capture (pid_user); CREATE INDEX sip_capture_auth_user_idx ON sip_capture (auth_user); CREATE INDEX sip_capture_callid_aleg_idx ON sip_capture (callid_aleg); CREATE INDEX sip_capture_date_idx ON sip_capture (date); CREATE INDEX sip_capture_callid_idx ON sip_capture (callid);So far no partitioning is defined yet.
-- Øyvind Kolbu
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-
Alexandr Dubovikov -
Øyvind Kolbu