1 /* 2 * Copyright (C) 2001-2003 FhG Fokus 3 * Copyright (C) 2007-2008 1&1 Internet AG 4 * 5 * This file is part of Kamailio, a free SIP server. 6 * 7 * Kamailio is free software; you can redistribute it and/or modify 8 * it under the terms of the GNU General Public License as published by 9 * the Free Software Foundation; either version 2 of the License, or 10 * (at your option) any later version 11 * 12 * Kamailio is distributed in the hope that it will be useful, 13 * but WITHOUT ANY WARRANTY; without even the implied warranty of 14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 15 * GNU General Public License for more details. 16 * 17 * You should have received a copy of the GNU General Public License 18 * along with this program; if not, write to the Free Software 19 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 20 */ 21 22 /** 23 * \file lib/srdb1/db.h 24 * \ingroup db1 25 * \ref db.c 26 * \brief Generic Database Interface 27 * 28 * This is a generic database interface for modules that need to utilize a 29 * database. The interface should be used by all modules that access database. 30 * The interface will be independent of the underlying database server. 31 * Notes: 32 * If possible, use the predefined macros if you need to access any structure 33 * attributes. 34 * For additional description, see the comments in the sources of mysql module. 35 * 36 * If you want to see more complicated examples of how the API could be used, 37 * take a look at the sources of the usrloc or auth modules. 38 */ 39 40 #ifndef DB1_H 41 #define DB1_H 42 43 #include "db_key.h" 44 #include "db_op.h" 45 #include "db_val.h" 46 #include "db_con.h" 47 #include "db_res.h" 48 #include "db_cap.h" 49 #include "db_con.h" 50 #include "db_row.h" 51 #include "db_pooling.h" 52 #include "db_locking.h" 53 54 /** 55 * \brief Specify table name that will be used for subsequent operations. 56 * 57 * The function db_use_table takes a table name and stores it db1_con_t structure. 58 * All subsequent operations (insert, delete, update, query) are performed on 59 * that table. 60 * \param _h database connection handle 61 * \param _t table name 62 * \return returns 0 if everything is OK, otherwise returns value < 0 63 */ 64 typedef int (*db_use_table_f)(db1_con_t* _h, const str * _t); 65 66 /** 67 * \brief Initialize database connection and obtain the connection handle. 68 * 69 * This function initialize the database API and open a new database 70 * connection. This function must be called after bind_dbmod but before any 71 * other database API function is called. 72 * 73 * The function takes one parameter, the parameter must contain the database 74 * connection URL. The URL is of the form 75 * mysql://username:password\@host:port/database where: 76 * 77 * username: Username to use when logging into database (optional). 78 * password: password if it was set (optional) 79 * host: Hosname or IP address of the host where database server lives (mandatory) 80 * port: Port number of the server if the port differs from default value (optional) 81 * database: If the database server supports multiple databases, you must specify the 82 * name of the database (optional). 83 * \see bind_dbmod 84 * \param _sqlurl database connection URL 85 * \return returns a pointer to the db1_con_t representing the connection if it was 86 * successful, otherwise 0 is returned 87 */ 88 typedef db1_con_t* (*db_init_f) (const str* _sqlurl); 89 90 /** 91 * \brief Initialize database connection and obtain the connection handle. 92 * 93 * This function initialize the database API and open a new database 94 * connection. This function must be called after bind_dbmod but before any 95 * other database API function is called. 96 * 97 * The function takes one parameter, the parameter must contain the database 98 * connection URL. The URL is of the form 99 * mysql://username:password\@host:port/database where: 100 * 101 * username: Username to use when logging into database (optional). 102 * password: password if it was set (optional) 103 * host: Hosname or IP address of the host where database server lives (mandatory) 104 * port: Port number of the server if the port differs from default value (optional) 105 * database: If the database server supports multiple databases, you must specify the 106 * name of the database (optional). 107 * \see bind_dbmod 108 * \param _sqlurl database connection URL 109 * \param _pooling whether or not to use a pooled connection 110 * \return returns a pointer to the db1_con_t representing the connection if it was 111 * successful, otherwise 0 is returned 112 */ 113 typedef db1_con_t* (*db_init2_f) (const str* _sqlurl, db_pooling_t _pooling); 114 115 /** 116 * \brief Close a database connection and free all memory used. 117 * 118 * The function closes previously open connection and frees all previously 119 * allocated memory. The function db_close must be the very last function called. 120 * \param _h db1_con_t structure representing the database connection 121 */ 122 typedef void (*db_close_f) (db1_con_t* _h); 123 124 125 /** 126 * \brief Query table for specified rows. 127 * 128 * This function implements the SELECT SQL directive. 129 * If _k and _v parameters are NULL and _n is zero, you will get the whole table. 130 * 131 * if _c is NULL and _nc is zero, you will get all table columns in the result. 132 * _r will point to a dynamically allocated structure, it is neccessary to call 133 * db_free_result function once you are finished with the result. 134 * 135 * If _op is 0, equal (=) will be used for all key-value pairs comparisons. 136 * 137 * Strings in the result are not duplicated, they will be discarded if you call 138 * db_free_result, make a copy yourself if you need to keep it after db_free_result. 139 * 140 * You must call db_free_result before you can call db_query again! 141 * \see db_free_result 142 * 143 * \param _h database connection handle 144 * \param _k array of column names that will be compared and their values must match 145 * \param _op array of operators to be used with key-value pairs 146 * \param _v array of values, columns specified in _k parameter must match these values 147 * \param _c array of column names that you are interested in 148 * \param _n number of key-value pairs to match in _k and _v parameters 149 * \param _nc number of columns in _c parameter 150 * \param _o order by statement for query 151 * \param _r address of variable where pointer to the result will be stored 152 * \return returns 0 if everything is OK, otherwise returns value < 0 153 */ 154 typedef int (*db_query_f) (const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op, 155 const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc, 156 const db_key_t _o, db1_res_t** _r); 157 158 /** 159 * \brief Gets a partial result set, fetch rows from a result 160 * 161 * Gets a partial result set, fetch a number of rows from a database result. 162 * This function initialize the given result structure on the first run, and 163 * fetches the nrows number of rows. On subsequenting runs, it uses the 164 * existing result and fetches more rows, until it reaches the end of the 165 * result set. Because of this the result needs to be null in the first 166 * invocation of the function. If the number of wanted rows is zero, the 167 * function returns anything with a result of zero. 168 * \param _h structure representing database connection 169 * \param _r structure for the result 170 * \param _n the number of rows that should be fetched 171 * \return returns 0 if everything is OK, otherwise returns value < 0 172 */ 173 typedef int (*db_fetch_result_f) (const db1_con_t* _h, db1_res_t** _r, const int _n); 174 175 176 /** 177 * \brief Raw SQL query. 178 * 179 * This function can be used to do database specific queries. Please 180 * use this function only if needed, as this creates portability issues 181 * for the different databases. Also keep in mind that you need to 182 * escape all external data sources that you use. You could use the 183 * escape_common and unescape_common functions in the core for this task. 184 * \see escape_common 185 * \see unescape_common 186 * \param _h structure representing database connection 187 * \param _s the SQL query 188 * \param _r structure for the result 189 * \return returns 0 if everything is OK, otherwise returns value < 0 190 */ 191 typedef int (*db_raw_query_f) (const db1_con_t* _h, const str* _s, db1_res_t** _r); 192 193 194 /** 195 * \brief Raw SQL query via async framework. 196 * 197 * This function can be used to do database specific queries. Please 198 * use this function only if needed, as this creates portability issues 199 * for the different databases. Also keep in mind that you need to 200 * escape all external data sources that you use. You could use the 201 * escape_common and unescape_common functions in the core for this task. 202 * \see escape_common 203 * \see unescape_common 204 * \param _h structure representing database connection 205 * \param _s the SQL query 206 * \return returns 0 if everything is OK, otherwise returns value < 0 207 */ 208 typedef int (*db_raw_query_async_f) (const db1_con_t* _h, const str* _s); 209 210 211 /** 212 * \brief Free a result allocated by db_query. 213 * 214 * This function frees all memory allocated previously in db_query. Its 215 * neccessary to call this function on a db1_res_t structure if you don't need the 216 * structure anymore. You must call this function before you call db_query again! 217 * \param _h database connection handle 218 * \param _r pointer to db1_res_t structure to destroy 219 * \return returns 0 if everything is OK, otherwise returns value < 0 220 */ 221 typedef int (*db_free_result_f) (db1_con_t* _h, db1_res_t* _r); 222 223 224 /** 225 * \brief Insert a row into the specified table. 226 * 227 * This function implements INSERT SQL directive, you can insert one or more 228 * rows in a table using this function. 229 * \param _h database connection handle 230 * \param _k array of keys (column names) 231 * \param _v array of values for keys specified in _k parameter 232 * \param _n number of keys-value pairs int _k and _v parameters 233 * \return returns 0 if everything is OK, otherwise returns value < 0 234 */ 235 typedef int (*db_insert_f) (const db1_con_t* _h, const db_key_t* _k, 236 const db_val_t* _v, const int _n); 237 238 239 /** 240 * \brief Delete a row from the specified table. 241 * 242 * This function implements DELETE SQL directive, it is possible to delete one or 243 * more rows from a table. 244 * If _k is NULL and _v is NULL and _n is zero, all rows are deleted, the 245 * resulting table will be empty. 246 * If _o is NULL, the equal operator "=" will be used for the comparison. 247 * 248 * \param _h database connection handle 249 * \param _k array of keys (column names) that will be matched 250 * \param _o array of operators to be used with key-value pairs 251 * \param _v array of values that the row must match to be deleted 252 * \param _n number of keys-value parameters in _k and _v parameters 253 * \return returns 0 if everything is OK, otherwise returns value < 0 254 */ 255 typedef int (*db_delete_f) (const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o, 256 const db_val_t* _v, const int _n); 257 258 259 /** 260 * \brief Update some rows in the specified table. 261 * 262 * The function implements UPDATE SQL directive. It is possible to modify one 263 * or more rows in a table using this function. 264 * \param _h database connection handle 265 * \param _k array of keys (column names) that will be matched 266 * \param _o array of operators to be used with key-value pairs 267 * \param _v array of values that the row must match to be modified 268 * \param _uk array of keys (column names) that will be modified 269 * \param _uv new values for keys specified in _k parameter 270 * \param _n number of key-value pairs in _k and _v parameters 271 * \param _un number of key-value pairs in _uk and _uv parameters 272 * \return returns 0 if everything is OK, otherwise returns value < 0 273 */ 274 typedef int (*db_update_f) (const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o, 275 const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv, 276 const int _n, const int _un); 277 278 279 /** 280 * \brief Insert a row and replace if one already exists. 281 * 282 * The function implements the REPLACE SQL directive. It is possible to insert 283 * a row and replace if one already exists. The old row will be deleted before 284 * the insertion of the new data. 285 * \param _h structure representing database connection 286 * \param _k key names 287 * \param _v values of the keys 288 * \param _n number of key=value pairs 289 * \param _un number of keys to build the unique key, starting from first _k 290 * \param _m mode - first update, then insert, or first insert, then update 291 * \note the last two parameters are used only if the DB server does not 292 * have native replace command (like postgres - the module doing an internal 293 * implementation using synchronized update/affected rows/insert mechanism) 294 * \return returns 0 if everything is OK, otherwise returns value < 0 295 */ 296 typedef int (*db_replace_f) (const db1_con_t* handle, const db_key_t* keys, 297 const db_val_t* vals, const int n, const int _un, const int _m); 298 299 300 /** 301 * \brief Retrieve the last inserted ID in a table. 302 * 303 * The function returns the value generated for an AUTO_INCREMENT column by the 304 * previous INSERT or UPDATE statement. Use this function after you have 305 * performed an INSERT statement into a table that contains an AUTO_INCREMENT 306 * field. 307 * \param _h structure representing database connection 308 * \return returns the ID as integer or returns 0 if the previous statement 309 * does not use an AUTO_INCREMENT value. 310 */ 311 typedef int (*db_last_inserted_id_f) (const db1_con_t* _h); 312 313 314 /** 315 * \brief Insert a row into specified table, update on duplicate key. 316 * 317 * The function implements the INSERT ON DUPLICATE KEY UPDATE SQL directive. 318 * It is possible to insert a row and update if one already exists. 319 * The old row will not deleted before the insertion of the new data. 320 * \param _h structure representing database connection 321 * \param _k key names 322 * \param _v values of the keys 323 * \param _n number of key=value pairs 324 * \return returns 0 if everything is OK, otherwise returns value < 0 325 */ 326 typedef int (*db_insert_update_f) (const db1_con_t* _h, const db_key_t* _k, 327 const db_val_t* _v, const int _n); 328 329 330 /** 331 * \brief Insert delayed a row into the specified table. 332 * 333 * This function implements INSERT DELAYED SQL directive. It is possible to 334 * insert one or more rows in a table with delay using this function. 335 * \param _h database connection handle 336 * \param _k array of keys (column names) 337 * \param _v array of values for keys specified in _k parameter 338 * \param _n number of keys-value pairs int _k and _v parameters 339 * \return returns 0 if everything is OK, otherwise returns value < 0 340 */ 341 typedef int (*db_insert_delayed_f) (const db1_con_t* _h, const db_key_t* _k, 342 const db_val_t* _v, const int _n); 343 344 /** 345 * \brief Insert a row into the specified table via async framework. 346 * 347 * This function implements INSERT DELAYED SQL directive. It is possible to 348 * insert one or more rows in a table with delay using this function. 349 * \param _h database connection handle 350 * \param _k array of keys (column names) 351 * \param _v array of values for keys specified in _k parameter 352 * \param _n number of keys-value pairs int _k and _v parameters 353 * \return returns 0 if everything is OK, otherwise returns value < 0 354 */ 355 typedef int (*db_insert_async_f) (const db1_con_t* _h, const db_key_t* _k, 356 const db_val_t* _v, const int _n); 357 358 359 360 /** 361 * \brief Retrieve the number of affected rows for the last query. 362 * 363 * The function returns the rows affected by the last query. 364 * If any other type of query was the last, it returns null. 365 * \param _h structure representing database connection 366 * \return returns the number of rows as integer or returns -1 on error 367 */ 368 typedef int (*db_affected_rows_f) (const db1_con_t* _h); 369 370 /** 371 * \brief Start a single transaction that will consist of one or more queries. 372 * 373 * \param _h structure representing database connection 374 * \return 0 if everything is OK, otherwise returns < 0 375 */ 376 typedef int (*db_start_transaction_f) (db1_con_t* _h, db_locking_t _l); 377 378 /** 379 * \brief End a transaction. 380 * 381 * \param _h structure representing database connection 382 * \return 0 if everything is OK, otherwise returns < 0 383 */ 384 typedef int (*db_end_transaction_f) (db1_con_t* _h); 385 386 /** 387 * \brief Abort a transaction. 388 * 389 * Use this function if you have an error after having started a transaction 390 * and you want to rollback any uncommitted changes before continuing. 391 * \param _h structure representing database connection 392 * \return 1 if there was something to rollbak, 0 if not, negative on failure 393 */ 394 typedef int (*db_abort_transaction_f) (db1_con_t* _h); 395 396 /** 397 * \brief Database module callbacks 398 * 399 * This structure holds function pointer to all database functions. Before this 400 * structure can be used it must be initialized with bind_dbmod. 401 * \see bind_dbmod 402 */ 403 typedef struct db_func { 404 unsigned int cap; /* Capability vector of the database transport */ 405 db_use_table_f use_table; /* Specify table name */ 406 db_init_f init; /* Initialize database connection */ 407 db_init2_f init2; /* Initialize database connection */ 408 db_close_f close; /* Close database connection */ 409 db_query_f query; /* query a table */ 410 db_fetch_result_f fetch_result; /* fetch result */ 411 db_raw_query_f raw_query; /* Raw query - SQL */ 412 db_free_result_f free_result; /* Free a query result */ 413 db_insert_f insert; /* Insert into table */ 414 db_delete_f delete; /* Delete from table */ 415 db_update_f update; /* Update table */ 416 db_replace_f replace; /* Replace row in a table */ 417 db_last_inserted_id_f last_inserted_id; /* Retrieve the last inserted ID 418 in a table */ 419 db_insert_update_f insert_update; /* Insert into table, update on duplicate key */ 420 db_insert_delayed_f insert_delayed; /* Insert delayed into table */ 421 db_insert_async_f insert_async; /* Insert async into table */ 422 db_affected_rows_f affected_rows; /* Numer of affected rows for last query */ 423 db_start_transaction_f start_transaction; /* Start a single transaction consisting of multiple queries */ 424 db_end_transaction_f end_transaction; /* End a transaction */ 425 db_abort_transaction_f abort_transaction; /* Abort a transaction */ 426 db_query_f query_lock; /* query a table and lock rows for update */ 427 db_raw_query_async_f raw_query_async; /* Raw query - SQL */ 428 } db_func_t; 429 430 431 /** 432 * \brief Bind database module functions 433 * 434 * This function is special, it's only purpose is to call find_export function in 435 * the core and find the addresses of all other database related functions. The 436 * db_func_t callback given as parameter is updated with the found addresses. 437 * 438 * This function must be called before any other database API call! 439 * 440 * The database URL is of the form "mysql://username:password@host:port/database" or 441 * "mysql" (database module name). 442 * In the case of a database connection URL, this function looks only at the first 443 * token (the database protocol). In the example above that would be "mysql": 444 * \see db_func_t 445 * \param mod database connection URL or a database module name 446 * \param dbf database module callbacks 447 * \return returns 0 if everything is OK, otherwise returns value < 0 448 */ 449 int db_bind_mod(const str* mod, db_func_t* dbf); 450 451 452 /** 453 * \brief Helper for db_init function. 454 * 455 * This helper method do the actual work for the database specific db_init 456 * functions. 457 * \param url database connection URL 458 * \param (*new_connection)() Pointer to the db specific connection creation method 459 * \return returns a pointer to the db1_con_t representing the connection if it was 460 successful, otherwise 0 is returned. 461 */ 462 db1_con_t* db_do_init(const str* url, void* (*new_connection)()); 463 464 465 /** 466 * \brief Helper for db_init2 function. 467 * 468 * This helper method do the actual work for the database specific db_init 469 * functions. 470 * \param url database connection URL 471 * \param (*new_connection)() Pointer to the db specific connection creation method 472 * \param pooling whether or not to use a pooled connection 473 * \return returns a pointer to the db1_con_t representing the connection if it was 474 successful, otherwise 0 is returned. 475 */ 476 db1_con_t* db_do_init2(const str* url, void* (*new_connection)(), db_pooling_t pooling); 477 478 479 /** 480 * \brief Helper for db_close function. 481 * 482 * This helper method does some work for the closing of a database 483 * connection. No function should be called after this 484 * \param _h database connection handle 485 * \param (*free_connection) Pointer to the db specifc free_connection method 486 */ 487 void db_do_close(db1_con_t* _h, void (*free_connection)()); 488 489 490 /** 491 * \brief Get the version of a table. 492 * 493 * Returns the version number of a given table from the version table. 494 * Instead of this function you should use db_check_table_version! 495 * \see db_check_table_version 496 * \param dbf database module callbacks 497 * \param con database connection handle 498 * \param table checked table 499 * \return the version number if present, 0 if no version data available, < 0 on error 500 */ 501 int db_table_version(const db_func_t* dbf, db1_con_t* con, const str* table); 502 503 /** 504 * \brief Check the table version 505 * 506 * Small helper function to check the table version. 507 * \param dbf database module callbacks 508 * \param dbh database connection handle 509 * \param table checked table 510 * \param version checked version 511 * \return 0 means ok, -1 means an error occurred 512 */ 513 int db_check_table_version(db_func_t* dbf, db1_con_t* dbh, const str* table, const unsigned int version); 514 515 /** 516 * \brief Stores the name of a table. 517 * 518 * Stores the name of the table that will be used by subsequent database 519 * functions calls in a db1_con_t structure. 520 * \param _h database connection handle 521 * \param _t stored name 522 * \return 0 if everything is ok, otherwise returns value < 0 523 */ 524 int db_use_table(db1_con_t* _h, const str* _t); 525 526 /** 527 * \brief Bind the DB API exported by a module. 528 * 529 * The function links the functions implemented by the module to the members 530 * of db_func_t structure 531 * \param dbb db_func_t structure representing the variable where to bind 532 * \return 0 if everything is ok, otherwise returns -1 533 */ 534 535 typedef int (*db_bind_api_f)(db_func_t *dbb); 536 537 /** 538 * \brief Generic query helper for load bulk data 539 * 540 * Generic query helper method for load bulk data, e.g. lcr tables 541 * \param binding database module binding 542 * \param handle database connection 543 * \param name database table name 544 * \param cols queried columns 545 * \param count number of queried columns 546 * \param strict if set to 1 an error is returned when no data could be loaded, 547 otherwise just a warning is logged 548 * \param res database result, unchanged on failure and if no data could be found 549 * \return 0 if the query was run successful, -1 otherwise 550 */ 551 int db_load_bulk_data(db_func_t* binding, db1_con_t* handle, str* name, db_key_t* cols, 552 unsigned int count, unsigned int strict, db1_res_t* res); 553 554 /** 555 * \brief DB API init function. 556 * 557 * This function must be executed by DB connector modules at load time to 558 * initialize the internals of DB API library. 559 * \return returns 0 on successful initialization, -1 on error. 560 */ 561 int db_api_init(void); 562 563 /** 564 * \brief wrapper around db query to handle fetch capability 565 * \return -1 error; 0 ok with no fetch capability; 1 ok with fetch capability 566 */ 567 int db_fetch_query(db_func_t *dbf, int frows, 568 db1_con_t* _h, const db_key_t* _k, const db_op_t* _op, 569 const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc, 570 const db_key_t _o, db1_res_t** _r); 571 572 /** 573 * \brief wrapper around db query_lock to handle fetch capability 574 * \return -1 error; 0 ok with no fetch capability; 1 ok with fetch capability 575 */ 576 int db_fetch_query_lock(db_func_t *dbf, int frows, 577 db1_con_t* _h, const db_key_t* _k, const db_op_t* _op, 578 const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc, 579 const db_key_t _o, db1_res_t** _r); 580 581 /** 582 * \brief wrapper around db fetch to handle fetch capability 583 * \return -1 error; 0 ok with no fetch capability; 1 ok with fetch capability 584 */ 585 int db_fetch_next(db_func_t *dbf, int frows, db1_con_t* _h, 586 db1_res_t** _r); 587 588 /** 589 * \brief Error logging helper for database version check error. 590 * \param table database table name string 591 */ 592 #define DB_TABLE_VERSION_ERROR(table) LM_ERR("Error during version check for db table:" \ 593 " %.*s, check database structure.\n", table.len, table.s) 594 595 #endif /* DB1_H */ 596