From 80882acd6ba407847fed0ef308e440b88796e0e1 Mon Sep 17 00:00:00 2001
From: Guilhem Moulin <guilhem@fripost.org>
Date: Wed, 23 Apr 2025 17:55:57 +0200
Subject: Change layer cache logic to target destination layers rather than
 sources.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

In a future commit we'll fingerprint layers to detect changes.
Comparing modification times is not enough since some sources (for
instance Naturvårdsverket's SCI_Rikstackande) are updated on the server
even though no objects are being added; the source layer remains
unchanged but the file differs because of OBJECTID changes we are not
interested in.

Rather than using another cache layer/table for fingerprints, we cache
destination layernames rather than triplets (source_path, archive_member,
layername), along with the time at which the import was started rather
than source_path's mtime.

There is indeed no value in having exact source_path's mtime in the
cache.  What we need is simply a way to detect whether source paths have
been updated in a subsequent run.  Thanks to the shared locks the ctime
of any updated source path will be at least the time when the locks are
released, thereby exceeding the last_updated value.
---
 schema.sql | 48 +++++++++++++++++++++++-------------------------
 1 file changed, 23 insertions(+), 25 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index b4d7608..e5a1002 100644
--- a/schema.sql
+++ b/schema.sql
@@ -2751,32 +2751,30 @@ ALTER SEQUENCE postgis."vbk:vindkraftverk_ogc_fid_seq" OWNED BY postgis."vbk:vin
 
 
 --
--- Name: sourcecache; Type: TABLE; Schema: public; Owner: webmap_import
+-- Name: layercache; Type: TABLE; Schema: public; Owner: webmap_import
 --
 
-CREATE TABLE public.sourcecache (
+CREATE TABLE public.layercache (
     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
+    last_updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
 );
 
 
-ALTER TABLE public.sourcecache OWNER TO webmap_import;
+ALTER TABLE public.layercache OWNER TO webmap_import;
 
 --
--- Name: TABLE sourcecache; Type: COMMENT; Schema: public; Owner: webmap_import
+-- Name: TABLE layercache; Type: COMMENT; Schema: public; Owner: webmap_import
 --
 
-COMMENT ON TABLE public.sourcecache IS 'Metadata cache for sources files';
+COMMENT ON TABLE public.layercache IS 'Layer metadata cache';
 
 
 --
--- Name: sourcecache_ogc_fid_seq; Type: SEQUENCE; Schema: public; Owner: webmap_import
+-- Name: layercache_ogc_fid_seq; Type: SEQUENCE; Schema: public; Owner: webmap_import
 --
 
-CREATE SEQUENCE public.sourcecache_ogc_fid_seq
+CREATE SEQUENCE public.layercache_ogc_fid_seq
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
@@ -2784,13 +2782,13 @@ CREATE SEQUENCE public.sourcecache_ogc_fid_seq
     CACHE 1;
 
 
-ALTER TABLE public.sourcecache_ogc_fid_seq OWNER TO webmap_import;
+ALTER TABLE public.layercache_ogc_fid_seq OWNER TO webmap_import;
 
 --
--- Name: sourcecache_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: webmap_import
+-- Name: layercache_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;
+ALTER SEQUENCE public.layercache_ogc_fid_seq OWNED BY public.layercache.ogc_fid;
 
 
 --
@@ -3165,10 +3163,10 @@ ALTER TABLE ONLY postgis."vbk:vindkraftverk" ALTER COLUMN ogc_fid SET DEFAULT ne
 
 
 --
--- Name: sourcecache ogc_fid; Type: DEFAULT; Schema: public; Owner: webmap_import
+-- Name: layercache 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);
+ALTER TABLE ONLY public.layercache ALTER COLUMN ogc_fid SET DEFAULT nextval('public.layercache_ogc_fid_seq'::regclass);
 
 
 --
@@ -4044,19 +4042,19 @@ ALTER TABLE ONLY postgis."vbk:vindkraftverk"
 
 
 --
--- Name: sourcecache sourcecache_pkey; Type: CONSTRAINT; Schema: public; Owner: webmap_import
+-- Name: layercache layercache_layername_key; Type: CONSTRAINT; Schema: public; Owner: webmap_import
 --
 
-ALTER TABLE ONLY public.sourcecache
-    ADD CONSTRAINT sourcecache_pkey PRIMARY KEY (ogc_fid);
+ALTER TABLE ONLY public.layercache
+    ADD CONSTRAINT layercache_layername_key UNIQUE (layername);
 
 
 --
--- Name: sourcecache sourcecache_source_path_archive_member_layername_key; Type: CONSTRAINT; Schema: public; Owner: webmap_import
+-- Name: layercache layercache_pkey; 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);
+ALTER TABLE ONLY public.layercache
+    ADD CONSTRAINT layercache_pkey PRIMARY KEY (ogc_fid);
 
 
 --
@@ -5210,17 +5208,17 @@ GRANT SELECT,USAGE ON SEQUENCE postgis."vbk:vindkraftverk_ogc_fid_seq" TO webmap
 
 
 --
--- Name: TABLE sourcecache; Type: ACL; Schema: public; Owner: webmap_import
+-- Name: TABLE layercache; Type: ACL; Schema: public; Owner: webmap_import
 --
 
-GRANT SELECT ON TABLE public.sourcecache TO webmap_guest;
+GRANT SELECT ON TABLE public.layercache TO webmap_guest;
 
 
 --
--- Name: SEQUENCE sourcecache_ogc_fid_seq; Type: ACL; Schema: public; Owner: webmap_import
+-- Name: SEQUENCE layercache_ogc_fid_seq; Type: ACL; Schema: public; Owner: webmap_import
 --
 
-GRANT SELECT,USAGE ON SEQUENCE public.sourcecache_ogc_fid_seq TO webmap_guest;
+GRANT SELECT,USAGE ON SEQUENCE public.layercache_ogc_fid_seq TO webmap_guest;
 
 
 --
-- 
cgit v1.2.3

