DenozordecandCursor
f57b430052
Backfill миграция; чтение префиксов через snapshot; запись без дублирования rows. Co-authored-by: Cursor <cursoragent@cursor.com>
31 lines
1.2 KiB
SQL
31 lines
1.2 KiB
SQL
-- SQLite backfill: one snapshot per revision with materialized prefixes.
|
|
|
|
INSERT INTO prefix_snapshot (id, content_hash)
|
|
SELECT lower(hex(randomblob(16))),
|
|
substr(replace(cr.id, '-', '') || replace(cr.id, '-', ''), 1, 64)
|
|
FROM config_revision cr
|
|
WHERE cr.prefix_snapshot_id IS NULL
|
|
AND EXISTS (SELECT 1 FROM revision_materialized_prefix rmp WHERE rmp.revision_id = cr.id);
|
|
|
|
UPDATE config_revision
|
|
SET prefix_snapshot_id = (
|
|
SELECT ps.id FROM prefix_snapshot ps
|
|
WHERE ps.content_hash = substr(replace(config_revision.id, '-', '') || replace(config_revision.id, '-', ''), 1, 64)
|
|
)
|
|
WHERE prefix_snapshot_id IS NULL
|
|
AND EXISTS (SELECT 1 FROM revision_materialized_prefix rmp WHERE rmp.revision_id = config_revision.id);
|
|
|
|
INSERT INTO prefix_snapshot_row (snapshot_id, ord, prefix, community_id, source)
|
|
SELECT cr.prefix_snapshot_id,
|
|
(SELECT COUNT(*) FROM revision_materialized_prefix r2
|
|
WHERE r2.revision_id = cr.id AND r2.id <= rmp.id) - 1,
|
|
rmp.prefix,
|
|
rmp.community_id,
|
|
rmp.source
|
|
FROM config_revision cr
|
|
JOIN revision_materialized_prefix rmp ON rmp.revision_id = cr.id
|
|
WHERE cr.prefix_snapshot_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM prefix_snapshot_row psr WHERE psr.snapshot_id = cr.prefix_snapshot_id
|
|
);
|