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