1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
|
-- POPFILE SCHEMA 3
-- ---------------------------------------------------------------------------------------------
--
-- popfile.schema - POPFile's database schema
--
-- Copyright (c) 2003-2004 John Graham-Cumming
--
-- This file is part of POPFile
--
-- POPFile is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- POPFile is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with POPFile; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
--
-- ---------------------------------------------------------------------------------------------
-- An ASCII ERD (you might like to find the 'users' table first and work from there)
--
-- +---------------+ +-----------------+
-- | user_template | | bucket_template |
-- +---------------+ +-----------------+
-- | id |-----+ | id |---+
-- | name | | | name | |
-- | def | | | def | |
-- +---------------+ | +-----------------+ |
-- | |
-- +---------------+ | +---------------+ |
-- | user_params | | | bucket_params | |
-- +---------------+ | +---------------+ |
-- | id | | | id | |
-- +---| userid | | +---| bucketid | |
-- | | utid |-----+ | | btid |---+
-- | | val | | | val |
-- | +---------------+ | +---------------+
-- | | +----------+
-- | | | matrix | +-------+
-- | | +---------+ +----------+ | words |
-- | +----------+ | | buckets | | id | +-------+
-- | | users | | +---------+ | wordid |------| id |
-- | +----------+ /--+---| id |=====---| bucketid | | word |
-- +----==| id |-----(-------| userid | \ | times | +-------+
-- / | name | | | name | | | lastseen |
-- | | password | | | pseudo | | +----------+
-- | +----------+ | +---------+ |
-- | | |
-- | | +-----------+ |
-- | | | magnets | |
-- | +------------+ | +-----------+ | +--------------+
-- | | history | | +--| id | | | magnet_types |
-- | +------------+ | | | bucketid |--+ +--------------+
-- | | id | | | | mtid |--------| id |
-- +---| userid | | | | val | | mtype |
-- | hdr_from | | | | seq | | header |
-- | hdr_to | | | +-----------+ +--------------+
-- | hdr_cc | | |
-- | hdr_subject| | |
-- | bucketid |--+ |
-- | usedtobe |--/ |
-- | magnetid |--------+
-- | hdr_date |
-- | inserted |
-- | hash |
-- | committed |
-- | size |
-- +------------+
--
-- TABLE DEFINITIONS
-- ---------------------------------------------------------------------------------------------
--
-- popfile - data about the database
--
-- ---------------------------------------------------------------------------------------------
create table popfile ( id integer primary key,
version integer -- version number of this schema
);
-- ---------------------------------------------------------------------------------------------
--
-- users - the table that stores the names and password of POPFile users
--
-- v0.21.0: With this release POPFile does not have an internal concept of
-- 'user' and hence this table consists of a single user called 'admin', once
-- we do the full multi-user release of POPFile this table will be used and
-- there will be suitable APIs and UI to modify it
--
-- ---------------------------------------------------------------------------------------------
create table users ( id integer primary key, -- unique ID for this user
name varchar(255), -- textual name of the user
password varchar(255), -- user's password
unique (name) -- the user name must be unique
);
-- ---------------------------------------------------------------------------------------------
--
-- buckets - the table that stores the name of POPFile buckets and relates
-- them to users.
--
-- Note: A single user may have multiple buckets, but a single bucket only has
-- one user. Hence there is a many-to-one relationship from buckets to users.
--
-- ---------------------------------------------------------------------------------------------
create table buckets( id integer primary key, -- unique ID for this bucket
userid integer, -- corresponds to an entry in
-- the users table
name varchar(255), -- the name of the bucket
pseudo int, -- 1 if this is a pseudobucket
-- (i.e. one POPFile uses internally)
unique (userid,name) -- a user can't have two buckets
-- with the same name
);
-- ---------------------------------------------------------------------------------------------
--
-- words - the table that creates a unique ID for a word.
--
-- Words and buckets come together in the matrix table to form the corpus of words for
-- each user.
--
-- ---------------------------------------------------------------------------------------------
create table words( id integer primary key, -- unique ID for this word
word varchar(255), -- the word
unique (word) -- each word is unique
);
-- ---------------------------------------------------------------------------------------------
--
-- matrix - the corpus that consists of buckets filled with words. Each word
-- in each bucket has a word count.
--
-- ---------------------------------------------------------------------------------------------
create table matrix( id integer primary key, -- unique ID for this entry
wordid integer, -- an ID in the words table
bucketid integer, -- an ID in the buckets table
times integer, -- number of times the word has
-- been seen
lastseen date, -- last time the record was read
-- or written
unique (wordid, bucketid) -- each word appears once in a bucket
);
-- ---------------------------------------------------------------------------------------------
--
-- user_template - the table of possible parameters that a user can have.
--
-- For example in the users table there is just an password associated with
-- the user. This table provides a flexible way of creating per user
-- parameters. It stores the definition of the parameters and the the
-- user_params table relates an actual user with each parameter
--
-- ---------------------------------------------------------------------------------------------
create table user_template( id integer primary key, -- unique ID for this entry
name varchar(255), -- the name of the
-- parameter
def varchar(255), -- the default value for
-- the parameter
unique (name) -- parameter name's are unique
);
-- ---------------------------------------------------------------------------------------------
--
-- user_params - the table that relates users with user parameters (as defined
-- in user_template) and specific values.
--
-- ---------------------------------------------------------------------------------------------
create table user_params( id integer primary key, -- unique ID for this
-- entry
userid integer, -- a user
utid integer, -- points to an entry in
-- user_template
val varchar(255), -- value for the
-- parameter
unique (userid, utid) -- each user has just one
-- instance of each parameter
);
-- ---------------------------------------------------------------------------------------------
--
-- bucket_template - the table of possible parameters that a bucket can have.
--
-- See commentary for user_template for an explanation of the philosophy
--
-- ---------------------------------------------------------------------------------------------
create table bucket_template( id integer primary key, -- unique ID for this entry
name varchar(255), -- the name of the
-- parameter
def varchar(255), -- the default value for
-- the parameter
unique (name) -- parameter name's are unique
);
-- ---------------------------------------------------------------------------------------------
--
-- bucket_params - the table that relates buckets with bucket parameters (as defined
-- in bucket_template) and specific values.
--
-- ---------------------------------------------------------------------------------------------
create table bucket_params( id integer primary key, -- unique ID for this
-- entry
bucketid integer, -- a bucket
btid integer, -- points to an entry in
-- bucket_template
val varchar(255), -- value for the
-- parameter
unique (bucketid, btid) -- each bucket has just one
-- instance of each parameter
);
-- ---------------------------------------------------------------------------------------------
--
-- magnet_types - the types of possible magnet and their associated header
--
-- ---------------------------------------------------------------------------------------------
create table magnet_types( id integer primary key, -- unique ID for this entry
mtype varchar(255), -- the type of magnet
-- (e.g. from)
header varchar(255), -- the header (e.g. From)
unique (mtype) -- types are unique
);
-- ---------------------------------------------------------------------------------------------
--
-- magnets - relates specific buckets to specific magnet types with actual
-- magnet values
--
-- ---------------------------------------------------------------------------------------------
create table magnets( id integer primary key, -- unique ID for this entry
bucketid integer, -- a bucket
mtid integer, -- the magnet type
val varchar(255), -- value for the magnet
comment varchar(255), -- user defined comment
seq integer -- used to set the order of magnets
);
-- ---------------------------------------------------------------------------------------------
--
-- history - this table contains the items in the POPFile history that
-- are managed by POPFile::History
--
-- ---------------------------------------------------------------------------------------------
create table history( id integer primary key, -- unique ID for this entry
userid integer, -- which user owns this
committed integer, -- 1 if this item has been committed
hdr_from varchar(255), -- The From: header
hdr_to varchar(255), -- The To: header
hdr_cc varchar(255), -- The Cc: header
hdr_subject varchar(255), -- The Subject: header
hdr_date date, -- The Date: header
hash varchar(255), -- MD5 message hash
inserted date, -- When this was added
bucketid integer, -- Current classification
usedtobe integer, -- Previous classification
magnetid integer, -- If classified with magnet
sort_from varchar(255), -- The From: header
sort_to varchar(255), -- The To: header
sort_cc varchar(255), -- The Cc: header
size integer -- Size of the message in Bytes
);
-- MySQL SPECIFIC
-- ---------------------------------------------------------------------------------------------
--
-- NOTE: The following alter table statements are required by MySQL in order
-- to get the ID fields to auto_increment on inserts.
--
-- ---------------------------------------------------------------------------------------------
alter table buckets modify id int(11) auto_increment;
alter table bucket_params modify id int(11) auto_increment;
alter table bucket_template modify id int(11) auto_increment;
alter table magnets modify id int(11) auto_increment;
alter table magnet_types modify id int(11) auto_increment;
alter table matrix modify id int(11) auto_increment;
alter table user_params modify id int(11) auto_increment;
alter table user_template modify id int(11) auto_increment;
alter table users modify id int(11) auto_increment;
alter table words modify id int(11) auto_increment;
alter table history modify id int(11) auto_increment;
alter table popfile modify id int(11) auto_increment;
-- MySQL treats char fields as case insensitive for searches, in order to have
-- the same behavior as SQLite (case sensitive searches) we alter the word.word
-- field to binary, that will trick MySQL into treating it the way we want.
alter table words modify word binary(255);
-- MySQL enforces types, SQLite uses the concept of manifest typing, where
-- the type of a value is associated with the value itself, not the column that
-- it is stored in. POPFile has two date fields in history where POPFile
-- is actually storing the unix time not a date. MySQL interprets the
-- unix time as a date of 0000-00-00, whereas SQLite simply stores the
-- unix time integer. The follow alter table statements redefine those
-- date fields as integer for MySQL so the correct behavior is obtained
-- for POPFile's use of the fields.
alter table history modify hdr_date int(11);
alter table history modify inserted int(11);
-- TRIGGERS
-- ---------------------------------------------------------------------------------------------
--
-- delete_bucket - if a/some bucket(s) are delete then this trigger ensures
-- that entries the hang off the bucket table are also deleted
--
-- It deletes the related entries in the 'matrix', 'bucket_params' and
-- 'magnets' tables.
--
-- ---------------------------------------------------------------------------------------------
create trigger delete_bucket delete on buckets
begin
delete from matrix where bucketid = old.id;
delete from history where bucketid = old.id;
delete from magnets where bucketid = old.id;
delete from bucket_params where bucketid = old.id;
end;
-- ---------------------------------------------------------------------------------------------
--
-- delete_user - deletes entries that are related to a user
--
-- It deletes the related entries in the 'matrix' and 'user_params'.
--
-- ---------------------------------------------------------------------------------------------
create trigger delete_user delete on users
begin
delete from history where userid = old.id;
delete from buckets where userid = old.id;
delete from user_params where userid = old.id;
end;
-- ---------------------------------------------------------------------------------------------
--
-- delete_magnet_type - handles the removal of a magnet type (this should be a
-- very rare thing)
--
-- ---------------------------------------------------------------------------------------------
create trigger delete_magnet_type delete on magnet_types
begin
delete from magnets where mtid = old.id;
end;
-- ---------------------------------------------------------------------------------------------
--
-- delete_user_template - handles the removal of a type of user parameters
--
-- ---------------------------------------------------------------------------------------------
create trigger delete_user_template delete on user_template
begin
delete from user_params where utid = old.id;
end;
-- ---------------------------------------------------------------------------------------------
--
-- delete_bucket_template - handles the removal of a type of bucket parameters
--
-- ---------------------------------------------------------------------------------------------
create trigger delete_bucket_template delete on bucket_template
begin
delete from bucket_params where btid = old.id;
end;
-- Default data
-- This is schema version 3
insert into popfile ( version ) values ( 3 );
-- There's always a user called 'admin'
insert into users ( name, password ) values ( 'admin', 'e11f180f4a31d8caface8e62994abfaf' );
insert into magnets ( id, bucketid, mtid, val, comment, seq ) values ( 0, 0, 0, '', '', 0 );
-- These are the possible parameters for a bucket
--
-- subject 1 if should do subject modification for message classified to this bucket
-- xtc 1 if should add X-Text-Classification header
-- xpl 1 if should add X-POPFile-Link header
-- fncount Number of messages that were incorrectly classified, and meant to go into
-- this bucket but did not
-- fpcount Number of messages that were incorrectly classified into this bucket
-- quarantine 1 if should quaratine (i.e. RFC822 wrap) messages in this bucket
-- count Total number of messages classified into this bucket
-- color The color used for this bucket in the UI
insert into bucket_template ( name, def ) values ( 'subject', '1' );
insert into bucket_template ( name, def ) values ( 'xtc', '1' );
insert into bucket_template ( name, def ) values ( 'xpl', '1' );
insert into bucket_template ( name, def ) values ( 'fncount', '0' );
insert into bucket_template ( name, def ) values ( 'fpcount', '0' );
insert into bucket_template ( name, def ) values ( 'quarantine', '0' );
insert into bucket_template ( name, def ) values ( 'count', '0' );
insert into bucket_template ( name, def ) values ( 'color', 'black' );
-- The possible magnet types
insert into magnet_types ( mtype, header ) values ( 'from', 'From' );
insert into magnet_types ( mtype, header ) values ( 'to', 'To' );
insert into magnet_types ( mtype, header ) values ( 'subject', 'Subject' );
insert into magnet_types ( mtype, header ) values ( 'cc', 'Cc' );
-- There's always a bucket called 'unclassified' which is where POPFile puts
-- messages that it isn't sure about.
insert into buckets ( name, pseudo, userid ) values ( 'unclassified', 1, 1 );
-- MySQL insists that auto_increment fields start at 1. POPFile requires
-- a special magnet record with an id of 0 in order to work properly.
-- The following SQL statement will fix the inserted special record
-- on MySQL installs so the id is 0, the statement should do nothing
-- on SQLite installs since it will not satisfy the where clause.
update magnets set id = 0 where id = 1 and (bucketid = 0 and mtid = 0);
-- END
|
