Asterisk.org: Developer Documentation (28 Oct 2009)

Wed Oct 28 15:47:49 2009

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