]>
Commit | Line | Data |
---|---|---|
77b1029d | 1 | -- Copyright (C) 1996-2020 The Squid Software Foundation and contributors |
9a1b46cc AJ |
2 | -- |
3 | -- Squid software is distributed under GPLv2+ license and includes | |
4 | -- contributions from numerous individuals and organizations. | |
5 | -- Please see the COPYING and CONTRIBUTORS files for details. | |
6 | -- | |
7 | ||
1c363fe7 AJ |
8 | -- we need a separate column to store the date and time of the request |
9 | ALTER TABLE access_log ADD COLUMN date_day DATE; | |
10 | ALTER TABLE access_log ADD COLUMN date_time TIME; | |
11 | ||
12 | -- let's populate the new columns, in case some rows already exist; | |
13 | -- the date and time values should be set by a trigger | |
14 | UPDATE access_log SET date_day = DATE(FROM_UNIXTIME(time_since_epoch)); | |
15 | UPDATE access_log SET date_time = TIME(FROM_UNIXTIME(time_since_epoch)); | |
16 | ||
17 | -- let's create a view that uses the date column | |
18 | CREATE VIEW requests_per_day_2 AS SELECT date_day, COUNT(*) AS num_of_requests FROM access_log GROUP BY 1 ORDER BY 1; | |
19 | ||
20 | -- that view needs an index on the group by column | |
21 | CREATE INDEX date_day_idx ON access_log(date_day); | |
22 | ||
23 | ||
24 | -- a trigger that automatically extracts the date value from the time_since_epoch column | |
25 | -- and stores it in the date_day column | |
26 | DELIMITER // | |
27 | CREATE TRIGGER extract_date_day_bi BEFORE INSERT ON access_log FOR EACH ROW | |
28 | BEGIN | |
29 | SET NEW.date_day = DATE(FROM_UNIXTIME(NEW.time_since_epoch)); | |
30 | SET NEW.date_time = TIME(FROM_UNIXTIME(NEW.time_since_epoch)); | |
31 | END // | |
32 | ||
33 | ||
34 | -- Note: after running this script against an already populated access_log, | |
35 | -- views have to be recreated, or the new date_day column will not show up. |