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
On Thursday, March 21, 2024 at 5:55:26 PM UTC+1 Marcel Overdijk wrote:
> 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:
>
>
>
> 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/
>
> On Thu, Mar 21, 2024 at 4:30 PM Marcel Overdijk <marcelo...@gmail.com>
> wrote:
>
>> 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...
>>
>>
>>
>>
>> On Thursday, March 21, 2024 at 4:08:35 PM UTC+1 Marcel Overdijk wrote:
>>
>>>
>>>
>>> // 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
>>>
>>> --
>> 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
>>
>> <https://groups.google.com/d/msgid/jooq-user/f02416bb-2c93-44c8-8492-c036cb059ffcn%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
> --
>
> 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
>
> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO4GQaVC2D9Vgs558AYWcQJUseOJBRf2_7Y8w56m1p3spA%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
>
--
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/65dac19c-5ca9-44d9-8019-1de6262103c5n%40googlegroups.com.
