1delimiter // 2 3DROP TRIGGER IF EXISTS Events_Hour_delete_trigger// 4CREATE TRIGGER Events_Hour_delete_trigger BEFORE DELETE ON Events_Hour 5FOR EACH ROW BEGIN 6 UPDATE Monitors SET 7 HourEvents = COALESCE(HourEvents,1)-1, 8 HourEventDiskSpace=COALESCE(HourEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 9 WHERE Id=OLD.MonitorId; 10END; 11// 12 13DROP TRIGGER IF EXISTS Events_Hour_update_trigger; 14CREATE TRIGGER Events_Hour_update_trigger AFTER UPDATE ON Events_Hour 15FOR EACH ROW 16 BEGIN 17 declare diff BIGINT default 0; 18 19 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 20 IF ( diff ) THEN 21 UPDATE Monitors SET HourEventDiskSpace=COALESCE(HourEventDiskSpace,0)+diff WHERE Monitors.Id=MonitorId; 22 END IF; 23END; 24// 25 26DROP TRIGGER IF EXISTS Events_Day_delete_trigger// 27CREATE TRIGGER Events_Day_delete_trigger BEFORE DELETE ON Events_Day 28FOR EACH ROW BEGIN 29 UPDATE Monitors SET 30 DayEvents = COALESCE(DayEvents,1)-1, 31 DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 32 WHERE Id=OLD.MonitorId; 33END; 34// 35 36DROP TRIGGER IF EXISTS Events_Day_update_trigger; 37CREATE TRIGGER Events_Day_update_trigger AFTER UPDATE ON Events_Day 38FOR EACH ROW 39 BEGIN 40 declare diff BIGINT default 0; 41 42 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 43 IF ( diff ) THEN 44 UPDATE Monitors SET DayEventDiskSpace=COALESCE(DayEventDiskSpace,0)+diff WHERE Monitors.Id=MonitorId; 45 END IF; 46END; 47// 48 49DROP TRIGGER IF EXISTS Events_Week_delete_trigger// 50CREATE TRIGGER Events_Week_delete_trigger BEFORE DELETE ON Events_Week 51FOR EACH ROW BEGIN 52 UPDATE Monitors SET 53 WeekEvents = COALESCE(WeekEvents,1)-1, 54 WeekEventDiskSpace=COALESCE(WeekEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 55 WHERE Id=OLD.MonitorId; 56END; 57// 58 59DROP TRIGGER IF EXISTS Events_Week_update_trigger; 60CREATE TRIGGER Events_Week_update_trigger AFTER UPDATE ON Events_Week 61FOR EACH ROW 62 BEGIN 63 declare diff BIGINT default 0; 64 65 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 66 IF ( diff ) THEN 67 UPDATE Monitors SET WeekEventDiskSpace=COALESCE(WeekEventDiskSpace,0)+diff WHERE Monitors.Id=MonitorId; 68 END IF; 69END; 70// 71 72DROP TRIGGER IF EXISTS Events_Month_delete_trigger// 73CREATE TRIGGER Events_Month_delete_trigger BEFORE DELETE ON Events_Month 74FOR EACH ROW BEGIN 75 UPDATE Monitors SET 76 MonthEvents = COALESCE(MonthEvents,1)-1, 77 MonthEventDiskSpace=COALESCE(MonthEventDiskSpace,0)-COALESCE(OLD.DiskSpace,0) 78 WHERE Id=OLD.MonitorId; 79END; 80// 81 82DROP TRIGGER IF EXISTS Events_Month_update_trigger; 83CREATE TRIGGER Events_Month_update_trigger AFTER UPDATE ON Events_Month 84FOR EACH ROW 85 BEGIN 86 declare diff BIGINT default 0; 87 88 set diff = COALESCE(NEW.DiskSpace,0) - COALESCE(OLD.DiskSpace,0); 89 IF ( diff ) THEN 90 UPDATE Monitors SET MonthEventDiskSpace=COALESCE(MonthEventDiskSpace,0)+diff WHERE Monitors.Id=MonitorId; 91 END IF; 92END; 93// 94 95DROP TRIGGER IF EXISTS event_insert_trigger// 96 97CREATE TRIGGER event_insert_trigger AFTER INSERT ON Events 98FOR EACH ROW 99 BEGIN 100 INSERT INTO Events_Hour (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 101 INSERT INTO Events_Day (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 102 INSERT INTO Events_Week (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 103 INSERT INTO Events_Month (EventId,MonitorId,StartTime,DiskSpace) VALUES (NEW.Id,NEW.MonitorId,NEW.StartTime,0); 104 UPDATE Monitors SET 105 HourEvents = COALESCE(HourEvents,0)+1, 106 DayEvents = COALESCE(DayEvents,0)+1, 107 WeekEvents = COALESCE(WeekEvents,0)+1, 108 MonthEvents = COALESCE(MonthEvents,0)+1, 109 TotalEvents = COALESCE(TotalEvents,0)+1 110 WHERE Id=NEW.MonitorId; 111 END; 112 // 113 114delimiter ; 115SET @s = (SELECT IF( 116 (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = DATABASE() 117 AND table_name = 'Events' 118 AND column_name = 'Scheme' 119 ) > 0, 120 "SELECT 'Column Scheme already exists in Events'", 121 "ALTER TABLE Events ADD `Scheme` enum('Deep','Medium','Shallow') NOT NULL default 'Deep' AFTER `DiskSpace`" 122 )); 123 124 PREPARE stmt FROM @s; 125 EXECUTE stmt; 126 127 128UPDATE Monitors SET 129TotalEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id), 130TotalEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND DiskSpace IS NOT NULL), 131HourEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB( NOW(), INTERVAL 1 hour) ), 132HourEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 hour) AND DiskSpace IS NOT NULL), 133DayEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 day)), 134DayEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 day) AND DiskSpace IS NOT NULL), 135WeekEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 week)), 136WeekEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 week) AND DiskSpace IS NOT NULL), 137MonthEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB( NOW(), INTERVAL 1 month)), 138MonthEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND StartTime > DATE_SUB(NOW(), INTERVAL 1 month) AND DiskSpace IS NOT NULL), 139ArchivedEvents=(SELECT COUNT(Id) FROM Events WHERE MonitorId=Monitors.Id AND Archived=1), 140ArchivedEventDiskSpace=(SELECT SUM(DiskSpace) FROM Events WHERE MonitorId=Monitors.Id AND Archived=1 AND DiskSpace IS NOT NULL) 141