Register

Latest revision as of 12:47, 11 January 2014

This module:

• is used for authentication, registration, and routing users stored in a database
• writes CDR(Call Detail Record) data in the database
• writes subscriptions in the database and make the initial notification

Note: You can setup the server params in the default section so you don't have to write them in every other section.
The default parameters can be overwritten by each section parameters.

[edit] Requirements

To use module register you need to choose a database module. Please see the requirements for the database module chosen.

The default queries are written in PostgreSQL. For MySQL you have to make small changes.

Create the account for connection to the database:

pgsqldb.conf

[yateadmin]
host=localhost
port=5432
database=yateadmin
user=postgres
password=secret

You will see how to set this account in register.conf at section 'default'. Here is an example of database schema.

[edit] Configuration

The file used is register.conf.

[edit] Section [general]

• [general] Global module settings that are read only at first initialization
  • expires: int: Will execute the engine.timer query only every that many seconds. Defaults to 30
  • stoperror: regexp: Regular expression matching errors that will stop fallback. Defaults to 'busy'

The following parameters enable handling of individual messages. Each must be enabled manually in this config file:

• user.auth: bool: Activate handler on the "user.auth" message. Defaults to no
• user.register: bool: Activate handler on the "user.register" message. Defaults to no
• user.unregister: bool: Activate handler on the "user.unregister" message. Defaults to no
• engine.timer: bool: Activate handler on the "engine.timer" message. Defaults to no
• call.preroute: bool: Activate handler on the "call.preroute" message. Defaults to no
• call.route: bool: Activate handler on the "call.route" message. Defaults to no
• call.cdr: bool: Activate handler on the "call.cdr" message. Defaults to no
• linetracker: bool: Activate line status tracking on "call.cdr". Defaults to no
• fallback: bool: Activate fallback routing handlers on the "chan.disconnected", "call.answered" and "chan.hangup" messages. Defaults to no
• accounts: bool: Activate client registrations on "user.notify" and "engine.timer". Defaults to no

[edit] Section [default]

• [default] This section holds default settings for each of the following message handlers. All these settings can be overriden in individual handler sections
  • priority: int: Priority in Yate's handlers chain. Defaults to 50
  • account: string: Name of the database connection to use. No default
  • account=yateadmin

In each of the following sections you have to specify:

- initial query to execute when module is initialized

- query to execute for each received message

- result field to copy in message's textual return value (only for some, mandatory for some)

You can also override the settings from section [default]

Sample queries below are for PostgreSQL. Timestamps and intervals are unfortunately non-portable.

[edit] Section [user.auth]

• [user.auth] Query and result name for the user.auth message, the result and its presence in the result set is mandatory starting with Rev. 3008
  • query=SELECT password FROM users WHERE username='${username}'
  • result=password

[edit] Section [user.register]

• [user.register] Query for the user.register message
  • query=UPDATE users SET location='${data}',expires=CURRENT_TIMESTAMP + INTERVAL '${expires} s' WHERE username='${username}'

[edit] Section [user.unregister]

• [user.unregister] Query for the user.unregister message
  • query=UPDATE users SET location=NULL,expires=NULL WHERE expires IS NOT NULL AND username='${username}'

[edit] Section [engine.timer]

• [engine.timer] Query for the timer message that expires registrations. Also look at the expires setting in section [general]
  • query=UPDATE users SET location=NULL,expires=NULL WHERE expires IS NOT NULL AND expires<=CURRENT_TIMESTAMP

[edit] Section [call.preroute]

• [call.preroute] Query and result name for the prerouting message
  • query=SELECT location FROM users WHERE username='${called}'
  • result=location
  • priority=120

[edit] Section [call.route]

• [call.route] Query and result name for the routing message
  • offlineauto: boolean: Set the "offline" error for queries that return records but no route. Defaults to yes
  • query=SELECT location,(CASE WHEN location IS NULL THEN 'offline' ELSE NULL END) AS error FROM users WHERE username='${called}'
  • result=location
  • priority=120
  • The authenticated user can be found in the '${username}' parameter, however if the user redirects the call '${diverter}' contains it.

[edit] Example of a stored procedure in PostgreSQL used for routing

Here you can use SQL stored function to route your calls and modify their preferences such as CallerID, Called Number, SS7 attributes, and so on.

