filter/README.md
Rama main · réplica de lectura del repositorio original.
# Memo Filter Engine
This package houses the memo-only filter engine that turns standard CEL syntax
into SQL fragments for the subset of expressions supported by the memo schema.
The engine follows a three phase pipeline inspired by systems
such as Calcite or Prisma:
1. **Parsing** – CEL expressions are parsed with `cel-go` and validated against
the memo-specific environment declared in `schema.go`. Only fields that
exist in the schema can surface in the filter, and non-standard legacy
coercions are rejected.
2. **Normalization** – the raw CEL AST is converted into an intermediate
representation (IR) defined in `ir.go`. The IR is a dialect-agnostic tree of
conditions (logical operators, comparisons, list membership, etc.). This
step enforces schema rules (e.g. operator compatibility, type checks).
3. **Rendering** – the renderer in `render.go` walks the IR and produces a SQL
fragment plus placeholder arguments tailored to a target dialect
(`sqlite`, `mysql`, or `postgres`). Dialect differences such as JSON access,
boolean semantics, placeholders, and `LIKE` vs `ILIKE` are encapsulated in
renderer helpers.
The entry point is `filter.DefaultEngine()` from `engine.go`. It lazily constructs
an `Engine` configured with the memo schema and exposes:
```go
engine, _ := filter.DefaultEngine()
stmt, _ := engine.CompileToStatement(ctx, `has_task_list && visibility == "PUBLIC"`, filter.RenderOptions{
Dialect: filter.DialectPostgres,
})
// stmt.SQL -> "((memo.payload->'property'->>'hasTaskList')::boolean IS TRUE AND memo.visibility = $1)"
// stmt.Args -> ["PUBLIC"]
```
## Core Files
| File | Responsibility |
| ------------- | ------------------------------------------------------------------------------- |
| `schema.go` | Declares memo fields, their types, backing columns, CEL environment options |
| `ir.go` | IR node definitions used across the pipeline |
| `parser.go` | Converts CEL `Expr` into IR while applying schema validation |
| `render.go` | Translates IR into SQL, handling dialect-specific behavior |
| `engine.go` | Glue between the phases; exposes `Compile`, `CompileToStatement`, and `DefaultEngine` |
| `helpers.go` | Convenience helpers for store integration (appending conditions) |
## SQL Generation Notes
- **Placeholders** — `?` is used for SQLite/MySQL, `$n` for Postgres. The renderer
tracks offsets to compose queries with pre-existing arguments.
- **JSON Fields** — Memo metadata lives in `memo.payload`. The renderer handles
`JSON_EXTRACT`/`json_extract`/`->`/`->>` variations and boolean coercion.
- **Time Fields** — `created_ts`, `updated_ts`, and attachment `create_time` are
CEL `timestamp` values. Express instants with the `now` variable,
`duration("…")` (e.g. `created_ts >= now - duration("24h")`), or
`timestamp("2006-01-02T15:04:05Z")` / `timestamp(<epoch-seconds>)`. These fold
to epoch seconds at compile time — `now` is frozen once per compile (injectable
for tests via the engine clock) — so the backing columns stay unchanged.
- **Tag Operations** — `tag in [...]`, `"tag" in tags`, and tag comprehension
predicates evaluate individual JSON array elements. Equality and string
matching are case-sensitive, without Unicode normalization; `%` and `_` in
string operands are literal. Hierarchy works through implied ancestors already
present in the memo tag set, not through prefix matching.
- **Boolean Flags** — Fields such as `has_task_list` render as an `IS TRUE` check
(a comparison against `CAST('true' AS JSON)` on MySQL). Memo payloads omit false
flags, so a missing key counts as false: `flag == false` and `flag != true`
render as the negated check and match the same memos as `!flag`.
- **Presence Flags** — `has_location` renders as a JSON key-existence check on
`memo.payload` (`$.location`). A missing key and an explicit JSON null both
count as absent on every dialect; any other value — including an empty object —
counts as present. Only `==`/`!=` against a boolean literal (or bare/negated
use) is allowed.
- **Related-Row Flags** — `has_attachments` renders as `EXISTS` over
`attachment` rows whose `memo_id` is the memo. `has_image` ORs the payload
flag `property.hasInlineImage` (Markdown image syntax in the content) with an
`EXISTS` over attachments whose `type` starts with `image/`, ignoring case.
These facts are read at query time because attachment binding changes without
the content changing; never copy them into the payload. Comparisons follow the
presence flags.
- **String Matching** — `content.contains(x)`, `content.startsWith(x)`, and
`content.endsWith(x)` render as case-insensitive `LIKE`/`ILIKE` with LIKE
metacharacters (`%`, `_`, `\`) escaped. Available on scalar string fields whose
schema sets `SupportsContains` (memo `content`; attachment `filename`,
`mime_type`).
- **Regex** — `field.matches("pattern")` renders to `~` (Postgres) or `REGEXP`
(MySQL/SQLite). SQLite uses a Go-backed `regexp` function registered in
`store/db/sqlite/functions.go`. Patterns are validated at compile time against
Go's RE2 via `cel.ValidateRegexLiterals()`. **Caveat:** regex *syntax* differs
per engine (Go RE2 on SQLite, POSIX ERE on Postgres, ICU on MySQL 8.0+), so
engine-specific patterns may not be portable.
- **Tag `all()` / `exists_one()`** — `tags.all(t, <pred>)` matches only non-empty
tag sets where every element satisfies the predicate; `tags.exists_one(t,
<pred>)` matches when exactly one element does (`COUNT(...) = 1`). Both iterate
per-element (`json_each` / `jsonb_array_elements_text` / `JSON_TABLE`).
- **Timestamp Accessors** — `created_ts.getFullYear()`, `getMonth()`, `getDate()`,
`getDayOfMonth()`, `getDayOfWeek()`, `getDayOfYear()`, `getHours()`,
`getMinutes()`, `getSeconds()` render to date-part extraction (`strftime` /
`EXTRACT` / `YEAR`/`MONTH`/…). Results are normalized to CEL's base (0-based
month, 0-based day-of-week with 0 = Sunday). The same accessors on `now` fold
to literal date parts of the frozen evaluation time (UTC), so saved filters
like `created_ts.getMonth() == now.getMonth() && created_ts.getDate() ==
now.getDate()` ("on this day") re-resolve on every compile. Extraction is UTC
on SQLite/Postgres (epoch columns); on MySQL the `TIMESTAMP` column is read in
the session time zone. A timezone argument is not supported.
- **Set Operations** — `ext.Sets()`: `sets.contains(tags, [...])`,
`sets.intersects(tags, [...])`, and `sets.equivalent(tags, [...])` desugar to
exact-membership checks (AND / OR of `"v" in tags`); `equivalent` adds a
`size(tags)` length check (relies on tags being a set).
- **`size()`** — `size(tags)` renders to JSON array length; `size(content)` (and
other string fields) render to `LENGTH` / `CHAR_LENGTH` (MySQL) for code-point
counts.
- **Arithmetic** — `+`, `-`, `*`, `/`, `%` constant-fold on literal/`now`/`duration`
operands (division and modulo guard against a zero divisor).
## Typical Integration
1. Fetch the engine with `filter.DefaultEngine()`.
2. Call `CompileToStatement` using the appropriate dialect enum.
3. Append the emitted SQL fragment/args to the existing `WHERE` clause.
4. Execute the resulting query through the store driver.
The `helpers.AppendConditions` helper encapsulates steps 2–3 when a driver needs
to process an array of filters.