-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase.sql
186 lines (168 loc) · 4.42 KB
/
database.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
-- Active: 1705636111347@@127.0.0.1@3306
----------------------------------------------------------------------------------------------
CREATE TABLE
users (
id TEXT PRIMARY KEY UNIQUE NOT NULL,
nickname TEXT NOT NULL,
email TEXT NOT NULL,
password TEXT NOT NULL,
role TEXT NOT NULL,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE TABLE
posts (
id TEXT PRIMARY KEY UNIQUE NOT NULL,
creator_id TEXT NOT NULL,
content TEXT NOT NULL,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
likes_count INTEGER NOT NULL,
dislikes_count INTEGER NOT NULL,
comments_count INTEGER NOT NULL,
FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE
comments (
id TEXT PRIMARY KEY UNIQUE NOT NULL,
creator_id TEXT NOT NULL,
post_id TEXT NOT NULL,
content TEXT NOT NULL,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
likes_count INTEGER NOT NULL,
dislikes_count INTEGER NOT NULL,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE
posts_likes_dislikes (
user_id TEXT,
post_id TEXT,
like INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE
comments_likes_dislikes (
user_id TEXT,
comment_id TEXT,
like INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (comment_id) REFERENCES comments(id) ON DELETE CASCADE ON UPDATE CASCADE
);
----------------------------------------------------------------------------------------------
INSERT INTO users
VALUES (
'u001',
'amanda',
'Amanda@123',
'ADMIN',
'07-12-2023 10:37:11',
'07-12-2023 10:37:11'
), (
'u002',
'luan',
'Luan@123',
'NORMAL',
'20-11-2023 16:55:26',
'20-11-2023 16:55:26'
), (
'u003',
'samuel',
'Samuel@123',
'NORMAL',
'20-11-2023 17:28:40',
'20-11-2023 17:28:40'
), (
'u004',
'lily',
'Lily@123',
'NORMAL',
'20-11-2023 18:20:02',
'20-11-2023 18:20:02'
);
INSERT INTO posts
VALUES (
'p001',
'u003',
'texto do post 1',
'20-11-2023 15:50:01',
'20-11-2023 15:50:01',
0,
0,
0
), (
'p002',
'u001',
'texto do post 2',
'20-11-2023 16:55:26',
'20-11-2023 16:55:26',
0,
0,
0
), (
'p003',
'u004',
'texto do post 3',
'20-11-2023 17:28:40',
'20-11-2023 17:28:40',
0,
0,
0
), (
'p004',
'u002',
'texto do post 4',
'20-11-2023 18:20:02',
'20-11-2023 18:20:02',
0,
0,
0
);
INSERT INTO comments
VALUES (
'c001',
'p001',
'u003',
'texto do comentário 1',
'20-11-2023 15:50:01',
'20-11-2023 15:50:01'
), (
'c002',
'p002',
'u001',
'texto do comentário 2',
'20-11-2023 16:55:26',
'20-11-2023 16:55:26'
), (
'c003',
'p003',
'u004',
'texto do comentário 3',
'20-11-2023 17:28:40',
'20-11-2023 17:28:40'
), (
'c004',
'p004',
'u002',
'texto do comentário 4',
'20-11-2023 18:20:02',
'20-11-2023 18:20:02'
);
INSERT INTO
posts_likes_dislikes
VALUES ('u001', 'p001', 1), ('u002', 'p002', 0), ('u001', 'p003', 0), ('u002', 'p004', 1);
INSERT INTO
comments_likes_dislikes
VALUES ('u001', 'c001', 1), ('u002', 'p002', 1), ('u001', 'c003', 1), ('u002', 'c004', 0);
----------------------------------------------------------------------------------------------
DROP TABLE users;
DROP TABLE posts;
DROP TABLE comments;
DROP TABLE posts_likes_dislikes;
DROP TABLE comments_likes_dislikes;