Difference between revisions of "SD table creation code"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
| + | The code below is used within the Data harvesting process to create the sd schema tables. The tables are recreated each time a harvest is run. The necessary database connection string is passed in to the class constructor. Tables are initially dropped using the drop_table function, and then recreated using successive calls to the create_table functions. | ||
| + | |||
<div style="font-family: monospace; font-size: 13px" > | <div style="font-family: monospace; font-size: 13px" > | ||
<pre> | <pre> | ||
Latest revision as of 13:46, 31 October 2020
The code below is used within the Data harvesting process to create the sd schema tables. The tables are recreated each time a harvest is run. The necessary database connection string is passed in to the class constructor. Tables are initially dropped using the drop_table function, and then recreated using successive calls to the create_table functions.
public class StudyTableBuildersSD
{
string db_conn;
public StudyTableBuildersSD(string _db_conn)
{
db_conn = _db_conn;
}
public void drop_table(string table_name)
{
string sql_string = @"DROP TABLE IF EXISTS sd." + table_name;
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_studies()
{
string sql_string = @"CREATE TABLE sd.studies(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, display_title VARCHAR NULL
, title_lang_code VARCHAR NULL default 'en'
, brief_description VARCHAR NULL
, bd_contains_html BOOLEAN NULL default false
, data_sharing_statement VARCHAR NULL
, dss_contains_html BOOLEAN NULL default false
, study_start_year INT NULL
, study_start_month INT NULL
, study_type_id INT NULL
, study_type VARCHAR NULL
, study_status_id INT NULL
, study_status VARCHAR NULL
, study_enrolment INT NULL
, study_gender_elig_id INT NULL
, study_gender_elig VARCHAR NULL
, min_age INT NULL
, min_age_units_id INT NULL
, min_age_units VARCHAR NULL
, max_age INT NULL
, max_age_units_id INT NULL
, max_age_units VARCHAR NULL
, datetime_of_data_fetch TIMESTAMPTZ NULL
, record_hash CHAR(32) NULL
, study_full_hash CHAR(32) NULL
);
CREATE INDEX studies_sid ON sd.studies(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_identifiers()
{
string sql_string = @"CREATE TABLE sd.study_identifiers(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, identifier_value VARCHAR NULL
, identifier_type_id INT NULL
, identifier_type VARCHAR NULL
, identifier_org_id INT NULL
, identifier_org VARCHAR NULL
, identifier_date VARCHAR NULL
, identifier_link VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX study_identifiers_sd_sid ON sd.study_identifiers(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_relationships()
{
string sql_string = @"CREATE TABLE sd.study_relationships(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, relationship_type_id INT NULL
, relationship_type VARCHAR NULL
, target_sd_sid VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX study_relationships_sd_sid ON sd.study_relationships(sd_sid);
CREATE INDEX study_relationships_target_sd_sid ON sd.study_relationships(target_sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_references()
{
string sql_string = @"CREATE TABLE sd.study_references(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, pmid VARCHAR NULL
, citation VARCHAR NULL
, doi VARCHAR NULL
, comments VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX study_references_sd_sid ON sd.study_references(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_titles()
{
string sql_string = @"CREATE TABLE sd.study_titles(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, title_type_id INT NULL
, title_type VARCHAR NULL
, title_text VARCHAR NULL
, lang_code VARCHAR NOT NULL default 'en'
, lang_usage_id INT NOT NULL default 11
, is_default BOOLEAN NULL
, comments VARCHAR NULL
, comparison_text VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX study_titles_sd_sid ON sd.study_titles(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_contributors()
{
string sql_string = @"CREATE TABLE sd.study_contributors(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, contrib_type_id INT NULL
, contrib_type VARCHAR 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
, record_hash CHAR(32) NULL
);
CREATE INDEX study_contributors_sd_sid ON sd.study_contributors(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_topics()
{
string sql_string = @"CREATE TABLE sd.study_topics(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid CHAR(24) NOT NULL
, topic_type_id INT NULL
, topic_type VARCHAR 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
, record_hash CHAR(32) NULL
);
CREATE INDEX study_topics_sd_sid ON sd.study_topics(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_features()
{
string sql_string = @"CREATE TABLE sd.study_features(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, feature_type_id INT NULL
, feature_type VARCHAR NULL
, feature_value_id INT NULL
, feature_value VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX study_features_sid ON sd.study_features(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_links()
{
string sql_string = @"CREATE TABLE sd.study_links(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, link_label VARCHAR NULL
, link_url VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX study_links_sd_sid ON sd.study_links(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_ipd_available()
{
string sql_string = @"CREATE TABLE sd.study_ipd_available(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, ipd_id VARCHAR NULL
, ipd_type VARCHAR NULL
, ipd_url VARCHAR NULL
, ipd_comment VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX study_ipd_available_sd_sid ON sd.study_ipd_available(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_study_hashes()
{
string sql_string = @"CREATE TABLE sd.study_hashes(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_sid VARCHAR NOT NULL
, hash_type_id INT NULL
, hash_type VARCHAR NULL
, composite_hash CHAR(32) NULL
);
CREATE INDEX study_hashes_sd_sid ON sd.study_hashes(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
}
public class ObjectTableBuildersSD
{
string db_conn;
public ObjectTableBuildersSD(string _db_conn)
{
db_conn = _db_conn;
}
public void drop_table(string table_name)
{
string sql_string = @"DROP TABLE IF EXISTS sd." + table_name;
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_data_objects()
{
string sql_string = @"CREATE TABLE sd.data_objects(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, sd_sid VARCHAR NULL
, 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_class VARCHAR NULL
, object_type_id INT NULL
, object_type VARCHAR NULL
, managing_org_id INT NULL
, managing_org VARCHAR NULL
, lang_code VARCHAR NULL
, access_type_id INT NULL
, access_type VARCHAR 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
, datetime_of_data_fetch TIMESTAMPTZ NULL
, record_hash CHAR(32) NULL
, object_full_hash CHAR(32) NULL
);
CREATE INDEX data_objects_sd_oid ON sd.data_objects(sd_oid);
CREATE INDEX data_objects_sd_sid ON sd.data_objects(sd_sid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_datasets()
{
string sql_string = @"CREATE TABLE sd.object_datasets(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, record_keys_type_id INT NULL
, record_keys_type VARCHAR NULL
, record_keys_details VARCHAR NULL
, deident_type_id INT NULL
, deident_type VARCHAR 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_type VARCHAR 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
, record_hash CHAR(32) NULL
);
CREATE INDEX object_datasets_sd_oid ON sd.object_datasets(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_dates()
{
string sql_string = @"CREATE TABLE sd.object_dates(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, date_type_id INT NULL
, date_type VARCHAR NULL
, is_date_range BOOLEAN NULL default false
, 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
, record_hash CHAR(32) NULL
);
CREATE INDEX object_dates_sd_oid ON sd.object_dates(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_instances()
{
string sql_string = @"CREATE TABLE sd.object_instances(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, instance_type_id INT NOT NULL
, instance_type VARCHAR 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_type VARCHAR NULL
, resource_size VARCHAR NULL
, resource_size_units VARCHAR NULL
, resource_comments VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_instances_sd_oid ON sd.object_instances(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_contributors()
{
string sql_string = @"CREATE TABLE sd.object_contributors(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, contrib_type_id INT NULL
, contrib_type VARCHAR 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
, record_hash CHAR(32) NULL
);
CREATE INDEX object_contributors_sd_oid ON sd.object_contributors(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_titles()
{
string sql_string = @"CREATE TABLE sd.object_titles(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, title_type_id INT NULL
, title_type VARCHAR NULL
, title_text VARCHAR NULL
, lang_code VARCHAR NULL
, lang_usage_id INT NOT NULL default 11
, is_default BOOLEAN NULL
, comments VARCHAR NULL
, comparison_text VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_titles_sd_oid ON sd.object_titles(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_topics()
{
string sql_string = @"CREATE TABLE sd.object_topics(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, topic_type_id INT NULL
, topic_type VARCHAR 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
, record_hash CHAR(32) NULL
);
CREATE INDEX object_topics_sd_oid ON sd.object_topics(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_comments()
{
string sql_string = @"CREATE TABLE sd.object_comments(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, ref_type VARCHAR NULL
, ref_source VARCHAR NULL
, pmid VARCHAR NULL
, pmid_version VARCHAR NULL
, notes VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_comments_sd_oid ON sd.object_comments(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_descriptions()
{
string sql_string = @"CREATE TABLE sd.object_descriptions(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, description_type_id INT NULL
, description_type VARCHAR NULL
, label VARCHAR NULL
, description_text VARCHAR NULL
, lang_code VARCHAR NULL
, contains_html BOOLEAN NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_descriptions_sd_oid ON sd.object_descriptions(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_identifiers()
{
string sql_string = @"CREATE TABLE sd.object_identifiers(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, identifier_value VARCHAR NULL
, identifier_type_id INT NULL
, identifier_type VARCHAR NULL
, identifier_org_id INT NULL
, identifier_org VARCHAR NULL
, identifier_date VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_identifiers_sd_oid ON sd.object_identifiers(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_db_links()
{
string sql_string = @"CREATE TABLE sd.object_db_links(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, db_sequence INT NULL
, db_name VARCHAR NULL
, id_in_db VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_db_links_sd_oid ON sd.object_db_links(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_publication_types()
{
string sql_string = @"CREATE TABLE sd.object_publication_types(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, type_name VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_publication_types_sd_oid ON sd.object_publication_types(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_relationships()
{
string sql_string = @"CREATE TABLE sd.object_relationships(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, relationship_type_id INT NULL
, relationship_type VARCHAR NULL
, target_sd_oid CHAR(24) NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_relationships_sd_oid ON sd.object_relationships(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_rights()
{
string sql_string = @"CREATE TABLE sd.object_rights(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, rights_name VARCHAR NULL
, rights_uri VARCHAR NULL
, comments VARCHAR NULL
, record_hash CHAR(32) NULL
);
CREATE INDEX object_rights_sd_oid ON sd.object_rights(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_object_hashes()
{
string sql_string = @"CREATE TABLE sd.object_hashes(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, hash_type_id INT NULL
, hash_type VARCHAR NULL
, composite_hash CHAR(32) NULL
);
CREATE INDEX object_hashes_sd_oid ON sd.object_hashes(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
public void create_table_citation_objects()
{
string sql_string = @"CREATE TABLE sd.citation_objects(
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, sd_oid CHAR(24) NOT NULL
, sd_sid VARCHAR NULL
, 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_class VARCHAR NULL
, object_type_id INT NULL
, object_type VARCHAR NULL
, managing_org_id INT NULL
, managing_org VARCHAR NULL
, lang_code VARCHAR NULL
, access_type_id INT NULL
, access_type VARCHAR 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
, datetime_of_data_fetch TIMESTAMPTZ NULL
, journal_title VARCHAR NULL
, pissn VARCHAR NULL
, eissn VARCHAR NULL
);
CREATE INDEX citation_objects_sd_oid ON sd.citation_objects(sd_oid);";
using (var conn = new NpgsqlConnection(db_conn))
{
conn.Execute(sql_string);
}
}
}
