001/*-
002 * #%L
003 * HAPI FHIR Server - SQL Migration
004 * %%
005 * Copyright (C) 2014 - 2025 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.util;
021
022import jakarta.annotation.Nonnull;
023import org.apache.commons.lang3.StringUtils;
024import org.apache.commons.lang3.Validate;
025
026import java.util.Arrays;
027import java.util.List;
028import java.util.Locale;
029import java.util.Optional;
030import java.util.regex.Matcher;
031import java.util.regex.Pattern;
032import java.util.stream.Collectors;
033
034public class SqlUtil {
035
036        /**
037         * This regex parses out the PK of a Postgres CREATE TABLE statement. To work on it,
038         * import it into <a href="https://regex101.com">https://regex101.com</a>. The
039         * raw Regex is:
040         * <pre>
041         *     create table ([a-zA-Z0-9_]+).*(\s|[a-zA-Z0-9,()_])+?primary key\s+\(([a-zA-Z_, ]+)\).*
042         * </pre>
043         * A sample testing value is:
044         * <pre>
045         *     create table HFJ_IDX_CMB_TOK_NU (
046         *                          PID bigint not null,
047         *                          PARTITION_ID integer not null,
048         *                          PARTITION_DATE date,
049         *                          HASH_COMPLETE bigint not null,
050         *                          IDX_STRING varchar(500) not null,
051         *                          RES_ID bigint,
052         *                          primary key (PID, PARTITION_ID)
053         *                      );
054         * </pre>
055         */
056        private static final Pattern CREATE_TABLE = Pattern.compile(
057                        "create table ([a-zA-Z0-9_]+).*(\\s|[a-zA-Z0-9,()_])+?primary key\\s+\\(([a-zA-Z_, ]+)\\).*",
058                        Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL);
059
060        /**
061         * This regex parses out the PK of a Postgres ALTER TABLE..ADD CONSTRAINT statement.
062         * To work on it, import it into <a href="https://regex101.com">https://regex101.com</a>.
063         * The raw Regex is:
064         * <pre>
065         *    alter table\s+(if exists)?\s+(\w+)\s+add constraint\s+(\w+)\s+foreign key \(([a-zA-Z_, ]+)\)\s+references (\w+).*
066         * </pre>
067         * A sample testing value is:
068         * <pre>
069         *    alter table if exists MPI_LINK
070         *               add constraint FK_EMPI_LINK_GOLDEN_RESOURCE
071         *               foreign key (GOLDEN_RESOURCE_PID, GOLDEN_RESOURCE_PARTITION_ID)
072         *               references HFJ_RESOURCE;
073         * </pre>
074         */
075        private static final Pattern ALTER_TABLE_ADD_CONSTRAINT_FOREIGN_KEY = Pattern.compile(
076                        "alter table\\s+(if exists)?\\s+(\\w+)\\s+add constraint\\s+(\\w+)\\s+foreign key \\(([a-zA-Z_, ]+)\\)\\s+references (\\w+).*",
077                        Pattern.CASE_INSENSITIVE | Pattern.MULTILINE | Pattern.DOTALL);
078
079        /**
080         * Non instantiable
081         */
082        private SqlUtil() {
083                // nothing
084        }
085
086        @Nonnull
087        public static List<String> splitSqlFileIntoStatements(String theSql) {
088                String sqlWithoutComments = Arrays.stream(theSql.split("\n"))
089                                .filter(t -> !t.trim().startsWith("--"))
090                                .collect(Collectors.joining("\n"));
091
092                return Arrays.stream(sqlWithoutComments.split(";"))
093                                .filter(StringUtils::isNotBlank)
094                                .map(StringUtils::trim)
095                                .collect(Collectors.toList());
096        }
097
098        /**
099         * Accepts a SQL statement and parses it as a SQL <code>CREATE TABLE</code>
100         * statement, returning details about the table name and primary key.
101         * <b>This method has only been tested for Postgresql DDL format!</b>
102         *
103         * @param theStatement A single SQL statement
104         * @return Returns details about the table name and PK columns if the SQL statement
105         *      contains a valid CREATE TABLE statement, returns {@literal null}
106         *      otherwise.
107         */
108        @Nonnull
109        public static Optional<CreateTablePrimaryKey> parseCreateTableStatementPrimaryKey(String theStatement) {
110                Matcher matcher = CREATE_TABLE.matcher(theStatement);
111                if (matcher.find()) {
112                        String tableName = matcher.group(1).toUpperCase(Locale.US);
113                        String primaryKeyColumnsString = matcher.group(3);
114                        List<String> primaryKeyColumns = splitCommaSeparatedList(primaryKeyColumnsString);
115                        return Optional.of(new CreateTablePrimaryKey(tableName, primaryKeyColumns));
116                }
117                return Optional.empty();
118        }
119
120        /**
121         * Accepts a DDL statement containing
122         * <code>ALTER TABLE [IF EXISTS]? table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_list)</code>
123         * and returns the parsed details.
124         */
125        @Nonnull
126        public static Optional<AlterTableAddConstraint> parseAlterTableAddConstraintConstraintForeignKey(
127                        String theStatement) {
128                Matcher matcher = ALTER_TABLE_ADD_CONSTRAINT_FOREIGN_KEY.matcher(theStatement);
129                if (matcher.find()) {
130                        String tableName = matcher.group(2);
131                        String constraintName = matcher.group(3);
132                        String columnsString = matcher.group(4);
133                        String references = matcher.group(5);
134                        List<String> columns = splitCommaSeparatedList(columnsString);
135
136                        return Optional.of(new AlterTableAddConstraint(tableName, constraintName, columns, references));
137                }
138                return Optional.empty();
139        }
140
141        @Nonnull
142        private static List<String> splitCommaSeparatedList(String primaryKeyColumnsString) {
143                return Arrays.asList(StringUtils.split(primaryKeyColumnsString, ", "));
144        }
145
146        public static class CreateTablePrimaryKey {
147                private final String myTableName;
148                private final List<String> myPrimaryKeyColumns;
149
150                public CreateTablePrimaryKey(String theTableName, List<String> thePrimaryKeyColumns) {
151                        myTableName = theTableName;
152                        myPrimaryKeyColumns = thePrimaryKeyColumns;
153                }
154
155                public List<String> getPrimaryKeyColumns() {
156                        return myPrimaryKeyColumns;
157                }
158
159                public String getTableName() {
160                        return myTableName;
161                }
162        }
163
164        public static class AlterTableAddConstraint {
165                private final String myConstraintName;
166                private final List<String> myColumns;
167                private final String myTableName;
168                private final String myReferences;
169
170                public AlterTableAddConstraint(
171                                String theTableName, String theConstraintName, List<String> theColumns, String theReferences) {
172                        Validate.isTrue(theTableName.matches("^[a-zA-Z0-9_]+$"), "Invalid table name '%s'", theTableName);
173                        Validate.isTrue(
174                                        theConstraintName.matches("^[a-zA-Z0-9_]+$"), "Invalid constraint name '%s'", theConstraintName);
175                        Validate.isTrue(theReferences.matches("^[a-zA-Z0-9_]+$"), "Invalid reference '%s'", theReferences);
176                        Validate.isTrue(!theColumns.isEmpty(), "Invalid columns '%s'", theColumns);
177                        Validate.isTrue(
178                                        theColumns.stream()
179                                                                        .map(t -> t.matches("^[a-zA-Z0-9_]+$"))
180                                                                        .filter(t -> t)
181                                                                        .count()
182                                                        == theColumns.size(),
183                                        "Invalid columns '%s'",
184                                        theColumns);
185                        myTableName = theTableName;
186                        myConstraintName = theConstraintName;
187                        myColumns = theColumns;
188                        myReferences = theReferences;
189                }
190
191                public String getReferences() {
192                        return myReferences;
193                }
194
195                public List<String> getColumns() {
196                        return myColumns;
197                }
198
199                public String getTableName() {
200                        return myTableName;
201                }
202
203                public String getConstraintName() {
204                        return myConstraintName;
205                }
206        }
207}