shithub Public

Watch 1 Fork 0 Star 0
MySQL · 4916 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- Include the owning user/org handle in repo search documents and
4 -- keep those documents fresh when owner display fields change.
5
6 -- +goose Up
7
8 -- +goose StatementBegin
9 CREATE OR REPLACE FUNCTION repos_search_tsv(
10 repo_name citext,
11 repo_description text,
12 repo_owner_user_id bigint,
13 repo_owner_org_id bigint
14 ) RETURNS tsvector
15 LANGUAGE plpgsql AS $$
16 DECLARE
17 owner_login text := '';
18 owner_display text := '';
19 BEGIN
20 IF repo_owner_user_id IS NOT NULL THEN
21 SELECT username::text, display_name
22 INTO owner_login, owner_display
23 FROM users
24 WHERE id = repo_owner_user_id;
25 ELSIF repo_owner_org_id IS NOT NULL THEN
26 SELECT slug::text, display_name
27 INTO owner_login, owner_display
28 FROM orgs
29 WHERE id = repo_owner_org_id;
30 END IF;
31
32 RETURN
33 setweight(to_tsvector('shithub_search', coalesce(repo_name::text, '')), 'A') ||
34 setweight(to_tsvector('shithub_search', coalesce(owner_login, '')), 'A') ||
35 setweight(to_tsvector('shithub_search', coalesce(repo_description, '')), 'B') ||
36 setweight(to_tsvector('shithub_search', coalesce(owner_display, '')), 'C');
37 END;
38 $$;
39 -- +goose StatementEnd
40
41 -- +goose StatementBegin
42 CREATE OR REPLACE FUNCTION tg_repos_search_upsert() RETURNS trigger
43 LANGUAGE plpgsql AS $$
44 BEGIN
45 INSERT INTO repos_search (repo_id, tsv) VALUES (
46 NEW.id,
47 repos_search_tsv(NEW.name, NEW.description, NEW.owner_user_id, NEW.owner_org_id)
48 )
49 ON CONFLICT (repo_id) DO UPDATE
50 SET tsv = EXCLUDED.tsv;
51 RETURN NEW;
52 END;
53 $$;
54 -- +goose StatementEnd
55
56 DROP TRIGGER IF EXISTS repos_search_upsert ON repos;
57 CREATE TRIGGER repos_search_upsert
58 AFTER INSERT OR UPDATE OF name, description, owner_user_id, owner_org_id ON repos
59 FOR EACH ROW EXECUTE FUNCTION tg_repos_search_upsert();
60
61 -- +goose StatementBegin
62 CREATE OR REPLACE FUNCTION tg_repos_search_user_owner_update() RETURNS trigger
63 LANGUAGE plpgsql AS $$
64 BEGIN
65 INSERT INTO repos_search (repo_id, tsv)
66 SELECT r.id, repos_search_tsv(r.name, r.description, r.owner_user_id, r.owner_org_id)
67 FROM repos r
68 WHERE r.owner_user_id = NEW.id
69 ON CONFLICT (repo_id) DO UPDATE
70 SET tsv = EXCLUDED.tsv;
71 RETURN NEW;
72 END;
73 $$;
74 -- +goose StatementEnd
75
76 CREATE TRIGGER repos_search_user_owner_update
77 AFTER UPDATE OF username, display_name ON users
78 FOR EACH ROW
79 WHEN (OLD.username IS DISTINCT FROM NEW.username OR OLD.display_name IS DISTINCT FROM NEW.display_name)
80 EXECUTE FUNCTION tg_repos_search_user_owner_update();
81
82 -- +goose StatementBegin
83 CREATE OR REPLACE FUNCTION tg_repos_search_org_owner_update() RETURNS trigger
84 LANGUAGE plpgsql AS $$
85 BEGIN
86 INSERT INTO repos_search (repo_id, tsv)
87 SELECT r.id, repos_search_tsv(r.name, r.description, r.owner_user_id, r.owner_org_id)
88 FROM repos r
89 WHERE r.owner_org_id = NEW.id
90 ON CONFLICT (repo_id) DO UPDATE
91 SET tsv = EXCLUDED.tsv;
92 RETURN NEW;
93 END;
94 $$;
95 -- +goose StatementEnd
96
97 CREATE TRIGGER repos_search_org_owner_update
98 AFTER UPDATE OF slug, display_name ON orgs
99 FOR EACH ROW
100 WHEN (OLD.slug IS DISTINCT FROM NEW.slug OR OLD.display_name IS DISTINCT FROM NEW.display_name)
101 EXECUTE FUNCTION tg_repos_search_org_owner_update();
102
103 INSERT INTO repos_search (repo_id, tsv)
104 SELECT r.id, repos_search_tsv(r.name, r.description, r.owner_user_id, r.owner_org_id)
105 FROM repos r
106 ON CONFLICT (repo_id) DO UPDATE
107 SET tsv = EXCLUDED.tsv;
108
109 -- +goose Down
110
111 DROP TRIGGER IF EXISTS repos_search_org_owner_update ON orgs;
112 DROP FUNCTION IF EXISTS tg_repos_search_org_owner_update();
113 DROP TRIGGER IF EXISTS repos_search_user_owner_update ON users;
114 DROP FUNCTION IF EXISTS tg_repos_search_user_owner_update();
115
116 DROP TRIGGER IF EXISTS repos_search_upsert ON repos;
117 DROP FUNCTION IF EXISTS tg_repos_search_upsert();
118 DROP FUNCTION IF EXISTS repos_search_tsv(citext, text, bigint, bigint);
119
120 -- +goose StatementBegin
121 CREATE OR REPLACE FUNCTION tg_repos_search_upsert() RETURNS trigger
122 LANGUAGE plpgsql AS $$
123 BEGIN
124 INSERT INTO repos_search (repo_id, tsv) VALUES (
125 NEW.id,
126 setweight(to_tsvector('shithub_search', coalesce(NEW.name::text, '')), 'A') ||
127 setweight(to_tsvector('shithub_search', coalesce(NEW.description, '')), 'B')
128 )
129 ON CONFLICT (repo_id) DO UPDATE
130 SET tsv = EXCLUDED.tsv;
131 RETURN NEW;
132 END;
133 $$;
134 -- +goose StatementEnd
135
136 CREATE TRIGGER repos_search_upsert
137 AFTER INSERT OR UPDATE OF name, description ON repos
138 FOR EACH ROW EXECUTE FUNCTION tg_repos_search_upsert();
139
140 INSERT INTO repos_search (repo_id, tsv)
141 SELECT id,
142 setweight(to_tsvector('shithub_search', coalesce(name::text, '')), 'A') ||
143 setweight(to_tsvector('shithub_search', coalesce(description, '')), 'B')
144 FROM repos
145 ON CONFLICT (repo_id) DO UPDATE
146 SET tsv = EXCLUDED.tsv;
147