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