1 -------------------------------------------------------------------------------
4 -- Revision: $Revision: 1.1 $
5 -- Description: This file creates the MAPS database.
6 -- Author: Andrew@DeFaria.com
7 -- Created: Tue May 13 13:28:18 PDT 2003
8 -- Modified: $Date: 2013/06/12 14:05:47 $
11 -- Copyright (c) 2000-2006, Andrew@DeFaria.com, all rights reserved
13 -------------------------------------------------------------------------------
14 -- Warning: The following line will delete the old database!
15 drop database if exists MAPS;
17 -- Create a new database
20 -- Now let's focus on this new database
23 -- user: Valid users and their passwords are contained here
25 userid varchar (128) not null,
26 name tinytext not null,
27 email varchar (128) not null,
28 password tinytext not null,
32 -- useropts: User's options are stored here
33 create table useropts (
34 userid varchar (128) not null,
37 key user_index (userid),
38 foreign key (userid) references user (userid) on delete cascade
41 -- email: Table that holds the email
43 userid varchar (128) not null,
44 sender varchar (128) not null,
45 subject varchar (255),
48 key user_index (userid),
49 foreign key (userid) references user (userid) on delete cascade,
50 key sender_index (sender)
53 -- list: Table holds the users' various lists
55 userid varchar (128) not null,
56 type enum ("white", "black", "null") not null,
57 pattern varchar (128),
59 comment varchar (128),
64 -- Retention: This field indicates how much time must pass before an inactive
65 -- list entry should be scrubbed. Null indicates retain forever.
66 -- other values include "x day(s)", "x month(s)", "x year(s)". So,
67 -- for example, a user on the white list may have its retention set
68 -- to say 1 year and when mapsscrub runs, if last_hit is older than
69 -- a year the whitelist entry would be removed. If, however,
70 -- retention is null then the record is kept forever. This is useful
71 -- for the null and black lists where one might want to insure that
72 -- a particular domain (e.g. @talentburst.com) will never come off
74 retention varchar (40),
75 key user_index (userid),
76 key user_listtype (userid, type),
77 unique (userid, type, sequence),
78 foreign key (userid) references user (userid) on delete cascade
81 -- log: Table to hold log information
83 userid varchar (128) not null,
97 message varchar (255) not null,
98 key user_index (userid),
99 foreign key (userid) references user (userid) on delete cascade
104 --create user 'maps'@'localhost' identified by 'spam';
105 grant all privileges on MAPS.* to 'maps'@'localhost';