Merged revisions 90736 via svnmerge from
[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 static char dbhost[MAX_DB_OPTION_SIZE] = "";
53 static char dbuser[MAX_DB_OPTION_SIZE] = "";
54 static char dbpass[MAX_DB_OPTION_SIZE] = "";
55 static char dbname[MAX_DB_OPTION_SIZE] = "";
56 static char dbsock[MAX_DB_OPTION_SIZE] = "";
57 static int dbport = 5432;
58 static time_t connect_time = 0;
59
60 static int parse_config(int reload);
61 static int pgsql_reconnect(const char *database);
62 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a);
63
64 static struct ast_cli_entry cli_realtime[] = {
65         AST_CLI_DEFINE(handle_cli_realtime_pgsql_status, "Shows connection information for the PostgreSQL RealTime driver"),
66 };
67
68 static struct ast_variable *realtime_pgsql(const char *database, const char *table, va_list ap)
69 {
70         PGresult *result = NULL;
71         int num_rows = 0, pgerror;
72         char sql[256], escapebuf[513];
73         char *stringp;
74         char *chunk;
75         char *op;
76         const char *newparam, *newval;
77         struct ast_variable *var = NULL, *prev = NULL;
78
79         if (!table) {
80                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
81                 return NULL;
82         }
83
84         /* Get the first parameter and first value in our list of passed paramater/value pairs */
85         newparam = va_arg(ap, const char *);
86         newval = va_arg(ap, const char *);
87         if (!newparam || !newval) {
88                 ast_log(LOG_WARNING,
89                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
90                 if (pgsqlConn) {
91                         PQfinish(pgsqlConn);
92                         pgsqlConn = NULL;
93                 };
94                 return NULL;
95         }
96
97         /* Create the first part of the query using the first parameter/value pairs we just extracted
98            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
99         op = strchr(newparam, ' ') ? "" : " =";
100
101         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
102         if (pgerror) {
103                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
104                 va_end(ap);
105                 return NULL;
106         }
107
108         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
109                          escapebuf);
110         while ((newparam = va_arg(ap, const char *))) {
111                 newval = va_arg(ap, const char *);
112                 if (!strchr(newparam, ' '))
113                         op = " =";
114                 else
115                         op = "";
116
117                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
118                 if (pgerror) {
119                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
120                         va_end(ap);
121                         return NULL;
122                 }
123
124                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
125                                  op, escapebuf);
126         }
127         va_end(ap);
128
129         /* We now have our complete statement; Lets connect to the server and execute it. */
130         ast_mutex_lock(&pgsql_lock);
131         if (!pgsql_reconnect(database)) {
132                 ast_mutex_unlock(&pgsql_lock);
133                 return NULL;
134         }
135
136         if (!(result = PQexec(pgsqlConn, sql))) {
137                 ast_log(LOG_WARNING,
138                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
139                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
140                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
141                 ast_mutex_unlock(&pgsql_lock);
142                 return NULL;
143         } else {
144                 ExecStatusType result_status = PQresultStatus(result);
145                 if (result_status != PGRES_COMMAND_OK
146                         && result_status != PGRES_TUPLES_OK
147                         && result_status != PGRES_NONFATAL_ERROR) {
148                         ast_log(LOG_WARNING,
149                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
150                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
151                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
152                                                 PQresultErrorMessage(result), PQresStatus(result_status));
153                         ast_mutex_unlock(&pgsql_lock);
154                         return NULL;
155                 }
156         }
157
158         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
159
160         if ((num_rows = PQntuples(result)) > 0) {
161                 int i = 0;
162                 int rowIndex = 0;
163                 int numFields = PQnfields(result);
164                 char **fieldnames = NULL;
165
166                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
167
168                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
169                         ast_mutex_unlock(&pgsql_lock);
170                         PQclear(result);
171                         return NULL;
172                 }
173                 for (i = 0; i < numFields; i++)
174                         fieldnames[i] = PQfname(result, i);
175                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
176                         for (i = 0; i < numFields; i++) {
177                                 stringp = PQgetvalue(result, rowIndex, i);
178                                 while (stringp) {
179                                         chunk = strsep(&stringp, ";");
180                                         if (!ast_strlen_zero(ast_strip(chunk))) {
181                                                 if (prev) {
182                                                         prev->next = ast_variable_new(fieldnames[i], chunk, "");
183                                                         if (prev->next) {
184                                                                 prev = prev->next;
185                                                         }
186                                                 } else {
187                                                         prev = var = ast_variable_new(fieldnames[i], chunk, "");
188                                                 }
189                                         }
190                                 }
191                         }
192                 }
193                 ast_free(fieldnames);
194         } else {
195                 ast_log(LOG_WARNING,
196                                 "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
197         }
198
199         ast_mutex_unlock(&pgsql_lock);
200         PQclear(result);
201
202         return var;
203 }
204
205 static struct ast_config *realtime_multi_pgsql(const char *database, const char *table, va_list ap)
206 {
207         PGresult *result = NULL;
208         int num_rows = 0, pgerror;
209         char sql[256], escapebuf[513];
210         const char *initfield = NULL;
211         char *stringp;
212         char *chunk;
213         char *op;
214         const char *newparam, *newval;
215         struct ast_variable *var = NULL;
216         struct ast_config *cfg = NULL;
217         struct ast_category *cat = NULL;
218
219         if (!table) {
220                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
221                 return NULL;
222         }
223
224         if (!(cfg = ast_config_new()))
225                 return NULL;
226
227         /* Get the first parameter and first value in our list of passed paramater/value pairs */
228         newparam = va_arg(ap, const char *);
229         newval = va_arg(ap, const char *);
230         if (!newparam || !newval) {
231                 ast_log(LOG_WARNING,
232                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
233                 if (pgsqlConn) {
234                         PQfinish(pgsqlConn);
235                         pgsqlConn = NULL;
236                 };
237                 return NULL;
238         }
239
240         initfield = ast_strdupa(newparam);
241         if ((op = strchr(initfield, ' '))) {
242                 *op = '\0';
243         }
244
245         /* Create the first part of the query using the first parameter/value pairs we just extracted
246            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
247
248         if (!strchr(newparam, ' '))
249                 op = " =";
250         else
251                 op = "";
252
253         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
254         if (pgerror) {
255                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
256                 va_end(ap);
257                 return NULL;
258         }
259
260         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s '%s'", table, newparam, op,
261                          escapebuf);
262         while ((newparam = va_arg(ap, const char *))) {
263                 newval = va_arg(ap, const char *);
264                 if (!strchr(newparam, ' '))
265                         op = " =";
266                 else
267                         op = "";
268
269                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
270                 if (pgerror) {
271                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
272                         va_end(ap);
273                         return NULL;
274                 }
275
276                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s '%s'", newparam,
277                                  op, escapebuf);
278         }
279
280         if (initfield) {
281                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
282         }
283
284         va_end(ap);
285
286         /* We now have our complete statement; Lets connect to the server and execute it. */
287         ast_mutex_lock(&pgsql_lock);
288         if (!pgsql_reconnect(database)) {
289                 ast_mutex_unlock(&pgsql_lock);
290                 return NULL;
291         }
292
293         if (!(result = PQexec(pgsqlConn, sql))) {
294                 ast_log(LOG_WARNING,
295                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
296                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
297                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
298                 ast_mutex_unlock(&pgsql_lock);
299                 return NULL;
300         } else {
301                 ExecStatusType result_status = PQresultStatus(result);
302                 if (result_status != PGRES_COMMAND_OK
303                         && result_status != PGRES_TUPLES_OK
304                         && result_status != PGRES_NONFATAL_ERROR) {
305                         ast_log(LOG_WARNING,
306                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
307                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
308                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
309                                                 PQresultErrorMessage(result), PQresStatus(result_status));
310                         ast_mutex_unlock(&pgsql_lock);
311                         return NULL;
312                 }
313         }
314
315         ast_debug(1, "PostgreSQL RealTime: Result=%p Query: %s\n", result, sql);
316
317         if ((num_rows = PQntuples(result)) > 0) {
318                 int numFields = PQnfields(result);
319                 int i = 0;
320                 int rowIndex = 0;
321                 char **fieldnames = NULL;
322
323                 ast_debug(1, "PostgreSQL RealTime: Found %d rows.\n", num_rows);
324
325                 if (!(fieldnames = ast_calloc(1, numFields * sizeof(char *)))) {
326                         ast_mutex_unlock(&pgsql_lock);
327                         PQclear(result);
328                         return NULL;
329                 }
330                 for (i = 0; i < numFields; i++)
331                         fieldnames[i] = PQfname(result, i);
332
333                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
334                         var = NULL;
335                         if (!(cat = ast_category_new("","",99999)))
336                                 continue;
337                         for (i = 0; i < numFields; i++) {
338                                 stringp = PQgetvalue(result, rowIndex, i);
339                                 while (stringp) {
340                                         chunk = strsep(&stringp, ";");
341                                         if (!ast_strlen_zero(ast_strip(chunk))) {
342                                                 if (initfield && !strcmp(initfield, fieldnames[i])) {
343                                                         ast_category_rename(cat, chunk);
344                                                 }
345                                                 var = ast_variable_new(fieldnames[i], chunk, "");
346                                                 ast_variable_append(cat, var);
347                                         }
348                                 }
349                         }
350                         ast_category_append(cfg, cat);
351                 }
352                 ast_free(fieldnames);
353         } else {
354                 ast_log(LOG_WARNING,
355                                 "PostgreSQL RealTime: Could not find any rows in table %s.\n", table);
356         }
357
358         ast_mutex_unlock(&pgsql_lock);
359         PQclear(result);
360
361         return cfg;
362 }
363
364 static int update_pgsql(const char *database, const char *table, const char *keyfield,
365                                                 const char *lookup, va_list ap)
366 {
367         PGresult *result = NULL;
368         int numrows = 0, pgerror;
369         char sql[256], escapebuf[513];
370         const char *newparam, *newval;
371
372         if (!table) {
373                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
374                 return -1;
375         }
376
377         /* Get the first parameter and first value in our list of passed paramater/value pairs */
378         newparam = va_arg(ap, const char *);
379         newval = va_arg(ap, const char *);
380         if (!newparam || !newval) {
381                 ast_log(LOG_WARNING,
382                                 "PostgreSQL RealTime: Realtime retrieval requires at least 1 parameter and 1 value to search on.\n");
383                 if (pgsqlConn) {
384                         PQfinish(pgsqlConn);
385                         pgsqlConn = NULL;
386                 };
387                 return -1;
388         }
389
390         /* Create the first part of the query using the first parameter/value pairs we just extracted
391            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
392
393         PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
394         if (pgerror) {
395                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
396                 va_end(ap);
397                 return -1;
398         }
399         snprintf(sql, sizeof(sql), "UPDATE %s SET %s = '%s'", table, newparam, escapebuf);
400
401         while ((newparam = va_arg(ap, const char *))) {
402                 newval = va_arg(ap, const char *);
403
404                 PQescapeStringConn(pgsqlConn, escapebuf, newval, (sizeof(escapebuf) - 1) / 2, &pgerror);
405                 if (pgerror) {
406                         ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", newval);
407                         va_end(ap);
408                         return -1;
409                 }
410
411                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s = '%s'", newparam,
412                                  escapebuf);
413         }
414         va_end(ap);
415
416         PQescapeStringConn(pgsqlConn, escapebuf, lookup, (sizeof(escapebuf) - 1) / 2, &pgerror);
417         if (pgerror) {
418                 ast_log(LOG_ERROR, "Postgres detected invalid input: '%s'\n", lookup);
419                 va_end(ap);
420                 return -1;
421         }
422
423         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s = '%s'", keyfield,
424                          escapebuf);
425
426         ast_debug(1, "PostgreSQL RealTime: Update SQL: %s\n", sql);
427
428         /* We now have our complete statement; Lets connect to the server and execute it. */
429         ast_mutex_lock(&pgsql_lock);
430         if (!pgsql_reconnect(database)) {
431                 ast_mutex_unlock(&pgsql_lock);
432                 return -1;
433         }
434
435         if (!(result = PQexec(pgsqlConn, sql))) {
436                 ast_log(LOG_WARNING,
437                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
438                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
439                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
440                 ast_mutex_unlock(&pgsql_lock);
441                 return -1;
442         } else {
443                 ExecStatusType result_status = PQresultStatus(result);
444                 if (result_status != PGRES_COMMAND_OK
445                         && result_status != PGRES_TUPLES_OK
446                         && result_status != PGRES_NONFATAL_ERROR) {
447                         ast_log(LOG_WARNING,
448                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
449                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
450                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
451                                                 PQresultErrorMessage(result), PQresStatus(result_status));
452                         ast_mutex_unlock(&pgsql_lock);
453                         return -1;
454                 }
455         }
456
457         numrows = atoi(PQcmdTuples(result));
458         ast_mutex_unlock(&pgsql_lock);
459
460         ast_debug(1, "PostgreSQL RealTime: Updated %d rows on table: %s\n", numrows, table);
461
462         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
463          * An integer greater than zero indicates the number of rows affected
464          * Zero indicates that no records were updated
465          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
466          */
467
468         if (numrows >= 0)
469                 return (int) numrows;
470
471         return -1;
472 }
473
474 static int store_pgsql(const char *database, const char *table, va_list ap)
475 {
476         PGresult *result = NULL;
477         Oid insertid;
478         char sql[256];
479         char params[256];
480         char vals[256];
481         char buf[256];
482         int pgresult;
483         const char *newparam, *newval;
484
485         if (!table) {
486                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
487                 return -1;
488         }
489
490         /* Get the first parameter and first value in our list of passed paramater/value pairs */
491         newparam = va_arg(ap, const char *);
492         newval = va_arg(ap, const char *);
493         if (!newparam || !newval) {
494                 ast_log(LOG_WARNING,
495                                 "PostgreSQL RealTime: Realtime storage requires at least 1 parameter and 1 value to store.\n");
496                 if (pgsqlConn) {
497                         PQfinish(pgsqlConn);
498                         pgsqlConn = NULL;
499                 };
500                 return -1;
501         }
502
503         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
504         ast_mutex_lock(&pgsql_lock);
505         if (!pgsql_reconnect(database)) {
506                 ast_mutex_unlock(&pgsql_lock);
507                 return -1;
508         }
509
510         /* Create the first part of the query using the first parameter/value pairs we just extracted
511            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
512         PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
513         snprintf(params, sizeof(params), "%s", buf);
514         PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
515         snprintf(vals, sizeof(vals), "'%s'", buf);
516         while ((newparam = va_arg(ap, const char *))) {
517                 newval = va_arg(ap, const char *);
518                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
519                 snprintf(params + strlen(params), sizeof(params) - strlen(params), ", %s", buf);
520                 PQescapeStringConn(pgsqlConn, buf, newval, sizeof(newval), &pgresult);
521                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", '%s'", buf);
522         }
523         va_end(ap);
524         snprintf(sql, sizeof(sql), "INSERT INTO (%s) VALUES (%s)", params, vals);
525
526         ast_debug(1, "PostgreSQL RealTime: Insert SQL: %s\n", sql);
527
528         if (!(result = PQexec(pgsqlConn, sql))) {
529                 ast_log(LOG_WARNING,
530                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
531                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
532                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
533                 ast_mutex_unlock(&pgsql_lock);
534                 return -1;
535         } else {
536                 ExecStatusType result_status = PQresultStatus(result);
537                 if (result_status != PGRES_COMMAND_OK
538                         && result_status != PGRES_TUPLES_OK
539                         && result_status != PGRES_NONFATAL_ERROR) {
540                         ast_log(LOG_WARNING,
541                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
542                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
543                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
544                                                 PQresultErrorMessage(result), PQresStatus(result_status));
545                         ast_mutex_unlock(&pgsql_lock);
546                         return -1;
547                 }
548         }
549
550         insertid = PQoidValue(result);
551         ast_mutex_unlock(&pgsql_lock);
552
553         ast_debug(1, "PostgreSQL RealTime: row inserted on table: %s, id: %u\n", table, insertid);
554
555         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
556          * An integer greater than zero indicates the number of rows affected
557          * Zero indicates that no records were updated
558          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
559          */
560
561         if (insertid >= 0)
562                 return (int) insertid;
563
564         return -1;
565 }
566
567 static int destroy_pgsql(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
568 {
569         PGresult *result = NULL;
570         int numrows = 0;
571         int pgresult;
572         char sql[256];
573         char buf[256], buf2[256];
574         const char *newparam, *newval;
575
576         if (!table) {
577                 ast_log(LOG_WARNING, "PostgreSQL RealTime: No table specified.\n");
578                 return -1;
579         }
580
581         /* Get the first parameter and first value in our list of passed paramater/value pairs */
582         /*newparam = va_arg(ap, const char *);
583         newval = va_arg(ap, const char *);
584         if (!newparam || !newval) {*/
585         if (ast_strlen_zero(keyfield) || ast_strlen_zero(lookup))  {
586                 ast_log(LOG_WARNING,
587                                 "PostgreSQL RealTime: Realtime destroy requires at least 1 parameter and 1 value to search on.\n");
588                 if (pgsqlConn) {
589                         PQfinish(pgsqlConn);
590                         pgsqlConn = NULL;
591                 };
592                 return -1;
593         }
594
595         /* Must connect to the server before anything else, as the escape function requires the connection handle.. */
596         ast_mutex_lock(&pgsql_lock);
597         if (!pgsql_reconnect(database)) {
598                 ast_mutex_unlock(&pgsql_lock);
599                 return -1;
600         }
601
602
603         /* Create the first part of the query using the first parameter/value pairs we just extracted
604            If there is only 1 set, then we have our query. Otherwise, loop thru the list and concat */
605
606         PQescapeStringConn(pgsqlConn, buf, keyfield, sizeof(keyfield), &pgresult);
607         PQescapeStringConn(pgsqlConn, buf2, lookup, sizeof(lookup), &pgresult);
608         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE %s = '%s'", table, buf, buf2);
609         while ((newparam = va_arg(ap, const char *))) {
610                 newval = va_arg(ap, const char *);
611                 PQescapeStringConn(pgsqlConn, buf, newparam, sizeof(newparam), &pgresult);
612                 PQescapeStringConn(pgsqlConn, buf2, newval, sizeof(newval), &pgresult);
613                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s = '%s'", buf, buf2);
614         }
615         va_end(ap);
616
617         ast_debug(1, "PostgreSQL RealTime: Delete SQL: %s\n", sql);
618
619         if (!(result = PQexec(pgsqlConn, sql))) {
620                 ast_log(LOG_WARNING,
621                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
622                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
623                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
624                 ast_mutex_unlock(&pgsql_lock);
625                 return -1;
626         } else {
627                 ExecStatusType result_status = PQresultStatus(result);
628                 if (result_status != PGRES_COMMAND_OK
629                         && result_status != PGRES_TUPLES_OK
630                         && result_status != PGRES_NONFATAL_ERROR) {
631                         ast_log(LOG_WARNING,
632                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
633                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
634                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
635                                                 PQresultErrorMessage(result), PQresStatus(result_status));
636                         ast_mutex_unlock(&pgsql_lock);
637                         return -1;
638                 }
639         }
640
641         numrows = atoi(PQcmdTuples(result));
642         ast_mutex_unlock(&pgsql_lock);
643
644         ast_debug(1, "PostgreSQL RealTime: Deleted %d rows on table: %s\n", numrows, table);
645
646         /* From http://dev.pgsql.com/doc/pgsql/en/pgsql-affected-rows.html
647          * An integer greater than zero indicates the number of rows affected
648          * Zero indicates that no records were updated
649          * -1 indicates that the query returned an error (although, if the query failed, it should have been caught above.)
650          */
651
652         if (numrows >= 0)
653                 return (int) numrows;
654
655         return -1;
656 }
657
658
659 static struct ast_config *config_pgsql(const char *database, const char *table,
660                                                                            const char *file, struct ast_config *cfg,
661                                                                            struct ast_flags flags, const char *suggested_incl)
662 {
663         PGresult *result = NULL;
664         long num_rows;
665         struct ast_variable *new_v;
666         struct ast_category *cur_cat = NULL;
667         char sqlbuf[1024] = "";
668         char *sql;
669         size_t sqlleft = sizeof(sqlbuf);
670         char last[80] = "";
671         int last_cat_metric = 0;
672
673         last[0] = '\0';
674
675         if (!file || !strcmp(file, RES_CONFIG_PGSQL_CONF)) {
676                 ast_log(LOG_WARNING, "PostgreSQL RealTime: Cannot configure myself.\n");
677                 return NULL;
678         }
679
680         ast_build_string(&sql, &sqlleft, "SELECT category, var_name, var_val, cat_metric FROM %s ", table);
681         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' and commented=0", file);
682         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
683
684         ast_debug(1, "PostgreSQL RealTime: Static SQL: %s\n", sqlbuf);
685
686         /* We now have our complete statement; Lets connect to the server and execute it. */
687         ast_mutex_lock(&pgsql_lock);
688         if (!pgsql_reconnect(database)) {
689                 ast_mutex_unlock(&pgsql_lock);
690                 return NULL;
691         }
692
693         if (!(result = PQexec(pgsqlConn, sqlbuf))) {
694                 ast_log(LOG_WARNING,
695                                 "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
696                 ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
697                 ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s\n", PQerrorMessage(pgsqlConn));
698                 ast_mutex_unlock(&pgsql_lock);
699                 return NULL;
700         } else {
701                 ExecStatusType result_status = PQresultStatus(result);
702                 if (result_status != PGRES_COMMAND_OK
703                         && result_status != PGRES_TUPLES_OK
704                         && result_status != PGRES_NONFATAL_ERROR) {
705                         ast_log(LOG_WARNING,
706                                         "PostgreSQL RealTime: Failed to query database. Check debug for more info.\n");
707                         ast_debug(1, "PostgreSQL RealTime: Query: %s\n", sql);
708                         ast_debug(1, "PostgreSQL RealTime: Query Failed because: %s (%s)\n",
709                                                 PQresultErrorMessage(result), PQresStatus(result_status));
710                         ast_mutex_unlock(&pgsql_lock);
711                         return NULL;
712                 }
713         }
714
715         if ((num_rows = PQntuples(result)) > 0) {
716                 int rowIndex = 0;
717
718                 ast_debug(1, "PostgreSQL RealTime: Found %ld rows.\n", num_rows);
719
720                 for (rowIndex = 0; rowIndex < num_rows; rowIndex++) {
721                         char *field_category = PQgetvalue(result, rowIndex, 0);
722                         char *field_var_name = PQgetvalue(result, rowIndex, 1);
723                         char *field_var_val = PQgetvalue(result, rowIndex, 2);
724                         char *field_cat_metric = PQgetvalue(result, rowIndex, 3);
725                         if (!strcmp(field_var_name, "#include")) {
726                                 if (!ast_config_internal_load(field_var_val, cfg, flags, "")) {
727                                         PQclear(result);
728                                         ast_mutex_unlock(&pgsql_lock);
729                                         return NULL;
730                                 }
731                                 continue;
732                         }
733
734                         if (strcmp(last, field_category) || last_cat_metric != atoi(field_cat_metric)) {
735                                 cur_cat = ast_category_new(field_category, "", 99999);
736                                 if (!cur_cat)
737                                         break;
738                                 strcpy(last, field_category);
739                                 last_cat_metric = atoi(field_cat_metric);
740                                 ast_category_append(cfg, cur_cat);
741                         }
742                         new_v = ast_variable_new(field_var_name, field_var_val, "");
743                         ast_variable_append(cur_cat, new_v);
744                 }
745         } else {
746                 ast_log(LOG_WARNING,
747                                 "PostgreSQL RealTime: Could not find config '%s' in database.\n", file);
748         }
749
750         PQclear(result);
751         ast_mutex_unlock(&pgsql_lock);
752
753         return cfg;
754 }
755
756 static struct ast_config_engine pgsql_engine = {
757         .name = "pgsql",
758         .load_func = config_pgsql,
759         .realtime_func = realtime_pgsql,
760         .realtime_multi_func = realtime_multi_pgsql,
761         .store_func = store_pgsql,
762         .destroy_func = destroy_pgsql,
763         .update_func = update_pgsql
764 };
765
766 static int load_module(void)
767 {
768         if(!parse_config(0))
769                 return AST_MODULE_LOAD_DECLINE;
770
771         ast_config_engine_register(&pgsql_engine);
772         ast_verb(1, "PostgreSQL RealTime driver loaded.\n");
773         ast_cli_register_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
774
775         return 0;
776 }
777
778 static int unload_module(void)
779 {
780         /* Acquire control before doing anything to the module itself. */
781         ast_mutex_lock(&pgsql_lock);
782
783         if (pgsqlConn) {
784                 PQfinish(pgsqlConn);
785                 pgsqlConn = NULL;
786         }
787         ast_cli_unregister_multiple(cli_realtime, sizeof(cli_realtime) / sizeof(struct ast_cli_entry));
788         ast_config_engine_deregister(&pgsql_engine);
789         ast_verb(1, "PostgreSQL RealTime unloaded.\n");
790
791         /* Unlock so something else can destroy the lock. */
792         ast_mutex_unlock(&pgsql_lock);
793
794         return 0;
795 }
796
797 static int reload(void)
798 {
799         parse_config(1);
800
801         return 0;
802 }
803
804 static int parse_config(int reload)
805 {
806         struct ast_config *config;
807         const char *s;
808         struct ast_flags config_flags = { reload ? CONFIG_FLAG_FILEUNCHANGED : 0 };
809
810         if ((config = ast_config_load(RES_CONFIG_PGSQL_CONF, config_flags)) == CONFIG_STATUS_FILEUNCHANGED)
811                 return 0;
812
813         if (!config) {
814                 ast_log(LOG_WARNING, "Unable to load config %s\n", RES_CONFIG_PGSQL_CONF);
815                 return 0;
816         }
817
818         ast_mutex_lock(&pgsql_lock);
819
820         if (pgsqlConn) {
821                 PQfinish(pgsqlConn);
822                 pgsqlConn = NULL;
823         }
824
825         if (!(s = ast_variable_retrieve(config, "general", "dbuser"))) {
826                 ast_log(LOG_WARNING,
827                                 "PostgreSQL RealTime: No database user found, using 'asterisk' as default.\n");
828                 strcpy(dbuser, "asterisk");
829         } else {
830                 ast_copy_string(dbuser, s, sizeof(dbuser));
831         }
832
833         if (!(s = ast_variable_retrieve(config, "general", "dbpass"))) {
834                 ast_log(LOG_WARNING,
835                                 "PostgreSQL RealTime: No database password found, using 'asterisk' as default.\n");
836                 strcpy(dbpass, "asterisk");
837         } else {
838                 ast_copy_string(dbpass, s, sizeof(dbpass));
839         }
840
841         if (!(s = ast_variable_retrieve(config, "general", "dbhost"))) {
842                 ast_log(LOG_WARNING,
843                                 "PostgreSQL RealTime: No database host found, using localhost via socket.\n");
844                 dbhost[0] = '\0';
845         } else {
846                 ast_copy_string(dbhost, s, sizeof(dbhost));
847         }
848
849         if (!(s = ast_variable_retrieve(config, "general", "dbname"))) {
850                 ast_log(LOG_WARNING,
851                                 "PostgreSQL RealTime: No database name found, using 'asterisk' as default.\n");
852                 strcpy(dbname, "asterisk");
853         } else {
854                 ast_copy_string(dbname, s, sizeof(dbname));
855         }
856
857         if (!(s = ast_variable_retrieve(config, "general", "dbport"))) {
858                 ast_log(LOG_WARNING,
859                                 "PostgreSQL RealTime: No database port found, using 5432 as default.\n");
860                 dbport = 5432;
861         } else {
862                 dbport = atoi(s);
863         }
864
865         if (!ast_strlen_zero(dbhost)) {
866                 /* No socket needed */
867         } else if (!(s = ast_variable_retrieve(config, "general", "dbsock"))) {
868                 ast_log(LOG_WARNING,
869                                 "PostgreSQL RealTime: No database socket found, using '/tmp/pgsql.sock' as default.\n");
870                 strcpy(dbsock, "/tmp/pgsql.sock");
871         } else {
872                 ast_copy_string(dbsock, s, sizeof(dbsock));
873         }
874         ast_config_destroy(config);
875
876         if (option_debug) {
877                 if (!ast_strlen_zero(dbhost)) {
878                         ast_debug(1, "PostgreSQL RealTime Host: %s\n", dbhost);
879                         ast_debug(1, "PostgreSQL RealTime Port: %i\n", dbport);
880                 } else {
881                         ast_debug(1, "PostgreSQL RealTime Socket: %s\n", dbsock);
882                 }
883                 ast_debug(1, "PostgreSQL RealTime User: %s\n", dbuser);
884                 ast_debug(1, "PostgreSQL RealTime Password: %s\n", dbpass);
885                 ast_debug(1, "PostgreSQL RealTime DBName: %s\n", dbname);
886         }
887
888         if (!pgsql_reconnect(NULL)) {
889                 ast_log(LOG_WARNING,
890                                 "PostgreSQL RealTime: Couldn't establish connection. Check debug.\n");
891                 ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQerrorMessage(pgsqlConn));
892         }
893
894         ast_verb(2, "PostgreSQL RealTime reloaded.\n");
895
896         /* Done reloading. Release lock so others can now use driver. */
897         ast_mutex_unlock(&pgsql_lock);
898
899         return 1;
900 }
901
902 static int pgsql_reconnect(const char *database)
903 {
904         char my_database[50];
905
906         ast_copy_string(my_database, S_OR(database, dbname), sizeof(my_database));
907
908         /* mutex lock should have been locked before calling this function. */
909
910         if (pgsqlConn && PQstatus(pgsqlConn) != CONNECTION_OK) {
911                 PQfinish(pgsqlConn);
912                 pgsqlConn = NULL;
913         }
914
915         if ((!pgsqlConn) && (!ast_strlen_zero(dbhost) || !ast_strlen_zero(dbsock)) && !ast_strlen_zero(dbuser) && !ast_strlen_zero(dbpass) && !ast_strlen_zero(my_database)) {
916                 char *connInfo = NULL;
917                 unsigned int size = 100 + strlen(dbhost)
918                         + strlen(dbuser)
919                         + strlen(dbpass)
920                         + strlen(my_database);
921                 
922                 if (!(connInfo = ast_malloc(size)))
923                         return 0;
924                 
925                 sprintf(connInfo, "host=%s port=%d dbname=%s user=%s password=%s",
926                                         dbhost, dbport, my_database, dbuser, dbpass);
927                 ast_debug(1, "%u connInfo=%s\n", size, connInfo);
928                 pgsqlConn = PQconnectdb(connInfo);
929                 ast_debug(1, "%u connInfo=%s\n", size, connInfo);
930                 ast_free(connInfo);
931                 connInfo = NULL;
932                 ast_debug(1, "pgsqlConn=%p\n", pgsqlConn);
933                 if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
934                         ast_debug(1, "PostgreSQL RealTime: Successfully connected to database.\n");
935                         connect_time = time(NULL);
936                         return 1;
937                 } else {
938                         ast_log(LOG_ERROR,
939                                         "PostgreSQL RealTime: Failed to connect database server %s on %s. Check debug for more info.\n",
940                                         dbname, dbhost);
941                         ast_debug(1, "PostgreSQL RealTime: Cannot Connect: %s\n", PQresultErrorMessage(NULL));
942                         return 0;
943                 }
944         } else {
945                 ast_debug(1, "PostgreSQL RealTime: Everything is fine.\n");
946                 return 1;
947         }
948 }
949
950 static char *handle_cli_realtime_pgsql_status(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
951 {
952         char status[256], status2[100] = "";
953         int ctime = time(NULL) - connect_time;
954
955         switch (cmd) {
956         case CLI_INIT:
957                 e->command = "realtime pgsql status";
958                 e->usage =
959                         "Usage: realtime pgsql status\n"
960                         "       Shows connection information for the PostgreSQL RealTime driver\n";
961                 return NULL;
962         case CLI_GENERATE:
963                 return NULL;
964         }
965
966         if (a->argc != 3)
967                 return CLI_SHOWUSAGE;
968
969         if (pgsqlConn && PQstatus(pgsqlConn) == CONNECTION_OK) {
970                 if (!ast_strlen_zero(dbhost))
971                         snprintf(status, 255, "Connected to %s@%s, port %d", dbname, dbhost, dbport);
972                 else if (!ast_strlen_zero(dbsock))
973                         snprintf(status, 255, "Connected to %s on socket file %s", dbname, dbsock);
974                 else
975                         snprintf(status, 255, "Connected to %s@%s", dbname, dbhost);
976
977                 if (!ast_strlen_zero(dbuser))
978                         snprintf(status2, 99, " with username %s", dbuser);
979
980                 if (ctime > 31536000)
981                         ast_cli(a->fd, "%s%s for %d years, %d days, %d hours, %d minutes, %d seconds.\n",
982                                         status, status2, ctime / 31536000, (ctime % 31536000) / 86400,
983                                         (ctime % 86400) / 3600, (ctime % 3600) / 60, ctime % 60);
984                 else if (ctime > 86400)
985                         ast_cli(a->fd, "%s%s for %d days, %d hours, %d minutes, %d seconds.\n", status,
986                                         status2, ctime / 86400, (ctime % 86400) / 3600, (ctime % 3600) / 60,
987                                         ctime % 60);
988                 else if (ctime > 3600)
989                         ast_cli(a->fd, "%s%s for %d hours, %d minutes, %d seconds.\n", status, status2,
990                                         ctime / 3600, (ctime % 3600) / 60, ctime % 60);
991                 else if (ctime > 60)
992                         ast_cli(a->fd, "%s%s for %d minutes, %d seconds.\n", status, status2, ctime / 60,
993                                         ctime % 60);
994                 else
995                         ast_cli(a->fd, "%s%s for %d seconds.\n", status, status2, ctime);
996
997                 return CLI_SUCCESS;
998         } else {
999                 return CLI_FAILURE;
1000         }
1001 }
1002
1003 /* needs usecount semantics defined */
1004 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "PostgreSQL RealTime Configuration Driver",
1005                 .load = load_module,
1006                 .unload = unload_module,
1007                 .reload = reload
1008                );