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(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(Msg.code(66) + 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                        case COCKROACHDB_21_1:
111                                if (!alreadyOfCorrectType) {
112                                        sql = "alter table " + getTableName() + " alter column " + getColumnName() + " type " + type;
113                                }
114                                if (!alreadyCorrectNullable) {
115                                        if (isNullable()) {
116                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " drop not null";
117                                        } else {
118                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " set not null";
119                                        }
120                                }
121                                break;
122                        case ORACLE_12C:
123                                String oracleNullableStmt = !alreadyCorrectNullable ? notNull : "";
124                                sql = "alter table " + getTableName() + " modify ( " + getColumnName() + " " + type + oracleNullableStmt + " )";
125                                break;
126                        case MSSQL_2012:
127                                sql = "alter table " + getTableName() + " alter column " + getColumnName() + " " + type + notNull;
128                                break;
129                        case H2_EMBEDDED:
130                                if (!alreadyOfCorrectType) {
131                                        sql = "alter table " + getTableName() + " alter column " + getColumnName() + " type " + type;
132                                }
133                                if (!alreadyCorrectNullable) {
134                                        if (isNullable()) {
135                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " drop not null";
136                                        } else {
137                                                sqlNotNull = "alter table " + getTableName() + " alter column " + getColumnName() + " set not null";
138                                        }
139                                }
140                                break;
141                        default:
142                                throw new IllegalStateException(Msg.code(67) + "Dont know how to handle " + getDriverType());
143                }
144
145                if (!isFailureAllowed() && isShrinkOnly) {
146                        setFailureAllowed(true);
147                }
148
149                logInfo(ourLog, "Updating column {} on table {} to type {}", getColumnName(), getTableName(), type);
150                if (sql != null) {
151                        executeSql(getTableName(), sql);
152                }
153
154                if (sqlNotNull != null) {
155                        logInfo(ourLog, "Updating column {} on table {} to not null", getColumnName(), getTableName());
156                        executeSql(getTableName(), sqlNotNull);
157                }
158        }
159
160        private boolean isColumnNullable(String tableName, String columnName) throws SQLException {
161                boolean result = JdbcUtils.isColumnNullable(getConnectionProperties(), tableName, columnName);
162                // Oracle sometimes stores the NULLABLE property in a Constraint, so override the result if this is an Oracle DB
163                switch (getDriverType()) {
164                        case ORACLE_12C:
165                                @Language("SQL") String findNullableConstraintSql =
166                                        "SELECT acc.owner, acc.table_name, acc.column_name, search_condition_vc " +
167                                                "FROM all_cons_columns acc, user_constraints uc " +
168                                                "WHERE acc.constraint_name = uc.constraint_name " +
169                                                "AND acc.table_name = uc.table_name " +
170                                                "AND uc.constraint_type = ? " +
171                                                "AND acc.table_name = ? " +
172                                                "AND acc.column_name = ? " +
173                                                "AND search_condition_vc = ? ";
174                                String[] params = new String[4];
175                                params[0] = "C";
176                                params[1] = tableName.toUpperCase();
177                                params[2] = columnName.toUpperCase();
178                                params[3] = "\"" + columnName.toUpperCase() + "\" IS NOT NULL";
179                                List<Map<String, Object>> queryResults = getConnectionProperties().getTxTemplate().execute(t ->
180                                        getConnectionProperties().newJdbcTemplate().query(findNullableConstraintSql, params, new ColumnMapRowMapper()));
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}