From 4ef3ec2d70bb6e40f9cee0b0a6fd17208f05494c Mon Sep 17 00:00:00 2001
From: Jordan Doyle <jordan@doyle.la>
Date: Sun, 22 May 2022 13:54:04 +0100
Subject: [PATCH] Add ability to lookup transactions by address

---
 frontend/src/lib/Transaction.svelte          | 109 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 frontend/src/routes/address/[address].svelte |  49 +++++++++++++++++++++++++++++++++++++++++++++++++
 frontend/src/routes/block/[id].svelte        |  98 ++------------------------------------------------------------------------------------------------
 migrations/up/V1__initial_schema.sql         |   1 +
 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 ++
 8 files changed, 250 insertions(+), 105 deletions(-)
 create mode 100644 frontend/src/lib/Transaction.svelte
 create mode 100644 frontend/src/routes/address/[address].svelte
 create mode 100644 web-api/src/methods/address.rs

diff --git a/frontend/src/lib/Transaction.svelte b/frontend/src/lib/Transaction.svelte
new file mode 100644
index 0000000..877a131
--- /dev/null
+++ b/frontend/src/lib/Transaction.svelte
@@ -0,0 +1,109 @@
+<script>
+    import {briefHexToAsm} from "./bitcoinScript";
+
+    export let transaction;
+    const scale = Math.pow(10, 8);
+</script>
+
+<section class="p-4">
+    <h3 class="text-base md:text-lg m-2 mb-4 md:mb-2 break-all" id={transaction.hash}>
+        <a href={`#${transaction.hash}`}>§</a>
+        {transaction.hash}
+    </h3>
+
+    <div class="flex flex-col md:table table-fixed w-full">
+        <div class="table-cell break-all">
+            {#if transaction.coinbase}
+                <div class="item w-full">
+                    <code>Coinbase</code>
+                </div>
+            {:else}
+                {#each transaction.inputs as input}
+                    <div class="item w-full">
+                        <div class="item-inner">
+                            <div class="flex-grow">
+                                {#if input.previous_output?.address}
+                                    <a href="/address/{input.previous_output?.address}">
+                                        <code>{input.previous_output?.address}</code>
+                                    </a>
+                                {:else}
+                                    <code>{briefHexToAsm(input.script).join('\n') || 'WITNESS (TODO)'}</code>
+                                {/if}
+                            </div>
+
+                            {#if input.previous_output}
+                                <div class="amount">
+                                    <code>{(input.previous_output.value / scale).toFixed(8)} BTC</code>
+                                </div>
+                            {/if}
+                        </div>
+                    </div>
+                {/each}
+            {/if}
+        </div>
+
+        <div class="hidden md:table-cell text-2xl w-10 align-middle text-center">
+            →
+        </div>
+
+        <div class="block md:hidden text-center text-2xl m-2 w-full align-middle text-center">
+            ↓
+        </div>
+
+        <div class="table-cell break-all">
+            {#each transaction.outputs as output}
+                <div class="item w-full">
+                    <div class="item-inner">
+                        <div class="flex-grow">
+                            {#if output.address}
+                                <a href="/address/{output.address}">
+                                    <code>{output.address}</code>
+                                </a>
+                            {:else}
+                                <code>{briefHexToAsm(output.script).join(' ').trim() || output.script}</code>
+                            {/if}
+                        </div>
+
+                        <div class="amount">
+                            <code>{(output.value / scale).toFixed(8)} BTC</code>
+                        </div>
+                    </div>
+                </div>
+            {/each}
+        </div>
+    </div>
+</section>
+
+<style lang="scss">
+  @import "../src/_section.scss";
+
+  section {
+    @apply text-xs;
+  }
+
+  .table-cell {
+    counter-reset: inout;
+  }
+
+  .amount {
+    @apply whitespace-nowrap ml-0 mt-2 md:mt-0 md:ml-4;
+  }
+
+  div.item {
+    @apply bg-gray-900/40 p-4 rounded-lg flex flex mb-2;
+    counter-increment: inout;
+
+    &:last-of-type {
+      @apply mb-0;
+    }
+
+    &::before {
+      @apply inline-block w-6 mr-2 select-none text-zinc-500;
+      content: counter(inout);
+    }
+
+    .item-inner {
+      @apply flex flex-col md:flex-row w-full;
+    }
+  }
+</style>
diff --git a/frontend/src/routes/address/[address].svelte b/frontend/src/routes/address/[address].svelte
new file mode 100644
index 0000000..ed0902a
--- /dev/null
+++ b/frontend/src/routes/address/[address].svelte
@@ -0,0 +1,49 @@
+<script context="module">
+    export async function load({ fetch, params, url }) {
+        let res = await fetch(`http://localhost:3001/address/${params.address}`);
+
+        if (res.ok) {
+            return {
+                props: {
+                    transactions: await res.json(),
+                    address: params.address,
+                }
+            };
+        }
+        return {
+            status: res.status,
+            error: new Error()
+        };
+    }
+</script>
+
+<script>
+    import { briefHexToAsm } from "$lib/bitcoinScript";
+    import Transaction from "$lib/Transaction.svelte";
+
+    export let transactions = {};
+    export let address = '';
+</script>
+
+<div>
+    <section class="p-7">
+        <h2 class="!p-0 !py-4">{address}</h2>
+    </section>
+
+    <section class="!bg-transparent">
+        <h3 class="text-white text-2xl">{transactions.length} Transaction{transactions.length > 1 ? 's' : ''}</h3>
+    </section>
+
+    {#each transactions as transaction}
+        <Transaction transaction={transaction} />
+    {/each}
+</div>
+
+<style lang="scss">
+  @import "../../_section.scss";
+  @import "../../_table.scss";
+
+  section {
+    @apply text-xs;
+  }
+</style>
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
+++ b/frontend/src/routes/block/[id].svelte
@@ -30,11 +30,10 @@
 
 <script>
   import { briefHexToAsm } from "$lib/bitcoinScript";
+  import Transaction from "$lib/Transaction.svelte";
 
   export let block = {};
   export let currentPage = 0;
-
-  const scale = Math.pow(10, 8);
 </script>
 
 <div>
@@ -63,74 +62,7 @@
   </section>
 
   {#each block.transactions as transaction}
-    <section class="p-4">
-      <h3 class="text-base md:text-lg m-2 mb-4 md:mb-2 break-all" id={transaction.hash}>
-        <a href={`#${transaction.hash}`}>§</a>
-        {transaction.hash}
-      </h3>
-
-      <div class="flex flex-col md:table table-fixed w-full">
-        <div class="table-cell break-all">
-          {#if transaction.coinbase}
-            <div class="item w-full">
-              <code>Coinbase</code>
-            </div>
-          {:else}
-            {#each transaction.inputs as input}
-              <div class="item w-full">
-                <div class="item-inner">
-                  <div class="flex-grow">
-                    {#if input.previous_output?.address}
-                      <a href="/address/{input.previous_output?.address}">
-                        <code>{input.previous_output?.address}</code>
-                      </a>
-                    {:else}
-                      <code>{briefHexToAsm(input.script).join('\n') || 'WITNESS (TODO)'}</code>
-                    {/if}
-                  </div>
-
-                  {#if input.previous_output}
-                    <div class="amount">
-                      <code>{(input.previous_output.value / scale).toFixed(8)} BTC</code>
-                    </div>
-                  {/if}
-                </div>
-              </div>
-            {/each}
-          {/if}
-        </div>
-
-        <div class="hidden md:table-cell text-2xl w-10 align-middle text-center">
-          →
-        </div>
-
-        <div class="block md:hidden text-center text-2xl m-2 w-full align-middle text-center">
-          ↓
-        </div>
-
-        <div class="table-cell break-all">
-          {#each transaction.outputs as output}
-            <div class="item w-full">
-              <div class="item-inner">
-                <div class="flex-grow">
-                  {#if output.address}
-                    <a href="/address/{output.address}">
-                      <code>{output.address}</code>
-                    </a>
-                  {:else}
-                    <code>{briefHexToAsm(output.script).join(' ').trim() || output.script}</code>
-                  {/if}
-                </div>
-
-                <div class="amount">
-                  <code>{(output.value / scale).toFixed(8)} BTC</code>
-                </div>
-              </div>
-            </div>
-          {/each}
-        </div>
-      </div>
-    </section>
+    <Transaction transaction={transaction} />
   {/each}
 
   <div class="pagination">
@@ -161,30 +93,4 @@
   section {
     @apply text-xs;
   }
-
-  .table-cell {
-    counter-reset: inout;
-  }
-
-  .amount {
-    @apply whitespace-nowrap ml-0 mt-2 md:mt-0 md:ml-4;
-  }
-
-  div.item {
-    @apply bg-gray-900/40 p-4 rounded-lg flex flex mb-2;
-    counter-increment: inout;
-
-    &:last-of-type {
-      @apply mb-0;
-    }
-
-    &::before {
-      @apply inline-block w-6 mr-2 select-none text-zinc-500;
-      content: counter(inout);
-    }
-
-    .item-inner {
-      @apply flex flex-col md:flex-row w-full;
-    }
-  }
 </style>
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
+++ b/migrations/up/V1__initial_schema.sql
@@ -48,6 +48,7 @@ CREATE TABLE transaction_outputs (
             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/web-api/src/database/transactions.rs b/web-api/src/database/transactions.rs
index 40848ee..499ac0a 100644
--- a/web-api/src/database/transactions.rs
+++ b/web-api/src/database/transactions.rs
@@ -91,7 +91,6 @@ pub async fn fetch_transactions_for_block(
             ) AS outputs
         FROM transactions
         WHERE transactions.block_id = $1
-        GROUP BY transactions.id
         ORDER BY transactions.id ASC
         LIMIT $2 OFFSET $3
     ";
@@ -111,3 +110,48 @@ pub async fn fetch_transactions_for_block(
             .collect::<Result<_>>()?,
     ))
 }
+
+pub async fn fetch_transactions_for_address(
+    db: &Connection,
+    address: &str,
+) -> Result<Vec<Transaction>> {
+    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
--- /dev/null
+++ b/web-api/src/methods/address.rs
@@ -0,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<Database>,
+    Path(address): Path<String>,
+) -> Json<Vec<Transaction>> {
+    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
+++ b/web-api/src/methods/block.rs
@@ -71,14 +71,14 @@ pub struct Block {
 
 #[derive(Serialize)]
 pub struct Transaction {
-    hash: String,
-    version: i32,
-    lock_time: i32,
-    weight: i64,
-    coinbase: bool,
-    replace_by_fee: bool,
-    inputs: Vec<TransactionInput>,
-    outputs: Vec<TransactionOutput>,
+    pub hash: String,
+    pub version: i32,
+    pub lock_time: i32,
+    pub weight: i64,
+    pub coinbase: bool,
+    pub replace_by_fee: bool,
+    pub inputs: Vec<TransactionInput>,
+    pub outputs: Vec<TransactionOutput>,
 }
 
 #[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
+++ b/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 @@ pub fn router() -> Router {
         .route("/height", get(height::handle))
         .route("/block", get(block::list))
         .route("/block/:height", get(block::handle))
+        .route("/address/:address", get(address::handle))
 }
--
libgit2 1.7.2