001package ca.uhn.fhir.jpa.migrate.taskdef;
002
003/*-
004 * #%L
005 * HAPI FHIR Server - SQL Migration
006 * %%
007 * Copyright (C) 2014 - 2022 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.i18n.Msg;
024import ca.uhn.fhir.jpa.migrate.JdbcUtils;
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(ourLog, "Column {} doesn't exist on table {} - No action performed", getColumnName(), getTableName());
059                        return;
060                }
061
062                try {
063                        existingType = JdbcUtils.getColumnType(getConnectionProperties(), getTableName(), getColumnName());
064                        nullable = isColumnNullable(getTableName(), getColumnName());
065                } catch (SQLException e) {
066                        throw new InternalErrorException(Msg.code(66) + e);
067                }
068
069                Long taskColumnLength = getColumnLength();
070                boolean isShrinkOnly = false;
071                if (taskColumnLength != null) {
072                        long existingLength = existingType.getLength() != null ? existingType.getLength() : 0;
073                        if (existingLength > taskColumnLength) {
074                                if (isNoColumnShrink()) {
075                                        taskColumnLength = existingLength;
076                                } else {
077                                        if (existingType.getColumnTypeEnum() == getColumnType()) {
078                                                isShrinkOnly = true;
079                                        }
080                                }
081                        }
082                }
083
084                boolean alreadyOfCorrectType = existingType.equals(getColumnType(), taskColumnLength);
085                boolean alreadyCorrectNullable = isNullable() == nullable;
086                if (alreadyOfCorrectType && alreadyCorrectNullable) {
087                        logInfo(ourLog, "Column {} on table {} is already of type {} and has nullable {} - No action performed", getColumnName(), getTableName(), existingType, nullable);
088                        return;
089                }
090
091                String type = getSqlType(taskColumnLength);
092                String notNull = getSqlNotNull();
093
094                String sql = null;
095                String sqlNotNull = null;
096                switch (getDriverType()) {
097                        case DERBY_EMBEDDED:
098                                if (!alreadyOfCorrectType) {
099                                        sql = "alter table " + getTableName() + " alter column " + getColumnName() + " set data type " + type;
100                                }
101                                if (!alreadyCorrectNullable) {
102                                        sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + notNull;
103                                }
104                                break;
105                        case MARIADB_10_1:
106                        case MYSQL_5_7:
107                                // Quote the column name as "SYSTEM" is a reserved word in MySQL
108                                sql = "alter table " + getTableName() + " modify column `" + getColumnName() + "` " + type + notNull;
109                                break;
110                        case POSTGRES_9_4:
111                        case COCKROACHDB_21_1:
112                                if (!alreadyOfCorrectType) {
113                                        sql = "alter table " + getTableName() + " alter column " + getColumnName() + " type " + type;
114                                }
115                                if (!alreadyCorrectNullable) {
116                                        if (isNullable()) {
117                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " drop not null";
118                                        } else {
119                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " set not null";
120                                        }
121                                }
122                                break;
123                        case ORACLE_12C:
124                                String oracleNullableStmt = !alreadyCorrectNullable ? notNull : "";
125                                sql = "alter table " + getTableName() + " modify ( " + getColumnName() + " " + type + oracleNullableStmt + " )";
126                                break;
127                        case MSSQL_2012:
128                                sql = "alter table " + getTableName() + " alter column " + getColumnName() + " " + type + notNull;
129                                break;
130                        case H2_EMBEDDED:
131                                if (!alreadyOfCorrectType) {
132                                        sql = "alter table " + getTableName() + " alter column " + getColumnName() + " type " + type;
133                                }
134                                if (!alreadyCorrectNullable) {
135                                        if (isNullable()) {
136                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " drop not null";
137                                        } else {
138                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " set not null";
139                                        }
140                                }
141                                break;
142                        default:
143                                throw new IllegalStateException(Msg.code(67) + "Dont know how to handle " + getDriverType());
144                }
145
146                if (!isFailureAllowed() && isShrinkOnly) {
147                        setFailureAllowed(true);
148                }
149
150                logInfo(ourLog, "Updating column {} on table {} to type {}", getColumnName(), getTableName(), type);
151                if (sql != null) {
152                        executeSql(getTableName(), sql);
153                }
154
155                if (sqlNotNull != null) {
156                        logInfo(ourLog, "Updating column {} on table {} to not null", getColumnName(), getTableName());
157                        executeSql(getTableName(), sqlNotNull);
158                }
159        }
160
161        private boolean isColumnNullable(String tableName, String columnName) throws SQLException {
162                boolean result = JdbcUtils.isColumnNullable(getConnectionProperties(), tableName, columnName);
163                // Oracle sometimes stores the NULLABLE property in a Constraint, so override the result if this is an Oracle DB
164                switch (getDriverType()) {
165                        case ORACLE_12C:
166                                @Language("SQL") String findNullableConstraintSql =
167                                        "SELECT acc.owner, acc.table_name, acc.column_name, search_condition_vc " +
168                                                "FROM all_cons_columns acc, all_constraints ac " +
169                                                "WHERE acc.constraint_name = ac.constraint_name " +
170                                                "AND acc.table_name = ac.table_name " +
171                                                "AND ac.constraint_type = ? " +
172                                                "AND acc.table_name = ? " +
173                                                "AND acc.column_name = ? " +
174                                                "AND search_condition_vc = ? ";
175                                String[] params = new String[4];
176                                params[0] = "C";
177                                params[1] = tableName.toUpperCase();
178                                params[2] = columnName.toUpperCase();
179                                params[3] = "\"" + columnName.toUpperCase() + "\" IS NOT NULL";
180                                List<Map<String, Object>> queryResults = getConnectionProperties().getTxTemplate().execute(t -> {
181                                        return getConnectionProperties().newJdbcTemplate().query(findNullableConstraintSql, params, new ColumnMapRowMapper());
182                                });
183                                // If this query returns a row then the existence of that row indicates that a NOT NULL constraint exists
184                                // on this Column and we must override whatever result was previously calculated and set it to false
185                                if (queryResults != null && queryResults.size() > 0 && queryResults.get(0) != null && !queryResults.get(0).isEmpty()) {
186                                        result = false;
187                                }
188                                break;
189                        default:
190                                // Do nothing since we already initialized the variable above
191                                break;
192                }
193                return result;
194        }
195}