Spaces:

vocam
/

web

Sleeping

web / database.py

Upload 8 files

5f694cd verified over 1 year ago

16.6 kB

import sqlite3
import os
import datetime

class LanguageLearningDB:
    def __init__(self, db_path):
        """Initialize the database connection."""
        self.db_path = db_path
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
        self.cursor = self.conn.cursor()
        
        # Create tables if they don't exist
        self._create_tables()
    
    def _create_tables(self):
        """Create necessary database tables if they don't exist."""
        self.cursor.executescript('''
        CREATE TABLE IF NOT EXISTS vocabulary (
            id INTEGER PRIMARY KEY,
            word_original TEXT NOT NULL,
            word_translated TEXT NOT NULL,
            language_translated TEXT NOT NULL,
            category TEXT,
            image_path TEXT,
            date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            source TEXT DEFAULT 'manual'
        );
        
        CREATE TABLE IF NOT EXISTS user_progress (
            id INTEGER PRIMARY KEY,
            vocabulary_id INTEGER,
            review_count INTEGER DEFAULT 0,
            correct_count INTEGER DEFAULT 0,
            last_reviewed TIMESTAMP,
            proficiency_level INTEGER DEFAULT 0,
            FOREIGN KEY (vocabulary_id) REFERENCES vocabulary (id)
        );
        
        CREATE TABLE IF NOT EXISTS sessions (
            id INTEGER PRIMARY KEY,
            start_time TIMESTAMP,
            end_time TIMESTAMP,
            words_studied INTEGER DEFAULT 0,
            words_learned INTEGER DEFAULT 0
        );
        
        CREATE TABLE IF NOT EXISTS camera_translations (
            id INTEGER PRIMARY KEY,
            image_path TEXT,
            detected_text TEXT,
            translated_text TEXT,
            source_language TEXT,
            target_language TEXT,
            date_captured TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            is_saved_to_vocabulary BOOLEAN DEFAULT 0
        );
        ''')
        self.conn.commit()
    
    def add_vocabulary(self, word_original, word_translated, language_translated, category=None, image_path=None, source='manual'):
        """Add a new vocabulary entry to the database."""
        try:
            vocab_id = self.cursor.execute('''
            INSERT INTO vocabulary 
            (word_original, word_translated, language_translated, category, image_path, source)
            VALUES (?, ?, ?, ?, ?, ?)
            ''', (word_original, word_translated, language_translated, category, image_path, source)).lastrowid
            
            # Initialize user progress for this vocabulary
            self.cursor.execute('''
            INSERT INTO user_progress (vocabulary_id, last_reviewed)
            VALUES (?, ?)
            ''', (vocab_id, datetime.datetime.now()))
            
            self.conn.commit()
            return vocab_id
        except sqlite3.Error as e:
            print(f"Error adding vocabulary: {e}")
            return None
    
    def get_vocabulary(self, vocabulary_id):
        """Get a specific vocabulary entry by ID."""
        try:
            self.cursor.execute('''
            SELECT * FROM vocabulary WHERE id = ?
            ''', (vocabulary_id,))
            
            return self.cursor.fetchone()
        except sqlite3.Error as e:
            print(f"Error getting vocabulary: {e}")
            return None
    
    def get_all_vocabulary(self, category=None, language=None):
        """Get all vocabulary entries, optionally filtered by category and/or language."""
        try:
            query = "SELECT * FROM vocabulary"
            params = []
            
            if category and language:
                query += " WHERE category = ? AND language_translated = ?"
                params = [category, language]
            elif category:
                query += " WHERE category = ?"
                params = [category]
            elif language:
                query += " WHERE language_translated = ?"
                params = [language]
                
            query += " ORDER BY date_added DESC"
            
            self.cursor.execute(query, params)
            return self.cursor.fetchall()
        except sqlite3.Error as e:
            print(f"Error getting vocabulary: {e}")
            return []
    
    def update_vocabulary(self, vocabulary_id, word_original=None, word_translated=None, 
                          language_translated=None, category=None, image_path=None):
        """Update an existing vocabulary entry."""
        try:
            # Get current values first
            current = self.get_vocabulary(vocabulary_id)
            if not current:
                return False
                
            # Use current values for any parameter not provided
            word_original = word_original if word_original is not None else current['word_original']
            word_translated = word_translated if word_translated is not None else current['word_translated']
            language_translated = language_translated if language_translated is not None else current['language_translated']
            category = category if category is not None else current['category']
            image_path = image_path if image_path is not None else current['image_path']
            
            self.cursor.execute('''
            UPDATE vocabulary 
            SET word_original = ?, word_translated = ?, 
                language_translated = ?, category = ?, image_path = ?
            WHERE id = ?
            ''', (word_original, word_translated, language_translated, 
                  category, image_path, vocabulary_id))
            
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error updating vocabulary: {e}")
            return False
    
    def delete_vocabulary(self, vocabulary_id):
        """Delete a vocabulary entry by ID and associated progress."""
        try:
            # Delete associated progress first (due to foreign key constraint)
            self.cursor.execute('''
            DELETE FROM user_progress WHERE vocabulary_id = ?
            ''', (vocabulary_id,))
            
            # Delete vocabulary entry
            self.cursor.execute('''
            DELETE FROM vocabulary WHERE id = ?
            ''', (vocabulary_id,))
            
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error deleting vocabulary: {e}")
            return False
    
    def search_vocabulary(self, search_term, language=None):
        """Search vocabulary entries for a term in original or translated word."""
        try:
            query = '''
            SELECT * FROM vocabulary 
            WHERE (word_original LIKE ? OR word_translated LIKE ?)
            '''
            params = [f'%{search_term}%', f'%{search_term}%']
            
            if language:
                query += " AND language_translated = ?"
                params.append(language)
                
            self.cursor.execute(query, params)
            return self.cursor.fetchall()
        except sqlite3.Error as e:
            print(f"Error searching vocabulary: {e}")
            return []
    
    def update_word_progress(self, vocabulary_id, is_correct):
        """Update the progress for a vocabulary word after review."""
        try:
            # Get current progress
            self.cursor.execute('''
            SELECT * FROM user_progress WHERE vocabulary_id = ?
            ''', (vocabulary_id,))
            
            progress = self.cursor.fetchone()
            if not progress:
                # Create new progress entry if it doesn't exist
                self.cursor.execute('''
                INSERT INTO user_progress 
                (vocabulary_id, review_count, correct_count, last_reviewed, proficiency_level)
                VALUES (?, ?, ?, ?, ?)
                ''', (vocabulary_id, 1, 1 if is_correct else 0, datetime.datetime.now(), 0))
            else:
                # Update existing progress
                review_count = progress['review_count'] + 1
                correct_count = progress['correct_count'] + (1 if is_correct else 0)
                
                # Calculate new proficiency level (0-5)
                # Simple algorithm: proficiency is percentage of correct answers, mapped to 0-5 scale
                proficiency = min(5, int((correct_count / review_count) * 5))
                
                self.cursor.execute('''
                UPDATE user_progress 
                SET review_count = ?, correct_count = ?, 
                    last_reviewed = ?, proficiency_level = ?
                WHERE vocabulary_id = ?
                ''', (review_count, correct_count, datetime.datetime.now(), 
                      proficiency, vocabulary_id))
            
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error updating word progress: {e}")
            return False
    
    def get_word_progress(self, vocabulary_id):
        """Get the progress for a specific vocabulary word."""
        try:
            self.cursor.execute('''
            SELECT * FROM user_progress WHERE vocabulary_id = ?
            ''', (vocabulary_id,))
            
            return self.cursor.fetchone()
        except sqlite3.Error as e:
            print(f"Error getting word progress: {e}")
            return None
    
    def get_words_for_review(self, limit=10, min_proficiency=None, max_proficiency=None):
        """Get words for review based on proficiency level."""
        try:
            query = '''
            SELECT v.*, p.proficiency_level, p.last_reviewed, p.review_count, p.correct_count
            FROM vocabulary v
            JOIN user_progress p ON v.id = p.vocabulary_id
            WHERE 1=1
            '''
            params = []
            
            if min_proficiency is not None:
                query += " AND p.proficiency_level >= ?"
                params.append(min_proficiency)
                
            if max_proficiency is not None:
                query += " AND p.proficiency_level <= ?"
                params.append(max_proficiency)
                
            # Order by last reviewed (oldest first) and proficiency level (lowest first)
            query += " ORDER BY p.last_reviewed ASC, p.proficiency_level ASC LIMIT ?"
            params.append(limit)
            
            self.cursor.execute(query, params)
            return self.cursor.fetchall()
        except sqlite3.Error as e:
            print(f"Error getting words for review: {e}")
            return []
    
    def start_session(self):
        """Start a new learning session."""
        try:
            session_id = self.cursor.execute('''
            INSERT INTO sessions (start_time, words_studied, words_learned)
            VALUES (?, 0, 0)
            ''', (datetime.datetime.now(),)).lastrowid
            
            self.conn.commit()
            return session_id
        except sqlite3.Error as e:
            print(f"Error starting session: {e}")
            return None
    
    def end_session(self, session_id, words_studied, words_learned):
        """End a learning session with statistics."""
        try:
            self.cursor.execute('''
            UPDATE sessions 
            SET end_time = ?, words_studied = ?, words_learned = ?
            WHERE id = ?
            ''', (datetime.datetime.now(), words_studied, words_learned, session_id))
            
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"Error ending session: {e}")
            return False
    
    def get_session_stats(self, days=30):
        """Get statistics from sessions in the last N days."""
        try:
            cutoff_date = datetime.datetime.now() - datetime.timedelta(days=days)
            
            self.cursor.execute('''
            SELECT 
                COUNT(*) as total_sessions,
                SUM(words_studied) as total_words_studied,
                SUM(words_learned) as total_words_learned,
                AVG(words_studied) as avg_words_per_session,
                AVG(words_learned) as avg_learned_per_session,
                SUM(CAST(strftime('%s', end_time) - strftime('%s', start_time) AS REAL)) / 60 as total_minutes
            FROM sessions
            WHERE start_time >= ?
            ''', (cutoff_date,))
            
            return self.cursor.fetchone()
        except sqlite3.Error as e:
            print(f"Error getting session stats: {e}")
            return None
    
    def save_camera_translation(self, image_path, detected_text, translated_text, 
                               source_language, target_language):
        """Save a translation from camera capture."""
        try:
            translation_id = self.cursor.execute('''
            INSERT INTO camera_translations
            (image_path, detected_text, translated_text, source_language, target_language)
            VALUES (?, ?, ?, ?, ?)
            ''', (image_path, detected_text, translated_text, source_language, target_language)).lastrowid
            
            self.conn.commit()
            return translation_id
        except sqlite3.Error as e:
            print(f"Error saving camera translation: {e}")
            return None
    
    def get_camera_translations(self, limit=50):
        """Get recent camera translations."""
        try:
            self.cursor.execute('''
            SELECT * FROM camera_translations
            ORDER BY date_captured DESC
            LIMIT ?
            ''', (limit,))
            
            return self.cursor.fetchall()
        except sqlite3.Error as e:
            print(f"Error getting camera translations: {e}")
            return []
    
    def add_camera_translation_to_vocabulary(self, translation_id, category=None):
        """Save a camera translation to vocabulary."""
        try:
            # Get the translation
            self.cursor.execute('''
            SELECT * FROM camera_translations WHERE id = ?
            ''', (translation_id,))
            
            translation = self.cursor.fetchone()
            if not translation:
                return False
            
            # Add to vocabulary
            vocab_id = self.add_vocabulary(
                translation['detected_text'],
                translation['translated_text'],
                translation['target_language'],
                category,
                translation['image_path'],
                'camera'
            )
            
            # Mark as saved to vocabulary
            if vocab_id:
                self.cursor.execute('''
                UPDATE camera_translations
                SET is_saved_to_vocabulary = 1
                WHERE id = ?
                ''', (translation_id,))
                
                self.conn.commit()
                return vocab_id
            
            return None
        except sqlite3.Error as e:
            print(f"Error adding camera translation to vocabulary: {e}")
            return None
    
    def close(self):
        """Close the database connection."""
        if self.conn:
            self.conn.close()

