--- a/bmsd/mysql.c Fri Oct 18 13:20:35 2019 +0200
+++ b/bmsd/mysql.c Fri May 01 16:37:31 2020 +0200
@@ -3,7 +3,7 @@
* @brief MySQL/MariaDB access.
* @author Michiel Broek <mbroek at mbse dot eu>
*
- * Copyright (C) 2018-2019
+ * Copyright (C) 2018-2020
*
* This file is part of the bms (Brewery Management System)
*
@@ -36,6 +36,7 @@
extern sys_node_list *nodes;
extern sys_fermenter_list *fermenters;
extern sys_co2meter_list *co2meters;
+extern sys_ispindel_list *ispindels;
extern int debug;
@@ -62,7 +63,8 @@
sys_node_list *node, *tmpp;
sys_fermenter_list *fermenter, *tmpf;
sys_co2meter_list *co2meter, *tmpc;
- int ccnt = 0, ncnt = 0, fcnt = 0;
+ sys_ispindel_list *ispindel, *tmpi;
+ int icnt = 0, ccnt = 0, ncnt = 0, fcnt = 0;
con = mysql_init(NULL);
if (con == NULL) {
@@ -97,7 +99,7 @@
node->next = NULL;
node->uuid = xstrcpy(row[1]);
node->node = xstrcpy(row[2]);
- node->online = false; // Will be set using MQTT
+ node->online = atoi(row[3]);
node->group_id = xstrcpy(row[4]);
node->hardwaremake = xstrcpy(row[5]);
node->hardwaremodel = xstrcpy(row[6]);
@@ -115,6 +117,7 @@
node->net_address = xstrcpy(row[18]);
node->net_ifname = xstrcpy(row[19]);
node->net_rssi = atoi(row[20]);
+ node->interval = atoi(row[21]);
if (nodes == NULL) {
nodes = node;
@@ -146,7 +149,7 @@
fermenter->uuid = xstrcpy(row[1]);
fermenter->alias = xstrcpy(row[2]);
fermenter->node = xstrcpy(row[3]);
- fermenter->online = false; // Will be set later
+ fermenter->online = atoi(row[4]);
fermenter->beercode = xstrcpy(row[5]);
fermenter->beername = xstrcpy(row[6]);
fermenter->beeruuid = xstrcpy(row[44]);
@@ -242,7 +245,7 @@
co2meter->uuid = xstrcpy(row[1]);
co2meter->alias = xstrcpy(row[2]);
co2meter->node = xstrcpy(row[3]);
- co2meter->online = 0; // Will be set later
+ co2meter->online = atoi(row[4]);
co2meter->beercode = xstrcpy(row[5]);
co2meter->beername = xstrcpy(row[6]);
co2meter->beeruuid = xstrcpy(row[7]);
@@ -272,7 +275,49 @@
}
}
- syslog(LOG_NOTICE, "MySQL: loaded %d nodes, %d fermenters, %d co2meters", ncnt, fcnt, ccnt);
+ if (mysql_query(con, "SELECT * FROM mon_ispindels")) {
+ syslog(LOG_NOTICE, "MySQL: SELECT * FROM mon_ispindels error %u (%s))", mysql_errno(con), mysql_error(con));
+ } else {
+ res_set = mysql_store_result(con);
+ if (res_set == NULL) {
+ syslog(LOG_NOTICE, "MySQL: mysq_store_result error %u (%s))", mysql_errno(con), mysql_error(con));
+ } else {
+ while ((row = mysql_fetch_row(res_set)) != NULL) {
+ ispindel = (sys_ispindel_list *)malloc(sizeof(sys_ispindel_list));
+ memset(ispindel, 0, sizeof(sys_ispindel_list));
+ ispindel->next = NULL;
+ ispindel->uuid = xstrcpy(row[1]);
+ ispindel->alias = xstrcpy(row[2]);
+ ispindel->node = xstrcpy(row[3]);
+ ispindel->online = atoi(row[4]);
+ ispindel->alarm = atoi(row[5]);
+ ispindel->beercode = xstrcpy(row[6]);
+ ispindel->beername = xstrcpy(row[7]);
+ ispindel->beeruuid = xstrcpy(row[8]);
+ ispindel->angle = atof(row[9]);
+ ispindel->temperature = atof(row[10]);
+ ispindel->battery = atof(row[11]);
+ ispindel->gravity = atof(row[12]);
+ ispindel->interval = atoi(row[13]);
+ ispindel->mode = xstrcpy(row[14]);
+
+ if (ispindels == NULL) {
+ ispindels = ispindel;
+ } else {
+ for (tmpi = ispindels; tmpi; tmpi = tmpi->next) {
+ if (tmpi->next == NULL) {
+ tmpi->next = ispindel;
+ break;
+ }
+ }
+ }
+ icnt++;
+ }
+ mysql_free_result(res_set);
+ }
+ }
+
+ syslog(LOG_NOTICE, "MySQL: loaded %d nodes, %d fermenters, %d co2meters %d ispindels", ncnt, fcnt, ccnt, icnt);
return 0;
}
@@ -280,6 +325,7 @@
void bms_mysql_end(void)
{
+ sys_ispindel_list *tmpi, *oldtmpi;
sys_co2meter_list *tmpp, *oldtmpp;
sys_fermenter_list *tmpf, *oldtmpf;
sys_node_list *tmpn, *oldtmpn;
@@ -290,6 +336,25 @@
if (debug)
fprintf(stdout, "MySQL: disconnected\n");
+ for (tmpi = ispindels; tmpi; tmpi = oldtmpi) {
+ oldtmpi = tmpi->next;
+ if (tmpi->uuid)
+ free(tmpi->uuid);
+ if (tmpi->alias)
+ free(tmpi->alias);
+ if (tmpi->node)
+ free(tmpi->node);
+ if (tmpi->beercode)
+ free(tmpi->beercode);
+ if (tmpi->beername)
+ free(tmpi->beername);
+ if (tmpi->beeruuid)
+ free(tmpi->beeruuid);
+ if (tmpi->mode)
+ free(tmpi->mode);
+ free(tmpi);
+ }
+
for (tmpp = co2meters; tmpp; tmpp = oldtmpp) {
oldtmpp = tmpp->next;
if (tmpp->uuid)
@@ -397,18 +462,20 @@
int bms_mysql_query(const char *query)
{
int rc = mysql_query(con, query);
+ int err = mysql_errno(con);
if (rc) {
- syslog(LOG_NOTICE, "MySQL: error %u (%s)", mysql_errno(con), mysql_error(con));
+ syslog(LOG_NOTICE, "MySQL: error %u (%s)", err, mysql_error(con));
syslog(LOG_NOTICE, query);
} else {
return 0;
}
/* Any error execpt server gone away */
- if (rc != 2006)
+ if (err != 2006)
return rc;
+ syslog(LOG_NOTICE, "Trying to reconnect");
/* Try to reconnect and do the query again */
mysql_close(con);
if (mysql_real_connect(con, Config.mysql_host, Config.mysql_user, Config.mysql_pass, Config.mysql_database, Config.mysql_port, NULL, 0) == NULL) {
@@ -434,25 +501,25 @@
void node_mysql_insert(sys_node_list *node)
{
- char *query = malloc(1024), first[21], last[21];
+ char *query = malloc(1024), first[73], last[73];
struct tm *mytime;
mytime = localtime(&node->firstseen);
- snprintf(first, 20, "%04d-%02d-%02d %02d:%02d:%02d",
+ snprintf(first, 72, "%04d-%02d-%02d %02d:%02d:%02d",
mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);
mytime = localtime(&node->lastseen);
- snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d",
+ snprintf(last, 72, "%04d-%02d-%02d %02d:%02d:%02d",
mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);
snprintf(query, 1023,
- "INSERT INTO mon_nodes SET uuid='%s', node='%s', online='%s', group_id='%s', " \
+ "INSERT INTO mon_nodes SET uuid='%s', node='%s', online='%d', group_id='%s', " \
"hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', firstseen='%s', lastseen='%s', " \
"temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \
- "net_address='%s', net_ifname='%s', net_rssi='%d'",
- node->uuid, node->node, node->online ?"Y":"N", node->group_id,
+ "net_address='%s', net_ifname='%s', net_rssi='%d', up_interval='%d'",
+ node->uuid, node->node, node->online ?1:0, node->group_id,
node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, first, last,
node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude,
- node->net_address, node->net_ifname, node->net_rssi);
+ node->net_address, node->net_ifname, node->net_rssi, node->interval);
if (bms_mysql_query(query) == 0) {
syslog(LOG_NOTICE, "MySQL: insert new node %s", node->node);
@@ -465,20 +532,20 @@
void node_mysql_update(sys_node_list *node)
{
- char *query = malloc(1024), last[21];
+ char *query = malloc(1024), last[65];
struct tm *mytime;
mytime = localtime(&node->lastseen);
- snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d",
+ snprintf(last, 64, "%04d-%02d-%02d %02d:%02d:%02d",
mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);
snprintf(query, 1023,
- "UPDATE mon_nodes SET online='%s', hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', lastseen='%s', " \
+ "UPDATE mon_nodes SET online='%d', hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', lastseen='%s', " \
"temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \
- "net_address='%s', net_ifname='%s', net_rssi='%d' WHERE uuid='%s'",
- node->online ?"Y":"N", node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, last,
+ "net_address='%s', net_ifname='%s', net_rssi='%d', up_interval='%d' WHERE uuid='%s'",
+ node->online ? 1:0, node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, last,
node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude,
- node->net_address, node->net_ifname, node->net_rssi, node->uuid);
+ node->net_address, node->net_ifname, node->net_rssi, node->interval, node->uuid);
bms_mysql_query(query);
free(query);
@@ -490,7 +557,7 @@
{
char *query = malloc(512);
- snprintf(query, 511, "UPDATE mon_nodes SET online='N' WHERE node='%s'", node);
+ snprintf(query, 511, "UPDATE mon_nodes SET online='0' WHERE node='%s'", node);
bms_mysql_query(query);
free(query);
}
@@ -502,7 +569,7 @@
char *query = malloc(2560);
snprintf(query, 2559,
- "INSERT INTO mon_fermenters SET uuid='%s', alias='%s', node='%s', online='%s', " \
+ "INSERT INTO mon_fermenters SET uuid='%s', alias='%s', node='%s', online='%d', " \
"beercode='%s', beername='%s', beeruuid='%s', " \
"air_address='%s', air_state='%s', air_temperature='%.3f', " \
"beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \
@@ -517,7 +584,7 @@
"profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \
"profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s', " \
"yeast_lo='%.1f', yeast_hi='%.1f', webcam_url='%s', webcam_light='%d'",
- fermenter->uuid, fermenter->alias, fermenter->node, fermenter->online ? "Y":"N",
+ fermenter->uuid, fermenter->alias, fermenter->node, fermenter->online ? 1:0,
fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "",
fermenter->beeruuid ? fermenter->beeruuid : "",
fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature,
@@ -548,7 +615,7 @@
char *query = malloc(2560);
snprintf(query, 2559,
- "UPDATE mon_fermenters SET online='%s', beercode='%s', beername='%s', beeruuid='%s', " \
+ "UPDATE mon_fermenters SET online='%d', beercode='%s', beername='%s', beeruuid='%s', " \
"air_address='%s', air_state='%s', air_temperature='%.3f', " \
"beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \
"chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \
@@ -562,7 +629,7 @@
"profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \
"profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s', " \
"yeast_lo='%.1f', yeast_hi='%.1f', webcam_url='%s', webcam_light='%d' WHERE uuid='%s'",
- fermenter->online ? "Y":"N", fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "",
+ fermenter->online ? 1:0, fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "",
fermenter->beeruuid ? fermenter->beeruuid : "",
fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature,
fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature,
@@ -591,9 +658,9 @@
char *query = malloc(512);
if (alias)
- snprintf(query, 511, "UPDATE mon_fermenters SET online='N' WHERE node='%s' and alias='%s'", node, alias);
+ snprintf(query, 511, "UPDATE mon_fermenters SET online='0' WHERE node='%s' and alias='%s'", node, alias);
else
- snprintf(query, 511, "UPDATE mon_fermenters SET online='N' WHERE node='%s'", node);
+ snprintf(query, 511, "UPDATE mon_fermenters SET online='0' WHERE node='%s'", node);
bms_mysql_query(query);
free(query);
@@ -655,3 +722,111 @@
}
+
+void ispindel_mysql_insert(sys_ispindel_list *ispindel)
+{
+ char *query = malloc(2560);
+
+ snprintf(query, 2559,
+ "INSERT INTO mon_ispindels SET uuid='%s', alias='%s', node='%s', online='%d', mode='%s', alarm='%d', " \
+ "angle='%.5f', temperature='%.4f', battery='%.6f', gravity='%.5f', up_interval='%d', og_gravity='0.0'",
+ ispindel->uuid, ispindel->alias, ispindel->node, ispindel->online ? 1:0, ispindel->mode, ispindel->alarm,
+ ispindel->angle, ispindel->temperature, ispindel->battery, ispindel->gravity, ispindel->interval);
+
+ if (bms_mysql_query(query) == 0) {
+ syslog(LOG_NOTICE, "MySQL: insert new ispindel %s", ispindel->node);
+ }
+ free(query);
+}
+
+
+
+void ispindel_mysql_update(sys_ispindel_list *ispindel)
+{
+ char *query = malloc(2560);
+
+ snprintf(query, 2559,
+ "UPDATE mon_ispindels SET online='%d', mode='%s', alarm='%d', " \
+ "angle='%.5f', temperature='%.4f', battery='%.6f', gravity='%.5f', up_interval='%d', og_gravity=GREATEST(og_gravity, '%.5f') WHERE uuid='%s'",
+ ispindel->online ? 1:0, ispindel->mode, ispindel->alarm,
+ ispindel->angle, ispindel->temperature, ispindel->battery, ispindel->gravity, ispindel->interval, ispindel->gravity, ispindel->uuid);
+
+ bms_mysql_query(query);
+ free(query);
+}
+
+
+
+void ispindel_mysql_death(char *node)
+{
+ char *query = malloc(512);
+
+ snprintf(query, 511, "UPDATE mon_ispindels SET online='0' WHERE node='%s'", node);
+ bms_mysql_query(query);
+ free(query);
+}
+
+
+
+/*
+ * Check using a new MySQL connection because we are running from another thread.
+ */
+void ispindel_mysql_check(void)
+{
+ sys_ispindel_list *tmpp;
+ MYSQL *con2 = NULL;
+ MYSQL_RES *res_set2;
+ MYSQL_ROW row2;
+
+ if (ispindels == NULL)
+ return;
+
+ con2 = mysql_init(NULL);
+ if (con2 == NULL) {
+ syslog(LOG_NOTICE, "MySQL: mysql_init() failed");
+ return;
+ }
+
+ if (mysql_real_connect(con2, Config.mysql_host, Config.mysql_user, Config.mysql_pass, Config.mysql_database, Config.mysql_port, NULL, 0) == NULL) {
+ syslog(LOG_NOTICE, "MySQL: mysql_real_connect() %s", mysql_error(con2));
+ return;
+ }
+
+ if (mysql_query(con2, "SELECT uuid,alias,beercode,beername,beeruuid,mode FROM mon_ispindels;")) {
+ syslog(LOG_NOTICE, "MySQL: SELECT uuid,alias,beercode,beername,beeruuid,mode FROM mon_ispindels error %u (%s))", mysql_errno(con2), mysql_error(con2));
+ } else {
+ res_set2 = mysql_store_result(con2);
+ if (res_set2 == NULL) {
+ syslog(LOG_NOTICE, "MySQL: mysq_store_result error %u (%s))", mysql_errno(con2), mysql_error(con2));
+ } else {
+ while ((row2 = mysql_fetch_row(res_set2)) != NULL) {
+ for (tmpp = ispindels; tmpp; tmpp = tmpp->next) {
+ if (strcmp(tmpp->uuid, row2[0]) == 0) {
+ if (strcmp(tmpp->beercode, row2[2]) || strcmp(tmpp->beername, row2[3]) || strcmp(tmpp->beeruuid, row2[4])) {
+ syslog(LOG_NOTICE, "ispindel `%s` change beer to `%s %s`", row2[1], row2[2], row2[3]);
+ if (tmpp->beercode)
+ free(tmpp->beercode);
+ tmpp->beercode = xstrcpy(row2[2]);
+ if (tmpp->beername)
+ free(tmpp->beername);
+ tmpp->beername = xstrcpy(row2[3]);
+ if (tmpp->beeruuid)
+ free(tmpp->beeruuid);
+ tmpp->beeruuid = xstrcpy(row2[4]);
+ }
+ if (strcmp(tmpp->mode, row2[5])) {
+ syslog(LOG_NOTICE, "ispindel `%s` change mode `%s`", row2[1], row2[5]);
+ if (tmpp->mode)
+ free(tmpp->mode);
+ tmpp->mode = xstrcpy(row2[5]);
+ }
+ }
+ }
+ }
+ mysql_free_result(res_set2);
+ }
+ }
+
+ mysql_close(con2);
+}
+