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