things3_core/
database.rs

1//! Database access layer for Things 3
2
3use crate::{
4    config::ThingsConfig,
5    error::Result,
6    models::{Area, Project, Task, TaskStatus, TaskType},
7};
8use chrono::{DateTime, NaiveDate, Utc};
9use rusqlite::Connection;
10use std::path::Path;
11use uuid::Uuid;
12
13/// Main database access struct
14pub struct ThingsDatabase {
15    conn: Connection,
16}
17
18impl ThingsDatabase {
19    /// Convert Things 3 type integer to `TaskType`
20    fn convert_task_type(type_value: i32) -> TaskType {
21        match type_value {
22            1 => TaskType::Project,
23            2 => TaskType::Heading,
24            3 => TaskType::Area, // Checklist items are treated as areas in our model
25            _ => TaskType::Todo,
26        }
27    }
28
29    /// Convert Things 3 status integer to `TaskStatus`
30    fn convert_task_status(status_value: i32) -> TaskStatus {
31        match status_value {
32            1 => TaskStatus::Completed,
33            2 => TaskStatus::Canceled,
34            3 => TaskStatus::Trashed,
35            _ => TaskStatus::Incomplete,
36        }
37    }
38
39    /// Convert Things 3 timestamp (REAL) to `DateTime<Utc>`
40    fn convert_timestamp(timestamp: Option<f64>) -> DateTime<Utc> {
41        timestamp.map_or_else(Utc::now, |ts| {
42            #[allow(clippy::cast_possible_truncation)]
43            {
44                DateTime::from_timestamp(ts as i64, 0).unwrap_or_else(Utc::now)
45            }
46        })
47    }
48
49    /// Convert Things 3 date (INTEGER) to `NaiveDate`
50    fn convert_date(date_value: Option<i64>) -> Option<NaiveDate> {
51        date_value.and_then(|d| {
52            // Things 3 stores dates as days since 2001-01-01
53            let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1)?;
54            #[allow(clippy::cast_sign_loss)]
55            {
56                base_date.checked_add_days(chrono::Days::new(d as u64))
57            }
58        })
59    }
60
61    /// Convert Things 3 UUID string to Uuid, handling None case
62    /// Things 3 uses a custom base64-like format, so we'll generate a UUID from the string
63    fn convert_uuid(uuid_str: Option<String>) -> Option<Uuid> {
64        uuid_str.map(|s| {
65            // Try to parse as standard UUID first
66            if let Ok(uuid) = Uuid::parse_str(&s) {
67                uuid
68            } else {
69                // For Things 3 format, generate a deterministic UUID from the string
70                use std::collections::hash_map::DefaultHasher;
71                use std::hash::{Hash, Hasher};
72                let mut hasher = DefaultHasher::new();
73                s.hash(&mut hasher);
74                let hash = hasher.finish();
75                // Create a UUID from the hash
76                Uuid::from_u128(u128::from(hash))
77            }
78        })
79    }
80    /// Create a new database connection
81    ///
82    /// # Errors
83    /// Returns `ThingsError::Database` if the database cannot be opened
84    pub fn new<P: AsRef<Path>>(db_path: P) -> Result<Self> {
85        let conn = Connection::open(db_path)?;
86        Ok(Self { conn })
87    }
88
89    /// Create a new database connection using configuration
90    ///
91    /// # Errors
92    /// Returns `ThingsError::Database` if the database cannot be opened
93    /// Returns `ThingsError::Message` if the database path is not found and fallback fails
94    pub fn with_config(config: &ThingsConfig) -> Result<Self> {
95        let db_path = config.get_effective_database_path()?;
96        Self::new(db_path)
97    }
98
99    /// Get the default Things 3 database path
100    #[must_use]
101    pub fn default_path() -> String {
102        format!(
103            "{}/Library/Group Containers/JLMPQHK86H.com.culturedcode.ThingsMac/ThingsData-0Z0Z2/Things Database.thingsdatabase/main.sqlite",
104            std::env::var("HOME").unwrap_or_else(|_| "~".to_string())
105        )
106    }
107
108    /// Create with default database path
109    ///
110    /// # Errors
111    /// Returns `ThingsError::Database` if the database cannot be opened
112    pub fn with_default_path() -> Result<Self> {
113        Self::new(Self::default_path())
114    }
115
116    /// Get tasks from inbox
117    ///
118    /// # Errors
119    /// Returns `ThingsError::Database` if the database query fails
120    ///
121    /// # Panics
122    /// Panics if UUID parsing fails (should not happen with valid database)
123    pub fn get_inbox(&self, limit: Option<usize>) -> Result<Vec<Task>> {
124        let mut stmt = self.conn.prepare(
125            "SELECT uuid, title, type, status, notes, startDate, deadline, creationDate, userModificationDate, project, area, heading 
126             FROM TMTask 
127             WHERE status = 0 AND project IS NULL AND area IS NULL 
128             ORDER BY creationDate DESC"
129        )?;
130
131        let rows = stmt.query_map([], |row| {
132            Ok(Task {
133                uuid: Self::convert_uuid(Some(row.get("uuid")?)).unwrap_or_else(Uuid::new_v4),
134                title: row.get("title")?,
135                task_type: Self::convert_task_type(row.get("type")?),
136                status: Self::convert_task_status(row.get("status")?),
137                notes: row.get("notes")?,
138                start_date: Self::convert_date(row.get("startDate")?),
139                deadline: Self::convert_date(row.get("deadline")?),
140                created: Self::convert_timestamp(row.get("creationDate")?),
141                modified: Self::convert_timestamp(row.get("userModificationDate")?),
142                project_uuid: Self::convert_uuid(row.get("project")?),
143                area_uuid: Self::convert_uuid(row.get("area")?),
144                parent_uuid: Self::convert_uuid(row.get("heading")?),
145                tags: vec![],     // TODO: Load tags separately
146                children: vec![], // TODO: Load children separately
147            })
148        })?;
149
150        let mut tasks: Vec<Task> = rows.collect::<std::result::Result<Vec<_>, _>>()?;
151
152        if let Some(limit) = limit {
153            tasks.truncate(limit);
154        }
155
156        Ok(tasks)
157    }
158
159    /// Get today's tasks
160    ///
161    /// # Errors
162    /// Returns `ThingsError::Database` if the database query fails
163    ///
164    /// # Panics
165    /// Panics if UUID parsing fails (should not happen with valid database)
166    pub fn get_today(&self, limit: Option<usize>) -> Result<Vec<Task>> {
167        let today = chrono::Utc::now().date_naive();
168        // Convert today to days since 2001-01-01 (Things 3 format)
169        let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap();
170        let days_since_2001 = today.signed_duration_since(base_date).num_days();
171
172        let mut stmt = self.conn.prepare(
173            "SELECT uuid, title, type, status, notes, startDate, deadline, creationDate, userModificationDate, project, area, heading 
174             FROM TMTask 
175             WHERE status = 0 AND startDate = ? 
176             ORDER BY creationDate DESC"
177        )?;
178
179        let rows = stmt.query_map([days_since_2001], |row| {
180            Ok(Task {
181                uuid: Uuid::parse_str(&row.get::<_, String>("uuid")?)
182                    .unwrap_or_else(|_| Uuid::new_v4()),
183                title: row.get("title")?,
184                task_type: match row.get::<_, i32>("type")? {
185                    1 => TaskType::Project,
186                    2 => TaskType::Heading,
187                    3 => TaskType::Area,
188                    _ => TaskType::Todo,
189                },
190                status: match row.get::<_, i32>("status")? {
191                    1 => TaskStatus::Completed,
192                    2 => TaskStatus::Canceled,
193                    3 => TaskStatus::Trashed,
194                    _ => TaskStatus::Incomplete,
195                },
196                notes: row.get("notes")?,
197                start_date: row.get::<_, Option<i32>>("startDate")?.and_then(|days| {
198                    // Convert from days since 2001-01-01 to NaiveDate
199                    let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap();
200                    base_date.checked_add_days(chrono::Days::new(days as u64))
201                }),
202                deadline: row.get::<_, Option<i32>>("deadline")?.and_then(|days| {
203                    // Convert from days since 2001-01-01 to NaiveDate
204                    let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap();
205                    base_date.checked_add_days(chrono::Days::new(days as u64))
206                }),
207                created: {
208                    let timestamp = row.get::<_, f64>("creationDate")?;
209                    // Convert from Core Data timestamp (seconds since 2001-01-01) to DateTime<Utc>
210                    let base_date = chrono::DateTime::parse_from_rfc3339("2001-01-01T00:00:00Z")
211                        .unwrap()
212                        .with_timezone(&chrono::Utc);
213                    base_date + chrono::Duration::seconds(timestamp as i64)
214                },
215                modified: {
216                    let timestamp = row.get::<_, f64>("userModificationDate")?;
217                    // Convert from Core Data timestamp (seconds since 2001-01-01) to DateTime<Utc>
218                    let base_date = chrono::DateTime::parse_from_rfc3339("2001-01-01T00:00:00Z")
219                        .unwrap()
220                        .with_timezone(&chrono::Utc);
221                    base_date + chrono::Duration::seconds(timestamp as i64)
222                },
223                project_uuid: row
224                    .get::<_, Option<String>>("project")?
225                    .and_then(|s| Uuid::parse_str(&s).ok()),
226                area_uuid: row
227                    .get::<_, Option<String>>("area")?
228                    .and_then(|s| Uuid::parse_str(&s).ok()),
229                parent_uuid: row
230                    .get::<_, Option<String>>("heading")?
231                    .and_then(|s| Uuid::parse_str(&s).ok()),
232                tags: vec![],     // TODO: Load tags separately
233                children: vec![], // TODO: Load children separately
234            })
235        })?;
236
237        let mut tasks: Vec<Task> = rows.collect::<std::result::Result<Vec<_>, _>>()?;
238
239        if let Some(limit) = limit {
240            tasks.truncate(limit);
241        }
242
243        Ok(tasks)
244    }
245
246    /// Get all projects
247    ///
248    /// # Errors
249    /// Returns `ThingsError::Database` if the database query fails
250    pub fn get_projects(&self, area_uuid: Option<Uuid>) -> Result<Vec<Project>> {
251        let query = if area_uuid.is_some() {
252            "SELECT uuid, title, notes, startDate, deadline, creationDate, userModificationDate, area, status 
253             FROM TMTask 
254             WHERE type = 1 AND area = ? 
255             ORDER BY creationDate DESC"
256        } else {
257            "SELECT uuid, title, notes, startDate, deadline, creationDate, userModificationDate, area, status 
258             FROM TMTask 
259             WHERE type = 1 
260             ORDER BY creationDate DESC"
261        };
262
263        let mut stmt = self.conn.prepare(query)?;
264        let rows = if let Some(area_uuid) = area_uuid {
265            stmt.query_map([area_uuid.to_string()], Self::map_project_row)?
266        } else {
267            stmt.query_map([], Self::map_project_row)?
268        };
269
270        let projects: Vec<Project> = rows.collect::<std::result::Result<Vec<_>, _>>()?;
271        Ok(projects)
272    }
273
274    /// Get all areas
275    ///
276    /// # Errors
277    /// Returns `ThingsError::Database` if the database query fails
278    ///
279    /// # Panics
280    /// Panics if UUID parsing fails (should not happen with valid database)
281    pub fn get_areas(&self) -> Result<Vec<Area>> {
282        let mut stmt = self.conn.prepare(
283            "SELECT uuid, title, visible, \"index\" 
284             FROM TMArea 
285             WHERE visible IS NULL OR visible = 1 
286             ORDER BY \"index\"",
287        )?;
288
289        let rows = stmt.query_map([], |row| {
290            Ok(Area {
291                uuid: Uuid::parse_str(&row.get::<_, String>("uuid")?)
292                    .unwrap_or_else(|_| Uuid::new_v4()),
293                title: row.get("title")?,
294                notes: None,                  // TMArea doesn't have notes field
295                created: chrono::Utc::now(),  // TMArea doesn't track creation date
296                modified: chrono::Utc::now(), // TMArea doesn't track modification date
297                tags: vec![],                 // TODO: Load tags separately
298                projects: vec![],             // TODO: Load projects separately
299            })
300        })?;
301
302        let areas: Vec<Area> = rows.collect::<std::result::Result<Vec<_>, _>>()?;
303        Ok(areas)
304    }
305
306    /// Search tasks
307    ///
308    /// # Errors
309    /// Returns `ThingsError::Database` if the database query fails
310    ///
311    /// # Panics
312    /// Panics if UUID parsing fails (should not happen with valid database)
313    pub fn search_tasks(&self, query: &str, limit: Option<usize>) -> Result<Vec<Task>> {
314        let search_pattern = format!("%{query}%");
315        let mut stmt = self.conn.prepare(
316            "SELECT uuid, title, type, status, notes, startDate, deadline, creationDate, userModificationDate, project, area, heading 
317             FROM TMTask 
318             WHERE (title LIKE ? OR notes LIKE ?) AND status = 0
319             ORDER BY creationDate DESC"
320        )?;
321
322        let rows = stmt.query_map([&search_pattern, &search_pattern], |row| {
323            let uuid_str = row.get::<_, String>("uuid")?;
324            let uuid = Uuid::parse_str(&uuid_str).unwrap_or_else(|_| {
325                // Generate a new UUID if parsing fails
326                Uuid::new_v4()
327            });
328            Ok(Task {
329                uuid,
330                title: row.get("title")?,
331                task_type: match row.get::<_, i32>("type")? {
332                    1 => TaskType::Project,
333                    2 => TaskType::Heading,
334                    3 => TaskType::Area,
335                    _ => TaskType::Todo,
336                },
337                status: match row.get::<_, i32>("status")? {
338                    1 => TaskStatus::Completed,
339                    2 => TaskStatus::Canceled,
340                    3 => TaskStatus::Trashed,
341                    _ => TaskStatus::Incomplete,
342                },
343                notes: row.get("notes")?,
344                start_date: row.get::<_, Option<i32>>("startDate")?.and_then(|days| {
345                    // Convert from days since 2001-01-01 to NaiveDate
346                    let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap();
347                    base_date.checked_add_days(chrono::Days::new(days as u64))
348                }),
349                deadline: row.get::<_, Option<i32>>("deadline")?.and_then(|days| {
350                    // Convert from days since 2001-01-01 to NaiveDate
351                    let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap();
352                    base_date.checked_add_days(chrono::Days::new(days as u64))
353                }),
354                created: {
355                    let timestamp = row.get::<_, f64>("creationDate")?;
356                    // Convert from Core Data timestamp (seconds since 2001-01-01) to DateTime<Utc>
357                    let base_date = chrono::DateTime::parse_from_rfc3339("2001-01-01T00:00:00Z")
358                        .unwrap()
359                        .with_timezone(&chrono::Utc);
360                    base_date + chrono::Duration::seconds(timestamp as i64)
361                },
362                modified: {
363                    let timestamp = row.get::<_, f64>("userModificationDate")?;
364                    // Convert from Core Data timestamp (seconds since 2001-01-01) to DateTime<Utc>
365                    let base_date = chrono::DateTime::parse_from_rfc3339("2001-01-01T00:00:00Z")
366                        .unwrap()
367                        .with_timezone(&chrono::Utc);
368                    base_date + chrono::Duration::seconds(timestamp as i64)
369                },
370                project_uuid: row
371                    .get::<_, Option<String>>("project")?
372                    .and_then(|s| Uuid::parse_str(&s).ok()),
373                area_uuid: row
374                    .get::<_, Option<String>>("area")?
375                    .and_then(|s| Uuid::parse_str(&s).ok()),
376                parent_uuid: row
377                    .get::<_, Option<String>>("heading")?
378                    .and_then(|s| Uuid::parse_str(&s).ok()),
379                tags: vec![],     // TODO: Load tags separately
380                children: vec![], // TODO: Load children separately
381            })
382        })?;
383
384        let mut tasks: Vec<Task> = rows.collect::<std::result::Result<Vec<_>, _>>()?;
385
386        if let Some(limit) = limit {
387            tasks.truncate(limit);
388        }
389
390        Ok(tasks)
391    }
392
393    /// Helper method to map a database row to a Project
394    fn map_project_row(row: &rusqlite::Row) -> rusqlite::Result<Project> {
395        Ok(Project {
396            uuid: Uuid::parse_str(&row.get::<_, String>("uuid")?)
397                .unwrap_or_else(|_| Uuid::new_v4()),
398            title: row.get("title")?,
399            notes: row.get("notes")?,
400            start_date: row.get::<_, Option<i32>>("startDate")?.and_then(|days| {
401                // Convert from days since 2001-01-01 to NaiveDate
402                let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap();
403                base_date.checked_add_days(chrono::Days::new(days as u64))
404            }),
405            deadline: row.get::<_, Option<i32>>("deadline")?.and_then(|days| {
406                // Convert from days since 2001-01-01 to NaiveDate
407                let base_date = chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap();
408                base_date.checked_add_days(chrono::Days::new(days as u64))
409            }),
410            created: {
411                let timestamp = row.get::<_, f64>("creationDate")?;
412                // Convert from Core Data timestamp (seconds since 2001-01-01) to DateTime<Utc>
413                let base_date = chrono::DateTime::parse_from_rfc3339("2001-01-01T00:00:00Z")
414                    .unwrap()
415                    .with_timezone(&chrono::Utc);
416                base_date + chrono::Duration::seconds(timestamp as i64)
417            },
418            modified: {
419                let timestamp = row.get::<_, f64>("userModificationDate")?;
420                // Convert from Core Data timestamp (seconds since 2001-01-01) to DateTime<Utc>
421                let base_date = chrono::DateTime::parse_from_rfc3339("2001-01-01T00:00:00Z")
422                    .unwrap()
423                    .with_timezone(&chrono::Utc);
424                base_date + chrono::Duration::seconds(timestamp as i64)
425            },
426            area_uuid: row
427                .get::<_, Option<String>>("area")?
428                .and_then(|s| Uuid::parse_str(&s).ok()),
429            tags: vec![], // TODO: Load tags separately
430            status: match row.get::<_, i32>("status")? {
431                1 => TaskStatus::Completed,
432                2 => TaskStatus::Canceled,
433                3 => TaskStatus::Trashed,
434                _ => TaskStatus::Incomplete,
435            },
436            tasks: vec![], // TODO: Load tasks separately
437        })
438    }
439}
440
441#[cfg(test)]
442mod tests {
443    use super::*;
444    use crate::test_utils::create_test_database;
445    use tempfile::NamedTempFile;
446
447    /// Test convert_task_type with all possible values
448    #[test]
449    fn test_convert_task_type() {
450        assert_eq!(ThingsDatabase::convert_task_type(1), TaskType::Project);
451        assert_eq!(ThingsDatabase::convert_task_type(2), TaskType::Heading);
452        assert_eq!(ThingsDatabase::convert_task_type(3), TaskType::Area);
453        assert_eq!(ThingsDatabase::convert_task_type(0), TaskType::Todo);
454        assert_eq!(ThingsDatabase::convert_task_type(4), TaskType::Todo);
455        assert_eq!(ThingsDatabase::convert_task_type(-1), TaskType::Todo);
456    }
457
458    /// Test convert_task_status with all possible values
459    #[test]
460    fn test_convert_task_status() {
461        assert_eq!(
462            ThingsDatabase::convert_task_status(1),
463            TaskStatus::Completed
464        );
465        assert_eq!(ThingsDatabase::convert_task_status(2), TaskStatus::Canceled);
466        assert_eq!(ThingsDatabase::convert_task_status(3), TaskStatus::Trashed);
467        assert_eq!(
468            ThingsDatabase::convert_task_status(0),
469            TaskStatus::Incomplete
470        );
471        assert_eq!(
472            ThingsDatabase::convert_task_status(4),
473            TaskStatus::Incomplete
474        );
475        assert_eq!(
476            ThingsDatabase::convert_task_status(-1),
477            TaskStatus::Incomplete
478        );
479    }
480
481    /// Test convert_timestamp with various inputs
482    #[test]
483    fn test_convert_timestamp() {
484        // Test with None - should return current time
485        let result = ThingsDatabase::convert_timestamp(None);
486        let _ = result; // Just verify it doesn't panic
487
488        // Test with valid timestamp - just check it returns a valid DateTime
489        let timestamp = 1234567890.0;
490        let result = ThingsDatabase::convert_timestamp(Some(timestamp));
491        let _ = result; // Just verify it doesn't panic
492
493        // Test with negative timestamp (should fallback to now)
494        let timestamp = -1234567890.0;
495        let result = ThingsDatabase::convert_timestamp(Some(timestamp));
496        let _ = result; // Just verify it doesn't panic
497
498        // Test with very large timestamp (should fallback to now)
499        let timestamp = 999999999999.0;
500        let result = ThingsDatabase::convert_timestamp(Some(timestamp));
501        let _ = result; // Just verify it doesn't panic
502    }
503
504    /// Test convert_date with various inputs
505    #[test]
506    fn test_convert_date() {
507        // Test with None
508        assert_eq!(ThingsDatabase::convert_date(None), None);
509
510        // Test with valid date (days since 2001-01-01)
511        let days = 0; // 2001-01-01
512        let result = ThingsDatabase::convert_date(Some(days));
513        assert_eq!(
514            result,
515            Some(chrono::NaiveDate::from_ymd_opt(2001, 1, 1).unwrap())
516        );
517
518        // Test with 365 days (2002-01-01)
519        let days = 365;
520        let result = ThingsDatabase::convert_date(Some(days));
521        assert_eq!(
522            result,
523            Some(chrono::NaiveDate::from_ymd_opt(2002, 1, 1).unwrap())
524        );
525
526        // Test with negative days (should return None as it's before 2001-01-01)
527        let days = -1;
528        let result = ThingsDatabase::convert_date(Some(days));
529        assert_eq!(result, None);
530
531        // Test with very large number
532        let days = 10000;
533        let result = ThingsDatabase::convert_date(Some(days));
534        assert!(result.is_some());
535    }
536
537    /// Test convert_uuid with various inputs
538    #[test]
539    fn test_convert_uuid() {
540        // Test with None
541        assert_eq!(ThingsDatabase::convert_uuid(None), None);
542
543        // Test with valid UUID string
544        let uuid_str = "550e8400-e29b-41d4-a716-446655440000";
545        let result = ThingsDatabase::convert_uuid(Some(uuid_str.to_string()));
546        assert_eq!(result, Some(Uuid::parse_str(uuid_str).unwrap()));
547
548        // Test with invalid UUID string (should generate deterministic UUID)
549        let uuid_str = "invalid-uuid";
550        let result = ThingsDatabase::convert_uuid(Some(uuid_str.to_string()));
551        assert!(result.is_some());
552        // Should be deterministic
553        let result2 = ThingsDatabase::convert_uuid(Some(uuid_str.to_string()));
554        assert_eq!(result, result2);
555
556        // Test with empty string
557        let result = ThingsDatabase::convert_uuid(Some("".to_string()));
558        assert!(result.is_some());
559
560        // Test with special characters
561        let uuid_str = "!@#$%^&*()";
562        let result = ThingsDatabase::convert_uuid(Some(uuid_str.to_string()));
563        assert!(result.is_some());
564    }
565
566    /// Test map_project_row with various inputs
567    #[test]
568    fn test_map_project_row() {
569        let temp_file = NamedTempFile::new().unwrap();
570        let db_path = temp_file.path();
571        create_test_database(db_path).unwrap();
572
573        let db = ThingsDatabase::new(db_path).unwrap();
574
575        // Test with a real project row (if TMProject table exists)
576        let mut stmt = match db.conn.prepare("SELECT uuid, title, notes, startDate, deadline, creationDate, userModificationDate, area, status FROM TMProject LIMIT 1") {
577            Ok(stmt) => stmt,
578            Err(_) => {
579                // TMProject table doesn't exist, skip this test
580                return;
581            }
582        };
583
584        let mut rows = stmt.query([]).unwrap();
585
586        if let Some(row) = rows.next().unwrap() {
587            let project = ThingsDatabase::map_project_row(row).unwrap();
588            assert!(!project.title.is_empty());
589            assert!(project.uuid != Uuid::nil());
590        }
591    }
592
593    /// Test database connection with invalid path
594    #[test]
595    fn test_database_invalid_path() {
596        let result = ThingsDatabase::new("/nonexistent/path/database.sqlite");
597        assert!(result.is_err());
598    }
599
600    /// Test database connection with malformed database
601    #[test]
602    fn test_database_malformed() {
603        let temp_file = NamedTempFile::new().unwrap();
604        let db_path = temp_file.path();
605
606        // Create a file that's not a valid SQLite database
607        std::fs::write(db_path, "not a database").unwrap();
608
609        let result = ThingsDatabase::new(db_path);
610        // SQLite might still open the file, so we test that it fails on query
611        match result {
612            Ok(db) => {
613                // If it opens, it should fail on query
614                let tasks = db.get_inbox(Some(1));
615                assert!(tasks.is_err());
616            }
617            Err(_) => {
618                // Expected error
619            }
620        }
621    }
622
623    /// Test get_inbox with malformed data
624    #[test]
625    fn test_get_inbox_malformed_data() {
626        let temp_file = NamedTempFile::new().unwrap();
627        let db_path = temp_file.path();
628        create_test_database(db_path).unwrap();
629
630        let db = ThingsDatabase::new(db_path).unwrap();
631
632        // Insert malformed data
633        db.conn.execute(
634            "INSERT INTO TMTask (uuid, title, type, status, notes, startDate, deadline, creationDate, userModificationDate, project, area, heading) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
635            ("invalid-uuid", "Test Task", 1, 0, "Notes", 0, 0, 0.0, 0.0, "invalid-project", "invalid-area", "invalid-heading")
636        ).unwrap();
637
638        // Should handle malformed data gracefully
639        let tasks = db.get_inbox(Some(10)).unwrap();
640        assert!(!tasks.is_empty());
641    }
642
643    /// Test get_today with edge case dates
644    #[test]
645    fn test_get_today_edge_cases() {
646        let temp_file = NamedTempFile::new().unwrap();
647        let db_path = temp_file.path();
648        create_test_database(db_path).unwrap();
649
650        let db = ThingsDatabase::new(db_path).unwrap();
651
652        // Test with very old date
653        db.conn.execute(
654            "INSERT INTO TMTask (uuid, title, type, status, notes, startDate, deadline, creationDate, userModificationDate, project, area, heading) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
655            ("test-uuid-1", "Old Task", 0, 0, "Notes", -1000, 0, 0.0, 0.0, None::<String>, None::<String>, None::<String>)
656        ).unwrap();
657
658        // Test with future date
659        db.conn.execute(
660            "INSERT INTO TMTask (uuid, title, type, status, notes, startDate, deadline, creationDate, userModificationDate, project, area, heading) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
661            ("test-uuid-2", "Future Task", 0, 0, "Notes", 10000, 0, 0.0, 0.0, None::<String>, None::<String>, None::<String>)
662        ).unwrap();
663
664        let tasks = db.get_today(Some(10)).unwrap();
665        // Should handle edge cases gracefully
666        let _ = tasks.len();
667    }
668
669    /// Test search_tasks with edge cases
670    #[test]
671    fn test_search_tasks_edge_cases() {
672        let temp_file = NamedTempFile::new().unwrap();
673        let db_path = temp_file.path();
674        create_test_database(db_path).unwrap();
675
676        let db = ThingsDatabase::new(db_path).unwrap();
677
678        // Test with empty query
679        let tasks = db.search_tasks("", Some(10)).unwrap();
680        let _ = tasks.len();
681
682        // Test with very long query
683        let long_query = "a".repeat(1000);
684        let tasks = db.search_tasks(&long_query, Some(10)).unwrap();
685        let _ = tasks.len();
686
687        // Test with special characters
688        let special_query = "!@#$%^&*()";
689        let tasks = db.search_tasks(special_query, Some(10)).unwrap();
690        let _ = tasks.len();
691
692        // Test with SQL injection attempt
693        let sql_query = "'; DROP TABLE TMTask; --";
694        let tasks = db.search_tasks(sql_query, Some(10)).unwrap();
695        let _ = tasks.len();
696    }
697
698    /// Test get_projects with edge cases
699    #[test]
700    fn test_get_projects_edge_cases() {
701        let temp_file = NamedTempFile::new().unwrap();
702        let db_path = temp_file.path();
703        create_test_database(db_path).unwrap();
704
705        let db = ThingsDatabase::new(db_path).unwrap();
706
707        // Test with invalid area UUID
708        let invalid_uuid = Uuid::new_v4();
709        let projects = db.get_projects(Some(invalid_uuid)).unwrap();
710        assert!(projects.is_empty());
711
712        // Test with no area filter
713        let projects = db.get_projects(None).unwrap();
714        let _ = projects.len();
715    }
716
717    /// Test get_areas with edge cases
718    #[test]
719    fn test_get_areas_edge_cases() {
720        let temp_file = NamedTempFile::new().unwrap();
721        let db_path = temp_file.path();
722        create_test_database(db_path).unwrap();
723
724        let db = ThingsDatabase::new(db_path).unwrap();
725
726        // Test basic areas functionality
727        let areas = db.get_areas().unwrap();
728        let _ = areas.len();
729    }
730
731    /// Test database connection persistence
732    #[test]
733    fn test_database_connection_persistence() {
734        let temp_file = NamedTempFile::new().unwrap();
735        let db_path = temp_file.path();
736        create_test_database(db_path).unwrap();
737
738        let db1 = ThingsDatabase::new(db_path).unwrap();
739        let tasks1 = db1.get_inbox(Some(5)).unwrap();
740
741        // Create another connection to the same database
742        let db2 = ThingsDatabase::new(db_path).unwrap();
743        let tasks2 = db2.get_inbox(Some(5)).unwrap();
744
745        // Should get the same results
746        assert_eq!(tasks1.len(), tasks2.len());
747    }
748
749    /// Test database error recovery
750    #[test]
751    fn test_database_error_recovery() {
752        let temp_file = NamedTempFile::new().unwrap();
753        let db_path = temp_file.path();
754        create_test_database(db_path).unwrap();
755
756        let db = ThingsDatabase::new(db_path).unwrap();
757
758        // Test that we can recover from errors
759        let result = db.get_inbox(Some(5));
760        assert!(result.is_ok());
761
762        // Test with invalid limit
763        let result = db.get_inbox(Some(0));
764        assert!(result.is_ok());
765    }
766
767    /// Test database query consistency
768    #[test]
769    fn test_database_query_consistency() {
770        let temp_file = NamedTempFile::new().unwrap();
771        let db_path = temp_file.path();
772        create_test_database(db_path).unwrap();
773
774        let db = ThingsDatabase::new(db_path).unwrap();
775
776        // Test that different queries return consistent results
777        let inbox = db.get_inbox(Some(10)).unwrap();
778        let today = db.get_today(Some(10)).unwrap();
779        let all_tasks = db.search_tasks("", Some(20)).unwrap();
780
781        // Inbox should be a subset of all tasks
782        assert!(all_tasks.len() >= inbox.len());
783
784        // Today should be a subset of all tasks
785        assert!(all_tasks.len() >= today.len());
786    }
787
788    /// Test database with mock data consistency
789    #[test]
790    fn test_database_with_mock_data_consistency() {
791        let temp_file = NamedTempFile::new().unwrap();
792        let db_path = temp_file.path();
793        create_test_database(db_path).unwrap();
794
795        let db = ThingsDatabase::new(db_path).unwrap();
796
797        // Test that mock data is consistent
798        let tasks = db.get_inbox(Some(10)).unwrap();
799        let projects = db.get_projects(None).unwrap();
800        let areas = db.get_areas().unwrap();
801
802        // Should have some data
803        assert!(tasks.len() > 0 || projects.len() > 0 || areas.len() > 0);
804
805        // Test that tasks with area relationships work
806        let tasks_with_areas = tasks.iter().filter(|t| t.area_uuid.is_some()).count();
807        let _ = tasks_with_areas;
808    }
809
810    /// Test database performance with large limits
811    #[test]
812    fn test_database_performance_with_large_limits() {
813        let temp_file = NamedTempFile::new().unwrap();
814        let db_path = temp_file.path();
815        create_test_database(db_path).unwrap();
816
817        let db = ThingsDatabase::new(db_path).unwrap();
818
819        // Test with very large limit
820        let start = std::time::Instant::now();
821        let tasks = db.get_inbox(Some(10000)).unwrap();
822        let duration = start.elapsed();
823
824        // Should complete quickly even with large limit
825        assert!(duration.as_secs() < 5);
826        let _ = tasks.len();
827    }
828
829    /// Test database helper functions indirectly
830    #[test]
831    fn test_database_helper_functions_indirectly() {
832        let temp_file = NamedTempFile::new().unwrap();
833        let db_path = temp_file.path();
834        create_test_database(db_path).unwrap();
835
836        let db = ThingsDatabase::new(db_path).unwrap();
837
838        // Test that helper functions are called through get_inbox
839        let tasks = db.get_inbox(Some(5)).unwrap();
840
841        // Verify that tasks have proper types and statuses
842        for task in tasks {
843            assert!(matches!(
844                task.task_type,
845                TaskType::Project | TaskType::Heading | TaskType::Area | TaskType::Todo
846            ));
847            assert!(matches!(
848                task.status,
849                TaskStatus::Completed
850                    | TaskStatus::Canceled
851                    | TaskStatus::Trashed
852                    | TaskStatus::Incomplete
853            ));
854        }
855    }
856}
things3_core/database.rs

things3_core/
database.rs
