SONAR-2956 Support Boolean type with Derby

- Change DDL to use BOOLEAN instead of SMALLINT
- Modify Hibernate dialect
- Modify activerecord-jdbc-adapter
  (ideas were taken from arjdbc/postgresql/adapter.rb)
- Use Derby for unit tests instead of HSQL and fix incorrect tests

8 files changed, 154 insertions, 89 deletions

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 5dd0bc6fee6..04a71997393 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
@@ -60,6 +60,13 @@ public class Derby implements Dialect {
 
       // Not possible to do alter column types in Derby
       registerColumnType(Types.BIGINT, "integer");
+
+      registerColumnType(Types.BIT, "boolean");
+    }
+
+    @Override
+    public String toBooleanValueString(boolean bool) {
+      return bool ? "true" : "false";
     }
 
     /**
diff --git a/sonar-core/src/main/java/org/sonar/jpa/session/MemoryDatabaseConnector.java b/sonar-core/src/main/java/org/sonar/jpa/session/MemoryDatabaseConnector.java
index ace82efec83..bf32e2cc8ca 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/session/MemoryDatabaseConnector.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/session/MemoryDatabaseConnector.java
@@ -23,7 +23,6 @@ import org.sonar.jpa.entity.SchemaMigration;
 import org.sonar.persistence.Database;
 
 import javax.persistence.EntityManager;
-import java.sql.Connection;
 
 public class MemoryDatabaseConnector extends DefaultDatabaseConnector {
   private int version;
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-derby.xml b/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-derby.xml
deleted file mode 100644
index fe3e76926dd..00000000000
--- a/sonar-core/src/main/resources/org/sonar/persistence/model/DuplicationMapper-derby.xml
+++ /dev/null
@@ -1,24 +0,0 @@
-<?xml version="1.0" encoding="UTF-8" ?>
-<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
-
-<mapper namespace="org.sonar.persistence.model.DuplicationMapper">
-
-  <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
-    WHERE from_blocks.snapshot_id = #{resource_snapshot_id}
-    AND to_blocks.hash = from_blocks.hash
-    AND to_blocks.snapshot_id = snapshot.id
-    AND snapshot.islast = 1
-    AND snapshot.project_id = res.id
-    <if test="last_project_snapshot_id != null">
-      AND to_blocks.project_snapshot_id != #{last_project_snapshot_id}
-    </if>
-  </select>
-
-  <insert id="insert" parameterType="DuplicationUnit" keyColumn="id" useGeneratedKeys="true">
-    INSERT INTO duplications_index (snapshot_id, project_snapshot_id, hash, index_in_file, start_line, end_line)
-    VALUES (#{snapshotId}, #{projectSnapshotId}, #{hash}, #{indexInFile}, #{startLine}, #{endLine})
-  </insert>
-
-</mapper>
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql b/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql
index 904b374cc40..9970b86ccba 100644
--- a/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql
+++ b/sonar-core/src/main/resources/org/sonar/persistence/rows-derby.sql
@@ -15,7 +15,7 @@ INSERT INTO CRITERIA(ID, FILTER_ID, FAMILY, KEE, OPERATOR, VALUE, TEXT_VALUE, VA
 INSERT INTO CRITERIA(ID, FILTER_ID, FAMILY, KEE, OPERATOR, VALUE, TEXT_VALUE, VARIATION) VALUES (3, 3, 'qualifier', null, '=', null, 'VW,SVW,TRK,BRC,DIR,PAC,FIL,CLA,UTS,LIB', null);
 ALTER TABLE CRITERIA ALTER COLUMN ID RESTART WITH 4;
 
-INSERT INTO DASHBOARDS(ID, USER_ID, NAME, DESCRIPTION, COLUMN_LAYOUT, SHARED, CREATED_AT, UPDATED_AT) VALUES (1, null, 'Dashboard', 'Default dashboard', '50%-50%', 1, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
+INSERT INTO DASHBOARDS(ID, USER_ID, NAME, DESCRIPTION, COLUMN_LAYOUT, SHARED, CREATED_AT, UPDATED_AT) VALUES (1, null, 'Dashboard', 'Default dashboard', '50%-50%', true, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
 ALTER TABLE DASHBOARDS ALTER COLUMN ID RESTART WITH 2;
 
 INSERT INTO FILTER_COLUMNS(ID, FILTER_ID, FAMILY, KEE, SORT_DIRECTION, ORDER_INDEX, VARIATION) VALUES (1, 1, 'metric', 'alert_status', null, 1, null);
@@ -35,9 +35,9 @@ INSERT INTO FILTER_COLUMNS(ID, FILTER_ID, FAMILY, KEE, SORT_DIRECTION, ORDER_IND
 INSERT INTO FILTER_COLUMNS(ID, FILTER_ID, FAMILY, KEE, SORT_DIRECTION, ORDER_INDEX, VARIATION) VALUES (15, 3, 'date', null, null, 5, null);
 ALTER TABLE FILTER_COLUMNS ALTER COLUMN ID RESTART WITH 16;
 
-INSERT INTO FILTERS(ID, NAME, USER_ID, SHARED, FAVOURITES, RESOURCE_ID, DEFAULT_VIEW, PAGE_SIZE, PERIOD_INDEX) VALUES (1, 'Projects', null, 1, 0, null, 'list', null, null);
-INSERT INTO FILTERS(ID, NAME, USER_ID, SHARED, FAVOURITES, RESOURCE_ID, DEFAULT_VIEW, PAGE_SIZE, PERIOD_INDEX) VALUES (2, 'Treemap', null, 1, 0, null, 'treemap', null, null);
-INSERT INTO FILTERS(ID, NAME, USER_ID, SHARED, FAVOURITES, RESOURCE_ID, DEFAULT_VIEW, PAGE_SIZE, PERIOD_INDEX) VALUES (3, 'My favourites', null, 1, 1, null, 'list', null, null);
+INSERT INTO FILTERS(ID, NAME, USER_ID, SHARED, FAVOURITES, RESOURCE_ID, DEFAULT_VIEW, PAGE_SIZE, PERIOD_INDEX) VALUES (1, 'Projects', null, true, false, null, 'list', null, null);
+INSERT INTO FILTERS(ID, NAME, USER_ID, SHARED, FAVOURITES, RESOURCE_ID, DEFAULT_VIEW, PAGE_SIZE, PERIOD_INDEX) VALUES (2, 'Treemap', null, true, false, null, 'treemap', null, null);
+INSERT INTO FILTERS(ID, NAME, USER_ID, SHARED, FAVOURITES, RESOURCE_ID, DEFAULT_VIEW, PAGE_SIZE, PERIOD_INDEX) VALUES (3, 'My favourites', null, true, true, null, 'list', null, null);
 ALTER TABLE FILTERS ALTER COLUMN ID RESTART WITH 4;
 
 INSERT INTO GROUP_ROLES(ID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (1, 1, null, 'admin');
@@ -167,15 +167,15 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('221');
 INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT, REMEMBER_TOKEN, REMEMBER_TOKEN_EXPIRES_AT) VALUES (1, 'admin', 'Administrator', '', 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '2011-09-26 22:27:48.0', '2011-09-26 22:27:48.0', null, null);
 ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2;
 
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (1, 1, 'size', 'Size metrics', null, 1, 1, 1, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (2, 1, 'comments_duplications', 'Comments duplications', null, 1, 2, 1, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (3, 1, 'complexity', 'Complexity', null, 1, 3, 1, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (4, 1, 'code_coverage', 'Code coverage', null, 1, 4, 1, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (5, 1, 'events', 'Events', null, 1, 5, 1, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (6, 1, 'description', 'Description', null, 1, 6, 1, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (7, 1, 'rules', 'Rules', null, 2, 1, 1, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (8, 1, 'alerts', 'Alerts', null, 2, 2, 1, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (9, 1, 'file_design', 'File design', null, 2, 3, 1, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (10, 1, 'package_design', 'Package design', null, 2, 4, 1, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
-INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (11, 1, 'ckjm', 'CKJM', null, 2, 5, 1, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (1, 1, 'size', 'Size metrics', null, 1, 1, true, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (2, 1, 'comments_duplications', 'Comments duplications', null, 1, 2, true, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (3, 1, 'complexity', 'Complexity', null, 1, 3, true, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (4, 1, 'code_coverage', 'Code coverage', null, 1, 4, true, '2011-09-26 22:27:55.0', '2011-09-26 22:27:55.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (5, 1, 'events', 'Events', null, 1, 5, true, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (6, 1, 'description', 'Description', null, 1, 6, true, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (7, 1, 'rules', 'Rules', null, 2, 1, true, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (8, 1, 'alerts', 'Alerts', null, 2, 2, true, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (9, 1, 'file_design', 'File design', null, 2, 3, true, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (10, 1, 'package_design', 'Package design', null, 2, 4, true, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
+INSERT INTO WIDGETS(ID, DASHBOARD_ID, WIDGET_KEY, NAME, DESCRIPTION, COLUMN_INDEX, ROW_INDEX, CONFIGURED, CREATED_AT, UPDATED_AT) VALUES (11, 1, 'ckjm', 'CKJM', null, 2, 5, true, '2011-09-26 22:27:56.0', '2011-09-26 22:27:56.0');
 ALTER TABLE WIDGETS ALTER COLUMN ID RESTART WITH 12;
\ No newline at end of file
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl b/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl
index 35228ef0afc..cc8a7e40f3e 100644
--- a/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl
+++ b/sonar-core/src/main/resources/org/sonar/persistence/schema-derby.ddl
@@ -11,19 +11,19 @@ CREATE TABLE "GROUPS_USERS" ("USER_ID" INTEGER, "GROUP_ID" INTEGER);
 
 CREATE TABLE "CHARACTERISTIC_EDGES" ("CHILD_ID" INTEGER, "PARENT_ID" INTEGER);
 
-CREATE TABLE "CRITERIA" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "FILTER_ID" INTEGER, "FAMILY" VARCHAR(100), "KEE" VARCHAR(100), "OPERATOR" VARCHAR(20), "VALUE" DECIMAL(30,20), "TEXT_VALUE" VARCHAR(256), "VARIATION" SMALLINT);
+CREATE TABLE "CRITERIA" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "FILTER_ID" INTEGER, "FAMILY" VARCHAR(100), "KEE" VARCHAR(100), "OPERATOR" VARCHAR(20), "VALUE" DECIMAL(30,20), "TEXT_VALUE" VARCHAR(256), "VARIATION" BOOLEAN);
 
 CREATE TABLE "DEPENDENCIES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "FROM_SNAPSHOT_ID" INTEGER, "FROM_RESOURCE_ID" INTEGER, "TO_SNAPSHOT_ID" INTEGER, "TO_RESOURCE_ID" INTEGER, "DEP_USAGE" VARCHAR(30), "DEP_WEIGHT" INTEGER, "PROJECT_SNAPSHOT_ID" INTEGER, "PARENT_DEPENDENCY_ID" BIGINT, "FROM_SCOPE" VARCHAR(3), "TO_SCOPE" VARCHAR(3));
 
-CREATE TABLE "CHARACTERISTICS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "QUALITY_MODEL_ID" INTEGER, "KEE" VARCHAR(100), "NAME" VARCHAR(100), "RULE_ID" INTEGER, "DEPTH" INTEGER, "CHARACTERISTIC_ORDER" INTEGER, "DESCRIPTION" VARCHAR(4000), "ENABLED" SMALLINT);
+CREATE TABLE "CHARACTERISTICS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "QUALITY_MODEL_ID" INTEGER, "KEE" VARCHAR(100), "NAME" VARCHAR(100), "RULE_ID" INTEGER, "DEPTH" INTEGER, "CHARACTERISTIC_ORDER" INTEGER, "DESCRIPTION" VARCHAR(4000), "ENABLED" BOOLEAN);
 
 CREATE TABLE "RULES_PARAMETERS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "RULE_ID" INTEGER NOT NULL, "NAME" VARCHAR(128) NOT NULL, "PARAM_TYPE" VARCHAR(512) NOT NULL, "DEFAULT_VALUE" VARCHAR(4000), "DESCRIPTION" VARCHAR(4000));
 
-CREATE TABLE "RULES_PROFILES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(100) NOT NULL, "DEFAULT_PROFILE" SMALLINT DEFAULT 0, "PROVIDED" SMALLINT NOT NULL DEFAULT 0, "LANGUAGE" VARCHAR(16), "PARENT_NAME" VARCHAR(100), "ENABLED" SMALLINT NOT NULL DEFAULT 1, "VERSION" INTEGER DEFAULT 1, "USED_PROFILE" SMALLINT DEFAULT 0);
+CREATE TABLE "RULES_PROFILES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(100) NOT NULL, "DEFAULT_PROFILE" BOOLEAN DEFAULT FALSE, "PROVIDED" BOOLEAN NOT NULL DEFAULT FALSE, "LANGUAGE" VARCHAR(16), "PARENT_NAME" VARCHAR(100), "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, "VERSION" INTEGER DEFAULT 1, "USED_PROFILE" BOOLEAN DEFAULT FALSE);
 
-CREATE TABLE "WIDGETS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "DASHBOARD_ID" INTEGER NOT NULL, "WIDGET_KEY" VARCHAR(256) NOT NULL, "NAME" VARCHAR(256), "DESCRIPTION" VARCHAR(1000), "COLUMN_INDEX" INTEGER, "ROW_INDEX" INTEGER, "CONFIGURED" SMALLINT, "CREATED_AT" TIMESTAMP, "UPDATED_AT" TIMESTAMP);
+CREATE TABLE "WIDGETS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "DASHBOARD_ID" INTEGER NOT NULL, "WIDGET_KEY" VARCHAR(256) NOT NULL, "NAME" VARCHAR(256), "DESCRIPTION" VARCHAR(1000), "COLUMN_INDEX" INTEGER, "ROW_INDEX" INTEGER, "CONFIGURED" BOOLEAN, "CREATED_AT" TIMESTAMP, "UPDATED_AT" TIMESTAMP);
 
-CREATE TABLE "FILTER_COLUMNS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "FILTER_ID" INTEGER, "FAMILY" VARCHAR(100), "KEE" VARCHAR(100), "SORT_DIRECTION" VARCHAR(5), "ORDER_INDEX" INTEGER, "VARIATION" SMALLINT);
+CREATE TABLE "FILTER_COLUMNS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "FILTER_ID" INTEGER, "FAMILY" VARCHAR(100), "KEE" VARCHAR(100), "SORT_DIRECTION" VARCHAR(5), "ORDER_INDEX" INTEGER, "VARIATION" BOOLEAN);
 
 CREATE TABLE "MEASURE_DATA" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "MEASURE_ID" INTEGER, "SNAPSHOT_ID" INTEGER, "DATA" BLOB(2147483647));
 
@@ -31,13 +31,13 @@ CREATE TABLE "GROUPS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (S
 
 CREATE TABLE "ACTIVE_RULE_PARAM_CHANGES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "ACTIVE_RULE_CHANGE_ID" INTEGER NOT NULL, "RULES_PARAMETER_ID" INTEGER NOT NULL, "OLD_VALUE" VARCHAR(4000), "NEW_VALUE" VARCHAR(4000));
 
-CREATE TABLE "SNAPSHOTS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "CREATED_AT" TIMESTAMP, "PROJECT_ID" INTEGER NOT NULL, "PARENT_SNAPSHOT_ID" INTEGER, "STATUS" VARCHAR(4) NOT NULL DEFAULT 'U', "ISLAST" SMALLINT NOT NULL DEFAULT 0, "SCOPE" VARCHAR(3), "QUALIFIER" VARCHAR(3), "ROOT_SNAPSHOT_ID" INTEGER, "VERSION" VARCHAR(60), "PATH" VARCHAR(96), "DEPTH" INTEGER, "ROOT_PROJECT_ID" INTEGER, "PERIOD1_MODE" VARCHAR(100), "PERIOD1_PARAM" VARCHAR(100), "PERIOD1_DATE" TIMESTAMP, "PERIOD2_MODE" VARCHAR(100), "PERIOD2_PARAM" VARCHAR(100), "PERIOD2_DATE" TIMESTAMP, "PERIOD3_MODE" VARCHAR(100), "PERIOD3_PARAM" VARCHAR(100), "PERIOD3_DATE" TIMESTAMP, "PERIOD4_MODE" VARCHAR(100), "PERIOD4_PARAM" VARCHAR(100), "PERIOD4_DATE" TIMESTAMP, "PERIOD5_MODE" VARCHAR(100), "PERIOD5_PARAM" VARCHAR(100), "PERIOD5_DATE" TIMESTAMP);
+CREATE TABLE "SNAPSHOTS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "CREATED_AT" TIMESTAMP, "PROJECT_ID" INTEGER NOT NULL, "PARENT_SNAPSHOT_ID" INTEGER, "STATUS" VARCHAR(4) NOT NULL DEFAULT 'U', "ISLAST" BOOLEAN NOT NULL DEFAULT FALSE, "SCOPE" VARCHAR(3), "QUALIFIER" VARCHAR(3), "ROOT_SNAPSHOT_ID" INTEGER, "VERSION" VARCHAR(60), "PATH" VARCHAR(96), "DEPTH" INTEGER, "ROOT_PROJECT_ID" INTEGER, "PERIOD1_MODE" VARCHAR(100), "PERIOD1_PARAM" VARCHAR(100), "PERIOD1_DATE" TIMESTAMP, "PERIOD2_MODE" VARCHAR(100), "PERIOD2_PARAM" VARCHAR(100), "PERIOD2_DATE" TIMESTAMP, "PERIOD3_MODE" VARCHAR(100), "PERIOD3_PARAM" VARCHAR(100), "PERIOD3_DATE" TIMESTAMP, "PERIOD4_MODE" VARCHAR(100), "PERIOD4_PARAM" VARCHAR(100), "PERIOD4_DATE" TIMESTAMP, "PERIOD5_MODE" VARCHAR(100), "PERIOD5_PARAM" VARCHAR(100), "PERIOD5_DATE" TIMESTAMP);
 
 CREATE TABLE "SCHEMA_MIGRATIONS" ("VERSION" VARCHAR(256) NOT NULL);
 
 CREATE TABLE "GROUP_ROLES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "GROUP_ID" INTEGER, "RESOURCE_ID" INTEGER, "ROLE" VARCHAR(64) NOT NULL);
 
-CREATE TABLE "RULES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "PLUGIN_RULE_KEY" VARCHAR(200) NOT NULL, "PLUGIN_NAME" VARCHAR(255) NOT NULL, "DESCRIPTION" CLOB(2147483647), "PRIORITY" INTEGER, "ENABLED" SMALLINT, "CARDINALITY" VARCHAR(10), "PARENT_ID" INTEGER, "PLUGIN_CONFIG_KEY" VARCHAR(500), "NAME" VARCHAR(200));
+CREATE TABLE "RULES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "PLUGIN_RULE_KEY" VARCHAR(200) NOT NULL, "PLUGIN_NAME" VARCHAR(255) NOT NULL, "DESCRIPTION" CLOB(2147483647), "PRIORITY" INTEGER, "ENABLED" BOOLEAN, "CARDINALITY" VARCHAR(10), "PARENT_ID" INTEGER, "PLUGIN_CONFIG_KEY" VARCHAR(500), "NAME" VARCHAR(200));
 
 CREATE TABLE "WIDGET_PROPERTIES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "WIDGET_ID" INTEGER NOT NULL, "KEE" VARCHAR(100), "TEXT_VALUE" VARCHAR(4000), "VALUE_TYPE" VARCHAR(20));
 
@@ -53,13 +53,13 @@ CREATE TABLE "DUPLICATIONS_INDEX" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS
 
 CREATE TABLE "REVIEW_COMMENTS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "CREATED_AT" TIMESTAMP, "UPDATED_AT" TIMESTAMP, "REVIEW_ID" INTEGER, "USER_ID" INTEGER, "REVIEW_TEXT" CLOB(2147483647));
 
-CREATE TABLE "ACTIVE_RULE_CHANGES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "USER_NAME" VARCHAR(200) NOT NULL, "PROFILE_ID" INTEGER NOT NULL, "PROFILE_VERSION" INTEGER NOT NULL, "RULE_ID" INTEGER NOT NULL, "CHANGE_DATE" TIMESTAMP NOT NULL, "ENABLED" SMALLINT, "OLD_SEVERITY" INTEGER, "NEW_SEVERITY" INTEGER);
+CREATE TABLE "ACTIVE_RULE_CHANGES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "USER_NAME" VARCHAR(200) NOT NULL, "PROFILE_ID" INTEGER NOT NULL, "PROFILE_VERSION" INTEGER NOT NULL, "RULE_ID" INTEGER NOT NULL, "CHANGE_DATE" TIMESTAMP NOT NULL, "ENABLED" BOOLEAN, "OLD_SEVERITY" INTEGER, "NEW_SEVERITY" INTEGER);
 
 CREATE TABLE "PROJECT_MEASURES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "VALUE" DECIMAL(30,20), "METRIC_ID" INTEGER NOT NULL, "SNAPSHOT_ID" INTEGER, "RULE_ID" INTEGER, "RULES_CATEGORY_ID" INTEGER, "TEXT_VALUE" VARCHAR(96), "TENDENCY" INTEGER, "MEASURE_DATE" TIMESTAMP, "PROJECT_ID" INTEGER, "ALERT_STATUS" VARCHAR(5), "ALERT_TEXT" VARCHAR(4000), "URL" VARCHAR(2000), "DESCRIPTION" VARCHAR(4000), "RULE_PRIORITY" INTEGER, "CHARACTERISTIC_ID" INTEGER, "VARIATION_VALUE_1" DECIMAL(30,20), "VARIATION_VALUE_2" DECIMAL(30,20), "VARIATION_VALUE_3" DECIMAL(30,20), "VARIATION_VALUE_4" DECIMAL(30,20), "VARIATION_VALUE_5" DECIMAL(30,20));
 
 CREATE TABLE "SNAPSHOT_SOURCES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "SNAPSHOT_ID" INTEGER NOT NULL, "DATA" CLOB(2147483647));
 
-CREATE TABLE "PROJECTS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(256), "DESCRIPTION" VARCHAR(2000), "ENABLED" SMALLINT NOT NULL DEFAULT 1, "SCOPE" VARCHAR(3), "QUALIFIER" VARCHAR(3), "KEE" VARCHAR(400), "ROOT_ID" INTEGER, "PROFILE_ID" INTEGER, "LANGUAGE" VARCHAR(5), "COPY_RESOURCE_ID" INTEGER, "LONG_NAME" VARCHAR(256));
+CREATE TABLE "PROJECTS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(256), "DESCRIPTION" VARCHAR(2000), "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, "SCOPE" VARCHAR(3), "QUALIFIER" VARCHAR(3), "KEE" VARCHAR(400), "ROOT_ID" INTEGER, "PROFILE_ID" INTEGER, "LANGUAGE" VARCHAR(5), "COPY_RESOURCE_ID" INTEGER, "LONG_NAME" VARCHAR(256));
 
 CREATE TABLE "REVIEWS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "CREATED_AT" TIMESTAMP, "UPDATED_AT" TIMESTAMP, "USER_ID" INTEGER, "ASSIGNEE_ID" INTEGER, "TITLE" VARCHAR(500), "STATUS" VARCHAR(10), "SEVERITY" VARCHAR(10), "RULE_FAILURE_PERMANENT_ID" INTEGER, "PROJECT_ID" INTEGER, "RESOURCE_ID" INTEGER, "RESOURCE_LINE" INTEGER, "RESOLUTION" VARCHAR(200));
 
@@ -83,13 +83,13 @@ CREATE TABLE "CHARACTERISTIC_PROPERTIES" ("ID" INTEGER NOT NULL GENERATED BY DEF
 
 CREATE TABLE "USERS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "LOGIN" VARCHAR(40), "NAME" VARCHAR(200), "EMAIL" VARCHAR(100), "CRYPTED_PASSWORD" VARCHAR(40), "SALT" VARCHAR(40), "CREATED_AT" TIMESTAMP, "UPDATED_AT" TIMESTAMP, "REMEMBER_TOKEN" VARCHAR(500), "REMEMBER_TOKEN_EXPIRES_AT" TIMESTAMP);
 
-CREATE TABLE "FILTERS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(100), "USER_ID" INTEGER, "SHARED" SMALLINT, "FAVOURITES" SMALLINT, "RESOURCE_ID" INTEGER, "DEFAULT_VIEW" VARCHAR(20), "PAGE_SIZE" INTEGER, "PERIOD_INDEX" INTEGER);
+CREATE TABLE "FILTERS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(100), "USER_ID" INTEGER, "SHARED" BOOLEAN, "FAVOURITES" BOOLEAN, "RESOURCE_ID" INTEGER, "DEFAULT_VIEW" VARCHAR(20), "PAGE_SIZE" INTEGER, "PERIOD_INDEX" INTEGER);
 
-CREATE TABLE "DASHBOARDS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "USER_ID" INTEGER, "NAME" VARCHAR(256), "DESCRIPTION" VARCHAR(1000), "COLUMN_LAYOUT" VARCHAR(20), "SHARED" SMALLINT, "CREATED_AT" TIMESTAMP, "UPDATED_AT" TIMESTAMP);
+CREATE TABLE "DASHBOARDS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "USER_ID" INTEGER, "NAME" VARCHAR(256), "DESCRIPTION" VARCHAR(1000), "COLUMN_LAYOUT" VARCHAR(20), "SHARED" BOOLEAN, "CREATED_AT" TIMESTAMP, "UPDATED_AT" TIMESTAMP);
 
-CREATE TABLE "RULE_FAILURES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "SNAPSHOT_ID" INTEGER NOT NULL, "RULE_ID" INTEGER NOT NULL, "FAILURE_LEVEL" INTEGER NOT NULL, "MESSAGE" VARCHAR(4000), "LINE" INTEGER, "COST" DECIMAL(30,20), "CREATED_AT" TIMESTAMP, "CHECKSUM" VARCHAR(1000), "PERMANENT_ID" INTEGER, "SWITCHED_OFF" SMALLINT);
+CREATE TABLE "RULE_FAILURES" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "SNAPSHOT_ID" INTEGER NOT NULL, "RULE_ID" INTEGER NOT NULL, "FAILURE_LEVEL" INTEGER NOT NULL, "MESSAGE" VARCHAR(4000), "LINE" INTEGER, "COST" DECIMAL(30,20), "CREATED_AT" TIMESTAMP, "CHECKSUM" VARCHAR(1000), "PERMANENT_ID" INTEGER, "SWITCHED_OFF" BOOLEAN);
 
-CREATE TABLE "METRICS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(64) NOT NULL, "DESCRIPTION" VARCHAR(255), "DIRECTION" INTEGER NOT NULL DEFAULT 0, "DOMAIN" VARCHAR(64), "SHORT_NAME" VARCHAR(64), "QUALITATIVE" SMALLINT NOT NULL DEFAULT 0, "VAL_TYPE" VARCHAR(8), "USER_MANAGED" SMALLINT DEFAULT 0, "ENABLED" SMALLINT DEFAULT 1, "ORIGIN" VARCHAR(3), "WORST_VALUE" DECIMAL(30,20), "BEST_VALUE" DECIMAL(30,20), "OPTIMIZED_BEST_VALUE" SMALLINT, "HIDDEN" SMALLINT);
+CREATE TABLE "METRICS" ("ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), "NAME" VARCHAR(64) NOT NULL, "DESCRIPTION" VARCHAR(255), "DIRECTION" INTEGER NOT NULL DEFAULT 0, "DOMAIN" VARCHAR(64), "SHORT_NAME" VARCHAR(64), "QUALITATIVE" BOOLEAN NOT NULL DEFAULT FALSE, "VAL_TYPE" VARCHAR(8), "USER_MANAGED" BOOLEAN DEFAULT FALSE, "ENABLED" BOOLEAN DEFAULT TRUE, "ORIGIN" VARCHAR(3), "WORST_VALUE" DECIMAL(30,20), "BEST_VALUE" DECIMAL(30,20), "OPTIMIZED_BEST_VALUE" BOOLEAN, "HIDDEN" BOOLEAN);
 
 -- ----------------------------------------------
 -- DDL Statements for indexes
diff --git a/sonar-core/src/test/java/org/sonar/jpa/entity/SchemaMigrationTest.java b/sonar-core/src/test/java/org/sonar/jpa/entity/SchemaMigrationTest.java
index 3a1710933f3..892fc5b9fba 100644
--- a/sonar-core/src/test/java/org/sonar/jpa/entity/SchemaMigrationTest.java
+++ b/sonar-core/src/test/java/org/sonar/jpa/entity/SchemaMigrationTest.java
@@ -21,8 +21,6 @@ package org.sonar.jpa.entity;
 
 import org.junit.Test;
 import org.mockito.Mockito;
-import org.sonar.api.config.Settings;
-import org.sonar.api.database.DatabaseProperties;
 import org.sonar.jpa.session.MemoryDatabaseConnector;
 import org.sonar.persistence.HsqlDatabase;
 
@@ -30,7 +28,6 @@ import java.sql.Connection;
 
 import static org.junit.Assert.assertEquals;
 
-
 public class SchemaMigrationTest {
 
   @Test
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 66a53efb87d..11bf0efb1d1 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,13 @@
  */
 package org.sonar.jpa.test;
 
