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();
> >>>
> >>> }
> >>>
> >>>
> >>> }
> >>
>
>
