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