[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[libeufin] branch master updated (94c8af5b -> 7440a4b8)
|
From: |
gnunet |
|
Subject: |
[libeufin] branch master updated (94c8af5b -> 7440a4b8) |
|
Date: |
Thu, 03 Aug 2023 17:04:34 +0200 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a change to branch master
in repository libeufin.
from 94c8af5b SQL comments
new 6a282641 edits
new 7440a4b8 SQL nexus by design
The 2 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
database-versioning/new/nexus-0001-refactor.sql | 364 ++++++++++++++----------
database-versioning/nexus-0001.sql | 32 +--
2 files changed, 221 insertions(+), 175 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 bbcd948b..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;
@@ -20,18 +5,23 @@ BEGIN;
SELECT _v.register_patch('nexus-0001', NULL, NULL);
CREATE TABLE IF NOT EXISTS nexususers
- (id BIGSERIAL PRIMARY KEY
- ,username TEXT NOT NULL
- ,"password" TEXT NOT NULL
- ,superuser BOOLEAN NOT NULL
+ (id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,username TEXT NOT NULL PRIMARY KEY
+ ,password TEXT 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?';
+
CREATE TABLE IF NOT EXISTS nexusbankconnections
(id BIGSERIAL PRIMARY KEY
,"connectionId" TEXT NOT NULL
- ,"type" TEXT NOT NULL
+ ,type TEXT NOT NULL
,dialect TEXT NULL
- ,"user" BIGINT NOT NULL
+ ,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.
- [libeufin] branch master updated (94c8af5b -> 7440a4b8),
gnunet <=