Last active
June 26, 2020 22:37
-
-
Save brettcannon/b0446107ec5835bbf3feaa2e05ef9b04 to your computer and use it in GitHub Desktop.
Database schema for repo statistics
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE issues ( | |
recording_date TEXT NOT NULL DEFAULT CURRENT_DATE, | |
gh_num INTEGER NOT NULL CHECK(gh_num >= 1), | |
url TEXT NOT NULL, | |
title TEXT NOT NULL, | |
created TEXT NOT NULL, -- Date of issue creation | |
by_team INT NOT NULL DEFAULT 0 CHECK(upvotes >= 0), | |
assignee TEXT NULL, -- Who the issue is assigned to | |
last_OP_comment TEXT NULL, -- When the last comment from the OP was | |
last_team_comment TEXT NULL, -- When the last time a "member" commented | |
type_ TEXT NULL CHECK(type_ IN ('bug', 'enhancement', 'code health')), | |
needs TEXT NULL CHECK(needs IN ('classify', 'triage', 'decision', 'spike', 'spec', 'PR', 'experimenting')), | |
priority INT CHECK(priority >= 0), -- NULL if not specified. | |
reason TEXT NULL CHECK(reason IN ('regression', 'preexisting', 'external', NULL)), | |
partner_ask INT NOT NULL DEFAULT 0 CHECK(partner_ask = 0 OR partner_ask = 1), | |
upvotes INT NOT NULL DEFAULT 0 CHECK(upvotes >= 0), | |
UNIQUE (recording_date, gh_num) -- Don't record an issue twice in the same day. | |
); | |
INSERT INTO issues (gh_num, title, assigned, type, needs, ack, priority, regression, partner_ask, upvotes) | |
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) | |
ON CONFLICT (gh_num, recording_date) DO NOTHING; | |
/* Bug count for the day. */ | |
SELECT COUNT(*) FROM issues WHERE recording_date = date('now') AND type = 'bug'; | |
/* P0s. Tweak for P1s. */ | |
SELECT gh_num, title FROM issues WHERE recording_date = date('now') AND priority = 0; | |
/* Regressions. Tweak for partner asks. */ | |
SELECT gh_num, title FROM issues WHERE recording_date = date('now') AND regression = 1; | |
/* Top requested bugs. Tweak for enhancements. */ | |
SELECT gh_num, title FROM issues WHERE recording_date = date('now') AND type = 'bug' ORDER BY upvotes DESC LIMIT 5; | |
/* ACK SLA of 3 days. */ | |
SELECT gh_num, created FROM issues WHERE by_team = 0 AND last_team_comment IS NULL AND created < datetime('now', '-3 day'); | |
/* XXX These could potentially be done faster as a GH Action. | |
Query to check for NULL 'type_' that aren't 'classify'. | |
Query to check for NULL 'needs'. | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment