2013/6/23 Roger Thomas <[email protected]> > I have to say, if you were to change things to get around reserved words I > would vote for d (all-uppercase). > > The logic is no more than - this seems to be the way people have/do write > SQL queries. I can't say its part of any standard, but it does seem to be > the convention and its one I've been following for rather to many years. It > maybe just because I and SQL come from an age when uppercase was use in > languages such as Informix 4GL :) >
.... and FORTRAN, COBOL, BASIC, I remember those days. Pascal and C really messed up our upper / lower casing universe! ;-) d) has its "extreme cases" as well, such as: - ON_DUPLICATE_KEY_UPDATE() - WHEN_MATCHED_THEN_UPDATE() - WHEN_NOT_MATCHED_THEN_INSERT() The obvious advantage of making SQL DSL methods all-uppercase is the fact that they can be immediately distinguished from non-DSL methods, such as: - a.BETWEEN(b).AND(c) - a.BETWEEN_SYMMETRIC(b).AND(c) - a.IS_NULL() - a.IS_NOT_NULL() - ROW_NUMBER().OVER().PARTITION_BY(a).ORDER_BY(b) as opposed to - a.getName() - a.getType() - a.getDataType() Maybe, both APIs could be maintained in parallel using a code-generator, leaving the decision up to the user, whether they want to camel-case or upper-case their SQL DSL. From a backwards-compatibility perspective, there's probably no choice in maintaining both in parallel anyway. > One thing, all the background info you have provided would fit rather well > in the main jOOQ document - under some form of background or why it is as > it is heading. > Yes, you're right. I had thought of this, yesterday. It's too bad to be "hidden" in an e-mail in the user-group. I'll document this with https://github.com/jOOQ/jOOQ/issues/2547 With Christopher Decker's recent helping in creating a railroad-diagram tool [1] and Peter Verhas's inspiration, mapping a finite state-machine to a fluent API (and / or a BNF), the language-aspects of jOOQ will hopefully be heavily improved in the near future [3]. Cheers Lukas [1]: https://github.com/Chrriis/RRDiagram [2]: https://github.com/verhas/fluflu [3]: https://github.com/jOOQ/jOOQ/issues/1737 > Roger > > > On Saturday, June 22, 2013 1:00:08 PM UTC+1, Lukas Eder wrote: > >> 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/<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. > > > -- 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.
