AKML SQL — Formatter Reference Updated

#Overview

The SQL formatter rewrites T-SQL text according to a formatting profile (.akmlstyle), preserving semantic equivalence. It runs as part of the out-of-process Engine and is invoked via the IPC API.

#Formatting Pipeline

The pipeline processes SQL in sequential stages. Failures in the semantic validation stage cause the original SQL to be returned unchanged.

Input SQL
  Stage 0a: NoformatScanner      → identifies -- noformat / -- endnoformat regions
  Stage 0b: SqlcmdPreprocessor   → replaces :r / :setvar directives with placeholders
  Stage 1:  TSql170Parser        → parses into TSqlScript AST
  Stage 2:  AstAnnotator         → attaches comments to AST nodes
  Stage 3:  LayoutEngine         → produces LayoutNode list (tokens + whitespace rules)
            — or, for a style written in SQL Prompt's model, SqlPromptLayout (see below)
  Stage 4:  CasingEngine         → applies keyword/identifier casing from profile
  Stage 5:  TextEmitter          → emits formatted string
  Stage 5b: SqlcmdPreprocessor   → restores SQLCMD placeholders
  Stage 6:  SemanticValidator    → re-parses both versions; normalizes and compares
  Stage 7:  IdempotencyCheck     → formats again; verifies output is identical
Output FormattedSQL

#Stage details

Stage 6 failure: returns original SQL unchanged. Stage 7 failure: returns the (possibly non-idempotent) formatted SQL with a diagnostic warning appended.

#SQL Prompt Styles (spec 039)

A style can be written in SQL Prompt's own model: the .akmlstyle then carries a "sqlPrompt" object — exactly the JSON document SQL Prompt 10.5+ reads and writes (one file per style: metadata, whitespace, lists, parentheses, casing, dml, ddl, controlFlow, cte, variables, joinStatements, insertStatements, functionCalls, caseExpressions, operators). Such a style is its document: it formats from it, the style editors edit it, and import / export move it unchanged.

{
  "metadata": { "name": "Team", "id": "…" },          // AKML metadata
  "sqlPrompt": {                                        // the style (SQL Prompt's JSON)
    "metadata": { "id": "…", "name": "Team" },
    "lists": { "placeCommasBeforeItems": true },
    "casing": { "reservedKeywords": "uppercase" }
  },
  "list": { "commaPosition": "leading" }, …             // projection for older builds
}

Layout. FormatterPipeline.Layout sends a style with a document to the SQL Prompt layout (src/AkmlSql.Formatting/SqlPrompt/) and every other style to the rule-based layout below (unchanged). Parsing, casing, formatting-off regions, semantic validation and the idempotency check are shared. The SQL Prompt layout emits every token once, in order, deciding only the whitespace before each (Layout/SqlWriter), so no option can change what the SQL means; one printer per construct (Layout/SqlPromptPrinter.*) reads its options straight from the style.

Options. SqlPromptOptionCatalog holds SQL Prompt's 114 schema options plus whitespace.newLines.alignMultilineCommentsMatchingPatterns, grouped as SQL Prompt's editor groups them (Global: Whitespace, Lists, Parentheses, Casing · Statements: Data (DML), Schema (DDL), Control flow, CTE, Variables · Clauses: Join, Insert · Expressions: Function calls, CASE, Operators), with labels, allowed values, defaults, which option turns another on, and notes. It is pinned to Redgate's vendored schema by test. How each ambiguous option is read, and where to correct it, is listed in specs/039-sqlprompt-style-editor/spec.md.

The document. SqlPromptStyleDocument keeps SQL Prompt's minimal form (an option at its default is left out), reads keys and values case-insensitively, keeps keys this build does not know, reads Redgate's historical intentedFromWhen typo, and applies SQL Prompt 11's collapse rule: a …ShorterThan threshold written without its collapseShort… switch means the collapse is on; switching it off writes false explicitly.

