forked from garywei944/CG3-Blog-Platform
-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema_design.sql
93 lines (74 loc) · 2.6 KB
/
schema_design.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
-- create tables
CREATE TABLE user_account (
username VARCHAR(20),
pwd VARCHAR(20),
profile_pic TEXT,
bio VARCHAR(250),
PRIMARY KEY(username)
);
CREATE TABLE post (
post_id serial primary key,
username VARCHAR(20),
title VARCHAR(50),
content TEXT,
post_time TIMESTAMP,
FOREIGN KEY(username) REFERENCES user_account(username)
);
CREATE TABLE liked (
username VARCHAR(20),
post_id serial,
FOREIGN KEY(username) REFERENCES user_account(username),
FOREIGN KEY(post_id) REFERENCES post(post_id)
);
CREATE TABLE follow (
username VARCHAR(20),
follower_name VARCHAR(20),
FOREIGN KEY(username) REFERENCES user_account(username),
FOREIGN KEY(follower_name) REFERENCES user_account(username)
);
-- populate tables with data
INSERT INTO user_account values
('Alex', 123, 'fake_url1', 'hello I am Alex'),
('Chris', 456, 'fake_url2', 'hello I am Chris'),
('Devin', 789, 'fake_url3', 'hello I am Devin');
INSERT INTO post (username, title, content) values
('Alex', 'post alex', 'story alex'),
('Alex', 'post alex 2', 'story alex 2'),
('Chris', 'post Chris', 'story Chris');
--INSERT INTO liked values
-- ('Alex', 'post Chris'),
-- ('Chris', 'post alex');
INSERT INTO liked values
('Alex', 3),
('Chris', 1);
INSERT INTO follow values ('Alex', 'Chris'),
('Chris', 'Alex'),
('Alex', 'Devin');
-- debug: modify column type
-- ALTER TABLE follow ALTER COLUMN follower_name TYPE VARCHAR(20);
-- add post_id
--ALTER TABLE post ADD post_id VARCHAR(20);
--INSERT INTO post (post_id) values ('Alex', 'post alex', 'story alex');
--UPDATE post SET post_id = '01' WHERE title = 'post alex';
--UPDATE post SET post_id = '02' WHERE title = 'post alex 2';
--UPDATE post SET post_id = '03' WHERE title = 'post Chris';
-- dropping primary key attempt: didn't actually do it
-- ALTER TABLE post DROP CONSTRAINT post_pkey;
---- adding three posts
--INSERT INTO post (username, title, content, post_id) values
-- ('Chris', 'post Chris 2', 'story Chris 2', '04'),
-- ('Devin', 'post Devin', 'story Devin', '05'),
-- ('Devin', 'post Devin 2', 'story Devin 2', '06');
-- adding three posts
INSERT INTO post (username, title, content) values
('Chris', 'post Chris 2', 'story Chris 2'),
('Devin', 'post Devin', 'story Devin'),
('Devin', 'post Devin 2', 'story Devin 2');
-- reconstruct the follow table
drop table follow;
create table follow (
this_username varchar(20),
following_username varchar(20),
foreign key(this_username) references user_account(username),
foreign key(following_username) references user_account(username)
);