[bugfix] Better Postgres search case insensitivity (#2526)

* [bugfix] Better Postgres search case insensitivity

* use ilike for postgres

1 files changed, 9 insertions, 24 deletions

diff --git a/internal/db/bundb/search.go b/internal/db/bundb/search.go
index 61e52ce06..f9c2df1f8 100644
--- a/internal/db/bundb/search.go
+++ b/internal/db/bundb/search.go
@@ -133,8 +133,7 @@ func (s *searchDB) SearchForAccounts(
 		// Normalize it and just look for
 		// usernames that start with query.
 		query = query[1:]
-		subQ := s.accountUsername()
-		q = whereStartsLike(q, subQ, query)
+		q = whereStartsLike(q, bun.Ident("account.username"), query)
 	} else {
 		// Query looks like arbitrary string.
 		// Search using LIKE for matches of query
@@ -199,14 +198,6 @@ func (s *searchDB) followedAccounts(accountID string) *bun.SelectQuery {
 		Where("? = ?", bun.Ident("follow.account_id"), accountID)
 }
 
-// accountUsername returns a subquery that just selects
-// from account usernames, without concatenation.
-func (s *searchDB) accountUsername() *bun.SelectQuery {
-	return s.db.
-		NewSelect().
-		Column("account.username")
-}
-
 // accountText returns a subquery that selects a concatenation
 // of account username and display name as "account_text". If
 // `following` is true, then account note will also be included
@@ -242,11 +233,8 @@ func (s *searchDB) accountText(following bool) *bun.SelectQuery {
 	// different number of placeholders depending on
 	// following/not following. COALESCE calls ensure
 	// that we're not trying to concatenate null values.
-	//
-	// SQLite search is case insensitive.
-	// Postgres searches get lowercased.
-	d := s.db.Dialect().Name()
-	switch {
+
+	switch d := s.db.Dialect().Name(); {
 
 	case d == dialect.SQLite && following:
 		query = "? || COALESCE(?, ?) || COALESCE(?, ?) AS ?"
@@ -255,13 +243,13 @@ func (s *searchDB) accountText(following bool) *bun.SelectQuery {
 		query = "? || COALESCE(?, ?) AS ?"
 
 	case d == dialect.PG && following:
-		query = "LOWER(CONCAT(?, COALESCE(?, ?), COALESCE(?, ?))) AS ?"
+		query = "CONCAT(?, COALESCE(?, ?), COALESCE(?, ?)) AS ?"
 
 	case d == dialect.PG && !following:
-		query = "LOWER(CONCAT(?, COALESCE(?, ?))) AS ?"
+		query = "CONCAT(?, COALESCE(?, ?)) AS ?"
 
 	default:
-		panic("db conn was neither pg not sqlite")
+		log.Panicf(nil, "db conn %s was neither pg nor sqlite", d)
 	}
 
 	return accountText.ColumnExpr(query, args...)
@@ -385,10 +373,7 @@ func (s *searchDB) statusText() *bun.SelectQuery {
 
 	// SQLite and Postgres use different
 	// syntaxes for concatenation.
-	//
-	// SQLite search is case insensitive.
-	// Postgres searches get lowercased.
-	switch s.db.Dialect().Name() {
+	switch d := s.db.Dialect().Name(); d {
 
 	case dialect.SQLite:
 		statusText = statusText.ColumnExpr(
@@ -398,12 +383,12 @@ func (s *searchDB) statusText() *bun.SelectQuery {
 
 	case dialect.PG:
 		statusText = statusText.ColumnExpr(
-			"LOWER(CONCAT(?, COALESCE(?, ?))) AS ?",
+			"CONCAT(?, COALESCE(?, ?)) AS ?",
 			bun.Ident("status.content"), bun.Ident("status.content_warning"), "",
 			bun.Ident("status_text"))
 
 	default:
-		panic("db conn was neither pg not sqlite")
+		log.Panicf(nil, "db conn %s was neither pg nor sqlite", d)
 	}
 
 	return statusText