Re: Types.OTHER

Clear now.

> On Jun 10, 2020, at 5:53 PM, John Huss <[email protected]> wrote:
> 
> On Wed, Jun 10, 2020 at 1:25 AM Andrus Adamchik <[email protected]>
> wrote:
> 
>>> 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.
>> 
> 
> By not parsing a JSON value from a String to an object, you avoid having to
> re-serialize it back to a String to return it from a web service. This is
> good since that provides a performance bump. But you also miss out on the
> "opportunity" for whatever mapper you are using to serialize data for your
> web service to examine the deeper structure of this Object and perhaps make
> different choices about how to serialize it.
> 
> The broader point is that the way your DB chooses to store a JSON value
> might differ from the way you want to present it in your web service. If
> that is the case, then preserving the same String form of the value from
> the DB through the output of the web service doesn't help you - you'll need
> to parse it and then re-serialize it with your special rules.
> 
> I think either approach is valid here. Based on my current use cases I
> don't mind the performance hit taken from deserializing the value from the
> DB into an object every time, and I appreciate the simplicity of just
> getting the object I want (rather than a JSON string) without any other
> ceremony. But I definitely understand the desire to avoid that
> deserialization in cases where it appears to be unnecessary.
> 
> 
>> Andrus
>> 
>> 
>> 
>>> On Jun 9, 2020, at 5:54 PM, John Huss <[email protected]> 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 <[email protected]>
>>> 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 <[email protected]> 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();
>>>>> 
>>>>> }
>>>>> 
>>>>> 
>>>>> }
>>>> 
>> 
>>