Repository navigation
Expand file tree
/
Copy pathSQLAirBase.h
More file actions
478 lines (455 loc) · 20.9 KB
/
Copy pathSQLAirBase.h
File metadata and controls
478 lines (455 loc) · 20.9 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
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
#ifndef SQL_AIR_BASE_H
#define SQL_AIR_BASE_H
/*
* A base class for SQL-Air -- 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 <iostream>
#include <tuple>
#include "Helper.h"
#include "CSV.h"
/*
* A base class for SQL-Air. The base class provides some of the core
* string processing and validation features of SQLAir. The actual processing
* of queries is delegated to a derived class which can manage data and
* implement the queries in slightly different ways.
*
* @note Use an instance of the derived SQLAir class instead of this base
* class.
*
* @note Ideally, this class should take a CSV class as a template argument
* and be very generic. However, for this project, we are assuming that we
* are working with a specific CSV object.
*
* Copyright (C) 2021 raodm@miamioh.edu
*/
class SQLAirBase {
public:
/**
* Top-level method to process a SQL-air query.
*
* @param sql The SQL-air query to be processed by this method.
*
* @param os The output stream to where results from the processing are
* to be written.
*
* @return This method returns true if further queries are to be processed.
* This method returns false if the command was "exit;"
*/
virtual bool process(const std::string& sql, std::ostream& os);
/**
* 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.
*/
virtual CSV& loadAndGet(std::string fileOrURL) {
throw Exp("loadAndGet method not implemented");
}
/**
* 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.
*/
virtual void selectQuery(CSV& csv, bool mustWait, StrVec colNames,
const int whereColIdx, const std::string& cond,
const std::string& value, std::ostream& os) {
throw Exp("select not implemented.");
}
/**
* 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"
*/
virtual void updateQuery(CSV& csv, bool mustWait, StrVec colNames,
StrVec values, const int whereColIdx, const std::string& cond,
const std::string& value, std::ostream& os) {
throw Exp("update not implemented.");
}
/**
* 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"
*/
virtual void insertQuery(CSV& csv, bool mustWait, StrVec colNames,
StrVec values, std::ostream& os) {
throw Exp("insert not implemented.");
}
/**
*
* 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"
*/
virtual void deleteQuery(CSV& csv, bool mustWait, const int whereColIdx,
const std::string& cond, const std::string& value, std::ostream& os) {
throw Exp("insert not implemented.");
}
/**
* 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.
*/
virtual void saveQuery(std::ostream& os) {
throw Exp("save not implemented.");
}
/**
* 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.
*/
virtual void runServer(boost::asio::ip::tcp::acceptor& server,
const int maxThr) {
throw Exp("runServer not implemented.");
}
/**
* A simple virtual destructor to be compliant with the virtual base class
* requirements of C++
*/
virtual ~SQLAirBase() {}
protected:
/**
* Helper method to check if value (in a column in a given row)
* matches/satisfies a given condition. This method is used in select
* and update statements with a "where" clause with conditions of the form
* "... where col1 = 10" or "... where name like Smith" or
* "... where year <> 2020".
*
* @param colVal The value in a column of the CSV to checked (against the
* value specified in the query).
*
* @param cond The condition to be checked. The string can be one of
* 3 values, namely: "=", "<>", or "like"
*
* @param value The value specified by the user to be used.
*
* @return This method returns \c true if the condition is met. Otherwise
* it returns \c false.
*/
virtual bool matches(const std::string& colVal, const std::string& cond,
const std::string& value) const;
/**
* Helper method to check if the list of column names (specified by the
* user) are valid and present in a given CSV. If a column name is
* invalid, then this method throws an exception.
*
* @param csv The CSV used to determine if the column names are valid.
* @param colNames The list of column names to be checked.
* @param emptyOK It is ok for the list to be empty. If not this method
* throws an exception if the list is empty.
* @param starOk It is ok if the list is just a "*". Otherwise this method
* throws an exception.
* @exception If a column name is invalid this method throws an exception.
*/
virtual void checkColNames(const CSV& csv, const StrVec& colNames,
const bool emptyOK = false, const bool starOk = true) const;
/**
* Checks if a SQL query is valid and calls the selectQuery() API method
* to process a valid select statement. This method is called from
* the process method to process a "select" statement.
*
* @param sql The tokens in the select statement to be processed.
* @param mustWait Flag to indicate if the query must keep running until
* at least 1 matching row is found.
* @param os The output stream to where the results are to be written.
*
* @exception This method throws an exception if error occur when
* processing the specified SQL
*/
virtual void validateAndProcessSelect(const StrVec& sql, bool mustWait,
std::ostream &os);
/**
* Checks if a SQL query is valid and calls the updateQuery() API method
* to process a valid update statement. This method is called from
* the process method to process a "update" statement.
*
* @param sql The tokens in the update statement to be processed.
* @param mustWait Flag to indicate if the query must keep running until
* at least 1 row is updated.
* @param os The output stream to where the results are to be written.
*
* @exception This method throws an exception if error occur when
* processing the specified SQL
*/
virtual void validateAndProcessUpdate(const StrVec& sql, bool mustWait,
std::ostream &os);
/**
* Checks if a SQL query is valid and calls the insertQuery() API method
* to process a valid insert statement. This method is called from
* the process method to process an "insert" statement.
*
* @param sql The tokens in the insert statement to be processed.
* @param mustWait Flag to indicate if the query must keep trying until
* a row is inserted.
* @param os The output stream to where the results are to be written.
*
* @exception This method throws an exception if error occur when
* processing the specified SQL
*/
virtual void validateAndProcessInsert(const StrVec& sql, bool mustWait,
std::ostream &os);
/**
* Checks if a SQL query is valid and calls the deleteQuery() API method
* to delete rows from a CSV. The specified CSV also becomes the
* next default (until some other statement changes the default). This
* method is called from the process() method to process
* an "delete" statement.
*
* @param sql The tokens in the insert statement to be processed.
* @param mustWait Flag to indicate if the query must keep trying until
* a row is deleted.
* @param os The output stream to where the results are to be written.
*
* @exception This method throws an exception if error occur when
* processing the specified query.
*/
virtual void validateAndProcessDelete(const StrVec& sql, bool mustWait,
std::ostream &os);
/**
* Checks if a SQL query is valid and calls the loadAndGet() API method
* to load and set the specified CSV. The specified CSV also becomes the
* next default (until some other statement changes the default). This
* method is called from the process() method to process an "use" statement.
*
* @param sql The tokens in the insert statement to be processed.
* @param mustWait This flag is not applicable for this query. If specified,
* it is ignored.
* @param os The output stream to where the results are to be written.
*
* @exception This method throws an exception if error occur when
* processing the specified query.
*/
virtual void validateAndProcessUse(const StrVec& sql, bool mustWait,
std::ostream &os);
/**
* Checks if a SQL query is valid and calls the saveQuery() API method
* to save the specified CSV or the currently used CSV. The specified CSV,
* if any, also becomes the next default (until some other statement
* changes the default). This method is called from the process() method
* to process an "save" statement.
*
* @param sql The tokens in the insert statement to be processed.
* @param mustWait This flag is not applicable for this query. If specified,
* it is ignored.
* @param os The output stream to where the results are to be written.
*
* @exception This method throws an exception if error occur when
* processing the specified query.
*/
virtual void validateAndProcessSave(const StrVec& sql, bool mustWait,
std::ostream &os);
/**
* This is a helper method that is called from process() method to
* break a given query into tokens. This method generates tokens via
* the following steps:
*
* 1. It trims leading and trailing blank spaces and removes any trailing
* ';' by calling Helper::trim() method.
* 2. It uses CSV::tokenize() method to create tokens.
* 3. If the first word is "wait" it remove that word and sets the isWait
* flag. It removes the "wait" clause from the tokens.
* 4. Returns the tokens back
*
* @param sql A long string of sql-air query to be tokenized.
* @return Returns the following 3 values from query tokenization process:
* 1. The tokens from the sql. If the sql had no tokens then this
* method returns an empty vector.
* 2. A boolean to indicate if the first token was a "wait" clause. The
* 3. An integer corresponding to the command to be processed.
*/
virtual std::tuple<StrVec, bool, int>
preprocess(const std::string& sql) const;
private:
/**
* Convenience internal helper method to extract name-value pair from
* a SQL query into two separate vector-of-string. This method is used
* to extract column-names and values from update or insert queries. This
* method is called from validateAndProcessUpdate() method as well as from
* validateAndProcessInsert() method.
*
* @param csv The reference CSV to be used to check if column names are
* valid.
* @param sql The tokens in the query from where column names and values
* are to be extracted.
* @param nameStartIdx The starting index in sql for column names.
* @param valStartIdx The starting index in sql for values.
* @param inc The increment value between adjacent tokens. It is 3 for
* update and 1 for insert statement respectively.
* @param delimiter An optional delimiter value to check for. It is "="
* in the case of update statements and empty string for insert.
* @param endToken An optional end token to use. It is ")" for insert
* and "where" for update.
* @return A of vector-of-string, the first is list of column names
* and the second is corresponding list of values. The 3rd value is the
* index position in sql where the processing was stopped.
* @exception This method throws exceptions when errors are encountered.
*/
std::tuple<StrVec, StrVec, int> getNamesValues(const CSV& csv,
const StrVec& sql, int nameStartIdx, int valStartIdx, int inc = 1,
const std::string& delimiter = "",
const std::string& endToken = "") const;
};
#endif /* SQL_AIR_BASE_H */