#!/usr/bin/lua local uci = require("uci") local cur = uci.cursor() local json = require "json" local logdb = arg[1] require "wm_util" if not logdb then print("no sqlite log defined!") return end function shallowcopy(orig) local orig_type = type(orig) local copy if orig_type == 'table' then copy = {} for orig_key, orig_value in pairs(orig) do copy[orig_key] = orig_value end else -- number, string, boolean, etc copy = orig end return copy end function median(dataset) table.sort(shallowcopy(dataset)) return dataset[math.floor(#dataset/2)] end function filter_data(dataset,width) if not width then width = 7 end if #dataset <= width then return dataset end local result = {} local window_spread = math.floor(width/2) local window = {} for i = 1,window_spread do window[#window+1] = dataset[i]["y"] end for key,value in pairs(dataset) do nextelem = dataset[key+window_spread] if nextelem then window[#window+1] = nextelem["y"] end if not nextelem or #window>width then table.remove(window,1) end row = {} row["t"]=value["t"] row["y"]=median(window) result[#result+1] = row end return result end function average_results(dataset,con) local name = os.tmpname() touch(name) local tmpcon = assert(env:connect(name)) assert(tmpcon:execute("create table series(time_stamp datetime,value float)")) for key,value in pairs(dataset) do assert(tmpcon:execute(string.format("INSERT INTO series(time_stamp,value) VALUES ('%s','%s')",value["t"],value["y"]))) end local sql = "select rounded as t,avg(value) as y from (select substr(strftime('%Y-%m-%dT%H:%M',time_stamp),1,15)||'5:00' rounded,value from series) group by rounded order by rounded" results = run_sql(sql,tmpcon) tmpcon:close() os.remove(name) return results end function run_sql(sql,con) local result = {} cursor = assert(con:execute(sql)) row = cursor:fetch ({}, "a") while row do result[#result+1] = row row = cursor:fetch ({}, "a") end return result end function get_list(day,con) if day == "-" then sql = string.format("SELECT DISTINCT sensor_id,sensor,param FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') ORDER BY sensor_id,sensor,param") else sql = string.format("SELECT DISTINCT sensor_id,sensor,param FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') ORDER BY sensor_id,sensor,param",day,day) end return run_sql(sql,con) end function get_raw(day,con,sensor_id,sensor_type,param) format = '%Y-%m-%dT%H:%M:%S' if day == "-" then sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,sensor_id,sensor_type,param) else sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,day,day,sensor_id,sensor_type,param) end return run_sql(sql,con) end function get_filtered(day,con,sensor_id,sensor_type,param,width) format = '%Y-%m-%dT%H:%M:%S' if day == "-" then sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>=datetime('now','-1 day','localtime') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,sensor_id,sensor_type,param) else sql = string.format("SELECT strftime('%s',time_stamp) as t,value as y FROM log WHERE time_stamp>='%s' and time_stamp<date('%s','+1 day') and sensor_id='%s' and sensor='%s' and param='%s' ORDER BY time_stamp",format,day,day,sensor_id,sensor_type,param) end return filter_data(run_sql(sql,con),width) end function dump_json(dataset,file) local f if file then f = io.open(file,"w") io.output(f) end io.write(json.encode(dataset)) if f then io.close(f) end end function dump_txt(dataset,file) local f if file then f = io.open(file,"w") io.output(f) end for key,row in pairs(dataset) do io.write(row["t"].." "..row["y"].."\n") end if f then io.close(f) end end function dump_list(dataset,file) local f if file then f = io.open(file,"w") io.output(f) end for key,row in pairs(dataset) do io.write(row["sensor_id"].." "..row["sensor"].." "..row["param"].."\n") end if f then io.close(f) end end local command = arg[2] local day = arg[3] local dbdriver = require "luasql.sqlite3" env = assert(dbdriver.sqlite3()) con = assert(env:connect(logdb,'READONLY')) if command == "list" then dump_list(get_list(day,con)) elseif command == "get" then sensor_id = arg[4] sensor_type = arg[5] param = arg[6] dump_txt(get_raw(day,con,sensor_id,sensor_type,param)) elseif command == "get-filtered" then sensor_id = arg[4] sensor_type = arg[5] param = arg[6] width = arg[7] if not width then width = 5 end dataset =get_filtered(day,con,sensor_id,sensor_type,param) dump_txt(dataset) elseif command == "get-compacted" then sensor_id = arg[4] sensor_type = arg[5] param = arg[6] width = arg[7] if not width then width = 5 end dataset =get_filtered(day,con,sensor_id,sensor_type,param) dump_txt(average_results(dataset)) elseif command == "dump" then local directory = arg[4] if not directory then directory = "." end list = get_list(day,con) dump_json(list,directory.."/sensors.json") for key,value in pairs(list) do dump_json(get_filtered(day,con,value["sensor_id"],value["sensor"],value["param"]),directory.."/"..value["sensor_id"].."."..value["sensor"].."."..value["param"]..".json") end elseif command == "dump-compacted" then local directory = arg[4] if not directory then directory = "." end list = get_list(day,con) dump_json(list,directory.."/sensors.json") for key,value in pairs(list) do dump_json(average_results(get_filtered(day,con,value["sensor_id"],value["sensor"],value["param"])),directory.."/"..value["sensor_id"].."."..value["sensor"].."."..value["param"]..".json") end end