Differences

This shows you the differences between two versions of the page.

sqlschema [2008/02/08 19:49]
127.0.0.1 external edit
sqlschema [2010/09/27 07:21] (current)
amatubu
Line 1: Line 1:
-===== V 0.21.0 Schema for POPFile =====+===== V 1.1.1 Schema for POPFile =====
<code> <code>
--- ---------------------------------------------------------------------------------------------+-- POPFILE SCHEMA 3 
 +-- ---------------------------------------------------------------------------
-- --
-- popfile.schema - POPFile's database schema -- popfile.schema - POPFile's database schema
-- --
--- 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
-- --
---   POPFile is free software; you can redistribute it and/or modify +--   POPFile is free software; you can redistribute it and/or modify it 
---   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.+
-- --
--   POPFile is distributed in the hope that it will be useful, --   POPFile is distributed in the hope that it will be useful,
Line 25: Line 25:
--   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA --   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)+-- An ASCII ERD (you might like to find the 'users' table first and work 
 +-- from there)
-- --
---      +---------------+      +-----------------+ +--      +---------------+        +-----------------+ 
---      | user_template |      | bucket_template | +--      | user_template |        | bucket_template | 
---      +---------------+      +-----------------+ +--      +---------------+        +-----------------+ 
---      |      id       |---+   |       id        |---+ +--      |      id       |-----+   |       id        |---+ 
---      |     name      |  |   |      name       |   | +--      |     name      |    |   |      name       |   | 
---      |     def       |  |   |       def       |   | +--      |     def       |    |   |       def       |   | 
---      +---------------+  |   +-----------------+   | +--      +---------------+    |   +-----------------+   | 
---                         |                         | +--                           |                         | 
---      +---------------+  |     +---------------+   | +--      +---------------+    |     +---------------+   | 
---      |  user_params  |  |     | bucket_params |   | +--      |  user_params  |    |     | bucket_params |   | 
---      +---------------+  |     +---------------+   | +--      +---------------+    |     +---------------+   | 
---      |      id       |  |     |      id       |   | +--      |      id       |    |     |      id       |   | 
---  +---|    userid     |  | +---|   bucketid    |   | +--  +---|    userid     |    | +---|   bucketid    |   | 
---  |   |     utid      |---+ |   |     btid      |---+ +--  |   |     utid      |-----+ |   |     btid      |---+ 
---  |   |     val       |    |   |     val       | +--  |   |     val       |      |   |     val       | 
---  |   +---------------+    |   +---------------+ +--  |   +---------------+      |   +---------------+ 
---  |                        |                      +----------+ +--  |                          |                      +----------+ 
---  |                        |                      |  matrix  |     +-------+ +--  |                          |                      |  matrix  |  +-------+ 
---  |                        |   +---------+        +----------+     | words | +--  |                          |   +---------+        +----------+  | words | 
---  |      +----------+      |   | buckets |        |    id    |     +-------+ +--  |      +----------+        |   | buckets |        |    id    |  +-------+ 
---  |      |   users  |      |   +---------+        |  wordid  |------|  id   | +--  |      |   users  |        |   +---------+        |  wordid  |---|  id   | 
---  |      +----------+   /--+---|    id   |=====---| bucketid |     |  word | +--  |      +----------+     /--+---|    id   |=====---| bucketid |  |  word | 
---  +----==|    id    |---(-------| userid  |     \  |  times   |     +-------+ +--  +----==|    id    |-----(-------| userid  |     \  |  times   |  +-------+ 
---      /  |   name   |  |       |  name   |     |  | lastseen | +--      /  |   name   |    |       |  name   |     |  | lastseen | 
---      |  | password |  |       | pseudo  |     |  +----------+ +--      |  | password |    |       | pseudo  |     |  +----------+ 
---      |  +----------+  |       +---------+     | +--      |  +----------+    |       +---------+     | 
---      |                |                       | +--      |                  |                       | 
---      |                |        +-----------+  | +--      |                  |        +-----------+  | 
---      |                |        |  magnets  |  | +--      |                  |        |  magnets  |  | 
---      |   +----------+  |        +-----------+  |     +--------------+ +--      |   +------------+  |        +-----------+  |     +--------------+ 
---      |   | history  |  |     +--|    id     |  |     | magnet_types | +--      |   |  history  |  |     +--|    id     |  |     | magnet_types | 
---      |   +----------+  |     |  | bucketid  |--+     +--------------+ +--      |   +------------+  |     |  | bucketid  |--+     +--------------+ 
---      |   |  id     |  |     |  |   mtid    |--------|      id      | +--      |   |    id     |  |     |  |   mtid    |--------|      id      | 
---      +---| userid   |  |     |  |   val     |        |     mtype    | +--      +---|  userid   |  |     |  |   val     |        |     mtype    | 
---          | frm     |  |     |  |   seq     |        |    header    | +--          |  hdr_from |  |     |  |   seq     |        |    header    | 
---          |   too    |  |     |  +-----------+        +--------------+ +--          |   hdr_to   |  |     |  +-----------+        +--------------+ 
---          |   cc     |  |     | +--          |   hdr_cc   |  |     | 
---          | subject  |  |     | +--          | hdr_subject|  |     | 
---          | bucketid |--+     | +--          | bucketid |--+     | 
---          | usedtobe |--/     | +--          | usedtobe |--/     | 
---          | magnetid |--------+ +--          | magnetid |--------+ 
---          |  message +--          |  hdr_date  | 
---          +----------++--          | 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, 
 +                       version integer         -- version number of this schema 
 +                     ); 
 + 
 +-- ---------------------------------------------------------------------------
-- --
-- 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),       -- textual name of the user +                     name varchar(255),       -- textual name of the user 
-                    password varchar(255),   -- user's password +                     password varchar(255),   -- user's password 
-                    unique (name)            -- the user name must be unique +                     unique (name)            -- the user name must be unique 
-                  );+                   );
--- ---------------------------------------------------------------------------------------------+-- ---------------------------------------------------------------------------
-- --
-- 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
-                     userid integer,         -- corresponds to an entry in +                      userid integer,         -- corresponds to an entry in 
-                                             -- the users table +                                              -- the users table 
-                     name varchar(255),      -- the name of the bucket +                      name varchar(255),      -- the name of the bucket 
-                     pseudo int,             -- 1 if this is a pseudobucket +                      pseudo int,             -- 1 if this is a pseudobucket 
-                                             -- (i.e. one POPFile uses internally) +                                              -- (i.e. one POPFile uses 
-                     unique (userid,name)    -- a user can't have two buckets +                                              -- internally) 
-                                             -- with the same name +                      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 - 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(   id integer primary key, -- unique ID for this word create table words(   id integer primary key, -- unique ID for this word
-                     word varchar(255),      -- the word +                      word varchar(255),      -- the word 
-                     unique (word)           -- each word is unique +                      unique (word)           -- each word is unique 
-                 );+                  );
--- ---------------------------------------------------------------------------------------------+-- ---------------------------------------------------------------------------
-- --
-- matrix - the corpus that consists of buckets filled with words.  Each word -- matrix - the corpus that consists of buckets filled with words.  Each word
--          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,           -- an ID in the words table +                     wordid integer,           -- an ID in the words table 
-                    bucketid integer,         -- an ID in the buckets table +                     bucketid integer,         -- an ID in the buckets table 
-                    times integer,            -- number of times the word has +                     times integer,            -- number of times the word has 
-                                              -- been seen +                                               -- been seen 
-                    lastseen date,            -- last time the record was read +                     lastseen date,            -- last time the record was read 
-                                              -- or written +                                               -- or written 
-                    unique (wordid, bucketid) -- each word appears once in a bucket  +                     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),        -- the name of the+                          name varchar(255),       -- the name of the
                                                    -- parameter                                                     -- parameter
