Line data Source code
1 : /*! \brief This file have the implementation for SQLiteWrapper class.
2 : \file sqlitewrapper.cc
3 : \author Alvaro Denis <denisacostaq@gmail.com>
4 : \date 6/19/2019
5 :
6 : \copyright
7 : \attention <h1><center><strong>COPYRIGHT © 2019 </strong>
8 : [<strong>denisacostaq</strong>][denisacostaq-URL].
9 : All rights reserved.</center></h1>
10 : \attention This file is part of [<strong>DAQs</strong>][DAQs-URL].
11 :
12 : Redistribution and use in source and binary forms, with or without
13 : modification, are permitted provided that the following conditions
14 : are met:
15 : - 1. Redistributions of source code must retain the above copyright
16 : notice, this list of conditions and the following disclaimer.
17 : - 2. Redistributions in binary form must reproduce the above copyright
18 : notice, this list of conditions and the following disclaimer in the
19 : documentation and/or other materials provided with the distribution.
20 : - 3. Neither the name of the University nor the names of its contributors
21 : may be used to endorse or promote products derived from this software
22 : without specific prior written permission.
23 :
24 : THIS PRODUCT IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
25 : AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
26 : IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
27 : ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER BE LIABLE FOR ANY
28 : DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
29 : (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
30 : LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
31 : ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
32 : (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
33 : THIS PRODUCT, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
34 :
35 : [denisacostaq-URL]: https://about.me/denisacostaq "Alvaro Denis Acosta"
36 : [DAQs-URL]: https://github.com/denisacostaq/DAQs "DAQs"
37 : */
38 : #include "src/database-server/data-source/sqlitewrapper.h"
39 :
40 : #include <cerrno>
41 : #include <cstring>
42 : #include <exception>
43 : #include <iostream>
44 : #include <memory>
45 : #include <string>
46 : #include <vector>
47 :
48 : #include <sqlite3.h>
49 :
50 : #include "src/database-server/data-model/variable.h"
51 : #include "src/database-server/data-model/varvalue.h"
52 :
53 26 : SQLiteWrapper::SQLiteWrapper(const std::string &db_path) : IDataSource() {
54 24 : int err = sqlite3_open(db_path.c_str(), &db_);
55 24 : if (err != SQLITE_OK) {
56 2 : std::cerr << sqlite3_errmsg(db_) << "\n";
57 2 : std::cerr << std::strerror(sqlite3_system_errno(db_)) << "\n";
58 2 : throw std::string{"Can't open database " + db_path};
59 : } else {
60 22 : std::clog << "Opened database successfully\n";
61 : }
62 22 : }
63 :
64 42 : SQLiteWrapper::~SQLiteWrapper() { sqlite3_close(db_); }
65 :
66 18 : IDataSource::Err SQLiteWrapper::create_scheme() noexcept {
67 36 : std::vector<std::string> stataments;
68 18 : stataments.reserve(2);
69 : std::string sql =
70 : "CREATE TABLE VARIABLE("
71 : "ID INTEGER PRIMARY KEY AUTOINCREMENT,"
72 : "COLOR CHAR(50),"
73 36 : "NAME CHAR(50) NOT NULL UNIQUE);";
74 18 : stataments.push_back(sql);
75 18 : sql =
76 : "CREATE TABLE VARIABLE_VALUE("
77 : "ID INTEGER PRIMARY KEY AUTOINCREMENT,"
78 : "VAL DOUBLE NOT NULL,"
79 : "TIMESTAMP INTEGER NOT NULL,"
80 : "VARIABLE_ID INT NOT NULL,"
81 : "FOREIGN KEY (VARIABLE_ID) REFERENCES VARIABLE(ID));"; // FIXME(denisacostaq@gmail.com):
82 : // add constrints
83 18 : stataments.push_back(sql);
84 54 : for (const auto &s : stataments) {
85 36 : char *err = nullptr;
86 36 : int rc = sqlite3_exec(db_, s.c_str(), nullptr, nullptr, &err);
87 36 : if (rc != SQLITE_OK) {
88 0 : std::cerr << "SQL error: " << err << "\n";
89 0 : sqlite3_free(err);
90 0 : return Err::Failed;
91 : } else {
92 36 : std::clog << "Table created successfully\n";
93 : }
94 : }
95 18 : return Err::Ok;
96 : }
97 :
98 34 : IDataSource::Err SQLiteWrapper::add_variable(const Variable &var) noexcept {
99 : std::string sql =
100 68 : sqlite3_mprintf("INSERT INTO VARIABLE(NAME, COLOR) VALUES('%q', '%q')",
101 136 : var.name().c_str(), var.color().c_str());
102 34 : char *err = nullptr;
103 34 : int res = sqlite3_exec(db_, sql.c_str(), nullptr, nullptr, &err);
104 34 : if (res != SQLITE_OK) {
105 4 : std::cerr << "Can not insert " << var.name() << ". " << err << "\n";
106 4 : sqlite3_free(err);
107 4 : return Err::Failed;
108 : }
109 30 : std::clog << "Insertion ok\n";
110 30 : return Err::Ok;
111 : }
112 :
113 442 : IDataSource::Err SQLiteWrapper::add_variable_value(
114 : const VarValue &var) noexcept {
115 442 : auto now{std::chrono::system_clock::now()};
116 : auto timestamp{std::chrono::duration_cast<std::chrono::milliseconds>(
117 442 : now.time_since_epoch())};
118 442 : char *err_msg = nullptr;
119 442 : std::string sql = sqlite3_mprintf(
120 : "INSERT INTO VARIABLE_VALUE(VAL, TIMESTAMP, VARIABLE_ID) VALUES(%f, %ld, "
121 : "(SELECT ID FROM VARIABLE WHERE NAME = '%q'))",
122 1326 : var.val(), timestamp.count(), var.name().c_str());
123 442 : if (sqlite3_exec(db_, sql.c_str(), nullptr, this, &err_msg) != SQLITE_OK) {
124 4 : std::cerr << "error " << err_msg << "\n";
125 4 : sqlite3_free(err_msg);
126 4 : return Err::Failed;
127 : }
128 438 : return Err::Ok;
129 : }
130 :
131 2 : IDataSource::Err SQLiteWrapper::fetch_variables(
132 : const std::function<void(const Variable &var, size_t index)>
133 : &send_vale) noexcept {
134 2 : char *err_msg = nullptr;
135 4 : std::string query = "SELECT COLOR, NAME FROM VARIABLE";
136 : using callbac_t = decltype(send_vale);
137 : using unqualified_callbac_t =
138 : std::remove_const_t<std::remove_reference_t<callbac_t>>;
139 4 : if (sqlite3_exec(
140 : db_, query.c_str(),
141 14 : +[](void *callback, int argc, char **argv, char **azColName) {
142 12 : Variable var{};
143 18 : for (decltype(argc) i = 0; i < argc; i++) {
144 12 : if (strcmp("NAME", azColName[i]) == 0) {
145 12 : std::string name{""};
146 6 : if (argv[i] != nullptr && std::strcmp(argv[i], "")) {
147 6 : name = argv[i];
148 : }
149 6 : var.set_name(name);
150 6 : } else if (strcmp("COLOR", azColName[i]) == 0) {
151 6 : if (strcmp("COLOR", azColName[i]) == 0) {
152 12 : std::string color{""};
153 6 : if (argv[i] != nullptr && std::strcmp(argv[i], "")) {
154 6 : color = argv[i];
155 : }
156 6 : var.set_color(color);
157 : }
158 : } else {
159 0 : return -1;
160 : }
161 : }
162 : static_cast<callbac_t> (
163 6 : *static_cast<unqualified_callbac_t *>(callback))(var, 0);
164 6 : return 0;
165 8 : },
166 : const_cast<unqualified_callbac_t *>(&send_vale),
167 2 : &err_msg) != SQLITE_OK) {
168 0 : std::cerr << "error " << err_msg << "\n";
169 0 : sqlite3_free(err_msg);
170 0 : return Err::Failed;
171 : }
172 2 : return Err::Ok;
173 : }
174 :
175 : namespace {
176 : struct CountCallback {
177 : size_t index;
178 : std::function<void(const VarValue &val, size_t)> *send_vale;
179 : };
180 : }; // namespace
181 8 : IDataSource::Err SQLiteWrapper::fetch_variable_values(
182 : const std::string &var_name,
183 : const std::function<void(const VarValue &val, size_t index)>
184 : &send_vale) noexcept {
185 8 : char *err_msg = nullptr;
186 8 : std::string query = sqlite3_mprintf(
187 : "SELECT VAL, TIMESTAMP FROM VARIABLE_VALUE WHERE VARIABLE_ID = (SELECT "
188 : "ID FROM VARIABLE WHERE NAME = '%q')",
189 24 : var_name.c_str());
190 8 : CountCallback cc{
191 : 0,
192 8 : const_cast<std::function<void(const VarValue &, size_t)> *>(&send_vale)};
193 16 : if (sqlite3_exec(db_, query.c_str(),
194 48 : +[](void *cc, int argc, char **argv, char **azColName) {
195 40 : VarValue val{};
196 60 : for (int i = 0; i < argc; i++) {
197 40 : if (strcmp("VAL", azColName[i]) == 0) {
198 : try {
199 20 : size_t processed = 0;
200 20 : val.set_val(std::stod(argv[i], &processed));
201 0 : } catch (std::invalid_argument e) {
202 0 : std::cerr << e.what();
203 0 : return -1;
204 0 : } catch (std::out_of_range e) {
205 0 : std::cerr << e.what();
206 0 : return -1;
207 : }
208 20 : } else if (strcmp("TIMESTAMP", azColName[i]) == 0) {
209 : try {
210 20 : size_t processed = 0;
211 20 : val.set_timestamp(std::stoull(argv[i], &processed));
212 0 : } catch (std::invalid_argument e) {
213 0 : std::cerr << e.what();
214 0 : return -1;
215 0 : } catch (std::out_of_range e) {
216 0 : std::cerr << e.what();
217 0 : return -1;
218 : }
219 : }
220 : }
221 20 : auto count_callback{static_cast<CountCallback *>(cc)};
222 20 : auto cb{count_callback->send_vale};
223 20 : (*cb)(val, count_callback->index);
224 20 : ++count_callback->index;
225 20 : return 0;
226 28 : },
227 8 : &cc, &err_msg) != SQLITE_OK) {
228 0 : std::cerr << "error " << err_msg << "\n";
229 0 : sqlite3_free(err_msg);
230 0 : return Err::Failed;
231 : }
232 8 : return Err::Ok;
233 : }
234 :
235 4 : IDataSource::Err SQLiteWrapper::count_variable_values(
236 : const std::string &var_name,
237 : const std::function<void(size_t count)> &send_count) noexcept {
238 4 : char *err_msg = nullptr;
239 4 : std::string count_query = sqlite3_mprintf(
240 : "SELECT COUNT(*) FROM VARIABLE_VALUE WHERE VARIABLE_ID = (SELECT "
241 : "ID FROM VARIABLE WHERE NAME = '%q')",
242 12 : var_name.c_str());
243 8 : if (sqlite3_exec(
244 : db_, count_query.c_str(),
245 12 : +[](void *callback, int argc, char **argv, char **azColName) {
246 4 : for (int i = 0; i < argc; ++i) {
247 4 : if (strcmp("COUNT(*)", azColName[i]) == 0) {
248 4 : size_t count{};
249 : try {
250 4 : size_t processed = 0;
251 4 : count = std::stoull(argv[i], &processed);
252 0 : } catch (std::invalid_argument e) {
253 0 : std::cerr << e.what();
254 0 : return -1;
255 0 : } catch (std::out_of_range e) {
256 0 : std::cerr << e.what();
257 0 : return -1;
258 : }
259 4 : (*static_cast<std::function<void(size_t count)> *>(callback))(
260 : count);
261 4 : return 0;
262 : }
263 : }
264 0 : return 0;
265 8 : },
266 : const_cast<std::function<void(size_t count)> *>(&send_count),
267 4 : &err_msg) != SQLITE_OK) {
268 0 : std::cerr << "error " << err_msg << "\n";
269 0 : sqlite3_free(err_msg);
270 0 : return Err::Failed;
271 : }
272 4 : return Err::Ok;
273 : }
274 :
275 8 : IDataSource::Err SQLiteWrapper::fetch_variable_values(
276 : const std::string &var_name,
277 : const std::chrono::system_clock::time_point &start_date,
278 : const std::chrono::system_clock::time_point &end_date,
279 : const std::function<void(const VarValue &val, size_t index)>
280 : &send_vale) noexcept {
281 8 : char *err_msg = nullptr;
282 : const std::int64_t sd{
283 16 : std::chrono::duration_cast<std::chrono::milliseconds>(
284 16 : std::chrono::time_point_cast<std::chrono::milliseconds>(start_date)
285 16 : .time_since_epoch())
286 8 : .count()};
287 : const std::int64_t ed{
288 16 : std::chrono::duration_cast<std::chrono::milliseconds>(
289 16 : std::chrono::time_point_cast<std::chrono::milliseconds>(end_date)
290 16 : .time_since_epoch())
291 8 : .count()};
292 8 : std::string query = sqlite3_mprintf(
293 : "SELECT VAL, TIMESTAMP FROM VARIABLE_VALUE WHERE VARIABLE_ID = (SELECT "
294 : "ID FROM VARIABLE WHERE NAME = '%q') AND TIMESTAMP >= %ld AND TIMESTAMP "
295 : "<= %ld;",
296 24 : var_name.c_str(), sd, ed);
297 8 : CountCallback cc{
298 : 0,
299 8 : const_cast<std::function<void(const VarValue &, size_t)> *>(&send_vale)};
300 16 : if (sqlite3_exec(db_, query.c_str(),
301 408 : +[](void *cc, int argc, char **argv, char **azColName) {
302 400 : VarValue val{};
303 600 : for (int i = 0; i < argc; i++) {
304 400 : if (strcmp("VAL", azColName[i]) == 0) {
305 : try {
306 200 : size_t processed = 0;
307 200 : val.set_val(std::stod(argv[i], &processed));
308 0 : } catch (std::invalid_argument e) {
309 0 : std::cerr << e.what();
310 0 : return -1;
311 0 : } catch (std::out_of_range e) {
312 0 : std::cerr << e.what();
313 0 : return -1;
314 : }
315 200 : } else if (strcmp("TIMESTAMP", azColName[i]) == 0) {
316 : try {
317 200 : size_t processed = 0;
318 200 : val.set_timestamp(std::stoull(argv[i], &processed));
319 0 : } catch (std::invalid_argument e) {
320 0 : std::cerr << e.what();
321 0 : return -1;
322 0 : } catch (std::out_of_range e) {
323 0 : std::cerr << e.what();
324 0 : return -1;
325 : }
326 : }
327 : }
328 200 : auto count_callback{static_cast<CountCallback *>(cc)};
329 200 : auto cb{count_callback->send_vale};
330 200 : (*cb)(val, count_callback->index);
331 200 : ++count_callback->index;
332 200 : return 0;
333 208 : },
334 8 : &cc, &err_msg) != SQLITE_OK) {
335 0 : std::cerr << "error " << err_msg << "\n";
336 0 : sqlite3_free(err_msg);
337 0 : return Err::Failed;
338 : }
339 8 : return Err::Ok;
340 : }
341 :
342 8 : IDataSource::Err SQLiteWrapper::count_variable_values(
343 : const std::string &var_name,
344 : const std::chrono::system_clock::time_point &start_date,
345 : const std::chrono::system_clock::time_point &end_date,
346 : const std::function<void(size_t count)> &send_count) noexcept {
347 8 : char *err_msg = nullptr;
348 16 : const std::int64_t sd{std::chrono::duration_cast<std::chrono::milliseconds>(
349 16 : start_date.time_since_epoch())
350 8 : .count()};
351 16 : const std::int64_t ed{std::chrono::duration_cast<std::chrono::milliseconds>(
352 16 : end_date.time_since_epoch())
353 8 : .count()};
354 8 : std::string count_query = sqlite3_mprintf(
355 : "SELECT COUNT(*) FROM VARIABLE_VALUE WHERE VARIABLE_ID = (SELECT ID FROM "
356 : "VARIABLE WHERE NAME = '%q') AND TIMESTAMP >= %ld AND TIMESTAMP <= %ld;",
357 24 : var_name.c_str(), sd, ed);
358 16 : if (sqlite3_exec(
359 : db_, count_query.c_str(),
360 24 : +[](void *callback, int argc, char **argv, char **azColName) {
361 8 : for (int i = 0; i < argc; ++i) {
362 8 : if (strcmp("COUNT(*)", azColName[i]) == 0) {
363 8 : size_t count{};
364 : try {
365 8 : size_t processed = 0;
366 8 : count = std::stoull(argv[i], &processed);
367 0 : } catch (std::invalid_argument e) {
368 0 : std::cerr << e.what();
369 0 : return -1;
370 0 : } catch (std::out_of_range e) {
371 0 : std::cerr << e.what();
372 0 : return -1;
373 : }
374 8 : (*static_cast<std::function<void(size_t count)> *>(callback))(
375 : count);
376 8 : return 0;
377 : }
378 : }
379 0 : return 0;
380 16 : },
381 : const_cast<std::function<void(size_t)> *>(&send_count),
382 8 : &err_msg) != SQLITE_OK) {
383 0 : std::cerr << "error " << err_msg << "\n";
384 0 : sqlite3_free(err_msg);
385 0 : return Err::Failed;
386 : }
387 8 : return Err::Ok;
388 114 : }
|
