This page records the SQL-entry feature: the supported SELECT subset, the parser and resolver, and the apply path that turns a statement back into the portable query graph. Phases A-D are implemented; Phase E is deferred.
Scope: a new parser/resolver/compiler in MAD/Source/sql/, an apply path on MADocument,
the SQL popover in MADDesigner, the persisted source text (§8, which adds the store’s first
versioning step), and the tests that pin all of it.
Date: 2026-10-22, read at cf0fe3acd; revised 2026-10-22 with the persistence decision (§8).
Status: implemented — Phases A–D landed 2026-10-22 and merged into master as c48a39fb7
(the internal task queue §16). Phase E (§9) remains deferred; §8.1–§8.2 carry the as-built amendments.
Today the only way to build a query is the visual designer: pick tables, link them field by field, add filters, add groupings — all through the tabbed UI. The user wants a second way in:
“…a user could hit a button and it flips around and shows a multiline text view where they can paste the SQL… when that SQL is saved, it manually creates the query in the backend of the report… it does everything it already would do with the user selecting all the details… the software still recognizes what fields are used what joins are used what tables are used, and it completely setups the document in the proper way, and then allows the user to even use the query designer with the fields entered by the standard query (if they wish to).”
So: the pasted SQL is compiled into the document’s query model (the Rptq object graph),
through exactly the same factories the visual designer calls. After apply, the visual query
designer, the visualization canvas, and the report designer all see the query as if it had
been built by hand — and the user can keep editing it there. There must be a validation phase.
Rptq graphThe document’s query lives in the portable store as plain Rptq* objects
(MAD/Source/rptq/); the MADModel* layer is a read-only view over them
(MADModel_Layer.md). The importer writes Rptq rows and nothing else.
RptqQuery (MAD/Source/rptq/autogen/RptqQuery.h:30-34): hasQueryTables,
hasQueryLinks, hasQueryPredicate (note the singular name), hasQueryGroups.RptqQueryTable — a schema-table reference plus an alias; the alias is computed:
the schema table’s name for the first copy, name + drop index afterwards
(Customer, Customer0, … — MADModelQueryTable.m:119-129).RptqQueryField — two ids: inQueryTable + databaseSchemaField. Every field of every
query table gets a row (the add-table flow creates one per schema field,
MADQueryViewController.m:762-770).RptqQueryLinks — one join: hasLeftField/hasRightField (a RptqQueryField each),
type (MADLinkType, MADQueryLinkType.h: inner / equi / natural / cross / left / right
/ full outer). Exactly one field pair per link; the generator always emits ON l = r
(MADQueryFull.m:366-380).RptqQueryPredicate — comparson (MADPredicateOperator: =, <>, >, >=, <,
<= — MADPredicateOperator.h, RptqQueryPredicate+Extras.m:245-265), plus a
left/right side that is either a RptqQueryField or a typed value string
(leftFieldID/rigthFieldID). All predicates are AND-ed, each written (left op right)
(MADQueryPredicate.m:43-93). No OR, no LIKE, no IN, no functions.RptqQueryGroup — hasField, sort (MADSortOrder), weight, and hasSection: the
report’s group header/footer sections (RptqQueryGroup.h:24-29).What the model does not hold — the boundaries the importer must respect:
MADQueryActiveQueryFields.m:37-97, MADQuerySelectExpressionActiveFields.m).RptqQueryGroup.sort).MADQueryFull builds the executed SQL from the model
(SQLOnlyActiveFields:managedObjectContext:error:, MADQueryFull.m:143-273):
SELECT part from the driver, then the active fields, sorted by column alias, written
without AS: "Table0"."Field" (MADQuerySelectExpressionActiveFields.m:32-38, 46-53).FROM — a DFS over the join graph: tableName <connector> queryAlias, then
LEFT OUTER JOIN tableName <connector> queryAlias ON l.field = r.field per link
(MADQueryFull.m:275-426, join text from RptqQueryLinks SqlForLinkType:,
RptqQueryLinks+Extras.m:76-86).WHERE — the AND-ed predicate triples (MADQueryPredicate.m).GROUP BY/ORDER BY in the SQL — grouping and sorting are applied over the
.maddb cache (MADParameters+QueryCacheAggregate, MADQueryCacheSelectGroupKeys).LIMIT clause.The importer’s contract is therefore: a pasted statement is acceptable exactly when it
could be the output of MADQueryFull for some model. That is what makes the round-trip
honest — the View side of the SQL popover shows the SQL regenerated from the imported model,
and it reads like what the user pasted.
MADOperationRefresh.m:60-172: connect with credentials supplied by operation dependencies,
build the SQL on the main queue (the store context is main-queue-owned — BUGS.md), then
[database executeSQL:fieldKeys:onConnection:intoResultTable:withError:]
(MADDatabaseProtocol.h:33) and dataTableMakeCache. A validation probe is the same
operation shape minus the cache build.
Writing Rptq rows and saving is enough; the MADModel* layer mirrors the change into the
UI via store notifications (MADModel_Layer.md §3.3). The factories to call, verbatim from
today’s UI code:
[RptqQueryTable newQueryTableForSchemaTable:database:forManagedObjectContext:]
then one [RptqQueryField newQueryField:context] per schema field, wired to the table,
table added to query.hasQueryTables, save (MADQueryViewController.m:744-799).[RptqQueryLinks newQueryLinksWithLeftField:rightField:joinType:managedObjectContext:]
then [document.activeDocumentModel addQueryLink:link], save
(MADQueryLinkBuilderController.m:112-122).[RptqQueryPredicate newQueryPredicateFactoryWithLeftFieldID:…] /
…WithLeftValue:… variants (MADQueryAddPredicateController.m:142-162,
RptqQueryPredicate+Extras.h:20-39).[document addQueryGroupWithQueryField:andSortOrder:]
(MADocument.m:728-792). This one call creates the group row and the report’s group
header/footer sections and their section layout managers — “completely sets up the
document” for a GROUP BY, for free.removeGroupWitObjectID:fromManagedObjectContext:
(MADocument.m:1130), deleteDatafieldsRelatedToTable:inContent: and
deleteAggregateFieldRelatedToTable:inContent: (MADocument.h:91-95), and the existing
link/predicate removal paths in the query UI. Phase B inventories these; any missing
symmetrical helper for links/predicates gets added there.The query builder split view controller already has a barButtonItemSQL toolbar item that
presents a read-only MADViewControllerSQL popover (MADQueryBuilderSplitViewController.m:31,
106-111; MADQuery.storyboard; MADViewControllerSQL.m:27-56 shows
[doc.mainQuery SQLOnlyActiveFields:…]). The feature is a mode of this same screen, so the
button, the popover, and the navigation all exist.
MADQueryFull builds from the model right now (today’s
popover content, kept as the default tab).RptqQuery.sqlSourceText, §8); hidden or empty when the query has never been built
from pasted SQL.
Both tabs are read-only — the “see what you pasted, and see what it became” screen.sqlSourceText when present
(the user re-edits their text, formatting and comments intact) and from the generated
SQL otherwise; a Revert to generated button re-seeds from the generated form.sqlSourceText (§8). The popover closes; the query tables
list, the visualization canvas, and the report designer all show the imported structure.
The designer’s field pickers work as usual.pasted SQL
→ MADSQLTokenizer dialect-aware lexer; quotes per driver; tracks line:column
→ MADSQLParser recursive descent; produces a MADSQLStatement parse tree
→ MADSQLStatementResolver binds names to RptqDatabaseSchemaTable / RptqDatabaseSchemaField
→ [MADOperationValidateSQL] server probe, when connected (§7); never writes
→ MADSQLQueryCompiler the write plan: deletes + creates Rptq rows via the §2.4 factories
→ store save → model notifications → query UI / visualization / designer update
Placement. Everything except the probe operation and the editor UI lives in the MAD
framework, pure Foundation, no UIKit — so it runs in the MAD test suite and in the Linux
image. The new files go on Linux/cmake/MADSources.cmake and ./Linux/build.sh stays green.
The probe operation (needs drivers, credentials, UIKit alerts) and the popover UI live in
MADDesigner/Source.
New files. MAD/Source/sql/MADSQLTokenizer.{h,m}, MADSQLParser.{h,m},
MADSQLStatement.{h,m} (the tree and its value types: MADSQLColumnReference,
MADSQLPredicateTerm, …), MADSQLStatementResolver.{h,m}, MADSQLQueryCompiler.{h,m},
MADSQLCompileError.{h,m}. App: MADOperationValidateSQL.{h,m} (mirroring
MADOperationRefresh), plus the editor changes in MADViewControllerSQL or a sibling
editor controller in the same storyboard seam.
One small API addition. The lexer needs each dialect’s identifier quoting (MySQL
backticks, T-SQL brackets, double quotes) before a connection exists. Add
+ (NSString *)identifierQuoteCharacters to MADDatabase (default: double quote), with
per-driver overrides in MADMySQL/MADTdsClient (MADPostgres uses the default double
quote) — or, if preferred, a class method on the driver protocol resolved through
MADDatabaseDriverRegistry. Either way it is a three-method addition.
The importer accepts exactly the language the model can hold — one SELECT, no
subqueries anywhere, and each clause within the limits of §2.1:
col, tbl.col, schema.tbl.col, alias.col,
quoted or bare, plus alias.* / schema.tbl.*. The list is validated and used for
table/column discovery, not stored (the model has no SELECT list — §2.1): every column
must resolve, and a qualified column names a table that must appear in FROM. Expressions,
aggregates, aliases (AS x), and DISTINCT are rejected with a pointer to Phase E (§9),
which is the phase that makes the pasted column list real by synthesizing designer elements.
The entered text itself is stored, verbatim, in sqlSourceText (§8) — validation shape
and input text are separate things.[schema.]name [AS alias], then a chain/tree of
[INNER] JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN each with
exactly one equi-condition l.col = r.col (bare, two-part, or three-part schema.tbl.col
columns; bare allowed when unambiguous).
name0, name1, schema.name0, or dot-stripped schemaname0 matching
RptqQueryTable -queryAlias) for a second copy of the same table — the model computes
aliases itself and cannot hold renames (Customer AS c is rejected: “the designer names
tables itself”).JOIN per
table pair; two links between the same pair would double the alias).NATURAL/CROSS joins (the link model holds exactly one field pair
and the generator always writes ON …), no subqueries in FROM/ON.left op right, op ∈
{ =, <>, >, >=, <, <= } (the MADPredicateOperator set), each side a column reference
or a literal. Parens are accepted only where they don’t change the flat AND shape.
Everything else — OR, IN, BETWEEN, LIKE, NOT, functions, subqueries — is
rejected: the predicate model ANDs flat triples and nothing else.MADocument helper, which also creates the report’s group sections.MADDocument.limitResultsDesignMode, MADQueryFull.limitResults).Literals are handled per dialect at generation time (newSafeLiteral:,
MADDatabaseSQLProtocol.h); the importer only records the value string and the field’s data
type, exactly as the predicate UI does (MADQueryPredicate.m:74-88).
Rejections are never silent: every one names the construct, the clause, the reason (which model limitation it hits), and the line:column.
RptqDatabaseSchemaTable by name, case-insensitive.
Unknown → error “unknown table”.RptqDatabaseSchemaField: qualified must exist on its table; bare must be unique
across the FROM set, else “ambiguous column”.Resolver output is a value-object plan: ordered table list, join list (MADLinkType per
join), predicate list (MADPredicateOperator + left/right terms), group list with sort
orders. The plan is validated whole before anything is written.
Apply replaces the query (the pasted SQL is the query — that is the feature). Before
writing, check every table the new plan does not include for references from designer
elements — the three shapes MADQueryActiveQueryFields knows about
(MADQueryActiveQueryFields.m:37-68: label-data, aggregate, barcode references) plus any
group sections that would be deleted. If a field of a dropped table is referenced, abort
with an error naming the elements (“this field is used by … in the designer”); nothing is
deleted, nothing is written, and the old query still runs. This is the one case where the
import cannot be atomic-and-silent, so it fails loudly instead.
In reverse-dependency order using the §2.4 helpers: remove links and predicates, remove
groups (via the existing group-removal path, which detaches their sections and layout
managers), remove tables that are not in the plan (via the existing
deleteDatafieldsRelatedToTable: family), then create tables → links → predicates → groups
through the same factories the UI calls. One save; the caller then marks the document dirty
and autosaves, as the add-table flow does (MADQueryViewController.m:796-799).
No UI poking: the store notification path updates the model layer and both query screens
(MADModel_Layer.md §3.3). The compiler returns a summary (tables / links / predicates /
groups counts) for the popover.
Structural (always, offline). Parse + resolve, §5–§6. Errors are
MADSQLCompileError-domain NSErrors with a code, line, column, and message. This is the
whole feature when the document’s database is unreachable.
Server probe (when a connection can be made). Wrap the statement:
SELECT * FROM (<user sql>) AS _mad_probe WHERE 1=0
The derived-table alias is mandatory in MySQL and valid in T-SQL; WHERE 1=0 folds, so the
probe compiles the query and returns column metadata without reading rows. Run it through
the MADOperationRefresh shape (MADOperationRefresh.m:104-162): connect with credentials
from operation dependencies, store reads marshaled to the main queue, results via the
driver’s executeSQL:…intoResultTable:… so MADDataColumn gives names and types. The probe
then catches what static parsing cannot — server-side name resolution (case sensitivity,
views, reserved words), dialect differences, and the server’s own syntax errors, which are
surfaced with the server’s message. Column names/types are compared against the resolved
schema fields and mismatches reported as warnings, not failures.
Offline, or when credentials are unavailable, the probe is skipped and the UI says “validated by structure only.”
RptqQuery.sqlSourceTextThe compiled model remains the source of truth for execution, but the user’s pasted text
is part of the document: a new nullable text attribute sqlSourceText on RptqQuery
holds the last successfully applied statement, verbatim. Deleting the user’s words was never
an option — the document keeps both the input and its compilation. The user chose a single
property over a history array (2026-10-22); a revision history can be added later as an
ordered to-many and backfilled from the current text with no data loss.
Persistence rules:
The attribute lands on both sides of the schema cross-check: the compiled model and the
hand-maintained MADStoreSchema descriptors, plus the RptqQuery autogen property
(MAD/Source/rptq/autogen/). MAD_FORMAT.md gains the column in its field list.
Amended (Phase A, 2026-10-22). The model change is a new MAD 4.xcdatamodel
version, not an in-place MAD 3 edit: a shipped model version’s hash pins every
in-the-wild document’s store metadata, and editing it orphans them. MAD 4 also drops
RptqDocumentElementBarcode.barcodeBorderType’s dead elementID="type" (no pre-MAD 3
version has a barcode entity; BUGS.md).
The portable store has no versioning today (MADStore.m carries no user_version pragma).
This phase adds the mechanism, deliberately minimal:
MADStoreSchema gains a version constant and an ordered list of migration steps. Today
that list has one entry: version 1 (the schema in the wild — user_version absent or 0)
→ version 2 (ALTER TABLE adding sqlSourceText).MADStore reads PRAGMA user_version, applies the missing steps in a
transaction, and writes the new version. Fresh stores are created at the current version
(createSchemaSQL already generates the whole schema).MADLegacyStoreConversion is unaffected in flow — converted Core Data documents are
created at the current version through the same MADStore open path.MADStore code, so existing documents open
identically in the image; the migration tests run there too.Amended (Phase A, 2026-10-22). MADStoreConverter now opens each legacy store with
the momd version matching its metadata, never via automatic migration: Core Data cannot
infer a mapping for this model family (the barcode elementID clash, BUGS.md), and the
momd-current model is no longer the version in-the-wild stores were written against.
The metadata plist that rides alongside the store in the guts directory could hold the text
with no schema change at all. Rejected: query data belongs to the query part of the store
(MADStorePartQuery — the store’s contract is “three parts and nothing else”,
MADStoreSchema.h:12-13), and the headless engine reads the store, not the plist.
The stored entered-text is the natural input surface for the agent-driven query design on
the product’s horizon (MAD_FUTURE.md): an agent — or a person — writes a statement, the
compiler below turns it into the document, and sqlSourceText keeps the prompt of record.
The strict compiled-model architecture is what makes that stepwise: nothing about the
compiler changes when the writer becomes an agent.
Phase A — the persisted source text: store versioning (MAD only; Linux green).
sqlSourceText on RptqQuery: compiled model + MADStoreSchema descriptors + autogen
property, in one change (the cross-check test fails otherwise).user_version
read/apply/write on open, fresh stores at the current version.MADStoreSchemaTests stays green on both sides; new migration tests (§10.2);
MAD_FORMAT.md updated.Acceptance: a document created before this phase opens cleanly, gains the column, keeps its data, and round-trips through Linux; a fresh document is born at the current version. This phase is self-contained — it lands before, and independently of, the parser.
Phase B — golden tests, then the parser (MAD only; Linux green).
MADQueryFull’s exact output per dialect (§10.1) — written
first, so the parser is built against the real generator.MADSQLTokenizer/MADSQLParser/MADSQLStatement + the identifier-quote API (§4).MADSQLParserTests — the accept/reject matrix of §5, error codes, line:column.Acceptance: every supported construct that MADQueryFull can emit parses; every rejected
construct fails with a coded, positioned error; MAD suite and ./Linux/build.sh green.
Phase C — resolver + compiler (MAD only; Linux green).
MADSQLStatementResolver, MADSQLQueryCompiler with atomic apply (§6) and the
sqlSourceText write on success (§8).MADSQLQueryCompilerTests + the fixpoint round-trip test (§10.4).Acceptance: applying a statement produces exactly the Rptq rows the visual designer would have, and stores the text verbatim; applying over an existing query leaves no orphans; the conflict case aborts with the store — and the previous text — untouched; the fixpoint test passes.
Phase D — the app (MADDesigner).
MADOperationValidateSQL probe, summary view.Acceptance: paste → validate → apply shows the tables/joins/predicates in the query designer and visualization, group sections in the report designer, and refresh runs the query; View shows both the generated SQL and the entered text; closing and reopening the document keeps the entered text.
Phase E — deferred, its own decisions.
sqlSourceText (§8) — the natural companion to the agent-driven query design of
§8.4.MAD/MADTests (extending MADQueryFullTests.m, which today
asserts only fragments, MADQueryFullTests.m:140-162): fixed store fixtures → exact
expected SQL strings, per driver dialect (MySQL, TDS, and PostgreSQL configs), covering alias naming
and the drop-index form, every join type, every predicate operator with literal quoting,
and the active-field ordering. These pin the generator the parser must round-trip, and
they run in the Linux image too.MADStoreSchemaTests — the existing cross-check now asserts the descriptors and the
compiled model agree on sqlSourceText (it is the test that forces the two sides of
§8.1 to land together).MADStoreMigrationTests (MAD): build an on-disk store at version 1 (the schema as it
is today, user_version absent) with rows in it → open with the new MADStore → the
column exists, user_version reads the current version, and every pre-existing row and
relationship survives; a fresh store is created at the current version; opening twice is
idempotent. These run in the Linux image too, since the headless engine shares the
migration code.MADSQLParserTests (MAD)--, /* */), numbers, operators, keywords case-insensitive; positions on errors.MADSQLQueryCompilerTests (MAD)In-memory store with a schema fixture (the pattern MADModelQueryTableTests uses):
MADLinkType and left/right fields, predicates with
correct comparson and value strings, groups with sort/weight and the created group
header/footer sections and layout managers; saved once; sqlSourceText equals the input
statement exactly (comments and whitespace included).sqlSourceText replaced.sqlSourceText.MADQueryFull → parse → compile into a fresh store → generate SQL again → the two SQL
strings are equal. This is the test that defines “the software recognizes what was
pasted.”WHERE 1=0 fold — with a faked database, no server.MADMySQLLiveTests.m style (skip when no server): valid pasted SQL
probes clean with the expected column names/types; a bad column returns the server’s
error; zero rows are read.MADDesignerUITestsWith a sample .mad (sqlite3 driver): open the SQL popover, switch to Edit, paste a
two-table join with a filter and a group, Apply, then assert the query tables list shows
both tables, the visualization canvas shows two boxes and a connector, the designer shows
the group sections, and View mode shows the regenerated SQL. Then close and reopen the
document: the As entered tab still shows the exact pasted text, and the Generated
tab still shows the compiled query’s SQL — the document kept both.
NSError (MADSQLCompileError); tests assert codes.Linux/cmake/MADSources.cmake; ./Linux/build.sh stays green.MADStore code with a version constant tests can
reference — never in Apple-only paths — so the Linux suites exercise it.MADocument helper does this, and it stays exactly as it is. Confirmed.sqlSourceText on RptqQuery, a single
property holding the last successfully applied statement (§8), shown as As entered
beside Generated. The generated form remains what refresh executes; nothing about
the text being stored changes execution. A history array was considered and set aside
(§8, Phase E).