001package ca.uhn.fhir.jpa.migrate.tasks; 002 003/*- 004 * #%L 005 * HAPI FHIR JPA Server 006 * %% 007 * Copyright (C) 2014 - 2022 Smile CDR, Inc. 008 * %% 009 * Licensed under the Apache License, Version 2.0 (the "License"); 010 * you may not use this file except in compliance with the License. 011 * You may obtain a copy of the License at 012 * 013 * http://www.apache.org/licenses/LICENSE-2.0 014 * 015 * Unless required by applicable law or agreed to in writing, software 016 * distributed under the License is distributed on an "AS IS" BASIS, 017 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 018 * See the License for the specific language governing permissions and 019 * limitations under the License. 020 * #L% 021 */ 022 023import ca.uhn.fhir.interceptor.model.RequestPartitionId; 024import ca.uhn.fhir.jpa.entity.Search; 025import ca.uhn.fhir.jpa.migrate.DriverTypeEnum; 026import ca.uhn.fhir.jpa.migrate.taskdef.ArbitrarySqlTask; 027import ca.uhn.fhir.jpa.migrate.taskdef.CalculateHashesTask; 028import ca.uhn.fhir.jpa.migrate.taskdef.CalculateOrdinalDatesTask; 029import ca.uhn.fhir.jpa.migrate.taskdef.ColumnTypeEnum; 030import ca.uhn.fhir.jpa.migrate.tasks.api.BaseMigrationTasks; 031import ca.uhn.fhir.jpa.migrate.tasks.api.Builder; 032import ca.uhn.fhir.jpa.model.config.PartitionSettings; 033import ca.uhn.fhir.jpa.model.entity.BaseResourceIndexedSearchParam; 034import ca.uhn.fhir.jpa.model.entity.ModelConfig; 035import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamDate; 036import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantity; 037import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamString; 038import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamToken; 039import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamUri; 040import ca.uhn.fhir.jpa.model.entity.SearchParamPresentEntity; 041import ca.uhn.fhir.util.VersionEnum; 042 043import java.util.Arrays; 044import java.util.HashMap; 045import java.util.List; 046import java.util.Map; 047import java.util.Optional; 048import java.util.Set; 049import java.util.stream.Collectors; 050 051@SuppressWarnings({"SqlNoDataSourceInspection", "SpellCheckingInspection"}) 052public class HapiFhirJpaMigrationTasks extends BaseMigrationTasks<VersionEnum> { 053 054 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 055 public static final DriverTypeEnum[] NON_AUTOMATIC_FK_INDEX_PLATFORMS = new DriverTypeEnum[]{ 056 DriverTypeEnum.POSTGRES_9_4, DriverTypeEnum.ORACLE_12C, DriverTypeEnum.MSSQL_2012}; 057 private final Set<FlagEnum> myFlags; 058 059 060 /** 061 * Constructor 062 */ 063 public HapiFhirJpaMigrationTasks(Set<String> theFlags) { 064 myFlags = theFlags 065 .stream() 066 .map(FlagEnum::fromCommandLineValue) 067 .collect(Collectors.toSet()); 068 069 init330(); // 20180114 - 20180329 070 init340(); // 20180401 - 20180528 071 init350(); // 20180601 - 20180917 072 init360(); // 20180918 - 20181112 073 init400(); // 20190401 - 20190814 074 init410(); // 20190815 - 20191014 075 init420(); // 20191015 - 20200217 076 init430(); // Replaced by 5.0.0 077 init500(); // 20200218 - 20200513 078 init501(); // 20200514 - 20200515 079 init510(); // 20200516 - 20201028 080 init520(); // 20201029 - 081 init530(); 082 init540(); // 20210218 - 20210520 083 init550(); // 20210520 - 084 init560(); // 20211027 - 085 init570(); // 20211102 - 086 init600(); // 20211102 - 087 init610(); 088 } 089 090 private void init610() { 091 Builder version = forVersion(VersionEnum.V6_1_0); 092 093 // add new REPORT column to BATCH2 tables 094 version 095 .onTable("BT2_JOB_INSTANCE") 096 .addColumn("20220601.1", "REPORT") 097 .nullable() 098 .type(ColumnTypeEnum.CLOB); 099 } 100 101 private void init600() { 102 Builder version = forVersion(VersionEnum.V6_0_0); 103 104 /** 105 * New indexing for the core SPIDX tables. 106 * Ensure all queries can be satisfied by the index directly, 107 * either as left or right table in a hash or sort join. 108 * 109 * new date search indexing 110 * @see ca.uhn.fhir.jpa.search.builder.predicate.DatePredicateBuilder 111 * @see ResourceIndexedSearchParamDate 112 */ 113 { 114 Builder.BuilderWithTableName dateTable = version.onTable("HFJ_SPIDX_DATE"); 115 116 // replace and drop IDX_SP_DATE_HASH 117 dateTable 118 .addIndex("20220207.1", "IDX_SP_DATE_HASH_V2") 119 .unique(false) 120 .online(true) 121 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH", "RES_ID", "PARTITION_ID"); 122 dateTable.dropIndexOnline("20220207.2", "IDX_SP_DATE_HASH"); 123 124 // drop redundant 125 dateTable.dropIndexOnline("20220207.3", "IDX_SP_DATE_HASH_LOW"); 126 127 // replace and drop IDX_SP_DATE_HASH_HIGH 128 dateTable 129 .addIndex("20220207.4", "IDX_SP_DATE_HASH_HIGH_V2") 130 .unique(false) 131 .online(true) 132 .withColumns("HASH_IDENTITY", "SP_VALUE_HIGH", "RES_ID", "PARTITION_ID"); 133 dateTable.dropIndexOnline("20220207.5", "IDX_SP_DATE_HASH_HIGH"); 134 135 // replace and drop IDX_SP_DATE_ORD_HASH 136 dateTable 137 .addIndex("20220207.6", "IDX_SP_DATE_ORD_HASH_V2") 138 .unique(false) 139 .online(true) 140 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL", "SP_VALUE_HIGH_DATE_ORDINAL", "RES_ID", "PARTITION_ID"); 141 dateTable.dropIndexOnline("20220207.7", "IDX_SP_DATE_ORD_HASH"); 142 143 // replace and drop IDX_SP_DATE_ORD_HASH_HIGH 144 dateTable 145 .addIndex("20220207.8", "IDX_SP_DATE_ORD_HASH_HIGH_V2") 146 .unique(false) 147 .online(true) 148 .withColumns("HASH_IDENTITY", "SP_VALUE_HIGH_DATE_ORDINAL", "RES_ID", "PARTITION_ID"); 149 dateTable.dropIndexOnline("20220207.9", "IDX_SP_DATE_ORD_HASH_HIGH"); 150 151 // drop redundant 152 dateTable.dropIndexOnline("20220207.10", "IDX_SP_DATE_ORD_HASH_LOW"); 153 154 // replace and drop IDX_SP_DATE_RESID 155 dateTable 156 .addIndex("20220207.11", "IDX_SP_DATE_RESID_V2") 157 .unique(false) 158 .online(true) 159 .withColumns("RES_ID", "HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH", "SP_VALUE_LOW_DATE_ORDINAL", "SP_VALUE_HIGH_DATE_ORDINAL", "PARTITION_ID"); 160 // some engines tie the FK constraint to a particular index. 161 // So we need to drop and recreate the constraint to drop the old RES_ID index. 162 // Rename it while we're at it. FK17s70oa59rm9n61k9thjqrsqm was not a pretty name. 163 dateTable.dropForeignKey("20220207.12", "FK17S70OA59RM9N61K9THJQRSQM", "HFJ_RESOURCE"); 164 dateTable.dropIndexOnline("20220207.13", "IDX_SP_DATE_RESID"); 165 dateTable.dropIndexOnline("20220207.14", "FK17S70OA59RM9N61K9THJQRSQM"); 166 167 dateTable.addForeignKey("20220207.15", "FK_SP_DATE_RES") 168 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 169 170 // drop obsolete 171 dateTable.dropIndexOnline("20220207.16", "IDX_SP_DATE_UPDATED"); 172 } 173 174 /** 175 * new token search indexing 176 * @see ca.uhn.fhir.jpa.search.builder.predicate.TokenPredicateBuilder 177 * @see ResourceIndexedSearchParamToken 178 */ 179 { 180 Builder.BuilderWithTableName tokenTable = version.onTable("HFJ_SPIDX_TOKEN"); 181 182 // replace and drop IDX_SP_TOKEN_HASH for sorting 183 tokenTable 184 .addIndex("20220208.1", "IDX_SP_TOKEN_HASH_V2") 185 .unique(false).online(true) 186 .withColumns("HASH_IDENTITY", "SP_SYSTEM", "SP_VALUE", "RES_ID", "PARTITION_ID"); 187 188 tokenTable.dropIndexOnline("20220208.2", "IDX_SP_TOKEN_HASH"); 189 190 // for search by system 191 tokenTable 192 .addIndex("20220208.3", "IDX_SP_TOKEN_HASH_S_V2") 193 .unique(false).online(true) 194 .withColumns("HASH_SYS", "RES_ID", "PARTITION_ID"); 195 196 tokenTable.dropIndexOnline("20220208.4", "IDX_SP_TOKEN_HASH_S"); 197 198 // for search by system+value 199 tokenTable 200 .addIndex("20220208.5", "IDX_SP_TOKEN_HASH_SV_V2") 201 .unique(false).online(true) 202 .withColumns("HASH_SYS_AND_VALUE", "RES_ID", "PARTITION_ID"); 203 204 tokenTable.dropIndexOnline("20220208.6", "IDX_SP_TOKEN_HASH_SV"); 205 206 // for search by value 207 tokenTable 208 .addIndex("20220208.7", "IDX_SP_TOKEN_HASH_V_V2") 209 .unique(false).online(true) 210 .withColumns("HASH_VALUE", "RES_ID", "PARTITION_ID"); 211 212 tokenTable.dropIndexOnline("20220208.8", "IDX_SP_TOKEN_HASH_V"); 213 214 // obsolete. We're dropping this column. 215 tokenTable.dropIndexOnline("20220208.9", "IDX_SP_TOKEN_UPDATED"); 216 217 // for joining as second table: 218 { 219 // replace and drop IDX_SP_TOKEN_RESID, and the associated fk constraint 220 tokenTable 221 .addIndex("20220208.10", "IDX_SP_TOKEN_RESID_V2") 222 .unique(false).online(true) 223 .withColumns("RES_ID", "HASH_SYS_AND_VALUE", "HASH_VALUE", "HASH_SYS", "HASH_IDENTITY", "PARTITION_ID"); 224 225 // some engines tie the FK constraint to a particular index. 226 // So we need to drop and recreate the constraint to drop the old RES_ID index. 227 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 228 tokenTable.dropForeignKey("20220208.11", "FK7ULX3J1GG3V7MAQREJGC7YBC4", "HFJ_RESOURCE"); 229 tokenTable.dropIndexOnline("20220208.12", "IDX_SP_TOKEN_RESID"); 230 tokenTable.dropIndexOnline("20220208.13", "FK7ULX3J1GG3V7MAQREJGC7YBC4"); 231 232 tokenTable.addForeignKey("20220208.14", "FK_SP_TOKEN_RES") 233 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 234 } 235 } 236 237 // fix for https://github.com/hapifhir/hapi-fhir/issues/3316 238 // index must have same name that indexed FK or SchemaMigrationTest complains because H2 sets this index automatically 239 240 version.onTable("TRM_VALUESET_C_DESIGNATION") 241 .addIndex("20220223.1", "FK_TRM_VALUESET_CONCEPT_PID") 242 .unique(false) 243 .withColumns("VALUESET_CONCEPT_PID") 244 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 245 246 // Batch2 Framework 247 248 Builder.BuilderAddTableByColumns batchInstance = version.addTableByColumns("20220227.1", "BT2_JOB_INSTANCE", "ID"); 249 batchInstance.addColumn("ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 250 batchInstance.addColumn("CREATE_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 251 batchInstance.addColumn("START_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 252 batchInstance.addColumn("END_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 253 batchInstance.addColumn("DEFINITION_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 254 batchInstance.addColumn("DEFINITION_VER").nonNullable().type(ColumnTypeEnum.INT); 255 batchInstance.addColumn("STAT").nonNullable().type(ColumnTypeEnum.STRING, 20); 256 batchInstance.addColumn("JOB_CANCELLED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 257 batchInstance.addColumn("PARAMS_JSON").nullable().type(ColumnTypeEnum.STRING, 2000); 258 batchInstance.addColumn("PARAMS_JSON_LOB").nullable().type(ColumnTypeEnum.CLOB); 259 batchInstance.addColumn("CMB_RECS_PROCESSED").nullable().type(ColumnTypeEnum.INT); 260 batchInstance.addColumn("CMB_RECS_PER_SEC").nullable().type(ColumnTypeEnum.DOUBLE); 261 batchInstance.addColumn("TOT_ELAPSED_MILLIS").nullable().type(ColumnTypeEnum.INT); 262 batchInstance.addColumn("WORK_CHUNKS_PURGED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 263 batchInstance.addColumn("PROGRESS_PCT").nullable().type(ColumnTypeEnum.DOUBLE); 264 batchInstance.addColumn("ERROR_MSG").nullable().type(ColumnTypeEnum.STRING, 500); 265 batchInstance.addColumn("ERROR_COUNT").nullable().type(ColumnTypeEnum.INT); 266 batchInstance.addColumn("EST_REMAINING").nullable().type(ColumnTypeEnum.STRING, 100); 267 batchInstance.addIndex("20220227.2", "IDX_BT2JI_CT").unique(false).withColumns("CREATE_TIME"); 268 269 Builder.BuilderAddTableByColumns batchChunk = version.addTableByColumns("20220227.3", "BT2_WORK_CHUNK", "ID"); 270 batchChunk.addColumn("ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 271 batchChunk.addColumn("SEQ").nonNullable().type(ColumnTypeEnum.INT); 272 batchChunk.addColumn("CREATE_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 273 batchChunk.addColumn("START_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 274 batchChunk.addColumn("END_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 275 batchChunk.addColumn("DEFINITION_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 276 batchChunk.addColumn("DEFINITION_VER").nonNullable().type(ColumnTypeEnum.INT); 277 batchChunk.addColumn("STAT").nonNullable().type(ColumnTypeEnum.STRING, 20); 278 batchChunk.addColumn("RECORDS_PROCESSED").nullable().type(ColumnTypeEnum.INT); 279 batchChunk.addColumn("TGT_STEP_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 280 batchChunk.addColumn("CHUNK_DATA").nullable().type(ColumnTypeEnum.CLOB); 281 batchChunk.addColumn("INSTANCE_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 282 batchChunk.addColumn("ERROR_MSG").nullable().type(ColumnTypeEnum.STRING, 500); 283 batchChunk.addColumn("ERROR_COUNT").nonNullable().type(ColumnTypeEnum.INT); 284 batchChunk.addIndex("20220227.4", "IDX_BT2WC_II_SEQ").unique(false).withColumns("INSTANCE_ID", "SEQ"); 285 batchChunk.addForeignKey("20220227.5", "FK_BT2WC_INSTANCE").toColumn("INSTANCE_ID").references("BT2_JOB_INSTANCE", "ID"); 286 287 replaceNumericSPIndices(version); 288 replaceQuantitySPIndices(version); 289 290 // Drop Index on HFJ_RESOURCE.INDEX_STATUS 291 version 292 .onTable("HFJ_RESOURCE") 293 .dropIndex("20220314.1", "IDX_INDEXSTATUS"); 294 295 version 296 .onTable("BT2_JOB_INSTANCE") 297 .addColumn("20220416.1", "CUR_GATED_STEP_ID") 298 .nullable() 299 .type(ColumnTypeEnum.STRING, 100); 300 301 //Make Job expiry nullable so that we can prevent job expiry by using a null value. 302 version 303 .onTable("HFJ_BLK_EXPORT_JOB").modifyColumn("20220423.1", "EXP_TIME").nullable().withType(ColumnTypeEnum.DATE_TIMESTAMP); 304 305 // New Index on HFJ_RESOURCE for $reindex Operation - hapi-fhir #3534 306 { 307 version.onTable("HFJ_RESOURCE") 308 .addIndex("20220425.1", "IDX_RES_TYPE_DEL_UPDATED") 309 .unique(false) 310 .online(true) 311 .withColumns("RES_TYPE", "RES_DELETED_AT", "RES_UPDATED", "PARTITION_ID", "RES_ID"); 312 313 // Drop existing Index on HFJ_RESOURCE.RES_TYPE since the new Index will meet the overall Index Demand 314 version 315 .onTable("HFJ_RESOURCE") 316 .dropIndexOnline("20220425.2", "IDX_RES_TYPE"); 317 } 318 319 /** 320 * Update string indexing 321 * @see ca.uhn.fhir.jpa.search.builder.predicate.StringPredicateBuilder 322 * @see ResourceIndexedSearchParamString 323 */ 324 { 325 Builder.BuilderWithTableName tokenTable = version.onTable("HFJ_SPIDX_STRING"); 326 327 // add res_id, and partition_id so queries are covered without row-reads. 328 tokenTable 329 .addIndex("20220428.1", "IDX_SP_STRING_HASH_NRM_V2") 330 .unique(false) 331 .online(true) 332 .withColumns("HASH_NORM_PREFIX", "SP_VALUE_NORMALIZED", "RES_ID", "PARTITION_ID"); 333 tokenTable.dropIndexOnline("20220428.2", "IDX_SP_STRING_HASH_NRM"); 334 335 tokenTable 336 .addIndex("20220428.3", "IDX_SP_STRING_HASH_EXCT_V2") 337 .unique(false) 338 .online(true) 339 .withColumns("HASH_EXACT", "RES_ID", "PARTITION_ID"); 340 tokenTable.dropIndexOnline("20220428.4", "IDX_SP_STRING_HASH_EXCT"); 341 342 // we will drop the updated column. Start with the index. 343 tokenTable.dropIndexOnline("20220428.5", "IDX_SP_STRING_UPDATED"); 344 } 345 346 // Update tag indexing 347 { 348 Builder.BuilderWithTableName resTagTable = version.onTable("HFJ_RES_TAG"); 349 350 // add res_id, and partition_id so queries are covered without row-reads. 351 resTagTable 352 .addIndex("20220429.1", "IDX_RES_TAG_RES_TAG") 353 .unique(false) 354 .online(true) 355 .withColumns("RES_ID", "TAG_ID", "PARTITION_ID"); 356 resTagTable 357 .addIndex("20220429.2", "IDX_RES_TAG_TAG_RES") 358 .unique(false) 359 .online(true) 360 .withColumns("TAG_ID", "RES_ID", "PARTITION_ID"); 361 362 resTagTable.dropIndex("20220429.4", "IDX_RESTAG_TAGID"); 363 // Weird that we don't have addConstraint. No time to do it today. 364 Map<DriverTypeEnum, String> addResTagConstraint = new HashMap<>(); 365 addResTagConstraint.put(DriverTypeEnum.H2_EMBEDDED, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 366 addResTagConstraint.put(DriverTypeEnum.MARIADB_10_1, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 367 addResTagConstraint.put(DriverTypeEnum.MSSQL_2012, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 368 addResTagConstraint.put(DriverTypeEnum.MYSQL_5_7, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 369 addResTagConstraint.put(DriverTypeEnum.ORACLE_12C, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 370 addResTagConstraint.put(DriverTypeEnum.POSTGRES_9_4, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 371 version.executeRawSql("20220429.5", addResTagConstraint); 372 373 Builder.BuilderWithTableName tagTable = version.onTable("HFJ_TAG_DEF"); 374 tagTable 375 .addIndex("20220429.6", "IDX_TAG_DEF_TP_CD_SYS") 376 .unique(false) 377 .online(false) 378 .withColumns("TAG_TYPE", "TAG_CODE", "TAG_SYSTEM", "TAG_ID"); 379 // move constraint to new index 380 // Ugh. Only oracle supports using IDX_TAG_DEF_TP_CD_SYS to enforce this constraint. The others will create another index. 381 // For Sql Server, should change the index to be unique with include columns. Do this in 6.1 382 tagTable.dropIndex("20220429.8", "IDX_TAGDEF_TYPESYSCODE"); 383 Map<DriverTypeEnum, String> addTagDefConstraint = new HashMap<>(); 384 addTagDefConstraint.put(DriverTypeEnum.H2_EMBEDDED, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 385 addTagDefConstraint.put(DriverTypeEnum.MARIADB_10_1, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 386 addTagDefConstraint.put(DriverTypeEnum.MSSQL_2012, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 387 addTagDefConstraint.put(DriverTypeEnum.MYSQL_5_7, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 388 addTagDefConstraint.put(DriverTypeEnum.ORACLE_12C, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 389 addTagDefConstraint.put(DriverTypeEnum.POSTGRES_9_4, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 390 version.executeRawSql("20220429.9", addTagDefConstraint); 391 392 } 393 394 395 // Fix for https://github.com/hapifhir/hapi-fhir-jpaserver-starter/issues/328 396 version.onTable("NPM_PACKAGE_VER") 397 .modifyColumn("20220501.1","FHIR_VERSION_ID").nonNullable().withType(ColumnTypeEnum.STRING, 20); 398 399 version.onTable("NPM_PACKAGE_VER_RES") 400 .modifyColumn("20220501.2","FHIR_VERSION_ID").nonNullable().withType(ColumnTypeEnum.STRING, 20); 401 402 // Fix for https://gitlab.com/simpatico.ai/cdr/-/issues/3166 403 version.onTable("MPI_LINK") 404 .addIndex("20220613.1", "IDX_EMPI_MATCH_TGT_VER") 405 .unique(false) 406 .online(true) 407 .withColumns("MATCH_RESULT", "TARGET_PID", "VERSION"); 408 } 409 410 /** 411 * new numeric search indexing 412 * 413 * @see ca.uhn.fhir.jpa.search.builder.predicate.NumberPredicateBuilder 414 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamNumber 415 */ 416 private void replaceNumericSPIndices(Builder theVersion) { 417 Builder.BuilderWithTableName numberTable = theVersion.onTable("HFJ_SPIDX_NUMBER"); 418 419 // Main query index 420 numberTable 421 .addIndex("20220304.1", "IDX_SP_NUMBER_HASH_VAL_V2") 422 .unique(false) 423 .online(true) 424 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 425 426 numberTable.dropIndexOnline("20220304.2", "IDX_SP_NUMBER_HASH_VAL"); 427 428 // for joining to other queries 429 { 430 numberTable 431 .addIndex("20220304.3", "IDX_SP_NUMBER_RESID_V2") 432 .unique(false).online(true) 433 .withColumns("RES_ID", "HASH_IDENTITY", "SP_VALUE", "PARTITION_ID"); 434 435 // some engines tie the FK constraint to a particular index. 436 // So we need to drop and recreate the constraint to drop the old RES_ID index. 437 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 438 numberTable.dropForeignKey("20220304.4", "FKCLTIHNC5TGPRJ9BHPT7XI5OTB", "HFJ_RESOURCE"); 439 numberTable.dropIndexOnline("20220304.5", "IDX_SP_NUMBER_RESID"); 440 numberTable.dropIndexOnline("20220304.6", "FKCLTIHNC5TGPRJ9BHPT7XI5OTB"); 441 442 numberTable.addForeignKey("20220304.7", "FK_SP_NUMBER_RES") 443 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 444 } 445 // obsolete 446 numberTable.dropIndexOnline("20220304.8", "IDX_SP_NUMBER_UPDATED"); 447 } 448 449 /** 450 * new quantity search indexing 451 * 452 * @see ca.uhn.fhir.jpa.search.builder.predicate.QuantityPredicateBuilder 453 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantity 454 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantityNormalized 455 */ 456 private void replaceQuantitySPIndices(Builder theVersion) { 457 { 458 Builder.BuilderWithTableName quantityTable = theVersion.onTable("HFJ_SPIDX_QUANTITY"); 459 460 // bare quantity 461 quantityTable 462 .addIndex("20220304.11", "IDX_SP_QUANTITY_HASH_V2") 463 .unique(false) 464 .online(true) 465 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 466 467 quantityTable.dropIndexOnline("20220304.12", "IDX_SP_QUANTITY_HASH"); 468 469 // quantity with system+units 470 quantityTable 471 .addIndex("20220304.13", "IDX_SP_QUANTITY_HASH_SYSUN_V2") 472 .unique(false) 473 .online(true) 474 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 475 476 quantityTable.dropIndexOnline("20220304.14", "IDX_SP_QUANTITY_HASH_SYSUN"); 477 478 // quantity with units 479 quantityTable 480 .addIndex("20220304.15", "IDX_SP_QUANTITY_HASH_UN_V2") 481 .unique(false) 482 .online(true) 483 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 484 485 quantityTable.dropIndexOnline("20220304.16", "IDX_SP_QUANTITY_HASH_UN"); 486 487 // for joining to other queries and sorts 488 { 489 quantityTable 490 .addIndex("20220304.17", "IDX_SP_QUANTITY_RESID_V2") 491 .unique(false).online(true) 492 .withColumns("RES_ID", "HASH_IDENTITY", "HASH_IDENTITY_SYS_UNITS", "HASH_IDENTITY_AND_UNITS", "SP_VALUE", "PARTITION_ID"); 493 494 // some engines tie the FK constraint to a particular index. 495 // So we need to drop and recreate the constraint to drop the old RES_ID index. 496 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 497 quantityTable.dropForeignKey("20220304.18", "FKN603WJJOI1A6ASEWXBBD78BI5", "HFJ_RESOURCE"); 498 quantityTable.dropIndexOnline("20220304.19", "IDX_SP_QUANTITY_RESID"); 499 quantityTable.dropIndexOnline("20220304.20", "FKN603WJJOI1A6ASEWXBBD78BI5"); 500 501 quantityTable.addForeignKey("20220304.21", "FK_SP_QUANTITY_RES") 502 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 503 } 504 // obsolete 505 quantityTable.dropIndexOnline("20220304.22", "IDX_SP_QUANTITY_UPDATED"); 506 } 507 508 { 509 Builder.BuilderWithTableName quantityNormTable = theVersion.onTable("HFJ_SPIDX_QUANTITY_NRML"); 510 511 // bare quantity 512 quantityNormTable 513 .addIndex("20220304.23", "IDX_SP_QNTY_NRML_HASH_V2") 514 .unique(false) 515 .online(true) 516 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 517 518 quantityNormTable.dropIndexOnline("20220304.24", "IDX_SP_QNTY_NRML_HASH"); 519 520 // quantity with system+units 521 quantityNormTable 522 .addIndex("20220304.25", "IDX_SP_QNTY_NRML_HASH_SYSUN_V2") 523 .unique(false) 524 .online(true) 525 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 526 527 quantityNormTable.dropIndexOnline("20220304.26", "IDX_SP_QNTY_NRML_HASH_SYSUN"); 528 529 // quantity with units 530 quantityNormTable 531 .addIndex("20220304.27", "IDX_SP_QNTY_NRML_HASH_UN_V2") 532 .unique(false) 533 .online(true) 534 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 535 536 quantityNormTable.dropIndexOnline("20220304.28", "IDX_SP_QNTY_NRML_HASH_UN"); 537 538 // for joining to other queries and sorts 539 { 540 quantityNormTable 541 .addIndex("20220304.29", "IDX_SP_QNTY_NRML_RESID_V2") 542 .unique(false).online(true) 543 .withColumns("RES_ID", "HASH_IDENTITY", "HASH_IDENTITY_SYS_UNITS", "HASH_IDENTITY_AND_UNITS", "SP_VALUE", "PARTITION_ID"); 544 545 // some engines tie the FK constraint to a particular index. 546 // So we need to drop and recreate the constraint to drop the old RES_ID index. 547 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 548 quantityNormTable.dropForeignKey("20220304.30", "FKRCJOVMUH5KC0O6FVBLE319PYV", "HFJ_RESOURCE"); 549 quantityNormTable.dropIndexOnline("20220304.31", "IDX_SP_QNTY_NRML_RESID"); 550 quantityNormTable.dropIndexOnline("20220304.32", "FKRCJOVMUH5KC0O6FVBLE319PYV"); 551 552 quantityNormTable.addForeignKey("20220304.33", "FK_SP_QUANTITYNM_RES") 553 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 554 } 555 // obsolete 556 quantityNormTable.dropIndexOnline("20220304.34", "IDX_SP_QNTY_NRML_UPDATED"); 557 558 } 559 } 560 561 /** 562 * See https://github.com/hapifhir/hapi-fhir/issues/3237 for reasoning for these indexes. 563 * This adds indexes to various tables to enhance delete-expunge performance, which deletes by PID. 564 */ 565 private void addIndexesForDeleteExpunge(Builder theVersion) { 566 567 theVersion.onTable("HFJ_HISTORY_TAG") 568 .addIndex("20211210.2", "IDX_RESHISTTAG_RESID") 569 .unique(false) 570 .withColumns("RES_ID"); 571 572 573 theVersion.onTable("HFJ_RES_VER_PROV") 574 .addIndex("20211210.3", "FK_RESVERPROV_RES_PID") 575 .unique(false) 576 .withColumns("RES_PID") 577 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 578 579 theVersion.onTable("HFJ_FORCED_ID") 580 .addIndex("20211210.4", "FK_FORCEDID_RESOURCE") 581 .unique(true) 582 .withColumns("RESOURCE_PID") 583 .doNothing()//This migration was added in error, as this table already has a unique constraint on RESOURCE_PID and every database creates an index on anything that is unique. 584 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 585 } 586 587 private void init570() { 588 Builder version = forVersion(VersionEnum.V5_7_0); 589 590 // both indexes must have same name that indexed FK or SchemaMigrationTest complains because H2 sets this index automatically 591 592 version.onTable("TRM_CONCEPT_PROPERTY") 593 .addIndex("20211102.1", "FK_CONCEPTPROP_CONCEPT") 594 .unique(false) 595 .withColumns("CONCEPT_PID") 596 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 597 598 version.onTable("TRM_CONCEPT_DESIG") 599 .addIndex("20211102.2", "FK_CONCEPTDESIG_CONCEPT") 600 .unique(false) 601 .withColumns("CONCEPT_PID") 602 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 603 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 604 605 version.onTable("TRM_CONCEPT_PC_LINK") 606 .addIndex("20211102.3", "FK_TERM_CONCEPTPC_CHILD") 607 .unique(false) 608 .withColumns("CHILD_PID") 609 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 610 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 611 612 version.onTable("TRM_CONCEPT_PC_LINK") 613 .addIndex("20211102.4", "FK_TERM_CONCEPTPC_PARENT") 614 .unique(false) 615 .withColumns("PARENT_PID") 616 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 617 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 618 619 addIndexesForDeleteExpunge(version); 620 621 // Add inline resource text column 622 version.onTable("HFJ_RES_VER") 623 .addColumn("20220102.1", "RES_TEXT_VC") 624 .nullable() 625 .type(ColumnTypeEnum.STRING, 4000); 626 627 // Add partition id column for mdm 628 Builder.BuilderWithTableName empiLink = version.onTable("MPI_LINK"); 629 630 empiLink.addColumn("20220324.1", "PARTITION_ID") 631 .nullable() 632 .type(ColumnTypeEnum.INT); 633 empiLink.addColumn("20220324.2", "PARTITION_DATE") 634 .nullable() 635 .type(ColumnTypeEnum.DATE_ONLY); 636 } 637 638 639 private void init560() { 640 init560_20211027(); 641 } 642 643 /** 644 * Mirgation for the batch job parameter size change. Overriding purposes only. 645 */ 646 protected void init560_20211027() { 647 // nothing 648 } 649 650 private void init550() { 651 652 Builder version = forVersion(VersionEnum.V5_5_0); 653 654 // For MSSQL only - Replace ForcedId index with a version that has an INCLUDE clause 655 Builder.BuilderWithTableName forcedId = version.onTable("HFJ_FORCED_ID"); 656 forcedId.dropIndex("20210516.1", "IDX_FORCEDID_TYPE_FID").onlyAppliesToPlatforms(DriverTypeEnum.MSSQL_2012).runEvenDuringSchemaInitialization(); 657 forcedId.addIndex("20210516.2", "IDX_FORCEDID_TYPE_FID").unique(true).includeColumns("RESOURCE_PID").withColumns("RESOURCE_TYPE", "FORCED_ID").onlyAppliesToPlatforms(DriverTypeEnum.MSSQL_2012).runEvenDuringSchemaInitialization(); 658 659 // Add bulk import file description 660 version.onTable("HFJ_BLK_IMPORT_JOBFILE") 661 .addColumn("20210528.1", "FILE_DESCRIPTION").nullable().type(ColumnTypeEnum.STRING, 500); 662 663 // Bump ConceptMap display lengths 664 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 665 .modifyColumn("20210617.1", "TARGET_DISPLAY").nullable().withType(ColumnTypeEnum.STRING, 500); 666 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 667 .modifyColumn("20210617.2", "SOURCE_DISPLAY").nullable().withType(ColumnTypeEnum.STRING, 500); 668 669 version.onTable("HFJ_BLK_EXPORT_JOB") 670 .modifyColumn("20210624.1", "REQUEST").nonNullable().withType(ColumnTypeEnum.STRING, 1024); 671 672 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 673 .modifyColumn("20210713.1", "IDX_STRING").nonNullable().withType(ColumnTypeEnum.STRING, 500); 674 675 version.onTable("HFJ_RESOURCE") 676 .addColumn("20210720.1", "SP_CMPTOKS_PRESENT").nullable().type(ColumnTypeEnum.BOOLEAN); 677 678 version.addIdGenerator("20210720.2", "SEQ_IDXCMBTOKNU_ID"); 679 680 Builder.BuilderAddTableByColumns cmpToks = version 681 .addTableByColumns("20210720.3", "HFJ_IDX_CMB_TOK_NU", "PID"); 682 cmpToks.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 683 cmpToks.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 684 cmpToks.addColumn("HASH_COMPLETE").nonNullable().type(ColumnTypeEnum.LONG); 685 cmpToks.addColumn("IDX_STRING").nonNullable().type(ColumnTypeEnum.STRING, 500); 686 cmpToks.addForeignKey("20210720.4", "FK_IDXCMBTOKNU_RES_ID").toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 687 cmpToks.addIndex("20210720.5", "IDX_IDXCMBTOKNU_STR").unique(false).withColumns("IDX_STRING"); 688 cmpToks.addIndex("20210720.6", "IDX_IDXCMBTOKNU_RES").unique(false).withColumns("RES_ID"); 689 690 Builder.BuilderWithTableName cmbTokNuTable = version.onTable("HFJ_IDX_CMB_TOK_NU"); 691 692 cmbTokNuTable.addColumn("20210722.1", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 693 cmbTokNuTable.addColumn("20210722.2", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 694 cmbTokNuTable.modifyColumn("20210722.3", "RES_ID").nullable().withType(ColumnTypeEnum.LONG); 695 696 // Dropping index on the language column, as it's no longer in use. 697 // TODO: After 2 releases from 5.5.0, drop the column too 698 version.onTable("HFJ_RESOURCE") 699 .dropIndex("20210908.1", "IDX_RES_LANG"); 700 701 version.onTable("TRM_VALUESET") 702 .addColumn("20210915.1", "EXPANDED_AT") 703 .nullable() 704 .type(ColumnTypeEnum.DATE_TIMESTAMP); 705 706 /* 707 * Replace CLOB columns with BLOB columns 708 */ 709 710 // TRM_VALUESET_CONCEPT.SOURCE_DIRECT_PARENT_PIDS 711 version.onTable("TRM_VALUESET_CONCEPT") 712 .migratePostgresTextClobToBinaryClob("20211003.1", "SOURCE_DIRECT_PARENT_PIDS"); 713 714 // TRM_CONCEPT.PARENT_PIDS 715 version.onTable("TRM_CONCEPT") 716 .migratePostgresTextClobToBinaryClob("20211003.2", "PARENT_PIDS"); 717 718 // HFJ_SEARCH.SEARCH_QUERY_STRING 719 version.onTable("HFJ_SEARCH") 720 .migratePostgresTextClobToBinaryClob("20211003.3", "SEARCH_QUERY_STRING"); 721 722 } 723 724 private void init540() { 725 726 Builder version = forVersion(VersionEnum.V5_4_0); 727 728 //-- add index on HFJ_SPIDX_DATE 729 version.onTable("HFJ_SPIDX_DATE").addIndex("20210309.1", "IDX_SP_DATE_HASH_HIGH") 730 .unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_HIGH") 731 .doNothing(); 732 733 //-- add index on HFJ_FORCED_ID 734 version.onTable("HFJ_FORCED_ID").addIndex("20210309.2", "IDX_FORCEID_FID") 735 .unique(false).withColumns("FORCED_ID"); 736 737 //-- ValueSet Concept Fulltext Indexing 738 version.onTable("TRM_VALUESET_CONCEPT").addColumn("20210406.1", "INDEX_STATUS").nullable().type(ColumnTypeEnum.LONG); 739 version.onTable("TRM_VALUESET_CONCEPT").addColumn("20210406.2", "SOURCE_DIRECT_PARENT_PIDS").nullable().type(ColumnTypeEnum.CLOB); 740 version.onTable("TRM_VALUESET_CONCEPT").addColumn("20210406.3", "SOURCE_PID").nullable().type(ColumnTypeEnum.LONG); 741 742 // Bulk Import Job 743 Builder.BuilderAddTableByColumns blkImportJobTable = version.addTableByColumns("20210410.1", "HFJ_BLK_IMPORT_JOB", "PID"); 744 blkImportJobTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 745 blkImportJobTable.addColumn("JOB_ID").nonNullable().type(ColumnTypeEnum.STRING, Search.UUID_COLUMN_LENGTH); 746 blkImportJobTable.addColumn("JOB_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 10); 747 blkImportJobTable.addColumn("STATUS_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 748 blkImportJobTable.addColumn("STATUS_MESSAGE").nullable().type(ColumnTypeEnum.STRING, 500); 749 blkImportJobTable.addColumn("JOB_DESC").nullable().type(ColumnTypeEnum.STRING, 500); 750 blkImportJobTable.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 751 blkImportJobTable.addColumn("FILE_COUNT").nonNullable().type(ColumnTypeEnum.INT); 752 blkImportJobTable.addColumn("ROW_PROCESSING_MODE").nonNullable().type(ColumnTypeEnum.STRING, 20); 753 blkImportJobTable.addColumn("BATCH_SIZE").nonNullable().type(ColumnTypeEnum.INT); 754 blkImportJobTable.addIndex("20210410.2", "IDX_BLKIM_JOB_ID").unique(true).withColumns("JOB_ID"); 755 version.addIdGenerator("20210410.3", "SEQ_BLKIMJOB_PID"); 756 757 // Bulk Import Job File 758 Builder.BuilderAddTableByColumns blkImportJobFileTable = version.addTableByColumns("20210410.4", "HFJ_BLK_IMPORT_JOBFILE", "PID"); 759 blkImportJobFileTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 760 blkImportJobFileTable.addColumn("JOB_PID").nonNullable().type(ColumnTypeEnum.LONG); 761 blkImportJobFileTable.addColumn("JOB_CONTENTS").nonNullable().type(ColumnTypeEnum.BLOB); 762 blkImportJobFileTable.addColumn("FILE_SEQ").nonNullable().type(ColumnTypeEnum.INT); 763 blkImportJobFileTable.addColumn("TENANT_NAME").nullable().type(ColumnTypeEnum.STRING, 200); 764 blkImportJobFileTable.addIndex("20210410.5", "IDX_BLKIM_JOBFILE_JOBID").unique(false).withColumns("JOB_PID"); 765 blkImportJobFileTable.addForeignKey("20210410.6", "FK_BLKIMJOBFILE_JOB").toColumn("JOB_PID").references("HFJ_BLK_IMPORT_JOB", "PID"); 766 version.addIdGenerator("20210410.7", "SEQ_BLKIMJOBFILE_PID"); 767 768 //Increase ResourceLink path length 769 version.onTable("HFJ_RES_LINK").modifyColumn("20210505.1", "SRC_PATH").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 500); 770 } 771 772 private void init530() { 773 Builder version = forVersion(VersionEnum.V5_3_0); 774 775 //-- TRM 776 version 777 .onTable("TRM_VALUESET_CONCEPT") 778 .dropIndex("20210104.1", "IDX_VS_CONCEPT_CS_CODE"); 779 780 version 781 .onTable("TRM_VALUESET_CONCEPT") 782 .addIndex("20210104.2", "IDX_VS_CONCEPT_CSCD").unique(true).withColumns("VALUESET_PID", "SYSTEM_URL", "CODEVAL"); 783 784 //-- Add new Table, HFJ_SPIDX_QUANTITY_NRML 785 version.addIdGenerator("20210109.1", "SEQ_SPIDX_QUANTITY_NRML"); 786 Builder.BuilderAddTableByColumns pkg = version.addTableByColumns("20210109.2", "HFJ_SPIDX_QUANTITY_NRML", "SP_ID"); 787 pkg.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 788 pkg.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 100); 789 pkg.addColumn("SP_UPDATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 790 pkg.addColumn("SP_MISSING").nonNullable().type(ColumnTypeEnum.BOOLEAN); 791 pkg.addColumn("SP_NAME").nonNullable().type(ColumnTypeEnum.STRING, 100); 792 pkg.addColumn("SP_ID").nonNullable().type(ColumnTypeEnum.LONG); 793 pkg.addColumn("SP_SYSTEM").nullable().type(ColumnTypeEnum.STRING, 200); 794 pkg.addColumn("SP_UNITS").nullable().type(ColumnTypeEnum.STRING, 200); 795 pkg.addColumn("HASH_IDENTITY_AND_UNITS").nullable().type(ColumnTypeEnum.LONG); 796 pkg.addColumn("HASH_IDENTITY_SYS_UNITS").nullable().type(ColumnTypeEnum.LONG); 797 pkg.addColumn("HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 798 pkg.addColumn("SP_VALUE").nullable().type(ColumnTypeEnum.FLOAT); 799 pkg.addIndex("20210109.3", "IDX_SP_QNTY_NRML_HASH").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE"); 800 pkg.addIndex("20210109.4", "IDX_SP_QNTY_NRML_HASH_UN").unique(false).withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE"); 801 pkg.addIndex("20210109.5", "IDX_SP_QNTY_NRML_HASH_SYSUN").unique(false).withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE"); 802 pkg.addIndex("20210109.6", "IDX_SP_QNTY_NRML_UPDATED").unique(false).withColumns("SP_UPDATED"); 803 pkg.addIndex("20210109.7", "IDX_SP_QNTY_NRML_RESID").unique(false).withColumns("RES_ID"); 804 805 //-- Link to the resourceTable 806 version.onTable("HFJ_RESOURCE").addColumn("20210109.10", "SP_QUANTITY_NRML_PRESENT").nullable().type(ColumnTypeEnum.BOOLEAN); 807 808 //-- Fixed the partition and fk 809 Builder.BuilderWithTableName nrmlTable = version.onTable("HFJ_SPIDX_QUANTITY_NRML"); 810 nrmlTable.addColumn("20210111.1", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 811 nrmlTable.addColumn("20210111.2", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 812 // - The fk name is generated from Hibernate, have to use this name here 813 nrmlTable 814 .addForeignKey("20210111.3", "FKRCJOVMUH5KC0O6FVBLE319PYV") 815 .toColumn("RES_ID") 816 .references("HFJ_RESOURCE", "RES_ID"); 817 818 Builder.BuilderWithTableName quantityTable = version.onTable("HFJ_SPIDX_QUANTITY"); 819 quantityTable.modifyColumn("20210116.1", "SP_VALUE").nullable().failureAllowed().withType(ColumnTypeEnum.DOUBLE); 820 821 // HFJ_RES_LINK 822 version.onTable("HFJ_RES_LINK") 823 .addColumn("20210126.1", "TARGET_RESOURCE_VERSION").nullable().type(ColumnTypeEnum.LONG); 824 825 } 826 827 protected void init520() { 828 Builder version = forVersion(VersionEnum.V5_2_0); 829 830 Builder.BuilderWithTableName mdmLink = version.onTable("MPI_LINK"); 831 mdmLink.addColumn("20201029.1", "GOLDEN_RESOURCE_PID").nonNullable().type(ColumnTypeEnum.LONG); 832 mdmLink.addColumn("20201029.2", "RULE_COUNT").nullable().type(ColumnTypeEnum.LONG); 833 mdmLink 834 .addForeignKey("20201029.3", "FK_EMPI_LINK_GOLDEN_RESOURCE") 835 .toColumn("GOLDEN_RESOURCE_PID") 836 .references("HFJ_RESOURCE", "RES_ID"); 837 } 838 839 protected void init510() { 840 Builder version = forVersion(VersionEnum.V5_1_0); 841 842 // NPM Packages 843 version.addIdGenerator("20200610.1", "SEQ_NPM_PACK"); 844 Builder.BuilderAddTableByColumns pkg = version.addTableByColumns("20200610.2", "NPM_PACKAGE", "PID"); 845 pkg.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 846 pkg.addColumn("PACKAGE_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 847 pkg.addColumn("CUR_VERSION_ID").nullable().type(ColumnTypeEnum.STRING, 200); 848 pkg.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 849 pkg.addColumn("PACKAGE_DESC").nullable().type(ColumnTypeEnum.STRING, 200); 850 pkg.addIndex("20200610.3", "IDX_PACK_ID").unique(true).withColumns("PACKAGE_ID"); 851 852 version.addIdGenerator("20200610.4", "SEQ_NPM_PACKVER"); 853 Builder.BuilderAddTableByColumns pkgVer = version.addTableByColumns("20200610.5", "NPM_PACKAGE_VER", "PID"); 854 pkgVer.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 855 pkgVer.addColumn("PACKAGE_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 856 pkgVer.addColumn("VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 857 pkgVer.addColumn("PACKAGE_PID").nonNullable().type(ColumnTypeEnum.LONG); 858 pkgVer.addColumn("BINARY_RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 859 pkgVer.addColumn("SAVED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 860 pkgVer.addColumn("PKG_DESC").nonNullable().type(ColumnTypeEnum.STRING, 200); 861 pkgVer.addColumn("DESC_UPPER").nonNullable().type(ColumnTypeEnum.STRING, 200); 862 pkgVer.addColumn("CURRENT_VERSION").nonNullable().type(ColumnTypeEnum.BOOLEAN); 863 pkgVer.addColumn("FHIR_VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 10); 864 pkgVer.addColumn("FHIR_VERSION").nonNullable().type(ColumnTypeEnum.STRING, 10); 865 pkgVer.addColumn("PACKAGE_SIZE_BYTES").nonNullable().type(ColumnTypeEnum.LONG); 866 pkgVer.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 867 pkgVer.addForeignKey("20200610.6", "FK_NPM_PKV_PKG").toColumn("PACKAGE_PID").references("NPM_PACKAGE", "PID"); 868 pkgVer.addForeignKey("20200610.7", "FK_NPM_PKV_RESID").toColumn("BINARY_RES_ID").references("HFJ_RESOURCE", "RES_ID"); 869 pkgVer.addIndex("20200610.8", "IDX_PACKVER").unique(true).withColumns("PACKAGE_ID", "VERSION_ID"); 870 871 version.addIdGenerator("20200610.9", "SEQ_NPM_PACKVERRES"); 872 Builder.BuilderAddTableByColumns pkgVerResAdd = version.addTableByColumns("20200610.10", "NPM_PACKAGE_VER_RES", "PID"); 873 pkgVerResAdd.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 874 pkgVerResAdd.addColumn("PACKVER_PID").nonNullable().type(ColumnTypeEnum.LONG); 875 pkgVerResAdd.addColumn("BINARY_RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 876 pkgVerResAdd.addColumn("FILE_DIR").nullable().type(ColumnTypeEnum.STRING, 200); 877 pkgVerResAdd.addColumn("FILE_NAME").nullable().type(ColumnTypeEnum.STRING, 200); 878 pkgVerResAdd.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 40); 879 pkgVerResAdd.addColumn("CANONICAL_URL").nullable().type(ColumnTypeEnum.STRING, 200); 880 pkgVerResAdd.addColumn("CANONICAL_VERSION").nullable().type(ColumnTypeEnum.STRING, 200); 881 pkgVerResAdd.addColumn("FHIR_VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 10); 882 pkgVerResAdd.addColumn("FHIR_VERSION").nonNullable().type(ColumnTypeEnum.STRING, 10); 883 pkgVerResAdd.addColumn("RES_SIZE_BYTES").nonNullable().type(ColumnTypeEnum.LONG); 884 pkgVerResAdd.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 885 pkgVerResAdd.addForeignKey("20200610.11", "FK_NPM_PACKVERRES_PACKVER").toColumn("PACKVER_PID").references("NPM_PACKAGE_VER", "PID"); 886 pkgVerResAdd.addForeignKey("20200610.12", "FK_NPM_PKVR_RESID").toColumn("BINARY_RES_ID").references("HFJ_RESOURCE", "RES_ID"); 887 pkgVerResAdd.addIndex("20200610.13", "IDX_PACKVERRES_URL").unique(false).withColumns("CANONICAL_URL"); 888 889 init510_20200610(); 890 891 Builder.BuilderWithTableName pkgVerMod = version.onTable("NPM_PACKAGE_VER"); 892 pkgVerMod.modifyColumn("20200629.1", "PKG_DESC").nullable().withType(ColumnTypeEnum.STRING, 200); 893 pkgVerMod.modifyColumn("20200629.2", "DESC_UPPER").nullable().withType(ColumnTypeEnum.STRING, 200); 894 895 init510_20200706_to_20200714(); 896 897 Builder.BuilderWithTableName empiLink = version.onTable("MPI_LINK"); 898 empiLink.addColumn("20200715.1", "VERSION").nonNullable().type(ColumnTypeEnum.STRING, 16); 899 empiLink.addColumn("20200715.2", "EID_MATCH").nullable().type(ColumnTypeEnum.BOOLEAN); 900 empiLink.addColumn("20200715.3", "NEW_PERSON").nullable().type(ColumnTypeEnum.BOOLEAN); 901 empiLink.addColumn("20200715.4", "VECTOR").nullable().type(ColumnTypeEnum.LONG); 902 empiLink.addColumn("20200715.5", "SCORE").nullable().type(ColumnTypeEnum.FLOAT); 903 904 905 init510_20200725(); 906 907 //EMPI Target Type 908 empiLink.addColumn("20200727.1", "TARGET_TYPE").nullable().type(ColumnTypeEnum.STRING, 40); 909 910 //ConceptMap add version for search 911 Builder.BuilderWithTableName trmConceptMap = version.onTable("TRM_CONCEPT_MAP"); 912 trmConceptMap.addColumn("20200910.1", "VER").nullable().type(ColumnTypeEnum.STRING, 200); 913 trmConceptMap.dropIndex("20200910.2", "IDX_CONCEPT_MAP_URL"); 914 trmConceptMap.addIndex("20200910.3", "IDX_CONCEPT_MAP_URL").unique(true).withColumns("URL", "VER"); 915 916 //Term CodeSystem Version and Term ValueSet Version 917 Builder.BuilderWithTableName trmCodeSystemVer = version.onTable("TRM_CODESYSTEM_VER"); 918 trmCodeSystemVer.addIndex("20200923.1", "IDX_CODESYSTEM_AND_VER").unique(true).withColumns("CODESYSTEM_PID", "CS_VERSION_ID"); 919 Builder.BuilderWithTableName trmValueSet = version.onTable("TRM_VALUESET"); 920 trmValueSet.addColumn("20200923.2", "VER").nullable().type(ColumnTypeEnum.STRING, 200); 921 trmValueSet.dropIndex("20200923.3", "IDX_VALUESET_URL"); 922 trmValueSet.addIndex("20200923.4", "IDX_VALUESET_URL").unique(true).withColumns("URL", "VER"); 923 924 //Term ValueSet Component add system version 925 Builder.BuilderWithTableName trmValueSetComp = version.onTable("TRM_VALUESET_CONCEPT"); 926 trmValueSetComp.addColumn("20201028.1", "SYSTEM_VER").nullable().type(ColumnTypeEnum.STRING, 200); 927 trmValueSetComp.dropIndex("20201028.2", "IDX_VS_CONCEPT_CS_CD"); 928 trmValueSetComp.addIndex("20201028.3", "IDX_VS_CONCEPT_CS_CODE").unique(true).withColumns("VALUESET_PID", "SYSTEM_URL", "SYSTEM_VER", "CODEVAL").doNothing(); 929 } 930 931 protected void init510_20200725() { 932 // nothing 933 } 934 935 protected void init510_20200610() { 936 // nothing 937 } 938 939 protected void init510_20200706_to_20200714() { 940 // nothing 941 } 942 943 private void init501() { //20200514 - present 944 Builder version = forVersion(VersionEnum.V5_0_1); 945 946 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 947 spidxDate.addIndex("20200514.1", "IDX_SP_DATE_HASH_LOW").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_LOW") 948 .doNothing(); 949 spidxDate.addIndex("20200514.2", "IDX_SP_DATE_ORD_HASH").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL", "SP_VALUE_HIGH_DATE_ORDINAL") 950 .doNothing(); 951 spidxDate.addIndex("20200514.3", "IDX_SP_DATE_ORD_HASH_LOW").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL") 952 .doNothing(); 953 954 // MPI_LINK 955 version.addIdGenerator("20200517.1", "SEQ_EMPI_LINK_ID"); 956 Builder.BuilderAddTableByColumns empiLink = version.addTableByColumns("20200517.2", "MPI_LINK", "PID"); 957 empiLink.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 958 959 empiLink.addColumn("PERSON_PID").nonNullable().type(ColumnTypeEnum.LONG); 960 empiLink 961 .addForeignKey("20200517.3", "FK_EMPI_LINK_PERSON") 962 .toColumn("PERSON_PID") 963 .references("HFJ_RESOURCE", "RES_ID"); 964 965 empiLink.addColumn("TARGET_PID").nonNullable().type(ColumnTypeEnum.LONG); 966 empiLink 967 .addForeignKey("20200517.4", "FK_EMPI_LINK_TARGET") 968 .toColumn("TARGET_PID") 969 .references("HFJ_RESOURCE", "RES_ID"); 970 971 empiLink.addColumn("MATCH_RESULT").nonNullable().type(ColumnTypeEnum.INT); 972 empiLink.addColumn("LINK_SOURCE").nonNullable().type(ColumnTypeEnum.INT); 973 empiLink.addColumn("CREATED").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 974 empiLink.addColumn("UPDATED").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 975 976 977 empiLink.addIndex("20200517.5", "IDX_EMPI_PERSON_TGT").unique(true).withColumns("PERSON_PID", "TARGET_PID"); 978 979 } 980 981 protected void init500() { // 20200218 - 20200519 982 Builder version = forVersion(VersionEnum.V5_0_0); 983 984 // Eliminate circular dependency. 985 version.onTable("HFJ_RESOURCE").dropColumn("20200218.1", "FORCED_ID_PID"); 986 version.onTable("HFJ_RES_VER").dropColumn("20200218.2", "FORCED_ID_PID"); 987 version.onTable("HFJ_RES_VER").addForeignKey("20200218.3", "FK_RESOURCE_HISTORY_RESOURCE").toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 988 version.onTable("HFJ_RES_VER").modifyColumn("20200220.1", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 989 // 990 991 // Drop unused column 992 version.onTable("HFJ_RESOURCE").dropIndex("20200419.1", "IDX_RES_PROFILE"); 993 version.onTable("HFJ_RESOURCE").dropColumn("20200419.2", "RES_PROFILE").failureAllowed(); 994 995 // Add Partitioning 996 Builder.BuilderAddTableByColumns partition = version.addTableByColumns("20200420.0", "HFJ_PARTITION", "PART_ID"); 997 partition.addColumn("PART_ID").nonNullable().type(ColumnTypeEnum.INT); 998 partition.addColumn("PART_NAME").nonNullable().type(ColumnTypeEnum.STRING, 200); 999 partition.addColumn("PART_DESC").nullable().type(ColumnTypeEnum.STRING, 200); 1000 partition.addIndex("20200420.1", "IDX_PART_NAME").unique(true).withColumns("PART_NAME"); 1001 1002 // Partition columns on individual tables 1003 version.onTable("HFJ_RESOURCE").addColumn("20200420.2", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1004 version.onTable("HFJ_RESOURCE").addColumn("20200420.3", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1005 version.onTable("HFJ_RES_VER").addColumn("20200420.4", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1006 version.onTable("HFJ_RES_VER").addColumn("20200420.5", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1007 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.6", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1008 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.7", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1009 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.8", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1010 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.9", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1011 version.onTable("HFJ_HISTORY_TAG").addColumn("20200420.10", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1012 version.onTable("HFJ_HISTORY_TAG").addColumn("20200420.11", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1013 version.onTable("HFJ_RES_TAG").addColumn("20200420.12", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1014 version.onTable("HFJ_RES_TAG").addColumn("20200420.13", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1015 version.onTable("HFJ_FORCED_ID").addColumn("20200420.14", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1016 version.onTable("HFJ_FORCED_ID").addColumn("20200420.15", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1017 version.onTable("HFJ_RES_LINK").addColumn("20200420.16", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1018 version.onTable("HFJ_RES_LINK").addColumn("20200420.17", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1019 version.onTable("HFJ_SPIDX_STRING").addColumn("20200420.18", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1020 version.onTable("HFJ_SPIDX_STRING").addColumn("20200420.19", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1021 version.onTable("HFJ_SPIDX_COORDS").addColumn("20200420.20", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1022 version.onTable("HFJ_SPIDX_COORDS").addColumn("20200420.21", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1023 version.onTable("HFJ_SPIDX_NUMBER").addColumn("20200420.22", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1024 version.onTable("HFJ_SPIDX_NUMBER").addColumn("20200420.23", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1025 version.onTable("HFJ_SPIDX_TOKEN").addColumn("20200420.24", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1026 version.onTable("HFJ_SPIDX_TOKEN").addColumn("20200420.25", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1027 version.onTable("HFJ_SPIDX_DATE").addColumn("20200420.26", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1028 version.onTable("HFJ_SPIDX_DATE").addColumn("20200420.27", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1029 version.onTable("HFJ_SPIDX_URI").addColumn("20200420.28", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1030 version.onTable("HFJ_SPIDX_URI").addColumn("20200420.29", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1031 version.onTable("HFJ_SPIDX_QUANTITY").addColumn("20200420.30", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1032 version.onTable("HFJ_SPIDX_QUANTITY").addColumn("20200420.31", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1033 version.onTable("HFJ_RES_VER_PROV").addColumn("20200420.32", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1034 version.onTable("HFJ_RES_VER_PROV").addColumn("20200420.33", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1035 version.onTable("HFJ_RES_PARAM_PRESENT").addColumn("20200420.34", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1036 version.onTable("HFJ_RES_PARAM_PRESENT").addColumn("20200420.35", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1037 1038 version.onTable("HFJ_SPIDX_STRING").modifyColumn("20200420.36", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1039 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20200420.37", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1040 version.onTable("HFJ_SPIDX_NUMBER").modifyColumn("20200420.38", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1041 version.onTable("HFJ_SPIDX_TOKEN").modifyColumn("20200420.39", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1042 version.onTable("HFJ_SPIDX_DATE").modifyColumn("20200420.40", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1043 version.onTable("HFJ_SPIDX_URI").modifyColumn("20200420.41", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1044 version.onTable("HFJ_SPIDX_QUANTITY").modifyColumn("20200420.42", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1045 1046 // Add support for integer comparisons during day-precision date search. 1047 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 1048 spidxDate.addColumn("20200501.1", "SP_VALUE_LOW_DATE_ORDINAL").nullable().type(ColumnTypeEnum.INT); 1049 spidxDate.addColumn("20200501.2", "SP_VALUE_HIGH_DATE_ORDINAL").nullable().type(ColumnTypeEnum.INT); 1050 1051 spidxDate.addTask(new CalculateOrdinalDatesTask(VersionEnum.V5_0_0, "20200501.3") 1052 .addCalculator("SP_VALUE_LOW_DATE_ORDINAL", t -> ResourceIndexedSearchParamDate.calculateOrdinalValue(t.getDate("SP_VALUE_LOW"))) 1053 .addCalculator("SP_VALUE_HIGH_DATE_ORDINAL", t -> ResourceIndexedSearchParamDate.calculateOrdinalValue(t.getDate("SP_VALUE_HIGH"))) 1054 .setColumnName("SP_VALUE_LOW_DATE_ORDINAL") //It doesn't matter which of the two we choose as they will both be null. 1055 ); 1056 1057 } 1058 1059 /** 1060 * Partway through the 4.3.0 releaase cycle we renumbered to 1061 * 5.0.0 - We have a bunch of NOP tasks here to avoid breakage for anyone 1062 * who installed a prerelease before we made the switch 1063 */ 1064 @SuppressWarnings("deprecation") 1065 private void init430() { 1066 Builder version = forVersion(VersionEnum.V4_3_0); 1067 version.addNop("20200218.1"); 1068 version.addNop("20200218.2"); 1069 version.addNop("20200218.3"); 1070 version.addNop("20200220.1"); 1071 version.addNop("20200419.1"); 1072 version.addNop("20200419.2"); 1073 version.addNop("20200420.0"); 1074 version.addNop("20200420.1"); 1075 version.addNop("20200420.2"); 1076 version.addNop("20200420.3"); 1077 version.addNop("20200420.4"); 1078 version.addNop("20200420.5"); 1079 version.addNop("20200420.6"); 1080 version.addNop("20200420.7"); 1081 version.addNop("20200420.8"); 1082 version.addNop("20200420.9"); 1083 version.addNop("20200420.10"); 1084 version.addNop("20200420.11"); 1085 version.addNop("20200420.12"); 1086 version.addNop("20200420.13"); 1087 version.addNop("20200420.14"); 1088 version.addNop("20200420.15"); 1089 version.addNop("20200420.16"); 1090 version.addNop("20200420.17"); 1091 version.addNop("20200420.18"); 1092 version.addNop("20200420.19"); 1093 version.addNop("20200420.20"); 1094 version.addNop("20200420.21"); 1095 version.addNop("20200420.22"); 1096 version.addNop("20200420.23"); 1097 version.addNop("20200420.24"); 1098 version.addNop("20200420.25"); 1099 version.addNop("20200420.26"); 1100 version.addNop("20200420.27"); 1101 version.addNop("20200420.28"); 1102 version.addNop("20200420.29"); 1103 version.addNop("20200420.30"); 1104 version.addNop("20200420.31"); 1105 version.addNop("20200420.32"); 1106 version.addNop("20200420.33"); 1107 version.addNop("20200420.34"); 1108 version.addNop("20200420.35"); 1109 version.addNop("20200420.36"); 1110 version.addNop("20200420.37"); 1111 version.addNop("20200420.38"); 1112 version.addNop("20200420.39"); 1113 version.addNop("20200420.40"); 1114 version.addNop("20200420.41"); 1115 version.addNop("20200420.42"); 1116 } 1117 1118 protected void init420() { // 20191015 - 20200217 1119 Builder version = forVersion(VersionEnum.V4_2_0); 1120 1121 // TermValueSetConceptDesignation 1122 version.onTable("TRM_VALUESET_C_DESIGNATION").dropIndex("20200202.1", "IDX_VALUESET_C_DSGNTN_VAL").failureAllowed(); 1123 Builder.BuilderWithTableName searchTable = version.onTable("HFJ_SEARCH"); 1124 searchTable.dropIndex("20200203.1", "IDX_SEARCH_LASTRETURNED"); 1125 searchTable.dropColumn("20200203.2", "SEARCH_LAST_RETURNED"); 1126 searchTable.addIndex("20200203.3", "IDX_SEARCH_CREATED").unique(false).withColumns("CREATED"); 1127 } 1128 1129 protected void init410() { // 20190815 - 20191014 1130 Builder version = forVersion(VersionEnum.V4_1_0); 1131 1132 /* 1133 * Note: The following tasks are markes as failure allowed - This is because all we're 1134 * doing is setting a not-null on a column that will never be null anyway. Setting not null 1135 * fails on SQL Server because there is an index on this column... Which is dumb, but hey. 1136 */ 1137 version.onTable("HFJ_SPIDX_NUMBER").modifyColumn("20190920.1", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1138 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20190920.2", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1139 version.onTable("HFJ_SPIDX_TOKEN").modifyColumn("20190920.3", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1140 version.onTable("HFJ_SPIDX_STRING").modifyColumn("20190920.4", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1141 version.onTable("HFJ_SPIDX_DATE").modifyColumn("20190920.5", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1142 version.onTable("HFJ_SPIDX_QUANTITY").modifyColumn("20190920.6", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1143 version.onTable("HFJ_SPIDX_URI").modifyColumn("20190920.7", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1144 1145 // HFJ_SEARCH 1146 version.onTable("HFJ_SEARCH").addColumn("20190921.1", "EXPIRY_OR_NULL").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1147 version.onTable("HFJ_SEARCH").addColumn("20190921.2", "NUM_BLOCKED").nullable().type(ColumnTypeEnum.INT); 1148 1149 // HFJ_BLK_EXPORT_JOB 1150 version.addIdGenerator("20190921.3", "SEQ_BLKEXJOB_PID"); 1151 Builder.BuilderAddTableByColumns bulkExportJob = version.addTableByColumns("20190921.4", "HFJ_BLK_EXPORT_JOB", "PID"); 1152 bulkExportJob.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1153 bulkExportJob.addColumn("JOB_ID").nonNullable().type(ColumnTypeEnum.STRING, 36); 1154 bulkExportJob.addColumn("JOB_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 10); 1155 bulkExportJob.addColumn("CREATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1156 bulkExportJob.addColumn("STATUS_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1157 bulkExportJob.addColumn("EXP_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1158 bulkExportJob.addColumn("REQUEST").nonNullable().type(ColumnTypeEnum.STRING, 500); 1159 bulkExportJob.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 1160 bulkExportJob.addColumn("EXP_SINCE").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1161 bulkExportJob.addColumn("STATUS_MESSAGE").nullable().type(ColumnTypeEnum.STRING, 500); 1162 bulkExportJob.addIndex("20190921.5", "IDX_BLKEX_EXPTIME").unique(false).withColumns("EXP_TIME"); 1163 bulkExportJob.addIndex("20190921.6", "IDX_BLKEX_JOB_ID").unique(true).withColumns("JOB_ID"); 1164 1165 // HFJ_BLK_EXPORT_COLLECTION 1166 version.addIdGenerator("20190921.7", "SEQ_BLKEXCOL_PID"); 1167 Builder.BuilderAddTableByColumns bulkExportCollection = version.addTableByColumns("20190921.8", "HFJ_BLK_EXPORT_COLLECTION", "PID"); 1168 bulkExportCollection.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1169 bulkExportCollection.addColumn("JOB_PID").nonNullable().type(ColumnTypeEnum.LONG); 1170 bulkExportCollection.addForeignKey("20190921.9", "FK_BLKEXCOL_JOB").toColumn("JOB_PID").references("HFJ_BLK_EXPORT_JOB", "PID"); 1171 bulkExportCollection.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 40); 1172 bulkExportCollection.addColumn("TYPE_FILTER").nullable().type(ColumnTypeEnum.STRING, 1000); 1173 bulkExportCollection.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 1174 1175 // HFJ_BLK_EXPORT_COLFILE 1176 version.addIdGenerator("20190921.10", "SEQ_BLKEXCOLFILE_PID"); 1177 Builder.BuilderAddTableByColumns bulkExportCollectionFile = version.addTableByColumns("20190921.11", "HFJ_BLK_EXPORT_COLFILE", "PID"); 1178 bulkExportCollectionFile.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1179 bulkExportCollectionFile.addColumn("COLLECTION_PID").nonNullable().type(ColumnTypeEnum.LONG); 1180 bulkExportCollectionFile.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1181 bulkExportCollectionFile.addForeignKey("20190921.12", "FK_BLKEXCOLFILE_COLLECT").toColumn("COLLECTION_PID").references("HFJ_BLK_EXPORT_COLLECTION", "PID"); 1182 1183 // HFJ_RES_VER_PROV 1184 version.startSectionWithMessage("Processing bulkExportCollectionFile: HFJ_RES_VER_PROV"); 1185 Builder.BuilderAddTableByColumns resVerProv = version.addTableByColumns("20190921.13", "HFJ_RES_VER_PROV", "RES_VER_PID"); 1186 resVerProv.addColumn("RES_VER_PID").nonNullable().type(ColumnTypeEnum.LONG); 1187 resVerProv 1188 .addForeignKey("20190921.14", "FK_RESVERPROV_RESVER_PID") 1189 .toColumn("RES_VER_PID") 1190 .references("HFJ_RES_VER", "PID"); 1191 resVerProv.addColumn("RES_PID").nonNullable().type(ColumnTypeEnum.LONG); 1192 resVerProv 1193 .addForeignKey("20190921.15", "FK_RESVERPROV_RES_PID") 1194 .toColumn("RES_PID") 1195 .references("HFJ_RESOURCE", "RES_ID"); 1196 resVerProv.addColumn("SOURCE_URI").nullable().type(ColumnTypeEnum.STRING, 100); 1197 resVerProv.addColumn("REQUEST_ID").nullable().type(ColumnTypeEnum.STRING, 16); 1198 resVerProv.addIndex("20190921.16", "IDX_RESVERPROV_SOURCEURI").unique(false).withColumns("SOURCE_URI"); 1199 resVerProv.addIndex("20190921.17", "IDX_RESVERPROV_REQUESTID").unique(false).withColumns("REQUEST_ID"); 1200 1201 // TermValueSetConceptDesignation 1202 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET_C_DESIGNATION"); 1203 Builder.BuilderWithTableName termValueSetConceptDesignationTable = version.onTable("TRM_VALUESET_C_DESIGNATION"); 1204 termValueSetConceptDesignationTable.addColumn("20190921.18", "VALUESET_PID").nonNullable().type(ColumnTypeEnum.LONG); 1205 termValueSetConceptDesignationTable 1206 .addForeignKey("20190921.19", "FK_TRM_VSCD_VS_PID") 1207 .toColumn("VALUESET_PID") 1208 .references("TRM_VALUESET", "PID"); 1209 1210 // Drop HFJ_SEARCH_RESULT foreign keys 1211 version.onTable("HFJ_SEARCH_RESULT").dropForeignKey("20190921.20", "FK_SEARCHRES_RES", "HFJ_RESOURCE"); 1212 version.onTable("HFJ_SEARCH_RESULT").dropForeignKey("20190921.21", "FK_SEARCHRES_SEARCH", "HFJ_SEARCH"); 1213 1214 // TermValueSet 1215 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET"); 1216 Builder.BuilderWithTableName termValueSetTable = version.onTable("TRM_VALUESET"); 1217 termValueSetTable.addColumn("20190921.22", "TOTAL_CONCEPTS").nonNullable().type(ColumnTypeEnum.LONG); 1218 termValueSetTable.addColumn("20190921.23", "TOTAL_CONCEPT_DESIGNATIONS").nonNullable().type(ColumnTypeEnum.LONG); 1219 termValueSetTable 1220 .dropIndex("20190921.24", "IDX_VALUESET_EXP_STATUS"); 1221 1222 version.dropIdGenerator("20190921.25", "SEQ_SEARCHPARM_ID"); 1223 1224 // TermValueSetConcept 1225 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET_CONCEPT"); 1226 Builder.BuilderWithTableName termValueSetConceptTable = version.onTable("TRM_VALUESET_CONCEPT"); 1227 termValueSetConceptTable.addColumn("20190921.26", "VALUESET_ORDER").nonNullable().type(ColumnTypeEnum.INT); 1228 termValueSetConceptTable 1229 .addIndex("20190921.27", "IDX_VS_CONCEPT_ORDER") 1230 .unique(true) 1231 .withColumns("VALUESET_PID", "VALUESET_ORDER"); 1232 1233 // Account for RESTYPE_LEN column increasing from 30 to 40 1234 version.onTable("HFJ_RESOURCE").modifyColumn("20191002.1", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1235 version.onTable("HFJ_RES_VER").modifyColumn("20191002.2", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1236 version.onTable("HFJ_HISTORY_TAG").modifyColumn("20191002.3", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1237 version.onTable("HFJ_RES_LINK").modifyColumn("20191002.4", "SOURCE_RESOURCE_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1238 version.onTable("HFJ_RES_LINK").modifyColumn("20191002.5", "TARGET_RESOURCE_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1239 version.onTable("HFJ_RES_TAG").modifyColumn("20191002.6", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1240 1241 // TermConceptDesignation 1242 version.startSectionWithMessage("Processing table: TRM_CONCEPT_DESIG"); 1243 version.onTable("TRM_CONCEPT_DESIG").modifyColumn("20191002.7", "VAL").nonNullable().withType(ColumnTypeEnum.STRING, 2000); 1244 1245 // TermValueSetConceptDesignation 1246 version.startSectionWithMessage("Processing table: TRM_VALUESET_C_DESIGNATION"); 1247 version.onTable("TRM_VALUESET_C_DESIGNATION").modifyColumn("20191002.8", "VAL").nonNullable().withType(ColumnTypeEnum.STRING, 2000); 1248 1249 // TermConceptProperty 1250 version.startSectionWithMessage("Processing table: TRM_CONCEPT_PROPERTY"); 1251 version.onTable("TRM_CONCEPT_PROPERTY").addColumn("20191002.9", "PROP_VAL_LOB").nullable().type(ColumnTypeEnum.BLOB); 1252 } 1253 1254 protected void init400() { // 20190401 - 20190814 1255 Builder version = forVersion(VersionEnum.V4_0_0); 1256 1257 // BinaryStorageEntity 1258 Builder.BuilderAddTableByColumns binaryBlob = version.addTableByColumns("20190722.1", "HFJ_BINARY_STORAGE_BLOB", "BLOB_ID"); 1259 binaryBlob.addColumn("BLOB_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1260 binaryBlob.addColumn("RESOURCE_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1261 binaryBlob.addColumn("BLOB_SIZE").nullable().type(ColumnTypeEnum.INT); 1262 binaryBlob.addColumn("CONTENT_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 100); 1263 binaryBlob.addColumn("BLOB_DATA").nonNullable().type(ColumnTypeEnum.BLOB); 1264 binaryBlob.addColumn("PUBLISHED_DATE").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1265 binaryBlob.addColumn("BLOB_HASH").nullable().type(ColumnTypeEnum.STRING, 128); 1266 1267 // Interim builds used this name 1268 version.onTable("TRM_VALUESET_CODE").dropThisTable("20190722.2"); 1269 1270 version.onTable("TRM_CONCEPT_MAP_GROUP") 1271 .renameColumn("20190722.3", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 1272 .renameColumn("20190722.4", "mySourceValueSet", "SOURCE_VS", false, true) 1273 .renameColumn("20190722.5", "myTargetValueSet", "TARGET_VS", false, true); 1274 version.onTable("TRM_CONCEPT_MAP_GROUP") 1275 .modifyColumn("20190722.6", "CONCEPT_MAP_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1276 version.onTable("TRM_CONCEPT_MAP_GROUP") 1277 .modifyColumn("20190722.7", "SOURCE_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1278 version.onTable("TRM_CONCEPT_MAP_GROUP") 1279 .modifyColumn("20190722.8", "SOURCE_VS").nullable().withType(ColumnTypeEnum.STRING, 200); 1280 version.onTable("TRM_CONCEPT_MAP_GROUP") 1281 .modifyColumn("20190722.9", "TARGET_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1282 version.onTable("TRM_CONCEPT_MAP_GROUP") 1283 .modifyColumn("20190722.10", "TARGET_VS").nullable().withType(ColumnTypeEnum.STRING, 200); 1284 1285 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1286 .renameColumn("20190722.11", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 1287 .renameColumn("20190722.12", "mySystem", "SYSTEM_URL", false, true) 1288 .renameColumn("20190722.13", "mySystemVersion", "SYSTEM_VERSION", false, true) 1289 .renameColumn("20190722.14", "myValueSet", "VALUESET_URL", false, true); 1290 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1291 .modifyColumn("20190722.15", "CONCEPT_MAP_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1292 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1293 .modifyColumn("20190722.16", "SOURCE_CODE").nonNullable().withType(ColumnTypeEnum.STRING, 500); 1294 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1295 .modifyColumn("20190722.17", "SYSTEM_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1296 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1297 .modifyColumn("20190722.18", "SYSTEM_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1298 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1299 .modifyColumn("20190722.19", "VALUESET_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1300 1301 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1302 .renameColumn("20190722.20", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 1303 .renameColumn("20190722.21", "mySystem", "SYSTEM_URL", false, true) 1304 .renameColumn("20190722.22", "mySystemVersion", "SYSTEM_VERSION", false, true) 1305 .renameColumn("20190722.23", "myValueSet", "VALUESET_URL", false, true); 1306 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1307 .modifyColumn("20190722.24", "CONCEPT_MAP_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1308 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1309 .modifyColumn("20190722.25", "SYSTEM_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1310 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1311 .modifyColumn("20190722.26", "SYSTEM_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1312 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1313 .modifyColumn("20190722.27", "TARGET_CODE").nonNullable().withType(ColumnTypeEnum.STRING, 500); 1314 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1315 .modifyColumn("20190722.28", "VALUESET_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1316 1317 version.onTable("TRM_CONCEPT") 1318 .renameColumn("20190722.29", "CODE", "CODEVAL", false, true); 1319 1320 1321 // TermValueSet 1322 version.startSectionWithMessage("Processing table: TRM_VALUESET"); 1323 version.addIdGenerator("20190722.30", "SEQ_VALUESET_PID"); 1324 Builder.BuilderAddTableByColumns termValueSetTable = version.addTableByColumns("20190722.31", "TRM_VALUESET", "PID"); 1325 termValueSetTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1326 termValueSetTable.addColumn("URL").nonNullable().type(ColumnTypeEnum.STRING, 200); 1327 termValueSetTable 1328 .addIndex("20190722.32", "IDX_VALUESET_URL") 1329 .unique(true) 1330 .withColumns("URL"); 1331 termValueSetTable.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1332 termValueSetTable 1333 .addForeignKey("20190722.33", "FK_TRMVALUESET_RES") 1334 .toColumn("RES_ID") 1335 .references("HFJ_RESOURCE", "RES_ID"); 1336 termValueSetTable.addColumn("NAME").nullable().type(ColumnTypeEnum.STRING, 200); 1337 1338 version.onTable("TRM_VALUESET") 1339 .renameColumn("20190722.34", "NAME", "VSNAME", true, true); 1340 version.onTable("TRM_VALUESET") 1341 .modifyColumn("20190722.35", "RES_ID").nullable().withType(ColumnTypeEnum.LONG); 1342 1343 Builder.BuilderWithTableName termValueSetTableChange = version.onTable("TRM_VALUESET"); 1344 termValueSetTableChange.addColumn("20190722.36", "EXPANSION_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 50); 1345 termValueSetTableChange 1346 .addIndex("20190722.37", "IDX_VALUESET_EXP_STATUS") 1347 .unique(false) 1348 .withColumns("EXPANSION_STATUS"); 1349 1350 // TermValueSetConcept 1351 version.startSectionWithMessage("Processing table: TRM_VALUESET_CONCEPT"); 1352 version.addIdGenerator("20190722.38", "SEQ_VALUESET_CONCEPT_PID"); 1353 Builder.BuilderAddTableByColumns termValueSetConceptTable = version.addTableByColumns("20190722.39", "TRM_VALUESET_CONCEPT", "PID"); 1354 termValueSetConceptTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1355 termValueSetConceptTable.addColumn("VALUESET_PID").nonNullable().type(ColumnTypeEnum.LONG); 1356 termValueSetConceptTable 1357 .addForeignKey("20190722.40", "FK_TRM_VALUESET_PID") 1358 .toColumn("VALUESET_PID") 1359 .references("TRM_VALUESET", "PID"); 1360 termValueSetConceptTable.addColumn("SYSTEM_URL").nonNullable().type(ColumnTypeEnum.STRING, 200); 1361 termValueSetConceptTable.addColumn("CODEVAL").nonNullable().type(ColumnTypeEnum.STRING, 500); 1362 termValueSetConceptTable.addColumn("DISPLAY").nullable().type(ColumnTypeEnum.STRING, 400); 1363 version.onTable("TRM_VALUESET_CONCEPT") 1364 .renameColumn("20190722.41", "CODE", "CODEVAL", true, true) 1365 .renameColumn("20190722.42", "SYSTEM", "SYSTEM_URL", true, true); 1366 1367 version.startSectionWithMessage("Processing table: TRM_VALUESET_CONCEPT, swapping index for unique constraint"); 1368 termValueSetConceptTable.dropIndex("20190801.1", "IDX_VALUESET_CONCEPT_CS_CD"); 1369 // This index has been renamed in later versions. As such, allowing failure here as some DBs disallow 1370 // multiple indexes referencing the same set of columns. 1371 termValueSetConceptTable 1372 .addIndex("20190801.2", "IDX_VS_CONCEPT_CS_CD") 1373 .unique(true) 1374 .withColumns("VALUESET_PID", "SYSTEM_URL", "CODEVAL").failureAllowed(); 1375 1376 // TermValueSetConceptDesignation 1377 version.startSectionWithMessage("Processing table: TRM_VALUESET_C_DESIGNATION"); 1378 version.addIdGenerator("20190801.3", "SEQ_VALUESET_C_DSGNTN_PID"); 1379 Builder.BuilderAddTableByColumns termValueSetConceptDesignationTable = version.addTableByColumns("20190801.4", "TRM_VALUESET_C_DESIGNATION", "PID"); 1380 termValueSetConceptDesignationTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1381 termValueSetConceptDesignationTable.addColumn("VALUESET_CONCEPT_PID").nonNullable().type(ColumnTypeEnum.LONG); 1382 termValueSetConceptDesignationTable 1383 .addForeignKey("20190801.5", "FK_TRM_VALUESET_CONCEPT_PID") 1384 .toColumn("VALUESET_CONCEPT_PID") 1385 .references("TRM_VALUESET_CONCEPT", "PID"); 1386 termValueSetConceptDesignationTable.addColumn("LANG").nullable().type(ColumnTypeEnum.STRING, 500); 1387 termValueSetConceptDesignationTable.addColumn("USE_SYSTEM").nullable().type(ColumnTypeEnum.STRING, 500); 1388 termValueSetConceptDesignationTable.addColumn("USE_CODE").nullable().type(ColumnTypeEnum.STRING, 500); 1389 termValueSetConceptDesignationTable.addColumn("USE_DISPLAY").nullable().type(ColumnTypeEnum.STRING, 500); 1390 termValueSetConceptDesignationTable.addColumn("VAL").nonNullable().type(ColumnTypeEnum.STRING, 500); 1391 1392 // This index turned out not to be needed so it is disabled 1393 termValueSetConceptDesignationTable 1394 .addIndex("20190801.6", "IDX_VALUESET_C_DSGNTN_VAL") 1395 .unique(false) 1396 .withColumns("VAL") 1397 .doNothing(); 1398 1399 // TermCodeSystemVersion 1400 version.startSectionWithMessage("Processing table: TRM_CODESYSTEM_VER"); 1401 Builder.BuilderWithTableName termCodeSystemVersionTable = version.onTable("TRM_CODESYSTEM_VER"); 1402 termCodeSystemVersionTable.addColumn("20190814.1", "CS_DISPLAY").nullable().type(ColumnTypeEnum.STRING, 200); 1403 1404 // ResourceReindexJobEntry 1405 version.addIdGenerator("20190814.2", "SEQ_RES_REINDEX_JOB"); 1406 Builder.BuilderAddTableByColumns reindex = version.addTableByColumns("20190814.3", "HFJ_RES_REINDEX_JOB", "PID"); 1407 reindex.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1408 reindex.addColumn("RES_TYPE").nullable().type(ColumnTypeEnum.STRING, 100); 1409 reindex.addColumn("UPDATE_THRESHOLD_HIGH").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1410 reindex.addColumn("JOB_DELETED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1411 reindex.addColumn("UPDATE_THRESHOLD_LOW").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1412 reindex.addColumn("SUSPENDED_UNTIL").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1413 reindex.addColumn("REINDEX_COUNT").nullable().type(ColumnTypeEnum.INT); 1414 1415 // Search 1416 version.onTable("HFJ_SEARCH") 1417 .addColumn("20190814.4", "SEARCH_DELETED").nullable().type(ColumnTypeEnum.BOOLEAN); 1418 version.onTable("HFJ_SEARCH") 1419 .modifyColumn("20190814.5", "SEARCH_LAST_RETURNED").nonNullable().withType(ColumnTypeEnum.DATE_TIMESTAMP); 1420 version.onTable("HFJ_SEARCH") 1421 .addColumn("20190814.6", "SEARCH_PARAM_MAP").nullable().type(ColumnTypeEnum.BLOB); 1422 version.onTable("HFJ_SEARCH") 1423 .modifyColumn("20190814.7", "SEARCH_UUID").nonNullable().withType(ColumnTypeEnum.STRING, 36); 1424 1425 version.onTable("HFJ_SEARCH_PARM").dropThisTable("20190814.8"); 1426 1427 // Make some columns non-nullable that were previously nullable - These are marked as failure allowed, since 1428 // SQL Server won't let us change nullability on columns with indexes pointing to them 1429 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20190814.9", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1430 version.onTable("HFJ_SPIDX_DATE").modifyColumn("20190814.10", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1431 version.onTable("HFJ_SPIDX_STRING").modifyColumn("20190814.11", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1432 version.onTable("HFJ_SPIDX_STRING").addColumn("20190814.12", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 1433 version.onTable("HFJ_SPIDX_STRING").addIndex("20190814.13", "IDX_SP_STRING_HASH_IDENT").unique(false).withColumns("HASH_IDENTITY"); 1434 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20190814.14", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1435 version.onTable("HFJ_SPIDX_QUANTITY").modifyColumn("20190814.15", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1436 version.onTable("HFJ_SPIDX_QUANTITY").dropColumn("20190814.16", "HASH_UNITS_AND_VALPREFIX"); 1437 version.onTable("HFJ_SPIDX_QUANTITY").dropColumn("20190814.17", "HASH_VALPREFIX"); 1438 version.onTable("HFJ_SPIDX_NUMBER").modifyColumn("20190814.18", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1439 version.onTable("HFJ_SPIDX_TOKEN").modifyColumn("20190814.19", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1440 version.onTable("HFJ_SPIDX_URI").modifyColumn("20190814.20", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1441 version.onTable("HFJ_SPIDX_URI").modifyColumn("20190814.21", "SP_URI").nullable().failureAllowed().withType(ColumnTypeEnum.STRING, 254); 1442 version.onTable("TRM_CODESYSTEM").modifyColumn("20190814.22", "CODE_SYSTEM_URI").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 200); 1443 version.onTable("TRM_CODESYSTEM").modifyColumn("20190814.23", "CS_NAME").nullable().failureAllowed().withType(ColumnTypeEnum.STRING, 200); 1444 version.onTable("TRM_CODESYSTEM_VER").modifyColumn("20190814.24", "CS_VERSION_ID").nullable().failureAllowed().withType(ColumnTypeEnum.STRING, 200); 1445 } 1446 1447 1448 private void init360() { // 20180918 - 20181112 1449 Builder version = forVersion(VersionEnum.V3_6_0); 1450 1451 // Resource Link 1452 Builder.BuilderWithTableName resourceLink = version.onTable("HFJ_RES_LINK"); 1453 version.startSectionWithMessage("Starting work on table: " + resourceLink.getTableName()); 1454 resourceLink 1455 .modifyColumn("20180929.1", "SRC_PATH") 1456 .nonNullable() 1457 .withType(ColumnTypeEnum.STRING, 200); 1458 1459 // Search 1460 Builder.BuilderWithTableName search = version.onTable("HFJ_SEARCH"); 1461 version.startSectionWithMessage("Starting work on table: " + search.getTableName()); 1462 search 1463 .addColumn("20181001.1", "OPTLOCK_VERSION") 1464 .nullable() 1465 .type(ColumnTypeEnum.INT); 1466 1467 version.addTableRawSql("20181104.1", "HFJ_RES_REINDEX_JOB") 1468 .addSql(DriverTypeEnum.MSSQL_2012, "create table HFJ_RES_REINDEX_JOB (PID bigint not null, JOB_DELETED bit not null, RES_TYPE varchar(255), SUSPENDED_UNTIL datetime2, UPDATE_THRESHOLD_HIGH datetime2 not null, UPDATE_THRESHOLD_LOW datetime2, primary key (PID))") 1469 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create table HFJ_RES_REINDEX_JOB (PID bigint not null, JOB_DELETED boolean not null, RES_TYPE varchar(255), SUSPENDED_UNTIL timestamp, UPDATE_THRESHOLD_HIGH timestamp not null, UPDATE_THRESHOLD_LOW timestamp, primary key (PID))") 1470 .addSql(DriverTypeEnum.MARIADB_10_1, "create table HFJ_RES_REINDEX_JOB (PID bigint not null, JOB_DELETED bit not null, RES_TYPE varchar(255), SUSPENDED_UNTIL datetime(6), UPDATE_THRESHOLD_HIGH datetime(6) not null, UPDATE_THRESHOLD_LOW datetime(6), primary key (PID))") 1471 .addSql(DriverTypeEnum.POSTGRES_9_4, "create table HFJ_RES_REINDEX_JOB (PID int8 not null, JOB_DELETED boolean not null, RES_TYPE varchar(255), SUSPENDED_UNTIL timestamp, UPDATE_THRESHOLD_HIGH timestamp not null, UPDATE_THRESHOLD_LOW timestamp, primary key (PID))") 1472 .addSql(DriverTypeEnum.MYSQL_5_7, " create table HFJ_RES_REINDEX_JOB (PID bigint not null, JOB_DELETED bit not null, RES_TYPE varchar(255), SUSPENDED_UNTIL datetime(6), UPDATE_THRESHOLD_HIGH datetime(6) not null, UPDATE_THRESHOLD_LOW datetime(6), primary key (PID))") 1473 .addSql(DriverTypeEnum.ORACLE_12C, "create table HFJ_RES_REINDEX_JOB (PID number(19,0) not null, JOB_DELETED number(1,0) not null, RES_TYPE varchar2(255 char), SUSPENDED_UNTIL timestamp, UPDATE_THRESHOLD_HIGH timestamp not null, UPDATE_THRESHOLD_LOW timestamp, primary key (PID))"); 1474 1475 version.onTable("TRM_CONCEPT_DESIG").addColumn("20181104.2", "CS_VER_PID").nullable().type(ColumnTypeEnum.LONG); 1476 version.onTable("TRM_CONCEPT_DESIG").addForeignKey("20181104.3", "FK_CONCEPTDESIG_CSV").toColumn("CS_VER_PID").references("TRM_CODESYSTEM_VER", "PID"); 1477 1478 version.onTable("TRM_CONCEPT_PROPERTY").addColumn("20181104.4", "CS_VER_PID").nullable().type(ColumnTypeEnum.LONG); 1479 version.onTable("TRM_CONCEPT_PROPERTY").addForeignKey("20181104.5", "FK_CONCEPTPROP_CSV").toColumn("CS_VER_PID").references("TRM_CODESYSTEM_VER", "PID"); 1480 1481 version.onTable("TRM_CONCEPT").addColumn("20181104.6", "PARENT_PIDS").nullable().type(ColumnTypeEnum.CLOB); 1482 1483 } 1484 1485 private void init350() { // 20180601 - 20180917 1486 Builder version = forVersion(VersionEnum.V3_5_0); 1487 1488 // Forced ID changes 1489 Builder.BuilderWithTableName forcedId = version.onTable("HFJ_FORCED_ID"); 1490 version.startSectionWithMessage("Starting work on table: " + forcedId.getTableName()); 1491 1492 forcedId 1493 .dropIndex("20180827.1", "IDX_FORCEDID_TYPE_FORCEDID"); 1494 forcedId 1495 .dropIndex("20180827.2", "IDX_FORCEDID_TYPE_RESID"); 1496 1497 forcedId 1498 .addIndex("20180827.3", "IDX_FORCEDID_TYPE_FID") 1499 .unique(true) 1500 .withColumns("RESOURCE_TYPE", "FORCED_ID"); 1501 1502 // Indexes - Coords 1503 Builder.BuilderWithTableName spidxCoords = version.onTable("HFJ_SPIDX_COORDS"); 1504 version.startSectionWithMessage("Starting work on table: " + spidxCoords.getTableName()); 1505 spidxCoords 1506 .addColumn("20180903.1", "HASH_IDENTITY") 1507 .nullable() 1508 .type(ColumnTypeEnum.LONG); 1509 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1510 spidxCoords 1511 .dropIndex("20180903.2", "IDX_SP_COORDS"); 1512 spidxCoords 1513 .addIndex("20180903.4", "IDX_SP_COORDS_HASH") 1514 .unique(false) 1515 .withColumns("HASH_IDENTITY", "SP_LATITUDE", "SP_LONGITUDE"); 1516 spidxCoords 1517 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.5") 1518 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1519 .setColumnName("HASH_IDENTITY") 1520 ); 1521 } 1522 1523 // Indexes - Date 1524 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 1525 version.startSectionWithMessage("Starting work on table: " + spidxDate.getTableName()); 1526 spidxDate 1527 .addColumn("20180903.6", "HASH_IDENTITY") 1528 .nullable() 1529 .type(ColumnTypeEnum.LONG); 1530 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1531 spidxDate 1532 .dropIndex("20180903.7", "IDX_SP_TOKEN"); 1533 spidxDate 1534 .addIndex("20180903.8", "IDX_SP_DATE_HASH") 1535 .unique(false) 1536 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH") 1537 .doNothing(); 1538 spidxDate 1539 .dropIndex("20180903.9", "IDX_SP_DATE"); 1540 spidxDate 1541 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.10") 1542 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1543 .setColumnName("HASH_IDENTITY") 1544 ); 1545 } 1546 1547 // Indexes - Number 1548 Builder.BuilderWithTableName spidxNumber = version.onTable("HFJ_SPIDX_NUMBER"); 1549 version.startSectionWithMessage("Starting work on table: " + spidxNumber.getTableName()); 1550 spidxNumber 1551 .addColumn("20180903.11", "HASH_IDENTITY") 1552 .nullable() 1553 .type(ColumnTypeEnum.LONG); 1554 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1555 spidxNumber 1556 .dropIndex("20180903.12", "IDX_SP_NUMBER"); 1557 spidxNumber 1558 .addIndex("20180903.13", "IDX_SP_NUMBER_HASH_VAL") 1559 .unique(false) 1560 .withColumns("HASH_IDENTITY", "SP_VALUE") 1561 .doNothing(); 1562 spidxNumber 1563 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.14") 1564 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1565 .setColumnName("HASH_IDENTITY") 1566 ); 1567 } 1568 1569 // Indexes - Quantity 1570 Builder.BuilderWithTableName spidxQuantity = version.onTable("HFJ_SPIDX_QUANTITY"); 1571 version.startSectionWithMessage("Starting work on table: " + spidxQuantity.getTableName()); 1572 spidxQuantity 1573 .addColumn("20180903.15", "HASH_IDENTITY") 1574 .nullable() 1575 .type(ColumnTypeEnum.LONG); 1576 spidxQuantity 1577 .addColumn("20180903.16", "HASH_IDENTITY_SYS_UNITS") 1578 .nullable() 1579 .type(ColumnTypeEnum.LONG); 1580 spidxQuantity 1581 .addColumn("20180903.17", "HASH_IDENTITY_AND_UNITS") 1582 .nullable() 1583 .type(ColumnTypeEnum.LONG); 1584 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1585 spidxQuantity 1586 .dropIndex("20180903.18", "IDX_SP_QUANTITY"); 1587 spidxQuantity 1588 .addIndex("20180903.19", "IDX_SP_QUANTITY_HASH") 1589 .unique(false) 1590 .withColumns("HASH_IDENTITY", "SP_VALUE"); 1591 spidxQuantity 1592 .addIndex("20180903.20", "IDX_SP_QUANTITY_HASH_UN") 1593 .unique(false) 1594 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE"); 1595 spidxQuantity 1596 .addIndex("20180903.21", "IDX_SP_QUANTITY_HASH_SYSUN") 1597 .unique(false) 1598 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE"); 1599 spidxQuantity 1600 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.22") 1601 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1602 .addCalculator("HASH_IDENTITY_AND_UNITS", t -> ResourceIndexedSearchParamQuantity.calculateHashUnits(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_UNITS"))) 1603 .addCalculator("HASH_IDENTITY_SYS_UNITS", t -> ResourceIndexedSearchParamQuantity.calculateHashSystemAndUnits(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_SYSTEM"), t.getString("SP_UNITS"))) 1604 .setColumnName("HASH_IDENTITY") 1605 ); 1606 } 1607 1608 // Indexes - String 1609 Builder.BuilderWithTableName spidxString = version.onTable("HFJ_SPIDX_STRING"); 1610 version.startSectionWithMessage("Starting work on table: " + spidxString.getTableName()); 1611 spidxString 1612 .addColumn("20180903.23", "HASH_NORM_PREFIX") 1613 .nullable() 1614 .type(ColumnTypeEnum.LONG); 1615 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1616 spidxString 1617 .dropIndex("20180903.24", "IDX_SP_STRING"); 1618 spidxString 1619 .addIndex("20180903.25", "IDX_SP_STRING_HASH_NRM") 1620 .unique(false) 1621 .withColumns("HASH_NORM_PREFIX", "SP_VALUE_NORMALIZED"); 1622 spidxString 1623 .addColumn("20180903.26", "HASH_EXACT") 1624 .nullable() 1625 .type(ColumnTypeEnum.LONG); 1626 spidxString 1627 .addIndex("20180903.27", "IDX_SP_STRING_HASH_EXCT") 1628 .unique(false) 1629 .withColumns("HASH_EXACT"); 1630 spidxString 1631 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.28") 1632 .setColumnName("HASH_NORM_PREFIX") 1633 .addCalculator("HASH_NORM_PREFIX", t -> ResourceIndexedSearchParamString.calculateHashNormalized(new PartitionSettings(), RequestPartitionId.defaultPartition(), new ModelConfig(), t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_VALUE_NORMALIZED"))) 1634 .addCalculator("HASH_EXACT", t -> ResourceIndexedSearchParamString.calculateHashExact(new PartitionSettings(), (ca.uhn.fhir.jpa.model.entity.PartitionablePartitionId) null, t.getResourceType(), t.getParamName(), t.getString("SP_VALUE_EXACT"))) 1635 ); 1636 } 1637 1638 // Indexes - Token 1639 Builder.BuilderWithTableName spidxToken = version.onTable("HFJ_SPIDX_TOKEN"); 1640 version.startSectionWithMessage("Starting work on table: " + spidxToken.getTableName()); 1641 spidxToken 1642 .addColumn("20180903.29", "HASH_IDENTITY") 1643 .nullable() 1644 .type(ColumnTypeEnum.LONG); 1645 spidxToken 1646 .addColumn("20180903.30", "HASH_SYS") 1647 .nullable() 1648 .type(ColumnTypeEnum.LONG); 1649 spidxToken 1650 .addColumn("20180903.31", "HASH_SYS_AND_VALUE") 1651 .nullable() 1652 .type(ColumnTypeEnum.LONG); 1653 spidxToken 1654 .addColumn("20180903.32", "HASH_VALUE") 1655 .nullable() 1656 .type(ColumnTypeEnum.LONG); 1657 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1658 spidxToken 1659 .dropIndex("20180903.33", "IDX_SP_TOKEN"); 1660 spidxToken 1661 .dropIndex("20180903.34", "IDX_SP_TOKEN_UNQUAL"); 1662 spidxToken 1663 .addIndex("20180903.35", "IDX_SP_TOKEN_HASH") 1664 .unique(false) 1665 .withColumns("HASH_IDENTITY") 1666 .doNothing(); 1667 spidxToken 1668 .addIndex("20180903.36", "IDX_SP_TOKEN_HASH_S") 1669 .unique(false) 1670 .withColumns("HASH_SYS") 1671 .doNothing(); 1672 spidxToken 1673 .addIndex("20180903.37", "IDX_SP_TOKEN_HASH_SV") 1674 .unique(false) 1675 .withColumns("HASH_SYS_AND_VALUE") 1676 .doNothing(); 1677 spidxToken 1678 .addIndex("20180903.38", "IDX_SP_TOKEN_HASH_V") 1679 .unique(false) 1680 .withColumns("HASH_VALUE") 1681 .doNothing(); 1682 spidxToken 1683 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.39") 1684 .setColumnName("HASH_IDENTITY") 1685 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1686 .addCalculator("HASH_SYS", t -> ResourceIndexedSearchParamToken.calculateHashSystem(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getParamName(), t.getString("SP_SYSTEM"))) 1687 .addCalculator("HASH_SYS_AND_VALUE", t -> ResourceIndexedSearchParamToken.calculateHashSystemAndValue(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getParamName(), t.getString("SP_SYSTEM"), t.getString("SP_VALUE"))) 1688 .addCalculator("HASH_VALUE", t -> ResourceIndexedSearchParamToken.calculateHashValue(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getParamName(), t.getString("SP_VALUE"))) 1689 ); 1690 } 1691 1692 // Indexes - URI 1693 Builder.BuilderWithTableName spidxUri = version.onTable("HFJ_SPIDX_URI"); 1694 version.startSectionWithMessage("Starting work on table: " + spidxUri.getTableName()); 1695 spidxUri 1696 .addColumn("20180903.40", "HASH_IDENTITY") 1697 .nullable() 1698 .type(ColumnTypeEnum.LONG); 1699 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1700 spidxUri 1701 .addIndex("20180903.41", "IDX_SP_URI_HASH_IDENTITY") 1702 .unique(false) 1703 .withColumns("HASH_IDENTITY", "SP_URI"); 1704 spidxUri 1705 .addColumn("20180903.42", "HASH_URI") 1706 .nullable() 1707 .type(ColumnTypeEnum.LONG); 1708 spidxUri 1709 .addIndex("20180903.43", "IDX_SP_URI_HASH_URI") 1710 .unique(false) 1711 .withColumns("HASH_URI"); 1712 spidxUri 1713 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.44") 1714 .setColumnName("HASH_IDENTITY") 1715 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), (RequestPartitionId) null, t.getResourceType(), t.getString("SP_NAME"))) 1716 .addCalculator("HASH_URI", t -> ResourceIndexedSearchParamUri.calculateHashUri(new PartitionSettings(), (RequestPartitionId) null, t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_URI"))) 1717 ); 1718 } 1719 1720 // Search Parameter Presence 1721 Builder.BuilderWithTableName spp = version.onTable("HFJ_RES_PARAM_PRESENT"); 1722 version.startSectionWithMessage("Starting work on table: " + spp.getTableName()); 1723 spp.dropIndex("20180903.45", "IDX_RESPARMPRESENT_SPID_RESID"); 1724 spp 1725 .addColumn("20180903.46", "HASH_PRESENCE") 1726 .nullable() 1727 .type(ColumnTypeEnum.LONG); 1728 spp 1729 .addIndex("20180903.47", "IDX_RESPARMPRESENT_HASHPRES") 1730 .unique(false) 1731 .withColumns("HASH_PRESENCE"); 1732 1733 ArbitrarySqlTask consolidateSearchParamPresenceIndexesTask = new ArbitrarySqlTask(VersionEnum.V3_5_0, "20180903.48", "HFJ_SEARCH_PARM", "Consolidate search parameter presence indexes"); 1734 consolidateSearchParamPresenceIndexesTask.setExecuteOnlyIfTableExists("HFJ_SEARCH_PARM"); 1735 consolidateSearchParamPresenceIndexesTask.setBatchSize(1); 1736 1737 String sql = "SELECT " + 1738 "HFJ_SEARCH_PARM.RES_TYPE RES_TYPE, HFJ_SEARCH_PARM.PARAM_NAME PARAM_NAME, " + 1739 "HFJ_RES_PARAM_PRESENT.PID PID, HFJ_RES_PARAM_PRESENT.SP_ID SP_ID, HFJ_RES_PARAM_PRESENT.SP_PRESENT SP_PRESENT, HFJ_RES_PARAM_PRESENT.HASH_PRESENCE HASH_PRESENCE " + 1740 "from HFJ_RES_PARAM_PRESENT " + 1741 "join HFJ_SEARCH_PARM ON (HFJ_SEARCH_PARM.PID = HFJ_RES_PARAM_PRESENT.SP_ID) " + 1742 "where HFJ_RES_PARAM_PRESENT.HASH_PRESENCE is null"; 1743 consolidateSearchParamPresenceIndexesTask.addExecuteOnlyIfColumnExists("HFJ_RES_PARAM_PRESENT", "SP_ID"); 1744 consolidateSearchParamPresenceIndexesTask.addQuery(sql, ArbitrarySqlTask.QueryModeEnum.BATCH_UNTIL_NO_MORE, t -> { 1745 Number pid = (Number) t.get("PID"); 1746 Boolean present = columnToBoolean(t.get("SP_PRESENT")); 1747 String resType = (String) t.get("RES_TYPE"); 1748 String paramName = (String) t.get("PARAM_NAME"); 1749 Long hash = SearchParamPresentEntity.calculateHashPresence(new PartitionSettings(), (RequestPartitionId) null, resType, paramName, present); 1750 consolidateSearchParamPresenceIndexesTask.executeSql("HFJ_RES_PARAM_PRESENT", "update HFJ_RES_PARAM_PRESENT set HASH_PRESENCE = ? where PID = ?", hash, pid); 1751 }); 1752 version.addTask(consolidateSearchParamPresenceIndexesTask); 1753 1754 // SP_ID is no longer needed 1755 spp.dropColumn("20180903.49", "SP_ID"); 1756 1757 // Concept 1758 Builder.BuilderWithTableName trmConcept = version.onTable("TRM_CONCEPT"); 1759 version.startSectionWithMessage("Starting work on table: " + trmConcept.getTableName()); 1760 trmConcept 1761 .addColumn("20180903.50", "CONCEPT_UPDATED") 1762 .nullable() 1763 .type(ColumnTypeEnum.DATE_TIMESTAMP); 1764 trmConcept 1765 .addIndex("20180903.51", "IDX_CONCEPT_UPDATED") 1766 .unique(false) 1767 .withColumns("CONCEPT_UPDATED"); 1768 trmConcept 1769 .modifyColumn("20180903.52", "CODE") 1770 .nonNullable() 1771 .withType(ColumnTypeEnum.STRING, 500); 1772 1773 // Concept Designation 1774 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_DESIG"); 1775 version 1776 .addTableRawSql("20180907.1", "TRM_CONCEPT_DESIG") 1777 .addSql(DriverTypeEnum.H2_EMBEDDED, "create table TRM_CONCEPT_DESIG (PID bigint not null, LANG varchar(500), USE_CODE varchar(500), USE_DISPLAY varchar(500), USE_SYSTEM varchar(500), VAL varchar(500) not null, CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1778 .addSql(DriverTypeEnum.H2_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1779 .addSql(DriverTypeEnum.H2_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 1780 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create table TRM_CONCEPT_DESIG (PID bigint not null, LANG varchar(500), USE_CODE varchar(500), USE_DISPLAY varchar(500), USE_SYSTEM varchar(500), VAL varchar(500) not null, CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1781 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1782 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 1783 .addSql(DriverTypeEnum.MYSQL_5_7, "create table TRM_CONCEPT_DESIG (PID bigint not null, LANG varchar(500), USE_CODE varchar(500), USE_DISPLAY varchar(500), USE_SYSTEM varchar(500), VAL varchar(500) not null, CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID)) ENGINE=InnoDB") 1784 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 1785 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 1786 .addSql(DriverTypeEnum.MARIADB_10_1, "create table TRM_CONCEPT_DESIG (PID bigint not null, LANG varchar(500), USE_CODE varchar(500), USE_DISPLAY varchar(500), USE_SYSTEM varchar(500), VAL varchar(500) not null, CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1787 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 1788 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 1789 .addSql(DriverTypeEnum.ORACLE_12C, "create table TRM_CONCEPT_DESIG (PID number(19,0) not null, LANG varchar2(500 char), USE_CODE varchar2(500 char), USE_DISPLAY varchar2(500 char), USE_SYSTEM varchar2(500 char), VAL varchar2(500 char) not null, CS_VER_PID number(19,0), CONCEPT_PID number(19,0), primary key (PID))") 1790 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1791 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 1792 .addSql(DriverTypeEnum.POSTGRES_9_4, "create table TRM_CONCEPT_DESIG (PID int8 not null, LANG varchar(500), USE_CODE varchar(500), USE_DISPLAY varchar(500), USE_SYSTEM varchar(500), VAL varchar(500) not null, CS_VER_PID int8, CONCEPT_PID int8, primary key (PID))") 1793 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1794 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 1795 .addSql(DriverTypeEnum.MSSQL_2012, "create table TRM_CONCEPT_DESIG (PID bigint not null, LANG varchar(500), USE_CODE varchar(500), USE_DISPLAY varchar(500), USE_SYSTEM varchar(500), VAL varchar(500) not null, CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1796 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1797 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT"); 1798 1799 // Concept Property 1800 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_PROPERTY"); 1801 version 1802 .addTableRawSql("20180907.2", "TRM_CONCEPT_PROPERTY") 1803 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create table TRM_CONCEPT_PROPERTY (PID bigint not null, PROP_CODESYSTEM varchar(500), PROP_DISPLAY varchar(500), PROP_KEY varchar(500) not null, PROP_TYPE integer not null, PROP_VAL varchar(500), CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1804 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1805 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 1806 .addSql(DriverTypeEnum.MARIADB_10_1, "create table TRM_CONCEPT_PROPERTY (PID bigint not null, PROP_CODESYSTEM varchar(500), PROP_DISPLAY varchar(500), PROP_KEY varchar(500) not null, PROP_TYPE integer not null, PROP_VAL varchar(500), CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1807 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 1808 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 1809 .addSql(DriverTypeEnum.MYSQL_5_7, "create table TRM_CONCEPT_PROPERTY (PID bigint not null, PROP_CODESYSTEM varchar(500), PROP_DISPLAY varchar(500), PROP_KEY varchar(500) not null, PROP_TYPE integer not null, PROP_VAL varchar(500), CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1810 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 1811 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 1812 .addSql(DriverTypeEnum.ORACLE_12C, "create table TRM_CONCEPT_PROPERTY (PID number(19,0) not null, PROP_CODESYSTEM varchar2(500 char), PROP_DISPLAY varchar2(500 char), PROP_KEY varchar2(500 char) not null, PROP_TYPE number(10,0) not null, PROP_VAL varchar2(500 char), CS_VER_PID number(19,0), CONCEPT_PID number(19,0), primary key (PID))") 1813 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1814 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 1815 .addSql(DriverTypeEnum.POSTGRES_9_4, "create table TRM_CONCEPT_PROPERTY (PID int8 not null, PROP_CODESYSTEM varchar(500), PROP_DISPLAY varchar(500), PROP_KEY varchar(500) not null, PROP_TYPE int4 not null, PROP_VAL varchar(500), CS_VER_PID int8, CONCEPT_PID int8, primary key (PID))") 1816 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1817 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 1818 .addSql(DriverTypeEnum.MSSQL_2012, "create table TRM_CONCEPT_PROPERTY (PID bigint not null, PROP_CODESYSTEM varchar(500), PROP_DISPLAY varchar(500), PROP_KEY varchar(500) not null, PROP_TYPE int not null, PROP_VAL varchar(500), CS_VER_PID bigint, CONCEPT_PID bigint, primary key (PID))") 1819 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 1820 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT"); 1821 1822 // Concept Map - Map 1823 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP"); 1824 version 1825 .addTableRawSql("20180907.3", "TRM_CONCEPT_MAP") 1826 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create table TRM_CONCEPT_MAP (PID bigint not null, RES_ID bigint, SOURCE_URL varchar(200), TARGET_URL varchar(200), URL varchar(200) not null, primary key (PID))") 1827 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 1828 .addSql(DriverTypeEnum.MYSQL_5_7, "create table TRM_CONCEPT_MAP (PID bigint not null, RES_ID bigint, SOURCE_URL varchar(200), TARGET_URL varchar(200), URL varchar(200) not null, primary key (PID))") 1829 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 1830 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE (RES_ID)") 1831 .addSql(DriverTypeEnum.ORACLE_12C, "create table TRM_CONCEPT_MAP (PID number(19,0) not null, RES_ID number(19,0), SOURCE_URL varchar2(200 char), TARGET_URL varchar2(200 char), URL varchar2(200 char) not null, primary key (PID))") 1832 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 1833 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 1834 .addSql(DriverTypeEnum.POSTGRES_9_4, "create table TRM_CONCEPT_MAP (PID int8 not null, RES_ID int8, SOURCE_URL varchar(200), TARGET_URL varchar(200), URL varchar(200) not null, primary key (PID))") 1835 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 1836 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 1837 .addSql(DriverTypeEnum.MSSQL_2012, "create table TRM_CONCEPT_MAP (PID bigint not null, RES_ID bigint, SOURCE_URL varchar(200), TARGET_URL varchar(200), URL varchar(200) not null, primary key (PID))") 1838 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 1839 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 1840 .addSql(DriverTypeEnum.MARIADB_10_1, "create table TRM_CONCEPT_MAP (PID bigint not null, RES_ID bigint, SOURCE_URL varchar(200), TARGET_URL varchar(200), URL varchar(200) not null, primary key (PID))") 1841 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE (RES_ID)") 1842 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)"); 1843 1844 // Concept Map - Group 1845 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GROUP"); 1846 version 1847 .addTableRawSql("20180907.4", "TRM_CONCEPT_MAP_GROUP") 1848 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create table TRM_CONCEPT_MAP_GROUP (PID bigint not null, myConceptMapUrl varchar(255), SOURCE_URL varchar(200) not null, mySourceValueSet varchar(255), SOURCE_VERSION varchar(100), TARGET_URL varchar(200) not null, myTargetValueSet varchar(255), TARGET_VERSION varchar(100), CONCEPT_MAP_PID bigint not null, primary key (PID))") 1849 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 1850 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create unique index IDX_CONCEPT_MAP_URL on TRM_CONCEPT_MAP (URL)") 1851 .addSql(DriverTypeEnum.ORACLE_12C, "create table TRM_CONCEPT_MAP_GROUP (PID number(19,0) not null, myConceptMapUrl varchar2(255 char), SOURCE_URL varchar2(200 char) not null, mySourceValueSet varchar2(255 char), SOURCE_VERSION varchar2(100 char), TARGET_URL varchar2(200 char) not null, myTargetValueSet varchar2(255 char), TARGET_VERSION varchar2(100 char), CONCEPT_MAP_PID number(19,0) not null, primary key (PID))") 1852 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 1853 .addSql(DriverTypeEnum.MARIADB_10_1, "create table TRM_CONCEPT_MAP_GROUP (PID bigint not null, myConceptMapUrl varchar(255), SOURCE_URL varchar(200) not null, mySourceValueSet varchar(255), SOURCE_VERSION varchar(100), TARGET_URL varchar(200) not null, myTargetValueSet varchar(255), TARGET_VERSION varchar(100), CONCEPT_MAP_PID bigint not null, primary key (PID))") 1854 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP (PID)") 1855 .addSql(DriverTypeEnum.MYSQL_5_7, "create table TRM_CONCEPT_MAP_GROUP (PID bigint not null, myConceptMapUrl varchar(255), SOURCE_URL varchar(200) not null, mySourceValueSet varchar(255), SOURCE_VERSION varchar(100), TARGET_URL varchar(200) not null, myTargetValueSet varchar(255), TARGET_VERSION varchar(100), CONCEPT_MAP_PID bigint not null, primary key (PID))") 1856 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP (PID)") 1857 .addSql(DriverTypeEnum.MSSQL_2012, "create table TRM_CONCEPT_MAP_GROUP (PID bigint not null, myConceptMapUrl varchar(255), SOURCE_URL varchar(200) not null, mySourceValueSet varchar(255), SOURCE_VERSION varchar(100), TARGET_URL varchar(200) not null, myTargetValueSet varchar(255), TARGET_VERSION varchar(100), CONCEPT_MAP_PID bigint not null, primary key (PID))") 1858 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 1859 .addSql(DriverTypeEnum.POSTGRES_9_4, "create table TRM_CONCEPT_MAP_GROUP (PID int8 not null, myConceptMapUrl varchar(255), SOURCE_URL varchar(200) not null, mySourceValueSet varchar(255), SOURCE_VERSION varchar(100), TARGET_URL varchar(200) not null, myTargetValueSet varchar(255), TARGET_VERSION varchar(100), CONCEPT_MAP_PID int8 not null, primary key (PID))") 1860 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP"); 1861 1862 // Concept Map - Group Element 1863 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GRP_ELEMENT"); 1864 version 1865 .addTableRawSql("20180907.5", "TRM_CONCEPT_MAP_GRP_ELEMENT") 1866 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create table TRM_CONCEPT_MAP_GRP_ELEMENT (PID bigint not null, SOURCE_CODE varchar(500) not null, myConceptMapUrl varchar(255), SOURCE_DISPLAY varchar(400), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GROUP_PID bigint not null, primary key (PID))") 1867 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 1868 .addSql(DriverTypeEnum.MARIADB_10_1, "create table TRM_CONCEPT_MAP_GRP_ELEMENT (PID bigint not null, SOURCE_CODE varchar(500) not null, myConceptMapUrl varchar(255), SOURCE_DISPLAY varchar(400), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GROUP_PID bigint not null, primary key (PID))") 1869 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP (PID)") 1870 .addSql(DriverTypeEnum.MARIADB_10_1, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 1871 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 1872 .addSql(DriverTypeEnum.MYSQL_5_7, "create table TRM_CONCEPT_MAP_GRP_ELEMENT (PID bigint not null, SOURCE_CODE varchar(500) not null, myConceptMapUrl varchar(255), SOURCE_DISPLAY varchar(400), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GROUP_PID bigint not null, primary key (PID))") 1873 .addSql(DriverTypeEnum.MYSQL_5_7, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 1874 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP (PID)") 1875 .addSql(DriverTypeEnum.POSTGRES_9_4, "create table TRM_CONCEPT_MAP_GRP_ELEMENT (PID int8 not null, SOURCE_CODE varchar(500) not null, myConceptMapUrl varchar(255), SOURCE_DISPLAY varchar(400), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GROUP_PID int8 not null, primary key (PID))") 1876 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 1877 .addSql(DriverTypeEnum.POSTGRES_9_4, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 1878 .addSql(DriverTypeEnum.ORACLE_12C, "create table TRM_CONCEPT_MAP_GRP_ELEMENT (PID number(19,0) not null, SOURCE_CODE varchar2(500 char) not null, myConceptMapUrl varchar2(255 char), SOURCE_DISPLAY varchar2(400 char), mySystem varchar2(255 char), mySystemVersion varchar2(255 char), myValueSet varchar2(255 char), CONCEPT_MAP_GROUP_PID number(19,0) not null, primary key (PID))") 1879 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 1880 .addSql(DriverTypeEnum.ORACLE_12C, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 1881 .addSql(DriverTypeEnum.MSSQL_2012, "create table TRM_CONCEPT_MAP_GRP_ELEMENT (PID bigint not null, SOURCE_CODE varchar(500) not null, myConceptMapUrl varchar(255), SOURCE_DISPLAY varchar(400), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GROUP_PID bigint not null, primary key (PID))") 1882 .addSql(DriverTypeEnum.MSSQL_2012, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 1883 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP"); 1884 1885 // Concept Map - Group Element Target 1886 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GRP_ELM_TGT"); 1887 version 1888 .addTableRawSql("20180907.6", "TRM_CONCEPT_MAP_GRP_ELM_TGT") 1889 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create table TRM_CONCEPT_MAP_GRP_ELM_TGT (PID bigint not null, TARGET_CODE varchar(500) not null, myConceptMapUrl varchar(255), TARGET_DISPLAY varchar(400), TARGET_EQUIVALENCE varchar(50), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GRP_ELM_PID bigint not null, primary key (PID))") 1890 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_MAP_GRP_ELM_TGT add constraint FK_TCMGETARGET_ELEMENT foreign key (CONCEPT_MAP_GRP_ELM_PID) references TRM_CONCEPT_MAP_GRP_ELEMENT") 1891 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 1892 .addSql(DriverTypeEnum.MARIADB_10_1, "create table TRM_CONCEPT_MAP_GRP_ELM_TGT (PID bigint not null, TARGET_CODE varchar(500) not null, myConceptMapUrl varchar(255), TARGET_DISPLAY varchar(400), TARGET_EQUIVALENCE varchar(50), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GRP_ELM_PID bigint not null, primary key (PID))") 1893 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP_GRP_ELM_TGT add constraint FK_TCMGETARGET_ELEMENT foreign key (CONCEPT_MAP_GRP_ELM_PID) references TRM_CONCEPT_MAP_GRP_ELEMENT (PID)") 1894 .addSql(DriverTypeEnum.MARIADB_10_1, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 1895 .addSql(DriverTypeEnum.MYSQL_5_7, "create table TRM_CONCEPT_MAP_GRP_ELM_TGT (PID bigint not null, TARGET_CODE varchar(500) not null, myConceptMapUrl varchar(255), TARGET_DISPLAY varchar(400), TARGET_EQUIVALENCE varchar(50), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GRP_ELM_PID bigint not null, primary key (PID))") 1896 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP_GRP_ELM_TGT add constraint FK_TCMGETARGET_ELEMENT foreign key (CONCEPT_MAP_GRP_ELM_PID) references TRM_CONCEPT_MAP_GRP_ELEMENT (PID)") 1897 .addSql(DriverTypeEnum.MYSQL_5_7, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 1898 .addSql(DriverTypeEnum.ORACLE_12C, "create table TRM_CONCEPT_MAP_GRP_ELM_TGT (PID number(19,0) not null, TARGET_CODE varchar2(500 char) not null, myConceptMapUrl varchar2(255 char), TARGET_DISPLAY varchar2(400 char), TARGET_EQUIVALENCE varchar2(50 char), mySystem varchar2(255 char), mySystemVersion varchar2(255 char), myValueSet varchar2(255 char), CONCEPT_MAP_GRP_ELM_PID number(19,0) not null, primary key (PID))") 1899 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP_GRP_ELM_TGT add constraint FK_TCMGETARGET_ELEMENT foreign key (CONCEPT_MAP_GRP_ELM_PID) references TRM_CONCEPT_MAP_GRP_ELEMENT") 1900 .addSql(DriverTypeEnum.ORACLE_12C, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 1901 .addSql(DriverTypeEnum.POSTGRES_9_4, "create table TRM_CONCEPT_MAP_GRP_ELM_TGT (PID int8 not null, TARGET_CODE varchar(500) not null, myConceptMapUrl varchar(255), TARGET_DISPLAY varchar(400), TARGET_EQUIVALENCE varchar(50), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GRP_ELM_PID int8 not null, primary key (PID))") 1902 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP_GRP_ELM_TGT add constraint FK_TCMGETARGET_ELEMENT foreign key (CONCEPT_MAP_GRP_ELM_PID) references TRM_CONCEPT_MAP_GRP_ELEMENT") 1903 .addSql(DriverTypeEnum.POSTGRES_9_4, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 1904 .addSql(DriverTypeEnum.MSSQL_2012, "create table TRM_CONCEPT_MAP_GRP_ELM_TGT (PID bigint not null, TARGET_CODE varchar(500) not null, myConceptMapUrl varchar(255), TARGET_DISPLAY varchar(400), TARGET_EQUIVALENCE varchar(50), mySystem varchar(255), mySystemVersion varchar(255), myValueSet varchar(255), CONCEPT_MAP_GRP_ELM_PID bigint not null, primary key (PID))") 1905 .addSql(DriverTypeEnum.MSSQL_2012, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 1906 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP_GRP_ELM_TGT add constraint FK_TCMGETARGET_ELEMENT foreign key (CONCEPT_MAP_GRP_ELM_PID) references TRM_CONCEPT_MAP_GRP_ELEMENT"); 1907 1908 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").modifyColumn("20180907.7", "IDX_STRING").nonNullable().withType(ColumnTypeEnum.STRING, 200); 1909 1910 1911 } 1912 1913 private Boolean columnToBoolean(Object theValue) { 1914 if (theValue == null) { 1915 return null; 1916 } 1917 if (theValue instanceof Boolean) { 1918 return (Boolean) theValue; 1919 } 1920 1921 long longValue = ((Number) theValue).longValue(); 1922 return longValue == 1L; 1923 } 1924 1925 private void init340() { // 20180401 - 20180528 1926 Builder version = forVersion(VersionEnum.V3_4_0); 1927 1928 // CodeSystem Version 1929 Builder.BuilderWithTableName resourceLink = version.onTable("TRM_CODESYSTEM_VER"); 1930 version.startSectionWithMessage("Starting work on table: " + resourceLink.getTableName()); 1931 resourceLink 1932 .dropIndex("20180401.1", "IDX_CSV_RESOURCEPID_AND_VER"); 1933 resourceLink 1934 .dropColumn("20180401.2", "RES_VERSION_ID"); 1935 resourceLink 1936 .addColumn("20180401.3", "CS_VERSION_ID") 1937 .nullable() 1938 .type(ColumnTypeEnum.STRING, 255); 1939 resourceLink 1940 .addColumn("20180401.4", "CODESYSTEM_PID") 1941 .nullable() 1942 .type(ColumnTypeEnum.LONG); 1943 resourceLink 1944 .addForeignKey("20180401.5", "FK_CODESYSVER_CS_ID") 1945 .toColumn("CODESYSTEM_PID") 1946 .references("TRM_CODESYSTEM", "PID"); 1947 1948 // Concept 1949 Builder.BuilderWithTableName concept = version.onTable("TRM_CONCEPT"); 1950 version.startSectionWithMessage("Starting work on table: " + concept.getTableName()); 1951 concept 1952 .addColumn("20180401.6", "CODE_SEQUENCE") 1953 .nullable() 1954 .type(ColumnTypeEnum.INT); 1955 1956 1957 } 1958 1959 protected void init330() { // 20180114 - 20180329 1960 Builder version = forVersion(VersionEnum.V3_3_0); 1961 1962 version.initializeSchema("20180115.0", new SchemaInitializationProvider("HAPI FHIR", "/ca/uhn/hapi/fhir/jpa/docs/database", "HFJ_RESOURCE", true)); 1963 1964 Builder.BuilderWithTableName hfjResource = version.onTable("HFJ_RESOURCE"); 1965 version.startSectionWithMessage("Starting work on table: " + hfjResource.getTableName()); 1966 hfjResource.dropColumn("20180115.1", "RES_TEXT"); 1967 hfjResource.dropColumn("20180115.2", "RES_ENCODING"); 1968 1969 Builder.BuilderWithTableName hfjResVer = version.onTable("HFJ_RES_VER"); 1970 version.startSectionWithMessage("Starting work on table: " + hfjResVer.getTableName()); 1971 hfjResVer.modifyColumn("20180115.3", "RES_ENCODING") 1972 .nullable(); 1973 hfjResVer.modifyColumn("20180115.4", "RES_TEXT") 1974 .nullable(); 1975 } 1976 1977 public enum FlagEnum { 1978 NO_MIGRATE_HASHES("no-migrate-350-hashes"); 1979 1980 private final String myCommandLineValue; 1981 1982 FlagEnum(String theCommandLineValue) { 1983 myCommandLineValue = theCommandLineValue; 1984 } 1985 1986 public static FlagEnum fromCommandLineValue(String theCommandLineValue) { 1987 Optional<FlagEnum> retVal = Arrays.stream(values()).filter(t -> t.myCommandLineValue.equals(theCommandLineValue)).findFirst(); 1988 return retVal.orElseThrow(() -> { 1989 List<String> validValues = Arrays.stream(values()).map(t -> t.myCommandLineValue).sorted().collect(Collectors.toList()); 1990 return new IllegalArgumentException("Invalid flag \"" + theCommandLineValue + "\". Valid values: " + validValues); 1991 }); 1992 } 1993 } 1994 1995 1996}