SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
-- -----------------------------------------------------
CREATE TABLE bci_stg (
           entrez_gene1 varchar(20) NOT NULL,
           name_gene1 varchar(40) NULL DEFAULT NULL,
           db1_xref varchar(20) NULL DEFAULT NULL,
           entrez_gene2 varchar(20) NOT NULL,
           name_gene2 varchar(40) NULL DEFAULT NULL,
           db2_xref varchar(20) NULL DEFAULT NULL,
           entrez_gene3 varchar(12) NULL DEFAULT NULL,
           name_gene3 varchar(40) NULL DEFAULT NULL,
           interaction_type varchar(30) NOT NULL COMMENT 'ppi,pdi,tfmi,etc',
           interaction_mode ENUM('activates','inhibits','no change','unsure') NOT NULL,
           probability double NOT NULL,
           confidence_type ENUM( 'probability', 'mutual information', '-log10(p-value)', 'likelihood ratio') NOT NULL,
           source varchar(15) NULL DEFAULT NULL COMMENT 'db source (MINT)',
           source_version varchar(10) NULL DEFAULT NULL,
           species_name varchar(100) NULL DEFAULT NULL,
           species_name2 varchar(100) NULL DEFAULT NULL,
           description text NULL DEFAULT NULL,
           evidence_code varchar(5) NULL DEFAULT NULL,
           web_visible_yn char(1) NULL DEFAULT NULL,
           KEY entrez_gene1_idx (entrez_gene1),                                                
           KEY entrez_gene2_idx (entrez_gene2),                                                
           KEY name_gene1_idx (name_gene1),                                                    
           KEY name_gene2_idx (name_gene2),                                                    
           KEY interaction_type_idx (interaction_type),                                        
           KEY source_idx (source)
         ) DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table algorithm
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS algorithm (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX algorithm_name_uniq_idx (name ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table algorithm_version
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS algorithm_version (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  algorithm_id SMALLINT(4) NOT NULL,
  version_identifier VARCHAR(10) NOT NULL,
  executable BLOB NULL DEFAULT NULL,
  modification_date DATE NULL DEFAULT NULL,
  revision_control_info TEXT NULL DEFAULT NULL,
  description TEXT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX algorithm_ver_uniq_idx (algorithm_id ASC, version_identifier ASC),
  CONSTRAINT algorithm_id_ibfk_1
    FOREIGN KEY (algorithm_id )
    REFERENCES algorithm (id ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table confidence_type
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS confidence_type (
  id TINYINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  description VARCHAR(100) NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX confidence_name_uniq_idx (name ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table dataset_type
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS dataset_type (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX dataset_type_uniq_idx (name ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table db_source
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS db_source (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  url VARCHAR(100) NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX name_uniq_idx (name ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table dataset
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS dataset (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  dataset_type_id SMALLINT(4),
  db_source_id SMALLINT(4),
  file_location VARCHAR(100) NULL,
  file_format TEXT NULL,
  file_blob BLOB NULL,
  description TEXT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX fk_dataset_dataset_type1 (dataset_type_id ASC),
  INDEX fk_dataset_db_source1 (db_source_id ASC),
  UNIQUE INDEX dataset_name_unique_idx (name ASC),
  CONSTRAINT fk_dataset_dataset_type1
    FOREIGN KEY (dataset_type_id )
    REFERENCES dataset_type (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_dataset_db_source1
    FOREIGN KEY (db_source_id )
    REFERENCES db_source (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table interactome
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interactome (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX interactome_name_uniq_idx (name ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table interactome_version
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interactome_version (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  interactome_id SMALLINT(4) NOT NULL,
  version VARCHAR(10) NOT NULL,
  authentication_yn ENUM('O','Y','N') NOT NULL,
  uploaded_file BLOB NULL,
  description TEXT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX interactome_ver_uniq_idx (interactome_id ASC, version ASC),
  CONSTRAINT interactome_id_ibfk_1
    FOREIGN KEY (interactome_id )
    REFERENCES interactome (id ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table interaction
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(60) NULL DEFAULT NULL,
  interaction_type SMALLINT(4) NOT NULL,
  interaction_mode ENUM('activates','inhibits','no change','unsure') NULL,
  description TEXT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX interaction_name_uniq_idx (name ASC),
  CONSTRAINT interaction_type_fk
    FOREIGN KEY (interaction_type )
    REFERENCES interaction_type (id ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table interaction_confidence
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_confidence (
  id INT NOT NULL AUTO_INCREMENT,
  interaction_id INT NOT NULL,
  confidence_type_id TINYINT NOT NULL,
  score DOUBLE NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_interaction_confidence_interaction1 (interaction_id ASC),
  INDEX fk_interaction_confidence_confidence_type1 (confidence_type_id ASC),
  UNIQUE INDEX index4 (interaction_id ASC, confidence_type_id ASC, score ASC),
  CONSTRAINT fk_interaction_confidence_interaction1
    FOREIGN KEY (interaction_id)
    REFERENCES interaction (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interaction_confidence_confidence_type1
    FOREIGN KEY (confidence_type_id)
    REFERENCES confidence_type (id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interaction_interactome_version
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_interactome_version (
  id INT(11) NOT NULL AUTO_INCREMENT,
  interaction_id INT(11) NOT NULL,
  interactome_version_id SMALLINT(4) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX interaction_interactome_uniq_idx (interaction_id ASC, interactome_version_id ASC),
  INDEX interactome_version_id_fk (interactome_version_id ASC),
  CONSTRAINT interaction_id_fk
    FOREIGN KEY (interaction_id )
    REFERENCES interaction (id ),
  CONSTRAINT interactome_version_id_fk
    FOREIGN KEY (interactome_version_id )
    REFERENCES interactome_version (id ))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table interactome_version_dataset
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interactome_version_dataset (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  interactome_version_id SMALLINT(4) NOT NULL,
  dataset_id SMALLINT(4) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  INDEX fk_interactome_version_dataset_dataset1 (dataset_id ASC),
  INDEX fk_interactome_version_dataset_interactome_version1 (interactome_version_id ASC),
  UNIQUE INDEX interactome_vers_dataset_uniq_idx (interactome_version_id ASC, dataset_id ASC),
  CONSTRAINT fk_interactome_version_dataset_dataset1
    FOREIGN KEY (dataset_id )
    REFERENCES dataset (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interactome_version_dataset_interactome_version1
    FOREIGN KEY (interactome_version_id )
    REFERENCES interactome_version (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interaction_type
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_type (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX interaction_type_uniq_idx (name ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table entity_type
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS entity_type (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX entity_type_uniq_idx (name ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table physical_entity
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS physical_entity (
  id INT NOT NULL AUTO_INCREMENT,
  primary_accession INT(11) NULL,
  secondary_accession VARCHAR(20) NULL,
  gene_symbol VARCHAR(20) NULL,
  taxonomy_id INT(11) NOT NULL,
  accession_db SMALLINT(4),
  entity_type_id SMALLINT(4),
  description TEXT NULL,
  entrez_version INT(11),
  discontinued_yn CHAR(1),
  discontinued_dt DATE,
  PRIMARY KEY (id),
  INDEX pe_db_source_idx (accession_db),
  INDEX pe_symbol_idx (gene_symbol),
  UNIQUE INDEX prim_accdb_uniq_idx (primary_accession, accession_db),
  UNIQUE INDEX sec_acc_uniq_idx (secondary_accession),
  CONSTRAINT fk_physical_entity_db_source1 FOREIGN KEY (accession_db) REFERENCES db_source (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_physical_entity_entity_type1 FOREIGN KEY (entity_type_id) REFERENCES entity_type (id) ON DELETE NO ACTION ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- ----------------------------------------
-- Table entrez_history -------------
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS entrez_history (
  discontinued_geneid INT(11) NOT NULL,
  discontinued_symbol VARCHAR(35) NOT NULL,
  discontinued_date INT(11) NOT NULL,
  UNIQUE INDEX geneid_idx (discontinued_geneid) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table role
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS role (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX role_name_uniq_idx (name ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interaction_participant
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_participant (
  id INT NOT NULL AUTO_INCREMENT,
  participant_id INT NOT NULL,
  interaction_id INT NOT NULL,
  role_id SMALLINT(4),
  PRIMARY KEY (id),
  INDEX fk_interaction_participant_physical_entity1 (participant_id ASC),
  INDEX fk_interaction_participant_interaction1 (interaction_id ASC),
  INDEX fk_interaction_participant_role1 (role_id ASC),
  UNIQUE INDEX interaction_participant_uniq_idx (participant_id ASC, interaction_id ASC),
  CONSTRAINT fk_interaction_participant_physical_entity1
    FOREIGN KEY (participant_id )
    REFERENCES physical_entity (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interaction_participant_interaction1
    FOREIGN KEY (interaction_id )
    REFERENCES interaction (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interaction_participant_role1
    FOREIGN KEY (role_id )
    REFERENCES role (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table xref
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS xref (
  id INT NOT NULL AUTO_INCREMENT,
  db_id SMALLINT(4) NOT NULL,
  accession_id VARCHAR(40) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  INDEX fk_xref_db_source1 (db_id ASC),
  UNIQUE INDEX accession_db_uniq_idx (accession_id ASC, db_id ASC),
  CONSTRAINT fk_xref_db_source1
    FOREIGN KEY (db_id )
    REFERENCES db_source (id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table cellular_compartment
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS cellular_compartment (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  go_acc VARCHAR(20) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX compartment_uniq_idx (name ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table evidence_type
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS evidence_type (
  id SMALLINT(4) NOT NULL AUTO_INCREMENT,
  code VARCHAR(5) NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX evidence_code_uniq_idx (code ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interactome_dataset_algorithm
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interactome_dataset_algorithm (
  id INT NOT NULL AUTO_INCREMENT,
  interactome_version_id SMALLINT(4) NOT NULL,
  dataset_id SMALLINT(4) NOT NULL,
  algorithm_version_id SMALLINT(4) NOT NULL,
  runtime_string TEXT NULL,
  run_date DATE NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  INDEX fk_interactome_dataset_algorithm_dataset1 (dataset_id ASC),
  INDEX fk_interactome_dataset_algorithm_interactome_version1 (interactome_version_id ASC),
  INDEX fk_interactome_dataset_algorithm_algorithm_version1 (algorithm_version_id ASC),
  UNIQUE INDEX interactome_dataset_algorithm_uniq_idx (interactome_version_id ASC, dataset_id ASC, algorithm_version_id ASC),
  CONSTRAINT fk_interactome_dataset_algorithm_dataset1
    FOREIGN KEY (dataset_id )
    REFERENCES dataset (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interactome_dataset_algorithm_interactome_version1
    FOREIGN KEY (interactome_version_id )
    REFERENCES interactome_version (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interactome_dataset_algorithm_algorithm_version1
    FOREIGN KEY (algorithm_version_id )
    REFERENCES algorithm_version (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interaction_compartment
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_compartment (
  id INT NOT NULL AUTO_INCREMENT,
  interaction_id INT NOT NULL,
  compartment_id SMALLINT(4) NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_interaction_compartment_cellular_compartment1 (compartment_id ASC),
  INDEX fk_interaction_compartment_interaction1 (interaction_id ASC),
  UNIQUE INDEX interaction_compartment_uniq_idx (interaction_id ASC, compartment_id ASC),
  CONSTRAINT fk_interaction_compartment_cellular_compartment1
    FOREIGN KEY (compartment_id )
    REFERENCES cellular_compartment (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interaction_compartment_interaction1
    FOREIGN KEY (interaction_id )
    REFERENCES interaction (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interaction_evidence
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_evidence (
  id INT NOT NULL AUTO_INCREMENT,
  interaction_id INT NOT NULL,
  evidence_id SMALLINT(4) NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_interaction_evidence_evidence_type1 (evidence_id ASC),
  INDEX fk_interaction_evidence_interaction1 (interaction_id ASC),
  UNIQUE INDEX interaction_evidence_uniq_idx (interaction_id ASC, evidence_id ASC),
  CONSTRAINT fk_interaction_evidence_evidence_type1
    FOREIGN KEY (evidence_id )
    REFERENCES evidence_type (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interaction_evidence_interaction1
    FOREIGN KEY (interaction_id )
    REFERENCES interaction (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table taxonomy
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS taxonomy (
  primary_acc INT NOT NULL,
  species_name VARCHAR(50) NOT NULL,
  PRIMARY KEY (primary_acc)
)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table phenotype
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS phenotype (
  id INT NOT NULL AUTO_INCREMENT,
  icd_code VARCHAR(30) NOT NULL,
  taxon_id INT NOT NULL,
  tissue VARCHAR(40) NULL,
  cell_type VARCHAR(40) NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX icd_code_uniq_idx (icd_code ASC),
  INDEX fk_phenotype_taxonomy1 (taxon_id ASC),
  CONSTRAINT fk_phenotype_taxonomy1
    FOREIGN KEY (taxon_id )
    REFERENCES taxonomy (primary_acc)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interaction_phenotype
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_phenotype (
  id INT NOT NULL AUTO_INCREMENT,
  interaction_id INT NOT NULL,
  phenotype_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_interaction_phenotype_phenotype1 (phenotype_id ASC),
  INDEX fk_interaction_phenotype_interaction1 (interaction_id ASC),
  UNIQUE INDEX interaction_phenotype_uniq_idx (interaction_id ASC, phenotype_id ASC),
  CONSTRAINT fk_interaction_phenotype_phenotype1
    FOREIGN KEY (phenotype_id )
    REFERENCES phenotype (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interaction_phenotype_interaction1
    FOREIGN KEY (interaction_id )
    REFERENCES interaction (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table interaction_xref
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS interaction_xref (
  id INT NOT NULL AUTO_INCREMENT,
  interaction_id INT NOT NULL,
  xref_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_interaction_xref_interaction1 (interaction_id ASC),
  INDEX fk_interaction_xref_xref1 (xref_id ASC),
  UNIQUE INDEX interaction_xref_uniq_idx (interaction_id ASC, xref_id ASC),
  CONSTRAINT fk_interaction_xref_interaction1
    FOREIGN KEY (interaction_id )
    REFERENCES interaction (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_interaction_xref_xref1
    FOREIGN KEY (xref_id )
    REFERENCES xref (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table pathway
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS pathway (
  id INT NOT NULL AUTO_INCREMENT,
  short_name VARCHAR(40) NOT NULL,
  taxon_id INT NOT NULL,
  description TEXT NULL,
  PRIMARY KEY (id),
  INDEX fk_pathway_taxonomy1 (taxon_id ASC),
  UNIQUE INDEX short_name_uniq_idx (short_name ASC),
  CONSTRAINT fk_pathway_taxonomy1
    FOREIGN KEY (taxon_id )
    REFERENCES taxonomy (primary_acc)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table pathway_component
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS pathway_component (
  id INT NOT NULL AUTO_INCREMENT,
  interaction_id INT NOT NULL,
  pathway_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_pathway_component_interaction1 (interaction_id ASC),
  INDEX fk_pathway_component_pathway1 (pathway_id ASC),
  UNIQUE INDEX pathway_component_uniq_idx (interaction_id ASC, pathway_id ASC),
  CONSTRAINT fk_pathway_component_interaction1
    FOREIGN KEY (interaction_id )
    REFERENCES interaction (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_pathway_component_pathway1
    FOREIGN KEY (pathway_id )
    REFERENCES pathway (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table pathway_xref
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS pathway_xref (
  id INT NOT NULL AUTO_INCREMENT,
  pathway_id INT NOT NULL,
  xref_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_pathway_xref_pathway1 (pathway_id ASC),
  INDEX fk_pathway_xref_xref1 (xref_id ASC),
  UNIQUE INDEX pathway_xref_uniq_idx (pathway_id ASC, xref_id ASC),
  CONSTRAINT fk_pathway_xref_pathway1
    FOREIGN KEY (pathway_id )
    REFERENCES pathway (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_pathway_xref_xref1
    FOREIGN KEY (xref_id )
    REFERENCES xref (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table physicalentity_xref
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS physicalentity_xref (
  id INT NOT NULL AUTO_INCREMENT,
  physical_entity_id INT NOT NULL,
  xref_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX fk_physicalentity_xref_physical_entity1 (physical_entity_id ASC),
  INDEX fk_physicalentity_xref_xref1 (xref_id ASC),
  CONSTRAINT fk_physicalentity_xref_physical_entity1
    FOREIGN KEY (physical_entity_id )
    REFERENCES physical_entity (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT fk_physicalentity_xref_xref1
    FOREIGN KEY (xref_id )
    REFERENCES xref (id )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
--
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Data for table 'bci_stg' ----------------
-- -----------------------------------------------------
-- HGi_Sun
$ awk 'BEGIN {FS="\t"; OFS="\t"} {print $1,$2,$3,"protein-dna","Human (Homo sapiens)","HGi_Sun"}' HGiV1_Sun.txt > ../parsed_files/bci_stg.sun
$ mysqlimport -u mhonig -p cellnet_kbase --columns=entrez_gene1,entrez_gene2,probability,interaction_type,species_name,source $HOME/InteractionDB/parsed_files/bci_stg.sun
-- HGi_Phillips
$ awk 'BEGIN {FS="\t"; OFS="\t"} {print $1,$2,$3,"protein-dna","Human (Homo sapiens)","HGi_Phillips"}' HGiV1_Phillips.txt > ../parsed_files/bci_stg.phillips
$ mysqlimport -u mhonig -p cellnet_kbase --columns=entrez_gene1,entrez_gene2,probability,interaction_type,species_name,source $HOME/InteractionDB/parsed_files/bci_stg.phillips
-- HGi_TCGA
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,entrez_gene2,probability,interaction_type,species_name,source $HOME/InteractionDB/parsed_files/bci_stg.tcgapdi
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,name_gene2,probability,interaction_type,species_name,source $HOME/InteractionDB/parsed_files/bci_stg.tcgamirna
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,db1_xref,entrez_gene2,db2_xref,entrez_gene3,probability,interaction_type,species_name,source /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg.tcga_ver2
-- HGi_v3
$ awk 'BEGIN {FS="\t"; OFS="\t"} {print $1,$2,$3,"miRNA-mRNA","Human (Homo sapiens)","HGi_v3"}' hgiv3.tab > ../parsed_files/bci_stg.hgi_v3
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,entrez_gene2,probability,interaction_type,species_name,source $HOME/InteractionDB/parsed_files/bci_stg.hgi_v3

-- HGi_v4
$ awk 'BEGIN {FS="\t"; OFS="\t"} {print $1,$2,$3,$4,"Human (Homo sapiens)","HGi_v4"}' hgiv4_multiple.txt > ../parsed_files/bci_stg.hgi_v4
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,entrez_gene2,interaction_type,probability,species_name,source /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg.hgi_v4
-- preppi_v1
$ mysql -u mhonig -p -e "use predusdb; select int_a, int_b, preppi_score, organism, version from preppi_int into /tmp/preppi.tab';"
$ scp preppi.tab mhonig@afdev.c2b2.columbia.edu:/tmp
$ awk 'BEGIN {FS="\t"; OFS="\t"} {print $1,"Uniprot",$2,"Uniprot",$3,"likelihood ratio",$4,$5,"preppi","protein-protein"}' preppi.tab > $HOME/InteractionDB/parsed_files/bci_stg.preppi_v1
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,db1_xref,entrez_gene2,db2_xref,probability,confidence_type,species_name,source_version,source,interaction_type /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg.preppi_v1

-- Reactome
$ wget http://www.reactome.org/download/current/homo_sapiens.interactions.txt.gz
$ perl ../Scripts/parse_reactome.pl | uniq > ../parsed_files/bci_stg.reactome_v33
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,db1_xref,entrez_gene2,db2_xref,probability,interaction_type,source /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg.reactome_v33
-- HPRD
$ perl ../Scripts/parse_hprd.pl | uniq > ../parsed_files/bci_stg.hprd_rel9
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=name_gene1,db1_xref,name_gene2,db2_xref,probability,interaction_type,species_name,source $HOME/InteractionDB/parsed_files/bci_stg.hprd_rel9
-- BIND
$ perl ../Scripts/parse_bind.pl > ../parsed_files/bci_stg.bond
$ mysqlimport -u mhonig -p -L cellnet_kbase --columns=entrez_gene1,db1_xref,name_gene1,entrez_gene2,db2_xref,name_gene2,species_name,interaction_type,source $HOME/InteractionDB/parsed_files/bci_stg.bond
UPDATE bci_stg SET interaction_type = 'protein-dna' WHERE interaction_type = 'dna-protein';
UPDATE bci_stg SET interaction_type = 'protein-rna' WHERE interaction_type = 'rna-protein';
UPDATE bci_stg SET probability = '1.0' WHERE source = 'BIND';
-- MINT
$ wget ftp://mint.bio.uniroma2.it/pub/release/mitab26/current/2010-07-27-mint-human-binary.mitab26.txt
$ ../Scripts/parse_mint.pl | uniq > ../parsed_files/bci_stg.mint
$ mysqlimport -u mhonig -p cellnet_kbase --columns=db1_xref,entrez_gene1,db2_xref,entrez_gene2,interaction_type,source,species_name,probability /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg.mint
DELETE FROM bci_stg WHERE db1_xref = 'genbank_protein_gi';
DELETE FROM bci_stg WHERE db2_xref = 'genbank_protein_gi';
UPDATE bci_stg SET source = 'MINT' WHERE source = 'mint';
UPDATE bci_stg SET db1_xref = 'Uniprot' WHERE db1_xref = 'uniprotkb';
UPDATE bci_stg SET db2_xref = 'Uniprot' WHERE db2_xref = 'uniprotkb';
UPDATE bci_stg SET db1_xref = 'RefSeq' WHERE db1_xref = 'refseq';
UPDATE bci_stg SET db2_xref = 'RefSeq' WHERE db2_xref = 'refseq';
/* ******************************************
************ ceRNET files *******************
******************************************** */
$ sed -e 's/^M//g' GBM_ceRNET.tab > GBM_ceRNET.tab2  /* do likewise for all files */
$ sed -e 's/\;/\,/g' GBM_ceRNET.tab2 > GBM_ceRNET.tab3

$ perl ../Scripts/parse_ceRNA_PRAD.pl > ../parsed_files/bci_stg_cerna.prad
$ perl ../Scripts/parse_ceRNA_OV.pl > ../parsed_files/bci_stg_cerna.ov
$ perl ../Scripts/parse_ceRNA_GBM.pl > ../parsed_files/bci_stg_cerna.gbm
$ perl ../Scripts/parse_ceRNA_BRCA.pl > ../parsed_files/bci_stg_cerna.brca

mysqlimport -u mhonig -p -L cellnet_kbase --columns=name_gene1,db1_xref,name_gene2,db2_xref,name_gene3,db3_xref,interaction_type,interaction_mode,probability,confidence_type,source,source_version,species_name,evidence_code,description  /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg_cerna.brca
mysqlimport -u mhonig -p -L cellnet_kbase --columns=name_gene1,db1_xref,name_gene2,db2_xref,name_gene3,db3_xref,interaction_type,interaction_mode,probability,confidence_type,source,source_version,species_name,evidence_code,description  /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg_cerna.ov
mysqlimport -u mhonig -p -L cellnet_kbase --columns=name_gene1,db1_xref,name_gene2,db2_xref,name_gene3,db3_xref,interaction_type,interaction_mode,probability,confidence_type,source,source_version,species_name,evidence_code,description  /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg_cerna.gbm
mysqlimport -u mhonig -p -L cellnet_kbase --columns=name_gene1,db1_xref,name_gene2,db2_xref,name_gene3,db3_xref,interaction_type,interaction_mode,probability,confidence_type,source,source_version,species_name,evidence_code,description  /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/bci_stg_cerna.prad
-- -----------------------------------------------------
-- Data for table ALGORITHM ----------------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO algorithm (id, name, description) VALUES
       (NULL, 'MINDy', NULL),
       (NULL, 'ARACNe', NULL),
       (NULL, 'BEIA', NULL);
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table ALGORITHM_VERSION ---------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO algorithm_version (id, algorithm_id, version_identifier) VALUES
       (null,(select id from algorithm where name = 'MINDy'),'1.0'),
       (null,(select id from algorithm where name = 'ARACNe'),'1.0'),
       (null,(select id from algorithm where name = 'ARACNe'),'2.0'),
       (null,(select id from algorithm where name = 'BEIA'),'1.0');
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table CONFIDENCE_TYPE
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO confidence_type (id, name, description) VALUES
       (null, 'likelihood ratio', null),
       (null, '-log10(p-value)', null),
       (null, 'mutual information', null),
       (null, 'probability', null);
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table DATASET_TYPE --------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO dataset_type (id, name, description) VALUES 
       (NULL, 'microarray-set', NULL),
       (NULL, 'gene-expression profile', NULL),
       (NULL, 'copy-number variation', NULL),
       (NULL, 'interaction-set', NULL);
COMMIT;
SET AUTOCOMMIT=1;
----------------------------------------------------
------- Data for table DATASET --------
----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO dataset (id, name, dataset_type_id, description) VALUES
(NULL,'BCi_GEP_HGU95A_254',(select id from dataset_type where name = 'gene-expression profile'),'B-cell gene-expression profile, Affymetrix HG-U95A for 254 samples'),
(NULL,'HGEP_Sun','Human Gene Expression Profile - Sun'),
(NULL,'HGEP_Phillips','Human Gene Expression Profile - Phillips'),
(NULL,'TCGA_GBM','The Cancer Genome Atlas - Glioblastoma Multiforme'),
(NULL,'TCGA_OC','The Cancer Genome Atlas - Ovarian Cancer'),
(NULL,'TCGA_LC','The Cancer Genome Atlas - Lung Cancer');
COMMIT;
SET AUTOCOMMIT=1;
-- -------------------------------------
-- Data for table DB_SOURCE -----
-- -------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO db_source (id, name, url) VALUES 
       (NULL, 'Swissprot', NULL),
       (NULL, 'Uniprot', NULL),
       (NULL, 'RefSeq', NULL),
       (NULL, 'IntAct', NULL),
       (NULL, 'HPRD', NULL),
       (NULL, 'MINT', NULL),
       (NULL, 'BIND', NULL),
       (NULL, 'DIP', NULL),
       (NULL, 'KEGG', NULL),
       (NULL, 'HGNC', NULL),
       (NULL, 'Entrez Gene', NULL),
       (NULL, 'Geneways', NULL),
       (NULL, 'Reactome', NULL),
       (NULL, 'BioGRID', NULL),
       (NULL, 'Gene Ontology', NULL),
       (NULL, 'PubMed', NULL),
       (NULL, 'miRDB', NULL),
       (NULL, 'Preppi', NULL);
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table INTERACTOME ------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO interactome (id, name, description) VALUES
       (NULL, 'HGi_TCGA', 
'Interactome produced using gene expression from TCGA consortium for 273 samples from high-grade glioma patients.
The dataset is described in: \"Network CGAR. Comprehensive genomic characterization defines human glioblastoma
genes and core pathways\". Nature. 2008;455(7216):1061-8'),
       (NULL, 'HGi_Sun', 
'The interactome was produced using gene expression profiles for 331 brain samples, including non-cancer tissue,
Glioblastome multiformae, and Astrocytoma. A detailed description of this dataset can be found in:
Sun L, Hui AM, Su Q, Vortmeyer A, Kotliarov Y, Pastorino S, et al. \"Neuronal and glioma-derived stem cell factor
induces angiogenesis within the brain.\" Cancer Cell. 2006;9(4):287-300'),
       (NULL, 'HGi_Phillips', 
'Interactome produced using gene expression profiles for 176 glioblastoma patients from the following publication:
Phillips HS, Kharbanda S, Chen R, Forrest WF, Soriano RH, Wu TD, et al. \"Molecular subclasses of high-grade glioma
predict prognosis, delineate a pattern of disease progression, and resemble stages in neurogenesis.\" Cancer Cell. 2006;9(3):157-73'),
       (NULL, 'BCi', 'Human B-cell Interactome'),
       (NULL, 'Reactome', 'Reactome'),
       (NULL, 'HPRD', 'the Human Protein Reference Database'),
       (NULL, 'Geneways', 'mined from various literature sources'),
       (NULL, 'BIND', 'Biomolecular Interaction Network Database'),
       (NULL, 'MINT', 'the Molecular INTeraction database'),
       (NULL, 'HGi', 'Integrated version of the HGi-TCGA, HGi-Phillips, and HGi-Sun interactomes.'),
       (NULL, 'Preppi','PREPPI is a database of predicted and experimentally determined protein-protein interactions (PPI)
              for yeast and human. Predicted interactions in the database are determined using Bayesian framework that combines
              structural, functional, evolutionary and expression information using a naïve Bayesian network as described in
              Reference'),
       (NULL, 'BRCA_ceRNA_v1', 'NO DESCRIPTION'),
       (NULL, 'OV_ceRNA_v1', 'NO DESCRIPTION'),
       (NULL, 'GBM_ceRNA_v1', 'NO DESCRIPTION'),
       (NULL, 'PRAD_ceRNA_v1', 'NO DESCRIPTION'),
       (NULL, 'Common_ceRNA_v1', 'NO DESCRIPTION'),
       (NULL, 'TCGA_MCF7_v1', 'NO DESCRIPTION');
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table INTERACTOME_VERSION --------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO interactome_version (id, interactome_id, version, authentication_yn, description) VALUES
       (NULL,(select id from interactome where name = 'BCi'),'1.0','N',''),
       (NULL,(select id from interactome where name = 'HGi_TCGA'),'1.0','N','Version includes transcriptional interactions
                        predicted by the ARACNe algorithm (Margolin AA, Nemenman I, Basso K, Wiggins C, Stolovitzky G, Favera D, et al.
                        \"ARACNE: An Algorithm for the Reconstruction of Gene Regulatory Networks in a Mammalian Cellular Context\". BMC Bioinformatics. 2006;7 Suppl 1:S1-7).
                        It contains both TF-target gene and TF-miRNA predictions (TF = Transcription Factor).'),
       (NULL,(select id from interactome where name = 'HGi_TCGA'),'2.0','N',''),
       (NULL,(select id from interactome where name = 'HGi_Sun'),'1.0','N','This version includes transcriptional interactions 
                        predicted by the ARACNe algorithm: Margolin AA, Nemenman I, Basso K, Wiggins C, Stolovitzky G, Favera D, et al.
                        \"ARACNE: An Algorithm for the Reconstruction of Gene Regulatory Networks in a Mammalian Cellular Context\". BMC Bioinformatics. 2006;7 Suppl 1:S1-7.'),
       (NULL,(select id from interactome where name = 'HGi_Phillips'),'1.0','Y','Version includes transcriptional interactions
        predicted by the ARACNe algorithm: Margolin AA, Nemenman I, Basso K, Wiggins C, Stolovitzky G, Favera D, et al.
        \"ARACNE: An Algorithm for the Reconstruction of Gene Regulatory Networks in a Mammalian Cellular Context\". BMC Bioinformatics. 2006;7 Suppl 1:S1-7.'),
       (NULL,(select id from interactome where name = 'Reactome'),'33','N',''),
       (NULL,(select id from interactome where name = 'HPRD'),'9','N',''),
       (NULL,(select id from interactome where name = 'Geneways'),'1.0','N',''),
       (NULL,(select id from interactome where name = 'BIND'),'082010','N',''),
       (NULL,(select id from interactome where name = 'MINT'),'090910','N',''),
       (NULL,(select id from interactome where name = 'HGi'),'3.0','Y',''),
       (NULL,(select id from interactome where name = 'HGi'),'4.0','Y','This version contains 4 types of interactions:
                         protein-protein interactions (PPIs), protein-DNA interactions (PDIs), transcription factor-modulator interactions (TFMIs)
                         and microRNA-mRNA interactions (miRmIs). PPIs and PDIs are predicted with a naïve bayes classifier while miRmIs are predicted using Support Vector Machine (SVM).'),
       (NULL,(select id from interactome where name = 'Preppi'),'1.0','N','Version 1.0 contains 31,402 high confidence
                        predicted PPIs for yeast and 317,813 PPIs for human).
                        Experimentally determined interactions are compiled from a set of publically available databases that curate
                        interactions from the literature, e.g., DIP, IntAct, HPRD, etc.'),
       (NULL, (select id from interactome where name = 'BRCA_ceRNA_v1'), '1.0', 'Y',''),
       (NULL, (select id from interactome where name = 'GBM_ceRNA_v1'), '1.0', 'Y',''),
       (NULL, (select id from interactome where name = 'OV_ceRNA_v1'), '1.0', 'Y',''),
       (NULL, (select id from interactome where name = 'PRAD_ceRNA_v1'), '1.0', 'Y',''),
       (NULL, (select id from interactome where name = 'Common_ceRNA_v1'), '1.0', 'Y',''),
       (NULL, (select id from interactome where name = 'TCGA_MCF7_v1'), '1.0', 'Y','');
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table INTERACTION_TYPE -------------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO interaction_type (id, name, short_name, description) VALUES
       (NULL, 'protein-dna', 'pd', NULL),
       (NULL, 'protein-protein', 'pp', NULL),
       (NULL, 'modulator-TF', 'tm', NULL),
       (NULL, 'miRNA-mRNA', 'mm', NULL),
       (NULL, 'rna-rna-miRNA', 'rm', NULL),
       (NULL, 'physical association', 'pa', NULL),
       (NULL, 'protein-rna', 'pn', NULL),
       (NULL, 'dna-dna', 'dd', NULL),
       (NULL, 'rna-rna', 'rr', NULL),
       (NULL, 'reaction->compound', 'rc', NULL),
       (NULL, 'protein->reaction', 'pr', NULL),
       (NULL, 'compound->reaction', 'cr', NULL),
       (NULL, 'genetic lethal relationship', 'gl', NULL),
       (NULL, 'protein-metabolite', 'pm', NULL),
       (NULL, 'metabolite-protein', 'mp', NULL);
COMMIT;
SET AUTOCOMMIT=1;
-- -------------------------------------------------
-- Data for table PHYSICAL_ENTITY --------
-- -------------------------------------------------
-- "Entrez Gene"
$ wget ftp://ftp.ncbi.nih.gov/gene/DATA/gene_info.gz
$ gunzip gene_info.gz
$ perl parse_geneinfo.pl > ../parsed_files/physical_entity.new
-- NCBI Entrez Gene
$ mysqlimport -u mhonig -p --columns=primary_accession,gene_symbol,taxonomy_id,description cellnet_kbase /ifs/home/c2b2/af_lab/mhonig/InteractionDB/parsed_files/physical_entity.new
SET AUTOCOMMIT=0;
UPDATE physical_entity
SET accession_db = (select id from db_source where name = 'Entrez Gene');
COMMIT;
SET AUTOCOMMIT=1;

-- Uniprot
$ wget ftp://ftp.uniprot.org/pub/databases/uniprot/current_release/knowledgebase/complete/uniprot_sprot.dat.gz
$ mysqlimport -u mhonig -p -L --columns=primary_accession,gene_symbol cellnet_kbase /ifs/home/c2b2/af_lab/mhonig/InteractionDB/Seed/physical_entity.uniprot
SET AUTOCOMMIT=0;
UPDATE physical_entity
SET accession_db = (select id from db_source where name = 'Uniprot')
WHERE accession_db IS NULL;
COMMIT;
SET AUTOCOMMIT=1;

-- miRDB
SET AUTOCOMMIT=0;
INSERT INTO physical_entity (secondary_accession, accession_db)
	select distinct name_gene2, (select id from db_source where name = 'miRDB')
	from bci_stg
	where name_gene2 like 'hsa-miR%';
COMMIT;
SET AUTOCOMMIT=1;
-- HGNC
SET AUTOCOMMIT=0;
INSERT INTO physical_entity (primary_accession)
SELECT DISTINCT gene_symbol
FROM physical_entity
WHERE gene_symbol NOT IN (select primary_accession from physical_entity)
AND gene_symbol is not null
AND accession_db is not null;
COMMIT;
UPDATE physical_entity
SET accession_db = (select id from db_source where name = 'HGNC')
WHERE accession_db IS NULL AND id <> 0;
COMMIT;
SET AUTOCOMMIT=1;
--
INSERT INTO physical_entity (primary_accession, gene_symbol, taxonomy_id, accession_db)
   select distinct name_gene1, name_gene1, 9606, (select id from db_source where name = 'HGNC')
	from bci_stg 
	where source = 'HPRD_Release9'
	and name_gene1 NOT IN (select primary_accession from physical_entity);
-- ---------------------------------------------------------------
-- Data for table INTERACTOME_DATASET_ALGORITHM -------
-- ---------------------------------------------------------------
-- BCi v1.0 interactome version --
SET AUTOCOMMIT=0;
INSERT INTO interactome_dataset_algorithm (id, interactome_version_id, dataset_id, algorithm_version_id) VALUES
       (NULL,(select id from interactome_version where interactome_id = (select id from interactome where name = 'BCi') and version = '1.0'),
             (select id from dataset where name = 'BCi_GEP_HGU95A_254'),
             (select id from algorithm_version where algorithm_id = (select id from algorithm where name = 'BEIA') and version_identifier = '1.0') );
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table ENTITY_TYPE ------------------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO entity_type (id, name, description) VALUES
       (NULL, 'dna', NULL),
       (NULL, 'rna', NULL),
       (NULL, 'protein', NULL),
       (NULL, 'complex', NULL),
       (NULL, 'small molecule', NULL);
COMMIT;
SET AUTOCOMMIT=1;
-- -----------------------------------------------------
-- Data for table EVIDENCE_TYPE ----------------
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO evidence_type (id, code, description) VALUES 
       (NULL, 'EXP', 'Inferred from Experiment'),
       (NULL, 'IDA', 'Inferred from Direct Assay'),
       (NULL, 'IPI', 'Inferred from Physical Interaction'),
       (NULL, 'IMP', 'Inferred from Mutant Phenotype'),
       (NULL, 'IGI', 'Inferred from Genetic Interaction'),
       (NULL, 'IEP', 'Inferred from Expression Pattern'),
       (NULL, 'ISS', 'Inferred from Sequence or Structural Similarity'),
       (NULL, 'ISO', 'Inferred from Sequence Orthology'),
       (NULL, 'ISA', 'Inferred from Sequence Alignment'),
       (NULL, 'ISM', 'Inferred from Sequence Model'),
       (NULL, 'IGC', 'Inferred from Genomic Context'),
       (NULL, 'RCA', 'Interred from Reviewed Computational Analysis'),
       (NULL, 'TAS', 'Traceable Author Statement'),
       (NULL, 'NAS', 'Non-traceable Author Statement'),
       (NULL, 'IC', 'Inferred from Curator'),
       (NULL, 'ND', 'No biological data available'),
       (NULL, 'IEA', 'Inferred from Electronic Annotation'),
       (NULL, 'NR', 'Not Recorded');
COMMIT;
SET AUTOCOMMIT=1;
-- -------------------------------------------------
-- Data for table ROLE ------------------
-- -------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO role (id, name, description) VALUES
       (NULL, 'transcription factor', NULL),
       (NULL, 'target', NULL),
       (NULL, 'modulator', NULL),
       (NULL, 'catalyst', NULL),
       (NULL, 'unknown', NULL);
COMMIT;
SET AUTOCOMMIT=1;
-- --------------------------------------------------
-- Data for table PHYSICAL_ENTITY --------------
-- --------------------------------------------------
INSERT INTO physical_entity (id, gene_symbol, taxonomy_id, accession_db)
       SELECT DISTINCT NULL, name_gene3, 9606, (select id from db_source where name = 'miRDB')
       FROM bci_stg_cerna;
-- --------------------------------------------------
-- Data for table TAXONOMY --------------
-- --------------------------------------------------
$ wget ftp://ftp.ncbi.nih.gov/pub/taxonomy/taxdmp.zip; $ unzip taxdmp.zip
$ grep 'scientific name' names.dmp | cut -f1,2 -d"|" > ../parsed_files/taxonomy_scientific.pipe
$ sed -e 's/\s*//g' taxonomy.pipe > taxonomy.txt
mysqlimport -u mhonig -p -L cellnet_kbase '--'columns=primary_acc,species_name '--'fields-terminated-by='|' $HOME/InteractionDB/parsed_files/taxonomy.txt
-- -------------------------------------------------------
-- Summary VIEW for interactome_version db sources ------
----------------------------------------------------------
DROP TABLE interactome_identifier_source_sum;
CREATE TABLE interactome_identifier_source_sum AS
SELECT DISTINCT interactome_version.id as "Interactome_Version_Identifier",
		physical_entity.accession_db as "AccessionDB_Identifier", 
		(select name from db_source where id = physical_entity.accession_db) as "DB_Name"
FROM interactome_version, interaction_interactome_version, interaction, interaction_participant, physical_entity, db_source
WHERE interactome_version.id = interaction_interactome_version.interactome_version_id
AND interaction_interactome_version.interaction_id = interaction.id
AND interaction.id = interaction_participant.interaction_id
AND interaction_participant.participant_id = physical_entity.id;