PLT-8312: Use combined LIKE/Full Text search for channels. (#8029)

* PLT-8312: Use combined LIKE/Full Text search for channels.

* Code tidyup

* Get it working consistently and update unit tests.

* Fix code style.

3 files changed, 140 insertions, 24 deletions

diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go
index 9869b3720..af78b06e0 100644
--- a/store/sqlstore/channel_store.go
+++ b/store/sqlstore/channel_store.go
@@ -1308,23 +1308,73 @@ func (s SqlChannelStore) SearchMore(userId string, teamId string, term string) s
 func (s SqlChannelStore) performSearch(searchQuery string, term string, parameters map[string]interface{}) store.StoreResult {
 	result := store.StoreResult{}
 
+	// Copy the terms as we will need to prepare them differently for each search type.
+	likeTerm := term
+	fulltextTerm := term
+
+	searchColumns := "Name, DisplayName"
+
 	// These chars must be removed from the like query.
-	for _, c := range ignoreUserSearchChar {
-		term = strings.Replace(term, c, "", -1)
+	for _, c := range ignoreLikeSearchChar {
+		likeTerm = strings.Replace(likeTerm, c, "", -1)
 	}
 
 	// These chars must be escaped in the like query.
-	for _, c := range escapeUserSearchChar {
-		term = strings.Replace(term, c, "*"+c, -1)
+	for _, c := range escapeLikeSearchChar {
+		likeTerm = strings.Replace(likeTerm, c, "*"+c, -1)
 	}
 
-	if term == "" {
+	// These chars must be treated as spaces in the fulltext query.
+	for _, c := range spaceFulltextSearchChar {
+		fulltextTerm = strings.Replace(fulltextTerm, c, " ", -1)
+	}
+
+	if likeTerm == "" {
+		// If the likeTerm is empty after preparing, then don't bother searching.
 		searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1)
 	} else {
-		isPostgreSQL := s.DriverName() == model.DATABASE_DRIVER_POSTGRES
-		searchQuery = generateSearchQuery(searchQuery, []string{term}, []string{"Name", "DisplayName"}, parameters, isPostgreSQL)
+		// Prepare the LIKE portion of the query.
+		var searchFields []string
+		for _, field := range strings.Split(searchColumns, ", ") {
+			if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+				searchFields = append(searchFields, fmt.Sprintf("lower(%s) LIKE lower(%s) escape '*'", field, ":LikeTerm"))
+			} else {
+				searchFields = append(searchFields, fmt.Sprintf("%s LIKE %s escape '*'", field, ":LikeTerm"))
+			}
+		}
+		likeSearchClause := fmt.Sprintf("(%s)", strings.Join(searchFields, " OR "))
+		parameters["LikeTerm"] = fmt.Sprintf("%s%%", likeTerm)
+
+		// Prepare the FULLTEXT portion of the query.
+		if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
+			splitTerm := strings.Fields(fulltextTerm)
+			for i, t := range strings.Fields(fulltextTerm) {
+				if i == len(splitTerm)-1 {
+					splitTerm[i] = t + ":*"
+				} else {
+					splitTerm[i] = t + ":* &"
+				}
+			}
+
+			fulltextTerm = strings.Join(splitTerm, " ")
+
+			fulltextSearchClause := fmt.Sprintf("((%s) @@ to_tsquery(:FulltextTerm))", convertMySQLFullTextColumnsToPostgres(searchColumns))
+			searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "AND ("+likeSearchClause+" OR "+fulltextSearchClause+")", 1)
+		} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
+			splitTerm := strings.Fields(fulltextTerm)
+			for i, t := range strings.Fields(fulltextTerm) {
+				splitTerm[i] = "+" + t + "*"
+			}
+
+			fulltextTerm = strings.Join(splitTerm, " ")
+
+			fulltextSearchClause := fmt.Sprintf("MATCH(%s) AGAINST (:FulltextTerm IN BOOLEAN MODE)", searchColumns)
+			searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", fmt.Sprintf("AND (%s OR %s)", likeSearchClause, fulltextSearchClause), 1)
+		}
 	}
 
+	parameters["FulltextTerm"] = fulltextTerm
+
 	var channels model.ChannelList
 
 	if _, err := s.GetReplica().Select(&channels, searchQuery, parameters); err != nil {
diff --git a/store/sqlstore/user_store.go b/store/sqlstore/user_store.go
index 5ecc1fdda..549377d61 100644
--- a/store/sqlstore/user_store.go
+++ b/store/sqlstore/user_store.go
@@ -1022,15 +1022,30 @@ func (us SqlUserStore) SearchInChannel(channelId string, term string, options ma
 	})
 }
 
