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