001/*- 002 * #%L 003 * HAPI FHIR JPA Server 004 * %% 005 * Copyright (C) 2014 - 2024 Smile CDR, Inc. 006 * %% 007 * Licensed under the Apache License, Version 2.0 (the "License"); 008 * you may not use this file except in compliance with the License. 009 * You may obtain a copy of the License at 010 * 011 * http://www.apache.org/licenses/LICENSE-2.0 012 * 013 * Unless required by applicable law or agreed to in writing, software 014 * distributed under the License is distributed on an "AS IS" BASIS, 015 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 016 * See the License for the specific language governing permissions and 017 * limitations under the License. 018 * #L% 019 */ 020package ca.uhn.fhir.jpa.migrate.tasks; 021 022import ca.uhn.fhir.interceptor.model.RequestPartitionId; 023import ca.uhn.fhir.jpa.entity.BulkExportJobEntity; 024import ca.uhn.fhir.jpa.entity.BulkImportJobEntity; 025import ca.uhn.fhir.jpa.entity.Search; 026import ca.uhn.fhir.jpa.migrate.DriverTypeEnum; 027import ca.uhn.fhir.jpa.migrate.taskdef.ArbitrarySqlTask; 028import ca.uhn.fhir.jpa.migrate.taskdef.CalculateHashesTask; 029import ca.uhn.fhir.jpa.migrate.taskdef.CalculateOrdinalDatesTask; 030import ca.uhn.fhir.jpa.migrate.taskdef.ColumnTypeEnum; 031import ca.uhn.fhir.jpa.migrate.taskdef.ForceIdMigrationCopyTask; 032import ca.uhn.fhir.jpa.migrate.taskdef.ForceIdMigrationFixTask; 033import ca.uhn.fhir.jpa.migrate.tasks.api.BaseMigrationTasks; 034import ca.uhn.fhir.jpa.migrate.tasks.api.Builder; 035import ca.uhn.fhir.jpa.model.config.PartitionSettings; 036import ca.uhn.fhir.jpa.model.entity.BaseResourceIndexedSearchParam; 037import ca.uhn.fhir.jpa.model.entity.ResourceHistoryTable; 038import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamDate; 039import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantity; 040import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamString; 041import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamToken; 042import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamUri; 043import ca.uhn.fhir.jpa.model.entity.ResourceTable; 044import ca.uhn.fhir.jpa.model.entity.SearchParamPresentEntity; 045import ca.uhn.fhir.jpa.model.entity.StorageSettings; 046import ca.uhn.fhir.util.ClasspathUtil; 047import ca.uhn.fhir.util.VersionEnum; 048import org.apache.commons.lang3.StringUtils; 049 050import java.util.Arrays; 051import java.util.HashMap; 052import java.util.List; 053import java.util.Map; 054import java.util.Optional; 055import java.util.Set; 056import java.util.stream.Collectors; 057 058import static ca.uhn.fhir.rest.api.Constants.UUID_LENGTH; 059 060@SuppressWarnings({"SqlNoDataSourceInspection", "SpellCheckingInspection", "java:S1192"}) 061public class HapiFhirJpaMigrationTasks extends BaseMigrationTasks<VersionEnum> { 062 063 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 064 public static final DriverTypeEnum[] NON_AUTOMATIC_FK_INDEX_PLATFORMS = 065 new DriverTypeEnum[] {DriverTypeEnum.POSTGRES_9_4, DriverTypeEnum.ORACLE_12C, DriverTypeEnum.MSSQL_2012}; 066 private static final String QUERY_FOR_COLUMN_COLLATION_TEMPLATE = "WITH defcoll AS (\n" 067 + " SELECT datcollate AS coll\n" 068 + " FROM pg_database\n" 069 + " WHERE datname = current_database())\n" 070 + ", collation_by_column AS (\n" 071 + " SELECT a.attname,\n" 072 + " CASE WHEN c.collname = 'default'\n" 073 + " THEN defcoll.coll\n" 074 + " ELSE c.collname\n" 075 + " END AS my_collation\n" 076 + " FROM pg_attribute AS a\n" 077 + " CROSS JOIN defcoll\n" 078 + " LEFT JOIN pg_collation AS c ON a.attcollation = c.oid\n" 079 + " WHERE a.attrelid = '%s'::regclass\n" 080 + " AND a.attnum > 0\n" 081 + " AND attname = '%s'\n" 082 + ")\n" 083 + "SELECT TRUE as result\n" 084 + "FROM collation_by_column\n" 085 + "WHERE EXISTS (SELECT 1\n" 086 + " FROM collation_by_column\n" 087 + " WHERE my_collation != 'C')"; 088 private final Set<FlagEnum> myFlags; 089 090 /** 091 * Constructor 092 */ 093 public HapiFhirJpaMigrationTasks(Set<String> theFlags) { 094 myFlags = theFlags.stream().map(FlagEnum::fromCommandLineValue).collect(Collectors.toSet()); 095 096 init330(); // 20180114 - 20180329 097 init340(); // 20180401 - 20180528 098 init350(); // 20180601 - 20180917 099 init360(); // 20180918 - 20181112 100 init400(); // 20190401 - 20190814 101 init410(); // 20190815 - 20191014 102 init420(); // 20191015 - 20200217 103 init430(); // Replaced by 5.0.0 104 init500(); // 20200218 - 20200513 105 init501(); // 20200514 - 20200515 106 init510(); // 20200516 - 20201028 107 init520(); // 20201029 - 108 init530(); 109 init540(); // 20210218 - 20210520 110 init550(); // 20210520 - 111 init560(); // 20211027 - 112 init570(); // 20211102 - 113 init600(); // 20211102 - 114 init610(); 115 init620(); 116 init640(); 117 init640_after_20230126(); 118 init660(); 119 init680(); 120 init680_Part2(); 121 init700(); 122 init720(); 123 } 124 125 protected void init720() { 126 // Start of migrations from 7.0 to 7.2 127 128 Builder version = forVersion(VersionEnum.V7_2_0); 129 130 // allow null codes in concept map targets (see comment on "20190722.27" if you are going to change this) 131 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 132 .modifyColumn("20240327.1", "TARGET_CODE") 133 .nullable() 134 .withType(ColumnTypeEnum.STRING, 500); 135 136 // Stop writing to hfj_forced_id https://github.com/hapifhir/hapi-fhir/pull/5817 137 Builder.BuilderWithTableName forcedId = version.onTable("HFJ_FORCED_ID"); 138 forcedId.dropForeignKey("20240402.1", "FK_FORCEDID_RESOURCE", "HFJ_RESOURCE"); 139 forcedId.dropIndex("20240402.2", "IDX_FORCEDID_RESID"); 140 forcedId.dropIndex("20240402.3", "IDX_FORCEDID_TYPE_FID"); 141 forcedId.dropIndex("20240402.4", "IDX_FORCEID_FID"); 142 } 143 144 protected void init700() { 145 /* ************************************************ 146 * Start of 6.10 migrations 147 *********************************************** */ 148 149 Builder version = forVersion(VersionEnum.V7_0_0); 150 151 // new indices on MdmLink 152 Builder.BuilderWithTableName mdmLinkTable = version.onTable("MPI_LINK"); 153 154 mdmLinkTable 155 .addIndex("20230911.1", "IDX_EMPI_TGT_MR_LS") 156 .unique(false) 157 .online(true) 158 .withColumns("TARGET_TYPE", "MATCH_RESULT", "LINK_SOURCE"); 159 mdmLinkTable 160 .addIndex("20230911.2", "IDX_EMPi_TGT_MR_SCore") 161 .unique(false) 162 .online(true) 163 .withColumns("TARGET_TYPE", "MATCH_RESULT", "SCORE"); 164 165 // Move forced_id constraints to hfj_resource and the new fhir_id column 166 // Note: we leave the HFJ_FORCED_ID.IDX_FORCEDID_TYPE_FID index in place to support old writers for a while. 167 version.addTask(new ForceIdMigrationCopyTask(version.getRelease(), "20231018.1").setDoNothing(true)); 168 169 Builder.BuilderWithTableName hfjResource = version.onTable("HFJ_RESOURCE"); 170 // commented out to make numeric space for the fix task below. 171 // This constraint can't be enabled until the column is fully populated, and the shipped version of 20231018.1 172 // was broken. 173 // hfjResource.modifyColumn("20231018.2", "FHIR_ID").nonNullable(); 174 175 // this was inserted after the release. 176 version.addTask(new ForceIdMigrationFixTask(version.getRelease(), "20231018.3")); 177 178 // added back in place of 20231018.2. If 20231018.2 already ran, this is a no-op. 179 hfjResource.modifyColumn("20231018.4", "FHIR_ID").nonNullable(); 180 181 hfjResource.dropIndex("20231027.1", "IDX_RES_FHIR_ID"); 182 hfjResource 183 .addIndex("20231027.2", "IDX_RES_TYPE_FHIR_ID") 184 .unique(true) 185 .online(true) 186 // include res_id and our deleted flag so we can satisfy Observation?_sort=_id from the index on 187 // platforms that support it. 188 .includeColumns("RES_ID, RES_DELETED_AT") 189 .withColumns("RES_TYPE", "FHIR_ID"); 190 191 // For resolving references that don't supply the type. 192 hfjResource 193 .addIndex("20231027.3", "IDX_RES_FHIR_ID") 194 .unique(false) 195 .online(true) 196 .withColumns("FHIR_ID"); 197 198 Builder.BuilderWithTableName batch2JobInstanceTable = version.onTable("BT2_JOB_INSTANCE"); 199 200 batch2JobInstanceTable.addColumn("20231128.1", "USER_NAME").nullable().type(ColumnTypeEnum.STRING, 200); 201 202 batch2JobInstanceTable.addColumn("20231128.2", "CLIENT_ID").nullable().type(ColumnTypeEnum.STRING, 200); 203 204 { 205 version.executeRawSql( 206 "20231212.1", 207 "CREATE INDEX CONCURRENTLY idx_sp_string_hash_nrm_pattern_ops ON hfj_spidx_string USING btree (hash_norm_prefix, sp_value_normalized varchar_pattern_ops, res_id, partition_id)") 208 .setTransactional(false) 209 .onlyAppliesToPlatforms(DriverTypeEnum.POSTGRES_9_4) 210 .onlyIf( 211 String.format( 212 QUERY_FOR_COLUMN_COLLATION_TEMPLATE, 213 "HFJ_SPIDX_STRING".toLowerCase(), 214 "SP_VALUE_NORMALIZED".toLowerCase()), 215 "Column HFJ_SPIDX_STRING.SP_VALUE_NORMALIZED already has a collation of 'C' so doing nothing") 216 .onlyIf( 217 "SELECT NOT EXISTS(select 1 from pg_indexes where indexname='idx_sp_string_hash_nrm_pattern_ops')", 218 "Index idx_sp_string_hash_nrm_pattern_ops already exists"); 219 version.executeRawSql( 220 "20231212.2", 221 "CREATE UNIQUE INDEX CONCURRENTLY idx_sp_uri_hash_identity_pattern_ops ON hfj_spidx_uri USING btree (hash_identity, sp_uri varchar_pattern_ops, res_id, partition_id)") 222 .setTransactional(false) 223 .onlyAppliesToPlatforms(DriverTypeEnum.POSTGRES_9_4) 224 .onlyIf( 225 String.format( 226 QUERY_FOR_COLUMN_COLLATION_TEMPLATE, 227 "HFJ_SPIDX_URI".toLowerCase(), 228 "SP_URI".toLowerCase()), 229 "Column HFJ_SPIDX_STRING.SP_VALUE_NORMALIZED already has a collation of 'C' so doing nothing") 230 .onlyIf( 231 "SELECT NOT EXISTS(select 1 from pg_indexes where indexname='idx_sp_uri_hash_identity_pattern_ops')", 232 "Index idx_sp_uri_hash_identity_pattern_ops already exists."); 233 } 234 235 // This fix was bad for MSSQL, it has been set to do nothing. 236 version.addTask(new ForceIdMigrationFixTask(version.getRelease(), "20231213.1").setDoNothing(true)); 237 238 // This fix will work for MSSQL or Oracle. 239 version.addTask(new ForceIdMigrationFixTask(version.getRelease(), "20231222.1")); 240 } 241 242 private void init680_Part2() { 243 Builder version = forVersion(VersionEnum.V6_8_0); 244 245 // Add additional LOB migration columns 246 version.onTable("BT2_JOB_INSTANCE") 247 .addColumn("20240227.1", "REPORT_VC") 248 .nullable() 249 .type(ColumnTypeEnum.TEXT); 250 version.onTable("BT2_JOB_INSTANCE") 251 .addColumn("20240227.2", "PARAMS_JSON_VC") 252 .nullable() 253 .type(ColumnTypeEnum.TEXT); 254 255 version.onTable("BT2_WORK_CHUNK") 256 .addColumn("20240227.3", "CHUNK_DATA_VC") 257 .nullable() 258 .type(ColumnTypeEnum.TEXT); 259 260 version.onTable("HFJ_SEARCH") 261 .addColumn("20240227.4", "SEARCH_QUERY_STRING_VC") 262 .nullable() 263 .type(ColumnTypeEnum.TEXT); 264 version.onTable("HFJ_SEARCH") 265 .addColumn("20240227.5", "SEARCH_PARAM_MAP_BIN") 266 .nullable() 267 .type(ColumnTypeEnum.BINARY); 268 269 version.onTable("HFJ_BLK_IMPORT_JOBFILE") 270 .addColumn("20240227.6", "JOB_CONTENTS_VC") 271 .nullable() 272 .type(ColumnTypeEnum.TEXT); 273 274 version.onTable("HFJ_BLK_IMPORT_JOBFILE") 275 .modifyColumn("20240227.7", "JOB_CONTENTS") 276 .nullable() 277 .withType(ColumnTypeEnum.BLOB); 278 } 279 280 protected void init680() { 281 Builder version = forVersion(VersionEnum.V6_8_0); 282 // HAPI-FHIR #4801 - Add New Index On HFJ_RESOURCE 283 Builder.BuilderWithTableName resourceTable = version.onTable("HFJ_RESOURCE"); 284 285 resourceTable 286 .addIndex("20230502.1", "IDX_RES_RESID_UPDATED") 287 .unique(false) 288 .online(true) 289 .withColumns("RES_ID", "RES_UPDATED", "PARTITION_ID"); 290 291 Builder.BuilderWithTableName tagDefTable = version.onTable("HFJ_TAG_DEF"); 292 tagDefTable.dropIndex("20230505.1", "IDX_TAGDEF_TYPESYSCODEVERUS"); 293 294 tagDefTable.dropIndex("20230505.2", "IDX_TAG_DEF_TP_CD_SYS"); 295 tagDefTable 296 .addIndex("20230505.3", "IDX_TAG_DEF_TP_CD_SYS") 297 .unique(false) 298 .online(false) 299 .withColumns("TAG_TYPE", "TAG_CODE", "TAG_SYSTEM", "TAG_ID", "TAG_VERSION", "TAG_USER_SELECTED"); 300 301 // This migration is failing in Oracle because there is already an index created on column RES_VER_PID since it 302 // is a primary key. 303 // IDX_RESVERPROV_RESVER_PID is removed in 20230523.1 304 version.onTable("HFJ_RES_VER_PROV") 305 .addIndex("20230510.1", "IDX_RESVERPROV_RESVER_PID") 306 .unique(false) 307 .withColumns("RES_VER_PID") 308 .failureAllowed(); 309 310 // drop the index for any database that has RES_PID column already indexed from previous migrations 311 version.onTable("HFJ_RES_VER_PROV") 312 .dropIndex("20230510.2", "FK_RESVERPROV_RES_PID") 313 .failureAllowed(); 314 315 version.onTable("HFJ_RES_VER_PROV") 316 .addIndex("20230510.3", "IDX_RESVERPROV_RES_PID") 317 .unique(false) 318 .withColumns("RES_PID"); 319 320 version.onTable(ResourceHistoryTable.HFJ_RES_VER) 321 .addColumn("20230510.4", "SOURCE_URI") 322 .nullable() 323 .type(ColumnTypeEnum.STRING, 100); 324 version.onTable(ResourceHistoryTable.HFJ_RES_VER) 325 .addColumn("20230510.5", "REQUEST_ID") 326 .nullable() 327 .type(ColumnTypeEnum.STRING, 16); 328 329 version.onTable("HFJ_RES_VER_PROV") 330 .addForeignKey("20230510.6", "FK_RESVERPROV_RES_PID") 331 .toColumn("RES_PID") 332 .references("HFJ_RESOURCE", "RES_ID"); 333 334 version.onTable("HFJ_RES_VER_PROV").dropIndex("20230523.1", "IDX_RESVERPROV_RESVER_PID"); 335 336 // add warning message to batch job instance 337 version.onTable("BT2_WORK_CHUNK") 338 .addColumn("20230524.1", "WARNING_MSG") 339 .nullable() 340 .type(ColumnTypeEnum.CLOB) 341 .doNothing(); // the migration below is the better implementation 342 343 version.onTable("BT2_JOB_INSTANCE") 344 .addColumn("20230524.2", "WARNING_MSG") 345 .nullable() 346 .type(ColumnTypeEnum.CLOB) 347 .doNothing(); // the migration below is the better implementation 348 349 // adding indexes to foreign keys 350 // this makes our table scans more efficient, 351 // but it also makes us more stable 352 // Oracle does not like unindexed foreign keys 353 version.onTable("NPM_PACKAGE_VER") 354 .addIndex("20230609.3", "FK_NPM_PKV_PKG") 355 .unique(false) 356 .withColumns("PACKAGE_PID") 357 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 358 version.onTable("NPM_PACKAGE_VER") 359 .addIndex("20230609.4", "FK_NPM_PKV_RESID") 360 .unique(false) 361 .withColumns("BINARY_RES_ID") 362 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 363 364 version.onTable("NPM_PACKAGE_VER_RES") 365 .addIndex("20230609.5", "FK_NPM_PACKVERRES_PACKVER") 366 .unique(false) 367 .withColumns("PACKVER_PID") 368 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 369 version.onTable("NPM_PACKAGE_VER_RES") 370 .addIndex("20230609.6", "FK_NPM_PKVR_RESID") 371 .unique(false) 372 .withColumns("BINARY_RES_ID") 373 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 374 375 version.onTable("MPI_LINK") 376 .addIndex("20230609.7", "FK_EMPI_LINK_TARGET") 377 .unique(false) 378 .withColumns("TARGET_PID") 379 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 380 381 version.onTable("TRM_CODESYSTEM") 382 .addIndex("20230609.8", "FK_TRMCODESYSTEM_RES") 383 .unique(false) 384 .withColumns("RES_ID") 385 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 386 version.onTable("TRM_CODESYSTEM") 387 .addIndex("20230609.9", "FK_TRMCODESYSTEM_CURVER") 388 .unique(false) 389 .withColumns("CURRENT_VERSION_PID") 390 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 391 392 version.onTable("TRM_CODESYSTEM_VER") 393 .addIndex("20230609.10", "FK_CODESYSVER_RES_ID") 394 .unique(false) 395 .withColumns("RES_ID") 396 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 397 version.onTable("TRM_CODESYSTEM_VER") 398 .addIndex("20230609.11", "FK_CODESYSVER_CS_ID") 399 .unique(false) 400 .withColumns("CODESYSTEM_PID") 401 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 402 403 version.onTable("TRM_CONCEPT_PC_LINK") 404 .addIndex("20230609.12", "FK_TERM_CONCEPTPC_CS") 405 .unique(false) 406 .withColumns("CODESYSTEM_PID") 407 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 408 409 version.onTable("TRM_CONCEPT_PROPERTY") 410 .addIndex("20230609.13", "FK_CONCEPTPROP_CSV") 411 .unique(false) 412 .withColumns("CS_VER_PID") 413 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 414 415 version.onTable("TRM_VALUESET") 416 .addIndex("20230609.14", "FK_TRMVALUESET_RES") 417 .unique(false) 418 .withColumns("RES_ID") 419 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 420 421 version.onTable("TRM_VALUESET_C_DESIGNATION") 422 .addIndex("20230609.15", "FK_TRM_VSCD_VS_PID") 423 .unique(false) 424 .withColumns("VALUESET_PID") 425 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 426 427 version.onTable("TRM_CONCEPT_MAP") 428 .addIndex("20230609.17", "FK_TRMCONCEPTMAP_RES") 429 .unique(false) 430 .withColumns("RES_ID") 431 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 432 433 version.onTable("TRM_CONCEPT_DESIG") 434 .addIndex("20230609.18", "FK_CONCEPTDESIG_CSV") 435 .unique(false) 436 .withColumns("CS_VER_PID") 437 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 438 439 version.onTable("TRM_CONCEPT_MAP_GROUP") 440 .addIndex("20230609.19", "FK_TCMGROUP_CONCEPTMAP") 441 .unique(false) 442 .withColumns("CONCEPT_MAP_PID") 443 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 444 445 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 446 .addIndex("20230609.20", "FK_TCMGELEMENT_GROUP") 447 .unique(false) 448 .withColumns("CONCEPT_MAP_GROUP_PID") 449 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 450 451 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 452 .addIndex("20230609.21", "FK_TCMGETARGET_ELEMENT") 453 .unique(false) 454 .withColumns("CONCEPT_MAP_GRP_ELM_PID") 455 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 456 457 // add warning message to batch job instance using limited varchar column to store 458 version.onTable("BT2_WORK_CHUNK") 459 .dropColumn("20230622.1", "WARNING_MSG") 460 .failureAllowed(); 461 462 version.onTable("BT2_WORK_CHUNK") 463 .addColumn("20230622.2", "WARNING_MSG") 464 .nullable() 465 .type(ColumnTypeEnum.STRING, 4000); 466 467 version.onTable("BT2_JOB_INSTANCE") 468 .dropColumn("20230622.3", "WARNING_MSG") 469 .failureAllowed(); 470 471 version.onTable("BT2_JOB_INSTANCE") 472 .addColumn("20230622.4", "WARNING_MSG") 473 .nullable() 474 .type(ColumnTypeEnum.STRING, 4000); 475 } 476 477 protected void init660() { 478 Builder version = forVersion(VersionEnum.V6_6_0); 479 480 // fix Postgres clob types - that stupid oid driver problem is still there 481 // BT2_JOB_INSTANCE.PARAMS_JSON_LOB 482 version.onTable("BT2_JOB_INSTANCE").migratePostgresTextClobToBinaryClob("20230208.1", "PARAMS_JSON_LOB"); 483 // BT2_JOB_INSTANCE.REPORT 484 version.onTable("BT2_JOB_INSTANCE").migratePostgresTextClobToBinaryClob("20230208.2", "REPORT"); 485 // BT2_WORK_CHUNK.CHUNK_DATA 486 version.onTable("BT2_WORK_CHUNK").migratePostgresTextClobToBinaryClob("20230208.3", "CHUNK_DATA"); 487 488 { 489 Builder.BuilderWithTableName tagDefTable = version.onTable("HFJ_TAG_DEF"); 490 491 // add columns 492 tagDefTable.addColumn("20230209.1", "TAG_VERSION").nullable().type(ColumnTypeEnum.STRING, 30); 493 tagDefTable.addColumn("20230209.2", "TAG_USER_SELECTED").nullable().type(ColumnTypeEnum.BOOLEAN); 494 495 // Update indexing 496 tagDefTable.dropIndex("20230209.3", "IDX_TAGDEF_TYPESYSCODE"); 497 498 tagDefTable.dropIndex("20230209.4", "IDX_TAGDEF_TYPESYSCODEVERUS"); 499 Map<DriverTypeEnum, String> addTagDefConstraint = new HashMap<>(); 500 addTagDefConstraint.put( 501 DriverTypeEnum.H2_EMBEDDED, 502 "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 503 addTagDefConstraint.put( 504 DriverTypeEnum.MARIADB_10_1, 505 "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 506 addTagDefConstraint.put( 507 DriverTypeEnum.MSSQL_2012, 508 "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 509 addTagDefConstraint.put( 510 DriverTypeEnum.MYSQL_5_7, 511 "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 512 addTagDefConstraint.put( 513 DriverTypeEnum.ORACLE_12C, 514 "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 515 addTagDefConstraint.put( 516 DriverTypeEnum.POSTGRES_9_4, 517 "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 518 version.executeRawSql("20230209.5", addTagDefConstraint); 519 } 520 521 version.onTable(Search.HFJ_SEARCH) 522 .addColumn("20230215.1", Search.SEARCH_UUID) 523 .nullable() 524 .type(ColumnTypeEnum.STRING, Search.SEARCH_UUID_COLUMN_LENGTH); 525 version.onTable(BulkImportJobEntity.HFJ_BLK_IMPORT_JOB) 526 .addColumn("20230215.2", BulkImportJobEntity.JOB_ID) 527 .nullable() 528 .type(ColumnTypeEnum.STRING, UUID_LENGTH); 529 version.onTable(BulkExportJobEntity.HFJ_BLK_EXPORT_JOB) 530 .addColumn("20230215.3", BulkExportJobEntity.JOB_ID) 531 .nullable() 532 .type(ColumnTypeEnum.STRING, UUID_LENGTH); 533 534 Builder.BuilderAddTableByColumns resSearchUrlTable = 535 version.addTableByColumns("20230227.1", "HFJ_RES_SEARCH_URL", "RES_SEARCH_URL"); 536 537 resSearchUrlTable.addColumn("RES_SEARCH_URL").nonNullable().type(ColumnTypeEnum.STRING, 768); 538 resSearchUrlTable.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 539 540 resSearchUrlTable.addColumn("CREATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 541 542 resSearchUrlTable 543 .addIndex("20230227.2", "IDX_RESSEARCHURL_RES") 544 .unique(false) 545 .withColumns("RES_ID"); 546 resSearchUrlTable 547 .addIndex("20230227.3", "IDX_RESSEARCHURL_TIME") 548 .unique(false) 549 .withColumns("CREATED_TIME"); 550 551 { 552 // string search index 553 Builder.BuilderWithTableName stringTable = version.onTable("HFJ_SPIDX_STRING"); 554 555 // add res_id to indentity to speed up sorts. 556 stringTable 557 .addIndex("20230303.1", "IDX_SP_STRING_HASH_IDENT_V2") 558 .unique(false) 559 .online(true) 560 .withColumns("HASH_IDENTITY", "RES_ID", "PARTITION_ID"); 561 stringTable.dropIndexOnline("20230303.2", "IDX_SP_STRING_HASH_IDENT"); 562 563 // add hash_norm to res_id to speed up joins on a second string. 564 stringTable 565 .addIndex("20230303.3", "IDX_SP_STRING_RESID_V2") 566 .unique(false) 567 .online(true) 568 .withColumns("RES_ID", "HASH_NORM_PREFIX", "PARTITION_ID"); 569 570 // drop and recreate FK_SPIDXSTR_RESOURCE since it will be useing the old IDX_SP_STRING_RESID 571 stringTable.dropForeignKey("20230303.4", "FK_SPIDXSTR_RESOURCE", "HFJ_RESOURCE"); 572 stringTable.dropIndexOnline("20230303.5", "IDX_SP_STRING_RESID"); 573 stringTable 574 .addForeignKey("20230303.6", "FK_SPIDXSTR_RESOURCE") 575 .toColumn("RES_ID") 576 .references("HFJ_RESOURCE", "RES_ID"); 577 } 578 579 final String revColumnName = "REV"; 580 final String enversRevisionTable = "HFJ_REVINFO"; 581 final String enversMpiLinkAuditTable = "MPI_LINK_AUD"; 582 final String revTstmpColumnName = "REVTSTMP"; 583 584 { 585 version.addIdGenerator("20230306.1", "SEQ_HFJ_REVINFO"); 586 587 final Builder.BuilderAddTableByColumns enversRevInfo = 588 version.addTableByColumns("20230306.2", enversRevisionTable, revColumnName); 589 590 enversRevInfo.addColumn(revColumnName).nonNullable().type(ColumnTypeEnum.LONG); 591 enversRevInfo.addColumn(revTstmpColumnName).nullable().type(ColumnTypeEnum.LONG); 592 593 final Builder.BuilderAddTableByColumns empiLink = 594 version.addTableByColumns("20230306.6", enversMpiLinkAuditTable, "PID", revColumnName); 595 596 empiLink.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 597 empiLink.addColumn("REV").nonNullable().type(ColumnTypeEnum.LONG); 598 empiLink.addColumn("REVTYPE").nullable().type(ColumnTypeEnum.TINYINT); 599 empiLink.addColumn("PERSON_PID").nullable().type(ColumnTypeEnum.LONG); 600 empiLink.addColumn("GOLDEN_RESOURCE_PID").nullable().type(ColumnTypeEnum.LONG); 601 empiLink.addColumn("TARGET_TYPE").nullable().type(ColumnTypeEnum.STRING, 40); 602 empiLink.addColumn("RULE_COUNT").nullable().type(ColumnTypeEnum.LONG); 603 empiLink.addColumn("TARGET_PID").nullable().type(ColumnTypeEnum.LONG); 604 empiLink.addColumn("MATCH_RESULT").nullable().type(ColumnTypeEnum.INT); 605 empiLink.addColumn("LINK_SOURCE").nullable().type(ColumnTypeEnum.INT); 606 empiLink.addColumn("CREATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 607 empiLink.addColumn("UPDATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 608 empiLink.addColumn("VERSION").nullable().type(ColumnTypeEnum.STRING, 16); 609 empiLink.addColumn("EID_MATCH").nullable().type(ColumnTypeEnum.BOOLEAN); 610 empiLink.addColumn("NEW_PERSON").nullable().type(ColumnTypeEnum.BOOLEAN); 611 empiLink.addColumn("VECTOR").nullable().type(ColumnTypeEnum.LONG); 612 empiLink.addColumn("SCORE").nullable().type(ColumnTypeEnum.FLOAT); 613 614 // N.B. It's impossible to rename a foreign key in a Hibernate Envers audit table, and the schema migration 615 // unit test will fail if we try to drop and recreate it 616 empiLink.addForeignKey("20230306.7", "FKAOW7NXNCLOEC419ARS0FPP58M") 617 .toColumn(revColumnName) 618 .references(enversRevisionTable, revColumnName); 619 } 620 621 { 622 Builder.BuilderAddTableByColumns resourceModifiedTable = 623 version.addTableByColumns("20230315.1", "HFJ_RESOURCE_MODIFIED", "RES_ID", "RES_VER"); 624 resourceModifiedTable.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.STRING, 256); 625 resourceModifiedTable.addColumn("RES_VER").nonNullable().type(ColumnTypeEnum.STRING, 8); 626 resourceModifiedTable.addColumn("CREATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 627 resourceModifiedTable.addColumn("SUMMARY_MESSAGE").nonNullable().type(ColumnTypeEnum.STRING, 4000); 628 resourceModifiedTable.addColumn("RESOURCE_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 40); 629 } 630 631 { 632 // The pre-release already contains the long version of this column 633 // We do this becausea doing a modifyColumn on Postgres (and possibly other RDBMS's) will fail with a nasty 634 // error: 635 // column "revtstmp" cannot be cast automatically to type timestamp without time zone Hint: You might need 636 // to specify "USING revtstmp::timestamp without time zone". 637 version.onTable(enversRevisionTable).dropColumn("20230316.1", revTstmpColumnName); 638 639 version.onTable(enversRevisionTable) 640 .addColumn("20230316.2", revTstmpColumnName) 641 .nullable() 642 .type(ColumnTypeEnum.DATE_TIMESTAMP); 643 644 // New columns from AuditableBasePartitionable 645 version.onTable(enversMpiLinkAuditTable) 646 .addColumn("20230316.3", "PARTITION_ID") 647 .nullable() 648 .type(ColumnTypeEnum.INT); 649 650 version.onTable(enversMpiLinkAuditTable) 651 .addColumn("20230316.4", "PARTITION_DATE") 652 .nullable() 653 .type(ColumnTypeEnum.DATE_ONLY); 654 } 655 656 version.onTable(ResourceTable.HFJ_RESOURCE) 657 .addColumn("20230323.1", "SEARCH_URL_PRESENT") 658 .nullable() 659 .type(ColumnTypeEnum.BOOLEAN); 660 661 { 662 Builder.BuilderWithTableName uriTable = version.onTable("HFJ_SPIDX_URI"); 663 uriTable.addIndex("20230324.1", "IDX_SP_URI_HASH_URI_V2") 664 .unique(true) 665 .online(true) 666 .withColumns("HASH_URI", "RES_ID", "PARTITION_ID"); 667 uriTable.addIndex("20230324.2", "IDX_SP_URI_HASH_IDENTITY_V2") 668 .unique(true) 669 .online(true) 670 .withColumns("HASH_IDENTITY", "SP_URI", "RES_ID", "PARTITION_ID"); 671 uriTable.dropIndex("20230324.3", "IDX_SP_URI_RESTYPE_NAME"); 672 uriTable.dropIndex("20230324.4", "IDX_SP_URI_UPDATED"); 673 uriTable.dropIndex("20230324.5", "IDX_SP_URI"); 674 uriTable.dropIndex("20230324.6", "IDX_SP_URI_HASH_URI"); 675 uriTable.dropIndex("20230324.7", "IDX_SP_URI_HASH_IDENTITY"); 676 } 677 678 version.onTable("HFJ_SPIDX_COORDS").dropIndex("20230325.1", "IDX_SP_COORDS_HASH"); 679 version.onTable("HFJ_SPIDX_COORDS") 680 .addIndex("20230325.2", "IDX_SP_COORDS_HASH_V2") 681 .unique(false) 682 .online(true) 683 .withColumns("HASH_IDENTITY", "SP_LATITUDE", "SP_LONGITUDE", "RES_ID", "PARTITION_ID"); 684 685 // Postgres tuning. 686 String postgresTuningStatementsAll = 687 ClasspathUtil.loadResource("ca/uhn/fhir/jpa/docs/database/hapifhirpostgres94-init01.sql"); 688 List<String> postgresTuningStatements = Arrays.stream(postgresTuningStatementsAll.split("\\n")) 689 .map(StringUtils::trim) 690 .filter(StringUtils::isNotBlank) 691 .filter(t -> !t.startsWith("--")) 692 .collect(Collectors.toList()); 693 version.executeRawSqls("20230402.1", Map.of(DriverTypeEnum.POSTGRES_9_4, postgresTuningStatements)); 694 695 // Use an unlimited length text column for RES_TEXT_VC 696 // N.B. This will FAIL SILENTLY on Oracle due to the fact that Oracle does not support an ALTER TABLE from 697 // VARCHAR to 698 // CLOB. Because of failureAllowed() this won't halt the migration 699 version.onTable("HFJ_RES_VER") 700 .modifyColumn("20230421.1", "RES_TEXT_VC") 701 .nullable() 702 .failureAllowed() 703 .withType(ColumnTypeEnum.TEXT); 704 705 { 706 // add hash_norm to res_id to speed up joins on a second string. 707 Builder.BuilderWithTableName linkTable = version.onTable("HFJ_RES_LINK"); 708 linkTable 709 .addIndex("20230424.1", "IDX_RL_TGT_v2") 710 .unique(false) 711 .online(true) 712 .withColumns( 713 "TARGET_RESOURCE_ID", 714 "SRC_PATH", 715 "SRC_RESOURCE_ID", 716 "TARGET_RESOURCE_TYPE", 717 "PARTITION_ID"); 718 719 // drop and recreate FK_SPIDXSTR_RESOURCE since it will be useing the old IDX_SP_STRING_RESID 720 linkTable.dropForeignKey("20230424.2", "FK_RESLINK_TARGET", "HFJ_RESOURCE"); 721 linkTable.dropIndexOnline("20230424.3", "IDX_RL_TPATHRES"); 722 linkTable.dropIndexOnline("20230424.4", "IDX_RL_DEST"); 723 linkTable 724 .addForeignKey("20230424.5", "FK_RESLINK_TARGET") 725 .toColumn("TARGET_RESOURCE_ID") 726 .references("HFJ_RESOURCE", "RES_ID"); 727 } 728 729 { 730 version.onTable("MPI_LINK") 731 .addIndex("20230504.1", "IDX_EMPI_GR_TGT") 732 .unique(false) 733 .withColumns("GOLDEN_RESOURCE_PID", "TARGET_PID"); 734 } 735 } 736 737 protected void init640() { 738 Builder version = forVersion(VersionEnum.V6_3_0); 739 740 // start forced_id inline migration 741 version.onTable("HFJ_RESOURCE") 742 .addColumn("20221108.1", "FHIR_ID") 743 .nullable() 744 // FHIR ids contain a subset of ascii, limited to 64 chars. 745 .type(ColumnTypeEnum.STRING, 64); 746 747 // Add new Index to HFJ_SEARCH_INCLUDE on SEARCH_PID 748 version.onTable("HFJ_SEARCH_INCLUDE") 749 .addIndex("20221207.1", "FK_SEARCHINC_SEARCH") 750 .unique(false) 751 .online(true) 752 .withColumns("SEARCH_PID") 753 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 754 } 755 756 protected void init640_after_20230126() { 757 Builder version = forVersion(VersionEnum.V6_3_0); 758 { // We added this constraint when userSelected and Version were added. It is no longer necessary. 759 Builder.BuilderWithTableName tagDefTable = version.onTable("HFJ_TAG_DEF"); 760 tagDefTable.dropIndex("20230503.1", "IDX_TAGDEF_TYPESYSCODEVERUS"); 761 } 762 } 763 764 private void init620() { 765 Builder version = forVersion(VersionEnum.V6_2_0); 766 767 // add new REPORT column to BATCH2 tables 768 version.onTable("BT2_JOB_INSTANCE") 769 .addColumn("20220830.1", "FAST_TRACKING") 770 .nullable() 771 .type(ColumnTypeEnum.BOOLEAN); 772 773 version.onTable("HFJ_BINARY_STORAGE_BLOB") 774 .modifyColumn("20221017.1", "BLOB_SIZE") 775 .nullable() 776 .withType(ColumnTypeEnum.LONG); 777 778 version.onTable("HFJ_SPIDX_URI") 779 .modifyColumn("20221103.1", "SP_URI") 780 .nullable() 781 .withType(ColumnTypeEnum.STRING, 500); 782 783 version.onTable("BT2_JOB_INSTANCE") 784 .addColumn("20230110.1", "UPDATE_TIME") 785 .nullable() 786 .type(ColumnTypeEnum.DATE_TIMESTAMP); 787 788 version.onTable("BT2_WORK_CHUNK") 789 .addColumn("20230110.2", "UPDATE_TIME") 790 .nullable() 791 .type(ColumnTypeEnum.DATE_TIMESTAMP); 792 793 Map<DriverTypeEnum, String> updateBatch2JobInstance = new HashMap<>(); 794 updateBatch2JobInstance.put( 795 DriverTypeEnum.H2_EMBEDDED, 796 "update BT2_JOB_INSTANCE set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 797 updateBatch2JobInstance.put( 798 DriverTypeEnum.MARIADB_10_1, 799 "update BT2_JOB_INSTANCE set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 800 updateBatch2JobInstance.put( 801 DriverTypeEnum.MYSQL_5_7, 802 "update BT2_JOB_INSTANCE set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 803 updateBatch2JobInstance.put( 804 DriverTypeEnum.ORACLE_12C, 805 "update BT2_JOB_INSTANCE set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 806 updateBatch2JobInstance.put( 807 DriverTypeEnum.POSTGRES_9_4, 808 "update BT2_JOB_INSTANCE set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 809 updateBatch2JobInstance.put( 810 DriverTypeEnum.MSSQL_2012, 811 "update BT2_JOB_INSTANCE set UPDATE_TIME = coalesce(end_time, start_time, create_time, CONVERT(DATETIME,'2023-01-01 00:00:00')) where UPDATE_TIME is null"); 812 version.executeRawSql("20230397.1", updateBatch2JobInstance); 813 814 Map<DriverTypeEnum, String> updateBatch2WorkChunk = new HashMap<>(); 815 updateBatch2WorkChunk.put( 816 DriverTypeEnum.H2_EMBEDDED, 817 "update bt2_work_chunk set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 818 updateBatch2WorkChunk.put( 819 DriverTypeEnum.MARIADB_10_1, 820 "update bt2_work_chunk set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 821 updateBatch2WorkChunk.put( 822 DriverTypeEnum.MYSQL_5_7, 823 "update bt2_work_chunk set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 824 updateBatch2WorkChunk.put( 825 DriverTypeEnum.ORACLE_12C, 826 "update bt2_work_chunk set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 827 updateBatch2WorkChunk.put( 828 DriverTypeEnum.POSTGRES_9_4, 829 "update bt2_work_chunk set UPDATE_TIME = coalesce(end_time, start_time, create_time, TIMESTAMP '2023-01-01 00:00:00') where UPDATE_TIME is null"); 830 updateBatch2WorkChunk.put( 831 DriverTypeEnum.MSSQL_2012, 832 "update bt2_work_chunk set UPDATE_TIME = coalesce(end_time, start_time, create_time, CONVERT(DATETIME,'2023-01-01 00:00:00')) where UPDATE_TIME is null"); 833 version.executeRawSql("20230397.2", updateBatch2WorkChunk); 834 } 835 836 private void init610() { 837 Builder version = forVersion(VersionEnum.V6_1_0); 838 839 // add new REPORT column to BATCH2 tables 840 version.onTable("BT2_JOB_INSTANCE") 841 .addColumn("20220601.1", "REPORT") 842 .nullable() 843 .type(ColumnTypeEnum.CLOB); 844 } 845 846 private void init600() { 847 Builder version = forVersion(VersionEnum.V6_0_0); 848 849 /** 850 * New indexing for the core SPIDX tables. 851 * Ensure all queries can be satisfied by the index directly, 852 * either as left or right table in a hash or sort join. 853 * 854 * new date search indexing 855 * @see ca.uhn.fhir.jpa.search.builder.predicate.DatePredicateBuilder 856 * @see ResourceIndexedSearchParamDate 857 */ 858 { 859 Builder.BuilderWithTableName dateTable = version.onTable("HFJ_SPIDX_DATE"); 860 861 // replace and drop IDX_SP_DATE_HASH 862 dateTable 863 .addIndex("20220207.1", "IDX_SP_DATE_HASH_V2") 864 .unique(false) 865 .online(true) 866 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH", "RES_ID", "PARTITION_ID"); 867 dateTable.dropIndexOnline("20220207.2", "IDX_SP_DATE_HASH"); 868 869 // drop redundant 870 dateTable.dropIndexOnline("20220207.3", "IDX_SP_DATE_HASH_LOW"); 871 872 // replace and drop IDX_SP_DATE_HASH_HIGH 873 dateTable 874 .addIndex("20220207.4", "IDX_SP_DATE_HASH_HIGH_V2") 875 .unique(false) 876 .online(true) 877 .withColumns("HASH_IDENTITY", "SP_VALUE_HIGH", "RES_ID", "PARTITION_ID"); 878 dateTable.dropIndexOnline("20220207.5", "IDX_SP_DATE_HASH_HIGH"); 879 880 // replace and drop IDX_SP_DATE_ORD_HASH 881 dateTable 882 .addIndex("20220207.6", "IDX_SP_DATE_ORD_HASH_V2") 883 .unique(false) 884 .online(true) 885 .withColumns( 886 "HASH_IDENTITY", 887 "SP_VALUE_LOW_DATE_ORDINAL", 888 "SP_VALUE_HIGH_DATE_ORDINAL", 889 "RES_ID", 890 "PARTITION_ID"); 891 dateTable.dropIndexOnline("20220207.7", "IDX_SP_DATE_ORD_HASH"); 892 893 // replace and drop IDX_SP_DATE_ORD_HASH_HIGH 894 dateTable 895 .addIndex("20220207.8", "IDX_SP_DATE_ORD_HASH_HIGH_V2") 896 .unique(false) 897 .online(true) 898 .withColumns("HASH_IDENTITY", "SP_VALUE_HIGH_DATE_ORDINAL", "RES_ID", "PARTITION_ID"); 899 dateTable.dropIndexOnline("20220207.9", "IDX_SP_DATE_ORD_HASH_HIGH"); 900 901 // drop redundant 902 dateTable.dropIndexOnline("20220207.10", "IDX_SP_DATE_ORD_HASH_LOW"); 903 904 // replace and drop IDX_SP_DATE_RESID 905 dateTable 906 .addIndex("20220207.11", "IDX_SP_DATE_RESID_V2") 907 .unique(false) 908 .online(true) 909 .withColumns( 910 "RES_ID", 911 "HASH_IDENTITY", 912 "SP_VALUE_LOW", 913 "SP_VALUE_HIGH", 914 "SP_VALUE_LOW_DATE_ORDINAL", 915 "SP_VALUE_HIGH_DATE_ORDINAL", 916 "PARTITION_ID"); 917 // some engines tie the FK constraint to a particular index. 918 // So we need to drop and recreate the constraint to drop the old RES_ID index. 919 // Rename it while we're at it. FK17s70oa59rm9n61k9thjqrsqm was not a pretty name. 920 dateTable.dropForeignKey("20220207.12", "FK17S70OA59RM9N61K9THJQRSQM", "HFJ_RESOURCE"); 921 dateTable.dropIndexOnline("20220207.13", "IDX_SP_DATE_RESID"); 922 dateTable.dropIndexOnline("20220207.14", "FK17S70OA59RM9N61K9THJQRSQM"); 923 924 dateTable 925 .addForeignKey("20220207.15", "FK_SP_DATE_RES") 926 .toColumn("RES_ID") 927 .references("HFJ_RESOURCE", "RES_ID"); 928 929 // drop obsolete 930 dateTable.dropIndexOnline("20220207.16", "IDX_SP_DATE_UPDATED"); 931 } 932 933 /** 934 * new token search indexing 935 * @see ca.uhn.fhir.jpa.search.builder.predicate.TokenPredicateBuilder 936 * @see ResourceIndexedSearchParamToken 937 */ 938 { 939 Builder.BuilderWithTableName tokenTable = version.onTable("HFJ_SPIDX_TOKEN"); 940 941 // replace and drop IDX_SP_TOKEN_HASH for sorting 942 tokenTable 943 .addIndex("20220208.1", "IDX_SP_TOKEN_HASH_V2") 944 .unique(false) 945 .online(true) 946 .withColumns("HASH_IDENTITY", "SP_SYSTEM", "SP_VALUE", "RES_ID", "PARTITION_ID"); 947 948 tokenTable.dropIndexOnline("20220208.2", "IDX_SP_TOKEN_HASH"); 949 950 // for search by system 951 tokenTable 952 .addIndex("20220208.3", "IDX_SP_TOKEN_HASH_S_V2") 953 .unique(false) 954 .online(true) 955 .withColumns("HASH_SYS", "RES_ID", "PARTITION_ID"); 956 957 tokenTable.dropIndexOnline("20220208.4", "IDX_SP_TOKEN_HASH_S"); 958 959 // for search by system+value 960 tokenTable 961 .addIndex("20220208.5", "IDX_SP_TOKEN_HASH_SV_V2") 962 .unique(false) 963 .online(true) 964 .withColumns("HASH_SYS_AND_VALUE", "RES_ID", "PARTITION_ID"); 965 966 tokenTable.dropIndexOnline("20220208.6", "IDX_SP_TOKEN_HASH_SV"); 967 968 // for search by value 969 tokenTable 970 .addIndex("20220208.7", "IDX_SP_TOKEN_HASH_V_V2") 971 .unique(false) 972 .online(true) 973 .withColumns("HASH_VALUE", "RES_ID", "PARTITION_ID"); 974 975 tokenTable.dropIndexOnline("20220208.8", "IDX_SP_TOKEN_HASH_V"); 976 977 // obsolete. We're dropping this column. 978 tokenTable.dropIndexOnline("20220208.9", "IDX_SP_TOKEN_UPDATED"); 979 980 // for joining as second table: 981 { 982 // replace and drop IDX_SP_TOKEN_RESID, and the associated fk constraint 983 tokenTable 984 .addIndex("20220208.10", "IDX_SP_TOKEN_RESID_V2") 985 .unique(false) 986 .online(true) 987 .withColumns( 988 "RES_ID", 989 "HASH_SYS_AND_VALUE", 990 "HASH_VALUE", 991 "HASH_SYS", 992 "HASH_IDENTITY", 993 "PARTITION_ID"); 994 995 // some engines tie the FK constraint to a particular index. 996 // So we need to drop and recreate the constraint to drop the old RES_ID index. 997 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 998 tokenTable.dropForeignKey("20220208.11", "FK7ULX3J1GG3V7MAQREJGC7YBC4", "HFJ_RESOURCE"); 999 tokenTable.dropIndexOnline("20220208.12", "IDX_SP_TOKEN_RESID"); 1000 tokenTable.dropIndexOnline("20220208.13", "FK7ULX3J1GG3V7MAQREJGC7YBC4"); 1001 1002 tokenTable 1003 .addForeignKey("20220208.14", "FK_SP_TOKEN_RES") 1004 .toColumn("RES_ID") 1005 .references("HFJ_RESOURCE", "RES_ID"); 1006 } 1007 } 1008 1009 // fix for https://github.com/hapifhir/hapi-fhir/issues/3316 1010 // index must have same name that indexed FK or SchemaMigrationTest complains because H2 sets this index 1011 // automatically 1012 1013 version.onTable("TRM_VALUESET_C_DESIGNATION") 1014 .addIndex("20220223.1", "FK_TRM_VALUESET_CONCEPT_PID") 1015 .unique(false) 1016 .withColumns("VALUESET_CONCEPT_PID") 1017 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1018 1019 // Batch2 Framework 1020 1021 Builder.BuilderAddTableByColumns batchInstance = 1022 version.addTableByColumns("20220227.1", "BT2_JOB_INSTANCE", "ID"); 1023 batchInstance.addColumn("ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1024 batchInstance.addColumn("CREATE_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1025 batchInstance.addColumn("START_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1026 batchInstance.addColumn("END_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1027 batchInstance.addColumn("DEFINITION_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1028 batchInstance.addColumn("DEFINITION_VER").nonNullable().type(ColumnTypeEnum.INT); 1029 batchInstance.addColumn("STAT").nonNullable().type(ColumnTypeEnum.STRING, 20); 1030 batchInstance.addColumn("JOB_CANCELLED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1031 batchInstance.addColumn("PARAMS_JSON").nullable().type(ColumnTypeEnum.STRING, 2000); 1032 batchInstance.addColumn("PARAMS_JSON_LOB").nullable().type(ColumnTypeEnum.CLOB); 1033 batchInstance.addColumn("CMB_RECS_PROCESSED").nullable().type(ColumnTypeEnum.INT); 1034 batchInstance.addColumn("CMB_RECS_PER_SEC").nullable().type(ColumnTypeEnum.DOUBLE); 1035 batchInstance.addColumn("TOT_ELAPSED_MILLIS").nullable().type(ColumnTypeEnum.INT); 1036 batchInstance.addColumn("WORK_CHUNKS_PURGED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1037 batchInstance.addColumn("PROGRESS_PCT").nullable().type(ColumnTypeEnum.DOUBLE); 1038 batchInstance.addColumn("ERROR_MSG").nullable().type(ColumnTypeEnum.STRING, 500); 1039 batchInstance.addColumn("ERROR_COUNT").nullable().type(ColumnTypeEnum.INT); 1040 batchInstance.addColumn("EST_REMAINING").nullable().type(ColumnTypeEnum.STRING, 100); 1041 batchInstance.addIndex("20220227.2", "IDX_BT2JI_CT").unique(false).withColumns("CREATE_TIME"); 1042 1043 Builder.BuilderAddTableByColumns batchChunk = version.addTableByColumns("20220227.3", "BT2_WORK_CHUNK", "ID"); 1044 batchChunk.addColumn("ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1045 batchChunk.addColumn("SEQ").nonNullable().type(ColumnTypeEnum.INT); 1046 batchChunk.addColumn("CREATE_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1047 batchChunk.addColumn("START_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1048 batchChunk.addColumn("END_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1049 batchChunk.addColumn("DEFINITION_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1050 batchChunk.addColumn("DEFINITION_VER").nonNullable().type(ColumnTypeEnum.INT); 1051 batchChunk.addColumn("STAT").nonNullable().type(ColumnTypeEnum.STRING, 20); 1052 batchChunk.addColumn("RECORDS_PROCESSED").nullable().type(ColumnTypeEnum.INT); 1053 batchChunk.addColumn("TGT_STEP_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1054 batchChunk.addColumn("CHUNK_DATA").nullable().type(ColumnTypeEnum.CLOB); 1055 batchChunk.addColumn("INSTANCE_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1056 batchChunk.addColumn("ERROR_MSG").nullable().type(ColumnTypeEnum.STRING, 500); 1057 batchChunk.addColumn("ERROR_COUNT").nonNullable().type(ColumnTypeEnum.INT); 1058 batchChunk.addIndex("20220227.4", "IDX_BT2WC_II_SEQ").unique(false).withColumns("INSTANCE_ID", "SEQ"); 1059 batchChunk 1060 .addForeignKey("20220227.5", "FK_BT2WC_INSTANCE") 1061 .toColumn("INSTANCE_ID") 1062 .references("BT2_JOB_INSTANCE", "ID"); 1063 1064 replaceNumericSPIndices(version); 1065 replaceQuantitySPIndices(version); 1066 1067 // Drop Index on HFJ_RESOURCE.INDEX_STATUS 1068 version.onTable("HFJ_RESOURCE").dropIndex("20220314.1", "IDX_INDEXSTATUS"); 1069 1070 version.onTable("BT2_JOB_INSTANCE") 1071 .addColumn("20220416.1", "CUR_GATED_STEP_ID") 1072 .nullable() 1073 .type(ColumnTypeEnum.STRING, 100); 1074 1075 // Make Job expiry nullable so that we can prevent job expiry by using a null value. 1076 version.onTable("HFJ_BLK_EXPORT_JOB") 1077 .modifyColumn("20220423.1", "EXP_TIME") 1078 .nullable() 1079 .withType(ColumnTypeEnum.DATE_TIMESTAMP); 1080 1081 // New Index on HFJ_RESOURCE for $reindex Operation - hapi-fhir #3534 1082 { 1083 version.onTable("HFJ_RESOURCE") 1084 .addIndex("20220425.1", "IDX_RES_TYPE_DEL_UPDATED") 1085 .unique(false) 1086 .online(true) 1087 .withColumns("RES_TYPE", "RES_DELETED_AT", "RES_UPDATED", "PARTITION_ID", "RES_ID"); 1088 1089 // Drop existing Index on HFJ_RESOURCE.RES_TYPE since the new Index will meet the overall Index Demand 1090 version.onTable("HFJ_RESOURCE").dropIndexOnline("20220425.2", "IDX_RES_TYPE"); 1091 } 1092 1093 /** 1094 * Update string indexing 1095 * @see ca.uhn.fhir.jpa.search.builder.predicate.StringPredicateBuilder 1096 * @see ResourceIndexedSearchParamString 1097 */ 1098 { 1099 Builder.BuilderWithTableName tokenTable = version.onTable("HFJ_SPIDX_STRING"); 1100 1101 // add res_id, and partition_id so queries are covered without row-reads. 1102 tokenTable 1103 .addIndex("20220428.1", "IDX_SP_STRING_HASH_NRM_V2") 1104 .unique(false) 1105 .online(true) 1106 .withColumns("HASH_NORM_PREFIX", "SP_VALUE_NORMALIZED", "RES_ID", "PARTITION_ID"); 1107 tokenTable.dropIndexOnline("20220428.2", "IDX_SP_STRING_HASH_NRM"); 1108 1109 tokenTable 1110 .addIndex("20220428.3", "IDX_SP_STRING_HASH_EXCT_V2") 1111 .unique(false) 1112 .online(true) 1113 .withColumns("HASH_EXACT", "RES_ID", "PARTITION_ID"); 1114 tokenTable.dropIndexOnline("20220428.4", "IDX_SP_STRING_HASH_EXCT"); 1115 1116 // we will drop the updated column. Start with the index. 1117 tokenTable.dropIndexOnline("20220428.5", "IDX_SP_STRING_UPDATED"); 1118 } 1119 1120 // Update tag indexing 1121 { 1122 Builder.BuilderWithTableName resTagTable = version.onTable("HFJ_RES_TAG"); 1123 1124 // add res_id, and partition_id so queries are covered without row-reads. 1125 resTagTable 1126 .addIndex("20220429.1", "IDX_RES_TAG_RES_TAG") 1127 .unique(false) 1128 .online(true) 1129 .withColumns("RES_ID", "TAG_ID", "PARTITION_ID"); 1130 resTagTable 1131 .addIndex("20220429.2", "IDX_RES_TAG_TAG_RES") 1132 .unique(false) 1133 .online(true) 1134 .withColumns("TAG_ID", "RES_ID", "PARTITION_ID"); 1135 1136 resTagTable.dropIndex("20220429.4", "IDX_RESTAG_TAGID"); 1137 // Weird that we don't have addConstraint. No time to do it today. 1138 Map<DriverTypeEnum, String> addResTagConstraint = new HashMap<>(); 1139 addResTagConstraint.put( 1140 DriverTypeEnum.H2_EMBEDDED, 1141 "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 1142 addResTagConstraint.put( 1143 DriverTypeEnum.MARIADB_10_1, 1144 "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 1145 addResTagConstraint.put( 1146 DriverTypeEnum.MSSQL_2012, 1147 "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 1148 addResTagConstraint.put( 1149 DriverTypeEnum.MYSQL_5_7, 1150 "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 1151 addResTagConstraint.put( 1152 DriverTypeEnum.ORACLE_12C, 1153 "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 1154 addResTagConstraint.put( 1155 DriverTypeEnum.POSTGRES_9_4, 1156 "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 1157 version.executeRawSql("20220429.5", addResTagConstraint); 1158 1159 Builder.BuilderWithTableName tagTable = version.onTable("HFJ_TAG_DEF"); 1160 tagTable.addIndex("20220429.6", "IDX_TAG_DEF_TP_CD_SYS") 1161 .unique(false) 1162 .online(false) 1163 .withColumns("TAG_TYPE", "TAG_CODE", "TAG_SYSTEM", "TAG_ID"); 1164 // move constraint to new index 1165 // Ugh. Only oracle supports using IDX_TAG_DEF_TP_CD_SYS to enforce this constraint. The others will 1166 // create another index. 1167 // For Sql Server, should change the index to be unique with include columns. Do this in 6.1 1168 // tagTable.dropIndex("20220429.8", "IDX_TAGDEF_TYPESYSCODE"); 1169 // Map<DriverTypeEnum, String> addTagDefConstraint = new HashMap<>(); 1170 // addTagDefConstraint.put( 1171 // DriverTypeEnum.H2_EMBEDDED, 1172 // "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, 1173 // TAG_SYSTEM)"); 1174 // addTagDefConstraint.put( 1175 // DriverTypeEnum.MARIADB_10_1, 1176 // "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, 1177 // TAG_SYSTEM)"); 1178 // addTagDefConstraint.put( 1179 // DriverTypeEnum.MSSQL_2012, 1180 // "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, 1181 // TAG_SYSTEM)"); 1182 // addTagDefConstraint.put( 1183 // DriverTypeEnum.MYSQL_5_7, 1184 // "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, 1185 // TAG_SYSTEM)"); 1186 // addTagDefConstraint.put( 1187 // DriverTypeEnum.ORACLE_12C, 1188 // "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, 1189 // TAG_SYSTEM)"); 1190 // addTagDefConstraint.put( 1191 // DriverTypeEnum.POSTGRES_9_4, 1192 // "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, 1193 // TAG_SYSTEM)"); 1194 // version.executeRawSql("20220429.9", addTagDefConstraint); 1195 version.addNop("20220429.9"); 1196 } 1197 1198 // Fix for https://github.com/hapifhir/hapi-fhir-jpaserver-starter/issues/328 1199 version.onTable("NPM_PACKAGE_VER") 1200 .modifyColumn("20220501.1", "FHIR_VERSION_ID") 1201 .nonNullable() 1202 .withType(ColumnTypeEnum.STRING, 20); 1203 1204 version.onTable("NPM_PACKAGE_VER_RES") 1205 .modifyColumn("20220501.2", "FHIR_VERSION_ID") 1206 .nonNullable() 1207 .withType(ColumnTypeEnum.STRING, 20); 1208 1209 // Fix for https://gitlab.com/simpatico.ai/cdr/-/issues/3166 1210 version.onTable("MPI_LINK") 1211 .addIndex("20220613.1", "IDX_EMPI_MATCH_TGT_VER") 1212 .unique(false) 1213 .online(true) 1214 .withColumns("MATCH_RESULT", "TARGET_PID", "VERSION"); 1215 } 1216 1217 /** 1218 * new numeric search indexing 1219 * 1220 * @see ca.uhn.fhir.jpa.search.builder.predicate.NumberPredicateBuilder 1221 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamNumber 1222 */ 1223 private void replaceNumericSPIndices(Builder theVersion) { 1224 Builder.BuilderWithTableName numberTable = theVersion.onTable("HFJ_SPIDX_NUMBER"); 1225 1226 // Main query index 1227 numberTable 1228 .addIndex("20220304.1", "IDX_SP_NUMBER_HASH_VAL_V2") 1229 .unique(false) 1230 .online(true) 1231 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 1232 1233 numberTable.dropIndexOnline("20220304.2", "IDX_SP_NUMBER_HASH_VAL"); 1234 1235 // for joining to other queries 1236 { 1237 numberTable 1238 .addIndex("20220304.3", "IDX_SP_NUMBER_RESID_V2") 1239 .unique(false) 1240 .online(true) 1241 .withColumns("RES_ID", "HASH_IDENTITY", "SP_VALUE", "PARTITION_ID"); 1242 1243 // some engines tie the FK constraint to a particular index. 1244 // So we need to drop and recreate the constraint to drop the old RES_ID index. 1245 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 1246 numberTable.dropForeignKey("20220304.4", "FKCLTIHNC5TGPRJ9BHPT7XI5OTB", "HFJ_RESOURCE"); 1247 numberTable.dropIndexOnline("20220304.5", "IDX_SP_NUMBER_RESID"); 1248 numberTable.dropIndexOnline("20220304.6", "FKCLTIHNC5TGPRJ9BHPT7XI5OTB"); 1249 1250 numberTable 1251 .addForeignKey("20220304.7", "FK_SP_NUMBER_RES") 1252 .toColumn("RES_ID") 1253 .references("HFJ_RESOURCE", "RES_ID"); 1254 } 1255 // obsolete 1256 numberTable.dropIndexOnline("20220304.8", "IDX_SP_NUMBER_UPDATED"); 1257 } 1258 1259 /** 1260 * new quantity search indexing 1261 * 1262 * @see ca.uhn.fhir.jpa.search.builder.predicate.QuantityPredicateBuilder 1263 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantity 1264 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantityNormalized 1265 */ 1266 private void replaceQuantitySPIndices(Builder theVersion) { 1267 { 1268 Builder.BuilderWithTableName quantityTable = theVersion.onTable("HFJ_SPIDX_QUANTITY"); 1269 1270 // bare quantity 1271 quantityTable 1272 .addIndex("20220304.11", "IDX_SP_QUANTITY_HASH_V2") 1273 .unique(false) 1274 .online(true) 1275 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 1276 1277 quantityTable.dropIndexOnline("20220304.12", "IDX_SP_QUANTITY_HASH"); 1278 1279 // quantity with system+units 1280 quantityTable 1281 .addIndex("20220304.13", "IDX_SP_QUANTITY_HASH_SYSUN_V2") 1282 .unique(false) 1283 .online(true) 1284 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 1285 1286 quantityTable.dropIndexOnline("20220304.14", "IDX_SP_QUANTITY_HASH_SYSUN"); 1287 1288 // quantity with units 1289 quantityTable 1290 .addIndex("20220304.15", "IDX_SP_QUANTITY_HASH_UN_V2") 1291 .unique(false) 1292 .online(true) 1293 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 1294 1295 quantityTable.dropIndexOnline("20220304.16", "IDX_SP_QUANTITY_HASH_UN"); 1296 1297 // for joining to other queries and sorts 1298 { 1299 quantityTable 1300 .addIndex("20220304.17", "IDX_SP_QUANTITY_RESID_V2") 1301 .unique(false) 1302 .online(true) 1303 .withColumns( 1304 "RES_ID", 1305 "HASH_IDENTITY", 1306 "HASH_IDENTITY_SYS_UNITS", 1307 "HASH_IDENTITY_AND_UNITS", 1308 "SP_VALUE", 1309 "PARTITION_ID"); 1310 1311 // some engines tie the FK constraint to a particular index. 1312 // So we need to drop and recreate the constraint to drop the old RES_ID index. 1313 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 1314 quantityTable.dropForeignKey("20220304.18", "FKN603WJJOI1A6ASEWXBBD78BI5", "HFJ_RESOURCE"); 1315 quantityTable.dropIndexOnline("20220304.19", "IDX_SP_QUANTITY_RESID"); 1316 quantityTable.dropIndexOnline("20220304.20", "FKN603WJJOI1A6ASEWXBBD78BI5"); 1317 1318 quantityTable 1319 .addForeignKey("20220304.21", "FK_SP_QUANTITY_RES") 1320 .toColumn("RES_ID") 1321 .references("HFJ_RESOURCE", "RES_ID"); 1322 } 1323 // obsolete 1324 quantityTable.dropIndexOnline("20220304.22", "IDX_SP_QUANTITY_UPDATED"); 1325 } 1326 1327 { 1328 Builder.BuilderWithTableName quantityNormTable = theVersion.onTable("HFJ_SPIDX_QUANTITY_NRML"); 1329 1330 // bare quantity 1331 quantityNormTable 1332 .addIndex("20220304.23", "IDX_SP_QNTY_NRML_HASH_V2") 1333 .unique(false) 1334 .online(true) 1335 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 1336 1337 quantityNormTable.dropIndexOnline("20220304.24", "IDX_SP_QNTY_NRML_HASH"); 1338 1339 // quantity with system+units 1340 quantityNormTable 1341 .addIndex("20220304.25", "IDX_SP_QNTY_NRML_HASH_SYSUN_V2") 1342 .unique(false) 1343 .online(true) 1344 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 1345 1346 quantityNormTable.dropIndexOnline("20220304.26", "IDX_SP_QNTY_NRML_HASH_SYSUN"); 1347 1348 // quantity with units 1349 quantityNormTable 1350 .addIndex("20220304.27", "IDX_SP_QNTY_NRML_HASH_UN_V2") 1351 .unique(false) 1352 .online(true) 1353 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 1354 1355 quantityNormTable.dropIndexOnline("20220304.28", "IDX_SP_QNTY_NRML_HASH_UN"); 1356 1357 // for joining to other queries and sorts 1358 { 1359 quantityNormTable 1360 .addIndex("20220304.29", "IDX_SP_QNTY_NRML_RESID_V2") 1361 .unique(false) 1362 .online(true) 1363 .withColumns( 1364 "RES_ID", 1365 "HASH_IDENTITY", 1366 "HASH_IDENTITY_SYS_UNITS", 1367 "HASH_IDENTITY_AND_UNITS", 1368 "SP_VALUE", 1369 "PARTITION_ID"); 1370 1371 // some engines tie the FK constraint to a particular index. 1372 // So we need to drop and recreate the constraint to drop the old RES_ID index. 1373 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 1374 quantityNormTable.dropForeignKey("20220304.30", "FKRCJOVMUH5KC0O6FVBLE319PYV", "HFJ_RESOURCE"); 1375 quantityNormTable.dropIndexOnline("20220304.31", "IDX_SP_QNTY_NRML_RESID"); 1376 quantityNormTable.dropIndexOnline("20220304.32", "FKRCJOVMUH5KC0O6FVBLE319PYV"); 1377 1378 quantityNormTable 1379 .addForeignKey("20220304.33", "FK_SP_QUANTITYNM_RES") 1380 .toColumn("RES_ID") 1381 .references("HFJ_RESOURCE", "RES_ID"); 1382 } 1383 // obsolete 1384 quantityNormTable.dropIndexOnline("20220304.34", "IDX_SP_QNTY_NRML_UPDATED"); 1385 } 1386 } 1387 1388 /** 1389 * See https://github.com/hapifhir/hapi-fhir/issues/3237 for reasoning for these indexes. 1390 * This adds indexes to various tables to enhance delete-expunge performance, which deletes by PID. 1391 */ 1392 private void addIndexesForDeleteExpunge(Builder theVersion) { 1393 1394 theVersion 1395 .onTable("HFJ_HISTORY_TAG") 1396 .addIndex("20211210.2", "IDX_RESHISTTAG_RESID") 1397 .unique(false) 1398 .withColumns("RES_ID"); 1399 1400 theVersion 1401 .onTable("HFJ_RES_VER_PROV") 1402 .addIndex("20211210.3", "FK_RESVERPROV_RES_PID") 1403 .unique(false) 1404 .withColumns("RES_PID") 1405 .doNothing() // This index is added below in a better form 1406 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1407 1408 theVersion 1409 .onTable("HFJ_FORCED_ID") 1410 .addIndex("20211210.4", "FK_FORCEDID_RESOURCE") 1411 .unique(true) 1412 .withColumns("RESOURCE_PID") 1413 // RESOURCE_PID and every database creates an index on anything that is unique. 1414 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS) 1415 .doNothing(); // This migration was added in error, as this table already has a unique constraint on 1416 } 1417 1418 private void init570() { 1419 Builder version = forVersion(VersionEnum.V5_7_0); 1420 1421 // both indexes must have same name that indexed FK or SchemaMigrationTest complains because H2 sets this index 1422 // automatically 1423 1424 version.onTable("TRM_CONCEPT_PROPERTY") 1425 .addIndex("20211102.1", "FK_CONCEPTPROP_CONCEPT") 1426 .unique(false) 1427 .withColumns("CONCEPT_PID") 1428 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1429 1430 version.onTable("TRM_CONCEPT_DESIG") 1431 .addIndex("20211102.2", "FK_CONCEPTDESIG_CONCEPT") 1432 .unique(false) 1433 .withColumns("CONCEPT_PID") 1434 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 1435 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1436 1437 version.onTable("TRM_CONCEPT_PC_LINK") 1438 .addIndex("20211102.3", "FK_TERM_CONCEPTPC_CHILD") 1439 .unique(false) 1440 .withColumns("CHILD_PID") 1441 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 1442 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1443 1444 version.onTable("TRM_CONCEPT_PC_LINK") 1445 .addIndex("20211102.4", "FK_TERM_CONCEPTPC_PARENT") 1446 .unique(false) 1447 .withColumns("PARENT_PID") 1448 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 1449 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1450 1451 addIndexesForDeleteExpunge(version); 1452 1453 // Add inline resource text column 1454 version.onTable("HFJ_RES_VER") 1455 .addColumn("20220102.1", "RES_TEXT_VC") 1456 .nullable() 1457 .type(ColumnTypeEnum.STRING, 4000); 1458 1459 // Add partition id column for mdm 1460 Builder.BuilderWithTableName empiLink = version.onTable("MPI_LINK"); 1461 1462 empiLink.addColumn("20220324.1", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1463 empiLink.addColumn("20220324.2", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1464 } 1465 1466 private void init560() { 1467 init560_20211027(); 1468 } 1469 1470 /** 1471 * Mirgation for the batch job parameter size change. Overriding purposes only. 1472 */ 1473 protected void init560_20211027() { 1474 // nothing 1475 } 1476 1477 private void init550() { 1478 1479 Builder version = forVersion(VersionEnum.V5_5_0); 1480 1481 // For MSSQL only - Replace ForcedId index with a version that has an INCLUDE clause 1482 Builder.BuilderWithTableName forcedId = version.onTable("HFJ_FORCED_ID"); 1483 forcedId.dropIndex("20210516.1", "IDX_FORCEDID_TYPE_FID") 1484 .onlyAppliesToPlatforms(DriverTypeEnum.MSSQL_2012) 1485 .runEvenDuringSchemaInitialization(); 1486 forcedId.addIndex("20210516.2", "IDX_FORCEDID_TYPE_FID") 1487 .unique(true) 1488 .includeColumns("RESOURCE_PID") 1489 .withColumns("RESOURCE_TYPE", "FORCED_ID") 1490 .onlyAppliesToPlatforms(DriverTypeEnum.MSSQL_2012) 1491 .runEvenDuringSchemaInitialization(); 1492 1493 // Add bulk import file description 1494 version.onTable("HFJ_BLK_IMPORT_JOBFILE") 1495 .addColumn("20210528.1", "FILE_DESCRIPTION") 1496 .nullable() 1497 .type(ColumnTypeEnum.STRING, 500); 1498 1499 // Bump ConceptMap display lengths 1500 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1501 .modifyColumn("20210617.1", "TARGET_DISPLAY") 1502 .nullable() 1503 .withType(ColumnTypeEnum.STRING, 500); 1504 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1505 .modifyColumn("20210617.2", "SOURCE_DISPLAY") 1506 .nullable() 1507 .withType(ColumnTypeEnum.STRING, 500); 1508 1509 version.onTable("HFJ_BLK_EXPORT_JOB") 1510 .modifyColumn("20210624.1", "REQUEST") 1511 .nonNullable() 1512 .withType(ColumnTypeEnum.STRING, 1024); 1513 1514 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 1515 .modifyColumn("20210713.1", "IDX_STRING") 1516 .nonNullable() 1517 .withType(ColumnTypeEnum.STRING, 500); 1518 1519 version.onTable("HFJ_RESOURCE") 1520 .addColumn("20210720.1", "SP_CMPTOKS_PRESENT") 1521 .nullable() 1522 .type(ColumnTypeEnum.BOOLEAN); 1523 1524 version.addIdGenerator("20210720.2", "SEQ_IDXCMBTOKNU_ID"); 1525 1526 Builder.BuilderAddTableByColumns cmpToks = version.addTableByColumns("20210720.3", "HFJ_IDX_CMB_TOK_NU", "PID"); 1527 cmpToks.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1528 cmpToks.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1529 cmpToks.addColumn("HASH_COMPLETE").nonNullable().type(ColumnTypeEnum.LONG); 1530 cmpToks.addColumn("IDX_STRING").nonNullable().type(ColumnTypeEnum.STRING, 500); 1531 cmpToks.addForeignKey("20210720.4", "FK_IDXCMBTOKNU_RES_ID") 1532 .toColumn("RES_ID") 1533 .references("HFJ_RESOURCE", "RES_ID"); 1534 cmpToks.addIndex("20210720.5", "IDX_IDXCMBTOKNU_STR").unique(false).withColumns("IDX_STRING"); 1535 cmpToks.addIndex("20210720.6", "IDX_IDXCMBTOKNU_RES").unique(false).withColumns("RES_ID"); 1536 1537 Builder.BuilderWithTableName cmbTokNuTable = version.onTable("HFJ_IDX_CMB_TOK_NU"); 1538 1539 cmbTokNuTable.addColumn("20210722.1", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1540 cmbTokNuTable.addColumn("20210722.2", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1541 cmbTokNuTable.modifyColumn("20210722.3", "RES_ID").nullable().withType(ColumnTypeEnum.LONG); 1542 1543 // Dropping index on the language column, as it's no longer in use. 1544 // TODO: After 2 releases from 5.5.0, drop the column too 1545 version.onTable("HFJ_RESOURCE").dropIndex("20210908.1", "IDX_RES_LANG"); 1546 1547 version.onTable("TRM_VALUESET") 1548 .addColumn("20210915.1", "EXPANDED_AT") 1549 .nullable() 1550 .type(ColumnTypeEnum.DATE_TIMESTAMP); 1551 1552 /* 1553 * Replace CLOB columns with BLOB columns 1554 */ 1555 1556 // TRM_VALUESET_CONCEPT.SOURCE_DIRECT_PARENT_PIDS 1557 version.onTable("TRM_VALUESET_CONCEPT") 1558 .migratePostgresTextClobToBinaryClob("20211003.1", "SOURCE_DIRECT_PARENT_PIDS"); 1559 1560 // TRM_CONCEPT.PARENT_PIDS 1561 version.onTable("TRM_CONCEPT").migratePostgresTextClobToBinaryClob("20211003.2", "PARENT_PIDS"); 1562 1563 // HFJ_SEARCH.SEARCH_QUERY_STRING 1564 version.onTable("HFJ_SEARCH").migratePostgresTextClobToBinaryClob("20211003.3", "SEARCH_QUERY_STRING"); 1565 } 1566 1567 private void init540() { 1568 1569 Builder version = forVersion(VersionEnum.V5_4_0); 1570 1571 // -- add index on HFJ_SPIDX_DATE 1572 version.onTable("HFJ_SPIDX_DATE") 1573 .addIndex("20210309.1", "IDX_SP_DATE_HASH_HIGH") 1574 .unique(false) 1575 .withColumns("HASH_IDENTITY", "SP_VALUE_HIGH") 1576 .doNothing(); 1577 1578 // -- add index on HFJ_FORCED_ID 1579 version.onTable("HFJ_FORCED_ID") 1580 .addIndex("20210309.2", "IDX_FORCEID_FID") 1581 .unique(false) 1582 .withColumns("FORCED_ID"); 1583 1584 // -- ValueSet Concept Fulltext Indexing 1585 version.onTable("TRM_VALUESET_CONCEPT") 1586 .addColumn("20210406.1", "INDEX_STATUS") 1587 .nullable() 1588 .type(ColumnTypeEnum.LONG); 1589 version.onTable("TRM_VALUESET_CONCEPT") 1590 .addColumn("20210406.2", "SOURCE_DIRECT_PARENT_PIDS") 1591 .nullable() 1592 .type(ColumnTypeEnum.CLOB); 1593 version.onTable("TRM_VALUESET_CONCEPT") 1594 .addColumn("20210406.3", "SOURCE_PID") 1595 .nullable() 1596 .type(ColumnTypeEnum.LONG); 1597 1598 // Bulk Import Job 1599 Builder.BuilderAddTableByColumns blkImportJobTable = 1600 version.addTableByColumns("20210410.1", "HFJ_BLK_IMPORT_JOB", "PID"); 1601 blkImportJobTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1602 blkImportJobTable.addColumn("JOB_ID").nonNullable().type(ColumnTypeEnum.STRING, UUID_LENGTH); 1603 blkImportJobTable.addColumn("JOB_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 10); 1604 blkImportJobTable.addColumn("STATUS_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1605 blkImportJobTable.addColumn("STATUS_MESSAGE").nullable().type(ColumnTypeEnum.STRING, 500); 1606 blkImportJobTable.addColumn("JOB_DESC").nullable().type(ColumnTypeEnum.STRING, 500); 1607 blkImportJobTable.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 1608 blkImportJobTable.addColumn("FILE_COUNT").nonNullable().type(ColumnTypeEnum.INT); 1609 blkImportJobTable.addColumn("ROW_PROCESSING_MODE").nonNullable().type(ColumnTypeEnum.STRING, 20); 1610 blkImportJobTable.addColumn("BATCH_SIZE").nonNullable().type(ColumnTypeEnum.INT); 1611 blkImportJobTable 1612 .addIndex("20210410.2", "IDX_BLKIM_JOB_ID") 1613 .unique(true) 1614 .withColumns("JOB_ID"); 1615 version.addIdGenerator("20210410.3", "SEQ_BLKIMJOB_PID"); 1616 1617 // Bulk Import Job File 1618 Builder.BuilderAddTableByColumns blkImportJobFileTable = 1619 version.addTableByColumns("20210410.4", "HFJ_BLK_IMPORT_JOBFILE", "PID"); 1620 blkImportJobFileTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1621 blkImportJobFileTable.addColumn("JOB_PID").nonNullable().type(ColumnTypeEnum.LONG); 1622 blkImportJobFileTable.addColumn("JOB_CONTENTS").nonNullable().type(ColumnTypeEnum.BLOB); 1623 blkImportJobFileTable.addColumn("FILE_SEQ").nonNullable().type(ColumnTypeEnum.INT); 1624 blkImportJobFileTable.addColumn("TENANT_NAME").nullable().type(ColumnTypeEnum.STRING, 200); 1625 blkImportJobFileTable 1626 .addIndex("20210410.5", "IDX_BLKIM_JOBFILE_JOBID") 1627 .unique(false) 1628 .withColumns("JOB_PID"); 1629 blkImportJobFileTable 1630 .addForeignKey("20210410.6", "FK_BLKIMJOBFILE_JOB") 1631 .toColumn("JOB_PID") 1632 .references("HFJ_BLK_IMPORT_JOB", "PID"); 1633 version.addIdGenerator("20210410.7", "SEQ_BLKIMJOBFILE_PID"); 1634 1635 // Increase ResourceLink path length 1636 version.onTable("HFJ_RES_LINK") 1637 .modifyColumn("20210505.1", "SRC_PATH") 1638 .nonNullable() 1639 .failureAllowed() 1640 .withType(ColumnTypeEnum.STRING, 500); 1641 } 1642 1643 private void init530() { 1644 Builder version = forVersion(VersionEnum.V5_3_0); 1645 1646 // -- TRM 1647 version.onTable("TRM_VALUESET_CONCEPT").dropIndex("20210104.1", "IDX_VS_CONCEPT_CS_CODE"); 1648 1649 version.onTable("TRM_VALUESET_CONCEPT") 1650 .addIndex("20210104.2", "IDX_VS_CONCEPT_CSCD") 1651 .unique(true) 1652 .withColumns("VALUESET_PID", "SYSTEM_URL", "CODEVAL"); 1653 1654 // -- Add new Table, HFJ_SPIDX_QUANTITY_NRML 1655 version.addIdGenerator("20210109.1", "SEQ_SPIDX_QUANTITY_NRML"); 1656 Builder.BuilderAddTableByColumns pkg = 1657 version.addTableByColumns("20210109.2", "HFJ_SPIDX_QUANTITY_NRML", "SP_ID"); 1658 pkg.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1659 pkg.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 100); 1660 pkg.addColumn("SP_UPDATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1661 pkg.addColumn("SP_MISSING").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1662 pkg.addColumn("SP_NAME").nonNullable().type(ColumnTypeEnum.STRING, 100); 1663 pkg.addColumn("SP_ID").nonNullable().type(ColumnTypeEnum.LONG); 1664 pkg.addColumn("SP_SYSTEM").nullable().type(ColumnTypeEnum.STRING, 200); 1665 pkg.addColumn("SP_UNITS").nullable().type(ColumnTypeEnum.STRING, 200); 1666 pkg.addColumn("HASH_IDENTITY_AND_UNITS").nullable().type(ColumnTypeEnum.LONG); 1667 pkg.addColumn("HASH_IDENTITY_SYS_UNITS").nullable().type(ColumnTypeEnum.LONG); 1668 pkg.addColumn("HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 1669 pkg.addColumn("SP_VALUE").nullable().type(ColumnTypeEnum.FLOAT); 1670 pkg.addIndex("20210109.3", "IDX_SP_QNTY_NRML_HASH").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE"); 1671 pkg.addIndex("20210109.4", "IDX_SP_QNTY_NRML_HASH_UN") 1672 .unique(false) 1673 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE"); 1674 pkg.addIndex("20210109.5", "IDX_SP_QNTY_NRML_HASH_SYSUN") 1675 .unique(false) 1676 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE"); 1677 pkg.addIndex("20210109.6", "IDX_SP_QNTY_NRML_UPDATED").unique(false).withColumns("SP_UPDATED"); 1678 pkg.addIndex("20210109.7", "IDX_SP_QNTY_NRML_RESID").unique(false).withColumns("RES_ID"); 1679 1680 // -- Link to the resourceTable 1681 version.onTable("HFJ_RESOURCE") 1682 .addColumn("20210109.10", "SP_QUANTITY_NRML_PRESENT") 1683 .nullable() 1684 .type(ColumnTypeEnum.BOOLEAN); 1685 1686 // -- Fixed the partition and fk 1687 Builder.BuilderWithTableName nrmlTable = version.onTable("HFJ_SPIDX_QUANTITY_NRML"); 1688 nrmlTable.addColumn("20210111.1", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1689 nrmlTable.addColumn("20210111.2", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1690 // Disabled - superceded by 20220304.33 1691 nrmlTable 1692 .addForeignKey("20210111.3", "FKRCJOVMUH5KC0O6FVBLE319PYV") 1693 .toColumn("RES_ID") 1694 .references("HFJ_RESOURCE", "RES_ID") 1695 .doNothing(); 1696 1697 Builder.BuilderWithTableName quantityTable = version.onTable("HFJ_SPIDX_QUANTITY"); 1698 quantityTable 1699 .modifyColumn("20210116.1", "SP_VALUE") 1700 .nullable() 1701 .failureAllowed() 1702 .withType(ColumnTypeEnum.DOUBLE); 1703 1704 // HFJ_RES_LINK 1705 version.onTable("HFJ_RES_LINK") 1706 .addColumn("20210126.1", "TARGET_RESOURCE_VERSION") 1707 .nullable() 1708 .type(ColumnTypeEnum.LONG); 1709 } 1710 1711 protected void init520() { 1712 Builder version = forVersion(VersionEnum.V5_2_0); 1713 1714 Builder.BuilderWithTableName mdmLink = version.onTable("MPI_LINK"); 1715 mdmLink.addColumn("20201029.1", "GOLDEN_RESOURCE_PID").nonNullable().type(ColumnTypeEnum.LONG); 1716 mdmLink.addColumn("20201029.2", "RULE_COUNT").nullable().type(ColumnTypeEnum.LONG); 1717 mdmLink.addForeignKey("20201029.3", "FK_EMPI_LINK_GOLDEN_RESOURCE") 1718 .toColumn("GOLDEN_RESOURCE_PID") 1719 .references("HFJ_RESOURCE", "RES_ID"); 1720 } 1721 1722 protected void init510() { 1723 Builder version = forVersion(VersionEnum.V5_1_0); 1724 1725 // NPM Packages 1726 version.addIdGenerator("20200610.1", "SEQ_NPM_PACK"); 1727 Builder.BuilderAddTableByColumns pkg = version.addTableByColumns("20200610.2", "NPM_PACKAGE", "PID"); 1728 pkg.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1729 pkg.addColumn("PACKAGE_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1730 pkg.addColumn("CUR_VERSION_ID").nullable().type(ColumnTypeEnum.STRING, 200); 1731 pkg.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1732 pkg.addColumn("PACKAGE_DESC").nullable().type(ColumnTypeEnum.STRING, 200); 1733 pkg.addIndex("20200610.3", "IDX_PACK_ID").unique(true).withColumns("PACKAGE_ID"); 1734 1735 version.addIdGenerator("20200610.4", "SEQ_NPM_PACKVER"); 1736 Builder.BuilderAddTableByColumns pkgVer = version.addTableByColumns("20200610.5", "NPM_PACKAGE_VER", "PID"); 1737 pkgVer.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1738 pkgVer.addColumn("PACKAGE_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1739 pkgVer.addColumn("VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1740 pkgVer.addColumn("PACKAGE_PID").nonNullable().type(ColumnTypeEnum.LONG); 1741 pkgVer.addColumn("BINARY_RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1742 pkgVer.addColumn("SAVED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1743 pkgVer.addColumn("PKG_DESC").nonNullable().type(ColumnTypeEnum.STRING, 200); 1744 pkgVer.addColumn("DESC_UPPER").nonNullable().type(ColumnTypeEnum.STRING, 200); 1745 pkgVer.addColumn("CURRENT_VERSION").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1746 pkgVer.addColumn("FHIR_VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 10); 1747 pkgVer.addColumn("FHIR_VERSION").nonNullable().type(ColumnTypeEnum.STRING, 10); 1748 pkgVer.addColumn("PACKAGE_SIZE_BYTES").nonNullable().type(ColumnTypeEnum.LONG); 1749 pkgVer.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1750 pkgVer.addForeignKey("20200610.6", "FK_NPM_PKV_PKG") 1751 .toColumn("PACKAGE_PID") 1752 .references("NPM_PACKAGE", "PID"); 1753 pkgVer.addForeignKey("20200610.7", "FK_NPM_PKV_RESID") 1754 .toColumn("BINARY_RES_ID") 1755 .references("HFJ_RESOURCE", "RES_ID"); 1756 pkgVer.addIndex("20200610.8", "IDX_PACKVER").unique(true).withColumns("PACKAGE_ID", "VERSION_ID"); 1757 1758 version.addIdGenerator("20200610.9", "SEQ_NPM_PACKVERRES"); 1759 Builder.BuilderAddTableByColumns pkgVerResAdd = 1760 version.addTableByColumns("20200610.10", "NPM_PACKAGE_VER_RES", "PID"); 1761 pkgVerResAdd.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1762 pkgVerResAdd.addColumn("PACKVER_PID").nonNullable().type(ColumnTypeEnum.LONG); 1763 pkgVerResAdd.addColumn("BINARY_RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1764 pkgVerResAdd.addColumn("FILE_DIR").nullable().type(ColumnTypeEnum.STRING, 200); 1765 pkgVerResAdd.addColumn("FILE_NAME").nullable().type(ColumnTypeEnum.STRING, 200); 1766 pkgVerResAdd.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 40); 1767 pkgVerResAdd.addColumn("CANONICAL_URL").nullable().type(ColumnTypeEnum.STRING, 200); 1768 pkgVerResAdd.addColumn("CANONICAL_VERSION").nullable().type(ColumnTypeEnum.STRING, 200); 1769 pkgVerResAdd.addColumn("FHIR_VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 10); 1770 pkgVerResAdd.addColumn("FHIR_VERSION").nonNullable().type(ColumnTypeEnum.STRING, 10); 1771 pkgVerResAdd.addColumn("RES_SIZE_BYTES").nonNullable().type(ColumnTypeEnum.LONG); 1772 pkgVerResAdd.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1773 pkgVerResAdd 1774 .addForeignKey("20200610.11", "FK_NPM_PACKVERRES_PACKVER") 1775 .toColumn("PACKVER_PID") 1776 .references("NPM_PACKAGE_VER", "PID"); 1777 pkgVerResAdd 1778 .addForeignKey("20200610.12", "FK_NPM_PKVR_RESID") 1779 .toColumn("BINARY_RES_ID") 1780 .references("HFJ_RESOURCE", "RES_ID"); 1781 pkgVerResAdd.addIndex("20200610.13", "IDX_PACKVERRES_URL").unique(false).withColumns("CANONICAL_URL"); 1782 1783 init510_20200610(); 1784 1785 Builder.BuilderWithTableName pkgVerMod = version.onTable("NPM_PACKAGE_VER"); 1786 pkgVerMod.modifyColumn("20200629.1", "PKG_DESC").nullable().withType(ColumnTypeEnum.STRING, 200); 1787 pkgVerMod.modifyColumn("20200629.2", "DESC_UPPER").nullable().withType(ColumnTypeEnum.STRING, 200); 1788 1789 init510_20200706_to_20200714(); 1790 1791 Builder.BuilderWithTableName empiLink = version.onTable("MPI_LINK"); 1792 empiLink.addColumn("20200715.1", "VERSION").nonNullable().type(ColumnTypeEnum.STRING, 16); 1793 empiLink.addColumn("20200715.2", "EID_MATCH").nullable().type(ColumnTypeEnum.BOOLEAN); 1794 empiLink.addColumn("20200715.3", "NEW_PERSON").nullable().type(ColumnTypeEnum.BOOLEAN); 1795 empiLink.addColumn("20200715.4", "VECTOR").nullable().type(ColumnTypeEnum.LONG); 1796 empiLink.addColumn("20200715.5", "SCORE").nullable().type(ColumnTypeEnum.FLOAT); 1797 1798 init510_20200725(); 1799 1800 // EMPI Target Type 1801 empiLink.addColumn("20200727.1", "TARGET_TYPE").nullable().type(ColumnTypeEnum.STRING, 40); 1802 1803 // ConceptMap add version for search 1804 Builder.BuilderWithTableName trmConceptMap = version.onTable("TRM_CONCEPT_MAP"); 1805 trmConceptMap.addColumn("20200910.1", "VER").nullable().type(ColumnTypeEnum.STRING, 200); 1806 trmConceptMap.dropIndex("20200910.2", "IDX_CONCEPT_MAP_URL").failureAllowed(); 1807 trmConceptMap.addIndex("20200910.3", "IDX_CONCEPT_MAP_URL").unique(true).withColumns("URL", "VER"); 1808 1809 // Term CodeSystem Version and Term ValueSet Version 1810 Builder.BuilderWithTableName trmCodeSystemVer = version.onTable("TRM_CODESYSTEM_VER"); 1811 trmCodeSystemVer 1812 .addIndex("20200923.1", "IDX_CODESYSTEM_AND_VER") 1813 .unique(true) 1814 .withColumns("CODESYSTEM_PID", "CS_VERSION_ID"); 1815 Builder.BuilderWithTableName trmValueSet = version.onTable("TRM_VALUESET"); 1816 trmValueSet.addColumn("20200923.2", "VER").nullable().type(ColumnTypeEnum.STRING, 200); 1817 trmValueSet.dropIndex("20200923.3", "IDX_VALUESET_URL").failureAllowed(); 1818 trmValueSet.addIndex("20200923.4", "IDX_VALUESET_URL").unique(true).withColumns("URL", "VER"); 1819 1820 // Term ValueSet Component add system version 1821 Builder.BuilderWithTableName trmValueSetComp = version.onTable("TRM_VALUESET_CONCEPT"); 1822 trmValueSetComp.addColumn("20201028.1", "SYSTEM_VER").nullable().type(ColumnTypeEnum.STRING, 200); 1823 trmValueSetComp.dropIndex("20201028.2", "IDX_VS_CONCEPT_CS_CD").failureAllowed(); 1824 trmValueSetComp 1825 .addIndex("20201028.3", "IDX_VS_CONCEPT_CS_CODE") 1826 .unique(true) 1827 .withColumns("VALUESET_PID", "SYSTEM_URL", "SYSTEM_VER", "CODEVAL") 1828 .doNothing(); 1829 } 1830 1831 protected void init510_20200725() { 1832 // nothing 1833 } 1834 1835 protected void init510_20200610() { 1836 // nothing 1837 } 1838 1839 protected void init510_20200706_to_20200714() { 1840 // nothing 1841 } 1842 1843 private void init501() { // 20200514 - present 1844 Builder version = forVersion(VersionEnum.V5_0_1); 1845 1846 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 1847 spidxDate 1848 .addIndex("20200514.1", "IDX_SP_DATE_HASH_LOW") 1849 .unique(false) 1850 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW") 1851 .doNothing(); 1852 spidxDate 1853 .addIndex("20200514.2", "IDX_SP_DATE_ORD_HASH") 1854 .unique(false) 1855 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL", "SP_VALUE_HIGH_DATE_ORDINAL") 1856 .doNothing(); 1857 spidxDate 1858 .addIndex("20200514.3", "IDX_SP_DATE_ORD_HASH_LOW") 1859 .unique(false) 1860 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL") 1861 .doNothing(); 1862 1863 // MPI_LINK 1864 version.addIdGenerator("20200517.1", "SEQ_EMPI_LINK_ID"); 1865 Builder.BuilderAddTableByColumns empiLink = version.addTableByColumns("20200517.2", "MPI_LINK", "PID"); 1866 empiLink.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1867 1868 empiLink.addColumn("PERSON_PID").nonNullable().type(ColumnTypeEnum.LONG); 1869 empiLink.addForeignKey("20200517.3", "FK_EMPI_LINK_PERSON") 1870 .toColumn("PERSON_PID") 1871 .references("HFJ_RESOURCE", "RES_ID"); 1872 1873 empiLink.addColumn("TARGET_PID").nonNullable().type(ColumnTypeEnum.LONG); 1874 empiLink.addForeignKey("20200517.4", "FK_EMPI_LINK_TARGET") 1875 .toColumn("TARGET_PID") 1876 .references("HFJ_RESOURCE", "RES_ID"); 1877 1878 empiLink.addColumn("MATCH_RESULT").nonNullable().type(ColumnTypeEnum.INT); 1879 empiLink.addColumn("LINK_SOURCE").nonNullable().type(ColumnTypeEnum.INT); 1880 empiLink.addColumn("CREATED").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1881 empiLink.addColumn("UPDATED").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1882 1883 empiLink.addIndex("20200517.5", "IDX_EMPI_PERSON_TGT").unique(true).withColumns("PERSON_PID", "TARGET_PID"); 1884 } 1885 1886 protected void init500() { // 20200218 - 20200519 1887 Builder version = forVersion(VersionEnum.V5_0_0); 1888 1889 // Eliminate circular dependency. 1890 version.onTable("HFJ_RESOURCE").dropColumn("20200218.1", "FORCED_ID_PID"); 1891 version.onTable("HFJ_RES_VER").dropColumn("20200218.2", "FORCED_ID_PID"); 1892 version.onTable("HFJ_RES_VER") 1893 .addForeignKey("20200218.3", "FK_RESOURCE_HISTORY_RESOURCE") 1894 .toColumn("RES_ID") 1895 .references("HFJ_RESOURCE", "RES_ID"); 1896 version.onTable("HFJ_RES_VER") 1897 .modifyColumn("20200220.1", "RES_ID") 1898 .nonNullable() 1899 .failureAllowed() 1900 .withType(ColumnTypeEnum.LONG); 1901 // 1902 1903 // Drop unused column 1904 version.onTable("HFJ_RESOURCE").dropIndex("20200419.1", "IDX_RES_PROFILE"); 1905 version.onTable("HFJ_RESOURCE").dropColumn("20200419.2", "RES_PROFILE").failureAllowed(); 1906 1907 // Add Partitioning 1908 Builder.BuilderAddTableByColumns partition = 1909 version.addTableByColumns("20200420.0", "HFJ_PARTITION", "PART_ID"); 1910 partition.addColumn("PART_ID").nonNullable().type(ColumnTypeEnum.INT); 1911 partition.addColumn("PART_NAME").nonNullable().type(ColumnTypeEnum.STRING, 200); 1912 partition.addColumn("PART_DESC").nullable().type(ColumnTypeEnum.STRING, 200); 1913 partition.addIndex("20200420.1", "IDX_PART_NAME").unique(true).withColumns("PART_NAME"); 1914 1915 // Partition columns on individual tables 1916 version.onTable("HFJ_RESOURCE") 1917 .addColumn("20200420.2", "PARTITION_ID") 1918 .nullable() 1919 .type(ColumnTypeEnum.INT); 1920 version.onTable("HFJ_RESOURCE") 1921 .addColumn("20200420.3", "PARTITION_DATE") 1922 .nullable() 1923 .type(ColumnTypeEnum.DATE_ONLY); 1924 version.onTable("HFJ_RES_VER") 1925 .addColumn("20200420.4", "PARTITION_ID") 1926 .nullable() 1927 .type(ColumnTypeEnum.INT); 1928 version.onTable("HFJ_RES_VER") 1929 .addColumn("20200420.5", "PARTITION_DATE") 1930 .nullable() 1931 .type(ColumnTypeEnum.DATE_ONLY); 1932 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 1933 .addColumn("20200420.6", "PARTITION_ID") 1934 .nullable() 1935 .type(ColumnTypeEnum.INT); 1936 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 1937 .addColumn("20200420.7", "PARTITION_DATE") 1938 .nullable() 1939 .type(ColumnTypeEnum.DATE_ONLY); 1940 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 1941 .addColumn("20200420.8", "PARTITION_ID") 1942 .nullable() 1943 .type(ColumnTypeEnum.INT); 1944 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 1945 .addColumn("20200420.9", "PARTITION_DATE") 1946 .nullable() 1947 .type(ColumnTypeEnum.DATE_ONLY); 1948 version.onTable("HFJ_HISTORY_TAG") 1949 .addColumn("20200420.10", "PARTITION_ID") 1950 .nullable() 1951 .type(ColumnTypeEnum.INT); 1952 version.onTable("HFJ_HISTORY_TAG") 1953 .addColumn("20200420.11", "PARTITION_DATE") 1954 .nullable() 1955 .type(ColumnTypeEnum.DATE_ONLY); 1956 version.onTable("HFJ_RES_TAG") 1957 .addColumn("20200420.12", "PARTITION_ID") 1958 .nullable() 1959 .type(ColumnTypeEnum.INT); 1960 version.onTable("HFJ_RES_TAG") 1961 .addColumn("20200420.13", "PARTITION_DATE") 1962 .nullable() 1963 .type(ColumnTypeEnum.DATE_ONLY); 1964 version.onTable("HFJ_FORCED_ID") 1965 .addColumn("20200420.14", "PARTITION_ID") 1966 .nullable() 1967 .type(ColumnTypeEnum.INT); 1968 version.onTable("HFJ_FORCED_ID") 1969 .addColumn("20200420.15", "PARTITION_DATE") 1970 .nullable() 1971 .type(ColumnTypeEnum.DATE_ONLY); 1972 version.onTable("HFJ_RES_LINK") 1973 .addColumn("20200420.16", "PARTITION_ID") 1974 .nullable() 1975 .type(ColumnTypeEnum.INT); 1976 version.onTable("HFJ_RES_LINK") 1977 .addColumn("20200420.17", "PARTITION_DATE") 1978 .nullable() 1979 .type(ColumnTypeEnum.DATE_ONLY); 1980 version.onTable("HFJ_SPIDX_STRING") 1981 .addColumn("20200420.18", "PARTITION_ID") 1982 .nullable() 1983 .type(ColumnTypeEnum.INT); 1984 version.onTable("HFJ_SPIDX_STRING") 1985 .addColumn("20200420.19", "PARTITION_DATE") 1986 .nullable() 1987 .type(ColumnTypeEnum.DATE_ONLY); 1988 version.onTable("HFJ_SPIDX_COORDS") 1989 .addColumn("20200420.20", "PARTITION_ID") 1990 .nullable() 1991 .type(ColumnTypeEnum.INT); 1992 version.onTable("HFJ_SPIDX_COORDS") 1993 .addColumn("20200420.21", "PARTITION_DATE") 1994 .nullable() 1995 .type(ColumnTypeEnum.DATE_ONLY); 1996 version.onTable("HFJ_SPIDX_NUMBER") 1997 .addColumn("20200420.22", "PARTITION_ID") 1998 .nullable() 1999 .type(ColumnTypeEnum.INT); 2000 version.onTable("HFJ_SPIDX_NUMBER") 2001 .addColumn("20200420.23", "PARTITION_DATE") 2002 .nullable() 2003 .type(ColumnTypeEnum.DATE_ONLY); 2004 version.onTable("HFJ_SPIDX_TOKEN") 2005 .addColumn("20200420.24", "PARTITION_ID") 2006 .nullable() 2007 .type(ColumnTypeEnum.INT); 2008 version.onTable("HFJ_SPIDX_TOKEN") 2009 .addColumn("20200420.25", "PARTITION_DATE") 2010 .nullable() 2011 .type(ColumnTypeEnum.DATE_ONLY); 2012 version.onTable("HFJ_SPIDX_DATE") 2013 .addColumn("20200420.26", "PARTITION_ID") 2014 .nullable() 2015 .type(ColumnTypeEnum.INT); 2016 version.onTable("HFJ_SPIDX_DATE") 2017 .addColumn("20200420.27", "PARTITION_DATE") 2018 .nullable() 2019 .type(ColumnTypeEnum.DATE_ONLY); 2020 version.onTable("HFJ_SPIDX_URI") 2021 .addColumn("20200420.28", "PARTITION_ID") 2022 .nullable() 2023 .type(ColumnTypeEnum.INT); 2024 version.onTable("HFJ_SPIDX_URI") 2025 .addColumn("20200420.29", "PARTITION_DATE") 2026 .nullable() 2027 .type(ColumnTypeEnum.DATE_ONLY); 2028 version.onTable("HFJ_SPIDX_QUANTITY") 2029 .addColumn("20200420.30", "PARTITION_ID") 2030 .nullable() 2031 .type(ColumnTypeEnum.INT); 2032 version.onTable("HFJ_SPIDX_QUANTITY") 2033 .addColumn("20200420.31", "PARTITION_DATE") 2034 .nullable() 2035 .type(ColumnTypeEnum.DATE_ONLY); 2036 version.onTable("HFJ_RES_VER_PROV") 2037 .addColumn("20200420.32", "PARTITION_ID") 2038 .nullable() 2039 .type(ColumnTypeEnum.INT); 2040 version.onTable("HFJ_RES_VER_PROV") 2041 .addColumn("20200420.33", "PARTITION_DATE") 2042 .nullable() 2043 .type(ColumnTypeEnum.DATE_ONLY); 2044 version.onTable("HFJ_RES_PARAM_PRESENT") 2045 .addColumn("20200420.34", "PARTITION_ID") 2046 .nullable() 2047 .type(ColumnTypeEnum.INT); 2048 version.onTable("HFJ_RES_PARAM_PRESENT") 2049 .addColumn("20200420.35", "PARTITION_DATE") 2050 .nullable() 2051 .type(ColumnTypeEnum.DATE_ONLY); 2052 2053 version.onTable("HFJ_SPIDX_STRING") 2054 .modifyColumn("20200420.36", "SP_MISSING") 2055 .nonNullable() 2056 .failureAllowed() 2057 .withType(ColumnTypeEnum.BOOLEAN); 2058 version.onTable("HFJ_SPIDX_COORDS") 2059 .modifyColumn("20200420.37", "SP_MISSING") 2060 .nonNullable() 2061 .failureAllowed() 2062 .withType(ColumnTypeEnum.BOOLEAN); 2063 version.onTable("HFJ_SPIDX_NUMBER") 2064 .modifyColumn("20200420.38", "SP_MISSING") 2065 .nonNullable() 2066 .failureAllowed() 2067 .withType(ColumnTypeEnum.BOOLEAN); 2068 version.onTable("HFJ_SPIDX_TOKEN") 2069 .modifyColumn("20200420.39", "SP_MISSING") 2070 .nonNullable() 2071 .failureAllowed() 2072 .withType(ColumnTypeEnum.BOOLEAN); 2073 version.onTable("HFJ_SPIDX_DATE") 2074 .modifyColumn("20200420.40", "SP_MISSING") 2075 .nonNullable() 2076 .failureAllowed() 2077 .withType(ColumnTypeEnum.BOOLEAN); 2078 version.onTable("HFJ_SPIDX_URI") 2079 .modifyColumn("20200420.41", "SP_MISSING") 2080 .nonNullable() 2081 .failureAllowed() 2082 .withType(ColumnTypeEnum.BOOLEAN); 2083 version.onTable("HFJ_SPIDX_QUANTITY") 2084 .modifyColumn("20200420.42", "SP_MISSING") 2085 .nonNullable() 2086 .failureAllowed() 2087 .withType(ColumnTypeEnum.BOOLEAN); 2088 2089 // Add support for integer comparisons during day-precision date search. 2090 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 2091 spidxDate 2092 .addColumn("20200501.1", "SP_VALUE_LOW_DATE_ORDINAL") 2093 .nullable() 2094 .type(ColumnTypeEnum.INT); 2095 spidxDate 2096 .addColumn("20200501.2", "SP_VALUE_HIGH_DATE_ORDINAL") 2097 .nullable() 2098 .type(ColumnTypeEnum.INT); 2099 2100 spidxDate.addTask( 2101 new CalculateOrdinalDatesTask(VersionEnum.V5_0_0, "20200501.3") 2102 .addCalculator( 2103 "SP_VALUE_LOW_DATE_ORDINAL", 2104 t -> ResourceIndexedSearchParamDate.calculateOrdinalValue(t.getDate("SP_VALUE_LOW"))) 2105 .addCalculator( 2106 "SP_VALUE_HIGH_DATE_ORDINAL", 2107 t -> ResourceIndexedSearchParamDate.calculateOrdinalValue(t.getDate("SP_VALUE_HIGH"))) 2108 .setColumnName( 2109 "SP_VALUE_LOW_DATE_ORDINAL") // It doesn't matter which of the two we choose as they 2110 // will both be null. 2111 ); 2112 } 2113 2114 /** 2115 * Partway through the 4.3.0 releaase cycle we renumbered to 2116 * 5.0.0 - We have a bunch of NOP tasks here to avoid breakage for anyone 2117 * who installed a prerelease before we made the switch 2118 */ 2119 @SuppressWarnings("deprecation") 2120 private void init430() { 2121 Builder version = forVersion(VersionEnum.V4_3_0); 2122 version.addNop("20200218.1"); 2123 version.addNop("20200218.2"); 2124 version.addNop("20200218.3"); 2125 version.addNop("20200220.1"); 2126 version.addNop("20200419.1"); 2127 version.addNop("20200419.2"); 2128 version.addNop("20200420.0"); 2129 version.addNop("20200420.1"); 2130 version.addNop("20200420.2"); 2131 version.addNop("20200420.3"); 2132 version.addNop("20200420.4"); 2133 version.addNop("20200420.5"); 2134 version.addNop("20200420.6"); 2135 version.addNop("20200420.7"); 2136 version.addNop("20200420.8"); 2137 version.addNop("20200420.9"); 2138 version.addNop("20200420.10"); 2139 version.addNop("20200420.11"); 2140 version.addNop("20200420.12"); 2141 version.addNop("20200420.13"); 2142 version.addNop("20200420.14"); 2143 version.addNop("20200420.15"); 2144 version.addNop("20200420.16"); 2145 version.addNop("20200420.17"); 2146 version.addNop("20200420.18"); 2147 version.addNop("20200420.19"); 2148 version.addNop("20200420.20"); 2149 version.addNop("20200420.21"); 2150 version.addNop("20200420.22"); 2151 version.addNop("20200420.23"); 2152 version.addNop("20200420.24"); 2153 version.addNop("20200420.25"); 2154 version.addNop("20200420.26"); 2155 version.addNop("20200420.27"); 2156 version.addNop("20200420.28"); 2157 version.addNop("20200420.29"); 2158 version.addNop("20200420.30"); 2159 version.addNop("20200420.31"); 2160 version.addNop("20200420.32"); 2161 version.addNop("20200420.33"); 2162 version.addNop("20200420.34"); 2163 version.addNop("20200420.35"); 2164 version.addNop("20200420.36"); 2165 version.addNop("20200420.37"); 2166 version.addNop("20200420.38"); 2167 version.addNop("20200420.39"); 2168 version.addNop("20200420.40"); 2169 version.addNop("20200420.41"); 2170 version.addNop("20200420.42"); 2171 } 2172 2173 protected void init420() { // 20191015 - 20200217 2174 Builder version = forVersion(VersionEnum.V4_2_0); 2175 2176 // TermValueSetConceptDesignation 2177 version.onTable("TRM_VALUESET_C_DESIGNATION") 2178 .dropIndex("20200202.1", "IDX_VALUESET_C_DSGNTN_VAL") 2179 .failureAllowed(); 2180 Builder.BuilderWithTableName searchTable = version.onTable("HFJ_SEARCH"); 2181 searchTable.dropIndex("20200203.1", "IDX_SEARCH_LASTRETURNED"); 2182 searchTable.dropColumn("20200203.2", "SEARCH_LAST_RETURNED"); 2183 searchTable.addIndex("20200203.3", "IDX_SEARCH_CREATED").unique(false).withColumns("CREATED"); 2184 } 2185 2186 protected void init410() { // 20190815 - 20191014 2187 Builder version = forVersion(VersionEnum.V4_1_0); 2188 2189 /* 2190 * Note: The following tasks are markes as failure allowed - This is because all we're 2191 * doing is setting a not-null on a column that will never be null anyway. Setting not null 2192 * fails on SQL Server because there is an index on this column... Which is dumb, but hey. 2193 */ 2194 version.onTable("HFJ_SPIDX_NUMBER") 2195 .modifyColumn("20190920.1", "RES_ID") 2196 .nonNullable() 2197 .failureAllowed() 2198 .withType(ColumnTypeEnum.LONG); 2199 version.onTable("HFJ_SPIDX_COORDS") 2200 .modifyColumn("20190920.2", "RES_ID") 2201 .nonNullable() 2202 .failureAllowed() 2203 .withType(ColumnTypeEnum.LONG); 2204 version.onTable("HFJ_SPIDX_TOKEN") 2205 .modifyColumn("20190920.3", "RES_ID") 2206 .nonNullable() 2207 .failureAllowed() 2208 .withType(ColumnTypeEnum.LONG); 2209 version.onTable("HFJ_SPIDX_STRING") 2210 .modifyColumn("20190920.4", "RES_ID") 2211 .nonNullable() 2212 .failureAllowed() 2213 .withType(ColumnTypeEnum.LONG); 2214 version.onTable("HFJ_SPIDX_DATE") 2215 .modifyColumn("20190920.5", "RES_ID") 2216 .nonNullable() 2217 .failureAllowed() 2218 .withType(ColumnTypeEnum.LONG); 2219 version.onTable("HFJ_SPIDX_QUANTITY") 2220 .modifyColumn("20190920.6", "RES_ID") 2221 .nonNullable() 2222 .failureAllowed() 2223 .withType(ColumnTypeEnum.LONG); 2224 version.onTable("HFJ_SPIDX_URI") 2225 .modifyColumn("20190920.7", "RES_ID") 2226 .nonNullable() 2227 .failureAllowed() 2228 .withType(ColumnTypeEnum.LONG); 2229 2230 // HFJ_SEARCH 2231 version.onTable("HFJ_SEARCH") 2232 .addColumn("20190921.1", "EXPIRY_OR_NULL") 2233 .nullable() 2234 .type(ColumnTypeEnum.DATE_TIMESTAMP); 2235 version.onTable("HFJ_SEARCH") 2236 .addColumn("20190921.2", "NUM_BLOCKED") 2237 .nullable() 2238 .type(ColumnTypeEnum.INT); 2239 2240 // HFJ_BLK_EXPORT_JOB 2241 version.addIdGenerator("20190921.3", "SEQ_BLKEXJOB_PID"); 2242 Builder.BuilderAddTableByColumns bulkExportJob = 2243 version.addTableByColumns("20190921.4", "HFJ_BLK_EXPORT_JOB", "PID"); 2244 bulkExportJob.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 2245 bulkExportJob.addColumn("JOB_ID").nonNullable().type(ColumnTypeEnum.STRING, 36); 2246 bulkExportJob.addColumn("JOB_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 10); 2247 bulkExportJob.addColumn("CREATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2248 bulkExportJob.addColumn("STATUS_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2249 bulkExportJob.addColumn("EXP_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2250 bulkExportJob.addColumn("REQUEST").nonNullable().type(ColumnTypeEnum.STRING, 500); 2251 bulkExportJob.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 2252 bulkExportJob.addColumn("EXP_SINCE").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2253 bulkExportJob.addColumn("STATUS_MESSAGE").nullable().type(ColumnTypeEnum.STRING, 500); 2254 bulkExportJob.addIndex("20190921.5", "IDX_BLKEX_EXPTIME").unique(false).withColumns("EXP_TIME"); 2255 bulkExportJob.addIndex("20190921.6", "IDX_BLKEX_JOB_ID").unique(true).withColumns("JOB_ID"); 2256 2257 // HFJ_BLK_EXPORT_COLLECTION 2258 version.addIdGenerator("20190921.7", "SEQ_BLKEXCOL_PID"); 2259 Builder.BuilderAddTableByColumns bulkExportCollection = 2260 version.addTableByColumns("20190921.8", "HFJ_BLK_EXPORT_COLLECTION", "PID"); 2261 bulkExportCollection.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 2262 bulkExportCollection.addColumn("JOB_PID").nonNullable().type(ColumnTypeEnum.LONG); 2263 bulkExportCollection 2264 .addForeignKey("20190921.9", "FK_BLKEXCOL_JOB") 2265 .toColumn("JOB_PID") 2266 .references("HFJ_BLK_EXPORT_JOB", "PID"); 2267 bulkExportCollection.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 40); 2268 bulkExportCollection.addColumn("TYPE_FILTER").nullable().type(ColumnTypeEnum.STRING, 1000); 2269 bulkExportCollection.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 2270 2271 // HFJ_BLK_EXPORT_COLFILE 2272 version.addIdGenerator("20190921.10", "SEQ_BLKEXCOLFILE_PID"); 2273 Builder.BuilderAddTableByColumns bulkExportCollectionFile = 2274 version.addTableByColumns("20190921.11", "HFJ_BLK_EXPORT_COLFILE", "PID"); 2275 bulkExportCollectionFile.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 2276 bulkExportCollectionFile.addColumn("COLLECTION_PID").nonNullable().type(ColumnTypeEnum.LONG); 2277 bulkExportCollectionFile.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 2278 bulkExportCollectionFile 2279 .addForeignKey("20190921.12", "FK_BLKEXCOLFILE_COLLECT") 2280 .toColumn("COLLECTION_PID") 2281 .references("HFJ_BLK_EXPORT_COLLECTION", "PID"); 2282 2283 // HFJ_RES_VER_PROV 2284 version.startSectionWithMessage("Processing bulkExportCollectionFile: HFJ_RES_VER_PROV"); 2285 Builder.BuilderAddTableByColumns resVerProv = 2286 version.addTableByColumns("20190921.13", "HFJ_RES_VER_PROV", "RES_VER_PID"); 2287 resVerProv.addColumn("RES_VER_PID").nonNullable().type(ColumnTypeEnum.LONG); 2288 resVerProv 2289 .addForeignKey("20190921.14", "FK_RESVERPROV_RESVER_PID") 2290 .toColumn("RES_VER_PID") 2291 .references("HFJ_RES_VER", "PID"); 2292 resVerProv.addColumn("RES_PID").nonNullable().type(ColumnTypeEnum.LONG); 2293 resVerProv 2294 .addForeignKey("20190921.15", "FK_RESVERPROV_RES_PID") 2295 .toColumn("RES_PID") 2296 .references("HFJ_RESOURCE", "RES_ID") 2297 .doNothing(); // Added below in a better form 2298 resVerProv.addColumn("SOURCE_URI").nullable().type(ColumnTypeEnum.STRING, 100); 2299 resVerProv.addColumn("REQUEST_ID").nullable().type(ColumnTypeEnum.STRING, 16); 2300 resVerProv 2301 .addIndex("20190921.16", "IDX_RESVERPROV_SOURCEURI") 2302 .unique(false) 2303 .withColumns("SOURCE_URI"); 2304 resVerProv 2305 .addIndex("20190921.17", "IDX_RESVERPROV_REQUESTID") 2306 .unique(false) 2307 .withColumns("REQUEST_ID"); 2308 2309 // TermValueSetConceptDesignation 2310 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET_C_DESIGNATION"); 2311 Builder.BuilderWithTableName termValueSetConceptDesignationTable = 2312 version.onTable("TRM_VALUESET_C_DESIGNATION"); 2313 termValueSetConceptDesignationTable 2314 .addColumn("20190921.18", "VALUESET_PID") 2315 .nonNullable() 2316 .type(ColumnTypeEnum.LONG); 2317 termValueSetConceptDesignationTable 2318 .addForeignKey("20190921.19", "FK_TRM_VSCD_VS_PID") 2319 .toColumn("VALUESET_PID") 2320 .references("TRM_VALUESET", "PID"); 2321 2322 // Drop HFJ_SEARCH_RESULT foreign keys 2323 version.onTable("HFJ_SEARCH_RESULT").dropForeignKey("20190921.20", "FK_SEARCHRES_RES", "HFJ_RESOURCE"); 2324 version.onTable("HFJ_SEARCH_RESULT").dropForeignKey("20190921.21", "FK_SEARCHRES_SEARCH", "HFJ_SEARCH"); 2325 2326 // TermValueSet 2327 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET"); 2328 Builder.BuilderWithTableName termValueSetTable = version.onTable("TRM_VALUESET"); 2329 termValueSetTable 2330 .addColumn("20190921.22", "TOTAL_CONCEPTS") 2331 .nonNullable() 2332 .type(ColumnTypeEnum.LONG); 2333 termValueSetTable 2334 .addColumn("20190921.23", "TOTAL_CONCEPT_DESIGNATIONS") 2335 .nonNullable() 2336 .type(ColumnTypeEnum.LONG); 2337 termValueSetTable.dropIndex("20190921.24", "IDX_VALUESET_EXP_STATUS"); 2338 2339 version.dropIdGenerator("20190921.25", "SEQ_SEARCHPARM_ID"); 2340 2341 // TermValueSetConcept 2342 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET_CONCEPT"); 2343 Builder.BuilderWithTableName termValueSetConceptTable = version.onTable("TRM_VALUESET_CONCEPT"); 2344 termValueSetConceptTable 2345 .addColumn("20190921.26", "VALUESET_ORDER") 2346 .nonNullable() 2347 .type(ColumnTypeEnum.INT); 2348 termValueSetConceptTable 2349 .addIndex("20190921.27", "IDX_VS_CONCEPT_ORDER") 2350 .unique(true) 2351 .withColumns("VALUESET_PID", "VALUESET_ORDER"); 2352 2353 // Account for RESTYPE_LEN column increasing from 30 to 40 2354 version.onTable("HFJ_RESOURCE") 2355 .modifyColumn("20191002.1", "RES_TYPE") 2356 .nonNullable() 2357 .failureAllowed() 2358 .withType(ColumnTypeEnum.STRING, 40); 2359 version.onTable("HFJ_RES_VER") 2360 .modifyColumn("20191002.2", "RES_TYPE") 2361 .nonNullable() 2362 .failureAllowed() 2363 .withType(ColumnTypeEnum.STRING, 40); 2364 version.onTable("HFJ_HISTORY_TAG") 2365 .modifyColumn("20191002.3", "RES_TYPE") 2366 .nonNullable() 2367 .failureAllowed() 2368 .withType(ColumnTypeEnum.STRING, 40); 2369 version.onTable("HFJ_RES_LINK") 2370 .modifyColumn("20191002.4", "SOURCE_RESOURCE_TYPE") 2371 .nonNullable() 2372 .failureAllowed() 2373 .withType(ColumnTypeEnum.STRING, 40); 2374 version.onTable("HFJ_RES_LINK") 2375 .modifyColumn("20191002.5", "TARGET_RESOURCE_TYPE") 2376 .nonNullable() 2377 .failureAllowed() 2378 .withType(ColumnTypeEnum.STRING, 40); 2379 version.onTable("HFJ_RES_TAG") 2380 .modifyColumn("20191002.6", "RES_TYPE") 2381 .nonNullable() 2382 .failureAllowed() 2383 .withType(ColumnTypeEnum.STRING, 40); 2384 2385 // TermConceptDesignation 2386 version.startSectionWithMessage("Processing table: TRM_CONCEPT_DESIG"); 2387 version.onTable("TRM_CONCEPT_DESIG") 2388 .modifyColumn("20191002.7", "VAL") 2389 .nonNullable() 2390 .withType(ColumnTypeEnum.STRING, 2000); 2391 2392 // TermValueSetConceptDesignation 2393 version.startSectionWithMessage("Processing table: TRM_VALUESET_C_DESIGNATION"); 2394 version.onTable("TRM_VALUESET_C_DESIGNATION") 2395 .modifyColumn("20191002.8", "VAL") 2396 .nonNullable() 2397 .withType(ColumnTypeEnum.STRING, 2000); 2398 2399 // TermConceptProperty 2400 version.startSectionWithMessage("Processing table: TRM_CONCEPT_PROPERTY"); 2401 version.onTable("TRM_CONCEPT_PROPERTY") 2402 .addColumn("20191002.9", "PROP_VAL_LOB") 2403 .nullable() 2404 .type(ColumnTypeEnum.BLOB); 2405 } 2406 2407 protected void init400() { // 20190401 - 20190814 2408 Builder version = forVersion(VersionEnum.V4_0_0); 2409 2410 // BinaryStorageEntity 2411 Builder.BuilderAddTableByColumns binaryBlob = 2412 version.addTableByColumns("20190722.1", "HFJ_BINARY_STORAGE_BLOB", "BLOB_ID"); 2413 binaryBlob.addColumn("BLOB_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 2414 binaryBlob.addColumn("RESOURCE_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 2415 binaryBlob.addColumn("BLOB_SIZE").nullable().type(ColumnTypeEnum.INT); 2416 binaryBlob.addColumn("CONTENT_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 100); 2417 binaryBlob.addColumn("BLOB_DATA").nonNullable().type(ColumnTypeEnum.BLOB); 2418 binaryBlob.addColumn("PUBLISHED_DATE").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2419 binaryBlob.addColumn("BLOB_HASH").nullable().type(ColumnTypeEnum.STRING, 128); 2420 2421 // Interim builds used this name 2422 version.onTable("TRM_VALUESET_CODE").dropThisTable("20190722.2"); 2423 2424 version.onTable("TRM_CONCEPT_MAP_GROUP") 2425 .renameColumn("20190722.3", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 2426 .renameColumn("20190722.4", "mySourceValueSet", "SOURCE_VS", false, true) 2427 .renameColumn("20190722.5", "myTargetValueSet", "TARGET_VS", false, true); 2428 version.onTable("TRM_CONCEPT_MAP_GROUP") 2429 .modifyColumn("20190722.6", "CONCEPT_MAP_URL") 2430 .nullable() 2431 .withType(ColumnTypeEnum.STRING, 200); 2432 version.onTable("TRM_CONCEPT_MAP_GROUP") 2433 .modifyColumn("20190722.7", "SOURCE_VERSION") 2434 .nullable() 2435 .withType(ColumnTypeEnum.STRING, 200); 2436 version.onTable("TRM_CONCEPT_MAP_GROUP") 2437 .modifyColumn("20190722.8", "SOURCE_VS") 2438 .nullable() 2439 .withType(ColumnTypeEnum.STRING, 200); 2440 version.onTable("TRM_CONCEPT_MAP_GROUP") 2441 .modifyColumn("20190722.9", "TARGET_VERSION") 2442 .nullable() 2443 .withType(ColumnTypeEnum.STRING, 200); 2444 version.onTable("TRM_CONCEPT_MAP_GROUP") 2445 .modifyColumn("20190722.10", "TARGET_VS") 2446 .nullable() 2447 .withType(ColumnTypeEnum.STRING, 200); 2448 2449 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 2450 .renameColumn("20190722.11", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 2451 .renameColumn("20190722.12", "mySystem", "SYSTEM_URL", false, true) 2452 .renameColumn("20190722.13", "mySystemVersion", "SYSTEM_VERSION", false, true) 2453 .renameColumn("20190722.14", "myValueSet", "VALUESET_URL", false, true); 2454 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 2455 .modifyColumn("20190722.15", "CONCEPT_MAP_URL") 2456 .nullable() 2457 .withType(ColumnTypeEnum.STRING, 200); 2458 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 2459 .modifyColumn("20190722.16", "SOURCE_CODE") 2460 .nonNullable() 2461 .withType(ColumnTypeEnum.STRING, 500); 2462 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 2463 .modifyColumn("20190722.17", "SYSTEM_URL") 2464 .nullable() 2465 .withType(ColumnTypeEnum.STRING, 200); 2466 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 2467 .modifyColumn("20190722.18", "SYSTEM_VERSION") 2468 .nullable() 2469 .withType(ColumnTypeEnum.STRING, 200); 2470 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 2471 .modifyColumn("20190722.19", "VALUESET_URL") 2472 .nullable() 2473 .withType(ColumnTypeEnum.STRING, 200); 2474 2475 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 2476 .renameColumn("20190722.20", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 2477 .renameColumn("20190722.21", "mySystem", "SYSTEM_URL", false, true) 2478 .renameColumn("20190722.22", "mySystemVersion", "SYSTEM_VERSION", false, true) 2479 .renameColumn("20190722.23", "myValueSet", "VALUESET_URL", false, true); 2480 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 2481 .modifyColumn("20190722.24", "CONCEPT_MAP_URL") 2482 .nullable() 2483 .withType(ColumnTypeEnum.STRING, 200); 2484 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 2485 .modifyColumn("20190722.25", "SYSTEM_URL") 2486 .nullable() 2487 .withType(ColumnTypeEnum.STRING, 200); 2488 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 2489 .modifyColumn("20190722.26", "SYSTEM_VERSION") 2490 .nullable() 2491 .withType(ColumnTypeEnum.STRING, 200); 2492 2493 /* 2494 DISABLED THIS STEP (20190722.27) ON PURPOSE BECAUSE IT STARTED CAUSING FAILURES ON MSSQL FOR A FRESH DB. 2495 I left it here for historical purposes. 2496 The reason for the failure is as follows. The TARGET_CODE column was originally 'not nullable' when it was 2497 first introduced. And in 7_2_0, it is being changed to a nullable column (see 20240327.1 in init720()). 2498 Starting with 7_2_0, on a fresh db, we create the table with nullable TARGET_CODE (as it is made nullable now). 2499 Since we run all migration steps on fresh db, this step will try to convert the column which is created as nullable 2500 to not nullable (which will then need to be coverted back to nullable in 7_2_0 migration). 2501 Changing a nullable column to not nullable is not allowed in 2502 MSSQL if there is an index on the column, which is the case here, as there is IDX_CNCPT_MP_GRP_ELM_TGT_CD 2503 on this column. Since init720() has the right migration 2504 step, where the column is set to nullable and has the right type and length, this statement is also 2505 not necessary anymore even for not fresh dbs. 2506 2507 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 2508 .modifyColumn("20190722.27", "TARGET_CODE") 2509 .nonNullable() 2510 .withType(ColumnTypeEnum.STRING, 500); 2511 */ 2512 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 2513 .modifyColumn("20190722.28", "VALUESET_URL") 2514 .nullable() 2515 .withType(ColumnTypeEnum.STRING, 200); 2516 2517 version.onTable("TRM_CONCEPT").renameColumn("20190722.29", "CODE", "CODEVAL", false, true); 2518 2519 // TermValueSet 2520 version.startSectionWithMessage("Processing table: TRM_VALUESET"); 2521 version.addIdGenerator("20190722.30", "SEQ_VALUESET_PID"); 2522 Builder.BuilderAddTableByColumns termValueSetTable = 2523 version.addTableByColumns("20190722.31", "TRM_VALUESET", "PID"); 2524 termValueSetTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 2525 termValueSetTable.addColumn("URL").nonNullable().type(ColumnTypeEnum.STRING, 200); 2526 termValueSetTable 2527 .addIndex("20190722.32", "IDX_VALUESET_URL") 2528 .unique(true) 2529 .withColumns("URL"); 2530 termValueSetTable.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 2531 termValueSetTable 2532 .addForeignKey("20190722.33", "FK_TRMVALUESET_RES") 2533 .toColumn("RES_ID") 2534 .references("HFJ_RESOURCE", "RES_ID"); 2535 termValueSetTable.addColumn("NAME").nullable().type(ColumnTypeEnum.STRING, 200); 2536 2537 version.onTable("TRM_VALUESET").renameColumn("20190722.34", "NAME", "VSNAME", true, true); 2538 version.onTable("TRM_VALUESET") 2539 .modifyColumn("20190722.35", "RES_ID") 2540 .nullable() 2541 .withType(ColumnTypeEnum.LONG); 2542 2543 Builder.BuilderWithTableName termValueSetTableChange = version.onTable("TRM_VALUESET"); 2544 termValueSetTableChange 2545 .addColumn("20190722.36", "EXPANSION_STATUS") 2546 .nonNullable() 2547 .type(ColumnTypeEnum.STRING, 50); 2548 termValueSetTableChange 2549 .addIndex("20190722.37", "IDX_VALUESET_EXP_STATUS") 2550 .unique(false) 2551 .withColumns("EXPANSION_STATUS"); 2552 2553 // TermValueSetConcept 2554 version.startSectionWithMessage("Processing table: TRM_VALUESET_CONCEPT"); 2555 version.addIdGenerator("20190722.38", "SEQ_VALUESET_CONCEPT_PID"); 2556 Builder.BuilderAddTableByColumns termValueSetConceptTable = 2557 version.addTableByColumns("20190722.39", "TRM_VALUESET_CONCEPT", "PID"); 2558 termValueSetConceptTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 2559 termValueSetConceptTable.addColumn("VALUESET_PID").nonNullable().type(ColumnTypeEnum.LONG); 2560 termValueSetConceptTable 2561 .addForeignKey("20190722.40", "FK_TRM_VALUESET_PID") 2562 .toColumn("VALUESET_PID") 2563 .references("TRM_VALUESET", "PID"); 2564 termValueSetConceptTable.addColumn("SYSTEM_URL").nonNullable().type(ColumnTypeEnum.STRING, 200); 2565 termValueSetConceptTable.addColumn("CODEVAL").nonNullable().type(ColumnTypeEnum.STRING, 500); 2566 termValueSetConceptTable.addColumn("DISPLAY").nullable().type(ColumnTypeEnum.STRING, 400); 2567 version.onTable("TRM_VALUESET_CONCEPT") 2568 .renameColumn("20190722.41", "CODE", "CODEVAL", true, true) 2569 .renameColumn("20190722.42", "SYSTEM", "SYSTEM_URL", true, true); 2570 2571 version.startSectionWithMessage("Processing table: TRM_VALUESET_CONCEPT, swapping index for unique constraint"); 2572 termValueSetConceptTable.dropIndex("20190801.1", "IDX_VALUESET_CONCEPT_CS_CD"); 2573 // This index has been renamed in later versions. As such, allowing failure here as some DBs disallow 2574 // multiple indexes referencing the same set of columns. 2575 termValueSetConceptTable 2576 .addIndex("20190801.2", "IDX_VS_CONCEPT_CS_CD") 2577 .unique(true) 2578 .withColumns("VALUESET_PID", "SYSTEM_URL", "CODEVAL") 2579 .failureAllowed(); 2580 2581 // TermValueSetConceptDesignation 2582 version.startSectionWithMessage("Processing table: TRM_VALUESET_C_DESIGNATION"); 2583 version.addIdGenerator("20190801.3", "SEQ_VALUESET_C_DSGNTN_PID"); 2584 Builder.BuilderAddTableByColumns termValueSetConceptDesignationTable = 2585 version.addTableByColumns("20190801.4", "TRM_VALUESET_C_DESIGNATION", "PID"); 2586 termValueSetConceptDesignationTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 2587 termValueSetConceptDesignationTable 2588 .addColumn("VALUESET_CONCEPT_PID") 2589 .nonNullable() 2590 .type(ColumnTypeEnum.LONG); 2591 termValueSetConceptDesignationTable 2592 .addForeignKey("20190801.5", "FK_TRM_VALUESET_CONCEPT_PID") 2593 .toColumn("VALUESET_CONCEPT_PID") 2594 .references("TRM_VALUESET_CONCEPT", "PID"); 2595 termValueSetConceptDesignationTable.addColumn("LANG").nullable().type(ColumnTypeEnum.STRING, 500); 2596 termValueSetConceptDesignationTable.addColumn("USE_SYSTEM").nullable().type(ColumnTypeEnum.STRING, 500); 2597 termValueSetConceptDesignationTable.addColumn("USE_CODE").nullable().type(ColumnTypeEnum.STRING, 500); 2598 termValueSetConceptDesignationTable.addColumn("USE_DISPLAY").nullable().type(ColumnTypeEnum.STRING, 500); 2599 termValueSetConceptDesignationTable.addColumn("VAL").nonNullable().type(ColumnTypeEnum.STRING, 500); 2600 2601 // This index turned out not to be needed so it is disabled 2602 termValueSetConceptDesignationTable 2603 .addIndex("20190801.6", "IDX_VALUESET_C_DSGNTN_VAL") 2604 .unique(false) 2605 .withColumns("VAL") 2606 .doNothing(); 2607 2608 // TermCodeSystemVersion 2609 version.startSectionWithMessage("Processing table: TRM_CODESYSTEM_VER"); 2610 Builder.BuilderWithTableName termCodeSystemVersionTable = version.onTable("TRM_CODESYSTEM_VER"); 2611 termCodeSystemVersionTable 2612 .addColumn("20190814.1", "CS_DISPLAY") 2613 .nullable() 2614 .type(ColumnTypeEnum.STRING, 200); 2615 2616 // ResourceReindexJobEntry 2617 version.addIdGenerator("20190814.2", "SEQ_RES_REINDEX_JOB"); 2618 Builder.BuilderAddTableByColumns reindex = 2619 version.addTableByColumns("20190814.3", "HFJ_RES_REINDEX_JOB", "PID"); 2620 reindex.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 2621 reindex.addColumn("RES_TYPE").nullable().type(ColumnTypeEnum.STRING, 100); 2622 reindex.addColumn("UPDATE_THRESHOLD_HIGH").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2623 reindex.addColumn("JOB_DELETED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 2624 reindex.addColumn("UPDATE_THRESHOLD_LOW").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2625 reindex.addColumn("SUSPENDED_UNTIL").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 2626 reindex.addColumn("REINDEX_COUNT").nullable().type(ColumnTypeEnum.INT); 2627 2628 // Search 2629 version.onTable("HFJ_SEARCH") 2630 .addColumn("20190814.4", "SEARCH_DELETED") 2631 .nullable() 2632 .type(ColumnTypeEnum.BOOLEAN); 2633 version.onTable("HFJ_SEARCH") 2634 .modifyColumn("20190814.5", "SEARCH_LAST_RETURNED") 2635 .nonNullable() 2636 .withType(ColumnTypeEnum.DATE_TIMESTAMP); 2637 version.onTable("HFJ_SEARCH") 2638 .addColumn("20190814.6", "SEARCH_PARAM_MAP") 2639 .nullable() 2640 .type(ColumnTypeEnum.BLOB); 2641 version.onTable("HFJ_SEARCH") 2642 .modifyColumn("20190814.7", "SEARCH_UUID") 2643 .nonNullable() 2644 .withType(ColumnTypeEnum.STRING, Search.SEARCH_UUID_COLUMN_LENGTH); 2645 2646 version.onTable("HFJ_SEARCH_PARM").dropThisTable("20190814.8"); 2647 2648 // Make some columns non-nullable that were previously nullable - These are marked as failure allowed, since 2649 // SQL Server won't let us change nullability on columns with indexes pointing to them 2650 version.onTable("HFJ_SPIDX_COORDS") 2651 .modifyColumn("20190814.9", "RES_TYPE") 2652 .nonNullable() 2653 .failureAllowed() 2654 .withType(ColumnTypeEnum.STRING, 100); 2655 version.onTable("HFJ_SPIDX_DATE") 2656 .modifyColumn("20190814.10", "RES_TYPE") 2657 .nonNullable() 2658 .failureAllowed() 2659 .withType(ColumnTypeEnum.STRING, 100); 2660 version.onTable("HFJ_SPIDX_STRING") 2661 .modifyColumn("20190814.11", "RES_TYPE") 2662 .nonNullable() 2663 .failureAllowed() 2664 .withType(ColumnTypeEnum.STRING, 100); 2665 version.onTable("HFJ_SPIDX_STRING") 2666 .addColumn("20190814.12", "HASH_IDENTITY") 2667 .nullable() 2668 .type(ColumnTypeEnum.LONG); 2669 version.onTable("HFJ_SPIDX_STRING") 2670 .addIndex("20190814.13", "IDX_SP_STRING_HASH_IDENT") 2671 .unique(false) 2672 .withColumns("HASH_IDENTITY"); 2673 version.onTable("HFJ_SPIDX_COORDS") 2674 .modifyColumn("20190814.14", "RES_TYPE") 2675 .nonNullable() 2676 .failureAllowed() 2677 .withType(ColumnTypeEnum.STRING, 100); 2678 version.onTable("HFJ_SPIDX_QUANTITY") 2679 .modifyColumn("20190814.15", "RES_TYPE") 2680 .nonNullable() 2681 .failureAllowed() 2682 .withType(ColumnTypeEnum.STRING, 100); 2683 version.onTable("HFJ_SPIDX_QUANTITY").dropColumn("20190814.16", "HASH_UNITS_AND_VALPREFIX"); 2684 version.onTable("HFJ_SPIDX_QUANTITY").dropColumn("20190814.17", "HASH_VALPREFIX"); 2685 version.onTable("HFJ_SPIDX_NUMBER") 2686 .modifyColumn("20190814.18", "RES_TYPE") 2687 .nonNullable() 2688 .failureAllowed() 2689 .withType(ColumnTypeEnum.STRING, 100); 2690 version.onTable("HFJ_SPIDX_TOKEN") 2691 .modifyColumn("20190814.19", "RES_TYPE") 2692 .nonNullable() 2693 .failureAllowed() 2694 .withType(ColumnTypeEnum.STRING, 100); 2695 version.onTable("HFJ_SPIDX_URI") 2696 .modifyColumn("20190814.20", "RES_TYPE") 2697 .nonNullable() 2698 .failureAllowed() 2699 .withType(ColumnTypeEnum.STRING, 100); 2700 version.onTable("HFJ_SPIDX_URI") 2701 .modifyColumn("20190814.21", "SP_URI") 2702 .nullable() 2703 .failureAllowed() 2704 .withType(ColumnTypeEnum.STRING, 254); 2705 version.onTable("TRM_CODESYSTEM") 2706 .modifyColumn("20190814.22", "CODE_SYSTEM_URI") 2707 .nonNullable() 2708 .failureAllowed() 2709 .withType(ColumnTypeEnum.STRING, 200); 2710 version.onTable("TRM_CODESYSTEM") 2711 .modifyColumn("20190814.23", "CS_NAME") 2712 .nullable() 2713 .failureAllowed() 2714 .withType(ColumnTypeEnum.STRING, 200); 2715 version.onTable("TRM_CODESYSTEM_VER") 2716 .modifyColumn("20190814.24", "CS_VERSION_ID") 2717 .nullable() 2718 .failureAllowed() 2719 .withType(ColumnTypeEnum.STRING, 200); 2720 } 2721 2722 private void init360() { // 20180918 - 20181112 2723 Builder version = forVersion(VersionEnum.V3_6_0); 2724 2725 // Resource Link 2726 Builder.BuilderWithTableName resourceLink = version.onTable("HFJ_RES_LINK"); 2727 version.startSectionWithMessage("Starting work on table: " + resourceLink.getTableName()); 2728 resourceLink.modifyColumn("20180929.1", "SRC_PATH").nonNullable().withType(ColumnTypeEnum.STRING, 200); 2729 2730 // Search 2731 Builder.BuilderWithTableName search = version.onTable("HFJ_SEARCH"); 2732 version.startSectionWithMessage("Starting work on table: " + search.getTableName()); 2733 search.addColumn("20181001.1", "OPTLOCK_VERSION").nullable().type(ColumnTypeEnum.INT); 2734 2735 version.addTableRawSql("20181104.1", "HFJ_RES_REINDEX_JOB") 2736 .addSql( 2737 DriverTypeEnum.MSSQL_2012, 2738 "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))") 2739 .addSql( 2740 DriverTypeEnum.DERBY_EMBEDDED, 2741 "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))") 2742 .addSql( 2743 DriverTypeEnum.MARIADB_10_1, 2744 "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))") 2745 .addSql( 2746 DriverTypeEnum.POSTGRES_9_4, 2747 "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))") 2748 .addSql( 2749 DriverTypeEnum.MYSQL_5_7, 2750 " 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))") 2751 .addSql( 2752 DriverTypeEnum.ORACLE_12C, 2753 "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))"); 2754 2755 version.onTable("TRM_CONCEPT_DESIG") 2756 .addColumn("20181104.2", "CS_VER_PID") 2757 .nullable() 2758 .type(ColumnTypeEnum.LONG); 2759 version.onTable("TRM_CONCEPT_DESIG") 2760 .addForeignKey("20181104.3", "FK_CONCEPTDESIG_CSV") 2761 .toColumn("CS_VER_PID") 2762 .references("TRM_CODESYSTEM_VER", "PID"); 2763 2764 version.onTable("TRM_CONCEPT_PROPERTY") 2765 .addColumn("20181104.4", "CS_VER_PID") 2766 .nullable() 2767 .type(ColumnTypeEnum.LONG); 2768 version.onTable("TRM_CONCEPT_PROPERTY") 2769 .addForeignKey("20181104.5", "FK_CONCEPTPROP_CSV") 2770 .toColumn("CS_VER_PID") 2771 .references("TRM_CODESYSTEM_VER", "PID"); 2772 2773 version.onTable("TRM_CONCEPT") 2774 .addColumn("20181104.6", "PARENT_PIDS") 2775 .nullable() 2776 .type(ColumnTypeEnum.CLOB); 2777 } 2778 2779 private void init350() { // 20180601 - 20180917 2780 Builder version = forVersion(VersionEnum.V3_5_0); 2781 2782 // Forced ID changes 2783 Builder.BuilderWithTableName forcedId = version.onTable("HFJ_FORCED_ID"); 2784 version.startSectionWithMessage("Starting work on table: " + forcedId.getTableName()); 2785 2786 forcedId.dropIndex("20180827.1", "IDX_FORCEDID_TYPE_FORCEDID"); 2787 forcedId.dropIndex("20180827.2", "IDX_FORCEDID_TYPE_RESID"); 2788 2789 forcedId.addIndex("20180827.3", "IDX_FORCEDID_TYPE_FID").unique(true).withColumns("RESOURCE_TYPE", "FORCED_ID"); 2790 2791 // Indexes - Coords 2792 Builder.BuilderWithTableName spidxCoords = version.onTable("HFJ_SPIDX_COORDS"); 2793 version.startSectionWithMessage("Starting work on table: " + spidxCoords.getTableName()); 2794 spidxCoords.addColumn("20180903.1", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 2795 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2796 spidxCoords.dropIndex("20180903.2", "IDX_SP_COORDS"); 2797 spidxCoords 2798 .addIndex("20180903.4", "IDX_SP_COORDS_HASH") 2799 .unique(false) 2800 .withColumns("HASH_IDENTITY", "SP_LATITUDE", "SP_LONGITUDE"); 2801 spidxCoords.addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.5") 2802 .addCalculator( 2803 "HASH_IDENTITY", 2804 t -> BaseResourceIndexedSearchParam.calculateHashIdentity( 2805 new PartitionSettings(), 2806 RequestPartitionId.defaultPartition(), 2807 t.getResourceType(), 2808 t.getString("SP_NAME"))) 2809 .setColumnName("HASH_IDENTITY")); 2810 } 2811 2812 // Indexes - Date 2813 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 2814 version.startSectionWithMessage("Starting work on table: " + spidxDate.getTableName()); 2815 spidxDate.addColumn("20180903.6", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 2816 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2817 spidxDate.dropIndex("20180903.7", "IDX_SP_TOKEN"); 2818 spidxDate 2819 .addIndex("20180903.8", "IDX_SP_DATE_HASH") 2820 .unique(false) 2821 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH") 2822 .doNothing(); 2823 spidxDate.dropIndex("20180903.9", "IDX_SP_DATE"); 2824 spidxDate.addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.10") 2825 .addCalculator( 2826 "HASH_IDENTITY", 2827 t -> BaseResourceIndexedSearchParam.calculateHashIdentity( 2828 new PartitionSettings(), 2829 RequestPartitionId.defaultPartition(), 2830 t.getResourceType(), 2831 t.getString("SP_NAME"))) 2832 .setColumnName("HASH_IDENTITY")); 2833 } 2834 2835 // Indexes - Number 2836 Builder.BuilderWithTableName spidxNumber = version.onTable("HFJ_SPIDX_NUMBER"); 2837 version.startSectionWithMessage("Starting work on table: " + spidxNumber.getTableName()); 2838 spidxNumber.addColumn("20180903.11", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 2839 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2840 spidxNumber.dropIndex("20180903.12", "IDX_SP_NUMBER"); 2841 spidxNumber 2842 .addIndex("20180903.13", "IDX_SP_NUMBER_HASH_VAL") 2843 .unique(false) 2844 .withColumns("HASH_IDENTITY", "SP_VALUE") 2845 .doNothing(); 2846 spidxNumber.addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.14") 2847 .addCalculator( 2848 "HASH_IDENTITY", 2849 t -> BaseResourceIndexedSearchParam.calculateHashIdentity( 2850 new PartitionSettings(), 2851 RequestPartitionId.defaultPartition(), 2852 t.getResourceType(), 2853 t.getString("SP_NAME"))) 2854 .setColumnName("HASH_IDENTITY")); 2855 } 2856 2857 // Indexes - Quantity 2858 Builder.BuilderWithTableName spidxQuantity = version.onTable("HFJ_SPIDX_QUANTITY"); 2859 version.startSectionWithMessage("Starting work on table: " + spidxQuantity.getTableName()); 2860 spidxQuantity.addColumn("20180903.15", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 2861 spidxQuantity 2862 .addColumn("20180903.16", "HASH_IDENTITY_SYS_UNITS") 2863 .nullable() 2864 .type(ColumnTypeEnum.LONG); 2865 spidxQuantity 2866 .addColumn("20180903.17", "HASH_IDENTITY_AND_UNITS") 2867 .nullable() 2868 .type(ColumnTypeEnum.LONG); 2869 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2870 spidxQuantity.dropIndex("20180903.18", "IDX_SP_QUANTITY"); 2871 spidxQuantity 2872 .addIndex("20180903.19", "IDX_SP_QUANTITY_HASH") 2873 .unique(false) 2874 .withColumns("HASH_IDENTITY", "SP_VALUE"); 2875 spidxQuantity 2876 .addIndex("20180903.20", "IDX_SP_QUANTITY_HASH_UN") 2877 .unique(false) 2878 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE"); 2879 spidxQuantity 2880 .addIndex("20180903.21", "IDX_SP_QUANTITY_HASH_SYSUN") 2881 .unique(false) 2882 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE"); 2883 spidxQuantity.addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.22") 2884 .addCalculator( 2885 "HASH_IDENTITY", 2886 t -> BaseResourceIndexedSearchParam.calculateHashIdentity( 2887 new PartitionSettings(), 2888 RequestPartitionId.defaultPartition(), 2889 t.getResourceType(), 2890 t.getString("SP_NAME"))) 2891 .addCalculator( 2892 "HASH_IDENTITY_AND_UNITS", 2893 t -> ResourceIndexedSearchParamQuantity.calculateHashUnits( 2894 new PartitionSettings(), 2895 RequestPartitionId.defaultPartition(), 2896 t.getResourceType(), 2897 t.getString("SP_NAME"), 2898 t.getString("SP_UNITS"))) 2899 .addCalculator( 2900 "HASH_IDENTITY_SYS_UNITS", 2901 t -> ResourceIndexedSearchParamQuantity.calculateHashSystemAndUnits( 2902 new PartitionSettings(), 2903 RequestPartitionId.defaultPartition(), 2904 t.getResourceType(), 2905 t.getString("SP_NAME"), 2906 t.getString("SP_SYSTEM"), 2907 t.getString("SP_UNITS"))) 2908 .setColumnName("HASH_IDENTITY")); 2909 } 2910 2911 // Indexes - String 2912 Builder.BuilderWithTableName spidxString = version.onTable("HFJ_SPIDX_STRING"); 2913 version.startSectionWithMessage("Starting work on table: " + spidxString.getTableName()); 2914 spidxString.addColumn("20180903.23", "HASH_NORM_PREFIX").nullable().type(ColumnTypeEnum.LONG); 2915 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2916 spidxString.dropIndex("20180903.24", "IDX_SP_STRING"); 2917 spidxString 2918 .addIndex("20180903.25", "IDX_SP_STRING_HASH_NRM") 2919 .unique(false) 2920 .withColumns("HASH_NORM_PREFIX", "SP_VALUE_NORMALIZED"); 2921 spidxString.addColumn("20180903.26", "HASH_EXACT").nullable().type(ColumnTypeEnum.LONG); 2922 spidxString 2923 .addIndex("20180903.27", "IDX_SP_STRING_HASH_EXCT") 2924 .unique(false) 2925 .withColumns("HASH_EXACT"); 2926 spidxString.addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.28") 2927 .setColumnName("HASH_NORM_PREFIX") 2928 .addCalculator( 2929 "HASH_NORM_PREFIX", 2930 t -> ResourceIndexedSearchParamString.calculateHashNormalized( 2931 new PartitionSettings(), 2932 RequestPartitionId.defaultPartition(), 2933 new StorageSettings(), 2934 t.getResourceType(), 2935 t.getString("SP_NAME"), 2936 t.getString("SP_VALUE_NORMALIZED"))) 2937 .addCalculator( 2938 "HASH_EXACT", 2939 t -> ResourceIndexedSearchParamString.calculateHashExact( 2940 new PartitionSettings(), 2941 (ca.uhn.fhir.jpa.model.entity.PartitionablePartitionId) null, 2942 t.getResourceType(), 2943 t.getParamName(), 2944 t.getString("SP_VALUE_EXACT")))); 2945 } 2946 2947 // Indexes - Token 2948 Builder.BuilderWithTableName spidxToken = version.onTable("HFJ_SPIDX_TOKEN"); 2949 version.startSectionWithMessage("Starting work on table: " + spidxToken.getTableName()); 2950 spidxToken.addColumn("20180903.29", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 2951 spidxToken.addColumn("20180903.30", "HASH_SYS").nullable().type(ColumnTypeEnum.LONG); 2952 spidxToken.addColumn("20180903.31", "HASH_SYS_AND_VALUE").nullable().type(ColumnTypeEnum.LONG); 2953 spidxToken.addColumn("20180903.32", "HASH_VALUE").nullable().type(ColumnTypeEnum.LONG); 2954 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2955 spidxToken.dropIndex("20180903.33", "IDX_SP_TOKEN"); 2956 spidxToken.dropIndex("20180903.34", "IDX_SP_TOKEN_UNQUAL"); 2957 spidxToken 2958 .addIndex("20180903.35", "IDX_SP_TOKEN_HASH") 2959 .unique(false) 2960 .withColumns("HASH_IDENTITY") 2961 .doNothing(); 2962 spidxToken 2963 .addIndex("20180903.36", "IDX_SP_TOKEN_HASH_S") 2964 .unique(false) 2965 .withColumns("HASH_SYS") 2966 .doNothing(); 2967 spidxToken 2968 .addIndex("20180903.37", "IDX_SP_TOKEN_HASH_SV") 2969 .unique(false) 2970 .withColumns("HASH_SYS_AND_VALUE") 2971 .doNothing(); 2972 spidxToken 2973 .addIndex("20180903.38", "IDX_SP_TOKEN_HASH_V") 2974 .unique(false) 2975 .withColumns("HASH_VALUE") 2976 .doNothing(); 2977 spidxToken.addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.39") 2978 .setColumnName("HASH_IDENTITY") 2979 .addCalculator( 2980 "HASH_IDENTITY", 2981 t -> BaseResourceIndexedSearchParam.calculateHashIdentity( 2982 new PartitionSettings(), 2983 RequestPartitionId.defaultPartition(), 2984 t.getResourceType(), 2985 t.getString("SP_NAME"))) 2986 .addCalculator( 2987 "HASH_SYS", 2988 t -> ResourceIndexedSearchParamToken.calculateHashSystem( 2989 new PartitionSettings(), 2990 RequestPartitionId.defaultPartition(), 2991 t.getResourceType(), 2992 t.getParamName(), 2993 t.getString("SP_SYSTEM"))) 2994 .addCalculator( 2995 "HASH_SYS_AND_VALUE", 2996 t -> ResourceIndexedSearchParamToken.calculateHashSystemAndValue( 2997 new PartitionSettings(), 2998 RequestPartitionId.defaultPartition(), 2999 t.getResourceType(), 3000 t.getParamName(), 3001 t.getString("SP_SYSTEM"), 3002 t.getString("SP_VALUE"))) 3003 .addCalculator( 3004 "HASH_VALUE", 3005 t -> ResourceIndexedSearchParamToken.calculateHashValue( 3006 new PartitionSettings(), 3007 RequestPartitionId.defaultPartition(), 3008 t.getResourceType(), 3009 t.getParamName(), 3010 t.getString("SP_VALUE")))); 3011 } 3012 3013 // Indexes - URI 3014 Builder.BuilderWithTableName spidxUri = version.onTable("HFJ_SPIDX_URI"); 3015 version.startSectionWithMessage("Starting work on table: " + spidxUri.getTableName()); 3016 spidxUri.addColumn("20180903.40", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 3017 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 3018 spidxUri.addIndex("20180903.41", "IDX_SP_URI_HASH_IDENTITY") 3019 .unique(false) 3020 .withColumns("HASH_IDENTITY", "SP_URI"); 3021 spidxUri.addColumn("20180903.42", "HASH_URI").nullable().type(ColumnTypeEnum.LONG); 3022 spidxUri.addIndex("20180903.43", "IDX_SP_URI_HASH_URI") 3023 .unique(false) 3024 .withColumns("HASH_URI"); 3025 spidxUri.addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.44") 3026 .setColumnName("HASH_IDENTITY") 3027 .addCalculator( 3028 "HASH_IDENTITY", 3029 t -> BaseResourceIndexedSearchParam.calculateHashIdentity( 3030 new PartitionSettings(), 3031 (RequestPartitionId) null, 3032 t.getResourceType(), 3033 t.getString("SP_NAME"))) 3034 .addCalculator( 3035 "HASH_URI", 3036 t -> ResourceIndexedSearchParamUri.calculateHashUri( 3037 new PartitionSettings(), 3038 (RequestPartitionId) null, 3039 t.getResourceType(), 3040 t.getString("SP_NAME"), 3041 t.getString("SP_URI")))); 3042 } 3043 3044 // Search Parameter Presence 3045 Builder.BuilderWithTableName spp = version.onTable("HFJ_RES_PARAM_PRESENT"); 3046 version.startSectionWithMessage("Starting work on table: " + spp.getTableName()); 3047 spp.dropIndex("20180903.45", "IDX_RESPARMPRESENT_SPID_RESID"); 3048 spp.addColumn("20180903.46", "HASH_PRESENCE").nullable().type(ColumnTypeEnum.LONG); 3049 spp.addIndex("20180903.47", "IDX_RESPARMPRESENT_HASHPRES").unique(false).withColumns("HASH_PRESENCE"); 3050 3051 ArbitrarySqlTask consolidateSearchParamPresenceIndexesTask = new ArbitrarySqlTask( 3052 VersionEnum.V3_5_0, "20180903.48", "HFJ_SEARCH_PARM", "Consolidate search parameter presence indexes"); 3053 consolidateSearchParamPresenceIndexesTask.setExecuteOnlyIfTableExists("HFJ_SEARCH_PARM"); 3054 consolidateSearchParamPresenceIndexesTask.setBatchSize(1); 3055 3056 String sql = "SELECT " + "HFJ_SEARCH_PARM.RES_TYPE RES_TYPE, HFJ_SEARCH_PARM.PARAM_NAME PARAM_NAME, " 3057 + "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 " 3058 + "from HFJ_RES_PARAM_PRESENT " 3059 + "join HFJ_SEARCH_PARM ON (HFJ_SEARCH_PARM.PID = HFJ_RES_PARAM_PRESENT.SP_ID) " 3060 + "where HFJ_RES_PARAM_PRESENT.HASH_PRESENCE is null"; 3061 consolidateSearchParamPresenceIndexesTask.addExecuteOnlyIfColumnExists("HFJ_RES_PARAM_PRESENT", "SP_ID"); 3062 consolidateSearchParamPresenceIndexesTask.addQuery( 3063 sql, ArbitrarySqlTask.QueryModeEnum.BATCH_UNTIL_NO_MORE, t -> { 3064 Number pid = (Number) t.get("PID"); 3065 Boolean present = columnToBoolean(t.get("SP_PRESENT")); 3066 String resType = (String) t.get("RES_TYPE"); 3067 String paramName = (String) t.get("PARAM_NAME"); 3068 Long hash = SearchParamPresentEntity.calculateHashPresence( 3069 new PartitionSettings(), (RequestPartitionId) null, resType, paramName, present); 3070 consolidateSearchParamPresenceIndexesTask.executeSql( 3071 "HFJ_RES_PARAM_PRESENT", 3072 "update HFJ_RES_PARAM_PRESENT set HASH_PRESENCE = ? where PID = ?", 3073 hash, 3074 pid); 3075 }); 3076 version.addTask(consolidateSearchParamPresenceIndexesTask); 3077 3078 // SP_ID is no longer needed 3079 spp.dropColumn("20180903.49", "SP_ID"); 3080 3081 // Concept 3082 Builder.BuilderWithTableName trmConcept = version.onTable("TRM_CONCEPT"); 3083 version.startSectionWithMessage("Starting work on table: " + trmConcept.getTableName()); 3084 trmConcept.addColumn("20180903.50", "CONCEPT_UPDATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 3085 trmConcept.addIndex("20180903.51", "IDX_CONCEPT_UPDATED").unique(false).withColumns("CONCEPT_UPDATED"); 3086 trmConcept.modifyColumn("20180903.52", "CODE").nonNullable().withType(ColumnTypeEnum.STRING, 500); 3087 3088 // Concept Designation 3089 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_DESIG"); 3090 version.addTableRawSql("20180907.1", "TRM_CONCEPT_DESIG") 3091 .addSql( 3092 DriverTypeEnum.H2_EMBEDDED, 3093 "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))") 3094 .addSql( 3095 DriverTypeEnum.H2_EMBEDDED, 3096 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3097 .addSql( 3098 DriverTypeEnum.H2_EMBEDDED, 3099 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 3100 .addSql( 3101 DriverTypeEnum.DERBY_EMBEDDED, 3102 "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))") 3103 .addSql( 3104 DriverTypeEnum.DERBY_EMBEDDED, 3105 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3106 .addSql( 3107 DriverTypeEnum.DERBY_EMBEDDED, 3108 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 3109 .addSql( 3110 DriverTypeEnum.MYSQL_5_7, 3111 "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") 3112 .addSql( 3113 DriverTypeEnum.MYSQL_5_7, 3114 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 3115 .addSql( 3116 DriverTypeEnum.MYSQL_5_7, 3117 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 3118 .addSql( 3119 DriverTypeEnum.MARIADB_10_1, 3120 "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))") 3121 .addSql( 3122 DriverTypeEnum.MARIADB_10_1, 3123 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 3124 .addSql( 3125 DriverTypeEnum.MARIADB_10_1, 3126 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 3127 .addSql( 3128 DriverTypeEnum.ORACLE_12C, 3129 "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))") 3130 .addSql( 3131 DriverTypeEnum.ORACLE_12C, 3132 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3133 .addSql( 3134 DriverTypeEnum.ORACLE_12C, 3135 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 3136 .addSql( 3137 DriverTypeEnum.POSTGRES_9_4, 3138 "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))") 3139 .addSql( 3140 DriverTypeEnum.POSTGRES_9_4, 3141 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3142 .addSql( 3143 DriverTypeEnum.POSTGRES_9_4, 3144 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 3145 .addSql( 3146 DriverTypeEnum.MSSQL_2012, 3147 "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))") 3148 .addSql( 3149 DriverTypeEnum.MSSQL_2012, 3150 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3151 .addSql( 3152 DriverTypeEnum.MSSQL_2012, 3153 "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT"); 3154 3155 // Concept Property 3156 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_PROPERTY"); 3157 version.addTableRawSql("20180907.2", "TRM_CONCEPT_PROPERTY") 3158 .addSql( 3159 DriverTypeEnum.DERBY_EMBEDDED, 3160 "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))") 3161 .addSql( 3162 DriverTypeEnum.DERBY_EMBEDDED, 3163 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3164 .addSql( 3165 DriverTypeEnum.DERBY_EMBEDDED, 3166 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 3167 .addSql( 3168 DriverTypeEnum.MARIADB_10_1, 3169 "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))") 3170 .addSql( 3171 DriverTypeEnum.MARIADB_10_1, 3172 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 3173 .addSql( 3174 DriverTypeEnum.MARIADB_10_1, 3175 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 3176 .addSql( 3177 DriverTypeEnum.MYSQL_5_7, 3178 "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))") 3179 .addSql( 3180 DriverTypeEnum.MYSQL_5_7, 3181 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 3182 .addSql( 3183 DriverTypeEnum.MYSQL_5_7, 3184 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 3185 .addSql( 3186 DriverTypeEnum.ORACLE_12C, 3187 "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))") 3188 .addSql( 3189 DriverTypeEnum.ORACLE_12C, 3190 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3191 .addSql( 3192 DriverTypeEnum.ORACLE_12C, 3193 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 3194 .addSql( 3195 DriverTypeEnum.POSTGRES_9_4, 3196 "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))") 3197 .addSql( 3198 DriverTypeEnum.POSTGRES_9_4, 3199 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3200 .addSql( 3201 DriverTypeEnum.POSTGRES_9_4, 3202 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 3203 .addSql( 3204 DriverTypeEnum.MSSQL_2012, 3205 "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))") 3206 .addSql( 3207 DriverTypeEnum.MSSQL_2012, 3208 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 3209 .addSql( 3210 DriverTypeEnum.MSSQL_2012, 3211 "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT"); 3212 3213 // Concept Map - Map 3214 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP"); 3215 version.addTableRawSql("20180907.3", "TRM_CONCEPT_MAP") 3216 .addSql( 3217 DriverTypeEnum.DERBY_EMBEDDED, 3218 "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))") 3219 .addSql( 3220 DriverTypeEnum.DERBY_EMBEDDED, 3221 "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 3222 .addSql( 3223 DriverTypeEnum.MYSQL_5_7, 3224 "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))") 3225 .addSql( 3226 DriverTypeEnum.MYSQL_5_7, 3227 "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 3228 .addSql( 3229 DriverTypeEnum.MYSQL_5_7, 3230 "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE (RES_ID)") 3231 .addSql( 3232 DriverTypeEnum.ORACLE_12C, 3233 "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))") 3234 .addSql( 3235 DriverTypeEnum.ORACLE_12C, 3236 "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 3237 .addSql( 3238 DriverTypeEnum.ORACLE_12C, 3239 "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 3240 .addSql( 3241 DriverTypeEnum.POSTGRES_9_4, 3242 "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))") 3243 .addSql( 3244 DriverTypeEnum.POSTGRES_9_4, 3245 "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 3246 .addSql( 3247 DriverTypeEnum.POSTGRES_9_4, 3248 "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 3249 .addSql( 3250 DriverTypeEnum.MSSQL_2012, 3251 "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))") 3252 .addSql( 3253 DriverTypeEnum.MSSQL_2012, 3254 "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 3255 .addSql( 3256 DriverTypeEnum.MSSQL_2012, 3257 "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 3258 .addSql( 3259 DriverTypeEnum.MARIADB_10_1, 3260 "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))") 3261 .addSql( 3262 DriverTypeEnum.MARIADB_10_1, 3263 "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE (RES_ID)") 3264 .addSql( 3265 DriverTypeEnum.MARIADB_10_1, 3266 "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)"); 3267 3268 // Concept Map - Group 3269 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GROUP"); 3270 version.addTableRawSql("20180907.4", "TRM_CONCEPT_MAP_GROUP") 3271 .addSql( 3272 DriverTypeEnum.DERBY_EMBEDDED, 3273 "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))") 3274 .addSql( 3275 DriverTypeEnum.DERBY_EMBEDDED, 3276 "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 3277 .addSql( 3278 DriverTypeEnum.DERBY_EMBEDDED, 3279 "create unique index IDX_CONCEPT_MAP_URL on TRM_CONCEPT_MAP (URL)") 3280 .addSql( 3281 DriverTypeEnum.ORACLE_12C, 3282 "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))") 3283 .addSql( 3284 DriverTypeEnum.ORACLE_12C, 3285 "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 3286 .addSql( 3287 DriverTypeEnum.MARIADB_10_1, 3288 "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))") 3289 .addSql( 3290 DriverTypeEnum.MARIADB_10_1, 3291 "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP (PID)") 3292 .addSql( 3293 DriverTypeEnum.MYSQL_5_7, 3294 "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))") 3295 .addSql( 3296 DriverTypeEnum.MYSQL_5_7, 3297 "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP (PID)") 3298 .addSql( 3299 DriverTypeEnum.MSSQL_2012, 3300 "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))") 3301 .addSql( 3302 DriverTypeEnum.MSSQL_2012, 3303 "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 3304 .addSql( 3305 DriverTypeEnum.POSTGRES_9_4, 3306 "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))") 3307 .addSql( 3308 DriverTypeEnum.POSTGRES_9_4, 3309 "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP"); 3310 3311 // Concept Map - Group Element 3312 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GRP_ELEMENT"); 3313 version.addTableRawSql("20180907.5", "TRM_CONCEPT_MAP_GRP_ELEMENT") 3314 .addSql( 3315 DriverTypeEnum.DERBY_EMBEDDED, 3316 "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))") 3317 .addSql( 3318 DriverTypeEnum.DERBY_EMBEDDED, 3319 "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 3320 .addSql( 3321 DriverTypeEnum.MARIADB_10_1, 3322 "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))") 3323 .addSql( 3324 DriverTypeEnum.MARIADB_10_1, 3325 "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP (PID)") 3326 .addSql( 3327 DriverTypeEnum.MARIADB_10_1, 3328 "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 3329 .addSql( 3330 DriverTypeEnum.DERBY_EMBEDDED, 3331 "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 3332 .addSql( 3333 DriverTypeEnum.MYSQL_5_7, 3334 "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))") 3335 .addSql( 3336 DriverTypeEnum.MYSQL_5_7, 3337 "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 3338 .addSql( 3339 DriverTypeEnum.MYSQL_5_7, 3340 "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP (PID)") 3341 .addSql( 3342 DriverTypeEnum.POSTGRES_9_4, 3343 "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))") 3344 .addSql( 3345 DriverTypeEnum.POSTGRES_9_4, 3346 "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 3347 .addSql( 3348 DriverTypeEnum.POSTGRES_9_4, 3349 "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 3350 .addSql( 3351 DriverTypeEnum.ORACLE_12C, 3352 "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))") 3353 .addSql( 3354 DriverTypeEnum.ORACLE_12C, 3355 "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 3356 .addSql( 3357 DriverTypeEnum.ORACLE_12C, 3358 "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 3359 .addSql( 3360 DriverTypeEnum.MSSQL_2012, 3361 "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))") 3362 .addSql( 3363 DriverTypeEnum.MSSQL_2012, 3364 "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 3365 .addSql( 3366 DriverTypeEnum.MSSQL_2012, 3367 "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP"); 3368 3369 // Concept Map - Group Element Target 3370 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GRP_ELM_TGT"); 3371 version.addTableRawSql("20180907.6", "TRM_CONCEPT_MAP_GRP_ELM_TGT") 3372 .addSql( 3373 DriverTypeEnum.DERBY_EMBEDDED, 3374 "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))") 3375 .addSql( 3376 DriverTypeEnum.DERBY_EMBEDDED, 3377 "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") 3378 .addSql( 3379 DriverTypeEnum.DERBY_EMBEDDED, 3380 "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 3381 .addSql( 3382 DriverTypeEnum.MARIADB_10_1, 3383 "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))") 3384 .addSql( 3385 DriverTypeEnum.MARIADB_10_1, 3386 "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)") 3387 .addSql( 3388 DriverTypeEnum.MARIADB_10_1, 3389 "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 3390 .addSql( 3391 DriverTypeEnum.MYSQL_5_7, 3392 "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))") 3393 .addSql( 3394 DriverTypeEnum.MYSQL_5_7, 3395 "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)") 3396 .addSql( 3397 DriverTypeEnum.MYSQL_5_7, 3398 "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 3399 .addSql( 3400 DriverTypeEnum.ORACLE_12C, 3401 "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))") 3402 .addSql( 3403 DriverTypeEnum.ORACLE_12C, 3404 "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") 3405 .addSql( 3406 DriverTypeEnum.ORACLE_12C, 3407 "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 3408 .addSql( 3409 DriverTypeEnum.POSTGRES_9_4, 3410 "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))") 3411 .addSql( 3412 DriverTypeEnum.POSTGRES_9_4, 3413 "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") 3414 .addSql( 3415 DriverTypeEnum.POSTGRES_9_4, 3416 "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 3417 .addSql( 3418 DriverTypeEnum.MSSQL_2012, 3419 "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))") 3420 .addSql( 3421 DriverTypeEnum.MSSQL_2012, 3422 "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 3423 .addSql( 3424 DriverTypeEnum.MSSQL_2012, 3425 "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"); 3426 3427 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 3428 .modifyColumn("20180907.7", "IDX_STRING") 3429 .nonNullable() 3430 .withType(ColumnTypeEnum.STRING, 200); 3431 } 3432 3433 private Boolean columnToBoolean(Object theValue) { 3434 if (theValue == null) { 3435 return null; 3436 } 3437 if (theValue instanceof Boolean) { 3438 return (Boolean) theValue; 3439 } 3440 3441 long longValue = ((Number) theValue).longValue(); 3442 return longValue == 1L; 3443 } 3444 3445 private void init340() { // 20180401 - 20180528 3446 Builder version = forVersion(VersionEnum.V3_4_0); 3447 3448 // CodeSystem Version 3449 Builder.BuilderWithTableName resourceLink = version.onTable("TRM_CODESYSTEM_VER"); 3450 version.startSectionWithMessage("Starting work on table: " + resourceLink.getTableName()); 3451 resourceLink.dropIndex("20180401.1", "IDX_CSV_RESOURCEPID_AND_VER"); 3452 resourceLink.dropColumn("20180401.2", "RES_VERSION_ID"); 3453 resourceLink.addColumn("20180401.3", "CS_VERSION_ID").nullable().type(ColumnTypeEnum.STRING, 255); 3454 resourceLink.addColumn("20180401.4", "CODESYSTEM_PID").nullable().type(ColumnTypeEnum.LONG); 3455 resourceLink 3456 .addForeignKey("20180401.5", "FK_CODESYSVER_CS_ID") 3457 .toColumn("CODESYSTEM_PID") 3458 .references("TRM_CODESYSTEM", "PID"); 3459 3460 // Concept 3461 Builder.BuilderWithTableName concept = version.onTable("TRM_CONCEPT"); 3462 version.startSectionWithMessage("Starting work on table: " + concept.getTableName()); 3463 concept.addColumn("20180401.6", "CODE_SEQUENCE").nullable().type(ColumnTypeEnum.INT); 3464 } 3465 3466 protected void init330() { // 20180114 - 20180329 3467 Builder version = forVersion(VersionEnum.V3_3_0); 3468 3469 version.initializeSchema( 3470 "20180115.0", 3471 new SchemaInitializationProvider( 3472 "HAPI FHIR", "/ca/uhn/hapi/fhir/jpa/docs/database", "HFJ_RESOURCE", true)); 3473 3474 Builder.BuilderWithTableName hfjResource = version.onTable("HFJ_RESOURCE"); 3475 version.startSectionWithMessage("Starting work on table: " + hfjResource.getTableName()); 3476 hfjResource.dropColumn("20180115.1", "RES_TEXT"); 3477 hfjResource.dropColumn("20180115.2", "RES_ENCODING"); 3478 3479 Builder.BuilderWithTableName hfjResVer = version.onTable("HFJ_RES_VER"); 3480 version.startSectionWithMessage("Starting work on table: " + hfjResVer.getTableName()); 3481 hfjResVer.modifyColumn("20180115.3", "RES_ENCODING").nullable(); 3482 hfjResVer.modifyColumn("20180115.4", "RES_TEXT").nullable(); 3483 } 3484 3485 public enum FlagEnum { 3486 NO_MIGRATE_HASHES("no-migrate-350-hashes"); 3487 3488 private final String myCommandLineValue; 3489 3490 FlagEnum(String theCommandLineValue) { 3491 myCommandLineValue = theCommandLineValue; 3492 } 3493 3494 public static FlagEnum fromCommandLineValue(String theCommandLineValue) { 3495 Optional<FlagEnum> retVal = Arrays.stream(values()) 3496 .filter(t -> t.myCommandLineValue.equals(theCommandLineValue)) 3497 .findFirst(); 3498 return retVal.orElseThrow(() -> { 3499 List<String> validValues = Arrays.stream(values()) 3500 .map(t -> t.myCommandLineValue) 3501 .sorted() 3502 .collect(Collectors.toList()); 3503 return new IllegalArgumentException( 3504 "Invalid flag \"" + theCommandLineValue + "\". Valid values: " + validValues); 3505 }); 3506 } 3507 } 3508}