Ongoing - Document existing database model

[jwchumley]

Ongoing - Document existing database model to strategize future materialized view work.

[vrajmohan]

IIRC, @fecjjeng created an update diagram of the materialized view dependencies. Assigning this issue to @fecjjeng to publish the document (perhaps in the Wiki) and provide a link to it in this issue.

[JonellaCulmer]

@rjayasekera

[rjayasekera]

We use graphviz to generate database object model diagram

1. Install graphviz by running brew install graphviz
2. 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;

3. 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";
..
..
"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";            
}

4. 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)

[rjayasekera]

@lbeaufort please take a look at the wiki link
https://github.com/fecgov/openFEC/wiki/Create-Database-model-diagram

[AmyKort]

With #3165 closed, this shouldn't be blocked. Can I move it to Ready?

[wjiangFEC]

Amy, In last grooming, I asked to have this ticket closed. Please closed this ticket (3188). Vraj has generated diagrams for current MVs. I don't think we should ask DBA or developers to spend more time to do more database document at this moment. We will need to have a longer time plan to redesign the MVs in the future. Regards, Wenchun Jiang On Jul 6, 2018, at 1:01 PM, Amy Kort <[email protected]<mailto:[email protected]>> wrote: With fecgov#3165<#3165> closed, this shouldn't be blocked. Can I move it to Ready? — You are receiving this because you were assigned. Reply to this email directly, view it on GitHub<#3188 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/Ac60cPn3qJPdrSi0RYDW19GuHQkRYwOvks5uD5fZgaJpZM4UbFdE>.

[AmyKort]

Closing while we look into a longer term plan.