Fri Apr 24 16:26:01 2009

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: 129741 $")
00040 
00041 #include <stdio.h>
00042 #include <stdlib.h>
00043 #include <unistd.h>
00044 #include <string.h>
00045 
00046 #include "asterisk/file.h"
00047 #include "asterisk/logger.h"
00048 #include "asterisk/channel.h"
00049 #include "asterisk/pbx.h"
00050 #include "asterisk/config.h"
00051 #include "asterisk/module.h"
00052 #include "asterisk/lock.h"
00053 #include "asterisk/options.h"
00054 #include "asterisk/res_odbc.h"
00055 #include "asterisk/utils.h"
00056 
00057 struct custom_prepare_struct {
00058    const char *sql;
00059    const char *extra;
00060    va_list ap;
00061 };
00062 
00063 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
00064 {
00065    int res, x = 1;
00066    struct custom_prepare_struct *cps = data;
00067    const char *newparam, *newval;
00068    SQLHSTMT stmt;
00069    va_list ap;
00070 
00071    va_copy(ap, cps->ap);
00072 
00073    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00074    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00075       ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00076       return NULL;
00077    }
00078 
00079    res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
00080    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00081       ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
00082       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00083       return NULL;
00084    }
00085 
00086    while ((newparam = va_arg(ap, const char *))) {
00087       newval = va_arg(ap, const char *);
00088       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00089    }
00090    va_end(ap);
00091 
00092    if (!ast_strlen_zero(cps->extra))
00093       SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
00094    return stmt;
00095 }
00096 
00097 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
00098 {
00099    struct odbc_obj *obj;
00100    SQLHSTMT stmt;
00101    char sql[1024];
00102    char coltitle[256];
00103    char rowdata[2048];
00104    char *op;
00105    const char *newparam, *newval;
00106    char *stringp;
00107    char *chunk;
00108    SQLSMALLINT collen;
00109    int res;
00110    int x;
00111    struct ast_variable *var=NULL, *prev=NULL;
00112    SQLULEN colsize;
00113    SQLSMALLINT colcount=0;
00114    SQLSMALLINT datatype;
00115    SQLSMALLINT decimaldigits;
00116    SQLSMALLINT nullable;
00117    SQLLEN indicator;
00118    va_list aq;
00119    struct custom_prepare_struct cps = { .sql = sql };
00120 
00121    va_copy(cps.ap, ap);
00122    va_copy(aq, ap);
00123 
00124    if (!table)
00125       return NULL;
00126 
00127    obj = ast_odbc_request_obj(database, 0);
00128 
00129    if (!obj) {
00130       ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
00131       return NULL;
00132    }
00133 
00134    newparam = va_arg(aq, const char *);
00135    if (!newparam) {
00136       ast_odbc_release_obj(obj);
00137       return NULL;
00138    }
00139    newval = va_arg(aq, const char *);
00140    op = !strchr(newparam, ' ') ? " =" : "";
00141    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00142       strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00143    while((newparam = va_arg(aq, const char *))) {
00144       op = !strchr(newparam, ' ') ? " =" : "";
00145       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00146          strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00147       newval = va_arg(aq, const char *);
00148    }
00149    va_end(aq);
00150 
00151    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00152 
00153    if (!stmt) {
00154       ast_odbc_release_obj(obj);
00155       return NULL;
00156    }
00157 
00158    res = SQLNumResultCols(stmt, &colcount);
00159    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00160       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00161       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00162       ast_odbc_release_obj(obj);
00163       return NULL;
00164    }
00165 
00166    res = SQLFetch(stmt);
00167    if (res == SQL_NO_DATA) {
00168       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00169       ast_odbc_release_obj(obj);
00170       return NULL;
00171    }
00172    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00173       ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00174       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00175       ast_odbc_release_obj(obj);
00176       return NULL;
00177    }
00178    for (x = 0; x < colcount; x++) {
00179       rowdata[0] = '\0';
00180       collen = sizeof(coltitle);
00181       res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00182                &datatype, &colsize, &decimaldigits, &nullable);
00183       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00184          ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00185          if (var)
00186             ast_variables_destroy(var);
00187          ast_odbc_release_obj(obj);
00188          return NULL;
00189       }
00190 
00191       indicator = 0;
00192       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00193       if (indicator == SQL_NULL_DATA)
00194          continue;
00195 
00196       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00197          ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00198          if (var)
00199             ast_variables_destroy(var);
00200          ast_odbc_release_obj(obj);
00201          return NULL;
00202       }
00203       stringp = rowdata;
00204       while(stringp) {
00205          chunk = strsep(&stringp, ";");
00206          if (!ast_strlen_zero(ast_strip(chunk))) {
00207             if (prev) {
00208                prev->next = ast_variable_new(coltitle, chunk);
00209                if (prev->next)
00210                   prev = prev->next;
00211             } else 
00212                prev = var = ast_variable_new(coltitle, chunk);
00213          }
00214       }
00215    }
00216 
00217 
00218    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00219    ast_odbc_release_obj(obj);
00220    return var;
00221 }
00222 
00223 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
00224 {
00225    struct odbc_obj *obj;
00226    SQLHSTMT stmt;
00227    char sql[1024];
00228    char coltitle[256];
00229    char rowdata[2048];
00230    const char *initfield=NULL;
00231    char *op;
00232    const char *newparam, *newval;
00233    char *stringp;
00234    char *chunk;
00235    SQLSMALLINT collen;
00236    int res;
00237    int x;
00238    struct ast_variable *var=NULL;
00239    struct ast_config *cfg=NULL;
00240    struct ast_category *cat=NULL;
00241    struct ast_realloca ra;
00242    SQLULEN colsize;
00243    SQLSMALLINT colcount=0;
00244    SQLSMALLINT datatype;
00245    SQLSMALLINT decimaldigits;
00246    SQLSMALLINT nullable;
00247    SQLLEN indicator;
00248    struct custom_prepare_struct cps = { .sql = sql };
00249    va_list aq;
00250 
00251    va_copy(cps.ap, ap);
00252    va_copy(aq, ap);
00253 
00254    if (!table)
00255       return NULL;
00256    memset(&ra, 0, sizeof(ra));
00257 
00258    obj = ast_odbc_request_obj(database, 0);
00259    if (!obj)
00260       return NULL;
00261 
00262    newparam = va_arg(aq, const char *);
00263    if (!newparam)  {
00264       ast_odbc_release_obj(obj);
00265       return NULL;
00266    }
00267    initfield = ast_strdupa(newparam);
00268    if ((op = strchr(initfield, ' '))) 
00269       *op = '\0';
00270    newval = va_arg(aq, const char *);
00271    op = !strchr(newparam, ' ') ? " =" : "";
00272    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00273       strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00274    while((newparam = va_arg(aq, const char *))) {
00275       op = !strchr(newparam, ' ') ? " =" : "";
00276       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00277          strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00278       newval = va_arg(aq, const char *);
00279    }
00280    if (initfield)
00281       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00282    va_end(aq);
00283 
00284    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00285 
00286    if (!stmt) {
00287       ast_odbc_release_obj(obj);
00288       return NULL;
00289    }
00290 
00291    res = SQLNumResultCols(stmt, &colcount);
00292    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00293       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00294       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00295       ast_odbc_release_obj(obj);
00296       return NULL;
00297    }
00298 
00299    cfg = ast_config_new();
00300    if (!cfg) {
00301       ast_log(LOG_WARNING, "Out of memory!\n");
00302       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00303       ast_odbc_release_obj(obj);
00304       return NULL;
00305    }
00306 
00307    while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
00308       var = NULL;
00309       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00310          ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00311          continue;
00312       }
00313       cat = ast_category_new("");
00314       if (!cat) {
00315          ast_log(LOG_WARNING, "Out of memory!\n");
00316          continue;
00317       }
00318       for (x=0;x<colcount;x++) {
00319          rowdata[0] = '\0';
00320          collen = sizeof(coltitle);
00321          res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00322                   &datatype, &colsize, &decimaldigits, &nullable);
00323          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00324             ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00325             ast_category_destroy(cat);
00326             continue;
00327          }
00328 
00329          indicator = 0;
00330          res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00331          if (indicator == SQL_NULL_DATA)
00332             continue;
00333 
00334          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00335             ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00336             ast_category_destroy(cat);
00337             continue;
00338          }
00339          stringp = rowdata;
00340          while(stringp) {
00341             chunk = strsep(&stringp, ";");
00342             if (!ast_strlen_zero(ast_strip(chunk))) {
00343                if (initfield && !strcmp(initfield, coltitle))
00344                   ast_category_rename(cat, chunk);
00345                var = ast_variable_new(coltitle, chunk);
00346                ast_variable_append(cat, var);
00347             }
00348          }
00349       }
00350       ast_category_append(cfg, cat);
00351    }
00352 
00353    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00354    ast_odbc_release_obj(obj);
00355    return cfg;
00356 }
00357 
00358 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00359 {
00360    struct odbc_obj *obj;
00361    SQLHSTMT stmt;
00362    char sql[256];
00363    SQLLEN rowcount=0;
00364    const char *newparam, *newval;
00365    int res;
00366    va_list aq;
00367    struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00368 
00369    va_copy(cps.ap, ap);
00370    va_copy(aq, ap);
00371    
00372    if (!table)
00373       return -1;
00374 
00375    obj = ast_odbc_request_obj(database, 0);
00376    if (!obj)
00377       return -1;
00378 
00379    newparam = va_arg(aq, const char *);
00380    if (!newparam)  {
00381       ast_odbc_release_obj(obj);
00382       return -1;
00383    }
00384    newval = va_arg(aq, const char *);
00385    snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
00386    while((newparam = va_arg(aq, const char *))) {
00387       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
00388       newval = va_arg(aq, const char *);
00389    }
00390    va_end(aq);
00391    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
00392 
00393    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00394 
00395    if (!stmt) {
00396       ast_odbc_release_obj(obj);
00397       return -1;
00398    }
00399 
00400    res = SQLRowCount(stmt, &rowcount);
00401    SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00402    ast_odbc_release_obj(obj);
00403 
00404    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00405       ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00406       return -1;
00407    }
00408 
00409    if (rowcount >= 0)
00410       return (int)rowcount;
00411 
00412    return -1;
00413 }
00414 
00415 struct config_odbc_obj {
00416    char *sql;
00417    unsigned long cat_metric;
00418    char category[128];
00419    char var_name[128];
00420    char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
00421    SQLLEN err;
00422 };
00423 
00424 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
00425 {
00426    struct config_odbc_obj *q = data;
00427    SQLHSTMT sth;
00428    int res;
00429 
00430    res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
00431    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00432       if (option_verbose > 3)
00433          ast_verbose( VERBOSE_PREFIX_4 "Failure in AllocStatement %d\n", res);
00434       return NULL;
00435    }
00436 
00437    res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
00438    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00439       if (option_verbose > 3)
00440          ast_verbose( VERBOSE_PREFIX_4 "Error in PREPARE %d\n", res);
00441       SQLFreeHandle(SQL_HANDLE_STMT, sth);
00442       return NULL;
00443    }
00444 
00445    SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
00446    SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
00447    SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
00448    SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
00449 
00450    return sth;
00451 }
00452 
00453 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *cfg, int withcomments)
00454 {
00455    struct ast_variable *new_v;
00456    struct ast_category *cur_cat;
00457    int res = 0;
00458    struct odbc_obj *obj;
00459    char sqlbuf[1024] = "";
00460    char *sql = sqlbuf;
00461    size_t sqlleft = sizeof(sqlbuf);
00462    unsigned int last_cat_metric = 0;
00463    SQLSMALLINT rowcount = 0;
00464    SQLHSTMT stmt;
00465    char last[128] = "";
00466    struct config_odbc_obj q;
00467 
00468    memset(&q, 0, sizeof(q));
00469 
00470    if (!file || !strcmp (file, "res_config_odbc.conf"))
00471       return NULL;      /* cant configure myself with myself ! */
00472 
00473    obj = ast_odbc_request_obj(database, 0);
00474    if (!obj)
00475       return NULL;
00476 
00477    ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
00478    ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
00479    ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00480    q.sql = sqlbuf;
00481 
00482    stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
00483 
00484    if (!stmt) {
00485       ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
00486       ast_odbc_release_obj(obj);
00487       return NULL;
00488    }
00489 
00490    res = SQLNumResultCols(stmt, &rowcount);
00491 
00492    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00493       ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
00494       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00495       ast_odbc_release_obj(obj);
00496       return NULL;
00497    }
00498 
00499    if (!rowcount) {
00500       ast_log(LOG_NOTICE, "found nothing\n");
00501       ast_odbc_release_obj(obj);
00502       return cfg;
00503    }
00504 
00505    cur_cat = ast_config_get_current_category(cfg);
00506 
00507    while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
00508       if (!strcmp (q.var_name, "#include")) {
00509          if (!ast_config_internal_load(q.var_val, cfg, 0)) {
00510             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00511             ast_odbc_release_obj(obj);
00512             return NULL;
00513          }
00514          continue;
00515       } 
00516       if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
00517          cur_cat = ast_category_new(q.category);
00518          if (!cur_cat) {
00519             ast_log(LOG_WARNING, "Out of memory!\n");
00520             break;
00521          }
00522          strcpy(last, q.category);
00523          last_cat_metric   = q.cat_metric;
00524          ast_category_append(cfg, cur_cat);
00525       }
00526 
00527       new_v = ast_variable_new(q.var_name, q.var_val);
00528       ast_variable_append(cur_cat, new_v);
00529    }
00530 
00531    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00532    ast_odbc_release_obj(obj);
00533    return cfg;
00534 }
00535 
00536 static struct ast_config_engine odbc_engine = {
00537    .name = "odbc",
00538    .load_func = config_odbc,
00539    .realtime_func = realtime_odbc,
00540    .realtime_multi_func = realtime_multi_odbc,
00541    .update_func = update_odbc
00542 };
00543 
00544 static int unload_module (void)
00545 {
00546    ast_module_user_hangup_all();
00547    ast_config_engine_deregister(&odbc_engine);
00548    if (option_verbose)
00549       ast_verbose("res_config_odbc unloaded.\n");
00550    return 0;
00551 }
00552 
00553 static int load_module (void)
00554 {
00555    ast_config_engine_register(&odbc_engine);
00556    if (option_verbose)
00557       ast_verbose("res_config_odbc loaded.\n");
00558    return 0;
00559 }
00560 
00561 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "ODBC Configuration",
00562       .load = load_module,
00563       .unload = unload_module,
00564       );

Generated on Fri Apr 24 16:26:01 2009 for Asterisk - the Open Source PBX by  doxygen 1.4.7