Unable to import DB either by schemas or as a whole

[JiffB]

Debian stable (buster)
java : openjdk-11-jre:amd64          11.0.6+10-1~deb10u1 amd64
       openjdk-11-jre-headless:amd64 11.0.6+10-1~deb10u1 amd64
PostgreSQL V.12 repo :  https://apt.postgresql.org/pub/repos/apt/ buster-pgdg main
DB Importation option ON : Respect database schema

=====================*= Hi, I can't import tables from my DB presumably because it uses my own types and domains, the following error occur if I try to import a whole schema :

ava.util.concurrent.ExecutionException: de.erdesignerng.exception.ReverseEngineeringException: Unknown data type da_label_6_nn for common_table.ri_table
	at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
	at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)
	at java.desktop/javax.swing.SwingWorker.get(SwingWorker.java:613)
	at de.erdesignerng.visual.LongRunningTask.run(LongRunningTask.java:62)
Caused by: de.erdesignerng.exception.ReverseEngineeringException: Unknown data type da_label_6_nn for common_table.ri_table
	at de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.reverseEngineerTable(JDBCReverseEngineeringStrategy.java:302)
	at de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.updateModelFromConnection(JDBCReverseEngineeringStrategy.java:750)
	at de.erdesignerng.visual.common.ReverseEngineerCommand$2.doWork(ReverseEngineerCommand.java:134)
	at de.erdesignerng.visual.common.ReverseEngineerCommand$2.doWork(ReverseEngineerCommand.java:125)
	at de.erdesignerng.visual.LongRunningTask$1.doInBackground(LongRunningTask.java:52)
	at java.desktop/javax.swing.SwingWorker$1.call(SwingWorker.java:304)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.desktop/javax.swing.SwingWorker.run(SwingWorker.java:343)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:835)

what I do not understand is MogwaiERDesignerNG has options to work with types and domains but can't import them to reverse engineering the DB !?!   Trying to import the whole DB (CTRL-A into the Reverse Engineering popup) lead to the same kind of error :

java.util.concurrent.ExecutionException: de.erdesignerng.exception.ReverseEngineeringException: Unknown data type db_true_nn for resald_common.active
	at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
	at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)
	at java.desktop/javax.swing.SwingWorker.get(SwingWorker.java:613)
	at de.erdesignerng.visual.LongRunningTask.run(LongRunningTask.java:62)
Caused by: de.erdesignerng.exception.ReverseEngineeringException: Unknown data type db_true_nn for resald_common.active
	at de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.reverseEngineerTable(JDBCReverseEngineeringStrategy.java:302)
	at de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.updateModelFromConnection(JDBCReverseEngineeringStrategy.java:750)
	at de.erdesignerng.visual.common.ReverseEngineerCommand$2.doWork(ReverseEngineerCommand.java:134)
	at de.erdesignerng.visual.common.ReverseEngineerCommand$2.doWork(ReverseEngineerCommand.java:125)
	at de.erdesignerng.visual.LongRunningTask$1.doInBackground(LongRunningTask.java:52)
	at java.desktop/javax.swing.SwingWorker$1.call(SwingWorker.java:304)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.desktop/javax.swing.SwingWorker.run(SwingWorker.java:343)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:835)

Is it because PostgreSQL is in V.12 or is the normal behavior to apparently force the user to build all things from Mogwai or is it a bug ?

[mirkosertic]

Hmm, it should work, but however the Dialect fails to retrive domain aka custom type information from the DB. Do you have an example DDL or pgdump in SQL form so I can reproduce this problem?

[JiffB]

Yep I'm preparing it and will send it to the email address of your gtihub profile ASAP.

[JiffB]

Ok, it's gone!

[mirkosertic]

Thank you for your quick reply.

After a little investigation, it seems ERDesigner has problems finding domains used in a table that is defined in a different schema than the domain. In your case, the domains are defined in the public schema, where the tables are defined in schemas like resto.

I've seen this situation before. To make reverse engineering possible, please include the public schema and the resto schema in the schema list before starting the reverse engineering.

[JiffB]

Raaahhh, this time, I tried to import the closest schemas, 'public' and 'resto' but there was another error, a more concerning one : null pointer exception :/

Here is the log :

java.util.concurrent.ExecutionException: java.lang.NullPointerException
	at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
	at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:191)
	at java.desktop/javax.swing.SwingWorker.get(SwingWorker.java:613)
	at de.erdesignerng.visual.LongRunningTask.run(LongRunningTask.java:62)
Caused by: java.lang.NullPointerException
	at de.erdesignerng.model.CustomType.getSqlDefinition(CustomType.java:65)
	at de.erdesignerng.dialect.sql92.SQL92SQLGenerator.createAddCustomTypeStatement(SQL92SQLGenerator.java:504)
	at de.erdesignerng.modificationtracker.HistoryModificationTracker.addCustomType(HistoryModificationTracker.java:167)
	at de.erdesignerng.model.Model.addCustomType(Model.java:476)
	at de.erdesignerng.dialect.postgres.PostgresReverseEngineeringStrategy.reverseEngineerCustomTypes(PostgresReverseEngineeringStrategy.java:319)
	at de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.updateModelFromConnection(JDBCReverseEngineeringStrategy.java:736)
	at de.erdesignerng.visual.common.ReverseEngineerCommand$2.doWork(ReverseEngineerCommand.java:134)
	at de.erdesignerng.visual.common.ReverseEngineerCommand$2.doWork(ReverseEngineerCommand.java:125)
	at de.erdesignerng.visual.LongRunningTask$1.doInBackground(LongRunningTask.java:52)
	at java.desktop/javax.swing.SwingWorker$1.call(SwingWorker.java:304)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.desktop/javax.swing.SwingWorker.run(SwingWorker.java:343)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:835)

NB: I also tried to import 'information_schema', 'pg_catalog', 'public' and 'resto' together, just in case but the result was the same, a null pointer error.

[JiffB]

O_o I also have this null pointer exception while trying to import the 'public' schema only !

[JiffB]

I also missed a warning on the command line from which I launched Mogwai :

2020-04-21 22:24:33,777 [main] de.erdesignerng.dialect.DataTypeIO
INFO : [] Replacing datatype clob[]
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by net.sf.jasperreports.engine.util.ClassUtils (file:/opt/ERDesignerNG/app/lib/jasperreports-6.9.0.jar) to constructor com.sun.org.apache.xerces.internal.util.XMLGrammarPoolImpl()
WARNING: Please consider reporting this to the maintainers of net.sf.jasperreports.engine.util.ClassUtils
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
2020-04-21 22:24:35,188 [AWT-EventQueue-0] de.erdesignerng.visual.common.DockingHelper
INFO : [] Workbench layout restored (<<< LAST LINE)

[mirkosertic]

I had to remove some unused types, domains and functions to make reverse engineering possible:

DROP DOMAIN public.da_label_6_array_defnul;
DROP DOMAIN public.dd_tr_defnul;
DROP TYPE public.timerange;
DROP DOMAIN public.tt_tstzrange_defnul;
DROP DOMAIN public.tt_tstzrange_nn;
DROP DOMAIN public.da_label_6_array_defnul;

Range-Types and Arrays are currently not supported.

After this and selecting all schemas except the v* ones, reverse egineering worked. I could't reverse engineer the v* schemas in combination with the others due do name clashes for some objects, like 'client`.

All this mess comes from the fact that schemas are currently not well supported by ERDesignerNG. In fact, I haven't touched the code for that for ages due to lack of time...

[JiffB]

Between Ranges, arrays and object being the same name that are impossible to import, it is unusable for my projects :/ Anyway, thanks for your time and best try.