Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Enable DataNucleus to deal with JSONB columns #1074

Open
nscuro opened this issue Feb 13, 2024 · 1 comment
Open

Enable DataNucleus to deal with JSONB columns #1074

nscuro opened this issue Feb 13, 2024 · 1 comment
Labels
component/api-server enhancement New feature or request p3 Nice-to-have features size/M Medium effort

Comments

@nscuro
Copy link
Member

nscuro commented Feb 13, 2024 •
edited

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 nscuro added enhancement New feature or request p3 Nice-to-have features size/M Medium effort component/api-server labels Feb 13, 2024
@nscuro nscuro mentioned this issue Feb 13, 2024
Merged
5 tasks
@nscuro
Copy link
Member Author

nscuro commented Feb 13, 2024

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: DependencyTrack/hyades-apiserver#573

@nscuro nscuro self-assigned this Feb 13, 2024
@nscuro nscuro mentioned this issue Feb 13, 2024
Merged
2 tasks
@nscuro nscuro removed their assignment Feb 28, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
component/api-server enhancement New feature or request p3 Nice-to-have features size/M Medium effort
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@nscuro