* Speed up logbook and history queries where ORM rows are not needed
This avoids having sqlalchemy wrap Result in ChunkedIteratorResult
which has additional overhead we do not need for these cases
* more places
* anything that uses _sorted_statistics_to_dict does not need orm rows either
* Fallback to generating a new ULID on migraiton if context is missing or invalid
It was discovered that postgresql will do a full scan if
there is a low cardinality on the index because of missing
context ids. We will now generate a ULID for the timestamp
of the row if the context data is missing or invalid
fixes#91514
* tests
* tweak
* tweak
* preen
* Ensure recorder run shutdown if the run loop raises
If anything goes wrong with the recorder we should
still try to shutdown cleanly
* tweak
* tests
* tests
* handle migraiton failure
* tweak comment
* naming
* order
* order
* order
* reword
* adjust test
* fixes
* threading
* failure case
* fix test
* have to wait for stop because the task blocks on thread join
* Use local timezone for recorder connection
The fix in #90335 had an unexpected side effect of
using UTC for the timezone since all recorder operations
use UTC. Since only sqlite much use the database executor
we can use a seperate connection pool which uses local time
This also ensures that the engines are disposed of
when Home Assistant is shutdown as previously we
did not cleanly disconnect
* coverage
* fix unclean shutdown in config flow
* tweaks
https://www.sqlite.org/pragma.html#pragma_optimize
> To achieve the best long-term query performance without the need to do a detailed engineering analysis of the application schema and SQL, it is recommended that applications run "PRAGMA optimize" (with no arguments) just before closing each database connection. Long-running applications might also benefit from setting a timer to run "PRAGMA optimize" every few hours.
> This pragma is usually a no-op or nearly so and is very fast.
Since we keep the recorder connection open for the entire time HA
is running we fall into the long-running application bucket
* delete more code
* tweak
* tweak
* wrappers
* restore lost performance
* restore lost performance
* restore lost performance
* compact
* reduce
* fix refactor
* DRY
* tweak
* delete the start time state injector
* move away the legacy code
* tweak
* adjust
* adjust
* tweak
* ignore impossible
* fix a bug where the first start was changed to the start time when there was no previous history recorded before
* avoid the empty scan most cases
* postgresql
* fixes
* workaround for mariadb < 10.4
* remove unused
* remove unused
* adjust
* bail early
* tweak
* tweak
* fix more tests
* fix recorderrun being init in the future in the test
* run history tests on schema 30 as well
* Revert "run history tests on schema 30 as well"
This reverts commit d798b100ac.
* reduce
* cleanup
* tweak
* reduce
* prune
* adjust
* adjust
* adjust
* reverse later is faster because the index is in forward order and the data size we are reversing is much smaller even if we are in python code
* Revert "reverse later is faster because the index is in forward order and the data size we are reversing is much smaller even if we are in python code"
This reverts commit bf974e103e.
* fix test
* Revert "Revert "reverse later is faster because the index is in forward order and the data size we are reversing is much smaller even if we are in python code""
This reverts commit 119354499e.
* more coverage
* adjust
* fix for table order
* impossible for it to be missing
* remove some more legacy from the all states
* Reduce overhead of legacy database columns on new installs
* Reduce overhead of legacy database columns on new installs
* Reduce overhead of legacy database columns on new installs
* Reduce overhead of legacy database columns on new installs
* not working as expected
* override the type compiler
* override the type compiler
* override the type compiler
* override the type compiler
* Apply suggestions from code review
* pgsql char1
* make entity filter test setup with old schema
* fix some more tests that were mutating state
* fix some more tests that were mutating state
* fix some more tests that were mutating state
* fix more dbstate mutations
* add shim for older tests
* split migration tests
* add coverage for purging legacy data
* tweak
* more fixes
* drop some legacy
* fix another test
* fix a few more
* add casts for postgresql in case someone deletes the schema changes table
* dry
* dry
* dry
* Restart entity id post migration after a restart
If the entity migration finished and Home Assistant was
restarted during the post migration it would never be resumed
which means the old index and space would never be recovered
* add migration resume test
* Reduce cache key size for queries that only need single columns
These queries only cared about a single row but would select
the whole set of columns from the orm object
* wrap it
* Allow passing an optional name to async_track_time_interval
This is the same idea as passing a name to asyncio.create_task which
makes it easier to track down bugs
* more
* short
* still cannot find it
* add a few more
* test
* Add a faster query for get_last_state_changes when the number of states is 1
related issue #90113
* Add a faster query for get_last_state_changes when the number of states is 1
related issue #90113
* coverage
* Apply suggestions from code review
* Add auto repairs for events schema
* Add auto repairs for events schema
* Add auto repairs for events schema
* Add auto repairs for events schema
* Add auto repairs for events schema
* fix bug - wrong table
* Fix index not being dropped on postgresql databases with a schema prefix
Added logging in case index drops fail so we can tell
why in the future
* coverage
* Break out statistics schema repairs into a repairs module
A future PR will add repairs for events, states, etc
* reorg
* reorg
* reorg
* reorg
* fixes
* fix patch targets
* name space rename
Fix missing length on context id column with MariaDB
spotted by @dcoder42
The migration still worked as intented but the blob should have been
a bit smaller.
This only affects dev so there is no need for a backport
