From 5b2166496e5f3ff2c4276e0b5b28f109c70673d5 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe 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 From fa6074eaf52be4254c17b74f20193aa96c940df8 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sat, 18 Apr 2026 00:53:11 +0100 Subject: Swap int for integer in schema Plays better with apgdiff --- src/schema.sql | 22 +++++++++++----------- 1 file changed, 11 insertions(+), 11 deletions(-) (limited to 'src/schema.sql') diff --git a/src/schema.sql b/src/schema.sql index fd9eb4f..b211505 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -33,7 +33,7 @@ CREATE TYPE entity AS ENUM( ); CREATE TABLE entities( - id int GENERATED ALWAYS AS IDENTITY, + id integer GENERATED ALWAYS AS IDENTITY, value text NOT NULL, type entity NOT NULL, detail jsonb, @@ -43,11 +43,11 @@ CREATE TABLE entities( CREATE UNIQUE INDEX uni_entities_value ON entities(MD5(value)); CREATE OR REPLACE FUNCTION entity(newValue text, newType entity) - RETURNS int + RETURNS integer AS $$ DECLARE now timestamp without time zone; - recid int; + recid integer; BEGIN IF newValue IS NULL THEN RETURN NULL; @@ -83,20 +83,20 @@ RETURNS NULL ON NULL INPUT; CREATE TABLE access_log( id bigint GENERATED ALWAYS AS IDENTITY, - hostname int NOT NULL, - virtual_host int NOT NULL, + hostname integer NOT NULL, + virtual_host integer NOT NULL, remoteip inet NOT NULL, request_time timestamp(6) NOT NULL, method http_verb NOT NULL, protocol protocol NOT NULL, - path int NOT NULL, - query_string int, + path integer NOT NULL, + query_string integer, status smallint NOT NULL, - size int NOT NULL, + size integer NOT NULL, duration interval second(6) NOT NULL, - referrer int, - user_agent int, - content_type int, + referrer integer, + user_agent integer, + content_type integer, CONSTRAINT pk_access_log PRIMARY KEY (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, -- cgit v1.3