6.3Testing Stored Procedures

Basic Function Test

def test_calculate_order_total(db):
    cursor = db.cursor()

    # Set up test data
    cursor.execute("INSERT INTO orders (id, status) VALUES ('test-order', 'pending')")
    cursor.execute("""
        INSERT INTO line_items (order_id, quantity, unit_price) VALUES
        ('test-order', 2, 10.00),
        ('test-order', 1, 25.50)
    """)

    # Call the stored procedure
    cursor.execute("SELECT calculate_order_total('test-order')")
    total = cursor.fetchone()[0]

    # Verify calculation
    expected = (2 * 10.00) + (1 * 25.50)  # 45.50
    assert total == expected, f"Expected {expected}, got {total}"

Testing Edge Cases

def test_calculate_order_total_empty_order(db):
    """Order with no line items should return 0."""
    cursor = db.cursor()
    cursor.execute("INSERT INTO orders (id, status) VALUES ('empty-order', 'pending')")
    cursor.execute("SELECT calculate_order_total('empty-order')")
    assert cursor.fetchone()[0] == 0

def test_calculate_order_total_nonexistent(db):
    """Non-existent order should raise an error or return NULL."""
    cursor = db.cursor()
    cursor.execute("SELECT calculate_order_total('nonexistent')")
    result = cursor.fetchone()[0]
    assert result is None or result == 0

def test_calculate_order_total_large_quantities(db):
    """Test with large quantities to check for overflow."""
    cursor = db.cursor()
    cursor.execute("INSERT INTO orders (id, status) VALUES ('big-order', 'pending')")
    cursor.execute("""
        INSERT INTO line_items (order_id, quantity, unit_price) VALUES
        ('big-order', 999999, 9999.99)
    """)
    cursor.execute("SELECT calculate_order_total('big-order')")
    total = cursor.fetchone()[0]
    expected = 999999 * 9999.99
    assert abs(total - expected) < 0.01  # Allow small floating-point difference

def test_calculate_order_total_precision(db):
    """Test decimal precision (common bug with money calculations)."""
    cursor = db.cursor()
    cursor.execute("INSERT INTO orders (id, status) VALUES ('precise-order', 'pending')")
    cursor.execute("""
        INSERT INTO line_items (order_id, quantity, unit_price) VALUES
        ('precise-order', 3, 0.10)
    """)
    cursor.execute("SELECT calculate_order_total('precise-order')")
    total = cursor.fetchone()[0]
    # 3 * 0.10 should be exactly 0.30, not 0.30000000000000004
    assert total == 0.30, f"Precision error: expected 0.30, got {total}"