Session Management

@db_session
def your_function():
    """Decorator for automatic database session management.
    
    Automatically starts a database session at function entry and commits
    or rolls back at function exit. Can be used as decorator or context manager.
    
    Usage as decorator:
        @db_session
        def create_user(name, email):
            return User(name=name, email=email)
    
    Usage as context manager:
        with db_session:
            user = User(name="Alice", email="alice@example.com")
    """

class db_session:
    def __init__(self, retry=0, immediate=False, ddl=False, 
                 serializable=False, strict=False, optimistic=True,
                 retry_exceptions=(TransactionError,), allowed_exceptions=(),
                 sql_debug=None, show_values=None):
        """Initialize database session with configuration options.
        
        Args:
            retry: Number of retry attempts for failed transactions (default: 0)
            immediate: Use immediate transaction mode (default: False)
            ddl: Allow DDL operations in session (default: False)
            serializable: Use serializable isolation level (default: False)
            strict: Enable strict mode for session (default: False)
            optimistic: Use optimistic concurrency control (default: True)
            retry_exceptions: Exception types that trigger retry (default: (TransactionError,))
            allowed_exceptions: Exceptions that don't cause rollback (default: ())
            sql_debug: Enable SQL debugging for this session (default: None)
            show_values: Show parameter values in debug output (default: None)
        """
        
    def __enter__(self):
        """Enter session context."""
        
    def __exit__(self, exc_type, exc_val, exc_tb):
        """Exit session context with automatic commit/rollback."""
        
    def commit(self):
        """Commit current transaction."""
        
    def rollback(self):
        """Rollback current transaction."""

def flush():
    """Flush pending changes to database without committing transaction.
    
    Forces all pending INSERT, UPDATE, DELETE operations to be sent
    to the database, making them visible within the current transaction
    but not committed until the transaction ends.
    """

def commit():
    """Commit current transaction.
    
    Permanently saves all changes made during the current database session.
    After commit, a new transaction is automatically started.
    """

def rollback():
    """Rollback current transaction.
    
    Discards all changes made during the current database session.
    After rollback, a new transaction is automatically started.
    """

def make_proxy(entity_instance):
    """Create EntityProxy wrapper for entity instance.
    
    Args:
        entity_instance: Entity object to wrap
        
    Returns:
        EntityProxy: Proxy object that enables lazy loading
        
    Usage:
        user_proxy = make_proxy(user)
        # Proxy can be passed around and used later in different sessions
    """

class EntityProxy:
    def __init__(self, entity_class, pk):
        """Initialize entity proxy.
        
        Args:
            entity_class: Entity class
            pk: Primary key value
        """
        
    def get(self):
        """Get actual entity instance (requires active db_session)."""
        
    def exists(self):
        """Check if proxied entity still exists in database."""

@with_transaction
def your_function():
    """Legacy transaction decorator (deprecated).
    
    Use db_session instead. Provided for backwards compatibility only.
    """

from pony.orm import *

# Using as decorator
@db_session
def create_user(name, email):
    """Function automatically runs in database session."""
    user = User(name=name, email=email)
    return user  # Changes automatically committed at function exit

# Using as context manager
def manual_session_example():
    with db_session:
        user = User(name="Alice", email="alice@example.com")
        # Changes automatically committed when exiting context
        
    # Session is now closed, user object is detached

# Multiple operations in single session
@db_session 
def bulk_operations():
    # All operations happen in same transaction
    user1 = User(name="Alice", email="alice@example.com")
    user2 = User(name="Bob", email="bob@example.com")
    
    # Update existing user
    existing = User.get(name="Charlie")
    existing.email = "charlie.new@example.com"
    
    # Delete old users
    delete(u for u in User if u.last_login < date(2020, 1, 1))
    
    # All changes committed together at function exit

