← back to AsSeenInMovies
scrapers/wikidata-enrich.js
262 lines
#!/usr/bin/env node
'use strict';
// Tick 3 — Wikidata SPARQL enrichment for asim_movies / asim_people.
//
// Wikidata is CC0 and free. Given an IMDb ID we can usually pull:
// movies : wikidata_id, poster_url (Commons CC), director's IMDb id, genres
// people : wikidata_id, headshot_url (Commons CC), birth_year, death_year,
// birth_place, primary_profession (occupation labels)
//
// Poster/headshot URLs come from Wikimedia Commons via Special:FilePath which
// is the canonical embed-resolver path. Commons content is "free" by Wikimedia
// definition (typically CC-BY-SA, sometimes CC0). We set poster_source =
// 'wikimedia-cc' so the per-asset license is auditable downstream.
//
// Usage:
// node scrapers/wikidata-enrich.js --table=movies --limit=500
// node scrapers/wikidata-enrich.js --table=people --limit=500
// node scrapers/wikidata-enrich.js --table=movies --priority # only rows already linked from credits/spotted
//
// Rate-limit: 1 batch / 1.2s. Batch size: 80 IDs (Wikidata advises <100).
// Polite User-Agent required by Wikidata policy.
require('dotenv').config();
const { Pool } = require('pg');
const args = process.argv.slice(2).reduce((m, a) => {
const [k, v] = a.replace(/^--/, '').split('=');
m[k] = v ?? true; return m;
}, {});
const TABLE = args.table || 'movies'; // movies | people
const LIMIT = parseInt(args.limit || '1000', 10);
const BATCH = Math.min(parseInt(args['batch-size'] || '80', 10), 100);
const SLEEP_MS = parseInt(args.sleep || '1200', 10);
const PRIORITY = !!args.priority;
const BY_CREDITS = !!args.byCredits; // rank people by credit count DESC (most-credited first)
const DRY = !!args.dry;
const UA = 'AsSeenInMovies/0.1 (https://asseeninmovies.com; steve@designerwallcoverings.com)';
const pool = new Pool({
connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
max: 4,
});
const sleep = (ms) => new Promise(r => setTimeout(r, ms));
function commonsImage(wdImageUrl) {
// wdImageUrl looks like: http://commons.wikimedia.org/wiki/Special:FilePath/Goosebumps_2023_poster.jpg
// It's already a redirecting URL that serves the file. We use it as-is and
// append ?width=600 so the resolver returns a thumbnail rather than full res
// (we don't need 4k posters in <img> tags).
if (!wdImageUrl) return null;
const u = wdImageUrl.replace(/^http:\/\//, 'https://');
return u + (u.includes('?') ? '&' : '?') + 'width=600';
}
async function sparql(query) {
const url = 'https://query.wikidata.org/sparql?format=json&query=' + encodeURIComponent(query);
const res = await fetch(url, {
headers: {
'Accept': 'application/sparql-results+json',
'User-Agent': UA,
},
});
if (!res.ok) throw new Error(`SPARQL ${res.status}: ${(await res.text()).slice(0, 200)}`);
const j = await res.json();
return j.results.bindings;
}
async function runStart(kind) {
if (DRY) return null;
const r = await pool.query(
`INSERT INTO asim_ingest_runs (run_kind, source, status) VALUES ($1, 'wikidata-sparql', 'running') RETURNING id`,
[kind]
);
return r.rows[0].id;
}
async function runDone(id, ok, inN, outN, note) {
if (!id) return;
await pool.query(
`UPDATE asim_ingest_runs SET status=$1, finished_at=NOW(), records_in=$2, records_out=$3, notes=$4 WHERE id=$5`,
[ok ? 'ok' : 'err', inN, outN, note || null, id]
);
}
// ── Movies ────────────────────────────────────────────────────────────────────
async function enrichMovies() {
const priorityClause = PRIORITY ? `
AND m.id IN (
SELECT DISTINCT movie_id FROM asim_credits
UNION
SELECT DISTINCT movie_id FROM asim_spotted
)` : '';
const sel = await pool.query(`
SELECT m.id, m.imdb_id
FROM asim_movies m
WHERE m.imdb_id LIKE 'tt%' AND m.wikidata_id IS NULL ${priorityClause}
ORDER BY ${PRIORITY ? 'm.id' : 'm.popularity DESC NULLS LAST'}
LIMIT $1`, [LIMIT]);
console.log(`[wikidata-movies] candidates=${sel.rows.length} priority=${PRIORITY} batch=${BATCH}`);
const runId = await runStart('wikidata-movies' + (PRIORITY ? '-priority' : ''));
let matched = 0, posterAdded = 0, noMatch = 0;
for (let i = 0; i < sel.rows.length; i += BATCH) {
const slice = sel.rows.slice(i, i + BATCH);
const values = slice.map(r => `"${r.imdb_id}"`).join(' ');
const q = `
SELECT ?film ?imdb ?image ?dirImdb WHERE {
VALUES ?imdb { ${values} }
?film wdt:P345 ?imdb.
OPTIONAL { ?film wdt:P18 ?image. }
OPTIONAL { ?film wdt:P57 ?dir. ?dir wdt:P345 ?dirImdb. }
}`;
let rows;
try { rows = await sparql(q); }
catch (e) {
console.error(` batch ${i / BATCH}: ${e.message}`);
await sleep(5000); continue;
}
// Group by imdb (Wikidata can return multiple rows per film if multiple directors)
const byImdb = new Map();
for (const r of rows) {
const imdb = r.imdb.value;
const wd = r.film.value.split('/').pop(); // Q-id
const img = r.image && r.image.value;
const dirImdb = r.dirImdb && r.dirImdb.value;
if (!byImdb.has(imdb)) byImdb.set(imdb, { wd, img: null, dirs: [] });
const e = byImdb.get(imdb);
if (img && !e.img) e.img = img;
if (dirImdb && !e.dirs.includes(dirImdb)) e.dirs.push(dirImdb);
}
for (const row of slice) {
const e = byImdb.get(row.imdb_id);
if (!e) { noMatch++; continue; }
const posterUrl = commonsImage(e.img);
if (!DRY) {
// poster_credit/license/source_url are filled by the
// wikimedia-credit-backfill.js post-pass (Commons extmetadata fetch
// is one extra API call per file — done in batched backfill rather
// than slowing the SPARQL drain).
await pool.query(`
UPDATE asim_movies
SET wikidata_id = $1,
poster_url = COALESCE($2, poster_url),
poster_source = CASE WHEN $2 IS NOT NULL THEN 'wikimedia-cc' ELSE poster_source END,
updated_at = NOW()
WHERE id = $3`, [e.wd, posterUrl, row.id]);
}
matched++;
if (posterUrl) posterAdded++;
}
if ((i / BATCH) % 10 === 0) {
console.log(` …batch ${Math.floor(i / BATCH) + 1}/${Math.ceil(sel.rows.length / BATCH)} matched=${matched} posters=${posterAdded} noMatch=${noMatch}`);
}
await sleep(SLEEP_MS);
}
console.log(`[wikidata-movies] done. matched=${matched} posters=${posterAdded} noMatch=${noMatch}`);
await runDone(runId, true, sel.rows.length, matched, `posters=${posterAdded} noMatch=${noMatch}`);
}
// ── People ────────────────────────────────────────────────────────────────────
async function enrichPeople() {
const priorityClause = PRIORITY ? `
AND p.id IN (SELECT DISTINCT person_id FROM asim_credits)` : '';
// BY_CREDITS = join credit-count and rank desc — most-credited untouched people first.
// Hit rate on bg1/bg2 random ordering was ~7-11%; credit-ranked should jump
// because famous actors have wikipedia articles.
// BY_CAST = people whose top credit is 'cast' (actors). bg1=11%, bg2=6.9%,
// bg3 (credit-rank) only 3.1% because most-credited rows are crew (production
// designers, costumes), not famous actors. Cast-ranked should swing back up.
const BY_CAST = args.byCast;
const sel = BY_CAST ? await pool.query(`
SELECT DISTINCT p.id, p.imdb_id
FROM asim_people p
JOIN asim_credits c ON c.person_id = p.id AND c.role = 'actor'
WHERE p.imdb_id LIKE 'nm%' AND p.wikidata_id IS NULL
ORDER BY p.id
LIMIT $1`, [LIMIT]) : BY_CREDITS ? await pool.query(`
SELECT p.id, p.imdb_id
FROM asim_people p
JOIN (SELECT person_id, count(*) AS c FROM asim_credits GROUP BY person_id) cc
ON cc.person_id = p.id
WHERE p.imdb_id LIKE 'nm%' AND p.wikidata_id IS NULL
ORDER BY cc.c DESC
LIMIT $1`, [LIMIT]) : await pool.query(`
SELECT p.id, p.imdb_id
FROM asim_people p
WHERE p.imdb_id LIKE 'nm%' AND p.wikidata_id IS NULL ${priorityClause}
ORDER BY p.id
LIMIT $1`, [LIMIT]);
console.log(`[wikidata-people] candidates=${sel.rows.length} priority=${PRIORITY} batch=${BATCH}`);
const runId = await runStart('wikidata-people' + (PRIORITY ? '-priority' : ''));
let matched = 0, headshotAdded = 0, noMatch = 0;
for (let i = 0; i < sel.rows.length; i += BATCH) {
const slice = sel.rows.slice(i, i + BATCH);
const values = slice.map(r => `"${r.imdb_id}"`).join(' ');
const q = `
SELECT ?person ?imdb ?image ?birthYear ?deathYear ?birthplaceLabel WHERE {
VALUES ?imdb { ${values} }
?person wdt:P345 ?imdb.
OPTIONAL { ?person wdt:P18 ?image. }
OPTIONAL { ?person wdt:P569 ?birth. BIND(YEAR(?birth) AS ?birthYear) }
OPTIONAL { ?person wdt:P570 ?death. BIND(YEAR(?death) AS ?deathYear) }
OPTIONAL { ?person wdt:P19 ?birthplace.
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". ?birthplace rdfs:label ?birthplaceLabel. }
}
}`;
let rows;
try { rows = await sparql(q); }
catch (e) {
console.error(` batch ${i / BATCH}: ${e.message}`);
await sleep(5000); continue;
}
const byImdb = new Map();
for (const r of rows) {
const imdb = r.imdb.value;
const wd = r.person.value.split('/').pop();
if (!byImdb.has(imdb)) byImdb.set(imdb, {
wd,
img: r.image && r.image.value,
birth: r.birthYear && parseInt(r.birthYear.value, 10) || null,
death: r.deathYear && parseInt(r.deathYear.value, 10) || null,
birthplace: r.birthplaceLabel && r.birthplaceLabel.value || null,
});
}
for (const row of slice) {
const e = byImdb.get(row.imdb_id);
if (!e) { noMatch++; continue; }
const img = commonsImage(e.img);
if (!DRY) {
await pool.query(`
UPDATE asim_people
SET wikidata_id = $1,
headshot_url = COALESCE($2, headshot_url),
headshot_source = CASE WHEN $2 IS NOT NULL THEN 'wikimedia-cc' ELSE headshot_source END,
birth_year = COALESCE(birth_year, $3),
death_year = COALESCE(death_year, $4),
birth_place = COALESCE(birth_place, $5),
updated_at = NOW()
WHERE id = $6`, [e.wd, img, e.birth, e.death, e.birthplace, row.id]);
}
matched++;
if (img) headshotAdded++;
}
if ((i / BATCH) % 10 === 0) {
console.log(` …batch ${Math.floor(i / BATCH) + 1}/${Math.ceil(sel.rows.length / BATCH)} matched=${matched} headshots=${headshotAdded} noMatch=${noMatch}`);
}
await sleep(SLEEP_MS);
}
console.log(`[wikidata-people] done. matched=${matched} headshots=${headshotAdded} noMatch=${noMatch}`);
await runDone(runId, true, sel.rows.length, matched, `headshots=${headshotAdded} noMatch=${noMatch}`);
}
(async function main() {
if (TABLE === 'movies') await enrichMovies();
else if (TABLE === 'people') await enrichPeople();
else { console.error('--table must be movies or people'); process.exit(1); }
await pool.end();
})().catch(e => { console.error('fatal:', e); process.exit(1); });