def test_update_future_lambda(self):
User, users = self.classes.User, self.tables.users
sess = Session(future=True)
john, jack, jill, jane = (
sess.execute(select(User).order_by(User.id)).scalars().all()
)
sess.execute(
lambda_stmt(
lambda: update(User)
.where(User.age > 29)
.values({"age": User.age - 10})
.execution_options(synchronize_session="evaluate")
),
)
eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27])
eq_(
sess.execute(select(User.age).order_by(User.id)).all(),
list(zip([25, 37, 29, 27])),
)
sess.execute(
lambda_stmt(
lambda: update(User)
.where(User.age > 29)
.values({User.age: User.age - 10})
.execution_options(synchronize_session="evaluate")
)
)
eq_([john.age, jack.age, jill.age, jane.age], [25, 27, 29, 27])
eq_(
sess.query(User.age).order_by(User.id).all(),
list(zip([25, 27, 29, 27])),
)
sess.query(User).filter(User.age > 27).update(
{users.c.age_int: User.age - 10}, synchronize_session="evaluate"
)
eq_([john.age, jack.age, jill.age, jane.age], [25, 27, 19, 27])
eq_(
sess.query(User.age).order_by(User.id).all(),
list(zip([25, 27, 19, 27])),
)
sess.query(User).filter(User.age == 25).update(
{User.age: User.age - 10}, synchronize_session="fetch"
)
eq_([john.age, jack.age, jill.age, jane.age], [15, 27, 19, 27])
eq_(
sess.query(User.age).order_by(User.id).all(),
list(zip([15, 27, 19, 27])),
)
def find_shared_data_id(attr_hash: int, shared_data: str) -> StatementLambdaElement:
"""Find a data_id by hash and shared_data."""
return lambda_stmt(
lambda: select(EventData.data_id)
.filter(EventData.hash == attr_hash)
.filter(EventData.shared_data == shared_data)
)
def find_states_to_purge(purge_before: datetime) -> StatementLambdaElement:
"""Find states to purge."""
return lambda_stmt(
lambda: select(States.state_id, States.attributes_id)
.filter(States.last_updated < purge_before)
.limit(MAX_ROWS_TO_PURGE)
)
def delete_event_data_rows(data_ids: Iterable[int]) -> StatementLambdaElement:
"""Delete event_data rows."""
return lambda_stmt(
lambda: delete(EventData)
.where(EventData.data_id.in_(data_ids))
.execution_options(synchronize_session=False)
)
def delete_states_rows(state_ids: Iterable[int]) -> StatementLambdaElement:
"""Delete states rows."""
return lambda_stmt(
lambda: delete(States)
.where(States.state_id.in_(state_ids))
.execution_options(synchronize_session=False)
)
def all_stmt(
start_day: dt,
end_day: dt,
event_types: tuple[str, ...],
states_entity_filter: ClauseList | None = None,
events_entity_filter: ClauseList | None = None,
context_id: str | None = None,
) -> StatementLambdaElement:
"""Generate a logbook query for all entities."""
stmt = lambda_stmt(
lambda: select_events_without_states(start_day, end_day, event_types))
if context_id is not None:
# Once all the old `state_changed` events
# are gone from the database remove the
# _legacy_select_events_context_id()
stmt += lambda s: s.where(Events.context_id == context_id).union_all(
_states_query_for_context_id(start_day, end_day, context_id),
legacy_select_events_context_id(start_day, end_day, context_id),
)
else:
if events_entity_filter is not None:
stmt += lambda s: s.where(events_entity_filter)
if states_entity_filter is not None:
stmt += lambda s: s.union_all(
_states_query_for_all(start_day, end_day).where(
states_entity_filter))
else:
stmt += lambda s: s.union_all(
_states_query_for_all(start_day, end_day))
stmt += lambda s: s.order_by(Events.time_fired)
return stmt
def find_legacy_event_state_and_attributes_and_data_ids_to_purge(
purge_before: datetime, ) -> StatementLambdaElement:
"""Find the latest row in the legacy format to purge."""
return lambda_stmt(lambda: select(
Events.event_id, Events.data_id, States.state_id, States.attributes_id
).join(States, Events.event_id == States.event_id).filter(
Events.time_fired < purge_before).limit(MAX_ROWS_TO_PURGE))
def delete_recorder_runs_rows(purge_before: datetime,
current_run_id: int) -> StatementLambdaElement:
"""Delete recorder_runs rows."""
return lambda_stmt(
lambda: delete(RecorderRuns).filter(RecorderRuns.start < purge_before).
filter(RecorderRuns.run_id != current_run_id).execution_options(
synchronize_session=False))
def query(names):
stmt = lambda_stmt(
lambda: select(User.name, Address.email_address).where(
User.name.in_(names)).join(User.addresses)) + (
lambda s: s.order_by(User.id, Address.id))
return s.execute(stmt)
def query(names):
u1 = aliased(User)
stmt = lambda_stmt(lambda: select(u1).where(
u1.name.in_(names)).options(selectinload(u1.addresses))
) + (lambda s: s.order_by(u1.id))
return s.execute(stmt)
def entities_devices_stmt(
start_day: dt,
end_day: dt,
event_types: tuple[str, ...],
entity_ids: list[str],
json_quoted_entity_ids: list[str],
json_quoted_device_ids: list[str],
) -> StatementLambdaElement:
"""Generate a logbook query for multiple entities."""
stmt = lambda_stmt(
lambda: _apply_entities_devices_context_union(
select_events_without_states(start_day, end_day, event_types).where(
_apply_event_entity_id_device_id_matchers(
json_quoted_entity_ids, json_quoted_device_ids
)
),
start_day,
end_day,
event_types,
entity_ids,
json_quoted_entity_ids,
json_quoted_device_ids,
).order_by(Events.time_fired)
)
return stmt
def fetch_files_by_name(filenames='all'):
"""
根据输入的物理量名，从files table获取 file object(s)
Parameters
----------
filenames : str or list[str]
Returns
-------
list[File]
"""
stmt = lambda_stmt(lambda: select(File))
if (filenames == 'all') or ('all' in list(filenames)):
pass
else:
if isinstance(filenames, str):
filenames = [filenames]
stmt += lambda s: s.where(File.name.in_(filenames))
try:
with Session() as session:
files = session.execute(stmt).scalars().all()
finally:
if not files:
raise Exception(f"{filenames} doesn't exists")
return files
def data_ids_exist_in_events_sqlite(
data_ids: Iterable[int],
) -> StatementLambdaElement:
"""Find data ids that exist in the events table."""
return lambda_stmt(
lambda: select(distinct(Events.data_id)).filter(Events.data_id.in_(data_ids))
)
def find_events_to_purge(purge_before: datetime) -> StatementLambdaElement:
"""Find events to purge."""
return lambda_stmt(
lambda: select(Events.event_id, Events.data_id)
.filter(Events.time_fired < purge_before)
.limit(MAX_ROWS_TO_PURGE)
)
def disconnect_states_rows(state_ids: Iterable[int]) -> StatementLambdaElement:
"""Disconnect states rows."""
return lambda_stmt(
lambda: update(States)
.where(States.old_state_id.in_(state_ids))
.values(old_state_id=None)
.execution_options(synchronize_session=False)
)
def query(names):
stmt = lambda_stmt(
lambda: select(User)
.where(User.name.in_(names))
.options(subqueryload(User.addresses))
) + (lambda s: s.order_by(User.id))
return s.execute(stmt)
def find_statistics_runs_to_purge(
purge_before: datetime,
) -> StatementLambdaElement:
"""Find statistics_runs to purge."""
return lambda_stmt(
lambda: select(StatisticsRuns.run_id)
.filter(StatisticsRuns.start < purge_before)
.limit(MAX_ROWS_TO_PURGE)
)
def delete_event_rows(
event_ids: Iterable[int],
) -> StatementLambdaElement:
"""Delete statistics_short_term rows."""
return lambda_stmt(
lambda: delete(Events)
.where(Events.event_id.in_(event_ids))
.execution_options(synchronize_session=False)
)
def delete_statistics_short_term_rows(
short_term_statistics: Iterable[int],
) -> StatementLambdaElement:
"""Delete statistics_short_term rows."""
return lambda_stmt(
lambda: delete(StatisticsShortTerm)
.where(StatisticsShortTerm.id.in_(short_term_statistics))
.execution_options(synchronize_session=False)
)
def delete_statistics_runs_rows(
statistics_runs: Iterable[int],
) -> StatementLambdaElement:
"""Delete statistics_runs rows."""
return lambda_stmt(
lambda: delete(StatisticsRuns)
.where(StatisticsRuns.run_id.in_(statistics_runs))
.execution_options(synchronize_session=False)
)
def find_short_term_statistics_to_purge(
purge_before: datetime,
) -> StatementLambdaElement:
"""Find short term statistics to purge."""
return lambda_stmt(
lambda: select(StatisticsShortTerm.id)
.filter(StatisticsShortTerm.start < purge_before)
.limit(MAX_ROWS_TO_PURGE)
)
def attributes_ids_exist_in_states_sqlite(
attributes_ids: Iterable[int],
) -> StatementLambdaElement:
"""Find attributes ids that exist in the states table."""
return lambda_stmt(
lambda: select(distinct(States.attributes_id)).filter(
States.attributes_id.in_(attributes_ids)
)
)
def find_shared_attributes_id(
data_hash: int, shared_attrs: str
) -> StatementLambdaElement:
"""Find an attributes_id by hash and shared_attrs."""
return lambda_stmt(
lambda: select(StateAttributes.attributes_id)
.filter(StateAttributes.hash == data_hash)
.filter(StateAttributes.shared_attrs == shared_attrs)
)
def go(ids, values):
stmt = lambda_stmt(lambda: update(User).where(User.id.in_(ids)))
s.execute(
stmt,
values,
# note this currently just unrolls the lambda on the statement.
# so lambda caching for updates is not actually that useful
# unless synchronize_session is turned off.
# evaluate is similar just doesn't work for IN yet.
execution_options={"synchronize_session": "fetch"},
)
def get_data(self):
c = ','.join(self.cols) if self.cols else '*'
query = text(f' {c} from {self.table}')
stmt = lambda_stmt(lambda: select(query))
if self.iterate_from and self.iterate_to:
col = column(self.iterable_column)
it_from = self.iterate_from
it_to = self.iterate_to
stmt += lambda s: s.where((col >= it_from) & (col <= it_to))
if self.iterable_column:
col = column(self.iterable_column)
stmt += lambda s: s.order_by(col)
result = self.connection.execute(stmt)
return result, [col for col in result.keys()]
def query(names):
class Foo(object):
def __init__(self):
self.u1 = aliased(User)
f1 = Foo()
stmt = lambda_stmt(
lambda: select(f1.u1).where(f1.u1.name.in_(names)).
options(selectinload(f1.u1.addresses)),
track_on=[f1.u1],
).add_criteria(lambda s: s.order_by(f1.u1.id),
track_on=[f1.u1])
return s.execute(stmt)
def test_update_fetch_returning_lambda(self):
User = self.classes.User
sess = Session(testing.db, future=True)
john, jack, jill, jane = (sess.execute(select(User).order_by(
User.id)).scalars().all())
with self.sql_execution_asserter() as asserter:
stmt = lambda_stmt(lambda: update(User).where(User.age > 29).
values({"age": User.age - 10}))
sess.execute(stmt,
execution_options={"synchronize_session": "fetch"})
# these are simple values, these are now evaluated even with
# the "fetch" strategy, new in 1.4, so there is no expiry
eq_([john.age, jack.age, jill.age, jane.age], [25, 37, 29, 27])
if testing.db.dialect.full_returning:
asserter.assert_(
CompiledSQL(
"UPDATE users SET age_int=(users.age_int - %(age_int_1)s) "
"WHERE users.age_int > %(age_int_2)s RETURNING users.id",
[{
"age_int_1": 10,
"age_int_2": 29
}],
dialect="postgresql",
), )
else:
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users "
"WHERE users.age_int > :age_int_1",
[{
"age_int_1": 29
}],
),
CompiledSQL(
"UPDATE users SET age_int=(users.age_int - :age_int_1) "
"WHERE users.age_int > :age_int_2",
[{
"age_int_1": 10,
"age_int_2": 29
}],
),
)
def test_delete_fetch_returning_lambda(self):
User = self.classes.User
sess = Session(testing.db, future=True)
john, jack, jill, jane = (sess.execute(select(User).order_by(
User.id)).scalars().all())
in_(john, sess)
in_(jack, sess)
with self.sql_execution_asserter() as asserter:
stmt = lambda_stmt(lambda: delete(User).where(User.age > 29))
sess.execute(stmt,
execution_options={"synchronize_session": "fetch"})
if testing.db.dialect.full_returning:
asserter.assert_(
CompiledSQL(
"DELETE FROM users WHERE users.age_int > %(age_int_1)s "
"RETURNING users.id",
[{
"age_int_1": 29
}],
dialect="postgresql",
), )
else:
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users "
"WHERE users.age_int > :age_int_1",
[{
"age_int_1": 29
}],
),
CompiledSQL(
"DELETE FROM users WHERE users.age_int > :age_int_1",
[{
"age_int_1": 29
}],
),
)
in_(john, sess)
not_in_(jack, sess)
in_(jill, sess)
not_in_(jane, sess)
def lambda_stmt_and_join_attributes(
schema_version: int,
no_attributes: bool,
include_last_changed: bool = True
) -> tuple[StatementLambdaElement, bool]:
"""Return the lambda_stmt and if StateAttributes should be joined.
Because these are lambda_stmt the values inside the lambdas need
to be explicitly written out to avoid caching the wrong values.
"""
# If no_attributes was requested we do the query
# without the attributes fields and do not join the
# state_attributes table
if no_attributes:
if include_last_changed:
return lambda_stmt(lambda: select(*QUERY_STATE_NO_ATTR)), False
return (
lambda_stmt(lambda: select(*QUERY_STATE_NO_ATTR_NO_LAST_CHANGED)),
False,
)
# If we in the process of migrating schema we do
# not want to join the state_attributes table as we
# do not know if it will be there yet
if schema_version < 25:
if include_last_changed:
return (
lambda_stmt(lambda: select(*QUERY_STATES_PRE_SCHEMA_25)),
False,
)
return (
lambda_stmt(
lambda: select(*QUERY_STATES_PRE_SCHEMA_25_NO_LAST_CHANGED)),
False,
)
# Finally if no migration is in progress and no_attributes
# was not requested, we query both attributes columns and
# join state_attributes
if include_last_changed:
return lambda_stmt(lambda: select(*QUERY_STATES)), True
return lambda_stmt(lambda: select(*QUERY_STATES_NO_LAST_CHANGED)), True
def find_legacy_row() -> StatementLambdaElement:
"""Check if there are still states in the table with an event_id."""
return lambda_stmt(lambda: select(func.max(States.event_id)))