optimize_database.py

#!/usr/bin/env python3
"""
Database optimization script for DXFMeta application.
This script adds necessary indexes to improve database performance.
"""

import os
import sys
import sqlite3
import argparse

def optimize_database(db_path):
    """Add performance optimizations to the database."""
    if not os.path.exists(db_path):
        print(f"Error: Database file not found at {db_path}")
        return False
    
    print(f"Optimizing database at {db_path}...")
    
    try:
        # Connect to the database
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if modules table exists
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='modules'")
        if not cursor.fetchone():
            print("Error: modules table not found in database")
            conn.close()
            return False
        
        # Get table structure to check column names
        cursor.execute("PRAGMA table_info(modules)")
        columns = [row[1] for row in cursor.fetchall()]
        print(f"Available columns: {columns}")
        
        # Add indexes on commonly queried fields
        indexes_to_create = [
            ("idx_modules_module_id", "CREATE INDEX IF NOT EXISTS idx_modules_module_id ON modules(module_id)"),
            ("idx_modules_module_type", "CREATE INDEX IF NOT EXISTS idx_modules_module_type ON modules(module_type)"),
            ("idx_modules_relative_path", "CREATE INDEX IF NOT EXISTS idx_modules_relative_path ON modules(relative_path)")
        ]
        
        # Create each index
        for idx_name, idx_sql in indexes_to_create:
            print(f"Creating index {idx_name}...")
            cursor.execute(idx_sql)
        
        # Enable WAL mode for better concurrent access
        print("Enabling WAL mode...")
        wal_mode = cursor.execute("PRAGMA journal_mode=WAL").fetchone()[0]
        print(f"Journal mode set to: {wal_mode}")
        
        # Increase cache size for better performance
        print("Increasing cache size...")
        cursor.execute("PRAGMA cache_size=-10000")  # 10MB cache
        
        # Set synchronous mode for better performance
        print("Setting synchronous mode...")
        cursor.execute("PRAGMA synchronous=NORMAL")
        sync_mode = cursor.execute("PRAGMA synchronous").fetchone()[0]
        print(f"Synchronous mode set to: {sync_mode}")
        
        # Optimize the database
        print("Running ANALYZE to update statistics...")
        cursor.execute("ANALYZE")
        
        # Vacuum the database to reclaim space and optimize structure
        print("Running VACUUM to optimize database structure...")
        cursor.execute("VACUUM")
        
        # Commit changes
        conn.commit()
        conn.close()
        
        print("Database optimization completed successfully")
        return True
        
    except sqlite3.Error as e:
        print(f"SQLite error: {str(e)}")
        return False
    except Exception as e:
        print(f"Error optimizing database: {str(e)}")
        return False

def main():
    """Main entry point."""
    parser = argparse.ArgumentParser(description='Optimize DXFMeta database performance')
    parser.add_argument('--db-path', help='Path to the database file')
    args = parser.parse_args()
    
    # Determine database path
    db_path = args.db_path
    if not db_path:
        # If not specified, look in the current directory
        if os.path.exists('dxf_metadata.db'):
            db_path = 'dxf_metadata.db'
        else:
            print("Error: Database path not specified and not found in current directory")
            parser.print_help()
            return 1
    
    # Run the optimization
    if optimize_database(db_path):
        return 0
    else:
        return 1

if __name__ == "__main__":
    sys.exit(main())