-
Notifications
You must be signed in to change notification settings - Fork 2
/
schema.sql
208 lines (184 loc) · 5.84 KB
/
schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
-- Next-auth recommended Schema for a Postgres database.
DROP TABLE IF EXISTS accounts CASCADE;
DROP TABLE IF EXISTS sessions CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS verification_requests CASCADE;
DROP TABLE IF EXISTS repositories CASCADE;
DROP TABLE IF EXISTS stars CASCADE;
DROP TABLE IF EXISTS "Label" CASCADE;
DROP TABLE IF EXISTS "Issue" CASCADE;
DROP TABLE IF EXISTS "Comment" CASCADE;
DROP TABLE IF EXISTS "_IssueToLabel" CASCADE;
DROP TYPE IF EXISTS "State" CASCADE;
CREATE TABLE accounts
(
id SERIAL,
compound_id VARCHAR(255) NOT NULL,
user_id INTEGER NOT NULL,
provider_type VARCHAR(255) NOT NULL,
provider_id VARCHAR(255) NOT NULL,
provider_account_id VARCHAR(255) NOT NULL,
refresh_token TEXT,
access_token TEXT,
access_token_expires TIMESTAMP,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);
CREATE TABLE sessions
(
id SERIAL,
user_id INTEGER NOT NULL,
expires TIMESTAMP NOT NULL,
session_token VARCHAR(255) NOT NULL,
access_token VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);
CREATE TABLE users
(
id SERIAL,
name VARCHAR(255),
email VARCHAR(255),
email_verified TIMESTAMP,
username VARCHAR(255),
image VARCHAR(255),
bio TEXT,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);
CREATE TABLE verification_requests
(
id SERIAL,
identifier VARCHAR(255) NOT NULL,
token VARCHAR(255) NOT NULL,
expires TIMESTAMP NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX compound_id
ON accounts(compound_id);
CREATE INDEX provider_account_id
ON accounts(provider_account_id);
CREATE INDEX provider_id
ON accounts(provider_id);
CREATE INDEX user_id
ON accounts(user_id);
CREATE UNIQUE INDEX session_token
ON sessions(session_token);
CREATE UNIQUE INDEX access_token
ON sessions(access_token);
CREATE UNIQUE INDEX email
ON users(email);
CREATE UNIQUE INDEX token
ON verification_requests(token);
-- Extra index
CREATE UNIQUE INDEX username
ON users(username);
-- Project schema
CREATE TABLE repositories
(
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL,
description VARCHAR(255),
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
max_issue_number INTEGER,
owner_id INTEGER,
UNIQUE (name, owner_id),
FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE stars
(
id SERIAL PRIMARY KEY NOT NULL,
UNIQUE (repository_id, user_id),
repository_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE "Label"
(
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255) NOT NULL,
color VARCHAR(255) NOT NULL,
description VARCHAR(255)
);
CREATE TYPE "State" AS ENUM('open','closed');
CREATE TABLE "Issue"
(
id SERIAL PRIMARY KEY NOT NULL,
number INTEGER,
title VARCHAR(255) NOT NULL,
body TEXT,
state "State" NOT NULL DEFAULT 'open',
assignee VARCHAR(255),
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
closed_at TIMESTAMP,
user_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
repo_id INTEGER,
UNIQUE (number, repo_id),
FOREIGN KEY (repo_id) REFERENCES repositories(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE "_IssueToLabel" (
"A" integer NOT NULL REFERENCES "Issue"(id) ON DELETE CASCADE,
"B" integer NOT NULL REFERENCES "Label"(id)
);
CREATE UNIQUE INDEX "_IssueToLabel_AB_unique" ON "_IssueToLabel"("A" int4_ops,"B" int4_ops);
CREATE INDEX "_IssueToLabel_B_index" ON "_IssueToLabel"("B" int4_ops);
CREATE TABLE "Comment"
(
id SERIAL PRIMARY KEY NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
user_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
issue_id INTEGER,
FOREIGN KEY (issue_id) REFERENCES "Issue"(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE OR REPLACE FUNCTION issue_number_function()
RETURNS TRIGGER AS
$BODY$
DECLARE
max INTEGER:=NULL;
BEGIN
SELECT max_issue_number INTO max FROM repositories WHERE id=NEW.repo_id;
IF max IS NULL THEN
max:=1;
ELSE
max=max+1;
END IF;
UPDATE repositories SET max_issue_number = max WHERE id=NEW.repo_id;
NEW.number:=max;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER issue_number_trigger
BEFORE INSERT
ON "Issue"
FOR EACH ROW
EXECUTE PROCEDURE issue_number_function();
-- Trigger to set 'closed_at' field to current date when the state is changed to 'closed'
CREATE OR REPLACE FUNCTION close_state_function()
RETURNS TRIGGER AS
$BODY$
BEGIN
IF NEW.state = 'closed'
THEN NEW.closed_at = NOW();
ELSE
NEW.closed_at = NULL;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER close_state_trigger
BEFORE UPDATE
ON "Issue"
FOR EACH ROW
WHEN (NEW.state != OLD.state)
EXECUTE PROCEDURE close_state_function();