oko/db/
video.rs

1use serde::{Deserialize, Serialize};
2use sqlx::{Result, SqlitePool};
3use time::{macros::format_description, OffsetDateTime};
4
5use crate::db::VideoCameraView;
6
7use super::Model;
8
9#[derive(Debug, Clone, Serialize, Deserialize)]
10pub struct Video {
11    pub video_id: i64,
12    pub camera_id: Option<i64>,
13    pub file_path: String,
14    pub start_time: OffsetDateTime,
15    pub end_time: Option<OffsetDateTime>,
16    pub file_size: Option<i64>,
17}
18
19pub struct Default {
20    pub video_id: i64,
21    pub end_time: Option<OffsetDateTime>,
22    pub file_name_format: &'static [time::format_description::BorrowedFormatItem<'static>],
23}
24
25impl Default {
26    #[allow(clippy::unused_self)]
27    pub fn start_time(&self) -> OffsetDateTime {
28        OffsetDateTime::now_utc()
29    }
30}
31
32impl Model for Video {
33    type Default = Default;
34    const DEFAULT: Default = Default {
35        video_id: -1,
36        end_time: None,
37        file_name_format: format_description!(
38            "[year]-[month]-[day]_[hour]-[minute]-[second]_[subsecond digits:9]Z"
39        ),
40    };
41
42    async fn create_using_self(&mut self, pool: &SqlitePool) -> Result<()> {
43        let result = sqlx::query!(
44            r#"
45            INSERT INTO videos (camera_id, file_path, start_time, end_time, file_size)
46            VALUES (?, ?, ?, ?, ?)
47            RETURNING video_id
48            "#,
49            self.camera_id,
50            self.file_path,
51            self.start_time,
52            self.end_time,
53            self.file_size
54        )
55        .fetch_one(pool)
56        .await?;
57
58        self.video_id = result.video_id;
59
60        Ok(())
61    }
62
63    async fn get_using_id(pool: &SqlitePool, id: i64) -> Result<Self> {
64        sqlx::query_as!(
65            Video,
66            r#"
67            SELECT video_id, camera_id, file_path, start_time, end_time, file_size
68            FROM videos WHERE video_id = ?
69            "#,
70            id
71        )
72        .fetch_one(pool)
73        .await
74    }
75
76    async fn update_using_self(&self, pool: &SqlitePool) -> Result<()> {
77        sqlx::query!(
78            r#"
79            UPDATE videos
80            SET camera_id = ?, end_time = ?, file_size = ?
81            WHERE video_id = ?
82            RETURNING video_id
83            "#,
84            self.camera_id,
85            self.end_time,
86            self.file_size,
87            self.video_id
88        )
89        .fetch_one(pool)
90        .await?;
91
92        Ok(())
93    }
94
95    async fn delete_using_id(pool: &SqlitePool, id: i64) -> Result<()> {
96        sqlx::query!(
97            r#"
98            DELETE
99            FROM videos
100            WHERE video_id = ?
101            RETURNING video_id
102            "#,
103            id
104        )
105        .fetch_one(pool)
106        .await?;
107
108        Ok(())
109    }
110}
111
112impl Video {
113    pub async fn list_for_camera(
114        db: &sqlx::Pool<sqlx::Sqlite>,
115        camera_id: i64,
116    ) -> Result<Vec<VideoCameraView>> {
117        sqlx::query_as!(
118            VideoCameraView,
119            r#"
120            SELECT v.video_id, v.camera_id, c.name as camera_name, v.file_path, v.file_size
121            FROM videos v
122            JOIN cameras c ON v.camera_id = c.camera_id
123            WHERE c.camera_id = ?
124            "#,
125            camera_id
126        )
127        .fetch_all(db)
128        .await
129    }
130}
131
132#[allow(clippy::unwrap_used)]
133#[cfg(test)]
134mod tests {
135    use super::*;
136
137    #[sqlx::test(fixtures(path = "../../fixtures", scripts("cameras", "videos")))]
138    async fn create(pool: SqlitePool) -> Result<()> {
139        let mut video = Video {
140            video_id: Video::DEFAULT.video_id,
141            camera_id: Some(1),
142            file_path: "/path/to/video.mp4".to_string(),
143            start_time: Video::DEFAULT.start_time(),
144            end_time: Video::DEFAULT.end_time,
145            file_size: Some(1024),
146        };
147
148        video.create_using_self(&pool).await?;
149
150        assert_eq!(video.video_id, 3);
151
152        let returned_video = Video::get_using_id(&pool, 3).await?;
153
154        assert_eq!(returned_video.camera_id, video.camera_id);
155        assert_eq!(returned_video.file_path, video.file_path);
156        assert_eq!(returned_video.start_time, video.start_time);
157        assert_eq!(returned_video.end_time, video.end_time);
158        assert_eq!(returned_video.file_size, video.file_size);
159
160        Ok(())
161    }
162
163    #[sqlx::test(fixtures(path = "../../fixtures", scripts("cameras", "videos")))]
164    async fn get(pool: SqlitePool) -> Result<(), Box<dyn std::error::Error>> {
165        let video_id = 1;
166        let returned_video = Video::get_using_id(&pool, video_id).await?;
167
168        assert_eq!(returned_video.video_id, video_id);
169        assert_eq!(returned_video.camera_id, Some(1));
170        assert_eq!(
171            returned_video.file_path,
172            "/home/piotrpdev/oko/backend/videos/1.mp4"
173        );
174        assert_eq!(
175            returned_video.start_time,
176            OffsetDateTime::from_unix_timestamp(1_729_479_512)?
177        );
178        assert_eq!(returned_video.file_size, Some(6_762_403));
179
180        Ok(())
181    }
182
183    #[sqlx::test(fixtures(path = "../../fixtures", scripts("cameras", "videos")))]
184    async fn update(pool: SqlitePool) -> Result<()> {
185        let old_video = Video::get_using_id(&pool, 1).await?;
186
187        let updated_video = Video {
188            video_id: old_video.video_id,
189            camera_id: Some(1),
190            file_path: old_video.file_path,
191            start_time: old_video.start_time,
192            end_time: Some(OffsetDateTime::now_utc()),
193            file_size: Some(2048),
194        };
195
196        let updated = updated_video.update_using_self(&pool).await;
197        assert!(updated.is_ok());
198
199        let returned_video = Video::get_using_id(&pool, old_video.video_id).await?;
200        assert_eq!(returned_video.camera_id, updated_video.camera_id);
201        assert_eq!(returned_video.file_path, updated_video.file_path);
202        assert_eq!(returned_video.start_time, updated_video.start_time);
203        assert_eq!(returned_video.end_time, updated_video.end_time);
204        assert_eq!(returned_video.file_size, updated_video.file_size);
205
206        Ok(())
207    }
208
209    #[sqlx::test(fixtures(path = "../../fixtures", scripts("cameras", "videos")))]
210    async fn delete(pool: SqlitePool) -> Result<()> {
211        let video_id = 1;
212        let deleted = Video::delete_using_id(&pool, video_id).await;
213        assert!(deleted.is_ok());
214
215        let returned_video_result = Video::get_using_id(&pool, video_id).await;
216        assert!(returned_video_result.is_err());
217
218        let impossible_deleted = Video::delete_using_id(&pool, video_id).await;
219
220        assert!(impossible_deleted.is_err());
221
222        Ok(())
223    }
224
225    #[sqlx::test(fixtures(path = "../../fixtures", scripts("cameras", "videos")))]
226    async fn list_for_camera(pool: SqlitePool) -> Result<()> {
227        let camera_id = 1;
228        let returned_videos = Video::list_for_camera(&pool, camera_id).await?;
229
230        assert_eq!(returned_videos.len(), 1);
231        assert_eq!(returned_videos.first().unwrap().video_id, 1);
232        assert_eq!(returned_videos.first().unwrap().camera_id, Some(1));
233        assert_eq!(returned_videos.first().unwrap().camera_name, "Front Door");
234        assert_eq!(
235            returned_videos.first().unwrap().file_path,
236            "/home/piotrpdev/oko/backend/videos/1.mp4"
237        );
238        assert_eq!(returned_videos.first().unwrap().file_size, Some(6_762_403));
239
240        Ok(())
241    }
242}
oko/db/video.rs

oko/db/
video.rs
