1use std::path::Path;
10use std::time::Duration;
11
12use anyhow::{Result, bail};
13use regex::bytes::RegexSet;
14use rusqlite::Connection;
15
16#[derive(Debug, Clone, Copy, PartialEq, Eq)]
18pub struct MergeStats {
19 pub considered: i64,
21 pub added: i64,
23 pub filtered: i64,
25 pub new_max_id: Option<i64>,
27}
28
29const WRITE_RETRY_BUDGET: Duration = Duration::from_secs(30);
32const CHUNK_SIZE: i64 = 5000;
33
34pub 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
43pub 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
54pub 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
75fn 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
92fn 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
99fn 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 let machine_id = if columns.iter().any(|c| c == "machine_id") { "machine_id" } else { "NULL" };
120
121 let lo = watermark.unwrap_or(-1);
123
124 let considered: i64 = conn
127 .prepare("SELECT COUNT(*) FROM other.command_history WHERE id > ?")?
128 .query_row([lo], |r| r.get(0))?;
129
130 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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}