1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
package tech.libeufin.nexus
import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.TransactionManager
import org.jetbrains.exposed.sql.transactions.transaction
import org.joda.time.DateTime
import tech.libeufin.nexus.EbicsSubscribersTable.entityId
import tech.libeufin.nexus.EbicsSubscribersTable.primaryKey
import tech.libeufin.util.IntIdTableWithAmount
import java.sql.Connection
const val ID_MAX_LENGTH = 50
object TalerIncomingPayments: LongIdTable() {
val payment = reference("payment", EbicsRawBankTransactionsTable)
val valid = bool("valid")
// avoid refunding twice!
val processed = bool("refunded").default(false)
}
class TalerIncomingPaymentEntry(id: EntityID<Long>) : LongEntity(id) {
companion object : LongEntityClass<TalerIncomingPaymentEntry>(TalerIncomingPayments)
var payment by EbicsRawBankTransactionEntry referencedOn TalerIncomingPayments.payment
var valid by TalerIncomingPayments.valid
}
object EbicsRawBankTransactionsTable : LongIdTable() {
val nexusSubscriber = reference("subscriber", EbicsSubscribersTable)
// How did we learn about this transaction? C52 / C53 / C54
val sourceType = text("sourceType")
// Name of the ZIP entry
val sourceFileName = text("sourceFileName")
// "Subject" of the SEPA transaction
val unstructuredRemittanceInformation = text("unstructuredRemittanceInformation")
// Debit or credit
val transactionType = text("transactionType")
val currency = text("currency")
val amount = text("amount")
val creditorIban = text("creditorIban")
val debitorIban = text("debitorIban")
val bookingDate = text("bookingDate")
}
class EbicsRawBankTransactionEntry(id: EntityID<Long>) : LongEntity(id) {
companion object : LongEntityClass<EbicsRawBankTransactionEntry>(EbicsRawBankTransactionsTable)
var sourceType by EbicsRawBankTransactionsTable.sourceType // C52 or C53 or C54?
var sourceFileName by EbicsRawBankTransactionsTable.sourceFileName
var unstructuredRemittanceInformation by EbicsRawBankTransactionsTable.unstructuredRemittanceInformation
var transactionType by EbicsRawBankTransactionsTable.transactionType
var currency by EbicsRawBankTransactionsTable.currency
var amount by EbicsRawBankTransactionsTable.amount
var creditorIban by EbicsRawBankTransactionsTable.creditorIban
var debitorIban by EbicsRawBankTransactionsTable.debitorIban
var bookingDate by EbicsRawBankTransactionsTable.bookingDate
var nexusSubscriber by EbicsSubscriberEntity referencedOn EbicsRawBankTransactionsTable.nexusSubscriber
}
object Pain001Table : IntIdTableWithAmount() {
val msgId = long("msgId").uniqueIndex().autoIncrement()
val paymentId = long("paymentId")
val fileDate = long("fileDate")
val sum = amount("sum")
val debtorAccount = text("debtorAccount")
val endToEndId = long("EndToEndId")
val subject = text("subject")
val creditorIban = text("creditorIban")
val creditorBic = text("creditorBic")
val creditorName = text("creditorName")
/* Indicates whether the PAIN message was sent to the bank. */
val submitted = bool("submitted").default(false)
/* Indicates whether the bank didn't perform the payment: note that
* this state can be reached when the payment gets listed in a CRZ
* response OR when the payment doesn's show up in a C52/C53 response
*/
val invalid = bool("invalid").default(false)
}
class Pain001Entity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<Pain001Entity>(Pain001Table)
var msgId by Pain001Table.msgId
var paymentId by Pain001Table.paymentId
var date by Pain001Table.fileDate
var sum by Pain001Table.sum
var debtorAccount by Pain001Table.debtorAccount
var endToEndId by Pain001Table.endToEndId
var subject by Pain001Table.subject
var creditorIban by Pain001Table.creditorIban
var creditorBic by Pain001Table.creditorBic
var creditorName by Pain001Table.creditorName
var submitted by Pain001Table.submitted
var invalid by Pain001Table.invalid
}
object EbicsAccountsInfoTable : IdTable<String>() {
override val id = varchar("id", ID_MAX_LENGTH).entityId().primaryKey()
val subscriber = reference("subscriber", EbicsSubscribersTable)
val accountHolder = text("accountHolder").nullable()
val iban = text("iban")
val bankCode = text("bankCode")
}
class EbicsAccountInfoEntity(id: EntityID<String>) : Entity<String>(id) {
companion object : EntityClass<String, EbicsAccountInfoEntity>(EbicsAccountsInfoTable)
var subscriber by EbicsSubscriberEntity referencedOn EbicsAccountsInfoTable.subscriber
var accountHolder by EbicsAccountsInfoTable.accountHolder
var iban by EbicsAccountsInfoTable.iban
var bankCode by EbicsAccountsInfoTable.bankCode
}
object EbicsSubscribersTable : IdTable<String>() {
override val id = varchar("id", ID_MAX_LENGTH).entityId().primaryKey()
val ebicsURL = text("ebicsURL")
val hostID = text("hostID")
val partnerID = text("partnerID")
val userID = text("userID")
val systemID = text("systemID").nullable()
val signaturePrivateKey = blob("signaturePrivateKey")
val encryptionPrivateKey = blob("encryptionPrivateKey")
val authenticationPrivateKey = blob("authenticationPrivateKey")
val bankEncryptionPublicKey = blob("bankEncryptionPublicKey").nullable()
val bankAuthenticationPublicKey = blob("bankAuthenticationPublicKey").nullable()
}
class EbicsSubscriberEntity(id: EntityID<String>) : Entity<String>(id) {
companion object : EntityClass<String, EbicsSubscriberEntity>(EbicsSubscribersTable)
var ebicsURL by EbicsSubscribersTable.ebicsURL
var hostID by EbicsSubscribersTable.hostID
var partnerID by EbicsSubscribersTable.partnerID
var userID by EbicsSubscribersTable.userID
var systemID by EbicsSubscribersTable.systemID
var signaturePrivateKey by EbicsSubscribersTable.signaturePrivateKey
var encryptionPrivateKey by EbicsSubscribersTable.encryptionPrivateKey
var authenticationPrivateKey by EbicsSubscribersTable.authenticationPrivateKey
var bankEncryptionPublicKey by EbicsSubscribersTable.bankEncryptionPublicKey
var bankAuthenticationPublicKey by EbicsSubscribersTable.bankAuthenticationPublicKey
}
fun dbCreateTables() {
Database.connect("jdbc:sqlite:libeufin-nexus.sqlite3", "org.sqlite.JDBC")
TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE
transaction {
addLogger(StdOutSqlLogger)
SchemaUtils.create(
Pain001Table,
EbicsSubscribersTable,
EbicsAccountsInfoTable,
EbicsRawBankTransactionsTable,
TalerIncomingPayments
)
}
}
|
