Hi Christopher, 2013/6/22 Christopher Deckers <[email protected]>
> Hi Lukas, > > >> The CASE expression is indeed modeled by DSL.decode(), as you can see >> here: >> >> http://www.jooq.org/doc/3.0/manual/sql-building/column-expressions/case-expressions/ >> > > "Unfortunately, both case and else are reserved words in Java. jOOQ chose > to use decode() from the Oracle DECODE function, and otherwise(), which > means the same as else." > > I think keywords should not be replaced with synonyms, otherwise you are > not mapping SQL to Java, but SQL to a jOOQ-specific QL. I think that even > if it is not as good as having "case()" and "else()", there should be > "case_()" and "else_()". Developers would understand that this is because > of reserved keywords and would not have to analyze the API/documentation to > map their SQL. > > > We are lucky that Java does not have more reserved keywords, otherwise: > SELECT * FROM BOOK WHERE PUBLISHED_IN = 2011 ORDER BY TITLE > would have been: > create.pickFrom(BOOK).condition(PUBLISHED_IN.sameAs(2011)).sortBy(TITLE) > > :) > Grrrr ;-) But in a way, you are right of course. First off, let's look at the various reasons why jOOQ cannot map SQL to Java 100%: 1. "Keywordless" syntax (e.g. SET a = 1, b = 2) 2. "Semantic" whitespace (e.g. ORDER[ ]BY) 3. "Superfluous" keywords (e.g. CASE ... END) 4. "Superfluous" syntactic elements (e.g. WITHIN GROUP (ORDER BY..) OVER (PARTITION BY..) 5. Java's reserved keywords 6. Java's missing operator overloading capabilities 7. SQL's "reference before declaration" capability How does jOOQ work around the above? *1. By introducing artificial keywords. Examples* SQL: UPDATE t SET a = 1, b = 2 jOOQ: update(t).set(a, 1).set(b, 2) Note that this example also shows missing operator overloading capabilities, where "=" is replaced by "," SQL: (a, b) IN ((1, 2), (3, 4)) jOOQ: row(a, b).in(row(1, 2), row(3, 4)) In this case, ROW is an actual (optional) SQL keyword implemented by at least Postgres *2. By using camel case* This is actually very simple to work around. jOOQ follows standard Java method naming conventions to map SQL keywords (case-insensitive) to Java methods (case-sensitive, camel-cased) SQL: ORDER BY jOOQ: orderBy() *3. By omitting them* Some SQL keywords aren't really necessary. While in SQL itself, it is easy to write them, writing them in Java is a bit more tedious.* * SQL: CASE .. WHEN .. THEN .. END jOOQ: decode() .. when(.., ..) Agreed, this goes along your argument and should probably be fixed. jOOQ currently omits THEN and END keywords, here. * * *4. By omitting them* Some SQL constructs are hard to map to Java, but they are also not really necessary SQL: LISTAGG(a, b) WITHIN GROUP (ORDER BY c) OVER (PARTITION BY d) jOOQ: listagg(a, b).withinGroupOrderBy(c).over().partitionBy(d) Alternatively, of course, jOOQ could introduce: listagg(a, b).withinGroup(orderBy(c)).over(partitionBy(d)) Creating reusable orderBy(...) and partitionBy(...) clauses. This has to be well-thought-through, though *5. By using an alternative* This is the point of interest in this discussion. Currently, there are collisions between: SQL: CASE jOOQ: decode SQL: ELSE jOOQ: otherwise SQL: FOR (in PIVOT clauses) jOOQ: on Future collision potential: SQL: BOOLEAN, CHAR, DEFAULT, DOUBLE, ENUM, FLOAT, IF, INT, LONG, PACKAGE *6. By using descriptive methods, or a comma* Most SQL operators have to be mapped to descriptive method names in Java: SQL: = jOOQ: equal(), eq() SQL: SET a = b jOOQ: set(a, b) SQL: <> jOOQ: notEqual(), ne() *7. That cannot be worked around* This is what has been keeping me from implementing CTE so far. SQL: WITH a(x, y) AS (SELECT 1, 2) SELECT a.x, a.y FROM a *Discussion* As you can see, there are a lot of caveats when implementing an internal domain specific language (as opposed to an external one). As you stated yourself, luckily, Java doesn't have too many reserved words. I currently see four ways to solve 5) a) by using synonyms b) by appending "disambiguators" such as case_ or case$ or case c) by introducing artificial spelling, such as casë, casǝ, casɇ ;-) d) by changing the API to be all-uppercase: SELECT(), CASE(), ORDER_BY() e) by changing the API to be camel-case with an upper-case initial letter: Select(), Case(), OrderBy() d) and e) are massive changes. Any other ideas? 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.
