add PostgreSQL storage support from log files

diff --git a/create.sql b/create.sql
new file mode 100644 (file)
index 0000000..84e9d6e
--- /dev/null
+++ b/create.sql
@@ -0,0 +1,17 @@
+drop table zc;
+create table zc (
+       time timestamp without time zone default now(),
+       PN integer not null,
+       Alarm_axis smallint not null,
+       X_axis_angle float not null,
+       Y_axis_angle float not null,
+       Signal_strength smallint not null,
+       Arming_disarming smallint not null,
+       Sensor_temperature float not null,
+       Power_source_voltage float not null,
+       Sensor_operating_mode smallint not null,
+       _id serial
+);
+
+create user grafana password 'readonly' ;
+grant select on zc to  grafana ;

diff --git a/debian-install.sh b/debian-install.sh
index 8e89dbe..af0d0cb 100755 (executable)
--- a/debian-install.sh
+++ b/debian-install.sh
@@ -1,6 +1,6 @@
 #!/bin/sh -xe
 
-sudo apt install libdata-dump-perl libmodule-pluggable-perl dh-make-perl
+sudo apt install libdata-dump-perl libmodule-pluggable-perl dh-make-perl libdbd-pg-perl libjson-perl
 if [ ! -f libnet-mqtt-perl_*deb ] ; then
        cpan2deb Net::MQTT::Message
        dpkg -i libnet-mqtt-perl_*.deb 

diff --git a/log2pgsql b/log2pgsql
new file mode 100755 (executable)
index 0000000..c063608
--- /dev/null
+++ b/log2pgsql
@@ -0,0 +1,122 @@
+#!/usr/bin/perl
+use warnings;
+use strict;
+
+use Data::Dump qw(dump);
+use DBD::Pg;
+use JSON;
+use Time::Local;
+
+use lib '.';
+use Protocol;
+
+my $debug = $ENV{DEBUG};
+
+$| = 1;
+
+my $dbh = DBI->connect("dbi:Pg:dbname=zc", "dpavlin", "", { RaiseError => 1 });
+my @columns = qw(
+PN
+Alarm_axis
+X_axis_angle
+Y_axis_angle
+Signal_strength
+Arming_disarming
+Sensor_temperature
+Power_source_voltage
+Sensor_operating_mode
+);
+my $cols = join(',', @columns);
+my $sth = $dbh->prepare(qq{insert into zc (time,$cols) values (?} . ',?' x ($#columns+1) . qq{)});
+
+warn "## protocol = ",dump( $protocol ) if $debug;
+
+open(my $i, '>', '/tmp/influx');
+
+sub print_hex {
+       my ( $timestamp, $sensor, $imei, $up_down, $hex ) = @_;
+
+       my ( $yyyy, $mm, $dd, $h, $m, $s ) = split(/[- :]/, $timestamp);
+       my $t = timelocal( $s, $m, $h, $dd, $mm, $yyyy ) * 1_000_000_000;
+
+       warn "hex = $hex\n";
+
+       my $raw = join('', map { chr(hex($_)) } split(/\s+/,$hex));
+
+       warn "raw = ", hex_dump($raw);
+
+       my $hash = protocol_decode( $up_down, $raw );
+
+       warn "hash = ",dump($hash);
+
+       my $function_code = $hash->{function_code} || die "no function_code";
+       my $fc_desc = $function_code_description->{$up_down}->{$function_code} || die "no function_code_description for $up_down $function_code";
+       print " function_code=$function_code ",
+               $fc_desc,
+               " ver=", $hash->{ver},
+               " len=", $hash->{len},
+               "\n";
+
+       my $json;
+
+       foreach my $data_id ( @{ $hash->{data_id_order} } ) {
+               my $fmt = $protocol->{$data_id}->{pack_fmt};
+               my $v = $hash->{data_id}->{$data_id};
+               print join(" | ", 
+                       $hash->{up_down},
+                       unpack('H*', chr($data_id)),
+                       $protocol->{$data_id}->{description},
+                       $v,
+                       "hex:" . unpack('H*', $hash->{data_range}->{$data_id}),
+                       "len:" . $hash->{data_len}->{$data_id},
+                       "fmt:" . $fmt,
+                       "range:" . $protocol->{$data_id}->{range}, 
+                       "remark:" . $protocol->{$data_id}->{remark}
+               ),"\n";
+
+               my $name = $protocol->{$data_id}->{description} || die;
+               $name =~ s/\W+/_/g;
+               $name = lc($name);
+               $json->{$name} = $v if length($v) > 0;
+       }
+
+       if ( $function_code == 0x07 ) {
+               $sth->execute( $timestamp, map { $json->{lc($_)} } @columns );
+       }
+
+}
+
+my $raw;
+my ( $timestamp, $sensor, $imei, $up_down );
+my $stat;
+while(<>) {
+       chomp;
+
+       if ( m{^(\d\d\d\d-\d\d-\d\d\s\d\d:\d\d:\d\d).*Inclinometer/([^/]+)/([^/]+)/(\w+)} ) {
+               if ( $raw ) {
+                       $raw =~ s{^\s+}{}; # strip leading spaces
+                       print "## $timestamp $sensor $imei $up_down $raw\n";
+                       print_hex( $timestamp, $sensor, $imei, $up_down, $raw );
+                       $raw = '';
+               }
+               ( $timestamp, $sensor, $imei, $up_down ) = ( $1, $2, $3, $4 );
+               $stat->{$sensor}->{$imei}->{$up_down}++;
+       } elsif ( m{^\s+} ) {
+               s/^\s+/ /;
+               s/\s\s.+$//g; # remove ascii
+               $raw .= $_;
+               warn "++ $_\n";
+       } else {
+               warn "IGNORE: $_\n";
+       }
+
+}
+
+if ( $raw ) {
+       $raw =~ s{^\s+}{}; # strip leading spaces
+       print "## $timestamp $sensor $imei $up_down $raw\n";
+       print_hex( $timestamp, $sensor, $imei, $up_down, $raw );
+}
+
+print "stat = ",dump($stat), "\n";
+