MM-8796: Full implementation of "Schemes" in Store/Model/App layers. (#8357)

* Add Scheme model and stub store.

* Port ChannelStore to be Scheme aware.

* Make almost all the API/APP layer work with ChannelSchemes.

Only thing still hacky is UpdateChannelMemberRoles().

* Add basic SchemeStore implementation.

* Migrate UpdateChannelMemberRoles properly and fix tests.

* Update store tests and mocks so they work.

* Include creating default roles in Scheme create store function.

* Implement role deletion and start scheme deletion.

* Only use non-deleted roles for authorization.

* Add GetByScheme method to Team store.

* Add GetChannelsByScheme.

* Update store mocks.

* Implement scheme deletion in the store.

* Rename is valid function.

* Add offset and limit to queries to fetch teams and channels by scheme.

* Fix queries.

* Implement scheme awareness in Team store and add a migration.

* Tidy up ChannelStore mapping functions and add exhaustive unit tests.

* Add all missing i18n.

* Proper tests for TeamStore internal functions and fix them.

* Make additional TeamMember fields nullable.

* Make new ChannelMember fields nullable.

* Create new nullable columns without defaults.

* Make new fields in large tables nullalble.

* Fix empty list of TeamMembers.

* Deduplicate SQL queries.

* Fix spelling.

* Fix review comment.

* More review fixes.

* More review fixes.

1 files changed, 312 insertions, 51 deletions

diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go
index 21785c461..0ddbc7221 100644
--- a/store/sqlstore/channel_store.go
+++ b/store/sqlstore/channel_store.go
@@ -13,6 +13,7 @@ import (
 
 	l4g "github.com/alecthomas/log4go"
 	"github.com/mattermost/gorp"
+
 	"github.com/mattermost/mattermost-server/einterfaces"
 	"github.com/mattermost/mattermost-server/model"
 	"github.com/mattermost/mattermost-server/store"
@@ -37,6 +38,200 @@ type SqlChannelStore struct {
 	metrics einterfaces.MetricsInterface
 }
 
+type channelMember struct {
+	ChannelId    string
+	UserId       string
+	Roles        string
+	LastViewedAt int64
+	MsgCount     int64
+	MentionCount int64
+	NotifyProps  model.StringMap
+	LastUpdateAt int64
+	SchemeUser   sql.NullBool
+	SchemeAdmin  sql.NullBool
+}
+
+func NewChannelMemberFromModel(cm *model.ChannelMember) *channelMember {
+	return &channelMember{
+		ChannelId:    cm.ChannelId,
+		UserId:       cm.UserId,
+		Roles:        cm.ExplicitRoles,
+		LastViewedAt: cm.LastViewedAt,
+		MsgCount:     cm.MsgCount,
+		MentionCount: cm.MentionCount,
+		NotifyProps:  cm.NotifyProps,
+		LastUpdateAt: cm.LastUpdateAt,
+		SchemeUser:   sql.NullBool{Valid: true, Bool: cm.SchemeUser},
+		SchemeAdmin:  sql.NullBool{Valid: true, Bool: cm.SchemeAdmin},
+	}
+}
+
+type channelMemberWithSchemeRoles struct {
+	ChannelId                     string
+	UserId                        string
+	Roles                         string
+	LastViewedAt                  int64
+	MsgCount                      int64
+	MentionCount                  int64
+	NotifyProps                   model.StringMap
+	LastUpdateAt                  int64
+	SchemeUser                    sql.NullBool
+	SchemeAdmin                   sql.NullBool
+	TeamSchemeDefaultUserRole     sql.NullString
+	TeamSchemeDefaultAdminRole    sql.NullString
+	ChannelSchemeDefaultUserRole  sql.NullString
+	ChannelSchemeDefaultAdminRole sql.NullString
+}
+
+type channelMemberWithSchemeRolesList []channelMemberWithSchemeRoles
+
+func (db channelMemberWithSchemeRoles) ToModel() *model.ChannelMember {
+	var roles []string
+	var explicitRoles []string
+
+	// Identify any system-wide scheme derived roles that are in "Roles" field due to not yet being migrated,
+	// and exclude them from ExplicitRoles field.
+	schemeUser := db.SchemeUser.Valid && db.SchemeUser.Bool
+	schemeAdmin := db.SchemeAdmin.Valid && db.SchemeAdmin.Bool
+	for _, role := range strings.Fields(db.Roles) {
+		isImplicit := false
+		if role == model.CHANNEL_USER_ROLE_ID {
+			// We have an implicit role via the system scheme. Override the "schemeUser" field to true.
+			schemeUser = true
+			isImplicit = true
+		} else if role == model.CHANNEL_ADMIN_ROLE_ID {
+			// We have an implicit role via the system scheme.
+			schemeAdmin = true
+			isImplicit = true
+		}
+
+		if !isImplicit {
+			explicitRoles = append(explicitRoles, role)
+		}
+		roles = append(roles, role)
+	}
+
+	// Add any scheme derived roles that are not in the Roles field due to being Implicit from the Scheme, and add
+	// them to the Roles field for backwards compatibility reasons.
+	var schemeImpliedRoles []string
+	if db.SchemeUser.Valid && db.SchemeUser.Bool {
+		if db.ChannelSchemeDefaultUserRole.Valid && db.ChannelSchemeDefaultUserRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultUserRole.String)
+		} else if db.TeamSchemeDefaultUserRole.Valid && db.TeamSchemeDefaultUserRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultUserRole.String)
+		} else {
+			schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_USER_ROLE_ID)
+		}
+	}
+	if db.SchemeAdmin.Valid && db.SchemeAdmin.Bool {
+		if db.ChannelSchemeDefaultAdminRole.Valid && db.ChannelSchemeDefaultAdminRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultAdminRole.String)
+		} else if db.TeamSchemeDefaultAdminRole.Valid && db.TeamSchemeDefaultAdminRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultAdminRole.String)
+		} else {
+			schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_ADMIN_ROLE_ID)
+		}
+	}
+	for _, impliedRole := range schemeImpliedRoles {
+		alreadyThere := false
+		for _, role := range roles {
+			if role == impliedRole {
+				alreadyThere = true
+			}
+		}
+		if !alreadyThere {
+			roles = append(roles, impliedRole)
+		}
+	}
+
+	return &model.ChannelMember{
+		ChannelId:     db.ChannelId,
+		UserId:        db.UserId,
+		Roles:         strings.Join(roles, " "),
+		LastViewedAt:  db.LastViewedAt,
+		MsgCount:      db.MsgCount,
+		MentionCount:  db.MentionCount,
+		NotifyProps:   db.NotifyProps,
+		LastUpdateAt:  db.LastUpdateAt,
+		SchemeAdmin:   schemeAdmin,
+		SchemeUser:    schemeUser,
+		ExplicitRoles: strings.Join(explicitRoles, " "),
+	}
+}
+
+func (db channelMemberWithSchemeRolesList) ToModel() *model.ChannelMembers {
+	cms := model.ChannelMembers{}
+
+	for _, cm := range db {
+		cms = append(cms, *cm.ToModel())
+	}
+
+	return &cms
+}
+
+type allChannelMember struct {
+	ChannelId                     string
+	Roles                         string
+	SchemeUser                    sql.NullBool
+	SchemeAdmin                   sql.NullBool
+	TeamSchemeDefaultUserRole     sql.NullString
+	TeamSchemeDefaultAdminRole    sql.NullString
+	ChannelSchemeDefaultUserRole  sql.NullString
+	ChannelSchemeDefaultAdminRole sql.NullString
+}
+
+type allChannelMembers []allChannelMember
+
+func (db allChannelMember) Process() (string, string) {
+	roles := strings.Fields(db.Roles)
+
+	// Add any scheme derived roles that are not in the Roles field due to being Implicit from the Scheme, and add
+	// them to the Roles field for backwards compatibility reasons.
+	var schemeImpliedRoles []string
+	if db.SchemeUser.Valid && db.SchemeUser.Bool {
+		if db.ChannelSchemeDefaultUserRole.Valid && db.ChannelSchemeDefaultUserRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultUserRole.String)
+		} else if db.TeamSchemeDefaultUserRole.Valid && db.TeamSchemeDefaultUserRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultUserRole.String)
+		} else {
+			schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_USER_ROLE_ID)
+		}
+	}
+	if db.SchemeAdmin.Valid && db.SchemeAdmin.Bool {
+		if db.ChannelSchemeDefaultAdminRole.Valid && db.ChannelSchemeDefaultAdminRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultAdminRole.String)
+		} else if db.TeamSchemeDefaultAdminRole.Valid && db.TeamSchemeDefaultAdminRole.String != "" {
+			schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultAdminRole.String)
+		} else {
+			schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_ADMIN_ROLE_ID)
+		}
+	}
+	for _, impliedRole := range schemeImpliedRoles {
+		alreadyThere := false
+		for _, role := range roles {
+			if role == impliedRole {
+				alreadyThere = true
+			}
+		}
+		if !alreadyThere {
+			roles = append(roles, impliedRole)
+		}
+	}
+
+	return db.ChannelId, strings.Join(roles, " ")
+}
+
+func (db allChannelMembers) ToMapStringString() map[string]string {
+	result := make(map[string]string)
+
+	for _, item := range db {
+		key, value := item.Process()
+		result[key] = value
+	}
+
+	return result
+}
+
 var channelMemberCountsCache = utils.NewLru(CHANNEL_MEMBERS_COUNTS_CACHE_SIZE)
 var allChannelMembersForUserCache = utils.NewLru(ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SIZE)
 var allChannelMembersNotifyPropsForChannelCache = utils.NewLru(ALL_CHANNEL_MEMBERS_NOTIFY_PROPS_FOR_CHANNEL_CACHE_SIZE)
