commit 6fe4b3ab24f88f3ff5f685318d85866c1ec34d57
parent e66401b06e243ae00d152ac6715efe81ec398d01
Author: Antoine A <>
Date: Sat, 22 Feb 2025 13:11:30 +0100
common: clean test SQL
Diffstat:
5 files changed, 111 insertions(+), 106 deletions(-)
diff --git a/common/taler-api/tests/common/db.rs b/common/taler-api/tests/common/db.rs
@@ -52,7 +52,7 @@ pub enum TransferResult {
pub async fn transfer(db: &PgPool, transfer: TransferRequest) -> sqlx::Result<TransferResult> {
sqlx::query(
"
- SELECT out_request_uid_reuse, out_wtid_reuse, out_tx_row_id, out_timestamp
+ SELECT out_request_uid_reuse, out_wtid_reuse, out_transfer_row_id, out_created_at
FROM taler_transfer(($1, $2)::taler_amount, $3, $4, $5, $6, $7, $8)
",
)
@@ -70,8 +70,8 @@ pub async fn transfer(db: &PgPool, transfer: TransferRequest) -> sqlx::Result<Tr
TransferResult::WtidReuse
} else {
TransferResult::Success(TransferResponse {
- timestamp: r.try_get_timestamp("out_timestamp")?,
- row_id: r.try_get_safeu64("out_tx_row_id")?,
+ row_id: r.try_get_safeu64("out_transfer_row_id")?,
+ timestamp: r.try_get_timestamp("out_created_at")?,
})
})
})
@@ -98,8 +98,8 @@ pub async fn transfer_page(
(amount).val as amount_val,
(amount).frac as amount_frac,
credit_payto,
- transfer_time
- FROM transfers WHERE
+ created_at
+ FROM transfer WHERE
",
);
if let Some(status) = status {
@@ -113,7 +113,7 @@ pub async fn transfer_page(
status: r.try_get("status")?,
amount: r.try_get_amount("amount", currency)?,
credit_account: r.try_get_payto("credit_payto")?,
- timestamp: r.try_get_timestamp("transfer_time")?,
+ timestamp: r.try_get_timestamp("created_at")?,
})
},
)
@@ -135,8 +135,8 @@ pub async fn transfer_by_id(
exchange_base_url,
wtid,
credit_payto,
- transfer_time
- FROM transfers WHERE transfer_id = $1
+ created_at
+ FROM transfer WHERE transfer_id = $1
",
)
.bind(id as i64)
@@ -148,7 +148,7 @@ pub async fn transfer_by_id(
origin_exchange_url: r.try_get("exchange_base_url")?,
wtid: r.try_get_base32("wtid")?,
credit_account: r.try_get_payto("credit_payto")?,
- timestamp: r.try_get_timestamp("transfer_time")?,
+ timestamp: r.try_get_timestamp("created_at")?,
})
})
.fetch_optional(db)
@@ -176,8 +176,8 @@ pub async fn outgoing_revenue(
exchange_base_url,
wtid,
credit_payto,
- transfer_time
- FROM transfers WHERE status = 'success' AND
+ created_at
+ FROM transfer WHERE status = 'success' AND
",
)
},
@@ -187,7 +187,7 @@ pub async fn outgoing_revenue(
wtid: r.try_get_base32("wtid")?,
credit_account: r.try_get_payto("credit_payto")?,
row_id: r.try_get_safeu64("transfer_id")?,
- date: r.try_get_timestamp("transfer_time")?,
+ date: r.try_get_timestamp("created_at")?,
exchange_base_url: r.try_get_url("exchange_base_url")?,
})
},
@@ -196,7 +196,7 @@ pub async fn outgoing_revenue(
}
pub enum AddIncomingResult {
- Success(SafeU64),
+ Success { id: SafeU64, created_at: Timestamp },
ReservePubReuse,
}
@@ -211,21 +211,24 @@ pub async fn add_incoming(
) -> sqlx::Result<AddIncomingResult> {
sqlx::query(
"
- SELECT out_reserve_pub_reuse, out_tx_row_id
- FROM add_incoming($1, $2, ($3, $4)::taler_amount, $5, $6, $7)
+ SELECT out_reserve_pub_reuse, out_tx_row_id, out_created_at
+ FROM add_incoming(($1, $2)::taler_amount, $3, $4, $5, $6, $7)
",
)
- .bind(key.as_slice())
- .bind(subject)
.bind_amount(amount)
+ .bind(subject)
.bind(debit_account.raw())
- .bind_timestamp(timestamp)
.bind(kind)
+ .bind(key.as_slice())
+ .bind_timestamp(timestamp)
.try_map(|r: PgRow| {
Ok(if r.try_get("out_reserve_pub_reuse")? {
AddIncomingResult::ReservePubReuse
} else {
- AddIncomingResult::Success(r.try_get_safeu64("out_tx_row_id")?)
+ AddIncomingResult::Success {
+ id: r.try_get_safeu64("out_tx_row_id")?,
+ created_at: r.try_get_timestamp("out_created_at")?,
+ }
})
})
.fetch_one(db)
@@ -240,7 +243,7 @@ pub async fn incoming_history(
) -> sqlx::Result<Vec<IncomingBankTransaction>> {
history(
db,
- "incoming_transaction_id",
+ "tx_in_id",
params,
listen,
|| {
@@ -248,14 +251,14 @@ pub async fn incoming_history(
"
SELECT
type,
- incoming_transaction_id,
+ tx_in_id,
(amount).val as amount_val,
(amount).frac as amount_frac,
- creation_time,
+ created_at,
debit_payto,
metadata,
origin_exchange_url
- FROM incoming_transactions WHERE
+ FROM tx_in WHERE
",
)
},
@@ -263,22 +266,22 @@ pub async fn incoming_history(
let kind: IncomingType = r.try_get("type")?;
Ok(match kind {
IncomingType::reserve => IncomingBankTransaction::Reserve {
- row_id: r.try_get_safeu64("incoming_transaction_id")?,
- date: r.try_get_timestamp("creation_time")?,
+ row_id: r.try_get_safeu64("tx_in_id")?,
+ date: r.try_get_timestamp("created_at")?,
amount: r.try_get_amount("amount", currency)?,
debit_account: r.try_get_payto("debit_payto")?,
reserve_pub: r.try_get_base32("metadata")?,
},
IncomingType::kyc => IncomingBankTransaction::Kyc {
- row_id: r.try_get_safeu64("incoming_transaction_id")?,
- date: r.try_get_timestamp("creation_time")?,
+ row_id: r.try_get_safeu64("tx_in_id")?,
+ date: r.try_get_timestamp("created_at")?,
amount: r.try_get_amount("amount", currency)?,
debit_account: r.try_get_payto("debit_payto")?,
account_pub: r.try_get_base32("metadata")?,
},
IncomingType::wad => IncomingBankTransaction::Wad {
- row_id: r.try_get_safeu64("incoming_transaction_id")?,
- date: r.try_get_timestamp("creation_time")?,
+ row_id: r.try_get_safeu64("tx_in_id")?,
+ date: r.try_get_timestamp("created_at")?,
amount: r.try_get_amount("amount", currency)?,
debit_account: r.try_get_payto("debit_payto")?,
origin_exchange_url: r.try_get_url("origin_exchange_url")?,
@@ -298,27 +301,27 @@ pub async fn revenue_history(
) -> sqlx::Result<Vec<RevenueIncomingBankTransaction>> {
history(
db,
- "incoming_transaction_id",
+ "tx_in_id",
params,
listen,
|| {
QueryBuilder::new(
"
SELECT
- incoming_transaction_id,
+ tx_in_id,
(amount).val as amount_val,
(amount).frac as amount_frac,
- creation_time,
+ created_at,
debit_payto,
subject
- FROM incoming_transactions WHERE
+ FROM tx_in WHERE
",
)
},
|r: PgRow| {
Ok(RevenueIncomingBankTransaction {
- row_id: r.try_get_safeu64("incoming_transaction_id")?,
- date: r.try_get_timestamp("creation_time")?,
+ row_id: r.try_get_safeu64("tx_in_id")?,
+ date: r.try_get_timestamp("created_at")?,
amount: r.try_get_amount("amount", currency)?,
credit_fee: None,
debit_account: r.try_get_payto("debit_payto")?,
diff --git a/common/taler-api/tests/common/mod.rs b/common/taler-api/tests/common/mod.rs
@@ -114,19 +114,21 @@ impl WireGateway for TestApi {
&self,
req: AddIncomingRequest,
) -> ApiResult<AddIncomingResponse> {
- let timestamp = Timestamp::now();
let res = db::add_incoming(
&self.pool,
&req.amount,
&req.debit_account,
"",
- ×tamp,
+ &Timestamp::now(),
IncomingType::reserve,
&req.reserve_pub,
)
.await?;
match res {
- db::AddIncomingResult::Success(row_id) => Ok(AddIncomingResponse { timestamp, row_id }),
+ db::AddIncomingResult::Success { id, created_at } => Ok(AddIncomingResponse {
+ timestamp: created_at,
+ row_id: id,
+ }),
db::AddIncomingResult::ReservePubReuse => Err(failure(
ErrorCode::BANK_DUPLICATE_RESERVE_PUB_SUBJECT,
"reserve_pub used already".to_owned(),
@@ -135,19 +137,21 @@ impl WireGateway for TestApi {
}
async fn add_incoming_kyc(&self, req: AddKycauthRequest) -> ApiResult<AddKycauthResponse> {
- let timestamp = Timestamp::now();
let res = db::add_incoming(
&self.pool,
&req.amount,
&req.debit_account,
"",
- ×tamp,
+ &Timestamp::now(),
IncomingType::kyc,
&req.account_pub,
)
.await?;
match res {
- db::AddIncomingResult::Success(row_id) => Ok(AddKycauthResponse { timestamp, row_id }),
+ db::AddIncomingResult::Success { id, created_at } => Ok(AddKycauthResponse {
+ timestamp: created_at,
+ row_id: id,
+ }),
db::AddIncomingResult::ReservePubReuse => Err(failure(
ErrorCode::BANK_DUPLICATE_RESERVE_PUB_SUBJECT,
"reserve_pub used already".to_owned(),
diff --git a/common/taler-common/src/api_wire.rs b/common/taler-common/src/api_wire.rs
@@ -157,11 +157,7 @@ pub struct AddKycauthRequest {
}
/// <https://docs.taler.net/core/api-bank-wire.html#tsref-type-AddKycauthResponse>
-#[derive(Debug, Clone, Serialize, Deserialize)]
-pub struct AddKycauthResponse {
- pub row_id: SafeU64,
- pub timestamp: Timestamp,
-}
+pub type AddKycauthResponse = AddIncomingResponse;
#[derive(Debug, Clone, Copy, Serialize, Deserialize, PartialEq, Eq, sqlx::Type)]
#[allow(non_camel_case_types)]
diff --git a/database-versioning/taler-api-0001.sql b/database-versioning/taler-api-0001.sql
@@ -18,48 +18,49 @@ SELECT _v.register_patch('taler-api-0001', NULL, NULL);
CREATE SCHEMA taler_api;
SET search_path TO taler_api;
-CREATE TYPE taler_amount
- AS (val INT8, frac INT4);
-COMMENT ON TYPE taler_amount
- IS 'Stores an amount, fraction is in units of 1/100000000 of the base value';
-
-CREATE TYPE transfer_status AS ENUM (
- 'pending'
- ,'transient_failure'
- ,'permanent_failure'
- ,'success'
-);
-
-CREATE TABLE transfers (
- transfer_id INT8 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
- ,amount taler_amount NOT NULL
- ,exchange_base_url TEXT NOT NULL
- ,subject TEXT NOT NULL
- ,credit_payto TEXT NOT NULL
- ,request_uid BYTEA UNIQUE NOT NULL CHECK (LENGTH(request_uid)=64)
- ,wtid BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid)=32)
- ,status transfer_status NOT NULL
- ,status_msg TEXT
- ,transfer_time INT8 NOT NULL
-);
+CREATE TYPE taler_amount AS (val INT8, frac INT4);
+COMMENT ON TYPE taler_amount IS 'Stores an amount, fraction is in units of 1/100000000 of the base value';
CREATE TYPE incoming_type AS ENUM
('reserve' ,'kyc', 'wad');
-CREATE TABLE incoming_transactions (
- incoming_transaction_id INT8 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
- ,amount taler_amount NOT NULL
- ,subject TEXT NOT NULL
- ,debit_payto TEXT NOT NULL
- ,creation_time INT8 NOT NULL
- ,type incoming_type NOT NULL
- ,metadata BYTEA NOT NULL
- ,origin_exchange_url TEXT
- ,CONSTRAINT polymorphism CHECK(
+COMMENT ON TYPE incoming_type IS 'Types of incoming talerable transactions';
+CREATE TABLE tx_in (
+ tx_in_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+ amount taler_amount NOT NULL,
+ subject TEXT NOT NULL,
+ debit_payto TEXT NOT NULL,
+ type incoming_type NOT NULL,
+ metadata BYTEA NOT NULL,
+ origin_exchange_url TEXT,
+ created_at INT8 NOT NULL,
+ CONSTRAINT polymorphism CHECK(
CASE type
WHEN 'wad' THEN LENGTH(metadata)=24 AND origin_exchange_url IS NOT NULL
ELSE LENGTH(metadata)=32 AND origin_exchange_url IS NULL
END
)
);
-
-CREATE UNIQUE INDEX incoming_transactions_unique_reserve_pub ON incoming_transactions (metadata) WHERE type = 'reserve';
-\ No newline at end of file
+CREATE UNIQUE INDEX tx_in_unique_reserve_pub ON tx_in (metadata) WHERE type = 'reserve';
+COMMENT ON TABLE tx_in IS 'Incoming transactions';
+
+CREATE TYPE transfer_status AS ENUM (
+ 'pending'
+ ,'transient_failure'
+ ,'permanent_failure'
+ ,'success'
+);
+COMMENT ON TYPE transfer_status IS 'Status of a Wire Gateway transfer';
+
+CREATE TABLE transfer (
+ transfer_id INT8 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+ request_uid BYTEA UNIQUE NOT NULL CHECK (LENGTH(request_uid)=64),
+ amount taler_amount NOT NULL,
+ subject TEXT NOT NULL,
+ credit_payto TEXT NOT NULL,
+ wtid BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid)=32),
+ exchange_base_url TEXT NOT NULL,
+ status transfer_status NOT NULL,
+ status_msg TEXT,
+ created_at INT8 NOT NULL
+);
+COMMENT ON TABLE transfer IS 'Wire Gateway transfers';
diff --git a/database-versioning/taler-api-procedures.sql b/database-versioning/taler-api-procedures.sql
@@ -45,13 +45,13 @@ CREATE FUNCTION taler_transfer(
IN in_credit_payto TEXT,
IN in_request_uid BYTEA,
IN in_wtid BYTEA,
- IN in_timestamp INT8,
+ IN in_now INT8,
-- Error status
OUT out_request_uid_reuse BOOLEAN,
OUT out_wtid_reuse BOOLEAN,
-- Success return
- OUT out_tx_row_id INT8,
- OUT out_timestamp INT8
+ OUT out_transfer_row_id INT8,
+ OUT out_created_at INT8
)
LANGUAGE plpgsql AS $$
BEGIN
@@ -60,9 +60,9 @@ SELECT (amount != in_amount
OR credit_payto != in_credit_payto
OR exchange_base_url != in_exchange_base_url
OR wtid != in_wtid)
- ,transfer_id, transfer_time
- INTO out_request_uid_reuse, out_tx_row_id, out_timestamp
- FROM transfers
+ ,transfer_id, created_at
+ INTO out_request_uid_reuse, out_transfer_row_id, out_created_at
+ FROM transfer
WHERE request_uid = in_request_uid;
IF FOUND THEN
out_wtid_reuse=FALSE;
@@ -70,13 +70,13 @@ IF FOUND THEN
END IF;
out_request_uid_reuse=FALSE;
-- Check for wtid reuse
-out_wtid_reuse = EXISTS(SELECT FROM transfers WHERE wtid=in_wtid);
+out_wtid_reuse = EXISTS(SELECT FROM transfer WHERE wtid=in_wtid);
IF out_wtid_reuse THEN
RETURN;
END IF;
-out_timestamp=in_timestamp;
+out_created_at=in_now;
-- Register exchange
-INSERT INTO transfers (
+INSERT INTO transfer (
amount,
exchange_base_url,
subject,
@@ -85,7 +85,7 @@ INSERT INTO transfers (
wtid,
status,
status_msg,
- transfer_time
+ created_at
) VALUES (
in_amount,
in_exchange_base_url,
@@ -95,38 +95,40 @@ INSERT INTO transfers (
in_wtid,
'success',
NULL,
- in_timestamp
-) RETURNING transfer_id INTO out_tx_row_id;
+ in_now
+) RETURNING transfer_id INTO out_transfer_row_id;
-- Notify new transaction
-PERFORM pg_notify('outgoing_tx', out_tx_row_id || '');
+PERFORM pg_notify('outgoing_tx', out_transfer_row_id || '');
END $$;
COMMENT ON FUNCTION taler_transfer IS 'Create an outgoing taler transaction and register it';
CREATE FUNCTION add_incoming(
- IN in_key BYTEA,
- IN in_subject TEXT,
IN in_amount taler_amount,
+ IN in_subject TEXT,
IN in_debit_payto TEXT,
- IN in_timestamp INT8,
IN in_type incoming_type,
+ IN in_metadata BYTEA,
+ IN in_now INT8,
-- Error status
OUT out_reserve_pub_reuse BOOLEAN,
-- Success return
- OUT out_tx_row_id INT8
+ OUT out_tx_row_id INT8,
+ OUT out_created_at INT8
)
LANGUAGE plpgsql AS $$
BEGIN
-- Check conflict
-SELECT in_type = 'reserve'::incoming_type AND EXISTS(SELECT FROM incoming_transactions WHERE metadata = in_key AND type = 'reserve')
+SELECT in_type = 'reserve'::incoming_type AND EXISTS(SELECT FROM tx_in WHERE metadata = in_metadata AND type = 'reserve')
INTO out_reserve_pub_reuse;
IF out_reserve_pub_reuse THEN
RETURN;
END IF;
-- Register incoming transaction
-INSERT INTO incoming_transactions (
+out_created_at=in_now;
+INSERT INTO tx_in (
amount,
debit_payto,
- creation_time,
+ created_at,
subject,
type,
metadata,
@@ -134,12 +136,12 @@ INSERT INTO incoming_transactions (
) VALUES (
in_amount,
in_debit_payto,
- in_timestamp,
+ in_now,
in_subject,
in_type,
- in_key,
+ in_metadata,
NULL
-) RETURNING incoming_transaction_id INTO out_tx_row_id;
+) RETURNING tx_in_id INTO out_tx_row_id;
-- Notify new incoming transaction
PERFORM pg_notify('incoming_tx', out_tx_row_id || '');
END $$;
