[6785] | 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 | |
---|