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                 return NULL;
 137         newval = va_arg(aq, const char *);
 138         op = !strchr(newparam, ' ') ? " =" : "";
 139         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
 140                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 141         while((newparam = va_arg(aq, const char *))) {
 142                 op = !strchr(newparam, ' ') ? " =" : "";
 143                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
 144                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 145                 newval = va_arg(aq, const char *);
 146         }
 147         va_end(aq);
 148
 149         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 150
 151         if (!stmt) {
 152                 ast_odbc_release_obj(obj);
 153                 return NULL;
 154         }
 155
 156         res = SQLNumResultCols(stmt, &colcount);
 157         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 158                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
 159                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 160                 ast_odbc_release_obj(obj);
 161                 return NULL;
 162         }
 163
 164         res = SQLFetch(stmt);
 165         if (res == SQL_NO_DATA) {
 166                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 167                 ast_odbc_release_obj(obj);
 168                 return NULL;
 169         }
 170         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 171                 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
 172                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 173                 ast_odbc_release_obj(obj);
 174                 return NULL;
 175         }
 176         for (x = 0; x < colcount; x++) {
 177                 rowdata[0] = '\0';
 178                 collen = sizeof(coltitle);
 179                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
 180                                         &datatype, &colsize, &decimaldigits, &nullable);
 181                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 182                         ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
 183                         if (var)
 184                                 ast_variables_destroy(var);
 185                         ast_odbc_release_obj(obj);
 186                         return NULL;
 187                 }
 188
 189                 indicator = 0;
 190                 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
 191                 if (indicator == SQL_NULL_DATA)
 192                         continue;
 193
 194                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 195                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
 196                         if (var)
 197                                 ast_variables_destroy(var);
 198                         ast_odbc_release_obj(obj);
 199                         return NULL;
 200                 }
 201                 stringp = rowdata;
 202                 while(stringp) {
 203                         chunk = strsep(&stringp, ";");
 204                         if (!ast_strlen_zero(ast_strip(chunk))) {
 205                                 if (prev) {
 206                                         prev->next = ast_variable_new(coltitle, chunk);
 207                                         if (prev->next)
 208                                                 prev = prev->next;
 209                                 } else
 210                                         prev = var = ast_variable_new(coltitle, chunk);
 211                         }
 212                 }
 213         }
 214
 215
 216         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 217         ast_odbc_release_obj(obj);
 218         return var;
 219 }
 220
 221 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
 222 {
 223         struct odbc_obj *obj;
 224         SQLHSTMT stmt;
 225         char sql[1024];
 226         char coltitle[256];
 227         char rowdata[2048];
 228         const char *initfield=NULL;
 229         char *op;
 230         const char *newparam, *newval;
 231         char *stringp;
 232         char *chunk;
 233         SQLSMALLINT collen;
 234         int res;
 235         int x;
 236         struct ast_variable *var=NULL;
 237         struct ast_config *cfg=NULL;
 238         struct ast_category *cat=NULL;
 239         struct ast_realloca ra;
 240         SQLULEN colsize;
 241         SQLSMALLINT colcount=0;
 242         SQLSMALLINT datatype;
 243         SQLSMALLINT decimaldigits;
 244         SQLSMALLINT nullable;
 245         SQLLEN indicator;
 246         struct custom_prepare_struct cps = { .sql = sql };
 247         va_list aq;
 248
 249         va_copy(cps.ap, ap);
 250         va_copy(aq, ap);
 251
 252         if (!table)
 253                 return NULL;
 254         memset(&ra, 0, sizeof(ra));
 255
 256         obj = ast_odbc_request_obj(database, 0);
 257         if (!obj)
 258                 return NULL;
 259
 260         newparam = va_arg(aq, const char *);
 261         if (!newparam)  {
 262                 ast_odbc_release_obj(obj);
 263                 return NULL;
 264         }
 265         initfield = ast_strdupa(newparam);
 266         if ((op = strchr(initfield, ' ')))
 267                 *op = '\0';
 268         newval = va_arg(aq, const char *);
 269         op = !strchr(newparam, ' ') ? " =" : "";
 270         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
 271                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 272         while((newparam = va_arg(aq, const char *))) {
 273                 op = !strchr(newparam, ' ') ? " =" : "";
 274                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
 275                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 276                 newval = va_arg(aq, const char *);
 277         }
 278         if (initfield)
 279                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
 280         va_end(aq);
 281
 282         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 283
 284         if (!stmt) {
 285                 ast_odbc_release_obj(obj);
 286                 return NULL;
 287         }
 288
 289         res = SQLNumResultCols(stmt, &colcount);
 290         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 291                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
 292                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 293                 ast_odbc_release_obj(obj);
 294                 return NULL;
 295         }
 296
 297         cfg = ast_config_new();
 298         if (!cfg) {
 299                 ast_log(LOG_WARNING, "Out of memory!\n");
 300                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 301                 ast_odbc_release_obj(obj);
 302                 return NULL;
 303         }
 304
 305         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
 306                 var = NULL;
 307                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 308                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
 309                         continue;
 310                 }
 311                 cat = ast_category_new("");
 312                 if (!cat) {
 313                         ast_log(LOG_WARNING, "Out of memory!\n");
 314                         continue;
 315                 }
 316                 for (x=0;x<colcount;x++) {
 317                         rowdata[0] = '\0';
 318                         collen = sizeof(coltitle);
 319                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
 320                                                 &datatype, &colsize, &decimaldigits, &nullable);
 321                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 322                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
 323                                 ast_category_destroy(cat);
 324                                 continue;
 325                         }
 326
 327                         indicator = 0;
 328                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
 329                         if (indicator == SQL_NULL_DATA)
 330                                 continue;
 331
 332                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 333                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
 334                                 ast_category_destroy(cat);
 335                                 continue;
 336                         }
 337                         stringp = rowdata;
 338                         while(stringp) {
 339                                 chunk = strsep(&stringp, ";");
 340                                 if (!ast_strlen_zero(ast_strip(chunk))) {
 341                                         if (initfield && !strcmp(initfield, coltitle))
 342                                                 ast_category_rename(cat, chunk);
 343                                         var = ast_variable_new(coltitle, chunk);
 344                                         ast_variable_append(cat, var);
 345                                 }
 346                         }
 347                 }
 348                 ast_category_append(cfg, cat);
 349         }
 350
 351         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 352         ast_odbc_release_obj(obj);
 353         return cfg;
 354 }
 355
 356 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
 357 {
 358         struct odbc_obj *obj;
 359         SQLHSTMT stmt;
 360         char sql[256];
 361         SQLLEN rowcount=0;
 362         const char *newparam, *newval;
 363         int res;
 364         va_list aq;
 365         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
 366
 367         va_copy(cps.ap, ap);
 368         va_copy(aq, ap);
 369
 370         if (!table)
 371                 return -1;
 372
 373         obj = ast_odbc_request_obj(database, 0);
 374         if (!obj)
 375                 return -1;
 376
 377         newparam = va_arg(aq, const char *);
 378         if (!newparam)  {
 379                 ast_odbc_release_obj(obj);
 380                 return -1;
 381         }
 382         newval = va_arg(aq, const char *);
 383         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
 384         while((newparam = va_arg(aq, const char *))) {
 385                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
 386                 newval = va_arg(aq, const char *);
 387         }
 388         va_end(aq);
 389         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
 390
 391         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 392
 393         if (!stmt) {
 394                 ast_odbc_release_obj(obj);
 395                 return -1;
 396         }
 397
 398         res = SQLRowCount(stmt, &rowcount);
 399         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 400         ast_odbc_release_obj(obj);
 401
 402         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 403                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
 404                 return -1;
 405         }
 406
 407         if (rowcount >= 0)
 408                 return (int)rowcount;
 409
 410         return -1;
 411 }
 412
 413 struct config_odbc_obj {
 414         char *sql;
 415         unsigned long cat_metric;
 416         char category[128];
 417         char var_name[128];
 418         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
 419         SQLLEN err;
 420 };
 421
 422 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
 423 {
 424         struct config_odbc_obj *q = data;
 425         SQLHSTMT sth;
 426         int res;
 427
 428         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
 429         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 430                 if (option_verbose > 3)
 431                         ast_verbose( VERBOSE_PREFIX_4 "Failure in AllocStatement %d\n", res);
 432                 return NULL;
 433         }
 434
 435         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
 436         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 437                 if (option_verbose > 3)
 438                         ast_verbose( VERBOSE_PREFIX_4 "Error in PREPARE %d\n", res);
 439                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
 440                 return NULL;
 441         }
 442
 443         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
 444         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
 445         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
 446         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
 447
 448         return sth;
 449 }
 450
 451 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *cfg, int withcomments)
 452 {
 453         struct ast_variable *new_v;
 454         struct ast_category *cur_cat;
 455         int res = 0;
 456         struct odbc_obj *obj;
 457         char sqlbuf[1024] = "";
 458         char *sql = sqlbuf;
 459         size_t sqlleft = sizeof(sqlbuf);
 460         unsigned int last_cat_metric = 0;
 461         SQLSMALLINT rowcount = 0;
 462         SQLHSTMT stmt;
 463         char last[128] = "";
 464         struct config_odbc_obj q;
 465
 466         memset(&q, 0, sizeof(q));
 467
 468         if (!file || !strcmp (file, "res_config_odbc.conf"))
 469                 return NULL;            /* cant configure myself with myself ! */
 470
 471         obj = ast_odbc_request_obj(database, 0);
 472         if (!obj)
 473                 return NULL;
 474
 475         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
 476         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
 477         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
 478         q.sql = sqlbuf;
 479
 480         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
 481
 482         if (!stmt) {
 483                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
 484                 ast_odbc_release_obj(obj);
 485                 return NULL;
 486         }
 487
 488         res = SQLNumResultCols(stmt, &rowcount);
 489
 490         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 491                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
 492                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 493                 ast_odbc_release_obj(obj);
 494                 return NULL;
 495         }
 496
 497         if (!rowcount) {
 498                 ast_log(LOG_NOTICE, "found nothing\n");
 499                 ast_odbc_release_obj(obj);
 500                 return cfg;
 501         }
 502
 503         cur_cat = ast_config_get_current_category(cfg);
 504
 505         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
 506                 if (!strcmp (q.var_name, "#include")) {
 507                         if (!ast_config_internal_load(q.var_val, cfg, 0)) {
 508                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 509                                 ast_odbc_release_obj(obj);
 510                                 return NULL;
 511                         }
 512                         continue;
 513                 }
 514                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
 515                         cur_cat = ast_category_new(q.category);
 516                         if (!cur_cat) {
 517                                 ast_log(LOG_WARNING, "Out of memory!\n");
 518                                 break;
 519                         }
 520                         strcpy(last, q.category);
 521                         last_cat_metric = q.cat_metric;
 522                         ast_category_append(cfg, cur_cat);
 523                 }
 524
 525                 new_v = ast_variable_new(q.var_name, q.var_val);
 526                 ast_variable_append(cur_cat, new_v);
 527         }
 528
 529         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 530         ast_odbc_release_obj(obj);
 531         return cfg;
 532 }
 533
 534 static struct ast_config_engine odbc_engine = {
 535         .name = "odbc",
 536         .load_func = config_odbc,
 537         .realtime_func = realtime_odbc,
 538         .realtime_multi_func = realtime_multi_odbc,
 539         .update_func = update_odbc
 540 };
 541
 542 static int unload_module (void)
 543 {
 544         ast_module_user_hangup_all();
 545         ast_config_engine_deregister(&odbc_engine);
 546         if (option_verbose)
 547                 ast_verbose("res_config_odbc unloaded.\n");
 548         return 0;
 549 }
 550
 551 static int load_module (void)
 552 {
 553         ast_config_engine_register(&odbc_engine);
 554         if (option_verbose)
 555                 ast_verbose("res_config_odbc loaded.\n");
 556         return 0;
 557 }
 558
 559 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "ODBC Configuration",
 560                 .load = load_module,
 561                 .unload = unload_module,
 562                 );