福州网站建设哪个好/绍兴百度seo排名
https://www.opensips.org/Documentation/Development-Manual
目录
12. SQL 数据库 API
13. NoSQL API
14. Event Interface API
12. SQL 数据库 API
OpenSIPS封装了一套数据库API，模块开发者可以用它操作常见的SQL查询。其优势在于：
- 编写后台无关代码，因为DB API对实现后端相关的模块实际代码解耦
- 让无SQL能力的后端提供类SQL的特性（比如说：db_flatstore 模块直接操作纯文本文件，但开发者可以使用SQL语句插入数据）
db/db.h 中声明了大部分数据库相关的函数。启动时，开发者仅持有它需要连接的数据库URL。通过调用db_bind_mod ，OpenSIPS DB API 将尝试自动定位实际支持特定后端技术的DB模块，并返回所有后端操作所需要的函数。
/** * \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 IDin 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;
}
与模块绑定成功之后，开发者必须保证从后端脚本提取的URL也具备后续处理的能力（比如说，操作纯文本文件时，没有提供db_last_inserted_id_f 函数，因此，如果C代码中调用它，模块就会crash）。这可以用DB_CAPABILITY 宏完成。
/*** 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;
}
现在，我们已经绑定所需的后端模块，也确信它支持所需要的能力，那么，我们可以继续连接后台，即调用绑定函数中的init函数：
/*** \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;
}
对于大部分后端技术来说(比如说MySQL、Postgres) ，不支持多进程共享连接。基于这样的事实，开发者必须保证为每个进程创建独立的数据库连接，在模块开发上下文中，需要在child_init 函数中打开连接。
init()函数的输出将是后续DB交互的handler。当连接不再需要时，需要调用close 方法关闭：
/*** \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);
在向后端执行查询之前，为了确保你的代码不会运行在旧的数据库结构之上，通常的做法是对表做版本控制。db/db.h里提供了db_check_table_version用于此目的，它检查缺省OpenSIPS 数据库里的version 这张表：
/*
Parameters :dbf - the functions to be used for running the version querydbh - the connection to run the version querytable - str containing the table name we want to check for versionversion - 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);
在通过API执行查询之前，需要选择执行查询的表：
/*** \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); 所有查询之前必须调用use_table 函数。OpenSIPS 内部维护连接池，在多模块请求连接同一数据库时，连接会在模块间共享。因此，在一个进程的上下文中，同一连接可能用于不同模块，永远不要假定连接是某个模块独占的。
要执行一次 SELECT 查询，你应当调用 query 函数。其原型是：
/*** \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);
查询执行成功后，开发者必须处理输出的db_res_t才能使用查询结果数据集。以下是用于解释查询结果数据集的结构：
/*** 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;
为了帮助编码和阅读代码，定义了许多宏：
/* 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)
下面是完整实例，从查询开始到查询结束：
/* 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;
从上面例子我们可以看到，调用query 函数查询成功之后，必须调用 free_result API 函数释放返回的数据集。
/*** \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);
有些时候，特别是查询一个大数据表的时候，一次获取所有记录行可能不现实，因为这可能导致OpenSIPS私有内存开销太大。这时在，强烈推荐使用 fetch_result API。
/** * \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);
以下代码是使用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);
调用insert API函数可以插入数据记录：
/*** \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);
删除数据则调用delete API ：
/*** \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);
更新数据调用update API :
/*** \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);
替换数据库中的行可以调用replace 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);
有时候，为了优化数据库操作，了解自增主键值对插入/更新记录会很有帮助。调用last_inserted_id API可以获取：
/*** \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);
此外，如果我们想在插入冲突时变为变更操作，可以调用 insert_update API：
/*** \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);
执行其它数据库查询，可以调用raw_query API：
/*** \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);