Clear now.
> On Jun 10, 2020, at 5:53 PM, John Huss <johnth...@gmail.com> wrote:
>
> On Wed, Jun 10, 2020 at 1:25 AM Andrus Adamchik <and...@objectstyle.org>
> 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 <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();
>>>>>
>>>>> }
>>>>>
>>>>>
>>>>> }
>>>>
>>
>>
