v0.8.0

[cwt]

NeoSQLite v0.8.0 Release Notes

Overview

This release introduces significant enhancements to NeoSQLite's aggregation pipeline processing capabilities with the implementation of a three-tier processing approach that dramatically expands the range of pipelines that can be processed efficiently with SQL optimization. The release also includes comprehensive binary data handling improvements, enhanced temporary table aggregation with support for additional stages like $addFields, and a unified SQL translation framework for better code maintainability.

Highlights

Three-Tier Aggregation Pipeline Processing

• Enhanced Processing Pipeline: NeoSQLite now uses a sophisticated three-tier approach for aggregation processing:
  1. Single SQL Query (Fastest): Try to process the entire pipeline with a single optimized SQL query
  2. Temporary Table Aggregation (Intermediate): Process compatible groups of stages using temporary tables
  3. Python Fallback (Slowest but most flexible): Fall back to Python implementation for unsupported operations
• Expanded SQL Optimization Coverage: The temporary table approach increases SQL optimization coverage from ~60% to over 85% of common aggregation pipelines
• Better Resource Management: Intermediate results are stored in temporary tables rather than Python memory, enabling processing of larger datasets
• Position Independence: Operations like $lookup can now be used in any pipeline position, not just at the end

Binary Data Handling Improvements

• Preserved Binary Subtypes: Binary objects now preserve their subtypes (FUNCTION, UUID, MD5, etc.) during insert and update operations
• Automatic Bytes Conversion: Raw bytes are automatically converted to Binary objects with proper JSON serialization
• SQL Update Support: Binary data can now be used in SQL-based update operations with proper serialization

Enhanced Temporary Table Aggregation

• Additional Stage Support: Added support for $addFields stage in temporary table aggregation
• Sequential Unwind Processing: Fixed nested array $unwind operations to handle sequential dependencies correctly
• Simplified Group Optimization: Improved $unwind + $group optimization by delegating to the general _build_group_query method

Unified SQL Translation Framework

• Code Reorganization: Extracted SQL translation logic into a separate sql_translator_unified.py module
• Shared Implementation: Both QueryEngine and TemporaryTableAggregationProcessor now use the same SQL translation framework
• Improved Maintainability: Reduced code duplication and improved consistency across SQL generation

New Features

Three-Tier Processing Implementation

• TemporaryTableAggregationProcessor: New class for processing complex pipelines using temporary tables
• DeterministicTempTableManager: Generates SHA256-based deterministic temporary table names
• Aggregation Pipeline Context: Context manager for automatic resource cleanup with guaranteed transaction atomicity
• Enhanced Pipeline Validation: Improved can_process_with_temporary_tables function for pipeline compatibility checking

Extended Stage Support

• $addFields Support: Temporary table aggregation now supports $addFields stage for adding new fields to documents
• Advanced Sort/Skip/Limit: Enhanced support for consecutive $sort, $skip, and $limit stages
• Complex Pipeline Combinations: Support for multi-stage pipelines combining different operations

Hybrid Pipeline Operations

• Granular Fallback: Individual unsupported stages can fall back to Python processing while keeping others in SQL
• Streaming Results: Temporary tables enable streaming of results for memory-efficient processing
• Parallel Processing Potential: Framework for processing independent pipeline branches in parallel

Performance Improvements

• 1.2x faster average performance across supported aggregation operations through temporary table processing
• Reduced memory usage for complex pipelines by storing intermediate results in database rather than Python memory
• Better scalability for larger datasets that might not fit in Python memory
• Improved resource management with automatic cleanup of temporary resources

Technical Benefits

• Expanded SQL Coverage: Process 85%+ of common aggregation pipelines at SQL level vs. ~60% previously
• Position Independence: Remove position constraints for optimized stages like $lookup
• Better Error Handling: Graceful fallback between processing approaches
• Enhanced Maintainability: Unified SQL translation framework reduces code duplication
• Robust Resource Management: Guaranteed cleanup with transaction-based atomicity

Breaking Changes

There are no intentional breaking changes in this release. All existing APIs and functionality remain fully compatible with previous versions.

Installation

# Standard installation
pip install neosqlite

# For enhanced JSON/JSONB support
pip install neosqlite[jsonb]

# For memory-constrained processing of large result sets
pip install neosqlite[memory-constrained]

Notable Features

Three-Tier Processing Example

# Complex pipeline now processed more efficiently
pipeline = [
    {"$match": {"status": "active"}},
    {"$unwind": "$tags"},
    {"$unwind": "$categories"},
    {"$group": {"_id": "$tags", "count": {"$sum": 1}}},
    {"$sort": {"count": -1}},
    {"$limit": 10}
]

# NeoSQLite will automatically:
# 1. Try single SQL optimization (fails for this complex pipeline)
# 2. Use temporary table approach (succeeds with stages 1-5)
# 3. Fall back to Python only for unsupported operations (none in this case)
cursor = collection.aggregate(pipeline)

Binary Data Handling Example

# Raw bytes automatically converted to Binary objects
document = {
    "name": "File Data",
    "raw_data": b"\x00\x01\x02\x03",
    "typed_data": Binary(b"function code", Binary.FUNCTION_SUBTYPE)
}

# Insert operation preserves subtypes
collection.insert_one(document)

# Retrieved document maintains proper binary handling
retrieved = collection.find_one({"name": "File Data"})
assert isinstance(retrieved["raw_data"], Binary)
assert retrieved["raw_data"].subtype == Binary.BINARY_SUBTYPE
assert isinstance(retrieved["typed_data"], Binary)
assert retrieved["typed_data"].subtype == Binary.FUNCTION_SUBTYPE

Enhanced Temporary Table Aggregation Example

# Pipeline with multiple stages now supported
pipeline = [
    {"$match": {"department": "Engineering"}},
    {"$unwind": "$skills"},
    {"$addFields": {"skill_upper": {"$toUpper": "$skills"}}},
    {"$group": {"_id": "$skill_upper", "count": {"$sum": 1}}},
    {"$sort": {"count": -1}}
]

# Uses temporary table approach for efficient processing
cursor = collection.aggregate(pipeline)

This release represents a significant advancement in NeoSQLite's aggregation pipeline capabilities, providing dramatic expansion in SQL optimization coverage while maintaining full backward compatibility and enhancing the overall robustness and performance of the system.

---

This discussion was created from the release v0.8.0.