From b2899f870c08c5f977ed888fd69eb05e3364ac89 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Mon, 8 Sep 2025 13:12:34 +0000 Subject: [PATCH] Add EVENTS table --- db/schemas/sokwedb/tables/Makefile | 4 +- db/schemas/sokwedb/tables/create/events.m4 | 44 +++++++ doc/src/epilog.inc.m4 | 19 +++ doc/src/tables.m4 | 1 + doc/src/tables/events.m4 | 142 +++++++++++++++++++++ include/limits.m4 | 8 ++ 6 files changed, 216 insertions(+), 2 deletions(-) create mode 100644 db/schemas/sokwedb/tables/create/events.m4 create mode 100644 doc/src/tables/events.m4 diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 3f3a3ef..71c880a 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -27,8 +27,8 @@ ORDER := biography_data \ sightings \ follows \ follow_observers \ - follow_studies - + follow_studies \ + events ## ## 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/events.m4 b/db/schemas/sokwedb/tables/create/events.m4 new file mode 100644 index 0000000..1a48490 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/events.m4 @@ -0,0 +1,44 @@ +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 events ( + key_column(`EVENTS', `EID', INTEGER) + ,fid INTEGER NOT NULL + REFERENCES follows + ,behavior TEXT NOT NULL + ,start TIME NOT NULL + noseconds_check(`Start') + ,stop TIME NOT NULL + noseconds_check(`Stop') + ,certainty TEXT NOT NULL + REFERENCES certainties + ,notes TEXT NOT NULL + notonlyspaces_check(`Notes') + + CONSTRAINT "Start cannot be after Stop" + CHECK (start <= stop) +); + +grant_priv(`EVENTS') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 0ffa6a1..7fc21f0 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -167,6 +167,25 @@ sdb_generated_rst()dnl .. |ENTRYTYPES.EntryType| replace:: :ref:`EntryType ` +.. |EVENTS| replace:: :ref:`EVENTS ` +.. |EVENTS.EID| replace:: + :ref:`EID ` +.. |EVENTS.FID| replace:: + :ref:`FID ` +.. |EVENTS.BEHAVIOR| replace:: + :ref:`Behavior ` +.. |EVENTS.Start| replace:: + :ref:`Start ` +.. |EVENTS.Stop| replace:: + :ref:`Stop ` +.. |EVENTS.Certainty| replace:: + :ref:`Certainty ` +.. Temporarily (?) removed + .. |EVENTS.Seq| replace:: + :ref:`Seq ` +.. |EVENTS.Notes| replace:: + :ref:`Notes ` + .. |CYCLE_STATES| replace:: :ref:`CYCLE_STATES ` .. |CYCLE_STATES.Code| replace:: :ref:`Code ` diff --git a/doc/src/tables.m4 b/doc/src/tables.m4 index da737da..9dbe81f 100644 --- a/doc/src/tables.m4 +++ b/doc/src/tables.m4 @@ -32,6 +32,7 @@ Data Tables tables/biography_log.rst tables/comm_membs.rst tables/comm_memb_log.rst + tables/events.rst tables/follows.rst tables/follow_observers.rst tables/follow_studies.rst diff --git a/doc/src/tables/events.m4 b/doc/src/tables/events.m4 new file mode 100644 index 0000000..485f3c3 --- /dev/null +++ b/doc/src/tables/events.m4 @@ -0,0 +1,142 @@ +.. 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 + +.. _EVENTS: + +EVENTS +------ + +.. |EVENTS_summary| replace:: + Each row represents an *event* that occurred during a follow, whether + involving the focal or not. + All recorded observations that occur during a follow are events. + +|EVENTS_summary| + +The |EVENTS.Start| time cannot be after the |EVENTS.Stop| time. + +.. _EVENTS.EID: + +EID (Event ID) +`````````````` + +.. |EVENTS.EID_summary| replace:: + |idcol| + +|EVENTS.EID_summary| |notnull| + + +.. _EVENTS.FID: + +FID (Follow IDentifier) +``````````````````````` + +.. |EVENTS.FID_summary| replace:: + The |FOLLOWS|.\ |FOLLOWS.FID| that identifies the follow during + which the event was recorded. + +|EVENTS.FID_summary| |notnull| + + +.. _EVENTS.Behavior: + +Behavior +```````` + +.. |EVENTS.Behavior_summary| replace:: + A code indicating the kind of event recorded. + These codes usually represent chimpanzee behaviors, but may otherwise + classify the event. + +|EVENTS.Behavior_summary| + +Each code has special meaning to SokweDB, controlling what additional +information may, or must, be related to the event. +In other words, what other tables may, or must, contain additional +information about the event. + +|notnull| + + +.. _EVENTS.Start: + +Start +````` + +.. |EVENTS.Start_summary| replace:: + The time the event started. + +|EVENTS.Start_summary| +The value of this column cannot be before ``sdb_min_event_start``. +|noseconds| |notnull| + + +.. _EVENTS.Stop: + +Stop +```` + +.. |EVENTS.Stop_summary| replace:: + The time the event stopped. + +|EVENTS.Stop_summary| +The value of this column cannot be after ``sdb_max_event_stop``. +|noseconds| |notnull| + + +.. _EVENTS.Certainty: + +Certainty +````````` + +.. |EVENTS.Certainty_summary| replace:: + A |CERTAINTIES|.\ |CERTAINTIES.Certainty| code designating the + certainty of the event observervation. + +|EVENTS.Certainty_summary| |notnull| + + +.. Don't have a seq column in events, unless later maybe + there's a sequence that multiple events need + .. _EVENTS.Seq: + + Seq (Sequence Number) + ````````````````````` + + .. |EVENTS.Seq_summary| replace:: + An automatically generated integer, starting with 1 and increasing + by one without gaps, which indicates the timewise ordering of... Something. + + |EVENTS.Seq_summary| So, the Seq increments each time an + individual (re)joins the follow. + |cannot_change| |notnull| + + +.. _EVENTS.Notes: + +Notes +````` + +.. |EVENTS.Notes_summary| replace:: + + Free form textual notes regarding the event. + +|EVENTS.Notes_summary| |notonlyspaces| |notnull| diff --git a/include/limits.m4 b/include/limits.m4 index 6e1b2e0..50be30c 100644 --- a/include/limits.m4 +++ b/include/limits.m4 @@ -59,6 +59,14 @@ dnl dnl The earliest possible FOLLOWS.Date define(`sdb_min_follow_date', `1960-07-14') +dnl The earliest possible time an event can occur in a follow. +dnl (In 24 hour time) +define(`sdb_min_event_start', `4:00') + +dnl The latest possible time an event can occur in a follow. +dnl (In 24 hour time) +define(`sdb_max_event_stop', `20:00') + dnl The earliest possible BIOGRAPHY_LOG.DateOfUpdate define(`sdb_first_biography_log_date', `2013-07-01') -- 2.34.1