Wed Aug 15 01:24:23 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    <depend>ltdl</depend>
00034    <depend>res_odbc</depend>
00035  ***/
00036 
00037 #include "asterisk.h"
00038 
00039 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 78488 $")
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 ?", table, newparam, op);
00140    while((newparam = va_arg(aq, const char *))) {
00141       op = !strchr(newparam, ' ') ? " =" : "";
00142       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00143          strcasestr(newparam, "LIKE") ? " ESCAPE '\\'" : "");
00144       newval = va_arg(aq, const char *);
00145    }
00146    va_end(aq);
00147 
00148    stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00149 
00150    if (!stmt) {
00151       ast_odbc_release_obj(obj);
00152       return NULL;
00153    }
00154 
00155    res = SQLNumResultCols(stmt, &colcount);
00156    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00157       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00158       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00159       ast_odbc_release_obj(obj);
00160       return NULL;
00161    }
00162 
00163    res = SQLFetch(stmt);
00164    if (res == SQL_NO_DATA) {
00165       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00166       ast_odbc_release_obj(obj);
00167       return NULL;
00168    }
00169    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00170       ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00171       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00172       ast_odbc_release_obj(obj);
00173       return NULL;
00174    }
00175    for (x = 0; x < colcount; x++) {
00176       rowdata[0] = '\0';
00177       collen = sizeof(coltitle);
00178       res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
00179                &datatype, &colsize, &decimaldigits, &nullable);
00180       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00181          ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00182          if (var)
00183             ast_variables_destroy(var);
00184          ast_odbc_release_obj(obj);
00185          return NULL;
00186       }
00187 
00188       indicator = 0;
00189       res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00190       if (indicator == SQL_NULL_DATA)
00191          continue;
00192 
00193       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00194          ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00195          if (var)
00196             ast_variables_destroy(var);
00197          ast_odbc_release_obj(obj);
00198          return NULL;
00199       }
00200       stringp = rowdata;
00201       while(stringp) {
00202          chunk = strsep(&stringp, ";");
00203          if (!ast_strlen_zero(ast_strip(chunk))) {
00204             if (prev) {
00205                prev->next = ast_variable_new(coltitle, chunk);
00206                if (prev->next)
00207                   prev = prev->next;
00208             } else 
00209                prev = var = ast_variable_new(coltitle, chunk);
00210          }
00211       }
00212    }
00213 
00214 
00215    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00216    ast_odbc_release_obj(obj);
00217    return var;
00218 }
00219 
00220 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
00221 {
00222    struct odbc_obj *obj;
00223    SQLHSTMT stmt;
00224    char sql[1024];
00225    char coltitle[256];
00226    char rowdata[2048];
00227    const char *initfield=NULL;
00228    char *op;
00229    const char *newparam, *newval;
00230    char *stringp;
00231    char *chunk;
00232    SQLSMALLINT collen;
00233    int res;
00234    int x;
00235    struct ast_variable *var=NULL;
00236    struct ast_config *cfg=NULL;
00237    struct ast_category *cat=NULL;
00238    struct ast_realloca ra;
00239    SQLULEN colsize;
00240    SQLSMALLINT colcount=0;
00241    SQLSMALLINT datatype;
00242    SQLSMALLINT decimaldigits;
00243    SQLSMALLINT nullable;
00244    SQLLEN indicator;
00245    struct custom_prepare_struct cps = { .sql = sql };
00246    va_list aq;
00247 
00248    va_copy(cps.ap, ap);
00249    va_copy(aq, ap);
00250 
00251    if (!table)
00252       return NULL;
00253    memset(&ra, 0, sizeof(ra));
00254 
00255    obj = ast_odbc_request_obj(database, 0);
00256    if (!obj)
00257       return NULL;
00258 
00259    newparam = va_arg(aq, const char *);
00260    if (!newparam)  {
00261       ast_odbc_release_obj(obj);
00262       return NULL;
00263    }
00264    initfield = ast_strdupa(newparam);
00265    if ((op = strchr(initfield, ' '))) 
00266       *op = '\0';
00267    newval = va_arg(aq, const char *);
00268    op = !strchr(newparam, ' ') ? " =" : "";
00269    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?", table, newparam, op);
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") ? " 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 Wed Aug 15 01:24:23 2007 for Asterisk - the Open Source PBX by  doxygen 1.5.3