From b4b57337e69f1ee0b816369ed6b7d657ee11da8c Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
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(+)

(limited to 'src/schema.sql')

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

