use std::collections::HashSet;
use schemerz_rusqlite::RusqliteMigration;
use uuid::Uuid;
use super::v_transactions_sender_grouping;
use crate::wallet::init::WalletMigrationError;
pub const MIGRATION_ID: Uuid = Uuid::from_u128(0xcbf88891_ba83_4329_a798_c986649d77e8);
const DEPENDENCIES: &[Uuid] = &[v_transactions_sender_grouping::MIGRATION_ID];
pub(super) struct Migration;
impl schemerz::Migration<Uuid> for Migration {
fn id(&self) -> Uuid {
MIGRATION_ID
}
fn dependencies(&self) -> HashSet<Uuid> {
DEPENDENCIES.iter().copied().collect()
}
fn description(&self) -> &'static str {
"Restores the legacy ZIP 318 field on v_transactions."
}
}
impl RusqliteMigration for Migration {
type Error = WalletMigrationError;
fn up(&self, conn: &rusqlite::Transaction) -> Result<(), Self::Error> {
conn.execute_batch("DROP VIEW v_transactions;")?;
conn.execute_batch(VIEW_TRANSACTIONS)?;
conn.prepare("SELECT * FROM v_transactions LIMIT 0")?;
Ok(())
}
fn down(&self, _conn: &rusqlite::Transaction) -> Result<(), Self::Error> {
Err(WalletMigrationError::CannotRevert(MIGRATION_ID))
}
}
const VIEW_TRANSACTIONS: &str = "
CREATE VIEW v_transactions AS
WITH
notes AS (
-- Outputs received in this transaction
SELECT ro.account_id AS account_id,
ro.transaction_id AS transaction_id,
ro.pool AS pool,
id_within_pool_table,
ro.value AS value,
ro.value AS received_value,
0 AS spent_value,
0 AS spent_note_count,
CASE
WHEN ro.is_change THEN 1
ELSE 0
END AS change_note_count,
CASE
WHEN ro.is_change THEN 0
ELSE 1
END AS received_count,
CASE
WHEN (ro.memo IS NULL OR ro.memo = X'F6')
THEN 0
ELSE 1
END AS memo_present,
-- The wallet cannot receive transparent outputs in shielding transactions.
CASE
WHEN ro.pool = 0
THEN 1
ELSE 0
END AS does_not_match_shielding
FROM v_received_outputs ro
UNION
-- Outputs spent in this transaction
SELECT ro.account_id AS account_id,
ros.transaction_id AS transaction_id,
ro.pool AS pool,
id_within_pool_table,
-ro.value AS value,
0 AS received_value,
ro.value AS spent_value,
1 AS spent_note_count,
0 AS change_note_count,
0 AS received_count,
0 AS memo_present,
-- The wallet cannot spend shielded outputs in shielding transactions.
CASE
WHEN ro.pool != 0
THEN 1
ELSE 0
END AS does_not_match_shielding
FROM v_received_outputs ro
JOIN v_received_output_spends ros
ON ros.pool = ro.pool
AND ros.received_output_id = ro.id_within_pool_table
),
-- What each account spent and received in each pool, per transaction. A pool the account
-- received value in but spent nothing from is a pool that value crossed into from
-- elsewhere, which is what `pool_crossings` below is built on.
notes_by_pool AS (
SELECT account_id, transaction_id, pool,
SUM(spent_note_count) AS spent_note_count,
SUM(received_count + change_note_count) AS received_note_count,
SUM(received_value) AS received_value
FROM notes
GROUP BY account_id, transaction_id, pool
),
-- Obtain a count of the notes that the wallet created in each transaction,
-- not counting change notes.
sent_note_counts AS (
SELECT sent_notes.from_account_id AS account_id,
sent_notes.transaction_id AS transaction_id,
COUNT(DISTINCT sent_notes.id) AS sent_notes,
SUM(
CASE
WHEN (sent_notes.memo IS NULL OR sent_notes.memo = X'F6' OR ro.transaction_id IS NOT NULL)
THEN 0
ELSE 1
END
) AS memo_count
FROM sent_notes
LEFT JOIN v_received_outputs ro ON sent_notes.id = ro.sent_note_id
WHERE COALESCE(ro.is_change, 0) = 0
-- Group by the sending account. A bare `account_id` here would resolve to the joined
-- `ro.account_id`, splitting one sender's notes into a group per receiving account.
GROUP BY sent_notes.from_account_id, sent_notes.transaction_id
),
-- Identifies the transactions that are wallet-internal transfers moving an account's own
-- funds between shielded pools, and reports the value that crossed. `crossing_value` is
-- non-NULL exactly for such a transaction, so it carries both the classification and the
-- amount; see the `pool_crossing_value` column below.
pool_crossings AS (
SELECT notes_by_pool.account_id AS account_id,
notes_by_pool.transaction_id AS transaction_id,
CASE WHEN (
-- Every note spent and every output received by the wallet is shielded.
SUM(CASE WHEN notes_by_pool.pool = 0 THEN notes_by_pool.spent_note_count + notes_by_pool.received_note_count ELSE 0 END) = 0
-- The transaction spends at least one of the account's notes.
AND SUM(notes_by_pool.spent_note_count) > 0
-- At least one output was received in a pool the account spent nothing
-- from, so value crossed between pools.
AND SUM(CASE WHEN notes_by_pool.spent_note_count = 0 THEN notes_by_pool.received_note_count ELSE 0 END) > 0
-- We do not know about any external outputs of the transaction.
AND MAX(COALESCE(sent_note_counts.sent_notes, 0)) = 0
)
-- The total value received in the pools the account did not spend from. The
-- condition above guarantees at least one such output, so when this branch is
-- taken the sum is never NULL.
THEN SUM(CASE WHEN notes_by_pool.spent_note_count = 0 THEN notes_by_pool.received_value ELSE 0 END)
END AS crossing_value
FROM notes_by_pool
LEFT JOIN sent_note_counts
ON sent_note_counts.account_id = notes_by_pool.account_id
AND sent_note_counts.transaction_id = notes_by_pool.transaction_id
GROUP BY notes_by_pool.account_id, notes_by_pool.transaction_id
),
blocks_max_height AS (
SELECT MAX(blocks.height) AS max_height FROM blocks
)
SELECT accounts.uuid AS account_uuid,
transactions.mined_height AS mined_height,
transactions.txid AS txid,
transactions.tx_index AS tx_index,
COALESCE(transactions.expiry_height, (SELECT history_expiry_height
FROM ironwood_enhance_routing WHERE transaction_id = transactions.id_tx AND route = 0))
AS expiry_height,
transactions.raw AS raw,
SUM(notes.value) AS account_balance_delta,
SUM(notes.spent_value) AS total_spent,
SUM(notes.received_value) AS total_received,
transactions.fee AS fee_paid,
SUM(notes.change_note_count) > 0 AS has_change,
MAX(COALESCE(sent_note_counts.sent_notes, 0)) AS sent_note_count,
SUM(notes.received_count) AS received_note_count,
SUM(notes.memo_present) + MAX(COALESCE(sent_note_counts.memo_count, 0)) AS memo_count,
blocks.time AS block_time,
(
transactions.mined_height IS NULL
AND transactions.expiry_height BETWEEN 1 AND blocks_max_height.max_height
) AS expired_unmined,
SUM(notes.spent_note_count) AS spent_note_count,
(
-- All of the wallet-spent and wallet-received notes are consistent with a
-- shielding transaction.
SUM(notes.does_not_match_shielding) = 0
-- The transaction contains at least one wallet-spent output.
AND SUM(notes.spent_note_count) > 0
-- The transaction contains at least one wallet-received note.
AND (SUM(notes.received_count) + SUM(notes.change_note_count)) > 0
-- We do not know about any external outputs of the transaction.
AND MAX(COALESCE(sent_note_counts.sent_notes, 0)) = 0
) AS is_shielding,
-- The value that crossed pools, when this transaction is a wallet-internal transfer
-- between shielded pools; NULL when it is not such a transfer. A transaction is one
-- exactly when this column is non-NULL.
pool_crossings.crossing_value AS pool_crossing_value,
transactions.trust_status,
transactions.zip318_kind
FROM notes
JOIN accounts ON accounts.id = notes.account_id
JOIN transactions ON transactions.id_tx = notes.transaction_id
LEFT JOIN blocks_max_height
LEFT JOIN blocks ON blocks.height = transactions.mined_height
LEFT JOIN sent_note_counts
ON sent_note_counts.account_id = notes.account_id
AND sent_note_counts.transaction_id = notes.transaction_id
LEFT JOIN pool_crossings
ON pool_crossings.account_id = notes.account_id
AND pool_crossings.transaction_id = notes.transaction_id
GROUP BY notes.account_id, notes.transaction_id
";
#[cfg(test)]
mod tests {
use super::{DEPENDENCIES, MIGRATION_ID, VIEW_TRANSACTIONS};
use crate::{
WalletDb,
testing::db::{test_clock, test_rng},
wallet::init::{WalletMigrator, migrations::tests::test_migrate},
};
use rusqlite::{Connection, types::Value};
use zcash_protocol::consensus::Network;
fn view(conn: &Connection) -> String {
conn.query_row(
"SELECT sql FROM sqlite_master WHERE type = 'view' AND name = 'v_transactions'",
[],
|row| row.get(0),
)
.unwrap()
}
fn install_historical_sender_grouping_view(conn: &Connection) {
let historical_view = VIEW_TRANSACTIONS.replace(
" transactions.trust_status,\n transactions.zip318_kind\n",
" transactions.trust_status\n",
);
assert!(!historical_view.contains("transactions.zip318_kind"));
conn.execute_batch(&format!("DROP VIEW v_transactions;{historical_view}"))
.unwrap();
}
fn transactions(conn: &Connection) -> Vec<Vec<Value>> {
let mut stmt = conn
.prepare("SELECT * FROM transactions ORDER BY id_tx")
.unwrap();
let column_count = stmt.column_count();
stmt.query_map([], |row| (0..column_count).map(|i| row.get(i)).collect())
.unwrap()
.collect::<Result<_, _>>()
.unwrap()
}
#[test]
fn migrate() {
test_migrate(&[MIGRATION_ID]);
}
#[test]
fn repairs_applied_sender_grouping_without_changing_stored_values() {
let file = tempfile::NamedTempFile::new().unwrap();
let mut db =
WalletDb::for_path(file.path(), Network::TestNetwork, test_clock(), test_rng())
.unwrap();
WalletMigrator::new()
.init_or_migrate_to(&mut db, DEPENDENCIES)
.unwrap();
install_historical_sender_grouping_view(&db.conn);
assert!(!view(&db.conn).contains("transactions.zip318_kind"));
db.conn.execute(
"INSERT INTO transactions (txid, min_observed_height, created, raw, fee, expiry_height, zip318_kind)
VALUES (?1, 100, '2026-10-04 12:00:00', X'010203', 10000, 120, 2)",
[[7u8; 32].as_slice()],
).unwrap();
db.conn
.execute_batch(
"CREATE VIEW application_history AS SELECT zip318_kind FROM v_transactions",
)
.unwrap();
let before = transactions(&db.conn);
WalletMigrator::new().init_or_migrate(&mut db).unwrap();
assert_eq!(transactions(&db.conn), before);
let repaired = view(&db.conn);
assert!(repaired.contains("transactions.zip318_kind"));
assert!(
repaired.contains("GROUP BY sent_notes.from_account_id, sent_notes.transaction_id")
);
db.conn
.prepare("SELECT * FROM application_history LIMIT 0")
.unwrap();
drop(db);
let mut db =
WalletDb::for_path(file.path(), Network::TestNetwork, test_clock(), test_rng())
.unwrap();
WalletMigrator::new().init_or_migrate(&mut db).unwrap();
assert_eq!(transactions(&db.conn), before);
assert_eq!(view(&db.conn), repaired);
let recorded: u32 = db
.conn
.query_row(
"SELECT COUNT(*) FROM schemer_migrations WHERE id = ?1",
[MIGRATION_ID.as_bytes().as_slice()],
|row| row.get(0),
)
.unwrap();
assert_eq!(recorded, 1);
}
#[test]
fn missing_retained_column_rolls_back_the_view_and_journal() {
let file = tempfile::NamedTempFile::new().unwrap();
let mut db =
WalletDb::for_path(file.path(), Network::TestNetwork, test_clock(), test_rng())
.unwrap();
WalletMigrator::new()
.init_or_migrate_to(&mut db, DEPENDENCIES)
.unwrap();
install_historical_sender_grouping_view(&db.conn);
db.conn
.execute_batch("ALTER TABLE transactions DROP COLUMN zip318_kind")
.unwrap();
let before = view(&db.conn);
assert!(WalletMigrator::new().init_or_migrate(&mut db).is_err());
assert_eq!(view(&db.conn), before);
let recorded: u32 = db
.conn
.query_row(
"SELECT COUNT(*) FROM schemer_migrations WHERE id = ?1",
[MIGRATION_ID.as_bytes().as_slice()],
|row| row.get(0),
)
.unwrap();
assert_eq!(recorded, 0);
}
}