Examples with RawSqlStatement liquibase.statement.core.RawSqlStatement used on opensource projects

Example 11 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class SequenceSnapshotGenerator method addTo.

@Override
protected void addTo(DatabaseObject foundObject, DatabaseSnapshot snapshot) throws DatabaseException, InvalidExampleException {
    if (!(foundObject instanceof Schema) || !snapshot.getDatabase().supportsSequences()) {
        return;
    }
    Schema schema = (Schema) foundObject;
    Database database = snapshot.getDatabase();
    // noinspection unchecked
    List<Map<String, ?>> sequences = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement(getSelectSequenceSql(schema, database)));
    if (sequences != null) {
        for (Map<String, ?> sequence : sequences) {
            schema.addDatabaseObject(mapToSequence(sequence, (Schema) foundObject, database));
        }
    }
}

Example 12 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class CreateProcedureGenerator method surroundWithSchemaSets.

/**
 * Convenience method for when the schemaName is set but we don't want to parse the body
 */
public static void surroundWithSchemaSets(List<Sql> sql, String schemaName, Database database) {
    if ((StringUtil.trimToNull(schemaName) != null) && !ChangeLogParserConfiguration.USE_PROCEDURE_SCHEMA.getCurrentValue()) {
        String defaultSchema = database.getDefaultSchemaName();
        if (database instanceof OracleDatabase) {
            sql.add(0, new UnparsedSql("ALTER SESSION SET CURRENT_SCHEMA=" + database.escapeObjectName(schemaName, Schema.class)));
            sql.add(new UnparsedSql("ALTER SESSION SET CURRENT_SCHEMA=" + database.escapeObjectName(defaultSchema, Schema.class)));
        } else if (database instanceof AbstractDb2Database) {
            sql.add(0, new UnparsedSql("SET CURRENT SCHEMA " + schemaName));
            sql.add(new UnparsedSql("SET CURRENT SCHEMA " + defaultSchema));
        } else if (database instanceof PostgresDatabase) {
            final Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database);
            String originalSearchPath = null;
            if (executor instanceof JdbcExecutor) {
                try {
                    originalSearchPath = executor.queryForObject(new RawSqlStatement("SHOW SEARCH_PATH"), String.class);
                } catch (Throwable e) {
                    Scope.getCurrentScope().getLog(CreateProcedureGenerator.class).warning("Cannot get search_path", e);
                }
            }
            if (originalSearchPath == null) {
                originalSearchPath = defaultSchema;
            }
            if (!originalSearchPath.equals(schemaName) && !originalSearchPath.startsWith(schemaName + ",") && !originalSearchPath.startsWith("\"" + schemaName + "\",")) {
                if (database instanceof EnterpriseDBDatabase) {
                    sql.add(0, new UnparsedSql("ALTER SESSION SET SEARCH_PATH TO " + database.escapeObjectName(defaultSchema, Schema.class) + ", " + originalSearchPath));
                    sql.add(new UnparsedSql("ALTER SESSION SET CURRENT SCHEMA " + originalSearchPath));
                } else {
                    sql.add(0, new UnparsedSql("SET SEARCH_PATH TO " + database.escapeObjectName(schemaName, Schema.class) + ", " + originalSearchPath));
                    sql.add(new UnparsedSql("SET CURRENT SCHEMA " + originalSearchPath));
                }
            }
        }
    }
}

Example 13 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class SnapshotGeneratorFactory method has.

/**
 * Checks if a specific object is present in a database
 * @param example The DatabaseObject to check for existence
 * @param database The DBMS in which the object might exist
 * @return true if object existence can be confirmed, false otherweise
 * @throws DatabaseException If a problem occurs in the DBMS-specific code
 * @throws InvalidExampleException If the object cannot be checked properly, e.g. if the object name is ambiguous
 */
