85 lines
2.9 KiB
SQL
85 lines
2.9 KiB
SQL
|
|
-- Migrate data from old news table to new articles table
|
|
INSERT INTO articles (title, url, summary, published_at, added_at, source_type, processing_status)
|
|
SELECT title,
|
|
url,
|
|
summary,
|
|
published,
|
|
datetime(created_at, 'unixepoch'),
|
|
'rss',
|
|
CASE
|
|
WHEN summary IS NOT NULL AND summary != '' THEN 'completed'
|
|
ELSE 'pending'
|
|
END
|
|
FROM news;
|
|
|
|
-- Create geographic tags from existing country data
|
|
INSERT OR IGNORE INTO tags (name, category, description, usage_count)
|
|
SELECT DISTINCT country,
|
|
'geographic',
|
|
'Geographic location: ' || country,
|
|
COUNT(*)
|
|
FROM news
|
|
WHERE country IS NOT NULL
|
|
AND country != ''
|
|
GROUP BY country;
|
|
|
|
-- Link articles to their geographic tags
|
|
INSERT OR IGNORE INTO article_tags (article_id, tag_id, ai_generated, confidence_score)
|
|
SELECT a.id,
|
|
t.id,
|
|
0, -- Not AI generated, migrated from legacy data
|
|
1.0 -- Full confidence for existing data
|
|
FROM articles a
|
|
JOIN news n ON a.url = n.url AND a.title = n.title
|
|
JOIN tags t ON t.name = n.country AND t.category = 'geographic'
|
|
WHERE n.country IS NOT NULL
|
|
AND n.country != '';
|
|
|
|
-- Create category tags if category column exists in news table
|
|
INSERT OR IGNORE INTO tags (name, category, description, usage_count)
|
|
SELECT DISTINCT n.category,
|
|
'content',
|
|
'Content category: ' || n.category,
|
|
COUNT(*)
|
|
FROM news n
|
|
WHERE n.category IS NOT NULL
|
|
AND n.category != ''
|
|
GROUP BY n.category;
|
|
|
|
-- Link articles to their category tags
|
|
INSERT OR IGNORE INTO article_tags (article_id, tag_id, ai_generated, confidence_score)
|
|
SELECT a.id,
|
|
t.id,
|
|
0, -- Not AI generated, migrated from legacy data
|
|
1.0 -- Full confidence for existing data
|
|
FROM articles a
|
|
JOIN news n ON a.url = n.url AND a.title = n.title
|
|
JOIN tags t ON t.name = n.category AND t.category = 'content'
|
|
WHERE n.category IS NOT NULL
|
|
AND n.category != '';
|
|
|
|
-- Record migration in legacy_migration table for countries
|
|
INSERT INTO legacy_migration (old_filter_type, old_value, new_tag_ids)
|
|
SELECT 'country',
|
|
n.country,
|
|
'[' || GROUP_CONCAT(t.id) || ']'
|
|
FROM (SELECT DISTINCT country FROM news WHERE country IS NOT NULL AND country != '') n
|
|
JOIN tags t ON t.name = n.country AND t.category = 'geographic'
|
|
GROUP BY n.country;
|
|
|
|
-- Record migration in legacy_migration table for categories (if they exist)
|
|
INSERT INTO legacy_migration (old_filter_type, old_value, new_tag_ids)
|
|
SELECT 'category',
|
|
n.category,
|
|
'[' || GROUP_CONCAT(t.id) || ']'
|
|
FROM (SELECT DISTINCT category FROM news WHERE category IS NOT NULL AND category != '') n
|
|
JOIN tags t ON t.name = n.category AND t.category = 'content'
|
|
GROUP BY n.category;
|
|
|
|
-- Update tag usage counts
|
|
UPDATE tags
|
|
SET usage_count = (SELECT COUNT(*)
|
|
FROM article_tags
|
|
WHERE tag_id = tags.id);
|