Wed, 18 Oct 2023 13:03:10 +0200
Moved init script to /etc/rc.d and must now be installed in /etc/rc.d/rc.local
/** * @file mysql.c * @brief MySQL/MariaDB access. * @author Michiel Broek <mbroek at mbse dot eu> * * Copyright (C) 2018-2023 * * This file is part of the bms (Brewery Management System) * * This is free software; you can redistribute it and/or modify it * under the terms of the GNU General Public License as published by the * Free Software Foundation; either version 2, or (at your option) any * later version. * * bms is distributed in the hope that it will be useful, but * WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * General Public License for more details. * * You should have received a copy of the GNU General Public License * along with ThermFerm; see the file COPYING. If not, write to the Free * Software Foundation, 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. */ #include "bms.h" #include "xutil.h" #include "mysql.h" #include "nodes.h" MYSQL *con = NULL; MYSQL_RES *res_set; MYSQL_ROW row; my_bool reconnect = 1; extern sys_config Config; extern sys_node_list *nodes; extern sys_fermenter_list *fermenters; extern sys_co2meter_list *co2meters; extern sys_ispindel_list *ispindels; extern int debug; time_t datetime_to_time_t(char *dt_string) { struct tm tm; memset(&tm, 0, sizeof(struct tm)); tm.tm_year = atoi(strtok(dt_string, "-")) - 1900; tm.tm_mon = atoi(strtok(NULL, "-")) - 1; tm.tm_mday = atoi(strtok(NULL, " ")); tm.tm_hour = atoi(strtok(NULL, ":")); tm.tm_min = atoi(strtok(NULL, ":")); tm.tm_sec = atoi(strtok(NULL, "\0")); tm.tm_isdst = 1; return mktime(&tm); } int bms_mysql_init(void) { sys_node_list *node, *tmpp; sys_fermenter_list *fermenter, *tmpf; sys_co2meter_list *co2meter, *tmpc; sys_ispindel_list *ispindel, *tmpi; int icnt = 0, ccnt = 0, ncnt = 0, fcnt = 0; con = mysql_init(NULL); if (con == NULL) { syslog(LOG_NOTICE, "MySQL: mysql_init() failed"); return 1; } if (mysql_real_connect(con, 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(con)); return 2; } mysql_optionsv(con, MYSQL_OPT_RECONNECT, (void *)&reconnect); syslog(LOG_NOTICE, "MySQL: connected to %s:%d database %s", Config.mysql_host, Config.mysql_port, Config.mysql_database); syslog(LOG_NOTICE, "MySQL: %s server info: %s", mysql_get_host_info(con), mysql_get_server_info(con)); if (debug) fprintf(stdout, "MySQL: connected\n"); /* * Restore nodes from the database */ if (mysql_query(con, "SELECT * FROM mon_nodes ORDER BY node")) { syslog(LOG_NOTICE, "MySQL: SELECT * FROM mon_nodes 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 { // Process results while ((row = mysql_fetch_row(res_set)) != NULL) { node = (sys_node_list *)malloc(sizeof(sys_node_list)); memset(node, 0, sizeof(sys_node_list)); node->next = NULL; node->uuid = xstrcpy(row[1]); node->node = xstrcpy(row[2]); node->online = atoi(row[3]); node->group_id = xstrcpy(row[4]); node->hardwaremake = xstrcpy(row[5]); node->hardwaremodel = xstrcpy(row[6]); node->os = xstrcpy(row[7]); node->os_version = xstrcpy(row[8]); node->firmware = xstrcpy(row[9]); node->firstseen = datetime_to_time_t(row[10]); node->lastseen = datetime_to_time_t(row[11]); node->temperature = atof(row[12]); node->humidity = atof(row[13]); node->barometer = atof(row[14]); node->gps_latitude = atof(row[15]); node->gps_longitude = atof(row[16]); node->gps_altitude = atof(row[17]); node->net_address = xstrcpy(row[18]); node->net_ifname = xstrcpy(row[19]); node->net_rssi = atoi(row[20]); node->interval = atoi(row[21]); node->net_ssid = xstrcpy(row[22]); if (nodes == NULL) { nodes = node; } else { for (tmpp = nodes; tmpp; tmpp = tmpp->next) { if (tmpp->next == NULL) { tmpp->next = node; break; } } } ncnt++; } mysql_free_result(res_set); } } if (mysql_query(con, "SELECT * FROM mon_fermenters ORDER BY node,alias")) { syslog(LOG_NOTICE, "MySQL: SELECT * FROM mon_fermenters 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) { fermenter = (sys_fermenter_list *)malloc(sizeof(sys_fermenter_list)); memset(fermenter, 0, sizeof(sys_fermenter_list)); fermenter->next = NULL; fermenter->uuid = xstrcpy(row[1]); fermenter->alias = xstrcpy(row[2]); fermenter->node = xstrcpy(row[3]); fermenter->online = atoi(row[4]); fermenter->beercode = xstrcpy(row[5]); fermenter->beername = xstrcpy(row[6]); fermenter->beeruuid = xstrcpy(row[44]); if (strlen(row[7])) { fermenter->air_address = xstrcpy(row[7]); fermenter->air_state = xstrcpy(row[8]); fermenter->air_temperature = atof(row[9]); } if (strlen(row[10])) { fermenter->beer_address = xstrcpy(row[10]); fermenter->beer_state = xstrcpy(row[11]); fermenter->beer_temperature = atof(row[12]); } if (strlen(row[13])) { fermenter->chiller_address = xstrcpy(row[13]); fermenter->chiller_state = xstrcpy(row[14]); fermenter->chiller_temperature = atof(row[15]); } if (strlen(row[16])) { fermenter->heater_address = xstrcpy(row[16]); fermenter->heater_state = atoi(row[17]); fermenter->heater_usage = atoi(row[18]); } if (strlen(row[19])) { fermenter->cooler_address = xstrcpy(row[19]); fermenter->cooler_state = atoi(row[20]); fermenter->cooler_usage = atoi(row[21]); } if (strlen(row[22])) { fermenter->fan_address = xstrcpy(row[22]); fermenter->fan_state = atoi(row[23]); fermenter->fan_usage = atoi(row[24]); } if (strlen(row[25])) { fermenter->light_address = xstrcpy(row[25]); fermenter->light_state = atoi(row[26]); fermenter->light_usage = atoi(row[27]); } if (strlen(row[28])) { fermenter->door_address = xstrcpy(row[28]); fermenter->door_state = atoi(row[29]); } if (strlen(row[30])) { fermenter->psu_address = xstrcpy(row[30]); fermenter->psu_state = atoi(row[31]); } fermenter->mode = xstrcpy(row[32]); fermenter->alarm = atoi(row[33]); fermenter->setpoint_high = atof(row[34]); fermenter->setpoint_low = atof(row[35]); if (strlen(row[36])) { fermenter->profile_uuid = xstrcpy(row[36]); fermenter->profile_name = xstrcpy(row[37]); fermenter->profile_state = xstrcpy(row[38]); fermenter->profile_percent = atoi(row[39]); fermenter->profile_inittemp_high = atof(row[40]); fermenter->profile_inittemp_low = atof(row[41]); fermenter->profile_steps = xstrcpy(row[42]); } fermenter->stage = xstrcpy(row[43]); fermenter->yeast_lo = atof(row[45]); fermenter->yeast_hi = atof(row[46]); fermenter->webcam_url = xstrcpy(row[47]); fermenter->webcam_light = atoi(row[48]); if (fermenters == NULL) { fermenters = fermenter; } else { for (tmpf = fermenters; tmpf; tmpf = tmpf->next) { if (tmpf->next == NULL) { tmpf->next = fermenter; break; } } } fcnt++; } mysql_free_result(res_set); } } if (mysql_query(con, "SELECT * FROM mon_co2meters ORDER BY node,alias")) { syslog(LOG_NOTICE, "MySQL: SELECT * FROM mon_co2meters 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) { co2meter = (sys_co2meter_list *)malloc(sizeof(sys_co2meter_list)); memset(co2meter, 0, sizeof(sys_co2meter_list)); co2meter->next = NULL; co2meter->uuid = xstrcpy(row[1]); co2meter->alias = xstrcpy(row[2]); co2meter->node = xstrcpy(row[3]); co2meter->online = atoi(row[4]); co2meter->beercode = xstrcpy(row[5]); co2meter->beername = xstrcpy(row[6]); co2meter->beeruuid = xstrcpy(row[7]); co2meter->mode = xstrcpy(row[8]); co2meter->temperature_state = xstrcpy(row[9]); co2meter->temperature_address = xstrcpy(row[10]); co2meter->temperature = atof(row[11]); co2meter->pressure_state = xstrcpy(row[12]); co2meter->pressure_channel = atoi(row[13]); co2meter->pressure_voltage = atof(row[14]); co2meter->pressure_zero = atof(row[15]); co2meter->pressure_bar = atof(row[16]); if (co2meters == NULL) { co2meters = co2meter; } else { for (tmpc = co2meters; tmpc; tmpc = tmpc->next) { if (tmpc->next == NULL) { tmpc->next = co2meter; break; } } } ccnt++; } mysql_free_result(res_set); } } if (mysql_query(con, "SELECT * FROM mon_ispindels ORDER BY node,alias")) { 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]); ispindel->og_gravity = atof(row[15]); ispindel->yeast_lo = atof(row[16]); ispindel->yeast_hi = atof(row[17]); ispindel->calibrate = xstrcpy(row[18]); ispindel->lastseen = datetime_to_time_t(row[19]); 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; } 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; mysql_close(con); syslog(LOG_NOTICE, "MySQL: disconnected"); 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); if (tmpi->calibrate) free(tmpi->calibrate); free(tmpi); } for (tmpp = co2meters; tmpp; tmpp = oldtmpp) { oldtmpp = tmpp->next; if (tmpp->uuid) free(tmpp->uuid); if (tmpp->alias) free(tmpp->alias); if (tmpp->node) free(tmpp->node); if (tmpp->beercode) free(tmpp->beercode); if (tmpp->beername) free(tmpp->beername); if (tmpp->beeruuid) free(tmpp->beeruuid); if (tmpp->temperature_address) free(tmpp->temperature_address); if (tmpp->temperature_state) free(tmpp->temperature_state); if (tmpp->pressure_state) free(tmpp->pressure_state); free(tmpp); } for (tmpf = fermenters; tmpf; tmpf = oldtmpf) { oldtmpf = tmpf->next; if (tmpf->uuid) free(tmpf->uuid); if (tmpf->alias) free(tmpf->alias); if (tmpf->node) free(tmpf->node); if (tmpf->beercode) free(tmpf->beercode); if (tmpf->beername) free(tmpf->beername); if (tmpf->beeruuid) free(tmpf->beeruuid); if (tmpf->air_address) free(tmpf->air_address); if (tmpf->air_state) free(tmpf->air_state); if (tmpf->beer_address) free(tmpf->beer_address); if (tmpf->beer_state) free(tmpf->beer_state); if (tmpf->chiller_address) free(tmpf->chiller_address); if (tmpf->chiller_state) free(tmpf->chiller_state); if (tmpf->heater_address) free(tmpf->heater_address); if (tmpf->cooler_address) free(tmpf->cooler_address); if (tmpf->fan_address) free(tmpf->fan_address); if (tmpf->light_address) free(tmpf->light_address); if (tmpf->door_address) free(tmpf->door_address); if (tmpf->psu_address) free(tmpf->psu_address); if (tmpf->mode) free(tmpf->mode); if (tmpf->stage) free(tmpf->stage); if (tmpf->profile_uuid) free(tmpf->profile_uuid); if (tmpf->profile_name) free(tmpf->profile_name); if (tmpf->profile_state) free(tmpf->profile_state); if (tmpf->profile_steps) free(tmpf->profile_steps); if (tmpf->webcam_url) free(tmpf->webcam_url); free(tmpf); } for (tmpn = nodes; tmpn; tmpn = oldtmpn) { oldtmpn = tmpn->next; if (tmpn->uuid) free(tmpn->uuid); if (tmpn->node) free(tmpn->node); if (tmpn->group_id) free(tmpn->group_id); if (tmpn->hardwaremake) free(tmpn->hardwaremake); if (tmpn->hardwaremodel) free(tmpn->hardwaremodel); if (tmpn->os) free(tmpn->os); if (tmpn->os_version) free(tmpn->os_version); if (tmpn->firmware) free(tmpn->firmware); if (tmpn->net_address) free(tmpn->net_address); if (tmpn->net_ifname) free(tmpn->net_ifname); if (tmpn->net_ssid) free(tmpn->net_ssid); free(tmpn); } } 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)", err, mysql_error(con)); syslog(LOG_NOTICE, query); } else { return 0; } return rc; } void node_mysql_insert(sys_node_list *node) { char *query = malloc(1024), first[73], last[73]; struct tm *mytime; mytime = localtime(&node->firstseen); 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, 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='%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', up_interval='%d', net_ssid='%s'", 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->interval, (node->net_ssid) ? node->net_ssid:""); if (bms_mysql_query(query) == 0) { syslog(LOG_NOTICE, "MySQL: insert new node %s", node->node); } free(query); } void node_mysql_update(sys_node_list *node) { char *query = malloc(1024), last[65]; struct tm *mytime; mytime = localtime(&node->lastseen); 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='%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', up_interval='%d', net_ssid='%s' 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->interval, (node->net_ssid) ? node->net_ssid:"", node->uuid); bms_mysql_query(query); free(query); } void node_mysql_death(char *node) { char *query = malloc(512); snprintf(query, 511, "UPDATE mon_nodes SET online='0' WHERE node='%s'", node); bms_mysql_query(query); free(query); } void fermenter_mysql_insert(sys_fermenter_list *fermenter) { char *query = malloc(2560); snprintf(query, 2559, "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', " \ "chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \ "heater_address='%s', heater_state='%d', heater_usage='%lu', " \ "cooler_address='%s', cooler_state='%d', cooler_usage='%lu', " \ "fan_address='%s', fan_state='%d', fan_usage='%lu', " \ "light_address='%s', light_state='%d', light_usage='%lu', " \ "door_address='%s', door_state='%d', " \ "psu_address='%s', psu_state='%d', " \ "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \ "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 ? 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 : "MISSING", fermenter->air_temperature, fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "MISSING", fermenter->beer_temperature, fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "MISSING", fermenter->chiller_temperature, fermenter->heater_address ? fermenter->heater_address : "", fermenter->heater_state, fermenter->heater_usage, fermenter->cooler_address ? fermenter->cooler_address : "", fermenter->cooler_state, fermenter->cooler_usage, fermenter->fan_address ? fermenter->fan_address : "", fermenter->fan_state, fermenter->fan_usage, fermenter->light_address ? fermenter->light_address : "", fermenter->light_state, fermenter->light_usage, fermenter->door_address ? fermenter->door_address : "", fermenter->door_state, fermenter->psu_address ? fermenter->psu_address : "", fermenter->psu_state, fermenter->mode, fermenter->alarm, fermenter->setpoint_high, fermenter->setpoint_low, fermenter->profile_uuid ? fermenter->profile_uuid : "", fermenter->profile_name ? fermenter->profile_name : "", fermenter->profile_state ? fermenter->profile_state : "OFF", fermenter->profile_percent, fermenter->profile_inittemp_high, fermenter->profile_inittemp_low, fermenter->profile_steps ? fermenter->profile_steps : "", fermenter->stage, fermenter->yeast_lo, fermenter->yeast_hi, fermenter->webcam_url ? fermenter->webcam_url : "", fermenter->webcam_light); if (bms_mysql_query(query) == 0) { syslog(LOG_NOTICE, "MySQL: insert new fermenter %s/%s", fermenter->node, fermenter->alias); } free(query); } void fermenter_mysql_update(sys_fermenter_list *fermenter) { char *query = malloc(2560); snprintf(query, 2559, "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', " \ "heater_address='%s', heater_state='%d', heater_usage='%lu', " \ "cooler_address='%s', cooler_state='%d', cooler_usage='%lu', " \ "fan_address='%s', fan_state='%d', fan_usage='%lu', " \ "light_address='%s', light_state='%d', light_usage='%lu', " \ "door_address='%s', door_state='%d', " \ "psu_address='%s', psu_state='%d', " \ "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \ "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 ? 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 : "MISSING", fermenter->air_temperature, fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "MISSING", fermenter->beer_temperature, fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "MISSING", fermenter->chiller_temperature, fermenter->heater_address ? fermenter->heater_address : "", fermenter->heater_state, fermenter->heater_usage, fermenter->cooler_address ? fermenter->cooler_address : "", fermenter->cooler_state, fermenter->cooler_usage, fermenter->fan_address ? fermenter->fan_address : "", fermenter->fan_state, fermenter->fan_usage, fermenter->light_address ? fermenter->light_address : "", fermenter->light_state, fermenter->light_usage, fermenter->door_address ? fermenter->door_address : "", fermenter->door_state, fermenter->psu_address ? fermenter->psu_address : "", fermenter->psu_state, fermenter->mode, fermenter->alarm, fermenter->setpoint_high, fermenter->setpoint_low, fermenter->profile_uuid ? fermenter->profile_uuid : "", fermenter->profile_name ? fermenter->profile_name : "", fermenter->profile_state ? fermenter->profile_state : "OFF", fermenter->profile_percent, fermenter->profile_inittemp_high, fermenter->profile_inittemp_low, fermenter->profile_steps ? fermenter->profile_steps : "", fermenter->stage, fermenter->yeast_lo, fermenter->yeast_hi, fermenter->webcam_url ? fermenter->webcam_url : "", fermenter->webcam_light, fermenter->uuid); bms_mysql_query(query); free(query); } void fermenter_mysql_death(char *node, char *alias) { char *query = malloc(512); if (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='0' WHERE node='%s'", node); bms_mysql_query(query); free(query); } void co2meter_mysql_insert(sys_co2meter_list *co2meter) { char *query = malloc(2560); snprintf(query, 2559, "INSERT INTO mon_co2meters SET uuid='%s', alias='%s', node='%s', online='%d', mode='%s', alarm='%d', " \ "temperature_address='%s', temperature_state='%s', temperature='%.3f', " \ "pressure_state='%s', pressure_channel='%d', pressure_voltage='%.3f', pressure_zero='%.3f', pressure_bar='%.3f'", co2meter->uuid, co2meter->alias, co2meter->node, co2meter->online ? 1:0, co2meter->mode, co2meter->alarm, co2meter->temperature_address, co2meter->temperature_state, co2meter->temperature, co2meter->pressure_state, co2meter->pressure_channel, co2meter->pressure_voltage, co2meter->pressure_zero, co2meter->pressure_bar); if (bms_mysql_query(query) == 0) { syslog(LOG_NOTICE, "MySQL: insert new co2meter %s/%s", co2meter->node, co2meter->alias); } free(query); } void co2meter_mysql_update(sys_co2meter_list *co2meter) { char *query = malloc(2560); snprintf(query, 2559, "UPDATE mon_co2meters SET online='%d', mode='%s', alarm='%d', " \ "temperature_address='%s', temperature_state='%s', temperature='%.3f', " \ "pressure_state='%s', pressure_channel='%d', pressure_voltage='%.3f', pressure_zero='%.3f', pressure_bar='%.3f' " \ "WHERE uuid='%s'", co2meter->online ? 1:0, co2meter->mode, co2meter->alarm, co2meter->temperature_address, co2meter->temperature_state, co2meter->temperature, co2meter->pressure_state, co2meter->pressure_channel, co2meter->pressure_voltage, co2meter->pressure_zero, co2meter->pressure_bar, co2meter->uuid); bms_mysql_query(query); free(query); } void co2meter_mysql_death(char *node, char *alias) { char *query = malloc(512); if (alias) snprintf(query, 511, "UPDATE mon_co2meters SET online='0' WHERE node='%s' and alias='%s'", node, alias); else snprintf(query, 511, "UPDATE mon_co2meters SET online='0' WHERE node='%s'", node); bms_mysql_query(query); free(query); } /* * Update the gravity calculated from the calibrate data. */ double ispindel_mysql_plato(char *uuid, double angle) { char *query = malloc(1024), *cal; struct json_object *jobj, *metric, *val; double poly[4], plato = 0; snprintf(query, 1023, "SELECT uuid,calibrate FROM mon_ispindels WHERE uuid='%s'", uuid); if (mysql_query(con, query)) { syslog(LOG_NOTICE, "MySQL: %s error %u (%s))", query, mysql_errno(con), mysql_error(con)); free(query); return 0.0; } 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)); free(query); return 0.0; } free(query); if ((row = mysql_fetch_row(res_set)) != NULL) { cal = xstrcpy(row[1]); jobj = json_tokener_parse(cal); if (json_object_object_get_ex(jobj, "polyData", &metric)) { int arraylen = json_object_array_length(metric); for (int i = 0; i < arraylen; i++) { val = json_object_array_get_idx(metric, i); poly[i] = json_object_get_double(val); } plato = (poly[0] * pow(angle, 3)) + (poly[1] * pow(angle, 2)) + (poly[2] * angle) + poly[3]; } free(cal); } return plato; } void ispindel_mysql_insert(sys_ispindel_list *ispindel) { char *query = malloc(2560), last[73]; struct tm *mytime; mytime = localtime(&ispindel->lastseen); 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, 2559, "INSERT INTO mon_ispindels SET uuid='%s', alias='%s', node='%s', online='%d', mode='%s', alarm='%d', " \ "angle='%.6f', temperature='%.4f', battery='%.6f', gravity='%.6f', up_interval='%d', og_gravity='0.0', " \ "yeast_lo='%.1f', yeast_hi='%.1f', lastseen='%s'", ispindel->uuid, ispindel->alias, ispindel->node, ispindel->online ? 1:0, ispindel->mode, ispindel->alarm, ispindel->angle, ispindel->temperature, ispindel->battery, ispindel->gravity, ispindel->interval, ispindel->yeast_lo, ispindel->yeast_hi, last); 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), last[73]; struct tm *mytime; mytime = localtime(&ispindel->lastseen); 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); /* * Do not update the calibrate field with regular updates. */ snprintf(query, 2559, "UPDATE mon_ispindels SET online='%d', mode='%s', alias='%s', alarm='%d', " \ "angle='%.6f', temperature='%.4f', battery='%.6f', gravity='%.6f', up_interval='%d', og_gravity=GREATEST(og_gravity, '%.6f'), " \ "yeast_lo='%.1f', yeast_hi='%.1f', lastseen='%s' WHERE uuid='%s'", ispindel->online ? 1:0, ispindel->mode, ispindel->alias, ispindel->alarm, ispindel->angle, ispindel->temperature, ispindel->battery, ispindel->gravity, ispindel->interval, ispindel->gravity, ispindel->yeast_lo, ispindel->yeast_hi, last, 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); }