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,
30 ) type=innodb; -- user
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
39 ) type=innodb; -- useropts
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)
51 ) type=innodb; -- email
53 -- whitelist: Table holds the users' whitelists
55 userid varchar (128) not null,
56 type enum ("white", "black", "null") not null,
57 pattern varchar (128),
59 comment varchar (128),
63 key user_index (userid),
64 key user_listtype (userid, type),
65 unique (userid, type, sequence),
66 foreign key (userid) references user (userid) on delete cascade
67 ) type=innodb; -- list
69 -- log: Table to hold log information
71 userid varchar (128) not null,
85 message varchar (255) not null,
86 key user_index (userid),
87 foreign key (userid) references user (userid) on delete cascade
91 --grant all privileges
92 -- on MAPS.* to mapsadmin@"%" identified by "mapsadmin";
94 -- on MAPS.* to mapsreader@"%" identified by "reader";
95 --grant insert, select, update, delete
96 -- on MAPS.* to mapswriter@"%" identified by "writer";