Fri Sep 29 11:12:31 2006

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

Generated on Fri Sep 29 11:12:31 2006 for Asterisk - the Open Source PBX by  doxygen 1.4.7