kashafaman123/Phase_3_Back

1"""2Comprehensive database verification script3Tests SQLModel ORM setup, table creation, and data persistence4"""5import sys6import os7 8# Add src to path9sys.path.insert(0, os.path.join(os.path.dirname(__file__), 'src'))10 11print("=" * 80)12print("DATABASE VERIFICATION TEST")13print("=" * 80)14 15# Step 1: Check environment variables16print("\n[1] Checking environment variables...")17from dotenv import load_dotenv18load_dotenv()19 20DATABASE_URL = os.getenv("DATABASE_URL")21if DATABASE_URL:22    # Mask password for security23    masked_url = DATABASE_URL.split('@')[0].split(':')[0] + ":****@" + DATABASE_URL.split('@')[1] if '@' in DATABASE_URL else "****"24    print(f"   ✓ DATABASE_URL found: {masked_url}")25else:26    print("   ✗ DATABASE_URL not set!")27    sys.exit(1)28 29# Step 2: Import SQLModel and create engine30print("\n[2] Importing SQLModel and creating engine...")31try:32    from sqlmodel import Session, create_engine, SQLModel, Field33    from sqlalchemy import text34    print("   ✓ SQLModel imported successfully")35 36    engine = create_engine(DATABASE_URL, echo=False)37    print("   ✓ Database engine created")38except Exception as e:39    print(f"   ✗ Failed to create engine: {e}")40    sys.exit(1)41 42# Step 3: Test database connection43print("\n[3] Testing database connection...")44try:45    with engine.connect() as conn:46        result = conn.execute(text("SELECT version()")).fetchone()47        print(f"   ✓ Connected to PostgreSQL: {result[0][:50]}...")48except Exception as e:49    print(f"   ✗ Connection failed: {e}")50    sys.exit(1)51 52# Step 4: Check existing tables53print("\n[4] Checking existing tables...")54try:55    with engine.connect() as conn:56        result = conn.execute(57            text("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")58        ).fetchall()59 60        tables = [row[0] for row in result]61        if tables:62            print(f"   ✓ Found {len(tables)} table(s):")63            for table in tables:64                print(f"     - {table}")65        else:66            print("   ! No tables found (will be created)")67except Exception as e:68    print(f"   ✗ Failed to query tables: {e}")69    sys.exit(1)70 71# Step 5: Import models72print("\n[5] Importing data models...")73try:74    from models.user import User75    from models.task import Task76    print("   ✓ User model imported")77    print("   ✓ Task model imported")78except Exception as e:79    print(f"   ✗ Failed to import models: {e}")80    sys.exit(1)81 82# Step 6: Create tables83print("\n[6] Creating database tables (if not exist)...")84try:85    SQLModel.metadata.create_all(engine)86    print("   ✓ Tables created/verified successfully")87except Exception as e:88    print(f"   ✗ Failed to create tables: {e}")89    sys.exit(1)90 91# Step 7: Verify table structure92print("\n[7] Verifying table structure...")93try:94    with engine.connect() as conn:95        # Check users table96        users_cols = conn.execute(97            text("SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users' ORDER BY ordinal_position")98        ).fetchall()99 100        if users_cols:101            print("   ✓ Users table structure:")102            for col in users_cols:103                print(f"     - {col[0]} ({col[1]})")104        else:105            print("   ✗ Users table not found!")106 107        # Check tasks table108        tasks_cols = conn.execute(109            text("SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'tasks' ORDER BY ordinal_position")110        ).fetchall()111 112        if tasks_cols:113            print("   ✓ Tasks table structure:")114            for col in tasks_cols:115                print(f"     - {col[0]} ({col[1]})")116        else:117            print("   ✗ Tasks table not found!")118except Exception as e:119    print(f"   ✗ Failed to verify structure: {e}")120    sys.exit(1)121 122# Step 8: Test data insertion and retrieval123print("\n[8] Testing data operations...")124try:125    from datetime import datetime126    import uuid127 128    with Session(engine) as session:129        # Create test user130        test_user_id = str(uuid.uuid4())131        test_user = User(132            id=test_user_id,133            email=f"test_{uuid.uuid4().hex[:8]}@example.com",134            password_hash="$2b$12$test_hash_for_verification",135            created_at=datetime.utcnow()136        )137 138        session.add(test_user)139        session.commit()140        print(f"   ✓ Test user created: {test_user.email}")141 142        # Create test task143        test_task = Task(144            user_id=test_user_id,145            title="Test Task - Verify Database",146            description="This is a test task to verify SQLModel ORM functionality",147            completed=False,148            created_at=datetime.utcnow(),149            updated_at=datetime.utcnow()150        )151 152        session.add(test_task)153        session.commit()154        session.refresh(test_task)155        print(f"   ✓ Test task created: ID={test_task.id}, Title='{test_task.title}'")156 157        # Verify task retrieval158        retrieved_task = session.get(Task, test_task.id)159        if retrieved_task and retrieved_task.title == test_task.title:160            print(f"   ✓ Task retrieved successfully")161        else:162            print(f"   ✗ Task retrieval failed")163 164        # Test update165        retrieved_task.completed = True166        retrieved_task.updated_at = datetime.utcnow()167        session.add(retrieved_task)168        session.commit()169        print(f"   ✓ Task updated (marked as completed)")170 171        # Verify update persisted172        updated_task = session.get(Task, test_task.id)173        if updated_task.completed:174            print(f"   ✓ Update persisted successfully")175        else:176            print(f"   ✗ Update did not persist")177 178        # Cleanup test data179        session.delete(updated_task)180        session.delete(test_user)181        session.commit()182        print(f"   ✓ Test data cleaned up")183 184except Exception as e:185    print(f"   ✗ Data operation failed: {e}")186    import traceback187    traceback.print_exc()188    sys.exit(1)189 190# Step 9: Check foreign key constraint191print("\n[9] Verifying foreign key constraints...")192try:193    with engine.connect() as conn:194        fk_result = conn.execute(195            text("""196                SELECT197                    tc.constraint_name,198                    tc.table_name,199                    kcu.column_name,200                    ccu.table_name AS foreign_table_name,201                    ccu.column_name AS foreign_column_name202                FROM information_schema.table_constraints AS tc203                JOIN information_schema.key_column_usage AS kcu204                  ON tc.constraint_name = kcu.constraint_name205                JOIN information_schema.constraint_column_usage AS ccu206                  ON ccu.constraint_name = tc.constraint_name207                WHERE tc.constraint_type = 'FOREIGN KEY'208                  AND tc.table_name = 'tasks'209            """)210        ).fetchall()211 212        if fk_result:213            print(f"   ✓ Foreign key constraints found:")214            for fk in fk_result:215                print(f"     - {fk[1]}.{fk[2]} → {fk[3]}.{fk[4]}")216        else:217            print("   ! No foreign key constraints found")218except Exception as e:219    print(f"   ✗ Failed to check constraints: {e}")220 221# Step 10: Count existing records222print("\n[10] Checking existing data...")223try:224    with Session(engine) as session:225        user_count = len(session.exec(text("SELECT id FROM users")).fetchall())226        task_count = len(session.exec(text("SELECT id FROM tasks")).fetchall())227 228        print(f"   ✓ Current data:")229        print(f"     - Users: {user_count}")230        print(f"     - Tasks: {task_count}")231except Exception as e:232    print(f"   ✗ Failed to count records: {e}")233 234print("\n" + "=" * 80)235print("✅ DATABASE VERIFICATION COMPLETE")236print("=" * 80)237print("\nSummary:")238print("  • SQLModel ORM is properly configured")239print("  • Database tables are created with correct schema")240print("  • Data insertion and retrieval work correctly")241print("  • Updates persist to the database")242print("  • Foreign key relationships are enforced")243print("\nConclusion: SQLModel ORM is functioning correctly with PostgreSQL!")244print("=" * 80)245