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