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