Ben Dailey a87e2d03a5 feat: replace Event_Summaries view with SWR snapshot table ...

Replace the Event_Summaries view with a physical snapshot table
refreshed via Stale-While-Revalidate (SWR) pattern. A stored
procedure (Refresh_Summaries_SWR) uses GET_LOCK for non-blocking
concurrency and atomic table rename for zero-downtime refresh.

Database changes (db/views.sql):
- Rename Event_Summaries view to VIEW_Event_Summaries (source view)
- Add Event_Summaries snapshot table and Event_Summaries_Metadata table
- Add Refresh_Summaries_SWR stored procedure with GET_LOCK and
  atomic rename pattern to prevent thundering herd
- Add MySQL EVENT for background refresh every 600 seconds

PHP call sites (web/):
- Add ensureSummariesFresh() helper in database.php with static
  per-request dedup and 60s staleness check
- Call ensureSummariesFresh() before Event_Summaries queries in
  console.php, _monitor_filters.php, and Monitor.php
- Add beforeFind() hook in CakePHP Event_Summary model

Perl call sites (scripts/):
- Add ensureSummariesFresh() sub in Event_Summary.pm with
  per-process 60s rate-limiting
- Call ensureSummariesFresh() in Monitor.pm Event_Summary accessor

Upgrade path (db/zm_update-1.37.78.sql.in):
- Drop any prior Event_Summaries view or table before recreating

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>

2026-04-07 12:53:42 -04:00

13 KiB

Raw Permalink Blame History

View Raw