diff options
| -rw-r--r-- | internal/db/bundb/search.go | 33 | ||||
| -rw-r--r-- | internal/db/bundb/search_test.go | 17 | ||||
| -rw-r--r-- | internal/db/bundb/util.go | 37 |
3 files changed, 58 insertions, 29 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 diff --git a/internal/db/bundb/search_test.go b/internal/db/bundb/search_test.go index bc791271e..75a2d8c8e 100644 --- a/internal/db/bundb/search_test.go +++ b/internal/db/bundb/search_test.go @@ -46,6 +46,15 @@ func (suite *SearchTestSuite) TestSearchAccounts1HappyWithPrefix() { suite.Len(accounts, 1) } +func (suite *SearchTestSuite) TestSearchAccounts1HappyWithPrefixUpper() { + testAccount := suite.testAccounts["local_account_1"] + + // Query will just look for usernames that start with "1HAPPY". + accounts, err := suite.db.SearchForAccounts(context.Background(), testAccount.ID, "@1HAPPY", "", "", 10, false, 0) + suite.NoError(err) + suite.Len(accounts, 1) +} + func (suite *SearchTestSuite) TestSearchAccounts1HappyNoPrefix() { testAccount := suite.testAccounts["local_account_1"] @@ -63,6 +72,14 @@ func (suite *SearchTestSuite) TestSearchAccountsTurtleFollowing() { suite.Len(accounts, 1) } +func (suite *SearchTestSuite) TestSearchAccountsTurtleFollowingUpper() { + testAccount := suite.testAccounts["local_account_1"] + + accounts, err := suite.db.SearchForAccounts(context.Background(), testAccount.ID, "TURTLE", "", "", 10, true, 0) + suite.NoError(err) + suite.Len(accounts, 1) +} + func (suite *SearchTestSuite) TestSearchAccountsPostFollowing() { testAccount := suite.testAccounts["local_account_1"] diff --git a/internal/db/bundb/util.go b/internal/db/bundb/util.go index a2bc87b88..cee20bbe1 100644 --- a/internal/db/bundb/util.go +++ b/internal/db/bundb/util.go @@ -23,8 +23,10 @@ import ( "github.com/superseriousbusiness/gotosocial/internal/cache" "github.com/superseriousbusiness/gotosocial/internal/db" + "github.com/superseriousbusiness/gotosocial/internal/log" "github.com/superseriousbusiness/gotosocial/internal/paging" "github.com/uptrace/bun" + "github.com/uptrace/bun/dialect" ) // likeEscaper is a thread-safe string replacer which escapes @@ -37,10 +39,29 @@ var likeEscaper = strings.NewReplacer( `_`, `\_`, // Exactly one char. ) +// likeOperator returns an appropriate LIKE or +// ILIKE operator for the given query's dialect. +func likeOperator(query *bun.SelectQuery) string { + const ( + like = "LIKE" + ilike = "ILIKE" + ) + + d := query.Dialect().Name() + if d == dialect.SQLite { + return like + } else if d == dialect.PG { + return ilike + } + + log.Panicf(nil, "db conn %s was neither pg nor sqlite", d) + return "" +} + // whereLike appends a WHERE clause to the // given SelectQuery, which searches for // matches of `search` in the given subQuery -// using LIKE. +// using LIKE (SQLite) or ILIKE (Postgres). func whereLike( query *bun.SelectQuery, subject interface{}, @@ -54,11 +75,14 @@ func whereLike( // zero or more chars around the query. search = `%` + search + `%` + // Get appropriate operator. + like := likeOperator(query) + // Append resulting WHERE // clause to the main query. return query.Where( - "(?) LIKE ? ESCAPE ?", - subject, search, `\`, + "(?) ? ? ESCAPE ?", + subject, bun.Safe(like), search, `\`, ) } @@ -78,11 +102,14 @@ func whereStartsLike( // zero or more chars after the query. search += `%` + // Get appropriate operator. + like := likeOperator(query) + // Append resulting WHERE // clause to the main query. return query.Where( - "(?) LIKE ? ESCAPE ?", - subject, search, `\`, + "(?) ? ? ESCAPE ?", + subject, bun.Safe(like), search, `\`, ) } |