Asterisk CSQL Real Time Queue

CSQL For Asterisk Real Time Queues


Asterisk with dynamic real time 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. Asterisk can retrieve Queue information from CSQL in 20 microseconds instead of few seconds to minutes(in case of other disk based shared DBMS such as MySQL, Postgres, Oracle, etc). 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.

Create Asterisk Queue Tables in CSQL


Run the below SQL statements using csql tool or isql tool to create the necessary tables


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


Add below lines to “res_odbc.conf”


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


Add below lines to “sip.conf”

[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


Add below lines in “extensions.conf” for “mycontext”

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


http://www.csqldb.com

CSQL For Asterisk Real Time Queues


Asterisk with dynamic real time 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. Asterisk can retrieve Queue information from CSQL in 20 microseconds instead of few seconds to minutes(in case of other disk based shared DBMS such as MySQL, Postgres, Oracle, etc). 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.

Create Asterisk Queue Tables in CSQL


Run the below SQL statements using csql tool or isql tool to create the necessary tables


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


Add below lines to “res_odbc.conf”


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


Add below lines to “sip.conf”

[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


Add below lines in “extensions.conf” for “mycontext”

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


http://www.csqldb.com

Created by: praba_tuty, Last modification: Mon 12 of Oct, 2009 (17:45 UTC)
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+