Digest for jooq-user@googlegroups.com - 4 updates in 3 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

Digest for jooq-user@googlegroups.com - 4 updates in 3 topics

http://groups.google.com/group/jooq-user/topics mailing list
Niclas Hedhman <[hidden email]>: Oct 26 02:15PM +0800

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 ??
JOOQ is supposed to hide such differences, but I think there is quite a lot
more to do to get there.
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() ) {}if(
DSL.supports( dialect, Features.dataType( Types.TIMESTAMP_WITH_TIMEZONE) )*
And many more...
I can see that JOOQ internals are dotted with similar checks here and
there, for instance to get the SQL rendering right.
Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java
Lukas Eder <[hidden email]>: Oct 26 06:05PM +0200

Hi Niclas,
> 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:
execute immediate '
create table "t"(
"i" number null,
"n" decimal(3, 1) null,
"s" varchar2(5) not null
when others then
if sqlerrm like 'ORA-00955%' then null;
else raise;
end if;
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
> * : : }*
> *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:
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(
Good point, we should annotate SQLDataType with the @Support annotation as
well. I've created a feature request for this:
Let me know if you see any additional missing features, I'll be very happy
to discuss them.
Niclas Hedhman <[hidden email]>: Oct 26 03:30PM +0800

Hi again,
JOOQ version; 3.10.1
I now have my JOOQ backed Entity Store extension in Apache Polygene passing
our test suites for Postgresql, SQLite, H2 and Derby. That is pretty cool
for us.
BUT MySQL/MariaDb doesn't pass and it is down to the use of CHAR instead of
VARCHAR for String fields.
I promised a complete test case;
start Docker instance of MySQL;
* docker run \*
* -d \*
* --name mariadb \ -e MYSQL_ROOT_PASSWORD=testing \ -e
MYSQL_DATABASE=testdb \ -p 3306:3306 \ mysql*
Then run the testcase below.
You will have created a table
* create table if not exists `ENTITIES`(`_identity` char null)*
and then an exception on insertion of data;
* insert into `ENTITIES` (`_identity`) values ('123')*
*org.jooq.exception.DataAccessException: SQL [insert into `ENTITIES`
(`_identity`) values (?)]; Data truncation: Data too long for column
'_identity' at row 1*
If you change to a single character value instead of "123", it will execute
without exception.
Even if I change the field declaration to be (instead of String.class)
Field<String> identityColumn = DSL.field( DSL.name( "_identity" ),
I get the same result.
I don't know if you can force MySQL into some other behavior, but I think
that is not the right way to go. I don't understand why CHAR was chosen in
the first place. if there was some clever reason...
package org.hedhman.niclas;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record;
import org.jooq.SQLDialect;
import org.jooq.Table;
import org.jooq.TransactionProvider;
import org.jooq.conf.RenderNameStyle;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.TableImpl;
import org.jooq.impl.ThreadLocalTransactionProvider;
import org.junit.Test;
public class Experiment
public void test1()
throws Exception
String host = "";
int port = 3306;
DataSource dataSource = dbcpDataSource( host, port );
Settings settings = new Settings().withRenderNameStyle(
RenderNameStyle.QUOTED );
SQLDialect dialect = SQLDialect.MARIADB;
ConnectionProvider connectionProvider = new
DataSourceConnectionProvider( dataSource );
TransactionProvider transactionProvider = new
ThreadLocalTransactionProvider( connectionProvider, false );
Configuration configuration = new DefaultConfiguration()
.set( dialect )
.set( connectionProvider )
.set( transactionProvider )
.set( settings );
DSLContext dsl = DSL.using( configuration );
Field<String> identityColumn = DSL.field( DSL.name(
"_identity" ), String.class );
Name entitiesTableName = DSL.name( "ENTITIES" );
Table<Record> entitiesTable = new TableImpl<Record>(
entitiesTableName );
dsl.transaction( t -> {
dsl.createTableIfNotExists( entitiesTable )
.column( identityColumn )
dsl.transaction( t -> {
dsl.insertInto( entitiesTable )
.set( identityColumn, "123" )
private DataSource dbcpDataSource( String host, int port )
throws Exception
BasicDataSource pool = new BasicDataSource();
String driverClass = "com.mysql.jdbc.Driver";
Class.forName( driverClass );
pool.setDriverClassName( driverClass );
pool.setUrl( "jdbc:mysql://" + host + ":" + port + "/testdb" );
pool.setUsername( "root" );
pool.setPassword( "testing" );
pool.setDefaultAutoCommit( false );
return pool;
Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java
Niclas Hedhman <[hidden email]>: Oct 26 10:40AM +0800

So, I have come back to this issue...
It seems that I was chasing another problem in the MySQL case, and adding
additional debugging support to figure out what was going on there, and
down the rabbit hole of this issue and lost track of the problem that
started this.
So, Postgresql case works fine. For MySQL, my VARCHAR fields becomes
CHAR(1) fields and the only code that differs is that Schema is not used in
MySQL case. I will try to create a testcase showing this a bit later.
Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].