cc255844dcdb847b54079e0d34246621ddaef857
[asterisk/asterisk.git] / res / res_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 1999 - 2005, Digium, Inc.
5  *
6  * Mark Spencer <markster@digium.com>
7  *
8  * res_odbc.c <ODBC resource manager>
9  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
10  *
11  * See http://www.asterisk.org for more information about
12  * the Asterisk project. Please do not directly contact
13  * any of the maintainers of this project for assistance;
14  * the project provides a web site, mailing lists and IRC
15  * channels for your use.
16  *
17  * This program is free software, distributed under the terms of
18  * the GNU General Public License Version 2. See the LICENSE file
19  * at the top of the source tree.
20  */
21
22
23 /*! \file
24  *
25  * \brief ODBC resource manager
26  * 
27  * \author Mark Spencer <markster@digium.com>
28  * \author Anthony Minessale II <anthmct@yahoo.com>
29  *
30  * \arg See also: \ref cdr_odbc
31  */
32
33 #include <stdio.h>
34 #include <stdlib.h>
35 #include <unistd.h>
36 #include <string.h>
37
38 #include "asterisk.h"
39
40 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
41
42 #include "asterisk/file.h"
43 #include "asterisk/logger.h"
44 #include "asterisk/channel.h"
45 #include "asterisk/config.h"
46 #include "asterisk/options.h"
47 #include "asterisk/pbx.h"
48 #include "asterisk/module.h"
49 #include "asterisk/cli.h"
50 #include "asterisk/lock.h"
51 #include "asterisk/res_odbc.h"
52 #define MAX_ODBC_HANDLES 25
53
54 struct odbc_list
55 {
56         char name[80];
57         odbc_obj *obj;
58         int used;
59 };
60
61 static struct odbc_list ODBC_REGISTRY[MAX_ODBC_HANDLES];
62
63
64 static void odbc_destroy(void)
65 {
66         int x = 0;
67
68         for (x = 0; x < MAX_ODBC_HANDLES; x++) {
69                 if (ODBC_REGISTRY[x].obj) {
70                         destroy_odbc_obj(&ODBC_REGISTRY[x].obj);
71                         ODBC_REGISTRY[x].obj = NULL;
72                 }
73         }
74 }
75
76 static odbc_obj *odbc_read(struct odbc_list *registry, const char *name)
77 {
78         int x = 0;
79         for (x = 0; x < MAX_ODBC_HANDLES; x++) {
80                 if (registry[x].used && !strcmp(registry[x].name, name)) {
81                         return registry[x].obj;
82                 }
83         }
84         return NULL;
85 }
86
87 static int odbc_write(struct odbc_list *registry, char *name, odbc_obj *obj)
88 {
89         int x = 0;
90         for (x = 0; x < MAX_ODBC_HANDLES; x++) {
91                 if (!registry[x].used) {
92                         ast_copy_string(registry[x].name, name, sizeof(registry[x].name));
93                         registry[x].obj = obj;
94                         registry[x].used = 1;
95                         return 1;
96                 }
97         }
98         return 0;
99 }
100
101 static void odbc_init(void)
102 {
103         int x = 0;
104         for (x = 0; x < MAX_ODBC_HANDLES; x++) {
105                 memset(&ODBC_REGISTRY[x], 0, sizeof(struct odbc_list));
106         }
107 }
108
109 /* internal stuff */
110
111 SQLHSTMT odbc_prepare_and_execute(odbc_obj *obj, SQLHSTMT (*prepare_cb)(odbc_obj *obj, void *data), void *data)
112 {
113         int res = 0, i, attempt;
114         SQLINTEGER nativeerror=0, numfields=0;
115         SQLSMALLINT diagbytes=0;
116         unsigned char state[10], diagnostic[256];
117         SQLHSTMT stmt;
118
119         for (attempt = 0; attempt < 2; attempt++) {
120                 /* This prepare callback may do more than just prepare -- it may also
121                  * bind parameters, bind results, etc.  The real key, here, is that
122                  * when we disconnect, all handles become invalid for most databases.
123                  * We must therefore redo everything when we establish a new
124                  * connection. */
125                 stmt = prepare_cb(obj, data);
126
127                 if (stmt) {
128                         res = SQLExecute(stmt);
129                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
130                                 if (res == SQL_ERROR) {
131                                         SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
132                                         for (i=0; i< numfields + 1; i++) {
133                                                 SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
134                                                 ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
135                                                 if (i > 10) {
136                                                         ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
137                                                         break;
138                                                 }
139                                         }
140                                 }
141
142                                 ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
143                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
144
145                                 ast_mutex_lock(&obj->lock);
146                                 obj->up = 0;
147                                 ast_mutex_unlock(&obj->lock);
148                                 odbc_obj_disconnect(obj);
149                                 odbc_obj_connect(obj);
150                                 continue;
151                         }
152                         break;
153                 }
154         }
155
156         return stmt;
157 }
158
159 int odbc_smart_execute(odbc_obj *obj, SQLHSTMT stmt) 
160 {
161         int res = 0, i;
162         SQLINTEGER nativeerror=0, numfields=0;
163         SQLSMALLINT diagbytes=0;
164         unsigned char state[10], diagnostic[256];
165
166         res = SQLExecute(stmt);
167         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
168                 if (res == SQL_ERROR) {
169                         SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
170                         for (i=0; i< numfields + 1; i++) {
171                                 SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
172                                 ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
173                                 if (i > 10) {
174                                         ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
175                                         break;
176                                 }
177                         }
178                 }
179 /*
180                 ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
181                 ast_mutex_lock(&obj->lock);
182                 obj->up = 0;
183                 ast_mutex_unlock(&obj->lock);
184                 odbc_obj_disconnect(obj);
185                 odbc_obj_connect(obj);
186                 res = SQLExecute(stmt);
187 */
188         }
189         
190         return res;
191 }
192
193
194 int odbc_smart_direct_execute(odbc_obj *obj, SQLHSTMT stmt, char *sql) 
195 {
196         int res = 0;
197
198         res = SQLExecDirect (stmt, (unsigned char *)sql, SQL_NTS);
199         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
200                 ast_log(LOG_WARNING, "SQL Execute error! Attempting a reconnect...\n");
201                 ast_mutex_lock(&obj->lock);
202                 obj->up = 0;
203                 ast_mutex_unlock(&obj->lock);
204                 odbc_obj_disconnect(obj);
205                 odbc_obj_connect(obj);
206                 res = SQLExecDirect (stmt, (unsigned char *)sql, SQL_NTS);
207         }
208         
209         return res;
210 }
211
212 int odbc_sanity_check(odbc_obj *obj) 
213 {
214         char *test_sql = "select 1";
215         SQLHSTMT stmt;
216         int res = 0;
217
218         ast_mutex_lock(&obj->lock);
219         if(obj->up) { /* so you say... let's make sure */
220                 res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
221                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
222                         obj->up = 0; /* Liar!*/
223                 } else {
224                         res = SQLPrepare(stmt, (unsigned char *)test_sql, SQL_NTS);
225                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
226                                 obj->up = 0; /* Liar!*/
227                         } else {
228                                 res = SQLExecute(stmt);
229                                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
230                                         obj->up = 0; /* Liar!*/
231                                 }
232                         }
233                 }
234                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
235         }
236         ast_mutex_unlock(&obj->lock);
237
238         if(!obj->up) { /* Try to reconnect! */
239                 ast_log(LOG_WARNING, "Connection is down attempting to reconnect...\n");
240                 odbc_obj_disconnect(obj);
241                 odbc_obj_connect(obj);
242         }
243         return obj->up;
244 }
245
246 static int load_odbc_config(void)
247 {
248         static char *cfg = "res_odbc.conf";
249         struct ast_config *config;
250         struct ast_variable *v;
251         char *cat, *dsn, *username, *password;
252         int enabled;
253         int connect = 0;
254         char *env_var;
255
256         odbc_obj *obj;
257
258         config = ast_config_load(cfg);
259         if (config) {
260                 for (cat = ast_category_browse(config, NULL); cat; cat=ast_category_browse(config, cat)) {
261                         if (!strcmp(cat, "ENV")) {
262                                 for (v = ast_variable_browse(config, cat); v; v = v->next) {
263                                         env_var = malloc(strlen(v->name) + strlen(v->value) + 2);
264                                         if (env_var) {
265                                                 sprintf(env_var, "%s=%s", v->name, v->value);
266                                                 ast_log(LOG_NOTICE, "Adding ENV var: %s=%s\n", v->name, v->value);
267                                                 putenv(env_var);
268                                                 free(env_var);
269                                         }
270                                 }
271
272                         cat = ast_category_browse(config, cat);
273                         }
274
275                         dsn = username = password = NULL;
276                         enabled = 1;
277                         connect = 0;
278                         for (v = ast_variable_browse(config, cat); v; v = v->next) {
279                                 if (!strcmp(v->name, "enabled"))
280                                         enabled = ast_true(v->value);
281                                 if (!strcmp(v->name, "pre-connect"))
282                                         connect = ast_true(v->value);
283                                 if (!strcmp(v->name, "dsn"))
284                                         dsn = v->value;
285                                 if (!strcmp(v->name, "username"))
286                                         username = v->value;
287                                 if (!strcmp(v->name, "password"))
288                                         password = v->value;
289                         }
290
291                         if (enabled && dsn) {
292                                 obj = new_odbc_obj(cat, dsn, username, password);
293                                 if (obj) {
294                                         register_odbc_obj(cat, obj);
295                                         ast_log(LOG_NOTICE, "registered database handle '%s' dsn->[%s]\n", cat, obj->dsn);
296                                         if (connect) {
297                                                 odbc_obj_connect(obj);
298                                         }
299                                 } else {
300                                         ast_log(LOG_WARNING, "Addition of obj %s failed.\n", cat);
301                                 }
302
303                         }
304                 }
305                 ast_config_destroy(config);
306         }
307         return 0;
308 }
309
310 int odbc_dump_fd(int fd, odbc_obj *obj)
311 {
312         /* make sure the connection is up before we lie to our master.*/
313         odbc_sanity_check(obj);
314         ast_cli(fd, "Name: %s\nDSN: %s\nConnected: %s\n\n", obj->name, obj->dsn, obj->up ? "yes" : "no");
315         return 0;
316 }
317
318 static int odbc_connect_usage(int fd)
319 {
320         ast_cli(fd, "usage odbc connect <DSN>\n");
321         return 0;
322 }
323
324 static int odbc_disconnect_usage(int fd)
325 {
326         ast_cli(fd, "usage odbc disconnect <DSN>\n");
327         return 0;
328 }
329
330 static int odbc_show_command(int fd, int argc, char **argv)
331 {
332         odbc_obj *obj;
333         int x = 0;
334         
335         if (!strcmp(argv[1], "show")) {
336                 if (!argv[2] || (argv[2] && !strcmp(argv[2], "all"))) {
337                         for (x = 0; x < MAX_ODBC_HANDLES; x++) {
338                                 if (!ODBC_REGISTRY[x].used)
339                                         break;
340                                 if (ODBC_REGISTRY[x].obj)
341                                         odbc_dump_fd(fd, ODBC_REGISTRY[x].obj);
342                         }
343                 } else {
344                         obj = odbc_read(ODBC_REGISTRY, argv[2]);
345                         if (obj)
346                                 odbc_dump_fd(fd, obj);
347                 }
348         }
349         return 0;
350 }
351
352 static int odbc_disconnect_command(int fd, int argc, char **argv)
353 {
354         odbc_obj *obj;
355         if (!strcmp(argv[1], "disconnect")) {
356                 if (!argv[2])
357                         return odbc_disconnect_usage(fd);
358
359                 obj = odbc_read(ODBC_REGISTRY, argv[2]);
360                 if (obj) {
361                         odbc_obj_disconnect(obj);
362                 }
363         } 
364         return 0;
365 }
366
367 static int odbc_connect_command(int fd, int argc, char **argv)
368 {
369         odbc_obj *obj;
370         if (!argv[1])
371                 return odbc_connect_usage(fd);
372
373         if (!strcmp(argv[1], "connect") || !strcmp(argv[1], "disconnect")) {
374                 if (!argv[2])
375                         return odbc_connect_usage(fd);
376
377                 obj = odbc_read(ODBC_REGISTRY, argv[2]);
378                 if (obj) {
379                         odbc_obj_connect(obj);
380                 }
381         }
382         return 0;
383 }
384
385
386 static char connect_usage[] =
387 "Usage: odbc connect <DSN>\n"
388 "       Connect to ODBC DSN\n";
389
390 static char disconnect_usage[] =
391 "Usage: odbc connect <DSN>\n"
392 "       Disconnect from ODBC DSN\n";
393
394 static char show_usage[] =
395 "Usage: odbc show {DSN}\n"
396 "       Show ODBC {DSN}\n"
397 "       Specifying DSN will show that DSN else, all DSNs are shown\n";
398
399 static struct ast_cli_entry odbc_connect_struct =
400         { { "odbc", "connect", NULL }, odbc_connect_command, "Connect to ODBC DSN", connect_usage };
401
402
403 static struct ast_cli_entry odbc_disconnect_struct =
404         { { "odbc", "disconnect", NULL }, odbc_disconnect_command, "Disconnect from ODBC DSN", disconnect_usage };
405
406 static struct ast_cli_entry odbc_show_struct =
407         { { "odbc", "show", NULL }, odbc_show_command, "Show ODBC DSN(s)", show_usage };
408
409 /* api calls */
410
411 int register_odbc_obj(char *name, odbc_obj *obj)
412 {
413         if (obj != NULL)
414                 return odbc_write(ODBC_REGISTRY, name, obj);
415         return 0;
416 }
417
418 odbc_obj *fetch_odbc_obj(const char *name, int check)
419 {
420         odbc_obj *obj = NULL;
421         if((obj = (odbc_obj *) odbc_read(ODBC_REGISTRY, name))) {
422                 if(check)
423                         odbc_sanity_check(obj);
424         }
425         return obj;
426 }
427
428 odbc_obj *new_odbc_obj(char *name, char *dsn, char *username, char *password)
429 {
430         static odbc_obj *new;
431
432         if (!(new = calloc(1, sizeof(*new))) || 
433             !(new->name = malloc(strlen(name) + 1)) || 
434             !(new->dsn = malloc(strlen(dsn) + 1)))
435                 goto cleanup;
436
437         if (username) {
438                 if (!(new->username = malloc(strlen(username) + 1)))
439                         goto cleanup;
440                 strcpy(new->username, username);
441         }
442
443         if (password) {
444                 if (!(new->password = malloc(strlen(password) + 1)))
445                         goto cleanup;
446                 strcpy(new->password, password);
447         }
448
449         strcpy(new->name, name);
450         strcpy(new->dsn, dsn);
451         new->env = SQL_NULL_HANDLE;
452         new->up = 0;
453         ast_mutex_init(&new->lock);
454         return new;
455
456 cleanup:
457         if (new) {
458                 free(new->name);
459                 free(new->dsn);
460                 free(new->username);
461                 free(new->password);
462
463                 free(new);      
464         }
465
466         return NULL;
467 }
468
469 void destroy_odbc_obj(odbc_obj **obj)
470 {
471         odbc_obj_disconnect(*obj);
472
473         ast_mutex_lock(&(*obj)->lock);
474         SQLFreeHandle(SQL_HANDLE_STMT, (*obj)->stmt);
475         SQLFreeHandle(SQL_HANDLE_DBC, (*obj)->con);
476         SQLFreeHandle(SQL_HANDLE_ENV, (*obj)->env);
477
478         free((*obj)->name);
479         free((*obj)->dsn);
480         if ((*obj)->username)
481                 free((*obj)->username);
482         if ((*obj)->password)
483                 free((*obj)->password);
484         ast_mutex_unlock(&(*obj)->lock);
485         ast_mutex_destroy(&(*obj)->lock);
486         free(*obj);
487 }
488
489 odbc_status odbc_obj_disconnect(odbc_obj *obj)
490 {
491         int res;
492         ast_mutex_lock(&obj->lock);
493
494         res = SQLDisconnect(obj->con);
495
496
497         if (res == ODBC_SUCCESS) {
498                 ast_log(LOG_WARNING, "res_odbc: disconnected %d from %s [%s]\n", res, obj->name, obj->dsn);
499         } else {
500                 ast_log(LOG_WARNING, "res_odbc: %s [%s] already disconnected\n",
501                 obj->name, obj->dsn);
502         }
503         obj->up = 0;
504         ast_mutex_unlock(&obj->lock);
505         return ODBC_SUCCESS;
506 }
507
508 odbc_status odbc_obj_connect(odbc_obj *obj)
509 {
510         int res;
511         SQLINTEGER err;
512         short int mlen;
513         unsigned char msg[200], stat[10];
514
515         ast_mutex_lock(&obj->lock);
516
517         if (obj->env == SQL_NULL_HANDLE) {
518                 res = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &obj->env);
519
520                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
521                         if (option_verbose > 3)
522                                 ast_log(LOG_WARNING, "res_odbc: Error AllocHandle\n");
523                         ast_mutex_unlock(&obj->lock);
524                         return ODBC_FAIL;
525                 }
526
527                 res = SQLSetEnvAttr(obj->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
528
529                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
530                         if (option_verbose > 3)
531                                 ast_log(LOG_WARNING, "res_odbc: Error SetEnv\n");
532                         SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
533                         ast_mutex_unlock(&obj->lock);
534                         return ODBC_FAIL;
535                 }
536
537                 res = SQLAllocHandle(SQL_HANDLE_DBC, obj->env, &obj->con);
538
539                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
540
541                         if (option_verbose > 3)
542                                 ast_log(LOG_WARNING, "res_odbc: Error AllocHDB %d\n", res);
543                         SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
544
545                         ast_mutex_unlock(&obj->lock);
546                         return ODBC_FAIL;
547                 }
548                 SQLSetConnectAttr(obj->con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 10, 0);
549         }
550         if(obj->up) {
551                 odbc_obj_disconnect(obj);
552                 ast_log(LOG_NOTICE,"Re-connecting %s\n", obj->name);
553         }
554
555         ast_log(LOG_NOTICE, "Connecting %s\n", obj->name);
556
557         res = SQLConnect(obj->con,
558                    (SQLCHAR *) obj->dsn, SQL_NTS,
559                    (SQLCHAR *) obj->username, SQL_NTS,
560                    (SQLCHAR *) obj->password, SQL_NTS);
561
562         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
563                 SQLGetDiagRec(SQL_HANDLE_DBC, obj->con, 1, stat, &err, msg, 100, &mlen);
564                 SQLFreeHandle(SQL_HANDLE_ENV, obj->env);
565                 ast_mutex_unlock(&obj->lock);
566                 ast_log(LOG_WARNING, "res_odbc: Error SQLConnect=%d errno=%d %s\n", res, (int)err, msg);
567                 return ODBC_FAIL;
568         } else {
569
570                 ast_log(LOG_NOTICE, "res_odbc: Connected to %s [%s]\n", obj->name, obj->dsn);
571                 obj->up = 1;
572         }
573
574         ast_mutex_unlock(&obj->lock);
575         return ODBC_SUCCESS;
576 }
577
578 LOCAL_USER_DECL;
579
580 static int unload_module(void *mod)
581 {
582         STANDARD_HANGUP_LOCALUSERS;
583         odbc_destroy();
584         ast_cli_unregister(&odbc_disconnect_struct);
585         ast_cli_unregister(&odbc_connect_struct);
586         ast_cli_unregister(&odbc_show_struct);
587         ast_log(LOG_NOTICE, "res_odbc unloaded.\n");
588         return 0;
589 }
590
591 static int load_module(void *mod)
592 {
593         odbc_init();
594         load_odbc_config();
595         ast_cli_register(&odbc_disconnect_struct);
596         ast_cli_register(&odbc_connect_struct);
597         ast_cli_register(&odbc_show_struct);
598         ast_log(LOG_NOTICE, "res_odbc loaded.\n");
599         return 0;
600 }
601
602 static const char *description(void)
603 {
604         return "ODBC Resource";
605 }
606
607 static const char *key(void)
608 {
609         return ASTERISK_GPL_KEY;
610 }
611
612 STD_MOD(MOD_0, NULL, NULL, NULL);