SQRP-191 Ingest and persist the new "chains" field on each dependency

Co-authored-by: Travis Collins <travistx@gmail.com>

6 files changed, 113 insertions, 19 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java
index cd147772d29..92dd2215aaa 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatis.java
@@ -151,6 +151,7 @@ import org.sonar.db.rule.RuleChangeMapper;
 import org.sonar.db.rule.RuleMapper;
 import org.sonar.db.rule.RuleParamDto;
 import org.sonar.db.rule.RuleRepositoryMapper;
+import org.sonar.db.sca.ListOfListOfStringsTypeHandler;
 import org.sonar.db.sca.ScaDependenciesMapper;
 import org.sonar.db.sca.ScaDependencyDto;
 import org.sonar.db.sca.ScaReleasesMapper;
@@ -205,6 +206,9 @@ public class MyBatis {
 
     MyBatisConfBuilder confBuilder = new MyBatisConfBuilder(database);
 
+    // Custom column type handlers, keep them sorted alphabetically
+    confBuilder.loadTypeHandler(ListOfListOfStringsTypeHandler.class);
+
     // DTO aliases, keep them sorted alphabetically
     confBuilder.loadAlias("ActiveRule", ActiveRuleDto.class);
     confBuilder.loadAlias("ActiveRuleParam", ActiveRuleParamDto.class);
@@ -220,6 +224,7 @@ public class MyBatis {
     confBuilder.loadAlias("GithubOrganizationGroup", GithubOrganizationGroupDto.class);
     confBuilder.loadAlias("FilePathWithHash", FilePathWithHashDto.class);
     confBuilder.loadAlias("KeyWithUuid", KeyWithUuidDto.class);
+    confBuilder.loadAlias("ListOfListOfStringsTypeHandler", ListOfListOfStringsTypeHandler.class);
     confBuilder.loadAlias("Group", GroupDto.class);
     confBuilder.loadAlias("GroupMembership", GroupMembershipDto.class);
     confBuilder.loadAlias("GroupPermission", GroupPermissionDto.class);
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatisConfBuilder.java b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatisConfBuilder.java
index a9efb454c14..928e5982c42 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatisConfBuilder.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/MyBatisConfBuilder.java
@@ -51,11 +51,15 @@ class MyBatisConfBuilder {
     this.conf.setLocalCacheScope(LocalCacheScope.STATEMENT);
   }
 
-  void loadAlias(String alias, Class dtoClass) {
+  void loadTypeHandler(Class<?> typeHandlerClass) {
+    this.conf.getTypeHandlerRegistry().register(typeHandlerClass);
+  }
+
+  void loadAlias(String alias, Class<?> dtoClass) {
     conf.getTypeAliasRegistry().registerAlias(alias, dtoClass);
   }
 
-  void loadMapper(Class mapperClass) {
+  void loadMapper(Class<?> mapperClass) {
     String configFile = configFilePath(mapperClass);
     InputStream input = null;
     try {
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ListOfListOfStringsTypeHandler.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ListOfListOfStringsTypeHandler.java
new file mode 100644
index 00000000000..c9b5c309647
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ListOfListOfStringsTypeHandler.java
@@ -0,0 +1,60 @@
+/*
+ * 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.db.sca;
+
+import org.apache.ibatis.type.BaseTypeHandler;
+import org.apache.ibatis.type.JdbcType;
+import com.google.gson.Gson;
+import java.util.List;
+import com.google.gson.reflect.TypeToken;
+import java.lang.reflect.Type;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+import java.sql.ResultSet;
+import java.sql.CallableStatement;
+import org.apache.ibatis.type.MappedJdbcTypes;
+import org.apache.ibatis.type.MappedTypes;
+
+@MappedJdbcTypes(JdbcType.CLOB)
+@MappedTypes(List.class)
+public class ListOfListOfStringsTypeHandler extends BaseTypeHandler<List<List<String>>> {
+  private static final Gson GSON = new Gson();
+  private static final Type type = new TypeToken<List<List<String>>>() {}.getType();
+
+  @Override
+  public void setNonNullParameter(PreparedStatement ps, int i, List<List<String>> parameter, JdbcType jdbcType) throws SQLException {
+    ps.setString(i, GSON.toJson(parameter));
+  }
+
+  @Override
+  public List<List<String>> getNullableResult(ResultSet rs, String columnName) throws SQLException {
+    return GSON.fromJson(rs.getString(columnName), type);
+  }
+
+  @Override
+  public List<List<String>> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
+    return GSON.fromJson(rs.getString(columnIndex), type);
+  }
+
+  @Override
+  public List<List<String>> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
+    return GSON.fromJson(cs.getString(columnIndex), type);
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyDto.java
index d4b25dab14a..19c9eee9b94 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyDto.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyDto.java
@@ -19,6 +19,9 @@
  */
 package org.sonar.db.sca;
 
+import com.google.gson.Gson;
+import com.google.gson.reflect.TypeToken;
+import java.util.List;
 import javax.annotation.Nullable;
 
 import static com.google.common.base.Preconditions.checkArgument;
@@ -38,6 +41,7 @@ import static com.google.common.base.Preconditions.checkArgument;
  * @param scope                      the scope of the dependency e.g. "development"
  * @param userDependencyFilePath     path to the user-editable file where the dependency was found ("manifest") e.g. package.json
  * @param lockfileDependencyFilePath path to the machine-maintained lockfile where the dependency was found e.g. package-lock.json
+ * @param chains                     a list of the purl chains that require the dependency, stored as JSON string, e.g. [["pkg:npm/foo@1.0.0", ...], ...]
  * @param createdAt                  timestamp of creation
  * @param updatedAt                  timestamp of most recent update
  */
@@ -48,6 +52,7 @@ public record ScaDependencyDto(
   String scope,
   @Nullable String userDependencyFilePath,
   @Nullable String lockfileDependencyFilePath,
+  @Nullable List<List<String>> chains,
   long createdAt,
   long updatedAt) {
 
@@ -56,6 +61,10 @@ public record ScaDependencyDto(
   public static final int SCOPE_MAX_LENGTH = 100;
   public static final int DEPENDENCY_FILE_PATH_MAX_LENGTH = 1000;
 
+  private static final Gson GSON = new Gson();
+  private static final TypeToken<List<List<String>>> CHAINS_TYPE = new TypeToken<>() {
+  };
+
   public ScaDependencyDto {
     // We want these to raise errors and not silently put junk values in the db
     checkLength(scope, SCOPE_MAX_LENGTH, "scope");
@@ -66,6 +75,10 @@ public record ScaDependencyDto(
     }
   }
 
+  public String getChainsJson() {
+    return chains == null ? null : GSON.toJson(chains);
+  }
+
   /**
    * Returns the userDependencyFilePath if it is not null, otherwise returns the lockfileDependencyFilePath.
    *
@@ -88,6 +101,7 @@ public record ScaDependencyDto(
     private String scope;
     private String userDependencyFilePath;
     private String lockfileDependencyFilePath;
+    private List<List<String>> chains;
     private long createdAt;
     private long updatedAt;
 
@@ -121,6 +135,11 @@ public record ScaDependencyDto(
       return this;
     }
 
+    public Builder setChains(List<List<String>> chains) {
+      this.chains = chains;
+      return this;
+    }
+
     public Builder setCreatedAt(long createdAt) {
       this.createdAt = createdAt;
       return this;
@@ -133,8 +152,7 @@ public record ScaDependencyDto(
 
     public ScaDependencyDto build() {
       return new ScaDependencyDto(
-        uuid, scaReleaseUuid, direct, scope, userDependencyFilePath, lockfileDependencyFilePath, createdAt, updatedAt
-      );
+        uuid, scaReleaseUuid, direct, scope, userDependencyFilePath, lockfileDependencyFilePath, chains, createdAt, updatedAt);
     }
   }
 
@@ -146,6 +164,7 @@ public record ScaDependencyDto(
       .setScope(this.scope)
       .setUserDependencyFilePath(this.userDependencyFilePath)
       .setLockfileDependencyFilePath(this.lockfileDependencyFilePath)
+      .setChains(this.chains)
       .setCreatedAt(this.createdAt)
       .setUpdatedAt(this.updatedAt);
   }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyReleaseDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyReleaseDto.java
index 58fcb0a1255..95682f5f688 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyReleaseDto.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/sca/ScaDependencyReleaseDto.java
@@ -19,6 +19,7 @@
  */
 package org.sonar.db.sca;
 
+import java.util.List;
 import javax.annotation.Nullable;
 
 /**
@@ -32,6 +33,7 @@ import javax.annotation.Nullable;
  * @param scope scope/type of the dep like "compile"
  * @param userDependencyFilePath which manifest file (e.g. package.json)
  * @param lockfileDependencyFilePath which lockfile (e.g. package-lock.json)
+ * @param chains chains that brought the dependency in, e.g. [["pkg:npm/foo@1.0.0", ...], ...]
  * @param packageUrl PURL specification URL
  * @param packageManager package manager
  * @param packageName name of package
@@ -40,19 +42,19 @@ import javax.annotation.Nullable;
  * @param known was the package known to Sonar
  */
 public record ScaDependencyReleaseDto(String dependencyUuid,
-                                      String releaseUuid,
-                                      String componentUuid,
-                                      boolean direct,
-                                      String scope,
-                                      @Nullable String userDependencyFilePath,
-                                      @Nullable String lockfileDependencyFilePath,
-                                      String packageUrl,
-                                      PackageManager packageManager,
-                                      String packageName,
-                                      String version,
-                                      String licenseExpression,
-                                      boolean known
-                                      ) {
+  String releaseUuid,
+  String componentUuid,
+  boolean direct,
+  String scope,
+  @Nullable String userDependencyFilePath,
+  @Nullable String lockfileDependencyFilePath,
+  @Nullable List<List<String>> chains,
+  String packageUrl,
+  PackageManager packageManager,
+  String packageName,
+  String version,
+  String licenseExpression,
+  boolean known) {
 
   public ScaDependencyReleaseDto(ScaDependencyDto dependency, ScaReleaseDto release) {
     this(
@@ -63,13 +65,13 @@ public record ScaDependencyReleaseDto(String dependencyUuid,
       dependency.scope(),
       dependency.userDependencyFilePath(),
       dependency.lockfileDependencyFilePath(),
+      dependency.chains(),
       release.packageUrl(),
       release.packageManager(),
       release.packageName(),
       release.version(),
       release.licenseExpression(),
-      release.known()
-    );
+      release.known());
     if (!dependency.scaReleaseUuid().equals(release.uuid())) {
       throw new IllegalArgumentException("Dependency and release UUIDs should match");
     }
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml
index 631c269f86e..9dc0f6a15a2 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/sca/ScaDependenciesMapper.xml
@@ -8,6 +8,7 @@
     sd.scope as scope,
     sd.user_dependency_file_path as userDependencyFilePath,
     sd.lockfile_dependency_file_path as lockfileDependencyFilePath,
+    sd.chains as chains,
     sd.created_at as createdAt,
     sd.updated_at as updatedAt
   </sql>
@@ -20,6 +21,7 @@
       scope,
       user_dependency_file_path,
       lockfile_dependency_file_path,
+      chains,
       created_at,
       updated_at
     ) values (
@@ -29,6 +31,7 @@
       #{scope,jdbcType=VARCHAR},
       #{userDependencyFilePath,jdbcType=VARCHAR},
       #{lockfileDependencyFilePath,jdbcType=VARCHAR},
+      #{chains,jdbcType=CLOB,typeHandler=ListOfListOfStringsTypeHandler},
       #{createdAt,jdbcType=BIGINT},
       #{updatedAt,jdbcType=BIGINT}
     )
@@ -107,6 +110,7 @@
       scope = #{scope, jdbcType=VARCHAR},
       user_dependency_file_path = #{userDependencyFilePath, jdbcType=VARCHAR},
       lockfile_dependency_file_path = #{lockfileDependencyFilePath, jdbcType=VARCHAR},
+      chains = #{chains, jdbcType=CLOB, typeHandler=ListOfListOfStringsTypeHandler},
       updated_at = #{updatedAt, jdbcType=BIGINT}
     where
       uuid = #{uuid, jdbcType=VARCHAR}