miden_client_sqlite_store/db_management/
utils.rs

1use std::string::String;
2use std::sync::LazyLock;
3use std::vec::Vec;
4
5use miden_client::store::StoreError;
6use miden_protocol::crypto::hash::blake::{Blake3_256, Blake3Digest};
7use rusqlite::types::FromSql;
8use rusqlite::{Connection, OptionalExtension, Result, ToSql, Transaction, params};
9use rusqlite_migration::{M, Migrations, SchemaVersion};
10
11use super::errors::SqliteStoreError;
12use crate::sql_error::SqlResultExt;
13
14// MACROS
15// ================================================================================================
16
17/// Auxiliary macro which substitutes `$src` token by `$dst` expression.
18#[macro_export]
19macro_rules! subst {
20    ($src:tt, $dst:expr_2021) => {
21        $dst
22    };
23}
24
25/// Generates a simple insert SQL statement with parameters for the provided table name and fields.
26/// Supports optional conflict resolution (adding "| REPLACE" or "| IGNORE" at the end will generate
27/// "OR REPLACE" and "OR IGNORE", correspondingly).
28///
29/// # Usage:
30///
31/// ```ignore
32/// insert_sql!(users { id, first_name, last_name, age } | REPLACE);
33/// ```
34///
35/// which generates:
36/// ```sql
37/// INSERT OR REPLACE INTO `users` (`id`, `first_name`, `last_name`, `age`) VALUES (?, ?, ?, ?)
38/// ```
39#[macro_export]
40macro_rules! insert_sql {
41    ($table:ident { $first_field:ident $(, $($field:ident),+)? $(,)? } $(| $on_conflict:expr)?) => {
42        concat!(
43            stringify!(INSERT $(OR $on_conflict)? INTO ),
44            "`",
45            stringify!($table),
46            "` (`",
47            stringify!($first_field),
48            $($(concat!("`, `", stringify!($field))),+ ,)?
49            "`) VALUES (",
50            subst!($first_field, "?"),
51            $($(subst!($field, ", ?")),+ ,)?
52            ")"
53        )
54    };
55}
56
57// MIGRATIONS
58// ================================================================================================
59
60type Hash = Blake3Digest<32>;
61
62const MIGRATION_SCRIPTS: [&str; 2] = [
63    include_str!("../store.sql"),
64    include_str!("../migrations/0002_prune_output_note_tags.sql"),
65];
66static MIGRATION_HASHES: LazyLock<Vec<Hash>> = LazyLock::new(compute_migration_hashes);
67static MIGRATIONS: LazyLock<Migrations> = LazyLock::new(prepare_migrations);
68
69/// Builds the migration for `MIGRATION_SCRIPTS[index]`. The cumulative hash is written by a
70/// hook inside the migration's own transaction, so the schema version and the stored hash can
71/// never diverge (an interrupted upgrade rolls both back together).
72fn migration(index: usize) -> M<'static> {
73    let hash = (*MIGRATION_HASHES[index]).to_vec();
74    M::up_with_hook(MIGRATION_SCRIPTS[index], move |tx: &Transaction| {
75        set_migrations_value(tx, DB_MIGRATION_HASH_FIELD, &hash)?;
76        Ok(())
77    })
78    .foreign_key_check()
79}
80
81const DB_MIGRATION_HASH_FIELD: &str = "db-migration-hash";
82
83/// Applies the migrations to the database.
84pub fn apply_migrations(conn: &mut Connection) -> Result<(), SqliteStoreError> {
85    let version_before = MIGRATIONS.current_version(conn)?;
86
87    if let SchemaVersion::Inside(ver) = version_before {
88        if !table_exists(&conn.transaction()?, "migrations")? {
89            return Err(SqliteStoreError::MissingMigrationsTable);
90        }
91
92        let expected_hash = &*MIGRATION_HASHES[ver.get() - 1];
93
94        let Ok(Some(actual_hash)) = get_migrations_value::<Vec<u8>>(conn, DB_MIGRATION_HASH_FIELD)
95        else {
96            return Err(SqliteStoreError::DatabaseError("Migration hash not found".to_owned()));
97        };
98
99        if &actual_hash[..] != expected_hash {
100            return Err(SqliteStoreError::MigrationHashMismatch);
101        }
102    }
103
104    MIGRATIONS.to_latest(conn)?;
105
106    Ok(())
107}
108
109fn prepare_migrations() -> Migrations<'static> {
110    Migrations::new((0..MIGRATION_SCRIPTS.len()).map(migration).collect())
111}
112
113fn compute_migration_hashes() -> Vec<Hash> {
114    let mut accumulator = Hash::default();
115    MIGRATION_SCRIPTS
116        .iter()
117        .map(|sql| {
118            let script_hash = Blake3_256::hash(preprocess_sql(sql).as_bytes());
119            accumulator = Blake3_256::merge(&[accumulator, script_hash]);
120            accumulator
121        })
122        .collect()
123}
124
125fn preprocess_sql(sql: &str) -> String {
126    // TODO: We can also remove all comments here (need to analyze the SQL script in order to remove
127    //       comments in string literals).
128    remove_spaces(sql)
129}
130
131fn remove_spaces(str: &str) -> String {
132    str.chars().filter(|chr| !chr.is_whitespace()).collect()
133}
134
135pub fn get_migrations_value<T: FromSql>(conn: &mut Connection, name: &str) -> Result<Option<T>> {
136    conn.transaction()?
137        .query_row("SELECT value FROM migrations WHERE name = $1", params![name], |row| row.get(0))
138        .optional()
139}
140
141pub fn set_migrations_value<T: ToSql>(conn: &Connection, name: &str, value: &T) -> Result<()> {
142    let count =
143        conn.execute(insert_sql!(migrations { name, value } | REPLACE), params![name, value])?;
144
145    debug_assert_eq!(count, 1);
146
147    Ok(())
148}
149
150pub fn get_setting<T: FromSql>(conn: &mut Connection, name: &str) -> Result<Option<T>, StoreError> {
151    conn.transaction()
152        .into_store_error()?
153        .query_row("SELECT value FROM settings WHERE name = $1", params![name], |row| row.get(0))
154        .optional()
155        .into_store_error()
156}
157
158pub fn set_setting<T: ToSql>(conn: &Connection, name: &str, value: &T) -> Result<()> {
159    let count =
160        conn.execute(insert_sql!(settings { name, value } | REPLACE), params![name, value])?;
161
162    debug_assert_eq!(count, 1);
163
164    Ok(())
165}
166
167pub fn remove_setting(conn: &Connection, name: &str) -> Result<(), StoreError> {
168    let count = conn
169        .execute("DELETE FROM settings WHERE name = $1", params![name])
170        .into_store_error()?;
171
172    debug_assert_eq!(count, 1);
173
174    Ok(())
175}
176
177pub fn list_setting_keys(conn: &Connection) -> Result<Vec<String>, StoreError> {
178    let mut stmt = conn.prepare("SELECT name FROM settings").into_store_error()?;
179    stmt.query_map([], |row| row.get::<_, String>(0))
180        .into_store_error()?
181        .collect::<Result<Vec<String>, _>>()
182        .into_store_error()
183}
184
185/// Checks if a table exists in the database.
186pub fn table_exists(transaction: &Transaction, table_name: &str) -> rusqlite::Result<bool> {
187    Ok(transaction
188        .query_row(
189            "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = $1",
190            params![table_name],
191            |_| Ok(()),
192        )
193        .optional()?
194        .is_some())
195}
196
197// TESTS
198// ================================================================================================
199
200#[cfg(test)]
201mod tests {
202    use std::vec;
203    use std::vec::Vec;
204
205    use miden_client::sync::NoteTagSource;
206    use miden_client::utils::Serializable;
207    use miden_protocol::EMPTY_WORD;
208    use miden_protocol::crypto::hash::rpo::Rpo256;
209    use miden_protocol::note::NoteDetailsCommitment;
210    use rusqlite::{Connection, params};
211    use rusqlite_migration::Migrations;
212
213    use super::{
214        DB_MIGRATION_HASH_FIELD,
215        MIGRATION_HASHES,
216        apply_migrations,
217        get_migrations_value,
218        migration,
219    };
220
221    fn note_source_bytes(commitment: &NoteDetailsCommitment) -> Vec<u8> {
222        NoteTagSource::Note(*commitment).to_bytes()
223    }
224
225    fn insert_tag(conn: &Connection, source: &[u8]) {
226        conn.execute("INSERT INTO tags (tag, source) VALUES (?, ?)", params![vec![0u8; 4], source])
227            .unwrap();
228    }
229
230    fn insert_output_note(conn: &Connection, commitment: &NoteDetailsCommitment) {
231        conn.execute(
232            "INSERT INTO output_notes (details_commitment, note_id, recipient_digest, assets, \
233             metadata, expected_height, state_discriminant, state, attachments) \
234             VALUES (?, ?, '0xrecipient', x'00', x'00', 0, 0, x'00', x'00')",
235            params![commitment.to_hex(), commitment.to_hex()],
236        )
237        .unwrap();
238    }
239
240    fn insert_input_note(
241        conn: &Connection,
242        commitment: &NoteDetailsCommitment,
243        state_discriminant: u8,
244    ) {
245        conn.execute(
246            "INSERT OR IGNORE INTO notes_scripts (script_root, serialized_note_script) \
247             VALUES ('0xscript', x'00')",
248            [],
249        )
250        .unwrap();
251        conn.execute(
252            "INSERT INTO input_notes (details_commitment, assets, attachments, serial_number, \
253             inputs, script_root, state_discriminant, state, created_at) \
254             VALUES (?, x'00', x'00', x'00', x'00', '0xscript', ?, x'00', 0)",
255            params![commitment.to_hex(), state_discriminant],
256        )
257        .unwrap();
258    }
259
260    /// The prune migration deletes output-note tags (and duplicates) but keeps tags still
261    /// needed by pre-inclusion input notes, input-only tags, and non-`Note`-sourced tags.
262    #[test]
263    fn migration_prunes_output_note_tags() {
264        let mut conn = Connection::open_in_memory().unwrap();
265
266        // Bring the database to schema version 1 (pre-prune), the way apply_migrations
267        // would have left a store created before the prune migration existed. The hook must
268        // leave the stored hash consistent with the version at every step.
269        Migrations::new(vec![migration(0)]).to_latest(&mut conn).unwrap();
270        let stored_hash: Vec<u8> =
271            get_migrations_value(&mut conn, DB_MIGRATION_HASH_FIELD).unwrap().unwrap();
272        assert_eq!(&stored_hash[..], &*MIGRATION_HASHES[0]);
273
274        let word = |seed: &[u8]| Rpo256::hash(seed);
275        // Output note with no input-note counterpart: its tag must be pruned.
276        let output_only = NoteDetailsCommitment::from_raw_commitments(EMPTY_WORD, word(b"a"));
277        // Self-directed note whose input record is still Expected: tag must be kept.
278        let self_directed = NoteDetailsCommitment::from_raw_commitments(EMPTY_WORD, word(b"b"));
279        // Self-directed note whose input record already committed: tag must be pruned.
280        let self_committed = NoteDetailsCommitment::from_raw_commitments(EMPTY_WORD, word(b"c"));
281        // Imported expected note with no output record: tag must be kept.
282        let imported_input = NoteDetailsCommitment::from_raw_commitments(EMPTY_WORD, word(b"d"));
283
284        insert_output_note(&conn, &output_only);
285        insert_tag(&conn, &note_source_bytes(&output_only));
286
287        insert_output_note(&conn, &self_directed);
288        insert_input_note(&conn, &self_directed, 0); // Expected
289        insert_tag(&conn, &note_source_bytes(&self_directed));
290
291        insert_output_note(&conn, &self_committed);
292        insert_input_note(&conn, &self_committed, 2); // Committed
293        insert_tag(&conn, &note_source_bytes(&self_committed));
294
295        // Inserted twice: the migration must also collapse duplicate rows.
296        insert_input_note(&conn, &imported_input, 0); // Expected
297        insert_tag(&conn, &note_source_bytes(&imported_input));
298        insert_tag(&conn, &note_source_bytes(&imported_input));
299
300        // Account-sourced tag (discriminant 0): must never be touched.
301        let account_source = vec![0u8; 16];
302        insert_tag(&conn, &account_source);
303
304        apply_migrations(&mut conn).unwrap();
305
306        // The stored hash must track the latest applied migration.
307        let stored_hash: Vec<u8> =
308            get_migrations_value(&mut conn, DB_MIGRATION_HASH_FIELD).unwrap().unwrap();
309        assert_eq!(&stored_hash[..], &*MIGRATION_HASHES[MIGRATION_HASHES.len() - 1]);
310
311        let remaining: Vec<Vec<u8>> = conn
312            .prepare("SELECT source FROM tags")
313            .unwrap()
314            .query_map([], |row| row.get(0))
315            .unwrap()
316            .collect::<Result<_, _>>()
317            .unwrap();
318
319        assert!(!remaining.contains(&note_source_bytes(&output_only)));
320        assert!(!remaining.contains(&note_source_bytes(&self_committed)));
321        assert!(remaining.contains(&note_source_bytes(&self_directed)));
322        assert!(remaining.contains(&note_source_bytes(&imported_input)));
323        assert!(remaining.contains(&account_source));
324        assert_eq!(remaining.len(), 3);
325    }
326}
miden_client_sqlite_store/db_management/utils.rs

miden_client_sqlite_store/db_management/
utils.rs
