jump to navigation

CSQL JDBC and ODBC Client on Windows Platform June 24, 2011

Posted by Prabakaran Thirumalai in Uncategorized.
add a comment

CSQL 3.3 release provides jdbc and odbc client libraries to access CSQL database running on Linux /Solaris Platform. Through database engine is not ported to windows platform, CSQL windows client libraries enable application written in windows platform using C/C++ or Java to access the database.

You can download CSQL Windows Client(JDBC/ODBC) libraries at the below link

https://sourceforge.net/projects/csql/files/csql/csql3.3/wincsqlclientinstall.msi/download
After you install, copy dll files from C:\program Files\CSQLClient to C:\Windows\system32.  These libraries are built using MS VS2010 compiler, so it depends on msvcr100.dll.

 

CSQL Embedded Edition July 20, 2010

Posted by Prabakaran Thirumalai in csqlcache.
add a comment

Latest release of CSQL is available in embedded mode.   Embedded mode deployment of CSQL has small footprint and self managing capabilities. It provide organizations with an “install and forget” solution to a business data requirements.

Near zero administration, reliability of data are the import requirements for embedded databases.  This edition is useful for applications where data is available only within the process address space and are accessed by multiple threads from the same process. Advantage of CSQL over other leading embedded DBMS like SQLLite is its ability to provide row level locking and standard SQL, ODBC and JDBC interface.

Storing Asterisk Real Time SIP Users in CSQL Main Memory Database October 15, 2009

Posted by Prabakaran Thirumalai in csqlcache, asterisk.
Tags: , ,
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

http://www.csqldb.com

http://www.asterisk.org

Using CSQL for Asterisk func_odbc October 14, 2009

Posted by Prabakaran Thirumalai in csqlcache, asterisk.
Tags: , ,
add a comment

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, 8) exited non-zero on ‘SIP/praba-08b9d420′

Configuring CSQL for unixODBC October 12, 2009

Posted by Prabakaran Thirumalai in asterisk, csqlcache.
Tags: , , ,
2 comments

CSQL Installation

CSQL follows extract and use installation strategy. When CSQL needs to be accessed from any terminal without setting the necessary environment such as LD_LIBRARY_PATH, CSQL_CONFIG_FILE, etc, some extra settings are required. This blog provides step by step procedure for doing the same.

Download the latest CSQL Release from http://www.csqlcache.com

$tar zxvf csql2.7-linux-x86.tar.gz

Copy install directory to /usr/local

$cp -R csql2.7-linux-x86 /usr/local

Create soft link csql to point to the csql installation directory

$ln -s /usr/local/csql2.7-linux-x86 csql

Configuring CSQL

$cd /usr/local/csql

Add below line to sample/csql.conf file

DURABILITY=true

Copy the configuration file to /etc directory

$mkdir /etc/csql

$cp sample/csql.conf /etc/csql/csql.conf

Create file /etc/ld.so.conf.d/csql.conf with following content “/usr/local/csql/lib”

unixODBC Confguration for CSQL

Download and install unixODBC from http://www.unixodbc.org

Add the below lines to ~/.odbc.ini file

[mycsql]
Driver = /usr/local/csql/lib/libcsqlodbc.so

Starting CSQL Server

CSQL provides two scripts,

  • setupenv.ksh – set the necessary environment variables such as PATH, LD_LIBRARY_PATH, CSQL_INSTALL_ROOT, etc.
  • startup.sh – start csqlserver

Run the scripts as mentioned in the below sequence

$cd /usr/local/csql

$. ./setupenv.ksh

$scripts/startup.sh

Checking configuration and server status

Verify whether server started by checking /tmp/csql/log/csqlserver.log file

Connect with isql tool to check ODBC configuration

$isql mycsql root manager

If the above command gives you SQL prompt, then ODBC configuration for CSQL works fine

More Information on CSQL Configuration

http://www.wiki.csqldb.com/index.php/UserManual/CSQLInstallation

CSQL For Asterisk Real Time Queues October 10, 2009

