search

DependencyTrack / hyades

Incubating project for decoupling responsibilities from Dependency-Track's monolithic API server into separate, scalable services.
https://dependencytrack.github.io/hyades/latest
Apache License 2.0
59 stars 18 forks source link

Enable DataNucleus to deal with `JSONB` columns #1074

Closed nscuro closed 2 weeks ago

nscuro commented 7 months ago

PostgreSQL's JSONB columns are more efficient to store, faster to query, and can be indexed.

DataNucleus does not support JSONB type per default.

We can map Java objects to JSON strings using custom AttributeConverters, but when inserting the resulting strings, Postgres yields a type error, e.g.:

Insert of object "org.dependencytrack.model.Project@2933fa0d" using statement "INSERT INTO "PROJECT" ("ACTIVE","AUTHOR","CLASSIFIER","CPE","DESCRIPTION","DIRECT_DEPENDENCIES","EXTERNAL_REFERENCES","GROUP","LAST_BOM_IMPORTED","LAST_BOM_IMPORTED_FORMAT","LAST_RISKSCORE","MANUFACTURER","NAME","PARENT_PROJECT_ID","PUBLISHER","PURL","SUPPLIER","SWIDTAGID","UUID","VERSION") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" failed : ERROR: column "MANUFACTURER" is of type jsonb but expression is of type character varying

With raw SQL, this could be addressed using explicit casting, like this:

INSERT INTO "FOO" ("COLUMN_A") VALUES (?::JSONB)

But with DataNucleus we don't get to make this customization.

Transmitting JSONB through raw JDBC can be achieved using PGobject:

var pgObject = new PGobject();
pgObject.setType("JSONB");
pgObject.setValue("{\"foo\":\"bar\"");

preparedStatement.setObject(1, pgObject);

To get this level of control with DataNucleus, it seems like we have to build a DataNucleus extension, which provides a ColumnMapping, as per https://www.datanucleus.org/products/accessplatform/extensions/extensions.html#rdbms_datastore_mapping

I made some progress using the extension way, but got stuck when trying to also make it work with H2, which is still being used in some tests.

// src/main/java/org/dependencytrack/persistence/converter/JsonColumnMapping.java
package org.dependencytrack.persistence.converter;

import org.datanucleus.exceptions.NucleusDataStoreException;
import org.datanucleus.store.rdbms.RDBMSStoreManager;
import org.datanucleus.store.rdbms.mapping.column.AbstractColumnMapping;
import org.datanucleus.store.rdbms.mapping.java.JavaTypeMapping;
import org.datanucleus.store.rdbms.table.Column;
import org.postgresql.util.PGobject;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

public class JsonColumnMapping extends AbstractColumnMapping {

    public JsonColumnMapping(final JavaTypeMapping mapping, final RDBMSStoreManager storeMgr, final Column column) {
        super(storeMgr, mapping);
        this.column = column;
    }

    @Override
    public int getJDBCType() {
        return Types.CLOB;
    }

    @Override
    public void setObject(final PreparedStatement ps, final int exprIndex, final Object value) {
        try {
            if (value == null) {
                final var pgObject = new PGobject();
                pgObject.setType("JSONB");
                pgObject.setValue(null);
                ps.setObject(exprIndex, pgObject);
                return;
            }

            if (!(value instanceof final String stringValue)) {
                throw new NucleusDataStoreException("Invalid type: %s".formatted(value.getClass()));
            }

            final var pgObject = new PGobject();
            pgObject.setType("JSONB");
            pgObject.setValue(stringValue);
            ps.setObject(exprIndex, pgObject);
        } catch (SQLException e) {
            throw new NucleusDataStoreException("foo", e);
        }

        throw new IllegalStateException("Unsupported datastore adapter %s".formatted(getDatastoreAdapter().getClass()));
    }

    @Override
    public Object getObject(final ResultSet rs, final int exprIndex) {
        try {
            final Object object = rs.getObject(exprIndex);
            if (object == null) {
                return null;
            }

            if (!(object instanceof final PGobject pgObject)) {
                throw new NucleusDataStoreException("baz");
            }

            if (pgObject.isNull()) {
                return null;
            }

            return pgObject.getValue();
        } catch (SQLException e) {
            throw new NucleusDataStoreException("bar", e);
        }
    }

