[libeufin] branch master updated: SQL syntax

[gnunet]

This is an automated email from the git hooks/post-receive script.

ms pushed a commit to branch master
in repository libeufin.

The following commit(s) were added to refs/heads/master by this push:
     new 8cbf0020 SQL syntax
8cbf0020 is described below

commit 8cbf0020ac0a8e66946546e36c4ccba610f845f8
Author: MS <ms@taler.net>
AuthorDate: Tue Aug 29 13:58:26 2023 +0200

    SQL syntax
---
 database-versioning/new/sandbox-0001-refactor.sql | 131 ++++++++++++----------
 sandbox/src/test/kotlin/DatabaseTest.kt           |   2 +
 2 files changed, 72 insertions(+), 61 deletions(-)

diff --git a/database-versioning/new/sandbox-0001-refactor.sql 
b/database-versioning/new/sandbox-0001-refactor.sql
index 4c06c4df..68c73c46 100644
--- a/database-versioning/new/sandbox-0001-refactor.sql
+++ b/database-versioning/new/sandbox-0001-refactor.sql
@@ -15,10 +15,18 @@
 
 BEGIN;
 
-SELECT _v.register_patch('sandbox-0001', NULL, NULL);
+SELECT _v.register_patch('libeufin-bank-0001', NULL, NULL);
 
-CREATE SCHEMA sandbox;
-SET search_path TO sandbox;
+CREATE SCHEMA libeufin_bank;
+SET search_path TO libeufin_bank;
+
+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';
 
 -- Indicates whether a transaction is incoming or outgoing.
 CREATE TYPE direction_enum
@@ -30,9 +38,16 @@ CREATE TYPE tan_enum
 CREATE TYPE cashout_status_enum
   AS ENUM ('pending', 'confirmed');
 
+CREATE TYPE subscriber_key_state_enum
+  AS ENUM ('new', 'invalid', 'confirmed');
+
+CREATE TYPE subscriber_state_enum
+  AS ENUM ('new', 'confirmed');
+
+
 -- FIXME: comments on types (see exchange for example)!
 
