diff options
Diffstat (limited to 'src/backenddb/merchant-0001.sql')
| -rw-r--r-- | src/backenddb/merchant-0001.sql | 331 |
1 files changed, 142 insertions, 189 deletions
diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql index 1bd35c35..2acf49cd 100644 --- a/src/backenddb/merchant-0001.sql +++ b/src/backenddb/merchant-0001.sql @@ -17,24 +17,23 @@ -- Everything in one big transaction BEGIN; --- TODO: consider adding BIGSERIAL primary keys on many of the tables! - -- Check patch versioning is in place. SELECT _v.register_patch('merchant-0001', NULL, NULL); ---------------- Exchange information --------------------------- CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees - (master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) + (wirefee_serial BIGSERIAL PRIMARY KEY + ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) ,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 - ,start_date INT8 NOT NULL - ,end_date INT8 NOT NULL ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) - ,PRIMARY KEY (exchange_pub,h_wire_method,start_date,end_date) + ,UNIQUE (master_pub,h_wire_method,start_date) ); COMMENT ON TABLE merchant_exchange_wire_fees IS 'Here we store proofs of the wire fee structure of the various exchanges'; @@ -42,13 +41,14 @@ COMMENT ON COLUMN merchant_exchange_wire_fees.master_pub IS 'Master public key of the exchange with these wire fees'; CREATE TABLE IF NOT EXISTS merchant_exchange_signing_keys - (master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) + (signkey_serial BIGSERIAL PRIMARY KEY + ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32) ,start_date INT8 NOT NULL ,expire_date INT8 NOT NULL ,end_date INT8 NOT NULL - ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) - ,PRIMARY KEY (master_pub,exchange_pub) + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64), + UNIQUE (master_pub, exchange_pub, start_date) ); COMMENT ON TABLE merchant_exchange_signing_keys IS 'Here we store proofs of the exchange online signing keys being signed by the exchange master key'; @@ -59,11 +59,11 @@ COMMENT ON COLUMN merchant_exchange_signing_keys.master_pub -------------------------- Instances --------------------------- CREATE TABLE IF NOT EXISTS merchant_instances - (merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32), + (merchant_serial BIGSERIAL PRIMARY KEY + ,merchant_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_pub)=32) ,merchant_name VARCHAR NOT NULL ,location BYTEA NOT NULL ,jurisdiction BYTEA NOT NULL - ,PRIMARY KEY (reserve_pub) ); COMMENT ON TABLE merchant_instances IS 'all the instances supported by this backend'; @@ -75,43 +75,42 @@ COMMENT ON COLUMN merchant_instances.jurisdiction IS 'jurisdiction of the merchant as a Location in JSON format (required)'; CREATE TABLE IF NOT EXISTS merchant_keys - (merchant_priv BYTEA NOT NULL CHECK (LENGTH(merchant_priv)=32) UNIQUE, - merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE - ,PRIMARY KEY (merchant_pub) + (merchant_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_priv)=32), + merchant_serial BIGINT PRIMARY KEY + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE ); COMMENT ON TABLE merchant_keys IS 'private keys of instances that have not been deleted'; CREATE TABLE IF NOT EXISTS merchant_instance_accounts - (account_id BIGSERIAL NOT NULL - merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32), - ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64), -- or did we use a shorter hash here? - ,salt BYTEA NOT NULL CHECK (LENGTH(salt)=64), -- or did we use a shorter salt here? + (account_serial BIGSERIAL PRIMARY KEY + ,merchant_serial BIGINT NOT NULL UNIQUE + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE + ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64) ,active boolean NOT NULL - ,payto_uri VARCHAR NOT NULL CHECK, - ,PRIMARY KEY (merchant_pub,h_wire) - ,FOREIGN KEY (merchant_pub) - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE + ,salt VARCHAR NOT NULL + ,payto_uri VARCHAR NOT NULL + ,UNIQUE (merchant_serial,payto_uri) ); -COMMENT ON TABLE merchant_accounts +COMMENT ON TABLE merchant_instance_accounts IS 'bank accounts of the instances'; -COMMENT ON COLUMN merchant_accounts.h_wire +COMMENT ON COLUMN merchant_instance_accounts.h_wire IS 'salted hash of payto_uri'; -COMMENT ON COLUMN merchant_accounts.salt +COMMENT ON COLUMN merchant_instance_accounts.salt IS 'salt used when hashing payto_uri into h_wire'; -COMMENT ON COLUMN merchant_accounts.payto_uri +COMMENT ON COLUMN merchant_instance_accounts.payto_uri IS 'payto URI of a merchant bank account'; -COMMENT ON COLUMN merchant_instances.active +COMMENT ON COLUMN merchant_instance_accounts.active IS 'true if we actively use this bank account, false if it is just kept around for older contracts to refer to'; -------------------------- Inventory --------------------------- CREATE TABLE IF NOT EXISTS merchant_inventory - (product_id VARCHAR NOT NULL - ,merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE + (product_serial BIGSERIAL PRIMARY KEY + ,merchant_serial BIGINT NOT NULL + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE + ,product_id VARCHAR NOT NULL ,description VARCHAR NOT NULL ,description_i18n BYTEA NOT NULL ,unit VARCHAR NOT NULL @@ -124,7 +123,7 @@ CREATE TABLE IF NOT EXISTS merchant_inventory ,total_lost BIGINT NOT NULL ,location BYTEA NOT NULL ,next_restock INT8 NOT NULL - ,PRIMARY KEY (product_id, merchant_pub) + ,UNIQUE (merchant_serial, product_id) ); COMMENT ON TABLE merchant_inventory IS 'products offered by the merchant (may be incomplete, frontend can override)'; @@ -152,38 +151,42 @@ COMMENT ON COLUMN merchant_inventory.next_restock IS 'GNUnet absolute time indicating when the next restock is expected. 0 for unknown.'; CREATE TABLE IF NOT EXISTS merchant_inventory_locks - (product_id VARCHAR NOT NULL - ,merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE - ,lock_uuid BYTEA NOT NULL -- FIXME: length constraint? + (product_serial BIGINT NOT NULL + REFERENCES merchant_inventory (product_serial) ON DELETE CASCADE + ,lock_uuid BYTEA NOT NULL CHECK (LENGTH(lock_uuid)=32) ,total_locked BIGINT NOT NULL - ,expiration TIMESTAMP NOT NULL, - ,FOREIGN KEY (product_id, merchant_pub) - REFERENCES merchant_inventory (product_id, merchant_pub) ON DELETE CASCADE + ,expiration TIMESTAMP NOT NULL ); +CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_product_and_lock + ON merchant_inventory_locks + (product_serial, lock_uuid); +CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_expiration + ON merchant_inventory_locks + (expiration); COMMENT ON TABLE merchant_inventory_locks IS 'locks on inventory helt by shopping carts'; -COMMENT ON TABLE merchant_inventory_locks.total_locked +COMMENT ON COLUMN merchant_inventory_locks.total_locked IS 'how many units of the product does this lock reserve'; -COMMENT ON TABLE merchant_inventory_locks.expiration +COMMENT ON COLUMN merchant_inventory_locks.expiration IS 'when does this lock automatically expire (if no order is created)'; ---------------- Orders and contracts --------------------------- CREATE TABLE IF NOT EXISTS merchant_orders - (order_id VARCHAR NOT NULL - ,merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE + (order_serial BIGSERIAL PRIMARY KEY + ,merchant_serial BIGINT NOT NULL + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE + ,order_id VARCHAR NOT NULL ,contract_terms BYTEA NOT NULL ,pay_deadline INT8 NOT NULL - ,PRIMARY KEY (order_id, merchant_pub) + ,UNIQUE (merchant_serial, order_id) ); COMMENT ON TABLE merchant_orders IS 'Orders we offered to a customer, but that have not yet been claimed'; -COMMENT ON COLUMN merchnat_orders.contract_terms +COMMENT ON COLUMN merchant_orders.contract_terms IS 'Claiming changes the contract_terms, hence we have no hash of the terms in this table'; -COMMENT ON COLUMN merchant_orders.merchant_pub +COMMENT ON COLUMN merchant_orders.merchant_serial IS 'Identifies the instance offering the contract'; COMMENT ON COLUMN merchant_orders.pay_deadline IS 'How long is the offer valid. After this time, the order can be garbage collected'; @@ -192,40 +195,40 @@ CREATE INDEX IF NOT EXISTS merchant_orders_by_expiration (pay_deadline); CREATE TABLE IF NOT EXISTS merchant_order_locks - (product_id VARCHAR NOT NULL - ,merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE + (product_serial BIGINT NOT NULL + REFERENCES merchant_inventory (product_serial) ON DELETE CASCADE ,total_locked BIGINT NOT NULL - ,order_id VARCHAR NOT NULL, - ,FOREIGN KEY (order_id, merchant_pub) - REFERENCES merchant_orders (order_id, merchant_pub) ON DELETE CASCADE - ,FOREIGN KEY (product_id, merchant_pub) - REFERENCES merchant_inventory (product_id, merchant_pub) ON DELETE CASCADE - ,PRIMARY KEY (product_id, merchant_pub, order_id) + ,order_serial BIGINT NOT NULL + REFERENCES merchant_orders (order_serial) ON DELETE CASCADE ); -COMMENT ON TABLE merchant_inventory_locks +CREATE INDEX IF NOT EXISTS merchant_orders_locks_by_order_and_product + ON merchant_order_locks + (order_serial, product_serial); +COMMENT ON TABLE merchant_order_locks IS 'locks on orders awaiting claim and payment'; -COMMENT ON TABLE merchant_inventory_locks.total_locked +COMMENT ON COLUMN merchant_order_locks.total_locked IS 'how many units of the product does this lock reserve'; CREATE TABLE IF NOT EXISTS merchant_contract_terms - (order_id VARCHAR NOT NULL - ,merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE + (contract_serial BIGSERIAL PRIMARY KEY + ,merchant_serial BIGINT NOT NULL + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE + ,contract_id VARCHAR NOT NULL ,contract_terms BYTEA NOT NULL ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) ,pay_deadline INT8 NOT NULL - ,row_id BIGSERIAL UNIQUE - ,paid boolean DEFAULT FALSE NOT NULL ,refund_deadline INT8 NOT NULL - ,PRIMARY KEY (order_id, merchant_pub) - ,UNIQUE (h_contract_terms, merchant_pub) + ,paid BOOLEAN DEFAULT FALSE NOT NULL + ,fulfillment_url VARCHAR NOT NULL + ,session_id VARCHAR NOT NULL + ,UNIQUE (merchant_serial, contract_id) + ,UNIQUE (merchant_serial, h_contract_terms) ); COMMENT ON TABLE merchant_contract_terms IS 'Contracts are orders that have been claimed by a wallet'; -COMMENT ON COLUMN merchant_contract_terms.order_id +COMMENT ON COLUMN merchant_contract_terms.contract_id IS 'Not a foreign key into merchant_orders because paid contracts persist after expiration'; -COMMENT ON COLUMN merchant_contract_terms.merchant_pub +COMMENT ON COLUMN merchant_contract_terms.merchant_serial IS 'Identifies the instance offering the contract'; COMMENT ON COLUMN merchant_contract_terms.contract_terms IS 'These contract terms include the wallet nonce'; @@ -235,19 +238,29 @@ COMMENT ON COLUMN merchant_contract_terms.refund_deadline IS 'By what times do refunds have to be approved (useful to reject refund requests)'; COMMENT ON COLUMN merchant_contract_terms.paid IS 'true implies the customer paid for this contract; order should be DELETEd from merchant_orders once paid is set to release merchant_order_locks; paid remains true even if the payment was later refunded'; +COMMENT ON COLUMN merchant_contract_terms.fulfillment_url + IS 'also included in contract_terms, but we need it here to SELECT on it during repurchase detection'; +COMMENT ON COLUMN merchant_contract_terms.session_id + IS 'last session_id from we confirmed the paying client to use, empty string for none'; COMMENT ON COLUMN merchant_contract_terms.pay_deadline IS 'How long is the offer valid. After this time, the order can be garbage collected'; -CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_expiration +CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_expiration + ON merchant_contract_terms + (merchant_serial,pay_deadline); +CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_payment ON merchant_contract_terms - (pay_deadline); + (merchant_serial,paid); +CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_session_and_fulfillment + ON merchant_contract_terms + (merchant_serial,fulfillment_url,session_id); ---------------- Payment and refunds --------------------------- CREATE TABLE IF NOT EXISTS merchant_deposits - (h_contract_terms BYTEA NOT NULL - ,merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE + (deposit_serial BIGSERIAL PRIMARY KEY + ,contract_serial BIGINT + REFERENCES merchant_contract_terms (contract_serial) ON DELETE CASCADE ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) ,exchange_url VARCHAR NOT NULL ,amount_with_fee_val INT8 NOT NULL @@ -258,107 +271,61 @@ CREATE TABLE IF NOT EXISTS merchant_deposits ,refund_fee_frac INT4 NOT NULL ,wire_fee_val INT8 NOT NULL ,wire_fee_frac INT4 NOT NULL - ,exchange_pub BYTEA NOT NULL - REFERENCES merchant_exchange_signing_keys (exchange_pub) ON DELETE CASCADE + ,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) - ,exchange_timestamp INT 8 NOT NULL - ,h_wire BYTEA NOT NULL - ,exchange_proof BYTEA NOT NULL - ,PRIMARY KEY (h_contract_terms, coin_pub) - ,FOREIGN KEY (h_contract_terms, merchant_pub) - REFERENCES merchant_contract_terms (h_contract_terms, merchant_pub) ON DELETE CASCADE - ,FOREIGN KEY (h_wire, merchant_pub) - REFERENCES merchant_instance_accounts (h_wire, merchant_pub) ON DELETE CASCADE + ,exchange_timestamp INT8 NOT NULL + ,account_serial BIGINT NOT NULL + REFERENCES merchant_instance_accounts (account_serial) ON DELETE CASCADE + ,UNIQUE (contract_serial, coin_pub) ); COMMENT ON TABLE merchant_deposits IS 'Table with the deposit confirmations for each coin we deposited at the exchange'; -COMMENT ON COLUMN merchant_deposits.exchange_pub +COMMENT ON COLUMN merchant_deposits.signkey_serial IS 'Online signing key of the exchange on 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 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.)'; -COMMENT ON COLUMN merchant_deposits.transferred - -CREATE TABLE IF NOT EXISTS merchant_session_info - (session_id VARCHAR NOT NULL - ,fulfillment_url VARCHAR NOT NULL --- FIXME: why do we store this here? - ,order_id VARCHAR NOT NULL - ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) - ,timestamp INT8 NOT NULL - ,PRIMARY KEY (session_id, fulfillment_url, merchant_pub) --- FIXME: I am confused why this even *IS* a primary key. - ,FOREIGN KEY (order_id, merchant_pub) - REFERENCES merchant_orders (order_id, merchant_pub) --- FIXME: if this is for session-bound payments, --- we need to reference merchant_contracts as --- the *order* may be GCed after payment but the --- session-bound payment mechanism should continue to work! - ,UNIQUE (session_id, fulfillment_url, order_id, merchant_pub) --- FIXME: isn't this redundant with the (confusing) PRIMARY KEY? - ); --- FIXME: Support for #5853 (limit session in number and duration) --- should be supported 'somewhere' here. --- => #5853 suggests a UNIQUE constraint on order_id+merchant_pub on this table! -COMMENT ON TABLE merchant_session_info - IS 'sessions and their order_id/fulfillment_url mapping'; -COMMENT ON COLUMN merchant_session_info.fulfillment_url - IS 'FIXME: Unclear why the fulfillment URL is in this table'; -COMMENT ON COLUMN merchant_session_info.order_id - IS 'FIXME: Why use merchant_pub+order_id here, instead of a say a contract_id?'; CREATE TABLE IF NOT EXISTS merchant_refunds - (rtransaction_id BIGSERIAL UNIQUE - ,merchant_pub BYTEA NOT NULL - ,h_contract_terms BYTEA NOT NULL + (refund_serial BIGSERIAL PRIMARY KEY + ,contract_serial BIGINT NOT NULL + REFERENCES merchant_contract_terms (contract_serial) ON DELETE CASCADE + ,rtransaction_id BIGINT NOT NULL ,coin_pub BYTEA NOT NULL ,reason VARCHAR NOT NULL ,refund_amount_val INT8 NOT NULL ,refund_amount_frac INT4 NOT NULL - ,FOREIGN KEY (h_contract_terms, coin_pub) - REFERENCES merchant_deposits (h_contract_terms, coin_pub) ON DELETE CASCADE - ,FOREIGN KEY (h_contract_terms, merchant_pub) - REFERENCES merchant_contract_terms (h_contract_terms, merchant_pub) ON DELETE CASCADE - ,PRIMARY KEY (h_contract_terms, merchant_pub, coin_pub, rtransaction_id) + ,UNIQUE (contract_serial, coin_pub, rtransaction_id) ); COMMENT ON TABLE merchant_deposits IS 'Refunds approved by the merchant (backoffice) logic, excludes abort refunds'; COMMENT ON COLUMN merchant_refunds.rtransaction_id IS 'Needed for uniqueness in case a refund is increased for the same order'; --- FIXME: do we really want rtransaction_id as BIGSERIAL UNIQUE? --- this exposes # of refunds granted to clients!!! CREATE TABLE IF NOT EXISTS merchant_refund_proofs - (rtransaction_id BIGSERIAL UNIQUE - ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) - ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) + (refund_serial BIGINT PRIMARY KEY + REFERENCES merchant_refunds (refund_serial) ON DELETE CASCADE ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) - ,exchange_pub BYTEA NOT NULL - REFERENCES merchant_exchange_signing_keys (exchange_pub) ON DELETE CASCADE - ,FOREIGN KEY (h_contract_terms, merchant_pub, coin_pub, rtransaction_id) - REFERENCES merchant_refunds (h_contract_terms, merchant_pub, coin_pub, rtransaction_id) ON DELETE CASCADE - ,PRIMARY KEY (h_contract_terms, merchant_pub, coin_pub, rtransaction_id) + ,signkey_serial BIGINT NOT NULL + REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE ); COMMENT ON TABLE merchant_refund_proofs IS 'Refunds confirmed by the exchange (not all approved refunds are grabbed by the wallet)'; --- FIXME: rtransaction_id as BIGSERIAL UNIQUE should suffice, rest of information --- in the FOREIGN KEY is duplicated for no good reason. - -------------------- Wire transfers --------------------------- CREATE TABLE IF NOT EXISTS merchant_credits - (credit_serial BIGSERIAL NOT NULL + (credit_serial BIGSERIAL 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 - ,account_id BIGSERIAL NOT NULL - REFERENCES merchant_instance_accounts (account_id) ON DELETE CASCADE + ,account_serial BIGINT NOT NULL + REFERENCES merchant_instance_accounts (account_serial) ON DELETE CASCADE ,verified BOOLEAN NOT NULL DEFAULT FALSE - ,PRIMARY KEY (wtid, exchange_url) + ,UNIQUE (wtid, exchange_url) ); COMMENT ON TABLE merchant_credits IS 'table represents the information provided by the (trusted) merchant about incoming wire transfers'; @@ -366,60 +333,47 @@ COMMENT ON COLUMN merchant_credits.verified IS 'true once we got an acceptable response from the exchange for this transfer'; CREATE TABLE IF NOT EXISTS merchant_transfer_signatures - (credit_serial BIGSERIAL NOT NULL + (credit_serial BIGINT PRIMARY KEY + REFERENCES merchant_credits (credit_serial) ON DELETE CASCADE + ,account_serial BIGINT NOT NULL + REFERENCES merchant_instance_accounts (account_serial) ON DELETE CASCADE + ,signkey_serial BIGINT NOT NULL + REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE ,execution_time INT8 NOT NULL - ,exchange_pub BYTEA NOT NULL - REFERENCES merchant_exchange_signing_keys (exchange_pub) ON DELETE CASCADE ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) - ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64) - ,PRIMARY KEY (credit_serial) - ,FOREIGN KEY (credit_serial) - REFERENCES merchant_credits (credit_serial) ON DELETE CASCADE ); 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.h_wire - IS 'salted hash of the merchant bank account'; CREATE TABLE IF NOT EXISTS merchant_transfer_by_coin - (h_contract_terms BYTEA NOT NULL - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) + (deposit_serial BIGINT UNIQUE NOT NULL + REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE + ,credit_serial BIGINT NOT NULL + REFERENCES merchant_credits (credit_serial) ON DELETE CASCADE ,offset_in_exchange_list INT8 NOT NULL - ,credit_serial BIGSERIAL NOT NULL - REFERENCES merchant_credits (credit_serial) ON DELETE CASCADE ,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 - ,PRIMARY KEY (h_contract_terms, coin_pub) ON DELETE CASCADE ); -CREATE INDEX IF NOT EXISTS merchant_transfers_by_coin - ON merchant_transfers - (h_contract_terms - ,coin_pub); -CREATE INDEX IF NOT EXISTS merchant_transfers_by_wtid - ON merchant_transfers - (wtid); -COMMENT ON TABLE merchant_transfers +CREATE INDEX IF NOT EXISTS merchant_transfers_by_credit + ON merchant_transfer_by_coin + (credit_serial); +COMMENT ON TABLE merchant_transfer_by_coin IS 'Mapping of deposits to wire transfers and vice versa'; -COMMENT ON COLUMN merchant_transfers.coin_pub - IS 'h_contract_terms and coin_pub are not a FOREIGN KEY into merchant_deposits because theoretically the exchange could pay us for things we are not aware of having sold. We still need to store this to check the signatures. This is also the reason why the deposit value and fees are duplicated here: this is about checkability of signatures. We may disagree with the claims of the exchange, but we still need the proof of what the exchange said.'; -COMMENT ON COLUMN merchant_transfers.exchange_deposit_value_val +COMMENT ON COLUMN merchant_transfer_by_coin.exchange_deposit_value_val IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits minus refunds'; -COMMENT ON COLUMN merchant_transfers.exchange_deposit_fee_val +COMMENT ON COLUMN merchant_transfer_by_coin.exchange_deposit_fee_val IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits'; -COMMENT ON COLUMN merchant_transfers.offset_in_exchange_list - IS 'At which offset in the exchange list for the wire transfer (under "deposits") does this coin appear'; -COMMENT ON COLUMN merchant_transfers.coin_pub - IS 'Note that the coin_pub/h_contract_terms can theoretically be unknown to us if someone else deposited for us at the exchange. Hence those cannot be foreign keys into the merchant_deposits table.'; -------------------------- Tipping --------------------------- CREATE TABLE IF NOT EXISTS merchant_tip_reserves - (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) - ,merchant_pub BYTEA NOT NULL - REFERENCES merchant_instances (merchant_pub) ON DELETE CASCADE + (reserve_serial BIGSERIAL PRIMARY KEY + ,reserve_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_pub)=32) + ,merchant_serial BIGINT NOT NULL + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE ,expiration INT8 NOT NULL ,merchant_initial_balance_val INT8 NOT NULL ,merchant_initial_balance_frac INT4 NOT NULL @@ -429,7 +383,6 @@ CREATE TABLE IF NOT EXISTS merchant_tip_reserves ,tips_committed_frac INT4 NOT NULL ,tips_picked_up_val INT8 NOT NULL ,tips_picked_up_frac INT4 NOT NULL - ,PRIMARY KEY (reserve_pub) ); COMMENT ON TABLE merchant_tip_reserves IS 'balances of the reserves available for tips'; @@ -445,19 +398,19 @@ COMMENT ON COLUMN merchant_tip_reserves.tips_picked_up_val IS 'Total amount tips that have been picked up from this reserve'; CREATE TABLE IF NOT EXISTS merchant_tip_reserve_kreys - (reserve_priv BYTEA NOT NULL CHECK (LENGTH(reserve_priv)=32) UNIQUE - ,reserve_pub BYTEA NOT NULL - REFERENCES merchant_tip_reserves (reserve_pub) ON DELETE CASCADE + (reserve_serial BIGINT NOT NULL UNIQUE + REFERENCES merchant_tip_reserves (reserve_serial) ON DELETE CASCADE + ,reserve_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_priv)=32) ,exchange_url VARCHAR NOT NULL - ,PRIMARY KEY (reserve_pub) ); -COMMENT ON TABLE merchant_tip_reserve_keys +COMMENT ON TABLE merchant_tip_reserves IS 'private keys of reserves that have not been deleted'; CREATE TABLE IF NOT EXISTS merchant_tips - (reserve_pub BYTEA NOT NULL - REFERENCES merchant_tip_reserves (reserve_pub) ON DELETE CASCADE - ,tip_id BYTEA NOT NULL CHECK (LENGTH(tip_id)=64) + (tip_serial BIGSERIAL PRIMARY KEY + ,reserve_serial BIGINT NOT NULL UNIQUE + REFERENCES merchant_tip_reserves (reserve_serial) ON DELETE CASCADE + ,tip_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(tip_id)=64) ,justification VARCHAR NOT NULL ,expiration INT8 NOT NULL ,amount_val INT8 NOT NULL @@ -465,28 +418,27 @@ CREATE TABLE IF NOT EXISTS merchant_tips ,left_val INT8 NOT NULL ,left_frac INT4 NOT NULL ,was_picked_up BOOLEAN NOT NULL DEFAULT FALSE - ,PRIMARY KEY (tip_id) ); +CREATE INDEX IF NOT EXISTS merchant_tips_by_pickup_and_expiration + ON merchant_tips + (was_picked_up,expiration); COMMENT ON TABLE merchant_tips IS 'tips that have been authorized'; COMMENT ON COLUMN merchant_tips.amount_val IS 'Overall tip amount'; COMMENT ON COLUMN merchant_tips.left_val IS 'Tip amount not yet picked up'; -COMMENT ON COLUMN merchant_tips.reserve_pub +COMMENT ON COLUMN merchant_tips.reserve_serial IS 'Reserve from which this tip is funded'; COMMENT ON COLUMN merchant_tips.expiration IS 'time by which the wallet has to pick up the tip before it expires'; -CREATE INDEX IF NOT EXISTS merchant_tips_by_expiration - ON merchant_tips - (expiration); CREATE TABLE IF NOT EXISTS merchant_tip_pickups - (tip_id BYTEA NOT NULL REFERENCES merchant_tips (tip_id) ON DELETE CASCADE - ,pickup_id BYTEA NOT NULL CHECK (LENGTH(pickup_id)=64) + (tip_serial BIGINT NOT NULL + REFERENCES merchant_tips (tip_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 - ,PRIMARY KEY (pickup_id) ); COMMENT ON TABLE merchant_tip_pickups IS 'tips that have been picked up'; @@ -494,7 +446,8 @@ COMMENT ON COLUMN merchant_tips.amount_val IS 'total transaction cost for all coins including withdraw fees'; CREATE TABLE IF NOT EXISTS merchant_tip_pickup_signatures - (pickup_id BYTEA NOT NULL REFERENCES merchant_tip_pickups (pickup_id) ON DELETE CASCADE + (pickup_id BYTEA NOT NULL + REFERENCES merchant_tip_pickups (pickup_id) ON DELETE CASCADE ,coin_offset INT4 NOT NULL ,blind_sig BYTEA NOT NULL ,PRIMARY KEY (pickup_id, coin_offset) |