merchant

merchant-0036.sql.in (5209B)

---

 --
 -- This file is part of TALER
 -- Copyright (C) 2026 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/>
 
 -- @file merchant-0036.sql.in
 -- @brief Move per-instance tables into per-instance schemas
 --   merchant_instance_<merchant_serial>.  Each existing row of
 --   merchant_instances is converted by calling
 --   merchant.create_instance_schema(serial), copying the rows of every
 --   per-instance table into the new schema (dropping the merchant_serial
 --   column), and finally dropping the now-empty merchant.<table>.
 -- @author Christian Grothoff
 
 BEGIN;
 
 SELECT _v.register_patch('merchant-0036', NULL, NULL);
 
 SET search_path TO merchant;
 
 -- ---------------------------------------------------------------------
 -- Step 0: Fold merchant_keys.merchant_priv into merchant_instances as a
 -- nullable column.  Setting merchant_priv to NULL replaces deleting the
 -- key row.  The merchant_keys table itself is dropped in step 4.
 -- ---------------------------------------------------------------------
 ALTER TABLE merchant.merchant_instances
   ADD COLUMN merchant_priv BYTEA
     CHECK (merchant_priv IS NULL OR LENGTH(merchant_priv) = 32);
 COMMENT ON COLUMN merchant.merchant_instances.merchant_priv
   IS 'private key of the instance, NULL once the key has been "deleted"';
 UPDATE merchant.merchant_instances mi
    SET merchant_priv = mk.merchant_priv
   FROM merchant.merchant_keys mk
  WHERE mk.merchant_serial = mi.merchant_serial;
 
 CREATE TABLE merchant.instance_fixups
  (instance_fixup_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY
   ,migration_name TEXT NOT NULL
   ,version INT8 NOT NULL);
 COMMENT ON TABLE instance_fixups
   IS 'Fixups to setup the per-instance tables';
 COMMENT ON COLUMN instance_fixups.migration_name
   IS 'Name of the stored procedure to run to fix up the schema of the instance';
 COMMENT ON COLUMN instance_fixups.version
   IS 'Version of the DB in which the given fix up should happen';
 
 CREATE INDEX instance_fixups_by_version
   ON instance_fixups (version);
 COMMENT ON INDEX instance_fixups_by_version
   IS 'Used by create_instance_schema (and in the future on import)';
 
 -- ---------------------------------------------------------------------
 -- Step 1: Install the schema constructor and actual fix-ups.
 -- procedures.sql will re-install the same definition (DROP IF EXISTS),
 -- so installing it here is harmless and lets the migration call it.
 -- ---------------------------------------------------------------------
 #include "../create_tables.sql"
 #include "../pg_create_instance_schema.sql"
 #include "../pg_fixup_instance_schema.sql"
 #include "merchant-0036-init.sql.fragment"
 
 -- ---------------------------------------------------------------------
 -- Step 2: Build a per-instance schema for every existing instance.
 -- ---------------------------------------------------------------------
 DO $MIG$
 DECLARE
   rec RECORD;
 BEGIN
   FOR rec IN
     SELECT merchant_serial
       FROM merchant.merchant_instances
   LOOP
     PERFORM merchant.create_instance_schema(rec.merchant_serial);
   END LOOP;
 END $MIG$;
 
 -- ---------------------------------------------------------------------
 -- Step 3: Migrate row data per instance.
 --   Tables are copied in FK-dependency order.  Every copy drops the
 --   merchant_serial column.  After each table is copied, its IDENTITY
 --   sequence (if any) is advanced past MAX(serial).
 --   Indirect-FK tables (merchant_kyc, merchant_deposits, ...) are copied
 --   by JOINing back to the table that owns the merchant_serial.
 -- ---------------------------------------------------------------------
 -- Suppress USER triggers during the bulk copy: per-instance triggers
 -- (e.g. merchant_kyc_insert_trigger) would otherwise fire on every
 -- inserted row and CALL into per-instance procedures that may rely on
 -- runtime invariants we do not have during migration.  This setting is
 -- transaction-local and does not affect normal callers.
 SET session_replication_role = replica;
 
 DO $MIG$
 DECLARE
   rec RECORD;
   s   TEXT;
 BEGIN
   FOR rec IN
     SELECT merchant_serial
       FROM merchant.merchant_instances
   LOOP
     s := 'merchant_instance_' || rec.merchant_serial::TEXT;
 #include "merchant-0036-copy.sql.fragment"
 #include "merchant-0036-setval.sql.fragment"
   END LOOP;
 END $MIG$;
 
 SET session_replication_role = origin;
 
 -- ---------------------------------------------------------------------
 -- Step 4: Drop old per-instance tables from the merchant schema, in
 -- reverse FK-dependency order so CASCADE is not needed.
 -- ---------------------------------------------------------------------
 #include "merchant-0036-drop.sql.fragment"
 
 COMMIT;