SCA-224 Start Recording Changes for Dependency RisksHEAD master

author: Mike Young <mike.young@sonarsource.com> 2025-04-17 11:55:22 -0400
committer: sonartech <sonartech@sonarsource.com> 2025-04-17 20:04:02 +0000
commit: ac8ec739b445da063ead88393f8293f19b2f98d4 (patch)
tree: 54c55c77f9adc2ed7bf8b7274aadc1a2e6ccd8f8 /server
parent: 342692267f0925d1fd9ce3a4da67aceef56b82a3 (diff)
download: sonarqube-master.tar.gz
sonarqube-master.zip
7 files changed, 240 insertions, 1 deletions
diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java b/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java
index 8f54baa76c7..a9001345f60 100644
--- a/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java
+++ b/server/sonar-db-core/src/main/java/org/sonar/db/version/SqTables.java
@@ -111,6 +111,7 @@ public final class SqTables {
     "sca_dependencies",
     "sca_issues",
     "sca_issues_releases",
+    "sca_issue_rels_changes",
     "sca_releases",
     "sca_vulnerability_issues",
     "scanner_analysis_cache",
diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl
index 4a6070fd752..6e10c00f147 100644
--- a/server/sonar-db-dao/src/schema/schema-sq.ddl
+++ b/server/sonar-db-dao/src/schema/schema-sq.ddl
@@ -1058,6 +1058,18 @@ CREATE TABLE "SCA_DEPENDENCIES"(
 ALTER TABLE "SCA_DEPENDENCIES" ADD CONSTRAINT "PK_SCA_DEPENDENCIES" PRIMARY KEY("UUID");
 CREATE INDEX "SCA_DEPENDENCIES_RELEASE_UUID" ON "SCA_DEPENDENCIES"("SCA_RELEASE_UUID" NULLS FIRST);
 
+CREATE TABLE "SCA_ISSUE_RELS_CHANGES"(
+    "UUID" CHARACTER VARYING(40) NOT NULL,
+    "SCA_ISSUES_RELEASES_UUID" CHARACTER VARYING(40) NOT NULL,
+    "USER_UUID" CHARACTER VARYING(40),
+    "CHANGE_TYPE" CHARACTER VARYING(40) NOT NULL,
+    "CHANGE_DATA" CHARACTER LARGE OBJECT,
+    "CREATED_AT" BIGINT NOT NULL,
+    "UPDATED_AT" BIGINT NOT NULL
+);
+ALTER TABLE "SCA_ISSUE_RELS_CHANGES" ADD CONSTRAINT "PK_SCA_ISSUE_RELS_CHANGES" PRIMARY KEY("UUID");
+CREATE INDEX "SCA_ISS_RELS_CHANGES_IR_UUID" ON "SCA_ISSUE_RELS_CHANGES"("SCA_ISSUES_RELEASES_UUID" NULLS FIRST);
+
 CREATE TABLE "SCA_ISSUES"(
     "UUID" CHARACTER VARYING(40) NOT NULL,
     "SCA_ISSUE_TYPE" CHARACTER VARYING(40) NOT NULL,
diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateIndexOnScaIssuesReleaseChangesReleaseIdIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateIndexOnScaIssuesReleaseChangesReleaseIdIT.java
new file mode 100644
index 00000000000..0bd955cf64e
--- /dev/null
+++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateIndexOnScaIssuesReleaseChangesReleaseIdIT.java
@@ -0,0 +1,52 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.v202503;
+
+import java.sql.SQLException;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.RegisterExtension;
+import org.sonar.db.MigrationDbTester;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static org.sonar.db.MigrationDbTester.createForMigrationStep;
+import static org.sonar.server.platform.db.migration.version.v202503.CreateIndexOnScaIssuesReleaseChangesReleaseId.COLUMN_NAME_SCA_RELEASE_UUID;
+import static org.sonar.server.platform.db.migration.version.v202503.CreateIndexOnScaIssuesReleaseChangesReleaseId.INDEX_NAME;
+import static org.sonar.server.platform.db.migration.version.v202503.CreateIndexOnScaIssuesReleaseChangesReleaseId.TABLE_NAME;
+
+class CreateIndexOnScaIssuesReleaseChangesReleaseIdIT {
+  @RegisterExtension
+  public final MigrationDbTester db = createForMigrationStep(CreateIndexOnScaIssuesReleaseChangesReleaseId.class);
+  private final DdlChange underTest = new CreateIndexOnScaIssuesReleaseChangesReleaseId(db.database());
+
+  @Test
+  void execute_shouldCreateIndex() throws SQLException {
+    db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME);
+    underTest.execute();
+    db.assertIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_SCA_RELEASE_UUID);
+  }
+
+  @Test
+  void execute_shouldBeReentrant() throws SQLException {
+    db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME);
+    underTest.execute();
+    underTest.execute();
+    db.assertIndex(TABLE_NAME, INDEX_NAME, COLUMN_NAME_SCA_RELEASE_UUID);
+  }
+}
diff --git a/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateScaIssuesReleasesChangesTableIT.java b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateScaIssuesReleasesChangesTableIT.java
new file mode 100644
index 00000000000..34d87e0b2b7
--- /dev/null
+++ b/server/sonar-db-migration/src/it/java/org/sonar/server/platform/db/migration/version/v202503/CreateScaIssuesReleasesChangesTableIT.java
@@ -0,0 +1,63 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.v202503;
+
+import java.sql.SQLException;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.RegisterExtension;
+import org.sonar.db.MigrationDbTester;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+import static java.sql.Types.BIGINT;
+import static java.sql.Types.CLOB;
+import static java.sql.Types.VARCHAR;
+import static org.sonar.db.MigrationDbTester.createForMigrationStep;
+import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE;
+
+class CreateScaIssuesReleasesChangesTableIT {
+  private static final String TABLE_NAME = "sca_issue_rels_changes";
+
+  @RegisterExtension
+  public final MigrationDbTester db = createForMigrationStep(CreateScaIssuesReleasesChangesTable.class);
+  private final DdlChange underTest = new CreateScaIssuesReleasesChangesTable(db.database());
+
+  @Test
+  void execute_shouldCreateTable() throws SQLException {
+    db.assertTableDoesNotExist(TABLE_NAME);
+    underTest.execute();
+    db.assertTableExists(TABLE_NAME);
+    db.assertPrimaryKey(TABLE_NAME, "pk_sca_issue_rels_changes", "uuid");
+    db.assertColumnDefinition(TABLE_NAME, "uuid", VARCHAR, UUID_SIZE, false);
+    db.assertColumnDefinition(TABLE_NAME, "sca_issues_releases_uuid", VARCHAR, UUID_SIZE, false);
+    db.assertColumnDefinition(TABLE_NAME, "user_uuid", VARCHAR, UUID_SIZE, true);
+    db.assertColumnDefinition(TABLE_NAME, "change_type", VARCHAR, 40, false);
+    db.assertColumnDefinition(TABLE_NAME, "change_data", CLOB, null, true);
+    db.assertColumnDefinition(TABLE_NAME, "created_at", BIGINT, null, false);
+    db.assertColumnDefinition(TABLE_NAME, "updated_at", BIGINT, null, false);
+  }
+
+  @Test
+  void execute_shouldBeReentrant() throws SQLException {
+    db.assertTableDoesNotExist(TABLE_NAME);
+    underTest.execute();
+    underTest.execute();
+    db.assertTableExists(TABLE_NAME);
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateIndexOnScaIssuesReleaseChangesReleaseId.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateIndexOnScaIssuesReleaseChangesReleaseId.java
new file mode 100644
index 00000000000..7054dd07fe4
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateIndexOnScaIssuesReleaseChangesReleaseId.java
@@ -0,0 +1,54 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.v202503;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.db.DatabaseUtils;
+import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder;
+import org.sonar.server.platform.db.migration.step.DdlChange;
+
+public class CreateIndexOnScaIssuesReleaseChangesReleaseId extends DdlChange {
+  static final String TABLE_NAME = "sca_issue_rels_changes";
+  static final String INDEX_NAME = "sca_iss_rels_changes_ir_uuid";
+  static final String COLUMN_NAME_SCA_RELEASE_UUID = "sca_issues_releases_uuid";
+
+  public CreateIndexOnScaIssuesReleaseChangesReleaseId(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    try (Connection connection = getDatabase().getDataSource().getConnection()) {
+      createIndex(context, connection);
+    }
+  }
+
+  private void createIndex(Context context, Connection connection) {
+    if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, connection)) {
+      context.execute(new CreateIndexBuilder(getDialect())
+        .setTable(TABLE_NAME)
+        .setName(INDEX_NAME)
+        .addColumn(COLUMN_NAME_SCA_RELEASE_UUID, false)
+        .build());
+    }
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateScaIssuesReleasesChangesTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateScaIssuesReleasesChangesTable.java
new file mode 100644
index 00000000000..77425f7a9b0
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/CreateScaIssuesReleasesChangesTable.java
@@ -0,0 +1,55 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2025 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.v202503;
+
+import java.sql.SQLException;
+import java.util.List;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.sql.CreateTableBuilder;
+import org.sonar.server.platform.db.migration.step.CreateTableChange;
+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.ClobColumnDef.newClobColumnDefBuilder;
+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 CreateScaIssuesReleasesChangesTable extends CreateTableChange {
+  public static final String TABLE_NAME = "sca_issue_rels_changes";
+
+  protected CreateScaIssuesReleasesChangesTable(Database db) {
+    super(db, TABLE_NAME);
+  }
+
+  @Override
+  public void execute(DdlChange.Context context, String tableName) throws SQLException {
+    List<String> createQuery = new CreateTableBuilder(getDialect(), tableName)
+      .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build())
+      .addColumn(newVarcharColumnDefBuilder().setColumnName("sca_issues_releases_uuid").setIsNullable(false).setLimit(UUID_SIZE).build())
+      .addColumn(newVarcharColumnDefBuilder().setColumnName("user_uuid").setIsNullable(true).setLimit(UUID_SIZE).build())
+      .addColumn(newVarcharColumnDefBuilder().setColumnName("change_type").setIsNullable(false).setLimit(40).build())
+      .addColumn(newClobColumnDefBuilder().setColumnName("change_data").setIsNullable(true).build())
+      .addColumn(newBigIntegerColumnDefBuilder().setColumnName("created_at").setIsNullable(false).build())
+      .addColumn(newBigIntegerColumnDefBuilder().setColumnName("updated_at").setIsNullable(false).build())
+      .build();
+
+    context.execute(createQuery);
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java
index 08f9c793c19..656ca4db2e5 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v202503/DbVersion202503.java
@@ -48,6 +48,8 @@ public class DbVersion202503 implements DbVersion {
       .add(2025_03_009, "Migrate to is_new on SCA dependencies", MigrateToIsNewOnScaDependencies.class)
       .add(2025_03_010, "Drop new_in_pull_request column from SCA releases", DropNewInPullRequestFromScaReleasesTable.class)
       .add(2025_03_011, "Drop new_in_pull_request column from SCA dependencies", DropNewInPullRequestFromScaDependenciesTable.class)
-      .add(2025_03_012, "Add assignee to SCA issues releases", AddAssigneeToScaIssuesReleases.class);
+      .add(2025_03_012, "Add assignee to SCA issues releases", AddAssigneeToScaIssuesReleases.class)
+      .add(2025_03_013, "Create ScaIssuesReleasesHistory table", CreateScaIssuesReleasesChangesTable.class)
+      .add(2025_03_014, "Create index for sca_issues_releases UUID on changes table", CreateIndexOnScaIssuesReleaseChangesReleaseId.class);
   }
 }
author	Mike Young <mike.young@sonarsource.com>	2025-04-17 11:55:22 -0400
committer	sonartech <sonartech@sonarsource.com>	2025-04-17 20:04:02 +0000
commit	ac8ec739b445da063ead88393f8293f19b2f98d4 (patch)
tree	54c55c77f9adc2ed7bf8b7274aadc1a2e6ccd8f8 /server
parent	342692267f0925d1fd9ce3a4da67aceef56b82a3 (diff)
download	sonarqube-master.tar.gz sonarqube-master.zip