refactor procedures.sql

1 files changed, 144 insertions, 0 deletions

diff --git a/src/exchangedb/exchange_do_gc.sql b/src/exchangedb/exchange_do_gc.sql
new file mode 100644
index 000000000..c6331c18e
--- /dev/null
+++ b/src/exchangedb/exchange_do_gc.sql
@@ -0,0 +1,144 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER 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 General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
+--
+
+CREATE OR REPLACE PROCEDURE exchange_do_gc(
+  IN in_ancient_date INT8,
+  IN in_now INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  reserve_uuid_min INT8; -- minimum reserve UUID still alive
+DECLARE
+  melt_min INT8; -- minimum melt still alive
+DECLARE
+  coin_min INT8; -- minimum known_coin still alive
+DECLARE
+  deposit_min INT8; -- minimum deposit still alive
+DECLARE
+  reserve_out_min INT8; -- minimum reserve_out still alive
+DECLARE
+  denom_min INT8; -- minimum denomination still alive
+BEGIN
+
+DELETE FROM exchange.prewire
+  WHERE finished=TRUE;
+
+DELETE FROM exchange.wire_fee
+  WHERE end_date < in_ancient_date;
+
+-- TODO: use closing fee as threshold?
+DELETE FROM exchange.reserves
+  WHERE gc_date < in_now
+    AND current_balance_val = 0
+    AND current_balance_frac = 0;
+
+SELECT
+     reserve_out_serial_id
+  INTO
+     reserve_out_min
+  FROM exchange.reserves_out
+  ORDER BY reserve_out_serial_id ASC
+  LIMIT 1;
+
+DELETE FROM exchange.recoup
+  WHERE reserve_out_serial_id < reserve_out_min;
+-- FIXME: recoup_refresh lacks GC!
+
+SELECT
+     reserve_uuid
+  INTO
+     reserve_uuid_min
+  FROM exchange.reserves
+  ORDER BY reserve_uuid ASC
+  LIMIT 1;
+
+DELETE FROM exchange.reserves_out
+  WHERE reserve_uuid < reserve_uuid_min;
+
+-- FIXME: this query will be horribly slow;
+-- need to find another way to formulate it...
+DELETE FROM exchange.denominations
+  WHERE expire_legal < in_now
+    AND denominations_serial NOT IN
+      (SELECT DISTINCT denominations_serial
+         FROM exchange.reserves_out)
+    AND denominations_serial NOT IN
+      (SELECT DISTINCT denominations_serial
+         FROM exchange.known_coins
+        WHERE coin_pub IN
+          (SELECT DISTINCT coin_pub
+             FROM exchange.recoup))
+    AND denominations_serial NOT IN
+      (SELECT DISTINCT denominations_serial
+         FROM exchange.known_coins
+        WHERE coin_pub IN
+          (SELECT DISTINCT coin_pub
+             FROM exchange.recoup_refresh));
+
+SELECT
+     melt_serial_id
+  INTO
+     melt_min
+  FROM exchange.refresh_commitments
+  ORDER BY melt_serial_id ASC
+  LIMIT 1;
+
+DELETE FROM exchange.refresh_revealed_coins
+  WHERE melt_serial_id < melt_min;
+
+DELETE FROM exchange.refresh_transfer_keys
+  WHERE melt_serial_id < melt_min;
+
+SELECT
+     known_coin_id
+  INTO
+     coin_min
+  FROM exchange.known_coins
+  ORDER BY known_coin_id ASC
+  LIMIT 1;
+
+DELETE FROM exchange.deposits
+  WHERE known_coin_id < coin_min;
+
+SELECT
+     deposit_serial_id
+  INTO
+     deposit_min
+  FROM exchange.deposits
+  ORDER BY deposit_serial_id ASC
+  LIMIT 1;
+
+DELETE FROM exchange.refunds
+  WHERE deposit_serial_id < deposit_min;
+
+DELETE FROM exchange.aggregation_tracking
+  WHERE deposit_serial_id < deposit_min;
+
+SELECT
+     denominations_serial
+  INTO
+     denom_min
+  FROM exchange.denominations
+  ORDER BY denominations_serial ASC
+  LIMIT 1;
+
+DELETE FROM exchange.cs_nonce_locks
+  WHERE max_denomination_serial <= denom_min;
+
+END $$;
+
+
+