MAD Designer iconMAD

MAD SQL Entry — pasting a SQL query into the query designer

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.


1. The goal

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.

2. What exists today (read first-hand)

2.1 The canonical query model — the Rptq graph

The 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.

What the model does not hold — the boundaries the importer must respect:

2.2 SQL generation — the round-trip contract

MADQueryFull builds the executed SQL from the model (SQLOnlyActiveFields:managedObjectContext:error:, MADQueryFull.m:143-273):

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.

2.3 The refresh pipeline — the pattern validation reuses

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.

2.4 The backend the UI already calls — the seam the compiler reuses

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:

2.5 The UI seam

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.

3. The feature, as the user sees it

  1. In the query tab, tap the existing SQL button. The popover now has View / Edit modes.
  2. View shows both representations of the query’s SQL, as a segmented control:
    • Generated — the SQL MADQueryFull builds from the model right now (today’s popover content, kept as the default tab).
    • As entered — the user’s last applied statement, verbatim (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.
  3. Edit — a monospaced multiline text view, seeded from 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.
  4. Validate — structural check plus, when the document’s database can be reached, a zero-row server probe (§7). Errors come back with line and column.
  5. Apply — the statement is compiled into the document (atomic — §6.5) and the exact pasted text is persisted as 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.
  6. A failed or conflicted apply persists nothing — not the text, not any rows. The previous query and its previous entered text stay exactly as they were.
  7. The user can flip back at any time: the visual designer keeps working on the imported query, View shows both forms, and Edit re-opens their own words.

4. Architecture

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.

5. The supported subset

The importer accepts exactly the language the model can hold — one SELECT, no subqueries anywhere, and each clause within the limits of §2.1:

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.

6. Resolution and compilation

6.1 Resolver

6.2 The write plan

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.

6.3 Conflict check — the apply is atomic

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.

6.4 Write

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).

6.5 After the write

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.

7. Validation — two levels

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.”

8. The persisted source text — RptqQuery.sqlSourceText

The 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:

8.1 The store change

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).

8.2 Store versioning — new, small, and now required

The portable store has no versioning today (MADStore.m carries no user_version pragma). This phase adds the mechanism, deliberately minimal:

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.

8.3 Considered and rejected: the guts metadata plist

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.

8.4 The future this unblocks

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.

9. Phases

Phase A — the persisted source text: store versioning (MAD only; Linux green).

  1. sqlSourceText on RptqQuery: compiled model + MADStoreSchema descriptors + autogen property, in one change (the cross-check test fails otherwise).
  2. The versioning mechanism of §8.2 — version constant, migration list, user_version read/apply/write on open, fresh stores at the current version.
  3. Tests: 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).

  1. Golden SQL tests pinning MADQueryFull’s exact output per dialect (§10.1) — written first, so the parser is built against the real generator.
  2. MADSQLTokenizer/MADSQLParser/MADSQLStatement + the identifier-quote API (§4).
  3. 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).

  1. MADSQLStatementResolver, MADSQLQueryCompiler with atomic apply (§6) and the sqlSourceText write on success (§8).
  2. Deletion-helper inventory; add the missing link/predicate removal helpers.
  3. 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).

  1. The View/Edit popover of §3, MADOperationValidateSQL probe, summary view.
  2. UI tests (§10.6) using a sample document (sqlite3 driver — no server needed).

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.

10. Tests

10.1 Pinning existing functionality (written first)

10.2 Store persistence and migration tests

10.3 Parser tests — MADSQLParserTests (MAD)

10.4 Compiler tests — MADSQLQueryCompilerTests (MAD)

In-memory store with a schema fixture (the pattern MADModelQueryTableTests uses):

10.5 Validation tests

10.6 UI tests — MADDesignerUITests

With 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.

10.7 Design-for-test constraints (binding on implementers)

11. Decisions — resolved with the user (2026-10-22)

  1. Strict subset. Confirmed: anything the model cannot hold is rejected with a reason (§5). No opaque-SQL fallback.
  2. Apply replaces the query (atomic, §6.3). Confirmed.
  3. GROUP BY import creates the report’s group sections automatically — the existing MADocument helper does this, and it stays exactly as it is. Confirmed.
  4. The SELECT list is validated, not stored, until Phase E — until element synthesis exists, an imported query returns join/group columns on refresh until the user adds elements, exactly like any new document. Confirmed as phased.
  5. The pasted text is persisted. 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).