------------------------------------------------------------------
-- DataNucleus SchemaTool (ran at 12/03/2012 13:02:28)
------------------------------------------------------------------
-- Complete schema required for the following classes:-
--     serpro.mailarchiver.domain.metaarchive.Address
--     serpro.mailarchiver.domain.metaarchive.AddressListField
--     serpro.mailarchiver.domain.metaarchive.AddressListField_Address
--     serpro.mailarchiver.domain.metaarchive.AddressListField_Group
--     serpro.mailarchiver.domain.metaarchive.AddressListField_Group_Mailbox
--     serpro.mailarchiver.domain.metaarchive.AddressListField_Mailbox
--     serpro.mailarchiver.domain.metaarchive.BinaryBody
--     serpro.mailarchiver.domain.metaarchive.Body
--     serpro.mailarchiver.domain.metaarchive.BodyEntity
--     serpro.mailarchiver.domain.metaarchive.BodyPart
--     serpro.mailarchiver.domain.metaarchive.ContentDispositionField
--     serpro.mailarchiver.domain.metaarchive.ContentTypeField
--     serpro.mailarchiver.domain.metaarchive.DateTimeField
--     serpro.mailarchiver.domain.metaarchive.EmbeddedMessage
--     serpro.mailarchiver.domain.metaarchive.Entity
--     serpro.mailarchiver.domain.metaarchive.Field
--     serpro.mailarchiver.domain.metaarchive.FieldAddress
--     serpro.mailarchiver.domain.metaarchive.Folder
--     serpro.mailarchiver.domain.metaarchive.MailboxField
--     serpro.mailarchiver.domain.metaarchive.MailboxField_Mailbox
--     serpro.mailarchiver.domain.metaarchive.MailboxListField
--     serpro.mailarchiver.domain.metaarchive.MailboxListField_Mailbox
--     serpro.mailarchiver.domain.metaarchive.Message
--     serpro.mailarchiver.domain.metaarchive.MessageBody
--     serpro.mailarchiver.domain.metaarchive.Multipart
--     serpro.mailarchiver.domain.metaarchive.Setting
--     serpro.mailarchiver.domain.metaarchive.SingleBody
--     serpro.mailarchiver.domain.metaarchive.TextBody
--     serpro.mailarchiver.domain.metaarchive.UnstructuredField
--     serpro.mailarchiver.domain.metaarchive.User
--     serpro.mailarchiver.service.Settings
--     serpro.mailarchiver.service.Utils
--     serpro.mailarchiver.service.find.FBinaryBody
--     serpro.mailarchiver.service.find.FFolder
--     serpro.mailarchiver.service.find.FMessage
--     serpro.mailarchiver.service.find.FTextBody
--     serpro.mailarchiver.service.find.FUser
--     serpro.mailarchiver.service.web.DefaultArchiveOperation
--     serpro.mailarchiver.service.web.DefaultArchiveServices
--     serpro.mailarchiver.service.web.DefaultCopyMessagesOperation
--     serpro.mailarchiver.service.web.DefaultCreateAbsoluteFolderOperation
--     serpro.mailarchiver.service.web.DefaultCreateFolderOperation
--     serpro.mailarchiver.service.web.DefaultDeleteFolderOperation
--     serpro.mailarchiver.service.web.DefaultDeleteMessagesOperation
--     serpro.mailarchiver.service.web.DefaultGetFaultInfoOperation
--     serpro.mailarchiver.service.web.DefaultGetFolderInfoOperation
--     serpro.mailarchiver.service.web.DefaultGetMessageBodyOperation
--     serpro.mailarchiver.service.web.DefaultGetMessageInfoOperation
--     serpro.mailarchiver.service.web.DefaultGetRawBinaryBodyOperation
--     serpro.mailarchiver.service.web.DefaultGetStateOperation
--     serpro.mailarchiver.service.web.DefaultListAttachmentsOperation
--     serpro.mailarchiver.service.web.DefaultListFoldersOperation
--     serpro.mailarchiver.service.web.DefaultListMessagesOperation
--     serpro.mailarchiver.service.web.DefaultLoginOperation
--     serpro.mailarchiver.service.web.DefaultLogoutOperation
--     serpro.mailarchiver.service.web.DefaultMoveFolderOperation
--     serpro.mailarchiver.service.web.DefaultMoveMessagesOperation
--     serpro.mailarchiver.service.web.DefaultRenameFolderOperation
--     serpro.mailarchiver.service.web.DefaultTagMessagesOperation
--     serpro.mailarchiver.service.web.DefaultZipMessagesOperation
--     serpro.mailarchiver.service.web.ServiceFault
--
-- Table METAARCHIVE.ADDRESS for classes [serpro.mailarchiver.domain.metaarchive.AddressListField_Group_Mailbox, serpro.mailarchiver.domain.metaarchive.Address, serpro.mailarchiver.domain.metaarchive.FieldAddress, serpro.mailarchiver.domain.metaarchive.MailboxListField_Mailbox, serpro.mailarchiver.domain.metaarchive.AddressListField_Mailbox, serpro.mailarchiver.domain.metaarchive.MailboxField_Mailbox, serpro.mailarchiver.domain.metaarchive.AddressListField_Group, serpro.mailarchiver.domain.metaarchive.AddressListField_Address]
CREATE TABLE METAARCHIVE.ADDRESS
(
    OID VARCHAR(36) NOT NULL,
    MBX_DOMAIN VARCHAR(1000) NULL,
    MBX_LOCAL_PART VARCHAR(1000) NULL,
    ADDR_NAME VARCHAR(1000) NULL,
    MBX_ROUTE VARCHAR(10000) NULL,
    DISCRIMINATOR VARCHAR(40) NOT NULL,
    GROUP_OID VARCHAR(36) NULL,
    GROUP_IDX INTEGER NULL,
    FIELD_OID VARCHAR(36) NULL,
    FIELD_IDX INTEGER NULL,
    CONSTRAINT ADDRESS_PK PRIMARY KEY (OID)
);

