* Add additional indicies on event and review tables. Every events or timeline endpoint filters on event start time and camera, this should speed things up by avoiding a range scan on the table.
* Rewrite to use a CTE to leverage speedups by using sqllite internal optimization to do a single query instead of a starter query to get distinct labels and a subsequent loop of querys per distinct event labels.
Frigate is currently shipping sqlite 3.46.1, which is above the minimum version 3.25 needed for CTEs.
* Collapse a few sequential queries into a single one.
* Use peewee instead of rw sql for the CTE query.
* Slightly simplify review logic and avoid duplicating the json response for empty review IDs.
* Rerun ruff formatting.
* Remove 2x unnecessary index on reviewsegment, remove reference to prior code implementation in comment in event.py
* Editor fail, re-ruff format.
* Remove the CTE and restore the generator with sub-queries, which is more performance (thanks Nick and Blake for testing against your larger DB!)
* Update peewee index migration description
* Add on_conflict_ignore, replacing the try/catch/pass on IntegrityError
* Add a testcase for validating that on_conflict_ignore bypasses what was formerly an IntegrityError
* Change testcase to clarify that it covers the peewee behavior of on_conflict_ignore
---------
Co-authored-by: Greg <{ID}+{username}@users.noreply.github.com>
* Initial implementation of active object counters. Need to clean up a bit more and examine reuse of stationary/active logic in neighboring modules.
* A bit more cleanup for references to active, referencing the tracked object method rather than duplicating logic.
* Minor formatting and readability cleanup
* Update docs with the new active mqtt metric definition.
* Move the check for a change in active status into the code block protected by a false positive check.
* - Add 'active' to the tracked object dictionary, use the previous object for active comparison.
- I also missed emitting updates when a tracked object is no longer tracked, and added handling for emitting zeros on object types.
