name mode size
..
doc 040000
Makefile 100644 333B
README 100644 8.37kB
sql_api.c 100644 14.98kB
sql_api.h 100644 3.31kB
sql_var.c 100644 6.69kB
sql_var.h 100644 1.15kB
sqlops.c 100644 7.2kB
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) 3.2. sqlres (str) 4. Exported Functions 4.1. sql_query(connection, query[, result]) 4.2. sql_xquery(connection, query, result) 4.3. sql_result_free(result) 5. Exported pseudo-variables 5.1. $dbr(result=>key) List of Examples 1.1. Set sqlcon parameter 1.2. Set sqlres parameter 1.3. sql_query() usage 1.4. sql_xquery() usage 1.5. sql_result_free() usage 1.6. $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) 3.2. sqlres (str) 4. Exported Functions 4.1. sql_query(connection, query[, result]) 4.2. sql_xquery(connection, query, result) 4.3. 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 it is 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. * alternatively, results can be stored in xavps - columns are accessed by their names, rows by xavp index. Xavp's are available during the transactions lifetime and don't need to be destroyed manually. 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.2. sqlres (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") ... 3.2. sqlres (str) The definition of a DB result ID. The value of the parameter can be any string. Results IDs are also added at fixup time when sql_query() parameters are parsed, there is no need to decalare them via module parameter unless you want to use them from within other modules and be available in all application processes. Default value is NULL. Example 1.2. Set sqlres parameter ... modparam("sqlops", "sqlres", "ra") ... 4. Exported Functions 4.1. sql_query(connection, query[, result]) 4.2. sql_xquery(connection, query, result) 4.3. 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. If omitted, any resultset will be discarded. The result parameter should normally only be omitted when no result is expected (INSERT, UPDATE, DELETE). This function can be used from ANY_ROUTE. Example 1.3. 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_xquery(connection, query, result) Make a SQL query using 'connection' and store data in 'result' xavp. * 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 xavp. Each row will be added to this xavp, each column can be accessed by its name. This function can be used from ANY_ROUTE. Example 1.4. sql_xquery() usage ... modparam("sqlops","sqlcon","ca=>dbdriver://username:password@dbhost/dbname") ... sql_xquery("ca", "select * from domain", "ra"); xlog("first domain: $xavp(ra=>domain) with id: $xavp(ra=>domain_id)\n"); ... 4.3. sql_result_free(result) Free data in SQL 'result'. This function can be used from ANY_ROUTE. Example 1.5. 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.6. $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"); ... ... if (sql_xquery("ca", "select * from domain", "ra") == 1) { # non-destructive iteration $var(i) = 0; while($xavp(ra[$var(i)]) != $null) { xlog("[id, domain] = [$xavp(ra[$var(i)]=>id), $xavp(ra[$var(i)]=>domain) ]\n"); $var(i) = $var(i) + 1; } # destructive iteration while($xavp(ra) != $null) { xlog("[id, domain] = [$xavp(ra=>id), $xavp(ra=>domain)]\n"); pv_unset("$xavp(ra)"); } } ...