[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[libeufin] 02/02: SQL nexus by design
|
From: |
gnunet |
|
Subject: |
[libeufin] 02/02: SQL nexus by design |
|
Date: |
Thu, 03 Aug 2023 17:04:36 +0200 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository libeufin.
commit 7440a4b8ef2f24a95f0280e5b27bd88d93349365
Author: Christian Grothoff <grothoff@gnunet.org>
AuthorDate: Thu Aug 3 17:04:30 2023 +0200
SQL nexus by design
---
database-versioning/new/nexus-0001-refactor.sql | 364 ++++++++++++++----------
database-versioning/nexus-0001.sql | 17 +-
2 files changed, 211 insertions(+), 170 deletions(-)
diff --git a/database-versioning/new/nexus-0001-refactor.sql
b/database-versioning/new/nexus-0001-refactor.sql
index 60b0ce9a..d8e1cd7c 100644
--- a/database-versioning/new/nexus-0001-refactor.sql
+++ b/database-versioning/new/nexus-0001-refactor.sql
@@ -1,3 +1,18 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2023 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
-- To Do: comments, although '--' vs 'COMMENT ON' is under discussion.
BEGIN;
@@ -7,17 +22,33 @@ SELECT _v.register_patch('nexus-0001', NULL, NULL);
CREATE SCHEMA nexus;
SET search_path TO nexus;
+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';
+
+CREATE TYPE resource_enum
+ AS ENUM ('account', 'connection', 'facade');
+
-- start of: user management
-- This table accounts the users registered at Nexus
-- without any mention of banking connections.
-CREATE TABLE IF NOT EXISTS nexus_users
- (id BIGSERIAL PRIMARY KEY
- ,username TEXT NOT NULL
+CREATE TABLE IF NOT EXISTS nexus_logins
+ (nexus_login_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,login TEXT NOT NULL PRIMARY KEY
,password TEXT NOT NULL
- ,superuser BOOLEAN NOT NULL
+ ,superuser BOOLEAN NOT NULL DEFAULT (false)
);
+COMMENT ON TABLE nexususers
+ IS 'xxx';
+COMMENT ON COLUMN nexususers.password
+ IS 'hashed password - FIXME: which hash, how encoded, salted?';
+
-- end of: user management
-- start of: connection management
@@ -27,12 +58,12 @@ CREATE TABLE IF NOT EXISTS nexus_users
-- created in Nexus and points to their owners. NO connection
-- configuration details are supposed to exist here.
CREATE TABLE IF NOT EXISTS nexus_bank_connections
- (id BIGSERIAL PRIMARY KEY
- ,connection_id TEXT NOT NULL
- ,type TEXT NOT NULL
- ,dialect TEXT NULL
- ,user BIGINT NOT NULL
- ,CONSTRAINT fk_nexusbankconnections_user_id FOREIGN KEY (user) REFERENCES
nexus_users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ (connection_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,connection_label TEXT NOT NULL
+ ,connection_type TEXT NOT NULL
+ ,nexus_login_id BIGINT NOT NULL
+ REFERENCES nexus_users(nexus_login_id)
+ ON DELETE CASCADE ON UPDATE RESTRICT
);
@@ -40,33 +71,38 @@ CREATE TABLE IF NOT EXISTS nexus_bank_connections
-- nexus_bank_connections, where the meta information (like name and type)
-- about the connection is stored.
CREATE TABLE IF NOT EXISTS nexus_ebics_subscribers
- (id BIGSERIAL PRIMARY KEY
+ (subscriber_id BIGSERIAL PRIMARY KEY
,ebics_url TEXT NOT NULL
,host_id TEXT NOT NULL
,partner_id TEXT NOT NULL
- ,user_id TEXT NOT NULL
- ,system_id TEXT NULL
- ,signature_private_key bytea NOT NULL
- ,encryption_private_key bytea NOT NULL
- ,authentication_private_key bytea NOT NULL
- ,bank_encryption_public_key bytea NULL
- ,bank_authentication_public_key bytea NULL
- ,nexus_bank_connection BIGINT NOT NULL
+ ,nexus_login_id BIGINT NOT NULL
+ REFERENCES nexus_users(nexus_login_id)
+ ON DELETE CASCADE ON UPDATE RESTRICT
+ ,system_id TEXT DEFAULT (NULL)
+ ,dialect TEXT DEFAULT (NULL)
+ ,signature_private_key BYTEA NOT NULL
+ ,encryption_private_key BYTEA NOT NULL
+ ,authentication_private_key BYTEA NOT NULL
+ ,bank_encryption_public_key BYTEA DEFAULT(NULL)
+ ,bank_authentication_public_key BYTEA NULL
+ ,connection_id BIGINT NOT NULL
+ REFERENCES nexus_bank_connections(connection_id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
,ebics_ini_state VARCHAR(16) NOT NULL
,ebics_hia_state VARCHAR(16) NOT NULL
- ,CONSTRAINT fk_nexusebicssubscribers_nexusbankconnection_id FOREIGN KEY
(nexus_bank_connection) REFERENCES nexus_bank_connections(id) ON DELETE
RESTRICT ON UPDATE RESTRICT
);
-- Details of one X-LIBEUFIN-BANK connection. In other
-- words, each line is one Libeufin-Sandbox user.
CREATE TABLE IF NOT EXISTS xlibeufin_bank_users
- (id BIGSERIAL PRIMARY KEY
- ,username TEXT NOT NULL
- ,password TEXT NOT NULL
- ,base_url TEXT NOT NULL
- ,nexus_bank_connection BIGINT NOT NULL
- ,CONSTRAINT fk_xlibeufinbankusers_nexusbankconnection_id FOREIGN KEY
(nexus_bank_connection) REFERENCES nexus_bank_connections(id) ON DELETE
RESTRICT ON UPDATE RESTRICT
+ (bank_user_id BIGSERIAL PRIMARY KEY
+ ,bank_username TEXT NOT NULL
+ ,bank_password TEXT NOT NULL
+ ,bank_base_url TEXT NOT NULL
+ ,bank_connection_id BIGINT NOT NULL
+ REFERENCES nexus_bank_connections(connection_id)
+ ON DELETE CASCADE ON UPDATE RESTRICT
);
@@ -77,15 +113,20 @@ CREATE TABLE IF NOT EXISTS xlibeufin_bank_users
-- providing friendlier names to the Nexus side of one bank
-- account.
CREATE TABLE IF NOT EXISTS offered_bank_accounts
- (id BIGSERIAL PRIMARY KEY
+ (offered_bank_account_id BIGSERIAL PRIMARY KEY
,offered_account_id TEXT NOT NULL
- ,bank_connection BIGINT NOT NULL
+ ,connection_id BIGINT NOT NULL
+ REFERENCES nexusbankconnections(connection_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
,iban TEXT NOT NULL
,bank_code TEXT NOT NULL
,holder_name TEXT NOT NULL
- ,imported BIGINT NULL
- ,CONSTRAINT fk_offeredbankaccounts_bankconnection_id FOREIGN KEY
(bank_connection) REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON
UPDATE RESTRICT
- ,CONSTRAINT fk_offeredbankaccounts_imported_id FOREIGN KEY (imported)
REFERENCES nexus_bank_accounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,imported BIGINT DEFAULT(NULL)
+ REFERENCES nexus_bank_accounts(account_id)
+ ON DELETE RESTRICT
+ ON UPDATE RESTRICT
+ ,UNIQUE (offered_account_id, connection_id)
);
-- end of: connection management
@@ -96,7 +137,7 @@ CREATE TABLE IF NOT EXISTS offered_bank_accounts
-- Accounts for the background tasks that were created by the user.
CREATE TABLE IF NOT EXISTS nexus_scheduled_tasks
(id BIGSERIAL PRIMARY KEY
- ,resource_type TEXT NOT NULL
+ ,resource_type resource_enum NOT NULL
,resource_id TEXT NOT NULL
,task_name TEXT NOT NULL
,task_type TEXT NOT NULL
@@ -108,71 +149,161 @@ CREATE TABLE IF NOT EXISTS nexus_scheduled_tasks
-- end of: background tasks
+-- start of: core banking
+
+-- A bank account managed by Nexus. Each row corresponds to an
+-- actual bank account at the bank and that is owned by the 'account_holder'
+-- column. FIXME: is account_holder a name or a user-name?
+CREATE TABLE IF NOT EXISTS nexus_bank_accounts
+ (nexus_account_id BIGSERIAL PRIMARY KEY
+ ,nexus_account_label TEXT NOT NULL UNIQUE
+ ,nexus_account_holder TEXT NOT NULL
+ ,iban TEXT NOT NULL
+ ,bank_code TEXT NOT NULL
+ ,default_connection_id BIGINT DEFAULT(NULL)
+ REFERENCES nexus_bank_connections(connection_id)
+ ON DELETE SET TO NULL -- FIXME: sql syntax?
+ ,last_statement_creation_timestamp BIGINT NULL
+ ,last_report_creation_timestamp BIGINT NULL
+ ,last_notification_creation_timestamp BIGINT NULL
+ ,highest_seen_bank_message_serial_id BIGINT NOT NULL
+ ,pain001counter BIGINT DEFAULT 1 NOT NULL -- keep?
+ );
+
-- start of: facades management
+CREATE TABLE IF NOT EXISTS facades
+ (facade_id BIGSERIAL PRIMARY KEY
+ ,facade_label TEXT NOT NULL UNIQUE
+ ,facace_type TEXT NOT NULL
+ ,creator_login_id BIGINT NOT NULL
+ REFERENCES nexus_logins(nexus_login_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
+ );
+
-- Basic information about the facade state.
-CREATE TABLE IF NOT EXISTS facade_state
- (id BIGSERIAL PRIMARY KEY
- ,bank_account TEXT NOT NULL
- ,bank_connection TEXT NOT NULL
- ,currency TEXT NOT NULL
+CREATE TABLE IF NOT EXISTS wire_gateway_facade_state
+ (wire_gateway_facade_state_id BIGSERIAL PRIMARY KEY
+ ,nexus_bank_account BIGINT NOT NULL
+ REFERENCES nexus_bank_accounts(nexus_account_id)
+ ,connection_id BIGINT NOT NULL
+ REFERENCES nexus_bank_connections (connection_id)
+ -- Taler maximum is 11 plus 0-terminator
+ ,currency VARCHAR(11) NOT NULL
-- The following column informs whether this facade
-- wants payment data to come from statements (usually
-- once a day when the payment is very likely settled),
-- reports (multiple times a day but the payment might
- -- not be settled).
+ -- not be settled). "report" or "statement" or "notification"
,reserve_transfer_level TEXT NOT NULL
- ,facade BIGINT NOT NULL
+ ,facade_id BIGINT NOT NULL
+ REFERENCES facades(id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
-- The following column points to the last transaction
-- that was processed already by the facade. It's used
-- along the facade-specific ingestion.
,highest_seen_message_serial_id BIGINT DEFAULT 0 NOT NULL
- ,CONSTRAINT fk_facadestate_facade_id FOREIGN KEY (facade) REFERENCES
facades(id) ON DELETE CASCADE ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS facades
- (id BIGSERIAL PRIMARY KEY
- ,facade_name TEXT NOT NULL UNIQUE
- ,type TEXT NOT NULL
- ,creator BIGINT NOT NULL
- ,CONSTRAINT fk_facades_creator_id FOREIGN KEY (creator) REFERENCES
nexus_users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- );
CREATE TABLE IF NOT EXISTS nexus_permissions
- (id BIGSERIAL PRIMARY KEY
- ,resource_type TEXT NOT NULL
- ,resource_id TEXT NOT NULL
- ,subject_type TEXT NOT NULL
- ,subject_name TEXT NOT NULL
- ,permission_name TEXT NOT NULL
+ (permission_id BIGSERIAL PRIMARY KEY
+ ,resource_type resource_enum NOT NULL
+ ,resource_id BIGINT NOT NULL -- comment: references X/Y/Z depending on
resource_type
+ ,subject_type TEXT NOT NULL -- fixme: enum?
+ ,subject_name TEXT NOT NULL -- fixme: bigint?
+ ,permission_name TEXT NOT NULL -- fixme: enum!
+ ,UNIQUE(resource_type, resource_id, subject_type, subject_name,
permission_name)
);
-- end of: general facades management
-- start of: Taler facade management
+-- All the payments that were ingested by Nexus. Each row
+-- points at the Nexus bank account that is related to the transaction.
+-- FIXME: explain 'updated_by'.
+CREATE TABLE IF NOT EXISTS nexus_bank_transactions
+ (transaction_id BIGSERIAL PRIMARY KEY
+ ,account_transaction_id TEXT NOT NULL
+ ,nexus_account_account_id NOT NULL
+ REFERENCES nexus_bank_accounts(nexus_account_id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,credit_debit_indicator TEXT NOT NULL -- FIXME: enum
+ ,currency TEXT NOT NULL
+ ,amount taler_amount NOT NULL
+ ,status VARCHAR(16) NOT NULL -- FIXME: enum
+ ,transaction_json TEXT NOT NULL
+ );
+
+
-- Holds valid Taler payments, typically those that are returned
-- to the Wirewatch by the Taler facade.
CREATE TABLE IF NOT EXISTS taler_incoming_payments
- (id BIGSERIAL PRIMARY KEY
- ,payment NOT NULL REFERENCES nexus_bank_transactions(id) ON DELETE RESTRICT
ON UPDATE RESTRICT
- ,reserve_public_key TEXT NOT NULL
- ,timestamp_ms BIGINT NOT NULL
+ (taler_payment_id BIGSERIAL PRIMARY KEY
+ ,transaction_id NOT NULL
+ REFERENCES nexus_bank_transactions(transaction_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
+ ,reserve_public_key BYTEA CHECK(LENGTH(reserve_public_key)=32)
+ ,timestamp_ms BIGINT NOT NULL -- change to GNUnet-style timestamps
(microseconds, not ms)
,incoming_payto_uri TEXT NOT NULL
);
+
+-- Table holding the data that represent one outgoing payment
+-- made by the (user owning the) 'bank_account'. The 'raw_confirmation'
+-- column points at the global table of all the ingested payments
+-- where the pointed ingested payment is the confirmation that the
+-- pointing payment initiation was finalized at the bank. All
+-- the IDs involved in this table mimic the semantics of ISO20022 pain.001.
+CREATE TABLE IF NOT EXISTS payment_initiations
+ (payment_initiation_id BIGSERIAL PRIMARY KEY
+ ,nexus_bank_account_id BIGINT NOT NULL
+ REFERENCES nexus_bank_accounts(nexus_bank_account_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
+ ,preparation_date BIGINT NOT NULL
+ ,submission_date BIGINT NULL
+ ,transaction_sum taler_amount NOT NULL
+ ,currency TEXT NOT NULL
+ ,end_to_end_id TEXT NOT NULL
+ ,payment_information_id TEXT NOT NULL
+ ,instruction_id TEXT NOT NULL
+ ,subject TEXT NOT NULL
+ ,creditor_iban TEXT NOT NULL
+ ,creditor_bic TEXT NULL
+ ,creditor_name TEXT NOT NULL
+ ,submitted BOOLEAN DEFAULT FALSE NOT NULL
+ ,invalid BOOLEAN -- document NULL case
+ ,message_id TEXT NOT NULL
+ ,confirmation_transaction_id BIGINT NULL
+ REFERENCES nexus_bank_transactions(transaction_id)
+ ON DELETE SET TO NULL -- fixme: sql syntax?
+ ON UPDATE RESTRICT
+ );
+
+
-- This table holds the outgoing payments that were requested
-- by the exchange to pay merchants. The columns reflect the
-- data model of the /transfer call from the TWG.
CREATE TABLE IF NOT EXISTS taler_requested_payments
- (id BIGSERIAL PRIMARY KEY
- ,facade NOT NULL REFERENCES facades(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- ,payment NOT NULL REFERENCES payment_initiations(id) ON DELETE RESTRICT ON
UPDATE RESTRICT
+ (taler_payment_request_id BIGSERIAL PRIMARY KEY
+ ,facade_id NOT NULL
+ REFERENCES facades(facade_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
+ ,payment_initiation_id NOT NULL
+ REFERENCES payment_initiations(payment_initiation_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
,request_uid TEXT NOT NULL
- ,amount TEXT NOT NULL
+ ,amount taler_amount NOT NULL -- currency from facade
,exchange_base_url TEXT NOT NULL
,wtid TEXT NOT NULL
- ,credit_account TEXT NOT NULL
+ ,credit_account TEXT NOT NULL -- add _payto_uri?
);
@@ -180,9 +311,11 @@ CREATE TABLE IF NOT EXISTS taler_requested_payments
-- key as the subject. The 'payment' columns points at the ingested
-- transaction that is invalid in the Taler sense.
CREATE TABLE IF NOT EXISTS taler_invalid_incoming_payments
- (id BIGSERIAL PRIMARY KEY
- ,payment NOT NULL REFERENCES nexus_bank_transactions(id) ON DELETE RESTRICT
ON UPDATE RESTRICT
- ,timestamp_ms BIGINT NOT NULL
+ (taler_invalid_incoming_payment_id BIGSERIAL PRIMARY KEY
+ ,transaction_id NOT NULL
+ REFERENCES nexus_bank_transactions(transaction_id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,timestamp_ms BIGINT NOT NULL -- FIXME: use GNUnet style timestamp
,refunded BOOLEAN DEFAULT false NOT NULL
);
@@ -191,89 +324,17 @@ CREATE TABLE IF NOT EXISTS taler_invalid_incoming_payments
-- start of: Anastasis facade management
CREATE TABLE IF NOT EXISTS anastasis_incoming_payments
- (id BIGSERIAL PRIMARY KEY
- ,payment NOT NULL REFERENCES nexus_bank_transactions(id) ON DELETE RESTRICT
ON UPDATE RESTRICT
+ (anastasis_incoming_payments_id BIGSERIAL PRIMARY KEY
+ ,transaction_id NOT NULL
+ REFERENCES nexus_bank_transactions(id)
+ ON DELETE RESTRICT ON UPDATE RESTRICT
,subject TEXT NOT NULL
- ,timestamp_ms BIGINT NOT NULL
+ ,timestamp_ms BIGINT NOT NULL -- FIXME: use GNUnet style payments
,incoming_payto_uri TEXT NOT NULL
);
-- end of: Anastasis facade management
--- start of: core banking
-
--- A bank account managed by Nexus. Each row corresponds to an
--- actual bank account at the bank and that is owned by the 'account_holder'
--- column. FIXME: is account_holder a name or a user-name?
-CREATE TABLE IF NOT EXISTS nexus_bank_accounts
- (id BIGSERIAL PRIMARY KEY
- ,bank_account_id TEXT NOT NULL UNIQUE
- ,account_holder TEXT NOT NULL
- ,iban TEXT NOT NULL
- ,bank_code TEXT NOT NULL
- ,default_bank_connection BIGINT NULL
- ,last_statement_creation_timestamp BIGINT NULL
- ,last_report_creation_timestamp BIGINT NULL
- ,last_notification_creation_timestamp BIGINT NULL
- ,highest_seen_bank_message_serial_id BIGINT NOT NULL
- ,pain001counter BIGINT DEFAULT 1 NOT NULL
- ,CONSTRAINT fk_nexusbankaccounts_defaultbankconnection_id FOREIGN KEY
(default_bank_connection) REFERENCES nexus_bank_connections(id) ON DELETE
RESTRICT ON UPDATE RESTRICT
- );
-
-
--- All the payments that were ingested by Nexus. Each row
--- points at the Nexus bank account that is related to the transaction.
--- FIXME: explain 'updated_by'.
-CREATE TABLE IF NOT EXISTS nexus_bank_transactions
- (id BIGSERIAL PRIMARY KEY
- ,account_transaction_id TEXT NOT NULL
- ,bank_account NOT NULL REFERENCES nexus_bank_accounts(id) ON DELETE RESTRICT
ON UPDATE RESTRICT
- ,credit_debit_indicator TEXT NOT NULL
- ,currency TEXT NOT NULL
- ,amount TEXT NOT NULL
- ,status VARCHAR(16) NOT NULL
- ,updated_by BIGINT NULL REFERENCES nexus_bank_transactions(id) ON DELETE
RESTRICT ON UPDATE RESTRICT
- ,transaction_json TEXT NOT NULL
- );
-
--- Table holding the data that represent one outgoing payment
--- made by the (user owning the) 'bank_account'. The 'raw_confirmation'
--- column points at the global table of all the ingested payments
--- where the pointed ingested payment is the confirmation that the
--- pointing payment initiation was finalized at the bank. All
--- the IDs involved in this table mimic the semantics of ISO20022 pain.001.
-CREATE TABLE IF NOT EXISTS payment_initiations
- (id BIGSERIAL PRIMARY KEY
- ,bank_account NOT NULL REFERENCES nexus_bank_accounts(id) ON DELETE RESTRICT
ON UPDATE RESTRICT
- ,preparation_date BIGINT NOT NULL
- ,submission_date BIGINT NULL
- ,sum TEXT NOT NULL
- ,currency TEXT NOT NULL
- ,end_to_end_id TEXT NOT NULL
- ,payment_information_id TEXT NOT NULL
- ,instruction_id TEXT NOT NULL
- ,subject TEXT NOT NULL
- ,creditor_iban TEXT NOT NULL
- ,creditor_bic TEXT NULL
- ,creditor_name TEXT NOT NULL
- ,submitted BOOLEAN DEFAULT false NOT NULL
- ,invalid BOOLEAN NULL
- ,message_id TEXT NOT NULL
- ,raw_confirmation BIGINT NULL REFERENCES nexus_bank_transactions(id) ON
DELETE RESTRICT ON UPDATE RESTRICT
- );
-
--- This table stores user balances for a certain bank account.
--- It was however never used, plus it needs the collaboration
--- of the bank, since giving balances along the ISO20022 is not
--- mandatory.
-CREATE TABLE IF NOT EXISTS nexus_bank_balances
- (id BIGSERIAL PRIMARY KEY
- ,balance TEXT NOT NULL
- ,credit_debit_indicator TEXT NOT NULL
- ,bank_account BIGINT NOT NULL
- ,date TEXT NOT NULL
- ,CONSTRAINT fk_nexusbankbalances_bankaccount_id FOREIGN KEY (bank_account)
REFERENCES nexus_bank_accounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- );
-- This table holds the business content that came from the
@@ -281,22 +342,17 @@ CREATE TABLE IF NOT EXISTS nexus_bank_balances
-- or when the storing is enabled. By default, successful messages
-- are never stored.
CREATE TABLE IF NOT EXISTS nexus_bank_messages
- (id BIGSERIAL PRIMARY KEY
+ (bank_message_id BIGSERIAL PRIMARY KEY
,bank_connection BIGINT NOT NULL
- ,message bytea NOT NULL
+ REFERENCES nexus_bank_connections(connection_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
+ ,message BYTEA NOT NULL
,message_id TEXT NULL
- ,fetch_level VARCHAR(16) NOT NULL -- report, statement or notification?
- ,errors BOOLEAN DEFAULT false NOT NULL
- ,CONSTRAINT fk_nexusbankmessages_bankconnection_id FOREIGN KEY
(bank_connection) REFERENCES nexus_bank_connections(id) ON DELETE RESTRICT ON
UPDATE RESTRICT
+ ,fetch_level VARCHAR(16) NOT NULL -- report, statement or notification? --
FIXME: enum?
+ ,errors BOOLEAN DEFAULT FALSE NOT NULL
);
--- Tuple made by the account name as it is offered by the bank
--- and the associated connection name.
-ALTER TABLE
- offered_bank_accounts ADD CONSTRAINT
offeredbankaccounts_offeredaccountid_bankconnection_unique UNIQUE
(offered_account_id, bank_connection);
-
-ALTER TABLE
- nexus_permissions ADD CONSTRAINT
nexuspermissions_resourcetype_resourceid_subjecttype_subjectnam UNIQUE
(resource_type, resource_id, subject_type, subject_name, permission_name);
-- end of: core banking
diff --git a/database-versioning/nexus-0001.sql
b/database-versioning/nexus-0001.sql
index 1504061c..8c9e076f 100644
--- a/database-versioning/nexus-0001.sql
+++ b/database-versioning/nexus-0001.sql
@@ -1,18 +1,3 @@
---
--- This file is part of TALER
--- Copyright (C) 2023 Taler Systems SA
---
--- TALER is free software; you can redistribute it and/or modify it under the
--- terms of the GNU General Public License as published by the Free Software
--- Foundation; either version 3, or (at your option) any later version.
---
--- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
--- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
--- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
---
--- You should have received a copy of the GNU General Public License along with
--- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
---
-- Compatible with LibEuFin version: 1fe2687aaf696c8566367fe7ed082f1d78e6b78d
BEGIN;
@@ -36,7 +21,7 @@ CREATE TABLE IF NOT EXISTS nexusbankconnections
,"connectionId" TEXT NOT NULL
,type TEXT NOT NULL
,dialect TEXT NULL
- ,user BIGINT NOT NULL REFERENCES nexususers(id) ON DELETE CASCADE
+ ,user BIGINT NOT NULL
,CONSTRAINT fk_nexusbankconnections_user_id FOREIGN KEY ("user") REFERENCES
nexususers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.