Skip to main content

max / makenotwork

5.5 KB · 168 lines History Blame Raw
1 //! Media library queries: user-scoped files for inline markdown content.
2
3 use sqlx::PgPool;
4
5 use super::id_types::{MediaFileId, UserId};
6 use super::models::DbMediaFile;
7 use crate::error::Result;
8
9 /// Insert a new media file record.
10 #[allow(clippy::too_many_arguments)]
11 #[tracing::instrument(skip_all)]
12 pub(crate) async fn create<'e>(
13 executor: impl sqlx::PgExecutor<'e>,
14 user_id: UserId,
15 folder: &str,
16 filename: &str,
17 s3_key: &str,
18 content_type: &str,
19 file_size_bytes: i64,
20 media_type: &str,
21 scan_status: &str,
22 ) -> Result<DbMediaFile> {
23 let row = sqlx::query_as!(
24 DbMediaFile,
25 r#"
26 INSERT INTO media_files (user_id, folder, filename, s3_key, content_type, file_size_bytes, media_type, scan_status)
27 VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
28 RETURNING id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
29 s3_key, content_type, file_size_bytes, media_type, scan_status,
30 created_at AS "created_at: chrono::DateTime<chrono::Utc>"
31 "#,
32 user_id as UserId,
33 folder,
34 filename,
35 s3_key,
36 content_type,
37 file_size_bytes,
38 media_type,
39 scan_status,
40 )
41 .fetch_one(executor)
42 .await?;
43
44 Ok(row)
45 }
46
47 /// Safety cap on the media picker listing. Hitting it is logged at WARN so a
48 /// creator with more than this many clean files in one folder isn't silently
49 /// truncated (mirrors `versions::VERSIONS_LIST_HARD_CAP`).
50 pub(crate) const MEDIA_LIST_HARD_CAP: i64 = 500;
51
52 /// List media files for a user, optionally filtered by folder.
53 #[tracing::instrument(skip_all)]
54 pub(crate) async fn list_by_user_folder(
55 pool: &PgPool,
56 user_id: UserId,
57 folder: Option<&str>,
58 ) -> Result<Vec<DbMediaFile>> {
59 let rows = if let Some(f) = folder {
60 sqlx::query_as!(
61 DbMediaFile,
62 r#"SELECT id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
63 s3_key, content_type, file_size_bytes, media_type, scan_status,
64 created_at AS "created_at: chrono::DateTime<chrono::Utc>"
65 FROM media_files
66 WHERE user_id = $1 AND folder = $2 AND scan_status = 'clean'
67 ORDER BY created_at DESC LIMIT $3"#,
68 user_id as UserId,
69 f,
70 MEDIA_LIST_HARD_CAP,
71 )
72 .fetch_all(pool)
73 .await?
74 } else {
75 sqlx::query_as!(
76 DbMediaFile,
77 r#"SELECT id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
78 s3_key, content_type, file_size_bytes, media_type, scan_status,
79 created_at AS "created_at: chrono::DateTime<chrono::Utc>"
80 FROM media_files
81 WHERE user_id = $1 AND scan_status = 'clean'
82 ORDER BY created_at DESC LIMIT $2"#,
83 user_id as UserId,
84 MEDIA_LIST_HARD_CAP,
85 )
86 .fetch_all(pool)
87 .await?
88 };
89
90 if rows.len() as i64 == MEDIA_LIST_HARD_CAP {
91 tracing::warn!(
92 %user_id, cap = MEDIA_LIST_HARD_CAP,
93 "list_by_user_folder hit hard cap; some media omitted from the picker"
94 );
95 }
96
97 Ok(rows)
98 }
99
100 /// List distinct folder names for a user.
101 #[tracing::instrument(skip_all)]
102 pub(crate) async fn list_folders(pool: &PgPool, user_id: UserId) -> Result<Vec<String>> {
103 let folders: Vec<String> = sqlx::query_scalar!(
104 "SELECT DISTINCT folder FROM media_files WHERE user_id = $1 ORDER BY folder",
105 user_id as UserId,
106 )
107 .fetch_all(pool)
108 .await?;
109
110 Ok(folders)
111 }
112
113 /// Get a single media file by ID.
114 #[tracing::instrument(skip_all)]
115 pub(crate) async fn get_by_id(pool: &PgPool, id: MediaFileId) -> Result<Option<DbMediaFile>> {
116 let row = sqlx::query_as!(
117 DbMediaFile,
118 r#"SELECT id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
119 s3_key, content_type, file_size_bytes, media_type, scan_status,
120 created_at AS "created_at: chrono::DateTime<chrono::Utc>"
121 FROM media_files WHERE id = $1"#,
122 id as MediaFileId,
123 )
124 .fetch_optional(pool)
125 .await?;
126
127 Ok(row)
128 }
129
130 /// Delete a media file owned by `user_id`. Ownership is scoped IN the SQL
131 /// (`WHERE id = $1 AND user_id = $2`) so the delete can't touch another user's
132 /// row even if a caller forgot to pre-check (Sec-M2); returns `None` when the row
133 /// doesn't exist or isn't owned by `user_id`.
134 #[tracing::instrument(skip_all)]
135 pub(crate) async fn delete<'e>(
136 executor: impl sqlx::PgExecutor<'e>,
137 id: MediaFileId,
138 user_id: UserId,
139 ) -> Result<Option<DbMediaFile>> {
140 let row = sqlx::query_as!(
141 DbMediaFile,
142 r#"DELETE FROM media_files WHERE id = $1 AND user_id = $2
143 RETURNING id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
144 s3_key, content_type, file_size_bytes, media_type, scan_status,
145 created_at AS "created_at: chrono::DateTime<chrono::Utc>""#,
146 id as MediaFileId,
147 user_id as UserId,
148 )
149 .fetch_optional(executor)
150 .await?;
151
152 Ok(row)
153 }
154
155 /// Count media files for a user.
156 #[allow(dead_code)]
157 #[tracing::instrument(skip_all)]
158 pub(crate) async fn count_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
159 let count: i64 = sqlx::query_scalar!(
160 r#"SELECT COUNT(*) AS "count!" FROM media_files WHERE user_id = $1"#,
161 user_id as UserId,
162 )
163 .fetch_one(pool)
164 .await?;
165
166 Ok(count)
167 }
168