alter user tfr quota unlimited on FILTERS_TABLES;
alter user tfr quota unlimited on FILTERS_IDX;

--
--
--  Define the FILTERS.DOMAIN_ENTRY_POINT table.
--
--
ALTER TABLE tfr.DOMAIN_ENTRY_POINT
 DROP PRIMARY KEY CASCADE;
DROP TABLE tfr.DOMAIN_ENTRY_POINT CASCADE CONSTRAINTS;

CREATE TABLE tfr.DOMAIN_ENTRY_POINT
(
  DOMAIN_ENTRY_POINT_ID  NUMBER CONSTRAINT DOMAINENTPNT_DOMAINENTPNT_NN NOT NULL,
  NAME                   VARCHAR2(100 BYTE) CONSTRAINT DOMAINENTPNT_NAME_NN NOT NULL,
  domain                 varchar2(100 BYTE) CONSTRAINT DOMAINENTPNT_DOMAIN_NN NOT NULL
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX tfr.DOMAINENTRYPOINT_UNQ_I ON tfr.DOMAIN_ENTRY_POINT
(DOMAIN_ENTRY_POINT_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



ALTER TABLE tfr.DOMAIN_ENTRY_POINT ADD 
(
  CONSTRAINT DOMAINENTRYPOINT_PK 
  PRIMARY KEY (DOMAIN_ENTRY_POINT_ID)
);




--
--
--  Define the tfr.ENTRY_FILTER table.
--
--
ALTER TABLE tfr.ENTRY_FILTER DROP PRIMARY KEY CASCADE;
DROP TABLE tfr.ENTRY_FILTER CASCADE CONSTRAINTS;

CREATE TABLE tfr.ENTRY_FILTER
(
  ENTRY_FILTER_ID        NUMBER CONSTRAINT ENTRYFLT_ENTRYFILTERID_NN NOT NULL,
  DOMAIN_ENTRY_POINT_ID  NUMBER CONSTRAINT ENTRYFLT_DOMAINENTPNTID_NN NOT NULL,
  XPATH                  VARCHAR2(4000 BYTE)
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX tfr.ENTRYFILTER_DOMENTPNTID_FK_I ON tfr.ENTRY_FILTER
(DOMAIN_ENTRY_POINT_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX tfr.ENTRYFILTER_UNQ_I ON tfr.ENTRY_FILTER
(ENTRY_FILTER_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



ALTER TABLE tfr.ENTRY_FILTER ADD 
(
  CONSTRAINT ENTRYFILTER_PK 
  PRIMARY KEY (ENTRY_FILTER_ID)
);



--
--
--  Define the tfr.FILTER_SCHEMA table.
--
--
ALTER TABLE tfr.FILTER_SCHEMA DROP PRIMARY KEY CASCADE;
DROP TABLE tfr.FILTER_SCHEMA CASCADE CONSTRAINTS;

CREATE TABLE tfr.FILTER_SCHEMA
(
  FILTER_SCHEMA_ID      NUMBER CONSTRAINT FLTSCH_FILTERSCHEMAID_NN NOT NULL,
  FILTER_IDENTIFIER     VARCHAR2(50 BYTE) CONSTRAINT FLTSCH_FILTERID_NN NOT NULL,
  FILTER_SCHEMA_TXT     CLOB CONSTRAINT FLTSCH_FILTERSCHEMATXT_NN NOT NULL,
  CONTAINS_CORRELATION  CHAR(1 BYTE)            DEFAULT 'N' CONSTRAINT FLTSCH_CONTAINSCORRELATION_NN NOT NULL,
  FILTER_DESCRIPTION    VARCHAR2(4000),
  DATE_ADDED    DATE default sysdate,
  DATE_ACTIVATED    DATE,
  DATE_DEACTIVATED    DATE,
  DATE_UPDATED        DATE,
  DATE_DEPRECATED    DATE,
  status    number
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

select * from tfr.filter_schema;

CREATE UNIQUE INDEX tfr.FILTERSCHEMA_UNQ_I ON tfr.FILTER_SCHEMA
(FILTER_SCHEMA_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



ALTER TABLE tfr.FILTER_SCHEMA ADD 
(
  CONSTRAINT FLTSCH_CONTAINSCORRELATION_CHK
  CHECK (CONTAINS_CORRELATION in ('Y','N')), CONSTRAINT FILTERSCHEMA_PK PRIMARY KEY (FILTER_SCHEMA_ID)
);




--
--
--  Define the FILTER_SCHEMA_ENTRY_FILTER table.
--
--
ALTER TABLE tfr.FILTER_SCHEMA_ENTRY_FILTER
  DROP PRIMARY KEY CASCADE;
DROP TABLE tfr.FILTER_SCHEMA_ENTRY_FILTER CASCADE CONSTRAINTS;

CREATE TABLE tfr.FILTER_SCHEMA_ENTRY_FILTER
(
  FILTER_SCHEMA_ID  NUMBER CONSTRAINT FLTSCHENTFLT_FILTERSCHEMAID_NN NOT NULL,
  ENTRY_FILTER_ID   NUMBER CONSTRAINT FLTSCHENTFLT_ENTRYFILTERID_NN NOT NULL
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX tfr.FLTSCHENTFLT_UNQ_I ON tfr.FILTER_SCHEMA_ENTRY_FILTER
(FILTER_SCHEMA_ID, ENTRY_FILTER_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;




ALTER TABLE tfr.FILTER_SCHEMA_ENTRY_FILTER ADD 
(
  CONSTRAINT FILTERSCHEMAENTRYFILTER_PK
  PRIMARY KEY (FILTER_SCHEMA_ID, ENTRY_FILTER_ID)
);




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

ALTER TABLE TFR.TEMPLATE_SCHEMA_VHIM_VERSION
 DROP PRIMARY KEY CASCADE;
DROP TABLE TFR.TEMPLATE_SCHEMA_VHIM_VERSION CASCADE CONSTRAINTS;

CREATE TABLE TFR.TEMPLATE_SCHEMA_VHIM_VERSION
(
  TEMPLATE_SCHEMA_ID  NUMBER                    NOT NULL,
  VHIM_VERSION_ID     NUMBER                    NOT NULL
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX TFR.TEMPLATE_SCHEMA_VHIM_VERSIONPK ON TFR.TEMPLATE_SCHEMA_VHIM_VERSION
(TEMPLATE_SCHEMA_ID, VHIM_VERSION_ID)
LOGGING
TABLESPACE FILTERS_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE TFR.TEMPLATE_SCHEMA_VHIM_VERSION ADD (
  CONSTRAINT TEMPLATE_SCHEMA_VHIM_VERSIONPK
 PRIMARY KEY
 (TEMPLATE_SCHEMA_ID, VHIM_VERSION_ID)
    USING INDEX 
    TABLESPACE FILTERS_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));


ALTER TABLE TFR.FILTER_SCHEMA_VHIM_VERSION
 DROP PRIMARY KEY CASCADE;
DROP TABLE TFR.FILTER_SCHEMA_VHIM_VERSION CASCADE CONSTRAINTS;

CREATE TABLE TFR.FILTER_SCHEMA_VHIM_VERSION
(
  FILTER_SCHEMA_ID  NUMBER                    NOT NULL,
  VHIM_VERSION_ID     NUMBER                    NOT NULL
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX TFR.FILTER_SCHEMA_VHIM_VERSIONPK ON TFR.FILTER_SCHEMA_VHIM_VERSION
(FILTER_SCHEMA_ID, VHIM_VERSION_ID)
LOGGING
TABLESPACE FILTERS_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE TFR.FILTER_SCHEMA_VHIM_VERSION ADD (
  CONSTRAINT FILTER_SCHEMA_VHIM_VERSIONPK
 PRIMARY KEY
 (FILTER_SCHEMA_ID, VHIM_VERSION_ID)
    USING INDEX 
    TABLESPACE FILTERS_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));


----------------------
--
--
--  Define the VHIM_VERSION table.
--
--
ALTER TABLE tfr.VHIM_VERSION
  DROP PRIMARY KEY CASCADE;
DROP TABLE tfr.VHIM_VERSION CASCADE CONSTRAINTS;

CREATE TABLE tfr.VHIM_VERSION
(
  VHIM_VERSION_ID   NUMBER CONSTRAINT VHIMVERSION_VHIMVERSIONID_NN NOT NULL,
  VHIM_VERSION_TXT  VARCHAR2(50 BYTE) CONSTRAINT VHIMVERSION_VHIMVERSIONTXT_NN NOT NULL
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX tfr.VHIMVERSION_UNQ_I ON tfr.VHIM_VERSION
(VHIM_VERSION_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



ALTER TABLE tfr.VHIM_VERSION ADD 
(
  CONSTRAINT VHIMVERSION_PK
  PRIMARY KEY (VHIM_VERSION_ID)
);




--  ***



ALTER TABLE tfr.TEMPLATE_SCHEMA
 DROP PRIMARY KEY CASCADE;
DROP TABLE tfr.TEMPLATE_SCHEMA CASCADE CONSTRAINTS;

CREATE TABLE tfr.TEMPLATE_SCHEMA
(
  TEMPLATE_SCHEMA_ID    NUMBER CONSTRAINT TEMPSCHEMA_TEMPSCHMID_NN NOT NULL,
  TEMPLATE_ID           VARCHAR2(100 BYTE) CONSTRAINT TEMPSCHEMA_TEMPID_NN NOT NULL,
  REQUEST_TYPE          VARCHAR2(4000 BYTE),
  TEMPLATE_NAME         VARCHAR2(4000 BYTE),
  TEMPLATE_DESCRIPTION  VARCHAR2(4000 BYTE),
  TEST_ONLY             CHAR(1 BYTE)            DEFAULT 'F',
  TEMPLATE_SCHEMA_BLOB  BLOB CONSTRAINT TEMPSCHEMA_TEMPSCHEMABLB_NN NOT NULL,
  DATE_ADDED    DATE default sysdate,
  DATE_ACTIVATED    DATE,
  DATE_DEACTIVATED    DATE,
  DATE_UPDATED        DATE,
  DATE_DEPRECATED    DATE,
  status    number
)
TABLESPACE FILTERS_IDX
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
LOB (TEMPLATE_SCHEMA_BLOB) STORE AS 
      ( TABLESPACE  FILTERS_IDX 
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  0
        CACHE
        STORAGE    (
                    INITIAL          10M
                    NEXT             10M
                    MINEXTENTS       1
                    MAXEXTENTS       UNLIMITED
                    PCTINCREASE      0
                    BUFFER_POOL      DEFAULT
                   )
      )
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX tfr.TEMPLATE_SCHEMA_PK ON tfr.TEMPLATE_SCHEMA
(TEMPLATE_SCHEMA_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;




ALTER TABLE tfr.TEMPLATE_SCHEMA ADD (
  CONSTRAINT TEMPLATE_SCHEMA_PK
 PRIMARY KEY
 (TEMPLATE_SCHEMA_ID)
    USING INDEX 
    TABLESPACE FILTERS_IDX
    PCTFREE    0
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          10M
                NEXT             10M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));







--- ***************

--
--
--  Define the TEMPLATE_SCHEMA_ENTRY_POINT table.
--
--
ALTER TABLE tfr.TEMPLATE_SCHEMA_ENTRY_POINT
  DROP PRIMARY KEY CASCADE;
DROP TABLE tfr.TEMPLATE_SCHEMA_ENTRY_POINT CASCADE CONSTRAINTS;

CREATE TABLE tfr.TEMPLATE_SCHEMA_ENTRY_POINT
(
  TEMPLATE_SCHEMA_ID  NUMBER CONSTRAINT TMPSCHENTPNT_TEMPSCHEMAID_NN NOT NULL,
  DOMAIN_ENTRY_POINT_ID   NUMBER CONSTRAINT TMPSCHENTPNT_DOMENTPNTID_NN NOT NULL
)
TABLESPACE FILTERS_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX tfr.TMPSCHENTPNT_UNQ_I ON tfr.TEMPLATE_SCHEMA_ENTRY_POINT
(TEMPLATE_SCHEMA_ID, DOMAIN_ENTRY_POINT_ID)
LOGGING
TABLESPACE FILTERS_IDX
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;




ALTER TABLE tfr.TEMPLATE_SCHEMA_ENTRY_POINT ADD 
(
  CONSTRAINT TEMPLATESCHEMAENTRYPOINT_PK
  PRIMARY KEY (TEMPLATE_SCHEMA_ID, DOMAIN_ENTRY_POINT_ID)
);






--- *************************


ALTER TABLE tfr.ENTRY_FILTER ADD 
(
  CONSTRAINT ENTFLT_DMNTPNT_DMNTPNTID_FK 
  FOREIGN KEY (DOMAIN_ENTRY_POINT_ID) REFERENCES tfr.DOMAIN_ENTRY_POINT (DOMAIN_ENTRY_POINT_ID)
);



ALTER TABLE tfr.FILTER_SCHEMA_ENTRY_FILTER ADD 
(
  CONSTRAINT FLTSCHENFLT_ENTFLT_ENTFLTID_FK 
  FOREIGN KEY (ENTRY_FILTER_ID) REFERENCES tfr.ENTRY_FILTER (ENTRY_FILTER_ID),
  CONSTRAINT FLTSCHENFLT_FLTSCH_FLTSCHID_FK 
  FOREIGN KEY (FILTER_SCHEMA_ID) REFERENCES tfr.FILTER_SCHEMA (FILTER_SCHEMA_ID)
);


ALTER TABLE tfr.TEMPLATE_SCHEMA_ENTRY_POINT ADD 
(
  CONSTRAINT TMPSCHENPT_DMENTPT_DMTPTID_FK 
  FOREIGN KEY (DOMAIN_ENTRY_POINT_ID) REFERENCES tfr.DOMAIN_ENTRY_POINT (DOMAIN_ENTRY_POINT_ID),
  CONSTRAINT TMPSCHENPT_TMPSCH_TMPSCHID_FK 
  FOREIGN KEY (TEMPLATE_SCHEMA_ID) REFERENCES tfr.TEMPLATE_SCHEMA (TEMPLATE_SCHEMA_ID)
);


ALTER TABLE tfr.template_schema_vhim_version ADD 
(
  CONSTRAINT template_schema_vhim_versi_r01
  FOREIGN KEY (template_schema_id) REFERENCES tfr.template_schema (template_schema_id),
  CONSTRAINT template_schema_vhim_versi_r02
  FOREIGN KEY (vhim_version_id) REFERENCES tfr.vhim_version (vhim_version_id)
);

ALTER TABLE tfr.filter_schema_vhim_version ADD 
(
  CONSTRAINT filter_schema_vhim_versi_r01
  FOREIGN KEY (filter_schema_id) REFERENCES tfr.filter_schema (filter_schema_id),
  CONSTRAINT filter_schema_vhim_versi_r02
  FOREIGN KEY (vhim_version_id) REFERENCES tfr.vhim_version (vhim_version_id)
);

DROP SEQUENCE TEMPLATE_SCHEMA_SEQ_ID;
CREATE SEQUENCE TEMPLATE_SCHEMA_SEQ_ID;

DROP SEQUENCE  FILTER_SCHEMA_SEQ_ID;
CREATE SEQUENCE FILTER_SCHEMA_SEQ_ID;


COMMIT;
