-
Notifications
You must be signed in to change notification settings - Fork 0
/
DB.js
160 lines (143 loc) · 4.36 KB
/
DB.js
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
const {Pool} = require('pg');
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: {
rejectUnauthorized: false
}
});
// name: 45 username: 15 text: 200 password: 61 follows: 100 avatar/banner: 20 options: 45
async function DB(offset, withFollow, follows) {
let withFollowS = ` where f.userId in (${follows})`;
if (!withFollow) {
withFollowS = '';
}
try {
//console.log(offset);
const conn = await pool.connect();
let rows = await conn.query(`select f.text, u.username, f.id, f.userId, u.name, u.avatar, f.image from feed f inner join users u on u.id = f.userId${withFollowS} order by f.id desc limit 10 offset ${(offset-1)*10}`);
conn.release();
//console.log(rows.rows);
return rows.rows;
} catch (err) {
throw err;
}
}
async function GetFollows(username) {
try {
let conn = await pool.connect();
let rows = await conn.query(`select follows from users where username='${username}'`);
//console.log(rows.rows[0]);
conn.release();
return rows.rows[0];
} catch (err) {
throw err;
}
}
async function NewFollow(username, follows) {
try {
let conn = await pool.connect();
let rows = await conn.query(`update users set follows='${follows}' where username='${username}'`);
//console.log(rows[0].email);
conn.release();
return;
} catch (err) {
throw err;
}
}
async function NewBanner(userId, banner) {
try {
let conn = await pool.connect();
let rows = await conn.query(`update users set banner='${banner}' where id='${userId}'`);
//console.log(rows[0].email);
conn.release();
return;
} catch (err) {
throw err;
}
}
async function NewAvatar(userId, avatar) {
try {
let conn = await pool.connect();
let rows = await conn.query(`update users set avatar='${avatar}' where id='${userId}'`);
//console.log(rows[0].email);
conn.release();
return;
} catch (err) {
throw err;
}
}
async function NewOptions(username, bool) {
try {
let conn = await pool.connect();
let rows = await conn.query(`update users set options='${bool}' where username='${username}'`);
//console.log(rows[0].email);
conn.release();
return;
} catch (err) {
throw err;
}
}
async function GetUsernames() {
try {
let conn = await pool.connect();
let rows = await conn.query(`SELECT username, id FROM users`);
//console.log(rows[0].email);
conn.release();
return rows.rows;
} catch (err) {
throw err;
}
}
async function GetPostN(userId) {
try {
const conn = await pool.connect();
const rows = await conn.query(`select count(userId) c from feed where userId=${userId}`);
conn.release();
return rows.rows[0];
} catch (err) {
throw err;
}
}
async function FindUser(username) {
try {
const conn = await pool.connect();
let rows = await conn.query(`SELECT * FROM users WHERE username = '${username}'`);
//console.log(rows.rows[0].follows);
conn.release();
return rows.rows[0];
} catch (err) {
throw err;
}
}
async function FindUserId(id) {
try {
let conn = await pool.connect();
let rows = await conn.query(`SELECT * FROM users WHERE id = '${id}'`);
//console.log(rows[0].id);
conn.release();
return rows.rows[0];
} catch (err) {
throw err;
}
}
async function NewPost(text, userId, image) {
try {
let conn = await pool.connect();
await conn.query(`INSERT INTO feed (text, userId, image) VALUES ('${text}','${userId}','${image}')`);
conn.release();
return;
} catch (err) {
throw err;
}
}
async function NewUser(username, name, password) {
try {
const conn = await pool.connect();
await conn.query(`INSERT INTO users (username, name, password) VALUES ('${username}','${name}','${password}')`);
conn.release();
return;
} catch (err) {
throw err;
}
}
module.exports = {DB, GetUsernames, FindUser, FindUserId, GetPostN, GetFollows, NewPost, NewUser, NewFollow, NewBanner, NewAvatar, NewOptions};