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
155
156
157
158
159
|
BEGIN;
SELECT _v.register_patch('0001-c2ec-schema', NULL, NULL);
CREATE SCHEMA c2ec;
COMMENT ON SCHEMA c2ec
IS 'Schema containing all tables and types related to c2ec (cashless2ecash)';
SET search_path TO c2ec;
CREATE TYPE withdrawal_operation_status AS ENUM (
'pending',
'selected',
'aborted',
'confirmed'
);
COMMENT ON TYPE withdrawal_operation_status
IS 'Enumerates the states of a withdrawal operation.
The states are the same as in the bank-integration API:
pending : the operation is pending parameters selection (exchange and reserve public key)
selected : the operations has been selected and is pending confirmation
aborted : the operation has been aborted
confirmed: the transfer has been confirmed and registered by the bank';
CREATE TYPE taler_amount_currency
AS (val INT8, frac INT4 , curr VARCHAR(12));
COMMENT ON TYPE taler_amount_currency
IS 'Stores an amount, fraction is in units of 1/100000000 of the base value.
copied from https://git.taler.net/merchant.git/tree/src/backenddb/merchant-0001.sql';
CREATE TABLE IF NOT EXISTS provider (
provider_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
payto_target_type TEXT NOT NULL UNIQUE,
backend_base_url TEXT NOT NULL,
backend_credentials TEXT NOT NULL
);
COMMENT ON TABLE provider
IS 'Table describing providers of c2ec terminal';
COMMENT ON COLUMN provider.provider_id
IS 'Uniquely identifies a provider';
COMMENT ON COLUMN provider.name
IS 'Name of the provider, used for selection in transaction proofing';
COMMENT ON COLUMN provider.payto_target_type
IS 'The Payto target type associated with the provider. Each payto target type
has exctly one provider. This is needed so that the attestor client can be dynamically
selected by C2EC.';
COMMENT ON COLUMN provider.backend_base_url
IS 'URL of the provider backend for transaction proofing';
COMMENT ON COLUMN provider.backend_credentials
IS 'Credentials used to access the backend of the provider';
CREATE TABLE IF NOT EXISTS terminal (
terminal_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
access_token TEXT NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
description TEXT,
provider_id INT8 NOT NULL REFERENCES provider(provider_id)
);
COMMENT ON TABLE terminal
IS 'Table containing information about terminals of providers';
COMMENT ON COLUMN terminal.terminal_id
IS 'Uniquely identifies a terminal';
COMMENT ON COLUMN terminal.access_token
IS 'The access token of the terminal used for authentication against the c2ec API. It is hashed using a PBKDF.';
COMMENT ON COLUMN terminal.active
IS 'Indicates if the terminal is active or deactivated';
COMMENT ON COLUMN terminal.description
IS 'Description to help identify the terminal. This may include the location and an identifier of the terminal.';
COMMENT ON COLUMN terminal.provider_id
IS 'Indicates the terminal provider to which the terminal belongs';
CREATE TABLE IF NOT EXISTS withdrawal (
withdrawal_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
wopid BYTEA CHECK (LENGTH(wopid)=32) UNIQUE NOT NULL,
reserve_pub_key BYTEA CHECK (LENGTH(reserve_pub_key)=32) NOT NULL,
registration_ts INT8 NOT NULL,
amount taler_amount_currency,
fees taler_amount_currency,
withdrawal_status withdrawal_operation_status NOT NULL DEFAULT 'pending',
terminal_id INT8 REFERENCES terminal(terminal_id),
provider_transaction_id TEXT,
last_retry_ts INT8,
retry_counter INT4 NOT NULL DEFAULT 0,
completion_proof BYTEA
);
COMMENT ON TABLE withdrawal
IS 'Table representing withdrawal processes initiated by terminals';
COMMENT ON COLUMN withdrawal.withdrawal_id
IS 'The withdrawal id is used a technical id used by the wire gateway to sequentially select new transactions';
COMMENT ON COLUMN withdrawal.wopid
IS 'The wopid (withdrawal operation id) is a nonce generated by the terminal requesting a withdrawal.
The wopid identifies a specific withdrawal spawning all involved systems.';
COMMENT ON COLUMN withdrawal.reserve_pub_key
IS 'Reserve public key for the reserve which will hold the withdrawal amount after completion';
COMMENT ON COLUMN withdrawal.registration_ts
IS 'Timestamp of when the withdrawal request was registered';
COMMENT ON COLUMN withdrawal.amount
IS 'Effective amount to be put into the reserve after completion';
COMMENT ON COLUMN withdrawal.fees
IS 'Fees associated with the withdrawal, including exchange and provider fees';
COMMENT ON COLUMN withdrawal.withdrawal_status
IS 'Status of the withdrawal process';
COMMENT ON COLUMN withdrawal.terminal_id
IS 'ID of the terminal that initiated the withdrawal';
COMMENT ON COLUMN withdrawal.provider_transaction_id
IS 'Transaction identifier supplied by the provider for backend request';
COMMENT ON COLUMN withdrawal.last_retry_ts
IS 'Timestamp of the last retry attempt';
COMMENT ON COLUMN withdrawal.retry_counter
IS 'Number of retry attempts';
COMMENT ON COLUMN withdrawal.completion_proof
IS 'Proof of transaction upon final completion delivered by the providers system';
CREATE INDEX wopid_index ON withdrawal (wopid);
COMMENT ON INDEX wopid_index
IS 'The wopid is the search key for each bank-integration api related request.
Thus it makes sense to create an index on the column.';
CREATE TABLE IF NOT EXISTS transfer (
request_uid BYTEA UNIQUE PRIMARY KEY,
row_id INT8 GENERATED BY DEFAULT AS IDENTITY,
amount taler_amount_currency NOT NULL,
exchange_base_url TEXT NOT NULL,
wtid TEXT NOT NULL,
credit_account TEXT NOT NULL,
transfer_ts INT8 NOT NULL,
transfer_status INT2 NOT NULL DEFAULT 1,
retries INT2 NOT NULL DEFAULT 0
);
COMMENT ON TABLE transfer
IS 'Table storing transfers which are sent by the exchange.';
COMMENT ON COLUMN transfer.row_id
IS 'The row id is used to support the history outgoing';
COMMENT ON COLUMN transfer.request_uid
IS 'A unique identifier for the transfer. ';
COMMENT ON COLUMN transfer.amount
IS 'The amount to be transferred';
COMMENT ON COLUMN transfer.exchange_base_url
IS 'The base url of the exchange, sending the transfer request';
COMMENT ON COLUMN transfer.wtid
IS 'The id of the transaction';
COMMENT ON COLUMN transfer.credit_account
IS 'The payto address of the transfer target';
COMMENT ON COLUMN transfer.transfer_ts
IS 'Timestamp when the transfer was last processesd';
COMMENT ON COLUMN transfer.transfer_status
IS 'Non-zero when the transfer failed at the last retry.
Zero if transfer succeeded. Negative, when max amount of
retries was exceeded. Because the transfer was not yet triggered
when it is added, the status is set to 1 by default.';
COMMENT ON COLUMN transfer.retries
IS 'Number of retries';
COMMIT;
|
