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