Skip to content

Commit f11a2ba

Browse files
tac0turtletac0turtle
committed
modify sql
1 parent 1c00648 commit f11a2ba

4 files changed

Lines changed: 139 additions & 24 deletions

File tree

‎backend/crates/atlas-server/src/api/handlers/transactions.rs‎

Lines changed: 89 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@ use axum::{
22
extract::{Path, Query, State},
33
Json,
44
};
5+
use serde::Deserialize;
56
use std::sync::Arc;
67

78
use super::get_table_count;
@@ -11,28 +12,101 @@ use atlas_common::{
1112
AtlasError, Erc20Transfer, NftTransfer, PaginatedResponse, Pagination, Transaction,
1213
};
1314

15+
/// Query parameters for the transaction list endpoint.
16+
/// Supports cursor-based pagination via (block_number, block_index) for O(log N)
17+
/// seeks on large partitioned tables, replacing O(N) OFFSET scans.
18+
#[derive(Debug, Deserialize)]
19+
pub struct TransactionListParams {
20+
#[serde(default = "default_page")]
21+
pub page: u32,
22+
#[serde(default = "default_limit")]
23+
pub limit: u32,
24+
/// Fetch transactions older than this (block_number, block_index).
25+
pub before_block: Option<i64>,
26+
pub before_index: Option<i32>,
27+
/// Fetch transactions newer than this (block_number, block_index).
28+
pub after_block: Option<i64>,
29+
pub after_index: Option<i32>,
30+
/// When true, fetch the oldest page of transactions.
31+
#[serde(default)]
32+
pub last_page: bool,
33+
}
34+
35+
fn default_page() -> u32 {
36+
1
37+
}
38+
fn default_limit() -> u32 {
39+
20
40+
}
41+
1442
pub async fn list_transactions(
1543
State(state): State<Arc<AppState>>,
16-
Query(pagination): Query<Pagination>,
44+
Query(params): Query<TransactionListParams>,
1745
) -> ApiResult<Json<PaginatedResponse<Transaction>>> {
18-
// Use optimized count (approximate for large tables, exact for small)
1946
let total = get_table_count(&state.pool, "transactions").await?;
20-
21-
let transactions: Vec<Transaction> = sqlx::query_as(
22-
"SELECT hash, block_number, block_index, from_address, to_address, value, gas_price, gas_used, input_data, status, contract_created, timestamp
23-
FROM transactions
24-
ORDER BY block_number DESC, block_index DESC
25-
LIMIT $1 OFFSET $2"
26-
)
27-
.bind(pagination.limit())
28-
.bind(pagination.offset())
29-
.fetch_all(&state.pool)
30-
.await?;
47+
let limit = params.limit.min(100) as i64;
48+
49+
let transactions: Vec<Transaction> =
50+
if let (Some(bb), Some(bi)) = (params.before_block, params.before_index) {
51+
// Next page: transactions older than cursor
52+
sqlx::query_as(
53+
"SELECT hash, block_number, block_index, from_address, to_address, value, gas_price, gas_used, input_data, status, contract_created, timestamp
54+
FROM transactions
55+
WHERE (block_number, block_index) < ($1, $2)
56+
ORDER BY block_number DESC, block_index DESC
57+
LIMIT $3",
58+
)
59+
.bind(bb)
60+
.bind(bi)
61+
.bind(limit)
62+
.fetch_all(&state.pool)
63+
.await?
64+
} else if let (Some(ab), Some(ai)) = (params.after_block, params.after_index) {
65+
// Prev page: transactions newer than cursor (fetch ASC, reverse)
66+
let mut txs: Vec<Transaction> = sqlx::query_as(
67+
"SELECT hash, block_number, block_index, from_address, to_address, value, gas_price, gas_used, input_data, status, contract_created, timestamp
68+
FROM transactions
69+
WHERE (block_number, block_index) > ($1, $2)
70+
ORDER BY block_number ASC, block_index ASC
71+
LIMIT $3",
72+
)
73+
.bind(ab)
74+
.bind(ai)
75+
.bind(limit)
76+
.fetch_all(&state.pool)
77+
.await?;
78+
txs.reverse();
79+
txs
80+
} else if params.last_page {
81+
// Last page: oldest transactions (fetch ASC, reverse)
82+
let mut txs: Vec<Transaction> = sqlx::query_as(
83+
"SELECT hash, block_number, block_index, from_address, to_address, value, gas_price, gas_used, input_data, status, contract_created, timestamp
84+
FROM transactions
85+
ORDER BY block_number ASC, block_index ASC
86+
LIMIT $1",
87+
)
88+
.bind(limit)
89+
.fetch_all(&state.pool)
90+
.await?;
91+
txs.reverse();
92+
txs
93+
} else {
94+
// First page (default): newest transactions
95+
sqlx::query_as(
96+
"SELECT hash, block_number, block_index, from_address, to_address, value, gas_price, gas_used, input_data, status, contract_created, timestamp
97+
FROM transactions
98+
ORDER BY block_number DESC, block_index DESC
99+
LIMIT $1",
100+
)
101+
.bind(limit)
102+
.fetch_all(&state.pool)
103+
.await?
104+
};
31105

32106
Ok(Json(PaginatedResponse::new(
33107
transactions,
34-
pagination.page,
35-
pagination.limit,
108+
params.page,
109+
params.limit,
36110
total,
37111
)))
38112
}

