tip/bmsd/mysql.c

/**
 * @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);
}