db.c 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528
  1. /*
  2. * $Id$
  3. *
  4. * Copyright (C) 2001-2003 FhG Fokus
  5. * Copyright (C) 2007-2008 1&1 Internet AG
  6. *
  7. * This file is part of Kamailio, a free SIP server.
  8. *
  9. * Kamailio is free software; you can redistribute it and/or modify
  10. * it under the terms of the GNU General Public License as published by
  11. * the Free Software Foundation; either version 2 of the License, or
  12. * (at your option) any later version
  13. *
  14. * Kamailio is distributed in the hope that it will be useful,
  15. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  16. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  17. * GNU General Public License for more details.
  18. *
  19. * You should have received a copy of the GNU General Public License
  20. * along with this program; if not, write to the Free Software
  21. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  22. */
  23. /*
  24. * History:
  25. * --------
  26. * 2004-06-06 bind_dbmod takes dbf as parameter (andrei)
  27. * 2006-10-10 Added support for retrieving the last inserted ID (Carsten Bock, BASIS AudioNet GmbH)
  28. */
  29. /**
  30. * \file lib/srdb1/db.c
  31. * \ingroup db1
  32. * \brief Generic Database Interface
  33. *
  34. */
  35. /*! \defgroup db DB: The Kamailio generic database interface
  36. * This is a generic database interface for modules that need to utilize a
  37. * database. The interface should be used by all modules that access database.
  38. * The interface will be independent of the underlying database server.
  39. * Notes:
  40. * If possible, use the predefined macros if you need to access any structure
  41. * attributes.
  42. * For additional description, see the comments in the sources of mysql module.
  43. *
  44. * If you want to see more complicated examples of how the API could be used,
  45. * take a look at the sources of the usrloc or auth modules.
  46. * - \ref usrloc
  47. * - \ref auth
  48. *
  49. * Implemented modules
  50. * - \ref ../modules/db_berkeley
  51. * - \ref ../modules/db_flatstore
  52. * - \ref ../modules/db_text
  53. * - \ref ../modules/db_mysql
  54. * - \ref ../modules/db_oracle
  55. * - \ref ../modules/db_postgres
  56. * - \ref ../modules/db_unixodbc
  57. */
  58. #include "../../dprint.h"
  59. #include "../../sr_module.h"
  60. #include "../../mem/mem.h"
  61. #include "../../ut.h"
  62. #include "../../globals.h"
  63. #include "db_cap.h"
  64. #include "db_id.h"
  65. #include "db_pool.h"
  66. #include "db_query.h"
  67. #include "db.h"
  68. static unsigned int MAX_URL_LENGTH = 255; /*!< maximum length of a SQL URL */
  69. int db_check_api(db_func_t* dbf, char *mname)
  70. {
  71. if(dbf==NULL)
  72. return -1;
  73. /* All modules must export db_use_table */
  74. if (dbf->use_table == 0) {
  75. LM_ERR("module %s does not export db_use_table function\n", mname);
  76. goto error;
  77. }
  78. /* All modules must export db_init */
  79. if (dbf->init == 0) {
  80. LM_ERR("module %s does not export db_init function\n", mname);
  81. goto error;
  82. }
  83. /* All modules must export db_close */
  84. if (dbf->close == 0) {
  85. LM_ERR("module %s does not export db_close function\n", mname);
  86. goto error;
  87. }
  88. if (dbf->query) {
  89. dbf->cap |= DB_CAP_QUERY;
  90. }
  91. if (dbf->fetch_result) {
  92. dbf->cap |= DB_CAP_FETCH;
  93. }
  94. if (dbf->raw_query) {
  95. dbf->cap |= DB_CAP_RAW_QUERY;
  96. }
  97. /* Free result must be exported if DB_CAP_QUERY or
  98. * DB_CAP_RAW_QUERY is set */
  99. if ((dbf->cap & (DB_CAP_QUERY|DB_CAP_RAW_QUERY)) && (dbf->free_result==0)) {
  100. LM_ERR("module %s supports queries but does not export free_result\n",
  101. mname);
  102. goto error;
  103. }
  104. if (dbf->insert) {
  105. dbf->cap |= DB_CAP_INSERT;
  106. }
  107. if (dbf->delete) {
  108. dbf->cap |= DB_CAP_DELETE;
  109. }
  110. if (dbf->update) {
  111. dbf->cap |= DB_CAP_UPDATE;
  112. }
  113. if (dbf->replace) {
  114. dbf->cap |= DB_CAP_REPLACE;
  115. }
  116. if (dbf->last_inserted_id) {
  117. dbf->cap |= DB_CAP_LAST_INSERTED_ID;
  118. }
  119. if (dbf->insert_update) {
  120. dbf->cap |= DB_CAP_INSERT_UPDATE;
  121. }
  122. if (dbf->insert_delayed) {
  123. dbf->cap |= DB_CAP_INSERT_DELAYED;
  124. }
  125. if (dbf->affected_rows) {
  126. dbf->cap |= DB_CAP_AFFECTED_ROWS;
  127. }
  128. return 0;
  129. error:
  130. return -1;
  131. }
  132. /*! \brief fills mydbf with the corresponding db module callbacks
  133. * \param mod
  134. * \param mydbf
  135. * \return returns 0 on success, -1 on error
  136. * \note on error mydbf will contain only 0s */
  137. int db_bind_mod(const str* mod, db_func_t* mydbf)
  138. {
  139. char *name, *tmp, *p;
  140. int len;
  141. db_func_t dbf;
  142. db_bind_api_f dbind;
  143. if (!mod || !mod->s) {
  144. LM_CRIT("null database module name\n");
  145. return -1;
  146. }
  147. if (mydbf==0) {
  148. LM_CRIT("null dbf parameter\n");
  149. return -1;
  150. }
  151. if (mod->len > MAX_URL_LENGTH)
  152. {
  153. LM_ERR("SQL URL too long\n");
  154. return -1;
  155. }
  156. // add the prefix
  157. name = pkg_malloc(mod->len + 4);
  158. if (!name) {
  159. LM_ERR("no private memory left\n");
  160. return -1;
  161. }
  162. memcpy(name, "db_", 3);
  163. memcpy(name+3, mod->s, mod->len);
  164. name[mod->len+3] = 0;
  165. /* for safety we initialize mydbf with 0 (this will cause
  166. * a segfault immediately if someone tries to call a function
  167. * from it without checking the return code from bind_dbmod */
  168. memset((void*)mydbf, 0, sizeof(db_func_t));
  169. p = strchr(name, ':');
  170. if (p) {
  171. len = p - name;
  172. tmp = (char*)pkg_malloc(len + 4);
  173. if (!tmp) {
  174. LM_ERR("no private memory left\n");
  175. pkg_free(name);
  176. return -1;
  177. }
  178. memcpy(tmp, name, len);
  179. tmp[len] = '\0';
  180. pkg_free(name);
  181. } else {
  182. tmp = name;
  183. }
  184. dbind = (db_bind_api_f)find_mod_export(tmp, "db_bind_api", 0, 0);
  185. if(dbind != NULL)
  186. {
  187. LM_DBG("using db bind api for %s\n", tmp);
  188. if(dbind(&dbf)<0)
  189. {
  190. LM_ERR("db_bind_api returned error for module %s\n", tmp);
  191. goto error;
  192. }
  193. } else {
  194. memset(&dbf, 0, sizeof(db_func_t));
  195. LM_DBG("using export interface to bind %s\n", tmp);
  196. dbf.use_table = (db_use_table_f)find_mod_export(tmp,
  197. "db_use_table", 2, 0);
  198. dbf.init = (db_init_f)find_mod_export(tmp, "db_init", 1, 0);
  199. dbf.init2 = (db_init2_f)find_mod_export(tmp, "db_init2", 1, 0);
  200. dbf.close = (db_close_f)find_mod_export(tmp, "db_close", 2, 0);
  201. dbf.query = (db_query_f)find_mod_export(tmp, "db_query", 2, 0);
  202. dbf.fetch_result = (db_fetch_result_f)find_mod_export(tmp,
  203. "db_fetch_result", 2, 0);
  204. dbf.raw_query = (db_raw_query_f)find_mod_export(tmp,
  205. "db_raw_query", 2, 0);
  206. dbf.free_result = (db_free_result_f)find_mod_export(tmp,
  207. "db_free_result", 2, 0);
  208. dbf.insert = (db_insert_f)find_mod_export(tmp, "db_insert", 2, 0);
  209. dbf.delete = (db_delete_f)find_mod_export(tmp, "db_delete", 2, 0);
  210. dbf.update = (db_update_f)find_mod_export(tmp, "db_update", 2, 0);
  211. dbf.replace = (db_replace_f)find_mod_export(tmp, "db_replace", 2, 0);
  212. dbf.last_inserted_id= (db_last_inserted_id_f)find_mod_export(tmp,
  213. "db_last_inserted_id", 1, 0);
  214. dbf.affected_rows = (db_affected_rows_f)find_mod_export(tmp,
  215. "db_affected_rows", 1, 0);
  216. dbf.insert_update = (db_insert_update_f)find_mod_export(tmp,
  217. "db_insert_update", 2, 0);
  218. dbf.insert_delayed = (db_insert_delayed_f)find_mod_export(tmp,
  219. "db_insert_delayed", 2, 0);
  220. dbf.start_transaction = (db_start_transaction_f)find_mod_export(tmp,
  221. "db_start_transaction", 2, 0);
  222. dbf.end_transaction = (db_end_transaction_f)find_mod_export(tmp,
  223. "db_end_transaction", 1, 0);
  224. dbf.abort_transaction = (db_abort_transaction_f)find_mod_export(tmp,
  225. "db_abort_transaction", 1, 0);
  226. dbf.query_lock = (db_query_f)find_mod_export(tmp, "db_query_lock", 2, 0);
  227. }
  228. if(db_check_api(&dbf, tmp)!=0)
  229. goto error;
  230. *mydbf=dbf; /* copy */
  231. pkg_free(tmp);
  232. return 0;
  233. error:
  234. pkg_free(tmp);
  235. return -1;
  236. }
  237. /*! \brief
  238. * Initialize database module
  239. * \note No function should be called before this
  240. */
  241. db1_con_t* db_do_init(const str* url, void* (*new_connection)())
  242. {
  243. return db_do_init2(url, *new_connection, DB_POOLING_PERMITTED);
  244. }
  245. /*! \brief
  246. * Initialize database module
  247. * \note No function should be called before this
  248. */
  249. db1_con_t* db_do_init2(const str* url, void* (*new_connection)(), db_pooling_t pooling)
  250. {
  251. struct db_id* id;
  252. void* con;
  253. db1_con_t* res;
  254. int con_size = sizeof(db1_con_t) + sizeof(void *);
  255. id = 0;
  256. res = 0;
  257. if (!url || !url->s || !new_connection) {
  258. LM_ERR("invalid parameter value\n");
  259. return 0;
  260. }
  261. if (url->len > MAX_URL_LENGTH)
  262. {
  263. LM_ERR("The configured db_url is too long\n");
  264. return 0;
  265. }
  266. /* this is the root memory for this database connection. */
  267. res = (db1_con_t*)pkg_malloc(con_size);
  268. if (!res) {
  269. LM_ERR("no private memory left\n");
  270. return 0;
  271. }
  272. memset(res, 0, con_size);
  273. id = new_db_id(url, pooling);
  274. if (!id) {
  275. LM_ERR("cannot parse URL '%.*s'\n", url->len, url->s);
  276. goto err;
  277. }
  278. /* Find the connection in the pool */
  279. con = pool_get(id);
  280. if (!con) {
  281. LM_DBG("connection %p not found in pool\n", id);
  282. /* Not in the pool yet */
  283. con = new_connection(id);
  284. if (!con) {
  285. LM_ERR("could not add connection to the pool");
  286. goto err;
  287. }
  288. pool_insert((struct pool_con*)con);
  289. } else {
  290. LM_DBG("connection %p found in pool\n", id);
  291. free_db_id(id); // free the new id, as we use the pool instead
  292. id = 0;
  293. }
  294. res->tail = (unsigned long)con;
  295. return res;
  296. err:
  297. if (id) free_db_id(id);
  298. if (res) pkg_free(res);
  299. return 0;
  300. }
  301. /*! \brief
  302. * Shut down database module
  303. * \note No function should be called after this
  304. */
  305. void db_do_close(db1_con_t* _h, void (*free_connection)())
  306. {
  307. struct pool_con* con;
  308. if (!_h || !_h->tail) {
  309. LM_ERR("invalid parameter value\n");
  310. return;
  311. }
  312. con = (struct pool_con*)_h->tail;
  313. if (pool_remove(con) == 1) {
  314. free_connection(con);
  315. }
  316. pkg_free(_h);
  317. }
  318. /*! \brief
  319. * Get version of a table
  320. * \param dbf
  321. * \param connection
  322. * \param table
  323. * \return If there is no row for the given table, return version 0
  324. */
  325. int db_table_version(const db_func_t* dbf, db1_con_t* connection, const str* table)
  326. {
  327. db_key_t key[1], col[1];
  328. db_val_t val[1];
  329. db1_res_t* res = NULL;
  330. db_val_t* ver = 0;
  331. str *version = &version_table;
  332. str tmp1 = str_init(TABLENAME_COLUMN);
  333. str tmp2 = str_init(VERSION_COLUMN);
  334. int ret;
  335. if (!dbf||!connection || !table || !table->s) {
  336. LM_CRIT("invalid parameter value\n");
  337. return -1;
  338. }
  339. if (dbf->use_table(connection, version) < 0) {
  340. LM_ERR("error while changing table\n");
  341. return -1;
  342. }
  343. key[0] = &tmp1;
  344. VAL_TYPE(val) = DB1_STR;
  345. VAL_NULL(val) = 0;
  346. VAL_STR(val) = *table;
  347. col[0] = &tmp2;
  348. if (dbf->query(connection, key, 0, val, col, 1, 1, 0, &res) < 0) {
  349. LM_ERR("error in db_query\n");
  350. return -1;
  351. }
  352. if (RES_ROW_N(res) == 0) {
  353. LM_DBG("no row for table %.*s found\n",
  354. table->len, ZSW(table->s));
  355. return 0;
  356. }
  357. if (RES_ROW_N(res) != 1) {
  358. LM_ERR("invalid number of rows received:"
  359. " %d, %.*s\n", RES_ROW_N(res), table->len, ZSW(table->s));
  360. dbf->free_result(connection, res);
  361. return -1;
  362. }
  363. ver = ROW_VALUES(RES_ROWS(res));
  364. if ( VAL_TYPE(ver)!=DB1_INT || VAL_NULL(ver) ) {
  365. LM_ERR("invalid type (%d) or nul (%d) version "
  366. "columns for %.*s\n", VAL_TYPE(ver), VAL_NULL(ver),
  367. table->len, ZSW(table->s));
  368. dbf->free_result(connection, res);
  369. return -1;
  370. }
  371. ret = VAL_INT(ver);
  372. dbf->free_result(connection, res);
  373. return ret;
  374. }
  375. /*! \brief
  376. * Check the table version
  377. * 0 means ok, -1 means an error occured
  378. */
  379. int db_check_table_version(db_func_t* dbf, db1_con_t* dbh, const str* table, const unsigned int version)
  380. {
  381. int ver = db_table_version(dbf, dbh, table);
  382. if (ver < 0) {
  383. LM_ERR("querying version for table %.*s\n", table->len, table->s);
  384. return -1;
  385. } else if (ver != version) {
  386. LM_ERR("invalid version %d for table %.*s found, expected %d (check table structure and table \"version\")\n", ver, table->len, table->s, version);
  387. return -1;
  388. }
  389. return 0;
  390. }
  391. /*! \brief
  392. * Store name of table that will be used by
  393. * subsequent database functions
  394. */
  395. int db_use_table(db1_con_t* _h, const str* _t)
  396. {
  397. if (!_h || !_t || !_t->s) {
  398. LM_ERR("invalid parameter value\n");
  399. return -1;
  400. }
  401. CON_TABLE(_h) = _t;
  402. return 0;
  403. }
  404. /*! \brief Generic query helper for load bulk data
  405. *
  406. * Generic query helper method for load bulk data, e.g. lcr tables
  407. * \param binding database module binding
  408. * \param handle database connection
  409. * \param name database table name
  410. * \param cols queried columns
  411. * \param count number of queried columns
  412. * \param strict if set to 1 an error is returned when no data could be loaded,
  413. otherwise just a warning is logged
  414. * \param res database result, unchanged on failure and if no data could be found
  415. * \return 0 if the query was run successful, -1 otherwise
  416. */
  417. int db_load_bulk_data(db_func_t* binding, db1_con_t* handle, str* name, db_key_t* cols,
  418. unsigned int count, unsigned int strict, db1_res_t* res)
  419. {
  420. if (binding == NULL) {
  421. LM_ERR("invalid database module binding\n");
  422. return -1;
  423. }
  424. if(handle == NULL) {
  425. LM_ERR("invalid database handle\n");
  426. return -1;
  427. }
  428. if (binding->use_table(handle, name) < 0) {
  429. LM_ERR("error in use_table for database\n");
  430. return -1;
  431. }
  432. /* select the whole table and all the columns */
  433. if(binding->query(handle, 0, 0, 0, cols, 0, count, 0, &res) < 0) {
  434. LM_ERR("error while querying database\n");
  435. return -1;
  436. }
  437. if(RES_ROW_N(res) == 0) {
  438. binding->free_result(handle, res);
  439. if (strict == 1) {
  440. LM_ERR("no data in the database table %.*s\n", name->len, name->s);
  441. return -1;
  442. } else {
  443. LM_WARN("no data in the database table %.*s, use an empty set\n", name->len, name->s);
  444. return 0;
  445. }
  446. }
  447. return 0;
  448. }
  449. /**
  450. * \brief DB API init function.
  451. *
  452. * This function must be executed by DB connector modules at load time to
  453. * initialize the internals of DB API library.
  454. * \return returns 0 on successful initialization, -1 on error.
  455. */
  456. int db_api_init(void)
  457. {
  458. if(db_query_init()<0)
  459. return -1;
  460. return 0;
  461. }