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
|
const path = require('path');
const sqlite3 = require("better-sqlite3");
const SU = 0;
// Start db connection
const connect = async function () {
try {
const db = await new sqlite3(path.resolve('db/sampleAPI.db'), {fileMustExist: true});
return db;
} catch (err) {
console.error(err);
}
};
module.exports = {
countAdmins: async function() {
try {
let db = await connect();
let sql = 'select count(*) as c from user where profile = ?';
let query = db.prepare(sql);
let row = await query.all(SU);
return row[0].c;
} catch (err) {
return 0;
}
},
getAllCities: async function (req, res, next) {
try {
let db = await connect();
let sql = 'select * from city';
let query = db.prepare(sql);
let rows = await query.all();
return rows;
} catch (err) {
res.status(400).json(err.message);
}
},
getAllContinents: async function (req, res, next) {
try {
let db = await connect();
let sql = 'select * from continent';
let query = db.prepare(sql);
let rows = await query.all();
return rows;
} catch (err) {
res.status(400).json(err.message);
}
},
getAllCountries: async function (req, res, next) {
try {
let db = await connect();
let sql = 'select * from country';
let query = db.prepare(sql);
let rows = await query.all();
return rows;
} catch (err) {
res.status(400).json(err.message);
}
},
getAllLanguages: async function (req, res, next) {
try {
let db = await connect();
let sql = 'select * from countrylanguage';
let query = db.prepare(sql);
let rows = await query.all();
return rows;
} catch (err) {
res.status(400).json(err.message);
}
},
getCitiesCtry: async function (req, res, next) {
try {
let db = await connect();
let sql = 'select * from city where countrycode = ?';
let query = db.prepare(sql);
let rows = await query.all(req.params.ctry);
return rows;
} catch (err) {
res.status(400).json(err.message);
}
},
getUser: async function (req, res, next) {
try {
let db = await connect();
let sql = 'select * from user where email = ?';
let query = db.prepare(sql);
let row = await query.get(req.body.email);
res.locals.user = row;
} catch (err) {
res.status(400).json(err.message);
}
},
insertUser: async function (req, res, next) {
try {
let db = await connect();
let sql = 'insert into user (email, password, bio) values(?, ?, ?)';
let query = db.prepare(sql);
let row = await query.run(req.body.email, res.locals.hash, req.body.bio);
res.locals.user = row;
} catch (err) {
res.status(400).json(err.message);
}
},
insertCity: async function (req, res, next) {
try {
let db = await connect();
let sql = 'insert into city values(?, ?, ?, ?)';
let query = db.prepare(sql);
let row = await query.run(req.body.name, req.body.countrycode, req.body.district, req.body.population);
} catch (err) {
res.status(400).json(err.message);
}
},
toggleAdmin: async function(req, res, next) {
try {
let db = await connect();
await this.getUser(req, res, next);
let pro = res.locals.user.profile;
if (pro == SU)
pro = 99;
else
pro = SU;
let sql = 'update user set profile = ? where email = ?';
let query = db.prepare(sql);
await query.run(pro, res.locals.user.email);
} catch (err) {
res.status(400).json(err.message);
}
}
}
|