1 | -- |
---|
2 | -- Expresso Mail Archiver |
---|
3 | -- |
---|
4 | -- Script H2 de criacao do banco de dados |
---|
5 | -- |
---|
6 | -- (consultar http://www.h2database.com) |
---|
7 | -- |
---|
8 | |
---|
9 | ------------------------------------------------------------------------------- |
---|
10 | CREATE SCHEMA METAARCHIVE; |
---|
11 | SET SCHEMA METAARCHIVE; |
---|
12 | |
---|
13 | ------------------------------------------------------------------------------- |
---|
14 | -- Table METAARCHIVE.FOLDER for class |
---|
15 | --serpro.mailarchiver.domain.metaarchive.Folder |
---|
16 | |
---|
17 | CREATE TABLE FOLDER ( |
---|
18 | |
---|
19 | OID VARCHAR(36) NOT NULL, |
---|
20 | |
---|
21 | FOLDER_NAME VARCHAR(1000) NOT NULL, |
---|
22 | |
---|
23 | PARENT_OID VARCHAR(36), |
---|
24 | PARENT_IDX INT, |
---|
25 | |
---|
26 | CONSTRAINT FOLDER_PK PRIMARY KEY (OID) |
---|
27 | |
---|
28 | ); |
---|
29 | |
---|
30 | ------------------------------------------------------------------------------- |
---|
31 | -- Table METAARCHIVE.SETTING for class |
---|
32 | --serpro.mailarchiver.domain.metaarchive.Setting |
---|
33 | |
---|
34 | CREATE TABLE SETTING ( |
---|
35 | |
---|
36 | ATTR_NAME VARCHAR(100) NOT NULL, |
---|
37 | ATTR_VALUE VARCHAR(1000) NOT NULL, |
---|
38 | |
---|
39 | CONSTRAINT SETTING_PK PRIMARY KEY (ATTR_NAME) |
---|
40 | |
---|
41 | ); |
---|
42 | |
---|
43 | ------------------------------------------------------------------------------- |
---|
44 | -- Table METAARCHIVE.USER for class |
---|
45 | --serpro.mailarchiver.domain.metaarchive.User |
---|
46 | |
---|
47 | CREATE TABLE USER ( |
---|
48 | |
---|
49 | USER_ID VARCHAR(100) NOT NULL, |
---|
50 | PASSWORD VARCHAR(1000) NOT NULL, |
---|
51 | |
---|
52 | CONSTRAINT USER_PK PRIMARY KEY (USER_ID) |
---|
53 | |
---|
54 | ); |
---|
55 | |
---|
56 | ------------------------------------------------------------------------------- |
---|
57 | -- Table METAARCHIVE.ENTITY for classes DISC |
---|
58 | --serpro.mailarchiver.domain.metaarchive.Message 1 |
---|
59 | --serpro.mailarchiver.domain.metaarchive.BodyPart 2 |
---|
60 | --serpro.mailarchiver.domain.metaarchive.EmbeddedMessage 3 |
---|
61 | --serpro.mailarchiver.domain.metaarchive.Entity 101 |
---|
62 | --serpro.mailarchiver.domain.metaarchive.BodyEntity 102 |
---|
63 | |
---|
64 | CREATE TABLE ENTITY ( |
---|
65 | |
---|
66 | DISCRIMINATOR VARCHAR(40) NOT NULL, |
---|
67 | |
---|
68 | OID VARCHAR(36) NOT NULL, |
---|
69 | |
---|
70 | COMPOSITE_OID VARCHAR(36), |
---|
71 | COMPOSITE_IDX INT, |
---|
72 | |
---|
73 | FOLDER_OID VARCHAR(36), |
---|
74 | FOLDER_IDX INT, |
---|
75 | |
---|
76 | START_LINE INT, |
---|
77 | SEPARATOR_LINE INT, |
---|
78 | END_LINE INT, |
---|
79 | |
---|
80 | ENTITY_SIZE INT, |
---|
81 | |
---|
82 | QUERY_CANDIDATES_SET BIGINT, |
---|
83 | |
---|
84 | CONSTRAINT ENTITY_PK PRIMARY KEY (OID) |
---|
85 | |
---|
86 | ); |
---|
87 | |
---|
88 | ------------------------------------------------------------------------------- |
---|
89 | -- Table METAARCHIVE.BODY for classes DISC |
---|
90 | --serpro.mailarchiver.domain.metaarchive.TextBody 1 |
---|
91 | --serpro.mailarchiver.domain.metaarchive.BinaryBody 2 |
---|
92 | --serpro.mailarchiver.domain.metaarchive.Multipart 3 |
---|
93 | --serpro.mailarchiver.domain.metaarchive.MessageBody 4 |
---|
94 | --serpro.mailarchiver.domain.metaarchive.Body 201 |
---|
95 | --serpro.mailarchiver.domain.metaarchive.SingleBody 202 |
---|
96 | |
---|
97 | CREATE TABLE BODY ( |
---|
98 | |
---|
99 | DISCRIMINATOR VARCHAR(40) NOT NULL, |
---|
100 | |
---|
101 | OID VARCHAR(36) NOT NULL, |
---|
102 | |
---|
103 | ENTITY_OID VARCHAR(36) NOT NULL, |
---|
104 | |
---|
105 | BODY_OFFSET INT, |
---|
106 | BODY_LENGTH INT, |
---|
107 | |
---|
108 | BODY_SIZE INT, |
---|
109 | |
---|
110 | PREVIEW VARCHAR(10000), |
---|
111 | |
---|
112 | PREAMBLE VARCHAR(10000), |
---|
113 | EPILOGUE VARCHAR(10000), |
---|
114 | |
---|
115 | CONSTRAINT BODY_PK PRIMARY KEY (OID) |
---|
116 | |
---|
117 | ); |
---|
118 | |
---|
119 | ------------------------------------------------------------------------------- |
---|
120 | -- Table METAARCHIVE.FIELD for classes DISC |
---|
121 | --serpro.mailarchiver.domain.metaarchive.UnstructuredField 1 |
---|
122 | --serpro.mailarchiver.domain.metaarchive.ContentTypeField 2 |
---|
123 | --serpro.mailarchiver.domain.metaarchive.ContentDispositionField 3 |
---|
124 | --serpro.mailarchiver.domain.metaarchive.DateTimeField 4 |
---|
125 | --serpro.mailarchiver.domain.metaarchive.AddressListField 5 |
---|
126 | --serpro.mailarchiver.domain.metaarchive.MailboxListField 6 |
---|
127 | --serpro.mailarchiver.domain.metaarchive.MailboxField 7 |
---|
128 | --serpro.mailarchiver.domain.metaarchive.Field 301 |
---|
129 | |
---|
130 | CREATE TABLE FIELD ( |
---|
131 | |
---|
132 | DISCRIMINATOR VARCHAR(40) NOT NULL, |
---|
133 | |
---|
134 | OID VARCHAR(36) NOT NULL, |
---|
135 | |
---|
136 | ENTITY_OID VARCHAR(36) NOT NULL, |
---|
137 | ENTITY_IDX INT NOT NULL, |
---|
138 | |
---|
139 | FIELD_NAME VARCHAR(1000) NOT NULL, |
---|
140 | |
---|
141 | TEXT VARCHAR(10000), |
---|
142 | |
---|
143 | MEDIA_TYPE VARCHAR(100), |
---|
144 | SUB_TYPE VARCHAR(100), |
---|
145 | |
---|
146 | DISPOSITION_TYPE VARCHAR(100), |
---|
147 | |
---|
148 | DATE_TIME TIMESTAMP, |
---|
149 | |
---|
150 | VALID BOOLEAN NOT NULL, |
---|
151 | PARSE_EXCEPTION_STACK_TRACE VARCHAR(100000), |
---|
152 | |
---|
153 | CONSTRAINT FIELD_PK PRIMARY KEY (OID) |
---|
154 | |
---|
155 | ); |
---|
156 | |
---|
157 | ------------------------------------------------------------------------------- |
---|
158 | -- Table METAARCHIVE.ADDRESS for classes DISC |
---|
159 | --serpro.mailarchiver.domain.metaarchive.AddressListField_Mailbox 1 |
---|
160 | --serpro.mailarchiver.domain.metaarchive.MailboxListField_Mailbox 2 |
---|
161 | --serpro.mailarchiver.domain.metaarchive.MailboxField_Mailbox 3 |
---|
162 | --serpro.mailarchiver.domain.metaarchive.AddressListField_Group_Mailbox 4 |
---|
163 | --serpro.mailarchiver.domain.metaarchive.AddressListField_Group 5 |
---|
164 | --serpro.mailarchiver.domain.metaarchive.Address 401 |
---|
165 | --serpro.mailarchiver.domain.metaarchive.FieldAddress 402 |
---|
166 | --serpro.mailarchiver.domain.metaarchive.AddressListField_Address 403 |
---|
167 | |
---|
168 | CREATE TABLE ADDRESS ( |
---|
169 | |
---|
170 | DISCRIMINATOR VARCHAR(40) NOT NULL, |
---|
171 | |
---|
172 | OID VARCHAR(36) NOT NULL, |
---|
173 | |
---|
174 | FIELD_OID VARCHAR(36), |
---|
175 | FIELD_IDX INT, |
---|
176 | |
---|
177 | GROUP_OID VARCHAR(36), |
---|
178 | GROUP_IDX INT, |
---|
179 | |
---|
180 | ADDR_NAME VARCHAR(1000), |
---|
181 | |
---|
182 | MBX_LOCAL_PART VARCHAR(1000), |
---|
183 | MBX_DOMAIN VARCHAR(1000), |
---|
184 | MBX_ROUTE VARCHAR(10000), |
---|
185 | |
---|
186 | CONSTRAINT ADDRESS_PK PRIMARY KEY (OID) |
---|
187 | |
---|
188 | ); |
---|
189 | |
---|
190 | ------------------------------------------------------------------------------- |
---|
191 | -- Table METAARCHIVE.TAG for set |
---|
192 | --serpro.mailarchiver.domain.metaarchive.Message#tags |
---|
193 | |
---|
194 | CREATE TABLE METAARCHIVE.TAG |
---|
195 | ( |
---|
196 | |
---|
197 | ENTITY_OID VARCHAR(36) NOT NULL, |
---|
198 | TAG_VALUE VARCHAR(100) NOT NULL, |
---|
199 | |
---|
200 | CONSTRAINT TAG_PK PRIMARY KEY (ENTITY_OID, TAG_VALUE) |
---|
201 | |
---|
202 | ); |
---|
203 | |
---|
204 | ------------------------------------------------------------------------------- |
---|
205 | -- Table METAARCHIVE.PARAMETER for maps |
---|
206 | --serpro.mailarchiver.domain.metaarchive.ContentDispositionField#parameters |
---|
207 | --serpro.mailarchiver.domain.metaarchive.ContentTypeField#parameters |
---|
208 | |
---|
209 | CREATE TABLE PARAMETER ( |
---|
210 | |
---|
211 | FIELD_OID VARCHAR(36) NOT NULL, |
---|
212 | ATTR_NAME VARCHAR(100) NOT NULL, |
---|
213 | ATTR_VALUE VARCHAR(1000), |
---|
214 | |
---|
215 | CONSTRAINT PARAMETER_PK PRIMARY KEY (FIELD_OID, ATTR_NAME) |
---|
216 | |
---|
217 | ); |
---|
218 | |
---|
219 | ------------------------------------------------------------------------------- |
---|
220 | CREATE INDEX FOLDER_IDX_PARENT_FOLDER |
---|
221 | ON FOLDER (PARENT_OID); |
---|
222 | |
---|
223 | CREATE INDEX FOLDER_IDX_NAME |
---|
224 | ON FOLDER (FOLDER_NAME); |
---|
225 | |
---|
226 | ALTER TABLE FOLDER ADD CONSTRAINT FOLDER_REF_PARENT_FOLDER |
---|
227 | FOREIGN KEY (PARENT_OID) |
---|
228 | REFERENCES FOLDER ON DELETE CASCADE; |
---|
229 | |
---|
230 | ------------------------------------------------------------------------------- |
---|
231 | CREATE INDEX MESSAGE_IDX_FOLDER |
---|
232 | ON ENTITY (FOLDER_OID); |
---|
233 | |
---|
234 | CREATE INDEX BODY_ENTITY_IDX_COMPOSITE |
---|
235 | ON ENTITY (COMPOSITE_OID); |
---|
236 | |
---|
237 | CREATE INDEX MESSAGE_IDX_QUERY_CANDIDATES_SET |
---|
238 | ON ENTITY (QUERY_CANDIDATES_SET); |
---|
239 | |
---|
240 | CREATE INDEX ENTITY_IDX_DISCRIMINATOR |
---|
241 | ON ENTITY (DISCRIMINATOR); |
---|
242 | |
---|
243 | ALTER TABLE ENTITY ADD CONSTRAINT MESSAGE_REF_FOLDER |
---|
244 | FOREIGN KEY (FOLDER_OID) |
---|
245 | REFERENCES FOLDER ON DELETE CASCADE; |
---|
246 | |
---|
247 | ALTER TABLE ENTITY ADD CONSTRAINT BODY_ENTITY_REF_COMPOSITE |
---|
248 | FOREIGN KEY (COMPOSITE_OID) |
---|
249 | REFERENCES BODY ON DELETE CASCADE; |
---|
250 | |
---|
251 | ------------------------------------------------------------------------------- |
---|
252 | CREATE INDEX BODY_IDX_ENTITY |
---|
253 | ON BODY (ENTITY_OID); |
---|
254 | |
---|
255 | CREATE INDEX BODY_IDX_DISCRIMINATOR |
---|
256 | ON BODY (DISCRIMINATOR); |
---|
257 | |
---|
258 | ALTER TABLE BODY ADD CONSTRAINT BODY_REF_ENTITY |
---|
259 | FOREIGN KEY (ENTITY_OID) |
---|
260 | REFERENCES ENTITY ON DELETE CASCADE; |
---|
261 | |
---|
262 | ------------------------------------------------------------------------------- |
---|
263 | CREATE INDEX FIELD_IDX_ENTITY |
---|
264 | ON FIELD (ENTITY_OID); |
---|
265 | |
---|
266 | CREATE INDEX FIELD_IDX_NAME |
---|
267 | ON FIELD (FIELD_NAME); |
---|
268 | |
---|
269 | CREATE INDEX FIELD_IDX_TEXT |
---|
270 | ON FIELD (TEXT); |
---|
271 | |
---|
272 | CREATE INDEX FIELD_IDX_DATE_TIME |
---|
273 | ON FIELD (DATE_TIME); |
---|
274 | |
---|
275 | CREATE INDEX FIELD_IDX_DISCRIMINATOR |
---|
276 | ON FIELD (DISCRIMINATOR); |
---|
277 | |
---|
278 | ALTER TABLE FIELD ADD CONSTRAINT FIELD_REF_ENTITY |
---|
279 | FOREIGN KEY (ENTITY_OID) |
---|
280 | REFERENCES ENTITY ON DELETE CASCADE; |
---|
281 | |
---|
282 | ------------------------------------------------------------------------------- |
---|
283 | -- MAILBOX REF MAILBOX_FIELD |
---|
284 | -- MAILBOX REF MAILBOX_LIST_FIELD |
---|
285 | -- ADDRESS REF ADDRESS_LIST_FIELD |
---|
286 | |
---|
287 | CREATE INDEX ADDRESS_IDX_FIELD |
---|
288 | ON ADDRESS (FIELD_OID); |
---|
289 | |
---|
290 | CREATE INDEX MAILBOX_IDX_GROUP |
---|
291 | ON ADDRESS (GROUP_OID); |
---|
292 | |
---|
293 | CREATE INDEX ADDRESS_IDX_NAME |
---|
294 | ON ADDRESS (ADDR_NAME); |
---|
295 | |
---|
296 | CREATE INDEX ADDRESS_IDX_LOCAL_PART |
---|
297 | ON ADDRESS (MBX_LOCAL_PART); |
---|
298 | |
---|
299 | CREATE INDEX ADDRESS_IDX_DOMAIN |
---|
300 | ON ADDRESS (MBX_DOMAIN); |
---|
301 | |
---|
302 | CREATE INDEX ADDRESS_IDX_DISCRIMINATOR |
---|
303 | ON ADDRESS (DISCRIMINATOR); |
---|
304 | |
---|
305 | ALTER TABLE ADDRESS ADD CONSTRAINT ADDRESS_REF_FIELD |
---|
306 | FOREIGN KEY (FIELD_OID) |
---|
307 | REFERENCES FIELD ON DELETE CASCADE; |
---|
308 | |
---|
309 | ALTER TABLE ADDRESS ADD CONSTRAINT MAILBOX_REF_GROUP |
---|
310 | FOREIGN KEY (GROUP_OID) |
---|
311 | REFERENCES ADDRESS ON DELETE CASCADE; |
---|
312 | |
---|
313 | ------------------------------------------------------------------------------- |
---|
314 | CREATE INDEX TAG_IDX_MESSAGE |
---|
315 | ON TAG (ENTITY_OID); |
---|
316 | |
---|
317 | ALTER TABLE TAG ADD CONSTRAINT TAG_REF_MESSAGE |
---|
318 | FOREIGN KEY (ENTITY_OID) |
---|
319 | REFERENCES ENTITY ON DELETE CASCADE; |
---|
320 | |
---|
321 | ------------------------------------------------------------------------------- |
---|
322 | CREATE INDEX PARAMETER_IDX_FIELD |
---|
323 | ON PARAMETER (FIELD_OID); |
---|
324 | |
---|
325 | ALTER TABLE PARAMETER ADD CONSTRAINT PARAMETER_REF_FIELD |
---|
326 | FOREIGN KEY (FIELD_OID) |
---|
327 | REFERENCES FIELD ON DELETE CASCADE; |
---|
328 | |
---|
329 | ------------------------------------------------------------------------------- |
---|
330 | CREATE SEQUENCE QUERY_CANDIDATES_SET; |
---|
331 | |
---|
332 | ------------------------------------------------------------------------------- |
---|
333 | |
---|
334 | COMMIT; |
---|
335 | |
---|