res/res_config_odbc.c

   1 /*
   2  * Asterisk -- An open source telephony toolkit.
   3  *
   4  * Copyright (C) 1999 - 2005, Digium, Inc.
   5  *
   6  * Mark Spencer <markster@digium.com>
   7  *
   8  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
   9  *
  10  * See http://www.asterisk.org for more information about
  11  * the Asterisk project. Please do not directly contact
  12  * any of the maintainers of this project for assistance;
  13  * the project provides a web site, mailing lists and IRC
  14  * channels for your use.
  15  *
  16  * This program is free software, distributed under the terms of
  17  * the GNU General Public License Version 2. See the LICENSE file
  18  * at the top of the source tree.
  19  */
  20
  21 /*! \file
  22  *
  23  * \brief odbc+odbc plugin for portable configuration engine
  24  *
  25  * \author Mark Spencer <markster@digium.com>
  26  * \author Anthony Minessale II <anthmct@yahoo.com>
  27  *
  28  * \arg http://www.unixodbc.org
  29  */
  30
  31 /*** MODULEINFO
  32         <depend>unixodbc</depend>
  33         <depend>ltdl</depend>
  34         <depend>res_odbc</depend>
  35  ***/
  36
  37 #include "asterisk.h"
  38
  39 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
  40
  41 #include <stdio.h>
  42 #include <stdlib.h>
  43 #include <unistd.h>
  44 #include <string.h>
  45
  46 #include "asterisk/file.h"
  47 #include "asterisk/logger.h"
  48 #include "asterisk/channel.h"
  49 #include "asterisk/pbx.h"
  50 #include "asterisk/config.h"
  51 #include "asterisk/module.h"
  52 #include "asterisk/lock.h"
  53 #include "asterisk/options.h"
  54 #include "asterisk/res_odbc.h"
  55 #include "asterisk/utils.h"
  56
  57 struct custom_prepare_struct {
  58         const char *sql;
  59         const char *extra;
  60         va_list ap;
  61 };
  62
  63 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
  64 {
  65         int res, x = 1;
  66         struct custom_prepare_struct *cps = data;
  67         const char *newparam, *newval;
  68         SQLHSTMT stmt;
  69         va_list ap;
  70
  71         va_copy(ap, cps->ap);
  72
  73         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
  74         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
  75                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
  76                 return NULL;
  77         }
  78
  79         res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
  80         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
  81                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
  82                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
  83                 return NULL;
  84         }
  85
  86         while ((newparam = va_arg(ap, const char *))) {
  87                 newval = va_arg(ap, const char *);
  88                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
  89         }
  90         va_end(ap);
  91
  92         if (!ast_strlen_zero(cps->extra))
  93                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
  94         return stmt;
  95 }
  96
  97 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
  98 {
  99         struct odbc_obj *obj;
 100         SQLHSTMT stmt;
 101         char sql[1024];
 102         char coltitle[256];
 103         char rowdata[2048];
 104         char *op;
 105         const char *newparam, *newval;
 106         char *stringp;
 107         char *chunk;
 108         SQLSMALLINT collen;
 109         int res;
 110         int x;
 111         struct ast_variable *var=NULL, *prev=NULL;
 112         SQLULEN colsize;
 113         SQLSMALLINT colcount=0;
 114         SQLSMALLINT datatype;
 115         SQLSMALLINT decimaldigits;
 116         SQLSMALLINT nullable;
 117         SQLLEN indicator;
 118         va_list aq;
 119         struct custom_prepare_struct cps = { .sql = sql };
 120
 121         va_copy(cps.ap, ap);
 122         va_copy(aq, ap);
 123
 124         if (!table)
 125                 return NULL;
 126
 127         obj = ast_odbc_request_obj(database, 0);
 128
 129         if (!obj) {
 130                 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
 131                 return NULL;
 132         }
 133
 134         newparam = va_arg(aq, const char *);
 135         if (!newparam) {
 136                 ast_odbc_release_obj(obj);
 137                 return NULL;
 138         }
 139         newval = va_arg(aq, const char *);
 140         op = !strchr(newparam, ' ') ? " =" : "";
 141         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
 142                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 143         while((newparam = va_arg(aq, const char *))) {
 144                 op = !strchr(newparam, ' ') ? " =" : "";
 145                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
 146                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 147                 newval = va_arg(aq, const char *);
 148         }
 149         va_end(aq);
 150
 151         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 152
 153         if (!stmt) {
 154                 ast_odbc_release_obj(obj);
 155                 return NULL;
 156         }
 157
 158         res = SQLNumResultCols(stmt, &colcount);
 159         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 160                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
 161                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 162                 ast_odbc_release_obj(obj);
 163                 return NULL;
 164         }
 165
 166         res = SQLFetch(stmt);
 167         if (res == SQL_NO_DATA) {
 168                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 169                 ast_odbc_release_obj(obj);
 170                 return NULL;
 171         }
 172         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 173                 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
 174                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 175                 ast_odbc_release_obj(obj);
 176                 return NULL;
 177         }
 178         for (x = 0; x < colcount; x++) {
 179                 rowdata[0] = '\0';
 180                 collen = sizeof(coltitle);
 181                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
 182                                         &datatype, &colsize, &decimaldigits, &nullable);
 183                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 184                         ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
 185                         if (var)
 186                                 ast_variables_destroy(var);
 187                         ast_odbc_release_obj(obj);
 188                         return NULL;
 189                 }
 190
 191                 indicator = 0;
 192                 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
 193                 if (indicator == SQL_NULL_DATA)
 194                         continue;
 195
 196                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 197                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
 198                         if (var)
 199                                 ast_variables_destroy(var);
 200                         ast_odbc_release_obj(obj);
 201                         return NULL;
 202                 }
 203                 stringp = rowdata;
 204                 while(stringp) {
 205                         chunk = strsep(&stringp, ";");
 206                         if (!ast_strlen_zero(ast_strip(chunk))) {
 207                                 if (prev) {
 208                                         prev->next = ast_variable_new(coltitle, chunk);
 209                                         if (prev->next)
 210                                                 prev = prev->next;
 211                                 } else
 212                                         prev = var = ast_variable_new(coltitle, chunk);
 213                         }
 214                 }
 215         }
 216
 217
 218         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 219         ast_odbc_release_obj(obj);
 220         return var;
 221 }
 222
 223 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
 224 {
 225         struct odbc_obj *obj;
 226         SQLHSTMT stmt;
 227         char sql[1024];
 228         char coltitle[256];
 229         char rowdata[2048];
 230         const char *initfield=NULL;
 231         char *op;
 232         const char *newparam, *newval;
 233         char *stringp;
 234         char *chunk;
 235         SQLSMALLINT collen;
 236         int res;
 237         int x;
 238         struct ast_variable *var=NULL;
 239         struct ast_config *cfg=NULL;
 240         struct ast_category *cat=NULL;
 241         struct ast_realloca ra;
 242         SQLULEN colsize;
 243         SQLSMALLINT colcount=0;
 244         SQLSMALLINT datatype;
 245         SQLSMALLINT decimaldigits;
 246         SQLSMALLINT nullable;
 247         SQLLEN indicator;
 248         struct custom_prepare_struct cps = { .sql = sql };
 249         va_list aq;
 250
 251         va_copy(cps.ap, ap);
 252         va_copy(aq, ap);
 253
 254         if (!table)
 255                 return NULL;
 256         memset(&ra, 0, sizeof(ra));
 257
 258         obj = ast_odbc_request_obj(database, 0);
 259         if (!obj)
 260                 return NULL;
 261
 262         newparam = va_arg(aq, const char *);
 263         if (!newparam)  {
 264                 ast_odbc_release_obj(obj);
 265                 return NULL;
 266         }
 267         initfield = ast_strdupa(newparam);
 268         if ((op = strchr(initfield, ' ')))
 269                 *op = '\0';
 270         newval = va_arg(aq, const char *);
 271         op = !strchr(newparam, ' ') ? " =" : "";
 272         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
 273                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 274         while((newparam = va_arg(aq, const char *))) {
 275                 op = !strchr(newparam, ' ') ? " =" : "";
 276                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
 277                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 278                 newval = va_arg(aq, const char *);
 279         }
 280         if (initfield)
 281                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
 282         va_end(aq);
 283
 284         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 285
 286         if (!stmt) {
 287                 ast_odbc_release_obj(obj);
 288                 return NULL;
 289         }
 290
 291         res = SQLNumResultCols(stmt, &colcount);
 292         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 293                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
 294                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 295                 ast_odbc_release_obj(obj);
 296                 return NULL;
 297         }
 298
 299         cfg = ast_config_new();
 300         if (!cfg) {
 301                 ast_log(LOG_WARNING, "Out of memory!\n");
 302                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 303                 ast_odbc_release_obj(obj);
 304                 return NULL;
 305         }
 306
 307         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
 308                 var = NULL;
 309                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 310                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
 311                         continue;
 312                 }
 313                 cat = ast_category_new("");
 314                 if (!cat) {
 315                         ast_log(LOG_WARNING, "Out of memory!\n");
 316                         continue;
 317                 }
 318                 for (x=0;x<colcount;x++) {
 319                         rowdata[0] = '\0';
 320                         collen = sizeof(coltitle);
 321                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
 322                                                 &datatype, &colsize, &decimaldigits, &nullable);
 323                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 324                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
 325                                 ast_category_destroy(cat);
 326                                 continue;
 327                         }
 328
 329                         indicator = 0;
 330                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
 331                         if (indicator == SQL_NULL_DATA)
 332                                 continue;
 333
 334                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 335                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
 336                                 ast_category_destroy(cat);
 337                                 continue;
 338                         }
 339                         stringp = rowdata;
 340                         while(stringp) {
 341                                 chunk = strsep(&stringp, ";");
 342                                 if (!ast_strlen_zero(ast_strip(chunk))) {
 343                                         if (initfield && !strcmp(initfield, coltitle))
 344                                                 ast_category_rename(cat, chunk);
 345                                         var = ast_variable_new(coltitle, chunk);
 346                                         ast_variable_append(cat, var);
 347                                 }
 348                         }
 349                 }
 350                 ast_category_append(cfg, cat);
 351         }
 352
 353         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 354         ast_odbc_release_obj(obj);
 355         return cfg;
 356 }
 357
 358 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
 359 {
 360         struct odbc_obj *obj;
 361         SQLHSTMT stmt;
 362         char sql[256];
 363         SQLLEN rowcount=0;
 364         const char *newparam, *newval;
 365         int res;
 366         va_list aq;
 367         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
 368
 369         va_copy(cps.ap, ap);
 370         va_copy(aq, ap);
 371
 372         if (!table)
 373                 return -1;
 374
 375         obj = ast_odbc_request_obj(database, 0);
 376         if (!obj)
 377                 return -1;
 378
 379         newparam = va_arg(aq, const char *);
 380         if (!newparam)  {
 381                 ast_odbc_release_obj(obj);
 382                 return -1;
 383         }
 384         newval = va_arg(aq, const char *);
 385         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
 386         while((newparam = va_arg(aq, const char *))) {
 387                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
 388                 newval = va_arg(aq, const char *);
 389         }
 390         va_end(aq);
 391         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
 392
 393         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 394
 395         if (!stmt) {
 396                 ast_odbc_release_obj(obj);
 397                 return -1;
 398         }
 399
 400         res = SQLRowCount(stmt, &rowcount);
 401         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 402         ast_odbc_release_obj(obj);
 403
 404         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 405                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
 406                 return -1;
 407         }
 408
 409         if (rowcount >= 0)
 410                 return (int)rowcount;
 411
 412         return -1;
 413 }
 414
 415 struct config_odbc_obj {
 416         char *sql;
 417         unsigned long cat_metric;
 418         char category[128];
 419         char var_name[128];
 420         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
 421         SQLLEN err;
 422 };
 423
 424 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
 425 {
 426         struct config_odbc_obj *q = data;
 427         SQLHSTMT sth;
 428         int res;
 429
 430         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
 431         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 432                 if (option_verbose > 3)
 433                         ast_verbose( VERBOSE_PREFIX_4 "Failure in AllocStatement %d\n", res);
 434                 return NULL;
 435         }
 436
 437         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
 438         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 439                 if (option_verbose > 3)
 440                         ast_verbose( VERBOSE_PREFIX_4 "Error in PREPARE %d\n", res);
 441                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
 442                 return NULL;
 443         }
 444
 445         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
 446         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
 447         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
 448         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
 449
 450         return sth;
 451 }
 452
 453 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *cfg, int withcomments)
 454 {
 455         struct ast_variable *new_v;
 456         struct ast_category *cur_cat;
 457         int res = 0;
 458         struct odbc_obj *obj;
 459         char sqlbuf[1024] = "";
 460         char *sql = sqlbuf;
 461         size_t sqlleft = sizeof(sqlbuf);
 462         unsigned int last_cat_metric = 0;
 463         SQLSMALLINT rowcount = 0;
 464         SQLHSTMT stmt;
 465         char last[128] = "";
 466         struct config_odbc_obj q;
 467
 468         memset(&q, 0, sizeof(q));
 469
 470         if (!file || !strcmp (file, "res_config_odbc.conf"))
 471                 return NULL;            /* cant configure myself with myself ! */
 472
 473         obj = ast_odbc_request_obj(database, 0);
 474         if (!obj)
 475                 return NULL;
 476
 477         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
 478         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
 479         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
 480         q.sql = sqlbuf;
 481
 482         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
 483
 484         if (!stmt) {
 485                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
 486                 ast_odbc_release_obj(obj);
 487                 return NULL;
 488         }
 489
 490         res = SQLNumResultCols(stmt, &rowcount);
 491
 492         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 493                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
 494                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 495                 ast_odbc_release_obj(obj);
 496                 return NULL;
 497         }
 498
 499         if (!rowcount) {
 500                 ast_log(LOG_NOTICE, "found nothing\n");
 501                 ast_odbc_release_obj(obj);
 502                 return cfg;
 503         }
 504
 505         cur_cat = ast_config_get_current_category(cfg);
 506
 507         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
 508                 if (!strcmp (q.var_name, "#include")) {
 509                         if (!ast_config_internal_load(q.var_val, cfg, 0)) {
 510                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 511                                 ast_odbc_release_obj(obj);
 512                                 return NULL;
 513                         }
 514                         continue;
 515                 }
 516                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
 517                         cur_cat = ast_category_new(q.category);
 518                         if (!cur_cat) {
 519                                 ast_log(LOG_WARNING, "Out of memory!\n");
 520                                 break;
 521                         }
 522                         strcpy(last, q.category);
 523                         last_cat_metric = q.cat_metric;
 524                         ast_category_append(cfg, cur_cat);
 525                 }
 526
 527                 new_v = ast_variable_new(q.var_name, q.var_val);
 528                 ast_variable_append(cur_cat, new_v);
 529         }
 530
 531         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 532         ast_odbc_release_obj(obj);
 533         return cfg;
 534 }
 535
 536 static struct ast_config_engine odbc_engine = {
 537         .name = "odbc",
 538         .load_func = config_odbc,
 539         .realtime_func = realtime_odbc,
 540         .realtime_multi_func = realtime_multi_odbc,
 541         .update_func = update_odbc
 542 };
 543
 544 static int unload_module (void)
 545 {
 546         ast_module_user_hangup_all();
 547         ast_config_engine_deregister(&odbc_engine);
 548         if (option_verbose)
 549                 ast_verbose("res_config_odbc unloaded.\n");
 550         return 0;
 551 }
 552
 553 static int load_module (void)
 554 {
 555         ast_config_engine_register(&odbc_engine);
 556         if (option_verbose)
 557                 ast_verbose("res_config_odbc loaded.\n");
 558         return 0;
 559 }
 560
 561 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "ODBC Configuration",
 562                 .load = load_module,
 563                 .unload = unload_module,
 564                 );