[dbrobins]

Indeed things are happening in the background, though these are focussed towards a completely revised schema. You'll be happy to know that FOREIGN keys are leveraged heavily in it. Currently I'm benchmarking an EAV/CR schema that will provide foundation for all content (eg. audio, video, pictures, etc).

CC me on the trac patch and I'll review it for inclusion.

If you're redesigning the entire schema, is it worthwhile for me to make a patch that adds foreign keys that are only going to be entirely replaced? If you have a repo where you're working on this, I'd like to help, or at least see how things are going.

I also discovered that SQLite spells "alter table X add constraint" as "rename table X to oldX; create table X with constraint; copy rows from oldX to X; drop table oldX", which makes it worse. Not impossible, as that could be done as part of the upgrade process.

Having appropriate foreign keys with cascading deletes will also mean that a lot of the cleanup code will go away - no need to explicitly delete from the link tables - although it will still be necessary to clean up referenced tables like actors.