Feature proposal: mysql

[wolfgangr]

anybody out there implemented a mysql / mariadb connectivity?

I'll have a glimpse on the influx code.
However, I'm not a python-pro.
Maybe, for me, it's easier to hack a quick mqtt-PERL-mysql path, based on existing work.
let's see....

[wolfgangr]

voila - done - works - at least for me
disclaimer: don't exect anything good and everey evil from it ;-)

#!/usr/bin/perl

# boilerplated from 
# https://github.com/wolfgangr/ew_mq_log/blob/master/log_ecowitt.pl

use warnings;
use strict;
use Net::MQTT::Simple;
use JSON;
use Data::Dumper;
use DBD::mysql;
use POSIX qw(strftime); # time string formatting


my $mqtt_server = "my.server.lokal";
my $em_topic = "SMA-EM";
# my $station = 1;
my $serial ='3006123456' ;  # serial number of SMA HM
my $db_creds ="my.cnf";
my $debug = 1;

# ======= eo config ============

my $eff_topic = $em_topic . '/' . $serial;

debug_print(1, sprintf("Listening to MQTT server at <%s>, topic <%s>\n",
                $mqtt_server, $eff_topic));


my $mqtt = Net::MQTT::Simple->new($mqtt_server);
my $json = JSON->new->allow_nonref;


# open database connection
my $db_driver = "mysql";
my $creds = parse_DB_creds($db_creds);
# exit;

my $user     = $creds->{'user'} ;
my $database = $creds->{'database'} ;
my $DBHost   = $creds->{'host'} ;
my $passwd   = $creds->{'password'} ;

debug_print(1, "\n$0 connecting as user <$user> to database <$database> at host <$DBHost>...\n");
# exit;

my $dsn = "DBI:$db_driver:$database;$DBHost";
my $dbh = DBI->connect($dsn, $user, $passwd)
        || die ("Could not connect to database: $DBI::errstr\n");

$dbh->{mysql_auto_reconnect} = 1;

debug_print(1,  "\t...connected to database \n\n") ;
# exit;


# main loop / listener

$mqtt->run(
        # $eff_topic  => \&parse_default,
        $eff_topic  => \&do_sma_emd,
        # $em_topic   => \&parse_default,   # \&do_ecowitt,
        # "#" => \&parse_default,
        # "#" => \&noop,

    );

#============== subs ==============
sub do_sma_emd {
  my $hr = parse_sma_emd(@_);
  debug_print(4, Dumper($hr));
  # disable for dev of aux sensor stuff
  log_sma_emd($hr);

  # parse and process aux sensor data
  # do_auxs($hr);

}


sub parse_sma_emd {
    my ($topic, $message) = @_;
    debug_print(4, "SMA HM data: \n $message\n");

    my $hashref = $json->decode( $message );
    return $hashref;
}

sub log_sma_emd {
  my $sql = build_sma_SQL($_[0]);
  debug_print(3, $sql);

  # execute sql statement
  my $affected = $dbh->do($sql);
  debug_print (2, "\t$affected Datasets updated\n");
}

sub build_sma_SQL {
  my $data = pop @_;

  my $sql = "INSERT INTO `hm_raw` SET";
  $sql .= sprintf (" \n `time` = from_unixtime('%s')   ", $data->{'timestamp'});
  $sql .= sprintf (",\n `p_consume`        = '%s'   ", $data->{'pconsume'});
  $sql .= sprintf (",\n `p_supply`         = '%s'   ", $data->{'psupply'});
  $sql .= sprintf (",\n `E_consume`        = '%s'   ", $data->{'pconsumecounter'});
  $sql .= sprintf (",\n `E_supply`         = '%s'   ", $data->{'psupplycounter'});

  $sql .= " ;\n" ;
  debug_print(4, $sql);
  return $sql
}


# for dbug: we may parse other messages
sub parse_default  {
            my ($topic, $message) = @_;
            print "default: [$topic] $message\n";
        }


#--- parse db credential file
# https://mariadb.com/kb/en/configuring-mariadb-with-option-files/
# https://mariadb.com/kb/en/mariadb-command-line-client/
sub parse_DB_creds {
  my $opt_file = pop @_;

  my %creds =();

  open my $OF, $opt_file or die "Could not open $opt_file: $!";
  while (<$OF>) {
    next if /^#/;
    next if /\[client\]/;
    if ( /^\s*(\w+)\s*=\s*([\w\-\.]+)\s*$/ ) {
      $creds{$1} = $2;
      next;
    }
    die("illegal line in config file $opt_file: >|$_|<");

  }
  close ($OF);
  debug_print(3, Dumper(\%creds));
  return \%creds;
}



# debug_print($level, $content)
sub debug_print {
  my $level = shift @_;
  print STDERR @_ if ( $level <= $debug) ;
  # print  @_ if $debug ;
}

[wolfgangr]

database credentials are kept in separate file
must be readable for daemon user as configured in service file (see below), eg like:
-rw-r----- 1 adminuser daemon 88 Sep 16 01:17 my.cnf

$ cat my.cnf 
[client]
host=localhost
user=sunnyhome-writer
password=secret12345
database=SMA-emd

[wolfgangr]

systemd service file

# perl skript, parses smaemd  messages
# logs to database 
#
# [wr] 2024-09-16

[Unit]
Description=log_SMAemd.pl daemon for SMA Sunny Home Manager data
After=network.target
# may be needs edit if services run on other machine
Wants=mysql.service
Wants=mosquitto.service
Wants=smaemd.service

[Service]
Type=simple
ExecStart=/usr/bin/perl log_SMAemd.pl
ExecReload=kill -HUP $MAINPID
KillMode=process
Restart=on-failure
RestartSec=63s
WorkingDirectory=/home/wrosner/SMA/SMA_emd

[Install]
WantedBy=multi-user.target

[wolfgangr]

SQL code for table creation
(autogenerated by Adminer)

-- Adminer 4.8.1 MySQL 10.11.6-MariaDB-0+deb12u1 dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `hm_raw`;
CREATE TABLE `hm_raw` (
  `time` datetime NOT NULL,
  `p_consume` decimal(8,2) NOT NULL DEFAULT 0.00,
  `p_supply` decimal(8,2) NOT NULL DEFAULT 0.00,
  `E_consume` decimal(15,5) NOT NULL DEFAULT 0.00000,
  `E_supply` decimal(15,5) NOT NULL DEFAULT 0.00000,
  PRIMARY KEY (`time` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;
-- 2024-09-16 16:58:50