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