Warning!!
Please make sure you have a recent and tested backup as this script potentially
deletes many rows from the history tables (on my test system database size went from 23 MB to 6 MB)
Code: Select all
--[[
Script to delete history data from database of the devices for which you don't want any historylog
requires domoticz V2020.2 build >= 13027
Warning!! Please make sure you have a recent and tested backup as this script potentially
deletes many rows from the history tables (on my test system database size went from 23 MB to 6 MB)
there are two ways to have this script ignore devices (and thus do not touch its history)
1. Add device ID to the comma separated variable "excludeDeviceString"
2. Add the string "ExcludeFromHistoryDelete" to the description field of the device
History:
20210301 initial release
20200302 Split JSON in used and unused to minimize risk of too many lines
]]--
local scriptVar = 'deleteHistory_'
return
{
on =
{
timer =
{
'at 1:53', -- Preferable at the the start of a quiet period
},
customEvents =
{
scriptVar,
},
httpResponses =
{
scriptVar .. '*',
},
},
logging =
{
level = domoticz.LOG_DEBUG, -- change to domoticz.LOG_ERROR when all ok
marker = scriptVar,
},
execute = function(dz, item)
--- enter below the comma separated list of devices that are to be excluded from deleteHistory
--- Or enter the string 'ExcludeFromHistoryDelete' anywhere in the device description
local excludeDeviceString = '3,4,5,6,10,446,447,834,835,192'
--- No changes required below this line
local function getDevices(used)
dz.openURL(
{
url = dz.settings['Domoticz url'] .. '/json.htm?type=devices&displayhidden=1&used=' .. tostring(used == 'used'),
callback = scriptVar .. used,
})
end
local function deleteSwitchLogHistory(idx, delay)
dz.openURL(dz.settings['Domoticz url'] .. '/json.htm?type=command¶m=clearlightlog&idx=' .. idx).afterSec(delay)
return delay + 2 -- Give command some time to be processed before next one
end
local function deleteCalendarHistory(idx, delay )
dz.openURL
(
dz.settings['Domoticz url'] .. '/json.htm?type=command¶m=deletedaterange'..
'&idx=' .. idx ..
'&fromdate=2010-01-01' ..
'&todate=9999-12-31'
).afterSec(delay)
return delay + 5 -- Give command some time to be processed before next one
end
local function vacuumDatebase(delay)
dz.log(delay, dz.LOG_DEBUG)
dz.openURL(dz.settings['Domoticz url'] .. '/json.htm?type=command¶m=vacuumdatabase').afterSec(delay + 10 )
end
local function hasCalendarHistory(dv)
return not(dv.Type:lower():find('switch') or dv.SubType:lower():find('switch') or dv.SubType =='AC' or dv.deviceSubType == 'Text')
end
local function createExcludedDevicesTable()
local excludeDevices = {}
for id in excludeDeviceString:gmatch("%d*") do
excludeDevices[id] = true
end
return excludeDevices
end
local function processResult(rt)
local delay = 0
local excludeDevices = createExcludedDevicesTable()
for key, dv in ipairs(rt) do
if excludeDevices[dv.idx] or dv.Description:find('ExcludeFromHistoryDelete') then
dz.log('Exclude: '.. dv.idx .. ' -->> ' .. dv.Name .. ': ' .. dv.Type .. ', ' ..dv.SubType .. ', ' .. dv.Description, dz.LOG_DEBUG)
elseif hasCalendarHistory(dv) then
dz.log('Calendar: '.. dv.idx .. ' -->> ' .. dv.Name .. ': ' .. dv.Type .. ', ' ..dv.SubType .. ', ' .. dv.Description, dz.LOG_DEBUG)
delay = deleteCalendarHistory(dv.idx, delay)
else
dz.log('Switch: '.. dv.idx .. ' -->> ' .. dv.Name .. ': ' .. dv.Type .. ', ' ..dv.SubType .. ', ' .. dv.Description, dz.LOG_DEBUG)
delay = deleteSwitchLogHistory(dv.idx, delay)
end
end
return delay
end
-- main
if item.isTimer then
getDevices('used')
elseif item.isCustomEvent then
getDevices('unused')
elseif not(item.json) then
dz.log('Problem retrieving data ' , dz.LOG_ERROR)
else -- httpResponse
local delay = processResult(item.json.result)
if item.trigger:find('unused') then
vacuumDatebase(delay)
else
dz.emitEvent(scriptVar).afterSec(delay)
end
end
end
}
The host is a Synology and Domoticz is a package compiled by Jadahl.
Sorry for that.
