From e5e8a6548ef156b785aae1b8a37fe71f26146061 Mon Sep 17 00:00:00 2001
From: Guilhem Moulin <guilhem@fripost.org>
Date: Sat, 19 Apr 2025 13:27:49 +0200
Subject: webmap-import: Add a cache layer and store the source file's last
 modification time.

That way we can avoid the expensive unpack+import when the source
file(s) have not been updated since the last run.  The check can be
bypassed with a new flag `--force`.

We use a sequence for the FID:s (primary key) and a UNIQUE constraint on
triplets (source_path, archive_member, layername) as GDAL doesn't
support multicolumns primary keys.

To avoid races between the stat(2) calls, gdal.OpenEx() and updates via
`webmap-download` runs we place a shared lock on the downloaded files.
One could resort to some tricks to eliminate the race between the first
two, but there is also some value in having consistency during the
entire execution of the script (a single source file can be used by
multiple layers for instance, and it makes sense to use the very same
file for all layers in that case).

We also intersperse dso.FlushCache() calls between _importSource() calls
in order to force the PG driver to call EndCopy() to detect errors and
trigger a rollback when _importSource() fails.
---
 schema.sql | 84 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 82 insertions(+), 2 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 307acac..f56f711 100644
--- a/schema.sql
+++ b/schema.sql
@@ -2,8 +2,8 @@
 -- PostgreSQL database dump
 --
 
--- Dumped from database version 15.8 (Debian 15.8-0+deb12u1)
--- Dumped by pg_dump version 15.8 (Debian 15.8-0+deb12u1)
+-- Dumped from database version 15.12 (Debian 15.12-0+deb12u2)
+-- Dumped by pg_dump version 15.12 (Debian 15.12-0+deb12u2)
 
 SET statement_timeout = 0;
 SET lock_timeout = 0;
@@ -2755,6 +2755,49 @@ ALTER TABLE postgis."vbk:vindkraftverk_ogc_fid_seq" OWNER TO webmap_import;
 ALTER SEQUENCE postgis."vbk:vindkraftverk_ogc_fid_seq" OWNED BY postgis."vbk:vindkraftverk".ogc_fid;
 
 
+--
+-- Name: sourcecache; Type: TABLE; Schema: public; Owner: webmap_import
+--
+
+CREATE TABLE public.sourcecache (
+    ogc_fid bigint NOT NULL,
+    source_path character varying(2047) NOT NULL,
+    archive_member character varying(2047) NOT NULL,
+    layername character varying(255) NOT NULL,
+    mtime_ns bigint
+);
+
+
+ALTER TABLE public.sourcecache OWNER TO webmap_import;
+
+--
+-- Name: TABLE sourcecache; Type: COMMENT; Schema: public; Owner: webmap_import
+--
+
+COMMENT ON TABLE public.sourcecache IS 'Metadata cache for sources files';
+
+
+--
+-- Name: sourcecache_ogc_fid_seq; Type: SEQUENCE; Schema: public; Owner: webmap_import
+--
+
+CREATE SEQUENCE public.sourcecache_ogc_fid_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER TABLE public.sourcecache_ogc_fid_seq OWNER TO webmap_import;
+
+--
+-- Name: sourcecache_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: webmap_import
+--
+
+ALTER SEQUENCE public.sourcecache_ogc_fid_seq OWNED BY public.sourcecache.ogc_fid;
+
+
 --
 -- Name: dammar ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import
 --
@@ -3126,6 +3169,13 @@ ALTER TABLE ONLY postgis."vbk:projekteringsomraden" ALTER COLUMN ogc_fid SET DEF
 ALTER TABLE ONLY postgis."vbk:vindkraftverk" ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis."vbk:vindkraftverk_ogc_fid_seq"'::regclass);
 
 
+--
+-- Name: sourcecache ogc_fid; Type: DEFAULT; Schema: public; Owner: webmap_import
+--
+
+ALTER TABLE ONLY public.sourcecache ALTER COLUMN ogc_fid SET DEFAULT nextval('public.sourcecache_ogc_fid_seq'::regclass);
+
+
 --
 -- Name: dammar dammar_DammID_key; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import
 --
@@ -4006,6 +4056,22 @@ ALTER TABLE ONLY postgis."vbk:vindkraftverk"
     ADD CONSTRAINT "vbk:vindkraftverk_pkey" PRIMARY KEY (ogc_fid);
 
 
+--
+-- Name: sourcecache sourcecache_pkey; Type: CONSTRAINT; Schema: public; Owner: webmap_import
+--
+
+ALTER TABLE ONLY public.sourcecache
+    ADD CONSTRAINT sourcecache_pkey PRIMARY KEY (ogc_fid);
+
+
+--
+-- Name: sourcecache sourcecache_source_path_archive_member_layername_key; Type: CONSTRAINT; Schema: public; Owner: webmap_import
+--
+
+ALTER TABLE ONLY public.sourcecache
+    ADD CONSTRAINT sourcecache_source_path_archive_member_layername_key UNIQUE (source_path, archive_member, layername);
+
+
 --
 -- Name: dammar_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import
 --
@@ -5156,6 +5222,20 @@ GRANT SELECT ON TABLE postgis."vbk:vindkraftverk" TO webmap_guest;
 GRANT SELECT,USAGE ON SEQUENCE postgis."vbk:vindkraftverk_ogc_fid_seq" TO webmap_guest;
 
 
+--
+-- Name: TABLE sourcecache; Type: ACL; Schema: public; Owner: webmap_import
+--
+
+GRANT SELECT ON TABLE public.sourcecache TO webmap_guest;
+
+
+--
+-- Name: SEQUENCE sourcecache_ogc_fid_seq; Type: ACL; Schema: public; Owner: webmap_import
+--
+
+GRANT SELECT,USAGE ON SEQUENCE public.sourcecache_ogc_fid_seq TO webmap_guest;
+
+
 --
 -- PostgreSQL database dump complete
 --
-- 
cgit v1.2.3