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