From dac98568efd76dea7e7149c55a841218ea733782 Mon Sep 17 00:00:00 2001
From: Guilhem Moulin <guilhem@fripost.org>
Date: Thu, 5 Mar 2026 23:14:36 +0100
Subject: PostgreSQL: Add ogr_system_tables schema.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

Cf. OGR_PG_ENABLE_METADATA=[YES/NO] from gdal ≥3.9.
https://gdal.org/en/stable/drivers/vector/pg.html#configuration-options
---
 schema.sql | 133 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 129 insertions(+), 4 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 2384624..d2d8a23 100644
--- a/schema.sql
+++ b/schema.sql
@@ -2,7 +2,7 @@
 -- PostgreSQL database dump
 --
 
-\restrict tdw7shj1CoJGedu290smNeVwju8Ghxzc3b4Yx4wsD2xKer4vYHgFQN93xACfOgy
+\restrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3
 
 -- Dumped from database version 17.8 (Debian 17.8-0+deb13u1)
 -- Dumped by pg_dump version 17.8 (Debian 17.8-0+deb13u1)
@@ -29,9 +29,9 @@ CREATE DATABASE webmap WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVID
 
 ALTER DATABASE webmap OWNER TO postgres;
 
-\unrestrict tdw7shj1CoJGedu290smNeVwju8Ghxzc3b4Yx4wsD2xKer4vYHgFQN93xACfOgy
+\unrestrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3
 \connect webmap
-\restrict tdw7shj1CoJGedu290smNeVwju8Ghxzc3b4Yx4wsD2xKer4vYHgFQN93xACfOgy
+\restrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3
 
 SET statement_timeout = 0;
 SET lock_timeout = 0;
@@ -148,6 +148,22 @@ ALTER SCHEMA nvr OWNER TO postgres;
 COMMENT ON SCHEMA nvr IS 'Naturvårdsregistret';
 
 
+--
+-- Name: ogr_system_tables; Type: SCHEMA; Schema: -; Owner: postgres
+--
+
+CREATE SCHEMA ogr_system_tables;
+
+
+ALTER SCHEMA ogr_system_tables OWNER TO postgres;
+
+--
+-- Name: SCHEMA ogr_system_tables; Type: COMMENT; Schema: -; Owner: postgres
+--
+
+COMMENT ON SCHEMA ogr_system_tables IS 'OGR metadata';
+
+
 --
 -- Name: sametinget; Type: SCHEMA; Schema: -; Owner: postgres
 --
@@ -316,6 +332,32 @@ CREATE FUNCTION lm_topo250.lansyta_subdivide_func() RETURNS trigger
 
 ALTER FUNCTION lm_topo250.lansyta_subdivide_func() OWNER TO webmap_import;
 
+--
+-- Name: event_trigger_function_for_metadata(); Type: FUNCTION; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+CREATE FUNCTION ogr_system_tables.event_trigger_function_for_metadata() RETURNS event_trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    obj record;
+BEGIN
+  IF has_schema_privilege('ogr_system_tables', 'USAGE') THEN
+   IF has_table_privilege('ogr_system_tables.metadata', 'DELETE') THEN
+    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
+    LOOP
+        IF obj.object_type = 'table' THEN
+            DELETE FROM ogr_system_tables.metadata m WHERE m.schema_name = obj.schema_name AND m.table_name = obj.object_name;
+        END IF;
+    END LOOP;
+   END IF;
+  END IF;
+END;
+$$;
+
+
+ALTER FUNCTION ogr_system_tables.event_trigger_function_for_metadata() OWNER TO webmap_import;
+
 SET default_tablespace = '';
 
 SET default_table_access_method = heap;
@@ -5146,6 +5188,42 @@ ALTER SEQUENCE nvr."Vattenskyddsomrade_ogc_fid_seq" OWNER TO webmap_import;
 ALTER SEQUENCE nvr."Vattenskyddsomrade_ogc_fid_seq" OWNED BY nvr."Vattenskyddsomrade".ogc_fid;
 
 
