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.sql

SELECT
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

PeptidesWithCounts.sql

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.

Previous Step | Next Step

Discussion

previousnext
 
expand all collapse all