SONAR-8867 populate table RULES_METADATA

6 files changed, 495 insertions, 2 deletions

diff --git a/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql b/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql
index 6ab5e9034ca..a4705b31fc7 100644
--- a/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql
+++ b/server/sonar-db-core/src/main/resources/org/sonar/db/version/rows-h2.sql
@@ -548,6 +548,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1612');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1613');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1614');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1615');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1616');
 
 INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, IS_ROOT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', false, '1418215735482', '1418215735482');
 ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2;
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
index 80f5e5d1555..5ee08ddf7b9 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64.java
@@ -42,6 +42,7 @@ public class DbVersion64 implements DbVersion {
       .add(1612, "Extend size of column LOADED_TEMPLATES.TEMPLATE_TYPE", ExtendLoadedTemplateTypeColumn.class)
       .add(1613, "Add index LOADED_TEMPLATES_TYPE", AddIndexLoadedTemplatesType.class)
       .add(1614, "Upgrade loaded template entries for quality profiles", UpgradeQualityTemplateLoadedTemplates.class)
-      .add(1615, "Create table RULES_METADATA", CreateRulesMetadata.class);
+      .add(1615, "Create table RULES_METADATA", CreateRulesMetadata.class)
+      .add(1616, "Populate table RULES_METADATA", PopulateRulesMetadata.class);
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadata.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadata.java
new file mode 100644
index 00000000000..873b10f240a
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadata.java
@@ -0,0 +1,126 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program 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.
+ *
+ * This program 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 this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v64;
+
+import java.sql.SQLException;
+import java.util.Date;
+import org.sonar.api.utils.System2;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+import org.sonar.server.platform.db.migration.step.MassUpdate;
+import org.sonar.server.platform.db.migration.step.Select;
+import org.sonar.server.platform.db.migration.step.SqlStatement;
+import org.sonar.server.platform.db.migration.version.v63.DefaultOrganizationUuid;
+
+public class PopulateRulesMetadata extends DataChange {
+  private final DefaultOrganizationUuid defaultOrganizationUuid;
+  private final System2 system2;
+
+  public PopulateRulesMetadata(Database db, DefaultOrganizationUuid defaultOrganizationUuid, System2 system2) {
+    super(db);
+    this.defaultOrganizationUuid = defaultOrganizationUuid;
+    this.system2 = system2;
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    String defaultOrganizationUuid = this.defaultOrganizationUuid.getAndCheck(context);
+
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select" +
+      " id," +
+      " note_data," +
+      " note_user_login," +
+      " note_created_at," +
+      " note_updated_at," +
+      " remediation_function," +
+      " remediation_gap_mult," +
+      " remediation_base_effort," +
+      " tags," +
+      " created_at," +
+      " updated_at" +
+      " from" +
+      "  rules r" +
+      " where" +
+      "  not exists (select 1 from rules_metadata rm where rm.rule_id = r.id)");
+    massUpdate.rowPluralName("rules metadata");
+    massUpdate.update("insert into rules_metadata" +
+      " (" +
+      " rule_id," +
+      " organization_uuid," +
+      " note_data," +
+      " note_user_login," +
+      " note_created_at," +
+      " note_updated_at," +
+      " remediation_function," +
+      " remediation_gap_mult," +
+      " remediation_base_effort," +
+      " tags," +
+      " created_at," +
+      " updated_at" +
+      ")" +
+      "values" +
+      "(" +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?," +
+      " ?" +
+      ")");
+    massUpdate.execute((row, update) -> handle(defaultOrganizationUuid, row, update));
+  }
+
+  private boolean handle(String defaultOrganizationUuid, Select.Row row, SqlStatement update) throws SQLException {
+    long now = system2.now();
+    int ruleId = row.getInt(1);
+    String noteData = row.getNullableString(2);
+    String noteUserLogin = row.getNullableString(3);
+    Date noteCreatedAt = row.getNullableDate(4);
+    Date noteUpdatedAt = row.getNullableDate(5);
+    String remediationFunction = row.getNullableString(6);
+    String remediationGapMultiplier = row.getNullableString(7);
+    String remediationBaseEffort = row.getNullableString(8);
+    String tags = row.getNullableString(9);
+    Long createdAt = row.getNullableLong(10);
+    Long updatedAt = row.getNullableLong(11);
+
+    update
+      .setInt(1, ruleId)
+      .setString(2, defaultOrganizationUuid)
+      .setString(3, noteData)
+      .setString(4, noteUserLogin)
+      .setLong(5, noteCreatedAt == null ? null : noteCreatedAt.getTime())
+      .setLong(6, noteUpdatedAt == null ? null : noteUpdatedAt.getTime())
+      .setString(7, remediationFunction)
+      .setString(8, remediationGapMultiplier)
+      .setString(9, remediationBaseEffort)
+      .setString(10, tags)
+      .setLong(11, createdAt == null ? now : createdAt)
+      .setLong(12, updatedAt == null ? now : updatedAt);
+    return true;
+  }
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
index 2b68829c2a0..4ca458bbdde 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/DbVersion64Test.java
@@ -35,7 +35,7 @@ public class DbVersion64Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 16);
+    verifyMigrationCount(underTest, 17);
   }
 
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadataTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadataTest.java
new file mode 100644
index 00000000000..414c1e5b2e7
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadataTest.java
@@ -0,0 +1,292 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program 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.
+ *
+ * This program 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 this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v64;
+
+import java.sql.SQLException;
+import java.util.Date;
+import java.util.Map;
+import javax.annotation.CheckForNull;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.ExpectedException;
+import org.sonar.api.utils.System2;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.version.v63.DefaultOrganizationUuidImpl;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
+public class PopulateRulesMetadataTest {
+  private static final String TABLE_RULES_METADATA = "rules_metadata";
+  private static final String TABLE_RULES = "rules";
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(PopulateRulesMetadataTest.class, "rules_and_rules_metadata_and_organization_and_internal_properties.sql");
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  private System2 system2 = mock(System2.class);
+  private PopulateRulesMetadata underTest = new PopulateRulesMetadata(db.database(), new DefaultOrganizationUuidImpl(), system2);
+
+  @Test
+  public void fails_with_ISE_when_no_default_organization_is_set() throws SQLException {
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("Default organization uuid is missing");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void fails_with_ISE_when_default_organization_does_not_exist_in_table_ORGANIZATIONS() throws SQLException {
+    db.defaultOrganization().insertInternalProperty("blabla");
+
+    expectedException.expect(IllegalStateException.class);
+    expectedException.expectMessage("Default organization with uuid 'blabla' does not exist in table ORGANIZATIONS");
+
+    underTest.execute();
+  }
+
+  @Test
+  public void execute_has_no_effect_if_loaded_templates_table_is_empty() throws Exception {
+    db.defaultOrganization().setupDefaultOrganization();
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_RULES_METADATA)).isEqualTo(0);
+  }
+
+  @Test
+  public void execute_has_no_effect_if_rules_is_empty() throws Exception {
+    db.defaultOrganization().setupDefaultOrganization();
+
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable(TABLE_RULES_METADATA)).isEqualTo(0);
+    assertThat(db.countRowsOfTable(TABLE_RULES)).isEqualTo(0);
+  }
+
+  @Test
+  public void execute_creates_row_with_data_from_table_rules_for_each_row_in_table_rules() throws SQLException {
+    String defaultOrganizationUuid = db.defaultOrganization().setupDefaultOrganization();
+    Metadata[] metadatas = {
+      new Metadata(),
+      new Metadata("foo"),
+      new Metadata().withNote("bar"),
+      new Metadata().withRemediation("doh"),
+      new Metadata().withTags("taggeah!")
+    };
+    Dates noDates = new Dates();
+    Dates dates = new Dates(122_334);
+    long now = 666_666_666L;
+    Dates defaultDates = new Dates(now, now);
+    long[] ruleIds = {
+      insertRule("r10", noDates, metadatas[0]),
+      insertRule("r11", dates, metadatas[0]),
+      insertRule("r20", noDates, metadatas[1]),
+      insertRule("r21", dates, metadatas[1]),
+      insertRule("r30", noDates, metadatas[2]),
+      insertRule("r31", dates, metadatas[2]),
+      insertRule("r40", noDates, metadatas[3]),
+      insertRule("r41", dates, metadatas[3]),
+      insertRule("r50", noDates, metadatas[4]),
+      insertRule("r51", dates, metadatas[4])
+    };
+    when(system2.now()).thenReturn(now);
+
+    underTest.execute();
+
+    verifyRulesMetadata(ruleIds[0], defaultOrganizationUuid, defaultDates, metadatas[0]);
+    verifyRulesMetadata(ruleIds[1], defaultOrganizationUuid, dates, metadatas[0]);
+    verifyRulesMetadata(ruleIds[2], defaultOrganizationUuid, defaultDates, metadatas[1]);
+    verifyRulesMetadata(ruleIds[3], defaultOrganizationUuid, dates, metadatas[1]);
+    verifyRulesMetadata(ruleIds[4], defaultOrganizationUuid, defaultDates, metadatas[2]);
+    verifyRulesMetadata(ruleIds[5], defaultOrganizationUuid, dates, metadatas[2]);
+    verifyRulesMetadata(ruleIds[6], defaultOrganizationUuid, defaultDates, metadatas[3]);
+    verifyRulesMetadata(ruleIds[7], defaultOrganizationUuid, dates, metadatas[3]);
+    verifyRulesMetadata(ruleIds[8], defaultOrganizationUuid, defaultDates, metadatas[4]);
+    verifyRulesMetadata(ruleIds[9], defaultOrganizationUuid, dates, metadatas[4]);
+  }
+
+  @Test
+  public void execute_creates_does_not_update_rows_in_table_RULES_METADATA() throws SQLException {
+    db.defaultOrganization().setupDefaultOrganization();
+    long ruleId = insertRule("r1", new Dates(999), new Metadata("foo"));
+    insertRuleMetadata(ruleId, "other org uuid", new Dates(1_000));
+
+    underTest.execute();
+
+    verifyRulesMetadata(ruleId, "other org uuid", new Dates(1_000), new Metadata());
+  }
+
+  @Test
+  public void execute_is_reentrant() throws SQLException {
+    db.defaultOrganization().setupDefaultOrganization();
+    insertRule("r1", new Dates(999), new Metadata("foo"));
+    insertRule("r2", new Dates(10_888), new Metadata("bar"));
+
+    underTest.execute();
+
+    underTest.execute();
+  }
+
+  private void insertRuleMetadata(long ruleId, String organizationUuid, Dates dates) {
+    db.executeInsert(
+      "rules_metadata",
+      "RULE_ID", ruleId,
+      "ORGANIZATION_UUID", organizationUuid,
+      "CREATED_AT", dates.getCreatedAt(),
+      "UPDATED_AT", dates.getUpdatedAt());
+  }
+
+  private void verifyRulesMetadata(long ruleId, String organizationUuid, Dates dates, Metadata metadata) {
+    Map<String, Object> row = db.selectFirst("select" +
+      " ORGANIZATION_UUID as \"organization\"," +
+      " NOTE_DATA as \"noteData\"," +
+      " NOTE_USER_LOGIN as \"noteUserLogin\"," +
+      " NOTE_CREATED_AT as \"noteCreatedAt\"," +
+      " NOTE_UPDATED_AT as \"noteUpdatedAt\"," +
+      " REMEDIATION_FUNCTION as \"function\"," +
+      " REMEDIATION_GAP_MULT as \"gap\"," +
+      " REMEDIATION_BASE_EFFORT as \"baseEffort\"," +
+      " TAGS as \"tags\"," +
+      " CREATED_AT as \"createdAt\"," +
+      " UPDATED_AT as \"updateAt\"" +
+      " from rules_metadata" +
+      " where rule_id = " + ruleId);
+    assertThat(row.get("organization")).isEqualTo(organizationUuid);
+    assertThat(row.get("noteData")).isEqualTo(metadata.noteData);
+    assertThat(row.get("noteUserLogin")).isEqualTo(metadata.noteUserLogin);
+    assertThat(row.get("noteCreatedAt")).isEqualTo(metadata.noteDates.getCreatedAt());
+    assertThat(row.get("noteUpdatedAt")).isEqualTo(metadata.noteDates.getUpdatedAt());
+    assertThat(row.get("function")).isEqualTo(metadata.remediationFunction);
+    assertThat(row.get("gap")).isEqualTo(metadata.remediationGapMult);
+    assertThat(row.get("baseEffort")).isEqualTo(metadata.remediationBaseEffort);
+    assertThat(row.get("tags")).isEqualTo(metadata.tags);
+    assertThat(row.get("createdAt")).isEqualTo(dates.getCreatedAt());
+    assertThat(row.get("updateAt")).isEqualTo(dates.getUpdatedAt());
+  }
+
+  private long insertRule(String key, Dates dates, Metadata metadata) {
+    db.executeInsert(
+      TABLE_RULES,
+      "PLUGIN_RULE_KEY", key,
+      "PLUGIN_NAME", "name_" + key,
+      "NOTE_DATA", metadata.noteData,
+      "NOTE_USER_LOGIN", metadata.noteUserLogin,
+      "NOTE_CREATED_AT", metadata.noteDates.getCreatedAtDate(),
+      "NOTE_UPDATED_AT", metadata.noteDates.getUpdatedAtDate(),
+      "REMEDIATION_FUNCTION", metadata.remediationFunction,
+      "REMEDIATION_GAP_MULT", metadata.remediationGapMult,
+      "REMEDIATION_BASE_EFFORT", metadata.remediationBaseEffort,
+      "TAGS", metadata.tags,
+      "CREATED_AT", dates.getCreatedAt(),
+      "UPDATED_AT", dates.getUpdatedAt());
+    return (long) db.selectFirst("select ID as \"id\" from RULES where PLUGIN_RULE_KEY='" + key + "'")
+      .get("id");
+  }
+
+  private static final class Metadata {
+    private String noteData;
+    private String noteUserLogin;
+    private Dates noteDates;
+    private String remediationFunction;
+    private String remediationGapMult;
+    private String remediationBaseEffort;
+    private String tags;
+
+    private Metadata() {
+      this.noteData = null;
+      this.noteUserLogin = null;
+      this.noteDates = new Dates();
+      this.remediationFunction = null;
+      this.remediationGapMult = null;
+      this.remediationBaseEffort = null;
+      this.tags = null;
+    }
+
+    private Metadata(String seed) {
+      withNote(seed);
+      withRemediation(seed);
+      this.tags = seed + "_tags";
+    }
+
+    private Metadata withNote(String seed) {
+      this.noteData = seed + "_noteData";
+      this.noteUserLogin = seed + "_noteUserLogin";
+      this.noteDates = new Dates(seed.hashCode());
+      ;
+      return this;
+    }
+
+    private Metadata withTags(String tags) {
+      this.tags = tags;
+      return this;
+    }
+
+    private Metadata withRemediation(String seed) {
+      this.remediationFunction = seed + "_Function";
+      this.remediationGapMult = seed + "_GapMult";
+      this.remediationBaseEffort = seed + "_BaseEffort";
+      return this;
+    }
+  }
+
+  private static final class Dates {
+    private final Long createdAt;
+    private final Long updatedAt;
+
+    private Dates() {
+      this.createdAt = null;
+      this.updatedAt = null;
+    }
+
+    private Dates(long seed) {
+      this.createdAt = seed + 5_778_765L;
+      this.updatedAt = seed + 9_111_100L;
+    }
+
+    public Dates(long createdAt, long updatedAt) {
+      this.createdAt = createdAt;
+      this.updatedAt = updatedAt;
+    }
+
+    @CheckForNull
+    public Long getCreatedAt() {
+      return createdAt;
+    }
+
+    @CheckForNull
+    public Date getCreatedAtDate() {
+      return createdAt == null ? null : new Date(createdAt);
+    }
+
+    @CheckForNull
+    public Long getUpdatedAt() {
+      return updatedAt;
+    }
+
+    @CheckForNull
+    public Date getUpdatedAtDate() {
+      return updatedAt == null ? null : new Date(updatedAt);
+    }
+  }
+
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadataTest/rules_and_rules_metadata_and_organization_and_internal_properties.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadataTest/rules_and_rules_metadata_and_organization_and_internal_properties.sql
new file mode 100644
index 00000000000..6337884e8cf
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v64/PopulateRulesMetadataTest/rules_and_rules_metadata_and_organization_and_internal_properties.sql
@@ -0,0 +1,73 @@
+CREATE TABLE "ORGANIZATIONS" (
+  "UUID" VARCHAR(40) NOT NULL PRIMARY KEY,
+  "KEE" VARCHAR(32) NOT NULL,
+  "NAME" VARCHAR(64) NOT NULL,
+  "DESCRIPTION" VARCHAR(256),
+  "URL" VARCHAR(256),
+  "AVATAR_URL" VARCHAR(256),
+  "GUARDED" BOOLEAN NOT NULL,
+  "USER_ID" INTEGER,
+  "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40),
+  "DEFAULT_PERM_TEMPLATE_VIEW" VARCHAR(40),
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL
+);
+CREATE UNIQUE INDEX "PK_ORGANIZATIONS" ON "ORGANIZATIONS" ("UUID");
+CREATE UNIQUE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE");
+
+CREATE TABLE "INTERNAL_PROPERTIES" (
+  "KEE" VARCHAR(50) NOT NULL PRIMARY KEY,
+  "IS_EMPTY" BOOLEAN NOT NULL,
+  "TEXT_VALUE" VARCHAR(4000),
+  "CLOB_VALUE" CLOB,
+  "CREATED_AT" BIGINT
+);
+CREATE UNIQUE INDEX "UNIQ_INTERNAL_PROPERTIES" ON "INTERNAL_PROPERTIES" ("KEE");
+
+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" VARCHAR(16777215),
+  "DESCRIPTION_FORMAT" VARCHAR(20),
+  "PRIORITY" INTEGER,
+  "IS_TEMPLATE" BOOLEAN DEFAULT FALSE,
+  "TEMPLATE_ID" INTEGER,
+  "PLUGIN_CONFIG_KEY" VARCHAR(200),
+  "NAME" VARCHAR(200),
+  "STATUS" VARCHAR(40),
+  "LANGUAGE" VARCHAR(20),
+  "NOTE_DATA" CLOB(2147483647),
+  "NOTE_USER_LOGIN" VARCHAR(255),
+  "NOTE_CREATED_AT" TIMESTAMP,
+  "NOTE_UPDATED_AT" TIMESTAMP,
+  "REMEDIATION_FUNCTION" VARCHAR(20),
+  "DEF_REMEDIATION_FUNCTION" VARCHAR(20),
+  "REMEDIATION_GAP_MULT" VARCHAR(20),
+  "DEF_REMEDIATION_GAP_MULT" VARCHAR(20),
+  "REMEDIATION_BASE_EFFORT" VARCHAR(20),
+  "DEF_REMEDIATION_BASE_EFFORT" VARCHAR(20),
+  "GAP_DESCRIPTION" VARCHAR(4000),
+  "TAGS" VARCHAR(4000),
+  "SYSTEM_TAGS" VARCHAR(4000),
+  "RULE_TYPE" TINYINT,
+  "CREATED_AT" BIGINT,
+  "UPDATED_AT" BIGINT
+);
+CREATE UNIQUE INDEX "RULES_REPO_KEY" ON "RULES" ("PLUGIN_NAME", "PLUGIN_RULE_KEY");
+
+CREATE TABLE "RULES_METADATA" (
+  "RULE_ID" INTEGER NOT NULL,
+  "ORGANIZATION_UUID" VARCHAR(40) NOT NULL,
+  "NOTE_DATA" CLOB(2147483647),
+  "NOTE_USER_LOGIN" VARCHAR(255),
+  "NOTE_CREATED_AT" BIGINT,
+  "NOTE_UPDATED_AT" BIGINT,
+  "REMEDIATION_FUNCTION" VARCHAR(20),
+  "REMEDIATION_GAP_MULT" VARCHAR(20),
+  "REMEDIATION_BASE_EFFORT" VARCHAR(20),
+  "TAGS" VARCHAR(4000),
+  "CREATED_AT" BIGINT NOT NULL,
+  "UPDATED_AT" BIGINT NOT NULL,
+  CONSTRAINT PK_RULES_METADATA PRIMARY KEY (RULE_ID,ORGANIZATION_UUID)
+);