You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
921 lines
46 KiB
SQL
921 lines
46 KiB
SQL
?column?
|
|
-------------------------------------------------------
|
|
CREATE TABLE DiseaseBurden.age_group +
|
|
( +
|
|
id integer NOT NULL, +
|
|
age_group character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.burdens +
|
|
( +
|
|
measure_id integer NOT NULL, +
|
|
location_id integer NOT NULL, +
|
|
sex_id integer NOT NULL, +
|
|
age_id integer NOT NULL, +
|
|
cause_id integer NOT NULL, +
|
|
metric_id integer NOT NULL, +
|
|
year date NOT NULL, +
|
|
val double precision NOT NULL, +
|
|
upper_95 double precision NOT NULL, +
|
|
lower_95 double precision NOT NULL, +
|
|
key_column integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.cause +
|
|
( +
|
|
id integer NOT NULL, +
|
|
cause character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.cause_hierarchy +
|
|
( +
|
|
cause_id integer NOT NULL, +
|
|
cause_name character varying, +
|
|
parent_id integer NOT NULL, +
|
|
parent_nae character varying, +
|
|
level integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.icd10_categories +
|
|
( +
|
|
id integer NOT NULL, +
|
|
start_code character varying NOT NULL, +
|
|
end_code character varying NOT NULL, +
|
|
group_name character varying NOT NULL, +
|
|
level integer NOT NULL, +
|
|
chapter character varying NOT NULL, +
|
|
chapter_code integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.icd10_to_cause +
|
|
( +
|
|
id integer NOT NULL, +
|
|
code character varying NOT NULL, +
|
|
cause_text character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.location +
|
|
( +
|
|
id integer NOT NULL, +
|
|
location character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.measures +
|
|
( +
|
|
id integer NOT NULL, +
|
|
label character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.metric +
|
|
( +
|
|
id integer NOT NULL, +
|
|
metric_label character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.rei +
|
|
( +
|
|
id integer NOT NULL, +
|
|
rei_label character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.sex +
|
|
( +
|
|
id integer NOT NULL, +
|
|
sex character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE DiseaseBurden.trial_to_icd10 +
|
|
( +
|
|
id integer NOT NULL, +
|
|
nct_id character varying NOT NULL, +
|
|
condition character varying NOT NULL, +
|
|
ui character varying, +
|
|
uri character varying, +
|
|
rootsource character varying, +
|
|
name character varying, +
|
|
source character varying, +
|
|
approved USER-DEFINED, +
|
|
approval_timestamp timestamp without time zone +
|
|
); +
|
|
|
|
CREATE TABLE Formularies.usp_dc_2023 +
|
|
( +
|
|
USP Class character varying(250), +
|
|
USP Pharmacotherapeutic Group character varying(250),+
|
|
API Concept character varying(250), +
|
|
rxcui character varying(15), +
|
|
tty character varying(10), +
|
|
Name character varying(256), +
|
|
Related BN character varying(250), +
|
|
Related DF character varying(25050), +
|
|
USP Category character varying(250) +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.active_storage_attachments +
|
|
( +
|
|
id bigint NOT NULL, +
|
|
name character varying NOT NULL, +
|
|
record_type character varying NOT NULL, +
|
|
record_id bigint NOT NULL, +
|
|
blob_id bigint NOT NULL, +
|
|
created_at timestamp without time zone NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.active_storage_blobs +
|
|
( +
|
|
metadata text, +
|
|
checksum character varying NOT NULL, +
|
|
byte_size bigint NOT NULL, +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
id bigint NOT NULL, +
|
|
key character varying NOT NULL, +
|
|
filename character varying NOT NULL, +
|
|
content_type character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.baseline_counts +
|
|
( +
|
|
count integer, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
ctgov_group_code character varying, +
|
|
units character varying, +
|
|
scope character varying, +
|
|
result_group_id integer +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.baseline_measurements +
|
|
( +
|
|
param_value character varying, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
result_group_id integer, +
|
|
ctgov_group_code character varying, +
|
|
classification character varying, +
|
|
category character varying, +
|
|
title character varying, +
|
|
description text, +
|
|
units character varying, +
|
|
param_type character varying, +
|
|
param_value_num numeric, +
|
|
dispersion_type character varying, +
|
|
dispersion_value character varying, +
|
|
dispersion_value_num numeric, +
|
|
dispersion_lower_limit numeric, +
|
|
dispersion_upper_limit numeric, +
|
|
explanation_of_na character varying, +
|
|
number_analyzed integer, +
|
|
number_analyzed_units character varying, +
|
|
population_description character varying, +
|
|
calculate_percentage character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.brief_summaries +
|
|
( +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
description text +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.browse_conditions +
|
|
( +
|
|
mesh_term character varying, +
|
|
id integer NOT NULL, +
|
|
mesh_type character varying, +
|
|
downcase_mesh_term character varying, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.browse_interventions +
|
|
( +
|
|
downcase_mesh_term character varying, +
|
|
mesh_term character varying, +
|
|
mesh_type character varying, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.calculated_values +
|
|
( +
|
|
number_of_secondary_outcomes_to_measure integer, +
|
|
maximum_age_unit character varying, +
|
|
minimum_age_unit character varying, +
|
|
maximum_age_num integer, +
|
|
minimum_age_num integer, +
|
|
has_single_facility boolean, +
|
|
has_us_facility boolean, +
|
|
months_to_report_results integer, +
|
|
number_of_sae_subjects integer, +
|
|
were_results_reported boolean, +
|
|
registered_in_calendar_year integer, +
|
|
nlm_download_date date, +
|
|
actual_duration integer, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
number_of_facilities integer, +
|
|
number_of_nsae_subjects integer, +
|
|
number_of_other_outcomes_to_measure integer, +
|
|
number_of_primary_outcomes_to_measure integer +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.central_contacts +
|
|
( +
|
|
phone_extension character varying, +
|
|
nct_id character varying, +
|
|
role character varying, +
|
|
id integer NOT NULL, +
|
|
contact_type character varying, +
|
|
name character varying, +
|
|
phone character varying, +
|
|
email character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.conditions +
|
|
( +
|
|
downcase_name character varying, +
|
|
name character varying, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.countries +
|
|
( +
|
|
name character varying, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
removed boolean +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.design_group_interventions +
|
|
( +
|
|
id integer NOT NULL, +
|
|
design_group_id integer, +
|
|
intervention_id integer, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.design_groups +
|
|
( +
|
|
group_type character varying, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
title character varying, +
|
|
description text +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.design_outcomes +
|
|
( +
|
|
description text, +
|
|
measure text, +
|
|
outcome_type character varying, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
time_frame text, +
|
|
population character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.designs +
|
|
( +
|
|
masking_description text, +
|
|
subject_masked boolean, +
|
|
caregiver_masked boolean, +
|
|
investigator_masked boolean, +
|
|
outcomes_assessor_masked boolean, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
allocation character varying, +
|
|
intervention_model character varying, +
|
|
observational_model character varying, +
|
|
primary_purpose character varying, +
|
|
time_perspective character varying, +
|
|
masking character varying, +
|
|
intervention_model_description text +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.detailed_descriptions +
|
|
( +
|
|
description text, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.documents +
|
|
( +
|
|
comment text, +
|
|
id integer NOT NULL, +
|
|
url character varying, +
|
|
document_type character varying, +
|
|
nct_id character varying, +
|
|
document_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.drop_withdrawals +
|
|
( +
|
|
period character varying, +
|
|
reason character varying, +
|
|
count integer, +
|
|
ctgov_group_code character varying, +
|
|
result_group_id integer, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.eligibilities +
|
|
( +
|
|
older_adult boolean, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
sampling_method character varying, +
|
|
gender character varying, +
|
|
minimum_age character varying, +
|
|
maximum_age character varying, +
|
|
healthy_volunteers character varying, +
|
|
population text, +
|
|
criteria text, +
|
|
gender_description text, +
|
|
gender_based boolean, +
|
|
adult boolean, +
|
|
child boolean +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.facilities +
|
|
( +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
status character varying, +
|
|
name character varying, +
|
|
city character varying, +
|
|
state character varying, +
|
|
zip character varying, +
|
|
country character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.facility_contacts +
|
|
( +
|
|
contact_type character varying, +
|
|
name character varying, +
|
|
email character varying, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
phone character varying, +
|
|
phone_extension character varying, +
|
|
facility_id integer +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.facility_investigators +
|
|
( +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
facility_id integer, +
|
|
role character varying, +
|
|
name character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.file_records +
|
|
( +
|
|
url character varying, +
|
|
id bigint NOT NULL, +
|
|
filename character varying, +
|
|
file_size bigint, +
|
|
file_type character varying, +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
updated_at timestamp without time zone NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.id_information +
|
|
( +
|
|
id integer NOT NULL, +
|
|
id_source character varying, +
|
|
nct_id character varying, +
|
|
id_link character varying, +
|
|
id_value character varying, +
|
|
id_type_description character varying, +
|
|
id_type character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.intervention_other_names +
|
|
( +
|
|
name character varying, +
|
|
nct_id character varying, +
|
|
intervention_id integer, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.interventions +
|
|
( +
|
|
id integer NOT NULL, +
|
|
name character varying, +
|
|
intervention_type character varying, +
|
|
description text, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.ipd_information_types +
|
|
( +
|
|
name character varying, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.keywords +
|
|
( +
|
|
name character varying, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
downcase_name character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.links +
|
|
( +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
description text, +
|
|
url character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.mesh_headings +
|
|
( +
|
|
qualifier character varying, +
|
|
id integer NOT NULL, +
|
|
subcategory character varying, +
|
|
heading character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.mesh_terms +
|
|
( +
|
|
description character varying, +
|
|
tree_number character varying, +
|
|
qualifier character varying, +
|
|
id integer NOT NULL, +
|
|
downcase_mesh_term character varying, +
|
|
mesh_term character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.milestones +
|
|
( +
|
|
count_units character varying, +
|
|
count integer, +
|
|
description text, +
|
|
period character varying, +
|
|
title character varying, +
|
|
ctgov_group_code character varying, +
|
|
result_group_id integer, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
milestone_description character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.outcome_analyses +
|
|
( +
|
|
other_analysis_description text, +
|
|
param_type character varying, +
|
|
non_inferiority_type character varying, +
|
|
outcome_id integer, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL, +
|
|
param_value numeric, +
|
|
dispersion_type character varying, +
|
|
dispersion_value numeric, +
|
|
p_value_modifier character varying, +
|
|
p_value double precision, +
|
|
ci_n_sides character varying, +
|
|
ci_percent numeric, +
|
|
ci_lower_limit numeric, +
|
|
ci_upper_limit numeric, +
|
|
ci_upper_limit_na_comment character varying, +
|
|
p_value_description character varying, +
|
|
method character varying, +
|
|
method_description text, +
|
|
estimate_description text, +
|
|
groups_description text, +
|
|
non_inferiority_description text +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.outcome_analysis_groups +
|
|
( +
|
|
result_group_id integer, +
|
|
ctgov_group_code character varying, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
outcome_analysis_id integer +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.outcome_counts +
|
|
( +
|
|
result_group_id integer, +
|
|
ctgov_group_code character varying, +
|
|
scope character varying, +
|
|
units character varying, +
|
|
count integer, +
|
|
outcome_id integer, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.outcome_measurements +
|
|
( +
|
|
result_group_id integer, +
|
|
ctgov_group_code character varying, +
|
|
classification character varying, +
|
|
category character varying, +
|
|
title character varying, +
|
|
description text, +
|
|
units character varying, +
|
|
param_type character varying, +
|
|
param_value character varying, +
|
|
param_value_num numeric, +
|
|
dispersion_type character varying, +
|
|
dispersion_value character varying, +
|
|
dispersion_value_num numeric, +
|
|
dispersion_lower_limit numeric, +
|
|
dispersion_upper_limit numeric, +
|
|
explanation_of_na text, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
outcome_id integer +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.outcomes +
|
|
( +
|
|
population text, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
outcome_type character varying, +
|
|
title text, +
|
|
description text, +
|
|
time_frame text, +
|
|
anticipated_posting_date date, +
|
|
anticipated_posting_month_year character varying, +
|
|
units character varying, +
|
|
units_analyzed character varying, +
|
|
dispersion_type character varying, +
|
|
param_type character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.overall_officials +
|
|
( +
|
|
name character varying, +
|
|
affiliation character varying, +
|
|
role character varying, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.participant_flows +
|
|
( +
|
|
count_units integer, +
|
|
nct_id character varying, +
|
|
pre_assignment_details text, +
|
|
units_analyzed character varying, +
|
|
drop_withdraw_comment character varying, +
|
|
reason_comment character varying, +
|
|
recruitment_details text, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.pending_results +
|
|
( +
|
|
event_date_description character varying, +
|
|
event_date date, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
event character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.provided_documents +
|
|
( +
|
|
has_sap boolean, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
document_type character varying, +
|
|
has_protocol boolean, +
|
|
has_icf boolean, +
|
|
document_date date, +
|
|
url character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.reported_event_totals +
|
|
( +
|
|
id integer NOT NULL, +
|
|
updated_at timestamp without time zone NOT NULL, +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
subjects_at_risk integer, +
|
|
subjects_affected integer, +
|
|
classification character varying NOT NULL, +
|
|
event_type character varying, +
|
|
ctgov_group_code character varying NOT NULL, +
|
|
nct_id character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.reported_events +
|
|
( +
|
|
vocab character varying, +
|
|
nct_id character varying, +
|
|
result_group_id integer, +
|
|
ctgov_group_code character varying, +
|
|
time_frame text, +
|
|
event_type character varying, +
|
|
default_vocab character varying, +
|
|
default_assessment character varying, +
|
|
subjects_affected integer, +
|
|
subjects_at_risk integer, +
|
|
description text, +
|
|
event_count integer, +
|
|
organ_system character varying, +
|
|
adverse_event_term character varying, +
|
|
frequency_threshold integer, +
|
|
assessment character varying, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.responsible_parties +
|
|
( +
|
|
affiliation text, +
|
|
nct_id character varying, +
|
|
responsible_party_type character varying, +
|
|
name character varying, +
|
|
title character varying, +
|
|
organization character varying, +
|
|
id integer NOT NULL, +
|
|
old_name_title character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.result_agreements +
|
|
( +
|
|
other_details text, +
|
|
restrictive_agreement character varying, +
|
|
restriction_type character varying, +
|
|
agreement text, +
|
|
pi_employee character varying, +
|
|
nct_id character varying, +
|
|
id integer NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.result_contacts +
|
|
( +
|
|
id integer NOT NULL, +
|
|
organization character varying, +
|
|
name character varying, +
|
|
phone character varying, +
|
|
email character varying, +
|
|
extension character varying, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.result_groups +
|
|
( +
|
|
result_type character varying, +
|
|
title character varying, +
|
|
description text, +
|
|
id integer NOT NULL, +
|
|
nct_id character varying, +
|
|
ctgov_group_code character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.retractions +
|
|
( +
|
|
pmid character varying, +
|
|
id bigint NOT NULL, +
|
|
nct_id character varying, +
|
|
source character varying, +
|
|
reference_id integer +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.search_results +
|
|
( +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
nct_id character varying NOT NULL, +
|
|
id integer NOT NULL, +
|
|
updated_at timestamp without time zone NOT NULL, +
|
|
grouping character varying NOT NULL, +
|
|
study_search_id integer, +
|
|
name character varying NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.sponsors +
|
|
( +
|
|
id integer NOT NULL, +
|
|
name character varying, +
|
|
lead_or_collaborator character varying, +
|
|
agency_class character varying, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.studies +
|
|
( +
|
|
phase character varying, +
|
|
delayed_posting character varying, +
|
|
source_class character varying, +
|
|
updated_at timestamp without time zone NOT NULL, +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
plan_to_share_ipd_description character varying, +
|
|
plan_to_share_ipd character varying, +
|
|
ipd_url character varying, +
|
|
ipd_access_criteria character varying, +
|
|
ipd_time_frame character varying, +
|
|
biospec_description text, +
|
|
biospec_retention character varying, +
|
|
is_us_export boolean, +
|
|
is_ppsd boolean, +
|
|
is_unapproved_device boolean, +
|
|
is_fda_regulated_device boolean, +
|
|
is_fda_regulated_drug boolean, +
|
|
has_dmc boolean, +
|
|
expanded_access_type_treatment boolean, +
|
|
expanded_access_type_intermediate boolean, +
|
|
expanded_access_type_individual boolean, +
|
|
has_expanded_access boolean, +
|
|
why_stopped character varying, +
|
|
number_of_groups integer, +
|
|
number_of_arms integer, +
|
|
limitations_and_caveats character varying, +
|
|
source character varying, +
|
|
enrollment_type character varying, +
|
|
enrollment integer, +
|
|
expanded_access_nctid character varying, +
|
|
last_known_status character varying, +
|
|
overall_status character varying, +
|
|
official_title text, +
|
|
brief_title text, +
|
|
baseline_population text, +
|
|
acronym character varying, +
|
|
study_type character varying, +
|
|
target_duration character varying, +
|
|
results_first_submitted_date date, +
|
|
study_first_submitted_date date, +
|
|
nlm_download_date_description character varying, +
|
|
primary_completion_date date, +
|
|
nct_id character varying, +
|
|
primary_completion_date_type character varying, +
|
|
primary_completion_month_year character varying, +
|
|
completion_date date, +
|
|
completion_date_type character varying, +
|
|
completion_month_year character varying, +
|
|
verification_date date, +
|
|
verification_month_year character varying, +
|
|
start_date date, +
|
|
start_date_type character varying, +
|
|
start_month_year character varying, +
|
|
last_update_posted_date_type character varying, +
|
|
last_update_posted_date date, +
|
|
last_update_submitted_qc_date date, +
|
|
disposition_first_posted_date_type character varying,+
|
|
disposition_first_posted_date date, +
|
|
disposition_first_submitted_qc_date date, +
|
|
results_first_posted_date_type character varying, +
|
|
results_first_posted_date date, +
|
|
results_first_submitted_qc_date date, +
|
|
study_first_posted_date_type character varying, +
|
|
study_first_posted_date date, +
|
|
study_first_submitted_qc_date date, +
|
|
last_update_submitted_date date, +
|
|
disposition_first_submitted_date date, +
|
|
baseline_type_units_analyzed character varying, +
|
|
fdaaa801_violation boolean, +
|
|
expanded_access_status_for_nctid character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.study_records +
|
|
( +
|
|
nct_id character varying, +
|
|
sha character varying, +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
updated_at timestamp without time zone NOT NULL, +
|
|
type character varying, +
|
|
content json, +
|
|
id bigint NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.study_references +
|
|
( +
|
|
id integer NOT NULL, +
|
|
citation text, +
|
|
reference_type character varying, +
|
|
pmid character varying, +
|
|
nct_id character varying +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.study_searches +
|
|
( +
|
|
query character varying NOT NULL, +
|
|
id integer NOT NULL, +
|
|
updated_at timestamp without time zone NOT NULL, +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
beta_api boolean NOT NULL, +
|
|
name character varying NOT NULL, +
|
|
grouping character varying NOT NULL, +
|
|
save_tsv boolean NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE ctgov.verifiers +
|
|
( +
|
|
id bigint NOT NULL, +
|
|
created_at timestamp without time zone NOT NULL, +
|
|
source json, +
|
|
updated_at timestamp without time zone NOT NULL, +
|
|
load_event_id integer, +
|
|
last_run timestamp without time zone, +
|
|
differences json NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE history.trial_snapshots +
|
|
( +
|
|
completion_date timestamp without time zone, +
|
|
nct_id character varying(15) NOT NULL, +
|
|
version integer NOT NULL, +
|
|
submission_date timestamp without time zone, +
|
|
primary_completion_date timestamp without time zone, +
|
|
primary_completion_date_category USER-DEFINED, +
|
|
start_date timestamp without time zone, +
|
|
start_date_category USER-DEFINED, +
|
|
completion_date_category USER-DEFINED, +
|
|
overall_status USER-DEFINED, +
|
|
enrollment integer, +
|
|
enrollment_category USER-DEFINED, +
|
|
sponsor character varying, +
|
|
responsible_party character varying +
|
|
); +
|
|
|
|
CREATE TABLE http.download_status +
|
|
( +
|
|
status USER-DEFINED NOT NULL, +
|
|
nct_id character varying(15) NOT NULL, +
|
|
id integer NOT NULL, +
|
|
update_timestamp timestamp with time zone +
|
|
); +
|
|
|
|
CREATE TABLE http.responses +
|
|
( +
|
|
nct_id character varying(15), +
|
|
version_a smallint, +
|
|
version_b smallint, +
|
|
url character varying(255), +
|
|
response_code smallint, +
|
|
response_date timestamp with time zone, +
|
|
id integer NOT NULL, +
|
|
html text +
|
|
); +
|
|
|
|
CREATE TABLE rxnorm_migrated.ALLNDC_HISTORY +
|
|
( +
|
|
sab character varying(10), +
|
|
ndc11_left9 character(9) NOT NULL, +
|
|
rowid integer NOT NULL, +
|
|
ndc character(13) NOT NULL, +
|
|
suppress character(1), +
|
|
edate character(6), +
|
|
sdate character(6), +
|
|
rxcui character(16) +
|
|
); +
|
|
|
|
CREATE TABLE rxnorm_migrated.ALLRXCUI_HISTORY +
|
|
( +
|
|
tty character varying(5), +
|
|
sts character(1), +
|
|
rxindb character(1), +
|
|
indb character(1), +
|
|
rowid integer NOT NULL, +
|
|
rxcui character(16) NOT NULL, +
|
|
sab character varying(20), +
|
|
str character varying(3000), +
|
|
sdate character(6), +
|
|
edate character(6) +
|
|
); +
|
|
|
|
CREATE TABLE rxnorm_migrated.rxnorm_props +
|
|
( +
|
|
rxcui character(8) NOT NULL, +
|
|
pres smallint NOT NULL, +
|
|
propvalue1 character varying(4000) NOT NULL, +
|
|
propname character varying(30) NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE rxnorm_migrated.rxnorm_relations +
|
|
( +
|
|
tty2 character(4) NOT NULL, +
|
|
rxcui1 character(8) NOT NULL, +
|
|
tty1 character(4) NOT NULL, +
|
|
cvf character(4) NOT NULL, +
|
|
rxcui2 character(8) NOT NULL +
|
|
); +
|
|
|
|
CREATE TABLE spl.nsde +
|
|
( +
|
|
proprietary_name character varying(500), +
|
|
package_ndc character varying(50), +
|
|
application_number_or_citation character varying(25),+
|
|
package_ndc11 character varying(11), +
|
|
id integer NOT NULL, +
|
|
reactivation_date date, +
|
|
inactivation_date date, +
|
|
marketing_start_date date, +
|
|
marketing_end_date date, +
|
|
billing_unit character varying(35), +
|
|
dosage_form character varying(155), +
|
|
marketing_category character varying(160), +
|
|
product_type character varying(90) +
|
|
); +
|
|
|
|
(76 rows)
|
|
|