Optimistic locking

Release Notes

New Feature: Optimistic Locking Support

ObjectQuel now supports optimistic locking through the @Orm\Version annotation, preventing lost updates in concurrent access scenarios.

Overview

Optimistic locking detects conflicts at commit time rather than locking records upfront. When multiple processes attempt to modify the same entity, only the first succeeds—subsequent attempts fail with a version mismatch error.

Usage

Add @Orm\Version to any property:

/**
 * @Orm\Column(name="version", type="integer", unsigned=true)
 * @Orm\Version
 */
protected ?int $version = null;

Supported Column Types

• integer: Auto-increments by 1 (starts at 1)
• datetime: Uses database NOW()
• uuid: Generates new GUID

Behavior

INSERT: Version initializes to 1 (integer), current time (datetime), or new GUID (uuid)

UPDATE: Version automatically increments/updates and is included in WHERE clause. Zero affected rows throws OrmException.

Example

// Process A loads entity
$product = $em->find(Product::class, 1); // version = 5
$product->setName("New Name");

// Process B updates same entity (version becomes 6)

// Process A flush fails
$em->flush(); // OrmException: version mismatch

Technical Implementation

• Version columns excluded from change detection
• Automatic version updates via SQL expressions (no round-trip required)
• Post-update SELECT fetches new version values for in-memory synchronization
• Values properly normalized according to property type annotations

Some things are better left untouched

Release Notes

🔒 New Feature: Immutable Entities

ObjectQuel now supports immutable entities for read-only data that should never be modified through the ORM.

What's New

Added @Orm\Immutable annotation to mark entities as read-only. Perfect for:

• Database views that aggregate data from multiple tables
• Read-only reference tables
• Audit logs and historical data
• Reports and dashboards with pre-calculated data

Usage

/**
 * @Orm\Table(name="v_customer_summary")
 * @Orm\Immutable
 */
class CustomerSummary {
    /**
     * @Orm\Column(name="customer_id", type="integer", primary_key=true)
     */
    private ?int $customerId = null;
    
    /**
     * @Orm\Column(name="total_orders", type="integer")
     */
    private int $totalOrders;
    
    /**
     * @Orm\Column(name="total_revenue", type="decimal")
     */
    private float $totalRevenue;
}

Protection

Immutable entities throw OrmException during flush for:

• INSERT operations
• UPDATE operations
• DELETE operations

Query operations (find(), findBy(), executeQuery()) work normally.

Why This Matters

Legacy databases often contain views and read-only tables that shouldn't be modified. The @Immutable annotation provides runtime protection against accidental mutations while keeping your ORM layer clean and expressive.

Range of Possibilities

Release Notes

New Feature: Temporary Ranges (Subqueries)

ObjectQuel now supports temporary ranges, enabling inline subqueries that create intermediate result sets for advanced query composition.

What's New

Temporary Range Syntax

• Define subqueries inline using the familiar range syntax
• Create intermediate result sets that can be joined with other ranges
• Compute derived values, perform aggregations, or pre-filter data before joining

Intelligent JOIN Optimization

• Automatic nullability analysis determines optimal JOIN types
• LEFT JOIN for potentially NULL fields (aggregates, nullable columns)
• INNER JOIN for guaranteed non-NULL fields (performance optimization)
• Aggregate functions (MAX, MIN, AVG, etc.) correctly handled as nullable

Example Usage

