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