This is an old revision of the document!
-- --------------------------------------------------------------------------------------------- -- -- popfile.schema - POPFile's database schema -- -- Copyright (c) 2001-2003 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 | -- | frm | | | | seq | | header | -- | too | | | +-----------+ +--------------+ -- | cc | | | -- | subject | | | -- | bucketid |--+ | -- | usedtobe |--/ | -- | magnetid |--------+ -- | message | -- +----------+ -- -- TABLE DEFINITIONS -- --------------------------------------------------------------------------------------------- -- -- 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 <code> 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 int -- used to set the order of magnets );
– 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_param 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 words modify word binary(255);
– 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 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 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 magnet 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
– There's always a user called 'admin'
insert into users ( name, password ) values ( 'admin', 'e11f180f4a31d8caface8e62994abfaf' );
– 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 );
– END
</code>
Should you find anything in the documentation that is incomplete, unclear, outdated or just plain wrong, please let us know and leave a note in the Documentation Forum.