🔍 Code Extractor

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.

Purpose

This diagnostic function is designed to troubleshoot and analyze the contents of a specific SQLite database (documents.db) used in a vice_ai application. It checks for database existence, lists all tables, examines the text_sections table structure, counts rows, and identifies suspicious or potentially corrupted chat_session_id values that contain special characters like '{' or '['. The function is primarily used for debugging and data quality assurance.

Source Code

def inspect_database():
    db_path = '/tf/active/vice_ai/documents.db'
    
    if not os.path.exists(db_path):
        print(f"❌ Database not found at: {db_path}")
        return
    
    print("🔍 Inspecting database contents...")
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        # Check what tables exist
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        print(f"📊 Tables in database: {[table[0] for table in tables]}")
        
        # Check if text_sections table exists and its structure
        cursor.execute("PRAGMA table_info(text_sections);")
        columns = cursor.fetchall()
        print(f"📋 text_sections columns: {[(col[1], col[2]) for col in columns]}")
        
        # Count total rows
        cursor.execute("SELECT COUNT(*) FROM text_sections;")
        total_count = cursor.fetchone()[0]
        print(f"📊 Total text_sections rows: {total_count}")
        
        # Check all chat_session_id values
        cursor.execute("""
            SELECT id, chat_session_id, title
            FROM text_sections 
            WHERE chat_session_id IS NOT NULL
            ORDER BY id
        """)
        
        rows_with_chat_id = cursor.fetchall()
        print(f"📊 Rows with non-NULL chat_session_id: {len(rows_with_chat_id)}")
        
        for row in rows_with_chat_id:
            section_id, chat_session_id, title = row
            title_short = title[:30] + "..." if len(title) > 30 else title
            print(f"   📄 {section_id}: '{chat_session_id}' - {title_short}")
        
        # Check for any specific patterns that might be corrupted
        cursor.execute("""
            SELECT id, chat_session_id, title
            FROM text_sections 
            WHERE chat_session_id LIKE '%{%' OR chat_session_id LIKE '%[%'
        """)
        
        suspicious_rows = cursor.fetchall()
        print(f"📊 Rows with suspicious chat_session_id (containing {{ or [): {len(suspicious_rows)}")
        
        for row in suspicious_rows:
            section_id, chat_session_id, title = row
            title_short = title[:30] + "..." if len(title) > 30 else title
            print(f"   🔴 {section_id}: '{chat_session_id}' - {title_short}")
        
    except Exception as e:
        print(f"❌ Error during inspection: {e}")
    finally:
        conn.close()

Return Value

This function returns None (implicitly). It produces side effects by printing diagnostic information to stdout, including database structure details, row counts, and lists of potentially problematic records.

Dependencies

• sqlite3
• os

Required Imports

import sqlite3
import os

Usage Example

import sqlite3
import os

def inspect_database():
    db_path = '/tf/active/vice_ai/documents.db'
    
    if not os.path.exists(db_path):
        print(f"❌ Database not found at: {db_path}")
        return
    
    print("🔍 Inspecting database contents...")
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    try:
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
        tables = cursor.fetchall()
        print(f"📊 Tables in database: {[table[0] for table in tables]}")
        
        cursor.execute("PRAGMA table_info(text_sections);")
        columns = cursor.fetchall()
        print(f"📋 text_sections columns: {[(col[1], col[2]) for col in columns]}")
        
        cursor.execute("SELECT COUNT(*) FROM text_sections;")
        total_count = cursor.fetchone()[0]
        print(f"📊 Total text_sections rows: {total_count}")
        
        cursor.execute("""
            SELECT id, chat_session_id, title
            FROM text_sections 
            WHERE chat_session_id IS NOT NULL
            ORDER BY id
        """)
        
        rows_with_chat_id = cursor.fetchall()
        print(f"📊 Rows with non-NULL chat_session_id: {len(rows_with_chat_id)}")
        
        for row in rows_with_chat_id:
            section_id, chat_session_id, title = row
            title_short = title[:30] + "..." if len(title) > 30 else title
            print(f"   📄 {section_id}: '{chat_session_id}' - {title_short}")
        
        cursor.execute("""
            SELECT id, chat_session_id, title
            FROM text_sections 
            WHERE chat_session_id LIKE '%{%' OR chat_session_id LIKE '%[%'
        """)
        
        suspicious_rows = cursor.fetchall()
        print(f"📊 Rows with suspicious chat_session_id (containing {{ or [): {len(suspicious_rows)}")
        
        for row in suspicious_rows:
            section_id, chat_session_id, title = row
            title_short = title[:30] + "..." if len(title) > 30 else title
            print(f"   🔴 {section_id}: '{chat_session_id}' - {title_short}")
        
    except Exception as e:
        print(f"❌ Error during inspection: {e}")
    finally:
        conn.close()

# Run the inspection
inspect_database()

Best Practices

• The database path is hardcoded to '/tf/active/vice_ai/documents.db' - consider parameterizing this for reusability
• The function uses proper try-finally blocks to ensure database connection is closed even if errors occur
• Consider adding error handling for specific SQL exceptions (e.g., table not found)
• The function assumes specific table structure (text_sections with id, chat_session_id, title columns)
• Output is printed to stdout - consider returning structured data or using logging for production use
• The suspicious pattern detection looks for '{' and '[' characters which may need adjustment based on actual data corruption patterns
• Consider adding pagination or limits when displaying rows to avoid overwhelming output with large datasets

Tags

Similar Components

AI-powered semantic similarity - components with related functionality:

• function check_and_fix_corruption 78.5% 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 direct_fix 76.4% 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_specific_corruption 75.6% 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 73.5% 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 debug_section_retrieval 72.0% similar

  A debugging utility function that tests database section retrieval by querying a specific text section and printing detailed diagnostic information about the section and its chat session association.

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