Comprehensive comparison for Database technology in Software Development applications

import mysql.connector
from mysql.connector import Error, pooling
from contextlib import contextmanager
import logging
from typing import Optional, Dict, List
import time

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

class AuroraConnectionPool:
    """Production-ready Aurora MySQL connection pool manager"""
    
    def __init__(self, host: str, database: str, user: str, password: str, pool_size: int = 10):
        """Initialize connection pool with Aurora cluster endpoint"""
        try:
            self.pool = pooling.MySQLConnectionPool(
                pool_name="aurora_pool",
                pool_size=pool_size,
                pool_reset_session=True,
                host=host,
                database=database,
                user=user,
                password=password,
                autocommit=False,
                connect_timeout=10,
                use_pure=False  # Use C extension for better performance
            )
            logger.info("Aurora connection pool initialized successfully")
        except Error as e:
            logger.error(f"Failed to create connection pool: {e}")
            raise
    
    @contextmanager
    def get_connection(self):
        """Context manager for safe connection handling"""
        connection = None
        try:
            connection = self.pool.get_connection()
            yield connection
        except Error as e:
            logger.error(f"Database connection error: {e}")
            if connection:
                connection.rollback()
            raise
        finally:
            if connection and connection.is_connected():
                connection.close()

class OrderService:
    """Service for handling e-commerce orders with Aurora"""
    
    def __init__(self, db_pool: AuroraConnectionPool):
        self.db_pool = db_pool
    
    def create_order(self, user_id: int, items: List[Dict]) -> Optional[int]:
        """Create order with transactional integrity across multiple tables"""
        max_retries = 3
        retry_count = 0
        
        while retry_count < max_retries:
            try:
                with self.db_pool.get_connection() as conn:
                    cursor = conn.cursor(dictionary=True)
                    
                    # Start transaction
                    conn.start_transaction()
                    
                    # Calculate total amount
                    total_amount = sum(item['price'] * item['quantity'] for item in items)
                    
                    # Insert order record
                    insert_order_query = """
                        INSERT INTO orders (user_id, total_amount, status, created_at)
                        VALUES (%s, %s, 'pending', NOW())
                    """
                    cursor.execute(insert_order_query, (user_id, total_amount))
                    order_id = cursor.lastrowid
                    
                    # Insert order items with inventory check
                    for item in items:
                        # Check inventory availability
                        cursor.execute(
                            "SELECT quantity FROM inventory WHERE product_id = %s FOR UPDATE",
                            (item['product_id'],)
                        )
                        inventory = cursor.fetchone()
                        
                        if not inventory or inventory['quantity'] < item['quantity']:
                            raise ValueError(f"Insufficient inventory for product {item['product_id']}")
                        
                        # Insert order item
                        insert_item_query = """
                            INSERT INTO order_items (order_id, product_id, quantity, price)
                            VALUES (%s, %s, %s, %s)
                        """
                        cursor.execute(insert_item_query, (
                            order_id,
                            item['product_id'],
                            item['quantity'],
                            item['price']
                        ))
                        
                        # Update inventory
                        update_inventory_query = """
                            UPDATE inventory 
                            SET quantity = quantity - %s,
                                updated_at = NOW()
                            WHERE product_id = %s
                        """
                        cursor.execute(update_inventory_query, (item['quantity'], item['product_id']))
                    
                    # Commit transaction
                    conn.commit()
                    logger.info(f"Order {order_id} created successfully for user {user_id}")
                    return order_id
                    
            except mysql.connector.errors.DatabaseError as e:
                # Handle deadlock or lock timeout - retry
                if e.errno in (1205, 1213):  # Lock wait timeout or deadlock
                    retry_count += 1
                    wait_time = 2 ** retry_count  # Exponential backoff
                    logger.warning(f"Deadlock detected, retry {retry_count}/{max_retries} after {wait_time}s")
                    time.sleep(wait_time)
                    continue
                else:
                    logger.error(f"Database error creating order: {e}")
                    raise
            except ValueError as e:
                logger.error(f"Business logic error: {e}")
                raise
            except Exception as e:
                logger.error(f"Unexpected error creating order: {e}")
                raise
        
        logger.error(f"Failed to create order after {max_retries} retries")
        return None

# Example usage
if __name__ == "__main__":
    # Initialize Aurora connection pool
    db_pool = AuroraConnectionPool(
        host="aurora-cluster.cluster-xxxxx.us-east-1.rds.amazonaws.com",
        database="ecommerce",
        user="app_user",
        password="secure_password",
        pool_size=20
    )
    
    # Create order service
    order_service = OrderService(db_pool)
    
    # Create sample order
    order_items = [
        {"product_id": 101, "quantity": 2, "price": 29.99},
        {"product_id": 102, "quantity": 1, "price": 49.99}
    ]
    
    order_id = order_service.create_order(user_id=12345, items=order_items)
    print(f"Order created with ID: {order_id}")