From 2e619db979d76c3e478d0b0592a569a005dc18c0 Mon Sep 17 00:00:00 2001
From: Shulhan <ms@kilabit.info>
Date: Mon, 18 Mar 2024 02:21:30 +0700
Subject: all: auto migrate the database when using postgresql forwarder

---
 _database/0001_http_log.sql | 68 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 68 insertions(+)
 create mode 100644 _database/0001_http_log.sql

(limited to '_database/0001_http_log.sql')

diff --git a/_database/0001_http_log.sql b/_database/0001_http_log.sql
new file mode 100644
index 0000000..47da300
--- /dev/null
+++ b/_database/0001_http_log.sql
@@ -0,0 +1,68 @@
+DROP TABLE IF EXISTS http_log CASCADE;
+
+CREATE TABLE http_log (
+  request_date  TIMESTAMP WITH TIME ZONE
+
+, client_ip     VARCHAR
+
+, frontend_name VARCHAR
+, backend_name  VARCHAR
+, server_name   VARCHAR
+
+, http_proto    VARCHAR
+, http_method   VARCHAR
+, http_url      VARCHAR
+, http_query    VARCHAR
+
+, header_request   VARCHAR
+, header_response  VARCHAR
+
+, cookie_request    VARCHAR
+, cookie_response   VARCHAR
+, termination_state VARCHAR
+
+, bytes_read    BIGINT
+
+, status_code   INTEGER
+, client_port   INTEGER
+
+, time_request  INTEGER
+, time_wait     INTEGER
+, time_connect  INTEGER
+, time_response INTEGER
+, time_all      INTEGER
+
+, conn_active   INTEGER
+, conn_frontend INTEGER
+, conn_backend  INTEGER
+, conn_server   INTEGER
+, retries       INTEGER
+
+, server_queue  INTEGER
+, backend_queue INTEGER
+);
+
+DROP INDEX IF EXISTS http_log_idx;
+
+CREATE INDEX IF NOT EXISTS http_log_idx ON http_log(
+  request_date
+, client_ip
+, frontend_name
+, backend_name
+, server_name
+, http_proto
+, http_method
+, http_url
+, termination_state
+, status_code
+);
+
+DROP INDEX IF EXISTS http_log_time_idx;
+
+CREATE INDEX IF NOT EXISTS http_log_time_idx ON http_log(
+  time_request
+, time_wait
+, time_connect
+, time_response
+, time_all
+);
-- 
cgit v1.3