45 lines
1.3 KiB
Python
45 lines
1.3 KiB
Python
"""Literal SQL for the FC-2d-vii-c artist backfill / artist-tag delete.
|
|
|
|
Intentionally pure string constants — NO model/slug imports, NO logic —
|
|
so migration 0008 and its test share one drift-proof source of truth.
|
|
Backfill steps are ordered primary -> provenance -> artist-tag and each
|
|
only touches rows still NULL (idempotent, first match wins). The
|
|
artist-tag step matches Artist.name = Tag.name: the importer always
|
|
created both from the same artist_name string.
|
|
"""
|
|
|
|
BACKFILL_PRIMARY_SQL = """
|
|
UPDATE image_record AS ir
|
|
SET artist_id = s.artist_id
|
|
FROM post p
|
|
JOIN source s ON s.id = p.source_id
|
|
WHERE ir.primary_post_id = p.id
|
|
AND ir.artist_id IS NULL
|
|
"""
|
|
|
|
BACKFILL_PROVENANCE_SQL = """
|
|
UPDATE image_record AS ir
|
|
SET artist_id = s.artist_id
|
|
FROM (
|
|
SELECT DISTINCT ON (ip.image_record_id)
|
|
ip.image_record_id, src.artist_id
|
|
FROM image_provenance ip
|
|
JOIN source src ON src.id = ip.source_id
|
|
ORDER BY ip.image_record_id, ip.id
|
|
) AS s
|
|
WHERE ir.id = s.image_record_id
|
|
AND ir.artist_id IS NULL
|
|
"""
|
|
|
|
BACKFILL_TAG_SQL = """
|
|
UPDATE image_record AS ir
|
|
SET artist_id = a.id
|
|
FROM image_tag it
|
|
JOIN tag t ON t.id = it.tag_id AND t.kind = 'artist'
|
|
JOIN artist a ON a.name = t.name
|
|
WHERE it.image_record_id = ir.id
|
|
AND ir.artist_id IS NULL
|
|
"""
|
|
|
|
DELETE_ARTIST_TAGS_SQL = "DELETE FROM tag WHERE kind = 'artist'"
|