This shows you the differences between two versions of the page.
Next revision | Previous revisionLast revisionBoth sides next revision | ||
sqlschema [2007/01/24 09:39] – amatubu | sqlschema [2010/09/27 05:21] – amatubu | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== V 0.21.0 Schema for POPFile ===== | + | ===== V 1.1.1 Schema for POPFile ===== |
< | < | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- POPFILE SCHEMA 3 |
+ | -- --------------------------------------------------------------------------- | ||
-- | -- | ||
-- popfile.schema - POPFile' | -- popfile.schema - POPFile' | ||
-- | -- | ||
- | -- Copyright (c) 2001-2003 John Graham-Cumming | + | -- Copyright (c) 2001-2010 John Graham-Cumming |
-- | -- | ||
-- This file is part of POPFile | -- This file is part of POPFile | ||
-- | -- | ||
- | -- | + | -- |
- | -- it under the terms of the GNU General Public License as published by | + | -- under the terms of version 2 of the GNU General Public License as |
- | -- the Free Software Foundation; either version 2 of the License, or | + | -- published by the Free Software Foundation. |
- | -- (at your option) any later version. | + | |
-- | -- | ||
-- | -- | ||
Line 25: | Line 25: | ||
-- | -- | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
- | -- An ASCII ERD (you might like to find the ' | + | -- An ASCII ERD (you might like to find the ' |
+ | -- from there) | ||
-- | -- | ||
- | -- +---------------+ | + | -- +---------------+ |
- | -- | user_template | | + | -- | user_template | |
- | -- +---------------+ | + | -- +---------------+ |
- | -- | id | + | -- | id |-----+ |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- +---------------+ | + | -- +---------------+ |
- | -- | | | + | -- | | |
- | -- +---------------+ | + | -- +---------------+ |
- | -- | user_params | + | -- | user_params |
- | -- +---------------+ | + | -- +---------------+ |
- | -- | id | + | -- | id |
- | -- +---| userid | + | -- +---| userid |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- | +----------+ | + | -- | +----------+ |
- | -- | | | + | -- | | |
- | -- | +----------+ | + | -- | +----------+ |
- | -- +----==| | + | -- +----==| |
- | -- / | | + | -- / | |
- | -- | | password | | + | -- | | password | |
- | -- | +----------+ | + | -- | +----------+ |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- | | + | -- | |
- | -- | | + | -- | +------------+ |
- | -- | | history | + | -- | |
- | -- | | + | -- | +------------+ |
- | -- | | + | -- | |
- | -- +---| userid | + | -- +---| |
- | -- | | + | -- | hdr_from |
- | -- | too | + | -- | hdr_to |
- | -- | cc | | | | + | -- | hdr_cc |
- | -- | subject | + | -- | hdr_subject| | | |
- | -- | bucketid |--+ | | + | -- | bucketid |
- | -- | usedtobe |--/ | | + | -- | usedtobe |
- | -- | magnetid |--------+ | + | -- | magnetid |
- | -- | | + | -- | |
- | -- +----------+ | + | -- | inserted |
+ | -- | hash | | ||
+ | -- | committed | ||
+ | -- | sort_from | ||
+ | -- | sort_cc | ||
+ | -- | sort_to | ||
+ | -- | size | ||
+ | -- +------------+ | ||
-- | -- | ||
-- TABLE DEFINITIONS | -- TABLE DEFINITIONS | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
+ | -- | ||
+ | -- popfile - data about the database | ||
+ | -- | ||
+ | -- --------------------------------------------------------------------------- | ||
+ | |||
+ | create table popfile ( id integer primary key, | ||
+ | | ||
+ | ); | ||
+ | |||
+ | -- --------------------------------------------------------------------------- | ||
-- | -- | ||
-- users - the table that stores the names and password of POPFile users | -- users - the table that stores the names and password of POPFile users | ||
Line 85: | Line 103: | ||
-- there will be suitable APIs and UI to modify it | -- there will be suitable APIs and UI to modify it | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create table users ( id integer primary key, -- unique ID for this user | create table users ( id integer primary key, -- unique ID for this user | ||
- | < | + | name varchar(255), |
- | password varchar(255), | + | |
- | unique (name) | + | |
- | );</ | + | |
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- buckets - the table that stores the name of POPFile buckets and relates | -- buckets - the table that stores the name of POPFile buckets and relates | ||
- | -- them to users. | + | -- them to users. |
-- | -- | ||
-- Note: A single user may have multiple buckets, but a single bucket only has | -- 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. | -- 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 | create table buckets( id integer primary key, -- unique ID for this bucket | ||
- | < | + | |
- | | + | -- the users table |
- | | + | name varchar(255), |
- | | + | pseudo int, -- 1 if this is a pseudobucket |
- | | + | -- (i.e. one POPFile uses |
- | | + | -- internally) |
- | | + | unique (userid, |
- | | + | -- with the same name |
+ | ); | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
- | -- words - the table that creates a unique ID for a word. | + | -- 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 | + | -- Words and buckets come together in the matrix table to form the corpus of |
- | -- each user. | + | -- words for each user. |
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create table words( | create table words( | ||
- | < | + | |
- | | + | unique (word) |
- | | + | ); |
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- matrix - the corpus that consists of buckets filled with words. | -- matrix - the corpus that consists of buckets filled with words. | ||
-- in each bucket has a word count. | -- in each bucket has a word count. | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create table matrix( id integer primary key, -- unique ID for this entry | create table matrix( id integer primary key, -- unique ID for this entry | ||
- | < | + | wordid integer, |
- | bucketid integer, | + | |
- | times integer, | + | |
- | -- 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. | + | -- 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 | -- For example in the users table there is just an password associated with | ||
Line 153: | Line 173: | ||
-- user_params table relates an actual user with each parameter | -- user_params table relates an actual user with each parameter | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
- | create table user_template( id integer primary key, -- unique ID for this entry | + | create table user_template( id integer primary key, -- unique ID for this entry |
- | < | + | name varchar(255), |
-- parameter | -- parameter | ||
- | def varchar(255), | + | |
-- the parameter | -- the parameter | ||
- | unique (name) | + | |
- | | + | -- unique |
+ | ); | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- user_params - the table that relates users with user parameters (as defined | -- user_params - the table that relates users with user parameters (as defined | ||
-- in user_template) and specific values. | -- in user_template) and specific values. | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
- | create table user_params( id integer primary key, -- unique ID for this | + | create table user_params( id integer primary key, -- unique ID for this |
- | < | + | -- entry |
- | | + | userid integer, |
- | | + | utid integer, |
- | -- user_template | + | -- user_template |
- | | + | val varchar(255), |
- | -- parameter | + | -- parameter |
- | < | + | unique (userid, utid) |
- | -- instance of each parameter | + | -- instance of each parameter |
- | < | + | ); |
- | + | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
- | -- bucket_template - the table of possible parameters that a bucket can have. | + | -- bucket_template - the table of possible parameters that a bucket can have. |
-- | -- | ||
-- See commentary for user_template for an explanation of the philosophy | -- See commentary for user_template for an explanation of the philosophy | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
- | create table bucket_template( id integer primary key, -- unique ID for this entry | + | create table bucket_template( id integer primary key, -- unique ID for this |
- | < | + | |
- | -- parameter | + | name varchar(255), |
- | | + | |
- | -- the parameter | + | def varchar(255), |
- | | + | |
- | | + | unique (name) |
+ | | ||
+ | ); | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
- | -- bucket_params - the table that relates buckets with bucket parameters | + | -- bucket_params - the table that relates buckets with bucket parameters |
- | -- in bucket_template) and specific values. | + | -- (as defined |
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
- | create table bucket_params( id integer primary key, -- unique ID for this | + | create table bucket_params( id integer primary key, |
- | < | + | -- entry |
- | | + | bucketid integer, |
- | | + | btid integer, |
-- bucket_template | -- bucket_template | ||
- | val varchar(255), | + | |
- | -- parameter | + | -- parameter |
- | < | + | unique (bucketid, btid) |
- | -- instance of each parameter | + | -- one instance of each |
- | < | + | -- parameter |
+ | ); | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- magnet_types - the types of possible magnet and their associated header | -- magnet_types - the types of possible magnet and their associated header | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create table magnet_types( id integer primary key, -- unique ID for this entry | create table magnet_types( id integer primary key, -- unique ID for this entry | ||
- | < | + | mtype varchar(255), |
- | | + | -- (e.g. from) |
- | header varchar(255), | + | |
- | unique (mtype) | + | |
- | );</ | + | |
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- magnets - relates specific buckets to specific magnet types with actual | -- magnets - relates specific buckets to specific magnet types with actual | ||
-- magnet values | -- magnet values | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create table magnets( id integer primary key, -- unique ID for this entry | create table magnets( id integer primary key, -- unique ID for this entry | ||
- | < | + | |
- | | + | mtid integer, |
- | | + | val varchar(255), |
- | | + | comment varchar(255), |
- | | + | seq integer |
- | | + | |
+ | ); | ||
- | -- MySQL SPECIFIC | + | -- --------------------------------------------------------------------------- |
+ | -- | ||
+ | -- history - this table contains the items in the POPFile history that | ||
+ | -- are managed by POPFile:: | ||
+ | -- | ||
+ | -- --------------------------------------------------------------------------- | ||
+ | |||
+ | create table history( id integer primary key, -- unique ID for this entry | ||
+ | userid integer, | ||
+ | committed integer, | ||
+ | -- committed | ||
+ | hdr_from | ||
+ | hdr_to | ||
+ | hdr_cc | ||
+ | hdr_subject varchar(255), | ||
+ | hdr_date | ||
+ | hash varchar(255), | ||
+ | inserted | ||
+ | bucketid integer, | ||
+ | usedtobe integer, | ||
+ | magnetid integer, | ||
+ | sort_from | ||
+ | sort_to | ||
+ | sort_cc | ||
+ | size integer | ||
+ | ); | ||
+ | |||
+ | -- MySQL SPECIFIC | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- NOTE: The following alter table statements are required by MySQL in order | -- NOTE: The following alter table statements are required by MySQL in order | ||
-- to get the ID fields to auto_increment on inserts. | -- to get the ID fields to auto_increment on inserts. | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
alter table buckets modify id int(11) auto_increment; | alter table buckets modify id int(11) auto_increment; | ||
- | alter table bucket_param | + | alter table bucket_params |
alter table bucket_template 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 magnets modify id int(11) auto_increment; | ||
Line 262: | Line 314: | ||
alter table users 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 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); | 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' | ||
+ | |||
+ | alter table history modify hdr_date int(11); | ||
+ | alter table history modify inserted int(11); | ||
-- TRIGGERS | -- TRIGGERS | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- delete_bucket - if a/some bucket(s) are delete then this trigger ensures | -- delete_bucket - if a/some bucket(s) are delete then this trigger ensures | ||
Line 272: | Line 343: | ||
-- | -- | ||
-- It deletes the related entries in the ' | -- It deletes the related entries in the ' | ||
- | -- ' | + | -- ' |
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
- | + | ||
create trigger delete_bucket delete on buckets | create trigger delete_bucket delete on buckets | ||
- | < | + | begin |
- | delete from matrix where bucketid = old.id; | + | |
- | delete from magnets where bucketid = old.id; | + | delete from history where bucketid = old.id; |
- | delete from bucket_params where bucketid = old.id; | + | delete from magnets where bucketid = old.id; |
- | end;</ | + | |
+ | | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- delete_user - deletes entries that are related to a user | -- delete_user - deletes entries that are related to a user | ||
Line 289: | Line 361: | ||
-- It deletes the related entries in the ' | -- It deletes the related entries in the ' | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create trigger delete_user delete on users | create trigger delete_user delete on users | ||
- | < | + | begin |
- | delete from buckets where userid = old.id; | + | delete from history where userid = old.id; |
- | delete from user_params where userid = old.id; | + | delete from buckets where userid = old.id; |
- | end;</ | + | |
+ | | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- delete_magnet_type - handles the removal of a magnet type (this should be a | -- delete_magnet_type - handles the removal of a magnet type (this should be a | ||
-- very rare thing) | -- very rare thing) | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create trigger delete_magnet_type delete on magnet_types | create trigger delete_magnet_type delete on magnet_types | ||
- | < | + | begin |
- | delete from magnet | + | |
- | end;</ | + | |
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
-- | -- | ||
-- delete_user_template - handles the removal of a type of user parameters | -- delete_user_template - handles the removal of a type of user parameters | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create trigger delete_user_template delete on user_template | 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 | -- delete_bucket_template - handles the removal of a type of bucket parameters | ||
-- | -- | ||
- | -- --------------------------------------------------------------------------------------------- | + | -- --------------------------------------------------------------------------- |
create trigger delete_bucket_template delete on bucket_template | create trigger delete_bucket_template delete on bucket_template | ||
- | < | + | begin |
- | delete from bucket_params where btid = old.id; | + | |
- | end;</ | + | |
-- Default data | -- Default data | ||
+ | |||
+ | -- This is schema version 3 | ||
+ | |||
+ | insert into popfile ( version ) values ( 3 ); | ||
-- There' | -- There' | ||
insert into users ( name, password ) values ( ' | insert into users ( name, password ) values ( ' | ||
+ | |||
+ | insert into magnets ( id, bucketid, mtid, val, comment, seq ) values ( 0, 0, 0, '', | ||
-- These are the possible parameters for a bucket | -- These are the possible parameters for a bucket | ||
-- | -- | ||
- | -- subject | + | -- subject |
+ | -- | ||
-- xtc 1 if should add X-Text-Classification header | -- xtc 1 if should add X-Text-Classification header | ||
-- xpl 1 if should add X-POPFile-Link header | -- xpl 1 if should add X-POPFile-Link header | ||
- | -- fncount | + | -- fncount |
- | -- | + | -- |
- | -- fpcount | + | -- fpcount |
- | -- quarantine | + | -- |
+ | -- quarantine | ||
+ | -- | ||
-- count Total number of messages classified into this bucket | -- count Total number of messages classified into this bucket | ||
-- color The color used for this bucket in the UI | -- color The color used for this bucket in the UI | ||
- | insert into bucket_template ( name, def ) values ( ' | + | insert into bucket_template ( name, def ) values ( ' |
insert into bucket_template ( name, def ) values ( ' | insert into bucket_template ( name, def ) values ( ' | ||
insert into bucket_template ( name, def ) values ( ' | insert into bucket_template ( name, def ) values ( ' | ||
Line 369: | Line 451: | ||
insert into buckets ( name, pseudo, userid ) values ( ' | insert into buckets ( name, pseudo, userid ) values ( ' | ||
+ | |||
+ | -- 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 | -- 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.