--- start of: bank config tables
+-- start of: bank config tables.  FIXME: eventually replaced by the INI file.
 
 CREATE TABLE IF NOT EXISTS configuration
   (config_key TEXT PRIMARY KEY
@@ -44,7 +59,7 @@ CREATE TABLE IF NOT EXISTS configuration
 -- start of: bank accounts
 
 CREATE TABLE IF NOT EXISTS customers
-  (customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,login TEXT NOT NULL
   ,password_hash TEXT NOT NULL
   ,name TEXT
@@ -62,12 +77,12 @@ COMMENT ON COLUMN customers.name
 
 
 CREATE TABLE IF NOT EXISTS bank_accounts 
-  (bank_account_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (bank_account_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,iban TEXT NOT NULL UNIQUE
   ,bic TEXT NOT NULL
   ,bank_account_label TEXT NOT NULL
   ,owning_customer_id BIGINT NOT NULL
-    REFERENCES bank_customers(bank_customer_id)
+    REFERENCES customers(customer_id)
   ,is_public BOOLEAN DEFAULT FALSE NOT NULL -- privacy by default
   ,last_nexus_fetch_row_id BIGINT
   ,balance taler_amount DEFAULT (0, 0)
@@ -80,30 +95,30 @@ respect to bank accounts (in contrast to what the Python 
bank
 did).  The idea was to provide multiple bank accounts to one
 user.  Nonetheless, for simplicity the current version enforces
 one bank account for one user, and additionally the bank
-account label matches always the login.'
+account label matches always the login.';
 
-COMMENT ON TABLE bank_accounts.last_nexus_fetch_row_id
+COMMENT ON COLUMN bank_accounts.last_nexus_fetch_row_id
   IS 'Keeps the ID of the last incoming payment that was learnt
 from Nexus.  For that reason, this ID is stored verbatim as
 it was returned by Nexus.  It helps to build queries to Nexus
-that needs this value as a parameter.'
+that needs this value as a parameter.';
 
 COMMENT ON COLUMN bank_accounts.is_public
   IS 'Indicates whether the bank account history
-can be publicly shared'
+can be publicly shared';
 
-COMMENT ON COLUMN bank_accounts.label
-  IS 'Label of the bank account'
+COMMENT ON COLUMN bank_accounts.bank_account_label
+  IS 'Label of the bank account';
 
 COMMENT ON COLUMN bank_accounts.owning_customer_id
-  IS 'Login that owns the bank account'
+  IS 'Login that owns the bank account';
 
 -- end of: bank accounts
 
 -- start of: money transactions
 
 CREATE TABLE IF NOT EXISTS bank_account_transactions 
-  (bank_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (bank_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,creditor_iban TEXT NOT NULL
   ,creditor_bic TEXT NULL
   ,creditor_name TEXT NOT NULL
@@ -123,23 +138,21 @@ CREATE TABLE IF NOT EXISTS bank_account_transactions
     ON DELETE CASCADE ON UPDATE RESTRICT
   );
 
-COMMENT ON bank_account_transactions.direction
-  IS 'Indicates whether the transaction is incoming
-or outgoing for the bank account associated with this
-transaction.'
-COMMENT ON bank_account_transactions.pmt_inf_id
-  IS 'ISO20022 specific'
-COMMENT ON bank_account_transactions.end_to_end_id
-  IS 'ISO20022 specific'
-COMMENT ON bank_account_transactions.bank
-  IS 'The bank hosting the transaction and its bank account.'
+COMMENT ON COLUMN bank_account_transactions.direction
+  IS 'Indicates whether the transaction is incoming or outgoing for the bank 
account associated with this transaction.';
+COMMENT ON COLUMN bank_account_transactions.payment_information_id
+  IS 'ISO20022 specific';
+COMMENT ON COLUMN bank_account_transactions.end_to_end_id
+  IS 'ISO20022 specific';
+COMMENT ON COLUMN bank_account_transactions.bank_account_id
+  IS 'The bank account affected by this transaction.';
 
 -- end of: money transactions
 
 -- start of: cashout management
 
 CREATE TABLE IF NOT EXISTS cashout_operations 
-  (cashout_operation_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (cashout_operation_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,cashout_uuid uuid PRIMARY KEY
   ,local_transaction BIGINT UNIQUE -- FIXME: Comment that the transaction only 
gets created after the TAN confirmation
     REFERENCES bank_account_transactions(bank_transaction_id)
@@ -167,11 +180,11 @@ CREATE TABLE IF NOT EXISTS cashout_operations
 -- FIXME: table comment missing
 
 COMMENT ON COLUMN cashout_operations.tan_confirmation_time
-  IS 'Timestamp when the customer confirmed the cash-out operation via TAN'
+  IS 'Timestamp when the customer confirmed the cash-out operation via TAN';
 COMMENT ON COLUMN cashout_operations.cashout_address
-  IS 'IBAN that ultimately gets the fiat payment'
-COMMENT ON COLUMN cashout_operations.tan_salt
-  IS 'text that the customer must send to confirm the cash-out operation'
+  IS 'IBAN that ultimately gets the fiat payment';
+COMMENT ON COLUMN cashout_operations.tan_code
+  IS 'text that the customer must send to confirm the cash-out operation';
 
 -- FIXME: check in the code if this really only has pending or failed 
submissions!
 CREATE TABLE IF NOT EXISTS pending_cashout_submissions 
@@ -184,12 +197,10 @@ CREATE TABLE IF NOT EXISTS pending_cashout_submissions
   ,submission_time BIGINT
   );
 
-COMMENT ON TABLE cashout_submissions
-  IS 'Tracks payment requests made from Sandbox to Nexus to trigger fiat 
transactions that finalize cash-outs.'
-COMMENT ON COLUMN cashout_submissions.local_transaction
-  IS 'Points to the bank transaction from the customer to the admin bank 
account that triggered the cash-out submission'
-COMMENT ON cashout_submissions.nexus_response
-  IS 'Keeps the Nexus response to the payment submission on failure'
+COMMENT ON TABLE pending_cashout_submissions
+  IS 'Tracks payment requests made from Sandbox to Nexus to trigger fiat 
transactions that finalize cash-outs.';
+COMMENT ON COLUMN pending_cashout_submissions.nexus_response
+  IS 'Keeps the Nexus response to the payment submission on failure';
 
 
 -- end of: cashout management
@@ -197,7 +208,7 @@ COMMENT ON cashout_submissions.nexus_response
 -- start of: EBICS management
 
 CREATE TABLE IF NOT EXISTS ebics_hosts 
-  (ebics_host_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (ebics_host_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,ebics_host_name TEXT NOT NULL
   ,ebics_version TEXT NOT NULL -- FIXME: This should be an enum
   -- FIXME: Do we want to specify the dialect here?
@@ -206,7 +217,7 @@ CREATE TABLE IF NOT EXISTS ebics_hosts
   );
 
 CREATE TABLE IF NOT EXISTS ebics_subscribers 
-  (ebics_subscriber_id SERIAL PRIMARY KEY
+  (ebics_subscriber_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,ebics_user_id TEXT NOT NULL
   ,ebics_partner_id TEXT NOT NULL
   ,ebics_system_id TEXT
@@ -219,11 +230,11 @@ CREATE TABLE IF NOT EXISTS ebics_subscribers
   -- FIXME: Do we need some information about the next order ID? There is a 
bug open.
   );
 COMMENT ON COLUMN ebics_subscribers.subscriber_state
-  IS 'Tracks the state changes of one subscriber.' -- Really needed?
+  IS 'Tracks the state changes of one subscriber.'; -- Really needed?
 
 -- FIXME: comment this table
 -- FIXME: indices on both columns individually
-CREATE TABLE ebics_subscribers_of_bank_accounts
+CREATE TABLE IF NOT EXISTS ebics_subscribers_of_bank_accounts
   (ebics_subscriber_id BIGINT NOT NULL
     REFERENCES ebics_subscribers(ebics_subscriber_id)
   ,bank_account_id BIGINT NOT NULL
@@ -253,12 +264,12 @@ CREATE TABLE IF NOT EXISTS ebics_download_transactions
   );
 
 COMMENT ON TABLE ebics_download_transactions
-  IS 'Tracks the evolution of one EBICS download transaction'
-COMMENT ON COLUMN ebics_download_transactions.ebics_host
-  IS 'EBICS host that governs this transaction' -- exists for a multi-host 
scenario.
+  IS 'Tracks the evolution of one EBICS download transaction';
+COMMENT ON COLUMN ebics_download_transactions.ebics_host_id
+  IS 'EBICS host that governs this transaction'; -- exists for a multi-host 
scenario.
 
 CREATE TABLE IF NOT EXISTS ebics_upload_transactions 
-  (transaction_id TEXT PRIMARY KEY
+  (ebics_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,order_type VARCHAR(3) NOT NULL
   -- EBICS3: ,btf_type TEXT -- fixme: document: in EBICS 2.x this can be NULL
   -- FIXME: see what else we need for EBICS 3
@@ -268,7 +279,7 @@ CREATE TABLE IF NOT EXISTS ebics_upload_transactions
       ON DELETE RESTRICT
       ON UPDATE RESTRICT
   ,ebics_subscriber BIGINT NOT NULL
-    REFERENCES ebics_subscribers(ebics_subscribers_id)
+    REFERENCES ebics_subscribers(ebics_subscriber_id)
       ON DELETE RESTRICT
       ON UPDATE RESTRICT
   ,num_segments INT NOT NULL
@@ -298,7 +309,7 @@ CREATE TABLE IF NOT EXISTS ebics_order_signatures
     ON UPDATE RESTRICT
   ,ebics_subscriber_id BIGINT
     REFERENCES ebics_subscribers(ebics_subscriber_id)
-    ON DELETE CASCASE
+    ON DELETE CASCADE
     ON UPDATE RESTRICT
   -- FIXME: do we also need to reference the ebics host? or does the 
subscriber uniquely determine it?
   ,order_id TEXT NOT NULL
@@ -308,7 +319,7 @@ CREATE TABLE IF NOT EXISTS ebics_order_signatures
   );
 
 COMMENT ON TABLE ebics_order_signatures
-  IS 'Keeps signature data collected from the subscribers.'
+  IS 'Keeps signature data collected from the subscribers.';
 
 -- end of: EBICS management
 
@@ -316,25 +327,23 @@ COMMENT ON TABLE ebics_order_signatures
 
 -- Really keep this table?  It tracks the EBICS reports.
 CREATE TABLE IF NOT EXISTS bank_account_reports 
-  (id BIGINT GENERATED BY IDENTITY
-  ,report_id TEXT NOT NULL
-  ,creation_time BIGINT NOT NULL
-  ,xml_message TEXT NOT NULL
-  ,bank_account BIGINT NOT NULL
-    REFERENCES bank_accounts(id)
-    ON DELETE CASCADE
-    ON UPDATE RESTRICT
+  (report_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+   ,creation_time BIGINT NOT NULL
+   ,xml_message TEXT NOT NULL
+   ,bank_account BIGINT NOT NULL
+      REFERENCES bank_accounts(bank_account_id)
+      ON DELETE CASCADE
+      ON UPDATE RESTRICT
   );
 
 -- Really keep this table?  It tracks the EBICS statements
 -- mostly because they are supposed never to change.  Not used
 CREATE TABLE IF NOT EXISTS bank_account_statements 
-  (id SERIAL PRIMARY KEY
-  ,statement_id TEXT NOT NULL
+  (statement_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,creation_time BIGINT NOT NULL
   ,xml_message TEXT NOT NULL
   ,bank_account BIGINT NOT NULL
-    REFERENCES bank_accounts(id)
+    REFERENCES bank_accounts(bank_account_id)
     ON DELETE CASCADE
     ON UPDATE RESTRICT
   ,balance_clbd TEXT NOT NULL -- FIXME: name. balance_closing?
@@ -350,7 +359,7 @@ CREATE TABLE IF NOT EXISTS taler_withdrawal_operations
   ,selection_done BOOLEAN DEFAULT FALSE NOT NULL
   ,aborted BOOLEAN DEFAULT FALSE NOT NULL
   ,confirmation_done BOOLEAN DEFAULT FALSE NOT NULL
-  ,reserve_pub BYTEA CHECK(len(reserve_pub)=32) NULL
+  ,reserve_pub BYTEA CHECK(length(reserve_pub)=32) NULL
   ,selected_exchange_payto TEXT
   ,wallet_bank_account BIGINT NOT NULL
     REFERENCES bank_accounts(id)
@@ -358,9 +367,9 @@ CREATE TABLE IF NOT EXISTS taler_withdrawal_operations
       ON UPDATE RESTRICT
   );
 COMMENT ON COLUMN taler_withdrawals.selection_done
-  IS 'Signals whether the wallet specified the exchange and gave the reserve 
public key'
+  IS 'Signals whether the wallet specified the exchange and gave the reserve 
public key';
 COMMENT ON COLUMN taler_withdrawals.confirmation_done
-  IS 'Signals whether the payment to the exchange took place'
+  IS 'Signals whether the payment to the exchange took place';
 
 -- end of: Taler integration
 COMMIT;
diff --git a/sandbox/src/test/kotlin/DatabaseTest.kt 
b/sandbox/src/test/kotlin/DatabaseTest.kt
new file mode 100644
index 00000000..5223cdf6
--- /dev/null
+++ b/sandbox/src/test/kotlin/DatabaseTest.kt
@@ -0,0 +1,2 @@
+class DatabaseTest {
+}
\ No newline at end of file

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.