cc128_log_mysql.pl

#!/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 = "password";
my $dbtable = "powermeter";

my $subclient = "/usr/bin/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();