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