MyBatis: prefix SQL requests with schema, when available.

Add support for integration tests, hosted at SonarSource.

20 files changed, 509 insertions, 16 deletions

diff --git a/sonar-core/pom.xml b/sonar-core/pom.xml
index aae6c8d74f5..dba6c874164 100644
--- a/sonar-core/pom.xml
+++ b/sonar-core/pom.xml
@@ -138,7 +138,10 @@
       <scope>test</scope>
     </dependency>
 
-    <!-- JDBC drivers for MyBatis integration tests -->
+    <!--
+         JDBC drivers for MyBatis integration tests.
+         They can't be moved to the profile run-mybatis-its because 
+    -->
     <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
@@ -155,4 +158,34 @@
       <scope>test</scope>
     </dependency>
   </dependencies>
+
+  <profiles>
+    <profile>
+      <!-- Integration Tests are not open-source -->
+      <!-- This profile is for internal use at SonarSource -->
+      <id>run-mybatis-its</id>
+      <build>
+        <plugins>
+          <plugin>
+            <groupId>org.apache.maven.plugins</groupId>
+            <artifactId>maven-surefire-plugin</artifactId>
+            <configuration>
+              <includes>
+                <include>org/sonar/persistence/dao/*Test.java</include>
+              </includes>
+            </configuration>
+          </plugin>
+        </plugins>
+      </build>
+      <dependencies>
+        <dependency>
+          <!-- this artifact is located in the SonarSource internal repository -->
+          <groupId>com.oracle</groupId>
+          <artifactId>ojdbc5</artifactId>
+          <version>11.2.0.2.0</version>
+          <scope>test</scope>
+        </dependency>
+      </dependencies>
+    </profile>
+  </profiles>
 </project>
diff --git a/sonar-core/src/main/java/org/sonar/jpa/dialect/Derby.java b/sonar-core/src/main/java/org/sonar/jpa/dialect/Derby.java
index 04a71997393..00ac69bcbd2 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/dialect/Derby.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/dialect/Derby.java
@@ -31,8 +31,10 @@ import java.sql.Types;
  */
 public class Derby implements Dialect {
 
+  public static final String ID = "derby";
+
   public String getId() {
-    return "derby";
+    return ID;
   }
 
   public String getActiveRecordDialectCode() {
diff --git a/sonar-core/src/main/java/org/sonar/jpa/dialect/MsSql.java b/sonar-core/src/main/java/org/sonar/jpa/dialect/MsSql.java
index bd00d293c0c..2648d1a615c 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/dialect/MsSql.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/dialect/MsSql.java
@@ -28,8 +28,10 @@ import java.sql.Types;
 
 public class MsSql implements Dialect {
 
+  public static final String ID = "mssql";
+
   public String getId() {
-    return "mssql";
+    return ID;
   }
 
   public String getActiveRecordDialectCode() {
diff --git a/sonar-core/src/main/java/org/sonar/jpa/dialect/MySql.java b/sonar-core/src/main/java/org/sonar/jpa/dialect/MySql.java
index 142fa9fbff8..666d4583752 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/dialect/MySql.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/dialect/MySql.java
@@ -30,8 +30,10 @@ import java.sql.Types;
  */
 public class MySql implements Dialect {
 
+  public static final String ID = "mysql";
+
   public String getId() {
-    return "mysql";
+    return ID;
   }
 
   public String getActiveRecordDialectCode() {
diff --git a/sonar-core/src/main/java/org/sonar/jpa/dialect/Oracle.java b/sonar-core/src/main/java/org/sonar/jpa/dialect/Oracle.java
index a0417fb3721..78d401444dc 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/dialect/Oracle.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/dialect/Oracle.java
@@ -30,8 +30,10 @@ import java.sql.Types;
  */
 public class Oracle implements Dialect {
 
+  public static final String ID = "oracle";
+
   public String getId() {
-    return "oracle";
+    return ID;
   }
 
   public String getActiveRecordDialectCode() {
diff --git a/sonar-core/src/main/java/org/sonar/jpa/dialect/PostgreSql.java b/sonar-core/src/main/java/org/sonar/jpa/dialect/PostgreSql.java
index 7f214a4aef0..59d0bf93f8b 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/dialect/PostgreSql.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/dialect/PostgreSql.java
@@ -29,8 +29,10 @@ import java.sql.Types;
  */
 public class PostgreSql implements Dialect {
 
+  public static final String ID = "postgresql";
+
   public String getId() {
-    return "postgresql";
+    return ID;
   }
 
   public String getActiveRecordDialectCode() {
diff --git a/sonar-core/src/main/java/org/sonar/persistence/Database.java b/sonar-core/src/main/java/org/sonar/persistence/Database.java
index 784edb4cec0..1f030afb3ab 100644
--- a/sonar-core/src/main/java/org/sonar/persistence/Database.java
+++ b/sonar-core/src/main/java/org/sonar/persistence/Database.java
@@ -31,7 +31,21 @@ import java.util.Properties;
 public interface Database {
   Database start();
   Database stop();
+
+  /**
+   * Returns the configured datasource. Null as long as start() is not executed.
+   */
   DataSource getDataSource();
+
+  /**
+   * @return the dialect or null if start() has not been executed
+   */
   Dialect getDialect();
+
+  /**
+   * @return the schema or null if not defined or if start() has not been executed
+   */
+  String getSchema();
+  
   Properties getHibernateProperties();
 }
diff --git a/sonar-core/src/main/java/org/sonar/persistence/DefaultDatabase.java b/sonar-core/src/main/java/org/sonar/persistence/DefaultDatabase.java
index 2b36ad4fc8c..0eb87719c26 100644
--- a/sonar-core/src/main/java/org/sonar/persistence/DefaultDatabase.java
+++ b/sonar-core/src/main/java/org/sonar/persistence/DefaultDatabase.java
@@ -29,6 +29,8 @@ import org.sonar.api.config.Settings;
 import org.sonar.api.database.DatabaseProperties;
 import org.sonar.jpa.dialect.Dialect;
 import org.sonar.jpa.dialect.DialectRepository;
+import org.sonar.jpa.dialect.Oracle;
+import org.sonar.jpa.dialect.PostgreSql;
 import org.sonar.jpa.session.CustomHibernateConnectionProvider;
 
 import javax.sql.DataSource;
@@ -47,6 +49,7 @@ public class DefaultDatabase implements Database {
   private Settings settings;
   private BasicDataSource datasource;
   private Dialect dialect;
+  private String schema;
 
   public DefaultDatabase(Settings settings) {
     this.settings = settings;
@@ -58,6 +61,7 @@ public class DefaultDatabase implements Database {
 
       Properties properties = getProperties();
       dialect = initDialect(properties);
+      schema = initSchema(properties, dialect);
       datasource = initDatasource(properties);
       return this;
 
@@ -66,6 +70,16 @@ public class DefaultDatabase implements Database {
     }
   }
 
+  private String initSchema(Properties properties, Dialect dialect) {
+    if (dialect.getId().equals(PostgreSql.ID)) {
+      return properties.getProperty("sonar.jdbc.postgreSearchPath", "public");
+    }
+    if (dialect.getId().equals(Oracle.ID)) {
+      return properties.getProperty("sonar.hibernate.default_schema", "sonar");
+    }
+    return null;
+  }
+
   BasicDataSource initDatasource(Properties properties) throws Exception {
     LOG.info("Create JDBC datasource");
     return (BasicDataSource) BasicDataSourceFactory.createDataSource(extractCommonsDbcpProperties(properties));
@@ -102,6 +116,10 @@ public class DefaultDatabase implements Database {
     return dialect;
   }
 
+  public String getSchema() {
+    return schema;
+  }
+
   public Properties getHibernateProperties() {
     Properties props = new Properties();
 
diff --git a/sonar-core/src/main/java/org/sonar/persistence/MyBatis.java b/sonar-core/src/main/java/org/sonar/persistence/MyBatis.java
index 040481e7b2a..6ba20d19236 100644
--- a/sonar-core/src/main/java/org/sonar/persistence/MyBatis.java
+++ b/sonar-core/src/main/java/org/sonar/persistence/MyBatis.java
@@ -49,6 +49,7 @@ public class MyBatis implements BatchComponent, ServerComponent {
     conf.setEnvironment(new Environment("production", createTransactionFactory(), database.getDataSource()));
     conf.setUseGeneratedKeys(true);
     conf.setLazyLoadingEnabled(false);
+    loadSchemaVariable(conf);
 
     loadAlias(conf, "DuplicationUnit", DuplicationUnit.class);
     loadAlias(conf, "Rule", Rule.class);
@@ -59,6 +60,15 @@ public class MyBatis implements BatchComponent, ServerComponent {
     return this;
   }
 
+  private void loadSchemaVariable(Configuration conf) {
+    String schema = database.getSchema();
+    if (StringUtils.isNotBlank(schema)) {
+      conf.getVariables().setProperty("_schema", schema + ".");
+    } else {
+      conf.getVariables().setProperty("_schema", "");
+    }
+  }
+
   public SqlSessionFactory getSessionFactory() {
     return sessionFactory;
   }
diff --git a/sonar-core/src/main/java/org/sonar/persistence/dao/DuplicationDao.java b/sonar-core/src/main/java/org/sonar/persistence/dao/DuplicationDao.java
index da308dc4643..149ca16b600 100644
--- a/sonar-core/src/main/java/org/sonar/persistence/dao/DuplicationDao.java
+++ b/sonar-core/src/main/java/org/sonar/persistence/dao/DuplicationDao.java
@@ -19,9 +19,12 @@
  */
 package org.sonar.persistence.dao;
 
+import java.sql.BatchUpdateException;
 import java.util.Collection;
 import java.util.List;
 
+import org.apache.ibatis.exceptions.PersistenceException;
+import org.apache.ibatis.executor.BatchExecutorException;
 import org.apache.ibatis.session.ExecutorType;
 import org.apache.ibatis.session.SqlSession;
 import org.sonar.api.BatchComponent;
@@ -60,6 +63,7 @@ public class DuplicationDao implements BatchComponent, ServerComponent {
         mapper.batchInsert(unit);
       }
       session.commit();
+      
     } finally {
       session.close();
     }
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-mssql.xml b/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-mssql.xml
index bc1837a35fa..fb495eeb7e3 100644
--- a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-mssql.xml
+++ b/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-mssql.xml
@@ -5,7 +5,7 @@
 
   <select id="selectCandidates" parameterType="map" resultType="DuplicationUnit">
     SELECT DISTINCT to_blocks.hash hash, res.kee resourceKey, to_blocks.index_in_file indexInFile, to_blocks.start_line startLine, to_blocks.end_line endLine
-    FROM duplications_index to_blocks, duplications_index from_blocks, snapshots snapshot, projects res
+    FROM ${_schema}duplications_index to_blocks, ${_schema}duplications_index from_blocks, ${_schema}snapshots snapshot, ${_schema}projects res
     WHERE from_blocks.snapshot_id = #{resource_snapshot_id}
     AND to_blocks.hash = from_blocks.hash
     AND to_blocks.snapshot_id = snapshot.id
@@ -17,7 +17,7 @@
   </select>
 
   <insert id="batchInsert" parameterType="DuplicationUnit" keyColumn="id" useGeneratedKeys="false">
-    INSERT INTO duplications_index (snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line)
+    INSERT INTO ${_schema}duplications_index (snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line)
     VALUES (#{snapshotId}, #{projectSnapshotId}, #{hash}, #{indexInFile}, #{startLine}, #{endLine})
   </insert>
 
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-oracle.xml b/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-oracle.xml
index 92a6bedc2fe..ff06ab3f95e 100644
--- a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-oracle.xml
+++ b/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-oracle.xml
@@ -5,7 +5,7 @@
 
   <select id="selectCandidates" parameterType="map" resultType="DuplicationUnit">
     SELECT DISTINCT to_blocks.hash hash, res.kee resourceKey, to_blocks.index_in_file indexInFile, to_blocks.start_line startLine, to_blocks.end_line endLine
-    FROM duplications_index to_blocks, duplications_index from_blocks, snapshots snapshot, projects res
+    FROM ${_schema}duplications_index to_blocks, ${_schema}duplications_index from_blocks, ${_schema}snapshots snapshot, ${_schema}projects res
     WHERE from_blocks.snapshot_id = #{resource_snapshot_id}
     AND to_blocks.hash = from_blocks.hash
     AND to_blocks.snapshot_id = snapshot.id
@@ -17,7 +17,7 @@
   </select>
 
   <insert id="batchInsert" parameterType="DuplicationUnit" keyColumn="id" useGeneratedKeys="false">
-    INSERT INTO duplications_index (id, snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line)
+    INSERT INTO ${_schema}duplications_index (id, snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line)
     VALUES (duplications_index_seq.NEXTVAL, #{snapshotId}, #{projectSnapshotId}, #{hash}, #{indexInFile}, #{startLine}, #{endLine})
   </insert>
 
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper.xml b/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper.xml
index 5f39d7dea7c..b6a6bcee564 100644
--- a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper.xml
@@ -5,7 +5,7 @@
 
   <select id="selectCandidates" parameterType="map" resultType="DuplicationUnit">
     SELECT DISTINCT to_blocks.hash hash, res.kee resourceKey, to_blocks.index_in_file indexInFile, to_blocks.start_line startLine, to_blocks.end_line endLine
-    FROM duplications_index to_blocks, duplications_index from_blocks, snapshots snapshot, projects res
+    FROM ${_schema}duplications_index to_blocks, ${_schema}duplications_index from_blocks, ${_schema}snapshots snapshot, ${_schema}projects res
     WHERE from_blocks.snapshot_id = #{resource_snapshot_id}
     AND to_blocks.hash = from_blocks.hash
     AND to_blocks.snapshot_id = snapshot.id
@@ -17,7 +17,7 @@
   </select>
 
   <insert id="batchInsert" parameterType="DuplicationUnit" useGeneratedKeys="false">
-    INSERT INTO duplications_index (snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line)
+    INSERT INTO ${_schema}duplications_index (snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line)
     VALUES (#{snapshotId}, #{projectSnapshotId}, #{hash}, #{indexInFile}, #{startLine}, #{endLine})
   </insert>
 
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/model/RuleMapper.xml b/sonar-core/src/main/resources/org/sonar/persistence/model/RuleMapper.xml
index 7cb565596dc..7d0614dea2b 100644
--- a/sonar-core/src/main/resources/org/sonar/persistence/model/RuleMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/persistence/model/RuleMapper.xml
@@ -4,11 +4,11 @@
 <mapper namespace="org.sonar.persistence.model.RuleMapper">
 
 	<select id="selectAll" resultType="Rule">
-		select id, plugin_rule_key as "ruleKey", plugin_name as "repositoryKey", description, enabled, name from RULES
+		select id, plugin_rule_key as "ruleKey", plugin_name as "repositoryKey", description, enabled, name from ${_schema}RULES
 	</select>
 
   <select id="selectById" parameterType="long" resultType="Rule">
-		select id, plugin_rule_key as "ruleKey", plugin_name as "repositoryKey", description, enabled, name from RULES WHERE id=#{id}
+		select id, plugin_rule_key as "ruleKey", plugin_name as "repositoryKey", description, enabled, name from ${_schema}RULES WHERE id=#{id}
 	</select>
 </mapper>
 
diff --git a/sonar-core/src/test/java/org/sonar/persistence/HsqlDatabase.java b/sonar-core/src/test/java/org/sonar/persistence/HsqlDatabase.java
index f302d40fbc4..13137509f49 100644
--- a/sonar-core/src/test/java/org/sonar/persistence/HsqlDatabase.java
+++ b/sonar-core/src/test/java/org/sonar/persistence/HsqlDatabase.java
@@ -85,6 +85,10 @@ public class HsqlDatabase implements Database {
     return new HsqlDb();
   }
 
+  public String getSchema() {
+    return null;
+  }
+
   public Properties getHibernateProperties() {
     Properties properties = new Properties();
     properties.put("hibernate.hbm2ddl.auto", "create-drop");
diff --git a/sonar-core/src/test/java/org/sonar/persistence/InMemoryDatabase.java b/sonar-core/src/test/java/org/sonar/persistence/InMemoryDatabase.java
index 0a145afaaa6..f499b6a83d9 100644
--- a/sonar-core/src/test/java/org/sonar/persistence/InMemoryDatabase.java
+++ b/sonar-core/src/test/java/org/sonar/persistence/InMemoryDatabase.java
@@ -149,6 +149,10 @@ public class InMemoryDatabase implements Database {
     return new Derby();
   }
 
+  public String getSchema() {
+    return null;
+  }
+
   public Properties getHibernateProperties() {
     Properties properties = new Properties();
     properties.put("hibernate.hbm2ddl.auto", "validate");
diff --git a/sonar-core/src/test/java/org/sonar/persistence/dao/DaoTestCase.java b/sonar-core/src/test/java/org/sonar/persistence/dao/DaoTestCase.java
new file mode 100644
index 00000000000..c8b32f4d6cf
--- /dev/null
+++ b/sonar-core/src/test/java/org/sonar/persistence/dao/DaoTestCase.java
@@ -0,0 +1,269 @@
+/*
+ * Sonar, open source software quality management tool.
+ * Copyright (C) 2008-2011 SonarSource
+ * mailto:contact AT sonarsource DOT com
+ *
+ * Sonar is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * Sonar is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public
+ * License along with Sonar; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02
+ */
+package org.sonar.persistence.dao;
+
+import com.google.common.collect.Lists;
+import org.apache.commons.io.IOUtils;
+import org.dbunit.Assertion;
+import org.dbunit.DataSourceDatabaseTester;
+import org.dbunit.DatabaseUnitException;
+import org.dbunit.IDatabaseTester;
+import org.dbunit.database.DatabaseConfig;
+import org.dbunit.database.IDatabaseConnection;
+import org.dbunit.dataset.*;
+import org.dbunit.dataset.filter.DefaultColumnFilter;
+import org.dbunit.dataset.xml.FlatXmlDataSet;
+import org.dbunit.operation.DatabaseOperation;
+import org.junit.*;
+import org.sonar.api.config.Settings;
+import org.sonar.persistence.Database;
+import org.sonar.persistence.DefaultDatabase;
+import org.sonar.persistence.InMemoryDatabase;
+import org.sonar.persistence.MyBatis;
+
+import java.io.InputStream;
+import java.io.StringWriter;
+import java.sql.*;
+import java.util.List;
+import java.util.Map;
+
+import static org.junit.Assert.fail;
+
+public abstract class DaoTestCase {
+
+  private static Database database;
+  private static MyBatis myBatis;
+  private static DatabaseCommands databaseCommands;
+  
+  private IDatabaseTester databaseTester;
+  private IDatabaseConnection connection;
+
+  @BeforeClass
+  public static void startDatabase() throws Exception {
+    Settings settings = new Settings();
+    settings.setProperties((Map)System.getProperties());
+    if (settings.hasKey("sonar.jdbc.dialect")) {
+      database = new DefaultDatabase(settings);
+    } else {
+      database = new InMemoryDatabase();
+    }
+    database.start();
+
+    myBatis = new MyBatis(database);
+    myBatis.start();
+
+    databaseCommands = DatabaseCommands.forDialect(database.getDialect());
+  }
+
+  @Before
+  public void setupDbUnit() throws SQLException {
+    truncateTables();
+    databaseTester = new DataSourceDatabaseTester(database.getDataSource());
+  }
+
+  @After
+  public void tearDownDbUnit() throws Exception {
+    databaseTester.onTearDown();
+    if (connection != null) {
+      connection.close();
+    }
+  }
+
+  @AfterClass
+  public static void stopDatabase() {
+    if (database != null) {
+      database.stop();
+    }
+  }
+
+  private List<String> listTables() throws SQLException {
+    Connection connection = myBatis.openSession().getConnection();
+    DatabaseMetaData meta = connection.getMetaData();
+    Statement statement = connection.createStatement();
+
+    ResultSet res = meta.getTables(null, null, null, new String[]{"TABLE"});
+    List<String> tables = Lists.newArrayList();
+    while (res.next()) {
+      String tableName = res.getString("TABLE_NAME");
+      tables.add(tableName.toLowerCase());
+    }
+    res.close();
+    statement.close();
+    connection.close();
+    return tables;
+  }
+
+  private void truncateTables() throws SQLException {
+    List<String> tables = listTables();
+    Connection connection = myBatis.openSession().getConnection();
+    Statement statement = connection.createStatement();
+    for (String table : tables) {
+      // 1. truncate
+      statement.executeUpdate(databaseCommands.truncate(table));
+      connection.commit();
+
+      // 2. reset primary keys
+      try {
+        statement.executeUpdate(databaseCommands.resetPrimaryKey(table));
+        connection.commit();
+      } catch (Exception e) {
+        // this table has no primary key
+        connection.rollback();
+      }
+    }
+    statement.close();
+    connection.commit();
+    connection.close();
+  }
+
+  protected MyBatis getMyBatis() {
+    return myBatis;
+  }
+
+  protected final void setupData(String... testNames) {
+    InputStream[] streams = new InputStream[testNames.length];
+    try {
+      for (int i = 0; i < testNames.length; i++) {
+        String className = getClass().getName();
+        className = String.format("/%s/%s.xml", className.replace(".", "/"), testNames[i]);
+        streams[i] = getClass().getResourceAsStream(className);
+        if (streams[i] == null) {
+          throw new RuntimeException("Test not found :" + className);
+        }
+      }
+
+      setupData(streams);
+
+    } finally {
+      for (InputStream stream : streams) {
+        IOUtils.closeQuietly(stream);
+      }
+    }
+  }
+
+  protected final void setupData(InputStream... dataSetStream) {
+    try {
+      IDataSet[] dataSets = new IDataSet[dataSetStream.length];
+      for (int i = 0; i < dataSetStream.length; i++) {
+        ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(dataSetStream[i]));
+        dataSet.addReplacementObject("[null]", null);
+        dataSets[i] = dataSet;
+      }
+      CompositeDataSet compositeDataSet = new CompositeDataSet(dataSets);
+
+      databaseTester.setDataSet(compositeDataSet);
+      connection = databaseTester.getConnection();
+
+      connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, databaseCommands.dbUnitFactory());
+      DatabaseOperation.CLEAN_INSERT.execute(connection, databaseTester.getDataSet());
+
+    } catch (Exception e) {
+      throw translateException("Could not setup DBUnit data", e);
+    }
+  }
+
+  protected final void checkTables(String testName, String... tables) {
+    checkTables(testName, new String[]{}, tables);
+  }
+
+  protected final void checkTables(String testName, String[] excludedColumnNames, String... tables) {
+    try {
+      IDataSet dataSet = getCurrentDataSet();
+      IDataSet expectedDataSet = getExpectedData(testName);
+      for (String table : tables) {
+        ITable filteredTable = DefaultColumnFilter.excludedColumnsTable(dataSet.getTable(table), excludedColumnNames);
+        Assertion.assertEquals(expectedDataSet.getTable(table), filteredTable);
+      }
+    } catch (DataSetException e) {
+      throw translateException("Error while checking results", e);
+    } catch (DatabaseUnitException e) {
+      fail(e.getMessage());
+    }
+  }
+
+  protected final void assertEmptyTables(String... emptyTables) {
+    for (String table : emptyTables) {
+      try {
+        Assert.assertEquals(0, getCurrentDataSet().getTable(table).getRowCount());
+      } catch (DataSetException e) {
+        throw translateException("Error while checking results", e);
+      }
+    }
+  }
+
+  protected final IDataSet getExpectedData(String testName) {
+    String className = getClass().getName();
+    className = String.format("/%s/%s-result.xml", className.replace(".", "/"), testName);
+
+    InputStream in = getClass().getResourceAsStream(className);
+    try {
+      return getData(in);
+    } finally {
+      IOUtils.closeQuietly(in);
+    }
+  }
+
+  protected final IDataSet getData(InputStream stream) {
+    try {
+      ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(stream));
+      dataSet.addReplacementObject("[null]", null);
+      return dataSet;
+    } catch (Exception e) {
+      throw translateException("Could not read the dataset stream", e);
+    }
+  }
+
+  protected final IDataSet getCurrentDataSet() {
+    try {
+      return connection.createDataSet();
+    } catch (SQLException e) {
+      throw translateException("Could not create the current dataset", e);
+    }
+  }
+
+  protected String getCurrentDataSetAsXML() {
+    return getDataSetAsXML(getCurrentDataSet());
+  }
+
+  protected String getDataSetAsXML(IDataSet dataset) {
+    try {
+      StringWriter writer = new StringWriter();
+      FlatXmlDataSet.write(dataset, writer);
+      return writer.getBuffer().toString();
+    } catch (Exception e) {
+      throw translateException("Could not build XML from dataset", e);
+    }
+  }
+
+  private static RuntimeException translateException(String msg, Exception cause) {
+    RuntimeException runtimeException = new RuntimeException(String.format("%s: [%s] %s", msg, cause.getClass().getName(), cause.getMessage()));
+    runtimeException.setStackTrace(cause.getStackTrace());
+    return runtimeException;
+  }
+
+  protected IDatabaseConnection getConnection() {
+    return connection;
+  }
+
+  protected IDatabaseTester getDatabaseTester() {
+    return databaseTester;
+  }
+
+}
diff --git a/sonar-core/src/test/java/org/sonar/persistence/dao/DatabaseCommands.java b/sonar-core/src/test/java/org/sonar/persistence/dao/DatabaseCommands.java
new file mode 100644
index 00000000000..0e4899bb9a1
--- /dev/null
+++ b/sonar-core/src/test/java/org/sonar/persistence/dao/DatabaseCommands.java
@@ -0,0 +1,127 @@
+/*
+ * Sonar, open source software quality management tool.
+ * Copyright (C) 2008-2011 SonarSource
+ * mailto:contact AT sonarsource DOT com
+ *
+ * Sonar is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * Sonar is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public
+ * License along with Sonar; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02
+ */
+package org.sonar.persistence.dao;
+
+import org.dbunit.dataset.datatype.DefaultDataTypeFactory;
+import org.dbunit.dataset.datatype.IDataTypeFactory;
+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.sonar.jpa.dialect.*;
+
+abstract class DatabaseCommands {
+
+  private IDataTypeFactory dbUnitFactory;
+
+  private DatabaseCommands(IDataTypeFactory dbUnitFactory) {
+    this.dbUnitFactory = dbUnitFactory;
+  }
+
+  abstract String truncate(String table);
+
+  abstract String resetPrimaryKey(String table);
+
+  final IDataTypeFactory dbUnitFactory() {
+    return dbUnitFactory;
+  }
+
+
+  
+  static final DatabaseCommands DERBY = new DatabaseCommands(new DefaultDataTypeFactory()) {
+    @Override
+    String truncate(String table) {
+      return "TRUNCATE TABLE " + table;
+    }
+
+    @Override
+    String resetPrimaryKey(String table) {
+      return "ALTER TABLE " + table + " ALTER COLUMN ID RESTART WITH 1";
+    }
+  };
+  
+  static final DatabaseCommands MSSQL = new DatabaseCommands(new MsSqlDataTypeFactory()) {
+    @Override
+    String truncate(String table) {
+      return "TRUNCATE TABLE " + table;
+    }
+
+    @Override
+    String resetPrimaryKey(String table) {
+      return "DBCC CHECKIDENT('" + table + "', RESEED, 1)";
+    }
+  };
+
+  static final DatabaseCommands MYSQL = new DatabaseCommands(new MySqlDataTypeFactory()) {
+    @Override
+    String truncate(String table) {
+      return "TRUNCATE TABLE " + table;
+    }
+
+    @Override
+    String resetPrimaryKey(String table) {
+      return "ALTER TABLE " + table + " AUTO_INCREMENT = 1";
+    }
+  };
+
+  static final DatabaseCommands ORACLE = new DatabaseCommands(new Oracle10DataTypeFactory()) {
+    @Override
+    String truncate(String table) {
+      return "TRUNCATE TABLE " + table;
+    }
+
+    @Override
+    String resetPrimaryKey(String table) {
+      return "ALTER SEQUENCE " + table + "_SEQ INCREMENT BY - MINVALUE 1;";
+    }
+  };
+
+  static final DatabaseCommands POSTGRESQL = new DatabaseCommands(new PostgresqlDataTypeFactory()) {
+    @Override
+    String truncate(String table) {
+      return "TRUNCATE TABLE " + table;
+    }
+
+    @Override
+    String resetPrimaryKey(String table) {
+      return "ALTER SEQUENCE " + table + "_id_seq RESTART WITH 1";
+    }
+  };
+
+
+  static DatabaseCommands forDialect(Dialect dialect) {
+    if (Derby.ID.equals(dialect.getId())) {
+      return DERBY;
+    }
+    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;
+    }
+    throw new IllegalArgumentException("Unknown database: " + dialect);
+  }
+}
diff --git a/sonar-core/src/test/java/org/sonar/persistence/dao/DuplicationDaoTest.java b/sonar-core/src/test/java/org/sonar/persistence/dao/DuplicationDaoTest.java
index 353a9cee987..7c1c4e0b210 100644
--- a/sonar-core/src/test/java/org/sonar/persistence/dao/DuplicationDaoTest.java
+++ b/sonar-core/src/test/java/org/sonar/persistence/dao/DuplicationDaoTest.java
@@ -33,7 +33,7 @@ import org.sonar.persistence.model.DuplicationUnit;
 
 import com.google.common.collect.Lists;
 
-public class DuplicationDaoTest extends AbstractDbUnitTestCase {
+public class DuplicationDaoTest extends DaoTestCase {
 
   private DuplicationDao dao;
 
diff --git a/sonar-core/src/test/java/org/sonar/persistence/dao/RuleDaoTest.java b/sonar-core/src/test/java/org/sonar/persistence/dao/RuleDaoTest.java
index a0dc3694f94..70dc8696247 100644
--- a/sonar-core/src/test/java/org/sonar/persistence/dao/RuleDaoTest.java
+++ b/sonar-core/src/test/java/org/sonar/persistence/dao/RuleDaoTest.java
@@ -28,7 +28,7 @@ import org.junit.Before;
 import org.junit.Test;
 import org.sonar.jpa.test.AbstractDbUnitTestCase;
 
-public class RuleDaoTest extends AbstractDbUnitTestCase {
+public class RuleDaoTest extends DaoTestCase {
 
   private static RuleDao dao;