Examples with DatabaseException liquibase.exception.DatabaseException used on opensource projects

Example 31 with DatabaseException

use of liquibase.exception.DatabaseException in project liquibase by liquibase.

the class TagDatabaseGenerator method generateSql.

@Override
public Sql[] generateSql(TagDatabaseStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String tableNameEscaped = database.escapeTableName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName());
    String orderColumnNameEscaped = database.escapeObjectName("ORDEREXECUTED", Column.class);
    String dateColumnNameEscaped = database.escapeObjectName("DATEEXECUTED", Column.class);
    String tagColumnNameEscaped = database.escapeObjectName("TAG", Column.class);
    String tagEscaped = DataTypeFactory.getInstance().fromObject(statement.getTag(), database).objectToSql(statement.getTag(), database);
    if (database instanceof MySQLDatabase) {
        return new Sql[] { new UnparsedSql("UPDATE " + tableNameEscaped + " AS C " + "INNER JOIN (" + "SELECT " + orderColumnNameEscaped + ", " + dateColumnNameEscaped + " " + "FROM " + tableNameEscaped + " order by " + dateColumnNameEscaped + " desc, " + orderColumnNameEscaped + " desc limit 1) AS D " + "ON C." + orderColumnNameEscaped + " = D." + orderColumnNameEscaped + " " + "SET C." + tagColumnNameEscaped + " = " + tagEscaped) };
    } else if (database instanceof PostgresDatabase) {
        return new Sql[] { new UnparsedSql("UPDATE " + tableNameEscaped + " t SET TAG=" + tagEscaped + " FROM (SELECT " + dateColumnNameEscaped + ", " + orderColumnNameEscaped + " FROM " + tableNameEscaped + " ORDER BY " + dateColumnNameEscaped + " DESC, " + orderColumnNameEscaped + " DESC LIMIT 1) sub " + "WHERE t." + dateColumnNameEscaped + "=sub." + dateColumnNameEscaped + " AND t." + orderColumnNameEscaped + "=sub." + orderColumnNameEscaped) };
    } else if (database instanceof InformixDatabase) {
        String tempTableNameEscaped = database.escapeObjectName("max_order_temp", Table.class);
        return new Sql[] { new UnparsedSql("SELECT MAX(" + dateColumnNameEscaped + ") AS " + dateColumnNameEscaped + ", MAX(" + orderColumnNameEscaped + ") AS " + orderColumnNameEscaped + " " + "FROM " + tableNameEscaped + " " + "INTO TEMP " + tempTableNameEscaped + " WITH NO LOG"), new UnparsedSql("UPDATE " + tableNameEscaped + " " + "SET TAG = " + tagEscaped + " " + "WHERE " + dateColumnNameEscaped + " = (" + "SELECT " + dateColumnNameEscaped + " " + "FROM " + tempTableNameEscaped + ") AND " + orderColumnNameEscaped + " = (" + "SELECT " + orderColumnNameEscaped + " " + "FROM " + tempTableNameEscaped + ");"), new UnparsedSql("DROP TABLE " + tempTableNameEscaped + ";") };
    } else if (database instanceof MSSQLDatabase) {
        String changelogAliasEscaped = database.escapeObjectName("changelog", Table.class);
        String latestAliasEscaped = database.escapeObjectName("latest", Table.class);
        String idColumnEscaped = database.escapeObjectName("ID", Column.class);
        String authorColumnEscaped = database.escapeObjectName("AUTHOR", Column.class);
        String filenameColumnEscaped = database.escapeObjectName("FILENAME", Column.class);
        String topClause = "TOP (1)";
        try {
            if (database.getDatabaseMajorVersion() < 10) {
                // SQL Server 2005 or earlier
                topClause = "TOP 1";
            }
        } catch (DatabaseException ignored) {
        // assume SQL Server 2008 or later
        }
        return new Sql[] { new UnparsedSql("UPDATE " + changelogAliasEscaped + " " + "SET " + tagColumnNameEscaped + " = " + tagEscaped + " " + "FROM " + tableNameEscaped + " AS " + changelogAliasEscaped + " " + "INNER JOIN (" + "SELECT " + topClause + " " + idColumnEscaped + ", " + authorColumnEscaped + ", " + filenameColumnEscaped + " " + "FROM " + tableNameEscaped + " " + "ORDER BY " + dateColumnNameEscaped + " DESC, " + orderColumnNameEscaped + " DESC" + ") AS " + latestAliasEscaped + " " + "ON " + latestAliasEscaped + "." + idColumnEscaped + " = " + changelogAliasEscaped + "." + idColumnEscaped + " " + "AND " + latestAliasEscaped + "." + authorColumnEscaped + " = " + changelogAliasEscaped + "." + authorColumnEscaped + " " + "AND " + latestAliasEscaped + "." + filenameColumnEscaped + " = " + changelogAliasEscaped + "." + filenameColumnEscaped) };
    } else if (database instanceof OracleDatabase || database instanceof DB2Database) {
        String selectClause = "SELECT";
        String endClause = ")";
        String delimiter = "";
        if (database instanceof OracleDatabase) {
            selectClause = "SELECT * FROM (SELECT";
            endClause = ") where rownum=1)";
        } else if (database instanceof DB2Database) {
            endClause = " FETCH FIRST 1 ROWS ONLY)";
        }
        return new Sql[] { new UnparsedSql("MERGE INTO " + tableNameEscaped + " a " + "USING (" + selectClause + " " + orderColumnNameEscaped + ", " + dateColumnNameEscaped + " from " + tableNameEscaped + " order by " + dateColumnNameEscaped + " desc, " + orderColumnNameEscaped + " desc" + endClause + " b " + "ON ( a." + dateColumnNameEscaped + " = b." + dateColumnNameEscaped + " and a." + orderColumnNameEscaped + "=b." + orderColumnNameEscaped + " ) " + "WHEN MATCHED THEN " + "UPDATE SET  a.tag=" + tagEscaped + delimiter) };
    } else {
        //Only uses dateexecuted as a default. Depending on the timestamp resolution, multiple rows may be tagged which normally works fine but can cause confusion and some issues.
        //We cannot use orderexecuted alone because it is only guaranteed to be incrementing per update call.
        //TODO: Better handle other databases to use dateexecuted desc, orderexecuted desc.
        UpdateStatement updateStatement = new UpdateStatement(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName()).addNewColumnValue("TAG", statement.getTag()).setWhereClause(dateColumnNameEscaped + " = (" + "SELECT MAX(" + dateColumnNameEscaped + ") " + "FROM " + tableNameEscaped + ")");
        return SqlGeneratorFactory.getInstance().generateSql(updateStatement, database);
    }
}

