SONAR-1974 refactor tables RULE_FAILURES and REVIEWS

* Rollback the previous changes on RULE_FAILURES : SNAPSHOT_ID NULLABLE and new column RESOURCE_ID
* Add the columns REVIEWS.RULE_ID (nullable) and MANUAL_VIOLATION (boolean)
* Add experimental MyBatis mapper for reviews
* Add the fields manualViolation (boolean) and permanentId to org.sonar.api.rules.Violation

16 files changed, 606 insertions, 43 deletions

diff --git a/sonar-core/src/main/java/org/sonar/jpa/entity/Review.java b/sonar-core/src/main/java/org/sonar/jpa/entity/Review.java
index 67771fc9bd1..a59a22df9a8 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/entity/Review.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/entity/Review.java
@@ -19,17 +19,12 @@
  */
 package org.sonar.jpa.entity;
 
-import java.util.Date;
-
-import javax.persistence.Column;
-import javax.persistence.Entity;
-import javax.persistence.GeneratedValue;
-import javax.persistence.Id;
-import javax.persistence.Table;
-
 import org.apache.commons.lang.builder.ToStringBuilder;
 import org.apache.commons.lang.builder.ToStringStyle;
 
+import javax.persistence.*;
+import java.util.Date;
+
 @Entity
 @Table(name = "reviews")
 public final class Review {
@@ -75,6 +70,12 @@ public final class Review {
   @Column(name = "severity")
   private String severity;
 
+  @Column(name = "rule_id")
+  private Integer ruleId;
+
+  @Column(name = "manual_violation")
+  private Boolean manualViolation;
+
   /**
    * @return id of review
    */
@@ -150,11 +151,11 @@ public final class Review {
   public void setResourceLine(Integer resourceLine) {
     this.resourceLine = resourceLine;
   }
-  
+
   public Date getUpdatedAt() {
     return updatedAt;
   }
-  
+
   public void setUpdatedAt(Date updatedAt) {
     this.updatedAt = updatedAt;
   }
diff --git a/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java b/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java
index 6c0639a8bfb..6cfd8d15f21 100644
--- a/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java
+++ b/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java
@@ -42,7 +42,7 @@ public class SchemaMigration {
       - complete the Derby DDL file used for unit tests : sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl
 
    */
-  public static final int LAST_VERSION = 233;
+  public static final int LAST_VERSION = 232;
 
   public final static String TABLE_NAME = "schema_migrations";
 
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 f0978cdb248..55871295384 100644
--- a/sonar-core/src/main/java/org/sonar/persistence/MyBatis.java
+++ b/sonar-core/src/main/java/org/sonar/persistence/MyBatis.java
@@ -19,9 +19,6 @@
  */
 package org.sonar.persistence;
 
-import java.io.IOException;
-import java.io.InputStream;
-
 import org.apache.commons.io.IOUtils;
 import org.apache.commons.lang.StringUtils;
 import org.apache.ibatis.builder.xml.XMLMapperBuilder;
@@ -30,10 +27,10 @@ import org.apache.ibatis.session.*;
 import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
 import org.sonar.api.BatchComponent;
 import org.sonar.api.ServerComponent;
-import org.sonar.persistence.model.DuplicationMapper;
-import org.sonar.persistence.model.DuplicationUnit;
-import org.sonar.persistence.model.Rule;
-import org.sonar.persistence.model.RuleMapper;
+import org.sonar.persistence.model.*;
+
+import java.io.IOException;
+import java.io.InputStream;
 
 public class MyBatis implements BatchComponent, ServerComponent {
 
@@ -52,8 +49,10 @@ public class MyBatis implements BatchComponent, ServerComponent {
 
     loadAlias(conf, "DuplicationUnit", DuplicationUnit.class);
     loadAlias(conf, "Rule", Rule.class);
+    loadAlias(conf, "Review", Review.class);
     loadMapper(conf, DuplicationMapper.class);
     loadMapper(conf, RuleMapper.class);
+    loadMapper(conf, ReviewMapper.class);
 
     sessionFactory = new SqlSessionFactoryBuilder().build(conf);
     return this;
diff --git a/sonar-core/src/main/java/org/sonar/persistence/dao/DaoUtils.java b/sonar-core/src/main/java/org/sonar/persistence/dao/DaoUtils.java
index 3af77afc30a..0c3fb5dfb90 100644
--- a/sonar-core/src/main/java/org/sonar/persistence/dao/DaoUtils.java
+++ b/sonar-core/src/main/java/org/sonar/persistence/dao/DaoUtils.java
@@ -27,6 +27,6 @@ public final class DaoUtils {
   }
 
   public static List<Class> getDaoClasses() {
-    return Arrays.<Class>asList(RuleDao.class, DuplicationDao.class);
+    return Arrays.<Class>asList(RuleDao.class, DuplicationDao.class, ReviewDao.class);
   }
 }
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 149ca16b600..a781ac25fae 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,12 +19,6 @@
  */
 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;
@@ -33,6 +27,9 @@ import org.sonar.persistence.MyBatis;
 import org.sonar.persistence.model.DuplicationMapper;
 import org.sonar.persistence.model.DuplicationUnit;
 
+import java.util.Collection;
+import java.util.List;
+
 public class DuplicationDao implements BatchComponent, ServerComponent {
 
   private final MyBatis mybatis;
@@ -63,7 +60,7 @@ public class DuplicationDao implements BatchComponent, ServerComponent {
         mapper.batchInsert(unit);
       }
       session.commit();
-      
+
     } finally {
       session.close();
     }
diff --git a/sonar-core/src/main/java/org/sonar/persistence/dao/ReviewDao.java b/sonar-core/src/main/java/org/sonar/persistence/dao/ReviewDao.java
new file mode 100644
index 00000000000..666a9d5ef9f
--- /dev/null
+++ b/sonar-core/src/main/java/org/sonar/persistence/dao/ReviewDao.java
@@ -0,0 +1,68 @@
+/*
+ * 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.apache.ibatis.session.SqlSession;
+import org.sonar.api.BatchComponent;
+import org.sonar.api.ServerComponent;
+import org.sonar.persistence.MyBatis;
+import org.sonar.persistence.model.Review;
+import org.sonar.persistence.model.ReviewMapper;
+import org.sonar.persistence.model.ReviewQuery;
+
+import java.util.List;
+
+public class ReviewDao implements BatchComponent, ServerComponent {
+  private final MyBatis mybatis;
+
+  public ReviewDao(MyBatis mybatis) {
+    this.mybatis = mybatis;
+  }
+
+  public Review selectById(long id) {
+    SqlSession sqlSession = mybatis.openSession();
+    try {
+      ReviewMapper mapper = sqlSession.getMapper(ReviewMapper.class);
+      return mapper.selectById(id);
+    } finally {
+      sqlSession.close();
+    }
+  }
+
+  public List<Review> selectByResource(int resourceId) {
+    SqlSession sqlSession = mybatis.openSession();
+    try {
+      ReviewMapper mapper = sqlSession.getMapper(ReviewMapper.class);
+      return mapper.selectByResource(resourceId);
+    } finally {
+      sqlSession.close();
+    }
+  }
+
+  public List<Review> selectByQuery(ReviewQuery query) {
+    SqlSession sqlSession = mybatis.openSession();
+    try {
+      ReviewMapper mapper = sqlSession.getMapper(ReviewMapper.class);
+      return mapper.selectByQuery(query);
+    } finally {
+      sqlSession.close();
+    }
+  }
+}
diff --git a/sonar-core/src/main/java/org/sonar/persistence/model/Review.java b/sonar-core/src/main/java/org/sonar/persistence/model/Review.java
new file mode 100644
index 00000000000..24877895b66
--- /dev/null
+++ b/sonar-core/src/main/java/org/sonar/persistence/model/Review.java
@@ -0,0 +1,165 @@
+/*
+ * 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.model;
+
+import java.util.Date;
+
+/**
+ * @since 2.13
+ */
+public class Review {
+  private Long id;
+  private Integer userId;
+  private Integer assigneeId;
+  private String title;
+  private String status;
+  private String resolution;
+  private Integer violationPermanentId;
+  private Integer projectId;
+  private Integer resourceId;
+  private Integer line;
+  private Date createdAt;
+  private Date updatedAt;
+  private String severity;
+  private Integer ruleId;
+  private Boolean manualViolation;
+
+  public Long getId() {
+    return id;
+  }
+
+  public Review setId(Long id) {
+    this.id = id;
+    return this;
+  }
+
+  public Integer getUserId() {
+    return userId;
+  }
+
+  public Review setUserId(Integer userId) {
+    this.userId = userId;
+    return this;
+  }
+
+  public Integer getAssigneeId() {
+    return assigneeId;
+  }
+
+  public void setAssigneeId(Integer assigneeId) {
+    this.assigneeId = assigneeId;
+  }
+
+  public String getTitle() {
+    return title;
+  }
+
+  public void setTitle(String title) {
+    this.title = title;
+  }
+
+  public String getStatus() {
+    return status;
+  }
+
+  public void setStatus(String status) {
+    this.status = status;
+  }
+
+  public String getResolution() {
+    return resolution;
+  }
+
+  public void setResolution(String resolution) {
+    this.resolution = resolution;
+  }
+
+  public Integer getViolationPermanentId() {
+    return violationPermanentId;
+  }
+
+  public void setViolationPermanentId(Integer violationPermanentId) {
+    this.violationPermanentId = violationPermanentId;
+  }
+
+  public Integer getProjectId() {
+    return projectId;
+  }
+
+  public void setProjectId(Integer projectId) {
+    this.projectId = projectId;
+  }
+
+  public Integer getResourceId() {
+    return resourceId;
+  }
+
+  public void setResourceId(Integer resourceId) {
+    this.resourceId = resourceId;
+  }
+
+  public Integer getLine() {
+    return line;
+  }
+
+  public void setLine(Integer line) {
+    this.line = line;
+  }
+
+  public Date getCreatedAt() {
+    return createdAt;
+  }
+
+  public void setCreatedAt(Date createdAt) {
+    this.createdAt = createdAt;
+  }
+
+  public Date getUpdatedAt() {
+    return updatedAt;
+  }
+
+  public void setUpdatedAt(Date updatedAt) {
+    this.updatedAt = updatedAt;
+  }
+
+  public String getSeverity() {
+    return severity;
+  }
+
+  public void setSeverity(String severity) {
+    this.severity = severity;
+  }
+
+  public Integer getRuleId() {
+    return ruleId;
+  }
+
+  public void setRuleId(Integer ruleId) {
+    this.ruleId = ruleId;
+  }
+
+  public Boolean getManualViolation() {
+    return manualViolation;
+  }
+
+  public void setManualViolation(Boolean manualViolation) {
+    this.manualViolation = manualViolation;
+  }
+}
diff --git a/sonar-core/src/main/java/org/sonar/persistence/model/ReviewMapper.java b/sonar-core/src/main/java/org/sonar/persistence/model/ReviewMapper.java
new file mode 100644
index 00000000000..82b39c74356
--- /dev/null
+++ b/sonar-core/src/main/java/org/sonar/persistence/model/ReviewMapper.java
@@ -0,0 +1,33 @@
+/*
+ * 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.model;
+
+import java.util.List;
+
+/**
+ * @since 2.13
+ */
+public interface ReviewMapper {
+  Review selectById(long id);
+
+  List<Review> selectByResource(int resourceId);
+
+  List<Review> selectByQuery(ReviewQuery query);
+}
diff --git a/sonar-core/src/main/java/org/sonar/persistence/model/ReviewQuery.java b/sonar-core/src/main/java/org/sonar/persistence/model/ReviewQuery.java
new file mode 100644
index 00000000000..69ee246e600
--- /dev/null
+++ b/sonar-core/src/main/java/org/sonar/persistence/model/ReviewQuery.java
@@ -0,0 +1,100 @@
+/*
+ * 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.model;
+
+public final class ReviewQuery {
+  private Boolean manualViolation;
+  private Integer resourceId;
+  private Integer userId;
+  private Integer violationPermanentId;
+  private Integer ruleId;
+  private String status;
+  private String resolution;
+
+  private ReviewQuery() {
+  }
+
+  public static ReviewQuery create() {
+    return new ReviewQuery();
+  }
+
+  public Boolean getManualViolation() {
+    return manualViolation;
+  }
+
+  public ReviewQuery setManualViolation(Boolean manualViolation) {
+    this.manualViolation = manualViolation;
+    return this;
+  }
+
+  public Integer getResourceId() {
+    return resourceId;
+  }
+
+  public ReviewQuery setResourceId(Integer resourceId) {
+    this.resourceId = resourceId;
+    return this;
+  }
+
+  public String getStatus() {
+    return status;
+  }
+
+  public ReviewQuery setStatus(String status) {
+    this.status = status;
+    return this;
+  }
+
+  public Integer getUserId() {
+    return userId;
+  }
+
+  public ReviewQuery setUserId(Integer userId) {
+    this.userId = userId;
+    return this;
+  }
+
+  public Integer getViolationPermanentId() {
+    return violationPermanentId;
+  }
+
+  public ReviewQuery setViolationPermanentId(Integer violationPermanentId) {
+    this.violationPermanentId = violationPermanentId;
+    return this;
+  }
+
+  public Integer getRuleId() {
+    return ruleId;
+  }
+
+  public ReviewQuery setRuleId(Integer ruleId) {
+    this.ruleId = ruleId;
+    return this;
+  }
+
+  public String getResolution() {
+    return resolution;
+  }
+
+  public ReviewQuery setResolution(String resolution) {
+    this.resolution = resolution;
+    return this;
+  }
+}
diff --git a/sonar-core/src/main/resources/org/sonar/persistence/model/ReviewMapper.xml b/sonar-core/src/main/resources/org/sonar/persistence/model/ReviewMapper.xml
new file mode 100644
index 00000000000..1a534b57c96
--- /dev/null
+++ b/sonar-core/src/main/resources/org/sonar/persistence/model/ReviewMapper.xml
@@ -0,0 +1,51 @@
+<?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.ReviewMapper">
+
+  <resultMap id="reviewResultMap" type="Review">
+    <result property="createdAt" column="created_at"/>
+    <result property="updatedAt" column="updated_at"/>
+    <result property="userId" column="user_id"/>
+    <result property="assigneeId" column="assignee_id"/>
+    <result property="violationPermanentId" column="rule_failure_permanent_id"/>
+    <result property="projectId" column="project_id"/>
+    <result property="resourceId" column="resource_id"/>
+    <result property="line" column="resource_line"/>
+    <result property="ruleId" column="rule_id"/>
+    <result property="manualViolation" column="manual_violation"/>
+  </resultMap>
+
+  <sql id="reviewColumns">id, created_at, updated_at, user_id, assignee_id, title,status,resolution,rule_failure_permanent_id,project_id, resource_id, resource_line, severity, rule_id,
+    manual_violation
+  </sql>
+
+  <select id="selectById" parameterType="long" resultMap="reviewResultMap">
+    select
+    <include refid="reviewColumns"/>
+    from REVIEWS where id=#{id}
+  </select>
+
+  <select id="selectByResource" parameterType="int" resultMap="reviewResultMap">
+    select
+    <include refid="reviewColumns"/>
+    from REVIEWS where resource_id=#{id}
+  </select>
+
+  <select id="selectByQuery" parameterType="org.sonar.persistence.model.ReviewQuery" resultMap="reviewResultMap">
+    select
+    <include refid="reviewColumns"/>
+    from REVIEWS
+    <where>
+      <if test="userId != null">user_id = #{userId}</if>
+      <if test="violationPermanentId != null">AND rule_failure_permanent_id = #{violationPermanentId}</if>
+      <if test="ruleId != null">AND rule_id = #{ruleId}</if>
+      <if test="resourceId != null">AND resource_id = #{resourceId}</if>
+      <if test="status != null">AND status = #{status}</if>
+      <if test="manualViolation != null">AND manual_violation = #{manualViolation}</if>
+      <if test="resolution != null">AND resolution = #{resolution}</if>
+    </where>
+  </select>
+
+</mapper>
+
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..6e25a64cd42 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
@@ -2,13 +2,12 @@
 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 
 <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>
+  <select id="selectAll" resultType="Rule">
+    select id, plugin_rule_key as "ruleKey", plugin_name as "repositoryKey", description, enabled, name from 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>
+    select id, plugin_rule_key as "ruleKey", plugin_name as "repositoryKey", description, enabled, name from RULES WHERE id=#{id}
+  </select>
 </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 d6861e0e7c9..b9b04e14ea6 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
@@ -167,7 +167,6 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('222');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('230');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('231');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('232');
-INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('233');
 
 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;
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 495d306aab3..f5d5c323dfd 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
@@ -312,7 +312,9 @@ CREATE TABLE "REVIEWS" (
   "PROJECT_ID" INTEGER,
   "RESOURCE_ID" INTEGER,
   "RESOURCE_LINE" INTEGER,
-  "RESOLUTION" VARCHAR(200)
+  "RESOLUTION" VARCHAR(200),
+  "RULE_ID" INTEGER,
+  "MANUAL_VIOLATION" BOOLEAN
 );
 
 CREATE TABLE "RULES_CATEGORIES" (
@@ -421,7 +423,7 @@ CREATE TABLE "DASHBOARDS" (
 
 CREATE TABLE "RULE_FAILURES" (
   "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
-  "SNAPSHOT_ID" INTEGER,
+  "SNAPSHOT_ID" INTEGER NOT NULL,
   "RULE_ID" INTEGER NOT NULL,
   "FAILURE_LEVEL" INTEGER NOT NULL,
   "MESSAGE" VARCHAR(4000),
@@ -430,8 +432,7 @@ CREATE TABLE "RULE_FAILURES" (
   "CREATED_AT" TIMESTAMP,
   "CHECKSUM" VARCHAR(1000),
   "PERMANENT_ID" INTEGER,
-  "SWITCHED_OFF" BOOLEAN,
-  "RESOURCE_ID" INTEGER
+  "SWITCHED_OFF" BOOLEAN
 );
 
 CREATE TABLE "METRICS" (
diff --git a/sonar-core/src/test/java/org/sonar/persistence/dao/ReviewDaoTest.java b/sonar-core/src/test/java/org/sonar/persistence/dao/ReviewDaoTest.java
new file mode 100644
index 00000000000..e9d7db583b5
--- /dev/null
+++ b/sonar-core/src/test/java/org/sonar/persistence/dao/ReviewDaoTest.java
@@ -0,0 +1,100 @@
+/*
+ * 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.junit.Before;
+import org.junit.Test;
+import org.sonar.persistence.model.Review;
+import org.sonar.persistence.model.ReviewQuery;
+
+import java.util.List;
+
+import static org.hamcrest.Matchers.anyOf;
+import static org.hamcrest.Matchers.is;
+import static org.junit.Assert.assertNull;
+import static org.junit.Assert.assertThat;
+
+public class ReviewDaoTest extends DaoTestCase {
+
+  private ReviewDao dao;
+
+  @Before
+  public void createDao() throws Exception {
+    dao = new ReviewDao(getMyBatis());
+  }
+
+  @Test
+  public void shouldSelectById() throws Exception {
+    setupData("shared");
+
+    Review review = dao.selectById(100L);
+    assertThat(review.getId(), is(100L));
+    assertThat(review.getStatus(), is("OPEN"));
+    assertThat(review.getResolution(), is("RESOLVE"));
+    assertThat(review.getProjectId(), is(20));
+    assertThat(review.getViolationPermanentId(), is(1));
+    assertThat(review.getSeverity(), is("BLOCKER"));
+    assertThat(review.getUserId(), is(300));
+    assertThat(review.getResourceId(), is(400));
+    assertThat(review.getRuleId(), is(500));
+    assertThat(review.getManualViolation(), is(true));
+  }
+
+  @Test
+  public void shouldReturnNullIfIdNotFound() throws Exception {
+    setupData("shared");
+
+    assertNull(dao.selectById(12345L));
+  }
+
+  @Test
+  public void shouldSelectByResource() throws Exception {
+    setupData("shared");
+
+    List<Review> reviews = dao.selectByResource(400);
+    assertThat(reviews.size(), is(2));
+    for (Review review : reviews) {
+      assertThat(review.getId(), anyOf(is(100L), is(101L)));
+      assertThat(review.getResourceId(), is(400));
+    }
+  }
+
+  @Test
+  public void shouldSelectByQuery() throws Exception {
+    setupData("shared");
+
+    List<Review> reviews = dao.selectByQuery(ReviewQuery.create().setResourceId(400));
+    assertThat(reviews.size(), is(2));
+    for (Review review : reviews) {
+      assertThat(review.getId(), anyOf(is(100L), is(101L)));
+      assertThat(review.getResourceId(), is(400));
+    }
+  }
+
+  @Test
+  public void shouldSelectByQuery_booleanCriteria() throws Exception {
+    setupData("shared");
+
+    List<Review> reviews = dao.selectByQuery(ReviewQuery.create().setResourceId(400).setManualViolation(true));
+    assertThat(reviews.size(), is(1));
+    assertThat(reviews.get(0).getId(), is(100L));
+    assertThat(reviews.get(0).getManualViolation(), is(Boolean.TRUE));
+  }
+}
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 70dc8696247..d90abfa51a2 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
@@ -19,14 +19,13 @@
  */
 package org.sonar.persistence.dao;
 
-import static org.junit.Assert.assertThat;
-
-import java.util.List;
-
 import org.hamcrest.core.Is;
 import org.junit.Before;
 import org.junit.Test;
-import org.sonar.jpa.test.AbstractDbUnitTestCase;
+
+import java.util.List;
+
+import static org.junit.Assert.assertThat;
 
 public class RuleDaoTest extends DaoTestCase {
 
@@ -53,7 +52,7 @@ public class RuleDaoTest extends DaoTestCase {
 
   @Test
   public void testSelectById() throws Exception {
-    setupData("selectById");
+    setupData("select");
     org.sonar.persistence.model.Rule rule = dao.selectById(2L);
 
     assertThat(rule.getId(), Is.is(2L));
diff --git a/sonar-core/src/test/resources/org/sonar/persistence/dao/ReviewDaoTest/shared.xml b/sonar-core/src/test/resources/org/sonar/persistence/dao/ReviewDaoTest/shared.xml
new file mode 100644
index 00000000000..59053089536
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/persistence/dao/ReviewDaoTest/shared.xml
@@ -0,0 +1,51 @@
+<dataset>
+
+  <!-- First resource -->
+  <reviews
+      id="100"
+      status="OPEN"
+      rule_failure_permanent_id="1"
+      resolution="RESOLVE"
+      created_at="[null]"
+      updated_at="[null]"
+      project_id="20"
+      resource_line="200"
+      severity="BLOCKER"
+      user_id="300"
+      resource_id="400"
+      rule_id="500"
+      manual_violation="true"/>
+
+  <reviews
+      id="101"
+      status="CLOSED"
+      rule_failure_permanent_id="1"
+      resolution="RESOLVE"
+      created_at="[null]"
+      updated_at="[null]"
+      project_id="30"
+      resource_line="120"
+      severity="MAJOR"
+      user_id="300"
+      resource_id="400"
+      rule_id="505"
+      manual_violation="false"/>
+
+
+  <!-- Second resource -->
+  <reviews
+      id="102"
+      status="OPEN"
+      rule_failure_permanent_id="1"
+      resolution="RESOLVE"
+      created_at="[null]"
+      updated_at="[null]"
+      project_id="20"
+      resource_line="200"
+      severity="BLOCKER"
+      user_id="300"
+      resource_id="401"
+      rule_id="500"
+      manual_violation="true"/>
+
+</dataset>