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

Example 16 with RawSqlStatement

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

the class DiffToChangeLog method addDependencies.

/**
 * Adds dependencies to the graph as schema.object_name.
 */
protected void addDependencies(DependencyUtil.DependencyGraph<String> graph, List<String> schemas, Database database) throws DatabaseException {
    if (database instanceof DB2Database) {
        Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database);
        List<Map<String, ?>> rs = executor.queryForList(new RawSqlStatement("select TABSCHEMA, TABNAME, BSCHEMA, BNAME from syscat.tabdep where (" + StringUtil.join(schemas, " OR ", new StringUtil.StringUtilFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "TABSCHEMA='" + obj + "'";
            }
        }) + ")"));
        for (Map<String, ?> row : rs) {
            String tabName = StringUtil.trimToNull((String) row.get("TABSCHEMA")) + "." + StringUtil.trimToNull((String) row.get("TABNAME"));
            String bName = StringUtil.trimToNull((String) row.get("BSCHEMA")) + "." + StringUtil.trimToNull((String) row.get("BNAME"));
            graph.add(bName, tabName);
        }
    } else if (database instanceof Db2zDatabase) {
        Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database);
        String db2ZosSql = "SELECT DSCHEMA AS TABSCHEMA, DNAME AS TABNAME, BSCHEMA, BNAME FROM SYSIBM.SYSDEPENDENCIES WHERE (" + StringUtil.join(schemas, " OR ", new StringUtil.StringUtilFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "DSCHEMA='" + obj + "'";
            }
        }) + ")";
        List<Map<String, ?>> rs = executor.queryForList(new RawSqlStatement(db2ZosSql));
        for (Map<String, ?> row : rs) {
            String tabName = StringUtil.trimToNull((String) row.get("TABSCHEMA")) + "." + StringUtil.trimToNull((String) row.get("TABNAME"));
            String bName = StringUtil.trimToNull((String) row.get("BSCHEMA")) + "." + StringUtil.trimToNull((String) row.get("BNAME"));
            graph.add(bName, tabName);
        }
    } else if (database instanceof OracleDatabase) {
        Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database);
        List<Map<String, ?>> rs = queryForDependenciesOracle(executor, schemas);
        for (Map<String, ?> row : rs) {
            String tabName = null;
            if (tryDbaDependencies) {
                tabName = StringUtil.trimToNull((String) row.get("OWNER")) + "." + StringUtil.trimToNull((String) row.get("NAME"));
            } else {
                tabName = StringUtil.trimToNull((String) row.get("REFERENCED_OWNER")) + "." + StringUtil.trimToNull((String) row.get("NAME"));
            }
            String bName = StringUtil.trimToNull((String) row.get("REFERENCED_OWNER")) + "." + StringUtil.trimToNull((String) row.get("REFERENCED_NAME"));
            graph.add(bName, tabName);
        }
    } else if (database instanceof MSSQLDatabase) {
        Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database);
        String sql = "select object_schema_name(referencing_id) as referencing_schema_name, object_name(referencing_id) as referencing_name, object_name(referenced_id) as referenced_name, object_schema_name(referenced_id) as referenced_schema_name  from sys.sql_expression_dependencies depz where (" + StringUtil.join(schemas, " OR ", new StringUtil.StringUtilFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "object_schema_name(referenced_id)='" + obj + "'";
            }
        }) + ")";
        sql += " UNION select object_schema_name(object_id) as referencing_schema_name, object_name(object_id) as referencing_name, object_name(parent_object_id) as referenced_name, object_schema_name(parent_object_id) as referenced_schema_name " + "from sys.objects " + "where parent_object_id > 0 " + "and is_ms_shipped=0 " + "and (" + StringUtil.join(schemas, " OR ", new StringUtil.StringUtilFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "object_schema_name(object_id)='" + obj + "'";
            }
        }) + ")";
        sql += " UNION select object_schema_name(fk.object_id) as referencing_schema_name, fk.name as referencing_name, i.name as referenced_name, object_schema_name(i.object_id) as referenced_schema_name " + "from sys.foreign_keys fk " + "join sys.indexes i on fk.referenced_object_id=i.object_id and fk.key_index_id=i.index_id " + "where fk.is_ms_shipped=0 " + "and (" + StringUtil.join(schemas, " OR ", new StringUtil.StringUtilFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "object_schema_name(fk.object_id)='" + obj + "'";
            }
        }) + ")";
        sql += " UNION select object_schema_name(i.object_id) as referencing_schema_name, object_name(i.object_id) as referencing_name, s.name as referenced_name, null as referenced_schema_name " + "from sys.indexes i " + "join sys.partition_schemes s on i.data_space_id = s.data_space_id";
        sql += " UNION select null as referencing_schema_name, s.name as referencing_name, f.name as referenced_name, null as referenced_schema_name from sys.partition_functions f " + "join sys.partition_schemes s on s.function_id=f.function_id";
        sql += " UNION select null as referencing_schema_name, s.name as referencing_name, fg.name as referenced_name, null as referenced_schema_name from sys.partition_schemes s " + "join sys.destination_data_spaces ds on s.data_space_id=ds.partition_scheme_id " + "join sys.filegroups fg on ds.data_space_id=fg.data_space_id";
        // get data file -> filegroup dependencies
        sql += " UNION select distinct null as referencing_schema_name, f.name as referencing_name, ds.name as referenced_name, null as referenced_schema_name from sys.database_files f " + "join sys.data_spaces ds on f.data_space_id=ds.data_space_id " + "where f.data_space_id > 1";
        // get table -> filestream dependencies
        sql += " UNION select object_schema_name(t.object_id) as referencing_schema_name, t.name as referencing_name, ds.name as referenced_name, null as referenced_schema_name from sys.tables t " + "join sys.data_spaces ds on t.filestream_data_space_id=ds.data_space_id " + "where t.filestream_data_space_id > 1";
        // get table -> filestream dependencies
        sql += " UNION select object_schema_name(t.object_id) as referencing_schema_name, t.name as referencing_name, ds.name as referenced_name, null as referenced_schema_name from sys.tables t " + "join sys.data_spaces ds on t.lob_data_space_id=ds.data_space_id " + "where t.lob_data_space_id > 1";
        // get index -> filegroup dependencies
        sql += " UNION select object_schema_name(i.object_id) as referencing_schema_name, i.name as referencing_name, ds.name as referenced_name, null as referenced_schema_name from sys.indexes i " + "join sys.data_spaces ds on i.data_space_id=ds.data_space_id " + "where i.data_space_id > 1";
        // get index -> table dependencies
        sql += " UNION select object_schema_name(i.object_id) as referencing_schema_name, i.name as referencing_name, object_name(i.object_id) as referenced_name, object_schema_name(i.object_id) as referenced_schema_name from sys.indexes i " + "where " + StringUtil.join(schemas, " OR ", new StringUtil.StringUtilFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "object_schema_name(i.object_id)='" + obj + "'";
            }
        });
        // get schema -> base object dependencies
        sql += " UNION SELECT SCHEMA_NAME(SCHEMA_ID) as referencing_schema_name, name as referencing_name, PARSENAME(BASE_OBJECT_NAME,1) AS referenced_name, (CASE WHEN PARSENAME(BASE_OBJECT_NAME,2) IS NULL THEN schema_name(schema_id) else PARSENAME(BASE_OBJECT_NAME,2) END) AS referenced_schema_name FROM sys.synonyms WHERE is_ms_shipped='false' AND " + StringUtil.join(schemas, " OR ", new StringUtil.StringUtilFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "SCHEMA_NAME(SCHEMA_ID)='" + obj + "'";
            }
        });
        // get non-clustered indexes -> unique clustered indexes on views dependencies
        sql += " UNION select object_schema_name(c.object_id) as referencing_schema_name, c.name as referencing_name, object_schema_name(nc.object_id) as referenced_schema_name, nc.name as referenced_name from sys.indexes c join sys.indexes nc on c.object_id=nc.object_id JOIN sys.objects o ON c.object_id = o.object_id where  c.index_id != nc.index_id and c.type_desc='CLUSTERED' and c.is_unique='true' and (not(nc.type_desc='CLUSTERED') OR nc.is_unique='false') AND o.type_desc='VIEW' AND o.name='AR_DETAIL_OPEN'";
        List<Map<String, ?>> rs = executor.queryForList(new RawSqlStatement(sql));
        if (!rs.isEmpty()) {
            for (Map<String, ?> row : rs) {
                String bName = StringUtil.trimToNull((String) row.get("REFERENCED_SCHEMA_NAME")) + "." + StringUtil.trimToNull((String) row.get("REFERENCED_NAME"));
                String tabName = StringUtil.trimToNull((String) row.get("REFERENCING_SCHEMA_NAME")) + "." + StringUtil.trimToNull((String) row.get("REFERENCING_NAME"));
                if (!bName.equals(tabName)) {
                    graph.add(bName, tabName);
                }
            }
        }
    } else if (database instanceof PostgresDatabase) {
        final String sql = queryForDependenciesPostgreSql(schemas);
        final Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("jdbc", database);
        final List<Map<String, ?>> queryForListResult = executor.queryForList(new RawSqlStatement(sql));
        for (Map<String, ?> row : queryForListResult) {
            String bName = StringUtil.trimToEmpty((String) row.get("REFERENCING_SCHEMA_NAME")) + "." + StringUtil.trimToEmpty((String) row.get("REFERENCING_NAME"));
            String tabName = StringUtil.trimToEmpty((String) row.get("REFERENCED_SCHEMA_NAME")) + "." + StringUtil.trimToEmpty((String) row.get("REFERENCED_NAME"));
            if (!(tabName.isEmpty() || bName.isEmpty())) {
                graph.add(bName.replace("\"", ""), tabName.replace("\"", ""));
                graph.add(bName.replace("\"", "").replaceAll("\\s*\\([^)]*\\)\\s*", ""), tabName.replace("\"", "").replaceAll("\\s*\\([^)]*\\)\\s*", ""));
            }
        }
    }
}

