From 69e11599c2194187e1c172ebe6b7b51945a7f306 Mon Sep 17 00:00:00 2001
From: pks <pks@pks.rocks>
Date: Sun, 2 May 2021 21:56:23 +0200
Subject: v2

---
 weather-db-from-old-log | 81 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 81 insertions(+)
 create mode 100755 weather-db-from-old-log

(limited to 'weather-db-from-old-log')

diff --git a/weather-db-from-old-log b/weather-db-from-old-log
new file mode 100755
index 0000000..3efa4b1
--- /dev/null
+++ b/weather-db-from-old-log
@@ -0,0 +1,81 @@
+#!/usr/bin/env ruby
+
+require 'sqlite3'
+
+# Berlin
+#$device_id_to_handle = {
+#  "b7:01" => "outside",
+#  "ca:01" => "outside",
+#  "d5:01" => "outside",
+#  "f4:01" => "outside",
+#  "1d:01" => "inside",
+#  "1f:01" => "inside",
+#  "37:01" => "inside",
+#  "38:01" => "inside",
+#  "9c:01" => "inside",
+#  "cb:01" => "inside"
+#}
+
+# Heidenheim
+$device_id_to_handle = {
+  "58:02" => "outside",
+  "62:02" => "outside",
+  "07:01" => "inside"
+}
+
+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 db_insert timestamp, handle, temperature, humidity
+  db_execute("INSERT INTO weather(timestamp, handle, temperature, humidity) VALUES(?,?,?,?)", 
+             [timestamp, handle, temperature, humidity])
+end
+
+
+$db = SQLite3::Database.new("weather.db")
+
+$db.execute <<-SQL
+          create table weather(
+           id INTEGER PRIMARY KEY,
+           timestamp DATETIME,
+           handle TEXT,
+           temperature FLOAT,
+           humidity FLOAT
+         );
+        SQL
+
+while line = STDIN.gets
+  parts = line.split "\t"
+  log_entry_type = parts.first.split("::")[1]
+  if log_entry_type == "temp"
+    data = {}
+    parts.each { |i|
+      key, value = i.split "::"
+      data[key] = value
+    }
+    data["timestamp"] = data["timestamp"].to_i
+    data["temperature"] = data["temperature"].to_f
+    data["humidity"] = data["humidity"].to_f
+
+    handle = $device_id_to_handle[data["device_id"]]
+    if handle
+      db_insert \
+        data["timestamp"], \
+        handle, \
+        data["temperature"], \
+        data["humidity"]
+    end
+  end
+end
+
+$db.close
-- 
cgit v1.2.3