Newer
Older
Adam Laskowski
committed
-- Creating tables
CREATE TABLE CONTROLLED_VOCABULARIES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F', MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, IS_CHOSEN_FROM_LIST BOOLEAN_CHAR NOT NULL DEFAULT TRUE, SOURCE_URI CHARACTER VARYING(250));
CREATE TABLE CONTROLLED_VOCABULARY_TERMS (ID TECH_ID NOT NULL,CODE OBJECT_NAME NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,COVO_ID TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,LABEL COLUMN_LABEL, DESCRIPTION DESCRIPTION_2000, ORDINAL ORDINAL_INT NOT NULL, IS_OFFICIAL BOOLEAN_CHAR NOT NULL DEFAULT 'T');
CREATE TABLE DATA_ALL (ID TECH_ID NOT NULL,CODE CODE,DATA_SET_KIND DATA_SET_KIND DEFAULT 'PHYSICAL' NOT NULL,DSTY_ID TECH_ID NOT NULL,DAST_ID TECH_ID NOT NULL,EXPE_ID TECH_ID,DATA_PRODUCER_CODE CODE,PRODUCTION_TIMESTAMP TIME_STAMP,SAMP_ID TECH_ID,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID,IS_VALID BOOLEAN_CHAR DEFAULT 'T', MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, ACCESS_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, IS_DERIVED BOOLEAN_CHAR NOT NULL, DEL_ID TECH_ID, ORIG_DEL TECH_ID, PERS_ID_MODIFIER TECH_ID, VERSION INTEGER DEFAULT 0, FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_CHILDREN BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_PARENTS BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_COMPS BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_CONTS BOOLEAN_CHAR NOT NULL DEFAULT 'F', EXPE_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', SAMP_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', TSVECTOR_DOCUMENT TSVECTOR NOT NULL);
CREATE TABLE DATA_SET_RELATIONSHIPS_ALL (DATA_ID_PARENT TECH_ID NOT NULL,DATA_ID_CHILD TECH_ID NOT NULL, RELATIONSHIP_ID TECH_ID NOT NULL, ORDINAL INTEGER, DEL_ID TECH_ID, PERS_ID_AUTHOR TECH_ID, REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, PARENT_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', CHILD_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', COMP_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', CONT_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE DATA_STORES (ID TECH_ID NOT NULL,UUID CODE NOT NULL,CODE CODE NOT NULL,DOWNLOAD_URL VARCHAR(1024) NOT NULL,REMOTE_URL VARCHAR(250) NOT NULL,SESSION_TOKEN VARCHAR(50) NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, IS_ARCHIVER_CONFIGURED BOOLEAN_CHAR NOT NULL DEFAULT 'F', DATA_SOURCE_DEFINITIONS TEXT_VALUE);
CREATE TABLE DATA_STORE_SERVICES (ID TECH_ID NOT NULL, KEY VARCHAR(256) NOT NULL, LABEL VARCHAR(256) NOT NULL, KIND DATA_STORE_SERVICE_KIND NOT NULL, DATA_STORE_ID TECH_ID NOT NULL, REPORTING_PLUGIN_TYPE DATA_STORE_SERVICE_REPORTING_PLUGIN_TYPE);
CREATE TABLE DATA_STORE_SERVICE_DATA_SET_TYPES (DATA_STORE_SERVICE_ID TECH_ID NOT NULL, DATA_SET_TYPE_ID TECH_ID NOT NULL);
CREATE TABLE DATA_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000 NOT NULL);
CREATE TABLE EVENTS (ID TECH_ID NOT NULL,EVENT_TYPE EVENT_TYPE NOT NULL,DESCRIPTION TEXT_VALUE,REASON DESCRIPTION_2000,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, entity_type VARCHAR(80) NOT NULL, identifiers TEXT_VALUE NOT NULL, CONTENT TEXT_VALUE, EXAC_ID TECH_ID);
CREATE TABLE EVENTS_SEARCH (ID TECH_ID NOT NULL, EVENT_TYPE EVENT_TYPE NOT NULL, ENTITY_TYPE TEXT_VALUE NOT NULL, ENTITY_SPACE TEXT_VALUE, ENTITY_SPACE_PERM_ID TEXT_VALUE, ENTITY_PROJECT TEXT_VALUE, ENTITY_PROJECT_PERM_ID TEXT_VALUE, ENTITY_REGISTERER TEXT_VALUE, ENTITY_REGISTRATION_TIMESTAMP TIME_STAMP, IDENTIFIER TEXT_VALUE NOT NULL, DESCRIPTION TEXT_VALUE, REASON TEXT_VALUE, CONTENT TEXT_VALUE, EXAC_ID TECH_ID, PERS_ID_REGISTERER TECH_ID NOT NULL, REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL);
CREATE TABLE EXPERIMENTS_ALL (ID TECH_ID NOT NULL,PERM_ID CODE NOT NULL,CODE CODE NOT NULL,EXTY_ID TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, PROJ_ID TECH_ID NOT NULL,DEL_ID TECH_ID, ORIG_DEL TECH_ID, IS_PUBLIC BOOLEAN_CHAR NOT NULL DEFAULT 'F', PERS_ID_MODIFIER TECH_ID, VERSION INTEGER DEFAULT 0, FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_SAMP BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_DATA BOOLEAN_CHAR NOT NULL DEFAULT 'F', PROJ_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', TSVECTOR_DOCUMENT TSVECTOR NOT NULL);
CREATE TABLE ATTACHMENTS (ID TECH_ID NOT NULL,EXPE_ID TECH_ID,SAMP_ID TECH_ID,PROJ_ID TECH_ID,EXAC_ID TECH_ID NOT NULL,FILE_NAME FILE_NAME NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,VERSION INTEGER NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL, title TITLE_100, description DESCRIPTION_2000, PROJ_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', EXPE_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', SAMP_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE ATTACHMENT_CONTENTS (ID TECH_ID NOT NULL,VALUE FILE NOT NULL);
CREATE TABLE EXPERIMENT_PROPERTIES (ID TECH_ID NOT NULL,EXPE_ID TECH_ID NOT NULL,ETPT_ID TECH_ID NOT NULL,VALUE TEXT_VALUE, CVTE_ID TECH_ID, MATE_PROP_ID TECH_ID, SAMP_PROP_ID TECH_ID, PERS_ID_REGISTERER TECH_ID NOT NULL, REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_AUTHOR TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, EXPE_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', TSVECTOR_DOCUMENT TSVECTOR NOT NULL, IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
CREATE TABLE EXPERIMENT_PROPERTIES_HISTORY (ID TECH_ID NOT NULL, EXPE_ID TECH_ID NOT NULL, ETPT_ID TECH_ID NOT NULL, VALUE TEXT_VALUE, VOCABULARY_TERM IDENTIFIER, MATERIAL IDENTIFIER, SAMPLE IDENTIFIER, PERS_ID_AUTHOR TECH_ID NOT NULL, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
CREATE TABLE EXPERIMENT_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, VALIDATION_SCRIPT_ID TECH_ID);
CREATE TABLE EXPERIMENT_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,EXTY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F',PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, ORDINAL ORDINAL_INT NOT NULL, SECTION DESCRIPTION_2000,SCRIPT_ID TECH_ID,IS_SHOWN_EDIT BOOLEAN_CHAR NOT NULL DEFAULT 'T',SHOW_RAW_VALUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE EXTERNAL_DATA (ID TECH_ID NOT NULL,SHARE_ID CODE,SIZE ORDINAL_INT,LOCATION VARCHAR(1024) NOT NULL,FFTY_ID TECH_ID NOT NULL,LOTY_ID TECH_ID NOT NULL,CVTE_ID_STOR_FMT TECH_ID NOT NULL,IS_COMPLETE BOOLEAN_CHAR_OR_UNKNOWN NOT NULL DEFAULT 'U',CVTE_ID_STORE TECH_ID, STATUS ARCHIVING_STATUS NOT NULL DEFAULT 'AVAILABLE', PRESENT_IN_ARCHIVE BOOLEAN_CHAR DEFAULT 'F', SPEED_HINT INTEGER NOT NULL DEFAULT -50, STORAGE_CONFIRMATION BOOLEAN_CHAR NOT NULL DEFAULT 'F', H5_FOLDERS BOOLEAN_CHAR NOT NULL, H5AR_FOLDERS BOOLEAN_CHAR NOT NULL, ARCHIVING_REQUESTED BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE FILE_FORMAT_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000);
CREATE TABLE GRID_CUSTOM_COLUMNS (ID TECH_ID NOT NULL, CODE VARCHAR(200) NOT NULL, LABEL column_label NOT NULL, DESCRIPTION DESCRIPTION_2000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, EXPRESSION GRID_EXPRESSION NOT NULL, IS_PUBLIC BOOLEAN NOT NULL, GRID_ID GRID_ID NOT NULL);
CREATE TABLE SPACES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL, FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_PROJ BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_SAMP BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE DELETIONS (ID TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,REASON DESCRIPTION_2000 NOT NULL);
CREATE TABLE LOCATOR_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000);
CREATE TABLE MATERIALS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,MATY_ID TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, TSVECTOR_DOCUMENT TSVECTOR NOT NULL);
Adam Laskowski
committed
CREATE TABLE MATERIAL_PROPERTIES (ID TECH_ID NOT NULL,MATE_ID TECH_ID NOT NULL,MTPT_ID TECH_ID NOT NULL,VALUE TEXT_VALUE,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_AUTHOR TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL,CVTE_ID TECH_ID, MATE_PROP_ID TECH_ID, TSVECTOR_DOCUMENT TSVECTOR NOT NULL, IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
CREATE TABLE MATERIAL_PROPERTIES_HISTORY (ID TECH_ID NOT NULL, MATE_ID TECH_ID NOT NULL, MTPT_ID TECH_ID NOT NULL, VALUE TEXT_VALUE, VOCABULARY_TERM IDENTIFIER, MATERIAL IDENTIFIER, PERS_ID_AUTHOR TECH_ID NOT NULL, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
Adam Laskowski
committed
CREATE TABLE MATERIAL_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, VALIDATION_SCRIPT_ID TECH_ID);
CREATE TABLE MATERIAL_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,MATY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F',REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL, ORDINAL ORDINAL_INT NOT NULL, SECTION DESCRIPTION_2000,SCRIPT_ID TECH_ID,IS_SHOWN_EDIT BOOLEAN_CHAR NOT NULL DEFAULT 'T',SHOW_RAW_VALUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE DATA_SET_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, MAIN_DS_PATTERN VARCHAR(300), MAIN_DS_PATH VARCHAR(1000), DELETION_DISALLOW BOOLEAN_CHAR DEFAULT 'F', VALIDATION_SCRIPT_ID TECH_ID);
CREATE TABLE PERSONS (ID TECH_ID NOT NULL,FIRST_NAME VARCHAR(30),LAST_NAME VARCHAR(30),USER_ID USER_ID NOT NULL,EMAIL OBJECT_NAME,SPACE_ID TECH_ID,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID, DISPLAY_SETTINGS FILE, IS_ACTIVE BOOLEAN DEFAULT TRUE);
CREATE TABLE PROJECTS (ID TECH_ID NOT NULL,PERM_ID CODE NOT NULL,CODE CODE NOT NULL,SPACE_ID TECH_ID NOT NULL,PERS_ID_LEADER TECH_ID,DESCRIPTION TEXT_VALUE,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, PERS_ID_MODIFIER TECH_ID, VERSION INTEGER DEFAULT 0, FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_EXP BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_SAMP BOOLEAN_CHAR NOT NULL DEFAULT 'F', SPACE_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE PROPERTY_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000 NOT NULL,LABEL COLUMN_LABEL NOT NULL,DATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,COVO_ID TECH_ID,IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F', MATY_PROP_ID TECH_ID, SATY_PROP_ID TECH_ID, SCHEMA TEXT_VALUE, TRANSFORMATION TEXT_VALUE, META_DATA JSONB);
CREATE TABLE ROLE_ASSIGNMENTS (ID TECH_ID NOT NULL,ROLE_CODE AUTHORIZATION_ROLE NOT NULL,SPACE_ID TECH_ID, PROJECT_ID TECH_ID, PERS_ID_GRANTEE TECH_ID, AG_ID_GRANTEE TECH_ID, PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE SAMPLES_ALL (ID TECH_ID NOT NULL,PERM_ID CODE NOT NULL, sample_identifier sample_identifier, CODE CODE NOT NULL, EXPE_ID TECH_ID,SATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,DEL_ID TECH_ID, ORIG_DEL TECH_ID, SPACE_ID TECH_ID, SAMP_ID_PART_OF TECH_ID, PERS_ID_MODIFIER TECH_ID, code_unique_check character varying(300), subcode_unique_check character varying(300), VERSION INTEGER DEFAULT 0, PROJ_ID TECH_ID, FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_COMP BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_CHILDREN BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_PARENTS BOOLEAN_CHAR NOT NULL DEFAULT 'F', FROZEN_FOR_DATA BOOLEAN_CHAR NOT NULL DEFAULT 'F', SPACE_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', PROJ_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', EXPE_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', CONT_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', TSVECTOR_DOCUMENT TSVECTOR NOT NULL);
CREATE TABLE SAMPLE_PROPERTIES (ID TECH_ID NOT NULL,SAMP_ID TECH_ID NOT NULL,STPT_ID TECH_ID NOT NULL,VALUE TEXT_VALUE,CVTE_ID TECH_ID,MATE_PROP_ID TECH_ID,SAMP_PROP_ID TECH_ID,PERS_ID_REGISTERER TECH_ID NOT NULL, REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_AUTHOR TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, SAMP_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', TSVECTOR_DOCUMENT TSVECTOR NOT NULL, IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
CREATE TABLE SAMPLE_PROPERTIES_HISTORY (ID TECH_ID NOT NULL, SAMP_ID TECH_ID NOT NULL, STPT_ID TECH_ID NOT NULL, VALUE TEXT_VALUE, VOCABULARY_TERM IDENTIFIER, MATERIAL IDENTIFIER, SAMPLE IDENTIFIER, PERS_ID_AUTHOR TECH_ID NOT NULL, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
CREATE TABLE SAMPLE_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_2000, IS_LISTABLE BOOLEAN_CHAR NOT NULL DEFAULT 'T', GENERATED_FROM_DEPTH INTEGER NOT NULL DEFAULT 0, PART_OF_DEPTH INTEGER NOT NULL DEFAULT 0, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, is_auto_generated_code BOOLEAN_CHAR NOT NULL DEFAULT 'F', generated_code_prefix CODE NOT NULL DEFAULT 'S', is_subcode_unique BOOLEAN_CHAR NOT NULL DEFAULT 'F', INHERIT_PROPERTIES BOOLEAN_CHAR NOT NULL DEFAULT 'F', VALIDATION_SCRIPT_ID TECH_ID, SHOW_PARENT_METADATA BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE SAMPLE_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,SATY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F',PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, IS_DISPLAYED BOOLEAN_CHAR NOT NULL DEFAULT 'T', ORDINAL ORDINAL_INT NOT NULL, SECTION DESCRIPTION_2000,SCRIPT_ID TECH_ID,IS_SHOWN_EDIT BOOLEAN_CHAR NOT NULL DEFAULT 'T',SHOW_RAW_VALUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F');
Adam Laskowski
committed
CREATE TABLE DATA_SET_PROPERTIES (ID TECH_ID NOT NULL,DS_ID TECH_ID NOT NULL,DSTPT_ID TECH_ID NOT NULL,VALUE TEXT_VALUE,CVTE_ID TECH_ID, MATE_PROP_ID TECH_ID, SAMP_PROP_ID TECH_ID, PERS_ID_REGISTERER TECH_ID NOT NULL, REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_AUTHOR TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, DASE_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', TSVECTOR_DOCUMENT TSVECTOR NOT NULL, IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
CREATE TABLE DATA_SET_PROPERTIES_HISTORY (ID TECH_ID NOT NULL, DS_ID TECH_ID NOT NULL, DSTPT_ID TECH_ID NOT NULL, VALUE TEXT_VALUE, VOCABULARY_TERM IDENTIFIER, MATERIAL IDENTIFIER, SAMPLE IDENTIFIER, PERS_ID_AUTHOR TECH_ID NOT NULL, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, INTEGER_ARRAY_VALUE LONG_VALUE[], REAL_ARRAY_VALUE DOUBLE_VALUE[], STRING_ARRAY_VALUE TEXT_VALUE[], TIMESTAMP_ARRAY_VALUE TIME_STAMP[], JSON_VALUE JSONB);
Adam Laskowski
committed
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
CREATE TABLE DATA_SET_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,DSTY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',IS_MANAGED_INTERNALLY BOOLEAN_CHAR NOT NULL DEFAULT 'F',PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, ORDINAL ORDINAL_INT NOT NULL, SECTION DESCRIPTION_2000,SCRIPT_ID TECH_ID, IS_SHOWN_EDIT BOOLEAN_CHAR NOT NULL DEFAULT 'T',SHOW_RAW_VALUE BOOLEAN_CHAR NOT NULL DEFAULT 'F', IS_UNIQUE BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE AUTHORIZATION_GROUPS (ID TECH_ID NOT NULL, CODE CODE NOT NULL, DESCRIPTION DESCRIPTION_2000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE AUTHORIZATION_GROUP_PERSONS (AG_ID TECH_ID NOT NULL, PERS_ID TECH_ID NOT NULL);
CREATE TABLE FILTERS (ID TECH_ID NOT NULL, NAME VARCHAR(200) NOT NULL, DESCRIPTION DESCRIPTION_2000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, EXPRESSION TEXT NOT NULL, IS_PUBLIC BOOLEAN NOT NULL, GRID_ID VARCHAR(200) NOT NULL);
CREATE TABLE QUERIES (ID TECH_ID NOT NULL, NAME VARCHAR(200) NOT NULL, DESCRIPTION DESCRIPTION_2000,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, PERS_ID_REGISTERER TECH_ID NOT NULL, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, EXPRESSION TEXT NOT NULL, IS_PUBLIC BOOLEAN NOT NULL, QUERY_TYPE QUERY_TYPE NOT NULL, ENTITY_TYPE_CODE CODE, DB_KEY CODE NOT NULL DEFAULT '1');
CREATE TABLE relationship_types (id TECH_ID NOT NULL, code CODE NOT NULL, label COLUMN_LABEL, parent_label COLUMN_LABEL, child_label COLUMN_LABEL, description DESCRIPTION_2000, registration_timestamp TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, pers_id_registerer TECH_ID NOT NULL, is_managed_internally BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE sample_relationships_all (id TECH_ID NOT NULL, sample_id_parent TECH_ID NOT NULL, relationship_id TECH_ID NOT NULL, sample_id_child TECH_ID NOT NULL, del_id TECH_ID, PERS_ID_AUTHOR TECH_ID, REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, MODIFICATION_TIMESTAMP TIME_STAMP DEFAULT CURRENT_TIMESTAMP, PARENT_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', CHILD_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F', CHILD_ANNOTATIONS JSONB, PARENT_ANNOTATIONS JSONB);
CREATE TABLE scripts (ID TECH_ID NOT NULL, NAME VARCHAR(200) NOT NULL, SCRIPT_TYPE SCRIPT_TYPE NOT NULL, DESCRIPTION DESCRIPTION_2000,SCRIPT TEXT_VALUE,REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,ENTITY_KIND ENTITY_KIND, PLUGIN_TYPE PLUGIN_TYPE NOT NULL DEFAULT 'JYTHON', IS_AVAILABLE BOOLEAN_CHAR NOT NULL DEFAULT TRUE);
CREATE TABLE CORE_PLUGINS (ID TECH_ID NOT NULL, NAME VARCHAR(200) NOT NULL, VERSION INTEGER NOT NULL, REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, MASTER_REG_SCRIPT TEXT_VALUE);
CREATE TABLE POST_REGISTRATION_DATASET_QUEUE (ID TECH_ID NOT NULL, DS_ID TECH_ID NOT NULL);
CREATE TABLE ENTITY_OPERATIONS_LOG (ID TECH_ID NOT NULL, REGISTRATION_ID TECH_ID NOT NULL);
CREATE TABLE PROJECT_RELATIONSHIPS_HISTORY (ID TECH_ID NOT NULL, MAIN_PROJ_ID TECH_ID NOT NULL, RELATION_TYPE TEXT_VALUE, EXPE_ID TECH_ID, SAMP_ID TECH_ID, SPACE_ID TECH_ID, ENTITY_KIND TEXT_VALUE, ENTITY_PERM_ID TEXT_VALUE, PERS_ID_AUTHOR TECH_ID, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP);
CREATE TABLE EXPERIMENT_RELATIONSHIPS_HISTORY (ID TECH_ID NOT NULL, MAIN_EXPE_ID TECH_ID NOT NULL, RELATION_TYPE TEXT_VALUE, SAMP_ID TECH_ID, DATA_ID TECH_ID, ENTITY_KIND TEXT_VALUE, ENTITY_PERM_ID TEXT_VALUE, PERS_ID_AUTHOR TECH_ID, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP, PROJ_ID TECH_ID);
CREATE TABLE SAMPLE_RELATIONSHIPS_HISTORY (ID TECH_ID NOT NULL, MAIN_SAMP_ID TECH_ID NOT NULL, RELATION_TYPE TEXT_VALUE, EXPE_ID TECH_ID, SAMP_ID TECH_ID, DATA_ID TECH_ID, ENTITY_KIND TEXT_VALUE, ENTITY_PERM_ID TEXT_VALUE, PERS_ID_AUTHOR TECH_ID, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP, SPACE_ID TECH_ID, PROJ_ID TECH_ID, ANNOTATIONS JSONB);
CREATE TABLE DATA_SET_RELATIONSHIPS_HISTORY (ID TECH_ID NOT NULL, MAIN_DATA_ID TECH_ID NOT NULL, RELATION_TYPE TEXT_VALUE, ORDINAL INTEGER, EXPE_ID TECH_ID, SAMP_ID TECH_ID, DATA_ID TECH_ID, ENTITY_KIND TEXT_VALUE, ENTITY_PERM_ID TEXT_VALUE, PERS_ID_AUTHOR TECH_ID, VALID_FROM_TIMESTAMP TIME_STAMP NOT NULL, VALID_UNTIL_TIMESTAMP TIME_STAMP);
CREATE TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS (ID TECH_ID, CODE CODE NOT NULL, LABEL TEXT_VALUE, ADDRESS TEXT_VALUE NOT NULL, ADDRESS_TYPE EDMS_ADDRESS_TYPE NOT NULL);
CREATE TABLE LINK_DATA(ID TECH_ID NOT NULL, DATA_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE CONTENT_COPIES (ID TECH_ID NOT NULL, LOCATION_TYPE LOCATION_TYPE NOT NULL, DATA_ID TECH_ID NOT NULL, EDMS_ID TECH_ID NOT NULL, EXTERNAL_CODE TEXT_VALUE, PATH TEXT_VALUE, GIT_COMMIT_HASH TEXT_VALUE, GIT_REPOSITORY_ID TEXT_VALUE, LOCATION_UNIQUE_CHECK TEXT_VALUE NOT NULL, PERS_ID_REGISTERER TECH_ID, REGISTRATION_TIMESTAMP TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP, DATA_FROZEN BOOLEAN_CHAR NOT NULL DEFAULT 'F');
CREATE TABLE METAPROJECTS (ID TECH_ID NOT NULL, NAME CODE NOT NULL, DESCRIPTION DESCRIPTION_2000, OWNER TECH_ID NOT NULL, PRIVATE BOOLEAN_CHAR NOT NULL DEFAULT TRUE, CREATION_DATE TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE METAPROJECT_ASSIGNMENTS_ALL (ID TECH_ID NOT NULL, MEPR_ID TECH_ID NOT NULL, EXPE_ID TECH_ID, SAMP_ID TECH_ID, DATA_ID TECH_ID, MATE_ID TECH_ID, DEL_ID TECH_ID, CREATION_DATE TIME_STAMP_DFL NOT NULL DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE OPERATION_EXECUTIONS (
ID TECH_ID NOT NULL,
CODE CODE NOT NULL,
STATE OPERATION_EXECUTION_STATE NOT NULL DEFAULT 'NEW',
OWNER TECH_ID NOT NULL,
DESCRIPTION TEXT_VALUE,
NOTIFICATION TEXT_VALUE,
AVAILABILITY OPERATION_EXECUTION_AVAILABILITY NOT NULL DEFAULT 'AVAILABLE',
AVAILABILITY_TIME BIGINT NOT NULL DEFAULT 1,
SUMMARY_OPERATIONS TEXT_VALUE,
SUMMARY_PROGRESS TEXT_VALUE,
SUMMARY_ERROR TEXT_VALUE,
SUMMARY_RESULTS TEXT_VALUE,
SUMMARY_AVAILABILITY OPERATION_EXECUTION_AVAILABILITY NOT NULL DEFAULT 'AVAILABLE',
SUMMARY_AVAILABILITY_TIME BIGINT NOT NULL DEFAULT 1,
DETAILS_PATH VARCHAR(1000),
DETAILS_AVAILABILITY OPERATION_EXECUTION_AVAILABILITY NOT NULL DEFAULT 'AVAILABLE',
DETAILS_AVAILABILITY_TIME BIGINT NOT NULL DEFAULT 1,
CREATION_DATE TIME_STAMP_DFL NOT NULL,
START_DATE TIME_STAMP,
FINISH_DATE TIME_STAMP
);
CREATE TABLE data_set_copies_history (
id TECH_ID NOT NULL,
cc_id TECH_ID NOT NULL,
data_id TECH_ID NOT NULL,
external_code TEXT_VALUE,
path TEXT_VALUE,
git_commit_hash TEXT_VALUE,
git_repository_id TEXT_VALUE,
edms_id TECH_ID NOT NULL,
edms_code CODE,
edms_label TEXT_VALUE,
edms_address TEXT_VALUE,
pers_id_author TECH_ID,
valid_from_timestamp TIME_STAMP NOT NULL,
valid_until_timestamp TIME_STAMP);
CREATE TABLE SEMANTIC_ANNOTATIONS (ID TECH_ID NOT NULL,
PERM_ID CODE NOT NULL,
SATY_ID TECH_ID,
STPT_ID TECH_ID,
PRTY_ID TECH_ID,
PREDICATE_ONTOLOGY_ID TEXT,
PREDICATE_ONTOLOGY_VERSION TEXT,
PREDICATE_ACCESSION_ID TEXT,
DESCRIPTOR_ONTOLOGY_ID TEXT,
DESCRIPTOR_ONTOLOGY_VERSION TEXT,
DESCRIPTOR_ACCESSION_ID TEXT,
CREATION_DATE time_stamp_dfl NOT NULL
);
-- Creating views - copied from schema generated for tests, '*' can't be used because of PgDiffViews limitation in view comparison
CREATE VIEW data AS
SELECT id, code, dsty_id, dast_id, expe_id, expe_frozen, data_producer_code, production_timestamp, samp_id, samp_frozen,
registration_timestamp, access_timestamp, pers_id_registerer, pers_id_modifier, is_valid, modification_timestamp,
is_derived, del_id, orig_del, version, data_set_kind,
frozen, frozen_for_children, frozen_for_parents, frozen_for_comps, frozen_for_conts, tsvector_document
FROM data_all
WHERE del_id IS NULL;
CREATE VIEW data_deleted AS
SELECT id, code, dsty_id, dast_id, expe_id, data_producer_code, production_timestamp, samp_id, registration_timestamp, access_timestamp, pers_id_registerer, pers_id_modifier, is_valid, modification_timestamp, is_derived, del_id, orig_del, version, data_set_kind
FROM data_all
WHERE del_id IS NOT NULL;
CREATE VIEW experiments AS
SELECT id, perm_id, code, exty_id, pers_id_registerer, pers_id_modifier, registration_timestamp, modification_timestamp,
proj_id, proj_frozen, del_id, orig_del, is_public, version, frozen, frozen_for_samp, frozen_for_data, tsvector_document
FROM experiments_all
WHERE del_id IS NULL;
CREATE VIEW experiments_deleted AS
SELECT id, perm_id, code, exty_id, pers_id_registerer, pers_id_modifier, registration_timestamp, modification_timestamp, proj_id, del_id, orig_del, is_public, version
FROM experiments_all
WHERE del_id IS NOT NULL;
CREATE VIEW samples AS
SELECT id, perm_id, code, proj_id, proj_frozen, expe_id, expe_frozen, saty_id, registration_timestamp,
modification_timestamp, pers_id_registerer, pers_id_modifier, del_id, orig_del, space_id, space_frozen,
samp_id_part_of, cont_frozen, version, frozen, frozen_for_comp, frozen_for_children, frozen_for_parents, frozen_for_data, tsvector_document, sample_identifier
FROM samples_all
WHERE del_id IS NULL;
CREATE VIEW samples_deleted AS
SELECT id, perm_id, code, expe_id, saty_id, registration_timestamp, modification_timestamp, pers_id_registerer, pers_id_modifier, del_id, orig_del, space_id, proj_id, samp_id_part_of, version
FROM samples_all
WHERE del_id IS NOT NULL;
CREATE VIEW data_set_relationships AS
SELECT data_id_parent, parent_frozen, cont_frozen, data_id_child, child_frozen, comp_frozen,
relationship_id, ordinal, del_id, pers_id_author, registration_timestamp, modification_timestamp
FROM data_set_relationships_all
WHERE del_id IS NULL;
CREATE VIEW sample_relationships AS
SELECT id, sample_id_parent, parent_frozen, relationship_id, sample_id_child, child_frozen, del_id, pers_id_author, registration_timestamp, modification_timestamp, child_annotations, parent_annotations
FROM sample_relationships_all
WHERE del_id IS NULL;
CREATE VIEW METAPROJECT_ASSIGNMENTS AS
SELECT ID, MEPR_ID, EXPE_ID, SAMP_ID, DATA_ID, MATE_ID, DEL_ID, CREATION_DATE
FROM METAPROJECT_ASSIGNMENTS_ALL
WHERE DEL_ID IS NULL;
CREATE VIEW sample_history_view AS (
SELECT
2*id as id,
main_samp_id,
relation_type,
space_id,
expe_id,
samp_id,
proj_id,
data_id,
entity_kind,
entity_perm_id,
annotations,
null as stpt_id,
null as value,
null as vocabulary_term,
null as material,
null as sample,
pers_id_author,
valid_from_timestamp,
Adam Laskowski
committed
valid_until_timestamp,
null as integer_array_value,
null as real_array_value,
null as string_array_value,
null as timestamp_array_value,
null as json_value
Adam Laskowski
committed
FROM
SAMPLE_RELATIONSHIPS_HISTORY
WHERE
valid_until_timestamp IS NOT NULL)
UNION
SELECT
2*id+1 as id,
samp_id as main_samp_id,
null as relation_type,
null as space_id,
null as expe_id,
null as samp_id,
null as proj_id,
null as data_id,
null as entity_kind,
null as entity_perm_id,
null as annotations,
stpt_id,
value,
vocabulary_term,
material,
sample,
pers_id_author,
valid_from_timestamp,
Adam Laskowski
committed
valid_until_timestamp,
integer_array_value,
real_array_value,
string_array_value,
timestamp_array_value,
json_value
Adam Laskowski
committed
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
FROM
SAMPLE_PROPERTIES_HISTORY;
CREATE VIEW data_set_history_view AS (
SELECT
3*id as id,
main_data_id,
relation_type,
ordinal,
expe_id,
samp_id,
data_id,
entity_kind,
entity_perm_id,
null as dstpt_id,
null as value,
null as vocabulary_term,
null as material,
null as sample,
null as external_code,
null as path,
null as git_commit_hash,
null as git_repository_id,
null::TECH_ID as edms_id,
null as edms_code,
null as edms_label,
null as edms_address,
pers_id_author,
valid_from_timestamp,
Adam Laskowski
committed
valid_until_timestamp,
null as integer_array_value,
null as real_array_value,
null as string_array_value,
null as timestamp_array_value,
null as json_value
Adam Laskowski
committed
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
FROM
data_set_relationships_history
WHERE
valid_until_timestamp IS NOT NULL)
UNION
SELECT
3*id+1 as id,
ds_id as main_data_id,
null as relation_type,
null as ordinal,
null as expe_id,
null as samp_id,
null as data_id,
null as entity_kind,
null as entity_perm_id,
dstpt_id,
value,
vocabulary_term,
material,
sample,
null as external_code,
null as path,
null as git_commit_hash,
null as git_repository_id,
null as edms_id,
null as edms_code,
null as edms_label,
null as edms_address,
pers_id_author,
valid_from_timestamp,
Adam Laskowski
committed
valid_until_timestamp,
integer_array_value,
real_array_value,
string_array_value,
timestamp_array_value,
json_value
Adam Laskowski
committed
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
FROM
data_set_properties_history
UNION
(SELECT
3*id+2 as id,
data_id as main_data_id,
null as relation_type,
null as ordinal,
null as expe_id,
null as samp_id,
null as data_id,
null as entity_kind,
null as entity_perm_id,
null as dstpt_id,
null as value,
null as vocabulary_term,
null as material,
null as sample,
external_code,
path,
git_commit_hash,
git_repository_id,
edms_id,
edms_code,
edms_label,
edms_address,
pers_id_author,
valid_from_timestamp,
Adam Laskowski
committed
valid_until_timestamp,
null as integer_array_value,
null as real_array_value,
null as string_array_value,
null as timestamp_array_value,
null as json_value
Adam Laskowski
committed
FROM
data_set_copies_history
WHERE
valid_until_timestamp IS NOT NULL);
CREATE VIEW experiment_history_view AS (
SELECT
2*id as id,
main_expe_id,
relation_type,
proj_id,
samp_id,
data_id,
entity_kind,
entity_perm_id,
null as etpt_id,
null as value,
null as vocabulary_term,
null as material,
null as sample,
pers_id_author,
valid_from_timestamp,
Adam Laskowski
committed
valid_until_timestamp,
null as integer_array_value,
null as real_array_value,
null as string_array_value,
null as timestamp_array_value,
null as json_value
Adam Laskowski
committed
FROM
EXPERIMENT_RELATIONSHIPS_HISTORY
WHERE valid_until_timestamp IS NOT NULL)
UNION
SELECT
2*id+1 as id,
expe_id as main_expe_id,
null as relation_type,
null as proj_id,
null as samp_id,
null as data_id,
null as entity_kind,
null as entity_perm_id,
etpt_id,
value,
vocabulary_term,
material,
sample,
pers_id_author,
valid_from_timestamp,
Adam Laskowski
committed
valid_until_timestamp,
integer_array_value,
real_array_value,
string_array_value,
timestamp_array_value,
json_value
Adam Laskowski
committed
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
FROM
EXPERIMENT_PROPERTIES_HISTORY;
-- Creating sequences
CREATE SEQUENCE CONTROLLED_VOCABULARY_ID_SEQ;
CREATE SEQUENCE CVTE_ID_SEQ;
CREATE SEQUENCE DATABASE_INSTANCE_ID_SEQ;
CREATE SEQUENCE DATA_ID_SEQ;
CREATE SEQUENCE DATA_SET_RELATIONSHIP_ID_SEQ;
CREATE SEQUENCE DATA_STORE_ID_SEQ;
CREATE SEQUENCE DATA_STORE_SERVICES_ID_SEQ;
CREATE SEQUENCE DATA_TYPE_ID_SEQ;
CREATE SEQUENCE ETPT_ID_SEQ;
CREATE SEQUENCE EVENT_ID_SEQ;
CREATE SEQUENCE EVENTS_SEARCH_ID_SEQ;
CREATE SEQUENCE ATTACHMENT_ID_SEQ;
CREATE SEQUENCE ATTACHMENT_CONTENT_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_PROPERTY_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_TYPE_ID_SEQ;
CREATE SEQUENCE FILE_FORMAT_TYPE_ID_SEQ;
CREATE SEQUENCE SPACE_ID_SEQ;
CREATE SEQUENCE DELETION_ID_SEQ;
CREATE SEQUENCE LOCATOR_TYPE_ID_SEQ;
CREATE SEQUENCE MATERIAL_ID_SEQ;
CREATE SEQUENCE MATERIAL_PROPERTY_ID_SEQ;
CREATE SEQUENCE MATERIAL_TYPE_ID_SEQ;
CREATE SEQUENCE MTPT_ID_SEQ;
CREATE SEQUENCE DATA_SET_TYPE_ID_SEQ;
CREATE SEQUENCE PERSON_ID_SEQ;
CREATE SEQUENCE PROJECT_ID_SEQ;
CREATE SEQUENCE PROPERTY_TYPE_ID_SEQ;
CREATE SEQUENCE ROLE_ASSIGNMENT_ID_SEQ;
CREATE SEQUENCE SAMPLE_ID_SEQ;
CREATE SEQUENCE SAMPLE_PROPERTY_ID_SEQ;
CREATE SEQUENCE SAMPLE_TYPE_ID_SEQ;
CREATE SEQUENCE STPT_ID_SEQ;
CREATE SEQUENCE DATA_SET_PROPERTY_ID_SEQ;
CREATE SEQUENCE DSTPT_ID_SEQ;
CREATE SEQUENCE CODE_SEQ;
CREATE SEQUENCE EXPERIMENT_CODE_SEQ;
CREATE SEQUENCE SAMPLE_CODE_SEQ;
CREATE SEQUENCE PERM_ID_SEQ;
CREATE SEQUENCE AUTHORIZATION_GROUP_ID_SEQ;
CREATE SEQUENCE FILTER_ID_SEQ;
CREATE SEQUENCE GRID_CUSTOM_COLUMNS_ID_SEQ;
CREATE SEQUENCE QUERY_ID_SEQ;
CREATE SEQUENCE RELATIONSHIP_TYPE_ID_SEQ;
CREATE SEQUENCE SAMPLE_RELATIONSHIP_ID_SEQ;
CREATE SEQUENCE SCRIPT_ID_SEQ;
CREATE SEQUENCE CORE_PLUGIN_ID_SEQ;
CREATE SEQUENCE POST_REGISTRATION_DATASET_QUEUE_ID_SEQ;
CREATE SEQUENCE ENTITY_OPERATIONS_LOG_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_RELATIONSHIPS_HISTORY_ID_SEQ;
CREATE SEQUENCE SAMPLE_RELATIONSHIPS_HISTORY_ID_SEQ;
CREATE SEQUENCE DATA_SET_RELATIONSHIPS_HISTORY_ID_SEQ;
CREATE SEQUENCE PROJECT_RELATIONSHIPS_HISTORY_ID_SEQ;
CREATE SEQUENCE EXTERNAL_DATA_MANAGEMENT_SYSTEM_ID_SEQ;
CREATE SEQUENCE METAPROJECT_ID_SEQ;
CREATE SEQUENCE METAPROJECT_ASSIGNMENT_ID_SEQ;
CREATE SEQUENCE OPERATION_EXECUTIONS_ID_SEQ;
CREATE SEQUENCE CONTENT_COPIES_ID_SEQ;
CREATE SEQUENCE DATA_SET_COPIES_HISTORY_ID_SEQ;
CREATE SEQUENCE SEMANTIC_ANNOTATION_ID_SEQ;
-- Creating primary key constraints
ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_PK PRIMARY KEY(ID);
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_PK PRIMARY KEY(ID);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_PK PRIMARY KEY(ID);
ALTER TABLE DATA_STORES ADD CONSTRAINT DAST_PK PRIMARY KEY(ID);
ALTER TABLE DATA_STORE_SERVICES ADD CONSTRAINT DSSE_PK PRIMARY KEY(ID);
ALTER TABLE DATA_TYPES ADD CONSTRAINT DATY_PK PRIMARY KEY(ID);
ALTER TABLE EVENTS ADD CONSTRAINT EVNT_PK PRIMARY KEY(ID);
ALTER TABLE EVENTS_SEARCH ADD CONSTRAINT EVENTS_SEARCH_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_PK PRIMARY KEY(ID);
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_PK PRIMARY KEY(ID);
ALTER TABLE ATTACHMENT_CONTENTS ADD CONSTRAINT EXAC_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_PROPERTIES_HISTORY ADD CONSTRAINT EXPRH_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_PK PRIMARY KEY(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_PK PRIMARY KEY(ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_PK PRIMARY KEY(ID);
ALTER TABLE SPACES ADD CONSTRAINT SPACE_PK PRIMARY KEY(ID);
ALTER TABLE DELETIONS ADD CONSTRAINT DEL_PK PRIMARY KEY(ID);
ALTER TABLE LOCATOR_TYPES ADD CONSTRAINT LOTY_PK PRIMARY KEY(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_PROPERTIES_HISTORY ADD CONSTRAINT MAPRH_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_PK PRIMARY KEY(ID);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_PK PRIMARY KEY(ID);
ALTER TABLE DATA_SET_TYPES ADD CONSTRAINT DSTY_PK PRIMARY KEY(ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_PK PRIMARY KEY(ID);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PK PRIMARY KEY(ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_PK PRIMARY KEY(ID);
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_PROPERTIES_HISTORY ADD CONSTRAINT SAPRH_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_PK PRIMARY KEY(ID);
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_PK PRIMARY KEY(ID);
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_PK PRIMARY KEY(ID);
ALTER TABLE DATA_SET_PROPERTIES_HISTORY ADD CONSTRAINT DSPRH_PK PRIMARY KEY(ID);
ALTER TABLE AUTHORIZATION_GROUPS ADD CONSTRAINT AG_PK PRIMARY KEY(ID);
ALTER TABLE AUTHORIZATION_GROUP_PERSONS ADD CONSTRAINT AGP_PK PRIMARY KEY(PERS_ID,AG_ID);
ALTER TABLE FILTERS ADD CONSTRAINT FILT_PK PRIMARY KEY(ID);
ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_PK PRIMARY KEY(ID);
ALTER TABLE QUERIES ADD CONSTRAINT QUER_PK PRIMARY KEY(ID);
ALTER TABLE relationship_types ADD CONSTRAINT rety_pk PRIMARY KEY (id);
ALTER TABLE sample_relationships_all ADD CONSTRAINT sare_pk PRIMARY KEY (id);
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_PK PRIMARY KEY(ID);
ALTER TABLE POST_REGISTRATION_DATASET_QUEUE ADD CONSTRAINT PRDQ_PK PRIMARY KEY(ID);
ALTER TABLE ENTITY_OPERATIONS_LOG ADD CONSTRAINT EOL_PK PRIMARY KEY(ID);
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_PK PRIMARY KEY(ID);
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_PK PRIMARY KEY(ID);
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_PK PRIMARY KEY(ID);
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_PK PRIMARY KEY(ID);
ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS ADD CONSTRAINT EDMS_PK PRIMARY KEY(ID);
ALTER TABLE CONTENT_COPIES ADD CONSTRAINT COCO_PK PRIMARY KEY(ID);
ALTER TABLE LINK_DATA ADD CONSTRAINT lnda_pk PRIMARY KEY(id);
ALTER TABLE METAPROJECTS ADD CONSTRAINT METAPROJECTS_PK PRIMARY KEY(ID);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_PK PRIMARY KEY(ID);
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_PK PRIMARY KEY(ID);
ALTER TABLE DATA_SET_COPIES_HISTORY ADD CONSTRAINT DSCH_PK PRIMARY KEY(ID);
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_PK PRIMARY KEY(ID);
-- Creating unique constraints
ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_BK_UK UNIQUE(CODE,IS_MANAGED_INTERNALLY);
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_BK_UK UNIQUE(CODE,COVO_ID);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_IDFRZ_UK UNIQUE(ID, FROZEN);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_IDFRZ_CH_UK UNIQUE(ID, FROZEN_FOR_CHILDREN);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_IDFRZ_P_UK UNIQUE(ID, FROZEN_FOR_PARENTS);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_IDFRZ_COMP_UK UNIQUE(ID, FROZEN_FOR_COMPS);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_IDFRZ_CONT_UK UNIQUE(ID, FROZEN_FOR_CONTS);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_BK_UK UNIQUE(CODE);
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DSRE_BK_UK UNIQUE(DATA_ID_CHILD,DATA_ID_PARENT,RELATIONSHIP_ID);
ALTER TABLE DATA_STORE_SERVICES ADD CONSTRAINT DSSE_BK_UK UNIQUE(KEY, DATA_STORE_ID);
ALTER TABLE DATA_STORE_SERVICE_DATA_SET_TYPES ADD CONSTRAINT DSSDST_BK_UK UNIQUE(DATA_STORE_SERVICE_ID, DATA_SET_TYPE_ID);
ALTER TABLE DATA_STORES ADD CONSTRAINT DAST_BK_UK UNIQUE(CODE,UUID);
ALTER TABLE DATA_TYPES ADD CONSTRAINT DATY_BK_UK UNIQUE(CODE);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_IDFRZ_UK UNIQUE(ID, FROZEN);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_IDFRZ_S_UK UNIQUE(ID, FROZEN_FOR_SAMP);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_IDFRZ_D_UK UNIQUE(ID, FROZEN_FOR_DATA);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_BK_UK UNIQUE(CODE,PROJ_ID);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_PI_UK UNIQUE(PERM_ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_BK_UK UNIQUE(EXPE_ID,ETPT_ID);
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_BK_UK UNIQUE(EXTY_ID,PRTY_ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_BK_UK UNIQUE(LOCATION,LOTY_ID);
ALTER TABLE FILE_FORMAT_TYPES ADD CONSTRAINT FFTY_BK_UK UNIQUE(CODE);
ALTER TABLE LOCATOR_TYPES ADD CONSTRAINT LOTY_BK_UK UNIQUE(CODE);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_BK_UK UNIQUE(CODE,MATY_ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_BK_UK UNIQUE(MATE_ID,MTPT_ID);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_BK_UK UNIQUE(MATY_ID,PRTY_ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_BK_UK UNIQUE(USER_ID);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_IDFRZ_UK UNIQUE(ID, FROZEN);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_IDFRZ_E_UK UNIQUE(ID, FROZEN_FOR_EXP);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_IDFRZ_S_UK UNIQUE(ID, FROZEN_FOR_SAMP);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_BK_UK UNIQUE(CODE,SPACE_ID);
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PI_UK UNIQUE(PERM_ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_BK_UK UNIQUE(CODE,IS_MANAGED_INTERNALLY);
CREATE UNIQUE INDEX ROAS_PE_SPACE_PROJECT_BK_UK ON ROLE_ASSIGNMENTS (PERS_ID_GRANTEE, ROLE_CODE, coalesce(SPACE_ID,-1), coalesce(PROJECT_ID,-1));
CREATE UNIQUE INDEX ROAS_AG_SPACE_PROJECT_BK_UK ON ROLE_ASSIGNMENTS (AG_ID_GRANTEE, ROLE_CODE, coalesce(SPACE_ID,-1), coalesce(PROJECT_ID,-1));
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_IDFRZ_UK UNIQUE(ID, FROZEN);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_IDFRZ_C_UK UNIQUE(ID, FROZEN_FOR_COMP);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_IDFRZ_CH_UK UNIQUE(ID, FROZEN_FOR_CHILDREN);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_IDFRZ_P_UK UNIQUE(ID, FROZEN_FOR_PARENTS);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_IDFRZ_D_UK UNIQUE(ID, FROZEN_FOR_DATA);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_PI_UK UNIQUE(PERM_ID);
ALTER TABLE samples_all ADD CONSTRAINT samp_code_unique_check_uk UNIQUE(code_unique_check);
ALTER TABLE samples_all ADD CONSTRAINT samp_subcode_unique_check_uk UNIQUE(subcode_unique_check);
ALTER TABLE samples_all ADD CONSTRAINT samp_identifier_uk UNIQUE(sample_identifier);
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_BK_UK UNIQUE(SAMP_ID,STPT_ID);
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_BK_UK UNIQUE(SATY_ID,PRTY_ID);
ALTER TABLE SPACES ADD CONSTRAINT SPACE_IDFRZ_UK UNIQUE(ID, FROZEN);
ALTER TABLE SPACES ADD CONSTRAINT SPACE_IDFRZ_P_UK UNIQUE(ID, FROZEN_FOR_PROJ);
ALTER TABLE SPACES ADD CONSTRAINT SPACE_IDFRZ_S_UK UNIQUE(ID, FROZEN_FOR_SAMP);
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_BK_UK UNIQUE(DSTY_ID,PRTY_ID);
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_BK_UK UNIQUE(DS_ID,DSTPT_ID);
-- NOTE: following uniqueness constraints for attachments work, because (null != null) in Postgres
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_EXPE_BK_UK UNIQUE(EXPE_ID,FILE_NAME,VERSION);
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_PROJ_BK_UK UNIQUE(PROJ_ID,FILE_NAME,VERSION);
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_SAMP_BK_UK UNIQUE(SAMP_ID,FILE_NAME,VERSION);
ALTER TABLE AUTHORIZATION_GROUPS ADD CONSTRAINT AG_BK_UK UNIQUE(CODE);
ALTER TABLE FILTERS ADD CONSTRAINT FILT_BK_UK UNIQUE(NAME, GRID_ID);
ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_BK_UK UNIQUE(CODE, GRID_ID);
ALTER TABLE QUERIES ADD CONSTRAINT QUER_BK_UK UNIQUE(NAME);
ALTER TABLE sample_relationships_all ADD CONSTRAINT sare_bk_uk UNIQUE(sample_id_child,sample_id_parent,relationship_id);
ALTER TABLE relationship_types ADD CONSTRAINT rety_uk UNIQUE(code);
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_UK UNIQUE(NAME);
ALTER TABLE CORE_PLUGINS ADD CONSTRAINT COPL_NAME_VER_UK UNIQUE(NAME,VERSION);
ALTER TABLE ENTITY_OPERATIONS_LOG ADD CONSTRAINT EOL_REG_ID_UK UNIQUE(REGISTRATION_ID);
ALTER TABLE EXTERNAL_DATA_MANAGEMENT_SYSTEMS ADD CONSTRAINT EDMS_CODE_UK UNIQUE(CODE);
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT SAMPLE_TYPE_PROPERTY_TYPES_UNIQUE UNIQUE (ID, IS_UNIQUE);
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT EXPERIMENT_TYPE_PROPERTY_TYPES_UNIQUE UNIQUE (ID, IS_UNIQUE);
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DATA_SET_TYPE_PROPERTY_TYPES_UNIQUE UNIQUE (ID, IS_UNIQUE);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MATERIAL_TYPE_PROPERTY_TYPES_UNIQUE UNIQUE (ID, IS_UNIQUE);
-- NOTE: following uniqueness constraints for metaproject assignments work, because (null != null) in Postgres
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_EXPE_ID_UK UNIQUE (MEPR_ID, EXPE_ID);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_SAMP_ID_UK UNIQUE (MEPR_ID, SAMP_ID);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_DATA_ID_UK UNIQUE (MEPR_ID, DATA_ID);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_MATE_ID_UK UNIQUE (MEPR_ID, MATE_ID);
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_CODE_UK UNIQUE (CODE);
ALTER TABLE CONTENT_COPIES ADD CONSTRAINT content_copies_unique_check_uk UNIQUE(location_unique_check);
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_PERM_ID_UK UNIQUE (PERM_ID);
-- Creating foreign key constraints
ALTER TABLE CONTROLLED_VOCABULARIES ADD CONSTRAINT COVO_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_COVO_FK FOREIGN KEY (COVO_ID) REFERENCES CONTROLLED_VOCABULARIES(ID);
ALTER TABLE CONTROLLED_VOCABULARY_TERMS ADD CONSTRAINT CVTE_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_DSTY_FK FOREIGN KEY (DSTY_ID) REFERENCES DATA_SET_TYPES(ID);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_EXPE_FK FOREIGN KEY (EXPE_ID, EXPE_FROZEN) REFERENCES EXPERIMENTS_ALL(ID, FROZEN_FOR_DATA) ON UPDATE CASCADE;
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_SAMP_FK FOREIGN KEY (SAMP_ID, SAMP_FROZEN) REFERENCES SAMPLES_ALL(ID, FROZEN_FOR_DATA) ON UPDATE CASCADE;
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_DAST_FK FOREIGN KEY (DAST_ID) REFERENCES DATA_STORES(ID);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_DEL_FK FOREIGN KEY (DEL_ID) REFERENCES DELETIONS(ID);
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_PERS_FK_MOD FOREIGN KEY (PERS_ID_MODIFIER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DSRE_DATA_FK_CHILD FOREIGN KEY (DATA_ID_CHILD, CHILD_FROZEN) REFERENCES DATA_ALL(ID, FROZEN_FOR_PARENTS) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DSRE_DATA_FK_PARENT FOREIGN KEY (DATA_ID_PARENT, PARENT_FROZEN) REFERENCES DATA_ALL(ID, FROZEN_FOR_CHILDREN) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DSRE_DATA_FK_COMP FOREIGN KEY (DATA_ID_CHILD, COMP_FROZEN) REFERENCES DATA_ALL(ID, FROZEN_FOR_CONTS) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DSRE_DATA_FK_CONT FOREIGN KEY (DATA_ID_PARENT, CONT_FROZEN) REFERENCES DATA_ALL(ID, FROZEN_FOR_COMPS) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DSRE_DATA_FK_RELATIONSHIP FOREIGN KEY (RELATIONSHIP_ID) REFERENCES RELATIONSHIP_TYPES(ID);
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DSRE_DEL_FK FOREIGN KEY (DEL_ID) REFERENCES DELETIONS(ID);
ALTER TABLE DATA_SET_RELATIONSHIPS_ALL ADD CONSTRAINT DATA_SET_RELATIONSHIPS_PERS_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE sample_relationships_all ADD CONSTRAINT sare_data_fk_child FOREIGN KEY (sample_id_child, CHILD_FROZEN) REFERENCES SAMPLES_ALL(id, FROZEN_FOR_PARENTS) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE sample_relationships_all ADD CONSTRAINT sare_data_fk_parent FOREIGN KEY (sample_id_parent, PARENT_FROZEN) REFERENCES SAMPLES_ALL(id, FROZEN_FOR_CHILDREN) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE sample_relationships_all ADD CONSTRAINT sare_data_fk_relationship FOREIGN KEY (relationship_id) REFERENCES relationship_types(id);
ALTER TABLE sample_relationships_all ADD CONSTRAINT sare_del_fk FOREIGN KEY (del_id) REFERENCES deletions(id);
ALTER TABLE SAMPLE_RELATIONSHIPS_ALL ADD CONSTRAINT SAMPLE_RELATIONSHIPS_PERS_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_STORE_SERVICES ADD CONSTRAINT DSSE_DS_FK FOREIGN KEY (DATA_STORE_ID) REFERENCES DATA_STORES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_STORE_SERVICE_DATA_SET_TYPES ADD CONSTRAINT DSSDST_DS_FK FOREIGN KEY (DATA_STORE_SERVICE_ID) REFERENCES DATA_STORE_SERVICES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_STORE_SERVICE_DATA_SET_TYPES ADD CONSTRAINT DSSDST_DST_FK FOREIGN KEY (DATA_SET_TYPE_ID) REFERENCES DATA_SET_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE EVENTS ADD CONSTRAINT EVNT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE EVENTS ADD CONSTRAINT EVNT_EXAC_FK FOREIGN KEY (EXAC_ID) REFERENCES ATTACHMENT_CONTENTS(ID);
ALTER TABLE EVENTS_SEARCH ADD CONSTRAINT EVENTS_SEARCH_PERS_ID_REGISTERER_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE EVENTS_SEARCH ADD CONSTRAINT EVENTS_SEARCH_EXAC_ID_FK FOREIGN KEY (EXAC_ID) REFERENCES ATTACHMENT_CONTENTS(ID);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_EXTY_FK FOREIGN KEY (EXTY_ID) REFERENCES EXPERIMENT_TYPES(ID);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_DEL_FK FOREIGN KEY (DEL_ID) REFERENCES DELETIONS(ID);
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_PROJ_FK FOREIGN KEY (PROJ_ID, PROJ_FROZEN) REFERENCES PROJECTS(ID, FROZEN_FOR_EXP) ON UPDATE CASCADE;
ALTER TABLE EXPERIMENTS_ALL ADD CONSTRAINT EXPE_PERS_FK_MOD FOREIGN KEY (PERS_ID_MODIFIER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_EXPE_FK FOREIGN KEY (EXPE_ID, EXPE_FROZEN) REFERENCES EXPERIMENTS_ALL(ID, FROZEN) ON UPDATE CASCADE;
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_PROJ_FK FOREIGN KEY (PROJ_ID, PROJ_FROZEN) REFERENCES PROJECTS(ID, FROZEN) ON UPDATE CASCADE;
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_SAMP_FK FOREIGN KEY (SAMP_ID, SAMP_FROZEN) REFERENCES SAMPLES_ALL(ID, FROZEN) ON UPDATE CASCADE;
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_CONT_FK FOREIGN KEY (EXAC_ID) REFERENCES ATTACHMENT_CONTENTS(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_CVTE_FK FOREIGN KEY (CVTE_ID) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_ETPT_FK FOREIGN KEY (ETPT_ID) REFERENCES EXPERIMENT_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_EXPE_FK FOREIGN KEY (EXPE_ID, EXPE_FROZEN) REFERENCES EXPERIMENTS_ALL(ID, FROZEN) ON UPDATE CASCADE;
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_MAPR_FK FOREIGN KEY (MATE_PROP_ID) REFERENCES MATERIALS(ID);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE EXPERIMENT_PROPERTIES_HISTORY ADD CONSTRAINT EXPRH_ETPT_FK FOREIGN KEY (ETPT_ID) REFERENCES EXPERIMENT_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_PROPERTIES_HISTORY ADD CONSTRAINT EXPRH_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_PROPERTIES_HISTORY ADD CONSTRAINT EXPRH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE EXPERIMENT_TYPES ADD CONSTRAINT EXTY_SCRIPT_FK FOREIGN KEY (VALIDATION_SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_EXTY_FK FOREIGN KEY (EXTY_ID) REFERENCES EXPERIMENT_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_PRTY_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_CVTE_FK FOREIGN KEY (CVTE_ID_STOR_FMT) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_CVTE_STORED_ON_FK FOREIGN KEY (CVTE_ID_STORE) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_DATA_FK FOREIGN KEY (ID) REFERENCES DATA_ALL(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_FFTY_FK FOREIGN KEY (FFTY_ID) REFERENCES FILE_FORMAT_TYPES(ID);
ALTER TABLE EXTERNAL_DATA ADD CONSTRAINT EXDA_LOTY_FK FOREIGN KEY (LOTY_ID) REFERENCES LOCATOR_TYPES(ID);
ALTER TABLE SPACES ADD CONSTRAINT SPACE_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DELETIONS ADD CONSTRAINT DEL_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_MATY_FK FOREIGN KEY (MATY_ID) REFERENCES MATERIAL_TYPES(ID);
ALTER TABLE MATERIALS ADD CONSTRAINT MATE_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_CVTE_FK FOREIGN KEY (CVTE_ID) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_MAPR_FK FOREIGN KEY (MATE_PROP_ID) REFERENCES MATERIALS(ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_MATE_FK FOREIGN KEY (MATE_ID) REFERENCES MATERIALS(ID);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_MTPT_FK FOREIGN KEY (MTPT_ID) REFERENCES MATERIAL_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE MATERIAL_PROPERTIES_HISTORY ADD CONSTRAINT MAPRH_MATE_FK FOREIGN KEY (MATE_ID) REFERENCES MATERIALS(ID) ON DELETE CASCADE;
ALTER TABLE MATERIAL_PROPERTIES_HISTORY ADD CONSTRAINT MAPRH_MTPT_FK FOREIGN KEY (MTPT_ID) REFERENCES MATERIAL_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE MATERIAL_PROPERTIES_HISTORY ADD CONSTRAINT MAPRH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE MATERIAL_TYPES ADD CONSTRAINT MATY_SCRIPT_FK FOREIGN KEY (VALIDATION_SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_MATY_FK FOREIGN KEY (MATY_ID) REFERENCES MATERIAL_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_PRTY_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_TYPES ADD CONSTRAINT DSTY_SCRIPT_FK FOREIGN KEY (VALIDATION_SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE PERSONS ADD CONSTRAINT PERS_SPACE_FK FOREIGN KEY (SPACE_ID) REFERENCES SPACES(ID) ON DELETE SET NULL;
ALTER TABLE PERSONS ADD CONSTRAINT PERS_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_SPACE_FK FOREIGN KEY (SPACE_ID, SPACE_FROZEN) REFERENCES SPACES(ID, FROZEN_FOR_PROJ) ON UPDATE CASCADE;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PERS_FK_LEADER FOREIGN KEY (PERS_ID_LEADER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE PROJECTS ADD CONSTRAINT PROJ_PERS_FK_MOD FOREIGN KEY (PERS_ID_MODIFIER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_COVO_FK FOREIGN KEY (COVO_ID) REFERENCES CONTROLLED_VOCABULARIES(ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_DATY_FK FOREIGN KEY (DATY_ID) REFERENCES DATA_TYPES(ID);
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_MATY_FK FOREIGN KEY (MATY_PROP_ID) REFERENCES MATERIAL_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE PROPERTY_TYPES ADD CONSTRAINT PRTY_SATY_FK FOREIGN KEY (SATY_PROP_ID) REFERENCES SAMPLE_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_SPACE_FK FOREIGN KEY (SPACE_ID) REFERENCES SPACES(ID) ON DELETE CASCADE;
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PROJECT_FK FOREIGN KEY (PROJECT_ID) REFERENCES PROJECTS(ID) ON DELETE CASCADE;
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PERS_FK_GRANTEE FOREIGN KEY (PERS_ID_GRANTEE) REFERENCES PERSONS(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_AG_FK_GRANTEE FOREIGN KEY (AG_ID_GRANTEE) REFERENCES AUTHORIZATION_GROUPS(ID) ON DELETE CASCADE;
ALTER TABLE ROLE_ASSIGNMENTS ADD CONSTRAINT ROAS_PERS_FK_REGISTERER FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_SPACE_FK FOREIGN KEY (SPACE_ID, SPACE_FROZEN) REFERENCES SPACES(ID, FROZEN_FOR_SAMP) ON UPDATE CASCADE;
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_PROJ_FK FOREIGN KEY (PROJ_ID, PROJ_FROZEN) REFERENCES PROJECTS(ID, FROZEN_FOR_SAMP) ON UPDATE CASCADE;
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_DEL_FK FOREIGN KEY (DEL_ID) REFERENCES DELETIONS(ID);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_SAMP_FK_PART_OF FOREIGN KEY (SAMP_ID_PART_OF, CONT_FROZEN) REFERENCES SAMPLES_ALL(ID, FROZEN_FOR_COMP) ON UPDATE CASCADE;
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_EXPE_FK FOREIGN KEY (EXPE_ID, EXPE_FROZEN) REFERENCES EXPERIMENTS_ALL(ID, FROZEN_FOR_SAMP) ON UPDATE CASCADE;
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_SATY_FK FOREIGN KEY (SATY_ID) REFERENCES SAMPLE_TYPES(ID);
ALTER TABLE SAMPLES_ALL ADD CONSTRAINT SAMP_PERS_FK_MOD FOREIGN KEY (PERS_ID_MODIFIER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_CVTE_FK FOREIGN KEY (CVTE_ID) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_MAPR_FK FOREIGN KEY (MATE_PROP_ID) REFERENCES MATERIALS(ID);
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_SAMP_FK FOREIGN KEY (SAMP_ID, SAMP_FROZEN) REFERENCES SAMPLES_ALL(ID, FROZEN) ON UPDATE CASCADE;
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_STPT_FK FOREIGN KEY (STPT_ID) REFERENCES SAMPLE_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLE_PROPERTIES_HISTORY ADD CONSTRAINT SAPRH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE CASCADE;
ALTER TABLE SAMPLE_PROPERTIES_HISTORY ADD CONSTRAINT SAPRH_STPT_FK FOREIGN KEY (STPT_ID) REFERENCES SAMPLE_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE SAMPLE_PROPERTIES_HISTORY ADD CONSTRAINT SAPRH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLE_TYPES ADD CONSTRAINT SATY_SCRIPT_FK FOREIGN KEY (VALIDATION_SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_PRTY_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_SATY_FK FOREIGN KEY (SATY_ID) REFERENCES SAMPLE_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_DSTY_FK FOREIGN KEY (DSTY_ID) REFERENCES DATA_SET_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_PRTY_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_CVTE_FK FOREIGN KEY (CVTE_ID) REFERENCES CONTROLLED_VOCABULARY_TERMS(ID);
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_DSTPT_FK FOREIGN KEY (DSTPT_ID) REFERENCES DATA_SET_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_DS_FK FOREIGN KEY (DS_ID, DASE_FROZEN) REFERENCES DATA_ALL(ID, FROZEN) ON UPDATE CASCADE;
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_MAPR_FK FOREIGN KEY (MATE_PROP_ID) REFERENCES MATERIALS(ID);
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_SET_PROPERTIES_HISTORY ADD CONSTRAINT DSPRH_DSTPT_FK FOREIGN KEY (DSTPT_ID) REFERENCES DATA_SET_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_PROPERTIES_HISTORY ADD CONSTRAINT DSPRH_DS_FK FOREIGN KEY (DS_ID) REFERENCES DATA_ALL(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_PROPERTIES_HISTORY ADD CONSTRAINT DSPRH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE AUTHORIZATION_GROUP_PERSONS ADD CONSTRAINT AGP_AG_FK FOREIGN KEY (AG_ID) REFERENCES AUTHORIZATION_GROUPS(ID);
ALTER TABLE AUTHORIZATION_GROUP_PERSONS ADD CONSTRAINT AGP_PERS_FK FOREIGN KEY (PERS_ID) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE AUTHORIZATION_GROUPS ADD CONSTRAINT AG_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE FILTERS ADD CONSTRAINT FILT_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE GRID_CUSTOM_COLUMNS ADD CONSTRAINT GRID_CUSTOM_COLUMNS_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE QUERIES ADD CONSTRAINT QUER_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRI_PERS_FK FOREIGN KEY (PERS_ID_REGISTERER) REFERENCES PERSONS(ID) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE MATERIAL_TYPE_PROPERTY_TYPES ADD CONSTRAINT MTPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE SAMPLE_TYPE_PROPERTY_TYPES ADD CONSTRAINT STPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE EXPERIMENT_TYPE_PROPERTY_TYPES ADD CONSTRAINT ETPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE DATA_SET_TYPE_PROPERTY_TYPES ADD CONSTRAINT DSTPT_SCRIPT_FK FOREIGN KEY (SCRIPT_ID) REFERENCES SCRIPTS(ID);
ALTER TABLE ONLY POST_REGISTRATION_DATASET_QUEUE ADD CONSTRAINT prdq_ds_fk FOREIGN KEY (ds_id) REFERENCES data_all(id) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_MAIN_EXPE_FK FOREIGN KEY (MAIN_EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE SET NULL;
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE SET NULL;
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_PROJ_FK FOREIGN KEY (PROJ_ID) REFERENCES PROJECTS(ID) ON DELETE SET NULL;
ALTER TABLE EXPERIMENT_RELATIONSHIPS_HISTORY ADD CONSTRAINT EXRELH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_MAIN_SAMP_FK FOREIGN KEY (MAIN_SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE CASCADE;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE SET NULL;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE SET NULL;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE SET NULL;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_SPACE_FK FOREIGN KEY (SPACE_ID) REFERENCES SPACES(ID) ON DELETE SET NULL;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_PROJECT_FK FOREIGN KEY (PROJ_ID) REFERENCES PROJECTS(ID) ON DELETE SET NULL;
ALTER TABLE SAMPLE_RELATIONSHIPS_HISTORY ADD CONSTRAINT SAMPRELH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_MAIN_DATA_FK FOREIGN KEY (MAIN_DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE CASCADE;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE SET NULL;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE SET NULL;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_DATA_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE SET NULL;
ALTER TABLE DATA_SET_RELATIONSHIPS_HISTORY ADD CONSTRAINT DATARELH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_MAIN_PROJ_FK FOREIGN KEY (MAIN_PROJ_ID) REFERENCES PROJECTS(ID) ON DELETE CASCADE;
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_EXPE_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE SET NULL;
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_SAMP_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE SET NULL;
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_SPACE_FK FOREIGN KEY (SPACE_ID) REFERENCES SPACES(ID) ON DELETE SET NULL;
ALTER TABLE PROJECT_RELATIONSHIPS_HISTORY ADD CONSTRAINT PRRELH_AUTH_FK FOREIGN KEY (PERS_ID_AUTHOR) REFERENCES PERSONS(ID) ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE LINK_DATA ADD CONSTRAINT lnda_data_fk FOREIGN KEY (ID, DATA_FROZEN) REFERENCES data_all(ID, FROZEN) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE LINK_DATA ADD CONSTRAINT LINK_DATA_IDFRZ_UK UNIQUE(ID, DATA_FROZEN);
ALTER TABLE CONTENT_COPIES ADD CONSTRAINT COCO_DATA_FK FOREIGN KEY (DATA_ID, DATA_FROZEN) REFERENCES LINK_DATA(ID, DATA_FROZEN) ON UPDATE CASCADE;
ALTER TABLE CONTENT_COPIES ADD CONSTRAINT COCO_EDMS_FK FOREIGN KEY (EDMS_ID) REFERENCES EXTERNAL_DATA_MANAGEMENT_SYSTEMS(ID);
ALTER TABLE METAPROJECTS ADD CONSTRAINT METAPROJECTS_OWNER_FK FOREIGN KEY (OWNER) REFERENCES PERSONS(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MEPR_ID_FK FOREIGN KEY (MEPR_ID) REFERENCES METAPROJECTS(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_EXPE_ID_FK FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS_ALL(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_SAMP_ID_FK FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES_ALL(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_DATA_ID_FK FOREIGN KEY (DATA_ID) REFERENCES DATA_ALL(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_MATE_ID_FK FOREIGN KEY (MATE_ID) REFERENCES MATERIALS(ID) ON DELETE CASCADE;
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_DEL_ID_FK FOREIGN KEY (DEL_ID) REFERENCES DELETIONS(ID);
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_OWNER_FK FOREIGN KEY (OWNER) REFERENCES PERSONS(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_SATY_ID_FK FOREIGN KEY (SATY_ID) REFERENCES SAMPLE_TYPES(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_STPT_ID_FK FOREIGN KEY (STPT_ID) REFERENCES SAMPLE_TYPE_PROPERTY_TYPES(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_PRTY_ID_FK FOREIGN KEY (PRTY_ID) REFERENCES PROPERTY_TYPES(ID) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAMPLE_PROPERTIES_UNIQUE_FK FOREIGN KEY (STPT_ID, IS_UNIQUE) REFERENCES SAMPLE_TYPE_PROPERTY_TYPES(ID, IS_UNIQUE) ON DELETE CASCADE;
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPERIMENT_PROPERTIES_UNIQUE_FK FOREIGN KEY (ETPT_ID, IS_UNIQUE) REFERENCES EXPERIMENT_TYPE_PROPERTY_TYPES(ID, IS_UNIQUE) ON DELETE CASCADE;
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DATA_SET_PROPERTIES_UNIQUE_FK FOREIGN KEY (DSTPT_ID, IS_UNIQUE) REFERENCES DATA_SET_TYPE_PROPERTY_TYPES(ID, IS_UNIQUE) ON DELETE CASCADE;
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MATERIAL_PROPERTIES_UNIQUE_FK FOREIGN KEY (MTPT_ID, IS_UNIQUE) REFERENCES MATERIAL_TYPE_PROPERTY_TYPES(ID, IS_UNIQUE) ON DELETE CASCADE;
-- Creating check constraints
ALTER TABLE DATA_ALL ADD CONSTRAINT DATA_CK CHECK (EXPE_ID IS NOT NULL OR SAMP_ID IS NOT NULL);
ALTER TABLE EXPERIMENT_PROPERTIES ADD CONSTRAINT EXPR_CK CHECK
((VALUE IS NOT NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NOT NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NOT NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NOT NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NOT NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NOT NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NOT NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NOT NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NOT NULL)
);
ALTER TABLE EXPERIMENT_PROPERTIES_HISTORY ADD CONSTRAINT EXPRH_CK CHECK
Adam Laskowski
committed
((VALUE IS NOT NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NOT NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NOT NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NOT NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NOT NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NOT NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NOT NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NOT NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NOT NULL)
Adam Laskowski
committed
);
ALTER TABLE SAMPLE_PROPERTIES ADD CONSTRAINT SAPR_CK CHECK
((VALUE IS NOT NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
Adam Laskowski
committed
(VALUE IS NULL AND CVTE_ID IS NOT NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NOT NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NOT NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NOT NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NOT NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NOT NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NOT NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NOT NULL)
);
Adam Laskowski
committed
ALTER TABLE SAMPLE_PROPERTIES_HISTORY ADD CONSTRAINT SAPRH_CK CHECK
Adam Laskowski
committed
((VALUE IS NOT NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NOT NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NOT NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NOT NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NOT NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NOT NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NOT NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NOT NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NOT NULL)
Adam Laskowski
committed
);
ALTER TABLE MATERIAL_PROPERTIES ADD CONSTRAINT MAPR_CK CHECK
((VALUE IS NOT NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NOT NULL AND MATE_PROP_ID IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NOT NULL)
);
ALTER TABLE MATERIAL_PROPERTIES_HISTORY ADD CONSTRAINT MAPRH_CK CHECK
((VALUE IS NOT NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NOT NULL AND MATERIAL IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NOT NULL)
);
ALTER TABLE DATA_SET_PROPERTIES ADD CONSTRAINT DSPR_CK CHECK
Adam Laskowski
committed
((VALUE IS NOT NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NOT NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NOT NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NOT NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NOT NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NOT NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NOT NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NOT NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND CVTE_ID IS NULL AND MATE_PROP_ID IS NULL AND SAMP_PROP_ID IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NOT NULL)
Adam Laskowski
committed
);
ALTER TABLE DATA_SET_PROPERTIES_HISTORY ADD CONSTRAINT DSPRH_CK CHECK
Adam Laskowski
committed
((VALUE IS NOT NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NOT NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NOT NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NOT NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NOT NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NOT NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NOT NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NOT NULL AND JSON_VALUE IS NULL) OR
(VALUE IS NULL AND VOCABULARY_TERM IS NULL AND MATERIAL IS NULL AND SAMPLE IS NULL AND INTEGER_ARRAY_VALUE IS NULL AND REAL_ARRAY_VALUE IS NULL AND TIMESTAMP_ARRAY_VALUE IS NULL AND STRING_ARRAY_VALUE IS NULL AND JSON_VALUE IS NOT NULL)
Adam Laskowski
committed
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
);
ALTER TABLE ATTACHMENTS ADD CONSTRAINT ATTA_ARC_CK CHECK
((EXPE_ID IS NOT NULL AND PROJ_ID IS NULL AND SAMP_ID IS NULL) OR
(EXPE_ID IS NULL AND PROJ_ID IS NOT NULL AND SAMP_ID IS NULL) OR
(EXPE_ID IS NULL AND PROJ_ID IS NULL AND SAMP_ID IS NOT NULL)
);
ALTER TABLE events ADD CONSTRAINT evnt_et_enum_ck CHECK
(entity_type IN ('ATTACHMENT', 'DATASET', 'EXPERIMENT', 'SPACE', 'MATERIAL', 'PROJECT', 'PROPERTY_TYPE', 'SAMPLE', 'VOCABULARY', 'AUTHORIZATION_GROUP', 'METAPROJECT'));
ALTER TABLE EVENTS_SEARCH ADD CONSTRAINT EVENTS_SEARCH_ENTITY_TYPE_CK CHECK
(ENTITY_TYPE IN ('ATTACHMENT', 'DATASET', 'EXPERIMENT', 'SPACE', 'MATERIAL', 'PROJECT', 'PROPERTY_TYPE', 'SAMPLE', 'VOCABULARY', 'AUTHORIZATION_GROUP', 'METAPROJECT'));
ALTER TABLE controlled_vocabulary_terms ADD CONSTRAINT cvte_ck CHECK (ordinal > 0);
ALTER TABLE METAPROJECT_ASSIGNMENTS_ALL ADD CONSTRAINT METAPROJECT_ASSIGNMENTS_ALL_CHECK_NN CHECK (
(EXPE_ID IS NOT NULL AND SAMP_ID IS NULL AND DATA_ID IS NULL AND MATE_ID IS NULL) OR
(EXPE_ID IS NULL AND SAMP_ID IS NOT NULL AND DATA_ID IS NULL AND MATE_ID IS NULL) OR
(EXPE_ID IS NULL AND SAMP_ID IS NULL AND DATA_ID IS NOT NULL AND MATE_ID IS NULL) OR
(EXPE_ID IS NULL AND SAMP_ID IS NULL AND DATA_ID IS NULL AND MATE_ID IS NOT NULL));
ALTER TABLE SCRIPTS ADD CONSTRAINT SCRIPT_NN_CK CHECK
(PLUGIN_TYPE = 'PREDEPLOYED' OR SCRIPT IS NOT NULL);
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_STATE_START_DATE_CHECK CHECK (
(STATE IN ('NEW','SCHEDULED') AND START_DATE IS NULL) OR
(STATE IN ('RUNNING','FINISHED','FAILED') AND START_DATE IS NOT NULL)
);
ALTER TABLE OPERATION_EXECUTIONS ADD CONSTRAINT OPERATION_EXECUTIONS_STATE_FINISH_DATE_CHECK CHECK (
(STATE IN ('NEW','SCHEDULED','RUNNING') AND FINISH_DATE IS NULL) OR
(STATE IN ('FINISHED','FAILED') AND FINISH_DATE IS NOT NULL)
);
ALTER TABLE SEMANTIC_ANNOTATIONS ADD CONSTRAINT SEMANTIC_ANNOTATIONS_SSP_CK CHECK
((SATY_ID IS NOT NULL AND STPT_ID IS NULL AND PRTY_ID IS NULL) OR
(SATY_ID IS NULL AND STPT_ID IS NOT NULL AND PRTY_ID IS NULL) OR
(SATY_ID IS NULL AND STPT_ID IS NULL AND PRTY_ID IS NOT NULL)
);
-- Creating indices
CREATE INDEX COVO_PERS_FK_I ON CONTROLLED_VOCABULARIES (PERS_ID_REGISTERER);
CREATE INDEX CVTE_COVO_FK_I ON CONTROLLED_VOCABULARY_TERMS (COVO_ID);
CREATE INDEX CVTE_PERS_FK_I ON CONTROLLED_VOCABULARY_TERMS (PERS_ID_REGISTERER);
CREATE INDEX DATA_IDFRZ_PK_I ON DATA_ALL (id, frozen);
CREATE INDEX DATA_IDFRZ_CH_PK_I ON DATA_ALL (id, frozen_for_children);
CREATE INDEX DATA_IDFRZ_P_PK_I ON DATA_ALL (id, frozen_for_parents);
CREATE INDEX DATA_IDFRZ_COMP_PK_I ON DATA_ALL (id, frozen_for_comps);
CREATE INDEX DATA_IDFRZ_CONT_PK_I ON DATA_ALL (id, frozen_for_conts);
CREATE INDEX DATA_DSTY_FK_I ON DATA_ALL (DSTY_ID);
CREATE INDEX DATA_SAMP_FK_I ON DATA_ALL (SAMP_ID);
CREATE INDEX DATA_EXPE_FK_I ON DATA_ALL (EXPE_ID);
CREATE INDEX DATA_DEL_FK_I ON DATA_ALL (DEL_ID);
CREATE INDEX DATA_ACCT_I ON DATA_ALL (ACCESS_TIMESTAMP);
CREATE INDEX LINK_DATA_IDFRZ_PK_I ON LINK_DATA (ID, DATA_FROZEN);
CREATE INDEX DSRE_DATA_FK_I_CHILD ON DATA_SET_RELATIONSHIPS_ALL (DATA_ID_CHILD);
CREATE INDEX DSRE_DATA_FK_I_PARENT ON DATA_SET_RELATIONSHIPS_ALL (DATA_ID_PARENT);
CREATE INDEX DSRE_DEL_FK_I ON DATA_SET_RELATIONSHIPS_ALL (DEL_ID);
CREATE INDEX sare_data_fk_i_child ON sample_relationships_all (sample_id_child);
CREATE INDEX sare_data_fk_i_parent ON sample_relationships_all (sample_id_parent);
CREATE INDEX sare_data_fk_i_relationship ON sample_relationships_all (relationship_id);
CREATE INDEX sare_del_fk_i ON sample_relationships_all (del_id);
CREATE INDEX DSSE_DS_FK_I ON DATA_STORE_SERVICES (DATA_STORE_ID);
CREATE INDEX DSSDST_DS_FK_I ON DATA_STORE_SERVICE_DATA_SET_TYPES (DATA_STORE_SERVICE_ID);
CREATE INDEX DSSDST_DST_FK_I ON DATA_STORE_SERVICE_DATA_SET_TYPES (DATA_SET_TYPE_ID);
CREATE INDEX ETPT_EXTY_FK_I ON EXPERIMENT_TYPE_PROPERTY_TYPES (EXTY_ID);
CREATE INDEX ETPT_PERS_FK_I ON EXPERIMENT_TYPE_PROPERTY_TYPES (PERS_ID_REGISTERER);
CREATE INDEX ETPT_PRTY_FK_I ON EXPERIMENT_TYPE_PROPERTY_TYPES (PRTY_ID);
CREATE INDEX EVNT_PERS_FK_I ON EVENTS (PERS_ID_REGISTERER);
CREATE INDEX EVNT_FR_ID_FK_I ON EVENTS (EVENT_TYPE, IDENTIFIERS) WHERE EVENT_TYPE = 'FREEZING';
CREATE INDEX EVNT_EXAC_FK_I ON EVENTS (EXAC_ID);
CREATE INDEX EVENTS_SEARCH_ENTITY_SPACE_I ON EVENTS_SEARCH (ENTITY_SPACE);
CREATE INDEX EVENTS_SEARCH_ENTITY_SPACE_PERM_ID_I ON EVENTS_SEARCH (ENTITY_SPACE_PERM_ID);
CREATE INDEX EVENTS_SEARCH_ENTITY_PROJECT_I ON EVENTS_SEARCH (ENTITY_PROJECT);
CREATE INDEX EVENTS_SEARCH_ENTITY_PROJECT_PERM_ID_I ON EVENTS_SEARCH (ENTITY_PROJECT_PERM_ID);
CREATE INDEX EVENTS_SEARCH_ENTITY_REGISTERER_I ON EVENTS_SEARCH (ENTITY_REGISTERER);
CREATE INDEX EVENTS_SEARCH_ENTITY_REGISTRATION_TIMESTAMP_I ON EVENTS_SEARCH (ENTITY_REGISTRATION_TIMESTAMP);
CREATE INDEX EVENTS_SEARCH_PERS_ID_REGISTERER_I ON EVENTS_SEARCH (PERS_ID_REGISTERER);
CREATE INDEX EVENTS_SEARCH_REGISTRATION_TIMESTAMP_I ON EVENTS_SEARCH (REGISTRATION_TIMESTAMP);
CREATE INDEX EVENTS_SEARCH_EXAC_ID_I ON EVENTS_SEARCH (EXAC_ID);
CREATE INDEX ATTA_EXPE_FK_I ON ATTACHMENTS (EXPE_ID);
CREATE INDEX ATTA_SAMP_FK_I ON ATTACHMENTS (SAMP_ID);
CREATE INDEX ATTA_PROJ_FK_I ON ATTACHMENTS (PROJ_ID);
CREATE INDEX ATTA_PERS_FK_I ON ATTACHMENTS (PERS_ID_REGISTERER);
CREATE INDEX ATTA_EXAC_FK_I ON ATTACHMENTS (EXAC_ID);
CREATE INDEX EXDA_CVTE_FK_I ON EXTERNAL_DATA (CVTE_ID_STOR_FMT);
CREATE INDEX EXDA_CVTE_STORED_ON_FK_I ON EXTERNAL_DATA (CVTE_ID_STORE);
CREATE INDEX EXDA_FFTY_FK_I ON EXTERNAL_DATA (FFTY_ID);
CREATE INDEX EXDA_LOTY_FK_I ON EXTERNAL_DATA (LOTY_ID);
CREATE INDEX EXPE_IDFRZ_PK_I ON EXPERIMENTS_ALL (id, frozen);
CREATE INDEX EXPE_IDFRZ_S_PK_I ON EXPERIMENTS_ALL (id, frozen_for_samp);
CREATE INDEX EXPE_IDFRZ_D_PK_I ON EXPERIMENTS_ALL (id, frozen_for_data);
CREATE INDEX EXPE_EXTY_FK_I ON EXPERIMENTS_ALL (EXTY_ID);
CREATE INDEX EXPE_DEL_FK_I ON EXPERIMENTS_ALL (DEL_ID);
CREATE INDEX EXPE_PERS_FK_I ON EXPERIMENTS_ALL (PERS_ID_REGISTERER);
CREATE INDEX EXPE_PROJ_FK_I ON EXPERIMENTS_ALL (PROJ_ID);
CREATE INDEX EXPR_CVTE_FK_I ON EXPERIMENT_PROPERTIES (CVTE_ID);
CREATE INDEX EXPR_ETPT_FK_I ON EXPERIMENT_PROPERTIES (ETPT_ID);
CREATE INDEX EXPR_EXPE_FK_I ON EXPERIMENT_PROPERTIES (EXPE_ID);
CREATE INDEX EXPR_PERS_FK_I ON EXPERIMENT_PROPERTIES (PERS_ID_REGISTERER);
CREATE INDEX EXPR_MAPR_FK_I ON EXPERIMENT_PROPERTIES (MATE_PROP_ID);
CREATE INDEX EXPR_SAPR_FK_I ON EXPERIMENT_PROPERTIES (SAMP_PROP_ID);
CREATE INDEX EXPRH_ETPT_FK_I ON EXPERIMENT_PROPERTIES_HISTORY (ETPT_ID);
CREATE INDEX EXPRH_EXPE_FK_I ON EXPERIMENT_PROPERTIES_HISTORY (EXPE_ID);
CREATE INDEX EXPRH_VUTS_FK_I ON EXPERIMENT_PROPERTIES_HISTORY (VALID_UNTIL_TIMESTAMP);
CREATE INDEX SPACE_PERS_REGISTERED_BY_FK_I ON SPACES (PERS_ID_REGISTERER);
CREATE INDEX DEL_PERS_FK_I ON DELETIONS (PERS_ID_REGISTERER);
CREATE INDEX MAPR_CVTE_FK_I ON MATERIAL_PROPERTIES (CVTE_ID);
CREATE INDEX MAPR_MATE_FK_I ON MATERIAL_PROPERTIES (MATE_ID);
CREATE INDEX MAPR_MTPT_FK_I ON MATERIAL_PROPERTIES (MTPT_ID);
CREATE INDEX MAPR_PERS_FK_I ON MATERIAL_PROPERTIES (PERS_ID_REGISTERER);
CREATE INDEX MAPR_MAPR_FK_I ON MATERIAL_PROPERTIES (MATE_PROP_ID);
CREATE INDEX MAPRH_ETPT_FK_I ON MATERIAL_PROPERTIES_HISTORY (MTPT_ID);
CREATE INDEX MAPRH_EXPE_FK_I ON MATERIAL_PROPERTIES_HISTORY (MATE_ID);
CREATE INDEX MAPRH_VUTS_FK_I ON MATERIAL_PROPERTIES_HISTORY (VALID_UNTIL_TIMESTAMP);
CREATE INDEX MATE_MATY_FK_I ON MATERIALS (MATY_ID);
CREATE INDEX MATE_PERS_FK_I ON MATERIALS (PERS_ID_REGISTERER);
CREATE INDEX MTPT_MATY_FK_I ON MATERIAL_TYPE_PROPERTY_TYPES (MATY_ID);
CREATE INDEX MTPT_PERS_FK_I ON MATERIAL_TYPE_PROPERTY_TYPES (PERS_ID_REGISTERER);
CREATE INDEX MTPT_PRTY_FK_I ON MATERIAL_TYPE_PROPERTY_TYPES (PRTY_ID);
CREATE INDEX PERS_SPACE_FK_I ON PERSONS (SPACE_ID);
CREATE INDEX PERS_IS_ACTIVE_I ON PERSONS (IS_ACTIVE);
CREATE INDEX PROJ_IDFRZ_PK_I ON PROJECTS (ID, FROZEN);
CREATE INDEX PROJ_IDFRZ_E_PK_I ON PROJECTS (ID, FROZEN_FOR_EXP);
CREATE INDEX PROJ_IDFRZ_S_PK_I ON PROJECTS (ID, FROZEN_FOR_SAMP);
CREATE INDEX PROJ_SPACE_FK_I ON PROJECTS (SPACE_ID);
CREATE INDEX PROJ_PERS_FK_I_LEADER ON PROJECTS (PERS_ID_LEADER);