From 8a89176b2b563ad20c21eea6f31cef01c46582dc Mon Sep 17 00:00:00 2001 From: pks Date: Sun, 16 May 2021 18:23:12 +0200 Subject: restart --- config/init.d/power-logger | 26 ++++------ db-create.rb | 24 --------- db-create.sh | 5 -- db-from-log.rb | 64 ------------------------ db-from-log.sh | 5 -- db-query.rb | 14 ------ mqtt-receiver.py | 27 ---------- power-logger | 122 +++++++++++++++++++++++++++++++++++++++++++++ power-logger.rb | 122 --------------------------------------------- power-meter | 18 ++++--- 10 files changed, 142 insertions(+), 285 deletions(-) delete mode 100755 db-create.rb delete mode 100755 db-create.sh delete mode 100755 db-from-log.rb delete mode 100755 db-from-log.sh delete mode 100755 db-query.rb delete mode 100644 mqtt-receiver.py create mode 100755 power-logger delete mode 100755 power-logger.rb diff --git a/config/init.d/power-logger b/config/init.d/power-logger index f1ac7a7..e50d080 100755 --- a/config/init.d/power-logger +++ b/config/init.d/power-logger @@ -8,32 +8,24 @@ # Short-Description: power-logger ### END INIT INFO -export SUFFIX=$(date +'%Y-%m-%d-%H:%M:%S') +export SUFFIX=$(date +'%Y-%m-%dT%H:%M:%S') export USER=pks export GROUP=pks -export DIR=/home/$USER/mqtt_client/ +export DIR=/usr/local/src/power_logger +export GEM_HOME=/home/$USER/.local/lib/ruby +export GEM_PATH=/home/$USER/.local/lib/ruby case "$1" in start) echo "Starting power-logger" - rm -f $DIR/stop - if [ ! -f $DIR/power.db ]; then - runuser -l $USER -c "$DIR/db-create.sh $DIR/power.db" - fi - mkdir -p $DIR/log - chown -R $USER:$GROUP $DIR/log - runuser -l $USER -c "source $DIR/env/bin/activate && python $DIR/mqtt-receiver.py 2> >(cat > $DIR/log/power.$SUFFIX.err) | tee >(cat > $DIR/log/power.$SUFFIX.out) | $DIR/db-from-log.sh $DIR/power.db 2> >(cat > $DIR/log/power.db-from-log.$SUFFIX.err)" & - echo $! > /run/power-logger.pid2 + su - $USER -c "mkdir -p $DIR/log $DIR/db" + su - $USER -c "$DIR/power-logger.rb -d $DIR/db/power.db -l $DIR/log/power.$SUFFIX.gz" & + echo $! > /run/power-logger.pid ;; stop) echo "Stopping power-logger" - runuser -l $USER -c "touch $DIR/stop" - sleep 3 - if [ -f /run/power-logger.pid2 ]; then - kill $(cat /run/power-logger.pid2) &>/dev/null - rm -f /run/power-logger.pid2 - fi - rm -f $DIR/stop + kill $(cat /run/power-logger.pid) + rm -f /run/power-logger.pid ;; *) echo "Usage: /etc/init.d/power-logger {start|stop}" diff --git a/db-create.rb b/db-create.rb deleted file mode 100755 index a9bf771..0000000 --- a/db-create.rb +++ /dev/null @@ -1,24 +0,0 @@ -#!/usr/bin/env ruby - -require 'sqlite3' - -def main - db = SQLite3::Database.new ARGV[0] - - db.execute <<-SQL - create table power( - id INTEGER PRIMARY KEY, - timestamp DATETIME, - device_name TEXT, - device_location_primary TEXT, - device_location_secondary TEXT, - total FLOAT, - total_start_time DATETIME - ); - SQL - - db.close -end - - -main diff --git a/db-create.sh b/db-create.sh deleted file mode 100755 index cb11626..0000000 --- a/db-create.sh +++ /dev/null @@ -1,5 +0,0 @@ -#!/usr/bin/env zsh - -export GEM_HOME=/home/pks/.local/lib/ruby -export GEM_PATH=/home/pks/.local/lib/ruby -$(which ruby) $(dirname $0:A)/db-create.rb $1 diff --git a/db-from-log.rb b/db-from-log.rb deleted file mode 100755 index dd4d379..0000000 --- a/db-from-log.rb +++ /dev/null @@ -1,64 +0,0 @@ -#!/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 diff --git a/db-from-log.sh b/db-from-log.sh deleted file mode 100755 index 75a316a..0000000 --- a/db-from-log.sh +++ /dev/null @@ -1,5 +0,0 @@ -#!/usr/bin/env zsh - -export GEM_HOME=/home/pks/.local/lib/ruby -export GEM_PATH=/home/pks/.local/lib/ruby -$(which ruby) $(dirname $0:A)/db-from-log.rb $1 diff --git a/db-query.rb b/db-query.rb deleted file mode 100755 index 3a3fa70..0000000 --- a/db-query.rb +++ /dev/null @@ -1,14 +0,0 @@ -#!/usr/bin/env ruby - -require 'sqlite3' - -def main - db = SQLite3::Database.new ARGV[0] - db.execute("select * from power") do |result| - puts result.to_s - end - db.close -end - - -main diff --git a/mqtt-receiver.py b/mqtt-receiver.py deleted file mode 100644 index 3d6cd8a..0000000 --- a/mqtt-receiver.py +++ /dev/null @@ -1,27 +0,0 @@ -import json -import paho.mqtt.client as mqtt -import sys - - -def on_connect(client, userdata, flags, rc): - client.subscribe("#") # Subscribe to all topics - sys.stderr.write("Connected with result code {}\n".format(rc)) - sys.stderr.flush() - - -def on_message(client, userdata, msg): - msg_as_str = msg.payload.decode('utf-8') - try: - print("{}\t{}\t{}".format(msg.timestamp, msg.topic, json.loads(msg_as_str))) - sys.stdout.flush() - except Exception as e: - sys.stderr.write("Cannot parse message: '{}'\n".format(msg_as_str)) - sys.stderr.flush() - - -if __name__ == "__main__": - client = mqtt.Client() - client.on_connect = on_connect - client.on_message = on_message - client.connect("localhost", 1883, 60) - client.loop_forever() diff --git a/power-logger b/power-logger new file mode 100755 index 0000000..827451f --- /dev/null +++ b/power-logger @@ -0,0 +1,122 @@ +#!/usr/bin/env ruby + +# FIXME +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/json-2.3.1/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/mqtt-0.5.0/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/optimist-3.0.1/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/sqlite3-1.4.2/lib/" +$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/zipf-1.2.6/lib/" + +require 'json' +require 'mqtt' +require 'optimist' +require 'sqlite3' +require 'time' +require 'zipf' + +def shutdown + STDERR.write "Shutting down ...\n" + $db.close + $logfile.close + STDERR.write "Done!\n" + exit +end + +Signal.trap("INT") { shutdown } +Signal.trap("TERM") { shutdown } + +def db_open_or_new filename + if File.exists? filename + $db = SQLite3::Database.open filename + else + $db = SQLite3::Database.new filename + $db.execute <<-SQL + create table power_log( + id INTEGER PRIMARY KEY, + timestamp DATETIME, + device_name TEXT, + device_location TEXT, + total FLOAT, + total_start_time DATETIME + ); + SQL + $db.execute <<-SQL + create table power_meter( + id INTEGER PRIMARY KEY, + timestamp DATETIME, + total FLOAT + ); + SQL + end +end + +def db_execute sql, data + 3.times { + begin + $db.execute sql, data + break + rescue SQLite3::BusyException + STDERR.write "DB busy, skipping data point '#{d.to_s}'\n" + sleep 1 + end + } +end + +def parse_topic topic + parts = topic.split "/" + else + _, device_name, _, device_location_1, device_location_2, _ = parts + end + device_location = [device_location_1, device_location_2] + return device_name, device_location +end + +def parse_message message + return JSON.parse message +end + +def insert_power 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 \ + "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 + options = Optimist::options do + opt :host, "MQTT hostname", :type => :string, :default => 'localhost', :short => '-h' + opt :port, "MQTT port", :type => :int, :default => 1883, :short => '-p' + opt :db, "SQLite3 database file", :type => :string, :required => true, :short => '-d' + opt :log, "Logfile", :type => :string, :required => true, :short => '-l' + end + + db_open_or_new options[:db] + + $logfile = WriteFile.new options[:log] + + $mqtt_client = MQTT::Client.connect( + :host => options[:host], + :port => options[:port], + :ssl => false) + $mqtt_client.subscribe '#' + + $mqtt_client.get do |topic,message| + begin + puts "#{topic}\t#{message} + #device_name, device_location = parse_topic topic + #data = parse_message message + #insert_power device_name, device_location, data + #$logfile.write "#{topic}\t#{message}\n" + rescue => exception + $logfile.write "[Ignoring]\t#{topic}\t#{message}\n" + end + end + + shutdown +end + + +main diff --git a/power-logger.rb b/power-logger.rb deleted file mode 100755 index 827451f..0000000 --- a/power-logger.rb +++ /dev/null @@ -1,122 +0,0 @@ -#!/usr/bin/env ruby - -# FIXME -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/json-2.3.1/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/mqtt-0.5.0/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/optimist-3.0.1/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/sqlite3-1.4.2/lib/" -$LOAD_PATH << "/home/pks/.local/lib/ruby/gems/zipf-1.2.6/lib/" - -require 'json' -require 'mqtt' -require 'optimist' -require 'sqlite3' -require 'time' -require 'zipf' - -def shutdown - STDERR.write "Shutting down ...\n" - $db.close - $logfile.close - STDERR.write "Done!\n" - exit -end - -Signal.trap("INT") { shutdown } -Signal.trap("TERM") { shutdown } - -def db_open_or_new filename - if File.exists? filename - $db = SQLite3::Database.open filename - else - $db = SQLite3::Database.new filename - $db.execute <<-SQL - create table power_log( - id INTEGER PRIMARY KEY, - timestamp DATETIME, - device_name TEXT, - device_location TEXT, - total FLOAT, - total_start_time DATETIME - ); - SQL - $db.execute <<-SQL - create table power_meter( - id INTEGER PRIMARY KEY, - timestamp DATETIME, - total FLOAT - ); - SQL - end -end - -def db_execute sql, data - 3.times { - begin - $db.execute sql, data - break - rescue SQLite3::BusyException - STDERR.write "DB busy, skipping data point '#{d.to_s}'\n" - sleep 1 - end - } -end - -def parse_topic topic - parts = topic.split "/" - else - _, device_name, _, device_location_1, device_location_2, _ = parts - end - device_location = [device_location_1, device_location_2] - return device_name, device_location -end - -def parse_message message - return JSON.parse message -end - -def insert_power 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 \ - "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 - options = Optimist::options do - opt :host, "MQTT hostname", :type => :string, :default => 'localhost', :short => '-h' - opt :port, "MQTT port", :type => :int, :default => 1883, :short => '-p' - opt :db, "SQLite3 database file", :type => :string, :required => true, :short => '-d' - opt :log, "Logfile", :type => :string, :required => true, :short => '-l' - end - - db_open_or_new options[:db] - - $logfile = WriteFile.new options[:log] - - $mqtt_client = MQTT::Client.connect( - :host => options[:host], - :port => options[:port], - :ssl => false) - $mqtt_client.subscribe '#' - - $mqtt_client.get do |topic,message| - begin - puts "#{topic}\t#{message} - #device_name, device_location = parse_topic topic - #data = parse_message message - #insert_power device_name, device_location, data - #$logfile.write "#{topic}\t#{message}\n" - rescue => exception - $logfile.write "[Ignoring]\t#{topic}\t#{message}\n" - end - end - - shutdown -end - - -main diff --git a/power-meter b/power-meter index 9ffd7c6..4e05af5 100755 --- a/power-meter +++ b/power-meter @@ -5,24 +5,28 @@ require 'sqlite3' def main db = SQLite3::Database.new ARGV[0] devices = db.execute "select distinct device_location_primary, device_location_secondary FROM power" + puts devices.to_s + exit devices.reject! { |i| not ["office", "living_room", "guest_restroom", "kitchen", "bedroom_2"].include? i[0] } - start_date = Date.new(2020,9,01).to_time.to_i - end_date = Date.new(2020,9,30).to_time.to_i + start_date = Date.new(2021,01,01).to_time.to_i + end_date = Date.new(2021,01,30).to_time.to_i totals = {} totals.default = 0.0 devices.sort_by{|i| i[0] }.each { |device| + puts "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP ASC LIMIT 1" + exit first = db.execute "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP ASC LIMIT 1" last = db.execute "select TOTAL, TIMESTAMP from power WHERE TIMESTAMP >= #{start_date} and TIMESTAMP <= #{end_date} AND DEVICE_LOCATION_PRIMARY = '#{device[0]}' AND DEVICE_LOCATION_SECONDARY = '#{device[1]}' ORDER BY TIMESTAMP DESC LIMIT 1" if first.size > 0 and last.size > 0 - puts "#{Time.at(first[0][1])} --- #{Time.at(last[0][1])}" - puts "#{first[0][0]} ::: #{last[0][0]}" + #puts "#{Time.at(first[0][1])} --- #{Time.at(last[0][1])}" + #puts "#{first[0][0]} ::: #{last[0][0]}" kwh = last[0][0] - first[0][0] - puts "#{device.join '/'}: #{kwh.round 0} kW/h" - puts + #puts "#{device.join '/'}: #{kwh.round 0} kW/h" + #puts totals[device[0]] += kwh end } - puts "=== TOTAL: #{totals.values.inject(:+).round 0} kW/h ===" + puts "TOTAL for #{start_date} - #{end_date}: #{totals.values.inject(:+).round 0} kW/h" puts "\nBy Room" puts "-------" totals.each_key { |k| -- cgit v1.2.3