Using SBEAMS
From SPCTools
| Revision as of 19:01, 15 June 2009 Tfarrah (Talk | contribs) ← Previous diff |
Revision as of 19:06, 15 June 2009 Tfarrah (Talk | contribs) (→Miscellaneous useful SQL queries) Next diff → |
||
| Line 1: | Line 1: | ||
| + | ===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. | ||
| + | <pre> | ||
| + | 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. | ||
| + | </pre> | ||
| + | Will need administrator privileges on your account if you want to do more than just view the data. | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | 6/9/09: Eric D. gave me permission to alter DB via Aqua Data Studio | ||
| + | |||
| + | 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 don't need to change schema bec. ProtProphet data is already there. (?) | ||
| + | Just need to change view. | ||
| + | |||
| + | PeptideMapping table == mapping of peptide in particular build. | ||
| + | No analagous thing for protein. Could add ProtProph to all peptides | ||
| + | mapping to that prot, or could create a new table. | ||
| + | 1) need to find place in schema to hold value | ||
| + | 2) need to change view | ||
| + | |||
| + | 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=== | ===Miscellaneous useful SQL queries=== | ||
| If you need to change schema constraints after defining your schema: | If you need to change schema constraints after defining your schema: | ||
Revision as of 19:06, 15 June 2009
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.
6/9/09: Eric D. gave me permission to alter DB via Aqua Data Studio
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 don't need to change schema bec. ProtProphet data is already there. (?) Just need to change view.
PeptideMapping table == mapping of peptide in particular build.
No analagous thing for protein. Could add ProtProph to all peptides mapping to that prot, or could create a new table.
1) need to find place in schema to hold value 2) need to change view
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
