>> > 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?
Yes. This section of the manual will give you a first impression about what you're going to be doing: http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/ The jOOQ code base is full of examples about how to properly implement query parts. >> > 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. Very interesting. I thought that Oracle was the only database that really implemented partitioning "for free". Good to know > 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. OK >> > 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. ;-) I have done that with Oracle, although we used some lower-level features (lower than SQL) to get the data loaded... It's interesting nonetheless. :-) > I can see two obvious choices - > > Solution #1 > [...] > Solution #2 > [...] > Hoping you can prevent me from traveling down the wrong path(s). Yes, there are a lot of possible "wrong" paths here. First off, I think it is important to understand that partitioning should be seen as a storage-level feature, if implemented correctly. A good example for this are Oracle's "PARTITION BY" DDL clause, which does not change the logical structure of the partitioned table. In other words, outside of the storage engine, there is only [A]. [A]_1 and [A]_2 are not visible, except maybe to the CBO and some query hints. >From what you're telling me, Postgres isn't that far advanced yet, and some of the partitioning into [A]_1 and [A]_2 has to be done above the storage-level, i.e. at the SQL-level (using triggers) or at the DAO level (using Java code, phrasing the correct SQL). Nonetheless, I think you should avoid disclosing the existence of [A]_1 and [A]_2 anywhere above some "last minute" SQL patching performed by some hook that you provide jOOQ with. Your application should only ever perform actions on [A], as [A] is the only relevant entity from a logical perspective. Storage facts should be hidden entirely. In other words, this is more or less your Solution #2: > 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. Yes. Based on your partitioning criteria (e.g. some date), you can divide the set of [A] into various buckets, generate SQL for that bucket and batch insert each bucket. > - How to convert [A] into [A]_* ? I've no idea, I hadn't seen anything > obvious in the interfaces. Maybe, runtime table mapping could help, here? http://www.jooq.org/doc/2.6/manual/sql-building/factory/runtime-schema-mapping/#N1061E This feature was originally designed to allow for adding table prefixes in environments where the database / schema is shared among several applications - e.g. on a shared hosting provider. It could work just the same for you. If you want, we could also discuss Solution #1, but I feel that it will be much harder to do correctly. Specifically because the _1 and _2 suffixes used for partitioning should probably be dynamic...? Cheers Lukas -- 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.
