Core table creation code
Jump to navigation
Jump to search
The code below is called to create the mdr core schema database tables. The connection string for the database (in this case for mdr) is injected into the class constructor. Any existing tables are first dropped by repeated calls to the drop_table function. Then each create_table function is called in turn, which simply executes the create table / create index statements. Note that for the core schema the ids are copied from the corresponding table in the st, ob or nk schemas - they are not generated as identities.
public class CoreTableBuilder
{
string db_conn;
public CoreTableBuilder(string _db_conn)
{
db_conn = _db_conn;
}
public void drop_table(string table_name)
{
string sql_string = @"DROP TABLE IF EXISTS core." + table_name;
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_studies()
{
string sql_string = @"CREATE TABLE core.studies(
id INT NOT NULL PRIMARY KEY
, display_title VARCHAR NULL
, title_lang_code VARCHAR NULL
, brief_description VARCHAR NULL
, bd_contains_html BOOLEAN NULL
, data_sharing_statement VARCHAR NULL
, dss_contains_html BOOLEAN NULL
, study_start_year INT NULL
, study_start_month INT NULL
, study_type_id INT NULL
, study_status_id INT NULL
, study_enrolment INT NULL
, study_gender_elig_id INT NULL
, min_age INT NULL
, min_age_units_id INT NULL
, max_age INT NULL
, max_age_units_id INT NULL
, provenance_string VARCHAR NULL
);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_identifiers()
{
string sql_string = @"CREATE TABLE core.study_identifiers(
id INT NOT NULL PRIMARY KEY
, study_id INT NOT NULL
, identifier_value VARCHAR NULL
, identifier_type_id INT NULL
, identifier_org_id INT NULL
, identifier_org VARCHAR NULL
, identifier_date VARCHAR NULL
, identifier_link VARCHAR NULL
);
CREATE INDEX study_identifiers_study_id ON core.study_identifiers(study_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_titles()
{
string sql_string = @"CREATE TABLE core.study_titles(
id INT NOT NULL PRIMARY KEY
, study_id INT NOT NULL
, title_type_id INT NULL
, title_text VARCHAR NULL
, lang_code VARCHAR NULL
, lang_usage_id INT NULL
, is_default BOOLEAN NULL
, comments VARCHAR NULL
);
CREATE INDEX study_titles_study_id ON core.study_titles(study_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_contributors()
{
string sql_string = @"CREATE TABLE core.study_contributors(
id INT NOT NULL PRIMARY KEY
, study_id INT NOT NULL
, contrib_type_id INT NULL
, is_individual BOOLEAN NULL
, organisation_id INT NULL
, organisation_name VARCHAR NULL
, person_id INT NULL
, person_given_name VARCHAR NULL
, person_family_name VARCHAR NULL
, person_full_name VARCHAR NULL
, person_identifier VARCHAR NULL
, identifier_type VARCHAR NULL
, person_affiliation VARCHAR NULL
, affil_org_id VARCHAR NULL
, affil_org_id_type VARCHAR NULL
);
CREATE INDEX study_contributors_study_id ON core.study_contributors(study_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_topics()
{
string sql_string = @"CREATE TABLE core.study_topics(
id INT NOT NULL PRIMARY KEY
, study_id INT NOT NULL
, topic_type_id INT NULL
, mesh_coded BOOLEAN NULL
, topic_code VARCHAR NULL
, topic_value VARCHAR NULL
, topic_qualcode VARCHAR NULL
, topic_qualvalue VARCHAR NULL
, original_ct_id INT NULL
, original_ct_code VARCHAR NULL
, original_value VARCHAR NULL
, comments VARCHAR NULL
);
CREATE INDEX study_topics_study_id ON core.study_topics(study_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_features()
{
string sql_string = @"CREATE TABLE core.study_features(
id INT NOT NULL PRIMARY KEY
, study_id INT NOT NULL
, feature_type_id INT NULL
, feature_value_id INT NULL
);
CREATE INDEX study_features_study_id ON core.study_features(study_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_relationships()
{
string sql_string = @"CREATE TABLE core.study_relationships(
id INT NOT NULL PRIMARY KEY
, study_id INT NOT NULL
, relationship_type_id INT NULL
, target_study_id VARCHAR NULL
);
CREATE INDEX study_relationships_study_id ON core.study_relationships(study_id);
CREATE INDEX study_relationships_target_study_id ON core.study_relationships(target_study_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_data_objects()
{
string sql_string = @"CREATE TABLE core.data_objects(
id INT NOT NULL PRIMARY KEY
, display_title VARCHAR NULL
, version VARCHAR NULL
, doi VARCHAR NULL
, doi_status_id INT NULL
, publication_year INT NULL
, object_class_id INT NULL
, object_type_id INT NULL
, managing_org_id INT NULL
, managing_org VARCHAR NULL
, lang_code VARCHAR NULL
, access_type_id INT NULL
, access_details VARCHAR NULL
, access_details_url VARCHAR NULL
, url_last_checked DATE NULL
, eosc_category INT NULL
, add_study_contribs BOOLEAN NULL
, add_study_topics BOOLEAN NULL
, provenance_string VARCHAR NULL
);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_datasets()
{
string sql_string = @"CREATE TABLE core.object_datasets(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, record_keys_type_id INT NULL
, record_keys_details VARCHAR NULL
, deident_type_id INT NULL
, deident_direct BOOLEAN NULL
, deident_hipaa BOOLEAN NULL
, deident_dates BOOLEAN NULL
, deident_nonarr BOOLEAN NULL
, deident_kanon BOOLEAN NULL
, deident_details VARCHAR NULL
, consent_type_id INT NULL
, consent_noncommercial BOOLEAN NULL
, consent_geog_restrict BOOLEAN NULL
, consent_research_type BOOLEAN NULL
, consent_genetic_only BOOLEAN NULL
, consent_no_methods BOOLEAN NULL
, consent_details VARCHAR NULL
);
CREATE INDEX object_datasets_object_id ON core.object_datasets(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_dates()
{
string sql_string = @"CREATE TABLE core.object_dates(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, date_type_id INT NULL
, is_date_range BOOLEAN NULL
, date_as_string VARCHAR NULL
, start_year INT NULL
, start_month INT NULL
, start_day INT NULL
, end_year INT NULL
, end_month INT NULL
, end_day INT NULL
, details VARCHAR NULL
);
CREATE INDEX object_dates_object_id ON core.object_dates(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_instances()
{
string sql_string = @"CREATE TABLE core.object_instances(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, instance_type_id INT NULL
, repository_org_id INT NULL
, repository_org VARCHAR NULL
, url VARCHAR NULL
, url_accessible BOOLEAN NULL
, url_last_checked DATE NULL
, resource_type_id INT NULL
, resource_size VARCHAR NULL
, resource_size_units VARCHAR NULL
, resource_comments VARCHAR NULL
);
CREATE INDEX object_instances_object_id ON core.object_instances(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_contributors()
{
string sql_string = @"CREATE TABLE core.object_contributors(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, contrib_type_id INT NULL
, is_individual BOOLEAN NULL
, organisation_id INT NULL
, organisation_name VARCHAR NULL
, person_id INT NULL
, person_given_name VARCHAR NULL
, person_family_name VARCHAR NULL
, person_full_name VARCHAR NULL
, person_identifier VARCHAR NULL
, identifier_type VARCHAR NULL
, person_affiliation VARCHAR NULL
, affil_org_id VARCHAR NULL
, affil_org_id_type VARCHAR NULL
);
CREATE INDEX object_contributors_object_id ON core.object_contributors(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_titles()
{
string sql_string = @"CREATE TABLE core.object_titles(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, title_type_id INT NULL
, title_text VARCHAR NULL
, lang_code VARCHAR NULL
, lang_usage_id INT NOT NULL default 11
, is_default BOOLEAN NULL
, comments VARCHAR NULL
);
CREATE INDEX object_titles_object_id ON core.object_titles(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_topics()
{
string sql_string = @"CREATE TABLE core.object_topics(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, topic_type_id INT NULL
, mesh_coded BOOLEAN NULL
, topic_code VARCHAR NULL
, topic_value VARCHAR NULL
, topic_qualcode VARCHAR NULL
, topic_qualvalue VARCHAR NULL
, original_ct_id INT NULL
, original_ct_code VARCHAR NULL
, original_value VARCHAR NULL
, comments VARCHAR NULL
);
CREATE INDEX object_topics_object_id ON core.object_topics(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_descriptions()
{
string sql_string = @"CREATE TABLE core.object_descriptions(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, description_type_id INT NULL
, label VARCHAR NULL
, description_text VARCHAR NULL
, lang_code VARCHAR NULL
, contains_html BOOLEAN NULL
);
CREATE INDEX object_descriptions_object_id ON core.object_descriptions(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_identifiers()
{
string sql_string = @"CREATE TABLE core.object_identifiers(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, identifier_value VARCHAR NULL
, identifier_type_id INT NULL
, identifier_org_id INT NULL
, identifier_org VARCHAR NULL
, identifier_date VARCHAR NULL
);
CREATE INDEX object_identifiers_object_id ON core.object_identifiers(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_relationships()
{
string sql_string = @"CREATE TABLE core.object_relationships(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, relationship_type_id INT NULL
, target_object_id INT NULL
);
CREATE INDEX object_relationships_object_id ON core.object_relationships(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_rights()
{
string sql_string = @"CREATE TABLE core.object_rights(
id INT NOT NULL PRIMARY KEY
, object_id INT NOT NULL
, rights_name VARCHAR NULL
, rights_uri VARCHAR NULL
, comments VARCHAR NULL
);
CREATE INDEX object_rights_object_id ON core.object_rights(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_object_links()
{
string sql_string = @"CREATE TABLE core.study_object_links(
id INT NOT NULL PRIMARY KEY
, study_id INT NOT NULL
, object_id INT NOT NULL
);
CREATE INDEX study_object_links_studyid ON core.study_object_links(study_id);
CREATE INDEX study_object_links_objectid ON core.study_object_links(object_id);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
}
