General Text Device: which table in the database?

[edgarhildering]

Ok, I think the subject is pretty clear on my question. My general text device receives device status messages via MQTT and I want to clear these messages if older than 14 days... But where are they in the database ? Or is there a setting so that this is done automaticly ?

kind regards, --Edgar

[HansieNL]

I'm using a dzvents script to fill a variable and text device.
The data in the text device will be removed if older than 20 minutes.
Maybe you can use some parts of this script to create your own script:

Code: Select all

-- dzuptime.lua
-- 
return {
    on = {   timer  =   {"every minute"}},

    execute = function(dz)
        local uptimeTextDevice = dz.devices("Domoticz Uptime")
        local uptimeUserVariable = dz.variables("Uptime")

        local function deleteTextLog(idx)
            dz.openURL(dz.settings['Domoticz url'] .. '/json.htm?type=command&param=clearlightlog&idx=' .. idx)
        end

        local minutes = dz.startTime.minutesAgo%60
        local days    = dz.startTime.daysAgo
        local hours   = dz.startTime.hoursAgo%24

        if days == 1 then DagenText = " dag" else DagenText = " dagen" end
        if minutes == 1 then MinutenText = " minuut" else MinutenText = " minuten" end

        uptimeTextDevice.updateText(days ..DagenText.. ", " .. hours .. " uur en " .. minutes ..MinutenText)
        uptimeUserVariable.set(days ..DagenText.. ", " .. hours .. " uur en " .. minutes ..MinutenText)

        if dz.time.matchesRule('every 20 minutes') then -- change to your liking
            deleteTextLog(uptimeTextDevice.id)
        end
    end
}

[edgarhildering]

As I understand it you remove the contents of the text variable if it is older than 20 minutes. But I get feedback from different switches and I want to keep these different feedbacks for at least 24 hours. So: every 5-10 minutes the content of the switch changes. I see there's at least a month in the database now and that's a long time...

Or... do I understand it incorrectly?



--Edgar

[edgarhildering]

The script helped me after all... I discovered that the table containing the messages is LightingLog; not where the name invited me to look...
I've detected 14000 messages as a result of 1 month of operation.

I think i need to create a script to remove excessive messages from the database...

Thanks for all the help,

--Edgar

[waltervl]

I think (but not sure) that these logs will automatically be purged with the setting Lights/switches log (default 30 days)

https://www.domoticz.com/wiki/Applicati ... og_History

[edgarhildering]

Although I am not a promotor of changing the database through the back door, here is the statement to do some cleanup.

Code: Select all

delete from LightingLog where DeviceRowID=<insert-ID-here> and date < date("now","-7 days");

[waltervl]

Did you check if the log history setting had some impact on the text device log history?

[edgarhildering]

I did not check it. However, I have my history log settings on 30 days for light switches, short log sensors on 7 days and that is ok. The general text switch turned out to behave like a light switch; i'd rather see it behave as a shortlog sensor!

--Edgar

[waltervl]

You are free to modify the source code and make a Pull Request (on github) to update