-var escapeUserSearchChar = []string{
+var escapeLikeSearchChar = []string{
 	"%",
 	"_",
 }
 
-var ignoreUserSearchChar = []string{
+var ignoreLikeSearchChar = []string{
 	"*",
 }
 
+var spaceFulltextSearchChar = []string{
+	"<",
+	">",
+	"+",
+	"-",
+	"(",
+	")",
+	"~",
+	":",
+	"*",
+	"\"",
+	"!",
+	"@",
+}
+
 func generateSearchQuery(searchQuery string, terms []string, fields []string, parameters map[string]interface{}, isPostgreSQL bool) string {
 	searchTerms := []string{}
 	for i, term := range terms {
@@ -1054,12 +1069,12 @@ func (us SqlUserStore) performSearch(searchQuery string, term string, options ma
 	result := store.StoreResult{}
 
 	// These chars must be removed from the like query.
-	for _, c := range ignoreUserSearchChar {
+	for _, c := range ignoreLikeSearchChar {
 		term = strings.Replace(term, c, "", -1)
 	}
 
 	// These chars must be escaped in the like query.
-	for _, c := range escapeUserSearchChar {
+	for _, c := range escapeLikeSearchChar {
 		term = strings.Replace(term, c, "*"+c, -1)
 	}
 
diff --git a/store/storetest/channel_store.go b/store/storetest/channel_store.go
index 853de67d8..12781bdad 100644
--- a/store/storetest/channel_store.go
+++ b/store/storetest/channel_store.go
@@ -1829,15 +1829,19 @@ func testChannelStoreSearchMore(t *testing.T, ss store.Store) {
 		}
 	}
 
-	if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, "off-topics"); result.Err != nil {
-		t.Fatal(result.Err)
-	} else {
-		channels := result.Data.(*model.ChannelList)
-		if len(*channels) != 0 {
-			t.Logf("%v\n", *channels)
-			t.Fatal("should be empty")
+	/*
+		// Disabling this check as it will fail on PostgreSQL as we have "liberalised" channel matching to deal with
+		// Full-Text Stemming Limitations.
+		if result := <-ss.Channel().SearchMore(m1.UserId, o1.TeamId, "off-topics"); result.Err != nil {
+			t.Fatal(result.Err)
+		} else {
+			channels := result.Data.(*model.ChannelList)
+			if len(*channels) != 0 {
+				t.Logf("%v\n", *channels)
+				t.Fatal("should be empty")
+			}
 		}
-	}
+	*/
 }
 
 func testChannelStoreSearchInTeam(t *testing.T, ss store.Store) {
@@ -1922,6 +1926,20 @@ func testChannelStoreSearchInTeam(t *testing.T, ss store.Store) {
 	o9.Type = model.CHANNEL_OPEN
 	store.Must(ss.Channel().Save(&o9, -1))
 
+	o10 := model.Channel{}
+	o10.TeamId = o1.TeamId
+	o10.DisplayName = "The"
+	o10.Name = "the"
+	o10.Type = model.CHANNEL_OPEN
+	store.Must(ss.Channel().Save(&o10, -1))
+
+	o11 := model.Channel{}
+	o11.TeamId = o1.TeamId
+	o11.DisplayName = "Native Mobile Apps"
+	o11.Name = "native-mobile-apps"
+	o11.Type = model.CHANNEL_OPEN
+	store.Must(ss.Channel().Save(&o11, -1))
+
 	if result := <-ss.Channel().SearchInTeam(o1.TeamId, "ChannelA"); result.Err != nil {
 		t.Fatal(result.Err)
 	} else {
@@ -1979,24 +1997,57 @@ func testChannelStoreSearchInTeam(t *testing.T, ss store.Store) {
 		}
 	}
 
-	if result := <-ss.Channel().SearchInTeam(o1.TeamId, "off-topics"); result.Err != nil {
+	/*
+		// Disabling this check as it will fail on PostgreSQL as we have "liberalised" channel matching to deal with
+		// Full-Text Stemming Limitations.
+		if result := <-ss.Channel().SearchMore(m1.
+		if result := <-ss.Channel().SearchInTeam(o1.TeamId, "off-topics"); result.Err != nil {
+			t.Fatal(result.Err)
+		} else {
+			channels := result.Data.(*model.ChannelList)
+			if len(*channels) != 0 {
+				t.Fatal("should be empty")
+			}
+		}
+	*/
+
+	if result := <-ss.Channel().SearchInTeam(o1.TeamId, "town square"); result.Err != nil {
 		t.Fatal(result.Err)
 	} else {
 		channels := result.Data.(*model.ChannelList)
-		if len(*channels) != 0 {
-			t.Fatal("should be empty")
+		if len(*channels) != 1 {
+			t.Fatal("should return 1 channel")
+		}
+
+		if (*channels)[0].Name != o9.Name {
+			t.Fatal("wrong channel returned")
 		}
 	}
 
-	if result := <-ss.Channel().SearchInTeam(o1.TeamId, "town square"); result.Err != nil {
+	if result := <-ss.Channel().SearchInTeam(o1.TeamId, "the"); result.Err != nil {
 		t.Fatal(result.Err)
 	} else {
 		channels := result.Data.(*model.ChannelList)
+		t.Log(channels.ToJson())
 		if len(*channels) != 1 {
 			t.Fatal("should return 1 channel")
 		}
 
-		if (*channels)[0].Name != o9.Name {
+		if (*channels)[0].Name != o10.Name {
+			t.Fatal("wrong channel returned")
+		}
+	}
+
+	if result := <-ss.Channel().SearchInTeam(o1.TeamId, "Mobile"); result.Err != nil {
+		t.Fatal(result.Err)
+	} else {
+		channels := result.Data.(*model.ChannelList)
+		t.Log(channels.ToJson())
+		if len(*channels) != 1 {
+			t.Fatal("should return 1 channel")
+		}
+
+		if (*channels)[0].Name != o11.Name {
 			t.Fatal("wrong channel returned")
 		}
 	}