diff options
Diffstat (limited to 'src/exchangedb/exchange-0001-part.sql')
| -rw-r--r-- | src/exchangedb/exchange-0001-part.sql | 464 |
1 files changed, 0 insertions, 464 deletions
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 ---------------------------------------- |