Fri Aug 24 02:22:16 2007

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

Generated on Fri Aug 24 02:22:17 2007 for Asterisk - the Open Source PBX by  doxygen 1.5.1