Here’s a much easier way to access dataMine data using the dmDB module… an HTTP server which responds to queries with CSV time/value pairs for a specified channel over a specified time range.
Attached to this post is an updated version of the dmDB module, which can simply be loaded onto Vera using the Luup files menu on the Develop Apps page.
The following server code can then be added to your Startup Lua, or just tried out in the Test Luup code section.
require "dmDB"
dm, dmDBstatus = dmDB.open ()
luup.log (dmDBstatus)
luup.log ('dmDB version = '..dmDB.version)
----
--dmDB_handler() responds to requests like:
-- [your_Vera_url]:3480/data_request?id=lr_getSearchKeyRange
--&output_format=csv
--&DeviceNum=7
--&serviceId=urn:upnp-org:serviceId:Dimming1
--&Variable=Test
--&From=42
--&To=88
function dmDB_handler (lul_request, lul_parameters, lul_outputformat)
local max_points = 10080 -- save Vera from running out of memory
local cursorKey, keyRange = {}, {}
local function query ()
local keys = {
serviceid = "Service", service = "Service", -- openCursor keys and aliases
devicenum = "Device", device = "Device",
id = "Id", channel = "Id",
variable = "Variable", name = "Name"}
local times = {
t1 = "t1", from = "t1", start = "t1", -- searchKeyRange parameters and aliases
t2 = "t2", to = "t2", stop = "t2"}
local k,l,t
for i,j in pairs (lul_parameters) do
l = string.lower(i)
k = keys[l]
t = times[l]
if k then cursorKey[k] = j elseif t then keyRange[t] = tonumber(j) end -- ignored if neither key nor time
end
local dmc, status = dm:openCursor {key = cursorKey}
if dmc then
local n = 0
local cpu = os.clock()
status = {}
for v,t in dmc:searchKeyRange (keyRange) do
n = n + 1
status[n] = ("%d,%g"): format (t,v) -- currently use csv as default format
if n == max_points then break end
end
status = table.concat (status, '\n')
luup.log (("CPU time for request %s = %.3f mS for %d points"): format (lul_request,(os.clock()-cpu)*1e3, n))
dmc:close ()
end
return status
end
local lul_content_type = "text/plain"
local ok, result = pcall (query)
return result, lul_content_type
end
luup.register_handler ("dmDB_handler", "getSearchKeyRange")
Using the default json-dm module, this startup code can take about 10 seconds to run, but this can be changed by editing the line:
dm, dmDBstatus = dmDB.open ()
to
dm, dmDBstatus = dmDB.open {json = "your-favourite-json-module" }
…the one I use is about 20 times faster. However, once open, the actual queries don’t depend on the speed of the json module.
The server responds to HTTP requests of the form:
http://[your Vera IP address]:3480/data_request?id=lr_getSearchKeyRange&Variable=Varname&DeviceNum=42&from=1376356271&to=1377572571
with a comma-separated list of the data (time/value pairs) within the given range of (UNIX) time stamps of the sort:
1376356271,0.275625
1376356331,0.278264
1376356391,0.280903
1376356452,0.283542
1376356512,0.286181
1376356572,0.288819
The possible request parameters and the meaning of their values are:
[ul][li][tt]Service[/tt] or [tt]ServiceId[/tt] - ServiceId[/li]
[li][tt]Variable[/tt] - Luup variable name[/li]
[li][tt]Device[/tt] or [tt]DeviceNum[/tt] - Device number[/li]
[li][tt]Id[/tt] or [tt]Channel[/tt] - dataMine channel number[/li]
[li][tt]Name[/tt] - dataMine channel name[/li]
[li][tt]T1[/tt] or [tt]From[/tt] or [tt]Start[/tt] - start Unix timestamp[/li]
[li][tt]T2[/tt] or [tt]To[/tt] or [tt]Stop[/tt] - ending Unix timestamp[/li][/ul]
As with the dmDB module itself, any mix of the above parameters may be defined, in any order, and their values (not their names listed above) can be truncated, so long as the whole query uniquely identifies a dataMine channel being logged. Usually sufficient is Luup variable name and device number. The T1/T2 or start/stop or from/to parameters define the time window of interest. T1 defaults to the earliest time in the database, and T2 defaults to the current time.
To save yourself from crashing Vera through using up all the memory, I’ve arbitrarily limited the number of returned points to a maximum of 10,080. This runs perfectly happily on my VeraLite and allows queries to span, for example:
[ul][li]1 year @ 1 hour sample rate = 8,760[/li]
[li]1 month @ 5 minutes sample rate = 8,928 (for 31 days)[/li]
[li]1 week @ 1 minute sample rate = 10,080[/li][/ul]
I hope this is of some use to those wishing to do some real data mining, especially as it opens up the possibility of using a separate machine to crunch the numbers subsequently. My goal is still to wrap this up in another layer of code (and, no doubt, a plugin) which will provide real statistical data queries and resampling capabilities, with a variety of response formats.
Feedback welcomed.
