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 #include "asterisk.h"
00030
00031 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 145123 $")
00032
00033 #include <libpq-fe.h>
00034
00035 #include "asterisk/file.h"
00036 #include "asterisk/channel.h"
00037 #include "asterisk/pbx.h"
00038 #include "asterisk/config.h"
00039 #include "asterisk/module.h"
00040 #include "asterisk/lock.h"
00041 #include "asterisk/utils.h"
00042 #include "asterisk/cli.h"
00043
00044 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
00045
00046 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
00047
00048 PGconn *pgsqlConn = NULL;
00049
00050 #define MAX_DB_OPTION_SIZE 64
00051
00052 static char dbhost[MAX_DB_OPTION_SIZE] = "";
00053 static char dbuser[MAX_DB_OPTION_SIZE] = "";
00054 static char dbpass[MAX_DB_OPTION_SIZE] = "";
00055 static char dbname[MAX_DB_OPTION_SIZE] = "";
00056 static char dbsock[MAX_DB_OPTION_SIZE] = "";
00057 static int dbport = 5432;
00058 static time_t connect_time = 0;
00059
00060 static int parse_config(int reload);
00061 static int pgsql_reconnect(const char *database);
00062 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
00063
00064 static struct ast_cli_entry cli_realtime[] = {
00065 AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
00066 };
00067
00068 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
00069 {
00070 PGresult *result = NULL;
00071 int num_rows = 0, pgerror;
00072 char sql[256], escapebuf[513];
00073 char *stringp;
00074 char *chunk;
00075 char *op;
00076 const char *newparam, *newval;
00077 struct ast_variable *var = NULL, *prev = NULL;
00078
00079 if (!table) {
00080 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00081 return NULL;
00082 }
00083
00084
00085 newparam = va_arg(ap, const char *);
00086 newval = va_arg(ap, const char *);
00087 if (!newparam || !newval) {
00088 ast_log(LOG_WARNING,
00089 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00090 if (pgsqlConn) {
00091 PQfinish(pgsqlConn);
00092 pgsqlConn = NULL;
00093 };
00094 return NULL;
00095 }
00096
00097
00098
00099 op = strchr(newparam, ' ') ? "" : " =";
00100
00101 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00102 if (pgerror) {
00103 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00104 va_end(ap);
00105 return NULL;
00106 }
00107
00108 snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
00109 escapebuf);
00110 while ((newparam = va_arg(ap, const char *))) {
00111 newval = va_arg(ap, const char *);
00112 if (!strchr(newparam, ' '))
00113 op = " =";
00114 else
00115 op = "";
00116
00117 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00118 if (pgerror) {
00119 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00120 va_end(ap);
00121 return NULL;
00122 }
00123
00124 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
00125 op, escapebuf);
00126 }
00127 va_end(ap);
00128
00129
00130 ast_mutex_lock(&pgsql_lock);
00131 if (!pgsql_reconnect(database)) {
00132 ast_mutex_unlock(&pgsql_lock);
00133 return NULL;
00134 }
00135
00136 if (!(result = PQexec(pgsqlConn, sql))) {
00137 ast_log(LOG_WARNING,
00138 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00139 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00140 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00141 ast_mutex_unlock(&pgsql_lock);
00142 return NULL;
00143 } else {
00144 ExecStatusType result_status = PQresultStatus(result);
00145 if (result_status != PGRES_COMMAND_OK
00146 && result_status != PGRES_TUPLES_OK
00147 && result_status != PGRES_NONFATAL_ERROR) {
00148 ast_log(LOG_WARNING,
00149 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00150 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00151 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00152 PQresultErrorMessage(result), PQresStatus(result_status));
00153 ast_mutex_unlock(&pgsql_lock);
00154 return NULL;
00155 }
00156 }
00157
00158 ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
00159
00160 if ((num_rows = PQntuples(result)) > 0) {
00161 int i = 0;
00162 int rowIndex = 0;
00163 int numFields = PQnfields(result);
00164 char **fieldnames = NULL;
00165
00166 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
00167
00168 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
00169 ast_mutex_unlock(&pgsql_lock);
00170 PQclear(result);
00171 return NULL;
00172 }
00173 for (i = 0; i < numFields; i++)
00174 fieldnames[i] = PQfname(result, i);
00175 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00176 for (i = 0; i < numFields; i++) {
00177 stringp = PQgetvalue(result, rowIndex, i);
00178 while (stringp) {
00179 chunk = strsep(&stringp, ";");
00180 if (!ast_strlen_zero(ast_strip(chunk))) {
00181 if (prev) {
00182 prev->next = ast_variable_new(fieldnames[i], chunk, "");
00183 if (prev->next) {
00184 prev = prev->next;
00185 }
00186 } else {
00187 prev = var = ast_variable_new(fieldnames[i], chunk, "");
00188 }
00189 }
00190 }
00191 }
00192 }
00193 ast_free(fieldnames);
00194 } else {
00195 ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s.\n", table);
00196 }
00197
00198 ast_mutex_unlock(&pgsql_lock);
00199 PQclear(result);
00200
00201 return var;
00202 }
00203
00204 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
00205 {
00206 PGresult *result = NULL;
00207 int num_rows = 0, pgerror;
00208 char sql[256], escapebuf[513];
00209 const char *initfield = NULL;
00210 char *stringp;
00211 char *chunk;
00212 char *op;
00213 const char *newparam, *newval;
00214 struct ast_variable *var = NULL;
00215 struct ast_config *cfg = NULL;
00216 struct ast_category *cat = NULL;
00217
00218 if (!table) {
00219 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00220 return NULL;
00221 }
00222
00223 if (!(cfg = ast_config_new()))
00224 return NULL;
00225
00226
00227 newparam = va_arg(ap, const char *);
00228 newval = va_arg(ap, const char *);
00229 if (!newparam || !newval) {
00230 ast_log(LOG_WARNING,
00231 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00232 if (pgsqlConn) {
00233 PQfinish(pgsqlConn);
00234 pgsqlConn = NULL;
00235 };
00236 return NULL;
00237 }
00238
00239 initfield = ast_strdupa(newparam);
00240 if ((op = strchr(initfield, ' '))) {
00241 *op = '\0';
00242 }
00243
00244
00245
00246
00247 if (!strchr(newparam, ' '))
00248 op = " =";
00249 else
00250 op = "";
00251
00252 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00253 if (pgerror) {
00254 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00255 va_end(ap);
00256 return NULL;
00257 }
00258
00259 snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
00260 escapebuf);
00261 while ((newparam = va_arg(ap, const char *))) {
00262 newval = va_arg(ap, const char *);
00263 if (!strchr(newparam, ' '))
00264 op = " =";
00265 else
00266 op = "";
00267
00268 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00269 if (pgerror) {
00270 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00271 va_end(ap);
00272 return NULL;
00273 }
00274
00275 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
00276 op, escapebuf);
00277 }
00278
00279 if (initfield) {
00280 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00281 }
00282
00283 va_end(ap);
00284
00285
00286 ast_mutex_lock(&pgsql_lock);
00287 if (!pgsql_reconnect(database)) {
00288 ast_mutex_unlock(&pgsql_lock);
00289 return NULL;
00290 }
00291
00292 if (!(result = PQexec(pgsqlConn, sql))) {
00293 ast_log(LOG_WARNING,
00294 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00295 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00296 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00297 ast_mutex_unlock(&pgsql_lock);
00298 return NULL;
00299 } else {
00300 ExecStatusType result_status = PQresultStatus(result);
00301 if (result_status != PGRES_COMMAND_OK
00302 && result_status != PGRES_TUPLES_OK
00303 && result_status != PGRES_NONFATAL_ERROR) {
00304 ast_log(LOG_WARNING,
00305 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00306 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00307 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00308 PQresultErrorMessage(result), PQresStatus(result_status));
00309 ast_mutex_unlock(&pgsql_lock);
00310 return NULL;
00311 }
00312 }
00313
00314 ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
00315
00316 if ((num_rows = PQntuples(result)) > 0) {
00317 int numFields = PQnfields(result);
00318 int i = 0;
00319 int rowIndex = 0;
00320 char **fieldnames = NULL;
00321
00322 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
00323
00324 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
00325 ast_mutex_unlock(&pgsql_lock);
00326 PQclear(result);
00327 return NULL;
00328 }
00329 for (i = 0; i < numFields; i++)
00330 fieldnames[i] = PQfname(result, i);
00331
00332 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00333 var = NULL;
00334 if (!(cat = ast_category_new("","",99999)))
00335 continue;
00336 for (i = 0; i < numFields; i++) {
00337 stringp = PQgetvalue(result, rowIndex, i);
00338 while (stringp) {
00339 chunk = strsep(&stringp, ";");
00340 if (!ast_strlen_zero(ast_strip(chunk))) {
00341 if (initfield && !strcmp(initfield, fieldnames[i])) {
00342 ast_category_rename(cat, chunk);
00343 }
00344 var = ast_variable_new(fieldnames[i], chunk, "");
00345 ast_variable_append(cat, var);
00346 }
00347 }
00348 }
00349 ast_category_append(cfg, cat);
00350 }
00351 ast_free(fieldnames);
00352 } else {
00353 ast_log(LOG_WARNING,
00354 "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
00355 }
00356
00357 ast_mutex_unlock(&pgsql_lock);
00358 PQclear(result);
00359
00360 return cfg;
00361 }
00362
00363 static int update_pgsql(const char *database, const char *table, const char *keyfield,
00364 const char *lookup, va_list ap)
00365 {
00366 PGresult *result = NULL;
00367 int numrows = 0, pgerror;
00368 char sql[256], escapebuf[513];
00369 const char *newparam, *newval;
00370
00371 if (!table) {
00372 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00373 return -1;
00374 }
00375
00376
00377 newparam = va_arg(ap, const char *);
00378 newval = va_arg(ap, const char *);
00379 if (!newparam || !newval) {
00380 ast_log(LOG_WARNING,
00381 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00382 if (pgsqlConn) {
00383 PQfinish(pgsqlConn);
00384 pgsqlConn = NULL;
00385 };
00386 return -1;
00387 }
00388
00389
00390
00391
00392 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00393 if (pgerror) {
00394 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00395 va_end(ap);
00396 return -1;
00397 }
00398 snprintf(sql, sizeof(sql), "UPDATE %s SET %s = '%s'", table, newparam, escapebuf);
00399
00400 while ((newparam = va_arg(ap, const char *))) {
00401 newval = va_arg(ap, const char *);
00402
00403 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00404 if (pgerror) {
00405 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00406 va_end(ap);
00407 return -1;
00408 }
00409
00410 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s = '%s'", newparam,
00411 escapebuf);
00412 }
00413 va_end(ap);
00414
00415 PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
00416 if (pgerror) {
00417 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
00418 va_end(ap);
00419 return -1;
00420 }
00421
00422 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s = '%s'", keyfield,
00423 escapebuf);
00424
00425 ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", sql);
00426
00427
00428 ast_mutex_lock(&pgsql_lock);
00429 if (!pgsql_reconnect(database)) {
00430 ast_mutex_unlock(&pgsql_lock);
00431 return -1;
00432 }
00433
00434 if (!(result = PQexec(pgsqlConn, sql))) {
00435 ast_log(LOG_WARNING,
00436 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00437 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00438 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00439 ast_mutex_unlock(&pgsql_lock);
00440 return -1;
00441 } else {
00442 ExecStatusType result_status = PQresultStatus(result);
00443 if (result_status != PGRES_COMMAND_OK
00444 && result_status != PGRES_TUPLES_OK
00445 && result_status != PGRES_NONFATAL_ERROR) {
00446 ast_log(LOG_WARNING,
00447 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00448 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00449 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00450 PQresultErrorMessage(result), PQresStatus(result_status));
00451 ast_mutex_unlock(&pgsql_lock);
00452 return -1;
00453 }
00454 }
00455
00456 numrows = atoi(PQcmdTuples(result));
00457 ast_mutex_unlock(&pgsql_lock);
00458
00459 ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, table);
00460
00461
00462
00463
00464
00465
00466
00467 if (numrows >= 0)
00468 return (int) numrows;
00469
00470 return -1;
00471 }
00472
00473 #define ESCAPE_STRING(buffer, stringname) \
00474 do { \
00475 int len; \
00476 if ((len = strlen(stringname)) > (buffer->len - 1) / 2) { \
00477 ast_str_make_space(&buffer, len * 2 + 1); \
00478 } \
00479 PQescapeStringConn(pgsqlConn, buffer->str, stringname, len, &pgresult); \
00480 } while (0)
00481
00482 static int store_pgsql(const char *database, const char *table, va_list ap)
00483 {
00484 PGresult *result = NULL;
00485 Oid insertid;
00486 struct ast_str *buf = ast_str_create(256);
00487 struct ast_str *sql1 = ast_str_create(256);
00488 struct ast_str *sql2 = ast_str_create(256);
00489 int pgresult;
00490 const char *newparam, *newval;
00491
00492 if (!table) {
00493 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00494 return -1;
00495 }
00496
00497
00498 newparam = va_arg(ap, const char *);
00499 newval = va_arg(ap, const char *);
00500 if (!newparam || !newval) {
00501 ast_log(LOG_WARNING,
00502 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
00503 if (pgsqlConn) {
00504 PQfinish(pgsqlConn);
00505 pgsqlConn = NULL;
00506 }
00507 return -1;
00508 }
00509
00510
00511 ast_mutex_lock(&pgsql_lock);
00512 if (!pgsql_reconnect(database)) {
00513 ast_mutex_unlock(&pgsql_lock);
00514 return -1;
00515 }
00516
00517
00518
00519 ESCAPE_STRING(buf, newparam);
00520 ast_str_set(&sql1, 0, "INSERT INTO %s (%s", table, buf->str);
00521 ESCAPE_STRING(buf, newval);
00522 ast_str_set(&sql2, 0, ") VALUES ('%s'", buf->str);
00523 while ((newparam = va_arg(ap, const char *))) {
00524 newval = va_arg(ap, const char *);
00525 ESCAPE_STRING(buf, newparam);
00526 ast_str_append(&sql1, 0, ", %s", buf->str);
00527 ESCAPE_STRING(buf, newval);
00528 ast_str_append(&sql2, 0, ", '%s'", buf->str);
00529 }
00530 va_end(ap);
00531 ast_str_append(&sql1, 0, "%s)", sql2->str);
00532
00533 ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", sql1->str);
00534
00535 if (!(result = PQexec(pgsqlConn, sql1->str))) {
00536 ast_log(LOG_WARNING,
00537 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00538 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql1->str);
00539 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00540 ast_mutex_unlock(&pgsql_lock);
00541 ast_free(sql1);
00542 ast_free(sql2);
00543 ast_free(buf);
00544 return -1;
00545 } else {
00546 ExecStatusType result_status = PQresultStatus(result);
00547 if (result_status != PGRES_COMMAND_OK
00548 && result_status != PGRES_TUPLES_OK
00549 && result_status != PGRES_NONFATAL_ERROR) {
00550 ast_log(LOG_WARNING,
00551 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00552 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql1->str);
00553 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00554 PQresultErrorMessage(result), PQresStatus(result_status));
00555 ast_mutex_unlock(&pgsql_lock);
00556 ast_free(sql1);
00557 ast_free(sql2);
00558 ast_free(buf);
00559 return -1;
00560 }
00561 }
00562
00563 insertid = PQoidValue(result);
00564 ast_mutex_unlock(&pgsql_lock);
00565 ast_free(sql1);
00566 ast_free(sql2);
00567 ast_free(buf);
00568
00569 ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
00570
00571
00572
00573
00574
00575
00576
00577 if (insertid >= 0)
00578 return (int) insertid;
00579
00580 return -1;
00581 }
00582
00583 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00584 {
00585 PGresult *result = NULL;
00586 int numrows = 0;
00587 int pgresult;
00588 struct ast_str *sql = ast_str_create(256);
00589 struct ast_str *buf1 = ast_str_create(60), *buf2 = ast_str_create(60);
00590 const char *newparam, *newval;
00591
00592 if (!table) {
00593 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00594 return -1;
00595 }
00596
00597
00598
00599
00600
00601 if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup)) {
00602 ast_log(LOG_WARNING,
00603 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
00604 if (pgsqlConn) {
00605 PQfinish(pgsqlConn);
00606 pgsqlConn = NULL;
00607 };
00608 return -1;
00609 }
00610
00611
00612 ast_mutex_lock(&pgsql_lock);
00613 if (!pgsql_reconnect(database)) {
00614 ast_mutex_unlock(&pgsql_lock);
00615 return -1;
00616 }
00617
00618
00619
00620
00621
00622 ESCAPE_STRING(buf1, keyfield);
00623 ESCAPE_STRING(buf2, lookup);
00624 ast_str_set(&sql, 0, "DELETE FROM %s WHERE %s = '%s'", table, buf1->str, buf2->str);
00625 while ((newparam = va_arg(ap, const char *))) {
00626 newval = va_arg(ap, const char *);
00627 ESCAPE_STRING(buf1, newparam);
00628 ESCAPE_STRING(buf2, newval);
00629 ast_str_append(&sql, 0, " AND %s = '%s'", buf1->str, buf2->str);
00630 }
00631 va_end(ap);
00632
00633 ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", sql->str);
00634
00635 if (!(result = PQexec(pgsqlConn, sql->str))) {
00636 ast_log(LOG_WARNING,
00637 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00638 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
00639 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00640 ast_mutex_unlock(&pgsql_lock);
00641 ast_free(buf1);
00642 ast_free(buf2);
00643 ast_free(sql);
00644 return -1;
00645 } else {
00646 ExecStatusType result_status = PQresultStatus(result);
00647 if (result_status != PGRES_COMMAND_OK
00648 && result_status != PGRES_TUPLES_OK
00649 && result_status != PGRES_NONFATAL_ERROR) {
00650 ast_log(LOG_WARNING,
00651 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00652 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
00653 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00654 PQresultErrorMessage(result), PQresStatus(result_status));
00655 ast_mutex_unlock(&pgsql_lock);
00656 ast_free(buf1);
00657 ast_free(buf2);
00658 ast_free(sql);
00659 return -1;
00660 }
00661 }
00662
00663 numrows = atoi(PQcmdTuples(result));
00664 ast_mutex_unlock(&pgsql_lock);
00665 ast_free(buf1);
00666 ast_free(buf2);
00667 ast_free(sql);
00668
00669 ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
00670
00671
00672
00673
00674
00675
00676
00677 if (numrows >= 0)
00678 return (int) numrows;
00679
00680 return -1;
00681 }
00682
00683
00684 static struct ast_config *config_pgsql(const char *database, const char *table,
00685 const char *file, struct ast_config *cfg,
00686 struct ast_flags flags, const char *suggested_incl, const char *who_asked)
00687 {
00688 PGresult *result = NULL;
00689 long num_rows;
00690 struct ast_variable *new_v;
00691 struct ast_category *cur_cat = NULL;
00692 char sqlbuf[1024] = "";
00693 char *sql = sqlbuf;
00694 size_t sqlleft = sizeof(sqlbuf);
00695 char last[80] = "";
00696 int last_cat_metric = 0;
00697
00698 last[0] = '\0';
00699
00700 if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
00701 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
00702 return NULL;
00703 }
00704
00705 ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
00706 ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
00707 ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00708
00709 ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", sqlbuf);
00710
00711
00712 ast_mutex_lock(&pgsql_lock);
00713 if (!pgsql_reconnect(database)) {
00714 ast_mutex_unlock(&pgsql_lock);
00715 return NULL;
00716 }
00717
00718 if (!(result = PQexec(pgsqlConn, sqlbuf))) {
00719 ast_log(LOG_WARNING,
00720 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00721 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00722 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00723 ast_mutex_unlock(&pgsql_lock);
00724 return NULL;
00725 } else {
00726 ExecStatusType result_status = PQresultStatus(result);
00727 if (result_status != PGRES_COMMAND_OK
00728 && result_status != PGRES_TUPLES_OK
00729 && result_status != PGRES_NONFATAL_ERROR) {
00730 ast_log(LOG_WARNING,
00731 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00732 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00733 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00734 PQresultErrorMessage(result), PQresStatus(result_status));
00735 ast_mutex_unlock(&pgsql_lock);
00736 return NULL;
00737 }
00738 }
00739
00740 if ((num_rows = PQntuples(result)) > 0) {
00741 int rowIndex = 0;
00742
00743 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
00744
00745 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00746 char *field_category = PQgetvalue(result, rowIndex, 0);
00747 char *field_var_name = PQgetvalue(result, rowIndex, 1);
00748 char *field_var_val = PQgetvalue(result, rowIndex, 2);
00749 char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
00750 if (!strcmp(field_var_name, "#include")) {
00751 if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
00752 PQclear(result);
00753 ast_mutex_unlock(&pgsql_lock);
00754 return NULL;
00755 }
00756 continue;
00757 }
00758
00759 if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
00760 cur_cat = ast_category_new(field_category, "", 99999);
00761 if (!cur_cat)
00762 break;
00763 strcpy(last, field_category);
00764 last_cat_metric = atoi(field_cat_metric);
00765 ast_category_append(cfg, cur_cat);
00766 }
00767 new_v = ast_variable_new(field_var_name, field_var_val, "");
00768 ast_variable_append(cur_cat, new_v);
00769 }
00770 } else {
00771 ast_log(LOG_WARNING,
00772 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
00773 }
00774
00775 PQclear(result);
00776 ast_mutex_unlock(&pgsql_lock);
00777
00778 return cfg;
00779 }
00780
00781 static struct ast_config_engine pgsql_engine = {
00782 .name = "pgsql",
00783 .load_func = config_pgsql,
00784 .realtime_func = realtime_pgsql,
00785 .realtime_multi_func = realtime_multi_pgsql,
00786 .store_func = store_pgsql,
00787 .destroy_func = destroy_pgsql,
00788 .update_func = update_pgsql
00789 };
00790
00791 static int load_module(void)
00792 {
00793 if(!parse_config(0))
00794 return AST_MODULE_LOAD_DECLINE;
00795
00796 ast_config_engine_register(&pgsql_engine);
00797 ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
00798 ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
00799
00800 return 0;
00801 }
00802
00803 static int unload_module(void)
00804 {
00805
00806 ast_mutex_lock(&pgsql_lock);
00807
00808 if (pgsqlConn) {
00809 PQfinish(pgsqlConn);
00810 pgsqlConn = NULL;
00811 }
00812 ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
00813 ast_config_engine_deregister(&pgsql_engine);
00814 ast_verb(1, "PostgreSQL RealTime unloaded.\n");
00815
00816
00817 ast_mutex_unlock(&pgsql_lock);
00818
00819 return 0;
00820 }
00821
00822 static int reload(void)
00823 {
00824 parse_config(1);
00825
00826 return 0;
00827 }
00828
00829 static int parse_config(int reload)
00830 {
00831 struct ast_config *config;
00832 const char *s;
00833 struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
00834
00835 if ((config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags)) == CONFIG_STATUS_FILEUNCHANGED)
00836 return 0;
00837
00838 if (!config) {
00839 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
00840 return 0;
00841 }
00842
00843 ast_mutex_lock(&pgsql_lock);
00844
00845 if (pgsqlConn) {
00846 PQfinish(pgsqlConn);
00847 pgsqlConn = NULL;
00848 }
00849
00850 if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
00851 ast_log(LOG_WARNING,
00852 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
00853 strcpy(dbuser, "asterisk");
00854 } else {
00855 ast_copy_string(dbuser, s, sizeof(dbuser));
00856 }
00857
00858 if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
00859 ast_log(LOG_WARNING,
00860 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
00861 strcpy(dbpass, "asterisk");
00862 } else {
00863 ast_copy_string(dbpass, s, sizeof(dbpass));
00864 }
00865
00866 if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
00867 ast_log(LOG_WARNING,
00868 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
00869 dbhost[0] = '\0';
00870 } else {
00871 ast_copy_string(dbhost, s, sizeof(dbhost));
00872 }
00873
00874 if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
00875 ast_log(LOG_WARNING,
00876 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
00877 strcpy(dbname, "asterisk");
00878 } else {
00879 ast_copy_string(dbname, s, sizeof(dbname));
00880 }
00881
00882 if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
00883 ast_log(LOG_WARNING,
00884 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
00885 dbport = 5432;
00886 } else {
00887 dbport = atoi(s);
00888 }
00889
00890 if (!ast_strlen_zero(dbhost)) {
00891
00892 } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
00893 ast_log(LOG_WARNING,
00894 "PostgreSQL RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
00895 strcpy(dbsock, "/tmp/pgsql.sock");
00896 } else {
00897 ast_copy_string(dbsock, s, sizeof(dbsock));
00898 }
00899 ast_config_destroy(config);
00900
00901 if (option_debug) {
00902 if (!ast_strlen_zero(dbhost)) {
00903 ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
00904 ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
00905 } else {
00906 ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
00907 }
00908 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
00909 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
00910 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
00911 }
00912
00913 if (!pgsql_reconnect(NULL)) {
00914 ast_log(LOG_WARNING,
00915 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
00916 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
00917 }
00918
00919 ast_verb(2, "PostgreSQL RealTime reloaded.\n");
00920
00921
00922 ast_mutex_unlock(&pgsql_lock);
00923
00924 return 1;
00925 }
00926
00927 static int pgsql_reconnect(const char *database)
00928 {
00929 char my_database[50];
00930
00931 ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
00932
00933
00934
00935 if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
00936 PQfinish(pgsqlConn);
00937 pgsqlConn = NULL;
00938 }
00939
00940
00941 if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
00942 struct ast_str *connInfo = ast_str_create(32);
00943
00944 ast_str_set(&connInfo, 0, "host=%s port=%d dbname=%s user=%s",
00945 dbhost, dbport, my_database, dbuser);
00946 if (!ast_strlen_zero(dbpass))
00947 ast_str_append(&connInfo, 0, " password=%s", dbpass);
00948
00949 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
00950 pgsqlConn = PQconnectdb(connInfo->str);
00951 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
00952 ast_free(connInfo);
00953 connInfo = NULL;
00954
00955 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
00956 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
00957 ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
00958 connect_time = time(NULL);
00959 return 1;
00960 } else {
00961 ast_log(LOG_ERROR,
00962 "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
00963 dbname, dbhost, PQresultErrorMessage(NULL));
00964 return 0;
00965 }
00966 } else {
00967 ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
00968 return 1;
00969 }
00970 }
00971
00972 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
00973 {
00974 char status[256], credentials[100] = "";
00975 int ctime = time(NULL) - connect_time;
00976
00977 switch (cmd) {
00978 case CLI_INIT:
00979 e->command = "realtime pgsql status";
00980 e->usage =
00981 "Usage: realtime pgsql status\n"
00982 " Shows connection information for the PostgreSQL RealTime driver\n";
00983 return NULL;
00984 case CLI_GENERATE:
00985 return NULL;
00986 }
00987
00988 if (a->argc != 3)
00989 return CLI_SHOWUSAGE;
00990
00991 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
00992 if (!ast_strlen_zero(dbhost))
00993 snprintf(status, sizeof(status), "Connected to %s@%s, port %d", dbname, dbhost, dbport);
00994 else if (!ast_strlen_zero(dbsock))
00995 snprintf(status, sizeof(status), "Connected to %s on socket file %s", dbname, dbsock);
00996 else
00997 snprintf(status, sizeof(status), "Connected to %s@%s", dbname, dbhost);
00998
00999 if (!ast_strlen_zero(dbuser))
01000 snprintf(credentials, sizeof(credentials), " with username %s", dbuser);
01001
01002 if (ctime > 31536000)
01003 ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
01004 status, credentials, ctime / 31536000, (ctime % 31536000) / 86400,
01005 (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
01006 else if (ctime > 86400)
01007 ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
01008 credentials, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
01009 ctime % 60);
01010 else if (ctime > 3600)
01011 ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, credentials,
01012 ctime / 3600, (ctime % 3600) / 60, ctime % 60);
01013 else if (ctime > 60)
01014 ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, credentials, ctime / 60,
01015 ctime % 60);
01016 else
01017 ast_cli(a->fd, "%s%s for %d seconds.\n", status, credentials, ctime);
01018
01019 return CLI_SUCCESS;
01020 } else {
01021 return CLI_FAILURE;
01022 }
01023 }
01024
01025
01026 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
01027 .load = load_module,
01028 .unload = unload_module,
01029 .reload = reload
01030 );