diff options
author | Christian Grothoff <christian@grothoff.org> | 2020-01-17 01:23:56 +0100 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2020-01-17 01:28:39 +0100 |
commit | 5b11d19b678283d6fbc72dc9fb3d5f3cfabc1d92 (patch) | |
tree | 0421987cbf3c6b53a541747355fbfcb4acf8d9d1 | |
parent | 6faf6fc732afe58a5da71dd442ede78cdbd7c495 (diff) | |
download | exchange-5b11d19b678283d6fbc72dc9fb3d5f3cfabc1d92.tar.xz |
SQL logic towards DB versioning
-rw-r--r-- | src/exchangedb/0000.sql | 293 | ||||
-rw-r--r-- | src/exchangedb/0001.sql | 368 | ||||
-rw-r--r-- | src/exchangedb/Makefile.am | 6 | ||||
-rw-r--r-- | src/exchangedb/plugin_exchangedb_postgres.c | 18 |
4 files changed, 670 insertions, 15 deletions
diff --git a/src/exchangedb/0000.sql b/src/exchangedb/0000.sql new file mode 100644 index 000000000..1483e2015 --- /dev/null +++ b/src/exchangedb/0000.sql @@ -0,0 +1,293 @@ +-- LICENSE AND COPYRIGHT +-- +-- Copyright (C) 2010 Hubert depesz Lubaczewski +-- +-- This program is distributed under the (Revised) BSD License: +-- L<http://www.opensource.org/licenses/bsd-license.php> +-- +-- Redistribution and use in source and binary forms, with or without +-- modification, are permitted provided that the following conditions +-- are met: +-- +-- * Redistributions of source code must retain the above copyright +-- notice, this list of conditions and the following disclaimer. +-- +-- * Redistributions in binary form must reproduce the above copyright +-- notice, this list of conditions and the following disclaimer in the +-- documentation and/or other materials provided with the distribution. +-- +-- * Neither the name of Hubert depesz Lubaczewski's Organization +-- nor the names of its contributors may be used to endorse or +-- promote products derived from this software without specific +-- prior written permission. +-- +-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" +-- AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE +-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE +-- DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE +-- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL +-- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR +-- SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER +-- CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, +-- OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE +-- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +-- +-- Code origin: https://gitlab.com/depesz/Versioning/blob/master/install.versioning.sql +-- +-- +-- # NAME +-- +-- **Versioning** - simplistic take on tracking and applying changes to databases. +-- +-- # DESCRIPTION +-- +-- This project strives to provide simple way to manage changes to +-- database. +-- +-- Instead of making changes on development server, then finding +-- differences between production and development, deciding which ones +-- should be installed on production, and finding a way to install them - +-- you start with writing diffs themselves! +-- +-- # INSTALLATION +-- +-- To install versioning simply run install.versioning.sql in your database +-- (all of them: production, stage, test, devel, ...). +-- +-- # USAGE +-- +-- In your files with patches to database, put whole logic in single +-- transaction, and use \_v.\* functions - usually \_v.register_patch() at +-- least to make sure everything is OK. +-- +-- For example. Let's assume you have patch files: +-- +-- ## 0001.sql: +-- +-- ``` +-- create table users (id serial primary key, username text); +-- ``` +-- +-- ## 0002.sql: +-- +-- ``` +-- insert into users (username) values ('depesz'); +-- ``` +-- To change it to use versioning you would change the files, to this +-- state: +-- +-- 0000.sql: +-- +-- ``` +-- BEGIN; +-- select _v.register_patch('000-base', NULL, NULL); +-- create table users (id serial primary key, username text); +-- COMMIT; +-- ``` +-- +-- ## 0002.sql: +-- +-- ``` +-- BEGIN; +-- select _v.register_patch('001-users', ARRAY['000-base'], NULL); +-- insert into users (username) values ('depesz'); +-- COMMIT; +-- ``` +-- +-- This will make sure that patch 001-users can only be applied after +-- 000-base. +-- +-- # AVAILABLE FUNCTIONS +-- +-- ## \_v.register_patch( TEXT ) +-- +-- Registers named patch, or dies if it is already registered. +-- +-- Returns integer which is id of patch in \_v.patches table - only if it +-- succeeded. +-- +-- ## \_v.register_patch( TEXT, TEXT[] ) +-- +-- Same as \_v.register_patch( TEXT ), but checks is all given patches (given as +-- array in second argument) are already registered. +-- +-- ## \_v.register_patch( TEXT, TEXT[], TEXT[] ) +-- +-- Same as \_v.register_patch( TEXT, TEXT[] ), but also checks if there are no conflicts with preexisting patches. +-- +-- Third argument is array of names of patches that conflict with current one. So +-- if any of them is installed - register_patch will error out. +-- +-- ## \_v.unregister_patch( TEXT ) +-- +-- Removes information about given patch from the versioning data. +-- +-- It doesn't remove objects that were created by this patch - just removes +-- metainformation. +-- +-- ## \_v.assert_user_is_superuser() +-- +-- Make sure that current patch is being loaded by superuser. +-- +-- If it's not - it will raise exception, and break transaction. +-- +-- ## \_v.assert_user_is_not_superuser() +-- +-- Make sure that current patch is not being loaded by superuser. +-- +-- If it is - it will raise exception, and break transaction. +-- +-- ## \_v.assert_user_is_one_of(TEXT, TEXT, ... ) +-- +-- Make sure that current patch is being loaded by one of listed users. +-- +-- If ```current_user``` is not listed as one of arguments - function will raise +-- exception and break the transaction. + +BEGIN; + +-- This file adds versioning support to database it will be loaded to. +-- It requires that PL/pgSQL is already loaded - will raise exception otherwise. +-- All versioning "stuff" (tables, functions) is in "_v" schema. + +-- All functions are defined as 'RETURNS SETOF INT4' to be able to make them to RETURN literaly nothing (0 rows). +-- >> RETURNS VOID<< IS similar, but it still outputs "empty line" in psql when calling. +CREATE SCHEMA IF NOT EXISTS _v; +COMMENT ON SCHEMA _v IS 'Schema for versioning data and functionality.'; + +CREATE TABLE IF NOT EXISTS _v.patches ( + patch_name TEXT PRIMARY KEY, + applied_tsz TIMESTAMPTZ NOT NULL DEFAULT now(), + applied_by TEXT NOT NULL, + requires TEXT[], + conflicts TEXT[] +); +COMMENT ON TABLE _v.patches IS 'Contains information about what patches are currently applied on database.'; +COMMENT ON COLUMN _v.patches.patch_name IS 'Name of patch, has to be unique for every patch.'; +COMMENT ON COLUMN _v.patches.applied_tsz IS 'When the patch was applied.'; +COMMENT ON COLUMN _v.patches.applied_by IS 'Who applied this patch (PostgreSQL username)'; +COMMENT ON COLUMN _v.patches.requires IS 'List of patches that are required for given patch.'; +COMMENT ON COLUMN _v.patches.conflicts IS 'List of patches that conflict with given patch.'; + +CREATE OR REPLACE FUNCTION _v.register_patch( IN in_patch_name TEXT, IN in_requirements TEXT[], in_conflicts TEXT[], OUT versioning INT4 ) RETURNS setof INT4 AS $$ +DECLARE + t_text TEXT; + t_text_a TEXT[]; + i INT4; +BEGIN + -- Thanks to this we know only one patch will be applied at a time + LOCK TABLE _v.patches IN EXCLUSIVE MODE; + + SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name; + IF FOUND THEN + RAISE EXCEPTION 'Patch % is already applied!', in_patch_name; + END IF; + + t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE patch_name = any( in_conflicts ) ); + IF array_upper( t_text_a, 1 ) IS NOT NULL THEN + RAISE EXCEPTION 'Versioning patches conflict. Conflicting patche(s) installed: %.', array_to_string( t_text_a, ', ' ); + END IF; + + IF array_upper( in_requirements, 1 ) IS NOT NULL THEN + t_text_a := '{}'; + FOR i IN array_lower( in_requirements, 1 ) .. array_upper( in_requirements, 1 ) LOOP + SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_requirements[i]; + IF NOT FOUND THEN + t_text_a := t_text_a || in_requirements[i]; + END IF; + END LOOP; + IF array_upper( t_text_a, 1 ) IS NOT NULL THEN + RAISE EXCEPTION 'Missing prerequisite(s): %.', array_to_string( t_text_a, ', ' ); + END IF; + END IF; + + INSERT INTO _v.patches (patch_name, applied_tsz, applied_by, requires, conflicts ) VALUES ( in_patch_name, now(), current_user, coalesce( in_requirements, '{}' ), coalesce( in_conflicts, '{}' ) ); + RETURN; +END; +$$ language plpgsql; +COMMENT ON FUNCTION _v.register_patch( TEXT, TEXT[], TEXT[] ) IS 'Function to register patches in database. Raises exception if there are conflicts, prerequisites are not installed or the migration has already been installed.'; + +CREATE OR REPLACE FUNCTION _v.register_patch( TEXT, TEXT[] ) RETURNS setof INT4 AS $$ + SELECT _v.register_patch( $1, $2, NULL ); +$$ language sql; +COMMENT ON FUNCTION _v.register_patch( TEXT, TEXT[] ) IS 'Wrapper to allow registration of patches without conflicts.'; +CREATE OR REPLACE FUNCTION _v.register_patch( TEXT ) RETURNS setof INT4 AS $$ + SELECT _v.register_patch( $1, NULL, NULL ); +$$ language sql; +COMMENT ON FUNCTION _v.register_patch( TEXT ) IS 'Wrapper to allow registration of patches without requirements and conflicts.'; + +CREATE OR REPLACE FUNCTION _v.unregister_patch( IN in_patch_name TEXT, OUT versioning INT4 ) RETURNS setof INT4 AS $$ +DECLARE + i INT4; + t_text_a TEXT[]; +BEGIN + -- Thanks to this we know only one patch will be applied at a time + LOCK TABLE _v.patches IN EXCLUSIVE MODE; + + t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE in_patch_name = ANY( requires ) ); + IF array_upper( t_text_a, 1 ) IS NOT NULL THEN + RAISE EXCEPTION 'Cannot uninstall %, as it is required by: %.', in_patch_name, array_to_string( t_text_a, ', ' ); + END IF; + + DELETE FROM _v.patches WHERE patch_name = in_patch_name; + GET DIAGNOSTICS i = ROW_COUNT; + IF i < 1 THEN + RAISE EXCEPTION 'Patch % is not installed, so it can''t be uninstalled!', in_patch_name; + END IF; + + RETURN; +END; +$$ language plpgsql; +COMMENT ON FUNCTION _v.unregister_patch( TEXT ) IS 'Function to unregister patches in database. Dies if the patch is not registered, or if unregistering it would break dependencies.'; + +CREATE OR REPLACE FUNCTION _v.assert_patch_is_applied( IN in_patch_name TEXT ) RETURNS TEXT as $$ +DECLARE + t_text TEXT; +BEGIN + SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name = in_patch_name; + IF NOT FOUND THEN + RAISE EXCEPTION 'Patch % is not applied!', in_patch_name; + END IF; + RETURN format('Patch %s is applied.', in_patch_name); +END; +$$ language plpgsql; +COMMENT ON FUNCTION _v.assert_patch_is_applied( TEXT ) IS 'Function that can be used to make sure that patch has been applied.'; + +CREATE OR REPLACE FUNCTION _v.assert_user_is_superuser() RETURNS TEXT as $$ +DECLARE + v_super bool; +BEGIN + SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user; + IF v_super THEN + RETURN 'assert_user_is_superuser: OK'; + END IF; + RAISE EXCEPTION 'Current user is not superuser - cannot continue.'; +END; +$$ language plpgsql; +COMMENT ON FUNCTION _v.assert_user_is_superuser() IS 'Function that can be used to make sure that patch is being applied using superuser account.'; + +CREATE OR REPLACE FUNCTION _v.assert_user_is_not_superuser() RETURNS TEXT as $$ +DECLARE + v_super bool; +BEGIN + SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user; + IF v_super THEN + RAISE EXCEPTION 'Current user is superuser - cannot continue.'; + END IF; + RETURN 'assert_user_is_not_superuser: OK'; +END; +$$ language plpgsql; +COMMENT ON FUNCTION _v.assert_user_is_not_superuser() IS 'Function that can be used to make sure that patch is being applied using normal (not superuser) account.'; + +CREATE OR REPLACE FUNCTION _v.assert_user_is_one_of(VARIADIC p_acceptable_users TEXT[] ) RETURNS TEXT as $$ +DECLARE +BEGIN + IF current_user = any( p_acceptable_users ) THEN + RETURN 'assert_user_is_one_of: OK'; + END IF; + RAISE EXCEPTION 'User is not one of: % - cannot continue.', p_acceptable_users; +END; +$$ language plpgsql; +COMMENT ON FUNCTION _v.assert_user_is_one_of(TEXT[]) IS 'Function that can be used to make sure that patch is being applied by one of defined users.'; + +COMMIT; diff --git a/src/exchangedb/0001.sql b/src/exchangedb/0001.sql new file mode 100644 index 000000000..92e0d599c --- /dev/null +++ b/src/exchangedb/0001.sql @@ -0,0 +1,368 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2020 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/> +-- + +-- Everything in one big transaction +BEGIN; + +-- Check patch versioning is in place. +SELECT _v.register_patch('0001', NULL, NULL); + + +-- Main denominations table. All the coins the exchange knows about. +CREATE TABLE IF NOT EXISTS denominations + (denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64) + ,denom_pub BYTEA NOT NULL + ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,valid_from INT8 NOT NULL + ,expire_withdraw INT8 NOT NULL + ,expire_deposit INT8 NOT NULL + ,expire_legal INT8 NOT NULL + ,coin_val INT8 NOT NULL + ,coin_frac INT4 NOT NULL + ,fee_withdraw_val INT8 NOT NULL + ,fee_withdraw_frac INT4 NOT NULL + ,fee_deposit_val INT8 NOT NULL + ,fee_deposit_frac INT4 NOT NULL + ,fee_refresh_val INT8 NOT NULL + ,fee_refresh_frac INT4 NOT NULL + ,fee_refund_val INT8 NOT NULL + ,fee_refund_frac INT4 NOT NULL + ); +CREATE INDEX denominations_expire_legal_index + ON denominations + (expire_legal); + +-- denomination_revocations table is for remembering which denomination keys have been revoked +CREATE TABLE IF NOT EXISTS denomination_revocations + (denom_revocations_serial_id BIGSERIAL UNIQUE + ,denom_pub_hash BYTEA PRIMARY KEY REFERENCES denominations (denom_pub_hash) ON DELETE CASCADE + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ); +-- reserves table is for summarization of a reserve. It is updated when new +-- funds are added and existing funds are withdrawn. The 'expiration_date' +-- can be used to eventually get rid of reserves that have not been used +-- for a very long time (usually by refunding the owner) +CREATE TABLE IF NOT EXISTS reserves + (reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32) + ,account_details TEXT NOT NULL + ,current_balance_val INT8 NOT NULL + ,current_balance_frac INT4 NOT NULL + ,expiration_date INT8 NOT NULL + ,gc_date INT8 NOT NULL + ); +-- index on reserves table (TODO: useless due to primary key!?) +CREATE INDEX reserves_reserve_pub_index + ON reserves + (reserve_pub); +-- index for get_expired_reserves +CREATE INDEX reserves_expiration_index + ON reserves + (expiration_date + ,current_balance_val + ,current_balance_frac + ); +-- index for reserve GC operations +CREATE INDEX reserves_gc_index + ON reserves + (gc_date); +-- reserves_in table collects the transactions which transfer funds +-- into the reserve. The rows of this table correspond to each +-- incoming transaction. +CREATE TABLE IF NOT EXISTS reserves_in + (reserve_in_serial_id BIGSERIAL UNIQUE + ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ,wire_reference INT8 NOT NULL + ,credit_val INT8 NOT NULL + ,credit_frac INT4 NOT NULL + ,sender_account_details TEXT NOT NULL + ,exchange_account_section TEXT NOT NULL + ,execution_date INT8 NOT NULL + ,PRIMARY KEY (reserve_pub, wire_reference) + ); +-- Create indices on reserves_in +CREATE INDEX reserves_in_execution_index + ON reserves_in + (exchange_account_section + ,execution_date + ); +CREATE INDEX reserves_in_exchange_account_serial + ON reserves_in + (exchange_account_section, + reserve_in_serial_id DESC + ); +-- This table contains the data for wire transfers the exchange has +-- executed to close a reserve. +CREATE TABLE IF NOT EXISTS reserves_close + (close_uuid BIGSERIAL PRIMARY KEY + ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ,execution_date INT8 NOT NULL + ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32) + ,receiver_account TEXT NOT NULL + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,closing_fee_val INT8 NOT NULL + ,closing_fee_frac INT4 NOT NULL); +CREATE INDEX reserves_close_by_reserve + ON reserves_close + (reserve_pub); +-- Table with the withdraw operations that have been performed on a reserve. +-- The 'h_blind_ev' is the hash of the blinded coin. It serves as a primary +-- key, as (broken) clients that use a non-random coin and blinding factor +-- should fail to even withdraw, as otherwise the coins will fail to deposit +-- (as they really must be unique). +-- For the denom_pub, we do NOT CASCADE on DELETE, we may keep the denomination key alive! +CREATE TABLE IF NOT EXISTS reserves_out + (reserve_out_serial_id BIGSERIAL UNIQUE + ,h_blind_ev BYTEA PRIMARY KEY CHECK (LENGTH(h_blind_ev)=64) + ,denom_pub_hash BYTEA NOT NULL REFERENCES denominations (denom_pub_hash) + ,denom_sig BYTEA NOT NULL + ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) + ,execution_date INT8 NOT NULL + ,amount_with_fee_val INT8 NOT NULL + ,amount_with_fee_frac INT4 NOT NULL + ); +-- Index blindcoins(reserve_pub) for get_reserves_out statement +CREATE INDEX reserves_out_reserve_pub_index + ON reserves_out + (reserve_pub); +CREATE INDEX reserves_out_execution_date + ON reserves_out + (execution_date); +CREATE INDEX reserves_out_for_get_withdraw_info + ON reserves_out + (denom_pub_hash + ,h_blind_ev + ); +-- Table with coins that have been (partially) spent, used to track +-- coin information only once. +CREATE TABLE IF NOT EXISTS known_coins + (coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32) + ,denom_pub_hash BYTEA NOT NULL REFERENCES denominations (denom_pub_hash) ON DELETE CASCADE + ,denom_sig BYTEA NOT NULL + ); +CREATE INDEX known_coins_by_denomination + ON known_coins + (denom_pub_hash); +-- Table with the commitments made when melting a coin. */ +CREATE TABLE IF NOT EXISTS refresh_commitments + (melt_serial_id BIGSERIAL UNIQUE + ,rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64) + ,old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ,old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64) + ,amount_with_fee_val INT8 NOT NULL + ,amount_with_fee_frac INT4 NOT NULL + ,noreveal_index INT4 NOT NULL + ); +CREATE INDEX refresh_commitments_old_coin_pub_index + ON refresh_commitments + (old_coin_pub); +-- Table with the revelations about the new coins that are to be created +-- during a melting session. Includes the session, the cut-and-choose +-- index and the index of the new coin, and the envelope of the new +-- coin to be signed, as well as the encrypted information about the +-- private key and the blinding factor for the coin (for verification +-- in case this newcoin_index is chosen to be revealed) +CREATE TABLE IF NOT EXISTS refresh_revealed_coins + (rc BYTEA NOT NULL REFERENCES refresh_commitments (rc) ON DELETE CASCADE + ,newcoin_index INT4 NOT NULL + ,link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64) + ,denom_pub_hash BYTEA NOT NULL REFERENCES denominations (denom_pub_hash) ON DELETE CASCADE + ,coin_ev BYTEA UNIQUE NOT NULL + ,h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64) + ,ev_sig BYTEA NOT NULL + ,PRIMARY KEY (rc, newcoin_index) + ,UNIQUE (h_coin_ev) + ); +CREATE INDEX refresh_revealed_coins_coin_pub_index + ON refresh_revealed_coins + (denom_pub_hash); +-- Table with the transfer keys of a refresh operation; includes +-- the rc for which this is the link information, the +-- transfer public key (for gamma) and the revealed transfer private +-- keys (array of TALER_CNC_KAPPA - 1 entries, with gamma being skipped) */ +CREATE TABLE IF NOT EXISTS refresh_transfer_keys + (rc BYTEA NOT NULL PRIMARY KEY REFERENCES refresh_commitments (rc) ON DELETE CASCADE + ,transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32) + ,transfer_privs BYTEA NOT NULL + ); +-- for get_link (not sure if this helps, as there should be very few +-- transfer_pubs per rc, but at least in theory this helps the ORDER BY +-- clause. +CREATE INDEX refresh_transfer_keys_coin_tpub + ON refresh_transfer_keys + (rc + ,transfer_pub + ); +-- This table contains the wire transfers the exchange is supposed to +-- execute to transmit funds to the merchants (and manage refunds). +CREATE TABLE IF NOT EXISTS deposits + (deposit_serial_id BIGSERIAL PRIMARY KEY + ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ,amount_with_fee_val INT8 NOT NULL + ,amount_with_fee_frac INT4 NOT NULL + ,timestamp INT8 NOT NULL + ,refund_deadline INT8 NOT NULL + ,wire_deadline INT8 NOT NULL + ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) + ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) + ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64) + ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) + ,wire TEXT NOT NULL + ,tiny BOOLEAN NOT NULL DEFAULT FALSE + ,done BOOLEAN NOT NULL DEFAULT FALSE + ,UNIQUE (coin_pub, merchant_pub, h_contract_terms) + ); +-- Index for get_deposit_for_wtid and get_deposit_statement */ +CREATE INDEX deposits_coin_pub_merchant_contract_index + ON deposits + (coin_pub + ,merchant_pub + ,h_contract_terms + ); +-- Index for deposits_get_ready +CREATE INDEX deposits_get_ready_index + ON deposits + (tiny + ,done + ,wire_deadline + ,refund_deadline + ); +-- Index for deposits_iterate_matching +CREATE INDEX deposits_iterate_matching + ON deposits + (merchant_pub + ,h_wire + ,done + ,wire_deadline + ); +-- Table with information about coins that have been refunded. (Technically +-- one of the deposit operations that a coin was involved with is refunded.) +-- The combo 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. */ +CREATE TABLE IF NOT EXISTS refunds + (refund_serial_id BIGSERIAL UNIQUE + ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ,merchant_pub BYTEA NOT NULL CHECK(LENGTH(merchant_pub)=32) + ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64) + ,h_contract_terms BYTEA NOT NULL CHECK(LENGTH(h_contract_terms)=64) + ,rtransaction_id INT8 NOT NULL + ,amount_with_fee_val INT8 NOT NULL + ,amount_with_fee_frac INT4 NOT NULL + ,PRIMARY KEY (coin_pub, merchant_pub, h_contract_terms, rtransaction_id) + ); +CREATE INDEX refunds_coin_pub_index + ON refunds + (coin_pub); +-- This table contains the data for +-- wire transfers the exchange has executed. +CREATE TABLE IF NOT EXISTS wire_out + (wireout_uuid BIGSERIAL PRIMARY KEY + ,execution_date INT8 NOT NULL + ,wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32) + ,wire_target TEXT NOT NULL + ,exchange_account_section TEXT NOT NULL + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ); +-- Table for the tracking API, mapping from wire transfer identifier +-- to transactions and back +CREATE TABLE IF NOT EXISTS aggregation_tracking + (aggregation_serial_id BIGSERIAL UNIQUE + ,deposit_serial_id INT8 PRIMARY KEY REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE + ,wtid_raw BYTEA CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE + ); +-- Index for lookup_transactions statement on wtid +CREATE INDEX aggregation_tracking_wtid_index + ON aggregation_tracking + (wtid_raw); +-- Table for the wire fees. +CREATE TABLE IF NOT EXISTS wire_fee + (wire_method VARCHAR NOT NULL + ,start_date INT8 NOT NULL + ,end_date INT8 NOT NULL + ,wire_fee_val INT8 NOT NULL + ,wire_fee_frac INT4 NOT NULL + ,closing_fee_val INT8 NOT NULL + ,closing_fee_frac INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,PRIMARY KEY (wire_method, start_date) + ); +CREATE INDEX wire_fee_gc_index + ON wire_fee + (end_date); +-- Table for /payback information +-- Do not cascade on the coin_pub, as we may keep the coin alive! */ +CREATE TABLE IF NOT EXISTS payback + (payback_uuid BIGSERIAL UNIQUE + ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) + ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64) + ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32) + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,timestamp INT8 NOT NULL + ,h_blind_ev BYTEA NOT NULL REFERENCES reserves_out (h_blind_ev) ON DELETE CASCADE + ); +CREATE INDEX payback_by_coin_index + ON payback + (coin_pub); +CREATE INDEX payback_by_h_blind_ev + ON payback + (h_blind_ev); +CREATE INDEX payback_for_by_reserve + ON payback + (coin_pub + ,h_blind_ev + ); +-- Table for /payback-refresh information +-- Do not cascade on the coin_pub, as we may keep the coin alive! */ +CREATE TABLE IF NOT EXISTS payback_refresh + (payback_refresh_uuid BIGSERIAL UNIQUE + ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) + ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64) + ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32) + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,timestamp INT8 NOT NULL + ,h_blind_ev BYTEA NOT NULL REFERENCES refresh_revealed_coins (h_coin_ev) ON DELETE CASCADE + ); +CREATE INDEX payback_refresh_by_coin_index + ON payback_refresh + (coin_pub); +CREATE INDEX payback_refresh_by_h_blind_ev + ON payback_refresh + (h_blind_ev); +CREATE INDEX payback_refresh_for_by_reserve + ON payback_refresh + (coin_pub + ,h_blind_ev + ); +-- This table contains the pre-commit data for +-- wire transfers the exchange is about to execute. +CREATE TABLE IF NOT EXISTS prewire + (prewire_uuid BIGSERIAL PRIMARY KEY + ,type TEXT NOT NULL + ,finished BOOLEAN NOT NULL DEFAULT false + ,buf BYTEA NOT NULL + ); +-- Index for wire_prepare_data_get and gc_prewire statement +CREATE INDEX prepare_iteration_index + ON prewire + (finished); + +-- Complete transaction +COMMIT; diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index c0d6bc669..d16cc8b31 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -12,6 +12,12 @@ pkgcfg_DATA = \ exchangedb.conf \ exchangedb-postgres.conf +sqldir = $(prefix)/share/taler/sql/exchange/ + +sql_DATA = \ + 0000.sql \ + 0001.sql + EXTRA_DIST = \ exchangedb.conf \ exchangedb-postgres.conf \ diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 59f0efe49..b9c9d6742 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2014--2019 GNUnet e.V. + Copyright (C) 2014--2020 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 @@ -238,7 +238,6 @@ postgres_create_tables (void *cls) ",expiration_date INT8 NOT NULL" ",gc_date INT8 NOT NULL" ");"), - /* index on reserves table (TODO: useless due to primary key!?) */ GNUNET_PQ_make_try_execute ("CREATE INDEX reserves_reserve_pub_index ON " "reserves (reserve_pub);"), /* index for get_expired_reserves */ @@ -264,10 +263,6 @@ postgres_create_tables (void *cls) /* Create indices on reserves_in */ GNUNET_PQ_make_try_execute ("CREATE INDEX reserves_in_execution_index" " ON reserves_in (exchange_account_section,execution_date);"), - /* TODO: verify this actually helps, given the PRIMARY_KEY already includes - reserve_pub as the first dimension! */ - GNUNET_PQ_make_try_execute ("CREATE INDEX reserves_in_reserve_pub" - " ON reserves_in (reserve_pub);"), GNUNET_PQ_make_try_execute ( "CREATE INDEX reserves_in_exchange_account_serial" " ON reserves_in (exchange_account_section,reserve_in_serial_id DESC);"), @@ -450,9 +445,6 @@ postgres_create_tables (void *cls) ",master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)" ",PRIMARY KEY (wire_method, start_date)" /* this combo must be unique */ ");"), - /* Index for lookup_transactions statement on wtid */ - GNUNET_PQ_make_try_execute ("CREATE INDEX aggregation_tracking_wtid_index " - "ON aggregation_tracking(wtid_raw);"), /* Index for gc_wire_fee */ GNUNET_PQ_make_try_execute ("CREATE INDEX wire_fee_gc_index " "ON wire_fee(end_date);"), @@ -471,10 +463,8 @@ postgres_create_tables (void *cls) "ON payback(coin_pub);"), GNUNET_PQ_make_try_execute ("CREATE INDEX payback_by_h_blind_ev " "ON payback(h_blind_ev);"), - GNUNET_PQ_make_try_execute ("CREATE INDEX payback_by_reserve_index " - "ON payback(reserve_pub);"), GNUNET_PQ_make_try_execute ("CREATE INDEX payback_for_by_reserve " - "ON payback(coin_pub,denom_pub_hash,h_blind_ev);"), + "ON payback(coin_pub,h_blind_ev);"), /* Table for /payback-refresh information */ GNUNET_PQ_make_execute ("CREATE TABLE IF NOT EXISTS payback_refresh " @@ -491,10 +481,8 @@ postgres_create_tables (void *cls) "ON payback_refresh(coin_pub);"), GNUNET_PQ_make_try_execute ("CREATE INDEX payback_refresh_by_h_blind_ev " "ON payback_refresh(h_blind_ev);"), - GNUNET_PQ_make_try_execute ("CREATE INDEX payback_refresh_by_reserve_index " - "ON payback_refresh(reserve_pub);"), GNUNET_PQ_make_try_execute ("CREATE INDEX payback_refresh_for_by_reserve " - "ON payback_refresh(coin_pub,denom_pub_hash,h_blind_ev);"), + "ON payback_refresh(coin_pub,h_blind_ev);"), /* This table contains the pre-commit data for wire transfers the exchange is about to execute. */ |