Merged revisions 298482 via svnmerge from
[asterisk/asterisk.git] / res / res_config_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 1999 - 2010, Digium, Inc.
5  *
6  * Mark Spencer <markster@digium.com>
7  *
8  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
9  *
10  * See http://www.asterisk.org for more information about
11  * the Asterisk project. Please do not directly contact
12  * any of the maintainers of this project for assistance;
13  * the project provides a web site, mailing lists and IRC
14  * channels for your use.
15  *
16  * This program is free software, distributed under the terms of
17  * the GNU General Public License Version 2. See the LICENSE file
18  * at the top of the source tree.
19  */
20
21 /*! \file
22  *
23  * \brief odbc+odbc plugin for portable configuration engine
24  *
25  * \author Mark Spencer <markster@digium.com>
26  * \author Anthony Minessale II <anthmct@yahoo.com>
27  *
28  * \arg http://www.unixodbc.org
29  */
30
31 /*** MODULEINFO
32         <depend>res_odbc</depend>
33  ***/
34
35 #include "asterisk.h"
36
37 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
38
39 #include "asterisk/file.h"
40 #include "asterisk/channel.h"
41 #include "asterisk/pbx.h"
42 #include "asterisk/config.h"
43 #include "asterisk/module.h"
44 #include "asterisk/lock.h"
45 #include "asterisk/res_odbc.h"
46 #include "asterisk/utils.h"
47 #include "asterisk/stringfields.h"
48
49 AST_THREADSTORAGE(sql_buf);
50
51 struct custom_prepare_struct {
52         const char *sql;
53         const char *extra;
54         AST_DECLARE_STRING_FIELDS(
55                 AST_STRING_FIELD(encoding)[256];
56         );
57         va_list ap;
58         unsigned long long skip;
59 };
60
61 static void decode_chunk(char *chunk)
62 {
63         for (; *chunk; chunk++) {
64                 if (*chunk == '^' && strchr("0123456789ABCDEF", chunk[1]) && strchr("0123456789ABCDEF", chunk[2])) {
65                         sscanf(chunk + 1, "%02hhX", chunk);
66                         memmove(chunk + 1, chunk + 3, strlen(chunk + 3) + 1);
67                 }
68         }
69 }
70
71 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
72 {
73         int res, x = 1, count = 0;
74         struct custom_prepare_struct *cps = data;
75         const char *newparam, *newval;
76         char encodebuf[1024];
77         SQLHSTMT stmt;
78         va_list ap;
79
80         va_copy(ap, cps->ap);
81
82         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
83         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
84                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
85                 return NULL;
86         }
87
88         ast_debug(1, "Skip: %lld; SQL: %s\n", cps->skip, cps->sql);
89
90         res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
91         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
92                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
93                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
94                 return NULL;
95         }
96
97         while ((newparam = va_arg(ap, const char *))) {
98                 newval = va_arg(ap, const char *);
99                 if ((1LL << count++) & cps->skip) {
100                         ast_debug(1, "Skipping field '%s'='%s' (%llo/%llo)\n", newparam, newval, 1LL << (count - 1), cps->skip);
101                         continue;
102                 }
103                 ast_debug(1, "Parameter %d ('%s') = '%s'\n", x, newparam, newval);
104                 if (strchr(newval, ';') || strchr(newval, '^')) {
105                         char *eptr = encodebuf;
106                         const char *vptr = newval;
107                         for (; *vptr && eptr < encodebuf + sizeof(encodebuf); vptr++) {
108                                 if (strchr("^;", *vptr)) {
109                                         /* We use ^XX, instead of %XX because '%' is a special character in SQL */
110                                         snprintf(eptr, encodebuf + sizeof(encodebuf) - eptr, "^%02hhX", *vptr);
111                                         eptr += 3;
112                                 } else {
113                                         *eptr++ = *vptr;
114                                 }
115                         }
116                         if (eptr < encodebuf + sizeof(encodebuf)) {
117                                 *eptr = '\0';
118                         } else {
119                                 encodebuf[sizeof(encodebuf) - 1] = '\0';
120                         }
121                         ast_string_field_set(cps, encoding[x], encodebuf);
122                         newval = cps->encoding[x];
123                 }
124                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
125         }
126         va_end(ap);
127
128         if (!ast_strlen_zero(cps->extra))
129                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
130         return stmt;
131 }
132
133 /*!
134  * \brief Excute an SQL query and return ast_variable list
135  * \param database
136  * \param table
137  * \param ap list containing one or more field/operator/value set.
138  *
139  * Select database and preform query on table, prepare the sql statement
140  * Sub-in the values to the prepared statement and execute it. Return results
141  * as a ast_variable list.
142  *
143  * \retval var on success
144  * \retval NULL on failure
145 */
146 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
147 {
148         struct odbc_obj *obj;
149         SQLHSTMT stmt;
150         char sql[1024];
151         char coltitle[256];
152         char rowdata[2048];
153         char *op;
154         const char *newparam, *newval;
155         char *stringp;
156         char *chunk;
157         SQLSMALLINT collen;
158         int res;
159         int x;
160         struct ast_variable *var=NULL, *prev=NULL;
161         SQLULEN colsize;
162         SQLSMALLINT colcount=0;
163         SQLSMALLINT datatype;
164         SQLSMALLINT decimaldigits;
165         SQLSMALLINT nullable;
166         SQLLEN indicator;
167         va_list aq;
168         struct custom_prepare_struct cps = { .sql = sql };
169         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
170
171         if (ast_string_field_init(&cps, 256)) {
172                 return NULL;
173         }
174         va_copy(cps.ap, ap);
175         va_copy(aq, ap);
176
177         if (!table) {
178                 ast_string_field_free_memory(&cps);
179                 return NULL;
180         }
181
182         obj = ast_odbc_request_obj2(database, connected_flag);
183
184         if (!obj) {
185                 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
186                 ast_string_field_free_memory(&cps);
187                 return NULL;
188         }
189
190         newparam = va_arg(aq, const char *);
191         if (!newparam) {
192                 ast_odbc_release_obj(obj);
193                 ast_string_field_free_memory(&cps);
194                 return NULL;
195         }
196         newval = va_arg(aq, const char *);
197         op = !strchr(newparam, ' ') ? " =" : "";
198         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
199                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
200         while((newparam = va_arg(aq, const char *))) {
201                 op = !strchr(newparam, ' ') ? " =" : "";
202                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
203                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
204                 newval = va_arg(aq, const char *);
205         }
206         va_end(aq);
207
208         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
209
210         if (!stmt) {
211                 ast_odbc_release_obj(obj);
212                 ast_string_field_free_memory(&cps);
213                 return NULL;
214         }
215
216         res = SQLNumResultCols(stmt, &colcount);
217         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
218                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
219                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
220                 ast_odbc_release_obj(obj);
221                 ast_string_field_free_memory(&cps);
222                 return NULL;
223         }
224
225         res = SQLFetch(stmt);
226         if (res == SQL_NO_DATA) {
227                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
228                 ast_odbc_release_obj(obj);
229                 ast_string_field_free_memory(&cps);
230                 return NULL;
231         }
232         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
233                 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
234                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
235                 ast_odbc_release_obj(obj);
236                 ast_string_field_free_memory(&cps);
237                 return NULL;
238         }
239         for (x = 0; x < colcount; x++) {
240                 rowdata[0] = '\0';
241                 collen = sizeof(coltitle);
242                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
243                                         &datatype, &colsize, &decimaldigits, &nullable);
244                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
245                         ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
246                         if (var)
247                                 ast_variables_destroy(var);
248                         ast_odbc_release_obj(obj);
249                         ast_string_field_free_memory(&cps);
250                         return NULL;
251                 }
252
253                 indicator = 0;
254                 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
255                 if (indicator == SQL_NULL_DATA)
256                         rowdata[0] = '\0';
257                 else if (ast_strlen_zero(rowdata)) {
258                         /* Because we encode the empty string for a NULL, we will encode
259                          * actual empty strings as a string containing a single whitespace. */
260                         ast_copy_string(rowdata, " ", sizeof(rowdata));
261                 }
262
263                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
264                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
265                         if (var)
266                                 ast_variables_destroy(var);
267                         ast_odbc_release_obj(obj);
268                         return NULL;
269                 }
270                 stringp = rowdata;
271                 while (stringp) {
272                         chunk = strsep(&stringp, ";");
273                         if (!ast_strlen_zero(ast_strip(chunk))) {
274                                 if (strchr(chunk, '^')) {
275                                         decode_chunk(chunk);
276                                 }
277                                 if (prev) {
278                                         prev->next = ast_variable_new(coltitle, chunk, "");
279                                         if (prev->next) {
280                                                 prev = prev->next;
281                                         }
282                                 } else {
283                                         prev = var = ast_variable_new(coltitle, chunk, "");
284                                 }
285                         }
286                 }
287         }
288
289
290         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
291         ast_odbc_release_obj(obj);
292         ast_string_field_free_memory(&cps);
293         return var;
294 }
295
296 /*!
297  * \brief Excute an Select query and return ast_config list
298  * \param database
299  * \param table
300  * \param ap list containing one or more field/operator/value set.
301  *
302  * Select database and preform query on table, prepare the sql statement
303  * Sub-in the values to the prepared statement and execute it. 
304  * Execute this prepared query against several ODBC connected databases.
305  * Return results as an ast_config variable.
306  *
307  * \retval var on success
308  * \retval NULL on failure
309 */
310 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
311 {
312         struct odbc_obj *obj;
313         SQLHSTMT stmt;
314         char sql[1024];
315         char coltitle[256];
316         char rowdata[2048];
317         const char *initfield=NULL;
318         char *op;
319         const char *newparam, *newval;
320         char *stringp;
321         char *chunk;
322         SQLSMALLINT collen;
323         int res;
324         int x;
325         struct ast_variable *var=NULL;
326         struct ast_config *cfg=NULL;
327         struct ast_category *cat=NULL;
328         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
329         SQLULEN colsize;
330         SQLSMALLINT colcount=0;
331         SQLSMALLINT datatype;
332         SQLSMALLINT decimaldigits;
333         SQLSMALLINT nullable;
334         SQLLEN indicator;
335         struct custom_prepare_struct cps = { .sql = sql };
336         va_list aq;
337
338         if (!table || ast_string_field_init(&cps, 256)) {
339                 return NULL;
340         }
341         va_copy(cps.ap, ap);
342         va_copy(aq, ap);
343
344
345         obj = ast_odbc_request_obj2(database, connected_flag);
346         if (!obj) {
347                 ast_string_field_free_memory(&cps);
348                 return NULL;
349         }
350
351         newparam = va_arg(aq, const char *);
352         if (!newparam)  {
353                 ast_odbc_release_obj(obj);
354                 ast_string_field_free_memory(&cps);
355                 return NULL;
356         }
357         initfield = ast_strdupa(newparam);
358         if ((op = strchr(initfield, ' '))) 
359                 *op = '\0';
360         newval = va_arg(aq, const char *);
361         op = !strchr(newparam, ' ') ? " =" : "";
362         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
363                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
364         while((newparam = va_arg(aq, const char *))) {
365                 op = !strchr(newparam, ' ') ? " =" : "";
366                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
367                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
368                 newval = va_arg(aq, const char *);
369         }
370         if (initfield)
371                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
372         va_end(aq);
373
374         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
375
376         if (!stmt) {
377                 ast_odbc_release_obj(obj);
378                 ast_string_field_free_memory(&cps);
379                 return NULL;
380         }
381
382         res = SQLNumResultCols(stmt, &colcount);
383         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
384                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
385                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
386                 ast_odbc_release_obj(obj);
387                 ast_string_field_free_memory(&cps);
388                 return NULL;
389         }
390
391         cfg = ast_config_new();
392         if (!cfg) {
393                 ast_log(LOG_WARNING, "Out of memory!\n");
394                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
395                 ast_odbc_release_obj(obj);
396                 ast_string_field_free_memory(&cps);
397                 return NULL;
398         }
399
400         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
401                 var = NULL;
402                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
403                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
404                         continue;
405                 }
406                 cat = ast_category_new("","",99999);
407                 if (!cat) {
408                         ast_log(LOG_WARNING, "Out of memory!\n");
409                         continue;
410                 }
411                 for (x=0;x<colcount;x++) {
412                         rowdata[0] = '\0';
413                         collen = sizeof(coltitle);
414                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
415                                                 &datatype, &colsize, &decimaldigits, &nullable);
416                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
417                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
418                                 ast_category_destroy(cat);
419                                 continue;
420                         }
421
422                         indicator = 0;
423                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
424                         if (indicator == SQL_NULL_DATA)
425                                 continue;
426
427                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
428                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
429                                 ast_category_destroy(cat);
430                                 continue;
431                         }
432                         stringp = rowdata;
433                         while (stringp) {
434                                 chunk = strsep(&stringp, ";");
435                                 if (!ast_strlen_zero(ast_strip(chunk))) {
436                                         if (strchr(chunk, '^')) {
437                                                 decode_chunk(chunk);
438                                         }
439                                         if (initfield && !strcmp(initfield, coltitle)) {
440                                                 ast_category_rename(cat, chunk);
441                                         }
442                                         var = ast_variable_new(coltitle, chunk, "");
443                                         ast_variable_append(cat, var);
444                                 }
445                         }
446                 }
447                 ast_category_append(cfg, cat);
448         }
449
450         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
451         ast_odbc_release_obj(obj);
452         ast_string_field_free_memory(&cps);
453         return cfg;
454 }
455
456 /*!
457  * \brief Excute an UPDATE query
458  * \param database
459  * \param table
460  * \param keyfield where clause field
461  * \param lookup value of field for where clause
462  * \param ap list containing one or more field/value set(s).
463  *
464  * Update a database table, prepare the sql statement using keyfield and lookup
465  * control the number of records to change. All values to be changed are stored in ap list.
466  * Sub-in the values to the prepared statement and execute it.
467  *
468  * \retval number of rows affected
469  * \retval -1 on failure
470 */
471 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
472 {
473         struct odbc_obj *obj;
474         SQLHSTMT stmt;
475         char sql[256];
476         SQLLEN rowcount=0;
477         const char *newparam, *newval;
478         int res, count = 1;
479         va_list aq;
480         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
481         struct odbc_cache_tables *tableptr;
482         struct odbc_cache_columns *column;
483         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
484
485         if (!table) {
486                 return -1;
487         }
488
489         va_copy(cps.ap, ap);
490         va_copy(aq, ap);
491
492         if (ast_string_field_init(&cps, 256)) {
493                 return -1;
494         }
495
496         tableptr = ast_odbc_find_table(database, table);
497         if (!(obj = ast_odbc_request_obj2(database, connected_flag))) {
498                 ast_odbc_release_table(tableptr);
499                 ast_string_field_free_memory(&cps);
500                 return -1;
501         }
502
503         newparam = va_arg(aq, const char *);
504         if (!newparam)  {
505                 ast_odbc_release_obj(obj);
506                 ast_odbc_release_table(tableptr);
507                 ast_string_field_free_memory(&cps);
508                 return -1;
509         }
510         newval = va_arg(aq, const char *);
511
512         if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
513                 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", newparam, table, database);
514         }
515
516         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
517         while((newparam = va_arg(aq, const char *))) {
518                 newval = va_arg(aq, const char *);
519                 if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
520                         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
521                 } else { /* the column does not exist in the table */
522                         cps.skip |= (1LL << count);
523                 }
524                 count++;
525         }
526         va_end(aq);
527         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
528         ast_odbc_release_table(tableptr);
529
530         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
531
532         if (!stmt) {
533                 ast_odbc_release_obj(obj);
534                 ast_string_field_free_memory(&cps);
535                 return -1;
536         }
537
538         res = SQLRowCount(stmt, &rowcount);
539         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
540         ast_odbc_release_obj(obj);
541         ast_string_field_free_memory(&cps);
542
543         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
544                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
545                 return -1;
546         }
547
548         if (rowcount >= 0) {
549                 return (int) rowcount;
550         }
551
552         return -1;
553 }
554
555 struct update2_prepare_struct {
556         const char *database;
557         const char *table;
558         va_list ap;
559 };
560
561 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
562 {
563         int res, x = 1, first = 1;
564         struct update2_prepare_struct *ups = data;
565         const char *newparam, *newval;
566         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
567         SQLHSTMT stmt;
568         va_list ap;
569         struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
570         struct odbc_cache_columns *column;
571
572         if (!sql) {
573                 if (tableptr) {
574                         ast_odbc_release_table(tableptr);
575                 }
576                 return NULL;
577         }
578
579         if (!tableptr) {
580                 ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'.  Update will fail!\n", ups->table, ups->database);
581                 return NULL;
582         }
583
584         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
585         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
586                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
587                 ast_odbc_release_table(tableptr);
588                 return NULL;
589         }
590
591         ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
592
593         /* Start by finding the second set of parameters */
594         va_copy(ap, ups->ap);
595
596         while ((newparam = va_arg(ap, const char *))) {
597                 newval = va_arg(ap, const char *);
598         }
599
600         while ((newparam = va_arg(ap, const char *))) {
601                 newval = va_arg(ap, const char *);
602                 if ((column = ast_odbc_find_column(tableptr, newparam))) {
603                         ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", newparam);
604                         SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
605                         first = 0;
606                 } else {
607                         ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", newparam, ups->table, ups->database);
608                 }
609         }
610         va_end(ap);
611
612         /* Restart search, because we need to add the search parameters */
613         va_copy(ap, ups->ap);
614         ast_str_append(&sql, 0, "WHERE");
615         first = 1;
616
617         while ((newparam = va_arg(ap, const char *))) {
618                 newval = va_arg(ap, const char *);
619                 if (!(column = ast_odbc_find_column(tableptr, newparam))) {
620                         ast_log(LOG_ERROR, "One or more of the criteria columns '%s' on '%s@%s' for this update does not exist!\n", newparam, ups->table, ups->database);
621                         ast_odbc_release_table(tableptr);
622                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
623                         return NULL;
624                 }
625                 ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", newparam);
626                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
627                 first = 0;
628         }
629         va_end(ap);
630
631         /* Done with the table metadata */
632         ast_odbc_release_table(tableptr);
633
634         res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
635         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
636                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
637                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
638                 return NULL;
639         }
640
641         return stmt;
642 }
643
644 /*!
645  * \brief Execute an UPDATE query
646  * \param database
647  * \param table
648  * \param ap list containing one or more field/value set(s).
649  *
650  * Update a database table, preparing the sql statement from a list of
651  * key/value pairs specified in ap.  The lookup pairs are specified first
652  * and are separated from the update pairs by a sentinel value.
653  * Sub-in the values to the prepared statement and execute it.
654  *
655  * \retval number of rows affected
656  * \retval -1 on failure
657 */
658 static int update2_odbc(const char *database, const char *table, va_list ap)
659 {
660         struct odbc_obj *obj;
661         SQLHSTMT stmt;
662         struct update2_prepare_struct ups = { .database = database, .table = table, };
663         struct ast_str *sql;
664         int res;
665         SQLLEN rowcount = 0;
666
667         va_copy(ups.ap, ap);
668
669         if (!(obj = ast_odbc_request_obj(database, 0))) {
670                 return -1;
671         }
672
673         if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
674                 ast_odbc_release_obj(obj);
675                 return -1;
676         }
677
678         res = SQLRowCount(stmt, &rowcount);
679         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
680         ast_odbc_release_obj(obj);
681
682         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
683                 /* Since only a single thread can access this memory, we can retrieve what would otherwise be lost. */
684                 sql = ast_str_thread_get(&sql_buf, 16);
685                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
686                 return -1;
687         }
688
689         if (rowcount >= 0) {
690                 return (int)rowcount;
691         }
692
693         return -1;
694 }
695
696 /*!
697  * \brief Excute an INSERT query
698  * \param database
699  * \param table
700  * \param ap list containing one or more field/value set(s)
701  *
702  * Insert a new record into database table, prepare the sql statement.
703  * All values to be changed are stored in ap list.
704  * Sub-in the values to the prepared statement and execute it.
705  *
706  * \retval number of rows affected
707  * \retval -1 on failure
708 */
709 static int store_odbc(const char *database, const char *table, va_list ap)
710 {
711         struct odbc_obj *obj;
712         SQLHSTMT stmt;
713         char sql[256];
714         char keys[256];
715         char vals[256];
716         SQLLEN rowcount=0;
717         const char *newparam, *newval;
718         int res;
719         va_list aq;
720         struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
721         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
722
723         va_copy(cps.ap, ap);
724         va_copy(aq, ap);
725         
726         if (!table)
727                 return -1;
728
729         obj = ast_odbc_request_obj2(database, connected_flag);
730         if (!obj)
731                 return -1;
732
733         newparam = va_arg(aq, const char *);
734         if (!newparam)  {
735                 ast_odbc_release_obj(obj);
736                 return -1;
737         }
738         newval = va_arg(aq, const char *);
739         snprintf(keys, sizeof(keys), "%s", newparam);
740         ast_copy_string(vals, "?", sizeof(vals));
741         while ((newparam = va_arg(aq, const char *))) {
742                 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
743                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
744                 newval = va_arg(aq, const char *);
745         }
746         va_end(aq);
747         snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
748
749         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
750
751         if (!stmt) {
752                 ast_odbc_release_obj(obj);
753                 return -1;
754         }
755
756         res = SQLRowCount(stmt, &rowcount);
757         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
758         ast_odbc_release_obj(obj);
759
760         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
761                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
762                 return -1;
763         }
764
765         if (rowcount >= 0)
766                 return (int)rowcount;
767
768         return -1;
769 }
770
771 /*!
772  * \brief Excute an DELETE query
773  * \param database
774  * \param table
775  * \param keyfield where clause field
776  * \param lookup value of field for where clause
777  * \param ap list containing one or more field/value set(s)
778  *
779  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
780  * control the number of records to change. Additional params to match rows are stored in ap list.
781  * Sub-in the values to the prepared statement and execute it.
782  *
783  * \retval number of rows affected
784  * \retval -1 on failure
785 */
786 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
787 {
788         struct odbc_obj *obj;
789         SQLHSTMT stmt;
790         char sql[256];
791         SQLLEN rowcount=0;
792         const char *newparam, *newval;
793         int res;
794         va_list aq;
795         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
796         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
797
798         va_copy(cps.ap, ap);
799         va_copy(aq, ap);
800         
801         if (!table)
802                 return -1;
803
804         obj = ast_odbc_request_obj2(database, connected_flag);
805         if (!obj)
806                 return -1;
807
808         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
809         while((newparam = va_arg(aq, const char *))) {
810                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
811                 newval = va_arg(aq, const char *);
812         }
813         va_end(aq);
814         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
815
816         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
817
818         if (!stmt) {
819                 ast_odbc_release_obj(obj);
820                 return -1;
821         }
822
823         res = SQLRowCount(stmt, &rowcount);
824         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
825         ast_odbc_release_obj(obj);
826
827         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
828                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
829                 return -1;
830         }
831
832         if (rowcount >= 0)
833                 return (int)rowcount;
834
835         return -1;
836 }
837
838
839 struct config_odbc_obj {
840         char *sql;
841         unsigned long cat_metric;
842         char category[128];
843         char var_name[128];
844         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
845         SQLLEN err;
846 };
847
848 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
849 {
850         struct config_odbc_obj *q = data;
851         SQLHSTMT sth;
852         int res;
853
854         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
855         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
856                 ast_verb(4, "Failure in AllocStatement %d\n", res);
857                 return NULL;
858         }
859
860         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
861         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
862                 ast_verb(4, "Error in PREPARE %d\n", res);
863                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
864                 return NULL;
865         }
866
867         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
868         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
869         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
870         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
871
872         return sth;
873 }
874
875 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *cfg, struct ast_flags flags, const char *sugg_incl, const char *who_asked)
876 {
877         struct ast_variable *new_v;
878         struct ast_category *cur_cat;
879         int res = 0;
880         struct odbc_obj *obj;
881         char sqlbuf[1024] = "";
882         char *sql = sqlbuf;
883         size_t sqlleft = sizeof(sqlbuf);
884         unsigned int last_cat_metric = 0;
885         SQLSMALLINT rowcount = 0;
886         SQLHSTMT stmt;
887         char last[128] = "";
888         struct config_odbc_obj q;
889         struct ast_flags loader_flags = { 0 };
890         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
891
892         memset(&q, 0, sizeof(q));
893
894         if (!file || !strcmp (file, "res_config_odbc.conf"))
895                 return NULL;            /* cant configure myself with myself ! */
896
897         obj = ast_odbc_request_obj2(database, connected_flag);
898         if (!obj)
899                 return NULL;
900
901         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
902         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
903         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
904         q.sql = sqlbuf;
905
906         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
907
908         if (!stmt) {
909                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
910                 ast_odbc_release_obj(obj);
911                 return NULL;
912         }
913
914         res = SQLNumResultCols(stmt, &rowcount);
915
916         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
917                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
918                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
919                 ast_odbc_release_obj(obj);
920                 return NULL;
921         }
922
923         if (!rowcount) {
924                 ast_log(LOG_NOTICE, "found nothing\n");
925                 ast_odbc_release_obj(obj);
926                 return cfg;
927         }
928
929         cur_cat = ast_config_get_current_category(cfg);
930
931         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
932                 if (!strcmp (q.var_name, "#include")) {
933                         if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
934                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
935                                 ast_odbc_release_obj(obj);
936                                 return NULL;
937                         }
938                         continue;
939                 } 
940                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
941                         cur_cat = ast_category_new(q.category, "", 99999);
942                         if (!cur_cat) {
943                                 ast_log(LOG_WARNING, "Out of memory!\n");
944                                 break;
945                         }
946                         strcpy(last, q.category);
947                         last_cat_metric = q.cat_metric;
948                         ast_category_append(cfg, cur_cat);
949                 }
950
951                 new_v = ast_variable_new(q.var_name, q.var_val, "");
952                 ast_variable_append(cur_cat, new_v);
953         }
954
955         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
956         ast_odbc_release_obj(obj);
957         return cfg;
958 }
959
960 #define warn_length(col, size)  ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is not long enough to contain realtime data (needs %d)\n", table, database, col->name, size)
961 #define warn_type(col, type)    ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is of the incorrect type (%d) to contain the required realtime data\n", table, database, col->name, col->type)
962
963 static int require_odbc(const char *database, const char *table, va_list ap)
964 {
965         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
966         struct odbc_cache_columns *col;
967         char *elm;
968         int type, size;
969
970         if (!tableptr) {
971                 return -1;
972         }
973
974         while ((elm = va_arg(ap, char *))) {
975                 type = va_arg(ap, require_type);
976                 size = va_arg(ap, int);
977                 /* Check if the field matches the criteria */
978                 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
979                         if (strcmp(col->name, elm) == 0) {
980                                 /* Type check, first.  Some fields are more particular than others */
981                                 switch (col->type) {
982                                 case SQL_CHAR:
983                                 case SQL_VARCHAR:
984                                 case SQL_LONGVARCHAR:
985 #ifdef HAVE_ODBC_WCHAR
986                                 case SQL_WCHAR:
987                                 case SQL_WVARCHAR:
988                                 case SQL_WLONGVARCHAR:
989 #endif
990                                 case SQL_BINARY:
991                                 case SQL_VARBINARY:
992                                 case SQL_LONGVARBINARY:
993                                 case SQL_GUID:
994 #define CHECK_SIZE(n) \
995                                                 if (col->size < n) {      \
996                                                         warn_length(col, n);  \
997                                                 }                         \
998                                                 break;
999                                         switch (type) {
1000                                         case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
1001                                         case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
1002                                         case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
1003                                         case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
1004                                         case RQ_UINTEGER3:                /*    16777215 */
1005                                         case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
1006                                         case RQ_DATE:                     /*  2008-06-09 */
1007                                         case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
1008                                         case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
1009                                         case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
1010                                         case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
1011                                         case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
1012                                         case RQ_FLOAT:
1013                                         case RQ_CHAR:      CHECK_SIZE(size)
1014                                         }
1015 #undef CHECK_SIZE
1016                                         break;
1017                                 case SQL_TYPE_DATE:
1018                                         if (type != RQ_DATE) {
1019                                                 warn_type(col, type);
1020                                         }
1021                                         break;
1022                                 case SQL_TYPE_TIMESTAMP:
1023                                 case SQL_TIMESTAMP:
1024                                         if (type != RQ_DATE && type != RQ_DATETIME) {
1025                                                 warn_type(col, type);
1026                                         }
1027                                         break;
1028                                 case SQL_BIT:
1029                                         warn_length(col, size);
1030                                         break;
1031 #define WARN_TYPE_OR_LENGTH(n)  \
1032                                                 if (!ast_rq_is_int(type)) {  \
1033                                                         warn_type(col, type);    \
1034                                                 } else {                     \
1035                                                         warn_length(col, n);  \
1036                                                 }
1037                                 case SQL_TINYINT:
1038                                         if (type != RQ_UINTEGER1) {
1039                                                 WARN_TYPE_OR_LENGTH(size)
1040                                         }
1041                                         break;
1042                                 case SQL_C_STINYINT:
1043                                         if (type != RQ_INTEGER1) {
1044                                                 WARN_TYPE_OR_LENGTH(size)
1045                                         }
1046                                         break;
1047                                 case SQL_C_USHORT:
1048                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
1049                                                 WARN_TYPE_OR_LENGTH(size)
1050                                         }
1051                                         break;
1052                                 case SQL_SMALLINT:
1053                                 case SQL_C_SSHORT:
1054                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
1055                                                 WARN_TYPE_OR_LENGTH(size)
1056                                         }
1057                                         break;
1058                                 case SQL_C_ULONG:
1059                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1060                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1061                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1062                                                 type != RQ_INTEGER4) {
1063                                                 WARN_TYPE_OR_LENGTH(size)
1064                                         }
1065                                         break;
1066                                 case SQL_INTEGER:
1067                                 case SQL_C_SLONG:
1068                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1069                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1070                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1071                                                 type != RQ_INTEGER4) {
1072                                                 WARN_TYPE_OR_LENGTH(size)
1073                                         }
1074                                         break;
1075                                 case SQL_C_UBIGINT:
1076                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1077                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1078                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1079                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1080                                                 type != RQ_INTEGER8) {
1081                                                 WARN_TYPE_OR_LENGTH(size)
1082                                         }
1083                                         break;
1084                                 case SQL_BIGINT:
1085                                 case SQL_C_SBIGINT:
1086                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1087                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1088                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1089                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1090                                                 type != RQ_INTEGER8) {
1091                                                 WARN_TYPE_OR_LENGTH(size)
1092                                         }
1093                                         break;
1094 #undef WARN_TYPE_OR_LENGTH
1095                                 case SQL_NUMERIC:
1096                                 case SQL_DECIMAL:
1097                                 case SQL_FLOAT:
1098                                 case SQL_REAL:
1099                                 case SQL_DOUBLE:
1100                                         if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
1101                                                 warn_type(col, type);
1102                                         }
1103                                         break;
1104                                 default:
1105                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
1106                                 }
1107                                 break;
1108                         }
1109                 }
1110                 if (!col) {
1111                         ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
1112                 }
1113         }
1114         va_end(ap);
1115         AST_RWLIST_UNLOCK(&tableptr->columns);
1116         return 0;
1117 }
1118 #undef warn_length
1119 #undef warn_type
1120
1121 static struct ast_config_engine odbc_engine = {
1122         .name = "odbc",
1123         .load_func = config_odbc,
1124         .realtime_func = realtime_odbc,
1125         .realtime_multi_func = realtime_multi_odbc,
1126         .store_func = store_odbc,
1127         .destroy_func = destroy_odbc,
1128         .update_func = update_odbc,
1129         .update2_func = update2_odbc,
1130         .require_func = require_odbc,
1131         .unload_func = ast_odbc_clear_cache,
1132 };
1133
1134 static int unload_module (void)
1135 {
1136         ast_config_engine_deregister(&odbc_engine);
1137
1138         ast_verb(1, "res_config_odbc unloaded.\n");
1139         return 0;
1140 }
1141
1142 static int load_module (void)
1143 {
1144         ast_config_engine_register(&odbc_engine);
1145         ast_verb(1, "res_config_odbc loaded.\n");
1146         return 0;
1147 }
1148
1149 static int reload_module(void)
1150 {
1151         return 0;
1152 }
1153
1154 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "Realtime ODBC configuration",
1155                 .load = load_module,
1156                 .unload = unload_module,
1157                 .reload = reload_module,
1158                 .load_pri = AST_MODPRI_REALTIME_DRIVER,
1159                 );