Comprehensive comparison for Database technology in Software Development applications

import pyodbc
import logging
from typing import List, Dict, Optional
from datetime import datetime
from contextlib import contextmanager

class SynapseSoftwareMetricsRepository:
    """
    Production-ready repository for tracking software development metrics
    in Azure Synapse Analytics dedicated SQL pool.
    """
    
    def __init__(self, server: str, database: str, username: str, password: str):
        self.connection_string = (
            f"Driver={{ODBC Driver 17 for SQL Server}};"
            f"Server=tcp:{server},1433;"
            f"Database={database};"
            f"Uid={username};"
            f"Pwd={password};"
            f"Encrypt=yes;"
            f"TrustServerCertificate=no;"
            f"Connection Timeout=30;"
        )
        self.logger = logging.getLogger(__name__)
    
    @contextmanager
    def get_connection(self):
        """Context manager for database connections with proper cleanup."""
        conn = None
        try:
            conn = pyodbc.connect(self.connection_string)
            yield conn
            conn.commit()
        except Exception as e:
            if conn:
                conn.rollback()
            self.logger.error(f"Database error: {str(e)}")
            raise
        finally:
            if conn:
                conn.close()
    
    def insert_build_metrics(self, build_data: Dict) -> bool:
        """
        Insert build pipeline metrics with proper error handling.
        Uses hash distribution for optimal query performance.
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                
                query = """
                    INSERT INTO dbo.BuildMetrics 
                    (BuildId, ProjectName, BranchName, BuildStatus, 
                     Duration, TestsPassed, TestsFailed, CodeCoverage, 
                     CreatedDate)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                """
                
                cursor.execute(query, (
                    build_data.get('build_id'),
                    build_data.get('project_name'),
                    build_data.get('branch_name'),
                    build_data.get('status'),
                    build_data.get('duration_seconds'),
                    build_data.get('tests_passed', 0),
                    build_data.get('tests_failed', 0),
                    build_data.get('code_coverage', 0.0),
                    datetime.utcnow()
                ))
                
                self.logger.info(f"Build metrics inserted: {build_data.get('build_id')}")
                return True
                
        except pyodbc.IntegrityError as e:
            self.logger.warning(f"Duplicate build entry: {build_data.get('build_id')}")
            return False
        except Exception as e:
            self.logger.error(f"Failed to insert build metrics: {str(e)}")
            raise
    
    def get_project_statistics(self, project_name: str, days: int = 30) -> Optional[Dict]:
        """
        Retrieve aggregated project statistics using Synapse distributed query.
        Optimized with proper indexing and statistics.
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                
                query = """
                    SELECT 
                        ProjectName,
                        COUNT(*) as TotalBuilds,
                        SUM(CASE WHEN BuildStatus = 'Success' THEN 1 ELSE 0 END) as SuccessfulBuilds,
                        AVG(Duration) as AvgDuration,
                        AVG(CodeCoverage) as AvgCodeCoverage,
                        SUM(TestsPassed) as TotalTestsPassed,
                        SUM(TestsFailed) as TotalTestsFailed
                    FROM dbo.BuildMetrics
                    WHERE ProjectName = ?
                        AND CreatedDate >= DATEADD(day, -?, GETUTCDATE())
                    GROUP BY ProjectName
                    OPTION (LABEL = 'ProjectStats_Query')
                """
                
                cursor.execute(query, (project_name, days))
                row = cursor.fetchone()
                
                if row:
                    return {
                        'project_name': row[0],
                        'total_builds': row[1],
                        'successful_builds': row[2],
                        'success_rate': (row[2] / row[1] * 100) if row[1] > 0 else 0,
                        'avg_duration': float(row[3]) if row[3] else 0,
                        'avg_code_coverage': float(row[4]) if row[4] else 0,
                        'total_tests_passed': row[5],
                        'total_tests_failed': row[6]
                    }
                return None
                
        except Exception as e:
            self.logger.error(f"Failed to retrieve project statistics: {str(e)}")
            raise

# Example usage
if __name__ == "__main__":
    logging.basicConfig(level=logging.INFO)
    
    repo = SynapseSoftwareMetricsRepository(
        server="mysynapse.sql.azuresynapse.net",
        database="DevMetricsDB",
        username="sqladmin",
        password="SecurePassword123!"
    )
    
    # Insert build metrics
    build_data = {
        'build_id': 'BUILD-12345',
        'project_name': 'PaymentService',
        'branch_name': 'main',
        'status': 'Success',
        'duration_seconds': 450,
        'tests_passed': 287,
        'tests_failed': 0,
        'code_coverage': 85.5
    }
    
    repo.insert_build_metrics(build_data)
    
    # Retrieve statistics
    stats = repo.get_project_statistics('PaymentService', days=30)
    print(f"Project Statistics: {stats}")