forked from colin-combe/CLMS-UI
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathsql.txt
125 lines (80 loc) · 5.61 KB
/
sql.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
1. table export
pg_dump -fc
-t next_ids -t v_gettablesizes -t xi_config -t acquisition_id -t base_setting -t base_setting_id_seq -t chosen_label_scheme -t crosslinker_id_seq -t enzyme -t enzyme_id_seq -t fdrlevel -t ion -t ion_id_seq -t label -t label_id_seq -t label_scheme -t label_scheme_id_seq -t loss -t loss_id_seq -t match_type -t match_type_id_seq -t modification -t modification_id_seq -t user_groups -t users -t score_id_seq -t crosslinker -t version_number -t sequence_file -t chosen_crosslinker_pkey -t parameter_set_pkey -t search_sequencedb_pkey -t chosen_crosslinker -t search_sequencedb -t layouts -t search_acquisition -t acquisition -t chosen_modification_pkey -t chosen_ions -t chosen_losses -t chosen_modification -t run -t parameter_set -t manual_annotations -t storage_ids -t protein_pkey -t search -t protein -t has_protein -t peptide -t spectrum -t spectrum_match -t matched_peptide -t spectrum_peak -t v_export_materialized
2. write search_id into matched_peptide
update matched_peptide set search_id = (select search_id from spectrum_match where id = matched_peptide.match_id);
3. runname:
CREATE TABLE run_names (id SERIAL, name VARCHAR) CONSTRAINT run_name_id_pkey PRIMARY KEY (id);
Insert into run_names (name) select distcinct run_name from v_export_materialized;
ALTER TABLE spectrum ADD run_id Integer CONSTRAINT spectrum_run_id_fk REFERENCES run_names (id);
UPDATE spectrum set run_id = (select run_id from run_names where run_name = (select run_name from v_export_materialized where spectrum_id = id and dynamic_rank));
4. score
========================================================================
SELECT sm.id
FROM
(select * from spectrum_match where search_id = 624 and dynamic_rank) sm
INNER JOIN
(select * from matched_peptide where search_id = 624) mp
ON sm.id = mp.match_id
INNER JOIN has_protein hp ON mp.peptide_id = hp.peptide_id
INNER JOIN protein p ON hp.protein_id = p.id
INNER JOIN peptide pep ON mp.peptide_id = pep.id
INNER JOIN spectrum sp ON sm.spectrum_id = sp.id
ORDER BY score DESC, sm.id, match_type;
17s
========================================================================
SELECT
mp.match_id, mp.match_type, mp.peptide_id,
mp.link_position + 1 AS link_position,
sm.score, sm.autovalidated, sm.validated, sm.search_id, sm.precursor_charge,
peptide.sequence AS pepseq, sp.scan_number
INTO TEMPORARY tempMatchPeps
FROM
(SELECT * FROM matched_peptide WHERE search_id = 624) mp INNER JOIN
(
SELECT sm.* FROM spectrum_match sm
INNER JOIN search s ON sm.search_id = 624 AND s.id=624 WHERE
(sm.search_id = 624 AND s.random_id = '45155-84658-40554-08490')
AND dynamic_rank = true
)
sm ON sm.id = mp.match_id
INNER JOIN peptide ON mp.peptide_id = peptide.id
INNER JOIN spectrum sp on sm.spectrum_id = sp.id
;
SELECT
has_protein.peptide_id, has_protein.protein_id,
(peptide_position + 1) as peptide_position,
(array_agg(protein.accession_number))[1] as accession
INTO TEMPORARY tempHasProt
FROM has_protein, tempMatchPeps, protein
WHERE tempMatchPeps.peptide_id = has_protein.peptide_id
AND has_protein.protein_id = protein.id
GROUP BY has_protein.peptide_id, has_protein.protein_id, peptide_position;
SELECT protein.id, (array_agg(protein.name))[1] AS name, (array_agg(protein.description))[1] AS description, (array_agg(protein.sequence))[1] AS sequence, (array_agg(protein.protein_length))[1] AS size, (array_agg(protein.accession_number))[1] AS accession FROM protein, tempHasProt WHERE tempHasProt.protein_id = protein.id GROUP BY protein.id;
SELECT
tempMatchPeps.*, proteins, positions
FROM
tempMatchPeps,
(SELECT peptide_id, array_to_string(array_agg(protein_id), ',') as proteins, array_to_string(array_agg(peptide_position), ',') as positions FROM tempHasProt GROUP BY tempHasProt.peptide_id) AS prt
WHERE
tempMatchPeps.peptide_id = prt.peptide_id ORDER BY score DESC, match_id, match_type;
-or-
SELECT protein.accession_number as id, (array_agg(protein.name))[1] AS name, (array_agg(protein.description))[1] AS description, (array_agg(protein.sequence))[1] AS sequence, (array_agg(protein.protein_length))[1] AS size, (array_agg(protein.accession_number))[1] AS accession FROM protein, tempHasProt WHERE tempHasProt.protein_id = protein.id GROUP BY protein.accession_number;
SELECT tempMatchPeps.*, proteins, positions FROM tempMatchPeps, (SELECT peptide_id, array_to_string(array_agg(accession), ',') as proteins, array_to_string(array_agg(peptide_position), ',') as positions FROM tempHasProt GROUP BY tempHasProt.peptide_id) AS prt WHERE tempMatchPeps.peptide_id = prt.peptide_id ORDER BY score DESC, match_id, match_type;
-----------------------------------------
-- ADDING sources to scans
-----------------------------------------
create table spectrum_source (id serial CONSTRAINT PK_spectrum_source PRIMARY KEY, name varchar);
alter table add column source_id int CONSTRAINT fk_spectrum_spectrum_source REFERENCES spectrum_source(id);
CREATE index spectrum_source_id_idx ON spectrum (source_id);
ALTER TABLE spectrum_match DROP COLUMN peptide_fdr;
ALTER TABLE spectrum_match DROP COLUMN link_fdr;
ALTER TABLE spectrum_match DROP COLUMN ppi_fdr;
ALTER TABLE spectrum_match ADD ScoreDelta REAL;
ALTER TABLE spectrum_match ADD ScoreModDelta REAL;
ALTER TABLE spectrum_match ADD ScorePeptide1MatchedConservative SMALLINT;
ALTER TABLE spectrum_match ADD ScorePeptide2MatchedConservative SMALLINT;
ALTER TABLE spectrum_match ADD ScoreFragmentsMatchedConservative SMALLINT;
ALTER TABLE spectrum_match ADD ScoreSpectrumPeaksExplained REAL;
ALTER TABLE spectrum_match ADD ScoreSpectrumIntensityExplained REAL;
ALTER TABLE spectrum_match ADD ScoreLinkSiteDelta REAL;