public boolean has(DatabaseObject example, Database database) throws DatabaseException, InvalidExampleException {
    // @todo I have seen duplicates in types - maybe convert the List into a Set? Need to understand it more thoroughly.
    List<Class<? extends DatabaseObject>> types = new ArrayList<>(getContainerTypes(example.getClass(), database));
    types.add(example.getClass());
    // @todo Actually, there may be extreme cases (distorted table statistics etc.) where a COUNT(*) might not be so cheap. Maybe SELECT a dummy constant is the better way?
    if ((example instanceof Table) && (example.getName().equals(database.getDatabaseChangeLogTableName()) || example.getName().equals(database.getDatabaseChangeLogLockTableName()))) {
        try {
            Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForInt(new RawSqlStatement("SELECT COUNT(*) FROM " + database.escapeObjectName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), example.getName(), Table.class)));
            return true;
        } catch (DatabaseException e) {
            if (database instanceof PostgresDatabase) {
                // throws "current transaction is aborted" unless we roll back the connection
                database.rollback();
            }
            return false;
        }
    }
    /*
          * If the query is about another object, try to create a snapshot of the of the object (or used the cached
          * snapshot. If that works, we count that as confirmation of existence.
          */
    SnapshotControl snapshotControl = (new SnapshotControl(database, false, types.toArray(new Class[types.size()])));
    snapshotControl.setWarnIfObjectNotFound(false);
    if (createSnapshot(example, database, snapshotControl) != null) {
        return true;
    }
    CatalogAndSchema catalogAndSchema;
    if (example.getSchema() == null) {
        catalogAndSchema = database.getDefaultSchema();
    } else {
        catalogAndSchema = example.getSchema().toCatalogAndSchema();
    }
    DatabaseSnapshot snapshot = createSnapshot(catalogAndSchema, database, new SnapshotControl(database, false, example.getClass()).setWarnIfObjectNotFound(false));
    for (DatabaseObject obj : snapshot.get(example.getClass())) {
        if (DatabaseObjectComparatorFactory.getInstance().isSameObject(example, obj, null, database)) {
            return true;
        }
    }
    return false;
}

Example 14 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class UniqueConstraintSnapshotGenerator method listColumns.

