Mercurial > bms / file comparison

comparison: bmsd/mysql.c

bmsd/mysql.c

branch
stable
changeset 665
4d01937ae7af
parent 628
a42166cbb19a
child 680
0bb48333d133
equal deleted inserted replaced
521:9d1aa6f3a4da 665:4d01937ae7af
1 /** 1 /**
2 * @file mysql.c 2 * @file mysql.c
3 * @brief MySQL/MariaDB access. 3 * @brief MySQL/MariaDB access.
4 * @author Michiel Broek <mbroek at mbse dot eu> 4 * @author Michiel Broek <mbroek at mbse dot eu>
5 * 5 *
6 * Copyright (C) 2018-2019 6 * Copyright (C) 2018-2020
7 * 7 *
8 * This file is part of the bms (Brewery Management System) 8 * This file is part of the bms (Brewery Management System)
9 * 9 *
10 * This is free software; you can redistribute it and/or modify it 10 * This is free software; you can redistribute it and/or modify it
11 * under the terms of the GNU General Public License as published by the 11 * under the terms of the GNU General Public License as published by the
34 34
35 extern sys_config Config; 35 extern sys_config Config;
36 extern sys_node_list *nodes; 36 extern sys_node_list *nodes;
37 extern sys_fermenter_list *fermenters; 37 extern sys_fermenter_list *fermenters;
38 extern sys_co2meter_list *co2meters; 38 extern sys_co2meter_list *co2meters;
39 extern sys_ispindel_list *ispindels;
39 extern int debug; 40 extern int debug;
40 41
41 42
42 time_t datetime_to_time_t(char *dt_string) 43 time_t datetime_to_time_t(char *dt_string)
43 { 44 {
60 int bms_mysql_init(void) 61 int bms_mysql_init(void)
61 { 62 {
62 sys_node_list *node, *tmpp; 63 sys_node_list *node, *tmpp;
63 sys_fermenter_list *fermenter, *tmpf; 64 sys_fermenter_list *fermenter, *tmpf;
64 sys_co2meter_list *co2meter, *tmpc; 65 sys_co2meter_list *co2meter, *tmpc;
65 int ccnt = 0, ncnt = 0, fcnt = 0; 66 sys_ispindel_list *ispindel, *tmpi;
67 int icnt = 0, ccnt = 0, ncnt = 0, fcnt = 0;
66 68
67 con = mysql_init(NULL); 69 con = mysql_init(NULL);
68 if (con == NULL) { 70 if (con == NULL) {
69 syslog(LOG_NOTICE, "MySQL: mysql_init() failed"); 71 syslog(LOG_NOTICE, "MySQL: mysql_init() failed");
70 return 1; 72 return 1;
95 node = (sys_node_list *)malloc(sizeof(sys_node_list)); 97 node = (sys_node_list *)malloc(sizeof(sys_node_list));
96 memset(node, 0, sizeof(sys_node_list)); 98 memset(node, 0, sizeof(sys_node_list));
97 node->next = NULL; 99 node->next = NULL;
98 node->uuid = xstrcpy(row[1]); 100 node->uuid = xstrcpy(row[1]);
99 node->node = xstrcpy(row[2]); 101 node->node = xstrcpy(row[2]);
100 node->online = false; // Will be set using MQTT 102 node->online = atoi(row[3]);
101 node->group_id = xstrcpy(row[4]); 103 node->group_id = xstrcpy(row[4]);
102 node->hardwaremake = xstrcpy(row[5]); 104 node->hardwaremake = xstrcpy(row[5]);
103 node->hardwaremodel = xstrcpy(row[6]); 105 node->hardwaremodel = xstrcpy(row[6]);
104 node->os = xstrcpy(row[7]); 106 node->os = xstrcpy(row[7]);
105 node->os_version = xstrcpy(row[8]); 107 node->os_version = xstrcpy(row[8]);
113 node->gps_longitude = atof(row[16]); 115 node->gps_longitude = atof(row[16]);
114 node->gps_altitude = atof(row[17]); 116 node->gps_altitude = atof(row[17]);
115 node->net_address = xstrcpy(row[18]); 117 node->net_address = xstrcpy(row[18]);
116 node->net_ifname = xstrcpy(row[19]); 118 node->net_ifname = xstrcpy(row[19]);
117 node->net_rssi = atoi(row[20]); 119 node->net_rssi = atoi(row[20]);
120 node->interval = atoi(row[21]);
118 121
119 if (nodes == NULL) { 122 if (nodes == NULL) {
120 nodes = node; 123 nodes = node;
121 } else { 124 } else {
122 for (tmpp = nodes; tmpp; tmpp = tmpp->next) { 125 for (tmpp = nodes; tmpp; tmpp = tmpp->next) {
144 memset(fermenter, 0, sizeof(sys_fermenter_list)); 147 memset(fermenter, 0, sizeof(sys_fermenter_list));
145 fermenter->next = NULL; 148 fermenter->next = NULL;
146 fermenter->uuid = xstrcpy(row[1]); 149 fermenter->uuid = xstrcpy(row[1]);
147 fermenter->alias = xstrcpy(row[2]); 150 fermenter->alias = xstrcpy(row[2]);
148 fermenter->node = xstrcpy(row[3]); 151 fermenter->node = xstrcpy(row[3]);
149 fermenter->online = false; // Will be set later 152 fermenter->online = atoi(row[4]);
150 fermenter->beercode = xstrcpy(row[5]); 153 fermenter->beercode = xstrcpy(row[5]);
151 fermenter->beername = xstrcpy(row[6]); 154 fermenter->beername = xstrcpy(row[6]);
152 fermenter->beeruuid = xstrcpy(row[44]); 155 fermenter->beeruuid = xstrcpy(row[44]);
153 if (strlen(row[7])) { 156 if (strlen(row[7])) {
154 fermenter->air_address = xstrcpy(row[7]); 157 fermenter->air_address = xstrcpy(row[7]);
240 memset(co2meter, 0, sizeof(sys_co2meter_list)); 243 memset(co2meter, 0, sizeof(sys_co2meter_list));
241 co2meter->next = NULL; 244 co2meter->next = NULL;
242 co2meter->uuid = xstrcpy(row[1]); 245 co2meter->uuid = xstrcpy(row[1]);
243 co2meter->alias = xstrcpy(row[2]); 246 co2meter->alias = xstrcpy(row[2]);
244 co2meter->node = xstrcpy(row[3]); 247 co2meter->node = xstrcpy(row[3]);
245 co2meter->online = 0; // Will be set later 248 co2meter->online = atoi(row[4]);
246 co2meter->beercode = xstrcpy(row[5]); 249 co2meter->beercode = xstrcpy(row[5]);
247 co2meter->beername = xstrcpy(row[6]); 250 co2meter->beername = xstrcpy(row[6]);
248 co2meter->beeruuid = xstrcpy(row[7]); 251 co2meter->beeruuid = xstrcpy(row[7]);
249 co2meter->mode = xstrcpy(row[8]); 252 co2meter->mode = xstrcpy(row[8]);
250 co2meter->temperature_state = xstrcpy(row[9]); 253 co2meter->temperature_state = xstrcpy(row[9]);
270 } 273 }
271 mysql_free_result(res_set); 274 mysql_free_result(res_set);
272 } 275 }
273 } 276 }
274 277
275 syslog(LOG_NOTICE, "MySQL: loaded %d nodes, %d fermenters, %d co2meters", ncnt, fcnt, ccnt); 278 if (mysql_query(con, "SELECT * FROM mon_ispindels")) {
279 syslog(LOG_NOTICE, "MySQL: SELECT * FROM mon_ispindels error %u (%s))", mysql_errno(con), mysql_error(con));
280 } else {
281 res_set = mysql_store_result(con);
282 if (res_set == NULL) {
283 syslog(LOG_NOTICE, "MySQL: mysq_store_result error %u (%s))", mysql_errno(con), mysql_error(con));
284 } else {
285 while ((row = mysql_fetch_row(res_set)) != NULL) {
286 ispindel = (sys_ispindel_list *)malloc(sizeof(sys_ispindel_list));
287 memset(ispindel, 0, sizeof(sys_ispindel_list));
288 ispindel->next = NULL;
289 ispindel->uuid = xstrcpy(row[1]);
290 ispindel->alias = xstrcpy(row[2]);
291 ispindel->node = xstrcpy(row[3]);
292 ispindel->online = atoi(row[4]);
293 ispindel->alarm = atoi(row[5]);
294 ispindel->beercode = xstrcpy(row[6]);
295 ispindel->beername = xstrcpy(row[7]);
296 ispindel->beeruuid = xstrcpy(row[8]);
297 ispindel->angle = atof(row[9]);
298 ispindel->temperature = atof(row[10]);
299 ispindel->battery = atof(row[11]);
300 ispindel->gravity = atof(row[12]);
301 ispindel->interval = atoi(row[13]);
302 ispindel->mode = xstrcpy(row[14]);
303
304 if (ispindels == NULL) {
305 ispindels = ispindel;
306 } else {
307 for (tmpi = ispindels; tmpi; tmpi = tmpi->next) {
308 if (tmpi->next == NULL) {
309 tmpi->next = ispindel;
310 break;
311 }
312 }
313 }
314 icnt++;
315 }
316 mysql_free_result(res_set);
317 }
318 }
319
320 syslog(LOG_NOTICE, "MySQL: loaded %d nodes, %d fermenters, %d co2meters %d ispindels", ncnt, fcnt, ccnt, icnt);
276 return 0; 321 return 0;
277 } 322 }
278 323
279 324
280 325
281 void bms_mysql_end(void) 326 void bms_mysql_end(void)
282 { 327 {
328 sys_ispindel_list *tmpi, *oldtmpi;
283 sys_co2meter_list *tmpp, *oldtmpp; 329 sys_co2meter_list *tmpp, *oldtmpp;
284 sys_fermenter_list *tmpf, *oldtmpf; 330 sys_fermenter_list *tmpf, *oldtmpf;
285 sys_node_list *tmpn, *oldtmpn; 331 sys_node_list *tmpn, *oldtmpn;
286 332
287 mysql_close(con); 333 mysql_close(con);
288 334
289 syslog(LOG_NOTICE, "MySQL: disconnected"); 335 syslog(LOG_NOTICE, "MySQL: disconnected");
290 if (debug) 336 if (debug)
291 fprintf(stdout, "MySQL: disconnected\n"); 337 fprintf(stdout, "MySQL: disconnected\n");
338
339 for (tmpi = ispindels; tmpi; tmpi = oldtmpi) {
340 oldtmpi = tmpi->next;
341 if (tmpi->uuid)
342 free(tmpi->uuid);
343 if (tmpi->alias)
344 free(tmpi->alias);
345 if (tmpi->node)
346 free(tmpi->node);
347 if (tmpi->beercode)
348 free(tmpi->beercode);
349 if (tmpi->beername)
350 free(tmpi->beername);
351 if (tmpi->beeruuid)
352 free(tmpi->beeruuid);
353 if (tmpi->mode)
354 free(tmpi->mode);
355 free(tmpi);
356 }
292 357
293 for (tmpp = co2meters; tmpp; tmpp = oldtmpp) { 358 for (tmpp = co2meters; tmpp; tmpp = oldtmpp) {
294 oldtmpp = tmpp->next; 359 oldtmpp = tmpp->next;
295 if (tmpp->uuid) 360 if (tmpp->uuid)
296 free(tmpp->uuid); 361 free(tmpp->uuid);
395 460
396 461
397 int bms_mysql_query(const char *query) 462 int bms_mysql_query(const char *query)
398 { 463 {
399 int rc = mysql_query(con, query); 464 int rc = mysql_query(con, query);
465 int err = mysql_errno(con);
400 466
401 if (rc) { 467 if (rc) {
402 syslog(LOG_NOTICE, "MySQL: error %u (%s)", mysql_errno(con), mysql_error(con)); 468 syslog(LOG_NOTICE, "MySQL: error %u (%s)", err, mysql_error(con));
403 syslog(LOG_NOTICE, query); 469 syslog(LOG_NOTICE, query);
404 } else { 470 } else {
405 return 0; 471 return 0;
406 } 472 }
407 473
408 /* Any error execpt server gone away */ 474 /* Any error execpt server gone away */
409 if (rc != 2006) 475 if (err != 2006)
410 return rc; 476 return rc;
411 477
478 syslog(LOG_NOTICE, "Trying to reconnect");
412 /* Try to reconnect and do the query again */ 479 /* Try to reconnect and do the query again */
413 mysql_close(con); 480 mysql_close(con);
414 if (mysql_real_connect(con, Config.mysql_host, Config.mysql_user, Config.mysql_pass, Config.mysql_database, Config.mysql_port, NULL, 0) == NULL) { 481 if (mysql_real_connect(con, Config.mysql_host, Config.mysql_user, Config.mysql_pass, Config.mysql_database, Config.mysql_port, NULL, 0) == NULL) {
415 syslog(LOG_NOTICE, "MySQL: mysql_real_connect() %s", mysql_error(con)); 482 syslog(LOG_NOTICE, "MySQL: mysql_real_connect() %s", mysql_error(con));
416 return 2; 483 return 2;
432 499
433 500
434 501
435 void node_mysql_insert(sys_node_list *node) 502 void node_mysql_insert(sys_node_list *node)
436 { 503 {
437 char *query = malloc(1024), first[21], last[21]; 504 char *query = malloc(1024), first[73], last[73];
438 struct tm *mytime; 505 struct tm *mytime;
439 506
440 mytime = localtime(&node->firstseen); 507 mytime = localtime(&node->firstseen);
441 snprintf(first, 20, "%04d-%02d-%02d %02d:%02d:%02d", 508 snprintf(first, 72, "%04d-%02d-%02d %02d:%02d:%02d",
442 mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec); 509 mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);
443 mytime = localtime(&node->lastseen); 510 mytime = localtime(&node->lastseen);
444 snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d", 511 snprintf(last, 72, "%04d-%02d-%02d %02d:%02d:%02d",
445 mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec); 512 mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);
446 513
447 snprintf(query, 1023, 514 snprintf(query, 1023,
448 "INSERT INTO mon_nodes SET uuid='%s', node='%s', online='%s', group_id='%s', " \ 515 "INSERT INTO mon_nodes SET uuid='%s', node='%s', online='%d', group_id='%s', " \
449 "hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', firstseen='%s', lastseen='%s', " \ 516 "hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', firstseen='%s', lastseen='%s', " \
450 "temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \ 517 "temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \
451 "net_address='%s', net_ifname='%s', net_rssi='%d'", 518 "net_address='%s', net_ifname='%s', net_rssi='%d', up_interval='%d'",
452 node->uuid, node->node, node->online ?"Y":"N", node->group_id, 519 node->uuid, node->node, node->online ?1:0, node->group_id,
453 node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, first, last, 520 node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, first, last,
454 node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude, 521 node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude,
455 node->net_address, node->net_ifname, node->net_rssi); 522 node->net_address, node->net_ifname, node->net_rssi, node->interval);
456 523
457 if (bms_mysql_query(query) == 0) { 524 if (bms_mysql_query(query) == 0) {
458 syslog(LOG_NOTICE, "MySQL: insert new node %s", node->node); 525 syslog(LOG_NOTICE, "MySQL: insert new node %s", node->node);
459 } 526 }
460 527
463 530
464 531
465 532
466 void node_mysql_update(sys_node_list *node) 533 void node_mysql_update(sys_node_list *node)
467 { 534 {
468 char *query = malloc(1024), last[21]; 535 char *query = malloc(1024), last[65];
469 struct tm *mytime; 536 struct tm *mytime;
470 537
471 mytime = localtime(&node->lastseen); 538 mytime = localtime(&node->lastseen);
472 snprintf(last, 20, "%04d-%02d-%02d %02d:%02d:%02d", 539 snprintf(last, 64, "%04d-%02d-%02d %02d:%02d:%02d",
473 mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec); 540 mytime->tm_year + 1900, mytime->tm_mon + 1, mytime->tm_mday, mytime->tm_hour, mytime->tm_min, mytime->tm_sec);
474 541
475 snprintf(query, 1023, 542 snprintf(query, 1023,
476 "UPDATE mon_nodes SET online='%s', hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', lastseen='%s', " \ 543 "UPDATE mon_nodes SET online='%d', hardwaremake='%s', hardwaremodel='%s', os='%s', os_version='%s', firmware='%s', lastseen='%s', " \
477 "temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \ 544 "temperature='%.3f', humidity='%.3f', barometer='%.3f', gps_latitude='%.8f', gps_longitude='%.8f', gps_altitude='%.8f', " \
478 "net_address='%s', net_ifname='%s', net_rssi='%d' WHERE uuid='%s'", 545 "net_address='%s', net_ifname='%s', net_rssi='%d', up_interval='%d' WHERE uuid='%s'",
479 node->online ?"Y":"N", node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, last, 546 node->online ? 1:0, node->hardwaremake, node->hardwaremodel, node->os, node->os_version, node->firmware, last,
480 node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude, 547 node->temperature, node->humidity, node->barometer, node->gps_latitude, node->gps_longitude, node->gps_altitude,
481 node->net_address, node->net_ifname, node->net_rssi, node->uuid); 548 node->net_address, node->net_ifname, node->net_rssi, node->interval, node->uuid);
482 549
483 bms_mysql_query(query); 550 bms_mysql_query(query);
484 free(query); 551 free(query);
485 } 552 }
486 553
488 555
489 void node_mysql_death(char *node) 556 void node_mysql_death(char *node)
490 { 557 {
491 char *query = malloc(512); 558 char *query = malloc(512);
492 559
493 snprintf(query, 511, "UPDATE mon_nodes SET online='N' WHERE node='%s'", node); 560 snprintf(query, 511, "UPDATE mon_nodes SET online='0' WHERE node='%s'", node);
494 bms_mysql_query(query); 561 bms_mysql_query(query);
495 free(query); 562 free(query);
496 } 563 }
497 564
498 565
500 void fermenter_mysql_insert(sys_fermenter_list *fermenter) 567 void fermenter_mysql_insert(sys_fermenter_list *fermenter)
501 { 568 {
502 char *query = malloc(2560); 569 char *query = malloc(2560);
503 570
504 snprintf(query, 2559, 571 snprintf(query, 2559,
505 "INSERT INTO mon_fermenters SET uuid='%s', alias='%s', node='%s', online='%s', " \ 572 "INSERT INTO mon_fermenters SET uuid='%s', alias='%s', node='%s', online='%d', " \
506 "beercode='%s', beername='%s', beeruuid='%s', " \ 573 "beercode='%s', beername='%s', beeruuid='%s', " \
507 "air_address='%s', air_state='%s', air_temperature='%.3f', " \ 574 "air_address='%s', air_state='%s', air_temperature='%.3f', " \
508 "beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \ 575 "beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \
509 "chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \ 576 "chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \
510 "heater_address='%s', heater_state='%d', heater_usage='%lu', " \ 577 "heater_address='%s', heater_state='%d', heater_usage='%lu', " \
515 "psu_address='%s', psu_state='%d', " \ 582 "psu_address='%s', psu_state='%d', " \
516 "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \ 583 "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \
517 "profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \ 584 "profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \
518 "profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s', " \ 585 "profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s', " \
519 "yeast_lo='%.1f', yeast_hi='%.1f', webcam_url='%s', webcam_light='%d'", 586 "yeast_lo='%.1f', yeast_hi='%.1f', webcam_url='%s', webcam_light='%d'",
520 fermenter->uuid, fermenter->alias, fermenter->node, fermenter->online ? "Y":"N", 587 fermenter->uuid, fermenter->alias, fermenter->node, fermenter->online ? 1:0,
521 fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "", 588 fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "",
522 fermenter->beeruuid ? fermenter->beeruuid : "", 589 fermenter->beeruuid ? fermenter->beeruuid : "",
523 fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature, 590 fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature,
524 fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature, 591 fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature,
525 fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", fermenter->chiller_temperature, 592 fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", fermenter->chiller_temperature,
546 void fermenter_mysql_update(sys_fermenter_list *fermenter) 613 void fermenter_mysql_update(sys_fermenter_list *fermenter)
547 { 614 {
548 char *query = malloc(2560); 615 char *query = malloc(2560);
549 616
550 snprintf(query, 2559, 617 snprintf(query, 2559,
551 "UPDATE mon_fermenters SET online='%s', beercode='%s', beername='%s', beeruuid='%s', " \ 618 "UPDATE mon_fermenters SET online='%d', beercode='%s', beername='%s', beeruuid='%s', " \
552 "air_address='%s', air_state='%s', air_temperature='%.3f', " \ 619 "air_address='%s', air_state='%s', air_temperature='%.3f', " \
553 "beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \ 620 "beer_address='%s', beer_state='%s', beer_temperature='%.3f', " \
554 "chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \ 621 "chiller_address='%s', chiller_state='%s', chiller_temperature='%.3f', " \
555 "heater_address='%s', heater_state='%d', heater_usage='%lu', " \ 622 "heater_address='%s', heater_state='%d', heater_usage='%lu', " \
556 "cooler_address='%s', cooler_state='%d', cooler_usage='%lu', " \ 623 "cooler_address='%s', cooler_state='%d', cooler_usage='%lu', " \
560 "psu_address='%s', psu_state='%d', " \ 627 "psu_address='%s', psu_state='%d', " \
561 "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \ 628 "mode='%s', alarm='%d', setpoint_high='%.3f', setpoint_low='%.3f', " \
562 "profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \ 629 "profile_uuid='%s', profile_name='%s', profile_state='%s', profile_percent='%d', " \
563 "profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s', " \ 630 "profile_inittemp_high='%.3f', profile_inittemp_low='%.3f', profile_steps='%s', stage='%s', " \
564 "yeast_lo='%.1f', yeast_hi='%.1f', webcam_url='%s', webcam_light='%d' WHERE uuid='%s'", 631 "yeast_lo='%.1f', yeast_hi='%.1f', webcam_url='%s', webcam_light='%d' WHERE uuid='%s'",
565 fermenter->online ? "Y":"N", fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "", 632 fermenter->online ? 1:0, fermenter->beercode ? fermenter->beercode : "", fermenter->beername ? fermenter->beername : "",
566 fermenter->beeruuid ? fermenter->beeruuid : "", 633 fermenter->beeruuid ? fermenter->beeruuid : "",
567 fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature, 634 fermenter->air_address ? fermenter->air_address : "", fermenter->air_state ? fermenter->air_state : "", fermenter->air_temperature,
568 fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature, 635 fermenter->beer_address ? fermenter->beer_address : "", fermenter->beer_state ? fermenter->beer_state : "", fermenter->beer_temperature,
569 fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", fermenter->chiller_temperature, 636 fermenter->chiller_address ? fermenter->chiller_address : "", fermenter->chiller_state ? fermenter->chiller_state : "", fermenter->chiller_temperature,
570 fermenter->heater_address ? fermenter->heater_address : "", fermenter->heater_state, fermenter->heater_usage, 637 fermenter->heater_address ? fermenter->heater_address : "", fermenter->heater_state, fermenter->heater_usage,
589 void fermenter_mysql_death(char *node, char *alias) 656 void fermenter_mysql_death(char *node, char *alias)
590 { 657 {
591 char *query = malloc(512); 658 char *query = malloc(512);
592 659
593 if (alias) 660 if (alias)
594 snprintf(query, 511, "UPDATE mon_fermenters SET online='N' WHERE node='%s' and alias='%s'", node, alias); 661 snprintf(query, 511, "UPDATE mon_fermenters SET online='0' WHERE node='%s' and alias='%s'", node, alias);
595 else 662 else
596 snprintf(query, 511, "UPDATE mon_fermenters SET online='N' WHERE node='%s'", node); 663 snprintf(query, 511, "UPDATE mon_fermenters SET online='0' WHERE node='%s'", node);
597 664
598 bms_mysql_query(query); 665 bms_mysql_query(query);
599 free(query); 666 free(query);
600 } 667 }
601 668
653 bms_mysql_query(query); 720 bms_mysql_query(query);
654 free(query); 721 free(query);
655 } 722 }
656 723
657 724
725
726 void ispindel_mysql_insert(sys_ispindel_list *ispindel)
727 {
728 char *query = malloc(2560);
729
730 snprintf(query, 2559,
731 "INSERT INTO mon_ispindels SET uuid='%s', alias='%s', node='%s', online='%d', mode='%s', alarm='%d', " \
732 "angle='%.5f', temperature='%.4f', battery='%.6f', gravity='%.5f', up_interval='%d', og_gravity='0.0'",
733 ispindel->uuid, ispindel->alias, ispindel->node, ispindel->online ? 1:0, ispindel->mode, ispindel->alarm,
734 ispindel->angle, ispindel->temperature, ispindel->battery, ispindel->gravity, ispindel->interval);
735
736 if (bms_mysql_query(query) == 0) {
737 syslog(LOG_NOTICE, "MySQL: insert new ispindel %s", ispindel->node);
738 }
739 free(query);
740 }
741
742
743
744 void ispindel_mysql_update(sys_ispindel_list *ispindel)
745 {
746 char *query = malloc(2560);
747
748 snprintf(query, 2559,
749 "UPDATE mon_ispindels SET online='%d', mode='%s', alarm='%d', " \
750 "angle='%.5f', temperature='%.4f', battery='%.6f', gravity='%.5f', up_interval='%d', og_gravity=GREATEST(og_gravity, '%.5f') WHERE uuid='%s'",
751 ispindel->online ? 1:0, ispindel->mode, ispindel->alarm,
752 ispindel->angle, ispindel->temperature, ispindel->battery, ispindel->gravity, ispindel->interval, ispindel->gravity, ispindel->uuid);
753
754 bms_mysql_query(query);
755 free(query);
756 }
757
758
759
760 void ispindel_mysql_death(char *node)
761 {
762 char *query = malloc(512);
763
764 snprintf(query, 511, "UPDATE mon_ispindels SET online='0' WHERE node='%s'", node);
765 bms_mysql_query(query);
766 free(query);
767 }
768
769
770
771 /*
772 * Check using a new MySQL connection because we are running from another thread.
773 */
774 void ispindel_mysql_check(void)
775 {
776 sys_ispindel_list *tmpp;
777 MYSQL *con2 = NULL;
778 MYSQL_RES *res_set2;
779 MYSQL_ROW row2;
780
781 if (ispindels == NULL)
782 return;
783
784 con2 = mysql_init(NULL);
785 if (con2 == NULL) {
786 syslog(LOG_NOTICE, "MySQL: mysql_init() failed");
787 return;
788 }
789
790 if (mysql_real_connect(con2, Config.mysql_host, Config.mysql_user, Config.mysql_pass, Config.mysql_database, Config.mysql_port, NULL, 0) == NULL) {
791 syslog(LOG_NOTICE, "MySQL: mysql_real_connect() %s", mysql_error(con2));
792 return;
793 }
794
795 if (mysql_query(con2, "SELECT uuid,alias,beercode,beername,beeruuid,mode FROM mon_ispindels;")) {
796 syslog(LOG_NOTICE, "MySQL: SELECT uuid,alias,beercode,beername,beeruuid,mode FROM mon_ispindels error %u (%s))", mysql_errno(con2), mysql_error(con2));
797 } else {
798 res_set2 = mysql_store_result(con2);
799 if (res_set2 == NULL) {
800 syslog(LOG_NOTICE, "MySQL: mysq_store_result error %u (%s))", mysql_errno(con2), mysql_error(con2));
801 } else {
802 while ((row2 = mysql_fetch_row(res_set2)) != NULL) {
803 for (tmpp = ispindels; tmpp; tmpp = tmpp->next) {
804 if (strcmp(tmpp->uuid, row2[0]) == 0) {
805 if (strcmp(tmpp->beercode, row2[2]) || strcmp(tmpp->beername, row2[3]) || strcmp(tmpp->beeruuid, row2[4])) {
806 syslog(LOG_NOTICE, "ispindel `%s` change beer to `%s %s`", row2[1], row2[2], row2[3]);
807 if (tmpp->beercode)
808 free(tmpp->beercode);
809 tmpp->beercode = xstrcpy(row2[2]);
810 if (tmpp->beername)
811 free(tmpp->beername);
812 tmpp->beername = xstrcpy(row2[3]);
813 if (tmpp->beeruuid)
814 free(tmpp->beeruuid);
815 tmpp->beeruuid = xstrcpy(row2[4]);
816 }
817 if (strcmp(tmpp->mode, row2[5])) {
818 syslog(LOG_NOTICE, "ispindel `%s` change mode `%s`", row2[1], row2[5]);
819 if (tmpp->mode)
820 free(tmpp->mode);
821 tmpp->mode = xstrcpy(row2[5]);
822 }
823 }
824 }
825 }
826 mysql_free_result(res_set2);
827 }
828 }
829
830 mysql_close(con2);
831 }
832

Mercurial Repository: bms

mercurial