def create_ocp_variant_table(my_cursor):
table_name = 'ocp_variant'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE ocp_variant ( ' \
'variantName varchar(100), ' \
'omniGene_gene_Id varchar(100), ' \
'description_id varchar(100), ' \
'pDot varchar(100), ' \
'cDot varchar(100), ' \
'gDot varchar(100), ' \
'regionType varchar(100), ' \
'indelType varchar(100), ' \
'variantType varchar(100), ' \
'variantProteinEffect varchar(100), ' \
'jax_variant_Id varchar(100) DEFAULT NULL, ' \
'go_variant_Id varchar(100) DEFAULT NULL, ' \
'clinvar_variant_Id varchar(100) DEFAULT NULL, ' \
'hot_spot_variant_Id varchar(100) DEFAULT NULL, ' \
'exon varchar(100) , ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT ocp_variant_description_fk_es_id FOREIGN KEY (description_id) REFERENCES editable_statements (graph_id), ' \
'CONSTRAINT ocp_variant_fk_omni_gene FOREIGN KEY (omniGene_gene_Id) REFERENCES omnigene (graph_id), ' \
'CONSTRAINT ocp_variant_fk_jax_variant FOREIGN KEY (jax_variant_Id) REFERENCES jax_variant (graph_id), ' \
'CONSTRAINT ocp_variant_fk_go_variant FOREIGN KEY (go_variant_Id) REFERENCES go_variant (graph_id), ' \
'CONSTRAINT ocp_variant_fk_clinvar_variant FOREIGN KEY (clinvar_variant_Id) REFERENCES clinvar (graph_id), ' \
'CONSTRAINT ocp_variant_fk_hot_spot_variant FOREIGN KEY (hot_spot_variant_Id) REFERENCES hot_spot (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_journals_table(my_cursor):
table_name = 'journals'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE journals ( ' \
'name varchar(250) NOT NULL, ' \
'graph_id varchar(100), ' \
'PRIMARY KEY (graph_id)' \
')'
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_jax_synonym_table(my_cursor):
table_name = 'jax_synonyms'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE jax_synonyms ( ' \
'id MEDIUMINT NOT NULL AUTO_INCREMENT, ' \
'name varchar(100) NOT NULL, ' \
'jax_gene_id varchar(100), ' \
'PRIMARY KEY (id),' \
'CONSTRAINT jax_synonym_fk_gene_id FOREIGN KEY (jax_gene_id) REFERENCES jax_gene (graph_id) ' \
')'
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_user_table(my_cursor):
table_name = 'users'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE users ( ' \
'name varchar(100), ' \
'password varchar(100), ' \
'isAdmin varchar(50), ' \
'graph_id varchar(100) PRIMARY KEY ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_internet_references_table(my_cursor):
table_name = 'internet_refs'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE internet_refs ( ' \
'accessed_date varchar(100), ' \
'web_address varchar(100), ' \
'short_reference varchar(200), ' \
'graph_id varchar(100) PRIMARY KEY ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_go_variant_table(my_cursor):
table_name = 'go_variant'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE go_variant ( ' \
'variantName varchar(100), ' \
'geneName varchar(100), ' \
'go_Id varchar(100), ' \
'mutationType varchar(100), ' \
'jaxVariant_Id varchar(100) DEFAULT NULL, ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT go_variant_fk_jax_variant FOREIGN KEY (jaxVariant_Id) REFERENCES jax_variant (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_editable_statement_table(my_cursor):
table_name = 'editable_statements'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE editable_statements ( ' \
'field varchar(100), ' \
'statement varchar(8000), ' \
'edit_date varchar(50), ' \
'editor_id varchar(100), ' \
'deleted varchar(50), ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT es_fk_editor_id FOREIGN KEY (editor_id) REFERENCES users (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_hot_spot_table(my_cursor):
table_name = 'hot_spot'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE hot_spot ( ' \
'hs_name varchar(100), ' \
'gene varchar(100), ' \
'referenceAminoAcid varchar(10), ' \
'variantAminoAcid varchar(10), ' \
'hs_begin varchar(100), ' \
'hs_end varchar(100), ' \
'hs_position MEDIUMINT, ' \
'graph_id varchar(100) PRIMARY KEY ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_author_ref_table(my_cursor):
table_name = 'author_ref'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE author_ref ( ' \
'id MEDIUMINT NOT NULL AUTO_INCREMENT, ' \
'author_id varchar(100), ' \
'ref_id varchar(100), ' \
'PRIMARY KEY (id), ' \
'FOREIGN KEY (author_id) ' \
'REFERENCES authors (graph_id), ' \
'FOREIGN KEY (ref_id) ' \
'REFERENCES refs (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_jax_gene_table(my_cursor):
table_name = 'jax_gene'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE jax_gene ( ' \
'name varchar(100), ' \
'canonicalTranscript varchar(50), ' \
'chromosome varchar(50), ' \
'entrezId varchar(50), ' \
'jaxId varchar(50), ' \
'description_Id varchar(50), ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT jax_description_fk_es_id FOREIGN KEY (description_Id) REFERENCES editable_statements (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_es_internet_refs_table(my_cursor):
table_name = 'es_internet_refs'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE es_internet_refs ( ' \
'id MEDIUMINT NOT NULL AUTO_INCREMENT, ' \
'es_id varchar(100), ' \
'internet_ref_id varchar(100), ' \
'PRIMARY KEY (id), ' \
'FOREIGN KEY (es_id) ' \
'REFERENCES editable_statements (graph_id), ' \
'FOREIGN KEY (internet_ref_id) ' \
'REFERENCES internet_refs (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_uniprot_entry_table(my_cursor):
table_name = 'uniprot_entry'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE uniprot_entry ( ' \
'name varchar(100), ' \
'accessionNumber varchar(100), ' \
'uniprot_id varchar(100), ' \
'function_Id varchar(50), ' \
'gene_Id varchar(50), ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT function_fk_es_id FOREIGN KEY (function_Id) REFERENCES editable_statements (graph_id), ' \
'CONSTRAINT uniprot_fk_gene_id FOREIGN KEY (gene_Id) REFERENCES mygene_info_gene (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_clinvar_table(my_cursor):
table_name = 'clinvar'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE clinvar ( ' \
'variantID varchar(100), ' \
'gene varchar(100), ' \
'pDot varchar(100), ' \
'cDot varchar(100), ' \
'significance varchar(100), ' \
'signficanceExplanation varchar(200), ' \
'cdot_pos BIGINT, ' \
'pdot_pos BIGINT, ' \
'graph_id varchar(100) PRIMARY KEY' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_hot_spot_occurrences_table(my_cursor):
table_name = 'hot_spot_occurrences'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE hot_spot_occurrences ( ' \
'id MEDIUMINT NOT NULL AUTO_INCREMENT, ' \
'disease varchar(100) NOT NULL, ' \
'oncoTreeCode varchar(20) NOT NULL, ' \
'percentOccurrence varchar(10) NOT NULL, ' \
'perThousandOccurrence MEDIUMINT NOT NULL, ' \
'occurrences MEDIUMINT NOT NULL, ' \
'totalSamples MEDIUMINT NOT NULL, ' \
'hot_spot_id varchar(100), ' \
'PRIMARY KEY (id),' \
'CONSTRAINT hs_occurrences_fk_hotspot_id FOREIGN KEY (hot_spot_id) REFERENCES hot_spot (graph_id) ' \
')'
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_references_table(my_cursor):
table_name = 'refs'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE refs ( ' \
'PMID varchar(20) NOT NULL, ' \
'DOI varchar(100), ' \
'title varchar(1000), ' \
'journal_graph_id varchar(100), ' \
'volume varchar(20), ' \
'first_page varchar(20), ' \
'last_page varchar(20), ' \
'publication_Year varchar(20), ' \
'short_reference varchar(200), ' \
'abstract varchar(8000), ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT refs_fk_journal_graph_id FOREIGN KEY (journal_graph_id) REFERENCES journals (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_jax_variant_table(my_cursor):
table_name = 'jax_variant'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE jax_variant ( ' \
'variantName varchar(100), ' \
'description_id varchar(100), ' \
'jax_Id varchar(100), ' \
'jaxGene_gene_Id varchar(100), ' \
'pDot varchar(100), ' \
'cDot varchar(100), ' \
'gDot varchar(100), ' \
'transcript varchar(100), ' \
'variantType varchar(100), ' \
'proteinEffect varchar(100), ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT variant_description_fk_es_id FOREIGN KEY (description_id) REFERENCES editable_statements (graph_id), ' \
'CONSTRAINT variant_fk_jax_gene FOREIGN KEY (jaxGene_gene_Id) REFERENCES jax_gene (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
def create_omnigene_table(my_cursor):
table_name = 'omnigene'
if not does_table_exist(my_cursor, table_name):
mySql_Create_Table_Query = 'CREATE TABLE omnigene ( ' \
'name varchar(100), ' \
'panelName varchar(100), ' \
'geneDescription_id varchar(100), ' \
'oncogenicCategory_Id varchar(100), ' \
'synonymsString_Id varchar(100), ' \
'myGeneInfo_gene_Id varchar(100), ' \
'jaxGene_gene_Id varchar(100), ' \
'uniprot_entry_Id varchar(100), ' \
'graph_id varchar(100) PRIMARY KEY, ' \
'CONSTRAINT geneDescription_fk_es_id FOREIGN KEY (geneDescription_id) REFERENCES editable_statements (graph_id), ' \
'CONSTRAINT oncogenicCategory_fk_es_id FOREIGN KEY (oncogenicCategory_Id) REFERENCES editable_statements (graph_id), ' \
'CONSTRAINT synonymsString_fk_es_id FOREIGN KEY (synonymsString_Id) REFERENCES editable_statements (graph_id), ' \
'CONSTRAINT omni_fk_mygene FOREIGN KEY (myGeneInfo_gene_Id) REFERENCES mygene_info_gene (graph_id), ' \
'CONSTRAINT omni_fk_jax FOREIGN KEY (jaxGene_gene_Id) REFERENCES jax_gene (graph_id), ' \
'CONSTRAINT omni_fk_uniprot FOREIGN KEY (uniprot_entry_Id) REFERENCES uniprot_entry (graph_id) ' \
')'
print(mySql_Create_Table_Query)
result = my_cursor.execute(mySql_Create_Table_Query)
print(f'{table_name} Table created successfully')