-- Table METAARCHIVE.FIELD for classes [serpro.mailarchiver.domain.metaarchive.DateTimeField, serpro.mailarchiver.domain.metaarchive.ContentTypeField, serpro.mailarchiver.domain.metaarchive.Field, serpro.mailarchiver.domain.metaarchive.AddressListField, serpro.mailarchiver.domain.metaarchive.ContentDispositionField, serpro.mailarchiver.domain.metaarchive.UnstructuredField, serpro.mailarchiver.domain.metaarchive.MailboxField, serpro.mailarchiver.domain.metaarchive.MailboxListField]
CREATE TABLE METAARCHIVE.FIELD
(
    OID VARCHAR(36) NOT NULL,
    ENTITY_OID VARCHAR(36) NOT NULL,
    ENTITY_IDX INTEGER NOT NULL,
    FIELD_NAME VARCHAR(1000) NOT NULL,
    PARSE_EXCEPTION_STACK_TRACE VARCHAR(100000) NULL,
    VALID BOOLEAN NOT NULL,
    DISCRIMINATOR VARCHAR(40) NOT NULL,
    MEDIA_TYPE VARCHAR(100) NULL,
    SUB_TYPE VARCHAR(100) NULL,
    DATE_TIME TIMESTAMP NULL,
    DISPOSITION_TYPE VARCHAR(100) NULL,
    TEXT VARCHAR(10000) NULL,
    CONSTRAINT FIELD_PK PRIMARY KEY (OID)
);

-- Table METAARCHIVE.BODY for classes [serpro.mailarchiver.domain.metaarchive.Body, serpro.mailarchiver.domain.metaarchive.SingleBody, serpro.mailarchiver.domain.metaarchive.Multipart, serpro.mailarchiver.domain.metaarchive.MessageBody, serpro.mailarchiver.domain.metaarchive.BinaryBody, serpro.mailarchiver.domain.metaarchive.TextBody]
CREATE TABLE METAARCHIVE.BODY
(
    OID VARCHAR(36) NOT NULL,
    ENTITY_OID VARCHAR(36) NOT NULL,
    DISCRIMINATOR VARCHAR(40) NOT NULL,
    PREVIEW VARCHAR(10000) NULL,
    EPILOGUE VARCHAR(10000) NULL,
    PREAMBLE VARCHAR(10000) NULL,
    BODY_LENGTH INTEGER NULL,
    BODY_OFFSET INTEGER NULL,
    BODY_SIZE INTEGER NULL,
    CONSTRAINT BODY_PK PRIMARY KEY (OID)
);

