Skip to main content

pxh/
sync.rs

1//! Database merge engine for sync.
2//!
3//! This is the shared core of every sync mode (directory, SSH, stdin/stdout):
4//! merge one pxh database file into the live connection with deduplication,
5//! optional secret filtering, an optional incremental watermark, and
6//! unsealed-row upgrades. Callers own transport and presentation; this module
7//! owns the merge semantics and reports what happened via [`MergeStats`].
8
9use std::path::Path;
10use std::time::Duration;
11
12use anyhow::{Result, bail};
13use regex::bytes::RegexSet;
14use rusqlite::Connection;
15
16/// What a merge did, for callers to present or assert on.
17#[derive(Debug, Clone, Copy, PartialEq, Eq)]
18pub struct MergeStats {
19    /// Rows we actually scanned in the source (above the watermark, if any).
20    pub considered: i64,
21    /// Rows newly inserted into main.
22    pub added: i64,
23    /// Rows skipped due to secret-pattern filtering.
24    pub filtered: i64,
25    /// `MAX(id)` from the source -- the next watermark for it.
26    pub new_max_id: Option<i64>,
27}
28
29/// Generous budget: sync is background work, so wait out long writers
30/// rather than failing the merge.
31const WRITE_RETRY_BUDGET: Duration = Duration::from_secs(30);
32const CHUNK_SIZE: i64 = 5000;
33
34/// Read the incremental-sync watermark recorded for a source machine, i.e.
35/// the highest source `id` a previous merge fully processed.
36pub fn sync_watermark(conn: &Connection, machine_id: u64) -> Option<i64> {
37    crate::get_setting(conn, &watermark_key(machine_id))
38        .ok()
39        .flatten()
40        .and_then(|bs| std::str::from_utf8(bs.as_slice()).ok()?.parse::<i64>().ok())
41}
42
43/// Persist the watermark for a source machine. Call only after a fully
44/// successful merge: an interrupted merge must re-consider its rows.
45pub fn set_sync_watermark(conn: &Connection, machine_id: u64, new_max: i64) -> Result<()> {
46    let bs = bstr::BString::from(new_max.to_string());
47    crate::set_setting(conn, &watermark_key(machine_id), &bs)
48}
49
50fn watermark_key(machine_id: u64) -> String {
51    format!("sync_watermark_{machine_id}")
52}
53
54/// Merge history from a database file into `conn`, with an optional secret
55/// filter (rows whose command matches are skipped and counted) and an
56/// optional incremental-sync watermark (skip rows in the source whose
57/// `id <= watermark`). The unsealed-row update always scans the whole source
58/// since seal info may sit at any id, watermark or not.
59///
60/// The source is attached read-only and never written to: peers in a shared
61/// directory may belong to other machines, be on read-only media, or not be
62/// pxh databases at all. Older schemas (pre-`machine_id`) are read as-is.
63pub fn merge_database_from_file(
64    conn: &mut Connection,
65    path: &Path,
66    secret_filter: Option<&RegexSet>,
67    watermark: Option<i64>,
68) -> Result<MergeStats> {
69    conn.execute("ATTACH DATABASE ? AS other", [read_only_uri(path)])?;
70    let result = merge_attached(conn, secret_filter, watermark);
71    conn.execute("DETACH DATABASE other", ())?;
72    result
73}
74
75/// `file:` URI that opens `path` read-only. Bytes outside the URI-safe set
76/// are percent-encoded so arbitrary (even non-UTF8) paths round-trip.
77fn read_only_uri(path: &Path) -> String {
78    use std::os::unix::ffi::OsStrExt;
79    let mut uri = String::from("file:");
80    for &b in path.as_os_str().as_bytes() {
81        match b {
82            b'A'..=b'Z' | b'a'..=b'z' | b'0'..=b'9' | b'-' | b'.' | b'_' | b'~' | b'/' => {
83                uri.push(b as char);
84            }
85            _ => uri.push_str(&format!("%{b:02X}")),
86        }
87    }
88    uri.push_str("?mode=ro");
89    uri
90}
91
92/// Column names of `other.command_history`; empty when the table is absent.
93fn source_columns(conn: &Connection) -> rusqlite::Result<Vec<String>> {
94    conn.prepare("PRAGMA other.table_info(command_history)")?
95        .query_map((), |row| row.get::<_, String>(1))?
96        .collect()
97}
98
99/// Merge `other.command_history` (already ATTACHed) into main.
100///
101/// Structured to minimize write-lock hold time so concurrent shell hooks
102/// (insert/seal, which only retry for ~1s) don't hit "database is locked"
103/// during a sync: all reads and regex filtering happen outside any write
104/// transaction (WAL readers never block writers), and inserts run in
105/// id-ordered chunks, each a short BEGIN IMMEDIATE transaction via
106/// `with_write_retry`. The merge is therefore not atomic, but INSERT OR
107/// IGNORE is idempotent and the caller only advances the watermark after
108/// full success, so an interrupted merge simply re-considers rows next sync.
109fn merge_attached(
110    conn: &mut Connection,
111    secret_filter: Option<&RegexSet>,
112    watermark: Option<i64>,
113) -> Result<MergeStats> {
114    let columns = source_columns(conn)?;
115    if columns.is_empty() {
116        bail!("not a pxh database (no command_history table)");
117    }
118    // Sources written before schema v1 have no machine_id column.
119    let machine_id = if columns.iter().any(|c| c == "machine_id") { "machine_id" } else { "NULL" };
120
121    // -1 sentinel matches all rows (id is AUTOINCREMENT, so always >= 1).
122    let lo = watermark.unwrap_or(-1);
123
124    // Read-only pre-pass: source stats, taken before the merge so
125    // `considered` reflects what we scan below.
126    let considered: i64 = conn
127        .prepare("SELECT COUNT(*) FROM other.command_history WHERE id > ?")?
128        .query_row([lo], |r| r.get(0))?;
129
130    // Highest id in source -- caller persists as the next watermark.
131    let new_max_id: Option<i64> =
132        conn.prepare("SELECT MAX(id) FROM other.command_history")?.query_row((), |r| r.get(0)).ok();
133
134    let mut added: usize = 0;
135    let mut filtered_count: i64 = 0;
136    let mut cursor = lo;
137    loop {
138        // Upper id bound of the next chunk (NULL once the source is drained).
139        let hi: Option<i64> = conn
140            .prepare(
141                "SELECT MAX(id) FROM (SELECT id FROM other.command_history
142                  WHERE id > ? ORDER BY id LIMIT ?)",
143            )?
144            .query_row((cursor, CHUNK_SIZE), |r| r.get(0))?;
145        let Some(hi) = hi else { break };
146
147        if let Some(regex_set) = secret_filter {
148            // Read and regex-filter the chunk before taking the write
149            // lock; pattern matching is the expensive part of the merge.
150            type SourceRow = (
151                i64,
152                Vec<u8>,
153                String,
154                Option<Vec<u8>>,
155                Option<Vec<u8>>,
156                Option<Vec<u8>>,
157                Option<i64>,
158                Option<i64>,
159                Option<i64>,
160                Option<i64>,
161            );
162            let rows: Vec<SourceRow> = conn
163                .prepare(&format!(
164                    r#"
165SELECT session_id, full_command, shellname, hostname, username,
166       working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, {machine_id}
167FROM other.command_history
168WHERE id > ? AND id <= ?
169"#
170                ))?
171                .query_map((cursor, hi), |row| {
172                    Ok((
173                        row.get(0)?,
174                        row.get(1)?,
175                        row.get(2)?,
176                        row.get(3)?,
177                        row.get(4)?,
178                        row.get(5)?,
179                        row.get(6)?,
180                        row.get(7)?,
181                        row.get(8)?,
182                        row.get(9)?,
183                    ))
184                })?
185                .collect::<rusqlite::Result<_>>()?;
186
187            let total = rows.len();
188            let keep: Vec<SourceRow> =
189                rows.into_iter().filter(|row| !regex_set.is_match(&row.1)).collect();
190            filtered_count += (total - keep.len()) as i64;
191
192            added += crate::with_write_retry(conn, WRITE_RETRY_BUDGET, |tx| {
193                let mut inserted = 0;
194                for row in &keep {
195                    inserted += tx.execute(
196                        r#"
197INSERT OR IGNORE INTO main.command_history (
198    session_id, full_command, shellname, hostname, username,
199    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
200)
201VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
202"#,
203                        rusqlite::params![
204                            row.0, row.1, row.2, row.3, row.4, row.5, row.6, row.7, row.8, row.9
205                        ],
206                    )?;
207                }
208                Ok(inserted)
209            })?;
210        } else {
211            // No filtering, bulk-copy the chunk in SQL.
212            added += crate::with_write_retry(conn, WRITE_RETRY_BUDGET, |tx| {
213                tx.execute(
214                    &format!(
215                        r#"
216INSERT OR IGNORE INTO main.command_history (
217    session_id, full_command, shellname, hostname, username,
218    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, machine_id
219)
220SELECT session_id, full_command, shellname, hostname, username,
221    working_directory, exit_status, start_unix_timestamp, end_unix_timestamp, {machine_id}
222FROM other.command_history
223WHERE id > ? AND id <= ?
224"#
225                    ),
226                    (cursor, hi),
227                )
228            })?;
229        }
230        cursor = hi;
231    }
232
233    // Upgrade unsealed rows: if a command was synced while still running
234    // (exit_status/end_unix_timestamp NULL), fill in the sealed values from
235    // the other database where available. Scans all of `other` regardless
236    // of watermark -- a seal can land at any id. Find candidates with a
237    // read-only join, then apply targeted updates in one short transaction.
238    let seal_updates: Vec<(i64, i64, Option<i64>)> = conn
239        .prepare(
240            r#"
241SELECT m.id, o.exit_status, o.end_unix_timestamp
242  FROM main.command_history m
243  JOIN other.command_history o
244    ON m.full_command = o.full_command
245   AND m.start_unix_timestamp IS o.start_unix_timestamp
246   AND m.shellname = o.shellname
247   AND COALESCE(m.hostname, '') = COALESCE(o.hostname, '')
248 WHERE m.exit_status IS NULL
249   AND o.exit_status IS NOT NULL
250"#,
251        )?
252        .query_map((), |r| Ok((r.get(0)?, r.get(1)?, r.get(2)?)))?
253        .collect::<rusqlite::Result<_>>()?;
254
255    if !seal_updates.is_empty() {
256        crate::with_write_retry(conn, WRITE_RETRY_BUDGET, |tx| {
257            for (id, exit_status, end_ts) in &seal_updates {
258                // Re-check exit_status IS NULL: a local seal may have
259                // landed since the read above.
260                tx.execute(
261                    "UPDATE command_history SET exit_status = ?, end_unix_timestamp = ?
262                      WHERE id = ? AND exit_status IS NULL",
263                    (exit_status, end_ts, id),
264                )?;
265            }
266            Ok(())
267        })?;
268    }
269
270    Ok(MergeStats { considered, added: added as i64, filtered: filtered_count, new_max_id })
271}
272
273#[cfg(test)]
274mod tests {
275    use std::path::PathBuf;
276
277    use super::*;
278
279    fn schema_db(path: &Path) -> Connection {
280        let conn = Connection::open(path).unwrap();
281        crate::initialize_base_schema(&conn).unwrap();
282        crate::run_schema_migrations(&conn).unwrap();
283        conn
284    }
285
286    fn insert_row(conn: &Connection, cmd: &str, ts: i64, exit_status: Option<i32>) {
287        conn.execute(
288            "INSERT INTO command_history (session_id, full_command, shellname, hostname,
289                                          start_unix_timestamp, exit_status)
290             VALUES (1, CAST(? AS blob), 'zsh', CAST('host1' AS blob), ?, ?)",
291            rusqlite::params![cmd, ts, exit_status],
292        )
293        .unwrap();
294    }
295
296    fn count(conn: &Connection) -> i64 {
297        conn.query_row("SELECT COUNT(*) FROM command_history", (), |r| r.get(0)).unwrap()
298    }
299
300    /// A target DB plus a source DB file, both schema-initialized.
301    fn merge_fixture() -> (tempfile::TempDir, Connection, PathBuf, Connection) {
302        let dir = tempfile::tempdir().unwrap();
303        let target_path = dir.path().join("target.db");
304        let source_path = dir.path().join("source.db");
305        let target = schema_db(&target_path);
306        let source = schema_db(&source_path);
307        (dir, target, source_path, source)
308    }
309
310    #[test]
311    fn test_merge_reports_stats_and_is_idempotent() {
312        let (_dir, mut target, source_path, source) = merge_fixture();
313        for i in 0..3 {
314            insert_row(&source, &format!("cmd-{i}"), 1000 + i, Some(0));
315        }
316        drop(source);
317
318        let stats = merge_database_from_file(&mut target, &source_path, None, None).unwrap();
319        assert_eq!(stats, MergeStats { considered: 3, added: 3, filtered: 0, new_max_id: Some(3) });
320        assert_eq!(count(&target), 3);
321
322        // INSERT OR IGNORE makes a re-merge a no-op.
323        let stats = merge_database_from_file(&mut target, &source_path, None, None).unwrap();
324        assert_eq!(stats, MergeStats { considered: 3, added: 0, filtered: 0, new_max_id: Some(3) });
325        assert_eq!(count(&target), 3);
326    }
327
328    #[test]
329    fn test_merge_crosses_chunk_boundaries() {
330        // More source rows than one chunk: the id-ordered chunk loop must
331        // walk every chunk, not just the first.
332        let (_dir, mut target, source_path, source) = merge_fixture();
333        let total = CHUNK_SIZE + 2;
334        {
335            let tx = source.unchecked_transaction().unwrap();
336            for i in 0..total {
337                tx.execute(
338                    "INSERT INTO command_history (session_id, full_command, shellname,
339                                                  start_unix_timestamp)
340                     VALUES (1, CAST(? AS blob), 'zsh', ?)",
341                    rusqlite::params![format!("cmd-{i}"), 1000 + i],
342                )
343                .unwrap();
344            }
345            tx.commit().unwrap();
346        }
347        drop(source);
348
349        let stats = merge_database_from_file(&mut target, &source_path, None, None).unwrap();
350        assert_eq!(stats.considered, total);
351        assert_eq!(stats.added, total);
352        assert_eq!(stats.new_max_id, Some(total));
353        assert_eq!(count(&target), total);
354    }
355
356    #[test]
357    fn test_merge_watermark_skips_already_merged_ids() {
358        let (_dir, mut target, source_path, source) = merge_fixture();
359        for i in 0..5 {
360            insert_row(&source, &format!("cmd-{i}"), 1000 + i, Some(0));
361        }
362        drop(source);
363
364        let stats = merge_database_from_file(&mut target, &source_path, None, Some(2)).unwrap();
365        assert_eq!(stats, MergeStats { considered: 3, added: 3, filtered: 0, new_max_id: Some(5) });
366        // Only rows with id > 2 (cmd-2 .. cmd-4) came over.
367        assert_eq!(count(&target), 3);
368        let has_cmd0: i64 = target
369            .query_row(
370                "SELECT COUNT(*) FROM command_history WHERE full_command = CAST('cmd-0' AS blob)",
371                (),
372                |r| r.get(0),
373            )
374            .unwrap();
375        assert_eq!(has_cmd0, 0, "row below the watermark must be skipped");
376    }
377
378    #[test]
379    fn test_merge_secret_filter_drops_and_counts() {
380        let (_dir, mut target, source_path, source) = merge_fixture();
381        insert_row(&source, "export API_KEY=hunter2", 1000, Some(0));
382        insert_row(&source, "ls -la", 1001, Some(0));
383        drop(source);
384
385        let filter = RegexSet::new(["API_KEY="]).unwrap();
386        let stats =
387            merge_database_from_file(&mut target, &source_path, Some(&filter), None).unwrap();
388        assert_eq!(stats, MergeStats { considered: 2, added: 1, filtered: 1, new_max_id: Some(2) });
389        assert_eq!(count(&target), 1);
390    }
391
392    #[test]
393    fn test_merge_upgrades_unsealed_rows() {
394        // A row synced while still running (no exit status) gets its sealed
395        // values filled in from a source that has them -- even when the row
396        // itself is below the watermark.
397        let (_dir, mut target, source_path, source) = merge_fixture();
398        insert_row(&target, "long-running", 1000, None);
399        insert_row(&source, "long-running", 1000, Some(7));
400        drop(source);
401
402        let stats = merge_database_from_file(&mut target, &source_path, None, Some(1000)).unwrap();
403        assert_eq!(stats.added, 0, "watermark excludes the row from insertion");
404
405        let exit_status: Option<i32> = target
406            .query_row(
407                "SELECT exit_status FROM command_history WHERE full_command = CAST('long-running' AS blob)",
408                (),
409                |r| r.get(0),
410            )
411            .unwrap();
412        assert_eq!(exit_status, Some(7), "seal info must be copied onto the unsealed row");
413    }
414
415    #[test]
416    fn test_merge_reports_max_id_regression_via_stats() {
417        // A source restored from backup can have max(id) below our watermark;
418        // the caller detects that from new_max_id and resets the watermark.
419        let (_dir, mut target, source_path, source) = merge_fixture();
420        insert_row(&source, "cmd", 1000, Some(0));
421        drop(source);
422
423        let stats = merge_database_from_file(&mut target, &source_path, None, Some(100)).unwrap();
424        assert_eq!(stats, MergeStats { considered: 0, added: 0, filtered: 0, new_max_id: Some(1) });
425    }
426
427    /// A source with the pre-v1 schema (no machine_id column, user_version 0),
428    /// as an older pxh would publish it.
429    fn old_schema_source(path: &Path, cmd: &str) {
430        let conn = Connection::open(path).unwrap();
431        conn.execute_batch(
432            "CREATE TABLE command_history (
433                id INTEGER PRIMARY KEY, session_id INTEGER NOT NULL,
434                full_command BLOB NOT NULL, shellname TEXT NOT NULL,
435                hostname BLOB, username BLOB, working_directory BLOB,
436                exit_status INTEGER, start_unix_timestamp INTEGER, end_unix_timestamp INTEGER);
437             CREATE TABLE settings (key TEXT PRIMARY KEY, value BLOB);",
438        )
439        .unwrap();
440        insert_row(&conn, cmd, 1000, Some(0));
441    }
442
443    #[test]
444    fn test_merge_never_writes_to_the_source() {
445        // Peers in a shared directory belong to other machines: merging must
446        // not migrate, stamp, or otherwise touch them -- even old-schema ones.
447        let (_dir, mut target, source_path, source) = merge_fixture();
448        drop(source);
449        std::fs::remove_file(&source_path).unwrap();
450        old_schema_source(&source_path, "from-old-peer");
451        let before = std::fs::read(&source_path).unwrap();
452
453        let stats = merge_database_from_file(&mut target, &source_path, None, None).unwrap();
454        assert_eq!(stats.added, 1);
455        assert_eq!(count(&target), 1);
456        assert_eq!(std::fs::read(&source_path).unwrap(), before, "source bytes changed");
457        let version: i32 = Connection::open(&source_path)
458            .unwrap()
459            .pragma_query_value(None, "user_version", |r| r.get(0))
460            .unwrap();
461        assert_eq!(version, 0, "source must not be migrated");
462    }
463
464    #[test]
465    fn test_merge_reads_a_read_only_source() {
466        use std::os::unix::fs::PermissionsExt;
467        let (_dir, mut target, source_path, source) = merge_fixture();
468        insert_row(&source, "cmd", 1000, Some(0));
469        drop(source);
470        std::fs::set_permissions(&source_path, std::fs::Permissions::from_mode(0o444)).unwrap();
471
472        let stats = merge_database_from_file(&mut target, &source_path, None, None).unwrap();
473        assert_eq!(stats.added, 1);
474    }
475
476    #[test]
477    fn test_merge_rejects_a_non_pxh_database_untouched() {
478        let (_dir, mut target, source_path, source) = merge_fixture();
479        drop(source);
480        std::fs::remove_file(&source_path).unwrap();
481        Connection::open(&source_path)
482            .unwrap()
483            .execute_batch("CREATE TABLE notes(body TEXT); INSERT INTO notes VALUES('x');")
484            .unwrap();
485        let before = std::fs::read(&source_path).unwrap();
486
487        let err = merge_database_from_file(&mut target, &source_path, None, None).unwrap_err();
488        assert!(err.to_string().contains("not a pxh database"), "{err}");
489        assert_eq!(count(&target), 0);
490        assert_eq!(std::fs::read(&source_path).unwrap(), before, "foreign file was modified");
491        // The failed attach must not leave `other` attached.
492        merge_database_from_file(&mut target, &source_path, None, None).unwrap_err();
493    }
494
495    #[test]
496    fn test_read_only_uri_percent_encodes_unsafe_bytes() {
497        use std::{ffi::OsStr, os::unix::ffi::OsStrExt};
498        let path = Path::new(OsStr::from_bytes(b"/tmp/a b?#%\xff.db"));
499        assert_eq!(read_only_uri(path), "file:/tmp/a%20b%3F%23%25%FF.db?mode=ro");
500    }
501
502    #[test]
503    fn test_watermark_roundtrip_and_missing() {
504        let dir = tempfile::tempdir().unwrap();
505        let conn = schema_db(&dir.path().join("db.db"));
506        assert_eq!(sync_watermark(&conn, 42), None);
507        set_sync_watermark(&conn, 42, 1234).unwrap();
508        assert_eq!(sync_watermark(&conn, 42), Some(1234));
509        set_sync_watermark(&conn, 42, 5678).unwrap();
510        assert_eq!(sync_watermark(&conn, 42), Some(5678));
511        assert_eq!(sync_watermark(&conn, 43), None, "keys are per machine_id");
512    }
513}