From 1123b0db81b6057e113f16a1ddea15c36b7b3d79 Mon Sep 17 00:00:00 2001
From: pks <pks@pks.rocks>
Date: Sun, 27 Sep 2020 20:55:18 +0200
Subject: init

---
 db-from-log.rb | 64 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 64 insertions(+)
 create mode 100755 db-from-log.rb

(limited to 'db-from-log.rb')

diff --git a/db-from-log.rb b/db-from-log.rb
new file mode 100755
index 0000000..dd4d379
--- /dev/null
+++ b/db-from-log.rb
@@ -0,0 +1,64 @@
+#!/usr/bin/env ruby
+
+require 'json'
+require 'sqlite3'
+require 'time'
+
+def db_execute db, s, d
+  begin
+    db.execute(s, d)
+    rescue SQLite3::BusyException
+      sleep 3
+      begin
+        db.execute(s, d)
+      rescue SQLite3::BusyException
+        STDERR.write "DB busy, skipping data point '#{d.to_s}'\n"
+      end
+  end
+end
+
+def parse_log_line line
+  _, topic, data = line.split "\t"
+  topic_parts = topic.split "/"
+  if topic_parts.size == 5 # old format, no hostname in topic
+    _, _, device_location_1, device_location_2, _ = topic_parts
+    device_name = ""
+  else
+    _, device_name, _, device_location_1, device_location_2, _ = topic_parts
+  end
+  device_location = [device_location_1, device_location_2]
+  data.gsub!("'", '"')
+  data = JSON.parse data
+
+  return device_name, device_location, data
+end
+
+def insert_power db, device_name, device_location, data
+  if data.has_key? "ENERGY"
+    timestamp = Time.parse(data["Time"]).utc.to_i
+    total_start_time = Time.parse(data["ENERGY"]["TotalStartTime"]).utc.to_i
+    db_execute db, \
+      "INSERT INTO power(timestamp, device_name, device_location_primary, device_location_secondary, total, total_start_time) VALUES(?,?,?,?,?,?)", \
+      [timestamp, device_name, device_location[0], device_location[1], data["ENERGY"]["Total"], total_start_time]
+  end
+end
+
+def main
+  db = SQLite3::Database.open ARGV[0]
+  while line = STDIN.gets
+    begin
+      device_name, device_location, data = parse_log_line line
+      insert_power db, device_name, device_location, data
+    rescue
+      STDERR.write "Cannot insert log entry '#{line.strip}'\n"
+    end
+    if File.exist? "#{File.expand_path(File.dirname(__FILE__))}/stop"
+      STDERR.write "Stopping loop\n"
+      break
+    end
+  end
+  db.close
+end
+
+
+main
-- 
cgit v1.2.3