SPB Git

Repository Public

003_native_clients.sql 5.3 KB 96 lines sql

1-- Native clients (SP Music for macOS): incremental library sync, device linking, listening history.2-- Nothing here changes what the web player reads or writes.34-- ── Incremental sync ─────────────────────────────────────────────────────────────────────────────────5-- Every library row carries a revision taken from one sequence. A client remembers the highest revision it6-- has applied and asks for what is above it; deletions are kept as tombstones.7--8-- Writers are serialised by a transaction-level advisory lock taken BEFORE the revision is drawn, so revisions9-- commit in increasing order: a reader's snapshot can never contain revision N+1 while N is still uncommitted.10CREATE SEQUENCE library_rev;1112ALTER TABLE artists ADD COLUMN rev bigint NOT NULL DEFAULT nextval('library_rev');13ALTER TABLE albums  ADD COLUMN rev bigint NOT NULL DEFAULT nextval('library_rev');14ALTER TABLE tracks  ADD COLUMN rev bigint NOT NULL DEFAULT nextval('library_rev');15CREATE INDEX artists_rev ON artists (rev);16CREATE INDEX albums_rev  ON albums (rev);17CREATE INDEX tracks_rev  ON tracks (rev);1819CREATE TABLE library_tombstones (20  kind       text NOT NULL CHECK (kind IN ('artist', 'album', 'track')),21  id         uuid NOT NULL,22  rev        bigint NOT NULL,23  deleted_at timestamptz NOT NULL DEFAULT now(),24  PRIMARY KEY (kind, id)25);26CREATE INDEX library_tombstones_rev ON library_tombstones (rev);2728CREATE FUNCTION library_next_rev() RETURNS bigint LANGUAGE plpgsql AS $$29BEGIN30  PERFORM pg_advisory_xact_lock(7741001);31  RETURN nextval('library_rev');32END $$;3334CREATE FUNCTION library_bump() RETURNS trigger LANGUAGE plpgsql AS $$35BEGIN36  NEW.rev := library_next_rev();37  RETURN NEW;38END $$;3940CREATE FUNCTION library_tombstone() RETURNS trigger LANGUAGE plpgsql AS $$41BEGIN42  INSERT INTO library_tombstones (kind, id, rev) VALUES (TG_ARGV[0], OLD.id, library_next_rev())43  ON CONFLICT (kind, id) DO UPDATE SET rev = EXCLUDED.rev, deleted_at = now();44  RETURN OLD;45END $$;4647-- A change to a track's files or lyrics is a change to the track as a client sees it.48CREATE FUNCTION library_touch_track() RETURNS trigger LANGUAGE plpgsql AS $$49DECLARE tid uuid;50BEGIN51  IF TG_OP = 'DELETE' THEN tid := OLD.track_id; ELSE tid := NEW.track_id; END IF;52  IF tid IS NOT NULL THEN UPDATE tracks SET rev = rev WHERE id = tid; END IF;   -- library_bump assigns the new revision53  RETURN NULL;54END $$;5556CREATE TRIGGER artists_rev BEFORE INSERT OR UPDATE ON artists FOR EACH ROW EXECUTE FUNCTION library_bump();57CREATE TRIGGER albums_rev  BEFORE INSERT OR UPDATE ON albums  FOR EACH ROW EXECUTE FUNCTION library_bump();58CREATE TRIGGER tracks_rev  BEFORE INSERT OR UPDATE ON tracks  FOR EACH ROW EXECUTE FUNCTION library_bump();59CREATE TRIGGER artists_gone AFTER DELETE ON artists FOR EACH ROW EXECUTE FUNCTION library_tombstone('artist');60CREATE TRIGGER albums_gone  AFTER DELETE ON albums  FOR EACH ROW EXECUTE FUNCTION library_tombstone('album');61CREATE TRIGGER tracks_gone  AFTER DELETE ON tracks  FOR EACH ROW EXECUTE FUNCTION library_tombstone('track');62CREATE TRIGGER media_assets_touch AFTER INSERT OR UPDATE OR DELETE ON media_assets FOR EACH ROW EXECUTE FUNCTION library_touch_track();63CREATE TRIGGER lyrics_touch AFTER INSERT OR UPDATE OR DELETE ON lyrics FOR EACH ROW EXECUTE FUNCTION library_touch_track();6465-- ── Device linking ───────────────────────────────────────────────────────────────────────────────────66-- A native app shows a short code; a signed-in browser approves it; the app then receives its own session.67-- Only hashes of the device secret are stored. Rows are short-lived (10 minutes) and single-use.68CREATE TABLE device_links (69  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),70  device_hash  text NOT NULL UNIQUE,71  user_code    text NOT NULL UNIQUE,72  device_name  text NOT NULL DEFAULT '',73  ip           text,74  created_at   timestamptz NOT NULL DEFAULT now(),75  expires_at   timestamptz NOT NULL,76  approved_by  uuid REFERENCES users(id) ON DELETE CASCADE,77  decided_at   timestamptz,78  denied       boolean NOT NULL DEFAULT false,79  consumed_at  timestamptz80);8182-- ── Listening history ────────────────────────────────────────────────────────────────────────────────83-- The id is generated by the client, so an event sent twice (retry after a lost answer) is stored once.84CREATE TABLE play_events (85  id          uuid PRIMARY KEY,86  user_id     uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,87  track_id    uuid NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,88  played_at   timestamptz NOT NULL,89  listened_ms integer NOT NULL DEFAULT 0 CHECK (listened_ms >= 0),90  completed   boolean NOT NULL DEFAULT false,91  source      text NOT NULL DEFAULT 'stream' CHECK (source IN ('stream', 'local')),92  client      text NOT NULL DEFAULT '',93  created_at  timestamptz NOT NULL DEFAULT now()94);95CREATE INDEX play_events_user ON play_events (user_id, played_at DESC);96
Files main
Menu

Theme