@@ -76,8 +271,9 @@ func NewSqlChannelStore(sqlStore SqlStore, metrics einterfaces.MetricsInterface)
 		table.ColMap("Header").SetMaxSize(1024)
 		table.ColMap("Purpose").SetMaxSize(250)
 		table.ColMap("CreatorId").SetMaxSize(26)
+		table.ColMap("SchemeId").SetMaxSize(26)
 
-		tablem := db.AddTableWithName(model.ChannelMember{}, "ChannelMembers").SetKeys(false, "ChannelId", "UserId")
+		tablem := db.AddTableWithName(channelMember{}, "ChannelMembers").SetKeys(false, "ChannelId", "UserId")
 		tablem.ColMap("ChannelId").SetMaxSize(26)
 		tablem.ColMap("UserId").SetMaxSize(26)
 		tablem.ColMap("Roles").SetMaxSize(64)
@@ -138,12 +334,12 @@ func (s SqlChannelStore) CreateDirectChannel(userId string, otherUserId string)
 	cm1 := &model.ChannelMember{
 		UserId:      userId,
 		NotifyProps: model.GetDefaultChannelNotifyProps(),
-		Roles:       model.CHANNEL_USER_ROLE_ID,
+		SchemeUser:  true,
 	}
 	cm2 := &model.ChannelMember{
 		UserId:      otherUserId,
 		NotifyProps: model.GetDefaultChannelNotifyProps(),
-		Roles:       model.CHANNEL_USER_ROLE_ID,
+		SchemeUser:  true,
 	}
 
 	return s.SaveDirectChannel(channel, cm1, cm2)
