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