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
|
--
-- This file is part of TALER
-- Copyright (C) 2022-2023 Taler Systems SA
--
-- TALER is free software; you can redistribute it and/or modify it under the
-- terms of the GNU General Public License as published by the Free Software
-- Foundation; either version 3, or (at your option) any later version.
--
-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along with
-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
--
-- @file merchant-0004.sql
-- @brief database helper functions for postgres used by the merchant and function for plugin_merchantdb_postgres.c
-- @author Priscilla Huang
BEGIN;
-- Check patch versioning is in place.
SELECT _v.register_patch('merchant-0004', NULL, NULL);
SET search_path TO merchant;
CREATE TABLE IF NOT EXISTS merchant_template
(template_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
,merchant_serial BIGINT NOT NULL
REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
,template_id VARCHAR NOT NULL
,template_description VARCHAR NOT NULL
,pos_key VARCHAR DEFAULT NULL,
,pos_algorithm INT NOT NULL DEFAULT (0);
,template_contract VARCHAR NOT NULL -- in JSON format
,UNIQUE (merchant_serial, template_id)
);
COMMENT ON TABLE merchant_template
IS 'template used by the merchant (may be incomplete, frontend can override)';
COMMENT ON COLUMN merchant_template.template_description
IS 'Human-readable template description';
COMMENT ON COLUMN merchant_template.pos_key
IS 'A base64-encoded key of the point-of-sale. It will be use by the TOTP';
COMMENT ON COLUMN merchant_template.pos_algorithm
IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
COMMENT ON COLUMN merchant_template.template_contract
IS 'The template contract will contains some additional information.';
CREATE TABLE IF NOT EXISTS merchant_webhook
(webhook_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
,merchant_serial BIGINT NOT NULL
REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
,webhook_id VARCHAR NOT NULL
,event_type VARCHAR NOT NULL
,url VARCHAR NOT NULL
,http_method VARCHAR NOT NULL
,header_template VARCHAR
,body_template VARCHAR
,UNIQUE (merchant_serial, webhook_id)
);
COMMENT ON TABLE merchant_webhook
IS 'webhook used by the merchant (may be incomplete, frontend can override)';
COMMENT ON COLUMN merchant_webhook.event_type
IS 'Event of the webhook';
COMMENT ON COLUMN merchant_webhook.url
IS 'URL to make the request to';
COMMENT ON COLUMN merchant_webhook.http_method
IS 'http method use by the merchant';
COMMENT ON COLUMN merchant_webhook.header_template
IS 'Template for the header of the webhook, to be modified based on trigger data';
COMMENT ON COLUMN merchant_webhook.body_template
IS 'Template for the body of the webhook, to be modified based on trigger data';
CREATE TABLE IF NOT EXISTS merchant_pending_webhooks
(webhook_pending_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
,merchant_serial BIGINT NOT NULL
REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
,webhook_serial BIGINT NOT NULL
REFERENCES merchant_webhook (webhook_serial) ON DELETE CASCADE
,next_attempt INT8 NOT NULL DEFAULT(0)
,retries INT4 NOT NULL DEFAULT(0)
,url VARCHAR NOT NULL
,http_method VARCHAR NOT NULL
,header VARCHAR
,body VARCHAR
,UNIQUE (merchant_serial, webhook_pending_serial)
);
COMMENT ON TABLE merchant_pending_webhooks
IS 'webhooks that still need to be executed by the merchant';
COMMENT ON COLUMN merchant_pending_webhooks.url
IS 'URL to make the request to';
COMMENT ON COLUMN merchant_pending_webhooks.webhook_serial
IS 'Reference to the configured webhook template';
COMMENT ON COLUMN merchant_pending_webhooks.retries
IS 'How often have we tried this request so far';
COMMENT ON COLUMN merchant_pending_webhooks.next_attempt
IS 'Time when we should make the next request to the webhook';
COMMENT ON COLUMN merchant_pending_webhooks.http_method
IS 'http method use for the webhook';
COMMENT ON COLUMN merchant_pending_webhooks.header
IS 'Header of the webhook';
COMMENT ON COLUMN merchant_pending_webhooks.body
IS 'Body of the webhook';
ALTER TABLE merchant_kyc
ADD COLUMN aml_decision INT4 NOT NULL DEFAULT (0);
COMMENT ON COLUMN merchant_kyc.aml_decision
IS 'current AML decision for our account at the exchange';
ALTER TABLE merchant_orders
ADD COLUMN pos_key VARCHAR DEFAULT NULL,
ADD COLUMN pos_algorithm INT NOT NULL DEFAULT (0);
COMMENT ON COLUMN merchant_orders.pos_key
IS 'encoded based key which is used for the verification of payment';
COMMENT ON COLUMN merchant_orders.pos_algorithm
IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
ALTER TABLE merchant_contract_terms
ADD COLUMN pos_key VARCHAR DEFAULT NULL,
ADD COLUMN pos_algorithm INT NOT NULL DEFAULT (0);
COMMENT ON COLUMN merchant_contract_terms.pos_key
IS 'enconded based key which is used for the verification of payment';
COMMENT ON COLUMN merchant_orders.pos_algorithm
IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
COMMIT;
|
