> Would this also address the use case where the DB doesn't have a special
> type (like JSONB) and just stores it as a plain varchar or byte[], but you
> still want the Java side to treat it as a complex object?
Yep. I did this in my implementation for Wkt type, which can be mapped as
either (VAR)CHAR or GEOMETRY. Of course in the former case DB-side geometry
operations will not be available, but I suppose that's expected. (I know Nikita
had some thoughts on how to expose custom operators for these new types, so
that we could use them in queries).
> 2) Regarding skipping parsing when just going straight back out again to
> serialization - I have felt the reluctance (like you) of parsing this in
> those cases. But one thing to think about is that the parser/serializer
> (let's say Jackson in this case) may have special rules for how types are
> outputted like pretty/minified, sorted/unsorted, string/number (for
> BigDecimal). So skipping that parse/serialization may not actually be
> desirable, at least not in every case.
Could you clarify this point please? I understand the part about special
settings in the ObjectMapper of Jackson, but to me that's an argument in favor
of keeping this logic outside Cayenne, and letting the user control it
themselves.
Andrus
> On Jun 9, 2020, at 5:54 PM, John Huss <johnth...@gmail.com> wrote:
>
> Thanks Andrus, that's great. It would be very useful to have more built-in
> or robust support in Cayenne for these types.
>
> A few things I thought of:
> 1) Would this also address the use case where the DB doesn't have a special
> type (like JSONB) and just stores it as a plain varchar or byte[], but you
> still want the Java side to treat it as a complex object? I would think
> that should be fairly easy to do given what you said. For example, storing
> JSON in a MySQL varchar column and mapping it in cayenne to some kind of
> java.util.Map
>
> 2) Regarding skipping parsing when just going straight back out again to
> serialization - I have felt the reluctance (like you) of parsing this in
> those cases. But one thing to think about is that the parser/serializer
> (let's say Jackson in this case) may have special rules for how types are
> outputted like pretty/minified, sorted/unsorted, string/number (for
> BigDecimal). So skipping that parse/serialization may not actually be
> desirable, at least not in every case.
>
>
>
> On Tue, Jun 9, 2020 at 1:59 AM Andrus Adamchik <and...@objectstyle.org>
> wrote:
>
>> Hi John, Malcom --
>>
>> (Don't know if Malcom is on dev@, so posting it here; we can move to dev@)
>>
>> Looks like the need for JSON type and other non-standard (from JDBC
>> standpoint) types is there. Many people need it. I am hoping the effort
>> that started independently by at least 3 community members can result in
>> something that we can bring to the Cayenne core. Let's discuss how this
>> might work.
>>
>> So... We'd need to create a taxonomy of "value object" types that map to
>> "java.sql.Types.OTHER" (e.g. Jsonb, Wkt, TimestampRange, etc.). Then we'd
>> create ExtendedTypes for each one of them, and register them inside the
>> corresponding DbAdapters. More than one adapter can support a given type.
>> Also we need to add adapter-specific logic to db-import Types.OTHER as the
>> right value object type using DB-specific metadata of a given column. Same
>> for SQL schema generation - for Types.OTHER we can derive the true type
>> based on the value object it is mapped to.
>>
>> An important question is the internal structure of the value object types.
>> Most of these types require parsing of a String or a byte[] into some
>> special Java object. And there are two problems with it:
>>
>> 1. Performance. You may not always need that special object in many cases.
>> E.g. your JSON is written directly as String to a REST response. No need to
>> parse it by default.
>> 2. External dependencies - Jsonb would require Jackson, Wkt - JTS libs to
>> parse it to geometry, etc.
>>
>> I solved both problems by structuring the types as unparsed wrappers of
>> String or byte[]. If there is a third-party lib dependency, it up to the
>> calling code to decide when and how to parse the data and which library to
>> use. If there are no dependencies (e.g. Postgres "tsrange" can be parsed to
>> a pair of LocalDateTime objects using JDK classes) a parser can be included
>> in the type, but the parsing should happen lazily.
>>
>> Also I was able to avoid the dependency on driver-specific code (PgObject
>> in John's example) by providing a custom "sql tree processor" (a 4.2
>> feature), so that String or byte[] can be bound to PreparedStatement, and
>> the tree processor would alter the SQL to wrap the parameter in a Postgres
>> function doing the type conversion in DB.
>>
>> I am planning to open-source the code that implements parts of the
>> solution above. Hopefully soon.
>>
>> Thoughts?
>>
>>
>> Also a minor note on John's example:
>>
>>> public static <T> void registerWithRuntime(ServerRuntime runtime,
>> Class<T> javaClass){
>>>
>>> JsonbType<T> jsonbType = *new* JsonbType<T>(javaClass);
>>> Collection<DataNode> nodes =
>> runtime.getDataDomain().getDataNodes();
>>> for(DataNode node : nodes) {
>>>
>> node.getAdapter().getExtendedTypes().registerType(jsonbType);
>>> }
>>> }
>>
>> Unless you have a custom DbAdapter as discussed above, an idiomatic way to
>> register an ExtendedType would be to write a DI module and add it to
>> runtime on startup:
>>
>> Module m = b -> ServerModule.contributeUserTypes(b).add(jsonbType);
>>
>>
>> Andrus
>>
>>
>>
>>
>>> On May 26, 2020, at 5:37 PM, John Huss <johnth...@gmail.com> wrote:
>>>
>>> For JSONB support I use this class (basically). But in addition you will
>>> probably want an immutable (or optionally immutable) version of Map
>> because
>>> changes to the Map attribute itself won't be detected by Cayenne unless
>> you
>>> reset the attribute value on the entity, like
>>> myEntityObject.setMyAttribute(new JsonMap(map)). In the Cayenne data
>>> map/model you use the OTHER jdbc type.
>>>
>>> *import* java.sql.CallableStatement;
>>>
>>> *import* java.sql.PreparedStatement;
>>>
>>> *import* java.sql.ResultSet;
>>>
>>> *import* java.util.Collection;
>>>
>>>
>>> *import* org.apache.cayenne.access.DataNode;
>>>
>>> *import* org.apache.cayenne.access.types.ExtendedType;
>>>
>>> *import* org.apache.cayenne.configuration.server.ServerRuntime;
>>>
>>> *import* org.apache.cayenne.eof.JsonList;
>>>
>>> *import* org.codehaus.jackson.map.ObjectMapper;
>>>
>>> *import* org.postgresql.util.PGobject;
>>>
>>>
>>> *public* *class* JsonbType<T> *implements* ExtendedType<T> {
>>>
>>>
>>> *private* *static* *final* String *DB_TYPE* = "jsonb";
>>>
>>>
>>> /**
>>>
>>> * Call this at startup time to register with the Cayenne runtime.
>>>
>>> * *@param* javaClass with user-created properties (getters and setters)
>>>
>>> */
>>>
>>> *public* *static* <T> *void* registerWithRuntime(ServerRuntime runtime,
>>> Class<T> javaClass){
>>>
>>> JsonbType<T> jsonbType = *new* JsonbType<T>(javaClass);
>>>
>>>
>>> Collection<DataNode> nodes = runtime.getDataDomain().getDataNodes();
>>>
>>> *for* (DataNode node : nodes) {
>>>
>>> node.getAdapter().getExtendedTypes().registerType(jsonbType);
>>>
>>> }
>>>
>>> }
>>>
>>>
>>> *private* *final* Class<T> clazz;
>>>
>>>
>>> *public* JsonbType(Class<T> clazz) {
>>>
>>> *this*.clazz = clazz;
>>>
>>> *try* {
>>>
>>> clazz.getConstructor(*new* Class[0]);
>>>
>>> } *catch* (NoSuchMethodException | SecurityException e) {
>>>
>>> *throw* *new* RuntimeException("Json type " + clazz.getName() + " does
>> not
>>> have a no-args constructor");
>>>
>>> }
>>>
>>> }
>>>
>>>
>>> @Override
>>>
>>> *public* String getClassName() {
>>>
>>> *return* clazz.getName();
>>>
>>> }
>>>
>>>
>>> @Override
>>>
>>> *public* T materializeObject(ResultSet rs, *int* index, *int* type)
>> *throws*
>>> Exception {
>>>
>>> String json = rs.getString(index);
>>>
>>> *return* materialize(json);
>>>
>>> }
>>>
>>>
>>> @Override
>>>
>>> *public* T materializeObject(CallableStatement rs, *int* index, *int*
>> type)
>>> *throws* Exception {
>>>
>>> String json = rs.getString(index);
>>>
>>> *return* materialize(json);
>>>
>>> }
>>>
>>>
>>> *private* T materialize(String json) {
>>>
>>> *if* (json != *null*) {
>>>
>>> *try* {
>>>
>>> T value = *new* ObjectMapper().readValue(json, clazz);
>>>
>>> *if* (value *instanceof* JsonList) {
>>>
>>> ((JsonList<?>)value).freeze();
>>>
>>> } *else* *if* (value *instanceof* JsonMap) {
>>>
>>> ((JsonMap)value).freeze();
>>>
>>> }
>>>
>>> *return* value;
>>>
>>> } *catch* (Exception e) {
>>>
>>> *throw* *new* RuntimeException("Failed to deserialize value of type " +
>>> clazz.getSimpleName() + " from " + json + ". " + e.getMessage(), e);
>>>
>>> }
>>>
>>> } *else* {
>>>
>>> *return* *null*;
>>>
>>> }
>>>
>>> }
>>>
>>>
>>> @Override
>>>
>>> *public* *void* setJdbcObject(
>>>
>>> PreparedStatement statement,
>>>
>>> T value,
>>>
>>> *int* pos,
>>>
>>> *int* type,
>>>
>>> *int* scale) *throws* Exception {
>>>
>>>
>>> *if* (value == *null*) {
>>>
>>> statement.setNull(pos, type);
>>>
>>> } *else* {
>>>
>>> String json;
>>>
>>> *try* {
>>>
>>> json = *new* ObjectMapper().writeValueAsString(value);
>>>
>>> } *catch* (Exception e) {
>>>
>>> *throw* *new* RuntimeException("Failed to serialize value of type " +
>>> clazz.getSimpleName()
>>> + " from " + value + ". " + e.getMessage());
>>>
>>> }
>>>
>>>
>>> PGobject dataObject = *new* PGobject();
>>>
>>> dataObject.setType(*DB_TYPE*);
>>>
>>> dataObject.setValue(json);
>>>
>>> statement.setObject(pos, dataObject);
>>>
>>> }
>>>
>>> }
>>>
>>>
>>> @Override
>>>
>>> *public* String toString(T value) {
>>>
>>> *return* value.toString();
>>>
>>> }
>>>
>>>
>>> }
>>
