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