2 * Asterisk -- An open source telephony toolkit.
4 * Copyright (C) 1999 - 2005, Digium, Inc.
6 * Mark Spencer <markster@digium.com>
8 * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
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.
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.
23 * \brief odbc+odbc plugin for portable configuration engine
25 * \author Mark Spencer <markster@digium.com>
26 * \author Anthony Minessale II <anthmct@yahoo.com>
28 * \arg http://www.unixodbc.org
32 <depend>unixodbc</depend>
34 <depend>res_odbc</depend>
39 ASTERISK_FILE_VERSION(__FILE__
, "$Revision$")
46 #include "asterisk/file.h"
47 #include "asterisk/logger.h"
48 #include "asterisk/channel.h"
49 #include "asterisk/pbx.h"
50 #include "asterisk/config.h"
51 #include "asterisk/module.h"
52 #include "asterisk/lock.h"
53 #include "asterisk/options.h"
54 #include "asterisk/res_odbc.h"
55 #include "asterisk/utils.h"
57 struct custom_prepare_struct
{
63 static SQLHSTMT
custom_prepare(struct odbc_obj
*obj
, void *data
)
66 struct custom_prepare_struct
*cps
= data
;
67 const char *newparam
, *newval
;
73 res
= SQLAllocHandle(SQL_HANDLE_STMT
, obj
->con
, &stmt
);
74 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
75 ast_log(LOG_WARNING
, "SQL Alloc Handle failed!\n");
79 res
= SQLPrepare(stmt
, (unsigned char *)cps
->sql
, SQL_NTS
);
80 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
81 ast_log(LOG_WARNING
, "SQL Prepare failed![%s]\n", cps
->sql
);
82 SQLFreeHandle (SQL_HANDLE_STMT
, stmt
);
86 while ((newparam
= va_arg(ap
, const char *))) {
87 newval
= va_arg(ap
, const char *);
88 SQLBindParameter(stmt
, x
++, SQL_PARAM_INPUT
, SQL_C_CHAR
, SQL_CHAR
, strlen(newval
), 0, (void *)newval
, 0, NULL
);
92 if (!ast_strlen_zero(cps
->extra
))
93 SQLBindParameter(stmt
, x
++, SQL_PARAM_INPUT
, SQL_C_CHAR
, SQL_CHAR
, strlen(cps
->extra
), 0, (void *)cps
->extra
, 0, NULL
);
97 static struct ast_variable
*realtime_odbc(const char *database
, const char *table
, va_list ap
)
105 const char *newparam
, *newval
;
111 struct ast_variable
*var
=NULL
, *prev
=NULL
;
113 SQLSMALLINT colcount
=0;
114 SQLSMALLINT datatype
;
115 SQLSMALLINT decimaldigits
;
116 SQLSMALLINT nullable
;
119 struct custom_prepare_struct cps
= { .sql
= sql
};
127 obj
= ast_odbc_request_obj(database
, 0);
130 ast_log(LOG_ERROR
, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database
);
134 newparam
= va_arg(aq
, const char *);
137 newval
= va_arg(aq
, const char *);
138 op
= !strchr(newparam
, ' ') ? " =" : "";
139 snprintf(sql
, sizeof(sql
), "SELECT * FROM %s WHERE %s%s ?", table
, newparam
, op
);
140 while((newparam
= va_arg(aq
, const char *))) {
141 op
= !strchr(newparam
, ' ') ? " =" : "";
142 snprintf(sql
+ strlen(sql
), sizeof(sql
) - strlen(sql
), " AND %s%s ?%s", newparam
, op
,
143 strcasestr(newparam
, "LIKE") ? " ESCAPE '\\'" : "");
144 newval
= va_arg(aq
, const char *);
148 stmt
= ast_odbc_prepare_and_execute(obj
, custom_prepare
, &cps
);
151 ast_odbc_release_obj(obj
);
155 res
= SQLNumResultCols(stmt
, &colcount
);
156 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
157 ast_log(LOG_WARNING
, "SQL Column Count error!\n[%s]\n\n", sql
);
158 SQLFreeHandle (SQL_HANDLE_STMT
, stmt
);
159 ast_odbc_release_obj(obj
);
163 res
= SQLFetch(stmt
);
164 if (res
== SQL_NO_DATA
) {
165 SQLFreeHandle (SQL_HANDLE_STMT
, stmt
);
166 ast_odbc_release_obj(obj
);
169 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
170 ast_log(LOG_WARNING
, "SQL Fetch error!\n[%s]\n\n", sql
);
171 SQLFreeHandle (SQL_HANDLE_STMT
, stmt
);
172 ast_odbc_release_obj(obj
);
175 for (x
= 0; x
< colcount
; x
++) {
177 collen
= sizeof(coltitle
);
178 res
= SQLDescribeCol(stmt
, x
+ 1, (unsigned char *)coltitle
, sizeof(coltitle
), &collen
,
179 &datatype
, &colsize
, &decimaldigits
, &nullable
);
180 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
181 ast_log(LOG_WARNING
, "SQL Describe Column error!\n[%s]\n\n", sql
);
183 ast_variables_destroy(var
);
184 ast_odbc_release_obj(obj
);
189 res
= SQLGetData(stmt
, x
+ 1, SQL_CHAR
, rowdata
, sizeof(rowdata
), &indicator
);
190 if (indicator
== SQL_NULL_DATA
)
193 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
194 ast_log(LOG_WARNING
, "SQL Get Data error!\n[%s]\n\n", sql
);
196 ast_variables_destroy(var
);
197 ast_odbc_release_obj(obj
);
202 chunk
= strsep(&stringp
, ";");
203 if (!ast_strlen_zero(ast_strip(chunk
))) {
205 prev
->next
= ast_variable_new(coltitle
, chunk
);
209 prev
= var
= ast_variable_new(coltitle
, chunk
);
215 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
216 ast_odbc_release_obj(obj
);
220 static struct ast_config
*realtime_multi_odbc(const char *database
, const char *table
, va_list ap
)
222 struct odbc_obj
*obj
;
227 const char *initfield
=NULL
;
229 const char *newparam
, *newval
;
235 struct ast_variable
*var
=NULL
;
236 struct ast_config
*cfg
=NULL
;
237 struct ast_category
*cat
=NULL
;
238 struct ast_realloca ra
;
240 SQLSMALLINT colcount
=0;
241 SQLSMALLINT datatype
;
242 SQLSMALLINT decimaldigits
;
243 SQLSMALLINT nullable
;
245 struct custom_prepare_struct cps
= { .sql
= sql
};
253 memset(&ra
, 0, sizeof(ra
));
255 obj
= ast_odbc_request_obj(database
, 0);
259 newparam
= va_arg(aq
, const char *);
261 ast_odbc_release_obj(obj
);
264 initfield
= ast_strdupa(newparam
);
265 if ((op
= strchr(initfield
, ' ')))
267 newval
= va_arg(aq
, const char *);
268 op
= !strchr(newparam
, ' ') ? " =" : "";
269 snprintf(sql
, sizeof(sql
), "SELECT * FROM %s WHERE %s%s ?", table
, newparam
, op
);
270 while((newparam
= va_arg(aq
, const char *))) {
271 op
= !strchr(newparam
, ' ') ? " =" : "";
272 snprintf(sql
+ strlen(sql
), sizeof(sql
) - strlen(sql
), " AND %s%s ?%s", newparam
, op
,
273 strcasestr(newparam
, "LIKE") ? " ESCAPE '\\'" : "");
274 newval
= va_arg(aq
, const char *);
277 snprintf(sql
+ strlen(sql
), sizeof(sql
) - strlen(sql
), " ORDER BY %s", initfield
);
280 stmt
= ast_odbc_prepare_and_execute(obj
, custom_prepare
, &cps
);
283 ast_odbc_release_obj(obj
);
287 res
= SQLNumResultCols(stmt
, &colcount
);
288 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
289 ast_log(LOG_WARNING
, "SQL Column Count error!\n[%s]\n\n", sql
);
290 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
291 ast_odbc_release_obj(obj
);
295 cfg
= ast_config_new();
297 ast_log(LOG_WARNING
, "Out of memory!\n");
298 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
299 ast_odbc_release_obj(obj
);
303 while ((res
=SQLFetch(stmt
)) != SQL_NO_DATA
) {
305 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
306 ast_log(LOG_WARNING
, "SQL Fetch error!\n[%s]\n\n", sql
);
309 cat
= ast_category_new("");
311 ast_log(LOG_WARNING
, "Out of memory!\n");
314 for (x
=0;x
<colcount
;x
++) {
316 collen
= sizeof(coltitle
);
317 res
= SQLDescribeCol(stmt
, x
+ 1, (unsigned char *)coltitle
, sizeof(coltitle
), &collen
,
318 &datatype
, &colsize
, &decimaldigits
, &nullable
);
319 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
320 ast_log(LOG_WARNING
, "SQL Describe Column error!\n[%s]\n\n", sql
);
321 ast_category_destroy(cat
);
326 res
= SQLGetData(stmt
, x
+ 1, SQL_CHAR
, rowdata
, sizeof(rowdata
), &indicator
);
327 if (indicator
== SQL_NULL_DATA
)
330 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
331 ast_log(LOG_WARNING
, "SQL Get Data error!\n[%s]\n\n", sql
);
332 ast_category_destroy(cat
);
337 chunk
= strsep(&stringp
, ";");
338 if (!ast_strlen_zero(ast_strip(chunk
))) {
339 if (initfield
&& !strcmp(initfield
, coltitle
))
340 ast_category_rename(cat
, chunk
);
341 var
= ast_variable_new(coltitle
, chunk
);
342 ast_variable_append(cat
, var
);
346 ast_category_append(cfg
, cat
);
349 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
350 ast_odbc_release_obj(obj
);
354 static int update_odbc(const char *database
, const char *table
, const char *keyfield
, const char *lookup
, va_list ap
)
356 struct odbc_obj
*obj
;
360 const char *newparam
, *newval
;
363 struct custom_prepare_struct cps
= { .sql
= sql
, .extra
= lookup
};
371 obj
= ast_odbc_request_obj(database
, 0);
375 newparam
= va_arg(aq
, const char *);
377 ast_odbc_release_obj(obj
);
380 newval
= va_arg(aq
, const char *);
381 snprintf(sql
, sizeof(sql
), "UPDATE %s SET %s=?", table
, newparam
);
382 while((newparam
= va_arg(aq
, const char *))) {
383 snprintf(sql
+ strlen(sql
), sizeof(sql
) - strlen(sql
), ", %s=?", newparam
);
384 newval
= va_arg(aq
, const char *);
387 snprintf(sql
+ strlen(sql
), sizeof(sql
) - strlen(sql
), " WHERE %s=?", keyfield
);
389 stmt
= ast_odbc_prepare_and_execute(obj
, custom_prepare
, &cps
);
392 ast_odbc_release_obj(obj
);
396 res
= SQLRowCount(stmt
, &rowcount
);
397 SQLFreeHandle (SQL_HANDLE_STMT
, stmt
);
398 ast_odbc_release_obj(obj
);
400 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
401 ast_log(LOG_WARNING
, "SQL Row Count error!\n[%s]\n\n", sql
);
406 return (int)rowcount
;
411 struct config_odbc_obj
{
413 unsigned long cat_metric
;
416 char var_val
[1024]; /* changed from 128 to 1024 via bug 8251 */
420 static SQLHSTMT
config_odbc_prepare(struct odbc_obj
*obj
, void *data
)
422 struct config_odbc_obj
*q
= data
;
426 res
= SQLAllocHandle(SQL_HANDLE_STMT
, obj
->con
, &sth
);
427 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
428 if (option_verbose
> 3)
429 ast_verbose( VERBOSE_PREFIX_4
"Failure in AllocStatement %d\n", res
);
433 res
= SQLPrepare(sth
, (unsigned char *)q
->sql
, SQL_NTS
);
434 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
435 if (option_verbose
> 3)
436 ast_verbose( VERBOSE_PREFIX_4
"Error in PREPARE %d\n", res
);
437 SQLFreeHandle(SQL_HANDLE_STMT
, sth
);
441 SQLBindCol(sth
, 1, SQL_C_ULONG
, &q
->cat_metric
, sizeof(q
->cat_metric
), &q
->err
);
442 SQLBindCol(sth
, 2, SQL_C_CHAR
, q
->category
, sizeof(q
->category
), &q
->err
);
443 SQLBindCol(sth
, 3, SQL_C_CHAR
, q
->var_name
, sizeof(q
->var_name
), &q
->err
);
444 SQLBindCol(sth
, 4, SQL_C_CHAR
, q
->var_val
, sizeof(q
->var_val
), &q
->err
);
449 static struct ast_config
*config_odbc(const char *database
, const char *table
, const char *file
, struct ast_config
*cfg
, int withcomments
)
451 struct ast_variable
*new_v
;
452 struct ast_category
*cur_cat
;
454 struct odbc_obj
*obj
;
455 char sqlbuf
[1024] = "";
457 size_t sqlleft
= sizeof(sqlbuf
);
458 unsigned int last_cat_metric
= 0;
459 SQLSMALLINT rowcount
= 0;
462 struct config_odbc_obj q
;
464 memset(&q
, 0, sizeof(q
));
466 if (!file
|| !strcmp (file
, "res_config_odbc.conf"))
467 return NULL
; /* cant configure myself with myself ! */
469 obj
= ast_odbc_request_obj(database
, 0);
473 ast_build_string(&sql
, &sqlleft
, "SELECT cat_metric, category, var_name, var_val FROM %s ", table
);
474 ast_build_string(&sql
, &sqlleft
, "WHERE filename='%s' AND commented=0 ", file
);
475 ast_build_string(&sql
, &sqlleft
, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
478 stmt
= ast_odbc_prepare_and_execute(obj
, config_odbc_prepare
, &q
);
481 ast_log(LOG_WARNING
, "SQL select error!\n[%s]\n\n", sql
);
482 ast_odbc_release_obj(obj
);
486 res
= SQLNumResultCols(stmt
, &rowcount
);
488 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
489 ast_log(LOG_WARNING
, "SQL NumResultCols error!\n[%s]\n\n", sql
);
490 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
491 ast_odbc_release_obj(obj
);
496 ast_log(LOG_NOTICE
, "found nothing\n");
497 ast_odbc_release_obj(obj
);
501 cur_cat
= ast_config_get_current_category(cfg
);
503 while ((res
= SQLFetch(stmt
)) != SQL_NO_DATA
) {
504 if (!strcmp (q
.var_name
, "#include")) {
505 if (!ast_config_internal_load(q
.var_val
, cfg
, 0)) {
506 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
507 ast_odbc_release_obj(obj
);
512 if (strcmp(last
, q
.category
) || last_cat_metric
!= q
.cat_metric
) {
513 cur_cat
= ast_category_new(q
.category
);
515 ast_log(LOG_WARNING
, "Out of memory!\n");
518 strcpy(last
, q
.category
);
519 last_cat_metric
= q
.cat_metric
;
520 ast_category_append(cfg
, cur_cat
);
523 new_v
= ast_variable_new(q
.var_name
, q
.var_val
);
524 ast_variable_append(cur_cat
, new_v
);
527 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
528 ast_odbc_release_obj(obj
);
532 static struct ast_config_engine odbc_engine
= {
534 .load_func
= config_odbc
,
535 .realtime_func
= realtime_odbc
,
536 .realtime_multi_func
= realtime_multi_odbc
,
537 .update_func
= update_odbc
540 static int unload_module (void)
542 ast_module_user_hangup_all();
543 ast_config_engine_deregister(&odbc_engine
);
545 ast_verbose("res_config_odbc unloaded.\n");
549 static int load_module (void)
551 ast_config_engine_register(&odbc_engine
);
553 ast_verbose("res_config_odbc loaded.\n");
557 AST_MODULE_INFO(ASTERISK_GPL_KEY
, AST_MODFLAG_GLOBAL_SYMBOLS
, "ODBC Configuration",
559 .unload
= unload_module
,