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 also allows to write the cdr(call logs) in the database.

This article will describe how to use register module to write CDRs, authenticate, register users, and route from database.

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] section and writing their associated queries.

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

In module used is register.conf, in section [default] the name of the database must be set:

[default]
account=yateadmin

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. Use call.cdr to write the CDRs in the database.

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

[general]
user.auth=yes
user.register=yes
call.route=yes
call.cdr=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}' 

[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.

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