On Mon, Feb 4, 2013 at 5:54 AM, Lukas Eder <[email protected]> wrote:
> Hello Pete, > > >> > - Support new dynamic query options > >> > >> What are these, according to you? > > > > Nothing crazy, just the ability to append conditions, the > SelectSelectStep > > object looks like it works for all the needs I have. > > I see. Yes, you'll find many examples about this on Stack Overflow, on > this user group, or in the jOOQ manual > > >> > - Support for PGobject (and thus PGhstore, PGgeometry, and > PGgeometryLW) > >> > >> They *might* work, but jOOQ doesn't guarantee anything > > > > Well, selects and inserts (which is the bulk of what I do) worked for all > > these object types without any weird binding issues. > > OK. Still, fixing those cases that don't work is best done by formally > supporting PostGIS. Otherwise, new surprises might arise later on. > > >> Unfortunately, as I said, with jOOQ 2.6 you will probably not be able > >> to properly use PostGIS. It will probably work as long as you avoid > >> variable binding of PG* objects, though. > > > > Well I'm used to having to implement all of this in raw JDBC, are there > any > > facilities in jOOQ to extend, or what not? I found these: > > > > > http://www.jooq.org/doc/2.6/manual/sql-execution/fetching/data-type-conversion/ > > http://www.jooq.org/doc/2.6/manual/code-generation/custom-data-types/ > > Yes, this is your best choice right now, although I haven't made any > concrete experiences with PostGIS and jOOQ's converters yet. You might > be breaking new grounds, at least from what I know from this user > group. Any feedback you may have about your experience is very welcome > on this group! Rodger that, I'll make sure to write these all before making final designs, best to avoid surprises. :-) > > So I see how the converters can work for tables easily enough - is there > > something similar for routines? > > Converters for routines aren't formally supported (yet). As a reminder > to implement this, I have created #2155 > https://github.com/jOOQ/jOOQ/issues/2155 > > You *could* play around with your own routine implementations, by > extending org.jooq.impl.CustomField: > http://www.jooq.org/javadoc/latest/org/jooq/impl/CustomField.html > > Essentially, you'll have to implement jOOQ's internal toSQL() and > bind() methods. The bind() method receives an org.jooq.BindContext, > which exposes the underlying PreparedStatement, so you're free to > properly bind your PG objects. > OK, I'm guessing there are examples of this in the source code I could base an implementation off? > > >> I haven't followed up on that discussion yet. In order to see how > >> suggestions could be made, could you explain a bit more about your > >> partitioning use case? > > > > This sums it up pretty well - > > > > http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html > > I see. That is an interesting approach. I've read about Instagram > having made good experience with such kinds of partitioning for > sharding. I've referenced that article here: > > http://blog.jooq.org/2011/12/10/a-success-story-of-sql-scaling-horizontally/ > > Ah, interesting, thanks for the link. > The difference between the above example and what I'm trying to implement > is > > the insert logic will be done in the Java code and not by a trigger. I > was > > looking for suggestions on how best to use jOOQ for this > > Hmm, these are some interesting points that might even lead to a new > feature within jOOQ. Essentially, you want to decide within the Java > application (within a DAO layer), whether your inserted record should > be inserted into a table [A]_1 or [A]_2, while the application layer > only knows about [A]. When selecting (depending on a relevant > predicate), you'd issue a UNION over [A]_1 and [A]_2, again with the > application layer only knowing about [A]. Is that it? > > Yes thats exactly it. In the case of Postgres 9.2+ you get the select for free - Postgres's query planner now uses table CHECK() conditions when planning and so a SELECT on [A] would work as expected. The insert does not, at this time, come for free. Testing shows that using a trigger in the DB to do the inserts is very slow, my own testing shows that doing the same logic on the Java side is considerably faster and can be batched. > > - I was considering > > a DAO implementation, but I'm not sure how the DAO interfaces tie to jOOQ > > (are they just a convence layer?) > > jOOQ's DAOs today are a mere convenience and a response to a > competitor product's feature set (OneWebSQL). They aren't very > sophisticated, but they take care of much of the basic CRUD > operations. Feel free to provide feature requests, should you see > anything missing. > > > and the interface is so very tightly bound > > to the record and table interfaces of jOOQ it looks like I would have to > > drop all the type safety. > > I'm not sure if I'm correctly understanding your ideas here. Why would > jOOQ's DAOs make you drop type safety? > > Not to say that any of this isn't correct! I know I'm trying to do something different then most people here - but when you have ~1 billion rows per year being inserted into the DB, you get interesting problems to solve. ;-) Anyway, if we look at the interfaces - interface DAO<R extends TableRecord<R>, P, T> interface TableRecord<R extends TableRecord<R>> extends Record interface Record extends FieldProvider, Store<Object> interface FieldProvider I can see two obvious choices - Solution #1 Construct [A]_1 or [A]_2 at data ingest time, storing them in buckets which then get dispatched to the correct DAO Problems: - The data ingest is all really the same, but the POJO interfaces don't inherit from each other, and the objects can't be assigned to anything but Object, which makes working with the data classes difficult (as far as I can see) - I didn't see any sort of putValue(Field,Value) function for TableRecord - I suppose I could modify the [A] POJO or TableRecord that is generated, making it implement all of the interfaces of [A]_* - Don't know how the generated TableRecord and POJO are converted, or how they interact w/ the generated interface Solution #2 Build all [A] and then pass them to a custom DAO which then converts the [A] TableRecord or POJO into [A]_1 or [A]_2 and pass them to their respective DAO. - How to convert [A] into [A]_* ? I've no idea, I hadn't seen anything obvious in the interfaces. Hoping you can prevent me from traveling down the wrong path(s). Thanks again, Pete -- 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/groups/opt_out.
