🔍 Code Extractor

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.

Purpose

This function performs direct SQLite database corruption repair for the vice_ai documents database. It identifies text_sections records with corrupted chat_session_id values (empty strings, '{}', '[]', 'null', or zero-length values) and updates them to NULL. It provides detailed diagnostic output showing which records are affected and displays sample data for verification. The function is designed for manual database maintenance and troubleshooting scenarios where chat session references have become corrupted.

Source Code

def direct_fix():
    db_path = '/tf/active/vice_ai/documents.db'
    
    print("🔧 Direct SQLite corruption fix...")
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # Check what we actually have in the database
        cursor.execute("""
            SELECT id, chat_session_id, title
            FROM text_sections 
            WHERE chat_session_id IN ('{}', '[]', 'null', '') 
               OR LENGTH(COALESCE(chat_session_id, '')) = 0
            ORDER BY id
        """)
        
        corrupted_rows = cursor.fetchall()
        print(f"📊 Found {len(corrupted_rows)} rows with corruption patterns:")
        
        for row in corrupted_rows:
            section_id, chat_session_id, title = row
            title_short = title[:40] + "..." if len(title) > 40 else title
            print(f"   🔴 {section_id}: '{chat_session_id}' -> {title_short}")
        
        if corrupted_rows:
            # Fix them
            cursor.execute("""
                UPDATE text_sections 
                SET chat_session_id = NULL 
                WHERE chat_session_id IN ('{}', '[]', 'null', '') 
                   OR LENGTH(COALESCE(chat_session_id, '')) = 0
            """)
            
            updated = cursor.rowcount
            conn.commit()
            print(f"✅ Fixed {updated} corrupted values")
        else:
            print("🤔 No corruption found in database...")
            
            # Let's check what the Flask server is seeing
            print("\n📊 All chat_session_id values in database:")
            cursor.execute("""
                SELECT id, 
                       CASE 
                           WHEN chat_session_id IS NULL THEN '[NULL]'
                           WHEN chat_session_id = '' THEN '[EMPTY]'
                           ELSE chat_session_id
                       END as displayed_value,
                       title
                FROM text_sections 
                ORDER BY id
                LIMIT 10
            """)
            
            sample_rows = cursor.fetchall()
            for row in sample_rows:
                section_id, displayed_value, title = row
                title_short = title[:30] + "..." if len(title) > 30 else title
                print(f"   📄 {section_id}: {displayed_value} -> {title_short}")
        
    except Exception as e:
        print(f"❌ Error: {e}")
        conn.rollback()
    finally:
        conn.close()

Return Value

This function does not return any value (implicitly returns None). It performs side effects by modifying the database and printing diagnostic information to stdout.

Dependencies

• sqlite3

Required Imports

import sqlite3

Usage Example

import sqlite3

def direct_fix():
    db_path = '/tf/active/vice_ai/documents.db'
    print("🔧 Direct SQLite corruption fix...")
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    try:
        cursor.execute("""
            SELECT id, chat_session_id, title
            FROM text_sections 
            WHERE chat_session_id IN ('{}', '[]', 'null', '') 
               OR LENGTH(COALESCE(chat_session_id, '')) = 0
            ORDER BY id
        """)
        corrupted_rows = cursor.fetchall()
        print(f"📊 Found {len(corrupted_rows)} rows with corruption patterns:")
        for row in corrupted_rows:
            section_id, chat_session_id, title = row
            title_short = title[:40] + "..." if len(title) > 40 else title
            print(f"   🔴 {section_id}: '{chat_session_id}' -> {title_short}")
        if corrupted_rows:
            cursor.execute("""
                UPDATE text_sections 
                SET chat_session_id = NULL 
                WHERE chat_session_id IN ('{}', '[]', 'null', '') 
                   OR LENGTH(COALESCE(chat_session_id, '')) = 0
            """)
            updated = cursor.rowcount
            conn.commit()
            print(f"✅ Fixed {updated} corrupted values")
        else:
            print("🤔 No corruption found in database...")
            cursor.execute("""
                SELECT id, 
                       CASE 
                           WHEN chat_session_id IS NULL THEN '[NULL]'
                           WHEN chat_session_id = '' THEN '[EMPTY]'
                           ELSE chat_session_id
                       END as displayed_value,
                       title
                FROM text_sections 
                ORDER BY id
                LIMIT 10
            """)
            sample_rows = cursor.fetchall()
            for row in sample_rows:
                section_id, displayed_value, title = row
                title_short = title[:30] + "..." if len(title) > 30 else title
                print(f"   📄 {section_id}: {displayed_value} -> {title_short}")
    except Exception as e:
        print(f"❌ Error: {e}")
        conn.rollback()
    finally:
        conn.close()

# Execute the fix
direct_fix()

Best Practices

• Always backup the database before running this function as it performs UPDATE operations
• The function uses hardcoded database path '/tf/active/vice_ai/documents.db' - consider parameterizing for reusability
• Function prints diagnostic output to stdout - redirect or capture if running in automated scripts
• Uses proper transaction management with commit/rollback and connection cleanup in finally block
• The function identifies specific corruption patterns: '{}', '[]', 'null', empty strings, and zero-length values
• Consider running in a maintenance window as it may lock the database during UPDATE operations
• Review the diagnostic output before running to understand the scope of corruption
• The function limits sample output to 10 rows for performance when no corruption is found
• Error handling catches all exceptions but only prints them - consider logging for production use

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function check_and_fix_corruption 89.7% 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 cleanup_corrupted_chat_session_ids 86.0% similar

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

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

• function check_specific_corruption 84.8% 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 raw_cleanup_database 83.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 inspect_database 76.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