bmsd/mysql.c

/**
 * @file mysql.c
 * @brief MySQL/MariaDB access.
 * @author Michiel Broek <mbroek at mbse dot eu>
 *
 * Copyright (C) 2018-2019
 *
 * 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 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;
    int			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        = false;	// Will be set using MQTT
		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]);

		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   = false;	// Will be set later
		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);
	}
    }

    syslog(LOG_NOTICE, "MySQL: loaded %d nodes, %d fermenters", ncnt, fcnt);
    return 0;
}


void bms_mysql_end(void)
{
    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 (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);

    if (rc) {
        syslog(LOG_NOTICE, "MySQL: error %u (%s)", mysql_errno(con), mysql_error(con));
        syslog(LOG_NOTICE, query);
    } else {
	return 0;
    }

    /* Any error execpt server gone away */
    if (rc != 2006)
	return rc;

    /* 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[21], last[21];
    struct tm	*mytime;

    mytime = localtime(&node->firstseen);
    snprintf(first, 20, "%04d-%02d-%02d %02d:%02d:%02d", 
		    mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);
    mytime = localtime(&node->lastseen);
    snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d",
		mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);

    snprintf(query, 1023,
	"INSERT INTO  mon_nodes SET uuid='%s', node='%s', online='%s', group_id='%s', " \
        "hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', firstseen='%s', lastseen='%s', " \
	"temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \
	"net_address='%s', net_ifname='%s', net_rssi='%d'",
	node->uuid, node->node, node->online ?"Y":"N", node->group_id, 
	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);

    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[21];
    struct tm   *mytime;

    mytime = localtime(&node->lastseen);
    snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d",
    	mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);

    snprintf(query, 1023,
	"UPDATE mon_nodes SET online='%s', hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', lastseen='%s', " \
	"temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \
	"net_address='%s', net_ifname='%s', net_rssi='%d' WHERE uuid='%s'",
	node->online ?"Y":"N", node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, last,
	node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude, 
	node->net_address, node->net_ifname, node->net_rssi, node->uuid);

    bms_mysql_query(query);
    free(query);
}


void node_mysql_death(char *node)
{
    char        *query = malloc(512);

    snprintf(query, 511, "UPDATE mon_nodes SET online='N' 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='%s', " \
	"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 ? "Y":"N",
	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='%s', 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 ? "Y":"N", 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='N' WHERE node='%s' and alias='%s'", node, alias);
    else
	snprintf(query, 511, "UPDATE mon_fermenters SET online='N' WHERE node='%s'", node);

    bms_mysql_query(query);
    free(query);
}