On Saturday, October 20, 2018 at 8:36:27 AM UTC-6, Lukas Eder wrote: > > On Fri, Oct 19, 2018 at 7:12 PM Marshall Pierce <[email protected] > <javascript:>> wrote: > > >> - In the forcedType `types` element, what should I be using for a >> Postgres TIMESTAMP WITH TIME ZONE? After fiddling around with a few >> guesses, I found that using "timestamp.*with.*time.*zone", the forcedType >> matches and I get the desired Instant fields, but "timestampwithtimezone" >> and "timestamp with time zone" don't match and of course the fields end up >> as OffsetDateTime again. Is there some mystery separator that's not a space >> between those words? >> > > Yeah, the default regular expression flags (<regexFlags>) is having > Pattern.COMMENTS turned on. This allows for adding comments to more complex > regexes, as well as formatting them as this flag ignores whitespace in the > regex. You will have to escape it: timestamp\ with\ time\ zone. See, e.g.: > > https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-regex-flags >
Excellent, that worked, thanks. > - I was using DSL.currentOffsetDateTime() in a certain UPDATE statement. >> Now that the fields are `Instant`s, that doesn't typecheck, so I'm using >> `DSL.currentTimestamp().cast(SQLDataType.INSTANT)` (mimicking >> currentOffsetDateTime()). This necessitated the creation of >> SQLDataType.INSTANT as follows: >> public static final DataType<Instant> INSTANT = new >> DefaultDataType<Instant>(null, Instant.class, "timestamp with time zone"); >> Is this the right way to handle `... set foo = now()` with Instant >> columns? >> > > Well, you're using internal API (DefaultDataType). It may work in > unexpected way. Much better to create a new data type from > SQLDataType.TIMESTAMPWITHTIMEZONE by calling asConvertedDataType(new > YourBinding()). > > That data type is then reusable. Also, the cast is probably not what you > want. It may result in an actual SQL CAST(...) expression. Field.coerce() > is one way to switch data types on an expression. Or, you can just use > plain SQL templating to re-create your now() function as an Instant data > type: > https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating > <https://www.google.com/url?q=https%3A%2F%2Fwww.jooq.org%2Fdoc%2Flatest%2Fmanual%2Fsql-building%2Fplain-sql-templating&sa=D&sntz=1&usg=AFQjCNFrhea7zlxsgZLgTTiA_MggvdZ3UA> > Aha. Field.coerce() with asConvertedDataType() works fine. No shortage of API to learn in jOOQ... I then map each Record into a simple tuple class: >> WidgetTuple(record.into(WIGETS), record.into(FLAVORS), record.into(COLORS), >> ...). Some of the Tables I'm passing to `.into()` are table aliases >> (because the same table is joined into a few different ways). If that's not >> enough detail we can look at getting you access to the code or I can make a >> synthetic repro. >> >> Once the tuples are created, it's then easy to traverse across the >> List<WidgetTuple>, grouping, filtering, etc as needed, and according to the >> profiler, that logic takes very very little time. >> > > I'll look into this, thanks. Specifically, this indexOf() call, which > you've noticed as well, seems like it should be cached: > > https://github.com/jOOQ/jOOQ/blob/version-3.11.5/jOOQ/src/main/java/org/jooq/impl/AbstractRecord.java#L750 > > The question is: Where should that cache be as you're looping over your > records in your own code. I could see some per-loop caching in > ResultImpl.into(Table) > Perhaps each Record might keep a reference to its parent Result and access some shared cache of field data? If that's not convenient, though, I'd be totally fine if there was some other way to make caching explicit (though it would be a shame to lose caching for all users transparently), like having Result provide a CachingRecordWidget that had some recordInto(Record, Table) method that would then make it trivial to cache the necessary metadata. > Well, there would need to be one query per branch, e.g. > > - 1 query fetching all the books > - 1 query fetching all the authors for these books (preferably using an IN > (SELECT ..) predicate, not an IN (?, ?, ? ..) predicate) > - 1 query fetching all the categories for these books (preferably... see > above) > > The first two could be combined with a join, but then you'd again > duplicated books. There's no perfect solution here. ORMs like JPA do > similar things to what I'm suggesting behind the scenes and let you > override their defaults (e.g. JPQL with its JOIN FETCH feature) > To be clear, if we had a BookTuple(book: BooksRecord, author: AuthorsRecord, category: CategoriesRecord) type, and we populated that with the obvious joins between books, authors, and categories, this is the type of "cleaning up the mess" logic I was referring to the following (in mostly Kotlin-esque syntax): tuples .groupBy { it.book.id } .values // now it's a sequence of List<BookTuple>, where each list corresponds to one book .map { bookTuples -> val bookRecord = bookTuples.first().book val authors = bookTuples.filter { it.author.id != null }.groupBy { it.author.id }.values.map { it.first().authorNameEtc } val categories = bookTuples.filter { it.category.id != null}.groupBy { it.category.id}.values.map {it.first().categoryDataHere} // construct your Book type appropriately from the above locals } AFAICT this takes care of all the deduplication. > I don't see any such way using standard JDBC based transactions, but I > think the sequential queries will be much faster than the single query with > duplication. If you're using a database that can run queries asynchronously > (e.g. PostgreSQL), you could try your luck with that. It should be possible > to share a transaction among client threads and run several statements in > parallel (they might still be sequenced by the database) - I have no > experience with that yet. > OK, I will look into it. Also, for your other question about if we really do need all the data... yes, it's for a good cause: exporting everything to CSV for people to slice'n'dice in a spreadsheet, populating the "show me everything about this entity" view, etc. -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
