fts_config.py

"""
FTS Configuration for Stack Overflow Solutions
"""

# Database paths
MAIN_DB_PATH = "/mnt/stackoverflow_db/databases/SELF_HEALING_AGI.db"
FTS_DB_PATH = "/var/lib/stackoverflow_fts/solutions_fts.db"

# Actual columns in solutions table
SOLUTIONS_COLUMNS = [
    'id',
    'question_id',
    'question_title',
    'question_body',
    'question_tags',
    'answer_body',
    'answer_score',
    'is_accepted',
    'error_pattern',
    'fix_command',
    'success_rate',
    'created_date'
]

# Columns indexed in FTS
FTS_INDEXED_COLUMNS = [
    'question_title',
    'question_body',
    'question_tags',
    'answer_body',
    'error_pattern',
    'fix_command'
]

# Example search queries
def search_solutions(query, limit=10):
    """Search solutions using FTS5"""
    import sqlite3
    
    # Connect to FTS database
    conn = sqlite3.connect(FTS_DB_PATH)
    
    # Attach main database to get full record data
    conn.execute(f"ATTACH DATABASE '{MAIN_DB_PATH}' AS main")
    
    # Search with relevance ranking
    results = conn.execute("""
        SELECT 
            m.original_id,
            s.id,
            s.question_title,
            s.answer_body,
            s.answer_score,
            s.is_accepted,
            s.error_pattern,
            s.fix_command,
            bm25(fts) as rank
        FROM solutions_fts fts
        JOIN fts_id_mapping m ON fts.rowid = m.fts_rowid
        JOIN main.solutions s ON s.id = m.original_id
        WHERE solutions_fts MATCH ?
        ORDER BY rank
        LIMIT ?
    """, (query, limit)).fetchall()
    
    conn.close()
    return results

# Test function
def test_fts():
    """Test FTS functionality"""
    test_queries = [
        "python error",
        "javascript async",
        "java nullpointerexception",
        "sql join performance"
    ]
    
    for query in test_queries:
        results = search_solutions(query, limit=3)
        print(f"\nSearch: '{query}' - Found {len(results)} results")
        for r in results:
            print(f"  - Score: {r[4]}, Title: {r[2][:60]}...")