001/*-
002 * #%L
003 * HAPI FHIR Server - SQL Migration
004 * %%
005 * Copyright (C) 2014 - 2023 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 org.apache.commons.lang3.Validate;
025import org.apache.commons.lang3.builder.EqualsBuilder;
026import org.apache.commons.lang3.builder.HashCodeBuilder;
027import org.intellij.lang.annotations.Language;
028import org.slf4j.Logger;
029import org.slf4j.LoggerFactory;
030import org.springframework.jdbc.core.JdbcTemplate;
031import org.springframework.jdbc.core.RowMapperResultSetExtractor;
032import org.springframework.jdbc.core.SingleColumnRowMapper;
033
034import javax.annotation.Nonnull;
035import javax.sql.DataSource;
036import java.sql.SQLException;
037import java.util.ArrayList;
038import java.util.Collections;
039import java.util.List;
040import java.util.Objects;
041import java.util.Set;
042
043public class DropIndexTask extends BaseTableTask {
044
045        private static final Logger ourLog = LoggerFactory.getLogger(DropIndexTask.class);
046        private String myIndexName;
047        private boolean myOnline;
048
049        public DropIndexTask(String theProductVersion, String theSchemaVersion) {
050                super(theProductVersion, theSchemaVersion);
051        }
052
053        List<String> generateSql() throws SQLException {
054                Validate.notBlank(myIndexName, "indexName must not be blank");
055                Validate.notBlank(getTableName(), "tableName must not be blank");
056
057                if (!JdbcUtils.getIndexNames(getConnectionProperties(), getTableName()).contains(myIndexName)) {
058                        return Collections.emptyList();
059                }
060                boolean isUnique = JdbcUtils.isIndexUnique(getConnectionProperties(), getTableName(), myIndexName);
061
062                return doGenerateSql(isUnique);
063        }
064
065        // testable without jdbc
066        @Nonnull
067        List<String> doGenerateSql(boolean isUnique) {
068                DriverTypeEnum driverType = getDriverType();
069                List<String> sql = new ArrayList<>();
070
071                if (isUnique) {
072                        // Drop constraint
073                        switch (driverType) {
074                                case MYSQL_5_7:
075                                case MARIADB_10_1:
076                                        // Need to quote the index name as the word "PRIMARY" is reserved in MySQL
077                                        sql.add("alter table " + getTableName() + " drop index `" + myIndexName + "`");
078                                        break;
079                                case H2_EMBEDDED:
080                                        sql.add("drop index " + myIndexName);
081                                        break;
082                                case DERBY_EMBEDDED:
083                                        sql.add("alter table " + getTableName() + " drop constraint " + myIndexName);
084                                        break;
085                                case ORACLE_12C:
086                                        sql.add("drop index " + myIndexName + (myOnline?" ONLINE":""));
087                                        break;
088                                case MSSQL_2012:
089                                        sql.add("drop index " + myIndexName + " on " + getTableName() + (myOnline?" WITH (ONLINE = ON)":""));
090                                        break;
091                                case POSTGRES_9_4:
092                                        sql.add("alter table " + getTableName() + " drop constraint if exists " + myIndexName + " cascade");
093                                        sql.add("drop index " + (myOnline?"CONCURRENTLY ":"") + "if exists " + myIndexName + " cascade");
094                                        setTransactional(!myOnline);
095                                        break;
096                                case COCKROACHDB_21_1:
097                                        sql.add("drop index if exists " + getTableName() + "@" + myIndexName + " cascade");
098                                        break;
099                        }
100                } else {
101                        // Drop index
102                        switch (driverType) {
103                                case MYSQL_5_7:
104                                case MARIADB_10_1:
105                                        sql.add("alter table " + getTableName() + " drop index " + myIndexName);
106                                        break;
107                                case POSTGRES_9_4:
108                                        sql.add("drop index " + (myOnline?"CONCURRENTLY ":"") + myIndexName);
109                                        setTransactional(!myOnline);
110                                        break;
111                                case DERBY_EMBEDDED:
112                                case H2_EMBEDDED:
113                                        sql.add("drop index " + myIndexName);
114                                        break;
115                                case ORACLE_12C:
116                                        sql.add("drop index " + myIndexName + (myOnline?" ONLINE":""));
117                                        break;
118                                case MSSQL_2012:
119                                        sql.add("drop index " + getTableName() + "." + myIndexName );
120                                        break;
121                                case COCKROACHDB_21_1:
122                                        sql.add("drop index " + getTableName() + "@" + myIndexName);
123                                        break;
124                        }
125                }
126                return sql;
127        }
128
129        @Override
130        public void validate() {
131                super.validate();
132                Validate.notBlank(myIndexName, "The index name must not be blank");
133
134                setDescription("Drop index " + myIndexName + " from table " + getTableName());
135        }
136
137        @Override
138        public void doExecute() throws SQLException {
139                /*
140                 * Derby and H2 both behave a bit weirdly if you create a unique constraint
141                 * using the @UniqueConstraint annotation in hibernate - They will create a
142                 * constraint with that name, but will then create a shadow index with a different
143                 * name, and it's that different name that gets reported when you query for the
144                 * list of indexes.
145                 *
146                 * For example, on H2 if you create a constraint named "IDX_FOO", the system
147                 * will create an index named "IDX_FOO_INDEX_A" and a constraint named "IDX_FOO".
148                 *
149                 * The following is a solution that uses appropriate native queries to detect
150                 * on the given platforms whether an index name actually corresponds to a
151                 * constraint, and delete that constraint.
152                 */
153
154                if (getDriverType() == DriverTypeEnum.H2_EMBEDDED) {
155                        @Language("SQL") String findConstraintSql = "SELECT DISTINCT constraint_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_name = ? AND table_name = ?";
156                        @Language("SQL") String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
157                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
158                } else if (getDriverType() == DriverTypeEnum.DERBY_EMBEDDED) {
159                        @Language("SQL") String findConstraintSql = "SELECT c.constraintname FROM sys.sysconstraints c, sys.systables t WHERE c.tableid = t.tableid AND c.constraintname = ? AND t.tablename = ?";
160                        @Language("SQL") String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
161                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
162                } else if (getDriverType() == DriverTypeEnum.ORACLE_12C) {
163                        @Language("SQL") String findConstraintSql = "SELECT constraint_name FROM user_constraints WHERE constraint_name = ? AND table_name = ?";
164                        @Language("SQL") String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
165                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
166                } else if (getDriverType() == DriverTypeEnum.MSSQL_2012) {
167                        // Legacy deletion for SQL Server unique indexes
168                        @Language("SQL") String findConstraintSql = "SELECT tc.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc WHERE tc.CONSTRAINT_NAME = ? AND tc.TABLE_NAME = ?";
169                        @Language("SQL") String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
170                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
171                }
172
173                Set<String> indexNames = JdbcUtils.getIndexNames(getConnectionProperties(), getTableName());
174
175                if (!indexNames.contains(myIndexName)) {
176                        logInfo(ourLog, "Index {} does not exist on table {} - No action needed", myIndexName, getTableName());
177                        return;
178                }
179
180                boolean isUnique = JdbcUtils.isIndexUnique(getConnectionProperties(), getTableName(), myIndexName);
181                String uniquenessString = isUnique ? "unique" : "non-unique";
182
183                List<String> sqls = generateSql();
184                if (!sqls.isEmpty()) {
185                        logInfo(ourLog, "Dropping {} index {} on table {}", uniquenessString, myIndexName, getTableName());
186                }
187                for (@Language("SQL") String sql : sqls) {
188                        executeSql(getTableName(), sql);
189                }
190        }
191
192        public void findAndDropConstraint(String theFindConstraintSql, String theDropConstraintSql) {
193                DataSource dataSource = Objects.requireNonNull(getConnectionProperties().getDataSource());
194                getConnectionProperties().getTxTemplate().executeWithoutResult(t -> {
195                        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
196                        RowMapperResultSetExtractor<String> resultSetExtractor = new RowMapperResultSetExtractor<>(new SingleColumnRowMapper<>(String.class));
197                        List<String> outcome = jdbcTemplate.query(theFindConstraintSql, new Object[]{myIndexName, getTableName()}, resultSetExtractor);
198                        assert outcome != null;
199                        for (String next : outcome) {
200                                String sql = theDropConstraintSql.replace("?", next);
201                                executeSql(getTableName(), sql);
202                        }
203                });
204        }
205
206        public DropIndexTask setIndexName(String theIndexName) {
207                myIndexName = theIndexName;
208                return this;
209        }
210
211        @Override
212        protected void generateEquals(EqualsBuilder theBuilder, BaseTask theOtherObject) {
213                DropIndexTask otherObject = (DropIndexTask) theOtherObject;
214                super.generateEquals(theBuilder, otherObject);
215                theBuilder.append(myIndexName, otherObject.myIndexName);
216                theBuilder.append(myOnline, otherObject.myOnline);
217        }
218
219        @Override
220        protected void generateHashCode(HashCodeBuilder theBuilder) {
221                super.generateHashCode(theBuilder);
222                theBuilder.append(myIndexName);
223                theBuilder.append(myOnline);
224        }
225
226        public void setOnline(boolean theFlag) {
227                this.myOnline = theFlag;
228        }
229}