protected List<Map<String, ?>> listColumns(UniqueConstraint example, Database database, DatabaseSnapshot snapshot) throws DatabaseException {
    Relation table = example.getRelation();
    Schema schema = table.getSchema();
    String name = example.getName();
    boolean bulkQuery;
    String sql;
    String cacheKey = "uniqueConstraints-" + example.getClass().getSimpleName() + "-" + example.getSchema().toCatalogAndSchema().customize(database).toString();
    String queryCountKey = "uniqueConstraints-" + example.getClass().getSimpleName() + "-queryCount";
    Map<String, List<Map<String, ?>>> columnCache = (Map<String, List<Map<String, ?>>>) snapshot.getScratchData(cacheKey);
    Integer columnQueryCount = (Integer) snapshot.getScratchData(queryCountKey);
    if (columnQueryCount == null) {
        columnQueryCount = 0;
    }
    if (columnCache == null) {
        bulkQuery = false;
        if (columnQueryCount > 3) {
            bulkQuery = supportsBulkQuery(database);
        }
        snapshot.setScratchData(queryCountKey, columnQueryCount + 1);
        if ((database instanceof MySQLDatabase) || (database instanceof HsqlDatabase)) {
            sql = "select const.CONSTRAINT_NAME, const.TABLE_NAME, COLUMN_NAME, const.constraint_schema as CONSTRAINT_CONTAINER " + "from " + database.getSystemSchema() + ".table_constraints const " + "join " + database.getSystemSchema() + ".key_column_usage col " + "on const.constraint_schema=col.constraint_schema " + "and const.table_name=col.table_name " + "and const.constraint_name=col.constraint_name " + "where const.constraint_schema='" + database.correctObjectName(schema.getCatalogName(), Catalog.class) + "' ";
            if (!bulkQuery) {
                sql += "and const.table_name='" + database.correctObjectName(example.getRelation().getName(), Table.class) + "' " + "and const.constraint_name='" + database.correctObjectName(name, UniqueConstraint.class) + "'";
            }
            sql += "order by ordinal_position";
        } else if (database instanceof PostgresDatabase) {
            sql = "select const.CONSTRAINT_NAME, COLUMN_NAME, const.constraint_schema as CONSTRAINT_CONTAINER " + "from " + database.getSystemSchema() + ".table_constraints const " + "join " + database.getSystemSchema() + ".key_column_usage col " + "on const.constraint_schema=col.constraint_schema " + "and const.table_name=col.table_name " + "and const.constraint_name=col.constraint_name " + "where const.constraint_catalog='" + database.correctObjectName(schema.getCatalogName(), Catalog.class) + "' ";
            if (database instanceof CockroachDatabase) {
                sql += " and (select count(*) from (select indexdef from pg_indexes where schemaname='" + database.correctObjectName(schema.getSchema().getName(), Schema.class) + "' AND indexname='" + database.correctObjectName(name, UniqueConstraint.class) + "' AND (position('DESC,' in indexdef) > 0 OR position('DESC)' in indexdef) > 0))) = 0" + "and const.constraint_name != 'primary' ";
            }
            sql += "and const.constraint_schema='" + database.correctObjectName(schema.getSchema().getName(), Schema.class) + "' ";
            if (!bulkQuery) {
                sql += "and const.table_name='" + database.correctObjectName(example.getRelation().getName(), Table.class) + "' " + "and const.constraint_name='" + database.correctObjectName(name, UniqueConstraint.class) + "'";
            }
            sql += "order by ordinal_position";
        } else if (database.getClass().getName().contains("MaxDB")) {
            // have to check classname as this is currently an extension
            sql = "select CONSTRAINTNAME as constraint_name, COLUMNNAME as column_name from CONSTRAINTCOLUMNS WHERE CONSTRAINTTYPE = 'UNIQUE_CONST' AND tablename = '" + database.correctObjectName(example.getRelation().getName(), Table.class) + "' AND constraintname = '" + database.correctObjectName(name, UniqueConstraint.class) + "'";
        } else if (database instanceof MSSQLDatabase) {
            sql = "SELECT " + "[kc].[name] AS [CONSTRAINT_NAME], " + "s.name AS constraint_container, " + "[c].[name] AS [COLUMN_NAME], " + "CASE [ic].[is_descending_key] WHEN 0 THEN N'A' WHEN 1 THEN N'D' END AS [ASC_OR_DESC] " + "FROM [sys].[schemas] AS [s] " + "INNER JOIN [sys].[tables] AS [t] " + "ON [t].[schema_id] = [s].[schema_id] " + "INNER JOIN [sys].[key_constraints] AS [kc] " + "ON [kc].[parent_object_id] = [t].[object_id] " + "INNER JOIN [sys].[indexes] AS [i] " + "ON [i].[object_id] = [kc].[parent_object_id] " + "AND [i].[index_id] = [kc].[unique_index_id] " + "INNER JOIN [sys].[index_columns] AS [ic] " + "ON [ic].[object_id] = [i].[object_id] " + "AND [ic].[index_id] = [i].[index_id] " + "INNER JOIN [sys].[columns] AS [c] " + "ON [c].[object_id] = [ic].[object_id] " + "AND [c].[column_id] = [ic].[column_id] " + "WHERE [s].[name] = N'" + database.escapeStringForDatabase(database.correctObjectName(schema.getName(), Schema.class)) + "' ";
            if (!bulkQuery) {
                sql += "AND [t].[name] = N'" + database.escapeStringForDatabase(database.correctObjectName(example.getRelation().getName(), Table.class)) + "' " + "AND [kc].[name] = N'" + database.escapeStringForDatabase(database.correctObjectName(name, UniqueConstraint.class)) + "' ";
            }
            sql += "ORDER BY " + "[ic].[key_ordinal]";
        } else if (database instanceof OracleDatabase) {
            sql = "select ucc.owner as constraint_container, ucc.constraint_name as constraint_name, ucc.column_name, f.validated as constraint_validate, ucc.table_name " + "from all_cons_columns ucc " + "INNER JOIN all_constraints f " + "ON ucc.owner = f.owner " + "AND ucc.constraint_name = f.constraint_name " + "where " + (bulkQuery ? "" : "ucc.constraint_name='" + database.correctObjectName(name, UniqueConstraint.class) + "' and ") + "ucc.owner='" + database.correctObjectName(schema.getCatalogName(), Catalog.class) + "' " + "and ucc.table_name not like 'BIN$%' " + "order by ucc.position";
        } else if (database instanceof DB2Database) {
            if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
                sql = "select T1.constraint_name as CONSTRAINT_NAME, T2.COLUMN_NAME as COLUMN_NAME, T1.CONSTRAINT_SCHEMA as CONSTRAINT_CONTAINER from QSYS2.TABLE_CONSTRAINTS T1, QSYS2.SYSCSTCOL T2\n" + "where T1.CONSTRAINT_TYPE='UNIQUE' and T1.CONSTRAINT_NAME=T2.CONSTRAINT_NAME\n" + "and T1.CONSTRAINT_SCHEMA='" + database.correctObjectName(schema.getName(), Schema.class) + "'\n" + "and T2.CONSTRAINT_SCHEMA='" + database.correctObjectName(schema.getName(), Schema.class) + "'\n" + // + "\n"
                "order by T2.COLUMN_NAME\n";
            } else {
                sql = "select k.constname as constraint_name, k.colname as column_name from syscat.keycoluse k, syscat.tabconst t " + "where k.constname = t.constname " + "and k.tabschema = t.tabschema " + "and t.type='U' " + (bulkQuery ? "" : "and k.constname='" + database.correctObjectName(name, UniqueConstraint.class) + "' ") + "and t.tabschema = '" + database.correctObjectName(schema.getName(), Schema.class) + "' " + "order by colseq";
            }
        } else if (database instanceof Db2zDatabase) {
            sql = "select k.colname as column_name from SYSIBM.SYSKEYCOLUSE k, SYSIBM.SYSTABCONST t " + "where k.constname = t.constname " + "and k.TBCREATOR = t.TBCREATOR " + "and t.type = 'U'" + "and k.constname='" + database.correctObjectName(name, UniqueConstraint.class) + "' " + "and t.TBCREATOR = '" + database.correctObjectName(schema.getName(), Schema.class) + "' " + "order by colseq";
        } else if (database instanceof DerbyDatabase) {
            // does not support bulkQuery,  supportsBulkQuery should return false()
            sql = "SELECT cg.descriptor as descriptor, t.tablename " + "FROM sys.sysconglomerates cg " + "JOIN sys.syskeys k ON cg.conglomerateid = k.conglomerateid " + "JOIN sys.sysconstraints c ON c.constraintid = k.constraintid " + "JOIN sys.systables t ON c.tableid = t.tableid " + "WHERE c.constraintname='" + database.correctObjectName(name, UniqueConstraint.class) + "'";
            List<Map<String, ?>> rows = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement(sql));
            List<Map<String, ?>> returnList = new ArrayList<>();
            if (rows.isEmpty()) {
                return returnList;
            } else if (rows.size() > 1) {
                throw new UnexpectedLiquibaseException("Got multiple rows back querying unique constraints");
            } else {
                Map rowData = rows.get(0);
                String descriptor = rowData.get("DESCRIPTOR").toString();
                descriptor = descriptor.replaceFirst(".*\\(", "").replaceFirst("\\).*", "");
                for (String columnNumber : StringUtil.splitAndTrim(descriptor, ",")) {
                    String columnName = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForObject(new RawSqlStatement("select c.columnname from sys.syscolumns c " + "join sys.systables t on t.tableid=c.referenceid " + "where t.tablename='" + rowData.get("TABLENAME") + "' and c.columnnumber=" + columnNumber), String.class);
                    Map<String, String> row = new HashMap<>();
                    row.put("COLUMN_NAME", columnName);
                    returnList.add(row);
                }
                return returnList;
            }
        } else if (database instanceof FirebirdDatabase) {
            // does not support bulkQuery,  supportsBulkQuery should return false()
            // Careful! FIELD_NAME and INDEX_NAME in RDB$INDEX_SEGMENTS are CHAR, not VARCHAR columns.
            sql = "SELECT TRIM(RDB$INDEX_SEGMENTS.RDB$FIELD_NAME) AS column_name " + "FROM RDB$INDEX_SEGMENTS " + "LEFT JOIN RDB$INDICES ON RDB$INDICES.RDB$INDEX_NAME = RDB$INDEX_SEGMENTS.RDB$INDEX_NAME " + "WHERE UPPER(TRIM(RDB$INDICES.RDB$INDEX_NAME))='" + database.correctObjectName(name, UniqueConstraint.class) + "' " + "ORDER BY RDB$INDEX_SEGMENTS.RDB$FIELD_POSITION";
        } else if (database instanceof SybaseASADatabase) {
            // does not support bulkQuery,  supportsBulkQuery should return false()
            sql = "select sysconstraint.constraint_name, syscolumn.column_name " + "from sysconstraint, syscolumn, systable " + "where sysconstraint.ref_object_id = syscolumn.object_id " + "and sysconstraint.table_object_id = systable.object_id " + "and sysconstraint.constraint_name = '" + database.correctObjectName(name, UniqueConstraint.class) + "' " + "and systable.table_name = '" + database.correctObjectName(example.getRelation().getName(), Table.class) + "'";
        } else if (database instanceof Ingres9Database) {
            // does not support bulkQuery,  supportsBulkQuery should return false()
            sql = "select constraint_name, column_name " + "from iikeys " + "where constraint_name = '" + database.correctObjectName(name, UniqueConstraint.class) + "' " + "and table_name = '" + database.correctObjectName(example.getTable().getName(), Table.class) + "'";
        } else if (database instanceof InformixDatabase) {
            // does not support bulkQuery,  supportsBulkQuery should return false()
            sql = getUniqueConstraintsSqlInformix((InformixDatabase) database, schema, name);
        } else if (database instanceof Db2zDatabase) {
            sql = "select KC.TBCREATOR as CONSTRAINT_CONTAINER, KC.CONSTNAME as CONSTRAINT_NAME, KC.COLNAME as COLUMN_NAME from SYSIBM.SYSKEYCOLUSE KC, SYSIBM.SYSTABCONST TC " + "where KC.CONSTNAME = TC.CONSTNAME " + "and KC.TBCREATOR = TC.TBCREATOR " + "and TC.TYPE='U' " + (bulkQuery ? "" : "and KC.CONSTNAME='" + database.correctObjectName(name, UniqueConstraint.class) + "' ") + "and TC.TBCREATOR = '" + database.correctObjectName(schema.getName(), Schema.class) + "' " + "order by KC.COLSEQ";
        } else if (database instanceof H2Database && database.getDatabaseMajorVersion() >= 2) {
            String catalogName = database.correctObjectName(schema.getCatalogName(), Catalog.class);
            String schemaName = database.correctObjectName(schema.getName(), Schema.class);
            String constraintName = database.correctObjectName(name, UniqueConstraint.class);
            String tableName = database.correctObjectName(table.getName(), Table.class);
            sql = "select table_constraints.CONSTRAINT_NAME, index_columns.COLUMN_NAME, table_constraints.constraint_schema as CONSTRAINT_CONTAINER " + "from information_schema.table_constraints " + "join information_schema.index_columns on index_columns.index_name=table_constraints.index_name " + "where constraint_type='UNIQUE' ";
            if (catalogName != null) {
                sql += "and constraint_catalog='" + catalogName + "' ";
            }
            if (schemaName != null) {
                sql += "and constraint_schema='" + schemaName + "' ";
            }
            if (!bulkQuery) {
                if (tableName != null) {
                    sql += "and table_constraints.table_name='" + tableName + "' ";
                }
                if (constraintName != null) {
                    sql += "and constraint_name='" + constraintName + "'";
                }
            }
        } else {
            // If we do not have a specific handler for the RDBMS, we assume that the database has an
            // INFORMATION_SCHEMA we can use. This is a last-resort measure and might fail.
            String catalogName = database.correctObjectName(schema.getCatalogName(), Catalog.class);
            String schemaName = database.correctObjectName(schema.getName(), Schema.class);
            String constraintName = database.correctObjectName(name, UniqueConstraint.class);
            String tableName = database.correctObjectName(table.getName(), Table.class);
            sql = "select CONSTRAINT_NAME, COLUMN_LIST as COLUMN_NAME, constraint_schema as CONSTRAINT_CONTAINER " + "from " + database.getSystemSchema() + ".constraints " + "where constraint_type='UNIQUE' ";
            if (catalogName != null) {
                sql += "and constraint_catalog='" + catalogName + "' ";
            }
            if (schemaName != null) {
                sql += "and constraint_schema='" + schemaName + "' ";
            }
            if (!bulkQuery) {
                if (tableName != null) {
                    sql += "and table_name='" + tableName + "' ";
                }
                if (constraintName != null) {
                    sql += "and constraint_name='" + constraintName + "'";
                }
            }
        }
        List<Map<String, ?>> rows = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database).queryForList(new RawSqlStatement(sql));
        if (bulkQuery) {
            columnCache = new HashMap<>();
            snapshot.setScratchData(cacheKey, columnCache);
            for (Map<String, ?> row : rows) {
                String key = getCacheKey(row, database);
                List<Map<String, ?>> constraintRows = columnCache.get(key);
                if (constraintRows == null) {
                    constraintRows = new ArrayList<>();
                    columnCache.put(key, constraintRows);
                }
                constraintRows.add(row);
            }
            return listColumns(example, database, snapshot);
        } else {
            return rows;
        }
    } else {
        String lookupKey = getCacheKey(example, database);
        List<Map<String, ?>> rows = columnCache.get(lookupKey);
        if (rows == null) {
            rows = new ArrayList<>();
        }
        return rows;
    }
}

