No relationships were detected in the schema with SchemaSpy

ZhiliangWu commented 5 years ago

Prerequisites

[ X ] Put an X between the brackets on this line if you have done all of the following:
- Checked the online documentation: https://mimic.physionet.org/about/mimic/
- Checked that your issue isn't already addressed: https://github.com/MIT-LCP/mimic-code/issues?utf8=%E2%9C%93&q=

Description

Description of the issue, including:

what you have tried

I am trying to build locally the ERD like the mimic SchemaSpy as posted here. I tried both the old version (5.0.0) and newer version of SchemaSpy (6.1.0) on both the complete mimic and the mimic-demo databases installed locally. However, things worked good except that there is no relationships were detected after running the code as shown below. Are there any tricks we should take so as to make it work?

queries demonstrating your question (if applicable)


$ java -jar schemaspy-6.1.0.jar -t pgsql -dp ../postgresql-42.2.8.jar -db mimic_demo -host localhost -port 5432 -s mimiciii -u xxxxxxxx  -p xxxx -o ./results -hq
____       _                          ____
/ ___|  ___| |__   ___ _ __ ___   __ _/ ___| _ __  _   _
\___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
___) | (__| | | |  __/ | | | | | (_| |___) | |_) | |_| |
|____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
                                         |_|    |___/

                                          6.1.0

SchemaSpy generates an HTML representation of a database schema's relationships. SchemaSpy comes with ABSOLUTELY NO WARRANTY. SchemaSpy is free software and can be redistributed under the conditions of LGPL version 3 or later. http://www.gnu.org/licenses/

INFO - Starting Main v6.1.0 on xxxxxxxx with PID 23252 (/home/xxxxxxxx/Downloads/schema/schemaspy-6.1.0.jar started by xxxxxxxx in /home/xxxxxxxx/Downloads/schema) INFO - The following profiles are active: default INFO - Started Main in 1.362 seconds (JVM running for 1.764) INFO - Starting schema analysis INFO - Connected to PostgreSQL - 9.6.15 INFO - Gathering schema details Gathering schema details...............................................(120sec) Connecting relationships...............................................(0sec) Writing/graphing summary.INFO - Gathered schema details in 0 seconds INFO - Writing/graphing summary INFO - Graphviz rendered set to '' ........(2sec) Writing/diagramming detailsINFO - Completed summary in 2 seconds INFO - Writing/diagramming details ............................................(0sec) Wrote relationship details of 44 tables/views to directory './results' in 123 seconds. View the results by opening ./results/index.html INFO - Wrote table details in 0 seconds INFO - Wrote relationship details of 44 tables/views to directory './results' in 123 seconds. INFO - View the results by opening ./results/index.html

tompollard commented 5 years ago

The screenshot is reporting no constraints on your database. Did you add them with: https://github.com/MIT-LCP/mimic-code/blob/master/buildmimic/postgres/postgres_add_constraints.sql?

ZhiliangWu commented 5 years ago

The screenshot is reporting no constraints on your database. Did you add them with: https://github.com/MIT-LCP/mimic-code/blob/master/buildmimic/postgres/postgres_add_constraints.sql?

@tompollard Thanks for your quick reply. I haven't added it as I followed the official tutorial hosted here. After running psql 'dbname=mimic user=xxxxxxx options=--search_path=mimiciii' -f postgres_add_constraints.sql and the previous building code. I got the relationships! Thanks for pointing this out!

Meanwhile, when I add the constraints, I got a lot of information regarding relation doesn't exist and skipping as below:


