001/*- 002 * #%L 003 * HAPI FHIR Server - SQL Migration 004 * %% 005 * Copyright (C) 2014 - 2024 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.i18n.Msg; 023import ca.uhn.fhir.jpa.migrate.JdbcUtils; 024import ca.uhn.fhir.jpa.migrate.tasks.api.TaskFlagEnum; 025import ca.uhn.fhir.rest.server.exceptions.InternalErrorException; 026import org.intellij.lang.annotations.Language; 027import org.slf4j.Logger; 028import org.slf4j.LoggerFactory; 029import org.springframework.jdbc.core.ColumnMapRowMapper; 030 031import java.sql.SQLException; 032import java.util.List; 033import java.util.Map; 034import java.util.Set; 035 036public class ModifyColumnTask extends BaseTableColumnTypeTask { 037 038 private static final Logger ourLog = LoggerFactory.getLogger(ModifyColumnTask.class); 039 040 public ModifyColumnTask(String theProductVersion, String theSchemaVersion) { 041 super(theProductVersion, theSchemaVersion); 042 } 043 044 @Override 045 public void validate() { 046 super.validate(); 047 setDescription("Modify column " + getColumnName() + " on table " + getTableName()); 048 } 049 050 @Override 051 public void doExecute() throws SQLException { 052 053 JdbcUtils.ColumnType existingType; 054 boolean nullable; 055 056 Set<String> columnNames = JdbcUtils.getColumnNames(getConnectionProperties(), getTableName()); 057 if (!columnNames.contains(getColumnName())) { 058 logInfo( 059 ourLog, 060 "Column {} doesn't exist on table {} - No action performed", 061 getColumnName(), 062 getTableName()); 063 return; 064 } 065 066 try { 067 existingType = JdbcUtils.getColumnType(getConnectionProperties(), getTableName(), getColumnName()); 068 nullable = isColumnNullable(getTableName(), getColumnName()); 069 } catch (SQLException e) { 070 throw new InternalErrorException(Msg.code(66) + e); 071 } 072 073 Long taskColumnLength = getColumnLength(); 074 boolean isShrinkOnly = false; 075 if (taskColumnLength != null) { 076 long existingLength = existingType.getLength() != null ? existingType.getLength() : 0; 077 if (existingLength > taskColumnLength) { 078 if (isNoColumnShrink()) { 079 taskColumnLength = existingLength; 080 } else { 081 if (existingType.getColumnTypeEnum() == getColumnType()) { 082 isShrinkOnly = true; 083 } 084 } 085 } 086 } 087 088 boolean alreadyOfCorrectType = existingType.equals(getColumnType(), taskColumnLength); 089 boolean alreadyCorrectNullable = isNullable() == nullable; 090 if (alreadyOfCorrectType && alreadyCorrectNullable) { 091 logInfo( 092 ourLog, 093 "Column {} on table {} is already of type {} and has nullable {} - No action performed", 094 getColumnName(), 095 getTableName(), 096 existingType, 097 nullable); 098 return; 099 } 100 101 String type = getSqlType(taskColumnLength); 102 String notNull = getSqlNotNull(); 103 104 String sql = null; 105 String sqlNotNull = null; 106 switch (getDriverType()) { 107 case DERBY_EMBEDDED: 108 if (!alreadyOfCorrectType) { 109 sql = "alter table " + getTableName() + " alter column " + getColumnName() + " set data type " 110 + type; 111 } 112 if (!alreadyCorrectNullable) { 113 sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + notNull; 114 } 115 break; 116 case MARIADB_10_1: 117 case MYSQL_5_7: 118 // Quote the column name as "SYSTEM" is a reserved word in MySQL 119 sql = "alter table " + getTableName() + " modify column `" + getColumnName() + "` " + type + notNull; 120 break; 121 case POSTGRES_9_4: 122 case COCKROACHDB_21_1: 123 if (!alreadyOfCorrectType) { 124 sql = "alter table " + getTableName() + " alter column " + getColumnName() + " type " + type; 125 } 126 if (!alreadyCorrectNullable) { 127 if (isNullable()) { 128 sqlNotNull = 129 "alter table " + getTableName() + " alter column " + getColumnName() + " drop not null"; 130 } else { 131 sqlNotNull = 132 "alter table " + getTableName() + " alter column " + getColumnName() + " set not null"; 133 } 134 } 135 break; 136 case ORACLE_12C: 137 String oracleNullableStmt = alreadyCorrectNullable ? "" : notNull; 138 String oracleTypeStmt = alreadyOfCorrectType ? "" : type; 139 sql = "alter table " + getTableName() + " modify ( " + getColumnName() + " " + oracleTypeStmt + " " 140 + oracleNullableStmt + " )"; 141 break; 142 case MSSQL_2012: 143 sql = "alter table " + getTableName() + " alter column " + getColumnName() + " " + type + notNull; 144 break; 145 case H2_EMBEDDED: 146 if (!alreadyOfCorrectType) { 147 sql = "alter table " + getTableName() + " alter column " + getColumnName() + " type " + type; 148 } 149 if (!alreadyCorrectNullable) { 150 if (isNullable()) { 151 sqlNotNull = 152 "alter table " + getTableName() + " alter column " + getColumnName() + " drop not null"; 153 } else { 154 sqlNotNull = 155 "alter table " + getTableName() + " alter column " + getColumnName() + " set not null"; 156 } 157 } 158 break; 159 default: 160 throw new IllegalStateException(Msg.code(67) + "Dont know how to handle " + getDriverType()); 161 } 162 163 if (isShrinkOnly) { 164 addFlag(TaskFlagEnum.FAILURE_ALLOWED); 165 } 166 167 logInfo(ourLog, "Updating column {} on table {} to type {}", getColumnName(), getTableName(), type); 168 if (sql != null) { 169 executeSql(getTableName(), sql); 170 } 171 172 if (sqlNotNull != null) { 173 logInfo(ourLog, "Updating column {} on table {} to not null", getColumnName(), getTableName()); 174 executeSql(getTableName(), sqlNotNull); 175 } 176 } 177 178 private boolean isColumnNullable(String tableName, String columnName) throws SQLException { 179 boolean result = JdbcUtils.isColumnNullable(getConnectionProperties(), tableName, columnName); 180 // Oracle sometimes stores the NULLABLE property in a Constraint, so override the result if this is an Oracle DB 181 switch (getDriverType()) { 182 case ORACLE_12C: 183 @Language("SQL") 184 String findNullableConstraintSql = 185 "SELECT acc.owner, acc.table_name, acc.column_name, search_condition_vc " 186 + "FROM all_cons_columns acc, user_constraints uc " 187 + "WHERE acc.constraint_name = uc.constraint_name " 188 + "AND acc.table_name = uc.table_name " 189 + "AND uc.constraint_type = ? " 190 + "AND acc.table_name = ? " 191 + "AND acc.column_name = ? " 192 + "AND search_condition_vc = ? "; 193 String[] params = new String[4]; 194 params[0] = "C"; 195 params[1] = tableName.toUpperCase(); 196 params[2] = columnName.toUpperCase(); 197 params[3] = "\"" + columnName.toUpperCase() + "\" IS NOT NULL"; 198 List<Map<String, Object>> queryResults = getConnectionProperties() 199 .getTxTemplate() 200 .execute(t -> getConnectionProperties() 201 .newJdbcTemplate() 202 .query(findNullableConstraintSql, params, new ColumnMapRowMapper())); 203 // If this query returns a row then the existence of that row indicates that a NOT NULL constraint 204 // exists 205 // on this Column and we must override whatever result was previously calculated and set it to false 206 if (queryResults != null 207 && queryResults.size() > 0 208 && queryResults.get(0) != null 209 && !queryResults.get(0).isEmpty()) { 210 result = false; 211 } 212 break; 213 default: 214 // Do nothing since we already initialized the variable above 215 break; 216 } 217 return result; 218 } 219}