From 7b65d077582731b3f52bb0f96fbeb98d67a169c0 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <kop@karlpinc.com>
Date: Thu, 7 Sep 2023 16:28:46 -0500
Subject: [PATCH] Initial database construction
This includes creation of a build system as well as tables, triggers, etc.
that create some initial demographic database objects. Also included
are some changes to the documentation which include additional database
validation rules.
---
.gitignore | 4 +
Makefile | 41 +-
db/README | 41 ++
db/creategroups.m4 | 33 ++
db/createtypes.m4 | 30 ++
db/droptypes.m4 | 30 ++
db/include/copyright.m4 | 68 +++
db/include/copyright.sql | 16 +
db/include/do_not_edit.sql | 4 +
db/include/functions.m4 | 73 +++
db/include/grants.m4 | 121 +++++
db/include/macros.m4 | 127 ++++-
db/include/tablemacros.m4 | 238 +++++++++
db/include/types.m4 | 54 ++
db/schemas/Makefile | 472 ++++++++++++++++++
db/schemas/check_target_schema.sh | 36 ++
db/schemas/create_sql.sh | 42 ++
db/schemas/createschemas.m4 | 31 ++
db/schemas/drop_sql.sh | 44 ++
db/schemas/dropschemas.m4 | 26 +
db/schemas/lookup/tables/Makefile | 32 ++
db/schemas/lookup/tables/create/comm_ids.m4 | 38 ++
.../lookup/tables/create/departtypes.m4 | 28 ++
db/schemas/lookup/tables/create/entrytypes.m4 | 28 ++
db/schemas/lookup/tables/create/people.m4 | 37 ++
db/schemas/lookup/triggers/Makefile | 29 ++
db/schemas/lookup/triggers/create/comm_ids.m4 | 64 +++
.../lookup/triggers/create/departtypes.m4 | 64 +++
db/schemas/lookup/triggers/drop/comm_ids.m4 | 24 +
.../lookup/triggers/drop/departtypes.m4 | 24 +
db/schemas/sokwedb/functions/Makefile | 27 +
db/schemas/sokwedb/functions/create/julian.m4 | 118 +++++
db/schemas/sokwedb/functions/drop/julian.m4 | 26 +
db/schemas/sokwedb/indexes/Makefile | 28 ++
.../sokwedb/indexes/create/biography.m4 | 46 ++
.../sokwedb/indexes/create/comm_membs.m4 | 35 ++
db/schemas/sokwedb/indexes/drop/biography.m4 | 30 ++
db/schemas/sokwedb/indexes/drop/comm_membs.m4 | 29 ++
db/schemas/sokwedb/tables/Makefile | 32 ++
db/schemas/sokwedb/tables/create/biography.m4 | 101 ++++
.../sokwedb/tables/create/comm_memb_log.m4 | 43 ++
.../sokwedb/tables/create/comm_membs.m4 | 43 ++
db/schemas/sokwedb/tables/create/sightings.m4 | 0
db/schemas/sokwedb/triggers/Makefile | 28 ++
.../sokwedb/triggers/create/biography.m4 | 387 ++++++++++++++
.../triggers/create/biography.synclines | 449 +++++++++++++++++
.../sokwedb/triggers/create/comm_membs.m4 | 185 +++++++
db/schemas/sokwedb/triggers/drop/biography.m4 | 24 +
.../sokwedb/triggers/drop/comm_membs.m4 | 23 +
db/schemas/sokwedb/views/Makefile | 29 ++
doc/include/macros.m4 | 2 +-
doc/src/epilog.inc.m4 | 40 +-
doc/src/tables/biography.m4 | 47 +-
doc/src/tables/comm_memb_log.m4 | 9 +-
doc/src/tables/comm_membs.m4 | 25 +-
include/global_constants.m4 | 4 +
include/limits.m4 | 57 +++
make_files/db_doc_header.mk | 28 ++
make_files/defaults.mk | 53 ++
make_files/make_db.mk | 430 ++++++++++++++++
make_files/make_docs.mk | 18 +-
make_files/set_search_path.sh | 26 +
make_files/things.mk | 171 +++++++
63 files changed, 4460 insertions(+), 32 deletions(-)
create mode 100644 db/README
create mode 100644 db/creategroups.m4
create mode 100644 db/createtypes.m4
create mode 100644 db/droptypes.m4
create mode 100644 db/include/copyright.m4
create mode 100644 db/include/copyright.sql
create mode 100644 db/include/do_not_edit.sql
create mode 100644 db/include/functions.m4
create mode 100644 db/include/grants.m4
create mode 100644 db/include/tablemacros.m4
create mode 100644 db/include/types.m4
create mode 100644 db/schemas/Makefile
create mode 100755 db/schemas/check_target_schema.sh
create mode 100755 db/schemas/create_sql.sh
create mode 100644 db/schemas/createschemas.m4
create mode 100755 db/schemas/drop_sql.sh
create mode 100644 db/schemas/dropschemas.m4
create mode 100644 db/schemas/lookup/tables/Makefile
create mode 100644 db/schemas/lookup/tables/create/comm_ids.m4
create mode 100644 db/schemas/lookup/tables/create/departtypes.m4
create mode 100644 db/schemas/lookup/tables/create/entrytypes.m4
create mode 100644 db/schemas/lookup/tables/create/people.m4
create mode 100644 db/schemas/lookup/triggers/Makefile
create mode 100644 db/schemas/lookup/triggers/create/comm_ids.m4
create mode 100644 db/schemas/lookup/triggers/create/departtypes.m4
create mode 100644 db/schemas/lookup/triggers/drop/comm_ids.m4
create mode 100644 db/schemas/lookup/triggers/drop/departtypes.m4
create mode 100644 db/schemas/sokwedb/functions/Makefile
create mode 100644 db/schemas/sokwedb/functions/create/julian.m4
create mode 100644 db/schemas/sokwedb/functions/drop/julian.m4
create mode 100644 db/schemas/sokwedb/indexes/Makefile
create mode 100644 db/schemas/sokwedb/indexes/create/biography.m4
create mode 100644 db/schemas/sokwedb/indexes/create/comm_membs.m4
create mode 100644 db/schemas/sokwedb/indexes/drop/biography.m4
create mode 100644 db/schemas/sokwedb/indexes/drop/comm_membs.m4
create mode 100644 db/schemas/sokwedb/tables/Makefile
create mode 100644 db/schemas/sokwedb/tables/create/biography.m4
create mode 100644 db/schemas/sokwedb/tables/create/comm_memb_log.m4
create mode 100644 db/schemas/sokwedb/tables/create/comm_membs.m4
create mode 100644 db/schemas/sokwedb/tables/create/sightings.m4
create mode 100644 db/schemas/sokwedb/triggers/Makefile
create mode 100644 db/schemas/sokwedb/triggers/create/biography.m4
create mode 100644 db/schemas/sokwedb/triggers/create/biography.synclines
create mode 100644 db/schemas/sokwedb/triggers/create/comm_membs.m4
create mode 100644 db/schemas/sokwedb/triggers/drop/biography.m4
create mode 100644 db/schemas/sokwedb/triggers/drop/comm_membs.m4
create mode 100644 db/schemas/sokwedb/views/Makefile
create mode 100644 include/limits.m4
create mode 100644 make_files/db_doc_header.mk
create mode 100644 make_files/defaults.mk
create mode 100644 make_files/make_db.mk
create mode 100755 make_files/set_search_path.sh
create mode 100644 make_files/things.mk
diff --git a/.gitignore b/.gitignore
index 0710d1b..15d3fb2 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,3 +1,7 @@
+# The generated sql
+db/schemas/**/*.sql
+db/*.sql
+
# Built image files
doc/images/
diff --git a/Makefile b/Makefile
index ea06649..2d7c1d9 100644
--- a/Makefile
+++ b/Makefile
@@ -32,11 +32,50 @@ MAKE_FILES := make_files
EMACS_BK_CHR := ~
export M4_GLOBAL_INCLUDE_PATH := include
+# Those files that might change the rebuild results
+# (At this point we don't distinguish between building docs and database.)
+GENERIC_DEPENDS := Makefile \
+ $(wildcard $(MAKE_FILES)/*.mk) \
+ $(wildcard $(MAKE_FILES)/*.sh) \
+ $(wildcard include/*.m4)
+
# Make "help" be the default target
include $(MAKE_FILES)/help.mk
-include $(MAKE_FILES)/make_docs.mk
+
+## ########################################################################
+## GENERIC TARGETS
+##
+
+## install Build and install everything
+.PHONY: install
+install: install-docs install-db install-commands
+
+## clean Delete all user-generated files
+.PHONY: clean
+clean: clean-docs clean-db
+
+#
+# The database portion of the build system
+# (Split into various files for improved digestion.)
+#
+
+include $(MAKE_FILES)/db_doc_header.mk
+# Defaults for variable values
+# Have a look as you'll probably want to set some of them.
+include $(MAKE_FILES)/defaults.mk
+include $(MAKE_FILES)/make_db.mk
#
# The commands part of the build system
#
include $(MAKE_FILES)/make_commands.mk
+
+#
+# The documentation portion of the build system
+# IMPORTANT: This is put last because it uses .SECONDEXPANSION
+# It's not _that_ important, but seems like a good idea to have
+# secondary expansion occur only where it is expected.
+#
+
+include $(MAKE_FILES)/make_docs.mk
+
diff --git a/db/README b/db/README
new file mode 100644
index 0000000..6f6b799
--- /dev/null
+++ b/db/README
@@ -0,0 +1,41 @@
+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
+the $(ORDER) variable controls creation and destruction ordering.
+
+
+The make process expects:
+
+Database objects to be created with CREATE statements that do not
+qualify the created object with a schema. The schema into which each
+object is placed is determined by the placement of the file which
+creates it in the build system's directory structure.
+
+CREATE FUNCTION to be written CREATE OR REPLACE FUNCTION....
+
+DROP FUNCTION to be written DROP FUNCTION IF EXISTS....
+
+DROP VIEW to be written DROP VIEW IF EXISTS....
+
+DROP INDEX to be written DROP INDEX IF EXISTS....
+
+DROP TYPE to be written DROP TYPE IF EXISTS....
+
+CREATE SCHEMA to be written CREATE SCHEMA IF NOT EXISTS....
+
+
+Notes:
+
+UNIQUE constraints are not used in CREATE TABLE statements. Instead
+create the index yourself. This lets us see all the indexes
+in a single file, have better control over index names, etc.
+
+The source files that produce SQL DDL which creates the database
+objects is organized in the file system by type, e.g., tables,
+functions, views, etc. There may come a time, especially with
+functions, where an orthogonal ordering may be required in order
+to specify the object's place in the creation/drop sequence.
+
+Probably this would involve creating sub-directories in each schema
+that further classify functions by the order in which they must
+be created or dropped. But the details are left for the future.
diff --git a/db/creategroups.m4 b/db/creategroups.m4
new file mode 100644
index 0000000..1ca6a0c
--- /dev/null
+++ b/db/creategroups.m4
@@ -0,0 +1,33 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl
+dnl Make Postgresql roles, which act as groups controlling permissions,
+dnl expected by the SokweDB database.
+dnl
+dnl Note that there is no corresponding dropgroups.m4 file. Dropping
+dnl these roles would break the permissions attached to individual
+dnl user's roles.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')
+include(`constants.m4')
+dnl
+
+CREATE ROLE sdb_reader NOLOGIN;
+CREATE ROLE sdb_writer NOLOGIN;
diff --git a/db/createtypes.m4 b/db/createtypes.m4
new file mode 100644
index 0000000..ae1d31b
--- /dev/null
+++ b/db/createtypes.m4
@@ -0,0 +1,30 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl
+dnl Make Postgresql datatypes needed by functions
+dnl
+dnl This is here as a placeholder, we don't currently have any types.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')
+include(`constants.m4')
+include(`types.m4')
+dnl
+
+-- Make Postgresql datatypes needed by whole system.
diff --git a/db/droptypes.m4 b/db/droptypes.m4
new file mode 100644
index 0000000..de5af53
--- /dev/null
+++ b/db/droptypes.m4
@@ -0,0 +1,30 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl
+dnl Remove all Postgresql data types from the database
+dnl
+dnl This is here as a placeholder, we don't currently have any types.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl m4 includes
+include(`copyright.m4')
+include(`types.m4')
+
+--
+-- Remove all Postgresql data types from the database so
+-- we can re-create it with our creation scripts.
+--
diff --git a/db/include/copyright.m4 b/db/include/copyright.m4
new file mode 100644
index 0000000..236a9e8
--- /dev/null
+++ b/db/include/copyright.m4
@@ -0,0 +1,68 @@
+dnl Copyright (C) 2011-2013 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl Copyright (C) 2004-2011, Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+changequote([{[,]}])dnl m4 foolery so includes include only once.
+dnl Once the macro is in the text, change the quotes back
+ifdef([{[_copyright.m4]}], [{[changequote(`,')]}], [{[dnl
+changequote(`,')dnl
+dnl
+dnl Standard test for having already included the file.
+define(`_copyright.m4')dnl
+dnl
+dnl Start your output.
+-- Copyright (C) 2011-2013, 2023 The Meme Factory, Inc.
+-- http://www.karlpinc.com/
+-- Copyright (C) 2004-2011, Karl O. Pinc <kop@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 <https://www.gnu.org/licenses/>.
+--
+-- Karl O. Pinc <kop@karlpinc.com>
+
+dnl Comment to keep people from working with the generated files.
+dnl You can ignore it and go ahead and edit _this_ file. :-)
+-- DO NOT EDIT THIS FILE. It was automatically generated. Edit
+-- the *.m4 files instead. (Files _should_ be re-created by
+-- typing 'make' at the command line.)
+dnl
+dnl Macro to support inclusion of copyright into function body
+dnl as stored in the database.
+dnl
+dnl Syntax: AGPL_notice(prefix, dates, holders)
+dnl
+dnl Prefix is prepended to additional generated lines.
+dnl Dates is a string containing the copyright dates
+dnl Holders is who holds the copyright
+dnl
+define(`AGPL_notice',`Copyright (C) $2 $3
+$1 Distributed under the GNU Affero General Public License, version 3 or
+$1 later.')dnl
+dnl
+]}])dnl End of ifdef over the whole file.
diff --git a/db/include/copyright.sql b/db/include/copyright.sql
new file mode 100644
index 0000000..1d3f1ee
--- /dev/null
+++ b/db/include/copyright.sql
@@ -0,0 +1,16 @@
+--
+-- 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 <https://www.gnu.org/licenses/>.
+--
+-- Karl O. Pinc <kop@karlpinc.com>
+
diff --git a/db/include/do_not_edit.sql b/db/include/do_not_edit.sql
new file mode 100644
index 0000000..68c6b24
--- /dev/null
+++ b/db/include/do_not_edit.sql
@@ -0,0 +1,4 @@
+--
+-- DO NOT EDIT THIS FILE. It was automatically generated. Edit
+-- the *.m4 files instead. (Files _should_ be re-created by
+-- typing 'make' at the command line.)
diff --git a/db/include/functions.m4 b/db/include/functions.m4
new file mode 100644
index 0000000..9ef53d5
--- /dev/null
+++ b/db/include/functions.m4
@@ -0,0 +1,73 @@
+dnl Copyright (C) 2012, 2023 The Meme Factory, Inc. www.karlpinc.com
+dnl Copyright (C) 2006, 2008, Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as
+dnl published by the Free Software Foundation, either version 3 of the
+dnl License, or (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Macros used when writing functions.
+dnl
+dnl This should be included first so that the divert macro requires a
+dnl parameter.
+dnl
+dnl For safety, prefix all macros with "sdb_".
+dnl
+dnl
+changequote([{[,]}])dnl m4 foolery so includes include only once.
+dnl Once the macro is in the text, change the quotes back
+ifdef([{[_db_functions.m4]}], [{[changequote(`,')]}], [{[dnl (see bottom)
+changequote(`,')dnl
+dnl
+dnl Standard test for having already included the file.
+define(`_db_functions.m4')dnl
+dnl
+dnl Have m4 discard output generated in this file (see bottom)
+divert(`-1')
+
+dnl Include standard macros
+include(macros.m4)
+include(constants.m4)
+
+
+dnl Grant privliges to a function.
+dnl
+dnl This also prints the name of the function. As every function has
+dnl priviliges granted on it we get to monitor progress this way.
+dnl
+dnl Syntax: grant_func_priv(function_declaration)
+changequote([,])
+define([grant_func_priv],[
+GRANT EXECUTE ON FUNCTION $1
+ TO GROUP sdb_writer;
+RAISE INFO USING MESSAGE='Permissions set on $1';
+])
+changequote(`,')
+
+
+dnl Grant everybody privliges to a function.
+dnl
+dnl This also prints the name of the function. As every function has
+dnl priviliges granted on it we get to monitor progress this way.
+dnl
+dnl Syntax: grant_everybody_func_priv(function_declaration)
+changequote([,])
+define([grant_everybody_func_priv],[
+GRANT EXECUTE ON FUNCTION $1
+ TO GROUP sdb_writer;
+GRANT EXECUTE ON FUNCTION $1
+ TO GROUP sdb_reader;
+RAISE INFO USING MESSAGE='Permissions set on $1';
+])
+changequote(`,')
+
+divert(`0')dnl Output with m4 again
+]}])dnl End of ifdef over the whole file.
diff --git a/db/include/grants.m4 b/db/include/grants.m4
new file mode 100644
index 0000000..c3d254b
--- /dev/null
+++ b/db/include/grants.m4
@@ -0,0 +1,121 @@
+dnl Copyright (C) 2023, The Meme Factory, Inc. https://www.karlpinc.com
+dnl Copyright (C) 2004, 2005, 2008 Karl O. Pinc
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as
+dnl published by the Free Software Foundation, either version 3 of the
+dnl License, or (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl Macros to set privileges.
+dnl
+dnl Remarks:
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`types.m4')dnl
+dnl
+changequote([{[,]}])dnl m4 foolery so includes include only once.
+dnl Once the macro is in the text, change the quotes back
+ifdef([{[_grants.m4]}], [{[changequote(`,')]}], [{[dnl
+changequote(`,')dnl
+dnl
+dnl Standard test for having already included the file.
+define(`_grants.m4')dnl
+dnl
+dnl Don't output anything while defining macros.
+divert(-1)
+
+dnl Define m4 macros so we don't have to type so much.
+dnl (Or at least type stuff that's more fun.)
+dnl
+dnl Watch out for using the single quote char as it has speical
+dnl meaning to m4.
+dnl
+dnl The dates and timestamps should probably have some sort of
+dnl reasonable constraints on them to prevent operator error.
+dnl (Like entering dates from the last century.)
+dnl
+dnl Bugs:
+
+
+dnl Grant privliges to a table.
+dnl
+dnl This also prints the name of the table. As every table has
+dnl priviliges granted on it we get to monitor progress this way.
+dnl
+dnl Syntax: grant_priv(tablename)
+changequote([,])
+define([grant_priv],[
+GRANT SELECT ON $1 TO GROUP sdb_reader;
+GRANT SELECT ON $1 TO GROUP sdb_writer;
+GRANT INSERT ON $1 TO GROUP sdb_writer;
+GRANT UPDATE ON $1 TO GROUP sdb_writer;
+GRANT DELETE ON $1 TO GROUP sdb_writer;
+RAISE INFO USING MESSAGE='Permissions set on $1';
+])
+changequote(`,')
+
+
+dnl Grant privliges to a table's sequence
+dnl
+dnl This also prints the name of the sequence.
+dnl
+dnl Syntax: grant_seq_priv(tablename, idname)
+changequote([,])
+define([grant_seq_priv],[
+GRANT SELECT ON $1_$2_seq TO GROUP sdb_reader;
+GRANT SELECT ON $1_$2_seq TO GROUP sdb_writer;
+GRANT UPDATE ON $1_$2_seq TO GROUP sdb_writer;
+RAISE INFO USING MESSAGE='Permissions set on $1_$2_seq';
+])
+changequote(`,')
+
+
+dnl Grant read-only privliges to a table.
+dnl
+dnl Used for those tables updated only by trigger.
+dnl
+dnl This also prints the name of the table. As every table has
+dnl priviliges granted on it we get to monitor progress this way.
+dnl
+dnl Syntax: grant_ro_priv(tablename)
+changequote([,])
+define([grant_ro_priv],[
+GRANT SELECT ON $1 TO GROUP sdb_reader;
+GRANT SELECT ON $1 TO GROUP sdb_writer;
+RAISE INFO USING MESSAGE='Permissions set on $1';
+])
+changequote(`,')
+
+
+dnl Grant read-only privliges to a table's sequence
+dnl
+dnl Used for those tables updated only by trigger.
+dnl
+dnl This also prints the name of the sequence.
+dnl
+dnl Syntax: grant_ro_seq_priv(tablename, idname)
+changequote([,])
+define([grant_ro_seq_priv],[
+GRANT SELECT ON $1_$2_seq TO GROUP sdb_reader;
+GRANT SELECT ON $1_$2_seq TO GROUP sdb_writer;
+RAISE INFO USING MESSAGE='Permissions set on $1_$2_seq';
+])
+changequote(`,')
+
+-- Done defining macros.
+divert(`0')dnl
+dnl
+dnl
+]}])dnl End of ifdef over the whole file.
diff --git a/db/include/macros.m4 b/db/include/macros.m4
index 425c882..10f1c36 100644
--- a/db/include/macros.m4
+++ b/db/include/macros.m4
@@ -1,4 +1,4 @@
-dnl Copyright (C) 2019, 2020, 2023 The Meme Factory, Inc. www.karlpinc.com
+dnl Copyright (C) 2019, 2020, 2023 The Meme Factory, Inc. www.karlpinc.com
dnl
dnl This program is free software: you can redistribute it and/or modify
dnl it under the terms of the GNU Affero General Public License as
@@ -18,7 +18,7 @@ dnl
dnl This should be included first so that the divert macro requires a
dnl parameter.
dnl
-dnl For safety, prefix all macros with "sdb_".
+dnl For safety, prefix all macros that don't take an argument with "sdb_".
dnl
dnl
changequote([{[,]}])dnl m4 foolery so includes include only once.
@@ -41,6 +41,129 @@ define(`sdb_generated_sql',
-- typing "make" at the command line.)
')
+dnl SET clause used in all function definition statements.
+define(`sdb_function_search_path', `sokwedb, lookup, upload, pg_temp')
+define(`sdb_function_set_search_path',
+ `SET search_path = sdb_function_search_path')
+
+dnl
+dnl Macros for error reporting
+dnl
+
+dnl sql fragment to cast values which might be NULL to text.
+dnl
+dnl Syntax: textualize(expr)
+dnl
+dnl expr An expression which might be NULL.
+dnl
+dnl Remarks:
+dnl Useful when raising errors to get a uniform external NULL
+dnl representation.
+dnl
+changequote({,})
+define({textualize},{CASE WHEN $1 IS NULL THEN 'NULL' ELSE $1::TEXT END
+})
+changequote(`,')
+
+dnl
+dnl Macros related to preventing change of columns and rows
+dnl
+
+dnl Plpgsql fragment for preventing a column from changing.
+dnl
+dnl Syntax: cannot_change(table, column)
+dnl Variables required:
+dnl Restrictions: Must be used in a FOR EACH ROW update trigger function.
+changequote({,})
+define({cannot_change}, {dnl
+ IF NEW.$2 <> OLD.$2
+ OR NEW.$2 IS NULL AND OLD.$2 IS NOT NULL
+ OR NEW.$2 IS NOT NULL AND OLD.$2 IS NULL THEN
+ -- $2 has changed
+ RAISE EXCEPTION data_exception USING
+ MESSAGE = 'Error updating $1'
+ , DETAIL = '($1) = ('
+ || textualize(OLD.$2)
+ || '): $1.$2 cannot be changed'
+ , HINT = 'It may be necessary to delete the old row and'
+ ' add a new one';
+ END IF;
+})
+changequote(`,')
+
+dnl Strings used in error messages
+dnl
+define(`sdb_cannot_change_msg',
+ `This row has special meaning to SokweDB and may not be changed by ordinary users')dnl
+define(`sdb_cannot_delete_msg',
+ `This row has special meaning to SokweDB and may not be deleted by ordinary users')dnl
+define(`sdb_superuser_hint',
+ `Login as a superuser to make changes')
+
+dnl plpgsql fragment for checking that a column cannot be changed
+dnl by regular users
+dnl
+dnl Syntax: restrict_all_change(table, column)
+dnl
+dnl table The table
+dnl column The column
+dnl
+changequote({,})
+define({restrict_all_change},{dnl
+ IF (NEW.$2 <> OLD.$2
+ OR NEW.$2 IS NULL AND OLD.$2 IS NOT NULL
+ OR NEW.$2 IS NOT NULL AND OLD.$2 IS NULL)
+ AND NOT(has_table_privilege('$1', 'trigger')) THEN
+ RAISE EXCEPTION insufficient_privilege USING
+ MESSAGE= 'Error updating $1.$2'
+ , DETAIL = 'This column has special meaning to SokweDB'
+ || ' and may not be changed by ordinary users'
+ , HINT = 'sdb_superuser_hint';
+ END IF;
+})dnl
+changequote(`,')dnl
+
+dnl plpgsql fragment for checking that a row cannot be changed by regular users
+dnl
+dnl Syntax: restrict_change(table, column, value)
+dnl
+dnl table The table
+dnl column The column identifying the unchanging row
+dnl value The value identifying the row that can't be changed by
+dnl ordinary users.
+dnl
+changequote({,})
+define({restrict_change},{dnl
+ IF (NEW.$2 = '$3' OR OLD.$2 = '$3')
+ AND (NEW <> OLD)
+ AND NOT(has_table_privilege('$1', 'trigger')) THEN
+ RAISE EXCEPTION insufficient_privilege USING
+ MESSAGE= 'Error updating $1; insufficent privilege'
+ , DETAIL = '($1.$2) = ($3): sdb_cannot_change_msg'
+ , HINT = 'sdb_superuser_hint';
+ END IF;
+})dnl
+changequote(`,')dnl
+
+dnl plpgsql fragment for checking that a value cannot be deleted by regular users
+dnl
+dnl Syntax: restrict_delete(table, column, value)
+dnl
+dnl table The table
+dnl column The column
+dnl value The value that can't be deleted by ordinary users.
+dnl
+changequote({,})
+define({restrict_delete},{dnl
+ IF OLD.$2 = '$3'
+ AND NOT(has_table_privilege('$1', 'trigger')) THEN
+ RAISE EXCEPTION insufficient_privilege USING
+ MESSAGE= 'Error deleting from $1; insufficent privilege'
+ , DETAIL = '($1.$2) = ($3): sdb_cannot_delete_msg'
+ , HINT = 'sdb_superuser_hint';
+ END IF;
+})dnl
+changequote(`,')dnl
divert(`0')dnl Output with m4 again
]}])dnl End of ifdef over the whole file.
diff --git a/db/include/tablemacros.m4 b/db/include/tablemacros.m4
new file mode 100644
index 0000000..46de56b
--- /dev/null
+++ b/db/include/tablemacros.m4
@@ -0,0 +1,238 @@
+dnl Copyright (C) 2012, 2014, 2023 The Meme Factory, Inc.
+dnl http://www.karlpinc.com/
+dnl Copyright (C) 2007, 2008, 2010, 2011, Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl Macros used when creating tables.
+dnl
+dnl
+dnl Define m4 macros so we don't have to type so much.
+dnl (Or at least type stuff that's more fun.)
+dnl
+dnl Watch out for using the single quote char as it has special
+dnl meaning to m4.
+dnl
+dnl The dates and timestamps should probably have some sort of
+dnl reasonable constraints on them to prevent operator error.
+dnl (Like entering dates from the last century.)
+dnl
+dnl Bugs:
+dnl
+changequote([{[,]}])dnl m4 foolery so includes include only once.
+dnl Once the macro is in the text, change the quotes back
+ifdef([{[_tablemacros.m4]}], [{[changequote(`,')]}], [{[dnl
+changequote(`,')dnl
+dnl
+dnl Standard test for having already included the file.
+define(`_tablemacros.m4')dnl
+dnl
+
+dnl Include standard macros
+include(macros.m4)
+dnl Don't output anything while defining macros.
+divert(`-1')
+
+dnl Make a table with/without a INTEGER SERIAL id.
+dnl Syntax: serial_table(tablename, id_datatype, tablecols)
+changequote([,])
+define([serial_table],[
+ifelse($2, [INTEGER],changequote([,])dnl Remove braces surrounding expanded text
+[dnl
+dnl Integer key, use SERIAL datatype
+CREATE TABLE $1 (
+ id SERIAL PRIMARY KEY
+,]changequote(`,')dnl See above.
+, changequote([,])dnl See above.
+[dnl
+dnl Non-INTEGER key, simulate SERIAL.
+CREATE SEQUENCE IF NOT EXISTS $1_id_seq;
+CREATE TABLE $1 (
+ id $2 PRIMARY KEY NOT NULL DEFAULT nextval('$1_id_seq')
+,]changequote(`,')dnl See above.
+)dnl
+$3dnl
+);
+grant_priv($1)
+])
+changequote(`,')
+
+
+dnl A hierarchical table.
+dnl Syntax: hier_table(tablename, id_datatype, parent, parent_datatype)
+define(`hier_table',`
+serial_table($1, $2, `dnl
+ description TEXT NOT NULL,
+ $3 $4 NOT NULL CONSTRAINT "$3 must be on $3" REFERENCES $3
+')
+')
+
+dnl Create a support table.
+dnl Syntax: support_table(tablename, id_name, id_datatype, id_constraint)
+define(`support_table',`
+CREATE TABLE $1 (
+ $2 $3 PRIMARY KEY NOT NULL $4
+ ,description TEXT NOT NULL UNIQUE
+ emptytext_check(`Description')
+);
+grant_priv($1)
+')
+
+--
+-- Data types/column definitions
+--
+
+-- AnimID
+define(`animid_column', `animid_type_column(animid, AnimID, NULL)')dnl
+
+dnl A AnimID column with a different name.
+dnl Syntax: animid_type_column(colname, Colname, null)
+dnl
+dnl colname The name of the column in lower case.
+dnl Colname The name of the column in camelcaps.
+dnl null Either `NULL' or `NOT NULL'.
+dnl
+define(`animid_type_column', `
+ $1 CHAR(3) $3
+ CONSTRAINT "$2 must be a BIOGRAPHY.AnimID value"
+ REFERENCES biography(animid)')dnl
+
+--
+-- Constraints
+--
+
+-- A column cannot contain the empty string or nothing but spaces.
+--
+-- Syntax: emptytext_check(col)
+--
+-- Input:
+-- col Name of the column to check.
+--
+changequote([,])
+define([emptytext_check], [ CONSTRAINT "$1: Cannot be empty or nothing but spaces"
+ CHECK(trim(from $1) <> '')])dnl
+changequote(`,')dnl See above.
+
+-- A column cannot contain nothing but spaces.
+--
+-- Syntax: notonlyspaces_check(col)
+--
+-- Input:
+-- col Name of the column to check.
+--
+changequote([,])
+define([notonlyspaces_check], [ CONSTRAINT "$1: Cannot be nothing but spaces"
+ CHECK($1 IS NULL OR $1 = '' OR trim(from $1) <> '')])dnl
+changequote(`,')dnl See above.
+
+-- A column cannot contain any spaces.
+--
+-- Syntax: nospaces_check(col)
+--
+-- Input:
+-- col Name of the column to check.
+--
+changequote([,])
+define([nospaces_check], [ CONSTRAINT "$1: Cannot contain any spaces"
+ CHECK($1 IS NULL OR POSITION(' ' IN $1) = 0)])dnl
+changequote(`,')dnl See above.
+
+-- A column cannot contain any lower case letters.
+--
+-- Syntax: uppercase_check(col)
+--
+-- Input:
+-- col Name of the column to check.
+--
+changequote([,])
+define([uppercase_check], [
+ CONSTRAINT "$1 may not contain lower case letters"
+ CHECK ($1 = UPPER($1))])dnl
+changequote(`,')dnl See above.
+
+-- A time-ish column cannot contain seconds or fractions of a second.
+--
+-- Syntax: noseconds_check(col)
+--
+-- Input:
+-- col Name of the column to check.
+--
+changequote([,])
+define([noseconds_check], [ CONSTRAINT "$1: Cannot contain seconds or fractions thereof"
+ CHECK (EXTRACT(SECONDS FROM $1) = 0)])dnl
+changequote(`,')dnl See above
+
+-- A pair of columns must both be either NULL or non-NULL.
+--
+-- Syntax: null_iff_null(col1, col2)
+--
+-- Input:
+-- col1 Name of one of the columns.
+-- col2 Name of the other column.
+--
+changequote([,])
+define([null_iff_null], [ CONSTRAINT "$1 must be NULL if and only if $2 is NULL"
+ CHECK(($1 IS NULL AND $2 IS NULL)
+ OR ($1 IS NOT NULL AND $2 IS NOT NULL))])dnl
+changequote(`,')dnl See above
+
+-- One column must be NULL if another is.
+--
+-- Syntax: null_if_null(col1, col2)
+--
+-- Input:
+-- col1 This column must be null if col2 is null.
+-- col2 Name of the referenced column.
+--
+changequote([,])
+define([null_if_null], [ CONSTRAINT "$1 must be NULL if $2 is NULL"
+ CHECK(($2 IS NULL AND $1 IS NULL)
+ OR $2 IS NOT NULL)])dnl
+changequote(`,')dnl See above
+
+-- One column must be NULL if and only if another is not TRUE.
+--
+-- Syntax: null_iff_not_true(col1, col2)
+--
+-- Input:
+-- col1 This column must be null if col2 is NULL or TRUE.
+-- col2 Name of the referenced column.
+--
+changequote([,])
+define([null_iff_not_true], [ CONSTRAINT "$1 must be NULL if and only if $2 is not TRUE"
+ CHECK((NOT($2) AND $1 IS NULL)
+ OR ($2 AND $2 IS NOT NULL AND $1 IS NOT NULL))])dnl
+changequote(`,')dnl See above
+
+
+-- One column must be NULL if another is not TRUE.
+--
+-- Syntax: null_if_not_true(col1, col2)
+--
+-- Input:
+-- col1 This column must be null if col2 is NULL or TRUE.
+-- col2 Name of the referenced column.
+--
+changequote([,])
+define([null_if_not_true], [ CONSTRAINT "$1 must be NULL if $2 is not TRUE"
+ CHECK((NOT($2) AND $1 IS NULL)
+ OR $2)])dnl
+changequote(`,')dnl See above
+
+-- Done defining macros.
+divert(`0')dnl
+
+]}])dnl End of ifdef over the whole file.
diff --git a/db/include/types.m4 b/db/include/types.m4
new file mode 100644
index 0000000..ec59340
--- /dev/null
+++ b/db/include/types.m4
@@ -0,0 +1,54 @@
+dnl Copyright (C) 2012 The Meme Factory, Inc. http://www.meme.com/
+dnl Copyright (C) 2004, 2008, Karl O. Pinc
+dnl
+dnl This file is part of Babase.
+dnl
+dnl Babase is free software; you can redistribute it and/or modify
+dnl it under the terms of the GNU General Public License as published by
+dnl the Free Software Foundation; either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU General Public License
+dnl along with Babase. If not, see <http://www.gnu.org/licenses/>.
+dnl
+dnl Make datatypes used throughout the application.
+dnl
+dnl Karl O. Pinc <kop@meme.com>
+dnl
+dnl Note that we wouldn't really need these datatypes if we
+dnl could use %TYPE notation to declare arrays.
+dnl
+dnl These types are used somewhat inconsistently. The group
+dnl type is used everywhere. (It's so grody.) The others are
+dnl just used when necessary, i.e., when declaring arrays and
+dnl in the original column defintion. Otherwise, the column
+dnl datatype is referenced with a %TYPE.
+dnl
+changequote([{[,]}])dnl m4 foolery so includes include only once.
+dnl Once the macro is in the text, change the quotes back
+ifdef([{[_maketypes.m4]}], [{[changequote(`,')]}], [{[dnl
+changequote(`,')dnl
+dnl
+dnl Standard test for having already included the file.
+define(`_maketypes.m4')dnl
+dnl
+dnl Turn output off. This file produces no output.
+divert(`-1')
+
+dnl Groups
+define(`b_grp_type',`NUMERIC(6,4)')
+
+dnl Census
+define(`b_cendate_type',`DATE')
+define(`b_censtatus_type',`CHAR(1)')
+
+dnl Turn output back on
+divert(`0')dnl
+dnl
+dnl
+]}])dnl End of ifdef over the whole file.
diff --git a/db/schemas/Makefile b/db/schemas/Makefile
new file mode 100644
index 0000000..6ba361f
--- /dev/null
+++ b/db/schemas/Makefile
@@ -0,0 +1,472 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for schemas
+#
+# Requires $ORDER be defined.
+# This variable determines the order in which the schemas are put into
+# the database. This is important because the tables containing
+# foreign keys must be put into the database after the tables they
+# reference, etc.
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# We need bash features (associative arrays)
+SHELL := /bin/bash
+
+include ../../make_files/help.mk
+
+# This determines the order in which the schemas are put into the database.
+# This is important because the tables containing foreign keys must be put
+# into the database after the tables they reference, etc.
+ORDER := lookup \
+ sokwedb \
+ upload
+
+# The schemas that contain tables, functions, etc.
+TABLE_SCHEMAS := lookup sokwedb
+FUNCTION_SCHEMAS := sokwedb
+TRIGGER_SCHEMAS := lookup sokwedb
+VIEW_SCHEMAS := sokwedb
+INDEX_SCHEMAS := sokwedb
+
+##
+## CAUTION: This Makefile is not designed to be run directly. It is normally
+## invoked by another Makefile.
+
+##
+## Targets:
+##
+
+# These are the targets that can be made
+TARGETS := createtables.sql droptables.sql \
+ createtriggers.sql droptriggers.sql \
+ createfunctions.sql dropfunctions.sql \
+ createviews.sql dropviews.sql \
+ createindexes.sql dropindexes.sql \
+ createschemas.sql dropschemas.sql
+
+# Paths to the m4 include directories.
+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)
+
+# Rebuild when any of these change
+export DB_DEPENDS := $(patsubst %,../../%,$(DB_DEPENDS)) Makefile \
+ check_target_schema.sh
+
+# The directories that can exist in each schema directory, _in_ _the_
+# _order_ in which they should be used to create db objects.
+SCHEMA_DIRS := tables triggers indexes functions views
+
+# Derived variables
+# (These shouldn't need editing.)
+# (The use of $(ORDER) and filtering is to ensure the correct ordering.)
+TABLE_SCHEMA_PATHS := $(patsubst %,%/tables, \
+ $(filter $(TABLE_SCHEMAS),$(ORDER)))
+TABLE_CREATE_FILES := $(patsubst %,%/createthings.sql,$(TABLE_SCHEMA_PATHS))
+TABLE_DROP_FILES := $(patsubst %,%/droptables.sql,$(TABLE_SCHEMA_PATHS))
+TRIGGER_SCHEMA_PATHS := $(patsubst %,%/triggers, \
+ $(filter $(TRIGGER_SCHEMAS),$(ORDER)))
+TRIGGER_CREATE_FILES := $(patsubst %,%/createthings.sql, \
+ $(TRIGGER_SCHEMA_PATHS))
+TRIGGER_DROP_FILES := $(patsubst %,%/dropthings.sql, \
+ $(TRIGGER_SCHEMA_PATHS))
+FUNCTION_SCHEMA_PATHS := $(patsubst %,%/functions, \
+ $(filter $(FUNCTION_SCHEMAS),$(ORDER)))
+FUNCTION_CREATE_FILES := $(patsubst %,%/createthings.sql, \
+ $(FUNCTION_SCHEMA_PATHS))
+FUNCTION_DROP_FILES := $(patsubst %,%/dropthings.sql, \
+ $(FUNCTION_SCHEMA_PATHS))
+VIEW_SCHEMA_PATHS := $(patsubst %,%/views, \
+ $(filter $(VIEW_SCHEMAS),$(ORDER)))
+VIEW_CREATE_FILES := $(patsubst %,%/createthings.sql, \
+ $(VIEW_SCHEMA_PATHS))
+VIEW_DROP_FILES := $(patsubst %,%/dropthings.sql, \
+ $(VIEW_SCHEMA_PATHS))
+INDEX_SCHEMA_PATHS := $(patsubst %,%/indexes, \
+ $(filter $(INDEX_SCHEMAS),$(ORDER)))
+INDEX_CREATE_FILES := $(patsubst %,%/createthings.sql, \
+ $(INDEX_SCHEMA_PATHS))
+INDEX_DROP_FILES := $(patsubst %,%/dropthings.sql, \
+ $(INDEX_SCHEMA_PATHS))
+
+## check Test that an input file exists for an expected output file
+.PHONY: check
+check:
+ for dirpath in $$(find . -mindepth 1 -maxdepth 1 -type d) ; do \
+ dirname=$$(basename $${dirpath}) ; \
+ awk -v order="$(ORDER)" \
+ -v dirname=$${dirname} \
+ 'BEGIN {if (match(order, "(^| )" dirname "($$| )") == 0) \
+ exit 2; \
+ }' /dev/null \
+ || { printf "%s %s %s.m4 %s\n" \
+ "The Makefile's ORDER variable" \
+ 'does not know about the' \
+ $${dirname} 'directory'\
+ ; exit 2 ; }\
+ ; done
+ for schema in $(ORDER) ; do \
+ { [ -d $${schema} ] \
+ || { printf "Schema %s has no corresponding directory\n" \
+ $${schema} \
+ ; exit 1 ; } ; \
+ check() { \
+ typeset -A arr ; \
+ for v in $$1 ; do \
+ arr+=([$$v]=) ; \
+ done ; \
+ if [ -d $$2/$$3 -a ! -v arr[$$2] ] ; then \
+ printf '%s "%s" %s %s\n' \
+ 'The Makefile does not expect schema' \
+ $${schema} \
+ 'to contain' \
+ $$3 ; \
+ exit 1 ; \
+ fi ; \
+ } ; \
+ for subdir in $(SCHEMA_DIRS) ; do \
+ case $${subdir} in \
+ tables) \
+ check '$(TABLE_SCHEMAS)' $${schema} tables ;; \
+ functions) \
+ check '$(FUNCTION_SCHEMAS)' $${schema} functions ;; \
+ triggers) \
+ check '$(TRIGGER_SCHEMAS)' $${schema} triggers ;; \
+ views) \
+ check '$(VIEW_SCHEMAS)' $${schema} views ;; \
+ indexes) \
+ check '$(TABLE_SCHEMAS)' $${schema} indexes ;; \
+ esac ; \
+ if [ -d $${schema}/$${subdir} \
+ -a -f $${schema}/$${subdir}/Makefile ] ; then \
+ $(MAKE) -C $${schema}/$${subdir} check \
+ || exit 1 ; \
+ fi ; \
+ done ; \
+ } ; \
+ done
+
+##
+## Lesser used targets:
+##
+# Makefile related phony targets.
+
+## clean-db Clean up the files produced by make
+.PHONY: clean-db
+clean-db:
+ rm -rf $(TARGETS)
+ for schema in $(ORDER) ; do \
+ for subdir in $(SCHEMA_DIRS) ; do \
+ if [ -d $${schema}/$${subdir} \
+ -a -f $${schema}/$${subdir}/Makefile ] ; then \
+ $(MAKE) -C $${schema}/$${subdir} clean-db \
+ || exit 1 ; \
+ fi ; \
+ done ; \
+ done
+
+# The phony targets that alter the database
+
+## installtables Install the tables into the database
+.PHONY: installtables
+installtables: createtables.sql
+ ( $(PSQL_SETUP) \
+ cat createtables.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## droptables Remove the tables from the database
+.PHONY: droptables
+droptables: droptables.sql
+ ( $(PSQL_SETUP) \
+ cat droptables.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## installtriggers Install the triggers into the database
+.PHONY: installtriggers
+installtriggers: createtriggers.sql
+ ( $(PSQL_SETUP) \
+ cat createtriggers.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## installtriggersnolock
+## Install the triggers into the database outside of
+## a transaction.
+.PHONY: installtriggersnolock
+installtriggersnolock: createtriggers.sql
+ ( $(PSQL_SETUP) \
+ cat createtriggers.sql ; ) \
+ | psql $(PSQL_ARGS)
+
+## droptriggersnolock
+## Drop the triggers from the database outside of
+## a transaction.
+.PHONY: droptriggersnolock
+droptriggersnolock: droptriggers.sql
+ ( $(PSQL_SETUP) \
+ cat droptriggers.sql ; ) \
+ | psql $(PSQL_ARGS)
+
+## droptriggers Remove the triggers from the database
+.PHONY: droptriggers
+droptriggers: droptriggers.sql
+ ( $(PSQL_SETUP) \
+ cat droptriggers.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## installfunctions Install the functions into the database
+.PHONY: installfunctions
+installfunctions: createfunctions.sql
+ ( $(PSQL_SETUP) \
+ cat createfunctions.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## dropfunctions Remove the functions from the database
+.PHONY: dropfunctions
+dropfunctions: dropfunctions.sql
+ ( $(PSQL_SETUP) \
+ cat dropfunctions.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## installviews Install the views into the database
+.PHONY: installviews
+installviews: createviews.sql
+ ( $(PSQL_SETUP) \
+ cat createviews.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## dropviews Remove the views from the database
+.PHONY: dropviews
+dropviews: dropviews.sql
+ ( $(PSQL_SETUP) \
+ cat dropviews.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## installindexes Install the indexes into the database
+.PHONY: installindexes
+installindexes: createindexes.sql
+ ( $(PSQL_SETUP) \
+ cat createindexes.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## dropindexes Remove the indexes from the database
+.PHONY: dropindexes
+dropindexes: dropindexes.sql
+ ( $(PSQL_SETUP) \
+ cat dropindexes.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## install_schema_tables
+## Install the tables into the schema of the TARGET_SCHEMA
+## variable
+.PHONY: install_schema_tables
+install_schema_tables:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/tables install
+
+## destroy_schema_tables
+## Drop the tables in the schema given by the TARGET_SCHEMA
+## variable
+.PHONY: destroy_schema_tables
+destroy_schema_tables:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/tables destroytables
+
+## install_schema_triggers
+## Install the triggers into the schema of the TARGET_SCHEMA
+## variable
+.PHONY: install_schema_triggers
+install_schema_triggers:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/triggers install
+
+## destroy_schema_triggers
+## Drop the triggers in the schema given by the TARGET_SCHEMA
+## variable
+.PHONY: destroy_schema_triggers
+destroy_schema_triggers:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/triggers destroy
+
+## install_schema_functions
+## Install the functions into the schema of the TARGET_SCHEMA
+## variable
+.PHONY: install_schema_functions
+install_schema_functions:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/functions install
+
+## destroy_schema_functions
+## Drop the functions in the schema given by the TARGET_SCHEMA
+## variable
+.PHONY: destroy_schema_functions
+destroy_schema_functions:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/functions destroy
+
+## install_schema_views
+## Install the views into the schema of the TARGET_SCHEMA
+## variable
+.PHONY: install_schema_views
+install_schema_views:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/views install
+
+## destroy_schema_views
+## Drop the views in the schema given by the TARGET_SCHEMA
+## variable
+.PHONY: destroy_schema_views
+destroy_schema_views:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/views destroy
+
+## install_schema_indexes
+## Install the indexes into the schema of the TARGET_SCHEMA
+## variable
+.PHONY: install_schema_indexes
+install_schema_indexes:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/indexes install
+
+## destroy_schema_indexes
+## Drop the indexes in the schema given by the TARGET_SCHEMA
+## variable
+.PHONY: destroy_schema_indexes
+destroy_schema_indexes:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ $(MAKE) -C $${TARGET_SCHEMA}/indexes destroy
+
+# Actual file creation
+
+# sql_file
+# Put the SQL used to create a database object into a file.
+.PHONY: sql_file
+sql_file:
+ ./check_target_schema.sh $(TARGET_SCHEMA)
+ [ "/$(TARGET_TYPE)/" != '//' ] \
+ || { printf \
+ 'The TARGET_TYPE variable is empty; Unknown type\n' ;\
+ exit 1 ; }
+ [ -d "$(TARGET_SCHEMA)/$(TARGET_TYPE)" ] \
+ || { printf \
+ 'The TARGET_TYPE "%s" does not exist in the %s schema\n' \
+ "$(TARGET_TYPE)" \
+ "$(TARGET_SCHEMA)" ; \
+ exit 1 ; }
+ [ "/$(TARGET_THING)/" != '//' ] \
+ || { printf '%s %s\n' \
+ 'The TARGET_THING variable is empty;' \
+ 'Unknown database object' ; \
+ exit 1 ; }
+ [ -f "$(TARGET_SCHEMA)/$(TARGET_TYPE)/$(TARGET_THING).m4" ] \
+ || { printf '%s "%s" %s %s %s %s %s\n' \
+ 'The TARGET_THING' \
+ "$(TARGET_THING)" \
+ 'does not exist in the' \
+ "$(TARGET_TYPE)" \
+ 'directory of the' \
+ "$(TARGET_SCHEMA)" \
+ 'schema' ; \
+ exit 1 ; }
+ $(MAKE) -C $(TARGET_SCHEMA)/$$(dirname $(TARGET_TYPE)) \
+ $$(basename $(TARGET_TYPE))/$(TARGET_THING).sql
+
+## createschemas.sql
+## A file with the SQL to make all schemas
+## dropschemas.sql
+## A file with the SQL to drop all schemas
+dropschemas.sql createschemas.sql: %.sql: %.m4 $(DB_DEPENDS)
+ m4 $(M4_DB_INCLUDE_ARGS) $< > $@
+
+
+## createtables.sql
+## A file with the SQL to make all the tables
+createtables.sql: $(ORDER) $(DB_DEPENDS) $(TABLE_CREATE_FILES) \
+ create_sql.sh
+ ./create_sql.sh '$(TABLE_CREATE_FILES)' > createtables.sql
+
+## droptables.sql
+## A file with the SQL which drops all the tables
+droptables.sql: $(DB_DEPENDS) $(TABLE_DROP_FILES) drop_sql.sh
+ ./drop_sql.sh '$(TABLE_DROP_FILES)' > droptables.sql
+
+
+## createtriggers.sql
+## A file with the SQL to make all the triggers
+createtriggers.sql: $(ORDER) $(DB_DEPENDS) $(TRIGGER_CREATE_FILES) \
+ create_sql.sh
+ ./create_sql.sh '$(TRIGGER_CREATE_FILES)' > createtriggers.sql
+
+## droptriggers.sql
+## A file with the SQL which drops all the tables
+droptriggers.sql: $(ORDER) $(DB_DEPENDS) $(TRIGGER_DROP_FILES) drop_sql.sh
+ ./drop_sql.sh '$(TRIGGER_DROP_FILES)' > droptriggers.sql
+
+
+## createfunctions.sql
+## A file with the SQL to make all the functions
+createfunctions.sql: $(ORDER) $(DB_DEPENDS) $(FUNCTION_CREATE_FILES) \
+ create_sql.sh
+ ./create_sql.sh '$(FUNCTION_CREATE_FILES)' > createfunctions.sql
+
+## dropfunctions.sql
+## A file with the SQL which drops all the tables
+dropfunctions.sql: $(ORDER) $(DB_DEPENDS) $(FUNCTION_DROP_FILES) drop_sql.sh
+ ./drop_sql.sh '$(FUNCTION_DROP_FILES)' > dropfunctions.sql
+
+
+## createviews.sql
+## A file with the SQL to make all the views
+createviews.sql: $(ORDER) $(DB_DEPENDS) $(VIEW_CREATE_FILES) \
+ create_sql.sh
+ ./create_sql.sh '$(VIEW_CREATE_FILES)' > createviews.sql
+
+## dropviews.sql
+## A file with the SQL which drops all the tables
+dropviews.sql: $(ORDER) $(DB_DEPENDS) $(VIEW_DROP_FILES) drop_sql.sh
+ ./drop_sql.sh '$(VIEW_DROP_FILES)' > dropviews.sql
+
+
+## createindexes.sql
+## A file with the SQL to make all the indexes
+createindexes.sql: $(ORDER) $(DB_DEPENDS) $(INDEX_CREATE_FILES) \
+ create_sql.sh
+ ./create_sql.sh '$(INDEX_CREATE_FILES)' > createindexes.sql
+
+## dropindexes.sql
+## A file with the SQL which drops all the tables
+dropindexes.sql: $(ORDER) $(DB_DEPENDS) $(INDEX_DROP_FILES) drop_sql.sh
+ ./drop_sql.sh '$(INDEX_DROP_FILES)' > dropindexes.sql
+
+
+# Pick up any changes in the schemas and re-generate the sub-sql files.
+# Always let the sub-make do it so that dependencies are picked up.
+.PHONY: $(TABLE_CREATE_FILES) $(FUNCTION_CREATE_FILES) $(VIEW_CREATE_FILES) \
+ $(TRIGGER_CREATE_FILES) $(INDEX_CREATE_FILES)
+$(TABLE_CREATE_FILES) $(FUNCTION_CREATE_FILES) $(VIEW_CREATE_FILES) \
+$(TRIGGER_CREATE_FILES) $(INDEX_CREATE_FILES):
+ $(MAKE) -C $$(dirname $@) createthings.sql
+
+.PHONY: $(FUNCTION_DROP_FILES) $(VIEW_DROP_FILES) \
+ $(TRIGGER_DROP_FILES) $(INDEX_DROP_FILES)
+$(FUNCTION_DROP_FILES) $(VIEW_DROP_FILES) \
+$(TRIGGER_DROP_FILES) $(INDEX_DROP_FILES):
+ $(MAKE) -C $$(dirname $@) dropthings.sql
+
+# Table drop files have a special target
+.PHONY: $(TABLE_DROP_FILES)
+$(TABLE_DROP_FILES):
+ $(MAKE) -C $$(dirname $@) droptables.sql
diff --git a/db/schemas/check_target_schema.sh b/db/schemas/check_target_schema.sh
new file mode 100755
index 0000000..d4a7735
--- /dev/null
+++ b/db/schemas/check_target_schema.sh
@@ -0,0 +1,36 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for schemas
+#
+# Requires $ORDER be defined.
+# This variable determines the order in which the schemas are put into
+# the database. This is important because the tables containing
+# foreign keys must be put into the database after the tables they
+# reference, etc.
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+TARGET_SCHEMA="$1"
+
+[ "/${TARGET_SCHEMA}/" != '//' ] \
+ || { printf \
+ 'The TARGET_SCHEMA variable is empty; Unknown schema\n' ;\
+ exit 1 ; }
+
+[ -d "${TARGET_SCHEMA}" ] \
+ || { printf 'The TARGET_SCHEMA "%s" does not exist\n' \
+ "${TARGET_SCHEMA}" ; \
+ exit 1 ; }
diff --git a/db/schemas/create_sql.sh b/db/schemas/create_sql.sh
new file mode 100755
index 0000000..316fec9
--- /dev/null
+++ b/db/schemas/create_sql.sh
@@ -0,0 +1,42 @@
+#!/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 <https://www.gnu.org/licenses/>.
+#
+# Construct an SQL file used to create database objects.
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+export SDB_QUOTE=ArbitraryBuildSystemDollarQuotesAvoidCollisions
+
+printf '%s %s\n' '-- Copyright (C) 2023 The Meme Factory, Inc.' \
+ ' https://karlpinc.com/'
+cat ../include/copyright.sql
+cat ../include/do_not_edit.sql
+# Ensure each sql file, each of which targets a different schema, creates
+# things in the targeted schema.
+for sql_file in $1 ; do
+ schema=$(dirname $(dirname ${sql_file}))
+ printf 'DO LANGUAGE plpgsql $%s$\n' ${SDB_QUOTE}
+ printf 'DECLARE s_path text;\n'
+ printf 'BEGIN\n'
+ printf "SELECT current_setting('search_path') INTO s_path;\\n"
+ printf "PERFORM set_config('search_path', '%s,' || s_path, FALSE);\\n" \
+ ${schema}
+ printf "RAISE INFO USING MESSAGE='Running %s';\n" ${sql_file}
+ cat ${sql_file}
+ printf "PERFORM set_config('search_path', s_path, FALSE);\\n"
+ printf 'END;\n'
+ printf '$%s$;\n' ${SDB_QUOTE}
+done
diff --git a/db/schemas/createschemas.m4 b/db/schemas/createschemas.m4
new file mode 100644
index 0000000..9e5fbe2
--- /dev/null
+++ b/db/schemas/createschemas.m4
@@ -0,0 +1,31 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+dnl
+
+DROP SCHEMA IF EXISTS public RESTRICT;
+CREATE SCHEMA IF NOT EXISTS lookup;
+CREATE SCHEMA IF NOT EXISTS sokwedb;
+CREATE SCHEMA IF NOT EXISTS upload;
+
+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/drop_sql.sh b/db/schemas/drop_sql.sh
new file mode 100755
index 0000000..aebac02
--- /dev/null
+++ b/db/schemas/drop_sql.sh
@@ -0,0 +1,44 @@
+#!/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 <https://www.gnu.org/licenses/>.
+#
+# Construct an SQL file used to create database objects.
+#
+# Karl O. Pinc <kop@karlpinc.com>
+#
+# It is tempting to do a "local" SET, but that means managing
+# transactions in this level of generated SQL. Too messy.
+export SDB_QUOTE=ArbitraryBuildSystemDollarQuotesAvoidCollisions
+
+printf '%s %s\n' '-- Copyright (C) 2023 The Meme Factory, Inc.' \
+ ' https://karlpinc.com/'
+cat ../include/copyright.sql
+cat ../include/do_not_edit.sql
+export REVERSE="$(printf '%s ' $1 | tac -s ' ')"
+# Ensure each sql file, each of which targets a different schema, drops
+# things in the targeted schema.
+for sql_file in ${REVERSE} ; do
+ schema=$(dirname $(dirname ${sql_file}))
+ printf 'DO LANGUAGE plpgsql $%s$\n' ${SDB_QUOTE}
+ printf 'DECLARE s_path text;\n'
+ printf 'BEGIN\n'
+ printf "SELECT current_setting('search_path') INTO s_path;\\n"
+ printf "PERFORM set_config('search_path', '%s,' || s_path, FALSE);\\n" \
+ ${schema}
+ cat ${sql_file}
+ printf "PERFORM set_config('search_path', s_path, FALSE);\\n"
+ printf 'END;\n'
+ printf '$%s$;\n' ${SDB_QUOTE}
+done
diff --git a/db/schemas/dropschemas.m4 b/db/schemas/dropschemas.m4
new file mode 100644
index 0000000..70a71d7
--- /dev/null
+++ b/db/schemas/dropschemas.m4
@@ -0,0 +1,26 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+dnl
+
+DROP SCHEMA IF EXISTS upload CASCADE;
+DROP SCHEMA IF EXISTS sokwedb CASCADE;
+DROP SCHEMA IF EXISTS lookup CASCADE;
+
diff --git a/db/schemas/lookup/tables/Makefile b/db/schemas/lookup/tables/Makefile
new file mode 100644
index 0000000..cd6f7f1
--- /dev/null
+++ b/db/schemas/lookup/tables/Makefile
@@ -0,0 +1,32 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for tables
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# This determines the order in which the tables are put into the database.
+# This is important because the tables containing foreign keys must be put
+# into the database after the tables they reference.
+ORDER := comm_ids \
+ departtypes \
+ entrytypes \
+ people
+
+##
+## 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/lookup/tables/create/comm_ids.m4 b/db/schemas/lookup/tables/create/comm_ids.m4
new file mode 100644
index 0000000..6a40569
--- /dev/null
+++ b/db/schemas/lookup/tables/create/comm_ids.m4
@@ -0,0 +1,38 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`tablemacros.m4')dnl
+include(`grants.m4')dnl
+dnl
+
+CREATE TABLE comm_ids (
+ commid TEXT PRIMARY KEY NOT NULL
+ emptytext_check(`CommID')
+ nospaces_check(`CommID')
+ ,name TEXT NOT NULL
+ nospaces_check(`Name')
+ ,notes TEXT NOT NULL
+ notonlyspaces_check(`Notes')
+ , memb_criteria TEXT NOT NULL
+ notonlyspaces_check(`Memb_Criteria')
+);
+
+grant_priv(`COMM_IDS')
diff --git a/db/schemas/lookup/tables/create/departtypes.m4 b/db/schemas/lookup/tables/create/departtypes.m4
new file mode 100644
index 0000000..70bb959
--- /dev/null
+++ b/db/schemas/lookup/tables/create/departtypes.m4
@@ -0,0 +1,28 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`tablemacros.m4')dnl
+include(`grants.m4')dnl
+
+support_table(`departtypes', `DepartType', `CHAR(1)', `
+ emptytext_check(`DepartType')
+ uppercase_check(`DepartType')'
+)
diff --git a/db/schemas/lookup/tables/create/entrytypes.m4 b/db/schemas/lookup/tables/create/entrytypes.m4
new file mode 100644
index 0000000..233a934
--- /dev/null
+++ b/db/schemas/lookup/tables/create/entrytypes.m4
@@ -0,0 +1,28 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`tablemacros.m4')dnl
+include(`grants.m4')dnl
+
+support_table(`entrytypes', `EntryType', `CHAR(1)', `
+ emptytext_check(`EntryType')
+ uppercase_check(`EntryType')'
+)
diff --git a/db/schemas/lookup/tables/create/people.m4 b/db/schemas/lookup/tables/create/people.m4
new file mode 100644
index 0000000..6bb9dff
--- /dev/null
+++ b/db/schemas/lookup/tables/create/people.m4
@@ -0,0 +1,37 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`tablemacros.m4')dnl
+include(`grants.m4')dnl
+dnl
+
+CREATE TABLE people (
+ person TEXT PRIMARY KEY NOT NULL
+ emptytext_check(`Person')
+ nospaces_check(`Person')
+ ,name TEXT NOT NULL
+ emptytext_check(`Name')
+ ,description TEXT NOT NULL
+ notonlyspaces_check(`Description')
+ ,active BOOLEAN NOT NULL
+);
+
+grant_priv(`PEOPLE')
diff --git a/db/schemas/lookup/triggers/Makefile b/db/schemas/lookup/triggers/Makefile
new file mode 100644
index 0000000..98fa535
--- /dev/null
+++ b/db/schemas/lookup/triggers/Makefile
@@ -0,0 +1,29 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for triggers
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# 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
+
+##
+## 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/lookup/triggers/create/comm_ids.m4 b/db/schemas/lookup/triggers/create/comm_ids.m4
new file mode 100644
index 0000000..194ada3
--- /dev/null
+++ b/db/schemas/lookup/triggers/create/comm_ids.m4
@@ -0,0 +1,64 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Triggers for comm_ids table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+RAISE INFO 'comm_ids_func';
+CREATE OR REPLACE FUNCTION comm_ids_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ sdb_function_set_search_path
+ AS $$
+ BEGIN
+ -- Function for comm_ids insert and update triggers
+ --
+ -- AGPL_notice(` --', `2023',
+ `The Meme Factory, Inc., www.karlpinc.com')
+
+ IF TG_OP = 'UPDATE' THEN
+ restrict_change(`COMM_IDS', `CommID', sdb_unknown_comm)
+ END IF;
+ END;
+$$;
+
+RAISE INFO 'comm_ids_delete_func';
+CREATE OR REPLACE FUNCTION comm_ids_delete_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ sdb_function_set_search_path
+ AS $$
+ BEGIN
+ restrict_delete(`COMM_IDS', `CommID', sdb_unknown_comm)
+ END;
+$$;
+
+RAISE INFO 'comm_ids_trigger';
+CREATE TRIGGER comm_ids_trigger
+ AFTER INSERT OR UPDATE
+ ON comm_ids FOR EACH ROW
+ EXECUTE PROCEDURE comm_ids_func();
+
+RAISE INFO 'comm_ids_delete_trigger';
+CREATE TRIGGER comm_ids_delete_trigger
+ AFTER DELETE
+ ON comm_ids FOR EACH ROW
+ EXECUTE PROCEDURE comm_ids_delete_func();
diff --git a/db/schemas/lookup/triggers/create/departtypes.m4 b/db/schemas/lookup/triggers/create/departtypes.m4
new file mode 100644
index 0000000..515c456
--- /dev/null
+++ b/db/schemas/lookup/triggers/create/departtypes.m4
@@ -0,0 +1,64 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Triggers for departtypes table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+RAISE INFO 'departtypes_func';
+CREATE OR REPLACE FUNCTION departtypes_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ sdb_function_set_search_path
+ AS $$
+ BEGIN
+ -- Function for departtypes insert and update triggers
+ --
+ -- AGPL_notice(` --', `2023',
+ `The Meme Factory, Inc., www.karlpinc.com')
+
+ IF TG_OP = 'UPDATE' THEN
+ restrict_change(`DEPARTTYPES', `CommID', sdb_end_of_obs)
+ END IF;
+ END;
+$$;
+
+RAISE INFO 'departtypes_delete_func';
+CREATE OR REPLACE FUNCTION departtypes_delete_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ sdb_function_set_search_path
+ AS $$
+ BEGIN
+ restrict_delete(`DEPARTTYPES', `CommID', sdb_end_of_obs)
+ END;
+$$;
+
+RAISE INFO 'departtypes_trigger';
+CREATE TRIGGER departtypes_trigger
+ AFTER INSERT OR UPDATE
+ ON departtypes FOR EACH ROW
+ EXECUTE PROCEDURE departtypes_func();
+
+RAISE INFO 'departtypes_delete_trigger';
+CREATE TRIGGER departtypes_delete_trigger
+ AFTER DELETE
+ ON departtypes FOR EACH ROW
+ EXECUTE PROCEDURE departtypes_delete_func();
diff --git a/db/schemas/lookup/triggers/drop/comm_ids.m4 b/db/schemas/lookup/triggers/drop/comm_ids.m4
new file mode 100644
index 0000000..0aaafca
--- /dev/null
+++ b/db/schemas/lookup/triggers/drop/comm_ids.m4
@@ -0,0 +1,24 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Drop triggers for comm_ids table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+
+DROP FUNCTION IF EXISTS comm_ids_func() CASCADE;
+DROP FUNCTION IF EXISTS comm_ids_delete_func() CASCADE;
diff --git a/db/schemas/lookup/triggers/drop/departtypes.m4 b/db/schemas/lookup/triggers/drop/departtypes.m4
new file mode 100644
index 0000000..4353b65
--- /dev/null
+++ b/db/schemas/lookup/triggers/drop/departtypes.m4
@@ -0,0 +1,24 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Drop triggers for departtypes table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+
+DROP FUNCTION IF EXISTS departtypes_func() CASCADE;
+DROP FUNCTION IF EXISTS departtypes_delete_func() CASCADE;
diff --git a/db/schemas/sokwedb/functions/Makefile b/db/schemas/sokwedb/functions/Makefile
new file mode 100644
index 0000000..c6c0b9d
--- /dev/null
+++ b/db/schemas/sokwedb/functions/Makefile
@@ -0,0 +1,27 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for functions
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# This determines the order in which the functions are put into the database.
+ORDER := julian
+
+##
+## 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/functions/create/julian.m4 b/db/schemas/sokwedb/functions/create/julian.m4
new file mode 100644
index 0000000..9c080f6
--- /dev/null
+++ b/db/schemas/sokwedb/functions/create/julian.m4
@@ -0,0 +1,118 @@
+dnl Copyright (C) 2012, 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl Copyright (C) 2004, 2008, 2010 Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Julian Day functions for the server side.
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')
+include(`constants.m4')
+include(`macros.m4')
+include(`functions.m4')
+dnl
+
+--
+-- General purpose date functions.
+--
+
+dnl Make plpg/sql function that returns the julian date as an integer
+dnl given different input data types.
+dnl
+dnl Syntax: _make_julian_func(datatype, caststr)
+dnl
+dnl Input:
+dnl datatype The postgres data type the function takes as input.
+dnl caststr The postgres cast, beginning with the 2 characters
+dnl '::', that cast the input data type to a pg DATE.
+dnl
+dnl Variables required:
+dnl Restrictions:
+changequote({,})
+define({_make_julian_func},{
+
+CREATE OR REPLACE FUNCTION julian(this_date $1)
+ RETURNS INT
+ LANGUAGE plpgsql
+ IMMUTABLE
+ sdb_function_set_search_path
+ AS $$
+
+ -- Convert a date into its Julian Day.
+ --
+ -- AGPL_notice(` --', `2004, 2008', `Karl O. Pinc <kop@karlpinc.com>')
+ --
+ -- Syntax: julian(date)
+ --
+ -- Input:
+ -- date The date to convert.
+ --
+ -- Returns:
+ -- The Julian Day of the date.
+ --
+ -- Remarks:
+ -- Really ought to be built-into postgresql.
+
+ DECLARE
+ day_zero CONSTANT DATE
+ := CURRENT_DATE - CAST (to_char(CURRENT_DATE, 'J') AS INT);
+
+ BEGIN
+ RETURN this_date$2 - day_zero;
+ END;
+$$;
+
+grant_everybody_func_priv(`julian($1)')
+})
+changequote(`,')dnl
+
+-- Make the functions.
+_make_julian_func(`DATE', `')
+_make_julian_func(`TIMESTAMP', `::DATE')
+
+
+CREATE OR REPLACE FUNCTION julian_to(julian_day INT)
+ RETURNS DATE
+ LANGUAGE plpgsql
+ IMMUTABLE
+ sdb_function_set_search_path
+ AS $$
+
+ -- Convert a Julian Day to its corresponding date.
+ --
+ -- AGPL_notice(` --', `2004, 2008', `Karl O. Pinc <kop@karlpinc.com>')
+ --
+ -- Syntax: julian_to(julian_day)
+ --
+ -- Input:
+ -- julian_date The date to convert.
+ --
+ -- Returns:
+ -- The date value for the given Julian Day.
+ --
+ -- Remarks:
+ -- Really ought to be built-into postgresql.
+
+ DECLARE
+ day_zero CONSTANT DATE
+ := CURRENT_DATE - CAST (to_char(CURRENT_DATE, 'J') AS INT);
+
+ BEGIN
+ RETURN day_zero + julian_day;
+ END;
+$$;
+
+grant_everybody_func_priv(`julian_to(INT)')
diff --git a/db/schemas/sokwedb/functions/drop/julian.m4 b/db/schemas/sokwedb/functions/drop/julian.m4
new file mode 100644
index 0000000..c469236
--- /dev/null
+++ b/db/schemas/sokwedb/functions/drop/julian.m4
@@ -0,0 +1,26 @@
+dnl Copyright (C)2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Drop Julian Day functions for the server side.
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')
+
+DROP FUNCTION IF EXISTS julian(DATE);
+DROP FUNCTION IF EXISTS julian(TIMESTAMP);
+
+DROP FUNCTION IF EXISTS julian_to(INT);
diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile
new file mode 100644
index 0000000..c2b8d1a
--- /dev/null
+++ b/db/schemas/sokwedb/indexes/Makefile
@@ -0,0 +1,28 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for tables
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# This determines the order in which the indexes are put into the database.
+# This is not really important but is needed for other types of db objects.
+ORDER := 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/indexes/create/biography.m4 b/db/schemas/sokwedb/indexes/create/biography.m4
new file mode 100644
index 0000000..e196e71
--- /dev/null
+++ b/db/schemas/sokwedb/indexes/create/biography.m4
@@ -0,0 +1,46 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+CREATE UNIQUE INDEX IF NOT EXISTS biography_animidnum ON biography
+ (animid);
+CREATE UNIQUE INDEX IF NOT EXISTS biography_animname ON biography
+ (animname);
+
+CREATE INDEX IF NOT EXISTS biography_momid ON biography
+ (momid);
+CREATE INDEX IF NOT EXISTS biography_dadid ON biography
+ (dadid);
+CREATE INDEX IF NOT EXISTS biography_birthdate ON biography
+ (birthdate);
+CREATE INDEX IF NOT EXISTS biography_bdmin ON biography
+ (bdmin);
+CREATE INDEX IF NOT EXISTS biography_bdmax ON biography
+ (bdmax);
+CREATE INDEX IF NOT EXISTS biography_entrydate ON biography
+ (entrydate);
+CREATE INDEX IF NOT EXISTS biography_entrytype ON biography
+ (entrytype);
+CREATE INDEX IF NOT EXISTS biography_departdate ON biography
+ (departdate);
+CREATE INDEX IF NOT EXISTS biography_departtype ON biography
+ (departtype);
diff --git a/db/schemas/sokwedb/indexes/create/comm_membs.m4 b/db/schemas/sokwedb/indexes/create/comm_membs.m4
new file mode 100644
index 0000000..ed5212a
--- /dev/null
+++ b/db/schemas/sokwedb/indexes/create/comm_membs.m4
@@ -0,0 +1,35 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+CREATE INDEX IF NOT EXISTS comm_membs_animid ON comm_membs
+ (animid);
+CREATE INDEX IF NOT EXISTS comm_membs_startdate ON comm_membs
+ (startdate);
+CREATE INDEX IF NOT EXISTS comm_membs_enddate ON comm_membs
+ (enddate);
+CREATE INDEX IF NOT EXISTS comm_membs_commid ON comm_membs
+ (commid);
+
+-- Compound indexes
+CREATE INDEX IF NOT EXISTS comm_membs_animid_startdate_enddate ON comm_membs
+ (animid, startdate, enddate);
diff --git a/db/schemas/sokwedb/indexes/drop/biography.m4 b/db/schemas/sokwedb/indexes/drop/biography.m4
new file mode 100644
index 0000000..cfd6811
--- /dev/null
+++ b/db/schemas/sokwedb/indexes/drop/biography.m4
@@ -0,0 +1,30 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+DROP INDEX IF EXISTS biography_animidnum;
+DROP INDEX IF EXISTS biography_name;
+DROP INDEX IF EXISTS biography_momid;
+DROP INDEX IF EXISTS biography_dadid;
+DROP INDEX IF EXISTS biography_birthdate;
+DROP INDEX IF EXISTS biography_bdmin;
+DROP INDEX IF EXISTS biography_bdmax;
diff --git a/db/schemas/sokwedb/indexes/drop/comm_membs.m4 b/db/schemas/sokwedb/indexes/drop/comm_membs.m4
new file mode 100644
index 0000000..e0b839d
--- /dev/null
+++ b/db/schemas/sokwedb/indexes/drop/comm_membs.m4
@@ -0,0 +1,29 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+DROP INDEX IF EXISTS comm_membs_animid;
+DROP INDEX IF EXISTS comm_membs_startdate;
+DROP INDEX IF EXISTS comm_membs_enddate;
+DROP INDEX IF EXISTS comm_membs_commid;
+
+DROP INDEX IF EXISTS comm_membs_animid_startdate_enddate;
diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile
new file mode 100644
index 0000000..3591f66
--- /dev/null
+++ b/db/schemas/sokwedb/tables/Makefile
@@ -0,0 +1,32 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for tables
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# This determines the order in which the tables are put into the database.
+# This is important because the tables containing foreign keys must be put
+# into the database after the tables they reference.
+ORDER := biography \
+ comm_membs \
+ comm_memb_log \
+ sightings
+
+##
+## 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/tables/create/biography.m4 b/db/schemas/sokwedb/tables/create/biography.m4
new file mode 100644
index 0000000..f114ae8
--- /dev/null
+++ b/db/schemas/sokwedb/tables/create/biography.m4
@@ -0,0 +1,101 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`tablemacros.m4')dnl
+include(`grants.m4')dnl
+dnl
+
+dnl Macro to generate birthdate constraints to prevent to-early or to-late
+dnl birthdates.
+dnl
+dnl Syntax: birth_constraint(column)
+dnl Variables required:
+dnl column The (camel-case) column name
+dnl
+changequote({,})
+define({birth_constraint}, {dnl
+ CONSTRAINT "$1 must not be before sdb_min_birthdate"
+ CHECK ($1 >= 'sdb_min_birthdate')
+ CONSTRAINT "$1 must not be after today's date"
+ CHECK ($1 <= CURRENT_DATE)
+})
+changequote(`,')
+
+
+
+CREATE TABLE biography (
+ animid TEXT PRIMARY KEY NOT NULL
+ emptytext_check(`AnimID')
+ nospaces_check(`AnimID')
+ ,animidnum INT NOT NULL
+ ,animname TEXT NOT NULL
+ emptytext_check(`AnimName')
+ ,birthcomm TEXT NULL
+ CONSTRAINT "BirthComm must be a COMM_IDS.CommID value"
+ REFERENCES comm_ids (commid)
+ ,bccertainty CHAR(1) NOT NULL
+ CONSTRAINT "BCCertainty must be one of: sdb_bg_certain sdb_bg_uncertain"
+ CHECK (bccertainty = 'sdb_bg_certain'
+ OR bccertainty = 'sdb_bg_uncertain')
+ ,sex CHAR(1) NOT NULL
+ CONSTRAINT "Sex must be one of: sdb_male sdb_female sdb_unk_sex"
+ CHECK (sex = 'sdb_male'
+ OR sex = 'sdb_female'
+ OR sex = 'sdb_unk_sex')
+ ,animid_type_column(`momid', `MomID', `NULL')
+ ,animid_type_column(`dadid', `DadID', `NULL')
+ ,dadidpub TEXT NOT NULL
+ emptytext_check(`DadIDPub')
+ ,firstborn CHAR(1) NOT NULL
+ CONSTRAINT
+ "FirstBorn must be one of: sdb_firstborn sdb_not_firstborn sdb_unk_firstborn"
+ CHECK (firstborn = 'sdb_firstborn'
+ OR firstborn = 'sdb_not_firstborn'
+ OR firstborn = 'sdb_unk_firstborn')
+ ,birthdate DATE NOT NULL
+ birth_constraint(`BirthDate')
+ ,bdmin DATE NOT NULL
+ birth_constraint(`bdmin')
+ ,bdmax DATE NOT NULL
+ birth_constraint(`bdmax')
+ ,bddist CHAR(1) NOT NULL,
+ CONSTRAINT "BDDist must be one of: sdb_bddist_normal sdb_bddist_uniform"
+ CHECK(bddist = 'sdb_bddist_normal'
+ OR bddist = 'sdb_bddist_uniform')
+ ,entrydate DATE NOT NULL
+ ,entrytype CHAR(1) NOT NULL
+ CONSTRAINT "EntryType must be a ENTRYTYPES.EntryType value"
+ REFERENCES entrytypes (entrytype)
+ ,departdate DATE NOT NULL
+ ,departtype CHAR(1) NOT NULL
+ CONSTRAINT "DepartType must be a DEPARTTYPES.DepartType value"
+ REFERENCES departtypes (departtype)
+ -- Inter-column constraints
+ CONSTRAINT "EntryDate may not be before BirthDate"
+ CHECK (birthdate <= entrydate)
+ CONSTRAINT "DepartDate may not be before EntryDate"
+ CHECK (entrydate <= departdate)
+ CONSTRAINT "The time between BDMin and DepartDate may not be more than sdb_max_lifespan years"
+ CHECK (bdmin
+ <= departdate - 'sdb_max_lifespan sdb_max_lifespan_units'::interval)
+);
+
+grant_priv(`BIOGRAPHY')
diff --git a/db/schemas/sokwedb/tables/create/comm_memb_log.m4 b/db/schemas/sokwedb/tables/create/comm_memb_log.m4
new file mode 100644
index 0000000..534692d
--- /dev/null
+++ b/db/schemas/sokwedb/tables/create/comm_memb_log.m4
@@ -0,0 +1,43 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`tablemacros.m4')dnl
+include(`grants.m4')dnl
+dnl
+
+CREATE TABLE comm_memb_log (
+ commmlid INT GENERATED ALWAYS AS IDENTITY
+ ,dateofupdate DATE NOT NULL
+ CONSTRAINT "DateOfUpdate may not be before sdb_first_memb_log_date"
+ CHECK ('sdb_first_memb_log_date'::DATE <= dateofupdate)
+ CONSTRAINT "DateOfUpdate may not be after today's date"
+ CHECK (dateofupdate <= CURRENT_DATE)
+ ,animid_column(`animid', `AnimID', `NOT NULL')
+ ,description TEXT NOT NULL
+ emptytext_check(`Description')
+ ,rationale TEXT NOT NULL
+ emptytext_check(`Rationale')
+ ,madeby TEXT NOT NULL
+ CONSTRAINT "MadeBy must be a PEOPLE.Person value"
+ REFERENCES people (person)
+);
+
+grant_priv(`COMM_MEMBS')
diff --git a/db/schemas/sokwedb/tables/create/comm_membs.m4 b/db/schemas/sokwedb/tables/create/comm_membs.m4
new file mode 100644
index 0000000..54ddb60
--- /dev/null
+++ b/db/schemas/sokwedb/tables/create/comm_membs.m4
@@ -0,0 +1,43 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc., http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published
+dnl by the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+dnl
+dnl
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`tablemacros.m4')dnl
+include(`grants.m4')dnl
+dnl
+
+CREATE TABLE comm_membs (
+ commmid INT GENERATED ALWAYS AS IDENTITY
+ ,animid_column(`animid', `AnimID', `NOT NULL')
+ ,startdate DATE NOT NULL
+ ,enddate DATE NOT NULL
+ ,commid CHAR(1) NOT NULL
+ CONSTRAINT "CommID must be a COMM_IDS.CommID value"
+ REFERENCES comm_ids (commid)
+ ,startsource TEXT NOT NULL
+ notonlyspaces_check(`StartSource')
+ ,endsource TEXT NOT NULL
+ notonlyspaces_check(`EndSource')
+ -- table constraints
+ CONSTRAINT "StartDate must not be after EndDate"
+ CHECK (startdate <= enddate)
+);
+
+grant_priv(`COMM_MEMBS')
diff --git a/db/schemas/sokwedb/tables/create/sightings.m4 b/db/schemas/sokwedb/tables/create/sightings.m4
new file mode 100644
index 0000000..e69de29
diff --git a/db/schemas/sokwedb/triggers/Makefile b/db/schemas/sokwedb/triggers/Makefile
new file mode 100644
index 0000000..db25fd8
--- /dev/null
+++ b/db/schemas/sokwedb/triggers/Makefile
@@ -0,0 +1,28 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for triggers
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# 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 := 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/sokwedb/triggers/create/biography.m4
new file mode 100644
index 0000000..c08bb35
--- /dev/null
+++ b/db/schemas/sokwedb/triggers/create/biography.m4
@@ -0,0 +1,387 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify it
+dnl under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Triggers for the biography table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+dnl Plpgsql fragment that checks that the parent is the right sex.
+dnl
+dnl Syntax: check_sex(parent_col, description, sex_code)
+dnl
+dnl Variables required:
+dnl parent_col The column to check, either 'momid' or 'dadid'
+dnl description Description of the relationship, either 'mother' or 'father'
+dnl sex_code The code for the parent's sex, either sdb_female or sdb_male
+dnl
+dnl Restrictions: Must be used in a FOR EACH ROW insert or update dnl trigger function.
+changequote({,})
+define({check_sex}, {dnl
+ IF NEW.$1 IS NOT NULL
+ AND ((TG_OP = 'UPDATE'
+ AND NEW.$1 IS DISTINCT FROM OLD.$1)
+ OR TG_OP = 'INSERT') THEN
+ DECLARE
+ parent_sex biography.sex%TYPE;
+ BEGIN
+ SELECT biography.sex
+ INTO parent_sex
+ FROM biography
+ WHERE biography.animid = NEW.$1
+ AND biography.sex <> '$3';
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'The $2''s sex must be $3'
+ , DETAIL = 'The $2 of (AnimID) = ('
+ || NEW.animid
+ || ') has an (AnimID) = ('
+ || NEW.$1
+ || ') and (Sex) = ('
+ || parent_sex
+ || ')';
+ END IF;
+ END;
+ END IF;
+})
+changequote(`,')
+
+dnl Plpgsql fragment that checks for invalid BIOGRAPHY.FirstBorn
+dnl values.
+dnl
+dnl Syntax: check_firstborn(new_or_old, msg_prefix)
+dnl
+dnl Variables required:
+dnl new_or_old Either NEW or OLD, depending on the momid to be checked.
+dnl msg_prefix The beginning of the error message.
+dnl
+dnl Restrictions: Must be used in a FOR EACH ROW insert or update
+dnl trigger function.
+changequote({,})
+define({check_firstborn}, {dnl
+ IF $1.momid IS NOT NULL THEN
+ DECLARE
+ -- Info on the first born
+ fb_animid biography.animid%TYPE;
+ fb_animname biography.animname%TYPE;
+ fb_birthdate biography.birthdate%TYPE;
+ -- Info on a sibling who breaks the rules
+ bad_animid biography.animid%TYPE;
+ bad_animname biography.animname%TYPE;
+ bad_birthdate biography.birthdate%TYPE;
+ bad_firstborn biography.firstborn%TYPE;
+ BEGIN
+ -- Find the mother's firstborn
+ SELECT biography.animid, biography.animname, biography.birthdate
+ INTO fb_animid, fb_animname, fb_birthdate
+ FROM biography
+ WHERE biography.momid = $1.momid
+ ORDER BY biography.birthdate;
+ -- Find a random sibling with a bad FirstBorn
+ -- This could be the row that's being inserted or updated;
+ -- we don't bother with a separate test/error message.
+ -- (Collect data to produce a wordy error message instead of using
+ -- a sub-select.)
+ SELECT biography.animid, biography.animname, biography.birthdate
+ , biography.firstborn
+ INTO bad_animid, bad_animname, bad_birthdate
+ , bad_firstborn
+ FROM biography
+ WHERE biography.momid = $1.momid
+ AND biography.birthdate > fb_birthdate
+ AND biography.firstborn <> 'sdb_not_firstborn'
+ ORDER BY biography.birthdate;
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'All but the youngest sibling (or siblings'
+ || ' in the case of twins) must have a'
+ || ' (BIOGRAPHY.FirstBorn) = (sdb_not_firstborn)'
+ , DETAIL = '$2 (AnimID) = ('
+ || $1.animid
+ || '), (AnimName) = ('
+ || $1.animname
+ || '), (MomID) = ('
+ || $1.momid
+ || '), (BirthDate) = ('
+ || $1.birthdate
+ || ') results in a sibling [(AnimID) = ('
+ || bad_animid
+ || '), (AnimName) = ('
+ || bad_animname
+ || '), (MomID) = ('
+ || $1.momid
+ || '), (BirthDate) = ('
+ || bad_birthdate
+ || '), (FirstBorn) = ('
+ || bad_firstborn
+ || ')] with an invalid FirstBorn value, being born'
+ || ' after [(AnimID) = ('
+ || fb_animid
+ || '), (AnimName) = ('
+ || fb_animname
+ || '), (MomID) = ('
+ || $1.momid
+ || '), (BirthDate) = ('
+ || fb_birthdate
+ || ')]'
+ , HINT = 'If the youngest sibling is no longer yougest,'
+ || ' set the youngest''s'
+ || ' (FirstBorn) = (sdb_not_firstborn) before'
+ || ' making this change';
+ END IF;
+ END;
+ END IF;
+})
+changequote(`,')
+
+
+RAISE INFO 'biography_func';
+CREATE OR REPLACE FUNCTION biography_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ sdb_function_set_search_path
+ AS $$
+ BEGIN
+ -- Function for biography insert and update triggers
+ --
+ -- AGPL_notice(` --', `2023',
+ `The Meme Factory, Inc., www.karlpinc.com')
+
+ IF TG_OP = 'UPDATE' THEN
+ -- Allowing the AnimID to change makes life too complicated.
+ cannot_change(`BIOGRAPHY', `AnimID')
+ END IF;
+
+ -- A mother must be female
+ check_sex(`momid', `mother', sdb_female)
+
+ -- A father must be male
+ check_sex(`dadid', `father', sdb_male)
+
+ --
+ -- A individual may not be too young to have offspring
+ --
+ IF TG_OP = 'INSERT'
+ OR (TG_OP = 'UPDATE'
+ AND (OLD.momid IS DISTINCT FROM NEW.momid
+ OR OLD.dadid IS DISTINCT FROM NEW.dadid
+ OR OLD.bdmin <> NEW.bdmin
+ OR OLD.bdmax <> NEW.bdmax)) THEN
+ DECLARE
+ other_animid biography.animid%TYPE;
+ other_animname biography.animname%TYPE;
+ other_bdmin biography.bdmin%TYPE;
+ other_bdmax biography.bdmax%TYPE;
+ other_sex biography.sex%TYPE;
+ BEGIN
+ IF TG_OP = 'UPDATE' THEN
+ -- See if one of this individual's children was born too late
+ -- (Inserts can't have existing children)
+ SELECT biography.animid, biography.animname, biography.bdmax
+ INTO other_animid, other_animname, other_bdmax
+ FROM biography
+ WHERE (NEW.sex = 'sdb_female'
+ AND biography.momid = NEW.animid
+ AND biography.bdmax
+ > NEW.bdmin
+ - 'sdb_min_f_birth sdb_min_f_birth_units'::interval)
+ OR (NEW.sex = 'sdb_male'
+ AND biography.dadid = NEW.animid
+ AND biography.bdmax
+ > NEW.bdbin
+ - 'sdb_min_m_birth sdb_min_m_birth_units'
+ ::interval);
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'An individual cannot have offspring when too young'
+ , DETAIL = '(AnimID) = ('
+ || NEW.animid
+ || '), (AnimName) = ('
+ || NEW.animname
+ || '), (Sex) = ('
+ || NEW.sex
+ || '), (BDMin) = ('
+ || NEW.bdmin
+ || ') has the offspring (AnimId) = ('
+ || other_animid
+ || '), (AnimName) = ('
+ || other_animname
+ || '), (BDMax) = ('
+ || other_bdmax
+ || ') too soon'
+ , HINT = 'The minimum age for females to be a parent is'
+ || ' sdb_min_f_birth sdb_min_f_birth_units. The'
+ || ' minimum age for males to be a parent is'
+ || ' sdb_min_m_birth sdb_min_m_birth_units.';
+ END IF;
+ END IF;
+ -- See if one of this individual's parents were born too soon.
+ SELECT biography.animid, biography.animname, biography.bdmin
+ , biography.sex
+ INTO other_animid, other_animname, other_bdmin
+ , other_sex
+ FROM biography
+ WHERE (biography.sex = 'sdb_female'
+ AND biography.animid = NEW.momid
+ AND NEW.bdmax
+ > biography.bdmin
+ - 'sdb_min_f_birth sdb_min_f_birth_units'::interval)
+ OR (biography.sex = 'sdb_male'
+ AND biography.animid = NEW.dadid
+ AND NEW.bdmax
+ > biography.bdmin
+ - 'sdb_min_m_birth sdb_min_m_birth_units'::interval);
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'An individual cannot have offspring when too young'
+ , DETAIL = '(AnimID) = ('
+ || NEW.animid
+ || '), (AnimName) = ('
+ || NEW.animname
+ || '), (BDMax) = ('
+ || NEW.bdmax
+ || ') has a parent (AnimId) = ('
+ || other_animid
+ || '), (AnimName) = ('
+ || other_animname
+ || '), (Sex) = ('
+ || other_sex
+ || '), (BDMin) = ('
+ || other_bdmin
+ || ') who is too young'
+ , HINT = 'The minimum age for females to be a parent is'
+ || ' sdb_min_f_birth sdb_min_f_birth_units. The'
+ || ' minimum age for males to be a parent is'
+ || ' sdb_min_m_birth sdb_min_m_birth_units.';
+ END IF;
+ END;
+ END IF;
+
+ --
+ -- All but the youngest sibling (or siblings in the case of twins)
+ -- must have FirstBorn of sdb_not_firstborn.
+ --
+ IF TG_OP = 'UPDATE' THEN
+ IF NEW.momid IS DISTINCT FROM OLD.momid THEN
+ -- Check offspring of both new and old mothers
+ check_firstborn(`NEW',
+ `Updating the BIOGRAPHY row where the new values are')
+ check_firstborn(`OLD',
+ `Updating the BIOGRAPHY row where the old values are')
+ ELSE
+ IF NEW.birthdate <> OLD.birthdate THEN
+ -- Check siblings
+ check_firstborn(`NEW',
+ `Updating the BIOGRAPHY row where the new values are')
+ END IF;
+ END IF;
+ ELSE
+ -- TG_OP = 'INSERT', check siblings
+ check_firstborn(`NEW', `Inserting a BIOGRAPHY row where')
+ END IF;
+
+ -- An individual may not be placed in a community before the
+ -- individual is under study.
+ DECLARE
+ commmid comm_membs.commmid%TYPE;
+ startdate comm_membs.startdate%TYPE;
+ BEGIN
+ SELECT comm_membs.startdate, comm_membs.commmid
+ INTO startdate, commmid
+ WHERE comm_membs.animid = NEW.animid
+ AND comm_membs.startdate < NEW.entrydate;
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'An individual may not be placed in a community before'
+ || ' the individual is under study'
+ , DETAIL = '(AnimID) = ('
+ || NEW.animid
+ || '), (AnimName) = ('
+ || NEW.animname
+ || '), (StartDate) = ('
+ || NEW.startdate
+ || ' has a related COMM_MEMBS row with (CommMID) = ('
+ || commmid
+ || '), and (StartDate) = ('
+ || startdate
+ || ')';
+ END IF;
+ END;
+
+ -- An individual may not be placed in a community after the
+ -- individual left the study.
+ DECLARE
+ commmid comm_membs.commmid%TYPE;
+ enddate comm_membs.enddate%TYPE;
+ BEGIN
+ SELECT comm_membs.enddate, comm_membs.commmid
+ INTO enddate, commmid
+ WHERE comm_membs.animid = NEW.animid
+ AND NEW.departdate < comm_membs.enddate;
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'An individual may not be placed in a community after'
+ || ' the individual has left the study'
+ , DETAIL = '(AnimID) = ('
+ || NEW.animid
+ || '), (AnimName) = ('
+ || NEW.animname
+ || '), (DepartDate) = ('
+ || NEW.departdate
+ || ' has a related COMM_MEMBS row with (CommMID) = ('
+ || commmid
+ || ') and (EndDate) = ('
+ || enddate
+ || ')';
+ END IF;
+ END;
+ END;
+$$;
+
+
+RAISE INFO 'biography_delete_func';
+CREATE OR REPLACE FUNCTION biography_delete_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ sdb_function_set_search_path
+ AS $$
+ BEGIN
+ -- Function for biography insert and update triggers
+ --
+ -- AGPL_notice(` --', `2023',
+ `The Meme Factory, Inc., www.karlpinc.com')
+
+ -- All but the youngest sibling (or siblings in the case of twins)
+ -- must have FirstBorn of sdb_not_firstborn.
+ check_firstborn(`OLD', `Deleting the BIOGRAPHY row')
+ END;
+$$;
+
+
+RAISE INFO 'biography_trigger';
+CREATE TRIGGER biography_trigger
+ AFTER INSERT OR UPDATE
+ ON biography FOR EACH ROW
+ EXECUTE PROCEDURE biography_func();
+
+RAISE INFO 'biography_delete_trigger';
+CREATE TRIGGER biography_delete_trigger
+ AFTER DELETE
+ ON biography FOR EACH ROW
+ EXECUTE PROCEDURE biography_delete_func();
diff --git a/db/schemas/sokwedb/triggers/create/biography.synclines b/db/schemas/sokwedb/triggers/create/biography.synclines
new file mode 100644
index 0000000..d8f3ece
--- /dev/null
+++ b/db/schemas/sokwedb/triggers/create/biography.synclines
@@ -0,0 +1,449 @@
+#line 19 "biography.m4"
+
+#line 23 "../../../../include/copyright.m4"
+-- Copyright (C) 2011-2013, 2023 The Meme Factory, Inc.
+#line 23
+-- http://www.karlpinc.com/
+#line 23
+-- Copyright (C) 2004-2011, Karl O. Pinc <kop@karlpinc.com>
+#line 23
+--
+#line 23
+-- This program is free software: you can redistribute it and/or modify
+#line 23
+-- it under the terms of the GNU Affero General Public License as
+#line 23
+-- published by the Free Software Foundation, either version 3 of the
+#line 23
+-- License, or (at your option) any later version.
+#line 23
+--
+#line 23
+-- This program is distributed in the hope that it will be useful,
+#line 23
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+#line 23
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+#line 23
+-- GNU Affero General Public License for more details.
+#line 23
+--
+#line 23
+-- You should have received a copy of the GNU Affero General Public License
+#line 23
+-- along with this program. If not, see <https://www.gnu.org/licenses/>.
+#line 23
+--
+#line 23
+-- Karl O. Pinc <kop@karlpinc.com>
+#line 23
+
+#line 23
+-- DO NOT EDIT THIS FILE. It was automatically generated. Edit
+#line 23
+-- the *.m4 files instead. (Files _should_ be re-created by
+#line 23
+-- typing 'make' at the command line.)
+#line 51 "../../../../../include/limits.m4"
+
+#line 21 "../../../../include/constants.m4"
+
+#line 21
+
+#line 21
+
+#line 26 "../../../../include/macros.m4"
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 26
+
+#line 24 "biography.m4"
+
+
+#line 39
+
+#line 109
+
+
+
+
+RAISE INFO 'biography_func';
+CREATE OR REPLACE FUNCTION biography_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ SET search_path = sokwedb, lookup, upload, pg_temp
+ AS $$
+ BEGIN
+ -- Function for biography insert and update triggers
+ --
+ -- Copyright (C) 2023 The Meme Factory, Inc., www.karlpinc.com
+#line 122
+ -- Distributed under the GNU Affero General Public License, version 3 or
+#line 122
+ -- later.
+#line 124
+
+ IF TG_OP = 'UPDATE' THEN
+ -- Allowing the AnimID to change makes life too complicated.
+ IF NEW.AnimID <> OLD.AnimID
+#line 127
+ OR NEW.AnimID IS NULL AND OLD.AnimID IS NOT NULL
+#line 127
+ OR NEW.AnimID IS NOT NULL AND OLD.AnimID IS NULL THEN
+#line 127
+ -- AnimID has changed
+#line 127
+ RAISE EXCEPTION data_exception USING
+#line 127
+ MESSAGE = 'Error updating BIOGRAPHY'
+#line 127
+ , DETAIL = '(BIOGRAPHY) = ('
+#line 127
+ || CASE WHEN OLD.AnimID IS NULL THEN 'NULL' ELSE OLD.AnimID::TEXT END
+#line 127
+
+#line 127
+ || '): BIOGRAPHY.AnimID cannot be changed';
+#line 127
+ END IF;
+#line 127
+
+ END IF;
+
+ -- All but the youngest sibling (or siblings in the case of twins)
+ -- must have FirstBorn of N.
+ IF TG_OP = 'UPDATE' THEN
+ IF NEW.momid IS DISTINCT FROM OLD.momid THEN
+ DECLARE
+#line 134
+ -- Info on the first born
+#line 134
+ fb_animid biography.animid%TYPE;
+#line 134
+ fb_animname biography.animname%TYPE;
+#line 134
+ fb_birthdate biography.birthdate%TYPE;
+#line 134
+ -- Info on a sibling who breaks the rules
+#line 134
+ bad_animid biography.animid%TYPE;
+#line 134
+ bad_animname biography.animname%TYPE;
+#line 134
+ bad_birthdate biography.birthdate%TYPE;
+#line 134
+ bad_firstborn biography.firstborn%TYPE;
+#line 134
+ BEGIN
+#line 134
+ -- Find the mother's firstborn
+#line 134
+ SELECT biography.animid, biography.animname, biography.birthdate
+#line 134
+ INTO fb_animid, fb_animname, fb_birthdate
+#line 134
+ FROM biography
+#line 134
+ WHERE biography.momid = NEW.momid
+#line 134
+ ORDER BY biography.birthdate
+#line 134
+ LIMIT 1;
+#line 134
+ -- Find a random sibling with a bad FirstBorn
+#line 134
+ -- (Collect data to produce a wordy error message instead of using
+#line 134
+ -- a sub-select.)
+#line 134
+ SELECT biography.animid, biography.animname, biography.birthdate,
+#line 134
+ biography.firstborn
+#line 134
+ INTO bad_animid, bad_animname, bad_birthdate,
+#line 134
+ bad_firstborn
+#line 134
+ FROM biography
+#line 134
+ WHERE biography.momid = NEW.momid
+#line 134
+ AND biography.birthdate > fb_birthdate
+#line 134
+ AND biography.firstborn <> 'N'
+#line 134
+ ORDER BY biography.birthdate
+#line 134
+ LIMIT 1;
+#line 134
+ IF FOUND THEN
+#line 134
+ RAISE EXCEPTION integrity_constraint_violation USING
+#line 134
+ MESSAGE = 'All but the youngest sibling (or siblings'
+#line 134
+ || ' in the case of twins) must have a'
+#line 134
+ || ' (BIOGRAPHY.FirstBorn) = (N)'
+#line 134
+ , DETAIL = 'Updating the BIOGRAPHY row where the new values are (AnimID) = ('
+#line 134
+ || NEW.animid
+#line 134
+ || '), (AnimName) = ('
+#line 134
+ || NEW.animname
+#line 134
+ || '), (MomID) = ('
+#line 134
+ || NEW.momid
+#line 134
+ || '), (BirthDate) = ('
+#line 134
+ || NEW.birthdate
+#line 134
+ || ') results in a sibling [(AnimID) = ('
+#line 134
+ || bad_animid
+#line 134
+ || '), (AnimName) = ('
+#line 134
+ || bad_animname
+#line 134
+ || '), (MomID) = ('
+#line 134
+ || NEW.momid
+#line 134
+ || '), (BirthDate) = ('
+#line 134
+ || bad_birthdate
+#line 134
+ || '), (FirstBorn) = ('
+#line 134
+ || bad_firstborn
+#line 134
+ || ')] with an invalid FirstBorn value, being born'
+#line 134
+ || ' after [(AnimID) = ('
+#line 134
+ || fb_animid
+#line 134
+ || '), (AnimName) = ('
+#line 134
+ || fb_animname
+#line 134
+ || '), (MomID) = ('
+#line 134
+ || NEW.momid
+#line 134
+ || '), (BirthDate) = ('
+#line 134
+ || fb_birthdate
+#line 134
+ || ')]'
+#line 134
+ , HINT = 'If the youngest sibling is no longer yougest, set it''s'
+#line 134
+ || ' (FirstBorn) = (N).';
+#line 134
+ END IF;
+#line 134
+ END;
+
+ END IF;
+ IF NEW.birthdate <> OLD.birthdate THEN
+ END IF;
+ END IF;
+ IF TG_OP = 'INSERT'
+ AND NEW.momid IS NOT NULL THEN
+ DECLARE
+#line 142
+ -- Info on the first born
+#line 142
+ fb_animid biography.animid%TYPE;
+#line 142
+ fb_animname biography.animname%TYPE;
+#line 142
+ fb_birthdate biography.birthdate%TYPE;
+#line 142
+ -- Info on a sibling who breaks the rules
+#line 142
+ bad_animid biography.animid%TYPE;
+#line 142
+ bad_animname biography.animname%TYPE;
+#line 142
+ bad_birthdate biography.birthdate%TYPE;
+#line 142
+ bad_firstborn biography.firstborn%TYPE;
+#line 142
+ BEGIN
+#line 142
+ -- Find the mother's firstborn
+#line 142
+ SELECT biography.animid, biography.animname, biography.birthdate
+#line 142
+ INTO fb_animid, fb_animname, fb_birthdate
+#line 142
+ FROM biography
+#line 142
+ WHERE biography.momid = NEW.momid
+#line 142
+ ORDER BY biography.birthdate
+#line 142
+ LIMIT 1;
+#line 142
+ -- Find a random sibling with a bad FirstBorn
+#line 142
+ -- (Collect data to produce a wordy error message instead of using
+#line 142
+ -- a sub-select.)
+#line 142
+ SELECT biography.animid, biography.animname, biography.birthdate,
+#line 142
+ biography.firstborn
+#line 142
+ INTO bad_animid, bad_animname, bad_birthdate,
+#line 142
+ bad_firstborn
+#line 142
+ FROM biography
+#line 142
+ WHERE biography.momid = NEW.momid
+#line 142
+ AND biography.birthdate > fb_birthdate
+#line 142
+ AND biography.firstborn <> 'N'
+#line 142
+ ORDER BY biography.birthdate
+#line 142
+ LIMIT 1;
+#line 142
+ IF FOUND THEN
+#line 142
+ RAISE EXCEPTION integrity_constraint_violation USING
+#line 142
+ MESSAGE = 'All but the youngest sibling (or siblings'
+#line 142
+ || ' in the case of twins) must have a'
+#line 142
+ || ' (BIOGRAPHY.FirstBorn) = (N)'
+#line 142
+ , DETAIL = 'Inserting a BIOGRAPHY row where (AnimID) = ('
+#line 142
+ || NEW.animid
+#line 142
+ || '), (AnimName) = ('
+#line 142
+ || NEW.animname
+#line 142
+ || '), (MomID) = ('
+#line 142
+ || NEW.momid
+#line 142
+ || '), (BirthDate) = ('
+#line 142
+ || NEW.birthdate
+#line 142
+ || ') results in a sibling [(AnimID) = ('
+#line 142
+ || bad_animid
+#line 142
+ || '), (AnimName) = ('
+#line 142
+ || bad_animname
+#line 142
+ || '), (MomID) = ('
+#line 142
+ || NEW.momid
+#line 142
+ || '), (BirthDate) = ('
+#line 142
+ || bad_birthdate
+#line 142
+ || '), (FirstBorn) = ('
+#line 142
+ || bad_firstborn
+#line 142
+ || ')] with an invalid FirstBorn value, being born'
+#line 142
+ || ' after [(AnimID) = ('
+#line 142
+ || fb_animid
+#line 142
+ || '), (AnimName) = ('
+#line 142
+ || fb_animname
+#line 142
+ || '), (MomID) = ('
+#line 142
+ || NEW.momid
+#line 142
+ || '), (BirthDate) = ('
+#line 142
+ || fb_birthdate
+#line 142
+ || ')]'
+#line 142
+ , HINT = 'If the youngest sibling is no longer yougest, set it''s'
+#line 142
+ || ' (FirstBorn) = (N).';
+#line 142
+ END IF;
+#line 142
+ END;
+#line 142
+
+ END IF;
+ END;
+$$;
+
+RAISE INFO 'biography_trigger';
+CREATE TRIGGER biography_trigger
+ AFTER INSERT OR UPDATE
+ ON biography FOR EACH ROW
+ EXECUTE PROCEDURE biography_func();
diff --git a/db/schemas/sokwedb/triggers/create/comm_membs.m4 b/db/schemas/sokwedb/triggers/create/comm_membs.m4
new file mode 100644
index 0000000..034a818
--- /dev/null
+++ b/db/schemas/sokwedb/triggers/create/comm_membs.m4
@@ -0,0 +1,185 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify it
+dnl under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Triggers for the comm_membs table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+include(`constants.m4')dnl
+include(`macros.m4')dnl
+
+
+RAISE INFO 'comm_membs_func';
+CREATE OR REPLACE FUNCTION comm_membs_func ()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ sdb_function_set_search_path
+ AS $$
+ BEGIN
+ -- Function for comm_membs insert and update triggers
+ --
+ -- AGPL_notice(` --', `2023',
+ `The Meme Factory, Inc., www.karlpinc.com')
+
+ -- An individual may not be recorded in more than one community on
+ -- any given day.
+ DECLARE
+ other_row comm_membs%ROWTYPE;
+ BEGIN
+ SELECT *
+ INTO other_row
+ FROM comm_membs
+ WHERE comm_membs.animid = NEW.animid
+ AND comm_membs.startdate <= NEW.enddate
+ AND comm_membs.enddate >= NEW.startdate
+ AND comm_membs.commmid <> NEW.comm_membs.commmid;
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'An individual may not be in more than one community'
+ || ' (or even twice in the same community) on'
+ || ' any given day'
+ , DETAIL = 'With COMM_MEMBS row (CommMID) = ('
+ || NEW.commmid
+ || '), (AnimID) = ('
+ || NEW.animid
+ || ') is being given a (CommID) = ('
+ || NEW.commid
+ || ') with a (StartDate) = ('
+ || NEW.StartDate
+ || ') and an (EndDate) = ('
+ || NEW.EndDate
+ || '), but this AnimID already has the row (CommMID) = ('
+ || other_row.commmid
+ || '), (StartDate) = ('
+ || other_row.startdate
+ || '), (EndDate) = ('
+ || other_row.enddate
+ || ')';
+ END IF;
+ END;
+
+ -- Two COMM_MEMBS rows may not be used to place an individual in the
+ -- same community on successive days.
+ DECLARE
+ other_commmid comm_membs.commmid%TYPE;
+ other_startdate comm_membs.startdate%TYPE;
+ other_enddate comm_membs.enddate%TYPE;
+ BEGIN
+ SELECT comm_membs.commmid, comm_membs.startdate, comm_membs.enddate
+ INTO other_commmid, other_startdate, other_enddate
+ FROM comm_membs
+ WHERE comm_membs.commmid <> NEW.commmid
+ AND comm_membs.animid = NEW.animid
+ AND (comm_membs.enddate = NEW.startdate - 1
+ OR comm_membs.startdate = NEW.enddate + 1);
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'Two COMM_MEMBS rows may not be used to place a'
+ || ' single individual in the same community on'
+ || ' successive days'
+ , DETAIL = 'The new values in the row (CommMID) = ('
+ || NEW.commmid
+ || '), (AnimID) = ('
+ || NEW.animid
+ || '), (CommID) = ('
+ || NEW.commid
+ || '), (StartDate) = ('
+ || NEW.startdate
+ || '), (EndDate) = ('
+ || NEW.enddate
+ || ') are contiguous with the row where (CommMID) = ('
+ || other_commmid
+ || '), (AnimID) = ('
+ || NEW.animid
+ || '), (StartDate) = ('
+ || other_startdate
+ || '), (EndDate) = ('
+ || other_enddate
+ || ')'
+ , HINT = 'Expand the time interval covered by an existing row';
+ END IF;
+ END;
+
+ -- An individual may not be placed in a community before the
+ -- individual is under study.
+ DECLARE
+ entrydate biography.entrydate%TYPE;
+ BEGIN
+ SELECT biography.entrydate
+ INTO entrydate
+ FROM biography
+ WHERE biography.animid = NEW.animid
+ AND NEW.startdate < biography.entrydate;
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'An individual may not be placed in a community'
+ || ' before the individual is under study'
+ , DETAIL = 'The new values in the row (CommMID) = ('
+ || NEW.commmid
+ || '), (AnimID) = ('
+ || NEW.animid
+ || '), (CommID) = ('
+ || NEW.commid
+ || '), (StartDate) = ('
+ || NEW.startdate
+ || ') place the individual in the community before'
+ || ' the individual came under study;'
+ || ' (BIOGRAPHY.EntryDate) = ('
+ || entrydate
+ || ')';
+ END IF;
+ END;
+
+ -- An individual may not be placed in a community after the
+ -- individual has left the study.
+ DECLARE
+ departdate biography.departdate%TYPE;
+ BEGIN
+ SELECT biography.departdate
+ INTO departdate
+ FROM biography
+ WHERE biography.animid = NEW.animid
+ AND biography.departdate < NEW.enddate;
+ IF FOUND THEN
+ RAISE EXCEPTION integrity_constraint_violation USING
+ MESSAGE = 'An individual may not be placed in a community'
+ || ' after the individual has left the study'
+ , DETAIL = 'The new values in the row (CommMID) = ('
+ || NEW.commmid
+ || '), (AnimID) = ('
+ || NEW.animid
+ || '), (CommID) = ('
+ || NEW.commid
+ || '), (EndDate) = ('
+ || NEW.enddate
+ || ') place the individual in the community after'
+ || ' the individual left the study;'
+ || ' (BIOGRAPHY.Departdate) = ('
+ || departdate
+ || ')';
+ END IF;
+ END;
+
+ END;
+$$;
+
+
+RAISE INFO 'comm_membs_trigger';
+CREATE TRIGGER comm_membs_trigger
+ AFTER INSERT OR UPDATE
+ ON comm_membs FOR EACH ROW
+ EXECUTE PROCEDURE comm_membs_func();
diff --git a/db/schemas/sokwedb/triggers/drop/biography.m4 b/db/schemas/sokwedb/triggers/drop/biography.m4
new file mode 100644
index 0000000..8ea5a38
--- /dev/null
+++ b/db/schemas/sokwedb/triggers/drop/biography.m4
@@ -0,0 +1,24 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Drop triggers for biography table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+
+DROP FUNCTION IF EXISTS biography_func() CASCADE;
+DROP FUNCTION IF EXISTS biography_delete_func CASCADE;
diff --git a/db/schemas/sokwedb/triggers/drop/comm_membs.m4 b/db/schemas/sokwedb/triggers/drop/comm_membs.m4
new file mode 100644
index 0000000..cf07b39
--- /dev/null
+++ b/db/schemas/sokwedb/triggers/drop/comm_membs.m4
@@ -0,0 +1,23 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. http://www.karlpinc.com/
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as published by
+dnl the Free Software Foundation, either version 3 of the License, or
+dnl (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl along with this program. If not, see <https://www.gnu.org/licenses/>.
+dnl
+dnl Drop triggers for the comm_membs table
+dnl
+dnl Karl O. Pinc <kop@karlpinc.com>
+
+dnl m4 includes
+include(`copyright.m4')dnl
+
+DROP FUNCTION IF EXISTS comm_membs_func() CASCADE;
diff --git a/db/schemas/sokwedb/views/Makefile b/db/schemas/sokwedb/views/Makefile
new file mode 100644
index 0000000..7008185
--- /dev/null
+++ b/db/schemas/sokwedb/views/Makefile
@@ -0,0 +1,29 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for views
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# This determines the order in which the views are put into the
+# database. This is important because views must be put into the
+# database after the views they reference.
+ORDER :=
+
+##
+## 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/doc/include/macros.m4 b/doc/include/macros.m4
index 9b0dbdb..6363707 100644
--- a/doc/include/macros.m4
+++ b/doc/include/macros.m4
@@ -32,7 +32,7 @@ dnl Have m4 discard output generated in this file (see bottom)
divert(`-1')
dnl Include standard macros
-dnl include(include/macros.m4)
+dnl include(global_macros.m4)
dnl Define special quotes for RST documents because they use the
dnl backtick character in their syntax.
diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4
index 341f0bb..05d8c58 100644
--- a/doc/src/epilog.inc.m4
+++ b/doc/src/epilog.inc.m4
@@ -41,13 +41,23 @@ sdb_generated_rst()dnl
.. |BIOGRAPHY| replace:: :ref:`BIOGRAPHY <BIOGRAPHY>`
.. |BIOGRAPHY.AnimID| replace:: :ref:`AnimID <BIOGRAPHY.AnimID>`
-.. |BIOGRAPHY.BDMin| replace:: :ref:`BDMin <BIOGRAPHY.BDMin>`
-.. |BIOGRAPHY.BDMax| replace:: :ref:`BDMax <BIOGRAPHY.BDMax>`
+.. |BIOGRAPHY.AnimIDNum| replace:: :ref:`AnimIDNum <BIOGRAPHY.AnimIDNum>`
+.. |BIOGRAPHY.AnimName| replace:: :ref:`AnimName <BIOGRAPHY.AnimName>`
.. |BIOGRAPHY.BirthComm| replace:: :ref:`BirthComm <BIOGRAPHY.BirthComm>`
+.. |BIOGRAPHY.BCCertainty| replace:: :ref:`BCCertainty <BIOGRAPHY.BCCertainty>`
+.. |BIOGRAPHY.Sex| replace:: :ref:`Sex <BIOGRAPHY.Sex>`
+.. |BIOGRAPHY.MomID| replace:: :ref:`MomID <BIOGRAPHY.MomID>`
+.. |BIOGRAPHY.DadID| replace:: :ref:`DadID <BIOGRAPHY.DadID>`
+.. |BIOGRAPHY.DadIDPub| replace:: :ref:`DadIDPub <BIOGRAPHY.DadIDPub>`
+.. |BIOGRAPHY.FirstBorn| replace:: :ref:`FirstBorn <BIOGRAPHY.FirstBorn>`
.. |BIOGRAPHY.BirthDate| replace:: :ref:`BirthDate <BIOGRAPHY.BirthDate>`
-.. |BIOGRAPHY.DepartDate| replace:: :ref:`DepartDate <BIOGRAPHY.DepartDate>`
+.. |BIOGRAPHY.BDMin| replace:: :ref:`BDMin <BIOGRAPHY.BDMin>`
+.. |BIOGRAPHY.BDMax| replace:: :ref:`BDMax <BIOGRAPHY.BDMax>`
+.. |BIOGRAPHY.BDDist| replace:: :ref:`BDDist <BIOGRAPHY.BDDist>`
.. |BIOGRAPHY.EntryDate| replace:: :ref:`EntryDate <BIOGRAPHY.EntryDate>`
-.. |BIOGRAPHY.FirstBorn| replace:: :ref:`FirstBorn <BIOGRAPHY.FirstBorn>`
+.. |BIOGRAPHY.EntryType| replace:: :ref:`EntryType <BIOGRAPHY.EntryType>`
+.. |BIOGRAPHY.DepartDate| replace:: :ref:`DepartDate <BIOGRAPHY.DepartDate>`
+.. |BIOGRAPHY.DepartType| replace:: :ref:`DepartType <BIOGRAPHY.DepartType>`
.. |COMM_IDS| replace:: :ref:`COMM_IDS <COMM_IDS>`
@@ -61,10 +71,24 @@ sdb_generated_rst()dnl
replace:: :ref:`MembCriteria <COMM_IDS.MembCriteria>`
.. |COMM_MEMB_LOG| replace:: :ref:`COMM_MEMB_LOG <COMM_MEMB_LOG>`
+.. |COMM_MEMB_LOG.CommMLID| replace:: :ref:`CommMLID <COMM_MEMB_LOG.CommMLID>`
+.. |COMM_MEMB_LOG.DateOfUpdate|
+ replace:: :ref:`DateOfUpdate <COMM_MEMB_LOG.DateOfUpdate>`
+.. |COMM_MEMB_LOG.AnimID| replace:: :ref:`AnimID <COMM_MEMB_LOG.AnimID>`
+.. |COMM_MEMB_LOG.Description|
+ replace:: :ref:`Description <COMM_MEMB_LOG.Description>`
+.. |COMM_MEMB_LOG.Rationale|
+ replace:: :ref:`Rationale <COMM_MEMB_LOG.Rationale>`
+.. |COMM_MEMB_LOG.MadeBy| replace:: :ref:`MadeBy <COMM_MEMB_LOG.MadeBy>`
.. |COMM_MEMBS| replace:: :ref:`COMM_MEMBS <COMM_MEMBS>`
+.. |COMM_MEMBS.CommMID| replace:: :ref:`CommMID <COMM_MEMBS.CommMID>`
+.. |COMM_MEMBS.AnimID| replace:: :ref:`AnimID <COMM_MEMBS.AnimID>`
.. |COMM_MEMBS.StartDate| replace:: :ref:`StartDate <COMM_MEMBS.StartDate>`
.. |COMM_MEMBS.EndDate| replace:: :ref:`EndDate <COMM_MEMBS.EndDate>`
+.. |COMM_MEMBS.CommID| replace:: :ref:`CommID <COMM_MEMBS.CommID>`
+.. |COMM_MEMBS.StartSource| replace:: :ref:`CommID <COMM_MEMBS.StartSource>`
+.. |COMM_MEMBS.EndSource| replace:: :ref:`CommID <COMM_MEMBS.EndSource>`
.. |DEPARTTYPES| replace:: :ref:`DEPARTTYPES <DEPARTTYPES>`
.. |DEPARTTYPES.DepartType|
@@ -134,11 +158,17 @@ sdb_generated_rst()dnl
.. Used in data table documentation of automatically generated ID columns.
.. |idcol| replace::
A unique, automatically generated, positive integer which serves to
- identify the row.
+ identify the row. |cannot_change|
+
+.. |cannot_change| replace::
+ The value of this column cannot be changed.
.. |uppercase| replace::
This column may not contain lower case letters.
+.. |noseconds| replace::
+ This column must contain values that fall on one minute intervals; it may
+ not contain values having seconds or fractions of seconds.
.. Substitutions, used to give labels to internal links
diff --git a/doc/src/tables/biography.m4 b/doc/src/tables/biography.m4
index 646765d..b6133ba 100644
--- a/doc/src/tables/biography.m4
+++ b/doc/src/tables/biography.m4
@@ -33,6 +33,33 @@ BIOGRAPHY
|BIOGRAPHY_summary| BIOGRAPHY contains the basic demographic data of
individual chimpanzees.
+A mother must be female; the |BIOGRAPHY.Sex| must be ``sdb_female``
+(female) of the |BIOGRAPHY| row identified by an offspring's
+|BIOGRAPHY.MomID|.
+
+A father must be male; the |BIOGRAPHY.Sex| must be ``sdb_male`` (male)
+of the |BIOGRAPHY| row identified by an offspring's |BIOGRAPHY.DadID|.
+
+.. Note that this next rule prevents an individual from being their
+ own mother.
+
+A female cannot be too young when giving birth.
+The difference between the mother's maximum birthdate, the
+|BIOGRAPHY.BDMin| of the |BIOGRAPHY| row identified by an offspring's
+|BIOGRAPHY.MomID|, and the offspring's minimum birthdate, the
+|BIOGRAPHY.BDMin| of the offspring, cannot be less than
+sdb_min_f_birth sdb_min_f_birth_units.
+
+.. Note that this next rule prevents an individual from being their
+ own father.
+
+A male cannot be too young when becoming a parent.
+The difference between the father's maximum birthdate, the
+|BIOGRAPHY.BDMin| of the |BIOGRAPHY| row identified by an offspring's
+|BIOGRAPHY.DadID|, and the offspring's minimum birthdate, the
+|BIOGRAPHY.BDMin| of the offspring, cannot be less than
+sdb_min_m_birth sdb_min_m_birth_units.
+
When the individual is not the first recorded offspring of their
mother, based on the |BIOGRAPHY.BirthDate| of all recorded maternal
siblings, the |BIOGRAPHY.FirstBorn| value must be
@@ -45,6 +72,11 @@ The date the individual left the study (|BIOGRAPHY.DepartDate|) may
not be before the date the individual entered the study
(|BIOGRAPHY.EntryDate|).
+The maximum age of an individual, the time span between the
+individual's earliest possible birth date (|BIOGRAPHY.BDMin|) and their
+|BIOGRAPHY.DepartDate|, may not be more than sdb_max_lifespan
+sdb_max_lifespan_units.
+
.. _BIOGRAPHY.AnimID:
@@ -54,7 +86,7 @@ AnimID (Animal IDentifier)
.. |BIOGRAPHY.AnimID_summary| replace::
A short sequence of characters which uniquely identify the chimpanzee.
-|BIOGRAPHY.AnimID_summary| |keycol|
+|BIOGRAPHY.AnimID_summary| |keycol| |cannot_change|
.. _BIOGRAPHY.AnimIDNum:
@@ -76,7 +108,7 @@ AnimName (Animal Name)
.. |BIOGRAPHY.AnimName_summary| replace::
The name of the chimpanzee.
-|BIOGRAPHY.AnimName_summary| |emptytext| |notnull|
+|BIOGRAPHY.AnimName_summary| |unique| |emptytext| |notnull|
.. _BIOGRAPHY.BirthComm:
@@ -150,7 +182,7 @@ DadIDPub (Publication of Paternity)
Citation of the publication where paternity was declared, or 'Unknown' when
paternity has not yet been published.
-|BIOGRAPHY.DadIDPub_summary| |notnull|
+|BIOGRAPHY.DadIDPub_summary| |emptytext| |notnull|
.. _BIOGRAPHY.FirstBorn:
@@ -176,7 +208,7 @@ BirthDate
.. |BIOGRAPHY.BirthDate_summary| replace::
The individual's (often estimated) birth date.
-|BIOGRAPHY.BirthDate_summary| |notnull|
+|BIOGRAPHY.BirthDate_summary| |notnull| |birthlimits|
.. _BIOGRAPHY.BDMin:
@@ -189,6 +221,7 @@ BDMin (Minimum Birth Date)
|BIOGRAPHY.BDMin_summary| If born into the study, this is the last
date prior to birth that the mother was seen without the infant. |notnull|
+|birthlimits|
.. _BIOGRAPHY.BDMax:
@@ -200,7 +233,7 @@ BDMin (Maximum Birth Date)
The latest possible birthdate.
|BIOGRAPHY.BDMax_summary| If born into the study, this is date of the
-first sighting of the infant. |notnull|
+first sighting of the infant. |notnull| |birthlimits|
.. _BIOGRAPHY.BDDist:
@@ -269,3 +302,7 @@ DepartType
is still alive and in the community.
|BIOGRAPHY.DepartType_summary| |notnull|
+
+
+.. |birthlimits| replace::
+ This value may not be before ``sdb_min_birthdate`` or after the current date.
diff --git a/doc/src/tables/comm_memb_log.m4 b/doc/src/tables/comm_memb_log.m4
index 8043daa..e33e5bd 100644
--- a/doc/src/tables/comm_memb_log.m4
+++ b/doc/src/tables/comm_memb_log.m4
@@ -26,8 +26,9 @@ COMM_MEMB_LOG
.. |COMM_MEMB_LOG_summary| replace::
Each row is a log entry describing a change in an individual
- chimpanzee's community membership. All community membership
- changes since November of 2013 are recorded here.
+ chimpanzee's community membership.
+ All community membership
+ changes starting from ``sdb_first_memb_log_date`` are recorded here.
|COMM_MEMB_LOG_summary|
@@ -55,7 +56,9 @@ DateOfUpdate
The date the database was updated to reflect the change in
community membership.
-|COMM_MEMB_LOG.DateOfUpdate_summary| |notnull|
+|COMM_MEMB_LOG.DateOfUpdate_summary| This value cannot be before
+``sdb_first_memb_log_date`` and cannot be after the current
+date. |notnull|
.. _COMM_MEMB_LOG.AnimID:
diff --git a/doc/src/tables/comm_membs.m4 b/doc/src/tables/comm_membs.m4
index 152e24e..e49d24e 100644
--- a/doc/src/tables/comm_membs.m4
+++ b/doc/src/tables/comm_membs.m4
@@ -26,18 +26,31 @@ COMM_MEMBS
.. |COMM_MEMBS_summary| replace::
Each row represents an un-interrupted series of days during which
- the given chimpanzee is a member of the given community. The unit
- of time is the day; it is not possible to place any given
+ the given chimpanzee is a member of the given community.
+ The unit of time is the day; it is not possible to place any given
chimpanzee in more than one community within a single day.
|COMM_MEMBS_summary| Leaving a community, whether to join another or
-not, ends this period of community membership. Another row in
-``COMM_MEMBS`` is required to record a newer period of community
-membership, whether in the same or a different community.
+not, ends this period of community membership.
+Another row in ``COMM_MEMBS`` is required to record a newer period of
+community membership, whether in the same or a different community.
An individual may not be recorded in more than one community on any
given day, although there may be days during which the individual is
not placed in any community.
+Further, an individual may not be placed in the same community, by
+use of two ``COMM_MEMBS`` rows, on the same day.
+There can be no "overlap" of ``COMM_MEMBS`` rows.
+The |COMM_MEMBS.StartDate| to |COMM_MEMBS.EndDate| intervals, of all
+the ``COMM_MEMBS`` rows with a given |COMM_MEMBS.AnimID|, may not
+overlap.
+
+Two ``COMM_MEMBS`` rows may not be used to place a single individual
+in the same community on successive days.
+Instead, combine the two ``COMM_MEMBS`` rows into one.
+The |COMM_MEMBS.StartDate| of an individual with a given
+|COMM_MEMBS.CommID| may not be the day after the |COMM_MEMBS.EndDate|
+of a ``COMM_MEMBS`` row having the same |COMM_MEMBS.AnimID| value.
An individual may not be placed in a community unless that individual
is under study; the |COMM_MEMBS.StartDate| may not be before the
@@ -45,6 +58,8 @@ individual's |BIOGRAPHY|.\ |BIOGRAPHY.EntryDate| and the
|COMM_MEMBS.EndDate| may not be after the individual's
|BIOGRAPHY|.\ |BIOGRAPHY.DepartDate|.
+The |COMM_MEMBS.StartDate| must not be after the |COMM_MEMBS.EndDate|.
+
.. _COMM_MEMBS.CommMID:
diff --git a/include/global_constants.m4 b/include/global_constants.m4
index b6cc777..9b288de 100644
--- a/include/global_constants.m4
+++ b/include/global_constants.m4
@@ -23,6 +23,10 @@ dnl
dnl Standard test for having already included the file.
define(`_global_constants.m4')dnl
dnl
+dnl Chimpanzee specific limits
+dnl (Included before the "divert", since it finishes by un-diverting.)
+include(`limits.m4')dnl
+dnl
dnl Have m4 discard output generated in this file (see bottom)
divert(`-1')
diff --git a/include/limits.m4 b/include/limits.m4
new file mode 100644
index 0000000..4135b8d
--- /dev/null
+++ b/include/limits.m4
@@ -0,0 +1,57 @@
+dnl Copyright (C) 2023 The Meme Factory, Inc. www.karlpinc.com
+dnl
+dnl This program is free software: you can redistribute it and/or modify
+dnl it under the terms of the GNU Affero General Public License as
+dnl published by the Free Software Foundation, either version 3 of the
+dnl License, or (at your option) any later version.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU Affero General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU Affero General Public License
+dnl
+dnl Establish symbols for limits and other domain specific constants used
+dnl when validating the database.
+dnl
+dnl
+changequote([{[,]}])dnl m4 foolery so includes include only once.
+dnl Once the macro is in the text, change the quotes back
+ifdef([{[_limits.m4]}], [{[changequote(`,')]}], [{[dnl (see bottom)
+changequote(`,')dnl
+dnl
+dnl Standard test for having already included the file.
+define(`_limits.m4')dnl
+dnl
+dnl Have m4 discard output generated in this file (see bottom)
+divert(`-1')
+
+dnl
+dnl Demographic related
+dnl
+
+dnl The earliest possible birthdate
+define(`sdb_min_birthdate', `1910-01-01')
+
+dnl The maximum lifespan
+define(`sdb_max_lifespan', `50')
+define(`sdb_max_lifespan_units', `years')
+
+dnl The minimum age at which a female can give birth.
+define(`sdb_min_f_birth', `10')
+define(`sdb_min_f_birth_units', `years')
+
+dnl The minimum age of a male at the birth of their first offspring
+define(`sdb_min_m_birth', `15')
+define(`sdb_min_m_birth_units', `years')
+
+dnl
+dnl Administration related
+dnl
+
+dnl The earliest possible COMM_MEMB_LOG.DateOfUpdate
+define(`sdb_first_memb_log_date', `2013-12-01')
+
+divert(`0')dnl Output with m4 again
+]}])dnl End of ifdef over the whole file.
diff --git a/make_files/db_doc_header.mk b/make_files/db_doc_header.mk
new file mode 100644
index 0000000..6e7eec4
--- /dev/null
+++ b/make_files/db_doc_header.mk
@@ -0,0 +1,28 @@
+# Copyright (C) 2023 The Meme Factory, Inc. 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 <https://www.gnu.org/licenses/>.
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+# This file exists only to give the database section of the Makefile
+# documentation a breakpoint/header.
+
+##
+## ########################################################################
+## DATABASE TARGETS
+##
+## Unless otherwise noted, database modification takes place in a transaction.
+##
+## Variables:
+##
diff --git a/make_files/defaults.mk b/make_files/defaults.mk
new file mode 100644
index 0000000..0f610a0
--- /dev/null
+++ b/make_files/defaults.mk
@@ -0,0 +1,53 @@
+# Copyright (C) 2012, 2013, 2023 The Meme Factory, Inc.
+# http://www.karlpinc.com/
+# Copyright (C) 2004, 2005, 2008, 2011 Karl O. Pinc <kop@karlpinc.com>
+#
+# This file is part of Sokwedb.
+#
+# Sokwedb is free software; you can redistribute it and/or modify
+# it under the terms of the GNU 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 General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with Sokwedb. If not, see <http://www.gnu.org/licenses/>.
+#
+# Default variable assignments for database creation related makefiles.
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+#
+# Database Connection Variables:
+#
+## SOKWEDB_DB
+## The name of the database with which to interact. The default
+## is 'sokwedb_copy'.
+##
+## ADMINUSER
+## The PostgreSQL role used to connect to the database. This must
+## have the permissions necessary to modify the db structure, etc.
+## The default is: sokwedb_admin
+##
+## HOST
+## The host hosting the database. The default is:
+## sokwe-dbs.postgresql.database.azure.com
+
+ifeq ($(strip $(SOKWEDB_DB)),)
+ export SOKWEDB_DB := sokwedb_copy
+endif
+export HOST := sokwe-dbs.postgresql.database.azure.com
+export ADMINUSER := sokwedb_admin
+
+# For invoking psql everywhere.
+export PSQL_ARGS_MINIMAL = -U $(ADMINUSER) -d $(SOKWEDB_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 := echo SET client_min_messages TO WARNING\; ; \
+ echo "SET plpgsql.extra_warnings TO 'all'\;" ;
diff --git a/make_files/make_db.mk b/make_files/make_db.mk
new file mode 100644
index 0000000..a96aa5c
--- /dev/null
+++ b/make_files/make_db.mk
@@ -0,0 +1,430 @@
+# Copyright (C) 2012, 2013, 2015, 2023 The Meme Factory, Inc.
+# http://www.karlpinc.com/
+# Copyright (C) 2004, 2005, 2008, 2011 Karl O. Pinc <kop@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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for database
+#
+# Karl O. Pinc <kop@karlpinc.com>
+#
+# Type "make" or "make help" for help on the build process.
+#
+# For initial system installation do:
+#
+# make databases
+#
+# (It may be necessary to 'make databases' as the postgres
+# Unix user, depending on your pg_hba.conf file.)
+#
+# make SOKWEDB_DB=sokwedb install
+# make SOKWEDB_DB=sokwedb_test install
+# make SOKWEDB_DB=sokwedb_copy install
+#
+# Bugs:
+#
+# The retriggernolock target does not
+# operate inside a transaction. Be _careful_ doing this
+# on the live database!
+#
+
+#
+# Variables which might be set on the command line:
+#
+
+##
+## Variables used by only some targets:
+##
+## TARGET_SCHEMA
+## Choose the schema when performing a build step on a specific
+## database object.
+##
+## TARGET_TYPE
+## Choose what sort of database object to operate on when performing a
+## build step on a specific database object. The valid choices are one
+## of: tables/create triggers/create views/create indexes/create
+## functions/create tables/drop triggers/drop views/drop indexes/drop
+## functions/drop
+##
+## TARGET_THING
+## Choose a database object (table, function, view, etc.) to
+## target for a specific build step.
+##
+## Example use of the TARGET_* variables:
+## make TARGET_SCHEMA=sokwedb TARGET_TYPE=tables/create \
+## TARGET_THING=biography sql_file
+
+##
+## To use a variable, set it on the command line, e.g.:
+## make SOKWEDB_DB=sokwedb_test installtables
+
+# Paths. Be explicit instead of using VPATH, et-al.
+DB_DIR = db
+# Locations of m4 macros
+export M4_DB_INCLUDE_PATH := $(DB_DIR)/include
+M4_DB_INCLUDE_ARGS := -I $(M4_DB_INCLUDE_PATH) -I $(M4_GLOBAL_INCLUDE_PATH)
+
+# Rebuild when any of these change
+export DB_DEPENDS := Makefile \
+ $(GENERIC_DEPENDS) \
+ $(wildcard $(M4_DB_INCLUDE_PATH)/*.m4) \
+ $(wildcard $(M4_DB_INCLUDE_PATH)/*.sql)
+
+##
+## The available targets for make (make TARGET) are:
+##
+
+# The files built by this Makefile
+# (Order is significant for CREATE/DROP targets, it is used to determine
+# install order.)
+# The db/creategroups.sql file is deliberately left off of CREATE_DB_TARGETS
+# because we do not drop the roles. (Doing so would break permission
+# assignment on existing user roles.) Group roles are expected to be
+# created at database creation time.
+CREATE_DB_TARGETS := db/createtypes.sql
+DROP_DB_TARGETS := db/droptypes.sql
+DB_TARGETS := $(CREATE_DB_TARGETS) $(DROP_DB_TARGETS)
+# The files built by other Makefiles
+# (Order is significant for CREATE/DROP targets, it is used to determine
+# install order.)
+CREATE_SUB_DB_TARGETS := db/schemas/createschemas.sql \
+ db/schemas/createtables.sql \
+ db/schemas/createindexes.sql \
+ db/schemas/createtriggers.sql \
+ db/schemas/createfunctions.sql \
+ db/schemas/createviews.sql
+DROP_SUB_DB_TARGETS := db/schemas/dropviews.sql \
+ db/schemas/dropindexes.sql \
+ db/schemas/droptriggers.sql \
+ db/schemas/dropfunctions.sql \
+ db/schemas/droptables.sql \
+ db/schemas/dropschemas.sql
+
+## check Validate input files against expected generated output
+.PHONY: check
+check:
+ $(MAKE) -C db/schemas check
+
+## install-db Install all database objects (schemas, tables, etc.)
+.PHONY: install-db
+install-db: db/schemas/createschemas.sql \
+ $(CREATE_DB_TARGETS) $(CREATE_SUB_DB_TARGETS)
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+## reinstall-db Drop all the database objects and re-install them
+## This is done in two transactions, one to drop
+## database objects and another to create them.
+## (This does not exercise destroy-db.)
+# This drops entire schemas to delete the database objects.
+.PHONY: reinstall-db
+reinstall-db: db/schemas/dropschemas.sql \
+ $(CREATE_DB_TARGETS) $(CREATE_SUB_DB_TARGETS)
+ ( $(PSQL_SETUP) \
+ cat db/schemas/dropschemas.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+ ( echo 'VACUUM;' ; ) \
+ | psql $(PSQL_ARGS)
+ ( $(PSQL_SETUP) \
+ cat $(CREATE_DB_TARGETS) $(CREATE_SUB_DB_TARGETS) ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+## destroy-db Drop all the database objects, individually
+# This drops each individual database object, then the schemas.
+.PHONY: destroy-db
+destroy-db: $(DROP_SUB_DB_TARGETS) $(DROP_DB_TARGETS)
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+ ( echo 'VACUUM;' ; ) \
+ | psql $(PSQL_ARGS)
+
+## installtables Create all tables in the db and set their permissions
+## droptables Drop all tables in the db
+## installtriggers Create all triggers in the db and set their permissions
+## droptriggers Drop all triggers in the db
+## installfunctions Create all functions in the db and set their
+## permissions
+## dropfunctions Drop all functions in the db
+## installviews Create all views in the db and set their permissions
+## dropviews Drop all views in the db
+## installindexes Create all indexes in the db and set their permissions
+## dropindexes Drop all indexes in the db
+.PHONY: installtables droptables \
+ installtriggers droptriggers \
+ installfunctions dropfunctions \
+ installviews dropviews \
+ installindexes dropindexes
+installtables droptables \
+installtriggers droptriggers \
+installfunctions dropfunctions \
+installviews dropviews \
+installindexes dropindexes:
+ $(MAKE) -C db/schemas $@
+
+## installtypes Install all the types into the db
+.PHONY: installtypes
+installtypes: createtypes.sql
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+## droptypes Drop all the types from the db
+.PHONY: droptypes
+droptypes: droptypes.sql
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+
+# Convenience targets
+# These tend to rely on CREATE OR REPLACE syntax being used, but
+# someday may need to be more complicated.
+
+## retable Re-install all the tables
+.PHONY: retable
+retable: db/schemas/droptables.sql db/schemas/createtables.sql
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | 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
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+## retriggernolock Re-install all the triggers, outside of a transaction.
+## Dangerous, but possibly useful during development
+## 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
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | psql $(PSQL_ARGS)
+
+## refunction Re-install all the functions
+.PHONY: refunction
+refunction: db/schemas/dropfunctions.sql db/schemas/createfunctions.sql
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | 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
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | 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
+ ( $(PSQL_SETUP) \
+ cat $? ; ) \
+ | 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
+# ( $(PSQL_SETUP) \
+# cat $? ; ) \
+# | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+## clean-db Remove all the generated files
+.PHONY: clean-db
+clean-db:
+ $(MAKE) -C db/schemas clean-db
+ rm -rf $(DB_TARGETS) db/creategroups.sql
+
+
+##
+## Lesser used targets:
+##
+
+## sql_file Create the file
+## db/schemas/<schemaname>/<type>/<tablename>.sql
+## based on the TARGET_SCHEMA, TARGET_TYPE, and
+## TARGET_THING variables. The generated file
+## contains the SQL used to create and, when
+## appropriate, set permissions on the designated
+## database object.
+.PHONY: sql_file
+sql_file:
+ $(MAKE) -C db/schemas sql_file
+
+## install_schemas Create the schemas
+.PHONY: install_schemas
+install_schemas: 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
+ ( $(PSQL_SETUP) \
+ cat db/schemas/dropschemas.sql ; ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
+## install_schema_tables
+## Install the tables into the schema of the TARGET_SCHEMA
+## variable
+## destroy_schema_tables
+## Drop the tables in the schema given by the
+## TARGET_SCHEMA variable
+## install_schema_triggers
+## Install the triggers into the schema of the
+## TARGET_SCHEMA variable
+## destroy_schema_triggers
+## Drop the triggers in the schema given by the
+## TARGET_SCHEMA variable
+## install_schema_functions
+## Install the functions into the schema of the
+## TARGET_SCHEMA variable
+## destroy_schema_functions
+## Drop the functions in the schema given by the
+## TARGET_SCHEMA variable
+## install_schema_views
+## Install the views into the schema of the TARGET_SCHEMA
+## variable
+## destroy_schema_views
+## Drop the views in the schema given by the
+## TARGET_SCHEMA variable
+## install_schema_indexes
+## Install the indexes into the schema of the
+## TARGET_SCHEMA variable
+## destroy_schema_indexes
+## Drop the indexes in the schema given by the
+## TARGET_SCHEMA variable
+.PHONY: 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_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:
+ $(MAKE) -C db/schemas $@
+
+## db/schemas/createschemas.sql
+## Build a file of sql which creates schemas and grants
+## permission to them
+## db/schemas/dropschemas.sql
+## Build a file of sql which drops all schemas
+## db/schemas/createtables.sql
+## Build a file of sql which creates tables and grants
+## permission to them
+## db/schemas/droptables.sql
+## Build a file of sql which drops all tables
+## db/schemas/createtriggers.sql
+## Build a file of sql which creates triggers and grants
+## permission to them
+## db/schemas/droptriggers.sql
+## Build a file of sql which destroys all triggers
+## db/schemas/createfunctions.sql
+## Build a file of sql which creates functions and grants
+## permission to them
+## db/schemas/dropfunctions.sql
+## Build a file of sql which destroys all functions
+## db/schemas/createviews.sql
+## Build a file of sql which creates views and grants
+## permission to them
+## db/schemas/dropviews.sql
+## Build a file of sql which destroys all views
+## db/schemas/createindexes.sql
+## Build a file of sql which creates indexes
+## db/schemas/dropindexes.sql
+## Build a file of sql which destroys all indexes
+# Phony targets because we rely on sub-make for dependencies.
+.PHONY: db/schemas/createschemas.sql db/schemas/dropschemas.sql \
+ 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/createschemas.sql db/schemas/dropschemas.sql \
+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:
+ $(MAKE) -C db/schemas $$(basename $@)
+
+## db/createtypes.sql Build file of sql which makes data types
+## db/droptypes.sql Build file of sql which drops data types
+## db/creategroups.sql
+## Build file of sql which creates the groups (roles)
+## expected by SokweDB
+$(DB_TARGETS): %.sql: %.m4 $(DB_DEPENDS)
+ m4 $(M4_DB_INCLUDE_ARGS) $< > $@
+
+# 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:
+# $(MAKE) -C test reset
+
+# No target for regression testing at the moment
+#.PHONY: test
+#test:
+# cd test; \
+# make test;
+
+# No integrity checking (aka regression testing) going on at the moment
+#check_integrity: check_integrity.m4
+# m4 $(M4_DB_INCLUDE_ARGS) check_integrity.m4 > check_integrity
+
+# Don't have permission to test this at the momment
+#.PHONY: databases
+#databases: makedatabases
+# sh makedatabases
+
+##
+## Database objects are sensitive to the order in which they are created.
+## I.e. you can't create an index or a trigger on a non-existant table.
+##
+## Debugging Tips:
+## Examining the generated SQL can be useful when debugging.
+## Especially to match up line numbers reported in error messages with
+## the line numbers of the m4 source. Note that reported line numbers
+## refer to the line number within the string that makes up the
+## function definition, and that that string never starts on the first
+## line of the file containing the SQL. Worst case, temporarly insert
+## a SQL comment in the m4 source that documents the comment's line
+## number in the source file. Your temporary comment will cause the
+## reported line number to change if occurs before the reported error.
+## Alternately, RAISE a NOTICE that supplies the source file's line number
+## at various points and note the last notice raised during execution.
+## You can also feed generated SQL directly to psql.
+##
+## Caution: If tables, functions, or other database objects are to be removed
+## from the database care must be taken to first drop the objects from the
+## database(s) _before_ deleting the code which drops them.
+##
+## WARNING: Dropping or re-installing certain types of database objects
+## can leave the database in an incomplete state. For example,
+## dropping tables drops all the associated indexes, triggers, and views,
+## and simply re-creating the tables does not re-create the associated
+## dropped objects.
+##
+## WARNING: Dropping database objects can drop related objects from the
+## user's private schemas.
+
diff --git a/make_files/make_docs.mk b/make_files/make_docs.mk
index 6814ace..f548d59 100644
--- a/make_files/make_docs.mk
+++ b/make_files/make_docs.mk
@@ -24,6 +24,7 @@ HTML_LOCAL_TARGET := local_html
HTML_TARGET := html
HTML_SINGLE_TARGET := html-single
+##
## ########################################################################
## DOCUMENTATION TARGETS
##
@@ -91,18 +92,15 @@ LATEX_SRCDIR := $(SPHINX_DOCDIR)/latex_src
LATEX_IMAGEDIR := $(LATEX_SRCDIR)/images
# Locations of m4 macros
-M4_INCLUDE_PATH := include
M4_DOC_INCLUDE_PATH := doc/include
-M4_DB_INCLUDE_PATH := db/include
+M4_DOC_INCLUDE_ARGS := -I $(M4_DOC_INCLUDE_PATH) -I $(M4_GLOBAL_INCLUDE_PATH)
# The locations the targets are copied into
PDF_A4_TARGET := $(DOCDIR)/$(PDF_A4_NAME)
PDF_LETTER_TARGET := $(DOCDIR)/$(PDF_LETTER_NAME)
# Rebuild when any of these change
-DB_DEPENDS := Makefile $(wildcard $(M4_INCLUDE_PATH)/*.m4) \
- $(wildcard $(M4_DB_INCLUDE_PATH)/*.m4)
-DOC_DEPENDS := Makefile $(wildcard $(M4_INCLUDE_PATH)/*.m4) \
+DOC_DEPENDS := $(GENERIC_DEPENDS) \
$(wildcard $(M4_DOC_INCLUDE_PATH)/*.m4) \
$(DOCDIR)/conf.py
@@ -208,7 +206,7 @@ mostlyclean-docs:
## clean-docs Delete all user generated files
.PHONY: clean-docs
-clean: mostlyclean-docs destroy-sphinx-venv
+clean-docs: mostlyclean-docs destroy-sphinx-venv
##
## Update the documenation tools to newer versions with:
@@ -380,12 +378,12 @@ $(SPHINX_PDF_LETTER_TARGET): latex_rst $(PNG_FILES) $(LATEX_EPILOG) \
# The rst_epilogs of conf.py.
$(HTML_EPILOG): $(SOURCE_EPILOG) $(DOC_DEPENDS)
- m4 -I $(M4_DOC_INCLUDE_PATH) \
+ m4 $(M4_DOC_INCLUDE_ARGS) \
--define=sdb_output_fmt=$(SDB_HTML_FMT) \
$< \
> $@
$(LATEX_EPILOG): $(SOURCE_EPILOG) $(DOC_DEPENDS)
- m4 -I $(M4_DOC_INCLUDE_PATH) \
+ m4 $(M4_DOC_INCLUDE_ARGS) \
--define=sdb_output_fmt=latex \
$< \
> $@
@@ -411,7 +409,7 @@ $(PNG_FILES): $$(patsubst %.png,%.svg,\
$(HTML_RST_FILES): $$(patsubst %.rst,%.m4,\
$$(subst $(HTML_SRCDIR),$(STOCK_SRCDIR),$$@)) \
$(DOC_DEPENDS)
- m4 -I $(M4_DOC_INCLUDE_PATH) \
+ m4 $(M4_DOC_INCLUDE_ARGS) \
--define=sdb_output_fmt=$(SDB_HTML_FMT) \
$< \
> $@
@@ -420,7 +418,7 @@ $(HTML_RST_FILES): $$(patsubst %.rst,%.m4,\
$(LATEX_RST_FILES): $$(patsubst %.rst,%.m4,\
$$(subst $(LATEX_SRCDIR),$(STOCK_SRCDIR),$$@)) \
$(DOC_DEPENDS)
- m4 -I $(M4_DOC_INCLUDE_PATH) \
+ m4 $(M4_DOC_INCLUDE_ARGS) \
--define=sdb_output_fmt=latex \
$(patsubst %.rst,%.m4,\
$(subst $(LATEX_SRCDIR),$(STOCK_SRCDIR),$@)) \
diff --git a/make_files/set_search_path.sh b/make_files/set_search_path.sh
new file mode 100755
index 0000000..c41e300
--- /dev/null
+++ b/make_files/set_search_path.sh
@@ -0,0 +1,26 @@
+#!/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 <https://www.gnu.org/licenses/>.
+#
+# Construct SQL that puts the current (as far as building goes) schema
+# at the top of the search path. (Expected to be called from the
+# db/schemas/<schema>/<type>/ directory.)
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+printf '%s%s%s\n' \
+ "SELECT set_config('search_path', '" \
+ $(basename $(dirname $(pwd))) \
+ ", ' || current_setting('search_path'), FALSE);"
diff --git a/make_files/things.mk b/make_files/things.mk
new file mode 100644
index 0000000..03990d0
--- /dev/null
+++ b/make_files/things.mk
@@ -0,0 +1,171 @@
+# 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 <https://www.gnu.org/licenses/>.
+#
+# Makefile for database objects
+#
+# Requires $ORDER be defined.
+# This variable determines the order in which the database objects are
+# put into the database. This is important because the tables
+# containing foreign keys must be put into the database after the
+# tables they reference, likewise with functions and views, etc.
+#
+# Karl O. Pinc <kop@karlpinc.com>
+
+##
+## CAUTION: Use the top-level Makefile. Direct invocation of this
+## Makefile does not guarentee the examination of all dependencies.
+##
+include ../../../../make_files/help.mk
+include ../../../../make_files/defaults.mk
+
+## Targets:
+##
+
+CREATE_TARGETS := $(patsubst %,create/%.sql,$(ORDER))
+DROP_TARGETS := $(patsubst %,drop/%.sql,$(ORDER))
+
+# These are the targets that can be made
+TARGETS := createthings.sql dropthings.sql $(CREATE_TARGETS) $(DROP_TARGETS)
+
+# Path to the m4 include directories.
+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)
+
+# All the files that might matter when building the database
+DB_DEPENDS := $(patsubst %,../../%,$(DB_DEPENDS)) Makefile
+
+## check Test that an input file exists for every table make expects
+.PHONY: check
+check:
+ export schema=$$(basename $$(dirname $$(pwd))) ; \
+ for m4name in create/*.m4 drop/*.m4 ; do \
+ dname=$$(basename $$(pwd))/$$(dirname $${m4name}) ; \
+ tname=$$(basename -s .m4 $${m4name}) ; \
+ awk -v order="$(ORDER)" \
+ -v dname=$${dname} \
+ -v tname=$${tname} \
+ 'BEGIN {if (match(order, "(^| )" tname "($$| )") == 0) \
+ exit 2; \
+ }' /dev/null \
+ || { printf "%s %s %s%s%s%s%s.m4 %s\n" \
+ "The Makefile's ORDER variable" \
+ 'does not know about the' \
+ $${schema} '/' $${dname} '/' $${tname} 'file' \
+ ; exit 2 ; } ; \
+ done ; \
+ for target in $(ORDER) ; do \
+ [ -f create/$${target}.m4 ] \
+ || { printf "Target %s.%s has no m4 file to create it\n" \
+ $${schema} $${target} \
+ ; exit 1 ; } ; \
+ if [ $$(basename $$(pwd)) != 'tables' ] ; then \
+ [ -f drop/$${target}.m4 ] \
+ || { printf "Target %s.%s has no m4 file to drop it\n" \
+ $${schema} $${target} \
+ ; exit 1 ; } ; \
+ fi ; \
+ done
+
+# The phony targets that alter the database
+
+## install Install the database objects into the database
+.PHONY: install
+install: createthings.sql
+ ( $(PSQL_SETUP) \
+ ../../../../make_files/set_search_path.sh ; \
+ cat createthings.sql ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## destroy Remove the database objects from the database
+.PHONY: destroy
+destroy: dropthings.sql
+ ( $(PSQL_SETUP) \
+ ../../../../make_files/set_search_path.sh ; \
+ cat dropthings.sql ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+## destroytables Remove the tables from the database
+.PHONY: destroytables
+destroytables: droptables.sql
+ ( $(PSQL_SETUP) \
+ ../../../../make_files/set_search_path.sh ; \
+ cat droptables.sql ) \
+ | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS)
+
+##
+## Lesser used targets:
+##
+# Makefile related phony targets.
+
+## clean-db Clean up the files produced by make
+.PHONY: clean-db
+clean-db:
+ rm -rf $(TARGETS) droptables.sql
+
+## createthings.sql
+## A file with the SQL to make all the database objects
+createthings.sql: $(CREATE_TARGETS) $(DB_DEPENDS)
+ printf '%s %s\n' '-- Copyright (C) 2023 The Meme Factory, Inc.' \
+ ' https://karlpinc.com/' > createthings.sql
+ cat ../../../include/copyright.sql >> createthings.sql
+ cat ../../../include/do_not_edit.sql >> createthings.sql
+ export \
+ pathend="$$(basename $$(dirname $$(pwd)))/$$(basename $$(pwd))" ; \
+ for target in $(CREATE_TARGETS) ; do \
+ printf "RAISE INFO USING MESSAGE='Running %s/%s';\n" \
+ $${pathend} $${target} \
+ >> createthings.sql ; \
+ cat $${target} >> createthings.sql ; \
+ done
+
+## dropthings.sql
+## A file with the SQL which drops all the database objects
+dropthings.sql: $(DROP_TARGETS) $(DB_DEPENDS)
+ printf '%s %s\n' '-- Copyright (C) 2023 The Meme Factory, Inc.' \
+ ' https://karlpinc.com/' > dropthings.sql
+ cat ../../../include/copyright.sql >> dropthings.sql
+ cat ../../../include/do_not_edit.sql >> dropthings.sql
+ export \
+ pathend="$$(basename $$(dirname $$(pwd)))/$$(basename $$(pwd))" ; \
+ export "REVERSE=$$(printf '%s ' '$(DROP_TARGETS)' | tac -s ' ')" ; \
+ for target in $${REVERSE} ; do \
+ printf "RAISE INFO USING MESSAGE='Running %s/%s';\n" \
+ $${pathend} $${target} \
+ >> createthings.sql ; \
+ cat $${target} >> dropthings.sql ; \
+ done
+
+# The tables are a special case -- we write the drop statements
+## droptables.sql
+## A file with the SQL which drops all the tables
+droptables.sql: $(DB_DEPENDS)
+ printf '%s %s\n' '-- Copyright (C) 2023 The Meme Factory, Inc.' \
+ ' https://karlpinc.com/' > droptables.sql
+ cat ../../../include/copyright.sql >> droptables.sql
+ cat ../../../include/do_not_edit.sql >> droptables.sql
+ export "REVERSE=$$(printf '%s ' '$(ORDER)' | tac -s ' ')" ; \
+ for table in $${REVERSE} ; do \
+ printf 'DROP TABLE IF EXISTS %s CASCADE;\n' $${table} \
+ >> droptables.sql ; \
+ done
+
+## THING Put SQL which creates the database object into a file
+## named "THING" (which must have a .sql suffix)
+# The rule to expand the m4 files.
+# Delete the target when m4 fails, for idempotency.
+$(CREATE_TARGETS) $(DROP_TARGETS): %.sql: %.m4 $(DB_DEPENDS)
+ m4 $(M4_DB_INCLUDE_ARGS) $< > $@ \
+ || { rm -f $@ ; exit 1 ; }
--
2.34.1