Sign in

Search issue titles and bodies, change requests and comments. Private repositories you cannot read are not included.
vole
Clone

Read only, no credentials:

https://rodent.software/vole.git

Write over SSH with a registered key:

git clone ssh://git@rodent.software:2222/vole.git

A free and open-source forge that lives in your Git repo.

Code

396 commit(s) on main

Compare History
Turn off the disk reserve for test-spawned runners Keenan Nemetz · 11m ago c284cb2c
history blame raw 19.0 KiB
1// Package index holds the derived read model. Everything in it can be thrown
2// away and rebuilt from the repos on disk, which is why it is safe for it to be
3// the layer that knows about SQL.
4package index
5
6import (
7 "crypto/sha256"
8 "database/sql"
9 "encoding/hex"
10 "errors"
11 "fmt"
12 "time"
13
14 _ "github.com/mattn/go-sqlite3"
15
16 "rodent.software/vole/forge/repo"
17)
18
19type Entry struct {
20 ID string
21 Name string
22 Description string
23 DefaultBranch string
24 Created time.Time
25 ACLSHA string
26 ACLPrincipals int
27 ACLDefault string
28 // The default branch's summary, maintained at push time. A repository with no
29 // commits has an empty HeadSHA.
30 HeadSHA string
31 HeadSubject string
32 HeadAuthor string
33 HeadWhen int64
34 Language string
35 LangClass string
36}
37
38type Store struct{ db *sql.DB }
39
40func Open(path string) (*Store, error) {
41 dsn := path + "?_busy_timeout=5000&_journal_mode=WAL"
42 db, err := sql.Open("sqlite3", dsn)
43 if err != nil {
44 return nil, err
45 }
46 db.SetMaxOpenConns(4)
47 s := &Store{db: db}
48 if _, err := db.Exec(`
49CREATE TABLE IF NOT EXISTS repos (
50 id TEXT PRIMARY KEY,
51 name TEXT NOT NULL UNIQUE,
52 description TEXT NOT NULL DEFAULT '',
53 default_branch TEXT NOT NULL DEFAULT 'main',
54 created INTEGER NOT NULL,
55 acl_sha TEXT NOT NULL DEFAULT '',
56 acl_principals INTEGER NOT NULL DEFAULT 0,
57 acl_default TEXT NOT NULL DEFAULT 'none',
58 head_sha TEXT NOT NULL DEFAULT '',
59 head_subject TEXT NOT NULL DEFAULT '',
60 head_author TEXT NOT NULL DEFAULT '',
61 head_when INTEGER NOT NULL DEFAULT 0,
62 language TEXT NOT NULL DEFAULT '',
63 lang_class TEXT NOT NULL DEFAULT ''
64);`); err != nil {
65 db.Close()
66 return nil, err
67 }
68 // Added after the first release of the index; the table is disposable, so a
69 // failed ALTER (already present) is not an error.
70 _, _ = db.Exec(`ALTER TABLE repos ADD COLUMN issues_sha TEXT NOT NULL DEFAULT ''`)
71 // events_count is the number of feed events a repository has, so an
72 // unfiltered pager gets its total without counting the table.
73 _, _ = db.Exec(`ALTER TABLE repos ADD COLUMN events_count INTEGER NOT NULL DEFAULT 0`)
74 // The head summary is the default branch's tip and the language it is mostly
75 // made of, refreshed when a push lands rather than on every page read.
76 for _, col := range []string{
77 "head_sha TEXT NOT NULL DEFAULT ''",
78 "head_subject TEXT NOT NULL DEFAULT ''",
79 "head_author TEXT NOT NULL DEFAULT ''",
80 "head_when INTEGER NOT NULL DEFAULT 0",
81 "language TEXT NOT NULL DEFAULT ''",
82 "lang_class TEXT NOT NULL DEFAULT ''",
83 } {
84 _, _ = db.Exec(`ALTER TABLE repos ADD COLUMN ` + col)
85 }
86 // The overview's git-derived summaries, recorded at push time. They are
87 // keyed by repo id and replaced together with the head they describe.
88 if _, err := db.Exec(`
89CREATE TABLE IF NOT EXISTS repo_languages (
90 repo_id TEXT NOT NULL,
91 name TEXT NOT NULL,
92 bytes INTEGER NOT NULL,
93 PRIMARY KEY (repo_id, name)
94);
95CREATE TABLE IF NOT EXISTS repo_commits (
96 repo_id TEXT NOT NULL,
97 pos INTEGER NOT NULL,
98 sha TEXT NOT NULL,
99 subject TEXT NOT NULL,
100 author TEXT NOT NULL,
101 when_at INTEGER NOT NULL,
102 PRIMARY KEY (repo_id, pos)
103);
104CREATE TABLE IF NOT EXISTS repo_tags (
105 repo_id TEXT NOT NULL,
106 name TEXT NOT NULL,
107 oid TEXT NOT NULL,
108 commit_sha TEXT NOT NULL,
109 annotated INTEGER NOT NULL,
110 tagger TEXT NOT NULL,
111 when_at INTEGER NOT NULL,
112 subject TEXT NOT NULL,
113 PRIMARY KEY (repo_id, name)
114);`); err != nil {
115 db.Close()
116 return nil, err
117 }
118 if _, err := db.Exec(`
119CREATE TABLE IF NOT EXISTS issues (
120 repo_id TEXT NOT NULL,
121 id TEXT NOT NULL,
122 title TEXT NOT NULL,
123 state TEXT NOT NULL,
124 author TEXT NOT NULL,
125 created INTEGER NOT NULL,
126 labels TEXT NOT NULL DEFAULT '',
127 comments INTEGER NOT NULL DEFAULT 0,
128 PRIMARY KEY (repo_id, id)
129);`); err != nil {
130 db.Close()
131 return nil, err
132 }
133 // Body text is indexed so search does not have to touch git per query.
134 _, _ = db.Exec(`ALTER TABLE issues ADD COLUMN body TEXT NOT NULL DEFAULT ''`)
135 // Assignees are denormalised onto the row the way labels are: the list page
136 // shows them without a join, and the filter table below is what queries use.
137 _, _ = db.Exec(`ALTER TABLE issues ADD COLUMN assignees TEXT NOT NULL DEFAULT ''`)
138 // updated is when the metadata branch last touched the issue: an edit, a
139 // comment, or a state change. The branch keeps no such field — the commit
140 // carries the time — so the index derives it from `git log` and stores it,
141 // which is what lets the list sort by activity without walking history per
142 // page. The index is derived: a stale value is repaired by a rebuild.
143 _, _ = db.Exec(`ALTER TABLE issues ADD COLUMN updated INTEGER NOT NULL DEFAULT 0`)
144 if _, err := db.Exec(`
145CREATE TABLE IF NOT EXISTS issue_labels (
146 repo_id TEXT NOT NULL,
147 issue_id TEXT NOT NULL,
148 label TEXT NOT NULL,
149 PRIMARY KEY (repo_id, issue_id, label)
150);
151CREATE INDEX IF NOT EXISTS issue_labels_lookup ON issue_labels(repo_id, label);
152CREATE TABLE IF NOT EXISTS issue_assignees (
153 repo_id TEXT NOT NULL,
154 issue_id TEXT NOT NULL,
155 assignee TEXT NOT NULL,
156 PRIMARY KEY (repo_id, issue_id, assignee)
157);
158CREATE INDEX IF NOT EXISTS issue_assignees_lookup ON issue_assignees(repo_id, assignee);`); err != nil {
159 db.Close()
160 return nil, err
161 }
162 // entity_numbers is the `#42` alias. It is derived from history
163 // (metadata.LoadNumbers), never written back to the metadata branch, and the
164 // primary key on the number is what makes a duplicate impossible rather than
165 // merely unlikely.
166 if _, err := db.Exec(`
167CREATE TABLE IF NOT EXISTS entity_numbers (
168 repo_id TEXT NOT NULL,
169 number INTEGER NOT NULL,
170 kind TEXT NOT NULL,
171 id TEXT NOT NULL,
172 PRIMARY KEY (repo_id, number)
173);
174CREATE INDEX IF NOT EXISTS entity_numbers_id ON entity_numbers(repo_id, id);
175
176CREATE TABLE IF NOT EXISTS requests (
177 repo_id TEXT NOT NULL,
178 id TEXT NOT NULL,
179 title TEXT NOT NULL,
180 state TEXT NOT NULL,
181 author TEXT NOT NULL,
182 created INTEGER NOT NULL,
183 base TEXT NOT NULL DEFAULT '',
184 head TEXT NOT NULL DEFAULT '',
185 head_ref TEXT NOT NULL DEFAULT '',
186 merged_base TEXT NOT NULL DEFAULT '',
187 merged_head TEXT NOT NULL DEFAULT '',
188 merged_by TEXT NOT NULL DEFAULT '',
189 merged_at INTEGER NOT NULL DEFAULT 0,
190 body TEXT NOT NULL DEFAULT '',
191 comments INTEGER NOT NULL DEFAULT 0,
192 PRIMARY KEY (repo_id, id)
193);
194-- A push asks whether any open request follows the branch it moved, so that a
195-- branch nobody is proposing from costs nothing.
196CREATE INDEX IF NOT EXISTS requests_head_ref ON requests(repo_id, head_ref);
197CREATE TABLE IF NOT EXISTS request_closes (
198 repo_id TEXT NOT NULL,
199 request_id TEXT NOT NULL,
200 issue_id TEXT NOT NULL,
201 -- position keeps the order the request's file lists them in, so a rebuilt
202 -- row answers with the same list the branch does.
203 position INTEGER NOT NULL DEFAULT 0,
204 PRIMARY KEY (repo_id, request_id, issue_id)
205);
206CREATE INDEX IF NOT EXISTS request_closes_issue ON request_closes(repo_id, issue_id);
207CREATE TABLE IF NOT EXISTS request_approvals (
208 repo_id TEXT NOT NULL,
209 request_id TEXT NOT NULL,
210 principal TEXT NOT NULL,
211 head TEXT NOT NULL,
212 base TEXT NOT NULL,
213 created INTEGER NOT NULL DEFAULT 0,
214 PRIMARY KEY (repo_id, request_id, principal)
215);
216
217-- comments holds the discussion on both kinds of entity. The entity row carries
218-- the count a list shows; these rows are what search reads and what a future
219-- thread renderer can page through.
220CREATE TABLE IF NOT EXISTS comments (
221 repo_id TEXT NOT NULL,
222 kind TEXT NOT NULL,
223 parent TEXT NOT NULL,
224 id TEXT NOT NULL,
225 author TEXT NOT NULL,
226 created INTEGER NOT NULL,
227 body TEXT NOT NULL DEFAULT '',
228 reply_to TEXT NOT NULL DEFAULT '',
229 deleted INTEGER NOT NULL DEFAULT 0,
230 path TEXT NOT NULL DEFAULT '',
231 side TEXT NOT NULL DEFAULT '',
232 line INTEGER NOT NULL DEFAULT 0,
233 blob TEXT NOT NULL DEFAULT '',
234 PRIMARY KEY (repo_id, kind, parent, id)
235);
236CREATE INDEX IF NOT EXISTS comments_parent ON comments(repo_id, kind, parent);
237
238-- artifacts are the release pointers: releases/<tag>/<name> on the metadata
239-- branch. The size is the pointer's, which is the artifact's real size, so a
240-- release page lists its files without reading the branch.
241CREATE TABLE IF NOT EXISTS artifacts (
242 repo_id TEXT NOT NULL,
243 tag TEXT NOT NULL,
244 name TEXT NOT NULL,
245 oid TEXT NOT NULL,
246 size INTEGER NOT NULL DEFAULT 0,
247 PRIMARY KEY (repo_id, tag, name)
248);
249
250-- state_changes is the closed/reopened/merged history the activity feed shows.
251-- It is derived from the metadata commit subjects, and keyed by the commit so a
252-- refresh that sees the same range twice cannot duplicate an event.
253CREATE TABLE IF NOT EXISTS state_changes (
254 repo_id TEXT NOT NULL,
255 sha TEXT NOT NULL,
256 kind TEXT NOT NULL,
257 id TEXT NOT NULL,
258 state TEXT NOT NULL,
259 actor TEXT NOT NULL,
260 at INTEGER NOT NULL,
261 -- ordinal is the order the commits entered the branch. Commit times are
262 -- seconds, so two changes in one second need this to keep their order.
263 ordinal INTEGER NOT NULL DEFAULT 0,
264 PRIMARY KEY (repo_id, sha)
265);
266CREATE INDEX IF NOT EXISTS state_changes_recent ON state_changes(repo_id, ordinal DESC);
267
268-- events is the activity feed as rows: one per issue, change and release, and
269-- one per comment, approval and state change. It is the read model the feed
270-- pages paginate; the row carries the title and assignees the feed shows, and
271-- ids rather than URLs so a repository rename cannot leave a dead link.
272CREATE TABLE IF NOT EXISTS events (
273 repo_id TEXT NOT NULL,
274 id TEXT NOT NULL,
275 kind TEXT NOT NULL, -- issue | change | release
276 type TEXT NOT NULL, -- open | comment | approval | state | release
277 at INTEGER NOT NULL,
278 actor TEXT NOT NULL,
279 title TEXT NOT NULL,
280 assignees TEXT NOT NULL DEFAULT '',
281 entity_id TEXT NOT NULL,
282 parent_id TEXT NOT NULL DEFAULT '',
283 comment_id TEXT NOT NULL DEFAULT '',
284 state TEXT NOT NULL DEFAULT '',
285 PRIMARY KEY (repo_id, id)
286);
287-- The feed's query: one repository, newest first. The id breaks a tie inside a
288-- second, so a page boundary cannot repeat or skip an event.
289CREATE INDEX IF NOT EXISTS events_repo ON events(repo_id, at DESC, id DESC);
290-- The instance feed orders across repositories; this index lets SQLite scan in
291-- time order and filter by repository rather than sorting every visible event.
292CREATE INDEX IF NOT EXISTS events_time ON events(at DESC, id DESC);
293CREATE INDEX IF NOT EXISTS events_entity ON events(repo_id, entity_id);
294-- A profile reads one account's events across the repositories the viewer can
295-- read. Without this the page would scan every repository's history and filter,
296-- which is the cost the (repository, time) index exists to avoid.
297CREATE INDEX IF NOT EXISTS events_actor ON events(actor, at DESC, id DESC);`); err != nil {
298 db.Close()
299 return nil, err
300 }
301 // updated is when the metadata branch last touched the request, derived from
302 // the commits that changed its directory. It is the same disposable shape as
303 // an issue's: a stale value is repaired by a rebuild.
304 _, _ = db.Exec(`ALTER TABLE requests ADD COLUMN updated INTEGER NOT NULL DEFAULT 0`)
305 // search is the universal search surface: one row for every issue, change
306 // request and comment, with the text FTS5 tokenizes. The first four columns are
307 // UNINDEXED because they identify the result rather than match it; title and
308 // body are what a query looks at. FTS5 is a build choice (the bundled SQLite
309 // needs -tags sqlite_fts5, see README.md), so its absence is reported as that
310 // rather than as a bare "no such module".
311 if _, err := db.Exec(`
312CREATE VIRTUAL TABLE IF NOT EXISTS search USING fts5(
313 repo_id UNINDEXED,
314 kind UNINDEXED,
315 entity_id UNINDEXED,
316 parent_id UNINDEXED,
317 title,
318 body,
319 author UNINDEXED,
320 tokenize = 'unicode61 remove_diacritics 2'
321);`); err != nil {
322 db.Close()
323 return nil, fmt.Errorf("index: cannot create the search table: SQLite has no FTS5 module (build with -tags sqlite_fts5): %w", err)
324 }
325 if err := s.migrate(); err != nil {
326 db.Close()
327 return nil, err
328 }
329 return s, nil
330}
331
332// schemaVersion is the shape the rows in this file were derived from. It is not
333// a migration chain: the index is disposable, so a bump drops the derived rows
334// and leaves every repo with an empty issues_sha, which makes the next read
335// rebuild that repo from git. A stale row is worse than a rebuild, and a rebuild is the
336// operation the index already has to survive.
337const schemaVersion = 14
338
339func (s *Store) migrate() error {
340 var v int
341 if err := s.db.QueryRow(`PRAGMA user_version`).Scan(&v); err != nil {
342 return err
343 }
344 if v == schemaVersion {
345 return nil
346 }
347 if _, err := s.db.Exec(`
348DELETE FROM issues;
349DELETE FROM issue_labels;
350DELETE FROM issue_assignees;
351DELETE FROM entity_numbers;
352DELETE FROM requests;
353DELETE FROM request_closes;
354DELETE FROM request_approvals;
355DELETE FROM comments;
356DELETE FROM artifacts;
357DELETE FROM state_changes;
358DELETE FROM events;
359DELETE FROM repo_languages;
360DELETE FROM repo_commits;
361DROP TABLE IF EXISTS repo_contributors;
362DELETE FROM repo_tags;
363DELETE FROM search;
364UPDATE repos SET issues_sha = '', events_count = 0,
365 head_sha = '', head_subject = '', head_author = '', head_when = 0,
366 language = '', lang_class = '';`); err != nil {
367 return err
368 }
369 _, err := s.db.Exec(fmt.Sprintf(`PRAGMA user_version = %d`, schemaVersion))
370 return err
371}
372
373// IssueRow is the indexed view of an issue: enough to list and link, not enough
374// to render. The detail page reads the issue from git so bodies are never stale.
375type IssueRow struct {
376 ID string
377 Title string
378 State string
379 Author string
380 Created time.Time
381 Labels []string
382 Assignees []string
383 Comments int
384 // Updated is when the metadata branch last touched the issue: the newest
385 // commit over its directory, which covers an edit, a comment, a label change,
386 // and a close or reopen. It is at least Created, so a freshly filed issue
387 // reads as just updated rather than as never touched.
388 Updated time.Time
389 // Number is the `#42` alias from entity_numbers, or 0 when the branch has not
390 // been indexed yet. It is a display alias; ID remains the identity.
391 Number int
392}
393
394func (s *Store) Close() error { return s.db.Close() }
395
396// Rebuild discards the index and reconstructs it from the repo store. This is
397// the operation that proves the index is derived and not authoritative.
398func (s *Store) Rebuild(st *repo.Store) (int, error) {
399 repos, err := st.List()
400 if err != nil {
401 return 0, err
402 }
403 tx, err := s.db.Begin()
404 if err != nil {
405 return 0, err
406 }
407 defer tx.Rollback()
408 if _, err := tx.Exec(`DELETE FROM repos`); err != nil {
409 return 0, err
410 }
411 // A rebuild is a replacement, not a merge: rows for a repository that no
412 // longer exists must not survive it.
413 for _, table := range []string{
414 "issues", "issue_labels", "issue_assignees", "requests", "request_closes",
415 "request_approvals", "comments", "artifacts", "state_changes", "events", "search", "entity_numbers",
416 "repo_languages", "repo_commits", "repo_tags",
417 } {
418 if _, err := tx.Exec(`DELETE FROM ` + table); err != nil {
419 return 0, err
420 }
421 }
422 for _, m := range repos {
423 e, err := entryFor(m)
424 if err != nil {
425 return 0, err
426 }
427 if err := insert(tx, e); err != nil {
428 return 0, err
429 }
430 }
431 if err := tx.Commit(); err != nil {
432 return 0, err
433 }
434 // Derived rows are indexed per repo after the repo rows exist, because every
435 // table is keyed by repo id.
436 for _, m := range repos {
437 if err := s.IndexRepo(m); err != nil {
438 return 0, err
439 }
440 }
441 return len(repos), nil
442}
443
444// Upsert refreshes one repo without a full rebuild.
445func (s *Store) Upsert(m *repo.Meta) error {
446 e, err := entryFor(m)
447 if err != nil {
448 return err
449 }
450 return insert(s.db, e)
451}
452
453func (s *Store) Remove(id string) error {
454 // A removed repository must leave no rows behind: every table is keyed by
455 // repo id and nothing else would ever delete them.
456 tx, err := s.db.Begin()
457 if err != nil {
458 return err
459 }
460 defer tx.Rollback()
461 for _, table := range []string{
462 "issues", "issue_labels", "issue_assignees", "requests", "request_closes",
463 "request_approvals", "comments", "artifacts", "state_changes", "events", "search",
464 "entity_numbers", "repo_languages", "repo_commits", "repo_tags",
465 } {
466 if _, err := tx.Exec(`DELETE FROM `+table+` WHERE repo_id=?`, id); err != nil {
467 return err
468 }
469 }
470 if _, err := tx.Exec(`DELETE FROM repos WHERE id=?`, id); err != nil {
471 return err
472 }
473 return tx.Commit()
474}
475
476func (s *Store) List() ([]Entry, error) {
477 rows, err := s.db.Query(`SELECT id, name, description, default_branch, created, acl_sha, acl_principals, acl_default,
478 head_sha, head_subject, head_author, head_when, language, lang_class
479 FROM repos ORDER BY name`)
480 if err != nil {
481 return nil, err
482 }
483 defer rows.Close()
484 var out []Entry
485 for rows.Next() {
486 var e Entry
487 var created int64
488 if err := rows.Scan(&e.ID, &e.Name, &e.Description, &e.DefaultBranch, &created, &e.ACLSHA, &e.ACLPrincipals, &e.ACLDefault,
489 &e.HeadSHA, &e.HeadSubject, &e.HeadAuthor, &e.HeadWhen, &e.Language, &e.LangClass); err != nil {
490 return nil, err
491 }
492 e.Created = time.Unix(created, 0).UTC()
493 out = append(out, e)
494 }
495 return out, rows.Err()
496}
497
498func (s *Store) Get(name string) (*Entry, error) {
499 var e Entry
500 var created int64
501 err := s.db.QueryRow(`SELECT id, name, description, default_branch, created, acl_sha, acl_principals, acl_default,
502 head_sha, head_subject, head_author, head_when, language, lang_class
503 FROM repos WHERE name=?`, name).
504 Scan(&e.ID, &e.Name, &e.Description, &e.DefaultBranch, &created, &e.ACLSHA, &e.ACLPrincipals, &e.ACLDefault,
505 &e.HeadSHA, &e.HeadSubject, &e.HeadAuthor, &e.HeadWhen, &e.Language, &e.LangClass)
506 if errors.Is(err, sql.ErrNoRows) {
507 return nil, osErrNotExist
508 }
509 if err != nil {
510 return nil, err
511 }
512 e.Created = time.Unix(created, 0).UTC()
513 return &e, nil
514}
515
516var osErrNotExist = fmt.Errorf("index: repo not found")
517
518func entryFor(m *repo.Meta) (Entry, error) {
519 e := Entry{
520 ID: m.ID,
521 Name: m.Name,
522 Description: m.Description,
523 DefaultBranch: m.DefaultBranch,
524 Created: m.Created,
525 }
526 a, err := m.ACL()
527 if err != nil {
528 return e, err
529 }
530 e.ACLDefault = a.Default
531 e.ACLPrincipals = len(a.Entries)
532 // A digest rather than a commit id: the rules live in the instance database now, so
533 // the thing worth recording is which version of them this row was built from.
534 if raw, err := a.Bytes(); err == nil {
535 sum := sha256.Sum256(raw)
536 e.ACLSHA = hex.EncodeToString(sum[:])
537 }
538 return e, nil
539}
540
541type execer interface {
542 Exec(query string, args ...any) (sql.Result, error)
543}
544
545func insert(x execer, e Entry) error {
546 _, err := x.Exec(`INSERT INTO repos(id, name, description, default_branch, created, acl_sha, acl_principals, acl_default)
547VALUES(?,?,?,?,?,?,?,?)
548ON CONFLICT(id) DO UPDATE SET
549 name=excluded.name, description=excluded.description, default_branch=excluded.default_branch,
550 acl_sha=excluded.acl_sha, acl_principals=excluded.acl_principals, acl_default=excluded.acl_default`,
551 e.ID, e.Name, e.Description, e.DefaultBranch, e.Created.Unix(), e.ACLSHA, e.ACLPrincipals, e.ACLDefault)
552 return err
553}
554