sonda/migrations/sqlite/0014_artist_descriptions.sql
tgiachi 48cb903115 feat(about): artist descriptions from Wikipedia
- GET /artists/{id}/description shows what Wikipedia says about an artist: a short
  text with its page, a link and the licence (CC BY-SA 4.0).
- New provider "wikipedia" (off by default, no key, contact in the User-Agent, which
  Wikimedia requires). The way to the text is a chain of ids, never a name: the
  artist's MusicBrainz id, the Wikidata item MusicBrainz links to it, the Wikipedia
  page Wikidata names in the first configured language that has one. A page is used
  only if it is an article about that same item.
- [wikipedia] languages in the config (one to five codes of two or three lower case
  letters, checked before they are sent: an unknown site makes Wikidata answer with
  every language).
- Stored 30 days (a day when there was nothing to find), served stale if Wikipedia
  fails, removed with the provider's data (the purge now reports descriptions).
- MusicBrainz client: WikidataID. Wikipedia client for Wikidata and the summary,
  which also reads Wikidata's errors that come with HTTP 200.
- artist_descriptions table (migration 0014, SQLite and PostgreSQL).
- Spec, contract test, README and example config updated.

Closes #39
2026-11-11 21:36:27 +01:00

19 lines
1 KiB
SQL

-- What Wikipedia said about an artist, kept as it said it, with the page it came from.
-- The text is CC BY-SA 4.0: the page, its link and the language are what a reader needs
-- to give credit, so they are stored with it.
--
-- One row per artist. status 'none' records that the chain was followed and there was
-- nothing to show (no Wikidata link, or no usable page), so it is not followed again at
-- once. wikidata_id is the item MusicBrainz linked to the artist, or ''.
CREATE TABLE artist_descriptions (
artist_id INTEGER PRIMARY KEY REFERENCES artists(id) ON DELETE CASCADE,
status TEXT NOT NULL CHECK (status IN ('ok', 'none')),
wikidata_id TEXT NOT NULL DEFAULT '',
language TEXT NOT NULL DEFAULT '',
title TEXT NOT NULL DEFAULT '',
short TEXT NOT NULL DEFAULT '', -- one line, like "gruppo musicale inglese"
body TEXT NOT NULL DEFAULT '', -- the text
url TEXT NOT NULL DEFAULT '',
retrieved_at INTEGER NOT NULL -- unix seconds
);