From e246dcd0446e9c1aac28402dccb8bdfca7288445 Mon Sep 17 00:00:00 2001 From: David Gageot Date: Mon, 16 Jul 2012 13:01:45 +0200 Subject: [PATCH] Fix database tests to make them behave the same on every database regarding sequences --- .../core/persistence/AbstractDaoTestCase.java | 15 +- .../core/persistence/DatabaseCommands.java | 151 +++++++----------- .../jpa/test/AbstractDbUnitTestCase.java | 15 +- 3 files changed, 76 insertions(+), 105 deletions(-) diff --git a/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java b/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java index 0e56a0f3f72..98bf90b68bc 100644 --- a/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java +++ b/sonar-core/src/test/java/org/sonar/core/persistence/AbstractDaoTestCase.java @@ -19,6 +19,9 @@ */ package org.sonar.core.persistence; +import org.dbunit.ext.mssql.InsertIdentityOperation; +import org.dbunit.operation.DatabaseOperation; + import com.google.common.collect.Maps; import com.google.common.io.Closeables; import org.apache.commons.io.IOUtils; @@ -68,7 +71,7 @@ public abstract class AbstractDaoTestCase { myBatis.start(); } - databaseCommands.truncateDatabase(database.getDataSource().getConnection()); + databaseCommands.truncateDatabase(database.getDataSource()); } protected MyBatis getMyBatis() { @@ -88,7 +91,9 @@ public abstract class AbstractDaoTestCase { } setupData(streams); - + databaseCommands.resetPrimaryKeys(database.getDataSource()); + } catch (SQLException e) { + throw translateException("Could not setup DBUnit data", e); } finally { for (InputStream stream : streams) { IOUtils.closeQuietly(stream); @@ -107,7 +112,7 @@ public abstract class AbstractDaoTestCase { connection = createConnection(); - databaseCommands.getDbunitDatabaseOperation().execute(connection, databaseTester.getDataSet()); + new InsertIdentityOperation(DatabaseOperation.INSERT).execute(connection, databaseTester.getDataSet()); } catch (Exception e) { throw translateException("Could not setup DBUnit data", e); } finally { @@ -215,8 +220,8 @@ public abstract class AbstractDaoTestCase { try { ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(stream)); dataSet.addReplacementObject("[null]", null); - dataSet.addReplacementObject("[false]", databaseCommands.getFalse()); - dataSet.addReplacementObject("[true]", databaseCommands.getTrue()); + dataSet.addReplacementObject("[false]", Boolean.FALSE); + dataSet.addReplacementObject("[true]", Boolean.TRUE); return dataSet; } catch (Exception e) { throw translateException("Could not read the dataset stream", e); diff --git a/sonar-core/src/test/java/org/sonar/core/persistence/DatabaseCommands.java b/sonar-core/src/test/java/org/sonar/core/persistence/DatabaseCommands.java index 1b787aa1bae..0e9f726039c 100644 --- a/sonar-core/src/test/java/org/sonar/core/persistence/DatabaseCommands.java +++ b/sonar-core/src/test/java/org/sonar/core/persistence/DatabaseCommands.java @@ -19,22 +19,25 @@ */ package org.sonar.core.persistence; +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableMap; import org.apache.commons.lang.StringUtils; import org.dbunit.dataset.datatype.IDataTypeFactory; import org.dbunit.ext.h2.H2DataTypeFactory; -import org.dbunit.ext.mssql.InsertIdentityOperation; import org.dbunit.ext.mssql.MsSqlDataTypeFactory; import org.dbunit.ext.mysql.MySqlDataTypeFactory; import org.dbunit.ext.oracle.Oracle10DataTypeFactory; import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory; -import org.dbunit.operation.DatabaseOperation; import org.sonar.core.persistence.dialect.Dialect; import org.sonar.core.persistence.dialect.MsSql; import org.sonar.core.persistence.dialect.MySql; import org.sonar.core.persistence.dialect.Oracle; import org.sonar.core.persistence.dialect.PostgreSql; +import javax.sql.DataSource; + import java.sql.Connection; +import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Arrays; @@ -47,147 +50,105 @@ public abstract class DatabaseCommands { this.dbUnitFactory = dbUnitFactory; } - public abstract String truncate(String table); - - public abstract List resetPrimaryKey(String table); - - public Object getTrue() { - return Boolean.TRUE; - } - - public Object getFalse() { - return Boolean.FALSE; - } - - public IDataTypeFactory getDbUnitFactory() { + public final IDataTypeFactory getDbUnitFactory() { return dbUnitFactory; } - public DatabaseOperation getDbunitDatabaseOperation() { - return new InsertIdentityOperation(DatabaseOperation.INSERT); - } - - static final DatabaseCommands H2 = new DatabaseCommands(new H2DataTypeFactory()) { - @Override - public String truncate(String table) { - return "TRUNCATE TABLE " + table; - } + abstract List resetPrimaryKey(String table, int minSequenceValue); - @Override - public List resetPrimaryKey(String table) { - return Arrays.asList("ALTER TABLE " + table + " ALTER COLUMN ID RESTART WITH 1"); - } - }; - - static final DatabaseCommands MSSQL = new DatabaseCommands(new MsSqlDataTypeFactory()) { - @Override - public String truncate(String table) { - return "TRUNCATE TABLE " + table; - } - - @Override - public List resetPrimaryKey(String table) { - return Arrays.asList("DBCC CHECKIDENT('" + table + "', RESEED, 1)"); - } + public static DatabaseCommands forDialect(Dialect dialect) { + DatabaseCommands command = ImmutableMap.of( + org.sonar.core.persistence.dialect.H2.ID, H2, + MsSql.ID, MSSQL, + MySql.ID, MYSQL, + Oracle.ID, ORACLE, + PostgreSql.ID, POSTGRESQL).get(dialect.getId()); + + return Preconditions.checkNotNull(command, "Unknown database: " + dialect); + } + private static final DatabaseCommands H2 = new DatabaseCommands(new H2DataTypeFactory()) { @Override - public DatabaseOperation getDbunitDatabaseOperation() { - return new InsertIdentityOperation(DatabaseOperation.CLEAN_INSERT); + List resetPrimaryKey(String table, int minSequenceValue) { + return Arrays.asList("ALTER TABLE " + table + " ALTER COLUMN ID RESTART WITH " + minSequenceValue); } }; - static final DatabaseCommands MYSQL = new DatabaseCommands(new MySqlDataTypeFactory()) { - @Override - public String truncate(String table) { - return "TRUNCATE TABLE " + table; - } - + private static final DatabaseCommands POSTGRESQL = new DatabaseCommands(new PostgresqlDataTypeFactory()) { @Override - public List resetPrimaryKey(String table) { - return Arrays.asList("ALTER TABLE " + table + " AUTO_INCREMENT = 1"); + List resetPrimaryKey(String table, int minSequenceValue) { + return Arrays.asList("ALTER SEQUENCE " + table + "_id_seq RESTART WITH " + minSequenceValue); } }; - static final DatabaseCommands ORACLE = new DatabaseCommands(new Oracle10DataTypeFactory()) { - @Override - public String truncate(String table) { - return "TRUNCATE TABLE " + table; - } - + private static final DatabaseCommands ORACLE = new DatabaseCommands(new Oracle10DataTypeFactory()) { @Override - public List resetPrimaryKey(String table) { + List resetPrimaryKey(String table, int minSequenceValue) { String sequence = StringUtils.upperCase(table) + "_SEQ"; return Arrays.asList( "DROP SEQUENCE " + sequence, - "CREATE SEQUENCE " + sequence + " INCREMENT BY 1 MINVALUE 1 START WITH 1" - ); + "CREATE SEQUENCE " + sequence + " INCREMENT BY 1 MINVALUE 1 START WITH " + minSequenceValue); } + }; + private static final DatabaseCommands MSSQL = new DatabaseCommands(new MsSqlDataTypeFactory()) { @Override - public Object getTrue() { - return 1; + public void resetPrimaryKeys(DataSource dataSource) { } @Override - public Object getFalse() { - return 0; + List resetPrimaryKey(String table, int minSequenceValue) { + return null; } }; - static final DatabaseCommands POSTGRESQL = new DatabaseCommands(new PostgresqlDataTypeFactory()) { + private static final DatabaseCommands MYSQL = new DatabaseCommands(new MySqlDataTypeFactory()) { @Override - public String truncate(String table) { - return "TRUNCATE TABLE " + table; + public void resetPrimaryKeys(DataSource dataSource) { } @Override - public List resetPrimaryKey(String table) { - return Arrays.asList("ALTER SEQUENCE " + table + "_id_seq RESTART WITH 1"); + List resetPrimaryKey(String table, int minSequenceValue) { + return null; } }; - public static DatabaseCommands forDialect(Dialect dialect) { - if (org.sonar.core.persistence.dialect.H2.ID.equals(dialect.getId())) { - return H2; - } - if (MsSql.ID.equals(dialect.getId())) { - return MSSQL; - } - if (MySql.ID.equals(dialect.getId())) { - return MYSQL; - } - if (Oracle.ID.equals(dialect.getId())) { - return ORACLE; - } - if (PostgreSql.ID.equals(dialect.getId())) { - return POSTGRESQL; + public void truncateDatabase(DataSource dataSource) throws SQLException { + Connection connection = dataSource.getConnection(); + connection.setAutoCommit(false); + + Statement statement = connection.createStatement(); + for (String table : DatabaseUtils.TABLE_NAMES) { + statement.executeUpdate("TRUNCATE TABLE " + table); + connection.commit(); } - throw new IllegalArgumentException("Unknown database: " + dialect); + + statement.close(); + connection.close(); } - public void truncateDatabase(Connection connection) throws SQLException { + public void resetPrimaryKeys(DataSource dataSource) throws SQLException { + Connection connection = dataSource.getConnection(); connection.setAutoCommit(false); Statement statement = connection.createStatement(); for (String table : DatabaseUtils.TABLE_NAMES) { - // 1. truncate - String truncateCommand = truncate(table); - statement.executeUpdate(truncateCommand); - connection.commit(); - - // 2. reset primary keys try { - for (String resetCommand : resetPrimaryKey(table)) { + ResultSet result = statement.executeQuery("SELECT CASE WHEN MAX(ID) IS NULL THEN 1 ELSE MAX(ID)+1 END FROM " + table); + result.next(); + int maxId = result.getInt(1); + result.close(); + + for (String resetCommand : resetPrimaryKey(table, maxId)) { statement.executeUpdate(resetCommand); } connection.commit(); } catch (Exception e) { - // this table has no primary key - connection.rollback(); + connection.rollback(); // this table has no primary key } } + statement.close(); - connection.commit(); connection.close(); } } diff --git a/sonar-core/src/test/java/org/sonar/jpa/test/AbstractDbUnitTestCase.java b/sonar-core/src/test/java/org/sonar/jpa/test/AbstractDbUnitTestCase.java index 9742e0174d8..53a1017c50f 100644 --- a/sonar-core/src/test/java/org/sonar/jpa/test/AbstractDbUnitTestCase.java +++ b/sonar-core/src/test/java/org/sonar/jpa/test/AbstractDbUnitTestCase.java @@ -19,6 +19,9 @@ */ package org.sonar.jpa.test; +import org.dbunit.ext.mssql.InsertIdentityOperation; +import org.dbunit.operation.DatabaseOperation; + import org.apache.commons.io.IOUtils; import org.dbunit.Assertion; import org.dbunit.DataSourceDatabaseTester; @@ -72,7 +75,7 @@ public abstract class AbstractDbUnitTestCase { session.start(); } - databaseCommands.truncateDatabase(database.getDataSource().getConnection()); + databaseCommands.truncateDatabase(database.getDataSource()); } @After @@ -110,7 +113,9 @@ public abstract class AbstractDbUnitTestCase { } setupData(streams); - + databaseCommands.resetPrimaryKeys(database.getDataSource()); + } catch (SQLException e) { + throw translateException("Could not setup DBUnit data", e); } finally { for (InputStream stream : streams) { IOUtils.closeQuietly(stream); @@ -129,7 +134,7 @@ public abstract class AbstractDbUnitTestCase { connection = createConnection(); - databaseCommands.getDbunitDatabaseOperation().execute(connection, databaseTester.getDataSet()); + new InsertIdentityOperation(DatabaseOperation.INSERT).execute(connection, databaseTester.getDataSet()); } catch (Exception e) { throw translateException("Could not setup DBUnit data", e); } finally { @@ -217,8 +222,8 @@ public abstract class AbstractDbUnitTestCase { try { ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(stream)); dataSet.addReplacementObject("[null]", null); - dataSet.addReplacementObject("[false]", databaseCommands.getFalse()); - dataSet.addReplacementObject("[true]", databaseCommands.getTrue()); + dataSet.addReplacementObject("[false]", Boolean.FALSE); + dataSet.addReplacementObject("[true]", Boolean.TRUE); return dataSet; } catch (Exception e) { throw translateException("Could not read the dataset stream", e); -- 2.39.5