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