SchemaSmith v2.7.2

Breaking Changes

• SQL Server: leaving EnableCDC out of a table now leaves its Change Data Capture as it is — #469. It used to mean false, so a deploy disabled CDC on any table where it had been turned on outside the package, dropping the capture instances and their change history. EnableCDC now works like EnableChangeTracking: true turns CDC on, false turns it off, and leaving it out changes nothing. A package that relied on leaving it out to turn CDC off must now say "EnableCDC": false. SchemaTongs writes EnableCDC only for a tracked table.

Added

• --Validate reports a foreign-key name used on two tables (SS-FK-006). MySQL names foreign keys per database and SQL Server per schema, so the second one fails the deploy: an error. MariaDB names them per table from 12.1, so there it is a warning, unless the product's MinimumVersion is 12.1 or later.
• SQL Server CDC can identify rows by a unique index (CdcIndexName). Set it on a table and SchemaSmith passes it to sp_cdc_enable_table as @index_name, so a table with no primary key can have net changes, and a capture instance a DBA set up on a unique index round-trips: SchemaTongs extracts CdcIndexName when the newest instance uses an index other than the primary key, along with its net changes. A declared index the newest instance does not use rotates to a new capture instance, like a CdcFilegroup change; one that is not a declared unique index over NOT NULL columns is refused by name before CDC is enabled. --Validate reports it as SS-CDC-002, and SS-CDC-001 now also warns when it is set without EnableCDC. A rotation that declares no index keeps the one the old instance used, with or without net changes. — #470

Changed

• DataTongs now exits non-zero when a table is not extracted — #439. It used to exit 0 however many tables failed, and wrote nothing to DataTongs - Errors.log. It now exits 2 when a table fails to extract. It exits 1 when it finishes without producing everything asked: a skipped table (missing from the source, no key columns, or malformed KeyColumns), a column left out because its type cannot be delivered (a rowversion does not count), a merge script not written for a MySQL source older than 8.0, or a script whose data token could not be wired. Each such table is named, with the reason, in the error log. A pipeline that ran DataTongs and ignored failures will now see them.
• A --ConfigFile that does not exist now stops the run. It used to be ignored: the tool ran with no settings, or with a same-named file from its own folder. Every tool now stops before anything connects and names the path it tried. The default settings file is unchanged: when it is absent the tool still runs on its other settings. — #448
• A LogHygiene block the tools cannot read is now reported. A list setting written as a single value ("ScrubTokens": "DeployKey"), a key that is not a setting, and a LogTokens that is not true or false were ignored in silence, so a value the user meant to mask was logged in clear. Each is now a warning naming the key. The SchemaTongs settings template suggested one such shape; it now shows "ScrubTokens": [ "ApiKey" ]. — #447
• A TemplateOrder entry is a template folder name, matched without regard to case, and never a path. Each entry used to be pasted into a file path, so the operating system decided what it meant: main loaded folder Main on Windows and failed on Linux, while ./Main loaded on Linux and failed on Windows. An entry now matches a folder directly under Templates/ whatever its case, on every OS. An entry that is not a plain folder name -- a path separator, . or .., a leading or trailing space, a trailing dot, or any of < > : " | ? * -- or that matches no folder, stops the deploy and is an SS-LOAD-002 finding in --Validate, which also now reports a missing Template.json there instead of stopping. — #450

Fixed