Example 17 with RawSqlStatement

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

the class AbstractSQLChange method generateStatements.

/**
 * Generates one or more SqlStatements depending on how the SQL should be parsed.
 * If split statements is set to true then the SQL is split and each command is made into a separate SqlStatement.
 * <p></p>
 * If stripping comments is true then any comments are removed before the splitting is executed.
 * The set SQL is passed through the {@link java.sql.Connection#nativeSQL} method if a connection is available.
 */
@Override
public SqlStatement[] generateStatements(Database database) {
    List<SqlStatement> returnStatements = new ArrayList<>();
    String sql = StringUtil.trimToNull(getSql());
    if (sql == null) {
        return new SqlStatement[0];
    }
    String processedSQL = normalizeLineEndings(sql);
    if (this instanceof RawSQLChange && ((RawSQLChange) this).isRerunnable()) {
        returnStatements.add(new RawSqlStatement(processedSQL, getEndDelimiter()));
        return returnStatements.toArray(new SqlStatement[returnStatements.size()]);
    }
    for (String statement : StringUtil.processMultiLineSQL(processedSQL, isStripComments(), isSplitStatements(), getEndDelimiter())) {
        if (database instanceof MSSQLDatabase) {
            statement = statement.replaceAll("\\n", "\r\n");
        }
        String escapedStatement = statement;
        try {
            if (database.getConnection() != null) {
                escapedStatement = database.getConnection().nativeSQL(statement);
            }
        } catch (DatabaseException e) {
            escapedStatement = statement;
        }
        returnStatements.add(new RawSqlStatement(escapedStatement, getEndDelimiter()));
    }
    return returnStatements.toArray(new SqlStatement[returnStatements.size()]);
}

