From 9ddd32d8ff051fe1e631f547fbab61c2ce9a5687 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Wed, 26 Feb 2025 17:25:29 -0600 Subject: [PATCH] Port macros and Sphinx substitutions --- db/include/macros.m4 | 23 ++++++++++++++++++ db/include/tablemacros.m4 | 49 ++++++++++++++++++++++++++++++++++++--- doc/src/epilog.inc.m4 | 10 ++++++++ 3 files changed, 79 insertions(+), 3 deletions(-) diff --git a/db/include/macros.m4 b/db/include/macros.m4 index 47cd106..9cab3bc 100644 --- a/db/include/macros.m4 +++ b/db/include/macros.m4 @@ -141,6 +141,29 @@ define({inserted_null}, {dnl }) changequote(`,') +dnl Plpgsql fragment for preventing values from being inserted into +dnl columns which have automatically generated values. +dnl This gets the user an error message instead of silently ignoring +dnl the inserted value. +dnl +dnl Syntax: inserted_null(table, column) +dnl Variables required: +dnl Restrictions: Must be used in a FOR EACH ROW insert trigger function. +changequote({,}) +define({inserted_null}, {dnl + IF NEW.$2 IS NOT NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error updating $1' + , DETAIL = '($2) = (' + || NEW.$2 + || '): $1.$2 must be NULL upon INSERT' + , HINT = 'This column contains automatically generated content;' + ' insert NULL values or omit the column from the' + ' INSERT statement'; + END IF; +}) +changequote(`,') + dnl Strings used in error messages dnl define(`sdb_cannot_change_msg', diff --git a/db/include/tablemacros.m4 b/db/include/tablemacros.m4 index ddbe364..0cbf280 100644 --- a/db/include/tablemacros.m4 +++ b/db/include/tablemacros.m4 @@ -1,4 +1,4 @@ -dnl Copyright (C) 2012, 2014, 2023, 2024 The Meme Factory, Inc. +dnl Copyright (C) 2012, 2014, 2023, 2024, 2025 The Meme Factory, Inc. dnl http://www.karlpinc.com/ dnl Copyright (C) 2007, 2008, 2010, 2011, Karl O. Pinc dnl @@ -46,6 +46,15 @@ include(macros.m4) dnl Don't output anything while defining macros. divert(`-1') +dnl Declare a sequence-based primary key of any (suitable) data type +dnl Syntax: key_column(table_name, column_name, type) +define(`key_column', `$2 $3 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY + ( SEQUENCE NAME $1_$2_seq ) -- the pg default name')`'dnl + +dnl Declare a "stock" INT primary key +dnl Syntax: int_key(table_name, column_name) +define(`int_key', `key_column(`$1', `$2', `INTEGER')') + dnl Make a table with/without a INTEGER SERIAL id. dnl Syntax: serial_table(tablename, id_datatype, tablecols) changequote([,]) @@ -54,14 +63,14 @@ ifelse($2, [INTEGER],changequote([,])dnl Remove braces surrounding expanded tex [dnl dnl Integer key, use SERIAL datatype CREATE TABLE $1 ( - id SERIAL PRIMARY KEY + int_key(`$1', `id') ,]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') + key_column(`$1', `id', `$2') ,]changequote(`,')dnl See above. )dnl $3dnl @@ -95,6 +104,12 @@ grant_priv($1) -- Data types/column definitions -- +-- Sys_Period columns +define(`sys_period_col',dnl + `sys_period TSTZRANGE NOT NULL + DEFAULT tstzrange(current_timestamp, NULL)') + + -- AnimID define(`animid_column', `animid_type_column(animid, AnimID, NULL)')dnl @@ -126,6 +141,21 @@ define([emptytext_check], [ CONSTRAINT "$1: Cannot be empty or nothing CHECK(trim(from $1) <> '')])dnl changequote(`,')dnl See above. +-- One column must be the empty string if another is NULL. +-- +-- Syntax: empty_if_null(col1, col2) +-- +-- Input: +-- col1 This column must be the empty string if col2 is null. +-- col2 Name of the referenced column. +-- +changequote([,]) +define([empty_if_null], [ CONSTRAINT + "$1 must be the empty string if $2 is NULL" + CHECK(($2 IS NULL AND $1 IS NULL) + OR $2 IS NOT NULL)])dnl +changequote(`,')dnl See above + -- A column cannot contain nothing but spaces. -- -- Syntax: notonlyspaces_check(col) @@ -138,6 +168,19 @@ define([notonlyspaces_check], [ CONSTRAINT "$1: Cannot be nothing but CHECK($1 IS NULL OR $1 = '' OR trim(from $1) <> '')])dnl changequote(`,')dnl See above. +-- A column cannot contain leading or trailing spaces. +-- +-- Syntax: trimmedofspaces_check(col) +-- +-- Input: +-- col +-- col Name of the column to check. +-- +changequote([,]) +define([trimmedofspaces_check], [ CONSTRAINT "$1: Cannot begin or end with spaces" + CHECK($1 IS NULL OR trim(from $1) = $1)])dnl +changequote(`,')dnl See above. + -- A column cannot contain any spaces. -- -- Syntax: nospaces_check(col) diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index f4f379c..ecf4044 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -280,6 +280,11 @@ sdb_generated_rst()dnl .. |unique| replace:: Each value stored in this column must be unique. +.. The unique() macro is used for uniquenocase. +.. |uniquenocase| replace:: + Each value stored in this column must be unique, independent of + character case. + .. |notnull| replace:: This column may *not* be |null|. @@ -298,6 +303,10 @@ sdb_generated_rst()dnl .. |nospaces| replace:: This column may not contain whitespace characters. +.. |trimmedofspaces| replace:: + This column must not begin or end with whitespace, i.e., spaces, + tabs, etc. + .. |notonlyspaces| replace:: This column may be empty text. It need not contain characters, but it may not contain only whitespace characters. @@ -313,6 +322,7 @@ sdb_generated_rst()dnl .. |cannot_change| replace:: The value of this column cannot be changed. + .. |system_maintained| replace:: The value of this column is automatically maintained by the system. Values inserted into this column must be |null| (or the column may -- 2.34.1