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
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
|
BEGIN TRANSACTION;
SET search_path = public;
-- Migration Start
CREATE SCHEMA sys;
ALTER SCHEMA sys OWNER TO xssbook;
CREATE DOMAIN sys."*/*" AS BYTEA;
CREATE TABLE sys.database_info (
name TEXT DEFAULT ''::text NOT NULL,
jwt_secret TEXT DEFAULT ''::text NOT NULL,
curr_revision INTEGER DEFAULT 0 NOT NULL
);
ALTER TABLE sys.database_info
ADD CONSTRAINT database_info_pkey PRIMARY KEY (name);
ALTER TABLE sys.database_info OWNER TO xssbook;
INSERT INTO sys.database_info
(name, curr_revision) VALUES (current_database(), 0);
CREATE TYPE sys.JWT AS (
token TEXT
);
CREATE SCHEMA admin;
ALTER SCHEMA admin OWNER TO xssbook;
CREATE SEQUENCE IF NOT EXISTS sys.user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE sys.user_id_seq OWNER TO xssbook;
CREATE TABLE admin.user (
id INTEGER DEFAULT nextval('sys.user_id_seq'::regclass) NOT NULL,
username TEXT NOT NULL,
password TEXT NOT NULL,
role NAME DEFAULT 'rest_user'::text NOT NULL,
first_name TEXT DEFAULT ''::text NOT NULL,
last_name TEXT DEFAULT ''::text NOT NULL,
middle_name TEXT DEFAULT ''::text NOT NULL,
email TEXT DEFAULT ''::text NOT NULL,
gender TEXT DEFAULT ''::text NOT NULL,
birth_date TIMESTAMP WITH TIME ZONE NOT NULL,
profile_bio TEXT DEFAULT ''::text NOT NULL,
created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
seen TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
deleted BOOLEAN DEFAULT FALSE NOT NULL
);
ALTER TABLE admin.user OWNER TO xssbook;
ALTER TABLE ONLY admin.user
ADD CONSTRAINT user_pkey PRIMARY KEY (id);
ALTER TABLE ONLY admin.user
ADD CONSTRAINT user_username_unique UNIQUE (username);
CREATE SEQUENCE IF NOT EXISTS sys.post_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE sys.post_id_seq OWNER TO xssbook;
CREATE TABLE admin.post (
id INTEGER DEFAULT nextval('sys.post_id_seq'::regclass) NOT NULL,
user_id INTEGER NOT NULL,
content TEXT DEFAULT ''::text NOT NULL,
created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
deleted BOOLEAN DEFAULT FALSE NOT NULL
);
ALTER TABLE admin.post OWNER TO xssbook;
ALTER TABLE ONLY admin.post
ADD CONSTRAINT post_pkey PRIMARY KEY (id);
ALTER TABLE ONLY admin.post
ADD CONSTRAINT post_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
CREATE SEQUENCE IF NOT EXISTS sys.comment_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE admin.comment (
id INTEGER DEFAULT nextval('sys.comment_id_seq'::regclass) NOT NULL,
user_id INTEGER NOT NULL,
post_id INTEGER NOT NULL,
content TEXT DEFAULT ''::text NOT NULL,
created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
deleted BOOLEAN DEFAULT FALSE NOT NULL
);
ALTER TABLE admin.comment OWNER TO xssbook;
ALTER TABLE ONLY admin.comment
ADD CONSTRAINT comment_pkey PRIMARY KEY (id);
ALTER TABLE ONLY admin.comment
ADD CONSTRAINT comment_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
ALTER TABLE ONLY admin.comment
ADD CONSTRAINT comment_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE;
CREATE SEQUENCE IF NOT EXISTS sys.like_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE admin.like (
id INTEGER DEFAULT nextval('sys.like_id_seq'::regclass) NOT NULL,
user_id INTEGER NOT NULL,
post_id INTEGER,
comment_id INTEGER,
value BOOLEAN NOT NULL DEFAULT TRUE,
created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);
ALTER TABLE admin.like OWNER TO xssbook;
ALTER TABLE ONLY admin.like
ADD CONSTRAINT like_pkey PRIMARY KEY (id);
ALTER TABLE ONLY admin.like
ADD CONSTRAINT like_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
ALTER TABLE ONLY admin.like
ADD CONSTRAINT like_post_id_fkey FOREIGN KEY (post_id) REFERENCES admin.post (id) ON DELETE CASCADE;
ALTER TABLE ONLY admin.like
ADD CONSTRAINT like_comment_id_fkey FOREIGN KEY (comment_id) REFERENCES admin.comment (id) ON DELETE CASCADE;
ALTER TABLE ONLY admin.like
ADD CONSTRAINT like_post_id_unique UNIQUE (user_id, post_id);
ALTER TABLE ONLY admin.like
ADD CONSTRAINT like_comment_id_unique UNIQUE (user_id, comment_id);
CREATE SEQUENCE IF NOT EXISTS sys.follow_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE admin.follow (
id INTEGER DEFAULT nextval('sys.follow_id_seq'::regclass) NOT NULL,
follower_id INTEGER NOT NULL,
followee_id INTEGER NOT NULL,
value BOOLEAN NOT NULL DEFAULT TRUE,
created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);
ALTER TABLE admin.follow OWNER TO xssbook;
ALTER TABLE ONLY admin.follow
ADD CONSTRAINT follow_pkey PRIMARY KEY (id);
ALTER TABLE ONLY admin.follow
ADD CONSTRAINT follow_follower_id FOREIGN KEY (follower_id) REFERENCES admin.user (id) ON DELETE CASCADE;
ALTER TABLE ONLY admin.follow
ADD CONSTRAINT follow_followee_id FOREIGN KEY (followee_id) REFERENCES admin.user (id) ON DELETE CASCADE;
ALTER TABLE ONLY admin.follow
ADD CONSTRAINT follow_follower_unique UNIQUE (follower_id, followee_id);
CREATE SEQUENCE IF NOT EXISTS sys.media_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE admin.media (
id INTEGER DEFAULT nextval('sys.media_id_seq'::regclass) NOT NULL,
name TEXT NOT NULL,
content BYTEA NOT NULL,
mime TEXT NOT NULL,
created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);
ALTER TABLE admin.media OWNER TO xssbook;
ALTER TABLE ONLY admin.media
ADD CONSTRAINT media_pkey PRIMARY KEY (id);
ALTER TABLE ONLY admin.media
ADD CONSTRAINT media_name_unique UNIQUE (name);
CREATE SEQUENCE IF NOT EXISTS sys.user_media_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TYPE admin.user_media_type AS ENUM (
'avatar', 'banner'
);
CREATE TABLE admin.user_media (
id INTEGER DEFAULT nextval('sys.user_media_id_seq'::regclass) NOT NULL,
user_id INTEGER NOT NULL,
content BYTEA NOT NULL,
mime TEXT NOT NULL,
type admin.user_media_type NOT NULL,
created TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
modified TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);
ALTER TABLE admin.user_media OWNER TO xssbook;
ALTER TABLE ONLY admin.user_media
ADD CONSTRAINT user_media_pkey PRIMARY KEY (id);
ALTER TABLE ONLY admin.user_media
ADD CONSTRAINT user_media_user_id_fkey FOREIGN KEY (user_id) REFERENCES admin.user (id) ON DELETE CASCADE;
ALTER TABLE ONLY admin.user_media
ADD CONSTRAINT user_media_type_unique UNIQUE (user_id, type);
ALTER DATABASE xssbook SET search_path = admin,public;
ALTER DATABASE xssbook SET bytea_output = 'hex';
-- Migration End;
-- Set Current Revision
UPDATE sys.database_info SET curr_revision = 1 WHERE name = current_database();
COMMIT TRANSACTION;
|