Example 18 with RawSqlStatement

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

the class Liquibase method outputHeader.

public void outputHeader(String message) throws DatabaseException {
    Executor executor = Scope.getCurrentScope().getSingleton(ExecutorService.class).getExecutor("logging", database);
    executor.comment("*********************************************************************");
    executor.comment(message);
    executor.comment("*********************************************************************");
    executor.comment("Change Log: " + changeLogFile);
    executor.comment("Ran at: " + DateFormat.getDateTimeInstance(DateFormat.SHORT, DateFormat.SHORT).format(new Date()));
    DatabaseConnection connection = getDatabase().getConnection();
    if (connection != null) {
        executor.comment("Against: " + connection.getConnectionUserName() + "@" + connection.getURL());
    }
    executor.comment("Liquibase version: " + LiquibaseUtil.getBuildVersionInfo());
    executor.comment("*********************************************************************" + StreamUtil.getLineSeparator());
    if ((database instanceof MSSQLDatabase) && (database.getDefaultCatalogName() != null)) {
        executor.execute(new RawSqlStatement("USE " + database.escapeObjectName(database.getDefaultCatalogName(), Catalog.class) + ";"));
    }
}

Example 19 with RawSqlStatement

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

the class ExecuteSqlCommand method run.

@Override
protected CommandResult run() throws Exception {
    Executor executor = ExecutorService.getInstance().getExecutor(database);
    String sqlText;
    if (sqlFile == null) {
        sqlText = sql;
    } else {
        File file = new File(sqlFile);
        if (!file.exists()) {
            throw new LiquibaseException(String.format("The file '%s' does not exist", file.getCanonicalPath()));
        }
        sqlText = FileUtil.getContents(file);
    }
    String out = "";
    String[] sqlStrings = StringUtils.processMutliLineSQL(sqlText, true, true, delimiter);
    for (String sql : sqlStrings) {
        if (sql.toLowerCase().matches("\\s*select .*")) {
            List<Map<String, ?>> rows = executor.queryForList(new RawSqlStatement(sql));
            out += "Output of " + sql + ":\n";
            if (rows.size() == 0) {
                out += "-- Empty Resultset --\n";
            } else {
                SortedSet<String> keys = new TreeSet<String>();
                for (Map<String, ?> row : rows) {
                    keys.addAll(row.keySet());
                }
                out += StringUtils.join(keys, " | ") + " |\n";
                for (Map<String, ?> row : rows) {
                    for (String key : keys) {
                        out += row.get(key) + " | ";
                    }
                    out += "\n";
                }
            }
        } else {
            executor.execute(new RawSqlStatement(sql));
            out += "Successfully Executed: " + sql + "\n";
        }
        out += "\n";
    }
    database.commit();
    return new CommandResult(out.trim());
}

