From 8ea4f6bdbb786280bfcc4d1e0bfa7118a8167c26 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc" Date: Tue, 30 Jan 2024 18:06:41 -0600 Subject: [PATCH] Create BIOGRAPHY view --- db/schemas/lib/triggers/Makefile | 5 +- db/schemas/lib/triggers/create/biography.m4 | 39 +++++++ db/schemas/sokwedb/views/Makefile | 2 +- db/schemas/sokwedb/views/create/biography.m4 | 71 ++++++++++++ doc/src/epilog.inc.m4 | 5 + doc/src/index.m4 | 1 + doc/src/tables/biography_data.m4 | 3 + doc/src/views.m4 | 45 ++++++++ doc/src/views/biography.m4 | 110 +++++++++++++++++++ 9 files changed, 279 insertions(+), 2 deletions(-) create mode 100644 db/schemas/lib/triggers/create/biography.m4 create mode 100644 db/schemas/sokwedb/views/create/biography.m4 create mode 100644 doc/src/views.m4 create mode 100644 doc/src/views/biography.m4 diff --git a/db/schemas/lib/triggers/Makefile b/db/schemas/lib/triggers/Makefile index 5ed227f..1476733 100644 --- a/db/schemas/lib/triggers/Makefile +++ b/db/schemas/lib/triggers/Makefile @@ -23,7 +23,10 @@ ORDER := comm_ids \ departtypes \ biography_data \ comm_membs \ - _error_immutable_view + _error_immutable_view \ + biography + +DROP_EXISTING := true ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/lib/triggers/create/biography.m4 b/db/schemas/lib/triggers/create/biography.m4 new file mode 100644 index 0000000..d07e12a --- /dev/null +++ b/db/schemas/lib/triggers/create/biography.m4 @@ -0,0 +1,39 @@ +dnl Copyright (C) 2023, 2024 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 biography_data table +dnl +dnl Karl O. Pinc + +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`macros.m4')dnl + + +CREATE TRIGGER biography_insert_trigger + INSTEAD OF INSERT + ON biography FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); + +CREATE TRIGGER biography_update_trigger + INSTEAD OF UPDATE + ON biography FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); + +CREATE TRIGGER biography_delete_trigger + INSTEAD OF DELETE + ON biography FOR EACH ROW + EXECUTE PROCEDURE _error_immutable_view(); diff --git a/db/schemas/sokwedb/views/Makefile b/db/schemas/sokwedb/views/Makefile index 7008185..9d88809 100644 --- a/db/schemas/sokwedb/views/Makefile +++ b/db/schemas/sokwedb/views/Makefile @@ -20,7 +20,7 @@ # This determines the order in which the views are put into the # database. This is important because views must be put into the # database after the views they reference. -ORDER := +ORDER := biography ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/views/create/biography.m4 b/db/schemas/sokwedb/views/create/biography.m4 new file mode 100644 index 0000000..a4a77fe --- /dev/null +++ b/db/schemas/sokwedb/views/create/biography.m4 @@ -0,0 +1,71 @@ +dnl Copyright (C) 2024 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 biography ( + animid + ,animidnum + ,animname + ,birthcomm + ,bccertainty + ,sex + ,momid + ,dadid + ,dadidpub + ,firstborn + ,birthdate + ,bdmin + ,bdmax + ,bddist + ,entrydate + ,entrytype + ,departdate + ,departtype) + AS + SELECT + biography_data.animid + ,biography_data.animidnum + ,biography_data.animname + ,biography_data.birthcomm + ,biography_data.bccertainty + ,biography_data.sex + ,biography_data.momid + ,CASE + WHEN biography_data.dadprelim + THEN biography_data.dadid || '_prelim' + ELSE biography_data.dadid + END CASE + ,biography_data.dadidpub + ,biography_data.firstborn + ,biography_data.birthdate + ,biography_data.bdmin + ,biography_data.bdmax + ,biography_data.bddist + ,biography_data.entrydate + ,biography_data.entrytype + ,biography_data.departdate + ,biography_data.departtype + FROM biography_data; + +grant_priv(`BIOGRAPHY') diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index c977866..0faaa4a 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -148,6 +148,11 @@ sdb_generated_rst()dnl .. |PEOPLE.Active| replace:: :ref:`Active ` +.. View name substitutions. + +.. |BIOGRAPHY| replace:: :ref:`BIOGRAPHY ` + + .. Function substitutions, which exist for the same reason as table and column name substitutions. Note that we cleverly use the "function_" prefix so as to avoid diff --git a/doc/src/index.m4 b/doc/src/index.m4 index 5478791..af8e191 100644 --- a/doc/src/index.m4 +++ b/doc/src/index.m4 @@ -34,6 +34,7 @@ Technical Documentation er_diagrams.rst code_tables.rst tables.rst + views.rst functions.rst appendices.rst diff --git a/doc/src/tables/biography_data.m4 b/doc/src/tables/biography_data.m4 index 0e4d968..f922439 100644 --- a/doc/src/tables/biography_data.m4 +++ b/doc/src/tables/biography_data.m4 @@ -33,6 +33,9 @@ BIOGRAPHY_DATA |BIOGRAPHY_DATA_summary| BIOGRAPHY_DATA contains the basic demographic data of individual chimpanzees. +.. note:: + The |BIOGRAPHY| view may be preferred to using the |BIOGRAPHY_DATA| table. + A mother must be female; the |BIOGRAPHY_DATA.Sex| must be ``sdb_female`` (female) of the |BIOGRAPHY_DATA| row identified by an offspring's |BIOGRAPHY_DATA.MomID|. diff --git a/doc/src/views.m4 b/doc/src/views.m4 new file mode 100644 index 0000000..77eaf3d --- /dev/null +++ b/doc/src/views.m4 @@ -0,0 +1,45 @@ +.. Copyright (C) 2024 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 + + +.. _data_retrieval_views: + +Data Retrieval Views +==================== + +The views appearing in this section exist for convenience in querying. +Some exist to make the data look more like the "old" data, as it appeared +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 | ++=============+======================+=================+===================+ +| |BIOGRAPHY| | |BIOGRAPHY_DATA| row | Reproduce "old" | |BIOGRAPHY_DATA| | +| | | data | | ++-------------+----------------------+-----------------+-------------------+ + +.. toctree:: + :maxdepth: 3 + + views/biography.rst + diff --git a/doc/src/views/biography.m4 b/doc/src/views/biography.m4 new file mode 100644 index 0000000..2c50217 --- /dev/null +++ b/doc/src/views/biography.m4 @@ -0,0 +1,110 @@ +.. Copyright (C) 2024 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 + +.. _BIOGRAPHY: + +BIOGRAPHY +--------- + +.. |BIOGRAPHY_summary| replace:: + Each row represents a chimpanzee and is a transformation of the + corresponding |BIOGRAPHY_DATA| row, making the data more like the + traditional format and therefore, in one sense, easier to work with. + +|BIOGRAPHY_summary| +This view contains one row for each chimpanzee on +which data has ever been recorded (in SokweDB), and an additional +row for ``sdb_unk`` a generic value used when a chimpanzee is +unrecognized. +BIOGRAPHY contains the basic demographic data of +individual chimpanzees. + + +.. _BIOGRAPHY_Definition: + +Definition +`````````` +.. include:: /view_sql/sokwedb/biography.sql + :code: sql + + +.. _BIOGRAPHY_Columns: + +Columns in the BIOGRAPHY 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 in BIOGRAPHY + :widths: auto + :class: longtable + + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Column | From | Description | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | AnimID | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimID| | Animal IDentifier | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | AnimIDNum | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimIDNum| | Animal IDentifier | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | AnimName | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.AnimName| | Animal Name | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | BirthComm | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BirthComm| | Birth Community | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | BCCertainty | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BCCertainty| | Certainty of |BIOGRAPHY_DATA.BirthComm| | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | Sex | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.Sex| | Individual's Sex | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | MomID | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.MomID| | |BIOGRAPHY_DATA.AnimID| of the individual's mother | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | DadID || |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DadID| | |BIOGRAPHY_DATA.AnimID| of the individual's father, | + | || |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DadPrelim| | suffixed with ``_prelim`` if |BIOGRAPHY_DATA.DadPrelim| is |TRUE| | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | DadIDPub | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DadIDPub| | Publication of Paternity citation | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | FirstBorn | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.FirstBorn| | First born status code | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | BirthDate | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BirthDate| | Birth Date | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | BDMin | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BDMin| | Minimum Birth Date | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | BDMax | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BDMax| | Maximum Birth Date | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | BDDist | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BDDist| | Birth Date Distribution | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | EntryDate | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryDate| | Date of study Entry | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | EntryType | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryType| | Entry status code | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | DepartDate | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate| | Date last seen | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + | DepartType | |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartType| | Depart date status code | + +-------------+-------------------------------------------------+---------------------------------------------------------------------+ + + +.. _BIOGRAPHY_Operations_Allowed: + +Operations Allowed +`````````````````` + +None. + -- 2.34.1