@@ -732,6 +928,25 @@ func (s SqlChannelStore) GetDeleted(teamId string, offset int, limit int) store.
 	})
 }
 
+var CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY = `
+	SELECT
+		ChannelMembers.*,
+		TeamScheme.DefaultChannelUserRole TeamSchemeDefaultUserRole,
+		TeamScheme.DefaultChannelAdminRole TeamSchemeDefaultAdminRole,
+		ChannelScheme.DefaultChannelUserRole ChannelSchemeDefaultUserRole,
+		ChannelScheme.DefaultChannelAdminRole ChannelSchemeDefaultAdminRole
+	FROM 
+		ChannelMembers
+	INNER JOIN 
+		Channels ON ChannelMembers.ChannelId = Channels.Id
+	LEFT JOIN
+		Schemes ChannelScheme ON Channels.SchemeId = ChannelScheme.Id
+	LEFT JOIN
+		Teams ON Channels.TeamId = Teams.Id
+	LEFT JOIN
+		Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id 
+`
+
 func (s SqlChannelStore) SaveMember(member *model.ChannelMember) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
 		// Grab the channel we are saving this member to
@@ -750,7 +965,7 @@ func (s SqlChannelStore) SaveMember(member *model.ChannelMember) store.StoreChan
 					if err := transaction.Commit(); err != nil {
 						result.Err = model.NewAppError("SqlChannelStore.SaveMember", "store.sql_channel.save_member.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError)
 					}
-					// If successfull record members have changed in channel
+					// If successful record members have changed in channel
 					if mu := <-s.extraUpdated(channel); mu.Err != nil {
 						result.Err = mu.Err
 					}
@@ -770,14 +985,25 @@ func (s SqlChannelStore) saveMemberT(transaction *gorp.Transaction, member *mode
 		return result
 	}
 
-	if err := transaction.Insert(member); err != nil {
+	dbMember := NewChannelMemberFromModel(member)
+
+	if err := transaction.Insert(dbMember); err != nil {
 		if IsUniqueConstraintError(err, []string{"ChannelId", "channelmembers_pkey"}) {
 			result.Err = model.NewAppError("SqlChannelStore.SaveMember", "store.sql_channel.save_member.exists.app_error", nil, "channel_id="+member.ChannelId+", user_id="+member.UserId+", "+err.Error(), http.StatusBadRequest)
 		} else {
 			result.Err = model.NewAppError("SqlChannelStore.SaveMember", "store.sql_channel.save_member.save.app_error", nil, "channel_id="+member.ChannelId+", user_id="+member.UserId+", "+err.Error(), http.StatusInternalServerError)
 		}
 	} else {
-		result.Data = member
+		var retrievedMember channelMemberWithSchemeRoles
+		if err := transaction.SelectOne(&retrievedMember, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId = :UserId", map[string]interface{}{"ChannelId": dbMember.ChannelId, "UserId": dbMember.UserId}); err != nil {
+			if err == sql.ErrNoRows {
+				result.Err = model.NewAppError("SqlChannelStore.GetMember", store.MISSING_CHANNEL_MEMBER_ERROR, nil, "channel_id="+dbMember.ChannelId+"user_id="+dbMember.UserId+","+err.Error(), http.StatusNotFound)
+			} else {
+				result.Err = model.NewAppError("SqlChannelStore.GetMember", "store.sql_channel.get_member.app_error", nil, "channel_id="+dbMember.ChannelId+"user_id="+dbMember.UserId+","+err.Error(), http.StatusInternalServerError)
+			}
+		} else {
+			result.Data = retrievedMember.ToModel()
+		}
 	}
 
 	return result
@@ -791,38 +1017,48 @@ func (s SqlChannelStore) UpdateMember(member *model.ChannelMember) store.StoreCh
 			return
 		}
 
-		if _, err := s.GetMaster().Update(member); err != nil {
+		if _, err := s.GetMaster().Update(NewChannelMemberFromModel(member)); err != nil {
 			result.Err = model.NewAppError("SqlChannelStore.UpdateMember", "store.sql_channel.update_member.app_error", nil, "channel_id="+member.ChannelId+", "+"user_id="+member.UserId+", "+err.Error(), http.StatusInternalServerError)
 		} else {
-			result.Data = member
+			var dbMember channelMemberWithSchemeRoles
+
+			if err := s.GetReplica().SelectOne(&dbMember, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId = :UserId", map[string]interface{}{"ChannelId": member.ChannelId, "UserId": member.UserId}); err != nil {
+				if err == sql.ErrNoRows {
+					result.Err = model.NewAppError("SqlChannelStore.GetMember", store.MISSING_CHANNEL_MEMBER_ERROR, nil, "channel_id="+member.ChannelId+"user_id="+member.UserId+","+err.Error(), http.StatusNotFound)
+				} else {
+					result.Err = model.NewAppError("SqlChannelStore.GetMember", "store.sql_channel.get_member.app_error", nil, "channel_id="+member.ChannelId+"user_id="+member.UserId+","+err.Error(), http.StatusInternalServerError)
+				}
+			} else {
+				result.Data = dbMember.ToModel()
+			}
 		}
 	})
 }
 
 func (s SqlChannelStore) GetMembers(channelId string, offset, limit int) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
