library(RPostgres)
library(tidyverse)

## ── Attaching packages ────────────────── tidyverse 1.3.0.9000 ──

## ✓ ggplot2 3.2.1            ✓ purrr   0.3.3.9000  
## ✓ tibble  2.99.99.9014     ✓ dplyr   0.8.4       
## ✓ tidyr   1.0.2            ✓ stringr 1.4.0       
## ✓ readr   1.3.1.9000       ✓ forcats 0.4.0

## ── Conflicts ────────────────────────── tidyverse_conflicts() ──
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()

library(dm)

## 
## Attaching package: 'dm'

## The following object is masked from 'package:stats':
## 
##     filter

user <- keyring::key_get("aact-db-user")
password <- keyring::key_get("aact-db-password", user = user)

con <- dbConnect(
  Postgres(),
  dbname="aact",
  host="aact-db.ctti-clinicaltrials.org",
  port=5432, user = user, password = password
)

dm <- dm::dm_from_src(con, schema = "ctgov")

dm_fk <-
  dm %>%
  dm_add_pk(baseline_counts, id) %>%
  dm_add_pk(baseline_measurements, id) %>%
  dm_add_pk(brief_summaries, id) %>%
  dm_add_pk(browse_conditions, id) %>%
  dm_add_pk(browse_interventions, id) %>%
  dm_add_pk(calculated_values, id) %>%
  dm_add_pk(central_contacts, id) %>%
  dm_add_pk(conditions, id) %>%
  dm_add_pk(countries, id) %>%
  dm_add_pk(design_group_interventions, id) %>%
  dm_add_pk(design_groups, id) %>%
  dm_add_pk(design_outcomes, id) %>%
  dm_add_pk(designs, id) %>%
  dm_add_pk(detailed_descriptions, id) %>%
  dm_add_pk(documents, id) %>%
  dm_add_pk(drop_withdrawals, id) %>%
  dm_add_pk(eligibilities, id) %>%
  dm_add_pk(facilities, id) %>%
  dm_add_pk(facility_contacts, id) %>%
  dm_add_pk(facility_investigators, id) %>%
  dm_add_pk(id_information, id) %>%
  dm_add_pk(intervention_other_names, id) %>%
  dm_add_pk(interventions, id) %>%
  dm_add_pk(ipd_information_types, id) %>%
  dm_add_pk(keywords, id) %>%
  dm_add_pk(links, id) %>%
  dm_add_pk(mesh_headings, id) %>%
  dm_add_pk(mesh_terms, id) %>%
  dm_add_pk(milestones, id) %>%
  dm_add_pk(outcome_analyses, id) %>%
  dm_add_pk(outcome_analysis_groups, id) %>%
  dm_add_pk(outcome_counts, id) %>%
  dm_add_pk(outcome_measurements, id) %>%
  dm_add_pk(outcomes, id) %>%
  dm_add_pk(overall_officials, id) %>%
  dm_add_pk(participant_flows, id) %>%
  dm_add_pk(pending_results, id) %>%
  dm_add_pk(provided_documents, id) %>%
  dm_add_pk(reported_events, id) %>%
  dm_add_pk(responsible_parties, id) %>%
  dm_add_pk(result_agreements, id) %>%
  dm_add_pk(result_contacts, id) %>%
  dm_add_pk(result_groups, id) %>%
  dm_add_pk(sponsors, id) %>%
  dm_add_pk(study_references, id) %>%
  dm_add_pk(studies, nct_id) %>%
  dm_add_fk(baseline_counts, nct_id, studies) %>%
  dm_add_fk(baseline_counts, result_group_id, result_groups) %>%
  dm_add_fk(baseline_measurements, nct_id, studies) %>%
  dm_add_fk(baseline_measurements, result_group_id, result_groups) %>%
  dm_add_fk(brief_summaries, nct_id, studies) %>%
  dm_add_fk(browse_conditions, nct_id, studies) %>%
  dm_add_fk(browse_interventions, nct_id, studies) %>%
  dm_add_fk(calculated_values, nct_id, studies) %>%
  dm_add_fk(central_contacts, nct_id, studies) %>%
  dm_add_fk(conditions, nct_id, studies) %>%
  dm_add_fk(countries, nct_id, studies) %>%
  dm_add_fk(design_group_interventions, nct_id, studies) %>%
  dm_add_fk(design_group_interventions, design_group_id, design_groups) %>%
  dm_add_fk(design_group_interventions, intervention_id, interventions) %>%
  dm_add_fk(design_groups, nct_id, studies) %>%
  dm_add_fk(design_outcomes, nct_id, studies) %>%
  dm_add_fk(designs, nct_id, studies) %>%
  dm_add_fk(detailed_descriptions, nct_id, studies) %>%
  dm_add_fk(documents, nct_id, studies) %>%
  dm_add_fk(drop_withdrawals, nct_id, studies) %>%
  dm_add_fk(drop_withdrawals, result_group_id, result_groups) %>%
  dm_add_fk(eligibilities, nct_id, studies) %>%
  dm_add_fk(facilities, nct_id, studies) %>%
  dm_add_fk(facility_contacts, nct_id, studies) %>%
  dm_add_fk(facility_contacts, facility_id, facilities) %>%
  dm_add_fk(facility_investigators, nct_id, studies) %>%
  dm_add_fk(facility_investigators, facility_id, facilities) %>%
  dm_add_fk(id_information, nct_id, studies) %>%
  dm_add_fk(intervention_other_names, nct_id, studies) %>%
  dm_add_fk(intervention_other_names, intervention_id, interventions) %>%
  dm_add_fk(interventions, nct_id, studies) %>%
  dm_add_fk(ipd_information_types, nct_id, studies) %>%
  dm_add_fk(keywords, nct_id, studies) %>%
  dm_add_fk(links, nct_id, studies) %>%
  dm_add_fk(milestones, nct_id, studies) %>%
  dm_add_fk(milestones, result_group_id, result_groups) %>%
  dm_add_fk(outcome_analyses, nct_id, studies) %>%
  dm_add_fk(outcome_analyses, outcome_id, outcomes) %>%
  dm_add_fk(outcome_analysis_groups, nct_id, studies) %>%
  dm_add_fk(outcome_analysis_groups, outcome_analysis_id, outcome_analyses) %>%
  dm_add_fk(outcome_analysis_groups, result_group_id, result_groups) %>%
  dm_add_fk(outcome_counts, nct_id, studies) %>%
  dm_add_fk(outcome_counts, outcome_id, outcomes) %>%
  dm_add_fk(outcome_counts, result_group_id, result_groups) %>%
  dm_add_fk(outcome_measurements, nct_id, studies) %>%
  dm_add_fk(outcome_measurements, outcome_id, outcomes) %>%
  dm_add_fk(outcome_measurements, result_group_id, result_groups) %>%
  dm_add_fk(outcomes, nct_id, studies) %>%
  dm_add_fk(overall_officials, nct_id, studies) %>%
  dm_add_fk(participant_flows, nct_id, studies) %>%
  dm_add_fk(pending_results, nct_id, studies) %>%
  dm_add_fk(provided_documents, nct_id, studies) %>%
  dm_add_fk(reported_events, nct_id, studies) %>%
  dm_add_fk(reported_events, result_group_id, result_groups) %>%
  dm_add_fk(responsible_parties, nct_id, studies) %>%
  dm_add_fk(result_agreements, nct_id, studies) %>%
  dm_add_fk(result_contacts, nct_id, studies) %>%
  dm_add_fk(result_groups, nct_id, studies) %>%
  dm_add_fk(sponsors, nct_id, studies) %>%
  dm_add_fk(studies, nct_id, studies) %>%
  dm_add_fk(study_references, nct_id, studies)

dm_fk %>%
  dm_examine_constraints()

## ℹ All constraints satisfied.

dm_fk %>%
  dm_draw()