dnl Copyright (C) 2012, 2014 The Meme Factory, Inc. http://www.meme.com/ dnl Copyright (C) 2007, 2008, 2010, 2011, Karl O. Pinc dnl dnl This file is part of Gombe-MI. dnl dnl Gombe-MI 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 Gombe-MI. If not, see . dnl dnl Karl O. Pinc dnl dnl Macros used to create 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 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 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 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 $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 descr 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, descr TEXT NOT NULL UNIQUE empty_string_check(descr) ); grant_priv($1) ') -- -- Data types/column definitions -- -- Sname define(`sname_column', `sname_type_column(sname, Sname, NULL)')dnl dnl A Sname column with a different name. dnl Syntax: sname_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(`sname_type_column', ` $1 CHAR(3) $3 CONSTRAINT "$2 on BIOGRAPH" REFERENCES biograph(sname)')dnl -- Sname -- on MATUREDATES -- There's a few tables that require a maturity date. define(`maturedates_sname_column', ` sname CHAR(3) NOT NULL CONSTRAINT "Sname on MATUREDATES" REFERENCES maturedates(sname)')dnl -- -- Constraints -- -- A column cannot contain the empty string or nothing but spaces. -- -- Syntax: empty_string_check(col) -- -- Input: -- col Name of the column to check. -- changequote([,]) define([empty_string_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: no_spaces_check(col) -- -- Input: -- col Name of the column to check. -- changequote([,]) define([no_spaces_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: space_not_allowed_check(col) -- -- Input: -- col Name of the column to check. -- changequote([,]) define([space_not_allowed_check], [ CONSTRAINT "$1: Cannot contain any spaces" CHECK($1 IS NULL OR POSITION(' ' IN $1) = 0)])dnl changequote(`,')dnl See above. -- A time-ish column cannot contain seconds or fractions of a second. -- -- Syntax: no_seconds_check(col) -- -- Input: -- col Name of the column to check. -- changequote([,]) define([no_seconds_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 -- -- Triggers -- -- -- We can't use a regular REFERENCES constraint with AnimID because -- we regularly replace the content of BIOGRAPHY_DATA and we -- don't want to have to defer every one of these constraints until -- transaction commit. Instead, we use 2 triggers, one on -- BIOGRAPHY_DATA that's deferred and another on the referencing -- table that's not. Rather than use a pl/pgsql EXECUTE statement -- we use m4 to define each pair of triggers. -- -- This is done in the table creation code because it's expected -- to be a "set it and forget it" type of constraint. -- -- A column references BIOGRAPY_DATA.AnimID. -- -- Syntax: animid_ref(table, col, key) -- -- Input: -- table The table which has the referencing column. -- col Name of the referenceing column. -- key (Optional) Name of the column which keys the table. -- When present key is shown in error message. -- -- Remarks: -- Creates the following: -- $1__$2_animid_ref_func() Function to check animid exists. -- $1__$2_animid_refd_func() Function to check animid is not referenced. -- 0_$1__$2_animid_ref_trigger Trigger to call ref function. -- 0_$1__$2_animid_refd_trigger Trigger to call refd function. -- -- Triggers begin with a0_ so they execute before other triggers. -- changequote([,]) define([animid_ref], [ include(`globalmacros.m4') SELECT '$1__$2_animid_ref_func' AS function; CREATE OR REPLACE FUNCTION $1__$2_animid_ref_func() RETURNS trigger LANGUAGE plpgsql gmi_function_set_search_path AS $$ -- Function for $1 insert and update to check -- $2 references to BIOGRAPHY_DATA.animid. -- -- GPL_notice(` --', `2012', `The Meme Factory, Inc. http://www.meme.com/') -- BEGIN IF NEW.$2 IS NOT NULL THEN PERFORM 1 FROM biography_data WHERE biography_data.animid = NEW.$2 LIMIT 1; IF NOT FOUND THEN RAISE foreign_key_violation USING MESSAGE = 'Error on insert or update of $1' , DETAIL = ifelse(`$3', `', `', '`Key ($3) = (''` || NEW.$3 || ''`): ''` || ') 'Value ($2) = (' || NEW.$2 || ') is not a BIOGRAPHY_DATA.AnimID'; RETURN NULL; END IF; END IF; RETURN NULL; END; $$; SELECT '$1__$2_animid_refd_func' AS function; CREATE OR REPLACE FUNCTION $1__$2_animid_refd_func() RETURNS trigger LANGUAGE plpgsql gmi_function_set_search_path AS $$ -- Function for BIOGRAPHY_DATA delete to check for dangling -- references from $1.$2. -- -- GPL_notice(` --', `2012', `The Meme Factory, Inc. http://www.meme.com/') -- -- Remarks: -- Fired on transaction commit so that we can replace the content -- of BIOGRAPHY_DATA in a single transaction. -- BEGIN -- See if the BIOGRAPHY_DATA row still exists. PERFORM 1 FROM biography_data WHERE biography_data.animid = OLD.animid; IF FOUND THEN -- The deleted row was subsequently re-inserted. -- Nothing to do. RETURN NULL; END IF; -- The deleted Animid must not exist on $1. PERFORM 1 FROM $1 WHERE $1.$2 = OLD.animid LIMIT 1; IF FOUND THEN RAISE foreign_key_violation USING MESSAGE = 'Error on commit of BIOGRAPHY_DATA after delete' , DETAIL = 'Key (AnimID) = (' || OLD.animid || ') is still referenced by $1.$2'; RETURN NULL; END IF; RETURN NULL; END; $$; SELECT 'a0_$1__$2_animid_ref_trigger' AS trigger; CREATE TRIGGER a0_$1__$2_animid_ref_trigger AFTER INSERT OR UPDATE ON $1 FOR EACH ROW EXECUTE PROCEDURE $1__$2_animid_ref_func(); SELECT 'a0_$1__$2_animid_refd_trigger' AS trigger; CREATE CONSTRAINT TRIGGER a0_$1__$2_animid_refd_trigger AFTER DELETE ON biography_data DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE $1__$2_animid_refd_func(); ])dnl changequote(`,')dnl See above -- Done defining macros. divert`'dnl ]}])dnl End of ifdef over the whole file.