-
Notifications
You must be signed in to change notification settings - Fork 2.3k
/
cc128_log_mysql.pl
executable file
·55 lines (45 loc) · 1.43 KB
/
cc128_log_mysql.pl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#!/usr/bin/perl
# Log CurrentCost power meter data to a mysql database.
# Assumes data is coming in on MQTT topic sensors/cc128
# and in format timestamp,temperature,ch1_data
# e.g. 1276605752,12.7,86
# To create database, table and user:
#
# CREATE DATABASE powermeter;
# USE 'powermeter';
# CREATE TABLE powermeter (
# `id` INT NOT NULL auto_increment,
# `timestamp` INT NOT NULL,
# `temperature` FLOAT NOT NULL DEFAULT 0.0,
# `ch1` INT NOT NULL DEFAULT 0,
# PRIMARY KEY (`id`),
# UNIQUE KEY `timestamp` (`timestamp`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
# CREATE USER 'powermeter'@'localhost' IDENTIFIED BY '<your password>';
# GRANT ALL ON powermeter.* to 'powermeter'@'localhost';
use strict;
use DBI();
use FileHandle;
local $| = 1;
my $dbname = "powermeter";
my $dbhost = "localhost";
my $dbusername = "powermeter";
my $dbpassword = "<your password>";
my $dbtable = "powermeter";
my $subclient = "mosquitto_sub -t sensors/cc128";
open(SUB, "$subclient|");
SUB->autoflush(1);
my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$dbhost",
"$dbusername", "$dbpassword", {'RaiseError' => 1});
my $query = "INSERT INTO powermeter (timestamp, temperature, ch1) VALUES (?,?,?)";
my @vals;
my ($timestamp, $temperature, $ch1);
while (my $line = <SUB>) {
@vals = split(/,/, $line);
$timestamp = @vals[0];
$temperature = @vals[1];
$ch1 = @vals[2];
$dbh->do($query, undef, $timestamp, $temperature, $ch1);
}
$dbh->disconnect();