From d0fe9fe6c613ec8894ca4b1d330e7847845a4710 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Sat, 23 Aug 2025 15:34:51 +0100
Subject: Add DB schema and setup a mock of it in testing

---
 src/schema.sql | 35 +++++++++++++++++++++++++++++++++++
 1 file changed, 35 insertions(+)
 create mode 100644 src/schema.sql

(limited to 'src/schema.sql')

diff --git a/src/schema.sql b/src/schema.sql
new file mode 100644
index 0000000..4ac3d84
--- /dev/null
+++ b/src/schema.sql
@@ -0,0 +1,35 @@
+CREATE TYPE http_verb AS ENUM('GET', 'HEAD', 'OPTIONS', 'TRACE', 'PUT', 'DELETE', 'POST', 'PATCH', 'CONNECT');
+CREATE TYPE protocol AS ENUM('HTTP/1.0', 'HTTP/1.1', 'HTTP/1.2', 'HTTP/1.3', 'HTTP/2.0', 'HTTPS/3.0');
+
+CREATE TABLE entities (
+	id bigint NOT NULL,
+	value text NOT NULL,
+
+	CONSTRAINT pk_entities PRIMARY KEY(id),
+	CONSTRAINT uni_entities_value UNIQUE(value)
+);
+
+CREATE TABLE access_log (
+	id serial NOT NULL,
+	hostname bigint NOT NULL,
+	virtual_host bigint NOT NULL,
+	remoteip inet NOT NULL,
+	request_time timestamp(6) NOT NULL,
+	method http_verb NOT NULL,
+	protocol protocol NOT NULL,
+	path bigint NOT NULL,
+	query_string bigint,
+	status smallint NOT NULL,
+	size int NOT NULL,
+	duration interval second(6) NOT NULL,
+	referrer bigint,
+	user_agent bigint,
+
+	CONSTRAINT pk_access_log PRIMARY KEY(id),
+	CONSTRAINT fk_access_log_hostname FOREIGN KEY(hostname) REFERENCES entities(id),
+	CONSTRAINT fk_access_log_virtualhost FOREIGN KEY(virtual_host) REFERENCES entities(id),
+	CONSTRAINT fk_access_log_path FOREIGN KEY(path) REFERENCES entities(id),
+	CONSTRAINT fk_access_log_query_string FOREIGN KEY(query_string) REFERENCES entities(id),
+	CONSTRAINT fk_access_log_referrer FOREIGN KEY(referrer) REFERENCES entities(id),
+	CONSTRAINT fk_access_log_user_agent FOREIGN KEY(user_agent) REFERENCES entities(id)
+);
-- 
cgit v1.2.3

