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

[Øyvind Kolbu]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: no-id_column.diff
Type: text/x-diff
Size: 11070 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-dev/attachments/20130122/960ab248/attachment-0001.diff>