Index: cdr/cdr_pgsql.c =================================================================== RCS file: /usr/cvsroot/asterisk/cdr/cdr_pgsql.c,v retrieving revision 1.6 diff -u -r1.6 cdr_pgsql.c --- cdr/cdr_pgsql.c 22 Jan 2004 17:03:11 -0000 1.6 +++ cdr/cdr_pgsql.c 29 Jan 2004 16:06:21 -0000 @@ -18,9 +18,11 @@ #include #include #include +#include #include #include #include +#include #include "../asterisk.h" #include @@ -28,117 +30,163 @@ #include #include +#include #include #include #define DATE_FORMAT "%Y-%m-%d %T" +/* define a backwards compatible value if columns is not specified + in cdr_pgsql.conf */ +#define DEFAULT_COLUMNS "calldate:string,clid:string,src:string,dst:string,dcontext:string,channel:string,dstchannel:string,lastapp:string,lastdata:string,duration:int,billsec:int,disposition:string,amaflags:int,accountcode:string,uniqueid:string,userfield:string" + static char *desc = "PostgreSQL CDR Backend"; static char *name = "pgsql"; static char *config = "cdr_pgsql.conf"; -static char *pghostname = NULL, *pgdbname = NULL, *pgdbuser = NULL, *pgpassword = NULL, *pgdbsock = NULL, *pgdbport = NULL; -static int hostname_alloc = 0, dbname_alloc = 0, dbuser_alloc = 0, password_alloc = 0, dbsock_alloc = 0, dbport_alloc = 0; +static char *pghostname = NULL, *pgdbname = NULL, *pgdbuser = NULL, *pgpassword = NULL, *pgdbsock = NULL, *pgdbport = NULL, *pgdbtable = NULL, *pgdbcols = NULL; +static int hostname_alloc = 0, dbname_alloc = 0, dbuser_alloc = 0, password_alloc = 0, dbsock_alloc = 0, dbport_alloc = 0, dbtable_alloc = 0, dbcols_alloc; static int connected = 0; static ast_mutex_t pgsql_lock = AST_MUTEX_INITIALIZER; +static struct varshead chanvars = AST_LIST_HEAD_INITIALIZER(varshead); PGconn *conn; PGresult *result; -static int pgsql_log(struct ast_cdr *cdr) +static int pgsql_log(struct ast_channel *chan) { - struct tm tm; - struct timeval tv; - char sqlcmd[2048], timestr[128]; - time_t t; - char *pgerror; - - ast_mutex_lock(&pgsql_lock); - - memset(sqlcmd,0,2048); + struct ast_var_t *vnp; + struct ast_cdr *cdr; + struct tm tm; + struct timeval tv; + char sqlcmd[2048], timestr[128]; + time_t t; + char *pgerror; + + if (!chan) + return -1; + cdr = chan-> cdr; + + ast_mutex_lock(&pgsql_lock); + + memset(sqlcmd,0,2048); + + gettimeofday(&tv,NULL); + t = tv.tv_sec; + localtime_r(&t,&tm); + strftime(timestr,128,DATE_FORMAT,&tm); + + if ((!connected) && pghostname && pgdbuser && pgpassword && pgdbname) { + conn = PQsetdbLogin(pghostname, pgdbport, NULL, NULL, pgdbname, pgdbuser, pgpassword); + if (PQstatus(conn) != CONNECTION_BAD) { + connected = 1; + } else { + pgerror = PQerrorMessage(conn); + ast_log(LOG_ERROR, "cdr_pgsql: Unable to connect to database server %s. " + "Calls will not be logged!\n", pghostname); + ast_log(LOG_ERROR, "cdr_pgsql: Reason: %s\n", pgerror); + } + } + + if (connected) { + char buf[BUFSIZ]; /* scratch space */ + + /* + shove the CDR data from the CDR structure + into channel variables. in reality this should + be done by the channel driver. + */ + pbx_builtin_setvar_helper(chan, "calldate", timestr); + PQescapeString(buf, cdr->clid, strlen(cdr->clid)); + pbx_builtin_setvar_helper(chan, "clid", buf); + PQescapeString(buf, cdr->dcontext, strlen(cdr->dcontext)); + pbx_builtin_setvar_helper(chan, "dcontext", buf); + PQescapeString(buf, cdr->channel, strlen(cdr->channel)); + pbx_builtin_setvar_helper(chan, "channel", buf); + PQescapeString(buf, cdr->dstchannel, strlen(cdr->dstchannel)); + pbx_builtin_setvar_helper(chan, "dstchannel", buf); + PQescapeString(buf, cdr->lastapp, strlen(cdr->lastapp)); + pbx_builtin_setvar_helper(chan, "lastapp", buf); + PQescapeString(buf, cdr->lastdata, strlen(cdr->lastdata)); + pbx_builtin_setvar_helper(chan, "lastdata", buf); + PQescapeString(buf, cdr->uniqueid, strlen(cdr->uniqueid)); + pbx_builtin_setvar_helper(chan, "uniqueid", buf); + PQescapeString(buf, cdr->userfield, strlen(cdr->userfield)); + pbx_builtin_setvar_helper(chan, "userfield", buf); + pbx_builtin_setvar_helper(chan, "src", cdr->src); + pbx_builtin_setvar_helper(chan, "dst", cdr->dst); + pbx_builtin_setvar_helper(chan, "disposition", ast_cdr_disp2str(cdr->disposition)); + sprintf(buf, "%d", cdr->amaflags); + pbx_builtin_setvar_helper(chan, "accountcode", cdr->accountcode); + pbx_builtin_setvar_helper(chan, "amaflags", buf); + sprintf(buf, "%d", cdr->duration); + pbx_builtin_setvar_helper(chan, "duration", buf); + sprintf(buf, "%d", cdr->billsec); + pbx_builtin_setvar_helper(chan, "billsec", buf); - gettimeofday(&tv,NULL); - t = tv.tv_sec; - localtime_r(&t,&tm); - strftime(timestr,128,DATE_FORMAT,&tm); - - if ((!connected) && pghostname && pgdbuser && pgpassword && pgdbname) { - conn = PQsetdbLogin(pghostname, pgdbport, NULL, NULL, pgdbname, pgdbuser, pgpassword); - if (PQstatus(conn) != CONNECTION_BAD) { - connected = 1; + ast_log(LOG_DEBUG,"cdr_pgsql: inserting a CDR record.\n"); + + snprintf(sqlcmd, sizeof(sqlcmd), "INSERT INTO %s (%s) VALUES (", + pgdbtable, pgdbcols); + AST_LIST_TRAVERSE(&chanvars, vnp, entries) { + char *name = ast_var_name(vnp); + char *type = ast_var_value(vnp); + char *value = pbx_builtin_getvar_helper(chan, name); + char qbuf[BUFSIZ]; + + if (!strcasecmp(type, "int")) { + sprintf(qbuf, "%d,", atoi(value)); + } else if (!strcasecmp(type, "float")) { + sprintf(qbuf, "%f,", atof(value)); + } else { + if (!value || !*value) { /* value is null string */ + sprintf(qbuf, "'',"); } else { - pgerror = PQerrorMessage(conn); - ast_log(LOG_ERROR, "cdr_pgsql: Unable to connect to database server %s. Calls will not be logged!\n", pghostname); - ast_log(LOG_ERROR, "cdr_pgsql: Reason: %s\n", pgerror); + PQescapeString(buf, value, strlen(value)); + snprintf(qbuf, sizeof(qbuf), "'%s',", buf); } + } + strncat(sqlcmd, qbuf, sizeof(sqlcmd)); } + sqlcmd[strlen(sqlcmd)-1] = ')'; - if (connected) { - char *clid=NULL, *dcontext=NULL, *channel=NULL, *dstchannel=NULL, *lastapp=NULL, *lastdata=NULL; - char *uniqueid=NULL, *userfield=NULL; - - /* Maximum space needed would be if all characters needed to be escaped, plus a trailing NULL */ - if ((clid = alloca(strlen(cdr->clid) * 2 + 1)) != NULL) - PQescapeString(clid, cdr->clid, strlen(cdr->clid)); - if ((dcontext = alloca(strlen(cdr->dcontext) * 2 + 1)) != NULL) - PQescapeString(dcontext, cdr->dcontext, strlen(cdr->dcontext)); - if ((channel = alloca(strlen(cdr->channel) * 2 + 1)) != NULL) - PQescapeString(channel, cdr->channel, strlen(cdr->channel)); - if ((dstchannel = alloca(strlen(cdr->dstchannel) * 2 + 1)) != NULL) - PQescapeString(dstchannel, cdr->dstchannel, strlen(cdr->dstchannel)); - if ((lastapp = alloca(strlen(cdr->lastapp) * 2 + 1)) != NULL) - PQescapeString(lastapp, cdr->lastapp, strlen(cdr->lastapp)); - if ((lastdata = alloca(strlen(cdr->lastdata) * 2 + 1)) != NULL) - PQescapeString(lastdata, cdr->lastdata, strlen(cdr->lastdata)); - if ((uniqueid = alloca(strlen(cdr->uniqueid) * 2 + 1)) != NULL) - PQescapeString(uniqueid, cdr->uniqueid, strlen(cdr->uniqueid)); - if ((userfield = alloca(strlen(cdr->userfield) * 2 + 1)) != NULL) - PQescapeString(userfield, cdr->userfield, strlen(cdr->userfield)); - - /* Check for all alloca failures above at once */ - if ((!clid) || (!dcontext) || (!channel) || (!dstchannel) || (!lastapp) || (!lastdata) || (!uniqueid) || (!userfield)) { - ast_log(LOG_ERROR, "cdr_pgsql: Out of memory error (insert fails)\n"); - ast_mutex_unlock(&pgsql_lock); - return -1; - } - - ast_log(LOG_DEBUG,"cdr_pgsql: inserting a CDR record.\n"); - - sprintf(sqlcmd,"INSERT INTO cdr (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,userfield) VALUES ('%s','%s','%s','%s','%s', '%s','%s','%s','%s',%i,%i,'%s',%i,'%s','%s','%s')",timestr,clid,cdr->src, cdr->dst, dcontext,channel, dstchannel, lastapp, lastdata,cdr->duration,cdr->billsec,ast_cdr_disp2str(cdr->disposition),cdr->amaflags, cdr->accountcode, uniqueid, userfield); - ast_log(LOG_DEBUG,"cdr_pgsql: SQL command executed: %s\n",sqlcmd); + ast_log(LOG_DEBUG,"cdr_pgsql: SQL command executed: %s\n",sqlcmd); - /* Test to be sure we're still connected... */ - /* If we're connected, and connection is working, good. */ - /* Otherwise, attempt reconnect. If it fails... sorry... */ - if (PQstatus(conn) == CONNECTION_OK) { - connected = 1; - } else { - ast_log(LOG_ERROR, "cdr_pgsql: Connection was lost... attempting to reconnect.\n"); - PQreset(conn); - if (PQstatus(conn) == CONNECTION_OK) { - ast_log(LOG_ERROR, "cdr_pgsql: Connection reestablished.\n"); - connected = 1; - } else { - pgerror = PQerrorMessage(conn); - ast_log(LOG_ERROR, "cdr_pgsql: Unable to reconnect to database server %s. Calls will not be logged!\n", pghostname); - ast_log(LOG_ERROR, "cdr_pgsql: Reason: %s\n", pgerror); - connected = 0; - ast_mutex_unlock(&pgsql_lock); - return -1; - } - } - result = PQexec(conn, sqlcmd); - if ( PQresultStatus(result) != PGRES_COMMAND_OK) { - pgerror = PQresultErrorMessage(result); - ast_log(LOG_ERROR,"cdr_pgsql: Failed to insert call detail record into database!\n"); - ast_log(LOG_ERROR,"cdr_pgsql: Reason: %s\n", pgerror); - ast_mutex_unlock(&pgsql_lock); - return -1; - } - } - ast_mutex_unlock(&pgsql_lock); - return 0; + /* Test to be sure we're still connected... */ + /* If we're connected, and connection is working, good. */ + /* Otherwise, attempt reconnect. If it fails... sorry... */ + if (PQstatus(conn) == CONNECTION_OK) { + connected = 1; + } else { + ast_log(LOG_ERROR, "cdr_pgsql: Connection was lost... attempting to reconnect.\n"); + PQreset(conn); + if (PQstatus(conn) == CONNECTION_OK) { + ast_log(LOG_ERROR, "cdr_pgsql: Connection reestablished.\n"); + connected = 1; + } else { + pgerror = PQerrorMessage(conn); + ast_log(LOG_ERROR, + "cdr_pgsql: Unable to reconnect to database server %s. " + "Calls will not be logged!\n", + pghostname); + ast_log(LOG_ERROR, "cdr_pgsql: Reason: %s\n", pgerror); + connected = 0; + ast_mutex_unlock(&pgsql_lock); + return -1; + } + } + result = PQexec(conn, sqlcmd); + if (PQresultStatus(result) != PGRES_COMMAND_OK) { + pgerror = PQresultErrorMessage(result); + ast_log(LOG_ERROR,"cdr_pgsql: Failed to insert call detail record into database!\n"); + ast_log(LOG_ERROR,"cdr_pgsql: Reason: %s\n", pgerror); + ast_mutex_unlock(&pgsql_lock); + return -1; + } + } + ast_mutex_unlock(&pgsql_lock); + return 0; } char *description(void) @@ -180,6 +228,19 @@ pgdbport = NULL; dbport_alloc = 0; } + if (pgdbtable && dbtable_alloc) { + free(pgdbtable); + pgdbtable = NULL; + dbtable_alloc = 0; + } + if (pgdbcols && dbcols_alloc) { + free(pgdbcols); + pgdbcols = NULL; + dbcols_alloc = 0; + } + while (!AST_LIST_EMPTY(&chanvars)) + AST_LIST_REMOVE_HEAD(&chanvars, entries); + ast_cdr_unregister(name); return 0; } @@ -191,6 +252,8 @@ struct ast_variable *var; char *pgerror; char *tmp; + struct ast_var_t *varname; + char *column, *type; cfg = ast_load(config); if (!cfg) { @@ -278,6 +341,51 @@ ast_log(LOG_WARNING,"PostgreSQL database port not specified. Using default 5432.\n"); pgdbport = "5432"; } + + tmp = ast_variable_retrieve(cfg,"global","table"); + if (tmp) { + pgdbtable = malloc(strlen(tmp) + 1); + if (!pgdbtable) { + ast_log(LOG_ERROR, "Out of memory error.\n"); + return -1; + } else { + dbtable_alloc = 1; + strcpy(pgdbtable, tmp); + } + } else { + pgdbtable = "cdr"; + } + + /* + * Parse out channel variable list that we are to log + */ + tmp = ast_variable_retrieve(cfg, "global", "chanvars"); + if (!tmp) { /* fill in with backwards compatible value */ + tmp = alloca(sizeof(DEFAULT_COLUMNS)); + memcpy(tmp, DEFAULT_COLUMNS, sizeof(DEFAULT_COLUMNS)); + } + + pgdbcols = malloc(strlen(tmp)); + if (!pgdbcols) { + ast_log(LOG_ERROR, "Memory allocation failed"); + return -1; + } + dbcols_alloc = 1; + memset(pgdbcols, 0, strlen(tmp)); + + column = strsep(&tmp, ","); + while (column) { + while (*column && !isalnum(*column)) + column++; + type = column; + strsep(&type, ":"); + varname = ast_var_assign(column, type); + strcat(pgdbcols, column); + strcat(pgdbcols, ","); + AST_LIST_INSERT_TAIL(&chanvars, varname, ast_var_t, entries); + column = strsep(&tmp, ","); + } + pgdbcols[strlen(pgdbcols)-1] = 0; ast_destroy(cfg); Index: configs/cdr_pgsql.conf.sample =================================================================== RCS file: /usr/cvsroot/asterisk/configs/cdr_pgsql.conf.sample,v retrieving revision 1.1 diff -u -r1.1 cdr_pgsql.conf.sample --- configs/cdr_pgsql.conf.sample 25 Nov 2003 01:17:50 -0000 1.1 +++ configs/cdr_pgsql.conf.sample 29 Jan 2004 16:06:21 -0000 @@ -6,3 +6,14 @@ ;dbname=asterisk ;password=password ;user=postgres +;table=cdr + +;; chanvars is basically a description of the columns +;; into which cdr data should be put the format is +;; column:type where type can be int, float or string +;; there are a few builtin choices for backwards compatibilty, +;; or any channel variable can be used. see the SetVar application +;; the default is this: +;; +chanvars=calldate:string,clid:string,src:string,dst:string,dcontext:string,channel:string,dstchannel:string,lastapp:string,lastdata:string,duration:int,billsec:int,disposition:string,amaflags:int,accountcode:string,uniqueid:string,userfield:string,var1:string +