File indexing completed on 2026-04-09 07:58:17
0001 CREATE SEQUENCE doma_idds."REQUEST_ID_SEQ" START WITH 1
0002
0003 CREATE TABLE doma_idds.requests (
0004 request_id BIGINT NOT NULL,
0005 scope VARCHAR(25),
0006 name VARCHAR(255),
0007 requester VARCHAR(20),
0008 request_type INTEGER NOT NULL,
0009 username VARCHAR(20),
0010 userdn VARCHAR(200),
0011 transform_tag VARCHAR(20),
0012 workload_id INTEGER,
0013 priority INTEGER,
0014 status INTEGER NOT NULL,
0015 substatus INTEGER,
0016 oldstatus INTEGER,
0017 locking INTEGER NOT NULL,
0018 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0019 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0020 next_poll_at TIMESTAMP WITHOUT TIME ZONE,
0021 accessed_at TIMESTAMP WITHOUT TIME ZONE,
0022 expired_at TIMESTAMP WITHOUT TIME ZONE,
0023 new_retries INTEGER,
0024 update_retries INTEGER,
0025 max_new_retries INTEGER,
0026 max_update_retries INTEGER,
0027 new_poll_period INTERVAL,
0028 update_poll_period INTERVAL,
0029 site VARCHAR(50),
0030 errors VARCHAR(1024),
0031 request_metadata JSONB,
0032 processing_metadata JSONB,
0033 CONSTRAINT "REQUESTS_PK" PRIMARY KEY (request_id),
0034 CONSTRAINT "REQUESTS_STATUS_ID_NN" CHECK (status IS NOT NULL)
0035 );
0036
0037 CREATE INDEX "REQUESTS_SCOPE_NAME_IDX" ON doma_idds.requests (name, scope, workload_id);
0038
0039 CREATE INDEX "REQUESTS_STATUS_PRIO_IDX" ON doma_idds.requests (status, priority, request_id, locking, updated_at, next_poll_at, created_at);
0040
0041 CREATE INDEX "REQUESTS_STATUS_POLL_IDX" ON doma_idds.requests (status, priority, locking, updated_at, new_poll_period, update_poll_period, created_at, request_id);
0042
0043 CREATE INDEX "REQUESTS_STATUS_SITE" ON doma_idds.requests (status, site, request_id);
0044
0045 CREATE SEQUENCE doma_idds."TRANSFORM_ID_SEQ" START WITH 1
0046
0047 CREATE TABLE doma_idds.transforms (
0048 transform_id BIGINT NOT NULL,
0049 request_id BIGINT NOT NULL,
0050 workload_id INTEGER,
0051 transform_type INTEGER NOT NULL,
0052 transform_tag VARCHAR(20),
0053 priority INTEGER,
0054 parent_transform_id BIGINT,
0055 previous_transform_id BIGINT,
0056 current_processing_id BIGINT,
0057 processing_type INTEGER,
0058 safe2get_output_from_input INTEGER,
0059 status INTEGER NOT NULL,
0060 substatus INTEGER,
0061 oldstatus INTEGER,
0062 locking INTEGER NOT NULL,
0063 retries INTEGER,
0064 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0065 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0066 next_poll_at TIMESTAMP WITHOUT TIME ZONE,
0067 started_at TIMESTAMP WITHOUT TIME ZONE,
0068 finished_at TIMESTAMP WITHOUT TIME ZONE,
0069 expired_at TIMESTAMP WITHOUT TIME ZONE,
0070 new_retries INTEGER,
0071 update_retries INTEGER,
0072 max_new_retries INTEGER,
0073 max_update_retries INTEGER,
0074 new_poll_period INTERVAL,
0075 update_poll_period INTERVAL,
0076 site VARCHAR(50),
0077 name VARCHAR(255),
0078 errors VARCHAR(1024),
0079 transform_metadata JSONB,
0080 running_metadata JSONB,
0081 CONSTRAINT "TRANSFORMS_PK" PRIMARY KEY (transform_id),
0082 CONSTRAINT "TRANSFORMS_STATUS_ID_NN" CHECK (status IS NOT NULL)
0083 );
0084
0085 CREATE INDEX "TRANSFORMS_TYPE_TAG_IDX" ON doma_idds.transforms (transform_type, transform_tag, transform_id);
0086
0087 CREATE INDEX "TRANSFORMS_STATUS_SITE" ON doma_idds.transforms (status, site, request_id, transform_id);
0088
0089 CREATE INDEX "TRANSFORMS_REQ_IDX" ON doma_idds.transforms (request_id, transform_id);
0090
0091 CREATE INDEX "TRANSFORMS_STATUS_POLL_IDX" ON doma_idds.transforms (status, locking, updated_at, new_poll_period, update_poll_period, created_at, transform_id);
0092
0093 CREATE INDEX "TRANSFORMS_STATUS_UPDATED_AT_IDX" ON doma_idds.transforms (status, locking, updated_at, next_poll_at, created_at);
0094
0095
0096 CREATE TABLE doma_idds.contents_update (
0097 content_id BIGSERIAL NOT NULL,
0098 substatus INTEGER,
0099 request_id BIGINT,
0100 transform_id BIGINT,
0101 workload_id INTEGER,
0102 fetch_status INTEGER NOT NULL,
0103 coll_id BIGINT,
0104 content_metadata VARCHAR(100),
0105 PRIMARY KEY (content_id)
0106 );
0107
0108
0109 CREATE TABLE doma_idds.contents_ext (
0110 content_id BIGSERIAL NOT NULL,
0111 transform_id BIGINT NOT NULL,
0112 coll_id BIGINT NOT NULL,
0113 request_id BIGINT NOT NULL,
0114 workload_id INTEGER,
0115 map_id BIGINT NOT NULL,
0116 status INTEGER NOT NULL,
0117 panda_id BIGINT,
0118 job_definition_id BIGINT,
0119 scheduler_id VARCHAR(128),
0120 pilot_id VARCHAR(200),
0121 creation_time TIMESTAMP WITHOUT TIME ZONE,
0122 modification_time TIMESTAMP WITHOUT TIME ZONE,
0123 start_time TIMESTAMP WITHOUT TIME ZONE,
0124 end_time TIMESTAMP WITHOUT TIME ZONE,
0125 prod_source_label VARCHAR(20),
0126 prod_user_id VARCHAR(250),
0127 assigned_priority INTEGER,
0128 current_priority INTEGER,
0129 attempt_nr INTEGER,
0130 max_attempt INTEGER,
0131 max_cpu_count INTEGER,
0132 max_cpu_unit VARCHAR(32),
0133 max_disk_count INTEGER,
0134 max_disk_unit VARCHAR(10),
0135 min_ram_count INTEGER,
0136 min_ram_unit VARCHAR(10),
0137 cpu_consumption_time INTEGER,
0138 cpu_consumption_unit VARCHAR(128),
0139 job_status VARCHAR(10),
0140 job_name VARCHAR(255),
0141 trans_exit_code INTEGER,
0142 pilot_error_code INTEGER,
0143 pilot_error_diag VARCHAR(500),
0144 exe_error_code INTEGER,
0145 exe_error_diag VARCHAR(500),
0146 sup_error_code INTEGER,
0147 sup_error_diag VARCHAR(250),
0148 ddm_error_code INTEGER,
0149 ddm_error_diag VARCHAR(500),
0150 brokerage_error_code INTEGER,
0151 brokerage_error_diag VARCHAR(250),
0152 job_dispatcher_error_code INTEGER,
0153 job_dispatcher_error_diag VARCHAR(250),
0154 task_buffer_error_code INTEGER,
0155 task_buffer_error_diag VARCHAR(300),
0156 computing_site VARCHAR(128),
0157 computing_element VARCHAR(128),
0158 grid VARCHAR(50),
0159 cloud VARCHAR(50),
0160 cpu_conversion FLOAT,
0161 task_id BIGINT,
0162 vo VARCHAR(16),
0163 pilot_timing VARCHAR(100),
0164 working_group VARCHAR(20),
0165 processing_type VARCHAR(64),
0166 prod_user_name VARCHAR(60),
0167 core_count INTEGER,
0168 n_input_files INTEGER,
0169 req_id BIGINT,
0170 jedi_task_id BIGINT,
0171 actual_core_count INTEGER,
0172 max_rss INTEGER,
0173 max_vmem INTEGER,
0174 max_swap INTEGER,
0175 max_pss INTEGER,
0176 avg_rss INTEGER,
0177 avg_vmem INTEGER,
0178 avg_swap INTEGER,
0179 avg_pss INTEGER,
0180 max_walltime INTEGER,
0181 disk_io INTEGER,
0182 failed_attempt INTEGER,
0183 hs06 INTEGER,
0184 hs06sec INTEGER,
0185 memory_leak VARCHAR(10),
0186 memory_leak_x2 VARCHAR(10),
0187 job_label VARCHAR(20),
0188 CONSTRAINT "CONTENTS_EXT_PK" PRIMARY KEY (content_id, request_id)
0189 ) PARTITION BY RANGE (request_id) ;
0190
0191 CREATE INDEX "CONTENTS_EXT_RTW_IDX" ON doma_idds.contents_ext (request_id, transform_id, workload_id);
0192
0193 CREATE INDEX "CONTENTS_EXT_RTM_IDX" ON doma_idds.contents_ext (request_id, transform_id, map_id);
0194
0195 CREATE INDEX "CONTENTS_EXT_RTF_IDX" ON doma_idds.contents_ext (request_id, transform_id, workload_id, coll_id, content_id, panda_id, status);
0196
0197 CREATE SEQUENCE doma_idds."HEALTH_ID_SEQ" START WITH 1
0198
0199 CREATE TABLE doma_idds.health (
0200 health_id BIGINT NOT NULL,
0201 agent VARCHAR(30),
0202 hostname VARCHAR(500),
0203 pid INTEGER,
0204 status INTEGER NOT NULL,
0205 thread_id BIGINT,
0206 thread_name VARCHAR(255),
0207 created_at TIMESTAMP WITHOUT TIME ZONE,
0208 updated_at TIMESTAMP WITHOUT TIME ZONE,
0209 payload VARCHAR(2048),
0210 CONSTRAINT "HEALTH_PK" PRIMARY KEY (health_id),
0211 CONSTRAINT "HEALTH_UK" UNIQUE (agent, hostname, pid, thread_id)
0212 );
0213
0214 CREATE SEQUENCE doma_idds."MESSAGE_ID_SEQ" START WITH 1
0215
0216 CREATE TABLE doma_idds.messages (
0217 msg_id BIGINT NOT NULL,
0218 msg_type INTEGER NOT NULL,
0219 status INTEGER NOT NULL,
0220 substatus INTEGER,
0221 locking INTEGER NOT NULL,
0222 source INTEGER NOT NULL,
0223 destination INTEGER NOT NULL,
0224 request_id BIGINT,
0225 workload_id INTEGER,
0226 transform_id INTEGER,
0227 processing_id INTEGER,
0228 num_contents INTEGER,
0229 retries INTEGER,
0230 fetching_id INTEGER,
0231 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0232 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0233 poll_period INTERVAL NOT NULL,
0234 msg_content JSONB,
0235 CONSTRAINT "MESSAGES_PK" PRIMARY KEY (msg_id)
0236 );
0237
0238 CREATE INDEX "MESSAGES_TYPE_ST_IDX" ON doma_idds.messages (msg_type, status, destination, request_id);
0239
0240 CREATE INDEX "MESSAGES_TYPE_STU_IDX" ON doma_idds.messages (msg_type, status, destination, retries, updated_at, created_at);
0241
0242 CREATE INDEX "MESSAGES_TYPE_ST_TF_IDX" ON doma_idds.messages (msg_type, status, destination, transform_id);
0243
0244 CREATE INDEX "MESSAGES_TYPE_ST_PR_IDX" ON doma_idds.messages (msg_type, status, destination, processing_id);
0245
0246 CREATE INDEX "MESSAGES_ST_IDX" ON doma_idds.messages (status, destination, created_at);
0247
0248 CREATE SEQUENCE doma_idds."COMMAND_ID_SEQ" START WITH 1
0249
0250 CREATE TABLE doma_idds.commands (
0251 cmd_id BIGINT NOT NULL,
0252 request_id BIGINT NOT NULL,
0253 workload_id INTEGER,
0254 transform_id INTEGER,
0255 processing_id INTEGER,
0256 cmd_type INTEGER,
0257 status INTEGER NOT NULL,
0258 substatus INTEGER,
0259 locking INTEGER NOT NULL,
0260 username VARCHAR(50),
0261 retries INTEGER,
0262 source INTEGER,
0263 destination INTEGER,
0264 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0265 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0266 cmd_content JSONB,
0267 errors VARCHAR(1024),
0268 CONSTRAINT "COMMANDS_PK" PRIMARY KEY (cmd_id)
0269 );
0270
0271 CREATE INDEX "COMMANDS_TYPE_ST_TF_IDX" ON doma_idds.commands (cmd_type, status, destination, transform_id);
0272
0273 CREATE INDEX "COMMANDS_TYPE_ST_PR_IDX" ON doma_idds.commands (cmd_type, status, destination, processing_id);
0274
0275 CREATE INDEX "COMMANDS_TYPE_ST_IDX" ON doma_idds.commands (cmd_type, status, destination, request_id);
0276
0277 CREATE INDEX "COMMANDS_STATUS_IDX" ON doma_idds.commands (status, locking, updated_at);
0278
0279
0280 CREATE TABLE doma_idds.events_priority (
0281 event_type INTEGER NOT NULL,
0282 event_actual_id INTEGER NOT NULL,
0283 priority INTEGER NOT NULL,
0284 last_processed_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0285 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0286 CONSTRAINT "EVENTS_PR_PK" PRIMARY KEY (event_type, event_actual_id)
0287 );
0288
0289 CREATE SEQUENCE doma_idds."EVENT_ID_SEQ" START WITH 1
0290
0291 CREATE TABLE doma_idds.events (
0292 event_id BIGINT NOT NULL,
0293 event_type INTEGER NOT NULL,
0294 event_actual_id INTEGER NOT NULL,
0295 priority INTEGER,
0296 status INTEGER NOT NULL,
0297 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0298 processing_at TIMESTAMP WITHOUT TIME ZONE,
0299 processed_at TIMESTAMP WITHOUT TIME ZONE,
0300 content JSONB,
0301 CONSTRAINT "EVENTS_PK" PRIMARY KEY (event_id)
0302 );
0303
0304
0305 CREATE TABLE doma_idds.events_archive (
0306 event_id BIGSERIAL NOT NULL,
0307 event_type INTEGER NOT NULL,
0308 event_actual_id INTEGER NOT NULL,
0309 priority INTEGER,
0310 status INTEGER NOT NULL,
0311 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0312 processing_at TIMESTAMP WITHOUT TIME ZONE,
0313 processed_at TIMESTAMP WITHOUT TIME ZONE,
0314 content JSONB,
0315 CONSTRAINT "EVENTS_AR_PK" PRIMARY KEY (event_id)
0316 );
0317
0318 CREATE SEQUENCE doma_idds."THROTTLER_ID_SEQ" START WITH 1
0319
0320 CREATE TABLE doma_idds.throttlers (
0321 throttler_id BIGSERIAL NOT NULL,
0322 site VARCHAR(50) NOT NULL,
0323 status INTEGER NOT NULL,
0324 num_requests INTEGER,
0325 num_transforms INTEGER,
0326 num_processings INTEGER,
0327 new_contents INTEGER,
0328 queue_contents INTEGER,
0329 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0330 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0331 others JSONB,
0332 CONSTRAINT "THROTTLER_PK" PRIMARY KEY (throttler_id),
0333 CONSTRAINT "THROTTLER_SITE_UQ" UNIQUE (site)
0334 );
0335
0336 CREATE SEQUENCE doma_idds."WORKPROGRESS_ID_SEQ" START WITH 1
0337
0338 CREATE TABLE doma_idds.workprogresses (
0339 workprogress_id BIGINT NOT NULL,
0340 request_id BIGINT,
0341 workload_id INTEGER,
0342 scope VARCHAR(25),
0343 name VARCHAR(255),
0344 priority INTEGER,
0345 status INTEGER,
0346 substatus INTEGER,
0347 locking INTEGER,
0348 created_at TIMESTAMP WITHOUT TIME ZONE,
0349 updated_at TIMESTAMP WITHOUT TIME ZONE,
0350 next_poll_at TIMESTAMP WITHOUT TIME ZONE,
0351 accessed_at TIMESTAMP WITHOUT TIME ZONE,
0352 expired_at TIMESTAMP WITHOUT TIME ZONE,
0353 errors VARCHAR(1024),
0354 workprogress_metadata JSONB,
0355 processing_metadata JSONB,
0356 CONSTRAINT "WORKPROGRESS_PK" PRIMARY KEY (workprogress_id),
0357 CONSTRAINT "REQ2WORKPROGRESS_REQ_ID_FK" FOREIGN KEY(request_id) REFERENCES doma_idds.requests (request_id),
0358 CONSTRAINT "WORKPROGRESS_STATUS_ID_NN" CHECK (status IS NOT NULL)
0359 );
0360
0361 CREATE INDEX "WORKPROGRESS_SCOPE_NAME_IDX" ON doma_idds.workprogresses (name, scope, workprogress_id);
0362
0363 CREATE INDEX "WORKPROGRESS_STATUS_PRIO_IDX" ON doma_idds.workprogresses (status, priority, workprogress_id, locking, updated_at, next_poll_at, created_at);
0364
0365 CREATE SEQUENCE doma_idds."PROCESSING_ID_SEQ" START WITH 1
0366
0367 CREATE TABLE doma_idds.processings (
0368 processing_id BIGINT NOT NULL,
0369 transform_id BIGINT NOT NULL,
0370 request_id BIGINT NOT NULL,
0371 workload_id INTEGER,
0372 status INTEGER NOT NULL,
0373 substatus INTEGER,
0374 oldstatus INTEGER,
0375 locking INTEGER NOT NULL,
0376 submitter VARCHAR(20),
0377 submitted_id INTEGER,
0378 granularity INTEGER,
0379 granularity_type INTEGER,
0380 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0381 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0382 next_poll_at TIMESTAMP WITHOUT TIME ZONE,
0383 poller_updated_at TIMESTAMP WITHOUT TIME ZONE,
0384 submitted_at TIMESTAMP WITHOUT TIME ZONE,
0385 finished_at TIMESTAMP WITHOUT TIME ZONE,
0386 expired_at TIMESTAMP WITHOUT TIME ZONE,
0387 new_retries INTEGER,
0388 update_retries INTEGER,
0389 max_new_retries INTEGER,
0390 max_update_retries INTEGER,
0391 new_poll_period INTERVAL,
0392 update_poll_period INTERVAL,
0393 site VARCHAR(50),
0394 errors VARCHAR(1024),
0395 processing_metadata JSONB,
0396 running_metadata JSONB,
0397 output_metadata JSONB,
0398 CONSTRAINT "PROCESSINGS_PK" PRIMARY KEY (processing_id),
0399 CONSTRAINT "PROCESSINGS_TRANSFORM_ID_FK" FOREIGN KEY(transform_id) REFERENCES doma_idds.transforms (transform_id),
0400 CONSTRAINT "PROCESSINGS_STATUS_ID_NN" CHECK (status IS NOT NULL),
0401 CONSTRAINT "PROCESSINGS_TRANSFORM_ID_NN" CHECK (transform_id IS NOT NULL)
0402 );
0403
0404 CREATE INDEX "PROCESSINGS_STATUS_UPDATED_IDX" ON doma_idds.processings (status, locking, updated_at, next_poll_at, created_at);
0405
0406 CREATE INDEX "PROCESSINGS_STATUS_POLL_IDX" ON doma_idds.processings (status, processing_id, locking, updated_at, new_poll_period, update_poll_period, created_at);
0407
0408 CREATE INDEX "PROCESSINGS_STATUS_SITE" ON doma_idds.processings (status, site, request_id, transform_id, processing_id);
0409
0410 CREATE SEQUENCE doma_idds."COLLECTION_ID_SEQ" START WITH 1
0411
0412 CREATE TABLE doma_idds.collections (
0413 coll_id BIGINT NOT NULL,
0414 request_id BIGINT NOT NULL,
0415 workload_id INTEGER,
0416 transform_id BIGINT NOT NULL,
0417 coll_type INTEGER NOT NULL,
0418 relation_type INTEGER NOT NULL,
0419 scope VARCHAR(25),
0420 name VARCHAR(255),
0421 bytes INTEGER,
0422 status INTEGER NOT NULL,
0423 substatus INTEGER,
0424 locking INTEGER NOT NULL,
0425 total_files INTEGER,
0426 storage_id INTEGER,
0427 new_files INTEGER,
0428 processed_files INTEGER,
0429 processing_files INTEGER,
0430 failed_files INTEGER,
0431 missing_files INTEGER,
0432 ext_files INTEGER,
0433 processed_ext_files INTEGER,
0434 failed_ext_files INTEGER,
0435 missing_ext_files INTEGER,
0436 processing_id INTEGER,
0437 retries INTEGER,
0438 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0439 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0440 next_poll_at TIMESTAMP WITHOUT TIME ZONE,
0441 accessed_at TIMESTAMP WITHOUT TIME ZONE,
0442 expired_at TIMESTAMP WITHOUT TIME ZONE,
0443 coll_metadata JSONB,
0444 CONSTRAINT "COLLECTIONS_PK" PRIMARY KEY (coll_id),
0445 CONSTRAINT "COLLECTIONS_NAME_SCOPE_UQ" UNIQUE (name, scope, transform_id, relation_type),
0446 CONSTRAINT "COLLECTIONS_TRANSFORM_ID_FK" FOREIGN KEY(transform_id) REFERENCES doma_idds.transforms (transform_id),
0447 CONSTRAINT "COLLECTIONS_STATUS_ID_NN" CHECK (status IS NOT NULL),
0448 CONSTRAINT "COLLECTIONS_TRANSFORM_ID_NN" CHECK (transform_id IS NOT NULL)
0449 );
0450
0451 CREATE INDEX "COLLECTIONS_STATUS_UPDATED_IDX" ON doma_idds.collections (status, locking, updated_at, next_poll_at, created_at);
0452
0453 CREATE INDEX "COLLECTIONS_TRANSFORM_IDX" ON doma_idds.collections (transform_id, coll_id);
0454
0455 CREATE INDEX "COLLECTIONS_STATUS_RELAT_IDX" ON doma_idds.collections (status, relation_type);
0456
0457 CREATE INDEX "COLLECTIONS_REQ_IDX" ON doma_idds.collections (request_id, transform_id, updated_at);
0458
0459
0460 CREATE TABLE doma_idds.wp2transforms (
0461 workprogress_id BIGINT NOT NULL,
0462 transform_id BIGINT NOT NULL,
0463 CONSTRAINT "WP2TRANSFORM_PK" PRIMARY KEY (workprogress_id, transform_id),
0464 CONSTRAINT "WP2TRANSFORM_WORK_ID_FK" FOREIGN KEY(workprogress_id) REFERENCES doma_idds.workprogresses (workprogress_id),
0465 CONSTRAINT "WP2TRANSFORM_TRANS_ID_FK" FOREIGN KEY(transform_id) REFERENCES doma_idds.transforms (transform_id)
0466 );
0467
0468 CREATE SEQUENCE doma_idds."CONTENT_ID_SEQ" START WITH 1
0469
0470 CREATE TABLE doma_idds.contents (
0471 content_id BIGINT NOT NULL,
0472 transform_id BIGINT NOT NULL,
0473 coll_id BIGINT NOT NULL,
0474 request_id BIGINT NOT NULL,
0475 workload_id INTEGER,
0476 map_id BIGINT NOT NULL,
0477 sub_map_id BIGINT,
0478 dep_sub_map_id BIGINT,
0479 content_dep_id BIGINT,
0480 scope VARCHAR(25),
0481 name VARCHAR(4000),
0482 name_md5 VARCHAR(33),
0483 scope_name_md5 VARCHAR(33),
0484 min_id INTEGER,
0485 max_id INTEGER,
0486 content_type INTEGER NOT NULL,
0487 content_relation_type INTEGER NOT NULL,
0488 status INTEGER NOT NULL,
0489 substatus INTEGER,
0490 locking INTEGER NOT NULL,
0491 bytes INTEGER,
0492 md5 VARCHAR(32),
0493 adler32 VARCHAR(8),
0494 processing_id INTEGER,
0495 storage_id INTEGER,
0496 retries INTEGER,
0497 external_coll_id BIGINT,
0498 external_content_id BIGINT,
0499 external_event_id BIGINT,
0500 external_event_status INTEGER,
0501 path VARCHAR(4000),
0502 created_at TIMESTAMP WITHOUT TIME ZONE,
0503 updated_at TIMESTAMP WITHOUT TIME ZONE,
0504 accessed_at TIMESTAMP WITHOUT TIME ZONE,
0505 expired_at TIMESTAMP WITHOUT TIME ZONE,
0506 content_metadata VARCHAR(1000),
0507 CONSTRAINT "CONTENTS_PK" PRIMARY KEY (content_id, request_id),
0508 CONSTRAINT "CONTENT_ID_UQ" UNIQUE (transform_id, coll_id, request_id, map_id, sub_map_id, dep_sub_map_id, content_relation_type, name_md5, scope_name_md5, min_id, max_id),
0509 CONSTRAINT "CONTENTS_TRANSFORM_ID_FK" FOREIGN KEY(transform_id) REFERENCES doma_idds.transforms (transform_id),
0510 CONSTRAINT "CONTENTS_COLL_ID_FK" FOREIGN KEY(coll_id) REFERENCES doma_idds.collections (coll_id),
0511 CONSTRAINT "CONTENTS_STATUS_ID_NN" CHECK (status IS NOT NULL),
0512 CONSTRAINT "CONTENTS_COLL_ID_NN" CHECK (coll_id IS NOT NULL)
0513 ) PARTITION BY RANGE (request_id) ;
0514
0515 CREATE INDEX "CONTENTS_STATUS_UPDATED_IDX" ON doma_idds.contents (status, locking, updated_at, created_at);
0516
0517 CREATE INDEX "CONTENTS_REL_IDX" ON doma_idds.contents (request_id, content_relation_type, transform_id, substatus);
0518
0519 CREATE INDEX "CONTENTS_DEP_IDX" ON doma_idds.contents (request_id, transform_id, content_dep_id);
0520
0521 CREATE INDEX "CONTENTS_REQ_TF_COLL_IDX" ON doma_idds.contents (request_id, transform_id, workload_id, coll_id, content_relation_type, status, substatus);
0522
0523 CREATE INDEX "CONTENTS_TF_IDX" ON doma_idds.contents (transform_id, request_id, coll_id, map_id, content_relation_type);
0524
0525 CREATE INDEX "CONTENTS_ID_NAME_IDX" ON doma_idds.contents (coll_id, scope, md5('name'), status);
0526
0527
0528 SET search_path TO doma_idds;
0529 CREATE OR REPLACE PROCEDURE update_contents_to_others(request_id_in int, transform_id_in int)
0530 AS $$
0531 BEGIN
0532 UPDATE doma_idds.contents set substatus = d.substatus from
0533 (select content_id, content_dep_id, substatus from doma_idds.contents where request_id = request_id_in and transform_id = transform_id_in and content_relation_type = 1 and status != 0) d
0534 where doma_idds.contents.request_id = request_id_in and doma_idds.contents.content_relation_type = 3 and doma_idds.contents.substatus != d.substatus and d.content_id = doma_idds.contents.content_dep_id;
0535 END;
0536 $$ LANGUAGE PLPGSQL
0537
0538
0539 SET search_path TO doma_idds;
0540 CREATE OR REPLACE PROCEDURE update_contents_from_others(request_id_in int, transform_id_in int)
0541 AS $$
0542 BEGIN
0543
0544 UPDATE doma_idds.contents set substatus = d.substatus from
0545 (select content_id, content_dep_id, substatus from doma_idds.contents where request_id = request_id_in and content_relation_type = 1 and status != 0) d
0546 where doma_idds.contents.request_id = request_id_in and doma_idds.contents.transform_id = transform_id_in and doma_idds.contents.content_relation_type = 3 and doma_idds.contents.substatus != d.substatus and d.content_id = doma_idds.contents.content_dep_id;
0547 END;
0548 $$ LANGUAGE PLPGSQL
0549
0550
0551 CREATE SEQUENCE doma_idds."METAINFO_ID_SEQ" START WITH 1;
0552 CREATE TABLE meta_info
0553 (
0554 meta_id BIGINT NOT NULL,
0555 name VARCHAR(50),
0556 status INTEGER,
0557 created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0558 updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
0559 description VARCHAR(1000),
0560 meta_info JSONB,
0561 CONSTRAINT "METAINFO_PK" PRIMARY KEY (meta_id),
0562 CONSTRAINT "METAINFO_NAME_UQ" UNIQUE (name)
0563 );
