Weasel 9.28.0

A SQLite release. Five changes, and unlike 9.27 — which was nine cases of Weasel reading a schema back wrong — four of these change the DDL Weasel emits. A SQLite database created by 9.28 does not have the same column types as one created by 9.27.

Read this before upgrading

If any table declares a column as numeric or decimal, the first migration after upgrading throws on every AutoCreate setting except AutoCreate.All. You get a Weasel.Core.SchemaMigrationException reading Cannot derive schema migrations for … AutoCreate.CreateOrUpdate.

The change itself is safe and the rebuild that applies it copies every row. It is the guard in front of it that refuses: SQLite cannot express a column type change as an ALTER, so the delta comes back as SchemaPatchDifference.Invalid, and AssertPatchingIsValid rejects Invalid on anything but AutoCreate.All regardless of whether a rebuild could apply it. That guard is not specific to this change — it rejects any SQLite column-type change the same way — and fixing it is a follow-up.

Run the first migration with AutoCreate.All, or declare the column real if REAL is what you actually wanted, or stay on 9.27.

What changed

#533 — numeric and decimal have NUMERIC affinity, not REAL. SQLite gives a declared type REAL affinity only when it contains REAL, FLOA or DOUB. NUMERIC and DECIMAL get NUMERIC affinity, and the two store data differently: NUMERIC keeps a whole number an integer, REAL converts it to a float. An id declared numeric came back as 1.0 rather than 1. This applies to a type declared as a string; AddColumn<decimal> is unchanged and still maps to REAL.

#532 — a column keeps the type it was declared with. TableColumn's constructor ran every type through ConvertSynonyms, collapsing declared types onto SQLite's storage classes. SQLite stores the declared text verbatim and derives affinity from it by substring rules, so this was not cosmetic: a model asking for TIMESTAMP (NUMERIC affinity) got a TEXT column, and reading an existing database back lost what that database actually said. This is what makes Weasel usable against a SQLite database it did not create.

#534 — NOT NULL survives on a primary key column. The column declaration suppressed NOT NULL whenever it emitted an inline PRIMARY KEY, on the grounds that SQLite applies it implicitly. It does not. Outside a WITHOUT ROWID table only INTEGER PRIMARY KEY is safe, and only because it is a rowid alias whose NULL is replaced by the next rowid rather than stored. A REAL or TEXT primary key stores the NULL, so a rebuilt table accepted keys the original rejected.

#531 — a view body is split on its own AS. This one is not SQLite-only; it affects SQL Server too. Both providers found a view's body by taking everything after the first " AS ", which is wrong whenever the view's own AS stands on a line by itself — the first match is then a column alias inside the SELECT list. It failed silently: both sides of a delta went through the same extraction, so the truncation cancelled out and the comparison reported no drift, while the view that would have been rebuilt was not valid SQL at all. Extraction now lives in Weasel.Core.ViewDefinition.ExtractBody and skips comments, string literals, delimited identifiers and parenthesised column lists. PostgreSQL and Oracle are unaffected — their catalogs return the body already.

#535 — STRICT tables accept the types they are given. A STRICT table accepts only INT, INTEGER, REAL, TEXT, BLOB and ANY, so declaring a column numeric or decimal on one failed outright with unknown datatype. NUMERIC now maps to ANY there — the one STRICT type that keeps a whole number an integer rather than converting it to a float. A parameterized type such as VARCHAR(255) was rejected the same way, and for longer than the other changes have existed; it now maps to TEXT.

Upgrading

Beyond the numeric/decimal break above, two things are worth knowing.

Existing tables are not rebuilt for #532: comparison normalizes both sides, so a column stored as TEXT and a model saying DATETIME still compare equal. But a table rebuilt for some other reason is recreated from the model's declared types, and a value that parses as a number — a bare 2024 in a DATE column, say — is copied into the new column as a number rather than as text.

And if you match existing views by declaring them in Weasel, a view whose stored text put AS on its own line was being compared on a truncated body. It will now compare on the whole one, which may surface a delta that was previously invisible.

Full notes: https://weasel.jasperfx.net/release-9-28