diff options
Diffstat (limited to 'src/schema.sql')
| -rw-r--r-- | src/schema.sql | 84 |
1 files changed, 65 insertions, 19 deletions
diff --git a/src/schema.sql b/src/schema.sql index 2bc3a0a..d04535e 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -1,18 +1,45 @@ -CREATE TYPE http_verb AS ENUM('GET', 'HEAD', 'OPTIONS', 'TRACE', 'PUT', 'DELETE', 'POST', 'PATCH', 'CONNECT', 'PROPFIND'); -CREATE TYPE protocol AS ENUM('HTTP/1.0', 'HTTP/1.1', 'HTTP/1.2', 'HTTP/1.3', 'HTTP/2.0', 'HTTPS/3.0'); -CREATE TYPE entity AS ENUM('host', 'virtual_host', 'path', 'query_string', 'referrer', 'user_agent', 'unparsable_line'); +CREATE TYPE http_verb AS ENUM( + 'GET', + 'HEAD', + 'OPTIONS', + 'TRACE', + 'PUT', + 'DELETE', + 'POST', + 'PATCH', + 'CONNECT', + 'PROPFIND' +); + +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 ( +CREATE TYPE entity AS ENUM( + 'host', + 'virtual_host', + 'path', + 'query_string', + 'referrer', + 'user_agent', + 'unparsable_line' +); + +CREATE TABLE entities( id oid NOT NULL, value text NOT NULL, type entity NOT NULL, detail jsonb, - - CONSTRAINT pk_entities PRIMARY KEY(id), - CONSTRAINT uni_entities_value UNIQUE(value) + CONSTRAINT pk_entities PRIMARY KEY (id), + CONSTRAINT uni_entities_value UNIQUE (value) ); -CREATE TABLE access_log ( +CREATE TABLE access_log( id bigint GENERATED ALWAYS AS IDENTITY, hostname oid NOT NULL, virtual_host oid NOT NULL, @@ -27,20 +54,39 @@ CREATE TABLE access_log ( duration interval second(6) NOT NULL, referrer oid, user_agent oid, - - 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 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) ); 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 +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 |