Core table creation code
Jump to navigation
Jump to search
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);
}
}
}
