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

Generated on Thu Oct 8 21:56:04 2009 for Asterisk - the Open Source PBX by  doxygen 1.5.6