neosqlite.collection.expr_temp_table module

Tier 2: Temporary table-based expression evaluation for complex $expr queries.

This module implements the second tier of the 3-tier $expr evaluation architecture: - Uses temporary tables for complex expressions that can’t be evaluated in a single query - Leverages SQLite JSON/JSONB native functions for optimal performance - Ensures data is converted to JSON format when moving to Python space

Tier 2 is used when: - Expressions are too complex for Tier 1 (single SQL WHERE clause) - Multiple intermediate calculations are needed - Aggregation or grouping is required within the expression

class neosqlite.collection.expr_temp_table.TempTableExprEvaluator(db_connection, data_column: str = 'data', translation_cache_size: int | None = 100)

Bases: object

Tier 2 evaluator using temporary tables for complex $expr expressions.

This evaluator: 1. Creates temporary tables to store intermediate results 2. Uses SQLite JSON/JSONB functions for efficient processing 3. Converts to JSON format when exporting to Python space

__init__(db_connection, data_column: str = 'data', translation_cache_size: int | None = 100)

Initialize the temporary table evaluator.

Parameters:

• db_connection – SQLite database connection

• data_column – Name of the column containing JSON data (default: “data”)

• translation_cache_size – Size of translation cache (None=default, 0=disabled)

property json_function_prefix: str

Get the appropriate JSON function prefix (json or jsonb).

is_cache_enabled() → bool

Check if translation cache is enabled.

get_cache_stats() → dict

Get cache statistics.

clear_cache() → None

Clear the translation cache.

cache_size() → int

Get current cache size.

cache_contains(expr: dict) → bool

Check if expression is in cache.

evict_from_cache(expr: dict) → bool

Evict expression from cache.

resize_cache(new_size: int) → None

Resize the cache.

dump_cache() → list

Dump cache contents for debugging.

evaluate(expr: dict[str, Any], collection_name: str, filter_expr: dict[str, Any] | None = None) → tuple[str | None, list[Any], list[str]]

Evaluate a $expr expression using temporary tables.

Parameters:

• expr – The $expr expression dictionary

• collection_name – Name of the collection table

• filter_expr – Optional additional filter to apply

Returns:

Tuple of (SQL query, parameters, table_names) or (None, [], []) for Python fallback

_analyze_complexity(expr: dict[str, Any]) → int

Analyze expression complexity to determine if Tier 2 is appropriate.

Complexity scoring: - Base expression: 1 point - Each nested operator: +1 point - Each arithmetic operator: +1 point - Each conditional operator: +2 points - Each array operator: +2 points - Each subquery/correlated expression: +3 points

Returns:

Complexity score (1-2: Tier 1, 3-10: Tier 2, 11+: Tier 3)

Return type:

int

_make_expr_key(expr: dict[str, Any]) → str

Create a cache key from expression structure.

Uses TranslationCache._extract_structure to create a hashable key that preserves field references ($field) but parameterizes literal values.

_build_from_cache(expr: dict[str, Any], collection_name: str, cached: tuple[str, tuple[str, ...]]) → tuple[str, list[Any], list[str]]

Build query from cached translation.

Parameters:

• expr – The original expression (for extracting actual parameter values)

• collection_name – Collection table name

• cached – Tuple of (where_clause_template, field_list)

Returns:

Tuple of (SQL query, parameters, table_names)

_extract_where_clause(full_query: str) → str

Extract WHERE clause from a full query string.

_extract_param_values_from_expr(expr: dict[str, Any]) → list[Any]

Extract actual parameter values from expression for cached query. Must follow the exact same traversal order as _convert_expr_to_temp_sql.

_build_tier2_query(expr: dict[str, Any], collection_name: str, filter_expr: dict[str, Any] | None = None) → tuple[str, list[Any], list[str]]

Build a Tier 2 query using temporary tables.

Strategy: 1. Create temp table with document IDs and intermediate calculations 2. Populate temp table with JSON-extracted values 3. Query main table joined with temp table using calculated values

Parameters:

• expr – The $expr expression

• collection_name – Collection table name

• filter_expr – Optional additional filter

Returns:

Tuple of (SQL query, parameters, table_names)

_create_temp_table(temp_table: str, collection_name: str, fields: list[str]) → None

Create a temporary table with extracted field values.

Parameters:

• temp_table – Name of the temporary table

• collection_name – Source collection name

• fields – List of field paths to extract

_build_main_query(expr: dict[str, Any], collection_name: str, temp_table: str, filter_expr: dict[str, Any] | None = None) → tuple[str, list[Any]]

Build the main query that uses the temporary table.

Parameters:

• expr – The $expr expression

• collection_name – Collection table name

• temp_table – Temporary table name

• filter_expr – Optional additional filter

Returns:

Tuple of (SQL query, parameters)

_build_expr_where_from_temp(expr: dict[str, Any], temp_table: str) → tuple[str, list[Any]]

Build WHERE clause using temporary table columns.

Parameters:

• expr – The $expr expression

• temp_table – Temporary table name

Returns:

Tuple of (WHERE clause, parameters)

_convert_expr_to_temp_sql(expr: dict[str, Any], temp_table: str) → tuple[str, list[Any]]

Convert expression to SQL using temporary table columns.

Parameters:

• expr – The $expr expression

• temp_table – Temporary table name

Returns:

Tuple of (SQL expression, parameters)

_convert_logical_to_temp_sql(operator: str, operands: list[Any], temp_table: str) → tuple[str, list[Any]]

Convert logical operators to SQL using temp table.

_convert_comparison_to_temp_sql(operator: str, operands: list[Any], temp_table: str) → tuple[str, list[Any]]

Convert comparison operators to SQL using temp table.

_convert_arithmetic_to_temp_sql(operator: str, operands: list[Any], temp_table: str) → tuple[str, list[Any]]

Convert arithmetic operators to SQL using temp table.

_convert_cond_to_temp_sql(operands: dict[str, Any], temp_table: str) → tuple[str, list[Any]]

Convert $cond operator to SQL CASE statement using temp table.

_convert_cmp_to_temp_sql(operands: list[Any], temp_table: str) → tuple[str, list[Any]]

Convert $cmp operator to SQL CASE statement using temp table.

_convert_math_to_temp_sql(operator: str, operands: list[Any], temp_table: str) → tuple[str, list[Any]]

Convert math operators to SQL using temp table.

_convert_operand_to_temp_sql(operand: Any, temp_table: str) → tuple[str, list[Any]]

Convert operand to SQL using temporary table columns.

Parameters:

• operand – Operand to convert

• temp_table – Temporary table name

Returns:

Tuple of (SQL expression, parameters)

_extract_field_references(expr: dict[str, Any]) → list[str]

Extract all unique field references from an expression.

Parameters:

expr – The $expr expression

Returns:

List of unique field paths

_extract_field_references_from_operand(operand: Any) → list[str]

Extract field references from an operand.

_sanitize_field_name(field_path: str) → str

Sanitize a field path for use as a SQL column name.

Parameters:

field_path – Field path (e.g., “stats.wins”)

Returns:

Sanitized column name (e.g., “stats_wins”)

cleanup_temp_tables() → None

Clean up all temporary tables created by this evaluator.