For "createTableIfNotExists", doesn't the JDBC Connection already abstract out what is needed?
https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[]) But I don't know if it is reliable enough across JDBC drivers, I don't have that level of SQL/JDBC experience. Ahhh! I just saw (looking for how to get the JDBC Connection), that the DatabaseMetaData is already abstracted out in class Meta. For me, that is good enough for now, as I can always make the check before doing a createTable and not rely on "ifNotExists". No action required from you, for _my_ benefit. For @Support annotation; If I understand it correctly, it is intended for when you use the JOOQ data modeling tooling. In my case, everything is runtime (Table<Record>, and no known types at compile time) and Polygene has its own modeling design (close to DDD) with Entity, Association, ManyAssociation and NamedAssociation and quite well manage to hide the implementation details in the underlying store. The upside is that no JOOQ details is visible to our users, and I can control exactly what method calls into JOOQ that is performed (quite few). For reference, we currently support; 'Cassandra', 'File', 'Geode', 'Hazelcast', 'JClouds', 'Jdbm', 'LevelDB', 'Memory', 'MongoDB', 'Java Preferences', 'Redis', 'Riak' and a SQL Key/Value store. This JOOQ-backed SQL Entity Store is strategically important to tackle enterprise environments. I agree that definition of "features" are difficult and might be counter-productive. Perhaps it is enough to get hold of the JDBC Connection, getDatabaseMetaData and check the many supportsXyz() methods in there. Is there a way to get hold of the current connection? I think I can only do acquire() on the ThreadLocalConnectionProvider but Reflection on the ThreadLocalTransactionProvider, but that is nasty and would like to avoid if there is a better way. All-in-all, there is not much "meat" left in this thread. I am quite happy here. Niclas On Friday, October 27, 2017 at 12:05:52 AM UTC+8, Lukas Eder wrote: > > Hi Niclas, > > 2017-10-26 9:15 GMT+03:00 Niclas Hedhman <[email protected] <javascript:> > >: > >> >> Is it reasonable to ask that JOOQ *createTableIfNotExists()* knows which >> underlying SQL implementations that supports it natively and does a "SELECT >> 1 FROM ..." automatically for those that don't ?? >> > > Sure, this is very desirable. And the feature is actually emulated in > those databases that do not support it, *if* they support anonymous > procedural blocks. E.g. in Oracle, the emulation runs something along the > lines of: > > begin > execute immediate ' > create table "t"( > "i" number null, > "n" decimal(3, 1) null, > "s" varchar2(5) not null > ) > '; > exception > when others then > if sqlerrm like 'ORA-00955%' then null; > else raise; > end if; > end; > > > There might just be dialects where we either didn't do it yet, or where we > didn't figure out how it could be done. What dialect(s) are you interested > in, and do you have a pointer on how it could be done? > > We could certainly also run two queries from the client instead of just > one. The problem with that is (so far) that we have to update our execution > model and all the SPIs like ExecuteListener to take into account the > possibility of a single execute() call by the client running several > queries. Also, what will Query.getSQL() produce? A ";" separated statement > batch? This will take some time to design properly. > > A much better solution would be to emulate anonymous blocks where they're > not available by creating temporary stored procedures. This would be > feature request https://github.com/jOOQ/jOOQ/issues/6474. > > There are other similar issues, such as knowing which SQL types are >> supported, if schemas are supported and probably a lot more I have not come >> across yet. Perhaps a first step is to have a >> >> >> >> >> * public class SQL { : :* >> >> * public static boolean supports( SQLDialect dialect, Feature >> feature ) { ... }* >> >> >> * : : }* >> >> >> *if( DSL.supports( dialect, Features.createTableIfNotExists() ) {}* >> > > Yeah, that suggestion has been discussed before. I'm not really a big fan > of it. From 10 years of experience developing jOOQ, I don't feel like > there's a thing like a well-defined "feature". > > Note, however, that we already annotate the entire API with the > @org.jooq.Support annotation. Since jOOQ 3.9, you can type check your API > usage using the checker framework plugin that we've added. A detailed blog > post about this can be found here: > > https://blog.jooq.org/2016/05/09/jsr-308-and-the-checker-framework-add-even-more-typesafety-to-jooq-3-9 > > If that sounds like something you'd like to use, I'd really appreciate any > feedback you could provide. We haven't seen much feedback on this > interesting feature yet. > > >> >> *if( DSL.supports( dialect, Features.dataType( >> Types.TIMESTAMP_WITH_TIMEZONE) )* >> > > Good point, we should annotate SQLDataType with the @Support annotation as > well. I've created a feature request for this: > https://github.com/jOOQ/jOOQ/issues/6741 > > Let me know if you see any additional missing features, I'll be very happy > to discuss them. > > Thanks, > 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/d/optout.
