<div dir="ltr"><font face="arial, sans-serif">Ok, I will fix it and upload your PgSQL schema as a separate sql file.</font><div><font face="arial, sans-serif"><br></font></div><div style><font face="arial, sans-serif">primary key should be (id,date) for partitions. (date range)</font></div>
<div style><font face="arial, sans-serif"><br></font></div><div style><br></div><div style><font face="arial, sans-serif">Wbr,</font></div><div style><font face="arial, sans-serif">Alexandr</font></div><div style><font face="arial, sans-serif"><br>
</font></div><div style><font face="arial, sans-serif"><br></font></div><div style><font face="arial, sans-serif"><br></font></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/1/22 Øyvind Kolbu <span dir="ltr"><<a href="mailto:oyvind.kolbu@usit.uio.no" target="_blank">oyvind.kolbu@usit.uio.no</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">On 2013-01-22 at 12:44, Alexandr Dubovikov wrote:<br>
> I have added this functionality to webHomer too. Please check and let us<br>
> know.<br>
<br>
</div>It works fine, thank you.<br>
<br>
I do have some questions about the SQL schema and sipcapture module:<br>
<br>
- Any reason why you have the schema almost duplicated in both<br>
examples/<a href="http://partrotate_unixtimestamp.pl" target="_blank">partrotate_unixtimestamp.pl</a> and sql/create_sipcapture.sql.<br>
I used the latter as the example for the pgsql version, but<br>
noticed today that you commited to the other file.<br>
<br>
- Why are source_ip and destination_ip VARCHAR(50) while<br>
contact_ip and originator_ip are VARCHAR(60)?<br>
<br>
- As 'id' should be autoincremented and thus unique, why is the primary<br>
key (id,date)? Should be sufficient with just id.<br>
<br>
- Regarding 'id', at least when used together with pgsql, it is always 0.<br>
From sipcapture.c::sip_capture_store:<br>
<br>
db_keys[0] = &id_column;<br>
db_vals[0].type = DB1_INT;<br>
db_vals[0].nul = 0;<br>
db_vals[0].val.int_val = 0;<br>
<br>
This seems wrong as it forces 'id' to always be 0. Easy fix was to skip<br>
adding a value to the id column. See attached patch.<br>
<br>
- I got errors from pgsql due to rows with NULL in the 'diversion' field,<br>
as it is defined to be NOT NULL. Had to permit NULL.<br>
<br>
The following pgsql schema is now running on my test rig:<br>
<br>
CREATE TABLE sip_capture (<br>
id SERIAL NOT NULL,<br>
date TIMESTAMP WITHOUT TIME ZONE DEFAULT '1900-01-01 00:00:01' NOT NULL,<br>
micro_ts BIGINT NOT NULL DEFAULT '0',<br>
method VARCHAR(50) NOT NULL DEFAULT '',<br>
reply_reason VARCHAR(100) NOT NULL,<br>
ruri VARCHAR(200) NOT NULL DEFAULT '',<br>
ruri_user VARCHAR(100) NOT NULL DEFAULT '',<br>
from_user VARCHAR(100) NOT NULL DEFAULT '',<br>
from_tag VARCHAR(64) NOT NULL DEFAULT '',<br>
to_user VARCHAR(100) NOT NULL DEFAULT '',<br>
to_tag VARCHAR(64) NOT NULL,<br>
pid_user VARCHAR(100) NOT NULL DEFAULT '',<br>
contact_user VARCHAR(120) NOT NULL,<br>
auth_user VARCHAR(120) NOT NULL,<br>
callid VARCHAR(100) NOT NULL DEFAULT '',<br>
callid_aleg VARCHAR(100) NOT NULL DEFAULT '',<br>
via_1 VARCHAR(256) NOT NULL,<br>
via_1_branch VARCHAR(80) NOT NULL,<br>
cseq VARCHAR(25) NOT NULL,<br>
diversion VARCHAR(256), /* MySQL: NOT NULL */<br>
reason VARCHAR(200) NOT NULL,<br>
content_type VARCHAR(256) NOT NULL,<br>
auth VARCHAR(256) NOT NULL,<br>
user_agent VARCHAR(256) NOT NULL,<br>
source_ip VARCHAR(50) NOT NULL DEFAULT '',<br>
source_port INTEGER NOT NULL,<br>
destination_ip VARCHAR(50) NOT NULL DEFAULT '',<br>
destination_port INTEGER NOT NULL,<br>
contact_ip VARCHAR(60) NOT NULL,<br>
contact_port INTEGER NOT NULL,<br>
originator_ip VARCHAR(60) NOT NULL DEFAULT '',<br>
originator_port INTEGER NOT NULL,<br>
proto INTEGER NOT NULL,<br>
family INTEGER NOT NULL,<br>
rtp_stat VARCHAR(256) NOT NULL,<br>
type INTEGER NOT NULL,<br>
node VARCHAR(125) NOT NULL,<br>
msg VARCHAR(1500) NOT NULL,<br>
PRIMARY KEY (id,date)<br>
);<br>
<br>
CREATE INDEX sip_capture_ruri_user_idx ON sip_capture (ruri_user);<br>
CREATE INDEX sip_capture_from_user_idx ON sip_capture (from_user);<br>
CREATE INDEX sip_capture_to_user_idx ON sip_capture (to_user);<br>
CREATE INDEX sip_capture_pid_user_idx ON sip_capture (pid_user);<br>
CREATE INDEX sip_capture_auth_user_idx ON sip_capture (auth_user);<br>
CREATE INDEX sip_capture_callid_aleg_idx ON sip_capture (callid_aleg);<br>
CREATE INDEX sip_capture_date_idx ON sip_capture (date);<br>
CREATE INDEX sip_capture_callid_idx ON sip_capture (callid);<br>
<br>
<br>
So far no partitioning is defined yet.<br>
<span class="HOEnZb"><font color="#888888"><br>
<br>
--<br>
Øyvind Kolbu<br>
</font></span><br>_______________________________________________<br>
sr-dev mailing list<br>
<a href="mailto:sr-dev@lists.sip-router.org">sr-dev@lists.sip-router.org</a><br>
<a href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev" target="_blank">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev</a><br>
<br></blockquote></div><br></div>