def state_changes_during_period(opp,
start_time,
end_time=None,
entity_id=None):
"""Return states changes during UTC period start_time - end_time."""
with session_scope(opp=opp) as session:
baked_query = opp.data[HISTORY_BAKERY](
lambda session: session.query(*QUERY_STATES))
baked_query += lambda q: q.filter(
(States.last_changed == States.last_updated)
& (States.last_updated > bindparam("start_time")))
if end_time is not None:
baked_query += lambda q: q.filter(States.last_updated < bindparam(
"end_time"))
if entity_id is not None:
baked_query += lambda q: q.filter_by(entity_id=bindparam(
"entity_id"))
entity_id = entity_id.lower()
baked_query += lambda q: q.order_by(States.entity_id, States.
last_updated)
states = execute(
baked_query(session).params(start_time=start_time,
end_time=end_time,
entity_id=entity_id))
entity_ids = [entity_id] if entity_id is not None else None
return _sorted_states_to_dict(opp, session, states, start_time,
entity_ids)
def get_last_state_changes(opp, number_of_states, entity_id):
"""Return the last number_of_states."""
start_time = dt_util.utcnow()
with session_scope(opp=opp) as session:
baked_query = opp.data[HISTORY_BAKERY](
lambda session: session.query(*QUERY_STATES))
baked_query += lambda q: q.filter(States.last_changed == States.
last_updated)
if entity_id is not None:
baked_query += lambda q: q.filter_by(entity_id=bindparam(
"entity_id"))
entity_id = entity_id.lower()
baked_query += lambda q: q.order_by(States.entity_id,
States.last_updated.desc())
baked_query += lambda q: q.limit(bindparam("number_of_states"))
states = execute(
baked_query(session).params(number_of_states=number_of_states,
entity_id=entity_id))
entity_ids = [entity_id] if entity_id is not None else None
return _sorted_states_to_dict(
opp,
session,
reversed(states),
start_time,
entity_ids,
include_start_time_state=False,
)
def _load_history_from_db(self):
"""Load the history of the brightness values from the database.
This only needs to be done once during startup.
"""
start_date = datetime.now() - timedelta(days=self._conf_check_days)
entity_id = self._readingmap.get(READING_BRIGHTNESS)
if entity_id is None:
_LOGGER.debug("Not reading the history from the database as "
"there is no brightness sensor configured")
return
_LOGGER.debug("Initializing values for %s from the database",
self._name)
with session_scope(opp=self.opp) as session:
query = (session.query(States).filter(
(States.entity_id == entity_id.lower())
and (States.last_updated > start_date)).order_by(
States.last_updated.asc()))
states = execute(query, to_native=True, validate_entity_ids=False)
for state in states:
# filter out all None, NaN and "unknown" states
# only keep real values
with suppress(ValueError):
self._brightness_history.add_measurement(
int(state.state), state.last_updated)
_LOGGER.debug("Initializing from database completed")
def test_recorder_bad_execute(opp_recorder):
"""Bad execute, retry 3 times."""
from sqlalchemy.exc import SQLAlchemyError
opp_recorder()
def to_native():
"""Rasie exception."""
raise SQLAlchemyError()
mck1 = MagicMock()
mck1.to_native = to_native
with pytest.raises(SQLAlchemyError), patch(
"openpeerpower.components.recorder.time.sleep") as e_mock:
util.execute((mck1, ))
assert e_mock.call_count == 2
def _get_single_entity_states_with_session(opp, session, utc_point_in_time,
entity_id):
# Use an entirely different (and extremely fast) query if we only
# have a single entity id
baked_query = opp.data[HISTORY_BAKERY](
lambda session: session.query(*QUERY_STATES))
baked_query += lambda q: q.filter(
States.last_updated < bindparam("utc_point_in_time"),
States.entity_id == bindparam("entity_id"),
)
baked_query += lambda q: q.order_by(States.last_updated.desc())
baked_query += lambda q: q.limit(1)
query = baked_query(session).params(utc_point_in_time=utc_point_in_time,
entity_id=entity_id)
return [LazyState(row) for row in execute(query)]
def get_significant_states(
opp,
start_time,
end_time=None,
entity_ids=None,
filters=None,
include_start_time_state=True,
):
"""
Return states changes during UTC period start_time - end_time.
Significant states are all states where there is a state change,
as well as all states from certain domains (for instance
thermostat so that we get current temperature in our graphs).
"""
timer_start = time.perf_counter()
with session_scope(opp=opp) as session:
query = session.query(States).filter(
(States.domain.in_(SIGNIFICANT_DOMAINS)
| (States.last_changed == States.last_updated))
& (States.last_updated > start_time))
if filters:
query = filters.apply(query, entity_ids)
if end_time is not None:
query = query.filter(States.last_updated < end_time)
query = query.order_by(States.last_updated)
states = (state for state in execute(query)
if (_is_significant(state)
and not state.attributes.get(ATTR_HIDDEN, False)))
if _LOGGER.isEnabledFor(logging.DEBUG):
elapsed = time.perf_counter() - timer_start
_LOGGER.debug("get_significant_states took %fs", elapsed)
return states_to_json(opp, states, start_time, entity_ids, filters,
include_start_time_state)
async def _async_initialize_from_database(self):
"""Initialize the list of states from the database.
The query will get the list of states in DESCENDING order so that we
can limit the result to self._sample_size. Afterwards reverse the
list so that we get it in the right order again.
If MaxAge is provided then query will restrict to entries younger then
current datetime - MaxAge.
"""
_LOGGER.debug("%s: initializing values from the database", self.entity_id)
with session_scope(opp=self.opp) as session:
query = session.query(States).filter(
States.entity_id == self._entity_id.lower()
)
if self._max_age is not None:
records_older_then = dt_util.utcnow() - self._max_age
_LOGGER.debug(
"%s: retrieve records not older then %s",
self.entity_id,
records_older_then,
)
query = query.filter(States.last_updated >= records_older_then)
else:
_LOGGER.debug("%s: retrieving all records", self.entity_id)
query = query.order_by(States.last_updated.desc()).limit(
self._sampling_size
)
states = execute(query, to_native=True, validate_entity_ids=False)
for state in reversed(states):
self._add_state_to_queue(state)
self.async_schedule_update_op_state(True)
_LOGGER.debug("%s: initializing from database completed", self.entity_id)
def state_changes_during_period(opp,
start_time,
end_time=None,
entity_id=None):
"""Return states changes during UTC period start_time - end_time."""
with session_scope(opp=opp) as session:
query = session.query(States).filter(
(States.last_changed == States.last_updated)
& (States.last_updated > start_time))
if end_time is not None:
query = query.filter(States.last_updated < end_time)
if entity_id is not None:
query = query.filter_by(entity_id=entity_id.lower())
entity_ids = [entity_id] if entity_id is not None else None
states = execute(query.order_by(States.last_updated))
return states_to_json(opp, states, start_time, entity_ids)
def get_last_state_changes(opp, number_of_states, entity_id):
"""Return the last number_of_states."""
start_time = dt_util.utcnow()
with session_scope(opp=opp) as session:
query = session.query(States).filter(
(States.last_changed == States.last_updated))
if entity_id is not None:
query = query.filter_by(entity_id=entity_id.lower())
entity_ids = [entity_id] if entity_id is not None else None
states = execute(
query.order_by(States.last_updated.desc()).limit(number_of_states))
return states_to_json(opp,
reversed(states),
start_time,
entity_ids,
include_start_time_state=False)
def _get_significant_states(
opp,
session,
start_time,
end_time=None,
entity_ids=None,
filters=None,
include_start_time_state=True,
significant_changes_only=True,
minimal_response=False,
):
"""
Return states changes during UTC period start_time - end_time.
Significant states are all states where there is a state change,
as well as all states from certain domains (for instance
thermostat so that we get current temperature in our graphs).
"""
timer_start = time.perf_counter()
baked_query = opp.data[HISTORY_BAKERY](
lambda session: session.query(*QUERY_STATES))
if significant_changes_only:
baked_query += lambda q: q.filter(
(States.domain.in_(SIGNIFICANT_DOMAINS)
| (States.last_changed == States.last_updated))
& (States.last_updated > bindparam("start_time")))
else:
baked_query += lambda q: q.filter(States.last_updated > bindparam(
"start_time"))
if entity_ids is not None:
baked_query += lambda q: q.filter(
States.entity_id.in_(bindparam("entity_ids", expanding=True)))
else:
baked_query += lambda q: q.filter(~States.domain.in_(IGNORE_DOMAINS))
if filters:
filters.bake(baked_query)
if end_time is not None:
baked_query += lambda q: q.filter(States.last_updated < bindparam(
"end_time"))
baked_query += lambda q: q.order_by(States.entity_id, States.last_updated)
states = execute(
baked_query(session).params(start_time=start_time,
end_time=end_time,
entity_ids=entity_ids))
if _LOGGER.isEnabledFor(logging.DEBUG):
elapsed = time.perf_counter() - timer_start
_LOGGER.debug("get_significant_states took %fs", elapsed)
return _sorted_states_to_dict(
opp,
session,
states,
start_time,
entity_ids,
filters,
include_start_time_state,
minimal_response,
)
def _get_states_with_session(opp,
session,
utc_point_in_time,
entity_ids=None,
run=None,
filters=None):
"""Return the states at a specific point in time."""
if entity_ids and len(entity_ids) == 1:
return _get_single_entity_states_with_session(opp, session,
utc_point_in_time,
entity_ids[0])
if run is None:
run = recorder.run_information_with_session(session, utc_point_in_time)
# History did not run before utc_point_in_time
if run is None:
return []
# We have more than one entity to look at (most commonly we want
# all entities,) so we need to do a search on all states since the
# last recorder run started.
query = session.query(*QUERY_STATES)
most_recent_states_by_date = session.query(
States.entity_id.label("max_entity_id"),
func.max(States.last_updated).label("max_last_updated"),
).filter((States.last_updated >= run.start)
& (States.last_updated < utc_point_in_time))
if entity_ids:
most_recent_states_by_date.filter(States.entity_id.in_(entity_ids))
most_recent_states_by_date = most_recent_states_by_date.group_by(
States.entity_id)
most_recent_states_by_date = most_recent_states_by_date.subquery()
most_recent_state_ids = session.query(
func.max(States.state_id).label("max_state_id")).join(
most_recent_states_by_date,
and_(
States.entity_id == most_recent_states_by_date.c.max_entity_id,
States.last_updated ==
most_recent_states_by_date.c.max_last_updated,
),
)
most_recent_state_ids = most_recent_state_ids.group_by(States.entity_id)
most_recent_state_ids = most_recent_state_ids.subquery()
query = query.join(
most_recent_state_ids,
States.state_id == most_recent_state_ids.c.max_state_id,
)
if entity_ids is not None:
query = query.filter(States.entity_id.in_(entity_ids))
else:
query = query.filter(~States.domain.in_(IGNORE_DOMAINS))
if filters:
query = filters.apply(query)
return [LazyState(row) for row in execute(query)]
def get_states(opp,
utc_point_in_time,
entity_ids=None,
run=None,
filters=None):
"""Return the states at a specific point in time."""
if run is None:
run = recorder.run_information(opp, utc_point_in_time)
# History did not run before utc_point_in_time
if run is None:
return []
with session_scope(opp=opp) as session:
query = session.query(States)
if entity_ids and len(entity_ids) == 1:
# Use an entirely different (and extremely fast) query if we only
# have a single entity id
query = (query.filter(
States.last_updated >= run.start,
States.last_updated < utc_point_in_time,
States.entity_id.in_(entity_ids),
).order_by(States.last_updated.desc()).limit(1))
else:
# We have more than one entity to look at (most commonly we want
# all entities,) so we need to do a search on all states since the
# last recorder run started.
most_recent_states_by_date = session.query(
States.entity_id.label("max_entity_id"),
func.max(States.last_updated).label("max_last_updated"),
).filter((States.last_updated >= run.start)
& (States.last_updated < utc_point_in_time))
if entity_ids:
most_recent_states_by_date.filter(
States.entity_id.in_(entity_ids))
most_recent_states_by_date = most_recent_states_by_date.group_by(
States.entity_id)
most_recent_states_by_date = most_recent_states_by_date.subquery()
most_recent_state_ids = session.query(
func.max(States.state_id).label("max_state_id")).join(
most_recent_states_by_date,
and_(
States.entity_id ==
most_recent_states_by_date.c.max_entity_id,
States.last_updated ==
most_recent_states_by_date.c.max_last_updated,
),
)
most_recent_state_ids = most_recent_state_ids.group_by(
States.entity_id)
most_recent_state_ids = most_recent_state_ids.subquery()
query = query.join(
most_recent_state_ids,
States.state_id == most_recent_state_ids.c.max_state_id,
).filter(~States.domain.in_(IGNORE_DOMAINS))
if filters:
query = filters.apply(query, entity_ids)
return [
state for state in execute(query)
if not state.attributes.get(ATTR_HIDDEN, False)
]
