[Users] The usrloc table, Oracle, and Asterisk

[Juan Carlos Castro y Castro]

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)
);