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