mirror of
https://github.com/ZoneMinder/zoneminder.git
synced 2026-05-29 08:55:36 -04:00
4fe2103fcd
Remove denormalized event summary tables and their associated triggers, replacing them with views that query the Events table directly. This eliminates trigger maintenance overhead and periodic reconciliation in zmaudit/zmstats, since the views compute stats on the fly. - Remove trigger definitions for event summary table maintenance - Remove event summary table inserts from zm_event.cpp - Remove event count reconciliation queries from zmaudit.pl - Remove DELETE-on-views calls from zmstats.pl (views filter by date inherently) - Remove Event_Summaries DELETE from Monitor.php (can't delete from a view) - Add db/views.sql with view definitions and covering index - Add upgrade script zm_update-1.37.78.sql.in (drop triggers, drop tables, create views) - Update zm_create.sql.in to use views instead of tables for fresh installs Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
44 lines
1.1 KiB
SQL
44 lines
1.1 KiB
SQL
DELIMITER //
|
|
|
|
DROP TRIGGER IF EXISTS Events_Hour_delete_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Events_Hour_update_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Events_Day_delete_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Events_Day_update_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Events_Week_delete_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Events_Week_update_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Events_Month_delete_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Events_Month_update_trigger//
|
|
|
|
DROP PROCEDURE IF EXISTS update_storage_stats//
|
|
|
|
DROP TRIGGER IF EXISTS event_update_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS event_insert_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS event_delete_trigger//
|
|
|
|
DROP TRIGGER IF EXISTS Zone_Insert_Trigger//
|
|
CREATE TRIGGER Zone_Insert_Trigger AFTER INSERT ON Zones
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE Monitors SET ZoneCount=(SELECT COUNT(*) FROM Zones WHERE MonitorId=NEW.MonitorId) WHERE Monitors.Id=NEW.MonitorID;
|
|
END
|
|
//
|
|
|
|
DROP TRIGGER IF EXISTS Zone_Delete_Trigger//
|
|
CREATE TRIGGER Zone_Delete_Trigger AFTER DELETE ON Zones
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE Monitors SET ZoneCount=(SELECT COUNT(*) FROM Zones WHERE MonitorId=OLD.MonitorId) WHERE Monitors.Id=OLD.MonitorID;
|
|
END
|
|
//
|
|
|
|
DELIMITER ;
|