neosqlite.collection.sql_translator_unified module

Unified SQL translation framework for NeoSQLite.

This module provides a unified approach to translating MongoDB-style queries into SQL statements that can be used both for direct execution and for temporary table generation.

neosqlite.collection.sql_translator_unified._empty_result() → tuple[str, list[Any]][source]

Return an empty result tuple for fallback cases.

Returns:

A tuple containing an empty string and an empty list.

Return type:

tuple[str, list[Any]]

neosqlite.collection.sql_translator_unified._text_search_fallback() → tuple[None, list[Any]][source]

Return a text search result tuple for fallback cases.

Returns:

A tuple containing None and an empty list.

Return type:

tuple[None, list[Any]]

neosqlite.collection.sql_translator_unified._convert_to_bitmask(value: Any) → int | None[source]

Convert a value to a bitmask for bitwise operators.

Parameters:

value – The value to convert (int, list of bit positions, or iterable)

Returns:

Integer bitmask or None if conversion fails

class neosqlite.collection.sql_translator_unified.SQLFieldAccessor(data_column: str = 'data', id_column: str = 'id', jsonb_supported: bool = False)[source]

Bases: object

Handles field access patterns for different contexts.

This class provides methods to generate appropriate SQL expressions for accessing fields in different contexts, such as direct table access or temporary table access.

__init__(data_column: str = 'data', id_column: str = 'id', jsonb_supported: bool = False)[source]

Initialize the SQLFieldAccessor with column names.

Parameters:

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

  • id_column – The name of the column containing document IDs (default: “id”)

  • jsonb_supported – Whether JSONB functions are supported (default: False)

_parse_json_path(field: str) → str[source]

Convert dot notation with array indexing to JSON path syntax.

Supports: - Simple fields: “name” -> “$.name” - Nested fields: “address.street” -> “$.address.street” - Array indexing: “tags[0]” -> “$.tags[0]” - Nested array access: “orders.items[2].name” -> “$.orders.items[2].name” - Complex paths: “a.b[0].c[1].d” -> “$.a.b[0].c[1].d”

Parameters:

field (str) – The field path in dot notation with optional array indices

Returns:

Properly formatted JSON path

Return type:

str

get_field_access(field: str, context: str = 'direct', query: dict | None = None) → str[source]

Generate field access SQL with enhanced JSON path support.

This method generates appropriate SQL expressions for accessing fields based on the field name and context. For the special “_id” field, it returns the _id column name for direct access. For other fields, it generates a json_extract or jsonb_extract expression to access the field from the JSON data column, with support for complex JSON paths including array indexing.

Parameters:

  • field – The field name to access

  • context – The context for field access (default: “direct”)

  • query – The query being processed (used to determine if text search is needed)

Returns:

SQL expression for accessing the field

class neosqlite.collection.sql_translator_unified.SQLOperatorTranslator(field_accessor: SQLFieldAccessor | None = None, json_each_function: str | None = None)[source]

Bases: object

Translates MongoDB operators to SQL expressions.

This class handles the translation of MongoDB query operators (like $eq, $gt, $in, etc.) into equivalent SQL expressions. It uses an SQLFieldAccessor to generate appropriate field access expressions for different contexts.

__init__(field_accessor: SQLFieldAccessor | None = None, json_each_function: str | None = None)[source]

Initialize the SQLOperatorTranslator with an optional field accessor.

Parameters:

  • field_accessor – An SQLFieldAccessor instance to use for field access expressions. If None, a default SQLFieldAccessor will be created.

  • json_each_function – The json_each function to use (‘jsonb_each’ or ‘json_each’). If None, defaults to ‘json_each’.

translate_operator(field_access: str, operator: str, value: Any) → tuple[str | None, list[Any]][source]

Translate a MongoDB operator to SQL.

This method handles the translation of MongoDB query operators into equivalent SQL expressions. It supports various operators including comparison operators ($eq, $gt, $lt, $gte, $lte, $ne), array operators ($in, $nin), existence checks ($exists), modulo operations ($mod), array size checks ($size), and substring searches ($contains).

