kot.pink
1-- Validation Queries for Database Migration
2
3-- 1. Verify Issues Table Structure
4PRAGMA table_info(issues);
5
6-- 2. Verify Comments Table Structure
7PRAGMA table_info(comments);
8
9-- 3. Check Total Row Count Consistency
10SELECT
11 'Issues Row Count' AS check_type,
12 (SELECT COUNT(*) FROM issues) AS row_count
13UNION ALL
14SELECT
15 'Comments Row Count' AS check_type,
16 (SELECT COUNT(*) FROM comments) AS row_count;
17
18-- 4. Verify Unique Constraint on Issues
19SELECT
20 repo_at,
21 issue_id,
22 COUNT(*) as duplicate_count
23FROM issues
24GROUP BY repo_at, issue_id
25HAVING duplicate_count > 1;
26
27-- 5. Verify Foreign Key Integrity for Comments
28SELECT
29 'Orphaned Comments' AS check_type,
30 COUNT(*) AS orphaned_count
31FROM comments c
32LEFT JOIN issues i ON c.repo_at = i.repo_at AND c.issue_id = i.issue_id
33WHERE i.id IS NULL;
34
35-- 6. Check Foreign Key Constraint
36PRAGMA foreign_key_list(comments);
37
38-- 7. Sample Data Integrity Check
39SELECT
40 'Sample Issues' AS check_type,
41 repo_at,
42 issue_id,
43 title,
44 created
45FROM issues
46LIMIT 5;
47
48-- 8. Sample Comments Data Integrity Check
49SELECT
50 'Sample Comments' AS check_type,
51 repo_at,
52 issue_id,
53 comment_id,
54 body,
55 created
56FROM comments
57LIMIT 5;
58
59-- 9. Verify Constraint on Comments (Issue ID and Comment ID Uniqueness)
60SELECT
61 issue_id,
62 comment_id,
63 COUNT(*) as duplicate_count
64FROM comments
65GROUP BY issue_id, comment_id
66HAVING duplicate_count > 1;