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 "asterisk/file.h"
  42 #include "asterisk/channel.h"
  43 #include "asterisk/pbx.h"
  44 #include "asterisk/config.h"
  45 #include "asterisk/module.h"
  46 #include "asterisk/lock.h"
  47 #include "asterisk/res_odbc.h"
  48 #include "asterisk/utils.h"
  49
  50 struct custom_prepare_struct {
  51         const char *sql;
  52         const char *extra;
  53         va_list ap;
  54         unsigned long long skip;
  55 };
  56
  57 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
  58 {
  59         int res, x = 1, count = 0;
  60         struct custom_prepare_struct *cps = data;
  61         const char *newparam, *newval;
  62         SQLHSTMT stmt;
  63         va_list ap;
  64
  65         va_copy(ap, cps->ap);
  66
  67         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
  68         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
  69                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
  70                 return NULL;
  71         }
  72
  73         res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
  74         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
  75                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
  76                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
  77                 return NULL;
  78         }
  79
  80         while ((newparam = va_arg(ap, const char *))) {
  81                 newval = va_arg(ap, const char *);
  82                 if ((1 << count) & cps->skip) {
  83                         continue;
  84                 }
  85                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
  86         }
  87         va_end(ap);
  88
  89         if (!ast_strlen_zero(cps->extra))
  90                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
  91         return stmt;
  92 }
  93
  94 /*!
  95  * \brief Excute an SQL query and return ast_variable list
  96  * \param database
  97  * \param table
  98  * \param ap list containing one or more field/operator/value set.
  99  *
 100  * Select database and preform query on table, prepare the sql statement
 101  * Sub-in the values to the prepared statement and execute it. Return results
 102  * as a ast_variable list.
 103  *
 104  * \retval var on success
 105  * \retval NULL on failure
 106 */
 107 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
 108 {
 109         struct odbc_obj *obj;
 110         SQLHSTMT stmt;
 111         char sql[1024];
 112         char coltitle[256];
 113         char rowdata[2048];
 114         char *op;
 115         const char *newparam, *newval;
 116         char *stringp;
 117         char *chunk;
 118         SQLSMALLINT collen;
 119         int res;
 120         int x;
 121         struct ast_variable *var=NULL, *prev=NULL;
 122         SQLULEN colsize;
 123         SQLSMALLINT colcount=0;
 124         SQLSMALLINT datatype;
 125         SQLSMALLINT decimaldigits;
 126         SQLSMALLINT nullable;
 127         SQLLEN indicator;
 128         va_list aq;
 129         struct custom_prepare_struct cps = { .sql = sql };
 130
 131         va_copy(cps.ap, ap);
 132         va_copy(aq, ap);
 133
 134         if (!table)
 135                 return NULL;
 136
 137         obj = ast_odbc_request_obj(database, 0);
 138
 139         if (!obj) {
 140                 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
 141                 return NULL;
 142         }
 143
 144         newparam = va_arg(aq, const char *);
 145         if (!newparam) {
 146                 ast_odbc_release_obj(obj);
 147                 return NULL;
 148         }
 149         newval = va_arg(aq, const char *);
 150         op = !strchr(newparam, ' ') ? " =" : "";
 151         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
 152                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 153         while((newparam = va_arg(aq, const char *))) {
 154                 op = !strchr(newparam, ' ') ? " =" : "";
 155                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
 156                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 157                 newval = va_arg(aq, const char *);
 158         }
 159         va_end(aq);
 160
 161         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 162
 163         if (!stmt) {
 164                 ast_odbc_release_obj(obj);
 165                 return NULL;
 166         }
 167
 168         res = SQLNumResultCols(stmt, &colcount);
 169         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 170                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
 171                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 172                 ast_odbc_release_obj(obj);
 173                 return NULL;
 174         }
 175
 176         res = SQLFetch(stmt);
 177         if (res == SQL_NO_DATA) {
 178                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 179                 ast_odbc_release_obj(obj);
 180                 return NULL;
 181         }
 182         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 183                 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
 184                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 185                 ast_odbc_release_obj(obj);
 186                 return NULL;
 187         }
 188         for (x = 0; x < colcount; x++) {
 189                 rowdata[0] = '\0';
 190                 collen = sizeof(coltitle);
 191                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
 192                                         &datatype, &colsize, &decimaldigits, &nullable);
 193                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 194                         ast_log(LOG_WARNING, "SQL Describe Column 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
 201                 indicator = 0;
 202                 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
 203                 if (indicator == SQL_NULL_DATA)
 204                         rowdata[0] = '\0';
 205                 else if (ast_strlen_zero(rowdata)) {
 206                         /* Because we encode the empty string for a NULL, we will encode
 207                          * actual empty strings as a string containing a single whitespace. */
 208                         ast_copy_string(rowdata, " ", sizeof(rowdata));
 209                 }
 210
 211                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 212                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
 213                         if (var)
 214                                 ast_variables_destroy(var);
 215                         ast_odbc_release_obj(obj);
 216                         return NULL;
 217                 }
 218                 stringp = rowdata;
 219                 while(stringp) {
 220                         chunk = strsep(&stringp, ";");
 221                         if (!ast_strlen_zero(ast_strip(chunk))) {
 222                                 if (prev) {
 223                                         prev->next = ast_variable_new(coltitle, chunk, "");
 224                                         if (prev->next)
 225                                                 prev = prev->next;
 226                                 } else
 227                                         prev = var = ast_variable_new(coltitle, chunk, "");
 228                         }
 229                 }
 230         }
 231
 232
 233         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 234         ast_odbc_release_obj(obj);
 235         return var;
 236 }
 237
 238 /*!
 239  * \brief Excute an Select query and return ast_config list
 240  * \param database
 241  * \param table
 242  * \param ap list containing one or more field/operator/value set.
 243  *
 244  * Select database and preform query on table, prepare the sql statement
 245  * Sub-in the values to the prepared statement and execute it.
 246  * Execute this prepared query against several ODBC connected databases.
 247  * Return results as an ast_config variable.
 248  *
 249  * \retval var on success
 250  * \retval NULL on failure
 251 */
 252 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
 253 {
 254         struct odbc_obj *obj;
 255         SQLHSTMT stmt;
 256         char sql[1024];
 257         char coltitle[256];
 258         char rowdata[2048];
 259         const char *initfield=NULL;
 260         char *op;
 261         const char *newparam, *newval;
 262         char *stringp;
 263         char *chunk;
 264         SQLSMALLINT collen;
 265         int res;
 266         int x;
 267         struct ast_variable *var=NULL;
 268         struct ast_config *cfg=NULL;
 269         struct ast_category *cat=NULL;
 270         SQLULEN colsize;
 271         SQLSMALLINT colcount=0;
 272         SQLSMALLINT datatype;
 273         SQLSMALLINT decimaldigits;
 274         SQLSMALLINT nullable;
 275         SQLLEN indicator;
 276         struct custom_prepare_struct cps = { .sql = sql };
 277         va_list aq;
 278
 279         va_copy(cps.ap, ap);
 280         va_copy(aq, ap);
 281
 282         if (!table)
 283                 return NULL;
 284
 285         obj = ast_odbc_request_obj(database, 0);
 286         if (!obj)
 287                 return NULL;
 288
 289         newparam = va_arg(aq, const char *);
 290         if (!newparam)  {
 291                 ast_odbc_release_obj(obj);
 292                 return NULL;
 293         }
 294         initfield = ast_strdupa(newparam);
 295         if ((op = strchr(initfield, ' ')))
 296                 *op = '\0';
 297         newval = va_arg(aq, const char *);
 298         op = !strchr(newparam, ' ') ? " =" : "";
 299         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
 300                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 301         while((newparam = va_arg(aq, const char *))) {
 302                 op = !strchr(newparam, ' ') ? " =" : "";
 303                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
 304                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
 305                 newval = va_arg(aq, const char *);
 306         }
 307         if (initfield)
 308                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
 309         va_end(aq);
 310
 311         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 312
 313         if (!stmt) {
 314                 ast_odbc_release_obj(obj);
 315                 return NULL;
 316         }
 317
 318         res = SQLNumResultCols(stmt, &colcount);
 319         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 320                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
 321                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 322                 ast_odbc_release_obj(obj);
 323                 return NULL;
 324         }
 325
 326         cfg = ast_config_new();
 327         if (!cfg) {
 328                 ast_log(LOG_WARNING, "Out of memory!\n");
 329                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 330                 ast_odbc_release_obj(obj);
 331                 return NULL;
 332         }
 333
 334         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
 335                 var = NULL;
 336                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 337                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
 338                         continue;
 339                 }
 340                 cat = ast_category_new("","",99999);
 341                 if (!cat) {
 342                         ast_log(LOG_WARNING, "Out of memory!\n");
 343                         continue;
 344                 }
 345                 for (x=0;x<colcount;x++) {
 346                         rowdata[0] = '\0';
 347                         collen = sizeof(coltitle);
 348                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
 349                                                 &datatype, &colsize, &decimaldigits, &nullable);
 350                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 351                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
 352                                 ast_category_destroy(cat);
 353                                 continue;
 354                         }
 355
 356                         indicator = 0;
 357                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
 358                         if (indicator == SQL_NULL_DATA)
 359                                 continue;
 360
 361                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 362                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
 363                                 ast_category_destroy(cat);
 364                                 continue;
 365                         }
 366                         stringp = rowdata;
 367                         while(stringp) {
 368                                 chunk = strsep(&stringp, ";");
 369                                 if (!ast_strlen_zero(ast_strip(chunk))) {
 370                                         if (initfield && !strcmp(initfield, coltitle))
 371                                                 ast_category_rename(cat, chunk);
 372                                         var = ast_variable_new(coltitle, chunk, "");
 373                                         ast_variable_append(cat, var);
 374                                 }
 375                         }
 376                 }
 377                 ast_category_append(cfg, cat);
 378         }
 379
 380         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 381         ast_odbc_release_obj(obj);
 382         return cfg;
 383 }
 384
 385 /*!
 386  * \brief Excute an UPDATE query
 387  * \param database
 388  * \param table
 389  * \param keyfield where clause field
 390  * \param lookup value of field for where clause
 391  * \param ap list containing one or more field/value set(s).
 392  *
 393  * Update a database table, prepare the sql statement using keyfield and lookup
 394  * control the number of records to change. All values to be changed are stored in ap list.
 395  * Sub-in the values to the prepared statement and execute it.
 396  *
 397  * \retval number of rows affected
 398  * \retval -1 on failure
 399 */
 400 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
 401 {
 402         struct odbc_obj *obj;
 403         SQLHSTMT stmt;
 404         char sql[256];
 405         SQLLEN rowcount=0;
 406         const char *newparam, *newval;
 407         int res, count = 0;
 408         va_list aq;
 409         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
 410         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
 411         struct odbc_cache_columns *column;
 412
 413         va_copy(cps.ap, ap);
 414         va_copy(aq, ap);
 415
 416         if (!table) {
 417                 ast_odbc_release_table(tableptr);
 418                 return -1;
 419         }
 420
 421         obj = ast_odbc_request_obj(database, 0);
 422         if (!obj) {
 423                 ast_odbc_release_table(tableptr);
 424                 return -1;
 425         }
 426
 427         newparam = va_arg(aq, const char *);
 428         if (!newparam)  {
 429                 ast_odbc_release_obj(obj);
 430                 ast_odbc_release_table(tableptr);
 431                 return -1;
 432         }
 433         newval = va_arg(aq, const char *);
 434
 435         if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
 436                 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", newparam, table, database);
 437         }
 438
 439         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
 440         while((newparam = va_arg(aq, const char *))) {
 441                 if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
 442                         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
 443                         newval = va_arg(aq, const char *);
 444                 } else { /* the column does not exist in the table OR we've exceeded the space in our flag field */
 445                         cps.skip |= (((long long)1) << count);
 446                 }
 447                 count++;
 448         }
 449         va_end(aq);
 450         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
 451         ast_odbc_release_table(tableptr);
 452
 453         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 454
 455         if (!stmt) {
 456                 ast_odbc_release_obj(obj);
 457                 return -1;
 458         }
 459
 460         res = SQLRowCount(stmt, &rowcount);
 461         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 462         ast_odbc_release_obj(obj);
 463
 464         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 465                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
 466                 return -1;
 467         }
 468
 469         if (rowcount >= 0)
 470                 return (int)rowcount;
 471
 472         return -1;
 473 }
 474
 475 /*!
 476  * \brief Excute an INSERT query
 477  * \param database
 478  * \param table
 479  * \param ap list containing one or more field/value set(s)
 480  *
 481  * Insert a new record into database table, prepare the sql statement.
 482  * All values to be changed are stored in ap list.
 483  * Sub-in the values to the prepared statement and execute it.
 484  *
 485  * \retval number of rows affected
 486  * \retval -1 on failure
 487 */
 488 static int store_odbc(const char *database, const char *table, va_list ap)
 489 {
 490         struct odbc_obj *obj;
 491         SQLHSTMT stmt;
 492         char sql[256];
 493         char keys[256];
 494         char vals[256];
 495         SQLLEN rowcount=0;
 496         const char *newparam, *newval;
 497         int res;
 498         va_list aq;
 499         struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
 500
 501         va_copy(cps.ap, ap);
 502         va_copy(aq, ap);
 503
 504         if (!table)
 505                 return -1;
 506
 507         obj = ast_odbc_request_obj(database, 0);
 508         if (!obj)
 509                 return -1;
 510
 511         newparam = va_arg(aq, const char *);
 512         if (!newparam)  {
 513                 ast_odbc_release_obj(obj);
 514                 return -1;
 515         }
 516         newval = va_arg(aq, const char *);
 517         snprintf(keys, sizeof(keys), "%s", newparam);
 518         ast_copy_string(vals, "?", sizeof(vals));
 519         while ((newparam = va_arg(aq, const char *))) {
 520                 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
 521                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
 522                 newval = va_arg(aq, const char *);
 523         }
 524         va_end(aq);
 525         snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
 526
 527         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 528
 529         if (!stmt) {
 530                 ast_odbc_release_obj(obj);
 531                 return -1;
 532         }
 533
 534         res = SQLRowCount(stmt, &rowcount);
 535         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 536         ast_odbc_release_obj(obj);
 537
 538         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 539                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
 540                 return -1;
 541         }
 542
 543         if (rowcount >= 0)
 544                 return (int)rowcount;
 545
 546         return -1;
 547 }
 548
 549 /*!
 550  * \brief Excute an DELETE query
 551  * \param database
 552  * \param table
 553  * \param keyfield where clause field
 554  * \param lookup value of field for where clause
 555  * \param ap list containing one or more field/value set(s)
 556  *
 557  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
 558  * control the number of records to change. Additional params to match rows are stored in ap list.
 559  * Sub-in the values to the prepared statement and execute it.
 560  *
 561  * \retval number of rows affected
 562  * \retval -1 on failure
 563 */
 564 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
 565 {
 566         struct odbc_obj *obj;
 567         SQLHSTMT stmt;
 568         char sql[256];
 569         SQLLEN rowcount=0;
 570         const char *newparam, *newval;
 571         int res;
 572         va_list aq;
 573         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
 574
 575         va_copy(cps.ap, ap);
 576         va_copy(aq, ap);
 577
 578         if (!table)
 579                 return -1;
 580
 581         obj = ast_odbc_request_obj(database, 0);
 582         if (!obj)
 583                 return -1;
 584
 585         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
 586         while((newparam = va_arg(aq, const char *))) {
 587                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
 588                 newval = va_arg(aq, const char *);
 589         }
 590         va_end(aq);
 591         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
 592
 593         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
 594
 595         if (!stmt) {
 596                 ast_odbc_release_obj(obj);
 597                 return -1;
 598         }
 599
 600         res = SQLRowCount(stmt, &rowcount);
 601         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 602         ast_odbc_release_obj(obj);
 603
 604         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 605                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
 606                 return -1;
 607         }
 608
 609         if (rowcount >= 0)
 610                 return (int)rowcount;
 611
 612         return -1;
 613 }
 614
 615
 616 struct config_odbc_obj {
 617         char *sql;
 618         unsigned long cat_metric;
 619         char category[128];
 620         char var_name[128];
 621         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
 622         SQLLEN err;
 623 };
 624
 625 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
 626 {
 627         struct config_odbc_obj *q = data;
 628         SQLHSTMT sth;
 629         int res;
 630
 631         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
 632         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 633                 ast_verb(4, "Failure in AllocStatement %d\n", res);
 634                 return NULL;
 635         }
 636
 637         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
 638         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 639                 ast_verb(4, "Error in PREPARE %d\n", res);
 640                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
 641                 return NULL;
 642         }
 643
 644         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
 645         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
 646         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
 647         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
 648
 649         return sth;
 650 }
 651
 652 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *cfg, struct ast_flags flags, const char *sugg_incl, const char *who_asked)
 653 {
 654         struct ast_variable *new_v;
 655         struct ast_category *cur_cat;
 656         int res = 0;
 657         struct odbc_obj *obj;
 658         char sqlbuf[1024] = "";
 659         char *sql = sqlbuf;
 660         size_t sqlleft = sizeof(sqlbuf);
 661         unsigned int last_cat_metric = 0;
 662         SQLSMALLINT rowcount = 0;
 663         SQLHSTMT stmt;
 664         char last[128] = "";
 665         struct config_odbc_obj q;
 666         struct ast_flags loader_flags = { 0 };
 667
 668         memset(&q, 0, sizeof(q));
 669
 670         if (!file || !strcmp (file, "res_config_odbc.conf"))
 671                 return NULL;            /* cant configure myself with myself ! */
 672
 673         obj = ast_odbc_request_obj(database, 0);
 674         if (!obj)
 675                 return NULL;
 676
 677         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
 678         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
 679         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
 680         q.sql = sqlbuf;
 681
 682         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
 683
 684         if (!stmt) {
 685                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
 686                 ast_odbc_release_obj(obj);
 687                 return NULL;
 688         }
 689
 690         res = SQLNumResultCols(stmt, &rowcount);
 691
 692         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 693                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
 694                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 695                 ast_odbc_release_obj(obj);
 696                 return NULL;
 697         }
 698
 699         if (!rowcount) {
 700                 ast_log(LOG_NOTICE, "found nothing\n");
 701                 ast_odbc_release_obj(obj);
 702                 return cfg;
 703         }
 704
 705         cur_cat = ast_config_get_current_category(cfg);
 706
 707         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
 708                 if (!strcmp (q.var_name, "#include")) {
 709                         if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
 710                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 711                                 ast_odbc_release_obj(obj);
 712                                 return NULL;
 713                         }
 714                         continue;
 715                 }
 716                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
 717                         cur_cat = ast_category_new(q.category, "", 99999);
 718                         if (!cur_cat) {
 719                                 ast_log(LOG_WARNING, "Out of memory!\n");
 720                                 break;
 721                         }
 722                         strcpy(last, q.category);
 723                         last_cat_metric = q.cat_metric;
 724                         ast_category_append(cfg, cur_cat);
 725                 }
 726
 727                 new_v = ast_variable_new(q.var_name, q.var_val, "");
 728                 ast_variable_append(cur_cat, new_v);
 729         }
 730
 731         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 732         ast_odbc_release_obj(obj);
 733         return cfg;
 734 }
 735
 736 #define warn_length(col, size)  ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is not long enough to contain realtime data (needs %d)\n", table, database, col->name, size)
 737 #define warn_type(col, type)    ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is of the incorrect type (%d) to contain the required realtime data\n", table, database, col->name, col->type)
 738
 739 static int require_odbc(const char *database, const char *table, va_list ap)
 740 {
 741         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
 742         struct odbc_cache_columns *col;
 743         char *elm;
 744         int type, size;
 745
 746         if (!tableptr) {
 747                 return -1;
 748         }
 749
 750         while ((elm = va_arg(ap, char *))) {
 751                 type = va_arg(ap, require_type);
 752                 size = va_arg(ap, int);
 753                 /* Check if the field matches the criteria */
 754                 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
 755                         if (strcmp(col->name, elm) == 0) {
 756                                 /* Type check, first.  Some fields are more particular than others */
 757                                 switch (col->type) {
 758                                 case SQL_CHAR:
 759                                 case SQL_VARCHAR:
 760                                 case SQL_LONGVARCHAR:
 761                                 case SQL_BINARY:
 762                                 case SQL_VARBINARY:
 763                                 case SQL_LONGVARBINARY:
 764                                 case SQL_GUID:
 765 #define CHECK_SIZE(n) \
 766                                                 if (col->size < n) {      \
 767                                                         warn_length(col, n);  \
 768                                                 }                         \
 769                                                 break;
 770                                         switch (type) {
 771                                         case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
 772                                         case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
 773                                         case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
 774                                         case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
 775                                         case RQ_UINTEGER3:                /*    16777215 */
 776                                         case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
 777                                         case RQ_DATE:                     /*  2008-06-09 */
 778                                         case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
 779                                         case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
 780                                         case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
 781                                         case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
 782                                         case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
 783                                         case RQ_FLOAT:
 784                                         case RQ_CHAR:      CHECK_SIZE(size)
 785                                         }
 786 #undef CHECK_SIZE
 787                                         break;
 788                                 case SQL_TYPE_DATE:
 789                                         if (type != RQ_DATE) {
 790                                                 warn_type(col, type);
 791                                         }
 792                                         break;
 793                                 case SQL_TYPE_TIMESTAMP:
 794                                 case SQL_TIMESTAMP:
 795                                         if (type != RQ_DATE && type != RQ_DATETIME) {
 796                                                 warn_type(col, type);
 797                                         }
 798                                         break;
 799                                 case SQL_BIT:
 800                                         warn_length(col, size);
 801                                         break;
 802 #define WARN_TYPE_OR_LENGTH(n)  \
 803                                                 if (!ast_rq_is_int(type)) {  \
 804                                                         warn_type(col, type);    \
 805                                                 } else {                     \
 806                                                         warn_length(col, n);  \
 807                                                 }
 808                                 case SQL_TINYINT:
 809                                         if (type != RQ_UINTEGER1) {
 810                                                 WARN_TYPE_OR_LENGTH(size)
 811                                         }
 812                                         break;
 813                                 case SQL_C_STINYINT:
 814                                         if (type != RQ_INTEGER1) {
 815                                                 WARN_TYPE_OR_LENGTH(size)
 816                                         }
 817                                         break;
 818                                 case SQL_C_USHORT:
 819                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
 820                                                 WARN_TYPE_OR_LENGTH(size)
 821                                         }
 822                                         break;
 823                                 case SQL_SMALLINT:
 824                                 case SQL_C_SSHORT:
 825                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
 826                                                 WARN_TYPE_OR_LENGTH(size)
 827                                         }
 828                                         break;
 829                                 case SQL_C_ULONG:
 830                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
 831                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
 832                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
 833                                                 type != RQ_INTEGER4) {
 834                                                 WARN_TYPE_OR_LENGTH(size)
 835                                         }
 836                                         break;
 837                                 case SQL_INTEGER:
 838                                 case SQL_C_SLONG:
 839                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
 840                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
 841                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
 842                                                 type != RQ_UINTEGER4) {
 843                                                 WARN_TYPE_OR_LENGTH(size)
 844                                         }
 845                                         break;
 846                                 case SQL_C_UBIGINT:
 847                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
 848                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
 849                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
 850                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
 851                                                 type != RQ_INTEGER8) {
 852                                                 WARN_TYPE_OR_LENGTH(size)
 853                                         }
 854                                         break;
 855                                 case SQL_BIGINT:
 856                                 case SQL_C_SBIGINT:
 857                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
 858                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
 859                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
 860                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
 861                                                 type != RQ_UINTEGER8) {
 862                                                 WARN_TYPE_OR_LENGTH(size)
 863                                         }
 864                                         break;
 865 #undef WARN_TYPE_OR_LENGTH
 866                                 case SQL_NUMERIC:
 867                                 case SQL_DECIMAL:
 868                                 case SQL_FLOAT:
 869                                 case SQL_REAL:
 870                                 case SQL_DOUBLE:
 871                                         if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
 872                                                 warn_type(col, type);
 873                                         }
 874                                         break;
 875                                 default:
 876                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
 877                                 }
 878                                 break;
 879                         }
 880                 }
 881                 if (!col) {
 882                         ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
 883                 }
 884         }
 885         va_end(ap);
 886         AST_RWLIST_UNLOCK(&tableptr->columns);
 887         return 0;
 888 }
 889 #undef warn_length
 890 #undef warn_type
 891
 892 static struct ast_config_engine odbc_engine = {
 893         .name = "odbc",
 894         .load_func = config_odbc,
 895         .realtime_func = realtime_odbc,
 896         .realtime_multi_func = realtime_multi_odbc,
 897         .store_func = store_odbc,
 898         .destroy_func = destroy_odbc,
 899         .update_func = update_odbc,
 900         .require_func = require_odbc,
 901         .unload_func = ast_odbc_clear_cache,
 902 };
 903
 904 static int unload_module (void)
 905 {
 906         ast_config_engine_deregister(&odbc_engine);
 907
 908         ast_verb(1, "res_config_odbc unloaded.\n");
 909         return 0;
 910 }
 911
 912 static int load_module (void)
 913 {
 914         ast_config_engine_register(&odbc_engine);
 915         ast_verb(1, "res_config_odbc loaded.\n");
 916         return 0;
 917 }
 918
 919 static int reload_module(void)
 920 {
 921         return 0;
 922 }
 923
 924 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "Realtime ODBC configuration",
 925                 .load = load_module,
 926                 .unload = unload_module,
 927                 .reload = reload_module,
 928                 );