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 java.sql.SQLException;
035import java.util.ArrayList;
036import java.util.Collections;
037import java.util.List;
038import java.util.Objects;
039import java.util.Set;
040import javax.annotation.Nonnull;
041import javax.sql.DataSource;
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()
090                                                        + (myOnline ? " WITH (ONLINE = ON)" : ""));
091                                        break;
092                                case POSTGRES_9_4:
093                                        sql.add("alter table " + getTableName() + " drop constraint if exists " + myIndexName + " cascade");
094                                        sql.add("drop index " + (myOnline ? "CONCURRENTLY " : "") + "if exists " + myIndexName
095                                                        + " cascade");
096                                        setTransactional(!myOnline);
097                                        break;
098                                case COCKROACHDB_21_1:
099                                        sql.add("drop index if exists " + getTableName() + "@" + myIndexName + " cascade");
100                                        break;
101                        }
102                } else {
103                        // Drop index
104                        switch (driverType) {
105                                case MYSQL_5_7:
106                                case MARIADB_10_1:
107                                        sql.add("alter table " + getTableName() + " drop index " + myIndexName);
108                                        break;
109                                case POSTGRES_9_4:
110                                        sql.add("drop index " + (myOnline ? "CONCURRENTLY " : "") + myIndexName);
111                                        setTransactional(!myOnline);
112                                        break;
113                                case DERBY_EMBEDDED:
114                                case H2_EMBEDDED:
115                                        sql.add("drop index " + myIndexName);
116                                        break;
117                                case ORACLE_12C:
118                                        sql.add("drop index " + myIndexName + (myOnline ? " ONLINE" : ""));
119                                        break;
120                                case MSSQL_2012:
121                                        sql.add("drop index " + getTableName() + "." + myIndexName);
122                                        break;
123                                case COCKROACHDB_21_1:
124                                        sql.add("drop index " + getTableName() + "@" + myIndexName);
125                                        break;
126                        }
127                }
128                return sql;
129        }
130
131        @Override
132        public void validate() {
133                super.validate();
134                Validate.notBlank(myIndexName, "The index name must not be blank");
135
136                setDescription("Drop index " + myIndexName + " from table " + getTableName());
137        }
138
139        @Override
140        public void doExecute() throws SQLException {
141                /*
142                 * Derby and H2 both behave a bit weirdly if you create a unique constraint
143                 * using the @UniqueConstraint annotation in hibernate - They will create a
144                 * constraint with that name, but will then create a shadow index with a different
145                 * name, and it's that different name that gets reported when you query for the
146                 * list of indexes.
147                 *
148                 * For example, on H2 if you create a constraint named "IDX_FOO", the system
149                 * will create an index named "IDX_FOO_INDEX_A" and a constraint named "IDX_FOO".
150                 *
151                 * The following is a solution that uses appropriate native queries to detect
152                 * on the given platforms whether an index name actually corresponds to a
153                 * constraint, and delete that constraint.
154                 */
155
156                if (getDriverType() == DriverTypeEnum.H2_EMBEDDED) {
157                        @Language("SQL")
158                        String findConstraintSql =
159                                        "SELECT DISTINCT constraint_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_name = ? AND table_name = ?";
160                        @Language("SQL")
161                        String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
162                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
163                } else if (getDriverType() == DriverTypeEnum.DERBY_EMBEDDED) {
164                        @Language("SQL")
165                        String findConstraintSql =
166                                        "SELECT c.constraintname FROM sys.sysconstraints c, sys.systables t WHERE c.tableid = t.tableid AND c.constraintname = ? AND t.tablename = ?";
167                        @Language("SQL")
168                        String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
169                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
170                } else if (getDriverType() == DriverTypeEnum.ORACLE_12C) {
171                        @Language("SQL")
172                        String findConstraintSql =
173                                        "SELECT constraint_name FROM user_constraints WHERE constraint_name = ? AND table_name = ?";
174                        @Language("SQL")
175                        String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
176                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
177                } else if (getDriverType() == DriverTypeEnum.MSSQL_2012) {
178                        // Legacy deletion for SQL Server unique indexes
179                        @Language("SQL")
180                        String findConstraintSql =
181                                        "SELECT tc.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc WHERE tc.CONSTRAINT_NAME = ? AND tc.TABLE_NAME = ?";
182                        @Language("SQL")
183                        String dropConstraintSql = "ALTER TABLE " + getTableName() + " DROP CONSTRAINT ?";
184                        findAndDropConstraint(findConstraintSql, dropConstraintSql);
185                }
186
187                Set<String> indexNames = JdbcUtils.getIndexNames(getConnectionProperties(), getTableName());
188
189                if (!indexNames.contains(myIndexName)) {
190                        logInfo(ourLog, "Index {} does not exist on table {} - No action needed", myIndexName, getTableName());
191                        return;
192                }
193
194                boolean isUnique = JdbcUtils.isIndexUnique(getConnectionProperties(), getTableName(), myIndexName);
195                String uniquenessString = isUnique ? "unique" : "non-unique";
196
197                List<String> sqls = generateSql();
198                if (!sqls.isEmpty()) {
199                        logInfo(ourLog, "Dropping {} index {} on table {}", uniquenessString, myIndexName, getTableName());
200                }
201                for (@Language("SQL") String sql : sqls) {
202                        executeSql(getTableName(), sql);
203                }
204        }
205
206        public void findAndDropConstraint(String theFindConstraintSql, String theDropConstraintSql) {
207                DataSource dataSource = Objects.requireNonNull(getConnectionProperties().getDataSource());
208                getConnectionProperties().getTxTemplate().executeWithoutResult(t -> {
209                        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
210                        RowMapperResultSetExtractor<String> resultSetExtractor =
211                                        new RowMapperResultSetExtractor<>(new SingleColumnRowMapper<>(String.class));
212                        List<String> outcome = jdbcTemplate.query(
213                                        theFindConstraintSql, new Object[] {myIndexName, getTableName()}, resultSetExtractor);
214                        assert outcome != null;
215                        for (String next : outcome) {
216                                String sql = theDropConstraintSql.replace("?", next);
217                                executeSql(getTableName(), sql);
218                        }
219                });
220        }
221
222        public DropIndexTask setIndexName(String theIndexName) {
223                myIndexName = theIndexName;
224                return this;
225        }
226
227        @Override
228        protected void generateEquals(EqualsBuilder theBuilder, BaseTask theOtherObject) {
229                DropIndexTask otherObject = (DropIndexTask) theOtherObject;
230                super.generateEquals(theBuilder, otherObject);
231                theBuilder.append(myIndexName, otherObject.myIndexName);
232                theBuilder.append(myOnline, otherObject.myOnline);
233        }
234
235        @Override
236        protected void generateHashCode(HashCodeBuilder theBuilder) {
237                super.generateHashCode(theBuilder);
238                theBuilder.append(myIndexName);
239                theBuilder.append(myOnline);
240        }
241
242        public void setOnline(boolean theFlag) {
243                this.myOnline = theFlag;
244        }
245}