> Some interesting thoughts here. What would be good > examples of processing snippets that could be wired > through jOOQ into rendered SQL?
The general idea is to be able to move any processing from Java to SQL and back without having to rewrite all the Java as SQL or vice versa. The approach would be to define a "processing DSL" that produces objects that can either generate the needed SQL or do the equivalent processing in Java. I hope the following examples make sense; I'm not in the best mental condition for explaining complicated stuff at this time. 1) Table join conditions as seen in FKs. I'd want to see an object that can either generate the join condition or find the set of Pojos that conform to it, with the ability to go from either side. The Java side is conceptually difficult because the same database data could be represented in different ways (Pojo, key->row maps, XML representation if you go wild, etc.), so I guess the Condition object would associate with different Java-side strategy objects rather than provide the implementations directly. Also, there's the complication that a condition can be used left-to-right or right-to-left, while following links in Java is unidirectional; essentially, the Java side would need to work similar to the query planning the database does. 2) Multi-key joins where keys are from more than one table. E.g. ARTICLE, COMPANY, GROUP, where GROUP is a table of company-specific article groups. GROUP has ArticleGroupId and CompanyId as PK, obviously. (We're talking legacy schemas here.) This is similar to #1 but the Java side would need to allow going from any of one or two tables of the group to the others. I.e. this is a case of "convert a multi-directional condition into all unidirectional use cases that it describes" - somewhat similar to the difference between specification and execution in Prolog. 3) Composite indirect join conditions. E.g. let's say we have A->B->C and A->C; we want all C rows that are reachable from A either directly or through B. 4) Non-FK joins. Join conditions that use a prefix or TRIM of a string and similar hilarity. 5) Simple stuff. Converting DB representation to Java representation (important if you want to ORDER BY the sort order of the Java-side representation). The conversion may be a mapping that lives in the database table, or something in an enum definition, or from a configuration file in text format, an SQL expression, or something that's specified directly. 5) Anything that does aggregation. Report stuff, essentially. That's my initial use case actually. I had that subtotals aggregation that would suck if done in Java when doing end-of-month cover-all-the-data processing because, well, you don't want to move a few million rows through Java if you can so it in SQL; it would also suck if the processing was to be done ad-hoc on a small subset of data because the aggregation required a few additional JOINs and that would push the query optimizer beyond its capacity to find a good query plan (the process-it-all SQL-side use case worked because doing full scans would be the best strategy anyway). -- 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.
