commit 9084386204326af2731765a50b32ade22495f756
parent e8f5586203391569159401156b7694314bef675b
Author: MS <ms@taler.net>
Date: Fri, 21 Jul 2023 21:23:45 +0200
Neuxs DB schema: using camel case.
Diffstat:
1 file changed, 37 insertions(+), 36 deletions(-)
diff --git a/database-versioning/new/nexus-0001-refactor.sql b/database-versioning/new/nexus-0001-refactor.sql
@@ -1,35 +1,36 @@
--- Compatible with LibEuFin version: 1fe2687aaf696c8566367fe7ed082f1d78e6b78d
+-- To Do: group tables per purpose.
+
BEGIN;
SELECT _v.register_patch('nexus-0001', NULL, NULL);
-CREATE TABLE IF NOT EXISTS nexususers
+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
+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
+ ,CONSTRAINT fk_nexusbankconnections_user_id FOREIGN KEY ("user") REFERENCES NexusUsers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS xlibeufinbankusers
+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
+ ,CONSTRAINT fk_xlibeufinbankusers_nexusbankconnection_id FOREIGN KEY ("nexusBankConnection") REFERENCES NexusBankConnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS nexusscheduledtasks
+CREATE TABLE IF NOT EXISTS NexusScheduledTasks
(id BIGSERIAL PRIMARY KEY
,"resourceType" TEXT NOT NULL
,"resourceId" TEXT NOT NULL
@@ -41,7 +42,7 @@ CREATE TABLE IF NOT EXISTS nexusscheduledtasks
,"lastScheduledExecutionSec" BIGINT NULL
);
-CREATE TABLE IF NOT EXISTS nexusbankaccounts
+CREATE TABLE IF NOT EXISTS NexusBankAccounts
(id BIGSERIAL PRIMARY KEY
,"bankAccountId" TEXT NOT NULL UNIQUE
,"accountHolder" TEXT NOT NULL
@@ -53,24 +54,24 @@ CREATE TABLE IF NOT EXISTS nexusbankaccounts
,"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
+ ,CONSTRAINT fk_nexusbankaccounts_defaultbankconnection_id FOREIGN KEY ("defaultBankConnection") REFERENCES NexusBankConnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS nexusbanktransactions
+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
+ ,"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
+ ,"updatedBy" BIGINT NULL REFERENCES NexusBankTransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
,"transactionJson" TEXT NOT NULL
);
-CREATE TABLE IF NOT EXISTS paymentinitiations
+CREATE TABLE IF NOT EXISTS PaymentInitiations
(id BIGSERIAL PRIMARY KEY
- ,"bankAccount" NOT NULL REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,"bankAccount" NOT NULL REFERENCES NexusBankAccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
,"preparationDate" BIGINT NOT NULL
,"submissionDate" BIGINT NULL
,"sum" TEXT NOT NULL
@@ -85,10 +86,10 @@ CREATE TABLE IF NOT EXISTS paymentinitiations
,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
+ ,"rawConfirmation" BIGINT NULL REFERENCES NexusBankTransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS nexusebicssubscribers
+CREATE TABLE IF NOT EXISTS NexusEbicsSubscribers
(id BIGSERIAL PRIMARY KEY
,"ebicsURL" TEXT NOT NULL
,"hostID" TEXT NOT NULL
@@ -103,29 +104,29 @@ CREATE TABLE IF NOT EXISTS nexusebicssubscribers
,"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
+ ,CONSTRAINT fk_nexusebicssubscribers_nexusbankconnection_id FOREIGN KEY ("nexusBankConnection") REFERENCES NexusBankConnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS nexusbankbalances
+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
+ ,CONSTRAINT fk_nexusbankbalances_bankaccount_id FOREIGN KEY ("bankAccount") REFERENCES NexusBankAccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS anastasisincomingpayments
+CREATE TABLE IF NOT EXISTS AnastasisIncomingPayments
(id BIGSERIAL PRIMARY KEY
- ,payment NOT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,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
+CREATE TABLE IF NOT EXISTS TalerIncomingPayments
(id BIGSERIAL PRIMARY KEY
- ,payment NOT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,payment NOT NULL REFERENCES NexusBankTransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
,"reservePublicKey" TEXT NOT NULL
,"timestampMs" BIGINT NOT NULL
,"incomingPaytoUri" TEXT NOT NULL
@@ -136,13 +137,13 @@ CREATE TABLE IF NOT EXISTS facades
,"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
+ ,CONSTRAINT fk_facades_creator_id FOREIGN KEY (creator) REFERENCES NexusUsers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS talerrequestedpayments
+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
+ ,payment NOT NULL REFERENCES PaymentInitiations(id) ON DELETE RESTRICT ON UPDATE RESTRICT
,"requestUid" TEXT NOT NULL
,amount TEXT NOT NULL
,"exchangeBaseUrl" TEXT NOT NULL
@@ -150,7 +151,7 @@ CREATE TABLE IF NOT EXISTS talerrequestedpayments
,"creditAccount" TEXT NOT NULL
);
-CREATE TABLE IF NOT EXISTS facadestate
+CREATE TABLE IF NOT EXISTS FacadeState
(id BIGSERIAL PRIMARY KEY
,"bankAccount" TEXT NOT NULL
,"bankConnection" TEXT NOT NULL
@@ -161,24 +162,24 @@ CREATE TABLE IF NOT EXISTS facadestate
,CONSTRAINT fk_facadestate_facade_id FOREIGN KEY (facade) REFERENCES facades(id) ON DELETE CASCADE ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS talerinvalidincomingpayments
+CREATE TABLE IF NOT EXISTS TalerInvalidIncomingPayments
(id BIGSERIAL PRIMARY KEY
- ,payment NOT NULL REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,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
+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
+ ,CONSTRAINT fk_nexusbankmessages_bankconnection_id FOREIGN KEY ("bankConnection") REFERENCES NexusBankConnections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS offeredbankaccounts
+CREATE TABLE IF NOT EXISTS OfferedBankAccounts
(id BIGSERIAL PRIMARY KEY
,"offeredAccountId" TEXT NOT NULL
,"bankConnection" BIGINT NOT NULL
@@ -187,13 +188,13 @@ CREATE TABLE IF NOT EXISTS offeredbankaccounts
,"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
+ ,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");
+ OfferedBankAccounts ADD CONSTRAINT offeredbankaccounts_offeredaccountid_bankconnection_unique UNIQUE ("offeredAccountId", "bankConnection");
-CREATE TABLE IF NOT EXISTS nexuspermissions
+CREATE TABLE IF NOT EXISTS NexusPermissions
(id BIGSERIAL PRIMARY KEY
,"resourceType" TEXT NOT NULL
,"resourceId" TEXT NOT NULL
@@ -203,6 +204,6 @@ CREATE TABLE IF NOT EXISTS nexuspermissions
);
ALTER TABLE
- nexuspermissions ADD CONSTRAINT nexuspermissions_resourcetype_resourceid_subjecttype_subjectnam UNIQUE ("resourceType", "resourceId", "subjectType", "subjectName", "permissionName");
+ NexusPermissions ADD CONSTRAINT nexuspermissions_resourcetype_resourceid_subjecttype_subjectnam UNIQUE ("resourceType", "resourceId", "subjectType", "subjectName", "permissionName");
COMMIT
