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