• CDC or Change Tracking enabled by a Before script was recorded as downgraded on the first deploy, so no tables were tracked (SQL Server) — #432. The database-level check ran before the Before slot, so a new or restored database read as not enabled and the deploy exited 0 with nothing captured; the second deploy tracked everything. When the template has Before scripts, the check now runs after them. Without any, it still runs up front, so UnsupportedFeaturePolicy=fail refuses before anything is created.
• A failure writing a local file was reported as a lost server connection. A missing checkpoint folder, a file in use or a full disk on the machine running SchemaQuench produced "Lost connection to ... check the server/container logs and available memory". These now report the file error itself.
• After fifty runs from one folder, the tools stopped archiving their logs. Every earlier backup folder counted against the limit meant for two runs colliding, so each run printed "UNABLE TO BACKUP LOG FILES" and kept no copy. Backups now continue into the next free folder.
• A columnstore index declared without a CompressionType failed every redeploy (SQL Server). The compression check compared the index's COLUMNSTORE compression with the declared default NONE and tried to rebuild it to NONE, which SQL Server refuses. A columnstore declaration that names no compression now means COLUMNSTORE. Extracted packages state it, so they were not affected.
• Compression and columnstore failed the deploy on Express, Standard and Web editions before SQL Server 2016 SP1, and a nonclustered columnstore index made its table read-only on 2012 and 2014 (SQL Server). Those editions refuse compression and columnstore (7738, 35315); the table or index is now created uncompressed, and the columnstore index skipped, each with a downgrade recorded. On 2012 and 2014 a nonclustered columnstore index waits for 2016, and 2014 no longer receives a clustered columnstore index beside rowstore indexes, which it refuses. An index already on the table is left alone.
• A full-text column declared with STATISTICAL_SEMANTICS failed the deploy where no semantic language statistics database is registered (SQL Server). SQL Server refuses the whole full-text index there (41209). The index is now created without semantic statistics under the unsupported-feature policy, with a downgrade recorded; fail refuses before anything is created.
• A SchemaShears patch that suppressed every drop still dropped indexes removed from the tables it carried — #441. The Indexes category stamped only DropUnknownIndexes. DropIndexesRemovedFromProduct, which is on by default, stayed on, so an index the source product had removed from an included table was dropped on deploy. The category now covers both flags. The SchemaShears reference also notes the two drop settings a patch cannot carry, DropEventsRemovedFromProduct and DropPeriodsRemovedFromProduct.
• WhatIf left out the foreign keys a deploy would create, and the view and event steps. A WhatIf run skipped the foreign-key step entirely, so a package adding a foreign key reported foreignKeys: 0 in the WhatIf summary and the real run then created it. Materialized views (PostgreSQL), indexed views (SQL Server) and declared scheduled events (MySQL, MariaDB) were skipped the same way, with a one-line "would quench" note in their place. WhatIf now runs each of these steps in preview mode, so the summary lists what they would change. — #442
• Log masking showed parts of some secrets, and hid text after others. The configuration echo at the top of each log masked a sensitively named list but printed its entries in clear, and printed every ScriptTokens value even with "LogTokens": false. A password whose quote never closed, or that held an escaped quote ("", '', N'...', JSON \"), showed the part after its first ;. Masking an unquoted password ran on past the end of its line, so a stack trace logged after a connection string was lost, and a very long word in a logged value made masking take quadratic time. Passwords set in SQL without a Password= key were not masked at all: OLD_PASSWORD = '...' (SQL Server), PASSWORD '...' (PostgreSQL), IDENTIFIED BY '...' and REPLACE '...' (MySQL, MariaDB), a credential's SECRET = '...' (SQL Server), and environment forms such as PGPASSWORD= and DB_PASSWORD=, so a logged script or an error that echoed one showed the password; PASSWORD('a;b') showed the part after the ;. — #447
• Date-like strings were rewritten in the host's time zone. JSON was read with a default that turns a string that looks like an ISO date into a date, so the value was written back converted to the time zone of the machine running the tool. It happened to Extensions values when SchemaTongs refreshed a table, to the definitions SchemaQuench sends to the database, to hand-edited values in regenerated .json-schemas files, to delivered table data on MariaDB 10.2-10.5 when a delivery is large enough to be sent in chunks, and to XML data delivery (--DeliveryEncoding=Xml), where a timestamp also lost its fraction and date-like text was rewritten in the host's date format. --Validate also rejected a date-like JSON-schema enum value that matched exactly. Strings now stay exactly as written. — #449
• A Product:Path or ScriptPath of spaces was used as a folder name. SchemaTongs wrote the package into a folder named " ", which Windows tools cannot open, and DataTongs failed on it. Both settings are now trimmed, and a blank value means the current directory.
• Package files with an upper-case extension were skipped on Linux and macOS. A script saved as status.SQL, or a table as Orders.JSON, was read on Windows and silently left out elsewhere. File extensions now match without regard to case on every OS. — #450
• --Validate matched the scripted form of an enum, sequence, domain or event by a path substring. A directory above the package named Enum Types, Sequences, Domain Types or Events, or a same-named subfolder inside another folder, made every Objects script count as that kind, so the duplicate-declaration findings (SS-ENUM-001, SS-SEQ-001, SS-DOM-001 and the event equivalent) reported objects that were declared only once. A schema-qualified script name such as public.status.sql was not matched at all. The folder is now the script folder's own path under the template, a schema. prefix on the file name is recognised, and a finding names the object's schema.
• A SQL Server database in a collation other than the server's could not be deployed to — #454. SchemaQuench's working temp tables declared their text columns without a collation, so they took the server's (tempdb's), and the first comparison with the database's own text failed: "Cannot resolve the collation conflict", from the first new table on. Every such column now uses the database's collation. The integration suite now deploys into databases collated differently from the server, and a build check refuses a temp table text column without one.
• After Product scripts and the product version stamp ran after a template had failed — #455. With ContinueOnDatabaseFailure (the default), a run in which a template failed still ran the product's After Product scripts and its VersionStampScript, recording the version as deployed. Both are now skipped after any failure, and the run says so; the exit code is unchanged (2). A --ResumeQuench that finishes the failed work runs them.
• SQL Server column collations were lost on extraction and reset on redeploy — #451. SchemaTongs read each column's collation from the wrong catalog view and wrote "Collation": "" for every column, so deploying an extracted package moved every column with its own collation to the database default, silently. Extraction now writes the collation of any column that differs from the database default. Separately, SchemaQuench's ALTER COLUMN for a type or length change named a collation only when the collation was also changing, which reset the column to the database default — and failed with error 5074 on an indexed column. It now always states the collation: the declared one, the database default for an empty declaration, or the column's current one under IGNORE. Packages extracted by earlier versions carry empty column collations; re-extract them.
• A SQL Server XML(DOCUMENT ...) column extracted as CONTENT, and was altered on every deploy. The column's document flag was never read, so SchemaTongs wrote the typed column without DOCUMENT — a redeploy accepted fragments the column had refused — and SchemaQuench, comparing the same way, re-altered a declared DOCUMENT column on every run. Both now read it. A declaration that spells out CONTENT, the default, no longer churns either.
• Altering a SQL Server column that an unchanged computed column reads failed — #456. The step meant to take such a computed column off first never selected one, so any type, length, nullability or collation change to the column it reads stopped the deploy: "ALTER TABLE ALTER COLUMN failed because one or more objects access this column". Those computed columns are now dropped and added back around the change. The match comes from SQL Server's dependency catalog rather than the expression text, which also caught columns whose names merely contained the altered one's.
• A change to a SQL Server temporal table's primary-key column failed with "Could not drop constraint" — #457. SQL Server will not drop a system-versioned table's primary key while versioning is on, and the deploy never turned it off. Versioning is now suspended around the rebuild, the change is mirrored onto the history table, and versioning resumes with the same history table and a data consistency check, keeping its rows. A run that stops part-way is resumed by the next one.
• DataTongs broke text values containing },{ when writing extracted JSON — #463. Rows were split onto separate lines wherever },{ appeared, including inside strings, so a SQL Server or PostgreSQL column holding [{"a":1},{"b":2}] produced a file that was not valid JSON. Line breaks now go between rows only; files without such values are written exactly as before.
• XML data delivery rounded wide numeric values — #465. Converting extracted JSON to XML read numbers as doubles, so 12345678901234567890.123456789 lost its trailing digits. Each number is now carried as its literal text.
• DataTongs could match rows on a key that was not unique across the table — #466. With no primary key, the merge key could come from a PostgreSQL partial or SQL Server filtered unique index, so rows sharing its value were merged into one: silently below PostgreSQL 15, and as a failed redelivery on 15+. A PostgreSQL expression index gave an empty or partial key, and INCLUDE columns were treated as key columns, so a changed included value inserted a duplicate. Only a unique index over the whole table is now used, and only its key columns.
• A deploy that would drop, rename or redefine an index a CDC capture instance uses is refused before any index is touched (SQL Server). SQL Server refuses the drop itself, but only part-way through the index work, and it allows a rename that leaves the instance naming an index that is gone and free to be dropped. The refusal names the index and the capture instance, and how to move off it. — #470
• SQL Server spatial, JSON and selective XML indexes no longer break a package or get dropped. SchemaTongs extracted a spatial or JSON index as a plain index over the geometry or JSON column, which the next deploy refused, and a selective XML index as a primary XML index that the deploy silently never created. A deploy that drops unknown indexes read them the same way and dropped them. Extraction now leaves these kinds out and names each in a warning, and deploys neither compare nor drop them. A package cannot declare them yet; create them with an After script. — #471
• SQL Server: a package with CompressionType MIXED deploys. SchemaTongs writes MIXED for a table or index whose partitions are compressed differently, meaning "leave it as it is". The index compression fixup wrote it into ALTER INDEX ... REBUILD WITH (DATA_COMPRESSION=MIXED), which failed the deploy with "Incorrect syntax near 'MIXED'", on the first deploy and every one after, in full and index-only deploys. — #472
• SQL Server: a memory-optimized table with a clustered columnstore index or a unique constraint deploys from its own extraction. The inline index list left a clustered columnstore index out of the CREATE TABLE, so the deploy then tried a CREATE INDEX that SQL Server refuses on a memory-optimized table, and it created a unique constraint as a unique index. — #473
• A template that targets several databases deploys every script to every one of them, each with its own query-token values. Each database's pass shared the template's scripts, so a script applied in one database was skipped in the databases that started their pass afterwards -- which still reported success -- and the first database's query-token values were written into the scripts every later database ran (with query tokens present, the later databases failed instead). Every script slot was affected. — #475