From 9f3ba29ebc973909bbbf7e04d23c6db2930f43b9 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 1 Jul 2026 00:03:19 +0000 Subject: [PATCH] Create the OBS view --- db/schemas/lib/triggers/Makefile | 1 + db/schemas/lib/triggers/create/obs.m4 | 394 ++++++++ db/schemas/lib/triggers/drop/obs.m4 | 25 + db/schemas/sokwedb/views/Makefile | 1 + db/schemas/sokwedb/views/create/obs.m4 | 58 ++ doc/diagrams/obs.svg | 1199 ++++++++++++++++++++++++ doc/src/epilog.inc.m4 | 1 + doc/src/views.m4 | 19 +- doc/src/views/obs.m4 | 273 ++++++ 9 files changed, 1963 insertions(+), 8 deletions(-) create mode 100644 db/schemas/lib/triggers/create/obs.m4 create mode 100644 db/schemas/lib/triggers/drop/obs.m4 create mode 100644 db/schemas/sokwedb/views/create/obs.m4 create mode 100644 doc/diagrams/obs.svg create mode 100644 doc/src/views/obs.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index e66e5ab..a64cf45 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -45,6 +45,7 @@ ORDER := comm_ids \ species_present \ repro_states \ pantgrunts \ + obs \ pantgrunts_view \ brecord_notes \ colobus \ diff --git a/db/schemas/lib/triggers/create/obs.m4 b/db/schemas/lib/triggers/create/obs.m4 new file mode 100644 index 0000000..d8fdca9 --- /dev/null +++ b/db/schemas/lib/triggers/create/obs.m4 @@ -0,0 +1,394 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify it +dnl under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Triggers for the obs view +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + +dnl Plpgsql fragment for use in errors to show content of NEW row +dnl +dnl Syntax: _show_row() +dnl +dnl Remarks: +dnl This makes everything more readable. +dnl +changequote({,}) +define({_show_row}, {'Key (WID) = (' + || textualize(`NEW.wid') + || '), Value (Date) = (' + || textualize(`NEW.date') + || '), Value (Focal) = (' + || textualize(`NEW.focal') + || '), Value (Type) = (' + || textualize(`NEW.type') + || '), Value (CommID) = (' + || textualize(`NEW.commid') + || '), Key (EID) = (' + || textualize(`NEW.eid') + || '), Value (Behavior) = (' + || textualize(`NEW.behavior') + || '), Value (Start) = (' + || textualize(`NEW.start') + || '), Value (Stop) = (' + || textualize(`NEW.stop') + || '), Value (Certainty) = (' + || textualize(`NEW.certainty') + || '), Value (Notes) = (' + || textualize(`NEW.notes') + || '), Value (Event_Notes) = (' + || textualize(`NEW.event_notes') + || ')'dnl +}) +changequote(`,') + + +RAISE INFO 'obs_insert_func'; +CREATE OR REPLACE FUNCTION obs_insert_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + target_wid watches.wid%TYPE; + target_focal watches.focal%TYPE; + target_commid watches.commid%TYPE; + target_date watches.date%TYPE; + target_type watches.type%TYPE; + target_notes watches.notes%TYPE; + + assign_wid BOOLEAN := FALSE; + assign_notes BOOLEAN := FALSE; + assign_watches_rest BOOLEAN := FALSE; + + target_eid events.eid%TYPE; + + BEGIN + -- Function for obs instead of insert trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + -- + -- Remarks: + -- Do not enforce any conditions on the ID values. The place + -- to do that is in the triggers on the tables, so the rules are + -- consistent no matter the interface used to change table content. + -- + -- The NEW row is updated with actual DB values, on the theory + -- that this will feed an INSERT's RETURNING clause. + + -- + -- Initial validation of data supplied + -- + + IF NEW.WID IS NULL THEN + -- No WID supplied + + -- Date must be supplied - to lookup WATCHES + IF NEW.date IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into OBS' + , DETAIL = 'The supplied Date value may not be NULL' + || ': ' + || _show_row(); + END IF; + + -- Focal must be supplied - to lookup WATCHES + IF NEW.focal IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into OBS' + , DETAIL = 'The supplied Focal value may not be NULL' + || ': ' + || _show_row(); + END IF; + + -- Type must be supplied - to lookup WATCHES + IF NEW.type IS NULL THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into OBS' + , DETAIL = 'The supplied Type value may not be NULL' + || ': ' + || _show_row(); + END IF; + + ELSE -- NEW.WID IS NOT NULL, WID is supplied + target_wid := NEW.wid; + + -- Get all the WATCHES columns and be sure they match. + -- (None can be NULL) + SELECT watches.focal, watches.commid, watches.date, watches.type + , watches.notes + INTO target_focal , 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) + OR (NEW.commid IS NOT NULL + AND NEW.commid <> target_commid) + OR (NEW.date IS NOT NULL + AND NEW.date <> target_date) + OR (NEW.type IS NOT NULL + AND NEW.type <> target_type) + OR (NEW.notes IS NOT NULL + AND NEW.notes <> target_notes) THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into OBS' + , DETAIL = 'The values in the database for the WATCHES' + || ' row with (WATCHES.WID) = (' + || NEW.wid + || '), which are:' + || ' Key (WID) = (' + || NEW.wid + || '), Value (Date) = (' + || target_date + || '), Value (Focal) = (' + || target_focal + || '), Value (Type) = (' + || target_type + || '), Value (CommID) = (' + || target_commid + || '), Value (Notes) = (' + || target_notes + || ') do not match the values supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + + -- Fill out NEW with data values. (Don't bother with testing if + -- this is necessary.) + assign_watches_rest := TRUE; + assign_notes := TRUE; + ELSE + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into OBS' + , DETAIL = 'The WATCHES row with (WATCHES.WID) = (' + || NEW.wid + || ') does not exist' + || ': ' + || _show_row(); + END IF; + END IF; + + -- The requirement that there be non-NULL data in the rest of the columns + -- is checked by the underlying tables. + + -- + -- Validation complete + -- + + IF NEW.WID IS NULL THEN + -- + -- Find the WATCHES row to use, or create one + -- + + SELECT watches.wid, watches.notes + INTO target_wid , target_notes + FROM watches + WHERE watches.focal = NEW.focal + AND watches.date = NEW.date + AND watches.type = NEW.type; + + IF FOUND THEN + IF NEW.notes IS NOT NULL + AND NEW.notes <> target_notes THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into OBS' + , DETAIL = 'The value in the database, (Notes) = (' + || target_notes + || '), does not match the value supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + + -- Fill out NEW with data values. (Don't bother with testing if + -- this is necessary.) + assign_wid := TRUE; + assign_notes := TRUE; + + ELSE -- NOT FOUND, no existing WATCHES row + INSERT INTO watches (focal, commid, date, type, notes) + VALUES (NEW.focal + ,NEW.commid + ,NEW.date + ,NEW.type + ,NEW.notes); + target_wid := CURRVAL('watches_wid_seq'); + assign_wid := TRUE; + END IF; + END IF; + + -- + -- Create the EVENTS row + -- + INSERT INTO events (wid, behavior, start, stop, certainty, notes) + VALUES (target_wid + ,NEW.behavior + ,NEW.start + ,NEW.stop + ,NEW.certainty + ,NEW.event_notes); + target_eid := CURRVAL('events_eid_seq'); + + IF NEW.eid IS NOT NULL + AND NEW.eid <> target_eid THEN + RAISE EXCEPTION data_exception USING + MESSAGE = 'Error on INSERT into OBS' + , DETAIL = 'The value of the EVENTS.EID inserted into the database,' + || ' (EID) = (' + || target_eid + || '), does not match the value supplied to the INSERT' + || ': ' + || _show_row(); + END IF; + + -- Fill out NEW with data values. + -- Don't do this until after all errors are raised so that + -- the data reported back to the user is the data the user supplied. + + IF assign_wid THEN + NEW.wid := target_wid; + END IF; + + IF assign_watches_rest THEN + NEW.focal := target_focal; + NEW.date := target_date; + NEW.commid := target_commid; + NEW.type := target_type; + END IF; + + IF assign_notes THEN + NEW.notes := target_notes; + END IF; + + NEW.eid := target_eid; + + RETURN NEW; + END; +$$; + + +RAISE INFO 'obs_update_func'; +CREATE OR REPLACE FUNCTION obs_update_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + changed BOOLEAN := FALSE; + + BEGIN + -- Function for obs instead of update trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + -- + -- Remarks: + -- Let the underlying tables do the validating. + + cannot_change(`OBS', `WID') + cannot_change(`OBS', `EID') + + IF NEW.focal <> OLD.focal + 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 + , commid = NEW.commid + , date = NEW.date + , type = NEW.type + , notes = NEW.notes + WHERE wid = OLD.wid; + + changed = TRUE; + END IF; + + IF NEW.behavior <> OLD.behavior + OR NEW.start <> OLD.start + OR NEW.stop <> OLD.stop + OR NEW.certainty <> OLD.certainty + OR NEW.event_notes <> OLD.event_notes THEN + UPDATE events + SET behavior = NEW.behavior + , start = NEW.start + , stop = NEW.stop + , certainty = NEW.certainty + , notes = NEW.event_notes + WHERE eid = OLD.eid; + + changed := TRUE; + END IF; + + IF changed THEN + RETURN NEW; + ELSE + RETURN NULL; + END IF; + END; +$$; + + +RAISE INFO 'obs_delete_func'; +CREATE OR REPLACE FUNCTION obs_delete_func () + RETURNS trigger + LANGUAGE plpgsql + sdb_function_set_search_path + AS $$ + DECLARE + changed BOOLEAN := FALSE; + + BEGIN + -- Function for obs instead of delete trigger + -- + -- AGPL_notice(` --', `2026', + `The Meme Factory, Inc., www.karlpinc.com') + -- + DELETE FROM events + WHERE events.eid = OLD.eid; + + DELETE FROM watches + WHERE watches.wid = OLD.wid + AND NOT EXISTS + (SELECT 1 + FROM events + WHERE events.wid = OLD.wid); + + RETURN OLD; + END; +$$; + + +CREATE TRIGGER obs_insert_trigger + INSTEAD OF INSERT + ON obs FOR EACH ROW + EXECUTE PROCEDURE obs_insert_func(); + +CREATE TRIGGER obs_update_trigger + INSTEAD OF UPDATE + ON obs FOR EACH ROW + EXECUTE PROCEDURE obs_update_func(); + +CREATE TRIGGER obs_delete_trigger + INSTEAD OF DELETE + ON obs FOR EACH ROW + EXECUTE PROCEDURE obs_delete_func(); diff --git a/db/schemas/lib/triggers/drop/obs.m4 b/db/schemas/lib/triggers/drop/obs.m4 new file mode 100644 index 0000000..02f5665 --- /dev/null +++ b/db/schemas/lib/triggers/drop/obs.m4 @@ -0,0 +1,25 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published by +dnl the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Drop triggers for obs view +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl + +DROP FUNCTION IF EXISTS obs_insert_func() CASCADE; +DROP FUNCTION IF EXISTS obs_update_func() CASCADE; +DROP FUNCTION IF EXISTS obs_delete_func CASCADE; diff --git a/db/schemas/sokwedb/views/Makefile b/db/schemas/sokwedb/views/Makefile index 1f64fa2..a9fd519 100644 --- a/db/schemas/sokwedb/views/Makefile +++ b/db/schemas/sokwedb/views/Makefile @@ -21,6 +21,7 @@ # database. This is important because views must be put into the # database after the views they reference. ORDER := biography \ + obs \ pantgrunts_view ## diff --git a/db/schemas/sokwedb/views/create/obs.m4 b/db/schemas/sokwedb/views/create/obs.m4 new file mode 100644 index 0000000..d9bc1be --- /dev/null +++ b/db/schemas/sokwedb/views/create/obs.m4 @@ -0,0 +1,58 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`tablemacros.m4')dnl +include(`grants.m4')dnl +dnl + +CREATE OR REPLACE VIEW obs ( + wid + ,date + ,focal + ,type + ,commid + ,eid + ,behavior + ,start + ,stop + ,certainty + ,notes + ,event_notes + ) + AS + SELECT + watches.wid + ,watches.date + ,watches.focal + ,watches.type + ,watches.commid + ,events.eid + ,events.behavior + ,events.start + ,events.stop + ,events.certainty + ,watches.notes + ,events.notes + FROM watches + JOIN events + ON (events.wid = watches.wid); + +grant_priv(`OBS') diff --git a/doc/diagrams/obs.svg b/doc/diagrams/obs.svg new file mode 100644 index 0000000..f94ef58 --- /dev/null +++ b/doc/diagrams/obs.svg @@ -0,0 +1,1199 @@ + +image/svg+xmlWATCHESWIDCommIDTypeNotesDateFocalEVENTSEIDBehaviorWIDStartStopCertaintyNotesWatch period (follow,attendance, etc.)during whichevent occurredEvents which occuredduring watch period(follow, attendance, etc.) diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 886d900..8d552d1 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -730,6 +730,7 @@ sdb_generated_rst()dnl .. View name substitutions. .. |BIOGRAPHY| replace:: :ref:`BIOGRAPHY ` +.. |OBS| replace:: :ref:`OBS ` .. |PANTGRUNTS_VIEW| replace:: :ref:`PANTGRUNTS_VIEW ` diff --git a/doc/src/views.m4 b/doc/src/views.m4 index 4f90b15..6b98846 100644 --- a/doc/src/views.m4 +++ b/doc/src/views.m4 @@ -58,16 +58,19 @@ in the old MS Access database. Others reproduce common query patterns, eliminiating the need to connect (join) multiple tables. -+-------------------+----------------------+-----------------------+----------------------+ -| View | One row for each | Purpose | Tables/Views used | -+===================+======================+=======================+======================+ -| |PANTGRUNTS_VIEW| | |EVENTS| row related | Upload pantgrunt | |WATCHES|, |EVENTS|, | -| | to a |PANTGRUNTS| | data, reproduce "old" | |ROLES|, | -| | row | data | |PANTGRUNTS| | -+-------------------+----------------------+-----------------------+----------------------+ ++-------------------+----------------------+--------------------------+----------------------+ +| |OBS| | |EVENTS| row, so one | Simplifies querying -- | |WATCHES|, |EVENTS| | +| (Observations) | row for every | attaches a date and a | | +| | observed event | |BIOGRAPHY_DATA.Animid|, | | +| | | etc., to each event. | | ++-------------------+----------------------+--------------------------+----------------------+ +| |PANTGRUNTS_VIEW| | |EVENTS| row related | Upload pantgrunt | |WATCHES|, |EVENTS|, | +| | to a |PANTGRUNTS| | data, reproduce "old" | |ROLES|, | +| | row | data | |PANTGRUNTS| | ++-------------------+----------------------+--------------------------+----------------------+ .. toctree:: :maxdepth: 3 + views/obs.rst views/pantgrunts_view.rst - diff --git a/doc/src/views/obs.m4 b/doc/src/views/obs.m4 new file mode 100644 index 0000000..3534a3b --- /dev/null +++ b/doc/src/views/obs.m4 @@ -0,0 +1,273 @@ +.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as + published by the Free Software Foundation, either version 3 of the + License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + +.. _OBS: + +OBS (OBservationS) +------------------ + +.. |OBS_summary| replace:: + Each row represents an event that occurred. + Events may be recorded as part of a follow, at the feeding station, + or on some other, more ad-hoc, basis. + +|OBS_summary| + +OBS rows are |EVENTS| rows, but extended with the date, +|BIOGRAPHY_DATA.AnimID|, and other information found on |WATCHES|. + +The OBS view is convienent when writing queries, instead of having to +``JOIN`` |EVENTS| with |WATCHES| every time behavior is queryed. + +For example, below is a query that returns all the arrivals and +departures from the feeding station attendance records, showing +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 + , 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; + +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|. +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 +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 +grooming, whether or not the grooming is reciprocal, the |ROLES|.\ +|ROLES.Participant| column must be used\ [#f4]_, as in the following +query: + +.. code-block:: sql + :caption: Unique B-Record interval scan groomers + + SELECT obs.wid, obs.eid, obs.date + , obs.start AS time + , roles.participant AS animid + FROM obs + JOIN roles ON (roles.eid = obs.eid) + JOIN groom_scans_b ON (groom_scans_b.eid = obs.eid) + WHERE (roles.role = 'sdb_actor' + OR roles.role = 'sdb_mutual') + AND NOT groom_scans_b.duplicate + ORDER BY roles.participant, obs.date, obs.start; + +The following query returns every day every individual was observed +exhibiting a behavior\ [#f3]_: + +.. code-block:: sql + :caption: Individuals observed, per day, some sexual swelling excepted + + SELECT obs.date, roles.participant AS animid + FROM obs + JOIN roles ON (roles.eid = obs.eid) + GROUP BY roles.participant, obs.date + ORDER BY roles.participant, obs.date; + +The OBS.\ |WATCHES.Focal| 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 +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. +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 +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 +researchers were on some errand. + + +.. _OBS_Definition: + +Definition +`````````` +.. include:: /view_sql/sokwedb/obs.sql + :code: sql + + +.. _OBS_ER_Diagram: + +ER Diagram +`````````` + +.. figure:: sdb_er_image_path(images/obs) + :alt: OBS Entity-Relationship Diagram + :width: 100 % + + OBS + + +.. _OBS_Columns: + +Columns of the OBS View +``````````````````````` + +.. Configure the From column to be 35% of the line length, Description 40% + Otherwise, the column names can be hyphenated and contain line breaks. +.. tabularcolumns:: l \Y{.35} \Y{.40} + +.. table:: The columns of the OBS view + :widths: auto + :class: longtable + + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Column | From | Description | + +=================+=================================================+=====================================================================+ + | WID | |WATCHES|.\ |WATCHES.WID| | Identifier of the related |WATCHES| row | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Date | |WATCHES|.\ |WATCHES.Date| | Date of the event | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Focal | |WATCHES|.\ |WATCHES.Focal| | 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 | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | EID | |EVENTS|.\ |EVENTS.EID| | Identifier of the |EVENTS| row | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Behavior | |EVENTS|.\ |EVENTS.Behavior| | Code designating the type of event observed | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Start | |EVENTS|.\ |EVENTS.Start| | Time the event started (inclusive) | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Stop | |EVENTS|.\ |EVENTS.Stop| | Time the event finished (inclusive) | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | 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 | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Event_Notes | |EVENTS|.\ |EVENTS.Notes| | Textual notes on the event | + +-----------------+-------------------------------------------------+---------------------------------------------------------------------+ + + +.. _OBS_Operations_Allowed: + +Operations Allowed +`````````````````` + +INSERT + INSERTing a row into OBS inserts a row into |EVENTS|, and will also + INSERT a row into |WATCHES| if no row already exists that matches + the data supplied. + + 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 + supplied. + Their values are matched to existing database content. + If an existing |WATCHES| row matches, the new |EVENTS| row is + related to the |WATCHES| row so discovered. + + In either case, whether or not a WID is supplied, when a matching + |WATCHES| row exists the (non-|null|) data values supplied must + match the values that already exist. + + The WID and EID columns do not have their values inserted into new + rows. + If non-|null| values are supplied for these columns they must match + the values already existing in, or inserted into, the database. + +UPDATE + UPDATEing an OBS row updates the underlying tables. + +DELETE + Deleting an OBS row deletes the underlying |EVENTS| row. + If the underlying |WATCHES| row then has no related |EVENTS| rows, + it too is deleted. + + +.. rubric:: Footnotes + +.. [#f4] + In the case of groomings, because they are dyadic interctions, it + can be more convienient to use the |DYADS| view. + When using |DYADS| it is no longer necessary to test for the role + played by the individual performing the grooming because the view + places the individual who groomed in the Actor column. + + .. code-block:: sql + :caption: Unique B-Record interval scan groomers + + SELECT dyads.wid, dyads.eid, dyads.date + , dyads.start AS time + , dyads.actor AS animid + FROM dyads + JOIN groom_scans_b ON (groom_scans_b.eid = dyads.eid) + WHERE NOT groom_scans_b.duplicate + ORDER BY dyads.actor, dyads.date, dyads.start; + +.. [#f3] + Not observed under *any* conditions, because the |SWELLING_SOURCES| + table contains data on female sexual swelling obtained from other + data sources. + +.. [#f1] + Some data recorded in the old, MS Access, database purported to be + collected during a follow, but no follow existed. + Rather than discard this data, or have to research what actually + happened, much of this data was converted into SokweDB by creating + rows on |WATCHES| that record the date and focal -- as if there + were a follow -- even though there was no actual follow. + These sorts of rows have their own |WATCHES|.\ |WATCHES.Type| value + that distinguish them from "normal" follows. + +.. [#f2] + The individual arriving at/departing from the feeding station is + also recorded in a related |ROLES| row, in the |ROLES|.\ + |ROLES.Participant| column. + + 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 |ROLES|.\ |ROLES.Participant| column is always there no matter + the kind of event. + + That said, it is sometimes convenient to use OBS.\ |WATCHES.Focal|. + The point is, care must be taken when querying to ensure that + the query actually retrieves the information that it is expected + to. + Familarity with the database structure, the data itself, the + procedures used to collect and record the data, and how all of this + changed over time is critical for accurate results. -- 2.34.1