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