internal/store/migrations/0070_reactions.up.sql

 1-- Reactions on issues, merge requests and their conversation comments
 2-- (#291). comment_id NULL is the reaction on the thread's own body. The
 3-- unique indexes are partial because NULLs never collide in a plain one.
 4CREATE TABLE issue_reactions (
 5    id         INTEGER PRIMARY KEY,
 6    issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
 7    comment_id INTEGER REFERENCES issue_comments(id) ON DELETE CASCADE,
 8    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 9    reaction   TEXT NOT NULL
10               CHECK (reaction IN ('+1','-1','laugh','hooray','confused','heart','rocket','eyes'))
11);
12CREATE UNIQUE INDEX issue_reactions_body ON issue_reactions(issue_id, user_id, reaction)
13    WHERE comment_id IS NULL;
14CREATE UNIQUE INDEX issue_reactions_comment ON issue_reactions(comment_id, user_id, reaction)
15    WHERE comment_id IS NOT NULL;
16CREATE INDEX issue_reactions_issue ON issue_reactions(issue_id);
17
18CREATE TABLE mr_reactions (
19    id         INTEGER PRIMARY KEY,
20    mr_id      INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
21    comment_id INTEGER REFERENCES mr_comments(id) ON DELETE CASCADE,
22    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
23    reaction   TEXT NOT NULL
24               CHECK (reaction IN ('+1','-1','laugh','hooray','confused','heart','rocket','eyes'))
25);
26CREATE UNIQUE INDEX mr_reactions_body ON mr_reactions(mr_id, user_id, reaction)
27    WHERE comment_id IS NULL;
28CREATE UNIQUE INDEX mr_reactions_comment ON mr_reactions(comment_id, user_id, reaction)
29    WHERE comment_id IS NOT NULL;
30CREATE INDEX mr_reactions_mr ON mr_reactions(mr_id);