From 2a801feebdf0f1f9890933f62cd4aaf0f00ad333 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Thu, 14 Sep 2023 08:32:11 -0500 Subject: [PATCH] Avoid hardcoding in Makefiles --- .gitignore | 2 + db/psql_setup.m4 | 30 ++++++++++ db/run_createdb.m4 | 34 +++++++++++ db/schemas/Makefile | 3 + include/global_constants.m4 | 2 - make_files/defaults.mk | 11 +--- make_files/make_db.mk | 109 ++++++++++++++++++++++-------------- make_files/things.mk | 3 + 8 files changed, 139 insertions(+), 55 deletions(-) create mode 100644 db/psql_setup.m4 create mode 100644 db/run_createdb.m4 diff --git a/.gitignore b/.gitignore index 6af8431..e5c365e 100644 --- a/.gitignore +++ b/.gitignore @@ -3,6 +3,8 @@ db/schemas/**/*.sql db/*.sql # Generated shell scripts +db/psql_setup.sh +db/run_createdb.sh db/set_timezone.sh # Built image files diff --git a/db/psql_setup.m4 b/db/psql_setup.m4 new file mode 100644 index 0000000..17f6598 --- /dev/null +++ b/db/psql_setup.m4 @@ -0,0 +1,30 @@ +#!/bin/sh +# 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 . +# +# Set the default time zone for WITH TIMEZONE data types. +# +# Karl O. Pinc +# +dnl +dnl m4 includes +include(`macros.m4')dnl +include(`constants.m4')dnl + +sdb_generated_comment()dnl + +printf 'SET client_min_messages TO WARNING;\n' +printf "SET plpgsql.extra_warnings TO 'all'\;\n" +printf 'SET ROLE sdb_admin_group;\n' diff --git a/db/run_createdb.m4 b/db/run_createdb.m4 new file mode 100644 index 0000000..bc9678c --- /dev/null +++ b/db/run_createdb.m4 @@ -0,0 +1,34 @@ +#!/bin/sh +# 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 . +# +# Set the default time zone for WITH TIMEZONE data types. +# +# Karl O. Pinc +# +dnl +dnl m4 includes +include(`macros.m4')dnl +include(`constants.m4')dnl + +sdb_generated_comment()dnl + +createdb --encoding='SQL_ASCII' \ + --locale='C' \ + --template='template0' \ + --owner='sdb_admin_group' \ + --username="${ADMINUSER}" \ + --host="${HOST}" \ + ${TARGET_DB} "${NEW_DESCRIPTION}" diff --git a/db/schemas/Makefile b/db/schemas/Makefile index d180f33..93cf30b 100644 --- a/db/schemas/Makefile +++ b/db/schemas/Makefile @@ -63,6 +63,9 @@ export M4_GLOBAL_INCLUDE_PATH := ../../$(M4_GLOBAL_INCLUDE_PATH) export M4_DB_INCLUDE_PATH := ../../$(M4_DB_INCLUDE_PATH) M4_DB_INCLUDE_ARGS := -I $(M4_DB_INCLUDE_PATH) -I $(M4_GLOBAL_INCLUDE_PATH) +# psql helper script +PSQL_SETUP := ../../$(PSQL_SETUP) + # Rebuild when any of these change export DB_DEPENDS := $(patsubst %,../../%,$(DB_DEPENDS)) Makefile \ check_target_schema.sh diff --git a/include/global_constants.m4 b/include/global_constants.m4 index 3972ac0..6974946 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -34,8 +34,6 @@ dnl dnl Database permissions dnl -dnl CAUTION: The Makefiles will need to be editing correspondingly if you -dnl change the sdb_admin_group. define(`sdb_admin_group', `admin') define(`sdb_reader', `reader') define(`sdb_writer', `writer') diff --git a/make_files/defaults.mk b/make_files/defaults.mk index 291e1cf..3071429 100644 --- a/make_files/defaults.mk +++ b/make_files/defaults.mk @@ -44,19 +44,10 @@ export HOST := sokwe-dbs.postgres.database.azure.com export ADMINUSER := $USER -# The group (aka role) that makes a user an admin or a developer. -# CAUTION: Changing this value requires a corresponding change to -# include/global_constants.m4. -# This is not part of make's "help" output since we want to -# discourage any change. -export ADMIN_GROUP := admin - # For invoking psql everywhere. export PSQL_ARGS_MINIMAL := -U $(ADMINUSER) -d $(TARGET_DB) -h $(HOST) export PSQL_ARGS := --tuples-only -q $(PSQL_ARGS_MINIMAL) export PSQL_SINGLE_TRANS := --single-transaction --file - # Variable to setup connections before use. -export PSQL_SETUP := printf 'SET client_min_messages TO WARNING;\n' ; \ - printf "SET plpgsql.extra_warnings TO 'all'\;\n" ; \ - printf 'SET ROLE $(ADMIN_GROUP);\n' ; +export PSQL_SETUP := db/psql_setup.sh diff --git a/make_files/make_db.mk b/make_files/make_db.mk index 32969e4..8f33d79 100644 --- a/make_files/make_db.mk +++ b/make_files/make_db.mk @@ -85,6 +85,9 @@ VACUUM_CLEANUP := 2>&1 \ | { result=$$? ; \ grep -qv '^Warning: .* only superuser can vacuum it$$' ; \ exit $$result ; } +# Almost all psql execution needs these targets, and who knows what sub-makes +# need (except for "clean" and "check") so always depend sub-makes on this too. +PSQL_DEPENDS := db/psql_setup.sh ## ## The available targets for make (make TARGET) are: @@ -122,8 +125,12 @@ check: $(MAKE) -C db/schemas check ## install-db Install all database objects (schemas, tables, etc.) +# Because $? is used, and we've "extra" dependencies, we need a level of +# indirection. .PHONY: install-db -install-db: db/schemas/createschemas.sql \ +install-db: $(PSQL_DEPENDS) do-install-db +.PHONY: do-install-db +do-install-db: db/schemas/createschemas.sql \ $(CREATE_DB_TARGETS) $(CREATE_SUB_DB_TARGETS) ( $(PSQL_SETUP) \ cat $? ; ) \ @@ -136,6 +143,7 @@ install-db: db/schemas/createschemas.sql \ # This drops entire schemas to delete the database objects. .PHONY: reinstall-db reinstall-db: db/schemas/dropschemas.sql \ + $(PSQL_DEPENDS) \ $(CREATE_DB_TARGETS) $(CREATE_SUB_DB_TARGETS) ( $(PSQL_SETUP) \ cat db/schemas/dropschemas.sql ; ) \ @@ -149,8 +157,12 @@ reinstall-db: db/schemas/dropschemas.sql \ ## destroy-db Drop all the database objects, individually # This drops each individual database object, then the schemas. +# Because $? is used, and we've "extra" dependencies, we need a level of +# indirection. .PHONY: destroy-db -destroy-db: $(DROP_SUB_DB_TARGETS) $(DROP_DB_TARGETS) +destroy-db: $(PSQL_DEPENDS) do-destroy-db +.PHONY: do-destroy-db +do-destroy-db: $(DROP_SUB_DB_TARGETS) $(DROP_DB_TARGETS) ( $(PSQL_SETUP) \ cat $? ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y @@ -178,21 +190,21 @@ installtables droptables \ installtriggers droptriggers \ installfunctions dropfunctions \ installviews dropviews \ -installindexes dropindexes: +installindexes dropindexes: $(PSQL_DEPENDS) $(MAKE) -C db/schemas $@ ## installtypes Install all the types into the db .PHONY: installtypes -installtypes: createtypes.sql +installtypes: $(PSQL_DEPENDS) createtypes.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat createtypes.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ## droptypes Drop all the types from the db .PHONY: droptypes -droptypes: droptypes.sql +droptypes: $(PSQL_DEPENDS) droptypes.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat droptypes.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y @@ -202,16 +214,19 @@ droptypes: droptypes.sql ## retable Re-install all the tables .PHONY: retable -retable: db/schemas/droptables.sql db/schemas/createtables.sql +retable: $(PSQL_DEPENDS) db/schemas/droptables.sql db/schemas/createtables.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat db/schemas/droptables.sql \ + db/schemas/createtables.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ## retrigger Re-install all the triggers .PHONY: retrigger -retrigger: db/schemas/droptriggers.sql db/schemas/createtriggers.sql +retrigger: $(PSQL_DEPENDS) \ + db/schemas/droptriggers.sql db/schemas/createtriggers.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat db/schemas/droptriggers.sql \ + db/schemas/createtriggers.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ## retriggernolock Re-install all the triggers, outside of a transaction. @@ -219,38 +234,47 @@ retrigger: db/schemas/droptriggers.sql db/schemas/createtriggers.sql ## when an ongoing transaction or something else has a ## lock and the triggers need updating. .PHONY: retriggernolock -retriggernolock: db/schemas/droptriggers.sql db/schemas/createtriggers.sql +retriggernolock: $(PSQL_DEPENDS) \ + db/schemas/droptriggers.sql db/schemas/createtriggers.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat db/schemas/droptriggers.sql \ + db/schemas/createtriggers.sql ; ) \ | psql $(PSQL_ARGS) ## refunction Re-install all the functions .PHONY: refunction -refunction: db/schemas/dropfunctions.sql db/schemas/createfunctions.sql +refunction: $(PSQL_DEPENDS) \ + db/schemas/dropfunctions.sql db/schemas/createfunctions.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat db/schemas/dropfunctions.sql \ + db/schemas/createfunctions.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ## review Re-install all the views .PHONY: review -review: db/scheams/dropviews.sql db/schemas/createviews.sql +review: $(PSQL_DEPENDS) \ + db/scheams/dropviews.sql db/schemas/createviews.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat db/scheams/dropviews.sql \ + db/schemas/createviews.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ## reindex Re-install all the indexes .PHONY: reindex -reindex: db/schemas/dropindexes.sql db/schemas/createindexes.sql +reindex: $(PSQL_DEPENDS) \ + db/schemas/dropindexes.sql db/schemas/createindexes.sql ( $(PSQL_SETUP) \ - cat $? ; ) \ + cat db/schemas/dropindexes.sql \ + db/schemas/createindexes.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ### retype Re-install all the data types ### (Less than useful since tables will be dropped.) #.PHONY: retype -#retype: droptypes.sql createtypes.sql +#retype: $(PSQL_DEPENDS) droptypes.sql createtypes.sql # ( $(PSQL_SETUP) \ -# cat $? ; ) \ +# cat droptypes.sql \ +# createtypes.sql ; ) \ # | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ## clean-db Remove all the generated files @@ -272,19 +296,19 @@ clean-db: ## appropriate, set permissions on the designated ## database object. .PHONY: sql_file -sql_file: +sql_file: $(PSQL_DEPENDS) $(MAKE) -C db/schemas sql_file ## install_schemas Create the schemas .PHONY: install_schemas -install_schemas: db/schemas/createschemas.sql +install_schemas: $(PSQL_DEPENDS) db/schemas/createschemas.sql ( $(PSQL_SETUP) \ cat db/schemas/createschemas.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y ## destroy_schemas Drop the schemas .PHONY: destroy_schemas -destroy_schemas: db/schemas/dropschemas.sql +destroy_schemas: $(PSQL_DEPENDS) db/schemas/dropschemas.sql ( $(PSQL_SETUP) \ cat db/schemas/dropschemas.sql ; ) \ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y @@ -328,7 +352,7 @@ install_schema_tables destroy_schema_tables \ install_schema_triggers destroy_schema_triggers \ install_schema_functions destroy_schema_functions \ install_schema_views destroy_schema_views \ -install_schema_indexes destroy_schema_indexes: +install_schema_indexes destroy_schema_indexes: $(PSQL_DEPENDS) $(MAKE) -C db/schemas $@ ## db/schemas/createschemas.sql @@ -372,7 +396,7 @@ db/schemas/createtables.sql db/schemas/droptables.sql \ db/schemas/createtriggers.sql db/schemas/droptriggers.sql \ db/schemas/createfunctions.sql db/schemas/dropfunctions.sql \ db/schemas/createviews.sql db/schemas/dropviews.sql \ -db/schemas/createindexes.sql db/schemas/dropindexes.sql: +db/schemas/createindexes.sql db/schemas/dropindexes.sql: $(PSQL_DEPENDS) $(MAKE) -C db/schemas $$(basename $@) ## db/createtypes.sql Build file of sql which makes data types @@ -386,7 +410,7 @@ $(DB_TARGETS) db/creategroups.sql: %.sql: %.m4 $(DB_DEPENDS) # It'd be nice to be able to empty the db of all data, but there's # no point until there's some regression testing. #.PHONY: empty -#empty: +#empty: $(PSQL_DEPENDS) # $(MAKE) -C test reset # No target for regression testing at the moment @@ -487,16 +511,15 @@ $(DB_TARGETS) db/creategroups.sql: %.sql: %.m4 $(DB_DEPENDS) ## Additional databases may be created using individual logins. Creating ## them with "make" will properly initialize them for use with SokweDB. -GENERATED_SHELL := db/set_timezone.sh +export NEW_DESCRIPTION + +# Have m4 generate some shell scripts which contain expanded macro constants. +GENERATED_SHELL := db/set_timezone.sh \ + db/run_createdb.sh \ + db/psql_setup.sh # Build the statements used to setup dbs. -CREATE_DB := createdb --encoding='SQL_ASCII' \ - --locale='C' \ - --template='template0' \ - --owner='$(ADMIN_GROUP)' \ - --username='$(ADMINUSER)' \ - --host='$(HOST)' \ - $(TARGET_DB) "$(NEW_DESCRIPTION)" +CREATE_DB := db/run_createdb.sh # (Note which contain double and which single quotes, which affects how the # printf statement using them is executed.) # ($$user because make otherwise interprets the $.) @@ -525,7 +548,7 @@ SET_PASSWORD := ALTER ROLE $(TARGET_ROLE) PASSWORD '$(NEW_PASSWORD)' ## user to use to login. This is expected to be ## executed as the Unix "root" user. .PHONY: create-adminuser -create-adminuser: +create-adminuser: $(PSQL_DEPENDS) [ -n "$(NEW_PASSWORD)" ] \ || { printf 'The NEW_PASSWORD variable must be set\n' >&2 ; \ exit 1 ; } @@ -552,11 +575,11 @@ create-groups: db/creategroups.sql ## transaction. Can create the database and then fail, ## leaving the cluster in an unusual state. .PHONY: init-database -init-database: $(GENERATED_SHELL) +init-database: $(GENERATED_SHELL) $(PSQL_DEPENDS) [ -n "$(TARGET_DB)" ] \ || { printf 'The TARGET_DB variable must be set\n' >&2 ; \ exit 1 ; } - [ -n "$(NEW_DESCRIPTION)" ] \ + [ -n "$${NEW_DESCRIPTION}" ] \ || { printf 'The NEW_DESCRIPTION variable must be set\n' >&2 ; \ exit 1 ; } $(CREATE_DB) @@ -595,7 +618,7 @@ drop-public-root: ## set-password Set the password of TARGET_ROLE to NEW_PASSWORD .PHONY: set-password -set-password: +set-password: $(PSQL_DEPENDS) [ -n "$(NEW_PASSWORD)" ] \ || { printf 'The NEW_PASSWORD variable must be set\n' >&2 ; \ exit 1 ; } @@ -609,7 +632,7 @@ set-password: ## This target otherwise works like create-adminuser, ## above. .PHONY: create-superuser -create-superuser: +create-superuser: $(PSQL_DEPENDS) [ -n "$(NEW_PASSWORD)" ] \ || { printf 'The NEW_PASSWORD variable must be set\n' >&2 ; \ exit 1 ; } @@ -624,7 +647,7 @@ create-superuser: ## set-search_path Set the default search_path the database named ## by the TARGET_DB variable. .PHONY: set-search_path -set-search_path: +set-search_path: $(PSQL_DEPENDS) ( $(PSQL_SETUP) \ printf '$(SET_SEARCH_PATH)\n' ; \ ) \ @@ -633,7 +656,7 @@ set-search_path: ## set-datestyle Set the default DATESTYLE in the database named ## by the TARGET_DB variable. .PHONY: set-datestyle -set-datestyle: +set-datestyle: $(PSQL_DEPENDS) ( $(PSQL_SETUP) \ printf "$(SET_DATESTYLE)\n" ; \ ) \ @@ -642,7 +665,7 @@ set-datestyle: ## set-timezone Set the default TimeZone in the database named ## by the TARGET_DB variable. .PHONY: set-timezone -set-timezone: db/set_timezone.sh +set-timezone: $(PSQL_DEPENDS) db/set_timezone.sh ( $(PSQL_SETUP) \ db/set_timezone.sh ; \ ) \ diff --git a/make_files/things.mk b/make_files/things.mk index 03990d0..cfb8f5f 100644 --- a/make_files/things.mk +++ b/make_files/things.mk @@ -44,6 +44,9 @@ M4_GLOBAL_INCLUDE_PATH := ../../$(M4_GLOBAL_INCLUDE_PATH) M4_DB_INCLUDE_PATH := ../../$(M4_DB_INCLUDE_PATH) M4_DB_INCLUDE_ARGS := -I $(M4_DB_INCLUDE_PATH) -I $(M4_GLOBAL_INCLUDE_PATH) +# psql helper script +PSQL_SETUP := ../../$(PSQL_SETUP) + # All the files that might matter when building the database DB_DEPENDS := $(patsubst %,../../%,$(DB_DEPENDS)) Makefile -- 2.34.1