+--
+-- Name: metadata; Type: TABLE; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+CREATE TABLE ogr_system_tables.metadata (
+    id integer NOT NULL,
+    schema_name text NOT NULL,
+    table_name text NOT NULL,
+    metadata text
+);
+
+
+ALTER TABLE ogr_system_tables.metadata OWNER TO webmap_import;
+
+--
+-- Name: metadata_id_seq; Type: SEQUENCE; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+CREATE SEQUENCE ogr_system_tables.metadata_id_seq
+    AS integer
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+ALTER SEQUENCE ogr_system_tables.metadata_id_seq OWNER TO webmap_import;
+
+--
+-- Name: metadata_id_seq; Type: SEQUENCE OWNED BY; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+ALTER SEQUENCE ogr_system_tables.metadata_id_seq OWNED BY ogr_system_tables.metadata.id;
+
+
 --
 -- Name: layercache; Type: TABLE; Schema: public; Owner: webmap_import
 --
@@ -7263,6 +7341,13 @@ ALTER TABLE ONLY nvr."Varldsarv" ALTER COLUMN ogc_fid SET DEFAULT nextval('nvr."
 ALTER TABLE ONLY nvr."Vattenskyddsomrade" ALTER COLUMN ogc_fid SET DEFAULT nextval('nvr."Vattenskyddsomrade_ogc_fid_seq"'::regclass);
 
 
+--
+-- Name: metadata id; Type: DEFAULT; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+ALTER TABLE ONLY ogr_system_tables.metadata ALTER COLUMN id SET DEFAULT nextval('ogr_system_tables.metadata_id_seq'::regclass);
+
+
 --
 -- Name: layercache ogc_fid; Type: DEFAULT; Schema: public; Owner: webmap_import
 --
@@ -8195,6 +8280,14 @@ ALTER TABLE ONLY nvr."Vattenskyddsomrade"
     ADD CONSTRAINT "Vattenskyddsomrade_pkey" PRIMARY KEY (ogc_fid);
 
 
+--
+-- Name: metadata metadata_schema_name_table_name_key; Type: CONSTRAINT; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+ALTER TABLE ONLY ogr_system_tables.metadata
+    ADD CONSTRAINT metadata_schema_name_table_name_key UNIQUE (schema_name, table_name);
+
+
 --
 -- Name: layercache layercache_layername_key; Type: CONSTRAINT; Schema: public; Owner: webmap_import
 --
@@ -9076,6 +9169,14 @@ GRANT USAGE ON SCHEMA nvr TO webmap_import;
 GRANT USAGE ON SCHEMA nvr TO webmap_guest;
 
 
+--
+-- Name: SCHEMA ogr_system_tables; Type: ACL; Schema: -; Owner: postgres
+--
+
+GRANT USAGE ON SCHEMA ogr_system_tables TO webmap_import;
+GRANT USAGE ON SCHEMA ogr_system_tables TO webmap_guest;
+
+
 --
 -- Name: SCHEMA public; Type: ACL; Schema: -; Owner: pg_database_owner
 --
@@ -9804,6 +9905,20 @@ GRANT SELECT ON TABLE nvr."Vattenskyddsomrade" TO webmap_guest;
 GRANT SELECT,USAGE ON SEQUENCE nvr."Vattenskyddsomrade_ogc_fid_seq" TO webmap_guest;
 
 
+--
+-- Name: TABLE metadata; Type: ACL; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+GRANT SELECT ON TABLE ogr_system_tables.metadata TO webmap_guest;
+
+
+--
+-- Name: SEQUENCE metadata_id_seq; Type: ACL; Schema: ogr_system_tables; Owner: webmap_import
+--
+
+GRANT SELECT,USAGE ON SEQUENCE ogr_system_tables.metadata_id_seq TO webmap_guest;
+
+
 --
 -- Name: TABLE geography_columns; Type: ACL; Schema: public; Owner: postgres
 --
@@ -10105,9 +10220,19 @@ GRANT SELECT ON TABLE vbk.vindkraftverk TO webmap_guest;
 GRANT SELECT,USAGE ON SEQUENCE vbk.vindkraftverk_ogc_fid_seq TO webmap_guest;
 
 
+--
+-- Name: ogr_system_tables_event_trigger_for_metadata; Type: EVENT TRIGGER; Schema: -; Owner: postgres
+--
+
+CREATE EVENT TRIGGER ogr_system_tables_event_trigger_for_metadata ON sql_drop
+   EXECUTE FUNCTION ogr_system_tables.event_trigger_function_for_metadata();
+
+
+ALTER EVENT TRIGGER ogr_system_tables_event_trigger_for_metadata OWNER TO postgres;
+
 --
 -- PostgreSQL database dump complete
 --
 
-\unrestrict tdw7shj1CoJGedu290smNeVwju8Ghxzc3b4Yx4wsD2xKer4vYHgFQN93xACfOgy
+\unrestrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3
 
-- 
cgit v1.2.3

