cel/cel_odbc: Provide microsecond precision in 'eventtime' column when possible
[asterisk/asterisk.git] / cel / cel_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 2008 Digium
5  *
6  * Adapted from cdr_adaptive_odbc:
7  * Tilghman Lesher <tlesher AT digium DOT com>
8  * by Steve Murphy
9  *
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.
15  *
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.
19  */
20
21 /*! \file
22  *
23  * \brief ODBC CEL backend
24  *
25  * \author Tilghman Lesher \verbatim <tlesher AT digium DOT com> \endverbatim
26  * \ingroup cel_drivers
27  */
28
29 /*** MODULEINFO
30         <depend>res_odbc</depend>
31         <support_level>core</support_level>
32  ***/
33
34 #include "asterisk.h"
35
36 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
37
38 #include <sys/types.h>
39 #include <time.h>
40
41 #include <sql.h>
42 #include <sqlext.h>
43 #include <sqltypes.h>
44
45 #include "asterisk/config.h"
46 #include "asterisk/channel.h"
47 #include "asterisk/lock.h"
48 #include "asterisk/linkedlists.h"
49 #include "asterisk/res_odbc.h"
50 #include "asterisk/cel.h"
51 #include "asterisk/module.h"
52
53 #define CONFIG  "cel_odbc.conf"
54
55 #define ODBC_BACKEND_NAME "ODBC CEL backend"
56
57 /*! \brief show_user_def is off by default */
58 #define CEL_SHOW_USERDEF_DEFAULT        0
59
60 /*! TRUE if we should set the eventtype field to USER_DEFINED on user events. */
61 static unsigned char cel_show_user_def;
62
63 /* Optimization to reduce number of memory allocations */
64 static int maxsize = 512, maxsize2 = 512;
65
66 struct columns {
67         char *name;
68         char *celname;
69         char *filtervalue;
70         char *staticvalue;
71         SQLSMALLINT type;
72         SQLINTEGER size;
73         SQLSMALLINT decimals;
74         SQLSMALLINT radix;
75         SQLSMALLINT nullable;
76         SQLINTEGER octetlen;
77         AST_LIST_ENTRY(columns) list;
78 };
79
80 struct tables {
81         char *connection;
82         char *table;
83         unsigned int usegmtime:1;
84         unsigned int allowleapsec:1;
85         AST_LIST_HEAD_NOLOCK(odbc_columns, columns) columns;
86         AST_RWLIST_ENTRY(tables) list;
87 };
88
89 static AST_RWLIST_HEAD_STATIC(odbc_tables, tables);
90
91 static int load_config(void)
92 {
93         struct ast_config *cfg;
94         struct ast_variable *var;
95         const char *tmp, *catg;
96         struct tables *tableptr;
97         struct columns *entry;
98         struct odbc_obj *obj;
99         char columnname[80];
100         char connection[40];
101         char table[40];
102         int lenconnection, lentable;
103         SQLLEN sqlptr;
104         int res = 0;
105         SQLHSTMT stmt = NULL;
106         struct ast_flags config_flags = { 0 }; /* Part of our config comes from the database */
107
108         cfg = ast_config_load(CONFIG, config_flags);
109         if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
110                 ast_log(LOG_WARNING, "Unable to load " CONFIG ".  No ODBC CEL records!\n");
111                 return -1;
112         }
113
114         /* Process the general category */
115         cel_show_user_def = CEL_SHOW_USERDEF_DEFAULT;
116         for (var = ast_variable_browse(cfg, "general"); var; var = var->next) {
117                 if (!strcasecmp(var->name, "show_user_defined")) {
118                         cel_show_user_def = ast_true(var->value) ? 1 : 0;
119                 } else {
120                         /* Unknown option name. */
121                 }
122         }
123
124         for (catg = ast_category_browse(cfg, NULL); catg; catg = ast_category_browse(cfg, catg)) {
125                 if (!strcasecmp(catg, "general")) {
126                         continue;
127                 }
128                 var = ast_variable_browse(cfg, catg);
129                 if (!var)
130                         continue;
131
132                 if (ast_strlen_zero(tmp = ast_variable_retrieve(cfg, catg, "connection"))) {
133                         ast_log(LOG_WARNING, "No connection parameter found in '%s'.  Skipping.\n", catg);
134                         continue;
135                 }
136                 ast_copy_string(connection, tmp, sizeof(connection));
137                 lenconnection = strlen(connection);
138
139                 /* When loading, we want to be sure we can connect. */
140                 obj = ast_odbc_request_obj(connection, 1);
141                 if (!obj) {
142                         ast_log(LOG_WARNING, "No such connection '%s' in the '%s' section of " CONFIG ".  Check res_odbc.conf.\n", connection, catg);
143                         continue;
144                 }
145
146                 if (ast_strlen_zero(tmp = ast_variable_retrieve(cfg, catg, "table"))) {
147                         ast_log(LOG_NOTICE, "No table name found.  Assuming 'cel'.\n");
148                         tmp = "cel";
149                 }
150                 ast_copy_string(table, tmp, sizeof(table));
151                 lentable = strlen(table);
152
153                 res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
154                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
155                         ast_log(LOG_WARNING, "SQL Alloc Handle failed on connection '%s'!\n", connection);
156                         ast_odbc_release_obj(obj);
157                         continue;
158                 }
159
160                 res = SQLColumns(stmt, NULL, 0, NULL, 0, (unsigned char *)table, SQL_NTS, (unsigned char *)"%", SQL_NTS);
161                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
162                         ast_log(LOG_ERROR, "Unable to query database columns on connection '%s'.  Skipping.\n", connection);
163                         ast_odbc_release_obj(obj);
164                         continue;
165                 }
166
167                 tableptr = ast_calloc(sizeof(char), sizeof(*tableptr) + lenconnection + 1 + lentable + 1);
168                 if (!tableptr) {
169                         ast_log(LOG_ERROR, "Out of memory creating entry for table '%s' on connection '%s'\n", table, connection);
170                         ast_odbc_release_obj(obj);
171                         res = -1;
172                         break;
173                 }
174
175                 tableptr->connection = (char *)tableptr + sizeof(*tableptr);
176                 tableptr->table = (char *)tableptr + sizeof(*tableptr) + lenconnection + 1;
177                 ast_copy_string(tableptr->connection, connection, lenconnection + 1);
178                 ast_copy_string(tableptr->table, table, lentable + 1);
179
180                 tableptr->usegmtime = 0;
181                 if (!ast_strlen_zero(tmp = ast_variable_retrieve(cfg, catg, "usegmtime"))) {
182                         tableptr->usegmtime = ast_true(tmp);
183                 }
184
185                 tableptr->allowleapsec = 1;
186                 if (!ast_strlen_zero(tmp = ast_variable_retrieve(cfg, catg, "allowleapsecond"))) {
187                         tableptr->allowleapsec = ast_true(tmp);
188                 }
189
190                 ast_verb(3, "Found CEL table %s@%s.\n", tableptr->table, tableptr->connection);
191
192                 /* Check for filters first */
193                 for (var = ast_variable_browse(cfg, catg); var; var = var->next) {
194                         if (strncmp(var->name, "filter", 6) == 0) {
195                                 char *celvar = ast_strdupa(var->name + 6);
196                                 celvar = ast_strip(celvar);
197                                 ast_verb(3, "Found filter %s for cel variable %s in %s@%s\n", var->value, celvar, tableptr->table, tableptr->connection);
198
199                                 entry = ast_calloc(sizeof(char), sizeof(*entry) + strlen(celvar) + 1 + strlen(var->value) + 1);
200                                 if (!entry) {
201                                         ast_log(LOG_ERROR, "Out of memory creating filter entry for CEL variable '%s' in table '%s' on connection '%s'\n", celvar, table, connection);
202                                         res = -1;
203                                         break;
204                                 }
205
206                                 /* NULL column entry means this isn't a column in the database */
207                                 entry->name = NULL;
208                                 entry->celname = (char *)entry + sizeof(*entry);
209                                 entry->filtervalue = (char *)entry + sizeof(*entry) + strlen(celvar) + 1;
210                                 strcpy(entry->celname, celvar);
211                                 strcpy(entry->filtervalue, var->value);
212
213                                 AST_LIST_INSERT_TAIL(&(tableptr->columns), entry, list);
214                         }
215                 }
216
217                 while ((res = SQLFetch(stmt)) != SQL_NO_DATA && res != SQL_ERROR) {
218                         char *celvar = "", *staticvalue = "";
219
220                         SQLGetData(stmt,  4, SQL_C_CHAR, columnname, sizeof(columnname), &sqlptr);
221
222                         /* Is there an alias for this column? */
223
224                         /* NOTE: This seems like a non-optimal parse method, but I'm going
225                          * for user configuration readability, rather than fast parsing. We
226                          * really don't parse this file all that often, anyway.
227                          */
228                         for (var = ast_variable_browse(cfg, catg); var; var = var->next) {
229                                 if (strncmp(var->name, "alias", 5) == 0 && strcasecmp(var->value, columnname) == 0) {
230                                         char *alias = ast_strdupa(var->name + 5);
231                                         celvar = ast_strip(alias);
232                                         ast_verb(3, "Found alias %s for column %s in %s@%s\n", celvar, columnname, tableptr->table, tableptr->connection);
233                                         break;
234                                 } else if (strncmp(var->name, "static", 6) == 0 && strcasecmp(var->value, columnname) == 0) {
235                                         char *item = ast_strdupa(var->name + 6);
236                                         item = ast_strip(item);
237                                         if (item[0] == '"' && item[strlen(item) - 1] == '"') {
238                                                 /* Remove surrounding quotes */
239                                                 item[strlen(item) - 1] = '\0';
240                                                 item++;
241                                         }
242                                         staticvalue = item;
243                                 }
244                         }
245
246                         entry = ast_calloc(sizeof(char), sizeof(*entry) + strlen(columnname) + 1 + strlen(celvar) + 1 + strlen(staticvalue) + 1);
247                         if (!entry) {
248                                 ast_log(LOG_ERROR, "Out of memory creating entry for column '%s' in table '%s' on connection '%s'\n", columnname, table, connection);
249                                 res = -1;
250                                 break;
251                         }
252                         entry->name = (char *)entry + sizeof(*entry);
253                         strcpy(entry->name, columnname);
254
255                         if (!ast_strlen_zero(celvar)) {
256                                 entry->celname = entry->name + strlen(columnname) + 1;
257                                 strcpy(entry->celname, celvar);
258                         } else { /* Point to same place as the column name */
259                                 entry->celname = (char *)entry + sizeof(*entry);
260                         }
261
262                         if (!ast_strlen_zero(staticvalue)) {
263                                 entry->staticvalue = entry->celname + strlen(entry->celname) + 1;
264                                 strcpy(entry->staticvalue, staticvalue);
265                         }
266
267                         SQLGetData(stmt,  5, SQL_C_SHORT, &entry->type, sizeof(entry->type), NULL);
268                         SQLGetData(stmt,  7, SQL_C_LONG, &entry->size, sizeof(entry->size), NULL);
269                         SQLGetData(stmt,  9, SQL_C_SHORT, &entry->decimals, sizeof(entry->decimals), NULL);
270                         SQLGetData(stmt, 10, SQL_C_SHORT, &entry->radix, sizeof(entry->radix), NULL);
271                         SQLGetData(stmt, 11, SQL_C_SHORT, &entry->nullable, sizeof(entry->nullable), NULL);
272                         SQLGetData(stmt, 16, SQL_C_LONG, &entry->octetlen, sizeof(entry->octetlen), NULL);
273
274                         /* Specification states that the octenlen should be the maximum number of bytes
275                          * returned in a char or binary column, but it seems that some drivers just set
276                          * it to NULL. (Bad Postgres! No biscuit!) */
277                         if (entry->octetlen == 0)
278                                 entry->octetlen = entry->size;
279
280                         ast_verb(10, "Found %s column with type %hd with len %ld, octetlen %ld, and numlen (%hd,%hd)\n", entry->name, entry->type, (long) entry->size, (long) entry->octetlen, entry->decimals, entry->radix);
281                         /* Insert column info into column list */
282                         AST_LIST_INSERT_TAIL(&(tableptr->columns), entry, list);
283                         res = 0;
284                 }
285
286                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
287                 ast_odbc_release_obj(obj);
288
289                 if (AST_LIST_FIRST(&(tableptr->columns)))
290                         AST_RWLIST_INSERT_TAIL(&odbc_tables, tableptr, list);
291                 else
292                         ast_free(tableptr);
293         }
294         return res;
295 }
296
297 static int free_config(void)
298 {
299         struct tables *table;
300         struct columns *entry;
301         while ((table = AST_RWLIST_REMOVE_HEAD(&odbc_tables, list))) {
302                 while ((entry = AST_LIST_REMOVE_HEAD(&(table->columns), list))) {
303                         ast_free(entry);
304                 }
305                 ast_free(table);
306         }
307         return 0;
308 }
309
310 static SQLHSTMT generic_prepare(struct odbc_obj *obj, void *data)
311 {
312         int res, i;
313         char *sql = data;
314         SQLHSTMT stmt;
315         SQLINTEGER nativeerror = 0, numfields = 0;
316         SQLSMALLINT diagbytes = 0;
317         unsigned char state[10], diagnostic[256];
318
319         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
320         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
321                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
322                 return NULL;
323         }
324
325         res = SQLPrepare(stmt, (unsigned char *)sql, SQL_NTS);
326         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
327                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
328                 SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
329                 for (i = 0; i < numfields; i++) {
330                         SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
331                         ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
332                         if (i > 10) {
333                                 ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
334                                 break;
335                         }
336                 }
337                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
338                 return NULL;
339         }
340
341         return stmt;
342 }
343
344 #define LENGTHEN_BUF1(size)                                                                                                             \
345                         do {                                                                                                                            \
346                                 /* Lengthen buffer, if necessary */                                                             \
347                                 if (ast_str_strlen(sql) + size + 1 > ast_str_size(sql)) {               \
348                                         if (ast_str_make_space(&sql, ((ast_str_size(sql) + size + 1) / 512 + 1) * 512) != 0) { \
349                                                 ast_log(LOG_ERROR, "Unable to allocate sufficient memory.  Insert CEL '%s:%s' failed.\n", tableptr->connection, tableptr->table); \
350                                                 ast_free(sql);                                                                                  \
351                                                 ast_free(sql2);                                                                                 \
352                                                 AST_RWLIST_UNLOCK(&odbc_tables);                                                \
353                                                 return;                                                                                                 \
354                                         }                                                                                                                       \
355                                 }                                                                                                                               \
356                         } while (0)
357
358 #define LENGTHEN_BUF2(size)                                                                                                             \
359                         do {                                                                                                                            \
360                                 if (ast_str_strlen(sql2) + size + 1 > ast_str_size(sql2)) {             \
361                                         if (ast_str_make_space(&sql2, ((ast_str_size(sql2) + size + 3) / 512 + 1) * 512) != 0) { \
362                                                 ast_log(LOG_ERROR, "Unable to allocate sufficient memory.  Insert CEL '%s:%s' failed.\n", tableptr->connection, tableptr->table); \
363                                                 ast_free(sql);                                                                                  \
364                                                 ast_free(sql2);                                                                                 \
365                                                 AST_RWLIST_UNLOCK(&odbc_tables);                                                \
366                                                 return;                                                                                                 \
367                                         }                                                                                                                       \
368                                 }                                                                                                                               \
369                         } while (0)
370
371 static void odbc_log(struct ast_event *event)
372 {
373         struct tables *tableptr;
374         struct columns *entry;
375         struct odbc_obj *obj;
376         struct ast_str *sql = ast_str_create(maxsize), *sql2 = ast_str_create(maxsize2);
377         char *tmp;
378         char colbuf[1024], *colptr;
379         SQLHSTMT stmt = NULL;
380         SQLLEN rows = 0;
381         struct ast_cel_event_record record = {
382                 .version = AST_CEL_EVENT_RECORD_VERSION,
383         };
384
385         if (ast_cel_fill_record(event, &record)) {
386                 return;
387         }
388
389         if (!sql || !sql2) {
390                 if (sql)
391                         ast_free(sql);
392                 if (sql2)
393                         ast_free(sql2);
394                 return;
395         }
396
397         if (AST_RWLIST_RDLOCK(&odbc_tables)) {
398                 ast_log(LOG_ERROR, "Unable to lock table list.  Insert CEL(s) failed.\n");
399                 ast_free(sql);
400                 ast_free(sql2);
401                 return;
402         }
403
404         AST_LIST_TRAVERSE(&odbc_tables, tableptr, list) {
405                 int first = 1;
406                 ast_str_set(&sql, 0, "INSERT INTO %s (", tableptr->table);
407                 ast_str_set(&sql2, 0, " VALUES (");
408
409                 /* No need to check the connection now; we'll handle any failure in prepare_and_execute */
410                 if (!(obj = ast_odbc_request_obj(tableptr->connection, 0))) {
411                         ast_log(LOG_WARNING, "Unable to retrieve database handle for '%s:%s'.  CEL failed: %s\n", tableptr->connection, tableptr->table, ast_str_buffer(sql));
412                         continue;
413                 }
414
415                 AST_LIST_TRAVERSE(&(tableptr->columns), entry, list) {
416                         int datefield = 0;
417                         int unknown = 0;
418                         if (strcasecmp(entry->celname, "eventtime") == 0) {
419                                 datefield = 1;
420                         }
421
422                         /* Check if we have a similarly named variable */
423                         if (entry->staticvalue) {
424                                 colptr = ast_strdupa(entry->staticvalue);
425                         } else if (datefield) {
426                                 struct timeval date_tv = record.event_time;
427                                 struct ast_tm tm = { 0, };
428                                 ast_localtime(&date_tv, &tm, tableptr->usegmtime ? "UTC" : NULL);
429                                 /* SQL server 2008 added datetime2 and datetimeoffset data types, that
430                                    are reported to SQLColumns() as SQL_WVARCHAR, according to "Enhanced
431                                    Date/Time Type Behavior with Previous SQL Server Versions (ODBC)".
432                                    Here we format the event time with fraction seconds, so these new
433                                    column types will be set to high-precision event time. However, 'date'
434                                    and 'time' columns, also newly introduced, reported as SQL_WVARCHAR
435                                    too, and insertion of the value formatted here into these will fail.
436                                    This should be ok, however, as nobody is going to store just event
437                                    date or just time for CDR purposes.
438                                  */
439                                 ast_strftime(colbuf, sizeof(colbuf), "%Y-%m-%d %H:%M:%S.%6q", &tm);
440                                 colptr = colbuf;
441                         } else {
442                                 if (strcmp(entry->celname, "userdeftype") == 0) {
443                                         ast_copy_string(colbuf, record.user_defined_name, sizeof(colbuf));
444                                 } else if (strcmp(entry->celname, "cid_name") == 0) {
445                                         ast_copy_string(colbuf, record.caller_id_name, sizeof(colbuf));
446                                 } else if (strcmp(entry->celname, "cid_num") == 0) {
447                                         ast_copy_string(colbuf, record.caller_id_num, sizeof(colbuf));
448                                 } else if (strcmp(entry->celname, "cid_ani") == 0) {
449                                         ast_copy_string(colbuf, record.caller_id_ani, sizeof(colbuf));
450                                 } else if (strcmp(entry->celname, "cid_rdnis") == 0) {
451                                         ast_copy_string(colbuf, record.caller_id_rdnis, sizeof(colbuf));
452                                 } else if (strcmp(entry->celname, "cid_dnid") == 0) {
453                                         ast_copy_string(colbuf, record.caller_id_dnid, sizeof(colbuf));
454                                 } else if (strcmp(entry->celname, "exten") == 0) {
455                                         ast_copy_string(colbuf, record.extension, sizeof(colbuf));
456                                 } else if (strcmp(entry->celname, "context") == 0) {
457                                         ast_copy_string(colbuf, record.context, sizeof(colbuf));
458                                 } else if (strcmp(entry->celname, "channame") == 0) {
459                                         ast_copy_string(colbuf, record.channel_name, sizeof(colbuf));
460                                 } else if (strcmp(entry->celname, "appname") == 0) {
461                                         ast_copy_string(colbuf, record.application_name, sizeof(colbuf));
462                                 } else if (strcmp(entry->celname, "appdata") == 0) {
463                                         ast_copy_string(colbuf, record.application_data, sizeof(colbuf));
464                                 } else if (strcmp(entry->celname, "accountcode") == 0) {
465                                         ast_copy_string(colbuf, record.account_code, sizeof(colbuf));
466                                 } else if (strcmp(entry->celname, "peeraccount") == 0) {
467                                         ast_copy_string(colbuf, record.peer_account, sizeof(colbuf));
468                                 } else if (strcmp(entry->celname, "uniqueid") == 0) {
469                                         ast_copy_string(colbuf, record.unique_id, sizeof(colbuf));
470                                 } else if (strcmp(entry->celname, "linkedid") == 0) {
471                                         ast_copy_string(colbuf, record.linked_id, sizeof(colbuf));
472                                 } else if (strcmp(entry->celname, "userfield") == 0) {
473                                         ast_copy_string(colbuf, record.user_field, sizeof(colbuf));
474                                 } else if (strcmp(entry->celname, "peer") == 0) {
475                                         ast_copy_string(colbuf, record.peer, sizeof(colbuf));
476                                 } else if (strcmp(entry->celname, "amaflags") == 0) {
477                                         snprintf(colbuf, sizeof(colbuf), "%u", record.amaflag);
478                                 } else if (strcmp(entry->celname, "extra") == 0) {
479                                         ast_copy_string(colbuf, record.extra, sizeof(colbuf));
480                                 } else if (strcmp(entry->celname, "eventtype") == 0) {
481                                         snprintf(colbuf, sizeof(colbuf), "%u", record.event_type);
482                                 } else {
483                                         colbuf[0] = 0;
484                                         unknown = 1;
485                                 }
486                                 colptr = colbuf;
487                         }
488
489                         if (colptr && !unknown) {
490                                 /* Check first if the column filters this entry.  Note that this
491                                  * is very specifically NOT ast_strlen_zero(), because the filter
492                                  * could legitimately specify that the field is blank, which is
493                                  * different from the field being unspecified (NULL). */
494                                 if (entry->filtervalue && strcasecmp(colptr, entry->filtervalue) != 0) {
495                                         ast_verb(4, "CEL column '%s' with value '%s' does not match filter of"
496                                                 " '%s'.  Cancelling this CEL.\n",
497                                                 entry->celname, colptr, entry->filtervalue);
498                                         goto early_release;
499                                 }
500
501                                 /* Only a filter? */
502                                 if (ast_strlen_zero(entry->name))
503                                         continue;
504
505                                 LENGTHEN_BUF1(strlen(entry->name));
506
507                                 switch (entry->type) {
508                                 case SQL_CHAR:
509                                 case SQL_VARCHAR:
510                                 case SQL_LONGVARCHAR:
511 #ifdef HAVE_ODBC_WCHAR
512                                 case SQL_WCHAR:
513                                 case SQL_WVARCHAR:
514                                 case SQL_WLONGVARCHAR:
515 #endif
516                                 case SQL_BINARY:
517                                 case SQL_VARBINARY:
518                                 case SQL_LONGVARBINARY:
519                                 case SQL_GUID:
520                                         /* For these two field names, get the rendered form, instead of the raw
521                                          * form (but only when we're dealing with a character-based field).
522                                          */
523                                         if (strcasecmp(entry->name, "eventtype") == 0) {
524                                                 const char *event_name;
525
526                                                 event_name = (!cel_show_user_def
527                                                         && record.event_type == AST_CEL_USER_DEFINED)
528                                                         ? record.user_defined_name : record.event_name;
529                                                 snprintf(colbuf, sizeof(colbuf), "%s", event_name);
530                                         }
531
532                                         /* Truncate too-long fields */
533                                         if (entry->type != SQL_GUID) {
534                                                 if (strlen(colptr) > entry->octetlen) {
535                                                         colptr[entry->octetlen] = '\0';
536                                                 }
537                                         }
538
539                                         ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
540                                         LENGTHEN_BUF2(strlen(colptr));
541
542                                         /* Encode value, with escaping */
543                                         ast_str_append(&sql2, 0, "%s'", first ? "" : ",");
544                                         for (tmp = colptr; *tmp; tmp++) {
545                                                 if (*tmp == '\'') {
546                                                         ast_str_append(&sql2, 0, "''");
547                                                 } else if (*tmp == '\\' && ast_odbc_backslash_is_escape(obj)) {
548                                                         ast_str_append(&sql2, 0, "\\\\");
549                                                 } else {
550                                                         ast_str_append(&sql2, 0, "%c", *tmp);
551                                                 }
552                                         }
553                                         ast_str_append(&sql2, 0, "'");
554                                         break;
555                                 case SQL_TYPE_DATE:
556                                         if (ast_strlen_zero(colptr)) {
557                                                 continue;
558                                         } else {
559                                                 int year = 0, month = 0, day = 0;
560                                                 if (strcasecmp(entry->name, "eventdate") == 0) {
561                                                         struct ast_tm tm;
562                                                         ast_localtime(&record.event_time, &tm, tableptr->usegmtime ? "UTC" : NULL);
563                                                         year = tm.tm_year + 1900;
564                                                         month = tm.tm_mon + 1;
565                                                         day = tm.tm_mday;
566                                                 } else {
567                                                         if (sscanf(colptr, "%4d-%2d-%2d", &year, &month, &day) != 3 || year <= 0 ||
568                                                                 month <= 0 || month > 12 || day < 0 || day > 31 ||
569                                                                 ((month == 4 || month == 6 || month == 9 || month == 11) && day == 31) ||
570                                                                 (month == 2 && year % 400 == 0 && day > 29) ||
571                                                                 (month == 2 && year % 100 == 0 && day > 28) ||
572                                                                 (month == 2 && year % 4 == 0 && day > 29) ||
573                                                                 (month == 2 && year % 4 != 0 && day > 28)) {
574                                                                 ast_log(LOG_WARNING, "CEL variable %s is not a valid date ('%s').\n", entry->name, colptr);
575                                                                 continue;
576                                                         }
577
578                                                         if (year > 0 && year < 100) {
579                                                                 year += 2000;
580                                                         }
581                                                 }
582
583                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
584                                                 LENGTHEN_BUF2(17);
585                                                 ast_str_append(&sql2, 0, "%s{d '%04d-%02d-%02d'}", first ? "" : ",", year, month, day);
586                                         }
587                                         break;
588                                 case SQL_TYPE_TIME:
589                                         if (ast_strlen_zero(colptr)) {
590                                                 continue;
591                                         } else {
592                                                 int hour = 0, minute = 0, second = 0;
593                                                 if (strcasecmp(entry->name, "eventdate") == 0) {
594                                                         struct ast_tm tm;
595                                                         ast_localtime(&record.event_time, &tm, tableptr->usegmtime ? "UTC" : NULL);
596                                                         hour = tm.tm_hour;
597                                                         minute = tm.tm_min;
598                                                         second = (tableptr->allowleapsec || tm.tm_sec < 60) ? tm.tm_sec : 59;
599                                                 } else {
600                                                         int count = sscanf(colptr, "%2d:%2d:%2d", &hour, &minute, &second);
601
602                                                         if ((count != 2 && count != 3) || hour < 0 || hour > 23 || minute < 0 || minute > 59 || second < 0 || second > (tableptr->allowleapsec ? 60 : 59)) {
603                                                                 ast_log(LOG_WARNING, "CEL variable %s is not a valid time ('%s').\n", entry->name, colptr);
604                                                                 continue;
605                                                         }
606                                                 }
607
608                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
609                                                 LENGTHEN_BUF2(15);
610                                                 ast_str_append(&sql2, 0, "%s{t '%02d:%02d:%02d'}", first ? "" : ",", hour, minute, second);
611                                         }
612                                         break;
613                                 case SQL_TYPE_TIMESTAMP:
614                                 case SQL_TIMESTAMP:
615                                         if (ast_strlen_zero(colptr)) {
616                                                 continue;
617                                         } else {
618                                                 int year = 0, month = 0, day = 0, hour = 0, minute = 0, second = 0, fraction = 0;
619                                                 if (strcasecmp(entry->name, "eventdate") == 0) {
620                                                         struct ast_tm tm;
621                                                         ast_localtime(&record.event_time, &tm, tableptr->usegmtime ? "UTC" : NULL);
622                                                         year = tm.tm_year + 1900;
623                                                         month = tm.tm_mon + 1;
624                                                         day = tm.tm_mday;
625                                                         hour = tm.tm_hour;
626                                                         minute = tm.tm_min;
627                                                         second = (tableptr->allowleapsec || tm.tm_sec < 60) ? tm.tm_sec : 59;
628                                                         fraction = tm.tm_usec;
629                                                 } else {
630                                                         int count = sscanf(colptr, "%4d-%2d-%2d %2d:%2d:%2d.%6d", &year, &month, &day, &hour, &minute, &second, &fraction);
631
632                                                         if ((count != 3 && count != 5 && count != 6 && count != 7) || year <= 0 ||
633                                                                 month <= 0 || month > 12 || day < 0 || day > 31 ||
634                                                                 ((month == 4 || month == 6 || month == 9 || month == 11) && day == 31) ||
635                                                                 (month == 2 && year % 400 == 0 && day > 29) ||
636                                                                 (month == 2 && year % 100 == 0 && day > 28) ||
637                                                                 (month == 2 && year % 4 == 0 && day > 29) ||
638                                                                 (month == 2 && year % 4 != 0 && day > 28) ||
639                                                                 hour > 23 || minute > 59 || second > (tableptr->allowleapsec ? 60 : 59) || hour < 0 || minute < 0 || second < 0 || fraction < 0) {
640                                                                 ast_log(LOG_WARNING, "CEL variable %s is not a valid timestamp ('%s').\n", entry->name, colptr);
641                                                                 continue;
642                                                         }
643
644                                                         if (year > 0 && year < 100) {
645                                                                 year += 2000;
646                                                         }
647                                                 }
648
649                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
650                                                 LENGTHEN_BUF2(27);
651                                                 ast_str_append(&sql2, 0, "%s{ts '%04d-%02d-%02d %02d:%02d:%02d.%d'}", first ? "" : ",", year, month, day, hour, minute, second, fraction);
652                                         }
653                                         break;
654                                 case SQL_INTEGER:
655                                         {
656                                                 int integer = 0;
657                                                 if (sscanf(colptr, "%30d", &integer) != 1) {
658                                                         ast_log(LOG_WARNING, "CEL variable %s is not an integer.\n", entry->name);
659                                                         continue;
660                                                 }
661
662                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
663                                                 LENGTHEN_BUF2(12);
664                                                 ast_str_append(&sql2, 0, "%s%d", first ? "" : ",", integer);
665                                         }
666                                         break;
667                                 case SQL_BIGINT:
668                                         {
669                                                 long long integer = 0;
670                                                 int ret;
671                                                 if ((ret = sscanf(colptr, "%30lld", &integer)) != 1) {
672                                                         ast_log(LOG_WARNING, "CEL variable %s is not an integer. (%d - '%s')\n", entry->name, ret, colptr);
673                                                         continue;
674                                                 }
675
676                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
677                                                 LENGTHEN_BUF2(24);
678                                                 ast_str_append(&sql2, 0, "%s%lld", first ? "" : ",", integer);
679                                         }
680                                         break;
681                                 case SQL_SMALLINT:
682                                         {
683                                                 short integer = 0;
684                                                 if (sscanf(colptr, "%30hd", &integer) != 1) {
685                                                         ast_log(LOG_WARNING, "CEL variable %s is not an integer.\n", entry->name);
686                                                         continue;
687                                                 }
688
689                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
690                                                 LENGTHEN_BUF2(7);
691                                                 ast_str_append(&sql2, 0, "%s%d", first ? "" : ",", integer);
692                                         }
693                                         break;
694                                 case SQL_TINYINT:
695                                         {
696                                                 signed char integer = 0;
697                                                 if (sscanf(colptr, "%30hhd", &integer) != 1) {
698                                                         ast_log(LOG_WARNING, "CEL variable %s is not an integer.\n", entry->name);
699                                                         continue;
700                                                 }
701
702                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
703                                                 LENGTHEN_BUF2(4);
704                                                 ast_str_append(&sql2, 0, "%s%d", first ? "" : ",", integer);
705                                         }
706                                         break;
707                                 case SQL_BIT:
708                                         {
709                                                 signed char integer = 0;
710                                                 if (sscanf(colptr, "%30hhd", &integer) != 1) {
711                                                         ast_log(LOG_WARNING, "CEL variable %s is not an integer.\n", entry->name);
712                                                         continue;
713                                                 }
714                                                 if (integer != 0)
715                                                         integer = 1;
716
717                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
718                                                 LENGTHEN_BUF2(2);
719                                                 ast_str_append(&sql2, 0, "%s%d", first ? "" : ",", integer);
720                                         }
721                                         break;
722                                 case SQL_NUMERIC:
723                                 case SQL_DECIMAL:
724                                         {
725                                                 double number = 0.0;
726                                                 if (sscanf(colptr, "%30lf", &number) != 1) {
727                                                         ast_log(LOG_WARNING, "CEL variable %s is not an numeric type.\n", entry->name);
728                                                         continue;
729                                                 }
730
731                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
732                                                 LENGTHEN_BUF2(entry->decimals + 2);
733                                                 ast_str_append(&sql2, 0, "%s%*.*lf", first ? "" : ",", entry->decimals, entry->radix, number);
734                                         }
735                                         break;
736                                 case SQL_FLOAT:
737                                 case SQL_REAL:
738                                 case SQL_DOUBLE:
739                                         {
740                                                 double number = 0.0;
741                                                 if (sscanf(colptr, "%30lf", &number) != 1) {
742                                                         ast_log(LOG_WARNING, "CEL variable %s is not an numeric type.\n", entry->name);
743                                                         continue;
744                                                 }
745
746                                                 ast_str_append(&sql, 0, "%s%s", first ? "" : ",", entry->name);
747                                                 LENGTHEN_BUF2(entry->decimals);
748                                                 ast_str_append(&sql2, 0, "%s%lf", first ? "" : ",", number);
749                                         }
750                                         break;
751                                 default:
752                                         ast_log(LOG_WARNING, "Column type %d (field '%s:%s:%s') is unsupported at this time.\n", entry->type, tableptr->connection, tableptr->table, entry->name);
753                                         continue;
754                                 }
755                                 first = 0;
756                         }
757                 }
758
759                 /* Concatenate the two constructed buffers */
760                 LENGTHEN_BUF1(ast_str_strlen(sql2));
761                 ast_str_append(&sql, 0, ")");
762                 ast_str_append(&sql2, 0, ")");
763                 ast_str_append(&sql, 0, "%s", ast_str_buffer(sql2));
764
765                 ast_debug(3, "Executing SQL statement: [%s]\n", ast_str_buffer(sql));
766                 stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, ast_str_buffer(sql));
767                 if (stmt) {
768                         SQLRowCount(stmt, &rows);
769                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
770                 }
771                 if (rows == 0) {
772                         ast_log(LOG_WARNING, "Insert failed on '%s:%s'.  CEL failed: %s\n", tableptr->connection, tableptr->table, ast_str_buffer(sql));
773                 }
774 early_release:
775                 ast_odbc_release_obj(obj);
776         }
777         AST_RWLIST_UNLOCK(&odbc_tables);
778
779         /* Next time, just allocate buffers that are that big to start with. */
780         if (ast_str_strlen(sql) > maxsize) {
781                 maxsize = ast_str_strlen(sql);
782         }
783         if (ast_str_strlen(sql2) > maxsize2) {
784                 maxsize2 = ast_str_strlen(sql2);
785         }
786
787         ast_free(sql);
788         ast_free(sql2);
789 }
790
791 static int unload_module(void)
792 {
793         if (AST_RWLIST_WRLOCK(&odbc_tables)) {
794                 ast_log(LOG_ERROR, "Unable to lock column list.  Unload failed.\n");
795                 return -1;
796         }
797
798         ast_cel_backend_unregister(ODBC_BACKEND_NAME);
799         free_config();
800         AST_RWLIST_UNLOCK(&odbc_tables);
801         AST_RWLIST_HEAD_DESTROY(&odbc_tables);
802         
803         return 0;
804 }
805
806 static int load_module(void)
807 {
808         AST_RWLIST_HEAD_INIT(&odbc_tables);
809
810         if (AST_RWLIST_WRLOCK(&odbc_tables)) {
811                 ast_log(LOG_ERROR, "Unable to lock column list.  Load failed.\n");
812                 return AST_MODULE_LOAD_FAILURE;
813         }
814         load_config();
815         AST_RWLIST_UNLOCK(&odbc_tables);
816         if (ast_cel_backend_register(ODBC_BACKEND_NAME, odbc_log)) {
817                 ast_log(LOG_ERROR, "Unable to subscribe to CEL events\n");
818                 return AST_MODULE_LOAD_FAILURE;
819         }
820         return AST_MODULE_LOAD_SUCCESS;
821 }
822
823 static int reload(void)
824 {
825         if (AST_RWLIST_WRLOCK(&odbc_tables)) {
826                 ast_log(LOG_ERROR, "Unable to lock column list.  Reload failed.\n");
827                 return AST_MODULE_LOAD_FAILURE;
828         }
829
830         free_config();
831         load_config();
832         AST_RWLIST_UNLOCK(&odbc_tables);
833         return AST_MODULE_LOAD_SUCCESS;
834 }
835
836 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, ODBC_BACKEND_NAME,
837         .support_level = AST_MODULE_SUPPORT_CORE,
838         .load = load_module,
839         .unload = unload_module,
840         .reload = reload,
841         .load_pri = AST_MODPRI_CDR_DRIVER,
842 );
843