etc/sql/oracle_11.sql

0001 DROP SEQUENCE MESSAGE_ID_SEQ;
0002 DROP SEQUENCE REQUEST_ID_SEQ;
0003 DROP SEQUENCE WORKPROGRESS_ID_SEQ;
0004 DROP SEQUENCE TRANSFORM_ID_SEQ;
0005 DROP SEQUENCE PROCESSING_ID_SEQ;
0006 DROP SEQUENCE COLLECTION_ID_SEQ;
0007 DROP SEQUENCE CONTENT_ID_SEQ;
0008
0009 delete from HEALTH;
0010 delete from MESSAGES;
0011 delete from CONTENTS;
0012 delete from REQ2WORKLOAD;
0013 delete from REQ2TRANSFORMS;
0014 delete from WP2TRANSFORMS;
0015 delete from WORKPROGRESSES;
0016 delete from PROCESSINGS;
0017 delete from COLLECTIONS;
0018 delete from TRANSFORMS;
0019 delete from REQUESTS;
0020
0021 Drop table HEALTH purge;
0022 DROP table MESSAGES purge;
0023 DROP table CONTENTS purge;
0024 DROP table REQ2WORKLOAD purge;
0025 DROP table REQ2TRANSFORMS purge;
0026 DROP table WP2TRANSFORMS purge;
0027 DROP table WORKPROGRESSES purge;
0028 DROP table PROCESSINGS purge;
0029 DROP table COLLECTIONS purge;
0030 DROP table TRANSFORMS purge;
0031 DROP table REQUESTS purge;
0032
0033
0034 --- requests
0035 CREATE SEQUENCE REQUEST_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER NOCACHE;
0036 CREATE TABLE REQUESTS
0037 (
0038         request_id NUMBER(12),
0039         scope VARCHAR2(25) constraint REQ_SCOPE_NN NOT NULL,
0040         name VARCHAR2(255) constraint REQ_NAME_NN NOT NULL,
0041         requester VARCHAR2(20),
0042         request_type NUMBER(2) constraint REQ_DATATYPE_NN NOT NULL,
0043         username VARCHAR2(20) default null,
0044         userdn VARCHAR2(200) default null,
0045         transform_tag VARCHAR2(10),
0046         workload_id NUMBER(10),
0047         priority NUMBER(7),
0048         status NUMBER(2) constraint REQ_STATUS_ID_NN NOT NULL,
0049         substatus NUMBER(2),
0050         locking NUMBER(2),
0051         created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_CREATED_NN NOT NULL,
0052         updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_UPDATED_NN NOT NULL,
0053         next_poll_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_NEXT_POLL_NN NOT NULL,
0054         accessed_at DATE,
0055         expired_at DATE,
0056         errors VARCHAR2(1024),
0057         request_metadata CLOB,
0058         processing_metadata CLOB,
0059         CONSTRAINT REQUESTS_PK PRIMARY KEY (request_id) USING INDEX LOCAL
0060         --- CONSTRAINT REQUESTS_NAME_SCOPE_UQ UNIQUE (name, scope, requester, request_type, transform_tag, workload_id) -- USING INDEX LOCAL,
0061 )
0062 PCTFREE 3
0063 PARTITION BY RANGE(REQUEST_ID)
0064 INTERVAL ( 100000 )
0065 ( PARTITION initial_part VALUES LESS THAN (1) );
0066
0067 CREATE OR REPLACE TRIGGER TRIG_REQUEST_ID
0068     BEFORE INSERT
0069     ON REQUESTS
0070     FOR EACH ROW
0071     BEGIN
0072         :NEW.request_id := REQUEST_ID_SEQ.NEXTVAL ;
0073     END;
0074  /
0075
0076 CREATE INDEX REQUESTS_SCOPE_NAME_IDX ON REQUESTS (name, scope, workload_id) LOCAL;
0077 --- drop index REQUESTS_STATUS_PRIORITY_IDX
0078 CREATE INDEX REQUESTS_STATUS_PRIORITY_IDX ON REQUESTS (status, priority, request_id, locking, updated_at, next_poll_at, created_at) LOCAL COMPRESS 1;
0079
0080 --alter table REQUESTS add (username VARCHAR2(20) default null);
0081 --alter table REQUESTS add (userdn VARCHAR2(200) default null);
0082
0083
0084 --- workprogress
0085 CREATE SEQUENCE WORKPROGRESS_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER NOCACHE;
0086 CREATE TABLE WORKPROGRESSES
0087 (
0088         workprogress_id NUMBER(12),
0089         request_id NUMBER(12) constraint WORKPROGRESS__REQ_ID_NN NOT NULL,
0090         workload_id NUMBER(10),
0091         scope VARCHAR2(25) constraint WORKPROGRESS_SCOPE_NN NOT NULL,
0092         name VARCHAR2(255) constraint WORKPROGRESS_NAME_NN NOT NULL,
0093         priority NUMBER(7),
0094         status NUMBER(2) constraint WORKPROGRESS_STATUS_ID_NN NOT NULL,
0095         substatus NUMBER(2),
0096         locking NUMBER(2),
0097         created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint WORKPROGRESS_CREATED_NN NOT NULL,
0098         updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint WORKPROGRESS_UPDATED_NN NOT NULL,
0099         next_poll_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint WORKPROGRESS_NEXT_POLL_NN NOT NULL,
0100         accessed_at DATE,
0101         expired_at DATE,
0102         errors VARCHAR2(1024),
0103         workprogress_metadata CLOB,
0104         processing_metadata CLOB,
0105         CONSTRAINT WORKPROGRESS_PK PRIMARY KEY (workprogress_id), --- USING INDEX LOCAL,
0106         CONSTRAINT WORKPROGRESS_REQ_ID_FK FOREIGN KEY(request_id) REFERENCES REQUESTS(request_id)
0107         --- CONSTRAINT REQUESTS_NAME_SCOPE_UQ UNIQUE (name, scope, requester, request_type, transform_tag, workload_id) -- USING INDEX LOCAL,
0108 )
0109 PCTFREE 3
0110 PARTITION BY RANGE(REQUEST_ID)
0111 INTERVAL ( 100000 )
0112 ( PARTITION initial_part VALUES LESS THAN (1) );
0113
0114 CREATE OR REPLACE TRIGGER TRIG_WORKPROGRESS_ID
0115     BEFORE INSERT
0116     ON WORKPROGRESSES
0117     FOR EACH ROW
0118     BEGIN
0119         :NEW.workprogress_id := WORKPROGRESS_ID_SEQ.NEXTVAL ;
0120     END;
0121  /
0122
0123 CREATE INDEX WORKPROGRESS_SCOPE_NAME_IDX ON WORKPROGRESSES (name, scope, workprogress_id) LOCAL;
0124 --- drop index REQUESTS_STATUS_PRIORITY_IDX
0125 CREATE INDEX WORKPROGRESS_STATUS_PRI_IDX ON WORKPROGRESSES (status, priority, workprogress_id, locking, updated_at, next_poll_at, created_at) LOCAL COMPRESS 1;
0126
0127
0128 --- transforms
0129 -- CREATE SEQUENCE TRANSFORM_ID_SEQ MINVALUE 1 INCREMENT BY 1 NOORDER CACHE 3 NOCYCLE;
0130 CREATE SEQUENCE TRANSFORM_ID_SEQ MINVALUE 1 INCREMENT BY 1 NOCACHE;
0131 CREATE TABLE TRANSFORMS
0132 (
0133         transform_id NUMBER(12),
0134         request_id NUMBER(12),
0135         workload_id NUMBER(10),
0136         transform_type NUMBER(2) constraint TRANSFORM_TYPE_NN NOT NULL,
0137         transform_tag VARCHAR2(20),
0138         priority NUMBER(7),
0139         safe2get_output_from_input NUMBER(10),
0140         status NUMBER(2),
0141         substatus NUMBER(2),
0142         locking NUMBER(2),
0143         retries NUMBER(5) DEFAULT 0,
0144         created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint TRANSFORM_CREATED_NN NOT NULL,
0145         updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint TRANSFORM_UPDATED_NN NOT NULL,
0146         next_poll_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint TRANSFORM_NEXT_POLL_NN NOT NULL,
0147         started_at DATE,
0148         finished_at DATE,
0149         expired_at DATE,
0150         name VARCHAR2(255),
0151         transform_metadata CLOB,
0152         running_metadata CLOB,
0153         CONSTRAINT TRANSFORMS_PK PRIMARY KEY (transform_id)
0154 )
0155 PCTFREE 3
0156 PARTITION BY RANGE(TRANSFORM_ID)
0157 INTERVAL ( 100000 )
0158 ( PARTITION initial_part VALUES LESS THAN (1) );
0159
0160 CREATE OR REPLACE TRIGGER TRIG_TRANSFORM_ID
0161     BEFORE INSERT
0162     ON TRANSFORMS
0163     FOR EACH ROW
0164     BEGIN
0165         :NEW.transform_id := TRANSFORM_ID_SEQ.NEXTVAL ;
0166     END;
0167  /
0168
0169
0170 CREATE INDEX TRANSFORMS_TYPE_TAG_IDX ON TRANSFORMS (transform_type, transform_tag, transform_id) LOCAL;
0171 CREATE INDEX TRANSFORMS_STATUS_UPDATED_IDX ON TRANSFORMS (status, locking, updated_at, next_poll_at, created_at) LOCAL;
0172
0173 --- req2transforms
0174 CREATE TABLE REQ2TRANSFORMS
0175 (
0176         request_id NUMBER(12) constraint REQ2TRANSFORM_REQ_ID_NN NOT NULL,
0177         transform_id NUMBER(12) constraint REQ2TRANSFORM_TASK_ID_NN NOT NULL,
0178         CONSTRAINT REQ2TRANSFORM_PK PRIMARY KEY (request_id, transform_id),
0179         CONSTRAINT REQ2TRANSFORM_REQ_ID_FK FOREIGN KEY(request_id) REFERENCES REQUESTS(request_id),
0180         CONSTRAINT REQ2TRANSFORM_TRANS_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id)
0181 )
0182 PCTFREE 3
0183 PARTITION BY RANGE(REQUEST_ID)
0184 INTERVAL ( 100000 )
0185 ( PARTITION initial_part VALUES LESS THAN (1) );
0186
0187
0188 --- req2workload
0189 CREATE TABLE REQ2WORKLOAD
0190 (
0191         request_id NUMBER(12) constraint REQ2TASKS_REQ_ID_NN NOT NULL,
0192         workload_id NUMBER(12) constraint REQ2TASKS_TASK_ID_NN NOT NULL,
0193         CONSTRAINT REQ2WORKLOAD_REQ_ID_FK FOREIGN KEY(request_id) REFERENCES REQUESTS(request_id)
0194 )
0195 PCTFREE 3
0196 PARTITION BY RANGE(REQUEST_ID)
0197 INTERVAL ( 100000 )
0198 ( PARTITION initial_part VALUES LESS THAN (1) );
0199 -- PCTFREE 0
0200 -- COMPRESS FOR OLTP
0201 -- PARTITION BY REFERENCE(REQ2WORKLOAD_REQ_ID_FK);
0202
0203
0204 --- workprogress2transform
0205 CREATE TABLE WP2TRANSFORMS
0206 (
0207         workprogress_id NUMBER(12) constraint WP2TRANSFORM_WP_ID_NN NOT NULL,
0208         transform_id NUMBER(12) constraint WP2TRANSFORM_TRANS_ID_NN NOT NULL,
0209         CONSTRAINT WP2TRANSFORM_PK PRIMARY KEY (workprogress_id, transform_id),
0210         CONSTRAINT WP2TRANSFORM_WORK_ID_FK FOREIGN KEY(workprogress_id) REFERENCES WORKPROGRESSES(workprogress_id),
0211         CONSTRAINT WP2TRANSFORM_TRANS_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id)
0212 )
0213 PCTFREE 3
0214 PARTITION BY RANGE(workprogress_id)
0215 INTERVAL ( 100000 )
0216 ( PARTITION initial_part VALUES LESS THAN (1) );
0217
0218
0219 ---- processings
0220 -- CREATE SEQUENCE PROCESSING_ID_SEQ MINVALUE 1 INCREMENT BY 1 NOORDER CACHE 3 NOCYCLE;
0221 CREATE SEQUENCE PROCESSING_ID_SEQ MINVALUE 1 INCREMENT BY 1  NOCACHE;
0222 CREATE TABLE PROCESSINGS
0223 (
0224         processing_id NUMBER(12),
0225         transform_id NUMBER(12) constraint PROCESSINGS_TRANSFORM_ID_NN NOT NULL,
0226         request_id NUMBER(12),
0227         workload_id NUMBER(10),
0228         status NUMBER(2) constraint PROCESSINGS_STATUS_ID_NN NOT NULL,
0229         substatus NUMBER(2),
0230         locking NUMBER(2),
0231         submitter VARCHAR2(20),
0232         submitted_id NUMBER(12),
0233         granularity NUMBER(10),
0234         granularity_type NUMBER(2),
0235         created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint PROCESSING_CREATED_NN NOT NULL,
0236         updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint PROCESSING_UPDATED_NN NOT NULL,
0237         next_poll_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint PROCESSING_NEXT_POLL_NN NOT NULL,
0238         submitted_at DATE,
0239         finished_at DATE,
0240         expired_at DATE,
0241         processing_metadata CLOB,
0242         running_metadata CLOB,
0243         output_metadata CLOB,
0244         CONSTRAINT PROCESSINGS_PK PRIMARY KEY (processing_id),
0245         CONSTRAINT PROCESSINGS_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id)
0246 )
0247 PCTFREE 3
0248 PARTITION BY RANGE(TRANSFORM_ID)
0249 INTERVAL ( 100000 )
0250 ( PARTITION initial_part VALUES LESS THAN (1) );
0251
0252 --- PCTFREE 0
0253 -- COMPRESS FOR OLTP
0254 --- PARTITION BY REFERENCE(PROCESSINGS_TRANSFORM_ID_FK);
0255
0256 CREATE OR REPLACE TRIGGER TRIG_PROCESSING_ID
0257     BEFORE INSERT
0258     ON PROCESSINGS
0259     FOR EACH ROW
0260     BEGIN
0261         :NEW.processing_id := PROCESSING_ID_SEQ.NEXTVAL ;
0262     END;
0263  /
0264
0265 CREATE INDEX PROCESSINGS_STATUS_UPDATED_IDX ON PROCESSINGS (status, locking, updated_at, next_poll_at, created_at) LOCAL;
0266
0267
0268 --- collections
0269 -- CREATE SEQUENCE COLLECTION_ID_SEQ MINVALUE 1 INCREMENT BY 1 NOORDER CACHE 2 NOCYCLE;
0270 CREATE SEQUENCE COLLECTION_ID_SEQ MINVALUE 1 INCREMENT BY 1 NOCACHE;
0271 CREATE TABLE COLLECTIONS
0272 (
0273     coll_id NUMBER(14),
0274     coll_type NUMBER(2),
0275     transform_id NUMBER(12) constraint COLLECTION_TRANSFORM_ID_NN NOT NULL,
0276     request_id NUMBER(12),
0277     workload_id NUMBER(10),
0278     relation_type NUMBER(2), -- input, output or log of the transform,
0279     scope VARCHAR2(25) constraint COLLECTION_SCOPE_NN NOT NULL,
0280     name VARCHAR2(255) constraint COLLECTION_NAME_NN NOT NULL,
0281     bytes NUMBER(19),
0282     status NUMBER(2),
0283     substatus NUMBER(2),
0284     locking NUMBER(2),
0285     total_files NUMBER(19),
0286     storage_id NUMBER(10),
0287     new_files NUMBER(10),
0288     processed_files NUMBER(10),
0289     processing_files NUMBER(10),
0290     processing_id NUMBER(12),
0291     retries NUMBER(5) DEFAULT 0,
0292     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint COLLECTION_CREATED_NN NOT NULL,
0293     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint COLLECTION_UPDATED_NN NOT NULL,
0294     next_poll_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint COLLECTION_NEXT_POLL_NN NOT NULL,
0295     accessed_at DATE,
0296     expired_at DATE,
0297     coll_metadata CLOB,
0298     CONSTRAINT COLLECTION_PK PRIMARY KEY (coll_id), -- USING INDEX LOCAL,
0299     CONSTRAINT COLLECTION_NAME_SCOPE_UQ UNIQUE (name, scope, transform_id, relation_type), -- USING INDEX LOCAL,
0300     CONSTRAINT COLLECTION_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id)
0301 )
0302 PCTFREE 3
0303 PARTITION BY RANGE(TRANSFORM_ID)
0304 INTERVAL ( 100000 )
0305 ( PARTITION initial_part VALUES LESS THAN (1) );
0306
0307 ---PCTFREE 0
0308 ---COMPRESS FOR OLTP
0309 ---PARTITION BY REFERENCE(COLLECTION_TRANSFORM_ID_FK);
0310
0311 CREATE OR REPLACE TRIGGER TRIG_COLLECTION_ID
0312     BEFORE INSERT
0313     ON COLLECTIONS
0314     FOR EACH ROW
0315     BEGIN
0316         :NEW.coll_id := COLLECTION_ID_SEQ.NEXTVAL ;
0317     END;
0318  /
0319
0320 CREATE INDEX COLLECTIONS_STATUS_RELAT_IDX ON COLLECTIONS(status, relation_type);
0321 CREATE INDEX COLLECTIONS_TRANSFORM_IDX ON COLLECTIONS(transform_id, coll_id);
0322 CREATE INDEX COLLECTIONS_STATUS_UPDATED_IDX ON COLLECTIONS (status, locking, updated_at, next_poll_at, created_at) LOCAL;
0323
0324
0325 --- contents
0326 CREATE SEQUENCE CONTENT_ID_SEQ MINVALUE 1 INCREMENT BY 1 NOORDER CACHE 10 NOCYCLE;
0327 CREATE TABLE CONTENTS
0328 (
0329         content_id NUMBER(12),
0330         transform_id NUMBER(12) constraint CONTENT_TRANSFORM_ID_NN NOT NULL,
0331         coll_id NUMBER(14) constraint CONTENT_COLL_ID_NN NOT NULL,
0332         request_id NUMBER(12),
0333         workload_id NUMBER(10),
0334         map_id NUMBER(12) DEFAULT 0,
0335         scope VARCHAR2(25) constraint CONTENT_SCOPE_NN NOT NULL,
0336         name VARCHAR2(255) constraint CONTENT_NAME_NN NOT NULL,
0337         min_id NUMBER(7) constraint CONTENT_MIN_ID_NN NOT NULL,
0338         max_id NUMBER(7) constraint CONTENT_MAX_ID_NN NOT NULL,
0339         content_type NUMBER(2) constraint CONTENT_TYPE_NN NOT NULL,
0340         content_relation_type NUMBER(2) constraint CONTENT_RTYPE_NN NOT NULL,
0341         status NUMBER(2) constraint CONTENT_STATUS_NN NOT NULL,
0342         substatus NUMBER(2),
0343         locking NUMBER(2),
0344         bytes NUMBER(12),
0345         md5 VARCHAR2(32),
0346         adler32 VARCHAR2(8),
0347         processing_id NUMBER(12),
0348         storage_id NUMBER(10),
0349         retries NUMBER(5) DEFAULT 0,
0350         path VARCHAR2(4000),
0351         created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint CONTENT_CREATED_NN NOT NULL,
0352         updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint CONTENT_UPDATED_NN NOT NULL,
0353         accessed_at DATE,
0354         expired_at DATE,
0355         --- content_metadata CLOB,
0356         content_metadata VARCHAR2(100),
0357         ---- CONSTRAINT CONTENT_PK PRIMARY KEY (name, scope, coll_id, content_type, min_id, max_id) USING INDEX LOCAL,
0358         CONSTRAINT CONTENT_PK PRIMARY KEY (content_id),
0359         --- CONSTRAINT CONTENT_SCOPE_NAME_UQ UNIQUE (name, scope, coll_id, content_type, min_id, max_id) USING INDEX LOCAL,
0360         --- CONSTRAINT CONTENT_SCOPE_NAME_UQ UNIQUE (name, scope, coll_id, min_id, max_id) USING INDEX LOCAL,
0361         CONSTRAINT CONTENT_ID_UQ UNIQUE (transform_id, coll_id, map_id, name, min_id, max_id) USING INDEX LOCAL,
0362         CONSTRAINT CONTENT_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id),
0363         CONSTRAINT CONTENT_COLL_ID_FK FOREIGN KEY(coll_id) REFERENCES COLLECTIONS(coll_id)
0364 )
0365 PCTFREE 3
0366 PARTITION BY RANGE(TRANSFORM_ID)
0367 INTERVAL ( 1000000 )
0368 ( PARTITION initial_part VALUES LESS THAN (1) );
0369
0370 alter table contents modify (min_id NUMBER(7) default 0)
0371 alter table contents modify (max_id NUMBER(7) default 0)
0372 alter table contents add content_relation_type NUMBER(2) default 0
0373
0374 ---PCTFREE 0
0375 ---COMPRESS FOR OLTP
0376 ---PARTITION BY REFERENCE(CONTENT_COLL_ID_FK);
0377
0378 CREATE OR REPLACE TRIGGER TRIG_CONTENT_ID
0379     BEFORE INSERT
0380     ON CONTENTS
0381     FOR EACH ROW
0382     BEGIN
0383         :NEW.content_id := CONTENT_ID_SEQ.NEXTVAL ;
0384     END;
0385  /
0386
0387
0388 CREATE INDEX CONTENTS_STATUS_UPDATED_IDX ON CONTENTS (status, locking, updated_at, created_at) LOCAL;
0389 CREATE INDEX CONTENTS_ID_NAME_IDX ON CONTENTS (coll_id, scope, name, status) LOCAL;
0390 CREATE INDEX CONTENTS_REQ_TF_COLL_IDX ON CONTENTS (request_id, transform_id, coll_id, status) LOCAL;
0391
0392 --- messages
0393 CREATE SEQUENCE MESSAGE_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
0394 CREATE TABLE MESSAGES
0395 (
0396     msg_id NUMBER(12),
0397     msg_type NUMBER(2),
0398     status NUMBER(2),
0399     substatus NUMBER(2),
0400     locking NUMBER(2),
0401     source NUMBER(2),
0402     destination NUMBER(2),
0403     request_id NUMBER(12),
0404     workload_id NUMBER(10),
0405     transform_id NUMBER(12),
0406     processing_id NUMBER(12),
0407     num_contents NUMBER(7),
0408     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0409     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0410     msg_content CLOB,
0411     CONSTRAINT MESSAGES_PK PRIMARY KEY (msg_id) -- USING INDEX LOCAL,
0412 );
0413
0414 CREATE OR REPLACE TRIGGER TRIG_MESSAGE_ID
0415     BEFORE INSERT
0416     ON MESSAGES
0417     FOR EACH ROW
0418     BEGIN
0419         :NEW.msg_id := MESSAGE_ID_SEQ.NEXTVAL ;
0420     END;
0421  /
0422
0423 alter table messages add destination NUMBER(2);
0424 alter table messages add processing_id NUMBER(12);
0425
0426 CREATE INDEX MESSAGES_TYPE_ST_IDX ON MESSAGES (msg_type, status, destination, request_id);
0427 CREATE INDEX MESSAGES_TYPE_ST_TF_IDX ON MESSAGES (msg_type, status, destination, transform_id);
0428 CREATE INDEX MESSAGES_TYPE_ST_PR_IDX ON MESSAGES (msg_type, status, destination, processing_id);
0429
0430
0431 --- health
0432 CREATE SEQUENCE HEALTH_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
0433 CREATE TABLE HEALTH
0434 (
0435     health_id NUMBER(12),
0436     agent VARCHAR2(30),
0437     hostname VARCHAR2(127),
0438     pid Number(12),
0439     thread_id Number(20),
0440     thread_name VARCHAR2(255),
0441     payload VARCHAR2(255),
0442     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0443     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0444     CONSTRAINT HEALTH_PK PRIMARY KEY (health_id), -- USING INDEX LOCAL,
0445     CONSTRAINT HEALTH_UQ UNIQUE (agent, hostname, pid, thread_id) -- USING INDEX LOCAL
0446 );
0447
0448 CREATE OR REPLACE TRIGGER TRIG_HEALTH_ID
0449     BEFORE INSERT
0450     ON HEALTH
0451     FOR EACH ROW
0452     BEGIN
0453         :NEW.health_id := HEALTH_ID_SEQ.NEXTVAL ;
0454     END;
0455  /
0456
0457
0458 --- request archive table
0459 CREATE TABLE REQUESTS_archive
0460 (
0461         request_id NUMBER(12),
0462         scope VARCHAR2(25),
0463         name VARCHAR2(255),
0464         requester VARCHAR2(20),
0465         request_type NUMBER(2),
0466         transform_tag VARCHAR2(10),
0467         workload_id NUMBER(10),
0468         priority NUMBER(7),
0469         status NUMBER(2),
0470         substatus NUMBER(2),
0471         locking NUMBER(2),
0472         created_at DATE,
0473         updated_at DATE,
0474         next_poll_at DATE,
0475         accessed_at DATE,
0476         expired_at DATE,
0477         errors VARCHAR2(1024),
0478         request_metadata CLOB,
0479         processing_metadata,
0480         CONSTRAINT REQ_AR_PK PRIMARY KEY (request_id) USING INDEX LOCAL
0481         --- CONSTRAINT REQUESTS_NAME_SCOPE_UQ UNIQUE (name, scope, requester, request_type, transform_tag, workload_id) -- USING INDEX LOCAL,
0482 )
0483 PCTFREE 3
0484 PARTITION BY RANGE(REQUEST_ID)
0485 INTERVAL ( 100000 )
0486 ( PARTITION initial_part VALUES LESS THAN (1) );
0487
0488 CREATE INDEX REQ_AR_SCOPE_NAME_IDX ON REQUESTS_archive (name, scope, workload_id) LOCAL;
0489 --- drop index REQUESTS_STATUS_PRIORITY_IDX
0490 CREATE INDEX REQ_AR_STATUS_PRIORITY_IDX ON REQUESTS_archive (status, priority, request_id, locking, updated_at, next_poll_at, created_at) LOCAL COMPRESS 1;
0491
0492
0493 CREATE TABLE TRANSFORMS_archive
0494 (
0495         transform_id NUMBER(12),
0496         request_id NUMBER(12),
0497         workload_id NUMBER(10),
0498         transform_type NUMBER(2),
0499         transform_tag VARCHAR2(20),
0500         priority NUMBER(7),
0501         safe2get_output_from_input NUMBER(10),
0502         status NUMBER(2),
0503         substatus NUMBER(2),
0504         locking NUMBER(2),
0505         retries NUMBER(5),
0506         created_at DATE,
0507         updated_at DATE,
0508         next_poll_at DATE,
0509         started_at DATE,
0510         finished_at DATE,
0511         expired_at DATE,
0512         transform_metadata CLOB,
0513         running_metadata CLOB,
0514         CONSTRAINT TF_AR_PK PRIMARY KEY (transform_id)
0515 )
0516 PCTFREE 3
0517 PARTITION BY RANGE(TRANSFORM_ID)
0518 INTERVAL ( 100000 )
0519 ( PARTITION initial_part VALUES LESS THAN (1) );
0520
0521
0522 CREATE TABLE PROCESSINGS_archive
0523 (
0524         processing_id NUMBER(12),
0525         transform_id NUMBER(12) Not NULL,
0526         request_id NUMBER(12),
0527         workload_id NUMBER(10),
0528         status NUMBER(2),
0529         substatus NUMBER(2),
0530         locking NUMBER(2),
0531         submitter VARCHAR2(20),
0532         submitted_id NUMBER(12),
0533         granularity NUMBER(10),
0534         granularity_type NUMBER(2),
0535         created_at DATE,
0536         updated_at DATE,
0537         next_poll_at DATE,
0538         submitted_at DATE,
0539         finished_at DATE,
0540         expired_at DATE,
0541         processing_metadata CLOB,
0542         running_metadata CLOB,
0543         output_metadata CLOB,
0544         CONSTRAINT PR_AR_PK PRIMARY KEY (processing_id),
0545         CONSTRAINT PR_AR_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS_archive(transform_id)
0546 )
0547 PCTFREE 3
0548 PARTITION BY RANGE(TRANSFORM_ID)
0549 INTERVAL ( 100000 )
0550 ( PARTITION initial_part VALUES LESS THAN (1) );
0551
0552
0553 CREATE TABLE COLLECTIONS_archive
0554 (
0555     coll_id NUMBER(14),
0556     coll_type NUMBER(2),
0557     transform_id NUMBER(12),
0558     request_id NUMBER(12),
0559     workload_id NUMBER(10),
0560     relation_type NUMBER(2),
0561     scope VARCHAR2(25),
0562     name VARCHAR2(255),
0563     bytes NUMBER(19),
0564     status NUMBER(2),
0565     substatus NUMBER(2),
0566     locking NUMBER(2),
0567     total_files NUMBER(19),
0568     storage_id NUMBER(10),
0569     new_files NUMBER(10),
0570     processed_files NUMBER(10),
0571     processing_files NUMBER(10),
0572     processing_id NUMBER(12),
0573     retries NUMBER(5) DEFAULT 0,
0574     created_at DATE,
0575     updated_at DATE,
0576     next_poll_at DATE,
0577     accessed_at DATE,
0578     expired_at DATE,
0579     coll_metadata CLOB,
0580     CONSTRAINT CL_AR_PK PRIMARY KEY (coll_id), -- USING INDEX LOCAL,
0581     CONSTRAINT CL_AR_NAME_SCOPE_UQ UNIQUE (name, scope, transform_id, relation_type), -- USING INDEX LOCAL,
0582     CONSTRAINT CL_AR_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS_archive(transform_id)
0583 )
0584 PCTFREE 3
0585 PARTITION BY RANGE(TRANSFORM_ID)
0586 INTERVAL ( 100000 )
0587 ( PARTITION initial_part VALUES LESS THAN (1) );
0588
0589
0590 CREATE TABLE CONTENTS_archive
0591 (
0592         content_id NUMBER(12),
0593         transform_id NUMBER(12),
0594         coll_id NUMBER(14),
0595         request_id NUMBER(12),
0596         workload_id NUMBER(10),
0597         map_id NUMBER(12),
0598         scope VARCHAR2(25),
0599         name VARCHAR2(255),
0600         min_id NUMBER(7),
0601         max_id NUMBER(7),
0602         content_type NUMBER(2),
0603         content_relation_type NUMBER(2),
0604         status NUMBER(2),
0605         substatus NUMBER(2),
0606         locking NUMBER(2),
0607         bytes NUMBER(12),
0608         md5 VARCHAR2(32),
0609         adler32 VARCHAR2(8),
0610         processing_id NUMBER(12),
0611         storage_id NUMBER(10),
0612         retries NUMBER(5) DEFAULT 0,
0613         path VARCHAR2(4000),
0614         created_at DATE,
0615         updated_at DATE,
0616         accessed_at DATE,
0617         expired_at DATE,
0618         content_metadata VARCHAR2(100),
0619         CONSTRAINT CT_AR_PK PRIMARY KEY (content_id),
0620         --- CONSTRAINT CT_ID_UQ UNIQUE (transform_id, coll_id, map_id, name, min_id, max_id) USING INDEX LOCAL,
0621         CONSTRAINT CT_AR_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS_archive(transform_id),
0622         CONSTRAINT CT_AR_COLL_ID_FK FOREIGN KEY(coll_id) REFERENCES COLLECTIONS_archive(coll_id)
0623 )
0624 PCTFREE 3
0625 PARTITION BY RANGE(TRANSFORM_ID)
0626 INTERVAL ( 100000 )
0627 ( PARTITION initial_part VALUES LESS THAN (1) );
0628
0629
0630 CREATE TABLE MESSAGES_archive
0631 (
0632     msg_id NUMBER(12),
0633     msg_type NUMBER(2),
0634     status NUMBER(2),
0635     substatus NUMBER(2),
0636     locking NUMBER(2),
0637     source NUMBER(2),
0638     destination NUMBER(2),
0639     request_id NUMBER(12),
0640     workload_id NUMBER(10),
0641     transform_id NUMBER(12),
0642     processing_id NUMBER(12),
0643     num_contents NUMBER(7),
0644     created_at DATE,
0645     updated_at DATE,
0646     msg_content CLOB,
0647     CONSTRAINT MG_AR_PK PRIMARY KEY (msg_id) -- USING INDEX LOCAL,
0648 )
0649 PCTFREE 3
0650 PARTITION BY RANGE(REQUEST_ID)
0651 INTERVAL ( 100000 )
0652 ( PARTITION initial_part VALUES LESS THAN (1) );
0653 SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
0654 FROM all_constraints cons, all_cons_columns cols
0655 WHERE cols.table_name = 'HEALTH'
0656 AND cons.constraint_type = 'P'
0657 AND cons.constraint_name = cols.constraint_name
0658 AND cons.owner = cols.owner
0659 ORDER BY cols.table_name, cols.position;
0660
0661
0662 select r.request_id, r.scope, r.name, r.status, tr.transform_id, tr.transform_status, tr.in_status, tr.in_total_files, tr.in_processed_files, tr.out_status, tr.out_total_files, tr.out_processed_files
0663 from requests r
0664  full outer join (
0665     select request_id, workprogress_id from workprogresses
0666  ) wp on (r.request_id=wp.request_id)
0667  full outer join wp2transforms wt on (wp.workprogress_id=wt.workprogress_id)
0668  full outer join (
0669     select t.transform_id, t.status transform_status, in_coll.status in_status, in_coll.total_files in_total_files, in_coll.processed_files in_processed_files,
0670     out_coll.status out_status, out_coll.total_files out_total_files, out_coll.processed_files out_processed_files
0671     from transforms t
0672     full outer join (select coll_id , transform_id, status, total_files, processed_files from collections where relation_type = 0) in_coll on (t.transform_id = in_coll.transform_id)
0673     full outer join (select coll_id , transform_id, status, total_files, processed_files from collections where relation_type = 1) out_coll on (t.transform_id = out_coll.transform_id)
0674  ) tr on (wt.transform_id=tr.transform_id)
0675 order by r.request_id
0676
0677
0678 select r.request_id, r.scope, r.name, r.status, tr.transform_id, tr.transform_status, tr.in_status, tr.in_total_files, tr.in_processed_files, tr.out_status, tr.out_total_files, tr.out_processed_files
0679 from requests r
0680  full outer join (
0681     select t.request_id, t.transform_id, t.status transform_status, in_coll.status in_status, in_coll.total_files in_total_files,
0682     in_coll.processed_files in_processed_files, out_coll.status out_status, out_coll.total_files out_total_files,
0683     out_coll.processed_files out_processed_files
0684     from transforms t
0685     full outer join (select coll_id , transform_id, status, total_files, processed_files from collections where relation_type = 0) in_coll on (t.transform_id = in_coll.transform_id)
0686     full outer join (select coll_id , transform_id, status, total_files, processed_files from collections where relation_type = 1) out_coll on (t.transform_id = out_coll.transform_id)
0687  ) tr on (r.request_id=tr.request_id)
0688 order by r.request_id