Wed Oct 28 15:47:55 2009

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: 59299 $")
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             stmt = NULL;
00144 
00145             ast_mutex_lock(&obj->lock);
00146             obj->up = 0;
00147             ast_mutex_unlock(&obj->lock);
00148             odbc_obj_disconnect(obj);
00149             odbc_obj_connect(obj);
00150             continue;
00151          }
00152          break;
00153       }
00154    }
00155 
00156    return stmt;
00157 }
00158 
00159 int odbc_smart_execute(odbc_obj *obj, SQLHSTMT stmt) 
00160 {
00161    int res = 0, i;
00162    SQLINTEGER nativeerror=0, numfields=0;
00163    SQLSMALLINT diagbytes=0;
00164    unsigned char state[10], diagnostic[256];
00165 
00166    res = SQLExecute(stmt);
00167    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
00168       if (res == SQL_ERROR) {
00169          SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
00170          for (i=0; i< numfields + 1; i++) {
00171             SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
00172             ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
00173             if (i > 10) {
00174                ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
00175                break;
00176             }
00177          }
00178       }
00179 /*
00180       ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
00181       ast_mutex_lock(&obj->lock);
00182       obj->up = 0;
00183       ast_mutex_unlock(&obj->lock);
00184       odbc_obj_disconnect(obj);
00185       odbc_obj_connect(obj);
00186       res = SQLExecute(stmt);
00187 */
00188    }
00189    
00190    return res;
00191 }
00192 
00193 
00194 int odbc_smart_direct_execute(odbc_obj *obj, SQLHSTMT stmt, char *sql) 
00195 {
00196    int res = 0;
00197 
00198    res = SQLExecDirect (stmt, (unsigned char *)sql, SQL_NTS);
00199    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00200       ast_log(LOG_WARNING, "SQL Execute error! Attempting a reconnect...\n");
00201       ast_mutex_lock(&obj->lock);
00202       obj->up = 0;
00203       ast_mutex_unlock(&obj->lock);
00204       odbc_obj_disconnect(obj);
00205       odbc_obj_connect(obj);
00206       res = SQLExecDirect (stmt, (unsigned char *)sql, SQL_NTS);
00207    }
00208    
00209    return res;
00210 }
00211 
00212 int odbc_sanity_check(odbc_obj *obj) 
00213 {
00214    char *test_sql = "select 1";
00215    SQLHSTMT stmt;
00216    int res = 0;
00217 
00218    ast_mutex_lock(&obj->lock);
00219    if(obj->up) { /* so you say... let's make sure */
00220       res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
00221       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00222          obj->up = 0; /* Liar!*/
00223       } else {
00224          res = SQLPrepare(stmt, (unsigned char *)test_sql, SQL_NTS);
00225          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00226             obj->up = 0; /* Liar!*/
00227          } else {
00228             res = SQLExecute(stmt);
00229             if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00230                obj->up = 0; /* Liar!*/
00231             }
00232          }
00233       }
00234       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00235    }
00236    ast_mutex_unlock(&obj->lock);
00237 
00238    if(!obj->up) { /* Try to reconnect! */
00239       ast_log(LOG_WARNING, "Connection is down attempting to reconnect...\n");
00240       odbc_obj_disconnect(obj);
00241       odbc_obj_connect(obj);
00242    }
00243    return obj->up;
00244 }
00245 
00246 static int load_odbc_config(void)
00247 {
00248    static char *cfg = "res_odbc.conf";
00249    struct ast_config *config;
00250    struct ast_variable *v;
00251    char *cat, *dsn, *username, *password;
00252    int enabled;
00253    int connect = 0;
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                setenv(v->name, v->value, 1);
00263             }
00264 
00265             cat = ast_category_browse(config, cat);
00266          }
00267 
00268          dsn = username = password = NULL;
00269          enabled = 1;
00270          connect = 0;
00271          for (v = ast_variable_browse(config, cat); v; v = v->next) {
00272             if (!strcmp(v->name, "enabled"))
00273                enabled = ast_true(v->value);
00274             if (!strcmp(v->name, "pre-connect"))
00275                connect = ast_true(v->value);
00276             if (!strcmp(v->name, "dsn"))
00277                dsn = v->value;
00278             if (!strcmp(v->name, "username"))
00279                username = v->value;
00280             if (!strcmp(v->name, "password"))
00281                password = v->value;
00282          }
00283 
00284          if (enabled && dsn) {
00285             obj = new_odbc_obj(cat, dsn, username, password);
00286             if (obj) {
00287                register_odbc_obj(cat, obj);
00288                ast_log(LOG_NOTICE, "registered database handle '%s' dsn->[%s]\n", cat, obj->dsn);
00289                if (connect) {
00290                   odbc_obj_connect(obj);
00291                }
00292             } else {
00293                ast_log(LOG_WARNING, "Addition of obj %s failed.\n", cat);
00294             }
00295 
00296          }
00297       }
00298       ast_config_destroy(config);
00299    }
00300    return 0;
00301 }
00302 
00303 int odbc_dump_fd(int fd, odbc_obj *obj)
00304 {
00305    /* make sure the connection is up before we lie to our master.*/
00306    odbc_sanity_check(obj);
00307    ast_cli(fd, "Name: %s\nDSN: %s\nConnected: %s\n\n", obj->name, obj->dsn, obj->up ? "yes" : "no");
00308    return 0;
00309 }
00310 
00311 static int odbc_connect_usage(int fd)
00312 {
00313    ast_cli(fd, "usage odbc connect <DSN>\n");
00314    return 0;
00315 }
00316 
00317 static int odbc_disconnect_usage(int fd)
00318 {
00319    ast_cli(fd, "usage odbc disconnect <DSN>\n");
00320    return 0;
00321 }
00322 
00323 static int odbc_show_command(int fd, int argc, char **argv)
00324 {
00325    odbc_obj *obj;
00326    int x = 0;
00327    
00328    if (!strcmp(argv[1], "show")) {
00329       if (!argv[2] || (argv[2] && !strcmp(argv[2], "all"))) {
00330          for (x = 0; x < MAX_ODBC_HANDLES; x++) {
00331             if (!ODBC_REGISTRY[x].used)
00332                break;
00333             if (ODBC_REGISTRY[x].obj)
00334                odbc_dump_fd(fd, ODBC_REGISTRY[x].obj);
00335          }
00336       } else {
00337          obj = odbc_read(ODBC_REGISTRY, argv[2]);
00338          if (obj)
00339             odbc_dump_fd(fd, obj);
00340       }
00341    }
00342    return 0;
00343 }
00344 
00345 static int odbc_disconnect_command(int fd, int argc, char **argv)
00346 {
00347    odbc_obj *obj;
00348    if (!strcmp(argv[1], "disconnect")) {
00349       if (!argv[2])
00350          return odbc_disconnect_usage(fd);
00351 
00352       obj = odbc_read(ODBC_REGISTRY, argv[2]);
00353       if (obj) {
00354          odbc_obj_disconnect(obj);
00355       }
00356    } 
00357    return 0;
00358 }
00359 
00360 static int odbc_connect_command(int fd, int argc, char **argv)
00361 {
00362    odbc_obj *obj;
00363    if (!argv[1])
00364       return odbc_connect_usage(fd);
00365 
00366    if (!strcmp(argv[1], "connect") || !strcmp(argv[1], "disconnect")) {
00367       if (!argv[2])
00368          return odbc_connect_usage(fd);
00369 
00370       obj = odbc_read(ODBC_REGISTRY, argv[2]);
00371       if (obj) {
00372          odbc_obj_connect(obj);
00373       }
00374    }
00375    return 0;
00376 }
00377 
00378 
00379 static char connect_usage[] =
00380 "Usage: odbc connect <DSN>\n"
00381 "       Connect to ODBC DSN\n";
00382 
00383 static char disconnect_usage[] =
00384 "Usage: odbc connect <DSN>\n"
00385 "       Disconnect from ODBC DSN\n";
00386 
00387 static char show_usage[] =
00388 "Usage: odbc show {DSN}\n"
00389 "       Show ODBC {DSN}\n"
00390 "       Specifying DSN will show that DSN else, all DSNs are shown\n";
00391 
00392 static struct ast_cli_entry odbc_connect_struct =
00393         { { "odbc", "connect", NULL }, odbc_connect_command, "Connect to ODBC DSN", connect_usage };
00394 
00395 
00396 static struct ast_cli_entry odbc_disconnect_struct =
00397         { { "odbc", "disconnect", NULL }, odbc_disconnect_command, "Disconnect from ODBC DSN", disconnect_usage };
00398 
00399 static struct ast_cli_entry odbc_show_struct =
00400         { { "odbc", "show", NULL }, odbc_show_command, "Show ODBC DSN(s)", show_usage };
00401 
00402 /* api calls */
00403 
00404 int register_odbc_obj(char *name, odbc_obj *obj)
00405 {
00406    if (obj != NULL)
00407       return odbc_write(ODBC_REGISTRY, name, obj);
00408    return 0;
00409 }
00410 
00411 odbc_obj *fetch_odbc_obj(const char *name, int check)
00412 {
00413    odbc_obj *obj = NULL;
00414    if((obj = (odbc_obj *) odbc_read(ODBC_REGISTRY, name))) {
00415       if(check)
00416          odbc_sanity_check(obj);
00417    }
00418    return obj;
00419 }
00420 
00421 odbc_obj *new_odbc_obj(char *name, char *dsn, char *username, char *password)
00422 {
00423    static odbc_obj *new;
00424 
00425    if (!(new = calloc(1, sizeof(*new))) || 
00426        !(new->name = malloc(strlen(name) + 1)) || 
00427        !(new->dsn = malloc(strlen(dsn) + 1)))
00428          goto cleanup;
00429 
00430    if (username) {
00431       if (!(new->username = malloc(strlen(username) + 1)))
00432          goto cleanup;
00433       strcpy(new->username, username);
00434    }
00435 
00436    if (password) {
00437       if (!(new->password = malloc(strlen(password) + 1)))
00438          goto cleanup;
00439       strcpy(new->password, password);
00440    }
00441 
00442    strcpy(new->name, name);
00443    strcpy(new->dsn, dsn);
00444    new->env = SQL_NULL_HANDLE;
00445    new->up = 0;
00446    ast_mutex_init(&new->lock);
00447    return new;
00448 
00449 cleanup:
00450    if (new) {
00451       free(new->name);
00452       free(new->dsn);
00453       free(new->username);
00454       free(new->password);
00455 
00456       free(new);  
00457    }
00458 
00459    return NULL;
00460 }
00461 
00462 void destroy_odbc_obj(odbc_obj **obj)
00463 {
00464    odbc_obj_disconnect(*obj);
00465 
00466    ast_mutex_lock(&(*obj)->lock);
00467    SQLFreeHandle(SQL_HANDLE_STMT, (*obj)->stmt);
00468    SQLFreeHandle(SQL_HANDLE_DBC, (*obj)->con);
00469    SQLFreeHandle(SQL_HANDLE_ENV, (*obj)->env);
00470 
00471    free((*obj)->name);
00472    free((*obj)->dsn);
00473    if ((*obj)->username)
00474       free((*obj)->username);
00475    if ((*obj)->password)
00476       free((*obj)->password);
00477    ast_mutex_unlock(&(*obj)->lock);
00478    ast_mutex_destroy(&(*obj)->lock);
00479    free(*obj);
00480 }
00481 
00482 odbc_status odbc_obj_disconnect(odbc_obj *obj)
00483 {
00484    int res;
00485    ast_mutex_lock(&obj->lock);
00486 
00487    res = SQLDisconnect(obj->con);
00488 
00489 
00490    if (res == ODBC_SUCCESS) {
00491       ast_log(LOG_WARNING, "res_odbc: disconnected %d from %s [%s]\n", res, obj->name, obj->dsn);
00492    } else {
00493       ast_log(LOG_WARNING, "res_odbc: %s [%s] already disconnected\n",
00494       obj->name, obj->dsn);
00495    }
00496    obj->up = 0;
00497    ast_mutex_unlock(&obj->lock);
00498    return ODBC_SUCCESS;
00499 }
00500 
00501 odbc_status odbc_obj_connect(odbc_obj *obj)
00502 {
00503    int res;
00504    SQLINTEGER err;
00505    short int mlen;
00506    unsigned char msg[200], stat[10];
00507 
00508    ast_mutex_lock(&obj->lock);
00509 
00510    if (obj->env == SQL_NULL_HANDLE) {
00511       res = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &obj->env);
00512 
00513       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00514          if (option_verbose > 3)
00515             ast_log(LOG_WARNING, "res_odbc: Error AllocHandle\n");
00516          ast_mutex_unlock(&obj->lock);
00517          return ODBC_FAIL;
00518       }
00519 
00520       res = SQLSetEnvAttr(obj->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
00521 
00522       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00523          if (option_verbose > 3)
00524             ast_log(LOG_WARNING, "res_odbc: Error SetEnv\n");
00525          SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
00526          ast_mutex_unlock(&obj->lock);
00527          return ODBC_FAIL;
00528       }
00529 
00530       res = SQLAllocHandle(SQL_HANDLE_DBC, obj->env, &obj->con);
00531 
00532       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00533 
00534          if (option_verbose > 3)
00535             ast_log(LOG_WARNING, "res_odbc: Error AllocHDB %d\n", res);
00536          SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
00537 
00538          ast_mutex_unlock(&obj->lock);
00539          return ODBC_FAIL;
00540       }
00541       SQLSetConnectAttr(obj->con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 10, 0);
00542    }
00543    if(obj->up) {
00544       odbc_obj_disconnect(obj);
00545       ast_log(LOG_NOTICE,"Re-connecting %s\n", obj->name);
00546    }
00547 
00548    ast_log(LOG_NOTICE, "Connecting %s\n", obj->name);
00549 
00550    res = SQLConnect(obj->con,
00551          (SQLCHAR *) obj->dsn, SQL_NTS,
00552          (SQLCHAR *) obj->username, SQL_NTS,
00553          (SQLCHAR *) obj->password, SQL_NTS);
00554 
00555    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00556       SQLGetDiagRec(SQL_HANDLE_DBC, obj->con, 1, stat, &err, msg, 100, &mlen);
00557       SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
00558       ast_mutex_unlock(&obj->lock);
00559       ast_log(LOG_WARNING, "res_odbc: Error SQLConnect=%d errno=%d %s\n", res, (int)err, msg);
00560       return ODBC_FAIL;
00561    } else {
00562 
00563       ast_log(LOG_NOTICE, "res_odbc: Connected to %s [%s]\n", obj->name, obj->dsn);
00564       obj->up = 1;
00565    }
00566 
00567    ast_mutex_unlock(&obj->lock);
00568    return ODBC_SUCCESS;
00569 }
00570 
00571 STANDARD_LOCAL_USER;
00572 
00573 LOCAL_USER_DECL;
00574 
00575 int unload_module(void)
00576 {
00577    STANDARD_HANGUP_LOCALUSERS;
00578    odbc_destroy();
00579    ast_cli_unregister(&odbc_disconnect_struct);
00580    ast_cli_unregister(&odbc_connect_struct);
00581    ast_cli_unregister(&odbc_show_struct);
00582    ast_log(LOG_NOTICE, "res_odbc unloaded.\n");
00583    return 0;
00584 }
00585 
00586 int load_module(void)
00587 {
00588    odbc_init();
00589    load_odbc_config();
00590    ast_cli_register(&odbc_disconnect_struct);
00591    ast_cli_register(&odbc_connect_struct);
00592    ast_cli_register(&odbc_show_struct);
00593    ast_log(LOG_NOTICE, "res_odbc loaded.\n");
00594    return 0;
00595 }
00596 
00597 char *description(void)
00598 {
00599    return tdesc;
00600 }
00601 
00602 int usecount(void)
00603 {
00604    int res;
00605    STANDARD_USECOUNT(res);
00606    return res;
00607 }
00608 
00609 char *key()
00610 {
00611    return ASTERISK_GPL_KEY;
00612 }

Generated on Wed Oct 28 15:47:55 2009 for Asterisk - the Open Source PBX by  doxygen 1.5.6