From 7a011357921a52f7722e3f705cda51476de4ad89 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Fri, 5 Jun 2026 16:17:00 +0000 Subject: [PATCH] New build_arrivals_seq() function, in case the auto-building goes wrong --- db/schemas/sokwedb/functions/Makefile | 2 +- .../functions/create/build_arrivals_seq.m4 | 132 ++++++++++++++++++ .../functions/drop/build_arrivals_seq.m4 | 24 ++++ doc/src/epilog.inc.m4 | 3 + doc/src/functions.m4 | 3 +- doc/src/functions/build_arrivals_seq.m4 | 60 ++++++++ 6 files changed, 222 insertions(+), 2 deletions(-) create mode 100644 db/schemas/sokwedb/functions/create/build_arrivals_seq.m4 create mode 100644 db/schemas/sokwedb/functions/drop/build_arrivals_seq.m4 create mode 100644 doc/src/functions/build_arrivals_seq.m4 diff --git a/db/schemas/sokwedb/functions/Makefile b/db/schemas/sokwedb/functions/Makefile index c2cb74e..c14f1b5 100644 --- a/db/schemas/sokwedb/functions/Makefile +++ b/db/schemas/sokwedb/functions/Makefile @@ -18,7 +18,7 @@ # Karl O. Pinc # This determines the order in which the functions are put into the database. -ORDER := julian julian_to +ORDER := build_arrivals_seq julian julian_to ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/functions/create/build_arrivals_seq.m4 b/db/schemas/sokwedb/functions/create/build_arrivals_seq.m4 new file mode 100644 index 0000000..9e6066e --- /dev/null +++ b/db/schemas/sokwedb/functions/create/build_arrivals_seq.m4 @@ -0,0 +1,132 @@ +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 Rebuild ARRIVALS.Seq functions for the server side. +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4') +include(`constants.m4') +include(`macros.m4') +include(`functions.m4') +dnl + +CREATE OR REPLACE FUNCTION build_arrivals_seq() + RETURNS INT + LANGUAGE plpgsql + VOLATILE + SECURITY INVOKER + PARALLEL UNSAFE + sdb_function_set_search_path + AS $$ + + -- Re-compute the ARRIVALS.Seq values of all ARRIVALS rows + -- + -- AGPL_notice(` --', `2026', `Karl O. Pinc ') + -- + -- Syntax: build_arrivals_seq() + -- + -- Input: + -- date The date to convert. + -- + -- Returns: + -- The number of rows processed. + -- + -- Remarks: + -- Exists so that the db content can be fixed if there's a bug + -- in the automatic ARRIVALS.Seq computations. + + DECLARE + a_fid follows.fid%TYPE; + cnt INT := 0; + a_result INT := 0; + + BEGIN + FOR a_fid IN + SELECT follows.fid + FROM follows + LOOP + SELECT build_arrivals_seq(a_fid) + INTO a_result; + cnt := cnt + a_result; + END LOOP; + + RETURN cnt; + END; +$$; + + +CREATE OR REPLACE FUNCTION build_arrivals_seq(a_fid INT) + RETURNS INT + LANGUAGE plpgsql + VOLATILE + SECURITY INVOKER + PARALLEL UNSAFE + sdb_function_set_search_path + AS $$ + + -- Re-compute the ARRIVALS.Seq values of the ARRIVALS rows related to a + -- follow + -- + -- AGPL_notice(` --', `2026', `Karl O. Pinc ') + -- + -- Syntax: build_arrivals_seq(fid) + -- + -- Input: + -- fid The FOLLOWS.FID of the follow for which ARRIVALS.Seq should + -- be re-built. + -- + -- Returns: + -- The number of rows processed. + + DECLARE + a_result INT := 0; + last_participant roles.participant%TYPE; + a_participant roles.participant%TYPE; + a_eid events.eid%TYPE; + a_seq arrivals.seq%TYPE; + + BEGIN + + FOR a_participant, a_eid IN + SELECT events.eid + FROM events + JOIN roles ON (roles.eid = events.eid) + WHERE events.fid = a_fid + AND events.behavior = 'sdb_arrival' + ORDER BY roles.participant + , events.start, events.stop, events.eid -- Tiebreakers + LOOP + IF a_participant IS DISTINCT FROM last_participant THEN + last_participant := a_participant; + a_seq := 1; + END IF; + + UPDATE arrivals + SET Seq = a_seq + WHERE arrivals.eid = a_eid; + + a_seq := a_seq + 1; + a_result := a_result + 1; + END LOOP; + + RETURN a_result; + END; +$$; + + +grant_everybody_func_priv(`build_arrivals_seq()') +grant_everybody_func_priv(`build_arrivals_seq(INT)') diff --git a/db/schemas/sokwedb/functions/drop/build_arrivals_seq.m4 b/db/schemas/sokwedb/functions/drop/build_arrivals_seq.m4 new file mode 100644 index 0000000..9d0db08 --- /dev/null +++ b/db/schemas/sokwedb/functions/drop/build_arrivals_seq.m4 @@ -0,0 +1,24 @@ +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 Drop ARRIVALS.Seq rebuilding functions for the server side. +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4') + +DROP FUNCTION IF EXISTS build_arrivals_seq(); +DROP FUNCTION IF EXISTS build_arrivals_seq(INT); diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index 552fdf6..34cc9f1 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -548,6 +548,9 @@ sdb_generated_rst()dnl Note that we cleverly use the "function_" prefix so as to avoid possible collisions with other names. +.. |function_build_arrivals_seq| replace:: + :ref:`build_arrivals_seq() ` + .. |function_julian| replace:: :ref:`julian() ` .. |function_julian_to| replace:: :ref:`julian_to() ` diff --git a/doc/src/functions.m4 b/doc/src/functions.m4 index 279e8e5..30c8a42 100644 --- a/doc/src/functions.m4 +++ b/doc/src/functions.m4 @@ -1,4 +1,4 @@ -.. Copyright (C) 2023 The Meme Factory, Inc. www.karlpinc.com +.. Copyright (C) 2023, 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 @@ -34,5 +34,6 @@ users. .. toctree:: :maxdepth: 1 + functions/build_arrivals_seq.rst functions/julian.rst functions/julian_to.rst diff --git a/doc/src/functions/build_arrivals_seq.m4 b/doc/src/functions/build_arrivals_seq.m4 new file mode 100644 index 0000000..13a82cc --- /dev/null +++ b/doc/src/functions/build_arrivals_seq.m4 @@ -0,0 +1,60 @@ +.. 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 + + +.. _function_build_arrivals_seq: + +build_arrivals_seq() -- Rebuild the |ARRIVALS|.\ |ARRIVALS.Seq| column +---------------------------------------------------------------------- + +Synopsis +```````` + +:: + + build_arrivals_seq() INT + build_arrivals_seq(fid INT) INT + +Input +````` + +fid +''' + +A |FOLLOWS|.\ |FOLLOWS.FID| value, designating the follow which is to +have its |ARRIVALS|.\ |ARRIVALS.Seq| values recomputed. + +Description +``````````` + +.. |build_arrivals_seq_summary| replace:: + Rebuild the |ARRIVALS|.\ |ARRIVALS.Seq| values, either all of them + or those of only one follow. + +|build_arrivals_seq_summary| +When this function is called without an argument, it re-computes all +the |ARRIVALS|.\ |ARRIVALS.Seq| values. +When called with a |FOLLOWS|.\ |FOLLOWS.FID| value, it re-computes the +the |ARRIVALS|.\ |ARRIVALS.Seq| values of the given follow. + +The function returns the number of rows processed, regardless of +whether or not a row's |ARRIVALS.Seq| value was changed by the +function call. -- 2.34.1