Related to generating the schema ddl I found there is the dsl.ddl(schema)
method of jooq.
So no need what I'm doing above ;-) and I changed my code to:
val jdbcUrl = "jdbc:sqlite:${outputFile.absolutePath}"
val jdbcProperties = Properties().apply {
setProperty("date_class", "text")
setProperty("date_string_format", "yyyy-MM-dd")
}
DriverManager.getConnection(jdbcUrl, jdbcProperties).use { connection ->
val ctx = using(connection, SQLDialect.SQLITE)
// Enable foreign keys
ctx.execute("PRAGMA foreign_keys = ON;")
// Create schema.
val ddl = ctx.ddl(DEFAULT_SCHEMA)
// Execute each DDL statement
for (query in ddl.queries()) {
println(query) // Print the DDL to be executed
query.execute()
}
Tables are generated, but it fails on the foreign keys then...
I noticed it generates the ddl in the order:
create table country ...
create index ...
alter table country add constraint CONSTRAINT_2E foreign key (continent_id)
references continent (id)
which causes:
> SQL [alter table country add constraint CONSTRAINT_2E foreign key
(continent_id) references continent (id)]; [SQLITE_ERROR] SQL error or
missing database (near "constraint": syntax error)
and I found this stackoverflow article:
https://stackoverflow.com/questions/1884818/how-do-i-add-a-foreign-key-to-an-existing-sqlite-table
which states that SQLite does not support adding a constraint to an
existing table, but that it should be part of the create table statement.
Am I using this incorrectly?
PS: I understand that for creating the constraints inline in the create
table statement, the order of the tables is important.
And I also see that when using the DDLDatabase in the codegen there is no
order maintained in the generated schema class.
So not sure if this can work at all.
Other way would be to use the way I did in my previous post, and create the
table.+ columns + constraints, making sure the correct order of execution
is maintained...
On Monday, March 18, 2024 at 4:50:59 PM UTC+1 Marcel Overdijk wrote:
> Yes I see. I will check out the RecordListener later then.
>
> Btw, based on the generated classes, I wanted to see if I could generate
> the DDL again from it ;-)
>
> I came up with this:
>
> // Table creation statement.
>
> String create = ctx
> .createTable(COUNTRY)
> .columns(COUNTRY.fields())
>
> .constraint(primaryKey(COUNTRY.getPrimaryKey().getFields()))
> .constraints(
> COUNTRY.getUniqueKeys().stream()
> .map(it -> unique(it.getFields()))
> .collect(Collectors.toList())
> )
> .constraints(
> COUNTRY.getReferences().stream()
> .map(it ->
> foreignKey(it.getFields()).references(it.getInverseKey().getTable(),
> it.getKeyFields()))
> .collect(Collectors.toList())
> )
> .getSQL(ParamType.INLINED);
>
> println(create);
>
> COUNTRY.getIndexes().forEach((index) -> {
> String stmt = ctx
> .createIndex(index.getName())
> .on(index.getTable(), index.getFields())
> .getSQL(ParamType.INLINED);
>
> println(stmt);
> });
>
> As far as I could see there is no one-liner that dumps the whole table
> creation including columns, constraints and indexes, right?
>
> So above -- and especially the getInverseKey().getTable(), in the fk --
> is a good approach, or am I missing something?
>
>
> Cheers,
> Marcel
>
>
>
>
>
>
>
>
>
>
>
>
> On Mon, Mar 18, 2024 at 4:03 PM Lukas Eder <lukas...@gmail.com> wrote:
>
>> Hi Marcel,
>>
>> On Mon, Mar 18, 2024 at 3:38 PM Marcel Overdijk <marcelo...@gmail.com>
>> wrote:
>>
>>> Hi Lukas, and thanks for your reply. This helps a lot.
>>>
>>> I used jOOQ In the past with code generation and I understand the
>>> benefits especially in terms of type information and type safety.
>>>
>>
>> Sure, I knew you were aware of it, I just point this out at every
>> occasion, given there are other readers.
>>
>>
>>> In terms of type safety, would it be possible to fail (exception) when
>>> there is no default mapping available?
>>>
>>
>> You can implement a RecordListener that fails if a record isn't entirely
>> populated. The RecordListener.loadEnd() event could help here. I don't
>> think this would be a good out of the box behaviour. There's no reason to
>> assume that everyone always uses 1:1 POJO to Record mappings.
>>
>>
>>> And would there be a way to implement a RecordUnmapper where one only
>>> needs to map fields that do not match automatically?
>>>
>>
>> You can implement one that delegates to the DefaultRecordUnmapper, and
>> then does some extra work on the resulting Record.
>>
>>
>>> Or would it be better to use another tool - like MapStruct - to map from
>>> Continent POJO to ContinentRecord?
>>>
>>
>> Well, not sure what "better" means in this context. I can't recommend
>> other tools because I don't know them. You'll exchange one set of problems
>> for another, as I'd expect :)
>>
>> I hope this helps,
>> Lukas
>>
>> --
>>
> 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/At5WzUp-1Po/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/CAB4ELO7Kr1duM_cyv8yRTaKC-E8-gnfw10bgiWncYjz7UjTQTQ%40mail.gmail.com
>>
>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO7Kr1duM_cyv8yRTaKC-E8-gnfw10bgiWncYjz7UjTQTQ%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/160843aa-1ec3-4013-927e-618cbd8284a7n%40googlegroups.com.
