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