[sr-dev] git:master: modules:sipcapture Changed authorization column to "auth". Now PostgreSQL should be happy.

[Alexandr Dubovikov]

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 at 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 at lists.sip-router.org
> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20130122/2b0dab32/attachment.htm>