Styles written in AKML's model keep formatting exactly as before. The editors show their SQL Prompt reading (SqlPromptProjection, or the .source.json a spec-031 import kept); saving one from an editor makes it a SQL Prompt style.

Editors.

• Web edition: Format styles page (/styles). Styles are saved to the paired engine when it advertises styles.sqlprompt.v1 — the styles folder SSMS uses — otherwise in the browser (IndexedDB). Built-ins are edited as copies that shadow them (Reset restores).
• SSMS: the Format Styles window asks the engine for SQL Prompt's model (StyleEditorSchemaRequest.SqlPromptModel); settings are sqlPrompt.<path>, so preview and save carry the document. Against an older engine the window falls back to AKML's settings schema.
• Both preview each page's own sample (SqlPromptPreviewSamples) or the user's SQL, and reformat on every change.

Import / export. Importing a SQL Prompt .json keeps its document and id. Exporting to a path ending in .json writes the style's SQL Prompt document in SQL Prompt's minimal form (UTF-8, no BOM); .sqlpromptstylev2 export (SQL Prompt 9 XML) is unchanged.

#PostgreSQL (spec 042)

A script of a PostgreSQL connection is formatted by its own formatter (AkmlSql.PostgreSql/Formatting, run by the engine; the web edition asks the engine). The style is any style: one written in SQL Prompt's model is used as it is, one in AKML's own model is read as its SQL Prompt equivalent (SqlPromptStyles.ToDocument).

• Layout. Tokens in, tokens out: every token of the script is printed once, in order; the layout decides only the white space and the case of keywords, functions and data types. Names are never recased, quotes are never added or removed, string constants and dollar-quoted bodies (functions, DO blocks) are verbatim. Clause alignment, lists, parentheses, CTEs, joins, CASE, AND / OR, BETWEEN, IN, INSERT, function calls and the layout of CREATE TABLE / INDEX / VIEW follow the style's options; MERGE, EXPLAIN, PREPARE and the other statements keep their own line breaks (a long one-line statement wraps after its top-level commas).
• Safety. The script must parse (otherwise it is returned unchanged with the first error). The result must parse to the same statements (location-free tree comparison), hold the same tokens and comments in the same order, and format to itself. Any failure returns the original with a message.
• Options with no PostgreSQL meaning (control flow, variables, batch separators, global variables, comparison alignment ...) are listed in SqlPromptApplicability and marked "No effect for PostgreSQL" in the Format Styles page while a PostgreSQL connection is active, whose preview is a PostgreSQL sample. The formatter never reads them.
• Format SQL actions. Case only; add / remove the last terminator; expand wildcards and qualify names with PostgreSQL quoting (both need the schema). Square brackets, AS keyword and the T-SQL rewrites are not available.
• Parse (Ctrl+F5) checks the syntax with the PostgreSQL parser, without the server, and lists every error with its line and column.
• Corpus. tests/pg-format-corpus: 151 scripts x the 8 built-in styles, every pair parse-equivalent, stable, literal-preserving and equal to its golden (AKML_UPDATE_PG_GOLDENS=1 regenerates).

#noformat Regions

Wrap any block you want the formatter to skip:

-- noformat
SELECT   weird   spacing   FROM   dbo.LegacyTable
-- endnoformat

The NoformatScanner identifies these regions before parsing, so even syntactically unusual SQL is safe.

#Profile Files (.akmlstyle)

Profiles are stored as JSON files:

%AppData%\AKML SQL\profiles\{name}.akmlstyle

Built-in profiles (read-only) are embedded in the extension. User profiles can be created, edited, imported, and exported via the Options UI.

#Profile file structure

