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