00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034
00035
00036
00037
00038
00039
00040 #include "asterisk.h"
00041
00042 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 164354 $")
00043
00044 #include <time.h>
00045
00046 #include <libpq-fe.h>
00047
00048 #include "asterisk/config.h"
00049 #include "asterisk/channel.h"
00050 #include "asterisk/cdr.h"
00051 #include "asterisk/module.h"
00052
00053 #define DATE_FORMAT "'%Y-%m-%d %T'"
00054
00055 static char *name = "pgsql";
00056 static char *config = "cdr_pgsql.conf";
00057 static char *pghostname = NULL, *pgdbname = NULL, *pgdbuser = NULL, *pgpassword = NULL, *pgdbport = NULL, *table = NULL;
00058 static int connected = 0;
00059 static int maxsize = 512, maxsize2 = 512;
00060
00061 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
00062
00063 static PGconn *conn = NULL;
00064
00065 struct columns {
00066 char *name;
00067 char *type;
00068 int len;
00069 unsigned int notnull:1;
00070 unsigned int hasdefault:1;
00071 AST_RWLIST_ENTRY(columns) list;
00072 };
00073
00074 static AST_RWLIST_HEAD_STATIC(psql_columns, columns);
00075
00076 #define LENGTHEN_BUF1(size) \
00077 do { \
00078 \
00079 if ((newsize = lensql + (size) + 3) > sizesql) { \
00080 if ((tmp = ast_realloc(sql, (newsize / 512 + 1) * 512))) { \
00081 sql = tmp; \
00082 sizesql = (newsize / 512 + 1) * 512; \
00083 } else { \
00084 ast_log(LOG_ERROR, "Unable to allocate sufficient memory. Insert CDR failed.\n"); \
00085 ast_free(sql); \
00086 ast_free(sql2); \
00087 AST_RWLIST_UNLOCK(&psql_columns); \
00088 return -1; \
00089 } \
00090 } \
00091 } while (0)
00092
00093 #define LENGTHEN_BUF2(size) \
00094 do { \
00095 if ((newsize = lensql2 + (size) + 3) > sizesql2) { \
00096 if ((tmp = ast_realloc(sql2, (newsize / 512 + 1) * 512))) { \
00097 sql2 = tmp; \
00098 sizesql2 = (newsize / 512 + 1) * 512; \
00099 } else { \
00100 ast_log(LOG_ERROR, "Unable to allocate sufficient memory. Insert CDR failed.\n"); \
00101 ast_free(sql); \
00102 ast_free(sql2); \
00103 AST_RWLIST_UNLOCK(&psql_columns); \
00104 return -1; \
00105 } \
00106 } \
00107 } while (0)
00108
00109 static int pgsql_log(struct ast_cdr *cdr)
00110 {
00111 struct ast_tm tm;
00112 char *pgerror;
00113 PGresult *result;
00114
00115 ast_mutex_lock(&pgsql_lock);
00116
00117 if ((!connected) && pghostname && pgdbuser && pgpassword && pgdbname) {
00118 conn = PQsetdbLogin(pghostname, pgdbport, NULL, NULL, pgdbname, pgdbuser, pgpassword);
00119 if (PQstatus(conn) != CONNECTION_BAD) {
00120 connected = 1;
00121 } else {
00122 pgerror = PQerrorMessage(conn);
00123 ast_log(LOG_ERROR, "cdr_pgsql: Unable to connect to database server %s. Calls will not be logged!\n", pghostname);
00124 ast_log(LOG_ERROR, "cdr_pgsql: Reason: %s\n", pgerror);
00125 PQfinish(conn);
00126 conn = NULL;
00127 }
00128 }
00129
00130 if (connected) {
00131 struct columns *cur;
00132 int lensql, lensql2, sizesql = maxsize, sizesql2 = maxsize2, newsize;
00133 char *sql = ast_calloc(sizeof(char), sizesql), *sql2 = ast_calloc(sizeof(char), sizesql2), *tmp, *value;
00134 char buf[257], escapebuf[513];
00135
00136 if (!sql || !sql2) {
00137 if (sql) {
00138 ast_free(sql);
00139 }
00140 if (sql2) {
00141 ast_free(sql2);
00142 }
00143 return -1;
00144 }
00145
00146 lensql = snprintf(sql, sizesql, "INSERT INTO %s (", table);
00147 lensql2 = snprintf(sql2, sizesql2, " VALUES (");
00148
00149 AST_RWLIST_RDLOCK(&psql_columns);
00150 AST_RWLIST_TRAVERSE(&psql_columns, cur, list) {
00151
00152 ast_cdr_getvar(cdr, cur->name, &value, buf, sizeof(buf), 0, 0);
00153 if (strcmp(cur->name, "calldate") == 0 && !value) {
00154 ast_cdr_getvar(cdr, "start", &value, buf, sizeof(buf), 0, 0);
00155 }
00156 if (!value) {
00157 if (cur->notnull && !cur->hasdefault) {
00158
00159 LENGTHEN_BUF1(strlen(cur->name) + 2);
00160 lensql += snprintf(sql + lensql, sizesql - lensql, "\"%s\",", cur->name);
00161 LENGTHEN_BUF2(3);
00162 strcat(sql2, "'',");
00163 lensql2 += 3;
00164 }
00165 continue;
00166 }
00167
00168 LENGTHEN_BUF1(strlen(cur->name) + 2);
00169 lensql += snprintf(sql + lensql, sizesql - lensql, "\"%s\",", cur->name);
00170
00171 if (strcmp(cur->name, "start") == 0 || strcmp(cur->name, "calldate") == 0) {
00172 if (strncmp(cur->type, "int", 3) == 0) {
00173 LENGTHEN_BUF2(12);
00174 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%ld", cdr->start.tv_sec);
00175 } else if (strncmp(cur->type, "float", 5) == 0) {
00176 LENGTHEN_BUF2(30);
00177 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%f", (double)cdr->start.tv_sec + (double)cdr->start.tv_usec / 1000000.0);
00178 } else {
00179
00180 LENGTHEN_BUF2(30);
00181 ast_localtime(&cdr->start, &tm, NULL);
00182 lensql2 += ast_strftime(sql2 + lensql2, sizesql2 - lensql2, DATE_FORMAT, &tm);
00183 }
00184 } else if (strcmp(cur->name, "answer") == 0) {
00185 if (strncmp(cur->type, "int", 3) == 0) {
00186 LENGTHEN_BUF2(12);
00187 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%ld", cdr->answer.tv_sec);
00188 } else if (strncmp(cur->type, "float", 5) == 0) {
00189 LENGTHEN_BUF2(30);
00190 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%f", (double)cdr->answer.tv_sec + (double)cdr->answer.tv_usec / 1000000.0);
00191 } else {
00192
00193 LENGTHEN_BUF2(30);
00194 ast_localtime(&cdr->start, &tm, NULL);
00195 lensql2 += ast_strftime(sql2 + lensql2, sizesql2 - lensql2, DATE_FORMAT, &tm);
00196 }
00197 } else if (strcmp(cur->name, "end") == 0) {
00198 if (strncmp(cur->type, "int", 3) == 0) {
00199 LENGTHEN_BUF2(12);
00200 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%ld", cdr->end.tv_sec);
00201 } else if (strncmp(cur->type, "float", 5) == 0) {
00202 LENGTHEN_BUF2(30);
00203 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%f", (double)cdr->end.tv_sec + (double)cdr->end.tv_usec / 1000000.0);
00204 } else {
00205
00206 LENGTHEN_BUF2(30);
00207 ast_localtime(&cdr->end, &tm, NULL);
00208 lensql2 += ast_strftime(sql2 + lensql2, sizesql2 - lensql2, DATE_FORMAT, &tm);
00209 }
00210 } else if (strcmp(cur->name, "duration") == 0 || strcmp(cur->name, "billsec") == 0) {
00211 if (cur->type[0] == 'i') {
00212
00213 ast_cdr_getvar(cdr, cur->name, &value, buf, sizeof(buf), 0, 0);
00214 LENGTHEN_BUF2(12);
00215 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%s", value);
00216 } else if (strncmp(cur->type, "float", 5) == 0) {
00217 struct timeval *tv = cur->name[0] == 'd' ? &cdr->start : &cdr->answer;
00218 LENGTHEN_BUF2(30);
00219 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%f", (double)cdr->end.tv_sec - tv->tv_sec + cdr->end.tv_usec / 1000000.0 - tv->tv_usec / 1000000.0);
00220 } else {
00221
00222 struct timeval *tv = cur->name[0] == 'd' ? &cdr->start : &cdr->answer;
00223 LENGTHEN_BUF2(30);
00224 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "'%f'", (double)cdr->end.tv_sec - tv->tv_sec + cdr->end.tv_usec / 1000000.0 - tv->tv_usec / 1000000.0);
00225 }
00226 } else if (strcmp(cur->name, "disposition") == 0 || strcmp(cur->name, "amaflags") == 0) {
00227 if (strncmp(cur->type, "int", 3) == 0) {
00228
00229 ast_cdr_getvar(cdr, cur->name, &value, buf, sizeof(buf), 0, 1);
00230 LENGTHEN_BUF2(12);
00231 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%s", value);
00232 } else {
00233
00234 ast_cdr_getvar(cdr, cur->name, &value, buf, sizeof(buf), 0, 0);
00235 LENGTHEN_BUF2(30);
00236 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "'%s'", value);
00237 }
00238 } else {
00239
00240 ast_cdr_getvar(cdr, cur->name, &value, buf, sizeof(buf), 0, 0);
00241 if (strncmp(cur->type, "int", 3) == 0) {
00242 long long whatever;
00243 if (value && sscanf(value, "%lld", &whatever) == 1) {
00244 LENGTHEN_BUF2(25);
00245 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%lld", whatever);
00246 } else {
00247 LENGTHEN_BUF2(1);
00248 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "0");
00249 }
00250 } else if (strncmp(cur->type, "float", 5) == 0) {
00251 long double whatever;
00252 if (value && sscanf(value, "%Lf", &whatever) == 1) {
00253 LENGTHEN_BUF2(50);
00254 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "%30Lf", whatever);
00255 } else {
00256 LENGTHEN_BUF2(1);
00257 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "0");
00258 }
00259
00260 } else {
00261 if (value)
00262 PQescapeStringConn(conn, escapebuf, value, strlen(value), NULL);
00263 else
00264 escapebuf[0] = '\0';
00265 LENGTHEN_BUF2(strlen(escapebuf) + 2);
00266 lensql2 += snprintf(sql2 + lensql2, sizesql2 - lensql2, "'%s'", escapebuf);
00267 }
00268 }
00269 LENGTHEN_BUF2(1);
00270 strcat(sql2 + lensql2, ",");
00271 lensql2++;
00272 }
00273 AST_RWLIST_UNLOCK(&psql_columns);
00274 LENGTHEN_BUF1(lensql2);
00275 sql[lensql - 1] = ')';
00276 sql2[lensql2 - 1] = ')';
00277 strcat(sql + lensql, sql2);
00278 ast_verb(11, "[%s]\n", sql);
00279
00280 ast_debug(2, "cdr_pgsql: inserting a CDR record.\n");
00281
00282
00283
00284
00285 if (PQstatus(conn) == CONNECTION_OK) {
00286 connected = 1;
00287 } else {
00288 ast_log(LOG_ERROR, "cdr_pgsql: Connection was lost... attempting to reconnect.\n");
00289 PQreset(conn);
00290 if (PQstatus(conn) == CONNECTION_OK) {
00291 ast_log(LOG_ERROR, "cdr_pgsql: Connection reestablished.\n");
00292 connected = 1;
00293 } else {
00294 pgerror = PQerrorMessage(conn);
00295 ast_log(LOG_ERROR, "cdr_pgsql: Unable to reconnect to database server %s. Calls will not be logged!\n", pghostname);
00296 ast_log(LOG_ERROR, "cdr_pgsql: Reason: %s\n", pgerror);
00297 PQfinish(conn);
00298 conn = NULL;
00299 connected = 0;
00300 ast_mutex_unlock(&pgsql_lock);
00301 ast_free(sql);
00302 ast_free(sql2);
00303 return -1;
00304 }
00305 }
00306 result = PQexec(conn, sql);
00307 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
00308 pgerror = PQresultErrorMessage(result);
00309 ast_log(LOG_ERROR,"cdr_pgsql: Failed to insert call detail record into database!\n");
00310 ast_log(LOG_ERROR,"cdr_pgsql: Reason: %s\n", pgerror);
00311 ast_log(LOG_ERROR,"cdr_pgsql: Connection may have been lost... attempting to reconnect.\n");
00312 PQreset(conn);
00313 if (PQstatus(conn) == CONNECTION_OK) {
00314 ast_log(LOG_ERROR, "cdr_pgsql: Connection reestablished.\n");
00315 connected = 1;
00316 PQclear(result);
00317 result = PQexec(conn, sql);
00318 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
00319 pgerror = PQresultErrorMessage(result);
00320 ast_log(LOG_ERROR,"cdr_pgsql: HARD ERROR! Attempted reconnection failed. DROPPING CALL RECORD!\n");
00321 ast_log(LOG_ERROR,"cdr_pgsql: Reason: %s\n", pgerror);
00322 }
00323 }
00324 ast_mutex_unlock(&pgsql_lock);
00325 PQclear(result);
00326 ast_free(sql);
00327 ast_free(sql2);
00328 return -1;
00329 }
00330 PQclear(result);
00331 ast_free(sql);
00332 ast_free(sql2);
00333 }
00334 ast_mutex_unlock(&pgsql_lock);
00335 return 0;
00336 }
00337
00338 static int unload_module(void)
00339 {
00340 struct columns *cur;
00341 ast_cdr_unregister(name);
00342
00343
00344 usleep(1);
00345 PQfinish(conn);
00346
00347 if (pghostname)
00348 ast_free(pghostname);
00349 if (pgdbname)
00350 ast_free(pgdbname);
00351 if (pgdbuser)
00352 ast_free(pgdbuser);
00353 if (pgpassword)
00354 ast_free(pgpassword);
00355 if (pgdbport)
00356 ast_free(pgdbport);
00357 if (table)
00358 ast_free(table);
00359
00360 AST_RWLIST_WRLOCK(&psql_columns);
00361 while ((cur = AST_RWLIST_REMOVE_HEAD(&psql_columns, list))) {
00362 ast_free(cur);
00363 }
00364 AST_RWLIST_UNLOCK(&psql_columns);
00365
00366 return 0;
00367 }
00368
00369 static int config_module(int reload)
00370 {
00371 struct ast_variable *var;
00372 char *pgerror;
00373 struct columns *cur;
00374 PGresult *result;
00375 const char *tmp;
00376 struct ast_config *cfg;
00377 struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
00378
00379 if ((cfg = ast_config_load(config, config_flags)) == NULL) {
00380 ast_log(LOG_WARNING, "Unable to load config for PostgreSQL CDR's: %s\n", config);
00381 return -1;
00382 } else if (cfg == CONFIG_STATUS_FILEUNCHANGED)
00383 return 0;
00384
00385 if (!(var = ast_variable_browse(cfg, "global"))) {
00386 ast_config_destroy(cfg);
00387 return 0;
00388 }
00389
00390 if (!(tmp = ast_variable_retrieve(cfg, "global", "hostname"))) {
00391 ast_log(LOG_WARNING, "PostgreSQL server hostname not specified. Assuming unix socket connection\n");
00392 tmp = "";
00393 }
00394
00395 if (pghostname)
00396 ast_free(pghostname);
00397 if (!(pghostname = ast_strdup(tmp))) {
00398 ast_config_destroy(cfg);
00399 return -1;
00400 }
00401
00402 if (!(tmp = ast_variable_retrieve(cfg, "global", "dbname"))) {
00403 ast_log(LOG_WARNING,"PostgreSQL database not specified. Assuming asterisk\n");
00404 tmp = "asteriskcdrdb";
00405 }
00406
00407 if (pgdbname)
00408 ast_free(pgdbname);
00409 if (!(pgdbname = ast_strdup(tmp))) {
00410 ast_config_destroy(cfg);
00411 return -1;
00412 }
00413
00414 if (!(tmp = ast_variable_retrieve(cfg, "global", "user"))) {
00415 ast_log(LOG_WARNING,"PostgreSQL database user not specified. Assuming asterisk\n");
00416 tmp = "asterisk";
00417 }
00418
00419 if (pgdbuser)
00420 ast_free(pgdbuser);
00421 if (!(pgdbuser = ast_strdup(tmp))) {
00422 ast_config_destroy(cfg);
00423 return -1;
00424 }
00425
00426 if (!(tmp = ast_variable_retrieve(cfg, "global", "password"))) {
00427 ast_log(LOG_WARNING,"PostgreSQL database password not specified. Assuming blank\n");
00428 tmp = "";
00429 }
00430
00431 if (pgpassword)
00432 ast_free(pgpassword);
00433 if (!(pgpassword = ast_strdup(tmp))) {
00434 ast_config_destroy(cfg);
00435 return -1;
00436 }
00437
00438 if (!(tmp = ast_variable_retrieve(cfg,"global","port"))) {
00439 ast_log(LOG_WARNING,"PostgreSQL database port not specified. Using default 5432.\n");
00440 tmp = "5432";
00441 }
00442
00443 if (pgdbport)
00444 ast_free(pgdbport);
00445 if (!(pgdbport = ast_strdup(tmp))) {
00446 ast_config_destroy(cfg);
00447 return -1;
00448 }
00449
00450 if (!(tmp = ast_variable_retrieve(cfg, "global", "table"))) {
00451 ast_log(LOG_WARNING,"CDR table not specified. Assuming cdr\n");
00452 tmp = "cdr";
00453 }
00454
00455 if (table)
00456 ast_free(table);
00457 if (!(table = ast_strdup(tmp))) {
00458 ast_config_destroy(cfg);
00459 return -1;
00460 }
00461
00462 if (option_debug) {
00463 if (ast_strlen_zero(pghostname))
00464 ast_debug(1, "cdr_pgsql: using default unix socket\n");
00465 else
00466 ast_debug(1, "cdr_pgsql: got hostname of %s\n", pghostname);
00467 ast_debug(1, "cdr_pgsql: got port of %s\n", pgdbport);
00468 ast_debug(1, "cdr_pgsql: got user of %s\n", pgdbuser);
00469 ast_debug(1, "cdr_pgsql: got dbname of %s\n", pgdbname);
00470 ast_debug(1, "cdr_pgsql: got password of %s\n", pgpassword);
00471 ast_debug(1, "cdr_pgsql: got sql table name of %s\n", table);
00472 }
00473
00474 conn = PQsetdbLogin(pghostname, pgdbport, NULL, NULL, pgdbname, pgdbuser, pgpassword);
00475 if (PQstatus(conn) != CONNECTION_BAD) {
00476 char sqlcmd[512];
00477 char *fname, *ftype, *flen, *fnotnull, *fdef;
00478 char *tableptr;
00479 int i, rows;
00480 ast_debug(1, "Successfully connected to PostgreSQL database.\n");
00481 connected = 1;
00482
00483
00484 if ((tableptr = strrchr(table, '.'))) {
00485 tableptr++;
00486 } else {
00487 tableptr = table;
00488 }
00489
00490
00491 snprintf(sqlcmd, sizeof(sqlcmd), "select a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc from pg_class c, pg_type t, pg_attribute a left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum where c.oid = a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname = '%s' order by c.relname, attnum", tableptr);
00492 result = PQexec(conn, sqlcmd);
00493 if (PQresultStatus(result) != PGRES_TUPLES_OK) {
00494 pgerror = PQresultErrorMessage(result);
00495 ast_log(LOG_ERROR, "cdr_pgsql: Failed to query database columns: %s\n", pgerror);
00496 PQclear(result);
00497 unload_module();
00498 return AST_MODULE_LOAD_DECLINE;
00499 }
00500
00501 rows = PQntuples(result);
00502 for (i = 0; i < rows; i++) {
00503 fname = PQgetvalue(result, i, 0);
00504 ftype = PQgetvalue(result, i, 1);
00505 flen = PQgetvalue(result, i, 2);
00506 fnotnull = PQgetvalue(result, i, 3);
00507 fdef = PQgetvalue(result, i, 4);
00508 ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
00509 cur = ast_calloc(1, sizeof(*cur) + strlen(fname) + strlen(ftype) + 2);
00510 if (cur) {
00511 sscanf(flen, "%d", &cur->len);
00512 cur->name = (char *)cur + sizeof(*cur);
00513 cur->type = (char *)cur + sizeof(*cur) + strlen(fname) + 1;
00514 strcpy(cur->name, fname);
00515 strcpy(cur->type, ftype);
00516 if (*fnotnull == 't') {
00517 cur->notnull = 1;
00518 } else {
00519 cur->notnull = 0;
00520 }
00521 if (!ast_strlen_zero(fdef)) {
00522 cur->hasdefault = 1;
00523 } else {
00524 cur->hasdefault = 0;
00525 }
00526 AST_RWLIST_INSERT_TAIL(&psql_columns, cur, list);
00527 }
00528 }
00529 PQclear(result);
00530 } else {
00531 pgerror = PQerrorMessage(conn);
00532 ast_log(LOG_ERROR, "cdr_pgsql: Unable to connect to database server %s. CALLS WILL NOT BE LOGGED!!\n", pghostname);
00533 ast_log(LOG_ERROR, "cdr_pgsql: Reason: %s\n", pgerror);
00534 connected = 0;
00535 }
00536
00537 ast_config_destroy(cfg);
00538
00539 return ast_cdr_register(name, ast_module_info->description, pgsql_log);
00540 }
00541
00542 static int load_module(void)
00543 {
00544 return config_module(0) ? AST_MODULE_LOAD_DECLINE : 0;
00545 }
00546
00547 static int reload(void)
00548 {
00549 return config_module(1);
00550 }
00551
00552 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "PostgreSQL CDR Backend",
00553 .load = load_module,
00554 .unload = unload_module,
00555 .reload = reload,
00556 );