Open Source Monorail

appengine/monorail/sql/queries.sql

+-- 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
+
+
+
+