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