Fix double comma typo in sql_create_table (bug #2196)
[asterisk/asterisk.git] / cdr / cdr_sqlite.c
1 /*
2  * Asterisk -- A telephony toolkit for Linux.
3  *
4  * Store CDR records in a SQLite database.
5  * 
6  * Copyright (C) 2004, Holger Schurig
7  *
8  * Holger Schurig <hs4233@mail.mn-solutions.de>
9  *
10  * This program is free software, distributed under the terms of
11  * the GNU General Public License.
12  *
13  * Ideas taken from other cdr_*.c files
14  */
15
16 #include <sys/types.h>
17 #include <asterisk/cdr.h>
18 #include <asterisk/module.h>
19 #include <asterisk/logger.h>
20 #include <asterisk/utils.h>
21 #include "../asterisk.h"
22 #include "../astconf.h"
23
24 #include <unistd.h>
25 #include <string.h>
26 #include <stdlib.h>
27 #include <sqlite.h>
28
29
30 #define LOG_UNIQUEID    0
31 #define LOG_USERFIELD   0
32
33 /* When you change the DATE_FORMAT, be sure to change the CHAR(19) below to something else */
34 #define DATE_FORMAT "%Y-%m-%d %T"
35
36 static char *desc = "SQLite CDR Backend";
37 static char *name = "sqlite";
38 static sqlite* db = NULL;
39
40 AST_MUTEX_DEFINE_STATIC(sqlite_lock);
41
42 static char sql_create_table[] = "CREATE TABLE cdr ("
43 "       AcctId          INTEGER PRIMARY KEY,"
44 "       clid            VARCHAR(80),"
45 "       src             VARCHAR(80),"
46 "       dst             VARCHAR(80),"
47 "       dcontext        VARCHAR(80),"
48 "       channel         VARCHAR(80),"
49 "       dstchannel      VARCHAR(80),"
50 "       lastapp         VARCHAR(80),"
51 "       lastdata        VARCHAR(80),"
52 "       start           CHAR(19),"
53 "       answer          CHAR(19),"
54 "       end             CHAR(19),"
55 "       duration        INTEGER,"
56 "       billsec         INTEGER,"
57 "       disposition     INTEGER,"
58 "       amaflags        INTEGER,"
59 "       accountcode     VARCHAR(20)"
60 #if LOG_UNIQUEID
61 "       ,uniqueid       VARCHAR(32)"
62 #endif
63 #if LOG_USERFIELD
64 "       ,userfield      VARCHAR(255)"
65 #endif
66 ");";
67
68 static int sqlite_log(struct ast_cdr *cdr)
69 {
70         int res = 0;
71         char *zErr = 0;
72         struct tm tm;
73         time_t t;
74         char startstr[80], answerstr[80], endstr[80];
75         int count;
76
77         ast_mutex_lock(&sqlite_lock);
78
79         t = cdr->start.tv_sec;
80         localtime_r(&t, &tm);
81         strftime(startstr, sizeof(startstr), DATE_FORMAT, &tm);
82
83         t = cdr->answer.tv_sec;
84         localtime_r(&t, &tm);
85         strftime(answerstr, sizeof(answerstr), DATE_FORMAT, &tm);
86
87         t = cdr->end.tv_sec;
88         localtime_r(&t, &tm);
89         strftime(endstr, sizeof(endstr), DATE_FORMAT, &tm);
90
91         for(count=0; count<5; count++) {
92                 res = sqlite_exec_printf(db,
93                         "INSERT INTO cdr ("
94                                 "clid,src,dst,dcontext,"
95                                 "channel,dstchannel,lastapp,lastdata, "
96                                 "start,answer,end,"
97                                 "duration,billsec,disposition,amaflags, "
98                                 "accountcode"
99 #                               if LOG_UNIQUEID
100                                 ",uniqueid"
101 #                               endif
102 #                               if LOG_USERFIELD
103                                 ",userfield"
104 #                               endif
105                         ") VALUES ("
106                                 "'%q', '%q', '%q', '%q', "
107                                 "'%q', '%q', '%q', '%q', "
108                                 "'%q', '%q', '%q', "
109                                 "%d, %d, %d, %d, "
110                                 "'%q'"
111 #                               if LOG_UNIQUEID
112                                 ",'%q'"
113 #                               endif
114 #                               if LOG_USERFIELD
115                                 ",'%q'"
116 #                               endif
117                         ")", NULL, NULL, &zErr,
118                                 cdr->clid, cdr->src, cdr->dst, cdr->dcontext,
119                                 cdr->channel, cdr->dstchannel, cdr->lastapp, cdr->lastdata,
120                                 startstr, answerstr, endstr,
121                                 cdr->duration, cdr->billsec, cdr->disposition, cdr->amaflags,
122                                 cdr->accountcode
123 #                               if LOG_UNIQUEID
124                                 ,cdr->uniqueid
125 #                               endif
126 #                               if LOG_USERFIELD
127                                 ,cdr->userfield
128 #                               endif
129                         );
130                 if (res != SQLITE_BUSY && res != SQLITE_LOCKED)
131                         break;
132                 usleep(200);
133         }
134         
135         if (zErr) {
136                 ast_log(LOG_ERROR, "cdr_sqlite: %s\n", zErr);
137                 free(zErr);
138         }
139
140         ast_mutex_unlock(&sqlite_lock);
141         return res;
142 }
143
144
145 char *description(void)
146 {
147         return desc;
148 }
149
150 int unload_module(void)
151 {
152         if (db)
153                 sqlite_close(db);
154         ast_cdr_unregister(name);
155         return 0;
156 }
157
158 int load_module(void)
159 {
160         char *zErr;
161         char fn[PATH_MAX];
162         int res;
163
164         /* is the database there? */
165         snprintf(fn, sizeof(fn), "%s/cdr.db", ast_config_AST_LOG_DIR);
166         db = sqlite_open(fn, 0660, &zErr);
167         if (!db) {
168                 ast_log(LOG_ERROR, "cdr_sqlite: %s\n", zErr);
169                 free(zErr);
170                 return -1;
171         }
172
173         /* is the table there? */
174         res = sqlite_exec(db, "SELECT COUNT(AcctId) FROM cdr;", NULL, NULL, NULL);
175         if (res) {
176                 res = sqlite_exec(db, sql_create_table, NULL, NULL, &zErr);
177                 if (res) {
178                         ast_log(LOG_ERROR, "cdr_sqlite: Unable to create table 'cdr': %s\n", zErr);
179                         free(zErr);
180                         goto err;
181                 }
182
183                 /* TODO: here we should probably create an index */
184         }
185         
186         res = ast_cdr_register(name, desc, sqlite_log);
187         if (res) {
188                 ast_log(LOG_ERROR, "Unable to register SQLite CDR handling\n");
189                 return -1;
190         }
191         return 0;
192
193 err:
194         if (db)
195                 sqlite_close(db);
196         return -1;
197 }
198
199 int reload(void)
200 {
201         return 0;
202 }
203
204 int usecount(void)
205 {
206         return 0;
207 }
208
209 char *key()
210 {
211         return ASTERISK_GPL_KEY;
212 }