bdk_chain/
rusqlite_impl.rs

1//! Support for persisting `bdk_chain` structures to SQLite using [`rusqlite`].
2
3use crate::*;
4use core::str::FromStr;
5
6use alloc::{
7    borrow::ToOwned,
8    boxed::Box,
9    string::{String, ToString},
10    sync::Arc,
11    vec::Vec,
12};
13use bitcoin::consensus::{Decodable, Encodable};
14use rusqlite;
15use rusqlite::named_params;
16use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
17use rusqlite::OptionalExtension;
18use rusqlite::Transaction;
19
20/// Table name for schemas.
21pub const SCHEMAS_TABLE_NAME: &str = "bdk_schemas";
22
23/// Initialize the schema table.
24fn init_schemas_table(db_tx: &Transaction) -> rusqlite::Result<()> {
25    let sql = format!("CREATE TABLE IF NOT EXISTS {SCHEMAS_TABLE_NAME}( name TEXT PRIMARY KEY NOT NULL, version INTEGER NOT NULL ) STRICT");
26    db_tx.execute(&sql, ())?;
27    Ok(())
28}
29
30/// Get schema version of `schema_name`.
31fn schema_version(db_tx: &Transaction, schema_name: &str) -> rusqlite::Result<Option<u32>> {
32    let sql = format!("SELECT version FROM {SCHEMAS_TABLE_NAME} WHERE name=:name");
33    db_tx
34        .query_row(&sql, named_params! { ":name": schema_name }, |row| {
35            row.get::<_, u32>("version")
36        })
37        .optional()
38}
39
40/// Set the `schema_version` of `schema_name`.
41fn set_schema_version(
42    db_tx: &Transaction,
43    schema_name: &str,
44    schema_version: u32,
45) -> rusqlite::Result<()> {
46    let sql = format!("REPLACE INTO {SCHEMAS_TABLE_NAME}(name, version) VALUES(:name, :version)");
47    db_tx.execute(
48        &sql,
49        named_params! { ":name": schema_name, ":version": schema_version },
50    )?;
51    Ok(())
52}
53
54/// Runs logic that initializes/migrates the table schemas.
55pub fn migrate_schema(
56    db_tx: &Transaction,
57    schema_name: &str,
58    versioned_scripts: &[&str],
59) -> rusqlite::Result<()> {
60    init_schemas_table(db_tx)?;
61    let current_version = schema_version(db_tx, schema_name)?;
62    let exec_from = current_version.map_or(0_usize, |v| v as usize + 1);
63    let scripts_to_exec = versioned_scripts.iter().enumerate().skip(exec_from);
64    for (version, script) in scripts_to_exec {
65        set_schema_version(db_tx, schema_name, version as u32)?;
66        db_tx.execute_batch(script)?;
67    }
68    Ok(())
69}
70
71impl FromSql for Impl<bitcoin::Txid> {
72    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
73        bitcoin::Txid::from_str(value.as_str()?)
74            .map(Self)
75            .map_err(from_sql_error)
76    }
77}
78
79impl ToSql for Impl<bitcoin::Txid> {
80    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
81        Ok(self.to_string().into())
82    }
83}
84
85impl FromSql for Impl<bitcoin::BlockHash> {
86    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
87        bitcoin::BlockHash::from_str(value.as_str()?)
88            .map(Self)
89            .map_err(from_sql_error)
90    }
91}
92
93impl ToSql for Impl<bitcoin::BlockHash> {
94    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
95        Ok(self.to_string().into())
96    }
97}
98
99#[cfg(feature = "miniscript")]
100impl FromSql for Impl<DescriptorId> {
101    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
102        DescriptorId::from_str(value.as_str()?)
103            .map(Self)
104            .map_err(from_sql_error)
105    }
106}
107
108#[cfg(feature = "miniscript")]
109impl ToSql for Impl<DescriptorId> {
110    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
111        Ok(self.to_string().into())
112    }
113}
114
115impl FromSql for Impl<bitcoin::Transaction> {
116    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
117        bitcoin::Transaction::consensus_decode_from_finite_reader(&mut value.as_bytes()?)
118            .map(Self)
119            .map_err(from_sql_error)
120    }
121}
122
123impl ToSql for Impl<bitcoin::Transaction> {
124    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
125        let mut bytes = Vec::<u8>::new();
126        self.consensus_encode(&mut bytes).map_err(to_sql_error)?;
127        Ok(bytes.into())
128    }
129}
130
131impl FromSql for Impl<bitcoin::ScriptBuf> {
132    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
133        Ok(bitcoin::Script::from_bytes(value.as_bytes()?)
134            .to_owned()
135            .into())
136    }
137}
138
139impl ToSql for Impl<bitcoin::ScriptBuf> {
140    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
141        Ok(self.as_bytes().into())
142    }
143}
144
145impl FromSql for Impl<bitcoin::Amount> {
146    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
147        Ok(bitcoin::Amount::from_sat(value.as_i64()?.try_into().map_err(from_sql_error)?).into())
148    }
149}
150
151impl ToSql for Impl<bitcoin::Amount> {
152    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
153        let amount: i64 = self.to_sat().try_into().map_err(to_sql_error)?;
154        Ok(amount.into())
155    }
156}
157
158#[cfg(feature = "miniscript")]
159impl FromSql for Impl<miniscript::Descriptor<miniscript::DescriptorPublicKey>> {
160    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
161        miniscript::Descriptor::from_str(value.as_str()?)
162            .map(Self)
163            .map_err(from_sql_error)
164    }
165}
166
167#[cfg(feature = "miniscript")]
168impl ToSql for Impl<miniscript::Descriptor<miniscript::DescriptorPublicKey>> {
169    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
170        Ok(self.to_string().into())
171    }
172}
173
174impl FromSql for Impl<bitcoin::Network> {
175    fn column_result(value: ValueRef<'_>) -> FromSqlResult<Self> {
176        bitcoin::Network::from_str(value.as_str()?)
177            .map(Self)
178            .map_err(from_sql_error)
179    }
180}
181
182impl ToSql for Impl<bitcoin::Network> {
183    fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
184        Ok(self.to_string().into())
185    }
186}
187
188fn from_sql_error<E: std::error::Error + Send + Sync + 'static>(err: E) -> FromSqlError {
189    FromSqlError::Other(Box::new(err))
190}
191
192fn to_sql_error<E: std::error::Error + Send + Sync + 'static>(err: E) -> rusqlite::Error {
193    rusqlite::Error::ToSqlConversionFailure(Box::new(err))
194}
195
196impl tx_graph::ChangeSet<ConfirmationBlockTime> {
197    /// Schema name for [`tx_graph::ChangeSet`].
198    pub const SCHEMA_NAME: &'static str = "bdk_txgraph";
199    /// Name of table that stores full transactions and `last_seen` timestamps.
200    pub const TXS_TABLE_NAME: &'static str = "bdk_txs";
201    /// Name of table that stores floating txouts.
202    pub const TXOUTS_TABLE_NAME: &'static str = "bdk_txouts";
203    /// Name of table that stores [`Anchor`]s.
204    pub const ANCHORS_TABLE_NAME: &'static str = "bdk_anchors";
205
206    /// Get v0 of sqlite [tx_graph::ChangeSet] schema
207    pub fn schema_v0() -> String {
208        // full transactions
209        let create_txs_table = format!(
210            "CREATE TABLE {} ( \
211            txid TEXT PRIMARY KEY NOT NULL, \
212            raw_tx BLOB, \
213            last_seen INTEGER \
214            ) STRICT",
215            Self::TXS_TABLE_NAME,
216        );
217        // floating txouts
218        let create_txouts_table = format!(
219            "CREATE TABLE {} ( \
220            txid TEXT NOT NULL, \
221            vout INTEGER NOT NULL, \
222            value INTEGER NOT NULL, \
223            script BLOB NOT NULL, \
224            PRIMARY KEY (txid, vout) \
225            ) STRICT",
226            Self::TXOUTS_TABLE_NAME,
227        );
228        // anchors
229        let create_anchors_table = format!(
230            "CREATE TABLE {} ( \
231            txid TEXT NOT NULL REFERENCES {} (txid), \
232            block_height INTEGER NOT NULL, \
233            block_hash TEXT NOT NULL, \
234            anchor BLOB NOT NULL, \
235            PRIMARY KEY (txid, block_height, block_hash) \
236            ) STRICT",
237            Self::ANCHORS_TABLE_NAME,
238            Self::TXS_TABLE_NAME,
239        );
240
241        format!("{create_txs_table}; {create_txouts_table}; {create_anchors_table}")
242    }
243
244    /// Get v1 of sqlite [tx_graph::ChangeSet] schema
245    pub fn schema_v1() -> String {
246        let add_confirmation_time_column = format!(
247            "ALTER TABLE {} ADD COLUMN confirmation_time INTEGER DEFAULT -1 NOT NULL",
248            Self::ANCHORS_TABLE_NAME,
249        );
250        let extract_confirmation_time_from_anchor_column = format!(
251            "UPDATE {} SET confirmation_time = json_extract(anchor, '$.confirmation_time')",
252            Self::ANCHORS_TABLE_NAME,
253        );
254        let drop_anchor_column = format!(
255            "ALTER TABLE {} DROP COLUMN anchor",
256            Self::ANCHORS_TABLE_NAME,
257        );
258        format!("{add_confirmation_time_column}; {extract_confirmation_time_from_anchor_column}; {drop_anchor_column}")
259    }
260
261    /// Get v2 of sqlite [tx_graph::ChangeSet] schema
262    pub fn schema_v2() -> String {
263        format!(
264            "ALTER TABLE {} ADD COLUMN last_evicted INTEGER",
265            Self::TXS_TABLE_NAME,
266        )
267    }
268
269    /// Get v3 of sqlite [tx_graph::ChangeSet] schema
270    pub fn schema_v3() -> String {
271        format!(
272            "ALTER TABLE {} ADD COLUMN first_seen INTEGER",
273            Self::TXS_TABLE_NAME,
274        )
275    }
276
277    /// Initialize sqlite tables.
278    pub fn init_sqlite_tables(db_tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
279        migrate_schema(
280            db_tx,
281            Self::SCHEMA_NAME,
282            &[
283                &Self::schema_v0(),
284                &Self::schema_v1(),
285                &Self::schema_v2(),
286                &Self::schema_v3(),
287            ],
288        )
289    }
290
291    /// Construct a [`TxGraph`] from an sqlite database.
292    ///
293    /// Remember to call [`Self::init_sqlite_tables`] beforehand.
294    pub fn from_sqlite(db_tx: &rusqlite::Transaction) -> rusqlite::Result<Self> {
295        let mut changeset = Self::default();
296
297        let mut statement = db_tx.prepare(&format!(
298            "SELECT txid, raw_tx, first_seen, last_seen, last_evicted FROM {}",
299            Self::TXS_TABLE_NAME,
300        ))?;
301        let row_iter = statement.query_map([], |row| {
302            Ok((
303                row.get::<_, Impl<bitcoin::Txid>>("txid")?,
304                row.get::<_, Option<Impl<bitcoin::Transaction>>>("raw_tx")?,
305                row.get::<_, Option<u64>>("first_seen")?,
306                row.get::<_, Option<u64>>("last_seen")?,
307                row.get::<_, Option<u64>>("last_evicted")?,
308            ))
309        })?;
310        for row in row_iter {
311            let (Impl(txid), tx, first_seen, last_seen, last_evicted) = row?;
312            if let Some(Impl(tx)) = tx {
313                changeset.txs.insert(Arc::new(tx));
314            }
315            if let Some(first_seen) = first_seen {
316                changeset.first_seen.insert(txid, first_seen);
317            }
318            if let Some(last_seen) = last_seen {
319                changeset.last_seen.insert(txid, last_seen);
320            }
321            if let Some(last_evicted) = last_evicted {
322                changeset.last_evicted.insert(txid, last_evicted);
323            }
324        }
325
326        let mut statement = db_tx.prepare(&format!(
327            "SELECT txid, vout, value, script FROM {}",
328            Self::TXOUTS_TABLE_NAME,
329        ))?;
330        let row_iter = statement.query_map([], |row| {
331            Ok((
332                row.get::<_, Impl<bitcoin::Txid>>("txid")?,
333                row.get::<_, u32>("vout")?,
334                row.get::<_, Impl<bitcoin::Amount>>("value")?,
335                row.get::<_, Impl<bitcoin::ScriptBuf>>("script")?,
336            ))
337        })?;
338        for row in row_iter {
339            let (Impl(txid), vout, Impl(value), Impl(script_pubkey)) = row?;
340            changeset.txouts.insert(
341                bitcoin::OutPoint { txid, vout },
342                bitcoin::TxOut {
343                    value,
344                    script_pubkey,
345                },
346            );
347        }
348
349        let mut statement = db_tx.prepare(&format!(
350            "SELECT block_hash, block_height, confirmation_time, txid FROM {}",
351            Self::ANCHORS_TABLE_NAME,
352        ))?;
353        let row_iter = statement.query_map([], |row| {
354            Ok((
355                row.get::<_, Impl<bitcoin::BlockHash>>("block_hash")?,
356                row.get::<_, u32>("block_height")?,
357                row.get::<_, u64>("confirmation_time")?,
358                row.get::<_, Impl<bitcoin::Txid>>("txid")?,
359            ))
360        })?;
361        for row in row_iter {
362            let (hash, height, confirmation_time, Impl(txid)) = row?;
363            changeset.anchors.insert((
364                ConfirmationBlockTime {
365                    block_id: BlockId::from((&height, &hash.0)),
366                    confirmation_time,
367                },
368                txid,
369            ));
370        }
371
372        Ok(changeset)
373    }
374
375    /// Persist `changeset` to the sqlite database.
376    ///
377    /// Remember to call [`Self::init_sqlite_tables`] beforehand.
378    pub fn persist_to_sqlite(&self, db_tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
379        let mut statement = db_tx.prepare_cached(&format!(
380            "INSERT INTO {}(txid, raw_tx) VALUES(:txid, :raw_tx) ON CONFLICT(txid) DO UPDATE SET raw_tx=:raw_tx",
381            Self::TXS_TABLE_NAME,
382        ))?;
383        for tx in &self.txs {
384            statement.execute(named_params! {
385                ":txid": Impl(tx.compute_txid()),
386                ":raw_tx": Impl(tx.as_ref().clone()),
387            })?;
388        }
389
390        let mut statement = db_tx.prepare_cached(&format!(
391            "INSERT INTO {}(txid, first_seen) VALUES(:txid, :first_seen) ON CONFLICT(txid) DO UPDATE SET first_seen=:first_seen",
392            Self::TXS_TABLE_NAME,
393        ))?;
394        for (&txid, &first_seen) in &self.first_seen {
395            let checked_time = first_seen.to_sql()?;
396            statement.execute(named_params! {
397                ":txid": Impl(txid),
398                ":first_seen": Some(checked_time),
399            })?;
400        }
401
402        let mut statement = db_tx
403            .prepare_cached(&format!(
404                "INSERT INTO {}(txid, last_seen) VALUES(:txid, :last_seen) ON CONFLICT(txid) DO UPDATE SET last_seen=:last_seen",
405                Self::TXS_TABLE_NAME,
406            ))?;
407        for (&txid, &last_seen) in &self.last_seen {
408            let checked_time = last_seen.to_sql()?;
409            statement.execute(named_params! {
410                ":txid": Impl(txid),
411                ":last_seen": Some(checked_time),
412            })?;
413        }
414
415        let mut statement = db_tx
416            .prepare_cached(&format!(
417                "INSERT INTO {}(txid, last_evicted) VALUES(:txid, :last_evicted) ON CONFLICT(txid) DO UPDATE SET last_evicted=:last_evicted",
418                Self::TXS_TABLE_NAME,
419            ))?;
420        for (&txid, &last_evicted) in &self.last_evicted {
421            let checked_time = last_evicted.to_sql()?;
422            statement.execute(named_params! {
423                ":txid": Impl(txid),
424                ":last_evicted": Some(checked_time),
425            })?;
426        }
427
428        let mut statement = db_tx.prepare_cached(&format!(
429            "REPLACE INTO {}(txid, vout, value, script) VALUES(:txid, :vout, :value, :script)",
430            Self::TXOUTS_TABLE_NAME,
431        ))?;
432        for (op, txo) in &self.txouts {
433            statement.execute(named_params! {
434                ":txid": Impl(op.txid),
435                ":vout": op.vout,
436                ":value": Impl(txo.value),
437                ":script": Impl(txo.script_pubkey.clone()),
438            })?;
439        }
440
441        let mut statement = db_tx.prepare_cached(&format!(
442            "REPLACE INTO {}(txid, block_height, block_hash, confirmation_time) VALUES(:txid, :block_height, :block_hash, :confirmation_time)",
443            Self::ANCHORS_TABLE_NAME,
444        ))?;
445        let mut statement_txid = db_tx.prepare_cached(&format!(
446            "INSERT OR IGNORE INTO {}(txid) VALUES(:txid)",
447            Self::TXS_TABLE_NAME,
448        ))?;
449        for (anchor, txid) in &self.anchors {
450            let anchor_block = anchor.anchor_block();
451            statement_txid.execute(named_params! {
452                ":txid": Impl(*txid)
453            })?;
454            statement.execute(named_params! {
455                ":txid": Impl(*txid),
456                ":block_height": anchor_block.height,
457                ":block_hash": Impl(anchor_block.hash),
458                ":confirmation_time": anchor.confirmation_time,
459            })?;
460        }
461
462        Ok(())
463    }
464}
465
466impl local_chain::ChangeSet {
467    /// Schema name for the changeset.
468    pub const SCHEMA_NAME: &'static str = "bdk_localchain";
469    /// Name of sqlite table that stores blocks of [`LocalChain`](local_chain::LocalChain).
470    pub const BLOCKS_TABLE_NAME: &'static str = "bdk_blocks";
471
472    /// Get v0 of sqlite [local_chain::ChangeSet] schema
473    pub fn schema_v0() -> String {
474        // blocks
475        format!(
476            "CREATE TABLE {} ( \
477            block_height INTEGER PRIMARY KEY NOT NULL, \
478            block_hash TEXT NOT NULL \
479            ) STRICT",
480            Self::BLOCKS_TABLE_NAME,
481        )
482    }
483
484    /// Initialize sqlite tables for persisting [`local_chain::LocalChain`].
485    pub fn init_sqlite_tables(db_tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
486        migrate_schema(db_tx, Self::SCHEMA_NAME, &[&Self::schema_v0()])
487    }
488
489    /// Construct a [`LocalChain`](local_chain::LocalChain) from sqlite database.
490    ///
491    /// Remember to call [`Self::init_sqlite_tables`] beforehand.
492    pub fn from_sqlite(db_tx: &rusqlite::Transaction) -> rusqlite::Result<Self> {
493        let mut changeset = Self::default();
494
495        let mut statement = db_tx.prepare(&format!(
496            "SELECT block_height, block_hash FROM {}",
497            Self::BLOCKS_TABLE_NAME,
498        ))?;
499        let row_iter = statement.query_map([], |row| {
500            Ok((
501                row.get::<_, u32>("block_height")?,
502                row.get::<_, Impl<bitcoin::BlockHash>>("block_hash")?,
503            ))
504        })?;
505        for row in row_iter {
506            let (height, Impl(hash)) = row?;
507            changeset.blocks.insert(height, Some(hash));
508        }
509
510        Ok(changeset)
511    }
512
513    /// Persist `changeset` to the sqlite database.
514    ///
515    /// Remember to call [`Self::init_sqlite_tables`] beforehand.
516    pub fn persist_to_sqlite(&self, db_tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
517        let mut replace_statement = db_tx.prepare_cached(&format!(
518            "REPLACE INTO {}(block_height, block_hash) VALUES(:block_height, :block_hash)",
519            Self::BLOCKS_TABLE_NAME,
520        ))?;
521        let mut delete_statement = db_tx.prepare_cached(&format!(
522            "DELETE FROM {} WHERE block_height=:block_height",
523            Self::BLOCKS_TABLE_NAME,
524        ))?;
525        for (&height, &hash) in &self.blocks {
526            match hash {
527                Some(hash) => replace_statement.execute(named_params! {
528                    ":block_height": height,
529                    ":block_hash": Impl(hash),
530                })?,
531                None => delete_statement.execute(named_params! {
532                    ":block_height": height,
533                })?,
534            };
535        }
536
537        Ok(())
538    }
539}
540
541#[cfg(feature = "miniscript")]
542impl keychain_txout::ChangeSet {
543    /// Schema name for the changeset.
544    pub const SCHEMA_NAME: &'static str = "bdk_keychaintxout";
545    /// Name for table that stores last revealed indices per descriptor id.
546    pub const LAST_REVEALED_TABLE_NAME: &'static str = "bdk_descriptor_last_revealed";
547    /// Name for table that stores derived spks.
548    pub const DERIVED_SPKS_TABLE_NAME: &'static str = "bdk_descriptor_derived_spks";
549
550    /// Get v0 of sqlite [keychain_txout::ChangeSet] schema
551    pub fn schema_v0() -> String {
552        format!(
553            "CREATE TABLE {} ( \
554            descriptor_id TEXT PRIMARY KEY NOT NULL, \
555            last_revealed INTEGER NOT NULL \
556            ) STRICT",
557            Self::LAST_REVEALED_TABLE_NAME,
558        )
559    }
560
561    /// Get v1 of sqlite [keychain_txout::ChangeSet] schema
562    pub fn schema_v1() -> String {
563        format!(
564            "CREATE TABLE {} ( \
565            descriptor_id TEXT NOT NULL, \
566            spk_index INTEGER NOT NULL, \
567            spk BLOB NOT NULL, \
568            PRIMARY KEY (descriptor_id, spk_index) \
569            ) STRICT",
570            Self::DERIVED_SPKS_TABLE_NAME,
571        )
572    }
573
574    /// Initialize sqlite tables for persisting
575    /// [`KeychainTxOutIndex`](keychain_txout::KeychainTxOutIndex).
576    pub fn init_sqlite_tables(db_tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
577        migrate_schema(
578            db_tx,
579            Self::SCHEMA_NAME,
580            &[&Self::schema_v0(), &Self::schema_v1()],
581        )
582    }
583
584    /// Construct [`KeychainTxOutIndex`](keychain_txout::KeychainTxOutIndex) from sqlite database
585    /// and given parameters.
586    ///
587    /// Remember to call [`Self::init_sqlite_tables`] beforehand.
588    pub fn from_sqlite(db_tx: &rusqlite::Transaction) -> rusqlite::Result<Self> {
589        let mut changeset = Self::default();
590
591        let mut statement = db_tx.prepare(&format!(
592            "SELECT descriptor_id, last_revealed FROM {}",
593            Self::LAST_REVEALED_TABLE_NAME,
594        ))?;
595        let row_iter = statement.query_map([], |row| {
596            Ok((
597                row.get::<_, Impl<DescriptorId>>("descriptor_id")?,
598                row.get::<_, u32>("last_revealed")?,
599            ))
600        })?;
601        for row in row_iter {
602            let (Impl(descriptor_id), last_revealed) = row?;
603            changeset.last_revealed.insert(descriptor_id, last_revealed);
604        }
605
606        let mut statement = db_tx.prepare(&format!(
607            "SELECT descriptor_id, spk_index, spk FROM {}",
608            Self::DERIVED_SPKS_TABLE_NAME
609        ))?;
610        let row_iter = statement.query_map([], |row| {
611            Ok((
612                row.get::<_, Impl<DescriptorId>>("descriptor_id")?,
613                row.get::<_, u32>("spk_index")?,
614                row.get::<_, Impl<bitcoin::ScriptBuf>>("spk")?,
615            ))
616        })?;
617        for row in row_iter {
618            let (Impl(descriptor_id), spk_index, Impl(spk)) = row?;
619            changeset
620                .spk_cache
621                .entry(descriptor_id)
622                .or_default()
623                .insert(spk_index, spk);
624        }
625
626        Ok(changeset)
627    }
628
629    /// Persist `changeset` to the sqlite database.
630    ///
631    /// Remember to call [`Self::init_sqlite_tables`] beforehand.
632    pub fn persist_to_sqlite(&self, db_tx: &rusqlite::Transaction) -> rusqlite::Result<()> {
633        let mut statement = db_tx.prepare_cached(&format!(
634            "REPLACE INTO {}(descriptor_id, last_revealed) VALUES(:descriptor_id, :last_revealed)",
635            Self::LAST_REVEALED_TABLE_NAME,
636        ))?;
637        for (&descriptor_id, &last_revealed) in &self.last_revealed {
638            statement.execute(named_params! {
639                ":descriptor_id": Impl(descriptor_id),
640                ":last_revealed": last_revealed,
641            })?;
642        }
643
644        let mut statement = db_tx.prepare_cached(&format!(
645            "REPLACE INTO {}(descriptor_id, spk_index, spk) VALUES(:descriptor_id, :spk_index, :spk)",
646            Self::DERIVED_SPKS_TABLE_NAME,
647        ))?;
648        for (&descriptor_id, spks) in &self.spk_cache {
649            for (&spk_index, spk) in spks {
650                statement.execute(named_params! {
651                    ":descriptor_id": Impl(descriptor_id),
652                    ":spk_index": spk_index,
653                    ":spk": Impl(spk.clone()),
654                })?;
655            }
656        }
657
658        Ok(())
659    }
660}
661
662#[cfg(test)]
663#[cfg_attr(coverage_nightly, coverage(off))]
664mod test {
665    use super::*;
666
667    use bdk_testenv::{anyhow, hash};
668    use bitcoin::{absolute, transaction, TxIn, TxOut};
669
670    #[test]
671    fn can_persist_anchors_and_txs_independently() -> anyhow::Result<()> {
672        type ChangeSet = tx_graph::ChangeSet<ConfirmationBlockTime>;
673        let mut conn = rusqlite::Connection::open_in_memory()?;
674
675        // init tables
676        {
677            let db_tx = conn.transaction()?;
678            ChangeSet::init_sqlite_tables(&db_tx)?;
679            db_tx.commit()?;
680        }
681
682        let tx = bitcoin::Transaction {
683            version: transaction::Version::TWO,
684            lock_time: absolute::LockTime::ZERO,
685            input: vec![TxIn::default()],
686            output: vec![TxOut::NULL],
687        };
688        let tx = Arc::new(tx);
689        let txid = tx.compute_txid();
690        let anchor = ConfirmationBlockTime {
691            block_id: BlockId {
692                height: 21,
693                hash: hash!("anchor"),
694            },
695            confirmation_time: 1342,
696        };
697
698        // First persist the anchor
699        {
700            let changeset = ChangeSet {
701                anchors: [(anchor, txid)].into(),
702                ..Default::default()
703            };
704            let db_tx = conn.transaction()?;
705            changeset.persist_to_sqlite(&db_tx)?;
706            db_tx.commit()?;
707        }
708
709        // Now persist the tx
710        {
711            let changeset = ChangeSet {
712                txs: [tx.clone()].into(),
713                ..Default::default()
714            };
715            let db_tx = conn.transaction()?;
716            changeset.persist_to_sqlite(&db_tx)?;
717            db_tx.commit()?;
718        }
719
720        // Loading changeset from sqlite should succeed
721        {
722            let db_tx = conn.transaction()?;
723            let changeset = ChangeSet::from_sqlite(&db_tx)?;
724            db_tx.commit()?;
725            assert!(changeset.txs.contains(&tx));
726            assert!(changeset.anchors.contains(&(anchor, txid)));
727        }
728
729        Ok(())
730    }
731
732    #[test]
733    fn v0_to_v3_schema_migration_is_backward_compatible() -> anyhow::Result<()> {
734        type ChangeSet = tx_graph::ChangeSet<ConfirmationBlockTime>;
735        let mut conn = rusqlite::Connection::open_in_memory()?;
736
737        // Create initial database with v0 sqlite schema
738        {
739            let db_tx = conn.transaction()?;
740            migrate_schema(&db_tx, ChangeSet::SCHEMA_NAME, &[&ChangeSet::schema_v0()])?;
741            db_tx.commit()?;
742        }
743
744        let tx = bitcoin::Transaction {
745            version: transaction::Version::TWO,
746            lock_time: absolute::LockTime::ZERO,
747            input: vec![TxIn::default()],
748            output: vec![TxOut::NULL],
749        };
750        let tx = Arc::new(tx);
751        let txid = tx.compute_txid();
752        let anchor = ConfirmationBlockTime {
753            block_id: BlockId {
754                height: 21,
755                hash: hash!("anchor"),
756            },
757            confirmation_time: 1342,
758        };
759
760        // Persist anchor with v0 sqlite schema
761        {
762            let changeset = ChangeSet {
763                anchors: [(anchor, txid)].into(),
764                ..Default::default()
765            };
766            let mut statement = conn.prepare_cached(&format!(
767                "REPLACE INTO {} (txid, block_height, block_hash, anchor)
768                 VALUES(
769                    :txid,
770                    :block_height,
771                    :block_hash,
772                    jsonb('{{
773                        \"block_id\": {{\"height\": {},\"hash\":\"{}\"}},
774                        \"confirmation_time\": {}
775                    }}')
776                 )",
777                ChangeSet::ANCHORS_TABLE_NAME,
778                anchor.block_id.height,
779                anchor.block_id.hash,
780                anchor.confirmation_time,
781            ))?;
782            let mut statement_txid = conn.prepare_cached(&format!(
783                "INSERT OR IGNORE INTO {}(txid) VALUES(:txid)",
784                ChangeSet::TXS_TABLE_NAME,
785            ))?;
786            for (anchor, txid) in &changeset.anchors {
787                let anchor_block = anchor.anchor_block();
788                statement_txid.execute(named_params! {
789                    ":txid": Impl(*txid)
790                })?;
791                match statement.execute(named_params! {
792                    ":txid": Impl(*txid),
793                    ":block_height": anchor_block.height,
794                    ":block_hash": Impl(anchor_block.hash),
795                }) {
796                    Ok(updated) => assert_eq!(updated, 1),
797                    Err(err) => panic!("update failed: {err}"),
798                }
799            }
800        }
801
802        // Apply v1, v2, v3 sqlite schema to tables with data
803        {
804            let db_tx = conn.transaction()?;
805            migrate_schema(
806                &db_tx,
807                ChangeSet::SCHEMA_NAME,
808                &[
809                    &ChangeSet::schema_v0(),
810                    &ChangeSet::schema_v1(),
811                    &ChangeSet::schema_v2(),
812                    &ChangeSet::schema_v3(),
813                ],
814            )?;
815            db_tx.commit()?;
816        }
817
818        // Loading changeset from sqlite should succeed
819        {
820            let db_tx = conn.transaction()?;
821            let changeset = ChangeSet::from_sqlite(&db_tx)?;
822            db_tx.commit()?;
823            assert!(changeset.anchors.contains(&(anchor, txid)));
824        }
825
826        Ok(())
827    }
828
829    #[test]
830    fn can_persist_first_seen() -> anyhow::Result<()> {
831        use bitcoin::hashes::Hash;
832
833        type ChangeSet = tx_graph::ChangeSet<ConfirmationBlockTime>;
834        let mut conn = rusqlite::Connection::open_in_memory()?;
835
836        // Init tables
837        {
838            let db_tx = conn.transaction()?;
839            ChangeSet::init_sqlite_tables(&db_tx)?;
840            db_tx.commit()?;
841        }
842
843        let txid = bitcoin::Txid::all_zeros();
844        let first_seen = 100;
845
846        // Persist `first_seen`
847        {
848            let changeset = ChangeSet {
849                first_seen: [(txid, first_seen)].into(),
850                ..Default::default()
851            };
852            let db_tx = conn.transaction()?;
853            changeset.persist_to_sqlite(&db_tx)?;
854            db_tx.commit()?;
855        }
856
857        // Load from sqlite should succeed
858        {
859            let db_tx = conn.transaction()?;
860            let changeset = ChangeSet::from_sqlite(&db_tx)?;
861            db_tx.commit()?;
862            assert_eq!(changeset.first_seen.get(&txid), Some(&first_seen));
863        }
864
865        Ok(())
866    }
867
868    #[test]
869    fn can_persist_last_evicted() -> anyhow::Result<()> {
870        use bitcoin::hashes::Hash;
871
872        type ChangeSet = tx_graph::ChangeSet<ConfirmationBlockTime>;
873        let mut conn = rusqlite::Connection::open_in_memory()?;
874
875        // Init tables
876        {
877            let db_tx = conn.transaction()?;
878            ChangeSet::init_sqlite_tables(&db_tx)?;
879            db_tx.commit()?;
880        }
881
882        let txid = bitcoin::Txid::all_zeros();
883        let last_evicted = 100;
884
885        // Persist `last_evicted`
886        {
887            let changeset = ChangeSet {
888                last_evicted: [(txid, last_evicted)].into(),
889                ..Default::default()
890            };
891            let db_tx = conn.transaction()?;
892            changeset.persist_to_sqlite(&db_tx)?;
893            db_tx.commit()?;
894        }
895
896        // Load from sqlite should succeed
897        {
898            let db_tx = conn.transaction()?;
899            let changeset = ChangeSet::from_sqlite(&db_tx)?;
900            db_tx.commit()?;
901            assert_eq!(changeset.last_evicted.get(&txid), Some(&last_evicted));
902        }
903
904        Ok(())
905    }
906}
bdk_chain/rusqlite_impl.rs

bdk_chain/
rusqlite_impl.rs