Don't forget that you have to receive the set of records from SQL function in fully correspondence with structure of routing DB table (if you use it).

For PostgreSQL the function should be created with the following parameters, for example:

• CREATE OR REPLACE FUNCTION "<scheme>"."<function name>"(IN text, IN text, IN text) RETURNS SETOF "<scheme>"."<the table with routing DB>" AS $BODY$DECLARE

Than you have to call this function from register.conf like this:

• query=SELECT * FROM <scheme>.<function name>('${called}','${caller}','${address}')

You see that I pass Called Number, Caller Number and Address of Caller to my function, it's enough in order to make following functions:

• different route incoming and outgoing calls (from SS7 PSTN and to SS7 PSTN);
• control which Caller ID uses SIP client and change it to allowed from predefined range;
• control of Called PSTN numbers in order deny some directions;
• manipulate of Caller number within calls from PSTN to SIP client in order to remove geographic code ('022');
• limit users with some q-ty of simultaneously calls;
• route users to system messages like "User is temporary unavailable" (if registration is expired) and so on.

The function returns the following record (set of) in my scenario, for example to call from SIP client with number 801000 to PSTN number 561167 :

prefix: 5

location: sig/561167

descrip: Route to MoldTelecom-55

expires: 2031-01-24 13:00:01

trunk: link1

callednumtype: subscriber

callernumtype: national

screening: user-provided-passed

caller: 22801000

