koan_core/db/queries/
auth.rs

1//! Auth queries: user CRUD, refresh token management.
2
3use rusqlite::{Connection, OptionalExtension, params};
4
5use crate::auth::{self, Role};
6
7// ---------------------------------------------------------------------------
8// Row types
9// ---------------------------------------------------------------------------
10
11#[derive(Debug, Clone)]
12pub struct UserRow {
13    pub id: i64,
14    pub username: String,
15    pub password_hash: String,
16    pub role: Role,
17    pub created_at: Option<String>,
18}
19
20#[derive(Debug, Clone)]
21pub struct RefreshTokenRow {
22    pub id: String,
23    pub user_id: i64,
24    pub expires_at: i64,
25    pub revoked: bool,
26    pub created_at: Option<String>,
27    /// The OAuth grant the token descends from, and the client it was granted
28    /// to; `None` for app and web sessions.
29    pub grant: Option<OAuthGrant>,
30}
31
32#[derive(Debug, Clone, PartialEq, Eq)]
33pub struct OAuthGrant {
34    pub id: String,
35    pub client_name: String,
36}
37
38const TOKEN_COLUMNS: &str = "id, user_id, expires_at, revoked, created_at, grant_id, client_name";
39
40fn token_row(row: &rusqlite::Row) -> rusqlite::Result<RefreshTokenRow> {
41    let grant_id: Option<String> = row.get(5)?;
42    let client_name: Option<String> = row.get(6)?;
43    Ok(RefreshTokenRow {
44        id: row.get(0)?,
45        user_id: row.get(1)?,
46        expires_at: row.get(2)?,
47        revoked: row.get::<_, i32>(3)? != 0,
48        created_at: row.get(4)?,
49        grant: grant_id.map(|id| OAuthGrant {
50            id,
51            client_name: client_name.unwrap_or_default(),
52        }),
53    })
54}
55
56// ---------------------------------------------------------------------------
57// Whose data
58// ---------------------------------------------------------------------------
59
60/// The user a caller with no account acts as: the macOS app, the TUI, a server
61/// with auth disabled, the Subsonic shared secret.
62///
63/// Favourites, playlists and play history are per user. An install with no
64/// admin account keeps them under this id; once there is one, the first admin
65/// owns them and this id [resolves](resolve_user) to theirs, so a single-user
66/// server and a local library behave the same.
67pub const LOCAL_USER: i64 = 0;
68
69/// The first admin account, which answers for [`LOCAL_USER`].
70pub fn first_admin(conn: &Connection) -> Result<Option<i64>, rusqlite::Error> {
71    conn.prepare_cached("SELECT MIN(id) FROM users WHERE role = 'admin'")?
72        .query_row([], |r| r.get(0))
73}
74
75/// The id whose rows `user` reads and writes: `user` itself for an account,
76/// the first admin (or [`LOCAL_USER`] while there is none) for the implicit user.
77pub fn resolve_user(conn: &Connection, user: i64) -> Result<i64, rusqlite::Error> {
78    if user != LOCAL_USER {
79        return Ok(user);
80    }
81    Ok(first_admin(conn)?.unwrap_or(LOCAL_USER))
82}
83
84/// Whether `user` is the one [`LOCAL_USER`] resolves to: whose favourites and
85/// playlists this koan syncs with an upstream server.
86pub fn is_local_user(conn: &Connection, user: i64) -> Result<bool, rusqlite::Error> {
87    Ok(resolve_user(conn, user)? == resolve_user(conn, LOCAL_USER)?)
88}
89
90/// Hand the implicit user's rows to the first admin, once there is one.
91///
92/// Where a row would duplicate one the admin already has, theirs is kept.
93pub fn adopt_local_rows(conn: &Connection) -> Result<(), rusqlite::Error> {
94    let Some(admin) = first_admin(conn)? else {
95        return Ok(());
96    };
97    for table in [
98        "favourites",
99        "favourite_albums",
100        "favourite_artists",
101        "track_ratings",
102        "album_ratings",
103        "artist_ratings",
104        "bookmarks",
105        "play_history",
106        "play_history_forgotten",
107        "playlists",
108        "shares",
109    ] {
110        // Runs on every open: a read, so it takes no write lock when there is
111        // nothing to hand over.
112        let pending: bool = conn.query_row(
113            &format!("SELECT EXISTS(SELECT 1 FROM {table} WHERE user_id = ?1)"),
114            params![LOCAL_USER],
115            |r| r.get(0),
116        )?;
117        if !pending {
118            continue;
119        }
120        conn.execute(
121            &format!("UPDATE OR IGNORE {table} SET user_id = ?1 WHERE user_id = ?2"),
122            params![admin, LOCAL_USER],
123        )?;
124        conn.execute(
125            &format!("DELETE FROM {table} WHERE user_id = ?1"),
126            params![LOCAL_USER],
127        )?;
128    }
129    Ok(())
130}
131
132// ---------------------------------------------------------------------------
133// User CRUD
134// ---------------------------------------------------------------------------
135
136/// Create a new user. Returns the user ID.
137pub fn create_user(
138    conn: &Connection,
139    username: &str,
140    password: &str,
141    role: Role,
142) -> Result<i64, rusqlite::Error> {
143    let hash = auth::hash_password(password)
144        .map_err(|e| rusqlite::Error::ToSqlConversionFailure(e.into()))?;
145    conn.execute(
146        "INSERT INTO users (username, password_hash, role) VALUES (?1, ?2, ?3)",
147        params![username, hash, role.as_str()],
148    )?;
149    let id = conn.last_insert_rowid();
150    adopt_local_rows(conn)?;
151    Ok(id)
152}
153
154/// Get a user by username.
155pub fn get_user_by_username(
156    conn: &Connection,
157    username: &str,
158) -> Result<Option<UserRow>, rusqlite::Error> {
159    let mut stmt = conn.prepare_cached(
160        "SELECT id, username, password_hash, role, created_at FROM users WHERE username = ?1",
161    )?;
162    let mut rows = stmt.query_map(params![username], |row| {
163        let role_str: String = row.get(3)?;
164        Ok(UserRow {
165            id: row.get(0)?,
166            username: row.get(1)?,
167            password_hash: row.get(2)?,
168            role: role_str.parse().unwrap_or(Role::Readonly),
169            created_at: row.get(4)?,
170        })
171    })?;
172    match rows.next() {
173        Some(Ok(user)) => Ok(Some(user)),
174        Some(Err(e)) => Err(e),
175        None => Ok(None),
176    }
177}
178
179/// Get a user by ID.
180pub fn get_user_by_id(conn: &Connection, user_id: i64) -> Result<Option<UserRow>, rusqlite::Error> {
181    let mut stmt = conn
182        .prepare("SELECT id, username, password_hash, role, created_at FROM users WHERE id = ?1")?;
183    let mut rows = stmt.query_map(params![user_id], |row| {
184        let role_str: String = row.get(3)?;
185        Ok(UserRow {
186            id: row.get(0)?,
187            username: row.get(1)?,
188            password_hash: row.get(2)?,
189            role: role_str.parse().unwrap_or(Role::Readonly),
190            created_at: row.get(4)?,
191        })
192    })?;
193    match rows.next() {
194        Some(Ok(user)) => Ok(Some(user)),
195        Some(Err(e)) => Err(e),
196        None => Ok(None),
197    }
198}
199
200/// List all users (no password hashes).
201pub fn list_users(conn: &Connection) -> Result<Vec<UserRow>, rusqlite::Error> {
202    let mut stmt = conn
203        .prepare("SELECT id, username, password_hash, role, created_at FROM users ORDER BY id")?;
204    let rows = stmt.query_map([], |row| {
205        let role_str: String = row.get(3)?;
206        Ok(UserRow {
207            id: row.get(0)?,
208            username: row.get(1)?,
209            password_hash: row.get(2)?,
210            role: role_str.parse().unwrap_or(Role::Readonly),
211            created_at: row.get(4)?,
212        })
213    })?;
214    rows.collect()
215}
216
217/// Delete a user by ID. Returns true if a row was deleted.
218pub fn delete_user(conn: &Connection, user_id: i64) -> Result<bool, rusqlite::Error> {
219    let count = conn.execute("DELETE FROM users WHERE id = ?1", params![user_id])?;
220    if count > 0 {
221        auth::account_changed(user_id);
222    }
223    Ok(count > 0)
224}
225
226/// Update a user's password. Revokes all their refresh tokens and API keys: a
227/// reset is how an admin shuts out whoever else had the password, and a key
228/// made with it would otherwise outlast it.
229pub fn update_password(
230    conn: &Connection,
231    username: &str,
232    new_password: &str,
233) -> Result<bool, Box<dyn std::error::Error>> {
234    let hash = crate::auth::hash_password(new_password)?;
235    let updated = conn.execute(
236        "UPDATE users SET password_hash = ?1 WHERE username = ?2",
237        params![hash, username],
238    )?;
239    if updated > 0 {
240        // Revoke all existing tokens for this user.
241        if let Some(user) = get_user_by_username(conn, username)? {
242            revoke_all_user_tokens(conn, user.id)?;
243            super::api_keys::revoke_user_api_keys(conn, user.id)?;
244            super::app_passwords::revoke_user_app_passwords(conn, user.id)?;
245            auth::account_changed(user.id);
246        }
247    }
248    Ok(updated > 0)
249}
250
251/// Update a user's role.
252pub fn update_role(
253    conn: &Connection,
254    username: &str,
255    role: crate::auth::Role,
256) -> Result<bool, rusqlite::Error> {
257    let updated: Option<i64> = conn
258        .query_row(
259            "UPDATE users SET role = ?1 WHERE username = ?2 RETURNING id",
260            params![role.as_str(), username],
261            |row| row.get(0),
262        )
263        .optional()?;
264    adopt_local_rows(conn)?;
265    if let Some(id) = updated {
266        auth::account_changed(id);
267    }
268    Ok(updated.is_some())
269}
270
271/// Check if any users exist (for first-run detection).
272pub fn has_users(conn: &Connection) -> Result<bool, rusqlite::Error> {
273    let count: i64 = conn.query_row("SELECT COUNT(*) FROM users", [], |row| row.get(0))?;
274    Ok(count > 0)
275}
276
277/// Count users with admin role.
278pub fn admin_count(conn: &Connection) -> Result<i64, rusqlite::Error> {
279    conn.query_row(
280        "SELECT COUNT(*) FROM users WHERE role = 'admin'",
281        [],
282        |row| row.get(0),
283    )
284}
285
286// ---------------------------------------------------------------------------
287// Refresh tokens
288// ---------------------------------------------------------------------------
289
290/// Store a refresh token. Only `sha256(token)` is persisted — the raw token is
291/// a bearer credential and read access to the database must not yield one.
292pub fn store_refresh_token(
293    conn: &Connection,
294    token_id: &str,
295    user_id: i64,
296    expires_at: i64,
297) -> Result<(), rusqlite::Error> {
298    store_grant_token(conn, token_id, user_id, expires_at, None)
299}
300
301/// Store a refresh token belonging to an OAuth grant, or to none.
302pub fn store_grant_token(
303    conn: &Connection,
304    token_id: &str,
305    user_id: i64,
306    expires_at: i64,
307    grant: Option<&OAuthGrant>,
308) -> Result<(), rusqlite::Error> {
309    conn.execute(
310        "INSERT INTO refresh_tokens (id, user_id, expires_at, grant_id, client_name)
311         VALUES (?1, ?2, ?3, ?4, ?5)",
312        params![
313            auth::sha256_hex(token_id),
314            user_id,
315            expires_at,
316            grant.map(|g| &g.id),
317            grant.map(|g| &g.client_name),
318        ],
319    )?;
320    Ok(())
321}
322
323/// Look up a refresh token. Returns None if not found, expired, or revoked.
324pub fn get_valid_refresh_token(
325    conn: &Connection,
326    token_id: &str,
327) -> Result<Option<RefreshTokenRow>, rusqlite::Error> {
328    let now = auth::now_unix() as i64;
329    let mut stmt = conn.prepare_cached(&format!(
330        "SELECT {TOKEN_COLUMNS} FROM refresh_tokens
331         WHERE id = ?1 AND revoked = 0 AND expires_at > ?2"
332    ))?;
333    let mut rows = stmt.query_map(params![auth::sha256_hex(token_id), now], token_row)?;
334    match rows.next() {
335        Some(Ok(token)) => Ok(Some(token)),
336        Some(Err(e)) => Err(e),
337        None => Ok(None),
338    }
339}
340
341/// Atomically consume a valid refresh token: revoke it and return the row in one
342/// statement. Returns `None` if the token doesn't exist, is already revoked, or
343/// has expired. This prevents TOCTOU races in refresh-token rotation.
344pub fn consume_refresh_token(
345    conn: &Connection,
346    token_id: &str,
347) -> Result<Option<RefreshTokenRow>, rusqlite::Error> {
348    let now = auth::now_unix() as i64;
349    let mut stmt = conn.prepare(&format!(
350        "UPDATE refresh_tokens SET revoked = 1, used_at = ?2
351         WHERE id = ?1 AND revoked = 0 AND expires_at > ?2
352         RETURNING {TOKEN_COLUMNS}"
353    ))?;
354    let mut rows = stmt.query_map(params![auth::sha256_hex(token_id), now], token_row)?;
355    match rows.next() {
356        Some(Ok(token)) => Ok(Some(token)),
357        Some(Err(e)) => Err(e),
358        None => Ok(None),
359    }
360}
361
362/// Revoke a single refresh token (logout).
363pub fn revoke_refresh_token(conn: &Connection, token_id: &str) -> Result<bool, rusqlite::Error> {
364    let count = conn.execute(
365        "UPDATE refresh_tokens SET revoked = 1 WHERE id = ?1",
366        params![auth::sha256_hex(token_id)],
367    )?;
368    Ok(count > 0)
369}
370
371/// Revoke all refresh tokens for a user (password change, account delete).
372pub fn revoke_all_user_tokens(conn: &Connection, user_id: i64) -> Result<usize, rusqlite::Error> {
373    let count = conn.execute(
374        "UPDATE refresh_tokens SET revoked = 1 WHERE user_id = ?1 AND revoked = 0",
375        params![user_id],
376    )?;
377    Ok(count)
378}
379
380/// A refresh token of an OAuth grant spent once already, more than `grace_secs`
381/// ago, is a copy in someone else's hands: revoke the whole grant, so whichever
382/// side refreshed first loses it too. The grace covers a client retrying a
383/// refresh whose answer it never received. Returns how many tokens were revoked.
384///
385/// OAuth grants only: browser tabs and app tasks share one session and may
386/// race a refresh, which is not theft.
387pub fn revoke_replayed_grant(
388    conn: &Connection,
389    token_id: &str,
390    grace_secs: i64,
391) -> Result<usize, rusqlite::Error> {
392    let cutoff = auth::now_unix() as i64 - grace_secs;
393    conn.execute(
394        "UPDATE refresh_tokens SET revoked = 1
395         WHERE revoked = 0 AND grant_id = (
396           SELECT grant_id FROM refresh_tokens
397           WHERE id = ?1 AND revoked = 1 AND grant_id IS NOT NULL AND used_at < ?2)",
398        params![auth::sha256_hex(token_id), cutoff],
399    )
400}
401
402/// Revoke every refresh token of an OAuth grant.
403pub fn revoke_grant(conn: &Connection, grant_id: &str) -> Result<usize, rusqlite::Error> {
404    conn.execute(
405        "UPDATE refresh_tokens SET revoked = 1 WHERE grant_id = ?1 AND revoked = 0",
406        params![grant_id],
407    )
408}
409
410/// Clean up expired/revoked refresh tokens (housekeeping). A spent token of an
411/// OAuth grant stays until it would have expired, so `revoke_replayed_grant`
412/// can still recognise it.
413pub fn cleanup_expired_tokens(conn: &Connection) -> Result<usize, rusqlite::Error> {
414    let now = auth::now_unix() as i64;
415    let count = conn.execute(
416        "DELETE FROM refresh_tokens
417         WHERE expires_at <= ?1 OR (revoked = 1 AND (grant_id IS NULL OR used_at IS NULL))",
418        params![now],
419    )?;
420    Ok(count)
421}
422
423// ---------------------------------------------------------------------------
424// Tests
425// ---------------------------------------------------------------------------
426
427#[cfg(test)]
428mod tests {
429    use super::*;
430    use crate::db::connection::Database;
431    use tempfile::TempDir;
432
433    fn test_db() -> (Database, TempDir) {
434        let tmp = TempDir::new().unwrap();
435        let db_path = tmp.path().join("test.db");
436        let db = Database::open(&db_path).unwrap();
437        (db, tmp)
438    }
439
440    #[test]
441    fn create_and_get_user() {
442        let (db, _tmp) = test_db();
443        let id = create_user(&db.conn, "alice", "password123", Role::Admin).unwrap();
444        assert!(id > 0);
445
446        let user = get_user_by_username(&db.conn, "alice").unwrap().unwrap();
447        assert_eq!(user.username, "alice");
448        assert_eq!(user.role, Role::Admin);
449        assert!(user.password_hash.starts_with("$argon2"));
450    }
451
452    #[test]
453    fn duplicate_username_rejected() {
454        let (db, _tmp) = test_db();
455        create_user(&db.conn, "bob", "pass1", Role::User).unwrap();
456        let result = create_user(&db.conn, "bob", "pass2", Role::User);
457        assert!(result.is_err());
458    }
459
460    #[test]
461    fn list_and_delete_users() {
462        let (db, _tmp) = test_db();
463        let id1 = create_user(&db.conn, "user1", "pass", Role::Admin).unwrap();
464        create_user(&db.conn, "user2", "pass", Role::User).unwrap();
465
466        let users = list_users(&db.conn).unwrap();
467        assert_eq!(users.len(), 2);
468
469        assert!(delete_user(&db.conn, id1).unwrap());
470        let users = list_users(&db.conn).unwrap();
471        assert_eq!(users.len(), 1);
472        assert_eq!(users[0].username, "user2");
473    }
474
475    #[test]
476    fn has_users_empty_and_populated() {
477        let (db, _tmp) = test_db();
478        assert!(!has_users(&db.conn).unwrap());
479        create_user(&db.conn, "first", "pass", Role::Admin).unwrap();
480        assert!(has_users(&db.conn).unwrap());
481    }
482
483    #[test]
484    fn refresh_token_lifecycle() {
485        let (db, _tmp) = test_db();
486        let uid = create_user(&db.conn, "user", "pass", Role::User).unwrap();
487
488        let future_ts = auth::now_unix() as i64 + 86400;
489        store_refresh_token(&db.conn, "tok-123", uid, future_ts).unwrap();
490
491        // Valid lookup.
492        let tok = get_valid_refresh_token(&db.conn, "tok-123")
493            .unwrap()
494            .unwrap();
495        assert_eq!(tok.user_id, uid);
496
497        // Revoke.
498        assert!(revoke_refresh_token(&db.conn, "tok-123").unwrap());
499        assert!(
500            get_valid_refresh_token(&db.conn, "tok-123")
501                .unwrap()
502                .is_none()
503        );
504    }
505
506    #[test]
507    fn expired_token_not_returned() {
508        let (db, _tmp) = test_db();
509        let uid = create_user(&db.conn, "user", "pass", Role::User).unwrap();
510
511        // Already expired.
512        store_refresh_token(&db.conn, "tok-old", uid, 0).unwrap();
513        assert!(
514            get_valid_refresh_token(&db.conn, "tok-old")
515                .unwrap()
516                .is_none()
517        );
518    }
519
520    #[test]
521    fn a_spent_grant_token_coming_back_revokes_the_grant() {
522        let (db, _tmp) = test_db();
523        let uid = create_user(&db.conn, "user", "pass", Role::User).unwrap();
524        let future = auth::now_unix() as i64 + 86400;
525        let grant = OAuthGrant {
526            id: "g1".into(),
527            client_name: "Claude".into(),
528        };
529        store_grant_token(&db.conn, "first", uid, future, Some(&grant)).unwrap();
530        let spent = consume_refresh_token(&db.conn, "first").unwrap().unwrap();
531        assert_eq!(spent.grant.as_ref(), Some(&grant));
532        store_grant_token(&db.conn, "second", uid, future, Some(&grant)).unwrap();
533        // An app session, spent the same way, is not a grant.
534        store_refresh_token(&db.conn, "app", uid, future).unwrap();
535        consume_refresh_token(&db.conn, "app").unwrap().unwrap();
536
537        // Within the grace: a retry, nothing revoked.
538        assert_eq!(revoke_replayed_grant(&db.conn, "first", 30).unwrap(), 0);
539        assert_eq!(revoke_replayed_grant(&db.conn, "app", -1).unwrap(), 0);
540        // Spent and kept, so cleanup leaves it to be recognised.
541        cleanup_expired_tokens(&db.conn).unwrap();
542        assert_eq!(revoke_replayed_grant(&db.conn, "first", -1).unwrap(), 1);
543        assert!(
544            get_valid_refresh_token(&db.conn, "second")
545                .unwrap()
546                .is_none()
547        );
548    }
549
550    #[test]
551    fn cleanup_removes_expired_and_revoked() {
552        let (db, _tmp) = test_db();
553        let uid = create_user(&db.conn, "user", "pass", Role::User).unwrap();
554
555        let future = auth::now_unix() as i64 + 86400;
556        store_refresh_token(&db.conn, "active", uid, future).unwrap();
557        store_refresh_token(&db.conn, "expired", uid, 0).unwrap();
558        store_refresh_token(&db.conn, "revoked", uid, future).unwrap();
559        revoke_refresh_token(&db.conn, "revoked").unwrap();
560
561        let cleaned = cleanup_expired_tokens(&db.conn).unwrap();
562        assert_eq!(cleaned, 2);
563
564        // Active token still there.
565        assert!(
566            get_valid_refresh_token(&db.conn, "active")
567                .unwrap()
568                .is_some()
569        );
570    }
571
572    // -- Per-user data ------------------------------------------------------
573
574    use crate::db::queries::{self, sample_meta, upsert_track};
575
576    fn count(db: &Database, sql: &str) -> i64 {
577        db.conn.query_row(sql, [], |r| r.get(0)).unwrap()
578    }
579
580    #[test]
581    fn two_users_star_the_same_track_independently() {
582        let (db, _tmp) = test_db();
583        let admin = create_user(&db.conn, "owner", "pw", Role::Admin).unwrap();
584        let mate = create_user(&db.conn, "mate", "pw", Role::User).unwrap();
585        let track = upsert_track(&db.conn, &sample_meta("Scatology", "Coil", "Scatology")).unwrap();
586        let album = queries::get_track_row(&db.conn, track)
587            .unwrap()
588            .unwrap()
589            .album_id
590            .unwrap();
591
592        queries::add_favourite(&db.conn, admin, track).unwrap();
593        queries::add_favourite(&db.conn, mate, track).unwrap();
594        queries::remove_favourite(&db.conn, admin, track).unwrap();
595
596        assert!(
597            queries::load_favourites(&db.conn, admin)
598                .unwrap()
599                .is_empty()
600        );
601        assert!(
602            queries::load_favourites(&db.conn, mate)
603                .unwrap()
604                .contains(&track)
605        );
606        assert!(queries::toggle_favourite_album(&db.conn, mate, album).unwrap());
607        assert!(queries::toggle_favourite_album(&db.conn, admin, album).unwrap());
608        assert_eq!(count(&db, "SELECT COUNT(*) FROM favourite_albums"), 2);
609    }
610
611    #[test]
612    fn the_local_user_is_the_first_admin_once_there_is_one() {
613        let (db, _tmp) = test_db();
614        let track = upsert_track(&db.conn, &sample_meta("T", "A", "B")).unwrap();
615        queries::add_favourite(&db.conn, LOCAL_USER, track).unwrap();
616        queries::record_play(&db.conn, LOCAL_USER, track, None).unwrap();
617        let list = queries::create_playlist(&db.conn, LOCAL_USER, "Mine", None).unwrap();
618        assert_eq!(resolve_user(&db.conn, LOCAL_USER).unwrap(), LOCAL_USER);
619
620        create_user(&db.conn, "mate", "pw", Role::User).unwrap();
621        assert_eq!(resolve_user(&db.conn, LOCAL_USER).unwrap(), LOCAL_USER);
622        let admin = create_user(&db.conn, "owner", "pw", Role::Admin).unwrap();
623
624        assert_eq!(resolve_user(&db.conn, LOCAL_USER).unwrap(), admin);
625        assert!(
626            queries::load_favourites(&db.conn, admin)
627                .unwrap()
628                .contains(&track)
629        );
630        assert_eq!(queries::play_count(&db.conn, admin, track).unwrap(), 1);
631        assert_eq!(
632            queries::get_playlist(&db.conn, list)
633                .unwrap()
634                .unwrap()
635                .user_id,
636            admin
637        );
638        assert_eq!(
639            count(&db, "SELECT COUNT(*) FROM favourites WHERE user_id = 0"),
640            0
641        );
642    }
643
644    #[test]
645    fn playlists_are_the_owners_plus_everyones_public_ones() {
646        let (db, _tmp) = test_db();
647        let admin = create_user(&db.conn, "owner", "pw", Role::Admin).unwrap();
648        let mate = create_user(&db.conn, "mate", "pw", Role::User).unwrap();
649        let private = queries::create_playlist(&db.conn, admin, "Private", None).unwrap();
650        let public = queries::create_playlist(&db.conn, admin, "Public", None).unwrap();
651        db.conn
652            .execute("UPDATE playlists SET public = 1 WHERE id = ?1", [public])
653            .unwrap();
654        let own = queries::create_playlist(&db.conn, mate, "Mate's", None).unwrap();
655
656        let ids = |user| -> Vec<i64> {
657            let mut ids: Vec<i64> = queries::list_playlists(&db.conn, user)
658                .unwrap()
659                .into_iter()
660                .map(|p| p.id)
661                .collect();
662            ids.sort_unstable();
663            ids
664        };
665        assert_eq!(ids(mate), vec![public, own]);
666        assert_eq!(ids(admin), vec![private, public]);
667        // The implicit user is the first admin.
668        assert_eq!(ids(LOCAL_USER), vec![private, public]);
669
670        let row = queries::get_playlist(&db.conn, public).unwrap().unwrap();
671        assert!(row.readable_by(mate) && !row.editable_by(mate));
672        assert_eq!(row.owner.as_deref(), Some("owner"));
673        let row = queries::get_playlist(&db.conn, private).unwrap().unwrap();
674        assert!(!row.readable_by(mate));
675    }
676
677    #[test]
678    fn deleting_an_account_takes_its_data_with_it() {
679        let (db, _tmp) = test_db();
680        let admin = create_user(&db.conn, "owner", "pw", Role::Admin).unwrap();
681        let mate = create_user(&db.conn, "mate", "pw", Role::User).unwrap();
682        let track = upsert_track(&db.conn, &sample_meta("T", "A", "B")).unwrap();
683        let row = queries::get_track_row(&db.conn, track).unwrap().unwrap();
684        for user in [admin, mate] {
685            queries::add_favourite(&db.conn, user, track).unwrap();
686            queries::set_favourite_album(&db.conn, user, row.album_id.unwrap(), true).unwrap();
687            queries::set_favourite_artist(&db.conn, user, row.artist_id.unwrap(), true).unwrap();
688            for (kind, id) in [
689                (queries::RatingKind::Track, track),
690                (queries::RatingKind::Album, row.album_id.unwrap()),
691                (queries::RatingKind::Artist, row.artist_id.unwrap()),
692            ] {
693                queries::set_rating(&db.conn, user, kind, id, 4).unwrap();
694            }
695            queries::save_bookmark(&db.conn, user, track, 1_000, None).unwrap();
696            queries::record_play(&db.conn, user, track, None).unwrap();
697            queries::record_play_at(&db.conn, user, track, 1, None, queries::SOURCE_SUBSONIC)
698                .unwrap();
699            queries::forget_shared_plays(&db.conn, user, &[(track, 1)]).unwrap();
700            queries::create_playlist(&db.conn, user, "List", None).unwrap();
701            queries::shares::create_share(
702                &db.conn,
703                user,
704                queries::shares::Slice::TRACKS,
705                &[track],
706                None,
707                0,
708                None,
709            )
710            .unwrap();
711        }
712
713        assert!(delete_user(&db.conn, mate).unwrap());
714
715        for table in [
716            "favourites",
717            "favourite_albums",
718            "favourite_artists",
719            "track_ratings",
720            "album_ratings",
721            "artist_ratings",
722            "bookmarks",
723            "play_history",
724            "play_history_forgotten",
725            "playlists",
726            "shares",
727        ] {
728            assert_eq!(
729                count(
730                    &db,
731                    &format!("SELECT COUNT(*) FROM {table} WHERE user_id = {mate}")
732                ),
733                0,
734                "{table} kept the deleted account's rows"
735            );
736            assert_eq!(
737                count(
738                    &db,
739                    &format!("SELECT COUNT(*) FROM {table} WHERE user_id = {admin}")
740                ),
741                1,
742                "{table} lost another account's rows"
743            );
744        }
745    }
746}
koan_core/db/queries/auth.rs

koan_core/db/queries/
auth.rs
