-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathqueries.txt
178 lines (140 loc) · 6.57 KB
/
queries.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
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
## network-programming-project
#Instructions
-Install nodejs-leagcy, mongodb-clients, mongodb server and Postman (google chrome app) for api testing.
-Clone the project.
```
npm install
npm start
```
-Test api at http://localhost:3000
-TODO : make logical names for routes.
create table user_schema(
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(100) NOT NULL,
firstname VARCHAR(100),
lastname VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,
bitsid VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
branch VARCHAR(100) NOT NULL,
conf_key INT,
PRIMARY KEY ( user_id ),
UNIQUE (username),
UNIQUE (bitsid)
);
create table project_schema(
project_id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
description VARCHAR(100) NOT NULL,
PRIMARY KEY ( project_id ),
UNIQUE ( title )
);
create table member_schema(
member_id INT NOT NULL AUTO_INCREMENT,
project_id INT NOT NULL,
user_id INT NOT NULL,
PRIMARY KEY ( member_id ),
FOREIGN KEY (project_id) REFERENCES project_schema(project_id),
FOREIGN KEY (user_id) REFERENCES user_schema(user_id)
);
create table request_schema(
request_id INT NOT NULL AUTO_INCREMENT,
project_id INT NOT NULL,
user_id INT NOT NULL,
sender_status INT,
PRIMARY KEY ( request_id ),
FOREIGN KEY (project_id) REFERENCES project_schema(project_id),
FOREIGN KEY (user_id) REFERENCES user_schema(user_id)
);
create table branch_schema(
branch_id INT NOT NULL AUTO_INCREMENT,
branch_name VARCHAR(100) NOT NULL,
PRIMARY KEY ( branch_id )
);
create table tag_schema(
tag_id INT NOT NULL AUTO_INCREMENT,
tag_name VARCHAR(100) NOT NULL,
PRIMARY KEY ( tag_id )
);
create table branch_project_schema(
branch_project_id INT NOT NULL AUTO_INCREMENT,
branch_id INT NOT NULL,
project_id INT NOT NULL,
PRIMARY KEY ( branch_project_id ),
FOREIGN KEY (branch_id) REFERENCES branch_schema(branch_id),
FOREIGN KEY (project_id) REFERENCES project_schema(project_id)
);
create table tag_project_schema(
tag_project_id INT NOT NULL AUTO_INCREMENT,
project_id INT NOT NULL,
tag_id INT NOT NULL,
PRIMARY KEY ( tag_project_id ),
FOREIGN KEY (tag_id) REFERENCES tag_schema(tag_id),
FOREIGN KEY (project_id) REFERENCES project_schema(project_id)
);
create table notice_schema(
notice_id INT NOT NULL AUTO_INCREMENT,
content VARCHAR(100) NOT NULL,
PRIMARY KEY ( notice_id )
);
create table project_notice_schema(
project_notice_id INT NOT NULL AUTO_INCREMENT,
project_id INT NOT NULL,
user_id INT NOT NULL,
notice_id INT NOT NULL,
PRIMARY KEY ( project_notice_id ),
FOREIGN KEY (project_id) REFERENCES project_schema(project_id),
FOREIGN KEY (user_id) REFERENCES user_schema(user_id)
);
SELECT a.title, a.description
FROM project_schema a, branch_project_schema b
ON a.project_id = b.project_id;
SELECT a.title, a.description
FROM project_schema a LEFT JOIN branch_project_schema b
ON a.project_id = b.project_id;
SELECT project_schema.project_id,title,description
FROM project_schema,branch_project_schema,branch_schema
WHERE branch_schema.branch_id = 1 AND branch_schema.branch_id = branch_project_schema.branch_id AND project_schema.project_id = branch_project_schema.project_id;
INSERT INTO branch_project_schema VALUES
(1, 1, 6);
INSERT INTO tag_project_schema VALUES
(1, 1, 1);
INSERT INTO member_schema VALUES
(30, 16, 19, 0);
INSERT INTO branch_schema VALUES
(1, 'eee');
INSERT INTO tag_schema VALUES
(1, 'wsn');
INSERT INTO request_schema VALUES
(1, 29, 1, 0);
SELECT * FROM user_schema UNION SELECT * FROM project_schema;
SELECT project_schema.project_id,title,description
FROM project_schema,branch_project_schema,branch_schema,tag_schema
WHERE branch_schema.branch_id = 1 AND tag_schema.tag_id = 2 AND branch_schema.branch_id = branch_project_schema.branch_id AND tag_schema.tag_id = tag_project_schema.tag_id AND project_schema.project_id = branch_project_schema.project_id;
eyJhbGciOiJIUzI1NiJ9.dW1hbmd0eQ.oYKKxaoyQwWIdHHPfdd5AWH9Kuuo_pIGe5JzUzxUKBY
eyJhbGciOiJIUzI1NiJ9.dW1hbmd0eQ.oYKKxaoyQwWIdHHPfdd5AWH9Kuuo_pIGe5JzUzxUKBY
SELECT project_schema.project_id,title,description
FROM project_schema,member_schema
WHERE member_schema.admin_status = 1 AND member_schema.user_id=40 AND project_schema.project_id = member_schema.project_id;
SELECT title, description
FROM project_schema, member_schema
WHERE member_schema.user_id != 40 AND project_schema.project_id = member_schema.project_id;
SELECT title, description, username
FROM project_schema, member_schema, user_schema
WHERE project_schema.project_id = member_schema.project_id AND user_schema.user_id = member_schema.user_id;
select * from ( select m.project_id , m.user_id , title,description from member_schema m inner join project_schema p on m.project_id=p.project_id) as t where t.project_id=16;
select * from ( select m.project_id , m.user_id , title,description from member_schema m inner join project_schema p on m.project_id=p.project_id) as t where t.user_id=40;
select * from (select n.content, p.project_id, p.project_notice_id from notice_schema n inner join project_notice_schema p on n.notice_id=p.project_notice_id) as r where r.project_id=8 order by r.project_notice_id desc;
SELECT title, description, username
FROM project_schema, member_schema, user_schema
WHERE member_schema.admin_status = 1 AND user_schema.user_id=member_schema.project_id;
TODO -
Implement proper protection authentication by extracting username from token passed by the user.
App should not crash when invalid request come at routes. Do this by chaining middlewares.
select title, u.username ,m.admin_status,description from member_schema m inner join user_schema u on m.user_id=u.user_id inner join project_schema p on m.project_id=p.project_id;
select * from (select n.content, p.project_id, p.project_notice_id from notice_schema n inner join project_notice_schema p on n.notice_id=p.project_notice_id) as r where r.project_id=12 order by r.project_notice_id desc
select u.username, p.title from member_schema n inner join user_schema u on u.user_id=m.user_id inner join project_schema p on m.project_id=p.project_id
// non member project query
select * from (select u.user_id, p.project_id, u.username, p.title from member_schema n inner join user_schema u on u.user_id=n.user_id inner join project_schema p on n.project_id=p.project_id ) as t where t.user_id!=40;
select p.project_id, p.title from request_schema r inner join project_schema p on r.project_id=p.project_id;
select u.username, u.user_id from request_schema r inner join user_schema u on r.user_id=u.user_id;