SchemaSmith v2.2.0

Added

• SchemaShears: object-level patch builder. Build a deployable patch (subset) package from a full schema product using a manifest -- a newline-delimited list of paths relative to the product root. The include set is manifest ∪ always-include ∪ scaffolding (Product.json + touched Template.json files). Emitted patches suppress drop-by-absence so omitted objects are preserved on the target; use --AllowDrops:<categories> to re-enable specific drop categories. A patch-build-report.txt in the output root lists every included file and its inclusion reason. Optional --Zip compresses the output for artifact handoff. The natural manifest producer: git diff --name-only <before> <after> -- <product-path>/. Cross-platform (SQL Server, PostgreSQL, MySQL targets via SchemaQuench).
• Product-level DropTablesRemovedFromProduct in Product.json — a package can now declare that its absent tables must not be dropped, composing (logical AND) with the environment-level setting. Foundation of the drop-protection work — #270.
• Drop-control cascade: environment → product → template. DropTablesRemovedFromProduct and DropUnknownIndexes now resolve across three tiers — environment (SchemaQuench.settings.json / env vars), product (Product.json), and template (Template.json) — with explicit-false-sticky semantics: a false at any tier locks the effective value for all lower tiers and cannot be re-enabled by a more-specific setting. A true at a lower tier overrides an inherited true but never an ancestor's false. Absent (not set) inherits from the tier above. This makes higher-tier false values hard guardrails: a production environment can suppress all auto-drops regardless of what individual packages or templates declare. Foundation slice — per-type column/FK/CHECK flags come in later slices. Cross-platform (SQL Server, PostgreSQL, MySQL). — #270
• Pre-flight diagnostics: --TestConnection and --PreviewTargets. Two new SchemaQuench CLI switches run targeted validation passes against a live server and exit before touching any schema — no deployment, no DDL, no side effects. --TestConnection validates the connection to every configured server (primary + secondaries) and enforces the product's MinimumVersion floor against each detected engine version. --PreviewTargets does everything --TestConnection does, then produces a read-only per-template report of every database and schema the deployment would target — including (would be created) for TemplateTargets.CreateIfMissing: true entries that don't yet exist. Both switches respect Target filters and TemplateTargets overrides. RequireAtLeastOneTarget enforcement applies during the preview, so a required template that matches nothing fails the diagnostic before any deployment begins. Exit code: 0 on pass, 2 on any connection failure, version violation, or required-template miss. Cross-platform (SQL Server, PostgreSQL, MySQL). (#310)
• MySQL recyclebin hooks — CustomTableDrop / CustomTableRestore parity. MySQL now supports the same custom table-removal hooks as SQL Server and PostgreSQL. When a SchemaSmith_CustomTableDrop procedure exists in the database, DropTablesRemovedFromProduct routes a removed table through it instead of issuing a plain DROP TABLE; and MissingTableAndColumnQuench calls SchemaSmith_CustomTableRestore for tables being added (in case they were custom-dropped), then skips recreating any the restore brought back so restored data survives. Both honor WhatIf (the preview shows the CALL … it would run). Enables recyclebin-style soft-drop/restore on MySQL. — #292
• ShouldApplyExpression accepts either a bare predicate or a full SELECT on every gate. Component gates (tables, columns, indexes, foreign keys, check constraints, statistics, and the platform-specific full-text / indexed-view / materialized-view carriers) historically required a bare boolean predicate, while script-folder gates required a full SELECT — writing the wrong form failed (e.g. SQL Server Msg 4145 on a component gate, a syntax error on a folder gate). Both forms now work on both kinds of gate: a folder gate wraps a bare predicate as SELECT CASE WHEN (…) THEN 1 ELSE 0 END, and a component gate strips a leading SELECT before embedding the predicate. Bare predicates are unchanged. Cross-platform (SQL Server, PostgreSQL, MySQL). — #282
• Runtime engine-version detection and version-adaptive code generation. SchemaSmith now detects each target server's version at deploy time (SQL Server major, PostgreSQL major, MySQL major.minor) and automatically adapts the DDL it generates where the supported version range diverges — so one package deploys correctly across, for example, PostgreSQL 15, 16, and 17 (the specific cases are in the ### Fixed entries below). Detection failure is a hard error — SchemaSmith never generates blind against an unknown target version. Cross-platform. — #296
• Always Encrypted columns: fail-closed guard on in-place encryption changes (SQL Server). SchemaQuench now raises a hard error before any DDL when a quench would require re-encrypting data on a populated column — changing EncryptionType, EncryptionKey, or EncryptionAlgorithm, or adding encryption to a previously-plaintext column that has rows. A standard (non-enclave) SQL Server holds no Column Master Key and cannot re-encrypt server-side; previously the attempt could produce a confusing SQL Server error mid-quench after partial DDL. The new guard fires immediately — in both live and WhatIf mode, naming [schema].[table].[column] — and the column is left untouched. Use a Before/After full-table rebuild with a Column Encryption Setting=Enabled connection for any encryption change on populated data. Adding a new encrypted column to an empty table continues to work normally. SQL Server only.
• DropColumnsRemovedFromProduct — gate column-drop-by-absence across a four-tier cascade. A new DropColumnsRemovedFromProduct flag (default true, preserving today's behavior) controls whether SchemaQuench drops columns that exist in the database but are absent from the table JSON. The flag resolves across four tiers — environment (SchemaQuench.settings.json / SmithySettings_DropColumnsRemovedFromProduct env var), product (Product.json), template (Template.json), and per-table (the table's .json file) — with explicit-false-sticky semantics: a false at any tier is a hard guardrail that cannot be re-enabled by a more-specific setting. A table can set its own false to protect its columns regardless of higher-tier settings; it cannot set true to override a higher-tier suppression. Before this flag, suppressing column drops required disabling the entire table-update phase (UpdateTables: false). Cross-platform (SQL Server, PostgreSQL, MySQL). — #270
• DropForeignKeysRemovedFromProduct — gate foreign-key-drop-by-absence across a four-tier cascade. A new DropForeignKeysRemovedFromProduct flag (default true, preserving today's behavior) controls whether SchemaQuench drops foreign keys that exist in the database but are absent from the table JSON. Same four-tier cascade as the other drop-control flags — environment (SchemaQuench.settings.json / SmithySettings_DropForeignKeysRemovedFromProduct env var), product (Product.json), template (Template.json), and per-table — with explicit-false-sticky semantics; a table can tighten to false to protect its own foreign keys but cannot re-enable a higher-tier suppression. Only by-absence removal is gated: a modified foreign key (same name, changed definition) is still dropped and recreated so the new definition applies. Cross-platform (SQL Server, PostgreSQL, MySQL). — #270
• DropCheckConstraintsRemovedFromProduct — gate check-constraint-drop-by-absence across a four-tier cascade. A new DropCheckConstraintsRemovedFromProduct flag (default true, preserving today's behavior) controls whether SchemaQuench drops table-level CHECK constraints that exist in the database but are absent from the table JSON. Same four-tier cascade and explicit-false-sticky semantics as the other drop-control flags; a table can tighten to false to protect its own check constraints. Only by-absence removal is gated — a modified check (same name, changed expression) is still dropped and recreated; column-level checks (driven by a column's CheckExpression) are governed by the column reconciliation, not this flag. Cross-platform (SQL Server, PostgreSQL, MySQL). — #270
• DropExcludeConstraintsRemovedFromProduct — gate exclude-constraint-drop-by-absence (PostgreSQL). A new DropExcludeConstraintsRemovedFromProduct flag (default true) controls whether SchemaQuench drops EXCLUDE constraints that exist in the database but are absent from the table JSON. EXCLUDE constraints are a PostgreSQL feature; the flag has no effect on SQL Server or MySQL. Same four-tier cascade and explicit-false-sticky semantics as the other drop-control flags, and only by-absence removal is gated (a modified exclude constraint still reconciles). — #270
• DropStatisticsRemovedFromProduct — gate statistics-drop-by-absence across a four-tier cascade. A new DropStatisticsRemovedFromProduct flag (default true) controls whether SchemaQuench drops user-created statistics objects that exist in the database but are absent from the table JSON. Same four-tier cascade and explicit-false-sticky semantics as the other drop-control flags. Only by-absence removal is gated (a modified statistics object still reconciles); auto-created statistics are never touched. SQL Server and PostgreSQL (MySQL has no separate statistics objects). — #270
• DropIndexesRemovedFromProduct — gate dropping product-owned indexes removed from the definition. A new DropIndexesRemovedFromProduct flag (default true) controls whether SchemaQuench drops an index it manages (product-owned) that has been removed from the table JSON. This is distinct from DropUnknownIndexes, which targets out-of-band indexes never managed by SchemaSmith. Same four-tier cascade and explicit-false-sticky semantics as the other drop-control flags; a table can tighten to false to protect its own indexes. On SQL Server and PostgreSQL it gates the removed-from-product drop directly; on MySQL it adds per-table suppression to the existing managed-index cleanup. — #270

Breaking Changes

• MinimumVersion in Product.json is now enforced as a pre-flight version floor (previously metadata only). The field was inert — documented as metadata only, with ValidationScript suggested for actual version gating. It now drives a real pre-flight gate: before any deployment work begins, SchemaQuench detects every resolved target's version and aborts the entire run — no partial deploy — if any target is below the declared floor, naming each below-floor server and its detected version. A product that declared a floor higher than one of its real targets will now abort where it previously deployed; set MinimumVersion to your true supported floor, or leave it blank for no floor. Accepted forms: SQL Server major (16) or release year (2022); PostgreSQL major (15); MySQL major.minor (8.0). — #296

Fixed

• DropUnknownIndexes was package-only; now environment-overridable. Setting DropUnknownIndexes in SchemaQuench.settings.json (or the SmithySettings_DropUnknownIndexes environment variable) now works as a deployment-wide guardrail. Previously the setting was read only from Product.json and Template.json; an environment-level false had no effect. — #270

• MySQL cleaned up orphaned foreign keys only when DropUnknownIndexes was enabled. On MySQL, dropping a foreign key that had been removed from the product definition was incorrectly gated on DropUnknownIndexes, so teams that left index-drops off never got foreign-key cleanup (SQL Server and PostgreSQL already dropped them independently). MySQL foreign-key-by-absence cleanup is now governed by its own DropForeignKeysRemovedFromProduct flag (default on), decoupled from index drops — bringing MySQL into line with the other engines. — #270

• SQL Server and MySQL never dropped table-level CHECK constraints removed from the product. Only PostgreSQL reconciled an orphaned table-level CHECK by absence; SQL Server and MySQL dropped a check only as a side effect of dropping its column, so a CHECK removed from a table's JSON lingered in the database. Both now drop orphaned table-level checks by absence (default on, governed by the new DropCheckConstraintsRemovedFromProduct flag), matching PostgreSQL. — #270

• SQL Server never dropped user-created statistics removed from the product. Only PostgreSQL reconciled an orphaned statistics object by absence; SQL Server dropped a statistics object only as a side effect of dropping or altering one of its columns, so a statistics definition removed from a table's JSON lingered in the database. SQL Server now drops orphaned user-created statistics by absence (default on, governed by the new DropStatisticsRemovedFromProduct flag), matching PostgreSQL. — #270

• --ForceReKindle was missing from the SchemaQuench --help listing. The switch (and its ForceReKindle settings key) shipped and worked since v2.1.0, but wasn't shown in the CLI help output, so it wasn't discoverable from --help. It's now listed alongside the other SchemaQuench switches.

• MySQL AutoIncrementValue was captured on extract but never applied on quench. Declaring AutoIncrementValue on a MySQL table now sets the AUTO_INCREMENT seed at quench time using set-if-higher semantics: the seed is only raised, never lowered, because MySQL silently clamps a below-current value to max+1 — skipping the statement when the declared value is not higher avoids phantom DDL on every quench. WhatIf-aware. Applies to new table creation and existing table modification. MySQL only — PostgreSQL controls the seed via its sequence scripts and SQL Server via IDENTITY(seed,inc) at column creation.

• Editor JSON schemas rejected valid package JSON. The .json-schemas/*.schema files SchemaSmith generates for editor validation (via SchemaTongs --WriteSchemasOnly) mis-typed several properties, so editors — and any JSON-Schema CI check — flagged correct table and template JSON as invalid. ulong properties (MySQL AutoIncrementValue) were typed as object instead of integer; the QuenchSlot enums (ProductQuenchSlot / TemplateQuenchSlot) were typed as integer instead of their serialized string names; the MySQL RowFormat pattern required upper-case values that never match what MySQL reports (Dynamic); foreign-key UpdateAction / DeleteAction rejected the empty (unspecified / NO ACTION default) value; and ServerToQuench and DatabaseIdentificationScript were marked schema-required despite having a default (the former) or a valid alternative (SchemaIdentificationScript, for the latter). The generated schemas now accept exactly the JSON the CLI itself produces and deploys. Affected all three engines where the property applies. — #315

• Column-level CheckExpression silently ignored on PostgreSQL and MySQL. A column with a CheckExpression property was applied as a check constraint on SQL Server but silently skipped on PostgreSQL and MySQL — the column JSON was deserialized through the base Column type, stripping the property before the quench scripts ran. The domain types for both engines now preserve CheckExpression through deserialization; the PostgreSQL quench creates (and idempotently re-applies) a column-level CHECK constraint using ALTER TABLE … ADD CONSTRAINT … CHECK (…); the MySQL quench does the same and additionally re-applies a modified table-level check expression. All three engines now behave consistently. — #313

• Cross-template special tokens ({{TableSchema_<TemplateName>}} and friends) never resolved. The five cross-template token families — {{TableSchema_<TemplateName>}}, {{ObjectScripts_<TemplateName>}}, {{QueryTokens_<TemplateName>}}, {{MaterializedViewSchema_<TemplateName>}}, and {{IndexedViewSchema_<TemplateName>}} — were never substituted, so a script in one template that read another template's schema received the literal token in its deployed SQL. The product-load step that detects which scripts carry these tokens matched the token name against the special-token tag prefixes (TableSchema_, …) with an exact-equals comparison, but the tags are prefixes and a real token is TableSchema_App — so the match never succeeded and the substitution pass was skipped entirely. The detection now matches by prefix, consistent with the rest of the token engine. Affected all three engines. — #299

• Phantom column-modify / primary-key drop+recreate on every quench for hand-authored decimal/numeric columns. A column whose authored DataType differed from the engine's canonical spelling only by whitespace (e.g. numeric(10,2) vs numeric(10, 2), a space before/inside the parens) or by the DECIMAL/NUMERIC synonym was wrongly detected as "modified" on every quench, re-altering the column — and on PostgreSQL and SQL Server drop/recreating any dependent primary key — so the deployment was never idempotent. The type-string comparison now normalizes whitespace around the structural delimiters and treats DECIMAL and NUMERIC as equivalent before comparing (the emitted DDL still uses the authored spelling verbatim). Affected all three engines. On MySQL the normalization is guarded so it never applies to ENUM/SET, whose parenthesized content is string values where whitespace is significant. — (#285)

• Phantom primary-key drop+recreate on every quench for naturally-authored PostgreSQL primary keys. A PostgreSQL primary key declared the natural way — an index entry with "PrimaryKey": true and no explicit "Unique": true — was wrongly classed as a "modified index" on every quench because the authored uniqueness (false) was compared against the existing PK backing index's indisunique (true), dropping and recreating the PK (and cascading dependent foreign keys) on every run. The index-modified comparison now treats a declared PrimaryKey or UniqueConstraint as implying uniqueness, matching the existing PK/unique index. SQL Server and MySQL already back-filled uniqueness from the primary-key flag at parse time and were unaffected. — (#285)

• Phantom column-modify on every quench for PostgreSQL VARCHAR/CHAR columns with a string-literal default. PostgreSQL stores a string-literal column default in the catalog with an explicit type cast — 'Standard'::character varying — but the modified-column comparison checked that against the authored Default ('Standard') verbatim, so a hand-authored column was classed as "modified" on every quench and re-issued ALTER COLUMN … SET DEFAULT. The default comparison now strips a trailing type cast from both the authored and the catalog value before comparing (via a new SchemaSmith.StripTypeCast helper), so a bare literal and the cast form SchemaTongs writes on extraction both converge to a no-op; the emitted DDL still uses the authored value verbatim. PostgreSQL only — SQL Server (parenthesized default, already stripped) and MySQL (bare value) were unaffected. — (#287)

• DropTablesRemovedFromProduct failed to drop a table still referenced by a foreign key. When a release both removed a table from the product and dropped a kept table's foreign key to it, deploying with DropTablesRemovedFromProduct: true aborted the quench — the removed-table drop ran before the foreign-key drop, so the table was still referenced when the drop was attempted (SQL Server: "Could not drop object … referenced by a FOREIGN KEY constraint"; PostgreSQL: "cannot drop table … because other objects depend on it"; MySQL: "Cannot drop table … referenced by a foreign key constraint"). Before dropping a table removed from the product, the quench now drops every foreign key that references it (from any table), so the table drop succeeds. The pre-drop honors WhatIf and works the same on the standard drop path and the CustomTableDrop recycle hook. Affected all three engines. — (#289)

• DropTablesRemovedFromProduct failed on system-versioned temporal tables (SQL Server). Removing a system-versioned temporal table from the product and deploying with DropTablesRemovedFromProduct: true aborted with error 13552 ("Drop table operation … not supported on system-versioned temporal tables"), because the removed-table drop issued a plain DROP TABLE while versioning was still on. The quench now turns system versioning off for a removed temporal table (capturing its history table first) before dropping it, then drops the now-orphaned history table; WhatIf previews the same steps. SQL Server only — PostgreSQL and MySQL have no system-versioning equivalent. — (#290)

• PostgreSQL CustomTableDrop hook generated a syntax error. When a SchemaSmith.CustomTableDrop procedure was installed, dropping a table removed from the product failed with 42601: syntax error at or near "END" — the generated CALL statement was missing the trailing semicolon the DROP TABLE branch already had, so it ran into the END of the wrapping DO block. The CALL is now terminated correctly. PostgreSQL only. — (#291)

• Data-delivery merge introspection hardened against identifier injection. The per-engine queries that read column metadata from the database to build a data-delivery MERGE interpolated the schema/table identifiers directly into the SQL. An identifier containing a single quote broke the query, and for names read from an introspected database (rather than the operator's own authored schema) the interpolation was an injection vector. Every such introspection predicate now binds the identifiers as query parameters instead of interpolating them. The generated merge script is unchanged. Affected all three engines.

• Data-delivery content-file resolution constrained to the template directory. A table's DataDelivery.ContentFile reference — which may come from an externally-authored schema package — was resolved with no containment check, so a ..-relative or absolute path could read a file outside the template root. Resolution now rejects rooted paths and any path that escapes the template root. Cross-platform.

• WhatIf could strand a removed table on PostgreSQL. A WhatIf deployment (WhatIfONLY / --WhatIf) was not fully read-only on PostgreSQL: the ownership-fixup procedures (FixupTableOwnership and its index / materialized-view siblings) ran their SchemaSmith.ProductOwnership INSERT/DELETE unconditionally — the caller never passed the WhatIf flag — so previewing a release that removes a table really deleted that table's ownership record. A subsequent real deployment then no longer recognized the table as product-owned and silently skipped dropping/recycling it, leaving the deployed schema diverged from the package. The fixup procedures now no-op under WhatIf. PostgreSQL only — SQL Server and MySQL perform ownership fixup inside their WhatIf-aware quench procedures and were unaffected. — #303

• Declared primary key silently not created when a same-column unique index already existed (SQL Server). When a table already had a unique index whose structure (columns, clustered, uniqueness) matched a PRIMARY KEY or UNIQUE constraint the package declared under a different name, the index-rename detection treated the two as a rename and sp_renamed the existing plain index into the constraint's name. The actual constraint was then never created — an ordinary index sat where the primary key should be (is_primary_key = 0) — with no error raised, so declared and deployed state diverged silently. A plain index and a primary-key / unique constraint are no longer treated as rename-equivalent (the match now requires the same constraint-ness), so the constraint is created, dropping the conflicting clustered index first when needed. Applies to both the full table quench and the index-only path. SQL Server only. — #304

• Replacing a clustered index in index-only mode failed with "Cannot create more than one clustered index" (SQL Server). An index-only deployment (IndexOnlyTableQuenches) that introduced a clustered index while a different clustered index still occupied the table's clustered slot aborted with error 1913 — common when overlaying indexes on a table whose other indexes are intentionally left in place (DropUnknownIndexes off). The index-only path now drops a conflicting clustered index before creating the new one, matching the full table quench's long-standing behavior. SQL Server only. — #302

• PostgreSQL generated-column expression changes hard-failed on PostgreSQL 15/16. Changing a stored generated column's expression emitted ALTER COLUMN … SET EXPRESSION AS (…), which exists only on PostgreSQL 17+ — on 15/16 the deployment aborted with a 42601 syntax error on every quench. On a target detected below 17, SchemaQuench now applies the change by dropping and re-adding the generated column (carrying its data type, collation, nullability, storage, and compression) instead of SET EXPRESSION; on 17+ the in-place SET EXPRESSION is unchanged. PostgreSQL only. — #296

• PostgreSQL data-delivery delete-on-absence hard-failed on PostgreSQL 15/16. A data delivery configured to delete rows absent from the source (MergeDelete) emitted MERGE … WHEN NOT MATCHED BY SOURCE THEN DELETE, which requires PostgreSQL 17+ — on 15/16 the deployment failed. On a target detected below 17, SchemaQuench now performs the delete-on-absence as a standalone DELETE … WHERE NOT EXISTS (…) after the INSERT/UPDATE MERGE — keyed identically, honoring the same MergeFilter, and handling NULL-safe (*-prefixed) keys; on 17+ the single-statement MERGE form is unchanged. PostgreSQL only. — #241

• Removing identity / AUTO_INCREMENT from a column was not applied (PostgreSQL, MySQL). Declaring a column without identity that was previously GENERATED … AS IDENTITY (PostgreSQL) or AUTO_INCREMENT (MySQL) left the deployed column unchanged — declared and deployed state diverged silently. PostgreSQL now emits ALTER COLUMN … DROP IDENTITY IF EXISTS; MySQL now detects the auto_increment delta and re-issues MODIFY COLUMN without it (the symmetric add case is now detected too). Both are data-preserving. (SQL Server already applied identity removal via a data-preserving column swap and is unchanged.)

• Data delivery with a NULL-safe (*-prefixed) match key generated invalid PostgreSQL MERGE. A data-delivery key column marked NULL-safe with a leading * produced a MERGE … ON clause referencing a literal *-prefixed column name ("Source"."*Id") plus an unquoted alias, so the merge failed at deploy time. The match-column builder now strips the marker and quotes both operands, producing a correct NULL-safe correspondence (matching the delete-on-absence fallback). PostgreSQL only.

• Token values containing single quotes broke generated SQL when the token appeared more than once in different contexts. SqlScript.TokenReplace decided whether to SQL-escape a token's value from the context of its first occurrence and then applied that one decision to every occurrence — so a quote-bearing token mentioned first in a comment (or otherwise outside a literal) and then used inside a '…' string literal was substituted un-escaped in the literal, terminating it early ("Unclosed quotation mark", with the leaked text compiled as invalid SQL). Escaping is now decided per occurrence from each occurrence's own surrounding context. Affects any quote-bearing token (cross-template schema tokens, query tokens, and others) used in two different contexts; all three engines. — #308

• SchemaTongs extracted Always Encrypted columns with EncryptionAlgorithm and EncryptionKey in the wrong fields. SchemaSmith.GenerateTableJSON populated EncryptionAlgorithm from sys.columns.column_encryption_key_database_name (the CEK name) and EncryptionKey from sys.columns.encryption_algorithm_name (the algorithm) — exactly swapped. A schema package produced by SchemaTongs could not round-trip: re-quenching the extracted JSON deployed columns with algorithm and key reversed, so the DDL SQL Server received was incorrect and the deployed column did not match the original. The extraction now maps EncryptionType ← encryption_type_desc, EncryptionAlgorithm ← encryption_algorithm_name, and EncryptionKey ← the bracketed CEK name via a sys.column_encryption_keys join. SQL Server only. — (#311)