ER Diagrams

[jjunk]

I'm just getting started with DBeaver and Eclipse (4.2).

I've opened an existing SQLite database with lots of tables and relationships between them.

When I view the ER Diagram, all of the tables are there but there are no relationships (lines, etc.) between any of the columns and tables, even though relationships exist.

I see a screenshot on your website that shows lines and related information between various columns and tables.

Am I missing something here?

Thanks

ER Diagram export

metadata.png (33.55 KiB) Viewed 62646 times

[Serge]

Hello,

What SQLLite driver do you use?
As far as I know all SQLite JDBC drivers (Zentus, Zerial, Werner) lacks foreign keys metadata support. While underlying SQLite engine supports FKs manipulations high level metadata API doesn't.
I've just checked recent versions and it seems that nothing changed in last couple of years. I'd be happy to add fully functional JDBC driver in DBeaver config but it doesn't exist
Maybe someday I'll add my own implementation of fixed JDBC driver for SQLite (as I did for Cassandra).

[jjunk]

Hi Serge,

Thanks for the quick response!

I have tried all three SQLite drivers with the same results.

Am I correct in understanding that it is a limitation of the SQLite JDBC drivers and is peculiar to reverse engineering SQLite ER Diagrams?

I haven't tried another database and related drivers yet (e.g. MySQL) but does it work for those in Eclipse?

Not a show stopper. Just wondering if I was doing something wrong.

Thanks

Jeff

[Serge]

Yes, it is a limitation of driver. DBeaver needs informations about foreign keys to build ERD associations.
For instance here is ERD I made a minute ago for Oracle sample schema:

[stonebig]

Hello,

I would like to see If I can improve the situation regarding SQLite Driver,as I see there is life in the Xerail Driver.
Could you pinpoint where I should start in existing documentation of DBeaver or JDBC ?

Regards,

[Serge]

Hello,

I agree, current situation with SQLite driver support isn't good. I'm going to change it in the next version.
I'll remove Werner and Zentus drivers (obviously they are too old, doesn't support many features, etc). And I'll update Xerial to the most recent version and will improve metadata reading functions.
Some improvements are already done. Example of ERD rendered in SQLite/Chinook sample DB is in attachment.
Next version release planned on the beginning of February. If you'd wish to test recent improvements then I can make EA version.

Thanks

[valix]

Hello,
It's nice to see that in the latest version the ER diagrams stared to work for SQLite, but there still is a limitation/bug.
If there are multiple foreign keys in a table, DBeaver is only displaying the first foreign key in table properties and the ER Diagram is incomplete.
I am running DBeaver 2.3.8 that is using sqlite-jdbc4-3.8.2 driver.

Is this a bug that can be addressed or it's another limitation ?

Thanks.

[Serge]

Hi,

Yep, it is a yet another bug (actually two bugs, one in SQLite driver and one in DBeaver). Driver just doesn't return names of FK/PK so I have to generate them when loading metadata.
This problem will be fixed in the next version.

Thanks for report!

[noanylove]

Having the same problem as valix mentioned, it does not work correctly when there are multiple foreign keys in a table. I'm using Dbeaver 3.0.2 and xerial's sqlite-jdbc-3.7.2.jar. Wish to fix the this probelm soon. Thanks.

[Serge]

Hello,

That's odd. I can see all FKs in my sample databases. But maybe you have another database version.
Did you try another SQLite drivers? (you can add them manually in driver manager).

Thanks