OXYGENHUB
◆ v0.5.1 oxygenhub / crates / hub / src / db / activity.rs
6.6 KiB · Rust · last changed feat(web): a denser sky, every change and run a mark; short links; the name above (refs #157) 22 days ago source blame history raw
25ab8c73 Andy McCall ↶ 1 
//! Cross-repository reads for the signed-in dashboard (hub spec §6):
2 
//! the activity feed and the proposals waiting on a reviewer. Callers
3 
//! pass the repository ids the viewer may see, so nothing here decides
4 
//! access.
5
6 
use super::Pool;
7 
use sqlx::FromRow;
8
9 
/// One line of the activity feed.
10 
#[derive(Clone, Debug, FromRow)]
11 
pub struct Activity {
12 
    pub owner: String,
13 
    pub name: String,
14 
    pub at_ms: i64,
15 
    pub actor: String,
16 
    /// `push`, `release`, `proposal` or `issue`.
17 
    pub kind: String,
18 
    /// What happened: the event name, or the bookmark for a push.
19 
    pub event: String,
20 
    /// The proposal or issue title, the release tag.
21 
    pub title: String,
22 
    /// The proposal or issue number, 0 otherwise.
23 
    pub number: i64,
24 
}
25
26 
impl Activity {
27 
    /// Where the line links to.
28 
    pub fn url(&self) -> String {
29 
        let base = format!("/{}/{}", self.owner, self.name);
30 
        match self.kind.as_str() {
31 
            "proposal" => format!("{base}/proposal/{}", self.number),
32 
            "issue" => format!("{base}/issue/{}", self.number),
33 
            "release" => format!("{base}/releases/tag/{}", self.title),
34 
            _ => format!("{base}/pushes"),
35 
        }
36 
    }
37 
}
38
39 
fn ids_json(ids: &[i64]) -> String {
40 
    serde_json::to_string(ids).unwrap_or_else(|_| "[]".into())
41 
}
42
43 
/// The newest `limit` events across the repositories in `repo_ids`.
44 
pub async fn feed(pool: &Pool, repo_ids: &[i64], limit: i64) -> sqlx::Result<Vec<Activity>> {
10174045 Andy McCall ↶ 45 
    feed_by(pool, repo_ids, None, limit).await
46 
}
47
48 
/// Like `feed`, only what `actor` did when one is given (profile pages).
49 
pub async fn feed_by(pool: &Pool, repo_ids: &[i64], actor: Option<&str>, limit: i64) -> sqlx::Result<Vec<Activity>> {
25ab8c73 Andy McCall ↶ 50 
    sqlx::query_as(
51 
        "WITH seen(id) AS (SELECT value FROM json_each(?)), ev AS ( \
52 
           SELECT repo_id, at_ms, pusher AS actor, 'push' AS kind, reference AS event, '' AS title, 0 AS number FROM pushes WHERE ok = 1 \
53 
           UNION ALL SELECT repo_id, created_ms, author, 'release', 'published', tag, 0 FROM releases \
54 
           UNION ALL SELECT p.repo_id, e.at_ms, e.actor, 'proposal', e.event, p.title, p.number FROM proposal_events e JOIN proposals p ON p.id = e.proposal_id \
55 
           UNION ALL SELECT i.repo_id, e.at_ms, e.actor, 'issue', e.event, i.title, i.number FROM issue_events e JOIN issues i ON i.id = e.issue_id) \
56 
         SELECT r.owner, r.name, ev.at_ms, ev.actor, ev.kind, ev.event, ev.title, ev.number \
57 
         FROM ev JOIN repositories r ON r.id = ev.repo_id WHERE ev.repo_id IN (SELECT id FROM seen) \
10174045 Andy McCall ↶ 58 
           AND (? IS NULL OR ev.actor = ? COLLATE NOCASE) \
25ab8c73 Andy McCall ↶ 59 
         ORDER BY ev.at_ms DESC LIMIT ?",
60 
    )
61 
    .bind(ids_json(repo_ids))
10174045 Andy McCall ↶ 62 
    .bind(actor)
63 
    .bind(actor)
25ab8c73 Andy McCall ↶ 64 
    .bind(limit)
65 
    .fetch_all(pool)
66 
    .await
67 
}
68
69 
/// A proposal on the dashboard, with its repository's name.
70 
#[derive(Clone, Debug, FromRow)]
71 
pub struct ProposalLine {
72 
    pub owner: String,
73 
    pub name: String,
74 
    pub number: i64,
75 
    pub title: String,
76 
    pub author: String,
77 
    pub state: String,
78 
    pub conflicted: bool,
79 
    pub updated_ms: i64,
80 
}
81
82 
/// Open proposals in `repo_ids` by someone else that `account_id` has
83 
/// not reviewed yet: the review queue.
84 
pub async fn awaiting_review(pool: &Pool, account_id: i64, repo_ids: &[i64]) -> sqlx::Result<Vec<ProposalLine>> {
85 
    sqlx::query_as(
86 
        "SELECT r.owner, r.name, p.number, p.title, p.author, p.state, p.conflicted, p.updated_ms \
87 
         FROM proposals p JOIN repositories r ON r.id = p.repo_id \
88 
         WHERE p.repo_id IN (SELECT value FROM json_each(?)) AND p.state = 'open' \
89 
           AND (p.author_id IS NULL OR p.author_id != ?) \
90 
           AND NOT EXISTS (SELECT 1 FROM proposal_reviews v WHERE v.proposal_id = p.id AND v.author_id = ?) \
91 
         ORDER BY p.updated_ms DESC",
92 
    )
93 
    .bind(ids_json(repo_ids))
94 
    .bind(account_id)
95 
    .bind(account_id)
96 
    .fetch_all(pool)
97 
    .await
98 
}
99
100 
/// `account_id`'s own open and draft proposals in repositories it can
101 
/// still see.
102 
pub async fn authored(pool: &Pool, account_id: i64, repo_ids: &[i64]) -> sqlx::Result<Vec<ProposalLine>> {
103 
    sqlx::query_as(
104 
        "SELECT r.owner, r.name, p.number, p.title, p.author, p.state, p.conflicted, p.updated_ms \
105 
         FROM proposals p JOIN repositories r ON r.id = p.repo_id \
106 
         WHERE p.author_id = ? AND p.state IN ('open', 'draft') AND p.repo_id IN (SELECT value FROM json_each(?)) \
107 
         ORDER BY p.updated_ms DESC",
108 
    )
109 
    .bind(account_id)
110 
    .bind(ids_json(repo_ids))
111 
    .fetch_all(pool)
112 
    .await
113 
}
114
115 
/// An issue on the dashboard, with its repository's name.
116 
#[derive(Clone, Debug, FromRow)]
117 
pub struct IssueLine {
118 
    pub owner: String,
119 
    pub name: String,
120 
    pub number: i64,
121 
    pub title: String,
122 
    pub updated_ms: i64,
123 
}
124
125 
/// Open issues assigned to `account_id`, in repositories it can see.
126 
pub async fn assigned(pool: &Pool, account_id: i64, repo_ids: &[i64]) -> sqlx::Result<Vec<IssueLine>> {
127 
    sqlx::query_as(
128 
        "SELECT r.owner, r.name, i.number, i.title, i.updated_ms \
129 
         FROM issues i JOIN issue_assignees a ON a.issue_id = i.id JOIN repositories r ON r.id = i.repo_id \
130 
         WHERE a.account_id = ? AND i.state = 'open' AND i.repo_id IN (SELECT value FROM json_each(?)) \
131 
         ORDER BY i.updated_ms DESC",
132 
    )
133 
    .bind(account_id)
134 
    .bind(ids_json(repo_ids))
135 
    .fetch_all(pool)
136 
    .await
137 
}
df5c69cb Andy McCall ↶ 138
139 
/// One event of Your sky (docs/backgrounds.md §5): its repository, when,
140 
/// its kind (0 a push, 1 a proposal event, 2 an issue event, 3 a release,
141 
/// 4 a Flow run) and how many marks it makes: a push's changes, else one.
142 
#[derive(Clone, Debug, FromRow)]
143 
pub struct SkyEvent {
144 
    pub repo_id: i64,
145 
    pub at_ms: i64,
146 
    pub kind: i64,
147 
    pub n: i64,
148 
}
149
150 
/// The events since `since_ms` in `repo_ids`, newest first, at most `limit`.
151 
pub async fn sky(pool: &Pool, repo_ids: &[i64], since_ms: i64, limit: i64) -> sqlx::Result<Vec<SkyEvent>> {
152 
    sqlx::query_as(
153 
        "WITH seen(id) AS (SELECT value FROM json_each(?)), ev AS ( \
154 
           SELECT repo_id, at_ms, 0 AS kind, MAX(changes, 1) AS n FROM pushes WHERE ok = 1 \
155 
           UNION ALL SELECT p.repo_id, e.at_ms, 1, 1 FROM proposal_events e JOIN proposals p ON p.id = e.proposal_id \
156 
           UNION ALL SELECT i.repo_id, e.at_ms, 2, 1 FROM issue_events e JOIN issues i ON i.id = e.issue_id \
157 
           UNION ALL SELECT repo_id, created_ms, 3, 1 FROM releases \
158 
           UNION ALL SELECT repo_id, created_ms, 4, 1 FROM flow_runs) \
159 
         SELECT repo_id, at_ms, kind, n FROM ev WHERE repo_id IN (SELECT id FROM seen) AND at_ms >= ? \
160 
         ORDER BY at_ms DESC LIMIT ?",
161 
    )
162 
    .bind(ids_json(repo_ids))
163 
    .bind(since_ms)
164 
    .bind(limit)
165 
    .fetch_all(pool)
166 
    .await
167 
}