-
Notifications
You must be signed in to change notification settings - Fork 0
/
Database.js
277 lines (257 loc) · 8.86 KB
/
Database.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
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
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
const pg = require('pg');
const md5 = require("md5");
class Database {
constructor(connectionStr) {
this.pool = new pg.Pool({connectionString: connectionStr});
}
async prepareUser(id) {
let client = await this.pool.connect();
let user = null;
try {
await client.query('BEGIN');
let res = await client.query('SELECT * FROM users WHERE id = $1', [id]);
if (res.rowCount === 0) {
await client.query('INSERT INTO users (id) VALUES ($1)', [id]);
res = await client.query('SELECT * FROM users WHERE id = $1', [id]);
}
await client.query('COMMIT');
user = res.rows[0];
} finally {
client.release();
}
return user;
}
async setUserKorpus(id, korpus) {
let client = await this.pool.connect();
try {
await client.query('BEGIN');
await client.query('UPDATE users SET korpus = $1 WHERE id = $2', [korpus, id]);
await client.query('COMMIT');
} finally {
client.release();
}
}
async getCabinets(korpus) {
let client = await this.pool.connect();
let cabinets = null;
try {
await client.query('BEGIN');
let res = await client.query('SELECT * FROM cabinets WHERE korpus = $1', [korpus]);
await client.query('COMMIT');
cabinets = res.rows;
} finally {
client.release();
}
return cabinets;
}
/**
* Get cabinet info
* @param cabinet Cabinet id
* @returns {Promise<null|{id:number,number:number,korpus:number,name:string,path:string}>}
*/
async getCabinet(cabinet) {
let client = await this.pool.connect();
let v = null;
try {
await client.query('BEGIN');
let res = await client.query('SELECT * FROM cabinets WHERE id = $1', [cabinet]);
await client.query('COMMIT');
v = res.rows[0];
} finally {
client.release();
}
return v;
}
/**
* Get all available times.
* @returns {Promise<null|{id:number,desk:string,start_time:string,end_time:string}[]>}
*/
async getFreeTimes() {
let client = await this.pool.connect();
let v = null;
try {
await client.query('BEGIN');
let res = await client.query('select * from aero_times where id not in (select time_id from aero_records)', []);
await client.query('COMMIT');
v = res.rows;
} finally {
client.release();
}
return v;
}
async isTimeStillFree(time) {
let client = await this.pool.connect();
let v = false;
try {
await client.query('BEGIN');
let res = await client.query('select * from aero_times where id = $1 and id not in (select time_id from aero_records)', [time]);
await client.query('COMMIT');
v = res.rowCount > 0;
} finally {
client.release();
}
return v;
}
/**
* Get time by id.
* @param id time id
* @returns {Promise<null|{id:number,desk:string,start_time:string,end_time:string}>}
*/
async getTime(id) {
let client = await this.pool.connect();
let v = null;
try {
await client.query('BEGIN');
let res = await client.query('SELECT * FROM aero_times WHERE id = $1', [id]);
await client.query('COMMIT');
v = res.rows[0];
} finally {
client.release();
}
return v;
}
async setState(id, state) {
let client = await this.pool.connect();
try {
await client.query('BEGIN');
await client.query('UPDATE aero_records SET state = $1 WHERE record_id = $2', [state, id]);
await client.query('COMMIT');
} finally {
client.release();
}
}
async recordOnAero(time, user, fio, group) {
let client = await this.pool.connect();
let id = null;
try {
await client.query('BEGIN');
await client.query('INSERT INTO aero_records (time_id, by_id, by_name, by_group) VALUES ($1, $2, $3, $4)', [time, user, fio, group]);
let res = await client.query('SELECT * FROM aero_records WHERE time_id = $1 AND by_id = $2 AND by_name = $3 AND by_group = $4', [time, user, fio, group]);
await client.query('COMMIT');
id = res.rows[0].record_id;
} finally {
client.release();
}
return id;
}
/**
* Gets all records created by user
* @param id User id
* @returns {Promise<null|{record_id:number,time_id:number,by_id:string,by_name:string,by_group:string,state:number}[]>}
*/
async getRecordsByUser(id) {
let client = await this.pool.connect();
let records = null;
try {
await client.query('BEGIN');
let res = await client.query('SELECT * FROM aero_records WHERE by_id = $1 AND state != 2', [id]);
await client.query('COMMIT');
records = res.rows;
} finally {
client.release();
}
return records;
}
async deleteRecord(id) {
let client = await this.pool.connect();
try {
await client.query('BEGIN');
await client.query('DELETE FROM aero_records WHERE record_id = $1', [id]);
await client.query('COMMIT');
} finally {
client.release();
}
}
async clearAero() {
let client = await this.pool.connect();
try {
await client.query('BEGIN');
await client.query('DELETE FROM aero_records');
await client.query('COMMIT');
} finally {
client.release();
}
}
/**
* Get record by id
* @param id Record id
* @returns {Promise<null|{record_id:number,time_id:number,by_id:string,by_name:string,by_group:string,state:number}>}
*/
async getRecord(id) {
let client = await this.pool.connect();
let v = null;
try {
await client.query('BEGIN');
let res = await client.query('SELECT * FROM aero_records WHERE record_id = $1', [id]);
await client.query('COMMIT');
v = res.rows[0];
} finally {
client.release();
}
return v;
}
async setUserAuth(id, username, password) {
let client = await this.pool.connect();
try {
await client.query('BEGIN');
await client.query('UPDATE users SET username = $1, user_password = $2 WHERE id = $3', [username, password, id]);
await client.query('COMMIT');
} finally {
client.release();
}
}
async addTemporalToken(token, refresh_token) {
let client = await this.pool.connect();
try {
let id = md5(refresh_token + Date.now());
await client.query('BEGIN');
await client.query('INSERT INTO temp_tokens (id, access_token, refresh_token) VALUES ($1, $2, $3)', [id, token, refresh_token]);
await client.query('COMMIT');
return id;
} finally {
client.release();
}
return null;
}
async getTemporalToken(id) {
let client = await this.pool.connect();
try {
await client.query("DELETE FROM temp_tokens WHERE expires_at < NOW()");
let response = await client.query('SELECT * FROM temp_tokens WHERE id = $1', [id]);
if (response.rowCount > 0) {
await client.query('DELETE FROM temp_tokens WHERE id = $1', [id]);
return response.rows[0];
} else {
return null;
}
} finally {
client.release();
}
return null;
}
async setToken(id, token, refresh_token) {
let client = await this.pool.connect();
try {
await client.query('BEGIN');
await client.query("INSERT INTO tokens (access_token, refresh_token, user_id) VALUES ($1,$2,$3) ON CONFLICT (user_id) do update SET access_token = $1, refresh_token = $2, expires_at = (CURRENT_TIMESTAMP + '00:55:00'::interval) WHERE tokens.user_id = $3", [token, refresh_token, id]);
await client.query('COMMIT');
} finally {
client.release();
}
}
async getToken(id) {
let client = await this.pool.connect();
try {
let response = await client.query('SELECT * FROM tokens WHERE user_id = $1', [id]);
if (response.rowCount > 0) {
return response.rows[0];
} else {
return null;
}
} finally {
client.release();
}
return null;
}
}
module.exports = Database;