commit ce47396f6e8a15f58f6577d5ba735b5a520de463
parent 7959642b97ef6cdb7f08ce4d768b3815051bf008
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Mon, 12 May 2025 18:15:00 +0200
v0 v1 migration logic
Diffstat:
5 files changed, 5102 insertions(+), 144 deletions(-)
diff --git a/migrate-db-v0-v1.sh b/migrate-db-v0-v1.sh
@@ -1,93 +0,0 @@
-#!/bin/sh
-# This file is in the public domain.
-# Call with exchange configuration file as the exchange user (with DB access).
-set -eu
-CONF=${1:-/etc/taler-exchange/taler-exchange.conf}
-DB=$(taler-exchange-config -c "$CONF" -s exchangedb-postgres -o config)
-DBNAME=$(echo "$DB" | awk -F / '{print $4}')
-DIR=$(mktemp -d)
-echo "Dumping database $DB to $DIR"
-pg_dump -d "$DB" -f "$DIR/full.sql"
-for t in $(echo "
-exchange.wire_accounts
-exchange.wire_targets
-exchange.account_merges
-exchange.known_coins
-exchange.batch_deposits
-exchange.coin_deposits
-exchange.aggregation_tracking
-exchange.aggregation_transient
-exchange.close_requests
-exchange.coin_history
-exchange.contracts
-exchange.cs_nonce_locks
-exchange.denominations
-exchange.exchange_sign_keys
-exchange.global_fee
-exchange.history_requests
-exchange.prewire
-exchange.purse_actions
-exchange.purse_requests
-exchange.purse_deletion
-exchange.purse_deposits
-exchange.purse_merges
-exchange.purse_decision
-exchange.refunds
-exchange.reserves
-exchange.reserves_close
-exchange.reserves_in
-exchange.reserves_open_deposits
-exchange.reserves_open_requests
-exchange.reserves_out
-exchange.revolving_work_shards
-exchange.wire_fee
-exchange.wire_out
-exchange.work_shards")
-do
- echo "Backing up Table $t"
- pg_dump -a -d "$DB" -t "$t" -f "$DIR/$t.sql"
-done
-echo "Migrating to new schema"
-taler-exchange-dbinit -c "$CONF" -r
-
-for t in $(echo "
-exchange.account_merges
-exchange.aggregation_tracking
-exchange.aggregation_transient
-exchange.batch_deposits
-exchange.close_requests
-exchange.coin_deposits
-exchange.coin_history
-exchange.contracts
-exchange.cs_nonce_locks
-exchange.denominations
-exchange.exchange_sign_keys
-exchange.global_fee
-exchange.history_requests
-exchange.known_coins
-exchange.prewire
-exchange.purse_actions
-exchange.purse_decision
-exchange.purse_deletion
-exchange.purse_deposits
-exchange.purse_merges
-exchange.purse_requests
-exchange.refunds
-exchange.reserves
-exchange.reserves_close
-exchange.reserves_in
-exchange.reserves_open_deposits
-exchange.reserves_open_requests
-exchange.reserves_out
-exchange.revolving_work_shards
-exchange.wire_accounts
-exchange.wire_fee
-exchange.wire_out
-exchange.wire_targets
-exchange.work_shards")
-do
- echo "Restoring Table $t"
- psql "$DB" < "$DIR/$t.sql"
-done
-echo "Done"
-exit 0
diff --git a/migration-v0-v1/migrate-db-v0-v1.sh b/migration-v0-v1/migrate-db-v0-v1.sh
@@ -0,0 +1,91 @@
+#!/bin/sh
+# This file is in the public domain.
+# Call with exchange configuration file as the exchange user (with DB access).
+set -eu
+CONF=${1:-/etc/taler-exchange/taler-exchange.conf}
+DB=$(taler-exchange-config -c "$CONF" -s exchangedb-postgres -o config)
+DBNAME=$(echo "$DB" | awk -F / '{print $4}')
+DIR=$(mktemp -d)
+echo "Dumping database $DB to $DIR"
+pg_dump -d "$DB" -f "$DIR/full.sql"
+for t in $(echo "
+exchange.wire_accounts
+exchange.wire_targets
+exchange.account_merges
+exchange.known_coins
+exchange.batch_deposits
+exchange.coin_deposits
+exchange.aggregation_tracking
+exchange.aggregation_transient
+exchange.close_requests
+exchange.coin_history
+exchange.contracts
+exchange.cs_nonce_locks
+exchange.denominations
+exchange.exchange_sign_keys
+exchange.global_fee
+exchange.history_requests
+exchange.prewire
+exchange.purse_actions
+exchange.purse_requests
+exchange.purse_deletion
+exchange.purse_deposits
+exchange.purse_merges
+exchange.purse_decision
+exchange.refunds
+exchange.reserves
+exchange.reserves_close
+exchange.reserves_in
+exchange.reserves_open_deposits
+exchange.reserves_open_requests
+exchange.reserves_out
+exchange.revolving_work_shards
+exchange.wire_fee
+exchange.wire_out
+exchange.work_shards")
+do
+ echo "Backing up Table $t"
+ pg_dump -a -d "$DB" -t "$t" -f "$DIR/$t.sql"
+done
+echo "Migrating to new schema"
+taler-exchange-dbinit -c "$CONF" -r
+
+for t in $(echo "
+exchange.cs_nonce_locks
+exchange.denominations
+exchange.exchange_sign_keys
+exchange.global_fee
+exchange.known_coins
+exchange.prewire
+exchange.wire_accounts
+exchange.wire_targets
+exchange.reserves
+exchange.batch_deposits
+exchange.aggregation_tracking
+exchange.aggregation_transient
+exchange.close_requests
+exchange.coin_deposits
+exchange.contracts
+exchange.history_requests
+exchange.purse_requests
+exchange.purse_decision
+exchange.purse_deletion
+exchange.purse_deposits
+exchange.purse_merges
+exchange.refunds
+exchange.account_merges
+exchange.reserves_close
+exchange.reserves_in
+exchange.reserves_open_deposits
+exchange.reserves_open_requests
+exchange.reserves_out
+exchange.revolving_work_shards
+exchange.wire_fee
+exchange.wire_out
+exchange.work_shards")
+do
+ echo "Restoring Table $t"
+ psql "$DB" < "$DIR/$t.sql"
+done
+echo "Done"
+exit 0
diff --git a/migration-v0-v1/procedures.sql b/migration-v0-v1/procedures.sql
@@ -0,0 +1,4961 @@
+BEGIN;
+SET search_path TO exchange;
+CREATE OR REPLACE FUNCTION create_partitioned_table(
+ IN table_definition TEXT
+ ,IN table_name TEXT
+ ,IN main_table_partition_str TEXT
+ ,IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF (partition_suffix IS NULL)
+ THEN
+ main_table_partition_str = '';
+ ELSE
+ IF (partition_suffix::int > 0)
+ THEN
+ table_name=table_name || '_' || partition_suffix;
+ END IF;
+ END IF;
+ EXECUTE FORMAT(
+ table_definition,
+ table_name,
+ main_table_partition_str
+ );
+END $$;
+COMMENT ON FUNCTION create_partitioned_table
+ IS 'Generic function to create a table that is partitioned or sharded.';
+CREATE OR REPLACE FUNCTION comment_partitioned_table(
+ IN table_comment TEXT
+ ,IN table_name TEXT
+ ,IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF ( (partition_suffix IS NOT NULL) AND
+ (partition_suffix::int > 0) )
+ THEN
+ table_name=table_name || '_' || partition_suffix;
+ END IF;
+ EXECUTE FORMAT(
+ 'COMMENT ON TABLE %s IS %s'
+ ,table_name
+ ,quote_literal(table_comment)
+ );
+END $$;
+COMMENT ON FUNCTION comment_partitioned_table
+ IS 'Generic function to create a comment on table that is partitioned.';
+CREATE OR REPLACE FUNCTION comment_partitioned_column(
+ IN table_comment TEXT
+ ,IN column_name TEXT
+ ,IN table_name TEXT
+ ,IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ IF ( (partition_suffix IS NOT NULL) AND
+ (partition_suffix::int > 0) )
+ THEN
+ table_name=table_name || '_' || partition_suffix;
+ END IF;
+ EXECUTE FORMAT(
+ 'COMMENT ON COLUMN %s.%s IS %s'
+ ,table_name
+ ,column_name
+ ,quote_literal(table_comment)
+ );
+END $$;
+COMMENT ON FUNCTION comment_partitioned_column
+ IS 'Generic function to create a comment on column of a table that is partitioned.';
+CREATE OR REPLACE FUNCTION exchange_do_create_tables(
+ num_partitions INTEGER
+)
+ RETURNS VOID
+ LANGUAGE plpgsql
+AS $$
+DECLARE
+ tc CURSOR FOR
+ SELECT table_serial_id
+ ,name
+ ,action
+ ,partitioned
+ ,by_range
+ FROM exchange.exchange_tables
+ WHERE NOT finished
+ ORDER BY table_serial_id ASC;
+BEGIN
+ FOR rec IN tc
+ LOOP
+ CASE rec.action
+ WHEN 'create'
+ THEN
+ IF (rec.partitioned AND
+ (num_partitions IS NOT NULL))
+ THEN
+ EXECUTE FORMAT(
+ 'SELECT exchange.create_table_%s (%s)'::text
+ ,rec.name
+ ,quote_literal('0')
+ );
+ IF (rec.by_range OR
+ (num_partitions = 0))
+ THEN
+ IF (rec.by_range)
+ THEN
+ EXECUTE FORMAT(
+ 'CREATE TABLE exchange.%s_default'
+ ' PARTITION OF %s'
+ ' DEFAULT'
+ ,rec.name
+ ,rec.name
+ );
+ ELSE
+ EXECUTE FORMAT(
+ 'CREATE TABLE exchange.%s_default'
+ ' PARTITION OF %s'
+ ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)'
+ ,rec.name
+ ,rec.name
+ );
+ END IF;
+ ELSE
+ FOR i IN 1..num_partitions LOOP
+ EXECUTE FORMAT(
+ 'CREATE TABLE exchange.%I'
+ ' PARTITION OF %I'
+ ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
+ ,rec.name || '_' || i
+ ,rec.name
+ ,num_partitions
+ ,i-1
+ );
+ END LOOP;
+ END IF;
+ ELSE
+ EXECUTE FORMAT(
+ 'SELECT exchange.create_table_%s ()'::text
+ ,rec.name
+ );
+ END IF;
+ EXECUTE FORMAT(
+ 'DROP FUNCTION exchange.create_table_%s'::text
+ ,rec.name
+ );
+ WHEN 'alter'
+ THEN
+ EXECUTE FORMAT(
+ 'SELECT exchange.alter_table_%s ()'::text
+ ,rec.name
+ );
+ EXECUTE FORMAT(
+ 'DROP FUNCTION exchange.alter_table_%s'::text
+ ,rec.name
+ );
+ WHEN 'constrain'
+ THEN
+ ASSERT rec.partitioned, 'constrain action only applies to partitioned tables';
+ IF (num_partitions IS NULL)
+ THEN
+ EXECUTE FORMAT(
+ 'SELECT exchange.constrain_table_%s (NULL)'::text
+ ,rec.name
+ );
+ ELSE
+ IF ( (num_partitions = 0) OR
+ (rec.by_range) )
+ THEN
+ EXECUTE FORMAT(
+ 'SELECT exchange.constrain_table_%s (%s)'::text
+ ,rec.name
+ ,quote_literal('default')
+ );
+ ELSE
+ FOR i IN 1..num_partitions LOOP
+ EXECUTE FORMAT(
+ 'SELECT exchange.constrain_table_%s (%s)'::text
+ ,rec.name
+ ,quote_literal(i)
+ );
+ END LOOP;
+ END IF;
+ END IF;
+ EXECUTE FORMAT(
+ 'DROP FUNCTION exchange.constrain_table_%s'::text
+ ,rec.name
+ );
+ WHEN 'foreign'
+ THEN
+ IF (num_partitions IS NULL)
+ THEN
+ EXECUTE FORMAT(
+ 'SELECT exchange.foreign_table_%s (%s)'::text
+ ,rec.name
+ ,NULL
+ );
+ END IF;
+ EXECUTE FORMAT(
+ 'DROP FUNCTION exchange.foreign_table_%s'::text
+ ,rec.name
+ );
+ WHEN 'master'
+ THEN
+ EXECUTE FORMAT(
+ 'SELECT exchange.master_table_%s ()'::text
+ ,rec.name
+ );
+ EXECUTE FORMAT(
+ 'DROP FUNCTION exchange.master_table_%s'::text
+ ,rec.name
+ );
+ ELSE
+ ASSERT FALSE, 'unsupported action type: ' || rec.action;
+ END CASE;
+ UPDATE exchange.exchange_tables
+ SET finished=TRUE
+ WHERE table_serial_id=rec.table_serial_id;
+ END LOOP;
+END $$;
+COMMENT ON FUNCTION exchange_do_create_tables
+ IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.';
+CREATE OR REPLACE FUNCTION amount_normalize(
+ IN amount taler_amount
+ ,OUT normalized taler_amount
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ normalized.val = amount.val + amount.frac / 100000000;
+ normalized.frac = amount.frac % 100000000;
+END $$;
+COMMENT ON FUNCTION amount_normalize
+ IS 'Returns the normalized amount by adding to the .val the value of (.frac / 100000000) and removing the modulus 100000000 from .frac.';
+CREATE OR REPLACE FUNCTION amount_add(
+ IN a taler_amount
+ ,IN b taler_amount
+ ,OUT sum taler_amount
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ sum = (a.val + b.val, a.frac + b.frac);
+ CALL amount_normalize(sum ,sum);
+ IF (sum.val > (1<<52))
+ THEN
+ RAISE EXCEPTION 'addition overflow';
+ END IF;
+END $$;
+COMMENT ON FUNCTION amount_add
+ IS 'Returns the normalized sum of two amounts. It raises an exception when the resulting .val is larger than 2^52';
+CREATE OR REPLACE FUNCTION amount_left_minus_right(
+ IN l taler_amount
+ ,IN r taler_amount
+ ,OUT diff taler_amount
+ ,OUT ok BOOLEAN
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+IF (l.val > r.val)
+THEN
+ ok = TRUE;
+ IF (l.frac >= r.frac)
+ THEN
+ diff.val = l.val - r.val;
+ diff.frac = l.frac - r.frac;
+ ELSE
+ diff.val = l.val - r.val - 1;
+ diff.frac = l.frac + 100000000 - r.frac;
+ END IF;
+ELSE
+ IF (l.val = r.val) AND (l.frac >= r.frac)
+ THEN
+ diff.val = 0;
+ diff.frac = l.frac - r.frac;
+ ok = TRUE;
+ ELSE
+ diff = (-1, -1);
+ ok = FALSE;
+ END IF;
+END IF;
+RETURN;
+END $$;
+COMMENT ON FUNCTION amount_left_minus_right
+ IS 'Subtracts the right amount from the left and returns the difference and TRUE, if the left amount is larger than the right, or an invalid amount and FALSE otherwise.';
+DROP FUNCTION IF EXISTS exchange_do_withdraw;
+CREATE FUNCTION exchange_do_withdraw(
+ IN in_amount_with_fee taler_amount,
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_sig BYTEA,
+ IN in_now INT8,
+ IN in_min_reserve_gc INT8,
+ IN in_planchets_h BYTEA,
+ IN in_maximum_age_committed INT2,
+ IN in_noreveal_index INT2,
+ IN in_selected_h BYTEA,
+ IN in_denom_serials INT8[],
+ IN in_denom_sigs BYTEA[],
+ IN in_blinding_seed BYTEA,
+ IN in_cs_r_values BYTEA[],
+ IN in_cs_r_choices INT8,
+ OUT out_reserve_found BOOLEAN,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_reserve_balance taler_amount,
+ OUT out_age_ok BOOLEAN,
+ OUT out_required_age INT2,
+ OUT out_reserve_birthday INT4,
+ OUT out_idempotent BOOLEAN,
+ OUT out_noreveal_index INT2,
+ OUT out_nonce_reuse BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_reserve RECORD;
+ my_difference RECORD;
+ my_balance taler_amount;
+ my_not_before DATE;
+ my_earliest_date DATE;
+BEGIN
+SELECT current_balance
+ ,birthday
+ ,gc_date
+ INTO my_reserve
+ FROM reserves
+ WHERE reserve_pub=in_reserve_pub;
+out_reserve_found = FOUND;
+IF NOT out_reserve_found
+THEN
+ out_age_ok = FALSE;
+ out_required_age = -1;
+ out_idempotent = FALSE;
+ out_noreveal_index = -1;
+ out_reserve_balance.val = 0;
+ out_reserve_balance.frac = 0;
+ out_balance_ok = FALSE;
+ out_nonce_reuse = FALSE;
+ RETURN;
+END IF;
+out_reserve_balance = my_reserve.current_balance;
+out_reserve_birthday = my_reserve.birthday;
+SELECT noreveal_index
+ INTO out_noreveal_index
+ FROM withdraw
+ WHERE reserve_pub = in_reserve_pub
+ AND planchets_h = in_planchets_h;
+out_idempotent = FOUND;
+IF out_idempotent
+THEN
+ out_balance_ok = TRUE;
+ out_age_ok = TRUE;
+ out_required_age = -1;
+ out_nonce_reuse = FALSE;
+ RETURN;
+END IF;
+out_noreveal_index = -1;
+IF (my_reserve.birthday <> 0)
+THEN
+ my_not_before=date '1970-01-01' + my_reserve.birthday;
+ my_earliest_date = current_date - make_interval(in_maximum_age_committed);
+ IF ( (in_maximum_age_committed IS NULL) OR
+ (my_earliest_date < my_not_before) )
+ THEN
+ out_required_age = extract(year FROM age(current_date, my_not_before));
+ out_age_ok = FALSE;
+ out_balance_ok = TRUE;
+ out_nonce_reuse = FALSE;
+ RETURN;
+ END IF;
+END IF;
+out_age_ok = TRUE;
+out_required_age = 0;
+SELECT *
+ INTO my_difference
+ FROM amount_left_minus_right(out_reserve_balance
+ ,in_amount_with_fee);
+out_balance_ok = my_difference.ok;
+IF NOT out_balance_ok
+THEN
+ out_nonce_reuse = FALSE;
+ RETURN;
+END IF;
+my_balance = my_difference.diff;
+in_min_reserve_gc=GREATEST(in_min_reserve_gc,my_reserve.gc_date);
+UPDATE reserves SET
+ gc_date=in_min_reserve_gc
+ ,current_balance=my_balance
+WHERE
+ reserve_pub=in_reserve_pub;
+IF in_blinding_seed IS NOT NULL
+THEN
+ INSERT INTO unique_withdraw_blinding_seed
+ (blinding_seed)
+ VALUES
+ (in_blinding_seed)
+ ON CONFLICT DO NOTHING;
+ IF NOT FOUND
+ THEN
+ out_nonce_reuse = TRUE;
+ RETURN;
+ END IF;
+END IF;
+out_nonce_reuse = FALSE;
+INSERT INTO withdraw
+ (planchets_h
+ ,execution_date
+ ,max_age
+ ,amount_with_fee
+ ,reserve_pub
+ ,reserve_sig
+ ,noreveal_index
+ ,denom_serials
+ ,selected_h
+ ,blinding_seed
+ ,cs_r_values
+ ,cs_r_choices
+ ,denom_sigs)
+VALUES
+ (in_planchets_h
+ ,in_now
+ ,in_maximum_age_committed
+ ,in_amount_with_fee
+ ,in_reserve_pub
+ ,in_reserve_sig
+ ,in_noreveal_index
+ ,in_denom_serials
+ ,in_selected_h
+ ,in_blinding_seed
+ ,in_cs_r_values
+ ,in_cs_r_choices
+ ,in_denom_sigs)
+ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ RAISE EXCEPTION 'Conflict on insert into withdraw despite idempotency check for reserve_pub(%) and planchets_h(%)!',
+ in_reserve_pub,
+ in_planchets_h;
+END IF;
+END $$;
+COMMENT ON FUNCTION exchange_do_withdraw(
+ taler_amount,
+ BYTEA,
+ BYTEA,
+ INT8,
+ INT8,
+ BYTEA,
+ INT2,
+ INT2,
+ BYTEA,
+ INT8[],
+ BYTEA[],
+ BYTEA,
+ BYTEA[],
+ INT8)
+ IS 'Checks whether the reserve has sufficient balance for an withdraw operation (or the request is repeated and was previously approved) and that age requirements are met. If so updates the database with the result. Includes storing the hashes of all blinded planchets, (separately) the hashes of the chosen planchets and denomination signatures, or signaling idempotency (and previous noreveal_index) or nonce reuse';
+DROP FUNCTION IF EXISTS exchange_do_refresh;
+CREATE FUNCTION exchange_do_refresh(
+ IN in_rc BYTEA,
+ IN in_now INT8,
+ IN in_refresh_seed BYTEA,
+ IN in_planchets_h BYTEA,
+ IN in_amount_with_fee taler_amount,
+ IN in_blinding_seed BYTEA,
+ IN in_cs_r_values BYTEA[],
+ IN in_cs_r_choices INT8,
+ IN in_selected_h BYTEA,
+ IN in_denom_sigs BYTEA[],
+ IN in_denom_serials INT8[],
+ IN in_old_coin_pub BYTEA,
+ IN in_old_coin_sig BYTEA,
+ IN in_noreveal_index INT4,
+ IN in_zombie_required BOOLEAN,
+ OUT out_coin_found BOOLEAN,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_zombie_bad BOOLEAN,
+ OUT out_nonce_reuse BOOLEAN,
+ OUT out_idempotent BOOLEAN,
+ OUT out_noreveal_index INT4,
+ OUT out_coin_balance taler_amount)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ known_coin RECORD;
+ difference RECORD;
+BEGIN
+SELECT known_coin_id
+ ,remaining
+ INTO known_coin
+ FROM known_coins
+ WHERE coin_pub = in_old_coin_pub;
+IF NOT FOUND
+THEN
+ out_coin_found = FALSE;
+ out_balance_ok = TRUE;
+ out_zombie_bad = FALSE;
+ out_nonce_reuse = FALSE;
+ out_idempotent = FALSE;
+ out_noreveal_index = -1 ;
+ out_coin_balance.val = 0;
+ out_coin_balance.frac = 0;
+ RETURN;
+END IF;
+out_coin_found = TRUE;
+out_coin_balance = known_coin.remaining;
+SELECT TRUE, noreveal_index
+INTO out_idempotent, out_noreveal_index
+FROM exchange.refresh
+WHERE rc=in_rc;
+IF out_idempotent
+THEN
+ out_balance_ok = TRUE;
+ out_zombie_bad = FALSE;
+ out_nonce_reuse = FALSE;
+RETURN;
+END IF;
+out_idempotent = FALSE;
+out_noreveal_index = in_noreveal_index;
+IF in_blinding_seed IS NOT NULL
+THEN
+ INSERT INTO unique_refresh_blinding_seed
+ (blinding_seed)
+ VALUES
+ (in_blinding_seed)
+ ON CONFLICT DO NOTHING;
+ IF NOT FOUND
+ THEN
+ out_nonce_reuse = TRUE;
+ out_balance_ok = TRUE;
+ out_zombie_bad = FALSE;
+ RETURN;
+ END IF;
+END IF;
+out_nonce_reuse = FALSE;
+INSERT INTO exchange.refresh
+ (rc
+ ,execution_date
+ ,old_coin_pub
+ ,old_coin_sig
+ ,planchets_h
+ ,amount_with_fee
+ ,noreveal_index
+ ,refresh_seed
+ ,blinding_seed
+ ,cs_r_values
+ ,cs_r_choices
+ ,selected_h
+ ,denom_sigs
+ ,denom_serials
+ )
+ VALUES
+ (in_rc
+ ,in_now
+ ,in_old_coin_pub
+ ,in_old_coin_sig
+ ,in_planchets_h
+ ,in_amount_with_fee
+ ,in_noreveal_index
+ ,in_refresh_seed
+ ,in_blinding_seed
+ ,in_cs_r_values
+ ,in_cs_r_choices
+ ,in_selected_h
+ ,in_denom_sigs
+ ,in_denom_serials
+ )
+ ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ RAISE EXCEPTION 'Conflict in refresh despite idempotency check for rc(%)!', rc;
+ RETURN;
+END IF;
+IF in_zombie_required
+THEN
+ PERFORM
+ FROM recoup_refresh
+ WHERE refresh_id IN
+ (SELECT refresh_id
+ FROM refresh
+ WHERE old_coin_pub=in_old_coin_pub);
+ IF NOT FOUND
+ THEN
+ out_zombie_bad=TRUE;
+ out_balance_ok=FALSE;
+ RETURN;
+ END IF;
+END IF;
+out_zombie_bad=FALSE;
+SELECT *
+INTO difference
+FROM amount_left_minus_right(out_coin_balance
+ ,in_amount_with_fee);
+out_balance_ok = difference.ok;
+IF NOT out_balance_ok
+THEN
+ RETURN;
+END IF;
+out_coin_balance = difference.diff;
+UPDATE known_coins
+ SET
+ remaining = out_coin_balance
+ WHERE
+ known_coin_id = known_coin.known_coin_id;
+END $$;
+DROP FUNCTION IF EXISTS exchange_do_deposit;
+CREATE FUNCTION exchange_do_deposit(
+ IN in_shard INT8,
+ IN in_merchant_pub BYTEA,
+ IN in_merchant_sig BYTEA,
+ IN in_wallet_timestamp INT8,
+ IN in_exchange_timestamp INT8,
+ IN in_refund_deadline INT8,
+ IN in_wire_deadline INT8,
+ IN in_h_contract_terms BYTEA,
+ IN in_wallet_data_hash BYTEA,
+ IN in_wire_salt BYTEA,
+ IN in_wire_target_h_payto BYTEA,
+ IN in_h_normalized_payto BYTEA,
+ IN in_policy_details_serial_id INT8,
+ IN in_policy_blocked BOOLEAN,
+ IN in_receiver_wire_account TEXT,
+ IN ina_coin_pub BYTEA[],
+ IN ina_coin_sig BYTEA[],
+ IN ina_amount_with_fee taler_amount[],
+ IN in_total_amount taler_amount,
+ OUT out_exchange_timestamp INT8,
+ OUT out_insufficient_balance_coin_index INT4,
+ OUT out_conflict BOOL
+ )
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ wtsi INT8;
+ bdsi INT8;
+ i INT4;
+ ini_amount_with_fee taler_amount;
+ ini_coin_pub BYTEA;
+ ini_coin_sig BYTEA;
+BEGIN
+INSERT INTO wire_targets
+ (wire_target_h_payto
+ ,h_normalized_payto
+ ,payto_uri)
+ VALUES
+ (in_wire_target_h_payto
+ ,in_h_normalized_payto
+ ,in_receiver_wire_account)
+ ON CONFLICT DO NOTHING
+ RETURNING
+ wire_target_serial_id
+ INTO
+ wtsi;
+IF NOT FOUND
+THEN
+ SELECT
+ wire_target_serial_id
+ INTO
+ wtsi
+ FROM wire_targets
+ WHERE
+ wire_target_h_payto=in_wire_target_h_payto;
+END IF;
+INSERT INTO batch_deposits
+ (shard
+ ,merchant_pub
+ ,merchant_sig
+ ,wallet_timestamp
+ ,exchange_timestamp
+ ,refund_deadline
+ ,wire_deadline
+ ,h_contract_terms
+ ,wallet_data_hash
+ ,wire_salt
+ ,wire_target_h_payto
+ ,policy_details_serial_id
+ ,policy_blocked
+ ,total_amount
+ )
+ VALUES
+ (in_shard
+ ,in_merchant_pub
+ ,in_merchant_sig
+ ,in_wallet_timestamp
+ ,in_exchange_timestamp
+ ,in_refund_deadline
+ ,in_wire_deadline
+ ,in_h_contract_terms
+ ,in_wallet_data_hash
+ ,in_wire_salt
+ ,in_wire_target_h_payto
+ ,in_policy_details_serial_id
+ ,in_policy_blocked
+ ,in_total_amount)
+ ON CONFLICT DO NOTHING
+ RETURNING
+ batch_deposit_serial_id
+ INTO
+ bdsi;
+IF NOT FOUND
+THEN
+ SELECT
+ exchange_timestamp
+ ,batch_deposit_serial_id
+ INTO
+ out_exchange_timestamp
+ ,bdsi
+ FROM batch_deposits
+ WHERE shard=in_shard
+ AND merchant_pub=in_merchant_pub
+ AND h_contract_terms=in_h_contract_terms
+ AND wire_target_h_payto=in_wire_target_h_payto
+ AND ( (wallet_data_hash=in_wallet_data_hash) OR
+ (wallet_data_hash IS NULL AND in_wallet_data_hash IS NULL) )
+ AND wire_salt=in_wire_salt
+ AND wallet_timestamp=in_wallet_timestamp
+ AND refund_deadline=in_refund_deadline
+ AND wire_deadline=in_wire_deadline
+ AND ( (policy_details_serial_id=in_policy_details_serial_id) OR
+ (policy_details_serial_id IS NULL AND in_policy_details_serial_id IS NULL) );
+ IF NOT FOUND
+ THEN
+ out_conflict=TRUE;
+ RETURN;
+ END IF;
+END IF;
+out_conflict=FALSE;
+FOR i IN 1..array_length(ina_coin_pub,1)
+LOOP
+ ini_coin_pub = ina_coin_pub[i];
+ ini_coin_sig = ina_coin_sig[i];
+ ini_amount_with_fee = ina_amount_with_fee[i];
+ INSERT INTO coin_deposits
+ (batch_deposit_serial_id
+ ,coin_pub
+ ,coin_sig
+ ,amount_with_fee
+ )
+ VALUES
+ (bdsi
+ ,ini_coin_pub
+ ,ini_coin_sig
+ ,ini_amount_with_fee
+ )
+ ON CONFLICT DO NOTHING;
+ IF FOUND
+ THEN
+ UPDATE known_coins kc
+ SET
+ remaining.frac=(kc.remaining).frac-ini_amount_with_fee.frac
+ + CASE
+ WHEN (kc.remaining).frac < ini_amount_with_fee.frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val-ini_amount_with_fee.val
+ - CASE
+ WHEN (kc.remaining).frac < ini_amount_with_fee.frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=ini_coin_pub
+ AND ( ((kc.remaining).val > ini_amount_with_fee.val) OR
+ ( ((kc.remaining).frac >= ini_amount_with_fee.frac) AND
+ ((kc.remaining).val >= ini_amount_with_fee.val) ) );
+ IF NOT FOUND
+ THEN
+ out_insufficient_balance_coin_index=i-1;
+ RETURN;
+ END IF;
+ END IF;
+END LOOP;
+END $$;
+DROP FUNCTION IF EXISTS exchange_do_check_deposit_idempotent;
+CREATE FUNCTION exchange_do_check_deposit_idempotent(
+ IN in_shard INT8,
+ IN in_merchant_pub BYTEA,
+ IN in_wallet_timestamp INT8,
+ IN in_exchange_timestamp INT8,
+ IN in_refund_deadline INT8,
+ IN in_wire_deadline INT8,
+ IN in_h_contract_terms BYTEA,
+ IN in_wallet_data_hash BYTEA,
+ IN in_wire_salt BYTEA,
+ IN in_wire_target_h_payto BYTEA,
+ IN in_policy_details_serial_id INT8,
+ IN in_policy_blocked BOOLEAN,
+ IN ina_coin_pub BYTEA[],
+ IN ina_coin_sig BYTEA[],
+ IN ina_amount_with_fee taler_amount[],
+ OUT out_exchange_timestamp INT8,
+ OUT out_is_idempotent BOOL
+ )
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ wtsi INT8;
+ bdsi INT8;
+ i INT4;
+ ini_amount_with_fee taler_amount;
+ ini_coin_pub BYTEA;
+ ini_coin_sig BYTEA;
+BEGIN
+out_exchange_timestamp = in_exchange_timestamp;
+SELECT wire_target_serial_id
+ INTO wtsi
+ FROM wire_targets
+ WHERE wire_target_h_payto=in_wire_target_h_payto;
+IF NOT FOUND
+THEN
+ out_is_idempotent = FALSE;
+ RETURN;
+END IF;
+SELECT
+ exchange_timestamp
+ ,batch_deposit_serial_id
+ INTO
+ out_exchange_timestamp
+ ,bdsi
+ FROM batch_deposits
+ WHERE shard=in_shard
+ AND merchant_pub=in_merchant_pub
+ AND h_contract_terms=in_h_contract_terms
+ AND wire_target_h_payto=in_wire_target_h_payto
+ AND ( (wallet_data_hash=in_wallet_data_hash) OR
+ (wallet_data_hash IS NULL AND in_wallet_data_hash IS NULL) )
+ AND wire_salt=in_wire_salt
+ AND wallet_timestamp=in_wallet_timestamp
+ AND refund_deadline=in_refund_deadline
+ AND wire_deadline=in_wire_deadline
+ AND ( (policy_details_serial_id=in_policy_details_serial_id) OR
+ (policy_details_serial_id IS NULL AND in_policy_details_serial_id IS NULL) );
+IF NOT FOUND
+THEN
+ out_is_idempotent=FALSE;
+ RETURN;
+END IF;
+FOR i IN 1..array_length(ina_coin_pub,1)
+LOOP
+ ini_coin_pub = ina_coin_pub[i];
+ ini_coin_sig = ina_coin_sig[i];
+ ini_amount_with_fee = ina_amount_with_fee[i];
+ PERFORM FROM coin_deposits
+ WHERE batch_deposit_serial_id=bdsi
+ AND coin_pub=ini_coin_pub
+ AND coin_sig=ini_coin_sig
+ AND amount_with_fee=ini_amount_with_fee;
+ IF NOT FOUND
+ THEN
+ out_is_idempotent=FALSE;
+ RETURN;
+ END IF;
+END LOOP;
+out_is_idempotent=TRUE;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_melt(
+ IN in_cs_rms BYTEA,
+ IN in_amount_with_fee taler_amount,
+ IN in_rc BYTEA,
+ IN in_old_coin_pub BYTEA,
+ IN in_old_coin_sig BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_noreveal_index INT4,
+ IN in_zombie_required BOOLEAN,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_zombie_bad BOOLEAN,
+ OUT out_noreveal_index INT4)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ denom_max INT8;
+BEGIN
+INSERT INTO exchange.refresh_commitments
+ (rc
+ ,old_coin_pub
+ ,old_coin_sig
+ ,amount_with_fee
+ ,noreveal_index
+ )
+ VALUES
+ (in_rc
+ ,in_old_coin_pub
+ ,in_old_coin_sig
+ ,in_amount_with_fee
+ ,in_noreveal_index)
+ ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ out_noreveal_index=-1;
+ SELECT
+ noreveal_index
+ INTO
+ out_noreveal_index
+ FROM exchange.refresh_commitments
+ WHERE rc=in_rc;
+ out_balance_ok=FOUND;
+ out_zombie_bad=FALSE;
+ RETURN;
+END IF;
+IF in_zombie_required
+THEN
+ PERFORM
+ FROM recoup_refresh
+ WHERE rrc_serial IN
+ (SELECT rrc_serial
+ FROM refresh_revealed_coins
+ WHERE melt_serial_id IN
+ (SELECT melt_serial_id
+ FROM refresh_commitments
+ WHERE old_coin_pub=in_old_coin_pub));
+ IF NOT FOUND
+ THEN
+ out_zombie_bad=TRUE;
+ out_balance_ok=FALSE;
+ RETURN;
+ END IF;
+END IF;
+out_zombie_bad=FALSE;
+UPDATE known_coins kc
+ SET
+ remaining.frac=(kc.remaining).frac-in_amount_with_fee.frac
+ + CASE
+ WHEN (kc.remaining).frac < in_amount_with_fee.frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val-in_amount_with_fee.val
+ - CASE
+ WHEN (kc.remaining).frac < in_amount_with_fee.frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_old_coin_pub
+ AND ( ((kc.remaining).val > in_amount_with_fee.val) OR
+ ( ((kc.remaining).frac >= in_amount_with_fee.frac) AND
+ ((kc.remaining).val >= in_amount_with_fee.val) ) );
+IF NOT FOUND
+THEN
+ out_noreveal_index=-1;
+ out_balance_ok=FALSE;
+ RETURN;
+END IF;
+IF in_cs_rms IS NOT NULL
+THEN
+ SELECT
+ denominations_serial
+ INTO
+ denom_max
+ FROM exchange.denominations
+ ORDER BY denominations_serial DESC
+ LIMIT 1;
+ INSERT INTO exchange.cs_nonce_locks
+ (nonce
+ ,max_denomination_serial
+ ,op_hash)
+ VALUES
+ (in_cs_rms
+ ,denom_max
+ ,in_rc)
+ ON CONFLICT DO NOTHING;
+ IF NOT FOUND
+ THEN
+ SELECT 1
+ FROM exchange.cs_nonce_locks
+ WHERE nonce=in_cs_rms
+ AND op_hash=in_rc;
+ IF NOT FOUND
+ THEN
+ out_balance_ok=FALSE;
+ out_zombie_bad=FALSE;
+ out_noreveal_index=42;
+ ASSERT false, 'nonce reuse attempted by client';
+ END IF;
+ END IF;
+END IF;
+out_balance_ok=TRUE;
+out_noreveal_index=in_noreveal_index;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_select_deposits_missing_wire(
+ IN in_min_serial_id INT8)
+RETURNS SETOF exchange_do_select_deposits_missing_wire_return_type
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ missing CURSOR
+ FOR
+ SELECT
+ batch_deposit_serial_id
+ ,wire_target_h_payto
+ ,wire_deadline
+ FROM batch_deposits
+ WHERE batch_deposit_serial_id > in_min_serial_id
+ ORDER BY batch_deposit_serial_id ASC;
+DECLARE
+ my_total_val INT8;
+DECLARE
+ my_total_frac INT8;
+DECLARE
+ my_total taler_amount;
+DECLARE
+ my_batch_record RECORD;
+DECLARE
+ i RECORD;
+BEGIN
+OPEN missing;
+LOOP
+ FETCH NEXT FROM missing INTO i;
+ EXIT WHEN NOT FOUND;
+ SELECT
+ SUM((cdep.amount_with_fee).val) AS total_val
+ ,SUM((cdep.amount_with_fee).frac::INT8) AS total_frac
+ INTO
+ my_batch_record
+ FROM coin_deposits cdep
+ WHERE cdep.batch_deposit_serial_id = i.batch_deposit_serial_id;
+ my_total_val=my_batch_record.total_val;
+ my_total_frac=my_batch_record.total_frac;
+ my_total.val = my_total_val + my_total_frac / 100000000;
+ my_total.frac = my_total_frac % 100000000;
+ RETURN NEXT (
+ i.batch_deposit_serial_id
+ ,my_total
+ ,i.wire_target_h_payto
+ ,i.wire_deadline);
+END LOOP;
+CLOSE missing;
+RETURN;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_select_justification_missing_wire(
+ IN in_wire_target_h_payto BYTEA,
+ IN in_current_time INT8,
+ OUT out_payto_uri TEXT,
+ OUT out_kyc_pending TEXT,
+ OUT out_aml_status INT4,
+ OUT out_aml_limit taler_amount)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_required_checks TEXT[];
+DECLARE
+ my_aml_data RECORD;
+DECLARE
+ satisfied CURSOR FOR
+ SELECT satisfied_checks
+ FROM kyc_attributes
+ WHERE h_payto=in_wire_target_h_payto
+ AND expiration_time < in_current_time;
+DECLARE
+ i RECORD;
+BEGIN
+ out_payto_uri = NULL;
+ SELECT payto_uri
+ INTO out_payto_uri
+ FROM wire_targets
+ WHERE wire_target_h_payto=my_wire_target_h_payto;
+ my_required_checks = NULL;
+ SELECT string_to_array (required_checks, ' ')
+ INTO my_required_checks
+ FROM legitimization_requirements
+ WHERE h_payto=my_wire_target_h_payto;
+ SELECT
+ new_threshold
+ ,kyc_requirements
+ ,new_status
+ INTO
+ my_aml_data
+ FROM aml_history
+ WHERE h_payto=in_wire_target_h_payto
+ ORDER BY aml_history_serial_id
+ DESC LIMIT 1;
+ IF FOUND
+ THEN
+ out_aml_limit=my_aml_data.new_threshold;
+ out_aml_status=my_aml_data.kyc_status;
+ my_required_checks
+ = array_cat (my_required_checks,
+ my_aml_data.kyc_requirements);
+ ELSE
+ out_aml_limit=NULL;
+ out_aml_status=0;
+ END IF;
+ OPEN satisfied;
+ LOOP
+ FETCH NEXT FROM satisfied INTO i;
+ EXIT WHEN NOT FOUND;
+ FOR i in 1..array_length(i.satisfied_checks)
+ LOOP
+ my_required_checks
+ = array_remove (my_required_checks,
+ i.satisfied_checks[i]);
+ END LOOP;
+ END LOOP;
+ IF ( (my_required_checks IS NOT NULL) AND
+ (0 < array_length(my_satisfied_checks)) )
+ THEN
+ out_kyc_pending
+ = array_to_string (my_required_checks, ' ');
+ END IF;
+ RETURN;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_refund(
+ IN in_amount_with_fee taler_amount,
+ IN in_amount taler_amount,
+ IN in_deposit_fee taler_amount,
+ IN in_h_contract_terms BYTEA,
+ IN in_rtransaction_id INT8,
+ IN in_deposit_shard INT8,
+ IN in_known_coin_id INT8,
+ IN in_coin_pub BYTEA,
+ IN in_merchant_pub BYTEA,
+ IN in_merchant_sig BYTEA,
+ OUT out_not_found BOOLEAN,
+ OUT out_refund_ok BOOLEAN,
+ OUT out_gone BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ bdsi INT8;
+DECLARE
+ tmp_val INT8;
+DECLARE
+ tmp_frac INT8;
+DECLARE
+ tmp taler_amount;
+DECLARE
+ deposit taler_amount;
+BEGIN
+SELECT
+ bdep.batch_deposit_serial_id
+ ,(cdep.amount_with_fee).val
+ ,(cdep.amount_with_fee).frac
+ ,bdep.done
+ INTO
+ bdsi
+ ,deposit.val
+ ,deposit.frac
+ ,out_gone
+ FROM batch_deposits bdep
+ JOIN coin_deposits cdep
+ USING (batch_deposit_serial_id)
+ WHERE cdep.coin_pub=in_coin_pub
+ AND shard=in_deposit_shard
+ AND merchant_pub=in_merchant_pub
+ AND h_contract_terms=in_h_contract_terms;
+IF NOT FOUND
+THEN
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=TRUE;
+ out_gone=FALSE;
+ RETURN;
+END IF;
+INSERT INTO refunds
+ (batch_deposit_serial_id
+ ,coin_pub
+ ,merchant_sig
+ ,rtransaction_id
+ ,amount_with_fee
+ )
+ VALUES
+ (bdsi
+ ,in_coin_pub
+ ,in_merchant_sig
+ ,in_rtransaction_id
+ ,in_amount_with_fee
+ )
+ ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ PERFORM
+ FROM exchange.refunds
+ WHERE coin_pub=in_coin_pub
+ AND batch_deposit_serial_id=bdsi
+ AND rtransaction_id=in_rtransaction_id
+ AND amount_with_fee=in_amount_with_fee;
+ IF NOT FOUND
+ THEN
+ out_refund_ok=FALSE;
+ out_conflict=TRUE;
+ out_not_found=FALSE;
+ RETURN;
+ END IF;
+ out_refund_ok=TRUE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ out_gone=FALSE;
+ RETURN;
+END IF;
+IF out_gone
+THEN
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ RETURN;
+END IF;
+SELECT
+ SUM((refs.amount_with_fee).val)
+ ,SUM(CAST((refs.amount_with_fee).frac AS INT8))
+ INTO
+ tmp_val
+ ,tmp_frac
+ FROM refunds refs
+ WHERE coin_pub=in_coin_pub
+ AND batch_deposit_serial_id=bdsi;
+IF tmp_val IS NULL
+THEN
+ RAISE NOTICE 'failed to sum up existing refunds';
+ out_refund_ok=FALSE;
+ out_conflict=FALSE;
+ out_not_found=FALSE;
+ RETURN;
+END IF;
+tmp.val = tmp_val + tmp_frac / 100000000;
+tmp.frac = tmp_frac % 100000000;
+IF (tmp.val < deposit.val)
+THEN
+ out_refund_ok=TRUE;
+ELSE
+ IF (tmp.val = deposit.val) AND (tmp.frac <= deposit.frac)
+ THEN
+ out_refund_ok=TRUE;
+ ELSE
+ out_refund_ok=FALSE;
+ END IF;
+END IF;
+IF (tmp.val = deposit.val) AND (tmp.frac = deposit.frac)
+THEN
+ in_amount.frac = in_amount.frac + in_deposit_fee.frac;
+ in_amount.val = in_amount.val + in_deposit_fee.val;
+ in_amount.val = in_amount.val + in_amount.frac / 100000000;
+ in_amount.frac = in_amount.frac % 100000000;
+END IF;
+UPDATE known_coins kc
+ SET
+ remaining.frac=(kc.remaining).frac+in_amount.frac
+ - CASE
+ WHEN (kc.remaining).frac+in_amount.frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val+in_amount.val
+ + CASE
+ WHEN (kc.remaining).frac+in_amount.frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_coin_pub;
+out_conflict=FALSE;
+out_not_found=FALSE;
+END $$;
+COMMENT ON FUNCTION exchange_do_refund(taler_amount, taler_amount, taler_amount, BYTEA, INT8, INT8, INT8, BYTEA, BYTEA, BYTEA)
+ IS 'Executes a refund operation, checking that the corresponding deposit was sufficient to cover the refunded amount';
+DROP FUNCTION IF EXISTS exchange_do_recoup_to_reserve;
+CREATE FUNCTION exchange_do_recoup_to_reserve(
+ IN in_reserve_pub BYTEA,
+ IN in_withdraw_id INT8,
+ IN in_coin_blind BYTEA,
+ IN in_coin_pub BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_sig BYTEA,
+ IN in_reserve_gc INT8,
+ IN in_reserve_expiration INT8,
+ IN in_recoup_timestamp INT8,
+ OUT out_recoup_ok BOOLEAN,
+ OUT out_internal_failure BOOLEAN,
+ OUT out_recoup_timestamp INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ tmp taler_amount;
+ balance taler_amount;
+ new_balance taler_amount;
+ reserve RECORD;
+ rval RECORD;
+BEGIN
+out_internal_failure=FALSE;
+SELECT
+ remaining
+ INTO
+ rval
+FROM exchange.known_coins
+ WHERE coin_pub=in_coin_pub;
+IF NOT FOUND
+THEN
+ out_internal_failure=TRUE;
+ out_recoup_ok=FALSE;
+ RETURN;
+END IF;
+tmp := rval.remaining;
+IF tmp.val + tmp.frac = 0
+THEN
+ SELECT
+ recoup_timestamp
+ INTO
+ out_recoup_timestamp
+ FROM exchange.recoup
+ WHERE coin_pub=in_coin_pub;
+ out_recoup_ok=FOUND;
+ RETURN;
+END IF;
+UPDATE known_coins
+ SET
+ remaining.val = 0
+ ,remaining.frac = 0
+ WHERE coin_pub=in_coin_pub;
+SELECT current_balance
+ INTO reserve
+ FROM reserves
+ WHERE reserve_pub=in_reserve_pub;
+balance = reserve.current_balance;
+new_balance.frac=balance.frac+tmp.frac
+ - CASE
+ WHEN balance.frac+tmp.frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END;
+new_balance.val=balance.val+tmp.val
+ + CASE
+ WHEN balance.frac+tmp.frac >= 100000000
+ THEN 1
+ ELSE 0
+ END;
+UPDATE reserves
+ SET
+ current_balance = new_balance,
+ gc_date=GREATEST(gc_date, in_reserve_gc),
+ expiration_date=GREATEST(expiration_date, in_reserve_expiration)
+ WHERE reserve_pub=in_reserve_pub;
+IF NOT FOUND
+THEN
+ RAISE NOTICE 'failed to increase reserve balance from recoup';
+ out_recoup_ok=TRUE;
+ out_internal_failure=TRUE;
+ RETURN;
+END IF;
+INSERT INTO exchange.recoup
+ (coin_pub
+ ,coin_sig
+ ,coin_blind
+ ,amount
+ ,recoup_timestamp
+ ,withdraw_id
+ )
+VALUES
+ (in_coin_pub
+ ,in_coin_sig
+ ,in_coin_blind
+ ,tmp
+ ,in_recoup_timestamp
+ ,in_withdraw_id);
+out_recoup_ok=TRUE;
+out_recoup_timestamp=in_recoup_timestamp;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_recoup_to_coin(
+ IN in_old_coin_pub BYTEA,
+ IN in_refresh_id INT8,
+ IN in_coin_blind BYTEA,
+ IN in_coin_pub BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_sig BYTEA,
+ IN in_recoup_timestamp INT8,
+ OUT out_recoup_ok BOOLEAN,
+ OUT out_internal_failure BOOLEAN,
+ OUT out_recoup_timestamp INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ rval RECORD;
+DECLARE
+ tmp taler_amount;
+BEGIN
+out_internal_failure=FALSE;
+SELECT
+ remaining
+ INTO
+ rval
+FROM exchange.known_coins
+ WHERE coin_pub=in_coin_pub;
+IF NOT FOUND
+THEN
+ out_internal_failure=TRUE;
+ out_recoup_ok=FALSE;
+ RETURN;
+END IF;
+tmp := rval.remaining;
+IF tmp.val + tmp.frac = 0
+THEN
+ SELECT
+ recoup_timestamp
+ INTO
+ out_recoup_timestamp
+ FROM recoup_refresh
+ WHERE coin_pub=in_coin_pub;
+ out_recoup_ok=FOUND;
+ RETURN;
+END IF;
+UPDATE known_coins
+ SET
+ remaining.val = 0
+ ,remaining.frac = 0
+ WHERE coin_pub=in_coin_pub;
+UPDATE known_coins kc
+ SET
+ remaining.frac=(kc.remaining).frac+tmp.frac
+ - CASE
+ WHEN (kc.remaining).frac+tmp.frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val+tmp.val
+ + CASE
+ WHEN (kc.remaining).frac+tmp.frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_old_coin_pub;
+IF NOT FOUND
+THEN
+ RAISE NOTICE 'failed to increase old coin balance from recoup';
+ out_recoup_ok=TRUE;
+ out_internal_failure=TRUE;
+ RETURN;
+END IF;
+INSERT INTO recoup_refresh
+ (coin_pub
+ ,known_coin_id
+ ,coin_sig
+ ,coin_blind
+ ,amount
+ ,recoup_timestamp
+ ,refresh_id
+ )
+VALUES
+ (in_coin_pub
+ ,in_known_coin_id
+ ,in_coin_sig
+ ,in_coin_blind
+ ,tmp
+ ,in_recoup_timestamp
+ ,in_refresh_id);
+out_recoup_ok=TRUE;
+out_recoup_timestamp=in_recoup_timestamp;
+END $$;
+CREATE OR REPLACE PROCEDURE exchange_do_main_gc(
+ IN in_ancient_date INT8,
+ IN in_now INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ reserve_uuid_min INT8;
+ coin_min INT8;
+ batch_deposit_min INT8;
+ withdraw_min INT8;
+ denom_min INT8;
+BEGIN
+DELETE FROM prewire
+ WHERE finished=TRUE;
+DELETE FROM wire_fee
+ WHERE end_date < in_ancient_date;
+DELETE FROM refresh
+ WHERE execution_date < in_ancient_date;
+DELETE FROM kycauths_in
+ WHERE execution_date < in_ancient_date;
+DELETE FROM reserves_in
+ WHERE execution_date < in_ancient_date;
+DELETE FROM batch_deposits
+ WHERE wire_deadline < in_ancient_date;
+DELETE FROM reserves
+ WHERE gc_date < in_now
+ AND current_balance = (0, 0);
+SELECT withdraw_id
+ INTO withdraw_min
+ FROM withdraw
+ ORDER BY withdraw_id ASC
+ LIMIT 1;
+DELETE FROM recoup
+ WHERE withdraw_id < withdraw_min;
+SELECT reserve_uuid
+ INTO reserve_uuid_min
+ FROM reserves
+ ORDER BY reserve_uuid ASC
+ LIMIT 1;
+DELETE FROM reserves_out
+ WHERE reserve_uuid < reserve_uuid_min;
+DELETE FROM denominations
+ WHERE expire_legal < in_now
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM reserves_out)
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM known_coins
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
+ FROM recoup))
+ AND denominations_serial NOT IN
+ (SELECT DISTINCT denominations_serial
+ FROM known_coins
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
+ FROM recoup_refresh));
+DELETE FROM recoup_refresh
+ WHERE known_coin_id < coin_min;
+SELECT known_coin_id
+ INTO coin_min
+ FROM known_coins
+ ORDER BY known_coin_id ASC
+ LIMIT 1;
+SELECT batch_deposit_serial_id
+ INTO batch_deposit_min
+ FROM coin_deposits
+ ORDER BY batch_deposit_serial_id ASC
+ LIMIT 1;
+DELETE FROM refunds
+ WHERE batch_deposit_serial_id < batch_deposit_min;
+DELETE FROM aggregation_tracking
+ WHERE batch_deposit_serial_id < batch_deposit_min;
+DELETE FROM coin_deposits
+ WHERE batch_deposit_serial_id < batch_deposit_min;
+SELECT denominations_serial
+ INTO denom_min
+ FROM denominations
+ ORDER BY denominations_serial ASC
+ LIMIT 1;
+DELETE FROM cs_nonce_locks
+ WHERE max_denomination_serial <= denom_min;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_purse_delete(
+ IN in_purse_pub BYTEA,
+ IN in_purse_sig BYTEA,
+ IN in_now INT8,
+ OUT out_decided BOOLEAN,
+ OUT out_found BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_deposit record;
+DECLARE
+ my_in_reserve_quota BOOLEAN;
+BEGIN
+PERFORM refunded FROM purse_decision
+ WHERE purse_pub=in_purse_pub;
+IF FOUND
+THEN
+ out_found=TRUE;
+ out_decided=TRUE;
+ RETURN;
+END IF;
+out_decided=FALSE;
+SELECT in_reserve_quota
+ INTO my_in_reserve_quota
+ FROM exchange.purse_requests
+ WHERE purse_pub=in_purse_pub;
+out_found=FOUND;
+IF NOT FOUND
+THEN
+ RETURN;
+END IF;
+INSERT INTO exchange.purse_deletion
+ (purse_pub
+ ,purse_sig)
+VALUES
+ (in_purse_pub
+ ,in_purse_sig)
+ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ RETURN;
+END IF;
+DELETE FROM contracts
+ WHERE purse_pub=in_purse_pub;
+INSERT INTO purse_decision
+ (purse_pub
+ ,action_timestamp
+ ,refunded)
+VALUES
+ (in_purse_pub
+ ,in_now
+ ,TRUE);
+IF (my_in_reserve_quota)
+THEN
+ UPDATE reserves
+ SET purses_active=purses_active-1
+ WHERE reserve_pub IN
+ (SELECT reserve_pub
+ FROM exchange.purse_merges
+ WHERE purse_pub=in_purse_pub
+ LIMIT 1);
+END IF;
+FOR my_deposit IN
+ SELECT coin_pub
+ ,amount_with_fee
+ FROM exchange.purse_deposits
+ WHERE purse_pub = in_purse_pub
+LOOP
+ UPDATE known_coins kc SET
+ remaining.frac=(kc.remaining).frac+(my_deposit.amount_with_fee).frac
+ - CASE
+ WHEN (kc.remaining).frac+(my_deposit.amount_with_fee).frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val+(my_deposit.amount_with_fee).val
+ + CASE
+ WHEN (kc.remaining).frac+(my_deposit.amount_with_fee).frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub = my_deposit.coin_pub;
+END LOOP;
+END $$;
+COMMENT ON FUNCTION exchange_do_purse_delete(BYTEA,BYTEA,INT8)
+ IS 'Delete a previously undecided purse and refund the coins (if any).';
+CREATE OR REPLACE FUNCTION exchange_do_purse_deposit(
+ IN in_partner_id INT8,
+ IN in_purse_pub BYTEA,
+ IN in_amount_with_fee taler_amount,
+ IN in_coin_pub BYTEA,
+ IN in_coin_sig BYTEA,
+ IN in_amount_without_fee taler_amount,
+ IN in_reserve_expiration INT8,
+ IN in_now INT8,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_late BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ was_merged BOOLEAN;
+DECLARE
+ psi INT8;
+DECLARE
+ my_amount taler_amount;
+DECLARE
+ was_paid BOOLEAN;
+DECLARE
+ my_in_reserve_quota BOOLEAN;
+DECLARE
+ my_reserve_pub BYTEA;
+DECLARE
+ rval RECORD;
+BEGIN
+INSERT INTO purse_deposits
+ (partner_serial_id
+ ,purse_pub
+ ,coin_pub
+ ,amount_with_fee
+ ,coin_sig)
+ VALUES
+ (in_partner_id
+ ,in_purse_pub
+ ,in_coin_pub
+ ,in_amount_with_fee
+ ,in_coin_sig)
+ ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ PERFORM
+ FROM purse_deposits
+ WHERE purse_pub = in_purse_pub
+ AND coin_pub = in_coin_pub
+ AND coin_sig = in_coin_sig;
+ IF NOT FOUND
+ THEN
+ out_balance_ok=FALSE;
+ out_late=FALSE;
+ out_conflict=TRUE;
+ RETURN;
+ ELSE
+ out_late=FALSE;
+ out_balance_ok=TRUE;
+ out_conflict=FALSE;
+ RETURN;
+ END IF;
+END IF;
+PERFORM
+ FROM exchange.purse_deletion
+ WHERE purse_pub = in_purse_pub;
+IF FOUND
+THEN
+ out_late=TRUE;
+ out_balance_ok=FALSE;
+ out_conflict=FALSE;
+ RETURN;
+END IF;
+UPDATE known_coins kc
+ SET
+ remaining.frac=(kc.remaining).frac-in_amount_with_fee.frac
+ + CASE
+ WHEN (kc.remaining).frac < in_amount_with_fee.frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val-in_amount_with_fee.val
+ - CASE
+ WHEN (kc.remaining).frac < in_amount_with_fee.frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_coin_pub
+ AND ( ((kc.remaining).val > in_amount_with_fee.val) OR
+ ( ((kc.remaining).frac >= in_amount_with_fee.frac) AND
+ ((kc.remaining).val >= in_amount_with_fee.val) ) );
+IF NOT FOUND
+THEN
+ out_balance_ok=FALSE;
+ out_late=FALSE;
+ out_conflict=FALSE;
+ RETURN;
+END IF;
+UPDATE purse_requests pr
+ SET
+ balance.frac=(pr.balance).frac+in_amount_without_fee.frac
+ - CASE
+ WHEN (pr.balance).frac+in_amount_without_fee.frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ balance.val=(pr.balance).val+in_amount_without_fee.val
+ + CASE
+ WHEN (pr.balance).frac+in_amount_without_fee.frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE purse_pub=in_purse_pub;
+out_conflict=FALSE;
+out_balance_ok=TRUE;
+SELECT COALESCE(partner_serial_id,0)
+ ,reserve_pub
+ INTO psi
+ ,my_reserve_pub
+ FROM purse_merges
+ WHERE purse_pub=in_purse_pub;
+IF NOT FOUND
+THEN
+ out_late=FALSE;
+ RETURN;
+END IF;
+SELECT
+ amount_with_fee
+ ,in_reserve_quota
+ INTO
+ rval
+ FROM exchange.purse_requests preq
+ WHERE (purse_pub=in_purse_pub)
+ AND ( ( ( ((preq.amount_with_fee).val <= (preq.balance).val)
+ AND ((preq.amount_with_fee).frac <= (preq.balance).frac) )
+ OR ((preq.amount_with_fee).val < (preq.balance).val) ) );
+IF NOT FOUND
+THEN
+ out_late=FALSE;
+ RETURN;
+END IF;
+my_amount := rval.amount_with_fee;
+my_in_reserve_quota := rval.in_reserve_quota;
+INSERT INTO purse_decision
+ (purse_pub
+ ,action_timestamp
+ ,refunded)
+VALUES
+ (in_purse_pub
+ ,in_now
+ ,FALSE)
+ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ out_late=TRUE;
+ RETURN;
+END IF;
+out_late=FALSE;
+IF (my_in_reserve_quota)
+THEN
+ UPDATE reserves
+ SET purses_active=purses_active-1
+ WHERE reserve_pub IN
+ (SELECT reserve_pub
+ FROM purse_merges
+ WHERE purse_pub=my_purse_pub
+ LIMIT 1);
+END IF;
+IF (0 != psi)
+THEN
+ UPDATE purse_actions
+ SET action_date=0
+ ,partner_serial_id=psi
+ WHERE purse_pub=in_purse_pub;
+ELSE
+ INSERT INTO reserves
+ (reserve_pub
+ ,current_balance
+ ,expiration_date
+ ,gc_date)
+ VALUES
+ (my_reserve_pub
+ ,my_amount
+ ,in_reserve_expiration
+ ,in_reserve_expiration)
+ ON CONFLICT DO NOTHING;
+ IF NOT FOUND
+ THEN
+ UPDATE reserves
+ SET
+ current_balance.frac=(current_balance).frac+my_amount.frac
+ - CASE
+ WHEN (current_balance).frac + my_amount.frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ ,current_balance.val=(current_balance).val+my_amount.val
+ + CASE
+ WHEN (current_balance).frac + my_amount.frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ ,expiration_date=GREATEST(expiration_date,in_reserve_expiration)
+ ,gc_date=GREATEST(gc_date,in_reserve_expiration)
+ WHERE reserve_pub=my_reserve_pub;
+ END IF;
+END IF;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_purse_merge(
+ IN in_purse_pub BYTEA,
+ IN in_merge_sig BYTEA,
+ IN in_merge_timestamp INT8,
+ IN in_reserve_sig BYTEA,
+ IN in_partner_url TEXT,
+ IN in_reserve_pub BYTEA,
+ IN in_wallet_h_payto BYTEA,
+ IN in_expiration_date INT8,
+ OUT out_no_partner BOOLEAN,
+ OUT out_no_balance BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_amount taler_amount;
+DECLARE
+ my_purse_fee taler_amount;
+DECLARE
+ my_partner_serial_id INT8;
+DECLARE
+ my_in_reserve_quota BOOLEAN;
+DECLARE
+ rval RECORD;
+DECLARE
+ reserve_bal RECORD;
+DECLARE
+ balance taler_amount;
+BEGIN
+INSERT INTO reserves
+ (reserve_pub
+ ,expiration_date
+ ,gc_date)
+ VALUES
+ (in_reserve_pub
+ ,in_expiration_date
+ ,in_expiration_date)
+ ON CONFLICT DO NOTHING;
+IF in_partner_url IS NULL
+THEN
+ my_partner_serial_id=NULL;
+ELSE
+ SELECT
+ partner_serial_id
+ INTO
+ my_partner_serial_id
+ FROM partners
+ WHERE partner_base_url=in_partner_url
+ AND start_date <= in_merge_timestamp
+ AND end_date > in_merge_timestamp;
+ IF NOT FOUND
+ THEN
+ out_no_partner=TRUE;
+ out_conflict=FALSE;
+ RETURN;
+ END IF;
+END IF;
+out_no_partner=FALSE;
+SELECT amount_with_fee
+ ,purse_fee
+ ,in_reserve_quota
+ INTO rval
+ FROM purse_requests pr
+ WHERE purse_pub=in_purse_pub
+ AND (pr.balance).val >= (pr.amount_with_fee).val
+ AND ( (pr.balance).frac >= (pr.amount_with_fee).frac OR
+ (pr.balance).val > (pr.amount_with_fee).val );
+IF NOT FOUND
+THEN
+ out_no_balance=TRUE;
+ out_conflict=FALSE;
+ RETURN;
+END IF;
+my_amount := rval.amount_with_fee;
+my_purse_fee := rval.purse_fee;
+my_in_reserve_quota := rval.in_reserve_quota;
+out_no_balance=FALSE;
+INSERT INTO purse_merges
+ (partner_serial_id
+ ,reserve_pub
+ ,purse_pub
+ ,merge_sig
+ ,merge_timestamp)
+ VALUES
+ (my_partner_serial_id
+ ,in_reserve_pub
+ ,in_purse_pub
+ ,in_merge_sig
+ ,in_merge_timestamp)
+ ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ PERFORM
+ FROM purse_merges
+ WHERE purse_pub=in_purse_pub
+ AND merge_sig=in_merge_sig;
+ IF NOT FOUND
+ THEN
+ out_conflict=TRUE;
+ RETURN;
+ END IF;
+ out_conflict=FALSE;
+ RETURN;
+END IF;
+INSERT INTO purse_decision
+ (purse_pub
+ ,action_timestamp
+ ,refunded)
+VALUES
+ (in_purse_pub
+ ,in_merge_timestamp
+ ,FALSE)
+ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ out_conflict=TRUE;
+ RETURN;
+END IF;
+out_conflict=FALSE;
+IF (my_in_reserve_quota)
+THEN
+ UPDATE reserves
+ SET purses_active=purses_active-1
+ WHERE reserve_pub IN
+ (SELECT reserve_pub
+ FROM purse_merges
+ WHERE purse_pub=my_purse_pub
+ LIMIT 1);
+END IF;
+INSERT INTO account_merges
+ (reserve_pub
+ ,reserve_sig
+ ,purse_pub
+ ,wallet_h_payto)
+ VALUES
+ (in_reserve_pub
+ ,in_reserve_sig
+ ,in_purse_pub
+ ,in_wallet_h_payto);
+IF (0 != my_partner_serial_id)
+THEN
+ UPDATE purse_actions
+ SET action_date=0
+ ,partner_serial_id=my_partner_serial_id
+ WHERE purse_pub=in_purse_pub;
+ELSE
+ my_amount.val = my_amount.val + my_purse_fee.val;
+ my_amount.frac = my_amount.frac + my_purse_fee.frac;
+ my_amount.val = my_amount.val + my_amount.frac / 100000000;
+ my_amount.frac = my_amount.frac % 100000000;
+ SELECT current_balance
+ INTO reserve_bal
+ FROM reserves
+ WHERE reserve_pub=in_reserve_pub;
+ balance = reserve_bal.current_balance;
+ balance.val=balance.val+my_amount.val
+ + CASE
+ WHEN balance.frac + my_amount.frac >= 100000000
+ THEN 1
+ ELSE 0
+ END;
+ balance.frac=balance.frac+my_amount.frac
+ - CASE
+ WHEN balance.frac + my_amount.frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END;
+ UPDATE reserves
+ SET current_balance=balance
+ WHERE reserve_pub=in_reserve_pub;
+END IF;
+RETURN;
+END $$;
+COMMENT ON FUNCTION exchange_do_purse_merge(BYTEA, BYTEA, INT8, BYTEA, TEXT, BYTEA, BYTEA, INT8)
+ IS 'Checks that the partner exists, the purse has not been merged with a different reserve and that the purse is full. If so, persists the merge data and either merges the purse with the reserve or marks it as ready for the taler-exchange-router. Caller MUST abort the transaction on failures so as to not persist data by accident.';
+CREATE OR REPLACE FUNCTION exchange_do_reserve_purse(
+ IN in_purse_pub BYTEA,
+ IN in_merge_sig BYTEA,
+ IN in_merge_timestamp INT8,
+ IN in_reserve_expiration INT8,
+ IN in_reserve_gc INT8,
+ IN in_reserve_sig BYTEA,
+ IN in_reserve_quota BOOLEAN,
+ IN in_purse_fee taler_amount,
+ IN in_reserve_pub BYTEA,
+ IN in_wallet_h_payto BYTEA,
+ OUT out_no_funds BOOLEAN,
+ OUT out_no_reserve BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+INSERT INTO purse_merges
+ (partner_serial_id
+ ,reserve_pub
+ ,purse_pub
+ ,merge_sig
+ ,merge_timestamp)
+ VALUES
+ (NULL
+ ,in_reserve_pub
+ ,in_purse_pub
+ ,in_merge_sig
+ ,in_merge_timestamp)
+ ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ PERFORM
+ FROM purse_merges
+ WHERE purse_pub=in_purse_pub
+ AND merge_sig=in_merge_sig;
+ IF NOT FOUND
+ THEN
+ out_conflict=TRUE;
+ out_no_reserve=FALSE;
+ out_no_funds=FALSE;
+ RETURN;
+ END IF;
+ out_conflict=FALSE;
+ out_no_funds=FALSE;
+ out_no_reserve=FALSE;
+ RETURN;
+END IF;
+out_conflict=FALSE;
+PERFORM
+ FROM exchange.reserves
+ WHERE reserve_pub=in_reserve_pub;
+out_no_reserve = NOT FOUND;
+IF (in_reserve_quota)
+THEN
+ IF (out_no_reserve)
+ THEN
+ out_no_funds=TRUE;
+ RETURN;
+ END IF;
+ UPDATE exchange.reserves
+ SET purses_active=purses_active+1
+ WHERE reserve_pub=in_reserve_pub
+ AND purses_active < purses_allowed;
+ IF NOT FOUND
+ THEN
+ out_no_funds=TRUE;
+ RETURN;
+ END IF;
+ELSE
+ IF (out_no_reserve)
+ THEN
+ IF ( (0 != in_purse_fee.val) OR
+ (0 != in_purse_fee.frac) )
+ THEN
+ out_no_funds=TRUE;
+ RETURN;
+ END IF;
+ INSERT INTO exchange.reserves
+ (reserve_pub
+ ,expiration_date
+ ,gc_date)
+ VALUES
+ (in_reserve_pub
+ ,in_reserve_expiration
+ ,in_reserve_gc);
+ ELSE
+ UPDATE exchange.reserves
+ SET
+ current_balance.frac=(current_balance).frac-in_purse_fee.frac
+ + CASE
+ WHEN (current_balance).frac < in_purse_fee.frac
+ THEN 100000000
+ ELSE 0
+ END,
+ current_balance.val=(current_balance).val-in_purse_fee.val
+ - CASE
+ WHEN (current_balance).frac < in_purse_fee.frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE reserve_pub=in_reserve_pub
+ AND ( ((current_balance).val > in_purse_fee.val) OR
+ ( ((current_balance).frac >= in_purse_fee.frac) AND
+ ((current_balance).val >= in_purse_fee.val) ) );
+ IF NOT FOUND
+ THEN
+ out_no_funds=TRUE;
+ RETURN;
+ END IF;
+ END IF;
+END IF;
+out_no_funds=FALSE;
+INSERT INTO account_merges
+ (reserve_pub
+ ,reserve_sig
+ ,purse_pub
+ ,wallet_h_payto)
+ VALUES
+ (in_reserve_pub
+ ,in_reserve_sig
+ ,in_purse_pub
+ ,in_wallet_h_payto);
+END $$;
+COMMENT ON FUNCTION exchange_do_reserve_purse(BYTEA, BYTEA, INT8, INT8, INT8, BYTEA, BOOLEAN, taler_amount, BYTEA, BYTEA)
+ IS 'Create a purse for a reserve.';
+CREATE OR REPLACE FUNCTION exchange_do_expire_purse(
+ IN in_start_time INT8,
+ IN in_end_time INT8,
+ IN in_now INT8,
+ OUT out_found BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_purse_pub BYTEA;
+DECLARE
+ my_deposit record;
+DECLARE
+ my_in_reserve_quota BOOLEAN;
+BEGIN
+SELECT purse_pub
+ ,in_reserve_quota
+ INTO my_purse_pub
+ ,my_in_reserve_quota
+ FROM purse_requests
+ WHERE (purse_expiration >= in_start_time) AND
+ (purse_expiration < in_end_time) AND
+ NOT was_decided
+ ORDER BY purse_expiration ASC
+ LIMIT 1;
+out_found = FOUND;
+IF NOT FOUND
+THEN
+ RETURN;
+END IF;
+INSERT INTO purse_decision
+ (purse_pub
+ ,action_timestamp
+ ,refunded)
+VALUES
+ (my_purse_pub
+ ,in_now
+ ,TRUE);
+NOTIFY X8DJSPNYJMNZDAP7GN6YQ4EZVSQXMF3HRP4VAR347WP9SZYP1C200;
+IF (my_in_reserve_quota)
+THEN
+ UPDATE reserves
+ SET purses_active=purses_active-1
+ WHERE reserve_pub IN
+ (SELECT reserve_pub
+ FROM exchange.purse_merges
+ WHERE purse_pub=my_purse_pub
+ LIMIT 1);
+END IF;
+FOR my_deposit IN
+ SELECT coin_pub
+ ,amount_with_fee
+ FROM purse_deposits
+ WHERE purse_pub = my_purse_pub
+LOOP
+ UPDATE known_coins kc SET
+ remaining.frac=(kc.remaining).frac+(my_deposit.amount_with_fee).frac
+ - CASE
+ WHEN (kc.remaining).frac+(my_deposit.amount_with_fee).frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val+(my_deposit.amount_with_fee).val
+ + CASE
+ WHEN (kc.remaining).frac+(my_deposit.amount_with_fee).frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub = my_deposit.coin_pub;
+ END LOOP;
+END $$;
+COMMENT ON FUNCTION exchange_do_expire_purse(INT8,INT8,INT8)
+ IS 'Finds an expired purse in the given time range and refunds the coins (if any).';
+CREATE OR REPLACE FUNCTION exchange_do_reserve_open_deposit(
+ IN in_coin_pub BYTEA,
+ IN in_known_coin_id INT8,
+ IN in_coin_sig BYTEA,
+ IN in_reserve_sig BYTEA,
+ IN in_reserve_pub BYTEA,
+ IN in_coin_total taler_amount,
+ OUT out_insufficient_funds BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+INSERT INTO exchange.reserves_open_deposits
+ (reserve_sig
+ ,reserve_pub
+ ,coin_pub
+ ,coin_sig
+ ,contribution
+ )
+ VALUES
+ (in_reserve_sig
+ ,in_reserve_pub
+ ,in_coin_pub
+ ,in_coin_sig
+ ,in_coin_total
+ )
+ ON CONFLICT DO NOTHING;
+IF NOT FOUND
+THEN
+ out_insufficient_funds=FALSE;
+ RETURN;
+END IF;
+UPDATE exchange.known_coins kc
+ SET
+ remaining.frac=(kc.remaining).frac-in_coin_total.frac
+ + CASE
+ WHEN (kc.remaining).frac < in_coin_total.frac
+ THEN 100000000
+ ELSE 0
+ END,
+ remaining.val=(kc.remaining).val-in_coin_total.val
+ - CASE
+ WHEN (kc.remaining).frac < in_coin_total.frac
+ THEN 1
+ ELSE 0
+ END
+ WHERE coin_pub=in_coin_pub
+ AND ( ((kc.remaining).val > in_coin_total.val) OR
+ ( ((kc.remaining).frac >= in_coin_total.frac) AND
+ ((kc.remaining).val >= in_coin_total.val) ) );
+IF NOT FOUND
+THEN
+ out_insufficient_funds=TRUE;
+ RETURN;
+END IF;
+out_insufficient_funds=FALSE;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_reserve_open(
+ IN in_reserve_pub BYTEA,
+ IN in_total_paid taler_amount,
+ IN in_reserve_payment taler_amount,
+ IN in_min_purse_limit INT4,
+ IN in_default_purse_limit INT4,
+ IN in_reserve_sig BYTEA,
+ IN in_desired_expiration INT8,
+ IN in_reserve_gc_delay INT8,
+ IN in_now INT8,
+ IN in_open_fee taler_amount,
+ OUT out_open_cost taler_amount,
+ OUT out_final_expiration INT8,
+ OUT out_no_reserve BOOLEAN,
+ OUT out_no_funds BOOLEAN,
+ OUT out_reserve_balance taler_amount)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_balance taler_amount;
+ my_cost taler_amount;
+ my_cost_tmp INT8;
+ my_years_tmp INT4;
+ my_years INT4;
+ my_needs_update BOOL;
+ my_expiration_date INT8;
+ reserve RECORD;
+BEGIN
+SELECT current_balance
+ ,expiration_date
+ ,purses_allowed
+ INTO reserve
+ FROM reserves
+ WHERE reserve_pub=in_reserve_pub;
+IF NOT FOUND
+THEN
+ RAISE NOTICE 'reserve not found';
+ out_no_reserve = TRUE;
+ out_no_funds = TRUE;
+ out_reserve_balance.val = 0;
+ out_reserve_balance.frac = 0;
+ out_open_cost.val = 0;
+ out_open_cost.frac = 0;
+ out_final_expiration = 0;
+ RETURN;
+END IF;
+out_no_reserve = FALSE;
+out_reserve_balance = reserve.current_balance;
+IF (reserve.expiration_date < in_now)
+THEN
+ my_expiration_date = in_now;
+ELSE
+ my_expiration_date = reserve.expiration_date;
+END IF;
+my_cost.val = 0;
+my_cost.frac = 0;
+my_needs_update = FALSE;
+my_years = 0;
+IF (my_expiration_date < in_desired_expiration)
+THEN
+ my_years = (31535999999999 + in_desired_expiration - my_expiration_date) / 31536000000000;
+ reserve.purses_allowed = in_default_purse_limit;
+ my_expiration_date = my_expiration_date + 31536000000000 * my_years;
+END IF;
+IF (reserve.purses_allowed < in_min_purse_limit)
+THEN
+ my_years = (31535999999999 + in_desired_expiration - in_now) / 31536000000000;
+ my_expiration_date = in_now + 31536000000000 * my_years;
+ my_years_tmp = (in_min_purse_limit + in_default_purse_limit - reserve.purses_allowed - 1) / in_default_purse_limit;
+ my_years = my_years + my_years_tmp;
+ reserve.purses_allowed = reserve.purses_allowed + (in_default_purse_limit * my_years_tmp);
+END IF;
+IF (my_years > 0)
+THEN
+ my_cost.val = my_years * in_open_fee.val;
+ my_cost_tmp = my_years * in_open_fee.frac / 100000000;
+ IF (CAST (my_cost.val + my_cost_tmp AS INT8) < my_cost.val)
+ THEN
+ out_open_cost.val=9223372036854775807;
+ out_open_cost.frac=2147483647;
+ out_final_expiration=my_expiration_date;
+ out_no_funds=FALSE;
+ RAISE NOTICE 'arithmetic issue computing amount';
+ RETURN;
+ END IF;
+ my_cost.val = CAST (my_cost.val + my_cost_tmp AS INT8);
+ my_cost.frac = my_years * in_open_fee.frac % 100000000;
+ my_needs_update = TRUE;
+END IF;
+IF NOT my_needs_update
+THEN
+ out_final_expiration = reserve.expiration_date;
+ out_open_cost.val = 0;
+ out_open_cost.frac = 0;
+ out_no_funds=FALSE;
+ RAISE NOTICE 'no change required';
+ RETURN;
+END IF;
+IF ( (in_total_paid.val < my_cost.val) OR
+ ( (in_total_paid.val = my_cost.val) AND
+ (in_total_paid.frac < my_cost.frac) ) )
+THEN
+ out_open_cost.val = my_cost.val;
+ out_open_cost.frac = my_cost.frac;
+ out_no_funds=FALSE;
+ IF (reserve.expiration_date >= in_desired_expiration)
+ THEN
+ RAISE NOTICE 'forcing low expiration time';
+ out_final_expiration = 0;
+ ELSE
+ out_final_expiration = reserve.expiration_date;
+ END IF;
+ RAISE NOTICE 'amount paid too low';
+ RETURN;
+END IF;
+IF (out_reserve_balance.val > in_reserve_payment.val)
+THEN
+ IF (out_reserve_balance.frac >= in_reserve_payment.frac)
+ THEN
+ my_balance.val=out_reserve_balance.val - in_reserve_payment.val;
+ my_balance.frac=out_reserve_balance.frac - in_reserve_payment.frac;
+ ELSE
+ my_balance.val=out_reserve_balance.val - in_reserve_payment.val - 1;
+ my_balance.frac=out_reserve_balance.frac + 100000000 - in_reserve_payment.frac;
+ END IF;
+ELSE
+ IF (out_reserve_balance.val = in_reserve_payment.val) AND (out_reserve_balance.frac >= in_reserve_payment.frac)
+ THEN
+ my_balance.val=0;
+ my_balance.frac=out_reserve_balance.frac - in_reserve_payment.frac;
+ ELSE
+ out_final_expiration = reserve.expiration_date;
+ out_open_cost.val = my_cost.val;
+ out_open_cost.frac = my_cost.frac;
+ out_no_funds=TRUE;
+ RAISE NOTICE 'reserve balance too low';
+ RETURN;
+ END IF;
+END IF;
+UPDATE reserves SET
+ current_balance=my_balance
+ ,gc_date=reserve.expiration_date + in_reserve_gc_delay
+ ,expiration_date=my_expiration_date
+ ,purses_allowed=reserve.purses_allowed
+WHERE
+ reserve_pub=in_reserve_pub;
+out_final_expiration=my_expiration_date;
+out_open_cost = my_cost;
+out_no_funds=FALSE;
+RETURN;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_insert_or_update_policy_details(
+ IN in_policy_hash_code BYTEA,
+ IN in_policy_json TEXT,
+ IN in_deadline INT8,
+ IN in_commitment taler_amount,
+ IN in_accumulated_total taler_amount,
+ IN in_fee taler_amount,
+ IN in_transferable taler_amount,
+ IN in_fulfillment_state SMALLINT,
+ OUT out_policy_details_serial_id INT8,
+ OUT out_accumulated_total taler_amount,
+ OUT out_fulfillment_state SMALLINT)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ cur_commitment taler_amount;
+DECLARE
+ cur_accumulated_total taler_amount;
+DECLARE
+ rval RECORD;
+BEGIN
+ INSERT INTO policy_details
+ (policy_hash_code,
+ policy_json,
+ deadline,
+ commitment,
+ accumulated_total,
+ fee,
+ transferable,
+ fulfillment_state)
+ VALUES (in_policy_hash_code,
+ in_policy_json,
+ in_deadline,
+ in_commitment,
+ in_accumulated_total,
+ in_fee,
+ in_transferable,
+ in_fulfillment_state)
+ ON CONFLICT (policy_hash_code) DO NOTHING
+ RETURNING policy_details_serial_id INTO out_policy_details_serial_id;
+ IF FOUND THEN
+ out_accumulated_total = in_accumulated_total;
+ out_fulfillment_state = in_fulfillment_state;
+ RETURN;
+ END IF;
+ SELECT policy_details_serial_id
+ ,commitment
+ ,accumulated_total
+ INTO rval
+ FROM policy_details
+ WHERE policy_hash_code = in_policy_hash_code;
+ out_policy_details_serial_id := rval.policy_details_serial_id;
+ cur_commitment := rval.commitment;
+ cur_accumulated_total := rval.accumulated_total;
+ out_accumulated_total.val = cur_accumulated_total.val + in_accumulated_total.val;
+ out_accumulated_total.frac = cur_accumulated_total.frac + in_accumulated_total.frac;
+ out_accumulated_total.val = out_accumulated_total.val + out_accumulated_total.frac / 100000000;
+ out_accumulated_total.frac = out_accumulated_total.frac % 100000000;
+ IF (out_accumulated_total.val > (1 << 52))
+ THEN
+ RAISE EXCEPTION 'accumulation overflow';
+ END IF;
+ IF (out_fullfillment_state = 2)
+ THEN
+ IF (out_accumulated_total.val >= cur_commitment.val OR
+ (out_accumulated_total.val = cur_commitment.val AND
+ out_accumulated_total.frac >= cur_commitment.frac))
+ THEN
+ out_fulfillment_state = 3;
+ END IF;
+ END IF;
+ UPDATE exchange.policy_details
+ SET
+ accumulated = out_accumulated_total,
+ fulfillment_state = out_fulfillment_state
+ WHERE
+ policy_details_serial_id = out_policy_details_serial_id;
+END $$;
+DROP FUNCTION IF EXISTS exchange_do_insert_aml_decision;
+CREATE FUNCTION exchange_do_insert_aml_decision(
+ IN in_payto_uri TEXT,
+ IN in_h_normalized_payto BYTEA,
+ IN in_h_full_payto BYTEA,
+ IN in_decision_time INT8,
+ IN in_expiration_time INT8,
+ IN in_properties TEXT,
+ IN in_kyc_attributes_enc BYTEA,
+ IN in_kyc_attributes_hash BYTEA,
+ IN in_kyc_attributes_expiration INT8,
+ IN in_new_rules TEXT,
+ IN in_to_investigate BOOLEAN,
+ IN in_new_measure_name TEXT,
+ IN in_jmeasures TEXT,
+ IN in_justification TEXT,
+ IN in_decider_pub BYTEA,
+ IN in_decider_sig BYTEA,
+ IN in_notify_s TEXT,
+ IN ina_events TEXT[],
+ IN in_form_name TEXT,
+ OUT out_invalid_officer BOOLEAN,
+ OUT out_account_unknown BOOLEAN,
+ OUT out_last_date INT8,
+ OUT out_legitimization_measure_serial_id INT8,
+ OUT out_payto_uri TEXT)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_outcome_serial_id INT8;
+ my_legitimization_process_serial_id INT8;
+ my_kyc_attributes_serial_id INT8;
+ my_rec RECORD;
+ my_access_token BYTEA;
+ my_i INT4;
+ ini_event TEXT;
+BEGIN
+out_account_unknown=FALSE;
+out_legitimization_measure_serial_id=0;
+IF in_decider_pub IS NOT NULL
+THEN
+ IF in_justification IS NULL OR in_decider_sig IS NULL
+ THEN
+ RAISE EXCEPTION 'Got in_decider_sig without justification or signature.';
+ END IF;
+ PERFORM
+ FROM aml_staff
+ WHERE decider_pub=in_decider_pub
+ AND is_active
+ AND NOT read_only;
+ IF NOT FOUND
+ THEN
+ out_invalid_officer=TRUE;
+ out_last_date=0;
+ RETURN;
+ END IF;
+END IF;
+out_invalid_officer=FALSE;
+SELECT decision_time
+ INTO out_last_date
+ FROM legitimization_outcomes
+ WHERE h_payto=in_h_normalized_payto
+ AND is_active
+ ORDER BY decision_time DESC, outcome_serial_id DESC;
+IF FOUND
+THEN
+ IF in_decider_pub IS NOT NULL AND out_last_date > in_decision_time
+ THEN
+ RETURN;
+ END IF;
+ UPDATE legitimization_outcomes
+ SET is_active=FALSE
+ WHERE h_payto=in_h_normalized_payto
+ AND is_active;
+ELSE
+ out_last_date = 0;
+END IF;
+SELECT access_token
+ ,payto_uri
+ INTO my_rec
+ FROM wire_targets
+ WHERE h_normalized_payto=in_h_normalized_payto;
+IF NOT FOUND
+THEN
+ IF in_payto_uri IS NULL
+ THEN
+ out_account_unknown=TRUE;
+ RETURN;
+ END IF;
+ out_payto_uri = in_payto_uri;
+ INSERT INTO wire_targets
+ (wire_target_h_payto
+ ,h_normalized_payto
+ ,payto_uri)
+ VALUES
+ (in_h_full_payto
+ ,in_h_normalized_payto
+ ,in_payto_uri)
+ RETURNING access_token
+ INTO my_access_token;
+ELSE
+ my_access_token = my_rec.access_token;
+ out_payto_uri = my_rec.payto_uri;
+END IF;
+IF in_jmeasures IS NOT NULL
+THEN
+ SELECT legitimization_measure_serial_id
+ INTO out_legitimization_measure_serial_id
+ FROM legitimization_measures
+ WHERE access_token=my_access_token
+ AND jmeasures=in_jmeasures
+ AND NOT is_finished;
+ IF NOT FOUND
+ THEN
+ INSERT INTO legitimization_measures
+ (access_token
+ ,start_time
+ ,jmeasures
+ ,display_priority)
+ VALUES
+ (my_access_token
+ ,in_decision_time
+ ,in_jmeasures
+ ,1)
+ RETURNING
+ legitimization_measure_serial_id
+ INTO
+ out_legitimization_measure_serial_id;
+ END IF;
+END IF;
+RAISE NOTICE 'marking legi measures of % as finished except for %', my_access_token, out_legitimization_measure_serial_id;
+UPDATE legitimization_measures
+ SET is_finished=TRUE
+ WHERE access_token=my_access_token
+ AND NOT is_finished
+ AND legitimization_measure_serial_id != out_legitimization_measure_serial_id;
+UPDATE legitimization_outcomes
+ SET is_active=FALSE
+ WHERE h_payto=in_h_normalized_payto
+ AND expiration_time >= in_decision_time;
+INSERT INTO legitimization_outcomes
+ (h_payto
+ ,decision_time
+ ,expiration_time
+ ,jproperties
+ ,new_measure_name
+ ,to_investigate
+ ,jnew_rules
+ )
+ VALUES
+ (in_h_normalized_payto
+ ,in_decision_time
+ ,in_expiration_time
+ ,in_properties
+ ,in_new_measure_name
+ ,in_to_investigate
+ ,in_new_rules
+ )
+ RETURNING
+ outcome_serial_id
+ INTO
+ my_outcome_serial_id;
+IF in_kyc_attributes_enc IS NOT NULL
+THEN
+ IF in_kyc_attributes_hash IS NULL OR in_kyc_attributes_expiration IS NULL
+ THEN
+ RAISE EXCEPTION 'Got in_kyc_attributes_hash without hash or expiration.';
+ END IF;
+ IF in_decider_pub IS NULL
+ THEN
+ RAISE EXCEPTION 'Got in_kyc_attributes_hash without in_decider_pub.';
+ END IF;
+ INSERT INTO legitimization_processes
+ (h_payto
+ ,start_time
+ ,expiration_time
+ ,provider_name
+ ,provider_user_id
+ ,finished
+ ) VALUES
+ (in_h_normalized_payto
+ ,in_decision_time
+ ,in_decision_time
+ ,'aml-officer'
+ ,in_decider_pub
+ ,TRUE
+ )
+ RETURNING legitimization_process_serial_id
+ INTO my_legitimization_process_serial_id;
+ INSERT INTO kyc_attributes
+ (h_payto
+ ,collection_time
+ ,expiration_time
+ ,form_name
+ ,by_aml_officer
+ ,encrypted_attributes
+ ,legitimization_serial
+ ) VALUES
+ (in_h_normalized_payto
+ ,in_decision_time
+ ,in_kyc_attributes_expiration
+ ,in_form_name
+ ,TRUE
+ ,in_kyc_attributes_enc
+ ,my_legitimization_process_serial_id
+ )
+ RETURNING kyc_attributes_serial_id
+ INTO my_kyc_attributes_serial_id;
+END IF;
+IF in_decider_pub IS NOT NULL
+THEN
+ INSERT INTO aml_history
+ (h_payto
+ ,outcome_serial_id
+ ,justification
+ ,decider_pub
+ ,decider_sig
+ ,kyc_attributes_hash
+ ,kyc_attributes_serial_id
+ ) VALUES
+ (in_h_normalized_payto
+ ,my_outcome_serial_id
+ ,in_justification
+ ,in_decider_pub
+ ,in_decider_sig
+ ,in_kyc_attributes_hash
+ ,my_kyc_attributes_serial_id
+ );
+END IF;
+FOR i IN 1..COALESCE(array_length(ina_events,1),0)
+LOOP
+ ini_event = ina_events[i];
+ INSERT INTO kyc_events
+ (event_timestamp
+ ,event_type)
+ VALUES
+ (in_decision_time
+ ,ini_event);
+END LOOP;
+INSERT INTO kyc_alerts
+ (h_payto
+ ,trigger_type)
+ VALUES
+ (in_h_normalized_payto,1)
+ ON CONFLICT DO NOTHING;
+EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,in_notify_s);
+END $$;
+COMMENT ON FUNCTION exchange_do_insert_aml_decision(TEXT, BYTEA, BYTEA, INT8, INT8, TEXT, BYTEA, BYTEA, INT8, TEXT, BOOLEAN, TEXT, TEXT, TEXT, BYTEA, BYTEA, TEXT, TEXT[], TEXT)
+ IS 'Checks whether the AML officer is eligible to make AML decisions and if so inserts the decision into the table';
+DROP FUNCTION IF EXISTS exchange_do_insert_successor_measure;
+CREATE FUNCTION exchange_do_insert_successor_measure(
+ IN in_h_normalized_payto BYTEA,
+ IN in_decision_time INT8,
+ IN in_expiration_time INT8,
+ IN in_new_measure_name TEXT,
+ IN in_jmeasures TEXT,
+ OUT out_last_date INT8,
+ OUT out_account_unknown BOOLEAN,
+ OUT out_legitimization_measure_serial_id INT8
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_outcome_serial_id INT8;
+ my_access_token BYTEA;
+BEGIN
+out_account_unknown=FALSE;
+out_legitimization_measure_serial_id=0;
+SELECT decision_time
+ INTO out_last_date
+ FROM legitimization_outcomes
+ WHERE h_payto=in_h_normalized_payto
+ AND is_active
+ ORDER BY decision_time DESC, outcome_serial_id DESC;
+IF FOUND
+THEN
+ IF out_last_date > in_decision_time
+ THEN
+ RETURN;
+ END IF;
+ UPDATE legitimization_outcomes
+ SET is_active=FALSE
+ WHERE h_payto=in_h_normalized_payto
+ AND is_active;
+ELSE
+ out_last_date = 0;
+END IF;
+SELECT access_token
+ INTO my_access_token
+ FROM wire_targets
+ WHERE h_normalized_payto=in_h_normalized_payto;
+IF NOT FOUND
+THEN
+ IF in_payto_uri IS NULL
+ THEN
+ out_account_unknown=TRUE;
+ RETURN;
+ END IF;
+ INSERT INTO wire_targets
+ (wire_target_h_payto
+ ,h_normalized_payto
+ ,payto_uri)
+ VALUES
+ (in_h_full_payto
+ ,in_h_normalized_payto
+ ,in_payto_uri)
+ RETURNING access_token
+ INTO my_access_token;
+END IF;
+SELECT legitimization_measure_serial_id
+ INTO out_legitimization_measure_serial_id
+ FROM legitimization_measures
+ WHERE access_token=my_access_token
+ AND jmeasures=in_jmeasures
+ AND NOT is_finished;
+IF NOT FOUND
+THEN
+ INSERT INTO legitimization_measures
+ (access_token
+ ,start_time
+ ,jmeasures
+ ,display_priority)
+ VALUES
+ (my_access_token
+ ,in_decision_time
+ ,in_jmeasures
+ ,1)
+ RETURNING
+ legitimization_measure_serial_id
+ INTO
+ out_legitimization_measure_serial_id;
+END IF;
+UPDATE legitimization_measures
+ SET is_finished=TRUE
+ WHERE access_token=my_access_token
+ AND NOT is_finished
+ AND legitimization_measure_serial_id != out_legitimization_measure_serial_id;
+UPDATE legitimization_outcomes
+ SET is_active=FALSE
+ WHERE h_payto=in_h_normalized_payto
+ AND expiration_time >= in_decision_time;
+INSERT INTO legitimization_outcomes
+ (h_payto
+ ,decision_time
+ ,expiration_time
+ ,jproperties
+ ,new_measure_name
+ ,to_investigate
+ ,jnew_rules
+ )
+ VALUES
+ (in_h_normalized_payto
+ ,in_decision_time
+ ,in_expiration_time
+ ,'{}'
+ ,in_new_measure_name
+ ,FALSE
+ ,NULL
+ )
+ RETURNING
+ outcome_serial_id
+ INTO
+ my_outcome_serial_id;
+END $$;
+COMMENT ON FUNCTION exchange_do_insert_successor_measure(BYTEA, INT8, INT8, TEXT, TEXT)
+ IS 'Checks whether the AML officer is eligible to make AML decisions and if so inserts the decision into the table';
+CREATE OR REPLACE FUNCTION exchange_do_insert_aml_officer(
+ IN in_decider_pub BYTEA,
+ IN in_master_sig BYTEA,
+ IN in_decider_name TEXT,
+ IN in_is_active BOOLEAN,
+ IN in_read_only BOOLEAN,
+ IN in_last_change INT8,
+ OUT out_last_change INT8)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+INSERT INTO exchange.aml_staff
+ (decider_pub
+ ,master_sig
+ ,decider_name
+ ,is_active
+ ,read_only
+ ,last_change
+ ) VALUES
+ (in_decider_pub
+ ,in_master_sig
+ ,in_decider_name
+ ,in_is_active
+ ,in_read_only
+ ,in_last_change)
+ ON CONFLICT DO NOTHING;
+IF FOUND
+THEN
+ out_last_change=0;
+ RETURN;
+END IF;
+SELECT last_change
+ INTO out_last_change
+ FROM exchange.aml_staff
+ WHERE decider_pub=in_decider_pub;
+ASSERT FOUND, 'cannot have INSERT conflict but no AML staff record';
+IF out_last_change >= in_last_change
+THEN
+ RETURN;
+END IF;
+UPDATE exchange.aml_staff
+ SET master_sig=in_master_sig
+ ,decider_name=in_decider_name
+ ,is_active=in_is_active
+ ,read_only=in_read_only
+ ,last_change=in_last_change
+ WHERE decider_pub=in_decider_pub;
+END $$;
+COMMENT ON FUNCTION exchange_do_insert_aml_officer(BYTEA, BYTEA, TEXT, BOOL, BOOL, INT8)
+ IS 'Inserts or updates AML staff record, making sure the update is more recent than the previous change';
+DROP FUNCTION IF EXISTS exchange_do_array_reserves_insert;
+CREATE FUNCTION exchange_do_array_reserves_insert(
+ IN in_gc_date INT8,
+ IN in_reserve_expiration INT8,
+ IN ina_reserve_pub BYTEA[],
+ IN ina_wire_ref INT8[],
+ IN ina_credit taler_amount[],
+ IN ina_exchange_account_name TEXT[],
+ IN ina_execution_date INT8[],
+ IN ina_wire_source_h_payto BYTEA[],
+ IN ina_h_normalized_payto BYTEA[],
+ IN ina_payto_uri TEXT[],
+ IN ina_notify TEXT[])
+RETURNS SETOF exchange_do_array_reserve_insert_return_type
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ conflict BOOL;
+ dup BOOL;
+ uuid INT8;
+ i INT4;
+ ini_reserve_pub BYTEA;
+ ini_wire_ref INT8;
+ ini_credit taler_amount;
+ ini_exchange_account_name TEXT;
+ ini_execution_date INT8;
+ ini_wire_source_h_payto BYTEA;
+ ini_h_normalized_payto BYTEA;
+ ini_payto_uri TEXT;
+ ini_notify TEXT;
+BEGIN
+ FOR i IN 1..array_length(ina_reserve_pub,1)
+ LOOP
+ ini_reserve_pub = ina_reserve_pub[i];
+ ini_wire_ref = ina_wire_ref[i];
+ ini_credit = ina_credit[i];
+ ini_exchange_account_name = ina_exchange_account_name[i];
+ ini_execution_date = ina_execution_date[i];
+ ini_wire_source_h_payto = ina_wire_source_h_payto[i];
+ ini_h_normalized_payto = ina_h_normalized_payto[i];
+ ini_payto_uri = ina_payto_uri[i];
+ ini_notify = ina_notify[i];
+ INSERT INTO wire_targets
+ (wire_target_h_payto
+ ,h_normalized_payto
+ ,payto_uri
+ ) VALUES (
+ ini_wire_source_h_payto
+ ,ini_h_normalized_payto
+ ,ini_payto_uri
+ )
+ ON CONFLICT DO NOTHING;
+ INSERT INTO reserves
+ (reserve_pub
+ ,current_balance
+ ,expiration_date
+ ,gc_date
+ ) VALUES (
+ ini_reserve_pub
+ ,ini_credit
+ ,in_reserve_expiration
+ ,in_gc_date
+ )
+ ON CONFLICT DO NOTHING
+ RETURNING reserve_uuid
+ INTO uuid;
+ conflict = NOT FOUND;
+ INSERT INTO reserves_in
+ (reserve_pub
+ ,wire_reference
+ ,credit
+ ,exchange_account_section
+ ,wire_source_h_payto
+ ,execution_date
+ ) VALUES (
+ ini_reserve_pub
+ ,ini_wire_ref
+ ,ini_credit
+ ,ini_exchange_account_name
+ ,ini_wire_source_h_payto
+ ,ini_execution_date
+ )
+ ON CONFLICT DO NOTHING;
+ IF NOT FOUND
+ THEN
+ IF conflict
+ THEN
+ dup = TRUE;
+ else
+ dup = FALSE;
+ END IF;
+ ELSE
+ IF NOT conflict
+ THEN
+ EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,ini_notify);
+ END IF;
+ dup = FALSE;
+ END IF;
+ RETURN NEXT (dup,uuid);
+ END LOOP;
+ RETURN;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_batch_reserves_update(
+ IN in_reserve_pub BYTEA,
+ IN in_expiration_date INT8,
+ IN in_wire_ref INT8,
+ IN in_credit taler_amount,
+ IN in_exchange_account_name TEXT,
+ IN in_wire_source_h_payto BYTEA,
+ IN in_notify text,
+ OUT out_duplicate BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO reserves_in
+ (reserve_pub
+ ,wire_reference
+ ,credit
+ ,exchange_account_section
+ ,wire_source_h_payto
+ ,execution_date)
+ VALUES
+ (in_reserve_pub
+ ,in_wire_ref
+ ,in_credit
+ ,in_exchange_account_name
+ ,in_wire_source_h_payto
+ ,in_expiration_date)
+ ON CONFLICT DO NOTHING;
+ IF FOUND
+ THEN
+ out_duplicate = FALSE;
+ UPDATE reserves rs
+ SET
+ current_balance.frac = (rs.current_balance).frac+in_credit.frac
+ - CASE
+ WHEN (rs.current_balance).frac + in_credit.frac >= 100000000
+ THEN 100000000
+ ELSE 1
+ END
+ ,current_balance.val = (rs.current_balance).val+in_credit.val
+ + CASE
+ WHEN (rs.current_balance).frac + in_credit.frac >= 100000000
+ THEN 1
+ ELSE 0
+ END
+ ,expiration_date=GREATEST(expiration_date,in_expiration_date)
+ ,gc_date=GREATEST(gc_date,in_expiration_date)
+ WHERE reserve_pub=in_reserve_pub;
+ EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,in_notify);
+ ELSE
+ out_duplicate = TRUE;
+ END IF;
+ RETURN;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_get_link_data(
+ IN in_coin_pub BYTEA
+)
+RETURNS SETOF record
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ curs CURSOR
+ FOR
+ SELECT
+ melt_serial_id
+ FROM refresh_commitments
+ WHERE old_coin_pub=in_coin_pub;
+DECLARE
+ i RECORD;
+BEGIN
+OPEN curs;
+LOOP
+ FETCH NEXT FROM curs INTO i;
+ EXIT WHEN NOT FOUND;
+ RETURN QUERY
+ SELECT
+ tp.transfer_pub
+ ,denoms.denom_pub
+ ,rrc.ev_sig
+ ,rrc.ewv
+ ,rrc.link_sig
+ ,rrc.freshcoin_index
+ ,rrc.coin_ev
+ FROM refresh_revealed_coins rrc
+ JOIN refresh_transfer_keys tp
+ ON (tp.melt_serial_id=rrc.melt_serial_id)
+ JOIN denominations denoms
+ ON (rrc.denominations_serial=denoms.denominations_serial)
+ WHERE rrc.melt_serial_id =i.melt_serial_id
+ ORDER BY tp.transfer_pub,
+ rrc.freshcoin_index ASC
+ ;
+END LOOP;
+CLOSE curs;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_batch4_known_coin(
+ IN in_coin_pub1 BYTEA,
+ IN in_denom_pub_hash1 BYTEA,
+ IN in_h_age_commitment1 BYTEA,
+ IN in_denom_sig1 BYTEA,
+ IN in_coin_pub2 BYTEA,
+ IN in_denom_pub_hash2 BYTEA,
+ IN in_h_age_commitment2 BYTEA,
+ IN in_denom_sig2 BYTEA,
+ IN in_coin_pub3 BYTEA,
+ IN in_denom_pub_hash3 BYTEA,
+ IN in_h_age_commitment3 BYTEA,
+ IN in_denom_sig3 BYTEA,
+ IN in_coin_pub4 BYTEA,
+ IN in_denom_pub_hash4 BYTEA,
+ IN in_h_age_commitment4 BYTEA,
+ IN in_denom_sig4 BYTEA,
+ OUT existed1 BOOLEAN,
+ OUT existed2 BOOLEAN,
+ OUT existed3 BOOLEAN,
+ OUT existed4 BOOLEAN,
+ OUT known_coin_id1 INT8,
+ OUT known_coin_id2 INT8,
+ OUT known_coin_id3 INT8,
+ OUT known_coin_id4 INT8,
+ OUT denom_pub_hash1 BYTEA,
+ OUT denom_pub_hash2 BYTEA,
+ OUT denom_pub_hash3 BYTEA,
+ OUT denom_pub_hash4 BYTEA,
+ OUT age_commitment_hash1 BYTEA,
+ OUT age_commitment_hash2 BYTEA,
+ OUT age_commitment_hash3 BYTEA,
+ OUT age_commitment_hash4 BYTEA)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+WITH dd AS (
+SELECT
+ denominations_serial,
+ coin
+ FROM denominations
+ WHERE denom_pub_hash
+ IN
+ (in_denom_pub_hash1,
+ in_denom_pub_hash2,
+ in_denom_pub_hash3,
+ in_denom_pub_hash4)
+ ),
+ input_rows AS (
+ VALUES
+ (in_coin_pub1,
+ in_denom_pub_hash1,
+ in_h_age_commitment1,
+ in_denom_sig1),
+ (in_coin_pub2,
+ in_denom_pub_hash2,
+ in_h_age_commitment2,
+ in_denom_sig2),
+ (in_coin_pub3,
+ in_denom_pub_hash3,
+ in_h_age_commitment3,
+ in_denom_sig3),
+ (in_coin_pub4,
+ in_denom_pub_hash4,
+ in_h_age_commitment4,
+ in_denom_sig4)
+ ),
+ ins AS (
+ INSERT INTO known_coins (
+ coin_pub,
+ denominations_serial,
+ age_commitment_hash,
+ denom_sig,
+ remaining
+ )
+ SELECT
+ ir.coin_pub,
+ dd.denominations_serial,
+ ir.age_commitment_hash,
+ ir.denom_sig,
+ dd.coin
+ FROM input_rows ir
+ JOIN dd
+ ON dd.denom_pub_hash = ir.denom_pub_hash
+ ON CONFLICT DO NOTHING
+ RETURNING known_coin_id
+ ),
+ exists AS (
+ SELECT
+ CASE
+ WHEN
+ ins.known_coin_id IS NOT NULL
+ THEN
+ FALSE
+ ELSE
+ TRUE
+ END AS existed,
+ ins.known_coin_id,
+ dd.denom_pub_hash,
+ kc.age_commitment_hash
+ FROM input_rows ir
+ LEFT JOIN ins
+ ON ins.coin_pub = ir.coin_pub
+ LEFT JOIN known_coins kc
+ ON kc.coin_pub = ir.coin_pub
+ LEFT JOIN dd
+ ON dd.denom_pub_hash = ir.denom_pub_hash
+ )
+SELECT
+ exists.existed AS existed1,
+ exists.known_coin_id AS known_coin_id1,
+ exists.denom_pub_hash AS denom_pub_hash1,
+ exists.age_commitment_hash AS age_commitment_hash1,
+ (
+ SELECT exists.existed
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ ) AS existed2,
+ (
+ SELECT exists.known_coin_id
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ ) AS known_coin_id2,
+ (
+ SELECT exists.denom_pub_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ ) AS denom_pub_hash2,
+ (
+ SELECT exists.age_commitment_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ )AS age_commitment_hash2,
+ (
+ SELECT exists.existed
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash3
+ ) AS existed3,
+ (
+ SELECT exists.known_coin_id
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash3
+ ) AS known_coin_id3,
+ (
+ SELECT exists.denom_pub_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash3
+ ) AS denom_pub_hash3,
+ (
+ SELECT exists.age_commitment_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash3
+ )AS age_commitment_hash3,
+ (
+ SELECT exists.existed
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash4
+ ) AS existed4,
+ (
+ SELECT exists.known_coin_id
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash4
+ ) AS known_coin_id4,
+ (
+ SELECT exists.denom_pub_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash4
+ ) AS denom_pub_hash4,
+ (
+ SELECT exists.age_commitment_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash4
+ )AS age_commitment_hash4
+FROM exists;
+RETURN;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_batch2_known_coin(
+ IN in_coin_pub1 BYTEA,
+ IN in_denom_pub_hash1 BYTEA,
+ IN in_h_age_commitment1 BYTEA,
+ IN in_denom_sig1 BYTEA,
+ IN in_coin_pub2 BYTEA,
+ IN in_denom_pub_hash2 BYTEA,
+ IN in_h_age_commitment2 BYTEA,
+ IN in_denom_sig2 BYTEA,
+ OUT existed1 BOOLEAN,
+ OUT existed2 BOOLEAN,
+ OUT known_coin_id1 INT8,
+ OUT known_coin_id2 INT8,
+ OUT denom_pub_hash1 BYTEA,
+ OUT denom_pub_hash2 BYTEA,
+ OUT age_commitment_hash1 BYTEA,
+ OUT age_commitment_hash2 BYTEA)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+WITH dd AS (
+SELECT
+ denominations_serial,
+ coin
+ FROM denominations
+ WHERE denom_pub_hash
+ IN
+ (in_denom_pub_hash1,
+ in_denom_pub_hash2)
+ ),
+ input_rows AS (
+ VALUES
+ (in_coin_pub1,
+ in_denom_pub_hash1,
+ in_h_age_commitment1,
+ in_denom_sig1),
+ (in_coin_pub2,
+ in_denom_pub_hash2,
+ in_h_age_commitment2,
+ in_denom_sig2)
+ ),
+ ins AS (
+ INSERT INTO known_coins (
+ coin_pub,
+ denominations_serial,
+ age_commitment_hash,
+ denom_sig,
+ remaining
+ )
+ SELECT
+ ir.coin_pub,
+ dd.denominations_serial,
+ ir.age_commitment_hash,
+ ir.denom_sig,
+ dd.coin
+ FROM input_rows ir
+ JOIN dd
+ ON dd.denom_pub_hash = ir.denom_pub_hash
+ ON CONFLICT DO NOTHING
+ RETURNING known_coin_id
+ ),
+ exists AS (
+ SELECT
+ CASE
+ WHEN ins.known_coin_id IS NOT NULL
+ THEN
+ FALSE
+ ELSE
+ TRUE
+ END AS existed,
+ ins.known_coin_id,
+ dd.denom_pub_hash,
+ kc.age_commitment_hash
+ FROM input_rows ir
+ LEFT JOIN ins
+ ON ins.coin_pub = ir.coin_pub
+ LEFT JOIN known_coins kc
+ ON kc.coin_pub = ir.coin_pub
+ LEFT JOIN dd
+ ON dd.denom_pub_hash = ir.denom_pub_hash
+ )
+SELECT
+ exists.existed AS existed1,
+ exists.known_coin_id AS known_coin_id1,
+ exists.denom_pub_hash AS denom_pub_hash1,
+ exists.age_commitment_hash AS age_commitment_hash1,
+ (
+ SELECT exists.existed
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ ) AS existed2,
+ (
+ SELECT exists.known_coin_id
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ ) AS known_coin_id2,
+ (
+ SELECT exists.denom_pub_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ ) AS denom_pub_hash2,
+ (
+ SELECT exists.age_commitment_hash
+ FROM exists
+ WHERE exists.denom_pub_hash = in_denom_pub_hash2
+ )AS age_commitment_hash2
+FROM exists;
+RETURN;
+END $$;
+CREATE OR REPLACE FUNCTION exchange_do_batch1_known_coin(
+ IN in_coin_pub1 BYTEA,
+ IN in_denom_pub_hash1 BYTEA,
+ IN in_h_age_commitment1 BYTEA,
+ IN in_denom_sig1 BYTEA,
+ OUT existed1 BOOLEAN,
+ OUT known_coin_id1 INT8,
+ OUT denom_pub_hash1 BYTEA,
+ OUT age_commitment_hash1 BYTEA)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+WITH dd AS (
+SELECT
+ denominations_serial,
+ coin
+ FROM denominations
+ WHERE denom_pub_hash
+ IN
+ (in_denom_pub_hash1,
+ in_denom_pub_hash2)
+ ),
+ input_rows AS (
+ VALUES
+ (in_coin_pub1,
+ in_denom_pub_hash1,
+ in_h_age_commitment1,
+ in_denom_sig1)
+ ),
+ ins AS (
+ INSERT INTO known_coins (
+ coin_pub,
+ denominations_serial,
+ age_commitment_hash,
+ denom_sig,
+ remaining
+ )
+ SELECT
+ ir.coin_pub,
+ dd.denominations_serial,
+ ir.age_commitment_hash,
+ ir.denom_sig,
+ dd.coin
+ FROM input_rows ir
+ JOIN dd
+ ON dd.denom_pub_hash = ir.denom_pub_hash
+ ON CONFLICT DO NOTHING
+ RETURNING known_coin_id
+ ),
+ exists AS (
+ SELECT
+ CASE
+ WHEN ins.known_coin_id IS NOT NULL
+ THEN
+ FALSE
+ ELSE
+ TRUE
+ END AS existed,
+ ins.known_coin_id,
+ dd.denom_pub_hash,
+ kc.age_commitment_hash
+ FROM input_rows ir
+ LEFT JOIN ins
+ ON ins.coin_pub = ir.coin_pub
+ LEFT JOIN known_coins kc
+ ON kc.coin_pub = ir.coin_pub
+ LEFT JOIN dd
+ ON dd.denom_pub_hash = ir.denom_pub_hash
+ )
+SELECT
+ exists.existed AS existed1,
+ exists.known_coin_id AS known_coin_id1,
+ exists.denom_pub_hash AS denom_pub_hash1,
+ exists.age_commitment_hash AS age_commitment_hash1
+FROM exists;
+RETURN;
+END $$;
+DROP PROCEDURE IF EXISTS exchange_do_kycauth_in_insert;
+CREATE PROCEDURE exchange_do_kycauth_in_insert(
+ IN in_account_pub BYTEA,
+ IN in_wire_reference INT8,
+ IN in_credit taler_amount,
+ IN in_wire_source_h_payto BYTEA,
+ IN in_h_normalized_payto BYTEA,
+ IN in_payto_uri TEXT,
+ IN in_exchange_account_name TEXT,
+ IN in_execution_date INT8,
+ IN in_notify_s TEXT)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ INSERT INTO kycauths_in
+ (account_pub
+ ,wire_reference
+ ,credit
+ ,wire_source_h_payto
+ ,exchange_account_section
+ ,execution_date
+ ) VALUES (
+ in_account_pub
+ ,in_wire_reference
+ ,in_credit
+ ,in_wire_source_h_payto
+ ,in_exchange_account_name
+ ,in_execution_date
+ )
+ ON CONFLICT DO NOTHING;
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ UPDATE wire_targets
+ SET target_pub=in_account_pub
+ WHERE wire_target_h_payto=in_wire_source_h_payto;
+ IF NOT FOUND
+ THEN
+ INSERT INTO wire_targets
+ (wire_target_h_payto
+ ,h_normalized_payto
+ ,payto_uri
+ ,target_pub
+ ) VALUES (
+ in_wire_source_h_payto
+ ,in_h_normalized_payto
+ ,in_payto_uri
+ ,in_account_pub);
+ END IF;
+ EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,in_notify_s);
+END $$;
+DROP FUNCTION IF EXISTS exchange_do_trigger_kyc_rule_for_account;
+CREATE FUNCTION exchange_do_trigger_kyc_rule_for_account(
+ IN in_h_normalized_payto BYTEA,
+ IN in_account_pub BYTEA,
+ IN in_merchant_pub BYTEA,
+ IN in_payto_uri TEXT,
+ IN in_h_full_payto BYTEA,
+ IN in_now INT8,
+ IN in_jmeasures TEXT,
+ IN in_display_priority INT4,
+ IN in_notify_s TEXT,
+ OUT out_legitimization_measure_serial_id INT8,
+ OUT out_bad_kyc_auth BOOL)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_rec RECORD;
+ my_access_token BYTEA;
+ my_account_pub BYTEA;
+ my_reserve_pub BYTEA;
+BEGIN
+SELECT
+ access_token
+ ,target_pub
+INTO
+ my_rec
+FROM wire_targets
+ WHERE h_normalized_payto=in_h_normalized_payto;
+IF FOUND
+THEN
+ my_access_token = my_rec.access_token;
+ my_account_pub = my_rec.target_pub;
+ out_bad_kyc_auth = COALESCE ((my_account_pub != in_merchant_pub), TRUE);
+ELSE
+ INSERT INTO wire_targets
+ (payto_uri
+ ,wire_target_h_payto
+ ,h_normalized_payto
+ ,target_pub)
+ VALUES
+ (in_payto_uri
+ ,in_h_full_payto
+ ,in_h_normalized_payto
+ ,in_account_pub)
+ RETURNING
+ access_token
+ INTO my_access_token;
+ out_bad_kyc_auth=TRUE;
+END IF;
+IF out_bad_kyc_auth
+THEN
+ PERFORM FROM reserves_in
+ WHERE wire_source_h_payto IN (
+ SELECT wire_target_h_payto
+ FROM wire_targets
+ WHERE h_normalized_payto=in_h_normalized_payto
+ )
+ AND reserve_pub = in_merchant_pub
+ ORDER BY execution_date DESC;
+ IF FOUND
+ THEN
+ out_bad_kyc_auth = FALSE;
+ END IF;
+END IF;
+UPDATE legitimization_measures
+ SET display_priority=GREATEST(in_display_priority,display_priority)
+ WHERE access_token=my_access_token
+ AND jmeasures=in_jmeasures
+ AND NOT is_finished
+ RETURNING legitimization_measure_serial_id
+ INTO out_legitimization_measure_serial_id;
+IF NOT FOUND
+THEN
+ INSERT INTO legitimization_measures
+ (access_token
+ ,start_time
+ ,jmeasures
+ ,display_priority)
+ VALUES
+ (my_access_token
+ ,in_now
+ ,in_jmeasures
+ ,in_display_priority)
+ RETURNING
+ legitimization_measure_serial_id
+ INTO
+ out_legitimization_measure_serial_id;
+ UPDATE legitimization_measures
+ SET is_finished=TRUE
+ WHERE access_token=my_access_token
+ AND NOT is_finished
+ AND legitimization_measure_serial_id != out_legitimization_measure_serial_id;
+END IF;
+EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,in_notify_s);
+END $$;
+DROP FUNCTION IF EXISTS exchange_do_lookup_kyc_requirement_by_row;
+CREATE FUNCTION exchange_do_lookup_kyc_requirement_by_row(
+ IN in_h_normalized_payto BYTEA,
+ OUT out_account_pub BYTEA,
+ OUT out_reserve_pub BYTEA,
+ OUT out_access_token BYTEA,
+ OUT out_jrules TEXT,
+ OUT out_payto TEXT,
+ OUT out_not_found BOOLEAN,
+ OUT out_rule_gen INT8,
+ OUT out_aml_review BOOLEAN,
+ OUT out_kyc_required BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_wtrec RECORD;
+ my_lorec RECORD;
+BEGIN
+SELECT access_token
+ ,target_pub
+ ,payto_uri
+ INTO my_wtrec
+ FROM wire_targets
+ WHERE h_normalized_payto=in_h_normalized_payto;
+IF NOT FOUND
+THEN
+ out_not_found = TRUE;
+ out_kyc_required = FALSE;
+ RETURN;
+END IF;
+out_not_found = FALSE;
+out_payto = my_wtrec.payto_uri;
+out_account_pub = my_wtrec.target_pub;
+out_access_token = my_wtrec.access_token;
+PERFORM
+ FROM legitimization_measures
+ WHERE access_token=out_access_token
+ AND NOT is_finished
+ LIMIT 1;
+out_kyc_required = FOUND;
+SELECT jnew_rules
+ ,to_investigate
+ ,outcome_serial_id
+ INTO my_lorec
+ FROM legitimization_outcomes
+ WHERE h_payto=in_h_normalized_payto
+ AND is_active;
+IF FOUND
+THEN
+ out_jrules=my_lorec.jnew_rules;
+ out_aml_review=my_lorec.to_investigate;
+ out_rule_gen=my_lorec.outcome_serial_id;
+END IF;
+SELECT reserve_pub
+ INTO out_reserve_pub
+ FROM reserves_in
+ WHERE wire_source_h_payto
+ IN (SELECT wire_source_h_payto
+ FROM wire_targets
+ WHERE h_normalized_payto=in_h_normalized_payto)
+ ORDER BY execution_date DESC
+ LIMIT 1;
+END $$;
+DROP FUNCTION IF EXISTS exchange_do_insert_active_legitimization_measure;
+CREATE FUNCTION exchange_do_insert_active_legitimization_measure(
+ IN in_access_token BYTEA,
+ IN in_start_time INT8,
+ IN in_jmeasures TEXT,
+ OUT out_legitimization_measure_serial_id INT8)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+UPDATE legitimization_measures
+ SET is_finished=TRUE
+ WHERE access_token=in_access_token
+ AND NOT is_finished;
+INSERT INTO legitimization_measures
+ (access_token
+ ,start_time
+ ,jmeasures
+ ,display_priority)
+ VALUES
+ (in_access_token
+ ,in_start_time
+ ,in_jmeasures
+ ,1)
+ RETURNING
+ legitimization_measure_serial_id
+ INTO
+ out_legitimization_measure_serial_id;
+END $$;
+COMMENT ON FUNCTION exchange_do_insert_active_legitimization_measure(BYTEA, INT8, TEXT)
+ IS 'Inserts legitimization measure for an account and marks all existing such measures as inactive';
+CREATE OR REPLACE FUNCTION exchange_do_select_aggregations_above_serial(
+ IN in_min_serial_id INT8)
+RETURNS SETOF exchange_do_select_aggregations_above_serial_return_type
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ aggregation CURSOR
+ FOR
+ SELECT
+ batch_deposit_serial_id
+ ,aggregation_serial_id
+ FROM aggregation_tracking
+ WHERE aggregation_serial_id >= in_min_serial_id
+ ORDER BY aggregation_serial_id ASC;
+DECLARE
+ my_total_val INT8;
+DECLARE
+ my_total_frac INT8;
+DECLARE
+ my_total taler_amount;
+DECLARE
+ my_batch_record RECORD;
+DECLARE
+ i RECORD;
+BEGIN
+OPEN aggregation;
+LOOP
+ FETCH NEXT FROM aggregation INTO i;
+ EXIT WHEN NOT FOUND;
+ SELECT
+ SUM((cdep.amount_with_fee).val) AS total_val
+ ,SUM((cdep.amount_with_fee).frac::INT8) AS total_frac
+ INTO
+ my_batch_record
+ FROM coin_deposits cdep
+ WHERE cdep.batch_deposit_serial_id = i.batch_deposit_serial_id;
+ my_total_val=my_batch_record.total_val;
+ my_total_frac=my_batch_record.total_frac;
+ my_total.val = my_total_val + my_total_frac / 100000000;
+ my_total.frac = my_total_frac % 100000000;
+ RETURN NEXT (
+ i.batch_deposit_serial_id
+ ,i.aggregation_serial_id
+ ,my_total
+ );
+END LOOP;
+CLOSE aggregation;
+RETURN;
+END $$;
+DROP FUNCTION IF EXISTS exchange_do_persist_kyc_attributes;
+CREATE FUNCTION exchange_do_persist_kyc_attributes(
+ IN in_process_row INT8,
+ IN in_h_payto BYTEA,
+ IN in_birthday INT4,
+ IN in_provider_name TEXT,
+ IN in_provider_account_id TEXT,
+ IN in_provider_legitimization_id TEXT,
+ IN in_collection_time_ts INT8,
+ IN in_expiration_time INT8,
+ IN in_expiration_time_ts INT8,
+ IN in_enc_attributes BYTEA,
+ IN in_kyc_completed_notify_s TEXT,
+ IN in_form_name TEXT,
+ OUT out_ok BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+INSERT INTO kyc_attributes
+ (h_payto
+ ,collection_time
+ ,expiration_time
+ ,form_name
+ ,by_aml_officer
+ ,encrypted_attributes
+ ,legitimization_serial
+ ) VALUES
+ (in_h_payto
+ ,in_collection_time_ts
+ ,in_expiration_time_ts
+ ,in_form_name
+ ,FALSE
+ ,in_enc_attributes
+ ,in_process_row);
+UPDATE legitimization_processes
+ SET provider_user_id=in_provider_account_id
+ ,provider_legitimization_id=in_provider_legitimization_id
+ ,expiration_time=GREATEST(expiration_time,in_expiration_time)
+ ,finished=TRUE
+ WHERE h_payto=in_h_payto
+ AND legitimization_process_serial_id=in_process_row
+ AND provider_name=in_provider_name;
+out_ok=FOUND;
+UPDATE reserves
+ SET birthday=in_birthday
+ WHERE (reserve_pub IN
+ (SELECT reserve_pub
+ FROM reserves_in
+ WHERE wire_source_h_payto IN
+ (SELECT wire_source_h_payto
+ FROM wire_targets
+ WHERE h_normalized_payto=in_h_payto) ) )
+ AND ( ((current_balance).frac > 0) OR
+ ((current_balance).val > 0 ) )
+ AND (expiration_date > in_collection_time_ts);
+EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,in_kyc_completed_notify_s);
+INSERT INTO kyc_alerts
+ (h_payto
+ ,trigger_type)
+ VALUES
+ (in_h_payto,1)
+ ON CONFLICT DO NOTHING;
+END $$;
+COMMENT ON FUNCTION exchange_do_persist_kyc_attributes(INT8, BYTEA, INT4, TEXT, TEXT, TEXT, INT8, INT8, INT8, BYTEA, TEXT, TEXT)
+ IS 'Inserts new KYC attributes and updates the status of the legitimization process';
+DROP FUNCTION IF EXISTS exchange_do_insert_aml_program_failure;
+CREATE FUNCTION exchange_do_insert_aml_program_failure (
+ IN in_legitimization_process_serial_id INT8,
+ IN in_h_payto BYTEA,
+ IN in_now INT8,
+ IN in_error_code INT4,
+ IN in_error_message TEXT,
+ IN in_kyc_completed_notify_s TEXT,
+ OUT out_update BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+UPDATE legitimization_processes
+ SET finished=TRUE
+ ,error_code=in_error_code
+ ,error_message=in_error_message
+ WHERE h_payto=in_h_payto
+ AND legitimization_process_serial_id=in_legitimization_process_serial_id;
+out_update = FOUND;
+IF NOT FOUND
+THEN
+ INSERT INTO legitimization_processes
+ (finished
+ ,error_code
+ ,error_message
+ ,h_payto
+ ,start_time
+ ,provider_section
+ ) VALUES (
+ TRUE
+ ,in_error_code
+ ,in_error_message
+ ,in_h_payto
+ ,in_now
+ ,'skip'
+ );
+END IF;
+EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,in_kyc_completed_notify_s);
+INSERT INTO kyc_alerts
+ (h_payto
+ ,trigger_type)
+ VALUES
+ (in_h_payto,1)
+ ON CONFLICT DO NOTHING;
+END $$;
+COMMENT ON FUNCTION exchange_do_insert_aml_program_failure(INT8, BYTEA, INT8, INT4, TEXT, TEXT)
+ IS 'Stores information about an AML program run that failed into the legitimization_processes table. Either updates a row of an existing legitimization process, or creates a new entry.';
+DROP FUNCTION IF EXISTS exchange_do_set_aml_lock;
+CREATE FUNCTION exchange_do_set_aml_lock (
+ IN in_h_payto BYTEA,
+ IN in_now INT8,
+ IN in_expiration INT8,
+ OUT out_aml_program_lock_timeout INT8)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+UPDATE wire_targets
+ SET aml_program_lock_timeout=in_expiration
+ WHERE h_normalized_payto=in_h_payto
+ AND ( (aml_program_lock_timeout IS NULL)
+ OR (aml_program_lock_timeout < in_now) );
+IF NOT FOUND
+THEN
+ SELECT aml_program_lock_timeout
+ INTO out_aml_program_lock_timeout
+ FROM wire_targets
+ WHERE h_normalized_payto=in_h_payto;
+ELSE
+ out_aml_program_lock_timeout = 0;
+END IF;
+END $$;
+COMMENT ON FUNCTION exchange_do_set_aml_lock(BYTEA, INT8, INT8)
+ IS 'Tries to lock an account for running an AML program. Returns the timeout of the existing lock, 0 if there is no existing lock, and NULL if we do not know the account.';
+DROP FUNCTION IF EXISTS exchange_do_insert_sanction_list_hit;
+CREATE FUNCTION exchange_do_insert_sanction_list_hit(
+ IN in_h_normalized_payto BYTEA,
+ IN in_decision_time INT8,
+ IN in_expiration_time INT8,
+ IN in_properties TEXT,
+ IN in_new_rules TEXT,
+ IN in_to_investigate BOOLEAN,
+ IN in_notify_s TEXT,
+ IN ina_events TEXT[],
+ OUT out_outcome_serial_id INT8)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_i INT4;
+ ini_event TEXT;
+BEGIN
+INSERT INTO legitimization_outcomes
+ (h_payto
+ ,decision_time
+ ,expiration_time
+ ,jproperties
+ ,to_investigate
+ ,jnew_rules
+ )
+ VALUES
+ (in_h_normalized_payto
+ ,in_decision_time
+ ,in_expiration_time
+ ,in_properties
+ ,in_to_investigate
+ ,in_new_rules
+ )
+ RETURNING
+ outcome_serial_id
+ INTO
+ out_outcome_serial_id;
+FOR i IN 1..COALESCE(array_length(ina_events,1),0)
+LOOP
+ ini_event = ina_events[i];
+ INSERT INTO kyc_events
+ (event_timestamp
+ ,event_type)
+ VALUES
+ (in_decision_time
+ ,ini_event);
+END LOOP;
+EXECUTE FORMAT (
+ 'NOTIFY %s'
+ ,in_notify_s);
+END $$;
+COMMENT ON FUNCTION exchange_do_insert_sanction_list_hit(BYTEA, INT8, INT8, TEXT, TEXT, BOOLEAN, TEXT, TEXT[])
+ IS 'Insert result from sanction list check into the table';
+SET search_path TO exchange;
+DROP FUNCTION IF EXISTS interval_to_start;
+CREATE OR REPLACE FUNCTION interval_to_start (
+ IN in_timestamp TIMESTAMP,
+ IN in_range statistic_range,
+ OUT out_bucket_start INT8
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ out_bucket_start = EXTRACT(EPOCH FROM DATE_TRUNC(in_range::text, in_timestamp));
+END $$;
+COMMENT ON FUNCTION interval_to_start
+ IS 'computes the start time of the bucket for an event at the current time given the desired bucket range';
+DROP PROCEDURE IF EXISTS exchange_do_bump_number_bucket_stat;
+CREATE OR REPLACE PROCEDURE exchange_do_bump_number_bucket_stat(
+ in_slug TEXT,
+ in_h_payto BYTEA,
+ in_timestamp TIMESTAMP,
+ in_delta INT8
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_meta INT8;
+ my_range statistic_range;
+ my_bucket_start INT8;
+ my_curs CURSOR (arg_slug TEXT)
+ FOR SELECT UNNEST(ranges)
+ FROM exchange_statistic_bucket_meta
+ WHERE slug=arg_slug;
+BEGIN
+ SELECT bmeta_serial_id
+ INTO my_meta
+ FROM exchange_statistic_bucket_meta
+ WHERE slug=in_slug
+ AND stype='number';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ OPEN my_curs (arg_slug:=in_slug);
+ LOOP
+ FETCH NEXT
+ FROM my_curs
+ INTO my_range;
+ EXIT WHEN NOT FOUND;
+ SELECT *
+ INTO my_bucket_start
+ FROM interval_to_start (in_timestamp, my_range);
+ UPDATE exchange_statistic_bucket_counter
+ SET cumulative_number = cumulative_number + in_delta
+ WHERE bmeta_serial_id=my_meta
+ AND h_payto=in_h_payto
+ AND bucket_start=my_bucket_start
+ AND bucket_range=my_range;
+ IF NOT FOUND
+ THEN
+ INSERT INTO exchange_statistic_bucket_counter
+ (bmeta_serial_id
+ ,h_payto
+ ,bucket_start
+ ,bucket_range
+ ,cumulative_number
+ ) VALUES (
+ my_meta
+ ,in_h_payto
+ ,my_bucket_start
+ ,my_range
+ ,in_delta);
+ END IF;
+ END LOOP;
+ CLOSE my_curs;
+END $$;
+DROP PROCEDURE IF EXISTS exchange_do_bump_amount_bucket_stat;
+CREATE OR REPLACE PROCEDURE exchange_do_bump_amount_bucket_stat(
+ in_slug TEXT,
+ in_h_payto BYTEA,
+ in_timestamp TIMESTAMP,
+ in_delta taler_amount
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_meta INT8;
+ my_range statistic_range;
+ my_bucket_start INT8;
+ my_curs CURSOR (arg_slug TEXT)
+ FOR SELECT UNNEST(ranges)
+ FROM exchange_statistic_bucket_meta
+ WHERE slug=arg_slug;
+BEGIN
+ SELECT bmeta_serial_id
+ INTO my_meta
+ FROM exchange_statistic_bucket_meta
+ WHERE slug=in_slug
+ AND stype='amount';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ OPEN my_curs (arg_slug:=in_slug);
+ LOOP
+ FETCH NEXT
+ FROM my_curs
+ INTO my_range;
+ EXIT WHEN NOT FOUND;
+ SELECT *
+ INTO my_bucket_start
+ FROM interval_to_start (in_timestamp, my_range);
+ UPDATE exchange_statistic_bucket_amount
+ SET
+ cumulative_value.val = (cumulative_value).val + (in_delta).val
+ + CASE
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_value.frac = (cumulative_value).frac + (in_delta).frac
+ - CASE
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ WHERE bmeta_serial_id=my_meta
+ AND h_payto=in_h_payto
+ AND bucket_start=my_bucket_start
+ AND bucket_range=my_range;
+ IF NOT FOUND
+ THEN
+ INSERT INTO exchange_statistic_bucket_amount
+ (bmeta_serial_id
+ ,h_payto
+ ,bucket_start
+ ,bucket_range
+ ,cumulative_value
+ ) VALUES (
+ my_meta
+ ,in_h_payto
+ ,my_bucket_start
+ ,my_range
+ ,in_delta);
+ END IF;
+ END LOOP;
+ CLOSE my_curs;
+END $$;
+COMMENT ON PROCEDURE exchange_do_bump_amount_bucket_stat
+ IS 'Updates an amount statistic tracked over buckets';
+DROP PROCEDURE IF EXISTS exchange_do_bump_number_interval_stat;
+CREATE OR REPLACE PROCEDURE exchange_do_bump_number_interval_stat(
+ in_slug TEXT,
+ in_h_payto BYTEA,
+ in_timestamp TIMESTAMP,
+ in_delta INT8
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_now INT8;
+ my_record RECORD;
+ my_meta INT8;
+ my_ranges INT8[];
+ my_precisions INT8[];
+ my_rangex INT8;
+ my_precisionx INT8;
+ my_start INT8;
+ my_event INT8;
+BEGIN
+ my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ SELECT imeta_serial_id
+ ,ranges AS ranges
+ ,precisions AS precisions
+ INTO my_record
+ FROM exchange_statistic_interval_meta
+ WHERE slug=in_slug
+ AND stype='number';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ my_start = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8 / 1000 / 1000;
+ my_precisions = my_record.precisions;
+ my_ranges = my_record.ranges;
+ my_rangex = NULL;
+ FOR my_x IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ IF my_now - my_ranges[my_x] < my_start
+ THEN
+ my_rangex = my_ranges[my_x];
+ my_precisionx = my_precisions[my_x];
+ EXIT;
+ END IF;
+ END LOOP;
+ IF my_rangex IS NULL
+ THEN
+ RETURN;
+ END IF;
+ my_meta = my_record.imeta_serial_id;
+ my_start = my_start - my_start % my_precisionx;
+ INSERT INTO exchange_statistic_counter_event AS msce
+ (imeta_serial_id
+ ,h_payto
+ ,slot
+ ,delta)
+ VALUES
+ (my_meta
+ ,in_h_payto
+ ,my_start
+ ,in_delta)
+ ON CONFLICT (imeta_serial_id, h_payto, slot)
+ DO UPDATE SET
+ delta = msce.delta + in_delta
+ RETURNING nevent_serial_id
+ INTO my_event;
+ UPDATE exchange_statistic_interval_counter
+ SET cumulative_number = cumulative_number + in_delta
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range=my_rangex;
+ IF NOT FOUND
+ THEN
+ INSERT INTO exchange_statistic_interval_counter
+ (imeta_serial_id
+ ,h_payto
+ ,range
+ ,event_delimiter
+ ,cumulative_number
+ ) VALUES (
+ my_meta
+ ,in_h_payto
+ ,my_rangex
+ ,my_event
+ ,in_delta);
+ END IF;
+END $$;
+COMMENT ON PROCEDURE exchange_do_bump_number_interval_stat
+ IS 'Updates a numeric statistic tracked over an interval';
+DROP PROCEDURE IF EXISTS exchange_do_bump_amount_interval_stat;
+CREATE OR REPLACE PROCEDURE exchange_do_bump_amount_interval_stat(
+ in_slug TEXT,
+ in_h_payto BYTEA,
+ in_timestamp TIMESTAMP,
+ in_delta taler_amount
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_now INT8;
+ my_record RECORD;
+ my_meta INT8;
+ my_ranges INT8[];
+ my_precisions INT8[];
+ my_x INT;
+ my_rangex INT8;
+ my_precisionx INT8;
+ my_start INT8;
+ my_event INT8;
+BEGIN
+ my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ INTO my_record
+ FROM exchange_statistic_interval_meta
+ WHERE slug=in_slug
+ AND stype='amount';
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ my_start = ROUND(EXTRACT(epoch FROM in_timestamp) * 1000000)::INT8 / 1000 / 1000;
+ my_precisions = my_record.precisions;
+ my_ranges = my_record.ranges;
+ my_rangex = NULL;
+ FOR my_x IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ IF my_now - my_ranges[my_x] < my_start
+ THEN
+ my_rangex = my_ranges[my_x];
+ my_precisionx = my_precisions[my_x];
+ EXIT;
+ END IF;
+ END LOOP;
+ IF my_rangex IS NULL
+ THEN
+ RETURN;
+ END IF;
+ my_start = my_start - my_start % my_precisionx;
+ my_meta = my_record.imeta_serial_id;
+ INSERT INTO exchange_statistic_amount_event AS msae
+ (imeta_serial_id
+ ,h_payto
+ ,slot
+ ,delta
+ ) VALUES (
+ my_meta
+ ,in_h_payto
+ ,my_start
+ ,in_delta
+ )
+ ON CONFLICT (imeta_serial_id, h_payto, slot)
+ DO UPDATE SET
+ delta.val = (msae.delta).val + (in_delta).val
+ + CASE
+ WHEN (in_delta).frac + (msae.delta).frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ delta.frac = (msae.delta).frac + (in_delta).frac
+ - CASE
+ WHEN (in_delta).frac + (msae.delta).frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ RETURNING aevent_serial_id
+ INTO my_event;
+ UPDATE exchange_statistic_interval_amount
+ SET
+ cumulative_value.val = (cumulative_value).val + (in_delta).val
+ + CASE
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_value.frac = (cumulative_value).frac + (in_delta).frac
+ - CASE
+ WHEN (in_delta).frac + (cumulative_value).frac >= 100000000
+ THEN 100000000
+ ELSE 0
+ END
+ WHERE imeta_serial_id=my_meta
+ AND h_payto=in_h_payto
+ AND range=my_rangex;
+ IF NOT FOUND
+ THEN
+ INSERT INTO exchange_statistic_interval_amount
+ (imeta_serial_id
+ ,h_payto
+ ,range
+ ,event_delimiter
+ ,cumulative_value
+ ) VALUES (
+ my_meta
+ ,in_h_payto
+ ,my_rangex
+ ,my_event
+ ,in_delta);
+ END IF;
+END $$;
+COMMENT ON PROCEDURE exchange_do_bump_amount_interval_stat
+ IS 'Updates an amount statistic tracked over an interval';
+DROP PROCEDURE IF EXISTS exchange_do_bump_number_stat;
+CREATE OR REPLACE PROCEDURE exchange_do_bump_number_stat(
+ in_slug TEXT,
+ in_h_payto BYTEA,
+ in_timestamp TIMESTAMP,
+ in_delta INT8
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL exchange_do_bump_number_bucket_stat (in_slug, in_h_payto, in_timestamp, in_delta);
+ CALL exchange_do_bump_number_interval_stat (in_slug, in_h_payto, in_timestamp, in_delta);
+END $$;
+COMMENT ON PROCEDURE exchange_do_bump_number_stat
+ IS 'Updates a numeric statistic (bucket or interval)';
+DROP PROCEDURE IF EXISTS exchange_do_bump_amount_stat;
+CREATE OR REPLACE PROCEDURE exchange_do_bump_amount_stat(
+ in_slug TEXT,
+ in_h_payto BYTEA,
+ in_timestamp TIMESTAMP,
+ in_delta taler_amount
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL exchange_do_bump_amount_bucket_stat (in_slug, in_h_payto, in_timestamp, in_delta);
+ CALL exchange_do_bump_amount_interval_stat (in_slug, in_h_payto, in_timestamp, in_delta);
+END $$;
+COMMENT ON PROCEDURE exchange_do_bump_amount_stat
+ IS 'Updates an amount statistic (bucket or interval)';
+DROP FUNCTION IF EXISTS exchange_statistic_interval_number_get;
+CREATE OR REPLACE FUNCTION exchange_statistic_interval_number_get (
+ IN in_slug TEXT,
+ IN in_h_payto BYTEA
+)
+RETURNS SETOF exchange_statistic_interval_number_get_return_value
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_ranges INT8[];
+ my_range INT8;
+ my_delta INT8;
+ my_meta INT8;
+ my_next_max_serial INT8;
+ my_rec RECORD;
+ my_irec RECORD;
+ my_i INT;
+ my_min_serial INT8 DEFAULT NULL;
+ my_rval exchange_statistic_interval_number_get_return_value;
+BEGIN
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ INTO my_rec
+ FROM exchange_statistic_interval_meta
+ WHERE slug=in_slug;
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ my_rval.rvalue = 0;
+ my_ranges = my_rec.ranges;
+ my_meta = my_rec.imeta_serial_id;
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ my_range = my_ranges[my_i];
+ SELECT event_delimiter
+ ,cumulative_number
+ INTO my_irec
+ FROM exchange_statistic_interval_counter
+ WHERE imeta_serial_id = my_meta
+ AND range = my_range
+ AND h_payto = in_h_payto;
+ IF FOUND
+ THEN
+ my_min_serial = my_irec.event_delimiter;
+ my_rval.rvalue = my_rval.rvalue + my_irec.cumulative_number;
+ SELECT SUM(delta) AS delta_sum
+ INTO my_irec
+ FROM exchange_statistic_counter_event
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND slot < my_time - my_range
+ AND nevent_serial_id >= my_min_serial;
+ IF FOUND AND my_irec.delta_sum IS NOT NULL
+ THEN
+ my_delta = my_irec.delta_sum;
+ my_rval.rvalue = my_rval.rvalue - my_delta;
+ SELECT nevent_serial_id
+ INTO my_next_max_serial
+ FROM exchange_statistic_counter_event
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND slot >= my_time - my_range
+ AND nevent_serial_id >= my_min_serial
+ ORDER BY slot ASC
+ LIMIT 1;
+ IF FOUND
+ THEN
+ UPDATE exchange_statistic_interval_counter
+ SET cumulative_number = cumulative_number - my_delta,
+ event_delimiter = my_next_max_serial
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range = my_range;
+ ELSE
+ DELETE FROM exchange_statistic_interval_counter
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range = my_range;
+ END IF;
+ IF (my_i < array_length(my_ranges,1))
+ THEN
+ UPDATE exchange_statistic_interval_counter AS usic SET
+ cumulative_number = cumulative_number + my_delta,
+ event_delimiter = LEAST(usic.event_delimiter,my_min_serial)
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range=my_ranges[my_i+1];
+ IF NOT FOUND
+ THEN
+ INSERT INTO exchange_statistic_interval_counter
+ (imeta_serial_id
+ ,h_payto
+ ,range
+ ,event_delimiter
+ ,cumulative_number
+ ) VALUES (
+ my_meta
+ ,in_h_payto
+ ,my_ranges[my_i+1]
+ ,my_min_serial
+ ,my_delta);
+ END IF;
+ ELSE
+ DELETE FROM exchange_statistic_counter_event
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND slot < my_time - my_range;
+ END IF;
+ END IF;
+ my_rval.range = my_range;
+ RETURN NEXT my_rval;
+ END IF;
+ END LOOP;
+END $$;
+COMMENT ON FUNCTION exchange_statistic_interval_number_get
+ IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value for each range';
+DROP FUNCTION IF EXISTS exchange_statistic_interval_amount_get;
+CREATE OR REPLACE FUNCTION exchange_statistic_interval_amount_get (
+ IN in_slug TEXT,
+ IN in_h_payto BYTEA
+)
+RETURNS SETOF exchange_statistic_interval_amount_get_return_value
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_ranges INT8[];
+ my_range INT8;
+ my_delta_value INT8;
+ my_delta_frac INT8;
+ my_delta taler_amount;
+ my_meta INT8;
+ my_next_max_serial INT8;
+ my_rec RECORD;
+ my_irec RECORD;
+ my_jrec RECORD;
+ my_i INT;
+ my_min_serial INT8 DEFAULT NULL;
+ my_rval exchange_statistic_interval_amount_get_return_value;
+BEGIN
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ INTO my_rec
+ FROM exchange_statistic_interval_meta
+ WHERE slug=in_slug;
+ IF NOT FOUND
+ THEN
+ RETURN;
+ END IF;
+ my_meta = my_rec.imeta_serial_id;
+ my_ranges = my_rec.ranges;
+ my_rval.rvalue.val = 0;
+ my_rval.rvalue.frac = 0;
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ my_range = my_ranges[my_i];
+ SELECT event_delimiter
+ ,cumulative_value
+ INTO my_irec
+ FROM exchange_statistic_interval_amount
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range = my_range;
+ IF FOUND
+ THEN
+ my_min_serial = my_irec.event_delimiter;
+ my_rval.rvalue.val = (my_rval.rvalue).val + (my_irec.cumulative_value).val + (my_irec.cumulative_value).frac / 100000000;
+ my_rval.rvalue.frac = (my_rval.rvalue).frac + (my_irec.cumulative_value).frac % 100000000;
+ IF (my_rval.rvalue).frac > 100000000
+ THEN
+ my_rval.rvalue.frac = (my_rval.rvalue).frac - 100000000;
+ my_rval.rvalue.val = (my_rval.rvalue).val + 1;
+ END IF;
+ SELECT SUM((esae.delta).val) AS value_sum
+ ,SUM((esae.delta).frac) AS frac_sum
+ INTO my_jrec
+ FROM exchange_statistic_amount_event esae
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND slot < my_time - my_range
+ AND aevent_serial_id >= my_min_serial;
+ IF FOUND AND my_jrec.value_sum IS NOT NULL
+ THEN
+ my_delta_value = my_jrec.value_sum + my_jrec.frac_sum / 100000000;
+ my_delta_frac = my_jrec.frac_sum % 100000000;
+ my_rval.rvalue.val = (my_rval.rvalue).val - my_delta_value;
+ IF ((my_rval.rvalue).frac >= my_delta_frac)
+ THEN
+ my_rval.rvalue.frac = (my_rval.rvalue).frac - my_delta_frac;
+ ELSE
+ my_rval.rvalue.frac = 100000000 + (my_rval.rvalue).frac - my_delta_frac;
+ my_rval.rvalue.val = (my_rval.rvalue).val - 1;
+ END IF;
+ SELECT aevent_serial_id
+ INTO my_next_max_serial
+ FROM exchange_statistic_amount_event
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND slot >= my_time - my_range
+ AND aevent_serial_id >= my_min_serial
+ ORDER BY slot ASC
+ LIMIT 1;
+ IF FOUND
+ THEN
+ UPDATE exchange_statistic_interval_amount SET
+ cumulative_value.val = (cumulative_value).val - my_delta_value
+ - CASE
+ WHEN (cumulative_value).frac < my_delta_frac
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_value.frac = (cumulative_value).frac - my_delta_frac
+ + CASE
+ WHEN (cumulative_value).frac < my_delta_frac
+ THEN 100000000
+ ELSE 0
+ END,
+ event_delimiter = my_next_max_serial
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range = my_range;
+ ELSE
+ DELETE FROM exchange_statistic_interval_amount
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range = my_range;
+ END IF;
+ IF (my_i < array_length(my_ranges,1))
+ THEN
+ UPDATE exchange_statistic_interval_amount AS msia SET
+ cumulative_value.val = (cumulative_value).val + my_delta_value
+ + CASE
+ WHEN (cumulative_value).frac + my_delta_frac > 100000000
+ THEN 1
+ ELSE 0
+ END,
+ cumulative_value.frac = (cumulative_value).frac + my_delta_value
+ - CASE
+ WHEN (cumulative_value).frac + my_delta_frac > 100000000
+ THEN 100000000
+ ELSE 0
+ END,
+ event_delimiter = LEAST (msia.event_delimiter,my_min_serial)
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND range=my_ranges[my_i+1];
+ IF NOT FOUND
+ THEN
+ my_delta.val = my_delta_value;
+ my_delta.frac = my_delta_frac;
+ INSERT INTO exchange_statistic_interval_amount
+ (imeta_serial_id
+ ,h_payto
+ ,event_delimiter
+ ,range
+ ,cumulative_value
+ ) VALUES (
+ my_meta
+ ,in_h_payto
+ ,my_min_serial
+ ,my_ranges[my_i+1]
+ ,my_delta);
+ END IF;
+ ELSE
+ DELETE FROM exchange_statistic_amount_event
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = in_h_payto
+ AND slot < my_time - my_range;
+ END IF;
+ END IF;
+ my_rval.range = my_range;
+ RETURN NEXT my_rval;
+ END IF;
+ END LOOP;
+END $$;
+COMMENT ON FUNCTION exchange_statistic_interval_amount_get
+ IS 'Returns deposit statistic tracking deposited amounts over certain time intervals; we first trim the stored data to only track what is still in-range, and then return the remaining value; multiple values are returned, one per range';
+DROP PROCEDURE IF EXISTS exchange_statistic_counter_gc;
+CREATE OR REPLACE PROCEDURE exchange_statistic_counter_gc ()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_h_payto BYTEA;
+ my_rec RECORD;
+ my_sum RECORD;
+ my_meta INT8;
+ my_ranges INT8[];
+ my_precisions INT8[];
+ my_precision INT4;
+ my_i INT4;
+ min_slot INT8;
+ max_slot INT8;
+ end_slot INT8;
+ my_total INT8;
+BEGIN
+ FOR my_h_payto IN
+ SELECT DISTINCT h_payto
+ FROM exchange_statistic_counter_event
+ LOOP
+ FOR my_rec IN
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ ,slug
+ FROM exchange_statistic_interval_meta
+ LOOP
+ PERFORM FROM exchange_statistic_interval_number_get (my_rec.slug, my_h_payto);
+ my_meta = my_rec.imeta_serial_id;
+ my_ranges = my_rec.ranges;
+ my_precisions = my_rec.precisions;
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ my_precision = my_precisions[my_i];
+ IF 1 >= my_precision
+ THEN
+ CONTINUE;
+ END IF;
+ IF 1 = my_i
+ THEN
+ min_slot = 0;
+ ELSE
+ min_slot = my_ranges[my_i - 1];
+ END IF;
+ end_slot = my_ranges[my_i];
+ LOOP
+ EXIT WHEN min_slot >= end_slot;
+ max_slot = min_slot + my_precision;
+ SELECT SUM(delta) AS total,
+ COUNT(*) AS matches,
+ MIN(nevent_serial_id) AS rep_serial_id
+ INTO my_sum
+ FROM exchange_statistic_counter_event
+ WHERE h_payto=my_h_payto
+ AND imeta_serial_id=my_meta
+ AND slot >= my_time - max_slot
+ AND slot < my_time - min_slot;
+ IF FOUND AND my_sum.matches > 1
+ THEN
+ my_total = my_sum.total;
+ DELETE FROM exchange_statistic_counter_event
+ WHERE h_payto=my_h_payto
+ AND imeta_serial_id=my_meta
+ AND slot >= my_time - max_slot
+ AND slot < my_time - min_slot
+ AND nevent_serial_id > my_sum.rep_serial_id;
+ UPDATE exchange_statistic_counter_event SET
+ delta = my_total
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = my_h_payto
+ AND nevent_serial_id = my_sum.rep_serial_id;
+ END IF;
+ min_slot = min_slot + my_precision;
+ END LOOP;
+ END LOOP;
+ DELETE FROM exchange_statistic_counter_event
+ WHERE h_payto=my_h_payto
+ AND imeta_serial_id=my_meta
+ AND slot < my_time - my_ranges[array_length(my_ranges,1)];
+ END LOOP;
+ END LOOP;
+END $$;
+COMMENT ON PROCEDURE exchange_statistic_counter_gc
+ IS 'Performs garbage collection and compaction of the exchange_statistic_counter_event table';
+DROP PROCEDURE IF EXISTS exchange_statistic_amount_gc;
+CREATE OR REPLACE PROCEDURE exchange_statistic_amount_gc ()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_time INT8 DEFAULT ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP) * 1000000)::INT8 / 1000 / 1000;
+ my_h_payto BYTEA;
+ my_rec RECORD;
+ my_sum RECORD;
+ my_meta INT8;
+ my_ranges INT8[];
+ my_precisions INT8[];
+ my_precision INT4;
+ my_i INT4;
+ min_slot INT8;
+ max_slot INT8;
+ end_slot INT8;
+ my_total_val INT8;
+ my_total_frac INT8;
+BEGIN
+ FOR my_h_payto IN
+ SELECT DISTINCT h_payto
+ FROM exchange_statistic_counter_event
+ LOOP
+ FOR my_rec IN
+ SELECT imeta_serial_id
+ ,ranges
+ ,precisions
+ ,slug
+ FROM exchange_statistic_interval_meta
+ LOOP
+ PERFORM FROM exchange_statistic_interval_amount_get (my_rec.slug, my_h_payto);
+ my_meta = my_rec.imeta_serial_id;
+ my_ranges = my_rec.ranges;
+ my_precisions = my_rec.precisions;
+ FOR my_i IN 1..COALESCE(array_length(my_ranges,1),0)
+ LOOP
+ my_precision = my_precisions[my_i];
+ IF 1 >= my_precision
+ THEN
+ CONTINUE;
+ END IF;
+ IF 1 = my_i
+ THEN
+ min_slot = 0;
+ ELSE
+ min_slot = my_ranges[my_i - 1];
+ END IF;
+ end_slot = my_ranges[my_i];
+ LOOP
+ EXIT WHEN min_slot >= end_slot;
+ max_slot = min_slot + my_precision;
+ SELECT SUM((delta).val) AS total_val,
+ SUM((delta).frac) AS total_frac,
+ COUNT(*) AS matches,
+ MIN(aevent_serial_id) AS rep_serial_id
+ INTO my_sum
+ FROM exchange_statistic_amount_event
+ WHERE imeta_serial_id=my_meta
+ AND h_payto=my_h_payto
+ AND slot >= my_time - max_slot
+ AND slot < my_time - max_slot;
+ IF FOUND AND my_sum.matches > 1
+ THEN
+ my_total_frac = my_sum.total_frac % 100000000;
+ my_total_val = my_sum.total_val + my_sum.total_frac / 100000000;
+ DELETE FROM exchange_statistic_amount_event
+ WHERE imeta_serial_id=my_meta
+ AND h_payto=my_h_payto
+ AND slot >= my_time - max_slot
+ AND slot < my_time - max_slot
+ AND aevent_serial_id > my_sum.rep_serial_id;
+ UPDATE exchange_statistic_amount_event SET
+ delta.val = my_total_value
+ ,delta.frac = my_total_frac
+ WHERE imeta_serial_id = my_meta
+ AND h_payto = my_h_payto
+ AND aevent_serial_id = my_sum.rep_serial_id;
+ END IF;
+ min_slot = min_slot + my_precision;
+ END LOOP;
+ END LOOP;
+ DELETE FROM exchange_statistic_amount_event
+ WHERE h_payto=my_h_payto
+ AND imeta_serial_id=my_meta
+ AND slot < my_time - my_ranges[array_length(my_ranges,1)];
+ END LOOP;
+ END LOOP;
+END $$;
+COMMENT ON PROCEDURE exchange_statistic_amount_gc
+ IS 'Performs garbage collection and compaction of the exchange_statistic_amount_event table';
+DROP PROCEDURE IF EXISTS exchange_statistic_bucket_gc;
+CREATE OR REPLACE PROCEDURE exchange_statistic_bucket_gc ()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_rec RECORD;
+ my_range TEXT;
+ my_now INT8;
+ my_end INT8;
+BEGIN
+ my_now = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP(0)::TIMESTAMP);
+ FOR my_rec IN
+ SELECT bmeta_serial_id
+ ,stype
+ ,ranges[array_length(ranges,1)] AS range
+ ,ages[array_length(ages,1)] AS age
+ FROM exchange_statistic_bucket_meta
+ LOOP
+ my_range = '1 ' || my_rec.range::TEXT;
+ my_end = my_now - my_rec.age * EXTRACT(SECONDS FROM (SELECT my_range::INTERVAL));
+ IF my_rec.stype = 'amount'
+ THEN
+ DELETE
+ FROM exchange_statistic_bucket_amount
+ WHERE bmeta_serial_id = my_rec.bmeta_serial_id
+ AND bucket_start >= my_end;
+ ELSE
+ DELETE
+ FROM exchange_statistic_bucket_counter
+ WHERE bmeta_serial_id = my_rec.bmeta_serial_id
+ AND bucket_start >= my_end;
+ END IF;
+ END LOOP;
+END $$;
+COMMENT ON PROCEDURE exchange_statistic_bucket_gc
+ IS 'Performs garbage collection of the exchange_statistic_bucket_counter and exchange_statistic_bucket_amount tables';
+DROP FUNCTION IF EXISTS exchange_drop_customization;
+CREATE OR REPLACE FUNCTION exchange_drop_customization (
+ IN in_schema TEXT,
+ OUT out_found BOOLEAN
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ my_xpatches TEXT;
+BEGIN
+ out_found = FALSE;
+ FOR my_xpatches IN
+ SELECT patch_name
+ FROM _v.patches
+ WHERE starts_with(patch_name, in_schema || '-')
+ LOOP
+ PERFORM _v.unregister_patch(my_xpatches);
+ out_found = TRUE;
+ END LOOP;
+ IF out_found
+ THEN
+ EXECUTE FORMAT('DROP SCHEMA %s CASCADE'
+ ,in_schema);
+ END IF;
+ DELETE
+ FROM exchange_statistic_interval_meta
+ WHERE origin=in_schema;
+ DELETE
+ FROM exchange_statistic_bucket_meta
+ WHERE origin=in_schema;
+END $$;
+COMMENT ON FUNCTION exchange_drop_customization
+ IS 'Removes all entries related to a particular exchange customization schema';
+CREATE OR REPLACE FUNCTION purse_requests_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO
+ exchange.purse_actions
+ (purse_pub
+ ,action_date)
+ VALUES
+ (NEW.purse_pub
+ ,NEW.purse_expiration);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION purse_requests_insert_trigger()
+ IS 'When a purse is created, insert it into the purse_action table to take action when the purse expires.';
+CREATE OR REPLACE FUNCTION reserves_out_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO exchange.reserve_history
+ (reserve_pub
+ ,table_name
+ ,serial_id)
+ SELECT
+ res.reserve_pub
+ ,'reserves_out'
+ ,NEW.reserve_out_serial_id
+ FROM
+ exchange.reserves res
+ WHERE res.reserve_uuid = NEW.reserve_uuid;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_out_insert_trigger()
+ IS 'Replicate reserve_out inserts into reserve_history table.';
+CREATE OR REPLACE FUNCTION reserves_in_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO exchange.reserve_history
+ (reserve_pub
+ ,table_name
+ ,serial_id)
+ VALUES
+ (NEW.reserve_pub
+ ,'reserves_in'
+ ,NEW.reserve_in_serial_id);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_in_insert_trigger()
+ IS 'Automatically generate reserve history entry.';
+CREATE OR REPLACE FUNCTION purse_decision_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ UPDATE exchange.purse_requests
+ SET was_decided=TRUE
+ WHERE purse_pub=NEW.purse_pub;
+ IF NEW.refunded
+ THEN
+ INSERT INTO exchange.coin_history
+ (coin_pub
+ ,table_name
+ ,serial_id)
+ SELECT
+ pd.coin_pub
+ ,'purse_decision'
+ ,NEW.purse_decision_serial_id
+ FROM exchange.purse_deposits pd
+ WHERE purse_pub = NEW.purse_pub;
+ ELSE
+ INSERT INTO exchange.reserve_history
+ (reserve_pub
+ ,table_name
+ ,serial_id)
+ SELECT
+ reserve_pub
+ ,'purse_decision'
+ ,NEW.purse_decision_serial_id
+ FROM exchange.purse_merges
+ WHERE purse_pub=NEW.purse_pub;
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION purse_decision_insert_trigger()
+ IS 'Automatically generate coin history entry and update decision status for the purse.';
+DROP PROCEDURE IF EXISTS exchange_do_gc;
+CREATE PROCEDURE exchange_do_gc(
+ IN in_ancient_date INT8,
+ IN in_now INT8)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ CALL exchange_do_main_gc(in_ancient_date,in_now);
+ CALL exchange_statistic_amount_gc ();
+ CALL exchange_statistic_bucket_gc ();
+ CALL exchange_statistic_counter_gc ();
+END $$;
+COMMENT ON PROCEDURE exchange_do_gc
+ IS 'calls all other garbage collection subroutines';
+COMMIT;
diff --git a/migration-v0-v1/retry-migrate-db-v0-v1.sh b/migration-v0-v1/retry-migrate-db-v0-v1.sh
@@ -0,0 +1,50 @@
+#!/bin/sh
+# This file is in the public domain.
+# Call with exchange configuration file as the exchange user (with DB access).
+set -eu
+CONF=${1:-/etc/taler-exchange/taler-exchange.conf}
+DB=$(taler-exchange-config -c "$CONF" -s exchangedb-postgres -o config)
+DBNAME=$(echo "$DB" | awk -F / '{print $4}')
+DIR=$2
+echo "Migrating to new schema"
+taler-exchange-dbinit -c "$CONF" -r
+
+for t in $(echo "
+exchange.cs_nonce_locks
+exchange.denominations
+exchange.exchange_sign_keys
+exchange.global_fee
+exchange.known_coins
+exchange.prewire
+exchange.wire_accounts
+exchange.wire_targets
+exchange.reserves
+exchange.batch_deposits
+exchange.aggregation_tracking
+exchange.aggregation_transient
+exchange.close_requests
+exchange.coin_deposits
+exchange.contracts
+exchange.history_requests
+exchange.purse_requests
+exchange.purse_decision
+exchange.purse_deletion
+exchange.purse_deposits
+exchange.purse_merges
+exchange.refunds
+exchange.account_merges
+exchange.reserves_close
+exchange.reserves_in
+exchange.reserves_open_deposits
+exchange.reserves_open_requests
+exchange.reserves_out
+exchange.revolving_work_shards
+exchange.wire_fee
+exchange.wire_out
+exchange.work_shards")
+do
+ echo "Restoring Table $t"
+ psql --set="search_path=exchange" "$DB" < "$DIR/$t.sql"
+done
+echo "Done"
+exit 0
diff --git a/retry-migrate-db-v0-v1.sh b/retry-migrate-db-v0-v1.sh
@@ -1,51 +0,0 @@
-#!/bin/sh
-# This file is in the public domain.
-# Call with exchange configuration file as the exchange user (with DB access).
-set -eu
-CONF=${1:-/etc/taler-exchange/taler-exchange.conf}
-DB=$(taler-exchange-config -c "$CONF" -s exchangedb-postgres -o config)
-DBNAME=$(echo "$DB" | awk -F / '{print $4}')
-DIR=$2
-echo "Migrating to new schema"
-taler-exchange-dbinit -c "$CONF" -r
-
-for t in $(echo "
-exchange.cs_nonce_locks
-exchange.denominations
-exchange.exchange_sign_keys
-exchange.global_fee
-exchange.known_coins
-exchange.prewire
-exchange.wire_accounts
-exchange.wire_targets
-exchange.reserves
-exchange.batch_deposits
-exchange.aggregation_tracking
-exchange.aggregation_transient
-exchange.close_requests
-exchange.coin_deposits
-exchange.contracts
-exchange.history_requests
-exchange.purse_actions
-exchange.purse_requests
-exchange.purse_decision
-exchange.purse_deletion
-exchange.purse_deposits
-exchange.purse_merges
-exchange.refunds
-exchange.account_merges
-exchange.reserves_close
-exchange.reserves_in
-exchange.reserves_open_deposits
-exchange.reserves_open_requests
-exchange.reserves_out
-exchange.revolving_work_shards
-exchange.wire_fee
-exchange.wire_out
-exchange.work_shards")
-do
- echo "Restoring Table $t"
- psql --set="search_path=exchange" "$DB" < "$DIR/$t.sql"
-done
-echo "Done"
-exit 0
