update to reflect recent rtp changes
[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         }
314         /* Trim trailing comma */
315         buf[buflen - 1] = '\0';
316
317         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
318         odbc_release_obj(obj);
319         return 0;
320 }
321
322 static int acf_escape(struct ast_channel *chan, char *cmd, char *data, char *buf, size_t len)
323 {
324         char *out = buf;
325
326         for (; *data && out - buf < len; data++) {
327                 if (*data == '\'') {
328                         *out = '\'';
329                         out++;
330                 }
331                 *out++ = *data;
332         }
333         *out = '\0';
334
335         return 0;
336 }
337
338 static struct ast_custom_function escape_function = {
339         .name = "SQL_ESC",
340         .synopsis = "Escapes single ticks for use in SQL statements",
341         .syntax = "SQL_ESC(<string>)",
342         .desc =
343 "Used in SQL templates to escape data which may contain single ticks (') which\n"
344 "are otherwise used to delimit data.  For example:\n"
345 "SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'\n",
346         .read = acf_escape,
347         .write = NULL,
348 };
349
350 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
351 {
352         char *tmp;
353
354         if (!cfg || !catg) {
355                 return -1;
356         }
357
358         *query = ast_calloc(1, sizeof(struct acf_odbc_query));
359         if (! (*query))
360                 return -1;
361
362         if ((tmp = ast_variable_retrieve(cfg, catg, "dsn"))) {
363                 ast_copy_string((*query)->dsn, tmp, sizeof((*query)->dsn));
364         } else {
365                 return -1;
366         }
367
368         if ((tmp = ast_variable_retrieve(cfg, catg, "read"))) {
369                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
370         }
371
372         if ((tmp = ast_variable_retrieve(cfg, catg, "write"))) {
373                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
374         }
375
376         /* Allow escaping of embedded commas in fields to be turned off */
377         ast_set_flag((*query), OPT_ESCAPECOMMAS);
378         if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
379                 if (ast_false(tmp))
380                         ast_clear_flag((*query), OPT_ESCAPECOMMAS);
381         }
382
383         (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
384         if (! (*query)->acf) {
385                 free(*query);
386                 return -1;
387         }
388
389         if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
390                 asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg);
391         } else {
392                 asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg);
393         }
394
395         if (!((*query)->acf->name)) {
396                 free((*query)->acf);
397                 free(*query);
398                 return -1;
399         }
400
401         asprintf((char **)&((*query)->acf->syntax), "%s(<arg1>[...[,<argN>]])", (*query)->acf->name);
402
403         if (!((*query)->acf->syntax)) {
404                 free((char *)(*query)->acf->name);
405                 free((*query)->acf);
406                 free(*query);
407                 return -1;
408         }
409
410         (*query)->acf->synopsis = "Runs the referenced query with the specified arguments";
411         if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
412                 asprintf((char **)&((*query)->acf->desc),
413                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
414                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
415                                         "${ARG2}, ... ${ARGn}.  When setting the function, the values are provided\n"
416                                         "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
417                                         "\nRead:\n%s\n\nWrite:\n%s\n",
418                                         (*query)->sql_read,
419                                         (*query)->sql_write);
420         } else if (!ast_strlen_zero((*query)->sql_read)) {
421                 asprintf((char **)&((*query)->acf->desc),
422                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
423                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
424                                         "${ARG2}, ... ${ARGn}.  This function may only be read, not set.\n\nSQL:\n%s\n",
425                                         (*query)->sql_read);
426         } else if (!ast_strlen_zero((*query)->sql_write)) {
427                 asprintf((char **)&((*query)->acf->desc),
428                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
429                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
430                                         "${ARG2}, ... ${ARGn}.  The values are provided either in whole as\n"
431                                         "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
432                                         "This function may only be set.\nSQL:\n%s\n",
433                                         (*query)->sql_write);
434         }
435
436         /* Could be out of memory, or could be we have neither sql_read nor sql_write */
437         if (! ((*query)->acf->desc)) {
438                 free((char *)(*query)->acf->syntax);
439                 free((char *)(*query)->acf->name);
440                 free((*query)->acf);
441                 free(*query);
442                 return -1;
443         }
444
445         if (ast_strlen_zero((*query)->sql_read)) {
446                 (*query)->acf->read = NULL;
447         } else {
448                 (*query)->acf->read = acf_odbc_read;
449         }
450
451         if (ast_strlen_zero((*query)->sql_write)) {
452                 (*query)->acf->write = NULL;
453         } else {
454                 (*query)->acf->write = acf_odbc_write;
455         }
456
457         return 0;
458 }
459
460 static int free_acf_query(struct acf_odbc_query *query)
461 {
462         if (query) {
463                 if (query->acf) {
464                         if (query->acf->name)
465                                 free((char *)query->acf->name);
466                         if (query->acf->syntax)
467                                 free((char *)query->acf->syntax);
468                         if (query->acf->desc)
469                                 free((char *)query->acf->desc);
470                         free(query->acf);
471                 }
472                 free(query);
473         }
474         return 0;
475 }
476
477 static int odbc_load_module(void)
478 {
479         int res = 0;
480         struct ast_config *cfg;
481         char *catg;
482
483         AST_LIST_LOCK(&queries);
484
485         cfg = ast_config_load(config);
486         if (!cfg) {
487                 ast_log(LOG_NOTICE, "Unable to load config for func_odbc: %s\n", config);
488                 AST_LIST_UNLOCK(&queries);
489                 return 0;
490         }
491
492         for (catg = ast_category_browse(cfg, NULL);
493              catg;
494              catg = ast_category_browse(cfg, catg)) {
495                 struct acf_odbc_query *query = NULL;
496
497                 if (init_acf_query(cfg, catg, &query)) {
498                         ast_log(LOG_ERROR, "Out of memory\n");
499                         free_acf_query(query);
500                 } else {
501                         AST_LIST_INSERT_HEAD(&queries, query, list);
502                         ast_custom_function_register(query->acf);
503                 }
504         }
505
506         ast_config_destroy(cfg);
507         ast_custom_function_register(&escape_function);
508
509         AST_LIST_UNLOCK(&queries);
510         return res;
511 }
512
513 static int odbc_unload_module(void)
514 {
515         struct acf_odbc_query *query;
516
517         AST_LIST_LOCK(&queries);
518         while (!AST_LIST_EMPTY(&queries)) {
519                 query = AST_LIST_REMOVE_HEAD(&queries, list);
520                 ast_custom_function_unregister(query->acf);
521                 free_acf_query(query);
522         }
523
524         ast_custom_function_unregister(&escape_function);
525
526         /* Allow any threads waiting for this lock to pass (avoids a race) */
527         AST_LIST_UNLOCK(&queries);
528         AST_LIST_LOCK(&queries);
529
530         AST_LIST_UNLOCK(&queries);
531         return 0;
532 }
533
534 static int reload(void)
535 {
536         int res = 0;
537         struct ast_config *cfg;
538         struct acf_odbc_query *oldquery;
539         char *catg;
540
541         AST_LIST_LOCK(&queries);
542
543         while (!AST_LIST_EMPTY(&queries)) {
544                 oldquery = AST_LIST_REMOVE_HEAD(&queries, list);
545                 ast_custom_function_unregister(oldquery->acf);
546                 free_acf_query(oldquery);
547         }
548
549         cfg = ast_config_load(config);
550         if (!cfg) {
551                 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
552                 goto reload_out;
553         }
554
555         for (catg = ast_category_browse(cfg, NULL);
556              catg;
557              catg = ast_category_browse(cfg, catg)) {
558                 struct acf_odbc_query *query = NULL;
559
560                 if (init_acf_query(cfg, catg, &query)) {
561                         ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
562                 } else {
563                         AST_LIST_INSERT_HEAD(&queries, query, list);
564                         ast_custom_function_register(query->acf);
565                 }
566         }
567
568         ast_config_destroy(cfg);
569 reload_out:
570         AST_LIST_UNLOCK(&queries);
571         return res;
572 }
573
574 static int unload_module(void)
575 {
576         return odbc_unload_module();
577 }
578
579 static int load_module(void)
580 {
581         return odbc_load_module();
582 }
583
584 /* XXX need to revise usecount - set if query_lock is set */
585
586 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC lookups",
587                 .load = load_module,
588                 .unload = unload_module,
589                 .reload = reload,
590                );
591