--
--  Expresso Mail Archiver
--
--  Script H2 de criacao do banco de dados
--
--  (consultar http://www.h2database.com)
--

-------------------------------------------------------------------------------
CREATE SCHEMA METAARCHIVE;
SET SCHEMA METAARCHIVE;

-------------------------------------------------------------------------------
-- Table METAARCHIVE.FOLDER for class
--serpro.mailarchiver.domain.metaarchive.Folder

CREATE TABLE FOLDER (

OID VARCHAR(36) NOT NULL,

FOLDER_NAME VARCHAR(1000) NOT NULL,

PARENT_OID VARCHAR(36),
PARENT_IDX INT,

CONSTRAINT FOLDER_PK PRIMARY KEY (OID)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.SETTING for class
--serpro.mailarchiver.domain.metaarchive.Setting

CREATE TABLE SETTING (

ATTR_NAME VARCHAR(100) NOT NULL,
ATTR_VALUE VARCHAR(1000) NOT NULL,

CONSTRAINT SETTING_PK PRIMARY KEY (ATTR_NAME)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.USER for class
--serpro.mailarchiver.domain.metaarchive.User

CREATE TABLE USER (

USER_ID VARCHAR(100) NOT NULL,
PASSWORD VARCHAR(1000) NOT NULL,

CONSTRAINT USER_PK PRIMARY KEY (USER_ID)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.ENTITY for classes                   DISC
--serpro.mailarchiver.domain.metaarchive.Message          1
--serpro.mailarchiver.domain.metaarchive.BodyPart         2
--serpro.mailarchiver.domain.metaarchive.EmbeddedMessage  3
--serpro.mailarchiver.domain.metaarchive.Entity           101
--serpro.mailarchiver.domain.metaarchive.BodyEntity       102

CREATE TABLE ENTITY (

DISCRIMINATOR VARCHAR(40) NOT NULL,

OID VARCHAR(36) NOT NULL,

COMPOSITE_OID VARCHAR(36),
COMPOSITE_IDX INT,

FOLDER_OID VARCHAR(36),
FOLDER_IDX INT,

START_LINE INT,
SEPARATOR_LINE INT,
END_LINE INT,

ENTITY_SIZE INT,

QUERY_CANDIDATES_SET BIGINT,

CONSTRAINT ENTITY_PK PRIMARY KEY (OID)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.BODY for classes                 DISC
--serpro.mailarchiver.domain.metaarchive.TextBody     1
--serpro.mailarchiver.domain.metaarchive.BinaryBody   2
--serpro.mailarchiver.domain.metaarchive.Multipart    3
--serpro.mailarchiver.domain.metaarchive.MessageBody  4
--serpro.mailarchiver.domain.metaarchive.Body         201
--serpro.mailarchiver.domain.metaarchive.SingleBody   202

CREATE TABLE BODY (

DISCRIMINATOR VARCHAR(40) NOT NULL,

OID VARCHAR(36) NOT NULL,

ENTITY_OID VARCHAR(36) NOT NULL,

BODY_OFFSET INT,
BODY_LENGTH INT,

BODY_SIZE INT,

PREVIEW VARCHAR(10000),

PREAMBLE VARCHAR(10000),
EPILOGUE VARCHAR(10000),

CONSTRAINT BODY_PK PRIMARY KEY (OID)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.FIELD for classes                            DISC
--serpro.mailarchiver.domain.metaarchive.UnstructuredField        1
--serpro.mailarchiver.domain.metaarchive.ContentTypeField         2
--serpro.mailarchiver.domain.metaarchive.ContentDispositionField  3
--serpro.mailarchiver.domain.metaarchive.DateTimeField            4
--serpro.mailarchiver.domain.metaarchive.AddressListField         5
--serpro.mailarchiver.domain.metaarchive.MailboxListField         6
--serpro.mailarchiver.domain.metaarchive.MailboxField             7
--serpro.mailarchiver.domain.metaarchive.Field                    301

CREATE TABLE FIELD (

DISCRIMINATOR VARCHAR(40) NOT NULL,

OID VARCHAR(36) NOT NULL,

ENTITY_OID VARCHAR(36) NOT NULL,
ENTITY_IDX INT NOT NULL,

FIELD_NAME VARCHAR(1000) NOT NULL,

TEXT VARCHAR(10000),

MEDIA_TYPE VARCHAR(100),
SUB_TYPE VARCHAR(100),

DISPOSITION_TYPE VARCHAR(100),

DATE_TIME TIMESTAMP,

VALID BOOLEAN NOT NULL,
PARSE_EXCEPTION_STACK_TRACE VARCHAR(100000),

CONSTRAINT FIELD_PK PRIMARY KEY (OID)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.ADDRESS for classes                                 DISC
--serpro.mailarchiver.domain.metaarchive.AddressListField_Mailbox        1
--serpro.mailarchiver.domain.metaarchive.MailboxListField_Mailbox        2
--serpro.mailarchiver.domain.metaarchive.MailboxField_Mailbox            3
--serpro.mailarchiver.domain.metaarchive.AddressListField_Group_Mailbox  4
--serpro.mailarchiver.domain.metaarchive.AddressListField_Group          5
--serpro.mailarchiver.domain.metaarchive.Address                         401
--serpro.mailarchiver.domain.metaarchive.FieldAddress                    402
--serpro.mailarchiver.domain.metaarchive.AddressListField_Address        403

CREATE TABLE ADDRESS (

DISCRIMINATOR VARCHAR(40) NOT NULL,

OID VARCHAR(36) NOT NULL,

FIELD_OID VARCHAR(36),
FIELD_IDX INT,

GROUP_OID VARCHAR(36),
GROUP_IDX INT,

ADDR_NAME VARCHAR(1000),

MBX_LOCAL_PART VARCHAR(1000),
MBX_DOMAIN VARCHAR(1000),
MBX_ROUTE VARCHAR(10000),

CONSTRAINT ADDRESS_PK PRIMARY KEY (OID)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.TAG for set
--serpro.mailarchiver.domain.metaarchive.Message#tags

CREATE TABLE METAARCHIVE.TAG
(

ENTITY_OID VARCHAR(36) NOT NULL,
TAG_VALUE VARCHAR(100) NOT NULL,

CONSTRAINT TAG_PK PRIMARY KEY (ENTITY_OID, TAG_VALUE)

);

-------------------------------------------------------------------------------
-- Table METAARCHIVE.PARAMETER for maps
--serpro.mailarchiver.domain.metaarchive.ContentDispositionField#parameters
--serpro.mailarchiver.domain.metaarchive.ContentTypeField#parameters

CREATE TABLE PARAMETER (

FIELD_OID VARCHAR(36) NOT NULL,
ATTR_NAME VARCHAR(100) NOT NULL,
ATTR_VALUE VARCHAR(1000),

CONSTRAINT PARAMETER_PK PRIMARY KEY (FIELD_OID, ATTR_NAME)

);

-------------------------------------------------------------------------------
CREATE INDEX FOLDER_IDX_PARENT_FOLDER
  ON FOLDER (PARENT_OID);

CREATE INDEX FOLDER_IDX_NAME
  ON FOLDER (FOLDER_NAME);

ALTER TABLE FOLDER ADD CONSTRAINT FOLDER_REF_PARENT_FOLDER
  FOREIGN KEY (PARENT_OID)
  REFERENCES FOLDER ON DELETE CASCADE;

-------------------------------------------------------------------------------
CREATE INDEX MESSAGE_IDX_FOLDER
  ON ENTITY (FOLDER_OID);

CREATE INDEX BODY_ENTITY_IDX_COMPOSITE
  ON ENTITY (COMPOSITE_OID);

CREATE INDEX MESSAGE_IDX_QUERY_CANDIDATES_SET
  ON ENTITY (QUERY_CANDIDATES_SET);

CREATE INDEX ENTITY_IDX_DISCRIMINATOR
  ON ENTITY (DISCRIMINATOR);

ALTER TABLE ENTITY ADD CONSTRAINT MESSAGE_REF_FOLDER
  FOREIGN KEY (FOLDER_OID)
  REFERENCES FOLDER ON DELETE CASCADE;

ALTER TABLE ENTITY ADD CONSTRAINT BODY_ENTITY_REF_COMPOSITE
  FOREIGN KEY (COMPOSITE_OID)
  REFERENCES BODY ON DELETE CASCADE;

-------------------------------------------------------------------------------
CREATE INDEX BODY_IDX_ENTITY
  ON BODY (ENTITY_OID);

CREATE INDEX BODY_IDX_DISCRIMINATOR
  ON BODY (DISCRIMINATOR);

ALTER TABLE BODY ADD CONSTRAINT BODY_REF_ENTITY
  FOREIGN KEY (ENTITY_OID)
  REFERENCES ENTITY ON DELETE CASCADE;

-------------------------------------------------------------------------------
CREATE INDEX FIELD_IDX_ENTITY
  ON FIELD (ENTITY_OID);

CREATE INDEX FIELD_IDX_NAME
  ON FIELD (FIELD_NAME);

CREATE INDEX FIELD_IDX_TEXT
  ON FIELD (TEXT);

CREATE INDEX FIELD_IDX_DATE_TIME
  ON FIELD (DATE_TIME);

CREATE INDEX FIELD_IDX_DISCRIMINATOR
  ON FIELD (DISCRIMINATOR);

ALTER TABLE FIELD ADD CONSTRAINT FIELD_REF_ENTITY
  FOREIGN KEY (ENTITY_OID)
  REFERENCES ENTITY ON DELETE CASCADE;

-------------------------------------------------------------------------------
-- MAILBOX REF MAILBOX_FIELD
-- MAILBOX REF MAILBOX_LIST_FIELD
-- ADDRESS REF ADDRESS_LIST_FIELD

CREATE INDEX ADDRESS_IDX_FIELD
  ON ADDRESS (FIELD_OID);

CREATE INDEX MAILBOX_IDX_GROUP
  ON ADDRESS (GROUP_OID);

CREATE INDEX ADDRESS_IDX_NAME
  ON ADDRESS (ADDR_NAME);

CREATE INDEX ADDRESS_IDX_LOCAL_PART
  ON ADDRESS (MBX_LOCAL_PART);

CREATE INDEX ADDRESS_IDX_DOMAIN
  ON ADDRESS (MBX_DOMAIN);

CREATE INDEX ADDRESS_IDX_DISCRIMINATOR
  ON ADDRESS (DISCRIMINATOR);

ALTER TABLE ADDRESS ADD CONSTRAINT ADDRESS_REF_FIELD
  FOREIGN KEY (FIELD_OID)
  REFERENCES FIELD ON DELETE CASCADE;

ALTER TABLE ADDRESS ADD CONSTRAINT MAILBOX_REF_GROUP
  FOREIGN KEY (GROUP_OID)
  REFERENCES ADDRESS ON DELETE CASCADE;

-------------------------------------------------------------------------------
CREATE INDEX TAG_IDX_MESSAGE
  ON TAG (ENTITY_OID);

ALTER TABLE TAG ADD CONSTRAINT TAG_REF_MESSAGE
  FOREIGN KEY (ENTITY_OID)
  REFERENCES ENTITY ON DELETE CASCADE;

-------------------------------------------------------------------------------
CREATE INDEX PARAMETER_IDX_FIELD
  ON PARAMETER (FIELD_OID);

ALTER TABLE PARAMETER ADD CONSTRAINT PARAMETER_REF_FIELD
  FOREIGN KEY (FIELD_OID)
  REFERENCES FIELD ON DELETE CASCADE;

-------------------------------------------------------------------------------
CREATE SEQUENCE QUERY_CANDIDATES_SET;

-------------------------------------------------------------------------------

COMMIT;