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