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 static char dbhost[MAX_DB_OPTION_SIZE] = "";
  53 static char dbuser[MAX_DB_OPTION_SIZE] = "";
  54 static char dbpass[MAX_DB_OPTION_SIZE] = "";
  55 static char dbname[MAX_DB_OPTION_SIZE] = "";
  56 static char dbsock[MAX_DB_OPTION_SIZE] = "";
  57 static int dbport = 5432;
  58 static time_t connect_time = 0;
  59
  60 static int parse_config(int reload);
  61 static int pgsql_reconnect(const char *database);
  62 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
  63
  64 static struct ast_cli_entry cli_realtime[] = {
  65         AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
  66 };
  67
  68 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
  69 {
  70         PGresult *result = NULL;
  71         int num_rows = 0, pgerror;
  72         char sql[256], escapebuf[513];
  73         char *stringp;
  74         char *chunk;
  75         char *op;
  76         const char *newparam, *newval;
  77         struct ast_variable *var = NULL, *prev = NULL;
  78
  79         if (!table) {
  80                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
  81                 return NULL;
  82         }
  83
  84         /* Get the first parameter and first value in our list of passed paramater/value pairs */
  85         newparam = va_arg(ap, const char *);
  86         newval = va_arg(ap, const char *);
  87         if (!newparam || !newval) {
  88                 ast_log(LOG_WARNING,
  89                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
  90                 if (pgsqlConn) {
  91                         PQfinish(pgsqlConn);
  92                         pgsqlConn = NULL;
  93                 };
  94                 return NULL;
  95         }
  96
  97         /* Create the first part of the query using the first parameter/value pairs we just extracted
  98            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
  99         op = strchr(newparam, ' ') ? "" : " =";
 100
 101         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 102         if (pgerror) {
 103                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 104                 va_end(ap);
 105                 return NULL;
 106         }
 107
 108         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
 109                          escapebuf);
 110         while ((newparam = va_arg(ap, const char *))) {
 111                 newval = va_arg(ap, const char *);
 112                 if (!strchr(newparam, ' '))
 113                         op = " =";
 114                 else
 115                         op = "";
 116
 117                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 118                 if (pgerror) {
 119                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 120                         va_end(ap);
 121                         return NULL;
 122                 }
 123
 124                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
 125                                  op, escapebuf);
 126         }
 127         va_end(ap);
 128
 129         /* We now have our complete statement; Lets connect to the server and execute it. */
 130         ast_mutex_lock(&pgsql_lock);
 131         if (!pgsql_reconnect(database)) {
 132                 ast_mutex_unlock(&pgsql_lock);
 133                 return NULL;
 134         }
 135
 136         if (!(result = PQexec(pgsqlConn, sql))) {
 137                 ast_log(LOG_WARNING,
 138                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 139                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 140                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 141                 ast_mutex_unlock(&pgsql_lock);
 142                 return NULL;
 143         } else {
 144                 ExecStatusType result_status = PQresultStatus(result);
 145                 if (result_status != PGRES_COMMAND_OK
 146                         && result_status != PGRES_TUPLES_OK
 147                         && result_status != PGRES_NONFATAL_ERROR) {
 148                         ast_log(LOG_WARNING,
 149                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 150                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 151                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 152                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 153                         ast_mutex_unlock(&pgsql_lock);
 154                         return NULL;
 155                 }
 156         }
 157
 158         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
 159
 160         if ((num_rows = PQntuples(result)) > 0) {
 161                 int i = 0;
 162                 int rowIndex = 0;
 163                 int numFields = PQnfields(result);
 164                 char **fieldnames = NULL;
 165
 166                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
 167
 168                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
 169                         ast_mutex_unlock(&pgsql_lock);
 170                         PQclear(result);
 171                         return NULL;
 172                 }
 173                 for (i = 0; i < numFields; i++)
 174                         fieldnames[i] = PQfname(result, i);
 175                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 176                         for (i = 0; i < numFields; i++) {
 177                                 stringp = PQgetvalue(result, rowIndex, i);
 178                                 while (stringp) {
 179                                         chunk = strsep(&stringp, ";");
 180                                         if (!ast_strlen_zero(ast_strip(chunk))) {
 181                                                 if (prev) {
 182                                                         prev->next = ast_variable_new(fieldnames[i], chunk, "");
 183                                                         if (prev->next) {
 184                                                                 prev = prev->next;
 185                                                         }
 186                                                 } else {
 187                                                         prev = var = ast_variable_new(fieldnames[i], chunk, "");
 188                                                 }
 189                                         }
 190                                 }
 191                         }
 192                 }
 193                 ast_free(fieldnames);
 194         } else {
 195                 ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s.\n", table);
 196         }
 197
 198         ast_mutex_unlock(&pgsql_lock);
 199         PQclear(result);
 200
 201         return var;
 202 }
 203
 204 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
 205 {
 206         PGresult *result = NULL;
 207         int num_rows = 0, pgerror;
 208         char sql[256], escapebuf[513];
 209         const char *initfield = NULL;
 210         char *stringp;
 211         char *chunk;
 212         char *op;
 213         const char *newparam, *newval;
 214         struct ast_variable *var = NULL;
 215         struct ast_config *cfg = NULL;
 216         struct ast_category *cat = NULL;
 217
 218         if (!table) {
 219                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 220                 return NULL;
 221         }
 222
 223         if (!(cfg = ast_config_new()))
 224                 return NULL;
 225
 226         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 227         newparam = va_arg(ap, const char *);
 228         newval = va_arg(ap, const char *);
 229         if (!newparam || !newval) {
 230                 ast_log(LOG_WARNING,
 231                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 232                 if (pgsqlConn) {
 233                         PQfinish(pgsqlConn);
 234                         pgsqlConn = NULL;
 235                 };
 236                 return NULL;
 237         }
 238
 239         initfield = ast_strdupa(newparam);
 240         if ((op = strchr(initfield, ' '))) {
 241                 *op = '\0';
 242         }
 243
 244         /* Create the first part of the query using the first parameter/value pairs we just extracted
 245            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 246
 247         if (!strchr(newparam, ' '))
 248                 op = " =";
 249         else
 250                 op = "";
 251
 252         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 253         if (pgerror) {
 254                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 255                 va_end(ap);
 256                 return NULL;
 257         }
 258
 259         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
 260                          escapebuf);
 261         while ((newparam = va_arg(ap, const char *))) {
 262                 newval = va_arg(ap, const char *);
 263                 if (!strchr(newparam, ' '))
 264                         op = " =";
 265                 else
 266                         op = "";
 267
 268                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 269                 if (pgerror) {
 270                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 271                         va_end(ap);
 272                         return NULL;
 273                 }
 274
 275                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
 276                                  op, escapebuf);
 277         }
 278
 279         if (initfield) {
 280                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
 281         }
 282
 283         va_end(ap);
 284
 285         /* We now have our complete statement; Lets connect to the server and execute it. */
 286         ast_mutex_lock(&pgsql_lock);
 287         if (!pgsql_reconnect(database)) {
 288                 ast_mutex_unlock(&pgsql_lock);
 289                 return NULL;
 290         }
 291
 292         if (!(result = PQexec(pgsqlConn, sql))) {
 293                 ast_log(LOG_WARNING,
 294                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 295                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 296                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 297                 ast_mutex_unlock(&pgsql_lock);
 298                 return NULL;
 299         } else {
 300                 ExecStatusType result_status = PQresultStatus(result);
 301                 if (result_status != PGRES_COMMAND_OK
 302                         && result_status != PGRES_TUPLES_OK
 303                         && result_status != PGRES_NONFATAL_ERROR) {
 304                         ast_log(LOG_WARNING,
 305                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 306                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 307                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 308                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 309                         ast_mutex_unlock(&pgsql_lock);
 310                         return NULL;
 311                 }
 312         }
 313
 314         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
 315
 316         if ((num_rows = PQntuples(result)) > 0) {
 317                 int numFields = PQnfields(result);
 318                 int i = 0;
 319                 int rowIndex = 0;
 320                 char **fieldnames = NULL;
 321
 322                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
 323
 324                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
 325                         ast_mutex_unlock(&pgsql_lock);
 326                         PQclear(result);
 327                         return NULL;
 328                 }
 329                 for (i = 0; i < numFields; i++)
 330                         fieldnames[i] = PQfname(result, i);
 331
 332                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 333                         var = NULL;
 334                         if (!(cat = ast_category_new("","",99999)))
 335                                 continue;
 336                         for (i = 0; i < numFields; i++) {
 337                                 stringp = PQgetvalue(result, rowIndex, i);
 338                                 while (stringp) {
 339                                         chunk = strsep(&stringp, ";");
 340                                         if (!ast_strlen_zero(ast_strip(chunk))) {
 341                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
 342                                                         ast_category_rename(cat, chunk);
 343                                                 }
 344                                                 var = ast_variable_new(fieldnames[i], chunk, "");
 345                                                 ast_variable_append(cat, var);
 346                                         }
 347                                 }
 348                         }
 349                         ast_category_append(cfg, cat);
 350                 }
 351                 ast_free(fieldnames);
 352         } else {
 353                 ast_log(LOG_WARNING,
 354                                 "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
 355         }
 356
 357         ast_mutex_unlock(&pgsql_lock);
 358         PQclear(result);
 359
 360         return cfg;
 361 }
 362
 363 static int update_pgsql(const char *database, const char *table, const char *keyfield,
 364                                                 const char *lookup, va_list ap)
 365 {
 366         PGresult *result = NULL;
 367         int numrows = 0, pgerror;
 368         char sql[256], escapebuf[513];
 369         const char *newparam, *newval;
 370
 371         if (!table) {
 372                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 373                 return -1;
 374         }
 375
 376         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 377         newparam = va_arg(ap, const char *);
 378         newval = va_arg(ap, const char *);
 379         if (!newparam || !newval) {
 380                 ast_log(LOG_WARNING,
 381                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 382                 if (pgsqlConn) {
 383                         PQfinish(pgsqlConn);
 384                         pgsqlConn = NULL;
 385                 };
 386                 return -1;
 387         }
 388
 389         /* Create the first part of the query using the first parameter/value pairs we just extracted
 390            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 391
 392         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 393         if (pgerror) {
 394                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 395                 va_end(ap);
 396                 return -1;
 397         }
 398         snprintf(sql, sizeof(sql), "UPDATE %s SET %s = '%s'", table, newparam, escapebuf);
 399
 400         while ((newparam = va_arg(ap, const char *))) {
 401                 newval = va_arg(ap, const char *);
 402
 403                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 404                 if (pgerror) {
 405                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 406                         va_end(ap);
 407                         return -1;
 408                 }
 409
 410                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s = '%s'", newparam,
 411                                  escapebuf);
 412         }
 413         va_end(ap);
 414
 415         PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
 416         if (pgerror) {
 417                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
 418                 va_end(ap);
 419                 return -1;
 420         }
 421
 422         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s = '%s'", keyfield,
 423                          escapebuf);
 424
 425         ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", sql);
 426
 427         /* We now have our complete statement; Lets connect to the server and execute it. */
 428         ast_mutex_lock(&pgsql_lock);
 429         if (!pgsql_reconnect(database)) {
 430                 ast_mutex_unlock(&pgsql_lock);
 431                 return -1;
 432         }
 433
 434         if (!(result = PQexec(pgsqlConn, sql))) {
 435                 ast_log(LOG_WARNING,
 436                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 437                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 438                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 439                 ast_mutex_unlock(&pgsql_lock);
 440                 return -1;
 441         } else {
 442                 ExecStatusType result_status = PQresultStatus(result);
 443                 if (result_status != PGRES_COMMAND_OK
 444                         && result_status != PGRES_TUPLES_OK
 445                         && result_status != PGRES_NONFATAL_ERROR) {
 446                         ast_log(LOG_WARNING,
 447                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 448                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 449                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 450                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 451                         ast_mutex_unlock(&pgsql_lock);
 452                         return -1;
 453                 }
 454         }
 455
 456         numrows = atoi(PQcmdTuples(result));
 457         ast_mutex_unlock(&pgsql_lock);
 458
 459         ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, table);
 460
 461         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
 462          * An integer greater than zero indicates the number of rows affected
 463          * Zero indicates that no records were updated
 464          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 465          */
 466
 467         if (numrows >= 0)
 468                 return (int) numrows;
 469
 470         return -1;
 471 }
 472
 473 static int store_pgsql(const char *database, const char *table, va_list ap)
 474 {
 475         PGresult *result = NULL;
 476         Oid insertid;
 477         char sql[256];
 478         char params[256];
 479         char vals[256];
 480         char buf[256];
 481         int pgresult;
 482         const char *newparam, *newval;
 483
 484         if (!table) {
 485                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 486                 return -1;
 487         }
 488
 489         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 490         newparam = va_arg(ap, const char *);
 491         newval = va_arg(ap, const char *);
 492         if (!newparam || !newval) {
 493                 ast_log(LOG_WARNING,
 494                                 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
 495                 if (pgsqlConn) {
 496                         PQfinish(pgsqlConn);
 497                         pgsqlConn = NULL;
 498                 };
 499                 return -1;
 500         }
 501
 502         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
 503         ast_mutex_lock(&pgsql_lock);
 504         if (!pgsql_reconnect(database)) {
 505                 ast_mutex_unlock(&pgsql_lock);
 506                 return -1;
 507         }
 508
 509         /* Create the first part of the query using the first parameter/value pairs we just extracted
 510            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 511         PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
 512         snprintf(params, sizeof(params), "%s", buf);
 513         PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
 514         snprintf(vals, sizeof(vals), "'%s'", buf);
 515         while ((newparam = va_arg(ap, const char *))) {
 516                 newval = va_arg(ap, const char *);
 517                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
 518                 snprintf(params + strlen(params), sizeof(params) - strlen(params), ", %s", buf);
 519                 PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
 520                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", '%s'", buf);
 521         }
 522         va_end(ap);
 523         snprintf(sql, sizeof(sql), "INSERT INTO (%s) VALUES (%s)", params, vals);
 524
 525         ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", sql);
 526
 527         if (!(result = PQexec(pgsqlConn, sql))) {
 528                 ast_log(LOG_WARNING,
 529                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 530                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 531                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 532                 ast_mutex_unlock(&pgsql_lock);
 533                 return -1;
 534         } else {
 535                 ExecStatusType result_status = PQresultStatus(result);
 536                 if (result_status != PGRES_COMMAND_OK
 537                         && result_status != PGRES_TUPLES_OK
 538                         && result_status != PGRES_NONFATAL_ERROR) {
 539                         ast_log(LOG_WARNING,
 540                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 541                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 542                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 543                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 544                         ast_mutex_unlock(&pgsql_lock);
 545                         return -1;
 546                 }
 547         }
 548
 549         insertid = PQoidValue(result);
 550         ast_mutex_unlock(&pgsql_lock);
 551
 552         ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
 553
 554         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
 555          * An integer greater than zero indicates the number of rows affected
 556          * Zero indicates that no records were updated
 557          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 558          */
 559
 560         if (insertid >= 0)
 561                 return (int) insertid;
 562
 563         return -1;
 564 }
 565
 566 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
 567 {
 568         PGresult *result = NULL;
 569         int numrows = 0;
 570         int pgresult;
 571         char sql[256];
 572         char buf[256], buf2[256];
 573         const char *newparam, *newval;
 574
 575         if (!table) {
 576                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
 577                 return -1;
 578         }
 579
 580         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 581         /*newparam = va_arg(ap, const char *);
 582         newval = va_arg(ap, const char *);
 583         if (!newparam || !newval) {*/
 584         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
 585                 ast_log(LOG_WARNING,
 586                                 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
 587                 if (pgsqlConn) {
 588                         PQfinish(pgsqlConn);
 589                         pgsqlConn = NULL;
 590                 };
 591                 return -1;
 592         }
 593
 594         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
 595         ast_mutex_lock(&pgsql_lock);
 596         if (!pgsql_reconnect(database)) {
 597                 ast_mutex_unlock(&pgsql_lock);
 598                 return -1;
 599         }
 600
 601
 602         /* Create the first part of the query using the first parameter/value pairs we just extracted
 603            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 604
 605         PQescapeStringConn(pgsqlConn, buf, keyfield, sizeof(keyfield), &pgresult);
 606         PQescapeStringConn(pgsqlConn, buf2, lookup, sizeof(lookup), &pgresult);
 607         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE %s = '%s'", table, buf, buf2);
 608         while ((newparam = va_arg(ap, const char *))) {
 609                 newval = va_arg(ap, const char *);
 610                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
 611                 PQescapeStringConn(pgsqlConn, buf2, newval, sizeof(newval), &pgresult);
 612                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s = '%s'", buf, buf2);
 613         }
 614         va_end(ap);
 615
 616         ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", sql);
 617
 618         if (!(result = PQexec(pgsqlConn, sql))) {
 619                 ast_log(LOG_WARNING,
 620                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 621                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 622                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 623                 ast_mutex_unlock(&pgsql_lock);
 624                 return -1;
 625         } else {
 626                 ExecStatusType result_status = PQresultStatus(result);
 627                 if (result_status != PGRES_COMMAND_OK
 628                         && result_status != PGRES_TUPLES_OK
 629                         && result_status != PGRES_NONFATAL_ERROR) {
 630                         ast_log(LOG_WARNING,
 631                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 632                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 633                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 634                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 635                         ast_mutex_unlock(&pgsql_lock);
 636                         return -1;
 637                 }
 638         }
 639
 640         numrows = atoi(PQcmdTuples(result));
 641         ast_mutex_unlock(&pgsql_lock);
 642
 643         ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
 644
 645         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
 646          * An integer greater than zero indicates the number of rows affected
 647          * Zero indicates that no records were updated
 648          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 649          */
 650
 651         if (numrows >= 0)
 652                 return (int) numrows;
 653
 654         return -1;
 655 }
 656
 657
 658 static struct ast_config *config_pgsql(const char *database, const char *table,
 659                                                                            const char *file, struct ast_config *cfg,
 660                                                                            struct ast_flags flags, const char *suggested_incl, const char *who_asked)
 661 {
 662         PGresult *result = NULL;
 663         long num_rows;
 664         struct ast_variable *new_v;
 665         struct ast_category *cur_cat = NULL;
 666         char sqlbuf[1024] = "";
 667         char *sql = sqlbuf;
 668         size_t sqlleft = sizeof(sqlbuf);
 669         char last[80] = "";
 670         int last_cat_metric = 0;
 671
 672         last[0] = '\0';
 673
 674         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
 675                 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
 676                 return NULL;
 677         }
 678
 679         ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
 680         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
 681         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
 682
 683         ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", sqlbuf);
 684
 685         /* We now have our complete statement; Lets connect to the server and execute it. */
 686         ast_mutex_lock(&pgsql_lock);
 687         if (!pgsql_reconnect(database)) {
 688                 ast_mutex_unlock(&pgsql_lock);
 689                 return NULL;
 690         }
 691
 692         if (!(result = PQexec(pgsqlConn, sqlbuf))) {
 693                 ast_log(LOG_WARNING,
 694                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 695                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 696                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
 697                 ast_mutex_unlock(&pgsql_lock);
 698                 return NULL;
 699         } else {
 700                 ExecStatusType result_status = PQresultStatus(result);
 701                 if (result_status != PGRES_COMMAND_OK
 702                         && result_status != PGRES_TUPLES_OK
 703                         && result_status != PGRES_NONFATAL_ERROR) {
 704                         ast_log(LOG_WARNING,
 705                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
 706                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
 707                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
 708                                                 PQresultErrorMessage(result), PQresStatus(result_status));
 709                         ast_mutex_unlock(&pgsql_lock);
 710                         return NULL;
 711                 }
 712         }
 713
 714         if ((num_rows = PQntuples(result)) > 0) {
 715                 int rowIndex = 0;
 716
 717                 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
 718
 719                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 720                         char *field_category = PQgetvalue(result, rowIndex, 0);
 721                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
 722                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
 723                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
 724                         if (!strcmp(field_var_name, "#include")) {
 725                                 if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
 726                                         PQclear(result);
 727                                         ast_mutex_unlock(&pgsql_lock);
 728                                         return NULL;
 729                                 }
 730                                 continue;
 731                         }
 732
 733                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
 734                                 cur_cat = ast_category_new(field_category, "", 99999);
 735                                 if (!cur_cat)
 736                                         break;
 737                                 strcpy(last, field_category);
 738                                 last_cat_metric = atoi(field_cat_metric);
 739                                 ast_category_append(cfg, cur_cat);
 740                         }
 741                         new_v = ast_variable_new(field_var_name, field_var_val, "");
 742                         ast_variable_append(cur_cat, new_v);
 743                 }
 744         } else {
 745                 ast_log(LOG_WARNING,
 746                                 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
 747         }
 748
 749         PQclear(result);
 750         ast_mutex_unlock(&pgsql_lock);
 751
 752         return cfg;
 753 }
 754
 755 static struct ast_config_engine pgsql_engine = {
 756         .name = "pgsql",
 757         .load_func = config_pgsql,
 758         .realtime_func = realtime_pgsql,
 759         .realtime_multi_func = realtime_multi_pgsql,
 760         .store_func = store_pgsql,
 761         .destroy_func = destroy_pgsql,
 762         .update_func = update_pgsql
 763 };
 764
 765 static int load_module(void)
 766 {
 767         if(!parse_config(0))
 768                 return AST_MODULE_LOAD_DECLINE;
 769
 770         ast_config_engine_register(&pgsql_engine);
 771         ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
 772         ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
 773
 774         return 0;
 775 }
 776
 777 static int unload_module(void)
 778 {
 779         /* Acquire control before doing anything to the module itself. */
 780         ast_mutex_lock(&pgsql_lock);
 781
 782         if (pgsqlConn) {
 783                 PQfinish(pgsqlConn);
 784                 pgsqlConn = NULL;
 785         }
 786         ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
 787         ast_config_engine_deregister(&pgsql_engine);
 788         ast_verb(1, "PostgreSQL RealTime unloaded.\n");
 789
 790         /* Unlock so something else can destroy the lock. */
 791         ast_mutex_unlock(&pgsql_lock);
 792
 793         return 0;
 794 }
 795
 796 static int reload(void)
 797 {
 798         parse_config(1);
 799
 800         return 0;
 801 }
 802
 803 static int parse_config(int reload)
 804 {
 805         struct ast_config *config;
 806         const char *s;
 807         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
 808
 809         if ((config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags)) == CONFIG_STATUS_FILEUNCHANGED)
 810                 return 0;
 811
 812         if (!config) {
 813                 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
 814                 return 0;
 815         }
 816
 817         ast_mutex_lock(&pgsql_lock);
 818
 819         if (pgsqlConn) {
 820                 PQfinish(pgsqlConn);
 821                 pgsqlConn = NULL;
 822         }
 823
 824         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
 825                 ast_log(LOG_WARNING,
 826                                 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
 827                 strcpy(dbuser, "asterisk");
 828         } else {
 829                 ast_copy_string(dbuser, s, sizeof(dbuser));
 830         }
 831
 832         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
 833                 ast_log(LOG_WARNING,
 834                                 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
 835                 strcpy(dbpass, "asterisk");
 836         } else {
 837                 ast_copy_string(dbpass, s, sizeof(dbpass));
 838         }
 839
 840         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
 841                 ast_log(LOG_WARNING,
 842                                 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
 843                 dbhost[0] = '\0';
 844         } else {
 845                 ast_copy_string(dbhost, s, sizeof(dbhost));
 846         }
 847
 848         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
 849                 ast_log(LOG_WARNING,
 850                                 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
 851                 strcpy(dbname, "asterisk");
 852         } else {
 853                 ast_copy_string(dbname, s, sizeof(dbname));
 854         }
 855
 856         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
 857                 ast_log(LOG_WARNING,
 858                                 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
 859                 dbport = 5432;
 860         } else {
 861                 dbport = atoi(s);
 862         }
 863
 864         if (!ast_strlen_zero(dbhost)) {
 865                 /* No socket needed */
 866         } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
 867                 ast_log(LOG_WARNING,
 868                                 "PostgreSQL RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
 869                 strcpy(dbsock, "/tmp/pgsql.sock");
 870         } else {
 871                 ast_copy_string(dbsock, s, sizeof(dbsock));
 872         }
 873         ast_config_destroy(config);
 874
 875         if (option_debug) {
 876                 if (!ast_strlen_zero(dbhost)) {
 877                         ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
 878                         ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
 879                 } else {
 880                         ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
 881                 }
 882                 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
 883                 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
 884                 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
 885         }
 886
 887         if (!pgsql_reconnect(NULL)) {
 888                 ast_log(LOG_WARNING,
 889                                 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
 890                 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
 891         }
 892
 893         ast_verb(2, "PostgreSQL RealTime reloaded.\n");
 894
 895         /* Done reloading. Release lock so others can now use driver. */
 896         ast_mutex_unlock(&pgsql_lock);
 897
 898         return 1;
 899 }
 900
 901 static int pgsql_reconnect(const char *database)
 902 {
 903         char my_database[50];
 904
 905         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
 906
 907         /* mutex lock should have been locked before calling this function. */
 908
 909         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
 910                 PQfinish(pgsqlConn);
 911                 pgsqlConn = NULL;
 912         }
 913
 914         /* DB password can legitimately be 0-length */
 915         if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
 916                 struct ast_str *connInfo = ast_str_create(32);
 917
 918                 ast_str_set(&connInfo, 0, "host=%s port=%d dbname=%s user=%s",
 919                         dbhost, dbport, my_database, dbuser);
 920                 if (!ast_strlen_zero(dbpass))
 921                         ast_str_append(&connInfo, 0, " password=%s", dbpass);
 922
 923                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
 924                 pgsqlConn = PQconnectdb(connInfo->str);
 925                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
 926                 ast_free(connInfo);
 927                 connInfo = NULL;
 928
 929                 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
 930                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
 931                         ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
 932                         connect_time = time(NULL);
 933                         return 1;
 934                 } else {
 935                         ast_log(LOG_ERROR,
 936                                         "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
 937                                         dbname, dbhost, PQresultErrorMessage(NULL));
 938                         return 0;
 939                 }
 940         } else {
 941                 ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
 942                 return 1;
 943         }
 944 }
 945
 946 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
 947 {
 948         char status[256], status2[100] = "";
 949         int ctime = time(NULL) - connect_time;
 950
 951         switch (cmd) {
 952         case CLI_INIT:
 953                 e->command = "realtime pgsql status";
 954                 e->usage =
 955                         "Usage: realtime pgsql status\n"
 956                         "       Shows connection information for the PostgreSQL RealTime driver\n";
 957                 return NULL;
 958         case CLI_GENERATE:
 959                 return NULL;
 960         }
 961
 962         if (a->argc != 3)
 963                 return CLI_SHOWUSAGE;
 964
 965         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
 966                 if (!ast_strlen_zero(dbhost))
 967                         snprintf(status, 255, "Connected to %s@%s, port %d", dbname, dbhost, dbport);
 968                 else if (!ast_strlen_zero(dbsock))
 969                         snprintf(status, 255, "Connected to %s on socket file %s", dbname, dbsock);
 970                 else
 971                         snprintf(status, 255, "Connected to %s@%s", dbname, dbhost);
 972
 973                 if (!ast_strlen_zero(dbuser))
 974                         snprintf(status2, 99, " with username %s", dbuser);
 975
 976                 if (ctime > 31536000)
 977                         ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
 978                                         status, status2, ctime / 31536000, (ctime % 31536000) / 86400,
 979                                         (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
 980                 else if (ctime > 86400)
 981                         ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
 982                                         status2, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
 983                                         ctime % 60);
 984                 else if (ctime > 3600)
 985                         ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, status2,
 986                                         ctime / 3600, (ctime % 3600) / 60, ctime % 60);
 987                 else if (ctime > 60)
 988                         ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, status2, ctime / 60,
 989                                         ctime % 60);
 990                 else
 991                         ast_cli(a->fd, "%s%s for %d seconds.\n", status, status2, ctime);
 992
 993                 return CLI_SUCCESS;
 994         } else {
 995                 return CLI_FAILURE;
 996         }
 997 }
 998
 999 /* needs usecount semantics defined */
1000 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
1001                 .load = load_module,
1002                 .unload = unload_module,
1003                 .reload = reload
1004                );