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