+import static org.junit.Assert.fail;
+
+import java.io.InputStream;
+import java.io.StringWriter;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
 import org.apache.commons.io.IOUtils;
 import org.dbunit.Assertion;
 import org.dbunit.DataSourceDatabaseTester;
@@ -31,9 +38,7 @@ import org.dbunit.dataset.filter.DefaultColumnFilter;
 import org.dbunit.dataset.xml.FlatXmlDataSet;
 import org.dbunit.ext.hsqldb.HsqldbDataTypeFactory;
 import org.dbunit.operation.DatabaseOperation;
-import org.junit.After;
-import org.junit.Assert;
-import org.junit.Before;
+import org.junit.*;
 import org.sonar.api.database.DatabaseSession;
 import org.sonar.jpa.dao.DaoFacade;
 import org.sonar.jpa.dao.MeasuresDao;
@@ -43,29 +48,33 @@ import org.sonar.jpa.session.DatabaseSessionFactory;
 import org.sonar.jpa.session.DefaultDatabaseConnector;
 import org.sonar.jpa.session.JpaDatabaseSession;
 import org.sonar.jpa.session.MemoryDatabaseConnector;
+import org.sonar.persistence.Database;
 import org.sonar.persistence.HsqlDatabase;
-
-import java.io.InputStream;
-import java.io.StringWriter;
-import java.sql.SQLException;
-
-import static org.junit.Assert.fail;
+import org.sonar.persistence.InMemoryDatabase;
 
 public abstract class AbstractDbUnitTestCase {
 
+  private static final boolean USE_HSQL = false;
+
   private DefaultDatabaseConnector dbConnector;
   private JpaDatabaseSession session;
   private DaoFacade dao;
   protected IDatabaseTester databaseTester;
   protected IDatabaseConnection connection;
-  private HsqlDatabase database;
+  private Database database;
 
   @Before
   public void startDatabase() throws Exception {
-    database = new HsqlDatabase();
+    if (USE_HSQL) {
+      database = new HsqlDatabase();
+    } else {
+      database = new InMemoryDatabase();
+    }
+
     database.start();
     dbConnector = new MemoryDatabaseConnector(database);
     dbConnector.start();
+
     session = new JpaDatabaseSession(dbConnector);
     session.start();
 
@@ -75,9 +84,13 @@ public abstract class AbstractDbUnitTestCase {
   @After
   public void stopDatabase() throws Exception {
     databaseTester.onTearDown();
+    // Important: close the connection and session, otherwise tests can stuck
+    if (connection != null) {
+      connection.close();
+    }
+    session.stop();
     dbConnector.stop();
     database.stop();
-
   }
 
   public DaoFacade getDao() {
@@ -136,20 +149,46 @@ public abstract class AbstractDbUnitTestCase {
 
       databaseTester.setDataSet(compositeDataSet);
       connection = databaseTester.getConnection();
-      connection.getConnection().prepareStatement("set referential_integrity FALSE").execute(); // HSQL DB
-      DatabaseConfig config = connection.getConfig();
-      config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory());
+
+      if (USE_HSQL) {
+        connection.getConnection().prepareStatement("set referential_integrity FALSE").execute(); // HSQL DB
+        DatabaseConfig config = connection.getConfig();
+        config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new HsqldbDataTypeFactory());
+      }
 
       DatabaseOperation.CLEAN_INSERT.execute(connection, databaseTester.getDataSet());
 
-      connection.getConnection().prepareStatement("set referential_integrity TRUE").execute(); // HSQL DB
+      if (USE_HSQL) {
+        connection.getConnection().prepareStatement("set referential_integrity TRUE").execute(); // HSQL DB
+      } else {
+        resetDerbySequence(compositeDataSet);
+      }
+
     } catch (Exception e) {
       throw translateException("Could not setup DBUnit data", e);
     }
   }
 
+  private void resetDerbySequence(CompositeDataSet compositeDataSet) throws DataSetException, SQLException {
+    for (ITable table : compositeDataSet.getTables()) {
+      ITableMetaData tableMetaData = table.getTableMetaData();
+      String tableName = tableMetaData.getTableName();
+      for (Column column : tableMetaData.getColumns()) {
+        if ("id".equalsIgnoreCase(column.getColumnName())) { // TODO hard-coded value
+          String maxSql = "SELECT MAX(id) FROM " + tableName;
+          ResultSet res = connection.getConnection().prepareStatement(maxSql).executeQuery();
+          res.next();
+          int max = res.getInt(1);
+          res.close();
+          String alterSql = "ALTER TABLE " + tableName + " ALTER COLUMN id RESTART WITH " + (max + 1);
+          connection.getConnection().prepareStatement(alterSql).execute();
+        }
+      }
+    }
+  }
+
   protected final void checkTables(String testName, String... tables) {
-    checkTables(testName, new String[]{}, tables);
+    checkTables(testName, new String[] {}, tables);
   }
 
   protected final void checkTables(String testName, String[] excludedColumnNames, String... tables) {
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 9442fa16f2b..c806af967eb 100644
--- a/sonar-core/src/test/java/org/sonar/persistence/InMemoryDatabase.java
+++ b/sonar-core/src/test/java/org/sonar/persistence/InMemoryDatabase.java
@@ -27,9 +27,7 @@ import org.sonar.jpa.dialect.Dialect;
 import org.sonar.jpa.session.CustomHibernateConnectionProvider;
 
 import javax.sql.DataSource;
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.SQLException;
+import java.sql.*;
 import java.util.Properties;
 
 /**
@@ -39,21 +37,27 @@ import java.util.Properties;
  */
 public class InMemoryDatabase implements Database {
 
-  private BasicDataSource datasource;
+  private static BasicDataSource datasource;
 
   public InMemoryDatabase start() {
-    startDatabase();
-    createSchema();
+    if (datasource == null) {
+      startDatabase();
+      createSchema();
+    }
+    truncateTables();
     return this;
   }
 
+  /**
+   * IMPORTANT: DB name changed from "sonar" to "sonar2" in order to not conflict with {@link org.sonar.test.persistence.DatabaseTestCase}
+   */
   void startDatabase() {
     try {
       Properties properties = new Properties();
       properties.put("driverClassName", "org.apache.derby.jdbc.EmbeddedDriver");
       properties.put("username", "sonar");
       properties.put("password", "sonar");
-      properties.put("url", "jdbc:derby:memory:sonar;create=true;user=sonar;password=sonar");
+      properties.put("url", "jdbc:derby:memory:sonar2;create=true;user=sonar;password=sonar");
 
       // limit to 2 because of Hibernate and MyBatis
       properties.put("maxActive", "2");
@@ -75,26 +79,58 @@ public class InMemoryDatabase implements Database {
       throw new IllegalStateException("Fail to create schema", e);
 
     } finally {
-      if (connection != null) {
-        try {
-          connection.close();
-        } catch (SQLException e) {
-          // crazy close method !
-        }
+      closeQuietly(connection);
+    }
+  }
+
+  private void truncateTables() {
+    Connection connection = null;
+    try {
+      connection = datasource.getConnection();
+
+      DatabaseMetaData meta = connection.getMetaData();
+
+      ResultSet res = meta.getTables(null, null, null, new String[] { "TABLE" });
+      while (res.next()) {
+        String tableName = res.getString("TABLE_NAME");
+        connection.prepareStatement("TRUNCATE TABLE " + tableName).execute();
+      }
+      res.close();
+
+      // See https://issues.apache.org/jira/browse/DERBY-5403
+      res = meta.getColumns(null, null, null, "ID");
+      while (res.next()) {
+        String tableName = res.getString("TABLE_NAME");
+        connection.prepareStatement("ALTER TABLE " + tableName + " ALTER COLUMN ID RESTART WITH 1").execute();
       }
+      res.close();
+
+    } catch (SQLException e) {
+      throw new IllegalStateException("Fail to truncate tables", e);
+
+    } finally {
+      closeQuietly(connection); // Important, otherwise tests can stuck
     }
   }
 
-  public InMemoryDatabase stop() {
+  void stopDatabase() {
     try {
       if (datasource != null) {
         datasource.close();
       }
-      DriverManager.getConnection("jdbc:derby:memory:sonar;drop=true");
+      DriverManager.getConnection("jdbc:derby:;shutdown=true");
 
     } catch (SQLException e) {
-      // silently ignore stop failure
+      // See http://db.apache.org/derby/docs/dev/getstart/rwwdactivity3.html
+      // XJ015 indicates successful shutdown of Derby
+      // 08006 successful shutdown of a single database
+      if (!"XJ015".equals(e.getSQLState())) {
+        throw new IllegalStateException("Fail to stop Derby", e);
+      }
     }
+  }
+
+  public InMemoryDatabase stop() {
     return this;
   }
 
@@ -113,4 +149,15 @@ public class InMemoryDatabase implements Database {
     properties.put(Environment.CONNECTION_PROVIDER, CustomHibernateConnectionProvider.class.getName());
     return properties;
   }
+
+  private static void closeQuietly(Connection connection) {
+    if (connection != null) {
+      try {
+        connection.close();
+      } catch (SQLException e) {
+        // ignore
+      }
+    }
+  }
+
 }