Class SqlPredicateNormalizer

Namespace

GroupeIsa.Neos.Migration.Schema

Assembly

GroupeIsa.Neos.Migration.Abstractions.dll

Normalizes a SQL boolean predicate to a canonical form so that the predicate authored by Neos and the (reformatted) predicate stored by the database engine compare equal.

public static class SqlPredicateNormalizer

Inheritance

object

SqlPredicateNormalizer

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

object.ReferenceEquals(object, object)

object.ToString()

Remarks

This is a generic SQL boolean-predicate canonicalizer. It currently serves filtered-index predicates (and the column expressions of function-based / CASE indexes) and is intended to serve CHECK constraint expressions as well, since database engines apply the same kind of reformatting to both before storing them.

When such a predicate is created, the engine rewrites it before storing it. The rewrites observed against live databases include:

• SQL Server wraps the whole predicate in parentheses, wraps integer literals (=(1)) and removes spaces around comparison operators, e.g. [Type] = 1 is stored as ([Type]=(1)). It also wraps each literal of an IN list individually, e.g. [Type] IN (1, 2) is stored as [Type] IN ((1),(2)); the IN expansion strips those per-value parentheses so the authored and stored forms converge.
• PostgreSQL wraps the whole predicate (and each comparison operand of a boolean operator) in parentheses and appends an explicit type cast to string literals, e.g. "Type" = 1 AND "BaseType" = 'OrderPoint' is stored as (("Type" = 1) AND ("BaseType" = 'OrderPoint'::text)). It also rewrites an IN list into an array-membership test, e.g. "Type" IN (1, 2) is stored as "Type" = ANY (ARRAY[1, 2]); that form is folded back into an IN list during normalization.

Without normalization, the raw string comparison done by the migrator would always differ from the authored predicate and the index (or constraint) would be dropped and recreated on every migration.

String-literal VALUES (the contents of single-quoted literals '...') are preserved EXACTLY: they are masked to placeholder tokens before any structural normalization (quote-stripping, case-folding, whitespace-compaction, operator-spacing, parenthesis collapsing) and restored verbatim afterwards. As a result, two predicates that differ only inside a literal — by case ('OrderPoint' vs 'orderpoint'), by inner whitespace, or by SQL punctuation such as parentheses ('(1)' vs '1') — normalize to DIFFERENT canonical forms, so a genuine filter change is detected. Symmetric round-trips still compare equal because the engines preserve literal content byte-for-byte (PostgreSQL only appends a cast such as ::text OUTSIDE the quotes, which is stripped first).

Known limitation: precedence-significant parentheses appearing OUTSIDE string literals are intentionally preserved (a parenthesized group is only collapsed when it contains no AND/OR). Therefore an exotic predicate whose stored form differs from the authored form only by such parentheses will trigger a single, one-time rebuild. The common predicate shapes (equality, string equality, conjunction, IS [NOT] NULL) are handled.

Methods

Normalize(string?)

Normalizes a SQL boolean predicate to a canonical form.

public static string? Normalize(string? filter)

Parameters

filter string

The predicate to normalize (authored or engine-stored).

Returns

string

The canonical form, or null when the input is null or blank.

TransformPreservingLiterals(string?, Func<string, string>)

Applies transformation to a SQL expression while leaving the contents of single-quoted string literals untouched.

public static string? TransformPreservingLiterals(string? value, Func<string, string> transformation)

Parameters

value string

The SQL expression (e.g. a function-based index column expression).

transformation Func<string, string>

The transformation applied to the non-literal parts.

Returns

string

The transformed expression, or null when value is null.

Remarks

The literal contents (e.g. 'OrderPoint') are masked to placeholder tokens before the transformation runs and restored verbatim afterwards, so a case-folding transformation does not alter the literal value (which would change the rows a function-based index covers). The single quotes that delimit a literal are part of the masked span, so they are preserved as well.