Fri Jun 19 12:09:50 2009

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

Generated on Fri Jun 19 12:09:51 2009 for Asterisk - the Open Source PBX by  doxygen 1.4.7