How to route and bill from a database

Yate was built to easily allow building applications based on it. Most of the applications require the use of a database.

Yate offers a module that does authentication, registration, routing from database, and it's called register and his configuration file is register.conf. It also allows to write the cdr(call logs) in the database.

Depending on the level of complexity of your application you can use just some of the features of the register module and implement some of the functionality from another module or external script.

Enable messages

In register.conf file you have to activate handlers for each message you are going to use. That means that you have to enable parameters:

- user.auth=yes -> Activate handler on the user.auth message. Used to authenticate users.

- user.register=yes -> Activate handler on the user.register message. Used for users registration.

- call.route=yes -> Activate handler on the call.route message. Used to route the calls.

- call.cdr=yes -> Activate handler on the call.cdr message. Used to write CDRs in the database.

Configure database in Yate

There are 3 steps to accomplish this task:

1. create your database with tables that you need for your configuration (here is an example of a database schema).
2. put the credentials of the database of the database created in Yate's configuration file: pgsqldb.conf).
3. 'tell' Yate which connection to use when users will be registered. This is done in register.conf file.

Set the connection information of database

In PostgreSQL configuration file: pgsqldb.conf we have to set the connection data for this database in a section that will have the same name as the account settled in register.conf:

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

After "=" you have to write your connection information.

Set the name of the database connection

When using register module, set the name of the database connection in section [default]:

[default]
account=yateadmin

Define, authenticate, register Users

The users with their information will be kept in the database. You can see table users as an example of a table schema.

Then in register.conf you have to authenticate, register your users by enabling user.auth and user.register in [general] and writing their associated queries.

To authenticate and register users user.auth and user.register are enabled. register.conf

[general]
user.auth=yes
user.register=yes

[user.auth]
query=SELECT password FROM users WHERE username='${username}' 
result=password 

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

Route calls from database

See an example of a database schema.

In register.conf in section [general] the call.route and call.cdr must be enabled. They are used for routing the calls and writing the call logs in the database.

[general]
call.route=yes
call.cdr=yes
 
[call.route] 
query=SELECT location,(CASE WHEN location IS NULL THEN 'offline' ELSE NULL END) AS error FROM users WHERE username='${called}'
result=location
    
[call.cdr]
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 caller='${caller}',called='${called}',duration=INTERVAL '${duration} s',billtime=INTERVAL '${billtime} s',\
ringtime=INTERVAL '${ringtime} s',status='${status}',reason='${reason}' WHERE chan='${chan}' AND billid='${billid}'
 
cdr_finalize=UPDATE cdr SET 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 billid='${billid}'

For more complex routing like routing to registered lines(gateways) which are defined in the database, you can do a stored procedures.

Billing from database

There are two types of billing:

• postpaid billing - the billing can be done for a period of time after the calls ended. You can use the data stored in cdr table ('billtime' field) to make the algorithm for taxing your users. For this you may need custom parameters and you have to add them in cdrbuild.conf. You can read more on how to add custom parameters in CDR from routing in a dedicated article.

• prepaid billing - the billing must be calculated when the routing is done. You can create a stored procedure in which parameter 'timeout' that will keep the life time for the channel in it.

On 'cdr.finalize' you have to update the call duration. This can be done from an external script or from a stored procedure.

Custom module to do routing

If you don't want to use stored procedures you can build your own global external module to calculate the timeout.

See also

• Register module - authenticate, register and route users from database.
• PostgreSQL
• Routing