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
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
use crate::library::MediaType;
use crate::media::Media;
use crate::user::UserID;
use crate::DatabaseError as DieselError;
use serde::Serialize;
use std::time::SystemTime;
#[derive(Debug, Serialize)]
pub struct Progress {
pub id: i64,
pub delta: i64,
pub media_id: i64,
pub user_id: UserID,
pub populated: i64,
}
impl Progress {
pub async fn set(
conn: &mut crate::Transaction<'_>,
delta: i64,
uid: UserID,
mid: i64,
) -> Result<usize, DieselError> {
let timestamp = SystemTime::now()
.duration_since(SystemTime::UNIX_EPOCH)
.unwrap()
.as_secs() as i64;
Ok(sqlx::query!(
"INSERT OR REPLACE INTO progress (delta, media_id, user_id, populated)
VALUES ($1, $2, $3, $4)",
delta,
mid,
uid,
timestamp
)
.execute(&mut *conn)
.await?
.rows_affected() as usize)
}
pub async fn get_for_media_user(
conn: &mut crate::Transaction<'_>,
uid: UserID,
mid: i64,
) -> Result<Self, DieselError> {
Ok(sqlx::query_as!(
Progress,
r#"SELECT id, user_id as "user_id: UserID", delta, media_id, populated FROM progress
WHERE user_id = ?
AND media_id = ?"#,
uid,
mid
)
.fetch_optional(&mut *conn)
.await?
.unwrap_or(Self {
id: Default::default(),
media_id: mid,
user_id: uid,
delta: Default::default(),
populated: Default::default(),
}))
}
pub async fn get_total_time_spent_watching(
conn: &mut crate::Transaction<'_>,
uid: UserID,
) -> Result<i32, DieselError> {
Ok(sqlx::query!(
"SELECT COALESCE(SUM(progress.delta), 0) as total FROM progress
WHERE progress.user_id = ?",
uid
)
.fetch_one(&mut *conn)
.await?
.total
.unwrap_or_default())
}
pub async fn get_total_for_media(
conn: &mut crate::Transaction<'_>,
media: &Media,
uid: UserID,
) -> Result<i64, DieselError> {
match media.media_type {
MediaType::Tv => Ok(Self::get_total_for_tv(conn, uid, media.id).await? as i64),
_ => Ok(Self::get_for_media_user(conn, uid, media.id)
.await
.map(|x| x.delta)?),
}
}
pub async fn get_progress_for_media(
conn: &mut crate::Transaction<'_>,
id: i64,
uid: UserID,
) -> Result<(i64, i64), DieselError> {
#[derive(sqlx::FromRow)]
struct Record {
delta: i64,
duration: i64,
}
let record = sqlx::query_as::<_, Record>(
"SELECT progress.delta, MAX(mediafile.duration) duration FROM _tblmedia
INNER JOIN mediafile ON mediafile.media_id = _tblmedia.id
LEFT OUTER JOIN progress ON progress.media_id = _tblmedia.id AND progress.user_id = ?
WHERE _tblmedia.id = ?
GROUP BY _tblmedia.id
LIMIT 1",
)
.bind(uid)
.bind(id)
.fetch_one(&mut *conn)
.await?;
Ok((record.delta, record.duration))
}
pub async fn get_total_for_tv(
conn: &mut crate::Transaction<'_>,
uid: UserID,
tv_id: i64,
) -> Result<i32, DieselError> {
#[derive(sqlx::FromRow)]
struct Row {
total: i32,
}
Ok(sqlx::query_as::<_, Row>(
"SELECT COALESCE(SUM(progress.delta), 0) as total FROM _tblmedia
JOIN progress ON progress.media_id = _tblmedia.id
JOIN episode ON episode.id = _tblmedia.id
JOIN season on season.id = episode.seasonid
JOIN tv_show ON tv_show.id = season.tvshowid
WHERE tv_show.id = ?
AND progress.user_id = ?",
)
.bind(tv_id)
.bind(uid)
.fetch_one(&mut *conn)
.await?
.total)
}
pub async fn get_continue_watching(
conn: &mut crate::Transaction<'_>,
uid: UserID,
count: i64,
) -> Result<Vec<i64>, DieselError> {
Ok(sqlx::query_scalar(
r#"SELECT _tblmedia.id FROM _tblmedia
JOIN season on season.tvshowid = _tblmedia.id
JOIN episode on episode.seasonid = season.id
JOIN progress on progress.media_id = episode.id
JOIN library on library.id = _tblmedia.library_id
WHERE NOT progress.populated = 0
AND progress.user_id = ?
AND NOT library.hidden
GROUP BY _tblmedia.id
ORDER BY progress.populated DESC
LIMIT ?"#,
)
.bind(uid)
.bind(count)
.fetch_all(&mut *conn)
.await?)
}
}
