Csqlcache Weblog

IVR applications developed on asterisk requires database operations such as data retrieval and modification. Asterisk provides func_odbc module for interacting with ODBC compliant database management systems such as Oracle, MySQL, CSQL, etc.

When the call volume increases, data retrieval becomes one of the major bottleneck in IVR applications.  Using main memory embedded DBMS such as CSQL improves the overall throughput as it provides 10 microseconds response time.  CSQL would be able to support 100,000 transactions /sec through JDBC/ODBC data access.

Add to “res_odbc.conf”

[mycsql]
enabled => yes
dsn => mycsql
username => root
password => manager
pre-connect => yes
pooling => yes

Add the below lines to “func_odbc.conf”

[PRESENCE]
dsn=mycsql
read=SELECT location FROM presence WHERE id=’${SQL_ESC(${ARG1})}’
write=UPDATE presence SET location=’${SQL_ESC(${VAL1})}’ WHERE id=’${SQL_ESC(${ARG1})}’

For Installation and Configuration of CSQL refer this blog.

Create below tables in CSQL using csql or isql tool

create table presence(id char(64), location char(64), primary key(id));

insert into presence values (‘333′, ‘office’);

insert into presence values (‘csqluser1′, ‘vacation’);

Add below to “extensions.conf”

exten = 333,1,Answer
exten = 333,n,Set(ODBC_PRESENCE(${EXTEN})=home)
exten = 333,n,Set(CURLOC=${ODBC_PRESENCE(${EXTEN})})
exten = 333,n,Set(CURLOC1=${ODBC_PRESENCE(csqluser1)})
exten = 333,n, NoOp(Current location of user ${EXTEN} is ${CURLOC}.)
exten = 333,n, NoOp(Current location of user csqluser1 is ${CURLOC1}.)
exten = 333,n,Hangup

Dial extension 333, asterisk should produce the following output

– Executing [333@mycontext:1] Answer(“SIP/praba-09b83bc0″, “”) in new stack
– Executing [333@mycontext:2] Set(“SIP/praba-09b83bc0″, “ODBC_PRESENCE(333)=home”) in new stack
– Executing [333@mycontext:3] Set(“SIP/praba-09b83bc0″, “CURLOC=home”) in new stack
– Executing [333@mycontext:4] Set(“SIP/praba-09b83bc0″, “CURLOC1=home”) in new stack
– Executing [333@mycontext:5] NoOp(“SIP/praba-09b83bc0″, “Current location of user 333 is home.”) in new stack
– Executing [333@mycontext:6] NoOp(“SIP/praba-09b83bc0″, “Current location of user csqluser1 is home.”) in new stack
– Executing [333@mycontext:7] Hangup(“SIP/praba-09b83bc0″, “”) in new stack
== Spawn extension (mycontext, 333, 7) exited non-zero on ‘SIP/praba-09b83bc0′

Retrieving Multiple rows from CSQL

Add below lines to “func_odbc.conf”

[MPRESENCE]
dsn=astmysql
read=SELECT location FROM multipresence WHERE id=’${SQL_ESC(${ARG1})}’
mode=multirow

Add below lines to “extensions.conf”

exten = 444,1,Answer
exten = 444,n,Set(RID=${ODBC_MPRESENCE(${EXTEN})})
exten = 444,n, NoOp(Number of ROWS is ${ODBCROWS}.)
exten = 444,n,Set(VALUE1=${ODBC_FETCH(${RID})})
exten = 444,n, NoOp(Current location of user csqluser is ${VALUE1}.)
exten = 444,n,Set(VALUE2=${ODBC_FETCH(${RID})})
exten = 444,n, NoOp(Current location of user csqluser is ${VALUE2}.)
exten = 444,n,Hangup

Create multipresence table in CSQL using csql or isql tool

create table multipresence(id char(64), location char(64));
insert into multipresence values (‘444′, ‘home’);
insert into multipresence values (‘444′, ‘office’);
insert into multipresence values (‘555′, ‘office’);

Dial extension 444, asterisk should produce the following output

== Using SIP RTP CoS mark 5
– Executing [444@mycontext:1] Answer(“SIP/praba-08b9d420″, “”) in new stack
– Executing [444@mycontext:2] Set(“SIP/praba-08b9d420″, “RID=1″) in new stack
– Executing [444@mycontext:3] NoOp(“SIP/praba-08b9d420″, “Number of ROWS is 2.”) in new stack
– Executing [444@mycontext:4] Set(“SIP/praba-08b9d420″, “VALUE1=home”) in new stack
– Executing [444@mycontext:5] NoOp(“SIP/praba-08b9d420″, “Current location of user csqluser is home.”) in new stack
– Executing [444@mycontext:6] Set(“SIP/praba-08b9d420″, “VALUE2=office”) in new stack
– Executing [444@mycontext:7] NoOp(“SIP/praba-08b9d420″, “Current location of user csqluser is office.”) in new stack
– Executing [444@mycontext:8] Hangup(“SIP/praba-08b9d420″, “”) in new stack
== Spawn extension (mycontext, 444, exited non-zero on ‘SIP/praba-08b9d420′