Re: cdbimport ignores foreign key constrains with SQLite

[Ali Baharev]

Dear Nikita,

Thank you for this workaround, it seems to work.

> Just checked dbimport with sqlite, seems like driver still skips part
> of the information about foreign keys (or rather quite strictly
> follows the definition of the table).

I don't think it is an issue with the driver. I think something is not
configured properly in Cayenne, or we are still missing some setting
in Cayenne.

I use org.xerial:sqlite-jdbc:RELEASE version 3.36.0.3 from Maven
Central, and the original sqlite database is handled properly in
DBeaver. For example the foreign key information is properly displayed
on the ER Diagram, etc. If DBeaver can do it, then it is not an issue
with the driver.

As I said in my previous email, one has to explicitly turn on the
foreign key support, which I did for this driver (connection). Apart
from that, I haven't changed anything.

Ali

Re: cdbimport ignores foreign key constrains with SQLite

[Nikita Timofeev]

Hi,

Just checked dbimport with sqlite, seems like driver still skips part
of the information about foreign keys (or rather quite strictly
follows the definition of the table).
Primary key name is missing in the FK metadata in particular if FK
references table without defining PK name explicitly.
A work around for this would be to include PK column name in the FK
definition, like "REFERENCES table(PkColumnName)". In this case JDBC
driver gives all the information needed.




On Fri, Jul 22, 2022 at 5:19 PM Ali Baharev  wrote:
>
> > IIRC the issue on Cayenne side was with SQLite JDBC driver not bringing over
> > the FK metadata during DB import. Again, this was years ago.
>
> It must have been a long while ago.
>
> If I use it from DBeaver, I do get all the foreign keys, so the JDBC
> driver has been fixed by now.
>
> Ali



-- 
Best regards,
Nikita Timofeev

Re: cdbimport ignores foreign key constrains with SQLite

[Ali Baharev]

> IIRC the issue on Cayenne side was with SQLite JDBC driver not bringing over
> the FK metadata during DB import. Again, this was years ago.

It must have been a long while ago.

If I use it from DBeaver, I do get all the foreign keys, so the JDBC
driver has been fixed by now.

Ali

Re: cdbimport ignores foreign key constrains with SQLite

[Andrus Adamchik]

> On Jul 22, 2022, at 3:56 PM, Ali Baharev  wrote:
> 
> FKs have been supported since at least 2009. It does have a well
> documented quirk though: You must enable foreign key support
> explicitly.
> 
> 2. Enabling Foreign Key Support
> https://www.sqlite.org/foreignkeys.html#fk_enable 
> 
> 
> But apart from that, it works like a charm.


IIRC the issue on Cayenne side was with SQLite JDBC driver not bringing over 
the FK metadata during DB import. Again, this was years ago.

A.

Re: cdbimport ignores foreign key constrains with SQLite

[Ali Baharev]

Dear Andrus,

Thank you for your prompt reply.

> Last time I checked, SQLite driver didn't support FKs. But this was a long
> time ago, so who knows :)

FKs have been supported since at least 2009. It does have a well
documented quirk though: You must enable foreign key support
explicitly.

2. Enabling Foreign Key Support
https://www.sqlite.org/foreignkeys.html#fk_enable

But apart from that, it works like a charm.

> Derby would certainly support FKs, and the driver is pretty solid.

I need a solution ASAP. If that is the case, then the whole DB will be
migrated to Embedded Derby.

As a side note: The derby team made a breaking change to the embedded
driver at around 10.13. So if I try to use the embedded driver as
documented on the Cayenne website, the Cayenne application crashes.

But the Derby team made a big mistake in my opinion: they made a
breaking change, but they did not bump up the major version to 11.

Thanks again for the prompt reply.

Best wishes,

Ali

Re: cdbimport ignores foreign key constrains with SQLite

[Andrus Adamchik]

Last time I checked, SQLite driver didn't support FKs. But this was a long time 
ago, so who knows :)

Derby would certainly support FKs, and the driver is pretty solid.

Andrus

> On Jul 22, 2022, at 3:24 PM, Ali Baharev  wrote:
> 
> Hi,
> 
> I am trying to use cdbimport with the attached toy SQLite database.
> The pom.xml file and the log file are also attached.
> 
> My tables are imported but Cayenne 4.2.RC1 does not recognize any of
> the foreign key constraints. In other words: I can import the tables
> but their relationships are lost.
> 
> I am following the steps in the Cayenne Guide:
> 
> 3. DB-First Flow
> https://cayenne.apache.org/docs/4.2/cayenne-guide/#db-first-flow
> 
> and also the Cayenne Database First tutorial:
> 
> 2. Importing database
> https://cayenne.apache.org/docs/4.2/getting-started-db-first/#importing-database
> 
> What am I doing wrong? Why are my foreign key constraints lost?
> 
> If – for some reason – SQLite is not yet supported, would it work with
> Apache Derby Embedded DB?
> 
> Any help is greatly appreciated.
> 
> Best wishes,
> 
> Ali
> 

