2013/8/8 Ben Hood <[email protected]> > Hi Lukas, > > On Tue, Aug 6, 2013 at 9:18 AM, Lukas Eder <[email protected]> wrote: > > You're probably right with this. I haven't evaluated the depths of the > STUFF > > solution yet, as this is really a low-prio improvement. But it appeared > to > > be quite a bit of a hack to me, at the time. Similar to jOOQ's RPAD/LPAD > > emulation for SQLite: > > > http://blog.jooq.org/2012/07/19/funky-string-function-simulation-in-sqlite > > Cool - I like the way people find ways around the idiosyncrasies of > all of the different DBs out there :-) >
Yeah. Give them a Stack Overflow bounty and they'll do the craziest stuff! ;-) > > > I see. Yes, this kind of thing is on the roadmap: > > https://github.com/jOOQ/jOOQ/issues/2337 > > > > It's certainly worth thinking about these things, in order to support > > arbitrary SQL dialects more easily. My first evaluations of this > > functionality showed, however, that it will be quite hard to implement > the > > full jOOQ SQL feature support in an external file. It might still be good > > enough for tweaking, though. > > That sounds like a good start. > > > Another option is to think about this new idea I've had, recently, in the > > context of a customer requirement: > > https://github.com/jOOQ/jOOQ/issues/2665 > > > > This issue will allow for providing a custom SQL rendering and variable > > binding SPI implementation. The original requirement was to be able to > > dynamically append predicates to WHERE clauses in the event of access to > any > > given table X. Of course, this functionality could be used to re-write > > jOOQ's SQL generation for 1-2 types of API elements, such as LISTAGG(). > > This sounds a bit more powerful than the external file approach. Not > that I claim to know anything about design, but this approach gives me > the impression that I can selectively decorate the render phase > according to my custom dialect. > Yes, that impression is justified - at least in the long run. > >> Note, jOOQ should probably support code generation for custom aggregate > >> functions. This is currently only supported in Oracle. I have added a > >> feature request for this: > >> https://github.com/jOOQ/jOOQ/issues/2677 > >> > >> > >> I guess what I'm trying to do is to monkey patch the groupConcat() and > >> redirect it to the custom aggregate function for SQL Server. If I was > trying > >> to get groupConcat() to be transparent across dialects, I probably > wouldn't > >> want to overload groupConcat(), instead I would use a separate function > name > >> (although the 3rd party function is actually called GROUP_CONCAT). > > > > > > I have a strong feeling that the LISTAGG syntax might make it into the > SQL > > standard. Not necessarily the function name, but the ordered aggregate > > clause: > > > > WITHIN GROUP (ORDER BY ...) > > OK, so what I've ended up having to do is implement my own custom > aggregation function in the CLR that implements the WITHIN GROUP > (ORDER BY ...) semantics that Oracle provides out of the box. > Basically ordered set functions are not supported in SQL Server - see > here: https://connect.microsoft.com/SQLServer/feedback/details/728969. > "Ordered set functions", that's the formally correct name - good to know. I just checked in my SQL:2008 draft specs. It says 10.9 <aggregate function> [...] <ordered set function> ::= <hypothetical set function> | <inverse distribution function> <hypothetical set function> ::= <rank function type> <left paren> <hypothetical set function value expression list> <right paren> <within group specification> *<within group specification> ::=* * WITHIN GROUP <left paren> ORDER BY <sort specification list> <right paren>* <inverse distribution function> ::= <inverse distribution function type> <left paren> <inverse distribution function argument> <right paren> <within group specification> So, in the long run, this syntax variant is clearly better than MySQL's GROUP_CONCAT() function. So where I've ended up from a JOOQ perspective is that I can easily > create a DSL field object that will render the correct function name, > so all is good with JOOQ for now :-) > > In addition, I just wanted to say that the SQL generation that JOOQ > has allowed me to build has been invaluable in maintaining ports of > non-trivial queries across Oracle, MySQL, Postgres and SQL Server - so > thanks for such a great library :-) > Great, good to know! 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.
