Raspberry Pi Weighting Control System
This project serves as a simple weighting control system, that was realized as a Bachelor Thesis
db_driver.cpp
Go to the documentation of this file.
1 #include "db_driver.h"
2 
3 #include <stdio.h>
4 #include <stdlib.h>
5 #include <string.h>
6 #include <time.h>
7 #include <exception>
8 #include <ctime>
9 #include <spdlog/spdlog.h>
10 
11 #define DB_SIZE_64 64 // used for password (blob/ binary) and some strings (varchar in db)
12 #define DB_SIZE_128 128 // used mostly for varchar
13 #define DB_SIZE_2048 2049 // used for description/ large varchar buffers
14 #define MAX_MEDIUM_BLOB 16777216 - 1 // max medium_blob size (2^24 - 1)
15 
16 #define DB_DEFAULT_HOST "localhost"
17 #define DB_DEFAULT_USER "pi"
18 #define DB_DEFAULT_PASSWD "raspberry"
19 #define DB_DEFAULT_DB "control_system"
20 #define DB_DEFAULT_PORT 3306
21 #define DB_DEFAULT_SOCKET NULL
22 #define DB_DEFAULT_CLIENTFLAGS 0
23 
25  MYSQL *conn_tmp = mysql_init(NULL);
26  // db_is_open is true by default
27 
28  if (conn_tmp) {
29  conn.reset(conn_tmp);
30  // FIXME ? Allow default values or enforce config values (config values must be used and correct)
31  if (!mysql_real_connect(conn.get(),
32  !conf->host.empty() ? conf->host.c_str() : DB_DEFAULT_HOST,
33  !conf->user.empty() ? conf->user.c_str() : DB_DEFAULT_USER,
34  !conf->passwd.empty() ? conf->passwd.c_str() : DB_DEFAULT_PASSWD,
35  !conf->db.empty() ? conf->db.c_str() : DB_DEFAULT_DB,
36  conf->port ? conf->port : DB_DEFAULT_PORT,
37  !conf->socket.empty() ? conf->socket.c_str() : DB_DEFAULT_SOCKET,
38  conf->clientflags ? conf->clientflags : DB_DEFAULT_CLIENTFLAGS))
39  {
40  spdlog::error("db_driver.cpp - (conf) constructor error. Failed to connect to database. Err: {0} - {1}",
41  mysql_errno(conn.get()), mysql_error(conn.get()));
42  // mysql_close(conn.get()); // connection was never open, this also causes double free (due to unique_ptr)
43  db_is_open = false;
44  }
45  } else {
46  spdlog::error("db_driver.cpp - (conf) constructor error. Failed to init MYSQL. Returned NULL pointer.");
47  // mysql_close(conn.get());
48  db_is_open = false;
49  }
50 }
51 
52 db_driver::db_driver(const char* host, const char* user, const char* passwd, const char* db,
53  unsigned int port, const char* socket, unsigned long cflags)
54 {
55  MYSQL *conn_tmp = mysql_init(0);
56 
57  if (conn_tmp) {
58  conn.reset(conn_tmp);
59  if (!mysql_real_connect(conn.get(), host, user, passwd, db, port, socket, cflags)) {
60  spdlog::error("db_driver.cpp - (test/debug) constructor error. Failed to connect to database");
61  mysql_close(conn.get());
62  db_is_open = false;
63  }
64  } else {
65  spdlog::error("db_driver.cpp - (test/debug) constructor error. Failed to init MYSQL");
66  mysql_close(conn.get());
67  db_is_open = false;
68  }
69 }
70 
72  return db_is_open;
73 }
74 
75 int db_driver::query_user_credentials(user_cred* creds, const char* uname) {
76  memset(creds->uname, 0, 64);
77  memset(creds->passwd, 0, 64);
78  creds->status = 0;
79 
80  int par_cnt = 1, res_cnt = 3;
81  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
82  unsigned long lengths [res_cnt] = {0};
83  my_bool is_null [res_cnt] = {0};
84  my_bool is_error [res_cnt] = {0};
85 
86  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
87  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
88 
89  std::string sql = "SELECT username, password, status FROM cs_users WHERE username = ?";
90  // spdlog::debug("db_driver.cpp - fnc: query_user_credentials. SQL: {0}", sql.c_str());
91 
92  bind_par[0].buffer_type = MYSQL_TYPE_STRING;
93  // WARNING! - this is potentially dangerous (casting const char* to char*), but input parameter should be written to
94  bind_par[0].buffer = (char*) uname;
95  bind_par[0].buffer_length = strlen(uname);
96 
97  // result data containers
98  uint8_t status = 0;
99  char username[DB_SIZE_64] = {0}, password[DB_SIZE_64] = {0};
100  // uint8_t password[DB_SIZE_64] = {0};
101 
102  // username
103  bind_res[0].buffer_type = MYSQL_TYPE_STRING;
104  bind_res[0].buffer = username;
105  bind_res[0].buffer_length = DB_SIZE_64;
106  bind_res[0].is_null = &is_null[0];
107  bind_res[0].length = &lengths [0];
108  bind_res[0].error = &is_error [0];
109 
110  // password
111  bind_res[1].buffer_type = MYSQL_TYPE_STRING; // DB type is BINARY(64) this should work
112  bind_res[1].buffer = password;
113  bind_res[1].buffer_length = DB_SIZE_64;
114  bind_res[1].is_null = &is_null[1];
115  bind_res[1].length = &lengths [1];
116  bind_res[1].error = &is_error [1];
117 
118  // role
119  bind_res[2].buffer_type = MYSQL_TYPE_TINY; // probably TINYINT ? (-128 to 127)
120  bind_res[2].buffer = &status;
121  bind_res[2].is_null = &is_null[2];
122  bind_res[2].length = &lengths [2];
123  bind_res[2].error = &is_error [2];
124 
125  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
126  if (stmt == NULL) {
127  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to init prepared statement.");
128  return 1;
129  }
130  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
131  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to prepare prepared statement.");
132  this->handle_stmt_error(stmt);
133  return 1;
134  }
135  if (mysql_stmt_bind_param(stmt, bind_par)) {
136  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to bind parameters to prepared statement");
137  this->handle_stmt_error(stmt);
138  return 1;
139  }
140  if (mysql_stmt_bind_result(stmt, bind_res)) {
141  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to bind results to prepared statement");
142  this->handle_stmt_error(stmt);
143  return 1;
144  }
145  if (mysql_stmt_execute(stmt)) {
146  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to execute prepared statement");
147  this->handle_stmt_error(stmt);
148  return 1;
149  }
150  if (mysql_stmt_store_result(stmt)) {
151  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to store result of prepared statement");
152  this->handle_stmt_error(stmt);
153  return 1;
154  }
155  unsigned long long row_cnt = 0;
156  if ((row_cnt = mysql_stmt_num_rows(stmt)) != 1) {
157  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Expected 1 result but got {0}!", row_cnt);
158  mysql_stmt_close(stmt);
159  return 2;
160  }
161 
162  int rv = mysql_stmt_fetch(stmt);
163  if (rv == 1 || rv == MYSQL_NO_DATA) {
164  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to fetch result even"
165  " tought it should exist. (rv: {0})", rv);
166  mysql_stmt_close(stmt);
167  return 1;
168  }
169 
170  bool err_exit = false;
171  if (is_null[0] || is_error[0] || lengths[0] <= 0) { // username
172  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Username mustn't be NULL, ERROR or EMPTY!");
173  err_exit = true;
174  } else {
175  strcpy(creds->uname, username);
176  }
177  if (is_null[1] || is_error[1] || lengths[1] <= 0) { // password
178  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Password mustn't be NULL, ERROR or EMPTY!");
179  err_exit = true;
180  } else {
181  strcpy(creds->passwd, password);
182  }
183  if (is_null[2] || is_error[2] || lengths[2] <= 0) { // status
184  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Role mustn't be NULL, ERROR or EMPTY!");
185  err_exit = true;
186  } else {
187  creds->status = status;
188  }
189 
190  mysql_stmt_close(stmt);
191  if (err_exit) {
192  spdlog::error("db_driver.cpp - fnc: query_user_credentials. Failed to retrieve some critical data.");
193  return 1;
194  }
195 
196  return 0;
197 }
198 
199 int db_driver::query_user_data(user_cont* usr, const char* uname, uint8_t* rfid_serial, uint8_t rfid_ser_len) {
200  if (uname == nullptr && rfid_serial == nullptr) {
201  spdlog::error("db_driver.cpp - fnc: query_user_data. The function must be called with either uname"
202  " or rfid serial number");
203  return 1;
204  }
205 
206  int par_cnt = 1, res_cnt = 13;
207  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
208  unsigned long lengths [res_cnt] = {0};
209  my_bool is_null [res_cnt] = {0};
210  my_bool is_error [res_cnt] = {0};
211 
212  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
213  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
214 
215  std::string sql;
216 
217  // rfid is prefered over uname
218  if (rfid_serial) {
219  sql = "SELECT id, role, username, password, rfid_serial, description, name, lastname, reg_date,"
220  " last_login_date, created_at, date_of_birth, measure_count FROM cs_users WHERE rfid_serial = ?";
221 
222  unsigned long len = rfid_ser_len; // better than casting uint8_t to unsigned long
223  bind_par[0].buffer_type = MYSQL_TYPE_TINY_BLOB;
224  bind_par[0].buffer = rfid_serial;
225  bind_par[0].length = &len;
226  }
227 
228  // both is supplied, ignores uname and uses sql from rfid, however both rfid and uname shouldnt be supplied
229  if (uname && !rfid_serial) {
230  // WARNING! - for wildcard (%) must be added to bind value, not here, but username is expected to be exact match
231  sql = "SELECT id, role, username, password, rfid_serial, description, name, lastname, reg_date,"
232  " last_login_date, created_at, date_of_birth, measure_count FROM cs_users WHERE username = ?";
233 
234  bind_par[0].buffer_type = MYSQL_TYPE_STRING;
235  // WARNING! - this is potentially dangerous (casting cosnt char* to char*), but input parameter should be written to
236  bind_par[0].buffer = (char*) uname;
237  bind_par[0].buffer_length = strlen(uname);
238  }
239 
240  // spdlog::debug("db_driver.cpp - fnc: query_user_data. SQL: {0}", sql.c_str());
241 
242  // result data holders
243  unsigned long id = 0, measure_count = 0; // unsigned?
244  uint8_t role = 0;
245  char username[DB_SIZE_64], description[DB_SIZE_2048], name[DB_SIZE_128], lastname[DB_SIZE_64];
246  uint8_t rfid_ser[DB_SIZE_64], password[DB_SIZE_64]; // rfid_serial is 10B max but is stored in tinyblob (max 255B)
247  MYSQL_TIME reg_date, ll_date, create_date, dob_date;
248 
249  // id
250  bind_res[0].buffer_type = MYSQL_TYPE_LONG;
251  bind_res[0].buffer = &id;
252  bind_res[0].is_null = &is_null[0];
253  bind_res[0].length = &lengths [0];
254  bind_res[0].error = &is_error [0];
255 
256  // role
257  bind_res[1].buffer_type = MYSQL_TYPE_TINY; // probably TINYINT ? (-128 to 127)
258  bind_res[1].buffer = &role;
259  bind_res[1].is_null = &is_null[1];
260  bind_res[1].length = &lengths [1];
261  bind_res[1].error = &is_error [1];
262 
263  // username
264  bind_res[2].buffer_type = MYSQL_TYPE_STRING;
265  bind_res[2].buffer = username;
266  bind_res[2].buffer_length = DB_SIZE_64;
267  bind_res[2].is_null = &is_null[2];
268  bind_res[2].length = &lengths [2];
269  bind_res[2].error = &is_error [2];
270 
271  // password
272  bind_res[3].buffer_type = MYSQL_TYPE_STRING; // DB type is BINARY(64) this should work
273  bind_res[3].buffer = password;
274  bind_res[3].buffer_length = DB_SIZE_64;
275  bind_res[3].is_null = &is_null[3];
276  bind_res[3].length = &lengths [3];
277  bind_res[3].error = &is_error [3];
278 
279  // rfid_serial
280  bind_res[4].buffer_type = MYSQL_TYPE_TINY_BLOB;
281  bind_res[4].buffer = rfid_ser;
282  bind_res[4].buffer_length = DB_SIZE_64;
283  bind_res[4].is_null = &is_null[4];
284  bind_res[4].length = &lengths [4];
285  bind_res[4].error = &is_error [4];
286 
287  // description
288  bind_res[5].buffer_type = MYSQL_TYPE_STRING;
289  bind_res[5].buffer = description;
290  bind_res[5].buffer_length = DB_SIZE_2048;
291  bind_res[5].is_null = &is_null[5];
292  bind_res[5].length = &lengths [5];
293  bind_res[5].error = &is_error [5];
294 
295  // name
296  bind_res[6].buffer_type = MYSQL_TYPE_STRING;
297  bind_res[6].buffer = name;
298  bind_res[6].buffer_length = DB_SIZE_128;
299  bind_res[6].is_null = &is_null[6];
300  bind_res[6].length = &lengths [6];
301  bind_res[6].error = &is_error [6];
302 
303  // lastname
304  bind_res[7].buffer_type = MYSQL_TYPE_STRING;
305  bind_res[7].buffer = lastname;
306  bind_res[7].buffer_length = DB_SIZE_64;
307  bind_res[7].is_null = &is_null[7];
308  bind_res[7].length = &lengths [7];
309  bind_res[7].error = &is_error [7];
310 
311  // reg_date
312  bind_res[8].buffer_type = MYSQL_TYPE_TIMESTAMP;
313  bind_res[8].buffer = &reg_date;
314  bind_res[8].is_null = &is_null[8];
315  bind_res[8].length = &lengths [8];
316  bind_res[8].error = &is_error [8];
317 
318  //last_login_date
319  bind_res[9].buffer_type = MYSQL_TYPE_TIMESTAMP;
320  bind_res[9].buffer = &ll_date;
321  bind_res[9].is_null = &is_null[9];
322  bind_res[9].length = &lengths [9];
323  bind_res[9].error = &is_error [9];
324 
325  //created_at
326  bind_res[10].buffer_type = MYSQL_TYPE_TIMESTAMP;
327  bind_res[10].buffer = &create_date;
328  bind_res[10].is_null = &is_null[10];
329  bind_res[10].length = &lengths [10];
330  bind_res[10].error = &is_error [10];
331 
332  //date_of_birth
333  bind_res[11].buffer_type = MYSQL_TYPE_DATE;
334  bind_res[11].buffer = &dob_date;
335  bind_res[11].is_null = &is_null[11];
336  bind_res[11].length = &lengths [11];
337  bind_res[11].error = &is_error [11];
338 
339  // measure_count
340  bind_res[12].buffer_type = MYSQL_TYPE_LONG;
341  bind_res[12].buffer = &measure_count;
342  bind_res[12].is_null = &is_null[12];
343  bind_res[12].length = &lengths [12];
344  bind_res[12].error = &is_error [12];
345 
346 
347  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
348  if (stmt == NULL) {
349  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to init prepared statement.");
350  return 1;
351  }
352  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
353  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to prepare prepared statement.");
354  this->handle_stmt_error(stmt);
355  return 1;
356  }
357  if (mysql_stmt_bind_param(stmt, bind_par)) {
358  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to bind parameters to prepared statement");
359  this->handle_stmt_error(stmt);
360  return 1;
361  }
362  if (mysql_stmt_bind_result(stmt, bind_res)) {
363  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to bind results to prepared statement");
364  this->handle_stmt_error(stmt);
365  return 1;
366  }
367  if (mysql_stmt_execute(stmt)) {
368  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to execute prepared statement");
369  this->handle_stmt_error(stmt);
370  return 1;
371  }
372  if (mysql_stmt_store_result(stmt)) {
373  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to store result of prepared statement");
374  this->handle_stmt_error(stmt);
375  return 1;
376  }
377  unsigned long long row_cnt = 0;
378  if ((row_cnt = mysql_stmt_num_rows(stmt)) != 1) {
379  spdlog::error("db_driver.cpp - fnc: query_user_data. Expected 1 result but got {0}!", row_cnt);
380  mysql_stmt_close(stmt);
381  return 1;
382  }
383 
384  int rv = mysql_stmt_fetch(stmt);
385  if (rv == 1 || rv == MYSQL_NO_DATA) {
386  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to fetch result even"
387  " tought it should exist. (rv: {0})", rv);
388  mysql_stmt_close(stmt);
389  return 1;
390  }
391 
392 // sql = "SELECT id, role, username, password, rfid_serial, description, name, lastname, reg_date,"
393  // " last_login_date, created_at, date_of_birth FROM cs_users WHERE rfid_serial = ?";
394 
395  bool err_exit = false;
396  if (is_null[0] || is_error[0]) { // id
397  spdlog::error("db_driver.cpp - fnc: query_user_data. ID mustn't be NULL or ERROR!");
398  err_exit = true;
399  } else {
400  usr->id = id;
401  }
402  if (is_null[1] || is_error[1]) { // role
403  spdlog::error("db_driver.cpp - fnc: query_user_data. Role mustn't be NULL or ERROR!");
404  err_exit = true;
405  } else {
406  usr->role = role;
407  }
408  if (is_null[2] || is_error[2] || lengths[2] <= 0) { //username
409  spdlog::error("db_driver.cpp - fnc: query_user_data. Username mustn't be NULL, ERROR or EMPTY!");
410  err_exit = true;
411  } else {
412  usr->username = username;
413  }
414  if (is_null[3] || is_error[3]) { // password (not doing any action for password)
415  spdlog::debug("db_driver.cpp - fnc: query_user_data. Failed to read password");
416  err_exit = true;
417  }
418  if (is_error[4]) { // rfid_serial
419  spdlog::error("db_driver.cpp - fnc: query_user_data. RFID is in error.");
420  err_exit = true;
421  } else {
422  if (is_null[4] || lengths[4] <= 0) {
423  spdlog::debug("db_driver.cpp - fnc: query_user_data. RFID is NULL or EMPTY");
424  memset(usr->rfid_serial_bin, 0, sizeof(uint8_t) * 10);
425  usr->rfid_ser_len = 0;
426  } else {
427  if (lengths[4] <= 10) {
428  memcpy(usr->rfid_serial_bin, rfid_ser, lengths[4]);
429  usr->rfid_ser_len = lengths[4];
430  } else {
431  spdlog::error("db_driver.cpp - fnc: query_user_data."
432  " RFID is longer than 10 Bytes which should be impossible.");
433  }
434  }
435  }
436  if (is_null[5] || is_error[5]) { // description
437  spdlog::debug("db_driver.cpp - fnc: query_user_data. Description is empty or error. Not doing anything");
438  // usr->description = std::string();
439  } else {
440  usr->description = description;
441  }
442  if (is_null[6] || is_error[6] || lengths[6] <= 0) { // name
443  spdlog::error("db_driver.cpp - fnc: query_user_data. Name mustn't be NULL, ERROR or EMPTY!");
444  err_exit = true;
445  } else {
446  usr->name = name;
447  }
448  if (is_null[7] || is_error[7] || lengths[7] <= 0) { // lastname
449  spdlog::error("db_driver.cpp - fnc: query_user_data. Lastname mustn't be NULL, ERROR or EMPTY!");
450  err_exit = true;
451  } else {
452  usr->lastname = name;
453  }
454  if (is_null[8] || is_error[8]) { // reg_date
455  spdlog::error("db_driver.cpp - fnc: query_user_data. Reg_date mustn't be NULL or ERROR!");
456  err_exit = true;
457  } else {
458  mysql_time_to_tm(&reg_date, &(usr->reg_date));
459  }
460  if (is_error[9]) { // last_login_date
461  spdlog::error("db_driver.cpp - fnc: query_user_data. Last_login_date mustn't be ERROR!");
462  err_exit = true;
463  } else {
464  if (is_null[9]) {
465  spdlog::debug("db_driver.cpp - fnc: query_user_data. Last_login_date is NULL!");
466  } else {
467  mysql_time_to_tm(&ll_date, &(usr->last_login_date));
468  }
469  }
470  if (is_null[10] || is_error[10]) { // created_at
471  spdlog::error("db_driver.cpp - fnc: query_user_data. Created_at date mustn't be NULL or ERROR!");
472  err_exit = true;
473  } else {
474  mysql_time_to_tm(&create_date, &(usr->created_at_date));
475  }
476  if (is_null[11] || is_error[11]) { // date_of_birth
477  spdlog::error("db_driver.cpp - fnc: query_user_data. Date_of_birth mustn't be NULL or ERROR!");
478  err_exit = true;
479  } else {
480  mysql_time_to_tm(&dob_date, &(usr->date_of_birth));
481  }
482  if (is_null[12] || is_error[12]) { // measure_count
483  spdlog::error("db_driver.cpp - fnc: query_user_data. Measure_count mustn't be NULL or ERROR!");
484  err_exit = true;
485  } else {
486  usr->measure_count = measure_count;
487  }
488 
489  mysql_stmt_close(stmt);
490  if (err_exit) {
491  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to retrieve some critical data.");
492  return 1;
493  }
494 
495  return 0;
496 }
497 
498 int db_driver::query_username(std::string& uname, unsigned long id) {
499  int par_cnt = 1, res_cnt = 1;
500  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
501  unsigned long lengths [res_cnt] = {0};
502  my_bool is_null [res_cnt] = {0};
503  my_bool is_error [res_cnt] = {0};
504 
505  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
506  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
507 
508  std::string sql = "SELECT username FROM cs_users WHERE id = ?";
509 
510  // user id param
511  bind_par[0].buffer_type = MYSQL_TYPE_LONG;
512  bind_par[0].buffer = &id;
513 
514  char name_buf[DB_SIZE_64] = {0};
515 
516  // role
517  bind_res[0].buffer_type = MYSQL_TYPE_STRING; // probably TINYINT ? (-128 to 127)
518  bind_res[0].buffer = &name_buf;
519  bind_res[0].buffer_length = DB_SIZE_64;
520  bind_res[0].is_null = &is_null[0];
521  bind_res[0].length = &lengths [0];
522  bind_res[0].error = &is_error [0];
523 
524  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
525  if (stmt == NULL) {
526  spdlog::error("db_driver.cpp - fnc: query_username. Failed to init prepared statement.");
527  return 1;
528  }
529  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
530  spdlog::error("db_driver.cpp - fnc: query_username. Failed to prepare prepared statement.");
531  this->handle_stmt_error(stmt);
532  return 1;
533  }
534  if (mysql_stmt_bind_param(stmt, bind_par)) {
535  spdlog::error("db_driver.cpp - fnc: query_username. Failed to bind parameters to prepared statement");
536  this->handle_stmt_error(stmt);
537  return 1;
538  }
539  if (mysql_stmt_bind_result(stmt, bind_res)) {
540  spdlog::error("db_driver.cpp - fnc: query_username. Failed to bind results to prepared statement");
541  this->handle_stmt_error(stmt);
542  return 1;
543  }
544  if (mysql_stmt_execute(stmt)) {
545  spdlog::error("db_driver.cpp - fnc: query_username. Failed to execute prepared statement");
546  this->handle_stmt_error(stmt);
547  return 1;
548  }
549  if (mysql_stmt_store_result(stmt)) {
550  spdlog::error("db_driver.cpp - fnc: query_username. Failed to store result of prepared statement");
551  this->handle_stmt_error(stmt);
552  return 1;
553  }
554  unsigned long long row_cnt = 0;
555  if ((row_cnt = mysql_stmt_num_rows(stmt)) != 1) {
556  spdlog::error("db_driver.cpp - fnc: query_username. Got {0} results! Expected 1.", row_cnt);
557  mysql_stmt_close(stmt);
558  return 2;
559  }
560 
561  int rv = mysql_stmt_fetch(stmt);
562  if (rv == 1 || rv == MYSQL_NO_DATA) {
563  spdlog::error("db_driver.cpp - fnc: query_username. Failed to fetch result even"
564  " tought it should exist. (rv: {0})", rv);
565  return 1;
566  }
567 
568  if (is_null[0] || is_error[0]) {
569  spdlog::error("db_driver.cpp - fnc: query_username. Username mustn't be NULL or ERROR!");
570  mysql_stmt_close(stmt);
571  return 1;
572  } else {
573  uname = name_buf;
574  }
575 
576  mysql_stmt_close(stmt);
577  return 0;
578 }
579 
581  int par_cnt = 1, res_cnt = 4;
582  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
583  unsigned long lengths [res_cnt] = {0};
584  my_bool is_null [res_cnt] = {0};
585  my_bool is_error [res_cnt] = {0};
586 
587  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
588  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
589 
590  // if measurement was continuous, only part 1 is selected for displayed
591  std::string sql = "SELECT id, measurement_number, measuring_start, measuree_id FROM"
592  " cs_measurements WHERE measuree_id = ? AND status = 1 AND measurement_part = 1"
593  " ORDER BY created_at DESC";
594 
595  // user id param
596  bind_par[0].buffer_type = MYSQL_TYPE_LONG;
597  bind_par[0].buffer = (void*) &(usr->id);
598 
599  // result data containers
600  unsigned long id = 0, measurement_number = 0, measuree_id = 0;
601  MYSQL_TIME measuring_start;
602 
603  // id
604  bind_res[0].buffer_type = MYSQL_TYPE_LONG;
605  bind_res[0].buffer = &id;
606  bind_res[0].is_null = &is_null[0];
607  bind_res[0].length = &lengths [0];
608  bind_res[0].error = &is_error [0];
609 
610  // measurement_number
611  bind_res[1].buffer_type = MYSQL_TYPE_LONG;
612  bind_res[1].buffer = &measurement_number;
613  bind_res[1].is_null = &is_null[1];
614  bind_res[1].length = &lengths [1];
615  bind_res[1].error = &is_error [1];
616 
617  // measuring_start
618  bind_res[2].buffer_type = MYSQL_TYPE_TIMESTAMP;
619  bind_res[2].buffer = &measuring_start;
620  bind_res[2].is_null = &is_null[2];
621  bind_res[2].length = &lengths [2];
622  bind_res[2].error = &is_error [2];
623 
624  // measuree_id
625  bind_res[3].buffer_type = MYSQL_TYPE_LONG;
626  bind_res[3].buffer = &measuree_id;
627  bind_res[3].is_null = &is_null[3];
628  bind_res[3].length = &lengths [3];
629  bind_res[3].error = &is_error [3];
630 
631  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
632  if (stmt == NULL) {
633  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Failed to init prepared statement.");
634  return 1;
635  }
636  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
637  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Failed to prepare prepared statement.");
638  this->handle_stmt_error(stmt);
639  return 1;
640  }
641  if (mysql_stmt_bind_param(stmt, bind_par)) {
642  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Failed to bind parameters to prepared statement");
643  this->handle_stmt_error(stmt);
644  return 1;
645  }
646  if (mysql_stmt_bind_result(stmt, bind_res)) {
647  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Failed to bind results to prepared statement");
648  this->handle_stmt_error(stmt);
649  return 1;
650  }
651  if (mysql_stmt_execute(stmt)) {
652  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Failed to execute prepared statement");
653  this->handle_stmt_error(stmt);
654  return 1;
655  }
656  if (mysql_stmt_store_result(stmt)) {
657  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Failed to store result of prepared statement");
658  this->handle_stmt_error(stmt);
659  return 1;
660  }
661  //can be used to get header = measurement count
662  unsigned long long row_cnt = 0;
663  if ((row_cnt = mysql_stmt_num_rows(stmt)) <= 0) {
664  spdlog::info("db_driver.cpp - fnc: query_measurement_headers. No rows fetched from stored from DB."
665  " Assuming that user has no measurements yet.");
666  mysql_stmt_close(stmt);
667  return 0;
668  } else {
669  spdlog::info("db_driver.cpp - fnc: query_measurement_headers. Fetched {0} headers.", row_cnt);
670  }
671 
672  usr->measur_headers.clear();
673  bool err_exit = false;
674  while (1) {
675 
676  int rv = mysql_stmt_fetch(stmt);
677  if (rv == 1 || rv == MYSQL_NO_DATA) {
678  break;
679  }
680 
681  measurement_header *h = (measurement_header*) calloc(1, sizeof(measurement_header));
682 
683  if (is_null[0] || is_error[0]) { // id
684  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Measurement ID mustn't be NULL or ERROR!");
685  err_exit = true;
686  } else {
687  h->id = id;
688  }
689  if (is_null[1] || is_error[1]) { // measurement_number
690  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Measuring number mustn't be NULL or ERROR!");
691  err_exit = true;
692  } else {
693  h->measurement_number = measurement_number;
694  }
695  if (is_null[2] || is_error[2]) { // measuring start
696  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Measuring start mustn't be NULL or ERROR!");
697  err_exit = true;
698  } else {
699  mysql_time_to_tm(&measuring_start, &(h->measuring_start));
700  }
701  if (is_null[3] || is_error[3]) { // measuree_id
702  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Measuree_id mustn't be NULL or ERROR!");
703  err_exit = true;
704  } else {
705  h->measuree_id = measuree_id;
706  }
707 
708  usr->measur_headers.push_back(std::unique_ptr<measurement_header>(h));
709  }
710 
711  mysql_stmt_close(stmt);
712  if (err_exit) {
713  spdlog::error("db_driver.cpp - fnc: query_measurement_headers. Failed to retrieve some critical data.");
714  return 1;
715  }
716 
717  return 0;
718 }
719 
720 int db_driver::query_measurement(measurement* m, unsigned long p_id) {
721  int par_cnt = 1, res_cnt = 17;
722  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
723  unsigned long lengths [res_cnt] = {0};
724  my_bool is_null [res_cnt] = {0};
725  my_bool is_error [res_cnt] = {0};
726 
727  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
728  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
729 
730  std::string sql = "SELECT id, measurement_number, measuree_id, measurer_id, measuring_length,"
731  " measuring_start, measuring_end, description, value_length, value_count, m_values, x_val_median,"
732  " x_val_avg, all_val_median, all_val_avg, created_at, measurement_part"
733  " FROM cs_measurements WHERE id = ?";
734 
735  // user id param
736  bind_par[0].buffer_type = MYSQL_TYPE_LONG;
737  bind_par[0].buffer = (void*) &p_id;
738 
739  unsigned long id = 0, measurement_number = 0, measuree_id = 0, measurer_id = 0, measuring_length = 0;
740  unsigned long value_count = 0, measurement_part = 0;
741  char description[DB_SIZE_2048] = {0};
742  uint8_t value_length = 0;
743  MYSQL_TIME measuring_start, measuring_end, created_at;
744  double *m_values = (double*) malloc(MAX_MEDIUM_BLOB); // using malloc to save a few operations
745  double x_val_med = 0.0, x_val_avg = 0.0, all_val_med = 0.0, all_val_avg = 0.0;
746 
747  if (m_values == NULL) {
748  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to allocated buffer for values");
749  return 1;
750  } else {
751  memset(m_values, 0, MAX_MEDIUM_BLOB);
752  }
753 
754  // id
755  bind_res[0].buffer_type = MYSQL_TYPE_LONG;
756  bind_res[0].buffer = &id;
757  bind_res[0].is_null = &is_null[0];
758  bind_res[0].length = &lengths [0];
759  bind_res[0].error = &is_error [0];
760 
761  // measurement_number
762  bind_res[1].buffer_type = MYSQL_TYPE_LONG;
763  bind_res[1].buffer = &measurement_number;
764  bind_res[1].is_null = &is_null[1];
765  bind_res[1].length = &lengths [1];
766  bind_res[1].error = &is_error [1];
767 
768  // measuree_id
769  bind_res[2].buffer_type = MYSQL_TYPE_LONG;
770  bind_res[2].buffer = &measuree_id;
771  bind_res[2].is_null = &is_null[2];
772  bind_res[2].length = &lengths [2];
773  bind_res[2].error = &is_error [2];
774 
775  // measurer_id
776  bind_res[3].buffer_type = MYSQL_TYPE_LONG;
777  bind_res[3].buffer = &measurer_id;
778  bind_res[3].is_null = &is_null[3];
779  bind_res[3].length = &lengths [3];
780  bind_res[3].error = &is_error [3];
781 
782  // measuring_length
783  bind_res[4].buffer_type = MYSQL_TYPE_LONG;
784  bind_res[4].buffer = &measuring_length;
785  bind_res[4].is_null = &is_null[4];
786  bind_res[4].length = &lengths [4];
787  bind_res[4].error = &is_error [4];
788 
789  // measuring_start
790  bind_res[5].buffer_type = MYSQL_TYPE_TIMESTAMP;
791  bind_res[5].buffer = &measuring_start;
792  bind_res[5].is_null = &is_null[5];
793  bind_res[5].length = &lengths [5];
794  bind_res[5].error = &is_error [5];
795 
796  // measuring end
797  bind_res[6].buffer_type = MYSQL_TYPE_TIMESTAMP;
798  bind_res[6].buffer = &measuring_end;
799  bind_res[6].is_null = &is_null[6];
800  bind_res[6].length = &lengths [6];
801  bind_res[6].error = &is_error [6];
802 
803  // description
804  bind_res[7].buffer_type = MYSQL_TYPE_STRING;
805  bind_res[7].buffer = description;
806  bind_res[7].buffer_length = DB_SIZE_2048;
807  bind_res[7].is_null = &is_null[7];
808  bind_res[7].length = &lengths [7];
809  bind_res[7].error = &is_error [7];
810 
811  // value_length
812  bind_res[8].buffer_type = MYSQL_TYPE_TINY; // probably TINYINT ? (-128 to 127)
813  bind_res[8].buffer = &value_length;
814  bind_res[8].is_null = &is_null[8];
815  bind_res[8].length = &lengths [8];
816  bind_res[8].error = &is_error [8];
817 
818  // value_count
819  bind_res[9].buffer_type = MYSQL_TYPE_LONG;
820  bind_res[9].buffer = &value_count;
821  bind_res[9].is_null = &is_null[9];
822  bind_res[9].length = &lengths [9];
823  bind_res[9].error = &is_error [9];
824 
825  // m_values
826  bind_res[10].buffer_type = MYSQL_TYPE_MEDIUM_BLOB;
827  bind_res[10].buffer = m_values;
828  bind_res[10].buffer_length = MAX_MEDIUM_BLOB;
829  bind_res[10].is_null = &is_null[10];
830  bind_res[10].length = &lengths [10];
831  bind_res[10].error = &is_error [10];
832 
833  // x_val_median
834  bind_res[11].buffer_type = MYSQL_TYPE_DOUBLE;
835  bind_res[11].buffer = &x_val_med;
836  bind_res[11].is_null = &is_null[11];
837  bind_res[11].length = &lengths [11];
838  bind_res[11].error = &is_error [11];
839 
840  // x_val_avg
841  bind_res[12].buffer_type = MYSQL_TYPE_DOUBLE;
842  bind_res[12].buffer = &x_val_avg;
843  bind_res[12].is_null = &is_null[12];
844  bind_res[12].length = &lengths [12];
845  bind_res[12].error = &is_error [12];
846 
847  // all_val_med
848  bind_res[13].buffer_type = MYSQL_TYPE_DOUBLE;
849  bind_res[13].buffer = &all_val_med;
850  bind_res[13].is_null = &is_null[13];
851  bind_res[13].length = &lengths [13];
852  bind_res[13].error = &is_error [13];
853 
854  // all_val_avg
855  bind_res[14].buffer_type = MYSQL_TYPE_DOUBLE;
856  bind_res[14].buffer = &all_val_avg;
857  bind_res[14].is_null = &is_null[14];
858  bind_res[14].length = &lengths [14];
859  bind_res[14].error = &is_error [14];
860 
861  // created at
862  bind_res[15].buffer_type = MYSQL_TYPE_TIMESTAMP;
863  bind_res[15].buffer = &created_at;
864  bind_res[15].is_null = &is_null[15];
865  bind_res[15].length = &lengths [15];
866  bind_res[15].error = &is_error [15];
867 
868  // measurement_part
869  bind_res[16].buffer_type = MYSQL_TYPE_LONG;
870  bind_res[16].buffer = &measurement_part;
871  bind_res[16].is_null = &is_null[16];
872  bind_res[16].length = &lengths [16];
873  bind_res[16].error = &is_error [16];
874 
875 
876  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
877  if (stmt == NULL) {
878  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to init prepared statement.");
879  free(m_values);
880  return 1;
881  }
882  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
883  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to prepare prepared statement.");
884  this->handle_stmt_error(stmt);
885  free(m_values);
886  return 1;
887  }
888  if (mysql_stmt_bind_param(stmt, bind_par)) {
889  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to bind parameters to prepared statement");
890  this->handle_stmt_error(stmt);
891  free(m_values);
892  return 1;
893  }
894  if (mysql_stmt_bind_result(stmt, bind_res)) {
895  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to bind results to prepared statement");
896  this->handle_stmt_error(stmt);
897  free(m_values);
898  return 1;
899  }
900  if (mysql_stmt_execute(stmt)) {
901  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to execute prepared statement");
902  this->handle_stmt_error(stmt);
903  free(m_values);
904  return 1;
905  }
906  if (mysql_stmt_store_result(stmt)) {
907  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to store result of prepared statement");
908  this->handle_stmt_error(stmt);
909  free(m_values);
910  return 1;
911  }
912  unsigned long long row_cnt = 0;
913  if ((row_cnt = mysql_stmt_num_rows(stmt)) != 1) {
914  spdlog::error("db_driver.cpp - fnc: query_measurement. Expected 1 result but got {0}!", row_cnt);
915  mysql_stmt_close(stmt);
916  free(m_values);
917  return 2;
918  }
919 
920  int rv = mysql_stmt_fetch(stmt);
921  if (rv == 1 || rv == MYSQL_NO_DATA) {
922  spdlog::error("db_driver.cpp - fnc: query_measurement. Failed to fetch result even"
923  " tought it should exist. (rv: {0})", rv);
924  mysql_stmt_close(stmt);
925  free(m_values);
926  return 1;
927  }
928 
929  bool err_exit = false;
930  if (is_null[0] || is_error[0]) { // id
931  spdlog::error("db_driver.cpp - fnc: query_user_data. ID mustn't be NULL or ERROR!");
932  err_exit = true;
933  } else {
934  m->id = id;
935  }
936  if (is_null[1] || is_error[1]) { // measurement_number
937  spdlog::error("db_driver.cpp - fnc: query_user_data. measurement_number mustn't be NULL or ERROR!");
938  err_exit = true;
939  } else {
940  m->measurement_number = measurement_number;
941  }
942  if (is_null[2] || is_error[2]) { // measuree_id
943  spdlog::error("db_driver.cpp - fnc: query_user_data. measuree_id mustn't be NULL or ERROR!");
944  err_exit = true;
945  } else {
946  m->measuree_id = measuree_id;
947  }
948  if (is_null[3] || is_error[3]) { // measurer_id
949  spdlog::error("db_driver.cpp - fnc: query_user_data. measurer_id mustn't be NULL or ERROR!");
950  err_exit = true;
951  } else {
952  m->measurer_id = measurer_id;
953  }
954  if (is_null[4] || is_error[4]) { // measuring_length
955  spdlog::debug("db_driver.cpp - fnc: query_user_data. measuring_length is NULL or ERROR!");
956  } else {
957  m->measuring_length = measuring_length;
958  }
959  if (is_null[5] || is_error[5]) { // measuring_start
960  spdlog::error("db_driver.cpp - fnc: query_user_data. measuring_start mustn't be NULL or ERROR!");
961  err_exit = true;
962  } else {
963  mysql_time_to_tm(&measuring_start, &(m->measuring_start));
964  }
965  if (is_null[6] || is_error[6]) { // measuring_end
966  spdlog::error("db_driver.cpp - fnc: query_user_data. measuring_end mustn't be NULL or ERROR!");
967  err_exit = true;
968  } else {
969  mysql_time_to_tm(&measuring_end, &(m->measuring_end));
970  }
971  if (is_null[7] || is_error[7]) { // description
972  spdlog::debug("db_driver.cpp - fnc: query_user_data. description is NULL or ERROR");
973  m->description = "";
974  } else {
975  m->description = description;
976  }
977 
978  // currently values are in double
979  m->value_length = sizeof(double);
980 
981  // if (is_null[8] || is_error[8]) { // value_length
982  // spdlog::debug("db_driver.cpp - fnc: query_user_data. value_length is NULL or ERROR!");
983  // m->value_length = sizeof(double);
984  // } else {
985  // if (value_length <= 0) {
986  // // value_length must be positive number
987  // m->value_length = sizeof(double);
988  // } else {
989  // m->value_length = value_length;
990  // }
991  // }
992  if (is_null[9] || is_error[9]) { // value_count
993  spdlog::debug("db_driver.cpp - fnc: query_user_data. value_count is NULL or ERROR!");
994  m->value_count = lengths[10] / m->value_length; // lengths[10] is m_value length (length of bin data)
995  } else {
996  m->value_count = value_count;
997  }
998  if (is_null[10] || is_error[10]) { // m_values
999  spdlog::info("db_driver.cpp - fnc: query_user_data. m_values is NULL or ERROR!"
1000  " This is accaptable, but shouldn't occur");
1001  } else {
1002  // spdlog::debug("values length: {0}", lengths[10]);
1003  // m->value_length is init sooner in this function. (If not read from db should be size of double), lengths is overall Byte size
1004  unsigned long val_cnt = lengths[10] / m->value_length;
1005  for (unsigned long i = 0; i < val_cnt; i++) {
1006  m->values.push_back(m_values[i]);
1007  }
1008  }
1009  if (is_null[11] || is_error[11]) { // x_val_median
1010  spdlog::error("db_driver.cpp - fnc: query_user_data. x_val_median mustn't be NULL or ERROR!");
1011  err_exit = true;
1012  } else {
1013  m->x_val_med = x_val_med;
1014  }
1015  if (is_null[12] || is_error[12]) { // x_val_avg
1016  spdlog::error("db_driver.cpp - fnc: query_user_data. x_val_avg mustn't be NULL or ERROR!");
1017  err_exit = true;
1018  } else {
1019  m->x_val_avg = x_val_avg;
1020  }
1021  if (is_null[13] || is_error[13]) { // all_val_median
1022  spdlog::error("db_driver.cpp - fnc: query_user_data. all_val_median mustn't be NULL or ERROR!");
1023  err_exit = true;
1024  } else {
1025  m->all_val_med = all_val_med;
1026  }
1027  if (is_null[14] || is_error[14]) { // all_val_avg
1028  spdlog::error("db_driver.cpp - fnc: query_user_data. all_val_avg mustn't be NULL or ERROR!");
1029  err_exit = true;
1030  } else {
1031  m->all_val_avg = all_val_avg;
1032  }
1033  if (is_null[15] || is_error[15]) { // created_at
1034  spdlog::debug("db_driver.cpp - fnc: query_user_data. measuring_end is NULL or ERROR!");
1035  } else {
1036  mysql_time_to_tm(&created_at, &(m->created_at));
1037  }
1038  if (is_null[16] || is_error[16]) { // measurement_part
1039  spdlog::error("db_driver.cpp - fnc: query_user_data. measurement_part mustn't be NULL or ERROR!");
1040  err_exit = true;
1041  } else {
1042  m->measurement_part = measurement_part;
1043  }
1044 
1045  free(m_values);
1046  if (err_exit) {
1047  spdlog::error("db_driver.cpp - fnc: query_user_data. Failed to retrieve some critical data.");
1048  return 1;
1049  }
1050 
1051  return 0;
1052 }
1053 
1054 int db_driver::query_continuous_measurement(std::vector<measurement> &ms, unsigned long m_number,
1055  unsigned long p_measuree_id)
1056 {
1057  int par_cnt = 2, res_cnt = 13;
1058  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
1059  unsigned long lengths [res_cnt] = {0};
1060  my_bool is_null [res_cnt] = {0};
1061  my_bool is_error [res_cnt] = {0};
1062 
1063  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1064  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
1065 
1066  std::string sql = "SELECT id, measurement_number, measuree_id, measurer_id, measuring_length,"
1067  " measuring_start, measuring_end, value_count, x_val_median,"
1068  " x_val_avg, all_val_median, all_val_avg, measurement_part"
1069  " FROM cs_measurements WHERE measurement_number = ? AND measuree_id = ?";
1070 
1071  // user id param
1072  bind_par[0].buffer_type = MYSQL_TYPE_LONG;
1073  bind_par[0].buffer = (void*) &m_number;
1074 
1075  bind_par[1].buffer_type = MYSQL_TYPE_LONG;
1076  bind_par[1].buffer = (void*) &p_measuree_id;
1077 
1078  unsigned long id = 0, measurement_number = 0, measuree_id = 0, measurer_id = 0, measuring_length = 0;
1079  unsigned long value_count = 0, measurement_part = 0;
1080  MYSQL_TIME measuring_start, measuring_end;
1081  double x_val_med = 0.0, x_val_avg = 0.0, all_val_med = 0.0, all_val_avg = 0.0;
1082 
1083  // id
1084  bind_res[0].buffer_type = MYSQL_TYPE_LONG;
1085  bind_res[0].buffer = &id;
1086  bind_res[0].is_null = &is_null[0];
1087  bind_res[0].length = &lengths [0];
1088  bind_res[0].error = &is_error [0];
1089 
1090  // measurement_number
1091  bind_res[1].buffer_type = MYSQL_TYPE_LONG;
1092  bind_res[1].buffer = &measurement_number;
1093  bind_res[1].is_null = &is_null[1];
1094  bind_res[1].length = &lengths [1];
1095  bind_res[1].error = &is_error [1];
1096 
1097  // measuree_id
1098  bind_res[2].buffer_type = MYSQL_TYPE_LONG;
1099  bind_res[2].buffer = &measuree_id;
1100  bind_res[2].is_null = &is_null[2];
1101  bind_res[2].length = &lengths [2];
1102  bind_res[2].error = &is_error [2];
1103 
1104  // measurer_id
1105  bind_res[3].buffer_type = MYSQL_TYPE_LONG;
1106  bind_res[3].buffer = &measurer_id;
1107  bind_res[3].is_null = &is_null[3];
1108  bind_res[3].length = &lengths [3];
1109  bind_res[3].error = &is_error [3];
1110 
1111  // measuring_length
1112  bind_res[4].buffer_type = MYSQL_TYPE_LONG;
1113  bind_res[4].buffer = &measuring_length;
1114  bind_res[4].is_null = &is_null[4];
1115  bind_res[4].length = &lengths [4];
1116  bind_res[4].error = &is_error [4];
1117 
1118  // measuring_start
1119  bind_res[5].buffer_type = MYSQL_TYPE_TIMESTAMP;
1120  bind_res[5].buffer = &measuring_start;
1121  bind_res[5].is_null = &is_null[5];
1122  bind_res[5].length = &lengths [5];
1123  bind_res[5].error = &is_error [5];
1124 
1125  // measuring end
1126  bind_res[6].buffer_type = MYSQL_TYPE_TIMESTAMP;
1127  bind_res[6].buffer = &measuring_end;
1128  bind_res[6].is_null = &is_null[6];
1129  bind_res[6].length = &lengths [6];
1130  bind_res[6].error = &is_error [6];
1131 
1132  // value_count
1133  bind_res[7].buffer_type = MYSQL_TYPE_LONG;
1134  bind_res[7].buffer = &value_count;
1135  bind_res[7].is_null = &is_null[7];
1136  bind_res[7].length = &lengths [7];
1137  bind_res[7].error = &is_error [7];
1138 
1139  // x_val_median
1140  bind_res[8].buffer_type = MYSQL_TYPE_DOUBLE;
1141  bind_res[8].buffer = &x_val_med;
1142  bind_res[8].is_null = &is_null[8];
1143  bind_res[8].length = &lengths [8];
1144  bind_res[8].error = &is_error [8];
1145 
1146  // x_val_avg
1147  bind_res[9].buffer_type = MYSQL_TYPE_DOUBLE;
1148  bind_res[9].buffer = &x_val_avg;
1149  bind_res[9].is_null = &is_null[9];
1150  bind_res[9].length = &lengths [9];
1151  bind_res[9].error = &is_error [9];
1152 
1153  // all_val_med
1154  bind_res[10].buffer_type = MYSQL_TYPE_DOUBLE;
1155  bind_res[10].buffer = &all_val_med;
1156  bind_res[10].is_null = &is_null[10];
1157  bind_res[10].length = &lengths [10];
1158  bind_res[10].error = &is_error [10];
1159 
1160  // all_val_avg
1161  bind_res[11].buffer_type = MYSQL_TYPE_DOUBLE;
1162  bind_res[11].buffer = &all_val_avg;
1163  bind_res[11].is_null = &is_null[11];
1164  bind_res[11].length = &lengths [11];
1165  bind_res[11].error = &is_error [11];
1166 
1167  // measurement_part
1168  bind_res[12].buffer_type = MYSQL_TYPE_LONG;
1169  bind_res[12].buffer = &measurement_part;
1170  bind_res[12].is_null = &is_null[12];
1171  bind_res[12].length = &lengths [12];
1172  bind_res[12].error = &is_error [12];
1173 
1174 
1175  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1176  if (stmt == NULL) {
1177  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to init prepared statement.");
1178  return 1;
1179  }
1180  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1181  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to prepare prepared statement.");
1182  this->handle_stmt_error(stmt);
1183  return 1;
1184  }
1185  if (mysql_stmt_bind_param(stmt, bind_par)) {
1186  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement."
1187  " Failed to bind parameters to prepared statement");
1188  this->handle_stmt_error(stmt);
1189  return 1;
1190  }
1191  if (mysql_stmt_bind_result(stmt, bind_res)) {
1192  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to bind results to prepared statement");
1193  this->handle_stmt_error(stmt);
1194  return 1;
1195  }
1196  if (mysql_stmt_execute(stmt)) {
1197  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to execute prepared statement");
1198  this->handle_stmt_error(stmt);
1199  return 1;
1200  }
1201  if (mysql_stmt_store_result(stmt)) {
1202  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to store result of prepared statement");
1203  this->handle_stmt_error(stmt);
1204  return 1;
1205  }
1206 
1207  bool err_exit = false;
1208 
1209  while (1) {
1210  int res = mysql_stmt_fetch(stmt);
1211  if (res == 1 || res == MYSQL_NO_DATA) {
1212  spdlog::debug("breaking with {0}", res);
1213  break;
1214  }
1215 
1216  measurement m;
1217 
1218  if (is_null[0] || is_error[0]) { // id
1219  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. ID mustn't be NULL or ERROR!");
1220  err_exit = true;
1221  } else {
1222  m.id = id;
1223  }
1224  if (is_null[1] || is_error[1]) { // measurement_number
1225  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. "
1226  "measurement_number mustn't be NULL or ERROR!");
1227  err_exit = true;
1228  } else {
1229  m.measurement_number = measurement_number;
1230  }
1231  if (is_null[2] || is_error[2]) { // measuree_id
1232  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. measuree_id mustn't be NULL or ERROR!");
1233  err_exit = true;
1234  } else {
1235  m.measuree_id = measuree_id;
1236  }
1237  if (is_null[3] || is_error[3]) { // measurer_id
1238  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. measurer_id mustn't be NULL or ERROR!");
1239  err_exit = true;
1240  } else {
1241  m.measurer_id = measurer_id;
1242  }
1243  if (is_null[4] || is_error[4]) { // measuring_length
1244  spdlog::debug("db_driver.cpp - fnc: query_continuous_measurement. measuring_length is NULL or ERROR!");
1245  } else {
1246  m.measuring_length = measuring_length;
1247  }
1248  if (is_null[5] || is_error[5]) { // measuring_start
1249  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. "
1250  "measuring_start mustn't be NULL or ERROR!");
1251  err_exit = true;
1252  } else {
1253  mysql_time_to_tm(&measuring_start, &(m.measuring_start));
1254  }
1255  if (is_null[6] || is_error[6]) { // measuring_end
1256  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. measuring_end mustn't be NULL or ERROR!");
1257  err_exit = true;
1258  } else {
1259  mysql_time_to_tm(&measuring_end, &(m.measuring_end));
1260  }
1261 
1262  // currently values are in double (save select from db with this)
1263  m.value_length = sizeof(double);
1264 
1265  if (is_null[7] || is_error[7]) { // value_count
1266  spdlog::debug("db_driver.cpp - fnc: query_continuous_measurement. value_count is NULL or "
1267  "ERROR but that isn't important in continuous measurement.");
1268  } else {
1269  m.value_count = value_count;
1270  }
1271  if (is_null[8] || is_error[8]) { // x_val_median
1272  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. x_val_median mustn't be NULL or ERROR!");
1273  err_exit = true;
1274  } else {
1275  m.x_val_med = x_val_med;
1276  }
1277  if (is_null[9] || is_error[9]) { // x_val_avg
1278  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. x_val_avg mustn't be NULL or ERROR!");
1279  err_exit = true;
1280  } else {
1281  m.x_val_avg = x_val_avg;
1282  }
1283  if (is_null[10] || is_error[10]) { // all_val_median
1284  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. all_val_median mustn't be NULL or ERROR!");
1285  err_exit = true;
1286  } else {
1287  m.all_val_med = all_val_med;
1288  }
1289  if (is_null[11] || is_error[11]) { // all_val_avg
1290  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. all_val_avg mustn't be NULL or ERROR!");
1291  err_exit = true;
1292  } else {
1293  m.all_val_avg = all_val_avg;
1294  }
1295  if (is_null[12] || is_error[12]) { // measurement_part
1296  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement."
1297  " measurement_part mustn't be NULL or ERROR!");
1298  err_exit = true;
1299  } else {
1300  m.measurement_part = measurement_part;
1301  }
1302 
1303  ms.push_back(m);
1304  }
1305 
1306  if (err_exit) {
1307  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to retrieve some critical data.");
1308  return 1;
1309  }
1310 
1311  return 0;
1312 }
1313 
1314 int db_driver::is_measurement_continuous(unsigned long m_number, unsigned long measuree_id) {
1315  int par_cnt = 2, res_cnt = 1;
1316  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
1317  unsigned long lengths [res_cnt] = {0};
1318  my_bool is_null [res_cnt] = {0};
1319  my_bool is_error [res_cnt] = {0};
1320 
1321  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1322  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
1323 
1324  std::string sql = "SELECT COUNT(id) FROM cs_measurements WHERE measurement_number = ? AND measuree_id = ?";
1325 
1326  // m_number
1327  bind_par[0].buffer_type = MYSQL_TYPE_LONG;
1328  bind_par[0].buffer = &m_number;
1329 
1330  // measuree_id
1331  bind_par[1].buffer_type = MYSQL_TYPE_LONG;
1332  bind_par[1].buffer = &measuree_id;
1333 
1334  long m_count = 0;
1335 
1336  // measurement count
1337  bind_res[0].buffer_type = MYSQL_TYPE_LONG;
1338  bind_res[0].buffer = &m_count;
1339  bind_res[0].is_null = &is_null[0];
1340  bind_res[0].length = &lengths [0];
1341  bind_res[0].error = &is_error [0];
1342 
1343  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1344  if (stmt == NULL) {
1345  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to init prepared statement.");
1346  return 1;
1347  }
1348  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1349  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to prepare prepared statement.");
1350  this->handle_stmt_error(stmt);
1351  return 1;
1352  }
1353  if (mysql_stmt_bind_param(stmt, bind_par)) {
1354  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. "
1355  "Failed to bind parameters to prepared statement");
1356  this->handle_stmt_error(stmt);
1357  return 1;
1358  }
1359  if (mysql_stmt_bind_result(stmt, bind_res)) {
1360  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to bind results to prepared statement");
1361  this->handle_stmt_error(stmt);
1362  return 1;
1363  }
1364  if (mysql_stmt_execute(stmt)) {
1365  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to execute prepared statement");
1366  this->handle_stmt_error(stmt);
1367  return 1;
1368  }
1369  if (mysql_stmt_store_result(stmt)) {
1370  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to store result of prepared statement");
1371  this->handle_stmt_error(stmt);
1372  return 1;
1373  }
1374  unsigned long long row_cnt = 0;
1375  if ((row_cnt = mysql_stmt_num_rows(stmt)) != 1) {
1376  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Got {0} results! Expected 1.", row_cnt);
1377  mysql_stmt_close(stmt);
1378  return 2;
1379  }
1380 
1381  int rv = mysql_stmt_fetch(stmt);
1382  if (rv == 1 || rv == MYSQL_NO_DATA) {
1383  spdlog::error("db_driver.cpp - fnc: query_continuous_measurement. Failed to fetch result even"
1384  " tought it should exist. (rv: {0})", rv);
1385  return 1;
1386  }
1387 
1388  if (is_null[0] || is_error[0]) {
1389  spdlog::error("db_driver.cpp - fnc: . Count mustn't be NULL or ERROR!");
1390  mysql_stmt_close(stmt);
1391  return 1;
1392  }
1393 
1394  mysql_stmt_close(stmt);
1395 
1396  // 1 if continuous, 9 if single, 1 otherwise (shouldn't occur)
1397  if (m_count > 1)
1398  return 0;
1399  if (m_count == 1)
1400  return 9;
1401  else
1402  return 1;
1403 }
1404 
1405 int db_driver::is_username_available(const char* uname) {
1406  int par_cnt = 1;
1407  MYSQL_BIND bind_par[par_cnt];
1408 
1409  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1410 
1411  std::string sql = "SELECT id FROM cs_users WHERE username = ?";
1412 
1413  // user id param
1414  bind_par[0].buffer_type = MYSQL_TYPE_STRING;
1415  bind_par[0].buffer = (char*) uname;
1416  bind_par[0].buffer_length = strlen(uname);
1417 
1418  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1419  if (stmt == NULL) {
1420  spdlog::error("db_driver.cpp - fnc: is_username_available. Failed to init prepared statement.");
1421  return 1;
1422  }
1423  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1424  spdlog::error("db_driver.cpp - fnc: is_username_available. Failed to prepare prepared statement.");
1425  this->handle_stmt_error(stmt);
1426  return 1;
1427  }
1428  if (mysql_stmt_bind_param(stmt, bind_par)) {
1429  spdlog::error("db_driver.cpp - fnc: is_username_available. Failed to bind parameters to prepared statement");
1430  this->handle_stmt_error(stmt);
1431  return 1;
1432  }
1433  if (mysql_stmt_execute(stmt)) {
1434  spdlog::error("db_driver.cpp - fnc: is_username_available. Failed to execute prepared statement");
1435  this->handle_stmt_error(stmt);
1436  return 1;
1437  }
1438  if (mysql_stmt_store_result(stmt)) {
1439  spdlog::error("db_driver.cpp - fnc: is_username_available. Failed to store result of prepared statement");
1440  this->handle_stmt_error(stmt);
1441  return 1;
1442  }
1443  unsigned long long row_cnt = 0;
1444  if ((row_cnt = mysql_stmt_num_rows(stmt)) > 1) {
1445  spdlog::error("db_driver.cpp - fnc: is_username_available. Got {0} results!"
1446  "This shouldn't occur, because username should be unique!!", row_cnt);
1447  mysql_stmt_close(stmt);
1448  return 2;
1449  }
1450  if (row_cnt == 1) {
1451  spdlog::info("db_driver.cpp - fnc: is_username_available. Username already exists!");
1452  mysql_stmt_close(stmt);
1453  return 3;
1454  }
1455 
1456  spdlog::info("db_driver.cpp - fnc: is_username_available. Username is available.");
1457  mysql_stmt_close(stmt);
1458  return 0;
1459 }
1460 
1461 int db_driver::is_rfid_serial_available(uint8_t *serial, uint8_t ser_len) {
1462  int par_cnt = 1;
1463  MYSQL_BIND bind_par[par_cnt];
1464 
1465  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1466 
1467  std::string sql = "SELECT id FROM cs_users WHERE rfid_serial = ?"; // maybe add status = 1?
1468 
1469  // user id param
1470  bind_par[0].buffer_type = MYSQL_TYPE_TINY_BLOB;
1471  bind_par[0].buffer = serial;
1472  bind_par[0].buffer_length = ser_len;
1473 
1474  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1475  if (stmt == NULL) {
1476  spdlog::error("db_driver.cpp - fnc: is_rfid_serial_available. Failed to init prepared statement.");
1477  return 1;
1478  }
1479  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1480  spdlog::error("db_driver.cpp - fnc: is_rfid_serial_available. Failed to prepare prepared statement.");
1481  this->handle_stmt_error(stmt);
1482  return 1;
1483  }
1484  if (mysql_stmt_bind_param(stmt, bind_par)) {
1485  spdlog::error("db_driver.cpp - fnc: is_rfid_serial_available. Failed to bind parameters to prepared statement");
1486  this->handle_stmt_error(stmt);
1487  return 1;
1488  }
1489  if (mysql_stmt_execute(stmt)) {
1490  spdlog::error("db_driver.cpp - fnc: is_rfid_serial_available. Failed to execute prepared statement");
1491  this->handle_stmt_error(stmt);
1492  return 1;
1493  }
1494  if (mysql_stmt_store_result(stmt)) {
1495  spdlog::error("db_driver.cpp - fnc: is_rfid_serial_available. Failed to store result of prepared statement");
1496  this->handle_stmt_error(stmt);
1497  return 1;
1498  }
1499  unsigned long long row_cnt = 0;
1500  if ((row_cnt = mysql_stmt_num_rows(stmt)) > 1) {
1501  spdlog::error("db_driver.cpp - fnc: is_rfid_serial_available. Got {0} results!"
1502  "This shouldn't occur, because username should be unique!!", row_cnt);
1503  mysql_stmt_close(stmt);
1504  return 2;
1505  }
1506  if (row_cnt == 1) {
1507  spdlog::info("db_driver.cpp - fnc: is_rfid_serial_available. RFID already exists!");
1508  mysql_stmt_close(stmt);
1509  return 3;
1510  }
1511 
1512  spdlog::info("db_driver.cpp - fnc: is_rfid_serial_available. RFID is available.");
1513  mysql_stmt_close(stmt);
1514  return 0;
1515 }
1516 
1518  int par_cnt = 1;
1519  MYSQL_BIND bind_par[par_cnt];
1520 
1521  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1522 
1523  std::string sql = "UPDATE cs_users SET measure_count = measure_count + 1 WHERE id = ?";
1524 
1525  // user id param
1526  bind_par[0].buffer_type = MYSQL_TYPE_LONG;
1527  bind_par[0].buffer = (void*) &id;
1528 
1529  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1530  if (stmt == NULL) {
1531  spdlog::error("db_driver.cpp - fnc: increment_user_measurement_count. Failed to init prepared statement.");
1532  return 1;
1533  }
1534  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1535  spdlog::error("db_driver.cpp - fnc: increment_user_measurement_count. Failed to prepare prepared statement.");
1536  this->handle_stmt_error(stmt);
1537  return 1;
1538  }
1539  if (mysql_stmt_bind_param(stmt, bind_par)) {
1540  spdlog::error("db_driver.cpp - fnc: increment_user_measurement_count."
1541  " Failed to bind parameters to prepared statement");
1542  this->handle_stmt_error(stmt);
1543  return 1;
1544  }
1545  if (mysql_stmt_execute(stmt)) {
1546  spdlog::error("db_driver.cpp - fnc: increment_user_measurement_count. Failed to execute prepared statement");
1547  this->handle_stmt_error(stmt);
1548  return 1;
1549  }
1550 
1551  mysql_stmt_close(stmt);
1552  return 0;
1553 }
1554 
1555 int db_driver::can_login_subuser(const char* uname) {
1556  int par_cnt = 1, res_cnt = 1;
1557  MYSQL_BIND bind_par[par_cnt], bind_res[res_cnt];
1558  unsigned long lengths [res_cnt] = {0};
1559  my_bool is_null [res_cnt] = {0};
1560  my_bool is_error [res_cnt] = {0};
1561 
1562  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1563  memset(bind_res, 0, sizeof(MYSQL_BIND) * res_cnt);
1564 
1565  std::string sql = "SELECT role FROM cs_users WHERE username = ?";
1566 
1567  // user id param
1568  bind_par[0].buffer_type = MYSQL_TYPE_STRING;
1569  bind_par[0].buffer = (char*) uname;
1570  bind_par[0].buffer_length = strlen(uname);
1571 
1572  uint8_t role;
1573 
1574  // role
1575  bind_res[0].buffer_type = MYSQL_TYPE_TINY; // probably TINYINT ? (-128 to 127)
1576  bind_res[0].buffer = &role;
1577  bind_res[0].is_null = &is_null[0];
1578  bind_res[0].length = &lengths [0];
1579  bind_res[0].error = &is_error [0];
1580 
1581  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1582  if (stmt == NULL) {
1583  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Failed to init prepared statement.");
1584  return 1;
1585  }
1586  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1587  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Failed to prepare prepared statement.");
1588  this->handle_stmt_error(stmt);
1589  return 1;
1590  }
1591  if (mysql_stmt_bind_param(stmt, bind_par)) {
1592  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Failed to bind parameters to prepared statement");
1593  this->handle_stmt_error(stmt);
1594  return 1;
1595  }
1596  if (mysql_stmt_bind_result(stmt, bind_res)) {
1597  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Failed to bind results to prepared statement");
1598  this->handle_stmt_error(stmt);
1599  return 1;
1600  }
1601  if (mysql_stmt_execute(stmt)) {
1602  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Failed to execute prepared statement");
1603  this->handle_stmt_error(stmt);
1604  return 1;
1605  }
1606  if (mysql_stmt_store_result(stmt)) {
1607  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Failed to store result of prepared statement");
1608  this->handle_stmt_error(stmt);
1609  return 1;
1610  }
1611  unsigned long long row_cnt = 0;
1612  if ((row_cnt = mysql_stmt_num_rows(stmt)) != 1) {
1613  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Got {0} results! Expected 1.", row_cnt);
1614  mysql_stmt_close(stmt);
1615  return 2;
1616  }
1617 
1618  int rv = mysql_stmt_fetch(stmt);
1619  if (rv == 1 || rv == MYSQL_NO_DATA) {
1620  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Failed to fetch result even"
1621  " tought it should exist. (rv: {0})", rv);
1622  return 1;
1623  }
1624 
1625  mysql_stmt_close(stmt);
1626 
1627  if (is_null[0] || is_error[0]) {
1628  spdlog::error("db_driver.cpp - fnc: can_login_subuser. Role mustn't be NULL or ERROR!");
1629  return 1;
1630  }
1631 
1632  return role == 0 ? 0 : 3; // only allow patients (role = 0)
1633 }
1634 
1635 int db_driver::update_user_rfid(unsigned long user_id, uint8_t* serial, uint8_t ser_len) {
1636  int par_cnt = 2;
1637  MYSQL_BIND bind_par[par_cnt];
1638  unsigned long serial_len = ser_len;
1639 
1640  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1641 
1642  std::string sql = "UPDATE cs_users SET rfid_serial = ? WHERE id = ?";
1643 
1644  // rfid_serial value
1645  bind_par[0].buffer_type = MYSQL_TYPE_TINY_BLOB;
1646  bind_par[0].buffer = serial;
1647  bind_par[0].length = &serial_len;
1648 
1649  // user id param
1650  bind_par[1].buffer_type = MYSQL_TYPE_LONG;
1651  bind_par[1].buffer = (void*) &(user_id);
1652 
1653  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1654  if (stmt == NULL) {
1655  spdlog::error("db_driver.cpp - fnc: update_user_rfid. Failed to init prepared statement.");
1656  return 1;
1657  }
1658  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1659  spdlog::error("db_driver.cpp - fnc: update_user_rfid. Failed to prepare prepared statement.");
1660  this->handle_stmt_error(stmt);
1661  return 1;
1662  }
1663  if (mysql_stmt_bind_param(stmt, bind_par)) {
1664  spdlog::error("db_driver.cpp - fnc: update_user_rfid. Failed to bind parameters to prepared statement");
1665  this->handle_stmt_error(stmt);
1666  return 1;
1667  }
1668  if (mysql_stmt_execute(stmt)) {
1669  spdlog::error("db_driver.cpp - fnc: update_user_rfid. Failed to execute prepared statement");
1670  this->handle_stmt_error(stmt);
1671  return 1;
1672  }
1673 
1674  mysql_stmt_close(stmt);
1675  return 0;
1676 }
1677 
1678 int db_driver::insert_user(user_cont *new_usr, const char* password) {
1679  if (password == nullptr || strlen(password) <= 0) {
1680  spdlog::error("db_driver.cpp - fnc: insert_user. Password musn't be null or empty");
1681  return 2;
1682  }
1683 
1684  int par_cnt = 8;
1685  MYSQL_BIND bind_par[par_cnt];
1686 
1687  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1688 
1689  std::string sql = "INSERT INTO"
1690  " cs_users(role, username, password, rfid_serial, description, name, lastname, date_of_birth)"
1691  " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
1692 
1693  // role
1694  bind_par[0].buffer_type = MYSQL_TYPE_TINY; // probably TINYINT ? (-128 to 127)
1695  bind_par[0].buffer = &(new_usr->role);
1696 
1697  // username
1698  bind_par[1].buffer_type = MYSQL_TYPE_STRING;
1699  bind_par[1].buffer = (char*) new_usr->username.c_str();
1700  bind_par[1].buffer_length = new_usr->username.size() + 1;
1701 
1702  // password
1703  bind_par[2].buffer_type = MYSQL_TYPE_STRING; // DB type is BINARY(64) this should work
1704  bind_par[2].buffer = (char*) password;
1705  bind_par[2].buffer_length = strlen(password);
1706 
1707  // rfid_serial
1708  bind_par[3].buffer_type = MYSQL_TYPE_TINY_BLOB;
1709  if (new_usr->rfid_ser_len <= 0) {
1710  bind_par[3].buffer = nullptr;
1711  } else {
1712  bind_par[3].buffer = new_usr->rfid_serial_bin;
1713  }
1714  bind_par[3].buffer_length = new_usr->rfid_ser_len;
1715 
1716  // description
1717  bind_par[4].buffer_type = MYSQL_TYPE_STRING;
1718  bind_par[4].buffer = (char*) new_usr->description.c_str();
1719  bind_par[4].buffer_length = new_usr->description.size() + 1;
1720 
1721  // name
1722  bind_par[5].buffer_type = MYSQL_TYPE_STRING;
1723  bind_par[5].buffer = (char*) new_usr->name.c_str();
1724  bind_par[5].buffer_length = new_usr->name.size() + 1;
1725 
1726  // lastname
1727  bind_par[6].buffer_type = MYSQL_TYPE_STRING;
1728  bind_par[6].buffer = (char*) new_usr->lastname.c_str();
1729  bind_par[6].buffer_length = new_usr->lastname.size() + 1;
1730 
1731  //date_of_birth
1732  char buf[32] = {0};
1733  strftime(buf, 32, "%Y-%m-%d", &(new_usr->date_of_birth));
1734  bind_par[7].buffer_type = MYSQL_TYPE_STRING;
1735  bind_par[7].buffer = buf;
1736  bind_par[7].buffer_length = strlen(buf);
1737 
1738 
1739  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1740  if (stmt == NULL) {
1741  spdlog::error("db_driver.cpp - fnc: insert_user. Failed to init prepared statement.");
1742  return 1;
1743  }
1744  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1745  spdlog::error("db_driver.cpp - fnc: insert_user. Failed to prepare prepared statement.");
1746  this->handle_stmt_error(stmt);
1747  return 1;
1748  }
1749  if (mysql_stmt_bind_param(stmt, bind_par)) {
1750  spdlog::error("db_driver.cpp - fnc: insert_user. Failed to bind parameters to prepared statement");
1751  this->handle_stmt_error(stmt);
1752  return 1;
1753  }
1754  if (mysql_stmt_execute(stmt)) {
1755  spdlog::error("db_driver.cpp - fnc: insert_user. Failed to execute prepared statement");
1756  this->handle_stmt_error(stmt);
1757  return 1;
1758  }
1759 
1760  spdlog::info("db_driver.cpp - fnc: insert_user. New user added successfully.");
1761  mysql_stmt_close(stmt);
1762  return 0;
1763 }
1764 
1766  int par_cnt = 15;
1767  MYSQL_BIND bind_par[par_cnt];
1768 
1769  memset(bind_par, 0, sizeof(MYSQL_BIND) * par_cnt);
1770 
1771  std::string sql = "INSERT INTO cs_measurements(measurement_number, measuree_id, measurer_id, measuring_length,"
1772  " measuring_start, measuring_end, description, value_length, value_count, m_values, x_val_median,"
1773  " x_val_avg, all_val_median, all_val_avg, measurement_part)"
1774  " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
1775 
1776  // measurement_number
1777  bind_par[0].buffer_type = MYSQL_TYPE_LONG;
1778  bind_par[0].buffer = &(m->measurement_number);
1779 
1780  // measuree_id
1781  bind_par[1].buffer_type = MYSQL_TYPE_LONG;
1782  bind_par[1].buffer = &(m->measuree_id);
1783 
1784  // measurer_id
1785  bind_par[2].buffer_type = MYSQL_TYPE_LONG;
1786  bind_par[2].buffer = &(m->measurer_id);
1787 
1788  // measuring_length
1789  bind_par[3].buffer_type = MYSQL_TYPE_LONG;
1790  bind_par[3].buffer = &(m->measuring_length);
1791 
1792  // measuring_start
1793  char date_start[32] = {0};
1794  strftime(date_start, 32, "%Y-%m-%d %H:%M:%S", &(m->measuring_start));
1795  bind_par[4].buffer_type = MYSQL_TYPE_STRING;
1796  bind_par[4].buffer = date_start;
1797  bind_par[4].buffer_length = strlen(date_start);
1798 
1799  // measuring_end (FIXME ran into problems on this part when measuring_end wasn't suplied)
1800  char date_end[32] = {0};
1801  strftime(date_end, 32, "%Y-%m-%d %H:%M:%S", &(m->measuring_end));
1802  bind_par[5].buffer_type = MYSQL_TYPE_STRING;
1803  bind_par[5].buffer = date_end;
1804  bind_par[5].buffer_length = strlen(date_end);
1805 
1806  // description
1807  bind_par[6].buffer_type = MYSQL_TYPE_STRING;
1808  bind_par[6].buffer = (char*) m->description.c_str();
1809  bind_par[6].buffer_length = m->description.size() + 1;
1810 
1811  // value_length
1812  bind_par[7].buffer_type = MYSQL_TYPE_TINY;
1813  bind_par[7].buffer = &(m->value_length);
1814 
1815  // value_count
1816  bind_par[8].buffer_type = MYSQL_TYPE_LONG;
1817  bind_par[8].buffer = &(m->value_count);
1818 
1819  // rfid_serial
1820  bind_par[9].buffer_type = MYSQL_TYPE_MEDIUM_BLOB;
1821  bind_par[9].buffer = m->values.data();
1822  bind_par[9].buffer_length = m->values.size() * sizeof(double); // n * s
1823 
1824  // x_val_median
1825  bind_par[10].buffer_type = MYSQL_TYPE_DOUBLE;
1826  bind_par[10].buffer = &(m->x_val_med);
1827 
1828  // x_val_avg
1829  bind_par[11].buffer_type = MYSQL_TYPE_DOUBLE;
1830  bind_par[11].buffer = &(m->x_val_avg);
1831 
1832  // all_val_median
1833  bind_par[12].buffer_type = MYSQL_TYPE_DOUBLE;
1834  bind_par[12].buffer = &(m->all_val_med);
1835 
1836  // all_val_avg
1837  bind_par[13].buffer_type = MYSQL_TYPE_DOUBLE;
1838  bind_par[13].buffer = &(m->all_val_avg);
1839 
1840  // measurement_part
1841  bind_par[14].buffer_type = MYSQL_TYPE_LONG;
1842  bind_par[14].buffer = &(m->measurement_part);
1843 
1844  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1845  if (stmt == NULL) {
1846  spdlog::error("db_driver.cpp - fnc: insert_measurement. Failed to init prepared statement.");
1847  return 1;
1848  }
1849  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1850  spdlog::error("db_driver.cpp - fnc: insert_measurement. Failed to prepare prepared statement.");
1851  this->handle_stmt_error(stmt);
1852  return 1;
1853  }
1854  if (mysql_stmt_bind_param(stmt, bind_par)) {
1855  spdlog::error("db_driver.cpp - fnc: insert_measurement. Failed to bind parameters to prepared statement");
1856  this->handle_stmt_error(stmt);
1857  return 1;
1858  }
1859  if (mysql_stmt_execute(stmt)) {
1860  spdlog::error("db_driver.cpp - fnc: insert_measurement. Failed to execute prepared statement");
1861  this->handle_stmt_error(stmt);
1862  return 1;
1863  }
1864 
1865  spdlog::info("db_driver.cpp - fnc: insert_measurement. Measurement added successfully.");
1866  mysql_stmt_close(stmt);
1867  return 0;
1868 }
1869 
1870 void db_driver::mysql_time_to_tm(MYSQL_TIME* src, std::tm *dest) {
1871  // https://stackoverflow.com/questions/421234/convert-mysql-time-data-type-to-char-or-c-string
1872  char buf[32] = {0};
1873  sprintf(buf, "%04d-%02d-%02d %02d:%02d:%02d", src->year, src->month, src->day, src->hour, src->minute, src->second);
1874  strptime(buf, "%Y-%m-%d %H:%M:%S", dest);
1875 
1876  // for better performance try: dest->tm_year = src->year; ...
1877 }
1878 
1879 void db_driver::handle_stmt_error(MYSQL_STMT* stmt) {
1880  spdlog::error("db_driver.cpp - Prepared Statement Error ({0}): [{1}] \"{2}\"",
1881  mysql_stmt_errno(stmt),
1882  mysql_stmt_sqlstate(stmt),
1883  mysql_stmt_error(stmt));
1884 
1885  mysql_stmt_close(stmt);
1886 }
1887 
1888 // int db_driver::prprd_stmt_executor(const char *sql, const char* fnc_name,
1889 // MYSQL_BIND *par, int par_cnt, MYSQL_BIND *res, int res_cnt)
1890 // {
1891 
1892 // }
1893 
1894 /*****
1895  * tests
1896 *****/
1897 
1898 static void show_stmt_error(MYSQL_STMT *stmt)
1899 {
1900  printf("Error(%d) [%s] \"%s\"", mysql_stmt_errno(stmt),
1901  mysql_stmt_sqlstate(stmt),
1902  mysql_stmt_error(stmt));
1903 }
1904 
1905 static char text_test[64] = "insert from c";
1906 static uint8_t binary_data_test[] = {0x10, 0x50, 0xFF, 0x07, 0xAB, 0xB7, 0xBC, 0xCB, 0xCC, 0x77};
1907 static int int_test = 5;
1908 static float float_test = 8.00719;
1909 
1911  int bind_count = 5;
1912  MYSQL_BIND bind[bind_count];
1913  std::string sql("INSERT INTO test(text_test, int_test, float_test, binary_data, blob_data) VALUES (?, ?, ?, ?, ?)");
1914  // std::string sql("INSERT INTO test(text_test, int_test) VALUES (?,?)");
1915 
1916  memset(bind, 0, sizeof(MYSQL_BIND) * bind_count);
1917 
1918  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1919  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1920  spdlog::error("Failed prepared statment.");
1921  show_stmt_error(stmt);
1922  return;
1923  }
1924 
1925  unsigned long bin_count = 10;
1926 
1927  bind[0].buffer_type = MYSQL_TYPE_STRING; // string works but VARCHAR doesnt, i guess it expects something more
1928  //bind[0].buffer_type = MYSQL_TYPE_VARCHAR;
1929  bind[0].buffer = text_test;
1930  bind[0].buffer_length = strlen(text_test);
1931 
1932  bind[1].buffer_type = MYSQL_TYPE_LONG;
1933  bind[1].buffer = &int_test;
1934 
1935  bind[2].buffer_type = MYSQL_TYPE_FLOAT;
1936  bind[2].buffer = &float_test;
1937 
1938  bind[3].buffer_type = MYSQL_TYPE_STRING;
1939  bind[3].buffer = binary_data_test;
1940  bind[3].length = &bin_count;
1941 
1942  bind[4].buffer_type = MYSQL_TYPE_MEDIUM_BLOB;
1943  bind[4].buffer = binary_data_test;
1944  bind[4].length = &bin_count;
1945 
1946  // int array_size = 5;
1947  // mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &array_size);
1948 
1949  mysql_stmt_bind_param(stmt, bind);
1950 
1951  if (mysql_stmt_execute(stmt))
1952  show_stmt_error(stmt);
1953 
1954  mysql_stmt_close(stmt);
1955 }
1956 
1958  int params = 1;
1959  int results = 5;
1960  MYSQL_BIND bind_param[params];
1961  MYSQL_BIND bind_res [results];
1962  unsigned long length[results] = {0};
1963  my_bool is_null [results] = {0};
1964  my_bool error [results] = {0};
1965 
1966  //std::string sql("SELECT text_test, int_test, float_test, binary_data, blob_data FROM test WHERE id = ?");
1967  // std::string sql("SELECT text_test, int_test, float_test, binary_data, blob_data FROM test WHERE text_test LIKE ?");
1968  std::string sql("SELECT text_test, int_test, float_test, binary_data, blob_data FROM test WHERE blob_data = ?");
1969  // std::string sql("INSERT INTO test(text_test, int_test) VALUES (?,?)");
1970 
1971  memset(bind_param, 0, sizeof(MYSQL_BIND) * params);
1972  memset(bind_res, 0, sizeof(MYSQL_BIND) * results);
1973 
1974  MYSQL_STMT *stmt = mysql_stmt_init(conn.get());
1975  if (mysql_stmt_prepare(stmt, sql.c_str(), strlen(sql.c_str()))) {
1976  spdlog::error("Failed prepared statment.");
1977  show_stmt_error(stmt);
1978  return;
1979  }
1980 
1981  // long searched_id = 5;
1982  // char* search_string = "%%from%";
1983  unsigned long bin_count = 10;
1984 
1985  float f_in;
1986  int i_in;
1987  char t_in[128];
1988  uint8_t bin_in[128];
1989  uint8_t blob_in[128];
1990 
1991  // string where
1992  // bind_param[0].buffer_type = MYSQL_TYPE_STRING; // string works but VARCHAR doesnt, i guess it expects something more
1993  // bind_param[0].buffer = search_string;
1994  // bind_param[0].buffer_length = strlen(search_string);
1995 
1996  // int (long) where
1997  // bind_param[0].buffer_type = MYSQL_TYPE_LONG;
1998  // bind_param[0].buffer = &searched_id;
1999 
2000  bind_param[0].buffer_type = MYSQL_TYPE_MEDIUM_BLOB;
2001  bind_param[0].buffer = binary_data_test;
2002  bind_param[0].length = &bin_count;
2003 
2004  bind_res[0].buffer_type = MYSQL_TYPE_STRING; // string works but VARCHAR doesnt, i guess it expects something more
2005  bind_res[0].buffer = t_in;
2006  bind_res[0].buffer_length = 128;
2007  bind_res[0].is_null = &is_null [0];
2008  bind_res[0].length = &length [0];
2009  bind_res[0].error = &error [0];
2010 
2011  bind_res[1].buffer_type = MYSQL_TYPE_LONG;
2012  bind_res[1].buffer = &i_in;
2013  bind_res[1].is_null = &is_null [1];
2014  bind_res[1].length = &length [1];
2015  bind_res[1].error = &error [1];
2016 
2017  bind_res[2].buffer_type = MYSQL_TYPE_FLOAT;
2018  bind_res[2].buffer = &f_in;
2019  bind_res[2].is_null = &is_null [2];
2020  bind_res[2].length = &length [2];
2021  bind_res[2].error = &error [2];
2022 
2023  bind_res[3].buffer_type = MYSQL_TYPE_STRING;
2024  bind_res[3].buffer = bin_in;
2025  bind_res[3].buffer_length = 128;
2026  bind_res[3].is_null = &is_null [3];
2027  bind_res[3].length = &length [3];
2028  bind_res[3].error = &error [3];
2029 
2030  bind_res[4].buffer_type = MYSQL_TYPE_MEDIUM_BLOB;
2031  bind_res[4].buffer = blob_in;
2032  bind_res[4].buffer_length = 128;
2033  bind_res[4].is_null = &is_null [4];
2034  bind_res[4].length = &length [4];
2035  bind_res[4].error = &error [4];
2036 
2037  // int array_size = 5;
2038  // mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, &array_size);
2039 
2040  if (mysql_stmt_bind_param(stmt, bind_param)) {
2041  spdlog::error("Failed to bind parameters");
2042  return;
2043  }
2044  if (mysql_stmt_bind_result(stmt, bind_res)) {
2045  spdlog::error("Failed to bind results");
2046  return;
2047  }
2048  if (mysql_stmt_execute(stmt)) {
2049  spdlog::error("Failed to execute statement");
2050  show_stmt_error(stmt);
2051  return;
2052  }
2053  if (mysql_stmt_store_result(stmt)) {
2054  spdlog::error("Failed to store result");
2055  show_stmt_error(stmt);
2056  return;
2057  }
2058 
2059  //spdlog::info("here");
2060  // printf("rv: %d, %d, %d\n", rv, MYSQL_NO_DATA, MYSQL_DATA_TRUNCATED);
2061  while (1) {
2062  int res = mysql_stmt_fetch(stmt);
2063  if (res == 1 || res == MYSQL_NO_DATA) {
2064  spdlog::info("breaking with {0}", res);
2065  break;
2066  }
2067 
2068  spdlog::info("Fetched result");
2069 
2070  for (int i = 0; i < results; i++) {
2071  printf("result flags: is_null: %c\terror: %c\tlength: %ld\n", is_null[i], error[i], length[i]);
2072  if (is_null[i]) {
2073  printf("value %d is null\n", i);
2074  }
2075  if (error[i]) {
2076  printf("error in val %d : %d\n", i, error[i]);
2077  }
2078  // printf("%s\n", t_in);
2079  }
2080 
2081  printf("%s\n", t_in);
2082  printf("%d\n", i_in);
2083  printf("%f\n", f_in);
2084 
2085  // test blob
2086  long unsigned int len = bin_count;
2087  bool correct = true;
2088  if (bin_count != length[results - 1]) {
2089  printf("blob size mismatch\n");
2090  len = length[results - 1] < len ? length[results - 1] : len;
2091  }
2092  for (long unsigned int i = 0; i < len; i++) {
2093  if (binary_data_test[i] != blob_in[i]) {
2094  printf("blob data aren't correct on byte %ld\n", i);
2095  correct = false;
2096  }
2097  }
2098 
2099  if (correct) {
2100  printf("YEEEEY! blob data match\n");
2101  } else {
2102  printf("OUCH! blob data dont match\n");
2103  }
2104 
2105  // test binary
2106  long unsigned int len2 = bin_count;
2107  bool correct2 = true;
2108  if (bin_count != length[results - 2]) {
2109  printf("binary size mismatch, but its expected. Binary is padded with 0 from right\n");
2110  len2 = length[results - 2] < len2 ? length[results - 2] : len2;
2111  }
2112  for (long unsigned int i = 0; i < len2; i++) {
2113  if (binary_data_test[i] != blob_in[i]) {
2114  printf("binary data aren't correct on byte %ld\n", i);
2115  correct2 = false;
2116  }
2117  }
2118 
2119  if (correct2) {
2120  printf("YEEEEY! binary data match\n");
2121  } else {
2122  printf("OUCH! binary data dont match\n");
2123  }
2124 
2125  }
2126 
2127  mysql_stmt_close(stmt);
2128 }
2129 
2135 int db_driver::test() {
2136  // Initialize Connection
2137  MYSQL *conn;
2138  if (!(conn = mysql_init(0)))
2139  {
2140  fprintf(stderr, "unable to initialize connection struct\n");
2141  exit(1);
2142  }
2143 
2144  // Connect to the database
2145  if (!mysql_real_connect(
2146  conn, // Connection
2147  "localhost", // Host
2148  "pi", // User account
2149  "raspberry", // User password
2150  "control_system", // Default database
2151  3306, // Port number
2152  NULL, // Path to socket file
2153  0 // Additional options
2154  ))
2155  {
2156  printf("Failed to open mysql connection.\n");
2157  // Report the failed-connection error & close the handle
2158  fprintf(stderr, "Error connecting to Server: %s\n", mysql_error(conn));
2159  mysql_close(conn);
2160  exit(1);
2161  }
2162 
2163  // Use the Connection
2164  // ...
2165  const char* sql = "SELECT * FROM test";
2166  if (mysql_real_query(conn, sql, strlen(sql))) {
2167  printf("Failed %s\n", sql);
2168  return 1;
2169  }
2170 
2171  MYSQL_RES* res = mysql_store_result(conn);
2172  if (!res) {
2173  printf("RESULT SET is null/ empty\n");
2174  return 1;
2175  }
2176 
2177  MYSQL_ROW row;
2178  unsigned int num_fields;
2179  unsigned int i;
2180 
2181  num_fields = mysql_num_fields(res);
2182  while ((row = mysql_fetch_row(res))) {
2183  unsigned long *lengths;
2184  lengths = mysql_fetch_lengths(res);
2185  for(i = 0; i < num_fields; i++)
2186  {
2187  printf("[%.*s] ", (int) lengths[i],
2188  row[i] ? row[i] : "NULL");
2189  }
2190  printf("\n");
2191  }
2192  /*
2193  int cols = mysql_field_count(conn);
2194  printf("%s returns %d cols\n", sql, cols);
2195  */
2196 
2197  mysql_free_result(res);
2198 
2199  // Close the Connection
2200  mysql_close(conn);
2201 
2202  return 0;
2203 }
int can_login_subuser(const char *uname)
Definition: db_driver.cpp:1555
bool is_open()
Definition: db_driver.cpp:71
void test_select_with_binary()
Definition: db_driver.cpp:1957
int insert_measurement(measurement *m)
Definition: db_driver.cpp:1765
int is_username_available(const char *uname)
Definition: db_driver.cpp:1405
int is_rfid_serial_available(uint8_t *serial, uint8_t ser_len)
Definition: db_driver.cpp:1461
int query_username(std::string &uname, unsigned long id)
Definition: db_driver.cpp:498
int is_measurement_continuous(unsigned long m_number, unsigned long measuree_id)
Definition: db_driver.cpp:1314
void test_insert_with_binary()
Definition: db_driver.cpp:1910
db_driver(db_driver_conf *conf)
Construct a new db driver object from configuration.
Definition: db_driver.cpp:24
int insert_user(user_cont *new_usr, const char *password)
Definition: db_driver.cpp:1678
int update_user_rfid(unsigned long user_id, uint8_t *serial, uint8_t ser_len)
Definition: db_driver.cpp:1635
int query_continuous_measurement(std::vector< measurement > &ms, unsigned long m_number, unsigned long measuree_id)
Definition: db_driver.cpp:1054
int query_measurement(measurement *m, unsigned long id)
Definition: db_driver.cpp:720
int query_measurement_headers(user_cont *usr)
Definition: db_driver.cpp:580
int query_user_data(user_cont *usr, const char *uname=nullptr, uint8_t *rfid_serial=nullptr, uint8_t rfid_ser_len=0)
Selects all user data into user container @usr. Data is selected by either username or rfid_serial....
Definition: db_driver.cpp:199
int query_user_credentials(user_cred *creds, const char *uname)
Selects username, password and status into @creds filtered by @uname.
Definition: db_driver.cpp:75
int increment_user_measurement_count(unsigned long id)
Definition: db_driver.cpp:1517
Serves as container for basic measurement information, that is dispayed to GUI.
unsigned long measuree_id
unsigned long measurement_number
unsigned long id
Container for measurement data and (convenience) variables, that are used to show measurement in GUI.
unsigned long measuring_length
unsigned long id
uint8_t value_length
unsigned long measurement_part
std::vector< double > values
unsigned long value_count
std::tm created_at
std::tm measuring_end
double all_val_med
double all_val_avg
unsigned long measuree_id
std::string description
unsigned long measurer_id
std::tm measuring_start
double x_val_avg
double x_val_med
unsigned long measurement_number
Class used as a container for user data, that are selected from database. (and also for insert,...
std::string lastname
uint8_t role
std::string username
unsigned long id
std::tm last_login_date
std::vector< std::unique_ptr< measurement_header > > measur_headers
std::tm reg_date
std::tm date_of_birth
unsigned long measure_count
std::tm created_at_date
std::string description
uint8_t rfid_serial_bin[10]
std::string name
uint8_t rfid_ser_len
#define conf
#define DB_DEFAULT_PASSWD
Definition: db_driver.cpp:18
#define DB_SIZE_128
Definition: db_driver.cpp:12
#define DB_DEFAULT_PORT
Definition: db_driver.cpp:20
#define MAX_MEDIUM_BLOB
Definition: db_driver.cpp:14
#define DB_DEFAULT_SOCKET
Definition: db_driver.cpp:21
#define DB_DEFAULT_DB
Definition: db_driver.cpp:19
#define DB_DEFAULT_USER
Definition: db_driver.cpp:17
#define DB_DEFAULT_CLIENTFLAGS
Definition: db_driver.cpp:22
#define DB_DEFAULT_HOST
Definition: db_driver.cpp:16
#define DB_SIZE_64
Definition: db_driver.cpp:11
#define DB_SIZE_2048
Definition: db_driver.cpp:13
Container for configuration neccessary to open database connnection.
Definition: db_driver.h:16
Struct used to contain username, password and user status until credentials are verified.
Definition: db_driver.h:30
char passwd[64]
Definition: db_driver.h:33
uint8_t status
Definition: db_driver.h:34
char uname[64]
Definition: db_driver.h:31