etc/sql/oracle_19.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 DROP SEQUENCE HEALTH_ID_SEQ;
0009
0010 DROP SEQUENCE COMMAND_ID_SEQ;
0011 DROP SEQUENCE EVENT_ID_SEQ;
0012 DROP SEQUENCE THROTTLER_ID_SEQ;
0013 DROP SEQUENCE METAINFO_ID_SEQ;
0014 DROP SEQUENCE CONDITION_ID_SEQ;
0015
0016 delete from HEALTH;
0017 delete from MESSAGES;
0018 delete from CONTENTS;
0019 delete from REQ2WORKLOAD;
0020 delete from REQ2TRANSFORMS;
0021 delete from WP2TRANSFORMS;
0022 delete from WORKPROGRESSES;
0023 delete from PROCESSINGS;
0024 delete from COLLECTIONS;
0025 delete from TRANSFORMS;
0026 delete from REQUESTS;
0027
0028 delete from CONDITIONS;
0029 delete from META_INFO;
0030 delete from THROTTLERS;
0031 delete from EVENTS;
0032 delete from EVENTS_ARCHIVE;
0033 delete from EVENTS_PRIORITY;
0034 delete from COMMANDS;
0035 delete from CONTENTS_EXT;
0036 delete from CONTENTS_update;
0037
0038 Drop table HEALTH purge;
0039 DROP table MESSAGES purge;
0040 DROP table CONTENTS purge;
0041 DROP table REQ2WORKLOAD purge;
0042 DROP table REQ2TRANSFORMS purge;
0043 DROP table WP2TRANSFORMS purge;
0044 DROP table WORKPROGRESSES purge;
0045 DROP table PROCESSINGS purge;
0046 DROP table COLLECTIONS purge;
0047 DROP table TRANSFORMS purge;
0048 DROP table REQUESTS purge;
0049
0050 DROP table CONDITIONS purge;
0051 DROP table META_INFO purge;
0052 DROP table THROTTLERS purge;
0053 DROP table EVENTS purge;
0054 DROP table EVENTS_ARCHIVE purge;
0055 DROP table EVENTS_PRIORITY purge;
0056 DROP table COMMANDS purge;
0057 DROP table CONTENTS_EXT purge;
0058 DROP table CONTENTS_update purge;
0059
0060 --- requests
0061 CREATE SEQUENCE REQUEST_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER NOCACHE NOCYCLE GLOBAL;
0062 CREATE TABLE REQUESTS
0063 (
0064         request_id NUMBER(12) DEFAULT ON NULL REQUEST_ID_SEQ.NEXTVAL constraint REQ_ID_NN NOT NULL,
0065         scope VARCHAR2(25) constraint REQ_SCOPE_NN NOT NULL,
0066         name VARCHAR2(255) constraint REQ_NAME_NN NOT NULL,
0067         requester VARCHAR2(20),
0068         request_type NUMBER(2) constraint REQ_DATATYPE_NN NOT NULL,
0069         username VARCHAR2(20) default null,
0070         userdn VARCHAR2(200) default null,
0071         transform_tag VARCHAR2(10),
0072         workload_id NUMBER(10),
0073         priority NUMBER(7),
0074         status NUMBER(2) constraint REQ_STATUS_ID_NN NOT NULL,
0075         substatus NUMBER(2),
0076         oldstatus NUMBER(2),
0077         locking NUMBER(2),
0078         created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_CREATED_NN NOT NULL,
0079         updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_UPDATED_NN NOT NULL,
0080         next_poll_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_NEXT_POLL_NN NOT NULL,
0081         accessed_at DATE,
0082         expired_at DATE,
0083         new_retries NUMBER(3),
0084         update_retries NUMBER(3),
0085         max_new_retries NUMBER(3),
0086         max_update_retries NUMBER(3),
0087         new_poll_period INTERVAL DAY TO SECOND,
0088         update_poll_period INTERVAL DAY TO SECOND,
0089         site VARCHAR2(50),
0090         campaign VARCHAR2(50),
0091         campaign_group VARCHAR2(250),
0092         campaign_tag VARCHAR2(20),
0093         errors VARCHAR2(1024),
0094         request_metadata CLOB constraint REQ_REQUEST_METADATA_ENSURE_JSON CHECK(request_metadata IS JSON(LAX)),
0095         processing_metadata CLOB constraint REQ_PROCESSING_METADATA_ENSURE_JSON CHECK(processing_metadata IS JSON(LAX)),
0096         CONSTRAINT REQUESTS_PK PRIMARY KEY (request_id) USING INDEX LOCAL,
0097         CONSTRAINT "REQUESTS_STATUS_ID_NN" CHECK (status IS NOT NULL)
0098         --- CONSTRAINT REQUESTS_NAME_SCOPE_UQ UNIQUE (name, scope, requester, request_type, transform_tag, workload_id) -- USING INDEX LOCAL,
0099 )
0100 PCTFREE 3
0101 PARTITION BY RANGE(REQUEST_ID)
0102 INTERVAL ( 100000 )
0103 ( PARTITION initial_part VALUES LESS THAN (1) );
0104
0105
0106 CREATE INDEX REQUESTS_SCOPE_NAME_IDX ON REQUESTS (name, scope, workload_id) LOCAL;
0107 --- drop index REQUESTS_STATUS_PRIORITY_IDX
0108 CREATE INDEX REQUESTS_STATUS_PRIORITY_IDX ON REQUESTS (status, priority, request_id, locking, updated_at, next_poll_at, created_at) LOCAL COMPRESS 1;
0109 CREATE INDEX REQUESTS_STATUS_POLL_IDX ON requests (status, priority, locking, updated_at, new_poll_period, update_poll_period, created_at, request_id) LOCAL;
0110 CREATE INDEX REQUESTS_STATUS_SITE ON requests (status, site, request_id);
0111
0112 -- alter table REQUESTS add (username VARCHAR2(20) default null);
0113 -- alter table REQUESTS add (userdn VARCHAR2(200) default null);
0114
0115 --- workprogress
0116 CREATE SEQUENCE WORKPROGRESS_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER NOCACHE NOCYCLE GLOBAL;
0117 CREATE TABLE WORKPROGRESSES
0118 (
0119         workprogress_id NUMBER(12) DEFAULT ON NULL WORKPROGRESS_ID_SEQ.NEXTVAL constraint WORKPROGRESS_ID_NN NOT NULL,
0120         request_id NUMBER(12) constraint WORKPROGRESS__REQ_ID_NN NOT NULL,
0121         workload_id NUMBER(10),
0122         scope VARCHAR2(25) constraint WORKPROGRESS_SCOPE_NN NOT NULL,
0123         name VARCHAR2(255) constraint WORKPROGRESS_NAME_NN NOT NULL,
0124         priority NUMBER(7),
0125         status NUMBER(2) constraint WORKPROGRESS_STATUS_ID_NN NOT NULL,
0126         substatus NUMBER(2),
0127         locking NUMBER(2),
0128         created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint WORKPROGRESS_CREATED_NN NOT NULL,
0129         updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint WORKPROGRESS_UPDATED_NN NOT NULL,
0130         next_poll_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint WORKPROGRESS_NEXT_POLL_NN NOT NULL,
0131         accessed_at DATE,
0132         expired_at DATE,
0133         errors VARCHAR2(1024),
0134         workprogress_metadata CLOB constraint WORKPROGRESS_REQUEST_METADATA_ENSURE_JSON CHECK(workprogress_metadata IS JSON(LAX)),
0135         processing_metadata CLOB constraint WORKPROGRESS_PROCESSING_METADATA_ENSURE_JSON CHECK(processing_metadata IS JSON(LAX)),
0136         CONSTRAINT WORKPROGRESS_PK PRIMARY KEY (workprogress_id) USING INDEX LOCAL,
0137         CONSTRAINT WORKPROGRESS_REQ_ID_FK FOREIGN KEY(request_id) REFERENCES REQUESTS(request_id)
0138 )
0139 PCTFREE 3
0140 PARTITION BY RANGE(workprogress_id)
0141 INTERVAL ( 100000 )
0142 ( PARTITION initial_part VALUES LESS THAN (1) );
0143
0144 CREATE INDEX WORKPROGRESS_SCOPE_NAME_IDX ON WORKPROGRESSES (name, scope, workprogress_id) LOCAL;
0145 --- drop index REQUESTS_STATUS_PRIORITY_IDX
0146 CREATE INDEX WORKPROGRESS_STATUS_PRIORITY_IDX ON WORKPROGRESSES (status, priority, workprogress_id, locking, updated_at, next_poll_at, created_at) LOCAL COMPRESS 1;
0147
0148
0149 --- transforms
0150 CREATE SEQUENCE TRANSFORM_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER NOCACHE NOCYCLE GLOBAL;
0151 CREATE TABLE TRANSFORMS
0152 (
0153         transform_id NUMBER(12) DEFAULT ON NULL TRANSFORM_ID_SEQ.NEXTVAL constraint TRANSFORM_ID_NN NOT NULL,
0154         request_id NUMBER(12),
0155         workload_id NUMBER(10),
0156         transform_type NUMBER(2) constraint TRANSFORM_TYPE_NN NOT NULL,
0157         transform_tag VARCHAR2(20),
0158         internal_id VARCHAR2(20),
0159         priority NUMBER(7),
0160         safe2get_output_from_input NUMBER(10),
0161         status NUMBER(2),
0162         substatus NUMBER(2),
0163         oldstatus NUMBER(2),
0164         locking NUMBER(2),
0165         retries NUMBER(5) DEFAULT 0,
0166         parent_transform_id NUMBER(12),
0167         previous_transform_id NUMBER(12),
0168         current_processing_id NUMBER(12),
0169         created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint TRANSFORM_CREATED_NN NOT NULL,
0170         updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint TRANSFORM_UPDATED_NN NOT NULL,
0171         next_poll_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint TRANSFORM_NEXT_POLL_NN NOT NULL,
0172         started_at DATE,
0173         finished_at DATE,
0174         expired_at DATE,
0175         new_retries NUMBER(5) DEFAULT 0,
0176         update_retries NUMBER(5) DEFAULT 0,
0177         max_new_retries NUMBER(5) DEFAULT 100,
0178         max_update_retries NUMBER(5) DEFAULT 100,
0179         new_poll_period INTERVAL DAY TO SECOND,
0180         update_poll_period INTERVAL DAY TO SECOND,
0181         site VARCHAR2(50),
0182         name VARCHAR2(255),
0183         has_previous_conditions NUMBER(5),
0184         loop_index NUMBER(5),
0185         cloned_from NUMBER(12),
0186         triggered_conditions CLOB,
0187         untriggered_conditions CLOB,
0188         errors VARCHAR2(1024 CHAR),
0189         transform_metadata CLOB constraint TRANSFORM_METADATA_ENSURE_JSON CHECK(transform_metadata IS JSON(LAX)),
0190         running_metadata CLOB,
0191         CONSTRAINT TRANSFORMS_PK PRIMARY KEY (transform_id),
0192         CONSTRAINT "TRANSFORMS_STATUS_ID_NN" CHECK (status IS NOT NULL)
0193 )
0194 PCTFREE 3
0195 PARTITION BY RANGE(TRANSFORM_ID)
0196 INTERVAL ( 100000 )
0197 ( PARTITION initial_part VALUES LESS THAN (1) );
0198
0199 --- alter table transforms add next_poll_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint TRANSFORM_NEXT_POLL_NN NOT NULL;
0200 CREATE INDEX TRANSFORMS_TYPE_TAG_IDX ON TRANSFORMS (transform_type, transform_tag, transform_id) LOCAL;
0201 CREATE INDEX TRANSFORMS_STATUS_UPDATED_AT_IDX ON TRANSFORMS (status, locking, updated_at, next_poll_at, created_at) LOCAL;
0202 CREATE INDEX TRANSFORMS_STATUS_SITE ON transforms (status, site, request_id, transform_id) LOCAL;
0203 CREATE INDEX TRANSFORMS_REQ_IDX ON transforms (request_id, transform_id);
0204 CREATE INDEX TRANSFORMS_STATUS_POLL_IDX ON transforms (status, locking, updated_at, new_poll_period, update_poll_period, created_at, transform_id);
0205
0206
0207 ---- processings
0208 CREATE SEQUENCE PROCESSING_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER NOCACHE NOCYCLE GLOBAL;
0209 CREATE TABLE PROCESSINGS
0210 (
0211         processing_id NUMBER(12) DEFAULT ON NULL PROCESSING_ID_SEQ.NEXTVAL constraint PROCESSING_ID_NN NOT NULL,
0212         transform_id NUMBER(12) constraint PROCESSINGS_TRANSFORM_ID_NN NOT NULL,
0213         request_id NUMBER(12),
0214         workload_id NUMBER(10),
0215         processing_type NUMBER(2) NOT NULL,
0216         status NUMBER(2) constraint PROCESSINGS_STATUS_ID_NN NOT NULL,
0217         substatus NUMBER(2),
0218         oldstatus NUMBER(2),
0219         locking NUMBER(2),
0220         submitter VARCHAR2(20),
0221         submitted_id NUMBER(12),
0222         granularity NUMBER(10),
0223         granularity_type NUMBER(2),
0224         created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint PROCESSING_CREATED_NN NOT NULL,
0225         updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint PROCESSING_UPDATED_NN NOT NULL,
0226         next_poll_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint PROCESSING_NEXT_POLL_NN NOT NULL,
0227         poller_updated_at DATE,
0228         submitted_at DATE,
0229         finished_at DATE,
0230         expired_at DATE,
0231         new_retries NUMBER(5) DEFAULT 0,
0232         update_retries NUMBER(5) DEFAULT 0,
0233         max_new_retries NUMBER(5) DEFAULT 100,
0234         max_update_retries NUMBER(5) DEFAULT 100,
0235         new_poll_period INTERVAL DAY TO SECOND,
0236         update_poll_period INTERVAL DAY TO SECOND,
0237         site VARCHAR2(50),
0238         errors VARCHAR2(1024),
0239         processing_metadata CLOB constraint PROCESSINGS_METADATA_ENSURE_JSON CHECK(processing_metadata IS JSON(LAX)),
0240         running_metadata CLOB,
0241         output_metadata CLOB constraint PROCESSINGS_OUTPUT_METADATA_ENSURE_JSON CHECK(output_metadata IS JSON(LAX)),
0242         CONSTRAINT PROCESSINGS_PK PRIMARY KEY (processing_id),
0243         CONSTRAINT PROCESSINGS_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id)
0244 )
0245 PCTFREE 0
0246 PARTITION BY REFERENCE(PROCESSINGS_TRANSFORM_ID_FK);
0247
0248 CREATE INDEX PROCESSINGS_STATUS_UPDATED_AT_IDX ON PROCESSINGS (status, locking, updated_at, next_poll_at, created_at) LOCAL;
0249 CREATE INDEX PROCESSINGS_STATUS_POLL_IDX ON processings (status, processing_id, locking, updated_at, new_poll_period, update_poll_period, created_at);
0250 CREATE INDEX PROCESSINGS_STATUS_SITE ON processings (status, site, request_id, transform_id, processing_id);
0251
0252
0253 --- collections
0254 CREATE SEQUENCE COLLECTION_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER CACHE 2 NOCYCLE GLOBAL;
0255 CREATE TABLE COLLECTIONS
0256 (
0257     coll_id NUMBER(14) DEFAULT ON NULL COLLECTION_ID_SEQ.NEXTVAL constraint COLLECTIONS_ID_NN NOT NULL,
0258     coll_type NUMBER(2),
0259     transform_id NUMBER(12) constraint COLLECTION_TRANSFORM_ID_NN NOT NULL,
0260     request_id NUMBER(12),
0261     workload_id NUMBER(10),
0262     relation_type NUMBER(2), -- input, output or log of the transform,
0263     scope VARCHAR2(25) constraint COLLECTION_SCOPE_NN NOT NULL,
0264     name VARCHAR2(255) constraint COLLECTION_NAME_NN NOT NULL,
0265     bytes NUMBER(19),
0266     status NUMBER(2),
0267     substatus NUMBER(2),
0268     locking NUMBER(2),
0269     total_files NUMBER(19),
0270     storage_id NUMBER(10),
0271     new_files NUMBER(10),
0272     processed_files NUMBER(10),
0273     processing_files NUMBER(10),
0274     processing_id NUMBER(12),
0275     failed_files NUMBER(10),
0276     missing_files NUMBER(10),
0277     ext_files NUMBER(10),
0278     processed_ext_files NUMBER(10),
0279     failed_ext_files NUMBER(10),
0280     missing_ext_files NUMBER(10),
0281     retries NUMBER(5) DEFAULT 0,
0282     created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint COLLECTION_CREATED_NN NOT NULL,
0283     updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint COLLECTION_UPDATED_NN NOT NULL,
0284     next_poll_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint COLLECTION_NEXT_POLL_NN NOT NULL,
0285     accessed_at DATE,
0286     expired_at DATE,
0287     coll_metadata CLOB constraint COLLECTION_METADATA_ensure_json CHECK (COLL_METADATA IS JSON (LAX)),
0288     CONSTRAINT COLLECTION_PK PRIMARY KEY (coll_id), -- USING INDEX LOCAL,
0289     CONSTRAINT COLLECTION_NAME_SCOPE_UQ UNIQUE (name, scope, transform_id, relation_type), -- USING INDEX LOCAL,
0290     CONSTRAINT COLLECTION_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id),
0291     CONSTRAINT "COLLECTIONS_STATUS_ID_NN" CHECK (status IS NOT NULL),
0292     CONSTRAINT "COLLECTIONS_TRANSFORM_ID_NN" CHECK (transform_id IS NOT NULL)
0293 )
0294 PCTFREE 0
0295 PARTITION BY REFERENCE(COLLECTION_TRANSFORM_ID_FK);
0296
0297 CREATE INDEX COLLECTIONS_STATUS_RELATIONTYPE_IDX ON COLLECTIONS(status, relation_type);
0298 CREATE INDEX COLLECTIONS_TRANSFORM_IDX ON COLLECTIONS(transform_id, coll_id);
0299 CREATE INDEX COLLECTIONS_STATUS_UPDATED_AT_IDX ON COLLECTIONS (status, locking, updated_at, next_poll_at, created_at) LOCAL;
0300 CREATE INDEX COLLECTIONS_REQ_IDX ON collections (request_id, transform_id, updated_at);
0301
0302
0303 --- contents
0304 CREATE SEQUENCE CONTENT_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER CACHE 10 NOCYCLE GLOBAL;
0305 CREATE TABLE CONTENTS
0306 (
0307         content_id NUMBER(12) DEFAULT ON NULL CONTENT_ID_SEQ.NEXTVAL constraint CONTENT_ID_NN NOT NULL,
0308         transform_id NUMBER(12) constraint CONTENT_TRANSFORM_ID_NN NOT NULL,
0309         coll_id NUMBER(14) constraint CONTENT_COLL_ID_NN NOT NULL,
0310         request_id NUMBER(12),
0311         workload_id NUMBER(10),
0312         map_id NUMBER(12) DEFAULT 0,
0313         sub_map_id NUMBER(19),
0314         dep_sub_map_id NUMBER(19),
0315         content_dep_id NUMBER(19),
0316         scope VARCHAR2(25) constraint CONTENT_SCOPE_NN NOT NULL,
0317         name VARCHAR2(255) constraint CONTENT_NAME_NN NOT NULL,
0318         name_md5 VARCHAR2(33),
0319         scope_name_md5 VARCHAR2(33),
0320         min_id NUMBER(7) default 0,
0321         max_id NUMBER(7) default 0,
0322         content_type NUMBER(2) constraint CONTENT_TYPE_NN NOT NULL,
0323         content_relation_type NUMBER(2) default 0,
0324         status NUMBER(2) constraint CONTENT_STATUS_NN NOT NULL,
0325         substatus NUMBER(2),
0326         locking NUMBER(2),
0327         bytes NUMBER(12),
0328         md5 VARCHAR2(32),
0329         adler32 VARCHAR2(8),
0330         processing_id NUMBER(12),
0331         storage_id NUMBER(10),
0332         retries NUMBER(5) DEFAULT 0,
0333         external_coll_id NUMBER(19),
0334         external_content_id NUMBER(19),
0335         external_event_id NUMBER(19),
0336         external_event_status NUMBER(5),
0337         path VARCHAR2(4000),
0338         created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint CONTENT_CREATED_NN NOT NULL,
0339         updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)) constraint CONTENT_UPDATED_NN NOT NULL,
0340         accessed_at DATE,
0341         expired_at DATE,
0342         --- content_metadata CLOB constraint CONTENT_METADATA_ENSURE_JSON CHECK(CONTENT_METADATA IS JSON(LAX)),
0343         content_metadata VARCHAR2(1000),
0344         --- CONSTRAINT CONTENT_PK PRIMARY KEY (name, scope, coll_id, content_type, min_id, max_id) USING INDEX LOCAL,
0345         CONSTRAINT CONTENT_PK PRIMARY KEY (content_id),
0346         ---- CONSTRAINT CONTENT_SCOPE_NAME_UQ UNIQUE (name, scope, coll_id, content_type, min_id, max_id) USING INDEX LOCAL,
0347         ---- CONSTRAINT CONTENT_SCOPE_NAME_UQ UNIQUE (name, scope, coll_id, min_id, max_id) USING INDEX LOCAL,
0348         ---- CONSTRAINT CONTENT_ID_UQ UNIQUE (transform_id, coll_id, map_id, name, min_id, max_id) USING INDEX LOCAL,
0349         CONSTRAINT CONTENT_ID_UQ UNIQUE (transform_id, coll_id, map_id, sub_map_id, dep_sub_map_id, content_relation_type, name_md5, scope_name_md5, min_id, max_id),
0350         CONSTRAINT CONTENT_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id),
0351         CONSTRAINT CONTENT_COLL_ID_FK FOREIGN KEY(coll_id) REFERENCES COLLECTIONS(coll_id),
0352         CONSTRAINT "CONTENTS_STATUS_ID_NN" CHECK (status IS NOT NULL),
0353         CONSTRAINT "CONTENTS_COLL_ID_NN" CHECK (coll_id IS NOT NULL)
0354 )
0355 PCTFREE 0
0356 PARTITION BY REFERENCE(CONTENT_TRANSFORM_ID_FK);
0357
0358 CREATE INDEX CONTENTS_STATUS_UPDATED_IDX ON CONTENTS (status, locking, updated_at, created_at) LOCAL;
0359 CREATE INDEX CONTENTS_ID_NAME_IDX ON CONTENTS (coll_id, scope, md5('name'), status) LOCAL;
0360 CREATE INDEX CONTENTS_REQ_TF_COLL_IDX ON CONTENTS (request_id, transform_id, workload_id, coll_id, content_relation_type, status, substatus) LOCAL;
0361 CREATE INDEX CONTENTS_TF_IDX ON contents (transform_id, request_id, coll_id, map_id, content_relation_type);
0362 CREATE INDEX CONTENTS_DEP_IDX ON contents (request_id, transform_id, content_dep_id);
0363 CREATE INDEX CONTENTS_REL_IDX ON contents (request_id, content_relation_type, transform_id, substatus);
0364
0365
0366 --- messages
0367 CREATE SEQUENCE MESSAGE_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0368 CREATE TABLE MESSAGES
0369 (
0370     msg_id NUMBER(12) DEFAULT ON NULL MESSAGE_ID_SEQ.NEXTVAL constraint MESSAGE_ID_NN NOT NULL,
0371     msg_type NUMBER(2),
0372     status NUMBER(2),
0373     substatus NUMBER(2),
0374     locking NUMBER(2),
0375     source NUMBER(2),
0376     destination NUMBER(2),
0377     request_id NUMBER(12),
0378     workload_id NUMBER(10),
0379     transform_id NUMBER(12),
0380     processing_id NUMBER(12),
0381     internal_id VARCHAR2(20),
0382     num_contents NUMBER(7),
0383     retries NUMBER(5) DEFAULT 0,
0384     fetching_id NUMBER(12),
0385     poll_period INTERVAL DAY TO SECOND NOT NULL,
0386     created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)),
0387     updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)),
0388     msg_content CLOB constraint MSG_CONTENT_ENSURE_JSON CHECK(msg_content IS JSON(LAX)),
0389     CONSTRAINT MESSAGES_PK PRIMARY KEY (msg_id) -- USING INDEX LOCAL,
0390 );
0391
0392 CREATE INDEX MESSAGES_TYPE_ST_IDX ON MESSAGES (msg_type, status, destination, request_id);
0393 CREATE INDEX MESSAGES_TYPE_ST_TF_IDX ON MESSAGES (msg_type, status, destination, transform_id);
0394 CREATE INDEX MESSAGES_TYPE_ST_PR_IDX ON MESSAGES (msg_type, status, destination, processing_id);
0395 CREATE INDEX MESSAGES_ST_IDX ON messages (status, destination, created_at);
0396 CREATE INDEX MESSAGES_TYPE_STU_IDX ON messages (msg_type, status, destination, retries, updated_at, created_at);
0397
0398
0399 --- health
0400 CREATE SEQUENCE HEALTH_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0401 CREATE TABLE HEALTH
0402 (
0403     health_id NUMBER(12) DEFAULT ON NULL HEALTH_ID_SEQ.NEXTVAL constraint HEALTH_ID_NN NOT NULL,
0404     agent VARCHAR2(30),
0405     hostname VARCHAR2(127),
0406     pid Number(12),
0407     status Number(2),
0408     thread_id Number(20),
0409     thread_name VARCHAR2(255),
0410     payload VARCHAR2(2048),
0411     created_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)),
0412     updated_at DATE DEFAULT ON NULL SYS_EXTRACT_UTC(systimestamp(0)),
0413     CONSTRAINT HEALTH_PK PRIMARY KEY (health_id), -- USING INDEX LOCAL,
0414     CONSTRAINT HEALTH_UQ UNIQUE (agent, hostname, pid, thread_id)
0415 );
0416
0417
0418 --- contents_update
0419 CREATE TABLE contents_update (
0420         content_id NUMBER(19) NOT NULL,
0421         substatus NUMBER(2),
0422         request_id NUMBER(19),
0423         transform_id NUMBER(19),
0424         workload_id NUMBER(19),
0425         fetch_status NUMBER(2) NOT NULL,
0426         coll_id NUMBER(19),
0427         content_metadata VARCHAR2(1000),
0428         PRIMARY KEY (content_id)
0429 );
0430
0431
0432 ---- contents_ext
0433 CREATE TABLE contents_ext (
0434         content_id NUMBER(19) NOT NULL,
0435         transform_id NUMBER(19) NOT NULL,
0436         coll_id NUMBER(19) NOT NULL,
0437         request_id NUMBER(19) NOT NULL,
0438         workload_id NUMBER(19),
0439         map_id NUMBER(19) NOT NULL,
0440         status INTEGER NOT NULL,
0441         panda_id NUMBER(19),
0442         job_definition_id NUMBER(19),
0443         scheduler_id VARCHAR2(128),
0444         pilot_id VARCHAR2(200),
0445         creation_time DATE,
0446         modification_time DATE,
0447         start_time DATE,
0448         end_time DATE,
0449         prod_source_label VARCHAR2(20),
0450         prod_user_id VARCHAR2(250),
0451         assigned_priority NUMBER(5),
0452         current_priority NUMBER(5),
0453         attempt_nr INTEGER,
0454         max_attempt INTEGER,
0455         max_cpu_count INTEGER,
0456         max_cpu_unit VARCHAR2(32),
0457         max_disk_count INTEGER,
0458         max_disk_unit VARCHAR2(10),
0459         min_ram_count INTEGER,
0460         min_ram_unit VARCHAR2(10),
0461         cpu_consumption_time INTEGER,
0462         cpu_consumption_unit VARCHAR2(128),
0463         job_status VARCHAR2(10),
0464         job_name VARCHAR2(255),
0465         trans_exit_code INTEGER,
0466         pilot_error_code INTEGER,
0467         pilot_error_diag VARCHAR2(500),
0468         exe_error_code INTEGER,
0469         exe_error_diag VARCHAR2(500),
0470         sup_error_code INTEGER,
0471         sup_error_diag VARCHAR2(250),
0472         ddm_error_code INTEGER,
0473         ddm_error_diag VARCHAR2(500),
0474         brokerage_error_code INTEGER,
0475         brokerage_error_diag VARCHAR2(250),
0476         job_dispatcher_error_code INTEGER,
0477         job_dispatcher_error_diag VARCHAR2(250),
0478         task_buffer_error_code INTEGER,
0479         task_buffer_error_diag VARCHAR2(300),
0480         computing_site VARCHAR2(128),
0481         computing_element VARCHAR2(128),
0482         grid VARCHAR2(50),
0483         cloud VARCHAR2(50),
0484         cpu_conversion FLOAT,
0485         task_id NUMBER(19),
0486         vo VARCHAR2(16 CHAR),
0487         pilot_timing VARCHAR2(100),
0488         working_group VARCHAR2(20),
0489         processing_type VARCHAR2(64),
0490         prod_user_name VARCHAR2(60),
0491         core_count INTEGER,
0492         n_input_files INTEGER,
0493         req_id NUMBER(19),
0494         jedi_task_id NUMBER(19),
0495         actual_core_count INTEGER,
0496         max_rss INTEGER,
0497         max_vmem INTEGER,
0498         max_swap INTEGER,
0499         max_pss INTEGER,
0500         avg_rss INTEGER,
0501         avg_vmem INTEGER,
0502         avg_swap INTEGER,
0503         avg_pss INTEGER,
0504         max_walltime INTEGER,
0505         disk_io INTEGER,
0506         failed_attempt INTEGER,
0507         hs06 INTEGER,
0508         hs06sec INTEGER,
0509         memory_leak VARCHAR2(10),
0510         memory_leak_x2 VARCHAR2(10),
0511         job_label VARCHAR2(20 CHAR),
0512         CONSTRAINT "CONTENTS_EXT_PK" PRIMARY KEY (content_id)
0513 );
0514
0515 CREATE INDEX CONTENTS_EXT_RTM_IDX ON contents_ext (request_id, transform_id, map_id);
0516
0517 CREATE INDEX CONTENTS_EXT_RTF_IDX ON contents_ext (request_id, transform_id, workload_id, coll_id, content_id, panda_id, status);
0518
0519 CREATE INDEX CONTENTS_EXT_RTW_IDX ON contents_ext (request_id, transform_id, workload_id);
0520
0521
0522 --- commands
0523 CREATE SEQUENCE COMMAND_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0524
0525 CREATE TABLE commands (
0526         cmd_id NUMBER(19) DEFAULT ON NULL COMMAND_ID_SEQ.NEXTVAL constraint COMMAND_ID_NN NOT NULL,
0527         request_id NUMBER(19) NOT NULL,
0528         workload_id NUMBER(19),
0529         transform_id NUMBER(19),
0530         processing_id NUMBER(19),
0531         cmd_type NUMBER(2),
0532         status NUMBER(2) NOT NULL,
0533         substatus INTEGER,
0534         locking INTEGER NOT NULL,
0535         username VARCHAR2(50 CHAR),
0536         retries INTEGER,
0537         source INTEGER,
0538         destination INTEGER,
0539         created_at DATE NOT NULL,
0540         updated_at DATE NOT NULL,
0541         cmd_content CLOB,
0542         errors VARCHAR2(1024 CHAR),
0543         CONSTRAINT "COMMANDS_PK" PRIMARY KEY (cmd_id)
0544 ) PCTFREE 0;
0545
0546 CREATE INDEX COMMANDS_TYPE_ST_PR_IDX ON commands (cmd_type, status, destination, processing_id);
0547
0548 CREATE INDEX COMMANDS_TYPE_ST_IDX ON commands (cmd_type, status, destination, request_id);
0549
0550 CREATE INDEX COMMANDS_STATUS_IDX ON commands (status, locking, updated_at);
0551
0552 CREATE INDEX COMMANDS_TYPE_ST_TF_IDX ON commands (cmd_type, status, destination, transform_id);
0553
0554
0555 --- events
0556 CREATE TABLE events_priority (
0557         event_type INTEGER NOT NULL,
0558         event_actual_id INTEGER NOT NULL,
0559         priority INTEGER NOT NULL,
0560         last_processed_at DATE NOT NULL,
0561         updated_at DATE NOT NULL,
0562         CONSTRAINT "EVENTS_PR_PK" PRIMARY KEY (event_type, event_actual_id)
0563 ) ;
0564
0565 CREATE SEQUENCE EVENT_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0566
0567 CREATE TABLE events (
0568         event_id NUMBER(19) DEFAULT ON NULL EVENT_ID_SEQ.NEXTVAL constraint EVENT_ID_NN NOT NULL,
0569         event_type INTEGER NOT NULL,
0570         event_actual_id INTEGER NOT NULL,
0571         priority INTEGER,
0572         status INTEGER NOT NULL,
0573         created_at DATE NOT NULL,
0574         processing_at DATE,
0575         processed_at DATE,
0576         content CLOB,
0577         CONSTRAINT "EVENTS_PK" PRIMARY KEY (event_id)
0578 ) ;
0579
0580
0581 CREATE TABLE events_archive (
0582         event_id NUMBER(19) NOT NULL,
0583         event_type INTEGER NOT NULL,
0584         event_actual_id INTEGER NOT NULL,
0585         priority INTEGER,
0586         status INTEGER NOT NULL,
0587         created_at DATE NOT NULL,
0588         processing_at DATE,
0589         processed_at DATE,
0590         content CLOB,
0591         CONSTRAINT "EVENTS_AR_PK" PRIMARY KEY (event_id)
0592 ) ;
0593
0594
0595 --- throttler
0596 CREATE SEQUENCE THROTTLER_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0597
0598 CREATE TABLE throttlers (
0599         throttler_id NUMBER(19) DEFAULT ON NULL THROTTLER_ID_SEQ.NEXTVAL constraint THROTTLER_ID_NN NOT NULL,
0600         site VARCHAR2(50 CHAR) NOT NULL,
0601         status INTEGER NOT NULL,
0602         num_requests INTEGER,
0603         num_transforms INTEGER,
0604         num_processings INTEGER,
0605         new_contents INTEGER,
0606         queue_contents INTEGER,
0607         created_at DATE NOT NULL,
0608         updated_at DATE NOT NULL,
0609         others CLOB,
0610         CONSTRAINT "THROTTLER_PK" PRIMARY KEY (throttler_id),
0611         CONSTRAINT "THROTTLER_SITE_UQ" UNIQUE (site)
0612 );
0613
0614
0615 --- meta_info
0616 CREATE SEQUENCE METAINFO_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0617
0618 CREATE TABLE meta_info (
0619         meta_id NUMBER(19) DEFAULT ON NULL METAINFO_ID_SEQ.NEXTVAL constraint METAINFO_ID_NN NOT NULL,
0620         name VARCHAR2(50 CHAR) NOT NULL,
0621         status INTEGER NOT NULL,
0622         created_at DATE NOT NULL,
0623         updated_at DATE NOT NULL,
0624         description VARCHAR2(1000 CHAR),
0625         meta_info CLOB,
0626         CONSTRAINT "METAINFO_PK" PRIMARY KEY (meta_id),
0627         CONSTRAINT "METAINFO_NAME_UQ" UNIQUE (name)
0628 ) ;
0629
0630 --- conditions
0631 CREATE SEQUENCE CONDITION_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0632
0633 CREATE TABLE conditions (
0634         condition_id NUMBER(19) DEFAULT ON NULL CONDITION_ID_SEQ.NEXTVAL constraint CONDITION_ID_NN NOT NULL,
0635         request_id NUMBER(19) NOT NULL,
0636         internal_id VARCHAR2(20 CHAR),
0637         name VARCHAR2(250 CHAR),
0638         status INTEGER NOT NULL,
0639         substatus INTEGER,
0640         is_loop INTEGER,
0641         loop_index INTEGER,
0642         cloned_from NUMBER(19),
0643         created_at DATE NOT NULL,
0644         updated_at DATE NOT NULL,
0645         evaluate_result VARCHAR2(1000 CHAR),
0646         previous_transforms CLOB,
0647         following_transforms CLOB,
0648         condition CLOB,
0649         CONSTRAINT "CONDITION_PK" PRIMARY KEY (condition_id),
0650         CONSTRAINT "CONDITION_ID_UQ" UNIQUE (request_id, internal_id)
0651 ) ;