Example 32 with DatabaseException

use of liquibase.exception.DatabaseException in project liquibase by liquibase.

the class OfflineDatabaseTest method canOutputSQLFromOfflineOracleDB.

/**
	 * Check if it it's possible to output SQL from an OfflineConnection
	 * set to Oracle (offline:oracle).
	 *
	 * @see <a href="https://liquibase.jira.com/browse/CORE-2192">CORE-2192</a>
	 */
@Test
public void canOutputSQLFromOfflineOracleDB() throws Exception {
    AddColumnChange change = new AddColumnChange();
    AddColumnConfig column1 = new AddColumnConfig();
    column1.setName("column1");
    column1.setType("INT");
    change.addColumn(column1);
    AddColumnConfig column2 = new AddColumnConfig();
    column2.setName("column2");
    column2.setType("INT");
    change.addColumn(column2);
    SqlStatement[] statements = new SqlStatement[0];
    try {
        statements = change.generateStatements(createOfflineDatabase("offline:oracle"));
    } catch (DatabaseException e) {
        Assert.fail("Can't generate statements from an Offline Oracle database.");
    }
    Assert.assertEquals(1, statements.length);
    Assert.assertTrue(statements[0] instanceof AddColumnStatement);
    AddColumnStatement stmt = (AddColumnStatement) statements[0];
    Assert.assertTrue(stmt.isMultiple());
    Assert.assertEquals(2, stmt.getColumns().size());
}

Example 33 with DatabaseException

use of liquibase.exception.DatabaseException in project liquibase by liquibase.

the class DatabaseTestContext method openConnection.

