From 99c893d3b1425c4d4b6b56f1337bcd0835799681 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Mon, 29 May 2017 09:21:33 +0200 Subject: [PATCH] SONAR-9324 add database migration purging developer data --- .../db/migration/version/v65/DbVersion65.java | 2 +- .../version/v65/PurgeDeveloperData.java | 109 ++++++++ .../version/v65/DbVersion65Test.java | 2 +- .../version/v65/PurgeDeveloperDataTest.java | 238 ++++++++++++++++++ .../projects_and_child_tables.sql | 147 +++++++++++ 5 files changed, 496 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperData.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest/projects_and_child_tables.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java index 601b8c10a56..52a9b367d38 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java @@ -41,6 +41,6 @@ public class DbVersion65 implements DbVersion { .add(1711, "Drop index MANUAL_MEASURES.COMPONENT_UUID", DropIndexManualMeasuresComponentUuid.class) .add(1712, "Make MANUAL_MEASURES.COMPONENT_UUID not nullable", MakeManualMeasuresComponentUuidNotNullable.class) .add(1713, "Recreate index MANUAL_MEASURES.COMPONENT_UUID", RecreateIndexManualMeasuresComponentUuid.class) - ; + .add(1714, "Purge developer data", PurgeDeveloperData.class); } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperData.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperData.java new file mode 100644 index 00000000000..ee618c394de --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperData.java @@ -0,0 +1,109 @@ +/* + * 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.v65; + +import java.sql.SQLException; +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; + +public class PurgeDeveloperData extends DataChange { + public PurgeDeveloperData(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + purgeProjectCopies(context); + + purgeDevelopers(context); + } + + private void purgeProjectCopies(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select" + + " child.id, child.uuid" + + " from projects child" + + " inner join projects root on" + + " root.uuid = child.project_uuid" + + " and root.scope=?" + + " and root.qualifier=?" + + " where" + + " child.uuid <> child.project_uuid") + .setString(1, "PRJ") + .setString(2, "DEV"); + massUpdate.rowPluralName("purged project copies"); + massUpdate.update("delete from project_measures where component_uuid=?"); + massUpdate.update("delete from projects where uuid=?"); + massUpdate.execute(PurgeDeveloperData::handlePurgeProjectCopies); + } + + private static boolean handlePurgeProjectCopies(Select.Row row, SqlStatement update, int updateIndex) throws SQLException { + if (updateIndex < 0 || updateIndex > 1) { + throw new IllegalArgumentException("Unsupported updateIndex " + updateIndex); + } + String uuid = row.getString(2); + update.setString(1, uuid); + return true; + } + + private void purgeDevelopers(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select" + + " id, uuid" + + " from projects" + + " where" + + " scope=?" + + " and qualifier=?") + .setString(1, "PRJ") + .setString(2, "DEV"); + massUpdate.update("delete from project_measures where component_uuid=?"); + massUpdate.update("delete from ce_activity where component_uuid=?"); + massUpdate.update("delete from snapshots where component_uuid=?"); + massUpdate.update("delete from group_roles where resource_id=?"); + massUpdate.update("delete from user_roles where resource_id=?"); + massUpdate.update("delete from projects where project_uuid=?"); + massUpdate.rowPluralName("purged developers"); + massUpdate.execute(PurgeDeveloperData::handlePurgeDevelopers); + } + + private static boolean handlePurgeDevelopers(Select.Row row, SqlStatement update, int updateIndex) throws SQLException { + long id = row.getLong(1); + String uuid = row.getString(2); + switch (updateIndex) { + case 0: + case 1: + case 2: + update.setString(1, uuid); + return true; + case 3: + case 4: + update.setLong(1, id); + return true; + case 5: + update.setString(1, uuid); + return true; + default: + throw new IllegalArgumentException("Unsupported updateIndex " + updateIndex); + } + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java index eafd9346c20..d2967b35950 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java @@ -35,6 +35,6 @@ public class DbVersion65Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 14); + verifyMigrationCount(underTest, 15); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest.java new file mode 100644 index 00000000000..efbfaf4a535 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest.java @@ -0,0 +1,238 @@ +/* + * 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.v65; + +import java.sql.SQLException; +import java.util.Random; +import javax.annotation.Nullable; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.db.CoreDbTester; + +import static java.lang.String.valueOf; +import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic; +import static org.assertj.core.api.Assertions.assertThat; + +public class PurgeDeveloperDataTest { + + private static final String TABLE_PROJECT_MEASURE = "PROJECT_MEASURES"; + private static final String TABLE_CE_ACTIVITY = "CE_ACTIVITY"; + private static final String TABLE_SNAPSHOTS = "SNAPSHOTS"; + private static final String TABLE_GROUP_ROLES = "GROUP_ROLES"; + private static final String TABLE_USER_ROLES = "USER_ROLES"; + private static final String SCOPE_PROJECT = "PRJ"; + private static final String QUALIFIER_DEVELOPER = "DEV"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PurgeDeveloperDataTest.class, "projects_and_child_tables.sql"); + + private final Random random = new Random(); + private PurgeDeveloperData underTest = new PurgeDeveloperData(db.database()); + + @Test + public void execute_has_no_effect_when_table_PROJECTS_is_empty() throws SQLException { + insertProjectMeasure(randomAlphabetic(5), randomAlphabetic(5)); + insertCeActivity(randomAlphabetic(3)); + insertSnapshot(randomAlphabetic(3)); + insertGroupRole(random.nextInt()); + insertUserRole(random.nextInt()); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROJECT_MEASURE)).isEqualTo(1); + assertThat(db.countRowsOfTable(TABLE_CE_ACTIVITY)).isEqualTo(1); + assertThat(db.countRowsOfTable(TABLE_SNAPSHOTS)).isEqualTo(1); + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(1); + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(1); + } + + @Test + public void execute_deletes_developer_and_children_of_a_developer_ignoring_scope_and_qualifier() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + insertComponent(randomAlphabetic(3), randomAlphabetic(3), devUuid); + insertComponent(randomAlphabetic(3), randomAlphabetic(3), devUuid); + String notADevChild = insertComponent(randomAlphabetic(3), randomAlphabetic(3), null); + String notADev = insertComponent(SCOPE_PROJECT, randomAlphabetic(3), null); + + underTest.execute(); + + assertThat(db.select("select uuid as \"UUID\" from projects").stream().map(row -> row.get("UUID"))) + .containsOnly(notADev, notADevChild); + } + + @Test + public void execute_deletes_PROJECT_MEASURE_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + insertProjectMeasure(devUuid, randomAlphabetic(3)); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROJECT_MEASURE)).isZero(); + } + + @Test + public void execute_deletes_CE_ACTIVITY_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + insertCeActivity(devUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_CE_ACTIVITY)).isZero(); + } + + @Test + public void execute_deletes_SNAPSHOT_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + insertSnapshot(devUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_SNAPSHOTS)).isZero(); + } + + @Test + public void execute_deletes_roles_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + long devId = idOfComponent(devUuid); + insertUserRole(devId); + insertGroupRole(devId); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isZero(); + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isZero(); + } + + @Test + public void execute_deletes_PROJECT_MEASURE_of_children_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + String childUuid = insertComponent(randomAlphabetic(3), randomAlphabetic(3), devUuid); + insertProjectMeasure(childUuid, randomAlphabetic(3)); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_PROJECT_MEASURE)).isEqualTo(0); + } + + @Test + public void execute_does_not_delete_CE_ACTIVITY_of_children_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + String childUuid = insertComponent(randomAlphabetic(3), randomAlphabetic(3), devUuid); + insertCeActivity(childUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_CE_ACTIVITY)).isEqualTo(1); + } + + @Test + public void execute_does_not_delete_SNAPSHOT_of_children_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + String childUuid = insertComponent(randomAlphabetic(3), randomAlphabetic(3), devUuid); + insertSnapshot(childUuid); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_SNAPSHOTS)).isEqualTo(1); + } + + @Test + public void execute_does_not_delete_roles_of_children_of_developer() throws SQLException { + String devUuid = insertComponent(SCOPE_PROJECT, QUALIFIER_DEVELOPER, null); + String childUuid = insertComponent(randomAlphabetic(3), randomAlphabetic(3), devUuid); + long childId = idOfComponent(childUuid); + insertUserRole(childId); + insertGroupRole(childId); + + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE_GROUP_ROLES)).isEqualTo(1); + assertThat(db.countRowsOfTable(TABLE_USER_ROLES)).isEqualTo(1); + } + + private String insertComponent(String scope, String qualifier, @Nullable String projectUuid) { + String uuid = UuidFactoryFast.getInstance().create(); + db.executeInsert( + "PROJECTS", + "ORGANIZATION_UUID", randomAlphabetic(3), + "UUID", uuid, + "UUID_PATH", "path_" + uuid, + "ROOT_UUID", randomAlphabetic(4), + "PROJECT_UUID", projectUuid == null ? uuid : projectUuid, + "SCOPE", scope, + "QUALIFIER", qualifier, + "PRIVATE", valueOf(random.nextBoolean()), + "ENABLED", valueOf(random.nextBoolean())); + return uuid; + } + + private long idOfComponent(String uuid) { + return (Long) db.selectFirst("select id \"ID\" from projects where uuid = '" + uuid + "'").get("ID"); + } + + private void insertProjectMeasure(String componentUuid, String analysisUuid) { + db.executeInsert( + TABLE_PROJECT_MEASURE, + "METRIC_ID", valueOf(random.nextInt()), + "COMPONENT_UUID", componentUuid, + "ANALYSIS_UUID", analysisUuid); + } + + private void insertCeActivity(String componentUuid) { + db.executeInsert( + TABLE_CE_ACTIVITY, + "UUID", randomAlphabetic(5), + "TASK_TYPE", randomAlphabetic(3), + "COMPONENT_UUID", componentUuid, + "STATUS", randomAlphabetic(2), + "IS_LAST", valueOf(random.nextBoolean()), + "IS_LAST_KEY", randomAlphabetic(5), + "EXECUTION_COUNT", valueOf(random.nextInt()), + "SUBMITTED_AT", valueOf(random.nextLong()), + "CREATED_AT", valueOf(random.nextLong()), + "UPDATED_AT", valueOf(random.nextInt())); + } + + private void insertSnapshot(String componentUuid) { + db.executeInsert( + TABLE_SNAPSHOTS, + "UUID", randomAlphabetic(4), + "COMPONENT_UUID", componentUuid, + "STATUS", randomAlphabetic(3), + "ISLAST", valueOf(random.nextBoolean())); + } + + private void insertGroupRole(long componentId) { + db.executeInsert( + TABLE_GROUP_ROLES, + "ORGANIZATION_UUID", randomAlphabetic(3), + "RESOURCE_ID", valueOf(componentId), + "ROLE", randomAlphabetic(4)); + } + + private void insertUserRole(long componentId) { + db.executeInsert( + TABLE_USER_ROLES, + "ORGANIZATION_UUID", randomAlphabetic(3), + "RESOURCE_ID", valueOf(componentId), + "ROLE", randomAlphabetic(4)); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest/projects_and_child_tables.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest/projects_and_child_tables.sql new file mode 100644 index 00000000000..7f11d12ecbc --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/PurgeDeveloperDataTest/projects_and_child_tables.sql @@ -0,0 +1,147 @@ +CREATE TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400), + "UUID" VARCHAR(50) NOT NULL, + "UUID_PATH" VARCHAR(1500) NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "PRIVATE" BOOLEAN NOT NULL, + "TAGS" VARCHAR(500), + "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, + "B_CHANGED" BOOLEAN, + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_UUID_PATH" VARCHAR(1500), + "B_LANGUAGE" VARCHAR(20), + "B_LONG_NAME" VARCHAR(500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "B_NAME" VARCHAR(500), + "B_PATH" VARCHAR(2000), + "B_QUALIFIER" VARCHAR(10) +); +CREATE INDEX "PROJECTS_ORGANIZATION" ON "PROJECTS" ("ORGANIZATION_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 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, + "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 +); +CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES" ("COMPONENT_UUID"); +CREATE INDEX "MEASURES_ANALYSIS_METRIC" ON "PROJECT_MEASURES" ("ANALYSIS_UUID", "METRIC_ID"); +CREATE INDEX "MEASURES_PERSON" ON "PROJECT_MEASURES" ("PERSON_ID"); + + +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, + "WORKER_UUID" VARCHAR(40) NULL, + "EXECUTION_COUNT" INTEGER NOT 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, + "ERROR_MESSAGE" VARCHAR(1000), + "ERROR_STACKTRACE" CLOB(2147483647) +); +CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY" ("UUID"); +CREATE INDEX "CE_ACTIVITY_COMPONENT_UUID" ON "CE_ACTIVITY" ("COMPONENT_UUID"); +CREATE INDEX "CE_ACTIVITY_ISLASTKEY" ON "CE_ACTIVITY" ("IS_LAST_KEY"); +CREATE INDEX "CE_ACTIVITY_ISLAST_STATUS" ON "CE_ACTIVITY" ("IS_LAST", "STATUS"); + + +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, + "STATUS" VARCHAR(4) NOT NULL DEFAULT 'U', + "PURGE_STATUS" INTEGER, + "ISLAST" BOOLEAN NOT NULL DEFAULT FALSE, + "VERSION" VARCHAR(500), + "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 INDEX "SNAPSHOT_COMPONENT" ON "SNAPSHOTS" ("COMPONENT_UUID"); +CREATE UNIQUE INDEX "ANALYSES_UUID" ON "SNAPSHOTS" ("UUID"); + + +CREATE TABLE "GROUP_ROLES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "GROUP_ID" INTEGER, + "RESOURCE_ID" INTEGER, + "ROLE" VARCHAR(64) NOT NULL +); +CREATE INDEX "GROUP_ROLES_RESOURCE" ON "GROUP_ROLES" ("RESOURCE_ID"); +CREATE UNIQUE INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES" ("ORGANIZATION_UUID", "GROUP_ID", "RESOURCE_ID", "ROLE"); + + +CREATE TABLE "USER_ROLES" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "USER_ID" INTEGER, + "RESOURCE_ID" INTEGER, + "ROLE" VARCHAR(64) NOT NULL +); +CREATE INDEX "USER_ROLES_RESOURCE" ON "USER_ROLES" ("RESOURCE_ID"); +CREATE INDEX "USER_ROLES_USER" ON "USER_ROLES" ("USER_ID"); -- 2.39.5