SPB Git

Repository Public

sync.ts 7.9 KB 110 lines typescript

1import { sql } from "@/lib/db";2import { ApiError } from "@/lib/http";34/**5 * Read side for native clients that keep their own copy of the catalogue (SP Music for macOS).6 *7 * `librarySync(since)` returns every artist, album and track whose revision is above `since`, plus the tombstones8 * of what was deleted. All four reads share one snapshot; revisions commit in increasing order (see migration 003),9 * so the highest revision in the answer is a safe cursor for the next call.10 */1112const PAGE = 4000;1314export async function librarySync(since: number) {15  return sql.begin("isolation level repeatable read read only", async (tx) => {16    const [artists, albums, tracks, deleted, [counts]] = await Promise.all([17      tx<{ rev: number }[]>`SELECT a.id, a.name, a.rev::float8 AS rev FROM artists a WHERE a.rev > ${since} ORDER BY a.rev LIMIT ${PAGE}`,18      tx<{ rev: number }[]>`19        SELECT al.id, al.title, al.year, al.artwork_id AS "artworkId", al.artist_id AS "artistId", g.name AS genre, al.created_at AS "createdAt", al.rev::float8 AS rev20        FROM albums al LEFT JOIN genres g ON g.id = al.genre_id WHERE al.rev > ${since} ORDER BY al.rev LIMIT ${PAGE}`,21      tx<{ rev: number }[]>`22        SELECT t.id, t.title, t.track_no AS "trackNo", t.disc_no AS "discNo", t.duration_ms AS "durationMs", t.year,23          t.codec, t.container, t.sample_rate AS "sampleRate", t.bit_depth AS "bitDepth", t.channels, t.bitrate,24          t.web_playable AS "webPlayable", t.compat_state AS "compatState", t.artist_id AS "artistId", t.album_id AS "albumId", g.name AS genre,25          (SELECT ly.state FROM lyrics ly WHERE ly.track_id = t.id) AS "lyricsState", t.created_at AS "createdAt",26          (SELECT json_build_object('assetId', m.id, 'sha256', m.sha256, 'sizeBytes', m.size_bytes, 'mime', m.mime) FROM media_assets m WHERE m.track_id = t.id AND m.kind = 'original') AS file,27          (SELECT json_build_object('assetId', m.id, 'sizeBytes', m.size_bytes, 'mime', m.mime) FROM media_assets m WHERE m.track_id = t.id AND m.kind = 'rendition') AS compat,28          t.rev::float8 AS rev29        FROM tracks t LEFT JOIN genres g ON g.id = t.genre_id WHERE t.rev > ${since} ORDER BY t.rev LIMIT ${PAGE}`,30      tx<{ rev: number }[]>`SELECT kind, id, rev::float8 AS rev FROM library_tombstones WHERE rev > ${since} ORDER BY rev LIMIT ${PAGE}`,31      tx<{ artists: number; albums: number; tracks: number }[]>`SELECT (SELECT count(*)::int FROM artists) AS artists, (SELECT count(*)::int FROM albums) AS albums, (SELECT count(*)::int FROM tracks) AS tracks`,32    ]);33    // A truncated list ends at its last revision: the cursor may not go past the lowest such end, or rows would be skipped.34    const lists = [artists, albums, tracks, deleted];35    const truncated = lists.filter((l) => l.length === PAGE);36    const more = truncated.length > 0;37    const top = (l: { rev: number }[]) => (l.length ? l[l.length - 1].rev : since);38    const rev = more ? Math.min(...truncated.map(top)) : Math.max(since, ...lists.map(top));39    const keep = <T extends { rev: number }>(l: T[]) => (more ? l.filter((r) => r.rev <= rev) : l);40    return { since, rev, more, artists: keep(artists), albums: keep(albums), tracks: keep(tracks), deleted: keep(deleted), counts };41  });42}4344/** Everything that belongs to one account, in one answer: small by nature, so always sent whole. */45export async function userSync(userId: string) {46  const [playlists, entries, favTracks, favAlbums, [pb]] = await Promise.all([47    sql<{ id: string }[]>`SELECT id, name, created_at AS "createdAt", updated_at AS "updatedAt" FROM playlists WHERE user_id = ${userId} ORDER BY updated_at DESC`,48    sql<{ playlistId: string }[]>`49      SELECT pt.playlist_id AS "playlistId", pt.id AS "entryId", pt.track_id AS "trackId", pt.position, pt.added_at AS "addedAt"50      FROM playlist_tracks pt JOIN playlists p ON p.id = pt.playlist_id WHERE p.user_id = ${userId} ORDER BY pt.playlist_id, pt.position, pt.added_at`,51    sql`SELECT track_id AS id, created_at AS "createdAt" FROM favorites WHERE user_id = ${userId} ORDER BY created_at DESC`,52    sql`SELECT album_id AS id, created_at AS "createdAt" FROM favorite_albums WHERE user_id = ${userId} ORDER BY created_at DESC`,53    sql<{ queue: string[]; queue_index: number; position_ms: number; shuffle: boolean; repeat: string; volume: number; updated_at: Date }[]>`54      SELECT queue, queue_index, position_ms, shuffle, repeat, volume, updated_at FROM playback_state WHERE user_id = ${userId}`,55  ]);56  const by = new Map<string, unknown[]>();57  for (const e of entries) { const { playlistId, ...rest } = e; (by.get(playlistId) ?? by.set(playlistId, []).get(playlistId)!).push(rest); }58  return {59    playlists: playlists.map((p) => ({ ...p, entries: by.get(p.id) ?? [] })),60    favoriteTracks: favTracks, favoriteAlbums: favAlbums,61    playback: pb ? { queue: pb.queue, index: pb.queue_index, positionMs: pb.position_ms, shuffle: pb.shuffle, repeat: pb.repeat, volume: pb.volume, updatedAt: pb.updated_at } : null,62  };63}6465// ── Listening history ───────────────────────────────────────────────────────────────────────────────6667export interface PlayEvent { id: string; trackId: string; playedAt: string; listenedMs: number; completed: boolean; source: "stream" | "local" }6869/** Idempotent: an event already stored (same client-generated id) or naming a deleted track is skipped, not refused. */70export async function recordPlays(userId: string, client: string, events: PlayEvent[]) {71  if (events.length === 0) return { stored: 0, skipped: 0 };72  const known = new Set((await sql<{ id: string }[]>`SELECT id FROM tracks WHERE id = ANY(${events.map((e) => e.trackId)}::uuid[])`).map((r) => r.id));73  const now = Date.now();74  let stored = 0;75  for (const e of events) {76    if (!known.has(e.trackId)) continue;77    const at = new Date(e.playedAt);78    if (Number.isNaN(at.getTime()) || at.getTime() > now + 86_400_000) continue;       // a clock far in the future is not history79    const r = await sql`INSERT INTO play_events (id, user_id, track_id, played_at, listened_ms, completed, source, client)80      VALUES (${e.id}, ${userId}, ${e.trackId}, ${at}, ${e.listenedMs}, ${e.completed}, ${e.source}, ${client.slice(0, 120)}) ON CONFLICT (id) DO NOTHING RETURNING id`;81    stored += r.length;82  }83  return { stored, skipped: events.length - stored };84}8586export async function listPlays(userId: string, p: { limit: number; offset: number }) {87  const [items, [{ n }]] = await Promise.all([88    sql`SELECT id, track_id AS "trackId", played_at AS "playedAt", listened_ms AS "listenedMs", completed, source89        FROM play_events WHERE user_id = ${userId} ORDER BY played_at DESC, id LIMIT ${p.limit} OFFSET ${p.offset}`,90    sql<{ n: number }[]>`SELECT count(*)::int AS n FROM play_events WHERE user_id = ${userId}`,91  ]);92  return { items, total: n, ...p };93}9495/** Optimistic concurrency for playlist edits made offline: refuse when the list changed after the client's copy. */96export async function assertPlaylistBase(userId: string, id: string, base: string | undefined) {97  if (!base) return;98  const at = new Date(base);99  if (Number.isNaN(at.getTime())) throw new ApiError(400, "invalid_input", "baseUpdatedAt is not a date");100  const [p] = await sql<{ updated_at: Date }[]>`SELECT updated_at FROM playlists WHERE id = ${id} AND user_id = ${userId}`;101  if (!p) throw new ApiError(404, "not_found", "Playlist not found");102  // The client holds millisecond precision (JSON dates); compare at that precision.103  if (Math.floor(p.updated_at.getTime()) > at.getTime()) throw new ApiError(409, "conflict", "The playlist was changed elsewhere", { updatedAt: p.updated_at.toISOString() });104}105106export async function playlistStamp(userId: string, id: string) {107  const [p] = await sql<{ updated_at: Date }[]>`SELECT updated_at FROM playlists WHERE id = ${id} AND user_id = ${userId}`;108  return { ok: true, updatedAt: p?.updated_at ?? null };109}110
Files main
Menu

Theme