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: 284472 $")
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 AST_THREADSTORAGE(sql_buf);
00046 AST_THREADSTORAGE(findtable_buf);
00047 AST_THREADSTORAGE(where_buf);
00048 AST_THREADSTORAGE(escapebuf_buf);
00049 AST_THREADSTORAGE(semibuf_buf);
00050
00051 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
00052
00053 PGconn *pgsqlConn = NULL;
00054 static int version;
00055 #define has_schema_support (version > 70300 ? 1 : 0)
00056
00057 #define MAX_DB_OPTION_SIZE 64
00058
00059 struct columns {
00060 char *name;
00061 char *type;
00062 int len;
00063 unsigned int notnull:1;
00064 unsigned int hasdefault:1;
00065 AST_LIST_ENTRY(columns) list;
00066 };
00067
00068 struct tables {
00069 ast_rwlock_t lock;
00070 AST_LIST_HEAD_NOLOCK(psql_columns, columns) columns;
00071 AST_LIST_ENTRY(tables) list;
00072 char name[0];
00073 };
00074
00075 static AST_LIST_HEAD_STATIC(psql_tables, tables);
00076
00077 static char dbhost[MAX_DB_OPTION_SIZE] = "";
00078 static char dbuser[MAX_DB_OPTION_SIZE] = "";
00079 static char dbpass[MAX_DB_OPTION_SIZE] = "";
00080 static char dbname[MAX_DB_OPTION_SIZE] = "";
00081 static char dbsock[MAX_DB_OPTION_SIZE] = "";
00082 static int dbport = 5432;
00083 static time_t connect_time = 0;
00084
00085 static int parse_config(int reload);
00086 static int pgsql_reconnect(const char *database);
00087 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
00088 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
00089
00090 enum { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR } requirements;
00091
00092 static struct ast_cli_entry cli_realtime[] = {
00093 AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
00094 AST_CLI_DEFINE(handle_cli_realtime_pgsql_cache, "Shows cached tables within the PostgreSQL realtime driver"),
00095 };
00096
00097 #define ESCAPE_STRING(buffer, stringname) \
00098 do { \
00099 int len = strlen(stringname); \
00100 struct ast_str *semi = ast_str_thread_get(&semibuf_buf, len * 3 + 1); \
00101 const char *chunk = stringname; \
00102 ast_str_reset(semi); \
00103 for (; *chunk; chunk++) { \
00104 if (strchr(";^", *chunk)) { \
00105 ast_str_append(&semi, 0, "^%02hhX", *chunk); \
00106 } else { \
00107 ast_str_append(&semi, 0, "%c", *chunk); \
00108 } \
00109 } \
00110 if (ast_str_strlen(semi) > (ast_str_size(buffer) - 1) / 2) { \
00111 ast_str_make_space(&buffer, ast_str_strlen(semi) * 2 + 1); \
00112 } \
00113 PQescapeStringConn(pgsqlConn, ast_str_buffer(buffer), ast_str_buffer(semi), ast_str_size(buffer), &pgresult); \
00114 } while (0)
00115
00116 static void destroy_table(struct tables *table)
00117 {
00118 struct columns *column;
00119 ast_rwlock_wrlock(&table->lock);
00120 while ((column = AST_LIST_REMOVE_HEAD(&table->columns, list))) {
00121 ast_free(column);
00122 }
00123 ast_rwlock_unlock(&table->lock);
00124 ast_rwlock_destroy(&table->lock);
00125 ast_free(table);
00126 }
00127
00128 static struct tables *find_table(const char *orig_tablename)
00129 {
00130 struct columns *column;
00131 struct tables *table;
00132 struct ast_str *sql = ast_str_thread_get(&findtable_buf, 330);
00133 char *pgerror;
00134 PGresult *result;
00135 char *fname, *ftype, *flen, *fnotnull, *fdef;
00136 int i, rows;
00137
00138 AST_LIST_LOCK(&psql_tables);
00139 AST_LIST_TRAVERSE(&psql_tables, table, list) {
00140 if (!strcasecmp(table->name, orig_tablename)) {
00141 ast_debug(1, "Found table in cache; now locking\n");
00142 ast_rwlock_rdlock(&table->lock);
00143 ast_debug(1, "Lock cached table; now returning\n");
00144 AST_LIST_UNLOCK(&psql_tables);
00145 return table;
00146 }
00147 }
00148
00149 ast_debug(1, "Table '%s' not found in cache, querying now\n", orig_tablename);
00150
00151
00152 if (has_schema_support) {
00153 char *schemaname, *tablename;
00154 if (strchr(orig_tablename, '.')) {
00155 schemaname = ast_strdupa(orig_tablename);
00156 tablename = strchr(schemaname, '.');
00157 *tablename++ = '\0';
00158 } else {
00159 schemaname = "";
00160 tablename = ast_strdupa(orig_tablename);
00161 }
00162
00163
00164 if (strchr(schemaname, '\\') || strchr(schemaname, '\'')) {
00165 char *tmp = schemaname, *ptr;
00166
00167 ptr = schemaname = alloca(strlen(tmp) * 2 + 1);
00168 for (; *tmp; tmp++) {
00169 if (strchr("\\'", *tmp)) {
00170 *ptr++ = *tmp;
00171 }
00172 *ptr++ = *tmp;
00173 }
00174 *ptr = '\0';
00175 }
00176
00177 if (strchr(tablename, '\\') || strchr(tablename, '\'')) {
00178 char *tmp = tablename, *ptr;
00179
00180 ptr = tablename = alloca(strlen(tmp) * 2 + 1);
00181 for (; *tmp; tmp++) {
00182 if (strchr("\\'", *tmp)) {
00183 *ptr++ = *tmp;
00184 }
00185 *ptr++ = *tmp;
00186 }
00187 *ptr = '\0';
00188 }
00189
00190 ast_str_set(&sql, 0, "SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc, a.atttypmod FROM (((pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace AND c.relname = '%s' AND n.nspname = %s%s%s) INNER JOIN pg_catalog.pg_attribute a ON (NOT a.attisdropped) AND a.attnum > 0 AND a.attrelid = c.oid) INNER JOIN pg_catalog.pg_type t ON t.oid = a.atttypid) LEFT OUTER JOIN pg_attrdef d ON a.atthasdef AND d.adrelid = a.attrelid AND d.adnum = a.attnum ORDER BY n.nspname, c.relname, attnum",
00191 tablename,
00192 ast_strlen_zero(schemaname) ? "" : "'", ast_strlen_zero(schemaname) ? "current_schema()" : schemaname, ast_strlen_zero(schemaname) ? "" : "'");
00193 } else {
00194
00195 if (strchr(orig_tablename, '\\') || strchr(orig_tablename, '\'')) {
00196 const char *tmp = orig_tablename;
00197 char *ptr;
00198
00199 orig_tablename = ptr = alloca(strlen(tmp) * 2 + 1);
00200 for (; *tmp; tmp++) {
00201 if (strchr("\\'", *tmp)) {
00202 *ptr++ = *tmp;
00203 }
00204 *ptr++ = *tmp;
00205 }
00206 *ptr = '\0';
00207 }
00208
00209 ast_str_set(&sql, 0, "SELECT a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc, a.atttypmod 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", orig_tablename);
00210 }
00211
00212 result = PQexec(pgsqlConn, ast_str_buffer(sql));
00213 ast_debug(1, "Query of table structure complete. Now retrieving results.\n");
00214 if (PQresultStatus(result) != PGRES_TUPLES_OK) {
00215 pgerror = PQresultErrorMessage(result);
00216 ast_log(LOG_ERROR, "Failed to query database columns: %s\n", pgerror);
00217 PQclear(result);
00218 AST_LIST_UNLOCK(&psql_tables);
00219 return NULL;
00220 }
00221
00222 if (!(table = ast_calloc(1, sizeof(*table) + strlen(orig_tablename) + 1))) {
00223 ast_log(LOG_ERROR, "Unable to allocate memory for new table structure\n");
00224 AST_LIST_UNLOCK(&psql_tables);
00225 return NULL;
00226 }
00227 strcpy(table->name, orig_tablename);
00228 ast_rwlock_init(&table->lock);
00229 AST_LIST_HEAD_INIT_NOLOCK(&table->columns);
00230
00231 rows = PQntuples(result);
00232 for (i = 0; i < rows; i++) {
00233 fname = PQgetvalue(result, i, 0);
00234 ftype = PQgetvalue(result, i, 1);
00235 flen = PQgetvalue(result, i, 2);
00236 fnotnull = PQgetvalue(result, i, 3);
00237 fdef = PQgetvalue(result, i, 4);
00238 ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
00239
00240 if (!(column = ast_calloc(1, sizeof(*column) + strlen(fname) + strlen(ftype) + 2))) {
00241 ast_log(LOG_ERROR, "Unable to allocate column element for %s, %s\n", orig_tablename, fname);
00242 destroy_table(table);
00243 AST_LIST_UNLOCK(&psql_tables);
00244 return NULL;
00245 }
00246
00247 if (strcmp(flen, "-1") == 0) {
00248
00249 flen = PQgetvalue(result, i, 5);
00250 sscanf(flen, "%30d", &column->len);
00251 column->len -= 4;
00252 } else {
00253 sscanf(flen, "%30d", &column->len);
00254 }
00255 column->name = (char *)column + sizeof(*column);
00256 column->type = (char *)column + sizeof(*column) + strlen(fname) + 1;
00257 strcpy(column->name, fname);
00258 strcpy(column->type, ftype);
00259 if (*fnotnull == 't') {
00260 column->notnull = 1;
00261 } else {
00262 column->notnull = 0;
00263 }
00264 if (!ast_strlen_zero(fdef)) {
00265 column->hasdefault = 1;
00266 } else {
00267 column->hasdefault = 0;
00268 }
00269 AST_LIST_INSERT_TAIL(&table->columns, column, list);
00270 }
00271 PQclear(result);
00272
00273 AST_LIST_INSERT_TAIL(&psql_tables, table, list);
00274 ast_rwlock_rdlock(&table->lock);
00275 AST_LIST_UNLOCK(&psql_tables);
00276 return table;
00277 }
00278
00279 #define release_table(table) ast_rwlock_unlock(&(table)->lock);
00280
00281 static struct columns *find_column(struct tables *t, const char *colname)
00282 {
00283 struct columns *column;
00284
00285
00286 AST_LIST_TRAVERSE(&t->columns, column, list) {
00287 if (strcmp(column->name, colname) == 0) {
00288 return column;
00289 }
00290 }
00291 return NULL;
00292 }
00293
00294 static char *decode_chunk(char *chunk)
00295 {
00296 char *orig = chunk;
00297 for (; *chunk; chunk++) {
00298 if (*chunk == '^' && strchr("0123456789ABCDEFabcdef", chunk[1]) && strchr("0123456789ABCDEFabcdef", chunk[2])) {
00299 sscanf(chunk + 1, "%02hhX", chunk);
00300 memmove(chunk + 1, chunk + 3, strlen(chunk + 3) + 1);
00301 }
00302 }
00303 return orig;
00304 }
00305
00306 static struct ast_variable *realtime_pgsql(const char *database, const char *tablename, va_list ap)
00307 {
00308 PGresult *result = NULL;
00309 int num_rows = 0, pgresult;
00310 struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
00311 struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 100);
00312 char *stringp;
00313 char *chunk;
00314 char *op;
00315 const char *newparam, *newval;
00316 struct ast_variable *var = NULL, *prev = NULL;
00317
00318 if (!tablename) {
00319 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00320 return NULL;
00321 }
00322
00323
00324 newparam = va_arg(ap, const char *);
00325 newval = va_arg(ap, const char *);
00326 if (!newparam || !newval) {
00327 ast_log(LOG_WARNING,
00328 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00329 if (pgsqlConn) {
00330 PQfinish(pgsqlConn);
00331 pgsqlConn = NULL;
00332 }
00333 return NULL;
00334 }
00335
00336
00337
00338 op = strchr(newparam, ' ') ? "" : " =";
00339
00340 ESCAPE_STRING(escapebuf, newval);
00341 if (pgresult) {
00342 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00343 va_end(ap);
00344 return NULL;
00345 }
00346
00347 ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", tablename, newparam, op, ast_str_buffer(escapebuf));
00348 while ((newparam = va_arg(ap, const char *))) {
00349 newval = va_arg(ap, const char *);
00350 if (!strchr(newparam, ' '))
00351 op = " =";
00352 else
00353 op = "";
00354
00355 ESCAPE_STRING(escapebuf, newval);
00356 if (pgresult) {
00357 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00358 va_end(ap);
00359 return NULL;
00360 }
00361
00362 ast_str_append(&sql, 0, " AND %s%s '%s'", newparam, op, ast_str_buffer(escapebuf));
00363 }
00364 va_end(ap);
00365
00366
00367 ast_mutex_lock(&pgsql_lock);
00368 if (!pgsql_reconnect(database)) {
00369 ast_mutex_unlock(&pgsql_lock);
00370 return NULL;
00371 }
00372
00373 if (!(result = PQexec(pgsqlConn, ast_str_buffer(sql)))) {
00374 ast_log(LOG_WARNING,
00375 "PostgreSQL RealTime: Failed to query '%s@%s'. Check debug for more info.\n", tablename, database);
00376 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00377 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00378 ast_mutex_unlock(&pgsql_lock);
00379 return NULL;
00380 } else {
00381 ExecStatusType result_status = PQresultStatus(result);
00382 if (result_status != PGRES_COMMAND_OK
00383 && result_status != PGRES_TUPLES_OK
00384 && result_status != PGRES_NONFATAL_ERROR) {
00385 ast_log(LOG_WARNING,
00386 "PostgreSQL RealTime: Failed to query '%s@%s'. Check debug for more info.\n", tablename, database);
00387 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00388 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00389 PQresultErrorMessage(result), PQresStatus(result_status));
00390 ast_mutex_unlock(&pgsql_lock);
00391 return NULL;
00392 }
00393 }
00394
00395 ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, ast_str_buffer(sql));
00396
00397 if ((num_rows = PQntuples(result)) > 0) {
00398 int i = 0;
00399 int rowIndex = 0;
00400 int numFields = PQnfields(result);
00401 char **fieldnames = NULL;
00402
00403 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
00404
00405 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
00406 ast_mutex_unlock(&pgsql_lock);
00407 PQclear(result);
00408 return NULL;
00409 }
00410 for (i = 0; i < numFields; i++)
00411 fieldnames[i] = PQfname(result, i);
00412 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00413 for (i = 0; i < numFields; i++) {
00414 stringp = PQgetvalue(result, rowIndex, i);
00415 while (stringp) {
00416 chunk = strsep(&stringp, ";");
00417 if (chunk && !ast_strlen_zero(decode_chunk(ast_strip(chunk)))) {
00418 if (prev) {
00419 prev->next = ast_variable_new(fieldnames[i], chunk, "");
00420 if (prev->next) {
00421 prev = prev->next;
00422 }
00423 } else {
00424 prev = var = ast_variable_new(fieldnames[i], chunk, "");
00425 }
00426 }
00427 }
00428 }
00429 }
00430 ast_free(fieldnames);
00431 } else {
00432 ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s@%s.\n", tablename, database);
00433 }
00434
00435 ast_mutex_unlock(&pgsql_lock);
00436 PQclear(result);
00437
00438 return var;
00439 }
00440
00441 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
00442 {
00443 PGresult *result = NULL;
00444 int num_rows = 0, pgresult;
00445 struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
00446 struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 100);
00447 const char *initfield = NULL;
00448 char *stringp;
00449 char *chunk;
00450 char *op;
00451 const char *newparam, *newval;
00452 struct ast_variable *var = NULL;
00453 struct ast_config *cfg = NULL;
00454 struct ast_category *cat = NULL;
00455
00456 if (!table) {
00457 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00458 return NULL;
00459 }
00460
00461 if (!(cfg = ast_config_new()))
00462 return NULL;
00463
00464
00465 newparam = va_arg(ap, const char *);
00466 newval = va_arg(ap, const char *);
00467 if (!newparam || !newval) {
00468 ast_log(LOG_WARNING,
00469 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00470 if (pgsqlConn) {
00471 PQfinish(pgsqlConn);
00472 pgsqlConn = NULL;
00473 }
00474 return NULL;
00475 }
00476
00477 initfield = ast_strdupa(newparam);
00478 if ((op = strchr(initfield, ' '))) {
00479 *op = '\0';
00480 }
00481
00482
00483
00484
00485 if (!strchr(newparam, ' '))
00486 op = " =";
00487 else
00488 op = "";
00489
00490 ESCAPE_STRING(escapebuf, newval);
00491 if (pgresult) {
00492 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00493 va_end(ap);
00494 return NULL;
00495 }
00496
00497 ast_str_set(&sql, 0, "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op, ast_str_buffer(escapebuf));
00498 while ((newparam = va_arg(ap, const char *))) {
00499 newval = va_arg(ap, const char *);
00500 if (!strchr(newparam, ' '))
00501 op = " =";
00502 else
00503 op = "";
00504
00505 ESCAPE_STRING(escapebuf, newval);
00506 if (pgresult) {
00507 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00508 va_end(ap);
00509 return NULL;
00510 }
00511
00512 ast_str_append(&sql, 0, " AND %s%s '%s'", newparam, op, ast_str_buffer(escapebuf));
00513 }
00514
00515 if (initfield) {
00516 ast_str_append(&sql, 0, " ORDER BY %s", initfield);
00517 }
00518
00519 va_end(ap);
00520
00521
00522 ast_mutex_lock(&pgsql_lock);
00523 if (!pgsql_reconnect(database)) {
00524 ast_mutex_unlock(&pgsql_lock);
00525 return NULL;
00526 }
00527
00528 if (!(result = PQexec(pgsqlConn, ast_str_buffer(sql)))) {
00529 ast_log(LOG_WARNING,
00530 "PostgreSQL RealTime: Failed to query %s@%s. Check debug for more info.\n", table, database);
00531 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00532 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00533 ast_mutex_unlock(&pgsql_lock);
00534 return NULL;
00535 } else {
00536 ExecStatusType result_status = PQresultStatus(result);
00537 if (result_status != PGRES_COMMAND_OK
00538 && result_status != PGRES_TUPLES_OK
00539 && result_status != PGRES_NONFATAL_ERROR) {
00540 ast_log(LOG_WARNING,
00541 "PostgreSQL RealTime: Failed to query %s@%s. Check debug for more info.\n", table, database);
00542 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00543 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00544 PQresultErrorMessage(result), PQresStatus(result_status));
00545 ast_mutex_unlock(&pgsql_lock);
00546 return NULL;
00547 }
00548 }
00549
00550 ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, ast_str_buffer(sql));
00551
00552 if ((num_rows = PQntuples(result)) > 0) {
00553 int numFields = PQnfields(result);
00554 int i = 0;
00555 int rowIndex = 0;
00556 char **fieldnames = NULL;
00557
00558 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
00559
00560 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
00561 ast_mutex_unlock(&pgsql_lock);
00562 PQclear(result);
00563 return NULL;
00564 }
00565 for (i = 0; i < numFields; i++)
00566 fieldnames[i] = PQfname(result, i);
00567
00568 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00569 var = NULL;
00570 if (!(cat = ast_category_new("","",99999)))
00571 continue;
00572 for (i = 0; i < numFields; i++) {
00573 stringp = PQgetvalue(result, rowIndex, i);
00574 while (stringp) {
00575 chunk = strsep(&stringp, ";");
00576 if (chunk && !ast_strlen_zero(decode_chunk(ast_strip(chunk)))) {
00577 if (initfield && !strcmp(initfield, fieldnames[i])) {
00578 ast_category_rename(cat, chunk);
00579 }
00580 var = ast_variable_new(fieldnames[i], chunk, "");
00581 ast_variable_append(cat, var);
00582 }
00583 }
00584 }
00585 ast_category_append(cfg, cat);
00586 }
00587 ast_free(fieldnames);
00588 } else {
00589 ast_debug(1, "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
00590 }
00591
00592 ast_mutex_unlock(&pgsql_lock);
00593 PQclear(result);
00594
00595 return cfg;
00596 }
00597
00598 static int update_pgsql(const char *database, const char *tablename, const char *keyfield,
00599 const char *lookup, va_list ap)
00600 {
00601 PGresult *result = NULL;
00602 int numrows = 0, pgresult;
00603 const char *newparam, *newval;
00604 struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
00605 struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 100);
00606 struct tables *table;
00607 struct columns *column = NULL;
00608
00609 if (!tablename) {
00610 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00611 return -1;
00612 }
00613
00614 if (!(table = find_table(tablename))) {
00615 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
00616 return -1;
00617 }
00618
00619
00620 newparam = va_arg(ap, const char *);
00621 newval = va_arg(ap, const char *);
00622 if (!newparam || !newval) {
00623 ast_log(LOG_WARNING,
00624 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00625 if (pgsqlConn) {
00626 PQfinish(pgsqlConn);
00627 pgsqlConn = NULL;
00628 }
00629 release_table(table);
00630 return -1;
00631 }
00632
00633
00634 AST_LIST_TRAVERSE(&table->columns, column, list) {
00635 if (strcmp(column->name, newparam) == 0) {
00636 break;
00637 }
00638 }
00639
00640 if (!column) {
00641 ast_log(LOG_ERROR, "PostgreSQL RealTime: Updating on column '%s', but that column does not exist within the table '%s'!\n", newparam, tablename);
00642 release_table(table);
00643 return -1;
00644 }
00645
00646
00647
00648
00649 ESCAPE_STRING(escapebuf, newval);
00650 if (pgresult) {
00651 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00652 va_end(ap);
00653 release_table(table);
00654 return -1;
00655 }
00656 ast_str_set(&sql, 0, "UPDATE %s SET %s = '%s'", tablename, newparam, ast_str_buffer(escapebuf));
00657
00658 while ((newparam = va_arg(ap, const char *))) {
00659 newval = va_arg(ap, const char *);
00660
00661 if (!find_column(table, newparam)) {
00662 ast_log(LOG_NOTICE, "Attempted to update column '%s' in table '%s', but column does not exist!\n", newparam, tablename);
00663 continue;
00664 }
00665
00666 ESCAPE_STRING(escapebuf, newval);
00667 if (pgresult) {
00668 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00669 va_end(ap);
00670 release_table(table);
00671 return -1;
00672 }
00673
00674 ast_str_append(&sql, 0, ", %s = '%s'", newparam, ast_str_buffer(escapebuf));
00675 }
00676 va_end(ap);
00677 release_table(table);
00678
00679 ESCAPE_STRING(escapebuf, lookup);
00680 if (pgresult) {
00681 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
00682 va_end(ap);
00683 return -1;
00684 }
00685
00686 ast_str_append(&sql, 0, " WHERE %s = '%s'", keyfield, ast_str_buffer(escapebuf));
00687
00688 ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
00689
00690
00691 ast_mutex_lock(&pgsql_lock);
00692 if (!pgsql_reconnect(database)) {
00693 ast_mutex_unlock(&pgsql_lock);
00694 return -1;
00695 }
00696
00697 if (!(result = PQexec(pgsqlConn, ast_str_buffer(sql)))) {
00698 ast_log(LOG_WARNING,
00699 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00700 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00701 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00702 ast_mutex_unlock(&pgsql_lock);
00703 return -1;
00704 } else {
00705 ExecStatusType result_status = PQresultStatus(result);
00706 if (result_status != PGRES_COMMAND_OK
00707 && result_status != PGRES_TUPLES_OK
00708 && result_status != PGRES_NONFATAL_ERROR) {
00709 ast_log(LOG_WARNING,
00710 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00711 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00712 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00713 PQresultErrorMessage(result), PQresStatus(result_status));
00714 ast_mutex_unlock(&pgsql_lock);
00715 return -1;
00716 }
00717 }
00718
00719 numrows = atoi(PQcmdTuples(result));
00720 ast_mutex_unlock(&pgsql_lock);
00721
00722 ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
00723
00724
00725
00726
00727
00728
00729
00730 if (numrows >= 0)
00731 return (int) numrows;
00732
00733 return -1;
00734 }
00735
00736 static int update2_pgsql(const char *database, const char *tablename, va_list ap)
00737 {
00738 PGresult *result = NULL;
00739 int numrows = 0, pgresult, first = 1;
00740 struct ast_str *escapebuf = ast_str_thread_get(&escapebuf_buf, 16);
00741 const char *newparam, *newval;
00742 struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
00743 struct ast_str *where = ast_str_thread_get(&where_buf, 100);
00744 struct tables *table;
00745
00746 if (!tablename) {
00747 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00748 return -1;
00749 }
00750
00751 if (!escapebuf || !sql || !where) {
00752
00753 return -1;
00754 }
00755
00756 if (!(table = find_table(tablename))) {
00757 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
00758 return -1;
00759 }
00760
00761 ast_str_set(&sql, 0, "UPDATE %s SET ", tablename);
00762 ast_str_set(&where, 0, "WHERE");
00763
00764 while ((newparam = va_arg(ap, const char *))) {
00765 if (!find_column(table, newparam)) {
00766 ast_log(LOG_ERROR, "Attempted to update based on criteria column '%s' (%s@%s), but that column does not exist!\n", newparam, tablename, database);
00767 release_table(table);
00768 return -1;
00769 }
00770
00771 newval = va_arg(ap, const char *);
00772 ESCAPE_STRING(escapebuf, newval);
00773 if (pgresult) {
00774 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00775 release_table(table);
00776 ast_free(sql);
00777 return -1;
00778 }
00779 ast_str_append(&where, 0, "%s %s='%s'", first ? "" : " AND", newparam, ast_str_buffer(escapebuf));
00780 first = 0;
00781 }
00782
00783 if (first) {
00784 ast_log(LOG_WARNING,
00785 "PostgreSQL RealTime: Realtime update requires at least 1 parameter and 1 value to search on.\n");
00786 if (pgsqlConn) {
00787 PQfinish(pgsqlConn);
00788 pgsqlConn = NULL;
00789 }
00790 release_table(table);
00791 return -1;
00792 }
00793
00794
00795 first = 1;
00796 while ((newparam = va_arg(ap, const char *))) {
00797 newval = va_arg(ap, const char *);
00798
00799
00800 if (!find_column(table, newparam)) {
00801 ast_log(LOG_NOTICE, "Attempted to update column '%s' in table '%s@%s', but column does not exist!\n", newparam, tablename, database);
00802 continue;
00803 }
00804
00805 ESCAPE_STRING(escapebuf, newval);
00806 if (pgresult) {
00807 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00808 release_table(table);
00809 ast_free(sql);
00810 return -1;
00811 }
00812
00813 ast_str_append(&sql, 0, "%s %s='%s'", first ? "" : ",", newparam, ast_str_buffer(escapebuf));
00814 }
00815 release_table(table);
00816
00817 ast_str_append(&sql, 0, " %s", ast_str_buffer(where));
00818
00819 ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", ast_str_buffer(sql));
00820
00821
00822 ast_mutex_lock(&pgsql_lock);
00823 if (!pgsql_reconnect(database)) {
00824 ast_mutex_unlock(&pgsql_lock);
00825 return -1;
00826 }
00827
00828 if (!(result = PQexec(pgsqlConn, ast_str_buffer(sql)))) {
00829 ast_log(LOG_WARNING,
00830 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00831 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00832 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00833 ast_mutex_unlock(&pgsql_lock);
00834 return -1;
00835 } else {
00836 ExecStatusType result_status = PQresultStatus(result);
00837 if (result_status != PGRES_COMMAND_OK
00838 && result_status != PGRES_TUPLES_OK
00839 && result_status != PGRES_NONFATAL_ERROR) {
00840 ast_log(LOG_WARNING,
00841 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00842 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
00843 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00844 PQresultErrorMessage(result), PQresStatus(result_status));
00845 ast_mutex_unlock(&pgsql_lock);
00846 return -1;
00847 }
00848 }
00849
00850 numrows = atoi(PQcmdTuples(result));
00851 ast_mutex_unlock(&pgsql_lock);
00852
00853 ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
00854
00855
00856
00857
00858
00859
00860
00861 if (numrows >= 0) {
00862 return (int) numrows;
00863 }
00864
00865 return -1;
00866 }
00867
00868 static int store_pgsql(const char *database, const char *table, va_list ap)
00869 {
00870 PGresult *result = NULL;
00871 Oid insertid;
00872 struct ast_str *buf = ast_str_thread_get(&escapebuf_buf, 256);
00873 struct ast_str *sql1 = ast_str_thread_get(&sql_buf, 256);
00874 struct ast_str *sql2 = ast_str_thread_get(&where_buf, 256);
00875 int pgresult;
00876 const char *newparam, *newval;
00877
00878 if (!table) {
00879 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00880 return -1;
00881 }
00882
00883
00884 newparam = va_arg(ap, const char *);
00885 newval = va_arg(ap, const char *);
00886 if (!newparam || !newval) {
00887 ast_log(LOG_WARNING,
00888 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
00889 if (pgsqlConn) {
00890 PQfinish(pgsqlConn);
00891 pgsqlConn = NULL;
00892 }
00893 return -1;
00894 }
00895
00896
00897 ast_mutex_lock(&pgsql_lock);
00898 if (!pgsql_reconnect(database)) {
00899 ast_mutex_unlock(&pgsql_lock);
00900 return -1;
00901 }
00902
00903
00904
00905 ESCAPE_STRING(buf, newparam);
00906 ast_str_set(&sql1, 0, "INSERT INTO %s (%s", table, ast_str_buffer(buf));
00907 ESCAPE_STRING(buf, newval);
00908 ast_str_set(&sql2, 0, ") VALUES ('%s'", ast_str_buffer(buf));
00909 while ((newparam = va_arg(ap, const char *))) {
00910 newval = va_arg(ap, const char *);
00911 ESCAPE_STRING(buf, newparam);
00912 ast_str_append(&sql1, 0, ", %s", ast_str_buffer(buf));
00913 ESCAPE_STRING(buf, newval);
00914 ast_str_append(&sql2, 0, ", '%s'", ast_str_buffer(buf));
00915 }
00916 va_end(ap);
00917 ast_str_append(&sql1, 0, "%s)", ast_str_buffer(sql2));
00918
00919 ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", ast_str_buffer(sql1));
00920
00921 if (!(result = PQexec(pgsqlConn, ast_str_buffer(sql1)))) {
00922 ast_log(LOG_WARNING,
00923 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00924 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql1));
00925 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00926 ast_mutex_unlock(&pgsql_lock);
00927 return -1;
00928 } else {
00929 ExecStatusType result_status = PQresultStatus(result);
00930 if (result_status != PGRES_COMMAND_OK
00931 && result_status != PGRES_TUPLES_OK
00932 && result_status != PGRES_NONFATAL_ERROR) {
00933 ast_log(LOG_WARNING,
00934 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00935 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql1));
00936 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00937 PQresultErrorMessage(result), PQresStatus(result_status));
00938 ast_mutex_unlock(&pgsql_lock);
00939 return -1;
00940 }
00941 }
00942
00943 insertid = PQoidValue(result);
00944 ast_mutex_unlock(&pgsql_lock);
00945
00946 ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
00947
00948
00949
00950
00951
00952
00953
00954 if (insertid >= 0)
00955 return (int) insertid;
00956
00957 return -1;
00958 }
00959
00960 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00961 {
00962 PGresult *result = NULL;
00963 int numrows = 0;
00964 int pgresult;
00965 struct ast_str *sql = ast_str_thread_get(&sql_buf, 256);
00966 struct ast_str *buf1 = ast_str_thread_get(&where_buf, 60), *buf2 = ast_str_thread_get(&escapebuf_buf, 60);
00967 const char *newparam, *newval;
00968
00969 if (!table) {
00970 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00971 return -1;
00972 }
00973
00974
00975
00976
00977
00978 if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup)) {
00979 ast_log(LOG_WARNING,
00980 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
00981 if (pgsqlConn) {
00982 PQfinish(pgsqlConn);
00983 pgsqlConn = NULL;
00984 };
00985 return -1;
00986 }
00987
00988
00989 ast_mutex_lock(&pgsql_lock);
00990 if (!pgsql_reconnect(database)) {
00991 ast_mutex_unlock(&pgsql_lock);
00992 return -1;
00993 }
00994
00995
00996
00997
00998
00999 ESCAPE_STRING(buf1, keyfield);
01000 ESCAPE_STRING(buf2, lookup);
01001 ast_str_set(&sql, 0, "DELETE FROM %s WHERE %s = '%s'", table, ast_str_buffer(buf1), ast_str_buffer(buf2));
01002 while ((newparam = va_arg(ap, const char *))) {
01003 newval = va_arg(ap, const char *);
01004 ESCAPE_STRING(buf1, newparam);
01005 ESCAPE_STRING(buf2, newval);
01006 ast_str_append(&sql, 0, " AND %s = '%s'", ast_str_buffer(buf1), ast_str_buffer(buf2));
01007 }
01008 va_end(ap);
01009
01010 ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", ast_str_buffer(sql));
01011
01012 if (!(result = PQexec(pgsqlConn, ast_str_buffer(sql)))) {
01013 ast_log(LOG_WARNING,
01014 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
01015 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
01016 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
01017 ast_mutex_unlock(&pgsql_lock);
01018 return -1;
01019 } else {
01020 ExecStatusType result_status = PQresultStatus(result);
01021 if (result_status != PGRES_COMMAND_OK
01022 && result_status != PGRES_TUPLES_OK
01023 && result_status != PGRES_NONFATAL_ERROR) {
01024 ast_log(LOG_WARNING,
01025 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
01026 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
01027 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
01028 PQresultErrorMessage(result), PQresStatus(result_status));
01029 ast_mutex_unlock(&pgsql_lock);
01030 return -1;
01031 }
01032 }
01033
01034 numrows = atoi(PQcmdTuples(result));
01035 ast_mutex_unlock(&pgsql_lock);
01036
01037 ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
01038
01039
01040
01041
01042
01043
01044
01045 if (numrows >= 0)
01046 return (int) numrows;
01047
01048 return -1;
01049 }
01050
01051
01052 static struct ast_config *config_pgsql(const char *database, const char *table,
01053 const char *file, struct ast_config *cfg,
01054 struct ast_flags flags, const char *suggested_incl, const char *who_asked)
01055 {
01056 PGresult *result = NULL;
01057 long num_rows;
01058 struct ast_variable *new_v;
01059 struct ast_category *cur_cat = NULL;
01060 struct ast_str *sql = ast_str_thread_get(&sql_buf, 100);
01061 char last[80] = "";
01062 int last_cat_metric = 0;
01063
01064 last[0] = '\0';
01065
01066 if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
01067 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
01068 return NULL;
01069 }
01070
01071 ast_str_set(&sql, 0, "SELECT category, var_name, var_val, cat_metric FROM %s "
01072 "WHERE filename='%s' and commented=0"
01073 "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ", table, file);
01074
01075 ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", ast_str_buffer(sql));
01076
01077
01078 ast_mutex_lock(&pgsql_lock);
01079 if (!pgsql_reconnect(database)) {
01080 ast_mutex_unlock(&pgsql_lock);
01081 return NULL;
01082 }
01083
01084 if (!(result = PQexec(pgsqlConn, ast_str_buffer(sql)))) {
01085 ast_log(LOG_WARNING,
01086 "PostgreSQL RealTime: Failed to query '%s@%s'. Check debug for more info.\n", table, database);
01087 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
01088 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
01089 ast_mutex_unlock(&pgsql_lock);
01090 return NULL;
01091 } else {
01092 ExecStatusType result_status = PQresultStatus(result);
01093 if (result_status != PGRES_COMMAND_OK
01094 && result_status != PGRES_TUPLES_OK
01095 && result_status != PGRES_NONFATAL_ERROR) {
01096 ast_log(LOG_WARNING,
01097 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
01098 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", ast_str_buffer(sql));
01099 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
01100 PQresultErrorMessage(result), PQresStatus(result_status));
01101 ast_mutex_unlock(&pgsql_lock);
01102 return NULL;
01103 }
01104 }
01105
01106 if ((num_rows = PQntuples(result)) > 0) {
01107 int rowIndex = 0;
01108
01109 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
01110
01111 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
01112 char *field_category = PQgetvalue(result, rowIndex, 0);
01113 char *field_var_name = PQgetvalue(result, rowIndex, 1);
01114 char *field_var_val = PQgetvalue(result, rowIndex, 2);
01115 char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
01116 if (!strcmp(field_var_name, "#include")) {
01117 if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
01118 PQclear(result);
01119 ast_mutex_unlock(&pgsql_lock);
01120 return NULL;
01121 }
01122 continue;
01123 }
01124
01125 if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
01126 cur_cat = ast_category_new(field_category, "", 99999);
01127 if (!cur_cat)
01128 break;
01129 strcpy(last, field_category);
01130 last_cat_metric = atoi(field_cat_metric);
01131 ast_category_append(cfg, cur_cat);
01132 }
01133 new_v = ast_variable_new(field_var_name, field_var_val, "");
01134 ast_variable_append(cur_cat, new_v);
01135 }
01136 } else {
01137 ast_log(LOG_WARNING,
01138 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
01139 }
01140
01141 PQclear(result);
01142 ast_mutex_unlock(&pgsql_lock);
01143
01144 return cfg;
01145 }
01146
01147 static int require_pgsql(const char *database, const char *tablename, va_list ap)
01148 {
01149 struct columns *column;
01150 struct tables *table = find_table(tablename);
01151 char *elm;
01152 int type, size, res = 0;
01153
01154 if (!table) {
01155 ast_log(LOG_WARNING, "Table %s not found in database. This table should exist if you're using realtime.\n", tablename);
01156 return -1;
01157 }
01158
01159 while ((elm = va_arg(ap, char *))) {
01160 type = va_arg(ap, require_type);
01161 size = va_arg(ap, int);
01162 AST_LIST_TRAVERSE(&table->columns, column, list) {
01163 if (strcmp(column->name, elm) == 0) {
01164
01165 if ((strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0 || strcmp(column->type, "bpchar") == 0)) {
01166 if ((size > column->len) && column->len != -1) {
01167 ast_log(LOG_WARNING, "Column '%s' should be at least %d long, but is only %d long.\n", column->name, size, column->len);
01168 res = -1;
01169 }
01170 } else if (strncmp(column->type, "int", 3) == 0) {
01171 int typesize = atoi(column->type + 3);
01172
01173 if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
01174 type == RQ_INTEGER4 || type == RQ_UINTEGER4 ||
01175 type == RQ_INTEGER3 || type == RQ_UINTEGER3 ||
01176 type == RQ_UINTEGER2) && typesize == 2) {
01177 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
01178 res = -1;
01179 } else if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
01180 type == RQ_UINTEGER4) && typesize == 4) {
01181 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
01182 res = -1;
01183 } else if (type == RQ_CHAR || type == RQ_DATETIME || type == RQ_FLOAT || type == RQ_DATE) {
01184 ast_log(LOG_WARNING, "Column '%s' is of the incorrect type: (need %s(%d) but saw %s)\n",
01185 column->name,
01186 type == RQ_CHAR ? "char" :
01187 type == RQ_DATETIME ? "datetime" :
01188 type == RQ_DATE ? "date" :
01189 type == RQ_FLOAT ? "float" :
01190 "a rather stiff drink ",
01191 size, column->type);
01192 res = -1;
01193 }
01194 } else if (strncmp(column->type, "float", 5) == 0) {
01195 if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
01196 ast_log(LOG_WARNING, "Column %s cannot be a %s\n", column->name, column->type);
01197 res = -1;
01198 }
01199 } else if (strncmp(column->type, "timestamp", 9) == 0) {
01200 if (type != RQ_DATETIME && type != RQ_DATE) {
01201 ast_log(LOG_WARNING, "Column %s cannot be a %s\n", column->name, column->type);
01202 res = -1;
01203 }
01204 } else {
01205 ast_log(LOG_WARNING, "Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
01206 res = -1;
01207 }
01208 break;
01209 }
01210 }
01211
01212 if (!column) {
01213 if (requirements == RQ_WARN) {
01214 ast_log(LOG_WARNING, "Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
01215 } else {
01216 struct ast_str *sql = ast_str_create(100);
01217 char fieldtype[15];
01218 PGresult *result;
01219
01220 if (requirements == RQ_CREATECHAR || type == RQ_CHAR) {
01221
01222
01223
01224 snprintf(fieldtype, sizeof(fieldtype), "CHAR(%d)",
01225 size < 15 ? size * 2 :
01226 (size * 3 / 2 > 255) ? 255 : size * 3 / 2);
01227 } else if (type == RQ_INTEGER1 || type == RQ_UINTEGER1 || type == RQ_INTEGER2) {
01228 snprintf(fieldtype, sizeof(fieldtype), "INT2");
01229 } else if (type == RQ_UINTEGER2 || type == RQ_INTEGER3 || type == RQ_UINTEGER3 || type == RQ_INTEGER4) {
01230 snprintf(fieldtype, sizeof(fieldtype), "INT4");
01231 } else if (type == RQ_UINTEGER4 || type == RQ_INTEGER8) {
01232 snprintf(fieldtype, sizeof(fieldtype), "INT8");
01233 } else if (type == RQ_UINTEGER8) {
01234
01235 snprintf(fieldtype, sizeof(fieldtype), "CHAR(20)");
01236 } else if (type == RQ_FLOAT) {
01237 snprintf(fieldtype, sizeof(fieldtype), "FLOAT8");
01238 } else if (type == RQ_DATE) {
01239 snprintf(fieldtype, sizeof(fieldtype), "DATE");
01240 } else if (type == RQ_DATETIME) {
01241 snprintf(fieldtype, sizeof(fieldtype), "TIMESTAMP");
01242 } else {
01243 ast_log(LOG_ERROR, "Unrecognized request type %d\n", type);
01244 ast_free(sql);
01245 continue;
01246 }
01247 ast_str_set(&sql, 0, "ALTER TABLE %s ADD COLUMN %s %s", tablename, elm, fieldtype);
01248 ast_debug(1, "About to lock pgsql_lock (running alter on table '%s' to add column '%s')\n", tablename, elm);
01249
01250 ast_mutex_lock(&pgsql_lock);
01251 if (!pgsql_reconnect(database)) {
01252 ast_mutex_unlock(&pgsql_lock);
01253 ast_log(LOG_ERROR, "Unable to add column: %s\n", ast_str_buffer(sql));
01254 ast_free(sql);
01255 continue;
01256 }
01257
01258 ast_debug(1, "About to run ALTER query on table '%s' to add column '%s'\n", tablename, elm);
01259 result = PQexec(pgsqlConn, ast_str_buffer(sql));
01260 ast_debug(1, "Finished running ALTER query on table '%s'\n", tablename);
01261 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
01262 ast_log(LOG_ERROR, "Unable to add column: %s\n", ast_str_buffer(sql));
01263 }
01264 PQclear(result);
01265 ast_mutex_unlock(&pgsql_lock);
01266
01267 ast_free(sql);
01268 }
01269 }
01270 }
01271 release_table(table);
01272 return res;
01273 }
01274
01275 static int unload_pgsql(const char *database, const char *tablename)
01276 {
01277 struct tables *cur;
01278 ast_debug(2, "About to lock table cache list\n");
01279 AST_LIST_LOCK(&psql_tables);
01280 ast_debug(2, "About to traverse table cache list\n");
01281 AST_LIST_TRAVERSE_SAFE_BEGIN(&psql_tables, cur, list) {
01282 if (strcmp(cur->name, tablename) == 0) {
01283 ast_debug(2, "About to remove matching cache entry\n");
01284 AST_LIST_REMOVE_CURRENT(list);
01285 ast_debug(2, "About to destroy matching cache entry\n");
01286 destroy_table(cur);
01287 ast_debug(1, "Cache entry '%s@%s' destroyed\n", tablename, database);
01288 break;
01289 }
01290 }
01291 AST_LIST_TRAVERSE_SAFE_END
01292 AST_LIST_UNLOCK(&psql_tables);
01293 ast_debug(2, "About to return\n");
01294 return cur ? 0 : -1;
01295 }
01296
01297 static struct ast_config_engine pgsql_engine = {
01298 .name = "pgsql",
01299 .load_func = config_pgsql,
01300 .realtime_func = realtime_pgsql,
01301 .realtime_multi_func = realtime_multi_pgsql,
01302 .store_func = store_pgsql,
01303 .destroy_func = destroy_pgsql,
01304 .update_func = update_pgsql,
01305 .update2_func = update2_pgsql,
01306 .require_func = require_pgsql,
01307 .unload_func = unload_pgsql,
01308 };
01309
01310 static int load_module(void)
01311 {
01312 if(!parse_config(0))
01313 return AST_MODULE_LOAD_DECLINE;
01314
01315 ast_config_engine_register(&pgsql_engine);
01316 ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
01317 ast_cli_register_multiple(cli_realtime, ARRAY_LEN(cli_realtime));
01318
01319 return 0;
01320 }
01321
01322 static int unload_module(void)
01323 {
01324 struct tables *table;
01325
01326 ast_mutex_lock(&pgsql_lock);
01327
01328 if (pgsqlConn) {
01329 PQfinish(pgsqlConn);
01330 pgsqlConn = NULL;
01331 }
01332 ast_cli_unregister_multiple(cli_realtime, ARRAY_LEN(cli_realtime));
01333 ast_config_engine_deregister(&pgsql_engine);
01334 ast_verb(1, "PostgreSQL RealTime unloaded.\n");
01335
01336
01337 AST_LIST_LOCK(&psql_tables);
01338 while ((table = AST_LIST_REMOVE_HEAD(&psql_tables, list))) {
01339 destroy_table(table);
01340 }
01341 AST_LIST_UNLOCK(&psql_tables);
01342
01343
01344 ast_mutex_unlock(&pgsql_lock);
01345
01346 return 0;
01347 }
01348
01349 static int reload(void)
01350 {
01351 parse_config(1);
01352
01353 return 0;
01354 }
01355
01356 static int parse_config(int is_reload)
01357 {
01358 struct ast_config *config;
01359 const char *s;
01360 struct ast_flags config_flags = { is_reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
01361
01362 config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags);
01363 if (config == CONFIG_STATUS_FILEUNCHANGED) {
01364 return 0;
01365 }
01366
01367 if (config == CONFIG_STATUS_FILEMISSING || config == CONFIG_STATUS_FILEINVALID) {
01368 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
01369 return 0;
01370 }
01371
01372 ast_mutex_lock(&pgsql_lock);
01373
01374 if (pgsqlConn) {
01375 PQfinish(pgsqlConn);
01376 pgsqlConn = NULL;
01377 }
01378
01379 if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
01380 ast_log(LOG_WARNING,
01381 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
01382 strcpy(dbuser, "asterisk");
01383 } else {
01384 ast_copy_string(dbuser, s, sizeof(dbuser));
01385 }
01386
01387 if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
01388 ast_log(LOG_WARNING,
01389 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
01390 strcpy(dbpass, "asterisk");
01391 } else {
01392 ast_copy_string(dbpass, s, sizeof(dbpass));
01393 }
01394
01395 if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
01396 ast_log(LOG_WARNING,
01397 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
01398 dbhost[0] = '\0';
01399 } else {
01400 ast_copy_string(dbhost, s, sizeof(dbhost));
01401 }
01402
01403 if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
01404 ast_log(LOG_WARNING,
01405 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
01406 strcpy(dbname, "asterisk");
01407 } else {
01408 ast_copy_string(dbname, s, sizeof(dbname));
01409 }
01410
01411 if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
01412 ast_log(LOG_WARNING,
01413 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
01414 dbport = 5432;
01415 } else {
01416 dbport = atoi(s);
01417 }
01418
01419 if (!ast_strlen_zero(dbhost)) {
01420
01421 } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
01422 ast_log(LOG_WARNING,
01423 "PostgreSQL RealTime: No database socket found, using '/tmp/.s.PGSQL.%d' as default.\n", dbport);
01424 strcpy(dbsock, "/tmp");
01425 } else {
01426 ast_copy_string(dbsock, s, sizeof(dbsock));
01427 }
01428
01429 if (!(s = ast_variable_retrieve(config, "general", "requirements"))) {
01430 ast_log(LOG_WARNING,
01431 "PostgreSQL RealTime: no requirements setting found, using 'warn' as default.\n");
01432 requirements = RQ_WARN;
01433 } else if (!strcasecmp(s, "createclose")) {
01434 requirements = RQ_CREATECLOSE;
01435 } else if (!strcasecmp(s, "createchar")) {
01436 requirements = RQ_CREATECHAR;
01437 }
01438
01439 ast_config_destroy(config);
01440
01441 if (option_debug) {
01442 if (!ast_strlen_zero(dbhost)) {
01443 ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
01444 ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
01445 } else {
01446 ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
01447 }
01448 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
01449 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
01450 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
01451 }
01452
01453 if (!pgsql_reconnect(NULL)) {
01454 ast_log(LOG_WARNING,
01455 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
01456 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
01457 }
01458
01459 ast_verb(2, "PostgreSQL RealTime reloaded.\n");
01460
01461
01462 ast_mutex_unlock(&pgsql_lock);
01463
01464 return 1;
01465 }
01466
01467 static int pgsql_reconnect(const char *database)
01468 {
01469 char my_database[50];
01470
01471 ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
01472
01473
01474
01475 if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
01476 PQfinish(pgsqlConn);
01477 pgsqlConn = NULL;
01478 }
01479
01480
01481 if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
01482 struct ast_str *connInfo = ast_str_create(32);
01483
01484 ast_str_set(&connInfo, 0, "host=%s port=%d dbname=%s user=%s",
01485 S_OR(dbhost, dbsock), dbport, my_database, dbuser);
01486 if (!ast_strlen_zero(dbpass))
01487 ast_str_append(&connInfo, 0, " password=%s", dbpass);
01488
01489 ast_debug(1, "%u connInfo=%s\n", (unsigned int)ast_str_size(connInfo), ast_str_buffer(connInfo));
01490 pgsqlConn = PQconnectdb(ast_str_buffer(connInfo));
01491 ast_debug(1, "%u connInfo=%s\n", (unsigned int)ast_str_size(connInfo), ast_str_buffer(connInfo));
01492 ast_free(connInfo);
01493 connInfo = NULL;
01494
01495 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
01496 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
01497 ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
01498 connect_time = time(NULL);
01499 version = PQserverVersion(pgsqlConn);
01500 return 1;
01501 } else {
01502 ast_log(LOG_ERROR,
01503 "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
01504 dbname, dbhost, PQresultErrorMessage(NULL));
01505 return 0;
01506 }
01507 } else {
01508 ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
01509 return 1;
01510 }
01511 }
01512
01513 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
01514 {
01515 struct tables *cur;
01516 int l, which;
01517 char *ret = NULL;
01518
01519 switch (cmd) {
01520 case CLI_INIT:
01521 e->command = "realtime show pgsql cache";
01522 e->usage =
01523 "Usage: realtime show pgsql cache [<table>]\n"
01524 " Shows table cache for the PostgreSQL RealTime driver\n";
01525 return NULL;
01526 case CLI_GENERATE:
01527 if (a->argc != 4) {
01528 return NULL;
01529 }
01530 l = strlen(a->word);
01531 which = 0;
01532 AST_LIST_LOCK(&psql_tables);
01533 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
01534 if (!strncasecmp(a->word, cur->name, l) && ++which > a->n) {
01535 ret = ast_strdup(cur->name);
01536 break;
01537 }
01538 }
01539 AST_LIST_UNLOCK(&psql_tables);
01540 return ret;
01541 }
01542
01543 if (a->argc == 4) {
01544
01545 AST_LIST_LOCK(&psql_tables);
01546 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
01547 ast_cli(a->fd, "%s\n", cur->name);
01548 }
01549 AST_LIST_UNLOCK(&psql_tables);
01550 } else if (a->argc == 5) {
01551
01552 if ((cur = find_table(a->argv[4]))) {
01553 struct columns *col;
01554 ast_cli(a->fd, "Columns for Table Cache '%s':\n", a->argv[4]);
01555 ast_cli(a->fd, "%-20.20s %-20.20s %-3.3s %-8.8s\n", "Name", "Type", "Len", "Nullable");
01556 AST_LIST_TRAVERSE(&cur->columns, col, list) {
01557 ast_cli(a->fd, "%-20.20s %-20.20s %3d %-8.8s\n", col->name, col->type, col->len, col->notnull ? "NOT NULL" : "");
01558 }
01559 release_table(cur);
01560 } else {
01561 ast_cli(a->fd, "No such table '%s'\n", a->argv[4]);
01562 }
01563 }
01564 return 0;
01565 }
01566
01567 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
01568 {
01569 char status[256], credentials[100] = "";
01570 int ctimesec = time(NULL) - connect_time;
01571
01572 switch (cmd) {
01573 case CLI_INIT:
01574 e->command = "realtime show pgsql status";
01575 e->usage =
01576 "Usage: realtime show pgsql status\n"
01577 " Shows connection information for the PostgreSQL RealTime driver\n";
01578 return NULL;
01579 case CLI_GENERATE:
01580 return NULL;
01581 }
01582
01583 if (a->argc != 4)
01584 return CLI_SHOWUSAGE;
01585
01586 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
01587 if (!ast_strlen_zero(dbhost))
01588 snprintf(status, sizeof(status), "Connected to %s@%s, port %d", dbname, dbhost, dbport);
01589 else if (!ast_strlen_zero(dbsock))
01590 snprintf(status, sizeof(status), "Connected to %s on socket file %s", dbname, dbsock);
01591 else
01592 snprintf(status, sizeof(status), "Connected to %s@%s", dbname, dbhost);
01593
01594 if (!ast_strlen_zero(dbuser))
01595 snprintf(credentials, sizeof(credentials), " with username %s", dbuser);
01596
01597 if (ctimesec > 31536000)
01598 ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
01599 status, credentials, ctimesec / 31536000, (ctimesec % 31536000) / 86400,
01600 (ctimesec % 86400) / 3600, (ctimesec % 3600) / 60, ctimesec % 60);
01601 else if (ctimesec > 86400)
01602 ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
01603 credentials, ctimesec / 86400, (ctimesec % 86400) / 3600, (ctimesec % 3600) / 60,
01604 ctimesec % 60);
01605 else if (ctimesec > 3600)
01606 ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, credentials,
01607 ctimesec / 3600, (ctimesec % 3600) / 60, ctimesec % 60);
01608 else if (ctimesec > 60)
01609 ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, credentials, ctimesec / 60,
01610 ctimesec % 60);
01611 else
01612 ast_cli(a->fd, "%s%s for %d seconds.\n", status, credentials, ctimesec);
01613
01614 return CLI_SUCCESS;
01615 } else {
01616 return CLI_FAILURE;
01617 }
01618 }
01619
01620
01621 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
01622 .load = load_module,
01623 .unload = unload_module,
01624 .reload = reload
01625 );