Table of Contents
db_url (string)usr_table (string)db_scheme (string)use_domain (integer)ps_id_max_buf_len (integer)bigint_to_str (int)uuid_column (string)username_column (string)domain_column (string)attribute_column (string)value_column (string)type_column (string)sql_query(query, [res_col_avps], [db_id])
sql_query_one(query, [res_col_vars], [db_id])
sql_select([columns],table,[filter],[order],[res_col_avps], [db_id])
sql_select_one([columns],table,[filter],[order],[res_col_vars], [db_id])
sql_update(columns,table,[filter],[db_id])
sql_insert(table,columns,[db_id])
sql_delete(table,[filter],[db_id])
sql_replace(table,columns,[db_id])
sql_avp_load(source, name, [db_id], [prefix]])
sql_avp_store(source, name, [db_id])
sql_avp_delete(source, name, [db_id])
List of Tables
List of Examples
db_url parameterusr_table parameterdb_scheme parameter
use_domain parameter
ps_id_max_buf_len parameter
bigint_to_str parameter
uuid_column parameterusername_column parameterdomain_column parameterattribute_column parameter
value_column parameter
type_column parameter
sql_query usagesql_query_one usagesql_select usagesql_select_one usagesql_update usagesql_insert usagesql_delete usagesql_avp_load usagesql_avp_store usagesql_avp_delete usageasync sql_query usageasync sql_query_one usageSQLops (SQL-operations) modules implements a set of script functions for generic SQL standard queries (raw or structure queries). It also provides a dedicated set of functions for DB manipulation (loading/storing/removing) of user AVPs (preferences).
DB URL for database connection. As the module allows the usage of multiple DBs (DB URLs), the actual DB URL may be preceded by an reference number. This reference number is to be passed to AVPOPS function that what to explicitly use this DB connection. If no reference number is given, 0 is assumed - this is the default DB URL.
This parameter is optional, it's default value being NULL.
Example 1.1. Set db_url parameter
...
# default URL
modparam("sqlops","db_url","mysql://user:passwd@host/database")
# an additional DB URL
modparam("sqlops","db_url","1 postgres://user:passwd@host2/opensips")
...
DB table to be used for user preferences (AVPs)
This parameter is optional, it's default value being “usr_preferences”.
Definition of a DB scheme to be used for accessing a non-standard User Preference -like table.
Definition of a DB scheme. Scheme syntax is:
db_scheme = name':'element[';'element]*
element =
'uuid_col='string
'username_col='string
'domain_col='string
'value_col='string
'value_type='('integer'|'string')
'table='string
Default value is “NULL”.
Example 1.3. Set db_scheme parameter
...
modparam("sqlops","db_scheme",
"scheme1:table=subscriber;uuid_col=uuid;value_col=first_name")
...
If the domain part of the a SIP URI should be used for identifying an AVP in DB operations.
Default value is 0 (no).
The maximum size of the buffer used to build the query IDs which are used for managing the Prepare Statements when comes to the "sql_select|update|insert|replace|delete()" functions
If the size is exceeded (when trying to build the PS query ID), the PS support will be dropped for the query. If set to 0, the PS support will be completly disabled.
Default value is 1024.
Controls bigint conversion. By default bigint values are returned as int. If the value stored in bigint is out of the int range, by enabling bigint to string conversion, the bigint value will be returned as string.
Default value is “0”.
Example 1.6. Set bigint_to_str parameter
...
# Return bigint as string
modparam("sqlops","bigint_to_str",1)
...
Name of column containing the uuid (unique user id).
Default value is “uuid”.
Name of column containing the username.
Default value is “username”.
Name of column containing the domain name.
Default value is “domain”.
Name of column containing the attribute name (AVP name).
Default value is “attribute”.
Example 1.10. Set attribute_column parameter
...
modparam("sqlops","attribute_column","attribute")
...
Name of column containing the AVP value.
Default value is “value”.
Make a database query and store the result in AVPs.
The meaning and usage of the parameters:
query (string) - must be a valid SQL query. The parameter can contain pseudo-variables.
You must escape any pseudo-variables manually to prevent SQL injection attacks. You can use the existing transformations escape.common and unescape.common to escape and unescape the content of any pseudo-variable. Failing to escape the variables used in the query makes you vulnerable to SQL injection, e.g. make it possible for an outside attacker to alter your database content. The function returns true if the query was successful, -2 in case the query returned an empty result set, and -1 for all other types of errors.
res_col_avps (string, optional, no expand) - a list with AVP names where to store the result. The format is “$avp(name1);$avp(name2);...”. If this parameter is omitted, the result is stored in “$avp(1);$avp(2);...”. If the result consists of multiple rows, then multiple AVPs with corresponding names will be added. The value type of the AVP (string or integer) will be derived from the type of the columns. If the value in the database is NULL, the returned avp will be a string with the <null> value.
db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the “db_url” module parameter. It can be either a constant, or a string/int variable.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
Example 1.13. sql_query usage
...
sql_query("SELECT password, ha1 FROM subscriber WHERE username='$tu'",
"$avp(pass);$avp(hash)");
sql_query("DELETE FROM subscriber");
sql_query("DELETE FROM subscriber", , 2);
$avp(id) = 2;
sql_query("DELETE FROM subscriber", , $avp(id));
...
Similar to sql_query(), it makes a generic raw database query and returns the results, but with the following differences:
returns only one row - even if the query results in a multi row result, only the first row will be returned to script.
return variables are not limited to AVPs - the variables for returning the query result may any kind of variable, of course, as time as it is writeable. NOTE that the number of return vairable MUST match (as number) the number of returned columns. If less variables are provided, the query will fail.
NULL is returned - any a DB NULL value resulting from the query will be pushed as NULL indicator (and NOT as <null> string) to the script variables.
This function can be used from any type of route.
Example 1.14. sql_query_one usage
...
sql_query_one("SELECT password, ha1 FROM subscriber WHERE username='$tU'",
"$var(pass);$var(hash)");
# $var(pass) or $var(hash) may be NULL if the corresponding columns
# are not populated
...
sql_query_one("SELECT value, type FROM usr_preferences WHERE username='$fU' and attribute='cfna'",
"$var(cf_uri);$var(type)");
# the above query will return only one row, even if there are multiple `cfna`
# attributes for the user
...
Function to perform a structured (not raw) SQL SELECT operation. The query is performed via OpenSIPS internal SQL interface, taking advantages of the prepared-statements support (if the db backend provides something like that). The selected columns are returned into a set of AVPs (one to one matching the selected columns).
If using varibales in constructing the query, you must manually escape their values in order to prevent SQL injection attacks. You can use the existing transformations escape.common and unescape.common to escape and unescape the content of any pseudo-variable. Failing to escape the variables used in the query makes you vulnerable to SQL injection, e.g. make it possible for an outside attacker to alter your database content.
The function returns true if the query was successful, -2 in case the query returned an empty result set, and -1 for all other types of errors.
The meaning and usage of the parameters:
columns (string,optional) - JSON formated string holding an array of columns to be returned by the select. Ex: “["col1","col2"]”. If missing, a “*” (all columns) select will be performed.
table (string, mandatory) - the name of the table to be queried.
filter (string, optional) - JSON formated string holding the "where" filter of the query. This must be an array of (column, operator,value) pairs. The exact JSON syntax of such a pair is “{"column":{"operator":"value"}}”.; operators may be `>`, `<`, `=`, `!=` or custom string; The values may be string, integer or `null`. To simplify the usage with the `=` operator, you can use “{"column":"value"}” If missing, all rows will be selected.
order (string, optional) - the name of the column to oder by (only ascending).
res_col_avps (string, optional, no expand) - a list with AVP names where to store the result. The format is “$avp(name1);$avp(name2);...”. If this parameter is omitted, the result is stored in “$avp(1);$avp(2);...”. If the result consists of multiple rows, then multiple AVPs with corresponding names will be added. The value type of the AVP (string or integer) will be derived from the type of the columns. If the value in the database is NULL, the returned avp will be a string with the <null> value.
db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db_url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
Example 1.15. sql_select usage
...
sql_select('["password","ha1"]', 'subscriber',
'[ {"username": "$tu"}, {"domain": {"!=", null}}]', ,
'$avp(pass);$avp(hash)');
...
Similar to sql_select(), it makes a SELECT SQL query and returns the results, but with the following differences:
returns only one row - even if the query results in a multi row result, only the first row will be returned to script.
return variables are not limited to AVPs - the variables for returning the query result may any kind of variable, of course, as time as it is writeable. NOTE that the number of return vairable MUST match (as number) the number of returned columns. If less variables are provided, the query will fail.
NULL is returned - any a DB NULL value resulting from the query will be pushed as NULL indicator (and NOT as <null> string) to the script variables.
This function can be used from any type of route.
Example 1.16. sql_select_one usage
...
sql_select_one('["value","type"]', 'usr_preferences',
'[ {"username": "$tu"}, {"attribute": "cfna"}]', ,
'$var(cf_uri);$var(type)');
# the above query will return only one row, even if there are multiple `cfna`
# attributes for the user
...
Function to perform a structured (not raw) SQL UPDATE operation. IMPORTANT: please see all the general notes from the sql_select() function.
The function returns true if the query was successful.
The meaning and usage of the parameters:
columns (string,mandatory) - JSON formated string holding an array of (column,value) pairs to be updated by the query. Ex: “[{"col1":"val1"},{"col2":"val1"}]”.
table (string, mandatory) - the name of the table to be queried.
filter (string, optional) - JSON formated string holding the "where" filter of the query. This must be an array of (column, operator,value) pairs. The exact JSON syntax of such a pair is “{"column":{"operator":"value"}}”.; operators may be `>`, `<`, `=`, `!=` or custom string; The values may be string, integer or `null`. To simplify the usage with the `=` operator, you can use “{"column":"value"}” If missing, all rows will be updated.
db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db_url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
Example 1.17. sql_update usage
...
sql_update( '[{"password":"my_secret"}]', 'subscriber',
'[{"username": "$tu"}]');
...
Function to perform a structured (not raw) SQL INSERT operation. IMPORTANT: please see all the general notes from the sql_select() function.
The function returns true if the query was successful.
The meaning and usage of the parameters:
table (string, mandatory) - the name of the table to be queried.
columns (string,mandatory) - JSON formated string holding an array of (column,value) pairs to be inserted. Ex: “[{"col1":"val1"},{"col2":"val1"}]”.
db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db_url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
Example 1.18. sql_insert usage
...
sql_insert( 'cc_agents', '[{"agentid":"agentX"},{"skills":"info"},{"location":null},{"msrp_location":"sip:agentX@opensips.com"},{"msrp_max_sessions":2}]' );
...
Function to perform a structured (not raw) SQL DELETE operation. IMPORTANT: please see all the general notes from the sql_select() function.
The function returns true if the query was successful.
The meaning and usage of the parameters:
table (string, mandatory) - the name of the table to delete from.
filter (string, optional) - JSON formated string holding the "where" filter of the query. This must be an array of (column, operator,value) pairs. The exact JSON syntax of such a pair is “{"column":{"operator":"value"}}”.; operators may be `>`, `<`, `=`, `!=` or custom string; The values may be string, integer or `null`. To simplify the usage with the `=` operator, you can use “{"column":"value"}” If missing, all rows will be updated.
db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db_url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
Function very similar to sql_insert() function, but performing an SQL REPLACE operation instead. Note that not all SQL backend in OpenSIPS may support a REPLACE operation.
The function returns true if the query was successful.
Loads from DB into memory the AVPs corresponding to the given source. If given, it sets the script flags for loaded AVPs. It returns true if it loaded some values in AVPs, false otherwise (db error, no avp loaded ...).
AVPs may be preceded by an optional prefix, in order to avoid some conflicts.
Meaning of the parameters is as follows:
source (string, no expand) - what info is used for identifying the AVPs. Parameter syntax:
source = (pvar|str_value) ['/'('username'|'domain'|'uri'|'uuid')])
pvar = any pseudo variable defined in OpenSIPS. If the pvar is $ru (request uri), $fu (from uri), $tu (to uri) or $ou (original uri), then the implicit flag is 'uri'. Otherwise, the implicit flag is 'uuid'.
name (string, no expand) - which AVPs will be loaded from DB into memory. Parameter syntax is:
name = avp_spec['/'(table_name|'$'db_scheme)]
db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the “db_url” module parameter.
prefix (string, optional) - static string which will precede the names of the AVPs populated by this function.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
Example 1.20. sql_avp_load usage
...
sql_avp_load("$fu", "$avp(678)");
sql_avp_load("$ru/domain", "i/domain_preferences");
sql_avp_load("$avp(uuid)", "$avp(404fwd)/fwd_table");
sql_avp_load("$ru", "$avp(123)/$some_scheme");
# use DB URL id 3
sql_avp_load("$ru", "$avp(1)", 3);
# precede all loaded AVPs by the "caller_" prefix
sql_avp_load("$ru", "$avp(100)", , "caller_");
xlog("Loaded: $avp(caller_100)\n");
...
Stores to DB the AVPs corresponding to the given source.
The meaning and usage of the parameters are identical as for sql_avp_load(source, name) function. Please refer to its description.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
Example 1.21. sql_avp_store usage
...
sql_avp_store("$tu", "$avp(678)");
sql_avp_store("$ru/username", "$avp(email)");
# use DB URL id 3
sql_avp_store("$ru", "$avp(1)", 3);
...
Deletes from DB the AVPs corresponding to the given source.
The meaning and usage of the parameters are identical as for sql_avp_load(source, name) function. Please refer to its description.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
Example 1.22. sql_avp_delete usage
...
sql_avp_delete("$tu", "$avp(678)");
sql_avp_delete("$ru/username", "$avp(email)");
sql_avp_delete("$avp(uuid)", "$avp(404fwd)/fwd_table");
# use DB URL id 3
sql_avp_delete("$ru", "$avp(1)", 3);
...
This function takes the same parameters and behaves identically to sql_query(), but asynchronously (after launching the query, the current SIP worker pauses the execution of the current SIP message until the result is available and attempts to process more SIP traffic).
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
Example 1.23. async sql_query usage
...
{
...
/* Example of a slow MySQL query - it should take around 5 seconds */
async(
sql_query(
"SELECT table_name, table_version, SLEEP(0.1) from version",
"$avp(tb_name); $avp(tb_ver); $avp(retcode)"),
my_resume_route);
/* script execution is halted right after the async() call */
}
/* We will be called when data is ready - meanwhile, the worker is free */
route [my_resume_route]
{
xlog("Results: \n$(avp(tb_name)[*])\n
-------------------\n$(avp(tb_ver)[*])\n
-------------------\n$(avp(retcode)[*])\n");
}
...
This function takes the same parameters and behaves identically to sql_query_one(), but asynchronously (after launching the query, the current SIP worker pauses the execution of the current SIP message until the result is available and attempts to process more SIP traffic).
This function can be used from any route.
Example 1.24. async sql_query_one usage
...
{
...
/* Example of a slow MySQL query - it should take around 5 seconds */
async(
sql_query_one(
"SELECT table_name, table_version, SLEEP(0.1) from version",
"$var(tb_name); $var(tb_ver); $var(retcode)"),
my_resume_route);
/* script execution is halted right after the async() call */
}
/* We will be called when data is ready - meanwhile, the worker is free */
route [my_resume_route]
{
xlog("Result: $var(tb_name) | $var(tb_ver) | $(var(retcode)\n");
}
...
Table 2.1. Top contributors by DevScore(1), authored commits(2) and lines added/removed(3)
| Name | DevScore | Commits | Lines ++ | Lines -- | |
|---|---|---|---|---|---|
| 1. | Bogdan-Andrei Iancu (@bogdan-iancu) | 247 | 73 | 6018 | 7507 |
| 2. | Daniel-Constantin Mierla (@miconda) | 105 | 44 | 2927 | 2158 |
| 3. | Liviu Chircu (@liviuchircu) | 54 | 28 | 1116 | 948 |
| 4. | Elena-Ramona Modroiu | 52 | 11 | 4040 | 390 |
| 5. | Razvan Crainea (@razvancrainea) | 20 | 14 | 149 | 246 |
| 6. | Elena-Ramona Modroiu | 18 | 5 | 1051 | 192 |
| 7. | Henning Westerholt (@henningw) | 12 | 8 | 112 | 133 |
| 8. | Vlad Paiu (@vladpaiu) | 9 | 7 | 39 | 2 |
| 9. | Ionut Ionita (@ionutrazvanionita) | 8 | 5 | 180 | 12 |
| 10. | Kobi Eshun (@ekobi) | 7 | 1 | 300 | 146 |
All remaining contributors: Norman Brandinger (@NormB), Andrei Pelinescu-Onciul, Anca Vamanu, Maksym Sobolyev (@sobomax), Ovidiu Sas (@ovidiusas), Vlad Patrascu (@rvlad-patrascu), Klaus Darilion, John Burke (@john08burke), Andrey Vorobiev, Olle E. Johansson, Kennard White, Julián Moreno Patiño, Konstantin Bokarius, Walter Doekes (@wdoekes), Andreas Granig, Peter Lemenkov (@lemenkov), Sergio Gutierrez, Edson Gellert Schubert.
(1) DevScore = author_commits + author_lines_added / (project_lines_added / project_commits) + author_lines_deleted / (project_lines_deleted / project_commits)
(2) including any documentation-related commits, excluding merge commits. Regarding imported patches/code, we do our best to count the work on behalf of the proper owner, as per the "fix_authors" and "mod_renames" arrays in opensips/doc/build-contrib.sh. If you identify any patches/commits which do not get properly attributed to you, please submit a pull request which extends "fix_authors" and/or "mod_renames".
(3) ignoring whitespace edits, renamed files and auto-generated files
Table 2.2. Most recently active contributors(1) to this module
| Name | Commit Activity | |
|---|---|---|
| 1. | Norman Brandinger (@NormB) | Aug 2006 - May 2024 |
| 2. | Liviu Chircu (@liviuchircu) | Mar 2013 - May 2024 |
| 3. | Ovidiu Sas (@ovidiusas) | Jul 2015 - Apr 2024 |
| 4. | Bogdan-Andrei Iancu (@bogdan-iancu) | Jun 2005 - Mar 2024 |
| 5. | Vlad Paiu (@vladpaiu) | Jun 2011 - Jul 2023 |
| 6. | Razvan Crainea (@razvancrainea) | Jun 2011 - Mar 2023 |
| 7. | Maksym Sobolyev (@sobomax) | Oct 2022 - Feb 2023 |
| 8. | John Burke (@john08burke) | Jun 2022 - Jun 2022 |
| 9. | Vlad Patrascu (@rvlad-patrascu) | May 2017 - Jul 2019 |
| 10. | Peter Lemenkov (@lemenkov) | Jun 2018 - Jun 2018 |
All remaining contributors: Andrey Vorobiev, Julián Moreno Patiño, Ionut Ionita (@ionutrazvanionita), Walter Doekes (@wdoekes), Anca Vamanu, Kennard White, Sergio Gutierrez, Kobi Eshun (@ekobi), Henning Westerholt (@henningw), Olle E. Johansson, Daniel-Constantin Mierla (@miconda), Konstantin Bokarius, Edson Gellert Schubert, Elena-Ramona Modroiu, Klaus Darilion, Andreas Granig, Andrei Pelinescu-Onciul, Elena-Ramona Modroiu.
(1) including any documentation-related commits, excluding merge commits
Last edited by: Liviu Chircu (@liviuchircu), Ovidiu Sas (@ovidiusas), Bogdan-Andrei Iancu (@bogdan-iancu), Razvan Crainea (@razvancrainea), John Burke (@john08burke), Peter Lemenkov (@lemenkov), Ionut Ionita (@ionutrazvanionita), Vlad Paiu (@vladpaiu), Anca Vamanu, Norman Brandinger (@NormB), Kobi Eshun (@ekobi), Henning Westerholt (@henningw), Daniel-Constantin Mierla (@miconda), Konstantin Bokarius, Edson Gellert Schubert, Elena-Ramona Modroiu, Klaus Darilion, Andrei Pelinescu-Onciul, Elena-Ramona Modroiu.
Documentation Copyrights:
Copyright © 2009-2024 www.opensips-solutions.com
Copyright © 2004-2008 Voice Sistem SRL