git:master: modules:sipcapture Changed authorization column to "auth". Now PostgreSQL should be happy.
22 Jan
2013
22 Jan
'13
11:44 a.m.
Module: sip-router
Branch: master
Commit: 58417e5f23c50a1892584d368e49373c579af31e
URL:
http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=58417e5…
Author: Alexandr Dubovikov <alexandr.dubovikov(a)gmail.com>
Committer: Alexandr Dubovikov <alexandr.dubovikov(a)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");
22 Jan
22 Jan
12:29 p.m.
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=58417e5…
Author: Alexandr Dubovikov<alexandr.dubovikov(a)gmail.com>
Committer: Alexandr Dubovikov<alexandr.dubovikov(a)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
12:44 p.m.
I have added this functionality to webHomer too. Please check and let us
know.
Thanks,
2013/1/22 Øyvind Kolbu <oyvind.kolbu(a)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**3c579af31e<http://git.sip-rou…
Author: Alexandr
Dubovikov<alexandr.dubovikov@**gmail.com<alexandr.dubovikov@gmail.com>
Committer: Alexandr
Dubovikov<alexandr.dubovikov@**gmail.com<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.
Thank you.
--
Øyvind
______________________________**_________________
sr-dev mailing list
sr-dev(a)lists.sip-router.org
http://lists.sip-router.org/**cgi-bin/mailman/listinfo/sr-**dev<http://l…
2:58 p.m.
New subject: git:master: modules:sipcapture Changed authorization column to "auth". Now PostgreSQL should be happy.
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.
--
Øyvind Kolbu
3:18 p.m.
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(a)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' 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.
--
Øyvind Kolbu
_______________________________________________
sr-dev mailing list
sr-dev(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
4134
days inactive
4134
days old
4 comments
2 participants
participants (2)
-
Alexandr Dubovikov -
Øyvind Kolbu