SPB Git

Repository Public

catalog.ts 14.0 KB 218 lines typescript

1import { sql } from "@/lib/db";2import { ApiError } from "@/lib/http";3import { norm } from "@/lib/text";45/** Read side of the library plus each account's own data (favourites, playlists, playback state). */67export interface Page { limit: number; offset: number }89const trackCols = (userId: string) => sql`10  t.id, t.title, t.track_no AS "trackNo", t.disc_no AS "discNo", t.duration_ms AS "durationMs", t.year,11  t.codec, t.container, t.sample_rate AS "sampleRate", t.bit_depth AS "bitDepth", t.channels, t.bitrate,12  t.web_playable AS "webPlayable", t.compat_state AS "compatState",13  json_build_object('id', ar.id, 'name', ar.name) AS artist,14  json_build_object('id', al.id, 'title', al.title, 'artworkId', al.artwork_id, 'artistName', aar.name) AS album,15  g.name AS genre,16  (SELECT ly.state FROM lyrics ly WHERE ly.track_id = t.id) AS "lyricsState",17  EXISTS (SELECT 1 FROM favorites f WHERE f.user_id = ${userId} AND f.track_id = t.id) AS favorite`;18const trackFrom = sql`19  FROM tracks t JOIN artists ar ON ar.id = t.artist_id JOIN albums al ON al.id = t.album_id20  JOIN artists aar ON aar.id = al.artist_id LEFT JOIN genres g ON g.id = t.genre_id`;2122const albumCols = (userId: string) => sql`23  al.id, al.title, al.year, al.artwork_id AS "artworkId", al.created_at AS "createdAt",24  json_build_object('id', aar.id, 'name', aar.name) AS artist, g.name AS genre,25  (SELECT count(*)::int FROM tracks t WHERE t.album_id = al.id) AS "trackCount",26  (SELECT coalesce(sum(t.duration_ms), 0)::bigint FROM tracks t WHERE t.album_id = al.id)::float8 AS "durationMs",27  EXISTS (SELECT 1 FROM favorite_albums f WHERE f.user_id = ${userId} AND f.album_id = al.id) AS favorite`;28const albumFrom = sql`FROM albums al JOIN artists aar ON aar.id = al.artist_id LEFT JOIN genres g ON g.id = al.genre_id`;2930const artistCols = sql`31  a.id, a.name,32  (SELECT count(*)::int FROM albums al WHERE al.artist_id = a.id) AS "albumCount",33  (SELECT count(*)::int FROM tracks t WHERE t.artist_id = a.id) AS "trackCount",34  (SELECT al.artwork_id FROM albums al WHERE al.artist_id = a.id AND al.artwork_id IS NOT NULL ORDER BY al.year DESC NULLS LAST LIMIT 1) AS "artworkId"`;3536export async function home(userId: string) {37  const [recentAlbums, artists, recentTracks, playlists, [counts]] = await Promise.all([38    sql`SELECT ${albumCols(userId)} ${albumFrom} ORDER BY al.created_at DESC, al.title LIMIT 12`,39    sql`SELECT ${artistCols} FROM artists a WHERE EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = a.id) OR EXISTS (SELECT 1 FROM albums al WHERE al.artist_id = a.id) ORDER BY a.name_norm LIMIT 12`,40    sql`SELECT ${trackCols(userId)} ${trackFrom} ORDER BY t.created_at DESC, al.title, t.disc_no, t.track_no LIMIT 12`,41    listPlaylists(userId),42    sql<{ tracks: number; albums: number; artists: number }[]>`SELECT (SELECT count(*)::int FROM tracks) AS tracks, (SELECT count(*)::int FROM albums) AS albums, (SELECT count(*)::int FROM artists) AS artists`,43  ]);44  return { recentAlbums, artists, recentTracks, playlists: playlists.slice(0, 8), counts };45}4647export async function listAlbums(userId: string, p: Page, sort: "recent" | "title" | "artist" | "year") {48  const order = sort === "recent" ? sql`al.created_at DESC, al.title_norm` : sort === "artist" ? sql`aar.name_norm, al.year NULLS LAST, al.title_norm` : sort === "year" ? sql`al.year DESC NULLS LAST, al.title_norm` : sql`al.title_norm`;49  const [items, [{ n }]] = await Promise.all([50    sql`SELECT ${albumCols(userId)} ${albumFrom} ORDER BY ${order} LIMIT ${p.limit} OFFSET ${p.offset}`,51    sql<{ n: number }[]>`SELECT count(*)::int AS n FROM albums`,52  ]);53  return { items, total: n, ...p };54}5556export async function listArtists(p: Page) {57  const [items, [{ n }]] = await Promise.all([58    sql`SELECT ${artistCols} FROM artists a ORDER BY a.name_norm LIMIT ${p.limit} OFFSET ${p.offset}`,59    sql<{ n: number }[]>`SELECT count(*)::int AS n FROM artists`,60  ]);61  return { items, total: n, ...p };62}6364export async function listTracks(userId: string, p: Page, sort: "title" | "recent" | "artist") {65  const order = sort === "recent" ? sql`t.created_at DESC, t.title_norm` : sort === "artist" ? sql`ar.name_norm, al.title_norm, t.disc_no, t.track_no` : sql`t.title_norm`;66  const [items, [{ n }]] = await Promise.all([67    sql`SELECT ${trackCols(userId)} ${trackFrom} ORDER BY ${order} LIMIT ${p.limit} OFFSET ${p.offset}`,68    sql<{ n: number }[]>`SELECT count(*)::int AS n FROM tracks`,69  ]);70  return { items, total: n, ...p };71}7273export async function getAlbum(userId: string, id: string) {74  const [album] = await sql`SELECT ${albumCols(userId)} ${albumFrom} WHERE al.id = ${id}`;75  if (!album) throw new ApiError(404, "not_found", "Album not found");76  const tracks = await sql`SELECT ${trackCols(userId)} ${trackFrom} WHERE t.album_id = ${id} ORDER BY t.disc_no, t.track_no NULLS LAST, t.title_norm`;77  return { album, tracks };78}7980export async function getArtist(userId: string, id: string) {81  const [artist] = await sql`SELECT ${artistCols} FROM artists a WHERE a.id = ${id}`;82  if (!artist) throw new ApiError(404, "not_found", "Artist not found");83  const [albums, tracks] = await Promise.all([84    sql`SELECT ${albumCols(userId)} ${albumFrom} WHERE al.artist_id = ${id} OR EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND t.artist_id = ${id}) ORDER BY al.year DESC NULLS LAST, al.title_norm`,85    sql`SELECT ${trackCols(userId)} ${trackFrom} WHERE t.artist_id = ${id} ORDER BY al.year DESC NULLS LAST, al.title_norm, t.disc_no, t.track_no NULLS LAST LIMIT 500`,86  ]);87  return { artist, albums, tracks };88}8990export async function getTracks(userId: string, ids: string[]) {91  if (ids.length === 0) return [];92  const rows = await sql<{ id: string }[]>`SELECT ${trackCols(userId)} ${trackFrom} WHERE t.id = ANY(${ids}::uuid[])`;93  const by = new Map(rows.map((r) => [r.id, r]));94  return ids.map((i) => by.get(i)).filter(Boolean);95}9697export async function search(userId: string, q: string) {98  const needle = norm(q);99  if (!needle) return { artists: [], albums: [], tracks: [] };100  const like = "%" + needle.replace(/[\\%_]/g, (c) => "\\" + c) + "%";101  const [artists, albums, tracks] = await Promise.all([102    sql`SELECT ${artistCols} FROM artists a WHERE a.name_norm LIKE ${like} ORDER BY similarity(a.name_norm, ${needle}) DESC, a.name_norm LIMIT 12`,103    sql`SELECT ${albumCols(userId)} ${albumFrom} WHERE al.title_norm LIKE ${like} OR aar.name_norm LIKE ${like} OR g.name_norm LIKE ${like}104        ORDER BY (al.title_norm LIKE ${like}) DESC, similarity(al.title_norm, ${needle}) DESC, al.title_norm LIMIT 18`,105    sql`SELECT ${trackCols(userId)} ${trackFrom} WHERE t.title_norm LIKE ${like} OR ar.name_norm LIKE ${like} OR al.title_norm LIKE ${like} OR g.name_norm LIKE ${like}106        ORDER BY (t.title_norm LIKE ${like}) DESC, similarity(t.title_norm, ${needle}) DESC, al.title_norm, t.disc_no, t.track_no LIMIT 50`,107  ]);108  return { artists, albums, tracks };109}110111// ── Favourites ──────────────────────────────────────────────────────────────────────────────────────112113export async function listFavorites(userId: string, p: Page) {114  const [tracks, albums, [{ n }]] = await Promise.all([115    sql`SELECT ${trackCols(userId)} ${trackFrom} JOIN favorites fv ON fv.track_id = t.id AND fv.user_id = ${userId} ORDER BY fv.created_at DESC LIMIT ${p.limit} OFFSET ${p.offset}`,116    sql`SELECT ${albumCols(userId)} ${albumFrom} JOIN favorite_albums fa ON fa.album_id = al.id AND fa.user_id = ${userId} ORDER BY fa.created_at DESC LIMIT 100`,117    sql<{ n: number }[]>`SELECT count(*)::int AS n FROM favorites WHERE user_id = ${userId}`,118  ]);119  return { tracks, albums, total: n, ...p };120}121122export async function setFavorite(userId: string, kind: "track" | "album", id: string, on: boolean) {123  try {124    if (kind === "track") {125      if (on) await sql`INSERT INTO favorites (user_id, track_id) VALUES (${userId}, ${id}) ON CONFLICT DO NOTHING`;126      else await sql`DELETE FROM favorites WHERE user_id = ${userId} AND track_id = ${id}`;127    } else if (on) await sql`INSERT INTO favorite_albums (user_id, album_id) VALUES (${userId}, ${id}) ON CONFLICT DO NOTHING`;128    else await sql`DELETE FROM favorite_albums WHERE user_id = ${userId} AND album_id = ${id}`;129  } catch (e) { if ((e as { code?: string }).code === "23503") throw new ApiError(404, "not_found"); throw e; }130  return { favorite: on };131}132133// ── Playlists (always scoped to their owner: another account's id answers 404) ──────────────────────134135export const listPlaylists = (userId: string) => sql`136  SELECT p.id, p.name, p.updated_at AS "updatedAt",137    (SELECT count(*)::int FROM playlist_tracks pt WHERE pt.playlist_id = p.id) AS "trackCount",138    (SELECT coalesce(array_agg(x.artwork_id), '{}') FROM (139       SELECT DISTINCT ON (al.artwork_id) al.artwork_id, pt.position FROM playlist_tracks pt JOIN tracks t ON t.id = pt.track_id JOIN albums al ON al.id = t.album_id140       WHERE pt.playlist_id = p.id AND al.artwork_id IS NOT NULL ORDER BY al.artwork_id, pt.position LIMIT 4) x) AS "artworkIds"141  FROM playlists p WHERE p.user_id = ${userId} ORDER BY p.updated_at DESC`;142143async function ownPlaylist(userId: string, id: string) {144  const [p] = await sql<{ id: string; name: string }[]>`SELECT id, name FROM playlists WHERE id = ${id} AND user_id = ${userId}`;145  if (!p) throw new ApiError(404, "not_found", "Playlist not found");146  return p;147}148149export async function createPlaylist(userId: string, name: string, trackIds: string[]) {150  const [{ n }] = await sql<{ n: number }[]>`SELECT count(*)::int AS n FROM playlists WHERE user_id = ${userId}`;151  if (n >= 500) throw new ApiError(409, "limit", "Too many playlists");152  const [p] = await sql<{ id: string }[]>`INSERT INTO playlists (user_id, name) VALUES (${userId}, ${name}) RETURNING id`;153  if (trackIds.length) await addToPlaylist(userId, p.id, trackIds);154  const [now] = await sql<{ updated_at: Date }[]>`SELECT updated_at FROM playlists WHERE id = ${p.id}`;155  return { id: p.id, updatedAt: now.updated_at };156}157158export async function getPlaylist(userId: string, id: string) {159  const p = await ownPlaylist(userId, id);160  const tracks = await sql`SELECT pt.id AS "entryId", ${trackCols(userId)} ${trackFrom} JOIN playlist_tracks pt ON pt.track_id = t.id WHERE pt.playlist_id = ${id} ORDER BY pt.position, pt.added_at`;161  return { playlist: p, tracks };162}163164export async function renamePlaylist(userId: string, id: string, name: string) {165  await ownPlaylist(userId, id);166  await sql`UPDATE playlists SET name = ${name}, updated_at = now() WHERE id = ${id}`;167}168169export async function deletePlaylist(userId: string, id: string) {170  await ownPlaylist(userId, id);171  await sql`DELETE FROM playlists WHERE id = ${id}`;172}173174export async function addToPlaylist(userId: string, id: string, trackIds: string[]) {175  await ownPlaylist(userId, id);176  const existing = await sql<{ id: string }[]>`SELECT id FROM tracks WHERE id = ANY(${trackIds}::uuid[])`;177  const known = new Set(existing.map((r) => r.id));178  const ids = trackIds.filter((t) => known.has(t));179  if (ids.length === 0) throw new ApiError(404, "not_found", "Track not found");180  await sql.begin(async (tx) => {181    const [{ max, n }] = await tx<{ max: number; n: number }[]>`SELECT coalesce(max(position), -1)::int AS max, count(*)::int AS n FROM playlist_tracks WHERE playlist_id = ${id}`;182    if (n + ids.length > 5000) throw new ApiError(409, "limit", "A playlist holds at most 5000 tracks");183    for (let i = 0; i < ids.length; i++) await tx`INSERT INTO playlist_tracks (playlist_id, track_id, position) VALUES (${id}, ${ids[i]}, ${max + 1 + i})`;184    await tx`UPDATE playlists SET updated_at = now() WHERE id = ${id}`;185  });186  return { added: ids.length };187}188189/** New order of the whole playlist, given as its entry ids. Entries left out are removed. */190export async function reorderPlaylist(userId: string, id: string, entryIds: string[]) {191  await ownPlaylist(userId, id);192  await sql.begin(async (tx) => {193    await tx`DELETE FROM playlist_tracks WHERE playlist_id = ${id} AND NOT (id = ANY(${entryIds}::uuid[]))`;194    for (let i = 0; i < entryIds.length; i++) await tx`UPDATE playlist_tracks SET position = ${i} WHERE id = ${entryIds[i]} AND playlist_id = ${id}`;195    await tx`UPDATE playlists SET updated_at = now() WHERE id = ${id}`;196  });197}198199// ── Playback state ──────────────────────────────────────────────────────────────────────────────────200201export async function getPlayback(userId: string) {202  const [s] = await sql<{ queue: string[]; queue_index: number; position_ms: number; shuffle: boolean; repeat: string; volume: number }[]>`203    SELECT queue, queue_index, position_ms, shuffle, repeat, volume FROM playback_state WHERE user_id = ${userId}`;204  if (!s) return { queue: [], index: 0, positionMs: 0, shuffle: false, repeat: "off", volume: 1 };205  // Tracks deleted since the state was saved are dropped; the index follows the surviving current track.206  const tracks = (await getTracks(userId, s.queue)) as { id: string }[];207  const currentId = s.queue[s.queue_index];208  const idx = tracks.findIndex((t) => t.id === currentId);209  return { queue: tracks, index: Math.max(0, idx), positionMs: idx >= 0 ? s.position_ms : 0, shuffle: s.shuffle, repeat: s.repeat, volume: s.volume };210}211212export async function savePlayback(userId: string, s: { queue: string[]; index: number; positionMs: number; shuffle: boolean; repeat: "off" | "all" | "one"; volume: number }) {213  await sql`INSERT INTO playback_state (user_id, queue, queue_index, position_ms, shuffle, repeat, volume, updated_at)214    VALUES (${userId}, ${s.queue}::uuid[], ${s.index}, ${s.positionMs}, ${s.shuffle}, ${s.repeat}, ${s.volume}, now())215    ON CONFLICT (user_id) DO UPDATE SET queue = EXCLUDED.queue, queue_index = EXCLUDED.queue_index, position_ms = EXCLUDED.position_ms,216      shuffle = EXCLUDED.shuffle, repeat = EXCLUDED.repeat, volume = EXCLUDED.volume, updated_at = now()`;217}218
Files main
Menu

Theme