From f461190e22a97bde985b067ce01c4ed9c74f7e62 Mon Sep 17 00:00:00 2001
From: pks <pks@pks.rocks>
Date: Sat, 1 May 2021 06:17:55 +0200
Subject: v1

---
 v1/fill-db-from-log.rb | 90 ++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 90 insertions(+)
 create mode 100755 v1/fill-db-from-log.rb

(limited to 'v1/fill-db-from-log.rb')

diff --git a/v1/fill-db-from-log.rb b/v1/fill-db-from-log.rb
new file mode 100755
index 0000000..1fb2ab3
--- /dev/null
+++ b/v1/fill-db-from-log.rb
@@ -0,0 +1,90 @@
+#!/usr/bin/env ruby
+
+require 'sqlite3'
+
+
+int = Proc.new do  |x|
+  x.to_i
+end
+float = Proc.new do |x|
+  x.to_f
+end
+str = Proc.new do |x|
+  x.to_s
+end
+common_conv = { "db" => str, "timestamp"=>float, "device_type"=>str, "type"=>str, "device_id"=>str, "battery" => int, "battery_numeric" => int, "rssi" => int, "rssi_numeric" => int }
+rain_conv = {"rain_rate"=>float, "rain_total"=>float}.merge(common_conv)
+wind_conv = {"chill"=>float, "temperature"=>float,"average_speed"=>float,"direction"=>int,"gust"=>float}.merge(common_conv)
+temp_conv = {"humidity"=>float, "humidity_status"=>str,"humidity_status_numeric"=>int,"temperature"=>float,"barometer"=>float,"forecast"=>str,"forecast_numeric"=>int}.merge(common_conv)
+conv = { "rain" => rain_conv, "wind" => wind_conv, "temp" => temp_conv }
+
+$db = SQLite3::Database.new("weather.db")
+
+
+def parse_to_hash s, conv, type
+  h = {}
+  begin
+    s.split("\t").map { |i|
+      i=i.split("::")
+      if not conv.keys.include? i[0]
+        STDERR.write "Unknown key '#{i[0]}' in data point of type '#{type}', ignoring!\n"
+      end
+      h[i[0]] = conv[i[0]].call(i[1])
+    }
+  rescue
+    STDERR.write "Cannot parse '#{s.strip}', skipping data point!\n"
+    return false
+  end
+  return h
+end
+
+
+def db_execute 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 insert_rain h
+  db_execute("INSERT INTO rain(timestamp, device_type, device_id, battery, rssi, rain_rate, rain_total) VALUES(?,?,?,?,?,?,?)", [h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["rain_rate"], h["rain_total"]])
+end
+
+
+def insert_wind h
+  db_execute("INSERT INTO wind(timestamp, device_type, device_id, battery, rssi, chill, temperature, average_speed, direction, gust) VALUES(?,?,?,?,?,?,?,?,?,?)", [h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["chill"], h["temperature"], h["average_speed"], h["direction"], h["gust"]])
+end
+
+
+def insert_temp h
+  if h.keys.include? "forecast"
+    db_execute("INSERT INTO temp(timestamp, device_type, device_id, battery, rssi, humidity, humidity_status, humidity_status_numeric, temperature, forecast, forecast_numeric) VALUES(?,?,?,?,?,?,?,?,?,?,?)",[h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["humidity"], h["humidity_status"], h["humidity_status_numeric"], h["temperature"], h["forecast"], h["forecast_numeric"]])
+  else
+    db_execute("INSERT INTO temp(timestamp, device_type, device_id, battery, rssi, humidity, humidity_status, humidity_status_numeric, temperature) VALUES(?,?,?,?,?,?,?,?,?)",[h["timestamp"], h["device_type"],h["device_id"], h["battery"], h["rssi"], h["humidity"], h["humidity_status"], h["humidity_status_numeric"], h["temperature"]])
+  end
+end
+
+
+while line = STDIN.gets
+  break if line.strip=="stop"
+  type = line.split("\t").first.split("::")[1]
+  h = parse_to_hash(line, conv[type], type)
+  if h
+    if type    == "rain"
+      insert_rain h
+    elsif type == "wind"
+      insert_wind h
+    elsif type == "temp"
+      insert_temp h
+    end
+  end
+end
+
+$db.close
-- 
cgit v1.2.3