mrubyでsqlite3

16877 ワード
mruby SQLite3 ZRouter SQLite3 テキストリンク

スマートメーターのデータなどをcsvで保存してjsonに変換してchart.jsでグラフにしているが、snmpなどのデータも取り始めて、sqlite3に変更してみた。

ずっとcsvでも良いかと思っていたのですが、sqlite3を調べてみたところ、ライブラリを22万行のファイル1本で書かれていて、依存もなくちょっとやってみようという気がおきました。

ZRouterのports-modokiに入れてピルドできるようにしました。

mrbgemsはこれを使いました。

csvで処理していた時には、cronから起動させるバッチで定期的にjsonを作っていましたが、sqlite3を使えばcgiで動的に作ることが可能になります。

とりあえずこんな感じでデータを保存しあります。1カラム目が日付で2カラム目が時間です。これらはdefault CURRENT_DATE,CURRENT_TIMEで設定してあります。

sqlite> select * from sm;
2020-04-05|23:50:47|6279.7
2020-04-06|00:00:01|6279.7
2020-04-06|00:10:01|6279.8
2020-04-06|00:20:01|6279.8
2020-04-06|00:30:02|6279.8
2020-04-06|00:40:02|6279.9
2020-04-06|00:50:01|6279.9
2020-04-06|01:00:01|6279.9
2020-04-06|01:10:02|6280.0
2020-04-06|01:20:01|6280.0
2020-04-06|01:30:02|6280.0
2020-04-06|01:40:01|6280.1
2020-04-06|01:50:01|6280.1
2020-04-06|02:00:02|6280.1

cvsの時はJSTで保存していたのですが、sqlite3はデフォルトUTCになるようです。

JSTとUTCの関係はこんな感じです。

JSTの今日はUTCの昨日の15時から今日の15時になります。

結構無理やりですが、今日のメーター値の抜き出しを、こんなコードにしてみました。

#!/usr/local/bin/mruby

db = SQLite3::Database.new('/tmp/ouchi.db')

time = Array.new
val = Array.new

yday = (Time.now - 60*60*24).to_s[0, 10]
tday = Time.now.to_s[0, 10]

i = 0
# select by JST
sel = 'select time, power from sm'
whe = ' where (time>="15:00:00" and date=?) or (time<"15:10:00" and date=?)'

db.execute(sel + whe, yday, tday) do |row, fields|
  time[i] = row[0]
  val[i] = row[1]
i = i + 1
end

print "Content-type: text/json\n\n"

j = 0
tmo = 0
print '{"date":"' + tday + '","type":"sm.log","data":['
while j < i do
  if j != 0
    print ","
  end
  print "["
# convert to JST from UTC
  h = time[j][0, 2].to_i
  if tmo == 0 && h >= 15
    h = h - 15
    hs = "0" + h.to_s
  elsif h == 0
    hs = "09"
    tmo = 1
  else
    h = h + 9
    hs = h.to_s
  end
  print "\"" + hs + time[j][2, 6] + "\"," + val[j].round(1).to_s
  print "]"
  j = j + 1
end
print ']}'

一日の集計は丸一日分のデータと次の日の一つ目のデータが必要なので24時台のデータを入れるようにしてあります。

jsonはバッチで作っていた時と同じフォーマットにして、こんな感じで吐かれます。

{"date":"2020-04-06","type":"sm.log","data":[["08:50:47",6279.7],["09:00:01",6279.7],["09:10:01",6279.8],["09:20:01",6279.8],["09:30:02",6279.8],["09:40:02",6279.9],["09:50:01",6279.9],["10:00:01",6279.9],["10:10:02",6280],["10:20:01",6280],["10:30:02",6280],["10:40:01",6280.1],["10:50:01",6280.1],["11:00:02",6280.1],["11:10:01",6280.2],["11:20:02",6280.2],["11:30:01",6280.2],["11:40:02",6280.3],["11:50:01",6280.3],["12:00:02",6280.3],["12:10:01",6280.4],["12:20:01",6280.4],["12:30:01",6280.4],["12:40:01",6280.4],["12:50:01",6280.5],["13:00:01",6280.5],["13:10:01",6280.5],["13:20:02",6280.6],["13:30:02",6280.6],["13:40:01",6280.6],["13:50:01",6280.7]]}

chart.jsでこんなグラフにしてます。

JSTのcsvファイルをUTCのcsvファイルに変換するプログラムを作って見ました。

def jst2utc d, t
  y = d[0, 4].to_i
  m = d[6, 2].to_i
  d = d[8, 2].to_i
  h = t[0, 2].to_i
  mi = t[3, 2].to_i
  s = t[6, 2].to_i
  u = Time.local(y, m, d, h, mi, s).utc
  [u.to_s[0, 10], u.to_s[11, 8]]
end

fd = File.open("sm.log")

while line = fd.gets do
  arr = line.chop.split(",")
  d, t = jst2utc arr[0], arr[1]
  print d + "," + t + "," + arr[2] + "\n"
end

データファイルサイズはcsvが240530バイトに対してsqlite3が323584になっていました。

日ごと使用量を調べるために日が変わった最初のデータを拾って引き算しています。

select date, power from sm where time>"15:00:00" and time<"15:00:10" order by date

これはあまりよくなくて、15:00:00のデータ取得に失敗していた場合には、数字がおかしくなります。

select date, power from sm as A where A.time in (select B.time from sm as B where B.date=A.date and time > "15:00:00" limit 1)

すると日が変わって最初のデータを拾えるようなので、問題起きないのですが、結構重いです。

以下のようにするとJSTに変換されるようです。

select datetime(date, time, 'localtime'), power from sm;

rubyもsqlも素人なので、もっと良い方法があると思うので、ご指摘ください。