From 52f4af3d9d5adf4aae0d2878e3503624da61bf76 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <kop@karlpinc.com>
Date: Fri, 8 Sep 2023 16:35:28 -0500
Subject: [PATCH] Beginnings of a cluster initialization build setup

---
 db/README             |  10 ++++
 make_files/make_db.mk | 112 ++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 122 insertions(+)

diff --git a/db/README b/db/README
index 6f6b799..90e2e27 100644
--- a/db/README
+++ b/db/README
@@ -1,3 +1,13 @@
+It is expected that a single PG role own all the database objects.
+This is the ADMINUSER variable of the makefiles.  The problem with
+having various developer's personal SUPERUSER roles owning various
+objects is that when the developer goes away, so should their personal
+roles.  And deleting roles that own things is a problem.  That said,
+the system will work just fine no matter who owns the objects in the
+dbs.
+
+About the build system:
+
 The Makefiles in the db/ directory must be told about what they are to
 build by setting the $(ORDER) variable.  This is because many objects
 (e.g. tables) must be created before other objects (e.g. views) and
diff --git a/make_files/make_db.mk b/make_files/make_db.mk
index 04bc8ad..f577833 100644
--- a/make_files/make_db.mk
+++ b/make_files/make_db.mk
@@ -428,3 +428,115 @@ $(DB_TARGETS): %.sql: %.m4 $(DB_DEPENDS)
 ## WARNING: Dropping database objects can drop related objects from the
 ## user's private schemas.
 
+
+##
+## ########################################################################
+##                       CLUSTER INITIALIZATION
+##
+## The following targets are used to setup the database cluster;
+## do things like create a database and properly initialize it.
+## In the case of Azure hosting, you can also create a postgres
+## instance.
+##
+## Variables (in addition to the DATABASE TARGET variables above):
+##
+##   DB_OWNER
+##     The role which is to own the new database.  This role is expected
+##     to be the ADMINUSER role, the role used to make database objects.
+##   TARGET_USER
+##     The user to be created or otherwise modified.
+##   NEW_DB
+##     The database to be created.
+##   NEW_DESCRIPTION
+##     The description of the new object (database)
+##
+## Usage:
+##  The expectation is that first an administrative user will be created, then
+##  a database created -- owned by the newly created role.
+##  Hint: Use TARGET_DB=template1 when doing this, since your new database
+##  won't yet exist.
+##
+
+DB_OWNER ?= $(ADMINUSER)
+
+# Build the statements used to setup dbs.
+CREATE_DB := createdb --encoding='SQL_ASCII' \
+                      --locale='C' \
+                      --template='template0' \
+                      --owner='$(DB_OWNER)' \
+                      --username='$(ADMINUSER)' \
+                      --host='$(HOST)' \
+                      $(NEW_DB) "$(NEW_DESCRIPTION)"
+# (Note which contain double and which single quotes, which affects how the
+# printf statement using them is executed.)
+# ($$user because make otherwise interprets the $.)
+SET_SEARCH_PATH := ALTER DATABASE $(NEW_DB) \
+                     SET search_path = sokwedb, lookup, upload, "$$user";
+SET_DATESTYLE := ALTER DATABASE $(NEW_DB) \
+                     SET DATESTYLE =  'ISO,MDY';
+CREATE_ADMINUSER := CREATE ROLE $(TARGET_USER) LOGIN SUPERUSER CREATEROLE \
+                    PASSWORD '$(NEW_PASSWORD)';
+
+## The available targets for make (make TARGET) are:
+##
+
+##   create-adminuser   Create the role expected to own all objects
+##                      in the database.  (See the db/README.)
+##                      Note that this role does not have permission
+##                      to create databases.  It does have permission to
+##                      create new users (roles). The TARGET_USER is created,
+##                      using the ADMINUSER to do so.
+.PHONY: create-adminuser
+create-adminuser:
+	[ -n "$(NEW_PASSWORD)" ] \
+           || { printf 'The NEW_PASSWORD variable must be set\n' >&2 ; \
+                exit 1 ; }
+	( $(PSQL_SETUP) \
+	  printf "$(CREATE_ADMINUSER)\n" ; \
+	  ) \
+	  | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+##   init-database      Create and initialize the database named
+##                      by the NEW_DB variable.  This target may be used
+##                      by a regular, non-root, Unix user.  To create the db
+##                      the ADMINUSER variable is used, which must have a
+##                      value different from the usual because it be a role
+##                      with permissions to create databases.  It is
+##                      strongly recommended that the DB_OWNER variable be
+##                      set to the administrative user that will be used to
+##                      create database objects.  (See the db/README.)
+##                      Note: Does not run in a transaction.  Can do some
+##                      things and then fail, leaving the cluster in an
+##                      unusual state.
+.PHONY: init-database
+init-database:
+	[ -n "$(NEW_DB)" ] \
+           || { printf 'The NEW_DB variable must be set\n' >&2 ; \
+                exit 1 ; }
+	$(CREATE_DB)
+	( $(PSQL_SETUP) \
+	  printf '$(SET_SEARCH_PATH)\n' ; \
+	  printf "$(SET_DATESTYLE)\n" ; \
+	  ) \
+	  | psql $(PSQL_ARGS) --set=ON_ERROR_STOP=y
+##
+## Lesser used targets:
+##
+
+##   set-search_path    Set the default search_path the database named
+##                      by the TARGET_DB variable.
+.PHONY: set-search_path
+set-search_path:
+	( $(PSQL_SETUP) \
+	  printf '$(SET_SEARCH_PATH)\n' ; \
+	  ) \
+	  | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+##   set-datestyle      Set the default DATESTYLE in the database named
+##                      by the TARGET_DB variable.
+.PHONY: set-datestyle
+set-datestyle:
+	( $(PSQL_SETUP) \
+	  printf "$(SET_DATESTYLE)\n" ; \
+	  ) \
+	  | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
-- 
2.34.1