Search in sources :

Example 1 with RawSqlStatement

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

the class SqlPrecondition method check.

@Override
public void check(Database database, DatabaseChangeLog changeLog, ChangeSet changeSet) throws PreconditionFailedException, PreconditionErrorException {
    DatabaseConnection connection = database.getConnection();
    try {
        String result = (String) ExecutorService.getInstance().getExecutor(database).queryForObject(new RawSqlStatement(getSql().replaceFirst(";$", "")), String.class);
        if (result == null) {
            throw new PreconditionFailedException("No rows returned from SQL Precondition", changeLog, this);
        }
        String expectedResult = getExpectedResult();
        if (!expectedResult.equals(result)) {
            throw new PreconditionFailedException("SQL Precondition failed.  Expected '" + expectedResult + "' got '" + result + "'", changeLog, this);
        }
    } catch (DatabaseException e) {
        throw new PreconditionErrorException(e, changeLog, this);
    }
}
Also used : RawSqlStatement(liquibase.statement.core.RawSqlStatement) DatabaseConnection(liquibase.database.DatabaseConnection)

Example 2 with RawSqlStatement

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

the class CommandLineUtils method initializeDatabase.

/**
     * Executes RawSqlStatements particular to each database engine to set the default schema for the given Database
     *  
     * @param username            The username used for the connection. Used with MSSQL databases
     * @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 username, String defaultCatalogName, String defaultSchemaName, Database database) throws DatabaseException {
    if ((defaultCatalogName != null || defaultSchemaName != null) && !(database.getConnection() instanceof OfflineConnection)) {
        if (database instanceof OracleDatabase) {
            String schema = defaultCatalogName;
            if (schema == null) {
                schema = defaultSchemaName;
            }
            ExecutorService.getInstance().getExecutor(database).execute(new RawSqlStatement("ALTER SESSION SET CURRENT_SCHEMA=" + database.escapeObjectName(schema, Schema.class)));
        } else if (database instanceof MSSQLDatabase && defaultSchemaName != null) {
            boolean sql2005OrLater = true;
            try {
                sql2005OrLater = database.getDatabaseMajorVersion() >= 9;
            } catch (DatabaseException e) {
            // Assume SQL Server 2005 or later
            }
            if (sql2005OrLater && username != null) {
                ExecutorService.getInstance().getExecutor(database).execute(new RawSqlStatement("IF USER_NAME() <> N'dbo'\r\n" + "BEGIN\r\n" + "	DECLARE @sql [nvarchar](MAX)\r\n" + "	SELECT @sql = N'ALTER USER ' + QUOTENAME(USER_NAME()) + N' WITH DEFAULT_SCHEMA = " + database.escapeStringForDatabase(database.escapeObjectName(username, DatabaseObject.class)) + "'\r\n" + "	EXEC sp_executesql @sql\r\n" + "END"));
            }
        } else if (database instanceof PostgresDatabase && defaultSchemaName != null) {
            ExecutorService.getInstance().getExecutor(database).execute(new RawSqlStatement("SET SEARCH_PATH TO " + database.escapeObjectName(defaultSchemaName, Schema.class)));
        } else if (database instanceof DB2Database) {
            String schema = defaultCatalogName;
            if (schema == null) {
                schema = defaultSchemaName;
            }
            ExecutorService.getInstance().getExecutor(database).execute(new RawSqlStatement("SET CURRENT SCHEMA " + schema));
        } else if (database instanceof MySQLDatabase) {
            String schema = defaultCatalogName;
            if (schema == null) {
                schema = defaultSchemaName;
            }
            ExecutorService.getInstance().getExecutor(database).execute(new RawSqlStatement("USE " + schema));
        }
    }
}
Also used : RawSqlStatement(liquibase.statement.core.RawSqlStatement) CatalogAndSchema(liquibase.CatalogAndSchema) Schema(liquibase.structure.core.Schema) OfflineConnection(liquibase.database.OfflineConnection)

Example 3 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, Collection<DatabaseObject> missingObjects, Database database) throws DatabaseException {
    if (database instanceof DB2Database) {
        Executor executor = ExecutorService.getInstance().getExecutor(database);
        List<Map<String, ?>> rs = executor.queryForList(new RawSqlStatement("select TABSCHEMA, TABNAME, BSCHEMA, BNAME from syscat.tabdep where (" + StringUtils.join(schemas, " OR ", new StringUtils.StringUtilsFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "TABSCHEMA='" + obj + "'";
            }
        }) + ")"));
        for (Map<String, ?> row : rs) {
            String tabName = StringUtils.trimToNull((String) row.get("TABSCHEMA")) + "." + StringUtils.trimToNull((String) row.get("TABNAME"));
            String bName = StringUtils.trimToNull((String) row.get("BSCHEMA")) + "." + StringUtils.trimToNull((String) row.get("BNAME"));
            graph.add(bName, tabName);
        }
    } else if (database instanceof OracleDatabase) {
        Executor executor = ExecutorService.getInstance().getExecutor(database);
        List<Map<String, ?>> rs = executor.queryForList(new RawSqlStatement("select OWNER, NAME, REFERENCED_OWNER, REFERENCED_NAME from DBA_DEPENDENCIES where REFERENCED_OWNER != 'SYS' AND NOT(NAME LIKE 'BIN$%') AND NOT(OWNER = REFERENCED_OWNER AND NAME = REFERENCED_NAME) AND (" + StringUtils.join(schemas, " OR ", new StringUtils.StringUtilsFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "OWNER='" + obj + "'";
            }
        }) + ")"));
        for (Map<String, ?> row : rs) {
            String tabName = StringUtils.trimToNull((String) row.get("OWNER")) + "." + StringUtils.trimToNull((String) row.get("NAME"));
            String bName = StringUtils.trimToNull((String) row.get("REFERENCED_OWNER")) + "." + StringUtils.trimToNull((String) row.get("REFERENCED_NAME"));
            graph.add(bName, tabName);
        }
    } else if (database instanceof MSSQLDatabase && database.getDatabaseMajorVersion() >= 9) {
        Executor executor = ExecutorService.getInstance().getExecutor(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 (" + StringUtils.join(schemas, " OR ", new StringUtils.StringUtilsFormatter<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 (" + StringUtils.join(schemas, " OR ", new StringUtils.StringUtilsFormatter<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 (" + StringUtils.join(schemas, " OR ", new StringUtils.StringUtilsFormatter<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 " + StringUtils.join(schemas, " OR ", new StringUtils.StringUtilsFormatter<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 " + StringUtils.join(schemas, " OR ", new StringUtils.StringUtilsFormatter<String>() {

            @Override
            public String toString(String obj) {
                return "SCHEMA_NAME(SCHEMA_ID)='" + obj + "'";
            }
        });
        List<Map<String, ?>> rs = executor.queryForList(new RawSqlStatement(sql));
        if (rs.size() > 0) {
            for (Map<String, ?> row : rs) {
                String bName = StringUtils.trimToNull((String) row.get("REFERENCED_SCHEMA_NAME")) + "." + StringUtils.trimToNull((String) row.get("REFERENCED_NAME"));
                String tabName = StringUtils.trimToNull((String) row.get("REFERENCING_SCHEMA_NAME")) + "." + StringUtils.trimToNull((String) row.get("REFERENCING_NAME"));
                if (!bName.equals(tabName)) {
                    graph.add(bName, tabName);
                }
            }
        }
    }
}
Also used : DB2Database(liquibase.database.core.DB2Database) RawSqlStatement(liquibase.statement.core.RawSqlStatement) OracleDatabase(liquibase.database.core.OracleDatabase) Executor(liquibase.executor.Executor) StringUtils(liquibase.util.StringUtils) MSSQLDatabase(liquibase.database.core.MSSQLDatabase)

Example 4 with RawSqlStatement

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

the class PostgresDatabase method getSearchPaths.

/*
     * Get the current search paths
     */
