| 2 |
raymond |
1 |
#pragma once
|
|
|
2 |
#include <Arduino.h>
|
|
|
3 |
#include <MySQL.h> // https://github.com/cotestatnt/Arduino-MySQL
|
|
|
4 |
#include <WiFiClient.h>
|
|
|
5 |
|
|
|
6 |
extern String getSHA256(const char*);
|
|
|
7 |
|
|
|
8 |
// MySQL client class
|
|
|
9 |
WiFiClient client;
|
|
|
10 |
MySQL sql(&client, dbHost.c_str(), dbPort);
|
|
|
11 |
|
|
|
12 |
#define MAX_QUERY_LEN 512 // MAX query string length
|
|
|
13 |
|
|
|
14 |
inline const char createUsersTable[] PROGMEM = R"string_literal(
|
|
|
15 |
CREATE TABLE %s (
|
|
|
16 |
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
17 |
username VARCHAR(32) UNIQUE,
|
|
|
18 |
password VARCHAR(128),
|
|
|
19 |
name VARCHAR(64),
|
|
|
20 |
email VARCHAR(64),
|
|
|
21 |
tag_code BIGINT UNSIGNED UNIQUE,
|
|
|
22 |
level INT
|
|
|
23 |
);
|
|
|
24 |
)string_literal";
|
|
|
25 |
|
|
|
26 |
inline const char createLogTable[] PROGMEM = R"string_literal(
|
|
|
27 |
CREATE TABLE %s (
|
|
|
28 |
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
29 |
epoch BIGINT,
|
|
|
30 |
username VARCHAR(32),
|
|
|
31 |
tag_code BIGINT UNSIGNED,
|
|
|
32 |
reader INT UNSIGNED
|
|
|
33 |
);
|
|
|
34 |
)string_literal";
|
|
|
35 |
|
|
|
36 |
|
|
|
37 |
// Insert or update a device (if ble_id already defined keep it's actual value)
|
|
|
38 |
inline const char newUpdateUser[] PROGMEM = R"string_literal(
|
|
|
39 |
INSERT INTO users (username, password, name, email, tag_code, level)
|
|
|
40 |
VALUES ('%s', '%s', '%s', '%s', %s, %s)
|
|
|
41 |
ON DUPLICATE KEY UPDATE
|
|
|
42 |
username = VALUES(username),
|
|
|
43 |
password = VALUES(password),
|
|
|
44 |
name = VALUES(name),
|
|
|
45 |
email = VALUES(email),
|
|
|
46 |
tag_code = VALUES(tag_code),
|
|
|
47 |
level = VALUES(level);
|
|
|
48 |
)string_literal";
|
|
|
49 |
|
|
|
50 |
|
|
|
51 |
// Establish connection with MySQL database according to the variables defined (/setup webpage)
|
|
|
52 |
bool connectToDatabase() {
|
|
|
53 |
if (sql.connected()) {
|
|
|
54 |
return true;
|
|
|
55 |
}
|
|
|
56 |
Serial.printf("\nConnecting to MySQL server %s on DataBase %s...\n", dbHost.c_str(), database.c_str());
|
|
|
57 |
if (sql.connect(user.c_str(), password.c_str(), database.c_str())) {
|
|
|
58 |
delay(200);
|
|
|
59 |
return true;
|
|
|
60 |
}
|
|
|
61 |
Serial.println("Fails!");
|
|
|
62 |
sql.disconnect();
|
|
|
63 |
return false;
|
|
|
64 |
}
|
|
|
65 |
|
|
|
66 |
|
|
|
67 |
// Variadic function that will execute the query selected with passed parameters
|
|
|
68 |
bool queryExecute(DataQuery_t& data, const char* queryStr, ...) {
|
|
|
69 |
if (connectToDatabase()) {
|
|
|
70 |
char buf[MAX_QUERY_LEN];
|
|
|
71 |
va_list args;
|
|
|
72 |
va_start (args, queryStr);
|
|
|
73 |
vsnprintf (buf, sizeof(buf), queryStr, args);
|
|
|
74 |
va_end (args);
|
|
|
75 |
|
|
|
76 |
// Execute the query
|
|
|
77 |
Serial.printf("Execute SQL query: %s\n", buf);
|
|
|
78 |
return sql.query(data, buf);
|
|
|
79 |
}
|
|
|
80 |
return false;
|
|
|
81 |
}
|
|
|
82 |
|
|
|
83 |
bool checkAndCreateTables() {
|
|
|
84 |
// Create tables if not exists
|
|
|
85 |
Serial.println("\nCreate table if not exists");
|
|
|
86 |
DataQuery_t data;
|
|
|
87 |
if (!queryExecute(data, createUsersTable, "users")) {
|
|
|
88 |
if (strcmp(sql.getLastSQLSTATE(), "42S01") != 0)
|
|
|
89 |
return false;
|
|
|
90 |
}
|
|
|
91 |
|
|
|
92 |
if (!queryExecute(data, createLogTable, "logs")) {
|
|
|
93 |
if (strcmp(sql.getLastSQLSTATE(), "42S01") != 0)
|
|
|
94 |
return false;
|
|
|
95 |
}
|
|
|
96 |
|
|
|
97 |
String hashed = getSHA256("admin");
|
|
|
98 |
if (queryExecute(data, newUpdateUser, "admin", hashed.c_str(), "Update password!", "", "0", "5")) {
|
|
|
99 |
Serial.println("admin@admin user created");
|
|
|
100 |
}
|
|
|
101 |
return true;
|
|
|
102 |
}
|
|
|
103 |
|