etc/sql/oracle_update.sql

0001 -- 2022.08.23
0002 alter table REQUESTS add (oldstatus NUMBER(2));
0003 alter table REQUESTS add (new_retries NUMBER(5) DEFAULT 0);
0004 alter table REQUESTS add (update_retries NUMBER(5) DEFAULT 0);
0005 alter table REQUESTS add (max_new_retries NUMBER(5) DEFAULT 3);
0006 alter table REQUESTS add (max_update_retries NUMBER(5) DEFAULT 0);
0007 # alter table REQUESTS add (new_poll_period NUMBER(10) DEFAULT 10);
0008 # alter table REQUESTS add (update_poll_period NUMBER(10) DEFAULT 10);
0009 # alter table REQUESTS drop column new_poll_period
0010 # alter table REQUESTS drop column update_poll_period
0011 alter table REQUESTS add (new_poll_period INTERVAL DAY TO SECOND DEFAULT '00 00:00:01');
0012 alter table REQUESTS add (update_poll_period INTERVAL DAY TO SECOND DEFAULT '00 00:00:10');
0013
0014 alter table TRANSFORMS add (oldstatus NUMBER(2));
0015 alter table TRANSFORMS add (new_retries NUMBER(5) DEFAULT 0);
0016 alter table TRANSFORMS add (update_retries NUMBER(5) DEFAULT 0);
0017 alter table TRANSFORMS add (max_new_retries NUMBER(5) DEFAULT 3);
0018 alter table TRANSFORMS add (max_update_retries NUMBER(5) DEFAULT 0);
0019 #alter table TRANSFORMS add (new_poll_period NUMBER(10) DEFAULT 10);
0020 #alter table TRANSFORMS add (update_poll_period NUMBER(10) DEFAULT 10);
0021 alter table TRANSFORMS add (errors VARCHAR2(1024));
0022 # alter table TRANSFORMS drop column new_poll_period
0023 # alter table TRANSFORMS drop column update_poll_period
0024 alter table TRANSFORMS add (new_poll_period INTERVAL DAY TO SECOND DEFAULT '00 00:00:01');
0025 alter table TRANSFORMS add (update_poll_period INTERVAL DAY TO SECOND DEFAULT '00 00:00:10');
0026
0027
0028 alter table PROCESSINGS add (oldstatus NUMBER(2));
0029 alter table PROCESSINGS add (new_retries NUMBER(5) DEFAULT 0);
0030 alter table PROCESSINGS add (update_retries NUMBER(5) DEFAULT 0);
0031 alter table PROCESSINGS add (max_new_retries NUMBER(5) DEFAULT 3);
0032 alter table PROCESSINGS add (max_update_retries NUMBER(5) DEFAULT 0);
0033 #alter table PROCESSINGS add (new_poll_period NUMBER(10) DEFAULT 10);
0034 #alter table PROCESSINGS add (update_poll_period NUMBER(10) DEFAULT 10);
0035 alter table PROCESSINGS add (errors VARCHAR2(1024));
0036 # alter table PROCESSINGS drop column new_poll_period
0037 # alter table PROCESSINGS drop column update_poll_period
0038 alter table PROCESSINGS add (new_poll_period INTERVAL DAY TO SECOND DEFAULT '00 00:00:01');
0039 alter table PROCESSINGS add (update_poll_period INTERVAL DAY TO SECOND DEFAULT '00 00:00:10');
0040
0041
0042 alter table MESSAGES add (retries NUMBER(5) DEFAULT 0);
0043
0044 -- oracle 11
0045 CREATE SEQUENCE COMMAND_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
0046 CREATE TABLE COMMANDS
0047 (
0048     cmd_id NUMBER(12),
0049     request_id NUMBER(12),
0050     workload_id NUMBER(10),
0051     transform_id NUMBER(12),
0052     processing_id NUMBER(12),
0053     cmd_type NUMBER(2),
0054     status NUMBER(2),
0055     substatus NUMBER(2),
0056     locking NUMBER(2),
0057     username VARCHAR2(20),
0058     retries NUMBER(5) DEFAULT 0,
0059     source NUMBER(2),
0060     destination NUMBER(2),
0061     num_contents NUMBER(7),
0062     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0063     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0064     cmd_content CLOB,
0065     errors VARCHAR2(1024),
0066     CONSTRAINT COMMANDS_PK PRIMARY KEY (cmd_id) -- USING INDEX LOCAL,
0067 );
0068
0069 CREATE OR REPLACE TRIGGER TRIG_COMMAND_ID
0070     BEFORE INSERT
0071     ON COMMANDS
0072     FOR EACH ROW
0073     BEGIN
0074         :NEW.cmd_id := COMMAND_ID_SEQ.NEXTVAL ;
0075     END;
0076  /
0077
0078 CREATE INDEX COMMANDS_TYPE_ST_IDX ON COMMANDS (cmd_type, status, destination, request_id);
0079 CREATE INDEX COMMANDS_TYPE_ST_TF_IDX ON COMMANDS (cmd_type, status, destination, transform_id);
0080 CREATE INDEX COMMANDS_TYPE_ST_PR_IDX ON COMMANDS (cmd_type, status, destination, processing_id);
0081
0082 -- oracle 19
0083 CREATE SEQUENCE COMMAND_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0084 CREATE TABLE COMMANDS
0085 (
0086     cmd_id NUMBER(12) DEFAULT ON NULL COMMAND_ID_SEQ.NEXTVAL constraint COMMAND_ID_NN NOT NULL,
0087     request_id NUMBER(12),
0088     workload_id NUMBER(10),
0089     transform_id NUMBER(12),
0090     processing_id NUMBER(12),
0091     cmd_type NUMBER(2),
0092     status NUMBER(2),
0093     substatus NUMBER(2),
0094     locking NUMBER(2),
0095     username VARCHAR2(20),
0096     retries NUMBER(5) DEFAULT 0,
0097     source NUMBER(2),
0098     destination NUMBER(2),
0099     num_contents NUMBER(7),
0100     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0101     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0102     cmd_content CLOB,
0103     errors VARCHAR2(1024),
0104     CONSTRAINT COMMANDS_PK PRIMARY KEY (cmd_id) -- USING INDEX LOCAL,
0105 );
0106
0107 CREATE INDEX COMMANDS_TYPE_ST_IDX ON COMMANDS (cmd_type, status, destination, request_id);
0108 CREATE INDEX COMMANDS_TYPE_ST_TF_IDX ON COMMANDS (cmd_type, status, destination, transform_id);
0109 CREATE INDEX COMMANDS_TYPE_ST_PR_IDX ON COMMANDS (cmd_type, status, destination, processing_id);
0110
0111 -- 2022.11.03
0112 alter table transforms add name VARCHAR2(255);
0113 alter table collections add failed_files NUMBER(10);
0114 alter table collections add missing_files NUMBER(10);
0115
0116
0117 -- 2022.11.30
0118 alter table contents add content_dep_id NUMBER(12);
0119 CREATE INDEX CONTENTS_DEP_IDX ON CONTENTS (request_id, transform_id, content_dep_id) LOCAL;
0120
0121 -- 2022.12.01
0122 alter table requests modify username VARCHAR2(50) default null;
0123
0124 -- 2022.12.06
0125 alter table collections add ext_files NUMBER(10);
0126 alter table collections add processed_ext_files NUMBER(10);
0127 alter table collections add failed_ext_files NUMBER(10);
0128 alter table collections add missing_ext_files NUMBER(10);
0129
0130
0131 -- 2022.12.08
0132 -- oracle 19
0133 CREATE TABLE CONTENTS_ext
0134 (
0135         content_id NUMBER(12) constraint CONTENT_EXT_PK_NN NOT NULL,
0136         transform_id NUMBER(12) constraint CONTENT_EXT_TF_ID_NN NOT NULL,
0137         coll_id NUMBER(14),
0138         request_id NUMBER(12),
0139         workload_id NUMBER(10),
0140         map_id NUMBER(12) DEFAULT 0,
0141         status NUMBER(2) constraint CONTENT_EXT_STATUS_NN NOT NULL,
0142         panda_id NUMBER(14),
0143         job_definition_id NUMBER(12),
0144         scheduler_id VARCHAR2(128),
0145         pilot_id VARCHAR2(200),
0146         creation_time DATE,
0147         modification_time DATE,
0148         start_time DATE,
0149         end_time DATE,
0150         prod_source_label VARCHAR2(20),
0151         prod_user_id VARCHAR2(250),
0152         assigned_priority NUMBER(5),
0153         current_priority NUMBER(5),
0154         attempt_nr NUMBER(5),
0155         max_attempt NUMBER(5),
0156         max_cpu_count NUMBER(5),
0157         max_cpu_unit VARCHAR2(32),
0158         max_disk_count NUMBER(12),
0159         max_disk_unit VARCHAR2(10),
0160         min_ram_count NUMBER(12),
0161         min_ram_unit VARCHAR2(10),
0162         cpu_consumption_time NUMBER(12),
0163         cpu_consumption_unit VARCHAR2(128),
0164         job_status VARCHAR2(10),
0165         job_name VARCHAR2(255),
0166         trans_exit_code NUMBER(5),
0167         pilot_error_code NUMBER(5),
0168         pilot_error_diag VARCHAR2(500),
0169         exe_error_code NUMBER(5),
0170         exe_error_diag VARCHAR2(500),
0171         sup_error_code NUMBER(5),
0172         sup_error_diag VARCHAR2(250),
0173         ddm_error_code NUMBER(5),
0174         ddm_error_diag VARCHAR2(500),
0175         brokerage_error_code NUMBER(5),
0176         brokerage_error_diag VARCHAR2(250),
0177         job_dispatcher_error_code NUMBER(5),
0178         job_dispatcher_error_diag VARCHAR2(250),
0179         task_buffer_error_code NUMBER(5),
0180         task_buffer_error_diag VARCHAR2(300),
0181         computing_site VARCHAR2(128),
0182         computing_element VARCHAR2(128),
0183         grid VARCHAR2(50),
0184         cloud VARCHAR2(50),
0185         cpu_conversion float(20),
0186         task_id NUMBER(12),
0187         vo VARCHAR2(16),
0188         pilot_timing VARCHAR2(100),
0189         working_group VARCHAR2(20),
0190         processing_type VARCHAR2(64),
0191         prod_user_name VARCHAR2(60),
0192         core_count NUMBER(5),
0193         n_input_files NUMBER(10),
0194         req_id NUMBER(12),
0195         jedi_task_id NUMBER(12),
0196         actual_core_count NUMBER(5),
0197         max_rss NUMBER(12),
0198         max_vmem NUMBER(12),
0199         max_swap NUMBER(12),
0200         max_pss NUMBER(12),
0201         avg_rss NUMBER(12),
0202         avg_vmem NUMBER(12),
0203         avg_swap NUMBER(12),
0204         avg_pss NUMBER(12),
0205         max_walltime NUMBER(12),
0206         disk_io NUMBER(12),
0207         failed_attempt NUMBER(5),
0208         hs06 NUMBER(12),
0209         hs06sec NUMBER(12),
0210         memory_leak VARCHAR2(10),
0211         memory_leak_x2 VARCHAR2(10),
0212         job_label VARCHAR2(20),
0213         CONSTRAINT CONTENT_EXT_PK PRIMARY KEY (content_id),
0214         CONSTRAINT CONTENT_EXT_TRANSFORM_ID_FK FOREIGN KEY(transform_id) REFERENCES TRANSFORMS(transform_id)
0215 )
0216 PCTFREE 3
0217 PARTITION BY RANGE(TRANSFORM_ID)
0218 INTERVAL ( 100000 )
0219 ( PARTITION initial_part VALUES LESS THAN (1) );
0220
0221 CREATE INDEX CONTENTS_EXT_RTF_IDX ON CONTENTS_ext (request_id, transform_id, workload_id, coll_id, content_id, panda_id, status) LOCAL;
0222
0223
0224 -- 2022.12.29
0225 create table contents_update(content_id number(12), substatus number(2))
0226 CREATE TRIGGER update_content_dep_status before delete ON contents_update
0227      for each row
0228         BEGIN
0229            update contents set substatus = :old.substatus where contents.content_dep_id = :old.content_id;
0230         END;
0231
0232 -- 2023.01.24
0233 alter table CONTENTS_ext modify max_cpu_count NUMBER(12);
0234
0235 -- 2023.01.25
0236 alter table contents_update add (
0237         request_id NUMBER(12),
0238         transform_id NUMBER(12),
0239         workload_id NUMBER(10),
0240         coll_id NUMBER(14));
0241
0242 -- 2023.02.01
0243 --- update (select c.content_id, c.substatus as c_substatus, t.substatus as t_substatus from  contents c inner join
0244 -- (select content_id, substatus from contents where request_id=486 and transform_id=3027 and content_relation_type =1 and status != substatus) t
0245 --- on c.content_dep_id = t.content_id where c.request_id=486 and c.substatus != t.substatus) set c_substatus = t_substatus;
0246
0247 --- remove
0248 """
0249 CREATE OR REPLACE FUNCTION update_contents_to_others(request_id_in IN NUMBER, transform_id_in IN NUMBER)
0250 RETURN NUMBER
0251 IS num_rows NUMBER;
0252 BEGIN
0253     update (select c.content_id, c.substatus as c_substatus, t.substatus as t_substatus from  contents c inner join
0254     (select content_id, substatus from contents where request_id = request_id_in and transform_id = transform_id_in and content_relation_type = 1 and status != substatus) t
0255     on c.content_dep_id = t.content_id where c.request_id = request_id_in and c.substatus != t.substatus) set c_substatus = t_substatus;
0256
0257     num_rows := SQL%rowcount;
0258     RETURN (num_rows);
0259 END;
0260
0261 CREATE OR REPLACE FUNCTION update_contents_from_others(request_id_in IN NUMBER, transform_id_in IN NUMBER)
0262 RETURN NUMBER
0263 IS num_rows NUMBER;
0264 BEGIN
0265     update (select c.content_id, c.substatus as c_substatus, t.substatus as t_substatus from  contents c inner join
0266     (select content_id, substatus from contents where request_id = request_id_in and content_relation_type = 1 and status != 0) t
0267     on c.content_dep_id = t.content_id where c.request_id = request_id_in and c.transform_id = transform_id_in and c.substatus != t.substatus) set c_substatus = t_substatus;
0268
0269     num_rows := SQL%rowcount;
0270     RETURN (num_rows);
0271 END;
0272 """
0273
0274 CREATE OR REPLACE procedure update_contents_from_others(request_id_in NUMBER, transform_id_in NUMBER) AS
0275 BEGIN
0276     update (select c.content_id, c.substatus as c_substatus, t.substatus as t_substatus from  contents c inner join
0277     (select content_id, substatus from contents where request_id = request_id_in and content_relation_type = 1) t
0278     on c.content_dep_id = t.content_id where c.request_id = request_id_in and c.transform_id = transform_id_in and c.content_relation_type = 3 and c.substatus != t.substatus) set c_substatus = t_substatus;
0279 END;
0280
0281
0282 CREATE OR REPLACE procedure update_contents_to_others(request_id_in NUMBER, transform_id_in NUMBER) AS
0283 BEGIN
0284     update (select c.content_id, c.substatus as c_substatus, t.substatus as t_substatus from  contents c inner join
0285     (select content_id, substatus from contents where request_id = request_id_in and transform_id = transform_id_in and content_relation_type = 1) t
0286     on c.content_dep_id = t.content_id where c.request_id = request_id_in and c.content_relation_type = 3 and c.substatus != t.substatus) set c_substatus = t_substatus;
0287 END;
0288
0289
0290
0291 -- 2023.02.14
0292 drop index CONTENTS_REQ_TF_COLL_IDX
0293 CREATE INDEX CONTENTS_REQ_TF_COLL_IDX ON CONTENTS (request_id, transform_id, workload_id, coll_id, content_relation_type, status, substatus) LOCAL;
0294
0295
0296 -- 2023.02.22
0297
0298 CREATE OR REPLACE procedure update_contents_from_others(request_id_in NUMBER, transform_id_in NUMBER) AS
0299 BEGIN
0300     update (select c.content_id, c.substatus as c_substatus, t.substatus as t_substatus from
0301     (select content_id, substatus, content_dep_id from contents where request_id = request_id_in and transform_id = transform_id_in and content_relation_type = 3) c inner join
0302     (select content_id, substatus from contents where request_id = request_id_in and content_relation_type = 1) t
0303     on c.content_dep_id = t.content_id where c.substatus != t.substatus) set c_substatus = t_substatus;
0304 END;
0305
0306
0307 CREATE OR REPLACE procedure update_contents_to_others(request_id_in NUMBER, transform_id_in NUMBER) AS
0308 BEGIN
0309     update (select c.content_id, c.substatus as c_substatus, t.substatus as t_substatus from
0310     (select content_id, substatus, content_dep_id from contents where request_id = request_id_in and content_relation_type = 3) c inner join
0311     (select content_id, substatus from contents where request_id = request_id_in and transform_id = transform_id_in and content_relation_type = 1) t
0312     on c.content_dep_id = t.content_id where c.substatus != t.substatus) set c_substatus = t_substatus;
0313 END;
0314
0315
0316
0317 --- 2023.03.06
0318 drop index PROCESSINGS_STATUS_POLL_IDX;
0319 drop index CONTENTS_REL_IDX;
0320 drop index CONTENTS_TF_IDX;
0321 drop index CONTENTS_EXT_RTW_IDX;
0322 drop index CONTENTS_EXT_RTM_IDX;
0323 drop index COMMANDS_STATUS_IDX;
0324 drop index MESSAGES_ST_IDX;
0325 drop index MESSAGES_TYPE_STU_IDX;
0326 drop index REQUESTS_STATUS_POLL_IDX;
0327 drop index TRANSFORMS_REQ_IDX;
0328 drop index TRANSFORMS_STATUS_POLL_IDX;
0329 drop index COLLECTIONS_REQ_IDX;
0330
0331 CREATE INDEX PROCESSINGS_STATUS_POLL_IDX ON PROCESSINGS (status, processing_id, locking, updated_at, new_poll_period, update_poll_period, created_at) COMPRESS 3 LOCAL;
0332
0333 CREATE INDEX CONTENTS_REL_IDX ON CONTENTS  (request_id, content_relation_type, transform_id, substatus) COMPRESS 3 LOCAL;
0334 CREATE INDEX CONTENTS_TF_IDX ON CONTENTS  (transform_id, request_id, coll_id, content_relation_type, map_id) COMPRESS 4 LOCAL;
0335
0336 CREATE INDEX CONTENTS_EXT_RTW_IDX ON contents_ext (request_id, transform_id, workload_id) COMPRESS 3 ;
0337 CREATE INDEX CONTENTS_EXT_RTM_IDX ON contents_ext (request_id, transform_id, map_id) COMPRESS 2;
0338
0339 CREATE INDEX COMMANDS_STATUS_IDX on commands (status, locking, updated_at) COMPRESS 2;
0340
0341 CREATE INDEX MESSAGES_ST_IDX on messages (status, destination, created_at) COMPRESS 2;
0342 CREATE INDEX MESSAGES_TYPE_STU_IDX on messages (msg_type, status, destination, retries, updated_at, created_at) COMPRESS 3;
0343
0344 CREATE INDEX REQUESTS_STATUS_POLL_IDX on REQUESTS (status, request_id, locking, priority, updated_at, new_poll_period, update_poll_period, next_poll_at, created_at) COMPRESS 3 LOCAL;
0345
0346 CREATE INDEX TRANSFORMS_REQ_IDX on transforms (request_id, transform_id) COMPRESS 2;
0347 CREATE INDEX TRANSFORMS_STATUS_POLL_IDX on transforms (status, transform_id, locking, updated_at, new_poll_period, update_poll_period, created_at) COMPRESS 3 LOCAL;
0348
0349 CREATE INDEX COLLECTIONS_REQ_IDX on collections (request_id, transform_id, updated_at) COMPRESS 2;
0350
0351
0352 -- 2023.03.10
0353
0354 CREATE SEQUENCE EVENT_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0355 CREATE TABLE EVENTS
0356 (
0357     event_id NUMBER(12) DEFAULT ON NULL EVENT_ID_SEQ.NEXTVAL constraint EVENT_ID_NN NOT NULL,
0358     event_type NUMBER(12),
0359     event_actual_id NUMBER(12),
0360     priority NUMBER(12),
0361     status NUMBER(2),
0362     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0363     processing_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0364     processed_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0365     content CLOB,
0366     CONSTRAINT EVENTS_PK PRIMARY KEY (event_id) -- USING INDEX LOCAL,
0367 );
0368
0369 CREATE TABLE EVENTS_ARCHIVE
0370 (
0371     event_id NUMBER(12),
0372     event_type NUMBER(12),
0373     event_actual_id NUMBER(12),
0374     priority NUMBER(12),
0375     status NUMBER(2),
0376     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0377     processing_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0378     processed_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0379     content CLOB,
0380     CONSTRAINT EVENTS_AR_PK PRIMARY KEY (event_id) -- USING INDEX LOCAL,
0381 );
0382
0383 CREATE TABLE EVENTS_PRIORITY
0384 (
0385     event_type NUMBER(12),
0386     event_actual_id NUMBER(12),
0387     priority NUMBER(12),
0388     last_processed_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0389     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0390     CONSTRAINT EVENTS_PR_PK PRIMARY KEY (event_type, event_actual_id) -- USING INDEX LOCAL,
0391 );
0392
0393
0394 --- 2023.03.16
0395 alter table HEALTH add (status NUMBER(2));
0396 alter table contents_update add content_metadata CLOB;
0397 alter table health modify payload VARCHAR2(2048);
0398
0399
0400 --- 2023.03.29
0401 alter table contents_update add fetch_status NUMBER(2) DEFAULT 0;
0402
0403
0404 -- 2023.05.18
0405 alter table requests add site VARCHAR2(50);
0406 CREATE INDEX REQUESTS_STATUS_SITE ON requests  (status, site, request_id) COMPRESS 3 LOCAL;
0407
0408 alter table transforms add site VARCHAR2(50);
0409 CREATE INDEX TRANSFORMS_STATUS_SITE ON transforms  (status, site, request_id, transform_id) COMPRESS 3 LOCAL;
0410
0411 alter table processings add site VARCHAR2(50);
0412 CREATE INDEX PROCESSINGS_STATUS_SITE ON processings  (status, site, request_id, transform_id, processing_id) COMPRESS 3 LOCAL;
0413
0414 alter table messages add fetching_id NUMBER(12);
0415
0416
0417 CREATE SEQUENCE THROTTLER_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0418 CREATE TABLE Throttlers
0419 (
0420     throttler_id NUMBER(12) DEFAULT ON NULL THROTTLER_ID_SEQ.NEXTVAL constraint THROTTLER_ID_NN NOT NULL,
0421     site VARCHAR2(50),
0422     status NUMBER(2),
0423     num_requests NUMBER(12),
0424     num_transforms NUMBER(12),
0425     num_processings NUMBER(12),
0426     new_contents NUMBER(12),
0427     queue_contents NUMBER(12),
0428     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0429     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0430     others CLOB,
0431     CONSTRAINT THROTTLER_PK PRIMARY KEY (throttler_id), -- USING INDEX LOCAL,
0432     CONSTRAINT THROTTLER_SITE_UQ UNIQUE (site)
0433 );
0434
0435 alter table Messages add (poll_period INTERVAL DAY TO SECOND DEFAULT '00 00:05:00');
0436
0437
0438 --- 20230626
0439 alter table contents add (external_coll_id NUMBER(12), external_content_id NUMBER(12), external_event_id NUMBER(12), external_event_status NUMBER(2));
0440
0441 alter table contents add (sub_map_id NUMBER(12) default 0);
0442 alter table contents add (dep_sub_map_id NUMBER(12) default 0);
0443 alter table contents drop constraint CONTENT_ID_UQ;
0444 alter table contents add constraint CONTENT_ID_UQ UNIQUE (transform_id, coll_id, map_id, sub_map_id, dep_sub_map_id, content_relation_type, name, min_id, max_id) USING INDEX LOCAL;
0445
0446
0447 --- 20230927
0448 alter table contents add (name_md5 varchar2(33), scope_name_md5 varchar2(33));
0449 update contents set name_md5=standard_hash(name, 'MD5'), scope_name_md5=standard_hash(scope || name, 'MD5');
0450 alter table contents drop constraint CONTENT_ID_UQ;
0451 alter table contents add 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) USING INDEX LOCAL;
0452 drop index CONTENTS_ID_NAME_IDX;
0453 CREATE INDEX CONTENTS_ID_NAME_IDX ON CONTENTS (coll_id, scope, standard_hash(name, 'MD5'), status);
0454
0455
0456 --- 20240111
0457 CREATE SEQUENCE METAINFO_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0458 CREATE TABLE meta_info
0459 (
0460     meta_id NUMBER(12) DEFAULT ON NULL METAINFO_ID_SEQ.NEXTVAL constraint METAINFO_ID_NN NOT NULL,
0461     name VARCHAR2(50),
0462     status NUMBER(2),
0463     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0464     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0465     description VARCHAR2(1000),
0466     meta_info CLOB,
0467     CONSTRAINT METAINFO_PK PRIMARY KEY (meta_id), -- USING INDEX LOCAL,
0468     CONSTRAINT METAINFO_NAME_UQ UNIQUE (name)
0469 );
0470
0471 --- 20240219
0472 alter table TRANSFORMS add (parent_transform_id NUMBER(12));
0473 alter table TRANSFORMS add (previous_transform_id NUMBER(12));
0474 alter table TRANSFORMS add (current_processing_id NUMBER(12));
0475 alter table PROCESSINGS add (processing_type NUMBER(2));
0476
0477 --- 20240327
0478 alter table requests modify (transform_tag VARCHAR2(20));
0479
0480 --- 20240703
0481 alter table requests add (campaign VARCHAR2(100), campaign_group VARCHAR2(250), campaign_tag VARCHAR2(20));
0482 alter table transforms add (internal_id VARCHAR2(20), has_previous_conditions NUMBER(3), loop_index NUMBER(3), cloned_from NUMBER(12), triggered_conditions CLOB, untriggered_conditions CLOB);
0483
0484 alter table messages add (internal_id VARCHAR2(20));
0485
0486 CREATE SEQUENCE CONDITION_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0487 CREATE TABLE conditions
0488 (
0489     condition_id NUMBER(12) DEFAULT ON NULL CONDITION_ID_SEQ.NEXTVAL constraint CONDITION_ID_NN NOT NULL,
0490     request_id NUMBER(12),
0491     internal_id VARCHAR2(20),
0492     status NUMBER(2),
0493     is_loop NUMBER(2),
0494     loop_index NUMBER(2),
0495     cloned_from NUMBER(12),
0496     created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0497     updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)),
0498     evaluate_result VARCHAR2(200),
0499     previous_transforms CLOB,
0500     following_transforms CLOB,
0501     condition CLOB,
0502     CONSTRAINT CONDITION_PK PRIMARY KEY (condition_id), -- USING INDEX LOCAL,
0503     CONSTRAINT CONDITION_ID_UQ UNIQUE (request_id, internal_id)
0504 );
0505
0506 --- 20250806
0507 alter table requests add (locking_hostname VARCHAR2(50), locking_pid NUMBER(12), locking_thread_id NUMBER(12), locking_thread_name VARCHAR2(100));
0508 alter table transforms add (locking_hostname VARCHAR2(50), locking_pid NUMBER(12), locking_thread_id NUMBER(12), locking_thread_name VARCHAR2(100));
0509 alter table processings add (locking_hostname VARCHAR2(50), locking_pid NUMBER(12), locking_thread_id NUMBER(12), locking_thread_name VARCHAR2(100));
0510
0511 CREATE SEQUENCE REQUEST_GROUP_ID_SEQ MINVALUE 1 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE GLOBAL;
0512 CREATE TABLE requests_group (
0513     group_id NUMBER(19,0) DEFAULT REQUEST_GROUP_ID_SEQ.NEXTVAL,
0514     campaign VARCHAR2(50 CHAR) DEFAULT 'Default' NOT NULL,
0515     campaign_scope VARCHAR2(25 CHAR) DEFAULT 'Default' NOT NULL, -- Replace XXX with SCOPE_LENGTH
0516     campaign_group VARCHAR2(255 CHAR) NOT NULL, -- Replace YYY with NAME_LENGTH
0517     campaign_tag VARCHAR2(100 CHAR) NOT NULL,
0518     requester VARCHAR2(20 CHAR),
0519     group_type VARCHAR2(255 CHAR) NOT NULL, -- Enums are typically VARCHARs in DB
0520     username VARCHAR2(20 CHAR),
0521     userdn VARCHAR2(200 CHAR),
0522     priority NUMBER(10,0) DEFAULT 50 NOT NULL,
0523     status VARCHAR2(255 CHAR) NOT NULL,
0524     substatus VARCHAR2(255 CHAR) DEFAULT '0',
0525     oldstatus VARCHAR2(255 CHAR) DEFAULT '0',
0526     locking VARCHAR2(255 CHAR) NOT NULL,
0527     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
0528     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
0529     next_poll_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
0530     accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
0531     expired_at TIMESTAMP,
0532     new_retries NUMBER(10,0) DEFAULT 0,
0533     update_retries NUMBER(10,0) DEFAULT 0,
0534     max_new_retries NUMBER(10,0) DEFAULT 3,
0535     max_update_retries NUMBER(10,0) DEFAULT 0,
0536     new_poll_period INTERVAL DAY TO SECOND DEFAULT INTERVAL '1' SECOND,
0537     update_poll_period INTERVAL DAY TO SECOND DEFAULT INTERVAL '10' SECOND,
0538     site VARCHAR2(50 CHAR),
0539     locking_hostname VARCHAR2(50 CHAR),
0540     locking_pid NUMBER(19,0),
0541     locking_thread_id NUMBER(19,0),
0542     locking_thread_name VARCHAR2(100 CHAR),
0543     errors CLOB, -- Assuming JSON string as CLOB
0544     group_metadata CLOB, -- JSON Field
0545     processing_metadata CLOB -- JSON Field
0546 );
0547
0548 ALTER TABLE requests_group ADD CONSTRAINT REQUESTGROUP_PK PRIMARY KEY (group_id);
0549
0550 ALTER TABLE requests_group ADD CONSTRAINT REQUESTGROUP_CM_UQ UNIQUE (campaign, campaign_scope, campaign_group, campaign_tag);
0551
0552 ALTER TABLE requests_group ADD CONSTRAINT REQUESTGROUP_STATUS_ID_NN CHECK (status IS NOT NULL);
0553
0554 CREATE UNIQUE INDEX REQUESTGROUP_CM_NAME_IDX ON requests_group (campaign, campaign_scope, campaign_group, campaign_tag);
0555
0556 CREATE INDEX REQUESTGROUP_STATUS_SITE ON requests_group (status, site, group_id);
0557
0558 CREATE INDEX REQUESTGROUP_STATUS_PRIO_IDX ON requests_group (status, priority, group_id, locking, updated_at, next_poll_at, created_at);
0559
0560 CREATE INDEX REQUESTGROUP_STATUS_POLL_IDX ON requests_group (status, priority, locking, updated_at, new_poll_period, update_poll_period, created_at, group_id);
0561
0562 -- Alter 'requests' table
0563 ALTER TABLE requests MODIFY campaign_tag VARCHAR2(100 CHAR);
0564
0565 ALTER TABLE requests ADD (group_id NUMBER(19,0));
0566
0567 ALTER TABLE requests ADD CONSTRAINT REQUESTS_GROUP_ID_FK FOREIGN KEY (group_id) REFERENCES schema_name.requests_group(group_id);
0568
0569
0570 -- Add 'additional_data_storage' column to 'requests' table
0571 ALTER TABLE requests ADD (additional_data_storage VARCHAR2(512 CHAR));
0572
0573 -- Add 'parent_internal_id' column to 'transforms' table
0574 ALTER TABLE transforms ADD (parent_internal_id VARCHAR2(20 CHAR));
0575
0576
0577 -- Add 'cloud' and 'queue' columns to 'requests' table
0578 ALTER TABLE requests ADD (
0579     cloud VARCHAR2(50 CHAR),
0580     queue VARCHAR2(50 CHAR)
0581 );
0582
0583 -- Add 'cloud' and 'queue' columns to 'requests_group' table
0584 ALTER TABLE requests_group ADD (
0585     cloud VARCHAR2(50 CHAR),
0586     queue VARCHAR2(50 CHAR)
0587 );
0588
0589 -- Alter 'requests' table
0590 ALTER TABLE requests ADD (
0591     campaign_scope VARCHAR2(SCOPE_LENGTH CHAR),
0592     total_transforms NUMBER(10),
0593     finished_transforms NUMBER(10),
0594     subfinished_transforms NUMBER(10),
0595     failed_transforms NUMBER(10),
0596     processing_transforms NUMBER(10)
0597 );
0598
0599 ALTER TABLE requests MODIFY (
0600     campaign_group VARCHAR2(NAME_LENGTH CHAR),
0601     campaign_tag VARCHAR2(100 CHAR)
0602 );
0603
0604 -- Alter 'requests_group' table
0605 ALTER TABLE requests_group ADD (
0606     total_requests NUMBER(10),
0607     finished_requests NUMBER(10),
0608     subfinished_requests NUMBER(10),
0609     failed_requests NUMBER(10),
0610     processing_requests NUMBER(10),
0611     new_requests NUMBER(10)
0612 );
0613
0614 ALTER TABLE requests_group ADD (max_processing_requests NUMBER(10));
0615
0616 ALTER TABLE collections ADD (preprocessing_files NUMBER(19));
0617 ALTER TABLE collections ADD (activated_files NUMBER(19));
0618
0619