This is an old revision of the document!
V 0.21.0 Schema for POPFile
-- ---------------------------------------------------------------------------------------------
--
-- 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
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
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.