// Find products priced above their category average
$results = $entityManager->executeQuery("
    range of avgPrices is (
        range of p is App\\Entity\\ProductEntity
        retrieve (categoryId=p.categoryId, avg=AVG(p.price))
    )
    range of p is App\\Entity\\ProductEntity via p.categoryId=avgPrices.categoryId
    retrieve (p.name, p.price, avgPrices.avg)
    where p.price > avgPrices.avg
");

Technical Details

• Subqueries execute first, creating temporary tables in the database
• JOIN type selection based on field nullability from source columns
• Complex expressions conservatively treated as nullable
• Automatic removal of unreferenced temporary ranges during optimization

Use Cases

• Pre-filtering large datasets before joining
• Computing derived values (profit margins, percentages, ratios)
• Combining aggregated data with detail records
• Multi-stage data transformations

Performance Considerations

• Each temporary range creates a database temporary table
• Use appropriate indexes on joined columns
• Keep subqueries focused for optimal performance
• Very large result sets may impact memory usage

Every Type Has Its Place

Release Notes

We're introducing native enum type support in ObjectQuel ORM, providing type-safe enumeration handling with automatic database synchronization and validation.

Enum Column Definition

Define enum columns in your entities using the new enumType parameter:

use App\Enums\TestEnum;

/**
 * @Orm\Column(name="test_enum", type="enum", enumType=App\Enums\TestEnum::class)
 */
protected TestEnum $testEnum;

Key Features

Database-Aware Migration Generation

The migration system intelligently handles enum types based on your database capabilities:

• MySQL/Databases with native enum support: Generates proper ENUM column definitions with all valid values from your PHP enum
• Other databases: Falls back to VARCHAR/STRING columns when native enums aren't supported

Dual-Layer Validation

ObjectQuel validates enum values at two levels:

1. Database Level (MySQL and compatible databases): Database enforces enum constraints natively
2. Application Level (all databases): PHP validates values against your enum class before persistence

This ensures data integrity regardless of database engine capabilities.

Automatic Migration Handling

When you run php vendor/bin/sculpt make:migrations, the system:

• Detects enum column additions and modifications
• Generates appropriate migration code for your database type
• Handles enum value list changes (adding/removing values)
• Manages conversions between enum and string types

Migration Code Examples

For MySQL:

->addColumn('status', 'enum', [
    'values' => ['draft', 'published', 'archived'],
    'null' => false
])

For PostgreSQL/SQLite:

->addColumn('status', 'string', [
    'limit' => 50,
    'null' => false
])

Sweet Query O' Mine

Release Notes

Drastically Improved Aggregate Handling

We're excited to announce a complete overhaul of ObjectQuel's aggregate processing system, delivering substantial performance improvements and automatic query optimization.

🚀 Key Improvements

Intelligent Aggregate Strategy Selection

ObjectQuel now automatically chooses the optimal execution strategy for each aggregate function:

• DIRECT: Keeps aggregates in the main query when most efficient
• SUBQUERY: Moves complex aggregates to correlated subqueries to reduce join overhead
• WINDOW: Converts suitable aggregates to window functions for maximum performance

Automatic GROUP BY Management

• Smart Detection: Automatically identifies when mixed aggregate/non-aggregate queries need GROUP BY clauses
• Standards Compliance: Ensures all queries remain SQL standards compliant

Advanced Window Function Support

• Automatic Conversion: Eligible aggregates are automatically rewritten as window functions using OVER() clause
• Performance Boost: Window functions eliminate expensive grouping operations
• Intelligent Validation: Only converts aggregates when semantically safe and performance-beneficial

🔧 Technical Enhancements

Query Structure Optimization

• Unused Range Removal: Eliminates unnecessary table joins in aggregate-only queries
• EXISTS Rewriting: Converts filter-only joins to efficient EXISTS subqueries
• Self-Join Simplification: Optimizes redundant self-joins into EXISTS conditions

Advanced Correlation Analysis

• Range Overlap Detection: Analyzes table relationships to determine optimal aggregate placement
• Cross-Reference Validation: Ensures aggregates and non-aggregates reference compatible data sources
• Semantic Preservation: All optimizations maintain original query semantics

Enhanced Pagination Integration

• Primary Key Optimization: Streamlined pagination using efficient primary key fetching
• Validation Control: Optional validation skipping for pre-validated datasets
• Memory Efficiency: Reduces memory footprint during paginated aggregate queries

📊 Performance Impact

These improvements deliver significant performance gains:

• Reduced Join Width: Aggregates are isolated to minimize expensive join operations
• Optimized Execution Plans: Database query planners can better optimize the generated SQL
• Window Function Acceleration: Modern database window function optimizations are leveraged automatically
• Memory Efficiency: Correlated subqueries reduce intermediate result set sizes

🔄 Backward Compatibility

All existing ObjectQuel queries continue to work without modification. The optimization system operates transparently, automatically applying the best strategy for each query pattern.

Sweet Queries Are Made of JOINS

Release Notes

New Features

IFNULL Function Support

Added support for the IFNULL function, which provides null value handling capabilities in queries. The function maps directly to SQL's COALESCE function, allowing developers to specify alternative values when expressions evaluate to NULL.

Usage:

// Returns 'Unknown' when customer.name is NULL
IFNULL(customer.name, 'Unknown')

Performance Improvements

Enhanced JOIN Optimization

Significantly improved the LEFT JOIN to INNER JOIN conversion logic by incorporating nullable field analysis in WHERE clause conditions. The optimizer now examines column nullability when determining safe JOIN type conversions.

Key Improvements:

• Nullability-Based Analysis: The optimizer now checks whether fields referenced in WHERE conditions are nullable before converting LEFT JOINs to INNER JOINs
• Safer Optimizations: Only converts JOIN types when the optimization won't change query semantics or filter out valid results
• Enhanced Decision Tree: Implements a sophisticated analysis that prioritizes NULL checks, then examines field references and nullability

Optimization Logic:

1. Expressions with explicit NULL checks remain as LEFT JOINs
2. Field references without NULL checks are analyzed for nullability
3. LEFT JOINs are converted to INNER JOINs only when referencing non-nullable fields
4. This ensures query correctness while maximizing performance opportunities

Optimized Aggregate Function Processing

Redesigned the aggregate function handling system to use linear flow decision-making, replacing complex nested conditional logic with a streamlined optimization strategy pattern.

Performance Enhancements:

• Strategy Pattern Implementation: New OptimizationStrategy class eliminates complex boolean checks with clear strategy types (CONSTANT_TRUE, SIMPLE_EXISTS, NULL_CHECK, JOIN_BASED, SUBQUERY)
• QueryAnalyzer Integration: Centralized analysis logic with caching to avoid repeated computation of optimization decisions
• Linear Flow Processing: Simplified aggregate operations (COUNT, SUM, AVG, MIN, MAX) with two-path decision making - either calculate in main query or use subquery
• Improved ANY() Optimization: Enhanced handling of existence checks using decision-tree optimization

Supported Optimizations:

• Single range queries optimize to constant true
• Equivalent ranges with simple equality joins optimize to constant true
• Base range references optimize to constant true
• No-join expressions use simple existence checks
• Optional ranges use specialized NULL checking
• Required and joined ranges leverage main query JOINs

Are you ok, ANY()?

Release Notes

New Features & Enhancements

🔍 New Aggregate Function: ANY()

The ANY() function provides a powerful existence checker that returns 1 if related records exist and 0 if they don't. This function intelligently optimizes its behavior based on the query context and relationship complexity.

Key Features:

• Smart Optimization: Uses efficient JOIN operations for simple relationships and optimized EXISTS subqueries for complex scenarios
• Dual Context Support: Works in both RETRIEVE and WHERE clauses
• Performance Aware: Automatically chooses the most efficient query strategy based on relationship types

Example Usage:

In RETRIEVE clauses (checking existence):

// Check which customers have orders
$results = $entityManager->executeQuery("
    range of c is App\\Entity\\CustomerEntity
    range of o is App\\Entity\\OrderEntity via c.orders
    retrieve (c.name, ANY(o.orderId))
");

foreach ($results as $row) {
    $customerName = $row['c.name'];
    $hasOrders = $row['ANY(o.orderId)'];  // 1 if customer has orders, 0 if not
}

In WHERE clauses (filtering by existence):

// Find all customers who have at least one order
$results = $entityManager->executeQuery("
    range of c is App\\Entity\\CustomerEntity
    range of o is App\\Entity\\OrderEntity via c.orders
    retrieve (c)
    where ANY(o.orderId) = 1
");

🧮 New Aggregate Function: SUMU()

ObjectQuel now includes the SUMU() aggregate function, which calculates the sum of unique values in a dataset. This complements our existing suite of aggregate functions and provides more precise calculations when dealing with datasets that may contain duplicate values.

Example Usage:

$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (SUM(p.price), SUMU(p.price))
    where p.category = :category
", [
    'category' => 'Electronics'
]);

$totalValue = $results[0]['SUM(p.price)'];      // Sum of all prices
$uniqueValue = $results[0]['SUMU(p.price)'];   // Sum of unique prices only

🔧 Enhanced Namespace Resolution

The ObjectQuel query processor now features intelligent namespace completion that improves developer experience and reduces the need for fully-qualified entity names in queries.

What's New:

• Automatic Use Clause Detection: The query processor now reads use statements from your PHP files to understand available namespaces
• Smart Entity Resolution: When entity names are not fully qualified, ObjectQuel attempts to resolve them using imported namespaces before falling back to the default entity directory
• Improved Developer Experience: Write cleaner, more maintainable queries with shorter entity references

Before:

// Previously, short entity names were automatically prepended with the default entity namespace
$results = $entityManager->executeQuery("
    range of p is ProductEntity
    range of c is CategoryEntity via p.categories
    retrieve (p)
");
// ObjectQuel would assume: App\Entity\ProductEntity, App\Entity\CategoryEntity

Now:

// With proper use statements in your PHP file:
use My\Custom\Namespace\ProductEntity;
use Another\Namespace\CategoryEntity;

// ObjectQuel now respects your imported namespaces
$results = $entityManager->executeQuery("
    range of p is ProductEntity
    range of c is CategoryEntity via p.categories
    retrieve (p)
");
// ObjectQuel resolves: My\Custom\Namespace\ProductEntity, Another\Namespace\CategoryEntity

This enhancement provides much greater flexibility for organizing entities across different namespaces while maintaining clean, readable query syntax.

---

Complete Aggregate Function Reference

ObjectQuel now supports the following aggregate functions:

Function	Description
COUNT	Returns the count of rows
COUNTU	Returns the count of unique rows
MIN	Returns the minimum value
MAX	Returns the maximum value
AVG	Returns the average value
AVGU	Returns the average of unique values
SUM	Returns the sum of all values
SUMU	Returns the sum of unique values (NEW)
ANY	Returns 1 if related records exist, 0 otherwise (NEW)

These enhancements continue ObjectQuel's mission to provide an intuitive, powerful, and developer-friendly approach to object-relational mapping that bridges the gap between object-oriented programming and database operations.

Repository Code Generator

Release Notes

Added a new console command make:repository that automatically generates repository classes for existing entities in ObjectQuel applications. This command streamlines the development process by creating boilerplate repository code with proper structure and validation.

Key Features

Automatic Repository Generation

• Creates repository classes that extend the base Repository class
• Generates proper namespace (App\Repositories) and class structure
• Includes complete PHP class with constructor and entity association

Smart Entity Validation

• Validates that the target entity exists before creating repository
• Uses EntityStore to verify entity availability
• Provides helpful error messages with suggestions for available entities

Flexible Input Handling

• Accepts entity name as command argument or interactive prompt
• Intelligently removes common suffixes ('Repository', 'Entity') from input
• Handles various entity naming conventions automatically

Command Syntax

# Basic usage
php sculpt make:repository User

# With force overwrite
php sculpt make:repository Product --force

# Interactive mode (prompts for entity name)
php sculpt make:repository

Generated Repository Structure

The command generates a complete repository class with:

• Proper namespace declaration (App\Repositories)
• Entity import statement
• Repository class extending base Repository
• Constructor with EntityManager dependency injection
• PHPDoc comments for documentation

Shorthand Window Syntax

Release Notes

🚀 New Feature: Shorthand Window Syntax

We're excited to introduce a more concise and developer-friendly pagination syntax to ObjectQuel. You can now use shorthand notation for pagination operations, making your queries cleaner and more intuitive.

What's New

Shorthand Window Syntax: window 0,10

In addition to the existing verbose syntax, ObjectQuel now supports a compact comma-separated format for pagination:

// NEW: Shorthand syntax (recommended)
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p)
    window 0,10
");

// EXISTING: Standard syntax (still supported)
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p)
    window 0 using window_size 10
");

Key Benefits

• Cleaner Code: Reduce query verbosity with compact pagination syntax
• Faster Development: Write pagination queries more quickly

Pagination fixes

Release Notes

🔧 Major Bug Fixes

Pagination System Overhaul

We've completely restructured the pagination handling system to provide more robust, efficient, and reliable pagination functionality.

Key Improvements:

• Enhanced Primary Key Detection: Fixed critical issues in fetchPrimaryKeyOfMainRange() method where pagination would fail when unable to identify the main entity's primary key
• Improved Edge Case Handling: Added graceful handling for empty result sets and invalid pagination parameters through addImpossibleCondition() method
• Better IN Condition Management: Enhanced addInConditionForPagination() to more intelligently handle existing IN conditions and prevent query conflicts
• Robust Error Recovery: Improved fallback mechanisms when pagination setup encounters unexpected conditions

Technical Details:

• Refactored pagination methods with cleaner separation of concerns between validation and processing phases
• Enhanced getPageSubset() logic for better boundary handling and array slicing
• Improved memory efficiency by optimizing primary key fetching during pagination preparation
• Added comprehensive validation for pagination window parameters

📋 Breaking Changes

None - This release maintains full backward compatibility with existing ObjectQuel queries and configurations.