Here we add more resources to the
queries directory, adding SQL queries and associated metadata files to provide additional properties.
If supplied, the metadata file should have the same name as the .sql file, but with a ".query.xml" extends (e.g., PeptideCounts.query.xml) (docs:
query.xsd)
Below we will create two SQL queries in the ms2 schema.
- Add two .sql files in the queries/ms2 directory, as follows:
reportDemo
│ module.properties
└───resources
├───queries
│ └───ms2
│ │
PeptideCounts.sql
│ │
PeptidesWithCounts.sql
│ └───Peptides
│ High Prob Matches.qview.xml
├───reports
└───views
Add the following contents to the files:
PeptideCounts.sqlSELECT
COUNT(Peptides.TrimmedPeptide) AS UniqueCount,
Peptides.Fraction.Run AS Run,
Peptides.TrimmedPeptide
FROM
Peptides
WHERE
Peptides.PeptideProphet >= 0.9
GROUP BY
Peptides.TrimmedPeptide,
Peptides.Fraction.Run
SELECT
pc.UniqueCount,
pc.TrimmedPeptide,
pc.Run,
p.PeptideProphet,
p.FractionalDeltaMass
FROM
PeptideCounts pc
INNER JOIN
Peptides p
ON (p.Fraction.Run = pc.Run AND pc.TrimmedPeptide = p.TrimmedPeptide)
WHERE pc.UniqueCount > 1
Note that the .sql files may contain spaces in their names.
See the SQL Queries
- Build and restart the server.
- To view your SQL queries, go to the schema browser at Admin -> Developer Links -> Schema Browser.
- On the left side, open the nodes ms2-> user-defined queries -> PeptideCounts.
Optionally, you can add metadata to these queries to enhance them. See
Modules: Query Metadata.
