🔍 Code Extractor

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.

Purpose

This function performs database maintenance by identifying and correcting corrupted chat_session_id values that may have been stored as empty strings, '{}', '[]', or 'null' strings. It provides detailed reporting of corruption status, counts affected rows, performs the fix operation, and verifies the results. This is useful for data integrity maintenance in applications using the vice_ai documents database.

Source Code

def check_and_fix_corruption():
    db_path = '/tf/active/vice_ai/documents.db'
    
    print("🔍 Checking for corrupted chat_session_id values...")
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # Get ALL rows with their raw chat_session_id values
        cursor.execute("""
            SELECT id, title, chat_session_id, 
                   CASE 
                       WHEN chat_session_id IS NULL THEN 'NULL'
                       WHEN chat_session_id = '' THEN 'EMPTY_STRING'
                       WHEN chat_session_id = '{}' THEN 'CORRUPTED_BRACES'
                       WHEN chat_session_id = '[]' THEN 'CORRUPTED_BRACKETS'
                       WHEN chat_session_id = 'null' THEN 'CORRUPTED_NULL_STRING'
                       ELSE 'VALID'
                   END as status
            FROM text_sections 
            ORDER BY id
        """)
        
        all_rows = cursor.fetchall()
        print(f"📊 Total text_sections: {len(all_rows)}")
        
        status_counts = {}
        corrupted_rows = []
        
        for row in all_rows:
            section_id, title, chat_session_id, status = row
            if status not in status_counts:
                status_counts[status] = 0
            status_counts[status] += 1
            
            if status.startswith('CORRUPTED'):
                corrupted_rows.append((section_id, chat_session_id, title[:30]))
                print(f"   🔴 FOUND CORRUPTION: {section_id} -> '{chat_session_id}' ({status})")
        
        print(f"\n📈 Status Summary:")
        for status, count in status_counts.items():
            print(f"   {status}: {count}")
        
        if corrupted_rows:
            print(f"\n🔧 Fixing {len(corrupted_rows)} corrupted values...")
            
            # Fix the corruption by setting to NULL
            cursor.execute("""
                UPDATE text_sections 
                SET chat_session_id = NULL 
                WHERE chat_session_id IN ('{}', '[]', 'null', '')
            """)
            
            rows_updated = cursor.rowcount
            conn.commit()
            
            print(f"✅ Updated {rows_updated} rows - set corrupted values to NULL")
            
            # Verify
            cursor.execute("""
                SELECT COUNT(*) 
                FROM text_sections 
                WHERE chat_session_id IN ('{}', '[]', 'null', '')
            """)
            remaining = cursor.fetchone()[0]
            print(f"✅ Verification: {remaining} corrupted values remaining")
            
        else:
            print("✅ No corrupted values found")
        
    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 status messages to stdout.

Dependencies

• sqlite3

Required Imports

import sqlite3

Usage Example

import sqlite3

def check_and_fix_corruption():
    db_path = '/tf/active/vice_ai/documents.db'
    
    print("🔍 Checking for corrupted chat_session_id values...")
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        cursor.execute("""
            SELECT id, title, chat_session_id, 
                   CASE 
                       WHEN chat_session_id IS NULL THEN 'NULL'
                       WHEN chat_session_id = '' THEN 'EMPTY_STRING'
                       WHEN chat_session_id = '{}' THEN 'CORRUPTED_BRACES'
                       WHEN chat_session_id = '[]' THEN 'CORRUPTED_BRACKETS'
                       WHEN chat_session_id = 'null' THEN 'CORRUPTED_NULL_STRING'
                       ELSE 'VALID'
                   END as status
            FROM text_sections 
            ORDER BY id
        """)
        
        all_rows = cursor.fetchall()
        print(f"📊 Total text_sections: {len(all_rows)}")
        
        status_counts = {}
        corrupted_rows = []
        
        for row in all_rows:
            section_id, title, chat_session_id, status = row
            if status not in status_counts:
                status_counts[status] = 0
            status_counts[status] += 1
            
            if status.startswith('CORRUPTED'):
                corrupted_rows.append((section_id, chat_session_id, title[:30]))
                print(f"   🔴 FOUND CORRUPTION: {section_id} -> '{chat_session_id}' ({status})")
        
        print(f"\n📈 Status Summary:")
        for status, count in status_counts.items():
            print(f"   {status}: {count}")
        
        if corrupted_rows:
            print(f"\n🔧 Fixing {len(corrupted_rows)} corrupted values...")
            
            cursor.execute("""
                UPDATE text_sections 
                SET chat_session_id = NULL 
                WHERE chat_session_id IN ('{}', '[]', 'null', '')
            """)
            
            rows_updated = cursor.rowcount
            conn.commit()
            
            print(f"✅ Updated {rows_updated} rows - set corrupted values to NULL")
            
            cursor.execute("""
                SELECT COUNT(*) 
                FROM text_sections 
                WHERE chat_session_id IN ('{}', '[]', 'null', '')
            """)
            remaining = cursor.fetchone()[0]
            print(f"✅ Verification: {remaining} corrupted values remaining")
            
        else:
            print("✅ No corrupted values found")
        
    except Exception as e:
        print(f"❌ Error: {e}")
        conn.rollback()
    finally:
        conn.close()

# Run the function
check_and_fix_corruption()

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
• The function commits changes automatically - ensure you want automatic fixes rather than manual review
• Error handling includes rollback on exceptions, but successful operations are committed immediately
• The function prints verbose output - redirect stdout if running in production environments
• Consider running in a transaction with manual commit if you want to review changes before applying
• The function identifies specific corruption patterns: '{}', '[]', 'null', and empty strings - add more patterns if needed
• Connection is properly closed in finally block to prevent resource leaks

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function check_specific_corruption 92.3% 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 direct_fix 89.7% 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 cleanup_corrupted_chat_session_ids 84.8% 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 raw_cleanup_database 80.6% 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 78.5% 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