    @Override
    public void setString(final PreparedStatement ps, final int exprIndex, final String value) {
        setObject(ps, exprIndex, value);
    }

    @Override
    public String getString(final ResultSet rs, final int exprIndex) {
        return (String) getObject(rs, exprIndex);
    }

}
<!-- src/main/resources/plugin.xml -->
<?xml version="1.0" encoding="UTF-8"?>
<plugin id="org.dependencytrack" provider-name="DependencyTrack">
    <extension point="org.datanucleus.store.rdbms.column_mapping">
        <mapping column-mapping-class="org.dependencytrack.persistence.converter.JsonColumnMapping"
                 java-type="java.lang.String" jdbc-type="CLOB" sql-type="JSONB" default="false"/>
    </extension>
</plugin>
# src/main/resources/META-INF/MANIFEST.MF
Manifest-Version: 1.0
Bundle-ManifestVersion: 2
Bundle-Name: DependencyTrack
Bundle-SymbolicName: org.dependencytrack;singleton:=true
Bundle-Vendor: DependencyTrack
Bundle-Version: 1.0.0

Fields annotated with @Column must then include the sqlType = "JSONB" qualifier for the ColumnMapper to be picked up:

@Persistent(defaultFetchGroup = "true")
@Convert(OrganizationalEntityJsonConverter.class)
@Column(name = "SUPPLIER", jdbcType = "CLOB", sqlType = "JSONB", allowsNull = "true")
private OrganizationalEntity supplier;

This works fine when using Postgres, but fails in tests where DataNucleus auto-generates the schema for an in-memory H2 database:

javax.jdo.JDOFatalUserException: A property named javax.jdo.PersistenceManagerFactoryClass must be specified, or a jar file with a META-INF/services/javax.jdo.PersistenceManagerFactory entry must be in the classpath, or a property named javax.jdo.option.PersistenceUnitName must be specified.

    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:870)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:1109)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:969)
    at alpine.server.persistence.PersistenceManagerFactory.createPersistenceManager(PersistenceManagerFactory.java:140)
    at alpine.server.persistence.PersistenceManagerFactory.getPersistenceManager(PersistenceManagerFactory.java:149)
    at alpine.persistence.AbstractAlpineQueryManager.<init>(AbstractAlpineQueryManager.java:85)
    at alpine.persistence.AlpineQueryManager.<init>(AlpineQueryManager.java:64)
    at org.dependencytrack.persistence.QueryManager.<init>(QueryManager.java:159)
    at org.dependencytrack.PersistenceCapableTest.before(PersistenceCapableTest.java:42)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    at org.junit.internal.runners.statements.RunBefores.invokeMethod(RunBefores.java:33)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
NestedThrowablesStackTrace:
javax.jdo.JDOFatalInternalException: java.lang.NullPointerException: Cannot invoke "org.datanucleus.store.rdbms.schema.SQLTypeInfo.getTypeName()" because "this.typeInfo" is null
    at org.datanucleus.api.jdo.JDOAdapter.getJDOExceptionForNucleusException(JDOAdapter.java:733)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:878)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.createPersistenceManagerFactory(JDOPersistenceManagerFactory.java:352)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.getPersistenceManagerFactory(JDOPersistenceManagerFactory.java:275)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at javax.jdo.JDOHelper$16.run(JDOHelper.java:1975)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:569)
    at javax.jdo.JDOHelper.invoke(JDOHelper.java:1970)
    at javax.jdo.JDOHelper.invokeGetPersistenceManagerFactoryOnImplementation(JDOHelper.java:1139)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:851)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:1109)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:969)
    at alpine.server.persistence.PersistenceManagerFactory.createPersistenceManager(PersistenceManagerFactory.java:140)
    at alpine.server.persistence.PersistenceManagerFactory.getPersistenceManager(PersistenceManagerFactory.java:149)
    at alpine.persistence.AbstractAlpineQueryManager.<init>(AbstractAlpineQueryManager.java:85)
    at alpine.persistence.AlpineQueryManager.<init>(AlpineQueryManager.java:64)
    at org.dependencytrack.persistence.QueryManager.<init>(QueryManager.java:159)
    at org.dependencytrack.PersistenceCapableTest.before(PersistenceCapableTest.java:42)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
    at org.junit.internal.runners.statements.RunBefores.invokeMethod(RunBefores.java:33)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:24)
    at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
    at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
    at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
    at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
