786e14bca108eebc16ec2ea946d88de0af34959b
[asterisk/asterisk.git] / res / res_config_odbc.c
1 /*
2  * Asterisk -- A telephony toolkit for Linux.
3  *
4  * Copyright (C) 1999-2004, 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 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 *op;
178         const char *newparam, *newval;
179         char *stringp;
180         char *chunk;
181         SQLSMALLINT collen;
182         int res;
183         int x;
184         struct ast_variable *var=NULL, *prev=NULL;
185         struct ast_config *cfg=NULL;
186         struct ast_category *cat=NULL;
187         SQLLEN rowcount=0;
188         SQLULEN colsize;
189         SQLSMALLINT colcount=0;
190         SQLSMALLINT datatype;
191         SQLSMALLINT decimaldigits;
192         SQLSMALLINT nullable;
193         va_list aq;
194         
195         va_copy(aq, ap);
196         
197         
198         if (!table)
199                 return NULL;
200
201         obj = fetch_odbc_obj(database);
202         if (!obj)
203                 return NULL;
204
205         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
206         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
207                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
208                 return NULL;
209         }
210
211         newparam = va_arg(aq, const char *);
212         if (!newparam)  {
213                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
214                 return NULL;
215         }
216         newval = va_arg(aq, const char *);
217         if (!strchr(newparam, ' ')) op = " ="; else op = "";
218         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?", table, newparam, op);
219         while((newparam = va_arg(aq, const char *))) {
220                 if (!strchr(newparam, ' ')) op = " ="; else op = "";
221                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?", newparam, op);
222                 newval = va_arg(aq, const char *);
223         }
224         va_end(aq);
225         res = SQLPrepare(stmt, sql, SQL_NTS);
226         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
227                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
228                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
229                 return NULL;
230         }
231         
232         /* Now bind the parameters */
233         x = 1;
234
235         while((newparam = va_arg(ap, const char *))) {
236                 newval = va_arg(ap, const char *);
237                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
238         }
239                 
240         res = SQLExecute(stmt);
241
242         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
243                 ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
244                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
245                 return NULL;
246         }
247
248         res = SQLRowCount(stmt, &rowcount);
249         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
250                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
251                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
252                 return NULL;
253         }
254
255         res = SQLNumResultCols(stmt, &colcount);
256         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
257                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
258                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
259                 return NULL;
260         }
261
262         while (rowcount) {
263                 var = NULL;
264                 prev = NULL;
265                 res = SQLFetch(stmt);
266                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
267                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
268                         continue;
269                 }
270                 for (x=0;x<colcount;x++) {
271                         rowdata[0] = '\0';
272                         collen = sizeof(coltitle);
273                         res = SQLDescribeCol(stmt, x + 1, coltitle, sizeof(coltitle), &collen, 
274                                                 &datatype, &colsize, &decimaldigits, &nullable);
275                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
276                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
277                                 if (var)
278                                         ast_destroy_realtime(var);
279                                 continue;
280                         }
281                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), NULL);
282                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
283                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
284                                 if (var)
285                                         ast_destroy_realtime(var);
286                                 continue;
287                         }
288                         stringp = rowdata;
289                         while(stringp) {
290                                 chunk = strsep(&stringp, ";");
291                                 if (chunk && !ast_strlen_zero(ast_strip(chunk))) {
292                                         if (prev) {
293                                                 prev->next = ast_new_variable(coltitle, chunk);
294                                                 if (prev->next)
295                                                         prev = prev->next;
296                                         } else 
297                                                 prev = var = ast_new_variable(coltitle, chunk);
298                                         
299                                 }
300                         }
301                         if (var) {
302                                 cat = ast_new_category("");
303                                 if (cat) {
304                                         cat->root = var;
305                                         if (!cfg) 
306                                                 cfg = ast_new_config();
307                                         if (cfg)
308                                                 ast_category_append(cfg, cat);
309                                         else 
310                                                 ast_category_destroy(cat);
311                                 } else {
312                                         ast_log(LOG_WARNING, "Out of memory!\n");
313                                         ast_destroy_realtime(var);
314                                 }
315                         }
316                 }
317         }
318
319         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
320         return cfg;
321 }
322
323 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
324 {
325         odbc_obj *obj;
326         SQLHSTMT stmt;
327         char sql[256];
328         SQLLEN rowcount=0;
329         const char *newparam, *newval;
330         int res;
331         int x;
332         va_list aq;
333         
334         va_copy(aq, ap);
335         
336         if (!table)
337                 return -1;
338
339         obj = fetch_odbc_obj (database);
340         if (!obj)
341                 return -1;
342
343         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
344         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
345                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
346                 return -1;
347         }
348
349         newparam = va_arg(aq, const char *);
350         if (!newparam)  {
351                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
352                 return -1;
353         }
354         newval = va_arg(aq, const char *);
355         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
356         while((newparam = va_arg(aq, const char *))) {
357                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
358                 newval = va_arg(aq, const char *);
359         }
360         va_end(aq);
361         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
362         
363         res = SQLPrepare(stmt, sql, SQL_NTS);
364         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
365                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
366                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
367                 return -1;
368         }
369         
370         /* Now bind the parameters */
371         x = 1;
372
373         while((newparam = va_arg(ap, const char *))) {
374                 newval = va_arg(ap, const char *);
375                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
376         }
377                 
378         SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(lookup), 0, (void *)lookup, 0, NULL);
379
380         res = SQLExecute(stmt);
381
382         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
383                 ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql);
384                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
385                 return -1;
386         }
387
388         res = SQLRowCount(stmt, &rowcount);
389         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
390
391         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
392                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
393                 return -1;
394         }
395
396         if (rowcount) 
397                 return 0;
398         return -1;
399 }
400
401 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, struct ast_variable **new_v_p, int recur)
402 {
403         struct ast_config *new;
404         struct ast_variable *cur_v, *new_v;
405         struct ast_category *cur_cat, *new_cat;
406         int res = 0;
407         odbc_obj *obj;
408         SQLINTEGER err=0, commented=0, cat_metric=0, var_metric=0, last_cat_metric=0;
409         SQLBIGINT id;
410         char sql[255] = "", filename[128], category[128], var_name[128], var_val[128];
411         SQLSMALLINT rowcount=0;
412         SQLHSTMT stmt;
413         char last[80] = "";
414         int cat_started = 0;
415         int var_started = 0;
416
417
418         if (!file || !strcmp (file, "res_config_odbc.conf"))
419                 return NULL;            // cant configure myself with myself !
420
421         obj = fetch_odbc_obj(database);
422         if (!obj)
423                 return NULL;
424
425         last[0] = '\0';
426
427         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
428
429         SQLBindCol (stmt, 1, SQL_C_ULONG, &id, sizeof (id), &err);
430         SQLBindCol (stmt, 2, SQL_C_ULONG, &cat_metric, sizeof (cat_metric), &err);
431         SQLBindCol (stmt, 3, SQL_C_ULONG, &var_metric, sizeof (var_metric), &err);
432         SQLBindCol (stmt, 4, SQL_C_ULONG, &commented, sizeof (commented), &err);
433         SQLBindCol (stmt, 5, SQL_C_CHAR, &filename, sizeof (filename), &err);
434         SQLBindCol (stmt, 6, SQL_C_CHAR, &category, sizeof (category), &err);
435         SQLBindCol (stmt, 7, SQL_C_CHAR, &var_name, sizeof (var_name), &err);
436         SQLBindCol (stmt, 8, SQL_C_CHAR, &var_val, sizeof (var_val), &err);
437
438         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);
439         res = SQLExecDirect (stmt, sql, SQL_NTS);
440
441         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
442                 ast_log (LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
443                 return NULL;
444         }
445
446         res = SQLNumResultCols (stmt, &rowcount);
447
448         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
449                 ast_log (LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
450                 return NULL;
451         }
452
453         if (new_config_s) {
454                 new = new_config_s;
455                 cat_started++;
456         } else {
457                 new = ast_new_config ();
458         }
459         
460         if (!new) {
461                 ast_log(LOG_WARNING, "Out of memory!\n");
462                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
463                 return NULL;
464         }
465
466         if (rowcount) {
467                 res = SQLFetch (stmt);
468                 cat_started = 0;
469
470                 cur_cat = *new_cat_p;
471                 cur_v = *new_v_p;
472
473                 if (cur_cat)
474                         cat_started = 1;
475                 if (cur_v)
476                         var_started = 1;
477
478                 while (res != SQL_NO_DATA) {
479                         if (!strcmp (var_name, "#include") && recur < MAX_INCLUDE_LEVEL) {
480
481                                 config_odbc(database, table, var_val, new, &cur_cat, &cur_v, recur + 1);
482                         } else {
483                                 if (strcmp (last, category) || last_cat_metric != cat_metric) {
484                                         strncpy(last, category, sizeof(last) - 1);
485                                         last_cat_metric = cat_metric;
486                                         new_cat = (struct ast_category *) ast_new_category (category);
487
488                                         if (!cat_started) {
489                                                 cat_started++;
490                                                 new->root = new_cat;
491                                                 cur_cat = new->root;
492                                         } else {
493                                                 cur_cat->next = new_cat;
494                                                 cur_cat = cur_cat->next;
495                                         }
496                                         var_started = 0;
497
498                                 }
499
500                                 new_v = ast_new_variable (var_name, var_val);
501
502                                 if (!var_started) {
503                                         var_started++;
504                                         cur_cat->root = new_v;
505                                         cur_v = cur_cat->root;
506                                 } else {
507                                         cur_v->next = new_v;
508                                         cur_v = cur_v->next;
509                                 }
510                         }
511
512                 // next row 
513                         res = SQLFetch (stmt);
514                 }
515
516                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
517         } else {
518                 ast_log (LOG_NOTICE, "found nothing\n");
519         }
520         return new;
521
522 }
523
524 int unload_module (void)
525 {
526         ast_cust_config_deregister (&reg1);
527         if (option_verbose)
528                 ast_verbose("res_config_odbc unloaded.\n");
529         STANDARD_HANGUP_LOCALUSERS;
530         return 0;
531 }
532
533 int load_module (void)
534 {
535         memset (&reg1, 0, sizeof (struct ast_config_reg));
536         strncpy(reg1.name, "odbc", sizeof(reg1.name) - 1);
537         reg1.static_func = config_odbc;
538         reg1.realtime_func = realtime_odbc;
539         reg1.realtime_multi_func = realtime_multi_odbc;
540         reg1.update_func = update_odbc;
541         ast_cust_config_register (&reg1);
542         if (option_verbose)
543                 ast_verbose("res_config_odbc loaded.\n");
544         return 0;
545 }
546
547 char *description (void)
548 {
549         return tdesc;
550 }
551
552 int usecount (void)
553 {
554         /* never unload a config module */
555         return 1;
556 }
557
558 char *key ()
559 {
560         return ASTERISK_GPL_KEY;
561 }