psql:postgres_add_constraints.sql:18: NOTICE:  constraint "admissions_fk_subject_id" of relation "admissions" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:29: NOTICE:  constraint "callout_fk_subject_id" of relation "callout" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:36: NOTICE:  constraint "callout_fk_hadm_id" of relation "callout" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:47: NOTICE:  constraint "chartevents_fk_subject_id" of relation "chartevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:54: NOTICE:  constraint "chartevents_fk_cgid" of relation "chartevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:61: NOTICE:  constraint "chartevents_fk_hadm_id" of relation "chartevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:68: NOTICE:  constraint "chartevents_fk_itemid" of relation "chartevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:75: NOTICE:  constraint "chartevents_fk_icustay_id" of relation "chartevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:86: NOTICE:  constraint "cptevents_fk_subject_id" of relation "cptevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:93: NOTICE:  constraint "cptevents_fk_hadm_id" of relation "cptevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:104: NOTICE:  constraint "datetimeevents_fk_subject_id" of relation "datetimeevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:111: NOTICE:  constraint "datetimeevents_fk_cgid" of relation "datetimeevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:118: NOTICE:  constraint "datetimeevents_fk_hadm_id" of relation "datetimeevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:125: NOTICE:  constraint "datetimeevents_fk_itemid" of relation "datetimeevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:132: NOTICE:  constraint "datetimeevents_fk_icustay_id" of relation "datetimeevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:144: NOTICE:  constraint "diagnoses_icd_fk_subject_id" of relation "diagnoses_icd" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:151: NOTICE:  constraint "diagnoses_icd_fk_hadm_id" of relation "diagnoses_icd" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:169: NOTICE:  constraint "drgcodes_fk_subject_id" of relation "drgcodes" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:176: NOTICE:  constraint "drgcodes_fk_hadm_id" of relation "drgcodes" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:187: NOTICE:  constraint "icustays_fk_subject_id" of relation "icustays" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:194: NOTICE:  constraint "icustays_fk_hadm_id" of relation "icustays" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:206: NOTICE:  constraint "inputevents_cv_fk_subject_id" of relation "inputevents_cv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:213: NOTICE:  constraint "inputevents_cv_fk_hadm_id" of relation "inputevents_cv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:220: NOTICE:  constraint "inputevents_cv_fk_icustay_id" of relation "inputevents_cv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:227: NOTICE:  constraint "inputevents_cv_fk_cgid" of relation "inputevents_cv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:239: NOTICE:  constraint "inputevents_mv_fk_subject_id" of relation "inputevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:246: NOTICE:  constraint "inputevents_mv_fk_hadm_id" of relation "inputevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:253: NOTICE:  constraint "inputevents_mv_fk_icustay_id" of relation "inputevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:259: NOTICE:  constraint "inputevents_mv_fk_itemid" of relation "inputevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:266: NOTICE:  constraint "inputevents_mv_fk_cgid" of relation "inputevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:278: NOTICE:  constraint "labevents_fk_subject_id" of relation "labevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:285: NOTICE:  constraint "labevents_fk_hadm_id" of relation "labevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:292: NOTICE:  constraint "labevents_fk_itemid" of relation "labevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:303: NOTICE:  constraint "microbiologyevents_fk_subject_id" of relation "microbiologyevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:310: NOTICE:  constraint "microbiologyevents_fk_hadm_id" of relation "microbiologyevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:317: NOTICE:  constraint "microbiologyevents_fk_spec_itemid" of relation "microbiologyevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:322: NOTICE:  constraint "microbiologyevents_fk_org_itemid" of relation "microbiologyevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:327: NOTICE:  constraint "microbiologyevents_fk_ab_itemid" of relation "microbiologyevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:338: NOTICE:  constraint "noteevents_fk_subject_id" of relation "noteevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:345: NOTICE:  constraint "noteevents_fk_hadm_id" of relation "noteevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:352: NOTICE:  constraint "noteevents_fk_cgid" of relation "noteevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:364: NOTICE:  constraint "outputevents_subject_id" of relation "outputevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:371: NOTICE:  constraint "outputevents_hadm_id" of relation "outputevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:378: NOTICE:  constraint "outputevents_icustay_id" of relation "outputevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:385: NOTICE:  constraint "outputevents_fk_itemid" of relation "outputevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:392: NOTICE:  constraint "outputevents_cgid" of relation "outputevents" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:405: NOTICE:  constraint "prescriptions_fk_subject_id" of relation "prescriptions" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:412: NOTICE:  constraint "prescriptions_fk_hadm_id" of relation "prescriptions" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:419: NOTICE:  constraint "prescriptions_fk_icustay_id" of relation "prescriptions" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:431: NOTICE:  constraint "procedureevents_mv_fk_subject_id" of relation "procedureevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:438: NOTICE:  constraint "procedureevents_mv_fk_hadm_id" of relation "procedureevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:445: NOTICE:  constraint "procedureevents_mv_fk_icustay_id" of relation "procedureevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:452: NOTICE:  constraint "procedureevents_mv_fk_itemid" of relation "procedureevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:459: NOTICE:  constraint "procedureevents_mv_fk_cgid" of relation "procedureevents_mv" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:471: NOTICE:  constraint "procedures_icd_fk_subject_id" of relation "procedures_icd" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:478: NOTICE:  constraint "procedures_icd_fk_hadm_id" of relation "procedures_icd" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:496: NOTICE:  constraint "services_fk_subject_id" of relation "services" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:503: NOTICE:  constraint "services_fk_hadm_id" of relation "services" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:514: NOTICE:  constraint "transfers_fk_subject_id" of relation "transfers" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:521: NOTICE:  constraint "transfers_fk_hadm_id" of relation "transfers" does not exist, skipping
ALTER TABLE
ALTER TABLE
psql:postgres_add_constraints.sql:528: NOTICE:  constraint "transfers_fk_icustay_id" of relation "transfers" does not exist, skipping
ALTER TABLE

Is that an expected behavior?

alistairewj commented 5 years ago

Yes - the script drops constaints if they exist. If they don't exist (as in your case), it warns you that it didn't drop anything. SchemaSpy uses the constraints to infer relationships which is why nothing showed up before you created them.

MIT-LCP / mimic-code

No relationships were detected in the schema with SchemaSpy #636

Prerequisites

Description