From dac98568efd76dea7e7149c55a841218ea733782 Mon Sep 17 00:00:00 2001 From: Guilhem Moulin 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(-) 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