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