forked from osopromadze/Spring-Boot-Blog-REST-API
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinit.sql
More file actions
186 lines (172 loc) · 7.74 KB
/
init.sql
File metadata and controls
186 lines (172 loc) · 7.74 KB
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
DROP TABLE IF EXISTS post_tag;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS photos;
DROP TABLE IF EXISTS albums;
DROP TABLE IF EXISTS todos;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS company;
DROP TABLE IF EXISTS geo;
CREATE TABLE IF NOT EXISTS tags
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NOT NULL,
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS geo
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
lat varchar(255),
lng varchar(255),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS company
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255),
catch_phrase varchar(255),
bs varchar(255),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS address
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
street varchar(255),
suite varchar(255),
city varchar(255),
zipcode varchar(255),
geo_id bigint check (geo_id > 0) NOT NULL REFERENCES geo (id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS users
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
email varchar(255) NOT NULL,
phone varchar(255),
website varchar(255),
address_id bigint check (address_id > 0) DEFAULT NULL REFERENCES address (id),
company_id bigint check (company_id > 0) DEFAULT NULL REFERENCES company (id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint,
updated_by bigint,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS todos
(
todo_id bigint check (todo_id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
title varchar(255) NOT NULL,
completed boolean default false,
user_id bigint check (user_id > 0) DEFAULT NULL REFERENCES users (id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (todo_id)
);
CREATE TABLE IF NOT EXISTS albums
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
title varchar(255) NOT NULL,
user_id bigint check (user_id > 0) DEFAULT NULL REFERENCES users (id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE photos
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
title varchar(255) NOT NULL,
url varchar(255) NOT NULL,
thumbnail_url varchar(255) NOT NULL,
album_id bigint check (album_id > 0) DEFAULT NULL REFERENCES albums (id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS categories
(
category_id bigint check (category_id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NOT NULL,
user_id bigint check (user_id > 0) DEFAULT NULL REFERENCES users (id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (category_id)
);
CREATE TABLE posts
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
title varchar(255) NOT NULL,
body text NOT NULL,
user_id bigint check (user_id > 0) DEFAULT NULL REFERENCES users (id),
category_id bigint check (category_id > 0) DEFAULT NULL REFERENCES categories (category_id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE post_tag
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
post_id bigint check (post_id > 0) NOT NULL REFERENCES posts (id),
tag_id bigint check ( tag_id > 0 ) NOT NULL REFERENCES tags (id),
PRIMARY KEY (id)
);
CREATE TABLE comments
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NOT NULL,
email varchar(255) NOT NULL,
body text NOT NULL,
post_id bigint check ( post_id > 0 ) DEFAULT NULL REFERENCES posts (id),
user_id bigint check ( user_id > 0 ) DEFAULT NULL REFERENCES users (id),
created_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by bigint check (created_by > 0) NOT NULL,
updated_by bigint check (updated_by > 0) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE roles
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE user_role
(
id bigint check (id > 0) NOT NULL GENERATED ALWAYS AS IDENTITY,
user_id bigint check ( user_id > 0 ) NOT NULL REFERENCES users (id),
role_id bigint check ( role_id > 0 ) NOT NULL REFERENCES roles (id),
PRIMARY KEY (id)
);