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