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
|
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,
join_date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL,
birth_date TIMESTAMP WITH TIME ZONE NOT NULL,
profile_avatar BYTEA,
profile_banner BYTEA,
profile_bio TEXT DEFAULT ''::text 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,
date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() 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,
date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() 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 TABLE admin.like (
user_id INTEGER NOT NULL,
post_id INTEGER,
comment_id INTEGER,
date TIMESTAMP WITH TIME ZONE DEFAULT clock_timestamp() NOT NULL
);
ALTER TABLE admin.like OWNER TO xssbook;
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;
CREATE TABLE admin.follow (
follower_id INTEGER NOT NULL,
followee_id INTEGER NOT NULL,
date 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 (follower_id, followee_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;
CREATE TABLE admin.media (
name TEXT NOT NULL,
content BYTEA NOT NULL,
type TEXT NOT NULL
);
ALTER TABLE admin.media OWNER TO xssbook;
ALTER TABLE ONLY admin.media
ADD CONSTRAINT media_pkey PRIMARY KEY (name);
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;
|
