aven 0.1.6

Local-first task manager CLI and sync server
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131

use anyhow::Result;
use sqlx::{Row, SqliteConnection};

use super::TuiStore;
use super::types::{DatabaseStatsPriorityCounts, DatabaseStatsStatusCounts, TuiDatabaseStats};
use crate::query::sync_history_stats;
use crate::workspaces::Workspace;

impl TuiStore {
    pub(crate) async fn load_database_stats(&mut self) -> Result<()> {
        let mut conn = self.pool.acquire().await?;
        self.db_stats = load_database_stats(&mut conn, &self.active_workspace).await?;
        Ok(())
    }
}

async fn load_database_stats(
    conn: &mut SqliteConnection,
    workspace: &Workspace,
) -> Result<TuiDatabaseStats> {
    let workspace_id = workspace.id.as_str();
    let row = sqlx::query(
        "SELECT
         COUNT(*) AS total_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND status NOT IN ('done', 'canceled') THEN 1 ELSE 0 END), 0) AS open_tasks,
         COALESCE(SUM(CASE WHEN deleted != 0 THEN 1 ELSE 0 END), 0) AS deleted_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'inbox' THEN 1 ELSE 0 END), 0) AS inbox_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'backlog' THEN 1 ELSE 0 END), 0) AS backlog_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'todo' THEN 1 ELSE 0 END), 0) AS todo_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'active' THEN 1 ELSE 0 END), 0) AS active_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'done' THEN 1 ELSE 0 END), 0) AS done_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND status = 'canceled' THEN 1 ELSE 0 END), 0) AS canceled_tasks,
         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'none' THEN 1 ELSE 0 END), 0) AS none_priority,
         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'low' THEN 1 ELSE 0 END), 0) AS low_priority,
         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'medium' THEN 1 ELSE 0 END), 0) AS medium_priority,
         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'high' THEN 1 ELSE 0 END), 0) AS high_priority,
         COALESCE(SUM(CASE WHEN deleted = 0 AND priority = 'urgent' THEN 1 ELSE 0 END), 0) AS urgent_priority,
         MAX(CASE WHEN deleted = 0 THEN created_at END) AS latest_created_at,
         MAX(CASE WHEN deleted = 0 THEN updated_at END) AS latest_updated_at
         FROM tasks
         WHERE workspace_id = ?",
    )
    .bind(workspace_id)
    .fetch_one(&mut *conn)
    .await?;

    let projects =
        sqlx::query_scalar("SELECT count(*) FROM projects WHERE workspace_id = ? AND deleted = 0")
            .bind(workspace_id)
            .fetch_one(&mut *conn)
            .await?;
    let labels = sqlx::query_scalar("SELECT count(*) FROM labels WHERE workspace_id = ?")
        .bind(workspace_id)
        .fetch_one(&mut *conn)
        .await?;
    let notes = sqlx::query_scalar("SELECT count(*) FROM notes WHERE workspace_id = ?")
        .bind(workspace_id)
        .fetch_one(&mut *conn)
        .await?;
    let task_labels = sqlx::query_scalar("SELECT count(*) FROM task_labels WHERE workspace_id = ?")
        .bind(workspace_id)
        .fetch_one(&mut *conn)
        .await?;
    let sync_history = sync_history_stats(conn).await?;
    let conflicts = sqlx::query_scalar(
        "SELECT count(*) FROM conflicts WHERE workspace_id = ? AND resolved = 0",
    )
    .bind(workspace_id)
    .fetch_one(&mut *conn)
    .await?;
    let sqlite_page_size = sqlite_pragma_i64(conn, SqlitePragma::PageSize).await?;
    let sqlite_page_count = sqlite_pragma_i64(conn, SqlitePragma::PageCount).await?;
    let sqlite_freelist_count = sqlite_pragma_i64(conn, SqlitePragma::FreelistCount).await?;

    Ok(TuiDatabaseStats {
        workspace_name: workspace.name.clone(),
        workspace_key: workspace.key.clone(),
        total_tasks: row.get("total_tasks"),
        open_tasks: row.get("open_tasks"),
        deleted_tasks: row.get("deleted_tasks"),
        statuses: DatabaseStatsStatusCounts {
            inbox: row.get("inbox_tasks"),
            backlog: row.get("backlog_tasks"),
            todo: row.get("todo_tasks"),
            active: row.get("active_tasks"),
            done: row.get("done_tasks"),
            canceled: row.get("canceled_tasks"),
        },
        priorities: DatabaseStatsPriorityCounts {
            none: row.get("none_priority"),
            low: row.get("low_priority"),
            medium: row.get("medium_priority"),
            high: row.get("high_priority"),
            urgent: row.get("urgent_priority"),
        },
        projects,
        labels,
        notes,
        task_labels,
        sync_history,
        conflicts,
        sqlite_page_size,
        sqlite_page_count,
        sqlite_freelist_count,
        latest_created_at: row.get("latest_created_at"),
        latest_updated_at: row.get("latest_updated_at"),
    })
}

#[derive(Debug, Clone, Copy)]
enum SqlitePragma {
    PageSize,
    PageCount,
    FreelistCount,
}

impl SqlitePragma {
    fn sql(self) -> &'static str {
        match self {
            Self::PageSize => "PRAGMA page_size",
            Self::PageCount => "PRAGMA page_count",
            Self::FreelistCount => "PRAGMA freelist_count",
        }
    }
}

async fn sqlite_pragma_i64(conn: &mut SqliteConnection, pragma: SqlitePragma) -> Result<i64> {
    Ok(sqlx::query_scalar(pragma.sql())
        .fetch_one(&mut *conn)
        .await?)
}