cdbimport ignores foreign key constrains with SQLite

[Ali Baharev]

Hi,

I am trying to use cdbimport with the attached toy SQLite database.
The pom.xml file and the log file are also attached.

My tables are imported but Cayenne 4.2.RC1 does not recognize any of
the foreign key constraints. In other words: I can import the tables
but their relationships are lost.

I am following the steps in the Cayenne Guide:

3. DB-First Flow
https://cayenne.apache.org/docs/4.2/cayenne-guide/#db-first-flow

and also the Cayenne Database First tutorial:

2. Importing database
https://cayenne.apache.org/docs/4.2/getting-started-db-first/#importing-database

What am I doing wrong? Why are my foreign key constraints lost?

If – for some reason – SQLite is not yet supported, would it work with
Apache Derby Embedded DB?

Any help is greatly appreciated.

Best wishes,

Ali
[INFO] Scanning for projects...
[INFO] 
[INFO] -< mygroup:autoimport >-
[INFO] Building autoimport 0.0.1-SNAPSHOT
[INFO] [ jar ]-
[INFO] 
[INFO] --- cayenne-maven-plugin:4.2.RC1:cdbimport (default-cli) @ autoimport ---
[INFO] Connecting to 'jdbc:sqlite:/tmp/kundeninteressen.sqlite3' as 'null'
[INFO] +++ Connecting: SUCCESS.
[INFO] Connecting to 'jdbc:sqlite:/tmp/kundeninteressen.sqlite3' as 'null'
[INFO] +++ Connecting: SUCCESS.
[INFO] Detected and installed adapter: 
org.apache.cayenne.dba.sqlite.SQLiteAdapter
[INFO] Connecting to 'jdbc:sqlite:/tmp/kundeninteressen.sqlite3' as 'null'
[INFO] +++ Connecting: SUCCESS.
[INFO] Detected and installed adapter: 
org.apache.cayenne.dba.sqlite.SQLiteAdapter
[INFO] Connecting to 'jdbc:sqlite:/tmp/kundeninteressen.sqlite3' as 'null'
[INFO] +++ Connecting: SUCCESS.
[INFO] Detected and installed adapter: 
org.apache.cayenne.dba.sqlite.SQLiteAdapter
[INFO] Connecting to 'jdbc:sqlite:/tmp/kundeninteressen.sqlite3' as 'null'
[INFO] +++ Connecting: SUCCESS.
[INFO]   Table: Interessen
[INFO]   Table: Kunden
[INFO]   Table: KundenInteressen
[INFO]   Table: Laender
[INFO] no attribute for declared primary key: 
[INFO] no attribute for declared primary key: 
[INFO] no attribute for declared primary key: 
[INFO] 
[INFO] Map file does not exist. Loaded db model will be saved into 
'/home/ali/ws/autoimport/src/main/resources/datamap.map.xml'
[INFO] 
[INFO] Detected changes: 
[INFO] Create Table Kunden
[INFO] Create Table Laender
[INFO] Create Table KundenInteressen
[INFO] Create Table Interessen
[INFO] 
[INFO] Migration Complete Successfully.
[INFO] Loading XML configuration resource from 
file:/home/ali/ws/autoimport/src/main/resources/cayenne/cayenne-project.xml
[INFO] Loading XML DataMap resource from 
file:/home/ali/ws/autoimport/src/main/resources/cayenne/datamap.map.xml
[INFO] 
[INFO] All changes saved.
[INFO] 
[INFO] BUILD SUCCESS
[INFO] 
[INFO] Total time:  1.489 s
[INFO] Finished at: 2022-07-22T15:18:59+02:00
[INFO] 


kundeninteressen.sqlite3
Description: application/vnd.sqlite3
http://maven.apache.org/POM/4.0.0; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance; xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd;>
4.0.0
mygroup
autoimport
0.0.1-SNAPSHOT


4.2.RC1
8
8






org.apache.cayenne.plugins
cayenne-maven-plugin
${cayenne.version}



${project.basedir}/src/main/resources/cayenne/cayenne-project.xml
${project.basedir}/src/main/resources/datamap.map.xml



jdbc:sqlite:/tmp/kundeninteressen.sqlite3


org.sqlite.JDBC




mypkg
.*
false
false






org.xerial
sqlite-jdbc
3.36.0.3