private List<String> getSearchPaths() {
    List<String> searchPaths = null;
    try {
        DatabaseConnection con = getConnection();
        if (con != null) {
            String searchPathResult = (String) ExecutorService.getInstance().getExecutor(this).queryForObject(new RawSqlStatement("SHOW search_path"), String.class);
            if (searchPathResult != null) {
                String[] dirtySearchPaths = searchPathResult.split("\\,");
                searchPaths = new ArrayList<String>();
                for (String searchPath : dirtySearchPaths) {
                    searchPath = searchPath.trim();
                    // Ensure there is consistency ..
                    if (searchPath.equals("\"$user\"")) {
                        searchPath = "$user";
                    }
                    searchPaths.add(searchPath);
                }
            }
        }
    } catch (Exception e) {
        // TODO: Something?
        e.printStackTrace();
        LogFactory.getLogger().severe("Failed to get default catalog name from postgres", e);
    }
    return searchPaths;
}
Also used : RawSqlStatement(liquibase.statement.core.RawSqlStatement) DatabaseConnection(liquibase.database.DatabaseConnection) DatabaseException(liquibase.exception.DatabaseException) SQLException(java.sql.SQLException)

Example 5 with RawSqlStatement

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

the class AbstractSQLChangeTest method generateStatements_willCallNativeSqlIfPossible.

