yggdrasil/api/assets/
refs.rs

1//! 评论与头像的素材引用登记。
2//!
3//! 写入评论、用户头像、友链头像时，先在同一事务内锁定并核对引用的素材，
4//! 再把引用写进对应的外键引用表；素材删除与孤儿清理只查引用表判定。
5
6use crate::api::error::AppError;
7
8/// 评论正文引用了未登记或已删除的素材。
9pub(crate) const MISSING_COMMENT_ASSET: &str = "评论中的图片不存在或已被删除，请重新上传";
10/// 头像 URL 指向未登记或已删除的素材。
11pub(crate) const MISSING_AVATAR_ASSET: &str = "头像素材不存在或已被删除，请重新上传";
12
13/// 一组素材路径在本事务内的锁定结果。
14pub(crate) struct LockedAssets {
15    /// 已登记且已加 FOR KEY SHARE 锁的素材 id（按 id 升序）。
16    pub ids: Vec<uuid::Uuid>,
17    /// 至少一个路径未登记，或在等锁期间被删除。
18    pub missing: bool,
19}
20
21/// 按相对路径锁定素材行，供随后在同一事务内写入引用。
22///
23/// `paths` 须已去重。`FOR KEY SHARE` 与素材删除/清理的 `FOR UPDATE` 互斥：
24/// READ COMMITTED 下若等锁期间删除已提交，本语句重读后跳过该行，计入 `missing`；
25/// 反之拿到锁后，删除方须等本事务提交，其引用复核是加锁之后的另一条语句、
26/// 取新快照，能看到本事务刚提交的引用行。按 id 升序加锁，与删除方加锁顺序一致，
27/// 避免多图评论与批量删除互相死锁。
28pub(crate) async fn lock_assets_by_path(
29    tx: &deadpool_postgres::Transaction<'_>,
30    paths: &[String],
31) -> Result<LockedAssets, AppError> {
32    if paths.is_empty() {
33        return Ok(LockedAssets {
34            ids: Vec::new(),
35            missing: false,
36        });
37    }
38    let rows = tx
39        .query(
40            "SELECT id FROM assets WHERE path = ANY($1) ORDER BY id FOR KEY SHARE",
41            &[&paths],
42        )
43        .await
44        .map_err(AppError::query)?;
45    Ok(LockedAssets {
46        missing: rows.len() < paths.len(),
47        ids: rows.iter().map(|r| r.get(0)).collect(),
48    })
49}
50
51/// 本地头像 URL（`/uploads/<path>`）→ 素材相对路径；外链或空返回 None。
52pub(crate) fn avatar_asset_path(avatar_url: Option<&str>) -> Option<String> {
53    avatar_url?.strip_prefix("/uploads/").map(str::to_string)
54}
55
56/// 登记新评论引用的素材（须已由 [`lock_assets_by_path`] 锁定）。
57pub(crate) async fn insert_comment_refs(
58    tx: &deadpool_postgres::Transaction<'_>,
59    comment_id: i64,
60    asset_ids: &[uuid::Uuid],
61) -> Result<(), AppError> {
62    if asset_ids.is_empty() {
63        return Ok(());
64    }
65    tx.execute(
66        "INSERT INTO comment_asset_refs (comment_id, asset_id) SELECT $1, unnest($2::uuid[])",
67        &[&comment_id, &asset_ids],
68    )
69    .await
70    .map_err(AppError::tx)?;
71    Ok(())
72}
73
74/// 用户头像引用改为 `asset_id`（None 表示无本地头像引用）。
75pub(crate) async fn sync_user_avatar_ref(
76    tx: &deadpool_postgres::Transaction<'_>,
77    user_id: i32,
78    asset_id: Option<uuid::Uuid>,
79) -> Result<(), AppError> {
80    tx.execute(
81        "DELETE FROM user_avatar_asset_refs WHERE user_id = $1",
82        &[&user_id],
83    )
84    .await
85    .map_err(AppError::tx)?;
86    if let Some(asset_id) = asset_id {
87        tx.execute(
88            "INSERT INTO user_avatar_asset_refs (user_id, asset_id) VALUES ($1, $2)",
89            &[&user_id, &asset_id],
90        )
91        .await
92        .map_err(AppError::tx)?;
93    }
94    Ok(())
95}
96
97/// 友链头像引用改为 `asset_id`（None 表示无本地头像引用）。
98pub(crate) async fn sync_friend_avatar_ref(
99    tx: &deadpool_postgres::Transaction<'_>,
100    friend_id: i32,
101    asset_id: Option<uuid::Uuid>,
102) -> Result<(), AppError> {
103    tx.execute(
104        "DELETE FROM friend_avatar_asset_refs WHERE friend_id = $1",
105        &[&friend_id],
106    )
107    .await
108    .map_err(AppError::tx)?;
109    if let Some(asset_id) = asset_id {
110        tx.execute(
111            "INSERT INTO friend_avatar_asset_refs (friend_id, asset_id) VALUES ($1, $2)",
112            &[&friend_id, &asset_id],
113        )
114        .await
115        .map_err(AppError::tx)?;
116    }
117    Ok(())
118}
119
120#[cfg(all(test, feature = "server"))]
121mod tests {
122    use super::lock_assets_by_path;
123    use crate::db::pool::get_conn;
124
125    /// 连接一次性测试库、执行迁移并清空素材与用户相关数据。
126    async fn fresh_database() -> deadpool_postgres::Object {
127        let mut client = get_conn().await.unwrap();
128        let database: String = client
129            .query_one("SELECT current_database()", &[])
130            .await
131            .unwrap()
132            .get(0);
133        assert_eq!(database, "ygg_mcp_test", "requires isolated test database");
134        crate::db::migrate::run_on_conn(&mut client).await.unwrap();
135        client
136            .batch_execute(
137                "TRUNCATE users RESTART IDENTITY CASCADE;
138                 TRUNCATE assets CASCADE;
139                 INSERT INTO users(id,username,email,password_hash,role)
140                   VALUES (1,'refs-owner','refs@test.invalid','unused','admin');",
141            )
142            .await
143            .unwrap();
144        client
145    }
146
147    #[test]
148    #[ignore = "requires disposable DATABASE_URL database ygg_mcp_test"]
149    fn asset_refs_database_comment_requires_and_protects_asset() {
150        crate::db::TEST_DATABASE_RUNTIME.block_on(comment_requires_and_protects_asset_impl());
151    }
152
153    /// 评论引用缺失素材被拒；引用存在的素材登记引用行，评论进回收站后仍禁删、不被清理。
154    async fn comment_requires_and_protects_asset_impl() {
155        let _guard = crate::db::TEST_DATABASE_LOCK.lock().await;
156        let client = fresh_database().await;
157        client
158            .batch_execute(
159                "INSERT INTO posts(id,author_id,title,slug,content_md,status,published_at)
160                   VALUES (1,1,'引用','asset-refs','正文','published',NOW());
161                 INSERT INTO assets(id,path,filename,mime,size_bytes,width,height,content_hash,created_at)
162                   VALUES (gen_random_uuid(),'2026/01/01/120000.kept.webp','kept.webp','image/webp',
163                           3,1,1,'kept',NOW()-INTERVAL '30 days');",
164            )
165            .await
166            .unwrap();
167
168        let missing = crate::api::comments::create_comment(
169            1,
170            None,
171            "读者".into(),
172            "reader@test.invalid".into(),
173            None,
174            "![x](/uploads/2026/01/01/090000.missing.webp)".into(),
175            String::new(),
176        )
177        .await
178        .unwrap();
179        assert_eq!(missing.error_code.as_deref(), Some("missing_asset"));
180        let comments: i64 = client
181            .query_one("SELECT count(*) FROM comments", &[])
182            .await
183            .unwrap()
184            .get(0);
185        assert_eq!(comments, 0, "缺失素材的评论不得写入");
186
187        let created = crate::api::comments::create_comment(
188            1,
189            None,
190            "读者".into(),
191            "reader@test.invalid".into(),
192            None,
193            "![x](/uploads/2026/01/01/120000.kept.webp)".into(),
194            String::new(),
195        )
196        .await
197        .unwrap();
198        assert!(created.success, "{}", created.message);
199        let asset_id: uuid::Uuid = client
200            .query_one("SELECT id FROM assets", &[])
201            .await
202            .unwrap()
203            .get(0);
204        let refs: Vec<(i64, uuid::Uuid)> = client
205            .query("SELECT comment_id, asset_id FROM comment_asset_refs", &[])
206            .await
207            .unwrap()
208            .iter()
209            .map(|r| (r.get(0), r.get(1)))
210            .collect();
211        assert_eq!(refs, [(created.comment_id.unwrap(), asset_id)]);
212
213        client
214            .execute("UPDATE comments SET status='trash', deleted_at=NOW()", &[])
215            .await
216            .unwrap();
217        let deleted = crate::api::assets::delete::delete_asset_impl(asset_id.to_string())
218            .await
219            .unwrap();
220        assert!(!deleted.success);
221        assert_eq!(deleted.message, "该素材正被评论引用，无法删除");
222        let purged = crate::api::assets::delete::purge_orphan_assets_impl(7)
223            .await
224            .unwrap();
225        assert_eq!(purged.deleted_count, 0);
226        let assets: i64 = client
227            .query_one("SELECT count(*) FROM assets", &[])
228            .await
229            .unwrap()
230            .get(0);
231        assert_eq!(assets, 1, "回收站评论引用的素材必须保留");
232    }
233
234    #[test]
235    #[ignore = "requires disposable DATABASE_URL database ygg_mcp_test"]
236    fn asset_refs_database_lock_reports_asset_deleted_while_waiting() {
237        crate::db::TEST_DATABASE_RUNTIME.block_on(lock_reports_deleted_impl());
238    }
239
240    /// 删除方先锁住素材行；写入方等锁期间删除提交，锁定结果必须报告缺失。
241    async fn lock_reports_deleted_impl() {
242        const PATH: &str = "2026/01/01/120000.race.webp";
243        let _guard = crate::db::TEST_DATABASE_LOCK.lock().await;
244        let client = fresh_database().await;
245        client
246            .execute(
247                "INSERT INTO assets(id,path,filename,mime,size_bytes,width,height,content_hash,created_at) \
248                 VALUES (gen_random_uuid(),$1,'race.webp','image/webp',3,1,1,'race',NOW())",
249                &[&PATH],
250            )
251            .await
252            .unwrap();
253
254        let mut deleter = get_conn().await.unwrap();
255        let delete = deleter.transaction().await.unwrap();
256        delete
257            .query_one("SELECT id FROM assets WHERE path = $1 FOR UPDATE", &[&PATH])
258            .await
259            .unwrap();
260
261        let writer = tokio::spawn(async {
262            let mut conn = get_conn().await.unwrap();
263            let tx = conn.transaction().await.unwrap();
264            let locked = lock_assets_by_path(&tx, &[PATH.to_string()]).await.unwrap();
265            (locked.ids.len(), locked.missing)
266        });
267        let mut waited = false;
268        for _ in 0..200 {
269            let blocked: i64 = client
270                .query_one(
271                    "SELECT count(*) FROM pg_stat_activity \
272                     WHERE datname = current_database() AND wait_event_type = 'Lock'",
273                    &[],
274                )
275                .await
276                .unwrap()
277                .get(0);
278            if blocked > 0 {
279                waited = true;
280                break;
281            }
282            tokio::time::sleep(std::time::Duration::from_millis(10)).await;
283        }
284        assert!(waited, "写入方应在素材行锁上等待");
285        delete
286            .execute("DELETE FROM assets WHERE path = $1", &[&PATH])
287            .await
288            .unwrap();
289        delete.commit().await.unwrap();
290
291        assert_eq!(writer.await.unwrap(), (0, true));
292    }
293}
yggdrasil/api/assets/refs.rs

yggdrasil/api/assets/
refs.rs
