Changeset 354

Timestamp:

05/16/10 00:28:39 (21 months ago)

Author:

AlexanderPico

Message:

updated to work on plato

Location:

trunk/dbbuilder/src/org/bridgedb/build/scripts

Files:

• PathVisioMySQL_BEEF.sql (modified) (2 diffs)
• PathVisioMySQL_BUILD.sql.template (modified) (5 diffs)
• buildPathVisio.sh (modified) (5 diffs)

trunk/dbbuilder/src/org/bridgedb/build/scripts/PathVisioMySQL_BEEF.sql

@@ -5 +5 @@
 ## Use 'Name' for GO-Slim additions
 INSERT INTO datanode
-SELECT XXXXXX_CS_YYYYYY.gene.Name, XXXXXX_CS_YYYYYY.gene.Code
-FROM XXXXXX_CS_YYYYYY.gene
-WHERE XXXXXX_CS_YYYYYY.gene.Name != ''
-AND XXXXXX_CS_YYYYYY.gene.Code IN ("Tb", "Tc", "Tm");
+SELECT genmapp_XXXXXX_CS_YYYYYY.gene.Name, genmapp_XXXXXX_CS_YYYYYY.gene.Code
+FROM genmapp_XXXXXX_CS_YYYYYY.gene
+WHERE genmapp_XXXXXX_CS_YYYYYY.gene.Name != ''
+AND genmapp_XXXXXX_CS_YYYYYY.gene.Code IN ("Tb", "Tc", "Tm");
 
 ## Beef up 'link'
@@ -14 +14 @@
 INSERT INTO link
 SELECT DISTINCT id_left, code_left, Name, code_right
-FROM XXXXXX_CS_YYYYYY.link left join XXXXXX_CS_YYYYYY.gene on XXXXXX_CS_YYYYYY.link.id_right = XXXXXX_CS_YYYYYY.gene.id
-WHERE XXXXXX_CS_YYYYYY.link.Code_Right IN ("Tb", "Tc", "Tm");
+FROM genmapp_XXXXXX_CS_YYYYYY.link left join genmapp_XXXXXX_CS_YYYYYY.gene on genmapp_XXXXXX_CS_YYYYYY.link.id_right = genmapp_XXXXXX_CS_YYYYYY.gene.id
+WHERE genmapp_XXXXXX_CS_YYYYYY.link.Code_Right IN ("Tb", "Tc", "Tm");
 
 

trunk/dbbuilder/src/org/bridgedb/build/scripts/PathVisioMySQL_BUILD.sql.template

@@ -4 +4 @@
 
 ##Copy Ensembl annotations (Description, Chr) to EntrezGene
-USE XXXXXX_CS_YYYYYY;
+USE genmapp_XXXXXX_CS_YYYYYY;
 #drop index index_id_right on link;
 create index index_id_right on link (ID_Right);
@@ -33 +33 @@
 
 ##Create database
-DROP DATABASE IF EXISTS XXXXXX_Derby_YYYYYY;
-CREATE DATABASE XXXXXX_Derby_YYYYYY;
-USE XXXXXX_Derby_YYYYYY;
+DROP DATABASE IF EXISTS genmapp_XXXXXX_Derby_YYYYYY;
+CREATE DATABASE genmapp_XXXXXX_Derby_YYYYYY;
+USE genmapp_XXXXXX_Derby_YYYYYY;
 
 ## Create 'datanode'
@@ -74 +74 @@
 ## Fill 'info'
 INSERT INTO info 
-SELECT XXXXXX_CS_YYYYYY.Info.Version, '3', 'Ensembl', XXXXXX_CS_YYYYYY.Info.Notes, XXXXXX_CS_YYYYYY.Info.Species, 'GeneProduct' 
-FROM XXXXXX_CS_YYYYYY.Info;
+SELECT genmapp_XXXXXX_CS_YYYYYY.Info.Version, '3', 'Ensembl', genmapp_XXXXXX_CS_YYYYYY.Info.Notes, genmapp_XXXXXX_CS_YYYYYY.Info.Species, 'GeneProduct' 
+FROM genmapp_XXXXXX_CS_YYYYYY.Info;
 
 ## Fill 'attribute'
@@ -81 +81 @@
 #symbol
 INSERT INTO attribute
