RenderQuotedNames.ALWAYS not rendering quotes for SQLite

[Marcel Overdijk]

        // Create context.

        Settings settings = new Settings()
                .withRenderQuotedNames(RenderQuotedNames.ALWAYS);

        DSLContext ctx = DSL.using(SQLDialect.SQLITE, settings);

        // Insert statement.

        Continent continent = new Continent();
        continent.setId("europe");
        continent.setCode("eu");
        continent.setName("Europe");
        continent.setDemonym("European");

        String insert = ctx
                .insertInto(CONTINENT)
                .set(ContinentMapper.INSTANCE.unmap(continent))
                .getSQL(ParamType.INLINED);

        println(insert);

but this generates:

insert into continent (id, code, name, demonym) values ('europe', 'eu', 'Europe', 'European')

(without quoted identifiers)

Note: the schema was generated using DDLDatabase but I assume that should not matter.

PS: for SQLite itself is does not matter that much that identifiers are not quoted (although SQLite DOES support this)..
But in my scenario I'm trying to generate SQL files with inserts that are database independent as possible.

I e.g. want to double quote the identifiers as I have fields with name year which otherwise cause problems.

Although MySQL and MariaDB do not support double quotes without SET sql_mode='ANSI_QUOTES'; I could make it work.
I'm basically looking for a ANSI dialect.

Cheers,

Marcel

[Marcel Overdijk]

Note I also tried with SQLDialect.POSTGRES and then I get the double quotes.

But then date values are rendered like:

INSERT INTO "TABLE_A" ("DATE") VALUES ( DATE '2024-03-21')

which I think is not mandatory for PostgreSQL, but is not supported e.g. with SQLite...

[Lukas Eder]

Thanks for your message.

The logic to prevent quoting in SQLite is very old. I don't recall the exact reason, but I believe that SQLite's parser had a lot of trouble with quoted identifiers in some contexts - so the solution was to simply avoid quoting, except for identifiers that conflict with keywords, or that contain special characters. We could review changing this back again if you can create a feature request?

https://github.com/jOOQ/jOOQ/issues/new/choose

Regarding your attempts to get "ANSI SQL," well, good luck :) This is the first of hundreds of problems you'll run into. Why do you want to have "standard SQL files"? How do you plan on executing them? Do note that jOOQ's parser may offer the answer you're looking for:

https://www.jooq.org/doc/latest/manual/sql-building/sql-parser/

You can even use it as a JDBC driver to translate all string based SQL to any dialect that jOOQ supports (if jOOQ can parse the SQL):

https://www.jooq.org/doc/latest/manual/sql-execution/parsing-connection/

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/f02416bb-2c93-44c8-8492-c036cb059ffcn%40googlegroups.com.

[Marcel Overdijk]

Yes I know it’s a challenge to try to create “ANSI SQL” files.

Maybe a bit of background I have to share 😉

I have a project which contains semi-static data which is updated ~2 times a month.

The data is provided to users in various formats like csv, json and smile.

Another variant is a SQLite database that can be used directly.

These formats all generated using a Java project and the base of all data is a bunch of yaml files.

But some prefer to import the data in MySQL or PostgreSQL. Now of course one could use some available tools to migrate the SQLite database to e.g. MySQL or PostgreSQL, or parse the json artifact and import it the desired database of their choice.

But lately I was experimenting a bit to see if could provide a sql file with the create schema ddl and insert statements.

And in such a way it could be imported in most databases.

So that’s where I’m now.

Another option is to create dedicated SQLite, MySQL and PostgreSQL sql script variants.

Sent from my iPhone

On 21 Mar 2024, at 17:00, Lukas Eder <lukas...@gmail.com> wrote:



You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/rCN6Idq28yc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4GQaVC2D9Vgs558AYWcQJUseOJBRf2_7Y8w56m1p3spA%40mail.gmail.com.

[Marcel Overdijk]

Hi Lukas,

I did some further investigation about what insert is generated for each db.

[          ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[CUBRID    ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[Derby     ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date('2023-12-31'), 'europe')
[DuckDB    ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[Firebird  ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[H2        ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[HSQLDB    ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[Ignite    ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[MariaDB   ] insert into `country` (`id`, `code`, `name`, `population`, `percentage`, `date`, `continent_id`) values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[MySQL     ] insert into `country` (`id`, `code`, `name`, `population`, `percentage`, `date`, `continent_id`) values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, {d '2023-12-31'}, 'europe')
[Postgres  ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[SQLite    ] insert into country (id, code, name, population, percentage, date, continent_id) values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, '2023-12-31', 'europe')
[Trino     ] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')
[YugabyteDB] insert into "country" ("id", "code", "name", "population", "percentage", "date", "continent_id") values ('netherlands', 'NL', 'Netherlands', 17947684, 0.2, date '2023-12-31', 'europe')

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?

Alternative is to generate dump with dialect MariaDB and just call it MySQL ;-) 

Otherwise, most likely when, I create a "dumps" for MySQL, PostgreSQL and SQLite it could be used for other databases as well.

MySQL --> MySQL, MariaDB

PostgreSQL --> PostgreSQL + probably many of the other databases 

SQLite --> just SQLite ;-)

Probably my only issue holding me back at the moment is https://github.com/jOOQ/jOOQ/issues/16470 (the separate alter table create foreign key statement which is not support for SQLite).

Cheers,

Marcel

[Lukas Eder]

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

[Marcel Overdijk]

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

[Lukas Eder]

On Fri, Mar 22, 2024 at 12:16 PM Marcel Overdijk <marcelo...@gmail.com> wrote:

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.

For the record, as I've documented also in 

https://github.com/jOOQ/jOOQ/issues/16498

There's still a limitation in MySQL 8.3.0, which I've reported to them here:

https://bugs.mysql.com/bug.php?id=114450

With that limitation in mind, I prefer not to switch away from the (officially supported) ODBC / JDBC escape syntax, which have worked in integration tests for more than a decade now