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