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