diff options
Diffstat (limited to 'database-versioning/libeufin-bank-0001.sql')
| -rw-r--r-- | database-versioning/libeufin-bank-0001.sql | 152 |
1 files changed, 76 insertions, 76 deletions
diff --git a/database-versioning/libeufin-bank-0001.sql b/database-versioning/libeufin-bank-0001.sql index 6bf7420a..5272eeae 100644 --- a/database-versioning/libeufin-bank-0001.sql +++ b/database-versioning/libeufin-bank-0001.sql @@ -21,10 +21,7 @@ CREATE SCHEMA libeufin_bank; SET search_path TO libeufin_bank; CREATE TYPE taler_amount - AS - (val INT8 - ,frac INT4 - ); + 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'; @@ -59,7 +56,7 @@ CREATE TYPE rounding_mode -- start of: bank accounts CREATE TABLE IF NOT EXISTS customers - (customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + (customer_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE ,login TEXT NOT NULL UNIQUE ,password_hash TEXT NOT NULL ,name TEXT @@ -72,27 +69,10 @@ COMMENT ON COLUMN customers.cashout_payto COMMENT ON COLUMN customers.name IS 'Full name of the customer.'; -CREATE TABLE IF NOT EXISTS bearer_tokens - (bearer_token_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,content BYTEA NOT NULL UNIQUE CHECK (LENGTH(content)=32) - ,creation_time INT8 - ,expiration_time INT8 - ,scope token_scope_enum - ,is_refreshable BOOLEAN - ,bank_customer BIGINT NOT NULL REFERENCES customers(customer_id) ON DELETE CASCADE -); -COMMENT ON TABLE bearer_tokens - IS 'Login tokens associated with one bank customer. There is currently' - ' no garbage collector that deletes the expired tokens from the table'; -COMMENT ON COLUMN bearer_tokens.bank_customer - IS 'The customer that directly created this token, or the customer that' - ' created the very first token that originated all the refreshes until' - ' this token was created.'; - CREATE TABLE IF NOT EXISTS bank_accounts - (bank_account_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + (bank_account_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE ,internal_payto_uri TEXT NOT NULL UNIQUE - ,owning_customer_id BIGINT NOT NULL UNIQUE -- UNIQUE enforces 1-1 map with customers + ,owning_customer_id INT8 NOT NULL UNIQUE -- UNIQUE enforces 1-1 map with customers REFERENCES customers(customer_id) ON DELETE CASCADE ,is_public BOOLEAN DEFAULT FALSE NOT NULL -- privacy by default @@ -116,8 +96,26 @@ can be publicly shared'; COMMENT ON COLUMN bank_accounts.owning_customer_id IS 'Login that owns the bank account'; +CREATE TABLE IF NOT EXISTS bearer_tokens + (bearer_token_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,content BYTEA NOT NULL UNIQUE CHECK (LENGTH(content)=32) + ,creation_time INT8 + ,expiration_time INT8 + ,scope token_scope_enum + ,is_refreshable BOOLEAN + ,bank_customer INT8 NOT NULL + REFERENCES customers(customer_id) + ON DELETE CASCADE +); +COMMENT ON TABLE bearer_tokens + IS 'Login tokens associated with one bank customer.'; +COMMENT ON COLUMN bearer_tokens.bank_customer + IS 'The customer that directly created this token, or the customer that' + ' created the very first token that originated all the refreshes until' + ' this token was created.'; + CREATE TABLE IF NOT EXISTS iban_history - (iban TEXT PRIMARY key + (iban TEXT PRIMARY KEY ,creation_time INT8 NOT NULL ); COMMENT ON TABLE iban_history IS 'Track all generated iban, some might be unused.'; @@ -127,21 +125,19 @@ COMMENT ON TABLE iban_history IS 'Track all generated iban, some might be unused -- start of: money transactions CREATE TABLE IF NOT EXISTS bank_account_transactions - (bank_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + (bank_transaction_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE ,creditor_payto_uri TEXT NOT NULL ,creditor_name TEXT NOT NULL ,debtor_payto_uri TEXT NOT NULL ,debtor_name TEXT NOT NULL ,subject TEXT NOT NULL ,amount taler_amount NOT NULL - ,transaction_date BIGINT NOT NULL -- is this ISO20022 terminology? document format (microseconds since epoch) + ,transaction_date INT8 NOT NULL ,account_servicer_reference TEXT ,payment_information_id TEXT ,end_to_end_id TEXT ,direction direction_enum NOT NULL - ,bank_account_id BIGINT NOT NULL - REFERENCES bank_accounts(bank_account_id) - ON DELETE CASCADE ON UPDATE RESTRICT + ,bank_account_id INT8 NOT NULL REFERENCES bank_accounts(bank_account_id) ); COMMENT ON COLUMN bank_account_transactions.direction @@ -157,7 +153,7 @@ COMMENT ON COLUMN bank_account_transactions.bank_account_id -- start of: TAN challenge CREATE TABLE IF NOT EXISTS challenges - (challenge_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE, + (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE, code TEXT NOT NULL, creation_date INT8 NOT NULL, expiration_date INT8 NOT NULL, @@ -184,27 +180,23 @@ COMMENT ON COLUMN challenges.confirmation_date -- start of: cashout management CREATE TABLE IF NOT EXISTS cashout_operations - (cashout_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + (cashout_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE ,request_uid BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(request_uid)=32) ,amount_debit taler_amount NOT NULL ,amount_credit taler_amount NOT NULL ,subject TEXT NOT NULL - ,creation_time BIGINT NOT NULL - ,bank_account BIGINT NOT NULL + ,creation_time INT8 NOT NULL + ,bank_account INT8 NOT NULL REFERENCES bank_accounts(bank_account_id) - ON DELETE CASCADE - ON UPDATE RESTRICT - ,challenge BIGINT NOT NULL UNIQUE + ,challenge INT8 NOT NULL UNIQUE REFERENCES challenges(challenge_id) - ON DELETE CASCADE - ON UPDATE RESTRICT - ,tan_channel TEXT NULL DEFAULT NULL -- TODO should be tan_enum but might be removed in the future + ON DELETE SET NULL + ,tan_channel TEXT NULL DEFAULT NULL ,tan_info TEXT NULL DEFAULT NULL ,aborted BOOLEAN NOT NULL DEFAULT FALSE - ,local_transaction BIGINT UNIQUE DEFAULT NULL-- FIXME: Comment that the transaction only gets created after the TAN confirmation + ,local_transaction INT8 UNIQUE DEFAULT NULL REFERENCES bank_account_transactions(bank_transaction_id) - ON DELETE RESTRICT - ON UPDATE RESTRICT + ON DELETE CASCADE ); COMMENT ON COLUMN cashout_operations.bank_account IS 'Bank amount to debit during confirmation'; COMMENT ON COLUMN cashout_operations.challenge IS 'TAN challenge used to confirm the operation'; @@ -216,31 +208,28 @@ COMMENT ON COLUMN cashout_operations.tan_info IS 'Info of the last successful tr -- start of: Taler integration CREATE TABLE IF NOT EXISTS taler_exchange_outgoing - (exchange_outgoing_id BIGINT GENERATED BY DEFAULT AS IDENTITY + (exchange_outgoing_id INT8 GENERATED BY DEFAULT AS IDENTITY ,request_uid BYTEA UNIQUE CHECK (LENGTH(request_uid)=64) ,wtid BYTEA NOT NULL UNIQUE CHECK (LENGTH(wtid)=32) ,exchange_base_url TEXT NOT NULL - ,bank_transaction BIGINT UNIQUE NOT NULL + ,bank_transaction INT8 UNIQUE NOT NULL REFERENCES bank_account_transactions(bank_transaction_id) - ON DELETE RESTRICT - ON UPDATE RESTRICT - ,creditor_account_id BIGINT NOT NULL + ON DELETE CASCADE + ,creditor_account_id INT8 NOT NULL REFERENCES bank_accounts(bank_account_id) - ON DELETE CASCADE ON UPDATE RESTRICT ); CREATE TABLE IF NOT EXISTS taler_exchange_incoming - (exchange_incoming_id BIGINT GENERATED BY DEFAULT AS IDENTITY + (exchange_incoming_id INT8 GENERATED BY DEFAULT AS IDENTITY ,reserve_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_pub)=32) - ,bank_transaction BIGINT UNIQUE NOT NULL + ,bank_transaction INT8 UNIQUE NOT NULL REFERENCES bank_account_transactions(bank_transaction_id) - ON DELETE RESTRICT - ON UPDATE RESTRICT + ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS taler_withdrawal_operations - (withdrawal_id BIGINT GENERATED BY DEFAULT AS IDENTITY - ,withdrawal_uuid uuid NOT NULL PRIMARY KEY + (withdrawal_id INT8 GENERATED BY DEFAULT AS IDENTITY + ,withdrawal_uuid uuid NOT NULL UNIQUE ,amount taler_amount NOT NULL ,selection_done BOOLEAN DEFAULT FALSE NOT NULL ,aborted BOOLEAN DEFAULT FALSE NOT NULL @@ -248,10 +237,9 @@ CREATE TABLE IF NOT EXISTS taler_withdrawal_operations ,reserve_pub BYTEA UNIQUE CHECK (LENGTH(reserve_pub)=32) ,subject TEXT ,selected_exchange_payto TEXT - ,wallet_bank_account BIGINT NOT NULL + ,wallet_bank_account INT8 NOT NULL REFERENCES bank_accounts(bank_account_id) - ON DELETE RESTRICT - ON UPDATE RESTRICT + ON DELETE CASCADE ); COMMENT ON COLUMN taler_withdrawal_operations.selection_done IS 'Signals whether the wallet specified the exchange and gave the reserve public key'; @@ -264,32 +252,44 @@ COMMENT ON COLUMN taler_withdrawal_operations.confirmation_done CREATE TABLE IF NOT EXISTS bank_stats ( timeframe stat_timeframe_enum NOT NULL ,start_time timestamp NOT NULL - ,taler_in_count BIGINT NOT NULL DEFAULT 0 + ,taler_in_count INT8 NOT NULL DEFAULT 0 ,taler_in_volume taler_amount NOT NULL DEFAULT (0, 0) - ,taler_out_count BIGINT NOT NULL DEFAULT 0 + ,taler_out_count INT8 NOT NULL DEFAULT 0 ,taler_out_volume taler_amount NOT NULL DEFAULT (0, 0) - ,cashin_count BIGINT NOT NULL DEFAULT 0 + ,cashin_count INT8 NOT NULL DEFAULT 0 ,cashin_regional_volume taler_amount NOT NULL DEFAULT (0, 0) ,cashin_fiat_volume taler_amount NOT NULL DEFAULT (0, 0) - ,cashout_count BIGINT NOT NULL DEFAULT 0 + ,cashout_count INT8 NOT NULL DEFAULT 0 ,cashout_regional_volume taler_amount NOT NULL DEFAULT (0, 0) ,cashout_fiat_volume taler_amount NOT NULL DEFAULT (0, 0) ,PRIMARY KEY (start_time, timeframe) ); --- TODO garbage collection -COMMENT ON TABLE bank_stats IS 'Stores statistics about the bank usage.'; -COMMENT ON COLUMN bank_stats.timeframe IS 'particular timeframe that this row accounts for'; -COMMENT ON COLUMN bank_stats.start_time IS 'timestamp of the start of the timeframe that this row accounts for, truncated according to the precision of the timeframe'; -COMMENT ON COLUMN bank_stats.taler_out_count IS 'how many internal payments were made by a Taler exchange'; -COMMENT ON COLUMN bank_stats.taler_out_volume IS 'how much internal currency was paid by a Taler exchange'; -COMMENT ON COLUMN bank_stats.taler_in_count IS 'how many internal payments were made to a Taler exchange'; -COMMENT ON COLUMN bank_stats.taler_in_volume IS 'how much internal currency was paid to a Taler exchange'; -COMMENT ON COLUMN bank_stats.cashin_count IS 'how many cashin operations took place in the timeframe'; -COMMENT ON COLUMN bank_stats.cashin_regional_volume IS 'how much regional currency was cashed in in the timeframe'; -COMMENT ON COLUMN bank_stats.cashin_fiat_volume IS 'how much fiat currency was cashed in in the timeframe'; -COMMENT ON COLUMN bank_stats.cashout_count IS 'how many cashout operations took place in the timeframe'; -COMMENT ON COLUMN bank_stats.cashout_regional_volume IS 'how much regional currency was payed by the bank to customers in the timeframe'; -COMMENT ON COLUMN bank_stats.cashout_fiat_volume IS 'how much fiat currency was payed by the bank to customers in the timeframe'; +COMMENT ON TABLE bank_stats + IS 'Stores statistics about the bank usage.'; +COMMENT ON COLUMN bank_stats.timeframe + IS 'particular timeframe that this row accounts for'; +COMMENT ON COLUMN bank_stats.start_time + IS 'timestamp of the start of the timeframe that this row accounts for, truncated according to the precision of the timeframe'; +COMMENT ON COLUMN bank_stats.taler_out_count + IS 'how many internal payments were made by a Taler exchange'; +COMMENT ON COLUMN bank_stats.taler_out_volume + IS 'how much internal currency was paid by a Taler exchange'; +COMMENT ON COLUMN bank_stats.taler_in_count + IS 'how many internal payments were made to a Taler exchange'; +COMMENT ON COLUMN bank_stats.taler_in_volume + IS 'how much internal currency was paid to a Taler exchange'; +COMMENT ON COLUMN bank_stats.cashin_count + IS 'how many cashin operations took place in the timeframe'; +COMMENT ON COLUMN bank_stats.cashin_regional_volume + IS 'how much regional currency was cashed in in the timeframe'; +COMMENT ON COLUMN bank_stats.cashin_fiat_volume + IS 'how much fiat currency was cashed in in the timeframe'; +COMMENT ON COLUMN bank_stats.cashout_count + IS 'how many cashout operations took place in the timeframe'; +COMMENT ON COLUMN bank_stats.cashout_regional_volume + IS 'how much regional currency was payed by the bank to customers in the timeframe'; +COMMENT ON COLUMN bank_stats.cashout_fiat_volume + IS 'how much fiat currency was payed by the bank to customers in the timeframe'; -- end of: Statistics |