Buckets:
AnonymousSpottedToad/candidate_bucket / codebase /python /Kolerr-Lab_supper-decoyable /decoyable /database.py
| """Database connection management and optimization for DECOYABLE.""" | |
| import logging | |
| import os | |
| from contextlib import contextmanager | |
| from typing import Any, Dict, List, Optional | |
| from sqlalchemy import JSON, Column, DateTime, Integer, String, Text, create_engine, pool | |
| from sqlalchemy.ext.declarative import declarative_base | |
| from sqlalchemy.orm import sessionmaker | |
| from sqlalchemy.sql import func | |
| logger = logging.getLogger(__name__) | |
| # SQLAlchemy Base for models | |
| Base = declarative_base() | |
| # Database models for scan results storage | |
| class ScanResult(Base): | |
| """Model for storing scan results.""" | |
| __tablename__ = "scan_results" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| scan_type = Column(String(50), nullable=False, index=True) # secrets, deps, sast | |
| target_path = Column(String(4096), nullable=False) | |
| status = Column(String(20), nullable=False) # success, error, in_progress | |
| results = Column(JSON, nullable=True) | |
| error_message = Column(Text, nullable=True) | |
| created_at = Column(DateTime(timezone=True), server_default=func.now(), index=True) | |
| updated_at = Column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now()) | |
| scan_duration = Column(Integer, nullable=True) # in seconds | |
| file_count = Column(Integer, nullable=True) | |
| class ScanCache(Base): | |
| """Model for caching scan results.""" | |
| __tablename__ = "scan_cache" | |
| id = Column(Integer, primary_key=True, autoincrement=True) | |
| cache_key = Column(String(128), nullable=False, unique=True, index=True) | |
| scan_type = Column(String(50), nullable=False, index=True) | |
| target_path = Column(String(4096), nullable=False) | |
| results = Column(JSON, nullable=False) | |
| created_at = Column(DateTime(timezone=True), server_default=func.now(), index=True) | |
| expires_at = Column(DateTime(timezone=True), nullable=False, index=True) | |
| hit_count = Column(Integer, default=0) | |
| class DatabaseManager: | |
| """Database connection manager with connection pooling and optimization.""" | |
| def __init__(self, database_url: Optional[str] = None): | |
| """ | |
| Initialize database manager. | |
| Args: | |
| database_url: Database connection URL | |
| """ | |
| self.database_url = database_url or os.getenv("DATABASE_URL", "sqlite:///decoyable.db") | |
| self.engine = None | |
| self.SessionLocal = None | |
| self._initialized = False | |
| def initialize(self) -> None: | |
| """Initialize database connection and create tables.""" | |
| if self._initialized: | |
| return | |
| try: | |
| # Configure connection pooling based on database type | |
| if self.database_url.startswith("postgresql"): | |
| # PostgreSQL connection pooling | |
| self.engine = create_engine( | |
| self.database_url, | |
| poolclass=pool.QueuePool, | |
| pool_size=10, # Maximum connections in pool | |
| max_overflow=20, # Maximum overflow connections | |
| pool_timeout=30, # Timeout for getting connection from pool | |
| pool_recycle=3600, # Recycle connections after 1 hour | |
| pool_pre_ping=True, # Test connections before use | |
| echo=False, # Set to True for SQL debugging | |
| ) | |
| elif self.database_url.startswith("mysql"): | |
| # MySQL connection pooling | |
| self.engine = create_engine( | |
| self.database_url, | |
| poolclass=pool.QueuePool, | |
| pool_size=10, | |
| max_overflow=20, | |
| pool_timeout=30, | |
| pool_recycle=3600, | |
| pool_pre_ping=True, | |
| echo=False, | |
| ) | |
| else: | |
| # SQLite (file-based, no pooling needed) | |
| self.engine = create_engine( | |
| self.database_url, | |
| poolclass=pool.NullPool, # No pooling for SQLite | |
| echo=False, | |
| ) | |
| # Create session factory | |
| self.SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=self.engine) | |
| # Create tables | |
| Base.metadata.create_all(bind=self.engine) | |
| self._initialized = True | |
| logger.info("Database initialized successfully") | |
| except Exception as e: | |
| logger.error(f"Failed to initialize database: {e}") | |
| raise | |
| def get_session(self): | |
| """Get database session with automatic cleanup.""" | |
| if not self._initialized: | |
| self.initialize() | |
| session = self.SessionLocal() | |
| try: | |
| yield session | |
| except Exception as e: | |
| session.rollback() | |
| logger.error(f"Database session error: {e}") | |
| raise | |
| finally: | |
| session.close() | |
| def store_scan_result( | |
| self, | |
| scan_type: str, | |
| target_path: str, | |
| status: str, | |
| results: Optional[Dict[str, Any]] = None, | |
| error_message: Optional[str] = None, | |
| scan_duration: Optional[int] = None, | |
| file_count: Optional[int] = None, | |
| ) -> int: | |
| """Store scan result in database.""" | |
| with self.get_session() as session: | |
| scan_result = ScanResult( | |
| scan_type=scan_type, | |
| target_path=target_path, | |
| status=status, | |
| results=results, | |
| error_message=error_message, | |
| scan_duration=scan_duration, | |
| file_count=file_count, | |
| ) | |
| session.add(scan_result) | |
| session.commit() | |
| return scan_result.id | |
| def get_scan_results( | |
| self, scan_type: Optional[str] = None, limit: int = 50, offset: int = 0 | |
| ) -> List[Dict[str, Any]]: | |
| """Retrieve scan results from database.""" | |
| with self.get_session() as session: | |
| query = session.query(ScanResult) | |
| if scan_type: | |
| query = query.filter(ScanResult.scan_type == scan_type) | |
| query = query.order_by(ScanResult.created_at.desc()).limit(limit).offset(offset) | |
| results = [] | |
| for result in query.all(): | |
| results.append( | |
| { | |
| "id": result.id, | |
| "scan_type": result.scan_type, | |
| "target_path": result.target_path, | |
| "status": result.status, | |
| "results": result.results, | |
| "error_message": result.error_message, | |
| "created_at": (result.created_at.isoformat() if result.created_at else None), | |
| "scan_duration": result.scan_duration, | |
| "file_count": result.file_count, | |
| } | |
| ) | |
| return results | |
| def get_scan_cache(self, cache_key: str) -> Optional[Dict[str, Any]]: | |
| """Get cached scan result.""" | |
| with self.get_session() as session: | |
| cache_entry = ( | |
| session.query(ScanCache) | |
| .filter(ScanCache.cache_key == cache_key, ScanCache.expires_at > func.now()) | |
| .first() | |
| ) | |
| if cache_entry: | |
| # Update hit count | |
| cache_entry.hit_count += 1 | |
| session.commit() | |
| return { | |
| "scan_type": cache_entry.scan_type, | |
| "target_path": cache_entry.target_path, | |
| "results": cache_entry.results, | |
| "created_at": (cache_entry.created_at.isoformat() if cache_entry.created_at else None), | |
| "hit_count": cache_entry.hit_count, | |
| } | |
| return None | |
| def set_scan_cache( | |
| self, | |
| cache_key: str, | |
| scan_type: str, | |
| target_path: str, | |
| results: Dict[str, Any], | |
| ttl_seconds: int = 3600, | |
| ) -> None: | |
| """Set cached scan result.""" | |
| from datetime import datetime, timedelta | |
| expires_at = datetime.utcnow() + timedelta(seconds=ttl_seconds) | |
| with self.get_session() as session: | |
| # Check if cache entry already exists | |
| existing = session.query(ScanCache).filter(ScanCache.cache_key == cache_key).first() | |
| if existing: | |
| # Update existing entry | |
| existing.results = results | |
| existing.expires_at = expires_at | |
| existing.hit_count = 0 | |
| else: | |
| # Create new entry | |
| cache_entry = ScanCache( | |
| cache_key=cache_key, | |
| scan_type=scan_type, | |
| target_path=target_path, | |
| results=results, | |
| expires_at=expires_at, | |
| ) | |
| session.add(cache_entry) | |
| session.commit() | |
| def cleanup_expired_cache(self) -> int: | |
| """Clean up expired cache entries. Returns number of deleted entries.""" | |
| with self.get_session() as session: | |
| deleted_count = session.query(ScanCache).filter(ScanCache.expires_at <= func.now()).delete() | |
| session.commit() | |
| logger.info(f"Cleaned up {deleted_count} expired cache entries") | |
| return deleted_count | |
| def get_database_stats(self) -> Dict[str, Any]: | |
| """Get database statistics and health information.""" | |
| try: | |
| with self.get_session() as session: | |
| # Get table counts | |
| scan_results_count = session.query(ScanResult).count() | |
| cache_entries_count = session.query(ScanCache).count() | |
| # Get recent activity | |
| recent_scans = ( | |
| session.query(ScanResult) | |
| .filter(ScanResult.created_at >= func.now() - func.interval("1 day")) | |
| .count() | |
| ) | |
| return { | |
| "database_type": self.database_url.split("://")[0], | |
| "scan_results_count": scan_results_count, | |
| "cache_entries_count": cache_entries_count, | |
| "recent_scans_24h": recent_scans, | |
| "connection_pool_size": ( | |
| getattr(self.engine.pool, "size", "N/A") if hasattr(self.engine, "pool") else "N/A" | |
| ), | |
| "status": "healthy", | |
| } | |
| except Exception as e: | |
| logger.error(f"Database health check failed: {e}") | |
| return {"status": "unhealthy", "error": str(e)} | |
| # Global database manager instance | |
| _db_manager: Optional[DatabaseManager] = None | |
| def get_database_manager() -> DatabaseManager: | |
| """Get or create global database manager instance.""" | |
| global _db_manager | |
| if _db_manager is None: | |
| database_url = os.getenv("DATABASE_URL") | |
| _db_manager = DatabaseManager(database_url=database_url) | |
| _db_manager.initialize() | |
| return _db_manager | |
| def store_scan_result(scan_type: str, target_path: str, status: str, **kwargs) -> int: | |
| """Convenience function to store scan result.""" | |
| db = get_database_manager() | |
| return db.store_scan_result(scan_type, target_path, status, **kwargs) | |
| def get_scan_results(scan_type: Optional[str] = None, limit: int = 50) -> List[Dict[str, Any]]: | |
| """Convenience function to get scan results.""" | |
| db = get_database_manager() | |
| return db.get_scan_results(scan_type=scan_type, limit=limit) | |
Xet Storage Details
- Size:
- 11.6 kB
- Xet hash:
- 0fcd2906efaa827588b9fff349740a7104027086898afe7975136e92d501ec1f
·
Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.