diff options
| -rw-r--r-- | src/schema.sql | 84 | ||||
| -rw-r--r-- | src/sql/accessLogInsert.sql | 7 | ||||
| -rw-r--r-- | src/sql/accessLogPurgeOld.sql | 19 | ||||
| -rw-r--r-- | src/sql/entityInsert.sql | 5 | ||||
| -rw-r--r-- | src/sql/entityUpdateDetail.sql | 7 | ||||
| -rw-r--r-- | src/sql/hostUpsert.sql | 10 |
6 files changed, 96 insertions, 36 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 diff --git a/src/sql/accessLogInsert.sql b/src/sql/accessLogInsert.sql index e0c39b8..42f809b 100644 --- a/src/sql/accessLogInsert.sql +++ b/src/sql/accessLogInsert.sql @@ -1,4 +1,3 @@ -INSERT INTO -access_log(hostname, virtual_host, remoteip, request_time, method, path, query_string, protocol, status, size, duration, - referrer, user_agent) -VALUES(?, ?, ?, TO_TIMESTAMP(? / 1000000.0) at time zone 'utc', ?, ?, ?, ?, ?, ?, ? * '1us'::interval, ?, ?) +INSERT INTO access_log(hostname, virtual_host, remoteip, request_time, method, path, query_string, protocol, status, + size, duration, referrer, user_agent) + VALUES (?, ?, ?, TO_TIMESTAMP(? / 1000000.0) at time zone 'utc', ?, ?, ?, ?, ?, ?, ? * '1us'::interval, ?, ?) diff --git a/src/sql/accessLogPurgeOld.sql b/src/sql/accessLogPurgeOld.sql index 8379018..18ec775 100644 --- a/src/sql/accessLogPurgeOld.sql +++ b/src/sql/accessLogPurgeOld.sql @@ -1,11 +1,18 @@ WITH scope AS ( - SELECT id - FROM access_log - ORDER BY id + SELECT + id + FROM + access_log + ORDER BY + id LIMIT ? -), scoperange AS ( - SELECT min(id) minid, max(id) maxid - FROM scope) +), +scoperange AS ( + SELECT + min(id) minid, + max(id) maxid + FROM + scope) DELETE FROM access_log USING scoperange WHERE request_time < CURRENT_DATE - ?::interval AND access_log.id BETWEEN scoperange.minid AND scoperange.maxid diff --git a/src/sql/entityInsert.sql b/src/sql/entityInsert.sql index 451e660..8e25810 100644 --- a/src/sql/entityInsert.sql +++ b/src/sql/entityInsert.sql @@ -1 +1,4 @@ -INSERT INTO entities(id, type, value) VALUES(?, ?, ?) ON CONFLICT DO NOTHING +INSERT INTO entities(id, type, value) + VALUES (?, ?, ?) +ON CONFLICT + DO NOTHING diff --git a/src/sql/entityUpdateDetail.sql b/src/sql/entityUpdateDetail.sql index 9e5ea82..2fd8ce6 100644 --- a/src/sql/entityUpdateDetail.sql +++ b/src/sql/entityUpdateDetail.sql @@ -1 +1,6 @@ -UPDATE entities SET detail = $2 WHERE id = $1 +UPDATE + entities +SET + detail = $2 +WHERE + id = $1 diff --git a/src/sql/hostUpsert.sql b/src/sql/hostUpsert.sql index 2b1109c..18e8df8 100644 --- a/src/sql/hostUpsert.sql +++ b/src/sql/hostUpsert.sql @@ -1,7 +1,7 @@ -INSERT INTO entities (id, type, value, detail) - VALUES ($1, 'host', $2, jsonb_build_object('sysname', $3::text, 'release', - $4::text, 'version', $5::text, 'machine', $6::text, 'domainname', $7::text)) +INSERT INTO entities(id, type, value, detail) + VALUES ($1, 'host', $2, jsonb_build_object('sysname', $3::text, 'release', $4::text, + 'version', $5::text, 'machine', $6::text, 'domainname', $7::text)) ON CONFLICT ON CONSTRAINT pk_entities DO UPDATE SET - detail = jsonb_build_object('sysname', $3::text, 'release', - $4::text, 'version', $5::text, 'machine', $6::text, 'domainname', $7::text) + detail = jsonb_build_object('sysname', $3::text, 'release', $4::text, 'version', + $5::text, 'machine', $6::text, 'domainname', $7::text) |