From b4b57337e69f1ee0b816369ed6b7d657ee11da8c Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Wed, 15 Oct 2025 23:21:50 +0100 Subject: Add access_log_view A pre-joined with entities view showing all the original data along with ids; ideal for human readable stuff. --- src/schema.sql | 11 +++++++++++ 1 file changed, 11 insertions(+) diff --git a/src/schema.sql b/src/schema.sql index 654699a..2bc3a0a 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -36,3 +36,14 @@ CREATE TABLE access_log ( CONSTRAINT fk_access_log_referrer FOREIGN KEY(referrer) REFERENCES entities(id), CONSTRAINT fk_access_log_user_agent FOREIGN KEY(user_agent) REFERENCES entities(id) ); + +CREATE OR REPLACE VIEW access_log_view AS +SELECT l.id, h.id hostname_id, h.value hostname, v.id virtual_host_id, v.value virtual_host, remoteip::text, request_time, method, protocol, + p.id path_id, p.value path, q.id query_string_id, q.value query_string, status, size, duration, r.id referrer_id, r.value referrer, u.id user_agent_id, u.value user_agent +FROM access_log l + LEFT OUTER JOIN entities h ON l.hostname = h.id + LEFT OUTER JOIN entities v ON l.virtual_host = v.id + LEFT OUTER JOIN entities p ON l.path = p.id + LEFT OUTER JOIN entities q ON l.query_string = q.id + LEFT OUTER JOIN entities r ON l.referrer = r.id + LEFT OUTER JOIN entities u ON l.user_agent = u.id; -- cgit v1.2.3