Special handling is included for Binary objects which are serialized using the compact format for SQL comparisons.

Parameters:

  • field_access – The SQL expression for accessing the field

  • operator – The MongoDB operator ($eq, $gt, etc.)

  • value – The value to compare against

Returns:

Tuple of (SQL expression, parameters) or (None, []) if unsupported

_is_datetime_value(value: Any) → bool[source]

Check if a value is a datetime string.

Parameters:

value – Value to check

Returns:

True if value is a datetime string, False otherwise

class neosqlite.collection.sql_translator_unified.SQLClauseBuilder(field_accessor: SQLFieldAccessor | None = None, operator_translator: SQLOperatorTranslator | None = None, json_each_function: str | None = None)[source]

Bases: object

Builds SQL clauses with reusable components.

This class provides methods to build various SQL clauses including WHERE, ORDER BY, and LIMIT/OFFSET clauses. It uses SQLFieldAccessor for field access and SQLOperatorTranslator for operator translations to create SQL expressions from MongoDB-style query specifications.

__init__(field_accessor: SQLFieldAccessor | None = None, operator_translator: SQLOperatorTranslator | None = None, json_each_function: str | None = None)[source]

Initialize the SQLClauseBuilder with optional field accessor and operator translator.

Parameters:

  • field_accessor – An SQLFieldAccessor instance to use for field access expressions. If None, a default SQLFieldAccessor will be created.

  • operator_translator – An SQLOperatorTranslator instance to use for operator translations. If None, a default SQLOperatorTranslator will be created.

  • json_each_function – The json_each function to use (‘jsonb_each’ or ‘json_each’). If provided and operator_translator is None, a new SQLOperatorTranslator will be created with this function.

_build_logical_condition(operator: str, conditions: list[dict[str, Any]], context: str = 'direct') → tuple[str | None, list[Any]][source]

Build a logical condition ($and, $or, $nor, $not).

This method handles the construction of SQL expressions for MongoDB logical operators. It recursively processes nested conditions and builds appropriate SQL expressions with proper parentheses grouping.

Parameters:

  • operator – The logical operator ($and, $or, $nor)

  • conditions – List of condition dictionaries

  • context – The context for field access (default: “direct”)

Returns:

Tuple of (SQL expression, parameters) or (None, []) if unsupported

build_where_clause(query: dict[str, Any], context: str = 'direct', is_nested: bool = False, query_param: dict | None = None) → tuple[str | None, list[Any]][source]

Build a WHERE clause from a MongoDB-style query.

This method translates a MongoDB-style query specification into an SQL WHERE clause. It handles both simple field conditions and complex logical operators ($and, $or, $nor, $not). The method recursively processes nested conditions and properly groups them with parentheses.

Parameters:

  • query – The MongoDB-style query

  • context – The context for field access (default: “direct”)

  • is_nested – Whether this is a nested condition within a logical operator (default: False)

  • query_param – The original query being processed (used to determine if text search is needed)

Returns:

Tuple of (WHERE clause, parameters) or (None, []) if unsupported

build_order_by_clause(sort_spec: dict[str, Any], context: str = 'direct') → str[source]

Build an ORDER BY clause from a sort specification.

This method translates a MongoDB-style sort specification into an SQL ORDER BY clause. It handles multiple sort fields with their respective sort directions (ascending or descending).

Parameters:

  • sort_spec – The sort specification mapping field names to sort directions

  • context – The context for field access (default: “direct”)

Returns:

ORDER BY clause as a string

build_limit_offset_clause(limit_value: int | None = None, skip_value: int = 0) → str[source]

Build LIMIT and OFFSET clauses.

This method constructs SQL LIMIT and OFFSET clauses based on the provided limit and skip values. It handles the special case where SQLite requires a LIMIT clause when using OFFSET.

Parameters:

  • limit_value – The limit value (default: None)

  • skip_value – The skip value (default: 0)

Returns:

LIMIT and OFFSET clauses as a string

class neosqlite.collection.sql_translator_unified.SQLTranslator(table_name: str | None = None, data_column: str = 'data', id_column: str = 'id', jsonb_supported: bool = False, json_each_function: str | None = None)[source]

