Repository navigation
Expand file tree
/
Copy pathSQLAir.h
More file actions
439 lines (417 loc) · 19.6 KB
/
Copy pathSQLAir.h
File metadata and controls
439 lines (417 loc) · 19.6 KB
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
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
#ifndef SQL_AIR_H
#define SQL_AIR_H
/*
* A very lightweight (light as air) implementation of a simple CSV-based
* database system that uses SQL-like syntax for querying and updating the
* CSV files.
*
* Copyright (C) 2021 raodm@miamioh.edu
*/
#include <boost/asio.hpp>
#include <string>
#include <unordered_map>
#include <iostream>
#include <tuple>
#include <memory>
#include <thread>
#include <atomic>
#include <condition_variable>
#include "SQLAirBase.h"
// Shortcut to smart pointer with TcpStream
using TcpStreamPtr = std::shared_ptr<boost::asio::ip::tcp::iostream>;
/**
* The top-level class that facilitates processing SQL-like queries on CSV
* files. The methods in this class override the default/dummy implementations
* in the base class to perform the actual CRUD operations.
*/
class SQLAir : public SQLAirBase {
public:
// Helper method to process each row in select queries
void selectRowProcess(CSV& csv, StrVec colNames,
const int whereColIdx, const std::string& cond,
const std::string& value,
std::string& rowText, int& rowCount);
// Helper method to process each row in update queries
void updateRowProcess(CSV& csv, StrVec colNames, StrVec values,
const int whereColIdx, const std::string& cond,
const std::string& value, int& rowCount);
/**
* Method to perform the actual operations associated with printing a
* given set of columns in a given CSV that match an optional condition.
* This method is called with a validated (the validation is not foolproof)
* set of parameters. This method must print the column names (separated
* by a tab character) and the selected data (also separated by a tab).
* The method must print the total number of rows selected towards the end.
*
* @note For checking conditions, use the matches() method in the base
* class.
*
* @note This method will be called from multiple threads. Hence, this
* method must take care to ensure its operations are MT-Safe
* (multi-threading safe).
*
* @param csv The CSV data to be used.
*
* @param mustWait If this flag is true, then this query must keep trying
* until at least one row is selected.
*
* @param colNames The column names in the CSV file to be printed by this
* method. The colNames will be just {"*"} or valid column names in the CSV.
*
* @param whereColIdx An optional column specified in a 'where' clause in
* the query for checking. If a 'where' clause was not specified, then this
* parameter will be -1. For example, if "where name like 'Test'" is
* specified, then this parameter will have the index of the 'name' column
* in the CSV.
*
* @param cond The condition to be applied. The condition will always be
* one of "=" (equal-to), "<>" (not equal to), or "like" (substring). For
* example, if "where name like 'Test'" is specified, then this parameter
* will be "like" (without quotes).
*
* @param value The value to be used for comparison. This is parameter
* has the value specified by the user in a 'where' clause. For
* example, if "where name like 'Test'" is specified, then this parameter
* will be "Test" (without quotes).
*
* @param os The output stream to where the results are to be written.
*/
void selectQuery(CSV& csv, bool mustWait, StrVec colNames,
const int whereColIdx, const std::string& cond,
const std::string& value, std::ostream& os) override;
/**
* Method that is called to perform actual operations to update specified
* values in the CSV. This method's documentation uses the following query
* as an example:
*
* update test.csv set rating=2.5, raters=2 where movieid = 12345;
*
* @note This method will be called from multiple threads. Hence, this
* method must take care to ensure its operations are MT-Safe
* (multi-threading safe).
*
* @param csv The CSV whose values are to be updated. Given the above query,
* the CSV will correspond to the data for "test.csv" (loaded into memory
* via call to the loadAndGet() method).
*
* @param mustWait If this flag is true then this method must repeatedly
* try performing the update operation until at least 1 row is updated.
*
* @param colNames The names of the columns to be updated in each row.
* This method may assume the colNames are valid (as they are validated
* in the the update method). Given the above example query, this vector
* will contain {"rating", "raters"}.
*
* @param values The values to be set for each column. This method may
* assume the values and colNames match (as it is validated in the update
* method). Given the above example query, this vector will contain
* {"2.5", "2"}.
*
* @param whereColIdx The integer value corresponding to the column in the
* the where clause if any. If a where clause is not present then this
* value is -1. In the above example query, is value will contain the index
* of the movieid column in test.csv.
*
* @param cond The condition to be used. If a value is not specified it
* is an empty string. Given the above example query, this parameter will
* contain the value "=" (without quotes).
*
* @param value The value to be compared against. Given the above query,
* this parameter will contain the value "12345" (without quotes)
*
* @param os The output stream to where the number of rows updated must
* be written -- e.g." "1 row(s) updated.\n"
*/
void updateQuery(CSV& csv, bool mustWait, StrVec colNames,
StrVec values, const int whereColIdx, const std::string& cond,
const std::string& value, std::ostream& os) override;
/**
* Helper method to perform the actual operations associated with inserting
* a new row into a given CSV. This method's documentation uses the
* following query as an example:
*
* insert into test.csv (name, rating, raters) values ('title', 4.5, 10);
*
* @note This method will be called from multiple threads. Hence, this
* method must take care to ensure its operations are MT-Safe
* (multi-threading safe).
*
* @param csv The CSV whose values are to be updated. Given the above query,
* the CSV will correspond to the data for "test.csv" (loaded into memory
* via call to the loadAndGet() method).
*
* @param mustWait This flag is not used by this method. It is present
* for potential future functionality.
*
* @param colNames The names of the columns to be set in the newly added
* row. If column names are not specified in the query, then this list
* will be empty. This method may assume the colNames are valid (as they
* are validated). Given the above example query, this vector
* will contain {"title", "rating", "raters"}.
*
* @param values The values to be set for each column. This method may
* assume the values and colNames match. Given the above example query,
* this vector will contain {"title", "2.5", "2"}. Columns whose values
* are not specified are assumed to be empty strings.
*
* @param os The output stream to where the number of rows updated must
* be written -- e.g." "1 row inserted.\n"
*/
void insertQuery(CSV& csv, bool mustWait, StrVec colNames, StrVec values,
std::ostream& os) override;
/**
*
* Helper method to perform the actual operations associated with inserting
* a new row into a given CSV. This method's documentation uses the
* following query as an example:
*
* delete from test.csv where year = 2006;
*
* @note This method will be called from multiple threads. Hence, this
* method must take care to ensure its operations are MT-Safe
* (multi-threading safe).
*
* @param csv The CSV whose values are to be updated. Given the above query,
* the CSV will correspond to the data for "test.csv" (loaded into memory
* via call to the loadAndGet() method).
*
* @param mustWait If this flag is true then this method must repeatedly
* try performing the delete operation until at least 1 row is deleted.
*
* @param whereColIdx The integer value corresponding to the column in the
* the where clause if any. If a where clause is not present then this
* value is -1. In the above example query, is value will contain the index
* of the "year" column in test.csv.
*
* @param cond The condition to be used. If a value is not specified it
* is an empty string. Given the above example query, this parameter will
* contain the value "=" (without quotes)
*
* @param value The value to be compared against. Given the above query,
* this parameter will contain the value "2006" (without quotes).
*
* @param os The output stream to where the number of rows updated must
* be written -- e.g." "1 row(s) deleted.\n"
*/
void deleteQuery(CSV& csv, bool mustWait, const int whereColIdx,
const std::string& cond, const std::string& value,
std::ostream& os) override;
/**
* Saves the recently used CSV using the name specified in the recentCSV
* string. If the recent CSV was downloaded from an URL, then this method
* throws an exception (as this feature is not yet implemented). If the CSV
* was loaded from a a file, then the data in the file is overwritten.
*
* @param os The output stream to where the result of saving (if any) is
* to be written.
*
* @exception This method may throw exceptions upon errors.
*/
void saveQuery(std::ostream& os) override;
/**
* Helper method to obtain a reference to a pre-loaded CSV file from the
* inMemoryCSV map. If the requested file is not present, then this
* method loads the data into the inMemoryCSV.
*
* @note Currently, this method is not MT-safe. So avoid calling this
* method from multiple threads simultaneously.
*
* @param fileOrURL Path to a CSV file or a URL to a CSV data to be returned
* by this method. If the path is empty string, then this method returns
* the most recently accessed CSV file. This method intentionally uses
* pass-by-value.
*
* @return A reference to the in-memory CSV file. If the CSV data could
* not be loaded, then this method throws an exception.
*
* @exception This method throws an exception if the file could not
* loaded.
*/
CSV& loadAndGet(std::string fileOrURL) override;
/**
* Method to have this class run as a web-server that runs forever and
* keeps processing requests. This method does not do the core processing.
* Instead, for each connection it starts a detached-thread to process the
* request from the client. Hence, the task of processing HTTP-GET request
* is delegated to the clientThread method.
*
* @note This method uses detached threads to process each request.
*
* @param server The BOOST acceptor that must be used to accept connections
* from clients.
*
* @param maxThr An optional maximum number of threads to be used by this
* method.
*/
void runServer(boost::asio::ip::tcp::acceptor& server, const int maxThr);
protected:
/**
* This method is a refactored utility method. This method is called from
* the seqlectQuery method. This method performs the actual operations
* associated with printing a given set of columns in a given CSV that
* match an optional condition. This method must print the column names
* (separated by a tab character) and the selected data (also separated
* by a tab). The method returns the total number of rows printed.
*
* @note For checking conditions, use the matches() method in the base
* class.
*
* @note This method will be called from multiple threads. Hence, this
* method must take care to ensure its operations are MT-Safe
* (multi-threading safe).
*
* @param csv The CSV data to be used.
*
* @param mustWait If this flag is true, then this query must keep trying
* until at least one row is selected.
*
* @param colNames The column names in the CSV file to be printed by this
* method. The colNames will be just {"*"} or valid column names in the CSV.
*
* @param whereColIdx An optional column specified in a 'where' clause in
* the query for checking. If a 'where' clause was not specified, then this
* parameter will be -1. For example, if "where name like 'Test'" is
* specified, then this parameter will have the index of the 'name' column
* in the CSV.
*
* @param cond The condition to be applied. The condition will always be
* one of "=" (equal-to), "<>" (not equal to), or "like" (substring). For
* example, if "where name like 'Test'" is specified, then this parameter
* will be "like" (without quotes).
*
* @param value The value to be used for comparison. This is parameter
* has the value specified by the user in a 'where' clause. For
* example, if "where name like 'Test'" is specified, then this parameter
* will be "Test" (without quotes).
*
* @param os The output stream to where the results are to be written.
*
* @return The number of rows printed by this method.
*/
int trySelect(CSV& csv, bool mustWait, StrVec colNames,
const int whereColIdx, const std::string& cond,
const std::string& value, std::ostream& os);
/**
* Method that is called to perform actual operations to update specified
* values in the CSV. This method's documentation uses the following query
* as an example:
*
* update test.csv set rating=2.5, raters=2 where movieid = 12345;
*
* @note This method will be called from multiple threads. Hence, this
* method must take care to ensure its operations are MT-Safe
* (multi-threading safe).
*
* @param csv The CSV whose values are to be updated. Given the above query,
* the CSV will correspond to the data for "test.csv" (loaded into memory
* via call to the loadAndGet() method).
*
* @param mustWait If this flag is true then this method must repeatedly
* try performing the update operation until at least 1 row is updated.
*
* @param colNames The names of the columns to be updated in each row.
* This method may assume the colNames are valid (as they are validated
* in the the update method). Given the above example query, this vector
* will contain {"rating", "raters"}.
*
* @param values The values to be set for each column. This method may
* assume the values and colNames match (as it is validated in the update
* method). Given the above example query, this vector will contain
* {"2.5", "2"}.
*
* @param whereColIdx The integer value corresponding to the column in the
* the where clause if any. If a where clause is not present then this
* value is -1. In the above example query, is value will contain the index
* of the movieid column in test.csv.
*
* @param cond The condition to be used. If a value is not specified it
* is an empty string. Given the above example query, this parameter will
* contain the value "=" (without quotes).
*
* @param value The value to be compared against. Given the above query,
* this parameter will contain the value "12345" (without quotes)
*
* @return This method returns the number of rows updated by this method.
*/
int tryUpdate(CSV& csv, bool mustWait, StrVec colNames,
StrVec values, const int whereColIdx, const std::string& cond,
const std::string& value);
/**
* A thread-main method to process each request from a web-client in a
* separate thread. This method is called from the runServer method
* each time a client connects, when sql-air is running as a web-server.
* This web-server will get the following 2 types of HTTP-GET requests:
* 1. Request to run a query where the request starts with the prefix
* "/sql-air?query=select;"
* 2. All other requests are assumed to be requests for files that are
* returned back to the client using http::file() helper method in
* the HTTPFile class.
*
* @param is The socket stream to be used for performing input operations.
* @param out The socket stream to be used for performing output operations.
*/
// void clientThread(TcpStreamPtr client);
void clientThread(std::istream& is, std::ostream& os);
/**
* Internal helper method to obtain CSV file from a given URL. The URL
* processing is initially done in the gloadAndGet method that calls
* this method. The URL of the form
* "http://os1.csi.miamioh.edu/raodm/test.csv"
* is broken down into host, port, and path by calling the
* Helper::breakdownURL() method. However, the user
* must continue to use the full URL for referencing the data. This method
* establishes the TCP stream and simply calls the csv.load() method to
* load the CSV data from the TCP stream.
*
* @param csv The CSV object into which the data is to be loaded.
*
* @param hostName The server host name from where the data is to be
* retrieved. This would be "localhost" or "os1.csi.miamioh.edu"
*
* @param port The port number from where the data is to be retrieved. This
* value is typically 80 but could be different.
*
* @param path The path to the CSV file on the server. The path to the
* file on the server. This is of the form "/test.csv"
*
* @exception Exp This method throws exceptions if errors ocurr when
* reading the data from the server.
*/
void loadFromURL(CSV& csv, const std::string& hostName,
const std::string& port, const std::string& path);
private:
// used in server unique lock
std::mutex serverMutex;
/**
* The most recently referenced CSV in a query. This value is updated
* int he getOrLoadCSV method.
*/
std::string recentCSV;
/**
* This is a convenience mutex that is used to enable thread-safe
* operations on the recentCSV instance variable in this class. This
* mutex is locked and unlocked in the loadAndGet method in this class.
*/
std::mutex recentCSVMutex;
/**
* An unordered map to maintain the CSV files that have been accessed
* in recent queries. This map is used to provide convenient/rapid
* access to CSV files that the user has recently worked with. The most
* recent CSV used is tracked by the recentCSV instance variable. See the
* getOrLoadCSV() method in this class.
*/
std::unordered_map<std::string, CSV> inMemoryCSV;
// -------------[ Limit number of threads ]-------------------
/** The atomic counter that tracks the number of active threads.
* This counter is incremented in runServer each time a thread is started.
* It is decremented at the end of clientThread each time a thread finishes.
*/
std::atomic<int> numThreads = {0};
/** A condition variable to wait if number of threads being used
* exceeds a given limit. The runServer method waits on it. The
* clientThread method call notify.
*/
std::condition_variable thrCond;
// -----------------------------------------------------------
};
#endif /* SQL_AIR_H */