# Example usage
if __name__ == "__main__":
    # Create a test database in memory
    db = LanguageLearningDB(":memory:")
    
    # Add some test vocabulary
    db.add_vocabulary("apple", "manzana", "es", "food")
    db.add_vocabulary("book", "libro", "es", "objects")
    
    # Update progress
    db.update_word_progress(1, True)  # Correct answer for word ID 1
    
    # Save a camera translation
    translation_id = db.save_camera_translation(
        "/path/to/image.jpg",
        "hello world",
        "hola mundo",
        "en",
        "es"
    )
    
    # Add translation to vocabulary
    db.add_camera_translation_to_vocabulary(translation_id, "phrases")
    
    # Get all vocabulary
    vocab = db.get_all_vocabulary()
    for word in vocab:
        print(f"{word['word_original']} -> {word['word_translated']} ({word['language_translated']})")
    
    # Close the connection
    db.close()

	import sqlite3
	import os
	import datetime

	class LanguageLearningDB:
	def __init__(self, db_path):
	"""Initialize the database connection."""
	self.db_path = db_path
	self.conn = sqlite3.connect(db_path)
	self.conn.row_factory = sqlite3.Row
	self.cursor = self.conn.cursor()

	# Create tables if they don't exist
	self._create_tables()

	def _create_tables(self):
	"""Create necessary database tables if they don't exist."""
	self.cursor.executescript('''
	CREATE TABLE IF NOT EXISTS vocabulary (
	id INTEGER PRIMARY KEY,
	word_original TEXT NOT NULL,
	word_translated TEXT NOT NULL,
	language_translated TEXT NOT NULL,
	category TEXT,
	image_path TEXT,
	date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	source TEXT DEFAULT 'manual'
	);

	CREATE TABLE IF NOT EXISTS user_progress (
	id INTEGER PRIMARY KEY,
	vocabulary_id INTEGER,
	review_count INTEGER DEFAULT 0,
	correct_count INTEGER DEFAULT 0,
	last_reviewed TIMESTAMP,
	proficiency_level INTEGER DEFAULT 0,
	FOREIGN KEY (vocabulary_id) REFERENCES vocabulary (id)
	);

	CREATE TABLE IF NOT EXISTS sessions (
	id INTEGER PRIMARY KEY,
	start_time TIMESTAMP,
	end_time TIMESTAMP,
	words_studied INTEGER DEFAULT 0,
	words_learned INTEGER DEFAULT 0
	);

	CREATE TABLE IF NOT EXISTS camera_translations (
	id INTEGER PRIMARY KEY,
	image_path TEXT,
	detected_text TEXT,
	translated_text TEXT,
	source_language TEXT,
	target_language TEXT,
	date_captured TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	is_saved_to_vocabulary BOOLEAN DEFAULT 0
	);
	''')
	self.conn.commit()

	def add_vocabulary(self, word_original, word_translated, language_translated, category=None, image_path=None, source='manual'):
	"""Add a new vocabulary entry to the database."""
	try:
	vocab_id = self.cursor.execute('''
	INSERT INTO vocabulary
	(word_original, word_translated, language_translated, category, image_path, source)
	VALUES (?, ?, ?, ?, ?, ?)
	''', (word_original, word_translated, language_translated, category, image_path, source)).lastrowid

	# Initialize user progress for this vocabulary
	self.cursor.execute('''
	INSERT INTO user_progress (vocabulary_id, last_reviewed)
	VALUES (?, ?)
	''', (vocab_id, datetime.datetime.now()))

	self.conn.commit()
	return vocab_id
	except sqlite3.Error as e:
	print(f"Error adding vocabulary: {e}")
	return None

	def get_vocabulary(self, vocabulary_id):
	"""Get a specific vocabulary entry by ID."""
	try:
	self.cursor.execute('''
	SELECT * FROM vocabulary WHERE id = ?
	''', (vocabulary_id,))

	return self.cursor.fetchone()
	except sqlite3.Error as e:
	print(f"Error getting vocabulary: {e}")
	return None

	def get_all_vocabulary(self, category=None, language=None):
	"""Get all vocabulary entries, optionally filtered by category and/or language."""
	try:
	query = "SELECT * FROM vocabulary"
	params = []

	if category and language:
	query += " WHERE category = ? AND language_translated = ?"
	params = [category, language]
	elif category:
	query += " WHERE category = ?"
	params = [category]
	elif language:
	query += " WHERE language_translated = ?"
	params = [language]

	query += " ORDER BY date_added DESC"

	self.cursor.execute(query, params)
	return self.cursor.fetchall()
	except sqlite3.Error as e:
	print(f"Error getting vocabulary: {e}")
	return []

	def update_vocabulary(self, vocabulary_id, word_original=None, word_translated=None,
	language_translated=None, category=None, image_path=None):
	"""Update an existing vocabulary entry."""
	try:
	# Get current values first
	current = self.get_vocabulary(vocabulary_id)
	if not current:
	return False

	# Use current values for any parameter not provided
	word_original = word_original if word_original is not None else current['word_original']
	word_translated = word_translated if word_translated is not None else current['word_translated']
	language_translated = language_translated if language_translated is not None else current['language_translated']
	category = category if category is not None else current['category']
	image_path = image_path if image_path is not None else current['image_path']

	self.cursor.execute('''
	UPDATE vocabulary
	SET word_original = ?, word_translated = ?,
	language_translated = ?, category = ?, image_path = ?
	WHERE id = ?
	''', (word_original, word_translated, language_translated,
	category, image_path, vocabulary_id))

	self.conn.commit()
	return True
	except sqlite3.Error as e:
	print(f"Error updating vocabulary: {e}")
	return False

	def delete_vocabulary(self, vocabulary_id):
	"""Delete a vocabulary entry by ID and associated progress."""
	try:
	# Delete associated progress first (due to foreign key constraint)
	self.cursor.execute('''
	DELETE FROM user_progress WHERE vocabulary_id = ?
	''', (vocabulary_id,))

	# Delete vocabulary entry
	self.cursor.execute('''
	DELETE FROM vocabulary WHERE id = ?
	''', (vocabulary_id,))

	self.conn.commit()
	return True
	except sqlite3.Error as e:
	print(f"Error deleting vocabulary: {e}")
	return False

	def search_vocabulary(self, search_term, language=None):
	"""Search vocabulary entries for a term in original or translated word."""
	try:
	query = '''
	SELECT * FROM vocabulary
	WHERE (word_original LIKE ? OR word_translated LIKE ?)
	'''
	params = [f'%{search_term}%', f'%{search_term}%']

	if language:
	query += " AND language_translated = ?"
	params.append(language)

	self.cursor.execute(query, params)
	return self.cursor.fetchall()
	except sqlite3.Error as e:
	print(f"Error searching vocabulary: {e}")
	return []

	def update_word_progress(self, vocabulary_id, is_correct):
	"""Update the progress for a vocabulary word after review."""
	try:
	# Get current progress
	self.cursor.execute('''
	SELECT * FROM user_progress WHERE vocabulary_id = ?
	''', (vocabulary_id,))

	progress = self.cursor.fetchone()
	if not progress:
	# Create new progress entry if it doesn't exist
	self.cursor.execute('''
	INSERT INTO user_progress
	(vocabulary_id, review_count, correct_count, last_reviewed, proficiency_level)
	VALUES (?, ?, ?, ?, ?)
	''', (vocabulary_id, 1, 1 if is_correct else 0, datetime.datetime.now(), 0))
	else:
	# Update existing progress
	review_count = progress['review_count'] + 1
	correct_count = progress['correct_count'] + (1 if is_correct else 0)

	# Calculate new proficiency level (0-5)
	# Simple algorithm: proficiency is percentage of correct answers, mapped to 0-5 scale
	proficiency = min(5, int((correct_count / review_count) * 5))

	self.cursor.execute('''
	UPDATE user_progress
	SET review_count = ?, correct_count = ?,
	last_reviewed = ?, proficiency_level = ?
	WHERE vocabulary_id = ?
	''', (review_count, correct_count, datetime.datetime.now(),
	proficiency, vocabulary_id))

	self.conn.commit()
	return True
	except sqlite3.Error as e:
	print(f"Error updating word progress: {e}")
	return False

	def get_word_progress(self, vocabulary_id):
	"""Get the progress for a specific vocabulary word."""
	try:
	self.cursor.execute('''
	SELECT * FROM user_progress WHERE vocabulary_id = ?
	''', (vocabulary_id,))

	return self.cursor.fetchone()
	except sqlite3.Error as e:
	print(f"Error getting word progress: {e}")
	return None

	def get_words_for_review(self, limit=10, min_proficiency=None, max_proficiency=None):
	"""Get words for review based on proficiency level."""
	try:
	query = '''
	SELECT v.*, p.proficiency_level, p.last_reviewed, p.review_count, p.correct_count
	FROM vocabulary v
	JOIN user_progress p ON v.id = p.vocabulary_id
	WHERE 1=1
	'''
	params = []

	if min_proficiency is not None:
	query += " AND p.proficiency_level >= ?"
	params.append(min_proficiency)

	if max_proficiency is not None:
	query += " AND p.proficiency_level <= ?"
	params.append(max_proficiency)

	# Order by last reviewed (oldest first) and proficiency level (lowest first)
	query += " ORDER BY p.last_reviewed ASC, p.proficiency_level ASC LIMIT ?"
	params.append(limit)

	self.cursor.execute(query, params)
	return self.cursor.fetchall()
	except sqlite3.Error as e:
	print(f"Error getting words for review: {e}")
	return []

	def start_session(self):
	"""Start a new learning session."""
	try:
	session_id = self.cursor.execute('''
	INSERT INTO sessions (start_time, words_studied, words_learned)
	VALUES (?, 0, 0)
	''', (datetime.datetime.now(),)).lastrowid

	self.conn.commit()
	return session_id
	except sqlite3.Error as e:
	print(f"Error starting session: {e}")
	return None

	def end_session(self, session_id, words_studied, words_learned):
	"""End a learning session with statistics."""
	try:
	self.cursor.execute('''
	UPDATE sessions
	SET end_time = ?, words_studied = ?, words_learned = ?
	WHERE id = ?
	''', (datetime.datetime.now(), words_studied, words_learned, session_id))

	self.conn.commit()
	return True
	except sqlite3.Error as e:
	print(f"Error ending session: {e}")
	return False

	def get_session_stats(self, days=30):
	"""Get statistics from sessions in the last N days."""
	try:
	cutoff_date = datetime.datetime.now() - datetime.timedelta(days=days)

	self.cursor.execute('''
	SELECT
	COUNT(*) as total_sessions,
	SUM(words_studied) as total_words_studied,
	SUM(words_learned) as total_words_learned,
	AVG(words_studied) as avg_words_per_session,
	AVG(words_learned) as avg_learned_per_session,
	SUM(CAST(strftime('%s', end_time) - strftime('%s', start_time) AS REAL)) / 60 as total_minutes
	FROM sessions
	WHERE start_time >= ?
	''', (cutoff_date,))

	return self.cursor.fetchone()
	except sqlite3.Error as e:
	print(f"Error getting session stats: {e}")
	return None

	def save_camera_translation(self, image_path, detected_text, translated_text,
	source_language, target_language):
	"""Save a translation from camera capture."""
	try:
	translation_id = self.cursor.execute('''
	INSERT INTO camera_translations
	(image_path, detected_text, translated_text, source_language, target_language)
	VALUES (?, ?, ?, ?, ?)
	''', (image_path, detected_text, translated_text, source_language, target_language)).lastrowid

	self.conn.commit()
	return translation_id
	except sqlite3.Error as e:
	print(f"Error saving camera translation: {e}")
	return None

	def get_camera_translations(self, limit=50):
	"""Get recent camera translations."""
	try:
	self.cursor.execute('''
	SELECT * FROM camera_translations
	ORDER BY date_captured DESC
	LIMIT ?
	''', (limit,))

	return self.cursor.fetchall()
	except sqlite3.Error as e:
	print(f"Error getting camera translations: {e}")
	return []

	def add_camera_translation_to_vocabulary(self, translation_id, category=None):
	"""Save a camera translation to vocabulary."""
	try:
	# Get the translation
	self.cursor.execute('''
	SELECT * FROM camera_translations WHERE id = ?
	''', (translation_id,))

	translation = self.cursor.fetchone()
	if not translation:
	return False

	# Add to vocabulary
	vocab_id = self.add_vocabulary(
	translation['detected_text'],
	translation['translated_text'],
	translation['target_language'],
	category,
	translation['image_path'],
	'camera'
	)

	# Mark as saved to vocabulary
	if vocab_id:
	self.cursor.execute('''
	UPDATE camera_translations
	SET is_saved_to_vocabulary = 1
	WHERE id = ?
	''', (translation_id,))

	self.conn.commit()
	return vocab_id

	return None
	except sqlite3.Error as e:
	print(f"Error adding camera translation to vocabulary: {e}")
	return None

	def close(self):
	"""Close the database connection."""
	if self.conn:
	self.conn.close()

	# Example usage
	if __name__ == "__main__":
	# Create a test database in memory
	db = LanguageLearningDB(":memory:")

	# Add some test vocabulary
	db.add_vocabulary("apple", "manzana", "es", "food")
	db.add_vocabulary("book", "libro", "es", "objects")

	# Update progress
	db.update_word_progress(1, True) # Correct answer for word ID 1

	# Save a camera translation
	translation_id = db.save_camera_translation(
	"/path/to/image.jpg",
	"hello world",
	"hola mundo",
	"en",
	"es"
	)

	# Add translation to vocabulary
	db.add_camera_translation_to_vocabulary(translation_id, "phrases")

	# Get all vocabulary
	vocab = db.get_all_vocabulary()
	for word in vocab:
	print(f"{word['word_original']} -> {word['word_translated']} ({word['language_translated']})")

	# Close the connection
	db.close()