246dd80a8f9c578dabf43f1f6803f1b245948d6f
[asterisk/asterisk.git] / res / res_config_odbc.c
1 /*
2  * Asterisk -- A telephony toolkit for Linux.
3  *
4  * Copyright (C) 1999 - 2005, Digium, Inc.
5  *
6  * Mark Spencer <markster@digium.com>
7  *
8  * res_config_odbc.c <odbc+odbc plugin for portable configuration engine >
9  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
10  */
11
12 #include <asterisk/file.h>
13 #include <asterisk/logger.h>
14 #include <asterisk/channel.h>
15 #include <asterisk/pbx.h>
16 #include <asterisk/config.h>
17 #include <asterisk/config_pvt.h>
18 #include <asterisk/module.h>
19 #include <asterisk/lock.h>
20 #include <asterisk/options.h>
21 #include <stdlib.h>
22 #include <unistd.h>
23 #include <string.h>
24 #include <asterisk/res_odbc.h>
25 #include <asterisk/utils.h>
26
27 static char *tdesc = "ODBC Configuration";
28 static struct ast_config_reg reg1;
29
30 STANDARD_LOCAL_USER;
31
32 LOCAL_USER_DECL;
33
34 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
35 {
36         odbc_obj *obj;
37         SQLHSTMT stmt;
38         char sql[1024];
39         char coltitle[256];
40         char rowdata[2048];
41         char *op;
42         const char *newparam, *newval;
43         char *stringp;
44         char *chunk;
45         SQLSMALLINT collen;
46         int res;
47         int x;
48         struct ast_variable *var=NULL, *prev=NULL;
49         SQLLEN rowcount=0;
50         SQLULEN colsize;
51         SQLSMALLINT colcount=0;
52         SQLSMALLINT datatype;
53         SQLSMALLINT decimaldigits;
54         SQLSMALLINT nullable;
55         va_list aq;
56         
57         va_copy(aq, ap);
58         
59         
60         if (!table)
61                 return NULL;
62
63         obj = fetch_odbc_obj(database);
64         if (!obj)
65                 return NULL;
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         newparam = va_arg(aq, const char *);
74         if (!newparam)  {
75                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
76                 return NULL;
77         }
78         newval = va_arg(aq, const char *);
79         if (!strchr(newparam, ' ')) op = " ="; else op = "";
80         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?", table, newparam, op);
81         while((newparam = va_arg(aq, const char *))) {
82                 if (!strchr(newparam, ' ')) op = " ="; else op = "";
83                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?", newparam, op);
84                 newval = va_arg(aq, const char *);
85         }
86         va_end(aq);
87         res = SQLPrepare(stmt, sql, SQL_NTS);
88         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
89                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
90                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
91                 return NULL;
92         }
93         
94         /* Now bind the parameters */
95         x = 1;
96
97         while((newparam = va_arg(ap, const char *))) {
98                 newval = va_arg(ap, const char *);
99                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
100         }
101                 
102         res = SQLExecute(stmt);
103
104         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
105                 ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
106                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
107                 return NULL;
108         }
109
110         res = SQLRowCount(stmt, &rowcount);
111         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
112                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
113                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
114                 return NULL;
115         }
116
117         res = SQLNumResultCols(stmt, &colcount);
118         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
119                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
120                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
121                 return NULL;
122         }
123
124         if (rowcount) {
125                 res = SQLFetch(stmt);
126                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
127                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
128                         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
129                         return NULL;
130                 }
131                 for (x=0;x<colcount;x++) {
132                         rowdata[0] = '\0';
133                         collen = sizeof(coltitle);
134                         res = SQLDescribeCol(stmt, x + 1, coltitle, sizeof(coltitle), &collen, 
135                                                 &datatype, &colsize, &decimaldigits, &nullable);
136                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
137                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
138                                 if (var)
139                                         ast_destroy_realtime(var);
140                                 return NULL;
141                         }
142                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), NULL);
143                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
144                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
145                                 if (var)
146                                         ast_destroy_realtime(var);
147                                 return NULL;
148                         }
149                         stringp = rowdata;
150                         while(stringp) {
151                                 chunk = strsep(&stringp, ";");
152                                 if (chunk && !ast_strlen_zero(ast_strip(chunk))) {
153                                         if (prev) {
154                                                 prev->next = ast_new_variable(coltitle, chunk);
155                                                 if (prev->next)
156                                                         prev = prev->next;
157                                         } else 
158                                                 prev = var = ast_new_variable(coltitle, chunk);
159                                         
160                                 }
161                         }
162                 }
163         }
164
165
166         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
167         return var;
168 }
169
170 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
171 {
172         odbc_obj *obj;
173         SQLHSTMT stmt;
174         char sql[1024];
175         char coltitle[256];
176         char rowdata[2048];
177         char *title=NULL;
178         const char *initfield=NULL;
179         char *op;
180         const char *newparam, *newval;
181         char *stringp;
182         char *chunk;
183         SQLSMALLINT collen;
184         int res;
185         int x;
186         struct ast_variable *var=NULL, *prev=NULL;
187         struct ast_config *cfg=NULL;
188         struct ast_category *cat=NULL;
189         struct ast_realloca ra;
190         SQLLEN rowcount=0;
191         SQLULEN colsize;
192         SQLSMALLINT colcount=0;
193         SQLSMALLINT datatype;
194         SQLSMALLINT decimaldigits;
195         SQLSMALLINT nullable;
196
197         va_list aq;
198         va_copy(aq, ap);
199         
200         
201         if (!table)
202                 return NULL;
203         memset(&ra, 0, sizeof(ra));
204
205         obj = fetch_odbc_obj(database);
206         if (!obj)
207                 return NULL;
208
209         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
210         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
211                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
212                 return NULL;
213         }
214
215         newparam = va_arg(aq, const char *);
216         if (!newparam)  {
217                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
218                 return NULL;
219         }
220         initfield = ast_strdupa(newparam);
221         if (initfield && (op = strchr(initfield, ' '))) 
222                 *op = '\0';
223         newval = va_arg(aq, const char *);
224         if (!strchr(newparam, ' ')) op = " ="; else op = "";
225         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?", table, newparam, op);
226         while((newparam = va_arg(aq, const char *))) {
227                 if (!strchr(newparam, ' ')) op = " ="; else op = "";
228                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?", newparam, op);
229                 newval = va_arg(aq, const char *);
230         }
231         if (initfield)
232                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
233         va_end(aq);
234         res = SQLPrepare(stmt, sql, SQL_NTS);
235         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
236                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
237                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
238                 return NULL;
239         }
240         
241         /* Now bind the parameters */
242         x = 1;
243
244         while((newparam = va_arg(ap, const char *))) {
245                 newval = va_arg(ap, const char *);
246                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
247         }
248                 
249         res = SQLExecute(stmt);
250
251         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
252                 ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
253                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
254                 return NULL;
255         }
256
257         res = SQLRowCount(stmt, &rowcount);
258         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
259                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
260                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
261                 return NULL;
262         }
263
264         res = SQLNumResultCols(stmt, &colcount);
265         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
266                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
267                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
268                 return NULL;
269         }
270
271         while (rowcount--) {
272                 var = NULL;
273                 prev = NULL;
274                 title = NULL;
275                 res = SQLFetch(stmt);
276                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
277                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
278                         continue;
279                 }
280                 for (x=0;x<colcount;x++) {
281                         rowdata[0] = '\0';
282                         collen = sizeof(coltitle);
283                         res = SQLDescribeCol(stmt, x + 1, coltitle, sizeof(coltitle), &collen, 
284                                                 &datatype, &colsize, &decimaldigits, &nullable);
285                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
286                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
287                                 if (var)
288                                         ast_destroy_realtime(var);
289                                 continue;
290                         }
291                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), NULL);
292                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
293                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
294                                 if (var)
295                                         ast_destroy_realtime(var);
296                                 continue;
297                         }
298                         stringp = rowdata;
299                         while(stringp) {
300                                 chunk = strsep(&stringp, ";");
301                                 if (chunk && !ast_strlen_zero(ast_strip(chunk))) {
302                                         if (initfield && !strcmp(initfield, coltitle) && !title)
303                                                 title = ast_restrdupa(&ra, chunk);
304                                         if (prev) {
305                                                 prev->next = ast_new_variable(coltitle, chunk);
306                                                 if (prev->next)
307                                                         prev = prev->next;
308                                         } else 
309                                                 prev = var = ast_new_variable(coltitle, chunk);
310                                         
311                                 }
312                         }
313                 }
314                 if (var) {
315                         cat = ast_new_category(title ? title : "");
316                         if (cat) {
317                                 cat->root = var;
318                                 if (!cfg) 
319                                         cfg = ast_new_config();
320                                 if (cfg)
321                                         ast_category_append(cfg, cat);
322                                 else 
323                                         ast_category_destroy(cat);
324                         } else {
325                                 ast_log(LOG_WARNING, "Out of memory!\n");
326                                 ast_destroy_realtime(var);
327                         }
328                 }
329         }
330
331         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
332         return cfg;
333 }
334
335 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
336 {
337         odbc_obj *obj;
338         SQLHSTMT stmt;
339         char sql[256];
340         SQLLEN rowcount=0;
341         const char *newparam, *newval;
342         int res;
343         int x;
344         va_list aq;
345         
346         va_copy(aq, ap);
347         
348         if (!table)
349                 return -1;
350
351         obj = fetch_odbc_obj (database);
352         if (!obj)
353                 return -1;
354
355         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
356         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
357                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
358                 return -1;
359         }
360
361         newparam = va_arg(aq, const char *);
362         if (!newparam)  {
363                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
364                 return -1;
365         }
366         newval = va_arg(aq, const char *);
367         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
368         while((newparam = va_arg(aq, const char *))) {
369                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
370                 newval = va_arg(aq, const char *);
371         }
372         va_end(aq);
373         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
374         
375         res = SQLPrepare(stmt, sql, SQL_NTS);
376         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
377                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
378                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
379                 return -1;
380         }
381         
382         /* Now bind the parameters */
383         x = 1;
384
385         while((newparam = va_arg(ap, const char *))) {
386                 newval = va_arg(ap, const char *);
387                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
388         }
389                 
390         SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(lookup), 0, (void *)lookup, 0, NULL);
391
392         res = SQLExecute(stmt);
393
394         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
395                 ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
396                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
397                 return -1;
398         }
399
400         res = SQLRowCount(stmt, &rowcount);
401         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
402
403         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
404                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
405                 return -1;
406         }
407
408         if (rowcount) 
409                 return 0;
410         return -1;
411 }
412
413 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *new_config_s, struct ast_category **new_cat_p, int recur)
414 {
415         struct ast_config *new;
416         struct ast_variable *cur_v, *new_v;
417         struct ast_category *cur_cat, *new_cat;
418         int res = 0;
419         odbc_obj *obj;
420         SQLINTEGER err=0, commented=0, cat_metric=0, var_metric=0, last_cat_metric=0;
421         SQLBIGINT id;
422         char sql[255] = "", filename[128], category[128], var_name[128], var_val[512];
423         SQLSMALLINT rowcount=0;
424         SQLHSTMT stmt;
425         char last[80] = "";
426         int cat_started = 0;
427         int var_started = 0;
428
429
430         if (!file || !strcmp (file, "res_config_odbc.conf"))
431                 return NULL;            /* cant configure myself with myself ! */
432
433         obj = fetch_odbc_obj(database);
434         if (!obj)
435                 return NULL;
436
437         last[0] = '\0';
438
439         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
440
441         SQLBindCol (stmt, 1, SQL_C_ULONG, &id, sizeof (id), &err);
442         SQLBindCol (stmt, 2, SQL_C_ULONG, &cat_metric, sizeof (cat_metric), &err);
443         SQLBindCol (stmt, 3, SQL_C_ULONG, &var_metric, sizeof (var_metric), &err);
444         SQLBindCol (stmt, 4, SQL_C_ULONG, &commented, sizeof (commented), &err);
445         SQLBindCol (stmt, 5, SQL_C_CHAR, &filename, sizeof (filename), &err);
446         SQLBindCol (stmt, 6, SQL_C_CHAR, &category, sizeof (category), &err);
447         SQLBindCol (stmt, 7, SQL_C_CHAR, &var_name, sizeof (var_name), &err);
448         SQLBindCol (stmt, 8, SQL_C_CHAR, &var_val, sizeof (var_val), &err);
449
450         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE filename='%s' and commented=0 ORDER BY filename,cat_metric desc,var_metric asc,category,var_name,var_val,id", table, file);
451         res = SQLExecDirect (stmt, sql, SQL_NTS);
452
453         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
454                 ast_log (LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
455                 return NULL;
456         }
457
458         res = SQLNumResultCols (stmt, &rowcount);
459
460         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
461                 ast_log (LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
462                 return NULL;
463         }
464
465         if (new_config_s) {
466                 new = new_config_s;
467                 cat_started++;
468         } else {
469                 new = ast_new_config ();
470         }
471         
472         if (!new) {
473                 ast_log(LOG_WARNING, "Out of memory!\n");
474                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
475                 return NULL;
476         }
477
478         if (rowcount) {
479                 res = SQLFetch (stmt);
480                 cat_started = 0;
481
482                 cur_cat = *new_cat_p;
483                 cur_v = NULL;
484
485                 if (cur_cat)
486                         cat_started = 1;
487                 if (cur_v)
488                         var_started = 1;
489
490                 while (res != SQL_NO_DATA) {
491                         if (!strcmp (var_name, "#include") && recur < MAX_INCLUDE_LEVEL) {
492
493                                 config_odbc(database, table, var_val, new, &cur_cat, recur + 1);
494                         } else {
495                                 if (strcmp (last, category) || last_cat_metric != cat_metric) {
496                                         strncpy(last, category, sizeof(last) - 1);
497                                         last_cat_metric = cat_metric;
498                                         new_cat = (struct ast_category *) ast_new_category (category);
499
500                                         if (!cat_started) {
501                                                 cat_started++;
502                                                 new->root = new_cat;
503                                                 cur_cat = new->root;
504                                         } else {
505                                                 cur_cat->next = new_cat;
506                                                 cur_cat = cur_cat->next;
507                                         }
508                                         var_started = 0;
509
510                                 }
511
512                                 new_v = ast_new_variable (var_name, var_val);
513
514                                 if (!var_started) {
515                                         var_started++;
516                                         cur_cat->root = new_v;
517                                         cur_v = cur_cat->root;
518                                 } else {
519                                         cur_v->next = new_v;
520                                         cur_v = cur_v->next;
521                                 }
522                         }
523
524                 /* next row  */
525                         res = SQLFetch (stmt);
526                 }
527
528                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
529         } else {
530                 ast_log (LOG_NOTICE, "found nothing\n");
531         }
532         return new;
533
534 }
535
536 int unload_module (void)
537 {
538         ast_cust_config_deregister (&reg1);
539         if (option_verbose)
540                 ast_verbose("res_config_odbc unloaded.\n");
541         STANDARD_HANGUP_LOCALUSERS;
542         return 0;
543 }
544
545 int load_module (void)
546 {
547         memset (&reg1, 0, sizeof (struct ast_config_reg));
548         strncpy(reg1.name, "odbc", sizeof(reg1.name) - 1);
549         reg1.static_func = config_odbc;
550         reg1.realtime_func = realtime_odbc;
551         reg1.realtime_multi_func = realtime_multi_odbc;
552         reg1.update_func = update_odbc;
553         ast_cust_config_register (&reg1);
554         if (option_verbose)
555                 ast_verbose("res_config_odbc loaded.\n");
556         return 0;
557 }
558
559 char *description (void)
560 {
561         return tdesc;
562 }
563
564 int usecount (void)
565 {
566         /* never unload a config module */
567         return 1;
568 }
569
570 char *key ()
571 {
572         return ASTERISK_GPL_KEY;
573 }