@Test
public void generateStatements_willCallNativeSqlIfPossible() throws DatabaseException {
    ExampleAbstractSQLChange change = new ExampleAbstractSQLChange("SOME SQL");
    Database database = mock(Database.class);
    DatabaseConnection connection = mock(DatabaseConnection.class);
    when(database.getConnection()).thenReturn(connection);
    when(connection.nativeSQL("SOME SQL")).thenReturn("SOME NATIVE SQL");
    SqlStatement[] statements = change.generateStatements(database);
    assertEquals(1, statements.length);
    assertEquals("SOME NATIVE SQL", ((RawSqlStatement) statements[0]).getSql());
    //If there is an error, it falls back to passed SQL
    when(connection.nativeSQL("SOME SQL")).thenThrow(new DatabaseException("Testing exception"));
    statements = change.generateStatements(database);
    assertEquals(1, statements.length);
    assertEquals("SOME SQL", ((RawSqlStatement) statements[0]).getSql());
}
Also used : RawSqlStatement(liquibase.statement.core.RawSqlStatement) SqlStatement(liquibase.statement.SqlStatement) MSSQLDatabase(liquibase.database.core.MSSQLDatabase) Database(liquibase.database.Database) DatabaseConnection(liquibase.database.DatabaseConnection) DatabaseException(liquibase.exception.DatabaseException) Test(org.junit.Test)

Aggregations

RawSqlStatement (liquibase.statement.core.RawSqlStatement)30 DatabaseException (liquibase.exception.DatabaseException)11 ExecutorService (liquibase.executor.ExecutorService)10 Executor (liquibase.executor.Executor)9 Map (java.util.Map)7 Schema (liquibase.structure.core.Schema)7 CatalogAndSchema (liquibase.CatalogAndSchema)6 MSSQLDatabase (liquibase.database.core.MSSQLDatabase)6 SqlStatement (liquibase.statement.SqlStatement)6 Test (org.junit.Test)6 DatabaseConnection (liquibase.database.DatabaseConnection)5 Table (liquibase.structure.core.Table)5 ArrayList (java.util.ArrayList)4 Database (liquibase.database.Database)4 OfflineConnection (liquibase.database.OfflineConnection)4 AbstractIntegrationTest (liquibase.dbtest.AbstractIntegrationTest)4 Column (liquibase.structure.core.Column)4 UnexpectedLiquibaseException (liquibase.exception.UnexpectedLiquibaseException)3 DatabaseSnapshot (liquibase.snapshot.DatabaseSnapshot)3 SnapshotControl (liquibase.snapshot.SnapshotControl)3