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