Converting tables to STRICT

A STRICT table enforces its declared types instead of preferring them: a value that does not fit the column is refused with an error rather than converted into something that does. Converting an existing table to STRICT is therefore a question about the data already in it, not only about the column types — which is why this is a wizard that reads the tables first and offers the conversion only at the end.

Select one or more tables in the tree and choose Object → Convert to STRICT..., or the same command from the tree's context menu. With more than one convertible table selected it reads Convert Selected Tables to STRICT... instead.

The command is offered for ordinary tables that are not STRICT already. It is unavailable for a table that is already STRICT, for a view, for a virtual table, and for the shadow tables a virtual table keeps for itself — the _content, _data and _idx tables beneath an FTS or R-Tree table, whose shape belongs to the module that owns them. Where a selection holds some of those and some ordinary tables, the command converts the ordinary ones and leaves the rest alone.

Nothing changes until the last step

The wizard works in two stages, and they are deliberately separate. It examines the selected tables and reports what a conversion would do to them; only then does it offer to carry it out, showing you the SQL before it runs. Until you reach that step the database is untouched, so leaving at any earlier page costs nothing. The wizard opens on a page that says as much, and you can read past it.

Tables to convert

The next page lists the tables the wizard will work on, so you can check the selection before it reads anything.

Status stays empty in ordinary use. The command filters out what it cannot convert before the wizard opens, so every table listed here is one it will examine; the column is there to name a reason in the cases that reach the list anyway, such as a shadow table belonging to a virtual table.

Next goes on to read the data.

Examining the data

Every value in every selected table is read once. This is the slow part on a large table, and it is done before any decisions, so that changing your mind afterwards is free.

A bar reports how far through the selected tables it is, and the line under it gives the time so far, followed by about 0:20 remaining once there is enough of the run behind it to be worth quoting. When it finishes, the line gives the total instead. The log beneath names each table as it is examined and what was found in it.

Reading a table is one query and cannot be stopped part way, so Cancel is refused while this page is working. Nothing is being written, so waiting costs only the time.

What each column would become

A tree of the tables and their columns, with the type each column is proposed to take and what that would do to the rows already there.

Column	Meaning
Now	What the column is declared as today, or (none) where it has no type at all. On a table's own row this is the number of rows it holds.
Becomes	The STRICT type the column will be declared as. Change it to tighten or loosen the column.
Refused	Rows STRICT would reject. One is enough to stop the table being converted.
Converted	Rows that would be kept but stored under a different type — an integer in a column becoming REAL, for instance.
Damaged	Rows that would come back as a different value. Only integers larger than 253 put into a REAL column, which no floating-point number holds exactly.
Note	What the counts amount to, in a few words. On a table's own row it is the reason the table cannot be converted, where it cannot.

The counts are recalculated as you change a type, so you can see what a choice costs before making it. Above, PUR_PRICE is a CURRENCY column holding 26 whole numbers and 10 fractional ones; tightening it from ANY to REAL keeps every row but stores 26 of them as reals rather than integers, and the wizard says so.

What is proposed, and why

Each column is offered the STRICT type its own declaration implies, where every value already suits it: CHAR(7) becomes TEXT, FLOAT becomes REAL, INTEGER stays INTEGER. The declaration says what the author of the table meant, and a conversion is not the place to overrule it.

Where the values do not suit that type, or where the declaration has no STRICT equivalent at all, the column is offered ANY — which accepts every value and every storage class exactly as they are, and enforces nothing.

Three common cases end up on ANY:

• A DECIMAL or NUMERIC column. SQLite has no STRICT equivalent, and NUMERIC affinity keeps whole numbers as integers — so a price column normally holds both integers and reals. INTEGER would refuse the reals, and REAL would convert every integer.
• A DATE, DATETIME or BOOLEAN column. SQLite has no such types either; they carry NUMERIC affinity, and what they hold depends entirely on how the data was written.
• A column with no declared type. Its affinity is BLOB, but proposing BLOB would refuse nearly everything it holds.

The proposal is deliberately not the tightest type the data would allow. A DATE column that happens to hold nothing but ISO text could be declared TEXT today and would still refuse a Julian day number tomorrow; tightening a column is a decision about what it is for, not only about what is in it. Make it yourself, with the counts in front of you.

Ready to convert

The last page before anything happens shows the exact SQL that will run, so you can read it — or copy it and run it yourself — before it does.

Then Converting does the work, one table at a time. Each table's script opens a savepoint of its own, so a table that fails is rolled back and left exactly as it was while the others still convert. Cancel is disabled while this runs, and Escape is answered with a message rather than obeyed: there is no honest way to stop half way through a rebuilt table. The last page reports how many tables were converted and how many were left as they were, followed by the log of what happened to each.

What a conversion changes besides the types

• Sizes and scales are dropped. A STRICT table takes the six type names bare — TEXT(50) and DECIMAL(10,2) are both rejected when the table is created. A column declared DECIMAL(10,2) therefore loses the two decimal places it was displayed with, and a VARCHAR(50) becomes an unsized TEXT, which this program reads as a memo column.
• A primary key becomes NOT NULL, whether or not it says so. A key column that currently holds a null therefore has nowhere to put it, and stops the conversion.
• The table is rebuilt — moved aside, recreated and copied — because there is no ALTER TABLE that makes a table STRICT. Its indexes, triggers and dependent objects are carried across.

What cannot be converted

The wizard reports these rather than failing part way through. The reason appears in the Note column beside the table on the mapping page. Where no choice of types could help, the table's columns are left collapsed, there being nothing worth changing under it; where a column is the problem, the table stays open so you can loosen that column. If nothing in the selection can be converted, Next says so instead of going on.

• A table with generated or hidden columns, which a rebuild cannot carry across.
• A table other tables reference, while a transaction is in progress. A rebuild moves the table aside under a temporary name, and foreign keys have to be off while it does — which SQLite does not allow inside a transaction. Commit or roll back the transaction and the table converts normally.
• A table where a column's proposed type would refuse rows. The note names the column and how many rows; loosen that column, or correct the data first — the grid shades values whose storage class does not suit their column, which is where to find them.
• A table whose primary key holds nulls, since a STRICT primary key is NOT NULL whether or not it says so. The note names the column and the count.

Note

Converting cannot be undone, and there is no ALTER TABLE that makes a STRICT table ordinary again — that is another rebuild. Back up the database first if you may want to go back.

See also: Data types · Editing a table · Applying the changes after editing a table