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