Asterisk.org: Developer Documentation (28 Oct 2009)

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