-
Notifications
You must be signed in to change notification settings - Fork 0
/
Database_microblogging.txt
116 lines (89 loc) · 3.76 KB
/
Database_microblogging.txt
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
Data Definition Language:
The below is the code for creating the main database tables in the POSTGRESQL database.
Execute the commands corresponding to the sections to create tables for the microblogging service.
--------------------------
The below is the definition and schema for the database
------------
USERS
------------
CREATE TABLE users(
user_id SERIAL PRIMARY KEY UNIQUE,
user_name VARCHAR(20) UNIQUE NOT NULL,
password VARCHAR(20) NOT NULL);
--------------
SESSION TABLE
--------------
CREATE TABLE session(
user_id INT REFERENCES users(user_id) UNIQUE,
ssid SERIAL PRIMARY KEY UNIQUE);
-----------------
TWEETS
-----------------
CREATE TABLE tweets(
tweet_id SERIAL PRIMARY KEY UNIQUE,
tweet_text VARCHAR(180) NOT NULL,
time_created timestamp WITHOUT TIME ZONE DEFAULT current_timestamp,
user_id INT REFERENCES users(user_id));
ALTER TABLE tweets ADD CONSTRAINT tweet_text CHECK ((char_length(tweet_text) > 20) AND (char_length(tweet_text)<180));
---------------------------
FOLLOWERS/FOLLOWING mapping
----------------------------
CREATE TABLE follow_tb(
follower INT REFERENCES users(user_id) NOT NULL,
following int REFERENCES users(user_id) NOT NULL,
UNIQUE(follower,following)
CONSTRAINT you_cant_follow_you CHECK(follower!=following));
//Adding not null constraint
ALTER TABLE follow_tb ALTER COLUMN follower SET NOT NULL;
--------------------------------------------------
The Following are Functionalities for the Microbloggin implementation
-----------------------
VIEWS for functionalies
-------------------------
1. Functionality: Show my Tweets
--------------------------------
SELECT tweet_text,tweet_id
FROM tweets
WHERE user_id IN (SELECT user_id FROM session WHERE ssid=(handler_from_c))
ORDER BY time_created DESC;
2.Functionality: Delete Tweet
-----------------------------
DELETE FROM tweets WHERE tweet_id=tweet_id_from_c
AND user_id=(SELECT user_id FROM session WHERE ssid=5);
2.5 Funcitonality ADD Tweet
---------------------------------
INSERT INTO tweets(tweet_text,user_id) VALUES ('tweet_text_from_c',(SELECT user_id FROM session WHERE ssid=user_Handler_from_c));
3. Functionality: AllTweets
---------------------------------
SELECT tweet_text,tweet_id FROM tweets
LEFT JOIN follow_tb ON tweets.user_id = follow_tb.following
WHERE follow_tb.follower=
(SELECT user_id FROM session WHERE ssid=(handler_from_c))UNION
SELECT tweet_text,tweet_id FROM tweets WHERE user_id=(SELECT user_id FROM session WHERE ssid=(handler_from_c));
4. Functionality: Follow Username
---------------------------------
INSERT INTO follow_tb VALUES(
(SELECT user_id FROM session WHERE ssid=(handler_get_from_c)),
(SELECT user_id FROM users WHERE user_name=('username_get_from_c') ));
5. Fuctionality : Unfollow Username;
-------------------------------------
DELETE FROM follow_tb WHERE
follower= (SELECT user_id FROM session WHERE ssid=(handler_get_from_c)) AND
following=(SELECT user_id FROM users WHERE user_name=('username_get_from_c')) ;
6. Functionality : Following List:
----------------------------------------
SELECT user_name FROM users
LEFT JOIN follow_tb ON users.user_id=follow_tb.following
WHERE follow_tb.follower=
(SELECT user_id FROM session WHERE ssid=(handler_from_C));
5. Functionality: Follower List
---------------------------------
SELECT user_name FROM users
LEFT JOIN follow_tb ON users.user_id=follow_tb.follower
WHERE follow_tb.following=
(SELECT user_id FROM session WHERE ssid=(handler_from_c));
6. Login
--------------------------
INSERT INTO session(user_id) SELECT (SELECT user_id FROM users WHERE user_name = 'user_name_from_c') WHERE
NOT EXISTS ( SELECT 1 FROM session WHERE user_id = (SELECT user_id FROM users WHERE user_name = 'user_name_from_c'));
SELECT ssid FROM session WHERE user_id = (SELECT user_id FROM users WHERE user_name = 'user_name_from_c');