001package com.identityworksllc.iiq.common.query;
002
003import sailpoint.tools.JdbcUtil;
004
005import java.sql.Connection;
006import java.sql.PreparedStatement;
007import java.sql.SQLException;
008import java.util.Arrays;
009import java.util.Collections;
010import java.util.HashSet;
011import java.util.Set;
012
013/**
014 * Utility class for database operations
015 */
016public class DatabaseUtil {
017    /**
018     * Represents the type of a database
019     */
020    public enum DatabaseType {
021        /**
022         * Oracle database
023         */
024        Oracle,
025
026        /**
027         * MySQL database
028         */
029        MySQL,
030
031        /**
032         * Microsoft SQL Server database
033         */
034        Microsoft,
035
036        /**
037         * PostgreSQL database
038         */
039        PostgreSQL
040    }
041    /**
042     * Set of reserved SQL words
043     */
044    public static final Set<String> RESERVED_SQL_WORDS = Collections.unmodifiableSet(new HashSet<>(Arrays.asList(
045            "ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC", "AUTO_INCREMENT", "BETWEEN", "BIGINT", "BINARY", "BIT",
046            "BLOB", "BOTH", "BY", "CASCADE", "CASE", "CHAR", "CHARACTER", "CHECK", "COLLATE", "COLUMN", "CONSTRAINT",
047            "CREATE", "CROSS", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "DATABASE",
048            "DEFAULT", "DELETE", "DESC", "DISTINCT", "DOUBLE", "DROP", "ELSE", "END", "ENUM", "ESCAPE", "EXISTS",
049            "FALSE", "FLOAT", "FOREIGN", "FROM", "FULLTEXT", "GROUP", "HAVING", "HEX", "HOUR", "IF", "IGNORE",
050            "IN", "INDEX", "INNER", "INSERT", "INT", "INTEGER", "INTERVAL", "INTO", "IS", "JOIN", "KEY", "LEFT",
051            "LIKE", "LIMIT", "LONGBLOB", "LONGTEXT", "MATCH", "MEDIUMBLOB", "MEDIUMINT", "MEDIUMTEXT", "MINUTE",
052            "MODIFY", "NOT", "NULL", "NUMBER", "NUMERIC", "ON", "OR", "ORDER", "OUTER", "PRIMARY", "REAL", "REFERENCES",
053            "REGEXP", "RENAME", "REPLACE", "RIGHT", "SELECT", "SET", "SHOW", "SMALLINT", "TABLE", "TEXT", "TIME",
054            "TIMESTAMP", "TINYBLOB", "TINYINT", "TINYTEXT", "TRUE", "UNIQUE", "UNSIGNED", "UPDATE", "USE", "USING",
055            "VALUES", "VARBINARY", "VARCHAR", "VARCHAR2", "VARCHARACTER", "VARYING", "WHEN", "WHERE", "YEAR", "ZEROFILL"
056    )));
057
058    /**
059     * Prevent instantiation
060     */
061    private DatabaseUtil() {
062        // Prevent instantiation
063    }
064
065    /**
066     * Determines the type of the given database
067     * @param connection The connection to the database
068     * @return The resulting type determination
069     * @throws SQLException if type determination fails, or if the DB is not a recognized type
070     */
071    public static DatabaseType getType(Connection connection) throws SQLException{
072        if (isOracle(connection)) {
073            return DatabaseType.Oracle;
074        } else if (isMysql(connection)) {
075            return DatabaseType.MySQL;
076        } else if (isMicrosoft(connection)) {
077            return DatabaseType.Microsoft;
078        } else if (isPostgres(connection)) {
079            return DatabaseType.PostgreSQL;
080        } else {
081            throw new SQLException("Unknown database type");
082        }
083    }
084
085    /**
086     * Determines if the given connection is to a Microsoft SQL Server database
087     * @param connection The connection to the database
088     * @return true if the database is Microsoft SQL Server, false otherwise
089     * @throws SQLException if the database type cannot be determined
090     */
091    public static boolean isMicrosoft(Connection connection) throws SQLException {
092        return connection.getMetaData().getDatabaseProductName().contains("Microsoft");
093    }
094
095    /**
096     * Determines if the given connection is to a MySQL database
097     * @param connection The connection to the database
098     * @return true if the database is MySQL, false otherwise
099     * @throws SQLException if the database type cannot be determined
100     */
101    public static boolean isMysql(Connection connection) throws SQLException {
102        return JdbcUtil.isMySQL(connection);
103    }
104
105    /**
106     * Determines if the given connection is to an Oracle database
107     * @param connection The connection to the database
108     * @return true if the database is Oracle, false otherwise
109     * @throws SQLException if the database type cannot be determined
110     */
111    public static boolean isOracle(Connection connection) throws SQLException {
112        return JdbcUtil.isOracle(connection);
113    }
114
115    /**
116     * Determines if the given connection is to a PostgreSQL database
117     * @param connection The connection to the database
118     * @return true if the database is PostgreSQL, false otherwise
119     * @throws SQLException if the database type cannot be determined
120     */
121    public static boolean isPostgres(Connection connection) throws SQLException {
122        return connection.getMetaData().getDatabaseProductName().contains("PostgreSQL");
123    }
124
125    /**
126     * Determines if the given name is a valid DB object name
127     * @param name The name to check
128     * @return true if the name is a valid object name, false otherwise
129     */
130    public static boolean isValidObjectName(String name) {
131        return name.length() < 30 && name.matches("[a-zA-Z_][a-zA-Z0-9_]*");
132    }
133
134    /**
135     * Returns true if the given table exists
136     * @param connection The connection to the database
137     * @param tableName The name of the table to check
138     * @return true if the table exists, false otherwise
139     */
140    public static boolean tableExists(Connection connection, String tableName) {
141        if (!isValidObjectName(tableName)) {
142            throw new IllegalArgumentException("Invalid table name: " + tableName);
143        }
144        try (PreparedStatement stmt = connection.prepareStatement("SELECT 1 FROM " + tableName + " WHERE 1 = 0")) {
145            stmt.execute();
146            return true;
147        } catch (SQLException e) {
148            return false;
149        }
150    }
151
152}