40 lines
1.2 KiB
SQL
40 lines
1.2 KiB
SQL
-- Remove migrated data (this will remove all articles and tags created from migration)
|
|
-- WARNING: This will delete all migrated data
|
|
|
|
-- Remove legacy migration records
|
|
DELETE
|
|
FROM legacy_migration
|
|
WHERE old_filter_type IN ('country', 'category');
|
|
|
|
-- Remove article-tag associations for migrated data (non-AI generated)
|
|
DELETE
|
|
FROM article_tags
|
|
WHERE ai_generated = 0;
|
|
|
|
-- Remove migrated geographic tags (only those created from country data)
|
|
DELETE
|
|
FROM tags
|
|
WHERE tags.category = 'geographic'
|
|
AND EXISTS (SELECT 1 FROM news WHERE news.country = tags.name);
|
|
|
|
-- Remove migrated content tags (only those created from category data)
|
|
DELETE
|
|
FROM tags
|
|
WHERE tags.category = 'content'
|
|
AND EXISTS (SELECT 1 FROM news WHERE news.category = tags.name);
|
|
|
|
-- Remove migrated articles (only those that match news entries)
|
|
DELETE
|
|
FROM articles
|
|
WHERE EXISTS (SELECT 1
|
|
FROM news
|
|
WHERE news.url = articles.url
|
|
AND news.title = articles.title
|
|
AND articles.source_type = 'rss');
|
|
|
|
-- Reset tag usage counts
|
|
UPDATE tags
|
|
SET usage_count = (SELECT COUNT(*)
|
|
FROM article_tags
|
|
WHERE tag_id = tags.id);
|