Using SBEAMS
From SPCTools
| Revision as of 17:47, 16 June 2009 Tfarrah (Talk | contribs) (→Filling in manageable tables) ← Previous diff |
Revision as of 18:03, 16 June 2009 Tfarrah (Talk | contribs) Next diff → |
||
| Line 1: | Line 1: | ||
| + | The Systems Biology Experiment Analysis System provides a customizable framework to meet the needs of modern systems biology research. It is composed of a unified state-of-the-art relational database management system (RDBMS) back end, a collection tools to store, manage, and query experiment information and results in the RDBMS, a web front end for querying the database and providing integrated access to remote data sources, and an interface to existing programs for clustering and other analysis. | ||
| + | |||
| + | PeptideAtlas is implemented in SBEAMS. SBEAMS home is at db.systemsbiology.net. | ||
| + | |||
| ===To query SBEAMS directly=== | ===To query SBEAMS directly=== | ||
| Use Aqua Data Studio on mimas by copying ~tfarrah/bin/datastudio.sh to your own ~/bin and executing. | Use Aqua Data Studio on mimas by copying ~tfarrah/bin/datastudio.sh to your own ~/bin and executing. | ||
Revision as of 18:03, 16 June 2009
The Systems Biology Experiment Analysis System provides a customizable framework to meet the needs of modern systems biology research. It is composed of a unified state-of-the-art relational database management system (RDBMS) back end, a collection tools to store, manage, and query experiment information and results in the RDBMS, a web front end for querying the database and providing integrated access to remote data sources, and an interface to existing programs for clustering and other analysis.
PeptideAtlas is implemented in SBEAMS. SBEAMS home is at db.systemsbiology.net.
Contents |
To query SBEAMS directly
Use Aqua Data Studio on mimas by copying ~tfarrah/bin/datastudio.sh to your own ~/bin and executing. Or, can install on desktop. Ask Eric D. for an account.
hostname: mssql port: default is fine (Terry's account username: usual, password: TFmssql205) click mssql in left sidebar, then server->properties to adjust username/pwd. click button with mag. glass over table for query analyzer Select a database to connect to. Type in queries and press green triangle to submit.
Will need administrator privileges on your account if you want to do more than just view the data.
Augmenting SBEAMS schema: adding tables or columns
Edit two tables using Excel, in $SBEAMS/lib/conf/PeptideAtlas
- PeptideAtlas_table_column.txt
- PeptideAtlas_table_property.txt (add call to ManageTable.cgi at end for manageable tables)
Define new tables in $SBEAMS/lib/perl/SBEAMS/PeptideAtlas/Tables.pm in 3 places, then:
cd $SBEAMS/lib/scripts/Core CONFDIR="../../conf" grep Pept batch_update_driver_tables.csh
Execute two commands that come out of grep. Then,
cd ../../../doc/Proteomics cd $SBEAMS/lib/scripts/Core set dbtype=mssql ./generate_schema.pl \ --table_prop ../../conf/PeptideAtlas/PeptideAtlas_table_property.txt \ --table_col ../../conf/PeptideAtlas/PeptideAtlas_table_column.txt \ --schema_file ../../sql/PeptideAtlas/PeptideAtlas \ --module PeptideAtlas \ --destination_type $dbtype
Optional, to be sure you're doing what you want to do:
- View the files PeptideAtlas_CREATETABLES.mssql and PeptideAtlas_CREATECONSTRAINTS.mssql to be sure they include the queries you want.
- Edit those files to include only your new stuff. For latter file, important constraints are in upper "half". Don't need the housekeeping constraints near bottom.
If you don't edit, no huge speed issue.
$SBEAMS/lib/scripts/Core/runsql.pl -u sbeamsadmin -s PeptideAtlas_CREATETABLES.mssql -i -delim GO # will need password # -i = ignore errors $SBEAMS/lib/scripts/Core/runsql.pl -u sbeamsadmin -s PeptideAtlas_CREATECONSTRAINTS.mssql -i -delim GO # you might notice 2 "Column x is not the same data type as referencing column y" errors while loading this file; you can safely ignore them.
Document changes in appropriate file in $SBEAMS/doc/upgrade/0.24
Filling in manageable tables
- add rows and/or update fields via web interface (e.g. http://db.systemsbiology.net/devTF/sbeams/cgi/PeptideAtlas/ManageTable.cgi?TABLE_NAME=AT_query_option)
- when you want to transfer the contents of these tables to a fresh PeptideAtlas installation, export tables into XML using $SBEAMS/lib/scripts/Core/DataExport.pl. See contents of /net/dblocal/www/html/dev2/sbeams/lib/refdata/PeptideAtlas and its subdirectory for an example. Then import into the new database using $SBEAMS/lib/scripts/Core/DataExport.pl.
Because we now have a mechanism for exporting/importing data, we no longer need to add INSERT/UPDATE queries to $SBEAMS/lib/sql/PeptideAtlas/PeptideAtlas_POPULATE.mssql (or similar files in same/parallel directories)
Changing CGI code
Random notes from Terry's log
load PA build: $SBEAMS/lib/scripts
lib/conf/* describe schema
Add column:
-- edit table in table_columns file (or whatever)
audit columns e.g. created_by
can delete a record by setting record status to D
still appears, but queries can't see it.
-- execute generate_schema.pl -- to help create SQL statement
generate_schema.pl -table_c PeptideAtlas_table_column.txt --table_p
PeptideAtlas_table_property.txt --schema ../../sql/PeptideAtlas/PeptideAtlas
--module PeptideAtlas --dest mssql
== will generate files in ../../sql. these scripts never in svn
-- write out SQL statement and ask Eric to execute
-- document changes in $SBEAMS/doc/upgrade/0.24
(browse through this)
-- run:
cd $SBEAMS/lib/script/Core
./update_driver_tables.pl \
../../conf/PeptideAtlas/PeptideAtlas_table_column.txt
(example gleaned from $SBEAMS/doc/upgrade/0.24)
Mysql slightly diff from our sql server. Latter is preferred. but
PeptideAtlas is by and large mysql compliant
I can monkey around in PeptideAtlas_Test with impunity
Dave has changed this for me.
I should set my devTF (in SBEAMS.conf) to send my log info to my own
log files
Miscellaneous useful SQL queries
If you need to change schema constraints after defining your schema:
- Change the constraint in $SBEAMS/conf/PeptideAtlas/PeptideAtlas_table_column.txt
- Actually make the schema change:
ALTER TABLE employee ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId) ALTER TABLE employee DROP CONSTRAINT pk_employee