-- Table METAARCHIVE."USER" for classes [serpro.mailarchiver.domain.metaarchive.User]
CREATE TABLE METAARCHIVE."USER"
(
    USER_ID VARCHAR(100) NOT NULL,
    PASSWORD VARCHAR(1000) NOT NULL,
    CONSTRAINT USER_PK PRIMARY KEY (USER_ID)
);

-- Table METAARCHIVE.ENTITY for classes [serpro.mailarchiver.domain.metaarchive.BodyEntity, serpro.mailarchiver.domain.metaarchive.Message, serpro.mailarchiver.domain.metaarchive.EmbeddedMessage, serpro.mailarchiver.domain.metaarchive.BodyPart, serpro.mailarchiver.domain.metaarchive.Entity]
CREATE TABLE METAARCHIVE.ENTITY
(
    OID VARCHAR(36) NOT NULL,
    END_LINE INTEGER NULL,
    SEPARATOR_LINE INTEGER NULL,
    ENTITY_SIZE INTEGER NULL,
    START_LINE INTEGER NULL,
    DISCRIMINATOR VARCHAR(40) NOT NULL,
    FOLDER_OID VARCHAR(36) NULL,
    FOLDER_IDX INTEGER NULL,
    QUERY_CANDIDATES_SET BIGINT NULL,
    COMPOSITE_OID VARCHAR(36) NULL,
    COMPOSITE_IDX INTEGER NULL,
    CONSTRAINT ENTITY_PK PRIMARY KEY (OID)
);

-- Table METAARCHIVE.SETTING for classes [serpro.mailarchiver.domain.metaarchive.Setting]
CREATE TABLE METAARCHIVE.SETTING
(
    ATTR_NAME VARCHAR(100) NOT NULL,
    ATTR_VALUE VARCHAR(1000) NOT NULL,
    CONSTRAINT SETTING_PK PRIMARY KEY (ATTR_NAME)
);

-- Table METAARCHIVE.FOLDER for classes [serpro.mailarchiver.domain.metaarchive.Folder]
CREATE TABLE METAARCHIVE.FOLDER
(
    OID VARCHAR(36) NOT NULL,
    FOLDER_NAME VARCHAR(1000) NOT NULL,
    PARENT_OID VARCHAR(36) NULL,
    PARENT_IDX INTEGER NULL,
    CONSTRAINT FOLDER_PK PRIMARY KEY (OID)
);

-- Table METAARCHIVE."PARAMETER" for join relationship
CREATE TABLE METAARCHIVE."PARAMETER"
(
    FIELD_OID VARCHAR(36) NOT NULL,
    ATTR_NAME VARCHAR(100) NOT NULL,
    ATTR_VALUE VARCHAR(1000) NULL,
    CONSTRAINT PARAMETER_PK PRIMARY KEY (FIELD_OID,ATTR_NAME)
);

-- Table METAARCHIVE.TAG for join relationship
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)
);

-- Constraints for table METAARCHIVE.ADDRESS for class(es) [serpro.mailarchiver.domain.metaarchive.AddressListField_Group_Mailbox, serpro.mailarchiver.domain.metaarchive.Address, serpro.mailarchiver.domain.metaarchive.FieldAddress, serpro.mailarchiver.domain.metaarchive.MailboxListField_Mailbox, serpro.mailarchiver.domain.metaarchive.AddressListField_Mailbox, serpro.mailarchiver.domain.metaarchive.MailboxField_Mailbox, serpro.mailarchiver.domain.metaarchive.AddressListField_Group, serpro.mailarchiver.domain.metaarchive.AddressListField_Address]
ALTER TABLE METAARCHIVE.ADDRESS ADD CONSTRAINT ADDRESS_FK1 FOREIGN KEY (GROUP_OID) REFERENCES METAARCHIVE.ADDRESS (OID) ;

ALTER TABLE METAARCHIVE.ADDRESS ADD CONSTRAINT ADDRESS_FK2 FOREIGN KEY (FIELD_OID) REFERENCES METAARCHIVE.FIELD (OID) ;

CREATE INDEX ADDRESS_N49 ON METAARCHIVE.ADDRESS (GROUP_OID);

CREATE INDEX ADDRESS_N50 ON METAARCHIVE.ADDRESS (FIELD_OID);


