Thanks for detailed feedback Luka, I really appreciate it.
I think https://github.com/jOOQ/jOOQ/issues/16498 makes sense,
but also MariaDB supports the { d '*str*' } expression (as expected as it
is a fork), so even in my case that should be no problem.
Note I'm generating the .sql file dumps at release time (which is just a
Gradle build).
It includes some straightforward create table and create view statements
and the inserts. Just basic types are used.
So for now I've taken this approach during the release/build:
>From the DDLDatabase generated code I basically create 3 DSLContext
instances (MySQL, PostgreSQL, SQLite),
and for each context I generate the .sql.
For creating the tables + indexes I use:
private static void createTable(DSLContext ctx, Table<?> table) {
ctx.settings().setRenderFormatted(true);
String create = ctx
.createTable(table)
.columns(table.fields())
.constraint(primaryKey(Objects.requireNonNull(table.getPrimaryKey()).getFields()))
.constraints(
table.getUniqueKeys().stream()
.map(it -> unique(it.getFields()))
.collect(Collectors.toList())
)
.constraints(
table.getReferences().stream()
.map(it ->
foreignKey(it.getFields()).references(it.getInverseKey().getTable(),
it.getKeyFields()))
.collect(Collectors.toList())
)
.getSQL(ParamType.INLINED);
println(ctx.dialect(), create + ";");
ctx.settings().setRenderFormatted(false);
table.getIndexes().forEach((index) -> {
String stmt = ctx
.createIndex(index.getName())
.on(index.getTable(), index.getFields())
.getSQL(ParamType.INLINED);
println(ctx.dialect(), stmt + ";");
});
// Above is alternative to use ddl method directly.
// See https://github.com/jOOQ/jOOQ/issues/16470 (sqlite foreign
key issue)
// Queries ddl = ctx.ddl(table);
// ddl.forEach(query -> println(ctx.dialect(),
query.getSQL(ParamType.INLINED) + ";"));
}
For creating the views:
private static void createView(DSLContext ctx, Table<?> view) {
ctx.settings().setRenderFormatted(true);
Queries ddl = ctx.ddl(view);
ddl.forEach(query -> println(ctx.dialect(),
query.getSQL(ParamType.INLINED) + ";"));
ctx.settings().setRenderFormatted(false);
}
and for the inserts:
String insert = ctx
.insertInto(table)
.set(record)
.getSQL(ParamType.INLINED);
println(ctx.dialect(), insert + ";");
And I think this will work.
Only I need to something special for the create table statement.
I've tested it with SQLite and it worked.
I only need to test the approach with MySQL and PostgreSQL.
Thanks!
Marcel
On Friday, March 22, 2024 at 11:49:56 AM UTC+1 lukas...@gmail.com wrote:
> Marcel,
>
> Yes I know it’s a challenge to try to create “ANSI SQL” files.
>>
>
> I've just thought of a problem you will inevitably run into with your
> approach: jOOQ does not give you any guarantee of "ANSI SQL." If you pick
> any dialect (e.g. PostgreSQL), it will generate a "reasonable" SQL query or
> expression for your target PostgreSQL dialect and version. This may or may
> not coincide with "ANSI SQL." There is no guarantee that it will remain the
> same over jOOQ versions. In the past, there have been numerous cases where
> a better syntax came along, and jOOQ switched to generating that instead of
> what PostgreSQL offered before.
>
> So, you should see each jOOQ SQLDialect as what it is. A dialect for a
> *specific* target database product (AND version!)
>
> Even SQLDialect.DEFAULT will not be ANSI SQL. There's an epic issue to
> change it to reflect the "most expected" syntax:
> https://github.com/jOOQ/jOOQ/issues/9085
>
> But it is not tested on any database. Its main purpose is toString()
> rendering when no other dialect is available.
>
>
>> But lately I was experimenting a bit to see if could provide a sql file
>> with the create schema ddl and insert statements.
>>
>
> If you're using jOOQ, I'd use "jOOQ SQL":
>
> https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/sql-parser-grammar/
>
> ... and use the parser to translate it to the target dialect.
>
> Another option is to create dedicated SQLite, MySQL and PostgreSQL sql
>> script variants.
>
>
> That's another option of course, and again jOOQ's parser / translator can
> help you with that. The difference would be whether you translate the files
> at runtime (former idea) or at compile time (latter idea)
>
>
> On Fri, Mar 22, 2024 at 10:33 AM Marcel Overdijk <marcelo...@gmail.com>
> wrote:
>
>> Hi Lukas,
>>
>> I did some further investigation about what insert is generated for each
>> db.
>>
>> [...]
>>
>
>> And there is of course quite some common ground, but also some subtle
>> nuances...
>>
>> MySQL and MariaDB use backticks as that's there default,
>> *I only wonder why the MYSQL insert is using {d '2023-12-31'} instead
>> of date '2023-12-31' ?*
>> *And is this something that could be manipulated easily using jOOQ
>> settings?*
>>
>
> This was because of:
> https://github.com/jOOQ/jOOQ/issues/3648
>
> I've created an issue to investigate this:
> https://github.com/jOOQ/jOOQ/issues/16498
>
> You can implement your own data type binding to override this behaviour.
> If you're using the parser, the parser would have to use some sort of meta
> data lookup where your binding is made available, e.g. by using generated
> code, or by hand-writing TableImpl classes.
>
>
>> Alternative is to generate dump with dialect MariaDB and just call it
>> MySQL ;-)
>>
>
> There are always subtle differences. For example, MariaDB supports INSERT
> .. RETURNING, MySQL doesn't. It doesn't apply in your case, but such
> differences always exist. Perhaps better patch the generated SQL with a
> regex for the time being. Or, if you execute the SQL with JDBC, then there
> shouldn't be a problem with the {d '...'} syntax
>
--
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 jooq-user+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/2ba2ff6c-4a90-4edf-bed6-037d547b7befn%40googlegroups.com.
