diff options
Diffstat (limited to 'src/backenddb/merchant-0001.sql')
| -rw-r--r-- | src/backenddb/merchant-0001.sql | 105 |
1 files changed, 45 insertions, 60 deletions
diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql index 7bd8d737..e959a071 100644 --- a/src/backenddb/merchant-0001.sql +++ b/src/backenddb/merchant-0001.sql @@ -30,6 +30,14 @@ COMMENT ON SCHEMA merchant IS 'taler-merchant data'; SET search_path TO merchant; +CREATE TYPE taler_amount + AS + (val INT8 + ,frac INT4 + ); +COMMENT ON TYPE taler_amount + IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; + ---------------- Exchange information --------------------------- CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees @@ -38,10 +46,8 @@ CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees ,h_wire_method BYTEA NOT NULL CHECK (LENGTH(h_wire_method)=64) ,start_date INT8 NOT NULL ,end_date INT8 NOT NULL - ,wire_fee_val INT8 NOT NULL - ,wire_fee_frac INT4 NOT NULL - ,closing_fee_val INT8 NOT NULL - ,closing_fee_frac INT4 NOT NULL + ,wire_fee taler_amount NOT NULL + ,closing_fee taler_amount NOT NULL ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) ,UNIQUE (master_pub,h_wire_method,start_date) ); @@ -80,10 +86,8 @@ CREATE TABLE IF NOT EXISTS merchant_instances ,logo BYTEA ,address BYTEA NOT NULL ,jurisdiction BYTEA NOT NULL - ,default_max_deposit_fee_val INT8 NOT NULL - ,default_max_deposit_fee_frac INT4 NOT NULL - ,default_max_wire_fee_val INT8 NOT NULL - ,default_max_wire_fee_frac INT4 NOT NULL + ,default_max_deposit_fee taler_amount NOT NULL + ,default_max_wire_fee taler_amount NOT NULL ,default_wire_fee_amortization INT4 NOT NULL ,default_wire_transfer_delay INT8 NOT NULL ,default_pay_delay INT8 NOT NULL @@ -166,8 +170,7 @@ CREATE TABLE IF NOT EXISTS merchant_inventory ,unit VARCHAR NOT NULL ,image BYTEA NOT NULL ,taxes BYTEA NOT NULL - ,price_val INT8 NOT NULL - ,price_frac INT4 NOT NULL + ,price taler_amount NOT NULL ,total_stock BIGINT NOT NULL ,total_sold BIGINT NOT NULL DEFAULT 0 ,total_lost BIGINT NOT NULL DEFAULT 0 @@ -188,7 +191,7 @@ COMMENT ON COLUMN merchant_inventory.image IS 'NOT NULL, but can be 0 bytes; must contain an ImageDataUrl'; COMMENT ON COLUMN merchant_inventory.taxes IS 'JSON array containing taxes the merchant pays, must be JSON, but can be just "[]"'; -COMMENT ON COLUMN merchant_inventory.price_val +COMMENT ON COLUMN merchant_inventory.price IS 'Current price of one unit of the product'; COMMENT ON COLUMN merchant_inventory.total_stock IS 'A value of -1 is used for unlimited (electronic good), may never be lowered'; @@ -358,14 +361,10 @@ CREATE TABLE IF NOT EXISTS merchant_deposits ,deposit_timestamp INT8 NOT NULL ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) ,exchange_url VARCHAR NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,deposit_fee_val INT8 NOT NULL - ,deposit_fee_frac INT4 NOT NULL - ,refund_fee_val INT8 NOT NULL - ,refund_fee_frac INT4 NOT NULL - ,wire_fee_val INT8 NOT NULL - ,wire_fee_frac INT4 NOT NULL + ,amount_with_fee taler_amount NOT NULL + ,deposit_fee taler_amount NOT NULL + ,refund_fee taler_amount NOT NULL + ,wire_fee taler_amount NOT NULL ,signkey_serial BIGINT NOT NULL REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) @@ -381,7 +380,7 @@ COMMENT ON COLUMN merchant_deposits.deposit_timestamp IS 'Time when the exchange generated the deposit confirmation'; COMMENT ON COLUMN merchant_deposits.exchange_sig IS 'Signature of the exchange over the deposit confirmation'; -COMMENT ON COLUMN merchant_deposits.wire_fee_val +COMMENT ON COLUMN merchant_deposits.wire_fee IS 'We MAY want to see if we should try to get this via merchant_exchange_wire_fees (not sure, may be too complicated with the date range, etc.)'; CREATE TABLE IF NOT EXISTS merchant_refunds @@ -392,8 +391,7 @@ CREATE TABLE IF NOT EXISTS merchant_refunds ,refund_timestamp INT8 NOT NULL ,coin_pub BYTEA NOT NULL ,reason VARCHAR NOT NULL - ,refund_amount_val INT8 NOT NULL - ,refund_amount_frac INT4 NOT NULL + ,refund_amount taler_amount NOT NULL ,UNIQUE (order_serial, coin_pub, rtransaction_id) ); COMMENT ON TABLE merchant_deposits @@ -422,8 +420,7 @@ CREATE TABLE IF NOT EXISTS merchant_transfers (credit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,exchange_url VARCHAR NOT NULL ,wtid BYTEA CHECK (LENGTH(wtid)=32) - ,credit_amount_val INT8 NOT NULL - ,credit_amount_frac INT4 NOT NULL + ,credit_amount taler_amount NOT NULL ,account_serial INT8 NOT NULL REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE ,ready_time INT8 NOT NULL DEFAULT (0) @@ -439,7 +436,7 @@ COMMENT ON COLUMN merchant_transfers.verified IS 'true once we got an acceptable response from the exchange for this transfer'; COMMENT ON COLUMN merchant_transfers.confirmed IS 'true once the merchant confirmed that this transfer was received'; -COMMENT ON COLUMN merchant_transfers.credit_amount_val +COMMENT ON COLUMN merchant_transfers.credit_amount IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the merchant'; COMMENT ON COLUMN merchant_transfers.failed IS 'set to true on permanent verification failures'; @@ -459,10 +456,8 @@ CREATE TABLE IF NOT EXISTS merchant_transfer_signatures REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE ,signkey_serial BIGINT NOT NULL REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE - ,wire_fee_val INT8 NOT NULL - ,wire_fee_frac INT4 NOT NULL - ,credit_amount_val INT8 NOT NULL - ,credit_amount_frac INT4 NOT NULL + ,wire_fee taler_amount NOT NULL + ,credit_amount taler_amount NOT NULL ,execution_time INT8 NOT NULL ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) ); @@ -470,7 +465,7 @@ COMMENT ON TABLE merchant_transfer_signatures IS 'table represents the main information returned from the /transfer request to the exchange.'; COMMENT ON COLUMN merchant_transfer_signatures.execution_time IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant'; -COMMENT ON COLUMN merchant_transfer_signatures.credit_amount_val +COMMENT ON COLUMN merchant_transfer_signatures.credit_amount IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the exchange'; @@ -480,26 +475,23 @@ CREATE TABLE IF NOT EXISTS merchant_transfer_to_coin ,credit_serial BIGINT NOT NULL REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE ,offset_in_exchange_list INT8 NOT NULL - ,exchange_deposit_value_val INT8 NOT NULL - ,exchange_deposit_value_frac INT4 NOT NULL - ,exchange_deposit_fee_val INT8 NOT NULL - ,exchange_deposit_fee_frac INT4 NOT NULL + ,exchange_deposit_value taler_amount NOT NULL + ,exchange_deposit_fee taler_amount NOT NULL ); CREATE INDEX IF NOT EXISTS merchant_transfers_by_credit ON merchant_transfer_to_coin (credit_serial); COMMENT ON TABLE merchant_transfer_to_coin IS 'Mapping of (credit) transfers to (deposited) coins'; -COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_value_val +COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_value IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits minus refunds'; -COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_fee_val +COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_fee IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits'; CREATE TABLE IF NOT EXISTS merchant_deposit_to_transfer (deposit_serial BIGINT NOT NULL REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE - ,coin_contribution_value_val INT8 NOT NULL - ,coin_contribution_value_frac INT4 NOT NULL + ,coin_contribution_value taler_amount NOT NULL ,credit_serial BIGINT NOT NULL REFERENCES merchant_transfers (credit_serial) ,execution_time INT8 NOT NULL @@ -523,26 +515,22 @@ CREATE TABLE IF NOT EXISTS merchant_reward_reserves REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE ,creation_time INT8 NOT NULL ,expiration INT8 NOT NULL - ,merchant_initial_balance_val INT8 NOT NULL - ,merchant_initial_balance_frac INT4 NOT NULL - ,exchange_initial_balance_val INT8 NOT NULL DEFAULT 0 - ,exchange_initial_balance_frac INT4 NOT NULL DEFAULT 0 - ,rewards_committed_val INT8 NOT NULL DEFAULT 0 - ,rewards_committed_frac INT4 NOT NULL DEFAULT 0 - ,rewards_picked_up_val INT8 NOT NULL DEFAULT 0 - ,rewards_picked_up_frac INT4 NOT NULL DEFAULT 0 + ,merchant_initial_balance taler_amount NOT NULL + ,exchange_initial_balance taler_amount NOT NULL DEFAULT (0,0) + ,rewards_committed taler_amount NOT NULL DEFAULT (0,0) + ,rewards_picked_up taler_amount NOT NULL DEFAULT (0,0) ); COMMENT ON TABLE merchant_reward_reserves IS 'balances of the reserves available for rewards'; COMMENT ON COLUMN merchant_reward_reserves.expiration IS 'FIXME: EXCHANGE API needs to tell us when reserves close if we are to compute this'; -COMMENT ON COLUMN merchant_reward_reserves.merchant_initial_balance_val +COMMENT ON COLUMN merchant_reward_reserves.merchant_initial_balance IS 'Set to the initial balance the merchant told us when creating the reserve'; -COMMENT ON COLUMN merchant_reward_reserves.exchange_initial_balance_val +COMMENT ON COLUMN merchant_reward_reserves.exchange_initial_balance IS 'Set to the initial balance the exchange told us when we queried the reserve status'; -COMMENT ON COLUMN merchant_reward_reserves.rewards_committed_val +COMMENT ON COLUMN merchant_reward_reserves.rewards_committed IS 'Amount of outstanding approved rewards that have not been picked up'; -COMMENT ON COLUMN merchant_reward_reserves.rewards_picked_up_val +COMMENT ON COLUMN merchant_reward_reserves.rewards_picked_up IS 'Total amount rewards that have been picked up from this reserve'; CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_reserve_pub_and_merchant_serial @@ -553,7 +541,7 @@ CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_merchant_serial_and_creat (merchant_serial,creation_time); CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_exchange_balance ON merchant_reward_reserves - (exchange_initial_balance_val,exchange_initial_balance_frac); + (exchange_initial_balance); @@ -578,10 +566,8 @@ CREATE TABLE IF NOT EXISTS merchant_rewards ,justification VARCHAR NOT NULL ,next_url VARCHAR NOT NULL ,expiration INT8 NOT NULL - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,picked_up_val INT8 NOT NULL DEFAULT 0 - ,picked_up_frac INT4 NOT NULL DEFAULT 0 + ,amount taler_amount NOT NULL + ,picked_up taler_amount NOT NULL DEFAULT (0, 0) ,was_picked_up BOOLEAN NOT NULL DEFAULT FALSE ); CREATE INDEX IF NOT EXISTS merchant_rewards_by_pickup_and_expiration @@ -589,9 +575,9 @@ CREATE INDEX IF NOT EXISTS merchant_rewards_by_pickup_and_expiration (was_picked_up,expiration); COMMENT ON TABLE merchant_rewards IS 'rewards that have been authorized'; -COMMENT ON COLUMN merchant_rewards.amount_val +COMMENT ON COLUMN merchant_rewards.amount IS 'Overall reward amount'; -COMMENT ON COLUMN merchant_rewards.picked_up_val +COMMENT ON COLUMN merchant_rewards.picked_up IS 'Reward amount left to be picked up'; COMMENT ON COLUMN merchant_rewards.reserve_serial IS 'Reserve from which this reward is funded'; @@ -603,12 +589,11 @@ CREATE TABLE IF NOT EXISTS merchant_reward_pickups ,reward_serial BIGINT NOT NULL REFERENCES merchant_rewards (reward_serial) ON DELETE CASCADE ,pickup_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(pickup_id)=64) - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL + ,amount taler_amount NOT NULL ); COMMENT ON TABLE merchant_reward_pickups IS 'rewards that have been picked up'; -COMMENT ON COLUMN merchant_rewards.amount_val +COMMENT ON COLUMN merchant_rewards.amount IS 'total transaction cost for all coins including withdraw fees'; CREATE TABLE IF NOT EXISTS merchant_reward_pickup_signatures |