-SELECT DISTINCT XXXXXX_CS_YYYYYY.attr.ID, XXXXXX_CS_YYYYYY.attr.Code, 
-XXXXXX_CS_YYYYYY.attr.Name, XXXXXX_CS_YYYYYY.attr.Value
-FROM XXXXXX_CS_YYYYYY.attr
-WHERE XXXXXX_CS_YYYYYY.attr.Value != '';
+SELECT DISTINCT genmapp_XXXXXX_CS_YYYYYY.attr.ID, genmapp_XXXXXX_CS_YYYYYY.attr.Code, 
+genmapp_XXXXXX_CS_YYYYYY.attr.Name, genmapp_XXXXXX_CS_YYYYYY.attr.Value
+FROM genmapp_XXXXXX_CS_YYYYYY.attr
+WHERE genmapp_XXXXXX_CS_YYYYYY.attr.Value != '';
 
 ## Fill 'datanode'
 ## From CS Database
 INSERT INTO datanode
-SELECT XXXXXX_CS_YYYYYY.gene.ID, XXXXXX_CS_YYYYYY.gene.Code
-FROM XXXXXX_CS_YYYYYY.gene
-WHERE XXXXXX_CS_YYYYYY.gene.ID != '' 
-AND XXXXXX_CS_YYYYYY.gene.Code IN ("EnXXXXXX","Mb","L","X","Il","Ag","Q","Om","U","Rf","S","Ip","T","Pd","H","M","R","D","Z","F","W","Gg","A","Ti","Ir","N","Uc","Pl","Gm","Bg","Ec","Wg");
+SELECT genmapp_XXXXXX_CS_YYYYYY.gene.ID, genmapp_XXXXXX_CS_YYYYYY.gene.Code
+FROM genmapp_XXXXXX_CS_YYYYYY.gene
+WHERE genmapp_XXXXXX_CS_YYYYYY.gene.ID != '' 
+AND genmapp_XXXXXX_CS_YYYYYY.gene.Code IN ("EnXXXXXX","Mb","L","X","Il","Ag","Q","Om","U","Rf","S","Ip","T","Pd","H","M","R","D","Z","F","W","Gg","A","Ti","Ir","N","Uc","Pl","Gm","Bg","Ec","Wg");
 
 ## Fill 'link'
@@ -98 +98 @@
 INSERT INTO link
 SELECT DISTINCT *
-FROM XXXXXX_CS_YYYYYY.link
-WHERE XXXXXX_CS_YYYYYY.link.Code_Right IN ("EnXXXXXX","Mb","L","X","Il","Ag","Q","Om","U","Rf","S","Ip","T","Pd","H","M","R","D","Z","F","W","Gg","A","Ti","Ir","N","Uc","Pl","Gm","Bg","Ec","Wg");
+FROM genmapp_XXXXXX_CS_YYYYYY.link
+WHERE genmapp_XXXXXX_CS_YYYYYY.link.Code_Right IN ("EnXXXXXX","Mb","L","X","Il","Ag","Q","Om","U","Rf","S","Ip","T","Pd","H","M","R","D","Z","F","W","Gg","A","Ti","Ir","N","Uc","Pl","Gm","Bg","Ec","Wg");
 
-##Add EnXXXXXXsembl IDs to right side of 'link' table
+##Add Ensembl IDs to right side of 'link' table
 INSERT INTO link
 SELECT datanode.id, "EnXXXXXX", datanode.id, "EnXXXXXX"

trunk/dbbuilder/src/org/bridgedb/build/scripts/buildPathVisio.sh

@@ -4 +4 @@
 DatabaseSpecies=$1
 DatabaseDate=$2
-Database=$1_Derby_$2
-DatabaseCS=$1_CS_$2
-
+Database=genmapp_$1_Derby_$2 #everything in mysql on plato needs "genmapp_" prefix
+DatabaseDb=$1_Derby_$2
+DatabaseCS=genmapp_$1_CS_$2
+mysql='mysql --host=mysql-dev.cgl.ucsf.edu --port=13308 -u genmapp -pfun4genmapp'
+mysqldump='mysqldump --host=mysql-dev.cgl.ucsf.edu --port=13308 -u genmapp -pfun4genmapp'
 ScriptsDir=/home/apico/Derby/scripts
 
@@ -14 +16 @@
         wget http://downloads.yeastgenome.org/gene_registry/registry.genenames.tab
         mv registry.genenames.tab ${ScriptsDir}/.
