Thu Apr 28 2011 17:15:24

Asterisk developer's documentation


res_config_odbc.c

Go to the documentation of this file.
00001 /*
00002  * Asterisk -- An open source telephony toolkit.
00003  *
00004  * Copyright (C) 1999 - 2010, Digium, Inc.
00005  *
00006  * Mark Spencer <markster@digium.com>
00007  *
00008  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
00009  *
00010  * See http://www.asterisk.org for more information about
00011  * the Asterisk project. Please do not directly contact
00012  * any of the maintainers of this project for assistance;
00013  * the project provides a web site, mailing lists and IRC
00014  * channels for your use.
00015  *
00016  * This program is free software, distributed under the terms of
00017  * the GNU General Public License Version 2. See the LICENSE file
00018  * at the top of the source tree.
00019  */
00020 
00021 /*! \file
00022  *
00023  * \brief odbc+odbc plugin for portable configuration engine
00024  *
00025  * \author Mark Spencer <markster@digium.com>
00026  * \author Anthony Minessale II <anthmct@yahoo.com>
00027  *
00028  * \arg http://www.unixodbc.org
00029  */
00030 
00031 /*** MODULEINFO
00032    <depend>res_odbc</depend>
00033  ***/
00034 
00035 #include "asterisk.h"
00036 
00037 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 298481 $")
00038 
00039 #include "asterisk/file.h"
00040 #include "asterisk/channel.h"
00041 #include "asterisk/pbx.h"
00042 #include "asterisk/config.h"
00043 #include "asterisk/module.h"
00044 #include "asterisk/lock.h"
00045 #include "asterisk/res_odbc.h"
00046 #include "asterisk/utils.h"
00047 #include "asterisk/stringfields.h"
00048 
00049 AST_THREADSTORAGE(sql_buf);
00050 
00051 struct custom_prepare_struct {
00052    const char *sql;
00053    const char *extra;
00054    AST_DECLARE_STRING_FIELDS(
00055       AST_STRING_FIELD(encoding)[256];
00056    );
00057    va_list ap;
00058    unsigned long long skip;
00059 };
00060 
00061 static void decode_chunk(char *chunk)
00062 {
00063    for (; *chunk; chunk++) {
00064       if (*chunk == '^' && strchr("0123456789ABCDEF", chunk[1]) && strchr("0123456789ABCDEF", chunk[2])) {
00065          sscanf(chunk + 1, "%02hhX", chunk);
00066          memmove(chunk + 1, chunk + 3, strlen(chunk + 3) + 1);
00067       }
00068    }
00069 }
00070 
00071 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
00072 {
00073    int res, x = 1, count = 0;
00074    struct custom_prepare_struct *cps = data;
00075    const char *newparam, *newval;
00076    char encodebuf[1024];
00077    SQLHSTMT stmt;
00078    va_list ap;
00079 
00080    va_copy(ap, cps->ap);
00081 
00082    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00083    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00084       ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00085       return NULL;
00086    }
00087 
00088    ast_debug(1, "Skip: %lld; SQL: %s\n", cps->skip, cps->sql);
00089 
00090    res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
00091    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00092       ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
00093       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00094       return NULL;
00095    }
00096 
00097    while ((newparam = va_arg(ap, const char *))) {
00098       newval = va_arg(ap, const char *);
00099       if ((1LL << count++) & cps->skip) {
00100          ast_debug(1, "Skipping field '%s'='%s' (%llo/%llo)\n", newparam, newval, 1LL << (count - 1), cps->skip);
00101          continue;
00102       }
00103       ast_debug(1, "Parameter %d ('%s') = '%s'\n", x, newparam, newval);
00104       if (strchr(newval, ';') || strchr(newval, '^')) {
00105          char *eptr = encodebuf;
00106          const char *vptr = newval;
00107          for (; *vptr && eptr < encodebuf + sizeof(encodebuf); vptr++) {
00108             if (strchr("^;", *vptr)) {
00109                /* We use ^XX, instead of %XX because '%' is a special character in SQL */
00110                snprintf(eptr, encodebuf + sizeof(encodebuf) - eptr, "^%02hhX", *vptr);
00111                eptr += 3;
00112             } else {
00113                *eptr++ = *vptr;
00114             }
00115          }
00116          if (eptr < encodebuf + sizeof(encodebuf)) {
00117             *eptr = '\0';
00118          } else {
00119             encodebuf[sizeof(encodebuf) - 1] = '\0';
00120          }
00121          ast_string_field_set(cps, encoding[x], encodebuf);
00122          newval = cps->encoding[x];
00123       }
00124       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00125    }
00126    va_end(ap);
00127 
00128    if (!ast_strlen_zero(cps->extra))
00129       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
00130    return stmt;
00131 }
00132 
00133 /*!
00134  * \brief Excute an SQL query and return ast_variable list
00135  * \param database
00136  * \param table
00137  * \param ap list containing one or more field/operator/value set.
00138  *
00139  * Select database and preform query on table, prepare the sql statement
00140  * Sub-in the values to the prepared statement and execute it. Return results
00141  * as a ast_variable list.
00142  *
00143  * \retval var on success
00144  * \retval NULL on failure
00145 */
00146 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
00147 {
00148    struct odbc_obj *obj;
00149    SQLHSTMT stmt;
00150    char sql[1024];
00151    char coltitle[256];
00152    char rowdata[2048];
00153    char *op;
00154    const char *newparam, *newval;
00155    char *stringp;
00156    char *chunk;
00157    SQLSMALLINT collen;
00158    int res;
00159    int x;
00160    struct ast_variable *var=NULL, *prev=NULL;
00161    SQLULEN colsize;
00162    SQLSMALLINT colcount=0;
00163    SQLSMALLINT datatype;
00164    SQLSMALLINT decimaldigits;
00165    SQLSMALLINT nullable;
00166    SQLLEN indicator;
00167    va_list aq;
00168    struct custom_prepare_struct cps = { .sql = sql };
00169 
00170    if (ast_string_field_init(&cps, 256)) {
00171       return NULL;
00172    }
00173    va_copy(cps.ap, ap);
00174    va_copy(aq, ap);
00175 
00176    if (!table) {
00177       ast_string_field_free_memory(&cps);
00178       return NULL;
00179    }
00180 
00181    obj = ast_odbc_request_obj(database, 0);
00182 
00183    if (!obj) {
00184       ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
00185       ast_string_field_free_memory(&cps);
00186       return NULL;
00187    }
00188 
00189    newparam = va_arg(aq, const char *);
00190    if (!newparam) {
00191       ast_odbc_release_obj(obj);
00192       ast_string_field_free_memory(&cps);
00193       return NULL;
00194    }
00195    newval = va_arg(aq, const char *);
00196    op = !strchr(newparam, ' ') ? " =" : "";
00197    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00198       strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00199    while((newparam = va_arg(aq, const char *))) {
00200       op = !strchr(newparam, ' ') ? " =" : "";
00201       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00202          strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00203       newval = va_arg(aq, const char *);
00204    }
00205    va_end(aq);
00206 
00207    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00208 
00209    if (!stmt) {
00210       ast_odbc_release_obj(obj);
00211       ast_string_field_free_memory(&cps);
00212       return NULL;
00213    }
00214 
00215    res = SQLNumResultCols(stmt, &colcount);
00216    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00217       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00218       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00219       ast_odbc_release_obj(obj);
00220       ast_string_field_free_memory(&cps);
00221       return NULL;
00222    }
00223 
00224    res = SQLFetch(stmt);
00225    if (res == SQL_NO_DATA) {
00226       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00227       ast_odbc_release_obj(obj);
00228       ast_string_field_free_memory(&cps);
00229       return NULL;
00230    }
00231    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00232       ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00233       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00234       ast_odbc_release_obj(obj);
00235       ast_string_field_free_memory(&cps);
00236       return NULL;
00237    }
00238    for (x = 0; x < colcount; x++) {
00239       rowdata[0] = '\0';
00240       collen = sizeof(coltitle);
00241       res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00242                &datatype, &colsize, &decimaldigits, &nullable);
00243       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00244          ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00245          if (var)
00246             ast_variables_destroy(var);
00247          ast_odbc_release_obj(obj);
00248          ast_string_field_free_memory(&cps);
00249          return NULL;
00250       }
00251 
00252       indicator = 0;
00253       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00254       if (indicator == SQL_NULL_DATA)
00255          rowdata[0] = '\0';
00256       else if (ast_strlen_zero(rowdata)) {
00257          /* Because we encode the empty string for a NULL, we will encode
00258           * actual empty strings as a string containing a single whitespace. */
00259          ast_copy_string(rowdata, " ", sizeof(rowdata));
00260       }
00261 
00262       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00263          ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00264          if (var)
00265             ast_variables_destroy(var);
00266          ast_odbc_release_obj(obj);
00267          return NULL;
00268       }
00269       stringp = rowdata;
00270       while (stringp) {
00271          chunk = strsep(&stringp, ";");
00272          if (!ast_strlen_zero(ast_strip(chunk))) {
00273             if (strchr(chunk, '^')) {
00274                decode_chunk(chunk);
00275             }
00276             if (prev) {
00277                prev->next = ast_variable_new(coltitle, chunk, "");
00278                if (prev->next) {
00279                   prev = prev->next;
00280                }
00281             } else {
00282                prev = var = ast_variable_new(coltitle, chunk, "");
00283             }
00284          }
00285       }
00286    }
00287 
00288 
00289    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00290    ast_odbc_release_obj(obj);
00291    ast_string_field_free_memory(&cps);
00292    return var;
00293 }
00294 
00295 /*!
00296  * \brief Excute an Select query and return ast_config list
00297  * \param database
00298  * \param table
00299  * \param ap list containing one or more field/operator/value set.
00300  *
00301  * Select database and preform query on table, prepare the sql statement
00302  * Sub-in the values to the prepared statement and execute it. 
00303  * Execute this prepared query against several ODBC connected databases.
00304  * Return results as an ast_config variable.
00305  *
00306  * \retval var on success
00307  * \retval NULL on failure
00308 */
00309 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
00310 {
00311    struct odbc_obj *obj;
00312    SQLHSTMT stmt;
00313    char sql[1024];
00314    char coltitle[256];
00315    char rowdata[2048];
00316    const char *initfield=NULL;
00317    char *op;
00318    const char *newparam, *newval;
00319    char *stringp;
00320    char *chunk;
00321    SQLSMALLINT collen;
00322    int res;
00323    int x;
00324    struct ast_variable *var=NULL;
00325    struct ast_config *cfg=NULL;
00326    struct ast_category *cat=NULL;
00327    SQLULEN colsize;
00328    SQLSMALLINT colcount=0;
00329    SQLSMALLINT datatype;
00330    SQLSMALLINT decimaldigits;
00331    SQLSMALLINT nullable;
00332    SQLLEN indicator;
00333    struct custom_prepare_struct cps = { .sql = sql };
00334    va_list aq;
00335 
00336    if (!table || ast_string_field_init(&cps, 256)) {
00337       return NULL;
00338    }
00339    va_copy(cps.ap, ap);
00340    va_copy(aq, ap);
00341 
00342 
00343    obj = ast_odbc_request_obj(database, 0);
00344    if (!obj) {
00345       ast_string_field_free_memory(&cps);
00346       return NULL;
00347    }
00348 
00349    newparam = va_arg(aq, const char *);
00350    if (!newparam)  {
00351       ast_odbc_release_obj(obj);
00352       ast_string_field_free_memory(&cps);
00353       return NULL;
00354    }
00355    initfield = ast_strdupa(newparam);
00356    if ((op = strchr(initfield, ' '))) 
00357       *op = '\0';
00358    newval = va_arg(aq, const char *);
00359    op = !strchr(newparam, ' ') ? " =" : "";
00360    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00361       strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00362    while((newparam = va_arg(aq, const char *))) {
00363       op = !strchr(newparam, ' ') ? " =" : "";
00364       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00365          strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00366       newval = va_arg(aq, const char *);
00367    }
00368    if (initfield)
00369       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00370    va_end(aq);
00371 
00372    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00373 
00374    if (!stmt) {
00375       ast_odbc_release_obj(obj);
00376       ast_string_field_free_memory(&cps);
00377       return NULL;
00378    }
00379 
00380    res = SQLNumResultCols(stmt, &colcount);
00381    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00382       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00383       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00384       ast_odbc_release_obj(obj);
00385       ast_string_field_free_memory(&cps);
00386       return NULL;
00387    }
00388 
00389    cfg = ast_config_new();
00390    if (!cfg) {
00391       ast_log(LOG_WARNING, "Out of memory!\n");
00392       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00393       ast_odbc_release_obj(obj);
00394       ast_string_field_free_memory(&cps);
00395       return NULL;
00396    }
00397 
00398    while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
00399       var = NULL;
00400       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00401          ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00402          continue;
00403       }
00404       cat = ast_category_new("","",99999);
00405       if (!cat) {
00406          ast_log(LOG_WARNING, "Out of memory!\n");
00407          continue;
00408       }
00409       for (x=0;x<colcount;x++) {
00410          rowdata[0] = '\0';
00411          collen = sizeof(coltitle);
00412          res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00413                   &datatype, &colsize, &decimaldigits, &nullable);
00414          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00415             ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00416             ast_category_destroy(cat);
00417             continue;
00418          }
00419 
00420          indicator = 0;
00421          res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00422          if (indicator == SQL_NULL_DATA)
00423             continue;
00424 
00425          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00426             ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00427             ast_category_destroy(cat);
00428             continue;
00429          }
00430          stringp = rowdata;
00431          while (stringp) {
00432             chunk = strsep(&stringp, ";");
00433             if (!ast_strlen_zero(ast_strip(chunk))) {
00434                if (strchr(chunk, '^')) {
00435                   decode_chunk(chunk);
00436                }
00437                if (initfield && !strcmp(initfield, coltitle)) {
00438                   ast_category_rename(cat, chunk);
00439                }
00440                var = ast_variable_new(coltitle, chunk, "");
00441                ast_variable_append(cat, var);
00442             }
00443          }
00444       }
00445       ast_category_append(cfg, cat);
00446    }
00447 
00448    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00449    ast_odbc_release_obj(obj);
00450    ast_string_field_free_memory(&cps);
00451    return cfg;
00452 }
00453 
00454 /*!
00455  * \brief Excute an UPDATE query
00456  * \param database
00457  * \param table
00458  * \param keyfield where clause field
00459  * \param lookup value of field for where clause
00460  * \param ap list containing one or more field/value set(s).
00461  *
00462  * Update a database table, prepare the sql statement using keyfield and lookup
00463  * control the number of records to change. All values to be changed are stored in ap list.
00464  * Sub-in the values to the prepared statement and execute it.
00465  *
00466  * \retval number of rows affected
00467  * \retval -1 on failure
00468 */
00469 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00470 {
00471    struct odbc_obj *obj;
00472    SQLHSTMT stmt;
00473    char sql[256];
00474    SQLLEN rowcount=0;
00475    const char *newparam, *newval;
00476    int res, count = 1;
00477    va_list aq;
00478    struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00479    struct odbc_cache_tables *tableptr;
00480    struct odbc_cache_columns *column;
00481 
00482    if (!table) {
00483       return -1;
00484    }
00485 
00486    va_copy(cps.ap, ap);
00487    va_copy(aq, ap);
00488 
00489    if (ast_string_field_init(&cps, 256)) {
00490       return -1;
00491    }
00492 
00493    tableptr = ast_odbc_find_table(database, table);
00494    if (!(obj = ast_odbc_request_obj(database, 0))) {
00495       ast_odbc_release_table(tableptr);
00496       ast_string_field_free_memory(&cps);
00497       return -1;
00498    }
00499 
00500    newparam = va_arg(aq, const char *);
00501    if (!newparam)  {
00502       ast_odbc_release_obj(obj);
00503       ast_odbc_release_table(tableptr);
00504       ast_string_field_free_memory(&cps);
00505       return -1;
00506    }
00507    newval = va_arg(aq, const char *);
00508 
00509    if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
00510       ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", newparam, table, database);
00511    }
00512 
00513    snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
00514    while((newparam = va_arg(aq, const char *))) {
00515       newval = va_arg(aq, const char *);
00516       if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
00517          snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
00518       } else { /* the column does not exist in the table */
00519          cps.skip |= (1LL << count);
00520       }
00521       count++;
00522    }
00523    va_end(aq);
00524    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
00525    ast_odbc_release_table(tableptr);
00526 
00527    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00528 
00529    if (!stmt) {
00530       ast_odbc_release_obj(obj);
00531       ast_string_field_free_memory(&cps);
00532       return -1;
00533    }
00534 
00535    res = SQLRowCount(stmt, &rowcount);
00536    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00537    ast_odbc_release_obj(obj);
00538    ast_string_field_free_memory(&cps);
00539 
00540    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00541       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00542       return -1;
00543    }
00544 
00545    if (rowcount >= 0) {
00546       return (int) rowcount;
00547    }
00548 
00549    return -1;
00550 }
00551 
00552 struct update2_prepare_struct {
00553    const char *database;
00554    const char *table;
00555    va_list ap;
00556 };
00557 
00558 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
00559 {
00560    int res, x = 1, first = 1;
00561    struct update2_prepare_struct *ups = data;
00562    const char *newparam, *newval;
00563    struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
00564    SQLHSTMT stmt;
00565    va_list ap;
00566    struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
00567    struct odbc_cache_columns *column;
00568 
00569    if (!sql) {
00570       if (tableptr) {
00571          ast_odbc_release_table(tableptr);
00572       }
00573       return NULL;
00574    }
00575 
00576    if (!tableptr) {
00577       ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'.  Update will fail!\n", ups->table, ups->database);
00578       return NULL;
00579    }
00580 
00581    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00582    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00583       ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00584       ast_odbc_release_table(tableptr);
00585       return NULL;
00586    }
00587 
00588    ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
00589 
00590    /* Start by finding the second set of parameters */
00591    va_copy(ap, ups->ap);
00592 
00593    while ((newparam = va_arg(ap, const char *))) {
00594       newval = va_arg(ap, const char *);
00595    }
00596 
00597    while ((newparam = va_arg(ap, const char *))) {
00598       newval = va_arg(ap, const char *);
00599       if ((column = ast_odbc_find_column(tableptr, newparam))) {
00600          ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", newparam);
00601          SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00602          first = 0;
00603       } else {
00604          ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", newparam, ups->table, ups->database);
00605       }
00606    }
00607    va_end(ap);
00608 
00609    /* Restart search, because we need to add the search parameters */
00610    va_copy(ap, ups->ap);
00611    ast_str_append(&sql, 0, "WHERE");
00612    first = 1;
00613 
00614    while ((newparam = va_arg(ap, const char *))) {
00615       newval = va_arg(ap, const char *);
00616       if (!(column = ast_odbc_find_column(tableptr, newparam))) {
00617          ast_log(LOG_ERROR, "One or more of the criteria columns '%s' on '%s@%s' for this update does not exist!\n", newparam, ups->table, ups->database);
00618          ast_odbc_release_table(tableptr);
00619          SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00620          return NULL;
00621       }
00622       ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", newparam);
00623       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00624       first = 0;
00625    }
00626    va_end(ap);
00627 
00628    /* Done with the table metadata */
00629    ast_odbc_release_table(tableptr);
00630 
00631    res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
00632    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00633       ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
00634       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00635       return NULL;
00636    }
00637 
00638    return stmt;
00639 }
00640 
00641 /*!
00642  * \brief Execute an UPDATE query
00643  * \param database
00644  * \param table
00645  * \param ap list containing one or more field/value set(s).
00646  *
00647  * Update a database table, preparing the sql statement from a list of
00648  * key/value pairs specified in ap.  The lookup pairs are specified first
00649  * and are separated from the update pairs by a sentinel value.
00650  * Sub-in the values to the prepared statement and execute it.
00651  *
00652  * \retval number of rows affected
00653  * \retval -1 on failure
00654 */
00655 static int update2_odbc(const char *database, const char *table, va_list ap)
00656 {
00657    struct odbc_obj *obj;
00658    SQLHSTMT stmt;
00659    struct update2_prepare_struct ups = { .database = database, .table = table, };
00660    struct ast_str *sql;
00661    int res;
00662    SQLLEN rowcount = 0;
00663 
00664    va_copy(ups.ap, ap);
00665 
00666    if (!(obj = ast_odbc_request_obj(database, 0))) {
00667       return -1;
00668    }
00669 
00670    if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
00671       ast_odbc_release_obj(obj);
00672       return -1;
00673    }
00674 
00675    res = SQLRowCount(stmt, &rowcount);
00676    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00677    ast_odbc_release_obj(obj);
00678 
00679    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00680       /* Since only a single thread can access this memory, we can retrieve what would otherwise be lost. */
00681       sql = ast_str_thread_get(&sql_buf, 16);
00682       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
00683       return -1;
00684    }
00685 
00686    if (rowcount >= 0) {
00687       return (int)rowcount;
00688    }
00689 
00690    return -1;
00691 }
00692 
00693 /*!
00694  * \brief Excute an INSERT query
00695  * \param database
00696  * \param table
00697  * \param ap list containing one or more field/value set(s)
00698  *
00699  * Insert a new record into database table, prepare the sql statement.
00700  * All values to be changed are stored in ap list.
00701  * Sub-in the values to the prepared statement and execute it.
00702  *
00703  * \retval number of rows affected
00704  * \retval -1 on failure
00705 */
00706 static int store_odbc(const char *database, const char *table, va_list ap)
00707 {
00708    struct odbc_obj *obj;
00709    SQLHSTMT stmt;
00710    char sql[256];
00711    char keys[256];
00712    char vals[256];
00713    SQLLEN rowcount=0;
00714    const char *newparam, *newval;
00715    int res;
00716    va_list aq;
00717    struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
00718 
00719    va_copy(cps.ap, ap);
00720    va_copy(aq, ap);
00721    
00722    if (!table)
00723       return -1;
00724 
00725    obj = ast_odbc_request_obj(database, 0);
00726    if (!obj)
00727       return -1;
00728 
00729    newparam = va_arg(aq, const char *);
00730    if (!newparam)  {
00731       ast_odbc_release_obj(obj);
00732       return -1;
00733    }
00734    newval = va_arg(aq, const char *);
00735    snprintf(keys, sizeof(keys), "%s", newparam);
00736    ast_copy_string(vals, "?", sizeof(vals));
00737    while ((newparam = va_arg(aq, const char *))) {
00738       snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
00739       snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
00740       newval = va_arg(aq, const char *);
00741    }
00742    va_end(aq);
00743    snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
00744 
00745    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00746 
00747    if (!stmt) {
00748       ast_odbc_release_obj(obj);
00749       return -1;
00750    }
00751 
00752    res = SQLRowCount(stmt, &rowcount);
00753    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00754    ast_odbc_release_obj(obj);
00755 
00756    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00757       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00758       return -1;
00759    }
00760 
00761    if (rowcount >= 0)
00762       return (int)rowcount;
00763 
00764    return -1;
00765 }
00766 
00767 /*!
00768  * \brief Excute an DELETE query
00769  * \param database
00770  * \param table
00771  * \param keyfield where clause field
00772  * \param lookup value of field for where clause
00773  * \param ap list containing one or more field/value set(s)
00774  *
00775  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
00776  * control the number of records to change. Additional params to match rows are stored in ap list.
00777  * Sub-in the values to the prepared statement and execute it.
00778  *
00779  * \retval number of rows affected
00780  * \retval -1 on failure
00781 */
00782 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00783 {
00784    struct odbc_obj *obj;
00785    SQLHSTMT stmt;
00786    char sql[256];
00787    SQLLEN rowcount=0;
00788    const char *newparam, *newval;
00789    int res;
00790    va_list aq;
00791    struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00792 
00793    va_copy(cps.ap, ap);
00794    va_copy(aq, ap);
00795    
00796    if (!table)
00797       return -1;
00798 
00799    obj = ast_odbc_request_obj(database, 0);
00800    if (!obj)
00801       return -1;
00802 
00803    snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
00804    while((newparam = va_arg(aq, const char *))) {
00805       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
00806       newval = va_arg(aq, const char *);
00807    }
00808    va_end(aq);
00809    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
00810 
00811    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00812 
00813    if (!stmt) {
00814       ast_odbc_release_obj(obj);
00815       return -1;
00816    }
00817 
00818    res = SQLRowCount(stmt, &rowcount);
00819    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00820    ast_odbc_release_obj(obj);
00821 
00822    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00823       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00824       return -1;
00825    }
00826 
00827    if (rowcount >= 0)
00828       return (int)rowcount;
00829 
00830    return -1;
00831 }
00832 
00833 
00834 struct config_odbc_obj {
00835    char *sql;
00836    unsigned long cat_metric;
00837    char category[128];
00838    char var_name[128];
00839    char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
00840    SQLLEN err;
00841 };
00842 
00843 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
00844 {
00845    struct config_odbc_obj *q = data;
00846    SQLHSTMT sth;
00847    int res;
00848 
00849    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
00850    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00851       ast_verb(4, "Failure in AllocStatement %d\n", res);
00852       return NULL;
00853    }
00854 
00855    res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
00856    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00857       ast_verb(4, "Error in PREPARE %d\n", res);
00858       SQLFreeHandle(SQL_HANDLE_STMT, sth);
00859       return NULL;
00860    }
00861 
00862    SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
00863    SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
00864    SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
00865    SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
00866 
00867    return sth;
00868 }
00869 
00870 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)
00871 {
00872    struct ast_variable *new_v;
00873    struct ast_category *cur_cat;
00874    int res = 0;
00875    struct odbc_obj *obj;
00876    char sqlbuf[1024] = "";
00877    char *sql = sqlbuf;
00878    size_t sqlleft = sizeof(sqlbuf);
00879    unsigned int last_cat_metric = 0;
00880    SQLSMALLINT rowcount = 0;
00881    SQLHSTMT stmt;
00882    char last[128] = "";
00883    struct config_odbc_obj q;
00884    struct ast_flags loader_flags = { 0 };
00885 
00886    memset(&q, 0, sizeof(q));
00887 
00888    if (!file || !strcmp (file, "res_config_odbc.conf"))
00889       return NULL;      /* cant configure myself with myself ! */
00890 
00891    obj = ast_odbc_request_obj(database, 0);
00892    if (!obj)
00893       return NULL;
00894 
00895    ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
00896    ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
00897    ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00898    q.sql = sqlbuf;
00899 
00900    stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
00901 
00902    if (!stmt) {
00903       ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
00904       ast_odbc_release_obj(obj);
00905       return NULL;
00906    }
00907 
00908    res = SQLNumResultCols(stmt, &rowcount);
00909 
00910    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00911       ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
00912       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00913       ast_odbc_release_obj(obj);
00914       return NULL;
00915    }
00916 
00917    if (!rowcount) {
00918       ast_log(LOG_NOTICE, "found nothing\n");
00919       ast_odbc_release_obj(obj);
00920       return cfg;
00921    }
00922 
00923    cur_cat = ast_config_get_current_category(cfg);
00924 
00925    while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
00926       if (!strcmp (q.var_name, "#include")) {
00927          if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
00928             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00929             ast_odbc_release_obj(obj);
00930             return NULL;
00931          }
00932          continue;
00933       } 
00934       if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
00935          cur_cat = ast_category_new(q.category, "", 99999);
00936          if (!cur_cat) {
00937             ast_log(LOG_WARNING, "Out of memory!\n");
00938             break;
00939          }
00940          strcpy(last, q.category);
00941          last_cat_metric   = q.cat_metric;
00942          ast_category_append(cfg, cur_cat);
00943       }
00944 
00945       new_v = ast_variable_new(q.var_name, q.var_val, "");
00946       ast_variable_append(cur_cat, new_v);
00947    }
00948 
00949    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00950    ast_odbc_release_obj(obj);
00951    return cfg;
00952 }
00953 
00954 #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)
00955 #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)
00956 
00957 static int require_odbc(const char *database, const char *table, va_list ap)
00958 {
00959    struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
00960    struct odbc_cache_columns *col;
00961    char *elm;
00962    int type, size;
00963 
00964    if (!tableptr) {
00965       return -1;
00966    }
00967 
00968    while ((elm = va_arg(ap, char *))) {
00969       type = va_arg(ap, require_type);
00970       size = va_arg(ap, int);
00971       /* Check if the field matches the criteria */
00972       AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
00973          if (strcmp(col->name, elm) == 0) {
00974             /* Type check, first.  Some fields are more particular than others */
00975             switch (col->type) {
00976             case SQL_CHAR:
00977             case SQL_VARCHAR:
00978             case SQL_LONGVARCHAR:
00979 #ifdef HAVE_ODBC_WCHAR
00980             case SQL_WCHAR:
00981             case SQL_WVARCHAR:
00982             case SQL_WLONGVARCHAR:
00983 #endif
00984             case SQL_BINARY:
00985             case SQL_VARBINARY:
00986             case SQL_LONGVARBINARY:
00987             case SQL_GUID:
00988 #define CHECK_SIZE(n) \
00989                   if (col->size < n) {      \
00990                      warn_length(col, n);  \
00991                   }                         \
00992                   break;
00993                switch (type) {
00994                case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
00995                case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
00996                case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
00997                case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
00998                case RQ_UINTEGER3:                /*    16777215 */
00999                case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
01000                case RQ_DATE:                     /*  2008-06-09 */
01001                case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
01002                case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
01003                case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
01004                case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
01005                case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
01006                case RQ_FLOAT:
01007                case RQ_CHAR:      CHECK_SIZE(size)
01008                }
01009 #undef CHECK_SIZE
01010                break;
01011             case SQL_TYPE_DATE:
01012                if (type != RQ_DATE) {
01013                   warn_type(col, type);
01014                }
01015                break;
01016             case SQL_TYPE_TIMESTAMP:
01017             case SQL_TIMESTAMP:
01018                if (type != RQ_DATE && type != RQ_DATETIME) {
01019                   warn_type(col, type);
01020                }
01021                break;
01022             case SQL_BIT:
01023                warn_length(col, size);
01024                break;
01025 #define WARN_TYPE_OR_LENGTH(n)   \
01026                   if (!ast_rq_is_int(type)) {  \
01027                      warn_type(col, type);    \
01028                   } else {                     \
01029                      warn_length(col, n);  \
01030                   }
01031             case SQL_TINYINT:
01032                if (type != RQ_UINTEGER1) {
01033                   WARN_TYPE_OR_LENGTH(size)
01034                }
01035                break;
01036             case SQL_C_STINYINT:
01037                if (type != RQ_INTEGER1) {
01038                   WARN_TYPE_OR_LENGTH(size)
01039                }
01040                break;
01041             case SQL_C_USHORT:
01042                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
01043                   WARN_TYPE_OR_LENGTH(size)
01044                }
01045                break;
01046             case SQL_SMALLINT:
01047             case SQL_C_SSHORT:
01048                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
01049                   WARN_TYPE_OR_LENGTH(size)
01050                }
01051                break;
01052             case SQL_C_ULONG:
01053                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01054                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01055                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01056                   type != RQ_INTEGER4) {
01057                   WARN_TYPE_OR_LENGTH(size)
01058                }
01059                break;
01060             case SQL_INTEGER:
01061             case SQL_C_SLONG:
01062                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01063                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01064                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01065                   type != RQ_INTEGER4) {
01066                   WARN_TYPE_OR_LENGTH(size)
01067                }
01068                break;
01069             case SQL_C_UBIGINT:
01070                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01071                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01072                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01073                   type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01074                   type != RQ_INTEGER8) {
01075                   WARN_TYPE_OR_LENGTH(size)
01076                }
01077                break;
01078             case SQL_BIGINT:
01079             case SQL_C_SBIGINT:
01080                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01081                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01082                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01083                   type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01084                   type != RQ_INTEGER8) {
01085                   WARN_TYPE_OR_LENGTH(size)
01086                }
01087                break;
01088 #undef WARN_TYPE_OR_LENGTH
01089             case SQL_NUMERIC:
01090             case SQL_DECIMAL:
01091             case SQL_FLOAT:
01092             case SQL_REAL:
01093             case SQL_DOUBLE:
01094                if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
01095                   warn_type(col, type);
01096                }
01097                break;
01098             default:
01099                ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
01100             }
01101             break;
01102          }
01103       }
01104       if (!col) {
01105          ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
01106       }
01107    }
01108    va_end(ap);
01109    AST_RWLIST_UNLOCK(&tableptr->columns);
01110    return 0;
01111 }
01112 #undef warn_length
01113 #undef warn_type
01114 
01115 static struct ast_config_engine odbc_engine = {
01116    .name = "odbc",
01117    .load_func = config_odbc,
01118    .realtime_func = realtime_odbc,
01119    .realtime_multi_func = realtime_multi_odbc,
01120    .store_func = store_odbc,
01121    .destroy_func = destroy_odbc,
01122    .update_func = update_odbc,
01123    .update2_func = update2_odbc,
01124    .require_func = require_odbc,
01125    .unload_func = ast_odbc_clear_cache,
01126 };
01127 
01128 static int unload_module (void)
01129 {
01130    ast_config_engine_deregister(&odbc_engine);
01131 
01132    ast_verb(1, "res_config_odbc unloaded.\n");
01133    return 0;
01134 }
01135 
01136 static int load_module (void)
01137 {
01138    ast_config_engine_register(&odbc_engine);
01139    ast_verb(1, "res_config_odbc loaded.\n");
01140    return 0;
01141 }
01142 
01143 static int reload_module(void)
01144 {
01145    return 0;
01146 }
01147 
01148 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "Realtime ODBC configuration",
01149       .load = load_module,
01150       .unload = unload_module,
01151       .reload = reload_module,
01152       );