From 4cf2d75d83a6e97accf0139674656a9aa2903479 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Sun, 26 Nov 2023 14:22:37 -0600 Subject: [PATCH] Move triggers and functions the user need not see into the "lib" schema --- db/schemas/Makefile | 14 +++++++-- db/schemas/createschemas.m4 | 2 ++ db/schemas/lib/triggers/Makefile | 31 +++++++++++++++++++ .../triggers/create/biography.m4 | 0 .../triggers/create/comm_ids.m4 | 0 .../triggers/create/comm_membs.m4 | 0 .../triggers/create/departtypes.m4 | 0 .../triggers/drop/biography.m4 | 0 .../{lookup => lib}/triggers/drop/comm_ids.m4 | 0 .../triggers/drop/comm_membs.m4 | 0 .../triggers/drop/departtypes.m4 | 0 make_files/make_cluster.mk | 2 +- 12 files changed, 46 insertions(+), 3 deletions(-) create mode 100644 db/schemas/lib/triggers/Makefile rename db/schemas/{sokwedb => lib}/triggers/create/biography.m4 (100%) rename db/schemas/{lookup => lib}/triggers/create/comm_ids.m4 (100%) rename db/schemas/{sokwedb => lib}/triggers/create/comm_membs.m4 (100%) rename db/schemas/{lookup => lib}/triggers/create/departtypes.m4 (100%) rename db/schemas/{sokwedb => lib}/triggers/drop/biography.m4 (100%) rename db/schemas/{lookup => lib}/triggers/drop/comm_ids.m4 (100%) rename db/schemas/{sokwedb => lib}/triggers/drop/comm_membs.m4 (100%) rename db/schemas/{lookup => lib}/triggers/drop/departtypes.m4 (100%) diff --git a/db/schemas/Makefile b/db/schemas/Makefile index 1f3deff..b32dc7e 100644 --- a/db/schemas/Makefile +++ b/db/schemas/Makefile @@ -32,13 +32,14 @@ include ../../make_files/help.mk # This is important because the tables containing foreign keys must be put # into the database after the tables they reference, etc. ORDER := lookup \ - sokwedb + sokwedb \ + lib # upload # The schemas that contain tables, functions, etc. TABLE_SCHEMAS := lookup sokwedb FUNCTION_SCHEMAS := sokwedb -TRIGGER_SCHEMAS := lookup sokwedb +TRIGGER_SCHEMAS := lib VIEW_SCHEMAS := sokwedb INDEX_SCHEMAS := sokwedb COMMENT_SCHEMAS := lookup sokwedb @@ -94,6 +95,14 @@ DB_CACHE := db_cache # separate prerequsite that keeps track of cache creation time. DB_CACHE_PREREQ := $(DB_CACHE)/creationtimefile +# The search path to use when putting functions in the "lib" schema +LIB_SEARCH_PATH := printf \ + '%s\n;' \ + "SELECT \ + set_config('search_path' \ + , 'lib, ' || current_setting('search_path') \ + , FALSE);" ; + # Derived variables # (These shouldn't need editing.) # (The use of $(ORDER) and filtering is to ensure the correct ordering.) @@ -598,6 +607,7 @@ $(DB_CACHE_PREREQ): $(DB_CACHE) $(DB_CACHE)/gen_func_comment_tmpl: $(PSQL_DEPENDS) $(DB_CACHE_PREREQ) \ gen_func_comment_tmpl.sql ( $(PSQL_SETUP) \ + $(LIB_SEARCH_PATH) \ cat gen_func_comment_tmpl.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y touch $(DB_CACHE)/gen_func_comment_tmpl diff --git a/db/schemas/createschemas.m4 b/db/schemas/createschemas.m4 index 3aaecd1..c9c1e53 100644 --- a/db/schemas/createschemas.m4 +++ b/db/schemas/createschemas.m4 @@ -21,10 +21,12 @@ include(`copyright.m4')dnl include(`constants.m4')dnl dnl +CREATE SCHEMA IF NOT EXISTS lib; CREATE SCHEMA IF NOT EXISTS lookup; CREATE SCHEMA IF NOT EXISTS sokwedb; CREATE SCHEMA IF NOT EXISTS upload; +GRANT USAGE ON SCHEMA lib TO sdb_reader, sdb_writer; GRANT USAGE ON SCHEMA lookup TO sdb_reader, sdb_writer; GRANT USAGE ON SCHEMA sokwedb TO sdb_reader, sdb_writer; GRANT USAGE ON SCHEMA upload TO sdb_reader, sdb_writer; diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile new file mode 100644 index 0000000..dfa0a0f --- /dev/null +++ b/db/schemas/lib/triggers/Makefile @@ -0,0 +1,31 @@ +# Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/ +# +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU Affero General Public License for more details. +# +# You should have received a copy of the GNU Affero General Public License +# along with this program. If not, see . +# +# Makefile for triggers +# +# Karl O. Pinc + +# This determines the order in which the triggers are put into the database. +# This is not particuarly significant but must contain every trigger file. +ORDER := comm_ids \ + departtypes \ + biography \ + comm_membs + +## +## CAUTION: This Makefile is not designed to be run directly. It is normally +## invoked by another Makefile. + +include ../../../../make_files/things.mk diff --git a/db/schemas/sokwedb/triggers/create/biography.m4 b/db/schemas/lib/triggers/create/biography.m4 similarity index 100% rename from db/schemas/sokwedb/triggers/create/biography.m4 rename to db/schemas/lib/triggers/create/biography.m4 diff --git a/db/schemas/lookup/triggers/create/comm_ids.m4 b/db/schemas/lib/triggers/create/comm_ids.m4 similarity index 100% rename from db/schemas/lookup/triggers/create/comm_ids.m4 rename to db/schemas/lib/triggers/create/comm_ids.m4 diff --git a/db/schemas/sokwedb/triggers/create/comm_membs.m4 b/db/schemas/lib/triggers/create/comm_membs.m4 similarity index 100% rename from db/schemas/sokwedb/triggers/create/comm_membs.m4 rename to db/schemas/lib/triggers/create/comm_membs.m4 diff --git a/db/schemas/lookup/triggers/create/departtypes.m4 b/db/schemas/lib/triggers/create/departtypes.m4 similarity index 100% rename from db/schemas/lookup/triggers/create/departtypes.m4 rename to db/schemas/lib/triggers/create/departtypes.m4 diff --git a/db/schemas/sokwedb/triggers/drop/biography.m4 b/db/schemas/lib/triggers/drop/biography.m4 similarity index 100% rename from db/schemas/sokwedb/triggers/drop/biography.m4 rename to db/schemas/lib/triggers/drop/biography.m4 diff --git a/db/schemas/lookup/triggers/drop/comm_ids.m4 b/db/schemas/lib/triggers/drop/comm_ids.m4 similarity index 100% rename from db/schemas/lookup/triggers/drop/comm_ids.m4 rename to db/schemas/lib/triggers/drop/comm_ids.m4 diff --git a/db/schemas/sokwedb/triggers/drop/comm_membs.m4 b/db/schemas/lib/triggers/drop/comm_membs.m4 similarity index 100% rename from db/schemas/sokwedb/triggers/drop/comm_membs.m4 rename to db/schemas/lib/triggers/drop/comm_membs.m4 diff --git a/db/schemas/lookup/triggers/drop/departtypes.m4 b/db/schemas/lib/triggers/drop/departtypes.m4 similarity index 100% rename from db/schemas/lookup/triggers/drop/departtypes.m4 rename to db/schemas/lib/triggers/drop/departtypes.m4 diff --git a/make_files/make_cluster.mk b/make_files/make_cluster.mk index 7c2a784..56614e1 100644 --- a/make_files/make_cluster.mk +++ b/make_files/make_cluster.mk @@ -161,7 +161,7 @@ CREATE_DB := db/run_createdb.sh # printf statement using them is executed.) # ($$user because make otherwise interprets the $.) SET_SEARCH_PATH := ALTER DATABASE $(TARGET_DB) \ - SET search_path = sokwedb, lookup, upload, "$$user"; + SET search_path = sokwedb, lookup, upload, lib, "$$user"; SET_DATESTYLE := ALTER DATABASE $(TARGET_DB) \ SET DATESTYLE = 'ISO,MDY'; SET_ISOLATION_LEVEL := ALTER DATABASE $(TARGET_DB) \ -- 2.34.1