Something went wrong. Try again.
forked from tangled.org/core
Something went wrong. Try again.
1.4 kB · 66 lines
SQL
at ci
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667-- Validation Queries for Database Migration
-- 1. Verify Issues Table StructurePRAGMA table_info(issues);
-- 2. Verify Comments Table StructurePRAGMA table_info(comments);
-- 3. Check Total Row Count ConsistencySELECT 'Issues Row Count' AS check_type, (SELECT COUNT(*) FROM issues) AS row_countUNION ALLSELECT 'Comments Row Count' AS check_type, (SELECT COUNT(*) FROM comments) AS row_count;
-- 4. Verify Unique Constraint on IssuesSELECT repo_at, issue_id, COUNT(*) as duplicate_countFROM issuesGROUP BY repo_at, issue_idHAVING duplicate_count > 1;
-- 5. Verify Foreign Key Integrity for CommentsSELECT 'Orphaned Comments' AS check_type, COUNT(*) AS orphaned_countFROM comments cLEFT JOIN issues i ON c.repo_at = i.repo_at AND c.issue_id = i.issue_idWHERE i.id IS NULL;
-- 6. Check Foreign Key ConstraintPRAGMA foreign_key_list(comments);
-- 7. Sample Data Integrity CheckSELECT 'Sample Issues' AS check_type, repo_at, issue_id, title, createdFROM issuesLIMIT 5;
-- 8. Sample Comments Data Integrity CheckSELECT 'Sample Comments' AS check_type, repo_at, issue_id, comment_id, body, createdFROM commentsLIMIT 5;
-- 9. Verify Constraint on Comments (Issue ID and Comment ID Uniqueness)SELECT issue_id, comment_id, COUNT(*) as duplicate_countFROM commentsGROUP BY issue_id, comment_idHAVING duplicate_count > 1;