Spaces:
Running
Running
File size: 4,727 Bytes
69be42f |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
"""Database schema verification script.
[Task]: T022, T023
[From]: specs/001-user-auth/tasks.md
This script verifies that the database schema is correct for authentication.
"""
import sys
from pathlib import Path
# Add parent directory to path for imports
sys.path.insert(0, str(Path(__file__).parent.parent))
from sqlmodel import Session, select, text
from core.config import engine
from models.user import User
from models.task import Task
def verify_schema():
"""Verify database schema for authentication."""
print("🔍 Verifying database schema...\n")
with Session(engine) as session:
# Check users table
print("📋 Checking users table...")
try:
result = session.exec(text("""
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
"""))
print("✅ Users table columns:")
for row in result:
print(f" - {row.column_name}: {row.data_type}")
except Exception as e:
print(f"❌ Error checking users table: {e}")
return False
print()
# Check tasks table
print("📋 Checking tasks table...")
try:
result = session.exec(text("""
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'tasks'
ORDER BY ordinal_position;
"""))
print("✅ Tasks table columns:")
for row in result:
print(f" - {row.column_name}: {row.data_type}")
except Exception as e:
print(f"❌ Error checking tasks table: {e}")
return False
print()
# Check indexes
print("📋 Checking indexes on tasks table...")
try:
result = session.exec(text("""
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'tasks';
"""))
print("✅ Indexes on tasks table:")
for row in result:
print(f" - {row.indexname}")
except Exception as e:
print(f"❌ Error checking indexes: {e}")
return False
print()
# Check foreign key constraints
print("📋 Checking foreign key constraints...")
try:
result = session.exec(text("""
SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name = 'tasks';
"""))
print("✅ Foreign key constraints:")
for row in result:
print(f" - {row.constraint_name}:")
print(f" {row.column_name} → {row.foreign_table_name}.{row.foreign_column_name}")
except Exception as e:
print(f"❌ Error checking foreign keys: {e}")
return False
print()
# Check unique constraints
print("📋 Checking unique constraints...")
try:
result = session.exec(text("""
SELECT
tc.constraint_name,
tc.table_name,
kcu.column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
WHERE tc.constraint_type = 'UNIQUE'
AND tc.table_name = 'users';
"""))
print("✅ Unique constraints on users table:")
for row in result:
print(f" - {row.constraint_name}: {row.column_name}")
except Exception as e:
print(f"❌ Error checking unique constraints: {e}")
return False
print("\n✅ Schema verification complete!")
print("\n🎉 Database schema is ready for authentication.")
return True
if __name__ == "__main__":
success = verify_schema()
sys.exit(0 if success else 1)
|