Login | Register

Documentation

Documentation -> Development Manual 3.5? -> SQL Database API

This page has been visited 319 times.

Pages for other versions: devel 3.5 3.4 Older versions: 3.3 3.2 3.1


SQL Database API

OpenSIPS exposes a SQL database API that the module developers can use for operating the most common SQL queries. Advantages here are :

  • writing back-end independent code, since the DB API is decoupled from the actual modules implementing the back-end specific code
  • the ability to expose SQL-like capabilities to back-ends who are not internally SQL ( eg. the db_flatstore modules operates directly with flat-text files, yet the developer can insert into the file as if he was inserting into a regular SQL database


db/db.h exposes most of the database related functions. At startup, the developer will have just the database URL where he needs to connect. By calling db_bind_mod , the OpenSIPS DB API will try to automatically locate the actual DB module that support that specific back-end, and will return all the needed functions for operating on the back-end.

/**                                        
 * \brief Bind database module functions                            
 *                                                                      
 * This function is special, it's only purpose is to call find_export function in
 * the core and find the addresses of all other database related functions. The
 * db_func_t callback given as parameter is updated with the found addresses.
 *                                                            
 * This function must be called before any other database API call!
 *                                                              
 * The database URL is of the form "mysql://username:password@host:port/database" or
 * "mysql" (database module name).
 * In the case of a database connection URL, this function looks only at the first
 * token (the database protocol). In the example above that would be "mysql":
 * \see db_func_t                                                            
 * \param mod database connection URL or a database module name
 * \param dbf database module callbacks to be further used                  
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */                                      
int db_bind_mod(const str* mod, db_func_t* dbf);

typedef struct db_func {
      unsigned int           cap;           /* Capability vector of the database transport */
      db_use_table_f         use_table;     /* Specify table name */
      db_init_f              init;          /* Initialize database connection */
      db_close_f             close;         /* Close database connection */
      db_query_f             query;         /* query a table */
      db_fetch_result_f      fetch_result;  /* fetch result */
      db_raw_query_f         raw_query;     /* Raw query - SQL */
      db_free_result_f       free_result;   /* Free a query result */
      db_insert_f            insert;        /* Insert into table */
      db_delete_f            delete;        /* Delete from table */
      db_update_f            update;        /* Update table */
      db_replace_f           replace;       /* Replace row in a table */
      db_last_inserted_id_f  last_inserted_id;  /* Retrieve the last inserted ID
                                                    in a table */
      db_insert_update_f insert_update;     /* Insert into table, update on duplicate key */
} db_func_t;

/* Example of usage below */
db_func_t sql_functions;
db_url = str_init("mysql://root:vlad@localhost/opensips");

if (db_bind_mod(db_url, &sql_functions) < 0){
      /* most likely the db_mysql modules was not loaded, or it was loaded after our module */
      LM_ERR("Unable to bind to a database driver\n");
      return -1;
}
 

After successfully binding to the module, the developer must also make sure that the URL provided from the script writer point of a back-end which also supports the capabilities that will be further used ( eg. when operating on a flat text file, the db_last_inserted_id_f function will not be populated, and thus if the C code calls that function, the module will crash ). This is done by using the DB_CAPABILITY macro :

/**
 * Returns true if all the capabilities in cpv are supported by module
 * represented by dbf, false otherwise
 */
#define DB_CAPABILITY(dbf, cpv) (((dbf).cap & (cpv)) == (cpv))

/**
 * Represents the capabilities that a database driver supports.
 */
typedef enum db_cap {
        DB_CAP_QUERY =     1 << 0,  /**< driver can perform queries                                     */
        DB_CAP_RAW_QUERY = 1 << 1,  /**< driver can perform raw queries                                 */
        DB_CAP_INSERT =    1 << 2,  /**< driver can insert data                                         */
        DB_CAP_DELETE =    1 << 3,  /**< driver can delete data                                         */
        DB_CAP_UPDATE =    1 << 4,  /**< driver can update data                                         */
        DB_CAP_REPLACE =   1 << 5,  /**< driver can replace (also known as INSERT OR UPDATE) data       */
        DB_CAP_FETCH   =   1 << 6,  /**< driver supports fetch result queries                           */
        DB_CAP_LAST_INSERTED_ID = 1 << 7,  /**< driver can return the ID of the last insert operation   */
        DB_CAP_INSERT_UPDATE = 1 << 8, /**< driver can insert data into database and update on duplicate */
        DB_CAP_MULTIPLE_INSERT = 1 << 9 /**< driver can insert multiple rows at once */
} db_cap_t;


/**
 * All database capabilities except raw_query, replace, insert_update and
 * last_inserted_id which should be checked separately when needed
 */
#define DB_CAP_ALL (DB_CAP_QUERY | DB_CAP_INSERT | DB_CAP_DELETE | DB_CAP_UPDATE)

/* Example of usage below */
if (!DB_CAPABILITY(sql_functions, DB_CAP_ALL)) {
      LM_CRIT("Database modules does not "
            "provide all functions needed by our module\n");
      return -1;
}


Now that we have binded to the needed module and also made sure it supports our needed capabilities, we can go ahead and connect to the back-end, by invoking the init function from the binded functions :

/**
 * \brief Initialize database connection and obtain the connection handle.
 *
 * This function initialize the database API and open a new database
 * connection. This function must be called after db_bind_mod but before any
 * other database API function is called.
 *
 * The function takes one parameter, the parameter must contain the database
 * connection URL. The URL is of the form
 * mysql://username:password\@host:port/database where:
 *
 * username: Username to use when logging into database (optional).
 * password: password if it was set (optional)
 * host:     Hosname or IP address of the host where database server lives (mandatory)
 * port:     Port number of the server if the port differs from default value (optional)
 * database: If the database server supports multiple databases, you must specify the
 * name of the database (optional).
 * \see bind_dbmod
 * \param _sqlurl database connection URL
 * \return returns a pointer to the db_con_t representing the connection if it was
 * successful, otherwise 0 is returned
 */
typedef db_con_t* (*db_init_f) (const str* _sqlurl);

/* Example of usage below */
static db_con_t* db_connection;

if ((db_connection = sql_functions.init(db_url)) == NULL) {
      LM_ERR("Failed to connect to the database \n");
      return -1;
}


Connection sharing between multiple processes does not work for the majority of back-end specific connectors ( eg. MySQL, Postgres, etc ). Due to this fact, the developers MUST make sure to create a sepparate database connection for each process that will eventually need one - in the context of Module development, the connections need to be opened in the child_init function.

The output of the init() function will be the handler to be further used for all database interactions. When the connection is not needed anymore, the close method should be called :

/**
 * \brief Close a database connection and free all memory used.
 *
 * The function closes previously open connection and frees all previously
 * allocated memory. The function db_close must be the very last function called.
 * \param _h db_con_t structure representing the database connection
 */
typedef void (*db_close_f) (db_con_t* _h);


Before running any queries on the back-end, common practice dictates that the used tables should be versioned, in order to ensure that the user is not running your code on top of an older database structure.
db/db.h exposes db_check_table_version for this purpose, that checks the version table in the default OpenSIPS database structure :

/*              
Parameters :
      dbf - the functions to be used for running the version query
      dbh - the connection to run the version query
      table - str containing the table name we want to check for version
      version - the version we expect to find
Returns :
      0 means table version was successfully validated, negative in case of error ( internal error or older version found )
 */
int db_check_table_version(db_func_t* dbf, db_con_t* dbh, const str* table, const unsigned int version);


Before running a query through the API, we need to choose the table that the query will run on :

/**
 * \brief Specify table name that will be used for subsequent operations.
 *
 * The function db_use_table takes a table name and stores it db_con_t structure.
 * All subsequent operations (insert, delete, update, query) are performed on
 * that table.
 * \param _h database connection handle
 * \param _t table name
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_use_table_f)(db_con_t* _h, const str * _t);


All queries must be preceded by a call to the use_table function. OpenSIPS internally does connection pooling - in case multiple module request connections to the same database, the connection will be shared between all those modules. Thus, in the context of a process, the same connection might be used by different modules - never assume a connection is dedicated to a single module.

For running a SELECT query, you should use the query function. Prototype is :

/**
 * \brief Query table for specified rows.
 *
 * This function implements the SELECT SQL directive.
 * If _k and _v parameters are NULL and _n is zero, you will get the whole table.
 *
 * if _c is NULL and _nc is zero, you will get all table columns in the result.
 * _r will point to a dynamically allocated structure, it is neccessary to call
 * db_free_result function once you are finished with the result.
 *
 * If _op is 0, equal (=) will be used for all key-value pairs comparisons.
 *
 * Strings in the result are not duplicated, they will be discarded if you call
 * db_free_result, make a copy yourself if you need to keep it after db_free_result.
 *
 * You must call db_free_result before you can call db_query again!
 * \see db_free_result
 *
 * \param _h database connection handle
 * \param _k array of column names that will be compared and their values must match
 * \param _op array of operators to be used with key-value pairs
 * \param _v array of values, columns specified in _k parameter must match these values
 * \param _c array of column names that you are interested in
 * \param _n number of key-value pairs to match in _k and _v parameters
 * \param _nc number of columns in _c parameter
 * \param _o order by statement for query
 * \param _r address of variable where pointer to the result will be stored
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_query_f) (const db_con_t* _h, const db_key_t* _k, const db_op_t* _op,
                                const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
                                const db_key_t _o, db_res_t** _r);


Upon a successful select query call, the developer will have to manipulate the output db_res_t in order to use the results of his select query. Below are the used structures for interpreting a query result set.

/**
 * This type represents a result returned by db_query function (see below). The
 * result can consist of zero or more rows (see db_row_t description).
 *
 * Note: A variable of type db_res_t returned by db_query function uses dynamicaly
 * allocated memory, don't forget to call db_free_result if you don't need the
 * variable anymore. You will encounter memory leaks if you fail to do this!
 *
 * In addition to zero or more rows, each db_res_t object contains also an array
 * of db_key_t objects. The objects represent keys (names of columns). *
 */
typedef struct db_res {
        struct {
                db_key_t* names;   /**< Column names                    */
                db_type_t* types;  /**< Column types                    */
                int n;             /**< Number of columns               */
        } col;
        struct db_row* rows;   /**< Rows                            */
        int n;                 /**< Number of rows in current fetch */
        int res_rows;          /**< Number of total rows in query   */
        int last_row;          /**< Last row                        */
} db_res_t;

/**
 * Structure holding the result of a query table function.
 * It represents one row in a database table. In other words, the row is an
 * array of db_val_t variables, where each db_val_t variable represents exactly
 * one cell in the table.
 */
typedef struct db_row {
        db_val_t* values;  /**< Columns in the row */
        int n;             /**< Number of columns in the row */
} db_row_t;

/**
 * This structure represents a value in the database. Several datatypes are
 * recognized and converted by the database API. These datatypes are automaticaly
 * recognized, converted from internal database representation and stored in the
 * variable of corresponding type.
 *
 * Module that want to use this values needs to copy them to another memory
 * location, because after the call to free_result there are not more available.
 *
 * If the structure holds a pointer to a string value that needs to be freed
 * because the module allocated new memory for it then the free flag must
 * be set to a non-zero value. A free flag of zero means that the string
 * data must be freed internally by the database driver.
 */
typedef struct {
        db_type_t type; /**< Type of the value                              */
        int nul;                /**< Means that the column in database has no value */
        int free;               /**< Means that the value should be freed */
        /** Column value structure that holds the actual data in a union.  */
        union {
                int           int_val;    /**< integer value              */
                long long     bigint_val; /**< big integer value          */
                double        double_val; /**< double value               */
                time_t        time_val;   /**< unix time_t value          */
                const char*   string_val; /**< zero terminated string     */
                str           str_val;    /**< str type string value      */
                str           blob_val;   /**< binary object data         */
                unsigned int  bitmap_val; /**< Bitmap data type           */
        } val;
} db_val_t;


Many macros are in place in order to help writing faster and easier to read code :

/* Macros below work on result sets ( db_res_t )
/** Return the column names */
#define RES_NAMES(re) ((re)->col.names)
/** Return the column types */
#define RES_TYPES(re) ((re)->col.types)
/** Return the number of columns */
#define RES_COL_N(re) ((re)->col.n)
/** Return the result rows */
#define RES_ROWS(re)  ((re)->rows)
/** Return the number of current result rows */
#define RES_ROW_N(re) ((re)->n)
/** Return the last row of the result */
#define RES_LAST_ROW(re)  ((re)->last_row)
/** Return the number of total result rows */
#define RES_NUM_ROWS(re) ((re)->res_rows)

/* Macros below work on rows */
/** Return the columns in the row */
#define ROW_VALUES(rw) ((rw)->values)
/** Return the number of colums */
#define ROW_N(rw)      ((rw)->n)

/* Macros below work on values */
/**
 * Use this macro if you need to set/get the type of the value.
 */
#define VAL_TYPE(dv)   ((dv)->type)
/**
 * Use this macro if you need to set/get the null flag. A non-zero flag means that
 * the corresponding cell in the database contains no data (a NULL value in MySQL
 * terminology).
 */
#define VAL_NULL(dv)   ((dv)->nul)
/**
 * Use this macro if you need to access the integer value in the db_val_t structure.
 */
#define VAL_INT(dv)    ((dv)->val.int_val)
/**
 * Use this macro if you need to access the str structure in the db_val_t structure.
 */
#define VAL_STR(dv)    ((dv)->val.str_val)


Find below an example of a full select query from start to finish :

/* we will work on 'mytable' table with just two columns, keyname and value.
The select query we will run is 'select value from mytable where keyname='abc';'
*/
db_key_t key;
db_val_t val;
db_key_t col;
db_res_t* db_res = NULL;
db_row_t * rows;
db_val_t * values;

#define KEY_COL "keyname"
#define VALUE_COL "value"
str key_column = str_init(KEY_COL);
str value_column = str_init(VALUE_COL);
str db_table = str_init("mytable");

val.type = DB_STR;
val.nul = 0;
val.val.str_val.s = "abc";
val.val.str_val.len = 3;

key = &key_column;
col = &value_column;

if (sql_functions.use_table(db_handle, &db_table) < 0) {
      LM_ERR("sql use_table failed\n");
      return -1;
}

if(sql_functions.query(db_handle, &key, NULL, &val, &col, 1, 1, NULL, &db_res) < 0) {
       LM_ERR("failed to query database\n");
       return -1;
}

nr_rows = RES_ROW_N(db_res);
rows = RES_ROWS(db_res);


if (nr_rows <= 0) {
      LM_DBG("no rows found\n");
      sql_functions.free_result(db_handle, db_res);
      return -1;
}

for (i=0;i<nr_rows;i++) {
      values = ROW_VALUES(rows + i);
      if (VAL_NULL(values)) {
            LM_WARN("Column value should not be null - skipping \n");
            continue;
      }

      LM_DBG("We have feteched %s\n",VAL_STRING(values));
      /* do further rows processing here */
}

sql_functions.free_result(db_handle, db_res);
return 0;
 


As can be seen from the example above, any successful call to the query SQL function must be followed by the freeing of the returned result set, which is done by calling the free_result API function :

/**
 * \brief Free a result allocated by db_query.
 *
 * This function frees all memory allocated previously in db_query. Its
 * neccessary to call this function on a db_res_t structure if you don't need the
 * structure anymore. You must call this function before you call db_query again!
 * \param _h database connection handle
 * \param _r pointer to db_res_t structure to destroy
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_free_result_f) (db_con_t* _h, db_res_t* _r);


Sometimes, especially when querying large tables, it is not desirable to fetch all the rows in one chunk, since that might lead to the filling of the OpenSIPS private memory.
For such scenarios, using the fetch_result API function is highly recommended

/**                                                              
 * \brief Fetch a number of rows from a result.                                
 *
 * The function fetches a number of rows from a database result. If the number
 * of wanted rows is zero, the function returns anything with a result of zero.
 * \param _h structure representing database connection
 * \param _r structure for the result
 * \param _n the number of rows that should be fetched            
 * \return returns 0 if everything is OK, otherwise returns value < 0        
 */
typedef int (*db_fetch_result_f) (const db_con_t* _h, db_res_t** _r, const int _n);


Find below a full example of using fetch_result :

        /* check if our used DB driver supports fetching a limited number of rows */
        if (DB_CAPABILITY(*dr_dbf, DB_CAP_FETCH)) {
                /* run our query as usual, but DO NOT provide a result set pointer ( last parameter 0 ) */
                if ( dr_dbf->query( db_hdl, 0, 0, 0, columns, 0, db_cols, 0, 0 ) < 0) {
                        LM_ERR("DB query failed\n");
                        goto error;
                }
                /* estimate how many rows we can fit into our current PKG memory */
                no_rows = estimate_available_rows( 4+32+15+4+32+4+128+4+32+4, db_cols);
                if (no_rows==0) no_rows = 10;
                /* try to fetch our rows */
                if(dr_dbf->fetch_result(db_hdl, &res, no_rows )<0) {
                        LM_ERR("Error fetching rows\n");
                        goto error;
                }
        } else {
                /* no fetching rows support - fallback to full rows loading */
                if ( dr_dbf->query(db_hdl,0,0,0,columns,0,db_cols,0,&res) < 0) {
                        LM_ERR("DB query failed\n");
                        goto error;
                }
        }

        do {
                for(i=0; i < RES_ROW_N(res); i++) {
                        row = RES_ROWS(res) + i;
                        /* start processing our loaded rows */
                }

                if (DB_CAPABILITY(*dr_dbf, DB_CAP_FETCH)) {
                        /* any more rows to fetch ? */
                        if(dr_dbf->fetch_result(db_hdl, &res, no_rows)<0) {
                                LM_ERR( "fetching rows (1)\n");
                                goto error;
                        }
                        /* success in fetching more rows - continue the loop */
                } else {
                        /* we were not supporting fetching rows in the first place, processed everything */
                        break;
                }
        } while(RES_ROW_N(res)>0);

        dr_dbf->free_result(db_hdl, res);
 


Inserting rows in a table can be done by calling the insert API function :

/**
 * \brief Insert a row into the specified table.
 *
 * This function implements INSERT SQL directive, you can insert one or more
 * rows in a table using this function.
 * \param _h database connection handle
 * \param _k array of keys (column names)
 * \param _v array of values for keys specified in _k parameter
 * \param _n number of keys-value pairs int _k and _v parameters
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_insert_f) (const db_con_t* _h, const db_key_t* _k,
                                const db_val_t* _v, const int _n);
 


Deleting rows from a table is accomplished by calling the delete API function :

/**
 * \brief Delete a row from the specified table.
 *
 * This function implements DELETE SQL directive, it is possible to delete one or
 * more rows from a table.
 * If _k is NULL and _v is NULL and _n is zero, all rows are deleted, the
 * resulting table will be empty.
 * If _o is NULL, the equal operator "=" will be used for the comparison.
 *
 * \param _h database connection handle
 * \param _k array of keys (column names) that will be matched
 * \param _o array of operators to be used with key-value pairs
 * \param _v array of values that the row must match to be deleted
 * \param _n number of keys-value parameters in _k and _v parameters
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_delete_f) (const db_con_t* _h, const db_key_t* _k, const db_op_t* _o,
                                const db_val_t* _v, const int _n);


Updating rows in a table can be done by calling the update API function :

/**
 * \brief Update some rows in the specified table.
 *
 * The function implements UPDATE SQL directive. It is possible to modify one
 * or more rows in a table using this function.
 * \param _h database connection handle
 * \param _k array of keys (column names) that will be matched
 * \param _o array of operators to be used with key-value pairs
 * \param _v array of values that the row must match to be modified
 * \param _uk array of keys (column names) that will be modified
 * \param _uv new values for keys specified in _k parameter
 * \param _n number of key-value pairs in _k and _v parameters
 * \param _un number of key-value pairs in _uk and _uv parameters
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_update_f) (const db_con_t* _h, const db_key_t* _k, const db_op_t* _o,
                                const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv,
                                const int _n, const int _un);


Replacing rows in a database table can be done with the replace function from the API :

/**
 * \brief Insert a row and replace if one already exists.
 *
 * The function implements the REPLACE SQL directive. It is possible to insert
 * a row and replace if one already exists. The old row will be deleted before
 * the insertion of the new data.
 * \param _h structure representing database connection
 * \param _k key names
 * \param _v values of the keys
 * \param _n number of key=value pairs
 * \return returns 0 if everything is OK, otherwise returns value < 0
*/
typedef int (*db_replace_f) (const db_con_t* handle, const db_key_t* keys,
                                const db_val_t* vals, const int n);


Sometimes, for optimizing database operations, it is useful to know the auto-increment primary key value upon inserting / updating a record. For such cases, the last_inserted_id API call can be used :

/**
 * \brief Retrieve the last inserted ID in a table.
 *
 * The function returns the value generated for an AUTO_INCREMENT column by the
 * previous INSERT or UPDATE  statement. Use this function after you have
 * performed an INSERT statement into a table that contains an AUTO_INCREMENT
 * field.
 * \param _h structure representing database connection
 * \return returns the ID as integer or returns 0 if the previous statement
 * does not use an AUTO_INCREMENT value.
 */
typedef int (*db_last_inserted_id_f) (const db_con_t* _h);


Also, when we want to insert a row into a table, and update the row in case of duplicate key errors, we should use the insert_update API call :

/**
 * \brief Insert a row into specified table, update on duplicate key.
 *
 * The function implements the INSERT ON DUPLICATE KEY UPDATE SQL directive.
 * It is possible to insert a row and update if one already exists.
 * The old row will not deleted before the insertion of the new data.
 * \param _h structure representing database connection
 * \param _k key names
 * \param _v values of the keys
 * \param _n number of key=value pairs
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_insert_update_f) (const db_con_t* _h, const db_key_t* _k,
                                const db_val_t* _v, const int _n);


For running all other database queries, one should use the raw_query API function.

/**
 * \brief Raw SQL query.
 *
 * This function can be used to do database specific queries. Please
 * use this function only if needed, as this creates portability issues
 * for the different databases. Also keep in mind that you need to
 * escape all external data sources that you use. You could use the
 * escape_common and unescape_common functions in the core for this task.
 * \see escape_common
 * \see unescape_common
 * \param _h structure representing database connection
 * \param _s the SQL query
 * \param _r structure for the result
 * \return returns 0 if everything is OK, otherwise returns value < 0
 */
typedef int (*db_raw_query_f) (const db_con_t* _h, const str* _s, db_res_t** _r);
Edit | History | Print | Recent Changes | Search
Page last modified on December 11, 2019, at 07:01 PM