From a9cbc0125b95c7931cd2577f7d26405ff87659a3 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Tue, 16 Jun 2026 17:51:01 +0000 Subject: [PATCH] Drop LOCATIONS_B, replace it with LOCATIONS_GPS and LOCATIONS_MAP Integrate the 2 new tables into the WATCHES system. --- db/schemas/lib/triggers/Makefile | 1 - .../lib/triggers/create/biography_data.m4 | 136 +-------- db/schemas/lib/triggers/create/events.m4 | 137 ++++++--- db/schemas/lib/triggers/create/locations_b.m4 | 289 ------------------ db/schemas/lib/triggers/create/roles.m4 | 14 +- db/schemas/lib/triggers/create/watches.m4 | 73 ----- db/schemas/sokwedb/indexes/Makefile | 2 +- db/schemas/sokwedb/indexes/create/events.m4 | 12 + .../locations_gps.m4} | 19 +- .../{locations_b.m4 => locations_map.m4} | 34 +-- db/schemas/sokwedb/indexes/create/watches.m4 | 5 +- db/schemas/sokwedb/indexes/drop/events.m4 | 5 + .../sokwedb/indexes/drop/locations_gps.m4 | 28 ++ .../indexes/drop/locations_map.m4} | 19 +- db/schemas/sokwedb/indexes/drop/watches.m4 | 2 +- db/schemas/sokwedb/tables/Makefile | 3 +- db/schemas/sokwedb/tables/create/events.m4 | 4 + .../{locations_b.m4 => locations_gps.m4} | 34 +-- .../sokwedb/tables/create/locations_map.m4 | 46 +++ doc/src/epilog.inc.m4 | 68 +++-- doc/src/tables.m4 | 3 +- doc/src/tables/events.m4 | 148 +++++++++ doc/src/tables/locations_gps.m4 | 158 ++++++++++ doc/src/tables/locations_map.m4 | 147 +++++++++ doc/src/tables/watches.m4 | 34 ++- include/global_constants.m4 | 7 +- 26 files changed, 770 insertions(+), 658 deletions(-) delete mode 100644 db/schemas/lib/triggers/create/locations_b.m4 rename db/schemas/sokwedb/indexes/{drop/locations_b.m4 => create/locations_gps.m4} (70%) rename db/schemas/sokwedb/indexes/create/{locations_b.m4 => locations_map.m4} (52%) create mode 100644 db/schemas/sokwedb/indexes/drop/locations_gps.m4 rename db/schemas/{lib/triggers/drop/locations_b.m4 => sokwedb/indexes/drop/locations_map.m4} (66%) rename db/schemas/sokwedb/tables/create/{locations_b.m4 => locations_gps.m4} (71%) create mode 100644 db/schemas/sokwedb/tables/create/locations_map.m4 create mode 100644 doc/src/tables/locations_gps.m4 create mode 100644 doc/src/tables/locations_map.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index d7af9d8..227523b 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -44,7 +44,6 @@ ORDER := comm_ids \ humans \ species_present \ repro_states \ - locations_b \ pantgrunts DROP_EXISTING := true diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 1d74698..b211bef 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -505,141 +505,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () END IF; END; - -- 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 + -- SWELLING_SOURCES DECLARE a_id swelling_sources.id%TYPE; a_date swelling_sources.date%TYPE; diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index fa3c969..d2b0b09 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -87,53 +87,104 @@ CREATE OR REPLACE FUNCTION events_func () ,'sdb_pantgrunt') THEN -- The event must be related to a follow. watch_type := 'sdb_follow'; - ELSE - -- NEW.behavior is sdb_arrival_a or sdb_groom_scan_a + ELSIF NEW.behavior IN ('sdb_arrival_a' + ,'sdb_groom_scan_a') THEN -- The event must be related to a feeding station attendance record. watch_type := 'sdb_attendance'; END IF; - SELECT watches.focal, watches.commid, watches.date, watches.type - , watches.notes - INTO a_focal, a_commid , a_date , a_type - , a_notes - FROM watches - WHERE watches.wid = NEW.wid - AND watches.type <> watch_type; - IF FOUND THEN - RAISE EXCEPTION integrity_constraint_violation USING - MESSAGE = 'Error on ' || TG_OP || ' of FOLLOW_OBSERVERS' - , DETAIL = 'Events with (Behavior) = (' - || NEW.behavior - || ') must be related to a WATCHES row with' - || ' (Type) = (' - || watch_type - || '): Key (EID) = (' - || NEW.eid - || '): Value (WID) = (' - || NEW.wid - || '): Value (Behavior) = (' - || NEW.behavior - || '), Value (Start) = (' - || NEW.start - || '), Value (Stop) = (' - || NEW.stop - || '), Value (Certainty) = (' - || NEW.certainty - || ') : Key (WATCHES.WID) = (' - || NEW.wid - || '), Value (WATCHES.Focal) = (' - || a_focal - || '), Value (WATCHES.Date) = (' - || a_date - || '), Value (WATCHES.Type) = (' - || a_type - || '), Value (WATCHES.CommID) = (' - || a_commid - || '), Value (WATCHES.Notes) = (' - || a_notes - || ')'; - END IF; + IF watch_type IS NOT NULL THEN + SELECT watches.focal, watches.commid, watches.date, watches.type + , watches.notes + INTO a_focal, a_commid , a_date , a_type + , a_notes + FROM watches + WHERE watches.wid = NEW.wid + AND watches.type <> watch_type; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of EVENTS' + , DETAIL = 'Events with (Behavior) = (' + || NEW.behavior + || ') must be related to a WATCHES row with' + || ' (Type) = (' + || watch_type + || '): Key (EID) = (' + || NEW.eid + || '): Value (WID) = (' + || NEW.wid + || '): Value (Behavior) = (' + || NEW.behavior + || '), Value (Start) = (' + || NEW.start + || '), Value (Stop) = (' + || NEW.stop + || '), Value (Certainty) = (' + || NEW.certainty + || ') : Key (WATCHES.WID) = (' + || NEW.wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.Type) = (' + || a_type + || '), Value (WATCHES.CommID) = (' + || a_commid + || '), Value (WATCHES.Notes) = (' + || a_notes + || ')'; + END IF; + END IF; + + IF NEW.behavior = 'sdb_gps' + OR NEW.behavior = 'sdb_map' THEN + -- The WATCHES row must be for a follow, if there is one, and if not + -- then for a location. + -- There can't exist a WATCHES row for a follow and a location for + -- the same individual for the same date, so this simplifies things. + SELECT watches.focal, watches.commid, watches.date, watches.type + , watches.notes + INTO a_focal, a_commid , a_date , a_type + , a_notes + FROM watches + WHERE watches.wid = NEW.wid + AND watches.type <> 'sdb_follow' + AND watches.type <> 'sdb_location'; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of EVENTS' + , DETAIL = 'Events with (Behavior) = (' + || NEW.behavior + || ') must be related to a WATCHES row with' + || ' (Type) = (sdb_follow) or (Type) = (sdb_location)' + || ': Key (EID) = (' + || NEW.eid + || '): Value (WID) = (' + || NEW.wid + || '): Value (Behavior) = (' + || NEW.behavior + || '), Value (Start) = (' + || NEW.start + || '), Value (Stop) = (' + || NEW.stop + || '), Value (Certainty) = (' + || NEW.certainty + || ') : Key (WATCHES.WID) = (' + || NEW.wid + || '), Value (WATCHES.Focal) = (' + || a_focal + || '), Value (WATCHES.Date) = (' + || a_date + || '), Value (WATCHES.Type) = (' + || a_type + || '), Value (WATCHES.CommID) = (' + || a_commid + || '), Value (WATCHES.Notes) = (' + || a_notes + || ')'; + END IF; + END IF; END; END IF; diff --git a/db/schemas/lib/triggers/create/locations_b.m4 b/db/schemas/lib/triggers/create/locations_b.m4 deleted file mode 100644 index 46742af..0000000 --- a/db/schemas/lib/triggers/create/locations_b.m4 +++ /dev/null @@ -1,289 +0,0 @@ -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 WATCHES row with -dnl the same Focal and Date as the new row. -dnl -dnl Syntax: _check_no_related_watch() -dnl -changequote({,}) -define({_check_no_related_watch}, {dnl - -- Cannot have a WATCHES row related on Focal and Date - DECLARE - a_wid watches.wid%TYPE; - - BEGIN - SELECT watches.wid - INTO a_wid - FROM watches - WHERE watches.animid IS NOT DISTINCT FROM NEW.focal - AND watches.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 WATCHES row cannot exist that has the same Focal' - || ' and same Date as a LOCATIONS_B row, but such' - || ' a row exists on WATCHES' - || ': 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 (WATCHES.WID) = (' - || a_wid - || ')'; - 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_watch() - END IF; - - changequote({,})dnl - person_active({LOCATIONS_B}, {EnteredBy}, - {'Key (LBID = (' - || NEW.lbid - || '): Value (WID) = (' - || textualize(NEW.wid) - || '): Value (AnimID) = (' - || textualize(NEW.animid) - || '): Value (Date) = (' - || textualize(NEW.date) - || '): Value (Time) = (' - || 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 - || ')'}) - changequote(`,')dnl - - 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 WATCHES row that has related - -- LOCATIONS_B rows, the WID must be removed from the LOCATIONS_B - -- row, because of the referential constraint, and then the - -- WATCHES row can be deleted. But removing a WID means adding - -- Focal and Date values, and we don't want these when there's - -- a related WATCHES row, which still exists at the time the - -- WID is removed and Focal and Date added. So, wait to check - -- for a related WATCHES 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_watch() - 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/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 6da736e..c21aee3 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -216,11 +216,14 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; -- There can be at most one related row on ROLES for arrival, feeding - -- station arrival, and food events + -- station arrival, food events, GPS map location events, and + -- map sequence map location events IF TG_OP = 'INSERT' AND (a_behavior = 'sdb_arrival' OR a_behavior = 'sdb_arrival_a' - OR a_behavior = 'sdb_food') THEN + OR a_behavior = 'sdb_food' + OR a_behavior = 'sdb_gps' + OR a_behavior = 'sdb_map') THEN DECLARE a_pid roles.pid%TYPE; a_role roles.role%TYPE; @@ -410,10 +413,13 @@ CREATE OR REPLACE FUNCTION roles_func () END IF; -- - -- The participant must be the focal for food events + -- The participant must be the focal for food events, for gps location + -- events, and for map location events -- IF TG_OP = 'INSERT' - AND a_behavior = 'sdb_food' THEN + AND (a_behavior = 'sdb_food' + OR a_behavior = 'sdb_gps' + OR a_behavior = 'sdb_map') THEN DECLARE a_focal watches.focal%TYPE; a_date watches.date%TYPE; diff --git a/db/schemas/lib/triggers/create/watches.m4 b/db/schemas/lib/triggers/create/watches.m4 index 27df462..153ba08 100644 --- a/db/schemas/lib/triggers/create/watches.m4 +++ b/db/schemas/lib/triggers/create/watches.m4 @@ -571,79 +571,6 @@ CREATE OR REPLACE FUNCTION watches_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 WATCHES 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 WATCHES 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/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index 659d22f..c122efa 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 \ watches 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 locations_b + arrivals_a species_present repro_states locations_gps locations_map ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/events.m4 b/db/schemas/sokwedb/indexes/create/events.m4 index 6a17b9d..7ee8300 100644 --- a/db/schemas/sokwedb/indexes/create/events.m4 +++ b/db/schemas/sokwedb/indexes/create/events.m4 @@ -21,6 +21,18 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +CREATE UNIQUE INDEX IF NOT EXISTS + "On EVENTS where (Behavior)=(sdb_gps), WID + Start must be unique" + ON events + (wid, start) + WHERE behavior = 'sdb_gps'; + +CREATE UNIQUE INDEX IF NOT EXISTS + "On EVENTS where (Behavior)=(sdb_map), WID + Start must be unique" + ON events + (wid, start) + WHERE behavior = 'sdb_map'; + CREATE INDEX IF NOT EXISTS events_wid ON events (wid); CREATE INDEX IF NOT EXISTS events_behavior ON events diff --git a/db/schemas/sokwedb/indexes/drop/locations_b.m4 b/db/schemas/sokwedb/indexes/create/locations_gps.m4 similarity index 70% rename from db/schemas/sokwedb/indexes/drop/locations_b.m4 rename to db/schemas/sokwedb/indexes/create/locations_gps.m4 index 1f5b3e1..a3e4584 100644 --- a/db/schemas/sokwedb/indexes/drop/locations_b.m4 +++ b/db/schemas/sokwedb/indexes/create/locations_gps.m4 @@ -22,14 +22,11 @@ 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, WID + Time must be unique"; - -DROP INDEX IF EXISTS locations_b_wid; -DROP INDEX IF EXISTS locations_b_focal; -DROP INDEX IF EXISTS locations_b_x; -DROP INDEX IF EXISTS locations_b_y; -DROP INDEX IF EXISTS locations_b_mapseq; -DROP INDEX IF EXISTS locations_b_commid; +CREATE INDEX IF NOT EXISTS locations_gps_x ON locations_gps + (x); +CREATE INDEX IF NOT EXISTS locations_gps_y ON locations_gps + (y); +CREATE INDEX IF NOT EXISTS locations_gps_commid ON locations_gps + (commid); +CREATE INDEX IF NOT EXISTS locations_gps_follownum ON locations_gps + (follownum); diff --git a/db/schemas/sokwedb/indexes/create/locations_b.m4 b/db/schemas/sokwedb/indexes/create/locations_map.m4 similarity index 52% rename from db/schemas/sokwedb/indexes/create/locations_b.m4 rename to db/schemas/sokwedb/indexes/create/locations_map.m4 index 99b89ab..c642f26 100644 --- a/db/schemas/sokwedb/indexes/create/locations_b.m4 +++ b/db/schemas/sokwedb/indexes/create/locations_map.m4 @@ -22,33 +22,9 @@ 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, WID + Time must be unique" - ON locations_b - (wid, time) - WHERE wid IS NOT NULL; - -CREATE INDEX IF NOT EXISTS locations_b_wid ON locations_b - (wid) - WHERE wid 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 +CREATE INDEX IF NOT EXISTS locations_map_mapseq ON locations_map + (mapseq); +CREATE INDEX IF NOT EXISTS locations_map_commid ON locations_map (commid); +CREATE INDEX IF NOT EXISTS locations_map_follownum ON locations_map + (follownum); diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index dbd50c4..83e30f6 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -22,10 +22,11 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl CREATE UNIQUE INDEX IF NOT EXISTS - "On WATCHES where (Type)=(sdb_follow), Date + Focal must be unique" + "(Type)=(sdb_follow) or (Type)=(sdb_follow) means Date + Focal must be unique" ON watches (date, focal) - WHERE type = 'sdb_follow'; + WHERE type = 'sdb_follow' + OR type = 'sdb_location'; CREATE UNIQUE INDEX IF NOT EXISTS "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique" diff --git a/db/schemas/sokwedb/indexes/drop/events.m4 b/db/schemas/sokwedb/indexes/drop/events.m4 index cffa127..3fd9cff 100644 --- a/db/schemas/sokwedb/indexes/drop/events.m4 +++ b/db/schemas/sokwedb/indexes/drop/events.m4 @@ -21,6 +21,11 @@ include(`copyright.m4')dnl include(`constants.m4')dnl include(`indexmacros.m4')dnl +DROP INDEX IF EXISTS + "On EVENTS where (Behavior)=(sdb_gps), WID + Start must be unique"; +DROP INDEX IF EXISTS + "On EVENTS where (Behavior)=(sdb_map), WID + Start must be unique"; + DROP INDEX IF EXISTS events_wid; DROP INDEX IF EXISTS events_behavior; diff --git a/db/schemas/sokwedb/indexes/drop/locations_gps.m4 b/db/schemas/sokwedb/indexes/drop/locations_gps.m4 new file mode 100644 index 0000000..5898245 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/locations_gps.m4 @@ -0,0 +1,28 @@ +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 locations_gps_x; +DROP INDEX IF EXISTS locations_gps_y; +DROP INDEX IF EXISTS locations_gps_commid; +DROP INDEX IF EXISTS locations_gps_follownum; diff --git a/db/schemas/lib/triggers/drop/locations_b.m4 b/db/schemas/sokwedb/indexes/drop/locations_map.m4 similarity index 66% rename from db/schemas/lib/triggers/drop/locations_b.m4 rename to db/schemas/sokwedb/indexes/drop/locations_map.m4 index b6a2120..426bbc6 100644 --- a/db/schemas/lib/triggers/drop/locations_b.m4 +++ b/db/schemas/sokwedb/indexes/drop/locations_map.m4 @@ -1,8 +1,8 @@ -dnl Copyright (C) 2025 The Meme Factory, Inc. http://www.karlpinc.com/ +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 by -dnl the Free Software Foundation, either version 3 of the License, or +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, @@ -13,12 +13,15 @@ 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 +dnl dnl m4 includes include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + -DROP FUNCTION IF EXISTS locations_b_func() CASCADE; -DROP FUNCTION IF EXISTS locations_b_update_commit_func() CASCADE; +DROP INDEX IF EXISTS locations_map_mapseq; +DROP INDEX IF EXISTS locations_map_commid; +DROP INDEX IF EXISTS locations_map_follownum; diff --git a/db/schemas/sokwedb/indexes/drop/watches.m4 b/db/schemas/sokwedb/indexes/drop/watches.m4 index c5fb7e7..edb43d2 100644 --- a/db/schemas/sokwedb/indexes/drop/watches.m4 +++ b/db/schemas/sokwedb/indexes/drop/watches.m4 @@ -23,7 +23,7 @@ include(`indexmacros.m4')dnl DROP INDEX IF EXISTS - "On WATCHES where (Type)=(sdb_follow), Date + Focal must be unique"; + "(Type)=(sdb_follow) or (Type)=(sdb_follow) means Date + Focal must be unique"; DROP INDEX IF EXISTS "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique"; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 6445706..3289b7e 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -44,7 +44,8 @@ ORDER := biography_data \ groom_scans_b \ arrivals_a \ repro_states \ - locations_b \ + locations_gps \ + locations_map \ pantgrunts ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/tables/create/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 index 70ce377..cb80e69 100644 --- a/db/schemas/sokwedb/tables/create/events.m4 +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -94,12 +94,16 @@ CREATE TABLE events ( _point_behavior_time(`sdb_groom_scan') _point_behavior_time(`sdb_groom_scan_a') _point_behavior_time(`sdb_pantgrunt') + _point_behavior_time(`sdb_gps') + _point_behavior_time(`sdb_map') _behavior_certain(`sdb_arrival_a') _behavior_certain(`sdb_groom_scan') _behavior_certain(`sdb_groom_scan_a') _behavior_certain(`sdb_other_species') _behavior_certain(`sdb_pantgrunt') + _behavior_certain(`sdb_gps') + _behavior_certain(`sdb_map') ); grant_priv(`EVENTS') diff --git a/db/schemas/sokwedb/tables/create/locations_b.m4 b/db/schemas/sokwedb/tables/create/locations_gps.m4 similarity index 71% rename from db/schemas/sokwedb/tables/create/locations_b.m4 rename to db/schemas/sokwedb/tables/create/locations_gps.m4 index 9595a7e..055f787 100644 --- a/db/schemas/sokwedb/tables/create/locations_b.m4 +++ b/db/schemas/sokwedb/tables/create/locations_gps.m4 @@ -23,28 +23,17 @@ include(`tablemacros.m4')dnl include(`grants.m4')dnl -CREATE TABLE locations_b ( - key_column(`LOCATIONS_B', `LBID', INTEGER) - ,wid INTEGER - REFERENCES watches - ,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 +CREATE TABLE locations_gps ( + eid INTEGER NOT NULL + REFERENCES events + ,x INTEGER NOT NULL 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 + ,y INTEGER NOT NULL CONSTRAINT "Y cannot be less than sdb_min_y_utm" CHECK (y IS NULL OR y >= sdb_min_y_utm) @@ -58,21 +47,18 @@ CREATE TABLE locations_b ( 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 + notonlyspaces_check(`FollowNum') ,origin TEXT NOT NULL + REFERENCES location_origins ,notes TEXT NOT NULL notonlyspaces_check(`Notes') ,entered TEXT NOT NULL REFERENCES people - - null_xor_null(`Focal', `WID') - null_xor_null(`Date', `WID') - null_iff_null(`Date', `Focal') ); -grant_priv(`LOCATIONS_B') +eid_primary_key(`LOCATIONS_GPS') + +grant_priv(`LOCATIONS_GPS') diff --git a/db/schemas/sokwedb/tables/create/locations_map.m4 b/db/schemas/sokwedb/tables/create/locations_map.m4 new file mode 100644 index 0000000..616a7cd --- /dev/null +++ b/db/schemas/sokwedb/tables/create/locations_map.m4 @@ -0,0 +1,46 @@ +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_map ( + eid INTEGER NOT NULL + REFERENCES events + ,mapseq INTEGER NOT NULL + ,meterstonextmapseq INTEGER + nonnegative_check(`MetersToNextMapSeq') + ,commid TEXT NOT NULL + REFERENCES comm_ids + ,follownum TEXT NOT NULL + notonlyspaces_check(`FollowNum') + ,origin TEXT NOT NULL + REFERENCES location_origins + ,notes TEXT NOT NULL + notonlyspaces_check(`Notes') + ,entered TEXT NOT NULL + REFERENCES people +); + +eid_primary_key(`LOCATIONS_MAP') + +grant_priv(`LOCATIONS_MAP') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 3180652..4083dca 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -413,37 +413,43 @@ sdb_generated_rst()dnl .. |HUMANS.NonResearchers| replace:: :ref:`NonResearchers ` -.. |LOCATIONS_B| replace:: :ref:`LOCATIONS_B ` -.. |LOCATIONS_B.LBID| replace:: - :ref:`LBID ` -.. |LOCATIONS_B.WID| replace:: - :ref:`WID ` -.. |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 ` +.. |LOCATIONS_GPS| replace:: :ref:`LOCATIONS_GPS ` +.. |LOCATIONS_GPS.EID| replace:: + :ref:`EID ` +.. |LOCATIONS_GPS.X| replace:: + :ref:`X ` +.. |LOCATIONS_GPS.Y| replace:: + :ref:`Y ` +.. |LOCATIONS_GPS.Elevation| replace:: + :ref:`Elevation ` +.. |LOCATIONS_GPS.CommID| replace:: + :ref:`CommID ` +.. |LOCATIONS_GPS.FollowNum| replace:: + :ref:`FollowNum ` +.. |LOCATIONS_GPS.Origin| replace:: + :ref:`Origin ` +.. |LOCATIONS_GPS.Notes| replace:: + :ref:`Notes ` +.. |LOCATIONS_GPS.Entered| replace:: + :ref:`Entered ` + +.. |LOCATIONS_MAP| replace:: :ref:`LOCATIONS_MAP ` +.. |LOCATIONS_MAP.EID| replace:: + :ref:`EID ` +.. |LOCATIONS_MAP.MapSeq| replace:: + :ref:`MapSeq ` +.. |LOCATIONS_MAP.MetersToNextMapSeq| replace:: + :ref:`MetersToNextMapSeq ` +.. |LOCATIONS_MAP.CommID| replace:: + :ref:`CommID ` +.. |LOCATIONS_MAP.FollowNum| replace:: + :ref:`FollowNum ` +.. |LOCATIONS_MAP.Origin| replace:: + :ref:`Origin ` +.. |LOCATIONS_MAP.Notes| replace:: + :ref:`Notes ` +.. |LOCATIONS_MAP.Entered| replace:: + :ref:`Entered ` .. |LOCATION_ORIGINS| replace:: :ref:`LOCATION_ORIGINS ` diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 8c36418..7c07559 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -48,7 +48,8 @@ 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/locations_gps.rst + tables/locations_map.rst tables/pantgrunts.rst tables/roles.rst tables/non_brec_sighting_sources.rst diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 788f5e9..5b2d384 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -252,6 +252,75 @@ The following table lists these rules and implications: finished. +.. _EVENTS_gps_code: + +``sdb_gps`` (GPS Location) + A record of an individual's location recorded in UTM + coordinates. + + An individual can be located in only one place at any given + time.\ [#f1]_ + This means that the combination of |EVENTS.WID| and |EVENTS.Start| + must be unique. + + The EVENTS row must be associated with a |WATCHES| row representing + either a follow or a location. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of either ``sdb_follow`` or + ``sdb_location``. + Further, as explained in the |WATCHES| documentation, if there is a + follow for a given individual on a given day then the EVENTS row + must be associated with the follow. + + A related row should exist on |LOCATIONS_GPS|; there should be a + row on |LOCATIONS_GPS| with a |LOCATIONS_GPS|.\ |LOCATIONS_GPS.EID| + value of the event's |EVENTS.EID|. + There may be at most one of these related |LOCATIONS_GPS| rows. + The system will generate a warning when there is no |LOCATIONS_GPS| + row related to the event. + + The |ROLES| row related to the event, the row with a |ROLES|.\ + |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, + designates the individual that was located. + There may be at most one |ROLES| row related to the GPS location + event. + The system will generate a warning when there is no |ROLES| row + related to the GPS location event. + + The individual located must be the the focal of the follow, when + the related |WATCHES| row represents a follow, and must be located + individual when the related |WATCHES| row records the date of a + spatial location. + This means that the |ROLES| row related to the event must have a + |ROLES|.\ |ROLES.Participant| value equal to the |WATCHES|.\ + |WATCHES.Focal| of the |WATCHES| row related to the GPS location + event. + + Both the |EVENTS|.\ |EVENTS.Start| and |EVENTS|.\ |EVENTS.Stop| + columns record the time the individual was at the recorded + location. + This means the value of the EVENTS.\ |EVENTS.Start| column must + equal the value of the |EVENTS|.\ |EVENTS.Stop| column. + + When the located individual is the focal of a follow -- when the + related |WATCHES|.\ |WATCHES.Type| value is ``sdb_follow`` -- the + time of the spatial recording should occur when the focal was under + observation. + The system will generate a warning when there is not an |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 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 time of the GPS location event value and the + |EVENTS|.\ |EVENTS.Stop| value is greater than or equal to the time + of the location event, and the event has a related |ROLES| row + where the |ROLES|.\ |ROLES.Participant| is the located individual. + + For GPS location events, the |EVENTS|.\ |EVENTS.Certainty| column + must be ``sdb_identity_certain``. + + .. _EVENTS_grooming_code: ``sdb_grooming`` (Grooming) @@ -344,6 +413,75 @@ The following table lists these rules and implications: |EVENTS.Certainty| column must be ``sdb_identity_certain``. +.. _EVENTS_map_code: + +``sdb_map`` (Map Location) + A record of an individual's location recorded using map + sequence numbers. + + An individual can be located in only one place at any given + time.\ [#f1]_ + This means that the combination of |EVENTS.WID| and |EVENTS.Start| + must be unique. + + The EVENTS row must be associated with a |WATCHES| row representing + either a follow or a location. + This means the |EVENTS.WID| column must reference a |WATCHES| row + with a |WATCHES|.\ |WATCHES.Type| value of either ``sdb_follow`` or + ``sdb_location``. + Further, as explained in the |WATCHES| documentation, if there is a + follow for a given individual on a given day then the EVENTS row + must be associated with the follow. + + A related row should exist on |LOCATIONS_MAP|; there should be a + row on |LOCATIONS_MAP| with a |LOCATIONS_MAP|.\ |LOCATIONS_MAP.EID| + value of the event's |EVENTS.EID|. + There may be at most one of these related |LOCATIONS_MAP| rows. + The system will generate a warning when there is no |LOCATIONS_MAP| + row related to the event. + + The |ROLES| row related to the event, the row with a |ROLES|.\ + |ROLES.EID| value equal to the EVENTS.\ |EVENTS.EID| value, + designates the individual that was located. + There may be at most one |ROLES| row related to the map location + event. + The system will generate a warning when there is no |ROLES| row + related to the map location event. + + The individual located must be the the focal of the follow, when + the related |WATCHES| row represents a follow, and must be located + individual when the related |WATCHES| row records the date of a + spatial location. + This means that the |ROLES| row related to the event must have a + |ROLES|.\ |ROLES.Participant| value equal to the |WATCHES|.\ + |WATCHES.Focal| of the |WATCHES| row related to the map location + event. + + Both the |EVENTS|.\ |EVENTS.Start| and |EVENTS|.\ |EVENTS.Stop| + columns record the time the individual was at the recorded + location. + This means the value of the EVENTS.\ |EVENTS.Start| column must + equal the value of the |EVENTS|.\ |EVENTS.Stop| column. + + When the located individual is the focal of a follow -- when the + related |WATCHES|.\ |WATCHES.Type| value is ``sdb_follow`` -- the + time of the spatial recording should occur when the focal was under + observation. + The system will generate a warning when there is not an |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 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 time of the map location event value and the + |EVENTS|.\ |EVENTS.Stop| value is greater than or equal to the time + of the location event, and the event has a related |ROLES| row + where the |ROLES|.\ |ROLES.Participant| is the located individual. + + For map location events, the |EVENTS|.\ |EVENTS.Certainty| column + must be ``sdb_identity_certain``. + + .. _EVENTS_mating_code: ``sdb_mating`` (Mating) @@ -569,3 +707,13 @@ Notes Free form textual notes regarding the event. |EVENTS.Notes_summary| |notonlyspaces| |notnull| + + +.. rubric:: Footnotes + +.. [#f1] + With the caveat that there are two ways to record a focal's + location, by map sequence number and by UTM coordinates and an + individual may have their location at a given time recorded using + both methods. + These two methods are not required to be in-sync. diff --git a/doc/src/tables/locations_gps.m4 b/doc/src/tables/locations_gps.m4 new file mode 100644 index 0000000..61d6114 --- /dev/null +++ b/doc/src/tables/locations_gps.m4 @@ -0,0 +1,158 @@ +.. 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_GPS: + +LOCATIONS_GPS (spatial LOCATIONS in UTM coordinates) +---------------------------------------------------- + +.. |LOCATIONS_GPS_summary| replace:: + Contains one row for each moment in time the spatial location of an + individual was taken, and recorded in UTM coordinates. + +|LOCATIONS_GPS_summary| + +.. contents:: + :depth: 2 + + +.. _LOCATIONS_GPS.EID: + +EID (Event IDentifier) +`````````````````````` + +.. |LOCATIONS_GPS.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying the spatial map location + event. + |idcol| + +|LOCATIONS_GPS.EID_summary| +The related event contains information on the time associated with the +location, and the related |WATCHES| row information on the located +individual and the date. + +|notnull| + + +.. _LOCATIONS_GPS.X: + +X (X utm coordinate) +```````````````````` + +.. |LOCATIONS_GPS.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_GPS.X_summary| +|notnull| + + +.. _LOCATIONS_GPS.Y: + +Y (Y utm coordinate) +```````````````````` + +.. |LOCATIONS_GPS.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_GPS.Y_summary| +|notnull| + + +.. _LOCATIONS_GPS.Elevation: + +Elevation +````````` + +.. |LOCATIONS_GPS.Elevation_summary| replace:: + The elevation of the location, in meters. + An integer beween ``sdb_min_elevation_utm`` and + ``sdb_max_elevation_utm`` inclusive. + +|LOCATIONS_GPS.Elevation_summary| +This value may be |null| when there is no elevation information. + + +.. _LOCATIONS_GPS.CommID: + +CommID +`````` + +.. |LOCATIONS_GPS.CommID_summary| replace:: + + A code for the community the observers associated with the location + record; the |COMM_IDS|.\ |COMM_IDS.CommID| of the community. + This is not necessarily the community the individual is a member of, + although it usually is. + +|LOCATIONS_GPS.CommID_summary| |notnull| + + +.. _LOCATIONS_GPS.FollowNum: + +FollowNum +````````` + +.. |LOCATIONS_GPS.FollowNum_summary| replace:: + A text string used for checking. + +|LOCATIONS_GPS.FollowNum_summary| +|notonlyspaces| |notnull| + + + +.. _LOCATIONS_GPS.Origin: + +Origin +`````` + +.. |LOCATIONS_GPS.Origin_summary| replace:: + A code indicating the source of the location information. + A |LOCATION_ORIGINS|.\ |LOCATION_ORIGINS.Code| value. + +|LOCATIONS_GPS.Origin_summary| +|notnull| + + + +.. _LOCATIONS_GPS.Notes: + +Notes +````` + +.. |LOCATIONS_GPS.Notes_summary| replace:: + Free form textual notes on the individual's spatial placement. + +|LOCATIONS_GPS.Notes_summary| |notonlyspaces| |notnull| + + +.. _LOCATIONS_GPS.Entered: + +Entered +``````` + +.. |LOCATIONS_GPS.Entered_summary| replace:: + The person who recorded the location. + A |PEOPLE|.\ |PEOPLE.Person| value. + +|LOCATIONS_GPS.Entered_summary| +|notnull| diff --git a/doc/src/tables/locations_map.m4 b/doc/src/tables/locations_map.m4 new file mode 100644 index 0000000..50278d0 --- /dev/null +++ b/doc/src/tables/locations_map.m4 @@ -0,0 +1,147 @@ +.. 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_MAP: + +LOCATIONS_MAP (spatial LOCATIONS in map numbers) +------------------------------------------------ + +.. |LOCATIONS_MAP_summary| replace:: + Contains one row for each moment in time the spatial location of an + individual was taken, and recorded as a map sequence number. + +|LOCATIONS_MAP_summary| + +.. contents:: + :depth: 2 + + +.. _LOCATIONS_MAP.EID: + +EID (Event IDentifier) +`````````````````````` + +.. |LOCATIONS_MAP.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying the spatial map location + event. + |idcol| + +|LOCATIONS_MAP.EID_summary| +The related event contains information on the time associated with the +location, and the related |WATCHES| row information on the located +individual and the date. + +|notnull| + + +.. _LOCATIONS_MAP.MapSeq: + +MapSeq +`````` + +.. |LOCATIONS_MAP.MapSeq_summary| replace:: + The number of the map where the individual was located, where maps + are numbered sequentially within the series of maps traversed + during the follow. + +|LOCATIONS_MAP.MapSeq_summary| +|notnull| + + +.. _LOCATIONS_MAP.MetersToNextMapSeq: + +MetersToNextMapSeq +`````````````````` + +.. |LOCATIONS_MAP.MetersToNextMapSeq_summary| replace:: + The number of meters to the next map sequence number. + +|LOCATIONS_MAP.MetersToNextMapSeq_summary| +This number was calculated in various ways over time. + +|nonnegative| +|notnull| + + +.. _LOCATIONS_MAP.CommID: + +CommID +`````` + +.. |LOCATIONS_MAP.CommID_summary| replace:: + + A code for the community the observers associated with the location + record; the |COMM_IDS|.\ |COMM_IDS.CommID| of the community. + This is not necessarily the community the individual is a member of, + although it usually is. + +|LOCATIONS_MAP.CommID_summary| |notnull| + + +.. _LOCATIONS_MAP.FollowNum: + +FollowNum +````````` + +.. |LOCATIONS_MAP.FollowNum_summary| replace:: + A text string used for checking. + +|LOCATIONS_MAP.FollowNum_summary| +|notonlyspaces| |notnull| + + + +.. _LOCATIONS_MAP.Origin: + +Origin +`````` + +.. |LOCATIONS_MAP.Origin_summary| replace:: + A code indicating the source of the location information. + A |LOCATION_ORIGINS|.\ |LOCATION_ORIGINS.Code| value. + +|LOCATIONS_MAP.Origin_summary| +|notnull| + + + +.. _LOCATIONS_MAP.Notes: + +Notes +````` + +.. |LOCATIONS_MAP.Notes_summary| replace:: + Free form textual notes on the individual's spatial placement. + +|LOCATIONS_MAP.Notes_summary| |notonlyspaces| |notnull| + + +.. _LOCATIONS_MAP.Entered: + +Entered +``````` + +.. |LOCATIONS_MAP.Entered_summary| replace:: + The person who recorded the location. + A |PEOPLE|.\ |PEOPLE.Person| value. + +|LOCATIONS_MAP.Entered_summary| +|notnull| diff --git a/doc/src/tables/watches.m4 b/doc/src/tables/watches.m4 index 4be320f..9d35e36 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -152,6 +152,31 @@ The available |WATCHES.Type| values are: This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and |WATCHES.Date| must be unique. +``sdb_location`` (Location) + Each row represents a date on which an individual was spatially + located. + WATCHES rows of the location type only exist when there is not + a follow-type row for the individual on the given date. + This means that if an individual is spatially located on a day they + are followed there cannot be a row on WATCHES for that individual + for that day with a |WATCHES.Type| of ``sdb_location``. + + The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + |BIOGRAPHY_DATA.AnimID| of the spatially located individual. + + The |WATCHES.CommID| column contains the code for the community + recorded when the location was recorded in the field; the + |COMM_IDS|.\ |COMM_IDS.CommID| of the community. + This is not necessarily the community the located individual is a + member of, although it usually is. + + There may only be one record of an individual who was spatially + located per day. + This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + |WATCHES.Date| must be unique. + If an individual's location is recorded multiple times on a single + day, there are multiple rows on the |EVENTS| table to record this. + The |WATCHES.Date| must be during a period when the watched individual was under study. This means that |WATCHES.Date| must be on or after the watched @@ -229,11 +254,14 @@ Type |WATCHES.Type_summary| -One of the following values:: +One of the following values: - ``sdb_follow`` (Follow) +``sdb_attendance`` (Attendance) + +``sdb_follow`` (Follow) - ``sdb_attendance`` (Attendance) +``sdb_location`` (Location) + Only used when there is no follow to use. See :ref:`overview of the WATCHES table `, above, for more information. diff --git a/include/global_constants.m4 b/include/global_constants.m4 index f4ded13..dbc2977 100644 --- a/include/global_constants.m4 +++ b/include/global_constants.m4 @@ -123,10 +123,14 @@ dnl The arrival event define(`sdb_arrival', `ARR') dnl The food/eating event define(`sdb_food', `FOOD') +dnl The recording of a GPS location +define(`sdb_gps', `GPS') dnl Grooming interval sampling define(`sdb_groom_scan', `GSCAN') dnl The grooming event define(`sdb_grooming', `GROOM') +dnl The recording of a map location +define(`sdb_map', `MAP') dnl The mating event define(`sdb_mating', `MATE') dnl The other species is present event @@ -190,8 +194,9 @@ dnl WATCHES dnl dnl The WATCHES.Type values -define(`sdb_follow', `F') define(`sdb_attendance', `A') +define(`sdb_follow', `F') +define(`sdb_location', `L') divert(`0')dnl Output with m4 again ]}])dnl End of ifdef over the whole file. -- 2.34.1