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