Sat Aug 6 00:39:23 2011

Asterisk developer's documentation


cdr_odbc.c

Go to the documentation of this file.
00001 /*
00002  * Asterisk -- An open source telephony toolkit.
00003  *
00004  * Copyright (C) 2003-2005, Digium, Inc.
00005  *
00006  * Brian K. West <brian@bkw.org>
00007  *
00008  * See http://www.asterisk.org for more information about
00009  * the Asterisk project. Please do not directly contact
00010  * any of the maintainers of this project for assistance;
00011  * the project provides a web site, mailing lists and IRC
00012  * channels for your use.
00013  *
00014  * This program is free software, distributed under the terms of
00015  * the GNU General Public License Version 2. See the LICENSE file
00016  * at the top of the source tree.
00017  */
00018 
00019 /*! \file
00020  *
00021  * \brief ODBC CDR Backend
00022  * 
00023  * \author Brian K. West <brian@bkw.org>
00024  *
00025  * See also:
00026  * \arg http://www.unixodbc.org
00027  * \arg \ref Config_cdr
00028  * \ingroup cdr_drivers
00029  */
00030 
00031 /*** MODULEINFO
00032    <depend>unixodbc</depend>
00033    <depend>ltdl</depend>
00034  ***/
00035 
00036 #include "asterisk.h"
00037 
00038 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 150056 $")
00039 
00040 #include <sys/types.h>
00041 #include <stdio.h>
00042 #include <string.h>
00043 
00044 #include <stdlib.h>
00045 #include <unistd.h>
00046 #include <time.h>
00047 
00048 #ifndef __CYGWIN__
00049 #include <sql.h>
00050 #include <sqlext.h>
00051 #include <sqltypes.h>
00052 #else
00053 #include <windows.h>
00054 #include <w32api/sql.h>
00055 #include <w32api/sqlext.h>
00056 #include <w32api/sqltypes.h>
00057 #endif
00058 
00059 #include "asterisk/config.h"
00060 #include "asterisk/options.h"
00061 #include "asterisk/channel.h"
00062 #include "asterisk/cdr.h"
00063 #include "asterisk/module.h"
00064 #include "asterisk/logger.h"
00065 
00066 #define DATE_FORMAT "%Y-%m-%d %T"
00067 
00068 static char *name = "ODBC";
00069 static char *config = "cdr_odbc.conf";
00070 static char *dsn = NULL, *username = NULL, *password = NULL, *table = NULL;
00071 static int loguniqueid = 0;
00072 static int usegmtime = 0;
00073 static int dispositionstring = 0;
00074 static int connected = 0;
00075 
00076 AST_MUTEX_DEFINE_STATIC(odbc_lock);
00077 
00078 static int odbc_do_query(void);
00079 static int odbc_init(void);
00080 
00081 static SQLHENV ODBC_env = SQL_NULL_HANDLE;   /* global ODBC Environment */
00082 static SQLHDBC ODBC_con;         /* global ODBC Connection Handle */
00083 static SQLHSTMT   ODBC_stmt;        /* global ODBC Statement Handle */
00084 
00085 static void odbc_disconnect(void)
00086 {
00087    ODBC_stmt = SQL_NULL_HANDLE;
00088    SQLDisconnect(ODBC_con);
00089    SQLFreeHandle(SQL_HANDLE_DBC, ODBC_con);
00090    ODBC_con = SQL_NULL_HANDLE;
00091    SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
00092    ODBC_env = SQL_NULL_HANDLE;
00093    connected = 0;
00094 }
00095 
00096 static void build_query(struct ast_cdr *cdr, char *timestr, int timesize)
00097 {
00098    int ODBC_res;
00099    char sqlcmd[2048] = "";
00100    int res = 0;
00101    struct tm tm;
00102 
00103    if (usegmtime) 
00104       gmtime_r(&cdr->start.tv_sec,&tm);
00105    else
00106       ast_localtime(&cdr->start.tv_sec, &tm, NULL);
00107 
00108    strftime(timestr, timesize, DATE_FORMAT, &tm);
00109    memset(sqlcmd,0,2048);
00110    if (loguniqueid) {
00111       snprintf(sqlcmd,sizeof(sqlcmd),"INSERT INTO %s "
00112       "(calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,"
00113       "lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) "
00114       "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", table);
00115    } else {
00116       snprintf(sqlcmd,sizeof(sqlcmd),"INSERT INTO %s "
00117       "(calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,"
00118       "duration,billsec,disposition,amaflags,accountcode) "
00119       "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", table);
00120    }
00121    if (!connected) {
00122       res = odbc_init();
00123       if (res < 0) {
00124          odbc_disconnect();
00125          return;
00126       }           
00127    }
00128 
00129    ODBC_res = SQLAllocHandle(SQL_HANDLE_STMT, ODBC_con, &ODBC_stmt);
00130 
00131    if ((ODBC_res != SQL_SUCCESS) && (ODBC_res != SQL_SUCCESS_WITH_INFO)) {
00132       if (option_verbose > 10)
00133          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Failure in AllocStatement %d\n", ODBC_res);
00134       SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmt);
00135       ODBC_stmt = SQL_NULL_HANDLE;
00136       odbc_disconnect();
00137       return;
00138    }
00139 
00140    /* We really should only have to do this once.  But for some
00141       strange reason if I don't it blows holes in memory like
00142       like a shotgun.  So we just do this so its safe. */
00143 
00144    ODBC_res = SQLPrepare(ODBC_stmt, (unsigned char *)sqlcmd, SQL_NTS);
00145    
00146    if ((ODBC_res != SQL_SUCCESS) && (ODBC_res != SQL_SUCCESS_WITH_INFO)) {
00147       if (option_verbose > 10)
00148          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Error in PREPARE %d\n", ODBC_res);
00149       SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmt);
00150       ODBC_stmt = SQL_NULL_HANDLE;
00151       odbc_disconnect();
00152       return;
00153    }
00154 
00155    SQLBindParameter(ODBC_stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, timesize, 0, timestr, 0, NULL);
00156    SQLBindParameter(ODBC_stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->clid), 0, cdr->clid, 0, NULL);
00157    SQLBindParameter(ODBC_stmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->src), 0, cdr->src, 0, NULL);
00158    SQLBindParameter(ODBC_stmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->dst), 0, cdr->dst, 0, NULL);
00159    SQLBindParameter(ODBC_stmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->dcontext), 0, cdr->dcontext, 0, NULL);
00160    SQLBindParameter(ODBC_stmt, 6, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->channel), 0, cdr->channel, 0, NULL);
00161    SQLBindParameter(ODBC_stmt, 7, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->dstchannel), 0, cdr->dstchannel, 0, NULL);
00162    SQLBindParameter(ODBC_stmt, 8, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->lastapp), 0, cdr->lastapp, 0, NULL);
00163    SQLBindParameter(ODBC_stmt, 9, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->lastdata), 0, cdr->lastdata, 0, NULL);
00164    SQLBindParameter(ODBC_stmt, 10, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &cdr->duration, 0, NULL);
00165    SQLBindParameter(ODBC_stmt, 11, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &cdr->billsec, 0, NULL);
00166    if (dispositionstring)
00167       SQLBindParameter(ODBC_stmt, 12, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(ast_cdr_disp2str(cdr->disposition)) + 1, 0, ast_cdr_disp2str(cdr->disposition), 0, NULL);
00168    else
00169       SQLBindParameter(ODBC_stmt, 12, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &cdr->disposition, 0, NULL);
00170    SQLBindParameter(ODBC_stmt, 13, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &cdr->amaflags, 0, NULL);
00171    SQLBindParameter(ODBC_stmt, 14, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->accountcode), 0, cdr->accountcode, 0, NULL);
00172 
00173    if (loguniqueid) {
00174       SQLBindParameter(ODBC_stmt, 15, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->uniqueid), 0, cdr->uniqueid, 0, NULL);
00175       SQLBindParameter(ODBC_stmt, 16, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, sizeof(cdr->userfield), 0, cdr->userfield, 0, NULL);
00176    }
00177 }
00178 
00179 static int odbc_log(struct ast_cdr *cdr)
00180 {
00181    int res = 0;
00182    char timestr[150];
00183 
00184    ast_mutex_lock(&odbc_lock);
00185    build_query(cdr, timestr, sizeof(timestr));
00186 
00187    if (connected) {
00188       res = odbc_do_query();
00189       if (res < 0) {
00190          if (option_verbose > 10)
00191             ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Reconnecting to dsn %s\n", dsn);
00192          odbc_disconnect();
00193          res = odbc_init();
00194          if (res < 0) {
00195             if (option_verbose > 10)
00196                ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: %s has gone away!\n", dsn);
00197             odbc_disconnect();
00198          } else {
00199             if (option_verbose > 10)
00200                ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Trying Query again!\n");
00201             SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmt);
00202             ODBC_stmt = SQL_NULL_HANDLE;
00203             build_query(cdr, timestr, sizeof(timestr)); /* what a waste. If we have to reconnect, we have to build a new query */
00204             res = odbc_do_query();
00205             if (res < 0) {
00206                if (option_verbose > 10)
00207                   ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Query FAILED Call not logged!\n");
00208             }
00209          }
00210       }
00211    } else {
00212       if (option_verbose > 10)
00213          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Query FAILED Call not logged!\n");
00214    }
00215    SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmt);
00216    ODBC_stmt = SQL_NULL_HANDLE;
00217    ast_mutex_unlock(&odbc_lock);
00218    return 0;
00219 }
00220 
00221 static int odbc_unload_module(void)
00222 {
00223    ast_mutex_lock(&odbc_lock);
00224    if (connected) {
00225       if (option_verbose > 10)
00226          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Disconnecting from %s\n", dsn);
00227       SQLFreeHandle(SQL_HANDLE_STMT, ODBC_stmt);
00228       ODBC_stmt = SQL_NULL_HANDLE;
00229       odbc_disconnect();
00230    }
00231    if (dsn) {
00232       if (option_verbose > 10)
00233          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: free dsn\n");
00234       free(dsn);
00235    }
00236    if (username) {
00237       if (option_verbose > 10)
00238          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: free username\n");
00239       free(username);
00240    }
00241    if (password) {
00242       if (option_verbose > 10)
00243          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: free password\n");
00244       free(password);
00245    }
00246    if (table) {
00247       if (option_verbose > 10)
00248          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: free table\n");
00249       free(table);
00250    }
00251 
00252    ast_cdr_unregister(name);
00253    ast_mutex_unlock(&odbc_lock);
00254    return 0;
00255 }
00256 
00257 static int odbc_load_module(void)
00258 {
00259    int res = 0;
00260    struct ast_config *cfg;
00261    struct ast_variable *var;
00262    const char *tmp;
00263 
00264    ast_mutex_lock(&odbc_lock);
00265 
00266    cfg = ast_config_load(config);
00267    if (!cfg) {
00268       ast_log(LOG_WARNING, "cdr_odbc: Unable to load config for ODBC CDR's: %s\n", config);
00269       res = AST_MODULE_LOAD_DECLINE;
00270       goto out;
00271    }
00272    
00273    var = ast_variable_browse(cfg, "global");
00274    if (!var) {
00275       /* nothing configured */
00276       goto out;
00277    }
00278 
00279    tmp = ast_variable_retrieve(cfg,"global","dsn");
00280    if (tmp == NULL) {
00281       ast_log(LOG_WARNING,"cdr_odbc: dsn not specified.  Assuming asteriskdb\n");
00282       tmp = "asteriskdb";
00283    }
00284    dsn = strdup(tmp);
00285    if (dsn == NULL) {
00286       ast_log(LOG_ERROR,"cdr_odbc: Out of memory error.\n");
00287       res = -1;
00288       goto out;
00289    }
00290 
00291    tmp = ast_variable_retrieve(cfg,"global","dispositionstring");
00292    if (tmp) {
00293       dispositionstring = ast_true(tmp);
00294    } else {
00295       dispositionstring = 0;
00296    }
00297       
00298    tmp = ast_variable_retrieve(cfg,"global","username");
00299    if (tmp) {
00300       username = strdup(tmp);
00301       if (username == NULL) {
00302          ast_log(LOG_ERROR,"cdr_odbc: Out of memory error.\n");
00303          res = -1;
00304          goto out;
00305       }
00306    }
00307 
00308    tmp = ast_variable_retrieve(cfg,"global","password");
00309    if (tmp) {
00310       password = strdup(tmp);
00311       if (password == NULL) {
00312          ast_log(LOG_ERROR,"cdr_odbc: Out of memory error.\n");
00313          res = -1;
00314          goto out;
00315       }
00316    }
00317 
00318    tmp = ast_variable_retrieve(cfg,"global","loguniqueid");
00319    if (tmp) {
00320       loguniqueid = ast_true(tmp);
00321       if (loguniqueid) {
00322          ast_log(LOG_DEBUG,"cdr_odbc: Logging uniqueid\n");
00323       } else {
00324          ast_log(LOG_DEBUG,"cdr_odbc: Not logging uniqueid\n");
00325       }
00326    } else {
00327       ast_log(LOG_DEBUG,"cdr_odbc: Not logging uniqueid\n");
00328       loguniqueid = 0;
00329    }
00330 
00331    tmp = ast_variable_retrieve(cfg,"global","usegmtime");
00332    if (tmp) {
00333       usegmtime = ast_true(tmp);
00334       if (usegmtime) {
00335          ast_log(LOG_DEBUG,"cdr_odbc: Logging in GMT\n");
00336       } else {
00337          ast_log(LOG_DEBUG,"cdr_odbc: Not logging in GMT\n");
00338       }
00339    } else {
00340       ast_log(LOG_DEBUG,"cdr_odbc: Not logging in GMT\n");
00341       usegmtime = 0;
00342    }
00343 
00344    tmp = ast_variable_retrieve(cfg,"global","table");
00345    if (tmp == NULL) {
00346       ast_log(LOG_WARNING,"cdr_odbc: table not specified.  Assuming cdr\n");
00347       tmp = "cdr";
00348    }
00349    table = strdup(tmp);
00350    if (table == NULL) {
00351       ast_log(LOG_ERROR,"cdr_odbc: Out of memory error.\n");
00352       res = -1;
00353       goto out;
00354    }
00355 
00356    if (option_verbose > 2) {
00357       ast_verbose( VERBOSE_PREFIX_3 "cdr_odbc: dsn is %s\n",dsn);
00358       if (username)
00359       {
00360          ast_verbose( VERBOSE_PREFIX_3 "cdr_odbc: username is %s\n",username);
00361          ast_verbose( VERBOSE_PREFIX_3 "cdr_odbc: password is [secret]\n");
00362       }
00363       else
00364          ast_verbose( VERBOSE_PREFIX_3 "cdr_odbc: retreiving username and password from odbc config\n");
00365       ast_verbose( VERBOSE_PREFIX_3 "cdr_odbc: table is %s\n",table);
00366    }
00367    
00368    res = odbc_init();
00369    if (res < 0) {
00370       ast_log(LOG_ERROR, "cdr_odbc: Unable to connect to datasource: %s\n", dsn);
00371       if (option_verbose > 2) {
00372          ast_verbose( VERBOSE_PREFIX_3 "cdr_odbc: Unable to connect to datasource: %s\n", dsn);
00373       }
00374    }
00375    res = ast_cdr_register(name, ast_module_info->description, odbc_log);
00376    if (res) {
00377       ast_log(LOG_ERROR, "cdr_odbc: Unable to register ODBC CDR handling\n");
00378    }
00379 out:
00380    if (cfg)
00381       ast_config_destroy(cfg);
00382    ast_mutex_unlock(&odbc_lock);
00383    return res;
00384 }
00385 
00386 static int odbc_do_query(void)
00387 {
00388    int ODBC_res;
00389    ODBC_res = SQLExecute(ODBC_stmt);
00390    
00391    if ((ODBC_res != SQL_SUCCESS) && (ODBC_res != SQL_SUCCESS_WITH_INFO)) {
00392       if (option_verbose > 10)
00393          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Error in Query %d\n", ODBC_res);
00394       return -1;
00395    } else {
00396       if (option_verbose > 10)
00397          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Query Successful!\n");
00398       connected = 1;
00399    }
00400    return 0;
00401 }
00402 
00403 static int odbc_init(void)
00404 {
00405    int ODBC_res;
00406 
00407    if (ODBC_env == SQL_NULL_HANDLE || connected == 0) {
00408       ODBC_res = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &ODBC_env);
00409       if ((ODBC_res != SQL_SUCCESS) && (ODBC_res != SQL_SUCCESS_WITH_INFO)) {
00410          if (option_verbose > 10)
00411             ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Error AllocHandle\n");
00412          connected = 0;
00413          return -1;
00414       }
00415 
00416       ODBC_res = SQLSetEnvAttr(ODBC_env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
00417 
00418       if ((ODBC_res != SQL_SUCCESS) && (ODBC_res != SQL_SUCCESS_WITH_INFO)) {
00419          if (option_verbose > 10)
00420             ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Error SetEnv\n");
00421          SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
00422          ODBC_env = SQL_NULL_HANDLE;
00423          connected = 0;
00424          return -1;
00425       }
00426 
00427       ODBC_res = SQLAllocHandle(SQL_HANDLE_DBC, ODBC_env, &ODBC_con);
00428 
00429       if ((ODBC_res != SQL_SUCCESS) && (ODBC_res != SQL_SUCCESS_WITH_INFO)) {
00430          if (option_verbose > 10)
00431             ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Error AllocHDB %d\n", ODBC_res);
00432          SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
00433          ODBC_env = SQL_NULL_HANDLE;
00434          connected = 0;
00435          return -1;
00436       }
00437       SQLSetConnectAttr(ODBC_con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)10, 0); 
00438    }
00439 
00440    /* Note that the username and password could be NULL here, but that is allowed in ODBC.
00441            In this case, the default username and password will be used from odbc.conf */
00442    ODBC_res = SQLConnect(ODBC_con, (SQLCHAR*)dsn, SQL_NTS, (SQLCHAR*)username, SQL_NTS, (SQLCHAR*)password, SQL_NTS);
00443 
00444    if ((ODBC_res != SQL_SUCCESS) && (ODBC_res != SQL_SUCCESS_WITH_INFO)) {
00445       if (option_verbose > 10)
00446          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Error SQLConnect %d\n", ODBC_res);
00447       SQLFreeHandle(SQL_HANDLE_DBC, ODBC_con);
00448       ODBC_con = SQL_NULL_HANDLE;
00449       SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
00450       ODBC_env = SQL_NULL_HANDLE;
00451       connected = 0;
00452       return -1;
00453    } else {
00454       if (option_verbose > 10)
00455          ast_verbose( VERBOSE_PREFIX_4 "cdr_odbc: Connected to %s\n", dsn);
00456       connected = 1;
00457    }
00458    return 0;
00459 }
00460 
00461 static int load_module(void)
00462 {
00463    return odbc_load_module();
00464 }
00465 
00466 static int unload_module(void)
00467 {
00468    return odbc_unload_module();
00469 }
00470 
00471 static int reload(void)
00472 {
00473    odbc_unload_module();
00474    return odbc_load_module();
00475 }
00476 
00477 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC CDR Backend",
00478       .load = load_module,
00479       .unload = unload_module,
00480       .reload = reload,
00481           );

Generated on Sat Aug 6 00:39:23 2011 for Asterisk - the Open Source PBX by  doxygen 1.4.7