From 5b2166496e5f3ff2c4276e0b5b28f109c70673d5 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan.goodliffe@octal.co.uk>
Date: Wed, 15 Apr 2026 12:03:21 +0100
Subject: Replace use of crc32 for entity id

Entity value is MD5 hashed same as DB unique key, but the id itself is
now taken from the DB primary key which is sequence generated.
---
 src/schema.sql | 69 +++++++++++++++++++++++++++++++++++++++++++++-------------
 1 file changed, 54 insertions(+), 15 deletions(-)

(limited to 'src/schema.sql')

diff --git a/src/schema.sql b/src/schema.sql
index 789789a..fd9eb4f 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -33,7 +33,7 @@ CREATE TYPE entity AS ENUM(
 );
 
 CREATE TABLE entities(
-	id oid NOT NULL,
+	id int GENERATED ALWAYS AS IDENTITY,
 	value text NOT NULL,
 	type entity NOT NULL,
 	detail jsonb,
@@ -42,30 +42,69 @@ CREATE TABLE entities(
 
 CREATE UNIQUE INDEX uni_entities_value ON entities(MD5(value));
 
+CREATE OR REPLACE FUNCTION entity(newValue text, newType entity)
+	RETURNS int
+	AS $$
+DECLARE
+	now timestamp without time zone;
+	recid int;
+BEGIN
+	IF newValue IS NULL THEN
+		RETURN NULL;
+	END IF;
+	INSERT INTO entities(value, type)
+	SELECT
+		newValue,
+		newType
+	WHERE
+		NOT EXISTS (
+			SELECT
+			FROM
+				entities
+			WHERE
+				md5(value) = md5(newValue))
+	ON CONFLICT
+		DO NOTHING
+	RETURNING
+		id INTO recid;
+	IF recid IS NULL THEN
+		SELECT
+			id INTO recid
+		FROM
+			entities
+		WHERE
+			md5(value) = md5(newValue);
+	END IF;
+	RETURN recid;
+END;
+$$
+LANGUAGE plpgSQL
+RETURNS NULL ON NULL INPUT;
+
 CREATE TABLE access_log(
 	id bigint GENERATED ALWAYS AS IDENTITY,
-	hostname oid NOT NULL,
-	virtual_host oid NOT NULL,
+	hostname int NOT NULL,
+	virtual_host int NOT NULL,
 	remoteip inet NOT NULL,
 	request_time timestamp(6) NOT NULL,
 	method http_verb NOT NULL,
 	protocol protocol NOT NULL,
-	path oid NOT NULL,
-	query_string oid,
+	path int NOT NULL,
+	query_string int,
 	status smallint NOT NULL,
 	size int NOT NULL,
 	duration interval second(6) NOT NULL,
-	referrer oid,
-	user_agent oid,
-	content_type oid,
+	referrer int,
+	user_agent int,
+	content_type int,
 	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),
-	CONSTRAINT fk_access_log_content_type FOREIGN KEY (content_type) REFERENCES entities(id)
+	CONSTRAINT fk_access_log_hostname FOREIGN KEY (hostname) REFERENCES entities(id) ON UPDATE CASCADE,
+	CONSTRAINT fk_access_log_virtualhost FOREIGN KEY (virtual_host) REFERENCES entities(id) ON UPDATE CASCADE,
+	CONSTRAINT fk_access_log_path FOREIGN KEY (path) REFERENCES entities(id) ON UPDATE CASCADE,
+	CONSTRAINT fk_access_log_query_string FOREIGN KEY (query_string) REFERENCES entities(id) ON UPDATE CASCADE,
+	CONSTRAINT fk_access_log_referrer FOREIGN KEY (referrer) REFERENCES entities(id) ON UPDATE CASCADE,
+	CONSTRAINT fk_access_log_user_agent FOREIGN KEY (user_agent) REFERENCES entities(id) ON UPDATE CASCADE,
+	CONSTRAINT fk_access_log_content_type FOREIGN KEY (content_type) REFERENCES entities(id) ON UPDATE CASCADE
 );
 
 CREATE OR REPLACE VIEW access_log_view AS
-- 
cgit v1.3