Storing Asterisk Real Time SIP Users in CSQL Main Memory Database October 15, 2009
Posted by Prabakaran Thirumalai in asterisk, csqlcache.Tags: csql, csql cache, real time sip
add a comment
Asterisk uses sip.conf file to store the sip users and peers in the system. When developing application on asterisk, SIP users are addded and removed based dynamically. This requires restart of asterisk resulting in high down time. To avoid this, asterisk provides real time configuration where sip user information is stored in external database such as CSQL.
CSQL main memory database with its very small footprint and high throughput reduces the CPU load compared to general purpose database. This blog outlines the step by step procedure to set up CSQL for stroring asterisk real time SIP users.
For CSQL Installation and Configuration, refer this blog.
Create table in CSQL for storing SIP users using either csql or isql tool. You may skip some of the fields except regserver, ipaddress, port, regseconds which are not used in your sip.conf file. The column names in CSQL database table correspond to the option names in sip.conf.
CREATE TABLE sip_buddies ( id int auto_increment, name char(80) NOT NULL default ”, host char(31) NOT NULL default ”, nat char(5) NOT NULL default ‘no’, type char(10) NOT NULL default ‘friend’, accountcode char(20), amaflags char(13), call_limit smallint, callgroup char(10) , callerid char(80) , cancallforward char(3) default ‘yes’, canreinvite char(3) default ‘yes’, context char(80) , defaultip char(15) , dtmfmode char(7) , fromuser char(80) , fromdomain char(80) , insecure char(4) , language char(2) , mailbox char(50) , md5secret char(80) , deny char(95) , permit char(95) , mask char(95) , musiconhold char(100) , pickupgroup char(10) , qualify char(3) , regexten char(80) , restrictcid char(3) , rtptimeout char(3) , rtpholdtimeout char(3) , secret char(80) , setvar char(100) , disallow char(100) default ‘all’, allow char(100) default ‘ulaw’, fullcontact char(80) default ”, ipaddr char(15) default ”, port smallint default ‘0′, regserver char(100) , regseconds int default ‘0′, lastms int default ‘0′, username char(80) default ”, defaultuser char(80) default ”, subscribecontext char(80));
CREATE INDEX sip_buddies_indx on sip_buddies(name)unique;
INSERT INTO sip_buddies (id, name, callerid, context, canreinvite, insecure, type,host, secret, allow, nat) VALUES (‘1′,’500′,’500′,’mycontext’,'no’,'no’,'peer’,
‘dynamic’,'500′ ,’ulaw’,'no’);
Add below lines to extconfig.conf
sippeers => odbc,mycsql,sip_buddies
sipusers => odbc,mycsql,sip_buddies
Register with SIP user 500 and password 500. It will succeed. Verify the registration status in asterisk console.
IAX and H323 users can also be stored the same way in CSQL for reducing the processing time.
More Information
CSQL For Asterisk Real Time Queues October 10, 2009
Posted by Prabakaran Thirumalai in asterisk, csqlcache.Tags: asterisk queue, csql, csql cache, csql real time queue
add a comment
CSQL For Asterisk Real Time Queues
Asterisk with dynamic realtime queue enables managing multiple queues and moving agents from one queue to another based on the load of the queue without any downtime. Without this feature, asterisk requires explicit reload after changing queue configuration file.
The queue definition and member list will be reloaded each time a caller joins the queue. This mandates using a main memory embedded database like CSQL, which provides high throughput and predictive response time for storing this queue information. By running CSQL in the same asterisk host, it reduces the network overhead involved in query processing. Reduces latency time in picking calls from the queue and reduces CPU load on asterisk server allowing it to handle more calls.
CSQL can be used in two modes for storing asterisk queue information
- Standalone mode with durability mode turned on
- Bidirectional cache for mysql or postgres database(where queue information is stored)
CSQL Bidirectional caching, ensures that modification on mysql or postgres database automatically reflects in the cache and are available for asterisk to fetch. This blog covers using CSQL to store queue information in stand alone configuration.
Refer this blog to find information on CSQL installation and Configuration
Create Asterisk Queue Tables in CSQL
CREATE TABLE ast_queues (name CHAR (128) NOT NULL , musiconhold CHAR (128), announce CHAR (128), context CHAR (128), timeout INT , monitor_join TINYINT , monitor_format CHAR (128), queue_youarenext CHAR (128), queue_thereare CHAR (128), queue_callswaiting CHAR (128), queue_holdtime CHAR (128), queue_minutes CHAR (128), queue_seconds CHAR (128), queue_lessthan CHAR (128), queue_thankyou CHAR (128), queue_reporthold CHAR (128), announce_frequency INT , announce_round_seconds INT , announce_holdtime CHAR (128), retry INT , wrapuptime INT , maxlen INT , servicelevel INT , strategy CHAR (128), joinempty CHAR (128), leavewhenempty CHAR (128), eventmemberstatus TINYINT , eventwhencalled TINYINT , reportholdtime TINYINT , memberdelay INT , weight INT , timeoutrestart TINYINT , ringinuse TINYINT , setinterfacevar TINYINT );
CREATE INDEX ast_queues_idx1_Primary on ast_queues ( name ) HASH UNIQUE;
CREATE TABLE ast_queue_member (uniqueid INT NOT NULL AUTO_INCREMENT , membername CHAR (40), queue_name CHAR (128), interface CHAR (128), penalty INT , paused INT );
CREATE INDEX ast_queue_member_idx on ast_queue_member ( queue_name ) HASH ;
INSERT INTO ast_queues VALUES( ‘csqltest_queue’, ‘default’, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,1, NULL, NULL, ‘leastrecent’, ‘yes’, NULL, NULL, NULL, NULL, NULL, NULL, NULL,0, NULL);
INSERT INTO ast_queue_member VALUES(1, ‘rashmi’, ‘csqltest_queue’, ‘SIP/rashmi’,1,0);
Asterisk Configuration
Add below lines to “extconfig.conf”
queues => odbc, asteriskcsql, ast_queues
queue_members => odbc, asteriskcsql, ast_queue_member
[asteriskcsql]
enabled => yes
dsn => mycsql
username => root
password => manager
pre-connect => yes
pooling => yes
[csqluser1]
type = peer
host = dynamic
dtmfmode = rfc2833
username = csqluser1
secret = csql123
qualify = yes
canreinvite = no
reinvite = no
callerid = csqluser1
context = mycontext
insecure = no
[csqluser2]
type = peer
host = dynamic
dtmfmode = rfc2833
username = csqluser2
secret = csql123
qualify = yes
canreinvite = no
reinvite = no
callerid = csqluser2
context = mycontext
insecure = no
exten = 222,1,Queue(csqltest_queue)
exten = 222,n,Hangup
Register “csqluser1″ and “csqluser2″ sip users and call extension “222″ from “csqluser2″. Check asterisk log if there is any error.
More Information
‘mysql.host’ doesn’t exist
