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