-- Constraints for table METAARCHIVE.FIELD for class(es) [serpro.mailarchiver.domain.metaarchive.DateTimeField, serpro.mailarchiver.domain.metaarchive.ContentTypeField, serpro.mailarchiver.domain.metaarchive.Field, serpro.mailarchiver.domain.metaarchive.AddressListField, serpro.mailarchiver.domain.metaarchive.ContentDispositionField, serpro.mailarchiver.domain.metaarchive.UnstructuredField, serpro.mailarchiver.domain.metaarchive.MailboxField, serpro.mailarchiver.domain.metaarchive.MailboxListField]
ALTER TABLE METAARCHIVE.FIELD ADD CONSTRAINT FIELD_FK1 FOREIGN KEY (ENTITY_OID) REFERENCES METAARCHIVE.ENTITY (OID) ;

CREATE INDEX FIELD_N49 ON METAARCHIVE.FIELD (ENTITY_OID);


-- Constraints for table METAARCHIVE.BODY for class(es) [serpro.mailarchiver.domain.metaarchive.Body, serpro.mailarchiver.domain.metaarchive.SingleBody, serpro.mailarchiver.domain.metaarchive.Multipart, serpro.mailarchiver.domain.metaarchive.MessageBody, serpro.mailarchiver.domain.metaarchive.BinaryBody, serpro.mailarchiver.domain.metaarchive.TextBody]
ALTER TABLE METAARCHIVE.BODY ADD CONSTRAINT BODY_FK1 FOREIGN KEY (ENTITY_OID) REFERENCES METAARCHIVE.ENTITY (OID) ;

CREATE INDEX BODY_N49 ON METAARCHIVE.BODY (ENTITY_OID);


-- Constraints for table METAARCHIVE."USER" for class(es) [serpro.mailarchiver.domain.metaarchive.User]

-- Constraints for table METAARCHIVE.ENTITY for class(es) [serpro.mailarchiver.domain.metaarchive.BodyEntity, serpro.mailarchiver.domain.metaarchive.Message, serpro.mailarchiver.domain.metaarchive.EmbeddedMessage, serpro.mailarchiver.domain.metaarchive.BodyPart, serpro.mailarchiver.domain.metaarchive.Entity]
ALTER TABLE METAARCHIVE.ENTITY ADD CONSTRAINT ENTITY_FK1 FOREIGN KEY (COMPOSITE_OID) REFERENCES METAARCHIVE.BODY (OID) ;

ALTER TABLE METAARCHIVE.ENTITY ADD CONSTRAINT ENTITY_FK2 FOREIGN KEY (FOLDER_OID) REFERENCES METAARCHIVE.FOLDER (OID) ;

CREATE INDEX ENTITY_N50 ON METAARCHIVE.ENTITY (COMPOSITE_OID);

CREATE INDEX ENTITY_N49 ON METAARCHIVE.ENTITY (FOLDER_OID);


-- Constraints for table METAARCHIVE.SETTING for class(es) [serpro.mailarchiver.domain.metaarchive.Setting]

-- Constraints for table METAARCHIVE.FOLDER for class(es) [serpro.mailarchiver.domain.metaarchive.Folder]
ALTER TABLE METAARCHIVE.FOLDER ADD CONSTRAINT FOLDER_FK1 FOREIGN KEY (PARENT_OID) REFERENCES METAARCHIVE.FOLDER (OID) ;

CREATE INDEX FOLDER_N49 ON METAARCHIVE.FOLDER (PARENT_OID);


-- Constraints for table METAARCHIVE."PARAMETER"
ALTER TABLE METAARCHIVE."PARAMETER" ADD CONSTRAINT PARAMETER_FK1 FOREIGN KEY (FIELD_OID) REFERENCES METAARCHIVE.FIELD (OID) ;

CREATE INDEX PARAMETER_N49 ON METAARCHIVE."PARAMETER" (FIELD_OID);


-- Constraints for table METAARCHIVE.TAG
ALTER TABLE METAARCHIVE.TAG ADD CONSTRAINT TAG_FK1 FOREIGN KEY (ENTITY_OID) REFERENCES METAARCHIVE.ENTITY (OID) ;

CREATE INDEX TAG_N49 ON METAARCHIVE.TAG (ENTITY_OID);



------------------------------------------------------------------
-- Sequences and SequenceTables