Example 20 with RawSqlStatement

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

the class AddLookupTableChange method generateStatements.

@Override
public SqlStatement[] generateStatements(Database database) {
    List<SqlStatement> statements = new ArrayList<>();
    String newTableCatalogName = getNewTableCatalogName();
    String newTableSchemaName = getNewTableSchemaName();
    String existingTableCatalogName = getExistingTableCatalogName();
    String existingTableSchemaName = getExistingTableSchemaName();
    SqlStatement[] createTablesSQL = { new RawSqlStatement("CREATE TABLE " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " AS SELECT DISTINCT " + database.escapeObjectName(getExistingColumnName(), Column.class) + " AS " + database.escapeObjectName(getNewColumnName(), Column.class) + " FROM " + database.escapeTableName(existingTableCatalogName, existingTableSchemaName, getExistingTableName()) + " WHERE " + database.escapeObjectName(getExistingColumnName(), Column.class) + " IS NOT NULL") };
    if (database instanceof MSSQLDatabase) {
        createTablesSQL = new SqlStatement[] { new RawSqlStatement("SELECT DISTINCT " + database.escapeObjectName(getExistingColumnName(), Column.class) + " AS " + database.escapeObjectName(getNewColumnName(), Column.class) + " INTO " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " FROM " + database.escapeTableName(existingTableCatalogName, existingTableSchemaName, getExistingTableName()) + " WHERE " + database.escapeObjectName(getExistingColumnName(), Column.class) + " IS NOT NULL") };
    } else if (database instanceof SybaseASADatabase) {
        createTablesSQL = new SqlStatement[] { new RawSqlStatement("SELECT DISTINCT " + database.escapeObjectName(getExistingColumnName(), Column.class) + " AS " + database.escapeObjectName(getNewColumnName(), Column.class) + " INTO " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " FROM " + database.escapeTableName(existingTableCatalogName, existingTableSchemaName, getExistingTableName()) + " WHERE " + database.escapeObjectName(getExistingColumnName(), Column.class) + " IS NOT NULL") };
    } else if (database instanceof Db2zDatabase) {
        CreateTableStatement tableStatement = new CreateTableStatement(newTableCatalogName, newTableSchemaName, getNewTableName());
        if (getNewColumnName() != null) {
            tableStatement.addColumn(getNewColumnName(), DataTypeFactory.getInstance().fromDescription(getNewColumnDataType(), database));
            tableStatement.addColumnConstraint(new NotNullConstraint(getNewColumnName()));
        }
        createTablesSQL = new SqlStatement[] { tableStatement, new RawSqlStatement("INSERT INTO " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " SELECT DISTINCT " + database.escapeObjectName(getExistingColumnName(), Column.class) + " FROM " + database.escapeTableName(existingTableCatalogName, existingTableSchemaName, getExistingTableName()) + " WHERE " + database.escapeObjectName(getExistingColumnName(), Column.class) + " IS NOT NULL") };
    } else if (database instanceof DB2Database) {
        createTablesSQL = new SqlStatement[] { new RawSqlStatement("CREATE TABLE " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " AS (SELECT " + database.escapeObjectName(getExistingColumnName(), Column.class) + " AS " + database.escapeObjectName(getNewColumnName(), Column.class) + " FROM " + database.escapeTableName(existingTableCatalogName, existingTableSchemaName, getExistingTableName()) + ") WITH NO DATA"), new RawSqlStatement("INSERT INTO " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " SELECT DISTINCT " + database.escapeObjectName(getExistingColumnName(), Column.class) + " FROM " + database.escapeTableName(existingTableCatalogName, existingTableSchemaName, getExistingTableName()) + " WHERE " + database.escapeObjectName(getExistingColumnName(), Column.class) + " IS NOT NULL") };
    } else if (database instanceof InformixDatabase) {
        createTablesSQL = new SqlStatement[] { new RawSqlStatement("CREATE TABLE " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " ( " + database.escapeObjectName(getNewColumnName(), Column.class) + " " + getNewColumnDataType() + " )"), new RawSqlStatement("INSERT INTO " + database.escapeTableName(newTableCatalogName, newTableSchemaName, getNewTableName()) + " ( " + database.escapeObjectName(getNewColumnName(), Column.class) + " ) SELECT DISTINCT " + database.escapeObjectName(getExistingColumnName(), Column.class) + " FROM " + database.escapeTableName(existingTableCatalogName, existingTableSchemaName, getExistingTableName()) + " WHERE " + database.escapeObjectName(getExistingColumnName(), Column.class) + " IS NOT NULL") };
    }
    statements.addAll(Arrays.asList(createTablesSQL));
    if (!(database instanceof OracleDatabase) && !(database instanceof Db2zDatabase)) {
        AddNotNullConstraintChange addNotNullChange = new AddNotNullConstraintChange();
        addNotNullChange.setSchemaName(newTableSchemaName);
        addNotNullChange.setTableName(getNewTableName());
        addNotNullChange.setColumnName(getNewColumnName());
        addNotNullChange.setColumnDataType(getNewColumnDataType());
        statements.addAll(Arrays.asList(addNotNullChange.generateStatements(database)));
    }
    if (database instanceof DB2Database) {
        statements.add(new ReorganizeTableStatement(newTableCatalogName, newTableSchemaName, getNewTableName()));
    }
    AddPrimaryKeyChange addPKChange = new AddPrimaryKeyChange();
    addPKChange.setSchemaName(newTableSchemaName);
    addPKChange.setTableName(getNewTableName());
    addPKChange.setColumnNames(getNewColumnName());
    statements.addAll(Arrays.asList(addPKChange.generateStatements(database)));
    if (database instanceof DB2Database) {
        statements.add(new ReorganizeTableStatement(newTableCatalogName, newTableSchemaName, getNewTableName()));
    }
    AddForeignKeyConstraintChange addFKChange = new AddForeignKeyConstraintChange();
    addFKChange.setBaseTableSchemaName(existingTableSchemaName);
    addFKChange.setBaseTableName(getExistingTableName());
    addFKChange.setBaseColumnNames(getExistingColumnName());
    addFKChange.setReferencedTableSchemaName(newTableSchemaName);
    addFKChange.setReferencedTableName(getNewTableName());
    addFKChange.setReferencedColumnNames(getNewColumnName());
    addFKChange.setConstraintName(getFinalConstraintName());
    statements.addAll(Arrays.asList(addFKChange.generateStatements(database)));
    return statements.toArray(new SqlStatement[statements.size()]);
}