-		var members model.ChannelMembers
-		_, err := s.GetReplica().Select(&members, "SELECT * FROM ChannelMembers WHERE ChannelId = :ChannelId LIMIT :Limit OFFSET :Offset", map[string]interface{}{"ChannelId": channelId, "Limit": limit, "Offset": offset})
+		var dbMembers channelMemberWithSchemeRolesList
+		_, err := s.GetReplica().Select(&dbMembers, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelId = :ChannelId LIMIT :Limit OFFSET :Offset", map[string]interface{}{"ChannelId": channelId, "Limit": limit, "Offset": offset})
 		if err != nil {
 			result.Err = model.NewAppError("SqlChannelStore.GetMembers", "store.sql_channel.get_members.app_error", nil, "channel_id="+channelId+err.Error(), http.StatusInternalServerError)
 		} else {
-			result.Data = &members
+			result.Data = dbMembers.ToModel()
 		}
 	})
 }
 
 func (s SqlChannelStore) GetMember(channelId string, userId string) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
-		var member model.ChannelMember
+		var dbMember channelMemberWithSchemeRoles
 
-		if err := s.GetReplica().SelectOne(&member, "SELECT * FROM ChannelMembers WHERE ChannelId = :ChannelId AND UserId = :UserId", map[string]interface{}{"ChannelId": channelId, "UserId": userId}); err != nil {
+		if err := s.GetReplica().SelectOne(&dbMember, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId = :UserId", map[string]interface{}{"ChannelId": channelId, "UserId": userId}); err != nil {
 			if err == sql.ErrNoRows {
 				result.Err = model.NewAppError("SqlChannelStore.GetMember", store.MISSING_CHANNEL_MEMBER_ERROR, nil, "channel_id="+channelId+"user_id="+userId+","+err.Error(), http.StatusNotFound)
 			} else {
 				result.Err = model.NewAppError("SqlChannelStore.GetMember", "store.sql_channel.get_member.app_error", nil, "channel_id="+channelId+"user_id="+userId+","+err.Error(), http.StatusInternalServerError)
 			}
 		} else {
-			result.Data = &member
+			result.Data = dbMember.ToModel()
 		}
 	})
 }
