From d3b7c326fca7e01901f29fa75d953f43d478f3bb Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Mon, 4 Jul 2016 15:01:01 +0200 Subject: [PATCH] SONAR-7705 add migration to kill tree errors in snapshots and projects --- .../1269_clean_usurper_root_components.rb | 29 + ...ots.rb => 1270_drop_trees_of_snapshots.rb} | 0 ...p_indices_on_tree_columns_of_snapshots.rb} | 0 ... 1272_drop_tree_columns_from_snapshots.rb} | 0 ..._drop_index_on_snapshot_id_of_measures.rb} | 0 ..._drop_snapshot_id_column_from_measures.rb} | 0 ...add_index_on_analysis_uuid_of_measures.rb} | 0 .../org/sonar/db/version/DatabaseVersion.java | 2 +- .../sonar/db/version/MigrationStepModule.java | 6 +- .../v60/CleanUsurperRootComponents.java | 159 ++++ .../org/sonar/db/version/rows-h2.sql | 1 + .../db/version/MigrationStepModuleTest.java | 2 +- .../v60/CleanUsurperRootComponentsTest.java | 359 +++++++++ .../complete_schema.sql | 692 ++++++++++++++++++ 14 files changed, 1246 insertions(+), 4 deletions(-) create mode 100644 server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1269_clean_usurper_root_components.rb rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{1269_drop_trees_of_snapshots.rb => 1270_drop_trees_of_snapshots.rb} (100%) rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{1270_drop_indices_on_tree_columns_of_snapshots.rb => 1271_drop_indices_on_tree_columns_of_snapshots.rb} (100%) rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{1271_drop_tree_columns_from_snapshots.rb => 1272_drop_tree_columns_from_snapshots.rb} (100%) rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{1272_drop_index_on_snapshot_id_of_measures.rb => 1273_drop_index_on_snapshot_id_of_measures.rb} (100%) rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{1273_drop_snapshot_id_column_from_measures.rb => 1274_drop_snapshot_id_column_from_measures.rb} (100%) rename server/sonar-web/src/main/webapp/WEB-INF/db/migrate/{1274_add_index_on_analysis_uuid_of_measures.rb => 1275_add_index_on_analysis_uuid_of_measures.rb} (100%) create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v60/CleanUsurperRootComponents.java create mode 100644 sonar-db/src/test/java/org/sonar/db/version/v60/CleanUsurperRootComponentsTest.java create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v60/CleanUsurperRootComponentsTest/complete_schema.sql diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1269_clean_usurper_root_components.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1269_clean_usurper_root_components.rb new file mode 100644 index 00000000000..f77ee059214 --- /dev/null +++ b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1269_clean_usurper_root_components.rb @@ -0,0 +1,29 @@ +# +# SonarQube, open source software quality management tool. +# Copyright (C) 2008-2014 SonarSource +# mailto:contact AT sonarsource DOT com +# +# SonarQube 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. +# +# SonarQube 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. +# + +# +# SonarQube 6.0 +# +class CleanUsurperRootComponents < ActiveRecord::Migration + + def self.up + execute_java_migration('org.sonar.db.version.v60.CleanUsurperRootComponents') + end +end diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1269_drop_trees_of_snapshots.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1270_drop_trees_of_snapshots.rb similarity index 100% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1269_drop_trees_of_snapshots.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1270_drop_trees_of_snapshots.rb diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1270_drop_indices_on_tree_columns_of_snapshots.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1271_drop_indices_on_tree_columns_of_snapshots.rb similarity index 100% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1270_drop_indices_on_tree_columns_of_snapshots.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1271_drop_indices_on_tree_columns_of_snapshots.rb diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1271_drop_tree_columns_from_snapshots.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1272_drop_tree_columns_from_snapshots.rb similarity index 100% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1271_drop_tree_columns_from_snapshots.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1272_drop_tree_columns_from_snapshots.rb diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1272_drop_index_on_snapshot_id_of_measures.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1273_drop_index_on_snapshot_id_of_measures.rb similarity index 100% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1272_drop_index_on_snapshot_id_of_measures.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1273_drop_index_on_snapshot_id_of_measures.rb diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1273_drop_snapshot_id_column_from_measures.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1274_drop_snapshot_id_column_from_measures.rb similarity index 100% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1273_drop_snapshot_id_column_from_measures.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1274_drop_snapshot_id_column_from_measures.rb diff --git a/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1274_add_index_on_analysis_uuid_of_measures.rb b/server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1275_add_index_on_analysis_uuid_of_measures.rb similarity index 100% rename from server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1274_add_index_on_analysis_uuid_of_measures.rb rename to server/sonar-web/src/main/webapp/WEB-INF/db/migrate/1275_add_index_on_analysis_uuid_of_measures.rb diff --git a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java index 6e2c2b45bf7..616324a2ed8 100644 --- a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java +++ b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java @@ -30,7 +30,7 @@ import org.sonar.db.MyBatis; public class DatabaseVersion { - public static final int LAST_VERSION = 1_274; + public static final int LAST_VERSION = 1_275; /** * The minimum supported version which can be upgraded. Lower diff --git a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java index 70c455862a7..41900151987 100644 --- a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java +++ b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java @@ -103,6 +103,7 @@ import org.sonar.db.version.v60.CleanMeasuresWithNullAnalysisUuid; import org.sonar.db.version.v60.CleanOrphanRowsInProjects; import org.sonar.db.version.v60.CleanOrphanRowsInResourceIndex; import org.sonar.db.version.v60.CleanOrphanRowsInSnapshots; +import org.sonar.db.version.v60.CleanUsurperRootComponents; import org.sonar.db.version.v60.DeleteOrphanDuplicationsIndexRowsWithoutAnalysis; import org.sonar.db.version.v60.DeleteOrphanDuplicationsIndexRowsWithoutComponent; import org.sonar.db.version.v60.DeleteOrphanMeasuresWithoutComponent; @@ -305,9 +306,10 @@ public class MigrationStepModule extends Module { CleanMeasuresWithNullAnalysisUuid.class, MakeAnalysisUuidNotNullOnMeasures.class, + CleanUsurperRootComponents.class, + DropTreesOfSnapshots.class, DropTreeColumnsFromSnapshots.class, - DropSnapshotIdColumnFromMeasures.class - ); + DropSnapshotIdColumnFromMeasures.class); } } diff --git a/sonar-db/src/main/java/org/sonar/db/version/v60/CleanUsurperRootComponents.java b/sonar-db/src/main/java/org/sonar/db/version/v60/CleanUsurperRootComponents.java new file mode 100644 index 00000000000..742168e9945 --- /dev/null +++ b/sonar-db/src/main/java/org/sonar/db/version/v60/CleanUsurperRootComponents.java @@ -0,0 +1,159 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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.db.version.v60; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.version.BaseDataChange; +import org.sonar.db.version.MassUpdate; + +public class CleanUsurperRootComponents extends BaseDataChange { + + public CleanUsurperRootComponents(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + // fix snapshots which don't have the scope and/or qualifier of their associated component + fixSnapshotScopeAndQualifier(context); + // delete components declaring themselves as root in table PROJECTS but which don't have a root scope and/or qualifier + cleanUsurperRootComponents(context); + // components which has snapshots reference a component as root which is not a root + cleanSnapshotWithIncorrectRoot(context); + } + + private static void fixSnapshotScopeAndQualifier(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select" + + " sn.id,p.scope,p.qualifier" + + " from" + + " snapshots sn, projects p" + + " where" + + " p.uuid = sn.component_uuid" + + " and (p.qualifier<>sn.qualifier or p.scope<>sn.scope)"); + massUpdate.update("update snapshots set scope=?,qualifier=? where id=?"); + massUpdate.rowPluralName("snapshots with inconsistent scope or qualifier"); + massUpdate.execute((row, update) -> { + long snapshotId = row.getLong(1); + String scope = row.getString(2); + String qualifier = row.getString(3); + + update.setString(1, scope); + update.setString(2, qualifier); + update.setLong(3, snapshotId); + + return true; + }); + } + + private static void cleanUsurperRootComponents(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select p.id,p.uuid from projects p " + + " where" + + " p.project_uuid = p.uuid" + + " and not (" + + " p.scope = 'PRJ'" + + " and p.qualifier in ('TRK', 'VW', 'DEV')" + + " )"); + massUpdate.update("delete from duplications_index where component_uuid=?"); + massUpdate.update("delete from project_measures where component_uuid=?"); + massUpdate.update("delete from ce_activity where component_uuid=?"); + massUpdate.update("delete from events where component_uuid=?"); + massUpdate.update("delete from project_links where component_uuid=?"); + massUpdate.update("delete from snapshots where component_uuid=? or root_component_uuid=?"); + massUpdate.update("delete from issues where component_uuid=? or project_uuid=?"); + massUpdate.update("delete from file_sources where file_uuid=? or project_uuid=?"); + massUpdate.update("delete from group_roles where resource_id=?"); + massUpdate.update("delete from user_roles where resource_id=?"); + massUpdate.update("delete from properties where resource_id=?"); + massUpdate.update("delete from widgets where resource_id=?"); + massUpdate.update("delete from projects where uuid=?"); + massUpdate.rowPluralName("usurper root components"); + massUpdate.execute((row, update, updateIndex) -> { + long componentId = row.getLong(1); + String componentUuid = row.getString(2); + switch (updateIndex) { + case 0: + case 1: + case 2: + case 3: + case 4: + update.setString(1, componentUuid); + return true; + case 5: + case 6: + case 7: + update.setString(1, componentUuid); + update.setString(2, componentUuid); + return true; + case 8: + case 9: + case 10: + case 11: + update.setLong(1, componentId); + return true; + case 12: + update.setString(1, componentUuid); + return true; + default: + throw new IllegalArgumentException("Unsupported update index " + updateIndex); + } + }); + } + + private void cleanSnapshotWithIncorrectRoot(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select" + + " sn.uuid,sn.id" + + " from " + + " projects p, snapshots sn" + + " where" + + " p.uuid = sn.root_component_uuid" + + " and not (" + + " p.scope = 'PRJ'" + + " and p.qualifier in ('TRK', 'VW', 'DEV')" + + " )"); + massUpdate.update("DELETE from duplications_index WHERE analysis_uuid=?"); + massUpdate.update("DELETE from project_measures WHERE analysis_uuid=?"); + massUpdate.update("DELETE from ce_activity WHERE analysis_uuid=?"); + massUpdate.update("DELETE from events WHERE analysis_uuid=?"); + massUpdate.update("DELETE from snapshots WHERE id=?"); + massUpdate.rowPluralName("snapshots with incorrect root"); + massUpdate.execute((row, update, updateIndex) -> { + String analysisUuid = row.getString(1); + long snapshotId = row.getLong(2); + switch (updateIndex) { + case 0: + case 1: + case 2: + case 3: + update.setString(1, analysisUuid); + return true; + case 4: + update.setLong(1, snapshotId); + return true; + default: + throw new IllegalArgumentException("Unsupported update index " + updateIndex); + } + }); + } + +} diff --git a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql index d753b8a5017..05dcd3a02ad 100644 --- a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql +++ b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql @@ -481,6 +481,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1271'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1272'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1273'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1274'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1275'); INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '1418215735482', '1418215735482'); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; diff --git a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java index c5fbc32539b..1983e73c711 100644 --- a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java +++ b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java @@ -29,6 +29,6 @@ public class MigrationStepModuleTest { public void verify_count_of_added_MigrationStep_types() { ComponentContainer container = new ComponentContainer(); new MigrationStepModule().configure(container); - assertThat(container.size()).isEqualTo(127); + assertThat(container.size()).isEqualTo(128); } } diff --git a/sonar-db/src/test/java/org/sonar/db/version/v60/CleanUsurperRootComponentsTest.java b/sonar-db/src/test/java/org/sonar/db/version/v60/CleanUsurperRootComponentsTest.java new file mode 100644 index 00000000000..6e54187085f --- /dev/null +++ b/sonar-db/src/test/java/org/sonar/db/version/v60/CleanUsurperRootComponentsTest.java @@ -0,0 +1,359 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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.db.version.v60; + +import com.google.common.collect.ImmutableList; +import java.sql.SQLException; +import java.util.List; +import java.util.Map; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.resources.Qualifiers; +import org.sonar.api.resources.Scopes; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +import static java.lang.String.valueOf; +import static org.assertj.core.api.Assertions.assertThat; + +public class CleanUsurperRootComponentsTest { + + private static final List TABLES = ImmutableList.of( + "duplications_index", "project_measures", "ce_activity", "events", "snapshots", + "project_links", "project_measures", "issues", "file_sources", "group_roles", + "user_roles", "properties", "widgets", "projects"); + + @Rule + public DbTester db = DbTester.createForSchema(System2.INSTANCE, CleanUsurperRootComponentsTest.class, + "complete_schema.sql"); + + private CleanUsurperRootComponents underTest = new CleanUsurperRootComponents(db.database()); + + @Test + public void migration_has_no_effect_on_empty_db() throws SQLException { + underTest.execute(); + + TABLES.forEach(tableName -> assertThat(db.countRowsOfTable(tableName)).isEqualTo(0)); + } + + @Test + public void execute_fixes_scope_and_qualifier_of_snapshot_inconsistent_with_component() throws SQLException { + String[] componentUuids = { + insertComponent("sc1", "qu1"), + insertComponent("sc2", "qu2"), + insertComponent("sc3", "qu3"), + insertComponent("sc4", "qu4") + }; + String[] snapshotUuids = { + insertSnapshot(componentUuids[0], "sc1", "qu1"), + insertSnapshot(componentUuids[1], "sc2", "quW"), + insertSnapshot(componentUuids[2], "scX", "qu3"), + insertSnapshot(componentUuids[3], "scY", "quZ"), + }; + + underTest.execute(); + + assertSnapshot(snapshotUuids[0], "sc1", "qu1"); + assertSnapshot(snapshotUuids[1], "sc2", "qu2"); + assertSnapshot(snapshotUuids[2], "sc3", "qu3"); + assertSnapshot(snapshotUuids[3], "sc4", "qu4"); + } + + @Test + public void executes_deletes_usurper_root_components() throws SQLException { + String[] componentUuids = { + insertRootComponent(Scopes.PROJECT, Qualifiers.PROJECT), + insertRootComponent(Scopes.PROJECT, Qualifiers.MODULE), + insertRootComponent(Scopes.DIRECTORY, Qualifiers.DIRECTORY), + insertRootComponent(Scopes.FILE, Qualifiers.FILE), + insertRootComponent(Scopes.PROJECT, Qualifiers.VIEW), + insertRootComponent(Scopes.PROJECT, Qualifiers.SUBVIEW), + insertRootComponent(Scopes.FILE, Qualifiers.PROJECT), + insertRootComponent(Scopes.PROJECT, "DEV"), + insertRootComponent(Scopes.PROJECT, "DEV_PRJ"), + }; + + underTest.execute(); + + assertUuidsInTableProjects("projects", componentUuids[0], componentUuids[4], componentUuids[7]); + } + + @Test + public void executes_deletes_data_in_all_children_tables_of_component_for_usurper_root_components() throws SQLException { + long usurperId = 12L; + String usurperUuid = "usurper_uuid"; + insertComponent(Scopes.PROJECT, Qualifiers.MODULE, usurperId, usurperUuid, usurperUuid); + insertDuplicationsIndex(usurperUuid); + insertProjectMeasures(usurperUuid, dontCare()); + insertCeActivity(usurperUuid, dontCare()); + insertEvent(usurperUuid, dontCare()); + insertSnapshot(usurperUuid, dontCare()); + insertSnapshot(dontCare(), usurperUuid); + insertSnapshot(usurperUuid, usurperUuid); + insertProjectLinks(usurperUuid); + insertIssue(usurperUuid, null); + insertIssue(null, usurperUuid); + insertIssue(usurperUuid, usurperUuid); + insertFileSource(null, usurperUuid); + insertFileSource(usurperUuid, null); + insertFileSource(usurperUuid, usurperUuid); + insertGroupRole(usurperId); + insertUserRole(usurperId); + insertProperties(usurperId); + insertWidget(usurperId); + + TABLES.stream() + .forEach(s -> assertThat(db.countRowsOfTable(s)).describedAs("table " + s).isGreaterThanOrEqualTo(1)); + + underTest.execute(); + + TABLES.stream() + .forEach(s -> assertThat(db.countRowsOfTable(s)).describedAs("table " + s).isEqualTo(0)); + } + + @Test + public void execute_deletes_snapshots_which_root_is_not_root() throws SQLException { + String[] componentUuids = { + insertRootComponent(Scopes.PROJECT, Qualifiers.PROJECT), + insertComponent(Scopes.PROJECT, Qualifiers.MODULE), + insertComponent(Scopes.DIRECTORY, Qualifiers.DIRECTORY), + insertComponent(Scopes.FILE, Qualifiers.FILE), + insertComponent(Scopes.PROJECT, Qualifiers.VIEW), + insertComponent(Scopes.PROJECT, Qualifiers.SUBVIEW), + insertComponent(Scopes.FILE, Qualifiers.PROJECT), + insertComponent(Scopes.PROJECT, "DEV"), + insertComponent(Scopes.PROJECT, "DEV_PRJ"), + }; + String[] snapshotUuids = { + insertSnapshot(dontCare(), componentUuids[0]), + insertSnapshot(dontCare(), componentUuids[1]), + insertSnapshot(dontCare(), componentUuids[2]), + insertSnapshot(dontCare(), componentUuids[3]), + insertSnapshot(dontCare(), componentUuids[4]), + insertSnapshot(dontCare(), componentUuids[5]), + insertSnapshot(dontCare(), componentUuids[6]), + insertSnapshot(dontCare(), componentUuids[7]), + insertSnapshot(dontCare(), componentUuids[8]) + }; + + underTest.execute(); + + assertUuidsInTableProjects("snapshots", snapshotUuids[0], snapshotUuids[4], snapshotUuids[7]); + } + + @Test + public void execute_deletes_children_tables_of_snapshots_when_root_of_snapshot_is_not_root() throws SQLException { + String componentUuid = insertComponent(Scopes.FILE, Scopes.FILE); + String snapshotUuid = insertSnapshot(dontCare(), componentUuid); + insertProjectMeasures(dontCare(), snapshotUuid); + insertCeActivity(componentUuid, snapshotUuid); + insertEvent(componentUuid, snapshotUuid); + + underTest.execute(); + + TABLES.stream() + .filter(s1 -> !s1.equals("projects")) + .forEach(s -> assertThat(db.countRowsOfTable(s)).describedAs("table " + s).isEqualTo(0)); + } + + private void insertDuplicationsIndex(String componentUuid) { + db.executeInsert( + "duplications_index", + "COMPONENT_UUID", componentUuid, + "ANALYSIS_UUID", dontCare(), + "HASH", dontCare(), + "INDEX_IN_FILE", valueOf(0), + "START_LINE", valueOf(0), + "END_LINE", valueOf(0)); + db.commit(); + } + + private void insertProjectMeasures(String componentUuid, String analysisUuid) { + db.executeInsert( + "project_measures", + "METRIC_ID", valueOf(123L), + "COMPONENT_UUID", componentUuid, + "ANALYSIS_UUID", analysisUuid); + db.commit(); + } + + private void insertCeActivity(String componentUuid, String analysisUuid) { + db.executeInsert( + "ce_activity", + "UUID", dontCare(), + "TASK_TYPE", dontCare(), + "COMPONENT_UUID", componentUuid, + "ANALYSIS_UUID", analysisUuid, + "STATUS", dontCare(), + "IS_LAST", "true", + "IS_LAST_KEY", dontCare(), + "SUBMITTED_AT", valueOf(121L), + "CREATED_AT", valueOf(122L), + "UPDATED_AT", valueOf(123L)); + db.commit(); + } + + private void insertEvent(String componentUuid, String analysisUuid) { + db.executeInsert( + "events", + "ANALYSIS_UUID", analysisUuid, + "COMPONENT_UUID", componentUuid, + "CREATED_AT", valueOf(122L), + "EVENT_DATE", valueOf(123L)); + db.commit(); + } + + private String insertSnapshot(String componentUuid, String rootComponentUuid) { + String uuid = "uuid_" + idGenerator++; + db.executeInsert( + "snapshots", + "UUID", uuid, + "COMPONENT_UUID", componentUuid, + "ROOT_COMPONENT_UUID", rootComponentUuid); + db.commit(); + return uuid; + } + + private void insertProjectLinks(String componentUuid) { + db.executeInsert( + "project_links", + "COMPONENT_UUID", componentUuid, + "HREF", dontCare()); + db.commit(); + } + + private void insertIssue(@Nullable String componentUuid, @Nullable String projectUuid) { + db.executeInsert( + "issues", + "COMPONENT_UUID", componentUuid == null ? dontCare() : componentUuid, + "PROJECT_UUID", projectUuid == null ? dontCare() : projectUuid, + "KEE", "kee_" + componentUuid + projectUuid, + "MANUAL_SEVERITY", valueOf(true)); + db.commit(); + } + + private void insertFileSource(@Nullable String fileUuid, @Nullable String projectUuid) { + db.executeInsert( + "file_sources", + "FILE_UUID", fileUuid == null ? dontCare() : fileUuid, + "PROJECT_UUID", projectUuid == null ? dontCare() : projectUuid, + "CREATED_AT", valueOf(122L), + "UPDATED_AT", valueOf(123L)); + db.commit(); + } + + private void insertGroupRole(long componentId) { + db.executeInsert( + "group_roles", + "RESOURCE_ID", valueOf(componentId), + "ROLE", dontCare()); + db.commit(); + } + + private void insertUserRole(long componentId) { + db.executeInsert( + "user_roles", + "RESOURCE_ID", valueOf(componentId), + "ROLE", dontCare()); + db.commit(); + } + + private void insertProperties(long componentId) { + db.executeInsert( + "properties", + "RESOURCE_ID", valueOf(componentId)); + db.commit(); + } + + private void insertWidget(long componentId) { + db.executeInsert( + "widgets", + "DASHBOARD_ID", valueOf(95), + "WIDGET_KEY", dontCare(), + "RESOURCE_ID", valueOf(componentId)); + db.commit(); + } + + private long idGenerator = 0; + + private String insertComponent(String scope, String qualifier) { + long id = idGenerator++; + String uuid = "uuid_" + id; + return insertComponent(scope, qualifier, id, uuid, dontCare()); + } + + private String insertRootComponent(String scope, String qualifier) { + long id = idGenerator++; + String uuid = "uuid_" + id; + return insertComponent(scope, qualifier, id, uuid, uuid); + } + + private String insertComponent(String scope, String qualifier, long id, String uuid, String projectUuid) { + db.executeInsert( + "projects", + "ID", valueOf(id), + "UUID", uuid, + "ROOT_UUID", dontCare(), + "PROJECT_UUID", projectUuid, + "UUID_PATH", dontCare(), + "SCOPE", scope, + "QUALIFIER", qualifier); + db.commit(); + return uuid; + } + + private String insertSnapshot(String componentUuid, String scope, String qualifier) { + long id = idGenerator++; + String uuid = "uuid_" + id; + + db.executeInsert( + "snapshots", + "ID", valueOf(id), + "UUID", uuid, + "component_uuid", componentUuid, + "root_component_uuid", dontCare(), + "scope", scope, + "qualifier", qualifier); + db.commit(); + return uuid; + } + + private void assertSnapshot(String snapshotUuid, String scope, String qualifier) { + List> rows = db.select("select SCOPE, QUALIFIER from snapshots where UUID='" + snapshotUuid + "'"); + assertThat(rows).hasSize(1); + Map row = rows.get(0); + assertThat(row.get("SCOPE")).isEqualTo(scope); + assertThat(row.get("QUALIFIER")).isEqualTo(qualifier); + } + + private void assertUuidsInTableProjects(String tableName, String... expected) { + assertThat(db.select("select uuid from " + tableName) + .stream() + .map(stringObjectMap -> (String) stringObjectMap.entrySet().iterator().next().getValue())) + .containsOnly(expected); + } + + private long dontCareGenerator = 0; + + private String dontCare() { + return "DC_" + dontCareGenerator++; + } +} diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v60/CleanUsurperRootComponentsTest/complete_schema.sql b/sonar-db/src/test/resources/org/sonar/db/version/v60/CleanUsurperRootComponentsTest/complete_schema.sql new file mode 100644 index 00000000000..3706047e5ab --- /dev/null +++ b/sonar-db/src/test/resources/org/sonar/db/version/v60/CleanUsurperRootComponentsTest/complete_schema.sql @@ -0,0 +1,692 @@ +CREATE TABLE "GROUPS_USERS" ( + "USER_ID" INTEGER, + "GROUP_ID" INTEGER +); + +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, + "LANGUAGE" VARCHAR(20), + "KEE" VARCHAR(255) NOT NULL, + "PARENT_KEE" VARCHAR(255), + "RULES_UPDATED_AT" VARCHAR(100), + "IS_DEFAULT" BOOLEAN NOT NULL DEFAULT FALSE, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, + "LAST_USED" BIGINT, + "USER_UPDATED_AT" BIGINT +); + +CREATE TABLE "PROJECT_QPROFILES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "PROFILE_KEY" VARCHAR(255) NOT NULL +); + +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, + "RESOURCE_ID" INTEGER +); + +CREATE TABLE "GROUPS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(500), + "DESCRIPTION" VARCHAR(200), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "SNAPSHOTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(50) NOT NULL, + "CREATED_AT" BIGINT, + "BUILD_DATE" BIGINT, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "PARENT_SNAPSHOT_ID" INTEGER, + "STATUS" VARCHAR(4) NOT NULL DEFAULT 'U', + "PURGE_STATUS" INTEGER, + "ISLAST" BOOLEAN NOT NULL DEFAULT FALSE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "ROOT_SNAPSHOT_ID" INTEGER, + "VERSION" VARCHAR(500), + "PATH" VARCHAR(500), + "DEPTH" INTEGER, + "ROOT_COMPONENT_UUID" VARCHAR(50) NOT NULL, + "PERIOD1_MODE" VARCHAR(100), + "PERIOD1_PARAM" VARCHAR(100), + "PERIOD1_DATE" BIGINT, + "PERIOD2_MODE" VARCHAR(100), + "PERIOD2_PARAM" VARCHAR(100), + "PERIOD2_DATE" BIGINT, + "PERIOD3_MODE" VARCHAR(100), + "PERIOD3_PARAM" VARCHAR(100), + "PERIOD3_DATE" BIGINT, + "PERIOD4_MODE" VARCHAR(100), + "PERIOD4_PARAM" VARCHAR(100), + "PERIOD4_DATE" BIGINT, + "PERIOD5_MODE" VARCHAR(100), + "PERIOD5_PARAM" VARCHAR(100), + "PERIOD5_DATE" BIGINT +); + +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" 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 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) +); + +CREATE TABLE "EVENTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(400), + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "CATEGORY" VARCHAR(50), + "EVENT_DATE" BIGINT NOT NULL, + "CREATED_AT" BIGINT NOT NULL, + "DESCRIPTION" VARCHAR(4000), + "EVENT_DATA" VARCHAR(4000) +); + +CREATE TABLE "QUALITY_GATES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, +); + +CREATE TABLE "QUALITY_GATE_CONDITIONS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "QGATE_ID" INTEGER, + "METRIC_ID" INTEGER, + "OPERATOR" VARCHAR(3), + "VALUE_ERROR" VARCHAR(64), + "VALUE_WARNING" VARCHAR(64), + "PERIOD" INTEGER, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP, +); + +CREATE TABLE "PROPERTIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROP_KEY" VARCHAR(512), + "RESOURCE_ID" INTEGER, + "TEXT_VALUE" CLOB(2147483647), + "USER_ID" INTEGER +); + +CREATE TABLE "PROJECT_LINKS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "COMPONENT_UUID" VARCHAR(50), + "LINK_TYPE" VARCHAR(20), + "NAME" VARCHAR(128), + "HREF" VARCHAR(2048) NOT NULL +); + +CREATE TABLE "DUPLICATIONS_INDEX" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "HASH" VARCHAR(50) NOT NULL, + "INDEX_IN_FILE" INTEGER NOT NULL, + "START_LINE" INTEGER NOT NULL, + "END_LINE" INTEGER NOT NULL +); + +CREATE TABLE "PROJECT_MEASURES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "VALUE" DOUBLE, + "METRIC_ID" INTEGER NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "ANALYSIS_UUID" VARCHAR(50) NOT NULL, + "SNAPSHOT_ID" INTEGER, + "TEXT_VALUE" VARCHAR(4000), + "ALERT_STATUS" VARCHAR(5), + "ALERT_TEXT" VARCHAR(4000), + "DESCRIPTION" VARCHAR(4000), + "PERSON_ID" INTEGER, + "VARIATION_VALUE_1" DOUBLE, + "VARIATION_VALUE_2" DOUBLE, + "VARIATION_VALUE_3" DOUBLE, + "VARIATION_VALUE_4" DOUBLE, + "VARIATION_VALUE_5" DOUBLE, + "MEASURE_DATA" BINARY(167772150) +); + +CREATE TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(400), + "UUID" VARCHAR(50) NOT NULL, + "UUID_PATH" VARCHAR(4000) NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50), + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(4000), + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "DEPRECATED_KEE" VARCHAR(400), + "PATH" VARCHAR(2000), + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "LONG_NAME" VARCHAR(2000), + "DEVELOPER_UUID" VARCHAR(50), + "CREATED_AT" TIMESTAMP, + "AUTHORIZATION_UPDATED_AT" BIGINT +); + +CREATE TABLE "MANUAL_MEASURES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "METRIC_ID" INTEGER NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "VALUE" DOUBLE, + "TEXT_VALUE" VARCHAR(4000), + "USER_LOGIN" VARCHAR(255), + "DESCRIPTION" VARCHAR(4000), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); + +CREATE TABLE "ACTIVE_RULES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROFILE_ID" INTEGER NOT NULL, + "RULE_ID" INTEGER NOT NULL, + "FAILURE_LEVEL" INTEGER NOT NULL, + "INHERITANCE" VARCHAR(10), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); + +CREATE TABLE "NOTIFICATIONS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "DATA" BLOB(167772150) +); + +CREATE TABLE "USER_ROLES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER, + "RESOURCE_ID" INTEGER, + "ROLE" VARCHAR(64) NOT NULL +); + +CREATE TABLE "ACTIVE_DASHBOARDS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "DASHBOARD_ID" INTEGER NOT NULL, + "USER_ID" INTEGER, + "ORDER_INDEX" INTEGER +); + +CREATE TABLE "ACTIVE_RULE_PARAMETERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ACTIVE_RULE_ID" INTEGER NOT NULL, + "RULES_PARAMETER_ID" INTEGER NOT NULL, + "RULES_PARAMETER_KEY" VARCHAR(128), + "VALUE" VARCHAR(4000) +); + +CREATE TABLE "USERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "LOGIN" VARCHAR(255), + "NAME" VARCHAR(200), + "EMAIL" VARCHAR(100), + "CRYPTED_PASSWORD" VARCHAR(40), + "SALT" VARCHAR(40), + "ACTIVE" BOOLEAN DEFAULT TRUE, + "SCM_ACCOUNTS" VARCHAR(4000), + "EXTERNAL_IDENTITY" VARCHAR(255), + "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100), + "USER_LOCAL" BOOLEAN, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT +); + +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, + "IS_GLOBAL" BOOLEAN, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +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, + "WORST_VALUE" DOUBLE, + "BEST_VALUE" DOUBLE, + "OPTIMIZED_BEST_VALUE" BOOLEAN, + "HIDDEN" BOOLEAN, + "DELETE_HISTORICAL_DATA" BOOLEAN, + "DECIMAL_SCALE" INTEGER +); + +CREATE TABLE "LOADED_TEMPLATES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(200), + "TEMPLATE_TYPE" VARCHAR(15) +); + +CREATE TABLE "RESOURCE_INDEX" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(400) NOT NULL, + "POSITION" INTEGER NOT NULL, + "NAME_SIZE" INTEGER NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "ROOT_COMPONENT_UUID" VARCHAR(50) NOT NULL, + "QUALIFIER" VARCHAR(10) NOT NULL +); + +CREATE TABLE "AUTHORS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PERSON_ID" INTEGER, + "LOGIN" VARCHAR(255), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "MEASURE_FILTERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "SHARED" BOOLEAN NOT NULL DEFAULT FALSE, + "USER_ID" INTEGER, + "DESCRIPTION" VARCHAR(4000), + "DATA" CLOB(2147483647), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "MEASURE_FILTER_FAVOURITES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER NOT NULL, + "MEASURE_FILTER_ID" INTEGER NOT NULL, + "CREATED_AT" TIMESTAMP +); + +CREATE TABLE "ISSUES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(50) UNIQUE NOT NULL, + "COMPONENT_UUID" VARCHAR(50), + "PROJECT_UUID" VARCHAR(50), + "RULE_ID" INTEGER, + "SEVERITY" VARCHAR(10), + "MANUAL_SEVERITY" BOOLEAN NOT NULL, + "MESSAGE" VARCHAR(4000), + "LINE" INTEGER, + "GAP" DOUBLE, + "EFFORT" INTEGER, + "STATUS" VARCHAR(20), + "RESOLUTION" VARCHAR(20), + "CHECKSUM" VARCHAR(1000), + "REPORTER" VARCHAR(255), + "ASSIGNEE" VARCHAR(255), + "AUTHOR_LOGIN" VARCHAR(255), + "ACTION_PLAN_KEY" VARCHAR(50) NULL, + "ISSUE_ATTRIBUTES" VARCHAR(4000), + "TAGS" VARCHAR(4000), + "ISSUE_CREATION_DATE" BIGINT, + "ISSUE_CLOSE_DATE" BIGINT, + "ISSUE_UPDATE_DATE" BIGINT, + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "LOCATIONS" BLOB(167772150), + "ISSUE_TYPE" TINYINT +); + +CREATE TABLE "ISSUE_CHANGES" ( + "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(50), + "ISSUE_KEY" VARCHAR(50) NOT NULL, + "USER_LOGIN" VARCHAR(255), + "CHANGE_TYPE" VARCHAR(40), + "CHANGE_DATA" VARCHAR(16777215), + "CREATED_AT" BIGINT, + "UPDATED_AT" BIGINT, + "ISSUE_CHANGE_CREATION_DATE" BIGINT +); + +CREATE TABLE "ISSUE_FILTERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "SHARED" BOOLEAN NOT NULL DEFAULT FALSE, + "USER_LOGIN" VARCHAR(255), + "DESCRIPTION" VARCHAR(4000), + "DATA" CLOB(2147483647), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "ISSUE_FILTER_FAVOURITES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_LOGIN" VARCHAR(255) NOT NULL, + "ISSUE_FILTER_ID" INTEGER NOT NULL, + "CREATED_AT" TIMESTAMP +); + +CREATE TABLE "PERMISSION_TEMPLATES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "NAME" VARCHAR(100) NOT NULL, + "KEE" VARCHAR(100) NOT NULL, + "DESCRIPTION" VARCHAR(4000), + "KEY_PATTERN" VARCHAR(500), + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "PERM_TPL_CHARACTERISTICS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "TEMPLATE_ID" INTEGER NOT NULL, + "PERMISSION_KEY" VARCHAR(64) NOT NULL, + "WITH_PROJECT_CREATOR" BOOLEAN NOT NULL DEFAULT FALSE, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); + +CREATE TABLE "PERM_TEMPLATES_USERS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "USER_ID" INTEGER NOT NULL, + "TEMPLATE_ID" INTEGER NOT NULL, + "PERMISSION_REFERENCE" VARCHAR(64) NOT NULL, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + +CREATE TABLE "PERM_TEMPLATES_GROUPS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "GROUP_ID" INTEGER, + "TEMPLATE_ID" INTEGER NOT NULL, + "PERMISSION_REFERENCE" VARCHAR(64) NOT NULL, + "CREATED_AT" TIMESTAMP, + "UPDATED_AT" TIMESTAMP +); + + +CREATE TABLE "ACTIVITIES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "LOG_KEY" VARCHAR(250), + "PROFILE_KEY" VARCHAR(255) NOT NULL, + "CREATED_AT" TIMESTAMP, + "USER_LOGIN" VARCHAR(255), + "LOG_TYPE" VARCHAR(250), + "LOG_ACTION" VARCHAR(250), + "LOG_MESSAGE" VARCHAR(250), + "DATA_FIELD" CLOB(2147483647) +); + +CREATE TABLE "FILE_SOURCES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "FILE_UUID" VARCHAR(50) NOT NULL, + "LINE_HASHES" CLOB(2147483647), + "BINARY_DATA" BLOB(167772150), + "DATA_TYPE" VARCHAR(20), + "DATA_HASH" VARCHAR(50), + "SRC_HASH" VARCHAR(50), + "REVISION" VARCHAR(100), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); + +CREATE TABLE "CE_QUEUE" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "COMPONENT_UUID" VARCHAR(40) NULL, + "STATUS" VARCHAR(15) NOT NULL, + "SUBMITTER_LOGIN" VARCHAR(255) NULL, + "STARTED_AT" BIGINT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); + +CREATE TABLE "CE_ACTIVITY" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "UUID" VARCHAR(40) NOT NULL, + "TASK_TYPE" VARCHAR(15) NOT NULL, + "COMPONENT_UUID" VARCHAR(40) NULL, + "ANALYSIS_UUID" VARCHAR(50) NULL, + "STATUS" VARCHAR(15) NOT NULL, + "IS_LAST" BOOLEAN NOT NULL, + "IS_LAST_KEY" VARCHAR(55) NOT NULL, + "SUBMITTER_LOGIN" VARCHAR(255) NULL, + "SUBMITTED_AT" BIGINT NOT NULL, + "STARTED_AT" BIGINT NULL, + "EXECUTED_AT" BIGINT NULL, + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL, + "EXECUTION_TIME_MS" BIGINT NULL +); + +CREATE TABLE "USER_TOKENS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "LOGIN" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(100) NOT NULL, + "TOKEN_HASH" VARCHAR(255) NOT NULL, + "CREATED_AT" BIGINT NOT NULL +); + +-- ---------------------------------------------- +-- DDL Statements for indexes +-- ---------------------------------------------- + +CREATE UNIQUE INDEX "ACTIVITIES_LOG_KEY" ON "ACTIVITIES" ("LOG_KEY"); + +CREATE INDEX "GROUP_ROLES_RESOURCE" ON "GROUP_ROLES" ("RESOURCE_ID"); + +CREATE INDEX "USER_ROLES_RESOURCE" ON "USER_ROLES" ("RESOURCE_ID"); + +CREATE INDEX "USER_ROLES_USER" ON "USER_ROLES" ("USER_ID"); + +CREATE INDEX "DUPLICATIONS_INDEX_HASH" ON "DUPLICATIONS_INDEX" ("HASH"); + +CREATE INDEX "DUPLICATION_ANALYSIS_COMPONENT" ON "DUPLICATIONS_INDEX" ("ANALYSIS_UUID", "COMPONENT_UUID"); + +CREATE INDEX "INDEX_GROUPS_USERS_ON_GROUP_ID" ON "GROUPS_USERS" ("GROUP_ID"); + +CREATE INDEX "INDEX_GROUPS_USERS_ON_USER_ID" ON "GROUPS_USERS" ("USER_ID"); + +CREATE UNIQUE INDEX "GROUPS_USERS_UNIQUE" ON "GROUPS_USERS" ("GROUP_ID", "USER_ID"); + +CREATE INDEX "MEASURES_SID_METRIC" ON "PROJECT_MEASURES" ("SNAPSHOT_ID", "METRIC_ID"); + +CREATE INDEX "MEASURES_PERSON" ON "PROJECT_MEASURES" ("PERSON_ID"); + +CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES" ("COMPONENT_UUID"); + +CREATE UNIQUE INDEX "METRICS_UNIQUE_NAME" ON "METRICS" ("NAME"); + +CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS" ("ANALYSIS_UUID"); + +CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS" ("COMPONENT_UUID"); + +CREATE INDEX "WIDGETS_WIDGETKEY" ON "WIDGETS" ("WIDGET_KEY"); + +CREATE INDEX "WIDGETS_DASHBOARDS" ON "WIDGETS" ("DASHBOARD_ID"); + +CREATE INDEX "SNAPSHOTS_QUALIFIER" ON "SNAPSHOTS" ("QUALIFIER"); + +CREATE INDEX "SNAPSHOTS_ROOT" ON "SNAPSHOTS" ("ROOT_SNAPSHOT_ID"); + +CREATE INDEX "SNAPSHOTS_PARENT" ON "SNAPSHOTS" ("PARENT_SNAPSHOT_ID"); + +CREATE INDEX "SNAPSHOT_COMPONENT" ON "SNAPSHOTS" ("COMPONENT_UUID"); + +CREATE UNIQUE INDEX "ANALYSES_UUID" ON "SNAPSHOTS" ("UUID"); + +CREATE INDEX "SNAPSHOTS_ROOT_COMPONENT" ON "SNAPSHOTS" ("ROOT_COMPONENT_UUID"); + +CREATE INDEX "RULES_PARAMETERS_RULE_ID" ON "RULES_PARAMETERS" ("RULE_ID"); + +CREATE INDEX "ACTIVE_DASHBOARDS_DASHBOARDID" ON "ACTIVE_DASHBOARDS" ("DASHBOARD_ID"); + +CREATE INDEX "ACTIVE_DASHBOARDS_USERID" ON "ACTIVE_DASHBOARDS" ("USER_ID"); + +CREATE INDEX "UNIQUE_SCHEMA_MIGRATIONS" ON "SCHEMA_MIGRATIONS" ("VERSION"); + +CREATE INDEX "WIDGET_PROPERTIES_WIDGETS" ON "WIDGET_PROPERTIES" ("WIDGET_ID"); + +CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY"); + +CREATE INDEX "MANUAL_MEASURES_COMPONENT_UUID" ON "MANUAL_MEASURES" ("COMPONENT_UUID"); + +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE"); + +CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID"); + +CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); + +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID"); + +CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID"); + +CREATE INDEX "PROJECTS_QUALIFIER" ON "PROJECTS" ("QUALIFIER"); + +CREATE INDEX "RESOURCE_INDEX_KEY" ON "RESOURCE_INDEX" ("KEE"); + +CREATE INDEX "RESOURCE_INDEX_COMPONENT" ON "RESOURCE_INDEX" ("COMPONENT_UUID"); + +CREATE UNIQUE INDEX "UNIQ_AUTHOR_LOGINS" ON "AUTHORS" ("LOGIN"); + +CREATE INDEX "MEASURE_FILTERS_NAME" ON "MEASURE_FILTERS" ("NAME"); + +CREATE INDEX "MEASURE_FILTER_FAVS_USERID" ON "MEASURE_FILTER_FAVOURITES" ("USER_ID"); + +CREATE UNIQUE INDEX "ISSUES_KEE" ON "ISSUES" ("KEE"); + +CREATE INDEX "ISSUES_COMPONENT_UUID" ON "ISSUES" ("COMPONENT_UUID"); + +CREATE INDEX "ISSUES_PROJECT_UUID" ON "ISSUES" ("PROJECT_UUID"); + +CREATE INDEX "ISSUES_RULE_ID" ON "ISSUES" ("RULE_ID"); + +CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES" ("RESOLUTION"); + +CREATE INDEX "ISSUES_ASSIGNEE" ON "ISSUES" ("ASSIGNEE"); + +CREATE INDEX "ISSUES_CREATION_DATE" ON "ISSUES" ("ISSUE_CREATION_DATE"); + +CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES" ("UPDATED_AT"); + +CREATE INDEX "ISSUE_CHANGES_KEE" ON "ISSUE_CHANGES" ("KEE"); + +CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY" ON "ISSUE_CHANGES" ("ISSUE_KEY"); + +CREATE INDEX "ISSUE_FILTERS_NAME" ON "ISSUE_FILTERS" ("NAME"); + +CREATE INDEX "ISSUE_FILTER_FAVS_USER" ON "ISSUE_FILTER_FAVOURITES" ("USER_LOGIN"); + +CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS" ("LOGIN"); + +CREATE INDEX "USERS_UPDATED_AT" ON "USERS" ("UPDATED_AT"); + +CREATE UNIQUE INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES" ("GROUP_ID", "RESOURCE_ID", "ROLE"); + +CREATE UNIQUE INDEX "RULES_REPO_KEY" ON "RULES" ("PLUGIN_NAME", "PLUGIN_RULE_KEY"); + +CREATE UNIQUE INDEX "UNIQ_QUALITY_GATES" ON "QUALITY_GATES" ("NAME"); + +CREATE UNIQUE INDEX "ACTIVE_RULES_UNIQUE" ON "ACTIVE_RULES" ("PROFILE_ID","RULE_ID"); + +CREATE UNIQUE INDEX "UNIQ_QPROF_KEY" ON "RULES_PROFILES" ("KEE"); + +CREATE INDEX "FILE_SOURCES_PROJECT_UUID" ON "FILE_SOURCES" ("PROJECT_UUID"); + +CREATE UNIQUE INDEX "FILE_SOURCES_UUID_TYPE" ON "FILE_SOURCES" ("FILE_UUID", "DATA_TYPE"); + +CREATE INDEX "FILE_SOURCES_UPDATED_AT" ON "FILE_SOURCES" ("UPDATED_AT"); + +CREATE UNIQUE INDEX "UNIQ_PROJECT_QPROFILES" ON "PROJECT_QPROFILES" ("PROJECT_UUID", "PROFILE_KEY"); + +CREATE UNIQUE INDEX "CE_QUEUE_UUID" ON "CE_QUEUE" ("UUID"); + +CREATE INDEX "CE_QUEUE_COMPONENT_UUID" ON "CE_QUEUE" ("COMPONENT_UUID"); + +CREATE INDEX "CE_QUEUE_STATUS" ON "CE_QUEUE" ("STATUS"); + +CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY" ("UUID"); + +CREATE INDEX "CE_ACTIVITY_COMPONENT_UUID" ON "CE_ACTIVITY" ("COMPONENT_UUID"); + +CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS" ("TOKEN_HASH"); + +CREATE UNIQUE INDEX "USER_TOKENS_LOGIN_NAME" ON "USER_TOKENS" ("LOGIN", "NAME"); + +CREATE INDEX "CE_ACTIVITY_ISLASTKEY" ON "CE_ACTIVITY" ("IS_LAST_KEY"); + +CREATE INDEX "CE_ACTIVITY_ISLAST_STATUS" ON "CE_ACTIVITY" ("IS_LAST", "STATUS"); + +CREATE UNIQUE INDEX "UNIQ_PERM_TPL_CHARAC" ON "PERM_TPL_CHARACTERISTICS" ("TEMPLATE_ID", "PERMISSION_KEY"); -- 2.39.5