From 4ef3ec2d70bb6e40f9cee0b0a6fd17208f05494c Mon Sep 17 00:00:00 2001 From: Jordan Doyle Date: Sun, 22 May 2022 13:54:04 +0100 Subject: [PATCH] Add ability to lookup transactions by address --- migrations/up/V1__initial_schema.sql | 1 + frontend/src/lib/Transaction.svelte | 109 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ web-api/src/database/transactions.rs | 46 ++++++++++++++++++++++++++++++++++++++++++++++ web-api/src/methods/address.rs | 34 ++++++++++++++++++++++++++++++++++ web-api/src/methods/block.rs | 16 ++++++++-------- web-api/src/methods/mod.rs | 2 ++ frontend/src/routes/address/[address].svelte | 49 +++++++++++++++++++++++++++++++++++++++++++++++++ frontend/src/routes/block/[id].svelte | 98 ++------------------------------------------------------------------------------ 8 files changed, 250 insertions(+), 105 deletions(-) diff --git a/migrations/up/V1__initial_schema.sql b/migrations/up/V1__initial_schema.sql index 18dfb50..e7b97cb 100644 --- a/migrations/up/V1__initial_schema.sql +++ a/migrations/up/V1__initial_schema.sql @@ -48,6 +48,7 @@ REFERENCES transactions(id) ); +CREATE INDEX transaction_outputs_address ON transaction_outputs (address); CREATE INDEX transaction_outputs_txid ON transaction_outputs (transaction_id); CREATE UNIQUE INDEX transaction_outputs_txid_index ON transaction_outputs (transaction_id, index); diff --git a/frontend/src/lib/Transaction.svelte b/frontend/src/lib/Transaction.svelte new file mode 100644 index 0000000..877a131 100644 --- /dev/null +++ a/frontend/src/lib/Transaction.svelte @@ -1,0 +1,109 @@ + + +
+

+ § + {transaction.hash} +

