Qwiratry — SQL-Features-Plan
A. Qwiratry language redesign for SQL-shaped features (v1)
Plan/Spec for version 0.10.2 (Language Redesign): how the Qwiratry language (Query AST, query slang, and operator surface) grows to cover the gaps listed in SQL-bits-missing.md — CrUD, Grouping, and Other — without turning Qwiratry into SQL.
Companion research: SQL-AST-Qwiratry-Mapping.md, SQL-AST-Comparison.md.Backend / Location / TaOP work stays out of scope here (see Qwiratry-TaOP-Basics.md and Qwiratry-TaOP-Plan.md for row sinks and Relation faces).
Status: Section A implemented (language + File/HTTP; API per §A.4.9).Scope: Language only — operators, slang forms, AST nodes, pipeline semantics, and how those map conceptually to SQL.Non-scope: Postgres/Memory Location APIs, SQL string encoding, Walker pushdown strategy internals (except where the language must expose a hook).
A.1. Goals
Make Create / Update / Delete first-class in the query language, tying mutation into Destination rather than inventing a second write vocabulary. 2 . . Keep Qwiratry’s design principles: declarative Query AST, Unicode/relational operators, chained pipelines, introspectable nodes, domain flexibility (tables and other models where the op makes sense).
A.2. Non-goals
Emitting or parsing SQL text in core.
A full SQL dialect grammar or keyword-for-keyword SQL slang.
Replacing TaOP Relation mutation APIs; pipelines and Relation remain complementary.
A separate
GroupByOperator/ γ — collapse is ⌿ →aggreduce(see Qwiratry-AggregateFunctions-Plan.md WQ7). Window / row-preserving aggregates use ⇶ →aggmap(also ordinary map). Unique/distinct remain separate.Row locking (
FOR UPDATE, …) in the first cut (deferred — see Post-SQL-TaOP-Improvements.md §D.5).Full Location backend APIs (row sinks, HTTP verbs, schema DDL). This plan states language meaning per location kind; backends implement later (Table / Schema Destination backends: Post-SQL-TaOP-Improvements.md §D.3).
A.3. Design principles
Pipeline position is semantics.
σbefore grouping ≈WHERE;σafter grouping ≈HAVING. Prefer that over a dedicatedHAVINGnode unless pushdown later needs an explicit tag.One Destination family, several operators. Bare Destination is overwrite. Composites (
⮷//,⮷+,⮷-,⮷⊕,⮷⊖) each parse as a single infix and each has its own AST class (OverwriteDestinationOperator, …; see §A.4.2). Ensure Present is Upsert and Append as one operator for now.
A.4. Destination Operators (Create/Update/Delete, etc)
A.4.1. Table of Proposed Destination Operators
Expanded form of the Current Proposal survey row. These are operators, not Raku metaoperators. Bare Destination is the overwrite operator. The other rows are composite Destination operators (Destination combined with another glyph). Related Raku assignment operators are an analogy only.
| Sym | Code | Unicode Name | SQL DML Operation | Qwiratry Name | Related Raku Assignment Operator | Notes |
|---|---|---|---|---|---|---|
⮷ | U+2BB7 | RIBBON ARROW RIGHT DOWN | TRUNCATE + INSERT (replace all rows / contents) | Overwrite Destination | = | Replace existing location contents; identity must already exist (DQ1 — breaking vs today’s create-if-absent ⮷) |
⮷// | U+2BB7 + U+002F U+002F | RIBBON ARROW RIGHT DOWN + SOLIDUS + SOLIDUS | INSERT | Insert Destination | //= | insert-if-absent |
⮷+ | U+2BB7 + U+002B | RIBBON ARROW RIGHT DOWN + PLUS SIGN | UPDATE | Update Destination | += | |
⮷- | U+2BB7 + U+002D | RIBBON ARROW RIGHT DOWN + HYPHEN-MINUS | DELETE | Delete from Destination | -= | |
⮷⊕ | U+2BB7 + U+2295 | RIBBON ARROW RIGHT DOWN + CIRCLED PLUS | UPSERT / MERGE | Ensure Present (Upsert/Append) Destination | ||
⮷⊖ | U+2BB7 + U+2296 | RIBBON ARROW RIGHT DOWN + CIRCLED MINUS | DELETE / DROP IF EXISTS | Ensure Absent Destination | ⊖= | Ensure the identity is gone |
A.4.2. Parse and AST decisions
Composite Destination glyphs parse as a single infix each (
⮷//,⮷+,⮷-,⮷⊕,⮷⊖), not as Destination followed by a second operator.Each form has a separate AST class. Today’s
DestinationOperatorbecomesOverwriteDestinationOperator. The composites areInsertDestinationOperator,UpdateDestinationOperator,DeleteFromDestinationOperator,EnsurePresentDestinationOperator, andEnsureAbsentDestinationOperator.Ensure Present is Upsert and Append as one operator for now (
EnsurePresentDestinationOperator).Bare
⮷remains today’s Destination infix, nowOverwriteDestinationOperator.
A.4.3. Destination Operator Mapping
What each operator means at a location. Backends may refuse a cell they cannot honour; the language still names the intent.
Identity (which thing is written) is the location for File and HTTP, and
the incoming row key / match for Table (often a prior σ, or key columns on the
payload). Schema is the named relation/column, not a row.
| Operator | File | HTTP | Table | Schema |
|---|---|---|---|---|
| Overwrite Destination | Overwrite existing path contents; do not create if absent | Replace existing resource body (PUT); do not create if absent | Empty the relation, then insert incoming rows | Replace the named object (DROP+CREATE / CREATE OR REPLACE) |
| Insert Destination | Create the path only if it is absent | Create the resource only if it is absent (PUT + If-None-Match: *) | Insert rows only when their identity is absent | CREATE the named object only if it is absent |
| Update Destination | Rewrite an existing path’s contents; do not create | Patch/replace an existing resource | Change existing rows that match identity | ALTER an existing named object |
| Delete from Destination | Remove the path | HTTP DELETE the resource | Delete matching rows (or the relation if the payload names it) | DROP the named object |
| Ensure Absent Destination | Remove the path if it exists; succeed if it is already gone | HTTP DELETE; treat missing (404/410) as success | Delete matching rows; succeed if none match | DROP IF EXISTS |
| Ensure Present (Upsert/Append) Destination | If the path exists, append; if not, create (file analogue of upsert) | PUT create-or-replace (or POST that upserts) | Insert new identities; update existing ones (MERGE / ON CONFLICT) | CREATE OR REPLACE / add-or-replace columns |
A.4.4. Destination error behaviour
Cells are language-level outcomes. “Exists” means the identity from §A.4.3 already has a value (file path, HTTP resource, row key, schema object).
| Operator | Does not exist | Already exists | Other action fails (eg. permission denied, etc) |
|---|---|---|---|
| Overwrite Destination | Throw | No error | Throw Error |
| Insert Destination | No Error | Throw | Throw Error |
| Update Destination | Throw | No Error | Throw Error |
| Delete from Destination | Throw | No Error | Throw Error |
| Ensure Absent Destination | No Error | No Error | Throw Error |
| Ensure Present (Upsert/Append) Destination | No Error | No Error | Throw Error |
A.4.5. Implementation decisions (this cut)
Table and Schema are specified, not implemented. §A.4.3 keeps those columns as language meaning. This cut does not implement table row sinks or schema DDL. Follow-up: Post-SQL-TaOP-Improvements.md §D.3.
Only already existing Locations are implemented now. That is File and HTTP. New Location kinds (eg. Table) stay in the mapping tables but are not part of this cut.
Pipeline evaluation is payload pass-through, as today’s Destination does: the write happens, then the upstream payload continues down the pipeline.
RETURNING-style results are deferred — Post-SQL-TaOP-Improvements.md §D.4.
A.4.6. Investigation: composite infix parse
Confirm that each composite Destination form is a legal single Raku infix
(infix:<…//>, infix:<…+>, infix:<…->, infix:<…⊕>, infix:<…⊖> with the
Destination glyph). This does not need a product decision unless that parse
shape fails; then we revisit slang encoding. Resolved — see DQ8.
A.4.7. Exceptions
Use the existing I/O family, with typed exists / missing under LocationError:
X::Qwiratry::IO::LocationError($.location,$.reason, optional$.resolved) — base for location failures. Today File already uses it for “File not found” and write failures; keep it for generic / other action failures (permission, I/O).X::Qwiratry::IO::LocationError::NotFound(subclass ofLocationError) — identity does not exist when the operator requires it (Overwrite, Update, Delete from). Use for §A.4.4 “Does not exist → Throw”.X::Qwiratry::IO::LocationError::AlreadyExists(subclass ofLocationError) — identity already exists when the operator forbids that (Insert Destination). Use for §A.4.4 “Already exists → Throw”.X::Qwiratry::IO::ElementTypewhen the payload is the wrong stream shape (already used by Destination).
Callers can when on the subclasses without string-matching $.reason. Generic
LocationError remains for failures that are neither missing nor conflict.
A.4.8. Backend API
Destination backends expose one method per Destination operator intent. The operator AST class chooses the method; backends are not passed an opaque mode flag from slang. This keeps each operator’s semantics visible in backend code and matches the “separate AST class per composite glyph” decision (§A.4.2).
Historical note. §A.4.8 below first proposed one named method per intent
(overwrite-walkable, …). §A.4.9 supersedes that with a single
write-walkable(DestinationMode $mode, $location, $content, :$ensure) (see DQ2 / DQ6 as revised).
The mode/HTTP/File behaviour tables in this section remain the semantic reference;
only the call shape changed. Payload and delete still take $content for
pass-through: File/HTTP ignore it for delete side effects (DQ3); Table backends
(later) use $content to select rows.
| Operator | write-walkable call | File | SQL | HTTP |
|---|---|---|---|---|
Overwrite (⮷) | DestinationMode::overwrite | spurt if present; NotFound if absent | TRUNCATE + INSERT; blob replace | PUT; fail if absent |
Insert (⮷//) | DestinationMode::insert | spurt if absent; AlreadyExists if present | INSERT; fail on conflict | PUT + If-None-Match: *; 412 → AlreadyExists |
Update (⮷+) | DestinationMode::update | spurt if present; NotFound if absent | UPDATE; fail if no row | PUT or PATCH |
Delete (⮷-) | DestinationMode::delete | unlink; NotFound if absent | DELETE; fail if no row | DELETE; fail on 404 |
Ensure Present (⮷⊕) | DestinationMode::update, :ensure | spurt if absent; append if present | MERGE / ON CONFLICT | PUT; create-or-replace |
Ensure Absent (⮷⊖) | DestinationMode::delete, :ensure | unlink if present; ok if absent | DELETE (ok if none); DROP IF EXISTS | DELETE; 404/410 = success |
Table notes (File / SQL / HTTP detail):
Overwrite — File: existing path only; truncate + write (DQ1 — breaking vs today’s create-if-absent
⮷). HTTP:PUTwhen resource exists.Insert — HTTP:
PUTwithIf-None-Match: *(DQ5); 412 Precondition Failed →AlreadyExists. Fallback:HEADthenPOSTif the server does not honour the header.Update — HTTP:
PUTfor full replace;PATCHwhen partial update is intended.Delete — HTTP: non-success including 404 is an error (unlike Ensure Absent).
Ensure Present — File: append if present, create if absent (DQ4). SQL: upsert / merge semantics. HTTP: idempotent create-or-replace by URI.
Ensure Absent — SQL: schema objects use
DROP IF EXISTS. HTTP: 404 and 410 treated as already absent (DQ5).
File backend (this cut). Map write-walkable modes to filesystem operations:
overwrite— throwNotFoundunlesspath.e; elsespurt(truncate + write).insert—path.echeck; throwAlreadyExistsif present; elsespurt.update— throwNotFoundunlesspath.e;spurt(no create).delete— throwNotFoundunlesspath.e;path.IO.unlink.update+:ensure— if absent,spurt; if present, append (spurt :append).delete+:ensure—unlinkif present; no error if absent.
HTTP backend (this cut). The client gains request helpers per method. Existence
checks use HEAD (fallback GET when HEAD is unsupported — DQ5). Treat 404
and 410 as “absent” for Ensure Absent and for Overwrite/Update/Insert
preconditions. Insert prefers PUT with If-None-Match: * (412 →
AlreadyExists); fall back to HEAD then POST when the server ignores the
header (DQ5).
Operator dispatch. LocationOperatorNode.evaluate (or each Destination AST
class) always evaluates the chained subject first (DQ3), then calls the matching
backend method. Typed errors from §A.4.7 are raised by the backend or a thin
shared helper that interprets existence checks.
Streaming sinks. Today’s File backend can write a text-chunk Iterator incrementally
via open-sink-push instead of joining chunks into one Str first. This cut keeps
that path for Overwrite and Ensure Present (create-if-absent and append).
Overwrite still requires the path to exist first (DQ1) before opening the sink.
Insert and Update stay on whole-value walkable/spurt: if the precondition
fails (AlreadyExists / NotFound) or the write fails, throw and let the caller
handle it (DQ7). HTTP stays whole-value this cut.
Capability. Backends that implement only some $mode / :ensure
combinations reject the others from write-walkable with LocationError
(unsupported operation), unless a future capability advert tells the operator to
fail earlier at TWEAK.
A.4.9 Backend API Revisions
| Operator | old method | current method | new method |
|---|---|---|---|
Overwrite (⮷) | write-walkable | overwrite-walkable | write-walkable(DestinationMode::overwrite, $location, $content) |
Insert (⮷//) | insert-walkable | write-walkable(DestinationMode::insert, …) | |
Update (⮷+) | update-walkable | write-walkable(DestinationMode::update, …) | |
Delete (⮷-) | delete-walkable | write-walkable(DestinationMode::delete, …) | |
Ensure Present (⮷⊕) | ensure-present-walkable | write-walkable(DestinationMode::update, …, :ensure) | |
Ensure Absent (⮷⊖) | ensure-absent-walkable | write-walkable(DestinationMode::delete, …, :ensure) |
A.5. Open Questions
None remaining for this Destination-operator cut.
A.6. Decided Questions
DQ1: Overwrite when identity is missing (§A.4.4). Require the identity to exist first — throw
NotFoundif absent. Breaking change: today’s bare⮷creates a missing file;OverwriteDestinationOperatorwill not.DQ2: Backend API shape (§A.4.9, revises §A.4.8). Single
write-walkable(DestinationMode $mode, $location, $content, :$ensure)onDestinationWalkable.$modeisQwiratry::Location::Base::DestinationMode(overwrite|insert|update|delete). Ensure Present isupdate+:ensure; Ensure Absent isdelete+:ensure. Not role-per-mode.DQ3: Delete operators and pipeline subject. Always evaluate the left-hand pipeline; pass the evaluated upstream value through (§A.4.5). For File/HTTP, identity is
$location— the delete backend method receives$contentbut ignores it for the side effect. For Table (later),$contentselects which rows to delete (eg.σ … ⮷- $table).DQ4: Ensure Present for blob destinations. One
EnsurePresentDestinationOperator; backends may differ by location kind — File append-if-present / create-if-absent; HTTP PUT create-or-replace (see §A.4.3, §A.4.8 table).DQ5: HTTP existence and idempotency.
HEADestablishes “exists” (fallbackGETwhenHEADis unsupported). 404 and 410 count as absent (Ensure Absent succeeds; Overwrite/Update fail withNotFound; Insert fails withAlreadyExistswhen the identity is present). Insert usesPUTwithIf-None-Match: *so the server rejects the write if the resource already exists (412 →AlreadyExists). If the server does not honour that header, fall back toHEADthenPOST.DQ6:
DestinationOperatorrename; walkable call shape (§A.4.9). Breaking rename toOverwriteDestinationOperatorin one release; no deprecatedDestinationOperatoralias. Destination backends expose onewrite-walkable(DestinationMode $mode, …, :$ensure)(not six*-walkablemethods).DQ7: Streaming writes for large payloads. File
open-sink-pushapplies to Overwrite and Ensure Present (create-if-absent and append). Insert and Update stay on whole-value methods; throwAlreadyExists/NotFound(or other write errors) and let the caller handle them. HTTP stays whole-value this cut.DQ8: Composite Destination infix parse (§A.4.6). Each composite form (
⮷//,⮷+,⮷-,⮷⊕,⮷⊖) is a legal single Raku infix operator; no slang encoding change needed.
B. Qwiratry language redesign for SQL-shaped features (v2)
B.1. Goals
Add real grouping (group-by + aggregates via ⌿ →
aggreduce), map / window (⇶ →aggmap: ordinary map, windowing when requested), and unique/distinct features/operators — not approximations via a separate legacy map glyph for the window path.Cover transactions and paging (
LIMIT/OFFSET) as language constructs that compose with pipelines.Keep Qwiratry’s design principles: declarative Query AST, Unicode/relational operators, chained pipelines, introspectable nodes, domain flexibility (tables and other models where the op makes sense).
B.2. Aggregation/Grouping
Started as a separate AggregateFunctions module; absorb into Qwiratry after TaOP basics so drivers can use Qwiratry Walkers instead of a private walk.
Spec: Qwiratry-AggregateFunctions-Plan.md.Prior survey: old-design-docs/SQL-Grouping-Spitballing.md.Standalone design (source of lifecycle semantics): raku-AggregateFunctions/Specification.md.
B.3. Paging
Moved to Post-SQL-TaOP-Improvements.md §D.1.
B.4. Unique/Distinct
Done. DeduplicationOperator (⑂, U+2442 OCR FORK) — sort-shaped
1-arg / 2-arg / postfix; see Aggregate Functions Plan §A.6 / §H.3.
C. Qwiratry language redesign for SQL-shaped features (v3)
Moved to Post-SQL-TaOP-Improvements.md §D.2 (Transactions / Atoms, including former §C.1.1–§C.1.3).