[Users] The usrloc table, Oracle, and Asterisk

[Bogdan-Andrei Iancu]

Hi Juan,

OpenSER and Asterisk is quite a hot topic and I see interesting and 
useful information on your email. You can refer to the wiki page :
    http://openser.org/dokuwiki/doku.php
to see or share info from/with other users.

now, regarding your question regarding "contact" and "received".

first of all, instead of using Oracle triggers you can log this info 
directly from OpenSER using  avp_db_query
       http://www.openser.org/docs/modules/1.2.x/avpops.html#AEN268
and pseudo-variables to get the src_ip ($si), src_port ($sp) and expire 
header. See
       http://www.openser.org/dokuwiki/doku.php/pseudovariables:devel

otherwise, using triggers, you need to look to the "contact" and 
"received" columns in location. "contact" contains the received contact 
and "received" the source addr of the REGISTER - used in nat scenarios, 
where contact has private IPs.
so,it depends of your OpenSER cfg, if received is or not populated (via 
fix_natted_registrar function). Anyhow, first you should try to use 
received and if empty, use contact. or, if no nat, just use contact.

regards,
bogdan

Juan Carlos Castro y Castro wrote:

> I'm working on an Asterisk+OpenSER setup and I am in the process of 
> implementing the following: registration has to be handled by OpenSER, 
> but it has to be recognized by Asterisk. For that purpose, OpenSER is 
> authenticating against Asterisk's "sipfriends" realtime table. That 
> works OK. But OpenSER should also update fields "ipaddr", "port", and 
> "regseconds" in the same table, or else Asterisk won't be able to 
> direct calls to the logged SIP phones.
>
> I'm thinking of implementing an Oracle trigger (or rather asking the 
> Oracle DBA to do that) so all updates/inserts into the location table 
> will cause updates in sipfriends. As far as I know, I need to take the 
> IP and port from the "contact" field of the location table. Or should 
> I use the "received" field? In my tests, the "received" field gets set 
> to NULL, although "contact" has the info I need. Can I use the data 
> from "contact" always?
>
> For the regseconds field, I'll just have to make a conversion from the 
> local time format (used by the "expires" field of location) to the 
> unix timestamp format (used by "regseconds" in Asterisk).
>
> Oh, by the way, this is the location table definition that worked OK 
> with Oracle. I had to ask for a login trigger so the date fields would 
> accept the 'YYYY-MM-DD HH:MM:SS' format. Also, Oracle refuses to 
> accept an empty string as a non-null value.
>
> CREATE TABLE location (
>  username varchar2(64) DEFAULT '',
>  domain varchar2(128) DEFAULT 'vono.net.br',
>  contact varchar2(255) DEFAULT '',
>  received varchar2(255) DEFAULT '',
>  path varchar2(255) DEFAULT '',
>  expires date DEFAULT '2020-05-28 21:32:15' NOT NULL,
>  q decimal(10,2) DEFAULT 1.0 NOT NULL,
>  callid varchar2(255) DEFAULT 'Default-Call-ID' NOT NULL,
>  cseq int DEFAULT 42 NOT NULL,
>  last_modified date DEFAULT '1900-01-01 00:00' NOT NULL,
>  flags int DEFAULT 0 NOT NULL,
>  user_agent varchar2(255) DEFAULT '',
>  socket varchar2(128) DEFAULT '',
>  methods int DEFAULT NULL,
>  PRIMARY KEY(username, domain, contact)
> );
>
>
>
> _______________________________________________
> Users mailing list
> Users at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/users
>