-
Notifications
You must be signed in to change notification settings - Fork 106
Create Database model diagram
Laura Beaufort edited this page Sep 9, 2020
·
2 revisions
We use graphviz to generate database object model diagram
- Install graphviz by running brew install graphviz
- Run following query to to generate object dependencies
SELECT
'"'||source_ns.nspname||'.'||source_table.relname||'" -> "'||dependent_ns.nspname||'.'||dependent_view.relname||'";'
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
WHERE
--
dependent_view.relname like '%_mv' and
-- dependent_view.relname IN ('list_specific_tables_here') and
--
dependent_ns.nspname||dependent_view.relname != source_ns.nspname||source_table.relname
group by dependent_ns.nspname
, dependent_view.relname
, source_ns.nspname
, source_table.relname
order by source_ns.nspname||'.'||source_table.relname, dependent_ns.nspname||'.'||dependent_view.relname;
- Copy and paste the output of the query in to a file {name}.dot and surround the query text with graphviz tags and save.
digraph {
rankdir=LR; #TB UD LR RL
node [shape="box"];
"auditsearch.audit_case" -> "public.ofec_audit_case_json_test_mv";
"auditsearch.audit_case" -> "public.ofec_audit_case_mv";
"auditsearch.audit_case" -> "public.ofec_candidate_fulltext_audit_mv";
"auditsearch.audit_case" -> "public.ofec_committee_fulltext_audit_mv";
"auditsearch.audit_case_finding" -> "public.ofec_audit_case_category_rel_mv";
"auditsearch.audit_case_finding" -> "public.ofec_audit_case_json_test_mv";
"auditsearch.audit_case_finding" -> "public.ofec_audit_case_mv";
"auditsearch.audit_case_finding" -> "public.ofec_audit_case_sub_category_rel_mv";
"auditsearch.finding" -> "public.ofec_audit_case_category_rel_mv";
"auditsearch.finding" -> "public.ofec_audit_case_json_test_mv";
"auditsearch.finding" -> "public.ofec_audit_case_sub_category_rel_mv";
"disclosure.cand_cmte_linkage" -> "public.ofec_cand_cmte_linkage_mv";
"disclosure.cand_cmte_linkage" -> "public.ofec_candidate_fulltext_mv";
"disclosure.cand_cmte_linkage" -> "public.ofec_candidate_history_mv";
"disclosure.cand_cmte_linkage" -> "public.ofec_committee_history_mv";
"disclosure.cand_cmte_linkage" -> "public.ofec_totals_house_senate_mv";
"disclosure.cand_inactive" -> "public.ofec_candidate_history_mv";
"disclosure.cand_valid_fec_yr" -> "public.ofec_audit_case_mv";
"disclosure.cand_valid_fec_yr" -> "public.ofec_candidate_fulltext_audit_mv";
"disclosure.cand_valid_fec_yr" -> "public.ofec_candidate_history_mv";
"disclosure.cand_valid_fec_yr" -> "public.ofec_election_result_mv";
"disclosure.cand_valid_fec_yr" -> "public.ofec_elections_list_mv";
"disclosure.cmte_valid_fec_yr" -> "public.ofec_audit_case_mv";
"disclosure.cmte_valid_fec_yr" -> "public.ofec_committee_fulltext_audit_mv";
"disclosure.cmte_valid_fec_yr" -> "public.ofec_committee_history_mv";
"disclosure.cmte_valid_fec_yr" -> "public.ofec_filings_all_mv";
"disclosure.cmte_valid_fec_yr" -> "public.ofec_report_pac_party_all_mv";
"disclosure.cmte_valid_fec_yr" -> "public.ofec_totals_combined_mv";
"disclosure.dim_race_inf" -> "public.ofec_elections_list_mv";
"disclosure.dsc_sched_a_aggregate_size" -> "public.ofec_sched_a_aggregate_size_merged_mv";
"disclosure.dsc_sched_a_aggregate_state" -> "public.ofec_sched_a_aggregate_state_recipient_totals_mv";
"disclosure.f_rpt_or_form_sub" -> "public.ofec_amendments_mv";
"disclosure.f_rpt_or_form_sub" -> "public.ofec_candidate_history_mv";
"disclosure.f_rpt_or_form_sub" -> "public.ofec_committee_history_mv";
"disclosure.f_rpt_or_form_sub" -> "public.ofec_filings_all_mv";
"disclosure.f_rpt_or_form_sub" -> "public.ofec_filings_mv";
"disclosure.f_rpt_or_form_sub" -> "public.ofec_operations_log_mv";
"disclosure.f_rpt_or_form_sub" -> "public.ofec_report_pac_party_all_mv";
"disclosure.nml_form_3" -> "public.ofec_house_senate_electronic_amendments_mv";
"disclosure.nml_form_3" -> "public.ofec_house_senate_paper_amendments_mv";
"disclosure.nml_form_3p" -> "public.ofec_presidential_electronic_amendments_mv";
"disclosure.nml_form_3p" -> "public.ofec_presidential_paper_amendments_mv";
"disclosure.nml_form_3x" -> "public.ofec_pac_party_electronic_amendments_mv";
"disclosure.nml_form_3x" -> "public.ofec_pac_party_paper_amendments_mv";
"disclosure.nml_form_rfai" -> "public.ofec_filings_all_mv";
"disclosure.nml_form_rfai" -> "public.ofec_filings_mv";
"disclosure.rad_cmte_analyst_search_vw" -> "public.ofec_rad_mv";
"disclosure.v_sum_and_det_sum_report" -> "public.ofec_agg_coverage_date_mv";
"disclosure.v_sum_and_det_sum_report" -> "public.ofec_report_pac_party_all_mv";
"disclosure.v_sum_and_det_sum_report" -> "public.ofec_reports_f3_mv";
"disclosure.v_sum_and_det_sum_report" -> "public.ofec_totals_candidate_committees_mv";
"disclosure.v_sum_and_det_sum_report" -> "public.ofec_totals_combined_mv";
"fecapp.trc_election" -> "public.ofec_election_result_mv";
"public.electioneering_com_vw" -> "public.ofec_electioneering_aggregate_candidate_mv";
"public.electioneering_com_vw" -> "public.ofec_electioneering_mv";
"public.fec_fitem_f57_vw" -> "public.ofec_sched_e_aggregate_candidate_mv";
"public.fec_fitem_f76_vw" -> "public.ofec_communication_cost_aggregate_candidate_mv";
"public.fec_fitem_f76_vw" -> "public.ofec_communication_cost_mv";
"public.fec_fitem_sched_c_vw" -> "public.ofec_sched_c_mv";
"public.fec_fitem_sched_e_vw" -> "public.ofec_sched_e_aggregate_candidate_mv";
"public.fec_fitem_sched_f_vw" -> "public.ofec_sched_f_mv";
"public.fec_vsum_f1_vw" -> "public.ofec_committee_history_mv";
"public.fec_vsum_f3p_vw" -> "public.ofec_reports_presidential_mv";
"public.fec_vsum_f3_vw" -> "public.ofec_reports_house_senate_mv";
"public.fec_vsum_f3x_vw" -> "public.ofec_report_pac_party_all_mv";
"public.fec_vsum_f3x_vw" -> "public.ofec_reports_pacs_parties_mv";
"public.fec_vsum_f5_vw" -> "public.ofec_reports_ie_only_mv";
"public.fec_vsum_f5_vw" -> "public.ofec_sched_e_aggregate_candidate_mv";
"public.ofec_amendments_mv" -> "public.ofec_filings_amendments_all_mv";
"public.ofec_amendments_mv" -> "public.ofec_reports_house_senate_mv";
"public.ofec_amendments_mv" -> "public.ofec_reports_pacs_parties_mv";
"public.ofec_amendments_mv" -> "public.ofec_reports_presidential_mv";
"public.ofec_cand_cmte_linkage_mv" -> "public.ofec_candidate_totals_mv";
"public.ofec_cand_cmte_linkage_mv" -> "public.ofec_totals_candidate_committees_mv";
"public.ofec_candidate_detail_mv" -> "public.ofec_candidate_election_mv";
"public.ofec_candidate_detail_mv" -> "public.ofec_candidate_fulltext_mv";
"public.ofec_candidate_election_mv" -> "public.ofec_totals_candidate_committees_mv";
"public.ofec_candidate_history_mv" -> "public.ofec_candidate_detail_mv";
"public.ofec_candidate_history_mv" -> "public.ofec_candidate_flag_mv";
"public.ofec_candidate_history_mv" -> "public.ofec_candidate_history_with_future_election_mv";
"public.ofec_candidate_history_mv" -> "public.ofec_filings_all_mv";
"public.ofec_candidate_history_mv" -> "public.ofec_filings_mv";
"public.ofec_candidate_history_with_future_election_mv" -> "public.ofec_candidate_totals_mv";
"public.ofec_candidate_totals_mv" -> "public.ofec_candidate_flag_mv";
"public.ofec_committee_detail_mv" -> "public.ofec_committee_fulltext_mv";
"public.ofec_committee_detail_mv" -> "public.ofec_sched_a_aggregate_state_recipient_totals_mv";
"public.ofec_committee_detail_mv" -> "public.ofec_totals_pacs_parties_mv";
"public.ofec_committee_history_mv" -> "public.ofec_committee_detail_mv";
"public.ofec_committee_history_mv" -> "public.ofec_communication_cost_mv";
"public.ofec_committee_history_mv" -> "public.ofec_filings_all_mv";
"public.ofec_committee_history_mv" -> "public.ofec_filings_mv";
"public.ofec_filings_amendments_all_mv" -> "public.ofec_filings_all_mv";
"public.ofec_filings_amendments_all_mv" -> "public.ofec_filings_mv";
"public.ofec_filings_amendments_all_mv" -> "public.ofec_report_pac_party_all_mv";
"public.ofec_filings_mv" -> "public.ofec_totals_candidate_committees_mv";
"public.ofec_filings_mv" -> "public.ofec_totals_combined_mv";
"public.ofec_house_senate_paper_amendments_mv" -> "public.ofec_filings_amendments_all_mv";
"public.ofec_house_senate_paper_amendments_mv" -> "public.ofec_reports_house_senate_mv";
"public.ofec_nicknames" -> "public.ofec_candidate_fulltext_mv";
"public.ofec_pac_party_paper_amendments_mv" -> "public.ofec_filings_amendments_all_mv";
"public.ofec_pac_party_paper_amendments_mv" -> "public.ofec_reports_pacs_parties_mv";
"public.ofec_pacronyms" -> "public.ofec_committee_fulltext_mv";
"public.ofec_presidential_paper_amendments_mv" -> "public.ofec_filings_amendments_all_mv";
"public.ofec_presidential_paper_amendments_mv" -> "public.ofec_reports_presidential_mv";
"public.ofec_totals_combined_mv" -> "public.ofec_candidate_fulltext_mv";
"public.ofec_totals_combined_mv" -> "public.ofec_committee_fulltext_mv";
"public.ofec_totals_combined_mv" -> "public.ofec_sched_a_aggregate_size_merged_mv";
"public.ofec_totals_combined_mv" -> "public.ofec_totals_house_senate_mv";
"public.ofec_totals_combined_mv" -> "public.ofec_totals_ie_only_mv";
"public.ofec_totals_combined_mv" -> "public.ofec_totals_pacs_parties_mv";
"public.ofec_totals_combined_mv" -> "public.ofec_totals_presidential_mv";
"public.ofec_totals_house_senate_mv" -> "public.ofec_candidate_totals_mv";
"public.ofec_totals_house_senate_mv" -> "public.ofec_entity_chart_mv";
"public.ofec_totals_pacs_mv" -> "public.ofec_entity_chart_mv";
"public.ofec_totals_pacs_parties_mv" -> "public.ofec_totals_pacs_mv";
"public.ofec_totals_pacs_parties_mv" -> "public.ofec_totals_parties_mv";
"public.ofec_totals_parties_mv" -> "public.ofec_entity_chart_mv";
"public.ofec_totals_presidential_mv" -> "public.ofec_candidate_totals_mv";
"public.unverified_filers_vw" -> "public.ofec_candidate_history_mv";
"public.unverified_filers_vw" -> "public.ofec_committee_history_mv";
"staging.operations_log" -> "public.ofec_agg_coverage_date_mv";
"staging.operations_log" -> "public.ofec_operations_log_mv";
"staging.ref_filed_cmte_tp" -> "public.ofec_audit_case_mv";
"staging.ref_filed_cmte_tp" -> "public.ofec_committee_fulltext_audit_mv";
"staging.ref_pty" -> "public.ofec_candidate_history_mv";
"staging.ref_rpt_tp" -> "public.ofec_filings_all_mv";
"staging.ref_rpt_tp" -> "public.ofec_filings_mv";
"staging.ref_rpt_tp" -> "public.ofec_report_pac_party_all_mv";
"staging.ref_zip_to_district" -> "public.ofec_sched_a_aggregate_state_recipient_totals_mv";
}
- Run following statement from the directory where you save the above {name}.dot file:
dot -Tsvg -o {name}.svg {name}.dot
(Change the output file type by changing -Tsvg to -Tpdf)