From 365b8b465e8a53ebb2da2bf3aef659ac81a2bc6a Mon Sep 17 00:00:00 2001 From: Joram Wilander Date: Wed, 19 Oct 2016 14:49:25 -0400 Subject: Merging performance branch into master (#4268) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit * improve performance on sendNotifications * Fix SQL queries * Remove get direct profiles, not needed anymore * Add raw data to error details if AppError fails to decode * men * Fix decode (#4052) * Fixing json decode * Adding unit test * Initial work for client scaling (#4051) * Begin adding paging to profiles API * Added more paging functionality * Finish hooking up admin console user lists * Add API for searching users and add searching to all user lists * Add lazy loading of profiles * Revert config.json * Fix unit tests and some style issues * Add GetProfilesFromList to Go driver and fix web unit test * Update etag for GetProfiles * Updating ui for filters and pagination (#4044) * Updating UI for pagination * Adjusting margins for filter row * Adjusting margin for specific modals * Adding relative padding to system console * Adjusting responsive view * Update client user tests * Minor fixes for direct messages modal (#4056) * Remove some unneeded initial load calls (#4057) * UX updates to user lists, added smart counts and bug fixes (#4059) * Improved getExplicitMentions and unit tests (#4064) * Refactor getting posts to lazy load profiles correctly (#4062) * Comment out SetActiveChannel test (#4066) * Profiler cpu, block, and memory profiler. (#4081) * Fix TestSetActiveChannel unit test (#4071) * Fixing build failure caused by dependancies updating (#4076) * Adding profiler * Fix admin_team_member_dropdown eslint errors * Bumping session cache size (#4077) * Bumping session cache size * Bumping status cache * Refactor how the client handles channel members to be large team friendly (#4106) * Refactor how the client handles channel members to be large team friendly * Change Id to ChannelId in ChannelStats model * Updated getChannelMember and getProfilesByIds routes to match proposal * Performance improvements (#4100) * Performance improvements * Fixing re-connect issue * Fixing error message * Some other minor perf tweaks * Some other minor perf tweaks * Fixing config file * Fixing buffer size * Fixing web socket send message * adding some error logging * fix getMe to be user required * Fix websocket event for new user * Fixing shutting down * Reverting web socket changes * Fixing logging lvl * Adding caching to GetMember * Adding some logging * Fixing caching * Fixing caching invalidate * Fixing direct message caching * Fixing caching * Fixing caching * Remove GetDirectProfiles from initial load * Adding logging and fixing websocket client * Adding back caching from bad merge. * Explicitly close go driver requests (#4162) * Refactored how the client handles team members to be more large team friendly (#4159) * Refactor getProfilesForDirectMessageList API into getAllProfiles API * Refactored how the client handles team members to be more large team friendly * Fix js error when receiving a notification * Fix JS error caused by current user being overwritten with sanitized version (#4165) * Adding error message to status failure (#4167) * Fix a few bugs caused by client scaling refactoring (#4170) * When there is no read replica, don't open a second set of connections to the master database (#4173) * Adding connection tacking to stats (#4174) * Reduce DB writes for statuses and other status related changes (#4175) * Fix bug preventing opening of DM channels from more modal (#4181) * Fixing socket timing error (#4183) * Fixing ping/pong handler * Fixing socket timing error * Commenting out status broadcasting * Removing user status changes * Removing user status changes * Removing user status changes * Removing user status changes * Adding DoPreComputeJson() * Performance improvements (#4194) * * Fix System Console Analytics queries * Add db.SetConnMaxLifetime to 15 minutes * Add "net/http/pprof" for profiling * Add FreeOSMemory() to manually release memory on reload config * Add flag to enable http profiler * Fix memory leak (#4197) * Fix memory leak * removed unneeded nil assignment * Fixing go routine leak (#4208) * Merge fixes * Merge fix * Refactored statuses to be queried by the client rather than broadcast by the server (#4212) * Refactored server code to reduce status broadcasts and to allow getting statuses by IDs * Refactor client code to periodically fetch statuses * Add store unit test for getting statuses by ids * Fix status unit test * Add getStatusesByIds REST API and move the client over to use that instead of the WebSocket * Adding multiple threads to websocket hub (#4230) * Adding multiple threads to websocket hub * Fixing unit tests * Fixing so websocket connections from the same user end up in the same… (#4240) * Fixing so websocket connections from the same user end up in the same list * Removing old comment * Refactor user autocomplete to query the server (#4239) * Add API for autocompleting users * Converted at mention autocomplete to query server * Converted user search autocomplete to query server * Switch autocomplete API naming to use term instead of username * Split autocomplete API into two, one for channels and for teams * Fix copy/paste error * Some final client scaling fixes (#4246) * Add lazy loading of profiles to integration pages * Add lazy loading of profiles to emoji page * Fix JS error when receiving post in select team menu and also clean up channel store --- store/sql_channel_store.go | 126 +++++++----- store/sql_channel_store_test.go | 65 +++--- store/sql_post_store.go | 70 +++---- store/sql_status_store.go | 41 +++- store/sql_status_store_test.go | 9 + store/sql_store.go | 26 ++- store/sql_team_store.go | 69 ++++++- store/sql_team_store_test.go | 79 +++++++- store/sql_user_store.go | 359 +++++++++++++++++++++++++++++---- store/sql_user_store_test.go | 428 +++++++++++++++++++++++++++++++++++++++- store/store.go | 26 ++- 11 files changed, 1106 insertions(+), 192 deletions(-) (limited to 'store') diff --git a/store/sql_channel_store.go b/store/sql_channel_store.go index eb150a63c..a860fea73 100644 --- a/store/sql_channel_store.go +++ b/store/sql_channel_store.go @@ -6,21 +6,26 @@ package store import ( "database/sql" + l4g "github.com/alecthomas/log4go" "github.com/go-gorp/gorp" "github.com/mattermost/platform/model" "github.com/mattermost/platform/utils" ) const ( - MISSING_CHANNEL_ERROR = "store.sql_channel.get_by_name.missing.app_error" - MISSING_CHANNEL_MEMBER_ERROR = "store.sql_channel.get_member.missing.app_error" - CHANNEL_EXISTS_ERROR = "store.sql_channel.save_channel.exists.app_error" + MISSING_CHANNEL_ERROR = "store.sql_channel.get_by_name.missing.app_error" + MISSING_CHANNEL_MEMBER_ERROR = "store.sql_channel.get_member.missing.app_error" + CHANNEL_EXISTS_ERROR = "store.sql_channel.save_channel.exists.app_error" + ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SIZE = model.SESSION_CACHE_SIZE + ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SEC = 900 // 15 mins ) type SqlChannelStore struct { *SqlStore } +var allChannelMembersForUserCache *utils.Cache = utils.NewLru(ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SIZE) + func NewSqlChannelStore(sqlStore *SqlStore) ChannelStore { s := &SqlChannelStore{sqlStore} @@ -517,6 +522,8 @@ func (s SqlChannelStore) SaveMember(member *model.ChannelMember) StoreChannel { } } + s.InvalidateAllChannelMembersForUser(member.UserId) + storeChannel <- result close(storeChannel) }() @@ -619,6 +626,33 @@ func (s SqlChannelStore) GetMember(channelId string, userId string) StoreChannel return storeChannel } +func (us SqlChannelStore) InvalidateAllChannelMembersForUser(userId string) { + allChannelMembersForUserCache.Remove(userId) +} + +func (us SqlChannelStore) IsUserInChannelUseCache(userId string, channelId string) bool { + if cacheItem, ok := allChannelMembersForUserCache.Get(userId); ok { + ids := cacheItem.(map[string]string) + if _, ok := ids[channelId]; ok { + return true + } else { + return false + } + } + + if result := <-us.GetAllChannelMembersForUser(userId, true); result.Err != nil { + l4g.Error("SqlChannelStore.IsUserInChannelUseCache: " + result.Err.Error()) + return false + } else { + ids := result.Data.(map[string]string) + if _, ok := ids[channelId]; ok { + return true + } else { + return false + } + } +} + func (s SqlChannelStore) GetMemberForPost(postId string, userId string) StoreChannel { storeChannel := make(StoreChannel, 1) @@ -649,26 +683,43 @@ func (s SqlChannelStore) GetMemberForPost(postId string, userId string) StoreCha return storeChannel } -func (s SqlChannelStore) GetMemberCount(channelId string) StoreChannel { +type allChannelMember struct { + ChannelId string + Roles string +} + +func (s SqlChannelStore) GetAllChannelMembersForUser(userId string, allowFromCache bool) StoreChannel { storeChannel := make(StoreChannel, 1) go func() { result := StoreResult{} - count, err := s.GetReplica().SelectInt(` - SELECT - count(*) - FROM - ChannelMembers, - Users - WHERE - ChannelMembers.UserId = Users.Id - AND ChannelMembers.ChannelId = :ChannelId - AND Users.DeleteAt = 0`, map[string]interface{}{"ChannelId": channelId}) + if allowFromCache { + if cacheItem, ok := allChannelMembersForUserCache.Get(userId); ok { + result.Data = cacheItem.(map[string]string) + storeChannel <- result + close(storeChannel) + return + } + } + + var data []allChannelMember + _, err := s.GetReplica().Select(&data, "SELECT ChannelId, Roles FROM Channels, ChannelMembers WHERE Channels.Id = ChannelMembers.ChannelId AND ChannelMembers.UserId = :UserId AND Channels.DeleteAt = 0", map[string]interface{}{"UserId": userId}) + if err != nil { - result.Err = model.NewLocAppError("SqlChannelStore.GetMemberCount", "store.sql_channel.get_member_count.app_error", nil, "channel_id="+channelId+", "+err.Error()) + result.Err = model.NewLocAppError("SqlChannelStore.GetAllChannelMembersForUser", "store.sql_channel.get_channels.get.app_error", nil, "userId="+userId+", err="+err.Error()) } else { - result.Data = count + + ids := make(map[string]string) + for i := range data { + ids[data[i].ChannelId] = data[i].Roles + } + + result.Data = ids + + if allowFromCache { + allChannelMembersForUserCache.AddWithExpiresInSecs(userId, ids, ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SEC) + } } storeChannel <- result @@ -678,55 +729,26 @@ func (s SqlChannelStore) GetMemberCount(channelId string) StoreChannel { return storeChannel } -func (s SqlChannelStore) GetExtraMembers(channelId string, limit int) StoreChannel { +func (s SqlChannelStore) GetMemberCount(channelId string) StoreChannel { storeChannel := make(StoreChannel, 1) go func() { result := StoreResult{} - var members []model.ExtraMember - var err error - - if limit != -1 { - _, err = s.GetReplica().Select(&members, ` - SELECT - Id, - Nickname, - Email, - ChannelMembers.Roles, - Username - FROM - ChannelMembers, - Users - WHERE - ChannelMembers.UserId = Users.Id - AND Users.DeleteAt = 0 - AND ChannelId = :ChannelId - LIMIT :Limit`, map[string]interface{}{"ChannelId": channelId, "Limit": limit}) - } else { - _, err = s.GetReplica().Select(&members, ` + count, err := s.GetReplica().SelectInt(` SELECT - Id, - Nickname, - Email, - ChannelMembers.Roles, - Username + count(*) FROM ChannelMembers, Users WHERE ChannelMembers.UserId = Users.Id - AND Users.DeleteAt = 0 - AND ChannelId = :ChannelId`, map[string]interface{}{"ChannelId": channelId}) - } - + AND ChannelMembers.ChannelId = :ChannelId + AND Users.DeleteAt = 0`, map[string]interface{}{"ChannelId": channelId}) if err != nil { - result.Err = model.NewLocAppError("SqlChannelStore.GetExtraMembers", "store.sql_channel.get_extra_members.app_error", nil, "channel_id="+channelId+", "+err.Error()) + result.Err = model.NewLocAppError("SqlChannelStore.GetMemberCount", "store.sql_channel.get_member_count.app_error", nil, "channel_id="+channelId+", "+err.Error()) } else { - for i := range members { - members[i].Sanitize(utils.Cfg.GetSanitizeOptions()) - } - result.Data = members + result.Data = count } storeChannel <- result diff --git a/store/sql_channel_store_test.go b/store/sql_channel_store_test.go index 19db3d003..d80d54d52 100644 --- a/store/sql_channel_store_test.go +++ b/store/sql_channel_store_test.go @@ -408,11 +408,6 @@ func TestChannelMemberStore(t *testing.T) { t.Fatal("should have go member") } - extraMembers := (<-store.Channel().GetExtraMembers(o1.ChannelId, 20)).Data.([]model.ExtraMember) - if len(extraMembers) != 1 { - t.Fatal("should have 1 extra members") - } - if err := (<-store.Channel().SaveMember(&o1)).Err; err == nil { t.Fatal("Should have been a duplicate") } @@ -422,18 +417,6 @@ func TestChannelMemberStore(t *testing.T) { if t4 != t3 { t.Fatal("Should not update time upon failure") } - - // rejoin the channel and make sure that an inactive user isn't returned by GetExtraMambers - Must(store.Channel().SaveMember(&o2)) - - u2.DeleteAt = 1000 - Must(store.User().Update(&u2, true)) - - if result := <-store.Channel().GetExtraMembers(o1.ChannelId, 20); result.Err != nil { - t.Fatal(result.Err) - } else if extraMembers := result.Data.([]model.ExtraMember); len(extraMembers) != 1 { - t.Fatal("should have 1 extra members") - } } func TestChannelDeleteMemberStore(t *testing.T) { @@ -534,6 +517,42 @@ func TestChannelStoreGetChannels(t *testing.T) { if list.Channels[0].Id != o1.Id { t.Fatal("missing channel") } + + acresult := <-store.Channel().GetAllChannelMembersForUser(m1.UserId, false) + ids := acresult.Data.(map[string]string) + if _, ok := ids[o1.Id]; !ok { + t.Fatal("missing channel") + } + + acresult2 := <-store.Channel().GetAllChannelMembersForUser(m1.UserId, true) + ids2 := acresult2.Data.(map[string]string) + if _, ok := ids2[o1.Id]; !ok { + t.Fatal("missing channel") + } + + acresult3 := <-store.Channel().GetAllChannelMembersForUser(m1.UserId, true) + ids3 := acresult3.Data.(map[string]string) + if _, ok := ids3[o1.Id]; !ok { + t.Fatal("missing channel") + } + + if !store.Channel().IsUserInChannelUseCache(m1.UserId, o1.Id) { + t.Fatal("missing channel") + } + + if store.Channel().IsUserInChannelUseCache(m1.UserId, o2.Id) { + t.Fatal("missing channel") + } + + if store.Channel().IsUserInChannelUseCache(m1.UserId, "blahblah") { + t.Fatal("missing channel") + } + + if store.Channel().IsUserInChannelUseCache("blahblah", "blahblah") { + t.Fatal("missing channel") + } + + store.Channel().InvalidateAllChannelMembersForUser(m1.UserId) } func TestChannelStoreGetMoreChannels(t *testing.T) { @@ -974,22 +993,10 @@ func TestUpdateExtrasByUser(t *testing.T) { t.Fatal("failed to update extras by user: %v", result.Err) } - if result := <-store.Channel().GetExtraMembers(c1.Id, -1); result.Err != nil { - t.Fatal("failed to get extras: %v", result.Err) - } else if len(result.Data.([]model.ExtraMember)) != 0 { - t.Fatal("got incorrect member count %v", len(result.Data.([]model.ExtraMember))) - } - u1.DeleteAt = 0 Must(store.User().Update(u1, true)) if result := <-store.Channel().ExtraUpdateByUser(u1.Id, u1.DeleteAt); result.Err != nil { t.Fatal("failed to update extras by user: %v", result.Err) } - - if result := <-store.Channel().GetExtraMembers(c1.Id, -1); result.Err != nil { - t.Fatal("failed to get extras: %v", result.Err) - } else if len(result.Data.([]model.ExtraMember)) != 1 { - t.Fatal("got incorrect member count %v", len(result.Data.([]model.ExtraMember))) - } } diff --git a/store/sql_post_store.go b/store/sql_post_store.go index ec8679b31..44ffb556e 100644 --- a/store/sql_post_store.go +++ b/store/sql_post_store.go @@ -43,6 +43,7 @@ func (s SqlPostStore) CreateIndexesIfNotExists() { s.CreateIndexIfNotExists("idx_posts_create_at", "Posts", "CreateAt") s.CreateIndexIfNotExists("idx_posts_channel_id", "Posts", "ChannelId") s.CreateIndexIfNotExists("idx_posts_root_id", "Posts", "RootId") + s.CreateIndexIfNotExists("idx_posts_user_id", "Posts", "UserId") s.CreateFullTextIndexIfNotExists("idx_posts_message_txt", "Posts", "Message") s.CreateFullTextIndexIfNotExists("idx_posts_hashtags_txt", "Posts", "Hashtags") @@ -811,47 +812,36 @@ func (s SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) StoreChan result := StoreResult{} query := - `SELECT - t1.Name, COUNT(t1.UserId) AS Value - FROM - (SELECT DISTINCT + `SELECT DISTINCT DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, - Posts.UserId - FROM - Posts, Channels - WHERE - Posts.ChannelId = Channels.Id` + COUNT(DISTINCT Posts.UserId) AS Value + FROM Posts + INNER JOIN Channels + ON Posts.ChannelId = Channels.Id` if len(teamId) > 0 { query += " AND Channels.TeamId = :TeamId" } query += ` AND Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime - ORDER BY Name DESC) AS t1 - GROUP BY Name + GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) ORDER BY Name DESC LIMIT 30` if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { query = `SELECT - TO_CHAR(t1.Name, 'YYYY-MM-DD') AS Name, COUNT(t1.UserId) AS Value - FROM - (SELECT DISTINCT - DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) AS Name, - Posts.UserId - FROM - Posts, Channels - WHERE - Posts.ChannelId = Channels.Id` + TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, COUNT(DISTINCT Posts.UserId) AS Value + FROM Posts + INNER JOIN Channels + ON Posts.ChannelId = Channels.Id` if len(teamId) > 0 { query += " AND Channels.TeamId = :TeamId" } query += ` AND Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime - ORDER BY Name DESC) AS t1 - GROUP BY Name + GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) ORDER BY Name DESC LIMIT 30` } @@ -884,15 +874,12 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { result := StoreResult{} query := - `SELECT - Name, COUNT(Value) AS Value - FROM - (SELECT + `SELECT DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name, - '1' AS Value - FROM - Posts, Channels - WHERE + COUNT(Posts.Id) AS Value + FROM Posts + INNER JOIN Channels + ON Posts.ChannelId = Channels.Id` if len(teamId) > 0 { @@ -900,31 +887,26 @@ func (s SqlPostStore) AnalyticsPostCountsByDay(teamId string) StoreChannel { } query += ` AND Posts.CreateAt <= :EndTime - AND Posts.CreateAt >= :StartTime) AS t1 - GROUP BY Name + AND Posts.CreateAt >= :StartTime + GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) ORDER BY Name DESC LIMIT 30` if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { query = - `SELECT - Name, COUNT(Value) AS Value - FROM - (SELECT - TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, - '1' AS Value - FROM - Posts, Channels - WHERE - Posts.ChannelId = Channels.Id` + `SELECT + TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, Count(Posts.Id) AS Value + FROM Posts + INNER JOIN Channels + ON Posts.ChannelId = Channels.Id` if len(teamId) > 0 { query += " AND Channels.TeamId = :TeamId" } query += ` AND Posts.CreateAt <= :EndTime - AND Posts.CreateAt >= :StartTime) AS t1 - GROUP BY Name + AND Posts.CreateAt >= :StartTime + GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)) ORDER BY Name DESC LIMIT 30` } diff --git a/store/sql_status_store.go b/store/sql_status_store.go index 4d186a30e..7b9fdea5d 100644 --- a/store/sql_status_store.go +++ b/store/sql_status_store.go @@ -5,6 +5,7 @@ package store import ( "database/sql" + "strconv" "github.com/mattermost/platform/model" ) @@ -43,11 +44,11 @@ func (s SqlStatusStore) SaveOrUpdate(status *model.Status) StoreChannel { if err := s.GetReplica().SelectOne(&model.Status{}, "SELECT * FROM Status WHERE UserId = :UserId", map[string]interface{}{"UserId": status.UserId}); err == nil { if _, err := s.GetMaster().Update(status); err != nil { - result.Err = model.NewLocAppError("SqlStatusStore.SaveOrUpdate", "store.sql_status.update.app_error", nil, "") + result.Err = model.NewLocAppError("SqlStatusStore.SaveOrUpdate", "store.sql_status.update.app_error", nil, err.Error()) } } else { if err := s.GetMaster().Insert(status); err != nil { - result.Err = model.NewLocAppError("SqlStatusStore.SaveOrUpdate", "store.sql_status.save.app_error", nil, "") + result.Err = model.NewLocAppError("SqlStatusStore.SaveOrUpdate", "store.sql_status.save.app_error", nil, err.Error()) } } @@ -89,6 +90,38 @@ func (s SqlStatusStore) Get(userId string) StoreChannel { return storeChannel } +func (s SqlStatusStore) GetByIds(userIds []string) StoreChannel { + storeChannel := make(StoreChannel, 1) + + go func() { + result := StoreResult{} + + props := make(map[string]interface{}) + idQuery := "" + + for index, userId := range userIds { + if len(idQuery) > 0 { + idQuery += ", " + } + + props["userId"+strconv.Itoa(index)] = userId + idQuery += ":userId" + strconv.Itoa(index) + } + + var statuses []*model.Status + if _, err := s.GetReplica().Select(&statuses, "SELECT * FROM Status WHERE UserId IN ("+idQuery+")", props); err != nil { + result.Err = model.NewLocAppError("SqlStatusStore.GetByIds", "store.sql_status.get.app_error", nil, err.Error()) + } else { + result.Data = statuses + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} + func (s SqlStatusStore) GetOnlineAway() StoreChannel { storeChannel := make(StoreChannel, 1) @@ -96,7 +129,7 @@ func (s SqlStatusStore) GetOnlineAway() StoreChannel { result := StoreResult{} var statuses []*model.Status - if _, err := s.GetReplica().Select(&statuses, "SELECT * FROM Status WHERE Status = :Online OR Status = :Away", map[string]interface{}{"Online": model.STATUS_ONLINE, "Away": model.STATUS_AWAY}); err != nil { + if _, err := s.GetReplica().Select(&statuses, "SELECT * FROM Status WHERE Status = :Online OR Status = :Away LIMIT 300", map[string]interface{}{"Online": model.STATUS_ONLINE, "Away": model.STATUS_AWAY}); err != nil { result.Err = model.NewLocAppError("SqlStatusStore.GetOnlineAway", "store.sql_status.get_online_away.app_error", nil, err.Error()) } else { result.Data = statuses @@ -157,7 +190,7 @@ func (s SqlStatusStore) ResetAll() StoreChannel { go func() { result := StoreResult{} - if _, err := s.GetMaster().Exec("UPDATE Status SET Status = :Status", map[string]interface{}{"Status": model.STATUS_OFFLINE}); err != nil { + if _, err := s.GetMaster().Exec("UPDATE Status SET Status = :Status WHERE Manual = 0", map[string]interface{}{"Status": model.STATUS_OFFLINE}); err != nil { result.Err = model.NewLocAppError("SqlStatusStore.ResetAll", "store.sql_status.reset_all.app_error", nil, "") } diff --git a/store/sql_status_store_test.go b/store/sql_status_store_test.go index dff4db55e..dce973850 100644 --- a/store/sql_status_store_test.go +++ b/store/sql_status_store_test.go @@ -60,6 +60,15 @@ func TestSqlStatusStore(t *testing.T) { } } + if result := <-store.Status().GetByIds([]string{status.UserId, "junk"}); result.Err != nil { + t.Fatal(result.Err) + } else { + statuses := result.Data.([]*model.Status) + if len(statuses) != 1 { + t.Fatal("should only have 1 status") + } + } + if err := (<-store.Status().ResetAll()).Err; err != nil { t.Fatal(err) } diff --git a/store/sql_store.go b/store/sql_store.go index a2bc8f1b8..1c0de5932 100644 --- a/store/sql_store.go +++ b/store/sql_store.go @@ -33,6 +33,7 @@ import ( const ( INDEX_TYPE_FULL_TEXT = "full_text" INDEX_TYPE_DEFAULT = "default" + MAX_DB_CONN_LIFETIME = 15 ) const ( @@ -94,9 +95,7 @@ func initConnection() *SqlStore { if len(utils.Cfg.SqlSettings.DataSourceReplicas) == 0 { sqlStore.replicas = make([]*gorp.DbMap, 1) - sqlStore.replicas[0] = setupConnection(fmt.Sprintf("replica-%v", 0), utils.Cfg.SqlSettings.DriverName, utils.Cfg.SqlSettings.DataSource, - utils.Cfg.SqlSettings.MaxIdleConns, utils.Cfg.SqlSettings.MaxOpenConns, - utils.Cfg.SqlSettings.Trace) + sqlStore.replicas[0] = sqlStore.master } else { sqlStore.replicas = make([]*gorp.DbMap, len(utils.Cfg.SqlSettings.DataSourceReplicas)) for i, replica := range utils.Cfg.SqlSettings.DataSourceReplicas { @@ -183,6 +182,7 @@ func setupConnection(con_type string, driver string, dataSource string, maxIdle db.SetMaxIdleConns(maxIdle) db.SetMaxOpenConns(maxOpen) + db.SetConnMaxLifetime(time.Duration(MAX_DB_CONN_LIFETIME) * time.Minute) var dbmap *gorp.DbMap @@ -205,6 +205,26 @@ func setupConnection(con_type string, driver string, dataSource string, maxIdle return dbmap } +func (ss SqlStore) TotalMasterDbConnections() int { + return ss.GetMaster().Db.Stats().OpenConnections +} + +func (ss SqlStore) TotalReadDbConnections() int { + + if len(utils.Cfg.SqlSettings.DataSourceReplicas) == 0 { + return 0 + } else { + count := 0 + for _, db := range ss.replicas { + count = count + db.Db.Stats().OpenConnections + } + + return count + } + + return 0 +} + func (ss SqlStore) GetCurrentSchemaVersion() string { version, _ := ss.GetMaster().SelectStr("SELECT Value FROM Systems WHERE Name='Version'") return version diff --git a/store/sql_team_store.go b/store/sql_team_store.go index 34a4a097d..a69c84904 100644 --- a/store/sql_team_store.go +++ b/store/sql_team_store.go @@ -5,6 +5,7 @@ package store import ( "database/sql" + "strconv" "github.com/mattermost/platform/model" "github.com/mattermost/platform/utils" @@ -441,14 +442,14 @@ func (s SqlTeamStore) GetMember(teamId string, userId string) StoreChannel { return storeChannel } -func (s SqlTeamStore) GetMembers(teamId string) StoreChannel { +func (s SqlTeamStore) GetMembers(teamId string, offset int, limit int) StoreChannel { storeChannel := make(StoreChannel, 1) go func() { result := StoreResult{} var members []*model.TeamMember - _, err := s.GetReplica().Select(&members, "SELECT * FROM TeamMembers WHERE TeamId = :TeamId", map[string]interface{}{"TeamId": teamId}) + _, err := s.GetReplica().Select(&members, "SELECT * FROM TeamMembers WHERE TeamId = :TeamId AND DeleteAt = 0 LIMIT :Limit OFFSET :Offset", map[string]interface{}{"TeamId": teamId, "Offset": offset, "Limit": limit}) if err != nil { result.Err = model.NewLocAppError("SqlTeamStore.GetMembers", "store.sql_team.get_members.app_error", nil, "teamId="+teamId+" "+err.Error()) } else { @@ -462,6 +463,70 @@ func (s SqlTeamStore) GetMembers(teamId string) StoreChannel { return storeChannel } +func (s SqlTeamStore) GetMemberCount(teamId string) StoreChannel { + storeChannel := make(StoreChannel, 1) + + go func() { + result := StoreResult{} + + count, err := s.GetReplica().SelectInt(` + SELECT + count(*) + FROM + TeamMembers, + Users + WHERE + TeamMembers.UserId = Users.Id + AND TeamMembers.TeamId = :TeamId + AND TeamMembers.DeleteAt = 0 + AND Users.DeleteAt = 0`, map[string]interface{}{"TeamId": teamId}) + if err != nil { + result.Err = model.NewLocAppError("SqlTeamStore.GetMemberCount", "store.sql_team.get_member_count.app_error", nil, "teamId="+teamId+" "+err.Error()) + } else { + result.Data = count + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} + +func (s SqlTeamStore) GetMembersByIds(teamId string, userIds []string) StoreChannel { + storeChannel := make(StoreChannel, 1) + + go func() { + result := StoreResult{} + + var members []*model.TeamMember + props := make(map[string]interface{}) + idQuery := "" + + for index, userId := range userIds { + if len(idQuery) > 0 { + idQuery += ", " + } + + props["userId"+strconv.Itoa(index)] = userId + idQuery += ":userId" + strconv.Itoa(index) + } + + props["TeamId"] = teamId + + if _, err := s.GetReplica().Select(&members, "SELECT * FROM TeamMembers WHERE TeamId = :TeamId AND UserId IN ("+idQuery+") AND DeleteAt = 0", props); err != nil { + result.Err = model.NewLocAppError("SqlTeamStore.GetMembersByIds", "store.sql_team.get_members_by_ids.app_error", nil, "teamId="+teamId+" "+err.Error()) + } else { + result.Data = members + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} + func (s SqlTeamStore) GetTeamsForUser(userId string) StoreChannel { storeChannel := make(StoreChannel, 1) diff --git a/store/sql_team_store_test.go b/store/sql_team_store_test.go index be72786d3..46215d9be 100644 --- a/store/sql_team_store_test.go +++ b/store/sql_team_store_test.go @@ -298,7 +298,7 @@ func TestTeamMembers(t *testing.T) { Must(store.Team().SaveMember(m2)) Must(store.Team().SaveMember(m3)) - if r1 := <-store.Team().GetMembers(teamId1); r1.Err != nil { + if r1 := <-store.Team().GetMembers(teamId1, 0, 100); r1.Err != nil { t.Fatal(r1.Err) } else { ms := r1.Data.([]*model.TeamMember) @@ -308,7 +308,7 @@ func TestTeamMembers(t *testing.T) { } } - if r1 := <-store.Team().GetMembers(teamId2); r1.Err != nil { + if r1 := <-store.Team().GetMembers(teamId2, 0, 100); r1.Err != nil { t.Fatal(r1.Err) } else { ms := r1.Data.([]*model.TeamMember) @@ -342,7 +342,7 @@ func TestTeamMembers(t *testing.T) { t.Fatal(r1.Err) } - if r1 := <-store.Team().GetMembers(teamId1); r1.Err != nil { + if r1 := <-store.Team().GetMembers(teamId1, 0, 100); r1.Err != nil { t.Fatal(r1.Err) } else { ms := r1.Data.([]*model.TeamMember) @@ -363,7 +363,7 @@ func TestTeamMembers(t *testing.T) { t.Fatal(r1.Err) } - if r1 := <-store.Team().GetMembers(teamId1); r1.Err != nil { + if r1 := <-store.Team().GetMembers(teamId1, 0, 100); r1.Err != nil { t.Fatal(r1.Err) } else { ms := r1.Data.([]*model.TeamMember) @@ -434,3 +434,74 @@ func TestGetTeamMember(t *testing.T) { t.Fatal("empty team id - should have failed") } } + +func TestGetTeamMembersByIds(t *testing.T) { + Setup() + + teamId1 := model.NewId() + + m1 := &model.TeamMember{TeamId: teamId1, UserId: model.NewId()} + Must(store.Team().SaveMember(m1)) + + if r := <-store.Team().GetMembersByIds(m1.TeamId, []string{m1.UserId}); r.Err != nil { + t.Fatal(r.Err) + } else { + rm1 := r.Data.([]*model.TeamMember)[0] + + if rm1.TeamId != m1.TeamId { + t.Fatal("bad team id") + } + + if rm1.UserId != m1.UserId { + t.Fatal("bad user id") + } + } + + m2 := &model.TeamMember{TeamId: teamId1, UserId: model.NewId()} + Must(store.Team().SaveMember(m2)) + + if r := <-store.Team().GetMembersByIds(m1.TeamId, []string{m1.UserId, m2.UserId, model.NewId()}); r.Err != nil { + t.Fatal(r.Err) + } else { + rm := r.Data.([]*model.TeamMember) + + if len(rm) != 2 { + t.Fatal("return wrong number of results") + } + } + + if r := <-store.Team().GetMembersByIds(m1.TeamId, []string{}); r.Err == nil { + t.Fatal("empty user ids - should have failed") + } +} + +func TestTeamStoreMemberCount(t *testing.T) { + Setup() + + u1 := &model.User{} + u1.Email = model.NewId() + Must(store.User().Save(u1)) + + teamId1 := model.NewId() + m1 := &model.TeamMember{TeamId: teamId1, UserId: u1.Id} + Must(store.Team().SaveMember(m1)) + + if result := <-store.Team().GetMemberCount(teamId1); result.Err != nil { + t.Fatal(result.Err) + } else { + if result.Data.(int64) != 1 { + t.Fatal("wrong count") + } + } + + m2 := &model.TeamMember{TeamId: teamId1, UserId: model.NewId()} + Must(store.Team().SaveMember(m2)) + + if result := <-store.Team().GetMemberCount(teamId1); result.Err != nil { + t.Fatal(result.Err) + } else { + if result.Data.(int64) != 1 { + t.Fatal("wrong count") + } + } +} diff --git a/store/sql_user_store.go b/store/sql_user_store.go index 8ada9eb2c..ca86ef115 100644 --- a/store/sql_user_store.go +++ b/store/sql_user_store.go @@ -15,14 +15,20 @@ import ( ) const ( - MISSING_ACCOUNT_ERROR = "store.sql_user.missing_account.const" - MISSING_AUTH_ACCOUNT_ERROR = "store.sql_user.get_by_auth.missing_account.app_error" + MISSING_ACCOUNT_ERROR = "store.sql_user.missing_account.const" + MISSING_AUTH_ACCOUNT_ERROR = "store.sql_user.get_by_auth.missing_account.app_error" + PROFILES_IN_CHANNEL_CACHE_SIZE = 5000 + PROFILES_IN_CHANNEL_CACHE_SEC = 900 // 15 mins + USER_SEARCH_TYPE_ALL = "Username, FirstName, LastName, Nickname" + USER_SEARCH_TYPE_USERNAME = "Username" ) type SqlUserStore struct { *SqlStore } +var profilesInChannelCache *utils.Cache = utils.NewLru(PROFILES_IN_CHANNEL_CACHE_SIZE) + func NewSqlUserStore(sqlStore *SqlStore) UserStore { us := &SqlUserStore{sqlStore} @@ -49,6 +55,9 @@ func NewSqlUserStore(sqlStore *SqlStore) UserStore { func (us SqlUserStore) CreateIndexesIfNotExists() { us.CreateIndexIfNotExists("idx_users_email", "Users", "Email") + + us.CreateFullTextIndexIfNotExists("idx_users_username_txt", "Users", USER_SEARCH_TYPE_USERNAME) + us.CreateFullTextIndexIfNotExists("idx_users_all_names_txt", "Users", USER_SEARCH_TYPE_ALL) } func (us SqlUserStore) Save(user *model.User) StoreChannel { @@ -457,7 +466,7 @@ func (s SqlUserStore) GetEtagForAllProfiles() StoreChannel { return storeChannel } -func (us SqlUserStore) GetAllProfiles() StoreChannel { +func (us SqlUserStore) GetAllProfiles(offset int, limit int) StoreChannel { storeChannel := make(StoreChannel, 1) @@ -466,8 +475,8 @@ func (us SqlUserStore) GetAllProfiles() StoreChannel { var users []*model.User - if _, err := us.GetReplica().Select(&users, "SELECT * FROM Users"); err != nil { - result.Err = model.NewLocAppError("SqlUserStore.GetProfiles", "store.sql_user.get_profiles.app_error", nil, err.Error()) + if _, err := us.GetReplica().Select(&users, "SELECT * FROM Users ORDER BY Username ASC LIMIT :Limit OFFSET :Offset", map[string]interface{}{"Offset": offset, "Limit": limit}); err != nil { + result.Err = model.NewLocAppError("SqlUserStore.GetAllProfiles", "store.sql_user.get_profiles.app_error", nil, err.Error()) } else { userMap := make(map[string]*model.User) @@ -509,7 +518,7 @@ func (s SqlUserStore) GetEtagForProfiles(teamId string) StoreChannel { return storeChannel } -func (us SqlUserStore) GetProfiles(teamId string) StoreChannel { +func (us SqlUserStore) GetProfiles(teamId string, offset int, limit int) StoreChannel { storeChannel := make(StoreChannel, 1) @@ -518,7 +527,7 @@ func (us SqlUserStore) GetProfiles(teamId string) StoreChannel { var users []*model.User - if _, err := us.GetReplica().Select(&users, "SELECT Users.* FROM Users, TeamMembers WHERE TeamMembers.TeamId = :TeamId AND Users.Id = TeamMembers.UserId", map[string]interface{}{"TeamId": teamId}); err != nil { + if _, err := us.GetReplica().Select(&users, "SELECT Users.* FROM Users, TeamMembers WHERE TeamMembers.TeamId = :TeamId AND Users.Id = TeamMembers.UserId AND TeamMembers.DeleteAt = 0 ORDER BY Users.Username ASC LIMIT :Limit OFFSET :Offset", map[string]interface{}{"TeamId": teamId, "Offset": offset, "Limit": limit}); err != nil { result.Err = model.NewLocAppError("SqlUserStore.GetProfiles", "store.sql_user.get_profiles.app_error", nil, err.Error()) } else { @@ -541,9 +550,64 @@ func (us SqlUserStore) GetProfiles(teamId string) StoreChannel { return storeChannel } -func (us SqlUserStore) GetDirectProfiles(userId string) StoreChannel { +func (us SqlUserStore) InvalidateProfilesInChannelCache(channelId string) { + profilesInChannelCache.Remove(channelId) +} - storeChannel := make(StoreChannel, 1) +func (us SqlUserStore) GetProfilesInChannel(channelId string, offset int, limit int, allowFromCache bool) StoreChannel { + + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + if allowFromCache && offset == -1 && limit == -1 { + if cacheItem, ok := profilesInChannelCache.Get(channelId); ok { + result.Data = cacheItem.(map[string]*model.User) + storeChannel <- result + close(storeChannel) + return + } + } + + var users []*model.User + + query := "SELECT Users.* FROM Users, ChannelMembers WHERE ChannelMembers.ChannelId = :ChannelId AND Users.Id = ChannelMembers.UserId AND Users.DeleteAt = 0" + + if limit >= 0 && offset >= 0 { + query += " ORDER BY Users.Username ASC LIMIT :Limit OFFSET :Offset" + } + + if _, err := us.GetReplica().Select(&users, query, map[string]interface{}{"ChannelId": channelId, "Offset": offset, "Limit": limit}); err != nil { + result.Err = model.NewLocAppError("SqlUserStore.GetProfilesInChannel", "store.sql_user.get_profiles.app_error", nil, err.Error()) + } else { + + userMap := make(map[string]*model.User) + + for _, u := range users { + u.Password = "" + u.AuthData = new(string) + *u.AuthData = "" + userMap[u.Id] = u + } + + result.Data = userMap + + if allowFromCache && offset == -1 && limit == -1 { + profilesInChannelCache.AddWithExpiresInSecs(channelId, userMap, PROFILES_IN_CHANNEL_CACHE_SEC) + } + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} + +func (us SqlUserStore) GetProfilesNotInChannel(teamId string, channelId string, offset int, limit int) StoreChannel { + + storeChannel := make(StoreChannel) go func() { result := StoreResult{} @@ -551,35 +615,20 @@ func (us SqlUserStore) GetDirectProfiles(userId string) StoreChannel { var users []*model.User if _, err := us.GetReplica().Select(&users, ` - SELECT - Users.* - FROM - Users - WHERE - Id IN (SELECT DISTINCT - UserId - FROM - ChannelMembers - WHERE - ChannelMembers.UserId != :UserId - AND ChannelMembers.ChannelId IN (SELECT - Channels.Id - FROM - Channels, - ChannelMembers - WHERE - Channels.Type = 'D' - AND Channels.Id = ChannelMembers.ChannelId - AND ChannelMembers.UserId = :UserId)) - OR Id IN (SELECT - Name - FROM - Preferences - WHERE - UserId = :UserId - AND Category = 'direct_channel_show') - `, map[string]interface{}{"UserId": userId}); err != nil { - result.Err = model.NewLocAppError("SqlUserStore.GetDirectProfiles", "store.sql_user.get_profiles.app_error", nil, err.Error()) + SELECT + u.* + FROM Users u + INNER JOIN TeamMembers tm + ON tm.UserId = u.Id + AND tm.TeamId = :TeamId + LEFT JOIN ChannelMembers cm + ON cm.UserId = u.Id + AND cm.ChannelId = :ChannelId + WHERE cm.UserId IS NULL + ORDER BY u.Username ASC + LIMIT :Limit OFFSET :Offset + `, map[string]interface{}{"TeamId": teamId, "ChannelId": channelId, "Offset": offset, "Limit": limit}); err != nil { + result.Err = model.NewLocAppError("SqlUserStore.GetProfilesNotInChannel", "store.sql_user.get_profiles.app_error", nil, err.Error()) } else { userMap := make(map[string]*model.User) @@ -601,6 +650,99 @@ func (us SqlUserStore) GetDirectProfiles(userId string) StoreChannel { return storeChannel } +func (us SqlUserStore) GetProfilesByUsernames(usernames []string, teamId string) StoreChannel { + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + var users []*model.User + props := make(map[string]interface{}) + idQuery := "" + + for index, usernames := range usernames { + if len(idQuery) > 0 { + idQuery += ", " + } + + props["username"+strconv.Itoa(index)] = usernames + idQuery += ":username" + strconv.Itoa(index) + } + + props["TeamId"] = teamId + + if _, err := us.GetReplica().Select(&users, `SELECT Users.* FROM Users INNER JOIN TeamMembers ON + Users.Id = TeamMembers.UserId AND Users.Username IN (`+idQuery+`) AND TeamMembers.TeamId = :TeamId `, props); err != nil { + result.Err = model.NewLocAppError("SqlUserStore.GetProfilesByUsernames", "store.sql_user.get_profiles.app_error", nil, err.Error()) + } else { + userMap := make(map[string]*model.User) + + for _, u := range users { + u.Password = "" + u.AuthData = new(string) + *u.AuthData = "" + userMap[u.Id] = u + } + + result.Data = userMap + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} + +type UserWithLastActivityAt struct { + model.User + LastActivityAt int64 +} + +func (us SqlUserStore) GetRecentlyActiveUsersForTeam(teamId string) StoreChannel { + + storeChannel := make(StoreChannel) + + go func() { + result := StoreResult{} + + var users []*UserWithLastActivityAt + + if _, err := us.GetReplica().Select(&users, ` + SELECT + u.*, + s.LastActivityAt + FROM Users AS u + INNER JOIN TeamMembers AS t ON u.Id = t.UserId + INNER JOIN Status AS s ON s.UserId = t.UserId + WHERE t.TeamId = :TeamId + ORDER BY s.LastActivityAt DESC + LIMIT 100 + `, map[string]interface{}{"TeamId": teamId}); err != nil { + result.Err = model.NewLocAppError("SqlUserStore.GetRecentlyActiveUsers", "store.sql_user.get_recently_active_users.app_error", nil, err.Error()) + } else { + + userMap := make(map[string]*model.User) + + for _, userWithLastActivityAt := range users { + u := userWithLastActivityAt.User + u.Password = "" + u.AuthData = new(string) + *u.AuthData = "" + u.LastActivityAt = userWithLastActivityAt.LastActivityAt + userMap[u.Id] = &u + } + + result.Data = userMap + } + + storeChannel <- result + close(storeChannel) + }() + + return storeChannel +} + func (us SqlUserStore) GetProfileByIds(userIds []string) StoreChannel { storeChannel := make(StoreChannel, 1) @@ -938,3 +1080,144 @@ func (us SqlUserStore) GetUnreadCountForChannel(userId string, channelId string) return storeChannel } + +func (us SqlUserStore) Search(teamId string, term string, searchType string) StoreChannel { + storeChannel := make(StoreChannel, 1) + + go func() { + searchQuery := "" + if teamId == "" { + searchQuery = ` + SELECT + * + FROM + Users + WHERE + DeleteAt = 0 + SEARCH_CLAUSE + ORDER BY Username ASC + LIMIT 50` + } else { + searchQuery = ` + SELECT + Users.* + FROM + Users, TeamMembers + WHERE + TeamMembers.TeamId = :TeamId + AND Users.Id = TeamMembers.UserId + AND Users.DeleteAt = 0 + AND TeamMembers.DeleteAt = 0 + SEARCH_CLAUSE + ORDER BY Users.Username ASC + LIMIT 100` + } + + storeChannel <- us.performSearch(searchQuery, term, searchType, map[string]interface{}{"TeamId": teamId}) + close(storeChannel) + + }() + + return storeChannel +} + +func (us SqlUserStore) SearchNotInChannel(teamId string, channelId string, term string, searchType string) StoreChannel { + storeChannel := make(StoreChannel, 1) + + go func() { + searchQuery := "" + if teamId == "" { + searchQuery = ` + SELECT + u.* + FROM Users u + LEFT JOIN ChannelMembers cm + ON cm.UserId = u.Id + AND cm.ChannelId = :ChannelId + WHERE cm.UserId IS NULL + SEARCH_CLAUSE + ORDER BY u.Username ASC + LIMIT 100` + } else { + searchQuery = ` + SELECT + u.* + FROM Users u + INNER JOIN TeamMembers tm + ON tm.UserId = u.Id + AND tm.TeamId = :TeamId + LEFT JOIN ChannelMembers cm + ON cm.UserId = u.Id + AND cm.ChannelId = :ChannelId + WHERE cm.UserId IS NULL + SEARCH_CLAUSE + ORDER BY u.Username ASC + LIMIT 100` + } + + storeChannel <- us.performSearch(searchQuery, term, searchType, map[string]interface{}{"TeamId": teamId, "ChannelId": channelId}) + close(storeChannel) + + }() + + return storeChannel +} + +func (us SqlUserStore) SearchInChannel(channelId string, term string, searchType string) StoreChannel { + storeChannel := make(StoreChannel, 1) + + go func() { + searchQuery := ` + SELECT + Users.* + FROM + Users, ChannelMembers + WHERE + ChannelMembers.ChannelId = :ChannelId + AND ChannelMembers.UserId = Users.Id + AND Users.DeleteAt = 0 + SEARCH_CLAUSE + ORDER BY Users.Username ASC + LIMIT 100` + + storeChannel <- us.performSearch(searchQuery, term, searchType, map[string]interface{}{"ChannelId": channelId}) + close(storeChannel) + + }() + + return storeChannel +} + +func (us SqlUserStore) performSearch(searchQuery string, term string, searchType string, parameters map[string]interface{}) StoreResult { + result := StoreResult{} + + if term == "" { + searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1) + } else if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES { + term = term + ":*" + searchClause := fmt.Sprintf("AND (%s) @@ to_tsquery(:Term)", searchType) + searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", searchClause, 1) + } else if utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_MYSQL { + term = term + "*" + searchClause := fmt.Sprintf("AND MATCH(%s) AGAINST (:Term IN BOOLEAN MODE)", searchType) + searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", searchClause, 1) + } + + var users []*model.User + + parameters["Term"] = term + + if _, err := us.GetReplica().Select(&users, searchQuery, parameters); err != nil { + result.Err = model.NewLocAppError("SqlUserStore.Search", "store.sql_user.search.app_error", nil, "term="+term+", "+"search_type="+searchType+", "+err.Error()) + } else { + for _, u := range users { + u.Password = "" + u.AuthData = new(string) + *u.AuthData = "" + } + + result.Data = users + } + + return result +} diff --git a/store/sql_user_store_test.go b/store/sql_user_store_test.go index 076be1a81..7ffb68a47 100644 --- a/store/sql_user_store_test.go +++ b/store/sql_user_store_test.go @@ -205,7 +205,7 @@ func TestUserStoreGetAllProfiles(t *testing.T) { Must(store.User().Save(u2)) Must(store.Team().SaveMember(&model.TeamMember{TeamId: teamId, UserId: u2.Id})) - if r1 := <-store.User().GetAllProfiles(); r1.Err != nil { + if r1 := <-store.User().GetAllProfiles(0, 100); r1.Err != nil { t.Fatal(r1.Err) } else { users := r1.Data.(map[string]*model.User) @@ -213,6 +213,15 @@ func TestUserStoreGetAllProfiles(t *testing.T) { t.Fatal("invalid returned users") } } + + if r2 := <-store.User().GetAllProfiles(0, 1); r2.Err != nil { + t.Fatal(r2.Err) + } else { + users := r2.Data.(map[string]*model.User) + if len(users) != 1 { + t.Fatal("invalid returned users, limit did not work") + } + } } func TestUserStoreGetProfiles(t *testing.T) { @@ -230,7 +239,7 @@ func TestUserStoreGetProfiles(t *testing.T) { Must(store.User().Save(u2)) Must(store.Team().SaveMember(&model.TeamMember{TeamId: teamId, UserId: u2.Id})) - if r1 := <-store.User().GetProfiles(teamId); r1.Err != nil { + if r1 := <-store.User().GetProfiles(teamId, 0, 100); r1.Err != nil { t.Fatal(r1.Err) } else { users := r1.Data.(map[string]*model.User) @@ -243,7 +252,7 @@ func TestUserStoreGetProfiles(t *testing.T) { } } - if r2 := <-store.User().GetProfiles("123"); r2.Err != nil { + if r2 := <-store.User().GetProfiles("123", 0, 100); r2.Err != nil { t.Fatal(r2.Err) } else { if len(r2.Data.(map[string]*model.User)) != 0 { @@ -252,7 +261,7 @@ func TestUserStoreGetProfiles(t *testing.T) { } } -func TestUserStoreGetDirectProfiles(t *testing.T) { +func TestUserStoreGetProfilesInChannel(t *testing.T) { Setup() teamId := model.NewId() @@ -267,22 +276,166 @@ func TestUserStoreGetDirectProfiles(t *testing.T) { Must(store.User().Save(u2)) Must(store.Team().SaveMember(&model.TeamMember{TeamId: teamId, UserId: u2.Id})) - if r1 := <-store.User().GetDirectProfiles(u1.Id); r1.Err != nil { + c1 := model.Channel{} + c1.TeamId = teamId + c1.DisplayName = "Profiles in channel" + c1.Name = "profiles-" + model.NewId() + c1.Type = model.CHANNEL_OPEN + + c2 := model.Channel{} + c2.TeamId = teamId + c2.DisplayName = "Profiles in private" + c2.Name = "profiles-" + model.NewId() + c2.Type = model.CHANNEL_PRIVATE + + Must(store.Channel().Save(&c1)) + Must(store.Channel().Save(&c2)) + + m1 := model.ChannelMember{} + m1.ChannelId = c1.Id + m1.UserId = u1.Id + m1.NotifyProps = model.GetDefaultChannelNotifyProps() + + m2 := model.ChannelMember{} + m2.ChannelId = c1.Id + m2.UserId = u2.Id + m2.NotifyProps = model.GetDefaultChannelNotifyProps() + + m3 := model.ChannelMember{} + m3.ChannelId = c2.Id + m3.UserId = u1.Id + m3.NotifyProps = model.GetDefaultChannelNotifyProps() + + Must(store.Channel().SaveMember(&m1)) + Must(store.Channel().SaveMember(&m2)) + Must(store.Channel().SaveMember(&m3)) + + if r1 := <-store.User().GetProfilesInChannel(c1.Id, -1, -1, false); r1.Err != nil { t.Fatal(r1.Err) } else { users := r1.Data.(map[string]*model.User) - if len(users) != 0 { + if len(users) != 2 { t.Fatal("invalid returned users") } + + if users[u1.Id].Id != u1.Id { + t.Fatal("invalid returned user") + } } - if r2 := <-store.User().GetDirectProfiles("123"); r2.Err != nil { + if r2 := <-store.User().GetProfilesInChannel(c2.Id, -1, -1, false); r2.Err != nil { t.Fatal(r2.Err) } else { - if len(r2.Data.(map[string]*model.User)) != 0 { + if len(r2.Data.(map[string]*model.User)) != 1 { + t.Fatal("should have returned empty map") + } + } + + if r2 := <-store.User().GetProfilesInChannel(c2.Id, -1, -1, true); r2.Err != nil { + t.Fatal(r2.Err) + } else { + if len(r2.Data.(map[string]*model.User)) != 1 { + t.Fatal("should have returned empty map") + } + } + + if r2 := <-store.User().GetProfilesInChannel(c2.Id, -1, -1, true); r2.Err != nil { + t.Fatal(r2.Err) + } else { + if len(r2.Data.(map[string]*model.User)) != 1 { t.Fatal("should have returned empty map") } } + + store.User().InvalidateProfilesInChannelCache(c2.Id) +} + +func TestUserStoreGetProfilesNotInChannel(t *testing.T) { + Setup() + + teamId := model.NewId() + + u1 := &model.User{} + u1.Email = model.NewId() + Must(store.User().Save(u1)) + Must(store.Team().SaveMember(&model.TeamMember{TeamId: teamId, UserId: u1.Id})) + + u2 := &model.User{} + u2.Email = model.NewId() + Must(store.User().Save(u2)) + Must(store.Team().SaveMember(&model.TeamMember{TeamId: teamId, UserId: u2.Id})) + + c1 := model.Channel{} + c1.TeamId = teamId + c1.DisplayName = "Profiles in channel" + c1.Name = "profiles-" + model.NewId() + c1.Type = model.CHANNEL_OPEN + + c2 := model.Channel{} + c2.TeamId = teamId + c2.DisplayName = "Profiles in private" + c2.Name = "profiles-" + model.NewId() + c2.Type = model.CHANNEL_PRIVATE + + Must(store.Channel().Save(&c1)) + Must(store.Channel().Save(&c2)) + + if r1 := <-store.User().GetProfilesNotInChannel(teamId, c1.Id, 0, 100); r1.Err != nil { + t.Fatal(r1.Err) + } else { + users := r1.Data.(map[string]*model.User) + if len(users) != 2 { + t.Fatal("invalid returned users") + } + + if users[u1.Id].Id != u1.Id { + t.Fatal("invalid returned user") + } + } + + if r2 := <-store.User().GetProfilesNotInChannel(teamId, c2.Id, 0, 100); r2.Err != nil { + t.Fatal(r2.Err) + } else { + if len(r2.Data.(map[string]*model.User)) != 2 { + t.Fatal("invalid returned users") + } + } + + m1 := model.ChannelMember{} + m1.ChannelId = c1.Id + m1.UserId = u1.Id + m1.NotifyProps = model.GetDefaultChannelNotifyProps() + + m2 := model.ChannelMember{} + m2.ChannelId = c1.Id + m2.UserId = u2.Id + m2.NotifyProps = model.GetDefaultChannelNotifyProps() + + m3 := model.ChannelMember{} + m3.ChannelId = c2.Id + m3.UserId = u1.Id + m3.NotifyProps = model.GetDefaultChannelNotifyProps() + + Must(store.Channel().SaveMember(&m1)) + Must(store.Channel().SaveMember(&m2)) + Must(store.Channel().SaveMember(&m3)) + + if r1 := <-store.User().GetProfilesNotInChannel(teamId, c1.Id, 0, 100); r1.Err != nil { + t.Fatal(r1.Err) + } else { + users := r1.Data.(map[string]*model.User) + if len(users) != 0 { + t.Fatal("invalid returned users") + } + } + + if r2 := <-store.User().GetProfilesNotInChannel(teamId, c2.Id, 0, 100); r2.Err != nil { + t.Fatal(r2.Err) + } else { + if len(r2.Data.(map[string]*model.User)) != 1 { + t.Fatal("should have had 1 user not in channel") + } + } } func TestUserStoreGetProfilesByIds(t *testing.T) { @@ -326,7 +479,7 @@ func TestUserStoreGetProfilesByIds(t *testing.T) { } } - if r2 := <-store.User().GetProfiles("123"); r2.Err != nil { + if r2 := <-store.User().GetProfiles("123", 0, 100); r2.Err != nil { t.Fatal(r2.Err) } else { if len(r2.Data.(map[string]*model.User)) != 0 { @@ -335,6 +488,50 @@ func TestUserStoreGetProfilesByIds(t *testing.T) { } } +func TestUserStoreGetProfilesByUsernames(t *testing.T) { + Setup() + + teamId := model.NewId() + + u1 := &model.User{} + u1.Email = model.NewId() + u1.Username = "username1" + model.NewId() + Must(store.User().Save(u1)) + Must(store.Team().SaveMember(&model.TeamMember{TeamId: teamId, UserId: u1.Id})) + + u2 := &model.User{} + u2.Email = model.NewId() + u2.Username = "username2" + model.NewId() + Must(store.User().Save(u2)) + Must(store.Team().SaveMember(&model.TeamMember{TeamId: teamId, UserId: u2.Id})) + + if r1 := <-store.User().GetProfilesByUsernames([]string{u1.Username, u2.Username}, teamId); r1.Err != nil { + t.Fatal(r1.Err) + } else { + users := r1.Data.(map[string]*model.User) + if len(users) != 2 { + t.Fatal("invalid returned users") + } + + if users[u1.Id].Id != u1.Id { + t.Fatal("invalid returned user") + } + } + + if r1 := <-store.User().GetProfilesByUsernames([]string{u1.Username}, teamId); r1.Err != nil { + t.Fatal(r1.Err) + } else { + users := r1.Data.(map[string]*model.User) + if len(users) != 1 { + t.Fatal("invalid returned users") + } + + if users[u1.Id].Id != u1.Id { + t.Fatal("invalid returned user") + } + } +} + func TestUserStoreGetSystemAdminProfiles(t *testing.T) { Setup() @@ -713,3 +910,216 @@ func TestUserStoreUpdateMfaActive(t *testing.T) { t.Fatal(err) } } + +func TestUserStoreGetRecentlyActiveUsersForTeam(t *testing.T) { + Setup() + + u1 := &model.User{} + u1.Email = model.NewId() + Must(store.User().Save(u1)) + Must(store.Status().SaveOrUpdate(&model.Status{u1.Id, model.STATUS_ONLINE, false, model.GetMillis(), ""})) + tid := model.NewId() + Must(store.Team().SaveMember(&model.TeamMember{TeamId: tid, UserId: u1.Id})) + + if r1 := <-store.User().GetRecentlyActiveUsersForTeam(tid); r1.Err != nil { + t.Fatal(r1.Err) + } +} + +func TestUserStoreSearch(t *testing.T) { + Setup() + + u1 := &model.User{} + u1.Username = "jimbo" + model.NewId() + u1.FirstName = "Tim" + u1.LastName = "Bill" + u1.Nickname = "Rob" + u1.Email = model.NewId() + Must(store.User().Save(u1)) + + tid := model.NewId() + Must(store.Team().SaveMember(&model.TeamMember{TeamId: tid, UserId: u1.Id})) + + if r1 := <-store.User().Search(tid, "jimb", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } + + if r1 := <-store.User().Search("", "jimb", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } + + if r1 := <-store.User().Search(tid, "", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } + + c1 := model.Channel{} + c1.TeamId = tid + c1.DisplayName = "NameName" + c1.Name = "a" + model.NewId() + "b" + c1.Type = model.CHANNEL_OPEN + c1 = *Must(store.Channel().Save(&c1)).(*model.Channel) + + if r1 := <-store.User().SearchNotInChannel(tid, c1.Id, "jimb", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } + + if r1 := <-store.User().SearchNotInChannel("", c1.Id, "jimb", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } + + if r1 := <-store.User().SearchNotInChannel("junk", c1.Id, "jimb", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if found { + t.Fatal("should not have found user") + } + } + + if r1 := <-store.User().SearchInChannel(c1.Id, "jimb", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if found { + t.Fatal("should not have found user") + } + } + + Must(store.Channel().SaveMember(&model.ChannelMember{ChannelId: c1.Id, UserId: u1.Id, NotifyProps: model.GetDefaultChannelNotifyProps()})) + + if r1 := <-store.User().SearchInChannel(c1.Id, "jimb", USER_SEARCH_TYPE_USERNAME); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } + + if r1 := <-store.User().Search(tid, "Tim", USER_SEARCH_TYPE_ALL); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } + + if r1 := <-store.User().Search(tid, "Bill", USER_SEARCH_TYPE_ALL); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } + + if r1 := <-store.User().Search(tid, "Rob", USER_SEARCH_TYPE_ALL); r1.Err != nil { + t.Fatal(r1.Err) + } else { + profiles := r1.Data.([]*model.User) + found := false + for _, profile := range profiles { + if profile.Id == u1.Id { + found = true + break + } + } + + if !found { + t.Fatal("should have found user") + } + } +} diff --git a/store/store.go b/store/store.go index 7474d3afb..900709f16 100644 --- a/store/store.go +++ b/store/store.go @@ -49,6 +49,8 @@ type Store interface { MarkSystemRanUnitTests() Close() DropAllTables() + TotalMasterDbConnections() int + TotalReadDbConnections() int } type TeamStore interface { @@ -66,7 +68,9 @@ type TeamStore interface { SaveMember(member *model.TeamMember) StoreChannel UpdateMember(member *model.TeamMember) StoreChannel GetMember(teamId string, userId string) StoreChannel - GetMembers(teamId string) StoreChannel + GetMembers(teamId string, offset int, limit int) StoreChannel + GetMembersByIds(teamId string, userIds []string) StoreChannel + GetMemberCount(teamId string) StoreChannel GetTeamsForUser(userId string) StoreChannel RemoveMember(teamId string, userId string) StoreChannel RemoveAllMembersByTeam(teamId string) StoreChannel @@ -89,16 +93,17 @@ type ChannelStore interface { GetChannelCounts(teamId string, userId string) StoreChannel GetAll(teamId string) StoreChannel GetForPost(postId string) StoreChannel - SaveMember(member *model.ChannelMember) StoreChannel UpdateMember(member *model.ChannelMember) StoreChannel GetMembers(channelId string) StoreChannel GetMember(channelId string, userId string) StoreChannel + GetAllChannelMembersForUser(userId string, allowFromCache bool) StoreChannel + InvalidateAllChannelMembersForUser(userId string) + IsUserInChannelUseCache(userId string, channelId string) bool GetMemberForPost(postId string, userId string) StoreChannel GetMemberCount(channelId string) StoreChannel RemoveMember(channelId string, userId string) StoreChannel PermanentDeleteMembersByUser(userId string) StoreChannel - GetExtraMembers(channelId string, limit int) StoreChannel UpdateLastViewedAt(channelId string, userId string) StoreChannel SetLastViewedAt(channelId string, userId string, newLastViewedAt int64) StoreChannel IncrementMentionCount(channelId string, userId string) StoreChannel @@ -135,9 +140,12 @@ type UserStore interface { UpdateMfaActive(userId string, active bool) StoreChannel Get(id string) StoreChannel GetAll() StoreChannel - GetAllProfiles() StoreChannel - GetProfiles(teamId string) StoreChannel - GetDirectProfiles(userId string) StoreChannel + InvalidateProfilesInChannelCache(channelId string) + GetProfilesInChannel(channelId string, offset int, limit int, allowFromCache bool) StoreChannel + GetProfilesNotInChannel(teamId string, channelId string, offset int, limit int) StoreChannel + GetProfilesByUsernames(usernames []string, teamId string) StoreChannel + GetAllProfiles(offset int, limit int) StoreChannel + GetProfiles(teamId string, offset int, limit int) StoreChannel GetProfileByIds(userId []string) StoreChannel GetByEmail(email string) StoreChannel GetByAuth(authData *string, authService string) StoreChannel @@ -147,7 +155,6 @@ type UserStore interface { VerifyEmail(userId string) StoreChannel GetEtagForAllProfiles() StoreChannel GetEtagForProfiles(teamId string) StoreChannel - GetEtagForDirectProfiles(userId string) StoreChannel UpdateFailedPasswordAttempts(userId string, attempts int) StoreChannel GetTotalUsersCount() StoreChannel GetSystemAdminProfiles() StoreChannel @@ -155,6 +162,10 @@ type UserStore interface { AnalyticsUniqueUserCount(teamId string) StoreChannel GetUnreadCount(userId string) StoreChannel GetUnreadCountForChannel(userId string, channelId string) StoreChannel + GetRecentlyActiveUsersForTeam(teamId string) StoreChannel + Search(teamId string, term string, searchType string) StoreChannel + SearchInChannel(channelId string, term string, searchType string) StoreChannel + SearchNotInChannel(teamId string, channelId string, term string, searchType string) StoreChannel } type SessionStore interface { @@ -274,6 +285,7 @@ type EmojiStore interface { type StatusStore interface { SaveOrUpdate(status *model.Status) StoreChannel Get(userId string) StoreChannel + GetByIds(userIds []string) StoreChannel GetOnlineAway() StoreChannel GetOnline() StoreChannel GetAllFromTeam(teamId string) StoreChannel -- cgit v1.2.3-1-g7c22