NestedThrowablesStackTrace:
java.lang.NullPointerException: Cannot invoke "org.datanucleus.store.rdbms.schema.SQLTypeInfo.getTypeName()" because "this.typeInfo" is null
    at org.datanucleus.store.rdbms.table.ColumnImpl.getSQLDefinition(ColumnImpl.java:401)
    at org.datanucleus.store.rdbms.adapter.BaseDatastoreAdapter.getCreateTableStatement(BaseDatastoreAdapter.java:1292)
    at org.datanucleus.store.rdbms.table.TableImpl.getSQLCreateStatements(TableImpl.java:1186)
    at org.datanucleus.store.rdbms.table.ClassTable.getSQLCreateStatements(ClassTable.java:2921)
    at org.datanucleus.store.rdbms.table.AbstractTable.create(AbstractTable.java:496)
    at org.datanucleus.store.rdbms.table.AbstractTable.exists(AbstractTable.java:544)
    at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:3497)
    at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:3024)
    at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:118)
    at org.datanucleus.store.rdbms.RDBMSStoreManager.createSchemaForClasses(RDBMSStoreManager.java:3866)
    at org.datanucleus.store.schema.SchemaTool.createSchemaForClasses(SchemaTool.java:507)
    at org.datanucleus.PersistenceNucleusContextImpl.initialiseSchema(PersistenceNucleusContextImpl.java:824)
    at org.datanucleus.PersistenceNucleusContextImpl.initialise(PersistenceNucleusContextImpl.java:484)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:865)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.createPersistenceManagerFactory(JDOPersistenceManagerFactory.java:352)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.getPersistenceManagerFactory(JDOPersistenceManagerFactory.java:275)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at javax.jdo.JDOHelper$16.run(JDOHelper.java:1975)
    at java.base/java.security.AccessController.doPrivileged(AccessController.java:569)
    at javax.jdo.JDOHelper.invoke(JDOHelper.java:1970)
    at javax.jdo.JDOHelper.invokeGetPersistenceManagerFactoryOnImplementation(JDOHelper.java:1139)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:851)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:1109)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:969)
    at alpine.server.persistence.PersistenceManagerFactory.createPersistenceManager(PersistenceManagerFactory.java:140)
    at alpine.server.persistence.PersistenceManagerFactory.getPersistenceManager(PersistenceManagerFactory.java:149)
    at alpine.persistence.AbstractAlpineQueryManager.<init>(AbstractAlpineQueryManager.java:85)
    at alpine.persistence.AlpineQueryManager.<init>(AlpineQueryManager.java:64)
    at org.dependencytrack.persistence.QueryManager.<init>(QueryManager.java:159)
    at org.dependencytrack.PersistenceCapableTest.before(PersistenceCapableTest.java:42)

The root cause being that H2 doesn't have a JSONB type.

nscuro commented 7 months ago

The easier alternative is to move all API server tests to use PostgreSQL testcontainers instead of H2. Has the nice side effect that we can ban H2 from the project alltogether.

PR to make this happen: https://github.com/DependencyTrack/hyades-apiserver/pull/573

nscuro commented 3 months ago

Raised https://github.com/datanucleus/datanucleus-rdbms/issues/494.

Not really sure what areas of DataNucleus will need to be touched, so hoping the maintainer can nudge me in the right direction.

nscuro commented 3 months ago

Turns out this can be solved with extensions:

@Persistent
@Column(name = "DATA", jdbcType = "CLOB")
@Extensions({
        @Extension(vendorName = "datanucleus", key = "insert-function", value = "(?::JSONB)"),
        @Extension(vendorName = "datanucleus", key = "update-function", value = "(?::JSONB)")
})
private String data;
nscuro commented 2 weeks ago

No implementation necessary for this one, see comment above.