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: