From 474e831b74f3d2f2a477d7c7203cb886528ce109 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sun, 5 Oct 2025 15:46:26 +0000 Subject: [PATCH] Create and document the ARRIVALS table --- db/schemas/sokwedb/indexes/Makefile | 2 +- db/schemas/sokwedb/indexes/create/arrivals.m4 | 31 +++++ db/schemas/sokwedb/indexes/drop/arrivals.m4 | 26 ++++ db/schemas/sokwedb/tables/Makefile | 3 +- db/schemas/sokwedb/tables/create/arrivals.m4 | 38 ++++++ doc/src/epilog.inc.m4 | 15 +++ doc/src/tables.m4 | 1 + doc/src/tables/arrivals.m4 | 119 ++++++++++++++++++ doc/src/tables/events.m4 | 7 ++ doc/src/tables/follows.m4 | 12 +- 10 files changed, 246 insertions(+), 8 deletions(-) create mode 100644 db/schemas/sokwedb/indexes/create/arrivals.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/arrivals.m4 create mode 100644 db/schemas/sokwedb/tables/create/arrivals.m4 create mode 100644 doc/src/tables/arrivals.m4 diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index d4a320f..7b55745 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -20,7 +20,7 @@ # This determines the order in which the indexes are put into the database. # This is not really important but is needed for other types of db objects. ORDER := biography_data biography_log comm_membs comm_memb_log \ - follows follow_observers follow_studies events roles + follows follow_observers follow_studies events roles arrivals ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/arrivals.m4 b/db/schemas/sokwedb/indexes/create/arrivals.m4 new file mode 100644 index 0000000..ce1a567 --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/arrivals.m4 @@ -0,0 +1,31 @@ +dnl Copyright (C) 2025 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +CREATE INDEX IF NOT EXISTS arrivals_eid ON arrivals + (eid); +CREATE INDEX IF NOT EXISTS arrivals_cycle ON arrivals + (cycle); +CREATE INDEX IF NOT EXISTS arrivals_datasource ON arrivals + (datasource); + + diff --git a/db/schemas/sokwedb/indexes/drop/arrivals.m4 b/db/schemas/sokwedb/indexes/drop/arrivals.m4 new file mode 100644 index 0000000..b053c24 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/arrivals.m4 @@ -0,0 +1,26 @@ +dnl Copyright (C) 2025 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +DROP INDEX IF EXISTS arrivals_eid; +DROP INDEX IF EXISTS arrivals_cycle; +DROP INDEX IF EXISTS arrivals_datasource; diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 35867d6..f7d5075 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -31,7 +31,8 @@ ORDER := biography_data \ events \ roles \ humans \ - species_present + species_present \ + arrivals ## ## CAUTION: This Makefile is not designed to be run directly. It is normally ## invoked by another Makefile. diff --git a/db/schemas/sokwedb/tables/create/arrivals.m4 b/db/schemas/sokwedb/tables/create/arrivals.m4 new file mode 100644 index 0000000..4b21c68 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/arrivals.m4 @@ -0,0 +1,38 @@ +dnl Copyright (C) 2025 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +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 TABLE arrivals ( + key_column(`ARRIVALS', `ArID', INTEGER) + ,eid INTEGER NOT NULL + REFERENCES events + ,neststart BOOLEAN NOT NULL + ,nestend BOOLEAN NOT NULL + ,cycle TEXT NOT NULL + REFERENCES cycle_states + ,datasource TEXT NOT NULL + REFERENCES arrival_sources +); + +grant_priv(`ARRIVALS') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index f1cf69d..307fe85 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -51,6 +51,21 @@ sdb_generated_rst()dnl the section heading and still get a link label with the name of the table or column. +.. |ARRIVALS| + replace:: :ref:`ARRIVALS ` +.. |ARRIVALS.ArID| + replace:: :ref:`ArID ` +.. |ARRIVALS.EID| + replace:: :ref:`EID ` +.. |ARRIVALS.NestStart| + replace:: :ref:`NestStart ` +.. |ARRIVALS.NestEnd| + replace:: :ref:`NestEnd ` +.. |ARRIVALS.Cycle| + replace:: :ref:`Cycle ` +.. |ARRIVALS.DataSource| + replace:: :ref:`DataSource ` + .. |ARRIVAL_SOURCES| replace:: :ref:`ARRIVAL_SOURCES ` .. |ARRIVAL_SOURCES.Code| diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index 1f7b378..9ad8c65 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -28,6 +28,7 @@ Data Tables .. toctree:: :maxdepth: 3 + tables/arrivals.rst tables/biography_data.rst tables/biography_log.rst tables/comm_membs.rst diff --git a/doc/src/tables/arrivals.m4 b/doc/src/tables/arrivals.m4 new file mode 100644 index 0000000..2b2b5ed --- /dev/null +++ b/doc/src/tables/arrivals.m4 @@ -0,0 +1,119 @@ +.. Copyright (C) 2025 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 + +.. _ARRIVALS: + +ARRIVALS +-------- + +.. |ARRIVALS_summary| replace:: + Each row, taken together with the related |EVENTS| row, represents + an arrival or departure of an individual from the observation + conducted during a follow. + +|ARRIVALS_summary| +The related |EVENTS| row must be an arrival event; it must have an +|EVENTS|.\ |EVENTS.Behavior| value of ``sdb_arrival``. +This related |EVENTS| row supplies arrival and departure time values, +and the |ROLES| role related to the event supplies information on the +individual arriving and departing. +For further information see the documentation of the :ref:`EVENTS +` table. + +.. _ARRIVALS.ArID: + +ArID (Arrival ID) +````````````````` + +.. |ARRIVALS.ArID_summary| replace:: |idcol| + +|ARRIVALS.ArID_summary| |notnull| + + +.. _ARRIVALS.EID: + +EID (Event ID) +`````````````` + +.. |ARRIVALS.EID_summary| replace:: + The |EVENTS|.\ |EVENTS.EID| identifying the arrival event that is related + to the arrival or departure. + +|ARRIVALS.EID_summary| +The related event contains information on arrival and departure times. + +|notnull| + + +.. _ARRIVALS.NestStart: + +NestStart (in Nest at the Start time) +````````````````````````````````````` + +.. |ARRIVALS.NestStart_summary| replace:: + A |boolean|, whether or not the related participent, related + through |EVENTS| and |ROLES|, was in a nest at the time of arrival. + +|ARRIVALS.NestStart_summary| |notnull| + + +.. _ARRIVALS.NestEnd: + +NestStart (in Nest at the Stop time) +```````````````````````````````````` + +.. |ARRIVALS.NestEnd_summary| replace:: + A |boolean|, whether or not the related participent, related + through |EVENTS| and |ROLES|, was in a nest at the time of departure. + +|ARRIVALS.NestEnd_summary| |notnull| + + +.. _ARRIVALS.Cycle: + +Cycle (Cycle state) +``````````````````` + +.. |ARRIVALS.Cycle_summary| replace:: + A |CYCLE_STATES|.\ |CYCLE_STATES.Code| value indicating the state + of the arriving individual's estrous cycle. + +|ARRIVALS.Cycle_summary| + +All indivdiuals, female or not, have a value in this column, to avoid +|tri-valued logic| and therefore simplify querying. +The value used for males and other non-cycling individuals is +un-interesting. + +|notnull| + + +.. _ARRIVALS.DataSource: + +DataSource (Data Source) +```````````````````````` + +.. |ARRIVALS.DataSource_summary| replace:: + An |ARRIVAL_SOURCES|.\ |ARRIVAL_SOURCES.Code| value designating the + document or procedure that was used to determine the arrival and + departure information. + +|ARRIVALS.ArID_summary| |notnull| diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 index e0b5da6..7f04d26 100644 --- a/doc/src/tables/events.m4 +++ b/doc/src/tables/events.m4 @@ -47,6 +47,13 @@ The following table lists these rules and implications: .. _EVENTS_arrival_code: ``sdb_arrival`` (Arrival) + A related row should exist on ARRIVALS; there should be a row on + |ARRIVALS| with an |ARRIVALS.EID| value of the event's + |EVENTS.EID|. + There may be at most one of these related |ARRIVALS| rows. + The system will generate a warning when there is no |ARRIVALS| row + related to the arrival event. + The |ROLES| row related to the event, the row with a |ROLES.EID| value equal to the |EVENTS.EID| value, designates the arriving/departing individual. diff --git a/doc/src/tables/follows.m4 b/doc/src/tables/follows.m4 index 431830c..ddc5d8f 100644 --- a/doc/src/tables/follows.m4 +++ b/doc/src/tables/follows.m4 @@ -33,12 +33,12 @@ observations, related |EVENTS| rows. The combination of |FOLLOWS.Focal| and |FOLLOWS.Date| must be unique. -.. Comment out until ARRIVALS exists - The system will generate a warning when a FOLLOWS row does not have a - |ARRIVALS| related row that records the presence of the focal, - does not have a related |EVENTS| row with a related |ARRIVALS| row where the - |ARRIVALS|.\ |ARRIVALS.AnimID| has the same value as the - follow's |FOLLOWS.Focal| column. +The system will generate a warning when a FOLLOWS row does not have a +|ARRIVALS| related row that records the presence of the focal: does +not have a related |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 +|FOLLOWS.Focal| column. A scheduled follow must have at least one related |FOLLOW_OBSERVERS| rows, to record the observers scheduled to conduct the follow. -- 2.34.1