-        mysql -u genmapp -pfun4genmapp -e "create table ${DatabaseCS}.sgd (a varchar(31), b varchar(31), c varchar(31), d varchar(31), e varchar(31), f varchar(31), g varchar(31))";
-        mysql -u genmapp -pfun4genmapp -e "load data local infile '${ScriptsDir}/registry.genenames.tab' into table ${DatabaseCS}.sgd fields terminated by '\t' lines terminated by '\n'";
-        mysql -u genmapp -pfun4genmapp -e "create index i_a on ${DatabaseCS}.sgd (a)";
-        mysql -u genmapp -pfun4genmapp -e "update ${DatabaseCS}.gene, ${DatabaseCS}.sgd set ${DatabaseCS}.gene.ID = ${DatabaseCS}.sgd.g where ${DatabaseCS}.gene.Symbol = ${DatabaseCS}.sgd.a and ${DatabaseCS}.gene.Code = 'D'";
-        mysql -u genmapp -pfun4genmapp -e "update ${DatabaseCS}.attr, ${DatabaseCS}.sgd set ${DatabaseCS}.attr.ID = ${DatabaseCS}.sgd.g where ${DatabaseCS}.attr.Value = ${DatabaseCS}.sgd.a and ${DatabaseCS}.attr.Code = 'D'";
-        mysql -u genmapp -pfun4genmapp -e "alter table ${DatabaseCS}.link add column (ID_Right2 varchar(31))";
-        mysql -u genmapp -pfun4genmapp -e "update ${DatabaseCS}.link set ID_Right2 = ID_Right where Code_Right = 'D'";
-        mysql -u genmapp -pfun4genmapp -e "update ${DatabaseCS}.link, ${DatabaseCS}.sgd set ${DatabaseCS}.link.ID_Right = ${DatabaseCS}.sgd.g where ${DatabaseCS}.link.ID_Right2 = ${DatabaseCS}.sgd.a and ${DatabaseCS}.link.Code_Right = 'D'";
-        mysql -u genmapp -pfun4genmapp -e "alter table ${DatabaseCS}.link drop column ID_Right2";
+        ${mysql} -e "create table ${DatabaseCS}.sgd (a varchar(31), b varchar(31), c varchar(31), d varchar(31), e varchar(31), f varchar(31), g varchar(31))";
+        ${mysql} -e "load data local infile '${ScriptsDir}/registry.genenames.tab' into table ${DatabaseCS}.sgd fields terminated by '\t' lines terminated by '\n'";
+        ${mysql} -e "create index i_a on ${DatabaseCS}.sgd (a)";
+        ${mysql} -e "update ${DatabaseCS}.gene, ${DatabaseCS}.sgd set ${DatabaseCS}.gene.ID = ${DatabaseCS}.sgd.g where ${DatabaseCS}.gene.Symbol = ${DatabaseCS}.sgd.a and ${DatabaseCS}.gene.Code = 'D'";
+        ${mysql} -e "update ${DatabaseCS}.attr, ${DatabaseCS}.sgd set ${DatabaseCS}.attr.ID = ${DatabaseCS}.sgd.g where ${DatabaseCS}.attr.Value = ${DatabaseCS}.sgd.a and ${DatabaseCS}.attr.Code = 'D'";
+        ${mysql} -e "alter table ${DatabaseCS}.link add column (ID_Right2 varchar(31))";
+        ${mysql} -e "update ${DatabaseCS}.link set ID_Right2 = ID_Right where Code_Right = 'D'";
+        ${mysql} -e "update ${DatabaseCS}.link, ${DatabaseCS}.sgd set ${DatabaseCS}.link.ID_Right = ${DatabaseCS}.sgd.g where ${DatabaseCS}.link.ID_Right2 = ${DatabaseCS}.sgd.a and ${DatabaseCS}.link.Code_Right = 'D'";
+        ${mysql} -e "alter table ${DatabaseCS}.link drop column ID_Right2";
 fi
 
@@ -29 +31 @@
 
 ## Build a MySQL Database for PathVisio: 
-mysql -u genmapp -pfun4genmapp < PathVisioMySQL_BUILD.sql 
+${mysql} < PathVisioMySQL_BUILD.sql 
 
 
@@ -36 +38 @@
 #  Otherwise, derby build will crash when semicolons 
 #  happen to precede key words (e.g., "negative")