{
  "metadata": {
    "id": "d3b07384-d9a4-4b8b-9a1d-3b4f5c6d7e8f",
    "schemaVersion": 1,
    "name": "My Profile",
    "description": "Adapted from Default",
    "author": "Jane Dev",
    "version": "1.0.0",
    "created": "2024-06-01T12:00:00Z",
    "modified": "2024-07-15T09:30:00Z",
    "basedOn": null,
    "isBuiltIn": false,
    "skipValidation": false,
    "enableIdempotencyCheck": true
  },
  "whitespace": { ... },
  "casing": { ... },
  "list": { ... },
  "parenthesis": { ... },
  "dml": { ... },
  "join": { ... },
  "ddl": { ... },
  "controlFlow": { ... },
  "case": { ... },
  "cte": { ... },
  "expression": { ... },
  "actions": { ... }
}

#Profile Options Reference

#metadata

#whitespace

Controls line breaks, indentation, and spacing.

#casing

Controls keyword and identifier casing.

#list

Controls how lists of items (SELECT columns, parameters, etc.) are laid out.

#parenthesis

Controls formatting of parenthesized groups (subqueries, expressions, parameter lists).

#dml

Controls DML (SELECT, INSERT, UPDATE, DELETE, MERGE) statement layout.

#join

Controls JOIN clause formatting.

#ddl

Controls DDL (CREATE TABLE, CREATE PROCEDURE, etc.) statement layout.

#controlFlow

Controls BEGIN/END, IF/ELSE, TRY/CATCH, and WHILE formatting.

#case

Controls CASE … WHEN … THEN … ELSE … END expression layout.

#cte

Controls Common Table Expression (CTE) formatting.

#expression

Controls expression and predicate formatting.

#actions (Format Action Config)

Controls which transformations are applied during Format Document / Format Action operations.

#SQL Prompt Style Interop (Spec 020)

.sqlpromptstylev2 (Redgate SQL Prompt's distributed XML format) is supported for both import and export, allowing teams to standardise across SQL Prompt and AKML installs.

Import — SqlPromptImporter.Import(xmlContent) in src/AkmlSql.Formatting/Profiles/SqlPromptImporter.cs. Handles both <Options><Option Name= Value=> and flat-element XML shapes. Returns a SqlPromptImportResult with the imported FormattingProfile plus MappedCount / UnmappedCount / UnmappedOptions[] so the editor can surface unsupported settings.

Export — SqlPromptExporter.Export(profile) in src/AkmlSql.Formatting/Profiles/SqlPromptExporter.cs. Round-trip safe: every setting in SqlPromptImporter.OptionMap has a parallel inverse getter in SqlPromptExporter.ReverseMap. Settings AKML supports but SQL Prompt has no equivalent for are omitted from the export.

Round-trip test definition (SC-007 / Q1 clarification): for each (input.sql × style.sqlpromptstylev2) pair, AKML's formatted output is compared to SQL Prompt's golden output as follows:

1. Strip trailing whitespace per line
2. Normalise line endings to \n (LF)
3. Drop UTF-8 BOM if present
4. Then require byte-exact equality

A pair passes iff the two normalised byte streams are equal. The corpus suite passes iff ≥ 95 % of pairs pass. See tests/format-parity/README.md for the corpus layout.

Format Settings Schema — the canonical descriptor consumed by the Format Styles editor lives at src/AkmlSql.Formatting/Profiles/FormatSettingSchema.cs. Built by reflection over FormattingProfile's 12 sub-category POCOs, exposed to the shell via the RequestStyleEditorSchema IPC (see doc/ipc-api.md).

#Format Actions (IPC)

Format operations can target specific transformations using the FormatActionType enum (src/AkmlSql.Core/Ipc/Messages/FormatActionRequest.cs; the values are wire codes, never renumber them):

#Bulk Formatting

The BulkFormatter applies formatting to multiple files in parallel (up to Environment.ProcessorCount threads). Configuration:

• confirmBulkFormat: show confirmation dialog before starting
• createBackups: write .bak files alongside originals
• handleParseErrors: skip files with parse errors instead of aborting
• respectNoformat: honour -- noformat regions in all files

Bulk format can be cancelled mid-operation via the BulkFormatCancel IPC message.

#Diagnostic Codes

The formatter may emit diagnostics alongside the formatted output: