diff -r 9d1aa6f3a4da -r 4d01937ae7af bmsd/mysql.c --- 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 * - * 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); +} +