The SQL support, reloaded

The SQL DB support may be considered one of the early first and long improved feature in the whole history of SER / OpenSER and OpenSIPS.

20+ years later, with OpenSIPS 3.5, the SQL support is keep improving, especially when comes to its usage from script level. This is a natural evolution dictated by the need of scripting more and more complex routing logics.

First, RIP AVPops

Initially, the AVPops was a game changer module, bringing (into SER at that time) the first concept of variables to the script – the AVP’s – , the ability to perform operations with variables and to load/store them into SQL DB. This was magnificent, considering that, at that time, the script had no concept of variables and operations with variables.

Now, the module served its purpose, the OpenSIPS script has now full blown support for various kind of variables and ways of operating with them. This makes like 60% of the AVPops module obsolete (most of the functionality provided by the module could be achieved by pure scripting now). The only remaining valuable part is the DB part, the ability to load/store AVPs from DB. And actually 95% of the people are using the AVPops module for the sole-purpose of the avp_db_query() function 🙂

Then, Welcome SQLops

With the upcoming 3.5 release, there was the decision to convert the AVPops module to an SQLops module – anyhow, the useful part of the old module was the DB side only. And of course, the idea was to bring in some more boost to the SQL functionality.

sql_query_one() function

The existing avp_db_query() was renamed as sql_query() and a new sql_query_one() was added. Why? In most of the cases, the queries you do from the script are targeting one-row results, and we wanted to offer an optimized version of querying for this scenario:

any variable may be used to store the query result, not only AVPs – this is possible as we no longer need to have multiple values for each queried column. So you can use $var(), $shv() or any kind of writable variable to fetch the query result.
no more “<null>” dummy holders in the query result – again, as we have a single row returned, we not care anymore about keeping all the AVPs in sync when comes to the number of values they have. So we do not need to set the dummy "<null>" as a replacement for NULL‘s returned from DB. Now, a NULL from the DB level will be returned as NULL to the storing variable.

...
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
...

Structured queries

As an alternative to the raw querying provided by sql_query() function, the SQLops module provides new functions to perform queries in a more structured way.

These functions differ as they directly map on top of the internal SQL DB API, so being more powerful and flexible.

The parameters are JSON encoded

This makes very easy to pass more complex data to the function, like list of columns or list of keys with operators and values. Even more, you can take advantage of the $json() script variable to prepare the parameters in an incremental way (like adding more conditions to a “where” filter step by step, in different parts of the script).

# columns to be retrieved
$var(cols) = '["agentid", "location"]';

# start an array of filters with `"skills"=test`
$json(filter) := '[{"skills":"test"}]';

# later in script add more filters
$json(filter[]) := '{"msrp_max_sessions":{">":2}}';

# run the query
sql_select(  $var(cols), "cc_agents", $json(filter), , "$avp(x),$avp(y)" );
xlog("Loaded: $(avp(x)[*]), $(avp(y)[*])\n");

As you can see, the new SQL functions expect to receive the JSON encoded input as a string, via whatever variable fits you. Nevertheless, you can take advantage on $json() in order to do an easier and cleaner preparation of the data.

All SQL backends are available

With the old SQL support (avp_db_query()), only raw queries were possible. And most of the SQL DB driver in OpenSIPS do not support raw queries, but only queries via the structured API.

Now, with the new SQL function, you can take advantage of any SQL DB backend OpenSIPS has to offer – so you can query dbtext, Oracle or unixodbc directly from your OpenSIPS script 🙂. Just set an additional “db_url” modparam to make use of your favorite SQL backend

Use prepared statements

As the new functions map over the internal SQL structured API, they can take now advantage of the prepared statements support. This is done totally transparent for you as a script writer. Of course, you need to be sure the SQL backend you use does support prepared statements (and only mysql does 🙂 ).

Advantages here ?

prepared statements queries are much much faster
you are protected against SQL injection attacks
you don’t need to bother with escaping

Conclusions

Using structured SQL queries instead of raw queries is not only cleaner and more flexible, but only more powerful in terms of performance.

Learn more about OpenSIPS 3.5 at OpenSIPS Summit 2024 in Valencia

The SQL support, reloaded

First, RIP AVPops