commit ae85603d9a9901195cde36e4a0e45f8986322822
parent de7b722a4c04d621fc4901246930bf22a68dd2e8
Author: Joel-Haeberli <haebu@rubigen.ch>
Date: Tue, 4 Jun 2024 14:16:22 +0200
fix: wire-gateway
Diffstat:
4 files changed, 51 insertions(+), 31 deletions(-)
diff --git a/c2ec/db-postgres.go b/c2ec/db-postgres.go
@@ -40,8 +40,9 @@ const PS_PAYMENT_NOTIFICATION = "UPDATE " + WITHDRAWAL_TABLE_NAME + " SET (" +
const PS_FINALISE_PAYMENT = "UPDATE " + WITHDRAWAL_TABLE_NAME + " SET (" +
WITHDRAWAL_FIELD_NAME_STATUS + "," +
- WITHDRAWAL_FIELD_NAME_COMPLETION_PROOF + ")" +
- " = ($1, $2)" +
+ WITHDRAWAL_FIELD_NAME_COMPLETION_PROOF + "," +
+ WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID + ")" +
+ " = ($1, $2, (SELECT ((SELECT MAX(" + WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID + ") FROM " + WITHDRAWAL_TABLE_NAME + " WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "')+1)))" +
" WHERE " + WITHDRAWAL_FIELD_NAME_ID + "=$3"
const PS_SET_LAST_RETRY = "UPDATE " + WITHDRAWAL_TABLE_NAME +
@@ -89,36 +90,33 @@ const PS_ADD_TRANSFER = "INSERT INTO " + TRANSFER_TABLE_NAME +
const PS_UPDATE_TRANSFER = "UPDATE " + TRANSFER_TABLE_NAME + " SET (" +
TRANSFER_FIELD_NAME_TS + ", " + TRANSFER_FIELD_NAME_STATUS + ", " +
- TRANSFER_FIELD_NAME_RETRIES + ") = ($1,$2,$3) WHERE " +
- TRANSFER_FIELD_NAME_ID + "=$4"
+ TRANSFER_FIELD_NAME_RETRIES + ", " + TRANSFER_FIELD_NAME_TRANSFERRED_ROW_ID + ") = ($1,$2,$3," +
+ "(SELECT ((SELECT MAX(" + TRANSFER_FIELD_NAME_TRANSFERRED_ROW_ID + ") FROM " + TRANSFER_TABLE_NAME + " WHERE " + TRANSFER_FIELD_NAME_STATUS + "=0)+1))" +
+ ") WHERE " + TRANSFER_FIELD_NAME_ID + "=$4"
const PS_CONFIRMED_TRANSACTIONS_ASC = "SELECT * FROM " + WITHDRAWAL_TABLE_NAME +
" WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "'" +
- " ORDER BY " + WITHDRAWAL_FIELD_NAME_ID + " ASC" +
+ " ORDER BY " + WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID + " ASC" +
" LIMIT $1" +
" OFFSET $2"
const PS_CONFIRMED_TRANSACTIONS_DESC = "SELECT * FROM " + WITHDRAWAL_TABLE_NAME +
" WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "'" +
- " ORDER BY " + WITHDRAWAL_FIELD_NAME_ID + " DESC" +
+ " ORDER BY " + WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID + " DESC" +
" LIMIT $1" +
" OFFSET $2"
const PS_CONFIRMED_TRANSACTIONS_ASC_MAX = "SELECT * FROM " + WITHDRAWAL_TABLE_NAME +
" WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "'" +
- " ORDER BY " + WITHDRAWAL_FIELD_NAME_ID + " ASC" +
+ " ORDER BY " + WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID + " ASC" +
" LIMIT $1" +
- " OFFSET (SELECT COUNT(*) FROM " + WITHDRAWAL_TABLE_NAME +
- " WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "'" +
- " AND " + WITHDRAWAL_FIELD_NAME_TS + "<$2)"
+ " OFFSET $2" // (SELECT MIN(" + WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID + ") FROM " + WITHDRAWAL_TABLE_NAME + " WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "')"
const PS_CONFIRMED_TRANSACTIONS_DESC_MAX = "SELECT * FROM " + WITHDRAWAL_TABLE_NAME +
" WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "'" +
" ORDER BY " + WITHDRAWAL_FIELD_NAME_ID + " DESC" +
" LIMIT $1" +
- " OFFSET (SELECT COUNT(*) FROM " + WITHDRAWAL_TABLE_NAME +
- " WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "'" +
- " AND " + WITHDRAWAL_FIELD_NAME_TS + ">$2)"
+ " OFFSET $2" //(SELECT MIN(" + WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID + ") FROM " + WITHDRAWAL_TABLE_NAME +" WHERE " + WITHDRAWAL_FIELD_NAME_STATUS + "='" + string(CONFIRMED) + "' AND )"
const PS_GET_TRANSFERS_ASC = "SELECT * FROM " + TRANSFER_TABLE_NAME +
" ORDER BY " + TRANSFER_FIELD_NAME_ROW_ID + " ASC" +
@@ -133,16 +131,12 @@ const PS_GET_TRANSFERS_DESC = "SELECT * FROM " + TRANSFER_TABLE_NAME +
const PS_GET_TRANSFERS_ASC_MAX = "SELECT * FROM " + TRANSFER_TABLE_NAME +
" ORDER BY " + TRANSFER_FIELD_NAME_ROW_ID + " ASC" +
" LIMIT $1" +
- " OFFSET (SELECT COUNT(*) FROM " + TRANSFER_TABLE_NAME +
- " WHERE " + TRANSFER_FIELD_NAME_STATUS + "=0" +
- " AND " + TRANSFER_FIELD_NAME_TS + ">=$2)"
+ " OFFSET $2" // (SELECT MAX(" + TRANSFER_FIELD_NAME_TRANSFERRED_ROW_ID + ") FROM " + TRANSFER_TABLE_NAME + " WHERE " + TRANSFER_FIELD_NAME_STATUS + "=0)"
const PS_GET_TRANSFERS_DESC_MAX = "SELECT * FROM " + TRANSFER_TABLE_NAME +
" ORDER BY " + TRANSFER_FIELD_NAME_ROW_ID + " DESC" +
" LIMIT $1" +
- " OFFSET (SELECT COUNT(*) FROM " + TRANSFER_TABLE_NAME +
- " WHERE " + TRANSFER_FIELD_NAME_STATUS + "=0" +
- " AND " + TRANSFER_FIELD_NAME_TS + ">=$2)"
+ " OFFSET $2" // (SELECT MAX(" + TRANSFER_FIELD_NAME_TRANSFERRED_ROW_ID + ") FROM " + TRANSFER_TABLE_NAME + " WHERE " + TRANSFER_FIELD_NAME_STATUS + "=0)"
const PS_GET_TRANSFERS_BY_STATUS = "SELECT * FROM " + TRANSFER_TABLE_NAME +
" WHERE " + TRANSFER_FIELD_NAME_STATUS + "=$1"
@@ -525,7 +519,7 @@ func (db *C2ECPostgres) GetConfirmedWithdrawals(start int, delta int, since time
offset = 0
}
- LogInfo("postgres", fmt.Sprintf("selected query=%s (for parameters delta=%d, start=%d, limit=%d, offset=%d, since=%d)", query, delta, start, limit, offset, since.Unix()))
+ LogInfo("postgres", fmt.Sprintf("selected query=%s (\nparameters:\n delta=%d,\n start=%d, limit=%d,\n offset=%d,\n since=%d\n)", query, delta, start, limit, offset, since.Unix()))
var row pgx.Rows
var err error
@@ -537,7 +531,7 @@ func (db *C2ECPostgres) GetConfirmedWithdrawals(start int, delta int, since time
db.ctx,
query,
limit,
- since.Unix(),
+ offset,
)
} else {
row, err = db.pool.Query(
@@ -807,7 +801,7 @@ func (db *C2ECPostgres) GetTransfers(start int, delta int, since time.Time) ([]*
db.ctx,
query,
limit,
- since.Unix(),
+ offset,
)
} else {
row, err = db.pool.Query(
diff --git a/c2ec/db.go b/c2ec/db.go
@@ -21,6 +21,7 @@ const TERMINAL_FIELD_NAME_PROVIDER_ID = "provider_id"
const WITHDRAWAL_TABLE_NAME = "c2ec.withdrawal"
const WITHDRAWAL_FIELD_NAME_ID = "withdrawal_row_id"
+const WITHDRAWAL_FIELD_NAME_CONFIRMED_ROW_ID = "confirmed_row_id"
const WITHDRAWAL_FIELD_NAME_RUID = "request_uid"
const WITHDRAWAL_FIELD_NAME_WOPID = "wopid"
const WITHDRAWAL_FIELD_NAME_RESPUBKEY = "reserve_pub_key"
@@ -38,6 +39,7 @@ const WITHDRAWAL_FIELD_NAME_COMPLETION_PROOF = "completion_proof"
const TRANSFER_TABLE_NAME = "c2ec.transfer"
const TRANSFER_FIELD_NAME_ID = "request_uid"
const TRANSFER_FIELD_NAME_ROW_ID = "row_id"
+const TRANSFER_FIELD_NAME_TRANSFERRED_ROW_ID = "transferred_row_id"
const TRANSFER_FIELD_NAME_AMOUNT = "amount"
const TRANSFER_FIELD_NAME_EXCHANGE_BASE_URL = "exchange_base_url"
const TRANSFER_FIELD_NAME_WTID = "wtid"
@@ -64,6 +66,7 @@ type Terminal struct {
type Withdrawal struct {
WithdrawalRowId uint64 `db:"withdrawal_row_id"`
+ ConfirmedRowId uint64 `db:"confirmed_row_id"`
RequestUid string `db:"request_uid"`
Wopid []byte `db:"wopid"`
ReservePubKey []byte `db:"reserve_pub_key"`
@@ -86,15 +89,16 @@ type TalerAmountCurrency struct {
}
type Transfer struct {
- RowId int `db:"row_id"`
- RequestUid []byte `db:"request_uid"`
- Amount *TalerAmountCurrency `db:"amount"`
- ExchangeBaseUrl string `db:"exchange_base_url"`
- Wtid string `db:"wtid"`
- CreditAccount string `db:"credit_account"`
- TransferTs int64 `db:"transfer_ts"`
- Status int16 `db:"transfer_status"`
- Retries int16 `db:"retries"`
+ RowId int `db:"row_id"`
+ TransferredRowId int `db:"transferred_row_id"`
+ RequestUid []byte `db:"request_uid"`
+ Amount *TalerAmountCurrency `db:"amount"`
+ ExchangeBaseUrl string `db:"exchange_base_url"`
+ Wtid string `db:"wtid"`
+ CreditAccount string `db:"credit_account"`
+ TransferTs int64 `db:"transfer_ts"`
+ Status int16 `db:"transfer_status"`
+ Retries int16 `db:"retries"`
}
type Notification struct {
diff --git a/c2ec/db/0001-c2ec_add_confirmed_id.sql b/c2ec/db/0001-c2ec_add_confirmed_id.sql
@@ -0,0 +1,20 @@
+BEGIN;
+
+SELECT _v.register_patch('0001-c2ec-add-confirmed-id', ARRAY['0001-c2ec-schema'], NULL);
+
+SET search_path TO c2ec;
+
+ALTER TABLE withdrawal
+ ADD confirmed_row_id INT8 DEFAULT 0;
+COMMENT ON COLUMN withdrawal.confirmed_row_id
+ IS 'Used by the wire-gateway API do not mess up confirmed and unconfirmed transactions row ids.';
+
+ALTER TABLE transfer
+ ADD transferred_row_id INT8 DEFAULT 0;
+COMMENT ON COLUMN transfer.transferred_row_id
+ IS 'Used by the wire-gateway API do not mess up pending and succeeded transfers.';
+
+UPDATE withdrawal SET confirmed_row_id=withdrawal_row_id WHERE withdrawal_status='confirmed';
+UPDATE transfer SET transferred_row_id=row_id WHERE transfer_status=0;
+
+COMMIT;
+\ No newline at end of file
diff --git a/c2ec/db/migrate.sh b/c2ec/db/migrate.sh
@@ -38,6 +38,7 @@ SQL_SCRIPTS=(
"./proc-c2ec_payment_notification_listener.sql"
"./proc-c2ec_retry_listener.sql"
"./proc-c2ec_transfer_listener.sql"
+ "./0001-c2ec_add_confirmed_id.sql"
)
execute_sql_scripts() {
