Introduction

So, in SQLite, ALTER TABLE is pretty limited. You can only add and delete columns and rename tables. Want to change a table constraint? SQLite has another procedure for that. The basic idea is that you create a new table that has the constraint you want, copy in the data from the old table, remove the old table, and rename the new one to the old one. Seems simple enough.

The Problem

CREATE TABLE Foo (mem1 INTEGER, mem2 INTEGER);
CREATE VIEW FooView AS SELECT * FROM Foo;

CREATE TABLE NewFoo (mem1 INTEGER, mem2 INTEGER);
DROP TABLE Foo;
ALTER TABLE NewFoo RENAME TO Foo;

Runtime error: error in view FooView: no such table: main.Foo

Nope.

Now, certain items like an INSERT ON Foo trigger are implicitly deleted, and it is explained in the docs that these items need to be re-created. Fine. But then the docs say,

If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.

This would seem to imply that if the view is compatible with the migration there is no need to change anything.

But we observed the opposite in the above example where we did not actually change a thing about Foo. You actually have to drop the view in order for the ALTER TABLE statement to run.

What this means is that you have to drop EVERY SINGLE VIEW OR TRIGGER THAT EVEN THINKS ABOUT THE TABLE UNDER MIGRATION. And re-create it again after the new table is in place.

If you have 30 moderately complicated views and triggers on a table, which can happen if it's doing a lot of heavy lifting in your entity model, suddenly your migration could easily approach a thousand lines.

Ick, ick, ick.

What to do about it

Simply do not use views and triggers if you can possibly help it.

Triggers can be done in the application layer.

And all views are are re-usable queries. Such a query can just as easily be a query string put in an easily accessible location. You will not have migration headaches that way.

The only thing besides the plain entity relationships in the database should be constraints on the database so that you can trust that the representation is always sound. Everything else belongs in the application, because SQL is just not built for business logic. Much less SQLite.

Please send comments to blogger-jack@pearson.onl.