001/*- 002 * #%L 003 * HAPI FHIR JPA Server 004 * %% 005 * Copyright (C) 2014 - 2023 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.tasks.api.BaseMigrationTasks; 032import ca.uhn.fhir.jpa.migrate.tasks.api.Builder; 033import ca.uhn.fhir.jpa.model.config.PartitionSettings; 034import ca.uhn.fhir.jpa.model.entity.BaseResourceIndexedSearchParam; 035import ca.uhn.fhir.jpa.model.entity.ResourceHistoryTable; 036import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamDate; 037import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantity; 038import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamString; 039import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamToken; 040import ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamUri; 041import ca.uhn.fhir.jpa.model.entity.ResourceTable; 042import ca.uhn.fhir.jpa.model.entity.SearchParamPresentEntity; 043import ca.uhn.fhir.jpa.model.entity.StorageSettings; 044import ca.uhn.fhir.util.ClasspathUtil; 045import ca.uhn.fhir.util.VersionEnum; 046import software.amazon.awssdk.utils.StringUtils; 047 048import java.util.Arrays; 049import java.util.HashMap; 050import java.util.List; 051import java.util.Map; 052import java.util.Optional; 053import java.util.Set; 054import java.util.stream.Collectors; 055 056import static ca.uhn.fhir.rest.api.Constants.UUID_LENGTH; 057 058@SuppressWarnings({"SqlNoDataSourceInspection", "SpellCheckingInspection", "java:S1192"}) 059public class HapiFhirJpaMigrationTasks extends BaseMigrationTasks<VersionEnum> { 060 061 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 062 public static final DriverTypeEnum[] NON_AUTOMATIC_FK_INDEX_PLATFORMS = new DriverTypeEnum[]{ 063 DriverTypeEnum.POSTGRES_9_4, DriverTypeEnum.ORACLE_12C, DriverTypeEnum.MSSQL_2012}; 064 private final Set<FlagEnum> myFlags; 065 066 067 /** 068 * Constructor 069 */ 070 public HapiFhirJpaMigrationTasks(Set<String> theFlags) { 071 myFlags = theFlags 072 .stream() 073 .map(FlagEnum::fromCommandLineValue) 074 .collect(Collectors.toSet()); 075 076 init330(); // 20180114 - 20180329 077 init340(); // 20180401 - 20180528 078 init350(); // 20180601 - 20180917 079 init360(); // 20180918 - 20181112 080 init400(); // 20190401 - 20190814 081 init410(); // 20190815 - 20191014 082 init420(); // 20191015 - 20200217 083 init430(); // Replaced by 5.0.0 084 init500(); // 20200218 - 20200513 085 init501(); // 20200514 - 20200515 086 init510(); // 20200516 - 20201028 087 init520(); // 20201029 - 088 init530(); 089 init540(); // 20210218 - 20210520 090 init550(); // 20210520 - 091 init560(); // 20211027 - 092 init570(); // 20211102 - 093 init600(); // 20211102 - 094 init610(); 095 init620(); 096 init640(); 097 init640_after_20230126(); 098 init660(); 099 init680(); 100 } 101 102 protected void init680() { 103 Builder version = forVersion(VersionEnum.V6_8_0); 104 105 // HAPI-FHIR #4801 - Add New Index On HFJ_RESOURCE 106 Builder.BuilderWithTableName resourceTable = version.onTable("HFJ_RESOURCE"); 107 108 resourceTable 109 .addIndex("20230502.1", "IDX_RES_RESID_UPDATED") 110 .unique(false) 111 .online(true) 112 .withColumns("RES_ID", "RES_UPDATED", "PARTITION_ID"); 113 114 Builder.BuilderWithTableName tagDefTable = version.onTable("HFJ_TAG_DEF"); 115 tagDefTable.dropIndex("20230505.1", "IDX_TAGDEF_TYPESYSCODEVERUS"); 116 117 tagDefTable.dropIndex("20230505.2", "IDX_TAG_DEF_TP_CD_SYS"); 118 tagDefTable 119 .addIndex("20230505.3", "IDX_TAG_DEF_TP_CD_SYS") 120 .unique(false) 121 .online(false) 122 .withColumns("TAG_TYPE", "TAG_CODE", "TAG_SYSTEM", "TAG_ID", "TAG_VERSION", "TAG_USER_SELECTED"); 123 124 // This migration is failing in Oracle because there is already an index created on column RES_VER_PID since it is a primary key. 125 // IDX_RESVERPROV_RESVER_PID is removed in 20230523.1 126 version 127 .onTable("HFJ_RES_VER_PROV") 128 .addIndex("20230510.1", "IDX_RESVERPROV_RESVER_PID") 129 .unique(false) 130 .withColumns("RES_VER_PID") 131 .failureAllowed(); 132 133 version 134 .onTable("HFJ_RES_VER_PROV") 135 .addIndex("20230510.2", "IDX_RESVERPROV_RES_PID") 136 .unique(false) 137 .withColumns("RES_PID"); 138 139 version 140 .onTable(ResourceHistoryTable.HFJ_RES_VER) 141 .addColumn("20230510.4", "SOURCE_URI") 142 .nullable() 143 .type(ColumnTypeEnum.STRING, 100); 144 version 145 .onTable(ResourceHistoryTable.HFJ_RES_VER) 146 .addColumn("20230510.5", "REQUEST_ID") 147 .nullable() 148 .type(ColumnTypeEnum.STRING, 16); 149 150 version 151 .onTable("HFJ_RES_VER_PROV") 152 .addForeignKey("20230510.6", "FK_RESVERPROV_RES_PID") 153 .toColumn("RES_PID") 154 .references("HFJ_RESOURCE", "RES_ID"); 155 156 version 157 .onTable("HFJ_RES_VER_PROV") 158 .dropIndex("20230523.1", "IDX_RESVERPROV_RESVER_PID"); 159 160 // add warning message to batch job instance 161 version 162 .onTable("BT2_WORK_CHUNK") 163 .addColumn("20230524.1", "WARNING_MSG") 164 .nullable() 165 .type(ColumnTypeEnum.CLOB); 166 167 version 168 .onTable("BT2_JOB_INSTANCE") 169 .addColumn("20230524.2", "WARNING_MSG") 170 .nullable() 171 .type(ColumnTypeEnum.CLOB); 172 } 173 174 protected void init660() { 175 Builder version = forVersion(VersionEnum.V6_6_0); 176 177 // fix Postgres clob types - that stupid oid driver problem is still there 178 // BT2_JOB_INSTANCE.PARAMS_JSON_LOB 179 version.onTable("BT2_JOB_INSTANCE") 180 .migratePostgresTextClobToBinaryClob("20230208.1", "PARAMS_JSON_LOB"); 181 // BT2_JOB_INSTANCE.REPORT 182 version.onTable("BT2_JOB_INSTANCE") 183 .migratePostgresTextClobToBinaryClob("20230208.2", "REPORT"); 184 // BT2_WORK_CHUNK.CHUNK_DATA 185 version.onTable("BT2_WORK_CHUNK") 186 .migratePostgresTextClobToBinaryClob("20230208.3", "CHUNK_DATA"); 187 188 { 189 Builder.BuilderWithTableName tagDefTable = version.onTable("HFJ_TAG_DEF"); 190 191 // add columns 192 tagDefTable 193 .addColumn("20230209.1", "TAG_VERSION") 194 .nullable() 195 .type(ColumnTypeEnum.STRING, 30); 196 tagDefTable 197 .addColumn("20230209.2", "TAG_USER_SELECTED") 198 .nullable() 199 .type(ColumnTypeEnum.BOOLEAN); 200 201 // Update indexing 202 tagDefTable.dropIndex("20230209.3", "IDX_TAGDEF_TYPESYSCODE"); 203 204 tagDefTable.dropIndex("20230209.4", "IDX_TAGDEF_TYPESYSCODEVERUS"); 205 Map<DriverTypeEnum, String> addTagDefConstraint = new HashMap<>(); 206 addTagDefConstraint.put(DriverTypeEnum.H2_EMBEDDED, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 207 addTagDefConstraint.put(DriverTypeEnum.MARIADB_10_1, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 208 addTagDefConstraint.put(DriverTypeEnum.MSSQL_2012, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 209 addTagDefConstraint.put(DriverTypeEnum.MYSQL_5_7, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 210 addTagDefConstraint.put(DriverTypeEnum.ORACLE_12C, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 211 addTagDefConstraint.put(DriverTypeEnum.POSTGRES_9_4, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODEVERUS UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM, TAG_VERSION, TAG_USER_SELECTED)"); 212 version.executeRawSql("20230209.5", addTagDefConstraint); 213 } 214 215 version 216 .onTable(Search.HFJ_SEARCH) 217 .addColumn("20230215.1", Search.SEARCH_UUID) 218 .nullable() 219 .type(ColumnTypeEnum.STRING, Search.SEARCH_UUID_COLUMN_LENGTH); 220 version 221 .onTable(BulkImportJobEntity.HFJ_BLK_IMPORT_JOB) 222 .addColumn("20230215.2", BulkImportJobEntity.JOB_ID) 223 .nullable() 224 .type(ColumnTypeEnum.STRING, UUID_LENGTH); 225 version 226 .onTable(BulkExportJobEntity.HFJ_BLK_EXPORT_JOB) 227 .addColumn("20230215.3", BulkExportJobEntity.JOB_ID) 228 .nullable() 229 .type(ColumnTypeEnum.STRING, UUID_LENGTH); 230 231 232 Builder.BuilderAddTableByColumns resSearchUrlTable = version.addTableByColumns("20230227.1", "HFJ_RES_SEARCH_URL", "RES_SEARCH_URL"); 233 234 resSearchUrlTable.addColumn("RES_SEARCH_URL").nonNullable().type(ColumnTypeEnum.STRING, 768); 235 resSearchUrlTable.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 236 237 resSearchUrlTable.addColumn("CREATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 238 239 resSearchUrlTable.addIndex("20230227.2", "IDX_RESSEARCHURL_RES").unique(false).withColumns("RES_ID"); 240 resSearchUrlTable.addIndex("20230227.3", "IDX_RESSEARCHURL_TIME").unique(false).withColumns("CREATED_TIME"); 241 242 { 243 // string search index 244 Builder.BuilderWithTableName stringTable = version.onTable("HFJ_SPIDX_STRING"); 245 246 // add res_id to indentity to speed up sorts. 247 stringTable 248 .addIndex("20230303.1", "IDX_SP_STRING_HASH_IDENT_V2") 249 .unique(false) 250 .online(true) 251 .withColumns("HASH_IDENTITY", "RES_ID", "PARTITION_ID"); 252 stringTable.dropIndexOnline("20230303.2", "IDX_SP_STRING_HASH_IDENT"); 253 254 // add hash_norm to res_id to speed up joins on a second string. 255 stringTable 256 .addIndex("20230303.3", "IDX_SP_STRING_RESID_V2") 257 .unique(false) 258 .online(true) 259 .withColumns("RES_ID", "HASH_NORM_PREFIX", "PARTITION_ID"); 260 261 // drop and recreate FK_SPIDXSTR_RESOURCE since it will be useing the old IDX_SP_STRING_RESID 262 stringTable.dropForeignKey("20230303.4", "FK_SPIDXSTR_RESOURCE", "HFJ_RESOURCE"); 263 stringTable.dropIndexOnline("20230303.5", "IDX_SP_STRING_RESID"); 264 stringTable.addForeignKey("20230303.6", "FK_SPIDXSTR_RESOURCE") 265 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 266 267 } 268 269 final String revColumnName = "REV"; 270 final String enversRevisionTable = "HFJ_REVINFO"; 271 final String enversMpiLinkAuditTable = "MPI_LINK_AUD"; 272 final String revTstmpColumnName = "REVTSTMP"; 273 274 { 275 version.addIdGenerator("20230306.1", "SEQ_HFJ_REVINFO"); 276 277 final Builder.BuilderAddTableByColumns enversRevInfo = version.addTableByColumns("20230306.2", enversRevisionTable, revColumnName); 278 279 enversRevInfo.addColumn(revColumnName).nonNullable().type(ColumnTypeEnum.LONG); 280 enversRevInfo.addColumn(revTstmpColumnName).nullable().type(ColumnTypeEnum.LONG); 281 282 final Builder.BuilderAddTableByColumns empiLink = version.addTableByColumns("20230306.6", enversMpiLinkAuditTable, "PID", revColumnName); 283 284 empiLink.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 285 empiLink.addColumn("REV").nonNullable().type(ColumnTypeEnum.LONG); 286 empiLink.addColumn("REVTYPE").nullable().type(ColumnTypeEnum.TINYINT); 287 empiLink.addColumn("PERSON_PID").nullable().type(ColumnTypeEnum.LONG); 288 empiLink.addColumn("GOLDEN_RESOURCE_PID").nullable().type(ColumnTypeEnum.LONG); 289 empiLink.addColumn("TARGET_TYPE").nullable().type(ColumnTypeEnum.STRING, 40); 290 empiLink.addColumn("RULE_COUNT").nullable().type(ColumnTypeEnum.LONG); 291 empiLink.addColumn("TARGET_PID").nullable().type(ColumnTypeEnum.LONG); 292 empiLink.addColumn("MATCH_RESULT").nullable().type(ColumnTypeEnum.INT); 293 empiLink.addColumn("LINK_SOURCE").nullable().type(ColumnTypeEnum.INT); 294 empiLink.addColumn("CREATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 295 empiLink.addColumn("UPDATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 296 empiLink.addColumn("VERSION").nullable().type(ColumnTypeEnum.STRING, 16); 297 empiLink.addColumn("EID_MATCH").nullable().type(ColumnTypeEnum.BOOLEAN); 298 empiLink.addColumn("NEW_PERSON").nullable().type(ColumnTypeEnum.BOOLEAN); 299 empiLink.addColumn("VECTOR").nullable().type(ColumnTypeEnum.LONG); 300 empiLink.addColumn("SCORE").nullable().type(ColumnTypeEnum.FLOAT); 301 302 // N.B. It's impossible to rename a foreign key in a Hibernate Envers audit table, and the schema migration unit test will fail if we try to drop and recreate it 303 empiLink.addForeignKey("20230306.7", "FKAOW7NXNCLOEC419ARS0FPP58M") 304 .toColumn(revColumnName) 305 .references(enversRevisionTable, revColumnName); 306 } 307 308 { 309 // The pre-release already contains the long version of this column 310 // We do this becausea doing a modifyColumn on Postgres (and possibly other RDBMS's) will fail with a nasty error: 311 // column "revtstmp" cannot be cast automatically to type timestamp without time zone Hint: You might need to specify "USING revtstmp::timestamp without time zone". 312 version 313 .onTable(enversRevisionTable) 314 .dropColumn("20230316.1", revTstmpColumnName); 315 316 version 317 .onTable(enversRevisionTable) 318 .addColumn("20230316.2", revTstmpColumnName) 319 .nullable() 320 .type(ColumnTypeEnum.DATE_TIMESTAMP); 321 322 // New columns from AuditableBasePartitionable 323 version 324 .onTable(enversMpiLinkAuditTable) 325 .addColumn("20230316.3", "PARTITION_ID") 326 .nullable() 327 .type(ColumnTypeEnum.INT); 328 329 version 330 .onTable(enversMpiLinkAuditTable) 331 .addColumn("20230316.4", "PARTITION_DATE") 332 .nullable() 333 .type(ColumnTypeEnum.DATE_ONLY); 334 } 335 336 version 337 .onTable(ResourceTable.HFJ_RESOURCE) 338 .addColumn("20230323.1", "SEARCH_URL_PRESENT") 339 .nullable() 340 .type(ColumnTypeEnum.BOOLEAN); 341 342 343 { 344 Builder.BuilderWithTableName uriTable = version.onTable("HFJ_SPIDX_URI"); 345 uriTable 346 .addIndex("20230324.1", "IDX_SP_URI_HASH_URI_V2") 347 .unique(true) 348 .online(true) 349 .withColumns("HASH_URI", "RES_ID", "PARTITION_ID"); 350 uriTable 351 .addIndex("20230324.2", "IDX_SP_URI_HASH_IDENTITY_V2") 352 .unique(true) 353 .online(true) 354 .withColumns("HASH_IDENTITY", "SP_URI", "RES_ID", "PARTITION_ID"); 355 uriTable.dropIndex("20230324.3", "IDX_SP_URI_RESTYPE_NAME"); 356 uriTable.dropIndex("20230324.4", "IDX_SP_URI_UPDATED"); 357 uriTable.dropIndex("20230324.5", "IDX_SP_URI"); 358 uriTable.dropIndex("20230324.6", "IDX_SP_URI_HASH_URI"); 359 uriTable.dropIndex("20230324.7", "IDX_SP_URI_HASH_IDENTITY"); 360 } 361 362 version.onTable("HFJ_SPIDX_COORDS") 363 .dropIndex("20230325.1", "IDX_SP_COORDS_HASH"); 364 version.onTable("HFJ_SPIDX_COORDS") 365 .addIndex("20230325.2", "IDX_SP_COORDS_HASH_V2") 366 .unique(false) 367 .online(true) 368 .withColumns("HASH_IDENTITY", "SP_LATITUDE", "SP_LONGITUDE", "RES_ID", "PARTITION_ID"); 369 370 371 // Postgres tuning. 372 String postgresTuningStatementsAll = ClasspathUtil.loadResource("ca/uhn/fhir/jpa/docs/database/hapifhirpostgres94-init01.sql"); 373 List<String> postgresTuningStatements = Arrays 374 .stream(postgresTuningStatementsAll.split("\\n")) 375 .map(org.apache.commons.lang3.StringUtils::trim) 376 .filter(StringUtils::isNotBlank) 377 .filter(t -> !t.startsWith("--")) 378 .collect(Collectors.toList()); 379 version.executeRawSqls("20230402.1", Map.of(DriverTypeEnum.POSTGRES_9_4, postgresTuningStatements)); 380 381 // Use an unlimited length text column for RES_TEXT_VC 382 version 383 .onTable("HFJ_RES_VER") 384 .modifyColumn("20230421.1", "RES_TEXT_VC") 385 .nullable() 386 .failureAllowed() 387 .withType(ColumnTypeEnum.TEXT); 388 389 { 390 // add hash_norm to res_id to speed up joins on a second string. 391 Builder.BuilderWithTableName linkTable = version.onTable("HFJ_RES_LINK"); 392 linkTable 393 .addIndex("20230424.1", "IDX_RL_TGT_v2") 394 .unique(false) 395 .online(true) 396 .withColumns("TARGET_RESOURCE_ID", "SRC_PATH", "SRC_RESOURCE_ID", "TARGET_RESOURCE_TYPE", "PARTITION_ID"); 397 398 // drop and recreate FK_SPIDXSTR_RESOURCE since it will be useing the old IDX_SP_STRING_RESID 399 linkTable.dropForeignKey("20230424.2", "FK_RESLINK_TARGET", "HFJ_RESOURCE"); 400 linkTable.dropIndexOnline("20230424.3", "IDX_RL_TPATHRES"); 401 linkTable.dropIndexOnline("20230424.4", "IDX_RL_DEST"); 402 linkTable.addForeignKey("20230424.5", "FK_RESLINK_TARGET") 403 .toColumn("TARGET_RESOURCE_ID").references("HFJ_RESOURCE", "RES_ID"); 404 } 405 406 { 407 version.onTable("MPI_LINK") 408 .addIndex("20230504.1", "IDX_EMPI_GR_TGT") 409 .unique(false) 410 .withColumns("GOLDEN_RESOURCE_PID", "TARGET_PID"); 411 } 412 413 } 414 415 protected void init640() { 416 Builder version = forVersion(VersionEnum.V6_3_0); 417 418 // start forced_id inline migration 419 version 420 .onTable("HFJ_RESOURCE") 421 .addColumn("20221108.1", "FHIR_ID") 422 .nullable() 423 // FHIR ids contain a subset of ascii, limited to 64 chars. 424 .type(ColumnTypeEnum.STRING, 64); 425 426 // Add new Index to HFJ_SEARCH_INCLUDE on SEARCH_PID 427 version 428 .onTable("HFJ_SEARCH_INCLUDE") 429 .addIndex("20221207.1", "FK_SEARCHINC_SEARCH") 430 .unique(false) 431 .online(true) 432 .withColumns("SEARCH_PID") 433 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 434 } 435 436 protected void init640_after_20230126() { 437 Builder version = forVersion(VersionEnum.V6_3_0); 438 { //We added this constraint when userSelected and Version were added. It is no longer necessary. 439 Builder.BuilderWithTableName tagDefTable = version.onTable("HFJ_TAG_DEF"); 440 tagDefTable.dropIndex("20230503.1", "IDX_TAGDEF_TYPESYSCODEVERUS"); 441 } 442 } 443 444 445 private void init620() { 446 Builder version = forVersion(VersionEnum.V6_2_0); 447 448 // add new REPORT column to BATCH2 tables 449 version 450 .onTable("BT2_JOB_INSTANCE") 451 .addColumn("20220830.1", "FAST_TRACKING") 452 .nullable() 453 .type(ColumnTypeEnum.BOOLEAN); 454 455 version 456 .onTable("HFJ_BINARY_STORAGE_BLOB") 457 .modifyColumn("20221017.1", "BLOB_SIZE") 458 .nullable() 459 .withType(ColumnTypeEnum.LONG); 460 461 version.onTable("HFJ_SPIDX_URI") 462 .modifyColumn("20221103.1", "SP_URI") 463 .nullable() 464 .withType(ColumnTypeEnum.STRING, 500); 465 466 version.onTable("BT2_JOB_INSTANCE") 467 .addColumn("20230110.1", "UPDATE_TIME") 468 .nullable() 469 .type(ColumnTypeEnum.DATE_TIMESTAMP); 470 471 version.onTable("BT2_WORK_CHUNK") 472 .addColumn("20230110.2", "UPDATE_TIME") 473 .nullable() 474 .type(ColumnTypeEnum.DATE_TIMESTAMP); 475 476 477 } 478 479 private void init610() { 480 Builder version = forVersion(VersionEnum.V6_1_0); 481 482 // add new REPORT column to BATCH2 tables 483 version 484 .onTable("BT2_JOB_INSTANCE") 485 .addColumn("20220601.1", "REPORT") 486 .nullable() 487 .type(ColumnTypeEnum.CLOB); 488 } 489 490 private void init600() { 491 Builder version = forVersion(VersionEnum.V6_0_0); 492 493 /** 494 * New indexing for the core SPIDX tables. 495 * Ensure all queries can be satisfied by the index directly, 496 * either as left or right table in a hash or sort join. 497 * 498 * new date search indexing 499 * @see ca.uhn.fhir.jpa.search.builder.predicate.DatePredicateBuilder 500 * @see ResourceIndexedSearchParamDate 501 */ 502 { 503 Builder.BuilderWithTableName dateTable = version.onTable("HFJ_SPIDX_DATE"); 504 505 // replace and drop IDX_SP_DATE_HASH 506 dateTable 507 .addIndex("20220207.1", "IDX_SP_DATE_HASH_V2") 508 .unique(false) 509 .online(true) 510 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH", "RES_ID", "PARTITION_ID"); 511 dateTable.dropIndexOnline("20220207.2", "IDX_SP_DATE_HASH"); 512 513 // drop redundant 514 dateTable.dropIndexOnline("20220207.3", "IDX_SP_DATE_HASH_LOW"); 515 516 // replace and drop IDX_SP_DATE_HASH_HIGH 517 dateTable 518 .addIndex("20220207.4", "IDX_SP_DATE_HASH_HIGH_V2") 519 .unique(false) 520 .online(true) 521 .withColumns("HASH_IDENTITY", "SP_VALUE_HIGH", "RES_ID", "PARTITION_ID"); 522 dateTable.dropIndexOnline("20220207.5", "IDX_SP_DATE_HASH_HIGH"); 523 524 // replace and drop IDX_SP_DATE_ORD_HASH 525 dateTable 526 .addIndex("20220207.6", "IDX_SP_DATE_ORD_HASH_V2") 527 .unique(false) 528 .online(true) 529 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL", "SP_VALUE_HIGH_DATE_ORDINAL", "RES_ID", "PARTITION_ID"); 530 dateTable.dropIndexOnline("20220207.7", "IDX_SP_DATE_ORD_HASH"); 531 532 // replace and drop IDX_SP_DATE_ORD_HASH_HIGH 533 dateTable 534 .addIndex("20220207.8", "IDX_SP_DATE_ORD_HASH_HIGH_V2") 535 .unique(false) 536 .online(true) 537 .withColumns("HASH_IDENTITY", "SP_VALUE_HIGH_DATE_ORDINAL", "RES_ID", "PARTITION_ID"); 538 dateTable.dropIndexOnline("20220207.9", "IDX_SP_DATE_ORD_HASH_HIGH"); 539 540 // drop redundant 541 dateTable.dropIndexOnline("20220207.10", "IDX_SP_DATE_ORD_HASH_LOW"); 542 543 // replace and drop IDX_SP_DATE_RESID 544 dateTable 545 .addIndex("20220207.11", "IDX_SP_DATE_RESID_V2") 546 .unique(false) 547 .online(true) 548 .withColumns("RES_ID", "HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH", "SP_VALUE_LOW_DATE_ORDINAL", "SP_VALUE_HIGH_DATE_ORDINAL", "PARTITION_ID"); 549 // some engines tie the FK constraint to a particular index. 550 // So we need to drop and recreate the constraint to drop the old RES_ID index. 551 // Rename it while we're at it. FK17s70oa59rm9n61k9thjqrsqm was not a pretty name. 552 dateTable.dropForeignKey("20220207.12", "FK17S70OA59RM9N61K9THJQRSQM", "HFJ_RESOURCE"); 553 dateTable.dropIndexOnline("20220207.13", "IDX_SP_DATE_RESID"); 554 dateTable.dropIndexOnline("20220207.14", "FK17S70OA59RM9N61K9THJQRSQM"); 555 556 dateTable.addForeignKey("20220207.15", "FK_SP_DATE_RES") 557 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 558 559 // drop obsolete 560 dateTable.dropIndexOnline("20220207.16", "IDX_SP_DATE_UPDATED"); 561 } 562 563 /** 564 * new token search indexing 565 * @see ca.uhn.fhir.jpa.search.builder.predicate.TokenPredicateBuilder 566 * @see ResourceIndexedSearchParamToken 567 */ 568 { 569 Builder.BuilderWithTableName tokenTable = version.onTable("HFJ_SPIDX_TOKEN"); 570 571 // replace and drop IDX_SP_TOKEN_HASH for sorting 572 tokenTable 573 .addIndex("20220208.1", "IDX_SP_TOKEN_HASH_V2") 574 .unique(false).online(true) 575 .withColumns("HASH_IDENTITY", "SP_SYSTEM", "SP_VALUE", "RES_ID", "PARTITION_ID"); 576 577 tokenTable.dropIndexOnline("20220208.2", "IDX_SP_TOKEN_HASH"); 578 579 // for search by system 580 tokenTable 581 .addIndex("20220208.3", "IDX_SP_TOKEN_HASH_S_V2") 582 .unique(false).online(true) 583 .withColumns("HASH_SYS", "RES_ID", "PARTITION_ID"); 584 585 tokenTable.dropIndexOnline("20220208.4", "IDX_SP_TOKEN_HASH_S"); 586 587 // for search by system+value 588 tokenTable 589 .addIndex("20220208.5", "IDX_SP_TOKEN_HASH_SV_V2") 590 .unique(false).online(true) 591 .withColumns("HASH_SYS_AND_VALUE", "RES_ID", "PARTITION_ID"); 592 593 tokenTable.dropIndexOnline("20220208.6", "IDX_SP_TOKEN_HASH_SV"); 594 595 // for search by value 596 tokenTable 597 .addIndex("20220208.7", "IDX_SP_TOKEN_HASH_V_V2") 598 .unique(false).online(true) 599 .withColumns("HASH_VALUE", "RES_ID", "PARTITION_ID"); 600 601 tokenTable.dropIndexOnline("20220208.8", "IDX_SP_TOKEN_HASH_V"); 602 603 // obsolete. We're dropping this column. 604 tokenTable.dropIndexOnline("20220208.9", "IDX_SP_TOKEN_UPDATED"); 605 606 // for joining as second table: 607 { 608 // replace and drop IDX_SP_TOKEN_RESID, and the associated fk constraint 609 tokenTable 610 .addIndex("20220208.10", "IDX_SP_TOKEN_RESID_V2") 611 .unique(false).online(true) 612 .withColumns("RES_ID", "HASH_SYS_AND_VALUE", "HASH_VALUE", "HASH_SYS", "HASH_IDENTITY", "PARTITION_ID"); 613 614 // some engines tie the FK constraint to a particular index. 615 // So we need to drop and recreate the constraint to drop the old RES_ID index. 616 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 617 tokenTable.dropForeignKey("20220208.11", "FK7ULX3J1GG3V7MAQREJGC7YBC4", "HFJ_RESOURCE"); 618 tokenTable.dropIndexOnline("20220208.12", "IDX_SP_TOKEN_RESID"); 619 tokenTable.dropIndexOnline("20220208.13", "FK7ULX3J1GG3V7MAQREJGC7YBC4"); 620 621 tokenTable.addForeignKey("20220208.14", "FK_SP_TOKEN_RES") 622 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 623 } 624 } 625 626 // fix for https://github.com/hapifhir/hapi-fhir/issues/3316 627 // index must have same name that indexed FK or SchemaMigrationTest complains because H2 sets this index automatically 628 629 version.onTable("TRM_VALUESET_C_DESIGNATION") 630 .addIndex("20220223.1", "FK_TRM_VALUESET_CONCEPT_PID") 631 .unique(false) 632 .withColumns("VALUESET_CONCEPT_PID") 633 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 634 635 // Batch2 Framework 636 637 Builder.BuilderAddTableByColumns batchInstance = version.addTableByColumns("20220227.1", "BT2_JOB_INSTANCE", "ID"); 638 batchInstance.addColumn("ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 639 batchInstance.addColumn("CREATE_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 640 batchInstance.addColumn("START_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 641 batchInstance.addColumn("END_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 642 batchInstance.addColumn("DEFINITION_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 643 batchInstance.addColumn("DEFINITION_VER").nonNullable().type(ColumnTypeEnum.INT); 644 batchInstance.addColumn("STAT").nonNullable().type(ColumnTypeEnum.STRING, 20); 645 batchInstance.addColumn("JOB_CANCELLED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 646 batchInstance.addColumn("PARAMS_JSON").nullable().type(ColumnTypeEnum.STRING, 2000); 647 batchInstance.addColumn("PARAMS_JSON_LOB").nullable().type(ColumnTypeEnum.CLOB); 648 batchInstance.addColumn("CMB_RECS_PROCESSED").nullable().type(ColumnTypeEnum.INT); 649 batchInstance.addColumn("CMB_RECS_PER_SEC").nullable().type(ColumnTypeEnum.DOUBLE); 650 batchInstance.addColumn("TOT_ELAPSED_MILLIS").nullable().type(ColumnTypeEnum.INT); 651 batchInstance.addColumn("WORK_CHUNKS_PURGED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 652 batchInstance.addColumn("PROGRESS_PCT").nullable().type(ColumnTypeEnum.DOUBLE); 653 batchInstance.addColumn("ERROR_MSG").nullable().type(ColumnTypeEnum.STRING, 500); 654 batchInstance.addColumn("ERROR_COUNT").nullable().type(ColumnTypeEnum.INT); 655 batchInstance.addColumn("EST_REMAINING").nullable().type(ColumnTypeEnum.STRING, 100); 656 batchInstance.addIndex("20220227.2", "IDX_BT2JI_CT").unique(false).withColumns("CREATE_TIME"); 657 658 Builder.BuilderAddTableByColumns batchChunk = version.addTableByColumns("20220227.3", "BT2_WORK_CHUNK", "ID"); 659 batchChunk.addColumn("ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 660 batchChunk.addColumn("SEQ").nonNullable().type(ColumnTypeEnum.INT); 661 batchChunk.addColumn("CREATE_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 662 batchChunk.addColumn("START_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 663 batchChunk.addColumn("END_TIME").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 664 batchChunk.addColumn("DEFINITION_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 665 batchChunk.addColumn("DEFINITION_VER").nonNullable().type(ColumnTypeEnum.INT); 666 batchChunk.addColumn("STAT").nonNullable().type(ColumnTypeEnum.STRING, 20); 667 batchChunk.addColumn("RECORDS_PROCESSED").nullable().type(ColumnTypeEnum.INT); 668 batchChunk.addColumn("TGT_STEP_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 669 batchChunk.addColumn("CHUNK_DATA").nullable().type(ColumnTypeEnum.CLOB); 670 batchChunk.addColumn("INSTANCE_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 671 batchChunk.addColumn("ERROR_MSG").nullable().type(ColumnTypeEnum.STRING, 500); 672 batchChunk.addColumn("ERROR_COUNT").nonNullable().type(ColumnTypeEnum.INT); 673 batchChunk.addIndex("20220227.4", "IDX_BT2WC_II_SEQ").unique(false).withColumns("INSTANCE_ID", "SEQ"); 674 batchChunk.addForeignKey("20220227.5", "FK_BT2WC_INSTANCE").toColumn("INSTANCE_ID").references("BT2_JOB_INSTANCE", "ID"); 675 676 replaceNumericSPIndices(version); 677 replaceQuantitySPIndices(version); 678 679 // Drop Index on HFJ_RESOURCE.INDEX_STATUS 680 version 681 .onTable("HFJ_RESOURCE") 682 .dropIndex("20220314.1", "IDX_INDEXSTATUS"); 683 684 version 685 .onTable("BT2_JOB_INSTANCE") 686 .addColumn("20220416.1", "CUR_GATED_STEP_ID") 687 .nullable() 688 .type(ColumnTypeEnum.STRING, 100); 689 690 //Make Job expiry nullable so that we can prevent job expiry by using a null value. 691 version 692 .onTable("HFJ_BLK_EXPORT_JOB").modifyColumn("20220423.1", "EXP_TIME").nullable().withType(ColumnTypeEnum.DATE_TIMESTAMP); 693 694 // New Index on HFJ_RESOURCE for $reindex Operation - hapi-fhir #3534 695 { 696 version.onTable("HFJ_RESOURCE") 697 .addIndex("20220425.1", "IDX_RES_TYPE_DEL_UPDATED") 698 .unique(false) 699 .online(true) 700 .withColumns("RES_TYPE", "RES_DELETED_AT", "RES_UPDATED", "PARTITION_ID", "RES_ID"); 701 702 // Drop existing Index on HFJ_RESOURCE.RES_TYPE since the new Index will meet the overall Index Demand 703 version 704 .onTable("HFJ_RESOURCE") 705 .dropIndexOnline("20220425.2", "IDX_RES_TYPE"); 706 } 707 708 /** 709 * Update string indexing 710 * @see ca.uhn.fhir.jpa.search.builder.predicate.StringPredicateBuilder 711 * @see ResourceIndexedSearchParamString 712 */ 713 { 714 Builder.BuilderWithTableName tokenTable = version.onTable("HFJ_SPIDX_STRING"); 715 716 // add res_id, and partition_id so queries are covered without row-reads. 717 tokenTable 718 .addIndex("20220428.1", "IDX_SP_STRING_HASH_NRM_V2") 719 .unique(false) 720 .online(true) 721 .withColumns("HASH_NORM_PREFIX", "SP_VALUE_NORMALIZED", "RES_ID", "PARTITION_ID"); 722 tokenTable.dropIndexOnline("20220428.2", "IDX_SP_STRING_HASH_NRM"); 723 724 tokenTable 725 .addIndex("20220428.3", "IDX_SP_STRING_HASH_EXCT_V2") 726 .unique(false) 727 .online(true) 728 .withColumns("HASH_EXACT", "RES_ID", "PARTITION_ID"); 729 tokenTable.dropIndexOnline("20220428.4", "IDX_SP_STRING_HASH_EXCT"); 730 731 // we will drop the updated column. Start with the index. 732 tokenTable.dropIndexOnline("20220428.5", "IDX_SP_STRING_UPDATED"); 733 } 734 735 // Update tag indexing 736 { 737 Builder.BuilderWithTableName resTagTable = version.onTable("HFJ_RES_TAG"); 738 739 // add res_id, and partition_id so queries are covered without row-reads. 740 resTagTable 741 .addIndex("20220429.1", "IDX_RES_TAG_RES_TAG") 742 .unique(false) 743 .online(true) 744 .withColumns("RES_ID", "TAG_ID", "PARTITION_ID"); 745 resTagTable 746 .addIndex("20220429.2", "IDX_RES_TAG_TAG_RES") 747 .unique(false) 748 .online(true) 749 .withColumns("TAG_ID", "RES_ID", "PARTITION_ID"); 750 751 resTagTable.dropIndex("20220429.4", "IDX_RESTAG_TAGID"); 752 // Weird that we don't have addConstraint. No time to do it today. 753 Map<DriverTypeEnum, String> addResTagConstraint = new HashMap<>(); 754 addResTagConstraint.put(DriverTypeEnum.H2_EMBEDDED, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 755 addResTagConstraint.put(DriverTypeEnum.MARIADB_10_1, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 756 addResTagConstraint.put(DriverTypeEnum.MSSQL_2012, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 757 addResTagConstraint.put(DriverTypeEnum.MYSQL_5_7, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 758 addResTagConstraint.put(DriverTypeEnum.ORACLE_12C, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 759 addResTagConstraint.put(DriverTypeEnum.POSTGRES_9_4, "ALTER TABLE HFJ_RES_TAG ADD CONSTRAINT IDX_RESTAG_TAGID UNIQUE (RES_ID, TAG_ID)"); 760 version.executeRawSql("20220429.5", addResTagConstraint); 761 762 Builder.BuilderWithTableName tagTable = version.onTable("HFJ_TAG_DEF"); 763 tagTable 764 .addIndex("20220429.6", "IDX_TAG_DEF_TP_CD_SYS") 765 .unique(false) 766 .online(false) 767 .withColumns("TAG_TYPE", "TAG_CODE", "TAG_SYSTEM", "TAG_ID"); 768 // move constraint to new index 769 // Ugh. Only oracle supports using IDX_TAG_DEF_TP_CD_SYS to enforce this constraint. The others will create another index. 770 // For Sql Server, should change the index to be unique with include columns. Do this in 6.1 771 tagTable.dropIndex("20220429.8", "IDX_TAGDEF_TYPESYSCODE"); 772 Map<DriverTypeEnum, String> addTagDefConstraint = new HashMap<>(); 773 addTagDefConstraint.put(DriverTypeEnum.H2_EMBEDDED, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 774 addTagDefConstraint.put(DriverTypeEnum.MARIADB_10_1, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 775 addTagDefConstraint.put(DriverTypeEnum.MSSQL_2012, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 776 addTagDefConstraint.put(DriverTypeEnum.MYSQL_5_7, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 777 addTagDefConstraint.put(DriverTypeEnum.ORACLE_12C, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 778 addTagDefConstraint.put(DriverTypeEnum.POSTGRES_9_4, "ALTER TABLE HFJ_TAG_DEF ADD CONSTRAINT IDX_TAGDEF_TYPESYSCODE UNIQUE (TAG_TYPE, TAG_CODE, TAG_SYSTEM)"); 779 version.executeRawSql("20220429.9", addTagDefConstraint); 780 781 } 782 783 784 // Fix for https://github.com/hapifhir/hapi-fhir-jpaserver-starter/issues/328 785 version.onTable("NPM_PACKAGE_VER") 786 .modifyColumn("20220501.1", "FHIR_VERSION_ID").nonNullable().withType(ColumnTypeEnum.STRING, 20); 787 788 version.onTable("NPM_PACKAGE_VER_RES") 789 .modifyColumn("20220501.2", "FHIR_VERSION_ID").nonNullable().withType(ColumnTypeEnum.STRING, 20); 790 791 // Fix for https://gitlab.com/simpatico.ai/cdr/-/issues/3166 792 version.onTable("MPI_LINK") 793 .addIndex("20220613.1", "IDX_EMPI_MATCH_TGT_VER") 794 .unique(false) 795 .online(true) 796 .withColumns("MATCH_RESULT", "TARGET_PID", "VERSION"); 797 } 798 799 /** 800 * new numeric search indexing 801 * 802 * @see ca.uhn.fhir.jpa.search.builder.predicate.NumberPredicateBuilder 803 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamNumber 804 */ 805 private void replaceNumericSPIndices(Builder theVersion) { 806 Builder.BuilderWithTableName numberTable = theVersion.onTable("HFJ_SPIDX_NUMBER"); 807 808 // Main query index 809 numberTable 810 .addIndex("20220304.1", "IDX_SP_NUMBER_HASH_VAL_V2") 811 .unique(false) 812 .online(true) 813 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 814 815 numberTable.dropIndexOnline("20220304.2", "IDX_SP_NUMBER_HASH_VAL"); 816 817 // for joining to other queries 818 { 819 numberTable 820 .addIndex("20220304.3", "IDX_SP_NUMBER_RESID_V2") 821 .unique(false).online(true) 822 .withColumns("RES_ID", "HASH_IDENTITY", "SP_VALUE", "PARTITION_ID"); 823 824 // some engines tie the FK constraint to a particular index. 825 // So we need to drop and recreate the constraint to drop the old RES_ID index. 826 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 827 numberTable.dropForeignKey("20220304.4", "FKCLTIHNC5TGPRJ9BHPT7XI5OTB", "HFJ_RESOURCE"); 828 numberTable.dropIndexOnline("20220304.5", "IDX_SP_NUMBER_RESID"); 829 numberTable.dropIndexOnline("20220304.6", "FKCLTIHNC5TGPRJ9BHPT7XI5OTB"); 830 831 numberTable.addForeignKey("20220304.7", "FK_SP_NUMBER_RES") 832 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 833 } 834 // obsolete 835 numberTable.dropIndexOnline("20220304.8", "IDX_SP_NUMBER_UPDATED"); 836 } 837 838 /** 839 * new quantity search indexing 840 * 841 * @see ca.uhn.fhir.jpa.search.builder.predicate.QuantityPredicateBuilder 842 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantity 843 * @see ca.uhn.fhir.jpa.model.entity.ResourceIndexedSearchParamQuantityNormalized 844 */ 845 private void replaceQuantitySPIndices(Builder theVersion) { 846 { 847 Builder.BuilderWithTableName quantityTable = theVersion.onTable("HFJ_SPIDX_QUANTITY"); 848 849 // bare quantity 850 quantityTable 851 .addIndex("20220304.11", "IDX_SP_QUANTITY_HASH_V2") 852 .unique(false) 853 .online(true) 854 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 855 856 quantityTable.dropIndexOnline("20220304.12", "IDX_SP_QUANTITY_HASH"); 857 858 // quantity with system+units 859 quantityTable 860 .addIndex("20220304.13", "IDX_SP_QUANTITY_HASH_SYSUN_V2") 861 .unique(false) 862 .online(true) 863 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 864 865 quantityTable.dropIndexOnline("20220304.14", "IDX_SP_QUANTITY_HASH_SYSUN"); 866 867 // quantity with units 868 quantityTable 869 .addIndex("20220304.15", "IDX_SP_QUANTITY_HASH_UN_V2") 870 .unique(false) 871 .online(true) 872 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 873 874 quantityTable.dropIndexOnline("20220304.16", "IDX_SP_QUANTITY_HASH_UN"); 875 876 // for joining to other queries and sorts 877 { 878 quantityTable 879 .addIndex("20220304.17", "IDX_SP_QUANTITY_RESID_V2") 880 .unique(false).online(true) 881 .withColumns("RES_ID", "HASH_IDENTITY", "HASH_IDENTITY_SYS_UNITS", "HASH_IDENTITY_AND_UNITS", "SP_VALUE", "PARTITION_ID"); 882 883 // some engines tie the FK constraint to a particular index. 884 // So we need to drop and recreate the constraint to drop the old RES_ID index. 885 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 886 quantityTable.dropForeignKey("20220304.18", "FKN603WJJOI1A6ASEWXBBD78BI5", "HFJ_RESOURCE"); 887 quantityTable.dropIndexOnline("20220304.19", "IDX_SP_QUANTITY_RESID"); 888 quantityTable.dropIndexOnline("20220304.20", "FKN603WJJOI1A6ASEWXBBD78BI5"); 889 890 quantityTable.addForeignKey("20220304.21", "FK_SP_QUANTITY_RES") 891 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 892 } 893 // obsolete 894 quantityTable.dropIndexOnline("20220304.22", "IDX_SP_QUANTITY_UPDATED"); 895 } 896 897 { 898 Builder.BuilderWithTableName quantityNormTable = theVersion.onTable("HFJ_SPIDX_QUANTITY_NRML"); 899 900 // bare quantity 901 quantityNormTable 902 .addIndex("20220304.23", "IDX_SP_QNTY_NRML_HASH_V2") 903 .unique(false) 904 .online(true) 905 .withColumns("HASH_IDENTITY", "SP_VALUE", "RES_ID", "PARTITION_ID"); 906 907 quantityNormTable.dropIndexOnline("20220304.24", "IDX_SP_QNTY_NRML_HASH"); 908 909 // quantity with system+units 910 quantityNormTable 911 .addIndex("20220304.25", "IDX_SP_QNTY_NRML_HASH_SYSUN_V2") 912 .unique(false) 913 .online(true) 914 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 915 916 quantityNormTable.dropIndexOnline("20220304.26", "IDX_SP_QNTY_NRML_HASH_SYSUN"); 917 918 // quantity with units 919 quantityNormTable 920 .addIndex("20220304.27", "IDX_SP_QNTY_NRML_HASH_UN_V2") 921 .unique(false) 922 .online(true) 923 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE", "RES_ID", "PARTITION_ID"); 924 925 quantityNormTable.dropIndexOnline("20220304.28", "IDX_SP_QNTY_NRML_HASH_UN"); 926 927 // for joining to other queries and sorts 928 { 929 quantityNormTable 930 .addIndex("20220304.29", "IDX_SP_QNTY_NRML_RESID_V2") 931 .unique(false).online(true) 932 .withColumns("RES_ID", "HASH_IDENTITY", "HASH_IDENTITY_SYS_UNITS", "HASH_IDENTITY_AND_UNITS", "SP_VALUE", "PARTITION_ID"); 933 934 // some engines tie the FK constraint to a particular index. 935 // So we need to drop and recreate the constraint to drop the old RES_ID index. 936 // Rename it while we're at it. FK7ULX3J1GG3V7MAQREJGC7YBC4 was not a pretty name. 937 quantityNormTable.dropForeignKey("20220304.30", "FKRCJOVMUH5KC0O6FVBLE319PYV", "HFJ_RESOURCE"); 938 quantityNormTable.dropIndexOnline("20220304.31", "IDX_SP_QNTY_NRML_RESID"); 939 quantityNormTable.dropIndexOnline("20220304.32", "FKRCJOVMUH5KC0O6FVBLE319PYV"); 940 941 quantityNormTable.addForeignKey("20220304.33", "FK_SP_QUANTITYNM_RES") 942 .toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 943 } 944 // obsolete 945 quantityNormTable.dropIndexOnline("20220304.34", "IDX_SP_QNTY_NRML_UPDATED"); 946 947 } 948 } 949 950 /** 951 * See https://github.com/hapifhir/hapi-fhir/issues/3237 for reasoning for these indexes. 952 * This adds indexes to various tables to enhance delete-expunge performance, which deletes by PID. 953 */ 954 private void addIndexesForDeleteExpunge(Builder theVersion) { 955 956 theVersion.onTable("HFJ_HISTORY_TAG") 957 .addIndex("20211210.2", "IDX_RESHISTTAG_RESID") 958 .unique(false) 959 .withColumns("RES_ID"); 960 961 theVersion.onTable("HFJ_RES_VER_PROV") 962 .addIndex("20211210.3", "FK_RESVERPROV_RES_PID") 963 .unique(false) 964 .withColumns("RES_PID") 965 .doNothing() // This index is added below in a better form 966 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 967 968 theVersion.onTable("HFJ_FORCED_ID") 969 .addIndex("20211210.4", "FK_FORCEDID_RESOURCE") 970 .unique(true) 971 .withColumns("RESOURCE_PID") 972 .doNothing()//This migration was added in error, as this table already has a unique constraint on RESOURCE_PID and every database creates an index on anything that is unique. 973 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 974 } 975 976 private void init570() { 977 Builder version = forVersion(VersionEnum.V5_7_0); 978 979 // both indexes must have same name that indexed FK or SchemaMigrationTest complains because H2 sets this index automatically 980 981 version.onTable("TRM_CONCEPT_PROPERTY") 982 .addIndex("20211102.1", "FK_CONCEPTPROP_CONCEPT") 983 .unique(false) 984 .withColumns("CONCEPT_PID") 985 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 986 987 version.onTable("TRM_CONCEPT_DESIG") 988 .addIndex("20211102.2", "FK_CONCEPTDESIG_CONCEPT") 989 .unique(false) 990 .withColumns("CONCEPT_PID") 991 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 992 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 993 994 version.onTable("TRM_CONCEPT_PC_LINK") 995 .addIndex("20211102.3", "FK_TERM_CONCEPTPC_CHILD") 996 .unique(false) 997 .withColumns("CHILD_PID") 998 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 999 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1000 1001 version.onTable("TRM_CONCEPT_PC_LINK") 1002 .addIndex("20211102.4", "FK_TERM_CONCEPTPC_PARENT") 1003 .unique(false) 1004 .withColumns("PARENT_PID") 1005 // H2, Derby, MariaDB, and MySql automatically add indexes to foreign keys 1006 .onlyAppliesToPlatforms(NON_AUTOMATIC_FK_INDEX_PLATFORMS); 1007 1008 addIndexesForDeleteExpunge(version); 1009 1010 // Add inline resource text column 1011 version.onTable("HFJ_RES_VER") 1012 .addColumn("20220102.1", "RES_TEXT_VC") 1013 .nullable() 1014 .type(ColumnTypeEnum.STRING, 4000); 1015 1016 // Add partition id column for mdm 1017 Builder.BuilderWithTableName empiLink = version.onTable("MPI_LINK"); 1018 1019 empiLink.addColumn("20220324.1", "PARTITION_ID") 1020 .nullable() 1021 .type(ColumnTypeEnum.INT); 1022 empiLink.addColumn("20220324.2", "PARTITION_DATE") 1023 .nullable() 1024 .type(ColumnTypeEnum.DATE_ONLY); 1025 } 1026 1027 1028 private void init560() { 1029 init560_20211027(); 1030 } 1031 1032 /** 1033 * Mirgation for the batch job parameter size change. Overriding purposes only. 1034 */ 1035 protected void init560_20211027() { 1036 // nothing 1037 } 1038 1039 private void init550() { 1040 1041 Builder version = forVersion(VersionEnum.V5_5_0); 1042 1043 // For MSSQL only - Replace ForcedId index with a version that has an INCLUDE clause 1044 Builder.BuilderWithTableName forcedId = version.onTable("HFJ_FORCED_ID"); 1045 forcedId.dropIndex("20210516.1", "IDX_FORCEDID_TYPE_FID").onlyAppliesToPlatforms(DriverTypeEnum.MSSQL_2012).runEvenDuringSchemaInitialization(); 1046 forcedId.addIndex("20210516.2", "IDX_FORCEDID_TYPE_FID").unique(true).includeColumns("RESOURCE_PID").withColumns("RESOURCE_TYPE", "FORCED_ID").onlyAppliesToPlatforms(DriverTypeEnum.MSSQL_2012).runEvenDuringSchemaInitialization(); 1047 1048 // Add bulk import file description 1049 version.onTable("HFJ_BLK_IMPORT_JOBFILE") 1050 .addColumn("20210528.1", "FILE_DESCRIPTION").nullable().type(ColumnTypeEnum.STRING, 500); 1051 1052 // Bump ConceptMap display lengths 1053 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1054 .modifyColumn("20210617.1", "TARGET_DISPLAY").nullable().withType(ColumnTypeEnum.STRING, 500); 1055 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1056 .modifyColumn("20210617.2", "SOURCE_DISPLAY").nullable().withType(ColumnTypeEnum.STRING, 500); 1057 1058 version.onTable("HFJ_BLK_EXPORT_JOB") 1059 .modifyColumn("20210624.1", "REQUEST").nonNullable().withType(ColumnTypeEnum.STRING, 1024); 1060 1061 version.onTable("HFJ_IDX_CMP_STRING_UNIQ") 1062 .modifyColumn("20210713.1", "IDX_STRING").nonNullable().withType(ColumnTypeEnum.STRING, 500); 1063 1064 version.onTable("HFJ_RESOURCE") 1065 .addColumn("20210720.1", "SP_CMPTOKS_PRESENT").nullable().type(ColumnTypeEnum.BOOLEAN); 1066 1067 version.addIdGenerator("20210720.2", "SEQ_IDXCMBTOKNU_ID"); 1068 1069 Builder.BuilderAddTableByColumns cmpToks = version 1070 .addTableByColumns("20210720.3", "HFJ_IDX_CMB_TOK_NU", "PID"); 1071 cmpToks.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1072 cmpToks.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1073 cmpToks.addColumn("HASH_COMPLETE").nonNullable().type(ColumnTypeEnum.LONG); 1074 cmpToks.addColumn("IDX_STRING").nonNullable().type(ColumnTypeEnum.STRING, 500); 1075 cmpToks.addForeignKey("20210720.4", "FK_IDXCMBTOKNU_RES_ID").toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 1076 cmpToks.addIndex("20210720.5", "IDX_IDXCMBTOKNU_STR").unique(false).withColumns("IDX_STRING"); 1077 cmpToks.addIndex("20210720.6", "IDX_IDXCMBTOKNU_RES").unique(false).withColumns("RES_ID"); 1078 1079 Builder.BuilderWithTableName cmbTokNuTable = version.onTable("HFJ_IDX_CMB_TOK_NU"); 1080 1081 cmbTokNuTable.addColumn("20210722.1", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1082 cmbTokNuTable.addColumn("20210722.2", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1083 cmbTokNuTable.modifyColumn("20210722.3", "RES_ID").nullable().withType(ColumnTypeEnum.LONG); 1084 1085 // Dropping index on the language column, as it's no longer in use. 1086 // TODO: After 2 releases from 5.5.0, drop the column too 1087 version.onTable("HFJ_RESOURCE") 1088 .dropIndex("20210908.1", "IDX_RES_LANG"); 1089 1090 version.onTable("TRM_VALUESET") 1091 .addColumn("20210915.1", "EXPANDED_AT") 1092 .nullable() 1093 .type(ColumnTypeEnum.DATE_TIMESTAMP); 1094 1095 /* 1096 * Replace CLOB columns with BLOB columns 1097 */ 1098 1099 // TRM_VALUESET_CONCEPT.SOURCE_DIRECT_PARENT_PIDS 1100 version.onTable("TRM_VALUESET_CONCEPT") 1101 .migratePostgresTextClobToBinaryClob("20211003.1", "SOURCE_DIRECT_PARENT_PIDS"); 1102 1103 // TRM_CONCEPT.PARENT_PIDS 1104 version.onTable("TRM_CONCEPT") 1105 .migratePostgresTextClobToBinaryClob("20211003.2", "PARENT_PIDS"); 1106 1107 // HFJ_SEARCH.SEARCH_QUERY_STRING 1108 version.onTable("HFJ_SEARCH") 1109 .migratePostgresTextClobToBinaryClob("20211003.3", "SEARCH_QUERY_STRING"); 1110 1111 } 1112 1113 private void init540() { 1114 1115 Builder version = forVersion(VersionEnum.V5_4_0); 1116 1117 //-- add index on HFJ_SPIDX_DATE 1118 version.onTable("HFJ_SPIDX_DATE").addIndex("20210309.1", "IDX_SP_DATE_HASH_HIGH") 1119 .unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_HIGH") 1120 .doNothing(); 1121 1122 //-- add index on HFJ_FORCED_ID 1123 version.onTable("HFJ_FORCED_ID").addIndex("20210309.2", "IDX_FORCEID_FID") 1124 .unique(false).withColumns("FORCED_ID"); 1125 1126 //-- ValueSet Concept Fulltext Indexing 1127 version.onTable("TRM_VALUESET_CONCEPT").addColumn("20210406.1", "INDEX_STATUS").nullable().type(ColumnTypeEnum.LONG); 1128 version.onTable("TRM_VALUESET_CONCEPT").addColumn("20210406.2", "SOURCE_DIRECT_PARENT_PIDS").nullable().type(ColumnTypeEnum.CLOB); 1129 version.onTable("TRM_VALUESET_CONCEPT").addColumn("20210406.3", "SOURCE_PID").nullable().type(ColumnTypeEnum.LONG); 1130 1131 // Bulk Import Job 1132 Builder.BuilderAddTableByColumns blkImportJobTable = version.addTableByColumns("20210410.1", "HFJ_BLK_IMPORT_JOB", "PID"); 1133 blkImportJobTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1134 blkImportJobTable.addColumn("JOB_ID").nonNullable().type(ColumnTypeEnum.STRING, UUID_LENGTH); 1135 blkImportJobTable.addColumn("JOB_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 10); 1136 blkImportJobTable.addColumn("STATUS_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1137 blkImportJobTable.addColumn("STATUS_MESSAGE").nullable().type(ColumnTypeEnum.STRING, 500); 1138 blkImportJobTable.addColumn("JOB_DESC").nullable().type(ColumnTypeEnum.STRING, 500); 1139 blkImportJobTable.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 1140 blkImportJobTable.addColumn("FILE_COUNT").nonNullable().type(ColumnTypeEnum.INT); 1141 blkImportJobTable.addColumn("ROW_PROCESSING_MODE").nonNullable().type(ColumnTypeEnum.STRING, 20); 1142 blkImportJobTable.addColumn("BATCH_SIZE").nonNullable().type(ColumnTypeEnum.INT); 1143 blkImportJobTable.addIndex("20210410.2", "IDX_BLKIM_JOB_ID").unique(true).withColumns("JOB_ID"); 1144 version.addIdGenerator("20210410.3", "SEQ_BLKIMJOB_PID"); 1145 1146 // Bulk Import Job File 1147 Builder.BuilderAddTableByColumns blkImportJobFileTable = version.addTableByColumns("20210410.4", "HFJ_BLK_IMPORT_JOBFILE", "PID"); 1148 blkImportJobFileTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1149 blkImportJobFileTable.addColumn("JOB_PID").nonNullable().type(ColumnTypeEnum.LONG); 1150 blkImportJobFileTable.addColumn("JOB_CONTENTS").nonNullable().type(ColumnTypeEnum.BLOB); 1151 blkImportJobFileTable.addColumn("FILE_SEQ").nonNullable().type(ColumnTypeEnum.INT); 1152 blkImportJobFileTable.addColumn("TENANT_NAME").nullable().type(ColumnTypeEnum.STRING, 200); 1153 blkImportJobFileTable.addIndex("20210410.5", "IDX_BLKIM_JOBFILE_JOBID").unique(false).withColumns("JOB_PID"); 1154 blkImportJobFileTable.addForeignKey("20210410.6", "FK_BLKIMJOBFILE_JOB").toColumn("JOB_PID").references("HFJ_BLK_IMPORT_JOB", "PID"); 1155 version.addIdGenerator("20210410.7", "SEQ_BLKIMJOBFILE_PID"); 1156 1157 //Increase ResourceLink path length 1158 version.onTable("HFJ_RES_LINK").modifyColumn("20210505.1", "SRC_PATH").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 500); 1159 } 1160 1161 private void init530() { 1162 Builder version = forVersion(VersionEnum.V5_3_0); 1163 1164 //-- TRM 1165 version 1166 .onTable("TRM_VALUESET_CONCEPT") 1167 .dropIndex("20210104.1", "IDX_VS_CONCEPT_CS_CODE"); 1168 1169 version 1170 .onTable("TRM_VALUESET_CONCEPT") 1171 .addIndex("20210104.2", "IDX_VS_CONCEPT_CSCD").unique(true).withColumns("VALUESET_PID", "SYSTEM_URL", "CODEVAL"); 1172 1173 //-- Add new Table, HFJ_SPIDX_QUANTITY_NRML 1174 version.addIdGenerator("20210109.1", "SEQ_SPIDX_QUANTITY_NRML"); 1175 Builder.BuilderAddTableByColumns pkg = version.addTableByColumns("20210109.2", "HFJ_SPIDX_QUANTITY_NRML", "SP_ID"); 1176 pkg.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1177 pkg.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 100); 1178 pkg.addColumn("SP_UPDATED").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1179 pkg.addColumn("SP_MISSING").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1180 pkg.addColumn("SP_NAME").nonNullable().type(ColumnTypeEnum.STRING, 100); 1181 pkg.addColumn("SP_ID").nonNullable().type(ColumnTypeEnum.LONG); 1182 pkg.addColumn("SP_SYSTEM").nullable().type(ColumnTypeEnum.STRING, 200); 1183 pkg.addColumn("SP_UNITS").nullable().type(ColumnTypeEnum.STRING, 200); 1184 pkg.addColumn("HASH_IDENTITY_AND_UNITS").nullable().type(ColumnTypeEnum.LONG); 1185 pkg.addColumn("HASH_IDENTITY_SYS_UNITS").nullable().type(ColumnTypeEnum.LONG); 1186 pkg.addColumn("HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 1187 pkg.addColumn("SP_VALUE").nullable().type(ColumnTypeEnum.FLOAT); 1188 pkg.addIndex("20210109.3", "IDX_SP_QNTY_NRML_HASH").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE"); 1189 pkg.addIndex("20210109.4", "IDX_SP_QNTY_NRML_HASH_UN").unique(false).withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE"); 1190 pkg.addIndex("20210109.5", "IDX_SP_QNTY_NRML_HASH_SYSUN").unique(false).withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE"); 1191 pkg.addIndex("20210109.6", "IDX_SP_QNTY_NRML_UPDATED").unique(false).withColumns("SP_UPDATED"); 1192 pkg.addIndex("20210109.7", "IDX_SP_QNTY_NRML_RESID").unique(false).withColumns("RES_ID"); 1193 1194 //-- Link to the resourceTable 1195 version.onTable("HFJ_RESOURCE").addColumn("20210109.10", "SP_QUANTITY_NRML_PRESENT").nullable().type(ColumnTypeEnum.BOOLEAN); 1196 1197 //-- Fixed the partition and fk 1198 Builder.BuilderWithTableName nrmlTable = version.onTable("HFJ_SPIDX_QUANTITY_NRML"); 1199 nrmlTable.addColumn("20210111.1", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1200 nrmlTable.addColumn("20210111.2", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1201 // - The fk name is generated from Hibernate, have to use this name here 1202 nrmlTable 1203 .addForeignKey("20210111.3", "FKRCJOVMUH5KC0O6FVBLE319PYV") 1204 .toColumn("RES_ID") 1205 .references("HFJ_RESOURCE", "RES_ID"); 1206 1207 Builder.BuilderWithTableName quantityTable = version.onTable("HFJ_SPIDX_QUANTITY"); 1208 quantityTable.modifyColumn("20210116.1", "SP_VALUE").nullable().failureAllowed().withType(ColumnTypeEnum.DOUBLE); 1209 1210 // HFJ_RES_LINK 1211 version.onTable("HFJ_RES_LINK") 1212 .addColumn("20210126.1", "TARGET_RESOURCE_VERSION").nullable().type(ColumnTypeEnum.LONG); 1213 1214 } 1215 1216 protected void init520() { 1217 Builder version = forVersion(VersionEnum.V5_2_0); 1218 1219 Builder.BuilderWithTableName mdmLink = version.onTable("MPI_LINK"); 1220 mdmLink.addColumn("20201029.1", "GOLDEN_RESOURCE_PID").nonNullable().type(ColumnTypeEnum.LONG); 1221 mdmLink.addColumn("20201029.2", "RULE_COUNT").nullable().type(ColumnTypeEnum.LONG); 1222 mdmLink 1223 .addForeignKey("20201029.3", "FK_EMPI_LINK_GOLDEN_RESOURCE") 1224 .toColumn("GOLDEN_RESOURCE_PID") 1225 .references("HFJ_RESOURCE", "RES_ID"); 1226 } 1227 1228 protected void init510() { 1229 Builder version = forVersion(VersionEnum.V5_1_0); 1230 1231 // NPM Packages 1232 version.addIdGenerator("20200610.1", "SEQ_NPM_PACK"); 1233 Builder.BuilderAddTableByColumns pkg = version.addTableByColumns("20200610.2", "NPM_PACKAGE", "PID"); 1234 pkg.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1235 pkg.addColumn("PACKAGE_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1236 pkg.addColumn("CUR_VERSION_ID").nullable().type(ColumnTypeEnum.STRING, 200); 1237 pkg.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1238 pkg.addColumn("PACKAGE_DESC").nullable().type(ColumnTypeEnum.STRING, 200); 1239 pkg.addIndex("20200610.3", "IDX_PACK_ID").unique(true).withColumns("PACKAGE_ID"); 1240 1241 version.addIdGenerator("20200610.4", "SEQ_NPM_PACKVER"); 1242 Builder.BuilderAddTableByColumns pkgVer = version.addTableByColumns("20200610.5", "NPM_PACKAGE_VER", "PID"); 1243 pkgVer.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1244 pkgVer.addColumn("PACKAGE_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1245 pkgVer.addColumn("VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1246 pkgVer.addColumn("PACKAGE_PID").nonNullable().type(ColumnTypeEnum.LONG); 1247 pkgVer.addColumn("BINARY_RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1248 pkgVer.addColumn("SAVED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1249 pkgVer.addColumn("PKG_DESC").nonNullable().type(ColumnTypeEnum.STRING, 200); 1250 pkgVer.addColumn("DESC_UPPER").nonNullable().type(ColumnTypeEnum.STRING, 200); 1251 pkgVer.addColumn("CURRENT_VERSION").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1252 pkgVer.addColumn("FHIR_VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 10); 1253 pkgVer.addColumn("FHIR_VERSION").nonNullable().type(ColumnTypeEnum.STRING, 10); 1254 pkgVer.addColumn("PACKAGE_SIZE_BYTES").nonNullable().type(ColumnTypeEnum.LONG); 1255 pkgVer.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1256 pkgVer.addForeignKey("20200610.6", "FK_NPM_PKV_PKG").toColumn("PACKAGE_PID").references("NPM_PACKAGE", "PID"); 1257 pkgVer.addForeignKey("20200610.7", "FK_NPM_PKV_RESID").toColumn("BINARY_RES_ID").references("HFJ_RESOURCE", "RES_ID"); 1258 pkgVer.addIndex("20200610.8", "IDX_PACKVER").unique(true).withColumns("PACKAGE_ID", "VERSION_ID"); 1259 1260 version.addIdGenerator("20200610.9", "SEQ_NPM_PACKVERRES"); 1261 Builder.BuilderAddTableByColumns pkgVerResAdd = version.addTableByColumns("20200610.10", "NPM_PACKAGE_VER_RES", "PID"); 1262 pkgVerResAdd.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1263 pkgVerResAdd.addColumn("PACKVER_PID").nonNullable().type(ColumnTypeEnum.LONG); 1264 pkgVerResAdd.addColumn("BINARY_RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1265 pkgVerResAdd.addColumn("FILE_DIR").nullable().type(ColumnTypeEnum.STRING, 200); 1266 pkgVerResAdd.addColumn("FILE_NAME").nullable().type(ColumnTypeEnum.STRING, 200); 1267 pkgVerResAdd.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 40); 1268 pkgVerResAdd.addColumn("CANONICAL_URL").nullable().type(ColumnTypeEnum.STRING, 200); 1269 pkgVerResAdd.addColumn("CANONICAL_VERSION").nullable().type(ColumnTypeEnum.STRING, 200); 1270 pkgVerResAdd.addColumn("FHIR_VERSION_ID").nonNullable().type(ColumnTypeEnum.STRING, 10); 1271 pkgVerResAdd.addColumn("FHIR_VERSION").nonNullable().type(ColumnTypeEnum.STRING, 10); 1272 pkgVerResAdd.addColumn("RES_SIZE_BYTES").nonNullable().type(ColumnTypeEnum.LONG); 1273 pkgVerResAdd.addColumn("UPDATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1274 pkgVerResAdd.addForeignKey("20200610.11", "FK_NPM_PACKVERRES_PACKVER").toColumn("PACKVER_PID").references("NPM_PACKAGE_VER", "PID"); 1275 pkgVerResAdd.addForeignKey("20200610.12", "FK_NPM_PKVR_RESID").toColumn("BINARY_RES_ID").references("HFJ_RESOURCE", "RES_ID"); 1276 pkgVerResAdd.addIndex("20200610.13", "IDX_PACKVERRES_URL").unique(false).withColumns("CANONICAL_URL"); 1277 1278 init510_20200610(); 1279 1280 Builder.BuilderWithTableName pkgVerMod = version.onTable("NPM_PACKAGE_VER"); 1281 pkgVerMod.modifyColumn("20200629.1", "PKG_DESC").nullable().withType(ColumnTypeEnum.STRING, 200); 1282 pkgVerMod.modifyColumn("20200629.2", "DESC_UPPER").nullable().withType(ColumnTypeEnum.STRING, 200); 1283 1284 init510_20200706_to_20200714(); 1285 1286 Builder.BuilderWithTableName empiLink = version.onTable("MPI_LINK"); 1287 empiLink.addColumn("20200715.1", "VERSION").nonNullable().type(ColumnTypeEnum.STRING, 16); 1288 empiLink.addColumn("20200715.2", "EID_MATCH").nullable().type(ColumnTypeEnum.BOOLEAN); 1289 empiLink.addColumn("20200715.3", "NEW_PERSON").nullable().type(ColumnTypeEnum.BOOLEAN); 1290 empiLink.addColumn("20200715.4", "VECTOR").nullable().type(ColumnTypeEnum.LONG); 1291 empiLink.addColumn("20200715.5", "SCORE").nullable().type(ColumnTypeEnum.FLOAT); 1292 1293 1294 init510_20200725(); 1295 1296 //EMPI Target Type 1297 empiLink.addColumn("20200727.1", "TARGET_TYPE").nullable().type(ColumnTypeEnum.STRING, 40); 1298 1299 //ConceptMap add version for search 1300 Builder.BuilderWithTableName trmConceptMap = version.onTable("TRM_CONCEPT_MAP"); 1301 trmConceptMap.addColumn("20200910.1", "VER").nullable().type(ColumnTypeEnum.STRING, 200); 1302 trmConceptMap.dropIndex("20200910.2", "IDX_CONCEPT_MAP_URL").failureAllowed(); 1303 trmConceptMap.addIndex("20200910.3", "IDX_CONCEPT_MAP_URL").unique(true).withColumns("URL", "VER"); 1304 1305 //Term CodeSystem Version and Term ValueSet Version 1306 Builder.BuilderWithTableName trmCodeSystemVer = version.onTable("TRM_CODESYSTEM_VER"); 1307 trmCodeSystemVer.addIndex("20200923.1", "IDX_CODESYSTEM_AND_VER").unique(true).withColumns("CODESYSTEM_PID", "CS_VERSION_ID"); 1308 Builder.BuilderWithTableName trmValueSet = version.onTable("TRM_VALUESET"); 1309 trmValueSet.addColumn("20200923.2", "VER").nullable().type(ColumnTypeEnum.STRING, 200); 1310 trmValueSet.dropIndex("20200923.3", "IDX_VALUESET_URL").failureAllowed(); 1311 trmValueSet.addIndex("20200923.4", "IDX_VALUESET_URL").unique(true).withColumns("URL", "VER"); 1312 1313 //Term ValueSet Component add system version 1314 Builder.BuilderWithTableName trmValueSetComp = version.onTable("TRM_VALUESET_CONCEPT"); 1315 trmValueSetComp.addColumn("20201028.1", "SYSTEM_VER").nullable().type(ColumnTypeEnum.STRING, 200); 1316 trmValueSetComp.dropIndex("20201028.2", "IDX_VS_CONCEPT_CS_CD").failureAllowed(); 1317 trmValueSetComp.addIndex("20201028.3", "IDX_VS_CONCEPT_CS_CODE").unique(true).withColumns("VALUESET_PID", "SYSTEM_URL", "SYSTEM_VER", "CODEVAL").doNothing(); 1318 } 1319 1320 protected void init510_20200725() { 1321 // nothing 1322 } 1323 1324 protected void init510_20200610() { 1325 // nothing 1326 } 1327 1328 protected void init510_20200706_to_20200714() { 1329 // nothing 1330 } 1331 1332 private void init501() { //20200514 - present 1333 Builder version = forVersion(VersionEnum.V5_0_1); 1334 1335 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 1336 spidxDate.addIndex("20200514.1", "IDX_SP_DATE_HASH_LOW").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_LOW") 1337 .doNothing(); 1338 spidxDate.addIndex("20200514.2", "IDX_SP_DATE_ORD_HASH").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL", "SP_VALUE_HIGH_DATE_ORDINAL") 1339 .doNothing(); 1340 spidxDate.addIndex("20200514.3", "IDX_SP_DATE_ORD_HASH_LOW").unique(false).withColumns("HASH_IDENTITY", "SP_VALUE_LOW_DATE_ORDINAL") 1341 .doNothing(); 1342 1343 // MPI_LINK 1344 version.addIdGenerator("20200517.1", "SEQ_EMPI_LINK_ID"); 1345 Builder.BuilderAddTableByColumns empiLink = version.addTableByColumns("20200517.2", "MPI_LINK", "PID"); 1346 empiLink.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1347 1348 empiLink.addColumn("PERSON_PID").nonNullable().type(ColumnTypeEnum.LONG); 1349 empiLink 1350 .addForeignKey("20200517.3", "FK_EMPI_LINK_PERSON") 1351 .toColumn("PERSON_PID") 1352 .references("HFJ_RESOURCE", "RES_ID"); 1353 1354 empiLink.addColumn("TARGET_PID").nonNullable().type(ColumnTypeEnum.LONG); 1355 empiLink 1356 .addForeignKey("20200517.4", "FK_EMPI_LINK_TARGET") 1357 .toColumn("TARGET_PID") 1358 .references("HFJ_RESOURCE", "RES_ID"); 1359 1360 empiLink.addColumn("MATCH_RESULT").nonNullable().type(ColumnTypeEnum.INT); 1361 empiLink.addColumn("LINK_SOURCE").nonNullable().type(ColumnTypeEnum.INT); 1362 empiLink.addColumn("CREATED").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1363 empiLink.addColumn("UPDATED").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1364 1365 1366 empiLink.addIndex("20200517.5", "IDX_EMPI_PERSON_TGT").unique(true).withColumns("PERSON_PID", "TARGET_PID"); 1367 1368 } 1369 1370 protected void init500() { // 20200218 - 20200519 1371 Builder version = forVersion(VersionEnum.V5_0_0); 1372 1373 // Eliminate circular dependency. 1374 version.onTable("HFJ_RESOURCE").dropColumn("20200218.1", "FORCED_ID_PID"); 1375 version.onTable("HFJ_RES_VER").dropColumn("20200218.2", "FORCED_ID_PID"); 1376 version.onTable("HFJ_RES_VER").addForeignKey("20200218.3", "FK_RESOURCE_HISTORY_RESOURCE").toColumn("RES_ID").references("HFJ_RESOURCE", "RES_ID"); 1377 version.onTable("HFJ_RES_VER").modifyColumn("20200220.1", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1378 // 1379 1380 // Drop unused column 1381 version.onTable("HFJ_RESOURCE").dropIndex("20200419.1", "IDX_RES_PROFILE"); 1382 version.onTable("HFJ_RESOURCE").dropColumn("20200419.2", "RES_PROFILE").failureAllowed(); 1383 1384 // Add Partitioning 1385 Builder.BuilderAddTableByColumns partition = version.addTableByColumns("20200420.0", "HFJ_PARTITION", "PART_ID"); 1386 partition.addColumn("PART_ID").nonNullable().type(ColumnTypeEnum.INT); 1387 partition.addColumn("PART_NAME").nonNullable().type(ColumnTypeEnum.STRING, 200); 1388 partition.addColumn("PART_DESC").nullable().type(ColumnTypeEnum.STRING, 200); 1389 partition.addIndex("20200420.1", "IDX_PART_NAME").unique(true).withColumns("PART_NAME"); 1390 1391 // Partition columns on individual tables 1392 version.onTable("HFJ_RESOURCE").addColumn("20200420.2", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1393 version.onTable("HFJ_RESOURCE").addColumn("20200420.3", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1394 version.onTable("HFJ_RES_VER").addColumn("20200420.4", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1395 version.onTable("HFJ_RES_VER").addColumn("20200420.5", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1396 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.6", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1397 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.7", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1398 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.8", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1399 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").addColumn("20200420.9", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1400 version.onTable("HFJ_HISTORY_TAG").addColumn("20200420.10", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1401 version.onTable("HFJ_HISTORY_TAG").addColumn("20200420.11", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1402 version.onTable("HFJ_RES_TAG").addColumn("20200420.12", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1403 version.onTable("HFJ_RES_TAG").addColumn("20200420.13", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1404 version.onTable("HFJ_FORCED_ID").addColumn("20200420.14", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1405 version.onTable("HFJ_FORCED_ID").addColumn("20200420.15", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1406 version.onTable("HFJ_RES_LINK").addColumn("20200420.16", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1407 version.onTable("HFJ_RES_LINK").addColumn("20200420.17", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1408 version.onTable("HFJ_SPIDX_STRING").addColumn("20200420.18", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1409 version.onTable("HFJ_SPIDX_STRING").addColumn("20200420.19", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1410 version.onTable("HFJ_SPIDX_COORDS").addColumn("20200420.20", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1411 version.onTable("HFJ_SPIDX_COORDS").addColumn("20200420.21", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1412 version.onTable("HFJ_SPIDX_NUMBER").addColumn("20200420.22", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1413 version.onTable("HFJ_SPIDX_NUMBER").addColumn("20200420.23", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1414 version.onTable("HFJ_SPIDX_TOKEN").addColumn("20200420.24", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1415 version.onTable("HFJ_SPIDX_TOKEN").addColumn("20200420.25", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1416 version.onTable("HFJ_SPIDX_DATE").addColumn("20200420.26", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1417 version.onTable("HFJ_SPIDX_DATE").addColumn("20200420.27", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1418 version.onTable("HFJ_SPIDX_URI").addColumn("20200420.28", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1419 version.onTable("HFJ_SPIDX_URI").addColumn("20200420.29", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1420 version.onTable("HFJ_SPIDX_QUANTITY").addColumn("20200420.30", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1421 version.onTable("HFJ_SPIDX_QUANTITY").addColumn("20200420.31", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1422 version.onTable("HFJ_RES_VER_PROV").addColumn("20200420.32", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1423 version.onTable("HFJ_RES_VER_PROV").addColumn("20200420.33", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1424 version.onTable("HFJ_RES_PARAM_PRESENT").addColumn("20200420.34", "PARTITION_ID").nullable().type(ColumnTypeEnum.INT); 1425 version.onTable("HFJ_RES_PARAM_PRESENT").addColumn("20200420.35", "PARTITION_DATE").nullable().type(ColumnTypeEnum.DATE_ONLY); 1426 1427 version.onTable("HFJ_SPIDX_STRING").modifyColumn("20200420.36", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1428 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20200420.37", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1429 version.onTable("HFJ_SPIDX_NUMBER").modifyColumn("20200420.38", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1430 version.onTable("HFJ_SPIDX_TOKEN").modifyColumn("20200420.39", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1431 version.onTable("HFJ_SPIDX_DATE").modifyColumn("20200420.40", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1432 version.onTable("HFJ_SPIDX_URI").modifyColumn("20200420.41", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1433 version.onTable("HFJ_SPIDX_QUANTITY").modifyColumn("20200420.42", "SP_MISSING").nonNullable().failureAllowed().withType(ColumnTypeEnum.BOOLEAN); 1434 1435 // Add support for integer comparisons during day-precision date search. 1436 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 1437 spidxDate.addColumn("20200501.1", "SP_VALUE_LOW_DATE_ORDINAL").nullable().type(ColumnTypeEnum.INT); 1438 spidxDate.addColumn("20200501.2", "SP_VALUE_HIGH_DATE_ORDINAL").nullable().type(ColumnTypeEnum.INT); 1439 1440 spidxDate.addTask(new CalculateOrdinalDatesTask(VersionEnum.V5_0_0, "20200501.3") 1441 .addCalculator("SP_VALUE_LOW_DATE_ORDINAL", t -> ResourceIndexedSearchParamDate.calculateOrdinalValue(t.getDate("SP_VALUE_LOW"))) 1442 .addCalculator("SP_VALUE_HIGH_DATE_ORDINAL", t -> ResourceIndexedSearchParamDate.calculateOrdinalValue(t.getDate("SP_VALUE_HIGH"))) 1443 .setColumnName("SP_VALUE_LOW_DATE_ORDINAL") //It doesn't matter which of the two we choose as they will both be null. 1444 ); 1445 1446 } 1447 1448 /** 1449 * Partway through the 4.3.0 releaase cycle we renumbered to 1450 * 5.0.0 - We have a bunch of NOP tasks here to avoid breakage for anyone 1451 * who installed a prerelease before we made the switch 1452 */ 1453 @SuppressWarnings("deprecation") 1454 private void init430() { 1455 Builder version = forVersion(VersionEnum.V4_3_0); 1456 version.addNop("20200218.1"); 1457 version.addNop("20200218.2"); 1458 version.addNop("20200218.3"); 1459 version.addNop("20200220.1"); 1460 version.addNop("20200419.1"); 1461 version.addNop("20200419.2"); 1462 version.addNop("20200420.0"); 1463 version.addNop("20200420.1"); 1464 version.addNop("20200420.2"); 1465 version.addNop("20200420.3"); 1466 version.addNop("20200420.4"); 1467 version.addNop("20200420.5"); 1468 version.addNop("20200420.6"); 1469 version.addNop("20200420.7"); 1470 version.addNop("20200420.8"); 1471 version.addNop("20200420.9"); 1472 version.addNop("20200420.10"); 1473 version.addNop("20200420.11"); 1474 version.addNop("20200420.12"); 1475 version.addNop("20200420.13"); 1476 version.addNop("20200420.14"); 1477 version.addNop("20200420.15"); 1478 version.addNop("20200420.16"); 1479 version.addNop("20200420.17"); 1480 version.addNop("20200420.18"); 1481 version.addNop("20200420.19"); 1482 version.addNop("20200420.20"); 1483 version.addNop("20200420.21"); 1484 version.addNop("20200420.22"); 1485 version.addNop("20200420.23"); 1486 version.addNop("20200420.24"); 1487 version.addNop("20200420.25"); 1488 version.addNop("20200420.26"); 1489 version.addNop("20200420.27"); 1490 version.addNop("20200420.28"); 1491 version.addNop("20200420.29"); 1492 version.addNop("20200420.30"); 1493 version.addNop("20200420.31"); 1494 version.addNop("20200420.32"); 1495 version.addNop("20200420.33"); 1496 version.addNop("20200420.34"); 1497 version.addNop("20200420.35"); 1498 version.addNop("20200420.36"); 1499 version.addNop("20200420.37"); 1500 version.addNop("20200420.38"); 1501 version.addNop("20200420.39"); 1502 version.addNop("20200420.40"); 1503 version.addNop("20200420.41"); 1504 version.addNop("20200420.42"); 1505 } 1506 1507 protected void init420() { // 20191015 - 20200217 1508 Builder version = forVersion(VersionEnum.V4_2_0); 1509 1510 // TermValueSetConceptDesignation 1511 version.onTable("TRM_VALUESET_C_DESIGNATION").dropIndex("20200202.1", "IDX_VALUESET_C_DSGNTN_VAL").failureAllowed(); 1512 Builder.BuilderWithTableName searchTable = version.onTable("HFJ_SEARCH"); 1513 searchTable.dropIndex("20200203.1", "IDX_SEARCH_LASTRETURNED"); 1514 searchTable.dropColumn("20200203.2", "SEARCH_LAST_RETURNED"); 1515 searchTable.addIndex("20200203.3", "IDX_SEARCH_CREATED").unique(false).withColumns("CREATED"); 1516 } 1517 1518 protected void init410() { // 20190815 - 20191014 1519 Builder version = forVersion(VersionEnum.V4_1_0); 1520 1521 /* 1522 * Note: The following tasks are markes as failure allowed - This is because all we're 1523 * doing is setting a not-null on a column that will never be null anyway. Setting not null 1524 * fails on SQL Server because there is an index on this column... Which is dumb, but hey. 1525 */ 1526 version.onTable("HFJ_SPIDX_NUMBER").modifyColumn("20190920.1", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1527 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20190920.2", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1528 version.onTable("HFJ_SPIDX_TOKEN").modifyColumn("20190920.3", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1529 version.onTable("HFJ_SPIDX_STRING").modifyColumn("20190920.4", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1530 version.onTable("HFJ_SPIDX_DATE").modifyColumn("20190920.5", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1531 version.onTable("HFJ_SPIDX_QUANTITY").modifyColumn("20190920.6", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1532 version.onTable("HFJ_SPIDX_URI").modifyColumn("20190920.7", "RES_ID").nonNullable().failureAllowed().withType(ColumnTypeEnum.LONG); 1533 1534 // HFJ_SEARCH 1535 version.onTable("HFJ_SEARCH").addColumn("20190921.1", "EXPIRY_OR_NULL").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1536 version.onTable("HFJ_SEARCH").addColumn("20190921.2", "NUM_BLOCKED").nullable().type(ColumnTypeEnum.INT); 1537 1538 // HFJ_BLK_EXPORT_JOB 1539 version.addIdGenerator("20190921.3", "SEQ_BLKEXJOB_PID"); 1540 Builder.BuilderAddTableByColumns bulkExportJob = version.addTableByColumns("20190921.4", "HFJ_BLK_EXPORT_JOB", "PID"); 1541 bulkExportJob.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1542 bulkExportJob.addColumn("JOB_ID").nonNullable().type(ColumnTypeEnum.STRING, 36); 1543 bulkExportJob.addColumn("JOB_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 10); 1544 bulkExportJob.addColumn("CREATED_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1545 bulkExportJob.addColumn("STATUS_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1546 bulkExportJob.addColumn("EXP_TIME").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1547 bulkExportJob.addColumn("REQUEST").nonNullable().type(ColumnTypeEnum.STRING, 500); 1548 bulkExportJob.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 1549 bulkExportJob.addColumn("EXP_SINCE").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1550 bulkExportJob.addColumn("STATUS_MESSAGE").nullable().type(ColumnTypeEnum.STRING, 500); 1551 bulkExportJob.addIndex("20190921.5", "IDX_BLKEX_EXPTIME").unique(false).withColumns("EXP_TIME"); 1552 bulkExportJob.addIndex("20190921.6", "IDX_BLKEX_JOB_ID").unique(true).withColumns("JOB_ID"); 1553 1554 // HFJ_BLK_EXPORT_COLLECTION 1555 version.addIdGenerator("20190921.7", "SEQ_BLKEXCOL_PID"); 1556 Builder.BuilderAddTableByColumns bulkExportCollection = version.addTableByColumns("20190921.8", "HFJ_BLK_EXPORT_COLLECTION", "PID"); 1557 bulkExportCollection.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1558 bulkExportCollection.addColumn("JOB_PID").nonNullable().type(ColumnTypeEnum.LONG); 1559 bulkExportCollection.addForeignKey("20190921.9", "FK_BLKEXCOL_JOB").toColumn("JOB_PID").references("HFJ_BLK_EXPORT_JOB", "PID"); 1560 bulkExportCollection.addColumn("RES_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 40); 1561 bulkExportCollection.addColumn("TYPE_FILTER").nullable().type(ColumnTypeEnum.STRING, 1000); 1562 bulkExportCollection.addColumn("OPTLOCK").nonNullable().type(ColumnTypeEnum.INT); 1563 1564 // HFJ_BLK_EXPORT_COLFILE 1565 version.addIdGenerator("20190921.10", "SEQ_BLKEXCOLFILE_PID"); 1566 Builder.BuilderAddTableByColumns bulkExportCollectionFile = version.addTableByColumns("20190921.11", "HFJ_BLK_EXPORT_COLFILE", "PID"); 1567 bulkExportCollectionFile.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1568 bulkExportCollectionFile.addColumn("COLLECTION_PID").nonNullable().type(ColumnTypeEnum.LONG); 1569 bulkExportCollectionFile.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1570 bulkExportCollectionFile.addForeignKey("20190921.12", "FK_BLKEXCOLFILE_COLLECT").toColumn("COLLECTION_PID").references("HFJ_BLK_EXPORT_COLLECTION", "PID"); 1571 1572 // HFJ_RES_VER_PROV 1573 version.startSectionWithMessage("Processing bulkExportCollectionFile: HFJ_RES_VER_PROV"); 1574 Builder.BuilderAddTableByColumns resVerProv = version.addTableByColumns("20190921.13", "HFJ_RES_VER_PROV", "RES_VER_PID"); 1575 resVerProv.addColumn("RES_VER_PID").nonNullable().type(ColumnTypeEnum.LONG); 1576 resVerProv 1577 .addForeignKey("20190921.14", "FK_RESVERPROV_RESVER_PID") 1578 .toColumn("RES_VER_PID") 1579 .references("HFJ_RES_VER", "PID"); 1580 resVerProv.addColumn("RES_PID").nonNullable().type(ColumnTypeEnum.LONG); 1581 resVerProv 1582 .addForeignKey("20190921.15", "FK_RESVERPROV_RES_PID") 1583 .toColumn("RES_PID") 1584 .references("HFJ_RESOURCE", "RES_ID") 1585 .doNothing(); // Added below in a better form 1586 resVerProv.addColumn("SOURCE_URI").nullable().type(ColumnTypeEnum.STRING, 100); 1587 resVerProv.addColumn("REQUEST_ID").nullable().type(ColumnTypeEnum.STRING, 16); 1588 resVerProv.addIndex("20190921.16", "IDX_RESVERPROV_SOURCEURI").unique(false).withColumns("SOURCE_URI"); 1589 resVerProv.addIndex("20190921.17", "IDX_RESVERPROV_REQUESTID").unique(false).withColumns("REQUEST_ID"); 1590 1591 // TermValueSetConceptDesignation 1592 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET_C_DESIGNATION"); 1593 Builder.BuilderWithTableName termValueSetConceptDesignationTable = version.onTable("TRM_VALUESET_C_DESIGNATION"); 1594 termValueSetConceptDesignationTable.addColumn("20190921.18", "VALUESET_PID").nonNullable().type(ColumnTypeEnum.LONG); 1595 termValueSetConceptDesignationTable 1596 .addForeignKey("20190921.19", "FK_TRM_VSCD_VS_PID") 1597 .toColumn("VALUESET_PID") 1598 .references("TRM_VALUESET", "PID"); 1599 1600 // Drop HFJ_SEARCH_RESULT foreign keys 1601 version.onTable("HFJ_SEARCH_RESULT").dropForeignKey("20190921.20", "FK_SEARCHRES_RES", "HFJ_RESOURCE"); 1602 version.onTable("HFJ_SEARCH_RESULT").dropForeignKey("20190921.21", "FK_SEARCHRES_SEARCH", "HFJ_SEARCH"); 1603 1604 // TermValueSet 1605 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET"); 1606 Builder.BuilderWithTableName termValueSetTable = version.onTable("TRM_VALUESET"); 1607 termValueSetTable.addColumn("20190921.22", "TOTAL_CONCEPTS").nonNullable().type(ColumnTypeEnum.LONG); 1608 termValueSetTable.addColumn("20190921.23", "TOTAL_CONCEPT_DESIGNATIONS").nonNullable().type(ColumnTypeEnum.LONG); 1609 termValueSetTable 1610 .dropIndex("20190921.24", "IDX_VALUESET_EXP_STATUS"); 1611 1612 version.dropIdGenerator("20190921.25", "SEQ_SEARCHPARM_ID"); 1613 1614 // TermValueSetConcept 1615 version.startSectionWithMessage("Processing bulkExportCollectionFile: TRM_VALUESET_CONCEPT"); 1616 Builder.BuilderWithTableName termValueSetConceptTable = version.onTable("TRM_VALUESET_CONCEPT"); 1617 termValueSetConceptTable.addColumn("20190921.26", "VALUESET_ORDER").nonNullable().type(ColumnTypeEnum.INT); 1618 termValueSetConceptTable 1619 .addIndex("20190921.27", "IDX_VS_CONCEPT_ORDER") 1620 .unique(true) 1621 .withColumns("VALUESET_PID", "VALUESET_ORDER"); 1622 1623 // Account for RESTYPE_LEN column increasing from 30 to 40 1624 version.onTable("HFJ_RESOURCE").modifyColumn("20191002.1", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1625 version.onTable("HFJ_RES_VER").modifyColumn("20191002.2", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1626 version.onTable("HFJ_HISTORY_TAG").modifyColumn("20191002.3", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1627 version.onTable("HFJ_RES_LINK").modifyColumn("20191002.4", "SOURCE_RESOURCE_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1628 version.onTable("HFJ_RES_LINK").modifyColumn("20191002.5", "TARGET_RESOURCE_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1629 version.onTable("HFJ_RES_TAG").modifyColumn("20191002.6", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 40); 1630 1631 // TermConceptDesignation 1632 version.startSectionWithMessage("Processing table: TRM_CONCEPT_DESIG"); 1633 version.onTable("TRM_CONCEPT_DESIG").modifyColumn("20191002.7", "VAL").nonNullable().withType(ColumnTypeEnum.STRING, 2000); 1634 1635 // TermValueSetConceptDesignation 1636 version.startSectionWithMessage("Processing table: TRM_VALUESET_C_DESIGNATION"); 1637 version.onTable("TRM_VALUESET_C_DESIGNATION").modifyColumn("20191002.8", "VAL").nonNullable().withType(ColumnTypeEnum.STRING, 2000); 1638 1639 // TermConceptProperty 1640 version.startSectionWithMessage("Processing table: TRM_CONCEPT_PROPERTY"); 1641 version.onTable("TRM_CONCEPT_PROPERTY").addColumn("20191002.9", "PROP_VAL_LOB").nullable().type(ColumnTypeEnum.BLOB); 1642 } 1643 1644 protected void init400() { // 20190401 - 20190814 1645 Builder version = forVersion(VersionEnum.V4_0_0); 1646 1647 // BinaryStorageEntity 1648 Builder.BuilderAddTableByColumns binaryBlob = version.addTableByColumns("20190722.1", "HFJ_BINARY_STORAGE_BLOB", "BLOB_ID"); 1649 binaryBlob.addColumn("BLOB_ID").nonNullable().type(ColumnTypeEnum.STRING, 200); 1650 binaryBlob.addColumn("RESOURCE_ID").nonNullable().type(ColumnTypeEnum.STRING, 100); 1651 binaryBlob.addColumn("BLOB_SIZE").nullable().type(ColumnTypeEnum.INT); 1652 binaryBlob.addColumn("CONTENT_TYPE").nonNullable().type(ColumnTypeEnum.STRING, 100); 1653 binaryBlob.addColumn("BLOB_DATA").nonNullable().type(ColumnTypeEnum.BLOB); 1654 binaryBlob.addColumn("PUBLISHED_DATE").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1655 binaryBlob.addColumn("BLOB_HASH").nullable().type(ColumnTypeEnum.STRING, 128); 1656 1657 // Interim builds used this name 1658 version.onTable("TRM_VALUESET_CODE").dropThisTable("20190722.2"); 1659 1660 version.onTable("TRM_CONCEPT_MAP_GROUP") 1661 .renameColumn("20190722.3", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 1662 .renameColumn("20190722.4", "mySourceValueSet", "SOURCE_VS", false, true) 1663 .renameColumn("20190722.5", "myTargetValueSet", "TARGET_VS", false, true); 1664 version.onTable("TRM_CONCEPT_MAP_GROUP") 1665 .modifyColumn("20190722.6", "CONCEPT_MAP_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1666 version.onTable("TRM_CONCEPT_MAP_GROUP") 1667 .modifyColumn("20190722.7", "SOURCE_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1668 version.onTable("TRM_CONCEPT_MAP_GROUP") 1669 .modifyColumn("20190722.8", "SOURCE_VS").nullable().withType(ColumnTypeEnum.STRING, 200); 1670 version.onTable("TRM_CONCEPT_MAP_GROUP") 1671 .modifyColumn("20190722.9", "TARGET_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1672 version.onTable("TRM_CONCEPT_MAP_GROUP") 1673 .modifyColumn("20190722.10", "TARGET_VS").nullable().withType(ColumnTypeEnum.STRING, 200); 1674 1675 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1676 .renameColumn("20190722.11", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 1677 .renameColumn("20190722.12", "mySystem", "SYSTEM_URL", false, true) 1678 .renameColumn("20190722.13", "mySystemVersion", "SYSTEM_VERSION", false, true) 1679 .renameColumn("20190722.14", "myValueSet", "VALUESET_URL", false, true); 1680 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1681 .modifyColumn("20190722.15", "CONCEPT_MAP_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1682 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1683 .modifyColumn("20190722.16", "SOURCE_CODE").nonNullable().withType(ColumnTypeEnum.STRING, 500); 1684 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1685 .modifyColumn("20190722.17", "SYSTEM_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1686 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1687 .modifyColumn("20190722.18", "SYSTEM_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1688 version.onTable("TRM_CONCEPT_MAP_GRP_ELEMENT") 1689 .modifyColumn("20190722.19", "VALUESET_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1690 1691 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1692 .renameColumn("20190722.20", "myConceptMapUrl", "CONCEPT_MAP_URL", false, true) 1693 .renameColumn("20190722.21", "mySystem", "SYSTEM_URL", false, true) 1694 .renameColumn("20190722.22", "mySystemVersion", "SYSTEM_VERSION", false, true) 1695 .renameColumn("20190722.23", "myValueSet", "VALUESET_URL", false, true); 1696 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1697 .modifyColumn("20190722.24", "CONCEPT_MAP_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1698 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1699 .modifyColumn("20190722.25", "SYSTEM_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1700 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1701 .modifyColumn("20190722.26", "SYSTEM_VERSION").nullable().withType(ColumnTypeEnum.STRING, 200); 1702 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1703 .modifyColumn("20190722.27", "TARGET_CODE").nonNullable().withType(ColumnTypeEnum.STRING, 500); 1704 version.onTable("TRM_CONCEPT_MAP_GRP_ELM_TGT") 1705 .modifyColumn("20190722.28", "VALUESET_URL").nullable().withType(ColumnTypeEnum.STRING, 200); 1706 1707 version.onTable("TRM_CONCEPT") 1708 .renameColumn("20190722.29", "CODE", "CODEVAL", false, true); 1709 1710 1711 // TermValueSet 1712 version.startSectionWithMessage("Processing table: TRM_VALUESET"); 1713 version.addIdGenerator("20190722.30", "SEQ_VALUESET_PID"); 1714 Builder.BuilderAddTableByColumns termValueSetTable = version.addTableByColumns("20190722.31", "TRM_VALUESET", "PID"); 1715 termValueSetTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1716 termValueSetTable.addColumn("URL").nonNullable().type(ColumnTypeEnum.STRING, 200); 1717 termValueSetTable 1718 .addIndex("20190722.32", "IDX_VALUESET_URL") 1719 .unique(true) 1720 .withColumns("URL"); 1721 termValueSetTable.addColumn("RES_ID").nonNullable().type(ColumnTypeEnum.LONG); 1722 termValueSetTable 1723 .addForeignKey("20190722.33", "FK_TRMVALUESET_RES") 1724 .toColumn("RES_ID") 1725 .references("HFJ_RESOURCE", "RES_ID"); 1726 termValueSetTable.addColumn("NAME").nullable().type(ColumnTypeEnum.STRING, 200); 1727 1728 version.onTable("TRM_VALUESET") 1729 .renameColumn("20190722.34", "NAME", "VSNAME", true, true); 1730 version.onTable("TRM_VALUESET") 1731 .modifyColumn("20190722.35", "RES_ID").nullable().withType(ColumnTypeEnum.LONG); 1732 1733 Builder.BuilderWithTableName termValueSetTableChange = version.onTable("TRM_VALUESET"); 1734 termValueSetTableChange.addColumn("20190722.36", "EXPANSION_STATUS").nonNullable().type(ColumnTypeEnum.STRING, 50); 1735 termValueSetTableChange 1736 .addIndex("20190722.37", "IDX_VALUESET_EXP_STATUS") 1737 .unique(false) 1738 .withColumns("EXPANSION_STATUS"); 1739 1740 // TermValueSetConcept 1741 version.startSectionWithMessage("Processing table: TRM_VALUESET_CONCEPT"); 1742 version.addIdGenerator("20190722.38", "SEQ_VALUESET_CONCEPT_PID"); 1743 Builder.BuilderAddTableByColumns termValueSetConceptTable = version.addTableByColumns("20190722.39", "TRM_VALUESET_CONCEPT", "PID"); 1744 termValueSetConceptTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1745 termValueSetConceptTable.addColumn("VALUESET_PID").nonNullable().type(ColumnTypeEnum.LONG); 1746 termValueSetConceptTable 1747 .addForeignKey("20190722.40", "FK_TRM_VALUESET_PID") 1748 .toColumn("VALUESET_PID") 1749 .references("TRM_VALUESET", "PID"); 1750 termValueSetConceptTable.addColumn("SYSTEM_URL").nonNullable().type(ColumnTypeEnum.STRING, 200); 1751 termValueSetConceptTable.addColumn("CODEVAL").nonNullable().type(ColumnTypeEnum.STRING, 500); 1752 termValueSetConceptTable.addColumn("DISPLAY").nullable().type(ColumnTypeEnum.STRING, 400); 1753 version.onTable("TRM_VALUESET_CONCEPT") 1754 .renameColumn("20190722.41", "CODE", "CODEVAL", true, true) 1755 .renameColumn("20190722.42", "SYSTEM", "SYSTEM_URL", true, true); 1756 1757 version.startSectionWithMessage("Processing table: TRM_VALUESET_CONCEPT, swapping index for unique constraint"); 1758 termValueSetConceptTable.dropIndex("20190801.1", "IDX_VALUESET_CONCEPT_CS_CD"); 1759 // This index has been renamed in later versions. As such, allowing failure here as some DBs disallow 1760 // multiple indexes referencing the same set of columns. 1761 termValueSetConceptTable 1762 .addIndex("20190801.2", "IDX_VS_CONCEPT_CS_CD") 1763 .unique(true) 1764 .withColumns("VALUESET_PID", "SYSTEM_URL", "CODEVAL").failureAllowed(); 1765 1766 // TermValueSetConceptDesignation 1767 version.startSectionWithMessage("Processing table: TRM_VALUESET_C_DESIGNATION"); 1768 version.addIdGenerator("20190801.3", "SEQ_VALUESET_C_DSGNTN_PID"); 1769 Builder.BuilderAddTableByColumns termValueSetConceptDesignationTable = version.addTableByColumns("20190801.4", "TRM_VALUESET_C_DESIGNATION", "PID"); 1770 termValueSetConceptDesignationTable.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1771 termValueSetConceptDesignationTable.addColumn("VALUESET_CONCEPT_PID").nonNullable().type(ColumnTypeEnum.LONG); 1772 termValueSetConceptDesignationTable 1773 .addForeignKey("20190801.5", "FK_TRM_VALUESET_CONCEPT_PID") 1774 .toColumn("VALUESET_CONCEPT_PID") 1775 .references("TRM_VALUESET_CONCEPT", "PID"); 1776 termValueSetConceptDesignationTable.addColumn("LANG").nullable().type(ColumnTypeEnum.STRING, 500); 1777 termValueSetConceptDesignationTable.addColumn("USE_SYSTEM").nullable().type(ColumnTypeEnum.STRING, 500); 1778 termValueSetConceptDesignationTable.addColumn("USE_CODE").nullable().type(ColumnTypeEnum.STRING, 500); 1779 termValueSetConceptDesignationTable.addColumn("USE_DISPLAY").nullable().type(ColumnTypeEnum.STRING, 500); 1780 termValueSetConceptDesignationTable.addColumn("VAL").nonNullable().type(ColumnTypeEnum.STRING, 500); 1781 1782 // This index turned out not to be needed so it is disabled 1783 termValueSetConceptDesignationTable 1784 .addIndex("20190801.6", "IDX_VALUESET_C_DSGNTN_VAL") 1785 .unique(false) 1786 .withColumns("VAL") 1787 .doNothing(); 1788 1789 // TermCodeSystemVersion 1790 version.startSectionWithMessage("Processing table: TRM_CODESYSTEM_VER"); 1791 Builder.BuilderWithTableName termCodeSystemVersionTable = version.onTable("TRM_CODESYSTEM_VER"); 1792 termCodeSystemVersionTable.addColumn("20190814.1", "CS_DISPLAY").nullable().type(ColumnTypeEnum.STRING, 200); 1793 1794 // ResourceReindexJobEntry 1795 version.addIdGenerator("20190814.2", "SEQ_RES_REINDEX_JOB"); 1796 Builder.BuilderAddTableByColumns reindex = version.addTableByColumns("20190814.3", "HFJ_RES_REINDEX_JOB", "PID"); 1797 reindex.addColumn("PID").nonNullable().type(ColumnTypeEnum.LONG); 1798 reindex.addColumn("RES_TYPE").nullable().type(ColumnTypeEnum.STRING, 100); 1799 reindex.addColumn("UPDATE_THRESHOLD_HIGH").nonNullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1800 reindex.addColumn("JOB_DELETED").nonNullable().type(ColumnTypeEnum.BOOLEAN); 1801 reindex.addColumn("UPDATE_THRESHOLD_LOW").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1802 reindex.addColumn("SUSPENDED_UNTIL").nullable().type(ColumnTypeEnum.DATE_TIMESTAMP); 1803 reindex.addColumn("REINDEX_COUNT").nullable().type(ColumnTypeEnum.INT); 1804 1805 // Search 1806 version.onTable("HFJ_SEARCH") 1807 .addColumn("20190814.4", "SEARCH_DELETED").nullable().type(ColumnTypeEnum.BOOLEAN); 1808 version.onTable("HFJ_SEARCH") 1809 .modifyColumn("20190814.5", "SEARCH_LAST_RETURNED").nonNullable().withType(ColumnTypeEnum.DATE_TIMESTAMP); 1810 version.onTable("HFJ_SEARCH") 1811 .addColumn("20190814.6", "SEARCH_PARAM_MAP").nullable().type(ColumnTypeEnum.BLOB); 1812 version.onTable("HFJ_SEARCH") 1813 .modifyColumn("20190814.7", "SEARCH_UUID").nonNullable().withType(ColumnTypeEnum.STRING, Search.SEARCH_UUID_COLUMN_LENGTH); 1814 1815 version.onTable("HFJ_SEARCH_PARM").dropThisTable("20190814.8"); 1816 1817 // Make some columns non-nullable that were previously nullable - These are marked as failure allowed, since 1818 // SQL Server won't let us change nullability on columns with indexes pointing to them 1819 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20190814.9", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1820 version.onTable("HFJ_SPIDX_DATE").modifyColumn("20190814.10", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1821 version.onTable("HFJ_SPIDX_STRING").modifyColumn("20190814.11", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1822 version.onTable("HFJ_SPIDX_STRING").addColumn("20190814.12", "HASH_IDENTITY").nullable().type(ColumnTypeEnum.LONG); 1823 version.onTable("HFJ_SPIDX_STRING").addIndex("20190814.13", "IDX_SP_STRING_HASH_IDENT").unique(false).withColumns("HASH_IDENTITY"); 1824 version.onTable("HFJ_SPIDX_COORDS").modifyColumn("20190814.14", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1825 version.onTable("HFJ_SPIDX_QUANTITY").modifyColumn("20190814.15", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1826 version.onTable("HFJ_SPIDX_QUANTITY").dropColumn("20190814.16", "HASH_UNITS_AND_VALPREFIX"); 1827 version.onTable("HFJ_SPIDX_QUANTITY").dropColumn("20190814.17", "HASH_VALPREFIX"); 1828 version.onTable("HFJ_SPIDX_NUMBER").modifyColumn("20190814.18", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1829 version.onTable("HFJ_SPIDX_TOKEN").modifyColumn("20190814.19", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1830 version.onTable("HFJ_SPIDX_URI").modifyColumn("20190814.20", "RES_TYPE").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 100); 1831 version.onTable("HFJ_SPIDX_URI").modifyColumn("20190814.21", "SP_URI").nullable().failureAllowed().withType(ColumnTypeEnum.STRING, 254); 1832 version.onTable("TRM_CODESYSTEM").modifyColumn("20190814.22", "CODE_SYSTEM_URI").nonNullable().failureAllowed().withType(ColumnTypeEnum.STRING, 200); 1833 version.onTable("TRM_CODESYSTEM").modifyColumn("20190814.23", "CS_NAME").nullable().failureAllowed().withType(ColumnTypeEnum.STRING, 200); 1834 version.onTable("TRM_CODESYSTEM_VER").modifyColumn("20190814.24", "CS_VERSION_ID").nullable().failureAllowed().withType(ColumnTypeEnum.STRING, 200); 1835 } 1836 1837 1838 private void init360() { // 20180918 - 20181112 1839 Builder version = forVersion(VersionEnum.V3_6_0); 1840 1841 // Resource Link 1842 Builder.BuilderWithTableName resourceLink = version.onTable("HFJ_RES_LINK"); 1843 version.startSectionWithMessage("Starting work on table: " + resourceLink.getTableName()); 1844 resourceLink 1845 .modifyColumn("20180929.1", "SRC_PATH") 1846 .nonNullable() 1847 .withType(ColumnTypeEnum.STRING, 200); 1848 1849 // Search 1850 Builder.BuilderWithTableName search = version.onTable("HFJ_SEARCH"); 1851 version.startSectionWithMessage("Starting work on table: " + search.getTableName()); 1852 search 1853 .addColumn("20181001.1", "OPTLOCK_VERSION") 1854 .nullable() 1855 .type(ColumnTypeEnum.INT); 1856 1857 version.addTableRawSql("20181104.1", "HFJ_RES_REINDEX_JOB") 1858 .addSql(DriverTypeEnum.MSSQL_2012, "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))") 1859 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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))") 1860 .addSql(DriverTypeEnum.MARIADB_10_1, "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))") 1861 .addSql(DriverTypeEnum.POSTGRES_9_4, "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))") 1862 .addSql(DriverTypeEnum.MYSQL_5_7, " 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))") 1863 .addSql(DriverTypeEnum.ORACLE_12C, "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))"); 1864 1865 version.onTable("TRM_CONCEPT_DESIG").addColumn("20181104.2", "CS_VER_PID").nullable().type(ColumnTypeEnum.LONG); 1866 version.onTable("TRM_CONCEPT_DESIG").addForeignKey("20181104.3", "FK_CONCEPTDESIG_CSV").toColumn("CS_VER_PID").references("TRM_CODESYSTEM_VER", "PID"); 1867 1868 version.onTable("TRM_CONCEPT_PROPERTY").addColumn("20181104.4", "CS_VER_PID").nullable().type(ColumnTypeEnum.LONG); 1869 version.onTable("TRM_CONCEPT_PROPERTY").addForeignKey("20181104.5", "FK_CONCEPTPROP_CSV").toColumn("CS_VER_PID").references("TRM_CODESYSTEM_VER", "PID"); 1870 1871 version.onTable("TRM_CONCEPT").addColumn("20181104.6", "PARENT_PIDS").nullable().type(ColumnTypeEnum.CLOB); 1872 1873 } 1874 1875 private void init350() { // 20180601 - 20180917 1876 Builder version = forVersion(VersionEnum.V3_5_0); 1877 1878 // Forced ID changes 1879 Builder.BuilderWithTableName forcedId = version.onTable("HFJ_FORCED_ID"); 1880 version.startSectionWithMessage("Starting work on table: " + forcedId.getTableName()); 1881 1882 forcedId 1883 .dropIndex("20180827.1", "IDX_FORCEDID_TYPE_FORCEDID"); 1884 forcedId 1885 .dropIndex("20180827.2", "IDX_FORCEDID_TYPE_RESID"); 1886 1887 forcedId 1888 .addIndex("20180827.3", "IDX_FORCEDID_TYPE_FID") 1889 .unique(true) 1890 .withColumns("RESOURCE_TYPE", "FORCED_ID"); 1891 1892 // Indexes - Coords 1893 Builder.BuilderWithTableName spidxCoords = version.onTable("HFJ_SPIDX_COORDS"); 1894 version.startSectionWithMessage("Starting work on table: " + spidxCoords.getTableName()); 1895 spidxCoords 1896 .addColumn("20180903.1", "HASH_IDENTITY") 1897 .nullable() 1898 .type(ColumnTypeEnum.LONG); 1899 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1900 spidxCoords 1901 .dropIndex("20180903.2", "IDX_SP_COORDS"); 1902 spidxCoords 1903 .addIndex("20180903.4", "IDX_SP_COORDS_HASH") 1904 .unique(false) 1905 .withColumns("HASH_IDENTITY", "SP_LATITUDE", "SP_LONGITUDE"); 1906 spidxCoords 1907 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.5") 1908 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1909 .setColumnName("HASH_IDENTITY") 1910 ); 1911 } 1912 1913 // Indexes - Date 1914 Builder.BuilderWithTableName spidxDate = version.onTable("HFJ_SPIDX_DATE"); 1915 version.startSectionWithMessage("Starting work on table: " + spidxDate.getTableName()); 1916 spidxDate 1917 .addColumn("20180903.6", "HASH_IDENTITY") 1918 .nullable() 1919 .type(ColumnTypeEnum.LONG); 1920 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1921 spidxDate 1922 .dropIndex("20180903.7", "IDX_SP_TOKEN"); 1923 spidxDate 1924 .addIndex("20180903.8", "IDX_SP_DATE_HASH") 1925 .unique(false) 1926 .withColumns("HASH_IDENTITY", "SP_VALUE_LOW", "SP_VALUE_HIGH") 1927 .doNothing(); 1928 spidxDate 1929 .dropIndex("20180903.9", "IDX_SP_DATE"); 1930 spidxDate 1931 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.10") 1932 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1933 .setColumnName("HASH_IDENTITY") 1934 ); 1935 } 1936 1937 // Indexes - Number 1938 Builder.BuilderWithTableName spidxNumber = version.onTable("HFJ_SPIDX_NUMBER"); 1939 version.startSectionWithMessage("Starting work on table: " + spidxNumber.getTableName()); 1940 spidxNumber 1941 .addColumn("20180903.11", "HASH_IDENTITY") 1942 .nullable() 1943 .type(ColumnTypeEnum.LONG); 1944 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1945 spidxNumber 1946 .dropIndex("20180903.12", "IDX_SP_NUMBER"); 1947 spidxNumber 1948 .addIndex("20180903.13", "IDX_SP_NUMBER_HASH_VAL") 1949 .unique(false) 1950 .withColumns("HASH_IDENTITY", "SP_VALUE") 1951 .doNothing(); 1952 spidxNumber 1953 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.14") 1954 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1955 .setColumnName("HASH_IDENTITY") 1956 ); 1957 } 1958 1959 // Indexes - Quantity 1960 Builder.BuilderWithTableName spidxQuantity = version.onTable("HFJ_SPIDX_QUANTITY"); 1961 version.startSectionWithMessage("Starting work on table: " + spidxQuantity.getTableName()); 1962 spidxQuantity 1963 .addColumn("20180903.15", "HASH_IDENTITY") 1964 .nullable() 1965 .type(ColumnTypeEnum.LONG); 1966 spidxQuantity 1967 .addColumn("20180903.16", "HASH_IDENTITY_SYS_UNITS") 1968 .nullable() 1969 .type(ColumnTypeEnum.LONG); 1970 spidxQuantity 1971 .addColumn("20180903.17", "HASH_IDENTITY_AND_UNITS") 1972 .nullable() 1973 .type(ColumnTypeEnum.LONG); 1974 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 1975 spidxQuantity 1976 .dropIndex("20180903.18", "IDX_SP_QUANTITY"); 1977 spidxQuantity 1978 .addIndex("20180903.19", "IDX_SP_QUANTITY_HASH") 1979 .unique(false) 1980 .withColumns("HASH_IDENTITY", "SP_VALUE"); 1981 spidxQuantity 1982 .addIndex("20180903.20", "IDX_SP_QUANTITY_HASH_UN") 1983 .unique(false) 1984 .withColumns("HASH_IDENTITY_AND_UNITS", "SP_VALUE"); 1985 spidxQuantity 1986 .addIndex("20180903.21", "IDX_SP_QUANTITY_HASH_SYSUN") 1987 .unique(false) 1988 .withColumns("HASH_IDENTITY_SYS_UNITS", "SP_VALUE"); 1989 spidxQuantity 1990 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.22") 1991 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 1992 .addCalculator("HASH_IDENTITY_AND_UNITS", t -> ResourceIndexedSearchParamQuantity.calculateHashUnits(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_UNITS"))) 1993 .addCalculator("HASH_IDENTITY_SYS_UNITS", t -> ResourceIndexedSearchParamQuantity.calculateHashSystemAndUnits(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_SYSTEM"), t.getString("SP_UNITS"))) 1994 .setColumnName("HASH_IDENTITY") 1995 ); 1996 } 1997 1998 // Indexes - String 1999 Builder.BuilderWithTableName spidxString = version.onTable("HFJ_SPIDX_STRING"); 2000 version.startSectionWithMessage("Starting work on table: " + spidxString.getTableName()); 2001 spidxString 2002 .addColumn("20180903.23", "HASH_NORM_PREFIX") 2003 .nullable() 2004 .type(ColumnTypeEnum.LONG); 2005 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2006 spidxString 2007 .dropIndex("20180903.24", "IDX_SP_STRING"); 2008 spidxString 2009 .addIndex("20180903.25", "IDX_SP_STRING_HASH_NRM") 2010 .unique(false) 2011 .withColumns("HASH_NORM_PREFIX", "SP_VALUE_NORMALIZED"); 2012 spidxString 2013 .addColumn("20180903.26", "HASH_EXACT") 2014 .nullable() 2015 .type(ColumnTypeEnum.LONG); 2016 spidxString 2017 .addIndex("20180903.27", "IDX_SP_STRING_HASH_EXCT") 2018 .unique(false) 2019 .withColumns("HASH_EXACT"); 2020 spidxString 2021 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.28") 2022 .setColumnName("HASH_NORM_PREFIX") 2023 .addCalculator("HASH_NORM_PREFIX", t -> ResourceIndexedSearchParamString.calculateHashNormalized(new PartitionSettings(), RequestPartitionId.defaultPartition(), new StorageSettings(), t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_VALUE_NORMALIZED"))) 2024 .addCalculator("HASH_EXACT", t -> ResourceIndexedSearchParamString.calculateHashExact(new PartitionSettings(), (ca.uhn.fhir.jpa.model.entity.PartitionablePartitionId) null, t.getResourceType(), t.getParamName(), t.getString("SP_VALUE_EXACT"))) 2025 ); 2026 } 2027 2028 // Indexes - Token 2029 Builder.BuilderWithTableName spidxToken = version.onTable("HFJ_SPIDX_TOKEN"); 2030 version.startSectionWithMessage("Starting work on table: " + spidxToken.getTableName()); 2031 spidxToken 2032 .addColumn("20180903.29", "HASH_IDENTITY") 2033 .nullable() 2034 .type(ColumnTypeEnum.LONG); 2035 spidxToken 2036 .addColumn("20180903.30", "HASH_SYS") 2037 .nullable() 2038 .type(ColumnTypeEnum.LONG); 2039 spidxToken 2040 .addColumn("20180903.31", "HASH_SYS_AND_VALUE") 2041 .nullable() 2042 .type(ColumnTypeEnum.LONG); 2043 spidxToken 2044 .addColumn("20180903.32", "HASH_VALUE") 2045 .nullable() 2046 .type(ColumnTypeEnum.LONG); 2047 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2048 spidxToken 2049 .dropIndex("20180903.33", "IDX_SP_TOKEN"); 2050 spidxToken 2051 .dropIndex("20180903.34", "IDX_SP_TOKEN_UNQUAL"); 2052 spidxToken 2053 .addIndex("20180903.35", "IDX_SP_TOKEN_HASH") 2054 .unique(false) 2055 .withColumns("HASH_IDENTITY") 2056 .doNothing(); 2057 spidxToken 2058 .addIndex("20180903.36", "IDX_SP_TOKEN_HASH_S") 2059 .unique(false) 2060 .withColumns("HASH_SYS") 2061 .doNothing(); 2062 spidxToken 2063 .addIndex("20180903.37", "IDX_SP_TOKEN_HASH_SV") 2064 .unique(false) 2065 .withColumns("HASH_SYS_AND_VALUE") 2066 .doNothing(); 2067 spidxToken 2068 .addIndex("20180903.38", "IDX_SP_TOKEN_HASH_V") 2069 .unique(false) 2070 .withColumns("HASH_VALUE") 2071 .doNothing(); 2072 spidxToken 2073 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.39") 2074 .setColumnName("HASH_IDENTITY") 2075 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getString("SP_NAME"))) 2076 .addCalculator("HASH_SYS", t -> ResourceIndexedSearchParamToken.calculateHashSystem(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getParamName(), t.getString("SP_SYSTEM"))) 2077 .addCalculator("HASH_SYS_AND_VALUE", t -> ResourceIndexedSearchParamToken.calculateHashSystemAndValue(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getParamName(), t.getString("SP_SYSTEM"), t.getString("SP_VALUE"))) 2078 .addCalculator("HASH_VALUE", t -> ResourceIndexedSearchParamToken.calculateHashValue(new PartitionSettings(), RequestPartitionId.defaultPartition(), t.getResourceType(), t.getParamName(), t.getString("SP_VALUE"))) 2079 ); 2080 } 2081 2082 // Indexes - URI 2083 Builder.BuilderWithTableName spidxUri = version.onTable("HFJ_SPIDX_URI"); 2084 version.startSectionWithMessage("Starting work on table: " + spidxUri.getTableName()); 2085 spidxUri 2086 .addColumn("20180903.40", "HASH_IDENTITY") 2087 .nullable() 2088 .type(ColumnTypeEnum.LONG); 2089 if (!myFlags.contains(FlagEnum.NO_MIGRATE_HASHES)) { 2090 spidxUri 2091 .addIndex("20180903.41", "IDX_SP_URI_HASH_IDENTITY") 2092 .unique(false) 2093 .withColumns("HASH_IDENTITY", "SP_URI"); 2094 spidxUri 2095 .addColumn("20180903.42", "HASH_URI") 2096 .nullable() 2097 .type(ColumnTypeEnum.LONG); 2098 spidxUri 2099 .addIndex("20180903.43", "IDX_SP_URI_HASH_URI") 2100 .unique(false) 2101 .withColumns("HASH_URI"); 2102 spidxUri 2103 .addTask(new CalculateHashesTask(VersionEnum.V3_5_0, "20180903.44") 2104 .setColumnName("HASH_IDENTITY") 2105 .addCalculator("HASH_IDENTITY", t -> BaseResourceIndexedSearchParam.calculateHashIdentity(new PartitionSettings(), (RequestPartitionId) null, t.getResourceType(), t.getString("SP_NAME"))) 2106 .addCalculator("HASH_URI", t -> ResourceIndexedSearchParamUri.calculateHashUri(new PartitionSettings(), (RequestPartitionId) null, t.getResourceType(), t.getString("SP_NAME"), t.getString("SP_URI"))) 2107 ); 2108 } 2109 2110 // Search Parameter Presence 2111 Builder.BuilderWithTableName spp = version.onTable("HFJ_RES_PARAM_PRESENT"); 2112 version.startSectionWithMessage("Starting work on table: " + spp.getTableName()); 2113 spp.dropIndex("20180903.45", "IDX_RESPARMPRESENT_SPID_RESID"); 2114 spp 2115 .addColumn("20180903.46", "HASH_PRESENCE") 2116 .nullable() 2117 .type(ColumnTypeEnum.LONG); 2118 spp 2119 .addIndex("20180903.47", "IDX_RESPARMPRESENT_HASHPRES") 2120 .unique(false) 2121 .withColumns("HASH_PRESENCE"); 2122 2123 ArbitrarySqlTask consolidateSearchParamPresenceIndexesTask = new ArbitrarySqlTask(VersionEnum.V3_5_0, "20180903.48", "HFJ_SEARCH_PARM", "Consolidate search parameter presence indexes"); 2124 consolidateSearchParamPresenceIndexesTask.setExecuteOnlyIfTableExists("HFJ_SEARCH_PARM"); 2125 consolidateSearchParamPresenceIndexesTask.setBatchSize(1); 2126 2127 String sql = "SELECT " + 2128 "HFJ_SEARCH_PARM.RES_TYPE RES_TYPE, HFJ_SEARCH_PARM.PARAM_NAME PARAM_NAME, " + 2129 "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 " + 2130 "from HFJ_RES_PARAM_PRESENT " + 2131 "join HFJ_SEARCH_PARM ON (HFJ_SEARCH_PARM.PID = HFJ_RES_PARAM_PRESENT.SP_ID) " + 2132 "where HFJ_RES_PARAM_PRESENT.HASH_PRESENCE is null"; 2133 consolidateSearchParamPresenceIndexesTask.addExecuteOnlyIfColumnExists("HFJ_RES_PARAM_PRESENT", "SP_ID"); 2134 consolidateSearchParamPresenceIndexesTask.addQuery(sql, ArbitrarySqlTask.QueryModeEnum.BATCH_UNTIL_NO_MORE, t -> { 2135 Number pid = (Number) t.get("PID"); 2136 Boolean present = columnToBoolean(t.get("SP_PRESENT")); 2137 String resType = (String) t.get("RES_TYPE"); 2138 String paramName = (String) t.get("PARAM_NAME"); 2139 Long hash = SearchParamPresentEntity.calculateHashPresence(new PartitionSettings(), (RequestPartitionId) null, resType, paramName, present); 2140 consolidateSearchParamPresenceIndexesTask.executeSql("HFJ_RES_PARAM_PRESENT", "update HFJ_RES_PARAM_PRESENT set HASH_PRESENCE = ? where PID = ?", hash, pid); 2141 }); 2142 version.addTask(consolidateSearchParamPresenceIndexesTask); 2143 2144 // SP_ID is no longer needed 2145 spp.dropColumn("20180903.49", "SP_ID"); 2146 2147 // Concept 2148 Builder.BuilderWithTableName trmConcept = version.onTable("TRM_CONCEPT"); 2149 version.startSectionWithMessage("Starting work on table: " + trmConcept.getTableName()); 2150 trmConcept 2151 .addColumn("20180903.50", "CONCEPT_UPDATED") 2152 .nullable() 2153 .type(ColumnTypeEnum.DATE_TIMESTAMP); 2154 trmConcept 2155 .addIndex("20180903.51", "IDX_CONCEPT_UPDATED") 2156 .unique(false) 2157 .withColumns("CONCEPT_UPDATED"); 2158 trmConcept 2159 .modifyColumn("20180903.52", "CODE") 2160 .nonNullable() 2161 .withType(ColumnTypeEnum.STRING, 500); 2162 2163 // Concept Designation 2164 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_DESIG"); 2165 version 2166 .addTableRawSql("20180907.1", "TRM_CONCEPT_DESIG") 2167 .addSql(DriverTypeEnum.H2_EMBEDDED, "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))") 2168 .addSql(DriverTypeEnum.H2_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2169 .addSql(DriverTypeEnum.H2_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 2170 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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))") 2171 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2172 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 2173 .addSql(DriverTypeEnum.MYSQL_5_7, "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") 2174 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 2175 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 2176 .addSql(DriverTypeEnum.MARIADB_10_1, "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))") 2177 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 2178 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 2179 .addSql(DriverTypeEnum.ORACLE_12C, "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))") 2180 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2181 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 2182 .addSql(DriverTypeEnum.POSTGRES_9_4, "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))") 2183 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2184 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 2185 .addSql(DriverTypeEnum.MSSQL_2012, "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))") 2186 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2187 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_DESIG add constraint FK_CONCEPTDESIG_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT"); 2188 2189 // Concept Property 2190 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_PROPERTY"); 2191 version 2192 .addTableRawSql("20180907.2", "TRM_CONCEPT_PROPERTY") 2193 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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))") 2194 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2195 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 2196 .addSql(DriverTypeEnum.MARIADB_10_1, "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))") 2197 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 2198 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 2199 .addSql(DriverTypeEnum.MYSQL_5_7, "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))") 2200 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER (PID)") 2201 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT (PID)") 2202 .addSql(DriverTypeEnum.ORACLE_12C, "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))") 2203 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2204 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 2205 .addSql(DriverTypeEnum.POSTGRES_9_4, "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))") 2206 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2207 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT") 2208 .addSql(DriverTypeEnum.MSSQL_2012, "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))") 2209 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CSV foreign key (CS_VER_PID) references TRM_CODESYSTEM_VER") 2210 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_PROPERTY add constraint FK_CONCEPTPROP_CONCEPT foreign key (CONCEPT_PID) references TRM_CONCEPT"); 2211 2212 // Concept Map - Map 2213 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP"); 2214 version 2215 .addTableRawSql("20180907.3", "TRM_CONCEPT_MAP") 2216 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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))") 2217 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 2218 .addSql(DriverTypeEnum.MYSQL_5_7, "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))") 2219 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 2220 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE (RES_ID)") 2221 .addSql(DriverTypeEnum.ORACLE_12C, "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))") 2222 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 2223 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 2224 .addSql(DriverTypeEnum.POSTGRES_9_4, "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))") 2225 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 2226 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 2227 .addSql(DriverTypeEnum.MSSQL_2012, "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))") 2228 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)") 2229 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE") 2230 .addSql(DriverTypeEnum.MARIADB_10_1, "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))") 2231 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP add constraint FK_TRMCONCEPTMAP_RES foreign key (RES_ID) references HFJ_RESOURCE (RES_ID)") 2232 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP add constraint IDX_CONCEPT_MAP_URL unique (URL)"); 2233 2234 // Concept Map - Group 2235 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GROUP"); 2236 version 2237 .addTableRawSql("20180907.4", "TRM_CONCEPT_MAP_GROUP") 2238 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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))") 2239 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 2240 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create unique index IDX_CONCEPT_MAP_URL on TRM_CONCEPT_MAP (URL)") 2241 .addSql(DriverTypeEnum.ORACLE_12C, "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))") 2242 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 2243 .addSql(DriverTypeEnum.MARIADB_10_1, "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))") 2244 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP (PID)") 2245 .addSql(DriverTypeEnum.MYSQL_5_7, "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))") 2246 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP (PID)") 2247 .addSql(DriverTypeEnum.MSSQL_2012, "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))") 2248 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP") 2249 .addSql(DriverTypeEnum.POSTGRES_9_4, "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))") 2250 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP_GROUP add constraint FK_TCMGROUP_CONCEPTMAP foreign key (CONCEPT_MAP_PID) references TRM_CONCEPT_MAP"); 2251 2252 // Concept Map - Group Element 2253 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GRP_ELEMENT"); 2254 version 2255 .addTableRawSql("20180907.5", "TRM_CONCEPT_MAP_GRP_ELEMENT") 2256 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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))") 2257 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 2258 .addSql(DriverTypeEnum.MARIADB_10_1, "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))") 2259 .addSql(DriverTypeEnum.MARIADB_10_1, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP (PID)") 2260 .addSql(DriverTypeEnum.MARIADB_10_1, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 2261 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 2262 .addSql(DriverTypeEnum.MYSQL_5_7, "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))") 2263 .addSql(DriverTypeEnum.MYSQL_5_7, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 2264 .addSql(DriverTypeEnum.MYSQL_5_7, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP (PID)") 2265 .addSql(DriverTypeEnum.POSTGRES_9_4, "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))") 2266 .addSql(DriverTypeEnum.POSTGRES_9_4, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 2267 .addSql(DriverTypeEnum.POSTGRES_9_4, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 2268 .addSql(DriverTypeEnum.ORACLE_12C, "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))") 2269 .addSql(DriverTypeEnum.ORACLE_12C, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP") 2270 .addSql(DriverTypeEnum.ORACLE_12C, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 2271 .addSql(DriverTypeEnum.MSSQL_2012, "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))") 2272 .addSql(DriverTypeEnum.MSSQL_2012, "create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE)") 2273 .addSql(DriverTypeEnum.MSSQL_2012, "alter table TRM_CONCEPT_MAP_GRP_ELEMENT add constraint FK_TCMGELEMENT_GROUP foreign key (CONCEPT_MAP_GROUP_PID) references TRM_CONCEPT_MAP_GROUP"); 2274 2275 // Concept Map - Group Element Target 2276 version.startSectionWithMessage("Starting work on table: TRM_CONCEPT_MAP_GRP_ELM_TGT"); 2277 version 2278 .addTableRawSql("20180907.6", "TRM_CONCEPT_MAP_GRP_ELM_TGT") 2279 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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))") 2280 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "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") 2281 .addSql(DriverTypeEnum.DERBY_EMBEDDED, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 2282 .addSql(DriverTypeEnum.MARIADB_10_1, "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))") 2283 .addSql(DriverTypeEnum.MARIADB_10_1, "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)") 2284 .addSql(DriverTypeEnum.MARIADB_10_1, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 2285 .addSql(DriverTypeEnum.MYSQL_5_7, "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))") 2286 .addSql(DriverTypeEnum.MYSQL_5_7, "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)") 2287 .addSql(DriverTypeEnum.MYSQL_5_7, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 2288 .addSql(DriverTypeEnum.ORACLE_12C, "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))") 2289 .addSql(DriverTypeEnum.ORACLE_12C, "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") 2290 .addSql(DriverTypeEnum.ORACLE_12C, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 2291 .addSql(DriverTypeEnum.POSTGRES_9_4, "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))") 2292 .addSql(DriverTypeEnum.POSTGRES_9_4, "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") 2293 .addSql(DriverTypeEnum.POSTGRES_9_4, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 2294 .addSql(DriverTypeEnum.MSSQL_2012, "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))") 2295 .addSql(DriverTypeEnum.MSSQL_2012, "create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE)") 2296 .addSql(DriverTypeEnum.MSSQL_2012, "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"); 2297 2298 version.onTable("HFJ_IDX_CMP_STRING_UNIQ").modifyColumn("20180907.7", "IDX_STRING").nonNullable().withType(ColumnTypeEnum.STRING, 200); 2299 2300 2301 } 2302 2303 private Boolean columnToBoolean(Object theValue) { 2304 if (theValue == null) { 2305 return null; 2306 } 2307 if (theValue instanceof Boolean) { 2308 return (Boolean) theValue; 2309 } 2310 2311 long longValue = ((Number) theValue).longValue(); 2312 return longValue == 1L; 2313 } 2314 2315 private void init340() { // 20180401 - 20180528 2316 Builder version = forVersion(VersionEnum.V3_4_0); 2317 2318 // CodeSystem Version 2319 Builder.BuilderWithTableName resourceLink = version.onTable("TRM_CODESYSTEM_VER"); 2320 version.startSectionWithMessage("Starting work on table: " + resourceLink.getTableName()); 2321 resourceLink 2322 .dropIndex("20180401.1", "IDX_CSV_RESOURCEPID_AND_VER"); 2323 resourceLink 2324 .dropColumn("20180401.2", "RES_VERSION_ID"); 2325 resourceLink 2326 .addColumn("20180401.3", "CS_VERSION_ID") 2327 .nullable() 2328 .type(ColumnTypeEnum.STRING, 255); 2329 resourceLink 2330 .addColumn("20180401.4", "CODESYSTEM_PID") 2331 .nullable() 2332 .type(ColumnTypeEnum.LONG); 2333 resourceLink 2334 .addForeignKey("20180401.5", "FK_CODESYSVER_CS_ID") 2335 .toColumn("CODESYSTEM_PID") 2336 .references("TRM_CODESYSTEM", "PID"); 2337 2338 // Concept 2339 Builder.BuilderWithTableName concept = version.onTable("TRM_CONCEPT"); 2340 version.startSectionWithMessage("Starting work on table: " + concept.getTableName()); 2341 concept 2342 .addColumn("20180401.6", "CODE_SEQUENCE") 2343 .nullable() 2344 .type(ColumnTypeEnum.INT); 2345 2346 2347 } 2348 2349 protected void init330() { // 20180114 - 20180329 2350 Builder version = forVersion(VersionEnum.V3_3_0); 2351 2352 version.initializeSchema("20180115.0", new SchemaInitializationProvider("HAPI FHIR", "/ca/uhn/hapi/fhir/jpa/docs/database", "HFJ_RESOURCE", true)); 2353 2354 Builder.BuilderWithTableName hfjResource = version.onTable("HFJ_RESOURCE"); 2355 version.startSectionWithMessage("Starting work on table: " + hfjResource.getTableName()); 2356 hfjResource.dropColumn("20180115.1", "RES_TEXT"); 2357 hfjResource.dropColumn("20180115.2", "RES_ENCODING"); 2358 2359 Builder.BuilderWithTableName hfjResVer = version.onTable("HFJ_RES_VER"); 2360 version.startSectionWithMessage("Starting work on table: " + hfjResVer.getTableName()); 2361 hfjResVer.modifyColumn("20180115.3", "RES_ENCODING") 2362 .nullable(); 2363 hfjResVer.modifyColumn("20180115.4", "RES_TEXT") 2364 .nullable(); 2365 } 2366 2367 public enum FlagEnum { 2368 NO_MIGRATE_HASHES("no-migrate-350-hashes"); 2369 2370 private final String myCommandLineValue; 2371 2372 FlagEnum(String theCommandLineValue) { 2373 myCommandLineValue = theCommandLineValue; 2374 } 2375 2376 public static FlagEnum fromCommandLineValue(String theCommandLineValue) { 2377 Optional<FlagEnum> retVal = Arrays.stream(values()).filter(t -> t.myCommandLineValue.equals(theCommandLineValue)).findFirst(); 2378 return retVal.orElseThrow(() -> { 2379 List<String> validValues = Arrays.stream(values()).map(t -> t.myCommandLineValue).sorted().collect(Collectors.toList()); 2380 return new IllegalArgumentException("Invalid flag \"" + theCommandLineValue + "\". Valid values: " + validValues); 2381 }); 2382 } 2383 } 2384 2385 2386}