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