« back to all changes in this revision
Viewing changes to src/sqlite3_drv.c
- browse files at revision 1
- compare with another revision
- download diff
- download tarball
- view history from revision 1
- Committer: Bazaar Package Importer
- Author(s): Trent Lloyd
- Date: 2006-03-21 07:23:12 UTC
- Revision ID: james.westby@ubuntu.com-20060321072312-jba9a1avit4r1y6s
Tags: upstream-3.6.4
Import upstream version 3.6.4
- files added:
- doc
- m4
- man
- scripts
- src
- src/tools
- src/tools.hash_drv
- src/tools.libdb3_drv
- src/tools.libdb4_drv
- src/tools.mysql_drv
- src/tools.ora_drv
- src/tools.pgsql_drv
- src/tools.sqlite_drv
- txt
- webui
- webui/cgi-bin
- webui/cgi-bin/templates
- webui/htdocs
added
removed
src/sqlite3_drv.c
1
/* $Id: sqlite3_drv.c,v 1.12 2006/01/20 17:28:33 jonz Exp $ */
2
3
/*
4
DSPAM
5
COPYRIGHT (C) 2002-2006 DEEP LOGIC INC.
6
7
This program is free software; you can redistribute it and/or
8
modify it under the terms of the GNU General Public License
9
as published by the Free Software Foundation; version 2
10
of the License.
11
12
This program is distributed in the hope that it will be useful,
13
but WITHOUT ANY WARRANTY; without even the implied warranty of
14
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
GNU General Public License for more details.
16
17
You should have received a copy of the GNU General Public License
18
along with this program; if not, write to the Free Software
19
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
20
21
*/
22
23
#ifdef HAVE_CONFIG_H
24
#include <auto-config.h>
25
#endif
26
27
#include <string.h>
28
#include <sys/types.h>
29
#include <sys/stat.h>
30
#ifndef _WIN32
31
# include <pwd.h>
32
# include <dirent.h>
33
#endif
34
#ifdef HAVE_UNISTD_H
35
# include <unistd.h>
36
#endif
37
#include <errno.h>
38
#include <stdlib.h>
39
#include <stdio.h>
40
#include <fcntl.h>
41
#include <signal.h>
42
43
#ifdef TIME_WITH_SYS_TIME
44
# include <sys/time.h>
45
# include <time.h>
46
#else
47
# ifdef HAVE_SYS_TIME_H
48
# include <sys/time.h>
49
# else
50
# include <time.h>
51
# endif
52
#endif
53
54
#include "storage_driver.h"
55
#include "sqlite3_drv.h"
56
#include "libdspam.h"
57
#include "config.h"
58
#include "error.h"
59
#include "language.h"
60
#include "util.h"
61
#include "config_shared.h"
62
63
#ifdef _WIN32
64
# include <process.h>
65
# include "dir_win32.h"
66
#endif
67
68
int
69
dspam_init_driver (DRIVER_CTX *DTX)
70
{
71
return 0;
72
}
73
74
int
75
dspam_shutdown_driver (DRIVER_CTX *DTX)
76
{
77
return 0;
78
}
79
80
int
81
_sqlite_drv_get_spamtotals (DSPAM_CTX * CTX)
82
{
83
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
84
char query[1024];
85
char *err=NULL, **row;
86
int nrow, ncolumn;
87
int rc;
88
89
if (s->dbh == NULL)
90
{
91
LOGDEBUG ("_sqlite_drv_get_spamtotals: invalid database handle (NULL)");
92
return EINVAL;
93
}
94
95
memset(&s->control_totals, 0, sizeof(struct _ds_spam_totals));
96
memset(&CTX->totals, 0, sizeof(struct _ds_spam_totals));
97
98
snprintf (query, sizeof (query),
99
"select spam_learned, innocent_learned, "
100
"spam_misclassified, innocent_misclassified, "
101
"spam_corpusfed, innocent_corpusfed, "
102
"spam_classified, innocent_classified "
103
" from dspam_stats");
104
105
if ((sqlite3_get_table(s->dbh, query, &row, &nrow, &ncolumn, &err))!=SQLITE_OK)
106
{
107
_sqlite_drv_query_error (err, query);
108
return EFAILURE;
109
}
110
111
if (nrow>0 && row != NULL) {
112
CTX->totals.spam_learned = strtol (row[ncolumn], NULL, 0);
113
CTX->totals.innocent_learned = strtol (row[ncolumn+1], NULL, 0);
114
CTX->totals.spam_misclassified = strtol (row[ncolumn+2], NULL, 0);
115
CTX->totals.innocent_misclassified = strtol (row[ncolumn+3], NULL, 0);
116
CTX->totals.spam_corpusfed = strtol (row[ncolumn+4], NULL, 0);
117
CTX->totals.innocent_corpusfed = strtol (row[ncolumn+5], NULL, 0);
118
CTX->totals.spam_classified = strtol (row[ncolumn+6], NULL, 0);
119
CTX->totals.innocent_classified = strtol (row[ncolumn+7], NULL, 0);
120
rc = 0;
121
} else {
122
rc = EFAILURE;
123
}
124
125
sqlite3_free_table(row);
126
if ( !rc )
127
memcpy(&s->control_totals, &CTX->totals, sizeof(struct _ds_spam_totals));
128
129
return rc;
130
}
131
132
int
133
_sqlite_drv_set_spamtotals (DSPAM_CTX * CTX)
134
{
135
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
136
char query[1024];
137
char *err=NULL;
138
int result;
139
140
if (s->dbh == NULL)
141
{
142
LOGDEBUG ("_sqlite_drv_set_spamtotals: invalid database handle (NULL)");
143
return EINVAL;
144
}
145
146
if (CTX->operating_mode == DSM_CLASSIFY)
147
{
148
_sqlite_drv_get_spamtotals (CTX); /* undo changes to in memory totals */
149
return 0;
150
}
151
152
/* dspam_stat_id insures only one stats record */
153
154
if (s->control_totals.innocent_learned == 0)
155
{
156
snprintf (query, sizeof (query),
157
"insert into dspam_stats(dspam_stat_id, spam_learned, "
158
"innocent_learned, spam_misclassified, innocent_misclassified, "
159
"spam_corpusfed, innocent_corpusfed, "
160
"spam_classified, innocent_classified) "
161
"values(%d, %ld, %ld, %ld, %ld, %ld, %ld, %ld, %ld)",
162
0,
163
CTX->totals.spam_learned,
164
CTX->totals.innocent_learned,
165
CTX->totals.spam_misclassified,
166
CTX->totals.innocent_misclassified,
167
CTX->totals.spam_corpusfed,
168
CTX->totals.innocent_corpusfed,
169
CTX->totals.spam_classified,
170
CTX->totals.innocent_classified);
171
result = sqlite3_exec(s->dbh, query, NULL, NULL, &err);
172
}
173
174
if (s->control_totals.innocent_learned != 0 || result != SQLITE_OK)
175
{
176
snprintf (query, sizeof (query),
177
"update dspam_stats set spam_learned = spam_learned %s %d, "
178
"innocent_learned = innocent_learned %s %d, "
179
"spam_misclassified = spam_misclassified %s %d, "
180
"innocent_misclassified = innocent_misclassified %s %d, "
181
"spam_corpusfed = spam_corpusfed %s %d, "
182
"innocent_corpusfed = innocent_corpusfed %s %d, "
183
"spam_classified = spam_classified %s %d, "
184
"innocent_classified = innocent_classified %s %d ",
185
(CTX->totals.spam_learned >
186
s->control_totals.spam_learned) ? "+" : "-",
187
abs (CTX->totals.spam_learned -
188
s->control_totals.spam_learned),
189
(CTX->totals.innocent_learned >
190
s->control_totals.innocent_learned) ? "+" : "-",
191
abs (CTX->totals.innocent_learned -
192
s->control_totals.innocent_learned),
193
(CTX->totals.spam_misclassified >
194
s->control_totals.spam_misclassified) ? "+" : "-",
195
abs (CTX->totals.spam_misclassified -
196
s->control_totals.spam_misclassified),
197
(CTX->totals.innocent_misclassified >
198
s->control_totals.innocent_misclassified) ? "+" : "-",
199
abs (CTX->totals.innocent_misclassified -
200
s->control_totals.innocent_misclassified),
201
(CTX->totals.spam_corpusfed >
202
s->control_totals.spam_corpusfed) ? "+" : "-",
203
abs (CTX->totals.spam_corpusfed -
204
s->control_totals.spam_corpusfed),
205
(CTX->totals.innocent_corpusfed >
206
s->control_totals.innocent_corpusfed) ? "+" : "-",
207
abs (CTX->totals.innocent_corpusfed -
208
s->control_totals.innocent_corpusfed),
209
(CTX->totals.spam_classified >
210
s->control_totals.spam_classified) ? "+" : "-",
211
abs (CTX->totals.spam_classified -
212
s->control_totals.spam_classified),
213
(CTX->totals.innocent_classified >
214
s->control_totals.innocent_classified) ? "+" : "-",
215
abs (CTX->totals.innocent_classified -
216
s->control_totals.innocent_classified));
217
218
if ((sqlite3_exec(s->dbh, query, NULL, NULL, &err))!=SQLITE_OK)
219
{
220
_sqlite_drv_query_error (err, query);
221
return EFAILURE;
222
}
223
}
224
225
return 0;
226
}
227
228
int
229
_ds_getall_spamrecords (DSPAM_CTX * CTX, ds_diction_t diction)
230
{
231
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
232
buffer *query;
233
ds_term_t ds_term;
234
ds_cursor_t ds_c;
235
char scratch[1024];
236
struct _ds_spam_stat stat;
237
unsigned long long token = 0;
238
char *err=NULL, **row;
239
int nrow, ncolumn, get_one = 0, i;
240
241
if (s->dbh == NULL)
242
{
243
LOGDEBUG ("_ds_getall_spamrecords: invalid database handle (NULL)");
244
return EINVAL;
245
}
246
247
stat.spam_hits = 0;
248
stat.innocent_hits = 0;
249
250
query = buffer_create (NULL);
251
if (query == NULL)
252
{
253
LOG (LOG_CRIT, ERR_MEM_ALLOC);
254
return EUNKNOWN;
255
}
256
257
snprintf (scratch, sizeof (scratch),
258
"select token, spam_hits, innocent_hits "
259
"from dspam_token_data where token in(");
260
261
buffer_cat (query, scratch);
262
ds_c = ds_diction_cursor(diction);
263
ds_term = ds_diction_next(ds_c);
264
while (ds_term)
265
{
266
snprintf (scratch, sizeof (scratch), "'%" LLU_FMT_SPEC "'", ds_term->key);
267
buffer_cat (query, scratch);
268
ds_term->s.innocent_hits = 0;
269
ds_term->s.spam_hits = 0;
270
ds_term->s.probability = 0;
271
ds_term->s.status &= ~TST_DISK;
272
ds_term = ds_diction_next(ds_c);
273
if (ds_term)
274
buffer_cat (query, ",");
275
get_one = 1;
276
}
277
ds_diction_close(ds_c);
278
buffer_cat (query, ")");
279
280
#ifdef VERBOSE
281
LOGDEBUG ("sqlite query length: %ld\n", query->used);
282
_sqlite_drv_query_error (strdup("VERBOSE DEBUG (INFO ONLY - NOT AN ERROR)"), query->data);
283
#endif
284
285
if (!get_one)
286
return 0;
287
288
if ((sqlite3_get_table(s->dbh, query->data, &row, &nrow, &ncolumn, &err))
289
!=SQLITE_OK)
290
{
291
_sqlite_drv_query_error (err, query->data);
292
buffer_destroy(query);
293
return EFAILURE;
294
}
295
296
if (nrow < 1) {
297
sqlite3_free_table(row);
298
buffer_destroy(query);
299
return 0;
300
}
301
302
if (row == NULL)
303
return 0;
304
305
stat.probability = 0;
306
stat.status |= TST_DISK;
307
for(i=1;i<=nrow;i++) {
308
token = strtoull (row[(i*ncolumn)], NULL, 0);
309
stat.spam_hits = strtol (row[1+(i*ncolumn)], NULL, 0);
310
stat.innocent_hits = strtol (row[2+(i*ncolumn)], NULL, 0);
311
312
if (stat.spam_hits < 0)
313
stat.spam_hits = 0;
314
if (stat.innocent_hits < 0)
315
stat.innocent_hits = 0;
316
317
ds_diction_addstat(diction, token, &stat);
318
}
319
320
sqlite3_free_table(row);
321
322
ds_c = ds_diction_cursor(diction);
323
ds_term = ds_diction_next(ds_c);
324
while(ds_term && !s->control_token) {
325
if (ds_term->s.spam_hits && ds_term->s.innocent_hits) {
326
s->control_token = ds_term->key;
327
s->control_sh = ds_term->s.spam_hits;
328
s->control_ih = ds_term->s.innocent_hits;
329
}
330
ds_term = ds_diction_next(ds_c);
331
}
332
ds_diction_close(ds_c);
333
334
if (!s->control_token)
335
{
336
ds_c = ds_diction_cursor(diction);
337
ds_term = ds_diction_next(ds_c);
338
s->control_token = ds_term->key;
339
s->control_sh = ds_term->s.spam_hits;
340
s->control_ih = ds_term->s.innocent_hits;
341
ds_diction_close(ds_c);
342
}
343
344
buffer_destroy (query);
345
return 0;
346
}
347
348
int
349
_ds_setall_spamrecords (DSPAM_CTX * CTX, ds_diction_t diction)
350
{
351
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
352
struct _ds_spam_stat stat, stat2;
353
ds_term_t ds_term;
354
ds_cursor_t ds_c;
355
buffer *query;
356
char scratch[1024];
357
char *err=NULL;
358
int update_one = 0;
359
360
if (s->dbh == NULL)
361
{
362
LOGDEBUG ("_ds_setall_spamrecords: invalid database handle (NULL)");
363
return EINVAL;
364
}
365
366
if (CTX->operating_mode == DSM_CLASSIFY &&
367
(CTX->training_mode != DST_TOE ||
368
(diction->whitelist_token == 0 && (!(CTX->flags & DSF_NOISE)))))
369
return 0;
370
371
query = buffer_create (NULL);
372
if (query == NULL)
373
{
374
LOG (LOG_CRIT, ERR_MEM_ALLOC);
375
return EUNKNOWN;
376
}
377
378
if (s->control_token == 0)
379
{
380
ds_c = ds_diction_cursor(diction);
381
ds_term = ds_diction_next(ds_c);
382
if (ds_term == NULL)
383
{
384
stat.spam_hits = 0;
385
stat.innocent_hits = 0;
386
}
387
else
388
{
389
stat.spam_hits = ds_term->s.spam_hits;
390
stat.innocent_hits = ds_term->s.innocent_hits;
391
}
392
ds_diction_close(ds_c);
393
}
394
else
395
{
396
ds_diction_getstat(diction, s->control_token, &stat);
397
}
398
399
snprintf (scratch, sizeof (scratch),
400
"update dspam_token_data set last_hit = date('now'), "
401
"spam_hits = max(0, spam_hits %s %d), "
402
"innocent_hits = max(0, innocent_hits %s %d) "
403
"where token in(",
404
(stat.spam_hits > s->control_sh) ? "+" : "-",
405
abs (stat.spam_hits - s->control_sh),
406
(stat.innocent_hits > s->control_ih) ? "+" : "-",
407
abs (stat.innocent_hits - s->control_ih));
408
409
buffer_cat (query, scratch);
410
411
ds_c = ds_diction_cursor(diction);
412
ds_term = ds_diction_next(ds_c);
413
while(ds_term)
414
{
415
int wrote_this = 0;
416
if (CTX->training_mode == DST_TOE &&
417
CTX->classification == DSR_NONE &&
418
CTX->operating_mode == DSM_CLASSIFY &&
419
diction->whitelist_token != ds_term->key &&
420
(!ds_term->name || strncmp(ds_term->name, "bnr.", 4)))
421
{
422
ds_term = ds_diction_next(ds_c);
423
continue;
424
}
425
426
if (!(ds_term->s.status & TST_DIRTY)) {
427
ds_term = ds_diction_next(ds_c);
428
continue;
429
}
430
431
ds_diction_getstat(diction, ds_term->key, &stat2);
432
433
if (!(stat2.status & TST_DISK))
434
{
435
char insert[1024];
436
437
snprintf(insert, sizeof (insert),
438
"insert into dspam_token_data(token, spam_hits, "
439
"innocent_hits, last_hit) values('%" LLU_FMT_SPEC "', %ld, %ld, "
440
"date('now'))",
441
ds_term->key,
442
stat2.spam_hits > 0 ? (long) 1 : (long) 0,
443
stat2.innocent_hits > 0 ? (long) 1 : (long) 0);
444
445
if ((sqlite3_exec(s->dbh, insert, NULL, NULL, &err)) != SQLITE_OK)
446
{
447
stat2.status |= TST_DISK;
448
free(err);
449
}
450
}
451
452
if ((stat2.status & TST_DISK))
453
{
454
snprintf (scratch, sizeof (scratch), "'%" LLU_FMT_SPEC "'", ds_term->key);
455
buffer_cat (query, scratch);
456
update_one = 1;
457
wrote_this = 1;
458
ds_term->s.status |= TST_DISK;
459
}
460
ds_term = ds_diction_next(ds_c);
461
if (ds_term && wrote_this)
462
buffer_cat (query, ",");
463
}
464
ds_diction_close(ds_c);
465
466
if (query->used && query->data[strlen (query->data) - 1] == ',')
467
{
468
query->used--;
469
query->data[strlen (query->data) - 1] = 0;
470
471
}
472
473
buffer_cat (query, ")");
474
475
LOGDEBUG("Control: [%ld %ld] [%ld %ld]", s->control_sh, s->control_ih, stat.spam_hits, stat.innocent_hits);
476
477
if (update_one)
478
{
479
if ((sqlite3_exec(s->dbh, query->data, NULL, NULL, &err))!=SQLITE_OK)
480
{
481
_sqlite_drv_query_error (err, query->data);
482
buffer_destroy(query);
483
return EFAILURE;
484
}
485
}
486
487
buffer_destroy (query);
488
return 0;
489
}
490
491
int
492
_ds_get_spamrecord (DSPAM_CTX * CTX, unsigned long long token,
493
struct _ds_spam_stat *stat)
494
{
495
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
496
char query[1024];
497
char *err=NULL, **row;
498
int nrow, ncolumn;
499
500
501
if (s->dbh == NULL)
502
{
503
LOGDEBUG ("_ds_get_spamrecord: invalid database handle (NULL)");
504
return EINVAL;
505
}
506
507
snprintf (query, sizeof (query),
508
"select spam_hits, innocent_hits from dspam_token_data "
509
"where token = '%" LLU_FMT_SPEC "' ", token);
510
511
stat->probability = 0.0;
512
stat->spam_hits = 0;
513
stat->innocent_hits = 0;
514
stat->status &= ~TST_DISK;
515
516
if ((sqlite3_get_table(s->dbh, query, &row, &nrow, &ncolumn, &err))!=SQLITE_OK)
517
{
518
_sqlite_drv_query_error (err, query);
519
return EFAILURE;
520
}
521
522
if (nrow < 1)
523
sqlite3_free_table(row);
524
525
if (nrow < 1 || row == NULL)
526
return 0;
527
528
stat->spam_hits = strtol (row[0], NULL, 0);
529
stat->innocent_hits = strtol (row[1], NULL, 0);
530
stat->status |= TST_DISK;
531
sqlite3_free_table(row);
532
return 0;
533
}
534
535
int
536
_ds_set_spamrecord (DSPAM_CTX * CTX, unsigned long long token,
537
struct _ds_spam_stat *stat)
538
{
539
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
540
char query[1024];
541
char *err=NULL;
542
int result = 0;
543
544
if (s->dbh == NULL)
545
{
546
LOGDEBUG ("_ds_set_spamrecord: invalid database handle (NULL)");
547
return EINVAL;
548
}
549
550
if (CTX->operating_mode == DSM_CLASSIFY)
551
return 0;
552
553
/* It's either not on disk or the caller isn't using stat.disk */
554
if (!(stat->status & TST_DISK))
555
{
556
snprintf (query, sizeof (query),
557
"insert into dspam_token_data(token, spam_hits, "
558
"innocent_hits, last_hit)"
559
" values('%" LLU_FMT_SPEC "', %ld, %ld, date('now'))",
560
token,
561
stat->spam_hits > 0 ? stat->spam_hits : 0,
562
stat->innocent_hits > 0 ? stat->innocent_hits : 0);
563
result = sqlite3_exec(s->dbh, query, NULL, NULL, &err);
564
}
565
566
if ((stat->status & TST_DISK) || result)
567
{
568
/* insert failed; try updating instead */
569
snprintf (query, sizeof (query), "update dspam_token_data "
570
"set spam_hits = %ld, "
571
"innocent_hits = %ld "
572
"where token = %" LLD_FMT_SPEC,
573
stat->spam_hits > 0 ? stat->spam_hits : 0,
574
stat->innocent_hits > 0 ? stat->innocent_hits : 0,
575
token);
576
577
if ((sqlite3_exec(s->dbh, query, NULL, NULL, &err))!=SQLITE_OK)
578
{
579
_sqlite_drv_query_error (err, query);
580
return EFAILURE;
581
}
582
}
583
584
return 0;
585
}
586
587
int
588
_ds_init_storage (DSPAM_CTX * CTX, void *dbh)
589
{
590
struct _sqlite_drv_storage *s;
591
FILE *file;
592
char buff[1024];
593
char filename[MAX_FILENAME_LENGTH];
594
char *err=NULL;
595
struct stat st;
596
int noexist;
597
598
buff[0] = 0;
599
600
if (CTX == NULL)
601
return EINVAL;
602
603
if (CTX->flags & DSF_MERGED) {
604
LOG(LOG_ERR, ERR_DRV_NO_MERGED);
605
return EINVAL;
606
}
607
608
/* don't init if we're already initted */
609
if (CTX->storage != NULL)
610
{
611
LOGDEBUG ("_ds_init_storage: storage already initialized");
612
return EINVAL;
613
}
614
615
s = malloc (sizeof (struct _sqlite_drv_storage));
616
if (s == NULL)
617
{
618
LOG (LOG_CRIT, ERR_MEM_ALLOC);
619
return EUNKNOWN;
620
}
621
622
s->dbh = NULL;
623
s->control_token = 0;
624
s->iter_token = NULL;
625
s->iter_sig = NULL;
626
s->control_token = 0;
627
s->control_sh = 0;
628
s->control_ih = 0;
629
s->dbh_attached = (dbh) ? 1 : 0;
630
631
if (CTX->group == NULL || CTX->group[0] == 0)
632
_ds_userdir_path (filename, CTX->home, CTX->username, "sdb");
633
else
634
_ds_userdir_path (filename, CTX->home, CTX->group, "sdb");
635
636
_ds_prepare_path_for (filename);
637
638
noexist = stat(filename, &st);
639
640
if (dbh)
641
s->dbh = dbh;
642
else
643
if ((sqlite3_open(filename, &s->dbh))!=SQLITE_OK)
644
s->dbh = NULL;
645
646
if (s->dbh == NULL)
647
{
648
LOGDEBUG
649
("_ds_init_storage: sqlite3_open: unable to initialize database: %s", err); return EUNKNOWN;
650
}
651
652
/* Commit timeout of 20 minutes */
653
sqlite3_busy_timeout(s->dbh, 1000 * 60 * 20);
654
655
/* Create database objects */
656
657
if (noexist) {
658
659
sqlite3_exec(s->dbh,
660
"create table dspam_token_data (token char(20) primary key, "
661
"spam_hits int, innocent_hits int, last_hit date)",
662
NULL,
663
NULL,
664
&err);
665
666
sqlite3_exec(s->dbh,
667
"create index id_token_data_02 on dspam_token_data"
668
"(innocent_hits)",
669
NULL,
670
NULL,
671
&err);
672
673
sqlite3_exec(s->dbh,
674
"create table dspam_signature_data ("
675
"signature char(128) primary key, data blob, created_on date)",
676
NULL,
677
NULL,
678
&err);
679
680
sqlite3_exec(s->dbh,
681
"create table dspam_stats (dspam_stat_id int primary key, "
682
"spam_learned int, innocent_learned int, "
683
"spam_misclassified int, innocent_misclassified int, "
684
"spam_corpusfed int, innocent_corpusfed int, "
685
"spam_classified int, innocent_classified int)",
686
NULL,
687
NULL,
688
&err);
689
}
690
691
if (_ds_read_attribute(CTX->config->attributes, "SQLitePragma")) {
692
char pragma[1024];
693
attribute_t t = _ds_find_attribute(CTX->config->attributes, "SQLitePragma");
694
while(t != NULL) {
695
snprintf(pragma, sizeof(pragma), "PRAGMA %s", t->value);
696
if ((sqlite3_exec(s->dbh, pragma, NULL, NULL, &err))!=SQLITE_OK)
697
{
698
LOG(LOG_WARNING, "sqlite.pragma function error: %s: %s", err, pragma);
699
_sqlite_drv_query_error (err, pragma);
700
}
701
t = t->next;
702
}
703
} else {
704
snprintf(filename, MAX_FILENAME_LENGTH, "%s/sqlite.pragma", CTX->home);
705
file = fopen(filename, "r");
706
if (file != NULL) {
707
while((fgets(buff, sizeof(buff), file))!=NULL) {
708
chomp(buff);
709
if ((sqlite3_exec(s->dbh, buff, NULL, NULL, &err))!=SQLITE_OK)
710
{
711
LOG(LOG_WARNING, "sqlite.pragma function error: %s: %s", err, buff);
712
_sqlite_drv_query_error (err, buff);
713
}
714
}
715
fclose(file);
716
}
717
}
718
719
CTX->storage = s;
720
s->dir_handles = nt_create (NT_INDEX);
721
722
s->control_token = 0;
723
s->control_ih = 0;
724
s->control_sh = 0;
725
726
/* get spam totals on successful init */
727
if (CTX->username != NULL)
728
{
729
if (_sqlite_drv_get_spamtotals (CTX))
730
{
731
LOGDEBUG ("unable to load totals. using zero values.");
732
}
733
}
734
else
735
{
736
memset (&CTX->totals, 0, sizeof (struct _ds_spam_totals));
737
memset (&s->control_totals, 0, sizeof (struct _ds_spam_totals));
738
}
739
740
return 0;
741
}
742
743
int
744
_ds_shutdown_storage (DSPAM_CTX * CTX)
745
{
746
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
747
struct nt_node *node_nt;
748
struct nt_c c_nt;
749
750
if (s->dbh == NULL)
751
{
752
LOGDEBUG ("_ds_shutdown_storage: invalid database handle (NULL)");
753
return EINVAL;
754
}
755
756
node_nt = c_nt_first (s->dir_handles, &c_nt);
757
while (node_nt != NULL)
758
{
759
DIR *dir;
760
dir = (DIR *) node_nt->ptr;
761
closedir (dir);
762
node_nt = c_nt_next (s->dir_handles, &c_nt);
763
}
764
765
nt_destroy (s->dir_handles);
766
767
768
/* Store spam totals on shutdown */
769
if (CTX->username != NULL && CTX->operating_mode != DSM_CLASSIFY)
770
{
771
_sqlite_drv_set_spamtotals (CTX);
772
}
773
774
if (!s->dbh_attached)
775
sqlite3_close(s->dbh);
776
777
s->dbh = NULL;
778
779
free(s);
780
CTX->storage = NULL;
781
782
return 0;
783
}
784
785
int
786
_ds_create_signature_id (DSPAM_CTX * CTX, char *buf, size_t len)
787
{
788
char session[64];
789
char digit[6];
790
int pid, j;
791
792
pid = getpid ();
793
snprintf (session, sizeof (session), "%8lx%d", (long) time (NULL), pid);
794
795
for (j = 0; j < 2; j++)
796
{
797
snprintf (digit, 6, "%d", rand ());
798
strlcat (session, digit, 64);
799
}
800
801
strlcpy (buf, session, len);
802
return 0;
803
}
804
805
int
806
_ds_get_signature (DSPAM_CTX * CTX, struct _ds_spam_signature *SIG,
807
const char *signature)
808
{
809
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
810
char query[128];
811
char *err=NULL;
812
const char *query_tail;
813
sqlite3_stmt *stmt;
814
815
if (s->dbh == NULL)
816
{
817
LOGDEBUG ("_ds_get_signature: invalid database handle (NULL)");
818
return EINVAL;
819
}
820
821
snprintf (query, sizeof (query),
822
"select data from dspam_signature_data where signature = \"%s\"",
823
signature);
824
825
if ((sqlite3_prepare(s->dbh, query, -1, &stmt, &query_tail))
826
!=SQLITE_OK)
827
{
828
_sqlite_drv_query_error (err, query);
829
return EFAILURE;
830
}
831
832
if ((sqlite3_step(stmt))!=SQLITE_ROW) {
833
sqlite3_finalize(stmt);
834
return EFAILURE;
835
}
836
837
SIG->length = sqlite3_column_bytes(stmt, 0);
838
SIG->data = malloc(SIG->length);
839
if (SIG->data == NULL) {
840
sqlite3_finalize(stmt);
841
LOG(LOG_CRIT, ERR_MEM_ALLOC);
842
return EUNKNOWN;
843
}
844
845
memcpy(SIG->data, sqlite3_column_blob(stmt, 0), SIG->length);
846
847
if ((sqlite3_finalize(stmt)!=SQLITE_OK))
848
LOGDEBUG("sqlite3_finalize() failed: %s", strerror(errno));
849
850
return 0;
851
}
852
853
int
854
_ds_set_signature (DSPAM_CTX * CTX, struct _ds_spam_signature *SIG,
855
const char *signature)
856
{
857
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
858
char *mem;
859
char scratch[1024];
860
char *err=NULL;
861
const char *query_tail=NULL;
862
sqlite3_stmt *stmt;
863
int r;
864
865
if (s->dbh == NULL)
866
{
867
LOGDEBUG ("_ds_set_signature; invalid database handle (NULL)");
868
return EINVAL;
869
}
870
871
mem = calloc (1, 2 + (257*SIG->length)/254);
872
if (mem == NULL)
873
{
874
LOG (LOG_CRIT, ERR_MEM_ALLOC);
875
return EUNKNOWN;
876
}
877
878
snprintf (scratch, sizeof (scratch),
879
"insert into dspam_signature_data(signature, created_on, data) "
880
"values(\"%s\", date('now'), ?)", signature);
881
882
if ((r = sqlite3_prepare(s->dbh, scratch, -1, &stmt, &query_tail))
883
!=SQLITE_OK)
884
{
885
_sqlite_drv_query_error ("sqlite3_prepare() failed", scratch);
886
return EFAILURE;
887
}
888
889
sqlite3_bind_blob(stmt, 1, SIG->data, SIG->length, SQLITE_STATIC);
890
891
if ((sqlite3_step(stmt))!=SQLITE_DONE) {
892
_sqlite_drv_query_error (err, scratch);
893
return EFAILURE;
894
}
895
896
sqlite3_finalize(stmt);
897
898
free (mem);
899
return 0;
900
}
901
902
int
903
_ds_delete_signature (DSPAM_CTX * CTX, const char *signature)
904
{
905
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
906
char query[128];
907
char *err=NULL;
908
909
if (s->dbh == NULL)
910
{
911
LOGDEBUG ("_ds_delete_signature: invalid database handle (NULL)");
912
return EINVAL;
913
}
914
915
snprintf (query, sizeof (query),
916
"delete from dspam_signature_data where signature = \"%s\"",
917
signature);
918
919
if ((sqlite3_exec(s->dbh, query, NULL, NULL, &err))!=SQLITE_OK)
920
{
921
_sqlite_drv_query_error (err, query);
922
return EFAILURE;
923
}
924
925
return 0;
926
}
927
928
int
929
_ds_verify_signature (DSPAM_CTX * CTX, const char *signature)
930
{
931
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
932
char query[128];
933
char *err=NULL, **row;
934
int nrow, ncolumn;
935
936
if (s->dbh == NULL)
937
{
938
LOGDEBUG ("_ds_verify_signature: invalid database handle (NULL)");
939
return EINVAL;
940
}
941
942
snprintf (query, sizeof (query),
943
"select signature from dspam_signature_data where signature = \"%s\"",
944
signature);
945
946
if ((sqlite3_get_table(s->dbh, query, &row, &nrow, &ncolumn, &err))!=SQLITE_OK) {
947
_sqlite_drv_query_error (err, query);
948
return EFAILURE;
949
}
950
951
sqlite3_free_table(row);
952
953
if (nrow<1) {
954
return -1;
955
}
956
957
return 0;
958
}
959
960
char *
961
_ds_get_nextuser (DSPAM_CTX * CTX)
962
{
963
static char user[MAX_FILENAME_LENGTH];
964
static char path[MAX_FILENAME_LENGTH];
965
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
966
struct nt_node *node_nt, *prev;
967
struct nt_c c_nt;
968
char *x = NULL, *y;
969
DIR *dir = NULL;
970
971
struct dirent *entry;
972
973
if (s->dir_handles->items == 0)
974
{
975
char filename[MAX_FILENAME_LENGTH];
976
snprintf(filename, MAX_FILENAME_LENGTH, "%s/data", CTX->home);
977
dir = opendir (filename);
978
if (dir == NULL)
979
{
980
LOG (LOG_WARNING,
981
"unable to open directory '%s' for reading: %s",
982
CTX->home, strerror (errno));
983
return NULL;
984
}
985
986
nt_add (s->dir_handles, (void *) dir);
987
strlcpy (path, filename, sizeof (path));
988
}
989
else
990
{
991
node_nt = c_nt_first (s->dir_handles, &c_nt);
992
while (node_nt != NULL)
993
{
994
if (node_nt->next == NULL)
995
dir = (DIR *) node_nt->ptr;
996
node_nt = c_nt_next (s->dir_handles, &c_nt);
997
}
998
}
999
1000
while ((entry = readdir (dir)) != NULL)
1001
{
1002
struct stat st;
1003
char filename[MAX_FILENAME_LENGTH];
1004
snprintf (filename, sizeof (filename), "%s/%s", path, entry->d_name);
1005
1006
if (!strcmp (entry->d_name, ".") || !strcmp (entry->d_name, ".."))
1007
continue;
1008
1009
if (stat (filename, &st)) {
1010
continue;
1011
}
1012
1013
/* push a new directory */
1014
if (st.st_mode & S_IFDIR)
1015
{
1016
DIR *ndir;
1017
1018
ndir = opendir (filename);
1019
if (ndir == NULL)
1020
continue;
1021
strlcat (path, "/", sizeof (path));
1022
strlcat (path, entry->d_name, sizeof (path));
1023
nt_add (s->dir_handles, (void *) ndir);
1024
return _ds_get_nextuser (CTX);
1025
}
1026
else if (!strncmp
1027
(entry->d_name + strlen (entry->d_name) - 4, ".sdb", 4))
1028
{
1029
strlcpy (user, entry->d_name, sizeof (user));
1030
user[strlen (user) - 4] = 0;
1031
return user;
1032
}
1033
}
1034
1035
/* pop current directory */
1036
y = strchr (path, '/');
1037
while (y != NULL)
1038
{
1039
x = y;
1040
y = strchr (x + 1, '/');
1041
}
1042
if (x)
1043
x[0] = 0;
1044
1045
/* pop directory handle from list */
1046
node_nt = c_nt_first (s->dir_handles, &c_nt);
1047
prev = NULL;
1048
while (node_nt != NULL)
1049
{
1050
if (node_nt->next == NULL)
1051
{
1052
dir = (DIR *) node_nt->ptr;
1053
closedir (dir);
1054
if (prev != NULL) {
1055
prev->next = NULL;
1056
s->dir_handles->insert = NULL;
1057
}
1058
else
1059
s->dir_handles->first = NULL;
1060
free (node_nt);
1061
s->dir_handles->items--;
1062
prev = node_nt;
1063
break;
1064
}
1065
prev = node_nt;
1066
node_nt = c_nt_next (s->dir_handles, &c_nt);
1067
}
1068
if (s->dir_handles->items > 0)
1069
return _ds_get_nextuser (CTX);
1070
1071
/* done */
1072
1073
user[0] = 0;
1074
return NULL;
1075
}
1076
1077
struct _ds_storage_record *
1078
_ds_get_nexttoken (DSPAM_CTX * CTX)
1079
{
1080
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
1081
struct _ds_storage_record *st;
1082
char query[128];
1083
char *err=NULL;
1084
const char *query_tail=NULL;
1085
int x;
1086
1087
if (s->dbh == NULL)
1088
{
1089
LOGDEBUG ("_ds_get_nexttoken: invalid database handle (NULL)");
1090
return NULL;
1091
}
1092
1093
st = calloc (1, sizeof (struct _ds_storage_record));
1094
if (st == NULL)
1095
{
1096
LOG (LOG_CRIT, ERR_MEM_ALLOC);
1097
return NULL;
1098
}
1099
1100
if (s->iter_token == NULL)
1101
{
1102
snprintf (query, sizeof (query),
1103
"select token, spam_hits, innocent_hits, strftime('%%s', "
1104
"last_hit) from dspam_token_data");
1105
1106
if ((sqlite3_prepare(s->dbh, query, -1, &s->iter_token, &query_tail))
1107
!=SQLITE_OK)
1108
{
1109
_sqlite_drv_query_error (err, query);
1110
free(st);
1111
return NULL;
1112
}
1113
}
1114
1115
if ((x = sqlite3_step(s->iter_token)) !=SQLITE_ROW) {
1116
if (x != SQLITE_DONE) {
1117
_sqlite_drv_query_error (err, query);
1118
s->iter_token = NULL;
1119
free(st);
1120
return NULL;
1121
}
1122
sqlite3_finalize((struct sqlite3_stmt *) s->iter_token);
1123
s->iter_token = NULL;
1124
free(st);
1125
return NULL;
1126
}
1127
1128
st->token = strtoull (sqlite3_column_text(s->iter_token, 0), NULL, 0);
1129
st->spam_hits = strtol (sqlite3_column_text(s->iter_token, 1), NULL, 0);
1130
st->innocent_hits = strtol (sqlite3_column_text(s->iter_token, 2), NULL, 0);
1131
st->last_hit = (time_t) strtol (sqlite3_column_text(s->iter_token, 3), NULL, 0);
1132
1133
return st;
1134
}
1135
1136
struct _ds_storage_signature *
1137
_ds_get_nextsignature (DSPAM_CTX * CTX)
1138
{
1139
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
1140
struct _ds_storage_signature *st;
1141
unsigned long length;
1142
char query[128];
1143
char *mem;
1144
char *err=NULL;
1145
const char *query_tail=NULL;
1146
int x;
1147
1148
if (s->dbh == NULL)
1149
{
1150
LOGDEBUG ("_ds_get_nextsignature: invalid database handle (NULL)");
1151
return NULL;
1152
}
1153
1154
st = calloc (1, sizeof (struct _ds_storage_signature));
1155
if (st == NULL)
1156
{
1157
LOG (LOG_CRIT, ERR_MEM_ALLOC);
1158
return NULL;
1159
}
1160
1161
if (s->iter_sig == NULL)
1162
{
1163
snprintf (query, sizeof (query),
1164
"select data, signature, strftime('%%s', created_on) "
1165
"from dspam_signature_data");
1166
1167
if ((sqlite3_prepare(s->dbh, query, -1, &s->iter_sig, &query_tail))
1168
!=SQLITE_OK)
1169
{
1170
_sqlite_drv_query_error (err, query);
1171
free(st);
1172
return NULL;
1173
}
1174
}
1175
1176
if ((x = sqlite3_step(s->iter_sig)) !=SQLITE_ROW) {
1177
if (x != SQLITE_DONE) {
1178
_sqlite_drv_query_error (err, query);
1179
s->iter_sig = NULL;
1180
free(st);
1181
return NULL;
1182
}
1183
sqlite3_finalize((struct sqlite3_stmt *) s->iter_sig);
1184
s->iter_sig = NULL;
1185
free(st);
1186
return NULL;
1187
}
1188
1189
length = sqlite3_column_bytes(s->iter_sig, 0);
1190
mem = malloc (length);
1191
if (mem == NULL)
1192
{
1193
LOG (LOG_CRIT, ERR_MEM_ALLOC);
1194
sqlite3_finalize(s->iter_sig);
1195
s->iter_sig = NULL;
1196
free(st);
1197
return NULL;
1198
}
1199
1200
memcpy(mem, sqlite3_column_blob(s->iter_sig, 0), length);
1201
1202
st->data = mem;
1203
strlcpy(st->signature, sqlite3_column_text(s->iter_sig, 1), sizeof(st->signature));
1204
st->length = length;
1205
st->created_on = (time_t) strtol(sqlite3_column_text(s->iter_sig, 2), NULL, 0);
1206
1207
return st;
1208
}
1209
1210
void
1211
_sqlite_drv_query_error (const char *error, const char *query)
1212
{
1213
FILE *file;
1214
time_t tm = time (NULL);
1215
char ct[128];
1216
char fn[MAX_FILENAME_LENGTH];
1217
1218
LOG (LOG_WARNING, "query error: %s: see sql.errors for more details",
1219
error);
1220
1221
snprintf (fn, sizeof (fn), "%s/sql.errors", LOGDIR);
1222
1223
snprintf (ct, sizeof (ct), "%s", ctime (&tm));
1224
chomp (ct);
1225
1226
file = fopen (fn, "a");
1227
1228
if (file == NULL)
1229
{
1230
LOG(LOG_ERR, ERR_IO_FILE_WRITE, fn, strerror (errno));
1231
}
1232
else
1233
{
1234
fprintf (file, "[%s] %d: %s: %s\n", ct, (int) getpid (), error, query);
1235
fclose (file);
1236
}
1237
1238
free((char *)error);
1239
return;
1240
}
1241
1242
int
1243
_ds_del_spamrecord (DSPAM_CTX * CTX, unsigned long long token)
1244
{
1245
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
1246
char query[128];
1247
char *err=NULL;
1248
1249
if (s->dbh == NULL)
1250
{
1251
LOGDEBUG ("_ds_delete_signature: invalid database handle (NULL)");
1252
return EINVAL;
1253
}
1254
1255
snprintf (query, sizeof (query),
1256
"delete from dspam_token_data where token = \"%" LLU_FMT_SPEC "\"",
1257
token);
1258
1259
if ((sqlite3_exec(s->dbh, query, NULL, NULL, &err))!=SQLITE_OK)
1260
{
1261
_sqlite_drv_query_error (err, query);
1262
return EFAILURE;
1263
}
1264
1265
return 0;
1266
}
1267
1268
int _ds_delall_spamrecords (DSPAM_CTX * CTX, ds_diction_t diction)
1269
{
1270
struct _sqlite_drv_storage *s = (struct _sqlite_drv_storage *) CTX->storage;
1271
ds_term_t ds_term;
1272
ds_cursor_t ds_c;
1273
buffer *query;
1274
char *err=NULL;
1275
char scratch[1024];
1276
char queryhead[1024];
1277
int writes = 0;
1278
1279
if (diction->items < 1)
1280
return 0;
1281
1282
if (s->dbh == NULL)
1283
{
1284
LOGDEBUG ("_ds_delall_spamrecords: invalid database handle (NULL)");
1285
return EINVAL;
1286
}
1287
1288
query = buffer_create (NULL);
1289
if (query == NULL)
1290
{
1291
LOG (LOG_CRIT, ERR_MEM_ALLOC);
1292
return EUNKNOWN;
1293
}
1294
1295
snprintf (queryhead, sizeof(queryhead),
1296
"delete from dspam_token_data "
1297
"where token in(");
1298
1299
buffer_cat (query, queryhead);
1300
1301
ds_c = ds_diction_cursor(diction);
1302
ds_term = ds_diction_next(ds_c);
1303
while (ds_term)
1304
{
1305
snprintf (scratch, sizeof (scratch), "'%" LLU_FMT_SPEC "'", ds_term->key);
1306
buffer_cat (query, scratch);
1307
ds_term = ds_diction_next(ds_c);
1308
1309
if (writes > 2500 || ds_term == NULL) {
1310
buffer_cat (query, ")");
1311
1312
if ((sqlite3_exec(s->dbh, query->data, NULL, NULL, &err))!=SQLITE_OK)
1313
{
1314
_sqlite_drv_query_error (err, query->data);
1315
buffer_destroy(query);
1316
return EFAILURE;
1317
}
1318
1319
buffer_copy(query, queryhead);
1320
writes = 0;
1321
1322
} else {
1323
writes++;
1324
if (ds_term)
1325
buffer_cat (query, ",");
1326
}
1327
}
1328
ds_diction_close(ds_c);
1329
1330
if (writes) {
1331
buffer_cat (query, ")");
1332
1333
if ((sqlite3_exec(s->dbh, query->data, NULL, NULL, &err))!=SQLITE_OK)
1334
{
1335
_sqlite_drv_query_error (err, query->data);
1336
buffer_destroy(query);
1337
return EFAILURE;
1338
}
1339
}
1340
1341
buffer_destroy (query);
1342
return 0;
1343
}
1344
1345
void *_ds_connect (DSPAM_CTX *CTX)
1346
{
1347
return NULL;
1348
}
1349
1350
1351
/* Preference Stubs for Flat-File */
1352
1353
agent_pref_t _ds_pref_load(config_t config, const char *user,
1354
const char *home, void *dbh)
1355
{
1356
return _ds_ff_pref_load(config, user, home, dbh);
1357
}
1358
1359
int _ds_pref_set(config_t config, const char *user, const char *home,
1360
const char *attrib, const char *value, void *dbh)
1361
{
1362
return _ds_ff_pref_set(config, user, home, attrib, value, dbh);
1363
}
1364
1365
int _ds_pref_del(config_t config, const char *user, const char *home,
1366
const char *attrib, void *dbh)
1367
{
1368
return _ds_pref_del(config, user, home, attrib, dbh);
1369
}
1370
Loggerhead is a web-based interface for Breezy
Version: 2.0.1