‎backend/migrations/20240109000001_cursor_pagination_indexes.sql‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
-- Composite index for cursor-based (keyset) pagination on transactions.
2+
-- Enables O(log N) index seeks instead of O(N) OFFSET scans for the
3+
-- global transaction listing endpoint.
4+
CREATE INDEX IF NOT EXISTS idx_transactions_block_idx
5+
ON transactions(block_number DESC, block_index DESC);

‎frontend/src/api/transactions.ts‎

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,12 +6,17 @@ export interface GetTransactionsParams {
66
limit?: number;
77
block_number?: number;
88
address?: string;
9+
before_block?: number;
10+
before_index?: number;
11+
after_block?: number;
12+
after_index?: number;
13+
last_page?: boolean;
914
}
1015

1116
export async function getTransactions(params: GetTransactionsParams = {}): Promise<PaginatedResponse<Transaction>> {
12-
const { page = 1, limit = 20, block_number, address } = params;
17+
const { page = 1, limit = 20, block_number, address, before_block, before_index, after_block, after_index, last_page } = params;
1318
return client.get<PaginatedResponse<Transaction>>('/transactions', {
14-
params: { page, limit, block_number, address },
19+
params: { page, limit, block_number, address, before_block, before_index, after_block, after_index, last_page: last_page || undefined },
1520
});
1621
}
1722

‎frontend/src/pages/TransactionsPage.tsx‎

Lines changed: 38 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -7,6 +7,13 @@ import { useEthPrice } from '../hooks';
77

88
export default function TransactionsPage() {
99
const [page, setPage] = useState(1);
10+
const [cursor, setCursor] = useState<{
11+
beforeBlock?: number;
12+
beforeIndex?: number;
13+
afterBlock?: number;
14+
afterIndex?: number;
15+
lastPage?: boolean;
16+
}>({});
1017
const [autoRefresh, setAutoRefresh] = useState<boolean>(() => {
1118
try {
1219
const v = localStorage.getItem('txs:autoRefresh');
@@ -16,7 +23,15 @@ export default function TransactionsPage() {
1623
}
1724
});
1825
const [, setTick] = useState(0);
19-
const { transactions, pagination, refetch, loading } = useTransactions({ page, limit: 20 });
26+
const { transactions, pagination, refetch, loading } = useTransactions({
27+
page,
28+
limit: 20,
29+
before_block: cursor.beforeBlock,
30+
before_index: cursor.beforeIndex,
31+
after_block: cursor.afterBlock,
32+
after_index: cursor.afterIndex,
33+
last_page: cursor.lastPage,
34+
});
2035
const [hasLoaded, setHasLoaded] = useState(false);
2136
useEffect(() => {
2237
if (!loading) setHasLoaded(true);
@@ -130,6 +145,22 @@ export default function TransactionsPage() {
130145
}
131146
};
132147

148+
// Cursor-based navigation: derive cursors from server-ordered data
149+
const goFirst = () => { setCursor({}); setPage(1); };
150+
const goPrev = () => {
151+
const f = transactions[0];
152+
if (f) setCursor({ afterBlock: f.block_number, afterIndex: f.block_index });
153+
setPage((p) => Math.max(1, p - 1));
154+
};
155+
const goNext = () => {
156+
const l = transactions[transactions.length - 1];
157+
if (l) setCursor({ beforeBlock: l.block_number, beforeIndex: l.block_index });
158+
setPage((p) => p + 1);
159+
};
160+
const goLast = () => {
161+
if (pagination) { setCursor({ lastPage: true }); setPage(pagination.total_pages); }
162+
};
163+
133164
return (
134165
<div>
135166
<div className="flex items-center justify-between mb-6">
@@ -321,7 +352,7 @@ export default function TransactionsPage() {
321352
<div className="flex items-center justify-center gap-2">
322353
<button
323354
className="btn btn-secondary text-xs"
324-
onClick={() => setPage(1)}
355+
onClick={goFirst}
325356
disabled={page === 1}
326357
aria-label="First page"
327358
title="First page"
@@ -333,7 +364,7 @@ export default function TransactionsPage() {
333364
</button>
334365
<button
335366
className="btn btn-secondary text-xs"
336-
onClick={() => setPage(Math.max(1, page - 1))}
367+
onClick={goPrev}
337368
disabled={page === 1}
338369
aria-label="Previous page"
339370
title="Previous page"
@@ -349,8 +380,8 @@ export default function TransactionsPage() {
349380
</span>
350381
<button
351382
className="btn btn-secondary text-xs"
352-
onClick={() => pagination && setPage(Math.min(pagination.total_pages, page + 1))}
353-
disabled={!pagination || page === pagination?.total_pages}
383+
onClick={goNext}
384+
disabled={!pagination || page >= pagination.total_pages}
354385
aria-label="Next page"
355386
title="Next page"
356387
>
@@ -360,8 +391,8 @@ export default function TransactionsPage() {
360391
</button>
361392
<button
362393
className="btn btn-secondary text-xs"
363-
onClick={() => pagination && setPage(pagination.total_pages)}
364-
disabled={!pagination || page === pagination?.total_pages}
394+
onClick={goLast}
395+
disabled={!pagination || page >= pagination.total_pages}
365396
aria-label="Last page"
366397
title="Last page"
367398
>

0 commit comments

Comments
 (0)