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