+ +
+
+ {#if transaction.coinbase} +
+ Coinbase +
+ {:else} + {#each transaction.inputs as input} +
+
+
+ {#if input.previous_output?.address} + + {input.previous_output?.address} + + {:else} + {briefHexToAsm(input.script).join('\n') || 'WITNESS (TODO)'} + {/if} +
+ + {#if input.previous_output} +
+ {(input.previous_output.value / scale).toFixed(8)} BTC +
+ {/if} +
+
+ {/each} + {/if} +
+ + + +
+ ↓ +
+ +
+ {#each transaction.outputs as output} +
+
+
+ {#if output.address} + + {output.address} + + {:else} + {briefHexToAsm(output.script).join(' ').trim() || output.script} + {/if} +
+ +
+ {(output.value / scale).toFixed(8)} BTC +
+
+
+ {/each} +
+
+
+ + diff --git a/web-api/src/database/transactions.rs b/web-api/src/database/transactions.rs index 40848ee..499ac0a 100644 --- a/web-api/src/database/transactions.rs +++ a/web-api/src/database/transactions.rs @@ -91,7 +91,6 @@ ) AS outputs FROM transactions WHERE transactions.block_id = $1 - GROUP BY transactions.id ORDER BY transactions.id ASC LIMIT $2 OFFSET $3 "; @@ -110,4 +109,49 @@ .map(Transaction::from_row) .collect::>()?, )) +} + +pub async fn fetch_transactions_for_address( + db: &Connection, + address: &str, +) -> Result> { + let select_query = " + SELECT + transactions.*, + ( + SELECT JSON_AGG(transaction_inputs) + FROM ( + SELECT ROW_TO_JSON(transaction_outputs) AS previous_output_tx, transaction_inputs.* + FROM transaction_inputs + LEFT JOIN transaction_outputs + ON transaction_outputs.id = transaction_inputs.previous_output + WHERE transactions.id = transaction_inputs.transaction_id + ) transaction_inputs + ) AS inputs, + ( + SELECT JSON_AGG(transaction_outputs.*) + FROM transaction_outputs + WHERE transactions.id = transaction_outputs.transaction_id + ) AS outputs + FROM transactions + WHERE transactions.id IN ( + SELECT transaction_outputs.transaction_id + FROM transaction_outputs + WHERE transaction_outputs.address = $1 + UNION + SELECT transaction_inputs.transaction_id + FROM transaction_inputs + LEFT JOIN transaction_outputs + ON transaction_outputs.id = transaction_inputs.previous_output + WHERE transaction_outputs.address = $1 + ) + ORDER BY transactions.id DESC + "; + + let transactions = db.query(select_query, &[&address]).await?; + + transactions + .into_iter() + .map(Transaction::from_row) + .collect() } diff --git a/web-api/src/methods/address.rs b/web-api/src/methods/address.rs new file mode 100644 index 0000000..3a94331 100644 --- /dev/null +++ a/web-api/src/methods/address.rs @@ -1,0 +1,34 @@ +use crate::{ + database::transactions::fetch_transactions_for_address, methods::block::Transaction, Database, +}; +use axum::{extract::Path, Extension, Json}; + +pub async fn handle( + Extension(database): Extension, + Path(address): Path, +) -> Json> { + let database = database.get().await.unwrap(); + let transactions = fetch_transactions_for_address(&database, &address) + .await + .unwrap(); + + Json( + transactions + .into_iter() + .map(|mut tx| { + tx.hash.reverse(); + + Transaction { + hash: hex::encode(tx.hash), + version: tx.version, + lock_time: tx.lock_time, + weight: tx.weight, + coinbase: tx.coinbase, + replace_by_fee: tx.replace_by_fee, + inputs: tx.inputs.0.into_iter().map(Into::into).collect(), + outputs: tx.outputs.0.into_iter().map(Into::into).collect(), + } + }) + .collect(), + ) +} diff --git a/web-api/src/methods/block.rs b/web-api/src/methods/block.rs index 098a9c6..d6fa61d 100644 --- a/web-api/src/methods/block.rs +++ a/web-api/src/methods/block.rs @@ -71,14 +71,14 @@ #[derive(Serialize)] pub struct Transaction { - hash: String, - version: i32, - lock_time: i32, - weight: i64, - coinbase: bool, - replace_by_fee: bool, - inputs: Vec, - outputs: Vec, + pub hash: String, + pub version: i32, + pub lock_time: i32, + pub weight: i64, + pub coinbase: bool, + pub replace_by_fee: bool, + pub inputs: Vec, + pub outputs: Vec, } #[derive(Serialize)] diff --git a/web-api/src/methods/mod.rs b/web-api/src/methods/mod.rs index 1e3ef08..c362378 100644 --- a/web-api/src/methods/mod.rs +++ a/web-api/src/methods/mod.rs @@ -1,6 +1,7 @@ use axum::routing::get; use axum::Router; +mod address; mod block; mod height; @@ -9,4 +10,5 @@ .route("/height", get(height::handle)) .route("/block", get(block::list)) .route("/block/:height", get(block::handle)) + .route("/address/:address", get(address::handle)) } diff --git a/frontend/src/routes/address/[address].svelte b/frontend/src/routes/address/[address].svelte new file mode 100644 index 0000000..ed0902a 100644 --- /dev/null +++ a/frontend/src/routes/address/[address].svelte @@ -1,0 +1,49 @@ + + + + +
+
+

{address}

+
+ +
+

{transactions.length} Transaction{transactions.length > 1 ? 's' : ''}

+
+ + {#each transactions as transaction} + + {/each} +
+ + diff --git a/frontend/src/routes/block/[id].svelte b/frontend/src/routes/block/[id].svelte index 3d0474b..43e94f1 100644 --- a/frontend/src/routes/block/[id].svelte +++ a/frontend/src/routes/block/[id].svelte @@ -30,11 +30,10 @@
@@ -63,74 +62,7 @@ {#each block.transactions as transaction} -
-

- § - {transaction.hash} -

- -
-
- {#if transaction.coinbase} -
- Coinbase -
- {:else} - {#each transaction.inputs as input} -
-
-
- {#if input.previous_output?.address} - - {input.previous_output?.address} - - {:else} - {briefHexToAsm(input.script).join('\n') || 'WITNESS (TODO)'} - {/if} -
- - {#if input.previous_output} -
- {(input.previous_output.value / scale).toFixed(8)} BTC -
- {/if} -
-
- {/each} - {/if} -
- - - -
- ↓ -
- -
- {#each transaction.outputs as output} -
-
-
- {#if output.address} - - {output.address} - - {:else} - {briefHexToAsm(output.script).join(' ').trim() || output.script} - {/if} -
- -
- {(output.value / scale).toFixed(8)} BTC -
-
-
- {/each} -
-
-
+ {/each}