aven_core/query/
database_stats.rs

1use anyhow::Result;
2use sqlx::{Row, SqliteConnection};
3
4use crate::workspaces::Workspace;
5
6use super::{SyncHistoryStats, sync_history_stats};
7
8#[derive(Debug, Clone, Default, PartialEq, Eq)]
9pub struct DatabaseStatsStatusCounts {
10    pub inbox: i64,
11    pub backlog: i64,
12    pub todo: i64,
13    pub active: i64,
14    pub done: i64,
15    pub canceled: i64,
16}
17
18#[derive(Debug, Clone, Default, PartialEq, Eq)]
19pub struct DatabaseStatsPriorityCounts {
20    pub none: i64,
21    pub low: i64,
22    pub medium: i64,
23    pub high: i64,
24    pub urgent: i64,
25}
26
27#[derive(Debug, Clone, Default, PartialEq, Eq)]
28pub struct DatabaseStats {
29    pub workspace_name: String,
30    pub workspace_key: String,
31    pub total_tasks: i64,
32    pub open_tasks: i64,
33    pub deleted_tasks: i64,
34    pub statuses: DatabaseStatsStatusCounts,
35    pub priorities: DatabaseStatsPriorityCounts,
36    pub projects: i64,
37    pub labels: i64,
38    pub notes: i64,
39    pub task_labels: i64,
40    pub sync_history: SyncHistoryStats,
41    pub conflicts: i64,
42    pub sqlite_page_size: i64,
43    pub sqlite_page_count: i64,
44    pub sqlite_freelist_count: i64,
45    pub latest_created_at: Option<String>,
46    pub latest_updated_at: Option<String>,
47}
48
49pub(super) async fn database_stats(
50    conn: &mut SqliteConnection,
51    workspace: &Workspace,
52) -> Result<DatabaseStats> {
53    let row = sqlx::query(
54        "SELECT
55         COUNT(*) AS total_tasks,
56         COALESCE(SUM(CASE WHEN deleted = 0 AND status NOT IN ('done', 'canceled') THEN 1 ELSE 0 END), 0) AS open_tasks,
57         COALESCE(SUM(CASE WHEN deleted != 0 THEN 1 ELSE 0 END), 0) AS deleted_tasks,
58         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'inbox' THEN 1 ELSE 0 END), 0) AS inbox_tasks,
59         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'backlog' THEN 1 ELSE 0 END), 0) AS backlog_tasks,
60         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'todo' THEN 1 ELSE 0 END), 0) AS todo_tasks,
61         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'active' THEN 1 ELSE 0 END), 0) AS active_tasks,
62         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'done' THEN 1 ELSE 0 END), 0) AS done_tasks,
63         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'canceled' THEN 1 ELSE 0 END), 0) AS canceled_tasks,
64         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'none' THEN 1 ELSE 0 END), 0) AS none_priority,
65         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'low' THEN 1 ELSE 0 END), 0) AS low_priority,
66         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'medium' THEN 1 ELSE 0 END), 0) AS medium_priority,
67         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'high' THEN 1 ELSE 0 END), 0) AS high_priority,
68         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'urgent' THEN 1 ELSE 0 END), 0) AS urgent_priority,
69         MAX(CASE WHEN deleted = 0 THEN created_at END) AS latest_created_at,
70         MAX(CASE WHEN deleted = 0 THEN updated_at END) AS latest_updated_at
71         FROM tasks
72         WHERE workspace_id = ?",
73    )
74    .bind(&workspace.id)
75    .fetch_one(&mut *conn)
76    .await?;
77
78    let projects =
79        sqlx::query_scalar("SELECT count(*) FROM projects WHERE workspace_id = ? AND deleted = 0")
80            .bind(&workspace.id)
81            .fetch_one(&mut *conn)
82            .await?;
83    let labels = sqlx::query_scalar("SELECT count(*) FROM labels WHERE workspace_id = ?")
84        .bind(&workspace.id)
85        .fetch_one(&mut *conn)
86        .await?;
87    let notes = sqlx::query_scalar("SELECT count(*) FROM notes WHERE workspace_id = ?")
88        .bind(&workspace.id)
89        .fetch_one(&mut *conn)
90        .await?;
91    let task_labels = sqlx::query_scalar("SELECT count(*) FROM task_labels WHERE workspace_id = ?")
92        .bind(&workspace.id)
93        .fetch_one(&mut *conn)
94        .await?;
95    let sync_history = sync_history_stats(conn).await?;
96    let conflicts = sqlx::query_scalar(
97        "SELECT count(*) FROM conflicts WHERE workspace_id = ? AND resolved = 0",
98    )
99    .bind(&workspace.id)
100    .fetch_one(&mut *conn)
101    .await?;
102
103    Ok(DatabaseStats {
104        workspace_name: workspace.name.clone(),
105        workspace_key: workspace.key.clone(),
106        total_tasks: row.get("total_tasks"),
107        open_tasks: row.get("open_tasks"),
108        deleted_tasks: row.get("deleted_tasks"),
109        statuses: DatabaseStatsStatusCounts {
110            inbox: row.get("inbox_tasks"),
111            backlog: row.get("backlog_tasks"),
112            todo: row.get("todo_tasks"),
113            active: row.get("active_tasks"),
114            done: row.get("done_tasks"),
115            canceled: row.get("canceled_tasks"),
116        },
117        priorities: DatabaseStatsPriorityCounts {
118            none: row.get("none_priority"),
119            low: row.get("low_priority"),
120            medium: row.get("medium_priority"),
121            high: row.get("high_priority"),
122            urgent: row.get("urgent_priority"),
123        },
124        projects,
125        labels,
126        notes,
127        task_labels,
128        sync_history,
129        conflicts,
130        sqlite_page_size: sqlite_pragma_i64(conn, "PRAGMA page_size").await?,
131        sqlite_page_count: sqlite_pragma_i64(conn, "PRAGMA page_count").await?,
132        sqlite_freelist_count: sqlite_pragma_i64(conn, "PRAGMA freelist_count").await?,
133        latest_created_at: row.get("latest_created_at"),
134        latest_updated_at: row.get("latest_updated_at"),
135    })
136}
137
138async fn sqlite_pragma_i64(conn: &mut SqliteConnection, sql: &'static str) -> Result<i64> {
139    Ok(sqlx::query_scalar(sql).fetch_one(&mut *conn).await?)
140}
aven_core/query/database_stats.rs

aven_core/query/
database_stats.rs
