Thu Oct 11 06:47:20 2012

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: 310141 $")
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       colsize = 0;
00241       collen = sizeof(coltitle);
00242       res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00243                &datatype, &colsize, &decimaldigits, &nullable);
00244       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00245          ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00246          if (var)
00247             ast_variables_destroy(var);
00248          ast_odbc_release_obj(obj);
00249          ast_string_field_free_memory(&cps);
00250          return NULL;
00251       }
00252 
00253       indicator = 0;
00254       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00255       if (indicator == SQL_NULL_DATA)
00256          rowdata[0] = '\0';
00257       else if (ast_strlen_zero(rowdata)) {
00258          /* Because we encode the empty string for a NULL, we will encode
00259           * actual empty strings as a string containing a single whitespace. */
00260          ast_copy_string(rowdata, " ", sizeof(rowdata));
00261       }
00262 
00263       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00264          ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00265          if (var)
00266             ast_variables_destroy(var);
00267          ast_odbc_release_obj(obj);
00268          return NULL;
00269       }
00270       stringp = rowdata;
00271       while (stringp) {
00272          chunk = strsep(&stringp, ";");
00273          if (!ast_strlen_zero(ast_strip(chunk))) {
00274             if (strchr(chunk, '^')) {
00275                decode_chunk(chunk);
00276             }
00277             if (prev) {
00278                prev->next = ast_variable_new(coltitle, chunk, "");
00279                if (prev->next) {
00280                   prev = prev->next;
00281                }
00282             } else {
00283                prev = var = ast_variable_new(coltitle, chunk, "");
00284             }
00285          }
00286       }
00287    }
00288 
00289 
00290    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00291    ast_odbc_release_obj(obj);
00292    ast_string_field_free_memory(&cps);
00293    return var;
00294 }
00295 
00296 /*!
00297  * \brief Excute an Select query and return ast_config list
00298  * \param database
00299  * \param table
00300  * \param ap list containing one or more field/operator/value set.
00301  *
00302  * Select database and preform query on table, prepare the sql statement
00303  * Sub-in the values to the prepared statement and execute it. 
00304  * Execute this prepared query against several ODBC connected databases.
00305  * Return results as an ast_config variable.
00306  *
00307  * \retval var on success
00308  * \retval NULL on failure
00309 */
00310 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
00311 {
00312    struct odbc_obj *obj;
00313    SQLHSTMT stmt;
00314    char sql[1024];
00315    char coltitle[256];
00316    char rowdata[2048];
00317    const char *initfield=NULL;
00318    char *op;
00319    const char *newparam, *newval;
00320    char *stringp;
00321    char *chunk;
00322    SQLSMALLINT collen;
00323    int res;
00324    int x;
00325    struct ast_variable *var=NULL;
00326    struct ast_config *cfg=NULL;
00327    struct ast_category *cat=NULL;
00328    SQLULEN colsize;
00329    SQLSMALLINT colcount=0;
00330    SQLSMALLINT datatype;
00331    SQLSMALLINT decimaldigits;
00332    SQLSMALLINT nullable;
00333    SQLLEN indicator;
00334    struct custom_prepare_struct cps = { .sql = sql };
00335    va_list aq;
00336 
00337    if (!table || ast_string_field_init(&cps, 256)) {
00338       return NULL;
00339    }
00340    va_copy(cps.ap, ap);
00341    va_copy(aq, ap);
00342 
00343 
00344    obj = ast_odbc_request_obj(database, 0);
00345    if (!obj) {
00346       ast_string_field_free_memory(&cps);
00347       return NULL;
00348    }
00349 
00350    newparam = va_arg(aq, const char *);
00351    if (!newparam)  {
00352       ast_odbc_release_obj(obj);
00353       ast_string_field_free_memory(&cps);
00354       return NULL;
00355    }
00356    initfield = ast_strdupa(newparam);
00357    if ((op = strchr(initfield, ' '))) 
00358       *op = '\0';
00359    newval = va_arg(aq, const char *);
00360    op = !strchr(newparam, ' ') ? " =" : "";
00361    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00362       strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00363    while((newparam = va_arg(aq, const char *))) {
00364       op = !strchr(newparam, ' ') ? " =" : "";
00365       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00366          strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00367       newval = va_arg(aq, const char *);
00368    }
00369    if (initfield)
00370       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00371    va_end(aq);
00372 
00373    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00374 
00375    if (!stmt) {
00376       ast_odbc_release_obj(obj);
00377       ast_string_field_free_memory(&cps);
00378       return NULL;
00379    }
00380 
00381    res = SQLNumResultCols(stmt, &colcount);
00382    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00383       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00384       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00385       ast_odbc_release_obj(obj);
00386       ast_string_field_free_memory(&cps);
00387       return NULL;
00388    }
00389 
00390    cfg = ast_config_new();
00391    if (!cfg) {
00392       ast_log(LOG_WARNING, "Out of memory!\n");
00393       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00394       ast_odbc_release_obj(obj);
00395       ast_string_field_free_memory(&cps);
00396       return NULL;
00397    }
00398 
00399    while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
00400       var = NULL;
00401       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00402          ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00403          continue;
00404       }
00405       cat = ast_category_new("","",99999);
00406       if (!cat) {
00407          ast_log(LOG_WARNING, "Out of memory!\n");
00408          continue;
00409       }
00410       for (x=0;x<colcount;x++) {
00411          rowdata[0] = '\0';
00412          colsize = 0;
00413          collen = sizeof(coltitle);
00414          res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00415                   &datatype, &colsize, &decimaldigits, &nullable);
00416          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00417             ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00418             ast_category_destroy(cat);
00419             continue;
00420          }
00421 
00422          indicator = 0;
00423          res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00424          if (indicator == SQL_NULL_DATA)
00425             continue;
00426 
00427          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00428             ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00429             ast_category_destroy(cat);
00430             continue;
00431          }
00432          stringp = rowdata;
00433          while (stringp) {
00434             chunk = strsep(&stringp, ";");
00435             if (!ast_strlen_zero(ast_strip(chunk))) {
00436                if (strchr(chunk, '^')) {
00437                   decode_chunk(chunk);
00438                }
00439                if (initfield && !strcmp(initfield, coltitle)) {
00440                   ast_category_rename(cat, chunk);
00441                }
00442                var = ast_variable_new(coltitle, chunk, "");
00443                ast_variable_append(cat, var);
00444             }
00445          }
00446       }
00447       ast_category_append(cfg, cat);
00448    }
00449 
00450    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00451    ast_odbc_release_obj(obj);
00452    ast_string_field_free_memory(&cps);
00453    return cfg;
00454 }
00455 
00456 /*!
00457  * \brief Excute an UPDATE query
00458  * \param database
00459  * \param table
00460  * \param keyfield where clause field
00461  * \param lookup value of field for where clause
00462  * \param ap list containing one or more field/value set(s).
00463  *
00464  * Update a database table, prepare the sql statement using keyfield and lookup
00465  * control the number of records to change. All values to be changed are stored in ap list.
00466  * Sub-in the values to the prepared statement and execute it.
00467  *
00468  * \retval number of rows affected
00469  * \retval -1 on failure
00470 */
00471 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00472 {
00473    struct odbc_obj *obj;
00474    SQLHSTMT stmt;
00475    char sql[256];
00476    SQLLEN rowcount=0;
00477    const char *newparam, *newval;
00478    int res, count = 1;
00479    va_list aq;
00480    struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00481    struct odbc_cache_tables *tableptr;
00482    struct odbc_cache_columns *column;
00483 
00484    if (!table) {
00485       return -1;
00486    }
00487 
00488    va_copy(cps.ap, ap);
00489    va_copy(aq, ap);
00490 
00491    if (ast_string_field_init(&cps, 256)) {
00492       return -1;
00493    }
00494 
00495    tableptr = ast_odbc_find_table(database, table);
00496    if (!(obj = ast_odbc_request_obj(database, 0))) {
00497       ast_odbc_release_table(tableptr);
00498       ast_string_field_free_memory(&cps);
00499       return -1;
00500    }
00501 
00502    newparam = va_arg(aq, const char *);
00503    if (!newparam)  {
00504       ast_odbc_release_obj(obj);
00505       ast_odbc_release_table(tableptr);
00506       ast_string_field_free_memory(&cps);
00507       return -1;
00508    }
00509    newval = va_arg(aq, const char *);
00510 
00511    if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
00512       ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", newparam, table, database);
00513    }
00514 
00515    snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
00516    while((newparam = va_arg(aq, const char *))) {
00517       newval = va_arg(aq, const char *);
00518       if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
00519          snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
00520       } else { /* the column does not exist in the table */
00521          cps.skip |= (1LL << count);
00522       }
00523       count++;
00524    }
00525    va_end(aq);
00526    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
00527    ast_odbc_release_table(tableptr);
00528 
00529    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00530 
00531    if (!stmt) {
00532       ast_odbc_release_obj(obj);
00533       ast_string_field_free_memory(&cps);
00534       return -1;
00535    }
00536 
00537    res = SQLRowCount(stmt, &rowcount);
00538    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00539    ast_odbc_release_obj(obj);
00540    ast_string_field_free_memory(&cps);
00541 
00542    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00543       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00544       return -1;
00545    }
00546 
00547    if (rowcount >= 0) {
00548       return (int) rowcount;
00549    }
00550 
00551    return -1;
00552 }
00553 
00554 struct update2_prepare_struct {
00555    const char *database;
00556    const char *table;
00557    va_list ap;
00558 };
00559 
00560 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
00561 {
00562    int res, x = 1, first = 1;
00563    struct update2_prepare_struct *ups = data;
00564    const char *newparam, *newval;
00565    struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
00566    SQLHSTMT stmt;
00567    va_list ap;
00568    struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
00569    struct odbc_cache_columns *column;
00570 
00571    if (!sql) {
00572       if (tableptr) {
00573          ast_odbc_release_table(tableptr);
00574       }
00575       return NULL;
00576    }
00577 
00578    if (!tableptr) {
00579       ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'.  Update will fail!\n", ups->table, ups->database);
00580       return NULL;
00581    }
00582 
00583    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00584    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00585       ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00586       ast_odbc_release_table(tableptr);
00587       return NULL;
00588    }
00589 
00590    ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
00591 
00592    /* Start by finding the second set of parameters */
00593    va_copy(ap, ups->ap);
00594 
00595    while ((newparam = va_arg(ap, const char *))) {
00596       newval = va_arg(ap, const char *);
00597    }
00598 
00599    while ((newparam = va_arg(ap, const char *))) {
00600       newval = va_arg(ap, const char *);
00601       if ((column = ast_odbc_find_column(tableptr, newparam))) {
00602          ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", newparam);
00603          SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00604          first = 0;
00605       } else {
00606          ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", newparam, ups->table, ups->database);
00607       }
00608    }
00609    va_end(ap);
00610 
00611    /* Restart search, because we need to add the search parameters */
00612    va_copy(ap, ups->ap);
00613    ast_str_append(&sql, 0, "WHERE");
00614    first = 1;
00615 
00616    while ((newparam = va_arg(ap, const char *))) {
00617       newval = va_arg(ap, const char *);
00618       if (!(column = ast_odbc_find_column(tableptr, newparam))) {
00619          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);
00620          ast_odbc_release_table(tableptr);
00621          SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00622          return NULL;
00623       }
00624       ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", newparam);
00625       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00626       first = 0;
00627    }
00628    va_end(ap);
00629 
00630    /* Done with the table metadata */
00631    ast_odbc_release_table(tableptr);
00632 
00633    res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
00634    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00635       ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
00636       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00637       return NULL;
00638    }
00639 
00640    return stmt;
00641 }
00642 
00643 /*!
00644  * \brief Execute an UPDATE query
00645  * \param database
00646  * \param table
00647  * \param ap list containing one or more field/value set(s).
00648  *
00649  * Update a database table, preparing the sql statement from a list of
00650  * key/value pairs specified in ap.  The lookup pairs are specified first
00651  * and are separated from the update pairs by a sentinel value.
00652  * Sub-in the values to the prepared statement and execute it.
00653  *
00654  * \retval number of rows affected
00655  * \retval -1 on failure
00656 */
00657 static int update2_odbc(const char *database, const char *table, va_list ap)
00658 {
00659    struct odbc_obj *obj;
00660    SQLHSTMT stmt;
00661    struct update2_prepare_struct ups = { .database = database, .table = table, };
00662    struct ast_str *sql;
00663    int res;
00664    SQLLEN rowcount = 0;
00665 
00666    va_copy(ups.ap, ap);
00667 
00668    if (!(obj = ast_odbc_request_obj(database, 0))) {
00669       return -1;
00670    }
00671 
00672    if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
00673       ast_odbc_release_obj(obj);
00674       return -1;
00675    }
00676 
00677    res = SQLRowCount(stmt, &rowcount);
00678    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00679    ast_odbc_release_obj(obj);
00680 
00681    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00682       /* Since only a single thread can access this memory, we can retrieve what would otherwise be lost. */
00683       sql = ast_str_thread_get(&sql_buf, 16);
00684       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
00685       return -1;
00686    }
00687 
00688    if (rowcount >= 0) {
00689       return (int)rowcount;
00690    }
00691 
00692    return -1;
00693 }
00694 
00695 /*!
00696  * \brief Excute an INSERT query
00697  * \param database
00698  * \param table
00699  * \param ap list containing one or more field/value set(s)
00700  *
00701  * Insert a new record into database table, prepare the sql statement.
00702  * All values to be changed are stored in ap list.
00703  * Sub-in the values to the prepared statement and execute it.
00704  *
00705  * \retval number of rows affected
00706  * \retval -1 on failure
00707 */
00708 static int store_odbc(const char *database, const char *table, va_list ap)
00709 {
00710    struct odbc_obj *obj;
00711    SQLHSTMT stmt;
00712    char sql[256];
00713    char keys[256];
00714    char vals[256];
00715    SQLLEN rowcount=0;
00716    const char *newparam, *newval;
00717    int res;
00718    va_list aq;
00719    struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
00720 
00721    va_copy(cps.ap, ap);
00722    va_copy(aq, ap);
00723    
00724    if (!table)
00725       return -1;
00726 
00727    obj = ast_odbc_request_obj(database, 0);
00728    if (!obj)
00729       return -1;
00730 
00731    newparam = va_arg(aq, const char *);
00732    if (!newparam)  {
00733       ast_odbc_release_obj(obj);
00734       return -1;
00735    }
00736    newval = va_arg(aq, const char *);
00737    snprintf(keys, sizeof(keys), "%s", newparam);
00738    ast_copy_string(vals, "?", sizeof(vals));
00739    while ((newparam = va_arg(aq, const char *))) {
00740       snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
00741       snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
00742       newval = va_arg(aq, const char *);
00743    }
00744    va_end(aq);
00745    snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
00746 
00747    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00748 
00749    if (!stmt) {
00750       ast_odbc_release_obj(obj);
00751       return -1;
00752    }
00753 
00754    res = SQLRowCount(stmt, &rowcount);
00755    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00756    ast_odbc_release_obj(obj);
00757 
00758    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00759       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00760       return -1;
00761    }
00762 
00763    if (rowcount >= 0)
00764       return (int)rowcount;
00765 
00766    return -1;
00767 }
00768 
00769 /*!
00770  * \brief Excute an DELETE query
00771  * \param database
00772  * \param table
00773  * \param keyfield where clause field
00774  * \param lookup value of field for where clause
00775  * \param ap list containing one or more field/value set(s)
00776  *
00777  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
00778  * control the number of records to change. Additional params to match rows are stored in ap list.
00779  * Sub-in the values to the prepared statement and execute it.
00780  *
00781  * \retval number of rows affected
00782  * \retval -1 on failure
00783 */
00784 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00785 {
00786    struct odbc_obj *obj;
00787    SQLHSTMT stmt;
00788    char sql[256];
00789    SQLLEN rowcount=0;
00790    const char *newparam, *newval;
00791    int res;
00792    va_list aq;
00793    struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00794 
00795    va_copy(cps.ap, ap);
00796    va_copy(aq, ap);
00797    
00798    if (!table)
00799       return -1;
00800 
00801    obj = ast_odbc_request_obj(database, 0);
00802    if (!obj)
00803       return -1;
00804 
00805    snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
00806    while((newparam = va_arg(aq, const char *))) {
00807       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
00808       newval = va_arg(aq, const char *);
00809    }
00810    va_end(aq);
00811    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
00812 
00813    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00814 
00815    if (!stmt) {
00816       ast_odbc_release_obj(obj);
00817       return -1;
00818    }
00819 
00820    res = SQLRowCount(stmt, &rowcount);
00821    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00822    ast_odbc_release_obj(obj);
00823 
00824    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00825       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00826       return -1;
00827    }
00828 
00829    if (rowcount >= 0)
00830       return (int)rowcount;
00831 
00832    return -1;
00833 }
00834 
00835 
00836 struct config_odbc_obj {
00837    char *sql;
00838    unsigned long cat_metric;
00839    char category[128];
00840    char var_name[128];
00841    char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
00842    SQLLEN err;
00843 };
00844 
00845 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
00846 {
00847    struct config_odbc_obj *q = data;
00848    SQLHSTMT sth;
00849    int res;
00850 
00851    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
00852    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00853       ast_verb(4, "Failure in AllocStatement %d\n", res);
00854       return NULL;
00855    }
00856 
00857    res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
00858    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00859       ast_verb(4, "Error in PREPARE %d\n", res);
00860       SQLFreeHandle(SQL_HANDLE_STMT, sth);
00861       return NULL;
00862    }
00863 
00864    SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
00865    SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
00866    SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
00867    SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
00868 
00869    return sth;
00870 }
00871 
00872 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)
00873 {
00874    struct ast_variable *new_v;
00875    struct ast_category *cur_cat;
00876    int res = 0;
00877    struct odbc_obj *obj;
00878    char sqlbuf[1024] = "";
00879    char *sql = sqlbuf;
00880    size_t sqlleft = sizeof(sqlbuf);
00881    unsigned int last_cat_metric = 0;
00882    SQLSMALLINT rowcount = 0;
00883    SQLHSTMT stmt;
00884    char last[128] = "";
00885    struct config_odbc_obj q;
00886    struct ast_flags loader_flags = { 0 };
00887 
00888    memset(&q, 0, sizeof(q));
00889 
00890    if (!file || !strcmp (file, "res_config_odbc.conf"))
00891       return NULL;      /* cant configure myself with myself ! */
00892 
00893    obj = ast_odbc_request_obj(database, 0);
00894    if (!obj)
00895       return NULL;
00896 
00897    ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
00898    ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
00899    ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00900    q.sql = sqlbuf;
00901 
00902    stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
00903 
00904    if (!stmt) {
00905       ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
00906       ast_odbc_release_obj(obj);
00907       return NULL;
00908    }
00909 
00910    res = SQLNumResultCols(stmt, &rowcount);
00911 
00912    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00913       ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
00914       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00915       ast_odbc_release_obj(obj);
00916       return NULL;
00917    }
00918 
00919    if (!rowcount) {
00920       ast_log(LOG_NOTICE, "found nothing\n");
00921       ast_odbc_release_obj(obj);
00922       return cfg;
00923    }
00924 
00925    cur_cat = ast_config_get_current_category(cfg);
00926 
00927    while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
00928       if (!strcmp (q.var_name, "#include")) {
00929          if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
00930             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00931             ast_odbc_release_obj(obj);
00932             return NULL;
00933          }
00934          continue;
00935       } 
00936       if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
00937          cur_cat = ast_category_new(q.category, "", 99999);
00938          if (!cur_cat) {
00939             ast_log(LOG_WARNING, "Out of memory!\n");
00940             break;
00941          }
00942          strcpy(last, q.category);
00943          last_cat_metric   = q.cat_metric;
00944          ast_category_append(cfg, cur_cat);
00945       }
00946 
00947       new_v = ast_variable_new(q.var_name, q.var_val, "");
00948       ast_variable_append(cur_cat, new_v);
00949    }
00950 
00951    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00952    ast_odbc_release_obj(obj);
00953    return cfg;
00954 }
00955 
00956 #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)
00957 #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)
00958 
00959 static int require_odbc(const char *database, const char *table, va_list ap)
00960 {
00961    struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
00962    struct odbc_cache_columns *col;
00963    char *elm;
00964    int type, size;
00965 
00966    if (!tableptr) {
00967       return -1;
00968    }
00969 
00970    while ((elm = va_arg(ap, char *))) {
00971       type = va_arg(ap, require_type);
00972       size = va_arg(ap, int);
00973       /* Check if the field matches the criteria */
00974       AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
00975          if (strcmp(col->name, elm) == 0) {
00976             /* Type check, first.  Some fields are more particular than others */
00977             switch (col->type) {
00978             case SQL_CHAR:
00979             case SQL_VARCHAR:
00980             case SQL_LONGVARCHAR:
00981 #ifdef HAVE_ODBC_WCHAR
00982             case SQL_WCHAR:
00983             case SQL_WVARCHAR:
00984             case SQL_WLONGVARCHAR:
00985 #endif
00986             case SQL_BINARY:
00987             case SQL_VARBINARY:
00988             case SQL_LONGVARBINARY:
00989             case SQL_GUID:
00990 #define CHECK_SIZE(n) \
00991                   if (col->size < n) {      \
00992                      warn_length(col, n);  \
00993                   }                         \
00994                   break;
00995                switch (type) {
00996                case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
00997                case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
00998                case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
00999                case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
01000                case RQ_UINTEGER3:                /*    16777215 */
01001                case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
01002                case RQ_DATE:                     /*  2008-06-09 */
01003                case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
01004                case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
01005                case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
01006                case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
01007                case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
01008                case RQ_FLOAT:
01009                case RQ_CHAR:      CHECK_SIZE(size)
01010                }
01011 #undef CHECK_SIZE
01012                break;
01013             case SQL_TYPE_DATE:
01014                if (type != RQ_DATE) {
01015                   warn_type(col, type);
01016                }
01017                break;
01018             case SQL_TYPE_TIMESTAMP:
01019             case SQL_TIMESTAMP:
01020                if (type != RQ_DATE && type != RQ_DATETIME) {
01021                   warn_type(col, type);
01022                }
01023                break;
01024             case SQL_BIT:
01025                warn_length(col, size);
01026                break;
01027 #define WARN_TYPE_OR_LENGTH(n)   \
01028                   if (!ast_rq_is_int(type)) {  \
01029                      warn_type(col, type);    \
01030                   } else {                     \
01031                      warn_length(col, n);  \
01032                   }
01033             case SQL_TINYINT:
01034                if (type != RQ_UINTEGER1) {
01035                   WARN_TYPE_OR_LENGTH(size)
01036                }
01037                break;
01038             case SQL_C_STINYINT:
01039                if (type != RQ_INTEGER1) {
01040                   WARN_TYPE_OR_LENGTH(size)
01041                }
01042                break;
01043             case SQL_C_USHORT:
01044                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
01045                   WARN_TYPE_OR_LENGTH(size)
01046                }
01047                break;
01048             case SQL_SMALLINT:
01049             case SQL_C_SSHORT:
01050                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
01051                   WARN_TYPE_OR_LENGTH(size)
01052                }
01053                break;
01054             case SQL_C_ULONG:
01055                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01056                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01057                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01058                   type != RQ_INTEGER4) {
01059                   WARN_TYPE_OR_LENGTH(size)
01060                }
01061                break;
01062             case SQL_INTEGER:
01063             case SQL_C_SLONG:
01064                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01065                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01066                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01067                   type != RQ_INTEGER4) {
01068                   WARN_TYPE_OR_LENGTH(size)
01069                }
01070                break;
01071             case SQL_C_UBIGINT:
01072                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01073                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01074                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01075                   type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01076                   type != RQ_INTEGER8) {
01077                   WARN_TYPE_OR_LENGTH(size)
01078                }
01079                break;
01080             case SQL_BIGINT:
01081             case SQL_C_SBIGINT:
01082                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01083                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01084                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01085                   type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01086                   type != RQ_INTEGER8) {
01087                   WARN_TYPE_OR_LENGTH(size)
01088                }
01089                break;
01090 #undef WARN_TYPE_OR_LENGTH
01091             case SQL_NUMERIC:
01092             case SQL_DECIMAL:
01093             case SQL_FLOAT:
01094             case SQL_REAL:
01095             case SQL_DOUBLE:
01096                if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
01097                   warn_type(col, type);
01098                }
01099                break;
01100             default:
01101                ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
01102             }
01103             break;
01104          }
01105       }
01106       if (!col) {
01107          ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
01108       }
01109    }
01110    va_end(ap);
01111    AST_RWLIST_UNLOCK(&tableptr->columns);
01112    return 0;
01113 }
01114 #undef warn_length
01115 #undef warn_type
01116 
01117 static struct ast_config_engine odbc_engine = {
01118    .name = "odbc",
01119    .load_func = config_odbc,
01120    .realtime_func = realtime_odbc,
01121    .realtime_multi_func = realtime_multi_odbc,
01122    .store_func = store_odbc,
01123    .destroy_func = destroy_odbc,
01124    .update_func = update_odbc,
01125    .update2_func = update2_odbc,
01126    .require_func = require_odbc,
01127    .unload_func = ast_odbc_clear_cache,
01128 };
01129 
01130 static int unload_module (void)
01131 {
01132    ast_config_engine_deregister(&odbc_engine);
01133 
01134    ast_verb(1, "res_config_odbc unloaded.\n");
01135    return 0;
01136 }
01137 
01138 static int load_module (void)
01139 {
01140    ast_config_engine_register(&odbc_engine);
01141    ast_verb(1, "res_config_odbc loaded.\n");
01142    return 0;
01143 }
01144 
01145 static int reload_module(void)
01146 {
01147    return 0;
01148 }
01149 
01150 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "Realtime ODBC configuration",
01151       .load = load_module,
01152       .unload = unload_module,
01153       .reload = reload_module,
01154       );

Generated on Thu Oct 11 06:47:20 2012 for Asterisk - the Open Source PBX by  doxygen 1.5.6