[chore] thread_id migration tweaks (#4198)

# Description

- add a `migrations run` sub command to allow simply starting / stopping the db service, useful if you want to run *only* the migrations, both for testing or if you have a speedier box you want to run them on
- tweaks to log messages
- moved more stages outside of transactions, on sqlite if the transactions were getting too lengthy it could occasionally show an `sqlite: disk i/o error`

## Checklist

- [x] I/we have read the [GoToSocial contribution guidelines](https://codeberg.org/superseriousbusiness/gotosocial/src/branch/main/CONTRIBUTING.md).
- [x] I/we have discussed the proposed changes already, either in an issue on the repository, or in the Matrix chat.
- [x] I/we have not leveraged AI to create the proposed changes.
- [x] I/we have performed a self-review of added code.
- [x] I/we have written code that is legible and maintainable by others.
- [x] I/we have commented the added code, particularly in hard-to-understand areas.
- [ ] I/we have made any necessary changes to documentation.
- [ ] I/we have added tests that cover new code.
- [x] I/we have run tests and they pass locally with the changes.
- [x] I/we have run `go fmt ./...` and `golangci-lint run`.

Reviewed-on: https://codeberg.org/superseriousbusiness/gotosocial/pulls/4198
Co-authored-by: kim <grufwub@gmail.com>
Co-committed-by: kim <grufwub@gmail.com>

2 files changed, 55 insertions, 51 deletions

diff --git a/internal/db/bundb/migrations/20250415111056_thread_all_statuses.go b/internal/db/bundb/migrations/20250415111056_thread_all_statuses.go
index 4213da4f2..fc02d1e40 100644
--- a/internal/db/bundb/migrations/20250415111056_thread_all_statuses.go
+++ b/internal/db/bundb/migrations/20250415111056_thread_all_statuses.go
@@ -49,10 +49,16 @@ func init() {
 			"thread_id", "thread_id_new", 1)
 
 		var sr statusRethreader
-		var total uint64
+		var count int
 		var maxID string
 		var statuses []*oldmodel.Status
 
+		// Get a total count of all statuses before migration.
+		total, err := db.NewSelect().Table("statuses").Count(ctx)
+		if err != nil {
+			return gtserror.Newf("error getting status table count: %w", err)
+		}
+
 		// Start at largest
 		// possible ULID value.
 		maxID = id.Highest
@@ -97,7 +103,7 @@ func init() {
 					if err != nil {
 						return gtserror.Newf("error rethreading status %s: %w", status.URI, err)
 					}
-					total += n
+					count += n
 				}
 
 				return nil
@@ -105,7 +111,12 @@ func init() {
 				return err
 			}
 
-			log.Infof(ctx, "[%d] rethreading statuses (top-level)", total)
+			log.Infof(ctx, "[approx %d of %d] rethreading statuses (top-level)", count, total)
+		}
+
+		// Attempt to merge any sqlite write-ahead-log.
+		if err := doWALCheckpoint(ctx, db); err != nil {
+			return err
 		}
 
 		log.Warn(ctx, "rethreading straggler statuses, this will take a *long* time")
@@ -146,7 +157,7 @@ func init() {
 					if err != nil {
 						return gtserror.Newf("error rethreading status %s: %w", status.URI, err)
 					}
-					total += n
+					count += n
 				}
 
 				return nil
@@ -154,7 +165,7 @@ func init() {
 				return err
 			}
 
-			log.Infof(ctx, "[%d] rethreading statuses (stragglers)", total)
+			log.Infof(ctx, "[approx %d of %d] rethreading statuses (stragglers)", count, total)
 		}
 
 		// Attempt to merge any sqlite write-ahead-log.