@@ -866,30 +1102,37 @@ func (s SqlChannelStore) IsUserInChannelUseCache(userId string, channelId string
 
 func (s SqlChannelStore) GetMemberForPost(postId string, userId string) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
-		member := &model.ChannelMember{}
-		if err := s.GetReplica().SelectOne(
-			member,
-			`SELECT
-				ChannelMembers.*
-			FROM
-				ChannelMembers,
-				Posts
+		var dbMember channelMemberWithSchemeRoles
+		if err := s.GetReplica().SelectOne(&dbMember,
+			`            
+			SELECT
+				ChannelMembers.*,
+				TeamScheme.DefaultChannelUserRole TeamSchemeDefaultUserRole,
+				TeamScheme.DefaultChannelAdminRole TeamSchemeDefaultAdminRole,
+				ChannelScheme.DefaultChannelUserRole ChannelSchemeDefaultUserRole,
+				ChannelScheme.DefaultChannelAdminRole ChannelSchemeDefaultAdminRole
+			FROM 
+				ChannelMembers
+			INNER JOIN
+				Posts ON ChannelMembers.ChannelId = Posts.ChannelId
+			INNER JOIN 
+				Channels ON ChannelMembers.ChannelId = Channels.Id
+			LEFT JOIN
+				Schemes ChannelScheme ON Channels.SchemeId = ChannelScheme.Id
+			LEFT JOIN
+				Teams ON Channels.TeamId = Teams.Id
+			LEFT JOIN
+				Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id
 			WHERE
-				ChannelMembers.ChannelId = Posts.ChannelId
-				AND ChannelMembers.UserId = :UserId
+				ChannelMembers.UserId = :UserId
 				AND Posts.Id = :PostId`, map[string]interface{}{"UserId": userId, "PostId": postId}); err != nil {
 			result.Err = model.NewAppError("SqlChannelStore.GetMemberForPost", "store.sql_channel.get_member_for_post.app_error", nil, "postId="+postId+", err="+err.Error(), http.StatusInternalServerError)
 		} else {
-			result.Data = member
+			result.Data = dbMember.ToModel()
 		}
 	})
 }
 
-type allChannelMember struct {
-	ChannelId string
-	Roles     string
-}
-
 func (s SqlChannelStore) GetAllChannelMembersForUser(userId string, allowFromCache bool) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
 		if allowFromCache {
@@ -910,17 +1153,32 @@ func (s SqlChannelStore) GetAllChannelMembersForUser(userId string, allowFromCac
 			}
 		}
 
-		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})
+		var data allChannelMembers
+		_, err := s.GetReplica().Select(&data, `
+			SELECT
+				ChannelMembers.ChannelId, ChannelMembers.Roles, ChannelMembers.SchemeUser, ChannelMembers.SchemeAdmin,
+				TeamScheme.DefaultChannelUserRole TeamSchemeDefaultUserRole,
+				TeamScheme.DefaultChannelAdminRole TeamSchemeDefaultAdminRole,
+				ChannelScheme.DefaultChannelUserRole ChannelSchemeDefaultUserRole,
+				ChannelScheme.DefaultChannelAdminRole ChannelSchemeDefaultAdminRole
+			FROM 
+				ChannelMembers
+			INNER JOIN 
+				Channels ON ChannelMembers.ChannelId = Channels.Id
+			LEFT JOIN
+				Schemes ChannelScheme ON Channels.SchemeId = ChannelScheme.Id
+			LEFT JOIN
+				Teams ON Channels.TeamId = Teams.Id
+			LEFT JOIN
+				Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id
+			WHERE
+				Channels.DeleteAt = 0
+				AND ChannelMembers.UserId = :UserId`, map[string]interface{}{"UserId": userId})
 
 		if err != nil {
 			result.Err = model.NewAppError("SqlChannelStore.GetAllChannelMembersForUser", "store.sql_channel.get_channels.get.app_error", nil, "userId="+userId+", err="+err.Error(), http.StatusInternalServerError)
 		} else {
-
-			ids := make(map[string]string)
-			for i := range data {
-				ids[data[i].ChannelId] = data[i].Roles
-			}
+			ids := data.ToMapStringString()
 
 			result.Data = ids
 
@@ -1249,21 +1507,13 @@ func (s SqlChannelStore) AnalyticsDeletedTypeCount(teamId string, channelType st
 
 func (s SqlChannelStore) GetMembersForUser(teamId string, userId string) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
-		members := &model.ChannelMembers{}
-		_, err := s.GetReplica().Select(members, `
-            SELECT cm.*
-            FROM ChannelMembers cm
-            INNER JOIN Channels c
-                ON c.Id = cm.ChannelId
-                AND (c.TeamId = :TeamId OR c.TeamId = '')
-                AND c.DeleteAt = 0
-            WHERE cm.UserId = :UserId
-		`, map[string]interface{}{"TeamId": teamId, "UserId": userId})
+		var dbMembers channelMemberWithSchemeRolesList
+		_, err := s.GetReplica().Select(&dbMembers, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.UserId = :UserId", map[string]interface{}{"TeamId": teamId, "UserId": userId})
 
 		if err != nil {
 			result.Err = model.NewAppError("SqlChannelStore.GetMembersForUser", "store.sql_channel.get_members.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError)
 		} else {
-			result.Data = members
+			result.Data = dbMembers.ToModel()
 		}
 	})
 }
@@ -1455,7 +1705,7 @@ func (s SqlChannelStore) performSearch(searchQuery string, term string, paramete
 
 func (s SqlChannelStore) GetMembersByIds(channelId string, userIds []string) store.StoreChannel {
 	return store.Do(func(result *store.StoreResult) {
-		var members model.ChannelMembers
+		var dbMembers channelMemberWithSchemeRolesList
 		props := make(map[string]interface{})
 		idQuery := ""
 
@@ -1470,11 +1720,22 @@ func (s SqlChannelStore) GetMembersByIds(channelId string, userIds []string) sto
 
 		props["ChannelId"] = channelId
 
-		if _, err := s.GetReplica().Select(&members, "SELECT * FROM ChannelMembers WHERE ChannelId = :ChannelId AND UserId IN ("+idQuery+")", props); err != nil {
+		if _, err := s.GetReplica().Select(&dbMembers, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId IN ("+idQuery+")", props); err != nil {
 			result.Err = model.NewAppError("SqlChannelStore.GetMembersByIds", "store.sql_channel.get_members_by_ids.app_error", nil, "channelId="+channelId+" "+err.Error(), http.StatusInternalServerError)
 		} else {
-			result.Data = &members
+			result.Data = dbMembers.ToModel()
+		}
+	})
+}
 
+func (s SqlChannelStore) GetChannelsByScheme(schemeId string, offset int, limit int) store.StoreChannel {
+	return store.Do(func(result *store.StoreResult) {
+		var channels []*model.Channel
+		_, err := s.GetReplica().Select(&channels, "SELECT * FROM Channels WHERE SchemeId = :SchemeId ORDER BY DisplayName LIMIT :Limit OFFSET :Offset", map[string]interface{}{"SchemeId": schemeId, "Offset": offset, "Limit": limit})
+		if err != nil {
+			result.Err = model.NewAppError("SqlChannelStore.GetChannelsByScheme", "store.sql_channel.get_by_scheme.app_error", nil, "schemeId="+schemeId+" "+err.Error(), http.StatusInternalServerError)
+		} else {
+			result.Data = channels
 		}
 	})
 }