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