[Kamailio-Devel] new module: sqlops - sql operations

[Daniel-Constantin Mierla]

Hello,

the devel version (to be released as 1.5.0 in Q1 2009) includes now a 
new module: sqlops.

It can be used to do raw sql queries in config file and play with the 
result data. Key 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 avp_db_query():
- 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

Testing and feedback is appreciated.

Cheers,
Daniel

-- 
Daniel-Constantin Mierla
http://www.asipto.com