Mercurial > bms / file revision

bmsd/mysql.c@92e1e8f175a2

bmsd/mysql.c

Tue, 31 Aug 2021 20:48:37 +0200

author
Michiel Broek <mbroek@mbse.eu>
date
Tue, 31 Aug 2021 20:48:37 +0200
changeset 774
92e1e8f175a2
parent 747
b6fbe6821468
child 799
cf145b35d65f
permissions
-rw-r--r--

Split batch, adjust mash step volume. In the duplicated log_brew handle the missing values. In save product, round the mash step sg to 4 decimals. In prod_edit, ingredients are stored as strings, not arrays. This triggered a memory corruption that only happened in rare circumstances. Don't fix mash step fields in the javascript, it is already done during load from the database. Calculation of the mash volume is rounded to 6 decimals. Enter mash step Brix/Plato value, the SG result is rounded to 4 decimals.

/**
 * @file mysql.c
 * @brief MySQL/MariaDB access.
 * @author Michiel Broek <mbroek at mbse dot eu>
 *
 * Copyright (C) 2018-2021
 *
 * 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;

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;
    }

    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]);

                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);
        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;
    }

    /* Any error execpt server gone away */
    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) {
        syslog(LOG_NOTICE, "MySQL: mysql_real_connect() %s", mysql_error(con));
        return 2;
    }
    syslog(LOG_NOTICE, "MySQL: reconnected.");
    rc = mysql_query(con, query);
    if (rc) {
        syslog(LOG_NOTICE, "MySQL: error %u (%s)", mysql_errno(con), mysql_error(con));
        syslog(LOG_NOTICE, query);
    }
    return rc;
}



void bms_mysql_ping(void)
{
}



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);

    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->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);
}



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='%.6f', temperature='%.4f', battery='%.6f', gravity='%.6f', 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', alias='%s', alarm='%d', " \
	"angle='%.6f', temperature='%.4f', battery='%.6f', gravity='%.6f', up_interval='%d', og_gravity=GREATEST(og_gravity, '%.6f') 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->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);
}

Mercurial Repository: bms

mercurial