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