With respect to bug 7862, the syntax and description are misleading to users. the...
[asterisk/asterisk.git] / funcs / func_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (c) 2005, 2006 Tilghman Lesher
5  *
6  * Tilghman Lesher <func_odbc__200508@the-tilghman.com>
7  *
8  * See http://www.asterisk.org for more information about
9  * the Asterisk project. Please do not directly contact
10  * any of the maintainers of this project for assistance;
11  * the project provides a web site, mailing lists and IRC
12  * channels for your use.
13  *
14  * This program is free software, distributed under the terms of
15  * the GNU General Public License Version 2. See the LICENSE file
16  * at the top of the source tree.
17  */
18
19 /*!
20  * \file
21  *
22  * \brief ODBC lookups
23  *
24  * \author Tilghman Lesher <func_odbc__200508@the-tilghman.com>
25  */
26
27 /*** MODULEINFO
28         <depend>unixodbc</depend>
29  ***/
30
31 #include "asterisk.h"
32
33 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
34
35 #include <sys/types.h>
36 #include <stdio.h>
37 #include <stdlib.h>
38 #include <unistd.h>
39 #include <string.h>
40
41 #include "asterisk/module.h"
42 #include "asterisk/file.h"
43 #include "asterisk/logger.h"
44 #include "asterisk/options.h"
45 #include "asterisk/channel.h"
46 #include "asterisk/pbx.h"
47 #include "asterisk/module.h"
48 #include "asterisk/config.h"
49 #include "asterisk/res_odbc.h"
50 #include "asterisk/app.h"
51
52 static char *config = "func_odbc.conf";
53
54 enum {
55         OPT_ESCAPECOMMAS =      (1 << 0),
56 } odbc_option_flags;
57
58 struct acf_odbc_query {
59         AST_LIST_ENTRY(acf_odbc_query) list;
60         char dsn[30];
61         char sql_read[2048];
62         char sql_write[2048];
63         unsigned int flags;
64         struct ast_custom_function *acf;
65 };
66
67 AST_LIST_HEAD_STATIC(queries, acf_odbc_query);
68
69 static SQLHSTMT generic_prepare(struct odbc_obj *obj, void *data)
70 {
71         int res;
72         char *sql = data;
73         SQLHSTMT stmt;
74
75         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
76         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
77                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
78                 return NULL;
79         }
80
81         res = SQLPrepare(stmt, (unsigned char *)sql, SQL_NTS);
82         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
83                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
84                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
85                 return NULL;
86         }
87
88         return stmt;
89 }
90
91 /*
92  * Master control routine
93  */
94 static int acf_odbc_write(struct ast_channel *chan, char *cmd, char *s, const char *value)
95 {
96         struct odbc_obj *obj;
97         struct acf_odbc_query *query;
98         char *t, buf[2048]="", varname[15];
99         int i;
100         AST_DECLARE_APP_ARGS(values,
101                 AST_APP_ARG(field)[100];
102         );
103         AST_DECLARE_APP_ARGS(args,
104                 AST_APP_ARG(field)[100];
105         );
106         SQLHSTMT stmt;
107         SQLINTEGER rows=0;
108
109         AST_LIST_LOCK(&queries);
110         AST_LIST_TRAVERSE(&queries, query, list) {
111                 if (!strcmp(query->acf->name, cmd)) {
112                         break;
113                 }
114         }
115
116         if (!query) {
117                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
118                 AST_LIST_UNLOCK(&queries);
119                 return -1;
120         }
121
122         obj = odbc_request_obj(query->dsn, 0);
123
124         if (!obj) {
125                 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", query->dsn);
126                 AST_LIST_UNLOCK(&queries);
127                 return -1;
128         }
129
130         /* Parse our arguments */
131         t = value ? ast_strdupa(value) : "";
132
133         if (!s || !t) {
134                 ast_log(LOG_ERROR, "Out of memory\n");
135                 AST_LIST_UNLOCK(&queries);
136                 return -1;
137         }
138
139         AST_STANDARD_APP_ARGS(args, s);
140         for (i = 0; i < args.argc; i++) {
141                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
142                 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
143         }
144
145         /* Parse values, just like arguments */
146         /* Can't use the pipe, because app Set removes them */
147         AST_NONSTANDARD_APP_ARGS(values, t, ',');
148         for (i = 0; i < values.argc; i++) {
149                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
150                 pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
151         }
152
153         /* Additionally set the value as a whole (but push an empty string if value is NULL) */
154         pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
155
156         pbx_substitute_variables_helper(chan, query->sql_write, buf, sizeof(buf) - 1);
157
158         /* Restore prior values */
159         for (i = 0; i < args.argc; i++) {
160                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
161                 pbx_builtin_setvar_helper(chan, varname, NULL);
162         }
163
164         for (i = 0; i < values.argc; i++) {
165                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
166                 pbx_builtin_setvar_helper(chan, varname, NULL);
167         }
168         pbx_builtin_setvar_helper(chan, "VALUE", NULL);
169
170         AST_LIST_UNLOCK(&queries);
171
172         stmt = odbc_prepare_and_execute(obj, generic_prepare, buf);
173
174         if (stmt) {
175                 /* Rows affected */
176                 SQLRowCount(stmt, &rows);
177         }
178
179         /* Output the affected rows, for all cases.  In the event of failure, we
180          * flag this as -1 rows.  Note that this is different from 0 affected rows
181          * which would be the case if we succeeded in our query, but the values did
182          * not change. */
183         snprintf(varname, sizeof(varname), "%d", (int)rows);
184         pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
185
186         if (stmt)
187                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
188         if (obj)
189                 odbc_release_obj(obj);
190
191         return 0;
192 }
193
194 static int acf_odbc_read(struct ast_channel *chan, char *cmd, char *s, char *buf, size_t len)
195 {
196         struct odbc_obj *obj;
197         struct acf_odbc_query *query;
198         char sql[2048] = "", varname[15];
199         int res, x, buflen = 0, escapecommas;
200         AST_DECLARE_APP_ARGS(args,
201                 AST_APP_ARG(field)[100];
202         );
203         SQLHSTMT stmt;
204         SQLSMALLINT colcount=0;
205         SQLINTEGER indicator;
206
207         AST_LIST_LOCK(&queries);
208         AST_LIST_TRAVERSE(&queries, query, list) {
209                 if (!strcmp(query->acf->name, cmd)) {
210                         break;
211                 }
212         }
213
214         if (!query) {
215                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
216                 AST_LIST_UNLOCK(&queries);
217                 return -1;
218         }
219
220         obj = odbc_request_obj(query->dsn, 0);
221
222         if (!obj) {
223                 ast_log(LOG_ERROR, "No such DSN registered (or out of connections): %s (check res_odbc.conf)\n", query->dsn);
224                 AST_LIST_UNLOCK(&queries);
225                 return -1;
226         }
227
228         AST_STANDARD_APP_ARGS(args, s);
229         for (x = 0; x < args.argc; x++) {
230                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
231                 pbx_builtin_pushvar_helper(chan, varname, args.field[x]);
232         }
233
234         pbx_substitute_variables_helper(chan, query->sql_read, sql, sizeof(sql) - 1);
235
236         /* Restore prior values */
237         for (x = 0; x < args.argc; x++) {
238                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
239                 pbx_builtin_setvar_helper(chan, varname, NULL);
240         }
241
242         /* Save this flag, so we can release the lock */
243         escapecommas = ast_test_flag(query, OPT_ESCAPECOMMAS);
244
245         AST_LIST_UNLOCK(&queries);
246
247         stmt = odbc_prepare_and_execute(obj, generic_prepare, sql);
248
249         if (!stmt) {
250                 odbc_release_obj(obj);
251                 return -1;
252         }
253
254         res = SQLNumResultCols(stmt, &colcount);
255         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
256                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
257                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
258                 odbc_release_obj(obj);
259                 return -1;
260         }
261
262         *buf = '\0';
263
264         res = SQLFetch(stmt);
265         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
266                 int res1 = -1;
267                 if (res == SQL_NO_DATA) {
268                         if (option_verbose > 3) {
269                                 ast_verbose(VERBOSE_PREFIX_4 "Found no rows [%s]\n", sql);
270                         }
271                         res1 = 0;
272                 } else if (option_verbose > 3) {
273                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql);
274                 }
275                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
276                 odbc_release_obj(obj);
277                 return res1;
278         }
279
280         for (x = 0; x < colcount; x++) {
281                 int i;
282                 char coldata[256];
283
284                 buflen = strlen(buf);
285                 res = SQLGetData(stmt, x + 1, SQL_CHAR, coldata, sizeof(coldata), &indicator);
286                 if (indicator == SQL_NULL_DATA) {
287                         coldata[0] = '\0';
288                         res = SQL_SUCCESS;
289                 }
290
291                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
292                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
293                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
294                         odbc_release_obj(obj);
295                         return -1;
296                 }
297
298                 /* Copy data, encoding '\' and ',' for the argument parser */
299                 for (i = 0; i < sizeof(coldata); i++) {
300                         if (escapecommas && (coldata[i] == '\\' || coldata[i] == ',')) {
301                                 buf[buflen++] = '\\';
302                         }
303                         buf[buflen++] = coldata[i];
304
305                         if (buflen >= len - 2)
306                                 break;
307
308                         if (coldata[i] == '\0')
309                                 break;
310                 }
311
312                 buf[buflen - 1] = ',';
313                 buf[buflen] = '\0';
314         }
315         /* Trim trailing comma */
316         buf[buflen - 1] = '\0';
317
318         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
319         odbc_release_obj(obj);
320         return 0;
321 }
322
323 static int acf_escape(struct ast_channel *chan, char *cmd, char *data, char *buf, size_t len)
324 {
325         char *out = buf;
326
327         for (; *data && out - buf < len; data++) {
328                 if (*data == '\'') {
329                         *out = '\'';
330                         out++;
331                 }
332                 *out++ = *data;
333         }
334         *out = '\0';
335
336         return 0;
337 }
338
339 static struct ast_custom_function escape_function = {
340         .name = "SQL_ESC",
341         .synopsis = "Escapes single ticks for use in SQL statements",
342         .syntax = "SQL_ESC(<string>)",
343         .desc =
344 "Used in SQL templates to escape data which may contain single ticks (') which\n"
345 "are otherwise used to delimit data.  For example:\n"
346 "SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'\n",
347         .read = acf_escape,
348         .write = NULL,
349 };
350
351 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
352 {
353         char *tmp;
354
355         if (!cfg || !catg) {
356                 return -1;
357         }
358
359         *query = ast_calloc(1, sizeof(struct acf_odbc_query));
360         if (! (*query))
361                 return -1;
362
363         if ((tmp = ast_variable_retrieve(cfg, catg, "dsn"))) {
364                 ast_copy_string((*query)->dsn, tmp, sizeof((*query)->dsn));
365         } else {
366                 return -1;
367         }
368
369         if ((tmp = ast_variable_retrieve(cfg, catg, "read"))) {
370                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
371         }
372
373         if ((tmp = ast_variable_retrieve(cfg, catg, "write"))) {
374                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
375         }
376
377         /* Allow escaping of embedded commas in fields to be turned off */
378         ast_set_flag((*query), OPT_ESCAPECOMMAS);
379         if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
380                 if (ast_false(tmp))
381                         ast_clear_flag((*query), OPT_ESCAPECOMMAS);
382         }
383
384         (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
385         if (! (*query)->acf) {
386                 free(*query);
387                 return -1;
388         }
389
390         if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
391                 asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg);
392         } else {
393                 asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg);
394         }
395
396         if (!((*query)->acf->name)) {
397                 free((*query)->acf);
398                 free(*query);
399                 return -1;
400         }
401
402         asprintf((char **)&((*query)->acf->syntax), "%s(<arg1>[...[,<argN>]])", (*query)->acf->name);
403
404         if (!((*query)->acf->syntax)) {
405                 free((char *)(*query)->acf->name);
406                 free((*query)->acf);
407                 free(*query);
408                 return -1;
409         }
410
411         (*query)->acf->synopsis = "Runs the referenced query with the specified arguments";
412         if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
413                 asprintf((char **)&((*query)->acf->desc),
414                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
415                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
416                                         "${ARG2}, ... ${ARGn}.  When setting the function, the values are provided\n"
417                                         "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
418                                         "\nRead:\n%s\n\nWrite:\n%s\n",
419                                         (*query)->sql_read,
420                                         (*query)->sql_write);
421         } else if (!ast_strlen_zero((*query)->sql_read)) {
422                 asprintf((char **)&((*query)->acf->desc),
423                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
424                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
425                                         "${ARG2}, ... ${ARGn}.  This function may only be read, not set.\n\nSQL:\n%s\n",
426                                         (*query)->sql_read);
427         } else if (!ast_strlen_zero((*query)->sql_write)) {
428                 asprintf((char **)&((*query)->acf->desc),
429                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
430                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
431                                         "${ARG2}, ... ${ARGn}.  The values are provided either in whole as\n"
432                                         "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
433                                         "This function may only be set.\nSQL:\n%s\n",
434                                         (*query)->sql_write);
435         }
436
437         /* Could be out of memory, or could be we have neither sql_read nor sql_write */
438         if (! ((*query)->acf->desc)) {
439                 free((char *)(*query)->acf->syntax);
440                 free((char *)(*query)->acf->name);
441                 free((*query)->acf);
442                 free(*query);
443                 return -1;
444         }
445
446         if (ast_strlen_zero((*query)->sql_read)) {
447                 (*query)->acf->read = NULL;
448         } else {
449                 (*query)->acf->read = acf_odbc_read;
450         }
451
452         if (ast_strlen_zero((*query)->sql_write)) {
453                 (*query)->acf->write = NULL;
454         } else {
455                 (*query)->acf->write = acf_odbc_write;
456         }
457
458         return 0;
459 }
460
461 static int free_acf_query(struct acf_odbc_query *query)
462 {
463         if (query) {
464                 if (query->acf) {
465                         if (query->acf->name)
466                                 free((char *)query->acf->name);
467                         if (query->acf->syntax)
468                                 free((char *)query->acf->syntax);
469                         if (query->acf->desc)
470                                 free((char *)query->acf->desc);
471                         free(query->acf);
472                 }
473                 free(query);
474         }
475         return 0;
476 }
477
478 static int odbc_load_module(void)
479 {
480         int res = 0;
481         struct ast_config *cfg;
482         char *catg;
483
484         AST_LIST_LOCK(&queries);
485
486         cfg = ast_config_load(config);
487         if (!cfg) {
488                 ast_log(LOG_NOTICE, "Unable to load config for func_odbc: %s\n", config);
489                 AST_LIST_UNLOCK(&queries);
490                 return AST_MODULE_LOAD_DECLINE;
491         }
492
493         for (catg = ast_category_browse(cfg, NULL);
494              catg;
495              catg = ast_category_browse(cfg, catg)) {
496                 struct acf_odbc_query *query = NULL;
497
498                 if (init_acf_query(cfg, catg, &query)) {
499                         ast_log(LOG_ERROR, "Out of memory\n");
500                         free_acf_query(query);
501                 } else {
502                         AST_LIST_INSERT_HEAD(&queries, query, list);
503                         ast_custom_function_register(query->acf);
504                 }
505         }
506
507         ast_config_destroy(cfg);
508         ast_custom_function_register(&escape_function);
509
510         AST_LIST_UNLOCK(&queries);
511         return res;
512 }
513
514 static int odbc_unload_module(void)
515 {
516         struct acf_odbc_query *query;
517
518         AST_LIST_LOCK(&queries);
519         while (!AST_LIST_EMPTY(&queries)) {
520                 query = AST_LIST_REMOVE_HEAD(&queries, list);
521                 ast_custom_function_unregister(query->acf);
522                 free_acf_query(query);
523         }
524
525         ast_custom_function_unregister(&escape_function);
526
527         /* Allow any threads waiting for this lock to pass (avoids a race) */
528         AST_LIST_UNLOCK(&queries);
529         AST_LIST_LOCK(&queries);
530
531         AST_LIST_UNLOCK(&queries);
532         return 0;
533 }
534
535 static int reload(void)
536 {
537         int res = 0;
538         struct ast_config *cfg;
539         struct acf_odbc_query *oldquery;
540         char *catg;
541
542         AST_LIST_LOCK(&queries);
543
544         while (!AST_LIST_EMPTY(&queries)) {
545                 oldquery = AST_LIST_REMOVE_HEAD(&queries, list);
546                 ast_custom_function_unregister(oldquery->acf);
547                 free_acf_query(oldquery);
548         }
549
550         cfg = ast_config_load(config);
551         if (!cfg) {
552                 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
553                 goto reload_out;
554         }
555
556         for (catg = ast_category_browse(cfg, NULL);
557              catg;
558              catg = ast_category_browse(cfg, catg)) {
559                 struct acf_odbc_query *query = NULL;
560
561                 if (init_acf_query(cfg, catg, &query)) {
562                         ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
563                 } else {
564                         AST_LIST_INSERT_HEAD(&queries, query, list);
565                         ast_custom_function_register(query->acf);
566                 }
567         }
568
569         ast_config_destroy(cfg);
570 reload_out:
571         AST_LIST_UNLOCK(&queries);
572         return res;
573 }
574
575 static int unload_module(void)
576 {
577         return odbc_unload_module();
578 }
579
580 static int load_module(void)
581 {
582         return odbc_load_module();
583 }
584
585 /* XXX need to revise usecount - set if query_lock is set */
586
587 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC lookups",
588                 .load = load_module,
589                 .unload = unload_module,
590                 .reload = reload,
591                );
592