Source code

001/*-
002 * #%L
003 * HAPI FHIR Server - SQL Migration
004 * %%
005 * Copyright (C) 2014 - 2025 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.taskdef;
021
022import ca.uhn.fhir.jpa.migrate.DriverTypeEnum;
023import ca.uhn.fhir.jpa.migrate.JdbcUtils;
024import jakarta.annotation.Nonnull;
025import org.apache.commons.lang3.Validate;
026import org.apache.commons.lang3.builder.EqualsBuilder;
027import org.apache.commons.lang3.builder.HashCodeBuilder;
028import org.slf4j.Logger;
029import org.slf4j.LoggerFactory;
030
031import java.sql.SQLException;
032import java.util.Arrays;
033import java.util.Collections;
034import java.util.List;
035import java.util.Locale;
036import java.util.Objects;
037import java.util.Set;
038import java.util.stream.Collectors;
039
040public class AddIndexTask extends BaseTableTask {
041
042        static final Logger ourLog = LoggerFactory.getLogger(AddIndexTask.class);
043
044        private String myIndexName;
045        private List<String> myColumns;
046        private List<String> myNullableColumns;
047        private Boolean myUnique = false;
048        private List<String> myIncludeColumns = Collections.emptyList();
049        /** Should the operation avoid taking a lock on the table */
050        private boolean myOnline;
051
052        private MetadataSource myMetadataSource = new MetadataSource();
053
054        public AddIndexTask(String theProductVersion, String theSchemaVersion) {
055                super(theProductVersion, theSchemaVersion);
056        }
057
058        public void setIndexName(String theIndexName) {
059                myIndexName = theIndexName.toUpperCase(Locale.US);
060        }
061
062        public void setColumns(List<String> theColumns) {
063                myColumns = theColumns;
064        }
065
066        public void setUnique(boolean theUnique) {
067                myUnique = theUnique;
068        }
069
070        public List<String> getNullableColumns() {
071                return myNullableColumns;
072        }
073
074        public void setNullableColumns(List<String> theNullableColumns) {
075                this.myNullableColumns = theNullableColumns;
076        }
077
078        @Override
079        public void validate() {
080                super.validate();
081                Validate.notBlank(myIndexName, "Index name not specified");
082                Validate.isTrue(
083                                !myColumns.isEmpty(),
084                                "Columns not specified for AddIndexTask " + myIndexName + " on table " + getTableName());
085                Validate.notNull(myUnique, "Uniqueness not specified");
086                setDescription("Add " + myIndexName + " index to table " + getTableName());
087        }
088
089        @Override
090        public void doExecute() throws SQLException {
091                Set<String> indexNames = JdbcUtils.getIndexNames(getConnectionProperties(), getTableName());
092                if (indexNames.contains(myIndexName)) {
093                        logInfo(ourLog, "Index {} already exists on table {} - No action performed", myIndexName, getTableName());
094                        return;
095                }
096
097                logInfo(
098                                ourLog,
099                                "Going to add a {} index named {} on table {} for columns {}",
100                                (myUnique ? "UNIQUE" : "NON-UNIQUE"),
101                                myIndexName,
102                                getTableName(),
103                                myColumns);
104
105                String sql = generateSql();
106                String tableName = getTableName();
107
108                try {
109                        executeSql(tableName, sql);
110                } catch (Exception e) {
111                        String message = e.toString();
112                        if (message.contains("already exists")
113                                        ||
114                                        // The Oracle message is ORA-01408: such column list already indexed
115                                        // TODO KHS consider db-specific handling here that uses the error code instead of the message so
116                                        // this is language independent
117                                        //  e.g. if the db is Oracle than checking e.getErrorCode() == 1408 should detect this case
118                                        message.contains("already indexed")) {
119                                ourLog.warn("Index {} already exists: {}", myIndexName, e.getMessage());
120                        } else {
121                                throw e;
122                        }
123                }
124        }
125
126        @Nonnull
127        String generateSql() {
128                String unique = myUnique ? "unique " : "";
129                String columns = String.join(", ", myColumns);
130                String includeClause = "";
131                String mssqlWhereClause = "";
132                if (!myIncludeColumns.isEmpty()) {
133                        switch (getDriverType()) {
134                                case POSTGRES_9_4:
135                                case MSSQL_2012:
136                                case COCKROACHDB_21_1:
137                                        includeClause = " INCLUDE (" + String.join(", ", myIncludeColumns) + ")";
138                                        break;
139                                case H2_EMBEDDED:
140                                case DERBY_EMBEDDED:
141                                case MARIADB_10_1:
142                                case MYSQL_5_7:
143                                case ORACLE_12C:
144                                        // These platforms don't support the include clause
145                                        // Per:
146                                        // https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes#postgresql-limitations
147                                        break;
148                        }
149                }
150                if (myUnique && getDriverType() == DriverTypeEnum.MSSQL_2012) {
151                        mssqlWhereClause = buildMSSqlNotNullWhereClause();
152                }
153                // Should we do this non-transactionally?  Avoids a write-lock, but introduces weird failure modes.
154                String postgresOnlineClause = "";
155                String oracleOnlineClause = "";
156                if (myOnline) {
157                        switch (getDriverType()) {
158                                case POSTGRES_9_4:
159                                case COCKROACHDB_21_1:
160                                        postgresOnlineClause = "CONCURRENTLY ";
161                                        // This runs without a lock, and can't be done transactionally.
162                                        setTransactional(false);
163                                        break;
164                                case MSSQL_2012:
165                                        // handled below in buildOnlineCreateWithTryCatchFallback()
166                                        break;
167                                case ORACLE_12C:
168                                        // todo: delete this once we figure out how run Oracle try-catch to match MSSQL.
169                                        if (myMetadataSource.isOnlineIndexSupported(getConnectionProperties())) {
170                                                oracleOnlineClause = " ONLINE DEFERRED INVALIDATION";
171                                        }
172                                        break;
173                                default:
174                        }
175                }
176
177                String bareCreateSql = "create " + unique + "index " + postgresOnlineClause + myIndexName + " on "
178                                + getTableName() + "(" + columns + ")" + includeClause + mssqlWhereClause + oracleOnlineClause;
179
180                String sql;
181                if (myOnline && DriverTypeEnum.MSSQL_2012 == getDriverType()) {
182                        sql = buildOnlineCreateWithTryCatchFallback(bareCreateSql);
183                } else {
184                        sql = bareCreateSql;
185                }
186                return sql;
187        }
188
189        /**
190         * Wrap a Sql Server create index in a try/catch to try it first ONLINE
191         * (meaning no table locks), and on failure, without ONLINE (locking the table).
192         *
193         * This try-catch syntax was manually tested via sql
194         * {@code
195         * BEGIN TRY
196         *      EXEC('create index FOO on TABLE_A (col1)  WITH (ONLINE = ON)');
197         *      select 'Online-OK';
198         * END TRY
199         * BEGIN CATCH
200         *      create index FOO on TABLE_A (col1);
201         *      select 'Offline';
202         * END CATCH;
203         * -- Then inspect the result set - Online-OK means it ran the ONLINE version.
204         * -- Note: we use EXEC() in the online path to lower the severity of the error
205         * -- so the CATCH can catch it.
206         * }
207         *
208         * @param bareCreateSql
209         * @return
210         */
211        static @Nonnull String buildOnlineCreateWithTryCatchFallback(String bareCreateSql) {
212                // Some "Editions" of Sql Server do not support ONLINE.
213                // @format:off
214                return "BEGIN TRY -- try first online, without locking the table \n"
215                                + "    EXEC('" + bareCreateSql + " WITH (ONLINE = ON)');\n"
216                                + "END TRY \n"
217                                + "BEGIN CATCH -- for Editions of Sql Server that don't support ONLINE, run with table locks \n"
218                                + bareCreateSql
219                                + "; \n"
220                                + "END CATCH;";
221                // @format:on
222        }
223
224        @Nonnull
225        private String buildMSSqlNotNullWhereClause() {
226                String mssqlWhereClause = "";
227                if (myNullableColumns == null || myNullableColumns.isEmpty()) {
228                        return mssqlWhereClause;
229                }
230
231                mssqlWhereClause = " WHERE (";
232                mssqlWhereClause += myNullableColumns.stream()
233                                .map(column -> column + " IS NOT NULL ")
234                                .collect(Collectors.joining("AND"));
235                mssqlWhereClause += ")";
236                return mssqlWhereClause;
237        }
238
239        public void setColumns(String... theColumns) {
240                setColumns(Arrays.asList(theColumns));
241        }
242
243        public void setNullableColumns(String... theColumns) {
244                setNullableColumns(Arrays.asList(theColumns));
245        }
246
247        public void setIncludeColumns(String... theIncludeColumns) {
248                setIncludeColumns(Arrays.asList(theIncludeColumns));
249        }
250
251        private void setIncludeColumns(List<String> theIncludeColumns) {
252                Objects.requireNonNull(theIncludeColumns);
253                myIncludeColumns = theIncludeColumns;
254        }
255
256        /**
257         * Add Index without locking the table.
258         */
259        public void setOnline(boolean theFlag) {
260                myOnline = theFlag;
261        }
262
263        @Override
264        protected void generateEquals(EqualsBuilder theBuilder, BaseTask theOtherObject) {
265                super.generateEquals(theBuilder, theOtherObject);
266
267                AddIndexTask otherObject = (AddIndexTask) theOtherObject;
268                theBuilder.append(myIndexName, otherObject.myIndexName);
269                theBuilder.append(myColumns, otherObject.myColumns);
270                theBuilder.append(myUnique, otherObject.myUnique);
271                theBuilder.append(myIncludeColumns, otherObject.myIncludeColumns);
272                theBuilder.append(myOnline, otherObject.myOnline);
273        }
274
275        @Override
276        protected void generateHashCode(HashCodeBuilder theBuilder) {
277                super.generateHashCode(theBuilder);
278                theBuilder.append(myIndexName);
279                theBuilder.append(myColumns);
280                theBuilder.append(myUnique);
281                theBuilder.append(myOnline);
282        }
283
284        public void setMetadataSource(MetadataSource theMetadataSource) {
285                myMetadataSource = theMetadataSource;
286        }
287}