Sat Mar 24 23:26:00 2007

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

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