Mon Mar 31 07:38:04 2008

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

Generated on Mon Mar 31 07:38:04 2008 for Asterisk - the Open Source PBX by  doxygen 1.5.1