/*
* Copyright 2010-2017 Boxfuse GmbH
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.flywaydb.core.internal.dbsupport.postgresql;
import org.flywaydb.core.DbCategory;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.FlywayException;
import org.flywaydb.core.internal.util.jdbc.DriverDataSource;
import org.flywaydb.core.internal.util.jdbc.JdbcUtils;
import org.flywaydb.core.migration.MigrationTestCase;
import org.junit.Ignore;
import org.junit.Test;
import org.junit.experimental.categories.Category;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import static org.hamcrest.CoreMatchers.containsString;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertThat;
/**
* Test to demonstrate the migration functionality using PostgreSQL.
*/
@SuppressWarnings({"JavaDoc"})
@Category(DbCategory.PostgreSQL.class)
public class PostgreSQLMigrationMediumTest extends MigrationTestCase {
@Override
protected DataSource createDataSource(Properties customProperties) {
String user = customProperties.getProperty("postgresql.user", "flyway");
String password = customProperties.getProperty("postgresql.password", "flyway");
String url = customProperties.getProperty("postgresql.url", "jdbc:postgresql://localhost/flyway_db");
return new DriverDataSource(Thread.currentThread().getContextClassLoader(), null, url, user, password, null);
}
@Override
protected String getQuoteLocation() {
return "migration/quote";
}
/**
* Tests clean and migrate for PostgreSQL Types.
*/
@Test
public void type() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/type");
flyway.migrate();
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
// Clean again, to prevent tests with non superuser rights to fail.
flyway.clean();
}
@Test
public void vacuum() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/vacuum");
try {
flyway.migrate();
} catch (FlywayException e) {
assertThat(e.getMessage(), containsString("non-transactional"));
}
flyway.setAllowMixedMigrations(true);
flyway.migrate();
}
@Test
public void index() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/index");
flyway.setAllowMixedMigrations(true);
flyway.migrate();
}
@Test
public void cleanUnknown() throws Exception {
flyway.setSchemas("non-existant");
flyway.clean();
}
/**
* Tests clean and migrate for PostgreSQL Stored Procedures.
*/
@Test
public void storedProcedure() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/procedure");
flyway.migrate();
assertEquals("Hello", jdbcTemplate.queryForString("SELECT value FROM test_data"));
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL Functions.
*/
@Test
public void function() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/function");
flyway.migrate();
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL Triggers.
*/
@Test
public void trigger() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/trigger");
flyway.migrate();
assertEquals(10, jdbcTemplate.queryForInt("SELECT count(*) FROM test4"));
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL Views.
*/
@Test
public void view() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/view");
flyway.migrate();
assertEquals(150, jdbcTemplate.queryForInt("SELECT value FROM \"\"\"v\"\"\""));
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL Materialized Views.
*/
@Ignore("PostgreSQL 9.3 and newer only")
@Test
public void materializedview() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/materializedview");
flyway.migrate();
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL child tables.
*/
@Test
public void inheritance() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/inheritance");
flyway.migrate();
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL Domains.
*/
@Test
public void domain() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/domain");
flyway.migrate();
assertEquals("foo", jdbcTemplate.queryForString("SELECT x FROM t"));
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL Enums.
*/
@Test
public void enumeration() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/enum");
flyway.migrate();
assertEquals("positive", jdbcTemplate.queryForString("SELECT x FROM t"));
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests clean and migrate for PostgreSQL Aggregates.
*/
@Test
public void aggregate() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/aggregate");
flyway.migrate();
flyway.clean();
// Running migrate again on an unclean database, triggers duplicate object exceptions.
flyway.migrate();
}
/**
* Tests parsing support for $$ string literals.
*/
@Test
public void dollarQuote() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/dollar");
flyway.migrate();
assertEquals(9, jdbcTemplate.queryForInt("select count(*) from dollar"));
}
/**
* Tests parsing support for multiline string literals.
*/
@Test
public void multiLine() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/multiline");
flyway.migrate();
assertEquals(1, jdbcTemplate.queryForInt("select count(*) from address"));
}
/**
* Tests support for COPY FROM STDIN statements generated by pg_dump..
*/
@Test
public void copy() throws Exception {
flyway.setLocations("migration/dbsupport/postgresql/sql/copy");
flyway.migrate();
assertEquals(6, jdbcTemplate.queryForInt("select count(*) from copy_test"));
}
/**
* Tests that the lock on SCHEMA_VERSION is not blocking SQL commands in migrations. This test won't fail if there's
* a too restrictive lock - it would just hang endlessly.
*/
@Test
public void lock() {
flyway.setLocations("migration/dbsupport/postgresql/sql/lock");
flyway.migrate();
}
@Test
public void emptySearchPath() {
Flyway flyway1 = new Flyway();
DriverDataSource driverDataSource = (DriverDataSource) dataSource;
flyway1.setDataSource(new DriverDataSource(Thread.currentThread().getContextClassLoader(),
null, driverDataSource.getUrl(), driverDataSource.getUser(), driverDataSource.getPassword(), null) {
@Override
public Connection getConnection() throws SQLException {
Connection connection = super.getConnection();
Statement statement = null;
try {
statement = connection.createStatement();
statement.execute("SELECT set_config('search_path', '', false)");
} finally {
JdbcUtils.closeStatement(statement);
}
return connection;
}
});
flyway1.setLocations(getBasedir());
flyway1.setSchemas("public");
flyway1.migrate();
}
@Test(expected = FlywayException.class)
public void warning() {
flyway.setLocations("migration/dbsupport/postgresql/sql/warning");
flyway.migrate();
// Log should contain "This is a warning"
}
@Override
protected void createFlyway3MetadataTable() throws Exception {
jdbcTemplate.execute("CREATE TABLE \"schema_version\" (\n" +
" \"version_rank\" INT NOT NULL,\n" +
" \"installed_rank\" INT NOT NULL,\n" +
" \"version\" VARCHAR(50) NOT NULL,\n" +
" \"description\" VARCHAR(200) NOT NULL,\n" +
" \"type\" VARCHAR(20) NOT NULL,\n" +
" \"script\" VARCHAR(1000) NOT NULL,\n" +
" \"checksum\" INTEGER,\n" +
" \"installed_by\" VARCHAR(100) NOT NULL,\n" +
" \"installed_on\" TIMESTAMP NOT NULL DEFAULT now(),\n" +
" \"execution_time\" INTEGER NOT NULL,\n" +
" \"success\" BOOLEAN NOT NULL\n" +
") WITH (\n" +
" OIDS=FALSE\n" +
")");
jdbcTemplate.execute("ALTER TABLE \"schema_version\" ADD CONSTRAINT \"schema_version_pk\" PRIMARY KEY (\"version\")");
jdbcTemplate.execute("CREATE INDEX \"schema_version_vr_idx\" ON \"schema_version\" (\"version_rank\")");
jdbcTemplate.execute("CREATE INDEX \"schema_version_ir_idx\" ON \"schema_version\" (\"installed_rank\")");
jdbcTemplate.execute("CREATE INDEX \"schema_version_s_idx\" ON \"schema_version\" (\"success\")");
}
}