1use std::path::PathBuf;
2
3use anyhow::{Result, anyhow, bail};
4use rusqlite::{Connection, OpenFlags, OptionalExtension};
5
6#[derive(Debug)]
7pub struct NoteRecord {
8 pub text: String,
9}
10
11#[derive(Debug, Clone, PartialEq, Eq)]
12pub struct NoteListItem {
13 pub identifier: String,
14 pub title: String,
15}
16
17#[derive(Debug, Clone, PartialEq, Eq)]
18pub struct SearchResult {
19 pub identifier: String,
20 pub title: String,
21 pub snippet: Option<String>,
22 pub modified_at: Option<i64>,
23 pub rank: u8,
24}
25
26#[derive(Debug, Clone, PartialEq, Eq)]
27pub struct ExportNote {
28 pub identifier: String,
29 pub title: String,
30 pub text: String,
31 pub pinned: bool,
32 pub created_at: Option<i64>,
33 pub modified_at: Option<i64>,
34 pub tags: Vec<String>,
35}
36
37#[derive(Debug, Clone, PartialEq, Eq)]
38pub struct DuplicateNote {
39 pub identifier: String,
40 pub modified_at: Option<String>,
41}
42
43#[derive(Debug, Clone, PartialEq, Eq)]
44pub struct DuplicateGroup {
45 pub title: String,
46 pub notes: Vec<DuplicateNote>,
47}
48
49#[derive(Debug, Clone, PartialEq, Eq)]
50pub struct StatsSummary {
51 pub total_notes: usize,
52 pub pinned_notes: usize,
53 pub tagged_notes: usize,
54 pub archived_notes: usize,
55 pub trashed_notes: usize,
56 pub unique_tags: usize,
57 pub total_words: usize,
58 pub notes_with_todos: usize,
59 pub oldest_modified: Option<i64>,
60 pub newest_modified: Option<i64>,
61 pub top_tags: Vec<(String, usize)>,
62}
63
64#[derive(Debug, Clone, PartialEq, Eq)]
65pub struct HealthNoteIssue {
66 pub identifier: String,
67 pub title: String,
68}
69
70#[derive(Debug, Clone, PartialEq, Eq)]
71pub struct LargeNoteIssue {
72 pub identifier: String,
73 pub title: String,
74 pub size_bytes: usize,
75}
76
77#[derive(Debug, Clone, PartialEq, Eq)]
78pub struct HealthSummary {
79 pub total_notes: usize,
80 pub duplicate_groups: usize,
81 pub duplicate_notes: usize,
82 pub empty_notes: Vec<HealthNoteIssue>,
83 pub untagged_notes: usize,
84 pub old_trashed_notes: Vec<HealthNoteIssue>,
85 pub large_notes: Vec<LargeNoteIssue>,
86 pub conflict_notes: Vec<HealthNoteIssue>,
87}
88
89#[derive(Debug, Clone, PartialEq, Eq)]
91pub struct NoteFile {
92 pub filename: String,
94 pub file_uuid: String,
97}
98
99pub struct BearDb {
100 connection: Connection,
101}
102
103impl BearDb {
104 pub fn open(path: PathBuf) -> Result<Self> {
105 let connection = Connection::open_with_flags(
106 &path,
107 OpenFlags::SQLITE_OPEN_READ_ONLY | OpenFlags::SQLITE_OPEN_NO_MUTEX,
108 )
109 .map_err(|err| anyhow!("failed to open Bear database at {}: {err}", path.display()))?;
110 Ok(Self { connection })
111 }
112
113 #[cfg(test)]
114 fn from_connection(connection: Connection) -> Self {
115 Self { connection }
116 }
117
118 pub fn find_note(
119 &self,
120 id: Option<&str>,
121 title: Option<&str>,
122 exclude_trashed: bool,
123 ) -> Result<NoteRecord> {
124 if id.is_none() && title.is_none() {
125 bail!("provide either --id or --title");
126 }
127
128 let sql = if id.is_some() {
129 if exclude_trashed {
130 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, '')
131 from ZSFNOTE
132 where ZUNIQUEIDENTIFIER = ?1 and ZTRASHED = 0
133 limit 1"
134 } else {
135 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, '')
136 from ZSFNOTE
137 where ZUNIQUEIDENTIFIER = ?1
138 limit 1"
139 }
140 } else if exclude_trashed {
141 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, '')
142 from ZSFNOTE
143 where ZTITLE = ?1 and ZTRASHED = 0
144 order by ZMODIFICATIONDATE desc
145 limit 1"
146 } else {
147 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, '')
148 from ZSFNOTE
149 where ZTITLE = ?1
150 order by ZMODIFICATIONDATE desc
151 limit 1"
152 };
153
154 let needle = id.or(title).unwrap_or_default();
155 self.connection
156 .query_row(sql, [needle], |row| Ok(NoteRecord { text: row.get(2)? }))
157 .optional()?
158 .ok_or_else(|| anyhow!("note not found"))
159 }
160
161 pub fn tags(&self) -> Result<Vec<String>> {
162 let mut stmt = self.connection.prepare(
163 "select ZTITLE
164 from ZSFNOTETAG
165 where ZTITLE is not null and ZENCRYPTED = 0
166 order by lower(ZTITLE) asc",
167 )?;
168 let rows = stmt.query_map([], |row| row.get(0))?;
169 rows.collect::<std::result::Result<Vec<_>, _>>()
170 .map_err(Into::into)
171 }
172
173 pub fn notes_for_tags(
174 &self,
175 tags: &[String],
176 include_trashed: bool,
177 ) -> Result<Vec<NoteListItem>> {
178 if tags.is_empty() {
179 bail!("at least one tag is required");
180 }
181
182 let placeholders = (0..tags.len()).map(|_| "?").collect::<Vec<_>>().join(", ");
183 let trashed_filter = if include_trashed {
184 ""
185 } else {
186 "and n.ZTRASHED = 0"
187 };
188 let sql = format!(
189 "select distinct n.ZUNIQUEIDENTIFIER, coalesce(n.ZTITLE, '')
190 from ZSFNOTE n
191 join Z_5TAGS nt on nt.Z_5NOTES = n.Z_PK
192 join ZSFNOTETAG t on t.Z_PK = nt.Z_13TAGS
193 where t.ZTITLE in ({placeholders})
194 and n.ZENCRYPTED = 0
195 and n.ZLOCKED = 0
196 and n.ZPERMANENTLYDELETED = 0
197 {trashed_filter}
198 order by lower(coalesce(n.ZTITLE, '')) asc"
199 );
200 let mut stmt = self.connection.prepare(&sql)?;
201 let rows = stmt.query_map(rusqlite::params_from_iter(tags.iter()), |row| {
202 Ok(NoteListItem {
203 identifier: row.get(0)?,
204 title: row.get(1)?,
205 })
206 })?;
207 rows.collect::<std::result::Result<Vec<_>, _>>()
208 .map_err(Into::into)
209 }
210
211 pub fn search(
212 &self,
213 term: Option<&str>,
214 tag: Option<&str>,
215 include_trashed: bool,
216 since: Option<i64>,
217 before: Option<i64>,
218 ) -> Result<Vec<SearchResult>> {
219 let term = term.unwrap_or_default().trim().to_lowercase();
220 let tag_filter = tag.map(str::trim).filter(|value| !value.is_empty());
221 let sql = if include_trashed {
222 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, ''), ZMODIFICATIONDATE
223 from ZSFNOTE
224 where ZENCRYPTED = 0
225 and ZLOCKED = 0
226 and ZPERMANENTLYDELETED = 0"
227 } else {
228 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, ''), ZMODIFICATIONDATE
229 from ZSFNOTE
230 where ZTRASHED = 0
231 and ZARCHIVED = 0
232 and ZENCRYPTED = 0
233 and ZLOCKED = 0
234 and ZPERMANENTLYDELETED = 0"
235 };
236 let mut stmt = self.connection.prepare(sql)?;
237 let rows = stmt.query_map([], |row| {
238 Ok((
239 row.get::<_, String>(0)?,
240 row.get::<_, String>(1)?,
241 row.get::<_, String>(2)?,
242 row.get::<_, Option<f64>>(3)?,
243 ))
244 })?;
245
246 let note_tags = self.note_tag_map()?;
247 let mut results = Vec::new();
248
249 for row in rows {
250 let (identifier, title, text, modified_at) = row?;
251 let modified_at = modified_at.map(|value| value as i64);
252
253 if let Some(since) = since {
254 if modified_at.is_some_and(|value| value < since) {
255 continue;
256 }
257 }
258 if let Some(before) = before {
259 if modified_at.is_some_and(|value| value >= before) {
260 continue;
261 }
262 }
263
264 let tags = note_tags.get(&identifier).cloned().unwrap_or_default();
265 if let Some(tag_filter) = tag_filter {
266 if !tags.iter().any(|candidate| candidate == tag_filter) {
267 continue;
268 }
269 }
270
271 let title_lower = title.to_lowercase();
272 let text_lower = text.to_lowercase();
273 let tag_match = !term.is_empty()
274 && tags
275 .iter()
276 .any(|candidate| candidate.to_lowercase().contains(&term));
277 let title_match = !term.is_empty() && title_lower.contains(&term);
278 let body_match = !term.is_empty() && text_lower.contains(&term);
279
280 if !term.is_empty() && !title_match && !tag_match && !body_match {
281 continue;
282 }
283
284 let rank = if title_match {
285 0
286 } else if tag_match {
287 1
288 } else {
289 2
290 };
291
292 results.push(SearchResult {
293 identifier,
294 title,
295 snippet: if body_match {
296 Some(make_snippet(&text, &text_lower, &term))
297 } else {
298 None
299 },
300 modified_at,
301 rank,
302 });
303 }
304
305 results.sort_by(|left, right| {
306 left.rank
307 .cmp(&right.rank)
308 .then_with(|| right.modified_at.cmp(&left.modified_at))
309 .then_with(|| left.title.to_lowercase().cmp(&right.title.to_lowercase()))
310 .then_with(|| left.identifier.cmp(&right.identifier))
311 });
312
313 Ok(results)
314 }
315
316 pub fn export_notes(&self, tag: Option<&str>) -> Result<Vec<ExportNote>> {
317 let sql = if tag.is_some() {
318 "select distinct n.ZUNIQUEIDENTIFIER, coalesce(n.ZTITLE, ''), coalesce(n.ZTEXT, ''), n.ZPINNED, n.ZCREATIONDATE, n.ZMODIFICATIONDATE
319 from ZSFNOTE n
320 join Z_5TAGS nt on nt.Z_5NOTES = n.Z_PK
321 join ZSFNOTETAG t on t.Z_PK = nt.Z_13TAGS
322 where t.ZTITLE = ?1
323 and n.ZTRASHED = 0
324 and n.ZENCRYPTED = 0
325 and n.ZLOCKED = 0
326 and n.ZPERMANENTLYDELETED = 0
327 order by lower(coalesce(n.ZTITLE, '')) asc"
328 } else {
329 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, ''), ZPINNED, ZCREATIONDATE, ZMODIFICATIONDATE
330 from ZSFNOTE
331 where ZTRASHED = 0
332 and ZENCRYPTED = 0
333 and ZLOCKED = 0
334 and ZPERMANENTLYDELETED = 0
335 order by lower(coalesce(ZTITLE, '')) asc"
336 };
337
338 let note_tags = self.note_tag_map()?;
339 let mut notes = Vec::new();
340
341 if let Some(tag) = tag {
342 let mut stmt = self.connection.prepare(sql)?;
343 let rows = stmt.query_map([tag], |row| {
344 Ok((
345 row.get::<_, String>(0)?,
346 row.get::<_, String>(1)?,
347 row.get::<_, String>(2)?,
348 row.get::<_, i64>(3)?,
349 row.get::<_, Option<f64>>(4)?,
350 row.get::<_, Option<f64>>(5)?,
351 ))
352 })?;
353
354 for row in rows {
355 let (identifier, title, text, pinned, created_at, modified_at) = row?;
356 notes.push(ExportNote {
357 tags: note_tags.get(&identifier).cloned().unwrap_or_default(),
358 identifier,
359 title,
360 text,
361 pinned: pinned == 1,
362 created_at: created_at.map(|value| value as i64),
363 modified_at: modified_at.map(|value| value as i64),
364 });
365 }
366 } else {
367 let mut stmt = self.connection.prepare(sql)?;
368 let rows = stmt.query_map([], |row| {
369 Ok((
370 row.get::<_, String>(0)?,
371 row.get::<_, String>(1)?,
372 row.get::<_, String>(2)?,
373 row.get::<_, i64>(3)?,
374 row.get::<_, Option<f64>>(4)?,
375 row.get::<_, Option<f64>>(5)?,
376 ))
377 })?;
378
379 for row in rows {
380 let (identifier, title, text, pinned, created_at, modified_at) = row?;
381 notes.push(ExportNote {
382 tags: note_tags.get(&identifier).cloned().unwrap_or_default(),
383 identifier,
384 title,
385 text,
386 pinned: pinned == 1,
387 created_at: created_at.map(|value| value as i64),
388 modified_at: modified_at.map(|value| value as i64),
389 });
390 }
391 }
392
393 Ok(notes)
394 }
395
396 pub fn duplicate_titles(&self) -> Result<Vec<DuplicateGroup>> {
397 let mut stmt = self.connection.prepare(
398 "select coalesce(ZTITLE, ''), ZUNIQUEIDENTIFIER, ZMODIFICATIONDATE
399 from ZSFNOTE
400 where ZTRASHED = 0
401 and ZPERMANENTLYDELETED = 0
402 and trim(coalesce(ZTITLE, '')) <> ''
403 order by lower(trim(coalesce(ZTITLE, ''))) asc, ZMODIFICATIONDATE desc",
404 )?;
405
406 let rows = stmt.query_map([], |row| {
407 Ok((
408 row.get::<_, String>(0)?,
409 row.get::<_, String>(1)?,
410 row.get::<_, Option<f64>>(2)?,
411 ))
412 })?;
413
414 let mut groups = std::collections::BTreeMap::<String, Vec<DuplicateNote>>::new();
415
416 for row in rows {
417 let (title, identifier, modified_at) = row?;
418 let trimmed_title = title.trim().to_string();
419 groups
420 .entry(trimmed_title)
421 .or_default()
422 .push(DuplicateNote {
423 identifier,
424 modified_at: modified_at.map(|value| value.to_string()),
425 });
426 }
427
428 Ok(groups
429 .into_iter()
430 .filter_map(|(title, notes)| {
431 if notes.len() > 1 {
432 Some(DuplicateGroup { title, notes })
433 } else {
434 None
435 }
436 })
437 .collect())
438 }
439
440 pub fn stats_summary(&self) -> Result<StatsSummary> {
441 let mut stmt = self.connection.prepare(
442 "select coalesce(ZTITLE, ''), coalesce(ZTEXT, ''), ZTRASHED, ZARCHIVED, ZPINNED, ZMODIFICATIONDATE
443 from ZSFNOTE
444 where ZPERMANENTLYDELETED = 0",
445 )?;
446
447 let rows = stmt.query_map([], |row| {
448 Ok((
449 row.get::<_, String>(0)?,
450 row.get::<_, String>(1)?,
451 row.get::<_, i64>(2)?,
452 row.get::<_, i64>(3)?,
453 row.get::<_, i64>(4)?,
454 row.get::<_, Option<f64>>(5)?,
455 ))
456 })?;
457
458 let tags = self.tags()?;
459 let mut total_notes = 0usize;
460 let mut pinned_notes = 0usize;
461 let mut tagged_notes = 0usize;
462 let mut archived_notes = 0usize;
463 let mut trashed_notes = 0usize;
464 let mut total_words = 0usize;
465 let mut notes_with_todos = 0usize;
466 let mut oldest_modified: Option<i64> = None;
467 let mut newest_modified: Option<i64> = None;
468
469 let mut tag_counts = std::collections::BTreeMap::<String, usize>::new();
470
471 let note_tags = self.note_tag_map()?;
472
473 for row in rows {
474 let (_title, text, trashed, archived, pinned, modified_at) = row?;
475
476 if trashed == 1 {
477 trashed_notes += 1;
478 continue;
479 }
480
481 total_notes += 1;
482 if pinned == 1 {
483 pinned_notes += 1;
484 }
485 if archived == 1 {
486 archived_notes += 1;
487 }
488 if text.contains("- [ ]") {
489 notes_with_todos += 1;
490 }
491 total_words += text
492 .split_whitespace()
493 .filter(|part| !part.is_empty())
494 .count();
495
496 if let Some(modified_at) = modified_at.map(|value| value as i64) {
497 oldest_modified = Some(match oldest_modified {
498 Some(current) => current.min(modified_at),
499 None => modified_at,
500 });
501 newest_modified = Some(match newest_modified {
502 Some(current) => current.max(modified_at),
503 None => modified_at,
504 });
505 }
506 }
507
508 for (note_id, tags) in note_tags {
509 if self.is_trashed(¬e_id)? {
510 continue;
511 }
512 if !tags.is_empty() {
513 tagged_notes += 1;
514 }
515 for tag in tags {
516 *tag_counts.entry(tag).or_default() += 1;
517 }
518 }
519
520 let mut top_tags = tag_counts.into_iter().collect::<Vec<_>>();
521 top_tags.sort_by(|left, right| right.1.cmp(&left.1).then_with(|| left.0.cmp(&right.0)));
522 top_tags.truncate(10);
523
524 Ok(StatsSummary {
525 total_notes,
526 pinned_notes,
527 tagged_notes,
528 archived_notes,
529 trashed_notes,
530 unique_tags: tags.len(),
531 total_words,
532 notes_with_todos,
533 oldest_modified,
534 newest_modified,
535 top_tags,
536 })
537 }
538
539 pub fn health_summary(&self) -> Result<HealthSummary> {
540 const OLD_TRASH_THRESHOLD: i64 = 30;
541 const LARGE_NOTE_THRESHOLD_BYTES: usize = 100_000;
542
543 let mut stmt = self.connection.prepare(
544 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, ''), coalesce(ZTEXT, ''), ZTRASHED, ZARCHIVED, ZMODIFICATIONDATE
545 from ZSFNOTE
546 where ZPERMANENTLYDELETED = 0
547 and ZENCRYPTED = 0
548 and ZLOCKED = 0",
549 )?;
550
551 let rows = stmt.query_map([], |row| {
552 Ok((
553 row.get::<_, String>(0)?,
554 row.get::<_, String>(1)?,
555 row.get::<_, String>(2)?,
556 row.get::<_, i64>(3)?,
557 row.get::<_, i64>(4)?,
558 row.get::<_, Option<f64>>(5)?,
559 ))
560 })?;
561
562 let duplicate_groups = self.duplicate_titles()?;
563 let note_tags = self.note_tag_map()?;
564
565 let mut total_notes = 0usize;
566 let mut empty_notes = Vec::new();
567 let mut untagged_notes = 0usize;
568 let mut old_trashed_notes = Vec::new();
569 let mut large_notes = Vec::new();
570 let mut conflict_notes = Vec::new();
571 let mut max_modified = 0i64;
572
573 let mut rows_cache = Vec::new();
574 for row in rows {
575 let row = row?;
576 if let Some(modified_at) = row.5.map(|value| value as i64) {
577 max_modified = max_modified.max(modified_at);
578 }
579 rows_cache.push(row);
580 }
581
582 for (identifier, title, text, trashed, archived, modified_at) in rows_cache {
583 let display_title = if title.trim().is_empty() {
584 "(untitled)".to_string()
585 } else {
586 title.trim().to_string()
587 };
588
589 if trashed == 0 {
590 total_notes += 1;
591
592 if text.trim().is_empty() {
593 empty_notes.push(HealthNoteIssue {
594 identifier: identifier.clone(),
595 title: display_title.clone(),
596 });
597 }
598
599 if !note_tags
600 .get(&identifier)
601 .map(|tags| !tags.is_empty())
602 .unwrap_or(false)
603 {
604 untagged_notes += 1;
605 }
606
607 let size_bytes = text.len();
608 if size_bytes >= LARGE_NOTE_THRESHOLD_BYTES {
609 large_notes.push(LargeNoteIssue {
610 identifier: identifier.clone(),
611 title: display_title.clone(),
612 size_bytes,
613 });
614 }
615
616 let lower_title = display_title.to_lowercase();
617 if lower_title.contains("conflict") || lower_title.contains("copy") {
618 conflict_notes.push(HealthNoteIssue {
619 identifier,
620 title: display_title,
621 });
622 }
623 } else if archived == 0 {
624 let modified_at = modified_at.map(|value| value as i64).unwrap_or_default();
625 if max_modified.saturating_sub(modified_at) >= OLD_TRASH_THRESHOLD {
626 old_trashed_notes.push(HealthNoteIssue {
627 identifier,
628 title: display_title,
629 });
630 }
631 }
632 }
633
634 Ok(HealthSummary {
635 total_notes,
636 duplicate_groups: duplicate_groups.len(),
637 duplicate_notes: duplicate_groups.iter().map(|group| group.notes.len()).sum(),
638 empty_notes,
639 untagged_notes,
640 old_trashed_notes,
641 large_notes,
642 conflict_notes,
643 })
644 }
645
646 pub fn untagged(&self, search: Option<&str>) -> Result<Vec<NoteListItem>> {
647 let like = format!("%{}%", search.unwrap_or_default());
648 let mut stmt = self.connection.prepare(
649 "select n.ZUNIQUEIDENTIFIER, coalesce(n.ZTITLE, '')
650 from ZSFNOTE n
651 where n.ZTRASHED = 0
652 and n.ZARCHIVED = 0
653 and n.ZENCRYPTED = 0
654 and n.ZLOCKED = 0
655 and n.ZPERMANENTLYDELETED = 0
656 and not exists (
657 select 1
658 from Z_5TAGS nt
659 where nt.Z_5NOTES = n.Z_PK
660 )
661 and (coalesce(n.ZTITLE, '') like ?1 or coalesce(n.ZTEXT, '') like ?1)
662 order by lower(coalesce(n.ZTITLE, '')) asc",
663 )?;
664 let rows = stmt.query_map([like], |row| {
665 Ok(NoteListItem {
666 identifier: row.get(0)?,
667 title: row.get(1)?,
668 })
669 })?;
670 rows.collect::<std::result::Result<Vec<_>, _>>()
671 .map_err(Into::into)
672 }
673
674 pub fn todo(&self, search: Option<&str>) -> Result<Vec<NoteListItem>> {
675 self.simple_filtered_list("ZTODOINCOMPLETED > 0", search)
676 }
677
678 pub fn today(&self, search: Option<&str>) -> Result<Vec<NoteListItem>> {
679 self.simple_filtered_list("ZSHOWNINTODAYWIDGET > 0", search)
680 }
681
682 pub fn locked(&self, search: Option<&str>) -> Result<Vec<NoteListItem>> {
683 let like = format!("%{}%", search.unwrap_or_default());
684 let mut stmt = self.connection.prepare(
685 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, '')
686 from ZSFNOTE
687 where ZLOCKED > 0
688 and ZPERMANENTLYDELETED = 0
689 and (coalesce(ZTITLE, '') like ?1 or coalesce(ZTEXT, '') like ?1)
690 order by lower(coalesce(ZTITLE, '')) asc",
691 )?;
692 let rows = stmt.query_map([like], |row| {
693 Ok(NoteListItem {
694 identifier: row.get(0)?,
695 title: row.get(1)?,
696 })
697 })?;
698 rows.collect::<std::result::Result<Vec<_>, _>>()
699 .map_err(Into::into)
700 }
701
702 pub fn note_files(&self, note_id: &str) -> Result<Vec<NoteFile>> {
706 let mut stmt = self.connection.prepare(
707 "SELECT f.ZFILENAME, f.ZUNIQUEIDENTIFIER
708 FROM ZSFNOTEFILE f
709 JOIN ZSFNOTE n ON f.ZNOTE = n.Z_PK
710 WHERE n.ZUNIQUEIDENTIFIER = ?1
711 AND f.ZPERMANENTLYDELETED = 0",
712 )?;
713 let rows = stmt.query_map([note_id], |row| {
714 Ok(NoteFile {
715 filename: row.get(0)?,
716 file_uuid: row.get(1)?,
717 })
718 })?;
719 rows.collect::<std::result::Result<Vec<_>, _>>()
720 .map_err(Into::into)
721 }
722
723 fn simple_filtered_list(
724 &self,
725 predicate: &str,
726 search: Option<&str>,
727 ) -> Result<Vec<NoteListItem>> {
728 let like = format!("%{}%", search.unwrap_or_default());
729 let sql = format!(
730 "select ZUNIQUEIDENTIFIER, coalesce(ZTITLE, '')
731 from ZSFNOTE
732 where {predicate}
733 and ZTRASHED = 0
734 and ZARCHIVED = 0
735 and ZENCRYPTED = 0
736 and ZLOCKED = 0
737 and ZPERMANENTLYDELETED = 0
738 and (coalesce(ZTITLE, '') like ?1 or coalesce(ZTEXT, '') like ?1)
739 order by lower(coalesce(ZTITLE, '')) asc"
740 );
741 let mut stmt = self.connection.prepare(&sql)?;
742 let rows = stmt.query_map([like], |row| {
743 Ok(NoteListItem {
744 identifier: row.get(0)?,
745 title: row.get(1)?,
746 })
747 })?;
748 rows.collect::<std::result::Result<Vec<_>, _>>()
749 .map_err(Into::into)
750 }
751
752 fn note_tag_map(&self) -> Result<std::collections::BTreeMap<String, Vec<String>>> {
753 let mut stmt = self.connection.prepare(
754 "select n.ZUNIQUEIDENTIFIER, t.ZTITLE
755 from ZSFNOTE n
756 left join Z_5TAGS nt on nt.Z_5NOTES = n.Z_PK
757 left join ZSFNOTETAG t on t.Z_PK = nt.Z_13TAGS
758 where n.ZPERMANENTLYDELETED = 0",
759 )?;
760
761 let rows = stmt.query_map([], |row| {
762 Ok((row.get::<_, String>(0)?, row.get::<_, Option<String>>(1)?))
763 })?;
764
765 let mut map = std::collections::BTreeMap::<String, Vec<String>>::new();
766 for row in rows {
767 let (note_id, tag) = row?;
768 let entry = map.entry(note_id).or_default();
769 if let Some(tag) = tag {
770 entry.push(tag);
771 }
772 }
773 Ok(map)
774 }
775
776 fn is_trashed(&self, note_id: &str) -> Result<bool> {
777 let mut stmt = self
778 .connection
779 .prepare("select ZTRASHED from ZSFNOTE where ZUNIQUEIDENTIFIER = ?1 limit 1")?;
780 let trashed = stmt.query_row([note_id], |row| row.get::<_, i64>(0))?;
781 Ok(trashed == 1)
782 }
783}
784
785fn make_snippet(text: &str, text_lower: &str, term: &str) -> String {
786 let normalized = text.split_whitespace().collect::<Vec<_>>().join(" ");
787 let normalized_lower = text_lower.split_whitespace().collect::<Vec<_>>().join(" ");
788 let index = normalized_lower.find(term).unwrap_or(0);
789 let start = index.saturating_sub(30);
790 let end = (index + term.len() + 50).min(normalized.len());
791 normalized[start..end].trim().to_string()
792}
793
794#[cfg(test)]
795mod tests {
796 use rusqlite::Connection;
797
798 use super::{
799 BearDb, DuplicateGroup, DuplicateNote, HealthNoteIssue, HealthSummary, NoteListItem,
800 SearchResult, StatsSummary,
801 };
802
803 fn test_db() -> BearDb {
804 let connection = Connection::open_in_memory().expect("in-memory db");
805 connection
806 .execute_batch(
807 "
808 create table ZSFNOTE (
809 Z_PK integer primary key,
810 ZTRASHED integer,
811 ZARCHIVED integer,
812 ZPINNED integer,
813 ZENCRYPTED integer,
814 ZLOCKED integer,
815 ZPERMANENTLYDELETED integer,
816 ZTODOINCOMPLETED integer,
817 ZSHOWNINTODAYWIDGET integer,
818 ZCREATIONDATE integer,
819 ZMODIFICATIONDATE integer,
820 ZTITLE text,
821 ZTEXT text,
822 ZUNIQUEIDENTIFIER text
823 );
824 create table ZSFNOTETAG (
825 Z_PK integer primary key,
826 ZENCRYPTED integer,
827 ZTITLE text
828 );
829 create table Z_5TAGS (
830 Z_5NOTES integer,
831 Z_13TAGS integer
832 );
833 insert into ZSFNOTE values
834 (1, 0, 0, 1, 0, 0, 0, 1, 1, 5, 10, 'Alpha', 'alpha body - [ ]', 'NOTE-1'),
835 (2, 0, 1, 0, 0, 0, 0, 0, 0, 15, 20, 'Beta', 'beta body', 'NOTE-2'),
836 (3, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 'Trash', 'trashed', 'NOTE-3'),
837 (4, 0, 0, 0, 0, 0, 0, 0, 0, 35, 40, 'Alpha', 'another alpha with rust body', 'NOTE-4'),
838 (5, 0, 0, 0, 0, 0, 0, 0, 0, 45, 50, ' ', 'blank title', 'NOTE-5'),
839 (6, 0, 0, 0, 0, 0, 0, 0, 0, 55, 60, 'Conflict copy', '', 'NOTE-6'),
840 (7, 0, 0, 0, 0, 0, 0, 0, 0, 65, 70, 'Gamma', 'body mention with rust across
841lines', 'NOTE-7'),
842 (8, 0, 0, 0, 0, 0, 0, 0, 0, 75, 80, 'Rust title', 'no body hit here', 'NOTE-8');
843 insert into ZSFNOTETAG values
844 (10, 0, 'work'),
845 (11, 0, 'misc'),
846 (12, 0, 'rust');
847 insert into Z_5TAGS values
848 (1, 10),
849 (2, 10),
850 (3, 11),
851 (7, 12);
852 ",
853 )
854 .expect("schema should be created");
855
856 BearDb::from_connection(connection)
857 }
858
859 #[test]
860 fn finds_note_by_title() {
861 let db = test_db();
862 let note = db
863 .find_note(None, Some("Alpha"), false)
864 .expect("note should exist");
865 assert_eq!(note.text, "another alpha with rust body");
866 }
867
868 #[test]
869 fn searches_non_trashed_notes() {
870 let db = test_db();
871 let notes = db
872 .search(Some("alpha body"), None, false, None, None)
873 .expect("search should work");
874 assert_eq!(
875 notes,
876 vec![SearchResult {
877 identifier: "NOTE-1".into(),
878 title: "Alpha".into(),
879 snippet: Some("alpha body - [ ]".into()),
880 modified_at: Some(10),
881 rank: 2,
882 }]
883 );
884 }
885
886 #[test]
887 fn lists_notes_for_tag_without_trashed_entries() {
888 let db = test_db();
889 let notes = db
890 .notes_for_tags(&["work".into(), "misc".into()], false)
891 .expect("tag lookup should work");
892 assert_eq!(
893 notes,
894 vec![
895 NoteListItem {
896 identifier: "NOTE-1".into(),
897 title: "Alpha".into()
898 },
899 NoteListItem {
900 identifier: "NOTE-2".into(),
901 title: "Beta".into()
902 }
903 ]
904 );
905 }
906
907 #[test]
908 fn finds_duplicate_titles() {
909 let db = test_db();
910 let groups = db
911 .duplicate_titles()
912 .expect("duplicate detection should work");
913
914 assert_eq!(
915 groups,
916 vec![DuplicateGroup {
917 title: "Alpha".into(),
918 notes: vec![
919 DuplicateNote {
920 identifier: "NOTE-4".into(),
921 modified_at: Some("40".into()),
922 },
923 DuplicateNote {
924 identifier: "NOTE-1".into(),
925 modified_at: Some("10".into()),
926 },
927 ],
928 }]
929 );
930 }
931
932 #[test]
933 fn computes_stats_summary() {
934 let db = test_db();
935 let summary = db.stats_summary().expect("stats should compute");
936
937 assert_eq!(
938 summary,
939 StatsSummary {
940 total_notes: 7,
941 pinned_notes: 1,
942 tagged_notes: 3,
943 archived_notes: 1,
944 trashed_notes: 1,
945 unique_tags: 3,
946 total_words: 24,
947 notes_with_todos: 1,
948 oldest_modified: Some(10),
949 newest_modified: Some(80),
950 top_tags: vec![("work".into(), 2), ("rust".into(), 1)],
951 }
952 );
953 }
954
955 #[test]
956 fn computes_health_summary() {
957 let db = test_db();
958 let summary = db.health_summary().expect("health should compute");
959
960 assert_eq!(
961 summary,
962 HealthSummary {
963 total_notes: 7,
964 duplicate_groups: 1,
965 duplicate_notes: 2,
966 empty_notes: vec![HealthNoteIssue {
967 identifier: "NOTE-6".into(),
968 title: "Conflict copy".into(),
969 }],
970 untagged_notes: 4,
971 old_trashed_notes: vec![HealthNoteIssue {
972 identifier: "NOTE-3".into(),
973 title: "Trash".into(),
974 }],
975 large_notes: vec![],
976 conflict_notes: vec![HealthNoteIssue {
977 identifier: "NOTE-6".into(),
978 title: "Conflict copy".into(),
979 }],
980 }
981 );
982 }
983
984 #[test]
985 fn search_ranks_title_match_over_tag_and_body() {
986 let db = test_db();
987 let results = db
988 .search(Some("rust"), None, false, None, None)
989 .expect("search should work");
990
991 assert_eq!(
992 results
993 .iter()
994 .map(|result| result.identifier.as_str())
995 .collect::<Vec<_>>(),
996 vec!["NOTE-8", "NOTE-7", "NOTE-4"]
997 );
998 }
999
1000 #[test]
1001 fn search_applies_since_and_before_filters() {
1002 let db = test_db();
1003 let results = db
1004 .search(Some("rust"), None, false, Some(50), Some(80))
1005 .expect("search should work");
1006
1007 assert_eq!(
1008 results
1009 .iter()
1010 .map(|result| result.identifier.as_str())
1011 .collect::<Vec<_>>(),
1012 vec!["NOTE-7"]
1013 );
1014 }
1015
1016 #[test]
1017 fn search_normalizes_snippets() {
1018 let db = test_db();
1019 let result = db
1020 .search(Some("rust"), None, false, None, None)
1021 .expect("search should work")
1022 .into_iter()
1023 .find(|result| result.identifier == "NOTE-7")
1024 .expect("body match should exist");
1025
1026 assert_eq!(
1027 result.snippet,
1028 Some("body mention with rust across lines".into())
1029 );
1030 }
1031
1032 #[test]
1033 fn exports_notes_with_tags() {
1034 let db = test_db();
1035 let notes = db
1036 .export_notes(Some("rust"))
1037 .expect("export query should work");
1038
1039 assert_eq!(notes.len(), 1);
1040 assert_eq!(notes[0].identifier, "NOTE-7");
1041 assert_eq!(notes[0].tags, vec!["rust".to_string()]);
1042 assert_eq!(notes[0].created_at, Some(65));
1043 }
1044}