diff options
Diffstat (limited to 'server/sonar-db-migration')
17 files changed, 953 insertions, 3 deletions
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/BaseSqlStatement.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/BaseSqlStatement.java index 6444617dc6e..ebb62540d0b 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/BaseSqlStatement.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/BaseSqlStatement.java @@ -19,6 +19,7 @@ */ package org.sonar.server.platform.db.migration.step; +import java.io.ByteArrayInputStream; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Timestamp; @@ -77,6 +78,16 @@ class BaseSqlStatement<CHILD extends SqlStatement> implements SqlStatement<CHILD } @Override + public CHILD setBytes(int columnIndex, @Nullable byte[] value) throws SQLException { + if (value == null) { + pstmt.setNull(columnIndex, Types.BINARY); + } else { + pstmt.setBinaryStream(columnIndex, new ByteArrayInputStream(value)); + } + return (CHILD) this; + } + + @Override public CHILD setDouble(int columnIndex, @Nullable Double value) throws SQLException { if (value == null) { pstmt.setNull(columnIndex, Types.DECIMAL); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java index 64aea4df5d9..2264d774ca5 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DataChange.java @@ -22,6 +22,7 @@ package org.sonar.server.platform.db.migration.step; import java.sql.Connection; import java.sql.SQLException; import org.sonar.db.Database; +import org.sonar.db.dialect.Dialect; public abstract class DataChange implements MigrationStep { @@ -31,6 +32,10 @@ public abstract class DataChange implements MigrationStep { this.db = db; } + protected final Dialect getDialect() { + return db.getDialect(); + } + @Override public final void execute() throws SQLException { try (Connection readConnection = createReadUncommittedConnection(); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/SqlStatement.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/SqlStatement.java index f9628f7c658..c15db0f90d6 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/SqlStatement.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/SqlStatement.java @@ -26,6 +26,8 @@ import javax.annotation.Nullable; public interface SqlStatement<CHILD extends SqlStatement> extends AutoCloseable { CHILD setBoolean(int columnIndex, @Nullable Boolean value) throws SQLException; + CHILD setBytes(int columnIndex, @Nullable byte[] value) throws SQLException; + CHILD setDate(int columnIndex, @Nullable Date value) throws SQLException; CHILD setDouble(int columnIndex, @Nullable Double value) throws SQLException; diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasures.java new file mode 100644 index 00000000000..bc37773d96c --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasures.java @@ -0,0 +1,125 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.sql.CreateTableBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.BlobColumnDef.newBlobColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.DecimalColumnDef.newDecimalColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.IntegerColumnDef.newIntegerColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class CreateTableLiveMeasures extends DdlChange { + + private static final String TABLE_NAME = "live_measures"; + + public CreateTableLiveMeasures(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new CreateTableBuilder(getDialect(), TABLE_NAME) + .addPkColumn(newVarcharColumnDefBuilder() + .setColumnName("uuid") + .setIsNullable(false) + .setLimit(VarcharColumnDef.UUID_SIZE) + .build()) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("project_uuid") + .setIsNullable(false) + .setLimit(VarcharColumnDef.UUID_VARCHAR_SIZE) + .build()) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("component_uuid") + .setIsNullable(false) + .setLimit(VarcharColumnDef.UUID_VARCHAR_SIZE) + .build()) + .addColumn(newIntegerColumnDefBuilder() + .setColumnName("metric_id") + .setIsNullable(false) + .build()) + .addColumn(newDecimalColumnDefBuilder() + .setColumnName("value") + .setPrecision(38) + .setScale(20) + .build()) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("text_value") + .setIsNullable(true) + .setLimit(4_000) + .build()) + .addColumn(newDecimalColumnDefBuilder() + .setColumnName("variation") + .setPrecision(38) + .setScale(20) + .build()) + .addColumn(newBlobColumnDefBuilder() + .setColumnName("measure_data") + .build()) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("update_marker") + .setIsNullable(true) + .setLimit(UUID_SIZE) + .build()) + .addColumn(newBigIntegerColumnDefBuilder() + .setColumnName("created_at") + .setIsNullable(false) + .build()) + .addColumn(newBigIntegerColumnDefBuilder() + .setColumnName("updated_at") + .setIsNullable(false) + .build()) + .build()); + + context.execute(new CreateIndexBuilder(getDialect()) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("project_uuid") + .setIsNullable(false) + .setLimit(VarcharColumnDef.UUID_VARCHAR_SIZE) + .build()) + .setUnique(false) + .setTable(TABLE_NAME) + .setName("live_measures_project") + .build()); + + context.execute(new CreateIndexBuilder(getDialect()) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("component_uuid") + .setIsNullable(false) + .setLimit(VarcharColumnDef.UUID_VARCHAR_SIZE) + .build()) + .addColumn(newIntegerColumnDefBuilder() + .setColumnName("metric_id") + .setIsNullable(false) + .build()) + .setUnique(true) + .setTable(TABLE_NAME) + .setName("live_measures_component") + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70.java index f03ca93038b..e51e2426a93 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70.java @@ -32,7 +32,11 @@ public class DbVersion70 implements DbVersion { .add(1902, "Make QUALITY_GATES.IS_BUILT_IN not null", MakeQualityGatesIsBuiltInNotNullable.class) .add(1903, "Remove quality gates loaded templates", RemoveQualityGateLoadedTemplates.class) .add(1904, "Rename quality gate \"SonarQube way\" to \"Sonar way\"", RenameOldSonarQubeWayQualityGate.class) - .add(1905, "Drop LOADED_TEMPLATES table", DropLoadedTemplatesTable.class); + .add(1905, "Drop LOADED_TEMPLATES table", DropLoadedTemplatesTable.class) + .add(1906, "Create table live_measures", CreateTableLiveMeasures.class) + .add(1907, "Populate table live_measures", PopulateLiveMeasures.class) + .add(1908, "Delete person and file measures", DeletePersonAndFileMeasures.class) + .add(1909, "Drop index on project_measures.person_id", DropIndexOnPersonMeasures.class) + ; } - } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasures.java new file mode 100644 index 00000000000..92ba449f7cd --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasures.java @@ -0,0 +1,84 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.dialect.H2; +import org.sonar.db.dialect.MsSql; +import org.sonar.db.dialect.MySql; +import org.sonar.db.dialect.Oracle; +import org.sonar.db.dialect.PostgreSql; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class DeletePersonAndFileMeasures extends DataChange { + public DeletePersonAndFileMeasures(Database db) { + super(db); + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("select uuid from snapshots"); + massUpdate.rowPluralName("snapshots"); + massUpdate.update(getDeleteSql()); + + massUpdate.execute((row, update) -> { + update.setString(1, row.getString(1)); + return true; + }); + } + + private String getDeleteSql() { + switch (getDialect().getId()) { + case MySql.ID: + case MsSql.ID: + return "delete pm from project_measures pm " + + "inner join projects c on c.uuid = pm.component_uuid " + + "where pm.analysis_uuid = ? " + + "and (c.qualifier in ('UTS', 'FIL') or pm.person_id is not null)"; + case H2.ID: + return "delete from project_measures " + + "where id in ( " + + " select pm.id from project_measures pm " + + " inner join projects c on c.uuid = pm.component_uuid " + + " where pm.analysis_uuid = ? " + + " and (c.qualifier in ('UTS', 'FIL') or pm.person_id is not null) " + + ")"; + case PostgreSql.ID: + return "delete from project_measures pm " + + "using projects c " + + "where pm.analysis_uuid = ? " + + "and c.uuid = pm.component_uuid " + + "and (c.qualifier in ('UTS', 'FIL') or pm.person_id is not null) "; + case Oracle.ID: + return "delete from project_measures pm where exists (" + + " select 1 from project_measures pm2 " + + " inner join projects c on c.uuid = pm2.component_uuid " + + " where pm2.analysis_uuid = ? " + + " and (c.qualifier in ('UTS', 'FIL') or pm.person_id is not null) " + + " and pm.id = pm2.id" + + ")"; + default: + throw new IllegalStateException("Unsupported DB dialect: " + getDialect()); + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasures.java new file mode 100644 index 00000000000..c11e4e48c8b --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasures.java @@ -0,0 +1,40 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.DropIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class DropIndexOnPersonMeasures extends DdlChange { + + public DropIndexOnPersonMeasures(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new DropIndexBuilder(getDialect()) + .setTable("project_measures") + .setName("measures_person") + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasures.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasures.java new file mode 100644 index 00000000000..1d226fca4f1 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasures.java @@ -0,0 +1,72 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import org.sonar.api.utils.System2; +import org.sonar.core.util.Uuids; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class PopulateLiveMeasures extends DataChange { + + private final System2 system2; + + public PopulateLiveMeasures(Database db, System2 system2) { + super(db); + this.system2 = system2; + } + + @Override + protected void execute(Context context) throws SQLException { + long now = system2.now(); + // reentrancy of migration + context.prepareUpsert("TRUNCATE TABLE live_measures").execute(); + + MassUpdate massUpdate = context.prepareMassUpdate(); + massUpdate.select("SELECT p.uuid, p.project_uuid, pm.metric_id, pm.value, pm.text_value, pm.variation_value_1, pm.measure_data " + + "FROM project_measures pm " + + "INNER JOIN projects p on p.uuid = pm.component_uuid " + + "INNER JOIN snapshots s on s.uuid = pm.analysis_uuid " + + "WHERE s.islast = ?") + .setBoolean(1, true); + + massUpdate.update("INSERT INTO live_measures " + + "(uuid, component_uuid, project_uuid, metric_id, value, text_value, variation, measure_data, created_at, updated_at) " + + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); + + massUpdate.rowPluralName("live measures"); + massUpdate.execute((row, update) -> { + update.setString(1, Uuids.create()); + update.setString(2, row.getString(1)); + update.setString(3, row.getString(2)); + update.setInt(4, row.getInt(3)); + update.setDouble(5, row.getNullableDouble(4)); + update.setString(6, row.getString(5)); + update.setDouble(7, row.getNullableDouble(6)); + update.setBytes(8, row.getNullableBytes(7)); + update.setLong(9, now); + update.setLong(10, now); + return true; + }); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasuresTest.java new file mode 100644 index 00000000000..806e0ea00e8 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasuresTest.java @@ -0,0 +1,59 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import java.sql.Types; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +import static org.assertj.core.api.Assertions.assertThat; + +public class CreateTableLiveMeasuresTest { + private static final String TABLE = "live_measures"; + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateTableLiveMeasuresTest.class, "empty.sql"); + + private CreateTableLiveMeasures underTest = new CreateTableLiveMeasures(db.database()); + + @Test + public void creates_table_on_empty_db() throws SQLException { + underTest.execute(); + + assertThat(db.countRowsOfTable(TABLE)).isEqualTo(0); + + db.assertColumnDefinition(TABLE, "uuid", Types.VARCHAR, 40, false); + db.assertColumnDefinition(TABLE, "project_uuid", Types.VARCHAR, 50, false); + db.assertColumnDefinition(TABLE, "component_uuid", Types.VARCHAR, 50, false); + db.assertColumnDefinition(TABLE, "metric_id", Types.INTEGER, null, false); + db.assertColumnDefinition(TABLE, "value", Types.DOUBLE, null, true); + db.assertColumnDefinition(TABLE, "text_value", Types.VARCHAR, 4_000, true); + db.assertColumnDefinition(TABLE, "variation", Types.DOUBLE, null, true); + db.assertColumnDefinition(TABLE, "measure_data", Types.BLOB, null, true); + db.assertColumnDefinition(TABLE, "update_marker", Types.VARCHAR, 40, true); + db.assertColumnDefinition(TABLE, "created_at", Types.BIGINT, null, false); + db.assertColumnDefinition(TABLE, "updated_at", Types.BIGINT, null, false); + + db.assertIndex(TABLE, "live_measures_project", "project_uuid"); + db.assertUniqueIndex(TABLE, "live_measures_component", "component_uuid", "metric_id"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70Test.java index ece11091b41..fa47fed02ed 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DbVersion70Test.java @@ -35,7 +35,7 @@ public class DbVersion70Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 6); + verifyMigrationCount(underTest, 10); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasuresTest.java new file mode 100644 index 00000000000..2019003f8e0 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasuresTest.java @@ -0,0 +1,125 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import java.util.concurrent.atomic.AtomicInteger; +import org.apache.commons.lang.math.RandomUtils; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static org.assertj.core.api.Assertions.assertThat; + +public class DeletePersonAndFileMeasuresTest { + private static final AtomicInteger GENERATOR = new AtomicInteger(); + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DeletePersonAndFileMeasuresTest.class, "initial.sql"); + + private DataChange underTest = new DeletePersonAndFileMeasures(db.database()); + + @Test + public void delete_file_and_person_measures() throws SQLException { + insertComponent("P1", "PRJ", "TRK"); + insertComponent("D1", "DIR", "DIR"); + insertComponent("F1", "FIL", "FIL"); + insertComponent("F2", "FIL", "UTS"); + insertSnapshot("S1", "P1", false); + insertSnapshot("S2", "P1", true); + // past measures + long m1 = insertMeasure("P1", "S1"); + long m2 = insertMeasure("D1", "S1"); + long m3 = insertMeasure("F1", "S1"); + long m4 = insertMeasure("F2", "S1"); + long m5 = insertPersonMeasure("P1", "S1"); + long m6 = insertPersonMeasure("F1", "S1"); + // last measures + long m7 = insertMeasure("P1", "S2"); + long m8 = insertMeasure("D1", "S2"); + long m9 = insertMeasure("F1", "S2"); + long m10 = insertMeasure("F2", "S2"); + long m11 = insertPersonMeasure("P1", "S2"); + long m12 = insertPersonMeasure("F1", "S2"); + + underTest.execute(); + + assertThat(db.countRowsOfTable("PROJECTS")).isEqualTo(4); + assertThat(db.countRowsOfTable("SNAPSHOTS")).isEqualTo(2); + assertThatMeasuresAreExactly(m1, m2, m7, m8); + + // migration is re-entrant + underTest.execute(); + assertThat(db.countRowsOfTable("PROJECTS")).isEqualTo(4); + assertThat(db.countRowsOfTable("SNAPSHOTS")).isEqualTo(2); + assertThatMeasuresAreExactly(m1, m2, m7, m8); + } + + private void assertThatMeasuresAreExactly(long... expectedMeasureIds) { + long[] ids = db.select("select id as \"id\" from project_measures") + .stream() + .mapToLong(m -> (Long) m.get("id")) + .toArray(); + assertThat(ids).containsOnly(expectedMeasureIds); + } + + private void insertComponent(String uuid, String scope, String qualifier) { + db.executeInsert("PROJECTS", + "ORGANIZATION_UUID", "O1", + "KEE", "" + GENERATOR.incrementAndGet(), + "UUID", uuid, + "PROJECT_UUID", "" + GENERATOR.incrementAndGet(), + "MAIN_BRANCH_PROJECT_UUID", "" + GENERATOR.incrementAndGet(), + "UUID_PATH", ".", + "ROOT_UUID", "" + GENERATOR.incrementAndGet(), + "PRIVATE", "true", + "QUALIFIER", qualifier, + "SCOPE", scope); + } + + private void insertSnapshot(String uuid, String projectUuid, boolean last) { + db.executeInsert("SNAPSHOTS", + "UUID", uuid, + "COMPONENT_UUID", projectUuid, + "STATUS", "P", + "ISLAST", last); + } + + private long insertMeasure(String componentUuid, String analysisUuid) { + long id = GENERATOR.incrementAndGet(); + db.executeInsert("PROJECT_MEASURES", + "ID", id, + "METRIC_ID", "42", + "COMPONENT_UUID", componentUuid, + "ANALYSIS_UUID", analysisUuid); + return id; + } + + private long insertPersonMeasure(String componentUuid, String analysisUuid) { + long id = GENERATOR.incrementAndGet(); + db.executeInsert("PROJECT_MEASURES", + "ID", id, + "METRIC_ID", "42", + "COMPONENT_UUID", componentUuid, + "ANALYSIS_UUID", analysisUuid, + "PERSON_ID", RandomUtils.nextInt(100)); + return id; + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasuresTest.java new file mode 100644 index 00000000000..08bf2079eec --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasuresTest.java @@ -0,0 +1,48 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class DropIndexOnPersonMeasuresTest { + + private static final String TABLE = "project_measures"; + private static final String INDEX = "measures_person"; + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DropIndexOnPersonMeasuresTest.class, "initial.sql"); + + private DdlChange underTest = new DropIndexOnPersonMeasures(db.database()); + + @Test + public void drop_index() throws SQLException { + db.assertIndex(TABLE, INDEX, "person_id"); + + underTest.execute(); + + db.assertIndexDoesNotExist(TABLE, INDEX); + } +} + + diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasuresTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasuresTest.java new file mode 100644 index 00000000000..7a2fc7e9e2c --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasuresTest.java @@ -0,0 +1,140 @@ +/* + * 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.v70; + +import java.sql.SQLException; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.function.Function; +import org.assertj.core.groups.Tuple; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.api.utils.System2; +import org.sonar.api.utils.internal.TestSystem2; +import org.sonar.db.CoreDbTester; + +import static org.assertj.core.api.Assertions.assertThat; +import static org.assertj.core.api.Assertions.tuple; + +public class PopulateLiveMeasuresTest { + + private System2 system2 = new TestSystem2().setNow(1_500_000_000_000L); + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PopulateLiveMeasuresTest.class, "initial.sql"); + + private PopulateLiveMeasures underTest = new PopulateLiveMeasures(db.database(), system2); + + @Test + public void do_nothing_when_no_data() throws SQLException { + assertThat(db.countRowsOfTable("PROJECT_MEASURES")).isEqualTo(0); + underTest.execute(); + assertThat(db.countRowsOfTable("LIVE_MEASURES")).isEqualTo(0); + } + + @Test + public void execute_must_update_database() throws SQLException { + generateProjectMeasures(); + + underTest.execute(); + + assertThat(getLiveMeasures()).extracting( + field("COMPONENT_UUID"), + field("PROJECT_UUID"), + field("METRIC_ID"), + field("VALUE"), + field("TEXT_VALUE"), + field("VARIATION"), + field("MEASURE_DATA") + ).containsExactlyInAnyOrder(generateLiveMeasures()); + } + + private Function<Map<String, Object>, Object> field(String name) { + return m -> m.get(name); + } + + @Test + public void migration_is_reentrant() throws SQLException { + generateProjectMeasures(); + + underTest.execute(); + underTest.execute(); + + assertThat(getLiveMeasures()).extracting( + field("COMPONENT_UUID"), + field("PROJECT_UUID"), + field("METRIC_ID"), + field("VALUE"), + field("TEXT_VALUE"), + field("VARIATION"), + field("MEASURE_DATA") + ).containsExactlyInAnyOrder(generateLiveMeasures()); + } + + private void generateProjectMeasures() { + Map<String, Object> project = new HashMap<>(); + project.put("UUID", "PRJ1"); + project.put("ORGANIZATION_UUID", "ORG1"); + project.put("UUID_PATH", "X"); + project.put("ROOT_UUID", "X"); + project.put("PROJECT_UUID", "PRJ1"); + project.put("PRIVATE", "FALSE"); + db.executeInsert("PROJECTS", project); + + Map<String, Object> analysis1 = new HashMap<>(); + analysis1.put("UUID", "A1"); + analysis1.put("ISLAST", "FALSE"); + analysis1.put("COMPONENT_UUID", "PRJ1"); + db.executeInsert("SNAPSHOTS", analysis1); + + Map<String, Object> analysis2 = new HashMap<>(); + analysis2.put("UUID", "A2"); + analysis2.put("ISLAST", "TRUE"); + analysis2.put("COMPONENT_UUID", "PRJ1"); + db.executeInsert("SNAPSHOTS", analysis2); + + Map<String, Object> measure1 = new HashMap<>(); + measure1.put("COMPONENT_UUID", "PRJ1"); + measure1.put("ANALYSIS_UUID", "A1"); + measure1.put("METRIC_ID", "123"); + db.executeInsert("PROJECT_MEASURES", measure1); + + Map<String, Object> measure2 = new HashMap<>(); + measure2.put("COMPONENT_UUID", "PRJ1"); + measure2.put("ANALYSIS_UUID", "A2"); + measure2.put("METRIC_ID", "123"); + measure2.put("VALUE", "234"); + measure2.put("TEXT_VALUE", "TEXT_VALUEx"); + measure2.put("VARIATION_VALUE_1", "345"); + measure2.put("MEASURE_DATA", "FFFF"); + db.executeInsert("PROJECT_MEASURES", measure2); + } + + private List<Map<String, Object>> getLiveMeasures() { + return db.select("SELECT * FROM LIVE_MEASURES"); + } + + private Tuple[] generateLiveMeasures() { + return new Tuple[] { + tuple("PRJ1", "PRJ1", 123L, 234.0, "TEXT_VALUEx", 345.0, new byte[] {-1, -1}) + }; + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasuresTest/empty.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasuresTest/empty.sql new file mode 100644 index 00000000000..e69de29bb2d --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/CreateTableLiveMeasuresTest/empty.sql diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasuresTest/initial.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasuresTest/initial.sql new file mode 100644 index 00000000000..be55d6f14e8 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/DeletePersonAndFileMeasuresTest/initial.sql @@ -0,0 +1,98 @@ +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), + "MAIN_BRANCH_PROJECT_UUID" VARCHAR(50), + "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 "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 "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"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasuresTest/initial.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasuresTest/initial.sql new file mode 100644 index 00000000000..00ef5e22307 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/DropIndexOnPersonMeasuresTest/initial.sql @@ -0,0 +1,21 @@ +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"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasuresTest/initial.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasuresTest/initial.sql new file mode 100644 index 00000000000..b3dc4557dbb --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v70/PopulateLiveMeasuresTest/initial.sql @@ -0,0 +1,116 @@ +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), + "MAIN_BRANCH_PROJECT_UUID" VARCHAR(50), + "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 "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 "LIVE_MEASURES" ( + "UUID" VARCHAR(40) NOT NULL PRIMARY KEY, + "PROJECT_UUID" VARCHAR(50) NOT NULL, + "COMPONENT_UUID" VARCHAR(50) NOT NULL, + "METRIC_ID" INTEGER NOT NULL, + "VALUE" DOUBLE, + "TEXT_VALUE" VARCHAR(4000), + "VARIATION" DOUBLE, + "MEASURE_DATA" BINARY, + "UPDATE_MARKER" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +CREATE INDEX "LIVE_MEASURES_PROJECT" ON "LIVE_MEASURES" ("PROJECT_UUID"); +CREATE UNIQUE INDEX "LIVE_MEASURES_COMPONENT" ON "LIVE_MEASURES" ("COMPONENT_UUID", "METRIC_ID"); + + +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"); + |