-                         def varchar(255),         -- the default value for+                          def varchar(255),        -- the default value for
                                                    -- the parameter                                                     -- the parameter
-                         unique (name)             -- parameter name's are unique  +                          unique (name)            -- parameter name's are 
-                       );+                                                    -- 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 +                                                  -- entry 
-                         userid integer,           -- a user +                          userid integer,        -- a user 
-                         utid integer,             -- points to an entry in  +                          utid integer,          -- points to an entry in 
-                                                    -- user_template +                                                  -- user_template 
-                         val varchar(255),         -- value for the +                          val varchar(255),      -- value for the 
-                             -- parameter +                                                  -- parameter 
-                         unique (userid, utid)     -- each user has just one +                          unique (userid, utid)  -- each user has just one 
-                             -- 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 
-                             name varchar(255),       -- the name of the +                                                       -- entry 
-                                                      -- parameter +                              name varchar(255),       -- the name of the 
-                             def varchar(255),        -- the default value for +                                                       -- parameter 
-                                                      -- the parameter +                              def varchar(255),        -- the default value for 
-                             unique (name)            -- parameter name's are unique  +                                                       -- the parameter 
-                           );+                              unique (name)            -- parameter names 
 +                                                       -- are unique 
 +                            );
--- ---------------------------------------------------------------------------------------------+-- ---------------------------------------------------------------------------
-- --
--- bucket_params - the table that relates buckets with bucket parameters (as defined +-- bucket_params - the table that relates buckets with bucket parameters 
---                 in bucket_template) and specific values.+--                 (as defined in bucket_template) and specific values.
-- --
--- ---------------------------------------------------------------------------------------------+-- ---------------------------------------------------------------------------
-create table bucket_params( id integer primary key,   -- unique ID for this+create table bucket_params( id integer primary key,  -- unique ID for this
                                                      -- entry                                                       -- entry
-                           bucketid integer,         -- a bucket +                            bucketid integer,        -- a bucket 
-                           btid integer,             -- points to an entry in +                            btid integer,            -- points to an entry in
                                                      -- bucket_template                                                       -- bucket_template
-                           val varchar(255),         -- value for the +                            val varchar(255),        -- value for the 
-                               -- parameter +                                                      -- parameter 
-                           unique (bucketid, btid)   -- each bucket has just one +                            unique (bucketid, btid)  -- each bucket has just 
-                               -- 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),      -- the type of magnet +                           mtype varchar(255),      -- the type of magnet 
-                                                   -- (e.g. from) +                                                    -- (e.g. from) 
-                          header varchar(255),     -- the header (e.g. From) +                           header varchar(255),     -- the header (e.g. From) 
-                          unique (mtype)           -- types are unique +                           unique (mtype)           -- types are unique 
-                        );+                         );
--- ---------------------------------------------------------------------------------------------+-- ---------------------------------------------------------------------------
-- --
-- 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
-                     bucketid integer,          -- a bucket +                      bucketid integer,          -- a bucket 
-                     mtid integer,              -- the magnet type +                      mtid integer,              -- the magnet type 
-                     val varchar(255),          -- value for the magnet +                      val varchar(255),          -- value for the magnet 
-                     comment varchar(255),      -- user defined comment +                      comment varchar(255),      -- user defined comment 
-                     seq int                    -- used to set the order of magnets +                      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 (bytes) 
 +                    );
