commit 385456ea8949e2aff219e81730cb13fa595baca3
parent 2ff15fb7d16ca90d0df4f79b987e5e12b7a36d8d
Author: MS <ms@taler.net>
Date: Fri, 21 Jul 2023 15:23:16 +0200
Nexus DB schema refactoring: first pass.
Diffstat:
2 files changed, 211 insertions(+), 0 deletions(-)
diff --git a/database-versioning/new/nexus-0001-refactor.sql b/database-versioning/new/nexus-0001-refactor.sql
@@ -0,0 +1,208 @@
+-- Compatible with LibEuFin version: 1fe2687aaf696c8566367fe7ed082f1d78e6b78d
+
+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
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankconnections
+ (id BIGSERIAL PRIMARY KEY
+ ,"connectionId" TEXT NOT NULL
+ ,"type" TEXT NOT NULL
+ ,dialect TEXT NULL
+ ,"user" BIGINT NOT NULL
+ ,CONSTRAINT fk_nexusbankconnections_user_id FOREIGN KEY ("user") REFERENCES nexususers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS xlibeufinbankusers
+ (id BIGSERIAL PRIMARY KEY
+ ,username TEXT NOT NULL
+ ,"password" TEXT NOT NULL
+ ,"baseUrl" TEXT NOT NULL
+ ,"nexusBankConnection" BIGINT NOT NULL
+ ,CONSTRAINT fk_xlibeufinbankusers_nexusbankconnection_id FOREIGN KEY ("nexusBankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS nexusscheduledtasks
+ (id BIGSERIAL PRIMARY KEY
+ ,"resourceType" TEXT NOT NULL
+ ,"resourceId" TEXT NOT NULL
+ ,"taskName" TEXT NOT NULL
+ ,"taskType" TEXT NOT NULL
+ ,"taskCronspec" TEXT NOT NULL
+ ,"taskParams" TEXT NOT NULL
+ ,"nextScheduledExecutionSec" BIGINT NULL
+ ,"lastScheduledExecutionSec" BIGINT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankaccounts
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankAccountId" TEXT NOT NULL UNIQUE
+ ,"accountHolder" TEXT NOT NULL
+ ,iban TEXT NOT NULL
+ ,"bankCode" TEXT NOT NULL
+ ,"defaultBankConnection" BIGINT NULL
+ ,"lastStatementCreationTimestamp" BIGINT NULL
+ ,"lastReportCreationTimestamp" BIGINT NULL
+ ,"lastNotificationCreationTimestamp" BIGINT NULL
+ ,"highestSeenBankMessageSerialId" BIGINT NOT NULL
+ ,pain001counter BIGINT DEFAULT 1 NOT NULL
+ ,CONSTRAINT fk_nexusbankaccounts_defaultbankconnection_id FOREIGN KEY ("defaultBankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbanktransactions
+ (id BIGSERIAL PRIMARY KEY
+ ,"accountTransactionId" TEXT NOT NULL
+ ,"bankAccount" NOT NULL REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,"creditDebitIndicator" TEXT NOT NULL
+ ,currency TEXT NOT NULL
+ ,amount TEXT NOT NULL
+ ,status VARCHAR(16) NOT NULL
+ ,"updatedBy" BIGINT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,"transactionJson" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS paymentinitiations
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankAccount" NOT NULL REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,"preparationDate" BIGINT NOT NULL
+ ,"submissionDate" BIGINT NULL
+ ,"sum" TEXT NOT NULL
+ ,currency TEXT NOT NULL
+ ,"endToEndId" TEXT NOT NULL
+ ,"paymentInformationId" TEXT NOT NULL
+ ,"instructionId" TEXT NOT NULL
+ ,subject TEXT NOT NULL
+ ,"creditorIban" TEXT NOT NULL
+ ,"creditorBic" TEXT NULL
+ ,"creditorName" TEXT NOT NULL
+ ,submitted BOOLEAN DEFAULT false NOT NULL
+ ,invalid BOOLEAN NULL
+ ,"messageId" TEXT NOT NULL
+ ,"rawConfirmation" BIGINT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS nexusebicssubscribers
+ (id BIGSERIAL PRIMARY KEY
+ ,"ebicsURL" TEXT NOT NULL
+ ,"hostID" TEXT NOT NULL
+ ,"partnerID" TEXT NOT NULL
+ ,"userID" TEXT NOT NULL
+ ,"systemID" TEXT NULL
+ ,"signaturePrivateKey" bytea NOT NULL
+ ,"encryptionPrivateKey" bytea NOT NULL
+ ,"authenticationPrivateKey" bytea NOT NULL
+ ,"bankEncryptionPublicKey" bytea NULL
+ ,"bankAuthenticationPublicKey" bytea NULL
+ ,"nexusBankConnection" BIGINT NOT NULL
+ ,"ebicsIniState" VARCHAR(16) NOT NULL
+ ,"ebicsHiaState" VARCHAR(16) NOT NULL
+ ,CONSTRAINT fk_nexusebicssubscribers_nexusbankconnection_id FOREIGN KEY ("nexusBankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankbalances
+ (id BIGSERIAL PRIMARY KEY
+ ,balance TEXT NOT NULL
+ ,"creditDebitIndicator" TEXT NOT NULL
+ ,"bankAccount" BIGINT NOT NULL
+ ,"date" TEXT NOT NULL
+ ,CONSTRAINT fk_nexusbankbalances_bankaccount_id FOREIGN KEY ("bankAccount") REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS anastasisincomingpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,payment NOT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,subject TEXT NOT NULL
+ ,"timestampMs" BIGINT NOT NULL
+ ,"incomingPaytoUri" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS talerincomingpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,payment NOT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,"reservePublicKey" TEXT NOT NULL
+ ,"timestampMs" BIGINT NOT NULL
+ ,"incomingPaytoUri" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS facades
+ (id BIGSERIAL PRIMARY KEY
+ ,"facadeName" TEXT NOT NULL UNIQUE
+ ,"type" TEXT NOT NULL
+ ,creator BIGINT NOT NULL
+ ,CONSTRAINT fk_facades_creator_id FOREIGN KEY (creator) REFERENCES nexususers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS talerrequestedpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,facade NOT NULL REFERENCES facades(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,payment NOT NULL REFERENCES paymentinitiations(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,"requestUid" TEXT NOT NULL
+ ,amount TEXT NOT NULL
+ ,"exchangeBaseUrl" TEXT NOT NULL
+ ,wtid TEXT NOT NULL
+ ,"creditAccount" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS facadestate
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankAccount" TEXT NOT NULL
+ ,"bankConnection" TEXT NOT NULL
+ ,currency TEXT NOT NULL
+ ,"reserveTransferLevel" TEXT NOT NULL
+ ,facade BIGINT NOT NULL
+ ,"highestSeenMessageSerialId" 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 talerinvalidincomingpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,payment NOT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,"timestampMs" BIGINT NOT NULL
+ ,refunded BOOLEAN DEFAULT false NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankmessages
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankConnection" BIGINT NOT NULL
+ ,message bytea NOT NULL
+ ,"messageId" TEXT NULL
+ ,"fetchLevel" VARCHAR(16) NOT NULL
+ ,errors BOOLEAN DEFAULT false NOT NULL
+ ,CONSTRAINT fk_nexusbankmessages_bankconnection_id FOREIGN KEY ("bankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS offeredbankaccounts
+ (id BIGSERIAL PRIMARY KEY
+ ,"offeredAccountId" TEXT NOT NULL
+ ,"bankConnection" BIGINT NOT NULL
+ ,iban TEXT NOT NULL
+ ,"bankCode" TEXT NOT NULL
+ ,"holderName" TEXT NOT NULL
+ ,imported BIGINT NULL
+ ,CONSTRAINT fk_offeredbankaccounts_bankconnection_id FOREIGN KEY ("bankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,CONSTRAINT fk_offeredbankaccounts_imported_id FOREIGN KEY (imported) REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+ALTER TABLE
+ offeredbankaccounts ADD CONSTRAINT offeredbankaccounts_offeredaccountid_bankconnection_unique UNIQUE ("offeredAccountId", "bankConnection");
+
+CREATE TABLE IF NOT EXISTS nexuspermissions
+ (id BIGSERIAL PRIMARY KEY
+ ,"resourceType" TEXT NOT NULL
+ ,"resourceId" TEXT NOT NULL
+ ,"subjectType" TEXT NOT NULL
+ ,"subjectName" TEXT NOT NULL
+ ,"permissionName" TEXT NOT NULL
+ );
+
+ALTER TABLE
+ nexuspermissions ADD CONSTRAINT nexuspermissions_resourcetype_resourceid_subjecttype_subjectnam UNIQUE ("resourceType", "resourceId", "subjectType", "subjectName", "permissionName");
+
+COMMIT
diff --git a/database-versioning/new/sandbox-0001-refactor.sql b/database-versioning/new/sandbox-0001-refactor.sql
@@ -1,3 +1,6 @@
+-- To Do:
+
+-- Use camel case.
-- Under discussion:
