hyades icon indicating copy to clipboard operation
hyades copied to clipboard
verified publisher icon DependencyTrack

Enable DataNucleus to deal with `JSONB` columns

Open nscuro opened this issue 1 year ago • 3 comments

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 avatar Feb 13 '24 14:02 nscuro

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 avatar Feb 13 '24 18:02 nscuro

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 avatar Jun 24 '24 14:06 nscuro

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 avatar Jun 29 '24 14:06 nscuro

No implementation necessary for this one, see comment above.

nscuro avatar Sep 18 '24 13:09 nscuro