🔍 Code Extractor

Database maintenance function that identifies and fixes corrupted chat_session_id values in the text_sections table by replacing invalid string representations with NULL.

Purpose

This function performs database cleanup to fix data integrity issues where chat_session_id values have been corrupted with invalid string representations like '{}', '[]', 'null', or empty strings. It scans the text_sections table, identifies corrupted entries, updates them to NULL, and provides detailed console output about the cleanup process. This is typically used as a maintenance script or migration tool to repair database inconsistencies.

Source Code

def cleanup_corrupted_chat_session_ids():
    """Fix corrupted chat_session_id values in the database"""
    
    print("🧹 Starting database cleanup for corrupted chat_session_id values...")
    
    # Connect to database
    db = DatabaseManager()
    conn = db._get_connection()
    cursor = conn.cursor()
    
    try:
        # Find all corrupted chat_session_id values
        cursor.execute("""
            SELECT id, title, chat_session_id 
            FROM text_sections 
            WHERE chat_session_id IN ('{}', '[]', 'null', '')
        """)
        corrupted_rows = cursor.fetchall()
        
        print(f"📊 Found {len(corrupted_rows)} sections with corrupted chat_session_id values:")
        for row in corrupted_rows:
            section_id, title, chat_session_id = row
            print(f"  📄 Section {section_id}: {title[:50]}... -> chat_session_id: {repr(chat_session_id)}")
        
        if corrupted_rows:
            print(f"\n🔧 Fixing {len(corrupted_rows)} corrupted values...")
            
            # Fix the corrupted values by setting them to NULL
            cursor.execute("""
                UPDATE text_sections 
                SET chat_session_id = NULL 
                WHERE chat_session_id IN ('{}', '[]', 'null', '')
            """)
            
            # Commit the changes
            conn.commit()
            
            print(f"✅ Successfully fixed {cursor.rowcount} corrupted chat_session_id values")
            
            # Verify the fix
            cursor.execute("""
                SELECT COUNT(*) 
                FROM text_sections 
                WHERE chat_session_id IN ('{}', '[]', 'null', '')
            """)
            remaining_count = cursor.fetchone()[0]
            
            if remaining_count == 0:
                print("✅ All corrupted values have been cleaned up!")
            else:
                print(f"⚠️  Still {remaining_count} corrupted values remaining")
                
        else:
            print("✅ No corrupted values found - database is clean!")
            
    except Exception as e:
        print(f"❌ Error during cleanup: {e}")
        conn.rollback()
        return False
    finally:
        conn.close()
        
    return True

Return Value

Returns a boolean value: True if the cleanup operation completed successfully (whether or not corrupted values were found), False if an exception occurred during the cleanup process. The function also prints detailed progress information to the console including the number of corrupted rows found, their details, and verification of the fix.

Dependencies

• sqlite3
• models

Required Imports

import sqlite3
from models import DatabaseManager

Usage Example

# Run the cleanup function to fix corrupted chat_session_id values
result = cleanup_corrupted_chat_session_ids()

if result:
    print("Cleanup completed successfully")
else:
    print("Cleanup failed - check error messages above")

# Example output:
# 🧹 Starting database cleanup for corrupted chat_session_id values...
# 📊 Found 3 sections with corrupted chat_session_id values:
#   📄 Section 1: Introduction to Python... -> chat_session_id: '{}'
#   📄 Section 2: Advanced Topics... -> chat_session_id: '[]'
# 🔧 Fixing 3 corrupted values...
# ✅ Successfully fixed 3 corrupted chat_session_id values
# ✅ All corrupted values have been cleaned up!

Best Practices

• Always backup the database before running this cleanup function as it performs UPDATE operations
• Run this function during maintenance windows or when the application is not actively being used to avoid conflicts
• Review the console output to understand what changes were made to the database
• The function uses transactions and will rollback on errors, but manual verification after cleanup is recommended
• This function should be run as a one-time migration or periodic maintenance task, not as part of regular application flow
• Ensure the DatabaseManager class properly implements connection management and the _get_connection() method
• The function specifically targets string representations of empty values ('{}', '[]', 'null', '') - if other corruption patterns exist, the SQL query needs to be updated

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function direct_fix 86.0% similar

  A database maintenance function that detects and fixes corrupted chat_session_id values in a SQLite database's text_sections table by identifying invalid patterns and setting them to NULL.

  From: /tf/active/vicechatdev/vice_ai/direct_sqlite_fix.py

• function check_and_fix_corruption 84.8% similar

  Scans a SQLite database for corrupted chat_session_id values in the text_sections table and automatically fixes them by setting invalid entries to NULL.

  From: /tf/active/vicechatdev/vice_ai/direct_corruption_checker.py

• function raw_cleanup_database 84.2% similar

  Performs raw database cleanup on a SQLite database to identify and fix corrupted chat_session_id values in the text_sections table by converting invalid string representations ('{}', '[]', 'null', '') to NULL.

  From: /tf/active/vicechatdev/vice_ai/raw_database_cleanup.py

• function check_specific_corruption 82.2% similar

  Detects and fixes specific corruption patterns in the chat_session_id column of a SQLite database's text_sections table, replacing invalid values with NULL.

  From: /tf/active/vicechatdev/vice_ai/check_specific_corruption.py

• function inspect_database 68.4% similar

  Inspects a SQLite database at a hardcoded path, examining table structure, row counts, and identifying potentially corrupted chat_session_id values in the text_sections table.

  From: /tf/active/vicechatdev/vice_ai/database_inspector.py