From a322770d290cae69e7d2f7629ee575e068254428 Mon Sep 17 00:00:00 2001
From: Christian Grothoff <christian@grothoff.org>
Date: Sun, 27 Nov 2022 14:05:47 +0100
Subject: more work on SQL refactoring

---
 src/exchangedb/exchange-0001-part.sql | 464 ----------------------------------
 1 file changed, 464 deletions(-)

(limited to 'src/exchangedb/exchange-0001-part.sql')

diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql
index 8a1036085..29412ca75 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -14,470 +14,6 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
--- ------------------------------ legitimization_processes ----------------------------------------
-
-SELECT create_table_legitimization_processes();
-
-COMMENT ON TABLE legitimization_processes
-  IS 'List of legitimization processes (ongoing and completed) by account and provider';
-COMMENT ON COLUMN legitimization_processes.legitimization_process_serial_id
-  IS 'unique ID for this legitimization process at the exchange';
-COMMENT ON COLUMN legitimization_processes.h_payto
-  IS 'foreign key linking the entry to the wire_targets table, NOT a primary key (multiple legitimizations are possible per wire target)';
-COMMENT ON COLUMN legitimization_processes.expiration_time
-  IS 'in the future if the respective KYC check was passed successfully';
-COMMENT ON COLUMN legitimization_processes.provider_section
-  IS 'Configuration file section with details about this provider';
-COMMENT ON COLUMN legitimization_processes.provider_user_id
-  IS 'Identifier for the user at the provider that was used for the legitimization. NULL if provider is unaware.';
-COMMENT ON COLUMN legitimization_processes.provider_legitimization_id
-  IS 'Identifier for the specific legitimization process at the provider. NULL if legitimization was not started.';
-
-SELECT add_constraints_to_legitimization_processes_partition('default');
-
-
--- ------------------------------ legitimization_requirements_ ----------------------------------------
-
-SELECT create_table_legitimization_requirements();
-
-COMMENT ON TABLE legitimization_requirements
-  IS 'List of required legitimization by account';
-COMMENT ON COLUMN legitimization_requirements.legitimization_requirement_serial_id
-  IS 'unique ID for this legitimization requirement at the exchange';
-COMMENT ON COLUMN legitimization_requirements.h_payto
-  IS 'foreign key linking the entry to the wire_targets table, NOT a primary key (multiple legitimizations are possible per wire target)';
-COMMENT ON COLUMN legitimization_requirements.required_checks
-  IS 'space-separated list of required checks';
-
-SELECT add_constraints_to_legitimization_requirements_partition('default');
-
-
-
--- ------------------------------ reserves ----------------------------------------
-
-SELECT create_table_reserves();
-
-COMMENT ON TABLE reserves
-  IS 'Summarizes the balance of a reserve. Updated when new funds are added or withdrawn.';
-COMMENT ON COLUMN reserves.reserve_pub
-  IS 'EdDSA public key of the reserve. Knowledge of the private key implies ownership over the balance.';
-COMMENT ON COLUMN reserves.current_balance_val
-  IS 'Current balance remaining with the reserve.';
-COMMENT ON COLUMN reserves.purses_active
-  IS 'Number of purses that were created by this reserve that are not expired and not fully paid.';
-COMMENT ON COLUMN reserves.purses_allowed
-  IS 'Number of purses that this reserve is allowed to have active at most.';
-COMMENT ON COLUMN reserves.expiration_date
-  IS 'Used to trigger closing of reserves that have not been drained after some time';
-COMMENT ON COLUMN reserves.gc_date
-  IS 'Used to forget all information about a reserve during garbage collection';
-
--- ------------------------------ reserves_in ----------------------------------------
-
-SELECT create_table_reserves_in();
-
-COMMENT ON TABLE reserves_in
-  IS 'list of transfers of funds into the reserves, one per incoming wire transfer';
-COMMENT ON COLUMN reserves_in.wire_source_h_payto
-  IS 'Identifies the debited bank account and KYC status';
-COMMENT ON COLUMN reserves_in.reserve_pub
-  IS 'Public key of the reserve. Private key signifies ownership of the remaining balance.';
-COMMENT ON COLUMN reserves_in.credit_val
-  IS 'Amount that was transferred into the reserve';
-
-
-SELECT add_constraints_to_reserves_in_partition('default');
-
--- ------------------------------ reserves_close ----------------------------------------
-
-SELECT create_table_reserves_close();
-
-COMMENT ON TABLE reserves_close
-  IS 'wire transfers executed by the reserve to close reserves';
-COMMENT ON COLUMN reserves_close.wire_target_h_payto
-  IS 'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.';
-
-
-SELECT add_constraints_to_reserves_close_partition('default');
-
-
-
-
-
-
--- ------------------------------ reserves_open_requests ----------------------------------------
-
-SELECT create_table_reserves_open_requests();
-
-COMMENT ON TABLE reserves_open_requests
-  IS 'requests to keep a reserve open';
-COMMENT ON COLUMN reserves_open_requests.reserve_payment_val
-  IS 'Funding to pay for the request from the reserve balance itself.';
-
-SELECT add_constraints_to_reserves_open_request_partition('default');
-
-
--- ------------------------------ reserves_open_deposits ----------------------------------------
-
-SELECT create_table_reserves_open_deposits();
-
-COMMENT ON TABLE reserves_open_deposits
-  IS 'coin contributions paying for a reserve to remain open';
-COMMENT ON COLUMN reserves_open_deposits.reserve_pub
-  IS 'Identifies the specific reserve being paid for (possibly together with reserve_sig).';
-
-
-SELECT add_constraints_to_reserves_open_deposits_partition('default');
-
-
--- ------------------------------ reserves_out ----------------------------------------
-
-SELECT create_table_reserves_out();
-
-COMMENT ON TABLE reserves_out
-  IS 'Withdraw operations performed on reserves.';
-COMMENT ON COLUMN reserves_out.h_blind_ev
-  IS 'Hash of the blinded coin, used as primary key here so that broken clients that use a non-random coin or blinding factor fail to withdraw (otherwise they would fail on deposit when the coin is not unique there).';
-COMMENT ON COLUMN reserves_out.denominations_serial
-  IS 'We do not CASCADE ON DELETE here, we may keep the denomination data alive';
-
-SELECT add_constraints_to_reserves_out_partition('default');
-
-
-SELECT create_table_reserves_out_by_reserve();
-
-COMMENT ON TABLE reserves_out_by_reserve
-  IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.';
-
-CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-BEGIN
-  INSERT INTO exchange.reserves_out_by_reserve
-    (reserve_uuid
-    ,h_blind_ev)
-  VALUES
-    (NEW.reserve_uuid
-    ,NEW.h_blind_ev);
-  RETURN NEW;
-END $$;
-COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
-  IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
-
-CREATE TRIGGER reserves_out_on_insert
-  AFTER INSERT
-   ON reserves_out
-   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
-
-CREATE OR REPLACE FUNCTION reserves_out_by_reserve_delete_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-BEGIN
-  DELETE FROM exchange.reserves_out_by_reserve
-   WHERE reserve_uuid = OLD.reserve_uuid;
-  RETURN OLD;
-END $$;
-COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
-  IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
-
-CREATE TRIGGER reserves_out_on_delete
-  AFTER DELETE
-    ON reserves_out
-   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
-
-
--- ------------------------------ known_coins ----------------------------------------
-
-SELECT create_table_known_coins();
-
-COMMENT ON TABLE known_coins
-  IS 'information about coins and their signatures, so we do not have to store the signatures more than once if a coin is involved in multiple operations';
-COMMENT ON COLUMN known_coins.denominations_serial
-  IS 'Denomination of the coin, determines the value of the original coin and applicable fees for coin-specific operations.';
-COMMENT ON COLUMN known_coins.coin_pub
-  IS 'EdDSA public key of the coin';
-COMMENT ON COLUMN known_coins.remaining_val
-  IS 'Value of the coin that remains to be spent';
-COMMENT ON COLUMN known_coins.age_commitment_hash
-  IS 'Optional hash of the age commitment for age restrictions as per DD 24 (active if denom_type has the respective bit set)';
-COMMENT ON COLUMN known_coins.denom_sig
-  IS 'This is the signature of the exchange that affirms that the coin is a valid coin. The specific signature type depends on denom_type of the denomination.';
-
-SELECT add_constraints_to_known_coins_partition('default');
-
-
--- ------------------------------ refresh_commitments ----------------------------------------
-
-SELECT create_table_refresh_commitments();
-
-COMMENT ON TABLE refresh_commitments
-  IS 'Commitments made when melting coins and the gamma value chosen by the exchange.';
-COMMENT ON COLUMN refresh_commitments.noreveal_index
-  IS 'The gamma value chosen by the exchange in the cut-and-choose protocol';
-COMMENT ON COLUMN refresh_commitments.rc
-  IS 'Commitment made by the client, hash over the various client inputs in the cut-and-choose protocol';
-COMMENT ON COLUMN refresh_commitments.old_coin_pub
-  IS 'Coin being melted in the refresh process.';
-
-SELECT add_constraints_to_refresh_commitments_partition('default');
-
-
--- ------------------------------ refresh_revealed_coins ----------------------------------------
-
-SELECT create_table_refresh_revealed_coins();
-
-COMMENT ON TABLE refresh_revealed_coins
-  IS 'Revelations about the new coins that are to be created during a melting session.';
-COMMENT ON COLUMN refresh_revealed_coins.rrc_serial
-  IS 'needed for exchange-auditor replication logic';
-COMMENT ON COLUMN refresh_revealed_coins.melt_serial_id
-  IS 'Identifies the refresh commitment (rc) of the melt operation.';
-COMMENT ON COLUMN refresh_revealed_coins.freshcoin_index
-  IS 'index of the fresh coin being created (one melt operation may result in multiple fresh coins)';
-COMMENT ON COLUMN refresh_revealed_coins.coin_ev
-  IS 'envelope of the new coin to be signed';
-COMMENT ON COLUMN refresh_revealed_coins.ewv
-  IS 'exchange contributed values in the creation of the fresh coin (see /csr)';
-COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
-  IS 'hash of the envelope of the new coin to be signed (for lookups)';
-COMMENT ON COLUMN refresh_revealed_coins.ev_sig
-  IS 'exchange signature over the envelope';
-
-SELECT add_constraints_to_refresh_revealed_coins_partition('default');
-
-
--- ------------------------------ refresh_transfer_keys ----------------------------------------
-
-SELECT create_table_refresh_transfer_keys();
-
-COMMENT ON TABLE refresh_transfer_keys
-  IS 'Transfer keys of a refresh operation (the data revealed to the exchange).';
-COMMENT ON COLUMN refresh_transfer_keys.rtc_serial
-  IS 'needed for exchange-auditor replication logic';
-COMMENT ON COLUMN refresh_transfer_keys.melt_serial_id
-  IS 'Identifies the refresh commitment (rc) of the operation.';
-COMMENT ON COLUMN refresh_transfer_keys.transfer_pub
-  IS 'transfer public key for the gamma index';
-COMMENT ON COLUMN refresh_transfer_keys.transfer_privs
-  IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been revealed, with the gamma entry being skipped';
-
-SELECT add_constraints_to_refresh_transfer_keys_partition('default');
-
-
--- ------------------------------ deposits ----------------------------------------
-
-SELECT create_table_deposits();
-
-COMMENT ON TABLE deposits
-  IS 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).';
-COMMENT ON COLUMN deposits.shard
-  IS 'Used for load sharding in the materialized indices. Should be set based on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.';
-COMMENT ON COLUMN deposits.known_coin_id
-  IS 'Used for garbage collection';
-COMMENT ON COLUMN deposits.wire_target_h_payto
-  IS 'Identifies the target bank account and KYC status';
-COMMENT ON COLUMN deposits.wire_salt
-  IS 'Salt used when hashing the payto://-URI to get the h_wire';
-COMMENT ON COLUMN deposits.done
-  IS 'Set to TRUE once we have included this deposit in some aggregate wire transfer to the merchant';
-COMMENT ON COLUMN deposits.policy_blocked
-  IS 'True if the aggregation of the deposit is currently blocked by some policy extension mechanism. Used to filter out deposits that must not be processed by the canonical deposit logic.';
-COMMENT ON COLUMN deposits.policy_details_serial_id
-  IS 'References policy extensions table, NULL if extensions are not used';
-
-SELECT add_constraints_to_deposits_partition('default');
-
-
-SELECT create_table_deposits_by_ready();
-
-COMMENT ON TABLE deposits_by_ready
-  IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER below';
-
-
-SELECT create_table_deposits_for_matching();
-
-COMMENT ON TABLE deposits_for_matching
-  IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via TRIGGER below';
-
-CREATE OR REPLACE FUNCTION deposits_insert_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-DECLARE
-  is_ready BOOLEAN;
-BEGIN
-  is_ready  = NOT (NEW.done OR NEW.policy_blocked);
-
-  IF (is_ready)
-  THEN
-    INSERT INTO exchange.deposits_by_ready
-      (wire_deadline
-      ,shard
-      ,coin_pub
-      ,deposit_serial_id)
-    VALUES
-      (NEW.wire_deadline
-      ,NEW.shard
-      ,NEW.coin_pub
-      ,NEW.deposit_serial_id);
-    INSERT INTO exchange.deposits_for_matching
-      (refund_deadline
-      ,merchant_pub
-      ,coin_pub
-      ,deposit_serial_id)
-    VALUES
-      (NEW.refund_deadline
-      ,NEW.merchant_pub
-      ,NEW.coin_pub
-      ,NEW.deposit_serial_id);
-  END IF;
-  RETURN NEW;
-END $$;
-COMMENT ON FUNCTION deposits_insert_trigger()
-  IS 'Replicate deposit inserts into materialized indices.';
-
-CREATE TRIGGER deposits_on_insert
-  AFTER INSERT
-   ON deposits
-   FOR EACH ROW EXECUTE FUNCTION deposits_insert_trigger();
-
-CREATE OR REPLACE FUNCTION deposits_update_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-DECLARE
-  was_ready BOOLEAN;
-DECLARE
-  is_ready BOOLEAN;
-BEGIN
-  was_ready = NOT (OLD.done OR OLD.policy_blocked);
-  is_ready  = NOT (NEW.done OR NEW.policy_blocked);
-  IF (was_ready AND NOT is_ready)
-  THEN
-    DELETE FROM exchange.deposits_by_ready
-     WHERE wire_deadline = OLD.wire_deadline
-       AND shard = OLD.shard
-       AND coin_pub = OLD.coin_pub
-       AND deposit_serial_id = OLD.deposit_serial_id;
-    DELETE FROM exchange.deposits_for_matching
-     WHERE refund_deadline = OLD.refund_deadline
-       AND merchant_pub = OLD.merchant_pub
-       AND coin_pub = OLD.coin_pub
-       AND deposit_serial_id = OLD.deposit_serial_id;
-  END IF;
-  IF (is_ready AND NOT was_ready)
-  THEN
-    INSERT INTO exchange.deposits_by_ready
-      (wire_deadline
-      ,shard
-      ,coin_pub
-      ,deposit_serial_id)
-    VALUES
-      (NEW.wire_deadline
-      ,NEW.shard
-      ,NEW.coin_pub
-      ,NEW.deposit_serial_id);
-    INSERT INTO exchange.deposits_for_matching
-      (refund_deadline
-      ,merchant_pub
-      ,coin_pub
-      ,deposit_serial_id)
-    VALUES
-      (NEW.refund_deadline
-      ,NEW.merchant_pub
-      ,NEW.coin_pub
-      ,NEW.deposit_serial_id);
-  END IF;
-  RETURN NEW;
-END $$;
-COMMENT ON FUNCTION deposits_update_trigger()
-  IS 'Replicate deposits changes into materialized indices.';
-
-CREATE TRIGGER deposits_on_update
-  AFTER UPDATE
-    ON deposits
-   FOR EACH ROW EXECUTE FUNCTION deposits_update_trigger();
-
-CREATE OR REPLACE FUNCTION deposits_delete_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-DECLARE
-  was_ready BOOLEAN;
-BEGIN
-  was_ready  = NOT (OLD.done OR OLD.policy_blocked);
-
-  IF (was_ready)
-  THEN
-    DELETE FROM exchange.deposits_by_ready
-     WHERE wire_deadline = OLD.wire_deadline
-       AND shard = OLD.shard
-       AND coin_pub = OLD.coin_pub
-       AND deposit_serial_id = OLD.deposit_serial_id;
-    DELETE FROM exchange.deposits_for_matching
-     WHERE refund_deadline = OLD.refund_deadline
-       AND merchant_pub = OLD.merchant_pub
-       AND coin_pub = OLD.coin_pub
-       AND deposit_serial_id = OLD.deposit_serial_id;
-  END IF;
-  RETURN NEW;
-END $$;
-COMMENT ON FUNCTION deposits_delete_trigger()
-  IS 'Replicate deposit deletions into materialized indices.';
-
-CREATE TRIGGER deposits_on_delete
-  AFTER DELETE
-   ON deposits
-   FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger();
-
-
--- ------------------------------ refunds ----------------------------------------
-
-SELECT create_table_refunds();
-
-COMMENT ON TABLE refunds
-  IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.';
-COMMENT ON COLUMN refunds.deposit_serial_id
-  IS 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.';
-COMMENT ON COLUMN refunds.rtransaction_id
-  IS 'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund';
-
-SELECT add_constraints_to_refunds_partition('default');
-
-
--- ------------------------------ wire_out ----------------------------------------
-
-SELECT create_table_wire_out();
-
-COMMENT ON TABLE wire_out
-  IS 'wire transfers the exchange has executed';
-COMMENT ON COLUMN wire_out.exchange_account_section
-  IS 'identifies the configuration section with the debit account of this payment';
-COMMENT ON COLUMN wire_out.wire_target_h_payto
-  IS 'Identifies the credited bank account and KYC status';
-
-SELECT add_constraints_to_wire_out_partition('default');
-
-CREATE OR REPLACE FUNCTION wire_out_delete_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-BEGIN
-  DELETE FROM exchange.aggregation_tracking
-   WHERE wtid_raw = OLD.wtid_raw;
-  RETURN OLD;
-END $$;
-COMMENT ON FUNCTION wire_out_delete_trigger()
-  IS 'Replicate reserve_out deletions into aggregation_tracking. This replaces an earlier use of an ON DELETE CASCADE that required a DEFERRABLE constraint and conflicted with nice partitioning.';
-
-CREATE TRIGGER wire_out_on_delete
-  AFTER DELETE
-    ON wire_out
-   FOR EACH ROW EXECUTE FUNCTION wire_out_delete_trigger();
-
-
 
 -- ------------------------------ aggregation_transient ----------------------------------------
 
-- 
cgit v1.2.3

