-
Notifications
You must be signed in to change notification settings - Fork 1
/
queries.sql
164 lines (139 loc) · 3.49 KB
/
queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
-- name: DeleteAllForBuild :exec
DELETE from results
WHERE build_id = ?;
-- name: deleteBuild :exec
DELETE from builds
WHERE build_id = ?;
-- name: GetBuild :one
SELECT * FROM builds
WHERE build_id = ?;
-- name: GetCategories :many
SELECT DISTINCT category
FROM pkgs
ORDER BY category;
-- name: getLatestBuilds :many
SELECT * FROM builds
ORDER BY build_ts DESC
LIMIT 1000;
-- name: GetLatestBuildsPerPlatform :many
-- This relies on the fact that IDs are monotonically increasing, so a newer
-- build will have a higher ID. There is probably a cleaner way.
SELECT * FROM builds
WHERE build_id IN (
SELECT DISTINCT
MAX(build_id) OVER (PARTITION BY platform, branch, compiler, build_user)
FROM builds
)
ORDER BY build_ts DESC
LIMIT 1000;
-- name: getAllPkgsMatching :many
SELECT pkgpath
FROM pkgpaths
WHERE pkgpath LIKE @name
ORDER BY pkgpath;
-- name: GetAllPkgResults :many
SELECT r.result_id, r.pkg_name, r.build_status, r.breaks, b.build_id, b.platform, b.build_ts, b.branch, b.compiler, b.build_user
FROM results r, builds b
WHERE r.build_id == b.build_id AND r.pkg_id == ?
ORDER BY b.build_ts DESC;
-- name: GetSingleResult :one
SELECT
r.result_id,
r.pkg_name,
r.build_status,
r.failed_deps,
r.breaks,
p.category,
p.dir,
b.build_id,
b.platform,
b.build_ts,
b.branch,
b.compiler,
b.build_user,
b.report_url
FROM results r, builds b, pkgs p
WHERE r.build_id == b.build_id AND r.pkg_id == p.pkg_id AND r.result_id == ?;
-- name: GetSingleResultByPkgName :one
SELECT
r.result_id,
r.pkg_name,
r.build_status,
r.failed_deps,
r.breaks,
p.category,
p.dir
FROM results r, pkgs p
WHERE r.build_id == ? AND r.pkg_id == p.pkg_id AND r.pkg_name == ?;
-- name: GetSingleResultIDByPkgName :one
SELECT result_id
FROM results
WHERE build_id == ? and pkg_name == ?;
-- name: GetPkgsInCategory :many
SELECT DISTINCT dir
FROM pkgs
WHERE category = ?
ORDER BY dir;
-- name: GetPkgID :one
SELECT pkg_id FROM pkgs
WHERE category == ? and dir == ?;
-- name: GetResultsInCategory :many
SELECT r.*, p.*
FROM results r
JOIN pkgs p ON (r.pkg_id == p.pkg_id)
WHERE p.category == ? AND r.build_id == ?;
-- name: GetPkgsBreakingMostOthers :many
SELECT
r.result_id,
(p.category || p.dir) AS pkg_path,
r.pkg_name,
r.build_status,
r.failed_deps,
r.breaks
FROM results r
JOIN pkgs p ON (r.pkg_id == p.pkg_id)
WHERE r.build_id == ? AND r.build_status > 0
ORDER BY r.breaks DESC
LIMIT 100;
-- name: GetSentinelStatus :many
-- Get the status and failed dependencies of the sentinel packages
-- (bulk-test-*).
SELECT *
FROM results
WHERE build_id == ? AND pkg_id IN (
SELECT pkg_id
FROM pkgs
WHERE dir LIKE 'bulk-test-%'
);
-- name: getPkgsBrokenBy :many
SELECT
r.result_id,
(p.category || p.dir) AS pkg_path,
r.pkg_name,
r.build_status,
r.failed_deps,
r.breaks
FROM results r
JOIN pkgs p ON (r.pkg_id == p.pkg_id)
WHERE r.build_id = ? AND
r.failed_deps LIKE ?;
-- name: PutBuild :one
-- PutBuild writes the Build record to the DB and returns the ID.
INSERT INTO builds
(platform, build_ts, branch, compiler, build_user, report_url, num_ok,
num_prefailed, num_failed, num_indirect_failed, num_indirect_prefailed)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
RETURNING build_id;
-- name: PutPkg :exec
INSERT OR IGNORE INTO pkgs
(category, dir)
VALUES (?, ?);
-- name: PutResult :exec
INSERT INTO results
(build_id, pkg_id, pkg_name, build_status, breaks, failed_deps)
VALUES (?, ?, ?, ?, ?, ?);
-- name: SetBuildLastError :exec
-- SetBuildLastError sets the last_error column on a given build.
UPDATE builds
SET last_error = ?
WHERE build_id = ?;