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.jpa.migrate.DriverTypeEnum;
023import ca.uhn.fhir.jpa.migrate.JdbcUtils;
024import org.apache.commons.lang3.Validate;
025import org.apache.commons.lang3.builder.EqualsBuilder;
026import org.apache.commons.lang3.builder.HashCodeBuilder;
027import org.slf4j.Logger;
028import org.slf4j.LoggerFactory;
029
030import java.sql.SQLException;
031import java.util.Arrays;
032import java.util.Collections;
033import java.util.List;
034import java.util.Locale;
035import java.util.Set;
036import javax.annotation.Nonnull;
037
038public class AddIndexTask extends BaseTableTask {
039
040        private static final Logger ourLog = LoggerFactory.getLogger(AddIndexTask.class);
041
042        private String myIndexName;
043        private List<String> myColumns;
044        private Boolean myUnique;
045        private List<String> myIncludeColumns = Collections.emptyList();
046        /** Should the operation avoid taking a lock on the table */
047        private boolean myOnline;
048
049        private MetadataSource myMetadataSource = new MetadataSource();
050
051        public AddIndexTask(String theProductVersion, String theSchemaVersion) {
052                super(theProductVersion, theSchemaVersion);
053        }
054
055        public void setIndexName(String theIndexName) {
056                myIndexName = theIndexName.toUpperCase(Locale.US);
057        }
058
059        public void setColumns(List<String> theColumns) {
060                myColumns = theColumns;
061        }
062
063        public void setUnique(boolean theUnique) {
064                myUnique = theUnique;
065        }
066
067        @Override
068        public void validate() {
069                super.validate();
070                Validate.notBlank(myIndexName, "Index name not specified");
071                Validate.isTrue(
072                                myColumns.size() > 0,
073                                "Columns not specified for AddIndexTask " + myIndexName + " on table " + getTableName());
074                Validate.notNull(myUnique, "Uniqueness not specified");
075                setDescription("Add " + myIndexName + " index to table " + getTableName());
076        }
077
078        @Override
079        public void doExecute() throws SQLException {
080                Set<String> indexNames = JdbcUtils.getIndexNames(getConnectionProperties(), getTableName());
081                if (indexNames.contains(myIndexName)) {
082                        logInfo(ourLog, "Index {} already exists on table {} - No action performed", myIndexName, getTableName());
083                        return;
084                }
085
086                logInfo(
087                                ourLog,
088                                "Going to add a {} index named {} on table {} for columns {}",
089                                (myUnique ? "UNIQUE" : "NON-UNIQUE"),
090                                myIndexName,
091                                getTableName(),
092                                myColumns);
093
094                String sql = generateSql();
095                String tableName = getTableName();
096
097                try {
098                        executeSql(tableName, sql);
099                } catch (Exception e) {
100                        if (e.toString().contains("already exists")) {
101                                ourLog.warn("Index {} already exists", myIndexName);
102                        } else {
103                                throw e;
104                        }
105                }
106        }
107
108        @Nonnull
109        String generateSql() {
110                String unique = myUnique ? "unique " : "";
111                String columns = String.join(", ", myColumns);
112                String includeClause = "";
113                String mssqlWhereClause = "";
114                if (!myIncludeColumns.isEmpty()) {
115                        switch (getDriverType()) {
116                                case POSTGRES_9_4:
117                                case MSSQL_2012:
118                                case COCKROACHDB_21_1:
119                                        includeClause = " INCLUDE (" + String.join(", ", myIncludeColumns) + ")";
120                                        break;
121                                case H2_EMBEDDED:
122                                case DERBY_EMBEDDED:
123                                case MARIADB_10_1:
124                                case MYSQL_5_7:
125                                case ORACLE_12C:
126                                        // These platforms don't support the include clause
127                                        // Per:
128                                        // https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes#postgresql-limitations
129                                        break;
130                        }
131                }
132                if (myUnique && getDriverType() == DriverTypeEnum.MSSQL_2012) {
133                        mssqlWhereClause = buildMSSqlNotNullWhereClause();
134                }
135                // Should we do this non-transactionally?  Avoids a write-lock, but introduces weird failure modes.
136                String postgresOnlineClause = "";
137                String msSqlOracleOnlineClause = "";
138                if (myOnline) {
139                        switch (getDriverType()) {
140                                case POSTGRES_9_4:
141                                case COCKROACHDB_21_1:
142                                        postgresOnlineClause = "CONCURRENTLY ";
143                                        // This runs without a lock, and can't be done transactionally.
144                                        setTransactional(false);
145                                        break;
146                                case ORACLE_12C:
147                                        if (myMetadataSource.isOnlineIndexSupported(getConnectionProperties())) {
148                                                msSqlOracleOnlineClause = " ONLINE DEFERRED INVALIDATION";
149                                        }
150                                        break;
151                                case MSSQL_2012:
152                                        if (myMetadataSource.isOnlineIndexSupported(getConnectionProperties())) {
153                                                msSqlOracleOnlineClause = " WITH (ONLINE = ON)";
154                                        }
155                                        break;
156                                default:
157                        }
158                }
159
160                String sql = "create " + unique + "index " + postgresOnlineClause + myIndexName + " on " + getTableName() + "("
161                                + columns + ")" + includeClause + mssqlWhereClause + msSqlOracleOnlineClause;
162                return sql;
163        }
164
165        @Nonnull
166        private String buildMSSqlNotNullWhereClause() {
167                String mssqlWhereClause;
168                mssqlWhereClause = " WHERE (";
169                for (int i = 0; i < myColumns.size(); i++) {
170                        mssqlWhereClause += myColumns.get(i) + " IS NOT NULL ";
171                        if (i < myColumns.size() - 1) {
172                                mssqlWhereClause += "AND ";
173                        }
174                }
175                mssqlWhereClause += ")";
176                return mssqlWhereClause;
177        }
178
179        public void setColumns(String... theColumns) {
180                setColumns(Arrays.asList(theColumns));
181        }
182
183        public void setIncludeColumns(String... theIncludeColumns) {
184                setIncludeColumns(Arrays.asList(theIncludeColumns));
185        }
186
187        private void setIncludeColumns(List<String> theIncludeColumns) {
188                Validate.notNull(theIncludeColumns);
189                myIncludeColumns = theIncludeColumns;
190        }
191
192        /**
193         * Add Index without locking the table.
194         */
195        public void setOnline(boolean theFlag) {
196                myOnline = theFlag;
197        }
198
199        @Override
200        protected void generateEquals(EqualsBuilder theBuilder, BaseTask theOtherObject) {
201                super.generateEquals(theBuilder, theOtherObject);
202
203                AddIndexTask otherObject = (AddIndexTask) theOtherObject;
204                theBuilder.append(myIndexName, otherObject.myIndexName);
205                theBuilder.append(myColumns, otherObject.myColumns);
206                theBuilder.append(myUnique, otherObject.myUnique);
207                theBuilder.append(myIncludeColumns, otherObject.myIncludeColumns);
208                theBuilder.append(myOnline, otherObject.myOnline);
209        }
210
211        @Override
212        protected void generateHashCode(HashCodeBuilder theBuilder) {
213                super.generateHashCode(theBuilder);
214                theBuilder.append(myIndexName);
215                theBuilder.append(myColumns);
216                theBuilder.append(myUnique);
217                theBuilder.append(myOnline);
218        }
219
220        public void setMetadataSource(MetadataSource theMetadataSource) {
221                myMetadataSource = theMetadataSource;
222        }
223}