Mon Jun 27 16:50:55 2011

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

Generated on Mon Jun 27 16:50:55 2011 for Asterisk - The Open Source Telephony Project by  doxygen 1.4.7