From aac2718d2ce524e95565171ec6f1e5cf617909fc Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sun, 1 Mar 2026 00:35:50 +0000 Subject: [PATCH] Create ESTRUS_SOURCES table and indexes --- db/schemas/sokwedb/indexes/Makefile | 3 +- .../sokwedb/indexes/create/estrus_sources.m4 | 29 ++++++++++ .../sokwedb/indexes/drop/estrus_sources.m4 | 27 ++++++++++ db/schemas/sokwedb/tables/Makefile | 1 + .../sokwedb/tables/create/estrus_sources.m4 | 53 +++++++++++++++++++ doc/src/tables/estrus_sources.m4 | 5 +- 6 files changed, 115 insertions(+), 3 deletions(-) create mode 100644 db/schemas/sokwedb/indexes/create/estrus_sources.m4 create mode 100644 db/schemas/sokwedb/indexes/drop/estrus_sources.m4 create mode 100644 db/schemas/sokwedb/tables/create/estrus_sources.m4 diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index 7b55745..29e56b1 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -20,7 +20,8 @@ # 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 arrivals + follows follow_observers follow_studies events roles arrivals \ + estrus_sources ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/indexes/create/estrus_sources.m4 b/db/schemas/sokwedb/indexes/create/estrus_sources.m4 new file mode 100644 index 0000000..1ac6f46 --- /dev/null +++ b/db/schemas/sokwedb/indexes/create/estrus_sources.m4 @@ -0,0 +1,29 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +CREATE INDEX IF NOT EXISTS estrus_sources_date ON estrus_sources + (date); +CREATE INDEX IF NOT EXISTS estrus_sources_animid ON estrus_sources + (animid); + + diff --git a/db/schemas/sokwedb/indexes/drop/estrus_sources.m4 b/db/schemas/sokwedb/indexes/drop/estrus_sources.m4 new file mode 100644 index 0000000..8e42ef6 --- /dev/null +++ b/db/schemas/sokwedb/indexes/drop/estrus_sources.m4 @@ -0,0 +1,27 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`indexmacros.m4')dnl + +DROP INDEX IF EXISTS estrus_sources_date; +DROP INDEX IF EXISTS estrus_sources_animid; + + diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index f7d5075..52fe9bb 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -22,6 +22,7 @@ # into the database after the tables they reference. ORDER := biography_data \ biography_log \ + estrus_sources \ comm_membs \ comm_memb_log \ sightings \ diff --git a/db/schemas/sokwedb/tables/create/estrus_sources.m4 b/db/schemas/sokwedb/tables/create/estrus_sources.m4 new file mode 100644 index 0000000..d014299 --- /dev/null +++ b/db/schemas/sokwedb/tables/create/estrus_sources.m4 @@ -0,0 +1,53 @@ +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 TABLE estrus_sources ( + key_column(`ESTRUS_SOURCES', `ID', INTEGER) + ,source TEXT NOT NULL + REFERENCES sighting_records + CONSTRAINT "Source may not be sdb_sokwedb_source" + CHECK(source <> 'sdb_sokwedb_source') + ,date DATE NOT NULL + ,animid_type_column(`animid', `AnimID', `NOT NULL') + ,estrusmin TEXT + REFERENCES cycle_states + CONSTRAINT + "EstrusMin may be NULL only when Source = sdb_manual_estrus" + CHECK(estrusmin IS NOT NULL + OR source = 'sdb_manual_estrus') + ,estrusmax TEXT + REFERENCES cycle_states + CONSTRAINT + "EstrusMax may be NULL only when Source = sdb_manual_estrus" + CHECK(estrusmax IS NOT NULL + OR source = 'sdb_manual_estrus') + ,notes TEXT NOT NULL + notonlyspaces_check(`Notes') + -- Table constraints + CONSTRAINT "Both EstrusMin and EstrusMax may not be NULL" + CHECK(NOT(estrusmin IS NULL AND estrusmax IS NULL)) +); + +grant_priv(`ESTRUS_SOURCES') diff --git a/doc/src/tables/estrus_sources.m4 b/doc/src/tables/estrus_sources.m4 index f94b096..88254fc 100644 --- a/doc/src/tables/estrus_sources.m4 +++ b/doc/src/tables/estrus_sources.m4 @@ -25,8 +25,9 @@ ESTRUS_SOURCES ------------------------- .. |ESTRUS_SOURCES_summary| replace:: - There is a row for each female chimpanzee, for each date - estrus information was recorded. + There is a row for each female chimpanzee, for each date estrus + information was recorded, for each kind of record -- that is not a + TRec -- where the estrus information was recorded. Rows exist both for non-BRec related sightings, and for BRec related sighting when those sightings were recorded in the old, MS Access based, database. -- 2.34.1