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