Start fresh with database migrations.

The migration path from the original project inception to now was complicated and buggy, and stranded _both_ Kit and I with broken databases due to oversights and incomplete migrations. We've agreed to start fresh, once.

If this is mistakenly started with an original-schema-flavour DB, startup will be aborted.

1 files changed, 64 insertions, 1 deletions

diff --git a/src/db.rs b/src/db.rs
index 93a1169..e09b0ba 100644
--- a/src/db.rs
+++ b/src/db.rs
@@ -2,8 +2,13 @@ use std::str::FromStr;
 
 use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
 
-pub async fn prepare(url: &str) -> sqlx::Result<SqlitePool> {
+pub async fn prepare(url: &str) -> Result<SqlitePool, Error> {
     let pool = create(url).await?;
+
+    // First migration of original migration series, from commit
+    // 9bd6d9862b1c243def02200bca2cfbf578ad2a2f or earlier.
+    reject_migration(&pool, "20240831024047", "login", "9949D238C4099295EC4BEE734BFDA8D87513B2973DFB895352A11AB01DD46CB95314B7F1B3431B77E3444A165FE3DC28").await?;
+
     sqlx::migrate!().run(&pool).await?;
     Ok(pool)
 }
@@ -17,6 +22,64 @@ async fn create(database_url: &str) -> sqlx::Result<SqlitePool> {
     Ok(pool)
 }
 
+async fn reject_migration(
+    pool: &SqlitePool,
+    version: &str,
+    description: &str,
+    checksum_hex: &str,
+) -> Result<(), Error> {
+    if !sqlx::query_scalar!(
+        r#"
+            select count(*) as "exists: bool"
+            from sqlite_master
+            where name = '_sqlx_migrations'
+        "#
+    )
+    .fetch_one(pool)
+    .await?
+    {
+        // No migrations table; this is a fresh DB.
+        return Ok(());
+    }
+
+    if !sqlx::query_scalar!(
+        r#"
+            select count(*) as "exists: bool"
+            from _sqlx_migrations
+            where version = $1
+                and description = $2
+                and hex(checksum) = $3
+        "#,
+        version,
+        description,
+        checksum_hex,
+    )
+    .fetch_one(pool)
+    .await?
+    {
+        // Rejected migration does not exist; this DB never ran it.
+        return Ok(());
+    }
+
+    Err(Error::Rejected(version.into(), description.into()))
+}
+
+/// Errors occurring during database setup.
+#[derive(Debug, thiserror::Error)]
+pub enum Error {
+    /// Failure due to a database error. See [`sqlx::Error`].
+    #[error(transparent)]
+    Database(#[from] sqlx::Error),
+    /// Failure due to a database migration error. See
+    /// [`sqlx::migrate::MigrateError`].
+    #[error(transparent)]
+    Migration(#[from] sqlx::migrate::MigrateError),
+    /// Failure because the database contains a migration from an unsupported
+    /// schema version.
+    #[error("database contains rejected migration {0}:{1}, move it aside")]
+    Rejected(String, String),
+}
+
 pub trait NotFound {
     type Ok;
     fn not_found<E, F>(self, map: F) -> Result<Self::Ok, E>