Closes#37473
### Background
Previously, we enabled foreign keys at all times for our sqlite database
that we use for client-side state.
The problem with this is that In sqlite, `alter table` is somewhat
limited, so for many migrations, you must *recreate* the table: create a
new table called e.g. `workspace__2`, then copy all of the data from
`workspaces` into `workspace__2`, then delete the old `workspaces` table
and rename `workspaces__2` to `workspaces`. The way foreign keys work in
sqlite, when we delete the old table, all of its associated records in
other tables will be deleted due to `on delete cascade` clauses.
Unfortunately, one of the types of associated records that can be
deleted are `editors`, which sometimes store unsaved text. It is very
bad to delete these records, as they are the *only* place that this
unsaved text is stored.
This has already happened multiple times as we have migrated tables as
we develop Zed, but I caused it to happened again in
https://github.com/zed-industries/zed/pull/36714.
### The Fix
The Sqlite docs recommend a multi-step approach to migrations where you:
* disable foreign keys
* start a transaction
* create a new table
* populate the new table with data from the old table
* delete the old table
* rename the new table to the old name
* run a foreign key check
* if it passes, commit the transaction
* enable foreign keys
In this PR, I've adjusted our sqlite migration code path to follow this
pattern more closely. Specifically, we disable foreign key checks before
running migrations, run a foreign key check before committing, and then
enable foreign key checks after the migrations are done.
In addition, I've added a generic query that we run *before* running the
foreign key check that explicitly deletes any rows that have dangling
foreign keys. This way, we avoid failing the migration (and breaking the
app) if a migration deletes data that *does* cause associated records to
need to be deleted.
But now, in the common case where we migrate old data in the new table
and keep the ids, all of the associated data will be preserved.
Release Notes:
- Fixed a bug where workspace state would be lost when upgrading from
Zed 0.201.x. or below.
23dc1f5ea4