@@ -165,59 +176,28 @@ func init() {
 		log.Info(ctx, "dropping old thread_to_statuses table")
 		if _, err := db.NewDropTable().
 			Table("thread_to_statuses").
-			IfExists().
 			Exec(ctx); err != nil {
 			return gtserror.Newf("error dropping old thread_to_statuses table: %w", err)
 		}
 
-		// Run the majority of the thread_id_new -> thread_id migration in a tx.
-		if err := db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
-			log.Info(ctx, "creating new statuses thread_id column")
-			if _, err := tx.NewAddColumn().
-				Table("statuses").
-				ColumnExpr(newColDef).
-				Exec(ctx); err != nil {
-				return gtserror.Newf("error creating new thread_id column: %w", err)
-			}
+		log.Info(ctx, "creating new statuses thread_id column")
+		if _, err := db.NewAddColumn().
+			Table("statuses").
+			ColumnExpr(newColDef).
+			Exec(ctx); err != nil {
+			return gtserror.Newf("error adding new thread_id column: %w", err)
+		}
 
-			log.Info(ctx, "setting thread_id_new = thread_id (this may take a while...)")
-			if err := batchUpdateByID(ctx, tx,
+		log.Info(ctx, "setting thread_id_new = thread_id (this may take a while...)")
+		if err := db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
+			return batchUpdateByID(ctx, tx,
 				"statuses",           // table
 				"id",                 // batchByCol
 				"UPDATE ? SET ? = ?", // updateQuery
 				[]any{bun.Ident("statuses"),
 					bun.Ident("thread_id_new"),
 					bun.Ident("thread_id")},
-			); err != nil {
-				return err
-			}
-
-			log.Info(ctx, "dropping old statuses thread_id index")
-			if _, err := tx.NewDropIndex().
-				Index("statuses_thread_id_idx").
-				Exec(ctx); err != nil {
-				return gtserror.Newf("error dropping old thread_id index: %w", err)
-			}
-
-			log.Info(ctx, "dropping old statuses thread_id column")
-			if _, err := tx.NewDropColumn().
-				Table("statuses").
-				Column("thread_id").
-				Exec(ctx); err != nil {
-				return gtserror.Newf("error dropping old thread_id column: %w", err)
-			}
-
-			log.Info(ctx, "renaming thread_id_new to thread_id")
-			if _, err := tx.NewRaw(
-				"ALTER TABLE ? RENAME COLUMN ? TO ?",
-				bun.Ident("statuses"),
-				bun.Ident("thread_id_new"),
-				bun.Ident("thread_id"),
-			).Exec(ctx); err != nil {
-				return gtserror.Newf("error renaming new column: %w", err)
-			}
-
-			return nil
+			)
 		}); err != nil {
 			return err
 		}
@@ -227,12 +207,36 @@ func init() {
 			return err
 		}
 
+		log.Info(ctx, "dropping old statuses thread_id index")
+		if _, err := db.NewDropIndex().
+			Index("statuses_thread_id_idx").
+			Exec(ctx); err != nil {
+			return gtserror.Newf("error dropping old thread_id index: %w", err)
+		}
+
+		log.Info(ctx, "dropping old statuses thread_id column")
+		if _, err := db.NewDropColumn().
+			Table("statuses").
+			Column("thread_id").
+			Exec(ctx); err != nil {
+			return gtserror.Newf("error dropping old thread_id column: %w", err)
+		}
+
+		log.Info(ctx, "renaming thread_id_new to thread_id")
+		if _, err := db.NewRaw(
+			"ALTER TABLE ? RENAME COLUMN ? TO ?",
+			bun.Ident("statuses"),
+			bun.Ident("thread_id_new"),
+			bun.Ident("thread_id"),
+		).Exec(ctx); err != nil {
+			return gtserror.Newf("error renaming new column: %w", err)
+		}
+
 		log.Info(ctx, "creating new statuses thread_id index")
 		if _, err := db.NewCreateIndex().
 			Table("statuses").
 			Index("statuses_thread_id_idx").
 			Column("thread_id").
-			IfNotExists().
 			Exec(ctx); err != nil {
 			return gtserror.Newf("error creating new thread_id index: %w", err)
 		}
@@ -286,7 +290,7 @@ type statusRethreader struct {
 
 // rethreadStatus is the main logic handler for statusRethreader{}. this is what gets called from the migration
 // in order to trigger a status rethreading operation for the given status, returning total number rethreaded.
-func (sr *statusRethreader) rethreadStatus(ctx context.Context, tx bun.Tx, status *oldmodel.Status) (uint64, error) {
+func (sr *statusRethreader) rethreadStatus(ctx context.Context, tx bun.Tx, status *oldmodel.Status) (int, error) {
 
 	// Zero slice and
 	// map ptr values.
@@ -435,7 +439,7 @@ func (sr *statusRethreader) rethreadStatus(ctx context.Context, tx bun.Tx, statu
 		}
 	}
 
-	return uint64(total), nil
+	return total, nil
 }
 
 // append will append the given status to the internal tracking of statusRethreader{} for
diff --git a/internal/db/bundb/migrations/util.go b/internal/db/bundb/migrations/util.go
index 8da861df7..f20f23c3f 100644
--- a/internal/db/bundb/migrations/util.go
+++ b/internal/db/bundb/migrations/util.go
@@ -417,7 +417,7 @@ func getModelField(db bun.IDB, rtype reflect.Type, fieldName string) (*schema.Fi
 }
 
 // doesColumnExist safely checks whether given column exists on table, handling both SQLite and PostgreSQL appropriately.
-func doesColumnExist(ctx context.Context, tx bun.Tx, table, col string) (bool, error) {
+func doesColumnExist(ctx context.Context, tx bun.IDB, table, col string) (bool, error) {
 	var n int
 	var err error
 	switch tx.Dialect().Name() {