Merge the cli_cleanup branch.
[asterisk/asterisk.git] / res / res_config_pgsql.c
1 /*
2  * Asterisk -- A telephony toolkit for Linux.
3  *
4  * Copyright (C) 1999-2005, Digium, Inc.
5  * 
6  * Manuel Guesdon <mguesdon@oxymium.net> - PostgreSQL RealTime Driver Author/Adaptor
7  * Mark Spencer <markster@digium.com>  - Asterisk Author
8  * Matthew Boehm <mboehm@cytelcom.com> - MySQL RealTime Driver Author
9  *
10  * res_config_pgsql.c <PostgreSQL plugin for RealTime configuration engine>
11  *
12  * v1.0   - (07-11-05) - Initial version based on res_config_mysql v2.0
13  */
14
15 /*! \file
16  *
17  * \brief PostgreSQL plugin for Asterisk RealTime Architecture
18  *
19  * \author Mark Spencer <markster@digium.com>
20  * \author Manuel Guesdon <mguesdon@oxymium.net> - PostgreSQL RealTime Driver Author/Adaptor
21  *
22  * \arg http://www.postgresql.org
23  */
24
25 /*** MODULEINFO
26         <depend>pgsql</depend>
27  ***/
28
29 #include "asterisk.h"
30
31 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
32
33 #include <libpq-fe.h>                   /* PostgreSQL */
34
35 #include "asterisk/file.h"
36 #include "asterisk/channel.h"
37 #include "asterisk/pbx.h"
38 #include "asterisk/config.h"
39 #include "asterisk/module.h"
40 #include "asterisk/lock.h"
41 #include "asterisk/utils.h"
42 #include "asterisk/cli.h"
43
44 AST_MUTEX_DEFINE_STATIC(pgsql_lock);
45
46 #define RES_CONFIG_PGSQL_CONF "res_pgsql.conf"
47
48 PGconn *pgsqlConn = NULL;
49
50 #define MAX_DB_OPTION_SIZE 64
51
52 struct columns {
53         char *name;
54         char *type;
55         int len;
56         unsigned int notnull:1;
57         unsigned int hasdefault:1;
58         AST_LIST_ENTRY(columns) list;
59 };
60
61 struct tables {
62         ast_mutex_t lock;
63         AST_LIST_HEAD_NOLOCK(psql_columns, columns) columns;
64         AST_LIST_ENTRY(tables) list;
65         char name[0];
66 };
67
68 static AST_LIST_HEAD_STATIC(psql_tables, tables);
69
70 static char dbhost[MAX_DB_OPTION_SIZE] = "";
71 static char dbuser[MAX_DB_OPTION_SIZE] = "";
72 static char dbpass[MAX_DB_OPTION_SIZE] = "";
73 static char dbname[MAX_DB_OPTION_SIZE] = "";
74 static char dbsock[MAX_DB_OPTION_SIZE] = "";
75 static int dbport = 5432;
76 static time_t connect_time = 0;
77
78 static int parse_config(int reload);
79 static int pgsql_reconnect(const char *database);
80 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
81 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
82
83 enum { RQ_WARN, RQ_CREATECLOSE, RQ_CREATECHAR } requirements;
84
85 static struct ast_cli_entry cli_realtime[] = {
86         AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
87         AST_CLI_DEFINE(handle_cli_realtime_pgsql_cache, "Shows cached tables within the PostgreSQL realtime driver"),
88 };
89
90 static void destroy_table(struct tables *table)
91 {
92         struct columns *column;
93         ast_mutex_lock(&table->lock);
94         while ((column = AST_LIST_REMOVE_HEAD(&table->columns, list))) {
95                 ast_free(column);
96         }
97         ast_mutex_unlock(&table->lock);
98         ast_mutex_destroy(&table->lock);
99         ast_free(table);
100 }
101
102 static struct tables *find_table(const char *tablename)
103 {
104         struct columns *column;
105         struct tables *table;
106         struct ast_str *sql = ast_str_create(330);
107         char *pgerror;
108         PGresult *result;
109         char *fname, *ftype, *flen, *fnotnull, *fdef;
110         int i, rows;
111
112         AST_LIST_LOCK(&psql_tables);
113         AST_LIST_TRAVERSE(&psql_tables, table, list) {
114                 if (!strcasecmp(table->name, tablename)) {
115                         ast_debug(1, "Found table in cache; now locking\n");
116                         ast_mutex_lock(&table->lock);
117                         ast_debug(1, "Lock cached table; now returning\n");
118                         AST_LIST_UNLOCK(&psql_tables);
119                         return table;
120                 }
121         }
122
123         ast_debug(1, "Table '%s' not found in cache, querying now\n", tablename);
124
125         /* Not found, scan the table */
126         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);
127         result = PQexec(pgsqlConn, sql->str);
128         ast_debug(1, "Query of table structure complete.  Now retrieving results.\n");
129         if (PQresultStatus(result) != PGRES_TUPLES_OK) {
130                 pgerror = PQresultErrorMessage(result);
131                 ast_log(LOG_ERROR, "Failed to query database columns: %s\n", pgerror);
132                 PQclear(result);
133                 AST_LIST_UNLOCK(&psql_tables);
134                 return NULL;
135         }
136
137         if (!(table = ast_calloc(1, sizeof(*table) + strlen(tablename) + 1))) {
138                 ast_log(LOG_ERROR, "Unable to allocate memory for new table structure\n");
139                 AST_LIST_UNLOCK(&psql_tables);
140                 return NULL;
141         }
142         strcpy(table->name, tablename); /* SAFE */
143         ast_mutex_init(&table->lock);
144         AST_LIST_HEAD_INIT_NOLOCK(&table->columns);
145         
146         rows = PQntuples(result);
147         for (i = 0; i < rows; i++) {
148                 fname = PQgetvalue(result, i, 0);
149                 ftype = PQgetvalue(result, i, 1);
150                 flen = PQgetvalue(result, i, 2);
151                 fnotnull = PQgetvalue(result, i, 3);
152                 fdef = PQgetvalue(result, i, 4);
153                 ast_verb(4, "Found column '%s' of type '%s'\n", fname, ftype);
154
155                 if (!(column = ast_calloc(1, sizeof(*column) + strlen(fname) + strlen(ftype) + 2))) {
156                         ast_log(LOG_ERROR, "Unable to allocate column element for %s, %s\n", tablename, fname);
157                         destroy_table(table);
158                         AST_LIST_UNLOCK(&psql_tables);
159                         return NULL;
160                 }
161
162                 if (strcmp(flen, "-1") == 0) {
163                         /* Some types, like chars, have the length stored in a different field */
164                         flen = PQgetvalue(result, i, 5);
165                         sscanf(flen, "%d", &column->len);
166                         column->len -= 4;
167                 } else {
168                         sscanf(flen, "%d", &column->len);
169                 }
170                 column->name = (char *)column + sizeof(*column);
171                 column->type = (char *)column + sizeof(*column) + strlen(fname) + 1;
172                 strcpy(column->name, fname);
173                 strcpy(column->type, ftype);
174                 if (*fnotnull == 't') {
175                         column->notnull = 1;
176                 } else {
177                         column->notnull = 0;
178                 }
179                 if (!ast_strlen_zero(fdef)) {
180                         column->hasdefault = 1;
181                 } else {
182                         column->hasdefault = 0;
183                 }
184                 AST_LIST_INSERT_TAIL(&table->columns, column, list);
185         }
186         PQclear(result);
187
188         AST_LIST_INSERT_TAIL(&psql_tables, table, list);
189         ast_mutex_lock(&table->lock);
190         AST_LIST_UNLOCK(&psql_tables);
191         return table;
192 }
193
194 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
195 {
196         PGresult *result = NULL;
197         int num_rows = 0, pgerror;
198         char sql[256], escapebuf[513];
199         char *stringp;
200         char *chunk;
201         char *op;
202         const char *newparam, *newval;
203         struct ast_variable *var = NULL, *prev = NULL;
204
205         if (!table) {
206                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
207                 return NULL;
208         }
209
210         /* Get the first parameter and first value in our list of passed paramater/value pairs */
211         newparam = va_arg(ap, const char *);
212         newval = va_arg(ap, const char *);
213         if (!newparam || !newval) {
214                 ast_log(LOG_WARNING,
215                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
216                 if (pgsqlConn) {
217                         PQfinish(pgsqlConn);
218                         pgsqlConn = NULL;
219                 };
220                 return NULL;
221         }
222
223         /* Create the first part of the query using the first parameter/value pairs we just extracted
224            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
225         op = strchr(newparam, ' ') ? "" : " =";
226
227         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
228         if (pgerror) {
229                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
230                 va_end(ap);
231                 return NULL;
232         }
233
234         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
235                          escapebuf);
236         while ((newparam = va_arg(ap, const char *))) {
237                 newval = va_arg(ap, const char *);
238                 if (!strchr(newparam, ' '))
239                         op = " =";
240                 else
241                         op = "";
242
243                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
244                 if (pgerror) {
245                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
246                         va_end(ap);
247                         return NULL;
248                 }
249
250                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
251                                  op, escapebuf);
252         }
253         va_end(ap);
254
255         /* We now have our complete statement; Lets connect to the server and execute it. */
256         ast_mutex_lock(&pgsql_lock);
257         if (!pgsql_reconnect(database)) {
258                 ast_mutex_unlock(&pgsql_lock);
259                 return NULL;
260         }
261
262         if (!(result = PQexec(pgsqlConn, sql))) {
263                 ast_log(LOG_WARNING,
264                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
265                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
266                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
267                 ast_mutex_unlock(&pgsql_lock);
268                 return NULL;
269         } else {
270                 ExecStatusType result_status = PQresultStatus(result);
271                 if (result_status != PGRES_COMMAND_OK
272                         && result_status != PGRES_TUPLES_OK
273                         && result_status != PGRES_NONFATAL_ERROR) {
274                         ast_log(LOG_WARNING,
275                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
276                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
277                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
278                                                 PQresultErrorMessage(result), PQresStatus(result_status));
279                         ast_mutex_unlock(&pgsql_lock);
280                         return NULL;
281                 }
282         }
283
284         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
285
286         if ((num_rows = PQntuples(result)) > 0) {
287                 int i = 0;
288                 int rowIndex = 0;
289                 int numFields = PQnfields(result);
290                 char **fieldnames = NULL;
291
292                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
293
294                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
295                         ast_mutex_unlock(&pgsql_lock);
296                         PQclear(result);
297                         return NULL;
298                 }
299                 for (i = 0; i < numFields; i++)
300                         fieldnames[i] = PQfname(result, i);
301                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
302                         for (i = 0; i < numFields; i++) {
303                                 stringp = PQgetvalue(result, rowIndex, i);
304                                 while (stringp) {
305                                         chunk = strsep(&stringp, ";");
306                                         if (!ast_strlen_zero(ast_strip(chunk))) {
307                                                 if (prev) {
308                                                         prev->next = ast_variable_new(fieldnames[i], chunk, "");
309                                                         if (prev->next) {
310                                                                 prev = prev->next;
311                                                         }
312                                                 } else {
313                                                         prev = var = ast_variable_new(fieldnames[i], chunk, "");
314                                                 }
315                                         }
316                                 }
317                         }
318                 }
319                 ast_free(fieldnames);
320         } else {
321                 ast_debug(1, "Postgresql RealTime: Could not find any rows in table %s.\n", table);
322         }
323
324         ast_mutex_unlock(&pgsql_lock);
325         PQclear(result);
326
327         return var;
328 }
329
330 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
331 {
332         PGresult *result = NULL;
333         int num_rows = 0, pgerror;
334         char sql[256], escapebuf[513];
335         const char *initfield = NULL;
336         char *stringp;
337         char *chunk;
338         char *op;
339         const char *newparam, *newval;
340         struct ast_variable *var = NULL;
341         struct ast_config *cfg = NULL;
342         struct ast_category *cat = NULL;
343
344         if (!table) {
345                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
346                 return NULL;
347         }
348
349         if (!(cfg = ast_config_new()))
350                 return NULL;
351
352         /* Get the first parameter and first value in our list of passed paramater/value pairs */
353         newparam = va_arg(ap, const char *);
354         newval = va_arg(ap, const char *);
355         if (!newparam || !newval) {
356                 ast_log(LOG_WARNING,
357                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
358                 if (pgsqlConn) {
359                         PQfinish(pgsqlConn);
360                         pgsqlConn = NULL;
361                 };
362                 return NULL;
363         }
364
365         initfield = ast_strdupa(newparam);
366         if ((op = strchr(initfield, ' '))) {
367                 *op = '\0';
368         }
369
370         /* Create the first part of the query using the first parameter/value pairs we just extracted
371            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
372
373         if (!strchr(newparam, ' '))
374                 op = " =";
375         else
376                 op = "";
377
378         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
379         if (pgerror) {
380                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
381                 va_end(ap);
382                 return NULL;
383         }
384
385         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
386                          escapebuf);
387         while ((newparam = va_arg(ap, const char *))) {
388                 newval = va_arg(ap, const char *);
389                 if (!strchr(newparam, ' '))
390                         op = " =";
391                 else
392                         op = "";
393
394                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
395                 if (pgerror) {
396                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
397                         va_end(ap);
398                         return NULL;
399                 }
400
401                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
402                                  op, escapebuf);
403         }
404
405         if (initfield) {
406                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
407         }
408
409         va_end(ap);
410
411         /* We now have our complete statement; Lets connect to the server and execute it. */
412         ast_mutex_lock(&pgsql_lock);
413         if (!pgsql_reconnect(database)) {
414                 ast_mutex_unlock(&pgsql_lock);
415                 return NULL;
416         }
417
418         if (!(result = PQexec(pgsqlConn, sql))) {
419                 ast_log(LOG_WARNING,
420                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
421                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
422                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
423                 ast_mutex_unlock(&pgsql_lock);
424                 return NULL;
425         } else {
426                 ExecStatusType result_status = PQresultStatus(result);
427                 if (result_status != PGRES_COMMAND_OK
428                         && result_status != PGRES_TUPLES_OK
429                         && result_status != PGRES_NONFATAL_ERROR) {
430                         ast_log(LOG_WARNING,
431                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
432                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
433                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
434                                                 PQresultErrorMessage(result), PQresStatus(result_status));
435                         ast_mutex_unlock(&pgsql_lock);
436                         return NULL;
437                 }
438         }
439
440         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
441
442         if ((num_rows = PQntuples(result)) > 0) {
443                 int numFields = PQnfields(result);
444                 int i = 0;
445                 int rowIndex = 0;
446                 char **fieldnames = NULL;
447
448                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
449
450                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
451                         ast_mutex_unlock(&pgsql_lock);
452                         PQclear(result);
453                         return NULL;
454                 }
455                 for (i = 0; i < numFields; i++)
456                         fieldnames[i] = PQfname(result, i);
457
458                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
459                         var = NULL;
460                         if (!(cat = ast_category_new("","",99999)))
461                                 continue;
462                         for (i = 0; i < numFields; i++) {
463                                 stringp = PQgetvalue(result, rowIndex, i);
464                                 while (stringp) {
465                                         chunk = strsep(&stringp, ";");
466                                         if (!ast_strlen_zero(ast_strip(chunk))) {
467                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
468                                                         ast_category_rename(cat, chunk);
469                                                 }
470                                                 var = ast_variable_new(fieldnames[i], chunk, "");
471                                                 ast_variable_append(cat, var);
472                                         }
473                                 }
474                         }
475                         ast_category_append(cfg, cat);
476                 }
477                 ast_free(fieldnames);
478         } else {
479                 ast_log(LOG_WARNING,
480                                 "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
481         }
482
483         ast_mutex_unlock(&pgsql_lock);
484         PQclear(result);
485
486         return cfg;
487 }
488
489 static int update_pgsql(const char *database, const char *tablename, const char *keyfield,
490                                                 const char *lookup, va_list ap)
491 {
492         PGresult *result = NULL;
493         int numrows = 0, pgerror;
494         char escapebuf[513];
495         const char *newparam, *newval;
496         struct ast_str *sql = ast_str_create(100);
497         struct tables *table;
498         struct columns *column = NULL;
499
500         if (!tablename) {
501                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
502                 ast_free(sql);
503                 return -1;
504         }
505
506         if (!(table = find_table(tablename))) {
507                 ast_log(LOG_ERROR, "Table '%s' does not exist!!\n", tablename);
508                 ast_free(sql);
509                 return -1;
510         }
511
512         /* Get the first parameter and first value in our list of passed paramater/value pairs */
513         newparam = va_arg(ap, const char *);
514         newval = va_arg(ap, const char *);
515         if (!newparam || !newval) {
516                 ast_log(LOG_WARNING,
517                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
518                 if (pgsqlConn) {
519                         PQfinish(pgsqlConn);
520                         pgsqlConn = NULL;
521                 };
522                 ast_mutex_unlock(&table->lock);
523                 ast_free(sql);
524                 return -1;
525         }
526
527         /* Check that the column exists in the table */
528         AST_LIST_TRAVERSE(&table->columns, column, list) {
529                 if (strcmp(column->name, newparam) == 0) {
530                         break;
531                 }
532         }
533
534         if (!column) {
535                 ast_log(LOG_ERROR, "PostgreSQL RealTime: Updating on column '%s', but that column does not exist within the table '%s'!\n", newparam, tablename);
536                 ast_mutex_unlock(&table->lock);
537                 ast_free(sql);
538                 return -1;
539         }
540
541         /* Create the first part of the query using the first parameter/value pairs we just extracted
542            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
543
544         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
545         if (pgerror) {
546                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
547                 va_end(ap);
548                 ast_mutex_unlock(&table->lock);
549                 ast_free(sql);
550                 return -1;
551         }
552         ast_str_set(&sql, 0, "UPDATE %s SET %s = '%s'", tablename, newparam, escapebuf);
553
554         while ((newparam = va_arg(ap, const char *))) {
555                 newval = va_arg(ap, const char *);
556
557                 /* If the column is not within the table, then skip it */
558                 AST_LIST_TRAVERSE(&table->columns, column, list) {
559                         if (strcmp(column->name, newparam) == 0) {
560                                 break;
561                         }
562                 }
563
564                 if (!column) {
565                         ast_log(LOG_WARNING, "Attempted to update column '%s' in table '%s', but column does not exist!\n", newparam, tablename);
566                         continue;
567                 }
568
569                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
570                 if (pgerror) {
571                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
572                         va_end(ap);
573                         ast_mutex_unlock(&table->lock);
574                         ast_free(sql);
575                         return -1;
576                 }
577
578                 ast_str_append(&sql, 0, ", %s = '%s'", newparam, escapebuf);
579         }
580         va_end(ap);
581         ast_mutex_unlock(&table->lock);
582
583         PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
584         if (pgerror) {
585                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
586                 va_end(ap);
587                 ast_free(sql);
588                 return -1;
589         }
590
591         ast_str_append(&sql, 0, " WHERE %s = '%s'", keyfield, escapebuf);
592
593         ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", sql->str);
594
595         /* We now have our complete statement; Lets connect to the server and execute it. */
596         ast_mutex_lock(&pgsql_lock);
597         if (!pgsql_reconnect(database)) {
598                 ast_mutex_unlock(&pgsql_lock);
599                 ast_free(sql);
600                 return -1;
601         }
602
603         if (!(result = PQexec(pgsqlConn, sql->str))) {
604                 ast_log(LOG_WARNING,
605                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
606                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
607                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
608                 ast_mutex_unlock(&pgsql_lock);
609                 ast_free(sql);
610                 return -1;
611         } else {
612                 ExecStatusType result_status = PQresultStatus(result);
613                 if (result_status != PGRES_COMMAND_OK
614                         && result_status != PGRES_TUPLES_OK
615                         && result_status != PGRES_NONFATAL_ERROR) {
616                         ast_log(LOG_WARNING,
617                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
618                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
619                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
620                                                 PQresultErrorMessage(result), PQresStatus(result_status));
621                         ast_mutex_unlock(&pgsql_lock);
622                         ast_free(sql);
623                         return -1;
624                 }
625         }
626
627         numrows = atoi(PQcmdTuples(result));
628         ast_mutex_unlock(&pgsql_lock);
629         ast_free(sql);
630
631         ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, tablename);
632
633         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
634          * An integer greater than zero indicates the number of rows affected
635          * Zero indicates that no records were updated
636          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
637          */
638
639         if (numrows >= 0)
640                 return (int) numrows;
641
642         return -1;
643 }
644
645 #define ESCAPE_STRING(buffer, stringname) \
646         do { \
647                 int len; \
648                 if ((len = strlen(stringname)) > (buffer->len - 1) / 2) { \
649                         ast_str_make_space(&buffer, len * 2 + 1); \
650                 } \
651                 PQescapeStringConn(pgsqlConn, buffer->str, stringname, len, &pgresult); \
652         } while (0)
653
654 static int store_pgsql(const char *database, const char *table, va_list ap)
655 {
656         PGresult *result = NULL;
657         Oid insertid;
658         struct ast_str *buf = ast_str_create(256);
659         struct ast_str *sql1 = ast_str_create(256);
660         struct ast_str *sql2 = ast_str_create(256);
661         int pgresult;
662         const char *newparam, *newval;
663
664         if (!table) {
665                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
666                 return -1;
667         }
668
669         /* Get the first parameter and first value in our list of passed paramater/value pairs */
670         newparam = va_arg(ap, const char *);
671         newval = va_arg(ap, const char *);
672         if (!newparam || !newval) {
673                 ast_log(LOG_WARNING,
674                                 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
675                 if (pgsqlConn) {
676                         PQfinish(pgsqlConn);
677                         pgsqlConn = NULL;
678                 }
679                 return -1;
680         }
681
682         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
683         ast_mutex_lock(&pgsql_lock);
684         if (!pgsql_reconnect(database)) {
685                 ast_mutex_unlock(&pgsql_lock);
686                 return -1;
687         }
688
689         /* Create the first part of the query using the first parameter/value pairs we just extracted
690            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
691         ESCAPE_STRING(buf, newparam);
692         ast_str_set(&sql1, 0, "INSERT INTO %s (%s", table, buf->str);
693         ESCAPE_STRING(buf, newval);
694         ast_str_set(&sql2, 0, ") VALUES ('%s'", buf->str);
695         while ((newparam = va_arg(ap, const char *))) {
696                 newval = va_arg(ap, const char *);
697                 ESCAPE_STRING(buf, newparam);
698                 ast_str_append(&sql1, 0, ", %s", buf->str);
699                 ESCAPE_STRING(buf, newval);
700                 ast_str_append(&sql2, 0, ", '%s'", buf->str);
701         }
702         va_end(ap);
703         ast_str_append(&sql1, 0, "%s)", sql2->str);
704
705         ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", sql1->str);
706
707         if (!(result = PQexec(pgsqlConn, sql1->str))) {
708                 ast_log(LOG_WARNING,
709                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
710                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql1->str);
711                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
712                 ast_mutex_unlock(&pgsql_lock);
713                 ast_free(sql1);
714                 ast_free(sql2);
715                 ast_free(buf);
716                 return -1;
717         } else {
718                 ExecStatusType result_status = PQresultStatus(result);
719                 if (result_status != PGRES_COMMAND_OK
720                         && result_status != PGRES_TUPLES_OK
721                         && result_status != PGRES_NONFATAL_ERROR) {
722                         ast_log(LOG_WARNING,
723                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
724                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql1->str);
725                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
726                                                 PQresultErrorMessage(result), PQresStatus(result_status));
727                         ast_mutex_unlock(&pgsql_lock);
728                         ast_free(sql1);
729                         ast_free(sql2);
730                         ast_free(buf);
731                         return -1;
732                 }
733         }
734
735         insertid = PQoidValue(result);
736         ast_mutex_unlock(&pgsql_lock);
737         ast_free(sql1);
738         ast_free(sql2);
739         ast_free(buf);
740
741         ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
742
743         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
744          * An integer greater than zero indicates the number of rows affected
745          * Zero indicates that no records were updated
746          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
747          */
748
749         if (insertid >= 0)
750                 return (int) insertid;
751
752         return -1;
753 }
754
755 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
756 {
757         PGresult *result = NULL;
758         int numrows = 0;
759         int pgresult;
760         struct ast_str *sql = ast_str_create(256);
761         struct ast_str *buf1 = ast_str_create(60), *buf2 = ast_str_create(60);
762         const char *newparam, *newval;
763
764         if (!table) {
765                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
766                 return -1;
767         }
768
769         /* Get the first parameter and first value in our list of passed paramater/value pairs */
770         /*newparam = va_arg(ap, const char *);
771         newval = va_arg(ap, const char *);
772         if (!newparam || !newval) {*/
773         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
774                 ast_log(LOG_WARNING,
775                                 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
776                 if (pgsqlConn) {
777                         PQfinish(pgsqlConn);
778                         pgsqlConn = NULL;
779                 };
780                 return -1;
781         }
782
783         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
784         ast_mutex_lock(&pgsql_lock);
785         if (!pgsql_reconnect(database)) {
786                 ast_mutex_unlock(&pgsql_lock);
787                 return -1;
788         }
789
790
791         /* Create the first part of the query using the first parameter/value pairs we just extracted
792            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
793
794         ESCAPE_STRING(buf1, keyfield);
795         ESCAPE_STRING(buf2, lookup);
796         ast_str_set(&sql, 0, "DELETE FROM %s WHERE %s = '%s'", table, buf1->str, buf2->str);
797         while ((newparam = va_arg(ap, const char *))) {
798                 newval = va_arg(ap, const char *);
799                 ESCAPE_STRING(buf1, newparam);
800                 ESCAPE_STRING(buf2, newval);
801                 ast_str_append(&sql, 0, " AND %s = '%s'", buf1->str, buf2->str);
802         }
803         va_end(ap);
804
805         ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", sql->str);
806
807         if (!(result = PQexec(pgsqlConn, sql->str))) {
808                 ast_log(LOG_WARNING,
809                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
810                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
811                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
812                 ast_mutex_unlock(&pgsql_lock);
813                 ast_free(buf1);
814                 ast_free(buf2);
815                 ast_free(sql);
816                 return -1;
817         } else {
818                 ExecStatusType result_status = PQresultStatus(result);
819                 if (result_status != PGRES_COMMAND_OK
820                         && result_status != PGRES_TUPLES_OK
821                         && result_status != PGRES_NONFATAL_ERROR) {
822                         ast_log(LOG_WARNING,
823                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
824                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql->str);
825                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
826                                                 PQresultErrorMessage(result), PQresStatus(result_status));
827                         ast_mutex_unlock(&pgsql_lock);
828                         ast_free(buf1);
829                         ast_free(buf2);
830                         ast_free(sql);
831                         return -1;
832                 }
833         }
834
835         numrows = atoi(PQcmdTuples(result));
836         ast_mutex_unlock(&pgsql_lock);
837         ast_free(buf1);
838         ast_free(buf2);
839         ast_free(sql);
840
841         ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
842
843         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
844          * An integer greater than zero indicates the number of rows affected
845          * Zero indicates that no records were updated
846          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
847          */
848
849         if (numrows >= 0)
850                 return (int) numrows;
851
852         return -1;
853 }
854
855
856 static struct ast_config *config_pgsql(const char *database, const char *table,
857                                                                            const char *file, struct ast_config *cfg,
858                                                                            struct ast_flags flags, const char *suggested_incl, const char *who_asked)
859 {
860         PGresult *result = NULL;
861         long num_rows;
862         struct ast_variable *new_v;
863         struct ast_category *cur_cat = NULL;
864         char sqlbuf[1024] = "";
865         char *sql = sqlbuf;
866         size_t sqlleft = sizeof(sqlbuf);
867         char last[80] = "";
868         int last_cat_metric = 0;
869
870         last[0] = '\0';
871
872         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
873                 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
874                 return NULL;
875         }
876
877         ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
878         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
879         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
880
881         ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", sqlbuf);
882
883         /* We now have our complete statement; Lets connect to the server and execute it. */
884         ast_mutex_lock(&pgsql_lock);
885         if (!pgsql_reconnect(database)) {
886                 ast_mutex_unlock(&pgsql_lock);
887                 return NULL;
888         }
889
890         if (!(result = PQexec(pgsqlConn, sqlbuf))) {
891                 ast_log(LOG_WARNING,
892                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
893                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
894                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
895                 ast_mutex_unlock(&pgsql_lock);
896                 return NULL;
897         } else {
898                 ExecStatusType result_status = PQresultStatus(result);
899                 if (result_status != PGRES_COMMAND_OK
900                         && result_status != PGRES_TUPLES_OK
901                         && result_status != PGRES_NONFATAL_ERROR) {
902                         ast_log(LOG_WARNING,
903                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
904                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
905                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
906                                                 PQresultErrorMessage(result), PQresStatus(result_status));
907                         ast_mutex_unlock(&pgsql_lock);
908                         return NULL;
909                 }
910         }
911
912         if ((num_rows = PQntuples(result)) > 0) {
913                 int rowIndex = 0;
914
915                 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
916
917                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
918                         char *field_category = PQgetvalue(result, rowIndex, 0);
919                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
920                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
921                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
922                         if (!strcmp(field_var_name, "#include")) {
923                                 if (!ast_config_internal_load(field_var_val, cfg, flags, "", who_asked)) {
924                                         PQclear(result);
925                                         ast_mutex_unlock(&pgsql_lock);
926                                         return NULL;
927                                 }
928                                 continue;
929                         }
930
931                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
932                                 cur_cat = ast_category_new(field_category, "", 99999);
933                                 if (!cur_cat)
934                                         break;
935                                 strcpy(last, field_category);
936                                 last_cat_metric = atoi(field_cat_metric);
937                                 ast_category_append(cfg, cur_cat);
938                         }
939                         new_v = ast_variable_new(field_var_name, field_var_val, "");
940                         ast_variable_append(cur_cat, new_v);
941                 }
942         } else {
943                 ast_log(LOG_WARNING,
944                                 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
945         }
946
947         PQclear(result);
948         ast_mutex_unlock(&pgsql_lock);
949
950         return cfg;
951 }
952
953 static int require_pgsql(const char *database, const char *tablename, va_list ap)
954 {
955         struct columns *column;
956         struct tables *table = find_table(tablename);
957         char *elm;
958         int type, size, res = 0;
959
960         if (!table) {
961                 ast_log(LOG_WARNING, "Table %s not found in database.  This table should exist if you're using realtime.\n", tablename);
962                 return -1;
963         }
964
965         while ((elm = va_arg(ap, char *))) {
966                 type = va_arg(ap, require_type);
967                 size = va_arg(ap, int);
968                 AST_LIST_TRAVERSE(&table->columns, column, list) {
969                         if (strcmp(column->name, elm) == 0) {
970                                 /* Char can hold anything, as long as it is large enough */
971                                 if ((strncmp(column->type, "char", 4) == 0 || strncmp(column->type, "varchar", 7) == 0 || strcmp(column->type, "bpchar") == 0)) {
972                                         if ((size > column->len) && column->len != -1) {
973                                                 ast_log(LOG_WARNING, "Column '%s' should be at least %d long, but is only %d long.\n", column->name, size, column->len);
974                                                 res = -1;
975                                         }
976                                 } else if (strncmp(column->type, "int", 3) == 0) {
977                                         int typesize = atoi(column->type + 3);
978                                         /* Integers can hold only other integers */
979                                         if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
980                                                 type == RQ_INTEGER4 || type == RQ_UINTEGER4 ||
981                                                 type == RQ_INTEGER3 || type == RQ_UINTEGER3 ||
982                                                 type == RQ_UINTEGER2) && typesize == 2) {
983                                                 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
984                                                 res = -1;
985                                         } else if ((type == RQ_INTEGER8 || type == RQ_UINTEGER8 ||
986                                                 type == RQ_UINTEGER4) && typesize == 4) {
987                                                 ast_log(LOG_WARNING, "Column '%s' may not be large enough for the required data length: %d\n", column->name, size);
988                                                 res = -1;
989                                         } else if (type == RQ_CHAR || type == RQ_DATETIME || type == RQ_FLOAT || type == RQ_DATE) {
990                                                 ast_log(LOG_WARNING, "Column '%s' is of the incorrect type: (need %s(%d) but saw %s)\n",
991                                                         column->name,
992                                                                 type == RQ_CHAR ? "char" :
993                                                                 type == RQ_DATETIME ? "datetime" :
994                                                                 type == RQ_DATE ? "date" :
995                                                                 type == RQ_FLOAT ? "float" :
996                                                                 "a rather stiff drink ",
997                                                         size, column->type);
998                                                 res = -1;
999                                         }
1000                                 } else if (strncmp(column->type, "float", 5) == 0 && !ast_rq_is_int(type) && type != RQ_FLOAT) {
1001                                         ast_log(LOG_WARNING, "Column %s cannot be a %s\n", column->name, column->type);
1002                                         res = -1;
1003                                 } else { /* There are other types that no module implements yet */
1004                                         ast_log(LOG_WARNING, "Possibly unsupported column type '%s' on column '%s'\n", column->type, column->name);
1005                                         res = -1;
1006                                 }
1007                                 break;
1008                         }
1009                 }
1010
1011                 if (!column) {
1012                         if (requirements == RQ_WARN) {
1013                                 ast_log(LOG_WARNING, "Table %s requires a column '%s' of size '%d', but no such column exists.\n", tablename, elm, size);
1014                         } else {
1015                                 struct ast_str *sql = ast_str_create(100);
1016                                 char fieldtype[15];
1017                                 PGresult *result;
1018
1019                                 if (requirements == RQ_CREATECHAR || type == RQ_CHAR) {
1020                                         /* Size is minimum length; make it at least 50% greater,
1021                                          * just to be sure, because PostgreSQL doesn't support
1022                                          * resizing columns. */
1023                                         snprintf(fieldtype, sizeof(fieldtype), "CHAR(%d)",
1024                                                 size < 15 ? size * 2 :
1025                                                 (size * 3 / 2 > 255) ? 255 : size * 3 / 2);
1026                                 } else if (type == RQ_INTEGER1 || type == RQ_UINTEGER1 || type == RQ_INTEGER2) {
1027                                         snprintf(fieldtype, sizeof(fieldtype), "INT2");
1028                                 } else if (type == RQ_UINTEGER2 || type == RQ_INTEGER3 || type == RQ_UINTEGER3 || type == RQ_INTEGER4) {
1029                                         snprintf(fieldtype, sizeof(fieldtype), "INT4");
1030                                 } else if (type == RQ_UINTEGER4 || type == RQ_INTEGER8) {
1031                                         snprintf(fieldtype, sizeof(fieldtype), "INT8");
1032                                 } else if (type == RQ_UINTEGER8) {
1033                                         /* No such type on PostgreSQL */
1034                                         snprintf(fieldtype, sizeof(fieldtype), "CHAR(20)");
1035                                 } else if (type == RQ_FLOAT) {
1036                                         snprintf(fieldtype, sizeof(fieldtype), "FLOAT8");
1037                                 } else if (type == RQ_DATE) {
1038                                         snprintf(fieldtype, sizeof(fieldtype), "DATE");
1039                                 } else if (type == RQ_DATETIME) {
1040                                         snprintf(fieldtype, sizeof(fieldtype), "TIMESTAMP");
1041                                 } else {
1042                                         ast_log(LOG_ERROR, "Unrecognized request type %d\n", type);
1043                                         ast_free(sql);
1044                                         continue;
1045                                 }
1046                                 ast_str_set(&sql, 0, "ALTER TABLE %s ADD COLUMN %s %s", tablename, elm, fieldtype);
1047                                 ast_debug(1, "About to lock pgsql_lock (running alter on table '%s' to add column '%s')\n", tablename, elm);
1048
1049                                 ast_mutex_lock(&pgsql_lock);
1050                                 if (!pgsql_reconnect(database)) {
1051                                         ast_mutex_unlock(&pgsql_lock);
1052                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", sql->str);
1053                                         ast_free(sql);
1054                                         continue;
1055                                 }
1056
1057                                 ast_debug(1, "About to run ALTER query on table '%s' to add column '%s'\n", tablename, elm);
1058                                 result = PQexec(pgsqlConn, sql->str);
1059                                 ast_debug(1, "Finished running ALTER query on table '%s'\n", tablename);
1060                                 if (PQresultStatus(result) != PGRES_COMMAND_OK) {
1061                                         ast_log(LOG_ERROR, "Unable to add column: %s\n", sql->str);
1062                                 }
1063                                 PQclear(result);
1064                                 ast_mutex_unlock(&pgsql_lock);
1065
1066                                 ast_free(sql);
1067                         }
1068                 }
1069         }
1070         ast_mutex_unlock(&table->lock);
1071         return res;
1072 }
1073
1074 static int unload_pgsql(const char *database, const char *tablename)
1075 {
1076         struct tables *cur;
1077         ast_debug(2, "About to lock table cache list\n");
1078         AST_LIST_LOCK(&psql_tables);
1079         ast_debug(2, "About to traverse table cache list\n");
1080         AST_LIST_TRAVERSE_SAFE_BEGIN(&psql_tables, cur, list) {
1081                 if (strcmp(cur->name, tablename) == 0) {
1082                         ast_debug(2, "About to remove matching cache entry\n");
1083                         AST_LIST_REMOVE_CURRENT(list);
1084                         ast_debug(2, "About to destroy matching cache entry\n");
1085                         destroy_table(cur);
1086                         ast_debug(1, "Cache entry '%s@%s' destroyed\n", tablename, database);
1087                         break;
1088                 }
1089         }
1090         AST_LIST_TRAVERSE_SAFE_END
1091         AST_LIST_UNLOCK(&psql_tables);
1092         ast_debug(2, "About to return\n");
1093         return cur ? 0 : -1;
1094 }
1095
1096 static struct ast_config_engine pgsql_engine = {
1097         .name = "pgsql",
1098         .load_func = config_pgsql,
1099         .realtime_func = realtime_pgsql,
1100         .realtime_multi_func = realtime_multi_pgsql,
1101         .store_func = store_pgsql,
1102         .destroy_func = destroy_pgsql,
1103         .update_func = update_pgsql,
1104         .require_func = require_pgsql,
1105         .unload_func = unload_pgsql,
1106 };
1107
1108 static int load_module(void)
1109 {
1110         if(!parse_config(0))
1111                 return AST_MODULE_LOAD_DECLINE;
1112
1113         ast_config_engine_register(&pgsql_engine);
1114         ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
1115         ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
1116
1117         return 0;
1118 }
1119
1120 static int unload_module(void)
1121 {
1122         struct tables *table;
1123         /* Acquire control before doing anything to the module itself. */
1124         ast_mutex_lock(&pgsql_lock);
1125
1126         if (pgsqlConn) {
1127                 PQfinish(pgsqlConn);
1128                 pgsqlConn = NULL;
1129         }
1130         ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
1131         ast_config_engine_deregister(&pgsql_engine);
1132         ast_verb(1, "PostgreSQL RealTime unloaded.\n");
1133
1134         /* Destroy cached table info */
1135         AST_LIST_LOCK(&psql_tables);
1136         while ((table = AST_LIST_REMOVE_HEAD(&psql_tables, list))) {
1137                 destroy_table(table);
1138         }
1139         AST_LIST_UNLOCK(&psql_tables);
1140
1141         /* Unlock so something else can destroy the lock. */
1142         ast_mutex_unlock(&pgsql_lock);
1143
1144         return 0;
1145 }
1146
1147 static int reload(void)
1148 {
1149         parse_config(1);
1150
1151         return 0;
1152 }
1153
1154 static int parse_config(int is_reload)
1155 {
1156         struct ast_config *config;
1157         const char *s;
1158         struct ast_flags config_flags = { is_reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
1159
1160         config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags);
1161         if (config == CONFIG_STATUS_FILEUNCHANGED) {
1162                 return 0;
1163         }
1164
1165         if (config == CONFIG_STATUS_FILEMISSING || config == CONFIG_STATUS_FILEINVALID) {
1166                 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
1167                 return 0;
1168         }
1169
1170         ast_mutex_lock(&pgsql_lock);
1171
1172         if (pgsqlConn) {
1173                 PQfinish(pgsqlConn);
1174                 pgsqlConn = NULL;
1175         }
1176
1177         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
1178                 ast_log(LOG_WARNING,
1179                                 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
1180                 strcpy(dbuser, "asterisk");
1181         } else {
1182                 ast_copy_string(dbuser, s, sizeof(dbuser));
1183         }
1184
1185         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
1186                 ast_log(LOG_WARNING,
1187                                 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
1188                 strcpy(dbpass, "asterisk");
1189         } else {
1190                 ast_copy_string(dbpass, s, sizeof(dbpass));
1191         }
1192
1193         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
1194                 ast_log(LOG_WARNING,
1195                                 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
1196                 dbhost[0] = '\0';
1197         } else {
1198                 ast_copy_string(dbhost, s, sizeof(dbhost));
1199         }
1200
1201         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
1202                 ast_log(LOG_WARNING,
1203                                 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
1204                 strcpy(dbname, "asterisk");
1205         } else {
1206                 ast_copy_string(dbname, s, sizeof(dbname));
1207         }
1208
1209         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
1210                 ast_log(LOG_WARNING,
1211                                 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
1212                 dbport = 5432;
1213         } else {
1214                 dbport = atoi(s);
1215         }
1216
1217         if (!ast_strlen_zero(dbhost)) {
1218                 /* No socket needed */
1219         } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
1220                 ast_log(LOG_WARNING,
1221                                 "PostgreSQL RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
1222                 strcpy(dbsock, "/tmp/pgsql.sock");
1223         } else {
1224                 ast_copy_string(dbsock, s, sizeof(dbsock));
1225         }
1226
1227         if (!(s = ast_variable_retrieve(config, "general", "requirements"))) {
1228                 ast_log(LOG_WARNING,
1229                                 "PostgreSQL RealTime: no requirements setting found, using 'warn' as default.\n");
1230                 requirements = RQ_WARN;
1231         } else if (!strcasecmp(s, "createclose")) {
1232                 requirements = RQ_CREATECLOSE;
1233         } else if (!strcasecmp(s, "createchar")) {
1234                 requirements = RQ_CREATECHAR;
1235         }
1236
1237         ast_config_destroy(config);
1238
1239         if (option_debug) {
1240                 if (!ast_strlen_zero(dbhost)) {
1241                         ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
1242                         ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
1243                 } else {
1244                         ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
1245                 }
1246                 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
1247                 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
1248                 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
1249         }
1250
1251         if (!pgsql_reconnect(NULL)) {
1252                 ast_log(LOG_WARNING,
1253                                 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
1254                 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
1255         }
1256
1257         ast_verb(2, "PostgreSQL RealTime reloaded.\n");
1258
1259         /* Done reloading. Release lock so others can now use driver. */
1260         ast_mutex_unlock(&pgsql_lock);
1261
1262         return 1;
1263 }
1264
1265 static int pgsql_reconnect(const char *database)
1266 {
1267         char my_database[50];
1268
1269         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
1270
1271         /* mutex lock should have been locked before calling this function. */
1272
1273         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
1274                 PQfinish(pgsqlConn);
1275                 pgsqlConn = NULL;
1276         }
1277
1278         /* DB password can legitimately be 0-length */
1279         if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(my_database)) {
1280                 struct ast_str *connInfo = ast_str_create(32);
1281
1282                 ast_str_set(&connInfo, 0, "host=%s port=%d dbname=%s user=%s",
1283                         dbhost, dbport, my_database, dbuser);
1284                 if (!ast_strlen_zero(dbpass))
1285                         ast_str_append(&connInfo, 0, " password=%s", dbpass);
1286
1287                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
1288                 pgsqlConn = PQconnectdb(connInfo->str);
1289                 ast_debug(1, "%u connInfo=%s\n", (unsigned int)connInfo->len, connInfo->str);
1290                 ast_free(connInfo);
1291                 connInfo = NULL;
1292
1293                 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
1294                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1295                         ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
1296                         connect_time = time(NULL);
1297                         return 1;
1298                 } else {
1299                         ast_log(LOG_ERROR,
1300                                         "PostgreSQL RealTime: Failed to connect database %s on %s: %s\n",
1301                                         dbname, dbhost, PQresultErrorMessage(NULL));
1302                         return 0;
1303                 }
1304         } else {
1305                 ast_debug(1, "PostgreSQL RealTime: One or more of the parameters in the config does not pass our validity checks.\n");
1306                 return 1;
1307         }
1308 }
1309
1310 static char *handle_cli_realtime_pgsql_cache(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1311 {
1312         struct tables *cur;
1313         int l, which;
1314         char *ret = NULL;
1315
1316         switch (cmd) {
1317         case CLI_INIT:
1318                 e->command = "realtime show pgsql cache";
1319                 e->usage =
1320                         "Usage: realtime show pgsql cache [<table>]\n"
1321                         "       Shows table cache for the PostgreSQL RealTime driver\n";
1322                 return NULL;
1323         case CLI_GENERATE:
1324                 if (a->argc != 4) {
1325                         return NULL;
1326                 }
1327                 l = strlen(a->word);
1328                 which = 0;
1329                 AST_LIST_LOCK(&psql_tables);
1330                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1331                         if (!strncasecmp(a->word, cur->name, l) && ++which > a->n) {
1332                                 ret = ast_strdup(cur->name);
1333                                 break;
1334                         }
1335                 }
1336                 AST_LIST_UNLOCK(&psql_tables);
1337                 return ret;
1338         }
1339
1340         if (a->argc == 4) {
1341                 /* List of tables */
1342                 AST_LIST_LOCK(&psql_tables);
1343                 AST_LIST_TRAVERSE(&psql_tables, cur, list) {
1344                         ast_cli(a->fd, "%s\n", cur->name);
1345                 }
1346                 AST_LIST_UNLOCK(&psql_tables);
1347         } else if (a->argc == 5) {
1348                 /* List of columns */
1349                 if ((cur = find_table(a->argv[4]))) {
1350                         struct columns *col;
1351                         ast_cli(a->fd, "Columns for Table Cache '%s':\n", a->argv[4]);
1352                         ast_cli(a->fd, "%-20.20s %-20.20s %-3.3s %-8.8s\n", "Name", "Type", "Len", "Nullable");
1353                         AST_LIST_TRAVERSE(&cur->columns, col, list) {
1354                                 ast_cli(a->fd, "%-20.20s %-20.20s %3d %-8.8s\n", col->name, col->type, col->len, col->notnull ? "NOT NULL" : "");
1355                         }
1356                         ast_mutex_unlock(&cur->lock);
1357                 } else {
1358                         ast_cli(a->fd, "No such table '%s'\n", a->argv[4]);
1359                 }
1360         }
1361         return 0;
1362 }
1363
1364 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1365 {
1366         char status[256], credentials[100] = "";
1367         int ctimesec = time(NULL) - connect_time;
1368
1369         switch (cmd) {
1370         case CLI_INIT:
1371                 e->command = "realtime show pgsql status";
1372                 e->usage =
1373                         "Usage: realtime show pgsql status\n"
1374                         "       Shows connection information for the PostgreSQL RealTime driver\n";
1375                 return NULL;
1376         case CLI_GENERATE:
1377                 return NULL;
1378         }
1379
1380         if (a->argc != 4)
1381                 return CLI_SHOWUSAGE;
1382
1383         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
1384                 if (!ast_strlen_zero(dbhost))
1385                         snprintf(status, sizeof(status), "Connected to %s@%s, port %d", dbname, dbhost, dbport);
1386                 else if (!ast_strlen_zero(dbsock))
1387                         snprintf(status, sizeof(status), "Connected to %s on socket file %s", dbname, dbsock);
1388                 else
1389                         snprintf(status, sizeof(status), "Connected to %s@%s", dbname, dbhost);
1390
1391                 if (!ast_strlen_zero(dbuser))
1392                         snprintf(credentials, sizeof(credentials), " with username %s", dbuser);
1393
1394                 if (ctimesec > 31536000)
1395                         ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
1396                                         status, credentials, ctimesec / 31536000, (ctimesec % 31536000) / 86400,
1397                                         (ctimesec % 86400) / 3600, (ctimesec % 3600) / 60, ctimesec % 60);
1398                 else if (ctimesec > 86400)
1399                         ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
1400                                         credentials, ctimesec / 86400, (ctimesec % 86400) / 3600, (ctimesec % 3600) / 60,
1401                                         ctimesec % 60);
1402                 else if (ctimesec > 3600)
1403                         ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, credentials,
1404                                         ctimesec / 3600, (ctimesec % 3600) / 60, ctimesec % 60);
1405                 else if (ctimesec > 60)
1406                         ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, credentials, ctimesec / 60,
1407                                         ctimesec % 60);
1408                 else
1409                         ast_cli(a->fd, "%s%s for %d seconds.\n", status, credentials, ctimesec);
1410
1411                 return CLI_SUCCESS;
1412         } else {
1413                 return CLI_FAILURE;
1414         }
1415 }
1416
1417 /* needs usecount semantics defined */
1418 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
1419                 .load = load_module,
1420                 .unload = unload_module,
1421                 .reload = reload
1422                );