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