Sign in

pulls/pr_01m47d24b0e6n91zwymwxg0vpx

public

pr_01m47d24b0e6n91zwymwxg0vpx/services/search/migrations/0001_init.sql

199 lines7,573 bytesCodeBlame
1-- Search across all of g1t: repositories, code on default branches, issues
2-- and pull requests, people and workspaces. Each kind is a table of what
3-- is shown and an FTS5 index over it, kept in step by triggers. Prose is
4-- tokenized as words (unicode61, so `pars*` finds `parser`); code by
5-- trigram, so any run of three characters or more is found, as in an
6-- editor. Who may see a row is decided when a query runs, from `repos`:
7-- every row of code, issues and pull requests joins its repository.
8-- Every timestamp is RFC 3339 UTC.
9
10-- Every repository that is not a pull request's fork, public or private.
11CREATE TABLE repos (
12 rid INTEGER PRIMARY KEY,
13 repo_id TEXT NOT NULL UNIQUE,
14 -- Lowercase, as the address has them.
15 namespace TEXT NOT NULL,
16 name TEXT NOT NULL,
17 description TEXT,
18 -- Space-separated, lowercase.
19 topics TEXT NOT NULL DEFAULT '',
20 -- The opening of the README on its default branch.
21 readme TEXT,
22 private INTEGER NOT NULL DEFAULT 0,
23 default_branch TEXT NOT NULL DEFAULT 'main',
24 -- What most of its indexed code is written in.
25 language TEXT,
26 -- The commit its code was last indexed at.
27 head TEXT,
28 -- `pending`, `indexing`, `done`, or `partial` when it was too large to
29 -- index all of.
30 state TEXT NOT NULL DEFAULT 'pending',
31 files INTEGER NOT NULL DEFAULT 0,
32 bytes INTEGER NOT NULL DEFAULT 0,
33 created_at TEXT NOT NULL,
34 pushed_at TEXT,
35 updated_at TEXT NOT NULL
36);
37CREATE INDEX repos_by_path ON repos (namespace, name);
38CREATE INDEX repos_active ON repos (private, pushed_at);
39CREATE INDEX repos_new ON repos (private, created_at);
40CREATE INDEX repos_by_language ON repos (private, language);
41
42CREATE VIRTUAL TABLE repos_fts USING fts5(
43 namespace, name, description, topics, readme,
44 content = 'repos', content_rowid = 'rid',
45 tokenize = 'unicode61 remove_diacritics 2', prefix = '2 3'
46);
47CREATE TRIGGER repos_ai AFTER INSERT ON repos BEGIN
48 INSERT INTO repos_fts (rowid, namespace, name, description, topics, readme)
49 VALUES (new.rid, new.namespace, new.name, new.description, new.topics, new.readme);
50END;
51CREATE TRIGGER repos_ad AFTER DELETE ON repos BEGIN
52 INSERT INTO repos_fts (repos_fts, rowid, namespace, name, description, topics, readme)
53 VALUES ('delete', old.rid, old.namespace, old.name, old.description, old.topics, old.readme);
54END;
55CREATE TRIGGER repos_au AFTER UPDATE OF namespace, name, description, topics, readme ON repos BEGIN
56 INSERT INTO repos_fts (repos_fts, rowid, namespace, name, description, topics, readme)
57 VALUES ('delete', old.rid, old.namespace, old.name, old.description, old.topics, old.readme);
58 INSERT INTO repos_fts (rowid, namespace, name, description, topics, readme)
59 VALUES (new.rid, new.namespace, new.name, new.description, new.topics, new.readme);
60END;
61
62-- A repository's topics, one row each, for Explore.
63CREATE TABLE repo_topics (
64 topic TEXT NOT NULL,
65 repo_id TEXT NOT NULL,
66 PRIMARY KEY (topic, repo_id)
67);
68CREATE INDEX repo_topics_by_repo ON repo_topics (repo_id);
69
70-- Each file of a default branch: indexed, or recorded as skipped so it is
71-- not read again until its blob changes.
72CREATE TABLE files (
73 fid INTEGER PRIMARY KEY,
74 repo_id TEXT NOT NULL,
75 path TEXT NOT NULL,
76 blob TEXT NOT NULL,
77 language TEXT,
78 bytes INTEGER NOT NULL DEFAULT 0,
79 -- Why it is not indexed: vendored, lockfile, binary, minified, too large.
80 skipped TEXT,
81 UNIQUE (repo_id, path)
82);
83CREATE INDEX files_by_language ON files (language);
84
85-- A file in pieces of at most 120 lines, so a match reads only the piece
86-- it is in. `path` is repeated so a query matches names and contents
87-- together.
88CREATE TABLE chunks (
89 cid INTEGER PRIMARY KEY,
90 fid INTEGER NOT NULL,
91 start_line INTEGER NOT NULL,
92 path TEXT NOT NULL,
93 content TEXT NOT NULL
94);
95CREATE INDEX chunks_by_file ON chunks (fid, start_line);
96
97CREATE VIRTUAL TABLE chunks_fts USING fts5(
98 path, content,
99 content = 'chunks', content_rowid = 'cid',
100 tokenize = 'trigram'
101);
102-- A match in a file's path counts four times one in its contents. Read
103-- through the rank column, which a query may take the minimum of.
104INSERT INTO chunks_fts (chunks_fts, rank) VALUES ('rank', 'bm25(4.0, 1.0)');
105CREATE TRIGGER chunks_ai AFTER INSERT ON chunks BEGIN
106 INSERT INTO chunks_fts (rowid, path, content) VALUES (new.cid, new.path, new.content);
107END;
108CREATE TRIGGER chunks_ad AFTER DELETE ON chunks BEGIN
109 INSERT INTO chunks_fts (chunks_fts, rowid, path, content) VALUES ('delete', old.cid, old.path, old.content);
110END;
111
112-- Files a push or a backfill changed, waiting to be read: `blob` null to
113-- remove the file. Drained a capped number at a time.
114CREATE TABLE pending (
115 repo_id TEXT NOT NULL,
116 path TEXT NOT NULL,
117 blob TEXT,
118 queued_at TEXT NOT NULL,
119 PRIMARY KEY (repo_id, path)
120);
121
122-- Issues and pull requests.
123CREATE TABLE items (
124 iid INTEGER PRIMARY KEY,
125 repo_id TEXT NOT NULL,
126 -- issue or pull.
127 kind TEXT NOT NULL,
128 number INTEGER NOT NULL,
129 title TEXT NOT NULL,
130 body TEXT NOT NULL DEFAULT '',
131 -- open or closed.
132 state TEXT NOT NULL,
133 -- An issue: open, completed or not_planned. A pull request: draft,
134 -- open, merged or closed.
135 status TEXT NOT NULL,
136 -- Lowercase username.
137 author TEXT NOT NULL,
138 -- Lowercase, each between bars: |bug|good first issue|.
139 labels TEXT NOT NULL DEFAULT '|',
140 created_at TEXT NOT NULL,
141 updated_at TEXT NOT NULL,
142 UNIQUE (repo_id, kind, number)
143);
144CREATE INDEX items_recent ON items (kind, updated_at);
145CREATE INDEX items_by_author ON items (author);
146
147CREATE VIRTUAL TABLE items_fts USING fts5(
148 title, body,
149 content = 'items', content_rowid = 'iid',
150 tokenize = 'unicode61 remove_diacritics 2', prefix = '2 3'
151);
152CREATE TRIGGER items_ai AFTER INSERT ON items BEGIN
153 INSERT INTO items_fts (rowid, title, body) VALUES (new.iid, new.title, new.body);
154END;
155CREATE TRIGGER items_ad AFTER DELETE ON items BEGIN
156 INSERT INTO items_fts (items_fts, rowid, title, body) VALUES ('delete', old.iid, old.title, old.body);
157END;
158CREATE TRIGGER items_au AFTER UPDATE OF title, body ON items BEGIN
159 INSERT INTO items_fts (items_fts, rowid, title, body) VALUES ('delete', old.iid, old.title, old.body);
160 INSERT INTO items_fts (rowid, title, body) VALUES (new.iid, new.title, new.body);
161END;
162
163-- People and workspaces, as their public pages show them.
164CREATE TABLE people (
165 pid INTEGER PRIMARY KEY,
166 -- user or workspace.
167 kind TEXT NOT NULL,
168 -- A username, or a workspace's id (its slug can change).
169 ref TEXT NOT NULL,
170 slug TEXT NOT NULL,
171 name TEXT,
172 bio TEXT,
173 avatar TEXT,
174 created_at TEXT NOT NULL,
175 UNIQUE (kind, ref)
176);
177CREATE INDEX people_by_slug ON people (slug);
178
179CREATE VIRTUAL TABLE people_fts USING fts5(
180 slug, name, bio,
181 content = 'people', content_rowid = 'pid',
182 tokenize = 'unicode61 remove_diacritics 2', prefix = '2 3'
183);
184CREATE TRIGGER people_ai AFTER INSERT ON people BEGIN
185 INSERT INTO people_fts (rowid, slug, name, bio) VALUES (new.pid, new.slug, new.name, new.bio);
186END;
187CREATE TRIGGER people_ad AFTER DELETE ON people BEGIN
188 INSERT INTO people_fts (people_fts, rowid, slug, name, bio) VALUES ('delete', old.pid, old.slug, old.name, old.bio);
189END;
190CREATE TRIGGER people_au AFTER UPDATE OF slug, name, bio ON people BEGIN
191 INSERT INTO people_fts (people_fts, rowid, slug, name, bio) VALUES ('delete', old.pid, old.slug, old.name, old.bio);
192 INSERT INTO people_fts (rowid, slug, name, bio) VALUES (new.pid, new.slug, new.name, new.bio);
193END;
194
195-- The service's own state: when the backfill started and finished.
196CREATE TABLE meta (
197 key TEXT PRIMARY KEY,
198 value TEXT NOT NULL
199);