funcs/func_odbc.c

   1 /*
   2  * Asterisk -- An open source telephony toolkit.
   3  *
   4  * Copyright (c) 2005, 2006 Tilghman Lesher
   5  *
   6  * Tilghman Lesher <func_odbc__200508@the-tilghman.com>
   7  *
   8  * See http://www.asterisk.org for more information about
   9  * the Asterisk project. Please do not directly contact
  10  * any of the maintainers of this project for assistance;
  11  * the project provides a web site, mailing lists and IRC
  12  * channels for your use.
  13  *
  14  * This program is free software, distributed under the terms of
  15  * the GNU General Public License Version 2. See the LICENSE file
  16  * at the top of the source tree.
  17  */
  18
  19 /*!
  20  * \file
  21  *
  22  * \brief ODBC lookups
  23  *
  24  * \author Tilghman Lesher <func_odbc__200508@the-tilghman.com>
  25  *
  26  * \ingroup functions
  27  */
  28
  29 /*** MODULEINFO
  30         <depend>unixodbc_or_iodbc</depend>
  31         <depend>ltdl</depend>
  32         <depend>res_odbc</depend>
  33         <use>unixodbc</use>
  34         <use>iodbc</use>
  35  ***/
  36
  37 #include "asterisk.h"
  38
  39 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
  40
  41 #include "asterisk/module.h"
  42 #include "asterisk/file.h"
  43 #include "asterisk/channel.h"
  44 #include "asterisk/pbx.h"
  45 #include "asterisk/config.h"
  46 #include "asterisk/res_odbc.h"
  47 #include "asterisk/app.h"
  48
  49 static char *config = "func_odbc.conf";
  50
  51 enum {
  52         OPT_ESCAPECOMMAS =      (1 << 0),
  53         OPT_MULTIROW     =      (1 << 1),
  54 } odbc_option_flags;
  55
  56 struct acf_odbc_query {
  57         AST_RWLIST_ENTRY(acf_odbc_query) list;
  58         char readhandle[5][30];
  59         char writehandle[5][30];
  60         char sql_read[2048];
  61         char sql_write[2048];
  62         unsigned int flags;
  63         int rowlimit;
  64         struct ast_custom_function *acf;
  65 };
  66
  67 static void odbc_datastore_free(void *data);
  68
  69 struct ast_datastore_info odbc_info = {
  70         .type = "FUNC_ODBC",
  71         .destroy = odbc_datastore_free,
  72 };
  73
  74 /* For storing each result row */
  75 struct odbc_datastore_row {
  76         AST_LIST_ENTRY(odbc_datastore_row) list;
  77         char data[0];
  78 };
  79
  80 /* For storing each result set */
  81 struct odbc_datastore {
  82         AST_LIST_HEAD(, odbc_datastore_row);
  83         char names[0];
  84 };
  85
  86 AST_RWLIST_HEAD_STATIC(queries, acf_odbc_query);
  87
  88 static int resultcount = 0;
  89
  90 AST_THREADSTORAGE(coldata_buf);
  91 AST_THREADSTORAGE(colnames_buf);
  92
  93 static void odbc_datastore_free(void *data)
  94 {
  95         struct odbc_datastore *result = data;
  96         struct odbc_datastore_row *row;
  97         AST_LIST_LOCK(result);
  98         while ((row = AST_LIST_REMOVE_HEAD(result, list))) {
  99                 ast_free(row);
 100         }
 101         AST_LIST_UNLOCK(result);
 102         AST_LIST_HEAD_DESTROY(result);
 103         ast_free(result);
 104 }
 105
 106 static SQLHSTMT generic_execute(struct odbc_obj *obj, void *data)
 107 {
 108         int res;
 109         char *sql = data;
 110         SQLHSTMT stmt;
 111
 112         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
 113         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 114                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
 115                 return NULL;
 116         }
 117
 118         res = SQLExecDirect(stmt, (unsigned char *)sql, SQL_NTS);
 119         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 120                 ast_log(LOG_WARNING, "SQL Exec Direct failed![%s]\n", sql);
 121                 SQLCloseCursor(stmt);
 122                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 123                 return NULL;
 124         }
 125
 126         return stmt;
 127 }
 128
 129 /*
 130  * Master control routine
 131  */
 132 static int acf_odbc_write(struct ast_channel *chan, const char *cmd, char *s, const char *value)
 133 {
 134         struct odbc_obj *obj = NULL;
 135         struct acf_odbc_query *query;
 136         char *t, varname[15];
 137         int i, dsn, bogus_chan = 0;
 138         AST_DECLARE_APP_ARGS(values,
 139                 AST_APP_ARG(field)[100];
 140         );
 141         AST_DECLARE_APP_ARGS(args,
 142                 AST_APP_ARG(field)[100];
 143         );
 144         SQLHSTMT stmt = NULL;
 145         SQLLEN rows=0;
 146         struct ast_str *buf = ast_str_create(16);
 147
 148         if (!buf) {
 149                 return -1;
 150         }
 151
 152         AST_RWLIST_RDLOCK(&queries);
 153         AST_RWLIST_TRAVERSE(&queries, query, list) {
 154                 if (!strcmp(query->acf->name, cmd)) {
 155                         break;
 156                 }
 157         }
 158
 159         if (!query) {
 160                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
 161                 AST_RWLIST_UNLOCK(&queries);
 162                 ast_free(buf);
 163                 return -1;
 164         }
 165
 166         if (!chan) {
 167                 if ((chan = ast_channel_alloc(0, 0, "", "", "", "", "", 0, "Bogus/func_odbc")))
 168                         bogus_chan = 1;
 169         }
 170
 171         if (chan)
 172                 ast_autoservice_start(chan);
 173
 174         ast_str_make_space(&buf, strlen(query->sql_write) * 2);
 175
 176         /* Parse our arguments */
 177         t = value ? ast_strdupa(value) : "";
 178
 179         if (!s || !t) {
 180                 ast_log(LOG_ERROR, "Out of memory\n");
 181                 AST_RWLIST_UNLOCK(&queries);
 182                 if (chan)
 183                         ast_autoservice_stop(chan);
 184                 if (bogus_chan)
 185                         ast_channel_free(chan);
 186                 ast_free(buf);
 187                 return -1;
 188         }
 189
 190         AST_STANDARD_APP_ARGS(args, s);
 191         for (i = 0; i < args.argc; i++) {
 192                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
 193                 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
 194         }
 195
 196         /* Parse values, just like arguments */
 197         AST_STANDARD_APP_ARGS(values, t);
 198         for (i = 0; i < values.argc; i++) {
 199                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
 200                 pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
 201         }
 202
 203         /* Additionally set the value as a whole (but push an empty string if value is NULL) */
 204         pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
 205
 206         pbx_substitute_variables_helper(chan, query->sql_write, buf->str, buf->len - 1);
 207
 208         /* Restore prior values */
 209         for (i = 0; i < args.argc; i++) {
 210                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
 211                 pbx_builtin_setvar_helper(chan, varname, NULL);
 212         }
 213
 214         for (i = 0; i < values.argc; i++) {
 215                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
 216                 pbx_builtin_setvar_helper(chan, varname, NULL);
 217         }
 218         pbx_builtin_setvar_helper(chan, "VALUE", NULL);
 219
 220         AST_RWLIST_UNLOCK(&queries);
 221
 222         for (dsn = 0; dsn < 5; dsn++) {
 223                 if (!ast_strlen_zero(query->writehandle[dsn])) {
 224                         obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
 225                         if (obj)
 226                                 stmt = ast_odbc_direct_execute(obj, generic_execute, buf);
 227                 }
 228                 if (stmt)
 229                         break;
 230         }
 231
 232         if (stmt) {
 233                 /* Rows affected */
 234                 SQLRowCount(stmt, &rows);
 235         }
 236
 237         /* Output the affected rows, for all cases.  In the event of failure, we
 238          * flag this as -1 rows.  Note that this is different from 0 affected rows
 239          * which would be the case if we succeeded in our query, but the values did
 240          * not change. */
 241         snprintf(varname, sizeof(varname), "%d", (int)rows);
 242         pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
 243
 244         if (stmt) {
 245                 SQLCloseCursor(stmt);
 246                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 247         }
 248         if (obj)
 249                 ast_odbc_release_obj(obj);
 250
 251         if (chan)
 252                 ast_autoservice_stop(chan);
 253         if (bogus_chan)
 254                 ast_channel_free(chan);
 255         ast_free(buf);
 256
 257         return 0;
 258 }
 259
 260 static int acf_odbc_read(struct ast_channel *chan, const char *cmd, char *s, char *buf, size_t len)
 261 {
 262         struct odbc_obj *obj = NULL;
 263         struct acf_odbc_query *query;
 264         char varname[15], rowcount[12] = "-1";
 265         struct ast_str *colnames = ast_str_thread_get(&colnames_buf, 16);
 266         int res, x, y, buflen = 0, escapecommas, rowlimit = 1, dsn, bogus_chan = 0;
 267         AST_DECLARE_APP_ARGS(args,
 268                 AST_APP_ARG(field)[100];
 269         );
 270         SQLHSTMT stmt = NULL;
 271         SQLSMALLINT colcount=0;
 272         SQLLEN indicator;
 273         SQLSMALLINT collength;
 274         struct odbc_datastore *resultset = NULL;
 275         struct odbc_datastore_row *row = NULL;
 276         struct ast_str *sql = ast_str_create(16);
 277
 278         if (!sql) {
 279                 return -1;
 280         }
 281
 282         AST_RWLIST_RDLOCK(&queries);
 283         AST_RWLIST_TRAVERSE(&queries, query, list) {
 284                 if (!strcmp(query->acf->name, cmd)) {
 285                         break;
 286                 }
 287         }
 288
 289         if (!query) {
 290                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
 291                 AST_RWLIST_UNLOCK(&queries);
 292                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 293                 ast_free(sql);
 294                 return -1;
 295         }
 296
 297         if (!chan) {
 298                 if ((chan = ast_channel_alloc(0, 0, "", "", "", "", "", 0, "Bogus/func_odbc")))
 299                         bogus_chan = 1;
 300         }
 301
 302         if (chan)
 303                 ast_autoservice_start(chan);
 304
 305         AST_STANDARD_APP_ARGS(args, s);
 306         for (x = 0; x < args.argc; x++) {
 307                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
 308                 pbx_builtin_pushvar_helper(chan, varname, args.field[x]);
 309         }
 310
 311         ast_str_make_space(&sql, strlen(query->sql_read) * 2);
 312         pbx_substitute_variables_helper(chan, query->sql_read, sql->str, sql->len - 1);
 313
 314         /* Restore prior values */
 315         for (x = 0; x < args.argc; x++) {
 316                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
 317                 pbx_builtin_setvar_helper(chan, varname, NULL);
 318         }
 319
 320         /* Save these flags, so we can release the lock */
 321         escapecommas = ast_test_flag(query, OPT_ESCAPECOMMAS);
 322         if (ast_test_flag(query, OPT_MULTIROW)) {
 323                 resultset = ast_calloc(1, sizeof(*resultset));
 324                 AST_LIST_HEAD_INIT(resultset);
 325                 if (query->rowlimit)
 326                         rowlimit = query->rowlimit;
 327                 else
 328                         rowlimit = INT_MAX;
 329         }
 330         AST_RWLIST_UNLOCK(&queries);
 331
 332         for (dsn = 0; dsn < 5; dsn++) {
 333                 if (!ast_strlen_zero(query->readhandle[dsn])) {
 334                         obj = ast_odbc_request_obj(query->readhandle[dsn], 0);
 335                         if (obj)
 336                                 stmt = ast_odbc_direct_execute(obj, generic_execute, sql->str);
 337                 }
 338                 if (stmt)
 339                         break;
 340         }
 341
 342         if (!stmt) {
 343                 ast_log(LOG_ERROR, "Unable to execute query [%s]\n", sql->str);
 344                 if (obj)
 345                         ast_odbc_release_obj(obj);
 346                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 347                 if (chan)
 348                         ast_autoservice_stop(chan);
 349                 if (bogus_chan)
 350                         ast_channel_free(chan);
 351                 ast_free(sql);
 352                 return -1;
 353         }
 354
 355         res = SQLNumResultCols(stmt, &colcount);
 356         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 357                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql->str);
 358                 SQLCloseCursor(stmt);
 359                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 360                 ast_odbc_release_obj(obj);
 361                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 362                 if (chan)
 363                         ast_autoservice_stop(chan);
 364                 if (bogus_chan)
 365                         ast_channel_free(chan);
 366                 ast_free(sql);
 367                 return -1;
 368         }
 369
 370         res = SQLFetch(stmt);
 371         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 372                 int res1 = -1;
 373                 if (res == SQL_NO_DATA) {
 374                         ast_verb(4, "Found no rows [%s]\n", sql->str);
 375                         res1 = 0;
 376                         buf[0] = '\0';
 377                         ast_copy_string(rowcount, "0", sizeof(rowcount));
 378                 } else {
 379                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql->str);
 380                 }
 381                 SQLCloseCursor(stmt);
 382                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 383                 ast_odbc_release_obj(obj);
 384                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 385                 if (chan)
 386                         ast_autoservice_stop(chan);
 387                 if (bogus_chan)
 388                         ast_channel_free(chan);
 389                 ast_free(sql);
 390                 return res1;
 391         }
 392
 393         for (y = 0; y < rowlimit; y++) {
 394                 for (x = 0; x < colcount; x++) {
 395                         int i;
 396                         struct ast_str *coldata = ast_str_thread_get(&coldata_buf, 16);
 397
 398                         if (y == 0) {
 399                                 char colname[256];
 400                                 SQLULEN maxcol;
 401
 402                                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)colname, sizeof(colname), &collength, NULL, &maxcol, NULL, NULL);
 403                                 ast_debug(3, "Got collength of %d and maxcol of %d for column '%s' (offset %d)\n", (int)collength, (int)maxcol, colname, x);
 404                                 if (((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) || collength == 0) {
 405                                         snprintf(colname, sizeof(colname), "field%d", x);
 406                                 }
 407
 408                                 if (coldata->len < maxcol + 1) {
 409                                         ast_str_make_space(&coldata, maxcol + 1);
 410                                 }
 411
 412                                 if (colnames->used) {
 413                                         ast_str_append(&colnames, 0, ",");
 414                                 }
 415                                 ast_str_make_space(&colnames, strlen(colname) * 2 + 1 + colnames->used);
 416
 417                                 /* Copy data, encoding '\' and ',' for the argument parser */
 418                                 for (i = 0; i < sizeof(colname); i++) {
 419                                         if (escapecommas && (colname[i] == '\\' || colname[i] == ',')) {
 420                                                 colnames->str[colnames->used++] = '\\';
 421                                         }
 422                                         colnames->str[colnames->used++] = colname[i];
 423
 424                                         if (colname[i] == '\0') {
 425                                                 break;
 426                                         }
 427                                 }
 428
 429                                 if (resultset) {
 430                                         void *tmp = ast_realloc(resultset, sizeof(*resultset) + colnames->used + 1);
 431                                         if (!tmp) {
 432                                                 ast_log(LOG_ERROR, "No space for a new resultset?\n");
 433                                                 ast_free(resultset);
 434                                                 SQLCloseCursor(stmt);
 435                                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 436                                                 ast_odbc_release_obj(obj);
 437                                                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 438                                                 if (chan)
 439                                                         ast_autoservice_stop(chan);
 440                                                 if (bogus_chan)
 441                                                         ast_channel_free(chan);
 442                                                 ast_free(sql);
 443                                                 return -1;
 444                                         }
 445                                         resultset = tmp;
 446                                         strcpy((char *)resultset + sizeof(*resultset), colnames->str);
 447                                 }
 448                         }
 449
 450                         buflen = strlen(buf);
 451                         res = SQLGetData(stmt, x + 1, SQL_CHAR, coldata->str, coldata->len, &indicator);
 452                         if (indicator == SQL_NULL_DATA) {
 453                                 ast_debug(3, "Got NULL data\n");
 454                                 ast_str_reset(coldata);
 455                                 res = SQL_SUCCESS;
 456                         }
 457
 458                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 459                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql->str);
 460                                 y = -1;
 461                                 buf[0] = '\0';
 462                                 goto end_acf_read;
 463                         }
 464
 465                         ast_debug(2, "Got coldata of '%s'\n", coldata->str);
 466                         coldata->used = strlen(coldata->str);
 467
 468                         /* Copy data, encoding '\' and ',' for the argument parser */
 469                         for (i = 0; i < coldata->used; i++) {
 470                                 if (escapecommas && (coldata->str[i] == '\\' || coldata->str[i] == ',')) {
 471                                         buf[buflen++] = '\\';
 472                                 }
 473                                 buf[buflen++] = coldata->str[i];
 474
 475                                 if (buflen >= len - 2)
 476                                         break;
 477
 478                                 if (coldata->str[i] == '\0')
 479                                         break;
 480                         }
 481
 482                         buf[buflen++] = ',';
 483                         buf[buflen] = '\0';
 484                         ast_debug(2, "buf is now set to '%s'\n", buf);
 485                 }
 486                 /* Trim trailing comma */
 487                 buf[buflen - 1] = '\0';
 488                 ast_debug(2, "buf is now set to '%s'\n", buf);
 489
 490                 if (resultset) {
 491                         row = ast_calloc(1, sizeof(*row) + buflen);
 492                         if (!row) {
 493                                 ast_log(LOG_ERROR, "Unable to allocate space for more rows in this resultset.\n");
 494                                 goto end_acf_read;
 495                         }
 496                         strcpy((char *)row + sizeof(*row), buf);
 497                         AST_LIST_INSERT_TAIL(resultset, row, list);
 498
 499                         /* Get next row */
 500                         res = SQLFetch(stmt);
 501                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 502                                 if (res != SQL_NO_DATA)
 503                                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql->str);
 504                                 y++;
 505                                 break;
 506                         }
 507                 }
 508         }
 509
 510 end_acf_read:
 511         snprintf(rowcount, sizeof(rowcount), "%d", y);
 512         pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 513         pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", colnames->str);
 514         if (resultset) {
 515                 int uid;
 516                 struct ast_datastore *odbc_store;
 517                 uid = ast_atomic_fetchadd_int(&resultcount, +1) + 1;
 518                 snprintf(buf, len, "%d", uid);
 519                 odbc_store = ast_datastore_alloc(&odbc_info, buf);
 520                 if (!odbc_store) {
 521                         ast_log(LOG_ERROR, "Rows retrieved, but unable to store it in the channel.  Results fail.\n");
 522                         odbc_datastore_free(resultset);
 523                         SQLCloseCursor(stmt);
 524                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 525                         ast_odbc_release_obj(obj);
 526                         if (chan)
 527                                 ast_autoservice_stop(chan);
 528                         if (bogus_chan)
 529                                 ast_channel_free(chan);
 530                         ast_free(sql);
 531                         return -1;
 532                 }
 533                 odbc_store->data = resultset;
 534                 ast_channel_datastore_add(chan, odbc_store);
 535         }
 536         SQLCloseCursor(stmt);
 537         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 538         ast_odbc_release_obj(obj);
 539         if (chan)
 540                 ast_autoservice_stop(chan);
 541         if (bogus_chan)
 542                 ast_channel_free(chan);
 543         ast_free(sql);
 544         return 0;
 545 }
 546
 547 static int acf_escape(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
 548 {
 549         char *out = buf;
 550
 551         for (; *data && out - buf < len; data++) {
 552                 if (*data == '\'') {
 553                         *out = '\'';
 554                         out++;
 555                 }
 556                 *out++ = *data;
 557         }
 558         *out = '\0';
 559
 560         return 0;
 561 }
 562
 563 static struct ast_custom_function escape_function = {
 564         .name = "SQL_ESC",
 565         .synopsis = "Escapes single ticks for use in SQL statements",
 566         .syntax = "SQL_ESC(<string>)",
 567         .desc =
 568 "Used in SQL templates to escape data which may contain single ticks (') which\n"
 569 "are otherwise used to delimit data.  For example:\n"
 570 "SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'\n",
 571         .read = acf_escape,
 572         .write = NULL,
 573 };
 574
 575 static int acf_fetch(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
 576 {
 577         struct ast_datastore *store;
 578         struct odbc_datastore *resultset;
 579         struct odbc_datastore_row *row;
 580         store = ast_channel_datastore_find(chan, &odbc_info, data);
 581         if (!store) {
 582                 pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "FAILURE");
 583                 return -1;
 584         }
 585         resultset = store->data;
 586         AST_LIST_LOCK(resultset);
 587         row = AST_LIST_REMOVE_HEAD(resultset, list);
 588         AST_LIST_UNLOCK(resultset);
 589         if (!row) {
 590                 /* Cleanup datastore */
 591                 ast_channel_datastore_remove(chan, store);
 592                 ast_datastore_free(store);
 593                 pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "FAILURE");
 594                 return -1;
 595         }
 596         pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", resultset->names);
 597         ast_copy_string(buf, row->data, len);
 598         ast_free(row);
 599         pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "SUCCESS");
 600         return 0;
 601 }
 602
 603 static struct ast_custom_function fetch_function = {
 604         .name = "ODBC_FETCH",
 605         .synopsis = "Fetch a row from a multirow query",
 606         .syntax = "ODBC_FETCH(<result-id>)",
 607         .desc =
 608 "For queries which are marked as mode=multirow, the original query returns a\n"
 609 "result-id from which results may be fetched.  This function implements the\n"
 610 "actual fetch of the results.\n"
 611 "This function also sets ODBC_FETCH_STATUS to one of \"SUCCESS\" or \"FAILURE\",\n"
 612 "depending upon whether there were rows available or not.\n",
 613         .read = acf_fetch,
 614         .write = NULL,
 615 };
 616
 617 static char *app_odbcfinish = "ODBCFinish";
 618 static char *syn_odbcfinish = "Clear the resultset of a successful multirow query";
 619 static char *desc_odbcfinish =
 620 "ODBCFinish(<result-id>)\n"
 621 "  Clears any remaining rows of the specified resultset\n";
 622
 623
 624 static int exec_odbcfinish(struct ast_channel *chan, void *data)
 625 {
 626         struct ast_datastore *store = ast_channel_datastore_find(chan, &odbc_info, data);
 627         if (!store) /* Already freed; no big deal. */
 628                 return 0;
 629         ast_channel_datastore_remove(chan, store);
 630         ast_datastore_free(store);
 631         return 0;
 632 }
 633
 634 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
 635 {
 636         const char *tmp;
 637         int i;
 638
 639         if (!cfg || !catg) {
 640                 return EINVAL;
 641         }
 642
 643         *query = ast_calloc(1, sizeof(struct acf_odbc_query));
 644         if (! (*query))
 645                 return ENOMEM;
 646
 647         if (((tmp = ast_variable_retrieve(cfg, catg, "writehandle"))) || ((tmp = ast_variable_retrieve(cfg, catg, "dsn")))) {
 648                 char *tmp2 = ast_strdupa(tmp);
 649                 AST_DECLARE_APP_ARGS(writeconf,
 650                         AST_APP_ARG(dsn)[5];
 651                 );
 652                 AST_STANDARD_APP_ARGS(writeconf, tmp2);
 653                 for (i = 0; i < 5; i++) {
 654                         if (!ast_strlen_zero(writeconf.dsn[i]))
 655                                 ast_copy_string((*query)->writehandle[i], writeconf.dsn[i], sizeof((*query)->writehandle[i]));
 656                 }
 657         }
 658
 659         if ((tmp = ast_variable_retrieve(cfg, catg, "readhandle"))) {
 660                 char *tmp2 = ast_strdupa(tmp);
 661                 AST_DECLARE_APP_ARGS(readconf,
 662                         AST_APP_ARG(dsn)[5];
 663                 );
 664                 AST_STANDARD_APP_ARGS(readconf, tmp2);
 665                 for (i = 0; i < 5; i++) {
 666                         if (!ast_strlen_zero(readconf.dsn[i]))
 667                                 ast_copy_string((*query)->readhandle[i], readconf.dsn[i], sizeof((*query)->readhandle[i]));
 668                 }
 669         } else {
 670                 /* If no separate readhandle, then use the writehandle for reading */
 671                 for (i = 0; i < 5; i++) {
 672                         if (!ast_strlen_zero((*query)->writehandle[i]))
 673                                 ast_copy_string((*query)->readhandle[i], (*query)->writehandle[i], sizeof((*query)->readhandle[i]));
 674                 }
 675         }
 676
 677         if ((tmp = ast_variable_retrieve(cfg, catg, "readsql")))
 678                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
 679         else if ((tmp = ast_variable_retrieve(cfg, catg, "read"))) {
 680                 ast_log(LOG_WARNING, "Parameter 'read' is deprecated for category %s.  Please use 'readsql' instead.\n", catg);
 681                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
 682         }
 683
 684         if (!ast_strlen_zero((*query)->sql_read) && ast_strlen_zero((*query)->readhandle[0])) {
 685                 ast_free(*query);
 686                 *query = NULL;
 687                 ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for reading: %s\n", catg);
 688                 return EINVAL;
 689         }
 690
 691         if ((tmp = ast_variable_retrieve(cfg, catg, "writesql")))
 692                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
 693         else if ((tmp = ast_variable_retrieve(cfg, catg, "write"))) {
 694                 ast_log(LOG_WARNING, "Parameter 'write' is deprecated for category %s.  Please use 'writesql' instead.\n", catg);
 695                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
 696         }
 697
 698         if (!ast_strlen_zero((*query)->sql_write) && ast_strlen_zero((*query)->writehandle[0])) {
 699                 ast_free(*query);
 700                 *query = NULL;
 701                 ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for writing: %s\n", catg);
 702                 return EINVAL;
 703         }
 704
 705         /* Allow escaping of embedded commas in fields to be turned off */
 706         ast_set_flag((*query), OPT_ESCAPECOMMAS);
 707         if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
 708                 if (ast_false(tmp))
 709                         ast_clear_flag((*query), OPT_ESCAPECOMMAS);
 710         }
 711
 712         if ((tmp = ast_variable_retrieve(cfg, catg, "mode"))) {
 713                 if (strcasecmp(tmp, "multirow") == 0)
 714                         ast_set_flag((*query), OPT_MULTIROW);
 715                 if ((tmp = ast_variable_retrieve(cfg, catg, "rowlimit")))
 716                         sscanf(tmp, "%d", &((*query)->rowlimit));
 717         }
 718
 719         (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
 720         if (! (*query)->acf) {
 721                 ast_free(*query);
 722                 *query = NULL;
 723                 return ENOMEM;
 724         }
 725
 726         if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
 727                 asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg);
 728         } else {
 729                 asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg);
 730         }
 731
 732         if (!((*query)->acf->name)) {
 733                 ast_free((*query)->acf);
 734                 ast_free(*query);
 735                 *query = NULL;
 736                 return ENOMEM;
 737         }
 738
 739         if ((tmp = ast_variable_retrieve(cfg, catg, "syntax")) && !ast_strlen_zero(tmp)) {
 740                 asprintf((char **)&((*query)->acf->syntax), "%s(%s)", (*query)->acf->name, tmp);
 741         } else {
 742                 asprintf((char **)&((*query)->acf->syntax), "%s(<arg1>[...[,<argN>]])", (*query)->acf->name);
 743         }
 744
 745         if (!((*query)->acf->syntax)) {
 746                 ast_free((char *)(*query)->acf->name);
 747                 ast_free((*query)->acf);
 748                 ast_free(*query);
 749                 *query = NULL;
 750                 return ENOMEM;
 751         }
 752
 753         if ((tmp = ast_variable_retrieve(cfg, catg, "synopsis")) && !ast_strlen_zero(tmp)) {
 754                 (*query)->acf->synopsis = ast_strdup(tmp);
 755         } else {
 756                 (*query)->acf->synopsis = ast_strdup("Runs the referenced query with the specified arguments");
 757         }
 758
 759         if (!((*query)->acf->synopsis)) {
 760                 ast_free((char *)(*query)->acf->name);
 761                 ast_free((char *)(*query)->acf->syntax);
 762                 ast_free((*query)->acf);
 763                 ast_free(*query);
 764                 *query = NULL;
 765                 return ENOMEM;
 766         }
 767
 768         if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
 769                 asprintf((char **)&((*query)->acf->desc),
 770                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
 771                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
 772                                         "${ARG2}, ... ${ARGn}.  When setting the function, the values are provided\n"
 773                                         "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
 774                                         "\nRead:\n%s\n\nWrite:\n%s\n",
 775                                         (*query)->sql_read,
 776                                         (*query)->sql_write);
 777         } else if (!ast_strlen_zero((*query)->sql_read)) {
 778                 asprintf((char **)&((*query)->acf->desc),
 779                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
 780                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
 781                                         "${ARG2}, ... ${ARGn}.  This function may only be read, not set.\n\nSQL:\n%s\n",
 782                                         (*query)->sql_read);
 783         } else if (!ast_strlen_zero((*query)->sql_write)) {
 784                 asprintf((char **)&((*query)->acf->desc),
 785                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
 786                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
 787                                         "${ARG2}, ... ${ARGn}.  The values are provided either in whole as\n"
 788                                         "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
 789                                         "This function may only be set.\nSQL:\n%s\n",
 790                                         (*query)->sql_write);
 791         } else {
 792                 ast_free((char *)(*query)->acf->synopsis);
 793                 ast_free((char *)(*query)->acf->syntax);
 794                 ast_free((char *)(*query)->acf->name);
 795                 ast_free((*query)->acf);
 796                 ast_free(*query);
 797                 ast_log(LOG_WARNING, "Section %s was found, but there was no SQL to execute.  Ignoring.\n", catg);
 798                 return EINVAL;
 799         }
 800
 801         if (! ((*query)->acf->desc)) {
 802                 ast_free((char *)(*query)->acf->synopsis);
 803                 ast_free((char *)(*query)->acf->syntax);
 804                 ast_free((char *)(*query)->acf->name);
 805                 ast_free((*query)->acf);
 806                 ast_free(*query);
 807                 *query = NULL;
 808                 return ENOMEM;
 809         }
 810
 811         if (ast_strlen_zero((*query)->sql_read)) {
 812                 (*query)->acf->read = NULL;
 813         } else {
 814                 (*query)->acf->read = acf_odbc_read;
 815         }
 816
 817         if (ast_strlen_zero((*query)->sql_write)) {
 818                 (*query)->acf->write = NULL;
 819         } else {
 820                 (*query)->acf->write = acf_odbc_write;
 821         }
 822
 823         return 0;
 824 }
 825
 826 static int free_acf_query(struct acf_odbc_query *query)
 827 {
 828         if (query) {
 829                 if (query->acf) {
 830                         if (query->acf->name)
 831                                 ast_free((char *)query->acf->name);
 832                         if (query->acf->syntax)
 833                                 ast_free((char *)query->acf->syntax);
 834                         if (query->acf->synopsis)
 835                                 ast_free((char *)query->acf->synopsis);
 836                         if (query->acf->desc)
 837                                 ast_free((char *)query->acf->desc);
 838                         ast_free(query->acf);
 839                 }
 840                 ast_free(query);
 841         }
 842         return 0;
 843 }
 844
 845 static int load_module(void)
 846 {
 847         int res = 0;
 848         struct ast_config *cfg;
 849         char *catg;
 850         struct ast_flags config_flags = { 0 };
 851
 852         res |= ast_custom_function_register(&fetch_function);
 853         res |= ast_register_application(app_odbcfinish, exec_odbcfinish, syn_odbcfinish, desc_odbcfinish);
 854         AST_RWLIST_WRLOCK(&queries);
 855
 856         cfg = ast_config_load(config, config_flags);
 857         if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
 858                 ast_log(LOG_NOTICE, "Unable to load config for func_odbc: %s\n", config);
 859                 AST_RWLIST_UNLOCK(&queries);
 860                 return AST_MODULE_LOAD_DECLINE;
 861         }
 862
 863         for (catg = ast_category_browse(cfg, NULL);
 864              catg;
 865              catg = ast_category_browse(cfg, catg)) {
 866                 struct acf_odbc_query *query = NULL;
 867                 int err;
 868
 869                 if ((err = init_acf_query(cfg, catg, &query))) {
 870                         if (err == ENOMEM)
 871                                 ast_log(LOG_ERROR, "Out of memory\n");
 872                         else if (err == EINVAL)
 873                                 ast_log(LOG_ERROR, "Invalid parameters for category %s\n", catg);
 874                         else
 875                                 ast_log(LOG_ERROR, "%s (%d)\n", strerror(err), err);
 876                 } else {
 877                         AST_RWLIST_INSERT_HEAD(&queries, query, list);
 878                         ast_custom_function_register(query->acf);
 879                 }
 880         }
 881
 882         ast_config_destroy(cfg);
 883         res |= ast_custom_function_register(&escape_function);
 884
 885         AST_RWLIST_UNLOCK(&queries);
 886         return res;
 887 }
 888
 889 static int unload_module(void)
 890 {
 891         struct acf_odbc_query *query;
 892         int res = 0;
 893
 894         AST_RWLIST_WRLOCK(&queries);
 895         while (!AST_RWLIST_EMPTY(&queries)) {
 896                 query = AST_RWLIST_REMOVE_HEAD(&queries, list);
 897                 ast_custom_function_unregister(query->acf);
 898                 free_acf_query(query);
 899         }
 900
 901         res |= ast_custom_function_unregister(&escape_function);
 902         res |= ast_custom_function_unregister(&fetch_function);
 903         res |= ast_unregister_application(app_odbcfinish);
 904
 905         /* Allow any threads waiting for this lock to pass (avoids a race) */
 906         AST_RWLIST_UNLOCK(&queries);
 907         usleep(1);
 908         AST_RWLIST_WRLOCK(&queries);
 909
 910         AST_RWLIST_UNLOCK(&queries);
 911         return 0;
 912 }
 913
 914 static int reload(void)
 915 {
 916         int res = 0;
 917         struct ast_config *cfg;
 918         struct acf_odbc_query *oldquery;
 919         char *catg;
 920         struct ast_flags config_flags = { CONFIG_FLAG_FILEUNCHANGED };
 921
 922         cfg = ast_config_load(config, config_flags);
 923         if (cfg == CONFIG_STATUS_FILEUNCHANGED || cfg == CONFIG_STATUS_FILEINVALID)
 924                 return 0;
 925
 926         AST_RWLIST_WRLOCK(&queries);
 927
 928         while (!AST_RWLIST_EMPTY(&queries)) {
 929                 oldquery = AST_RWLIST_REMOVE_HEAD(&queries, list);
 930                 ast_custom_function_unregister(oldquery->acf);
 931                 free_acf_query(oldquery);
 932         }
 933
 934         if (!cfg) {
 935                 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
 936                 goto reload_out;
 937         }
 938
 939         for (catg = ast_category_browse(cfg, NULL);
 940              catg;
 941              catg = ast_category_browse(cfg, catg)) {
 942                 struct acf_odbc_query *query = NULL;
 943
 944                 if (init_acf_query(cfg, catg, &query)) {
 945                         ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
 946                 } else {
 947                         AST_RWLIST_INSERT_HEAD(&queries, query, list);
 948                         ast_custom_function_register(query->acf);
 949                 }
 950         }
 951
 952         ast_config_destroy(cfg);
 953 reload_out:
 954         AST_RWLIST_UNLOCK(&queries);
 955         return res;
 956 }
 957
 958 /* XXX need to revise usecount - set if query_lock is set */
 959
 960 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC lookups",
 961                 .load = load_module,
 962                 .unload = unload_module,
 963                 .reload = reload,
 964                );
 965