Bases: object

Unified SQL translator that can be used for both direct SQL generation and temporary table generation.

This class provides a high-level interface for translating MongoDB-style query operations into SQL clauses. It integrates SQLFieldAccessor, SQLOperatorTranslator, and SQLClauseBuilder to provide comprehensive SQL translation capabilities.

__init__(table_name: str | None = None, data_column: str = 'data', id_column: str = 'id', jsonb_supported: bool = False, json_each_function: str | None = None)[source]

Initialize the SQLTranslator with table and column names.

Parameters:

  • table_name – The name of the table to query (default: “collection”)

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

  • id_column – The name of the column containing document IDs (default: “id”)

  • jsonb_supported – Whether JSONB functions are supported (default: False)

  • json_each_function – The json_each function to use (‘jsonb_each’ or ‘json_each’). If None, defaults to ‘json_each’.

translate_match(match_spec: dict[str, Any], context: str = 'direct') → tuple[str | None, list[Any]][source]

Translate a $match stage to SQL WHERE clause.

This method translates a MongoDB $match specification into an SQL WHERE clause. It handles special cases like text search queries by returning None to indicate that fallback to Python implementation is required. For regular queries, it delegates to the SQLClauseBuilder’s build_where_clause method.

Parameters:

  • match_spec – The $match specification

  • context – The context for field access (default: “direct”)

Returns:

Tuple of (WHERE clause, parameters) or (None, []) for text search

_contains_text_operator(query: dict[str, Any]) → bool[source]

Check if a query contains any $text operators, including nested in logical operators.

This method delegates to the centralized _contains_text_operator function to ensure consistent text search detection across all NeoSQLite components.

Parameters:

query – The query to check

Returns:

True if the query contains $text operators, False otherwise

translate_sort(sort_spec: dict[str, Any], context: str = 'direct') → str[source]

Translate a $sort stage to SQL ORDER BY clause.

This method translates a MongoDB $sort specification into an SQL ORDER BY clause. It delegates to the SQLClauseBuilder’s build_order_by_clause method to perform the actual translation, handling multiple sort fields with their respective sort directions (ascending or descending).

Parameters:

  • sort_spec – The $sort specification

  • context – The context for field access (default: “direct”)

Returns:

ORDER BY clause

translate_skip_limit(limit_value: int | None = None, skip_value: int = 0) → str[source]

Translate $skip and $limit stages to SQL LIMIT/OFFSET clauses.

This method translates MongoDB $skip and $limit specifications into SQL LIMIT and OFFSET clauses. It delegates to the SQLClauseBuilder’s build_limit_offset_clause method to perform the actual translation, handling both limit and skip values appropriately for SQLite syntax.

Parameters:

  • limit_value – The limit value (default: None)

  • skip_value – The skip value (default: 0)

Returns:

LIMIT and OFFSET clauses

translate_field_access(field: str, context: str = 'direct') → str[source]

Translate field access for a given field and context.

This method generates the appropriate SQL expression for accessing a field based on the field name and context. It delegates to the SQLFieldAccessor’s get_field_access method to perform the actual translation, handling special cases like the “_id” field which maps to the ID column, and regular fields which use json_extract expressions.

Parameters:

  • field – The field name

  • context – The context for field access (default: “direct”)

Returns:

SQL expression for accessing the field

translate_sort_skip_limit(sort_spec: dict[str, Any] | None, skip_value: int = 0, limit_value: int | None = None, context: str = 'direct') → tuple[str, str, str][source]

Translate sort/skip/limit stages to SQL clauses.

This method translates MongoDB sort, skip, and limit specifications into their corresponding SQL clauses. It combines the functionality of translate_sort and translate_skip_limit to generate ORDER BY, LIMIT, and OFFSET clauses in a single call.

Parameters:

  • sort_spec – The $sort specification (default: None)

  • skip_value – The skip value (default: 0)

  • limit_value – The limit value (default: None)

  • context – The context for field access (default: “direct”)

Returns:

Tuple of (ORDER BY clause, LIMIT clause, OFFSET clause)