/
server.js
245 lines (212 loc) · 7.49 KB
/
server.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
// Authors: Ben Frey, Grant Toegnes, Logan Schaffer
// Professor: Dr. Marrinan
// Course: CISC 375: Web Development
// Date: 220501
// Description: The main purpose of this project is to demonstrate the implementation of a
// RESTful API Server that can access information stored on a database within this repository.
// The SQL database contains St. Paul crime data over the last few years and can be accessed
// via cURL HTTP requests when the API server is hosted locally. To setup the server, please
// see the "Installation Process" section.
// Built-in Node.js modules
let fs = require('fs');
let path = require('path');
let cors = require('cors');
// NPM modules
let express = require('express');
let sqlite3 = require('sqlite3');
let public_dir = path.join(__dirname, 'public');
let template_dir = path.join(__dirname, 'templates');
let db_filename = path.join(__dirname, 'db', 'stpaul_crime.sqlite3');
// Setup APP (API Server) on port 8000
let app = express();
app.use(express.json());
app.use(cors());
let port = 8000;
// Open sqlite3 database
let db = new sqlite3.Database(db_filename, sqlite3.OPEN_READWRITE, (err) => {
if (err) {
console.log('Error opening ' + db_filename);
}
else {
console.log('Now connected to ' + db_filename);
}
});
// GET /codes
app.get('/codes', (req, res) => {
// Initial part of sql query
sqlQuery = 'SELECT * FROM Codes';
// Specific code(s) was(were) supplied ex: ?code=120,140
let codes = req.query.code;
if(codes){
codeArray = codes.split(",");
// Add to sql query
sqlQuery = sqlQuery + " WHERE code = " + codeArray.join(" OR code = ");
}
// End of sql query statement
sqlQuery += " ORDER BY code";
// Make the database query
new Promise( (resolve, reject) => {
db.all(sqlQuery, (err, rows) => {
if (err) {
console.log(err); // error somewhere, cannot resolve promise
reject();
} else {
resolve(rows); // rows were received, now resolve
}
});
})
.then(rows => {
res.status(200).type('application/json').send(rows);
}).catch(err => {
res.status(500).send("Error querying database");
});
});
// Get /neighborhoods
app.get('/neighborhoods', (req, res) => {
// Initial part of sql query
sqlQuery = 'SELECT * FROM Neighborhoods';
// Specific code(s) was(were) supplied ex: ?id=11,14
let ids = req.query.id;
if(ids){
idArray = ids.split(",");
// Add to sql query
sqlQuery = sqlQuery + " WHERE neighborhood_number = " + idArray.join(" OR neighborhood_number = ");
}
// End of sql query statement
sqlQuery += " ORDER BY neighborhood_number";
// Make the database query
new Promise( (resolve, reject) => {
db.all(sqlQuery, (err, rows) => {
if (err) {
console.log(err); // error somewhere, cannot resolve promise
reject();
} else {
resolve(rows); // rows were received, now resolve
}
});
})
.then(rows => {
res.status(200).type('application/json').send(rows);
}).catch(err => {
res.status(500).send("Error querying database");
});
});
// Get /incidents
app.get('/incidents', (req, res) => {
// Initial part of sql query
var sqlQuery = 'SELECT * FROM Incidents';
var params = [];
// Specific query (logan and grant)
//this case_number is not specifically needed, just using for testing purposes
if(req.query.case_number){
sqlQuery+= ' WHERE case_number= "'+req.query.case_number+'"';
}
if(req.query.neighborhood){
nieghborhoodArray = req.query.neighborhood.split(",");
sqlQuery += " WHERE neighborhood_number = " + nieghborhoodArray.join(" OR neighborhood_number = ");
}
if(req.query.grid){
gridArray = req.query.grid.split(",");
if(req.query.neighborhood){
sqlQuery += " AND police_grid = " + gridArray.join(" OR police_grid = ");
}else{
sqlQuery += " WHERE police_grid = " + gridArray.join(" OR police_grid = ");
}
}
if(req.query.code){
codeArray = req.query.code.split(",");
if(req.query.grid || req.query.neighborhood){
sqlQuery += " AND code = " + codeArray.join(" OR code = ");
}else{
sqlQuery += " WHERE code = " + codeArray.join(" OR code = ");
}
}
if(req.query.start_date && req.query.end_date){
if(req.query.code || req.query.grid || req.query.neighborhood){
sqlQuery += " AND date_time BETWEEN ? and ?";
params.push(req.query.start_date);
params.push(req.query.end_date);
}else{
sqlQuery += " WHERE date_time BETWEEN ? and ?";
params.push(req.query.start_date);
params.push(req.query.end_date);
}
}else if(req.query.end_date){
if(req.query.code || req.query.grid || req.query.neighborhood){
sqlQuery += " AND date_time <= ?";
params.push(req.query.end_date);
}else{
sqlQuery += " WHERE date_time <= ?";
params.push(req.query.end_date);
}
}else if(req.query.start_date){
if(req.query.code || req.query.grid || req.query.neighborhood){
sqlQuery += " AND date_time >= ?";
params.push(req.query.start_date);
}else{
sqlQuery += " WHERE date_time >= ?" ;
params.push(req.query.start_date);
}
}
// End of sql query statement
sqlQuery += " ORDER BY date_time";
if(req.query.limit){
sqlQuery += ' LIMIT '+req.query.limit;
} else {
sqlQuery += ' LIMIT 1000';
}
console.log(sqlQuery);
// Make the database query
new Promise( (resolve, reject) => {
db.all(sqlQuery, params, (err, rows) => {
if (err) {
console.log(err); // error somewhere, cannot resolve promise
reject();
} else {
resolve(rows); // rows were received, now resolve
}
});
})
.then(rows => {
res.status(200).type('application/json').send(rows);
}).catch(err => {
res.status(500).send("Error querying database");
});
});
// Put /new-incident
app.put('/new-incident', (req, res) =>{
db.get('SELECT * FROM Incidents WHERE case_number = ?', [req.body.case_number], (err, row)=> {
if(err || row !== undefined ) {
res.status(500).type('txt').send('Error, could not insert incident');
} else {
db.run('INSERT INTO Incidents (case_number, date_time, code, incident, police_grid, neighborhood_number, block) VALUES(?, ?, ?, ?, ?, ?, ?)',[req.body.case_number, req.body.date_time, req.body.code, req.body.incident, req.body.police_grid, req.body.neighborhoood_number, req.body.block], (err) =>{
if(err){
console.log(err);
res.status(500).type('txt').send('Error, could not insert incident');
}else{
res.status(200).type('txt').send('success');
}
});
}
});
});
// Delete /remove-incident
app.delete('/remove-incident', (req, res) =>{
db.get('SELECT * FROM Incidents WHERE case_number = ?', [req.query.case_number], (err, row)=> {
if(err || row === undefined ) {
res.status(500).type('txt').send('Error, incident does not exist');
} else {
db.run('DELETE FROM Incidents WHERE case_number = ?',[req.query.case_number], (err) =>{
if(err){
console.log(err);
res.status(500).type('txt').send('Error, could not delete');
}else{
res.status(200).type('txt').send('Entry Deleted');
}
});
}
});
});
app.listen(port, () => {
console.log('Now listening on port ' + port);
});