search

Mohlsson / ReplayLightsHistory

AppDaemon App for Home Assistant to replay light switch history when no one is home.
Apache License 2.0
29 stars 5 forks source link

Stopped working some time in the past 4 months #27

Closed HEAT16 closed 1 year ago

HEAT16 commented 1 year ago

Previously I had this working fine for the past few years, but recently I was away and it was not turning on/off any of the lights in the house. When checking the appdaemon logs it would say "replaying history" over and over but never list anything it was doing or any exclusions. I haven't changed anything with the configuration so not really sure what's up. I can provide any logs or screenshots anyone needs to help me troubleshoot.

Mohlsson commented 1 year ago

Hi, I haven't had a look at the logs for quite a while and haven't been away from my house for any longer periods recently. Now that you mention it I see the same problem. I will take a look and see what has changed in home assistant. My bet is that the on/off events are stored slightly different in the database now. I'll get back with a fix as soon as I have the extra time to dig in to this.

Mohlsson commented 1 year ago

I have now had some time to check this. It seems there has been changes how events are stored in the database. I have made changes to the events lookup for SQLite databases. If you use MariaDB the same changes may have to be done for that case. Unfortunately I can not test as I use SQLite in my setup.

HEAT16 commented 1 year ago

I'm also using SQLite, so I can verify this week, but can't help with MariaDB either.

teskanoo commented 1 year ago

I created a view (with some additional columns) in my MariaDB Database and modified the AppDaemon App to use that instead if the inline query


create or replace view view_states_replay as
select entity_id
     , substr(service, 6) as state
     , created
     , domain
     , service
     , service_data
from (
         select TRIM(BOTH '"' FROM (json_extract(shared_data, '$.domain')))       as domain
              , TRIM(BOTH '"' FROM (json_extract(shared_data, '$.service')))      as service
              , TRIM(BOTH '"'
                     FROM (
                         TRIM(BOTH '['
                              FROM (
                                  TRIM(BOTH ']'
                                       FROM (json_extract(shared_data, '$.service_data.entity_id')))
                                  )
                             )
                         )
             )                                                                    as entity_id
              , TRIM(BOTH '"' FROM (json_extract(shared_data, '$.service_data'))) as service_data
              , e.time_fired                                                      as created
         from events e
                  inner join event_data ed on e.data_id = ed.data_id
         where e.event_type = 'call_service'
     ) as x
where service in ('turn_on', 'turn_off')
order by created

image