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