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