PJSIP: Add Path header support
[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         op = !strchr(field->name, ' ') ? " =" : "";
344         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, field->name, op,
345                 strcasestr(field->name, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
346         while ((field = field->next)) {
347                 op = !strchr(field->name, ' ') ? " =" : "";
348                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", field->name, op,
349                         strcasestr(field->name, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
350         }
351
352         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
353
354         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
355
356         if (!stmt) {
357                 ast_odbc_release_obj(obj);
358                 ast_string_field_free_memory(&cps);
359                 return NULL;
360         }
361
362         res = SQLNumResultCols(stmt, &colcount);
363         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
364                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
365                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
366                 ast_odbc_release_obj(obj);
367                 ast_string_field_free_memory(&cps);
368                 return NULL;
369         }
370
371         cfg = ast_config_new();
372         if (!cfg) {
373                 ast_log(LOG_WARNING, "Out of memory!\n");
374                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
375                 ast_odbc_release_obj(obj);
376                 ast_string_field_free_memory(&cps);
377                 return NULL;
378         }
379
380         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
381                 var = NULL;
382                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
383                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
384                         continue;
385                 }
386                 cat = ast_category_new("","",99999);
387                 if (!cat) {
388                         ast_log(LOG_WARNING, "Out of memory!\n");
389                         continue;
390                 }
391                 for (x=0;x<colcount;x++) {
392                         rowdata[0] = '\0';
393                         colsize = 0;
394                         collen = sizeof(coltitle);
395                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
396                                                 &datatype, &colsize, &decimaldigits, &nullable);
397                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
398                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
399                                 ast_category_destroy(cat);
400                                 goto next_sql_fetch;
401                         }
402
403                         indicator = 0;
404                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
405                         if (indicator == SQL_NULL_DATA)
406                                 continue;
407
408                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
409                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
410                                 ast_category_destroy(cat);
411                                 goto next_sql_fetch;
412                         }
413                         stringp = rowdata;
414                         while (stringp) {
415                                 chunk = strsep(&stringp, ";");
416                                 if (!ast_strlen_zero(ast_strip(chunk))) {
417                                         if (strchr(chunk, '^')) {
418                                                 decode_chunk(chunk);
419                                         }
420                                         if (!strcmp(initfield, coltitle)) {
421                                                 ast_category_rename(cat, chunk);
422                                         }
423                                         var = ast_variable_new(coltitle, chunk, "");
424                                         ast_variable_append(cat, var);
425                                 }
426                         }
427                 }
428                 ast_category_append(cfg, cat);
429 next_sql_fetch:;
430         }
431
432         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
433         ast_odbc_release_obj(obj);
434         ast_string_field_free_memory(&cps);
435         return cfg;
436 }
437
438 /*!
439  * \brief Excute an UPDATE query
440  * \param database
441  * \param table
442  * \param keyfield where clause field
443  * \param lookup value of field for where clause
444  * \param ap list containing one or more field/value set(s).
445  *
446  * Update a database table, prepare the sql statement using keyfield and lookup
447  * control the number of records to change. All values to be changed are stored in ap list.
448  * Sub-in the values to the prepared statement and execute it.
449  *
450  * \retval number of rows affected
451  * \retval -1 on failure
452 */
453 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, const struct ast_variable *fields)
454 {
455         struct odbc_obj *obj;
456         SQLHSTMT stmt;
457         char sql[256];
458         SQLLEN rowcount=0;
459         const struct ast_variable *field = fields;
460         int res, count = 1;
461         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup, .fields = fields, };
462         struct odbc_cache_tables *tableptr;
463         struct odbc_cache_columns *column = NULL;
464         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
465
466         if (!table || !field) {
467                 return -1;
468         }
469
470         if (ast_string_field_init(&cps, 256)) {
471                 return -1;
472         }
473
474         tableptr = ast_odbc_find_table(database, table);
475         if (!(obj = ast_odbc_request_obj2(database, connected_flag))) {
476                 ast_odbc_release_table(tableptr);
477                 ast_string_field_free_memory(&cps);
478                 return -1;
479         }
480
481         if (tableptr && !ast_odbc_find_column(tableptr, field->name)) {
482                 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", field->name, table, database);
483         }
484
485         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, field->name);
486         while ((field = field->next)) {
487                 if ((tableptr && (column = ast_odbc_find_column(tableptr, field->name))) || count > 63) {
488                         /* NULL test for integer-based columns */
489                         if (ast_strlen_zero(field->name) && tableptr && column && column->nullable && count < 64 &&
490                                 (column->type == SQL_INTEGER || column->type == SQL_BIGINT ||
491                                  column->type == SQL_SMALLINT || column->type == SQL_TINYINT ||
492                                  column->type == SQL_NUMERIC || column->type == SQL_DECIMAL)) {
493                                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=NULL", field->name);
494                                 cps.skip |= (1LL << count);
495                         } else {
496                                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", field->name);
497                         }
498                 } else { /* the column does not exist in the table */
499                         cps.skip |= (1LL << count);
500                 }
501                 count++;
502         }
503         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
504         ast_odbc_release_table(tableptr);
505
506         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
507
508         if (!stmt) {
509                 ast_odbc_release_obj(obj);
510                 ast_string_field_free_memory(&cps);
511                 return -1;
512         }
513
514         res = SQLRowCount(stmt, &rowcount);
515         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
516         ast_odbc_release_obj(obj);
517         ast_string_field_free_memory(&cps);
518
519         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
520                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
521                 return -1;
522         }
523
524         if (rowcount >= 0) {
525                 return (int) rowcount;
526         }
527
528         return -1;
529 }
530
531 struct update2_prepare_struct {
532         const char *database;
533         const char *table;
534         const struct ast_variable *lookup_fields;
535         const struct ast_variable *update_fields;
536 };
537
538 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
539 {
540         int res, x = 1, first = 1;
541         struct update2_prepare_struct *ups = data;
542         const struct ast_variable *field;
543         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
544         SQLHSTMT stmt;
545         struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
546
547         if (!sql) {
548                 if (tableptr) {
549                         ast_odbc_release_table(tableptr);
550                 }
551                 return NULL;
552         }
553
554         if (!tableptr) {
555                 ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'.  Update will fail!\n", ups->table, ups->database);
556                 return NULL;
557         }
558
559         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
560         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
561                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
562                 ast_odbc_release_table(tableptr);
563                 return NULL;
564         }
565
566         ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
567
568         for (field = ups->update_fields; field; field = field->next) {
569                 if (ast_odbc_find_column(tableptr, field->name)) {
570                         ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", field->name);
571                         SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(field->name), 0, (void *)field->value, 0, NULL);
572                         first = 0;
573                 } else {
574                         ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", field->name, ups->table, ups->database);
575                 }
576         }
577
578         ast_str_append(&sql, 0, "WHERE");
579         first = 1;
580
581         for (field = ups->lookup_fields; field; field = field->next) {
582                 if (!ast_odbc_find_column(tableptr, field->name)) {
583                         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);
584                         ast_odbc_release_table(tableptr);
585                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
586                         return NULL;
587                 }
588                 ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", field->name);
589                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(field->value), 0, (void *)field->value, 0, NULL);
590                 first = 0;
591         }
592
593         /* Done with the table metadata */
594         ast_odbc_release_table(tableptr);
595
596         res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
597         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
598                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
599                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
600                 return NULL;
601         }
602
603         return stmt;
604 }
605
606 /*!
607  * \brief Execute an UPDATE query
608  * \param database
609  * \param table
610  * \param ap list containing one or more field/value set(s).
611  *
612  * Update a database table, preparing the sql statement from a list of
613  * key/value pairs specified in ap.  The lookup pairs are specified first
614  * and are separated from the update pairs by a sentinel value.
615  * Sub-in the values to the prepared statement and execute it.
616  *
617  * \retval number of rows affected
618  * \retval -1 on failure
619 */
620 static int update2_odbc(const char *database, const char *table, const struct ast_variable *lookup_fields, const struct ast_variable *update_fields)
621 {
622         struct odbc_obj *obj;
623         SQLHSTMT stmt;
624         struct update2_prepare_struct ups = { .database = database, .table = table, .lookup_fields = lookup_fields, .update_fields = update_fields, };
625         struct ast_str *sql;
626         int res;
627         SQLLEN rowcount = 0;
628
629         if (!(obj = ast_odbc_request_obj(database, 0))) {
630                 return -1;
631         }
632
633         if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
634                 ast_odbc_release_obj(obj);
635                 return -1;
636         }
637
638         res = SQLRowCount(stmt, &rowcount);
639         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
640         ast_odbc_release_obj(obj);
641
642         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
643                 /* Since only a single thread can access this memory, we can retrieve what would otherwise be lost. */
644                 sql = ast_str_thread_get(&sql_buf, 16);
645                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
646                 return -1;
647         }
648
649         if (rowcount >= 0) {
650                 return (int)rowcount;
651         }
652
653         return -1;
654 }
655
656 /*!
657  * \brief Excute an INSERT query
658  * \param database
659  * \param table
660  * \param ap list containing one or more field/value set(s)
661  *
662  * Insert a new record into database table, prepare the sql statement.
663  * All values to be changed are stored in ap list.
664  * Sub-in the values to the prepared statement and execute it.
665  *
666  * \retval number of rows affected
667  * \retval -1 on failure
668 */
669 static int store_odbc(const char *database, const char *table, const struct ast_variable *fields)
670 {
671         struct odbc_obj *obj;
672         SQLHSTMT stmt;
673         char sql[256];
674         char keys[256];
675         char vals[256];
676         SQLLEN rowcount=0;
677         const struct ast_variable *field = fields;
678         int res;
679         struct custom_prepare_struct cps = { .sql = sql, .extra = NULL, .fields = fields, };
680         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
681
682         if (!table || !field) {
683                 return -1;
684         }
685
686         obj = ast_odbc_request_obj2(database, connected_flag);
687         if (!obj) {
688                 return -1;
689         }
690
691         snprintf(keys, sizeof(keys), "%s", field->name);
692         ast_copy_string(vals, "?", sizeof(vals));
693         while ((field = field->next)) {
694                 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", field->name);
695                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
696         }
697         snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
698
699         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
700
701         if (!stmt) {
702                 ast_odbc_release_obj(obj);
703                 return -1;
704         }
705
706         res = SQLRowCount(stmt, &rowcount);
707         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
708         ast_odbc_release_obj(obj);
709
710         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
711                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
712                 return -1;
713         }
714
715         if (rowcount >= 0)
716                 return (int)rowcount;
717
718         return -1;
719 }
720
721 /*!
722  * \brief Excute an DELETE query
723  * \param database
724  * \param table
725  * \param keyfield where clause field
726  * \param lookup value of field for where clause
727  * \param ap list containing one or more field/value set(s)
728  *
729  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
730  * control the number of records to change. Additional params to match rows are stored in ap list.
731  * Sub-in the values to the prepared statement and execute it.
732  *
733  * \retval number of rows affected
734  * \retval -1 on failure
735 */
736 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, const struct ast_variable *fields)
737 {
738         struct odbc_obj *obj;
739         SQLHSTMT stmt;
740         char sql[256];
741         SQLLEN rowcount=0;
742         const struct ast_variable *field;
743         int res;
744         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup, .fields = fields, };
745         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
746
747         if (!table) {
748                 return -1;
749         }
750
751         obj = ast_odbc_request_obj2(database, connected_flag);
752         if (!obj) {
753                 return -1;
754         }
755
756         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
757
758         for (field = fields; field; field = field->next) {
759                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", field->name);
760         }
761         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
762
763         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
764
765         if (!stmt) {
766                 ast_odbc_release_obj(obj);
767                 return -1;
768         }
769
770         res = SQLRowCount(stmt, &rowcount);
771         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
772         ast_odbc_release_obj(obj);
773
774         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
775                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
776                 return -1;
777         }
778
779         if (rowcount >= 0)
780                 return (int)rowcount;
781
782         return -1;
783 }
784
785
786 struct config_odbc_obj {
787         char *sql;
788         unsigned long cat_metric;
789         char category[128];
790         char var_name[128];
791         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
792         SQLLEN err;
793 };
794
795 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
796 {
797         struct config_odbc_obj *q = data;
798         SQLHSTMT sth;
799         int res;
800
801         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
802         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
803                 ast_verb(4, "Failure in AllocStatement %d\n", res);
804                 return NULL;
805         }
806
807         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
808         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
809                 ast_verb(4, "Error in PREPARE %d\n", res);
810                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
811                 return NULL;
812         }
813
814         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
815         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
816         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
817         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
818
819         return sth;
820 }
821
822 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)
823 {
824         struct ast_variable *new_v;
825         struct ast_category *cur_cat;
826         int res = 0;
827         struct odbc_obj *obj;
828         char sqlbuf[1024] = "";
829         char *sql = sqlbuf;
830         size_t sqlleft = sizeof(sqlbuf);
831         unsigned int last_cat_metric = 0;
832         SQLSMALLINT rowcount = 0;
833         SQLHSTMT stmt;
834         char last[128] = "";
835         struct config_odbc_obj q;
836         struct ast_flags loader_flags = { 0 };
837         struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
838
839         memset(&q, 0, sizeof(q));
840
841         if (!file || !strcmp (file, "res_config_odbc.conf"))
842                 return NULL;            /* cant configure myself with myself ! */
843
844         obj = ast_odbc_request_obj2(database, connected_flag);
845         if (!obj)
846                 return NULL;
847
848         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
849         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
850         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
851         q.sql = sqlbuf;
852
853         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
854
855         if (!stmt) {
856                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
857                 ast_odbc_release_obj(obj);
858                 return NULL;
859         }
860
861         res = SQLNumResultCols(stmt, &rowcount);
862
863         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
864                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
865                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
866                 ast_odbc_release_obj(obj);
867                 return NULL;
868         }
869
870         if (!rowcount) {
871                 ast_log(LOG_NOTICE, "found nothing\n");
872                 ast_odbc_release_obj(obj);
873                 return cfg;
874         }
875
876         cur_cat = ast_config_get_current_category(cfg);
877
878         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
879                 if (!strcmp (q.var_name, "#include")) {
880                         if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
881                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
882                                 ast_odbc_release_obj(obj);
883                                 return NULL;
884                         }
885                         continue;
886                 } 
887                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
888                         cur_cat = ast_category_new(q.category, "", 99999);
889                         if (!cur_cat) {
890                                 ast_log(LOG_WARNING, "Out of memory!\n");
891                                 break;
892                         }
893                         strcpy(last, q.category);
894                         last_cat_metric = q.cat_metric;
895                         ast_category_append(cfg, cur_cat);
896                 }
897
898                 new_v = ast_variable_new(q.var_name, q.var_val, "");
899                 ast_variable_append(cur_cat, new_v);
900         }
901
902         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
903         ast_odbc_release_obj(obj);
904         return cfg;
905 }
906
907 #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)
908 #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)
909
910 static int require_odbc(const char *database, const char *table, va_list ap)
911 {
912         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
913         struct odbc_cache_columns *col;
914         char *elm;
915         int type, size;
916
917         if (!tableptr) {
918                 return -1;
919         }
920
921         while ((elm = va_arg(ap, char *))) {
922                 type = va_arg(ap, require_type);
923                 size = va_arg(ap, int);
924                 /* Check if the field matches the criteria */
925                 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
926                         if (strcmp(col->name, elm) == 0) {
927                                 /* Type check, first.  Some fields are more particular than others */
928                                 switch (col->type) {
929                                 case SQL_CHAR:
930                                 case SQL_VARCHAR:
931                                 case SQL_LONGVARCHAR:
932 #ifdef HAVE_ODBC_WCHAR
933                                 case SQL_WCHAR:
934                                 case SQL_WVARCHAR:
935                                 case SQL_WLONGVARCHAR:
936 #endif
937                                 case SQL_BINARY:
938                                 case SQL_VARBINARY:
939                                 case SQL_LONGVARBINARY:
940                                 case SQL_GUID:
941 #define CHECK_SIZE(n) \
942                                                 if (col->size < n) {      \
943                                                         warn_length(col, n);  \
944                                                 }                         \
945                                                 break;
946                                         switch (type) {
947                                         case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
948                                         case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
949                                         case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
950                                         case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
951                                         case RQ_UINTEGER3:                /*    16777215 */
952                                         case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
953                                         case RQ_DATE:                     /*  2008-06-09 */
954                                         case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
955                                         case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
956                                         case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
957                                         case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
958                                         case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
959                                         case RQ_FLOAT:
960                                         case RQ_CHAR:      CHECK_SIZE(size)
961                                         }
962 #undef CHECK_SIZE
963                                         break;
964                                 case SQL_TYPE_DATE:
965                                         if (type != RQ_DATE) {
966                                                 warn_type(col, type);
967                                         }
968                                         break;
969                                 case SQL_TYPE_TIMESTAMP:
970                                 case SQL_TIMESTAMP:
971                                         if (type != RQ_DATE && type != RQ_DATETIME) {
972                                                 warn_type(col, type);
973                                         }
974                                         break;
975                                 case SQL_BIT:
976                                         warn_length(col, size);
977                                         break;
978 #define WARN_TYPE_OR_LENGTH(n)  \
979                                                 if (!ast_rq_is_int(type)) {  \
980                                                         warn_type(col, type);    \
981                                                 } else {                     \
982                                                         warn_length(col, n);  \
983                                                 }
984                                 case SQL_TINYINT:
985                                         if (type != RQ_UINTEGER1) {
986                                                 WARN_TYPE_OR_LENGTH(size)
987                                         }
988                                         break;
989                                 case SQL_C_STINYINT:
990                                         if (type != RQ_INTEGER1) {
991                                                 WARN_TYPE_OR_LENGTH(size)
992                                         }
993                                         break;
994                                 case SQL_C_USHORT:
995                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
996                                                 WARN_TYPE_OR_LENGTH(size)
997                                         }
998                                         break;
999                                 case SQL_SMALLINT:
1000                                 case SQL_C_SSHORT:
1001                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
1002                                                 WARN_TYPE_OR_LENGTH(size)
1003                                         }
1004                                         break;
1005                                 case SQL_C_ULONG:
1006                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1007                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1008                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1009                                                 type != RQ_INTEGER4) {
1010                                                 WARN_TYPE_OR_LENGTH(size)
1011                                         }
1012                                         break;
1013                                 case SQL_INTEGER:
1014                                 case SQL_C_SLONG:
1015                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1016                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1017                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1018                                                 type != RQ_INTEGER4) {
1019                                                 WARN_TYPE_OR_LENGTH(size)
1020                                         }
1021                                         break;
1022                                 case SQL_C_UBIGINT:
1023                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1024                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1025                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1026                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1027                                                 type != RQ_INTEGER8) {
1028                                                 WARN_TYPE_OR_LENGTH(size)
1029                                         }
1030                                         break;
1031                                 case SQL_BIGINT:
1032                                 case SQL_C_SBIGINT:
1033                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1034                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1035                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1036                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1037                                                 type != RQ_INTEGER8) {
1038                                                 WARN_TYPE_OR_LENGTH(size)
1039                                         }
1040                                         break;
1041 #undef WARN_TYPE_OR_LENGTH
1042                                 case SQL_NUMERIC:
1043                                 case SQL_DECIMAL:
1044                                 case SQL_FLOAT:
1045                                 case SQL_REAL:
1046                                 case SQL_DOUBLE:
1047                                         if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
1048                                                 warn_type(col, type);
1049                                         }
1050                                         break;
1051                                 default:
1052                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
1053                                 }
1054                                 break;
1055                         }
1056                 }
1057                 if (!col) {
1058                         ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
1059                 }
1060         }
1061         AST_RWLIST_UNLOCK(&tableptr->columns);
1062         return 0;
1063 }
1064 #undef warn_length
1065 #undef warn_type
1066
1067 static int unload_odbc(const char *a, const char *b)
1068 {
1069         return ast_odbc_clear_cache(a, b);
1070 }
1071
1072 static struct ast_config_engine odbc_engine = {
1073         .name = "odbc",
1074         .load_func = config_odbc,
1075         .realtime_func = realtime_odbc,
1076         .realtime_multi_func = realtime_multi_odbc,
1077         .store_func = store_odbc,
1078         .destroy_func = destroy_odbc,
1079         .update_func = update_odbc,
1080         .update2_func = update2_odbc,
1081         .require_func = require_odbc,
1082         .unload_func = unload_odbc,
1083 };
1084
1085 static int unload_module (void)
1086 {
1087         ast_config_engine_deregister(&odbc_engine);
1088
1089         ast_verb(1, "res_config_odbc unloaded.\n");
1090         return 0;
1091 }
1092
1093 static int load_module (void)
1094 {
1095         ast_config_engine_register(&odbc_engine);
1096         ast_verb(1, "res_config_odbc loaded.\n");
1097         return 0;
1098 }
1099
1100 static int reload_module(void)
1101 {
1102         return 0;
1103 }
1104
1105 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "Realtime ODBC configuration",
1106                 .load = load_module,
1107                 .unload = unload_module,
1108                 .reload = reload_module,
1109                 .load_pri = AST_MODPRI_REALTIME_DRIVER,
1110                 );