@db_session
def explicit_transaction_example():
    try:
        # Create some entities
        user = User(name="Alice", email="alice@example.com")
        order = Order(user=user, total=99.99, date=datetime.now())
        
        # Force changes to database (but not committed yet)
        flush()
        
        # Can now see the changes in current transaction
        print(f"User ID: {user.id}")  # ID is available after flush
        
        # Some business logic that might fail
        if order.total > 1000:
            raise ValueError("Order too large")
        
        # Explicitly commit if everything is ok
        commit()
        print("Transaction committed successfully")
        
    except Exception as e:
        print(f"Error occurred: {e}")
        rollback()  # Explicit rollback
        raise

# Session with custom configuration
with db_session(retry=3, optimistic=False, sql_debug=True):
    # Operations with retry on failure and pessimistic locking
    user = User.get_for_update(name="Alice")  # Row lock
    user.balance += 100
    
# Session allowing DDL operations
with db_session(ddl=True):
    db.execute("CREATE INDEX idx_user_email ON User(email)")
    
# Serializable isolation level for strict consistency
with db_session(serializable=True):
    # Critical financial operations
    account = Account.get(id=123)
    if account.balance >= 100:
        account.balance -= 100
        Transaction(account=account, amount=-100, type="withdrawal")

# Create proxy outside of session
user_proxy = None

@db_session
def create_proxy():
    global user_proxy
    user = User.get(name="Alice")
    user_proxy = make_proxy(user)
    # Proxy can be used outside of this session

# Use proxy in different session
@db_session  
def use_proxy():
    if user_proxy.exists():
        actual_user = user_proxy.get()
        print(f"User: {actual_user.name}")
    else:
        print("User no longer exists")

# Proxies are useful for caching and passing between functions
def process_users():
    proxies = []
    
    with db_session:
        # Create proxies for all users
        for user in User.select():
            proxies.append(make_proxy(user))
    
    # Later, in different sessions
    for proxy in proxies:
        with db_session:
            if proxy.exists():
                user = proxy.get() 
                # Process user...

from pony.orm import TransactionError, OptimisticCheckError

@db_session(retry=3, retry_exceptions=(TransactionError,))
def retry_on_conflict():
    """Automatically retry on transaction conflicts."""
    user = User.get(name="Alice")
    user.login_count += 1
    # If concurrent modification occurs, will retry up to 3 times

@db_session
def custom_error_handling():
    try:
        # Risky operations
        user = User(name="Duplicate", email="existing@example.com")
        commit()
        
    except IntegrityError as e:
        print(f"Constraint violation: {e}")
        rollback()
        
    except OptimisticCheckError as e:
        print(f"Concurrent modification detected: {e}")
        rollback()
        
    except Exception as e:
        print(f"Unexpected error: {e}")
        rollback()
        raise

# Advanced session configuration for specific use cases
@db_session(optimistic=False, immediate=True)
def pessimistic_operations():
    """Use pessimistic locking for critical operations."""
    account = Account.get_for_update(id=123)  # Immediate row lock
    account.balance -= 100
    # No optimistic checks, immediate locking

# Good: Keep sessions short and focused
@db_session
def process_single_order(order_id):
    order = Order[order_id]
    order.status = "processed"
    order.processed_at = datetime.now()
    # Auto-commit at function exit

# Good: Batch related operations
@db_session
def daily_user_cleanup():
    # All related operations in single transaction
    inactive_users = select(u for u in User if u.last_login < cutoff_date)
    for user in inactive_users:
        # Archive user data
        Archive(user_data=user.to_dict())
        user.delete()

# Avoid: Long-running sessions
def bad_long_session():
    with db_session:
        for i in range(10000):  # Don't do this
            user = User(name=f"User{i}")
            # Creates very large transaction
            
# Better: Batch processing with session management
def good_batch_processing():
    batch_size = 100
    for i in range(0, 10000, batch_size):
        with db_session:
            for j in range(i, min(i + batch_size, 10000)):
                user = User(name=f"User{j}")
            # Smaller transactions, better performance