Wed Aug 18 22:33:55 2010

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 - 2005, 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>unixodbc</depend>
00033    <depend>ltdl</depend>
00034    <depend>res_odbc</depend>
00035  ***/
00036 
00037 #include "asterisk.h"
00038 
00039 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 217642 $")
00040 
00041 #include "asterisk/file.h"
00042 #include "asterisk/channel.h"
00043 #include "asterisk/pbx.h"
00044 #include "asterisk/config.h"
00045 #include "asterisk/module.h"
00046 #include "asterisk/lock.h"
00047 #include "asterisk/res_odbc.h"
00048 #include "asterisk/utils.h"
00049 
00050 struct custom_prepare_struct {
00051    const char *sql;
00052    const char *extra;
00053    va_list ap;
00054    unsigned long long skip;
00055 };
00056 
00057 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
00058 {
00059    int res, x = 1, count = 0;
00060    struct custom_prepare_struct *cps = data;
00061    const char *newparam, *newval;
00062    SQLHSTMT stmt;
00063    va_list ap;
00064 
00065    va_copy(ap, cps->ap);
00066 
00067    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00068    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00069       ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00070       return NULL;
00071    }
00072 
00073    ast_debug(1, "Skip: %lld; SQL: %s\n", cps->skip, cps->sql);
00074 
00075    res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
00076    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00077       ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
00078       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00079       return NULL;
00080    }
00081 
00082    while ((newparam = va_arg(ap, const char *))) {
00083       newval = va_arg(ap, const char *);
00084       if ((1LL << count++) & cps->skip) {
00085          ast_debug(1, "Skipping field '%s'='%s' (%llo/%llo)\n", newparam, newval, 1LL << (count - 1), cps->skip);
00086          continue;
00087       }
00088       ast_debug(1, "Parameter %d ('%s') = '%s'\n", x, newparam, newval);
00089       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00090    }
00091    va_end(ap);
00092 
00093    if (!ast_strlen_zero(cps->extra))
00094       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
00095    return stmt;
00096 }
00097 
00098 /*!
00099  * \brief Excute an SQL query and return ast_variable list
00100  * \param database
00101  * \param table
00102  * \param ap list containing one or more field/operator/value set.
00103  *
00104  * Select database and preform query on table, prepare the sql statement
00105  * Sub-in the values to the prepared statement and execute it. Return results
00106  * as a ast_variable list.
00107  *
00108  * \retval var on success
00109  * \retval NULL on failure
00110 */
00111 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
00112 {
00113    struct odbc_obj *obj;
00114    SQLHSTMT stmt;
00115    char sql[1024];
00116    char coltitle[256];
00117    char rowdata[2048];
00118    char *op;
00119    const char *newparam, *newval;
00120    char *stringp;
00121    char *chunk;
00122    SQLSMALLINT collen;
00123    int res;
00124    int x;
00125    struct ast_variable *var=NULL, *prev=NULL;
00126    SQLULEN colsize;
00127    SQLSMALLINT colcount=0;
00128    SQLSMALLINT datatype;
00129    SQLSMALLINT decimaldigits;
00130    SQLSMALLINT nullable;
00131    SQLLEN indicator;
00132    va_list aq;
00133    struct custom_prepare_struct cps = { .sql = sql };
00134 
00135    va_copy(cps.ap, ap);
00136    va_copy(aq, ap);
00137 
00138    if (!table)
00139       return NULL;
00140 
00141    obj = ast_odbc_request_obj(database, 0);
00142 
00143    if (!obj) {
00144       ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
00145       return NULL;
00146    }
00147 
00148    newparam = va_arg(aq, const char *);
00149    if (!newparam) {
00150       ast_odbc_release_obj(obj);
00151       return NULL;
00152    }
00153    newval = va_arg(aq, const char *);
00154    op = !strchr(newparam, ' ') ? " =" : "";
00155    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00156       strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00157    while((newparam = va_arg(aq, const char *))) {
00158       op = !strchr(newparam, ' ') ? " =" : "";
00159       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00160          strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00161       newval = va_arg(aq, const char *);
00162    }
00163    va_end(aq);
00164 
00165    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00166 
00167    if (!stmt) {
00168       ast_odbc_release_obj(obj);
00169       return NULL;
00170    }
00171 
00172    res = SQLNumResultCols(stmt, &colcount);
00173    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00174       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00175       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00176       ast_odbc_release_obj(obj);
00177       return NULL;
00178    }
00179 
00180    res = SQLFetch(stmt);
00181    if (res == SQL_NO_DATA) {
00182       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00183       ast_odbc_release_obj(obj);
00184       return NULL;
00185    }
00186    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00187       ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00188       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00189       ast_odbc_release_obj(obj);
00190       return NULL;
00191    }
00192    for (x = 0; x < colcount; x++) {
00193       rowdata[0] = '\0';
00194       collen = sizeof(coltitle);
00195       res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00196                &datatype, &colsize, &decimaldigits, &nullable);
00197       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00198          ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00199          if (var)
00200             ast_variables_destroy(var);
00201          ast_odbc_release_obj(obj);
00202          return NULL;
00203       }
00204 
00205       indicator = 0;
00206       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00207       if (indicator == SQL_NULL_DATA)
00208          rowdata[0] = '\0';
00209       else if (ast_strlen_zero(rowdata)) {
00210          /* Because we encode the empty string for a NULL, we will encode
00211           * actual empty strings as a string containing a single whitespace. */
00212          ast_copy_string(rowdata, " ", sizeof(rowdata));
00213       }
00214 
00215       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00216          ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00217          if (var)
00218             ast_variables_destroy(var);
00219          ast_odbc_release_obj(obj);
00220          return NULL;
00221       }
00222       stringp = rowdata;
00223       while(stringp) {
00224          chunk = strsep(&stringp, ";");
00225          if (!ast_strlen_zero(ast_strip(chunk))) {
00226             if (prev) {
00227                prev->next = ast_variable_new(coltitle, chunk, "");
00228                if (prev->next)
00229                   prev = prev->next;
00230             } else 
00231                prev = var = ast_variable_new(coltitle, chunk, "");
00232          }
00233       }
00234    }
00235 
00236 
00237    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00238    ast_odbc_release_obj(obj);
00239    return var;
00240 }
00241 
00242 /*!
00243  * \brief Excute an Select query and return ast_config list
00244  * \param database
00245  * \param table
00246  * \param ap list containing one or more field/operator/value set.
00247  *
00248  * Select database and preform query on table, prepare the sql statement
00249  * Sub-in the values to the prepared statement and execute it. 
00250  * Execute this prepared query against several ODBC connected databases.
00251  * Return results as an ast_config variable.
00252  *
00253  * \retval var on success
00254  * \retval NULL on failure
00255 */
00256 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
00257 {
00258    struct odbc_obj *obj;
00259    SQLHSTMT stmt;
00260    char sql[1024];
00261    char coltitle[256];
00262    char rowdata[2048];
00263    const char *initfield=NULL;
00264    char *op;
00265    const char *newparam, *newval;
00266    char *stringp;
00267    char *chunk;
00268    SQLSMALLINT collen;
00269    int res;
00270    int x;
00271    struct ast_variable *var=NULL;
00272    struct ast_config *cfg=NULL;
00273    struct ast_category *cat=NULL;
00274    SQLULEN colsize;
00275    SQLSMALLINT colcount=0;
00276    SQLSMALLINT datatype;
00277    SQLSMALLINT decimaldigits;
00278    SQLSMALLINT nullable;
00279    SQLLEN indicator;
00280    struct custom_prepare_struct cps = { .sql = sql };
00281    va_list aq;
00282 
00283    va_copy(cps.ap, ap);
00284    va_copy(aq, ap);
00285 
00286    if (!table)
00287       return NULL;
00288 
00289    obj = ast_odbc_request_obj(database, 0);
00290    if (!obj)
00291       return NULL;
00292 
00293    newparam = va_arg(aq, const char *);
00294    if (!newparam)  {
00295       ast_odbc_release_obj(obj);
00296       return NULL;
00297    }
00298    initfield = ast_strdupa(newparam);
00299    if ((op = strchr(initfield, ' '))) 
00300       *op = '\0';
00301    newval = va_arg(aq, const char *);
00302    op = !strchr(newparam, ' ') ? " =" : "";
00303    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00304       strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00305    while((newparam = va_arg(aq, const char *))) {
00306       op = !strchr(newparam, ' ') ? " =" : "";
00307       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00308          strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00309       newval = va_arg(aq, const char *);
00310    }
00311    if (initfield)
00312       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00313    va_end(aq);
00314 
00315    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00316 
00317    if (!stmt) {
00318       ast_odbc_release_obj(obj);
00319       return NULL;
00320    }
00321 
00322    res = SQLNumResultCols(stmt, &colcount);
00323    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00324       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00325       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00326       ast_odbc_release_obj(obj);
00327       return NULL;
00328    }
00329 
00330    cfg = ast_config_new();
00331    if (!cfg) {
00332       ast_log(LOG_WARNING, "Out of memory!\n");
00333       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00334       ast_odbc_release_obj(obj);
00335       return NULL;
00336    }
00337 
00338    while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
00339       var = NULL;
00340       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00341          ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00342          continue;
00343       }
00344       cat = ast_category_new("","",99999);
00345       if (!cat) {
00346          ast_log(LOG_WARNING, "Out of memory!\n");
00347          continue;
00348       }
00349       for (x=0;x<colcount;x++) {
00350          rowdata[0] = '\0';
00351          collen = sizeof(coltitle);
00352          res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00353                   &datatype, &colsize, &decimaldigits, &nullable);
00354          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00355             ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00356             ast_category_destroy(cat);
00357             continue;
00358          }
00359 
00360          indicator = 0;
00361          res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00362          if (indicator == SQL_NULL_DATA)
00363             continue;
00364 
00365          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00366             ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00367             ast_category_destroy(cat);
00368             continue;
00369          }
00370          stringp = rowdata;
00371          while(stringp) {
00372             chunk = strsep(&stringp, ";");
00373             if (!ast_strlen_zero(ast_strip(chunk))) {
00374                if (initfield && !strcmp(initfield, coltitle))
00375                   ast_category_rename(cat, chunk);
00376                var = ast_variable_new(coltitle, chunk, "");
00377                ast_variable_append(cat, var);
00378             }
00379          }
00380       }
00381       ast_category_append(cfg, cat);
00382    }
00383 
00384    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00385    ast_odbc_release_obj(obj);
00386    return cfg;
00387 }
00388 
00389 /*!
00390  * \brief Excute an UPDATE query
00391  * \param database
00392  * \param table
00393  * \param keyfield where clause field
00394  * \param lookup value of field for where clause
00395  * \param ap list containing one or more field/value set(s).
00396  *
00397  * Update a database table, prepare the sql statement using keyfield and lookup
00398  * control the number of records to change. All values to be changed are stored in ap list.
00399  * Sub-in the values to the prepared statement and execute it.
00400  *
00401  * \retval number of rows affected
00402  * \retval -1 on failure
00403 */
00404 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00405 {
00406    struct odbc_obj *obj;
00407    SQLHSTMT stmt;
00408    char sql[256];
00409    SQLLEN rowcount=0;
00410    const char *newparam, *newval;
00411    int res, count = 1;
00412    va_list aq;
00413    struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00414    struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
00415    struct odbc_cache_columns *column;
00416 
00417    va_copy(cps.ap, ap);
00418    va_copy(aq, ap);
00419    
00420    if (!table) {
00421       ast_odbc_release_table(tableptr);
00422       return -1;
00423    }
00424 
00425    obj = ast_odbc_request_obj(database, 0);
00426    if (!obj) {
00427       ast_odbc_release_table(tableptr);
00428       return -1;
00429    }
00430 
00431    newparam = va_arg(aq, const char *);
00432    if (!newparam)  {
00433       ast_odbc_release_obj(obj);
00434       ast_odbc_release_table(tableptr);
00435       return -1;
00436    }
00437    newval = va_arg(aq, const char *);
00438 
00439    if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
00440       ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", newparam, table, database);
00441    }
00442 
00443    snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
00444    while((newparam = va_arg(aq, const char *))) {
00445       newval = va_arg(aq, const char *);
00446       if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
00447          snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
00448       } else { /* the column does not exist in the table */
00449          cps.skip |= (1LL << count);
00450       }
00451       count++;
00452    }
00453    va_end(aq);
00454    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
00455    ast_odbc_release_table(tableptr);
00456 
00457    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00458 
00459    if (!stmt) {
00460       ast_odbc_release_obj(obj);
00461       return -1;
00462    }
00463 
00464    res = SQLRowCount(stmt, &rowcount);
00465    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00466    ast_odbc_release_obj(obj);
00467 
00468    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00469       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00470       return -1;
00471    }
00472 
00473    if (rowcount >= 0)
00474       return (int)rowcount;
00475 
00476    return -1;
00477 }
00478 
00479 /*!
00480  * \brief Excute an INSERT query
00481  * \param database
00482  * \param table
00483  * \param ap list containing one or more field/value set(s)
00484  *
00485  * Insert a new record into database table, prepare the sql statement.
00486  * All values to be changed are stored in ap list.
00487  * Sub-in the values to the prepared statement and execute it.
00488  *
00489  * \retval number of rows affected
00490  * \retval -1 on failure
00491 */
00492 static int store_odbc(const char *database, const char *table, va_list ap)
00493 {
00494    struct odbc_obj *obj;
00495    SQLHSTMT stmt;
00496    char sql[256];
00497    char keys[256];
00498    char vals[256];
00499    SQLLEN rowcount=0;
00500    const char *newparam, *newval;
00501    int res;
00502    va_list aq;
00503    struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
00504 
00505    va_copy(cps.ap, ap);
00506    va_copy(aq, ap);
00507    
00508    if (!table)
00509       return -1;
00510 
00511    obj = ast_odbc_request_obj(database, 0);
00512    if (!obj)
00513       return -1;
00514 
00515    newparam = va_arg(aq, const char *);
00516    if (!newparam)  {
00517       ast_odbc_release_obj(obj);
00518       return -1;
00519    }
00520    newval = va_arg(aq, const char *);
00521    snprintf(keys, sizeof(keys), "%s", newparam);
00522    ast_copy_string(vals, "?", sizeof(vals));
00523    while ((newparam = va_arg(aq, const char *))) {
00524       snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
00525       snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
00526       newval = va_arg(aq, const char *);
00527    }
00528    va_end(aq);
00529    snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
00530 
00531    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00532 
00533    if (!stmt) {
00534       ast_odbc_release_obj(obj);
00535       return -1;
00536    }
00537 
00538    res = SQLRowCount(stmt, &rowcount);
00539    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00540    ast_odbc_release_obj(obj);
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    return -1;
00551 }
00552 
00553 /*!
00554  * \brief Excute an DELETE query
00555  * \param database
00556  * \param table
00557  * \param keyfield where clause field
00558  * \param lookup value of field for where clause
00559  * \param ap list containing one or more field/value set(s)
00560  *
00561  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
00562  * control the number of records to change. Additional params to match rows are stored in ap list.
00563  * Sub-in the values to the prepared statement and execute it.
00564  *
00565  * \retval number of rows affected
00566  * \retval -1 on failure
00567 */
00568 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00569 {
00570    struct odbc_obj *obj;
00571    SQLHSTMT stmt;
00572    char sql[256];
00573    SQLLEN rowcount=0;
00574    const char *newparam, *newval;
00575    int res;
00576    va_list aq;
00577    struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00578 
00579    va_copy(cps.ap, ap);
00580    va_copy(aq, ap);
00581    
00582    if (!table)
00583       return -1;
00584 
00585    obj = ast_odbc_request_obj(database, 0);
00586    if (!obj)
00587       return -1;
00588 
00589    snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
00590    while((newparam = va_arg(aq, const char *))) {
00591       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
00592       newval = va_arg(aq, const char *);
00593    }
00594    va_end(aq);
00595    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
00596 
00597    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00598 
00599    if (!stmt) {
00600       ast_odbc_release_obj(obj);
00601       return -1;
00602    }
00603 
00604    res = SQLRowCount(stmt, &rowcount);
00605    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00606    ast_odbc_release_obj(obj);
00607 
00608    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00609       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00610       return -1;
00611    }
00612 
00613    if (rowcount >= 0)
00614       return (int)rowcount;
00615 
00616    return -1;
00617 }
00618 
00619 
00620 struct config_odbc_obj {
00621    char *sql;
00622    unsigned long cat_metric;
00623    char category[128];
00624    char var_name[128];
00625    char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
00626    SQLLEN err;
00627 };
00628 
00629 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
00630 {
00631    struct config_odbc_obj *q = data;
00632    SQLHSTMT sth;
00633    int res;
00634 
00635    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
00636    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00637       ast_verb(4, "Failure in AllocStatement %d\n", res);
00638       return NULL;
00639    }
00640 
00641    res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
00642    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00643       ast_verb(4, "Error in PREPARE %d\n", res);
00644       SQLFreeHandle(SQL_HANDLE_STMT, sth);
00645       return NULL;
00646    }
00647 
00648    SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
00649    SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
00650    SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
00651    SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
00652 
00653    return sth;
00654 }
00655 
00656 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)
00657 {
00658    struct ast_variable *new_v;
00659    struct ast_category *cur_cat;
00660    int res = 0;
00661    struct odbc_obj *obj;
00662    char sqlbuf[1024] = "";
00663    char *sql = sqlbuf;
00664    size_t sqlleft = sizeof(sqlbuf);
00665    unsigned int last_cat_metric = 0;
00666    SQLSMALLINT rowcount = 0;
00667    SQLHSTMT stmt;
00668    char last[128] = "";
00669    struct config_odbc_obj q;
00670    struct ast_flags loader_flags = { 0 };
00671 
00672    memset(&q, 0, sizeof(q));
00673 
00674    if (!file || !strcmp (file, "res_config_odbc.conf"))
00675       return NULL;      /* cant configure myself with myself ! */
00676 
00677    obj = ast_odbc_request_obj(database, 0);
00678    if (!obj)
00679       return NULL;
00680 
00681    ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
00682    ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
00683    ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00684    q.sql = sqlbuf;
00685 
00686    stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
00687 
00688    if (!stmt) {
00689       ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
00690       ast_odbc_release_obj(obj);
00691       return NULL;
00692    }
00693 
00694    res = SQLNumResultCols(stmt, &rowcount);
00695 
00696    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00697       ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
00698       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00699       ast_odbc_release_obj(obj);
00700       return NULL;
00701    }
00702 
00703    if (!rowcount) {
00704       ast_log(LOG_NOTICE, "found nothing\n");
00705       ast_odbc_release_obj(obj);
00706       return cfg;
00707    }
00708 
00709    cur_cat = ast_config_get_current_category(cfg);
00710 
00711    while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
00712       if (!strcmp (q.var_name, "#include")) {
00713          if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
00714             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00715             ast_odbc_release_obj(obj);
00716             return NULL;
00717          }
00718          continue;
00719       } 
00720       if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
00721          cur_cat = ast_category_new(q.category, "", 99999);
00722          if (!cur_cat) {
00723             ast_log(LOG_WARNING, "Out of memory!\n");
00724             break;
00725          }
00726          strcpy(last, q.category);
00727          last_cat_metric   = q.cat_metric;
00728          ast_category_append(cfg, cur_cat);
00729       }
00730 
00731       new_v = ast_variable_new(q.var_name, q.var_val, "");
00732       ast_variable_append(cur_cat, new_v);
00733    }
00734 
00735    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00736    ast_odbc_release_obj(obj);
00737    return cfg;
00738 }
00739 
00740 #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)
00741 #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)
00742 
00743 static int require_odbc(const char *database, const char *table, va_list ap)
00744 {
00745    struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
00746    struct odbc_cache_columns *col;
00747    char *elm;
00748    int type, size;
00749 
00750    if (!tableptr) {
00751       return -1;
00752    }
00753 
00754    while ((elm = va_arg(ap, char *))) {
00755       type = va_arg(ap, require_type);
00756       size = va_arg(ap, int);
00757       /* Check if the field matches the criteria */
00758       AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
00759          if (strcmp(col->name, elm) == 0) {
00760             /* Type check, first.  Some fields are more particular than others */
00761             switch (col->type) {
00762             case SQL_CHAR:
00763             case SQL_VARCHAR:
00764             case SQL_LONGVARCHAR:
00765 #ifdef HAVE_ODBC_WCHAR
00766             case SQL_WCHAR:
00767             case SQL_WVARCHAR:
00768             case SQL_WLONGVARCHAR:
00769 #endif
00770             case SQL_BINARY:
00771             case SQL_VARBINARY:
00772             case SQL_LONGVARBINARY:
00773             case SQL_GUID:
00774 #define CHECK_SIZE(n) \
00775                   if (col->size < n) {      \
00776                      warn_length(col, n);  \
00777                   }                         \
00778                   break;
00779                switch (type) {
00780                case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
00781                case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
00782                case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
00783                case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
00784                case RQ_UINTEGER3:                /*    16777215 */
00785                case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
00786                case RQ_DATE:                     /*  2008-06-09 */
00787                case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
00788                case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
00789                case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
00790                case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
00791                case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
00792                case RQ_FLOAT:
00793                case RQ_CHAR:      CHECK_SIZE(size)
00794                }
00795 #undef CHECK_SIZE
00796                break;
00797             case SQL_TYPE_DATE:
00798                if (type != RQ_DATE) {
00799                   warn_type(col, type);
00800                }
00801                break;
00802             case SQL_TYPE_TIMESTAMP:
00803             case SQL_TIMESTAMP:
00804                if (type != RQ_DATE && type != RQ_DATETIME) {
00805                   warn_type(col, type);
00806                }
00807                break;
00808             case SQL_BIT:
00809                warn_length(col, size);
00810                break;
00811 #define WARN_TYPE_OR_LENGTH(n)   \
00812                   if (!ast_rq_is_int(type)) {  \
00813                      warn_type(col, type);    \
00814                   } else {                     \
00815                      warn_length(col, n);  \
00816                   }
00817             case SQL_TINYINT:
00818                if (type != RQ_UINTEGER1) {
00819                   WARN_TYPE_OR_LENGTH(size)
00820                }
00821                break;
00822             case SQL_C_STINYINT:
00823                if (type != RQ_INTEGER1) {
00824                   WARN_TYPE_OR_LENGTH(size)
00825                }
00826                break;
00827             case SQL_C_USHORT:
00828                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
00829                   WARN_TYPE_OR_LENGTH(size)
00830                }
00831                break;
00832             case SQL_SMALLINT:
00833             case SQL_C_SSHORT:
00834                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
00835                   WARN_TYPE_OR_LENGTH(size)
00836                }
00837                break;
00838             case SQL_C_ULONG:
00839                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
00840                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
00841                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
00842                   type != RQ_INTEGER4) {
00843                   WARN_TYPE_OR_LENGTH(size)
00844                }
00845                break;
00846             case SQL_INTEGER:
00847             case SQL_C_SLONG:
00848                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
00849                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
00850                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
00851                   type != RQ_INTEGER4) {
00852                   WARN_TYPE_OR_LENGTH(size)
00853                }
00854                break;
00855             case SQL_C_UBIGINT:
00856                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
00857                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
00858                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
00859                   type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
00860                   type != RQ_INTEGER8) {
00861                   WARN_TYPE_OR_LENGTH(size)
00862                }
00863                break;
00864             case SQL_BIGINT:
00865             case SQL_C_SBIGINT:
00866                if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
00867                   type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
00868                   type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
00869                   type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
00870                   type != RQ_INTEGER8) {
00871                   WARN_TYPE_OR_LENGTH(size)
00872                }
00873                break;
00874 #undef WARN_TYPE_OR_LENGTH
00875             case SQL_NUMERIC:
00876             case SQL_DECIMAL:
00877             case SQL_FLOAT:
00878             case SQL_REAL:
00879             case SQL_DOUBLE:
00880                if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
00881                   warn_type(col, type);
00882                }
00883                break;
00884             default:
00885                ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
00886             }
00887             break;
00888          }
00889       }
00890       if (!col) {
00891          ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
00892       }
00893    }
00894    va_end(ap);
00895    AST_RWLIST_UNLOCK(&tableptr->columns);
00896    return 0;
00897 }
00898 #undef warn_length
00899 #undef warn_type
00900 
00901 static struct ast_config_engine odbc_engine = {
00902    .name = "odbc",
00903    .load_func = config_odbc,
00904    .realtime_func = realtime_odbc,
00905    .realtime_multi_func = realtime_multi_odbc,
00906    .store_func = store_odbc,
00907    .destroy_func = destroy_odbc,
00908    .update_func = update_odbc,
00909    .require_func = require_odbc,
00910    .unload_func = ast_odbc_clear_cache,
00911 };
00912 
00913 static int unload_module (void)
00914 {
00915    ast_config_engine_deregister(&odbc_engine);
00916 
00917    ast_verb(1, "res_config_odbc unloaded.\n");
00918    return 0;
00919 }
00920 
00921 static int load_module (void)
00922 {
00923    ast_config_engine_register(&odbc_engine);
00924    ast_verb(1, "res_config_odbc loaded.\n");
00925    return 0;
00926 }
00927 
00928 static int reload_module(void)
00929 {
00930    return 0;
00931 }
00932 
00933 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "Realtime ODBC configuration",
00934       .load = load_module,
00935       .unload = unload_module,
00936       .reload = reload_module,
00937       );

Generated on Wed Aug 18 22:33:55 2010 for Asterisk - the Open Source PBX by  doxygen 1.4.7