OLD | NEW |
(Empty) | |
| 1 -- Copyright 2016 The Chromium Authors. All Rights Reserved. |
| 2 -- |
| 3 -- Use of this source code is governed by a BSD-style |
| 4 -- license that can be found in the LICENSE file or at |
| 5 -- https://developers.google.com/open-source/licenses/bsd |
| 6 |
| 7 |
| 8 -- Example queries for common operations. |
| 9 |
| 10 use monorail; |
| 11 |
| 12 -- -------------------------- |
| 13 -- PROJECT-RELATED QUERIES |
| 14 |
| 15 -- Look up the id of the project mention in the URL, and get info to display |
| 16 -- in the page header. |
| 17 SELECT project_id, summary, state, access |
| 18 FROM Project |
| 19 WHERE project_name = 'projb'; |
| 20 |
| 21 -- Get one project to display on the project home page. |
| 22 SELECT summary, description, state, access |
| 23 FROM Project |
| 24 WHERE project_id = 1002; |
| 25 |
| 26 -- Get the list of members in a project for the project peoeple list page. |
| 27 SELECT email, role_name |
| 28 FROM User2Project NATURAL JOIN User |
| 29 WHERE project_id = 1002 |
| 30 ORDER BY role_name, email; |
| 31 |
| 32 -- Get the list of all projects where a user has a role for the profile page. |
| 33 SELECT project_name, role_name |
| 34 FROM User2Project NATURAL JOIN Project |
| 35 WHERE user_id = 111 AND state = 'live' |
| 36 ORDER BY role_name, project_name; |
| 37 |
| 38 |
| 39 -- TODO: user groups |
| 40 |
| 41 |
| 42 -- -------------------------- |
| 43 -- ISSUE-RELATED QUERIES |
| 44 |
| 45 -- Get all issues in a project, ordered by ID, no pagination. |
| 46 SELECT Issue.* |
| 47 FROM Issue |
| 48 WHERE project_id = 1002 |
| 49 ORDER BY Issue.id; |
| 50 |
| 51 -- Get the second page of issues in a project, ordered by ID. Pagination size is
10. |
| 52 SELECT Issue.* |
| 53 FROM Issue |
| 54 WHERE project_id = 1002 |
| 55 ORDER BY Issue.id |
| 56 LIMIT 10 OFFSET 10; |
| 57 |
| 58 -- Get all open issues in a project. |
| 59 SELECT Issue.* |
| 60 FROM Issue |
| 61 LEFT JOIN StatusDef sd1 ON Issue.project_id = sd1.project_id AND LOWER(Issue
.status) = LOWER(sd1.status) |
| 62 WHERE Issue.project_id = 1002 |
| 63 AND (sd1.means_open = TRUE OR sd1.means_open IS NULL); -- this matches od
dball or NULL status values |
| 64 |
| 65 -- Search based on ID. |
| 66 SELECT Issue.* |
| 67 FROM Issue |
| 68 WHERE project_id = 1002 AND Issue.local_id > 8; |
| 69 |
| 70 |
| 71 -- Search based on status and owner_id. |
| 72 SELECT Issue.* |
| 73 FROM Issue |
| 74 WHERE project_id = 1002 AND LOWER(status) = 'new' AND owner_id = 222; |
| 75 |
| 76 -- Search based on date modiifed, opened, and closed. |
| 77 -- TODO: Gives an empty result with the current test data. |
| 78 SELECT Issue.* |
| 79 FROM Issue |
| 80 WHERE project_id = 1002 AND modified > '2011-01-01' |
| 81 AND opened > '2010-01-01' AND closed > '2010-02-01'; |
| 82 |
| 83 |
| 84 -- Search for has:owner and has:status. |
| 85 SELECT Issue.* |
| 86 FROM Issue |
| 87 WHERE project_id = 1002 AND status != '' AND owner_id IS NOT NULL; |
| 88 |
| 89 |
| 90 -- All issues in a project that have a label Priority-High |
| 91 SELECT Issue.* |
| 92 FROM Issue NATURAL JOIN Issue2Label |
| 93 WHERE project_id = 1002 AND label = 'Priority-High'; |
| 94 |
| 95 -- All issues in a project that DO NOT have a label Priority-High |
| 96 SELECT Issue.* |
| 97 FROM Issue |
| 98 WHERE project_id = 1002 |
| 99 AND NOT EXISTS ( |
| 100 SELECT * |
| 101 FROM Issue2Label cond1 |
| 102 WHERE cond1.project_id = Issue.project_id AND cond1.id = Issue.id |
| 103 AND label = 'Priority-High'); |
| 104 |
| 105 |
| 106 -- Search based on priority and milestone. |
| 107 SELECT Issue.* |
| 108 FROM Issue |
| 109 JOIN Issue2Label cond1 ON Issue.project_id = cond1.project_id AND Issue.id =
cond1.id |
| 110 JOIN Issue2Label cond2 ON Issue.project_id = cond2.project_id AND Issue.id =
cond2.id |
| 111 WHERE Issue.project_id = 1002 |
| 112 AND LOWER(cond1.label) = 'priority-medium' |
| 113 AND LOWER(cond2.label) = 'milestone-1.1'; |
| 114 |
| 115 |
| 116 -- Permissions checked |
| 117 -- TODO: add additional permissions |
| 118 |
| 119 |
| 120 -- Get all comments on issue |
| 121 -- TODO: add some comment test data |
| 122 SELECT Comment.* |
| 123 FROM Comment |
| 124 WHERE project_id = 1002 AND issue.local_id = 3 |
| 125 ORDER BY created; |
| 126 |
| 127 |
| 128 -- Get non-deleted comments on an issue |
| 129 -- TODO: add some comment test data |
| 130 SELECT Comment.* |
| 131 FROM Comment |
| 132 WHERE project_id = 1002 AND issue.local_id = 3 AND deleted_by IS NULL |
| 133 ORDER BY created; |
| 134 |
| 135 -- Cross-project search |
| 136 SELECT Issue.* |
| 137 FROM Issue |
| 138 JOIN Issue2Label cond1 ON Issue.project_id = cond1.project_id AND Issue.id =
cond1.id |
| 139 JOIN Issue2Label cond2 ON Issue.project_id = cond2.project_id AND Issue.id =
cond2.id |
| 140 WHERE LOWER(cond1.label) = 'priority-medium' |
| 141 AND LOWER(cond2.label) = 'type-defect'; |
| 142 |
| 143 -- All issues in a project, sorted by milestone. Milestone order is defined by
the rank field of the well-known labels table. |
| 144 -- Issues with oddball milestones sort lexographcially after issues with well kn
own milestones. |
| 145 -- Issues which do not have milestone sort last. |
| 146 -- Note that table sort_N holds the value needed for the Nth sort directive, and
table rank_N holds the ranking |
| 147 -- number of that value, if any. |
| 148 SELECT Issue.*, sort1.label |
| 149 FROM Issue |
| 150 LEFT JOIN (Issue2Label sort1 LEFT JOIN LabelDef rank1 |
| 151 ON sort1.project_id = rank1.project_id AND LOWER(sort1.label) = L
OWER(rank1.label)) |
| 152 ON Issue.project_id = sort1.project_id AND Issue.id = sort1.id |
| 153 AND sort1.label LIKE 'milestone-%' |
| 154 WHERE Issue.project_id = 1002 |
| 155 ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(sort1.label), LOWER(sort1.label)
, Issue.id; |
| 156 |
| 157 -- *Open* issues, sorted by milestone. Any status that is not known to be close
d is considered open. |
| 158 SELECT Issue.project_id, Issue.local_id, Issue.summary, Issue.status, sort1.labe
l |
| 159 FROM Issue |
| 160 LEFT JOIN (Issue2Label sort1 LEFT JOIN LabelDef rank1 |
| 161 ON sort1.project_id = rank1.project_id AND LOWER(sort1.label) = L
OWER(rank1.label)) |
| 162 ON Issue.project_id = sort1.project_id AND Issue.id = sort1.id |
| 163 AND sort1.label LIKE 'milestone-%' |
| 164 LEFT JOIN StatusDef sd1 ON Issue.project_id = sd1.project_id AND LOWER(Issue
.status) = LOWER(sd1.status) |
| 165 WHERE Issue.project_id = 1002 |
| 166 AND (sd1.means_open = TRUE OR sd1.means_open IS NULL) -- this matches odd
ball or NULL status values |
| 167 ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(sort1.label), LOWER(sort1.label)
, |
| 168 Issue.id; -- tie breaker |
| 169 |
| 170 -- *Open* issues, sorted by status. Any status that is not known to be closed i
s considered open. |
| 171 SELECT Issue.* |
| 172 FROM Issue |
| 173 LEFT JOIN StatusDef rank1 ON Issue.project_id = rank1.project_id AND LOWER(I
ssue.status) = LOWER(rank1.status) |
| 174 LEFT JOIN StatusDef sr1 ON Issue.project_id = sr1.project_id AND LOWER(Issue
.status) = LOWER(sr1.status) |
| 175 WHERE Issue.project_id = 1002 |
| 176 AND (sr1.means_open = TRUE or sr1.means_open IS NULL) -- this matches odd
ball or NULL status values |
| 177 ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(Issue.status), LOWER(Issue.statu
s), |
| 178 Issue.id; -- tie breaker |
| 179 |
| 180 |
| 181 -- Realistic query: Open issues with component != printing, sorted by milestone
then priority. |
| 182 SELECT Issue.local_id, Issue.summary, Issue.status, sort1.label, sort2.label |
| 183 FROM Issue |
| 184 LEFT JOIN (Issue2Label sort1 LEFT JOIN LabelDef rank1 |
| 185 ON sort1.project_id = rank1.project_id AND LOWER(sort1.label) = L
OWER(rank1.label)) |
| 186 ON Issue.project_id = sort1.project_id AND Issue.id = sort1.id |
| 187 AND sort1.label LIKE 'mstone-%' |
| 188 LEFT JOIN (Issue2Label sort2 LEFT JOIN LabelDef rank2 |
| 189 ON sort2.project_id = rank2.project_id AND LOWER(sort2.label) = L
OWER(rank2.label)) |
| 190 ON Issue.project_id = sort2.project_id AND Issue.id = sort2.id |
| 191 AND sort2.label LIKE 'pri-%' |
| 192 LEFT JOIN StatusDef sr1 ON Issue.project_id = sr1.project_id AND LOWER(Issue
.status) = LOWER(sr1.status) |
| 193 WHERE Issue.project_id = 1002 |
| 194 AND (sr1.means_open = TRUE or sr1.means_open IS NULL) -- this matches odd
ball or NULL status values |
| 195 AND NOT EXISTS ( |
| 196 SELECT * |
| 197 FROM Issue2Label cond1 |
| 198 WHERE Issue.project_id = cond1.project_id AND Issue.id = cond1.id |
| 199 AND LOWER(cond1.label) = 'component-printing' |
| 200 ) |
| 201 ORDER BY ISNULL(rank1.rank), rank1.rank, ISNULL(sort1.label), LOWER(sort1.label)
, |
| 202 ISNULL(rank2.rank), rank2.rank, ISNULL(sort2.label), LOWER(sort2.label)
, |
| 203 Issue.id; -- tie breaker |
| 204 |
| 205 |
| 206 |
| 207 |
OLD | NEW |