internal/store/runners.go

  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}