From 9690b0990b4770d0c4cb7994882c056d011c3fb4 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Tue, 9 Jun 2026 00:33:25 +0000 Subject: [PATCH] Create, document, index, and trigger LOCATIONS_B --- db/schemas/lib/triggers/Makefile | 3 +- .../lib/triggers/create/biography_data.m4 | 136 +++++++- db/schemas/lib/triggers/create/follows.m4 | 73 +++++ db/schemas/lib/triggers/create/locations_b.m4 | 258 +++++++++++++++ db/schemas/lib/triggers/drop/locations_b.m4 | 24 ++ db/schemas/sokwedb/indexes/Makefile | 2 +- .../sokwedb/indexes/create/locations_b.m4 | 55 ++++ .../sokwedb/indexes/drop/locations_b.m4 | 35 ++ db/schemas/sokwedb/tables/Makefile | 3 +- .../sokwedb/tables/create/locations_b.m4 | 78 +++++ doc/src/epilog.inc.m4 | 32 ++ doc/src/tables.m4 | 1 + doc/src/tables/locations_b.m4 | 305 ++++++++++++++++++ include/limits.m4 | 19 ++ 14 files changed, 1020 insertions(+), 4 deletions(-) create mode 100644 db/schemas/lib/triggers/create/locations_b.m4 create mode 100644 db/schemas/lib/triggers/drop/locations_b.m4 create mode 100644 db/schemas/sokwedb/indexes/create/locations_b.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/locations_b.m4 create mode 100644 db/schemas/sokwedb/tables/create/locations_b.m4 create mode 100644 doc/src/tables/locations_b.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 450db80..9c84125 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -43,7 +43,8 @@ ORDER := comm_ids \ swelling_sources \ humans \ species_present \ - repro_states + repro_states \ + locations_b DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index a351f71..06b2e57 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -561,7 +561,141 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END; - -- SWELLING_SOURCES + -- LOCATIONS_B + DECLARE + a_lbid locations_b.lbid%TYPE; + a_time locations_b.time%TYPE; + a_x locations_b.x%TYPE; + a_y locations_b.y%TYPE; + a_elevation locations_b.elevation%TYPE; + a_mapseq locations_b.mapseq%TYPE; + a_meterstonextmapseq locations_b.meterstonextmapseq%TYPE; + a_commid locations_b.commid%TYPE; + a_follownum locations_b.follownum%TYPE; + a_origin locations_b.origin%TYPE; + a_notes locations_b.notes%TYPE; + a_entered locations_b.entered%TYPE; + + BEGIN + -- Cannot have a record of being at a map location before + -- the individual was studied. + IF NEW.entrydate <> OLD.entrydate THEN + SELECT locations_b.lbid, locations_b.time, locations_b.x, locations_b.y + , locations_b.elevation, locations_b.mapseq + , locations_b.meterstonextmapseq, locations_b.commid + , locations_b.follownum, locations_b.origin, locations_b.notes + , locations_b.entered + INTO a_lbid , a_time , a_x , a_y + , a_elevation , a_mapseq + , a_meterstonextmapseq , a_commid + , a_follownum , a_origin , a_notes + , a_entered + FROM locations_b + WHERE locations_b.focal IS NOT DISTINCT FROM NEW.focal + AND locations_b.date IS NOT NULL + AND locations_b.date < NEW.entrydate + ORDER BY locations_b.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be at a map location' + || ' before they are under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (EntryDate) = (' + || NEW.entrydate + || ': Key (LOCATIONS_B.LBID) = (' + || a_lbid + || '), Value (LOCATIONS_B.Focal) = (' + || textualize(`a_focal') + || '), Value (LOCATIONS_B.Date) = (' + || textualize(`a_Date') + || '), Value (LOCATIONS_B.Time) = (' + || textualize(`a_time') + || '), Value (LOCATIONS_B.X) = (' + || textualize(`a_x') + || '), Value (LOCATIONS_B.y) = (' + || textualize(`a_y') + || '), Value (LOCATIONS_B.Elevation) = (' + || textualize(`a_elevation') + || '), Value (LOCATIONS_B.MapSeq) = (' + || textualize(`a_mapseq') + || '), Value (LOCATIONS_B.MetersTonextMapSeq) = (' + || textualize(`a_meterstonextmapseq') + || '), Value (LOCATIONS_B.CommID) = (' + || a_commid + || '), Value (LOCATIONS_B.Follownum) = (' + || a_follownum + || '), Value (LOCATIONS_B.Origin) = (' + || a_origin + || '), Value (LOCATIONS_B.Notes) = (' + || a_notes + || '), Value (LOCATIONS_B.Entered) = (' + || a_entered + || ')'; + END IF; + END IF; + + IF NEW.departdate <> OLD.departdate THEN + SELECT locations_b.lbid, locations_b.time, locations_b.x, locations_b.y + , locations_b.elevation, locations_b.mapseq + , locations_b.meterstonextmapseq, locations_b.commid + , locations_b.follownum, locations_b.origin, locations_b.notes + , locations_b.entered + INTO a_lbid , a_time , a_x , a_y + , a_elevation , a_mapseq + , a_meterstonextmapseq , a_commid + , a_follownum , a_origin , a_notes + , a_entered + FROM locations_b + WHERE locations_b.focal IS NOT DISTINCT FROM NEW.focal + AND locations_b.date IS NOT NULL + AND NEW.departdate < locations_b.date + ORDER BY locations_b.date; -- consistency + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA' + , DETAIL = 'An individual cannot be at the feeding station' + || ' after they are no longer under study' + || ': Key (Animid) = (' + || NEW.animid + || '), Value (DepartDate) = (' + || NEW.departdate + || ': Key (LOCATIONS_B.LBID) = (' + || a_lbid + || '), Value (LOCATIONS_B.Focal) = (' + || textualize(`a_focal') + || '), Value (LOCATIONS_B.Date) = (' + || textualize(`a_Date') + || '), Value (LOCATIONS_B.Time) = (' + || textualize(`a_time') + || '), Value (LOCATIONS_B.X) = (' + || textualize(`a_x') + || '), Value (LOCATIONS_B.y) = (' + || textualize(`a_y') + || '), Value (LOCATIONS_B.Elevation) = (' + || textualize(`a_elevation') + || '), Value (LOCATIONS_B.MapSeq) = (' + || textualize(`a_mapseq') + || '), Value (LOCATIONS_B.MetersTonextMapSeq) = (' + || textualize(`a_meterstonextmapseq') + || '), Value (LOCATIONS_B.CommID) = (' + || a_commid + || '), Value (LOCATIONS_B.Follownum) = (' + || a_follownum + || '), Value (LOCATIONS_B.Origin) = (' + || a_origin + || '), Value (LOCATIONS_B.Notes) = (' + || a_notes + || '), Value (LOCATIONS_B.Entered) = (' + || a_entered + || ')'; + END IF; + END IF; + END; + + +-- SWELLING_SOURCES DECLARE a_id swelling_sources.id%TYPE; a_date swelling_sources.date%TYPE; diff --git a/db/schemas/lib/triggers/create/follows.m4 b/db/schemas/lib/triggers/create/follows.m4 index 91bc93f..a8455bf 100644 --- a/db/schemas/lib/triggers/create/follows.m4 +++ b/db/schemas/lib/triggers/create/follows.m4 @@ -568,6 +568,79 @@ CREATE OR REPLACE FUNCTION follows_func () END IF; END IF; + IF TG_OP = 'INSERT' + OR NEW.focal <> OLD.focal + OR NEW.date <> OLD.date THEN + + DECLARE + a_lbid locations_b.lbid%TYPE; + a_time locations_b.time%TYPE; + a_x locations_b.x%TYPE; + a_y locations_b.y%TYPE; + a_elevation locations_b.elevation%TYPE; + a_mapseq locations_b.mapseq%TYPE; + a_meterstonextmapseq locations_b.meterstonextmapseq%TYPE; + a_commid locations_b.commid%TYPE; + a_follownum locations_b.follownum%TYPE; + a_origin locations_b.origin%TYPE; + a_notes locations_b.notes%TYPE; + a_entered locations_b.entered%TYPE; + + BEGIN + -- There cannot be a LOCATIONS_B row with the same Focal and Date + -- as the FOLLOWS row + SELECT locations_b.lbid, locations_b.time, locations_b.x, locations_b.y + , locations_b.elevation, locations_b.mapseq + , locations_b.meterstonextmapseq, locations_b.commid + , locations_b.follownum, locations_b.origin, locations_b.notes + , locations_b.entered + INTO a_lbid , a_time , a_x , a_y + , a_elevation , a_mapseq + , a_meterstonextmapseq , a_commid + , a_follownum , a_origin , a_notes + , a_entered + FROM locations_b + WHERE locations_b.focal IS NOT DISTINCT FROM NEW.focal + AND locations_b.date IS NOT DISTINCT FROM NEW.date; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of LOCATIONS_B' + , DETAIL = 'A LOCATIONS_B row cannot exist with the that shares' + || ' Focal and Date values with a FOLLOWS row, but' + || ' such a row exists' + || ': Key (LOCATIONS_B.LBID) = (' + || a_lbid + || '), Value (LOCATIONS_B.Focal) = (' + || textualize(`a_focal') + || '), Value (LOCATIONS_B.Date) = (' + || textualize(`a_Date') + || '), Value (LOCATIONS_B.Time) = (' + || textualize(`a_time') + || '), Value (LOCATIONS_B.X) = (' + || textualize(`a_x') + || '), Value (LOCATIONS_B.y) = (' + || textualize(`a_y') + || '), Value (LOCATIONS_B.Elevation) = (' + || textualize(`a_elevation') + || '), Value (LOCATIONS_B.MapSeq) = (' + || textualize(`a_mapseq') + || '), Value (LOCATIONS_B.MetersTonextMapSeq) = (' + || textualize(`a_meterstonextmapseq') + || '), Value (LOCATIONS_B.CommID) = (' + || a_commid + || '), Value (LOCATIONS_B.Follownum) = (' + || a_follownum + || '), Value (LOCATIONS_B.Origin) = (' + || a_origin + || '), Value (LOCATIONS_B.Notes) = (' + || a_notes + || '), Value (LOCATIONS_B.Entered) = (' + || a_entered + || ')'; + END IF; + END; + END IF; + RETURN NULL; END; $$; diff --git a/db/schemas/lib/triggers/create/locations_b.m4 b/db/schemas/lib/triggers/create/locations_b.m4 new file mode 100644 index 0000000..3477962 --- /dev/null +++ b/db/schemas/lib/triggers/create/locations_b.m4 @@ -0,0 +1,258 @@ +dnl Copyright (C) 2026 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 . +dnl +dnl Triggers for the locations_b table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +dnl Plpgsql fragment that checks that there is not a FOLLOWS row with +dnl the same Focal and Date as the new row. +dnl +dnl Syntax: _check_no_related_follow() +dnl +changequote({,}) +define({_check_no_related_follow}, {dnl + -- Cannot have a FOLLOWS row related on Focal and Date + DECLARE + a_fid follows.fid%TYPE; + + BEGIN + SELECT follows.fid + INTO a_fid + FROM follows + WHERE follows.animid IS NOT DISTINCT FROM NEW.focal + AND follows.date IS NOT DISTINCT FROM NEW.date; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of LOCATIONS_B' + , DETAIL = 'A FOLLOWS row cannot exist that has the same Focal' + || ' and same Date as a LOCATIONS_B row, but such' + || ' a row exists on FOLLOWS' + || ': Key (LBID) = (' + || NEW.lbid + || '), Value (Focal) = (' + || textualize(`NEW.focal') + || '), Value (Date) = (' + || textualize(`NEW.Date') + || '), Value (Time) = (' + || textualize(`NEW.time') + || '), Value (X) = (' + || textualize(`NEW.x') + || '), Value (y) = (' + || textualize(`NEW.y') + || '), Value (Elevation) = (' + || textualize(`NEW.elevation') + || '), Value (MapSeq) = (' + || textualize(`NEW.mapseq') + || '), Value (MetersTonextMapSeq) = (' + || textualize(`NEW.meterstonextmapseq') + || '), Value (CommID) = (' + || NEW.commid + || '), Value (Follownum) = (' + || NEW.follownum + || '), Value (Origin) = (' + || NEW.origin + || '), Value (Notes) = (' + || NEW.notes + || '), Value (Entered) = (' + || NEW.entered + || '), Key (FOLLOWS.FID) = (' + || a_fid + || ')'; + END IF; + END; +}) +changequote(`,') + + +RAISE INFO 'locations_b_func'; +CREATE OR REPLACE FUNCTION locations_b_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for locations_b insert and update triggers + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + + IF TG_OP = 'UPDATE' THEN + cannot_change(`LOCATIONS_B', `LBID') + END IF; + + -- Cannot have a follow date before the individual was studied + DECLARE + this_entrydate biography_data.entrydate%TYPE; + + BEGIN + SELECT biography_data.entrydate + INTO this_entrydate + FROM biography_data + WHERE biography_data.animid IS NOT DISTINCT FROM NEW.focal + AND biography_data.entrydate IS NOT NULL + AND biography_data.entrypdate > NEW.date; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of LOCATIONS_B' + , DETAIL = 'An individual cannot be followed before they are' + || ' studied' + || ': Key (LBID) = (' + || NEW.lbid + || '), Value (Focal) = (' + || textualize(`NEW.focal') + || '), Value (Date) = (' + || textualize(`NEW.Date') + || '), Value (Time) = (' + || textualize(`NEW.time') + || '), Value (X) = (' + || textualize(`NEW.x') + || '), Value (y) = (' + || textualize(`NEW.y') + || '), Value (Elevation) = (' + || textualize(`NEW.elevation') + || '), Value (MapSeq) = (' + || textualize(`NEW.mapseq') + || '), Value (MetersTonextMapSeq) = (' + || textualize(`NEW.meterstonextmapseq') + || '), Value (CommID) = (' + || NEW.commid + || '), Value (Follownum) = (' + || NEW.follownum + || '), Value (Origin) = (' + || NEW.origin + || '), Value (Notes) = (' + || NEW.notes + || '), Value (Entered) = (' + || NEW.entered + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || this_entrydate + || ')'; + END IF; + END; + + -- Cannot have a follow date after the study of the individual has stopped + DECLARE + this_departdate biography_data.departdate%TYPE; + + BEGIN + SELECT biography_data.departdate + INTO this_departdate + FROM biography_data + WHERE biography_data.animid IS NOT DISTINCT FROM NEW.focal + AND NEW.date IS NOT NULL + AND NEW.DATE > biography_data.departdate; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of LOCATIONS_B' + , DETAIL = 'An individual cannot be followed when they are no' + || ' longer under study' + || ': Key (LBID) = (' + || NEW.lbid + || '), Value (Focal) = (' + || textualize(`NEW.focal') + || '), Value (Date) = (' + || textualize(`NEW.Date') + || '), Value (Time) = (' + || textualize(`NEW.time') + || '), Value (X) = (' + || textualize(`NEW.x') + || '), Value (y) = (' + || textualize(`NEW.y') + || '), Value (Elevation) = (' + || textualize(`NEW.elevation') + || '), Value (MapSeq) = (' + || textualize(`NEW.mapseq') + || '), Value (MetersTonextMapSeq) = (' + || textualize(`NEW.meterstonextmapseq') + || '), Value (CommID) = (' + || NEW.commid + || '), Value (Follownum) = (' + || NEW.follownum + || '), Value (Origin) = (' + || NEW.origin + || '), Value (Notes) = (' + || NEW.notes + || '), Value (Entered) = (' + || NEW.entered + || '), Value (BIOGRAPHY_DATA.DepartDate) = (' + || this_departdate + || ')'; + END IF; + END; + + IF TG_OP = 'INSERT' + AND NEW.focal IS NOT NULL THEN + + _check_no_related_follow() + END IF; + + RETURN NULL; + END; +$$; + + +RAISE INFO 'locations_b_update_commit_func'; +CREATE OR REPLACE FUNCTION locations_b_update_commit_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + BEGIN + -- Function for locations_b update trigger fired on transaction commit + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + -- + -- Remarks: + -- In order to be able to delete a FOLLOWS row that has related + -- LOCATIONS_B rows, the FID must be removed from the LOCATIONS_B + -- row, because of the referential constraint, and then the + -- FOLLOWS row can be deleted. But removing a FID means adding + -- Focal and Date values, and we don't want these when there's + -- a related FOLLOWS row, which still exists at the time the + -- FID is removed and Focal and Date added. So, wait to check + -- for a related FOLLOWS row until after transaction commit. + + IF (NEW.focal IS DISTINCT FROM OLD.focal + OR NEW.date IS DISTINCT FROM OLD.date) + AND NEW.focal IS NOT NULL THEN + + _check_no_related_follow() + END IF; + + RETURN NULL; + END; +$$; + +RAISE INFO 'locations_b_trigger'; +CREATE TRIGGER locations_b_trigger + AFTER INSERT OR UPDATE + ON locations_b FOR EACH ROW + EXECUTE PROCEDURE locations_b_func(); + +RAISE INFO 'locations_b_update_commit_trigger'; +CREATE CONSTRAINT TRIGGER locations_b_update_commit_trigger + AFTER INSERT + ON locations_b + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE PROCEDURE locations_b_update_commit_func(); diff --git a/db/schemas/lib/triggers/drop/locations_b.m4 b/db/schemas/lib/triggers/drop/locations_b.m4 new file mode 100644 index 0000000..b6a2120 --- /dev/null +++ b/db/schemas/lib/triggers/drop/locations_b.m4 @@ -0,0 +1,24 @@ +dnl Copyright (C) 2025 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 . +dnl +dnl Drop triggers for locations_b table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS locations_b_func() CASCADE; +DROP FUNCTION IF EXISTS locations_b_update_commit_func() CASCADE; diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index a1bd3a9..66ead0a 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -23,7 +23,7 @@ ORDER := biography_data biography_log comm_membs comm_memb_log \ follows follow_observers follow_studies events roles arrivals \ swelling_sources swelling_states aggression_event_log sightings \ aggressions food_events groomings attendance \ - arrivals_a species_present repro_states + arrivals_a species_present repro_states locations_b ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/locations_b.m4 b/db/schemas/sokwedb/indexes/create/locations_b.m4 new file mode 100644 index 0000000..8cb5f68 --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/locations_b.m4 @@ -0,0 +1,55 @@ +dnl Copyright (C) 2026 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 . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + + +CREATE UNIQUE INDEX IF NOT EXISTS + "On LOCATIONS_B, Date + Focal + Time must be unique" + ON locations_b + (date, focal, time) + WHERE date IS NOT NULL + AND focal IS NOT NULL; + +CREATE UNIQUE INDEX IF NOT EXISTS + "On LOCATIONS_B, FID + Time must be unique" + ON locations_b + (date, focal, time) + WHERE date IS NOT NULL + AND focal IS NOT NULL; + +CREATE INDEX IF NOT EXISTS locations_b_fid ON locations_b + (fid) + WHERE fid IS NOT NULL; +CREATE INDEX IF NOT EXISTS locations_b_focal ON locations_b + (focal) + WHERE focal IS NOT NULL; +CREATE INDEX IF NOT EXISTS locations_b_x ON locations_b + (x) + WHERE x IS NOT NULL; +CREATE INDEX IF NOT EXISTS locations_b_y ON locations_b + (y) + WHERE y IS NOT NULL; +CREATE INDEX IF NOT EXISTS locations_b_mapseq ON locations_b + (mapseq) + WHERE mapseq IS NOT NULL; +CREATE INDEX IF NOT EXISTS locations_b_commid ON locations_b + (commid); diff --git a/db/schemas/sokwedb/indexes/drop/locations_b.m4 b/db/schemas/sokwedb/indexes/drop/locations_b.m4 new file mode 100644 index 0000000..97a5321 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/locations_b.m4 @@ -0,0 +1,35 @@ +dnl Copyright (C) 2026 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 . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + + +DROP INDEX IF EXISTS + "On LOCATIONS_B, Date + Focal + Time must be unique"; +DROP INDEX IF EXISTS + "On LOCATIONS_B, FID + Time must be unique"; + +DROP INDEX IF EXISTS follows_fid; +DROP INDEX IF EXISTS follows_focal; +DROP INDEX IF EXISTS follows_x; +DROP INDEX IF EXISTS follows_y; +DROP INDEX IF EXISTS follows_mapseq; +DROP INDEX IF EXISTS follows_commid; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 881008f..ab4cd49 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -43,7 +43,8 @@ ORDER := biography_data \ groomings \ groom_scans_b \ arrivals_a \ - repro_states + repro_states \ + locations_b ## ## CAUTION: This Makefile is not designed to be run directly. It is normally ## invoked by another Makefile. diff --git a/db/schemas/sokwedb/tables/create/locations_b.m4 b/db/schemas/sokwedb/tables/create/locations_b.m4 new file mode 100644 index 0000000..f00c7df --- /dev/null +++ b/db/schemas/sokwedb/tables/create/locations_b.m4 @@ -0,0 +1,78 @@ +dnl Copyright (C) 2026 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 . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`tablemacros.m4')dnl +include(`grants.m4')dnl + + +CREATE TABLE locations_b ( + key_column(`LOCATIONS_B', `LBID', INTEGER) + ,fid INTEGER + REFERENCES follows + ,animid_type_column(`focal', `Focal', `NULL') + ,date DATE NOT NULL + CONSTRAINT "Date cannot be before sdb_min_follow_date" + CHECK (date IS NULL + OR date >= 'sdb_min_follow_date') + ,time TIME NOT NULL + CONSTRAINT "Time cannot be before sdb_min_event_start" + CHECK (time >= 'sdb_min_event_start') + CONSTRAINT "Time cannot be after sdb_max_event_stop" + CHECK (time >= 'sdb_max_event_stop') + ,x INTEGER + CONSTRAINT "X cannot be less than sdb_min_x_utm" + CHECK (x IS NULL + OR x >= sdb_min_x_utm) + CONSTRAINT "X cannot be more than than sdb_max_x_utm" + CHECK (x IS NULL + OR x <= sdb_max_x_utm) + ,y INTEGER + CONSTRAINT "Y cannot be less than sdb_min_y_utm" + CHECK (y IS NULL + OR y >= sdb_min_y_utm) + CONSTRAINT "Y cannot be more than than sdb_max_y_utm" + CHECK (y IS NULL + OR y <= sdb_max_y_utm) + ,elevation INTEGER + CONSTRAINT "Elevation cannot be less than sdb_min_elevation" + CHECK (elevation IS NULL + OR elevation >= sdb_min_elevation) + CONSTRAINT "Elevation cannot be more than than sdb_max_elevation" + CHECK (elevation IS NULL + OR elevation <= sdb_max_elevation) + ,mapseq INTEGER + ,meterstonextmapseq INTEGER + nonnegative_check(`MetersToNextMapSeq') + ,commid TEXT NOT NULL + REFERENCES comm_ids + ,follownum TEXT NOT NULL + ,origin TEXT NOT NULL + ,notes TEXT NOT NULL + notonlyspaces_check(`Notes') + ,entered TEXT NOT NULL + REFERENCES people + + null_xor_null(`Focal', `FID') + null_xor_null(`Date', `FID') + null_iff_null(`Date', `Focal') +); + +grant_priv(`LOCATIONS_B') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 0156e3d..c5f8c5e 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -427,6 +427,38 @@ sdb_generated_rst()dnl .. |HUMANS.NonResearchers| replace:: :ref:`NonResearchers ` +.. |LOCATIONS_B| replace:: :ref:`LOCATIONS_B ` +.. |LOCATIONS_B.LBID| replace:: + :ref:`LBID ` +.. |LOCATIONS_B.FID| replace:: + :ref:`FID ` +.. |LOCATIONS_B.Focal| replace:: + :ref:`Focal ` +.. |LOCATIONS_B.Date| replace:: + :ref:`Date ` +.. |LOCATIONS_B.Time| replace:: + :ref:`Time ` +.. |LOCATIONS_B.X| replace:: + :ref:`X ` +.. |LOCATIONS_B.Y| replace:: + :ref:`Y ` +.. |LOCATIONS_B.Elevation| replace:: + :ref:`Elevation ` +.. |LOCATIONS_B.MapSeq| replace:: + :ref:`MapSeq ` +.. |LOCATIONS_B.MetersToNextMapSeq| replace:: + :ref:`MetersToNextMapSeq ` +.. |LOCATIONS_B.CommID| replace:: + :ref:`CommID ` +.. |LOCATIONS_B.FollowNum| replace:: + :ref:`FollowNum ` +.. |LOCATIONS_B.Origin| replace:: + :ref:`Origin ` +.. |LOCATIONS_B.Notes| replace:: + :ref:`Notes ` +.. |LOCATIONS_B.Entered| replace:: + :ref:`Entered ` + .. |LOCATION_ORIGINS| replace:: :ref:`LOCATION_ORIGINS ` .. |LOCATION_ORIGINS.Code| replace:: diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 6f01a3e..6dfb480 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -50,6 +50,7 @@ and are therefore the result of at least a rudimentary analytical process. tables/groom_scans_b.rst tables/groomings.rst tables/humans.rst + tables/locations_b.rst tables/roles.rst tables/non_brec_sighting_sources.rst tables/species_present.rst diff --git a/doc/src/tables/locations_b.m4 b/doc/src/tables/locations_b.m4 new file mode 100644 index 0000000..2880db6 --- /dev/null +++ b/doc/src/tables/locations_b.m4 @@ -0,0 +1,305 @@ +.. Copyright (C) 2026 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 . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + +.. _LOCATIONS_B: + +LOCATIONS_B (spatial LOCATIONS from B records) +---------------------------------------------- + +.. |LOCATIONS_B_summary| replace:: + Contains one row for each moment in time the spatial location of a + follow's focal was recorded; even when there is no other + information on the follow when there is no |FOLLOWS| record for + the focal on the given day. + +|LOCATIONS_B_summary| +To support recording spatial information when there is no follow +information there are columns in this table for the +|LOCATIONS_B.Focal| and the follow |LOCATIONS_B.Date|. + +There must either be a related follow row or there must be information +in the |LOCATIONS_B.Focal| and |LOCATIONS_B.Date| columns, but there +cannot be both. +This means the the following rules are enforced: +sdb_null_xor_null(*m4[|LOCATIONS_B.Focal|]m4*, *m4[|LOCATIONS_B.FID|]m4*) +sdb_null_xor_null(*m4[|LOCATIONS_B.Date|]m4*, *m4[|LOCATIONS_B.FID|]m4*) +sdb_null_iff_null(*m4[|LOCATIONS_B.Date|]m4*, *m4[|LOCATIONS_B.Focal|]m4*) + +Further, follow information can be recorded in only one of SokweDB's +tables, in one row. +This means that there cannot be a row on |FOLLOWS| with focal and date +values that match a LOCATIONS_B row's focal and date values. +(|transaction commit|) + +Any given follow can locate the focal in only one place at any given +time.\ [#f1]_ +This means two things. +The combination of |LOCATIONS_B.Focal|, |LOCATIONS_B.Date|, and +|LOCATIONS_B.Time| must be unique. +And, the combination of |LOCATIONS_B.FID| and |LOCATIONS_B.Time| must +be unique. + +The system will generate a warning when there is no related follow, +when the |LOCATIONS_B.FID| column is |null|. + +The |LOCATIONS_B.Date| must be during a period when the focal individual +was under study, on or after the focal's |BIOGRAPHY_DATA|.\ +|BIOGRAPHY_DATA.EntryDate| and on or before the focal's +|BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|. + +The time of the spatial recording should occur when the focal was +under observation. +The system will generate a warning when a LOCATIONS_B row does not +have a |ARRIVALS| related row that records the presence of the focal +at the time the spatial observation occurred. +This means that a warning will be generated when there is a non-|null| +|LOCATIONS_B.FID| value, but there is no related |EVENTS| row where +the |EVENTS.Behavior| code is ``sdb_arrival`` and the |EVENTS|.\ +|EVENTS.Start| value is less than or equal to the |LOCATIONS_B.Time| +value and the |EVENTS|.\ |EVENTS.Stop| value is greater than or equal +to the |LOCATIONS_B.Time| value, and the event has a related |ROLES| +row where the |ROLES|.\ |ROLES.Participant| has the same value as the +|LOCATIONS_B.Focal| column. + +Any given spatial location is recorded in more than one column. +The data contained in these columns must be consistent. +This means the following 2 conditions must be met. +sdb_null_iff_null(*m4[|LOCATIONS_B.Y|]m4*, *m4[|LOCATIONS_B.X|]m4*) +If |LOCATIONS_B.MapSeq| is |null| then +|LOCATIONS_B.MetersToNextMapSeq| must also be |null|. + +.. contents:: + :depth: 2 + + +.. _LOCATIONS_B.LBID: + +LBID (spatial Location B-record ID) +``````````````````````````````````` + +.. |LOCATIONS_B.LBID_summary| replace:: |idcol| + +|LOCATIONS_B.LBID_summary| |notnull| + + +.. _LOCATIONS_B.FID: + +FID (Follow ID) +``````````````` + +.. |LOCATIONS_B.FID_summary| replace:: + The |FOLLOWS|.\ |FOLLOWS.FID| that identifies the follow during + which the spatial position of the focal was recorded. + +|LOCATIONS_B.FID_summary| +This column must be |null| when there is no information on the follow +other than the recorded spatial placement of the focal. + + +.. _LOCATIONS_B.Focal: + +Focal (Focal animal id) +``````````````````````` + +.. |LOCATIONS_B.Focal_summary| replace:: + The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| identifying the + focal individual. + +|LOCATIONS_B.Focal_summary| +This column must be |null| when there is a related |FOLLOWS| row. + + +.. _LOCATIONS_B.Date: + +Date +```` + +.. |LOCATIONS_B.Date_summary| replace:: + The date the follow was conducted. + +|LOCATIONS_B.Date_summary| +This date may not be before ``sdb_min_follow_date``. +This column must be |null| when there is a related |FOLLOWS| row. + + +.. _LOCATIONS_B.Time: + +Time +```` + +.. |LOCATIONS_B.Time_summary| replace:: + The time the follow was conducted. + +|LOCATIONS_B.Time_summary| +The value of this column cannot be before ``sdb_min_event_start`` or +after ``sdb_max_event_stop``. + + + +.. _LOCATIONS_B.X: + +X (X utm coordinate) +```````````````````` + +.. |LOCATIONS_B.X_summary| replace:: + The X coodinate of the (UTM WGS84 encoded) location. + An integer beween ``sdb_min_x_utm`` and ``sdb_max_x_utm``, inclusive. + +|LOCATIONS_B.X_summary| +This value may be |null| when the location was recorded in map +traversal sequences. + + +.. _LOCATIONS_B.Y: + +Y (Y utm coordinate) +```````````````````` + +.. |LOCATIONS_B.Y_summary| replace:: + The Y coodinate of the (UTM WGS84 encoded) location. + An integer beween ``sdb_min_y_utm`` and ``sdb_max_y_utm`` inclusive. + +|LOCATIONS_B.Y_summary| +This value may be |null| when the location was recorded in map +traversal sequences. + + +.. _LOCATIONS_B.Elevation: + +Elevation +````````` + +.. |LOCATIONS_B.Elevation_summary| replace:: + The elevation of the location, in meters. + An integer beween ``sdb_min_elevation_utm`` and + ``sdb_max_elevation_utm`` inclusive. + +|LOCATIONS_B.Elevation_summary| +This value may be |null| when the location was recorded in map +traversal sequences. + + +.. _LOCATIONS_B.MapSeq: + +MapSeq +`````` + +.. |LOCATIONS_B.MapSeq_summary| replace:: + The number of the map where the focal was located, where maps are + numbered sequentially within the series of maps traversed during + the follow. + +|LOCATIONS_B.MapSeq_summary| +This value may be |null| when the location was not recorded in map +traversal sequences. + + + +.. _LOCATIONS_B.MetersToNextMapSeq: + +MetersToNextMapSeq +`````````````````` + +.. |LOCATIONS_B.MetersToNextMapSeq_summary| replace:: + The number of meters to the next map sequence number within the follow. + +|LOCATIONS_B.MetersToNextMapSeq_summary| +This number was calculated in various ways over time. + +|nonnegative| +This value may be |null| when the location was not recorded in map +traversal sequences. + + +.. _LOCATIONS_B.CommID: + +CommID +`````` + +.. |LOCATIONS_B.CommID_summary| replace:: + + A code for the community the observers are associated with and + intended to follow; the |COMM_IDS|.\ |COMM_IDS.CommID| of the + community. + This is not necessarily the community the focal is a member of, + although it usually is. + +|LOCATIONS_B.CommID_summary| |notnull| + + +.. _LOCATIONS_B.FollowNum: + +FollowNum +````````` + +.. |LOCATIONS_B.FollowNum_summary| replace:: + A text string used for checking. + +|LOCATIONS_B.FollowNum_summary| +|notonlyspaces| |notnull| + + + +.. _LOCATIONS_B.Origin: + +Origin +`````` + +.. |LOCATIONS_B.Origin_summary| replace:: + A code indicating the source of the location information. + A |LOCATION_ORIGINS|.\ |LOCATION_ORIGINS.Code| value. + +|LOCATIONS_B.Origin_summary| +|notnull| + + + +.. _LOCATIONS_B.Notes: + +Notes +````` + +.. |LOCATIONS_B.Notes_summary| replace:: + Free form textual notes on the focal's spatial placement. + +|LOCATIONS_B.Notes_summary| |notonlyspaces| |notnull| + + +.. _LOCATIONS_B.Entered: + +Entered +``````` + +.. |LOCATIONS_B.Entered_summary| replace:: + The person who recorded the location. + A |PEOPLE|.\ |PEOPLE.Person| value. + +|LOCATIONS_B.Entered_summary| +|notnull| + + +.. rubric:: Footnotes + +.. [#f1] + With the caveat that any one row in LOCATIONS_B has two ways to + record a focal's location, by map sequence number and by UTM + coordinates. + These two values are not required to be in-sync. diff --git a/include/limits.m4 b/include/limits.m4 index d48fee4..104458a 100644 --- a/include/limits.m4 +++ b/include/limits.m4 @@ -88,5 +88,24 @@ define(`sdb_max_foods', `4') dnl The maximum banana count in ATTENDANCE define(`sdb_max_bananas', `12') +dnl +dnl LOCATIONS_B +dnl + +dnl The minimum X UTM coordinate value +define(`sdb_min_x_utm', `79246') +dnl The maximum X UTM coordinate value +define(`sdb_max_x_utm', `9486544') + +dnl The minimum Y UTM coordinate value +define(`sdb_min_y_utm', `791268') +dnl The maximum Y UTM coordinate value +define(`sdb_max_y_utm', `9503828') + +dnl The minimum elevation value +define(`sdb_min_elevation', `439') +dnl The maximum elevation value +define(`sdb_max_elevation', `20000') + divert(`0')dnl Output with m4 again ]}])dnl End of ifdef over the whole file. -- 2.34.1