00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034
00035 #include "asterisk.h"
00036
00037 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 298482 $")
00038
00039 #include "asterisk/file.h"
00040 #include "asterisk/channel.h"
00041 #include "asterisk/pbx.h"
00042 #include "asterisk/config.h"
00043 #include "asterisk/module.h"
00044 #include "asterisk/lock.h"
00045 #include "asterisk/res_odbc.h"
00046 #include "asterisk/utils.h"
00047 #include "asterisk/stringfields.h"
00048
00049 AST_THREADSTORAGE(sql_buf);
00050
00051 struct custom_prepare_struct {
00052 const char *sql;
00053 const char *extra;
00054 AST_DECLARE_STRING_FIELDS(
00055 AST_STRING_FIELD(encoding)[256];
00056 );
00057 va_list ap;
00058 unsigned long long skip;
00059 };
00060
00061 static void decode_chunk(char *chunk)
00062 {
00063 for (; *chunk; chunk++) {
00064 if (*chunk == '^' && strchr("0123456789ABCDEF", chunk[1]) && strchr("0123456789ABCDEF", chunk[2])) {
00065 sscanf(chunk + 1, "%02hhX", chunk);
00066 memmove(chunk + 1, chunk + 3, strlen(chunk + 3) + 1);
00067 }
00068 }
00069 }
00070
00071 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
00072 {
00073 int res, x = 1, count = 0;
00074 struct custom_prepare_struct *cps = data;
00075 const char *newparam, *newval;
00076 char encodebuf[1024];
00077 SQLHSTMT stmt;
00078 va_list ap;
00079
00080 va_copy(ap, cps->ap);
00081
00082 res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00083 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00084 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00085 return NULL;
00086 }
00087
00088 ast_debug(1, "Skip: %lld; SQL: %s\n", cps->skip, cps->sql);
00089
00090 res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
00091 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00092 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
00093 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00094 return NULL;
00095 }
00096
00097 while ((newparam = va_arg(ap, const char *))) {
00098 newval = va_arg(ap, const char *);
00099 if ((1LL << count++) & cps->skip) {
00100 ast_debug(1, "Skipping field '%s'='%s' (%llo/%llo)\n", newparam, newval, 1LL << (count - 1), cps->skip);
00101 continue;
00102 }
00103 ast_debug(1, "Parameter %d ('%s') = '%s'\n", x, newparam, newval);
00104 if (strchr(newval, ';') || strchr(newval, '^')) {
00105 char *eptr = encodebuf;
00106 const char *vptr = newval;
00107 for (; *vptr && eptr < encodebuf + sizeof(encodebuf); vptr++) {
00108 if (strchr("^;", *vptr)) {
00109
00110 snprintf(eptr, encodebuf + sizeof(encodebuf) - eptr, "^%02hhX", *vptr);
00111 eptr += 3;
00112 } else {
00113 *eptr++ = *vptr;
00114 }
00115 }
00116 if (eptr < encodebuf + sizeof(encodebuf)) {
00117 *eptr = '\0';
00118 } else {
00119 encodebuf[sizeof(encodebuf) - 1] = '\0';
00120 }
00121 ast_string_field_set(cps, encoding[x], encodebuf);
00122 newval = cps->encoding[x];
00123 }
00124 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00125 }
00126 va_end(ap);
00127
00128 if (!ast_strlen_zero(cps->extra))
00129 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
00130 return stmt;
00131 }
00132
00133
00134
00135
00136
00137
00138
00139
00140
00141
00142
00143
00144
00145
00146 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
00147 {
00148 struct odbc_obj *obj;
00149 SQLHSTMT stmt;
00150 char sql[1024];
00151 char coltitle[256];
00152 char rowdata[2048];
00153 char *op;
00154 const char *newparam, *newval;
00155 char *stringp;
00156 char *chunk;
00157 SQLSMALLINT collen;
00158 int res;
00159 int x;
00160 struct ast_variable *var=NULL, *prev=NULL;
00161 SQLULEN colsize;
00162 SQLSMALLINT colcount=0;
00163 SQLSMALLINT datatype;
00164 SQLSMALLINT decimaldigits;
00165 SQLSMALLINT nullable;
00166 SQLLEN indicator;
00167 va_list aq;
00168 struct custom_prepare_struct cps = { .sql = sql };
00169 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00170
00171 if (ast_string_field_init(&cps, 256)) {
00172 return NULL;
00173 }
00174 va_copy(cps.ap, ap);
00175 va_copy(aq, ap);
00176
00177 if (!table) {
00178 ast_string_field_free_memory(&cps);
00179 return NULL;
00180 }
00181
00182 obj = ast_odbc_request_obj2(database, connected_flag);
00183
00184 if (!obj) {
00185 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
00186 ast_string_field_free_memory(&cps);
00187 return NULL;
00188 }
00189
00190 newparam = va_arg(aq, const char *);
00191 if (!newparam) {
00192 ast_odbc_release_obj(obj);
00193 ast_string_field_free_memory(&cps);
00194 return NULL;
00195 }
00196 newval = va_arg(aq, const char *);
00197 op = !strchr(newparam, ' ') ? " =" : "";
00198 snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00199 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00200 while((newparam = va_arg(aq, const char *))) {
00201 op = !strchr(newparam, ' ') ? " =" : "";
00202 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00203 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00204 newval = va_arg(aq, const char *);
00205 }
00206 va_end(aq);
00207
00208 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00209
00210 if (!stmt) {
00211 ast_odbc_release_obj(obj);
00212 ast_string_field_free_memory(&cps);
00213 return NULL;
00214 }
00215
00216 res = SQLNumResultCols(stmt, &colcount);
00217 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00218 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00219 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00220 ast_odbc_release_obj(obj);
00221 ast_string_field_free_memory(&cps);
00222 return NULL;
00223 }
00224
00225 res = SQLFetch(stmt);
00226 if (res == SQL_NO_DATA) {
00227 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00228 ast_odbc_release_obj(obj);
00229 ast_string_field_free_memory(&cps);
00230 return NULL;
00231 }
00232 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00233 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00234 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00235 ast_odbc_release_obj(obj);
00236 ast_string_field_free_memory(&cps);
00237 return NULL;
00238 }
00239 for (x = 0; x < colcount; x++) {
00240 rowdata[0] = '\0';
00241 collen = sizeof(coltitle);
00242 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
00243 &datatype, &colsize, &decimaldigits, &nullable);
00244 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00245 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00246 if (var)
00247 ast_variables_destroy(var);
00248 ast_odbc_release_obj(obj);
00249 ast_string_field_free_memory(&cps);
00250 return NULL;
00251 }
00252
00253 indicator = 0;
00254 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00255 if (indicator == SQL_NULL_DATA)
00256 rowdata[0] = '\0';
00257 else if (ast_strlen_zero(rowdata)) {
00258
00259
00260 ast_copy_string(rowdata, " ", sizeof(rowdata));
00261 }
00262
00263 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00264 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00265 if (var)
00266 ast_variables_destroy(var);
00267 ast_odbc_release_obj(obj);
00268 return NULL;
00269 }
00270 stringp = rowdata;
00271 while (stringp) {
00272 chunk = strsep(&stringp, ";");
00273 if (!ast_strlen_zero(ast_strip(chunk))) {
00274 if (strchr(chunk, '^')) {
00275 decode_chunk(chunk);
00276 }
00277 if (prev) {
00278 prev->next = ast_variable_new(coltitle, chunk, "");
00279 if (prev->next) {
00280 prev = prev->next;
00281 }
00282 } else {
00283 prev = var = ast_variable_new(coltitle, chunk, "");
00284 }
00285 }
00286 }
00287 }
00288
00289
00290 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00291 ast_odbc_release_obj(obj);
00292 ast_string_field_free_memory(&cps);
00293 return var;
00294 }
00295
00296
00297
00298
00299
00300
00301
00302
00303
00304
00305
00306
00307
00308
00309
00310 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
00311 {
00312 struct odbc_obj *obj;
00313 SQLHSTMT stmt;
00314 char sql[1024];
00315 char coltitle[256];
00316 char rowdata[2048];
00317 const char *initfield=NULL;
00318 char *op;
00319 const char *newparam, *newval;
00320 char *stringp;
00321 char *chunk;
00322 SQLSMALLINT collen;
00323 int res;
00324 int x;
00325 struct ast_variable *var=NULL;
00326 struct ast_config *cfg=NULL;
00327 struct ast_category *cat=NULL;
00328 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00329 SQLULEN colsize;
00330 SQLSMALLINT colcount=0;
00331 SQLSMALLINT datatype;
00332 SQLSMALLINT decimaldigits;
00333 SQLSMALLINT nullable;
00334 SQLLEN indicator;
00335 struct custom_prepare_struct cps = { .sql = sql };
00336 va_list aq;
00337
00338 if (!table || ast_string_field_init(&cps, 256)) {
00339 return NULL;
00340 }
00341 va_copy(cps.ap, ap);
00342 va_copy(aq, ap);
00343
00344
00345 obj = ast_odbc_request_obj2(database, connected_flag);
00346 if (!obj) {
00347 ast_string_field_free_memory(&cps);
00348 return NULL;
00349 }
00350
00351 newparam = va_arg(aq, const char *);
00352 if (!newparam) {
00353 ast_odbc_release_obj(obj);
00354 ast_string_field_free_memory(&cps);
00355 return NULL;
00356 }
00357 initfield = ast_strdupa(newparam);
00358 if ((op = strchr(initfield, ' ')))
00359 *op = '\0';
00360 newval = va_arg(aq, const char *);
00361 op = !strchr(newparam, ' ') ? " =" : "";
00362 snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00363 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00364 while((newparam = va_arg(aq, const char *))) {
00365 op = !strchr(newparam, ' ') ? " =" : "";
00366 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00367 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00368 newval = va_arg(aq, const char *);
00369 }
00370 if (initfield)
00371 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00372 va_end(aq);
00373
00374 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00375
00376 if (!stmt) {
00377 ast_odbc_release_obj(obj);
00378 ast_string_field_free_memory(&cps);
00379 return NULL;
00380 }
00381
00382 res = SQLNumResultCols(stmt, &colcount);
00383 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00384 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00385 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00386 ast_odbc_release_obj(obj);
00387 ast_string_field_free_memory(&cps);
00388 return NULL;
00389 }
00390
00391 cfg = ast_config_new();
00392 if (!cfg) {
00393 ast_log(LOG_WARNING, "Out of memory!\n");
00394 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00395 ast_odbc_release_obj(obj);
00396 ast_string_field_free_memory(&cps);
00397 return NULL;
00398 }
00399
00400 while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
00401 var = NULL;
00402 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00403 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00404 continue;
00405 }
00406 cat = ast_category_new("","",99999);
00407 if (!cat) {
00408 ast_log(LOG_WARNING, "Out of memory!\n");
00409 continue;
00410 }
00411 for (x=0;x<colcount;x++) {
00412 rowdata[0] = '\0';
00413 collen = sizeof(coltitle);
00414 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
00415 &datatype, &colsize, &decimaldigits, &nullable);
00416 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00417 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00418 ast_category_destroy(cat);
00419 continue;
00420 }
00421
00422 indicator = 0;
00423 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00424 if (indicator == SQL_NULL_DATA)
00425 continue;
00426
00427 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00428 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00429 ast_category_destroy(cat);
00430 continue;
00431 }
00432 stringp = rowdata;
00433 while (stringp) {
00434 chunk = strsep(&stringp, ";");
00435 if (!ast_strlen_zero(ast_strip(chunk))) {
00436 if (strchr(chunk, '^')) {
00437 decode_chunk(chunk);
00438 }
00439 if (initfield && !strcmp(initfield, coltitle)) {
00440 ast_category_rename(cat, chunk);
00441 }
00442 var = ast_variable_new(coltitle, chunk, "");
00443 ast_variable_append(cat, var);
00444 }
00445 }
00446 }
00447 ast_category_append(cfg, cat);
00448 }
00449
00450 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00451 ast_odbc_release_obj(obj);
00452 ast_string_field_free_memory(&cps);
00453 return cfg;
00454 }
00455
00456
00457
00458
00459
00460
00461
00462
00463
00464
00465
00466
00467
00468
00469
00470
00471 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00472 {
00473 struct odbc_obj *obj;
00474 SQLHSTMT stmt;
00475 char sql[256];
00476 SQLLEN rowcount=0;
00477 const char *newparam, *newval;
00478 int res, count = 1;
00479 va_list aq;
00480 struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00481 struct odbc_cache_tables *tableptr;
00482 struct odbc_cache_columns *column;
00483 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00484
00485 if (!table) {
00486 return -1;
00487 }
00488
00489 va_copy(cps.ap, ap);
00490 va_copy(aq, ap);
00491
00492 if (ast_string_field_init(&cps, 256)) {
00493 return -1;
00494 }
00495
00496 tableptr = ast_odbc_find_table(database, table);
00497 if (!(obj = ast_odbc_request_obj2(database, connected_flag))) {
00498 ast_odbc_release_table(tableptr);
00499 ast_string_field_free_memory(&cps);
00500 return -1;
00501 }
00502
00503 newparam = va_arg(aq, const char *);
00504 if (!newparam) {
00505 ast_odbc_release_obj(obj);
00506 ast_odbc_release_table(tableptr);
00507 ast_string_field_free_memory(&cps);
00508 return -1;
00509 }
00510 newval = va_arg(aq, const char *);
00511
00512 if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
00513 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'. Update will fail\n", newparam, table, database);
00514 }
00515
00516 snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
00517 while((newparam = va_arg(aq, const char *))) {
00518 newval = va_arg(aq, const char *);
00519 if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
00520 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
00521 } else {
00522 cps.skip |= (1LL << count);
00523 }
00524 count++;
00525 }
00526 va_end(aq);
00527 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
00528 ast_odbc_release_table(tableptr);
00529
00530 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00531
00532 if (!stmt) {
00533 ast_odbc_release_obj(obj);
00534 ast_string_field_free_memory(&cps);
00535 return -1;
00536 }
00537
00538 res = SQLRowCount(stmt, &rowcount);
00539 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00540 ast_odbc_release_obj(obj);
00541 ast_string_field_free_memory(&cps);
00542
00543 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00544 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00545 return -1;
00546 }
00547
00548 if (rowcount >= 0) {
00549 return (int) rowcount;
00550 }
00551
00552 return -1;
00553 }
00554
00555 struct update2_prepare_struct {
00556 const char *database;
00557 const char *table;
00558 va_list ap;
00559 };
00560
00561 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
00562 {
00563 int res, x = 1, first = 1;
00564 struct update2_prepare_struct *ups = data;
00565 const char *newparam, *newval;
00566 struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
00567 SQLHSTMT stmt;
00568 va_list ap;
00569 struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
00570 struct odbc_cache_columns *column;
00571
00572 if (!sql) {
00573 if (tableptr) {
00574 ast_odbc_release_table(tableptr);
00575 }
00576 return NULL;
00577 }
00578
00579 if (!tableptr) {
00580 ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'. Update will fail!\n", ups->table, ups->database);
00581 return NULL;
00582 }
00583
00584 res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00585 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00586 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00587 ast_odbc_release_table(tableptr);
00588 return NULL;
00589 }
00590
00591 ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
00592
00593
00594 va_copy(ap, ups->ap);
00595
00596 while ((newparam = va_arg(ap, const char *))) {
00597 newval = va_arg(ap, const char *);
00598 }
00599
00600 while ((newparam = va_arg(ap, const char *))) {
00601 newval = va_arg(ap, const char *);
00602 if ((column = ast_odbc_find_column(tableptr, newparam))) {
00603 ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", newparam);
00604 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00605 first = 0;
00606 } else {
00607 ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", newparam, ups->table, ups->database);
00608 }
00609 }
00610 va_end(ap);
00611
00612
00613 va_copy(ap, ups->ap);
00614 ast_str_append(&sql, 0, "WHERE");
00615 first = 1;
00616
00617 while ((newparam = va_arg(ap, const char *))) {
00618 newval = va_arg(ap, const char *);
00619 if (!(column = ast_odbc_find_column(tableptr, newparam))) {
00620 ast_log(LOG_ERROR, "One or more of the criteria columns '%s' on '%s@%s' for this update does not exist!\n", newparam, ups->table, ups->database);
00621 ast_odbc_release_table(tableptr);
00622 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00623 return NULL;
00624 }
00625 ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", newparam);
00626 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00627 first = 0;
00628 }
00629 va_end(ap);
00630
00631
00632 ast_odbc_release_table(tableptr);
00633
00634 res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
00635 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00636 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
00637 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00638 return NULL;
00639 }
00640
00641 return stmt;
00642 }
00643
00644
00645
00646
00647
00648
00649
00650
00651
00652
00653
00654
00655
00656
00657
00658 static int update2_odbc(const char *database, const char *table, va_list ap)
00659 {
00660 struct odbc_obj *obj;
00661 SQLHSTMT stmt;
00662 struct update2_prepare_struct ups = { .database = database, .table = table, };
00663 struct ast_str *sql;
00664 int res;
00665 SQLLEN rowcount = 0;
00666
00667 va_copy(ups.ap, ap);
00668
00669 if (!(obj = ast_odbc_request_obj(database, 0))) {
00670 return -1;
00671 }
00672
00673 if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
00674 ast_odbc_release_obj(obj);
00675 return -1;
00676 }
00677
00678 res = SQLRowCount(stmt, &rowcount);
00679 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00680 ast_odbc_release_obj(obj);
00681
00682 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00683
00684 sql = ast_str_thread_get(&sql_buf, 16);
00685 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
00686 return -1;
00687 }
00688
00689 if (rowcount >= 0) {
00690 return (int)rowcount;
00691 }
00692
00693 return -1;
00694 }
00695
00696
00697
00698
00699
00700
00701
00702
00703
00704
00705
00706
00707
00708
00709 static int store_odbc(const char *database, const char *table, va_list ap)
00710 {
00711 struct odbc_obj *obj;
00712 SQLHSTMT stmt;
00713 char sql[256];
00714 char keys[256];
00715 char vals[256];
00716 SQLLEN rowcount=0;
00717 const char *newparam, *newval;
00718 int res;
00719 va_list aq;
00720 struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
00721 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00722
00723 va_copy(cps.ap, ap);
00724 va_copy(aq, ap);
00725
00726 if (!table)
00727 return -1;
00728
00729 obj = ast_odbc_request_obj2(database, connected_flag);
00730 if (!obj)
00731 return -1;
00732
00733 newparam = va_arg(aq, const char *);
00734 if (!newparam) {
00735 ast_odbc_release_obj(obj);
00736 return -1;
00737 }
00738 newval = va_arg(aq, const char *);
00739 snprintf(keys, sizeof(keys), "%s", newparam);
00740 ast_copy_string(vals, "?", sizeof(vals));
00741 while ((newparam = va_arg(aq, const char *))) {
00742 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
00743 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
00744 newval = va_arg(aq, const char *);
00745 }
00746 va_end(aq);
00747 snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
00748
00749 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00750
00751 if (!stmt) {
00752 ast_odbc_release_obj(obj);
00753 return -1;
00754 }
00755
00756 res = SQLRowCount(stmt, &rowcount);
00757 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00758 ast_odbc_release_obj(obj);
00759
00760 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00761 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00762 return -1;
00763 }
00764
00765 if (rowcount >= 0)
00766 return (int)rowcount;
00767
00768 return -1;
00769 }
00770
00771
00772
00773
00774
00775
00776
00777
00778
00779
00780
00781
00782
00783
00784
00785
00786 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00787 {
00788 struct odbc_obj *obj;
00789 SQLHSTMT stmt;
00790 char sql[256];
00791 SQLLEN rowcount=0;
00792 const char *newparam, *newval;
00793 int res;
00794 va_list aq;
00795 struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00796 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00797
00798 va_copy(cps.ap, ap);
00799 va_copy(aq, ap);
00800
00801 if (!table)
00802 return -1;
00803
00804 obj = ast_odbc_request_obj2(database, connected_flag);
00805 if (!obj)
00806 return -1;
00807
00808 snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
00809 while((newparam = va_arg(aq, const char *))) {
00810 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
00811 newval = va_arg(aq, const char *);
00812 }
00813 va_end(aq);
00814 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
00815
00816 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00817
00818 if (!stmt) {
00819 ast_odbc_release_obj(obj);
00820 return -1;
00821 }
00822
00823 res = SQLRowCount(stmt, &rowcount);
00824 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00825 ast_odbc_release_obj(obj);
00826
00827 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00828 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00829 return -1;
00830 }
00831
00832 if (rowcount >= 0)
00833 return (int)rowcount;
00834
00835 return -1;
00836 }
00837
00838
00839 struct config_odbc_obj {
00840 char *sql;
00841 unsigned long cat_metric;
00842 char category[128];
00843 char var_name[128];
00844 char var_val[1024];
00845 SQLLEN err;
00846 };
00847
00848 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
00849 {
00850 struct config_odbc_obj *q = data;
00851 SQLHSTMT sth;
00852 int res;
00853
00854 res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
00855 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00856 ast_verb(4, "Failure in AllocStatement %d\n", res);
00857 return NULL;
00858 }
00859
00860 res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
00861 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00862 ast_verb(4, "Error in PREPARE %d\n", res);
00863 SQLFreeHandle(SQL_HANDLE_STMT, sth);
00864 return NULL;
00865 }
00866
00867 SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
00868 SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
00869 SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
00870 SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
00871
00872 return sth;
00873 }
00874
00875 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)
00876 {
00877 struct ast_variable *new_v;
00878 struct ast_category *cur_cat;
00879 int res = 0;
00880 struct odbc_obj *obj;
00881 char sqlbuf[1024] = "";
00882 char *sql = sqlbuf;
00883 size_t sqlleft = sizeof(sqlbuf);
00884 unsigned int last_cat_metric = 0;
00885 SQLSMALLINT rowcount = 0;
00886 SQLHSTMT stmt;
00887 char last[128] = "";
00888 struct config_odbc_obj q;
00889 struct ast_flags loader_flags = { 0 };
00890 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00891
00892 memset(&q, 0, sizeof(q));
00893
00894 if (!file || !strcmp (file, "res_config_odbc.conf"))
00895 return NULL;
00896
00897 obj = ast_odbc_request_obj2(database, connected_flag);
00898 if (!obj)
00899 return NULL;
00900
00901 ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
00902 ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
00903 ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00904 q.sql = sqlbuf;
00905
00906 stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
00907
00908 if (!stmt) {
00909 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
00910 ast_odbc_release_obj(obj);
00911 return NULL;
00912 }
00913
00914 res = SQLNumResultCols(stmt, &rowcount);
00915
00916 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00917 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
00918 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00919 ast_odbc_release_obj(obj);
00920 return NULL;
00921 }
00922
00923 if (!rowcount) {
00924 ast_log(LOG_NOTICE, "found nothing\n");
00925 ast_odbc_release_obj(obj);
00926 return cfg;
00927 }
00928
00929 cur_cat = ast_config_get_current_category(cfg);
00930
00931 while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
00932 if (!strcmp (q.var_name, "#include")) {
00933 if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
00934 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00935 ast_odbc_release_obj(obj);
00936 return NULL;
00937 }
00938 continue;
00939 }
00940 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
00941 cur_cat = ast_category_new(q.category, "", 99999);
00942 if (!cur_cat) {
00943 ast_log(LOG_WARNING, "Out of memory!\n");
00944 break;
00945 }
00946 strcpy(last, q.category);
00947 last_cat_metric = q.cat_metric;
00948 ast_category_append(cfg, cur_cat);
00949 }
00950
00951 new_v = ast_variable_new(q.var_name, q.var_val, "");
00952 ast_variable_append(cur_cat, new_v);
00953 }
00954
00955 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00956 ast_odbc_release_obj(obj);
00957 return cfg;
00958 }
00959
00960 #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)
00961 #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)
00962
00963 static int require_odbc(const char *database, const char *table, va_list ap)
00964 {
00965 struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
00966 struct odbc_cache_columns *col;
00967 char *elm;
00968 int type, size;
00969
00970 if (!tableptr) {
00971 return -1;
00972 }
00973
00974 while ((elm = va_arg(ap, char *))) {
00975 type = va_arg(ap, require_type);
00976 size = va_arg(ap, int);
00977
00978 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
00979 if (strcmp(col->name, elm) == 0) {
00980
00981 switch (col->type) {
00982 case SQL_CHAR:
00983 case SQL_VARCHAR:
00984 case SQL_LONGVARCHAR:
00985 #ifdef HAVE_ODBC_WCHAR
00986 case SQL_WCHAR:
00987 case SQL_WVARCHAR:
00988 case SQL_WLONGVARCHAR:
00989 #endif
00990 case SQL_BINARY:
00991 case SQL_VARBINARY:
00992 case SQL_LONGVARBINARY:
00993 case SQL_GUID:
00994 #define CHECK_SIZE(n) \
00995 if (col->size < n) { \
00996 warn_length(col, n); \
00997 } \
00998 break;
00999 switch (type) {
01000 case RQ_UINTEGER1: CHECK_SIZE(3)
01001 case RQ_INTEGER1: CHECK_SIZE(4)
01002 case RQ_UINTEGER2: CHECK_SIZE(5)
01003 case RQ_INTEGER2: CHECK_SIZE(6)
01004 case RQ_UINTEGER3:
01005 case RQ_INTEGER3: CHECK_SIZE(8)
01006 case RQ_DATE:
01007 case RQ_UINTEGER4: CHECK_SIZE(10)
01008 case RQ_INTEGER4: CHECK_SIZE(11)
01009 case RQ_DATETIME:
01010 case RQ_UINTEGER8: CHECK_SIZE(19)
01011 case RQ_INTEGER8: CHECK_SIZE(20)
01012 case RQ_FLOAT:
01013 case RQ_CHAR: CHECK_SIZE(size)
01014 }
01015 #undef CHECK_SIZE
01016 break;
01017 case SQL_TYPE_DATE:
01018 if (type != RQ_DATE) {
01019 warn_type(col, type);
01020 }
01021 break;
01022 case SQL_TYPE_TIMESTAMP:
01023 case SQL_TIMESTAMP:
01024 if (type != RQ_DATE && type != RQ_DATETIME) {
01025 warn_type(col, type);
01026 }
01027 break;
01028 case SQL_BIT:
01029 warn_length(col, size);
01030 break;
01031 #define WARN_TYPE_OR_LENGTH(n) \
01032 if (!ast_rq_is_int(type)) { \
01033 warn_type(col, type); \
01034 } else { \
01035 warn_length(col, n); \
01036 }
01037 case SQL_TINYINT:
01038 if (type != RQ_UINTEGER1) {
01039 WARN_TYPE_OR_LENGTH(size)
01040 }
01041 break;
01042 case SQL_C_STINYINT:
01043 if (type != RQ_INTEGER1) {
01044 WARN_TYPE_OR_LENGTH(size)
01045 }
01046 break;
01047 case SQL_C_USHORT:
01048 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
01049 WARN_TYPE_OR_LENGTH(size)
01050 }
01051 break;
01052 case SQL_SMALLINT:
01053 case SQL_C_SSHORT:
01054 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
01055 WARN_TYPE_OR_LENGTH(size)
01056 }
01057 break;
01058 case SQL_C_ULONG:
01059 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01060 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01061 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01062 type != RQ_INTEGER4) {
01063 WARN_TYPE_OR_LENGTH(size)
01064 }
01065 break;
01066 case SQL_INTEGER:
01067 case SQL_C_SLONG:
01068 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01069 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01070 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01071 type != RQ_INTEGER4) {
01072 WARN_TYPE_OR_LENGTH(size)
01073 }
01074 break;
01075 case SQL_C_UBIGINT:
01076 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01077 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01078 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01079 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01080 type != RQ_INTEGER8) {
01081 WARN_TYPE_OR_LENGTH(size)
01082 }
01083 break;
01084 case SQL_BIGINT:
01085 case SQL_C_SBIGINT:
01086 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01087 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01088 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01089 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01090 type != RQ_INTEGER8) {
01091 WARN_TYPE_OR_LENGTH(size)
01092 }
01093 break;
01094 #undef WARN_TYPE_OR_LENGTH
01095 case SQL_NUMERIC:
01096 case SQL_DECIMAL:
01097 case SQL_FLOAT:
01098 case SQL_REAL:
01099 case SQL_DOUBLE:
01100 if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
01101 warn_type(col, type);
01102 }
01103 break;
01104 default:
01105 ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
01106 }
01107 break;
01108 }
01109 }
01110 if (!col) {
01111 ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
01112 }
01113 }
01114 va_end(ap);
01115 AST_RWLIST_UNLOCK(&tableptr->columns);
01116 return 0;
01117 }
01118 #undef warn_length
01119 #undef warn_type
01120
01121 static struct ast_config_engine odbc_engine = {
01122 .name = "odbc",
01123 .load_func = config_odbc,
01124 .realtime_func = realtime_odbc,
01125 .realtime_multi_func = realtime_multi_odbc,
01126 .store_func = store_odbc,
01127 .destroy_func = destroy_odbc,
01128 .update_func = update_odbc,
01129 .update2_func = update2_odbc,
01130 .require_func = require_odbc,
01131 .unload_func = ast_odbc_clear_cache,
01132 };
01133
01134 static int unload_module (void)
01135 {
01136 ast_config_engine_deregister(&odbc_engine);
01137
01138 ast_verb(1, "res_config_odbc unloaded.\n");
01139 return 0;
01140 }
01141
01142 static int load_module (void)
01143 {
01144 ast_config_engine_register(&odbc_engine);
01145 ast_verb(1, "res_config_odbc loaded.\n");
01146 return 0;
01147 }
01148
01149 static int reload_module(void)
01150 {
01151 return 0;
01152 }
01153
01154 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "Realtime ODBC configuration",
01155 .load = load_module,
01156 .unload = unload_module,
01157 .reload = reload_module,
01158 .load_pri = AST_MODPRI_REALTIME_DRIVER,
01159 );