From d0fe9fe6c613ec8894ca4b1d330e7847845a4710 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Sat, 23 Aug 2025 15:34:51 +0100
Subject: Add DB schema and setup a mock of it in testing

---
 Jamroot.jam          |  6 ++++++
 src/schema.sql       | 35 +++++++++++++++++++++++++++++++++++
 test/Jamfile.jam     |  6 ++++++
 test/test-ingest.cpp | 17 +++++++++++++++++
 4 files changed, 64 insertions(+)
 create mode 100644 src/schema.sql

diff --git a/Jamroot.jam b/Jamroot.jam
index 5894a7d..c991a05 100644
--- a/Jamroot.jam
+++ b/Jamroot.jam
@@ -1,8 +1,14 @@
+using pkg-config ;
+import pkg-config ;
 import testing ;
 
 build-project src ;
 build-project test ;
 
+pkg-config.import glibmm : : <name>glibmm-2.68 ;
+lib adhocutil : : <link>shared : : <include>/usr/include/adhocutil <library>glibmm ;
+lib dbppcore : : <link>shared : : <include>/usr/include/dbpp <library>adhocutil ;
+lib dbpp-postgresql : : <link>shared : : <include>/usr/include/dbpp-postgresql <library>dbppcore ;
 lib z : : <link>shared ;
 
 project webstat : requirements
diff --git a/src/schema.sql b/src/schema.sql
new file mode 100644
index 0000000..4ac3d84
--- /dev/null
+++ b/src/schema.sql
@@ -0,0 +1,35 @@
+CREATE TYPE http_verb AS ENUM('GET', 'HEAD', 'OPTIONS', 'TRACE', 'PUT', 'DELETE', 'POST', 'PATCH', 'CONNECT');
+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 (
+	id bigint NOT NULL,
+	value text NOT NULL,
+
+	CONSTRAINT pk_entities PRIMARY KEY(id),
+	CONSTRAINT uni_entities_value UNIQUE(value)
+);
+
+CREATE TABLE access_log (
+	id serial NOT NULL,
+	hostname bigint NOT NULL,
+	virtual_host bigint NOT NULL,
+	remoteip inet NOT NULL,
+	request_time timestamp(6) NOT NULL,
+	method http_verb NOT NULL,
+	protocol protocol NOT NULL,
+	path bigint NOT NULL,
+	query_string bigint,
+	status smallint NOT NULL,
+	size int NOT NULL,
+	duration interval second(6) NOT NULL,
+	referrer bigint,
+	user_agent bigint,
+
+	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)
+);
diff --git a/test/Jamfile.jam b/test/Jamfile.jam
index a008606..8460cef 100644
--- a/test/Jamfile.jam
+++ b/test/Jamfile.jam
@@ -1,14 +1,20 @@
 lib boost_unit_test_framework : : <link>shared ;
+lib dbpptestcore : : <link>shared ;
+lib stdc++fs ;
 
 path-constant src : ../src ;
 path-constant test : . ;
 
 run test-ingest.cpp :
 	-- :
+	../src/schema.sql
 	:
 	<define>BOOST_TEST_DYN_LINK
 	<define>SRC=\"$(src)\"
 	<define>TEST=\"$(test)\"
 	<library>$(src)//webstat
+	<library>..//dbpp-postgresql
 	<library>boost_unit_test_framework
+	<library>dbpptestcore
+	<library>stdc++fs
 	;
diff --git a/test/test-ingest.cpp b/test/test-ingest.cpp
index 0bbeeae..af063f3 100644
--- a/test/test-ingest.cpp
+++ b/test/test-ingest.cpp
@@ -2,7 +2,24 @@
 #include <boost/test/data/test_case.hpp>
 #include <boost/test/unit_test.hpp>
 
+#include <dbpp-postgresql/pq-mock.h>
+#include <filesystem>
 #include <ingestor.hpp>
+#include <mockDatabase.h>
+
+#define XSTR(s) STR(s)
+#define STR(s) #s
+const std::filesystem::path SRC_DIR(XSTR(SRC));
+const std::filesystem::path TEST_DIR(XSTR(TEST));
+#undef XSTR
+#undef STR
+
+class Mock : public DB::PluginMock<PQ::Mock> {
+public:
+	Mock() : DB::PluginMock<PQ::Mock>("webstat", {SRC_DIR / "schema.sql"}, "user=postgres dbname=postgres") { }
+};
+
+BOOST_GLOBAL_FIXTURE(Mock);
 
 using ScanValues = std::remove_cvref_t<decltype(std::declval<WebStat::Ingestor::ScanResult>()->values())>;
 template<typename Out> using ParseData = std::tuple<std::string_view, Out>;
-- 
cgit v1.2.3