private DatabaseConnection openConnection(final String url) throws Exception {
    if (connectionsAttempted.containsKey(url)) {
        JdbcConnection connection = (JdbcConnection) connectionsByUrl.get(url);
        if (connection == null) {
            return null;
        } else if (connection.getUnderlyingConnection().isClosed()) {
            connectionsByUrl.put(url, openDatabaseConnection(url));
        }
        return connectionsByUrl.get(url);
    }
    connectionsAttempted.put(url, Boolean.TRUE);
    if (System.getProperty(TEST_DATABASES_PROPERTY) != null) {
        boolean shouldTest = false;
        String[] databasesToTest = System.getProperty(TEST_DATABASES_PROPERTY).split("\\s*,\\s*");
        for (String database : databasesToTest) {
            if (url.indexOf(database) >= 0) {
                shouldTest = true;
            }
        }
        if (!shouldTest) {
            System.out.println("test.databases system property forbids testing against " + url);
            return null;
        } else {
            System.out.println("Will be tested against " + url);
        }
    }
    DatabaseConnection connection = openDatabaseConnection(url);
    if (connection == null) {
        return null;
    }
    Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(connection);
    final DatabaseConnection databaseConnection = database.getConnection();
    if (databaseConnection.getAutoCommit()) {
        databaseConnection.setAutoCommit(false);
    }
    try {
        if (url.startsWith("jdbc:hsql")) {
            ((JdbcConnection) databaseConnection).getUnderlyingConnection().createStatement().execute("CREATE SCHEMA " + ALT_SCHEMA + " AUTHORIZATION DBA");
        } else if (url.startsWith("jdbc:sqlserver") || url.startsWith("jdbc:postgresql") || url.startsWith("jdbc:h2")) {
            ((JdbcConnection) databaseConnection).getUnderlyingConnection().createStatement().execute("CREATE SCHEMA " + ALT_SCHEMA);
        }
        if (!databaseConnection.getAutoCommit()) {
            databaseConnection.commit();
        }
    } catch (SQLException e) {
        //schema already exists
        ;
    } finally {
        try {
            databaseConnection.rollback();
        } catch (DatabaseException e) {
            if (database instanceof DB2Database) {
            //                    expected, there is a problem with it
            } else {
                throw e;
            }
        }
    }
    connectionsByUrl.put(url, databaseConnection);
    Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {

        @Override
        public void run() {
            try {
                try {
                    if (!((JdbcConnection) databaseConnection).getUnderlyingConnection().getAutoCommit()) {
                        ((JdbcConnection) databaseConnection).getUnderlyingConnection().rollback();
                    }
                } catch (SQLException e) {
                    ;
                }
                ((JdbcConnection) databaseConnection).getUnderlyingConnection().close();
            } catch (SQLException e) {
                System.out.println("Could not close " + url);
                e.printStackTrace();
            }
        }
    }));
    return databaseConnection;
}

Example 34 with DatabaseException

use of liquibase.exception.DatabaseException in project liquibase by liquibase.

the class LockServiceExecuteTest method fixupLockTables.

private void fixupLockTables() throws DatabaseException, LockException {
    for (Database database : TestContext.getInstance().getAllDatabases()) {
        if (database.getConnection() != null) {
            Statement statement = null;
            try {
                statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
                try {
                    statement.execute("drop table " + database.escapeTableName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName()));
                } catch (Exception e) {
                //ok
                }
                try {
                    statement.execute("drop table " + database.escapeTableName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogLockTableName()));
                } catch (Exception e) {
                //ok
                }
                statement.close();
                database.commit();
            } catch (SQLException e) {
                throw new DatabaseException(e);
            }
        }
    }
}

Example 35 with DatabaseException

use of liquibase.exception.DatabaseException in project liquibase by liquibase.

the class AbstractIntegrationTest method clearDatabase.

protected void clearDatabase(Liquibase liquibase) throws DatabaseException {
    liquibase.dropAll(getSchemasToDrop());
    try {
        Statement statement = null;
        try {
            statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
            statement.execute("drop table " + database.escapeTableName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName()));
            database.commit();
        } catch (Exception e) {
            System.out.println("Probably expected error dropping databasechangelog table");
            e.printStackTrace();
            database.rollback();
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
        try {
            statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
            statement.execute("drop table " + database.escapeTableName(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogLockTableName()));
            database.commit();
        } catch (Exception e) {
            System.out.println("Probably expected error dropping databasechangeloglock table");
            e.printStackTrace();
            database.rollback();
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    } catch (SQLException e) {
        throw new DatabaseException(e);
    }
    SnapshotGeneratorFactory.resetAll();
    DatabaseFactory.reset();
}