Mercurial > bms / file revision

bmsd/mysql.c@48f8f3fce7c0

bmsd/mysql.c

Mon, 18 May 2020 11:00:59 +0200

author
Michiel Broek <mbroek@mbse.eu>
date
Mon, 18 May 2020 11:00:59 +0200
changeset 679
48f8f3fce7c0
parent 628
a42166cbb19a
child 680
0bb48333d133
permissions
-rw-r--r--

Added reconnecting-websocket.js to automatic reconnect the websocket if the connection is lost. Usefull for mobile devices that go to sleep after a while. Changed mon_fermenters to use websockets instead of polling. Fixed wrong temperature color ranges on the fermenter monior. Increased the websocket receive buffer to 2048. In cannot overflow, but larger messages are chunked and the application does not handle these split messages. Needs termferm 0.9.9 or newer.

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

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



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);
	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'",
	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);

    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' 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->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 : "", fermenter->air_temperature,
	fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature,
     	fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", 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 : "", 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 : "", fermenter->air_temperature,
	fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature,
	fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", 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 : "", 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='%.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);
}

Mercurial Repository: bms

mercurial