How to route and bill from a database

There are many applications that you can define using Yate, but the most common will use a database to authenticate and route users from it.

Yate offers a module that does just that(authentication, registration, routing from database), and it's called register and his configuration file is register.conf.

This article will describe how to use register module to write CDRs, authenticate and 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.

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}' 

Bill from database

You need to have a table to keep the prefixes and one for the prices. Then you have to look into the billtime field and do the algorithm for taxing your users.

See also

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