kamailio/modules_k/sqlops/README

SQLOps Module

Daniel-Constantin Mierla

   asipto.com
   <miconda@gmail.com>

Edited by

Daniel-Constantin Mierla

   <miconda@gmail.com>

   Copyright © 2008 http://www.asipto.com
     __________________________________________________________________

   Table of Contents

   1. Admin Guide

        1. Overview
        2. Dependencies

              2.1. Kamailio Modules
              2.2. External Libraries or Applications

        3. Exported Parameters

              3.1. sqlcon (str)

        4. Exported Functions

              4.1. sql_query(connection, query, result)
              4.2. sql_result_free(result)

        5. Exported pseudo-variables

              5.1. $dbr(result=>key)

   List of Examples

   1.1. Set sqlcon parameter
   1.2. sql_query() usage
   1.3. sql_result_free() usage
   1.4. $dbr(result=>key) usage

Chapter 1. Admin Guide

   Table of Contents

   1. Overview
   2. Dependencies

        2.1. Kamailio Modules
        2.2. External Libraries or Applications

   3. Exported Parameters

        3.1. sqlcon (str)

   4. Exported Functions

        4.1. sql_query(connection, query, result)
        4.2. sql_result_free(result)

   5. Exported pseudo-variables

        5.1. $dbr(result=>key)

1. Overview

   The module adds support for raw SQL queries in the configuration file.

   Among the features:
     * Multiple DB connections - the module can connect to many databases
       on different servers using different DB driver modules 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 it is
       possible to work in parallel with several DB results.
     * access via pseudo-variables - the content of the SQL query result
       is accessible via pseudo-variables. Please note that only integer
       and string variables are supported at the moment because of the
       internal usage of "AVPs" to hold the values. So its not possible
       for example to return floating point or big integer values this
       way.
     * array indexes - fast access to result values via array position:
       [row,column].
     * persistence in process space - a result can be used many times in
       the same worker process. Query once, use many times.

2. Dependencies

   2.1. Kamailio Modules
   2.2. External Libraries or Applications

2.1. Kamailio Modules

   The following modules must be loaded before this module:
     * a DB SQL module (mysql, postgres, ...).

2.2. External Libraries or Applications

   The following libraries or applications must be installed before
   running Kamailio with this module loaded:
     * None.

3. Exported Parameters

   3.1. sqlcon (str)

3.1. sqlcon (str)

   The definition of a DB connection. The value of the parameter must have
   the following format:
     * "connection_name=>database_url"

   This parameter may be set multiple times to get many DB connections in
   the same configuration file.
     * connection_name - string specifying the name of a DB connection.
       This string is used by the "sql_query()" function to refer to the
       DB connection.
     * database_url - Standardized database URL used to connect to
       database.

   Default value is NULL.

   Example 1.1. Set sqlcon parameter
...
modparam("sqlops","sqlcon","cb=>mysql://openser:abc@10.10.1.1/testdb")
modparam("sqlops","sqlcon","ca=>dbdriver://username:password@dbhost/dbname")
...

4. Exported Functions

   4.1. sql_query(connection, query, result)
   4.2. sql_result_free(result)

4.1. sql_query(connection, query, result)

   Make a SQL query using 'connection' and store data in 'result'.
     * connection - the name of the connection to be used for query
       (defined via the "sqlcon" parameter).
     * query - SQL query string or pseudo-variables containing SQL query.
     * result - string name to identify the result. Will be used by
       $dbr(...) pseudo-variable to access result attributes.

   This function can be used from REQUEST_ROUTE, FAILURE_ROUTE,
   ONREPLY_ROUTE, BRANCH_ROUTE.

   Example 1.2. sql_query() usage
...
modparam("sqlops","sqlcon","ca=>dbdriver://username:password@dbhost/dbname")
...
sql_query("ca", "select * from domain", "ra");
xlog("number of rows in table domain: $dbr(ra=>rows)\n");
sql_result_free("ra");
...

4.2. sql_result_free(result)

   Free data in SQL 'result'.

   This function can be used from REQUEST_ROUTE, FAILURE_ROUTE,
   ONREPLY_ROUTE, BRANCH_ROUTE.

   Example 1.3. sql_result_free() usage
...
modparam("sqlops","sqlcon","ca=>dbdriver://username:password@dbhost/dbname")
...
sql_query("ca", "select * from domain", "ra");
xlog("number of rows in table domain: $dbr(ra=>rows)\n");
...
sql_result_free("ra");
...

5. Exported pseudo-variables

   5.1. $dbr(result=>key)

5.1. $dbr(result=>key)

   Access hash table entries.

   The "result" must be the name identifying a SQL result (third parameter
   of sql_query(...)).

   The "key" can be:
     * rows - return the number of rows in query result
     * cols - return the number of columns in the result.
     * [row,col] - return the value at position (row,col) in the result
       set. 'row' and 'col' must be integer or pseudo-variable holding an
       integer.
     * colname[N] - return the name of the N-th column in the result set.

   Example 1.4. $dbr(result=>key) usage
...
modparam("sqlops","sqlcon","ca=>dbdriver://username:password@dbhost/dbname")
...
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");
...