2 * ulogd output plugin for logging to a SQLITE database
4 * (C) 2005 by Ben La Monica <ben.lamonica@gmail.com>
6 * This program is free software; you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License version 2
8 * as published by the Free Software Foundation
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
19 * This module has been adapted from the ulogd_MYSQL.c written by
20 * Harald Welte <laforge@gnumonks.org>
21 * Alex Janssen <alex@ynfonatic.de>
23 * You can see benchmarks and an explanation of the testing
24 * at http://www.pojo.us/ulogd/
26 * 2005-02-09 Harald Welte <laforge@gnumonks.org>:
27 * - port to ulogd-1.20
32 #include <arpa/inet.h>
33 #include <ulogd/ulogd.h>
34 #include <ulogd/conffile.h>
38 #define DEBUGP(x, args...) fprintf(stderr, x, ## args)
40 #define DEBUGP(x, args...)
44 char name[ULOGD_MAX_KEYLEN];
49 /* the database handle we are using */
52 /* a linked list of the fields the table has */
53 static struct _field *fields;
55 /* buffer for our insert statement */
58 /* pointer to the final prepared statement */
59 static sqlite3_stmt *p_stmt;
61 /* number of statements to buffer before we commit */
62 static int buffer_size;
64 /* number of statements currently in the buffer */
65 static int buffer_ctr;
67 /* our configuration directives */
68 static config_entry_t db_ce = {
70 .type = CONFIG_TYPE_STRING,
71 .options = CONFIG_OPT_MANDATORY,
74 static config_entry_t table_ce = {
77 .type = CONFIG_TYPE_STRING,
78 .options = CONFIG_OPT_MANDATORY,
81 static config_entry_t buffer_ce = {
84 .type = CONFIG_TYPE_INT,
85 .options = CONFIG_OPT_MANDATORY,
88 /* our main output function, called by ulogd */
89 static int _sqlite3_output(ulog_iret_t *result)
100 for (f = fields; f; f = f->next) {
101 res = keyh_getres(f->id);
104 ulogd_log(ULOGD_NOTICE,
105 "no result for %s ?!?\n", f->name);
108 if (!res || !IS_VALID((*res))) {
109 /* no result, pass a null */
110 sqlite3_bind_null(p_stmt, col_counter);
117 sqlite3_bind_int(p_stmt,col_counter,res->value.i8);
119 case ULOGD_RET_INT16:
120 sqlite3_bind_int(p_stmt,col_counter,res->value.i16);
122 case ULOGD_RET_INT32:
123 sqlite3_bind_int(p_stmt,col_counter,res->value.i32);
125 case ULOGD_RET_INT64:
126 sqlite3_bind_int64(p_stmt,col_counter,res->value.i64);
128 case ULOGD_RET_UINT8:
129 sqlite3_bind_int(p_stmt,col_counter,res->value.ui8);
131 case ULOGD_RET_UINT16:
132 sqlite3_bind_int(p_stmt,col_counter,res->value.ui16);
134 case ULOGD_RET_IPADDR:
136 memset(&addr, 0, sizeof(addr));
137 addr.s_addr = ntohl(res->value.ui32);
138 ipaddr = inet_ntoa(addr);
139 sqlite3_bind_text(p_stmt,col_counter,ipaddr,strlen(ipaddr),SQLITE_STATIC);
141 #endif /* IP_AS_STRING */
142 /* EVIL: fallthrough when logging IP as u_int32_t */
143 case ULOGD_RET_UINT32:
144 sqlite3_bind_int(p_stmt,col_counter,res->value.ui32);
146 case ULOGD_RET_UINT64:
147 sqlite3_bind_int64(p_stmt,col_counter,res->value.ui64);
150 sqlite3_bind_int(p_stmt,col_counter,res->value.b);
152 case ULOGD_RET_STRING:
153 sqlite3_bind_text(p_stmt,col_counter,res->value.ptr,strlen(res->value.ptr),SQLITE_STATIC);
156 ulogd_log(ULOGD_NOTICE,
157 "unknown type %d for %s\n",
158 res->type, res->key);
165 /* now we have created our statement, insert it */
167 if (sqlite3_step(p_stmt) == SQLITE_DONE) {
168 sqlite3_reset(p_stmt);
171 ulogd_log(ULOGD_ERROR, "sql error during insert: %s\n",
172 sqlite3_errmsg(dbh));
176 /* commit all of the inserts to the database, ie flush buffer */
177 if (buffer_ctr >= buffer_size) {
178 if (sqlite3_exec(dbh,"commit",NULL,NULL,NULL) != SQLITE_OK)
179 ulogd_log(ULOGD_ERROR,"unable to commit records to db.");
181 if (sqlite3_exec(dbh,"begin deferred",NULL,NULL,NULL) != SQLITE_OK)
182 ulogd_log(ULOGD_ERROR,"unable to begin a new transaction.");
185 DEBUGP("committing.\n");
191 #define _SQLITE3_INSERTTEMPL "insert into X (Y) values (Z)"
193 /* create the static part of our insert statement */
194 static int _sqlite3_createstmt(void)
198 char buf[ULOGD_MAX_KEYLEN];
205 ulogd_log(ULOGD_NOTICE, "createstmt called, but stmt"
206 " already existing\n");
210 /* caclulate the size for the insert statement */
211 size = strlen(_SQLITE3_INSERTTEMPL) + strlen(table_ce.u.string);
213 DEBUGP("initial size: %u\n", size);
216 for (f = fields; f; f = f->next) {
217 /* we need space for the key and a comma, and a ? */
218 size += strlen(f->name) + 3;
219 DEBUGP("size is now %u since adding %s\n",size,f->name);
223 DEBUGP("there were %d columns\n",col_count);
224 DEBUGP("after calc name length: %u\n",size);
226 ulogd_log(ULOGD_DEBUG, "allocating %u bytes for statement\n", size);
228 stmt = (char *) malloc(size);
231 ulogd_log(ULOGD_ERROR, "OOM!\n");
235 sprintf(stmt, "insert into %s (", table_ce.u.string);
236 stmt_pos = stmt + strlen(stmt);
238 for (f = fields; f; f = f->next) {
239 strncpy(buf, f->name, ULOGD_MAX_KEYLEN);
240 while ((underscore = strchr(buf, '.')))
242 sprintf(stmt_pos, "%s,", buf);
243 stmt_pos = stmt + strlen(stmt);
246 *(stmt_pos - 1) = ')';
248 sprintf(stmt_pos, " values (");
249 stmt_pos = stmt + strlen(stmt);
251 for (i = 0; i < col_count - 1; i++) {
252 sprintf(stmt_pos,"?,");
256 sprintf(stmt_pos, "?)");
257 ulogd_log(ULOGD_DEBUG, "stmt='%s'\n", stmt);
259 DEBUGP("about to prepare statement.\n");
261 sqlite3_prepare(dbh,stmt,-1,&p_stmt,0);
263 DEBUGP("statement prepared.\n");
266 ulogd_log(ULOGD_ERROR,"unable to prepare statement");
274 /* length of "select * from \0" */
275 #define SQLITE_SELECT_LEN 15
277 /* find out which columns the table has */
278 static int _sqlite3_get_columns(const char *table)
280 char buf[ULOGD_MAX_KEYLEN];
281 char query[SQLITE_SELECT_LEN + CONFIG_VAL_STRING_LEN] = "select * from \0";
284 sqlite3_stmt *schema_stmt;
292 strncat(query,table,LINE_LEN);
294 result = sqlite3_prepare(dbh,query,-1,&schema_stmt,0);
296 if (result != SQLITE_OK)
299 for (column = 0; column < sqlite3_column_count(schema_stmt); column++) {
300 /* replace all underscores with dots */
301 strncpy(buf, sqlite3_column_name(schema_stmt,column), ULOGD_MAX_KEYLEN);
302 while ((underscore = strchr(buf, '_')))
305 DEBUGP("field '%s' found: ", buf);
307 if (!(id = keyh_getid(buf))) {
308 DEBUGP(" no keyid!\n");
312 DEBUGP("keyid %u\n", id);
314 /* prepend it to the linked list */
315 f = (struct _field *) malloc(sizeof *f);
317 ulogd_log(ULOGD_ERROR, "OOM!\n");
320 strncpy(f->name, buf, ULOGD_MAX_KEYLEN);
326 sqlite3_finalize(schema_stmt);
331 * make connection and select database
332 * returns 0 if database failed to open.
334 static int _sqlite3_open_db(char *db_file)
336 DEBUGP("opening database.\n");
337 return sqlite3_open(db_file,&dbh);
340 /* give us an opportunity to close the database down properly */
341 static void _sqlite3_fini(void)
343 DEBUGP("cleaning up db connection\n");
345 /* free up our prepared statements so we can close the db */
347 sqlite3_finalize(p_stmt);
348 DEBUGP("prepared statement finalized\n");
353 /* flush the remaining insert statements to the database. */
354 result = sqlite3_exec(dbh,"commit",NULL,NULL,NULL);
356 if (result != SQLITE_OK)
357 ulogd_log(ULOGD_ERROR,"unable to commit remaining records to db.");
360 DEBUGP("database file closed\n");
364 #define _SQLITE3_BUSY_TIMEOUT 300
366 static int _sqlite3_init(void)
368 /* have the opts parsed */
369 config_parse_file("SQLITE3", &buffer_ce);
371 if (_sqlite3_open_db(db_ce.u.string)) {
372 ulogd_log(ULOGD_ERROR, "can't open the database file\n");
376 /* set the timeout so that we don't automatically fail
377 * if the table is busy. */
378 sqlite3_busy_timeout(dbh, _SQLITE3_BUSY_TIMEOUT);
380 /* read the fieldnames to know which values to insert */
381 if (_sqlite3_get_columns(table_ce.u.string)) {
382 ulogd_log(ULOGD_ERROR, "unable to get sqlite columns\n");
386 /* initialize our buffer size and counter */
387 buffer_size = buffer_ce.u.value;
390 DEBUGP("Have a buffer size of : %d\n", buffer_size);
392 if (sqlite3_exec(dbh,"begin deferred",NULL,NULL,NULL) != SQLITE_OK)
393 ulogd_log(ULOGD_ERROR,"can't create a new transaction\n");
395 /* create and prepare the actual insert statement */
396 _sqlite3_createstmt();
401 static ulog_output_t _sqlite3_plugin = {
403 .output = &_sqlite3_output,
404 .init = &_sqlite3_init,
405 .fini = &_sqlite3_fini,
410 register_output(&_sqlite3_plugin);