82d6c989dfad1e031f26c15ec39241558d796c96
[asterisk/asterisk.git] / res / res_config_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 1999 - 2005, 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>unixodbc_or_iodbc</depend>
33         <depend>ltdl</depend>
34         <depend>res_odbc</depend>
35         <use>unixodbc</use>
36         <use>iodbc</use>
37  ***/
38
39 #include "asterisk.h"
40
41 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
42
43 #include "asterisk/file.h"
44 #include "asterisk/channel.h"
45 #include "asterisk/pbx.h"
46 #include "asterisk/config.h"
47 #include "asterisk/module.h"
48 #include "asterisk/lock.h"
49 #include "asterisk/res_odbc.h"
50 #include "asterisk/utils.h"
51
52 struct custom_prepare_struct {
53         const char *sql;
54         const char *extra;
55         va_list ap;
56         unsigned long long skip;
57 };
58
59 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
60 {
61         int res, x = 1, count = 0;
62         struct custom_prepare_struct *cps = data;
63         const char *newparam, *newval;
64         SQLHSTMT stmt;
65         va_list ap;
66
67         va_copy(ap, cps->ap);
68
69         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
70         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
71                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
72                 return NULL;
73         }
74
75         res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
76         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
77                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
78                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
79                 return NULL;
80         }
81
82         while ((newparam = va_arg(ap, const char *))) {
83                 newval = va_arg(ap, const char *);
84                 if ((1 << count) & cps->skip) {
85                         continue;
86                 }
87                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
88         }
89         va_end(ap);
90
91         if (!ast_strlen_zero(cps->extra))
92                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
93         return stmt;
94 }
95
96 /*!
97  * \brief Excute an SQL query and return ast_variable list
98  * \param database
99  * \param table
100  * \param ap list containing one or more field/operator/value set.
101  *
102  * Select database and preform query on table, prepare the sql statement
103  * Sub-in the values to the prepared statement and execute it. Return results
104  * as a ast_variable list.
105  *
106  * \retval var on success
107  * \retval NULL on failure
108 */
109 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
110 {
111         struct odbc_obj *obj;
112         SQLHSTMT stmt;
113         char sql[1024];
114         char coltitle[256];
115         char rowdata[2048];
116         char *op;
117         const char *newparam, *newval;
118         char *stringp;
119         char *chunk;
120         SQLSMALLINT collen;
121         int res;
122         int x;
123         struct ast_variable *var=NULL, *prev=NULL;
124         SQLULEN colsize;
125         SQLSMALLINT colcount=0;
126         SQLSMALLINT datatype;
127         SQLSMALLINT decimaldigits;
128         SQLSMALLINT nullable;
129         SQLLEN indicator;
130         va_list aq;
131         struct custom_prepare_struct cps = { .sql = sql };
132
133         va_copy(cps.ap, ap);
134         va_copy(aq, ap);
135
136         if (!table)
137                 return NULL;
138
139         obj = ast_odbc_request_obj(database, 0);
140
141         if (!obj) {
142                 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
143                 return NULL;
144         }
145
146         newparam = va_arg(aq, const char *);
147         if (!newparam) {
148                 ast_odbc_release_obj(obj);
149                 return NULL;
150         }
151         newval = va_arg(aq, const char *);
152         op = !strchr(newparam, ' ') ? " =" : "";
153         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
154                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
155         while((newparam = va_arg(aq, const char *))) {
156                 op = !strchr(newparam, ' ') ? " =" : "";
157                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
158                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
159                 newval = va_arg(aq, const char *);
160         }
161         va_end(aq);
162
163         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
164
165         if (!stmt) {
166                 ast_odbc_release_obj(obj);
167                 return NULL;
168         }
169
170         res = SQLNumResultCols(stmt, &colcount);
171         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
172                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
173                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
174                 ast_odbc_release_obj(obj);
175                 return NULL;
176         }
177
178         res = SQLFetch(stmt);
179         if (res == SQL_NO_DATA) {
180                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
181                 ast_odbc_release_obj(obj);
182                 return NULL;
183         }
184         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
185                 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
186                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
187                 ast_odbc_release_obj(obj);
188                 return NULL;
189         }
190         for (x = 0; x < colcount; x++) {
191                 rowdata[0] = '\0';
192                 collen = sizeof(coltitle);
193                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
194                                         &datatype, &colsize, &decimaldigits, &nullable);
195                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
196                         ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
197                         if (var)
198                                 ast_variables_destroy(var);
199                         ast_odbc_release_obj(obj);
200                         return NULL;
201                 }
202
203                 indicator = 0;
204                 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
205                 if (indicator == SQL_NULL_DATA)
206                         rowdata[0] = '\0';
207                 else if (ast_strlen_zero(rowdata)) {
208                         /* Because we encode the empty string for a NULL, we will encode
209                          * actual empty strings as a string containing a single whitespace. */
210                         ast_copy_string(rowdata, " ", sizeof(rowdata));
211                 }
212
213                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
214                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
215                         if (var)
216                                 ast_variables_destroy(var);
217                         ast_odbc_release_obj(obj);
218                         return NULL;
219                 }
220                 stringp = rowdata;
221                 while(stringp) {
222                         chunk = strsep(&stringp, ";");
223                         if (!ast_strlen_zero(ast_strip(chunk))) {
224                                 if (prev) {
225                                         prev->next = ast_variable_new(coltitle, chunk, "");
226                                         if (prev->next)
227                                                 prev = prev->next;
228                                 } else 
229                                         prev = var = ast_variable_new(coltitle, chunk, "");
230                         }
231                 }
232         }
233
234
235         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
236         ast_odbc_release_obj(obj);
237         return var;
238 }
239
240 /*!
241  * \brief Excute an Select query and return ast_config list
242  * \param database
243  * \param table
244  * \param ap list containing one or more field/operator/value set.
245  *
246  * Select database and preform query on table, prepare the sql statement
247  * Sub-in the values to the prepared statement and execute it. 
248  * Execute this prepared query against several ODBC connected databases.
249  * Return results as an ast_config variable.
250  *
251  * \retval var on success
252  * \retval NULL on failure
253 */
254 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
255 {
256         struct odbc_obj *obj;
257         SQLHSTMT stmt;
258         char sql[1024];
259         char coltitle[256];
260         char rowdata[2048];
261         const char *initfield=NULL;
262         char *op;
263         const char *newparam, *newval;
264         char *stringp;
265         char *chunk;
266         SQLSMALLINT collen;
267         int res;
268         int x;
269         struct ast_variable *var=NULL;
270         struct ast_config *cfg=NULL;
271         struct ast_category *cat=NULL;
272         SQLULEN colsize;
273         SQLSMALLINT colcount=0;
274         SQLSMALLINT datatype;
275         SQLSMALLINT decimaldigits;
276         SQLSMALLINT nullable;
277         SQLLEN indicator;
278         struct custom_prepare_struct cps = { .sql = sql };
279         va_list aq;
280
281         va_copy(cps.ap, ap);
282         va_copy(aq, ap);
283
284         if (!table)
285                 return NULL;
286
287         obj = ast_odbc_request_obj(database, 0);
288         if (!obj)
289                 return NULL;
290
291         newparam = va_arg(aq, const char *);
292         if (!newparam)  {
293                 ast_odbc_release_obj(obj);
294                 return NULL;
295         }
296         initfield = ast_strdupa(newparam);
297         if ((op = strchr(initfield, ' '))) 
298                 *op = '\0';
299         newval = va_arg(aq, const char *);
300         op = !strchr(newparam, ' ') ? " =" : "";
301         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
302                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
303         while((newparam = va_arg(aq, const char *))) {
304                 op = !strchr(newparam, ' ') ? " =" : "";
305                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
306                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
307                 newval = va_arg(aq, const char *);
308         }
309         if (initfield)
310                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
311         va_end(aq);
312
313         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
314
315         if (!stmt) {
316                 ast_odbc_release_obj(obj);
317                 return NULL;
318         }
319
320         res = SQLNumResultCols(stmt, &colcount);
321         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
322                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
323                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
324                 ast_odbc_release_obj(obj);
325                 return NULL;
326         }
327
328         cfg = ast_config_new();
329         if (!cfg) {
330                 ast_log(LOG_WARNING, "Out of memory!\n");
331                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
332                 ast_odbc_release_obj(obj);
333                 return NULL;
334         }
335
336         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
337                 var = NULL;
338                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
339                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
340                         continue;
341                 }
342                 cat = ast_category_new("","",99999);
343                 if (!cat) {
344                         ast_log(LOG_WARNING, "Out of memory!\n");
345                         continue;
346                 }
347                 for (x=0;x<colcount;x++) {
348                         rowdata[0] = '\0';
349                         collen = sizeof(coltitle);
350                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
351                                                 &datatype, &colsize, &decimaldigits, &nullable);
352                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
353                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
354                                 ast_category_destroy(cat);
355                                 continue;
356                         }
357
358                         indicator = 0;
359                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
360                         if (indicator == SQL_NULL_DATA)
361                                 continue;
362
363                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
364                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
365                                 ast_category_destroy(cat);
366                                 continue;
367                         }
368                         stringp = rowdata;
369                         while(stringp) {
370                                 chunk = strsep(&stringp, ";");
371                                 if (!ast_strlen_zero(ast_strip(chunk))) {
372                                         if (initfield && !strcmp(initfield, coltitle))
373                                                 ast_category_rename(cat, chunk);
374                                         var = ast_variable_new(coltitle, chunk, "");
375                                         ast_variable_append(cat, var);
376                                 }
377                         }
378                 }
379                 ast_category_append(cfg, cat);
380         }
381
382         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
383         ast_odbc_release_obj(obj);
384         return cfg;
385 }
386
387 /*!
388  * \brief Excute an UPDATE query
389  * \param database
390  * \param table
391  * \param keyfield where clause field
392  * \param lookup value of field for where clause
393  * \param ap list containing one or more field/value set(s).
394  *
395  * Update a database table, prepare the sql statement using keyfield and lookup
396  * control the number of records to change. All values to be changed are stored in ap list.
397  * Sub-in the values to the prepared statement and execute it.
398  *
399  * \retval number of rows affected
400  * \retval -1 on failure
401 */
402 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
403 {
404         struct odbc_obj *obj;
405         SQLHSTMT stmt;
406         char sql[256];
407         SQLLEN rowcount=0;
408         const char *newparam, *newval;
409         int res, count = 0;
410         va_list aq;
411         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
412         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
413         struct odbc_cache_columns *column;
414
415         va_copy(cps.ap, ap);
416         va_copy(aq, ap);
417         
418         if (!table) {
419                 ast_odbc_release_table(tableptr);
420                 return -1;
421         }
422
423         obj = ast_odbc_request_obj(database, 0);
424         if (!obj) {
425                 ast_odbc_release_table(tableptr);
426                 return -1;
427         }
428
429         newparam = va_arg(aq, const char *);
430         if (!newparam)  {
431                 ast_odbc_release_obj(obj);
432                 ast_odbc_release_table(tableptr);
433                 return -1;
434         }
435         newval = va_arg(aq, const char *);
436
437         if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
438                 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", newparam, table, database);
439         }
440
441         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
442         while((newparam = va_arg(aq, const char *))) {
443                 if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
444                         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
445                         newval = va_arg(aq, const char *);
446                 } else { /* the column does not exist in the table OR we've exceeded the space in our flag field */
447                         cps.skip |= (((long long)1) << count);
448                 }
449                 count++;
450         }
451         va_end(aq);
452         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
453         ast_odbc_release_table(tableptr);
454
455         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
456
457         if (!stmt) {
458                 ast_odbc_release_obj(obj);
459                 return -1;
460         }
461
462         res = SQLRowCount(stmt, &rowcount);
463         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
464         ast_odbc_release_obj(obj);
465
466         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
467                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
468                 return -1;
469         }
470
471         if (rowcount >= 0)
472                 return (int)rowcount;
473
474         return -1;
475 }
476
477 /*!
478  * \brief Excute an INSERT query
479  * \param database
480  * \param table
481  * \param ap list containing one or more field/value set(s)
482  *
483  * Insert a new record into database table, prepare the sql statement.
484  * All values to be changed are stored in ap list.
485  * Sub-in the values to the prepared statement and execute it.
486  *
487  * \retval number of rows affected
488  * \retval -1 on failure
489 */
490 static int store_odbc(const char *database, const char *table, va_list ap)
491 {
492         struct odbc_obj *obj;
493         SQLHSTMT stmt;
494         char sql[256];
495         char keys[256];
496         char vals[256];
497         SQLLEN rowcount=0;
498         const char *newparam, *newval;
499         int res;
500         va_list aq;
501         struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
502
503         va_copy(cps.ap, ap);
504         va_copy(aq, ap);
505         
506         if (!table)
507                 return -1;
508
509         obj = ast_odbc_request_obj(database, 0);
510         if (!obj)
511                 return -1;
512
513         newparam = va_arg(aq, const char *);
514         if (!newparam)  {
515                 ast_odbc_release_obj(obj);
516                 return -1;
517         }
518         newval = va_arg(aq, const char *);
519         snprintf(keys, sizeof(keys), "%s", newparam);
520         ast_copy_string(vals, "?", sizeof(vals));
521         while ((newparam = va_arg(aq, const char *))) {
522                 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
523                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
524                 newval = va_arg(aq, const char *);
525         }
526         va_end(aq);
527         snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
528
529         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
530
531         if (!stmt) {
532                 ast_odbc_release_obj(obj);
533                 return -1;
534         }
535
536         res = SQLRowCount(stmt, &rowcount);
537         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
538         ast_odbc_release_obj(obj);
539
540         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
541                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
542                 return -1;
543         }
544
545         if (rowcount >= 0)
546                 return (int)rowcount;
547
548         return -1;
549 }
550
551 /*!
552  * \brief Excute an DELETE query
553  * \param database
554  * \param table
555  * \param keyfield where clause field
556  * \param lookup value of field for where clause
557  * \param ap list containing one or more field/value set(s)
558  *
559  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
560  * control the number of records to change. Additional params to match rows are stored in ap list.
561  * Sub-in the values to the prepared statement and execute it.
562  *
563  * \retval number of rows affected
564  * \retval -1 on failure
565 */
566 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
567 {
568         struct odbc_obj *obj;
569         SQLHSTMT stmt;
570         char sql[256];
571         SQLLEN rowcount=0;
572         const char *newparam, *newval;
573         int res;
574         va_list aq;
575         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
576
577         va_copy(cps.ap, ap);
578         va_copy(aq, ap);
579         
580         if (!table)
581                 return -1;
582
583         obj = ast_odbc_request_obj(database, 0);
584         if (!obj)
585                 return -1;
586
587         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
588         while((newparam = va_arg(aq, const char *))) {
589                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
590                 newval = va_arg(aq, const char *);
591         }
592         va_end(aq);
593         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
594
595         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
596
597         if (!stmt) {
598                 ast_odbc_release_obj(obj);
599                 return -1;
600         }
601
602         res = SQLRowCount(stmt, &rowcount);
603         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
604         ast_odbc_release_obj(obj);
605
606         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
607                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
608                 return -1;
609         }
610
611         if (rowcount >= 0)
612                 return (int)rowcount;
613
614         return -1;
615 }
616
617
618 struct config_odbc_obj {
619         char *sql;
620         unsigned long cat_metric;
621         char category[128];
622         char var_name[128];
623         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
624         SQLLEN err;
625 };
626
627 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
628 {
629         struct config_odbc_obj *q = data;
630         SQLHSTMT sth;
631         int res;
632
633         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
634         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
635                 ast_verb(4, "Failure in AllocStatement %d\n", res);
636                 return NULL;
637         }
638
639         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
640         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
641                 ast_verb(4, "Error in PREPARE %d\n", res);
642                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
643                 return NULL;
644         }
645
646         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
647         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
648         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
649         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
650
651         return sth;
652 }
653
654 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)
655 {
656         struct ast_variable *new_v;
657         struct ast_category *cur_cat;
658         int res = 0;
659         struct odbc_obj *obj;
660         char sqlbuf[1024] = "";
661         char *sql = sqlbuf;
662         size_t sqlleft = sizeof(sqlbuf);
663         unsigned int last_cat_metric = 0;
664         SQLSMALLINT rowcount = 0;
665         SQLHSTMT stmt;
666         char last[128] = "";
667         struct config_odbc_obj q;
668         struct ast_flags loader_flags = { 0 };
669
670         memset(&q, 0, sizeof(q));
671
672         if (!file || !strcmp (file, "res_config_odbc.conf"))
673                 return NULL;            /* cant configure myself with myself ! */
674
675         obj = ast_odbc_request_obj(database, 0);
676         if (!obj)
677                 return NULL;
678
679         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
680         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
681         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
682         q.sql = sqlbuf;
683
684         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
685
686         if (!stmt) {
687                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
688                 ast_odbc_release_obj(obj);
689                 return NULL;
690         }
691
692         res = SQLNumResultCols(stmt, &rowcount);
693
694         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
695                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
696                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
697                 ast_odbc_release_obj(obj);
698                 return NULL;
699         }
700
701         if (!rowcount) {
702                 ast_log(LOG_NOTICE, "found nothing\n");
703                 ast_odbc_release_obj(obj);
704                 return cfg;
705         }
706
707         cur_cat = ast_config_get_current_category(cfg);
708
709         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
710                 if (!strcmp (q.var_name, "#include")) {
711                         if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
712                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
713                                 ast_odbc_release_obj(obj);
714                                 return NULL;
715                         }
716                         continue;
717                 } 
718                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
719                         cur_cat = ast_category_new(q.category, "", 99999);
720                         if (!cur_cat) {
721                                 ast_log(LOG_WARNING, "Out of memory!\n");
722                                 break;
723                         }
724                         strcpy(last, q.category);
725                         last_cat_metric = q.cat_metric;
726                         ast_category_append(cfg, cur_cat);
727                 }
728
729                 new_v = ast_variable_new(q.var_name, q.var_val, "");
730                 ast_variable_append(cur_cat, new_v);
731         }
732
733         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
734         ast_odbc_release_obj(obj);
735         return cfg;
736 }
737
738 #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)
739 #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)
740
741 static int require_odbc(const char *database, const char *table, va_list ap)
742 {
743         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
744         struct odbc_cache_columns *col;
745         char *elm;
746         int type, size;
747
748         if (!tableptr) {
749                 return -1;
750         }
751
752         while ((elm = va_arg(ap, char *))) {
753                 type = va_arg(ap, require_type);
754                 size = va_arg(ap, int);
755                 /* Check if the field matches the criteria */
756                 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
757                         if (strcmp(col->name, elm) == 0) {
758                                 /* Type check, first.  Some fields are more particular than others */
759                                 switch (col->type) {
760                                 case SQL_CHAR:
761                                 case SQL_VARCHAR:
762                                 case SQL_LONGVARCHAR:
763                                 case SQL_BINARY:
764                                 case SQL_VARBINARY:
765                                 case SQL_LONGVARBINARY:
766                                 case SQL_GUID:
767 #define CHECK_SIZE(n) \
768                                                 if (col->size < n) {      \
769                                                         warn_length(col, n);  \
770                                                 }                         \
771                                                 break;
772                                         switch (type) {
773                                         case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
774                                         case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
775                                         case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
776                                         case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
777                                         case RQ_UINTEGER3:                /*    16777215 */
778                                         case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
779                                         case RQ_DATE:                     /*  2008-06-09 */
780                                         case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
781                                         case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
782                                         case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
783                                         case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
784                                         case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
785                                         case RQ_FLOAT:
786                                         case RQ_CHAR:      CHECK_SIZE(size)
787                                         }
788 #undef CHECK_SIZE
789                                         break;
790                                 case SQL_TYPE_DATE:
791                                         if (type != RQ_DATE) {
792                                                 warn_type(col, type);
793                                         }
794                                         break;
795                                 case SQL_TYPE_TIMESTAMP:
796                                 case SQL_TIMESTAMP:
797                                         if (type != RQ_DATE && type != RQ_DATETIME) {
798                                                 warn_type(col, type);
799                                         }
800                                         break;
801                                 case SQL_BIT:
802                                         warn_length(col, size);
803                                         break;
804 #define WARN_TYPE_OR_LENGTH(n)  \
805                                                 if (!ast_rq_is_int(type)) {  \
806                                                         warn_type(col, type);    \
807                                                 } else {                     \
808                                                         warn_length(col, n);  \
809                                                 }
810                                 case SQL_TINYINT:
811                                         if (type != RQ_UINTEGER1) {
812                                                 WARN_TYPE_OR_LENGTH(size)
813                                         }
814                                         break;
815                                 case SQL_C_STINYINT:
816                                         if (type != RQ_INTEGER1) {
817                                                 WARN_TYPE_OR_LENGTH(size)
818                                         }
819                                         break;
820                                 case SQL_C_USHORT:
821                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
822                                                 WARN_TYPE_OR_LENGTH(size)
823                                         }
824                                         break;
825                                 case SQL_SMALLINT:
826                                 case SQL_C_SSHORT:
827                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
828                                                 WARN_TYPE_OR_LENGTH(size)
829                                         }
830                                         break;
831                                 case SQL_C_ULONG:
832                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
833                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
834                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
835                                                 type != RQ_INTEGER4) {
836                                                 WARN_TYPE_OR_LENGTH(size)
837                                         }
838                                         break;
839                                 case SQL_INTEGER:
840                                 case SQL_C_SLONG:
841                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
842                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
843                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
844                                                 type != RQ_UINTEGER4) {
845                                                 WARN_TYPE_OR_LENGTH(size)
846                                         }
847                                         break;
848                                 case SQL_C_UBIGINT:
849                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
850                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
851                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
852                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
853                                                 type != RQ_INTEGER8) {
854                                                 WARN_TYPE_OR_LENGTH(size)
855                                         }
856                                         break;
857                                 case SQL_BIGINT:
858                                 case SQL_C_SBIGINT:
859                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
860                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
861                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
862                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
863                                                 type != RQ_UINTEGER8) {
864                                                 WARN_TYPE_OR_LENGTH(size)
865                                         }
866                                         break;
867 #undef WARN_TYPE_OR_LENGTH
868                                 case SQL_NUMERIC:
869                                 case SQL_DECIMAL:
870                                 case SQL_FLOAT:
871                                 case SQL_REAL:
872                                 case SQL_DOUBLE:
873                                         if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
874                                                 warn_type(col, type);
875                                         }
876                                         break;
877                                 default:
878                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
879                                 }
880                                 break;
881                         }
882                 }
883                 if (!col) {
884                         ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
885                 }
886         }
887         va_end(ap);
888         AST_RWLIST_UNLOCK(&tableptr->columns);
889         return 0;
890 }
891 #undef warn_length
892 #undef warn_type
893
894 static struct ast_config_engine odbc_engine = {
895         .name = "odbc",
896         .load_func = config_odbc,
897         .realtime_func = realtime_odbc,
898         .realtime_multi_func = realtime_multi_odbc,
899         .store_func = store_odbc,
900         .destroy_func = destroy_odbc,
901         .update_func = update_odbc,
902         .require_func = require_odbc,
903         .unload_func = ast_odbc_clear_cache,
904 };
905
906 static int unload_module (void)
907 {
908         ast_config_engine_deregister(&odbc_engine);
909
910         ast_verb(1, "res_config_odbc unloaded.\n");
911         return 0;
912 }
913
914 static int load_module (void)
915 {
916         ast_config_engine_register(&odbc_engine);
917         ast_verb(1, "res_config_odbc loaded.\n");
918         return 0;
919 }
920
921 static int reload_module(void)
922 {
923         return 0;
924 }
925
926 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_GLOBAL_SYMBOLS, "Realtime ODBC configuration",
927                 .load = load_module,
928                 .unload = unload_module,
929                 .reload = reload_module,
930                 );