internal/store/runners.go
195 lines · 6866 bytes
1package store
2
3import "sort"
4
5// Runner is one runner key as the instance admin sees it.
6type Runner struct {
7 Username string `json:"username"`
8 Fingerprint string `json:"fingerprint"`
9 KeyID int64 `json:"-"`
10 LastSeen string `json:"last_seen"`
11 // Scope is what the runner asked for: comma-joined owner/name, ""
12 // for any. admin runners replaces it with the attachments for a
13 // runner key.
14 Scope string `json:"scope,omitempty"`
15 // The build it holds, if any.
16 BuildRepo string `json:"build_repo,omitempty"`
17 BuildNumber int64 `json:"build_number,omitempty"`
18 BuildJob string `json:"build_job,omitempty"`
19 StartedAt string `json:"started_at,omitempty"`
20}
21
22// RepoRunner is one key attached to a repository, as repo runner list
23// shows it.
24type RepoRunner struct {
25 Fingerprint string `json:"fingerprint"`
26 Algo string `json:"algo"`
27 Username string `json:"username"`
28 AddedAt string `json:"added_at"`
29 LastSeen string `json:"last_seen,omitempty"`
30 BuildRepo string `json:"build_repo,omitempty"`
31 BuildNumber int64 `json:"build_number,omitempty"`
32 BuildJob string `json:"build_job,omitempty"`
33 StartedAt string `json:"started_at,omitempty"`
34}
35
36// AttachRunner lets a key claim a repository's builds. Attaching twice is
37// one row.
38func (s *Store) AttachRunner(keyID, repoID int64) error {
39 _, err := s.DB.Exec("INSERT OR IGNORE INTO runner_repos (key_id, repo_id) VALUES (?, ?)", keyID, repoID)
40 return err
41}
42
43// DetachRunner removes one attachment by fingerprint. The key itself stays.
44func (s *Store) DetachRunner(repoID int64, fingerprint string) error {
45 res, err := s.DB.Exec(`DELETE FROM runner_repos WHERE repo_id = ?
46 AND key_id = (SELECT id FROM ssh_keys WHERE fingerprint = ?)`, repoID, fingerprint)
47 if err != nil {
48 return err
49 }
50 if n, _ := res.RowsAffected(); n == 0 {
51 return ErrNotFound
52 }
53 return nil
54}
55
56// RunnerRepoIDs is every repository a key is attached to.
57func (s *Store) RunnerRepoIDs(keyID int64) ([]int64, error) {
58 rows, err := s.DB.Query("SELECT repo_id FROM runner_repos WHERE key_id = ? ORDER BY repo_id", keyID)
59 if err != nil {
60 return nil, err
61 }
62 defer rows.Close()
63 var ids []int64
64 for rows.Next() {
65 var id int64
66 if err := rows.Scan(&id); err != nil {
67 return nil, err
68 }
69 ids = append(ids, id)
70 }
71 return ids, rows.Err()
72}
73
74// RunnerRepoPaths is RunnerRepoIDs as owner/name, sorted.
75func (s *Store) RunnerRepoPaths(keyID int64) ([]string, error) {
76 rows, err := s.DB.Query(`SELECT COALESCE(u.username, o.name) || '/' || r.name
77 FROM runner_repos rr JOIN repos r ON r.id = rr.repo_id
78 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
79 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
80 WHERE rr.key_id = ?`, keyID)
81 if err != nil {
82 return nil, err
83 }
84 defer rows.Close()
85 var paths []string
86 for rows.Next() {
87 var p string
88 if err := rows.Scan(&p); err != nil {
89 return nil, err
90 }
91 paths = append(paths, p)
92 }
93 sort.Strings(paths)
94 return paths, rows.Err()
95}
96
97// RunnerAttached reports whether a key may claim a repository's builds.
98func (s *Store) RunnerAttached(keyID, repoID int64) (bool, error) {
99 var n int
100 err := s.DB.QueryRow("SELECT count(*) FROM runner_repos WHERE key_id = ? AND repo_id = ?", keyID, repoID).Scan(&n)
101 return n > 0, err
102}
103
104// ListRepoRunners is every key attached to a repository with its last
105// poll and the build it holds, oldest attachment first.
106func (s *Store) ListRepoRunners(repoID int64) ([]RepoRunner, error) {
107 rows, err := s.DB.Query(`SELECT k.fingerprint, k.algo, u.username, rr.added_at,
108 COALESCE(rs.last_seen, ''),
109 COALESCE(COALESCE(bu.username, bo.name) || '/' || br.name, ''),
110 COALESCE(b.number, 0), COALESCE(b.job, ''), COALESCE(b.started_at, '')
111 FROM runner_repos rr
112 JOIN ssh_keys k ON k.id = rr.key_id
113 JOIN users u ON u.id = k.user_id
114 LEFT JOIN runner_seen rs ON rs.key_id = rr.key_id
115 LEFT JOIN builds b ON b.id = rs.build_id AND b.status = 'running'
116 LEFT JOIN repos br ON br.id = b.repo_id
117 LEFT JOIN users bu ON br.owner_kind = 'user' AND bu.id = br.owner_id
118 LEFT JOIN orgs bo ON br.owner_kind = 'org' AND bo.id = br.owner_id
119 WHERE rr.repo_id = ? ORDER BY rr.added_at, k.id`, repoID)
120 if err != nil {
121 return nil, err
122 }
123 defer rows.Close()
124 var out []RepoRunner
125 for rows.Next() {
126 var r RepoRunner
127 if err := rows.Scan(&r.Fingerprint, &r.Algo, &r.Username, &r.AddedAt, &r.LastSeen,
128 &r.BuildRepo, &r.BuildNumber, &r.BuildJob, &r.StartedAt); err != nil {
129 return nil, err
130 }
131 out = append(out, r)
132 }
133 return out, rows.Err()
134}
135
136// TouchRunner records a poll by one key: the time, the scope the runner
137// asked for, and the build it just claimed (0 for none).
138func (s *Store) TouchRunner(keyID, userID int64, scope string, buildID int64) error {
139 _, err := s.DB.Exec(`INSERT INTO runner_seen (key_id, user_id, last_seen, scope, build_id)
140 VALUES (?1, ?2, strftime('%Y-%m-%dT%H:%M:%fZ','now'), ?3, NULLIF(?4, 0))
141 ON CONFLICT (key_id) DO UPDATE SET
142 last_seen = excluded.last_seen, scope = excluded.scope,
143 build_id = COALESCE(excluded.build_id, runner_seen.build_id)`,
144 keyID, userID, scope, buildID)
145 return err
146}
147
148// ForgetRunner drops a key's heartbeat row. The key itself stays.
149func (s *Store) ForgetRunner(fingerprint string) error {
150 res, err := s.DB.Exec(`DELETE FROM runner_seen
151 WHERE key_id = (SELECT id FROM ssh_keys WHERE fingerprint = ?)`, fingerprint)
152 if err != nil {
153 return err
154 }
155 if n, _ := res.RowsAffected(); n == 0 {
156 return ErrNotFound
157 }
158 return nil
159}
160
161// RunnerDone records that the key reported and holds nothing now.
162func (s *Store) RunnerDone(keyID int64) error {
163 _, err := s.DB.Exec(`UPDATE runner_seen SET last_seen = strftime('%Y-%m-%dT%H:%M:%fZ','now'),
164 build_id = NULL WHERE key_id = ?`, keyID)
165 return err
166}
167
168// ListRunners lists every key that has ever polled as a runner, most
169// recently seen first.
170func (s *Store) ListRunners() ([]Runner, error) {
171 rows, err := s.DB.Query(`SELECT u.username, k.fingerprint, k.id, r.last_seen, r.scope,
172 COALESCE(COALESCE(bu.username, bo.name) || '/' || br.name, ''),
173 COALESCE(b.number, 0), COALESCE(b.job, ''), COALESCE(b.started_at, '')
174 FROM runner_seen r JOIN users u ON u.id = r.user_id
175 JOIN ssh_keys k ON k.id = r.key_id
176 LEFT JOIN builds b ON b.id = r.build_id AND b.status = 'running'
177 LEFT JOIN repos br ON br.id = b.repo_id
178 LEFT JOIN users bu ON br.owner_kind = 'user' AND bu.id = br.owner_id
179 LEFT JOIN orgs bo ON br.owner_kind = 'org' AND bo.id = br.owner_id
180 ORDER BY r.last_seen DESC`)
181 if err != nil {
182 return nil, err
183 }
184 defer rows.Close()
185 var out []Runner
186 for rows.Next() {
187 var r Runner
188 if err := rows.Scan(&r.Username, &r.Fingerprint, &r.KeyID, &r.LastSeen, &r.Scope,
189 &r.BuildRepo, &r.BuildNumber, &r.BuildJob, &r.StartedAt); err != nil {
190 return nil, err
191 }
192 out = append(out, r)
193 }
194 return out, rows.Err()
195}