""" Comprehensive vocabulary database setup script This script handles multiple scenarios: 1. Check if the database exists 2. Create the vocabulary table if it doesn't exist 3. Check if foreign keys are properly configured 4. Add a test entry """ import os import sqlite3 from datetime import datetime import sys import traceback def setup_vocabulary_database(): """Set up the vocabulary table in the database""" print("\n" + "=" * 60) print(" COMPREHENSIVE VOCABULARY DATABASE SETUP ") print("=" * 60) # 1. Find the database file root_dir = os.path.dirname(os.path.abspath(__file__)) instance_db = os.path.join(root_dir, 'instance', 'app.db') root_db = os.path.join(root_dir, 'app.db') if os.path.exists(instance_db): db_path = instance_db print(f"✓ Found database in instance folder: {instance_db}") elif os.path.exists(root_db): db_path = root_db print(f"✓ Found database in root folder: {root_db}") else: print(f"✗ ERROR: Database not found in expected locations:") print(f" - {instance_db}") print(f" - {root_db}") return False try: # 2. Connect to the database print(f"\n[1/5] Connecting to database...") conn = sqlite3.connect(db_path) cursor = conn.cursor() print(f"✓ Connected successfully to {db_path}") # 3. Check database schema print(f"\n[2/5] Checking database schema...") cursor.execute("SELECT name FROM sqlite_master WHERE type='table'") tables = [table[0] for table in cursor.fetchall()] print(f"✓ Found {len(tables)} tables in database") print(f" Tables: {', '.join(tables)}") # 4. Check if we have a users table print(f"\n[3/5] Verifying users table...") if 'users' not in tables: print(f"✗ ERROR: 'users' table not found. Required for vocabulary foreign key.") print(f" Available tables: {', '.join(tables)}") return False # Get user count cursor.execute("SELECT COUNT(*) FROM users") user_count = cursor.fetchone()[0] print(f"✓ Found {user_count} users in database") # 5. Create or verify vocabulary table print(f"\n[4/5] Setting up vocabulary table...") if 'vocabulary' in tables: print(f"✓ Vocabulary table already exists") # Check vocabulary table structure cursor.execute("PRAGMA table_info(vocabulary)") columns = cursor.fetchall() column_names = [col[1] for col in columns] expected_columns = ['id', 'student_id', 'word', 'pronunciation', 'definition', 'synonyms', 'antonyms', 'example', 'date_added'] missing_columns = [col for col in expected_columns if col not in column_names] if missing_columns: print(f"✗ WARNING: Vocabulary table is missing columns: {', '.join(missing_columns)}") print(f" Current columns: {', '.join(column_names)}") # Attempt to add missing columns for col in missing_columns: try: if col == 'id': continue # Can't alter primary key elif col == 'student_id': cursor.execute("ALTER TABLE vocabulary ADD COLUMN student_id INTEGER NOT NULL REFERENCES users(id)") elif col == 'date_added': cursor.execute("ALTER TABLE vocabulary ADD COLUMN date_added DATETIME DEFAULT CURRENT_TIMESTAMP") elif col in ['word', 'pronunciation']: cursor.execute(f"ALTER TABLE vocabulary ADD COLUMN {col} VARCHAR(100)") else: cursor.execute(f"ALTER TABLE vocabulary ADD COLUMN {col} TEXT") print(f"✓ Added missing column: {col}") except sqlite3.OperationalError as e: print(f"✗ Could not add column {col}: {str(e)}") else: print(f"✓ Vocabulary table has all expected columns") else: # Create the vocabulary table print(f"Creating new vocabulary table...") try: cursor.execute(''' CREATE TABLE vocabulary ( id INTEGER PRIMARY KEY AUTOINCREMENT, student_id INTEGER NOT NULL, word VARCHAR(100) NOT NULL, pronunciation VARCHAR(100), definition TEXT NOT NULL, synonyms TEXT, antonyms TEXT, example TEXT, date_added DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (student_id) REFERENCES users(id) ) ''') print(f"✓ Vocabulary table created successfully") except sqlite3.OperationalError as e: print(f"✗ ERROR creating vocabulary table: {str(e)}") return False # 6. Add a test entry if the table is empty print(f"\n[5/5] Adding test vocabulary entry...") # Check if there are any entries cursor.execute("SELECT COUNT(*) FROM vocabulary") vocab_count = cursor.fetchone()[0] if vocab_count > 0: print(f"✓ Found {vocab_count} existing vocabulary entries") else: # Get a valid user ID for the test entry cursor.execute("SELECT id FROM users LIMIT 1") user_result = cursor.fetchone() if not user_result: print(f"✗ ERROR: No users found in database. Cannot add test vocabulary.") return False user_id = user_result[0] print(f"✓ Using user ID {user_id} for test vocabulary entry") # Add test entry now = datetime.utcnow().isoformat() try: cursor.execute(''' INSERT INTO vocabulary (student_id, word, pronunciation, definition, synonyms, antonyms, example, date_added) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ''', ( user_id, "Serendipity", "/ˌsɛrənˈdɪpɪti/", "The occurrence and development of events by chance in a happy or beneficial way", "Chance,Luck,Fortune,Happenstance", "Plan,Intent,Design,Misfortune", "Finding that rare book was pure serendipity.", now )) # Commit the changes conn.commit() print(f"✓ Test vocabulary entry added successfully") # Verify entry was added cursor.execute("SELECT id, word FROM vocabulary ORDER BY id DESC LIMIT 1") entry = cursor.fetchone() if entry: print(f"✓ Confirmed new entry: ID {entry[0]} - '{entry[1]}'") except sqlite3.Error as e: print(f"✗ ERROR adding test entry: {str(e)}") conn.rollback() return False # Commit any pending changes and close connection conn.commit() conn.close() print("\n" + "=" * 60) print(" DATABASE SETUP COMPLETED SUCCESSFULLY ") print("=" * 60) print("\nYou can now use the vocabulary feature in the application.") print("Navigate to the student dashboard and click on 'My Vocabulary'") print("to view and manage your vocabulary words.") return True except Exception as e: print(f"\n✗ UNEXPECTED ERROR: {str(e)}") traceback.print_exc() return False if __name__ == "__main__": setup_vocabulary_database()