From 9d1df4800291affae3a9923c39b9bab02a078b65 Mon Sep 17 00:00:00 2001 From: athreyab Date: Tue, 28 Feb 2012 19:13:12 +0000 Subject: [PATCH] modifications to add date and column type changes svn path=/; revision=302 --- .../interactions/interactionPathsFromSif.pl | 5 +- .../interactions/interactionPathsFromTsv.pl | 4 +- .../interactions/interactionTables.sql | 97 ++++++++++--------- 3 files changed, 54 insertions(+), 52 deletions(-) diff --git a/Personnel/athreyab/interactions/interactionPathsFromSif.pl b/Personnel/athreyab/interactions/interactionPathsFromSif.pl index eb93e75..cbf4df7 100644 --- a/Personnel/athreyab/interactions/interactionPathsFromSif.pl +++ b/Personnel/athreyab/interactions/interactionPathsFromSif.pl @@ -4,6 +4,7 @@ use Switch; use DBI; use Config::IniFiles; +use POSIX qw/strftime/; require "interactionPath.pl"; @@ -200,8 +201,8 @@ sub importSifData #but in the future, we may allow people other than the devs to upload the data themselves. $interactionId = getInteractionId($accession_left_id,$interaction_type_id,$accession_right_id,$curatorId); if(isEmpty($interactionId)){ - $query = "INSERT INTO Interaction(`object_id_left`,`object_id_right`,`interaction_type_id`,`curator_id`) - VALUES('$accession_left_id','$accession_right_id','$interaction_type_id','$curatorId')"; + $query = "INSERT INTO Interaction(`object_id_left`,`object_id_right`,`interaction_type_id`,`curator_id`,`date`) + VALUES('$accession_left_id','$accession_right_id','$interaction_type_id','$curatorId', '".strftime("%Y-%m-%d", localtime)."')"; executeDbQuery($query); } } diff --git a/Personnel/athreyab/interactions/interactionPathsFromTsv.pl b/Personnel/athreyab/interactions/interactionPathsFromTsv.pl index bf0b174..950f0aa 100644 --- a/Personnel/athreyab/interactions/interactionPathsFromTsv.pl +++ b/Personnel/athreyab/interactions/interactionPathsFromTsv.pl @@ -76,9 +76,9 @@ sub importSeedInteractionData(){ #if not, insert data into to the database if(isEmpty($interaction_id)){ $query = "INSERT INTO Interaction(`object_id_left`,`object_id_right`,`interaction_type_id`,`mode_of_action_id`, - `interactor_type_id_left`,`interactor_type_id_right`,`evidence_id`,`evidence_code_id`,`experiment_id`,`comments`,`curator_id`) + `interactor_type_id_left`,`interactor_type_id_right`,`evidence_id`,`evidence_code_id`,`experiment_id`,`comments`,`curator_id`,`date`) VALUES ('$obj_id_left','$obj_id_right','$interaction_type_id','$mode_of_action_id','$int_type_id_left','$int_type_id_right' - ,'$evidence_id','$evidence_code_id','$experiment_id','$comments','$curator_id')"; + ,'$evidence_id','$evidence_code_id','$experiment_id','$comments','$curator_id', '".strftime("%Y-%m-%d", localtime)."')"; executeDbQuery($query); } } diff --git a/Personnel/athreyab/interactions/interactionTables.sql b/Personnel/athreyab/interactions/interactionTables.sql index 26b5b8a..ad87626 100644 --- a/Personnel/athreyab/interactions/interactionTables.sql +++ b/Personnel/athreyab/interactions/interactionTables.sql @@ -15,6 +15,7 @@ CREATE TABLE IF NOT EXISTS `interaction`.`Species` ( `species_id` INT NOT NULL AUTO_INCREMENT , `species` VARCHAR(45) NOT NULL , `NCBI_taxonomy_id` VARCHAR(45) NULL , + `accession_info_url` VARCHAR(45) NULL , PRIMARY KEY (`species_id`) , UNIQUE INDEX `species_UNIQUE` (`species` ASC) ) ENGINE = InnoDB; @@ -171,7 +172,7 @@ CREATE TABLE IF NOT EXISTS `interaction`.`Interaction` ( `experiment_id` INT NULL , `comments` VARCHAR(255) NULL , `curator_id` INT NULL , - `date` DATE NULL , + `date` VARCHAR(10) NULL , PRIMARY KEY (`interaction_id`) , INDEX `fk_Interactions_1` (`object_id_left` ASC) , INDEX `fk_Interactions_2` (`object_id_right` ASC) , @@ -418,53 +419,53 @@ INSERT INTO `Mode_of_action`(`mode_of_action`) VALUE('down regulates'); -- ----------------------------------------------------- -- Populate `interaction`.`Species` with known values -- ----------------------------------------------------- -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('59689','Arabidopsis lyrata'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3702','Arabidopsis thaliana'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('15368','Brachypodium distachyon'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('6239','Caenorhabditis elegans'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3649','Carica papaya'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3055','Chlamydomonas reinhardtii'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('85681','Citrus clementina'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('2711','Citrus sinensis'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3659','Cucumis sativus'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('7955','Danio rerio'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('7227','Drosophila melanogaster'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('2880','Ectocarpus siliculosus'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('562','Escherichia coli'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('71139','Eucalyptus grandis'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('57918','Fragaria vesca'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('5507','Fusarium oxysporum'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3847','Glycine max'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('9606','Homo sapiens'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('180498','Jatropha curcas'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('29883','Laccaria bicolor'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3983','Manihot esculenta'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3880','Medicago truncatula'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('4155','Mimulus guttatus'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('10090','Mus musculus'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('140110','Nectria haematococca'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('5141','Neurospora crassa'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('272131','Nostoc punctiforme'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('4530','Oryza sativa'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('121225','Pediculus humanus'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('42345','Phoenix dactylifera'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3218','Physcomitrella patens'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3694','Populus trichocarpa'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3760','Prunus persica'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('10116','Rattus norvegicus'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('64495','Rhizopus oryzae'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3988','Ricinus communis'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('4932','Saccharomyces cerevisiae'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('4896','Schizosaccharomyces pombe'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('88036','Selaginella moellendorffii'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('4113','Solanum tuberosum'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('4558','Sorghum bicolor'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('3641','Theobroma cacao'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('29910','Tolypocladium inflatum'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('63577','Trichoderma atroviride'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('1206','Trichodesmium erythraeum'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('29760','Vitis vinifera'); -INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`) VALUES('4577','Zea mays'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('59689','Arabidopsis lyrata','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3702','Arabidopsis thaliana','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('15368','Brachypodium distachyon','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('6239','Caenorhabditis elegans','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3649','Carica papaya','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3055','Chlamydomonas reinhardtii','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('85681','Citrus clementina','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('2711','Citrus sinensis','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3659','Cucumis sativus','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('7955','Danio rerio','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('7227','Drosophila melanogaster','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('2880','Ectocarpus siliculosus','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('562','Escherichia coli','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('71139','Eucalyptus grandis','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('57918','Fragaria vesca','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('5507','Fusarium oxysporum','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3847','Glycine max','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('9606','Homo sapiens','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('180498','Jatropha curcas','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('29883','Laccaria bicolor','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3983','Manihot esculenta','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3880','Medicago truncatula','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('4155','Mimulus guttatus','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('10090','Mus musculus','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('140110','Nectria haematococca','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('5141','Neurospora crassa','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('272131','Nostoc punctiforme','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('4530','Oryza sativa','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('121225','Pediculus humanus','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('42345','Phoenix dactylifera','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3218','Physcomitrella patens','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3694','Populus trichocarpa','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3760','Prunus persica','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('10116','Rattus norvegicus','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('64495','Rhizopus oryzae','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3988','Ricinus communis','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('4932','Saccharomyces cerevisiae','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('4896','Schizosaccharomyces pombe','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('88036','Selaginella moellendorffii','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('4113','Solanum tuberosum','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('4558','Sorghum bicolor','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('3641','Theobroma cacao','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('29910','Tolypocladium inflatum','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('63577','Trichoderma atroviride','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('1206','Trichodesmium erythraeum','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('29760','Vitis vinifera','gramene'); +INSERT INTO `Species`(`NCBI_taxonomy_id`,`species`,`accession_info_url`) VALUES('4577','Zea mays','gramene'); SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; -- 2.34.1