[libeufin] branch master updated: Nexus DB schema refactoring: first pass.

[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 385456ea Nexus DB schema refactoring: first pass.
385456ea is described below

commit 385456ea8949e2aff219e81730cb13fa595baca3
Author: MS <ms@taler.net>
AuthorDate: Fri Jul 21 15:23:16 2023 +0200

    Nexus DB schema refactoring: first pass.
---
 database-versioning/new/nexus-0001-refactor.sql   | 208 ++++++++++++++++++++++
 database-versioning/new/sandbox-0001-refactor.sql |   3 +
 2 files changed, 211 insertions(+)

diff --git a/database-versioning/new/nexus-0001-refactor.sql 
b/database-versioning/new/nexus-0001-refactor.sql
new file mode 100644
index 00000000..dad533d4
--- /dev/null
+++ 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
index b8045979..ef9d1f1b 100644
--- 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:
 

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