Kamailio Advanced Training
Click here for more details!

Learn how to build RTC services with Kamailio!

Monday, December 8, 2008

SQLOPS Module

sqlops module focuses to offer fast and easy access to SQL backends directly from the configuration file. Among its features:
  • many DB connections - the module can connect to many databases on different servers using different DB driver modules (mysql, postgres, ...) at the same time.
  • many DB results - the module can store many results of different SQL queries in separate structures at the same time. Thus is possible to work in parallel with several DB results.
  • access via pseudo-variables - the content of SQL query result is accessible via pseudo-variables.
  • array indexes - fast access to result values via array possition: [row,column].
  • persistence in process space - a result can be used many times in same worker process. Query once, use many times.

Here is an example of fetching and printing the content of a table:

...
modparam("sqlops","sqlcon","ca=>mysql://openser:abc@localhost/openser")
...
sql_query("ca", "select * from domain", "ra");
xlog("rows: $dbr(ra=>rows) cols: $dbr(ra=>cols)\n");
if($dbr(ra=>rows)>0)
{
    $var(i) = 0;
    while($var(i)<$dbr(ra=>cols))
    {
        xlog("--- SCRIPT: column[$var(i)] = $dbr(ra=>colname[$var(i)])\n");
        $var(i) = $var(i) + 1;
    }
    $var(i) = 0;
    while($var(i)<$dbr(ra=>rows))
    {
        $var(j) = 0;
        while($var(j)<$dbr(ra=>cols))
        {
            xlog("[$var(i),$var(j)] = $dbr(ra=>[$var(i),$var(j)])\n");
            $var(j) = $var(j) + 1;
        }
        $var(i) = $var(i) + 1;
    }
}
sql_result_free("ra");
...


Other advantages than the ones listed above against old avp_db_query() function:

- does not mess up fields position if the value in db is null

- use private memory, no locking

- does not iterate through linked (avp) list to get each field, direct

reference to each data structure in result array, thus much faster.

More info at:

http://kamailio.org/docs/modules/devel/sqlops.html

No comments:

Post a Comment