res/res_config_pgsql.c

   1 /*
   2  * Asterisk -- A telephony toolkit for Linux.
   3  *
   4  * Copyright (C) 1999-2005, Digium, Inc.
   5  *
   6  * Manuel Guesdon <mguesdon@oxymium.net> - PostgreSQL RealTime Driver Author/Adaptor
   7  * Mark Spencer <markster@digium.com>  - Asterisk Author
   8  * Matthew Boehm <mboehm@cytelcom.com> - MySQL RealTime Driver Author
   9  *
  10  * res_config_pgsql.c <PostgreSQL plugin for RealTime configuration engine>
  11  *
  12  * v1.0   - (07-11-05) - Initial version based on res_config_mysql v2.0
  13  */
  14
  15 /*! \file
  16  *
  17  * \brief PostgreSQL plugin for Asterisk RealTime Architecture
  18  *
  19  * \author Mark Spencer <markster@digium.com>
  20  * \author Manuel Guesdon <mguesdon@oxymium.net> - PostgreSQL RealTime Driver Author/Adaptor
  21  *
  22  * \arg http://www.postgresql.org
  23  */
  24
  25 /*** MODULEINFO
  26         <depend>pgsql</depend>
  27  ***/
  28
  29 #include "asterisk.h"
  30
  31 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
  32
  33 #include <libpq-fe.h>                   /* PostgreSQL */
  34
  35 #include "asterisk/file.h"
  36 #include "asterisk/channel.h"
  37 #include "asterisk/pbx.h"
  38 #include "asterisk/config.h"
  39 #include "asterisk/module.h"
  40 #include "asterisk/lock.h"
  41 #include "asterisk/utils.h"
  42 #include "asterisk/cli.h"
  43
  44 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
  45
  46 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
  47
  48 PGconn *pgsqlConn = NULL;
  49
  50 #define MAX_DB_OPTION_SIZE 64
  51
  52 struct columns {
  53         char *name;
  54         char *type;
  55         int len;
  56         unsigned int notnull:1;
  57         unsigned int hasdefault:1;
  58         AST_LIST_ENTRY(columns) list;
  59 };
  60
  61 struct tables {
  62         ast_mutex_t lock;
  63         AST_LIST_HEAD_NOLOCK(psql_columns, columns) columns;
  64         AST_LIST_ENTRY(tables) list;
  65         char name[0];
  66 };
  67
  68 static AST_LIST_HEAD_STATIC(psql_tables, tables);
  69
  70 static char dbhost[MAX_DB_OPTION_SIZE] = "";
  71 static char dbuser[MAX_DB_OPTION_SIZE] = "";
  72 static char dbpass[MAX_DB_OPTION_SIZE] = "";
  73 static char dbname[MAX_DB_OPTION_SIZE] = "";
  74 static char dbsock[MAX_DB_OPTION_SIZE] = "";
  75 static int dbport = 5432;
  76 static time_t connect_time = 0;
  77
  78 static int parse_config(int reload);
  79 static int pgsql_reconnect(const char *database);
  80 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
  81 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
  82
  83 enum { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR } requirements;
  84
  85 static struct ast_cli_entry cli_realtime[] = {
  86         AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
  87         AST_CLI_DEFINE(handle_cli_realtime_pgsql_cache, "Shows cached tables within the PostgreSQL realtime driver"),
  88 };
  89
  90 static void destroy_table(struct tables *table)
  91 {
  92         struct columns *column;
  93         ast_mutex_lock(&table->lock);
  94         while ((column = AST_LIST_REMOVE_HEAD(&table->columns, list))) {
  95                 ast_free(column);
  96         }
  97         ast_mutex_unlock(&table->lock);
  98         ast_mutex_destroy(&table->lock);
  99         ast_free(table);
 100 }
 101
 102 static struct tables *find_table(const char *tablename)
 103 {
 104         struct columns *column;
 105         struct tables *table;
 106         struct ast_str *sql = ast_str_create(330);
 107         char *pgerror;
 108         PGresult *result;
 109         char *fname, *ftype, *flen, *fnotnull, *fdef;
 110         int i, rows;
 111
 112         AST_LIST_LOCK(&psql_tables);
 113         AST_LIST_TRAVERSE(&psql_tables, table, list) {
 114                 if (!strcasecmp(table->name, tablename)) {
 115                         ast_debug(1, "Found table in cache; now locking\n");
 116                         ast_mutex_lock(&table->lock);
 117                         ast_debug(1, "Lock cached table; now returning\n");
 118                         AST_LIST_UNLOCK(&psql_tables);
 119                         return table;
 120                 }
 121         }
 122
 123         ast_debug(1, "Table '%s' not found in cache, querying now\n", tablename);
 124
 125         /* Not found, scan the table */
 126         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", tablename);
 127         result = PQexec(pgsqlConn, sql->str);
 128         ast_debug(1, "Query of table structure complete.  Now retrieving results.\n");
 129         if (PQresultStatus(result) != PGRES_TUPLES_OK) {
 130                 pgerror = PQresultErrorMessage(result);
 131                 ast_log(LOG_ERROR, "Failed to query database columns: %s\n", pgerror);
 132                 PQclear(result);
 133                 AST_LIST_UNLOCK(&psql_tables);
 134                 return NULL;
 135         }
 136
 137         if (!(table = ast_calloc(1, sizeof(*table) + strlen(tablename) + 1))) {
 138                 ast_log(LOG_ERROR, "Unable to allocate memory for new table structure\n");
 139                 AST_LIST_UNLOCK(&psql_tables);
 140                 return NULL;
 141         }
 142         strcpy(table->name, tablename); /* SAFE */
 143         ast_mutex_init(&table->lock);
 144         AST_LIST_HEAD_INIT_NOLOCK(&table->columns);
 145
 146         rows = PQntuples(result);
 147         for (i = 0; i < rows; i++) {
 148                 fname = PQgetvalue(result, i, 0);
 149                 ftype = PQgetvalue(result, i, 1);
 150                 flen = PQgetvalue(result, i, 2);
 151                 fnotnull = PQgetvalue(result, i, 3);
 152                 fdef = PQgetvalue(result, i, 4);
 153                 ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
 154
 155                 if (!(column = ast_calloc(1, sizeof(*column) + strlen(fname) + strlen(ftype) + 2))) {
 156                         ast_log(LOG_ERROR, "Unable to allocate column element for %s, %s\n", tablename, fname);
 157                         destroy_table(table);
 158                         AST_LIST_UNLOCK(&psql_tables);
 159                         return NULL;
 160                 }
 161
 162                 if (strcmp(flen, "-1") == 0) {
 163                         /* Some types, like chars, have the length stored in a different field */
 164                         flen = PQgetvalue(result, i, 5);
 165                         sscanf(flen, "%d", &column->len);
 166                         column->len -= 4;
 167                 } else {
 168                         sscanf(flen, "%d", &column->len);
 169                 }
 170                 column->name = (char *)column + sizeof(*column);
 171                 column->type = (char *)column + sizeof(*column) + strlen(fname) + 1;
 172                 strcpy(column->name, fname);
 173                 strcpy(column->type, ftype);
 174                 if (*fnotnull == 't') {
 175                         column->notnull = 1;
 176                 } else {
 177                         column->notnull = 0;
 178                 }
 179                 if (!ast_strlen_zero(fdef)) {
 180                         column->hasdefault = 1;
 181                 } else {
 182                         column->hasdefault = 0;
 183                 }
 184                 AST_LIST_INSERT_TAIL(&table->columns, column, list);
 185         }
 186         PQclear(result);
 187
 188         AST_LIST_INSERT_TAIL(&psql_tables, table, list);
 189         ast_mutex_lock(&table->lock);
 190         AST_LIST_UNLOCK(&psql_tables);
 191         return table;
 192 }
 193
 194 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
 195 {
 196         PGresult *result = NULL;
 197         int num_rows = 0, pgerror;
 198         char sql[256], escapebuf[513];
 199         char *stringp;
 200         char *chunk;
 201         char *op;
 202         const char *newparam, *newval;
 203         struct ast_variable *var = NULL, *prev = NULL;
 204
 205         if (!table) {
 206                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 207                 return NULL;
 208         }
 209
 210         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 211         newparam = va_arg(ap, const char *);
 212         newval = va_arg(ap, const char *);
 213         if (!newparam || !newval) {
 214                 ast_log(LOG_WARNING,
 215                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 216                 if (pgsqlConn) {
 217                         PQfinish(pgsqlConn);
 218                         pgsqlConn = NULL;
 219                 };
 220                 return NULL;
 221         }
 222
 223         /* Create the first part of the query using the first parameter/value pairs we just extracted
 224            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 225         op = strchr(newparam, ' ') ? "" : " =";
 226
 227         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 228         if (pgerror) {
 229                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 230                 va_end(ap);
 231                 return NULL;
 232         }
 233
 234         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
 235                          escapebuf);
 236         while ((newparam = va_arg(ap, const char *))) {
 237                 newval = va_arg(ap, const char *);
 238                 if (!strchr(newparam, ' '))
 239                         op = " =";
 240                 else
 241                         op = "";
 242
 243                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 244                 if (pgerror) {
 245                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 246                         va_end(ap);
 247                         return NULL;
 248                 }
 249
 250                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
 251                                  op, escapebuf);
 252         }
 253         va_end(ap);
 254
 255         /* We now have our complete statement; Lets connect to the server and execute it. */
 256         ast_mutex_lock(&pgsql_lock);
 257         if (!pgsql_reconnect(database)) {
 258                 ast_mutex_unlock(&pgsql_lock);
 259                 return NULL;
 260         }
 261
 262         if (!(result = PQexec(pgsqlConn, sql))) {
 263                 ast_log(LOG_WARNING,
 264                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 265                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 266                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 267                 ast_mutex_unlock(&pgsql_lock);
 268                 return NULL;
 269         } else {
 270                 ExecStatusType result_status = PQresultStatus(result);
 271                 if (result_status != PGRES_COMMAND_OK
 272                         && result_status != PGRES_TUPLES_OK
 273                         && result_status != PGRES_NONFATAL_ERROR) {
 274                         ast_log(LOG_WARNING,
 275                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 276                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 277                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 278                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 279                         ast_mutex_unlock(&pgsql_lock);
 280                         return NULL;
 281                 }
 282         }
 283
 284         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
 285
 286         if ((num_rows = PQntuples(result)) > 0) {
 287                 int i = 0;
 288                 int rowIndex = 0;
 289                 int numFields = PQnfields(result);
 290                 char **fieldnames = NULL;
 291
 292                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
 293
 294                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
 295                         ast_mutex_unlock(&pgsql_lock);
 296                         PQclear(result);
 297                         return NULL;
 298                 }
 299                 for (i = 0; i < numFields; i++)
 300                         fieldnames[i] = PQfname(result, i);
 301                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 302                         for (i = 0; i < numFields; i++) {
 303                                 stringp = PQgetvalue(result, rowIndex, i);
 304                                 while (stringp) {
 305                                         chunk = strsep(&stringp, ";");
 306                                         if (!ast_strlen_zero(ast_strip(chunk))) {
 307                                                 if (prev) {
 308                                                         prev->next = ast_variable_new(fieldnames[i], chunk, "");
 309                                                         if (prev->next) {
 310                                                                 prev = prev->next;
 311                                                         }
 312                                                 } else {
 313                                                         prev = var = ast_variable_new(fieldnames[i], chunk, "");
 314                                                 }
 315                                         }
 316                                 }
 317                         }
 318                 }
 319                 ast_free(fieldnames);
 320         } else {
 321                 ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s.\n", table);
 322         }
 323
 324         ast_mutex_unlock(&pgsql_lock);
 325         PQclear(result);
 326
 327         return var;
 328 }
 329
 330 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
 331 {
 332         PGresult *result = NULL;
 333         int num_rows = 0, pgerror;
 334         char sql[256], escapebuf[513];
 335         const char *initfield = NULL;
 336         char *stringp;
 337         char *chunk;
 338         char *op;
 339         const char *newparam, *newval;
 340         struct ast_variable *var = NULL;
 341         struct ast_config *cfg = NULL;
 342         struct ast_category *cat = NULL;
 343
 344         if (!table) {
 345                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 346                 return NULL;
 347         }
 348
 349         if (!(cfg = ast_config_new()))
 350                 return NULL;
 351
 352         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 353         newparam = va_arg(ap, const char *);
 354         newval = va_arg(ap, const char *);
 355         if (!newparam || !newval) {
 356                 ast_log(LOG_WARNING,
 357                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 358                 if (pgsqlConn) {
 359                         PQfinish(pgsqlConn);
 360                         pgsqlConn = NULL;
 361                 };
 362                 return NULL;
 363         }
 364
 365         initfield = ast_strdupa(newparam);
 366         if ((op = strchr(initfield, ' '))) {
 367                 *op = '\0';
 368         }
 369
 370         /* Create the first part of the query using the first parameter/value pairs we just extracted
 371            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 372
 373         if (!strchr(newparam, ' '))
 374                 op = " =";
 375         else
 376                 op = "";
 377
 378         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 379         if (pgerror) {
 380                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 381                 va_end(ap);
 382                 return NULL;
 383         }
 384
 385         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
 386                          escapebuf);
 387         while ((newparam = va_arg(ap, const char *))) {
 388                 newval = va_arg(ap, const char *);
 389                 if (!strchr(newparam, ' '))
 390                         op = " =";
 391                 else
 392                         op = "";
 393
 394                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 395                 if (pgerror) {
 396                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 397                         va_end(ap);
 398                         return NULL;
 399                 }
 400
 401                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
 402                                  op, escapebuf);
 403         }
 404
 405         if (initfield) {
 406                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
 407         }
 408
 409         va_end(ap);
 410
 411         /* We now have our complete statement; Lets connect to the server and execute it. */
 412         ast_mutex_lock(&pgsql_lock);
 413         if (!pgsql_reconnect(database)) {
 414                 ast_mutex_unlock(&pgsql_lock);
 415                 return NULL;
 416         }
 417
 418         if (!(result = PQexec(pgsqlConn, sql))) {
 419                 ast_log(LOG_WARNING,
 420                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 421                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 422                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 423                 ast_mutex_unlock(&pgsql_lock);
 424                 return NULL;
 425         } else {
 426                 ExecStatusType result_status = PQresultStatus(result);
 427                 if (result_status != PGRES_COMMAND_OK
 428                         && result_status != PGRES_TUPLES_OK
 429                         && result_status != PGRES_NONFATAL_ERROR) {
 430                         ast_log(LOG_WARNING,
 431                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 432                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 433                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 434                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 435                         ast_mutex_unlock(&pgsql_lock);
 436                         return NULL;
 437                 }
 438         }
 439
 440         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
 441
 442         if ((num_rows = PQntuples(result)) > 0) {
 443                 int numFields = PQnfields(result);
 444                 int i = 0;
 445                 int rowIndex = 0;
 446                 char **fieldnames = NULL;
 447
 448                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
 449
 450                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
 451                         ast_mutex_unlock(&pgsql_lock);
 452                         PQclear(result);
 453                         return NULL;
 454                 }
 455                 for (i = 0; i < numFields; i++)
 456                         fieldnames[i] = PQfname(result, i);
 457
 458                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 459                         var = NULL;
 460                         if (!(cat = ast_category_new("","",99999)))
 461                                 continue;
 462                         for (i = 0; i < numFields; i++) {
 463                                 stringp = PQgetvalue(result, rowIndex, i);
 464                                 while (stringp) {
 465                                         chunk = strsep(&stringp, ";");
 466                                         if (!ast_strlen_zero(ast_strip(chunk))) {
 467                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
 468                                                         ast_category_rename(cat, chunk);
 469                                                 }
 470                                                 var = ast_variable_new(fieldnames[i], chunk, "");
 471                                                 ast_variable_append(cat, var);
 472                                         }
 473                                 }
 474                         }
 475                         ast_category_append(cfg, cat);
 476                 }
 477                 ast_free(fieldnames);
 478         } else {
 479                 ast_log(LOG_WARNING,
 480                                 "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
 481         }
 482
 483         ast_mutex_unlock(&pgsql_lock);
 484         PQclear(result);
 485
 486         return cfg;
 487 }
 488
 489 static int update_pgsql(const char *database, const char *tablename, const char *keyfield,
 490                                                 const char *lookup, va_list ap)
 491 {
 492         PGresult *result = NULL;
 493         int numrows = 0, pgerror;
 494         char escapebuf[513];
 495         const char *newparam, *newval;
 496         struct ast_str *sql = ast_str_create(100);
 497         struct tables *table;
 498         struct columns *column = NULL;
 499
 500         if (!tablename) {
 501                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 502                 ast_free(sql);
 503                 return -1;
 504         }
 505
 506         if (!(table = find_table(tablename))) {
 507                 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
 508                 ast_free(sql);
 509                 return -1;
 510         }
 511
 512         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 513         newparam = va_arg(ap, const char *);
 514         newval = va_arg(ap, const char *);
 515         if (!newparam || !newval) {
 516                 ast_log(LOG_WARNING,
 517                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 518                 if (pgsqlConn) {
 519                         PQfinish(pgsqlConn);
 520                         pgsqlConn = NULL;
 521                 };
 522                 ast_mutex_unlock(&table->lock);
 523                 ast_free(sql);
 524                 return -1;
 525         }
 526
 527         /* Check that the column exists in the table */
 528         AST_LIST_TRAVERSE(&table->columns, column, list) {
 529                 if (strcmp(column->name, newparam) == 0) {
 530                         break;
 531                 }
 532         }
 533
 534         if (!column) {
 535                 ast_log(LOG_ERROR, "PostgreSQL RealTime: Updating on column '%s', but that column does not exist within the table '%s'!\n", newparam, tablename);
 536                 ast_mutex_unlock(&table->lock);
 537                 ast_free(sql);
 538                 return -1;
 539         }
 540
 541         /* Create the first part of the query using the first parameter/value pairs we just extracted
 542            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 543
 544         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 545         if (pgerror) {
 546                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 547                 va_end(ap);
 548                 ast_mutex_unlock(&table->lock);
 549                 ast_free(sql);
 550                 return -1;
 551         }
 552         ast_str_set(&sql, 0, "UPDATE %s SET %s = '%s'", tablename, newparam, escapebuf);
 553
 554         while ((newparam = va_arg(ap, const char *))) {
 555                 newval = va_arg(ap, const char *);
 556
 557                 /* If the column is not within the table, then skip it */
 558                 AST_LIST_TRAVERSE(&table->columns, column, list) {
 559                         if (strcmp(column->name, newparam) == 0) {
 560                                 break;
 561                         }
 562                 }
 563
 564                 if (!column) {
 565                         ast_log(LOG_WARNING, "Attempted to update column '%s' in table '%s', but column does not exist!\n", newparam, tablename);
 566                         continue;
 567                 }
 568
 569                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 570                 if (pgerror) {
 571                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 572                         va_end(ap);
 573                         ast_mutex_unlock(&table->lock);
 574                         ast_free(sql);
 575                         return -1;
 576                 }
 577
 578                 ast_str_append(&sql, 0, ", %s = '%s'", newparam, escapebuf);
 579         }
 580         va_end(ap);
 581         ast_mutex_unlock(&table->lock);
 582
 583         PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
 584         if (pgerror) {
 585                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
 586                 va_end(ap);
 587                 ast_free(sql);
 588                 return -1;
 589         }
 590
 591         ast_str_append(&sql, 0, " WHERE %s = '%s'", keyfield, escapebuf);
 592
 593         ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", sql->str);
 594
 595         /* We now have our complete statement; Lets connect to the server and execute it. */
 596         ast_mutex_lock(&pgsql_lock);
 597         if (!pgsql_reconnect(database)) {
 598                 ast_mutex_unlock(&pgsql_lock);
 599                 ast_free(sql);
 600                 return -1;
 601         }
 602
 603         if (!(result = PQexec(pgsqlConn, sql->str))) {
 604                 ast_log(LOG_WARNING,
 605                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 606                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
 607                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 608                 ast_mutex_unlock(&pgsql_lock);
 609                 ast_free(sql);
 610                 return -1;
 611         } else {
 612                 ExecStatusType result_status = PQresultStatus(result);
 613                 if (result_status != PGRES_COMMAND_OK
 614                         && result_status != PGRES_TUPLES_OK
 615                         && result_status != PGRES_NONFATAL_ERROR) {
 616                         ast_log(LOG_WARNING,
 617                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 618                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
 619                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 620                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 621                         ast_mutex_unlock(&pgsql_lock);
 622                         ast_free(sql);
 623                         return -1;
 624                 }
 625         }
 626
 627         numrows = atoi(PQcmdTuples(result));
 628         ast_mutex_unlock(&pgsql_lock);
 629         ast_free(sql);
 630
 631         ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
 632
 633         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
 634          * An integer greater than zero indicates the number of rows affected
 635          * Zero indicates that no records were updated
 636          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 637          */
 638
 639         if (numrows >= 0)
 640                 return (int) numrows;
 641
 642         return -1;
 643 }
 644
 645 static int store_pgsql(const char *database, const char *table, va_list ap)
 646 {
 647         PGresult *result = NULL;
 648         Oid insertid;
 649         char sql[256];
 650         char params[256];
 651         char vals[256];
 652         char buf[256];
 653         int pgresult;
 654         const char *newparam, *newval;
 655
 656         if (!table) {
 657                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 658                 return -1;
 659         }
 660
 661         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 662         newparam = va_arg(ap, const char *);
 663         newval = va_arg(ap, const char *);
 664         if (!newparam || !newval) {
 665                 ast_log(LOG_WARNING,
 666                                 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
 667                 if (pgsqlConn) {
 668                         PQfinish(pgsqlConn);
 669                         pgsqlConn = NULL;
 670                 };
 671                 return -1;
 672         }
 673
 674         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
 675         ast_mutex_lock(&pgsql_lock);
 676         if (!pgsql_reconnect(database)) {
 677                 ast_mutex_unlock(&pgsql_lock);
 678                 return -1;
 679         }
 680
 681         /* Create the first part of the query using the first parameter/value pairs we just extracted
 682            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 683         PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
 684         snprintf(params, sizeof(params), "%s", buf);
 685         PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
 686         snprintf(vals, sizeof(vals), "'%s'", buf);
 687         while ((newparam = va_arg(ap, const char *))) {
 688                 newval = va_arg(ap, const char *);
 689                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
 690                 snprintf(params + strlen(params), sizeof(params) - strlen(params), ", %s", buf);
 691                 PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
 692                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", '%s'", buf);
 693         }
 694         va_end(ap);
 695         snprintf(sql, sizeof(sql), "INSERT INTO (%s) VALUES (%s)", params, vals);
 696
 697         ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", sql);
 698
 699         if (!(result = PQexec(pgsqlConn, sql))) {
 700                 ast_log(LOG_WARNING,
 701                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 702                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 703                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 704                 ast_mutex_unlock(&pgsql_lock);
 705                 return -1;
 706         } else {
 707                 ExecStatusType result_status = PQresultStatus(result);
 708                 if (result_status != PGRES_COMMAND_OK
 709                         && result_status != PGRES_TUPLES_OK
 710                         && result_status != PGRES_NONFATAL_ERROR) {
 711                         ast_log(LOG_WARNING,
 712                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 713                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 714                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 715                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 716                         ast_mutex_unlock(&pgsql_lock);
 717                         return -1;
 718                 }
 719         }
 720
 721         insertid = PQoidValue(result);
 722         ast_mutex_unlock(&pgsql_lock);
 723
 724         ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
 725
 726         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
 727          * An integer greater than zero indicates the number of rows affected
 728          * Zero indicates that no records were updated
 729          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 730          */
 731
 732         if (insertid >= 0)
 733                 return (int) insertid;
 734
 735         return -1;
 736 }
 737
 738 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
 739 {
 740         PGresult *result = NULL;
 741         int numrows = 0;
 742         int pgresult;
 743         char sql[256];
 744         char buf[256], buf2[256];
 745         const char *newparam, *newval;
 746
 747         if (!table) {
 748                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 749                 return -1;
 750         }
 751
 752         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 753         /*newparam = va_arg(ap, const char *);
 754         newval = va_arg(ap, const char *);
 755         if (!newparam || !newval) {*/
 756         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
 757                 ast_log(LOG_WARNING,
 758                                 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
 759                 if (pgsqlConn) {
 760                         PQfinish(pgsqlConn);
 761                         pgsqlConn = NULL;
 762                 };
 763                 return -1;
 764         }
 765
 766         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
 767         ast_mutex_lock(&pgsql_lock);
 768         if (!pgsql_reconnect(database)) {
 769                 ast_mutex_unlock(&pgsql_lock);
 770                 return -1;
 771         }
 772
 773
 774         /* Create the first part of the query using the first parameter/value pairs we just extracted
 775            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 776
 777         PQescapeStringConn(pgsqlConn, buf, keyfield, sizeof(keyfield), &pgresult);
 778         PQescapeStringConn(pgsqlConn, buf2, lookup, sizeof(lookup), &pgresult);
 779         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE %s = '%s'", table, buf, buf2);
 780         while ((newparam = va_arg(ap, const char *))) {
 781                 newval = va_arg(ap, const char *);
 782                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
 783                 PQescapeStringConn(pgsqlConn, buf2, newval, sizeof(newval), &pgresult);
 784                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s = '%s'", buf, buf2);
 785         }
 786         va_end(ap);
 787
 788         ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", sql);
 789
 790         if (!(result = PQexec(pgsqlConn, sql))) {
 791                 ast_log(LOG_WARNING,
 792                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 793                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 794                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 795                 ast_mutex_unlock(&pgsql_lock);
 796                 return -1;
 797         } else {
 798                 ExecStatusType result_status = PQresultStatus(result);
 799                 if (result_status != PGRES_COMMAND_OK
 800                         && result_status != PGRES_TUPLES_OK
 801                         && result_status != PGRES_NONFATAL_ERROR) {
 802                         ast_log(LOG_WARNING,
 803                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 804                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 805                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 806                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 807                         ast_mutex_unlock(&pgsql_lock);
 808                         return -1;
 809                 }
 810         }
 811
 812         numrows = atoi(PQcmdTuples(result));
 813         ast_mutex_unlock(&pgsql_lock);
 814
 815         ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
 816
 817         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
 818          * An integer greater than zero indicates the number of rows affected
 819          * Zero indicates that no records were updated
 820          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 821          */
 822
 823         if (numrows >= 0)
 824                 return (int) numrows;
 825
 826         return -1;
 827 }
 828
 829
 830 static struct ast_config *config_pgsql(const char *database, const char *table,
 831                                                                            const char *file, struct ast_config *cfg,
 832                                                                            struct ast_flags flags, const char *suggested_incl, const char *who_asked)
 833 {
 834         PGresult *result = NULL;
 835         long num_rows;
 836         struct ast_variable *new_v;
 837         struct ast_category *cur_cat = NULL;
 838         char sqlbuf[1024] = "";
 839         char *sql = sqlbuf;
 840         size_t sqlleft = sizeof(sqlbuf);
 841         char last[80] = "";
 842         int last_cat_metric = 0;
 843
 844         last[0] = '\0';
 845
 846         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
 847                 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
 848                 return NULL;
 849         }
 850
 851         ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
 852         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
 853         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
 854
 855         ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", sqlbuf);
 856
 857         /* We now have our complete statement; Lets connect to the server and execute it. */
 858         ast_mutex_lock(&pgsql_lock);
 859         if (!pgsql_reconnect(database)) {
 860                 ast_mutex_unlock(&pgsql_lock);
 861                 return NULL;
 862         }
 863
 864         if (!(result = PQexec(pgsqlConn, sqlbuf))) {
 865                 ast_log(LOG_WARNING,
 866                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 867                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 868                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 869                 ast_mutex_unlock(&pgsql_lock);
 870                 return NULL;
 871         } else {
 872                 ExecStatusType result_status = PQresultStatus(result);
 873                 if (result_status != PGRES_COMMAND_OK
 874                         && result_status != PGRES_TUPLES_OK
 875                         && result_status != PGRES_NONFATAL_ERROR) {
 876                         ast_log(LOG_WARNING,
 877                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 878                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 879                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 880                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 881                         ast_mutex_unlock(&pgsql_lock);
 882                         return NULL;
 883                 }
 884         }
 885
 886         if ((num_rows = PQntuples(result)) > 0) {
 887                 int rowIndex = 0;
 888
 889                 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
 890
 891                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 892                         char *field_category = PQgetvalue(result, rowIndex, 0);
 893                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
 894                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
 895                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
 896                         if (!strcmp(field_var_name, "#include")) {
 897                                 if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
 898                                         PQclear(result);
 899                                         ast_mutex_unlock(&pgsql_lock);
 900                                         return NULL;
 901                                 }
 902                                 continue;
 903                         }
 904
 905                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
 906                                 cur_cat = ast_category_new(field_category, "", 99999);
 907                                 if (!cur_cat)
 908                                         break;
 909                                 strcpy(last, field_category);
 910                                 last_cat_metric = atoi(field_cat_metric);
 911                                 ast_category_append(cfg, cur_cat);
 912                         }
 913                         new_v = ast_variable_new(field_var_name, field_var_val, "");
 914                         ast_variable_append(cur_cat, new_v);
 915                 }
 916         } else {
 917                 ast_log(LOG_WARNING,
 918                                 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
 919         }
 920
 921         PQclear(result);
 922         ast_mutex_unlock(&pgsql_lock);
 923
 924         return cfg;
 925 }
 926
 927 static int require_pgsql(const char *database, const char *tablename, va_list ap)
 928 {
 929         struct columns *column;
 930         struct tables *table = find_table(tablename);
 931         char *elm;
 932         int type, size, res = 0;
 933
 934         if (!table) {
 935                 ast_log(LOG_WARNING, "Table %s not found in database.  This table should exist if you're using realtime.\n", tablename);
 936                 return -1;
 937         }
 938
 939         while ((elm = va_arg(ap, char *))) {
 940                 type = va_arg(ap, require_type);
 941                 size = va_arg(ap, int);
 942                 AST_LIST_TRAVERSE(&table->columns, column, list) {
 943                         if (strcmp(column->name, elm) == 0) {
 944                                 /* Char can hold anything, as long as it is large enough */
 945                                 if ((strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0 || strcmp(column->type, "bpchar") == 0)) {
 946                                         if ((size > column->len) && column->len != -1) {
 947                                                 ast_log(LOG_WARNING, "Column '%s' should be at least %d long, but is only %d long.\n", column->name, size, column->len);
 948                                                 res = -1;
 949                                         }
 950                                 } else if (strncmp(column->type, "int", 3) == 0) {
 951                                         int typesize = atoi(column->type + 3);
 952                                         /* Integers can hold only other integers */
 953                                         if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
 954                                                 type == RQ_INTEGER4 || type == RQ_UINTEGER4 ||
 955                                                 type == RQ_INTEGER3 || type == RQ_UINTEGER3 ||
 956                                                 type == RQ_UINTEGER2) && typesize == 2) {
 957                                                 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
 958                                                 res = -1;
 959                                         } else if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
 960                                                 type == RQ_UINTEGER4) && typesize == 4) {
 961                                                 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
 962                                                 res = -1;
 963                                         } else if (type == RQ_CHAR || type == RQ_DATETIME || type == RQ_FLOAT || type == RQ_DATE) {
 964                                                 ast_log(LOG_WARNING, "Column '%s' is of the incorrect type: (need %s(%d) but saw %s)\n",
 965                                                         column->name,
 966                                                                 type == RQ_CHAR ? "char" :
 967                                                                 type == RQ_DATETIME ? "datetime" :
 968                                                                 type == RQ_DATE ? "date" :
 969                                                                 type == RQ_FLOAT ? "float" :
 970                                                                 "a rather stiff drink ",
 971                                                         size, column->type);
 972                                                 res = -1;
 973                                         }
 974                                 } else if (strncmp(column->type, "float", 5) == 0 && !ast_rq_is_int(type) && type != RQ_FLOAT) {
 975                                         ast_log(LOG_WARNING, "Column %s cannot be a %s\n", column->name, column->type);
 976                                         res = -1;
 977                                 } else { /* There are other types that no module implements yet */
 978                                         ast_log(LOG_WARNING, "Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
 979                                         res = -1;
 980                                 }
 981                                 break;
 982                         }
 983                 }
 984
 985                 if (!column) {
 986                         if (requirements == RQ_WARN) {
 987                                 ast_log(LOG_WARNING, "Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
 988                         } else {
 989                                 struct ast_str *sql = ast_str_create(100);
 990                                 char fieldtype[15];
 991                                 PGresult *res;
 992
 993                                 if (requirements == RQ_CREATECHAR || type == RQ_CHAR) {
 994                                         /* Size is minimum length; make it at least 50% greater,
 995                                          * just to be sure, because PostgreSQL doesn't support
 996                                          * resizing columns. */
 997                                         snprintf(fieldtype, sizeof(fieldtype), "CHAR(%d)",
 998                                                 size < 15 ? size * 2 :
 999                                                 (size * 3 / 2 > 255) ? 255 : size * 3 / 2);
1000                                 } else if (type == RQ_INTEGER1 || type == RQ_UINTEGER1 || type == RQ_INTEGER2) {
1001                                         snprintf(fieldtype, sizeof(fieldtype), "INT2");
1002                                 } else if (type == RQ_UINTEGER2 || type == RQ_INTEGER3 || type == RQ_UINTEGER3 || type == RQ_INTEGER4) {
1003                                         snprintf(fieldtype, sizeof(fieldtype), "INT4");
1004                                 } else if (type == RQ_UINTEGER4 || type == RQ_INTEGER8) {
1005                                         snprintf(fieldtype, sizeof(fieldtype), "INT8");
1006                                 } else if (type == RQ_UINTEGER8) {
1007                                         /* No such type on PostgreSQL */
1008                                         snprintf(fieldtype, sizeof(fieldtype), "CHAR(20)");
1009                                 } else if (type == RQ_FLOAT) {
1010                                         snprintf(fieldtype, sizeof(fieldtype), "FLOAT8");
1011                                 } else if (type == RQ_DATE) {
1012                                         snprintf(fieldtype, sizeof(fieldtype), "DATE");
1013                                 } else if (type == RQ_DATETIME) {
1014                                         snprintf(fieldtype, sizeof(fieldtype), "TIMESTAMP");
1015                                 } else {
1016                                         ast_log(LOG_ERROR, "Unrecognized request type %d\n", type);
1017                                         ast_free(sql);
1018                                         continue;
1019                                 }
1020                                 ast_str_set(&sql, 0, "ALTER TABLE %s ADD COLUMN %s %s", tablename, elm, fieldtype);
1021                                 ast_debug(1, "About to lock pgsql_lock (running alter on table '%s' to add column '%s')\n", tablename, elm);
1022
1023                                 ast_mutex_lock(&pgsql_lock);
1024                                 if (!pgsql_reconnect(database)) {
1025                                         ast_mutex_unlock(&pgsql_lock);
1026                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", sql->str);
1027                                         ast_free(sql);
1028                                         continue;
1029                                 }
1030
1031                                 ast_debug(1, "About to run ALTER query on table '%s' to add column '%s'\n", tablename, elm);
1032                                 res = PQexec(pgsqlConn, sql->str);
1033                                 ast_debug(1, "Finished running ALTER query on table '%s'\n", tablename);
1034                                 if (PQresultStatus(res) != PGRES_COMMAND_OK) {
1035                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", sql->str);
1036                                 }
1037                                 PQclear(res);
1038                                 ast_mutex_unlock(&pgsql_lock);
1039
1040                                 ast_free(sql);
1041                         }
1042                 }
1043         }
1044         ast_mutex_unlock(&table->lock);
1045         return res;
1046 }
1047
1048 static int unload_pgsql(const char *database, const char *tablename)
1049 {
1050         struct tables *cur;
1051         ast_debug(2, "About to lock table cache list\n");
1052         AST_LIST_LOCK(&psql_tables);
1053         ast_debug(2, "About to traverse table cache list\n");
1054         AST_LIST_TRAVERSE_SAFE_BEGIN(&psql_tables, cur, list) {
1055                 if (strcmp(cur->name, tablename) == 0) {
1056                         ast_debug(2, "About to remove matching cache entry\n");
1057                         AST_LIST_REMOVE_CURRENT(list);
1058                         ast_debug(2, "About to destroy matching cache entry\n");
1059                         destroy_table(cur);
1060                         ast_debug(1, "Cache entry '%s@%s' destroyed\n", tablename, database);
1061                         break;
1062                 }
1063         }
1064         AST_LIST_TRAVERSE_SAFE_END
1065         AST_LIST_UNLOCK(&psql_tables);
1066         ast_debug(2, "About to return\n");
1067         return cur ? 0 : -1;
1068 }
1069
1070 static struct ast_config_engine pgsql_engine = {
1071         .name = "pgsql",
1072         .load_func = config_pgsql,
1073         .realtime_func = realtime_pgsql,
1074         .realtime_multi_func = realtime_multi_pgsql,
1075         .store_func = store_pgsql,
1076         .destroy_func = destroy_pgsql,
1077         .update_func = update_pgsql,
1078         .require_func = require_pgsql,
1079         .unload_func = unload_pgsql,
1080 };
1081
1082 static int load_module(void)
1083 {
1084         if(!parse_config(0))
1085                 return AST_MODULE_LOAD_DECLINE;
1086
1087         ast_config_engine_register(&pgsql_engine);
1088         ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
1089         ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
1090
1091         return 0;
1092 }
1093
1094 static int unload_module(void)
1095 {
1096         struct tables *table;
1097         /* Acquire control before doing anything to the module itself. */
1098         ast_mutex_lock(&pgsql_lock);
1099
1100         if (pgsqlConn) {
1101                 PQfinish(pgsqlConn);
1102                 pgsqlConn = NULL;
1103         }
1104         ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
1105         ast_config_engine_deregister(&pgsql_engine);
1106         ast_verb(1, "PostgreSQL RealTime unloaded.\n");
1107
1108         /* Destroy cached table info */
1109         AST_LIST_LOCK(&psql_tables);
1110         while ((table = AST_LIST_REMOVE_HEAD(&psql_tables, list))) {
1111                 destroy_table(table);
1112         }
1113         AST_LIST_UNLOCK(&psql_tables);
1114
1115         /* Unlock so something else can destroy the lock. */
1116         ast_mutex_unlock(&pgsql_lock);
1117
1118         return 0;
1119 }
1120
1121 static int reload(void)
1122 {
1123         parse_config(1);
1124
1125         return 0;
1126 }
1127
1128 static int parse_config(int reload)
1129 {
1130         struct ast_config *config;
1131         const char *s;
1132         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
1133
1134         if ((config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags)) == CONFIG_STATUS_FILEUNCHANGED)
1135                 return 0;
1136
1137         if (!config) {
1138                 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
1139                 return 0;
1140         }
1141
1142         ast_mutex_lock(&pgsql_lock);
1143
1144         if (pgsqlConn) {
1145                 PQfinish(pgsqlConn);
1146                 pgsqlConn = NULL;
1147         }
1148
1149         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
1150                 ast_log(LOG_WARNING,
1151                                 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
1152                 strcpy(dbuser, "asterisk");
1153         } else {
1154                 ast_copy_string(dbuser, s, sizeof(dbuser));
1155         }
1156
1157         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
1158                 ast_log(LOG_WARNING,
1159                                 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
1160                 strcpy(dbpass, "asterisk");
1161         } else {
1162                 ast_copy_string(dbpass, s, sizeof(dbpass));
1163         }
1164
1165         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
1166                 ast_log(LOG_WARNING,
1167                                 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
1168                 dbhost[0] = '\0';
1169         } else {
1170                 ast_copy_string(dbhost, s, sizeof(dbhost));
1171         }
1172
1173         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
1174                 ast_log(LOG_WARNING,
1175                                 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
1176                 strcpy(dbname, "asterisk");
1177         } else {
1178                 ast_copy_string(dbname, s, sizeof(dbname));
1179         }
1180
1181         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
1182                 ast_log(LOG_WARNING,
1183                                 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
1184                 dbport = 5432;
1185         } else {
1186                 dbport = atoi(s);
1187         }
1188
1189         if (!ast_strlen_zero(dbhost)) {
1190                 /* No socket needed */
1191         } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
1192                 ast_log(LOG_WARNING,
1193                                 "PostgreSQL RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
1194                 strcpy(dbsock, "/tmp/pgsql.sock");
1195         } else {
1196                 ast_copy_string(dbsock, s, sizeof(dbsock));
1197         }
1198
1199         if (!(s = ast_variable_retrieve(config, "general", "requirements"))) {
1200                 ast_log(LOG_WARNING,
1201                                 "PostgreSQL RealTime: no requirements setting found, using 'warn' as default.\n");
1202                 requirements = RQ_WARN;
1203         } else if (!strcasecmp(s, "createclose")) {
1204                 requirements = RQ_CREATECLOSE;
1205         } else if (!strcasecmp(s, "createchar")) {
1206                 requirements = RQ_CREATECHAR;
1207         }
1208
1209         ast_config_destroy(config);
1210
1211         if (option_debug) {
1212                 if (!ast_strlen_zero(dbhost)) {
1213                         ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
1214                         ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
1215                 } else {
1216                         ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
1217                 }
1218                 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
1219                 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
1220                 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
1221         }
1222
1223         if (!pgsql_reconnect(NULL)) {
1224                 ast_log(LOG_WARNING,
1225                                 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
1226                 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
1227         }
1228
1229         ast_verb(2, "PostgreSQL RealTime reloaded.\n");
1230
1231         /* Done reloading. Release lock so others can now use driver. */
1232         ast_mutex_unlock(&pgsql_lock);
1233
1234         return 1;
1235 }
1236
1237 static int pgsql_reconnect(const char *database)
1238 {
1239         char my_database[50];
1240
1241         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
1242
1243         /* mutex lock should have been locked before calling this function. */
1244
1245         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
1246                 PQfinish(pgsqlConn);
1247                 pgsqlConn = NULL;
1248         }
1249
1250         /* DB password can legitimately be 0-length */
1251         if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
1252                 struct ast_str *connInfo = ast_str_create(32);
1253
1254                 ast_str_set(&connInfo, 0, "host=%s port=%d dbname=%s user=%s",
1255                         dbhost, dbport, my_database, dbuser);
1256                 if (!ast_strlen_zero(dbpass))
1257                         ast_str_append(&connInfo, 0, " password=%s", dbpass);
1258
1259                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
1260                 pgsqlConn = PQconnectdb(connInfo->str);
1261                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
1262                 ast_free(connInfo);
1263                 connInfo = NULL;
1264
1265                 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
1266                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1267                         ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
1268                         connect_time = time(NULL);
1269                         return 1;
1270                 } else {
1271                         ast_log(LOG_ERROR,
1272                                         "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
1273                                         dbname, dbhost, PQresultErrorMessage(NULL));
1274                         return 0;
1275                 }
1276         } else {
1277                 ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
1278                 return 1;
1279         }
1280 }
1281
1282 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1283 {
1284         struct tables *cur;
1285         int l, which;
1286         char *ret = NULL;
1287
1288         switch (cmd) {
1289         case CLI_INIT:
1290                 e->command = "realtime pgsql cache";
1291                 e->usage =
1292                         "Usage: realtime pgsql cache [<table>]\n"
1293                         "       Shows table cache for the PostgreSQL RealTime driver\n";
1294                 return NULL;
1295         case CLI_GENERATE:
1296                 if (a->argc != 3) {
1297                         return NULL;
1298                 }
1299                 l = strlen(a->word);
1300                 which = 0;
1301                 AST_LIST_LOCK(&psql_tables);
1302                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1303                         if (!strncasecmp(a->word, cur->name, l) && ++which > a->n) {
1304                                 ret = ast_strdup(cur->name);
1305                                 break;
1306                         }
1307                 }
1308                 AST_LIST_UNLOCK(&psql_tables);
1309                 return ret;
1310         }
1311
1312         if (a->argc == 3) {
1313                 /* List of tables */
1314                 AST_LIST_LOCK(&psql_tables);
1315                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1316                         ast_cli(a->fd, "%s\n", cur->name);
1317                 }
1318                 AST_LIST_UNLOCK(&psql_tables);
1319         } else if (a->argc == 4) {
1320                 /* List of columns */
1321                 if ((cur = find_table(a->argv[3]))) {
1322                         struct columns *col;
1323                         ast_cli(a->fd, "Columns for Table Cache '%s':\n", a->argv[3]);
1324                         ast_cli(a->fd, "%-20.20s %-20.20s %-3.3s %-8.8s\n", "Name", "Type", "Len", "Nullable");
1325                         AST_LIST_TRAVERSE(&cur->columns, col, list) {
1326                                 ast_cli(a->fd, "%-20.20s %-20.20s %3d %-8.8s\n", col->name, col->type, col->len, col->notnull ? "NOT NULL" : "");
1327                         }
1328                         ast_mutex_unlock(&cur->lock);
1329                 } else {
1330                         ast_cli(a->fd, "No such table '%s'\n", a->argv[3]);
1331                 }
1332         }
1333         return 0;
1334 }
1335
1336 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1337 {
1338         char status[256], credentials[100] = "";
1339         int ctime = time(NULL) - connect_time;
1340
1341         switch (cmd) {
1342         case CLI_INIT:
1343                 e->command = "realtime pgsql status";
1344                 e->usage =
1345                         "Usage: realtime pgsql status\n"
1346                         "       Shows connection information for the PostgreSQL RealTime driver\n";
1347                 return NULL;
1348         case CLI_GENERATE:
1349                 return NULL;
1350         }
1351
1352         if (a->argc != 3)
1353                 return CLI_SHOWUSAGE;
1354
1355         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1356                 if (!ast_strlen_zero(dbhost))
1357                         snprintf(status, sizeof(status), "Connected to %s@%s, port %d", dbname, dbhost, dbport);
1358                 else if (!ast_strlen_zero(dbsock))
1359                         snprintf(status, sizeof(status), "Connected to %s on socket file %s", dbname, dbsock);
1360                 else
1361                         snprintf(status, sizeof(status), "Connected to %s@%s", dbname, dbhost);
1362
1363                 if (!ast_strlen_zero(dbuser))
1364                         snprintf(credentials, sizeof(credentials), " with username %s", dbuser);
1365
1366                 if (ctime > 31536000)
1367                         ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
1368                                         status, credentials, ctime / 31536000, (ctime % 31536000) / 86400,
1369                                         (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
1370                 else if (ctime > 86400)
1371                         ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
1372                                         credentials, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
1373                                         ctime % 60);
1374                 else if (ctime > 3600)
1375                         ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, credentials,
1376                                         ctime / 3600, (ctime % 3600) / 60, ctime % 60);
1377                 else if (ctime > 60)
1378                         ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, credentials, ctime / 60,
1379                                         ctime % 60);
1380                 else
1381                         ast_cli(a->fd, "%s%s for %d seconds.\n", status, credentials, ctime);
1382
1383                 return CLI_SUCCESS;
1384         } else {
1385                 return CLI_FAILURE;
1386         }
1387 }
1388
1389 /* needs usecount semantics defined */
1390 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
1391                 .load = load_module,
1392                 .unload = unload_module,
1393                 .reload = reload
1394                );