Rewrite the SQLite backend for more efficient/future-proof table rewriting.
Review Request #11080 — Created July 14, 2020 and submitted
SQLite is a quirky database when it comes to how you make changes to the
schema. In most databases you perform an
ALTER TABLEand then, behind
the scenes, the database server will effectively create a new table with
the desired schema, copy everything over, drop the old table, rename
the new table in its place, and rebuild any indexes. With SQLite, all
this is the responsibility of the caller.
The way this worked before in Django Evolution was that the SQLite
backend had a number of utility functions for generating different
stages of this table rebuild, making much of this consistent, but still
putting a lot of work on the calling code. This was harder to maintain,
and began showing its age while working on some fixes to the process
(which this change begins to implement).
Now, there's a
_rebuild_table()method that handles all the work of
rebuilding the table, preventing callers from being able to make a
mistake. This greatly simplifies the calling code.
The old implementation performed some legacy steps in its table
recreation, doing basically:CREATE TEMPORARY TABLE <temp_name> (<simplified new schema>); INSERT INTO <temp_name> (<new columns>) SELECT <old columns> from <name>; DROP TABLE <name>; CREATE TABLE <name> (<new schema>); INSERT INTO <name> (<new columns>) SELECT <new columns> FROM <temp_name>; DROP TABLE <temp_name>;
That last table creation was inefficient and unnecessary, and the manner
in which it was all performed prevented some constraints from being able
to be applied or recorded. It was, basically, a lossy table rebuild.
The new implementation improves upon this greatly, replacing the last 3
lines with an
ALTER TABLE ... RENAME TO ...;, which we can rely on
for all supported versions of Django (given their requirements for
SQLite). It also allows us to create that table with the full schema,
constraints and all, and restore indexes after. This looks like:CREATE TABLE <temp_name> (<full new schema>); INSERT INTO <temp_name> (<new columns>) SELECT <old columns> from <name>; DROP TABLE <name>; ALTER TABLE <temp_name> RENAME TO <name>; CREATE INDEX ...;` ...
The new code also restores any indexes. The end result is a completely
compatible table (with the exception of when a primary key is renamed,
but that will be handled in another change). The result
Since all this code was getting rewritte and cleaned up, I've taken the
opportunity to document it in the process. Going forward, we'll have a
much better foundation upon which to test and improve SQLite support.
Unit tests pass on all supported versions of Django.
Tested with SQLite 3.24 and 3.28. (This distinction is important because
there are a lot of changes to table alteration between these versions.)
Tested on Python 2.7, 3.6, 3.7, and 3.8.
Removed an unused variable and fixed an alignment issue.
Revision 2 (+1762 -3186)
Checks run (2 succeeded)