From 495a6c093a4b7da1c1e74deb066e73d9190c8d57 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 3 Jul 2026 17:12:28 +0000 Subject: [PATCH] Change column name WATCHES.Focal -> WATCHES.AnimID --- conversion/load_aggressions.m4 | 2 +- conversion/load_finish.sql | 2 +- conversion/load_follow_arrival.m4 | 4 +- conversion/load_follow_to_other.sql | 4 +- conversion/load_follow_to_watches.sql | 12 +- db/schemas/lib/triggers/create/aggressions.m4 | 10 +- db/schemas/lib/triggers/create/arrivals.m4 | 76 ++++---- db/schemas/lib/triggers/create/arrivals_a.m4 | 10 +- .../lib/triggers/create/biography_data.m4 | 80 ++++----- .../lib/triggers/create/brecord_notes.m4 | 10 +- db/schemas/lib/triggers/create/colobus.m4 | 10 +- db/schemas/lib/triggers/create/dyads.m4 | 64 +++---- db/schemas/lib/triggers/create/events.m4 | 28 +-- .../lib/triggers/create/follow_observers.m4 | 18 +- db/schemas/lib/triggers/create/food_events.m4 | 30 ++-- .../lib/triggers/create/groom_scans_b.m4 | 10 +- db/schemas/lib/triggers/create/groomings.m4 | 20 +-- db/schemas/lib/triggers/create/humans.m4 | 18 +- db/schemas/lib/triggers/create/matings.m4 | 10 +- db/schemas/lib/triggers/create/obs.m4 | 36 ++-- db/schemas/lib/triggers/create/pantgrunts.m4 | 10 +- .../lib/triggers/create/pantgrunts_view.m4 | 62 +++---- db/schemas/lib/triggers/create/roles.m4 | 168 +++++++++--------- .../lib/triggers/create/species_present.m4 | 18 +- db/schemas/lib/triggers/create/watches.m4 | 56 +++--- db/schemas/sokwedb/indexes/create/watches.m4 | 28 +-- db/schemas/sokwedb/indexes/drop/watches.m4 | 14 +- db/schemas/sokwedb/tables/create/watches.m4 | 2 +- db/schemas/sokwedb/views/create/dyads.m4 | 4 +- db/schemas/sokwedb/views/create/obs.m4 | 4 +- .../sokwedb/views/create/pantgrunts_view.m4 | 4 +- doc/diagrams/dyads.svg | 2 +- doc/diagrams/obs.svg | 2 +- doc/diagrams/pantgrunts_view.svg | 2 +- doc/diagrams/watches.svg | 2 +- doc/src/epilog.inc.m4 | 4 +- doc/src/tables/events.m4 | 10 +- doc/src/tables/watches.m4 | 61 +++---- doc/src/views/dyads.m4 | 12 +- doc/src/views/obs.m4 | 32 ++-- doc/src/views/pantgrunts_view.m4 | 6 +- 41 files changed, 478 insertions(+), 479 deletions(-) diff --git a/conversion/load_aggressions.m4 b/conversion/load_aggressions.m4 index b05b613..98e9720 100644 --- a/conversion/load_aggressions.m4 +++ b/conversion/load_aggressions.m4 @@ -46,7 +46,7 @@ BEGIN SELECT watches.wid INTO STRICT related_wid FROM watches - WHERE watches.focal = this_ae.ae_fol_b_focal_id + WHERE watches.animid = this_ae.ae_fol_b_focal_id AND watches.date = this_ae.ae_date; -- aggression_event -> EVENTS diff --git a/conversion/load_finish.sql b/conversion/load_finish.sql index 1fe5097..99bedc9 100644 --- a/conversion/load_finish.sql +++ b/conversion/load_finish.sql @@ -34,7 +34,7 @@ PERFORM * WHERE NOT EXISTS (SELECT watches.wid FROM watches - WHERE watches.focal = follow_arrival.fa_fol_b_focal_animid + WHERE watches.animid = follow_arrival.fa_fol_b_focal_animid AND watches.date = follow_arrival.fa_fol_date); IF FOUND THEN RAISE data_exception USING diff --git a/conversion/load_follow_arrival.m4 b/conversion/load_follow_arrival.m4 index ef7791d..d60ebbe 100644 --- a/conversion/load_follow_arrival.m4 +++ b/conversion/load_follow_arrival.m4 @@ -1,4 +1,4 @@ --- Copyright (C) 2023-2025 The Meme Factory, Inc. http://www.karlpinc.com/ +-- Copyright (C) 2023-2026 The Meme Factory, Inc. http://www.karlpinc.com/ -- -- This program is free software: you can redistribute it and/or modify -- it under the terms of the GNU Affero General Public License as @@ -57,7 +57,7 @@ BEGIN SELECT watches.wid INTO STRICT related_wid FROM watches - WHERE watches.focal = this_fa.fa_fol_b_focal_animid + WHERE watches.animid = this_fa.fa_fol_b_focal_animid AND watches.date = this_fa.fa_fol_date; -- follow_arrival -> EVENTS diff --git a/conversion/load_follow_to_other.sql b/conversion/load_follow_to_other.sql index 958ef00..e23bd63 100644 --- a/conversion/load_follow_to_other.sql +++ b/conversion/load_follow_to_other.sql @@ -46,7 +46,7 @@ INSERT INTO follow_studies (wid, code) SELECT (SELECT watches.wid FROM watches - WHERE watches.focal = follow.fol_b_animid + WHERE watches.animid = follow.fol_b_animid AND watches.date = follow.fol_date) , UPPER(follow.fol_study_code_1) FROM clean.follow @@ -57,7 +57,7 @@ INSERT INTO follow_studies (wid, code) SELECT (SELECT watches.wid FROM watches - WHERE watches.focal = follow.fol_b_animid + WHERE watches.animid = follow.fol_b_animid AND watches.date = follow.fol_date) , UPPER(follow.fol_study_code_2) FROM clean.follow diff --git a/conversion/load_follow_to_watches.sql b/conversion/load_follow_to_watches.sql index 0b9be74..89c12ba 100644 --- a/conversion/load_follow_to_watches.sql +++ b/conversion/load_follow_to_watches.sql @@ -55,7 +55,7 @@ $$; -- follow -> WATCHES -- INSERT INTO watches ( - focal + animid , commid , date , type @@ -91,14 +91,14 @@ SELECT follow.fol_b_animid SELECT (SELECT watches.wid FROM watches - WHERE watches.focal = obs.fol_b_animid + WHERE watches.animid = obs.fol_b_animid AND watches.date = obs.fol_date) , 'AM' , COALESCE(obs.am_obs1, 'NONE') -- problem #38 , COALESCE(obs.am_obs2, 'NONE') -- problem #38 FROM obs -- Don't try to load watches that haven't been converted - JOIN watches ON (watches.focal = obs.fol_b_animid + JOIN watches ON (watches.animid = obs.fol_b_animid AND watches.date = obs.fol_date) WHERE COALESCE(obs.am_obs1, obs.am_obs2) IS NOT NULL; @@ -121,14 +121,14 @@ SELECT follow.fol_b_animid SELECT (SELECT watches.wid FROM watches - WHERE watches.focal = obs.fol_b_animid + WHERE watches.animid = obs.fol_b_animid AND watches.date = obs.fol_date) , 'PM' , COALESCE(obs.pm_obs1, 'NONE') -- problem #38 , COALESCE(obs.pm_obs2, 'NONE') -- problem #38 FROM obs -- Don't try to load watches that haven't been converted - JOIN watches ON (watches.focal = obs.fol_b_animid + JOIN watches ON (watches.animid = obs.fol_b_animid AND watches.date = obs.fol_date) WHERE COALESCE(obs.pm_obs1, obs.pm_obs2) IS NOT NULL; @@ -142,7 +142,7 @@ INSERT INTO follow_observers ( SELECT (SELECT watches.wid FROM watches - WHERE watches.focal = follow.fol_b_animid + WHERE watches.animid = follow.fol_b_animid AND watches.date = follow.fol_date) , 'UNK' , 'NONE' diff --git a/db/schemas/lib/triggers/create/aggressions.m4 b/db/schemas/lib/triggers/create/aggressions.m4 index 08e855c..7d1eda0 100644 --- a/db/schemas/lib/triggers/create/aggressions.m4 +++ b/db/schemas/lib/triggers/create/aggressions.m4 @@ -48,7 +48,7 @@ CREATE OR REPLACE FUNCTION aggressions_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -57,10 +57,10 @@ CREATE OR REPLACE FUNCTION aggressions_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -114,8 +114,8 @@ CREATE OR REPLACE FUNCTION aggressions_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/arrivals.m4 b/db/schemas/lib/triggers/create/arrivals.m4 index 27c494e..a9290d6 100644 --- a/db/schemas/lib/triggers/create/arrivals.m4 +++ b/db/schemas/lib/triggers/create/arrivals.m4 @@ -93,7 +93,7 @@ CREATE OR REPLACE FUNCTION arrivals_before_func () a_start events.start%TYPE; a_stop events.stop%TYPE; -- WATCHES - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -102,10 +102,10 @@ CREATE OR REPLACE FUNCTION arrivals_before_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -139,8 +139,8 @@ CREATE OR REPLACE FUNCTION arrivals_before_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -148,9 +148,9 @@ CREATE OR REPLACE FUNCTION arrivals_before_func () -- There must be a related ROLES row. SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -184,8 +184,8 @@ CREATE OR REPLACE FUNCTION arrivals_before_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -250,16 +250,16 @@ CREATE OR REPLACE FUNCTION arrivals_before_func () a_role roles.role%TYPE; a_participant roles.participant%TYPE; -- WATCHES - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; BEGIN SELECT events.behavior, events.start, events.stop , roles.pid, roles.role, roles.participant - , watches.focal, watches.date + , watches.animid, watches.date INTO a_behavior , a_start , a_stop , a_pid , a_role , a_participant - , a_focal , a_date + , a_animid , a_date FROM events JOIN watches ON (watches.wid = events.wid) JOIN roles ON (roles.eid = NEW.eid) @@ -298,8 +298,8 @@ CREATE OR REPLACE FUNCTION arrivals_before_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -338,7 +338,7 @@ CREATE OR REPLACE FUNCTION arrivals_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_w_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -354,12 +354,12 @@ CREATE OR REPLACE FUNCTION arrivals_func () , roles.role, roles.pid , biography_data.animid, biography_data.sex , biography_data.birthdate - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date INTO a_eid , a_behavior , a_start , a_stop , a_role , a_pid , a_animid , a_sex , a_birthdate - , a_wid , a_focal , a_date + , a_wid , a_w_animid , a_date FROM events JOIN roles ON (roles.eid = events.eid) @@ -406,8 +406,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_w_animid || '), Value (WATCHES.Date) = (' || a_date || ')' @@ -458,8 +458,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_w_animid || '), Value (WATCHES.Date) = (' || a_date || ')' @@ -506,8 +506,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_w_animid || '), Value (WATCHES.Date) = (' || a_date || ')' @@ -557,8 +557,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_w_animid || '), Value (WATCHES.Date) = (' || a_date || ')' @@ -603,8 +603,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_w_animid || '), Value (WATCHES.Date) = (' || a_date || ')' @@ -648,8 +648,8 @@ CREATE OR REPLACE FUNCTION arrivals_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_w_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -725,7 +725,7 @@ CREATE OR REPLACE FUNCTION arrivals_commit_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -749,10 +749,10 @@ CREATE OR REPLACE FUNCTION arrivals_commit_func () IF NEW.seq > 1 THEN -- Does the expected NEW.seq - 1 row exist? SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM roles , events @@ -806,8 +806,8 @@ CREATE OR REPLACE FUNCTION arrivals_commit_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -818,10 +818,10 @@ CREATE OR REPLACE FUNCTION arrivals_commit_func () -- When there is an OLD.Seq + 1 row, -- does the expected OLD.seq row exist? SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM roles , events @@ -881,8 +881,8 @@ CREATE OR REPLACE FUNCTION arrivals_commit_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/arrivals_a.m4 b/db/schemas/lib/triggers/create/arrivals_a.m4 index 3ff6341..6bb0748 100644 --- a/db/schemas/lib/triggers/create/arrivals_a.m4 +++ b/db/schemas/lib/triggers/create/arrivals_a.m4 @@ -49,7 +49,7 @@ CREATE OR REPLACE FUNCTION arrivals_a_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -58,10 +58,10 @@ CREATE OR REPLACE FUNCTION arrivals_a_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -94,8 +94,8 @@ CREATE OR REPLACE FUNCTION arrivals_a_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index ac729bd..5490d7f 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -449,7 +449,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT watches.wid, watches.commid, watches.date, watches.type INTO a_wid , a_commid , a_date , a_type FROM watches - WHERE watches.focal = NEW.animid + WHERE watches.animid = NEW.animid AND watches.date < NEW.entrydate ORDER BY watches.date; -- consistency IF FOUND THEN @@ -479,7 +479,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT watches.wid, watches.commid, watches.date, watches.type INTO a_wid , a_commid , a_date , a_type FROM watches - WHERE watches.focal = NEW.animid + WHERE watches.animid = NEW.animid AND watches.date > NEW.departdate ORDER BY watches.date DESC; -- consistency IF FOUND THEN @@ -720,7 +720,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_type watches.type%TYPE; @@ -730,10 +730,10 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF NEW.entrydate > OLD.entrydate THEN SELECT roles.pid, roles.role , events.eid, events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_pid , a_role , a_eid , a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN watches ON (watches.wid = events.wid) @@ -768,8 +768,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -783,10 +783,10 @@ CREATE OR REPLACE FUNCTION biography_data_func () IF NEW.departdate < OLD.departdate THEN SELECT roles.pid, roles.role , events.eid, events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_pid , a_role , a_eid , a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN watches ON (watches.wid = events.wid) @@ -821,8 +821,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -836,10 +836,10 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- sdb_actee the female. SELECT roles.pid, roles.role , events.eid, events.behavior, events.start - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_pid , a_role , a_eid , a_behavior , a_start - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN watches ON (watches.wid = events.wid) @@ -876,8 +876,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_start || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -898,7 +898,7 @@ CREATE OR REPLACE FUNCTION biography_data_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_type watches.type%TYPE; -- ROLES @@ -918,11 +918,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -963,8 +963,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_cycle || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -986,11 +986,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1038,8 +1038,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_cycle || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -1053,11 +1053,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_eid , a_cycle - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1106,8 +1106,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_cycle || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -1125,11 +1125,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_eid , a_cycle - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1178,8 +1178,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_cycle || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -1192,11 +1192,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1246,8 +1246,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_cycle || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -1265,11 +1265,11 @@ CREATE OR REPLACE FUNCTION biography_data_func () SELECT roles.role , events.eid, events.behavior, events.start, events.stop , arrivals.cycle - , watches.wid, watches.focal, watches.date, watches.type + , watches.wid, watches.animid, watches.date, watches.type INTO a_role , a_eid , a_behavior , a_start , a_stop , a_cycle - , a_wid , a_focal , a_date , a_type + , a_wid , a_animid , a_date , a_type FROM roles JOIN events ON (events.eid = roles.eid) JOIN arrivals ON (arrivals.eid = roles.eid) @@ -1310,8 +1310,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () || a_cycle || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' diff --git a/db/schemas/lib/triggers/create/brecord_notes.m4 b/db/schemas/lib/triggers/create/brecord_notes.m4 index a21dd3b..5ff1a00 100644 --- a/db/schemas/lib/triggers/create/brecord_notes.m4 +++ b/db/schemas/lib/triggers/create/brecord_notes.m4 @@ -48,14 +48,14 @@ CREATE OR REPLACE FUNCTION brecord_notes_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -96,8 +96,8 @@ CREATE OR REPLACE FUNCTION brecord_notes_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/colobus.m4 b/db/schemas/lib/triggers/create/colobus.m4 index fbf397e..2295842 100644 --- a/db/schemas/lib/triggers/create/colobus.m4 +++ b/db/schemas/lib/triggers/create/colobus.m4 @@ -48,14 +48,14 @@ CREATE OR REPLACE FUNCTION colobus_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -127,8 +127,8 @@ CREATE OR REPLACE FUNCTION colobus_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/dyads.m4 b/db/schemas/lib/triggers/create/dyads.m4 index 7cae496..f411664 100644 --- a/db/schemas/lib/triggers/create/dyads.m4 +++ b/db/schemas/lib/triggers/create/dyads.m4 @@ -34,8 +34,8 @@ define({_show_row}, {'Key (WID) = (' || textualize(`NEW.wid') || '), Value (Date) = (' || textualize(`NEW.date') - || '), Value (Focal) = (' - || textualize(`NEW.focal') + || '), Value (AnimID) = (' + || textualize(`NEW.animid') || '), Value (Type) = (' || textualize(`NEW.type') || '), Value (CommID) = (' @@ -74,7 +74,7 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () AS $$ DECLARE target_wid watches.wid%TYPE; - target_focal watches.focal%TYPE; + target_animid watches.animid%TYPE; target_commid watches.commid%TYPE; target_date watches.date%TYPE; target_type watches.type%TYPE; @@ -146,7 +146,7 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () target_stop := NEW.stop; IF NEW.wid IS NULL THEN - -- Use Date, Focal Type, Behavior, Start, and Stop to query OBS. + -- Use Date, AnimID Type, Behavior, Start, and Stop to query OBS. -- Date must be supplied - to lookup WATCHES IF NEW.date IS NULL THEN @@ -158,11 +158,11 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () || _show_row(); END IF; - -- Focal must be supplied - to lookup WATCHES - IF NEW.focal IS NULL THEN + -- AnimID must be supplied - to lookup WATCHES + IF NEW.animid IS NULL THEN RAISE EXCEPTION data_exception USING MESSAGE = 'Error on INSERT into DYADS' - , DETAIL = 'The supplied Focal value may not be NULL' + , DETAIL = 'The supplied AnimID value may not be NULL' || ' when WID is NULL' || ': ' || _show_row(); @@ -178,14 +178,14 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () || _show_row(); END IF; - SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid , obs.certainty , obs.notes, obs.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_certainty , target_notes, target_event_notes FROM obs - WHERE obs.focal = NEW.focal + WHERE obs.animid = NEW.animid AND obs.date = NEW.date AND obs.type = NEW.type AND obs.behavior = NEW.behavior @@ -205,8 +205,8 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () RAISE EXCEPTION data_exception USING MESSAGE = 'Error on INSERT into DYADS' , DETAIL = 'The values in the database for the OBS' - ||' row with (OBS.Focal) = (' - || NEW.focal + ||' row with (OBS.AnimID) = (' + || NEW.animid || ') and (OBS.Date = (' || NEW.date || ') and (OBS.Type = (' @@ -222,8 +222,8 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () || target_wid || '), Value (Date) = (' || target_date - || '), Value (Focal) = (' - || target_focal + || '), Value (AnimID) = (' + || target_animid || '), Value (Type) = (' || target_type || '), Value (CommID) = (' @@ -248,10 +248,10 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () target_wid := NEW.wid; -- Use WID, Behavior, Start, and Stop to query OBS - SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid , obs.eid , obs.certainty , obs.notes, obs.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_eid, target_certainty , target_notes, target_event_notes FROM obs @@ -264,8 +264,8 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () -- Validate supplied values against db values IF (NEW.date IS NOT NULL AND NEW.date <> target_date) - OR (NEW.focal IS NOT NULL - AND NEW.focal <> target_focal) + OR (NEW.animid IS NOT NULL + AND NEW.animid <> target_animid) OR (NEW.type IS NOT NULL AND NEW.type <> target_type) OR (NEW.commid IS NOT NULL @@ -292,8 +292,8 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () || target_wid || '), Value (Date) = (' || target_date - || '), Value (Focal) = (' - || target_focal + || '), Value (AnimID) = (' + || target_animid || '), Value (Type) = (' || target_type || '), Value (CommID) = (' @@ -318,23 +318,23 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () IF create_obs THEN WITH ins AS - (INSERT INTO obs (wid , date , focal , type , commid + (INSERT INTO obs (wid , date , animid , type , commid , eid , behavior , start , stop , certainty , notes , event_notes) - VALUES ( NEW.wid, NEW.date, NEW.focal, NEW.type, NEW.commid + VALUES ( NEW.wid, NEW.date, NEW.animid, NEW.type, NEW.commid , NEW.eid, NEW.behavior, NEW.start, NEW.stop , NEW.certainty , NEW.notes, NEW.event_notes) - RETURNING wid, date, focal, type, commid + RETURNING wid, date, animid, type, commid , eid, behavior, start, stop , certainty , notes, event_notes) - SELECT ins.wid , ins.date , ins.focal , ins.type , ins.commid + SELECT ins.wid , ins.date , ins.animid , ins.type , ins.commid , ins.eid , ins.behavior , ins.start , ins.stop , ins.certainty , ins.notes , ins.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_eid, target_behavior, target_start, target_stop , target_certainty , target_notes, target_event_notes @@ -345,10 +345,10 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () target_eid := NEW.eid; -- Use EID to lookup OBS - SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid , obs.behavior , obs.start , obs.stop , obs.certainty , obs.notes, obs.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_behavior, target_start, target_stop, target_certainty , target_notes, target_event_notes FROM obs @@ -360,8 +360,8 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () AND NEW.wid <> target_wid) OR (NEW.date IS NOT NULL AND NEW.date <> target_date) - OR (NEW.focal IS NOT NULL - AND NEW.focal <> target_focal) + OR (NEW.animid IS NOT NULL + AND NEW.animid <> target_animid) OR (NEW.type IS NOT NULL AND NEW.type <> target_type) OR (NEW.commid IS NOT NULL @@ -388,8 +388,8 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () || target_wid || '), Value (Date) = (' || target_date - || '), Value (Focal) = (' - || target_focal + || '), Value (AnimID) = (' + || target_animid || '), Value (Type) = (' || target_type || '), Value (CommID) = (' @@ -484,7 +484,7 @@ CREATE OR REPLACE FUNCTION dyads_insert_func () -- Assign values to all the NEW columns. In theory then RETURNING -- will return the newly inserted, or existing, data values. NEW.wid := target_wid; - NEW.focal := target_focal; + NEW.animid := target_animid; NEW.commid := target_commid; NEW.date := target_date; NEW.type := target_type; diff --git a/db/schemas/lib/triggers/create/events.m4 b/db/schemas/lib/triggers/create/events.m4 index e03b201..b19e763 100644 --- a/db/schemas/lib/triggers/create/events.m4 +++ b/db/schemas/lib/triggers/create/events.m4 @@ -66,7 +66,7 @@ CREATE OR REPLACE FUNCTION events_func () IF TG_OP = 'INSERT' THEN DECLARE - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_commid watches.commid%TYPE; a_date watches.date%TYPE; a_type watches.type%TYPE; @@ -94,9 +94,9 @@ CREATE OR REPLACE FUNCTION events_func () END IF; IF watch_type IS NOT NULL THEN - SELECT watches.focal, watches.commid, watches.date, watches.type + SELECT watches.animid, watches.commid, watches.date, watches.type , watches.notes - INTO a_focal, a_commid , a_date , a_type + INTO a_animid, a_commid , a_date , a_type , a_notes FROM watches WHERE watches.wid = NEW.wid @@ -123,8 +123,8 @@ CREATE OR REPLACE FUNCTION events_func () || NEW.certainty || ') : Key (WATCHES.WID) = (' || NEW.wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -158,9 +158,9 @@ CREATE OR REPLACE FUNCTION events_func () watch_type := 'sdb_location'; END IF; - SELECT watches.focal, watches.commid, watches.date, watches.type + SELECT watches.animid, watches.commid, watches.date, watches.type , watches.notes - INTO a_focal, a_commid , a_date , a_type + INTO a_animid, a_commid , a_date , a_type , a_notes FROM watches WHERE watches.wid = NEW.wid @@ -188,8 +188,8 @@ CREATE OR REPLACE FUNCTION events_func () || NEW.certainty || ') : Key (WATCHES.WID) = (' || NEW.wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -246,7 +246,7 @@ CREATE OR REPLACE FUNCTION events_delete_commit_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -260,12 +260,12 @@ CREATE OR REPLACE FUNCTION events_delete_commit_func () SELECT arrivals.seq, arrivals.neststart , arrivals.nestend, arrivals.cycle, arrivals.datasource , events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_seq , a_neststart , a_nestend , a_cycle , a_datasource , a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM watches , events @@ -325,8 +325,8 @@ CREATE OR REPLACE FUNCTION events_delete_commit_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES .Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/follow_observers.m4 b/db/schemas/lib/triggers/create/follow_observers.m4 index f3f6341..b0d0157 100644 --- a/db/schemas/lib/triggers/create/follow_observers.m4 +++ b/db/schemas/lib/triggers/create/follow_observers.m4 @@ -29,7 +29,7 @@ CREATE OR REPLACE FUNCTION follow_observers_func () sdb_function_set_search_path AS $$ DECLARE - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_commid watches.commid%TYPE; a_date watches.date%TYPE; a_type watches.type%TYPE; @@ -47,9 +47,9 @@ CREATE OR REPLACE FUNCTION follow_observers_func () END IF; -- The observers can only have watched a follow. - SELECT watches.focal, watches.commid, watches.date, watches.type + SELECT watches.animid, watches.commid, watches.date, watches.type , watches.notes - INTO a_focal, a_commid , a_date , a_type + INTO a_animid, a_commid , a_date , a_type , a_notes FROM watches WHERE watches.wid = NEW.wid @@ -71,8 +71,8 @@ CREATE OR REPLACE FUNCTION follow_observers_func () || NEW.obs_tiki || ') : Key (WATCHES.WID) = (' || NEW.wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -91,8 +91,8 @@ CREATE OR REPLACE FUNCTION follow_observers_func () || '): Value (WID) = (' || NEW.WID || '): ' - || (SELECT 'Value (WATCHES.Focal) = (' - || watches.focal + || (SELECT 'Value (WATCHES.AnimID) = (' + || watches.animid || '): Value (WATCHES.CommID) = (' || watches.commid || '): Value (WATCHES.Date) = (' @@ -109,8 +109,8 @@ CREATE OR REPLACE FUNCTION follow_observers_func () || '): Value (WID) = (' || NEW.wid || '): ' - || (SELECT 'Value (WATCHES.Focal) = (' - || watches.focal + || (SELECT 'Value (WATCHES.AnimID) = (' + || watches.animid || '): Value (WATCHES.CommID) = (' || watches.commid || '): Value (WATCHES.Date) = (' diff --git a/db/schemas/lib/triggers/create/food_events.m4 b/db/schemas/lib/triggers/create/food_events.m4 index 5344bdc..137ee4a 100644 --- a/db/schemas/lib/triggers/create/food_events.m4 +++ b/db/schemas/lib/triggers/create/food_events.m4 @@ -49,7 +49,7 @@ CREATE OR REPLACE FUNCTION food_events_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -58,10 +58,10 @@ CREATE OR REPLACE FUNCTION food_events_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -101,8 +101,8 @@ CREATE OR REPLACE FUNCTION food_events_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -160,7 +160,7 @@ CREATE OR REPLACE FUNCTION food_events_commit_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -169,10 +169,10 @@ CREATE OR REPLACE FUNCTION food_events_commit_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -213,8 +213,8 @@ CREATE OR REPLACE FUNCTION food_events_commit_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -269,7 +269,7 @@ CREATE OR REPLACE FUNCTION food_events_delete_commit_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -278,10 +278,10 @@ CREATE OR REPLACE FUNCTION food_events_delete_commit_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -322,8 +322,8 @@ CREATE OR REPLACE FUNCTION food_events_delete_commit_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/groom_scans_b.m4 b/db/schemas/lib/triggers/create/groom_scans_b.m4 index af42f5d..60824c4 100644 --- a/db/schemas/lib/triggers/create/groom_scans_b.m4 +++ b/db/schemas/lib/triggers/create/groom_scans_b.m4 @@ -48,7 +48,7 @@ CREATE OR REPLACE FUNCTION groom_scans_b_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -57,10 +57,10 @@ CREATE OR REPLACE FUNCTION groom_scans_b_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -98,8 +98,8 @@ CREATE OR REPLACE FUNCTION groom_scans_b_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/groomings.m4 b/db/schemas/lib/triggers/create/groomings.m4 index 86fac95..80df96f 100644 --- a/db/schemas/lib/triggers/create/groomings.m4 +++ b/db/schemas/lib/triggers/create/groomings.m4 @@ -48,7 +48,7 @@ CREATE OR REPLACE FUNCTION groomings_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -57,10 +57,10 @@ CREATE OR REPLACE FUNCTION groomings_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -99,8 +99,8 @@ CREATE OR REPLACE FUNCTION groomings_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -137,9 +137,9 @@ define({_sdb_grm_actor_or_actee}, -- The $1 must be one of the participants in the grooming SELECT events.start, events.stop, events.behavior - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date INTO a_start , a_stop , a_behavior - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -179,8 +179,8 @@ define({_sdb_grm_actor_or_actee}, || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -210,7 +210,7 @@ CREATE OR REPLACE FUNCTION groomings_commit_func () a_behavior events.behavior%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; BEGIN diff --git a/db/schemas/lib/triggers/create/humans.m4 b/db/schemas/lib/triggers/create/humans.m4 index 423cc8c..69d9731 100644 --- a/db/schemas/lib/triggers/create/humans.m4 +++ b/db/schemas/lib/triggers/create/humans.m4 @@ -47,16 +47,16 @@ CREATE OR REPLACE FUNCTION humans_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; BEGIN -- The EVENTS.Behavior must be sdb_other_species SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -82,8 +82,8 @@ CREATE OR REPLACE FUNCTION humans_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -96,10 +96,10 @@ CREATE OR REPLACE FUNCTION humans_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , species_present.species INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_species FROM events JOIN watches ON (watches.wid = events.wid) @@ -129,8 +129,8 @@ CREATE OR REPLACE FUNCTION humans_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '): Key (SPECIES_PRESENT.EID) = (' diff --git a/db/schemas/lib/triggers/create/matings.m4 b/db/schemas/lib/triggers/create/matings.m4 index 608609e..8251da6 100644 --- a/db/schemas/lib/triggers/create/matings.m4 +++ b/db/schemas/lib/triggers/create/matings.m4 @@ -48,7 +48,7 @@ CREATE OR REPLACE FUNCTION matings_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -57,10 +57,10 @@ CREATE OR REPLACE FUNCTION matings_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -99,8 +99,8 @@ CREATE OR REPLACE FUNCTION matings_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/obs.m4 b/db/schemas/lib/triggers/create/obs.m4 index d8fdca9..f57062d 100644 --- a/db/schemas/lib/triggers/create/obs.m4 +++ b/db/schemas/lib/triggers/create/obs.m4 @@ -34,8 +34,8 @@ define({_show_row}, {'Key (WID) = (' || textualize(`NEW.wid') || '), Value (Date) = (' || textualize(`NEW.date') - || '), Value (Focal) = (' - || textualize(`NEW.focal') + || '), Value (AnimID) = (' + || textualize(`NEW.animid') || '), Value (Type) = (' || textualize(`NEW.type') || '), Value (CommID) = (' @@ -67,7 +67,7 @@ CREATE OR REPLACE FUNCTION obs_insert_func () AS $$ DECLARE target_wid watches.wid%TYPE; - target_focal watches.focal%TYPE; + target_animid watches.animid%TYPE; target_commid watches.commid%TYPE; target_date watches.date%TYPE; target_type watches.type%TYPE; @@ -109,11 +109,11 @@ CREATE OR REPLACE FUNCTION obs_insert_func () || _show_row(); END IF; - -- Focal must be supplied - to lookup WATCHES - IF NEW.focal IS NULL THEN + -- AnimID must be supplied - to lookup WATCHES + IF NEW.animid IS NULL THEN RAISE EXCEPTION data_exception USING MESSAGE = 'Error on INSERT into OBS' - , DETAIL = 'The supplied Focal value may not be NULL' + , DETAIL = 'The supplied AnimID value may not be NULL' || ': ' || _show_row(); END IF; @@ -132,16 +132,16 @@ CREATE OR REPLACE FUNCTION obs_insert_func () -- Get all the WATCHES columns and be sure they match. -- (None can be NULL) - SELECT watches.focal, watches.commid, watches.date, watches.type + SELECT watches.animid, watches.commid, watches.date, watches.type , watches.notes - INTO target_focal , target_commid , target_date , target_type + INTO target_animid , target_commid , target_date , target_type , target_notes FROM watches WHERE watches.wid = NEW.wid; IF FOUND THEN - IF (NEW.focal IS NOT NULL - AND NEW.focal <> target_focal) + IF (NEW.animid IS NOT NULL + AND NEW.animid <> target_animid) OR (NEW.commid IS NOT NULL AND NEW.commid <> target_commid) OR (NEW.date IS NOT NULL @@ -160,8 +160,8 @@ CREATE OR REPLACE FUNCTION obs_insert_func () || NEW.wid || '), Value (Date) = (' || target_date - || '), Value (Focal) = (' - || target_focal + || '), Value (AnimID) = (' + || target_animid || '), Value (Type) = (' || target_type || '), Value (CommID) = (' @@ -203,7 +203,7 @@ CREATE OR REPLACE FUNCTION obs_insert_func () SELECT watches.wid, watches.notes INTO target_wid , target_notes FROM watches - WHERE watches.focal = NEW.focal + WHERE watches.animid = NEW.animid AND watches.date = NEW.date AND watches.type = NEW.type; @@ -225,8 +225,8 @@ CREATE OR REPLACE FUNCTION obs_insert_func () assign_notes := TRUE; ELSE -- NOT FOUND, no existing WATCHES row - INSERT INTO watches (focal, commid, date, type, notes) - VALUES (NEW.focal + INSERT INTO watches (animid, commid, date, type, notes) + VALUES (NEW.animid ,NEW.commid ,NEW.date ,NEW.type @@ -269,7 +269,7 @@ CREATE OR REPLACE FUNCTION obs_insert_func () END IF; IF assign_watches_rest THEN - NEW.focal := target_focal; + NEW.animid := target_animid; NEW.date := target_date; NEW.commid := target_commid; NEW.type := target_type; @@ -307,13 +307,13 @@ CREATE OR REPLACE FUNCTION obs_update_func () cannot_change(`OBS', `WID') cannot_change(`OBS', `EID') - IF NEW.focal <> OLD.focal + IF NEW.animid <> OLD.animid OR NEW.commid <> OLD.commid OR NEW.date <> OLD.date OR NEW.type <> OLD.type OR NEW.notes <> OLD.notes THEN UPDATE watches - SET focal = NEW.focal + SET animid = NEW.animid , commid = NEW.commid , date = NEW.date , type = NEW.type diff --git a/db/schemas/lib/triggers/create/pantgrunts.m4 b/db/schemas/lib/triggers/create/pantgrunts.m4 index 0bbc010..2a4fe19 100644 --- a/db/schemas/lib/triggers/create/pantgrunts.m4 +++ b/db/schemas/lib/triggers/create/pantgrunts.m4 @@ -48,7 +48,7 @@ CREATE OR REPLACE FUNCTION pantgrunts_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; -- ROLES a_pid roles.pid%TYPE; @@ -57,10 +57,10 @@ CREATE OR REPLACE FUNCTION pantgrunts_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , roles.pid, roles.role, roles.participant INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_pid , a_role , a_participant FROM events JOIN watches ON (watches.wid = events.wid) @@ -101,8 +101,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; diff --git a/db/schemas/lib/triggers/create/pantgrunts_view.m4 b/db/schemas/lib/triggers/create/pantgrunts_view.m4 index d178eee..a05128b 100644 --- a/db/schemas/lib/triggers/create/pantgrunts_view.m4 +++ b/db/schemas/lib/triggers/create/pantgrunts_view.m4 @@ -36,8 +36,8 @@ dnl changequote({,}) define({_show_row}, {'Value (Date) = (' || textualize(`NEW.date') - || '), Value (Focal) = (' - || textualize(`NEW.focal') + || '), Value (AnimID) = (' + || textualize(`NEW.animid') || '), Value (Time) = (' || textualize(`NEW.time') || '), Value (Actor) = (' @@ -84,7 +84,7 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () AS $$ DECLARE target_wid watches.wid%TYPE; - target_focal watches.focal%TYPE; + target_animid watches.animid%TYPE; target_commid watches.commid%TYPE; target_date watches.date%TYPE; target_type watches.type%TYPE; @@ -150,7 +150,7 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () target_time := NEW.time; IF NEW.wid IS NULL THEN - -- Use Date, Focal, and Time to query OBS. + -- Use Date, AnimID, and Time to query OBS. -- Date must be supplied - to lookup WATCHES IF NEW.date IS NULL THEN @@ -162,22 +162,22 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () || _show_row(); END IF; - -- Focal must be supplied - to lookup WATCHES - IF NEW.focal IS NULL THEN + -- AnimID must be supplied - to lookup WATCHES + IF NEW.animid IS NULL THEN RAISE EXCEPTION data_exception USING MESSAGE = 'Error on INSERT into PANTGRUNTS_VIEW' - , DETAIL = 'The supplied Focal value may not be NULL' + , DETAIL = 'The supplied AnimID value may not be NULL' || ' when WID is NULL' || ': ' || _show_row(); END IF; - SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid , obs.notes, obs.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_notes, target_event_notes FROM obs - WHERE obs.focal = NEW.focal + WHERE obs.animid = NEW.animid AND obs.date = NEW.date AND obs.behavior = 'sdb_pg_event' AND obs.start = NEW.time @@ -214,8 +214,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () -- || target_wid -- || '), Value (Date) = (' -- || target_date --- || '), Value (Focal) = (' --- || target_focal +-- || '), Value (AnimID) = (' +-- || target_animid -- || '), Value (Type) = (' -- || target_type -- || '), Value (CommID) = (' @@ -237,7 +237,7 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () SELECT watches.type INTO target_type FROM watches - WHERE watches.focal = NEW.focal + WHERE watches.animid = NEW.animid AND watches.date = NEW.date AND (watches.type = 'sdb_follow' OR watches.type = 'sdb_pantgrunt'); @@ -256,10 +256,10 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () target_wid := NEW.wid; -- Use WID and Time to query OBS - SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid , obs.eid , obs.notes, obs.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_eid , target_notes, target_event_notes FROM obs @@ -277,8 +277,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () -- -- Validate supplied values against db values -- IF (NEW.date IS NOT NULL -- AND NEW.date <> target_date) --- OR (NEW.focal IS NOT NULL --- AND NEW.focal <> target_focal) +-- OR (NEW.animid IS NOT NULL +-- AND NEW.animid <> target_animid) -- OR (NEW.type IS NOT NULL -- AND NEW.type <> target_type) -- OR (NEW.commid IS NOT NULL @@ -303,8 +303,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () -- || target_wid -- || '), Value (Date) = (' -- || target_date --- || '), Value (Focal) = (' --- || target_focal +-- || '), Value (AnimID) = (' +-- || target_animid -- || '), Value (Type) = (' -- || target_type -- || '), Value (CommID) = (' @@ -344,10 +344,10 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () target_eid := NEW.eid; -- Use EID to lookup OBS - SELECT obs.wid , obs.date , obs.focal , obs.type , obs.commid + SELECT obs.wid , obs.date , obs.animid , obs.type , obs.commid , obs.start , obs.notes, obs.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_time , target_notes, target_event_notes FROM obs @@ -360,8 +360,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () AND NEW.wid <> target_wid) OR (NEW.date IS NOT NULL AND NEW.date <> target_date) - OR (NEW.focal IS NOT NULL - AND NEW.focal <> target_focal) + OR (NEW.animid IS NOT NULL + AND NEW.animid <> target_animid) OR (NEW.type IS NOT NULL AND NEW.type <> 'sdb_follow' AND NEW.type <> 'sdb_pantgrunt') @@ -383,8 +383,8 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () || target_wid || '), Value (Date) = (' || target_date - || '), Value (Focal) = (' - || target_focal + || '), Value (AnimID) = (' + || target_animid || '), Value (Type) = (' || target_type || '), Value (CommID) = (' @@ -425,32 +425,32 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () -- Insert the DYADS row. -- WITH ins AS - (INSERT INTO dyads (wid , date , focal , type , commid + (INSERT INTO dyads (wid , date , animid , type , commid , eid , behavior , start , stop , certainty , actor_pid , actor , recipient_pid , recipient , twosided , notes , event_notes) - VALUES ( NEW.wid, NEW.date, NEW.focal, target_type, NEW.commid + VALUES ( NEW.wid, NEW.date, NEW.animid, target_type, NEW.commid , NEW.eid, 'sdb_pg_event', NEW.time, NEW.time , 'sdb_identity_certain' , NEW.actor_pid, NEW.actor , NEW.recipient_pid, NEW.recipient , NEW.twosided , NEW.notes, NEW.event_notes) - RETURNING wid, date, focal, type, commid + RETURNING wid, date, animid, type, commid , eid, start , actor_pid, actor , recipient_pid, recipient , twosided , notes, event_notes) - SELECT ins.wid , ins.date , ins.focal , ins.type , ins.commid + SELECT ins.wid , ins.date , ins.animid , ins.type , ins.commid , ins.eid , ins.start , ins.actor_pid, ins.recipient_pid , ins.twosided , ins.notes , ins.event_notes - INTO target_wid, target_date, target_focal, target_type, target_commid + INTO target_wid, target_date, target_animid, target_type, target_commid , target_eid, target_time , target_actor_pid, target_recipient_pid , target_twosided @@ -476,7 +476,7 @@ CREATE OR REPLACE FUNCTION pantgrunts_view_insert_func () -- Assign values to all the NEW columns. In theory then RETURNING -- will return the newly inserted, or existing, data values. NEW.wid := target_wid; - NEW.focal := target_focal; + NEW.animid := target_animid; NEW.commid := target_commid; NEW.date := target_date; NEW.type := target_type; diff --git a/db/schemas/lib/triggers/create/roles.m4 b/db/schemas/lib/triggers/create/roles.m4 index 19e0440..673154a 100644 --- a/db/schemas/lib/triggers/create/roles.m4 +++ b/db/schemas/lib/triggers/create/roles.m4 @@ -78,12 +78,12 @@ CREATE OR REPLACE FUNCTION roles_func () OR a_behavior = 'sdb_brec_note' OR a_behavior = 'sdb_colobus') THEN DECLARE - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; BEGIN - SELECT watches.focal, watches.date - INTO a_focal , a_date + SELECT watches.animid, watches.date + INTO a_animid , a_date FROM watches WHERE watches.wid = a_wid; IF FOUND THEN @@ -109,8 +109,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -123,16 +123,16 @@ CREATE OR REPLACE FUNCTION roles_func () DECLARE a_entrydate biography_data.entrydate%TYPE; a_departdate biography_data.departdate%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_type watches.type%TYPE; BEGIN -- The follow cannot be before the participant's entrydate. SELECT biography_data.entrydate - , watches.focal, watches.date, watches.type + , watches.animid, watches.date, watches.type INTO a_entrydate - , a_focal , a_date , a_type + , a_animid , a_date , a_type FROM biography_data , watches WHERE biography_data.animid = NEW.participant @@ -163,8 +163,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -174,9 +174,9 @@ CREATE OR REPLACE FUNCTION roles_func () -- The follow cannot be after the participant's departdate. SELECT biography_data.entrydate - , watches.focal, watches.date, watches.type + , watches.animid, watches.date, watches.type INTO a_departdate - , a_focal , a_date , a_type + , a_animid , a_date , a_type FROM biography_data , watches WHERE biography_data.animid = NEW.participant @@ -208,8 +208,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '): Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -233,7 +233,7 @@ CREATE OR REPLACE FUNCTION roles_func () a_role roles.role%TYPE; a_participant roles.participant%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date DATE; a_commid comm_ids.commid%TYPE; @@ -246,8 +246,8 @@ CREATE OR REPLACE FUNCTION roles_func () AND roles.pid <> NEW.pid; IF FOUND THEN - SELECT watches.focal, watches.date, watches.commid - INTO a_focal , a_date , a_commid + SELECT watches.animid, watches.date, watches.commid + INTO a_animid , a_date , a_commid FROM watches WHERE watches.wid = a_wid; @@ -280,8 +280,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -305,7 +305,7 @@ CREATE OR REPLACE FUNCTION roles_func () a_role roles.role%TYPE; a_participant roles.participant%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date DATE; a_commid comm_ids.commid%TYPE; @@ -321,8 +321,8 @@ CREATE OR REPLACE FUNCTION roles_func () AND NEW.role <> 'sdb_actee' THEN -- The ROLES rows for aggression, groom scan, and mating -- events must have a role of sdb_actor or sdb_actee. - SELECT watches.focal, watches.date, watches.commid - INTO a_focal , a_date , a_commid + SELECT watches.animid, watches.date, watches.commid + INTO a_animid , a_date , a_commid FROM watches WHERE watches.wid = a_wid; IF FOUND THEN @@ -345,8 +345,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -370,8 +370,8 @@ CREATE OR REPLACE FUNCTION roles_func () AND roles.role = NEW.role; IF FOUND THEN - SELECT watches.focal, watches.date, watches.commid - INTO a_focal , a_date , a_commid + SELECT watches.animid, watches.date, watches.commid + INTO a_animid , a_date , a_commid FROM watches WHERE watches.wid = a_wid; @@ -407,8 +407,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -427,16 +427,16 @@ CREATE OR REPLACE FUNCTION roles_func () OR a_behavior = 'sdb_gps' OR a_behavior = 'sdb_map') THEN DECLARE - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_commid watches.commid%TYPE; BEGIN - SELECT watches.focal, watches.date, watches.commid - INTO a_focal , a_date , a_commid + SELECT watches.animid, watches.date, watches.commid + INTO a_animid , a_date , a_commid FROM watches WHERE watches.wid = a_wid - AND NEW.participant <> watches.focal; + AND NEW.participant <> watches.animid; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on INSERT of ROLES' @@ -457,8 +457,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -479,13 +479,13 @@ CREATE OR REPLACE FUNCTION roles_func () AND NEW.role <> 'sdb_actee' AND NEW.role <> 'sdb_mutual' THEN DECLARE - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_commid watches.commid%TYPE; BEGIN - SELECT watches.focal, watches.date, watches.commid - INTO a_focal , a_date , a_commid + SELECT watches.animid, watches.date, watches.commid + INTO a_animid , a_date , a_commid FROM watches WHERE watches.wid = a_wid; RAISE EXCEPTION integrity_constraint_violation USING @@ -509,8 +509,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -537,15 +537,15 @@ CREATE OR REPLACE FUNCTION roles_func () a_role roles.role%TYPE; a_participant roles.participant%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_commid watches.commid%TYPE; BEGIN SELECT roles.pid, roles.role, roles.participant - , watches.focal, watches.date, watches.commid + , watches.animid, watches.date, watches.commid INTO a_pid , a_role , a_participant - , a_focal , a_date , a_commid + , a_animid , a_date , a_commid FROM roles , watches WHERE roles.eid = NEW.eid @@ -584,8 +584,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -646,8 +646,8 @@ CREATE OR REPLACE FUNCTION roles_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -676,7 +676,7 @@ CREATE OR REPLACE FUNCTION roles_delete_func () -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_commid watches.commid%TYPE; @@ -694,11 +694,11 @@ CREATE OR REPLACE FUNCTION roles_delete_func () -- Arrival events must have a related participant -- (Because otherwise we cannot sequence them per arriving individual.) SELECT events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid , arrivals.seq, arrivals.neststart, arrivals.nestend , arrivals.cycle INTO a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid , a_seq , a_neststart , a_nestend , a_cycle FROM events @@ -727,8 +727,8 @@ CREATE OR REPLACE FUNCTION roles_delete_func () || a_stop || '), Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -765,7 +765,7 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () a_stop events.stop%TYPE; a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_type watches.type%TYPE; a_commid watches.commid%TYPE; @@ -790,10 +790,10 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () END CASE; SELECT events.behavior ,events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid , watches.type INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid , a_type FROM events JOIN watches ON (watches.wid = events.wid) @@ -827,8 +827,8 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -843,10 +843,10 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () -- When there is a sdb_mutual Role, the event must have another ROLES -- row that has a sdb_mutual Role. SELECT events.behavior ,events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid , watches.type INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid , a_type FROM events JOIN watches ON (watches.wid = events.wid) @@ -880,8 +880,8 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -896,10 +896,10 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () -- the focal and must be the sdb_actor or the sdb_actee SELECT events.behavior ,events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid , watches.type INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid , a_type FROM events JOIN watches ON (watches.wid = events.wid) @@ -909,7 +909,7 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () (SELECT 1 FROM roles WHERE roles.eid = NEW.eid - AND roles.participant = watches.focal + AND roles.participant = watches.animid AND (roles.role = 'sdb_actor' OR roles.role = 'sdb_actee')); IF FOUND THEN @@ -935,8 +935,8 @@ CREATE OR REPLACE FUNCTION roles_insert_commit_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -965,7 +965,7 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; a_type watches.type%TYPE; a_commid watches.commid%TYPE; @@ -1014,10 +1014,10 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () AND actee_count = 0)) THEN SELECT events.behavior ,events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid , watches.type INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid , a_type FROM events JOIN watches ON (watches.wid = events.wid) @@ -1049,8 +1049,8 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -1081,10 +1081,10 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () IF mutual_count = 1 THEN SELECT events.behavior ,events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid , watches.type INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid , a_type FROM events JOIN watches ON (watches.wid = events.wid) @@ -1112,8 +1112,8 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.Type) = (' @@ -1129,19 +1129,19 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () -- the focal, who must be either the sdb_actor or the sdb_actee SELECT events.behavior ,events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = OLD.eid AND events.behavior = 'sdb_grooming' - AND watches.focal = OLD.participant + AND watches.animid = OLD.participant AND NOT EXISTS (SELECT 1 FROM roles WHERE roles.eid = OLD.eid - AND roles.participant = watches.focal + AND roles.participant = watches.animid AND (roles.role = 'sdb_actor' OR roles.role = 'sdb_actee')); IF FOUND THEN @@ -1167,8 +1167,8 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' @@ -1180,11 +1180,11 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () -- a participant and an sdb_actor or an sdb_actee. SELECT events.behavior ,events.start, events.stop - , watches.wid, watches.focal, watches.date, watches.commid + , watches.wid, watches.animid, watches.date, watches.commid , groomings.initiator, groomings.terminator , groomings.problems, groomings.extractedby INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date , a_commid + , a_wid , a_animid , a_date , a_commid , a_initiator , a_terminator , a_problems , a_extractedby FROM events @@ -1231,8 +1231,8 @@ CREATE OR REPLACE FUNCTION roles_delete_commit_func () || a_stop || '), Value (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '), Value (WATCHES.CommID) = (' diff --git a/db/schemas/lib/triggers/create/species_present.m4 b/db/schemas/lib/triggers/create/species_present.m4 index b2a3ab7..838c5b9 100644 --- a/db/schemas/lib/triggers/create/species_present.m4 +++ b/db/schemas/lib/triggers/create/species_present.m4 @@ -47,16 +47,16 @@ CREATE OR REPLACE FUNCTION species_present_func () a_stop events.stop%TYPE; -- WATCHES a_wid watches.wid%TYPE; - a_focal watches.focal%TYPE; + a_animid watches.animid%TYPE; a_date watches.date%TYPE; BEGIN -- The EVENTS.Behavior must be sdb_other_species SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date FROM events JOIN watches ON (watches.wid = events.wid) WHERE events.eid = NEW.eid @@ -82,8 +82,8 @@ CREATE OR REPLACE FUNCTION species_present_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || ')'; @@ -97,10 +97,10 @@ CREATE OR REPLACE FUNCTION species_present_func () BEGIN SELECT events.behavior, events.start, events.stop - , watches.wid, watches.focal, watches.date + , watches.wid, watches.animid, watches.date , humans.researchers, humans.nonresearchers INTO a_behavior , a_start , a_stop - , a_wid , a_focal , a_date + , a_wid , a_animid , a_date , a_researchers , a_nonresearchers FROM events JOIN watches ON (watches.wid = events.wid) @@ -130,8 +130,8 @@ CREATE OR REPLACE FUNCTION species_present_func () || a_stop || '): Key (WATCHES.WID) = (' || a_wid - || '), Value (WATCHES.Focal) = (' - || a_focal + || '), Value (WATCHES.AnimID) = (' + || a_animid || '), Value (WATCHES.Date) = (' || a_date || '): Key (HUMANS.EID) = (' diff --git a/db/schemas/lib/triggers/create/watches.m4 b/db/schemas/lib/triggers/create/watches.m4 index 2fb5e2b..97ace51 100644 --- a/db/schemas/lib/triggers/create/watches.m4 +++ b/db/schemas/lib/triggers/create/watches.m4 @@ -62,7 +62,7 @@ CREATE OR REPLACE FUNCTION watches_func () SELECT biography_data.entrydate INTO this_entrydate FROM biography_data - WHERE biography_data.animid = NEW.focal + WHERE biography_data.animid = NEW.animid AND biography_data.entrydate > NEW.date; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING @@ -71,8 +71,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' studied' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.Date || '), Value (CommID) = (' @@ -91,7 +91,7 @@ CREATE OR REPLACE FUNCTION watches_func () SELECT biography_data.departdate INTO this_departdate FROM biography_data - WHERE biography_data.animid = NEW.focal + WHERE biography_data.animid = NEW.animid AND NEW.date > biography_data.departdate; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING @@ -100,8 +100,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' longer under study' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.Date || '), Value (CommID) = (' @@ -151,8 +151,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' (before BIOGRAPHY_DATA.EntryDate)' || '): Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (CommID) = (' || NEW.commid || '), Value (Date) = (' @@ -203,8 +203,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' (after BIOGRAPHY_DATA.EntryDate)' || '): Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (CommID) = (' || NEW.commid || '), Value (Date) = (' @@ -271,8 +271,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' cycle state' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.date || '): Key (EVENTS.EID) = (' @@ -342,8 +342,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' cycle state' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.date || '): Key (EVENTS.EID) = (' @@ -418,8 +418,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' sexual swelling' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.date || '): Key (EVENTS.EID) = (' @@ -495,8 +495,8 @@ CREATE OR REPLACE FUNCTION watches_func () || ' sexual swelling' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.date || '): Key (EVENTS.EID) = (' @@ -528,7 +528,7 @@ CREATE OR REPLACE FUNCTION watches_func () END IF; IF TG_OP = 'UPDATE' - AND NEW.focal <> OLD.focal THEN + AND NEW.animid <> OLD.animid THEN -- The individual eating must be the focal. SELECT events.eid, events.behavior, events.start, events.stop @@ -540,15 +540,15 @@ CREATE OR REPLACE FUNCTION watches_func () ON (roles.eid = events.eid) WHERE events.wid = NEW.wid AND events.behavior = 'sdb_food' - AND roles.participant <> NEW.focal; + AND roles.participant <> NEW.animid; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on UPDATE of WATCHES' , DETAIL = 'The individual eating must the the focal' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.date || '): Key (EVENTS.EID) = (' @@ -600,8 +600,8 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () || ' FOLLOW_OBSERVERS' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.Date || '), Value (CommID) = (' @@ -610,7 +610,7 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () END IF; IF TG_OP = 'UPDATE' - AND NEW.focal <> OLD.focal THEN + AND NEW.animid <> OLD.animid THEN -- One of the individuals grooming must be the focal, and be either -- the sdb_actor or the sdb_actee. @@ -631,7 +631,7 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () (SELECT 1 FROM roles WHERE roles.eid = events.eid - AND roles.participant = NEW.focal + AND roles.participant = NEW.animid AND (roles.role = 'sdb_actor' OR roles.role = 'sdb_actee')); IF FOUND THEN @@ -643,8 +643,8 @@ CREATE OR REPLACE FUNCTION watches_insert_commit_func () || ' being groomed' || ': Key (WID) = (' || NEW.wid - || '), Value (Focal) = (' - || NEW.focal + || '), Value (AnimID) = (' + || NEW.animid || '), Value (Date) = (' || NEW.date || '): Key (EVENTS.EID) = (' diff --git a/db/schemas/sokwedb/indexes/create/watches.m4 b/db/schemas/sokwedb/indexes/create/watches.m4 index 21eb1bc..f2b4640 100644 --- a/db/schemas/sokwedb/indexes/create/watches.m4 +++ b/db/schemas/sokwedb/indexes/create/watches.m4 @@ -22,37 +22,37 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + Focal must be unique" + "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + AnimID must be unique" ON watches - (date, focal) + (date, animid) WHERE type = 'sdb_follow' OR type = 'sdb_brec'; CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + Focal must be unique" + "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + AnimID must be unique" ON watches - (date, focal) + (date, animid) WHERE type = 'sdb_follow' OR type = 'sdb_location'; CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + Focal must be unique" + "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + AnimID must be unique" ON watches - (date, focal) + (date, animid) WHERE type = 'sdb_follow' OR type = 'sdb_mating'; CREATE UNIQUE INDEX IF NOT EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + Focal must be unique" + "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + AnimID must be unique" ON watches - (date, focal) + (date, animid) WHERE type = 'sdb_follow' OR type = 'sdb_pantgrunt'; CREATE UNIQUE INDEX IF NOT EXISTS - "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique" + "On WATCHES where (Type)=(sdb_attendance), Date + AnimID must be unique" ON watches - (date, focal) + (date, animid) WHERE type = 'sdb_attendance'; CREATE UNIQUE INDEX IF NOT EXISTS @@ -61,10 +61,10 @@ CREATE UNIQUE INDEX IF NOT EXISTS (date) WHERE type = 'sdb_ag_scan'; -CREATE INDEX IF NOT EXISTS watches_date_focal_type ON watches - (date, focal, type); -CREATE INDEX IF NOT EXISTS watches_focal ON watches - (focal); +CREATE INDEX IF NOT EXISTS watches_date_animid_type ON watches + (date, animid, type); +CREATE INDEX IF NOT EXISTS watches_animid ON watches + (animid); CREATE INDEX IF NOT EXISTS watches_commid ON watches (commid); CREATE INDEX IF NOT EXISTS watches_commid ON watches diff --git a/db/schemas/sokwedb/indexes/drop/watches.m4 b/db/schemas/sokwedb/indexes/drop/watches.m4 index 9736e13..684c2fe 100644 --- a/db/schemas/sokwedb/indexes/drop/watches.m4 +++ b/db/schemas/sokwedb/indexes/drop/watches.m4 @@ -22,24 +22,24 @@ include(`constants.m4')dnl include(`indexmacros.m4')dnl DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + Focal must be unique"; + "(Type)=(sdb_follow) or (Type)=(sdb_brec) means Date + AnimID must be unique"; DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + Focal must be unique"; + "(Type)=(sdb_follow) or (Type)=(sdb_location) means Date + AnimID must be unique"; DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + Focal must be unique"; + "(Type)=(sdb_follow) or (Type)=(sdb_mating) means Date + AnimID must be unique"; DROP INDEX IF EXISTS - "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + Focal must be unique"; + "(Type)=(sdb_follow) or (Type)=(sdb_pantgrunt) means Date + AnimID must be unique"; DROP INDEX IF EXISTS - "On WATCHES where (Type)=(sdb_attendance), Date + Focal must be unique"; + "On WATCHES where (Type)=(sdb_attendance), Date + AnimID must be unique"; DROP INDEX IF EXISTS "On WATCHES where (Type)=(sdb_ag_scan), Date must be unique"; -DROP INDEX IF EXISTS watches_date_focal_type; -DROP INDEX IF EXISTS watches_focal; +DROP INDEX IF EXISTS watches_date_animid_type; +DROP INDEX IF EXISTS watches_animid; DROP INDEX IF EXISTS watches_commid; DROP INDEX IF EXISTS watches_type; diff --git a/db/schemas/sokwedb/tables/create/watches.m4 b/db/schemas/sokwedb/tables/create/watches.m4 index 0109795..679a77f 100644 --- a/db/schemas/sokwedb/tables/create/watches.m4 +++ b/db/schemas/sokwedb/tables/create/watches.m4 @@ -42,7 +42,7 @@ changequote(`,') CREATE TABLE watches ( key_column(`WATCHES', `WID', INTEGER) - ,animid_type_column(`focal', `Focal', `NOT NULL') + ,animid_type_column(`animid', `AnimID', `NOT NULL') ,commid TEXT NOT NULL REFERENCES comm_ids ,date DATE NOT NULL diff --git a/db/schemas/sokwedb/views/create/dyads.m4 b/db/schemas/sokwedb/views/create/dyads.m4 index dbff0af..784b690 100644 --- a/db/schemas/sokwedb/views/create/dyads.m4 +++ b/db/schemas/sokwedb/views/create/dyads.m4 @@ -26,7 +26,7 @@ dnl CREATE OR REPLACE VIEW dyads ( wid ,date - ,focal + ,animid ,type ,commid ,eid @@ -46,7 +46,7 @@ CREATE OR REPLACE VIEW dyads ( SELECT obs.wid ,obs.date - ,obs.focal + ,obs.animid ,obs.type ,obs.commid ,obs.eid diff --git a/db/schemas/sokwedb/views/create/obs.m4 b/db/schemas/sokwedb/views/create/obs.m4 index d9bc1be..13a07c2 100644 --- a/db/schemas/sokwedb/views/create/obs.m4 +++ b/db/schemas/sokwedb/views/create/obs.m4 @@ -26,7 +26,7 @@ dnl CREATE OR REPLACE VIEW obs ( wid ,date - ,focal + ,animid ,type ,commid ,eid @@ -41,7 +41,7 @@ CREATE OR REPLACE VIEW obs ( SELECT watches.wid ,watches.date - ,watches.focal + ,watches.animid ,watches.type ,watches.commid ,events.eid diff --git a/db/schemas/sokwedb/views/create/pantgrunts_view.m4 b/db/schemas/sokwedb/views/create/pantgrunts_view.m4 index ed6848c..c8d058f 100644 --- a/db/schemas/sokwedb/views/create/pantgrunts_view.m4 +++ b/db/schemas/sokwedb/views/create/pantgrunts_view.m4 @@ -25,7 +25,7 @@ dnl CREATE OR REPLACE VIEW pantgrunts_view ( date - ,focal + ,animid ,time ,actor ,recipient @@ -48,7 +48,7 @@ CREATE OR REPLACE VIEW pantgrunts_view ( AS SELECT dyads.date AS date - ,dyads.focal AS focal + ,dyads.animid AS animid ,dyads.start AS time ,dyads.actor AS actor ,dyads.recipient AS recipient diff --git a/doc/diagrams/dyads.svg b/doc/diagrams/dyads.svg index ead599b..c1b13f0 100644 --- a/doc/diagrams/dyads.svg +++ b/doc/diagrams/dyads.svg @@ -1152,7 +1152,7 @@ id="tspan2100-9-1-3-1-6-5-9" x="17.413933" y="32.173676" - style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:2.82222px;font-family:Sans;-inkscape-font-specification:'Sans, Normal';font-variant-ligatures:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;stroke-width:0.264205px">FocalAnimIDFocalAnimIDFocalAnimIDFocalAnimID` .. |WATCHES.WID| replace:: :ref:`WID ` -.. |WATCHES.Focal| replace:: - :ref:`Focal ` +.. |WATCHES.AnimID| replace:: + :ref:`AnimID ` .. |WATCHES.CommID| replace:: :ref:`CommID ` .. |WATCHES.Date| replace:: diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index 0a821df..db8fe78 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -245,7 +245,7 @@ The following table lists these rules and implications: There can be no |ROLES| rows related to the event. The concept behind this is that, even though B-Record notes are associated with an individual, the related |WATCHES|.\ - |WATCHES.Focal|, B-Record note translations are an administrative + |WATCHES.AnimID|, B-Record note translations are an administrative record. The focal of the B-Record follow, therefore, is not playing a paricular role in some behavioral category and so there should be @@ -325,7 +325,7 @@ The following table lists these rules and implications: follow. 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 follow that is related to the food event. + |WATCHES.AnimID| of the follow that is related to the food event. The EVENTS.\ |EVENTS.Start| column contains the time the food bout began. @@ -375,7 +375,7 @@ The following table lists these rules and implications: 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 + |WATCHES.AnimID| of the |WATCHES| row related to the GPS location event. Both the EVENTS.\ |EVENTS.Start| and EVENTS.\ |EVENTS.Stop| @@ -440,7 +440,7 @@ The following table lists these rules and implications: This means that on of the the |ROLES| rows related to the event must have a |ROLES|.\ |ROLES.Role| value of either ``sdb_actor`` or ``sdb_actee``, and have a |ROLES|.\ - |ROLES.Participant| value equal to the |WATCHES|.\ |WATCHES.Focal| + |ROLES.Participant| value equal to the |WATCHES|.\ |WATCHES.AnimID| of the follow that is related to the grooming event. For grooming events, the EVENTS.\ |EVENTS.Certainty| column @@ -536,7 +536,7 @@ The following table lists these rules and implications: 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 + |WATCHES.AnimID| of the |WATCHES| row related to the map location event. Both the EVENTS.\ |EVENTS.Start| and EVENTS.\ |EVENTS.Stop| diff --git a/doc/src/tables/watches.m4 b/doc/src/tables/watches.m4 index 017f1b8..fdd3db7 100644 --- a/doc/src/tables/watches.m4 +++ b/doc/src/tables/watches.m4 @@ -61,7 +61,7 @@ The available |WATCHES.Type| values are: the WATCHES row recording the presence of the individual at the feeding station on the given day. - The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual that appeared at the feeding station. @@ -80,7 +80,7 @@ The available |WATCHES.Type| values are: have a related |EVENTS| row where the |EVENTS.Behavior| code is ``sdb_arrival_a``, and a |ROLES| row, related to the event, where the |ROLES|.\ |ROLES.Participant| has the same value as the - |WATCHES.Focal| column. + |WATCHES.AnimID| column. The system will generate a warning if the ``sdb_male_swelling`` code is assigned to a female, an individual with a |BIOGRAPHY_DATA|.\ @@ -111,7 +111,7 @@ The available |WATCHES.Type| values are: For any given individual there may not be more than one record of daily attendance at the feeding station on any given day. - This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and |WATCHES.Date| must be unique. If an individual arrives at and leaves the feeding station multiple times on a single day, the one WATCHES row has @@ -122,7 +122,7 @@ The available |WATCHES.Type| values are: notes translated into English, but there is no follow on record in the database. - The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual. The |WATCHES.CommID| column contains the code for the community @@ -133,7 +133,7 @@ The available |WATCHES.Type| values are: There may only be one row on WATCHES per day to record an individual who has translated B-Record notes. - This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and |WATCHES.Date| must be unique. If there were multiple times on a single day that B-Record notes were taken, the one WATCHES row is related to multiple rows on the @@ -146,7 +146,7 @@ The available |WATCHES.Type| values are: A follow may or may not have any related observations, related |EVENTS| rows. - The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the focal of the follow. The |WATCHES.CommID| column contains the code for the community @@ -163,7 +163,7 @@ The available |WATCHES.Type| values are: |EVENTS| row where the |EVENTS.Behavior| code is ``sdb_arrival``, and a |ROLES| row, related to the event, where the |ROLES|.\ |ROLES.Participant| has the same value as the follow's - |WATCHES.Focal| column. + |WATCHES.AnimID| column. A follow must have at least one related |FOLLOW_OBSERVERS| rows, to record the observers scheduled to conduct the follow. @@ -171,7 +171,7 @@ The available |WATCHES.Type| values are: There may only be one row on WATCHES to record a follow of any given individual on any given day. - This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and |WATCHES.Date| must be unique. ``sdb_ag_scan`` (Attendance Groom scan) @@ -182,11 +182,11 @@ The available |WATCHES.Type| values are: The expectation is that the individual associated *with* *the* *scan* (not the grooming), the individual recorded in the - |WATCHES.Focal| column, will always be ``sdb_unk``. + |WATCHES.AnimID| column, will always be ``sdb_unk``. The system will generate a warning when the associated individual - is not ``sdb_unk``, when |WATCHES.Focal| is not ``sdb_unk``. + is not ``sdb_unk``, when |WATCHES.AnimID| is not ``sdb_unk``. - The |WATCHES.Focal| column contains little information that is + The |WATCHES.AnimID| column contains little information that is useful, because feeding station groom scans were not associated with any type of observation that specifically targeted a particular individual. @@ -210,7 +210,7 @@ The available |WATCHES.Type| values are: Each row represents a date on which an individual was spatially located. - The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the spatially located individual. The |WATCHES.CommID| column contains the code for the community @@ -221,7 +221,7 @@ The available |WATCHES.Type| values are: There may only be one row on WATCHES per day to record an individual who was spatially located. - This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and |WATCHES.Date| must be unique. If an individual's location is recorded multiple times on a single day, the one WATCHES row is related to multiple rows @@ -235,15 +235,15 @@ The available |WATCHES.Type| values are: non-meaningful |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| -- often ``sdb_unk``. - The |WATCHES.Focal| column contains little information that is + The |WATCHES.AnimID| column contains little information that is useful. - The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual that was purportedly the focal of a follow, but no such follow of the individual exists. When there is no focal on record, for whatever reason, the special |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value of ``sdb_unk``, representing an unknown individual -- or, in this case, "no - individual", is expected to be the |WATCHES.Focal| value. + individual", is expected to be the |WATCHES.AnimID| value. The |WATCHES.CommID| column contains the code for the community recorded at along with the mating; the |COMM_IDS|.\ @@ -253,7 +253,7 @@ The available |WATCHES.Type| values are: There may only be one row on WATCHES per day, per individual recorded along with the mating data. - This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and |WATCHES.Date| must be unique. When multiple matings are recorded in a follow, or are recorded on some day when there is no follow, the one WATCHES @@ -267,15 +267,15 @@ The available |WATCHES.Type| values are: non-meaningful |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| -- often ``sdb_unk``. - The |WATCHES.Focal| column contains little information that is + The |WATCHES.AnimID| column contains little information that is useful. - The |WATCHES.Focal| column contains the |BIOGRAPHY_DATA|.\ + The |WATCHES.AnimID| column contains the |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| of the individual that was purportedly the focal of a follow, but no such follow of the individual exists. When there is no focal on record, for whatever reason, the special |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| value of ``sdb_unk``, representing an unknown individual -- or, in this case, "no - individual", is expected to be the |WATCHES.Focal| value. + individual", is expected to be the |WATCHES.AnimID| value. The |WATCHES.CommID| column contains the code for the community recorded at along with the pantgrunt; the |COMM_IDS|.\ @@ -285,7 +285,7 @@ The available |WATCHES.Type| values are: There may only be one row on WATCHES per day, per individual recorded along with the pantgrunt data. - This means, the combination of |WATCHES.Type|, |WATCHES.Focal| and + This means, the combination of |WATCHES.Type|, |WATCHES.AnimID| and |WATCHES.Date| must be unique. When multiple pantgrunts are recorded in a follow, or are recorded on some day when there is no follow, the one WATCHES @@ -337,7 +337,7 @@ there are only pantgrunt related events. 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 -individual's -- the |WATCHES.Focal|'s -- |BIOGRAPHY_DATA|.\ +individual's -- the |WATCHES.AnimID|'s -- |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryDate| and on or before the watched individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|. @@ -355,20 +355,21 @@ WID (Watch period ID) |WATCHES.WID_summary| |notnull| -.. _WATCHES.Focal: +.. _WATCHES.AnimID: -Focal (Focal animal id) -``````````````````````` +AnimID (focal or other watched ANIMal ID) +````````````````````````````````````````` -.. |WATCHES.Focal_summary| replace:: +.. |WATCHES.AnimID_summary| replace:: The |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| identifying the individual under observation. -|WATCHES.Focal_summary| +|WATCHES.AnimID_summary| -The designated individual is the focal, or the individual observed at -the feeding station, etc., depending on the |WATCHES.Type| value. +The designated individual that is the focal of a follow, or the +individual observed at the feeding station, or ``sdb_unk`` -- the +unknown individual --, etc., depending on the |WATCHES.Type| value. |notnull| @@ -382,7 +383,7 @@ CommID A code for the community the observers have recorded for association with the watch period. - This is not necessarily the community the |WATCHES.Focal| is a + This is not necessarily the community the |WATCHES.AnimID| is a member of, although it usually is. |WATCHES.CommID_summary| diff --git a/doc/src/views/dyads.m4 b/doc/src/views/dyads.m4 index 7bf5721..8f69d3f 100644 --- a/doc/src/views/dyads.m4 +++ b/doc/src/views/dyads.m4 @@ -94,13 +94,13 @@ Columns of the DYADS View +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Date | |WATCHES|.\ |WATCHES.Date| | Date of the event | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | Focal | |WATCHES|.\ |WATCHES.Focal| | Focal of follow, or focal of a non-existant follow, or an | + | AnimID | |WATCHES|.\ |WATCHES.AnimID| | Focal of follow, or focal of a non-existant follow, or an | | | | un-interesting AnimID | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Type | |WATCHES|.\ |WATCHES.Type| | Category of observation, often determining the data collection | | | | protocol: follow, feeding station attendance, groom scans, etc. | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | CommID | |WATCHES|.\ |WATCHES.CommID| | The community identifier associated with the Date/Focal/Type | + | CommID | |WATCHES|.\ |WATCHES.CommID| | The community identifier associated with the Date/AnimID/Type | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | EID | |EVENTS|.\ |EVENTS.EID| | Identifier of the |EVENTS| row | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ @@ -127,7 +127,7 @@ Columns of the DYADS View | TwoSided | An expression based on |ROLES|.\ |ROLES.Role| | Boolean, |true| when both the Actor and the Recipient were | | | | performing the action | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | Notes | |WATCHES|.\ |WATCHES.Notes| | Textual notes on the observation for the Date/Focal/Type | + | Notes | |WATCHES|.\ |WATCHES.Notes| | Textual notes on the observation for the Date/AnimID/Type | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Event_Notes | |EVENTS|.\ |EVENTS.Notes| | Textual notes on the event | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ @@ -143,7 +143,7 @@ INSERT One row may also be inserted into |EVENTS|, and one may be inserted into |WATCHES|. - The columns EID, WID, Focal, Date, Type, Behavior, Start, and Stop + The columns EID, WID, AnimID, Date, Type, Behavior, Start, and Stop may all be used, in the various combinations described below, to relate the new |ROLES| rows to existing |EVENTS| rows, or to create new |EVENTS| and |WATCHES| rows when they do not already exist. @@ -151,7 +151,7 @@ INSERT If an EID is supplied, that is all that is required. The identified |EVENTS| row must already exist. - If an EID is not supplied, the columns WID, Focal, Date, Type, + If an EID is not supplied, the columns WID, AnimID, Date, Type, Behavior, Start, and Stop are used to query |OBS| to discover if a matching |EVENTS| row exists or if there is no match and new one must be inserted. @@ -159,7 +159,7 @@ INSERT If a WID is supplied it is used, along with Behavior, Start, and Stop, to match against database content. - If a WID is not supplied, the Focal, Date, and Type columns must + If a WID is not supplied, the AnimID, Date, and Type columns must be supplied. Then they are used, along with Behavior, Start, and Stop, to match against database content. diff --git a/doc/src/views/obs.m4 b/doc/src/views/obs.m4 index 3534a3b..390abdd 100644 --- a/doc/src/views/obs.m4 +++ b/doc/src/views/obs.m4 @@ -44,24 +44,23 @@ bananas eaten. .. code-block:: sql :caption: Arrivals/departures at the feeding station with banana count - SELECT obs.wid, obs.eid, obs.date - , obs.focal AS animid + SELECT obs.wid, obs.eid, obs.date, obs.animid , obs.start AS arrived, obs.stop AS departed , arrivals_a.bananas FROM obs JOIN arrivals_a ON (arrivals_a.eid = obs.eid) - ORDER BY obs.focal, obs.date, obs.start; + ORDER BY obs.animid, obs.date, obs.start; It is important to remember that the |BIOGRAPHY_DATA.AnimID| value -presented, the OBS.\ |WATCHES.Focal| column, is the value found in -|WATCHES|.\ |WATCHES.Focal|. +presented, the OBS.\ |WATCHES.AnimID| column, is the value found in +|WATCHES|.\ |WATCHES.AnimID|. This is *not* always the individual observed to have exhibited some behavior or otherwise participated in the event. *That* individual is found in a related |ROLES| row, in the |ROLES|.\ |ROLES.Participant| column. For example, grooming information is recorded in B-Record follows. -So the OBS.\ |WATCHES.Focal| is the focal of the follow, not +So the OBS.\ |WATCHES.AnimID| is the focal of the follow, not necessarily the individual doing the grooming. To find every (unique) grooming event, from the groomings recording during B-Record interval scanning, and the individual doing the @@ -95,21 +94,20 @@ exhibiting a behavior\ [#f3]_: GROUP BY roles.participant, obs.date ORDER BY roles.participant, obs.date; -The OBS.\ |WATCHES.Focal| column contains the chimpanzee who, in some +The OBS.\ |WATCHES.AnimID| column contains the chimpanzee who, in some sense, is the reason why the observers were present. Often this is the focal of the follow that was ongoing when the event occurred.\ [#f1]_ -(Hence the name OBS.\ |WATCHES.Focal|.) Feeding station attendance observations have for their OBS.\ -|WATCHES.Focal| value, the individual who arrived at the feeding +|WATCHES.AnimID| value, the individual who arrived at the feeding station.\ [#f2]_ Other kinds of events, distinguished by their OBS.\ |EVENTS.Behavior| values, have other rules regarding the identity of the individual -recorded in the OBS.\ |WATCHES.Focal| column. +recorded in the OBS.\ |WATCHES.AnimID| column. See the documentation of the |EVENTS| table for more information. Many events are expected to have the unknown individual, ``sdb_unk``, -as their OBS.\ |WATCHES.Focal| value -- indicating that the field team +as their OBS.\ |WATCHES.AnimID| value -- indicating that the field team had no particular intention to conduct an observation but, instead, happened to observe the event. An example of this could be a pantgrunt event observed while the field @@ -156,13 +154,13 @@ Columns of the OBS View +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Date | |WATCHES|.\ |WATCHES.Date| | Date of the event | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | Focal | |WATCHES|.\ |WATCHES.Focal| | Focal of follow, or focal of a non-existant follow, or an | + | AnimID | |WATCHES|.\ |WATCHES.AnimID| | Focal of follow, or focal of a non-existant follow, or an | | | | un-interesting AnimID | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Type | |WATCHES|.\ |WATCHES.Type| | Category of observation, often determining the data collection | | | | protocol: follow, feeding station attendance, groom scans, etc. | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | CommID | |WATCHES|.\ |WATCHES.CommID| | The community identifier associated with the Date/Focal/Type | + | CommID | |WATCHES|.\ |WATCHES.CommID| | The community identifier associated with the Date/AnimID/Type | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | EID | |EVENTS|.\ |EVENTS.EID| | Identifier of the |EVENTS| row | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ @@ -174,7 +172,7 @@ Columns of the OBS View +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Certainty | |EVENTS|.\ |EVENTS.Certainty| | Certainty of the event observation, when meaningful | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | Notes | |WATCHES|.\ |WATCHES.Notes| | Textual notes on the observation for the Date/Focal/Type | + | Notes | |WATCHES|.\ |WATCHES.Notes| | Textual notes on the observation for the Date/AnimID/Type | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Event_Notes | |EVENTS|.\ |EVENTS.Notes| | Textual notes on the event | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ @@ -193,7 +191,7 @@ INSERT If a WID is supplied, the identified |WATCHES| table row must already exist. - If a WID is not supplied, the Date, Focal, and Type columns must be + If a WID is not supplied, the Date, AnimID, and Type columns must be supplied. Their values are matched to existing database content. If an existing |WATCHES| row matches, the new |EVENTS| row is @@ -260,11 +258,11 @@ DELETE The |ROLES|.\ |ROLES.Participant| column is the recommened column to obtain event participant information from, only because you don't need to be aware of all the rules regarding what might be in - the OBS.\ |WATCHES.Focal| column. + the OBS.\ |WATCHES.AnimID| column. The |ROLES|.\ |ROLES.Participant| column is always there no matter the kind of event. - That said, it is sometimes convenient to use OBS.\ |WATCHES.Focal|. + That said, it is sometimes convenient to use OBS.\ |WATCHES.AnimID|. The point is, care must be taken when querying to ensure that the query actually retrieves the information that it is expected to. diff --git a/doc/src/views/pantgrunts_view.m4 b/doc/src/views/pantgrunts_view.m4 index 58496e9..ad42abe 100644 --- a/doc/src/views/pantgrunts_view.m4 +++ b/doc/src/views/pantgrunts_view.m4 @@ -77,7 +77,7 @@ Columns of the PANTGRUNTS_VIEW View +=================+=================================================+=====================================================================+ | Date | |WATCHES|.\ |WATCHES.Date| | Date of pantgrunt | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ - | Focal | |WATCHES|.\ |WATCHES.Focal| | Focal of follow, or focal of a non-existant follow, or an | + | AnimID | |WATCHES|.\ |WATCHES.AnimID| | Focal of follow, or focal of a non-existant follow, or an | | | | un-interesting AnimID | +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ | Time | |EVENTS|.\ |EVENTS.Start| | Time of pantgrunt | @@ -138,7 +138,7 @@ INSERT One row may be inserted into |EVENTS|, and one may be inserted into |WATCHES|. - The columns EID, WID, Focal, Date, and Time may all be used, in the + The columns EID, WID, AnimID, Date, and Time may all be used, in the various combinations described below, to relate new |PANTGRUNTS| and |ROLES| rows to existing |EVENTS| rows, or to create new |EVENTS| and |WATCHES| rows when they do not already exist. @@ -153,7 +153,7 @@ INSERT If a WID is supplied it is used, along with Time, to match against database content. - If a WID is not supplied, the Focal and Date columns must + If a WID is not supplied, the AnimID and Date columns must be supplied. Then they are used, along with Time, to match against database content. -- 2.34.1