Thu Jul 9 13:40:39 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: 145123 $")
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 static char dbhost[MAX_DB_OPTION_SIZE] = "";
00053 static char dbuser[MAX_DB_OPTION_SIZE] = "";
00054 static char dbpass[MAX_DB_OPTION_SIZE] = "";
00055 static char dbname[MAX_DB_OPTION_SIZE] = "";
00056 static char dbsock[MAX_DB_OPTION_SIZE] = "";
00057 static int dbport = 5432;
00058 static time_t connect_time = 0;
00059 
00060 static int parse_config(int reload);
00061 static int pgsql_reconnect(const char *database);
00062 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
00063 
00064 static struct ast_cli_entry cli_realtime[] = {
00065    AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
00066 };
00067 
00068 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
00069 {
00070    PGresult *result = NULL;
00071    int num_rows = 0, pgerror;
00072    char sql[256], escapebuf[513];
00073    char *stringp;
00074    char *chunk;
00075    char *op;
00076    const char *newparam, *newval;
00077    struct ast_variable *var = NULL, *prev = NULL;
00078 
00079    if (!table) {
00080       ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00081       return NULL;
00082    }
00083 
00084    /* Get the first parameter and first value in our list of passed paramater/value pairs */
00085    newparam = va_arg(ap, const char *);
00086    newval = va_arg(ap, const char *);
00087    if (!newparam || !newval) {
00088       ast_log(LOG_WARNING,
00089             "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00090       if (pgsqlConn) {
00091          PQfinish(pgsqlConn);
00092          pgsqlConn = NULL;
00093       };
00094       return NULL;
00095    }
00096 
00097    /* Create the first part of the query using the first parameter/value pairs we just extracted
00098       If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
00099    op = strchr(newparam, ' ') ? "" : " =";
00100 
00101    PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00102    if (pgerror) {
00103       ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00104       va_end(ap);
00105       return NULL;
00106    }
00107 
00108    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
00109           escapebuf);
00110    while ((newparam = va_arg(ap, const char *))) {
00111       newval = va_arg(ap, const char *);
00112       if (!strchr(newparam, ' '))
00113          op = " =";
00114       else
00115          op = "";
00116 
00117       PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00118       if (pgerror) {
00119          ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00120          va_end(ap);
00121          return NULL;
00122       }
00123 
00124       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
00125              op, escapebuf);
00126    }
00127    va_end(ap);
00128 
00129    /* We now have our complete statement; Lets connect to the server and execute it. */
00130    ast_mutex_lock(&pgsql_lock);
00131    if (!pgsql_reconnect(database)) {
00132       ast_mutex_unlock(&pgsql_lock);
00133       return NULL;
00134    }
00135 
00136    if (!(result = PQexec(pgsqlConn, sql))) {
00137       ast_log(LOG_WARNING,
00138             "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00139       ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00140       ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00141       ast_mutex_unlock(&pgsql_lock);
00142       return NULL;
00143    } else {
00144       ExecStatusType result_status = PQresultStatus(result);
00145       if (result_status != PGRES_COMMAND_OK
00146          && result_status != PGRES_TUPLES_OK
00147          && result_status != PGRES_NONFATAL_ERROR) {
00148          ast_log(LOG_WARNING,
00149                "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00150          ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00151          ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00152                   PQresultErrorMessage(result), PQresStatus(result_status));
00153          ast_mutex_unlock(&pgsql_lock);
00154          return NULL;
00155       }
00156    }
00157 
00158    ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
00159 
00160    if ((num_rows = PQntuples(result)) > 0) {
00161       int i = 0;
00162       int rowIndex = 0;
00163       int numFields = PQnfields(result);
00164       char **fieldnames = NULL;
00165 
00166       ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
00167 
00168       if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
00169          ast_mutex_unlock(&pgsql_lock);
00170          PQclear(result);
00171          return NULL;
00172       }
00173       for (i = 0; i < numFields; i++)
00174          fieldnames[i] = PQfname(result, i);
00175       for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00176          for (i = 0; i < numFields; i++) {
00177             stringp = PQgetvalue(result, rowIndex, i);
00178             while (stringp) {
00179                chunk = strsep(&stringp, ";");
00180                if (!ast_strlen_zero(ast_strip(chunk))) {
00181                   if (prev) {
00182                      prev->next = ast_variable_new(fieldnames[i], chunk, "");
00183                      if (prev->next) {
00184                         prev = prev->next;
00185                      }
00186                   } else {
00187                      prev = var = ast_variable_new(fieldnames[i], chunk, "");
00188                   }
00189                }
00190             }
00191          }
00192       }
00193       ast_free(fieldnames);
00194    } else {
00195       ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s.\n", table);
00196    }
00197 
00198    ast_mutex_unlock(&pgsql_lock);
00199    PQclear(result);
00200 
00201    return var;
00202 }
00203 
00204 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
00205 {
00206    PGresult *result = NULL;
00207    int num_rows = 0, pgerror;
00208    char sql[256], escapebuf[513];
00209    const char *initfield = NULL;
00210    char *stringp;
00211    char *chunk;
00212    char *op;
00213    const char *newparam, *newval;
00214    struct ast_variable *var = NULL;
00215    struct ast_config *cfg = NULL;
00216    struct ast_category *cat = NULL;
00217 
00218    if (!table) {
00219       ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00220       return NULL;
00221    }
00222 
00223    if (!(cfg = ast_config_new()))
00224       return NULL;
00225 
00226    /* Get the first parameter and first value in our list of passed paramater/value pairs */
00227    newparam = va_arg(ap, const char *);
00228    newval = va_arg(ap, const char *);
00229    if (!newparam || !newval) {
00230       ast_log(LOG_WARNING,
00231             "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00232       if (pgsqlConn) {
00233          PQfinish(pgsqlConn);
00234          pgsqlConn = NULL;
00235       };
00236       return NULL;
00237    }
00238 
00239    initfield = ast_strdupa(newparam);
00240    if ((op = strchr(initfield, ' '))) {
00241       *op = '\0';
00242    }
00243 
00244    /* Create the first part of the query using the first parameter/value pairs we just extracted
00245       If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
00246 
00247    if (!strchr(newparam, ' '))
00248       op = " =";
00249    else
00250       op = "";
00251 
00252    PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00253    if (pgerror) {
00254       ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00255       va_end(ap);
00256       return NULL;
00257    }
00258 
00259    snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
00260           escapebuf);
00261    while ((newparam = va_arg(ap, const char *))) {
00262       newval = va_arg(ap, const char *);
00263       if (!strchr(newparam, ' '))
00264          op = " =";
00265       else
00266          op = "";
00267 
00268       PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00269       if (pgerror) {
00270          ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00271          va_end(ap);
00272          return NULL;
00273       }
00274 
00275       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
00276              op, escapebuf);
00277    }
00278 
00279    if (initfield) {
00280       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00281    }
00282 
00283    va_end(ap);
00284 
00285    /* We now have our complete statement; Lets connect to the server and execute it. */
00286    ast_mutex_lock(&pgsql_lock);
00287    if (!pgsql_reconnect(database)) {
00288       ast_mutex_unlock(&pgsql_lock);
00289       return NULL;
00290    }
00291 
00292    if (!(result = PQexec(pgsqlConn, sql))) {
00293       ast_log(LOG_WARNING,
00294             "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00295       ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00296       ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00297       ast_mutex_unlock(&pgsql_lock);
00298       return NULL;
00299    } else {
00300       ExecStatusType result_status = PQresultStatus(result);
00301       if (result_status != PGRES_COMMAND_OK
00302          && result_status != PGRES_TUPLES_OK
00303          && result_status != PGRES_NONFATAL_ERROR) {
00304          ast_log(LOG_WARNING,
00305                "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00306          ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00307          ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00308                   PQresultErrorMessage(result), PQresStatus(result_status));
00309          ast_mutex_unlock(&pgsql_lock);
00310          return NULL;
00311       }
00312    }
00313 
00314    ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
00315 
00316    if ((num_rows = PQntuples(result)) > 0) {
00317       int numFields = PQnfields(result);
00318       int i = 0;
00319       int rowIndex = 0;
00320       char **fieldnames = NULL;
00321 
00322       ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
00323 
00324       if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
00325          ast_mutex_unlock(&pgsql_lock);
00326          PQclear(result);
00327          return NULL;
00328       }
00329       for (i = 0; i < numFields; i++)
00330          fieldnames[i] = PQfname(result, i);
00331 
00332       for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00333          var = NULL;
00334          if (!(cat = ast_category_new("","",99999)))
00335             continue;
00336          for (i = 0; i < numFields; i++) {
00337             stringp = PQgetvalue(result, rowIndex, i);
00338             while (stringp) {
00339                chunk = strsep(&stringp, ";");
00340                if (!ast_strlen_zero(ast_strip(chunk))) {
00341                   if (initfield && !strcmp(initfield, fieldnames[i])) {
00342                      ast_category_rename(cat, chunk);
00343                   }
00344                   var = ast_variable_new(fieldnames[i], chunk, "");
00345                   ast_variable_append(cat, var);
00346                }
00347             }
00348          }
00349          ast_category_append(cfg, cat);
00350       }
00351       ast_free(fieldnames);
00352    } else {
00353       ast_log(LOG_WARNING,
00354             "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
00355    }
00356 
00357    ast_mutex_unlock(&pgsql_lock);
00358    PQclear(result);
00359 
00360    return cfg;
00361 }
00362 
00363 static int update_pgsql(const char *database, const char *table, const char *keyfield,
00364                   const char *lookup, va_list ap)
00365 {
00366    PGresult *result = NULL;
00367    int numrows = 0, pgerror;
00368    char sql[256], escapebuf[513];
00369    const char *newparam, *newval;
00370 
00371    if (!table) {
00372       ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00373       return -1;
00374    }
00375 
00376    /* Get the first parameter and first value in our list of passed paramater/value pairs */
00377    newparam = va_arg(ap, const char *);
00378    newval = va_arg(ap, const char *);
00379    if (!newparam || !newval) {
00380       ast_log(LOG_WARNING,
00381             "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
00382       if (pgsqlConn) {
00383          PQfinish(pgsqlConn);
00384          pgsqlConn = NULL;
00385       };
00386       return -1;
00387    }
00388 
00389    /* Create the first part of the query using the first parameter/value pairs we just extracted
00390       If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
00391 
00392    PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00393    if (pgerror) {
00394       ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00395       va_end(ap);
00396       return -1;
00397    }
00398    snprintf(sql, sizeof(sql), "UPDATE %s SET %s = '%s'", table, newparam, escapebuf);
00399 
00400    while ((newparam = va_arg(ap, const char *))) {
00401       newval = va_arg(ap, const char *);
00402 
00403       PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
00404       if (pgerror) {
00405          ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
00406          va_end(ap);
00407          return -1;
00408       }
00409 
00410       snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s = '%s'", newparam,
00411              escapebuf);
00412    }
00413    va_end(ap);
00414 
00415    PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
00416    if (pgerror) {
00417       ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
00418       va_end(ap);
00419       return -1;
00420    }
00421 
00422    snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s = '%s'", keyfield,
00423           escapebuf);
00424 
00425    ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", sql);
00426 
00427    /* We now have our complete statement; Lets connect to the server and execute it. */
00428    ast_mutex_lock(&pgsql_lock);
00429    if (!pgsql_reconnect(database)) {
00430       ast_mutex_unlock(&pgsql_lock);
00431       return -1;
00432    }
00433 
00434    if (!(result = PQexec(pgsqlConn, sql))) {
00435       ast_log(LOG_WARNING,
00436             "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00437       ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00438       ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00439       ast_mutex_unlock(&pgsql_lock);
00440       return -1;
00441    } else {
00442       ExecStatusType result_status = PQresultStatus(result);
00443       if (result_status != PGRES_COMMAND_OK
00444          && result_status != PGRES_TUPLES_OK
00445          && result_status != PGRES_NONFATAL_ERROR) {
00446          ast_log(LOG_WARNING,
00447                "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00448          ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00449          ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00450                   PQresultErrorMessage(result), PQresStatus(result_status));
00451          ast_mutex_unlock(&pgsql_lock);
00452          return -1;
00453       }
00454    }
00455 
00456    numrows = atoi(PQcmdTuples(result));
00457    ast_mutex_unlock(&pgsql_lock);
00458 
00459    ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, table);
00460 
00461    /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
00462     * An integer greater than zero indicates the number of rows affected
00463     * Zero indicates that no records were updated
00464     * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
00465     */
00466 
00467    if (numrows >= 0)
00468       return (int) numrows;
00469 
00470    return -1;
00471 }
00472 
00473 #define ESCAPE_STRING(buffer, stringname) \
00474    do { \
00475       int len; \
00476       if ((len = strlen(stringname)) > (buffer->len - 1) / 2) { \
00477          ast_str_make_space(&buffer, len * 2 + 1); \
00478       } \
00479       PQescapeStringConn(pgsqlConn, buffer->str, stringname, len, &pgresult); \
00480    } while (0)
00481 
00482 static int store_pgsql(const char *database, const char *table, va_list ap)
00483 {
00484    PGresult *result = NULL;
00485    Oid insertid;
00486    struct ast_str *buf = ast_str_create(256);
00487    struct ast_str *sql1 = ast_str_create(256);
00488    struct ast_str *sql2 = ast_str_create(256);
00489    int pgresult;
00490    const char *newparam, *newval;
00491 
00492    if (!table) {
00493       ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00494       return -1;
00495    }
00496 
00497    /* Get the first parameter and first value in our list of passed paramater/value pairs */
00498    newparam = va_arg(ap, const char *);
00499    newval = va_arg(ap, const char *);
00500    if (!newparam || !newval) {
00501       ast_log(LOG_WARNING,
00502             "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
00503       if (pgsqlConn) {
00504          PQfinish(pgsqlConn);
00505          pgsqlConn = NULL;
00506       }
00507       return -1;
00508    }
00509 
00510    /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
00511    ast_mutex_lock(&pgsql_lock);
00512    if (!pgsql_reconnect(database)) {
00513       ast_mutex_unlock(&pgsql_lock);
00514       return -1;
00515    }
00516 
00517    /* Create the first part of the query using the first parameter/value pairs we just extracted
00518       If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
00519    ESCAPE_STRING(buf, newparam);
00520    ast_str_set(&sql1, 0, "INSERT INTO %s (%s", table, buf->str);
00521    ESCAPE_STRING(buf, newval);
00522    ast_str_set(&sql2, 0, ") VALUES ('%s'", buf->str);
00523    while ((newparam = va_arg(ap, const char *))) {
00524       newval = va_arg(ap, const char *);
00525       ESCAPE_STRING(buf, newparam);
00526       ast_str_append(&sql1, 0, ", %s", buf->str);
00527       ESCAPE_STRING(buf, newval);
00528       ast_str_append(&sql2, 0, ", '%s'", buf->str);
00529    }
00530    va_end(ap);
00531    ast_str_append(&sql1, 0, "%s)", sql2->str);
00532 
00533    ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", sql1->str);
00534 
00535    if (!(result = PQexec(pgsqlConn, sql1->str))) {
00536       ast_log(LOG_WARNING,
00537             "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00538       ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql1->str);
00539       ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00540       ast_mutex_unlock(&pgsql_lock);
00541       ast_free(sql1);
00542       ast_free(sql2);
00543       ast_free(buf);
00544       return -1;
00545    } else {
00546       ExecStatusType result_status = PQresultStatus(result);
00547       if (result_status != PGRES_COMMAND_OK
00548          && result_status != PGRES_TUPLES_OK
00549          && result_status != PGRES_NONFATAL_ERROR) {
00550          ast_log(LOG_WARNING,
00551                "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00552          ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql1->str);
00553          ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00554                   PQresultErrorMessage(result), PQresStatus(result_status));
00555          ast_mutex_unlock(&pgsql_lock);
00556          ast_free(sql1);
00557          ast_free(sql2);
00558          ast_free(buf);
00559          return -1;
00560       }
00561    }
00562 
00563    insertid = PQoidValue(result);
00564    ast_mutex_unlock(&pgsql_lock);
00565    ast_free(sql1);
00566    ast_free(sql2);
00567    ast_free(buf);
00568 
00569    ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
00570 
00571    /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
00572     * An integer greater than zero indicates the number of rows affected
00573     * Zero indicates that no records were updated
00574     * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
00575     */
00576 
00577    if (insertid >= 0)
00578       return (int) insertid;
00579 
00580    return -1;
00581 }
00582 
00583 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00584 {
00585    PGresult *result = NULL;
00586    int numrows = 0;
00587    int pgresult;
00588    struct ast_str *sql = ast_str_create(256);
00589    struct ast_str *buf1 = ast_str_create(60), *buf2 = ast_str_create(60);
00590    const char *newparam, *newval;
00591 
00592    if (!table) {
00593       ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
00594       return -1;
00595    }
00596 
00597    /* Get the first parameter and first value in our list of passed paramater/value pairs */
00598    /*newparam = va_arg(ap, const char *);
00599    newval = va_arg(ap, const char *);
00600    if (!newparam || !newval) {*/
00601    if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
00602       ast_log(LOG_WARNING,
00603             "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
00604       if (pgsqlConn) {
00605          PQfinish(pgsqlConn);
00606          pgsqlConn = NULL;
00607       };
00608       return -1;
00609    }
00610 
00611    /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
00612    ast_mutex_lock(&pgsql_lock);
00613    if (!pgsql_reconnect(database)) {
00614       ast_mutex_unlock(&pgsql_lock);
00615       return -1;
00616    }
00617 
00618 
00619    /* Create the first part of the query using the first parameter/value pairs we just extracted
00620       If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
00621 
00622    ESCAPE_STRING(buf1, keyfield);
00623    ESCAPE_STRING(buf2, lookup);
00624    ast_str_set(&sql, 0, "DELETE FROM %s WHERE %s = '%s'", table, buf1->str, buf2->str);
00625    while ((newparam = va_arg(ap, const char *))) {
00626       newval = va_arg(ap, const char *);
00627       ESCAPE_STRING(buf1, newparam);
00628       ESCAPE_STRING(buf2, newval);
00629       ast_str_append(&sql, 0, " AND %s = '%s'", buf1->str, buf2->str);
00630    }
00631    va_end(ap);
00632 
00633    ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", sql->str);
00634 
00635    if (!(result = PQexec(pgsqlConn, sql->str))) {
00636       ast_log(LOG_WARNING,
00637             "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00638       ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
00639       ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00640       ast_mutex_unlock(&pgsql_lock);
00641       ast_free(buf1);
00642       ast_free(buf2);
00643       ast_free(sql);
00644       return -1;
00645    } else {
00646       ExecStatusType result_status = PQresultStatus(result);
00647       if (result_status != PGRES_COMMAND_OK
00648          && result_status != PGRES_TUPLES_OK
00649          && result_status != PGRES_NONFATAL_ERROR) {
00650          ast_log(LOG_WARNING,
00651                "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00652          ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
00653          ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00654                   PQresultErrorMessage(result), PQresStatus(result_status));
00655          ast_mutex_unlock(&pgsql_lock);
00656          ast_free(buf1);
00657          ast_free(buf2);
00658          ast_free(sql);
00659          return -1;
00660       }
00661    }
00662 
00663    numrows = atoi(PQcmdTuples(result));
00664    ast_mutex_unlock(&pgsql_lock);
00665    ast_free(buf1);
00666    ast_free(buf2);
00667    ast_free(sql);
00668 
00669    ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
00670 
00671    /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
00672     * An integer greater than zero indicates the number of rows affected
00673     * Zero indicates that no records were updated
00674     * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
00675     */
00676 
00677    if (numrows >= 0)
00678       return (int) numrows;
00679 
00680    return -1;
00681 }
00682 
00683 
00684 static struct ast_config *config_pgsql(const char *database, const char *table,
00685                               const char *file, struct ast_config *cfg,
00686                               struct ast_flags flags, const char *suggested_incl, const char *who_asked)
00687 {
00688    PGresult *result = NULL;
00689    long num_rows;
00690    struct ast_variable *new_v;
00691    struct ast_category *cur_cat = NULL;
00692    char sqlbuf[1024] = "";
00693    char *sql = sqlbuf;
00694    size_t sqlleft = sizeof(sqlbuf);
00695    char last[80] = "";
00696    int last_cat_metric = 0;
00697 
00698    last[0] = '\0';
00699 
00700    if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
00701       ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
00702       return NULL;
00703    }
00704 
00705    ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
00706    ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
00707    ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00708 
00709    ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", sqlbuf);
00710 
00711    /* We now have our complete statement; Lets connect to the server and execute it. */
00712    ast_mutex_lock(&pgsql_lock);
00713    if (!pgsql_reconnect(database)) {
00714       ast_mutex_unlock(&pgsql_lock);
00715       return NULL;
00716    }
00717 
00718    if (!(result = PQexec(pgsqlConn, sqlbuf))) {
00719       ast_log(LOG_WARNING,
00720             "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00721       ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00722       ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
00723       ast_mutex_unlock(&pgsql_lock);
00724       return NULL;
00725    } else {
00726       ExecStatusType result_status = PQresultStatus(result);
00727       if (result_status != PGRES_COMMAND_OK
00728          && result_status != PGRES_TUPLES_OK
00729          && result_status != PGRES_NONFATAL_ERROR) {
00730          ast_log(LOG_WARNING,
00731                "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
00732          ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
00733          ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
00734                   PQresultErrorMessage(result), PQresStatus(result_status));
00735          ast_mutex_unlock(&pgsql_lock);
00736          return NULL;
00737       }
00738    }
00739 
00740    if ((num_rows = PQntuples(result)) > 0) {
00741       int rowIndex = 0;
00742 
00743       ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
00744 
00745       for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
00746          char *field_category = PQgetvalue(result, rowIndex, 0);
00747          char *field_var_name = PQgetvalue(result, rowIndex, 1);
00748          char *field_var_val = PQgetvalue(result, rowIndex, 2);
00749          char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
00750          if (!strcmp(field_var_name, "#include")) {
00751             if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
00752                PQclear(result);
00753                ast_mutex_unlock(&pgsql_lock);
00754                return NULL;
00755             }
00756             continue;
00757          }
00758 
00759          if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
00760             cur_cat = ast_category_new(field_category, "", 99999);
00761             if (!cur_cat)
00762                break;
00763             strcpy(last, field_category);
00764             last_cat_metric = atoi(field_cat_metric);
00765             ast_category_append(cfg, cur_cat);
00766          }
00767          new_v = ast_variable_new(field_var_name, field_var_val, "");
00768          ast_variable_append(cur_cat, new_v);
00769       }
00770    } else {
00771       ast_log(LOG_WARNING,
00772             "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
00773    }
00774 
00775    PQclear(result);
00776    ast_mutex_unlock(&pgsql_lock);
00777 
00778    return cfg;
00779 }
00780 
00781 static struct ast_config_engine pgsql_engine = {
00782    .name = "pgsql",
00783    .load_func = config_pgsql,
00784    .realtime_func = realtime_pgsql,
00785    .realtime_multi_func = realtime_multi_pgsql,
00786    .store_func = store_pgsql,
00787    .destroy_func = destroy_pgsql,
00788    .update_func = update_pgsql
00789 };
00790 
00791 static int load_module(void)
00792 {
00793    if(!parse_config(0))
00794       return AST_MODULE_LOAD_DECLINE;
00795 
00796    ast_config_engine_register(&pgsql_engine);
00797    ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
00798    ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
00799 
00800    return 0;
00801 }
00802 
00803 static int unload_module(void)
00804 {
00805    /* Acquire control before doing anything to the module itself. */
00806    ast_mutex_lock(&pgsql_lock);
00807 
00808    if (pgsqlConn) {
00809       PQfinish(pgsqlConn);
00810       pgsqlConn = NULL;
00811    }
00812    ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
00813    ast_config_engine_deregister(&pgsql_engine);
00814    ast_verb(1, "PostgreSQL RealTime unloaded.\n");
00815 
00816    /* Unlock so something else can destroy the lock. */
00817    ast_mutex_unlock(&pgsql_lock);
00818 
00819    return 0;
00820 }
00821 
00822 static int reload(void)
00823 {
00824    parse_config(1);
00825 
00826    return 0;
00827 }
00828 
00829 static int parse_config(int reload)
00830 {
00831    struct ast_config *config;
00832    const char *s;
00833    struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
00834 
00835    if ((config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags)) == CONFIG_STATUS_FILEUNCHANGED)
00836       return 0;
00837 
00838    if (!config) {
00839       ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
00840       return 0;
00841    }
00842 
00843    ast_mutex_lock(&pgsql_lock);
00844 
00845    if (pgsqlConn) {
00846       PQfinish(pgsqlConn);
00847       pgsqlConn = NULL;
00848    }
00849 
00850    if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
00851       ast_log(LOG_WARNING,
00852             "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
00853       strcpy(dbuser, "asterisk");
00854    } else {
00855       ast_copy_string(dbuser, s, sizeof(dbuser));
00856    }
00857 
00858    if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
00859       ast_log(LOG_WARNING,
00860             "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
00861       strcpy(dbpass, "asterisk");
00862    } else {
00863       ast_copy_string(dbpass, s, sizeof(dbpass));
00864    }
00865 
00866    if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
00867       ast_log(LOG_WARNING,
00868             "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
00869       dbhost[0] = '\0';
00870    } else {
00871       ast_copy_string(dbhost, s, sizeof(dbhost));
00872    }
00873 
00874    if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
00875       ast_log(LOG_WARNING,
00876             "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
00877       strcpy(dbname, "asterisk");
00878    } else {
00879       ast_copy_string(dbname, s, sizeof(dbname));
00880    }
00881 
00882    if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
00883       ast_log(LOG_WARNING,
00884             "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
00885       dbport = 5432;
00886    } else {
00887       dbport = atoi(s);
00888    }
00889 
00890    if (!ast_strlen_zero(dbhost)) {
00891       /* No socket needed */
00892    } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
00893       ast_log(LOG_WARNING,
00894             "PostgreSQL RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
00895       strcpy(dbsock, "/tmp/pgsql.sock");
00896    } else {
00897       ast_copy_string(dbsock, s, sizeof(dbsock));
00898    }
00899    ast_config_destroy(config);
00900 
00901    if (option_debug) {
00902       if (!ast_strlen_zero(dbhost)) {
00903          ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
00904          ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
00905       } else {
00906          ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
00907       }
00908       ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
00909       ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
00910       ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
00911    }
00912 
00913    if (!pgsql_reconnect(NULL)) {
00914       ast_log(LOG_WARNING,
00915             "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
00916       ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
00917    }
00918 
00919    ast_verb(2, "PostgreSQL RealTime reloaded.\n");
00920 
00921    /* Done reloading. Release lock so others can now use driver. */
00922    ast_mutex_unlock(&pgsql_lock);
00923 
00924    return 1;
00925 }
00926 
00927 static int pgsql_reconnect(const char *database)
00928 {
00929    char my_database[50];
00930 
00931    ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
00932 
00933    /* mutex lock should have been locked before calling this function. */
00934 
00935    if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
00936       PQfinish(pgsqlConn);
00937       pgsqlConn = NULL;
00938    }
00939 
00940    /* DB password can legitimately be 0-length */
00941    if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
00942       struct ast_str *connInfo = ast_str_create(32);
00943 
00944       ast_str_set(&connInfo, 0, "host=%s port=%d dbname=%s user=%s",
00945          dbhost, dbport, my_database, dbuser);
00946       if (!ast_strlen_zero(dbpass))
00947          ast_str_append(&connInfo, 0, " password=%s", dbpass);
00948 
00949       ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
00950       pgsqlConn = PQconnectdb(connInfo->str);
00951       ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
00952       ast_free(connInfo);
00953       connInfo = NULL;
00954 
00955       ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
00956       if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
00957          ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
00958          connect_time = time(NULL);
00959          return 1;
00960       } else {
00961          ast_log(LOG_ERROR,
00962                "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
00963                dbname, dbhost, PQresultErrorMessage(NULL));
00964          return 0;
00965       }
00966    } else {
00967       ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
00968       return 1;
00969    }
00970 }
00971 
00972 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
00973 {
00974    char status[256], credentials[100] = "";
00975    int ctime = time(NULL) - connect_time;
00976 
00977    switch (cmd) {
00978    case CLI_INIT:
00979       e->command = "realtime pgsql status";
00980       e->usage =
00981          "Usage: realtime pgsql status\n"
00982          "       Shows connection information for the PostgreSQL RealTime driver\n";
00983       return NULL;
00984    case CLI_GENERATE:
00985       return NULL;
00986    }
00987 
00988    if (a->argc != 3)
00989       return CLI_SHOWUSAGE;
00990 
00991    if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
00992       if (!ast_strlen_zero(dbhost))
00993          snprintf(status, sizeof(status), "Connected to %s@%s, port %d", dbname, dbhost, dbport);
00994       else if (!ast_strlen_zero(dbsock))
00995          snprintf(status, sizeof(status), "Connected to %s on socket file %s", dbname, dbsock);
00996       else
00997          snprintf(status, sizeof(status), "Connected to %s@%s", dbname, dbhost);
00998 
00999       if (!ast_strlen_zero(dbuser))
01000          snprintf(credentials, sizeof(credentials), " with username %s", dbuser);
01001 
01002       if (ctime > 31536000)
01003          ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
01004                status, credentials, ctime / 31536000, (ctime % 31536000) / 86400,
01005                (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
01006       else if (ctime > 86400)
01007          ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
01008                credentials, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
01009                ctime % 60);
01010       else if (ctime > 3600)
01011          ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, credentials,
01012                ctime / 3600, (ctime % 3600) / 60, ctime % 60);
01013       else if (ctime > 60)
01014          ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, credentials, ctime / 60,
01015                ctime % 60);
01016       else
01017          ast_cli(a->fd, "%s%s for %d seconds.\n", status, credentials, ctime);
01018 
01019       return CLI_SUCCESS;
01020    } else {
01021       return CLI_FAILURE;
01022    }
01023 }
01024 
01025 /* needs usecount semantics defined */
01026 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
01027       .load = load_module,
01028       .unload = unload_module,
01029       .reload = reload
01030           );

Generated on Thu Jul 9 13:40:39 2009 for Asterisk - the Open Source PBX by  doxygen 1.4.7