blob: a84bc707d7d5b5eb0ce3061a0606dcf0cfc46efd [file] [log] [blame]
-- Copyright 2016 The Chromium Authors. All Rights Reserved.
--
-- Use of this source code is governed by a BSD-style
-- license that can be found in the LICENSE file or at
-- https://developers.google.com/open-source/licenses/bsd
-- Example queries for common operations.
use monorail;
-- --------------------------
-- PROJECT-RELATED QUERIES
-- Look up the id of the project mention in the URL, and get info to display
-- in the page header.
SELECT project_id, summary, state, access
FROM Project
WHERE project_name = 'projb';
-- Get one project to display on the project home page.
SELECT summary, description, state, access
FROM Project
WHERE project_id = 1002;
-- Get the list of members in a project for the project peoeple list page.
SELECT email, role_name
FROM User2Project NATURAL JOIN User
WHERE project_id = 1002
ORDER BY role_name, email;
-- Get the list of all projects where a user has a role for the profile page.
SELECT project_name, role_name
FROM User2Project NATURAL JOIN Project
WHERE user_id = 111 AND state = 'live'
ORDER BY role_name, project_name;
-- TODO: user groups
-- --------------------------
-- ISSUE-RELATED QUERIES
-- Get all issues in a project, ordered by ID, no pagination.
SELECT Issue.*
FROM Issue
WHERE project_id = 1002
ORDER BY Issue.id;
-- Get the second page of issues in a project, ordered by ID. Pagination size is 10.
SELECT Issue.*
FROM Issue
WHERE project_id = 1002
ORDER BY Issue.id
LIMIT 10 OFFSET 10;
-- Get all open issues in a project.
SELECT Issue.*
FROM Issue
LEFT JOIN StatusDef sd1 ON Issue.project_id = sd1.project_id AND LOWER(Issue.status) = LOWER(sd1.status)
WHERE Issue.project_id = 1002
AND (sd1.means_open = TRUE OR sd1.means_open IS NULL); -- this matches oddball or NULL status values
-- Search based on ID.
SELECT Issue.*
FROM Issue
WHERE project_id = 1002 AND Issue.local_id > 8;
-- Search based on status and owner_id.
SELECT Issue.*
FROM Issue
WHERE project_id = 1002 AND LOWER(status) = 'new' AND owner_id = 222;
-- Search based on date modiifed, opened, and closed.
-- TODO: Gives an empty result with the current test data.
SELECT Issue.*
FROM Issue
WHERE project_id = 1002 AND modified > '2011-01-01'
AND opened > '2010-01-01' AND closed > '2010-02-01';
-- Search for has:owner and has:status.
SELECT Issue.*
FROM Issue
WHERE project_id = 1002 AND status != '' AND owner_id IS NOT NULL;
-- All issues in a project that have a label Priority-High
SELECT Issue.*
FROM Issue NATURAL JOIN Issue2Label
WHERE project_id = 1002 AND label = 'Priority-High';
-- All issues in a project that DO NOT have a label Priority-High
SELECT Issue.*
FROM Issue
WHERE project_id = 1002
AND NOT EXISTS (
SELECT *
FROM Issue2Label cond1
WHERE cond1.project_id = Issue.project_id AND cond1.id = Issue.id
AND label = 'Priority-High');
-- Search based on priority and milestone.
SELECT Issue.*
FROM Issue
JOIN Issue2Label cond1 ON Issue.project_id = cond1.project_id AND Issue.id = cond1.id
JOIN Issue2Label cond2 ON Issue.project_id = cond2.project_id AND Issue.id = cond2.id
WHERE Issue.project_id = 1002
AND LOWER(cond1.label) = 'priority-medium'
AND LOWER(cond2.label) = 'milestone-1.1';
-- Permissions checked
-- TODO: add additional permissions
-- Get all comments on issue
-- TODO: add some comment test data
SELECT Comment.*
FROM Comment
WHERE project_id = 1002 AND issue.local_id = 3
ORDER BY created;
-- Get non-deleted comments on an issue
-- TODO: add some comment test data
SELECT Comment.*
FROM Comment
WHERE project_id = 1002 AND issue.local_id = 3 AND deleted_by IS NULL
ORDER BY created;
-- Cross-project search
SELECT Issue.*
FROM Issue
JOIN Issue2Label cond1 ON Issue.project_id = cond1.project_id AND Issue.id = cond1.id
JOIN Issue2Label cond2 ON Issue.project_id = cond2.project_id AND Issue.id = cond2.id
WHERE LOWER(cond1.label) = 'priority-medium'
AND LOWER(cond2.label) = 'type-defect';
-- All issues in a project, sorted by milestone. Milestone order is defined by the rank field of the well-known labels table.
-- Issues with oddball milestones sort lexographcially after issues with well known milestones.
-- Issues which do not have milestone sort last.
-- Note that table sort_N holds the value needed for the Nth sort directive, and table rank_N holds the ranking
-- number of that value, if any.
SELECT Issue.*, sort1.label
FROM Issue
LEFT JOIN (Issue2Label sort1 LEFT JOIN LabelDef rank1
ON sort1.project_id = rank1.project_id AND LOWER(sort1.label) = LOWER(rank1.label))
ON Issue.project_id = sort1.project_id AND Issue.id = sort1.id
AND sort1.label LIKE 'milestone-%'
WHERE Issue.project_id = 1002
ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(sort1.label), LOWER(sort1.label), Issue.id;
-- *Open* issues, sorted by milestone. Any status that is not known to be closed is considered open.
SELECT Issue.project_id, Issue.local_id, Issue.summary, Issue.status, sort1.label
FROM Issue
LEFT JOIN (Issue2Label sort1 LEFT JOIN LabelDef rank1
ON sort1.project_id = rank1.project_id AND LOWER(sort1.label) = LOWER(rank1.label))
ON Issue.project_id = sort1.project_id AND Issue.id = sort1.id
AND sort1.label LIKE 'milestone-%'
LEFT JOIN StatusDef sd1 ON Issue.project_id = sd1.project_id AND LOWER(Issue.status) = LOWER(sd1.status)
WHERE Issue.project_id = 1002
AND (sd1.means_open = TRUE OR sd1.means_open IS NULL) -- this matches oddball or NULL status values
ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(sort1.label), LOWER(sort1.label),
Issue.id; -- tie breaker
-- *Open* issues, sorted by status. Any status that is not known to be closed is considered open.
SELECT Issue.*
FROM Issue
LEFT JOIN StatusDef rank1 ON Issue.project_id = rank1.project_id AND LOWER(Issue.status) = LOWER(rank1.status)
LEFT JOIN StatusDef sr1 ON Issue.project_id = sr1.project_id AND LOWER(Issue.status) = LOWER(sr1.status)
WHERE Issue.project_id = 1002
AND (sr1.means_open = TRUE or sr1.means_open IS NULL) -- this matches oddball or NULL status values
ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(Issue.status), LOWER(Issue.status),
Issue.id; -- tie breaker
-- Realistic query: Open issues with component != printing, sorted by milestone then priority.
SELECT Issue.local_id, Issue.summary, Issue.status, sort1.label, sort2.label
FROM Issue
LEFT JOIN (Issue2Label sort1 LEFT JOIN LabelDef rank1
ON sort1.project_id = rank1.project_id AND LOWER(sort1.label) = LOWER(rank1.label))
ON Issue.project_id = sort1.project_id AND Issue.id = sort1.id
AND sort1.label LIKE 'mstone-%'
LEFT JOIN (Issue2Label sort2 LEFT JOIN LabelDef rank2
ON sort2.project_id = rank2.project_id AND LOWER(sort2.label) = LOWER(rank2.label))
ON Issue.project_id = sort2.project_id AND Issue.id = sort2.id
AND sort2.label LIKE 'pri-%'
LEFT JOIN StatusDef sr1 ON Issue.project_id = sr1.project_id AND LOWER(Issue.status) = LOWER(sr1.status)
WHERE Issue.project_id = 1002
AND (sr1.means_open = TRUE or sr1.means_open IS NULL) -- this matches oddball or NULL status values
AND NOT EXISTS (
SELECT *
FROM Issue2Label cond1
WHERE Issue.project_id = cond1.project_id AND Issue.id = cond1.id
AND LOWER(cond1.label) = 'component-printing'
)
ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(sort1.label), LOWER(sort1.label),
ISNULL(rank2.rank), rank2.rank, ISNULL(sort2.label), LOWER(sort2.label),
Issue.id; -- tie breaker