-#mysql -u genmapp -pfun4genmapp -e "update ${Database}.datanode set backpageText =replace(backpageText, \";\", \".\")";  
+#${mysql} -e "update ${Database}.datanode set backpageText =replace(backpageText, \";\", \".\")";  
 
 # remove pipes from around species name in Info table
-mysql -u genmapp -pfun4genmapp -e "update ${Database}.info set species =replace(species, \"|\", \"\")"; 
+${mysql} -e "update ${Database}.info set species =replace(species, \"|\", \"\")"; 
 
 ## Dump MySQL-PathVisio Database to .sql
 # --net_buffer_length=30K (used to limit size of extended-inserts) 
 # --skip-extended-insert (used to force line-by-line inserts)
-mysqldump --compatible=db2 --no-create-info --skip-add-locks --skip-disable-keys --skip-quote-names --net_buffer_length=30K -u genmapp -pfun4genmapp ${Database} > ${Database}.db2.sql.temp
+${mysqldump} --compatible=db2 --no-create-info --skip-add-locks --skip-disable-keys --skip-quote-names --net_buffer_length=30K ${Database} > ${DatabaseDb}.db2.sql.temp
+## Dump table creation statements separately for 2-step recontruction later (e.g., for bridgedb rest server)
+${mysqldump} --no-data ${Database} > ${DatabaseDb}.db2.sql.tables
 
 ## Clean up dump file
 # Replace in emacs using esc-% (! = all occurances)
 # Replace in vi using :%s/old/new/g (use vi if file is too large for emacs)
-cat ${Database}.db2.sql.temp | sed "s/\/\*/--/g" | sed "s/\\\'/\'\'/g" > ${Database}.db2.sql
+cat ${DatabaseDb}.db2.sql.temp | sed "s/\/\*/--/g" | sed "s/\\\'/\'\'/g" > ${DatabaseDb}.db2.sql
         
 ## Create & Fill Derby via ij tool 
@@ -56 +60 @@
 export CLASSPATH=$DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar:.
 # Run ij Tool
-cat ${ScriptsDir}/PathVisioDerby_BUILD.ij.template | sed "s/XXXXXX/${Database}/g" | perl ${ScriptsDir}/replacePath.pl ${ScriptsDir} > PathVisioDerby_BUILD.ij 
+cat ${ScriptsDir}/PathVisioDerby_BUILD.ij.template | sed "s/XXXXXX/${DatabaseDb}/g" | perl ${ScriptsDir}/replacePath.pl ${ScriptsDir} > PathVisioDerby_BUILD.ij 
 
 java -XX:MaxPermSize=128m -Xmx1024m org.apache.derby.tools.ij PathVisioDerby_BUILD.ij
 
 # Package it up
-mv ${Database} database
-zip -r -0 ${Database}.bridge database/
-mv database ${Database}
+mv ${DatabaseDb} database
+zip -r -0 ${DatabaseDb}.bridge database/
+mv database ${DatabaseDb}
 
 ## Beef up MySQL version of Derby databases for web service deployment
-mysql -u genmapp -pfun4genmapp < PathVisioMySQL_BEEF.sql
-mysqldump --skip-add-locks --skip-disable-keys --skip-quote-names --net_buffer_length=30K -u genmapp -pfun4genmapp ${Database} > ${Database}.sql.dump
+${mysql} < PathVisioMySQL_BEEF.sql
+${mysqldump} --skip-add-locks --skip-disable-keys --skip-quote-names --net_buffer_length=30K ${Database} > ${DatabaseDb}.sql.dump
 
 # report on products (check size > 1.9M)
-ls -lh ${Database}.bridge | echo
+ls -lh ${DatabaseDb}.bridge | echo
 
 # BELOW: Not yet automated
 # - ssh-key authentication not working
-#scp ${Database}.bridge genmappftp@conklinwolf.ucsf.edu: (password=genmappftp)
-#scp ${Database}.bridge jeff@conklinwolf.ucsf.edu:/home2/GenMAPP2_DataTreeRoot/Derby/ (1357jeff)
+#scp ${DatabaseDb}.bridge genmappftp@conklinwolf.ucsf.edu: (password=genmappftp)
+#scp ${DatabaseDb}.bridge jeff@conklinwolf.ucsf.edu:/home2/GenMAPP2_DataTreeRoot/Derby/ (1357jeff)
 
 # Install at WikiPathways