For [[regexroute.conf this route can be presented like this: ^5.....$=sig/\1;link=link1;callednumtype=subscriber;callernumtype=national;screening=user-provided-passed;caller=22${caller}

And the following for call from PSTN 561167 to SIP client 801201:

prefix: 801201

location: sip/sip:801201@111.111.111.111

descrip: Local SIP client

expires: 2031-01-24 13:00:01

trunk: [Null]

callednumtype: [Null]

callernumtype: [Null]

screening: [Null]

caller: 561167

Not all above columns are mandatory. 'Callednumtype', 'callernumtype' and 'screening' is necessary for SS7. 'Descrip' - only for our internal notes. Don't use 'id' column name even if your SQL allows it. Each attribute with it value should be in the separate column where the column name is attribute name. And you cannot use variables ('${caller}', '${called}', '\1') such as you do it regexroute.conf in the return from SQL, instead you need to return the final routing direction with all needed information.

[edit] Section [call.cdr]

• [call.cdr] Queries for the CDR updating message
  • critical: boolean: Reject all registrations and routing if query fails. Defaults to yes
  • initquery=UPDATE cdr SET ended=true WHERE ended IS NULL OR NOT ended
  • cdr_initialize=INSERT INTO cdr VALUES(TIMESTAMP 'EPOCH' + INTERVAL '${time} s','${chan}','${address}','${direction}','${billid}','${caller}','${called}',INTERVAL '${duration} s' ,INTERVAL '${billtime} s',INTERVAL '${ringtime} s','${status}','${reason}',false)
  • cdr_update=UPDATE cdr SET address='${address}',direction='${direction}',billid='${billid}', caller='${caller}',called='${called}',duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',ringtime=INTERVAL '${ringtime} s',status='${status}', reason='${reason}' WHERE chan='${chan}' AND time=TIMESTAMP 'EPOCH' + INTERVAL '${time} s'
  • cdr_finalize=UPDATE cdr SET address='${address}',direction='${direction}', billid='${billid}',caller='${caller}',called='${called}',duration=INTERVAL '${duration} s', billtime=INTERVAL '${billtime} s',ringtime=INTERVAL '${ringtime} s',status='${status}', reason='${reason}',ended=true WHERE chan='${chan}' AND time=TIMESTAMP 'EPOCH' + INTERVAL '${time} s'
  • cdr_combined=INSERT INTO cdr VALUES(TIMESTAMP 'EPOCH' + INTERVAL '${time} s','${chan}','${address}','${caller}','${called}',INTERVAL '${duration} s', INTERVAL '${billtime} s', INTERVAL '${ringtime} s','${status}','${reason}','${out_leg.chan}','${out_leg.address}', INTERVAL '${out_leg.billtime} s',INTERVAL '${out_leg.ringtime} s', INTERVAL '${out_leg.duration} s','${out_leg.reason}'

BE CAREFUL:

The INSERT/UPDATE queries shown above are only examples. In real world you need to adjust your queries accordingly to handle random order calls of cdr_initialize/cdr_update/cdr_finalize, meaning: For each call Yate at least runs one cdr_initialize and one cdr_finalize query. The cdr_update may not be called in case the call ends to fast, so it is: cdr_initialize, [cdr_update, cdr_update, ...] cdr_finalize.
Moreover it is NOT guaranteed, that the queries are executed in a specific order (cdr_finalize MAY be called BEFORE cdr_initialize) and it is not guaranteed, that there is a specific time delay between execution of one of these queries, especially when using connection pooling (delay between firing cdr_initialize/cdr_update/cdr_finalize can be below 1ms).

So at best use a SQL procedure that is capable of handling this by using UPSERT (first try UPDATE, then if no record was updated INSERT).
Also you can rely on columns billid and chan to be UNIQUE in combination (at best also add a specific Yate server column, so that you know which cdr record was issued by which server; billid+chan+server will give you a unique identification criterion). Then use this procedure for cdr_initialize, cdr_update and cdr_finalize. As said, the procedure should be capable of doing UPSERT (to handle INSERTs and UPDATES appropriately) and you should use a UNIQUE constraint on billid+chan(+server) to prevent getting double CDRs for the same call leg.
See this page for an example.

[edit] Section [linetracker]

• [linetracker] Queries for the line usage tracker. This counts the number of ongoing calls for a user.
  • critical: boolean: Reject all registrations and routing if query fails
  • critical=no
  • initquery=UPDATE users SET inuse=0
  • cdr_initialize=UPDATE users SET inuse=inuse+1 WHERE username='${external}'
  • cdr_finalize=UPDATE users SET inuse=(CASE WHEN inuse>0 THEN inuse-1 ELSE 0 END) WHERE username='${external}'

[edit] Section [accounts]

• [accounts] Queries for registering as client to other servers

• initquery Query executed only once to create client registrations at startup.

initquery=SELECT account,enabled,protocol,username,password,server FROM accounts WHERE enabled IS TRUE

• timerquery Poll on timer to update registrations if database was changed externally.

timerquery=BEGIN; SELECT account,enabled,protocol,username,password,server FROM accounts WHERE modified IS TRUE; UPDATE accounts SET modified=FALSE; COMMIT

• statusquery update account status after receiving an user.notify.

statusquery=UPDATE accounts SET status='${status}' WHERE account='${internalaccount}'

This is the same functionality as in accfile.conf. The only difference is that in register it is done from database and in accfile it is done from file accfile.conf.

[edit] Section [resource.subscribe]

• [resource.subscribe] Queries used for event subscribe and event state changes notification
  • subscribe_subscribe Query executed on subscribe requests to check authorization and refresh a subscription.
    subscribe_subscribe=SELECT * FROM subscriptions_subscribe('${notifier}', '${event}', '${subscriber}', '${data}', '${notifyto}', INTERVAL '${expires} s') as t(notifier text,data text,subscriber text,event text,notifyto text,notifyseq int8)
  • subscribe_unsubscribe Query executed on unsubscribe requests to check authorization and remove a subscription.
    subscribe_unsubscribe=SELECT * FROM subscriptions_unsubscribe('${notifier}','${event}', '${subscriber}')
  • subscribe_notify Query executed when needded to get all subscribers for a specified notifier's event.
    subscribe_notify=SELECT * FROM subscriptions_notify('${notifier}','${event}') as t(notifier text, data text,subscriber text,event text,notifyto text,notifyseq int8)
  • subscribe_expire Query used to expire subscriptions.
    subscribe_expire=SELECT * FROM subscriptions_expires() AS t(notifier text,data text,subscriber text,event text,notifyto text,notifyseq int8)

Example of database schema. Please note that the queries above were tested for PostGreSQL. For MySQL you would need to adapt them, especially the ones in [call.cdr] section.

As an example on how to use database to authenticate, register, route and bill your users take a look at this article: How to route and bill from a database.

See also

• Register Database Schema for PostgreSQL and Mysql database schema and MySQL CDR queries.
• CDR Build Module
• CDR File Module
• Routing