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