Open source Home Automation System
https://forum.domoticz.com/
That is correctCharlesL wrote:It looks (but I can't be completely sure) as if Domoticz stores everything (configuration, scenes, measurements) in one single database file: /home/pi/domoticz/domoticz.db
It's a SQLite databaseCharlesL wrote:Does anyone know what format this file is?
I use SQLIteman on Linux, there are also Windows tools, google is your friendCharlesL wrote:Are there any standalone applications that can read it?
no, search the forum for more informationCharlesL wrote:More in general: is there any documentation on Domoticz at a level between the end-user manual and the source code?
Code: Select all
pi@raspberrypi ~ $ sqlite3 /home/pi/domoticz/domoticz.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
BackupLog Meter Scenes
Cameras Meter_Calendar SetpointTimers
CamerasActiveDevices MobileDevices SharedDevices
CustomImages MultiMeter Temperature
DeviceStatus MultiMeter_Calendar Temperature_Calendar
DeviceToPlansMap MySensors Timers
EnoceanSensors MySensorsChilds ToonDevices
EventActions MySensorsVars UV
EventMaster Notifications UV_Calendar
EventRules Percentage UserSessions
Fan Percentage_Calendar UserVariables
Fan_Calendar Plans Users
FibaroLink Preferences WOLNodes
Floorplans Rain Wind
Hardware Rain_Calendar Wind_Calendar
HttpLink SceneDevices ZWaveNodes
LightSubDevices SceneLog
LightingLog SceneTimers
sqlite> .schema Temperature
CREATE TABLE [Temperature] ([DeviceRowID] BIGINT(10) NOT NULL, [Temperature] FLOAT NOT NULL, [Chill] FLOAT DEFAULT 0, [Humidity] INTEGER DEFAULT 0, [Barometer] INTEGER DEFAULT 0, [DewPoint] FLOAT DEFAULT 0, [SetPoint] FLOAT DEFAULT 0, [Date] DATETIME DEFAULT (datetime('now','localtime')));
CREATE INDEX t_idx on Temperature(DeviceRowID);
sqlite> .quit
Code: Select all
-- ********************************************************************************
--
-- Called every minute, records current values of device list every 5 minutes
-- 1) yeah, current, no avg - want it simple, doesn't need more
-- 2) won't see less than 5 min events - sees switches if they last longer than 5min
--
-- comma "," is the separator => european users may double click csv files to Excel
-- Date and Time in 2 different columns, Excel treats days oddly but hours ok,
-- "plot.ly" (see below) recognizes them and concatenate them nicely (thanks, plotly).
--
-- You can mix all kind of devices in device list (temps, switch, meteo, temphum,
-- thermostat, text; etc...)
--
-- easily used with https://plot.ly/ (great, bit slow to load, free for personnal use, ++)
--
-- switch On Off are replaced with 15-0 to be clearly seen with temps around 20°C
--
-- ********************************************************************************
-- Variables
fichierJournalier="/var/tmp/daily.csv" -- actually, mine is on a tmpfs dir (RAM Drive)
fichierMensuel="/mnt/pc/monthly.csv" -- on cifs dir, shared with Windows
devices = {"Température Chambre"
, "Température Salle de Bain"
, "Thermostat Chambre"
, "Radiateur Chambre 1"
, "Temp dehors"
, "Confort Chambre"
, "Météo Baromètre"
, "Thermostat Salon"
-- , "JourRouge"
, "Absence"
-- , "JourFerie"
-- , "JourDeSemaine"
-- , "CouleurDuJour"
-- , "CouleurDemain"
, "Prise 2 (Box)"
, "Prise 3 (Ordinateur)"
, "Prise 4"
, "TempHum1"
, "TempHum2"
-- , "Arrosage1"
-- , "Arrosage2"
}
-- ********************************************************************************
commandArray = {}
print('Script v01 strip_time_CSV.lua')
function debug(m)
print("......CSV "..m)
end
now=os.date("*t")
-- ça commence ici
-- ********************************************************************************
-- debug ("TempHum1="..otherdevices_svalues['TempHum1'])
-- debug ("Météo Baromètre="..otherdevices_svalues["Météo Baromètre"])
if now.min % 5 == 0 then
-- debug("début")
-- create file if needed : write headers to file
f=io.open(fichierJournalier, "r")
if f == nil then
-- debug("création du fichier journalier")
f=io.open("/tmp/a.txt", "w")
f:write("Date,Time") -- separator = comma
for i,d in ipairs(devices) do
-- special treatment for headers of multi valued datas :
-- check data type
v=otherdevices[d] -- v is the value attached to device d
debug("d="..d.."="..(v or "nil"))
if v==nil or v=="" then -- multi valued ?
v=otherdevices_svalues[d] or ""
--debug("d="..d.."="..v)
v,nbCommas=string.gsub(v,";",",")
if nbCommas==0 then
f:write(",", d)
else -- write it as Meteo 1, Meteo 2, ...
for i=1,nbCommas+1 do f:write(",", d.." "..i) end
end
else
f:write(",", d) -- separator = comma
end
end
f:write("\n")
f:close()
-- made to go on a cifs directory, shared with Windows
os.execute("iconv -f utf8 -t ISO-8859-1 /tmp/a.txt > '"..fichierJournalier.."'")
os.execute("chmod 666 '"..fichierJournalier.."'")
else
f:close()
end -- ok, header is now created
-- do the stuff (generate datas)
-- debug("génération des données")
f=io.open(fichierJournalier, "a")
f:write(os.date("%Y-%m-%d,%H:%M")) -- separator = comma
for i,d in ipairs(devices) do
v=otherdevices[d]
-- debug("d="..d.."="..(v or "nil"))
if v==nil or v=="" then -- multi valued ?
v=otherdevices_svalues[d] or ""
v=string.gsub(v, ";", ",") -- separator = comma
end
v=string.gsub(v,"On","15") -- yeah, On=15, Off=0
v=string.gsub(v,"Off","0")
f:write(",", v) -- separator = comma
end
f:write("\n")
f:close()
-- once a day, concat daily file to monthly file,
-- 1) could be any TIME COMPATIBLE WITH % 5 ABOVE
-- 2) if fichierMensuel is on a cifs dir, you have to choose
-- a time where your PC is on.
if now.hour == 20 and now.min == 35 then
debug("concaténation dans le fichier mensuel")
f=io.open(fichierMensuel, "r")
if f == nil then
os.execute("head -n 1 '"..fichierJournalier.."' >> '"..fichierMensuel.."'")
else
f:close()
end
os.execute("tail -n +2 '"..fichierJournalier.."' >> '"..fichierMensuel.."'")
fMensuelOk=io.open(fichierMensuel, "r")
if fMensuelOk == nil then
debug("Pas de suppression car impossible de lire "..fichierMensuel)
else
os.execute("/bin/rm -f '"..fichierJournalier.."'")
debug("Suppression de "..fichierJournalier)
fMensuelOk:close()
end
end
end
-- ********************************************************************************
-- la derniere ligne est obligatoire !
return commandArray
Code: Select all
for i,d in ipairs(devices) do
v=otherdevices[d]
... use v
end -- of loop