1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
|
# Copyright (C) 1999-2004 IC & S dbmail@ic-s.nl
#
# This program 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.
#
# This program 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 this program; if not, write to the Free Software
# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#
# schema for innodb tables with added indexes and foreign keys
# for more speed.
# This file was originally created by Paul Stevens and added
# to dbmail on August 27th, 2003.
#
# $Id$
# if database needs to be created, uncomment next line
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS dbmail_aliases;
CREATE TABLE dbmail_aliases (
alias_idnr bigint(21) NOT NULL auto_increment,
alias varchar(100) NOT NULL default '',
deliver_to varchar(250) NOT NULL default '',
client_idnr bigint(21) NOT NULL default '0',
PRIMARY KEY (alias_idnr),
INDEX alias_index (alias),
INDEX client_idnr_index (client_idnr)
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_users;
CREATE TABLE dbmail_users (
user_idnr bigint(21) NOT NULL auto_increment,
userid varchar(100) NOT NULL default '',
passwd varchar(34) NOT NULL default '',
client_idnr bigint(21) NOT NULL default '0',
maxmail_size bigint(21) NOT NULL default '0',
curmail_size bigint(21) NOT NULL default '0',
encryption_type varchar(20) NOT NULL default '',
last_login datetime NOT NULL default '1979-11-03 22:05:58',
PRIMARY KEY (user_idnr),
UNIQUE INDEX userid_index (userid)
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_usermap;
CREATE TABLE dbmail_usermap (
user_idnr INT8 REFERENCES dbmail_users (user_idnr) ON UPDATE CASCADE ON DELETE CASCADE,
userid VARCHAR(100)
) TYPE = InnoDB;
CREATE INDEX usermap_userid_idx ON dbmail_usermap(userid);
DROP TABLE IF EXISTS dbmail_mailboxes;
CREATE TABLE dbmail_mailboxes (
mailbox_idnr bigint(21) NOT NULL auto_increment,
owner_idnr bigint(21) NOT NULL default '0',
name varchar(100) BINARY NOT NULL default '',
seen_flag tinyint(1) NOT NULL default '0',
answered_flag tinyint(1) NOT NULL default '0',
deleted_flag tinyint(1) NOT NULL default '0',
flagged_flag tinyint(1) NOT NULL default '0',
recent_flag tinyint(1) NOT NULL default '0',
draft_flag tinyint(1) NOT NULL default '0',
no_inferiors tinyint(1) NOT NULL default '0',
no_select tinyint(1) NOT NULL default '0',
permission tinyint(1) default '2',
PRIMARY KEY (mailbox_idnr),
INDEX name_index (name),
INDEX owner_idnr_index (owner_idnr),
UNIQUE INDEX owner_idnr_name_index (owner_idnr, name),
FOREIGN KEY owner_idnr_fk (owner_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_subscription;
CREATE TABLE dbmail_subscription (
user_id bigint(21) not null default '0',
mailbox_id bigint(21) not null,
primary key (user_id, mailbox_id),
index user_id_index (user_id),
index mailbox_id_index (mailbox_id),
FOREIGN KEY user_id_fk (user_id)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY mailbox_id_fk (mailbox_id)
REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_acl;
CREATE TABLE dbmail_acl (
user_id bigint(21) NOT NULL,
mailbox_id bigint(21) NOT NULL,
lookup_flag tinyint(1) default '0' not null,
read_flag tinyint(1) default '0' not null,
seen_flag tinyint(1) default '0' not null,
write_flag tinyint(1) default '0' not null,
insert_flag tinyint(1) default '0' not null,
post_flag tinyint(1) default '0' not null,
create_flag tinyint(1) default '0' not null,
delete_flag tinyint(1) default '0' not null,
administer_flag tinyint(1) default '0' not null,
PRIMARY KEY(user_id, mailbox_id),
INDEX user_id_index (user_id),
INDEX mailbox_id_index (mailbox_id),
FOREIGN KEY user_id_fk (user_id)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY mailbox_id_fk (mailbox_id)
REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_physmessage;
CREATE TABLE dbmail_physmessage (
id bigint(21) NOT NULL auto_increment,
messagesize bigint(21) NOT NULL default '0',
rfcsize bigint(21) NOT NULL default '0',
internal_date datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_messages;
CREATE TABLE dbmail_messages (
message_idnr bigint(21) NOT NULL auto_increment,
mailbox_idnr bigint(21) NOT NULL default '0',
physmessage_id bigint(21) NOT NULL default '0',
seen_flag tinyint(1) NOT NULL default '0',
answered_flag tinyint(1) NOT NULL default '0',
deleted_flag tinyint(1) NOT NULL default '0',
flagged_flag tinyint(1) NOT NULL default '0',
recent_flag tinyint(1) NOT NULL default '0',
draft_flag tinyint(1) NOT NULL default '0',
unique_id varchar(70) NOT NULL default '',
status tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (message_idnr),
INDEX physmessage_id_index (physmessage_id),
INDEX mailbox_idnr_index (mailbox_idnr),
INDEX seen_flag_index (seen_flag),
INDEX unique_id_index (unique_id),
INDEX status_index (status),
INDEX mailbox_status (mailbox_idnr, status),
FOREIGN KEY physmessage_id_fk (physmessage_id)
REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY mailbox_idnr_fk (mailbox_idnr)
REFERENCES dbmail_mailboxes (mailbox_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_messageblks;
CREATE TABLE dbmail_messageblks (
messageblk_idnr bigint(21) NOT NULL auto_increment,
physmessage_id bigint(21) NOT NULL default '0',
messageblk longblob NOT NULL,
blocksize bigint(21) NOT NULL default '0',
is_header tinyint(1) NOT NULL default '0',
PRIMARY KEY (messageblk_idnr),
INDEX physmessage_id_index (physmessage_id),
INDEX physmessage_id_is_header_index (physmessage_id, is_header),
FOREIGN KEY physmessage_id_fk (physmessage_id)
REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_auto_notifications;
CREATE TABLE dbmail_auto_notifications (
user_idnr bigint(21) NOT NULL,
notify_address VARCHAR(100) NOT NULL,
INDEX user_idnr_index (user_idnr),
FOREIGN KEY user_idnr_fk (user_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_auto_replies;
CREATE TABLE dbmail_auto_replies (
user_idnr bigint(21) DEFAULT '0' NOT NULL,
reply_body mediumtext,
INDEX user_idnr_index (user_idnr),
FOREIGN KEY user_idnr_fk (user_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_pbsp;
CREATE TABLE dbmail_pbsp (
idnr bigint(21) NOT NULL auto_increment,
since datetime default '0' not null,
ipnumber varchar(40) NOT NULL,
PRIMARY KEY (idnr),
UNIQUE INDEX ipnumber_index (ipnumber),
INDEX since_index (since)
) TYPE=InnoDB;
DROP TABLE IF EXISTS dbmail_sievescripts;
CREATE TABLE dbmail_sievescripts (
owner_idnr bigint(21) DEFAULT '0' NOT NULL,
name varchar(100) NOT NULL,
script text,
active tinyint(1) default '0' not null,
INDEX (name),
INDEX (owner_idnr),
INDEX (owner_idnr, name)
) TYPE=InnoDB;
SET FOREIGN_KEY_CHECKS=1;
# create the user for the delivery chain
INSERT INTO dbmail_users (userid, passwd, encryption_type)
VALUES ('__@!internal_delivery_user!@__', '', 'md5');
# insert the 'anyone' user which is used for ACLs.
INSERT INTO dbmail_users (userid, passwd, encryption_type)
VALUES ('anyone', '', 'md5');
|
