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 <stdlib.h>
  34 #include <stdio.h>
  35 #include <string.h>
  36 #include <libpq-fe.h>                   /* PostgreSQL */
  37
  38 #include "asterisk/file.h"
  39 #include "asterisk/logger.h"
  40 #include "asterisk/channel.h"
  41 #include "asterisk/pbx.h"
  42 #include "asterisk/config.h"
  43 #include "asterisk/module.h"
  44 #include "asterisk/lock.h"
  45 #include "asterisk/options.h"
  46 #include "asterisk/utils.h"
  47 #include "asterisk/cli.h"
  48
  49 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
  50
  51 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
  52
  53 PGconn *pgsqlConn = NULL;
  54
  55 #define MAX_DB_OPTION_SIZE 64
  56
  57 static char dbhost[MAX_DB_OPTION_SIZE] = "";
  58 static char dbuser[MAX_DB_OPTION_SIZE] = "";
  59 static char dbpass[MAX_DB_OPTION_SIZE] = "";
  60 static char dbname[MAX_DB_OPTION_SIZE] = "";
  61 static char dbsock[MAX_DB_OPTION_SIZE] = "";
  62 static int dbport = 5432;
  63 static time_t connect_time = 0;
  64
  65 static int parse_config(void);
  66 static int pgsql_reconnect(const char *database);
  67 static int realtime_pgsql_status(int fd, int argc, char **argv);
  68
  69 static char cli_realtime_pgsql_status_usage[] =
  70         "Usage: realtime pgsql status\n"
  71         "       Shows connection information for the Postgresql RealTime driver\n";
  72
  73 static struct ast_cli_entry cli_realtime[] = {
  74         { { "realtime", "pgsql", "status", NULL },
  75         realtime_pgsql_status, "Shows connection information for the Postgresql RealTime driver",
  76         cli_realtime_pgsql_status_usage },
  77 };
  78
  79 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
  80 {
  81         PGresult *result = NULL;
  82         int num_rows = 0, pgerror;
  83         char sql[256], escapebuf[513];
  84         char *stringp;
  85         char *chunk;
  86         char *op;
  87         const char *newparam, *newval;
  88         struct ast_variable *var = NULL, *prev = NULL;
  89
  90         if (!table) {
  91                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
  92                 return NULL;
  93         }
  94
  95         /* Get the first parameter and first value in our list of passed paramater/value pairs */
  96         newparam = va_arg(ap, const char *);
  97         newval = va_arg(ap, const char *);
  98         if (!newparam || !newval) {
  99                 ast_log(LOG_WARNING,
 100                                 "Postgresql RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 101                 if (pgsqlConn) {
 102                         PQfinish(pgsqlConn);
 103                         pgsqlConn = NULL;
 104                 };
 105                 return NULL;
 106         }
 107
 108         /* Create the first part of the query using the first parameter/value pairs we just extracted
 109            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 110         op = strchr(newparam, ' ') ? "" : " =";
 111
 112         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 113         if (pgerror) {
 114                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 115                 va_end(ap);
 116                 return NULL;
 117         }
 118
 119         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
 120                          escapebuf);
 121         while ((newparam = va_arg(ap, const char *))) {
 122                 newval = va_arg(ap, const char *);
 123                 if (!strchr(newparam, ' '))
 124                         op = " =";
 125                 else
 126                         op = "";
 127
 128                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 129                 if (pgerror) {
 130                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 131                         va_end(ap);
 132                         return NULL;
 133                 }
 134
 135                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
 136                                  op, escapebuf);
 137         }
 138         va_end(ap);
 139
 140         /* We now have our complete statement; Lets connect to the server and execute it. */
 141         ast_mutex_lock(&pgsql_lock);
 142         if (!pgsql_reconnect(database)) {
 143                 ast_mutex_unlock(&pgsql_lock);
 144                 return NULL;
 145         }
 146
 147         if (!(result = PQexec(pgsqlConn, sql))) {
 148                 ast_log(LOG_WARNING,
 149                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
 150                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 151                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s\n",
 152                                 PQerrorMessage(pgsqlConn));
 153                 ast_mutex_unlock(&pgsql_lock);
 154                 return NULL;
 155         } else {
 156                 ExecStatusType result_status = PQresultStatus(result);
 157                 if (result_status != PGRES_COMMAND_OK
 158                         && result_status != PGRES_TUPLES_OK
 159                         && result_status != PGRES_NONFATAL_ERROR) {
 160                         ast_log(LOG_WARNING,
 161                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
 162                         ast_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 163                         ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s (%s)\n",
 164                                         PQresultErrorMessage(result), PQresStatus(result_status));
 165                         ast_mutex_unlock(&pgsql_lock);
 166                         return NULL;
 167                 }
 168         }
 169
 170         ast_log(LOG_DEBUG, "1Postgresql RealTime: Result=%p Query: %s\n", result, sql);
 171
 172         if ((num_rows = PQntuples(result)) > 0) {
 173                 int i = 0;
 174                 int rowIndex = 0;
 175                 int numFields = PQnfields(result);
 176                 char **fieldnames = NULL;
 177
 178                 ast_log(LOG_DEBUG, "Postgresql RealTime: Found %d rows.\n", num_rows);
 179
 180                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
 181                         ast_mutex_unlock(&pgsql_lock);
 182                         PQclear(result);
 183                         return NULL;
 184                 }
 185                 for (i = 0; i < numFields; i++)
 186                         fieldnames[i] = PQfname(result, i);
 187                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 188                         for (i = 0; i < numFields; i++) {
 189                                 stringp = PQgetvalue(result, rowIndex, i);
 190                                 while (stringp) {
 191                                         chunk = strsep(&stringp, ";");
 192                                         if (chunk && !ast_strlen_zero(ast_strip(chunk))) {
 193                                                 if (prev) {
 194                                                         prev->next = ast_variable_new(fieldnames[i], chunk);
 195                                                         if (prev->next) {
 196                                                                 prev = prev->next;
 197                                                         }
 198                                                 } else {
 199                                                         prev = var = ast_variable_new(fieldnames[i], chunk);
 200                                                 }
 201                                         }
 202                                 }
 203                         }
 204                 }
 205                 ast_free(fieldnames);
 206         } else {
 207                 ast_log(LOG_WARNING,
 208                                 "Postgresql RealTime: Could not find any rows in table %s.\n", table);
 209         }
 210
 211         ast_mutex_unlock(&pgsql_lock);
 212         PQclear(result);
 213
 214         return var;
 215 }
 216
 217 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
 218 {
 219         PGresult *result = NULL;
 220         int num_rows = 0, pgerror;
 221         char sql[256], escapebuf[513];
 222         const char *initfield = NULL;
 223         char *stringp;
 224         char *chunk;
 225         char *op;
 226         const char *newparam, *newval;
 227         struct ast_realloca ra;
 228         struct ast_variable *var = NULL;
 229         struct ast_config *cfg = NULL;
 230         struct ast_category *cat = NULL;
 231
 232         if (!table) {
 233                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
 234                 return NULL;
 235         }
 236
 237         memset(&ra, 0, sizeof(ra));
 238
 239         if (!(cfg = ast_config_new()))
 240                 return NULL;
 241
 242         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 243         newparam = va_arg(ap, const char *);
 244         newval = va_arg(ap, const char *);
 245         if (!newparam || !newval) {
 246                 ast_log(LOG_WARNING,
 247                                 "Postgresql RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 248                 if (pgsqlConn) {
 249                         PQfinish(pgsqlConn);
 250                         pgsqlConn = NULL;
 251                 };
 252                 return NULL;
 253         }
 254
 255         initfield = ast_strdupa(newparam);
 256         if ((op = strchr(initfield, ' '))) {
 257                 *op = '\0';
 258         }
 259
 260         /* Create the first part of the query using the first parameter/value pairs we just extracted
 261            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 262
 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, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
 276                          escapebuf);
 277         while ((newparam = va_arg(ap, const char *))) {
 278                 newval = va_arg(ap, const char *);
 279                 if (!strchr(newparam, ' '))
 280                         op = " =";
 281                 else
 282                         op = "";
 283
 284                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 285                 if (pgerror) {
 286                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 287                         va_end(ap);
 288                         return NULL;
 289                 }
 290
 291                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
 292                                  op, escapebuf);
 293         }
 294
 295         if (initfield) {
 296                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
 297         }
 298
 299         va_end(ap);
 300
 301         /* We now have our complete statement; Lets connect to the server and execute it. */
 302         ast_mutex_lock(&pgsql_lock);
 303         if (!pgsql_reconnect(database)) {
 304                 ast_mutex_unlock(&pgsql_lock);
 305                 return NULL;
 306         }
 307
 308         if (!(result = PQexec(pgsqlConn, sql))) {
 309                 ast_log(LOG_WARNING,
 310                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
 311                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 312                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s\n",
 313                                 PQerrorMessage(pgsqlConn));
 314                 ast_mutex_unlock(&pgsql_lock);
 315                 return NULL;
 316         } else {
 317                 ExecStatusType result_status = PQresultStatus(result);
 318                 if (result_status != PGRES_COMMAND_OK
 319                         && result_status != PGRES_TUPLES_OK
 320                         && result_status != PGRES_NONFATAL_ERROR) {
 321                         ast_log(LOG_WARNING,
 322                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
 323                         ast_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 324                         ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s (%s)\n",
 325                                         PQresultErrorMessage(result), PQresStatus(result_status));
 326                         ast_mutex_unlock(&pgsql_lock);
 327                         return NULL;
 328                 }
 329         }
 330
 331         ast_log(LOG_DEBUG, "2Postgresql RealTime: Result=%p Query: %s\n", result, sql);
 332
 333         if ((num_rows = PQntuples(result)) > 0) {
 334                 int numFields = PQnfields(result);
 335                 int i = 0;
 336                 int rowIndex = 0;
 337                 char **fieldnames = NULL;
 338
 339                 ast_log(LOG_DEBUG, "Postgresql RealTime: Found %d rows.\n", num_rows);
 340
 341                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
 342                         ast_mutex_unlock(&pgsql_lock);
 343                         PQclear(result);
 344                         return NULL;
 345                 }
 346                 for (i = 0; i < numFields; i++)
 347                         fieldnames[i] = PQfname(result, i);
 348
 349                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 350                         var = NULL;
 351                         if (!(cat = ast_category_new("")))
 352                                 continue;
 353                         for (i = 0; i < numFields; i++) {
 354                                 stringp = PQgetvalue(result, rowIndex, i);
 355                                 while (stringp) {
 356                                         chunk = strsep(&stringp, ";");
 357                                         if (chunk && !ast_strlen_zero(ast_strip(chunk))) {
 358                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
 359                                                         ast_category_rename(cat, chunk);
 360                                                 }
 361                                                 var = ast_variable_new(fieldnames[i], chunk);
 362                                                 ast_variable_append(cat, var);
 363                                         }
 364                                 }
 365                         }
 366                         ast_category_append(cfg, cat);
 367                 }
 368                 ast_free(fieldnames);
 369         } else {
 370                 ast_log(LOG_WARNING,
 371                                 "Postgresql RealTime: Could not find any rows in table %s.\n", table);
 372         }
 373
 374         ast_mutex_unlock(&pgsql_lock);
 375         PQclear(result);
 376
 377         return cfg;
 378 }
 379
 380 static int update_pgsql(const char *database, const char *table, const char *keyfield,
 381                                                 const char *lookup, va_list ap)
 382 {
 383         PGresult *result = NULL;
 384         int numrows = 0, pgerror;
 385         char sql[256], escapebuf[513];
 386         const char *newparam, *newval;
 387
 388         if (!table) {
 389                 ast_log(LOG_WARNING, "Postgresql RealTime: No table specified.\n");
 390                 return -1;
 391         }
 392
 393         /* Get the first parameter and first value in our list of passed paramater/value pairs */
 394         newparam = va_arg(ap, const char *);
 395         newval = va_arg(ap, const char *);
 396         if (!newparam || !newval) {
 397                 ast_log(LOG_WARNING,
 398                                 "Postgresql RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
 399                 if (pgsqlConn) {
 400                         PQfinish(pgsqlConn);
 401                         pgsqlConn = NULL;
 402                 };
 403                 return -1;
 404         }
 405
 406         /* Create the first part of the query using the first parameter/value pairs we just extracted
 407            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
 408
 409         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 410         if (pgerror) {
 411                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 412                 va_end(ap);
 413                 return -1;
 414         }
 415         snprintf(sql, sizeof(sql), "UPDATE %s SET %s = '%s'", table, newparam, escapebuf);
 416
 417         while ((newparam = va_arg(ap, const char *))) {
 418                 newval = va_arg(ap, const char *);
 419
 420                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
 421                 if (pgerror) {
 422                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
 423                         va_end(ap);
 424                         return -1;
 425                 }
 426
 427                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s = '%s'", newparam,
 428                                  escapebuf);
 429         }
 430         va_end(ap);
 431
 432         PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
 433         if (pgerror) {
 434                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
 435                 va_end(ap);
 436                 return -1;
 437         }
 438
 439         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s = '%s'", keyfield,
 440                          escapebuf);
 441
 442         ast_log(LOG_DEBUG, "Postgresql RealTime: Update SQL: %s\n", sql);
 443
 444         /* We now have our complete statement; Lets connect to the server and execute it. */
 445         ast_mutex_lock(&pgsql_lock);
 446         if (!pgsql_reconnect(database)) {
 447                 ast_mutex_unlock(&pgsql_lock);
 448                 return -1;
 449         }
 450
 451         if (!(result = PQexec(pgsqlConn, sql))) {
 452                 ast_log(LOG_WARNING,
 453                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
 454                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 455                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s\n",
 456                                 PQerrorMessage(pgsqlConn));
 457                 ast_mutex_unlock(&pgsql_lock);
 458                 return -1;
 459         } else {
 460                 ExecStatusType result_status = PQresultStatus(result);
 461                 if (result_status != PGRES_COMMAND_OK
 462                         && result_status != PGRES_TUPLES_OK
 463                         && result_status != PGRES_NONFATAL_ERROR) {
 464                         ast_log(LOG_WARNING,
 465                                         "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
 466                         ast_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 467                         ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s (%s)\n",
 468                                         PQresultErrorMessage(result), PQresStatus(result_status));
 469                         ast_mutex_unlock(&pgsql_lock);
 470                         return -1;
 471                 }
 472         }
 473
 474         numrows = atoi(PQcmdTuples(result));
 475         ast_mutex_unlock(&pgsql_lock);
 476
 477         ast_log(LOG_DEBUG, "Postgresql RealTime: Updated %d rows on table: %s\n", numrows,
 478                         table);
 479
 480         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
 481          * An integer greater than zero indicates the number of rows affected
 482          * Zero indicates that no records were updated
 483          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
 484          */
 485
 486         if (numrows >= 0)
 487                 return (int) numrows;
 488
 489         return -1;
 490 }
 491
 492 static struct ast_config *config_pgsql(const char *database, const char *table,
 493                                            const char *file, struct ast_config *cfg,
 494                                            int withcomments)
 495 {
 496         PGresult *result = NULL;
 497         long num_rows;
 498         struct ast_variable *new_v;
 499         struct ast_category *cur_cat = NULL;
 500         char sqlbuf[1024] = "";
 501         char *sql = sqlbuf;
 502         size_t sqlleft = sizeof(sqlbuf);
 503         char last[80] = "";
 504         int last_cat_metric = 0;
 505
 506         last[0] = '\0';
 507
 508         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
 509                 ast_log(LOG_WARNING, "Postgresql RealTime: Cannot configure myself.\n");
 510                 return NULL;
 511         }
 512
 513         ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
 514         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
 515         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
 516
 517         ast_log(LOG_DEBUG, "Postgresql RealTime: Static SQL: %s\n", sqlbuf);
 518
 519         /* We now have our complete statement; Lets connect to the server and execute it. */
 520         ast_mutex_lock(&pgsql_lock);
 521         if (!pgsql_reconnect(database)) {
 522                 ast_mutex_unlock(&pgsql_lock);
 523                 return NULL;
 524         }
 525
 526         if (!(result = PQexec(pgsqlConn, sqlbuf))) {
 527                 ast_log(LOG_WARNING,
 528                                 "Postgresql RealTime: Failed to query database. Check debug for more info.\n");
 529                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 530                 ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s\n",
 531                                 PQerrorMessage(pgsqlConn));
 532                 ast_mutex_unlock(&pgsql_lock);
 533                 return NULL;
 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_log(LOG_DEBUG, "Postgresql RealTime: Query: %s\n", sql);
 542                         ast_log(LOG_DEBUG, "Postgresql RealTime: Query Failed because: %s (%s)\n",
 543                                         PQresultErrorMessage(result), PQresStatus(result_status));
 544                         ast_mutex_unlock(&pgsql_lock);
 545                         return NULL;
 546                 }
 547         }
 548
 549         if ((num_rows = PQntuples(result)) > 0) {
 550                 int rowIndex = 0;
 551
 552                 ast_log(LOG_DEBUG, "Postgresql RealTime: Found %ld rows.\n", num_rows);
 553
 554                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
 555                         char *field_category = PQgetvalue(result, rowIndex, 0);
 556                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
 557                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
 558                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
 559                         if (!strcmp(field_var_name, "#include")) {
 560                                 if (!ast_config_internal_load(field_var_val, cfg, 0)) {
 561                                         PQclear(result);
 562                                         ast_mutex_unlock(&pgsql_lock);
 563                                         return NULL;
 564                                 }
 565                                 continue;
 566                         }
 567
 568                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
 569                                 cur_cat = ast_category_new(field_category);
 570                                 if (!cur_cat)
 571                                         break;
 572                                 strcpy(last, field_category);
 573                                 last_cat_metric = atoi(field_cat_metric);
 574                                 ast_category_append(cfg, cur_cat);
 575                         }
 576                         new_v = ast_variable_new(field_var_name, field_var_val);
 577                         ast_variable_append(cur_cat, new_v);
 578                 }
 579         } else {
 580                 ast_log(LOG_WARNING,
 581                                 "Postgresql RealTime: Could not find config '%s' in database.\n", file);
 582         }
 583
 584         PQclear(result);
 585         ast_mutex_unlock(&pgsql_lock);
 586
 587         return cfg;
 588 }
 589
 590 static struct ast_config_engine pgsql_engine = {
 591         .name = "pgsql",
 592         .load_func = config_pgsql,
 593         .realtime_func = realtime_pgsql,
 594         .realtime_multi_func = realtime_multi_pgsql,
 595         .update_func = update_pgsql
 596 };
 597
 598 static int load_module(void)
 599 {
 600         if(!parse_config())
 601                 return AST_MODULE_LOAD_DECLINE;
 602
 603         ast_mutex_lock(&pgsql_lock);
 604
 605         if (!pgsql_reconnect(NULL)) {
 606                 ast_log(LOG_WARNING,
 607                                 "Postgresql RealTime: Couldn't establish connection. Check debug.\n");
 608                 ast_log(LOG_DEBUG, "Postgresql RealTime: Cannot Connect: %s\n",
 609                                 PQerrorMessage(pgsqlConn));
 610         }
 611
 612         ast_config_engine_register(&pgsql_engine);
 613         if (option_verbose) {
 614                 ast_verbose("Postgresql RealTime driver loaded.\n");
 615         }
 616         ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
 617
 618         ast_mutex_unlock(&pgsql_lock);
 619
 620         return 0;
 621 }
 622
 623 static int unload_module(void)
 624 {
 625         /* Aquire control before doing anything to the module itself. */
 626         ast_mutex_lock(&pgsql_lock);
 627
 628         if (pgsqlConn) {
 629                 PQfinish(pgsqlConn);
 630                 pgsqlConn = NULL;
 631         };
 632         ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
 633         ast_config_engine_deregister(&pgsql_engine);
 634         if (option_verbose) {
 635                 ast_verbose("Postgresql RealTime unloaded.\n");
 636         }
 637
 638         ast_module_user_hangup_all();
 639
 640         /* Unlock so something else can destroy the lock. */
 641         ast_mutex_unlock(&pgsql_lock);
 642
 643         return 0;
 644 }
 645
 646 static int reload(void)
 647 {
 648         /* Aquire control before doing anything to the module itself. */
 649         ast_mutex_lock(&pgsql_lock);
 650
 651         if (pgsqlConn) {
 652                 PQfinish(pgsqlConn);
 653                 pgsqlConn = NULL;
 654         };
 655         parse_config();
 656
 657         if (!pgsql_reconnect(NULL)) {
 658                 ast_log(LOG_WARNING,
 659                                 "Postgresql RealTime: Couldn't establish connection. Check debug.\n");
 660                 ast_log(LOG_DEBUG, "Postgresql RealTime: Cannot Connect: %s\n",
 661                                 PQerrorMessage(pgsqlConn));
 662         }
 663
 664         ast_verbose(VERBOSE_PREFIX_2 "Postgresql RealTime reloaded.\n");
 665
 666         /* Done reloading. Release lock so others can now use driver. */
 667         ast_mutex_unlock(&pgsql_lock);
 668
 669         return 0;
 670 }
 671
 672 static int parse_config(void)
 673 {
 674         struct ast_config *config;
 675         const char *s;
 676
 677         config = ast_config_load(RES_CONFIG_PGSQL_CONF);
 678
 679         if (!config) {
 680                 ast_log(LOG_WARNING, "Unable to load config %s\n",RES_CONFIG_PGSQL_CONF);
 681                 return 0;
 682         }
 683         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
 684                 ast_log(LOG_WARNING,
 685                                 "Postgresql RealTime: No database user found, using 'asterisk' as default.\n");
 686                 strcpy(dbuser, "asterisk");
 687         } else {
 688                 ast_copy_string(dbuser, s, sizeof(dbuser));
 689         }
 690
 691         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
 692                 ast_log(LOG_WARNING,
 693                                 "Postgresql RealTime: No database password found, using 'asterisk' as default.\n");
 694                 strcpy(dbpass, "asterisk");
 695         } else {
 696                 ast_copy_string(dbpass, s, sizeof(dbpass));
 697         }
 698
 699         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
 700                 ast_log(LOG_WARNING,
 701                                 "Postgresql RealTime: No database host found, using localhost via socket.\n");
 702                 dbhost[0] = '\0';
 703         } else {
 704                 ast_copy_string(dbhost, s, sizeof(dbhost));
 705         }
 706
 707         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
 708                 ast_log(LOG_WARNING,
 709                                 "Postgresql RealTime: No database name found, using 'asterisk' as default.\n");
 710                 strcpy(dbname, "asterisk");
 711         } else {
 712                 ast_copy_string(dbname, s, sizeof(dbname));
 713         }
 714
 715         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
 716                 ast_log(LOG_WARNING,
 717                                 "Postgresql RealTime: No database port found, using 5432 as default.\n");
 718                 dbport = 5432;
 719         } else {
 720                 dbport = atoi(s);
 721         }
 722
 723         if (!ast_strlen_zero(dbhost)) {
 724                 /* No socket needed */
 725         } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
 726                 ast_log(LOG_WARNING,
 727                                 "Postgresql RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
 728                 strcpy(dbsock, "/tmp/pgsql.sock");
 729         } else {
 730                 ast_copy_string(dbsock, s, sizeof(dbsock));
 731         }
 732         ast_config_destroy(config);
 733
 734         if (!ast_strlen_zero(dbhost)) {
 735                 ast_log(LOG_DEBUG, "Postgresql RealTime Host: %s\n", dbhost);
 736                 ast_log(LOG_DEBUG, "Postgresql RealTime Port: %i\n", dbport);
 737         } else {
 738                 ast_log(LOG_DEBUG, "Postgresql RealTime Socket: %s\n", dbsock);
 739         }
 740         ast_log(LOG_DEBUG, "Postgresql RealTime User: %s\n", dbuser);
 741         ast_log(LOG_DEBUG, "Postgresql RealTime Password: %s\n", dbpass);
 742         ast_log(LOG_DEBUG, "Postgresql RealTime DBName: %s\n", dbname);
 743
 744         return 1;
 745 }
 746
 747 static int pgsql_reconnect(const char *database)
 748 {
 749         char my_database[50];
 750
 751         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
 752
 753         /* mutex lock should have been locked before calling this function. */
 754
 755         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
 756                 PQfinish(pgsqlConn);
 757                 pgsqlConn = NULL;
 758         }
 759
 760         if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(dbpass) && !ast_strlen_zero(my_database)) {
 761                 char *connInfo = NULL;
 762                 unsigned int size = 100 + strlen(dbhost)
 763                         + strlen(dbuser)
 764                         + strlen(dbpass)
 765                         + strlen(my_database);
 766
 767                 if (!(connInfo = ast_malloc(size)))
 768                         return 0;
 769
 770                 sprintf(connInfo, "host=%s port=%d dbname=%s user=%s password=%s",
 771                                         dbhost, dbport, my_database, dbuser, dbpass);
 772                 ast_log(LOG_DEBUG, "%u connInfo=%s\n", size, connInfo);
 773                 pgsqlConn = PQconnectdb(connInfo);
 774                 ast_log(LOG_DEBUG, "%u connInfo=%s\n", size, connInfo);
 775                 ast_free(connInfo);
 776                 connInfo = NULL;
 777                 ast_log(LOG_DEBUG, "pgsqlConn=%p\n", pgsqlConn);
 778                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
 779                         ast_log(LOG_DEBUG, "Postgresql RealTime: Successfully connected to database.\n");
 780                         connect_time = time(NULL);
 781                         return 1;
 782                 } else {
 783                         ast_log(LOG_ERROR,
 784                                         "Postgresql RealTime: Failed to connect database server %s on %s. Check debug for more info.\n",
 785                                         dbname, dbhost);
 786                         ast_log(LOG_DEBUG, "Postgresql RealTime: Cannot Connect: %s\n",
 787                                         PQresultErrorMessage(NULL));
 788                         return 0;
 789                 }
 790         } else {
 791                 ast_log(LOG_DEBUG, "Postgresql RealTime: Everything is fine.\n");
 792                 return 1;
 793         }
 794 }
 795
 796 static int realtime_pgsql_status(int fd, int argc, char **argv)
 797 {
 798         char status[256], status2[100] = "";
 799         int ctime = time(NULL) - connect_time;
 800
 801         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
 802                 if (!ast_strlen_zero(dbhost)) {
 803                         snprintf(status, 255, "Connected to %s@%s, port %d", dbname, dbhost, dbport);
 804                 } else if (!ast_strlen_zero(dbsock)) {
 805                         snprintf(status, 255, "Connected to %s on socket file %s", dbname, dbsock);
 806                 } else {
 807                         snprintf(status, 255, "Connected to %s@%s", dbname, dbhost);
 808                 }
 809
 810                 if (!ast_strlen_zero(dbuser)) {
 811                         snprintf(status2, 99, " with username %s", dbuser);
 812                 }
 813
 814                 if (ctime > 31536000) {
 815                         ast_cli(fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
 816                                         status, status2, ctime / 31536000, (ctime % 31536000) / 86400,
 817                                         (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
 818                 } else if (ctime > 86400) {
 819                         ast_cli(fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
 820                                         status2, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
 821                                         ctime % 60);
 822                 } else if (ctime > 3600) {
 823                         ast_cli(fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, status2,
 824                                         ctime / 3600, (ctime % 3600) / 60, ctime % 60);
 825                 } else if (ctime > 60) {
 826                         ast_cli(fd, "%s%s for %d minutes, %d seconds.\n", status, status2, ctime / 60,
 827                                         ctime % 60);
 828                 } else {
 829                         ast_cli(fd, "%s%s for %d seconds.\n", status, status2, ctime);
 830                 }
 831
 832                 return RESULT_SUCCESS;
 833         } else {
 834                 return RESULT_FAILURE;
 835         }
 836 }
 837
 838 /* needs usecount semantics defined */
 839 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
 840                 .load = load_module,
 841                 .unload = unload_module,
 842                 .reload = reload
 843                );