[performance] improved enum migrations (#3782)

* updates the enum migration to perform a singular update for all values, using an SQL case statement

* fix logging

* fix code comment

* well i guess we'll get rid of the useful but unused function then, linter. fine, i see how it is!

* append to byte buffer instead of WriteString() to shut the linter up (i know you're reading this, linter)

2 files changed, 23 insertions, 18 deletions

diff --git a/internal/db/bundb/migrations/20241121121623_enum_strings_to_ints.go b/internal/db/bundb/migrations/20241121121623_enum_strings_to_ints.go
index 5f3eb1409..60f99de85 100644
--- a/internal/db/bundb/migrations/20241121121623_enum_strings_to_ints.go
+++ b/internal/db/bundb/migrations/20241121121623_enum_strings_to_ints.go
@@ -71,7 +71,7 @@ func init() {
 			// we must drop all indices that rely on it.
 			log.Info(ctx, "dropping old visibility indexes...")
 			for _, index := range visIndices {
-				log.Info(ctx, "dropping old index %s...", index.name)
+				log.Infof(ctx, "dropping old index %s...", index.name)
 				if _, err := tx.NewDropIndex().
 					Index(index.name).
 					Exec(ctx); err != nil {
@@ -93,7 +93,7 @@ func init() {
 			// Recreate the visibility indices.
 			log.Info(ctx, "creating new visibility indexes...")
 			for _, index := range visIndices {
-				log.Info(ctx, "creating new index %s...", index.name)
+				log.Infof(ctx, "creating new index %s...", index.name)
 				q := tx.NewCreateIndex().
 					Table("statuses").
 					Index(index.name).
diff --git a/internal/db/bundb/migrations/util.go b/internal/db/bundb/migrations/util.go
index edf7c1d05..bae5750df 100644
--- a/internal/db/bundb/migrations/util.go
+++ b/internal/db/bundb/migrations/util.go
@@ -82,26 +82,31 @@ func convertEnums[OldType ~string, NewType ~int16](
 		return gtserror.Newf("error selecting total count: %w", err)
 	}
 
-	var updated int
+	var args []any
+	var qbuf byteutil.Buffer
+
+	// Prepare a singular UPDATE statement using
+	// SET $newColumn = (CASE $column WHEN $old THEN $new ... END)
+	qbuf.B = append(qbuf.B, "UPDATE ? SET ? = (CASE ? "...)
+	args = append(args, bun.Ident(table))
+	args = append(args, bun.Ident(newColumn))
+	args = append(args, bun.Ident(column))
 	for old, new := range mapping {
+		qbuf.B = append(qbuf.B, "WHEN ? THEN ? "...)
+		args = append(args, old, new)
+	}
+	qbuf.B = append(qbuf.B, "ELSE ? END)"...)
+	args = append(args, *defaultValue)
 
-		// Update old to new values.
-		res, err := tx.NewUpdate().
-			Table(table).
-			Where("? = ?", bun.Ident(column), old).
-			Set("? = ?", bun.Ident(newColumn), new).
-			Exec(ctx)
-		if err != nil {
-			return gtserror.Newf("error updating old column values: %w", err)
-		}
-
-		// Count number items updated.
-		n, _ := res.RowsAffected()
-		updated += int(n)
+	// Execute the prepared raw query with arguments.
+	res, err := tx.NewRaw(qbuf.String(), args...).Exec(ctx)
+	if err != nil {
+		return gtserror.Newf("error updating old column values: %w", err)
 	}
 
-	// Check total updated.
-	if total != updated {
+	// Count number items updated.
+	updated, _ := res.RowsAffected()
+	if total != int(updated) {
 		log.Warnf(ctx, "total=%d does not match updated=%d", total, updated)
 	}