Example 15 with RawSqlStatement

use of liquibase.statement.core.RawSqlStatement in project liquibase by liquibase.

the class DatabaseUtils method initializeDatabase.

/**
 * Executes RawSqlStatements particular to each database engine to set the default schema for the given Database
 *
 * @param defaultCatalogName Catalog name and schema name are similar concepts.
 *                           Used if defaultCatalogName is null.
 * @param defaultSchemaName  Catalog name and schema name are similar concepts.
 *                           Catalog is used with Oracle, DB2 and MySQL, and takes
 *                           precedence over the schema name.
 * @param database           Which Database object is affected by the initialization.
 * @throws DatabaseException
 */
public static void initializeDatabase(String defaultCatalogName, String defaultSchemaName, Database database) throws DatabaseException {
    if (((defaultCatalogName != null) || (defaultSchemaName != null)) && !(database.getConnection() instanceof OfflineConnection)) {
        final Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database);
        if (database instanceof OracleDatabase) {
            String schema = defaultCatalogName;
            if (schema == null) {
                schema = defaultSchemaName;
            }
            executor.execute(new RawSqlStatement("ALTER SESSION SET CURRENT_SCHEMA=" + database.escapeObjectName(schema, Schema.class)));
        } else if (database instanceof PostgresDatabase && defaultSchemaName != null) {
            final String searchPath = executor.queryForObject(new RawSqlStatement("SHOW SEARCH_PATH"), String.class);
            if (!searchPath.equals(defaultCatalogName) && !searchPath.startsWith(defaultSchemaName + ",") && !searchPath.startsWith("\"" + defaultSchemaName + "\",")) {
                executor.execute(new RawSqlStatement("SET SEARCH_PATH TO " + database.escapeObjectName(defaultSchemaName, Schema.class) + ", " + searchPath));
            }
        } else if (database instanceof AbstractDb2Database) {
            String schema = defaultCatalogName;
            if (schema == null) {
                schema = defaultSchemaName;
            }
            executor.execute(new RawSqlStatement("SET CURRENT SCHEMA " + schema));
        } else if (database instanceof MySQLDatabase) {
            String schema = defaultCatalogName;
            if (schema == null) {
                schema = defaultSchemaName;
            }
            executor.execute(new RawSqlStatement("USE " + schema));
        }
    }
}