diff options
| author | Dan Goodliffe <dan@randomdan.homeip.net> | 2026-01-17 18:33:56 +0000 |
|---|---|---|
| committer | Dan Goodliffe <dan@randomdan.homeip.net> | 2026-01-17 20:33:37 +0000 |
| commit | 9f1dfacb0f236da5f0413ac20e3376388df7d798 (patch) | |
| tree | 9b4c52f35b8ee0215d4911a786efed382d803942 /src/sql | |
| parent | 30a9d45f5322a63c2adf1de7a248ba3a9a0c3903 (diff) | |
| download | webstat-9f1dfacb0f236da5f0413ac20e3376388df7d798.tar.bz2 webstat-9f1dfacb0f236da5f0413ac20e3376388df7d798.tar.xz webstat-9f1dfacb0f236da5f0413ac20e3376388df7d798.zip | |
pg_format schema.sql and sql/*.sql
No changes.
Diffstat (limited to 'src/sql')
| -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 |
5 files changed, 31 insertions, 17 deletions
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) |