--- MySQL SPECIFIC +-- 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 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 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's use of the fields.
 +
 +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 'matrix', 'bucket_params' and -- It deletes the related entries in the 'matrix', 'bucket_params' and
--- 'magnets' tables. +-- 'magnets' tables.
-- --
--- --------------------------------------------------------------------------------------------- +-- --------------------------------------------------------------------------- 
- +
create trigger delete_bucket delete on buckets create trigger delete_bucket delete on buckets
-            begin +             begin 
-                delete from matrix where bucketid = old.id; +                 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 from bucket_params 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 'matrix' and 'user_params'. -- It deletes the related entries in the 'matrix' and 'user_params'.
-- --
--- ---------------------------------------------------------------------------------------------+-- ---------------------------------------------------------------------------
create trigger delete_user delete on users create trigger delete_user delete on users
-            begin +             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 from user_params 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 +             begin 
-                delete from magnet where mtid = old.id; +                 delete from magnets where mtid = old.id; 
-            end;+             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 +             begin 
-                delete from user_params where utid = old.id; +                 delete from user_params where utid = old.id; 
-            end;+             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 +             begin 
-                delete from bucket_params where btid = old.id; +                 delete from bucket_params where btid = old.id; 
-            end;+             end;
-- Default data -- Default data
 +
 +-- This is schema version 3
 +
 +insert into popfile ( version ) values ( 3 );
-- There's always a user called 'admin' -- There's always a user called 'admin'
insert into users ( name, password ) values ( 'admin', 'e11f180f4a31d8caface8e62994abfaf' ); 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 -- These are the possible parameters for a bucket
-- --
--- subject      1 if should do subject modification for message classified to this bucket+-- subject      1 if should do subject modification for message classified 
 +--              to this bucket
-- 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      Number of messages that were incorrectly classified, and meant to go into +-- fncount      Number of messages that were incorrectly classified, and 
---                  this bucket but did not +--              meant to go into this bucket but did not 
--- fpcount      Number of messages that were incorrectly classified into this bucket +-- fpcount      Number of messages that were incorrectly classified into 
--- quarantine   1 if should quaratine (i.e. RFC822 wrap) messages in this bucket+--              this bucket 
 +-- quarantine   1 if should quaratine (i.e. RFC822 wrap) messages in this 
 +--              bucket
-- 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 ( 'subject',    '1' ); +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 ( 'xtc',        '1' );
insert into bucket_template ( name, def ) values ( 'xpl',        '1' ); insert into bucket_template ( name, def ) values ( 'xpl',        '1' );
Line 369: Line 451:
insert into buckets ( name, pseudo, userid ) values ( 'unclassified', 1, 1 ); 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 -- END
 
sqlschema.txt · Last modified: 2010/09/27 07:21 by amatubu

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.

Recent changes RSS feed Donate Driven by DokuWiki
The content of this wiki is protected by the GNU Fee Documentation License