null_analysis/
null_analysis.rs

1//! Example demonstrating how to use the query interface to analyze NULL values in Bear's database.
2//!
3//! This example shows how many notes have NULL titles, content, and IDs.
4
5use bear_query::BearDb;
6
7fn main() -> Result<(), bear_query::BearError> {
8  // Connect to Bear's database
9  let db = BearDb::new()?;
10
11  println!("=== Bear Database NULL Value Analysis ===\n");
12
13  // Query for notes with NULL titles
14  let null_titles_df = db.query("SELECT COUNT(*) as count FROM notes WHERE title IS NULL")?;
15  let null_titles_count = null_titles_df.column("count")?.i64()?.get(0).unwrap();
16
17  println!("Notes with NULL titles: {}", null_titles_count);
18
19  // Query for notes with NULL content
20  let null_content_df = db.query("SELECT COUNT(*) as count FROM notes WHERE content IS NULL")?;
21  let null_content_count = null_content_df.column("count")?.i64()?.get(0).unwrap();
22
23  println!("Notes with NULL content: {}", null_content_count);
24
25  // Query for notes with NULL id (UUID)
26  let null_id_df = db.query("SELECT COUNT(*) as count FROM notes WHERE id IS NULL")?;
27  let null_id_count = null_id_df.column("count")?.i64()?.get(0).unwrap();
28
29  println!("Notes with NULL id: {}", null_id_count);
30
31  // Get total note count for comparison
32  let total_df = db.query("SELECT COUNT(*) as count FROM notes")?;
33  let total_count = total_df.column("count")?.i64()?.get(0).unwrap();
34
35  println!("\nTotal notes in database: {}", total_count);
36
37  // Show percentages
38  if total_count > 0 {
39    println!("\n=== Percentages ===");
40    println!(
41      "NULL titles: {:.2}%",
42      (null_titles_count as f64 / total_count as f64) * 100.0
43    );
44    println!(
45      "NULL content: {:.2}%",
46      (null_content_count as f64 / total_count as f64) * 100.0
47    );
48    println!(
49      "NULL id: {:.2}%",
50      (null_id_count as f64 / total_count as f64) * 100.0
51    );
52  }
53
54  // Show some examples of notes with NULL titles (if any exist)
55  if null_titles_count > 0 {
56    println!("\n=== Sample Notes with NULL Titles ===");
57    let sample_df = db.query("SELECT id, content FROM notes WHERE title IS NULL LIMIT 5")?;
58
59    println!("{}", sample_df);
60  }
61
62  // Show some examples of notes with NULL content (if any exist)
63  if null_content_count > 0 {
64    println!("\n=== Sample Notes with NULL Content ===");
65    let sample_df = db.query("SELECT id, title FROM notes WHERE content IS NULL LIMIT 5")?;
66
67    println!("{}", sample_df);
68  }
69
70  // More detailed analysis: notes with multiple NULL fields
71  println!("\n=== Notes with Multiple NULL Fields ===");
72  let multiple_nulls_df = db.query(
73    r"
74        SELECT
75            COUNT(*) as count,
76            CASE
77                WHEN title IS NULL AND content IS NULL THEN 'Both title and content'
78                WHEN title IS NULL AND id IS NULL THEN 'Both title and id'
79                WHEN content IS NULL AND id IS NULL THEN 'Both content and id'
80                ELSE 'Other combination'
81            END as null_combination
82        FROM notes
83        WHERE (title IS NULL OR content IS NULL OR id IS NULL)
84        GROUP BY null_combination
85        ",
86  )?;
87
88  if multiple_nulls_df.height() > 0 {
89    println!("{}", multiple_nulls_df);
90  } else {
91    println!("No notes with multiple NULL fields found.");
92  }
93
94  // Analysis by note status (trashed, archived, etc.)
95  println!("\n=== NULL Values by Note Status ===");
96  let by_status_df = db.query(
97    r"
98        SELECT
99            CASE
100                WHEN is_trashed = 1 THEN 'Trashed'
101                WHEN is_archived = 1 THEN 'Archived'
102                ELSE 'Active'
103            END as status,
104            COUNT(*) as total_notes,
105            SUM(CASE WHEN title IS NULL THEN 1 ELSE 0 END) as null_titles,
106            SUM(CASE WHEN content IS NULL THEN 1 ELSE 0 END) as null_content,
107            SUM(CASE WHEN id IS NULL THEN 1 ELSE 0 END) as null_id
108        FROM notes
109        GROUP BY status
110        ORDER BY total_notes DESC
111        ",
112  )?;
113
114  println!("{}", by_status_df);
115
116  Ok(())
117}
null_analysis/null_analysis.rs

null_analysis/
null_analysis.rs
