Sat Mar 24 23:26:06 2007

Asterisk developer's documentation


res_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  * res_odbc.c <ODBC resource manager>
00009  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
00010  *
00011  * See http://www.asterisk.org for more information about
00012  * the Asterisk project. Please do not directly contact
00013  * any of the maintainers of this project for assistance;
00014  * the project provides a web site, mailing lists and IRC
00015  * channels for your use.
00016  *
00017  * This program is free software, distributed under the terms of
00018  * the GNU General Public License Version 2. See the LICENSE file
00019  * at the top of the source tree.
00020  */
00021 
00022 
00023 /*! \file
00024  *
00025  * \brief ODBC resource manager
00026  * 
00027  * \arg See also: \ref cdr_odbc
00028  *
00029  */
00030 
00031 #include <stdio.h>
00032 #include <stdlib.h>
00033 #include <unistd.h>
00034 #include <string.h>
00035 
00036 #include "asterisk.h"
00037 
00038 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 9073 $")
00039 
00040 #include "asterisk/file.h"
00041 #include "asterisk/logger.h"
00042 #include "asterisk/channel.h"
00043 #include "asterisk/config.h"
00044 #include "asterisk/options.h"
00045 #include "asterisk/pbx.h"
00046 #include "asterisk/module.h"
00047 #include "asterisk/cli.h"
00048 #include "asterisk/lock.h"
00049 #include "asterisk/res_odbc.h"
00050 #define MAX_ODBC_HANDLES 25
00051 
00052 struct odbc_list
00053 {
00054    char name[80];
00055    odbc_obj *obj;
00056    int used;
00057 };
00058 
00059 static struct odbc_list ODBC_REGISTRY[MAX_ODBC_HANDLES];
00060 
00061 
00062 static void odbc_destroy(void)
00063 {
00064    int x = 0;
00065 
00066    for (x = 0; x < MAX_ODBC_HANDLES; x++) {
00067       if (ODBC_REGISTRY[x].obj) {
00068          destroy_odbc_obj(&ODBC_REGISTRY[x].obj);
00069          ODBC_REGISTRY[x].obj = NULL;
00070       }
00071    }
00072 }
00073 
00074 static odbc_obj *odbc_read(struct odbc_list *registry, const char *name)
00075 {
00076    int x = 0;
00077    for (x = 0; x < MAX_ODBC_HANDLES; x++) {
00078       if (registry[x].used && !strcmp(registry[x].name, name)) {
00079          return registry[x].obj;
00080       }
00081    }
00082    return NULL;
00083 }
00084 
00085 static int odbc_write(struct odbc_list *registry, char *name, odbc_obj *obj)
00086 {
00087    int x = 0;
00088    for (x = 0; x < MAX_ODBC_HANDLES; x++) {
00089       if (!registry[x].used) {
00090          ast_copy_string(registry[x].name, name, sizeof(registry[x].name));
00091          registry[x].obj = obj;
00092          registry[x].used = 1;
00093          return 1;
00094       }
00095    }
00096    return 0;
00097 }
00098 
00099 static void odbc_init(void)
00100 {
00101    int x = 0;
00102    for (x = 0; x < MAX_ODBC_HANDLES; x++) {
00103       memset(&ODBC_REGISTRY[x], 0, sizeof(struct odbc_list));
00104    }
00105 }
00106 
00107 static char *tdesc = "ODBC Resource";
00108 /* internal stuff */
00109 
00110 SQLHSTMT odbc_prepare_and_execute(odbc_obj *obj, SQLHSTMT (*prepare_cb)(odbc_obj *obj, void *data), void *data)
00111 {
00112    int res = 0, i, attempt;
00113    SQLINTEGER nativeerror=0, numfields=0;
00114    SQLSMALLINT diagbytes=0;
00115    unsigned char state[10], diagnostic[256];
00116    SQLHSTMT stmt;
00117 
00118    for (attempt = 0; attempt < 2; attempt++) {
00119       /* This prepare callback may do more than just prepare -- it may also
00120        * bind parameters, bind results, etc.  The real key, here, is that
00121        * when we disconnect, all handles become invalid for most databases.
00122        * We must therefore redo everything when we establish a new
00123        * connection. */
00124       stmt = prepare_cb(obj, data);
00125 
00126       if (stmt) {
00127          res = SQLExecute(stmt);
00128          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
00129             if (res == SQL_ERROR) {
00130                SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
00131                for (i=0; i< numfields + 1; i++) {
00132                   SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
00133                   ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
00134                   if (i > 10) {
00135                      ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
00136                      break;
00137                   }
00138                }
00139             }
00140 
00141             ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
00142             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00143 
00144             ast_mutex_lock(&obj->lock);
00145             obj->up = 0;
00146             ast_mutex_unlock(&obj->lock);
00147             odbc_obj_disconnect(obj);
00148             odbc_obj_connect(obj);
00149             continue;
00150          }
00151          break;
00152       }
00153    }
00154 
00155    return stmt;
00156 }
00157 
00158 int odbc_smart_execute(odbc_obj *obj, SQLHSTMT stmt) 
00159 {
00160    int res = 0, i;
00161    SQLINTEGER nativeerror=0, numfields=0;
00162    SQLSMALLINT diagbytes=0;
00163    unsigned char state[10], diagnostic[256];
00164 
00165    res = SQLExecute(stmt);
00166    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
00167       if (res == SQL_ERROR) {
00168          SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
00169          for (i=0; i< numfields + 1; i++) {
00170             SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
00171             ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
00172             if (i > 10) {
00173                ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
00174                break;
00175             }
00176          }
00177       }
00178 /*
00179       ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
00180       ast_mutex_lock(&obj->lock);
00181       obj->up = 0;
00182       ast_mutex_unlock(&obj->lock);
00183       odbc_obj_disconnect(obj);
00184       odbc_obj_connect(obj);
00185       res = SQLExecute(stmt);
00186 */
00187    }
00188    
00189    return res;
00190 }
00191 
00192 
00193 int odbc_smart_direct_execute(odbc_obj *obj, SQLHSTMT stmt, char *sql) 
00194 {
00195    int res = 0;
00196 
00197    res = SQLExecDirect (stmt, (unsigned char *)sql, SQL_NTS);
00198    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00199       ast_log(LOG_WARNING, "SQL Execute error! Attempting a reconnect...\n");
00200       ast_mutex_lock(&obj->lock);
00201       obj->up = 0;
00202       ast_mutex_unlock(&obj->lock);
00203       odbc_obj_disconnect(obj);
00204       odbc_obj_connect(obj);
00205       res = SQLExecDirect (stmt, (unsigned char *)sql, SQL_NTS);
00206    }
00207    
00208    return res;
00209 }
00210 
00211 int odbc_sanity_check(odbc_obj *obj) 
00212 {
00213    char *test_sql = "select 1";
00214    SQLHSTMT stmt;
00215    int res = 0;
00216 
00217    ast_mutex_lock(&obj->lock);
00218    if(obj->up) { /* so you say... let's make sure */
00219       res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
00220       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00221          obj->up = 0; /* Liar!*/
00222       } else {
00223          res = SQLPrepare(stmt, (unsigned char *)test_sql, SQL_NTS);
00224          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00225             obj->up = 0; /* Liar!*/
00226          } else {
00227             res = SQLExecute(stmt);
00228             if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00229                obj->up = 0; /* Liar!*/
00230             }
00231          }
00232       }
00233       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00234    }
00235    ast_mutex_unlock(&obj->lock);
00236 
00237    if(!obj->up) { /* Try to reconnect! */
00238       ast_log(LOG_WARNING, "Connection is down attempting to reconnect...\n");
00239       odbc_obj_disconnect(obj);
00240       odbc_obj_connect(obj);
00241    }
00242    return obj->up;
00243 }
00244 
00245 static int load_odbc_config(void)
00246 {
00247    static char *cfg = "res_odbc.conf";
00248    struct ast_config *config;
00249    struct ast_variable *v;
00250    char *cat, *dsn, *username, *password;
00251    int enabled;
00252    int connect = 0;
00253    char *env_var;
00254 
00255    odbc_obj *obj;
00256 
00257    config = ast_config_load(cfg);
00258    if (config) {
00259       for (cat = ast_category_browse(config, NULL); cat; cat=ast_category_browse(config, cat)) {
00260          if (!strcmp(cat, "ENV")) {
00261             for (v = ast_variable_browse(config, cat); v; v = v->next) {
00262                env_var = malloc(strlen(v->name) + strlen(v->value) + 2);
00263                if (env_var) {
00264                   sprintf(env_var, "%s=%s", v->name, v->value);
00265                   ast_log(LOG_NOTICE, "Adding ENV var: %s=%s\n", v->name, v->value);
00266                   putenv(env_var);
00267                   free(env_var);
00268                }
00269             }
00270 
00271          cat = ast_category_browse(config, cat);
00272          }
00273 
00274          dsn = username = password = NULL;
00275          enabled = 1;
00276          connect = 0;
00277          for (v = ast_variable_browse(config, cat); v; v = v->next) {
00278             if (!strcmp(v->name, "enabled"))
00279                enabled = ast_true(v->value);
00280             if (!strcmp(v->name, "pre-connect"))
00281                connect = ast_true(v->value);
00282             if (!strcmp(v->name, "dsn"))
00283                dsn = v->value;
00284             if (!strcmp(v->name, "username"))
00285                username = v->value;
00286             if (!strcmp(v->name, "password"))
00287                password = v->value;
00288          }
00289 
00290          if (enabled && dsn) {
00291             obj = new_odbc_obj(cat, dsn, username, password);
00292             if (obj) {
00293                register_odbc_obj(cat, obj);
00294                ast_log(LOG_NOTICE, "registered database handle '%s' dsn->[%s]\n", cat, obj->dsn);
00295                if (connect) {
00296                   odbc_obj_connect(obj);
00297                }
00298             } else {
00299                ast_log(LOG_WARNING, "Addition of obj %s failed.\n", cat);
00300             }
00301 
00302          }
00303       }
00304       ast_config_destroy(config);
00305    }
00306    return 0;
00307 }
00308 
00309 int odbc_dump_fd(int fd, odbc_obj *obj)
00310 {
00311    /* make sure the connection is up before we lie to our master.*/
00312    odbc_sanity_check(obj);
00313    ast_cli(fd, "Name: %s\nDSN: %s\nConnected: %s\n\n", obj->name, obj->dsn, obj->up ? "yes" : "no");
00314    return 0;
00315 }
00316 
00317 static int odbc_connect_usage(int fd)
00318 {
00319    ast_cli(fd, "usage odbc connect <DSN>\n");
00320    return 0;
00321 }
00322 
00323 static int odbc_disconnect_usage(int fd)
00324 {
00325    ast_cli(fd, "usage odbc disconnect <DSN>\n");
00326    return 0;
00327 }
00328 
00329 static int odbc_show_command(int fd, int argc, char **argv)
00330 {
00331    odbc_obj *obj;
00332    int x = 0;
00333    
00334    if (!strcmp(argv[1], "show")) {
00335       if (!argv[2] || (argv[2] && !strcmp(argv[2], "all"))) {
00336          for (x = 0; x < MAX_ODBC_HANDLES; x++) {
00337             if (!ODBC_REGISTRY[x].used)
00338                break;
00339             if (ODBC_REGISTRY[x].obj)
00340                odbc_dump_fd(fd, ODBC_REGISTRY[x].obj);
00341          }
00342       } else {
00343          obj = odbc_read(ODBC_REGISTRY, argv[2]);
00344          if (obj)
00345             odbc_dump_fd(fd, obj);
00346       }
00347    }
00348    return 0;
00349 }
00350 
00351 static int odbc_disconnect_command(int fd, int argc, char **argv)
00352 {
00353    odbc_obj *obj;
00354    if (!strcmp(argv[1], "disconnect")) {
00355       if (!argv[2])
00356          return odbc_disconnect_usage(fd);
00357 
00358       obj = odbc_read(ODBC_REGISTRY, argv[2]);
00359       if (obj) {
00360          odbc_obj_disconnect(obj);
00361       }
00362    } 
00363    return 0;
00364 }
00365 
00366 static int odbc_connect_command(int fd, int argc, char **argv)
00367 {
00368    odbc_obj *obj;
00369    if (!argv[1])
00370       return odbc_connect_usage(fd);
00371 
00372    if (!strcmp(argv[1], "connect") || !strcmp(argv[1], "disconnect")) {
00373       if (!argv[2])
00374          return odbc_connect_usage(fd);
00375 
00376       obj = odbc_read(ODBC_REGISTRY, argv[2]);
00377       if (obj) {
00378          odbc_obj_connect(obj);
00379       }
00380    }
00381    return 0;
00382 }
00383 
00384 
00385 static char connect_usage[] =
00386 "Usage: odbc connect <DSN>\n"
00387 "       Connect to ODBC DSN\n";
00388 
00389 static char disconnect_usage[] =
00390 "Usage: odbc connect <DSN>\n"
00391 "       Disconnect from ODBC DSN\n";
00392 
00393 static char show_usage[] =
00394 "Usage: odbc show {DSN}\n"
00395 "       Show ODBC {DSN}\n"
00396 "       Specifying DSN will show that DSN else, all DSNs are shown\n";
00397 
00398 static struct ast_cli_entry odbc_connect_struct =
00399         { { "odbc", "connect", NULL }, odbc_connect_command, "Connect to ODBC DSN", connect_usage };
00400 
00401 
00402 static struct ast_cli_entry odbc_disconnect_struct =
00403         { { "odbc", "disconnect", NULL }, odbc_disconnect_command, "Disconnect from ODBC DSN", disconnect_usage };
00404 
00405 static struct ast_cli_entry odbc_show_struct =
00406         { { "odbc", "show", NULL }, odbc_show_command, "Show ODBC DSN(s)", show_usage };
00407 
00408 /* api calls */
00409 
00410 int register_odbc_obj(char *name, odbc_obj *obj)
00411 {
00412    if (obj != NULL)
00413       return odbc_write(ODBC_REGISTRY, name, obj);
00414    return 0;
00415 }
00416 
00417 odbc_obj *fetch_odbc_obj(const char *name, int check)
00418 {
00419    odbc_obj *obj = NULL;
00420    if((obj = (odbc_obj *) odbc_read(ODBC_REGISTRY, name))) {
00421       if(check)
00422          odbc_sanity_check(obj);
00423    }
00424    return obj;
00425 }
00426 
00427 odbc_obj *new_odbc_obj(char *name, char *dsn, char *username, char *password)
00428 {
00429    static odbc_obj *new;
00430 
00431    if (!(new = calloc(1, sizeof(*new))) || 
00432        !(new->name = malloc(strlen(name) + 1)) || 
00433        !(new->dsn = malloc(strlen(dsn) + 1)))
00434          goto cleanup;
00435 
00436    if (username) {
00437       if (!(new->username = malloc(strlen(username) + 1)))
00438          goto cleanup;
00439       strcpy(new->username, username);
00440    }
00441 
00442    if (password) {
00443       if (!(new->password = malloc(strlen(password) + 1)))
00444          goto cleanup;
00445       strcpy(new->password, password);
00446    }
00447 
00448    strcpy(new->name, name);
00449    strcpy(new->dsn, dsn);
00450    new->env = SQL_NULL_HANDLE;
00451    new->up = 0;
00452    ast_mutex_init(&new->lock);
00453    return new;
00454 
00455 cleanup:
00456    if (new) {
00457       free(new->name);
00458       free(new->dsn);
00459       free(new->username);
00460       free(new->password);
00461 
00462       free(new);  
00463    }
00464 
00465    return NULL;
00466 }
00467 
00468 void destroy_odbc_obj(odbc_obj **obj)
00469 {
00470    odbc_obj_disconnect(*obj);
00471 
00472    ast_mutex_lock(&(*obj)->lock);
00473    SQLFreeHandle(SQL_HANDLE_STMT, (*obj)->stmt);
00474    SQLFreeHandle(SQL_HANDLE_DBC, (*obj)->con);
00475    SQLFreeHandle(SQL_HANDLE_ENV, (*obj)->env);
00476 
00477    free((*obj)->name);
00478    free((*obj)->dsn);
00479    if ((*obj)->username)
00480       free((*obj)->username);
00481    if ((*obj)->password)
00482       free((*obj)->password);
00483    ast_mutex_unlock(&(*obj)->lock);
00484    ast_mutex_destroy(&(*obj)->lock);
00485    free(*obj);
00486 }
00487 
00488 odbc_status odbc_obj_disconnect(odbc_obj *obj)
00489 {
00490    int res;
00491    ast_mutex_lock(&obj->lock);
00492 
00493    res = SQLDisconnect(obj->con);
00494 
00495 
00496    if (res == ODBC_SUCCESS) {
00497       ast_log(LOG_WARNING, "res_odbc: disconnected %d from %s [%s]\n", res, obj->name, obj->dsn);
00498    } else {
00499       ast_log(LOG_WARNING, "res_odbc: %s [%s] already disconnected\n",
00500       obj->name, obj->dsn);
00501    }
00502    obj->up = 0;
00503    ast_mutex_unlock(&obj->lock);
00504    return ODBC_SUCCESS;
00505 }
00506 
00507 odbc_status odbc_obj_connect(odbc_obj *obj)
00508 {
00509    int res;
00510    SQLINTEGER err;
00511    short int mlen;
00512    unsigned char msg[200], stat[10];
00513 
00514    ast_mutex_lock(&obj->lock);
00515 
00516    if (obj->env == SQL_NULL_HANDLE) {
00517       res = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &obj->env);
00518 
00519       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00520          if (option_verbose > 3)
00521             ast_log(LOG_WARNING, "res_odbc: Error AllocHandle\n");
00522          ast_mutex_unlock(&obj->lock);
00523          return ODBC_FAIL;
00524       }
00525 
00526       res = SQLSetEnvAttr(obj->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
00527 
00528       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00529          if (option_verbose > 3)
00530             ast_log(LOG_WARNING, "res_odbc: Error SetEnv\n");
00531          SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
00532          ast_mutex_unlock(&obj->lock);
00533          return ODBC_FAIL;
00534       }
00535 
00536       res = SQLAllocHandle(SQL_HANDLE_DBC, obj->env, &obj->con);
00537 
00538       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00539 
00540          if (option_verbose > 3)
00541             ast_log(LOG_WARNING, "res_odbc: Error AllocHDB %d\n", res);
00542          SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
00543 
00544          ast_mutex_unlock(&obj->lock);
00545          return ODBC_FAIL;
00546       }
00547       SQLSetConnectAttr(obj->con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 10, 0);
00548    }
00549    if(obj->up) {
00550       odbc_obj_disconnect(obj);
00551       ast_log(LOG_NOTICE,"Re-connecting %s\n", obj->name);
00552    }
00553 
00554    ast_log(LOG_NOTICE, "Connecting %s\n", obj->name);
00555 
00556    res = SQLConnect(obj->con,
00557          (SQLCHAR *) obj->dsn, SQL_NTS,
00558          (SQLCHAR *) obj->username, SQL_NTS,
00559          (SQLCHAR *) obj->password, SQL_NTS);
00560 
00561    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00562       SQLGetDiagRec(SQL_HANDLE_DBC, obj->con, 1, stat, &err, msg, 100, &mlen);
00563       SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
00564       ast_mutex_unlock(&obj->lock);
00565       ast_log(LOG_WARNING, "res_odbc: Error SQLConnect=%d errno=%d %s\n", res, (int)err, msg);
00566       return ODBC_FAIL;
00567    } else {
00568 
00569       ast_log(LOG_NOTICE, "res_odbc: Connected to %s [%s]\n", obj->name, obj->dsn);
00570       obj->up = 1;
00571    }
00572 
00573    ast_mutex_unlock(&obj->lock);
00574    return ODBC_SUCCESS;
00575 }
00576 
00577 STANDARD_LOCAL_USER;
00578 
00579 LOCAL_USER_DECL;
00580 
00581 int unload_module(void)
00582 {
00583    STANDARD_HANGUP_LOCALUSERS;
00584    odbc_destroy();
00585    ast_cli_unregister(&odbc_disconnect_struct);
00586    ast_cli_unregister(&odbc_connect_struct);
00587    ast_cli_unregister(&odbc_show_struct);
00588    ast_log(LOG_NOTICE, "res_odbc unloaded.\n");
00589    return 0;
00590 }
00591 
00592 int load_module(void)
00593 {
00594    odbc_init();
00595    load_odbc_config();
00596    ast_cli_register(&odbc_disconnect_struct);
00597    ast_cli_register(&odbc_connect_struct);
00598    ast_cli_register(&odbc_show_struct);
00599    ast_log(LOG_NOTICE, "res_odbc loaded.\n");
00600    return 0;
00601 }
00602 
00603 char *description(void)
00604 {
00605    return tdesc;
00606 }
00607 
00608 int usecount(void)
00609 {
00610    int res;
00611    STANDARD_USECOUNT(res);
00612    return res;
00613 }
00614 
00615 char *key()
00616 {
00617    return ASTERISK_GPL_KEY;
00618 }

Generated on Sat Mar 24 23:26:06 2007 for Asterisk - the Open Source PBX by  doxygen 1.4.6