Wed Aug 18 22:33:55 2010

Asterisk developer's documentation


res_config_pgsql.c

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

Generated on Wed Aug 18 22:33:55 2010 for Asterisk - the Open Source PBX by  doxygen 1.4.7