Posted by Prabakaran Thirumalai in csqlcache, asterisk.
Tags: , , ,
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

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.asterisk.org

http://www.csqldb.com

[ERROR] Fatal error: Can’t open and lock privilege tables: Table
‘mysql.host’ doesn’t exist

Postpone hardware upgrade by employing CSQL Cache September 27, 2009

Posted by Prabakaran Thirumalai in csqlcache.
Tags: , , , , ,
add a comment

With the speed of business increasing, and the volume of information that enterprises must process growing as well, businesses in many industry domains transition to real time data management in order to stay competitive.

Though there is huge demand for speed, enterprises are reluctant to migrate their applications, as they do not want to give up the existing database systems they are using for many years that are proven stable in their environment. By employing caching for frequently accessed tables at the application tier, application can reduce load on the backend databases and reduce network calls, resulting in very high throughput.

Enterprises shall postpone hardware upgrade (more processors or more machines replicating data) for data management by 20 to 30 times by employing transparent caching for their existing database.

CSQL Cache is generic database caching platform to cache frequently accessed tables from your existing open source or commercial database management system (Oracle, DB2, Sybase, MySQL, Postgres, etc) close to application tier. It uses the fastest Main Memory Database (CSQL
MMDB) designed for high performance and high volume data computing to cache the table and enables real time applications to provide faster and predictive response time with high throughput.

One of the main advantage of CSQL over other caching mechanism is that the caching is transparent to the application and CSQL allows updates on the cached data which are automatically propagated to the actual database. It also allows application to cache partial records or partial fields from the actual table.

For More Information, visit

http://www.csqldb.com

CSQL Cache VS Object Caching Techniques August 15, 2009

Posted by Prabakaran Thirumalai in csqlcache.
Tags: ,
add a comment

Some of the shortcomings of object caching techniques are given below

1. Object Cache is suitable for read only workload

2. Serialization and De serialization operations are slow (especially in Java)

3. Grouping and Aggregation operations on set of related objects are very slow

4. Update on cache should be applied to database by application explicitly

5. Direct updates on database does not propagate to cache automatically

6. Every cache hit involves network overhead even if the cache resides in same machine (in case of memcached)

CSQL Cache provides transparent caching of complete or partial tables from database allowing applications to perform any SQL operations on the cached tables. It can also be used to store temporary data such as session information in MMDB allowing applications to scale.

For more information on CSQL Cache visit,

http://www.csqldb.com

Cache MySQL table to CSQL April 28, 2009

Posted by Prabakaran Thirumalai in csqlcache.
Tags: ,
add a comment

Extreme speed and capabilities of CSQL will be available to MySQL customers, allowing them to process their growing data volumes faster than ever before. In today’s quick-stepped business environment, the ability to access, capture, analyze data in real time is increasingly becoming the source of competitive advantage for many companies and must run with very low response time and high throughput.

Although MySQL provides memory based storage engine in open-source and enterprise level to improve the throughput and performance for applications, CSQL compliments and provides a formidable 20-30 times faster performance in accordance with all types of queries which returns single record in standard Wisconsin Benchmark.

CSQL’s utmost speed is a vital resource for clients in many industries, such as healthcare, telecommunications companies, government, ticketing and reservation service providers, web retailers and capital market firms which require instant and reliable business information. To provide a more predictive response time to applications such as web collaboration, on-line mobile phone charging and stock trading, data from MySQL can be cached in CSQL to support peak workloads.

For more information visit, http://www.csqldb.com

Accelerate Postgres with CSQL MMDB April 15, 2009

Posted by Prabakaran Thirumalai in cache, csqlcache.
add a comment

CSQL , main memory database engine provides transparent caching for Postgres databases with no or minimal application code changes. Main memory databases are times faster than disk based databases. By caching data close to the application using CSQL MMDB, it reduces the network latency and provides unprecendented performance for data access.

For more information visit

http://www.csqldb.com

Follow

Get every new post delivered to your Inbox.