appengine/monorail/sql/queries.sql - infra/infra - Git at Google

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