github.com/haalcala/mattermost-server-change-repo/v5@v5.33.2/store/sqlstore/team_store.go (about)

     1  // Copyright (c) 2015-present Mattermost, Inc. All Rights Reserved.
     2  // See LICENSE.txt for license information.
     3  
     4  package sqlstore
     5  
     6  import (
     7  	"context"
     8  	"database/sql"
     9  	"fmt"
    10  	"strings"
    11  
    12  	sq "github.com/Masterminds/squirrel"
    13  	"github.com/mattermost/gorp"
    14  	"github.com/pkg/errors"
    15  
    16  	"github.com/mattermost/mattermost-server/v5/model"
    17  	"github.com/mattermost/mattermost-server/v5/store"
    18  	"github.com/mattermost/mattermost-server/v5/utils"
    19  )
    20  
    21  const (
    22  	TeamMemberExistsError = "store.sql_team.save_member.exists.app_error"
    23  )
    24  
    25  type SqlTeamStore struct {
    26  	*SqlStore
    27  
    28  	teamsQuery sq.SelectBuilder
    29  }
    30  
    31  type teamMember struct {
    32  	TeamId      string
    33  	UserId      string
    34  	Roles       string
    35  	DeleteAt    int64
    36  	SchemeUser  sql.NullBool
    37  	SchemeAdmin sql.NullBool
    38  	SchemeGuest sql.NullBool
    39  }
    40  
    41  func NewTeamMemberFromModel(tm *model.TeamMember) *teamMember {
    42  	return &teamMember{
    43  		TeamId:      tm.TeamId,
    44  		UserId:      tm.UserId,
    45  		Roles:       tm.ExplicitRoles,
    46  		DeleteAt:    tm.DeleteAt,
    47  		SchemeGuest: sql.NullBool{Valid: true, Bool: tm.SchemeGuest},
    48  		SchemeUser:  sql.NullBool{Valid: true, Bool: tm.SchemeUser},
    49  		SchemeAdmin: sql.NullBool{Valid: true, Bool: tm.SchemeAdmin},
    50  	}
    51  }
    52  
    53  type teamMemberWithSchemeRoles struct {
    54  	TeamId                     string
    55  	UserId                     string
    56  	Roles                      string
    57  	DeleteAt                   int64
    58  	SchemeGuest                sql.NullBool
    59  	SchemeUser                 sql.NullBool
    60  	SchemeAdmin                sql.NullBool
    61  	TeamSchemeDefaultGuestRole sql.NullString
    62  	TeamSchemeDefaultUserRole  sql.NullString
    63  	TeamSchemeDefaultAdminRole sql.NullString
    64  }
    65  
    66  type teamMemberWithSchemeRolesList []teamMemberWithSchemeRoles
    67  
    68  func teamMemberSliceColumns() []string {
    69  	return []string{"TeamId", "UserId", "Roles", "DeleteAt", "SchemeUser", "SchemeAdmin", "SchemeGuest"}
    70  }
    71  
    72  func teamMemberToSlice(member *model.TeamMember) []interface{} {
    73  	resultSlice := []interface{}{}
    74  	resultSlice = append(resultSlice, member.TeamId)
    75  	resultSlice = append(resultSlice, member.UserId)
    76  	resultSlice = append(resultSlice, member.ExplicitRoles)
    77  	resultSlice = append(resultSlice, member.DeleteAt)
    78  	resultSlice = append(resultSlice, member.SchemeUser)
    79  	resultSlice = append(resultSlice, member.SchemeAdmin)
    80  	resultSlice = append(resultSlice, member.SchemeGuest)
    81  	return resultSlice
    82  }
    83  
    84  func wildcardSearchTerm(term string) string {
    85  	return strings.ToLower("%" + term + "%")
    86  }
    87  
    88  type rolesInfo struct {
    89  	roles         []string
    90  	explicitRoles []string
    91  	schemeGuest   bool
    92  	schemeUser    bool
    93  	schemeAdmin   bool
    94  }
    95  
    96  func getTeamRoles(schemeGuest, schemeUser, schemeAdmin bool, defaultTeamGuestRole, defaultTeamUserRole, defaultTeamAdminRole string, roles []string) rolesInfo {
    97  	result := rolesInfo{
    98  		roles:         []string{},
    99  		explicitRoles: []string{},
   100  		schemeGuest:   schemeGuest,
   101  		schemeUser:    schemeUser,
   102  		schemeAdmin:   schemeAdmin,
   103  	}
   104  	// Identify any scheme derived roles that are in "Roles" field due to not yet being migrated, and exclude
   105  	// them from ExplicitRoles field.
   106  	for _, role := range roles {
   107  		switch role {
   108  		case model.TEAM_GUEST_ROLE_ID:
   109  			result.schemeGuest = true
   110  		case model.TEAM_USER_ROLE_ID:
   111  			result.schemeUser = true
   112  		case model.TEAM_ADMIN_ROLE_ID:
   113  			result.schemeAdmin = true
   114  		default:
   115  			result.explicitRoles = append(result.explicitRoles, role)
   116  			result.roles = append(result.roles, role)
   117  		}
   118  	}
   119  
   120  	// Add any scheme derived roles that are not in the Roles field due to being Implicit from the Scheme, and add
   121  	// them to the Roles field for backwards compatibility reasons.
   122  	var schemeImpliedRoles []string
   123  	if result.schemeGuest {
   124  		if defaultTeamGuestRole != "" {
   125  			schemeImpliedRoles = append(schemeImpliedRoles, defaultTeamGuestRole)
   126  		} else {
   127  			schemeImpliedRoles = append(schemeImpliedRoles, model.TEAM_GUEST_ROLE_ID)
   128  		}
   129  	}
   130  	if result.schemeUser {
   131  		if defaultTeamUserRole != "" {
   132  			schemeImpliedRoles = append(schemeImpliedRoles, defaultTeamUserRole)
   133  		} else {
   134  			schemeImpliedRoles = append(schemeImpliedRoles, model.TEAM_USER_ROLE_ID)
   135  		}
   136  	}
   137  	if result.schemeAdmin {
   138  		if defaultTeamAdminRole != "" {
   139  			schemeImpliedRoles = append(schemeImpliedRoles, defaultTeamAdminRole)
   140  		} else {
   141  			schemeImpliedRoles = append(schemeImpliedRoles, model.TEAM_ADMIN_ROLE_ID)
   142  		}
   143  	}
   144  	for _, impliedRole := range schemeImpliedRoles {
   145  		alreadyThere := false
   146  		for _, role := range result.roles {
   147  			if role == impliedRole {
   148  				alreadyThere = true
   149  			}
   150  		}
   151  		if !alreadyThere {
   152  			result.roles = append(result.roles, impliedRole)
   153  		}
   154  	}
   155  	return result
   156  }
   157  
   158  func (db teamMemberWithSchemeRoles) ToModel() *model.TeamMember {
   159  	// Identify any scheme derived roles that are in "Roles" field due to not yet being migrated, and exclude
   160  	// them from ExplicitRoles field.
   161  	schemeGuest := db.SchemeGuest.Valid && db.SchemeGuest.Bool
   162  	schemeUser := db.SchemeUser.Valid && db.SchemeUser.Bool
   163  	schemeAdmin := db.SchemeAdmin.Valid && db.SchemeAdmin.Bool
   164  
   165  	defaultTeamGuestRole := ""
   166  	if db.TeamSchemeDefaultGuestRole.Valid {
   167  		defaultTeamGuestRole = db.TeamSchemeDefaultGuestRole.String
   168  	}
   169  
   170  	defaultTeamUserRole := ""
   171  	if db.TeamSchemeDefaultUserRole.Valid {
   172  		defaultTeamUserRole = db.TeamSchemeDefaultUserRole.String
   173  	}
   174  
   175  	defaultTeamAdminRole := ""
   176  	if db.TeamSchemeDefaultAdminRole.Valid {
   177  		defaultTeamAdminRole = db.TeamSchemeDefaultAdminRole.String
   178  	}
   179  
   180  	rolesResult := getTeamRoles(schemeGuest, schemeUser, schemeAdmin, defaultTeamGuestRole, defaultTeamUserRole, defaultTeamAdminRole, strings.Fields(db.Roles))
   181  
   182  	tm := &model.TeamMember{
   183  		TeamId:        db.TeamId,
   184  		UserId:        db.UserId,
   185  		Roles:         strings.Join(rolesResult.roles, " "),
   186  		DeleteAt:      db.DeleteAt,
   187  		SchemeGuest:   rolesResult.schemeGuest,
   188  		SchemeUser:    rolesResult.schemeUser,
   189  		SchemeAdmin:   rolesResult.schemeAdmin,
   190  		ExplicitRoles: strings.Join(rolesResult.explicitRoles, " "),
   191  	}
   192  	return tm
   193  }
   194  
   195  func (db teamMemberWithSchemeRolesList) ToModel() []*model.TeamMember {
   196  	tms := make([]*model.TeamMember, 0)
   197  
   198  	for _, tm := range db {
   199  		tms = append(tms, tm.ToModel())
   200  	}
   201  
   202  	return tms
   203  }
   204  
   205  func newSqlTeamStore(sqlStore *SqlStore) store.TeamStore {
   206  	s := &SqlTeamStore{
   207  		SqlStore: sqlStore,
   208  	}
   209  
   210  	s.teamsQuery = s.getQueryBuilder().
   211  		Select("Teams.*").
   212  		From("Teams")
   213  
   214  	for _, db := range sqlStore.GetAllConns() {
   215  		table := db.AddTableWithName(model.Team{}, "Teams").SetKeys(false, "Id")
   216  		table.ColMap("Id").SetMaxSize(26)
   217  		table.ColMap("DisplayName").SetMaxSize(64)
   218  		table.ColMap("Name").SetMaxSize(64).SetUnique(true)
   219  		table.ColMap("Description").SetMaxSize(255)
   220  		table.ColMap("Type").SetMaxSize(255)
   221  		table.ColMap("Email").SetMaxSize(128)
   222  		table.ColMap("CompanyName").SetMaxSize(64)
   223  		table.ColMap("AllowedDomains").SetMaxSize(1000)
   224  		table.ColMap("InviteId").SetMaxSize(32)
   225  		table.ColMap("SchemeId").SetMaxSize(26)
   226  
   227  		tablem := db.AddTableWithName(teamMember{}, "TeamMembers").SetKeys(false, "TeamId", "UserId")
   228  		tablem.ColMap("TeamId").SetMaxSize(26)
   229  		tablem.ColMap("UserId").SetMaxSize(26)
   230  		tablem.ColMap("Roles").SetMaxSize(64)
   231  	}
   232  
   233  	return s
   234  }
   235  
   236  func (s SqlTeamStore) createIndexesIfNotExists() {
   237  	s.CreateIndexIfNotExists("idx_teams_name", "Teams", "Name")
   238  	s.RemoveIndexIfExists("idx_teams_description", "Teams")
   239  	s.CreateIndexIfNotExists("idx_teams_invite_id", "Teams", "InviteId")
   240  	s.CreateIndexIfNotExists("idx_teams_update_at", "Teams", "UpdateAt")
   241  	s.CreateIndexIfNotExists("idx_teams_create_at", "Teams", "CreateAt")
   242  	s.CreateIndexIfNotExists("idx_teams_delete_at", "Teams", "DeleteAt")
   243  	s.CreateIndexIfNotExists("idx_teams_scheme_id", "Teams", "SchemeId")
   244  
   245  	s.CreateIndexIfNotExists("idx_teammembers_team_id", "TeamMembers", "TeamId")
   246  	s.CreateIndexIfNotExists("idx_teammembers_user_id", "TeamMembers", "UserId")
   247  	s.CreateIndexIfNotExists("idx_teammembers_delete_at", "TeamMembers", "DeleteAt")
   248  }
   249  
   250  // Save adds the team to the database if a team with the same name does not already
   251  // exist in the database. It returns the team added if the operation is successful.
   252  func (s SqlTeamStore) Save(team *model.Team) (*model.Team, error) {
   253  	if team.Id != "" {
   254  		return nil, store.NewErrInvalidInput("Team", "id", team.Id)
   255  	}
   256  
   257  	team.PreSave()
   258  
   259  	if err := team.IsValid(); err != nil {
   260  		return nil, err
   261  	}
   262  
   263  	if err := s.GetMaster().Insert(team); err != nil {
   264  		if IsUniqueConstraintError(err, []string{"Name", "teams_name_key"}) {
   265  			return nil, store.NewErrInvalidInput("Team", "id", team.Id)
   266  		}
   267  		return nil, errors.Wrapf(err, "failed to save Team with id=%s", team.Id)
   268  	}
   269  	return team, nil
   270  }
   271  
   272  // Update updates the details of the team passed as the parameter using the team Id
   273  // if the team exists in the database.
   274  // It returns the updated team if the operation is successful.
   275  func (s SqlTeamStore) Update(team *model.Team) (*model.Team, error) {
   276  
   277  	team.PreUpdate()
   278  
   279  	if err := team.IsValid(); err != nil {
   280  		return nil, err
   281  	}
   282  
   283  	oldResult, err := s.GetMaster().Get(model.Team{}, team.Id)
   284  	if err != nil {
   285  		return nil, errors.Wrapf(err, "failed to get Team with id=%s", team.Id)
   286  
   287  	}
   288  
   289  	if oldResult == nil {
   290  		return nil, store.NewErrInvalidInput("Team", "id", team.Id)
   291  	}
   292  
   293  	oldTeam := oldResult.(*model.Team)
   294  	team.CreateAt = oldTeam.CreateAt
   295  	team.UpdateAt = model.GetMillis()
   296  
   297  	count, err := s.GetMaster().Update(team)
   298  	if err != nil {
   299  		return nil, errors.Wrapf(err, "failed to update Team with id=%s", team.Id)
   300  	}
   301  	if count > 1 {
   302  		return nil, errors.Wrapf(err, "multiple Teams updated with id=%s", team.Id)
   303  	}
   304  
   305  	return team, nil
   306  }
   307  
   308  // Get returns from the database the team that matches the id provided as parameter.
   309  // If the team doesn't exist it returns a model.AppError with a
   310  // http.StatusNotFound in the StatusCode field.
   311  func (s SqlTeamStore) Get(id string) (*model.Team, error) {
   312  	obj, err := s.GetReplica().Get(model.Team{}, id)
   313  	if err != nil {
   314  		return nil, errors.Wrapf(err, "failed to get Team with id=%s", id)
   315  	}
   316  	if obj == nil {
   317  		return nil, store.NewErrNotFound("Team", id)
   318  	}
   319  
   320  	return obj.(*model.Team), nil
   321  }
   322  
   323  // GetByInviteId returns from the database the team that matches the inviteId provided as parameter.
   324  // If the parameter provided is empty or if there is no match in the database, it returns a model.AppError
   325  // with a http.StatusNotFound in the StatusCode field.
   326  func (s SqlTeamStore) GetByInviteId(inviteId string) (*model.Team, error) {
   327  	team := model.Team{}
   328  
   329  	query, args, err := s.teamsQuery.Where(sq.Eq{"InviteId": inviteId}).ToSql()
   330  	if err != nil {
   331  		return nil, errors.Wrap(err, "team_tosql")
   332  	}
   333  	err = s.GetReplica().SelectOne(&team, query, args...)
   334  	if err != nil {
   335  		return nil, store.NewErrNotFound("Team", fmt.Sprintf("inviteId=%s", inviteId))
   336  	}
   337  
   338  	if inviteId == "" || team.InviteId != inviteId {
   339  		return nil, store.NewErrNotFound("Team", fmt.Sprintf("inviteId=%s", inviteId))
   340  	}
   341  	return &team, nil
   342  }
   343  
   344  // GetByName returns from the database the team that matches the name provided as parameter.
   345  // If there is no match in the database, it returns a model.AppError with a
   346  // http.StatusNotFound in the StatusCode field.
   347  func (s SqlTeamStore) GetByName(name string) (*model.Team, error) {
   348  
   349  	team := model.Team{}
   350  	query, args, err := s.teamsQuery.Where(sq.Eq{"Name": name}).ToSql()
   351  	if err != nil {
   352  		return nil, errors.Wrap(err, "team_tosql")
   353  	}
   354  	err = s.GetReplica().SelectOne(&team, query, args...)
   355  	if err != nil {
   356  		if err == sql.ErrNoRows {
   357  			return nil, store.NewErrNotFound("Team", fmt.Sprintf("name=%s", name))
   358  		}
   359  		return nil, errors.Wrapf(err, "failed to find Team with name=%s", name)
   360  	}
   361  	return &team, nil
   362  }
   363  
   364  func (s SqlTeamStore) GetByNames(names []string) ([]*model.Team, error) {
   365  	uniqueNames := utils.RemoveDuplicatesFromStringArray(names)
   366  
   367  	query, args, err := s.teamsQuery.Where(sq.Eq{"Name": uniqueNames}).ToSql()
   368  
   369  	if err != nil {
   370  		return nil, errors.Wrap(err, "team_tosql")
   371  	}
   372  
   373  	teams := []*model.Team{}
   374  	_, err = s.GetReplica().Select(&teams, query, args...)
   375  	if err != nil {
   376  		if err == sql.ErrNoRows {
   377  			return nil, store.NewErrNotFound("Team", fmt.Sprintf("nameIn=%v", names))
   378  		}
   379  		return nil, errors.Wrap(err, "failed to find Teams")
   380  	}
   381  	if len(teams) != len(uniqueNames) {
   382  		return nil, store.NewErrNotFound("Team", fmt.Sprintf("nameIn=%v", names))
   383  	}
   384  	return teams, nil
   385  }
   386  
   387  func (s SqlTeamStore) teamSearchQuery(term string, opts *model.TeamSearch, countQuery bool) sq.SelectBuilder {
   388  	var selectStr string
   389  	if countQuery {
   390  		selectStr = "count(*)"
   391  	} else {
   392  		selectStr = "*"
   393  	}
   394  
   395  	query := s.getQueryBuilder().
   396  		Select(selectStr).
   397  		From("Teams as t")
   398  
   399  	// Don't order or limit if getting count
   400  	if !countQuery {
   401  		query = query.OrderBy("t.DisplayName")
   402  
   403  		if opts.IsPaginated() {
   404  			query = query.Limit(uint64(*opts.PerPage)).Offset(uint64(*opts.Page * *opts.PerPage))
   405  		}
   406  	}
   407  
   408  	if term != "" {
   409  		term = sanitizeSearchTerm(term, "\\")
   410  		term = wildcardSearchTerm(term)
   411  
   412  		operatorKeyword := "ILIKE"
   413  		if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   414  			operatorKeyword = "LIKE"
   415  		}
   416  
   417  		query = query.Where(fmt.Sprintf("(Name %[1]s ? OR DisplayName %[1]s ?)", operatorKeyword), term, term)
   418  	}
   419  
   420  	var teamFilters sq.Sqlizer
   421  	var openInviteFilter sq.Sqlizer
   422  	if opts.AllowOpenInvite != nil {
   423  		if *opts.AllowOpenInvite {
   424  			openInviteFilter = sq.Eq{"AllowOpenInvite": true}
   425  		} else {
   426  			openInviteFilter = sq.And{
   427  				sq.Or{
   428  					sq.NotEq{"AllowOpenInvite": true},
   429  					sq.Eq{"AllowOpenInvite": nil},
   430  				},
   431  				sq.Or{
   432  					sq.NotEq{"GroupConstrained": true},
   433  					sq.Eq{"GroupConstrained": nil},
   434  				},
   435  			}
   436  		}
   437  
   438  		teamFilters = openInviteFilter
   439  	}
   440  
   441  	var groupConstrainedFilter sq.Sqlizer
   442  	if opts.GroupConstrained != nil {
   443  		if *opts.GroupConstrained {
   444  			groupConstrainedFilter = sq.Eq{"GroupConstrained": true}
   445  		} else {
   446  			groupConstrainedFilter = sq.Or{
   447  				sq.NotEq{"GroupConstrained": true},
   448  				sq.Eq{"GroupConstrained": nil},
   449  			}
   450  		}
   451  
   452  		if teamFilters == nil {
   453  			teamFilters = groupConstrainedFilter
   454  		} else {
   455  			teamFilters = sq.Or{teamFilters, groupConstrainedFilter}
   456  		}
   457  	}
   458  
   459  	query = query.Where(teamFilters)
   460  
   461  	return query
   462  }
   463  
   464  // SearchAll returns from the database a list of teams that match the Name or DisplayName
   465  // passed as the term search parameter.
   466  func (s SqlTeamStore) SearchAll(term string, opts *model.TeamSearch) ([]*model.Team, error) {
   467  	var teams []*model.Team
   468  
   469  	queryString, args, err := s.teamSearchQuery(term, opts, false).ToSql()
   470  	if err != nil {
   471  		return nil, errors.Wrap(err, "team_tosql")
   472  	}
   473  
   474  	if _, err = s.GetReplica().Select(&teams, queryString, args...); err != nil {
   475  		return nil, errors.Wrapf(err, "failed to find Teams with term=%s", term)
   476  	}
   477  
   478  	return teams, nil
   479  }
   480  
   481  // SearchAllPaged returns a teams list and the total count of teams that matched the search.
   482  func (s SqlTeamStore) SearchAllPaged(term string, opts *model.TeamSearch) ([]*model.Team, int64, error) {
   483  	var teams []*model.Team
   484  	var totalCount int64
   485  
   486  	queryString, args, err := s.teamSearchQuery(term, opts, false).ToSql()
   487  	if err != nil {
   488  		return nil, 0, errors.Wrap(err, "team_tosql")
   489  	}
   490  	if _, err = s.GetReplica().Select(&teams, queryString, args...); err != nil {
   491  		return nil, 0, errors.Wrapf(err, "failed to find Teams with term=%s", term)
   492  	}
   493  
   494  	queryString, args, err = s.teamSearchQuery(term, opts, true).ToSql()
   495  	if err != nil {
   496  		return nil, 0, errors.Wrap(err, "team_tosql")
   497  	}
   498  	totalCount, err = s.GetReplica().SelectInt(queryString, args...)
   499  	if err != nil {
   500  		return nil, 0, errors.Wrapf(err, "failed to count Teams with term=%s", term)
   501  	}
   502  
   503  	return teams, totalCount, nil
   504  }
   505  
   506  // SearchOpen returns from the database a list of public teams that match the Name or DisplayName
   507  // passed as the term search parameter.
   508  func (s SqlTeamStore) SearchOpen(term string) ([]*model.Team, error) {
   509  	var teams []*model.Team
   510  
   511  	term = sanitizeSearchTerm(term, "\\")
   512  	term = wildcardSearchTerm(term)
   513  	query := s.teamsQuery.Where(sq.Eq{"Type": "O", "AllowOpenInvite": true})
   514  	if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   515  		query = query.Where(sq.Or{sq.Like{"Name": term}, sq.Like{"DisplayName": term}})
   516  	} else {
   517  		query = query.Where(sq.Or{sq.ILike{"Name": term}, sq.ILike{"DisplayName": term}})
   518  	}
   519  
   520  	queryString, args, err := query.ToSql()
   521  	if err != nil {
   522  		return nil, errors.Wrap(err, "team_tosql")
   523  	}
   524  
   525  	if _, err = s.GetReplica().Select(&teams, queryString, args...); err != nil {
   526  		return nil, errors.Wrapf(err, "failed to count Teams with term=%s", term)
   527  	}
   528  
   529  	return teams, nil
   530  }
   531  
   532  // SearchPrivate returns from the database a list of private teams that match the Name or DisplayName
   533  // passed as the term search parameter.
   534  func (s SqlTeamStore) SearchPrivate(term string) ([]*model.Team, error) {
   535  	var teams []*model.Team
   536  
   537  	term = sanitizeSearchTerm(term, "\\")
   538  	term = wildcardSearchTerm(term)
   539  	query := s.teamsQuery.Where(sq.Eq{"Type": "O", "AllowOpenInvite": false})
   540  	if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   541  		query = query.Where(sq.Or{sq.Like{"Name": term}, sq.Like{"DisplayName": term}})
   542  	} else {
   543  		query = query.Where(sq.Or{sq.ILike{"Name": term}, sq.ILike{"DisplayName": term}})
   544  	}
   545  
   546  	queryString, args, err := query.ToSql()
   547  	if err != nil {
   548  		return nil, errors.Wrap(err, "team_tosql")
   549  	}
   550  
   551  	if _, err = s.GetReplica().Select(&teams, queryString, args...); err != nil {
   552  		return nil, errors.Wrapf(err, "failed to count Teams with term=%s", term)
   553  	}
   554  	return teams, nil
   555  }
   556  
   557  // GetAll returns all teams
   558  func (s SqlTeamStore) GetAll() ([]*model.Team, error) {
   559  	var teams []*model.Team
   560  
   561  	query, args, err := s.teamsQuery.OrderBy("DisplayName").ToSql()
   562  
   563  	if err != nil {
   564  		return nil, errors.Wrap(err, "team_tosql")
   565  	}
   566  
   567  	_, err = s.GetReplica().Select(&teams, query, args...)
   568  	if err != nil {
   569  		return nil, errors.Wrap(err, "failed to find Teams")
   570  	}
   571  	return teams, nil
   572  }
   573  
   574  // GetAllPage returns teams, up to a total limit passed as parameter and paginated by offset number passed as parameter.
   575  func (s SqlTeamStore) GetAllPage(offset int, limit int) ([]*model.Team, error) {
   576  	var teams []*model.Team
   577  
   578  	query, args, err := s.teamsQuery.
   579  		OrderBy("DisplayName").
   580  		Limit(uint64(limit)).
   581  		Offset(uint64(offset)).ToSql()
   582  
   583  	if err != nil {
   584  		return nil, errors.Wrap(err, "team_tosql")
   585  	}
   586  	if _, err = s.GetReplica().Select(&teams, query, args...); err != nil {
   587  		return nil, errors.Wrap(err, "failed to find Teams")
   588  	}
   589  
   590  	return teams, nil
   591  }
   592  
   593  // GetTeamsByUserId returns from the database all teams that userId belongs to.
   594  func (s SqlTeamStore) GetTeamsByUserId(userId string) ([]*model.Team, error) {
   595  	var teams []*model.Team
   596  	query, args, err := s.teamsQuery.
   597  		Join("TeamMembers ON TeamMembers.TeamId = Teams.Id").
   598  		Where(sq.Eq{"TeamMembers.UserId": userId, "TeamMembers.DeleteAt": 0, "Teams.DeleteAt": 0}).ToSql()
   599  
   600  	if err != nil {
   601  		return nil, errors.Wrap(err, "team_tosql")
   602  	}
   603  
   604  	if _, err = s.GetReplica().Select(&teams, query, args...); err != nil {
   605  		return nil, errors.Wrap(err, "failed to find Teams")
   606  	}
   607  
   608  	return teams, nil
   609  }
   610  
   611  // GetAllPrivateTeamListing returns all private teams.
   612  func (s SqlTeamStore) GetAllPrivateTeamListing() ([]*model.Team, error) {
   613  	query, args, err := s.teamsQuery.Where(sq.Eq{"AllowOpenInvite": false}).
   614  		OrderBy("DisplayName").ToSql()
   615  	if err != nil {
   616  		return nil, errors.Wrap(err, "team_tosql")
   617  	}
   618  	var data []*model.Team
   619  	if _, err = s.GetReplica().Select(&data, query, args...); err != nil {
   620  		return nil, errors.Wrap(err, "failed to find Teams")
   621  	}
   622  	return data, nil
   623  }
   624  
   625  // GetAllPublicTeamPageListing returns public teams, up to a total limit passed as parameter and paginated by offset number passed as parameter.
   626  func (s SqlTeamStore) GetAllPublicTeamPageListing(offset int, limit int) ([]*model.Team, error) {
   627  	query, args, err := s.teamsQuery.Where(sq.Eq{"AllowOpenInvite": true}).
   628  		OrderBy("DisplayName").
   629  		Limit(uint64(limit)).
   630  		Offset(uint64(offset)).ToSql()
   631  	if err != nil {
   632  		return nil, errors.Wrap(err, "team_tosql")
   633  	}
   634  
   635  	var data []*model.Team
   636  	if _, err = s.GetReplica().Select(&data, query, args...); err != nil {
   637  		return nil, errors.Wrap(err, "failed to find Teams")
   638  	}
   639  
   640  	return data, nil
   641  }
   642  
   643  // GetAllPrivateTeamPageListing returns private teams, up to a total limit passed as paramater and paginated by offset number passed as parameter.
   644  func (s SqlTeamStore) GetAllPrivateTeamPageListing(offset int, limit int) ([]*model.Team, error) {
   645  	query, args, err := s.teamsQuery.Where(sq.Eq{"AllowOpenInvite": false}).
   646  		OrderBy("DisplayName").
   647  		Limit(uint64(limit)).
   648  		Offset(uint64(offset)).ToSql()
   649  
   650  	if err != nil {
   651  		return nil, errors.Wrap(err, "team_tosql")
   652  	}
   653  
   654  	var data []*model.Team
   655  	if _, err = s.GetReplica().Select(&data, query, args...); err != nil {
   656  		return nil, errors.Wrap(err, "failed to find Teams")
   657  	}
   658  
   659  	return data, nil
   660  }
   661  
   662  // GetAllTeamListing returns all public teams.
   663  func (s SqlTeamStore) GetAllTeamListing() ([]*model.Team, error) {
   664  	query, args, err := s.teamsQuery.Where(sq.Eq{"AllowOpenInvite": true}).
   665  		OrderBy("DisplayName").ToSql()
   666  
   667  	if err != nil {
   668  		return nil, errors.Wrap(err, "team_tosql")
   669  	}
   670  
   671  	var data []*model.Team
   672  	if _, err = s.GetReplica().Select(&data, query, args...); err != nil {
   673  		return nil, errors.Wrap(err, "failed to find Teams")
   674  	}
   675  
   676  	return data, nil
   677  }
   678  
   679  // GetAllTeamPageListing returns public teams, up to a total limit passed as parameter and paginated by offset number passed as parameter.
   680  func (s SqlTeamStore) GetAllTeamPageListing(offset int, limit int) ([]*model.Team, error) {
   681  	query, args, err := s.teamsQuery.Where(sq.Eq{"AllowOpenInvite": true}).
   682  		OrderBy("DisplayName").
   683  		Limit(uint64(limit)).
   684  		Offset(uint64(offset)).ToSql()
   685  
   686  	if err != nil {
   687  		return nil, errors.Wrap(err, "team_tosql")
   688  	}
   689  
   690  	var teams []*model.Team
   691  	if _, err = s.GetReplica().Select(&teams, query, args...); err != nil {
   692  		return nil, errors.Wrap(err, "failed to find Teams")
   693  	}
   694  
   695  	return teams, nil
   696  }
   697  
   698  // PermanentDelete permanently deletes from the database the team entry that matches the teamId passed as parameter.
   699  // To soft-delete the team you can Update it with the DeleteAt field set to the current millisecond using model.GetMillis()
   700  func (s SqlTeamStore) PermanentDelete(teamId string) error {
   701  	sql, args, err := s.getQueryBuilder().
   702  		Delete("Teams").
   703  		Where(sq.Eq{"Id": teamId}).ToSql()
   704  	if err != nil {
   705  		return errors.Wrap(err, "team_tosql")
   706  	}
   707  	if _, err = s.GetMaster().Exec(sql, args...); err != nil {
   708  		return errors.Wrapf(err, "failed to delete Team with id=%s", teamId)
   709  	}
   710  	return nil
   711  }
   712  
   713  // AnalyticsPublicTeamCount returns the number of active public teams.
   714  func (s SqlTeamStore) AnalyticsPublicTeamCount() (int64, error) {
   715  	query, args, err := s.getQueryBuilder().
   716  		Select("COUNT(*) FROM Teams").
   717  		Where(sq.Eq{"DeleteAt": 0, "AllowOpenInvite": true}).ToSql()
   718  
   719  	if err != nil {
   720  		return 0, errors.Wrap(err, "team_tosql")
   721  	}
   722  
   723  	c, err := s.GetReplica().SelectInt(query, args...)
   724  
   725  	if err != nil {
   726  		return int64(0), errors.Wrap(err, "failed to count Teams")
   727  	}
   728  
   729  	return c, nil
   730  }
   731  
   732  // AnalyticsPrivateTeamCount returns the number of active private teams.
   733  func (s SqlTeamStore) AnalyticsPrivateTeamCount() (int64, error) {
   734  	query, args, err := s.getQueryBuilder().
   735  		Select("COUNT(*) FROM Teams").
   736  		Where(sq.Eq{"DeleteAt": 0, "AllowOpenInvite": false}).ToSql()
   737  
   738  	if err != nil {
   739  		return 0, errors.Wrap(err, "team_tosql")
   740  	}
   741  	c, err := s.GetReplica().SelectInt(query, args...)
   742  
   743  	if err != nil {
   744  		return int64(0), errors.Wrap(err, "failed to count Teams")
   745  	}
   746  
   747  	return c, nil
   748  }
   749  
   750  // AnalyticsTeamCount returns the total number of teams including deleted teams if parameter passed is set to 'true'.
   751  func (s SqlTeamStore) AnalyticsTeamCount(includeDeleted bool) (int64, error) {
   752  	query := s.getQueryBuilder().Select("COUNT(*) FROM Teams")
   753  	if !includeDeleted {
   754  		query = query.Where(sq.Eq{"DeleteAt": 0})
   755  	}
   756  
   757  	queryString, args, err := query.ToSql()
   758  	if err != nil {
   759  		return 0, errors.Wrap(err, "team_tosql")
   760  	}
   761  
   762  	c, err := s.GetReplica().SelectInt(queryString, args...)
   763  
   764  	if err != nil {
   765  		return int64(0), errors.Wrap(err, "failed to count Teams")
   766  	}
   767  
   768  	return c, nil
   769  }
   770  
   771  func (s SqlTeamStore) getTeamMembersWithSchemeSelectQuery() sq.SelectBuilder {
   772  	return s.getQueryBuilder().
   773  		Select(
   774  			"TeamMembers.*",
   775  			"TeamScheme.DefaultTeamGuestRole TeamSchemeDefaultGuestRole",
   776  			"TeamScheme.DefaultTeamUserRole TeamSchemeDefaultUserRole",
   777  			"TeamScheme.DefaultTeamAdminRole TeamSchemeDefaultAdminRole",
   778  		).
   779  		From("TeamMembers").
   780  		LeftJoin("Teams ON TeamMembers.TeamId = Teams.Id").
   781  		LeftJoin("Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id")
   782  }
   783  
   784  func (s SqlTeamStore) SaveMultipleMembers(members []*model.TeamMember, maxUsersPerTeam int) ([]*model.TeamMember, error) {
   785  	newTeamMembers := map[string]int{}
   786  	users := map[string]bool{}
   787  	for _, member := range members {
   788  		newTeamMembers[member.TeamId] = 0
   789  	}
   790  
   791  	for _, member := range members {
   792  		newTeamMembers[member.TeamId]++
   793  		users[member.UserId] = true
   794  
   795  		if err := member.IsValid(); err != nil {
   796  			return nil, err
   797  		}
   798  	}
   799  
   800  	teams := []string{}
   801  	for team := range newTeamMembers {
   802  		teams = append(teams, team)
   803  	}
   804  
   805  	defaultTeamRolesByTeam := map[string]struct {
   806  		Id    string
   807  		Guest sql.NullString
   808  		User  sql.NullString
   809  		Admin sql.NullString
   810  	}{}
   811  
   812  	queryRoles := s.getQueryBuilder().
   813  		Select(
   814  			"Teams.Id as Id",
   815  			"TeamScheme.DefaultTeamGuestRole as Guest",
   816  			"TeamScheme.DefaultTeamUserRole as User",
   817  			"TeamScheme.DefaultTeamAdminRole as Admin",
   818  		).
   819  		From("Teams").
   820  		LeftJoin("Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id").
   821  		Where(sq.Eq{"Teams.Id": teams})
   822  
   823  	sqlRolesQuery, argsRoles, err := queryRoles.ToSql()
   824  	if err != nil {
   825  		return nil, errors.Wrap(err, "team_roles_tosql")
   826  	}
   827  	var defaultTeamsRoles []struct {
   828  		Id    string
   829  		Guest sql.NullString
   830  		User  sql.NullString
   831  		Admin sql.NullString
   832  	}
   833  	_, err = s.GetMaster().Select(&defaultTeamsRoles, sqlRolesQuery, argsRoles...)
   834  	if err != nil {
   835  		return nil, errors.Wrap(err, "default_team_roles_select")
   836  	}
   837  
   838  	for _, defaultRoles := range defaultTeamsRoles {
   839  		defaultTeamRolesByTeam[defaultRoles.Id] = defaultRoles
   840  	}
   841  
   842  	if maxUsersPerTeam >= 0 {
   843  		queryCount := s.getQueryBuilder().
   844  			Select(
   845  				"COUNT(0) as Count, TeamMembers.TeamId as TeamId",
   846  			).
   847  			From("TeamMembers").
   848  			Join("Users ON TeamMembers.UserId = Users.Id").
   849  			Where(sq.Eq{"TeamMembers.TeamId": teams}).
   850  			Where(sq.Eq{"TeamMembers.DeleteAt": 0}).
   851  			Where(sq.Eq{"Users.DeleteAt": 0}).
   852  			GroupBy("TeamMembers.TeamId")
   853  
   854  		sqlCountQuery, argsCount, errCount := queryCount.ToSql()
   855  		if errCount != nil {
   856  			return nil, errors.Wrap(err, "member_count_tosql")
   857  		}
   858  
   859  		var counters []struct {
   860  			Count  int    `db:"Count"`
   861  			TeamId string `db:"TeamId"`
   862  		}
   863  
   864  		_, err = s.GetMaster().Select(&counters, sqlCountQuery, argsCount...)
   865  		if err != nil {
   866  			return nil, errors.Wrap(err, "failed to count users in the teams of the memberships")
   867  		}
   868  
   869  		for teamId, newMembers := range newTeamMembers {
   870  			existingMembers := 0
   871  			for _, counter := range counters {
   872  				if counter.TeamId == teamId {
   873  					existingMembers = counter.Count
   874  				}
   875  			}
   876  			if existingMembers+newMembers > maxUsersPerTeam {
   877  				return nil, store.NewErrLimitExceeded("TeamMember", existingMembers+newMembers, "team members limit exceeded")
   878  			}
   879  		}
   880  	}
   881  
   882  	query := s.getQueryBuilder().Insert("TeamMembers").Columns(teamMemberSliceColumns()...)
   883  	for _, member := range members {
   884  		query = query.Values(teamMemberToSlice(member)...)
   885  	}
   886  
   887  	sql, args, err := query.ToSql()
   888  	if err != nil {
   889  		return nil, errors.Wrap(err, "insert_members_to_sql")
   890  	}
   891  
   892  	if _, err = s.GetMaster().Exec(sql, args...); err != nil {
   893  		if IsUniqueConstraintError(err, []string{"TeamId", "teammembers_pkey", "PRIMARY"}) {
   894  			return nil, store.NewErrConflict("TeamMember", err, "")
   895  		}
   896  		return nil, errors.Wrap(err, "unable_to_save_team_member")
   897  	}
   898  
   899  	newMembers := []*model.TeamMember{}
   900  	for _, member := range members {
   901  		s.InvalidateAllTeamIdsForUser(member.UserId)
   902  		defaultTeamGuestRole := defaultTeamRolesByTeam[member.TeamId].Guest.String
   903  		defaultTeamUserRole := defaultTeamRolesByTeam[member.TeamId].User.String
   904  		defaultTeamAdminRole := defaultTeamRolesByTeam[member.TeamId].Admin.String
   905  		rolesResult := getTeamRoles(member.SchemeGuest, member.SchemeUser, member.SchemeAdmin, defaultTeamGuestRole, defaultTeamUserRole, defaultTeamAdminRole, strings.Fields(member.ExplicitRoles))
   906  		newMember := *member
   907  		newMember.SchemeGuest = rolesResult.schemeGuest
   908  		newMember.SchemeUser = rolesResult.schemeUser
   909  		newMember.SchemeAdmin = rolesResult.schemeAdmin
   910  		newMember.Roles = strings.Join(rolesResult.roles, " ")
   911  		newMember.ExplicitRoles = strings.Join(rolesResult.explicitRoles, " ")
   912  		newMembers = append(newMembers, &newMember)
   913  	}
   914  
   915  	return newMembers, nil
   916  }
   917  
   918  func (s SqlTeamStore) SaveMember(member *model.TeamMember, maxUsersPerTeam int) (*model.TeamMember, error) {
   919  	members, err := s.SaveMultipleMembers([]*model.TeamMember{member}, maxUsersPerTeam)
   920  	if err != nil {
   921  		return nil, err
   922  	}
   923  	return members[0], nil
   924  }
   925  
   926  func (s SqlTeamStore) UpdateMultipleMembers(members []*model.TeamMember) ([]*model.TeamMember, error) {
   927  	teams := []string{}
   928  	for _, member := range members {
   929  		member.PreUpdate()
   930  
   931  		if err := member.IsValid(); err != nil {
   932  			return nil, err
   933  		}
   934  
   935  		if _, err := s.GetMaster().Update(NewTeamMemberFromModel(member)); err != nil {
   936  			return nil, errors.Wrap(err, "failed to update TeamMember")
   937  		}
   938  		teams = append(teams, member.TeamId)
   939  	}
   940  
   941  	query := s.getQueryBuilder().
   942  		Select(
   943  			"Teams.Id as Id",
   944  			"TeamScheme.DefaultTeamGuestRole as Guest",
   945  			"TeamScheme.DefaultTeamUserRole as User",
   946  			"TeamScheme.DefaultTeamAdminRole as Admin",
   947  		).
   948  		From("Teams").
   949  		LeftJoin("Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id").
   950  		Where(sq.Eq{"Teams.Id": teams})
   951  
   952  	sqlQuery, args, err := query.ToSql()
   953  	if err != nil {
   954  		return nil, errors.Wrap(err, "team_tosql")
   955  	}
   956  	var defaultTeamsRoles []struct {
   957  		Id    string
   958  		Guest sql.NullString
   959  		User  sql.NullString
   960  		Admin sql.NullString
   961  	}
   962  	_, err = s.GetMaster().Select(&defaultTeamsRoles, sqlQuery, args...)
   963  	if err != nil {
   964  		return nil, errors.Wrap(err, "failed to find Teams")
   965  	}
   966  
   967  	defaultTeamRolesByTeam := map[string]struct {
   968  		Id    string
   969  		Guest sql.NullString
   970  		User  sql.NullString
   971  		Admin sql.NullString
   972  	}{}
   973  	for _, defaultRoles := range defaultTeamsRoles {
   974  		defaultTeamRolesByTeam[defaultRoles.Id] = defaultRoles
   975  	}
   976  
   977  	updatedMembers := []*model.TeamMember{}
   978  	for _, member := range members {
   979  		s.InvalidateAllTeamIdsForUser(member.UserId)
   980  		defaultTeamGuestRole := defaultTeamRolesByTeam[member.TeamId].Guest.String
   981  		defaultTeamUserRole := defaultTeamRolesByTeam[member.TeamId].User.String
   982  		defaultTeamAdminRole := defaultTeamRolesByTeam[member.TeamId].Admin.String
   983  		rolesResult := getTeamRoles(member.SchemeGuest, member.SchemeUser, member.SchemeAdmin, defaultTeamGuestRole, defaultTeamUserRole, defaultTeamAdminRole, strings.Fields(member.ExplicitRoles))
   984  		updatedMember := *member
   985  		updatedMember.SchemeGuest = rolesResult.schemeGuest
   986  		updatedMember.SchemeUser = rolesResult.schemeUser
   987  		updatedMember.SchemeAdmin = rolesResult.schemeAdmin
   988  		updatedMember.Roles = strings.Join(rolesResult.roles, " ")
   989  		updatedMember.ExplicitRoles = strings.Join(rolesResult.explicitRoles, " ")
   990  		updatedMembers = append(updatedMembers, &updatedMember)
   991  	}
   992  
   993  	return updatedMembers, nil
   994  }
   995  
   996  func (s SqlTeamStore) UpdateMember(member *model.TeamMember) (*model.TeamMember, error) {
   997  	members, err := s.UpdateMultipleMembers([]*model.TeamMember{member})
   998  	if err != nil {
   999  		return nil, err
  1000  	}
  1001  	return members[0], nil
  1002  }
  1003  
  1004  // GetMember returns a single member of the team that matches the teamId and userId provided as parameters.
  1005  func (s SqlTeamStore) GetMember(teamId string, userId string) (*model.TeamMember, error) {
  1006  	query := s.getTeamMembersWithSchemeSelectQuery().
  1007  		Where(sq.Eq{"TeamMembers.TeamId": teamId}).
  1008  		Where(sq.Eq{"TeamMembers.UserId": userId})
  1009  
  1010  	queryString, args, err := query.ToSql()
  1011  	if err != nil {
  1012  		return nil, errors.Wrap(err, "team_tosql")
  1013  	}
  1014  
  1015  	var dbMember teamMemberWithSchemeRoles
  1016  	err = s.GetReplica().SelectOne(&dbMember, queryString, args...)
  1017  	if err != nil {
  1018  		if err == sql.ErrNoRows {
  1019  			return nil, store.NewErrNotFound("TeamMember", fmt.Sprintf("teamId=%s, userId=%s", teamId, userId))
  1020  		}
  1021  		return nil, errors.Wrapf(err, "failed to find TeamMembers with teamId=%s and userId=%s", teamId, userId)
  1022  	}
  1023  
  1024  	return dbMember.ToModel(), nil
  1025  }
  1026  
  1027  // GetMembers returns a list of members from the database that matches the teamId passed as parameter and,
  1028  // also expects teamMembersGetOptions to be passed as a parameter which allows to further filter what to show in the result.
  1029  // TeamMembersGetOptions Model has following options->
  1030  // 1. Sort through USERNAME [ if provided, which otherwise defaults to ID ]
  1031  // 2. Sort through USERNAME [ if provided, which otherwise defaults to ID ] and exclude deleted members.
  1032  // 3. Return all the members but, exclude deleted ones.
  1033  // 4. Apply ViewUsersRestrictions to restrict what is visible to the user.
  1034  func (s SqlTeamStore) GetMembers(teamId string, offset int, limit int, teamMembersGetOptions *model.TeamMembersGetOptions) ([]*model.TeamMember, error) {
  1035  	query := s.getTeamMembersWithSchemeSelectQuery().
  1036  		Where(sq.Eq{"TeamMembers.TeamId": teamId}).
  1037  		Where(sq.Eq{"TeamMembers.DeleteAt": 0}).
  1038  		Limit(uint64(limit)).
  1039  		Offset(uint64(offset))
  1040  
  1041  	if teamMembersGetOptions == nil || teamMembersGetOptions.Sort == "" {
  1042  		query = query.OrderBy("UserId")
  1043  	}
  1044  
  1045  	if teamMembersGetOptions != nil {
  1046  		if teamMembersGetOptions.Sort == model.USERNAME || teamMembersGetOptions.ExcludeDeletedUsers {
  1047  			query = query.LeftJoin("Users ON TeamMembers.UserId = Users.Id")
  1048  		}
  1049  
  1050  		if teamMembersGetOptions.ExcludeDeletedUsers {
  1051  			query = query.Where(sq.Eq{"Users.DeleteAt": 0})
  1052  		}
  1053  
  1054  		if teamMembersGetOptions.Sort == model.USERNAME {
  1055  			query = query.OrderBy(model.USERNAME)
  1056  		}
  1057  
  1058  		query = applyTeamMemberViewRestrictionsFilter(query, teamMembersGetOptions.ViewRestrictions)
  1059  	}
  1060  
  1061  	queryString, args, err := query.ToSql()
  1062  	if err != nil {
  1063  		return nil, errors.Wrap(err, "team_tosql")
  1064  	}
  1065  
  1066  	var dbMembers teamMemberWithSchemeRolesList
  1067  	_, err = s.GetReplica().Select(&dbMembers, queryString, args...)
  1068  	if err != nil {
  1069  		return nil, errors.Wrapf(err, "failed to find TeamMembers with teamId=%s", teamId)
  1070  	}
  1071  
  1072  	return dbMembers.ToModel(), nil
  1073  }
  1074  
  1075  // GetTotalMemberCount returns the number of all members in a team for the teamId passed as a parameter.
  1076  // Expects a restrictions parameter of type ViewUsersRestrictions that defines a set of Teams and Channels that are visible to the caller of the query, and applies restrictions with a filtered result.
  1077  func (s SqlTeamStore) GetTotalMemberCount(teamId string, restrictions *model.ViewUsersRestrictions) (int64, error) {
  1078  	query := s.getQueryBuilder().
  1079  		Select("count(DISTINCT TeamMembers.UserId)").
  1080  		From("TeamMembers, Users").
  1081  		Where("TeamMembers.DeleteAt = 0").
  1082  		Where("TeamMembers.UserId = Users.Id").
  1083  		Where(sq.Eq{"TeamMembers.TeamId": teamId})
  1084  
  1085  	query = applyTeamMemberViewRestrictionsFilterForStats(query, restrictions)
  1086  	queryString, args, err := query.ToSql()
  1087  	if err != nil {
  1088  		return int64(0), errors.Wrap(err, "team_tosql")
  1089  	}
  1090  
  1091  	count, err := s.GetReplica().SelectInt(queryString, args...)
  1092  	if err != nil {
  1093  		return int64(0), errors.Wrap(err, "failed to count TeamMembers")
  1094  	}
  1095  	return count, nil
  1096  }
  1097  
  1098  // GetActiveMemberCount returns the number of active members in a team for the teamId passed as a parameter i.e. members with 'DeleteAt = 0'
  1099  // Expects a restrictions parameter of type ViewUsersRestrictions that defines a set of Teams and Channels that are visible to the caller of the query, and applies restrictions with a filtered result.
  1100  func (s SqlTeamStore) GetActiveMemberCount(teamId string, restrictions *model.ViewUsersRestrictions) (int64, error) {
  1101  	query := s.getQueryBuilder().
  1102  		Select("count(DISTINCT TeamMembers.UserId)").
  1103  		From("TeamMembers, Users").
  1104  		Where("TeamMembers.DeleteAt = 0").
  1105  		Where("TeamMembers.UserId = Users.Id").
  1106  		Where("Users.DeleteAt = 0").
  1107  		Where(sq.Eq{"TeamMembers.TeamId": teamId})
  1108  
  1109  	query = applyTeamMemberViewRestrictionsFilterForStats(query, restrictions)
  1110  	queryString, args, err := query.ToSql()
  1111  	if err != nil {
  1112  		return 0, errors.Wrap(err, "team_tosql")
  1113  	}
  1114  
  1115  	count, err := s.GetReplica().SelectInt(queryString, args...)
  1116  	if err != nil {
  1117  		return 0, errors.Wrap(err, "failed to count TeamMembers")
  1118  	}
  1119  
  1120  	return count, nil
  1121  }
  1122  
  1123  // GetMembersByIds returns a list of members from the database that matches the teamId and the list of userIds passed as parameters.
  1124  // Expects a restrictions parameter of type ViewUsersRestrictions that defines a set of Teams and Channels that are visible to the caller of the query, and applies restrictions with a filtered result.
  1125  func (s SqlTeamStore) GetMembersByIds(teamId string, userIds []string, restrictions *model.ViewUsersRestrictions) ([]*model.TeamMember, error) {
  1126  	if len(userIds) == 0 {
  1127  		return nil, errors.New("invalid list of user ids")
  1128  	}
  1129  
  1130  	query := s.getTeamMembersWithSchemeSelectQuery().
  1131  		Where(sq.Eq{"TeamMembers.TeamId": teamId}).
  1132  		Where(sq.Eq{"TeamMembers.UserId": userIds}).
  1133  		Where(sq.Eq{"TeamMembers.DeleteAt": 0})
  1134  
  1135  	query = applyTeamMemberViewRestrictionsFilter(query, restrictions)
  1136  
  1137  	queryString, args, err := query.ToSql()
  1138  	if err != nil {
  1139  		return nil, errors.Wrap(err, "team_tosql")
  1140  	}
  1141  
  1142  	var dbMembers teamMemberWithSchemeRolesList
  1143  	if _, err = s.GetReplica().Select(&dbMembers, queryString, args...); err != nil {
  1144  		return nil, errors.Wrap(err, "failed to find TeamMembers")
  1145  	}
  1146  	return dbMembers.ToModel(), nil
  1147  }
  1148  
  1149  // GetTeamsForUser returns a list of teams that the user is a member of. Expects userId to be passed as a parameter.
  1150  func (s SqlTeamStore) GetTeamsForUser(ctx context.Context, userId string) ([]*model.TeamMember, error) {
  1151  	query := s.getTeamMembersWithSchemeSelectQuery().
  1152  		Where(sq.Eq{"TeamMembers.UserId": userId})
  1153  
  1154  	queryString, args, err := query.ToSql()
  1155  	if err != nil {
  1156  		return nil, errors.Wrap(err, "team_tosql")
  1157  	}
  1158  
  1159  	var dbMembers teamMemberWithSchemeRolesList
  1160  	_, err = s.SqlStore.DBFromContext(ctx).Select(&dbMembers, queryString, args...)
  1161  	if err != nil {
  1162  		return nil, errors.Wrapf(err, "failed to find TeamMembers with userId=%s", userId)
  1163  	}
  1164  
  1165  	return dbMembers.ToModel(), nil
  1166  }
  1167  
  1168  // GetTeamsForUserWithPagination returns limited TeamMembers according to the perPage parameter specified.
  1169  // It also offsets the records as per the page parameter supplied.
  1170  func (s SqlTeamStore) GetTeamsForUserWithPagination(userId string, page, perPage int) ([]*model.TeamMember, error) {
  1171  	query := s.getTeamMembersWithSchemeSelectQuery().
  1172  		Where(sq.Eq{"TeamMembers.UserId": userId}).
  1173  		Limit(uint64(perPage)).
  1174  		Offset(uint64(page * perPage))
  1175  
  1176  	queryString, args, err := query.ToSql()
  1177  	if err != nil {
  1178  		return nil, errors.Wrap(err, "team_tosql")
  1179  	}
  1180  
  1181  	var dbMembers teamMemberWithSchemeRolesList
  1182  	_, err = s.GetReplica().Select(&dbMembers, queryString, args...)
  1183  	if err != nil {
  1184  		return nil, errors.Wrapf(err, "failed to find TeamMembers with userId=%s", userId)
  1185  	}
  1186  
  1187  	return dbMembers.ToModel(), nil
  1188  }
  1189  
  1190  // GetChannelUnreadsForAllTeams returns unreads msg count, mention counts, and notifyProps
  1191  // for all the channels in all the teams except the excluded ones.
  1192  func (s SqlTeamStore) GetChannelUnreadsForAllTeams(excludeTeamId, userId string) ([]*model.ChannelUnread, error) {
  1193  	query, args, err := s.getQueryBuilder().
  1194  		Select("Channels.TeamId TeamId", "Channels.Id ChannelId", "(Channels.TotalMsgCount - ChannelMembers.MsgCount) MsgCount", "ChannelMembers.MentionCount MentionCount", "ChannelMembers.NotifyProps NotifyProps").
  1195  		From("Channels").
  1196  		Join("ChannelMembers ON Id = ChannelId").
  1197  		Where(sq.Eq{"UserId": userId, "DeleteAt": 0}).
  1198  		Where(sq.NotEq{"TeamId": excludeTeamId}).ToSql()
  1199  
  1200  	if err != nil {
  1201  		return nil, errors.Wrap(err, "team_tosql")
  1202  	}
  1203  	var data []*model.ChannelUnread
  1204  	_, err = s.GetReplica().Select(&data, query, args...)
  1205  
  1206  	if err != nil {
  1207  		return nil, errors.Wrapf(err, "failed to find Channels with userId=%s and teamId!=%s", userId, excludeTeamId)
  1208  	}
  1209  
  1210  	return data, nil
  1211  }
  1212  
  1213  // GetChannelUnreadsForTeam returns unreads msg count, mention counts and notifyProps for all the channels in a single team.
  1214  func (s SqlTeamStore) GetChannelUnreadsForTeam(teamId, userId string) ([]*model.ChannelUnread, error) {
  1215  	query, args, err := s.getQueryBuilder().
  1216  		Select("Channels.TeamId TeamId", "Channels.Id ChannelId", "(Channels.TotalMsgCount - ChannelMembers.MsgCount) MsgCount", "ChannelMembers.MentionCount MentionCount", "ChannelMembers.NotifyProps NotifyProps").
  1217  		From("Channels").
  1218  		Join("ChannelMembers ON Id = ChannelId").
  1219  		Where(sq.Eq{"UserId": userId, "TeamId": teamId, "DeleteAt": 0}).ToSql()
  1220  
  1221  	if err != nil {
  1222  		return nil, errors.Wrap(err, "team_tosql")
  1223  	}
  1224  
  1225  	var channels []*model.ChannelUnread
  1226  	_, err = s.GetReplica().Select(&channels, query, args...)
  1227  
  1228  	if err != nil {
  1229  		return nil, errors.Wrapf(err, "failed to find Channels with teamId=%s and userId=%s", teamId, userId)
  1230  	}
  1231  	return channels, nil
  1232  }
  1233  
  1234  func (s SqlTeamStore) RemoveMembers(teamId string, userIds []string) error {
  1235  	builder := s.getQueryBuilder().
  1236  		Delete("TeamMembers").
  1237  		Where(sq.Eq{"TeamId": teamId}).
  1238  		Where(sq.Eq{"UserId": userIds})
  1239  
  1240  	query, args, err := builder.ToSql()
  1241  	if err != nil {
  1242  		return errors.Wrap(err, "team_tosql")
  1243  	}
  1244  	_, err = s.GetMaster().Exec(query, args...)
  1245  	if err != nil {
  1246  		return errors.Wrapf(err, "failed to delete TeamMembers with teamId=%s and userId in %v", teamId, userIds)
  1247  	}
  1248  	return nil
  1249  }
  1250  
  1251  // RemoveMember remove from the database the team members that match the userId and teamId passed as parameter.
  1252  func (s SqlTeamStore) RemoveMember(teamId string, userId string) error {
  1253  	return s.RemoveMembers(teamId, []string{userId})
  1254  }
  1255  
  1256  // RemoveAllMembersByTeam removes from the database the team members that belong to the teamId passed as parameter.
  1257  func (s SqlTeamStore) RemoveAllMembersByTeam(teamId string) error {
  1258  	query, args, err := s.getQueryBuilder().
  1259  		Delete("TeamMembers").
  1260  		Where(sq.Eq{"TeamId": teamId}).ToSql()
  1261  	if err != nil {
  1262  		return errors.Wrap(err, "team_tosql")
  1263  	}
  1264  
  1265  	_, err = s.GetMaster().Exec(query, args...)
  1266  	if err != nil {
  1267  		return errors.Wrapf(err, "failed to delete TeamMembers with teamId=%s", teamId)
  1268  	}
  1269  	return nil
  1270  }
  1271  
  1272  // RemoveAllMembersByUser removes from the database the team members that match the userId passed as parameter.
  1273  func (s SqlTeamStore) RemoveAllMembersByUser(userId string) error {
  1274  	query, args, err := s.getQueryBuilder().
  1275  		Delete("TeamMembers").
  1276  		Where(sq.Eq{"UserId": userId}).ToSql()
  1277  	if err != nil {
  1278  		return errors.Wrap(err, "team_tosql")
  1279  	}
  1280  	_, err = s.GetMaster().Exec(query, args...)
  1281  	if err != nil {
  1282  		return errors.Wrapf(err, "failed to delete TeamMembers with userId=%s", userId)
  1283  	}
  1284  	return nil
  1285  }
  1286  
  1287  // UpdateLastTeamIconUpdate sets the last updated time for the icon based on the parameter passed in teamId. The
  1288  // LastTeamIconUpdate and UpdateAt fields are set to the parameter passed in curTime. Returns nil on success and an error
  1289  // otherwise.
  1290  func (s SqlTeamStore) UpdateLastTeamIconUpdate(teamId string, curTime int64) error {
  1291  	query, args, err := s.getQueryBuilder().
  1292  		Update("Teams").
  1293  		SetMap(sq.Eq{"LastTeamIconUpdate": curTime, "UpdateAt": curTime}).
  1294  		Where(sq.Eq{"Id": teamId}).ToSql()
  1295  	if err != nil {
  1296  		return errors.Wrap(err, "team_tosql")
  1297  	}
  1298  
  1299  	if _, err = s.GetMaster().Exec(query, args...); err != nil {
  1300  		return errors.Wrap(err, "failed to update Team")
  1301  	}
  1302  	return nil
  1303  }
  1304  
  1305  // GetTeamsByScheme returns from the database all teams that match the schemeId provided as parameter, up to
  1306  // a total limit passed as paramater and paginated by offset number passed as parameter.
  1307  func (s SqlTeamStore) GetTeamsByScheme(schemeId string, offset int, limit int) ([]*model.Team, error) {
  1308  	query, args, err := s.teamsQuery.Where(sq.Eq{"SchemeId": schemeId}).
  1309  		OrderBy("DisplayName").
  1310  		Limit(uint64(limit)).
  1311  		Offset(uint64(offset)).ToSql()
  1312  
  1313  	if err != nil {
  1314  		return nil, errors.Wrap(err, "team_tosql")
  1315  	}
  1316  
  1317  	var teams []*model.Team
  1318  	_, err = s.GetReplica().Select(&teams, query, args...)
  1319  	if err != nil {
  1320  		return nil, errors.Wrapf(err, "failed to find Teams with schemeId=%s", schemeId)
  1321  	}
  1322  	return teams, nil
  1323  }
  1324  
  1325  // MigrateTeamMembers performs the Advanced Permissions Phase 2 migration for TeamMember objects. Migration is done
  1326  // in batches as a single transaction per batch to ensure consistency but to also minimise execution time to avoid
  1327  // causing unnecessary table locks. **THIS FUNCTION SHOULD NOT BE USED FOR ANY OTHER PURPOSE.** Executing this function
  1328  // *after* the new Schemes functionality has been used on an installation will have unintended consequences.
  1329  func (s SqlTeamStore) MigrateTeamMembers(fromTeamId string, fromUserId string) (map[string]string, error) {
  1330  	var transaction *gorp.Transaction
  1331  	var err error
  1332  
  1333  	if transaction, err = s.GetMaster().Begin(); err != nil {
  1334  		return nil, errors.Wrap(err, "begin_transaction")
  1335  	}
  1336  	defer finalizeTransaction(transaction)
  1337  
  1338  	var teamMembers []teamMember
  1339  	if _, err := transaction.Select(&teamMembers, "SELECT * from TeamMembers WHERE (TeamId, UserId) > (:FromTeamId, :FromUserId) ORDER BY TeamId, UserId LIMIT 100", map[string]interface{}{"FromTeamId": fromTeamId, "FromUserId": fromUserId}); err != nil {
  1340  		return nil, errors.Wrap(err, "failed to find TeamMembers")
  1341  	}
  1342  
  1343  	if len(teamMembers) == 0 {
  1344  		// No more team members in query result means that the migration has finished.
  1345  		return nil, nil
  1346  	}
  1347  
  1348  	for i := range teamMembers {
  1349  		member := teamMembers[i]
  1350  		roles := strings.Fields(member.Roles)
  1351  		var newRoles []string
  1352  		if !member.SchemeAdmin.Valid {
  1353  			member.SchemeAdmin = sql.NullBool{Bool: false, Valid: true}
  1354  		}
  1355  		if !member.SchemeUser.Valid {
  1356  			member.SchemeUser = sql.NullBool{Bool: false, Valid: true}
  1357  		}
  1358  		if !member.SchemeGuest.Valid {
  1359  			member.SchemeGuest = sql.NullBool{Bool: false, Valid: true}
  1360  		}
  1361  		for _, role := range roles {
  1362  			if role == model.TEAM_ADMIN_ROLE_ID {
  1363  				member.SchemeAdmin = sql.NullBool{Bool: true, Valid: true}
  1364  			} else if role == model.TEAM_USER_ROLE_ID {
  1365  				member.SchemeUser = sql.NullBool{Bool: true, Valid: true}
  1366  			} else if role == model.TEAM_GUEST_ROLE_ID {
  1367  				member.SchemeGuest = sql.NullBool{Bool: true, Valid: true}
  1368  			} else {
  1369  				newRoles = append(newRoles, role)
  1370  			}
  1371  		}
  1372  		member.Roles = strings.Join(newRoles, " ")
  1373  
  1374  		if _, err := transaction.Update(&member); err != nil {
  1375  			return nil, errors.Wrap(err, "failed to update TeamMember")
  1376  		}
  1377  
  1378  	}
  1379  
  1380  	if err := transaction.Commit(); err != nil {
  1381  		return nil, errors.Wrap(err, "commit_transaction")
  1382  	}
  1383  
  1384  	data := make(map[string]string)
  1385  	data["TeamId"] = teamMembers[len(teamMembers)-1].TeamId
  1386  	data["UserId"] = teamMembers[len(teamMembers)-1].UserId
  1387  
  1388  	return data, nil
  1389  }
  1390  
  1391  // ResetAllTeamSchemes Set all Team's SchemeId values to an empty string.
  1392  func (s SqlTeamStore) ResetAllTeamSchemes() error {
  1393  	if _, err := s.GetMaster().Exec("UPDATE Teams SET SchemeId=''"); err != nil {
  1394  		return errors.Wrap(err, "failed to update Teams")
  1395  	}
  1396  	return nil
  1397  }
  1398  
  1399  // ClearCaches method not implemented.
  1400  func (s SqlTeamStore) ClearCaches() {}
  1401  
  1402  // InvalidateAllTeamIdsForUser does not execute anything because the store does not handle the cache.
  1403  //nolint:unparam
  1404  func (s SqlTeamStore) InvalidateAllTeamIdsForUser(userId string) {}
  1405  
  1406  // ClearAllCustomRoleAssignments removes all custom role assignments from TeamMembers.
  1407  func (s SqlTeamStore) ClearAllCustomRoleAssignments() error {
  1408  
  1409  	builtInRoles := model.MakeDefaultRoles()
  1410  	lastUserId := strings.Repeat("0", 26)
  1411  	lastTeamId := strings.Repeat("0", 26)
  1412  
  1413  	for {
  1414  		var transaction *gorp.Transaction
  1415  		var err error
  1416  
  1417  		if transaction, err = s.GetMaster().Begin(); err != nil {
  1418  			return errors.Wrap(err, "begin_transaction")
  1419  		}
  1420  		defer finalizeTransaction(transaction)
  1421  
  1422  		var teamMembers []*teamMember
  1423  		if _, err := transaction.Select(&teamMembers, "SELECT * from TeamMembers WHERE (TeamId, UserId) > (:TeamId, :UserId) ORDER BY TeamId, UserId LIMIT 1000", map[string]interface{}{"TeamId": lastTeamId, "UserId": lastUserId}); err != nil {
  1424  			return errors.Wrap(err, "failed to find TeamMembers")
  1425  		}
  1426  
  1427  		if len(teamMembers) == 0 {
  1428  			break
  1429  		}
  1430  
  1431  		for _, member := range teamMembers {
  1432  			lastUserId = member.UserId
  1433  			lastTeamId = member.TeamId
  1434  
  1435  			var newRoles []string
  1436  
  1437  			for _, role := range strings.Fields(member.Roles) {
  1438  				for name := range builtInRoles {
  1439  					if name == role {
  1440  						newRoles = append(newRoles, role)
  1441  						break
  1442  					}
  1443  				}
  1444  			}
  1445  
  1446  			newRolesString := strings.Join(newRoles, " ")
  1447  			if newRolesString != member.Roles {
  1448  				if _, err := transaction.Exec("UPDATE TeamMembers SET Roles = :Roles WHERE UserId = :UserId AND TeamId = :TeamId", map[string]interface{}{"Roles": newRolesString, "TeamId": member.TeamId, "UserId": member.UserId}); err != nil {
  1449  					return errors.Wrap(err, "failed to update TeamMembers")
  1450  				}
  1451  			}
  1452  		}
  1453  
  1454  		if err := transaction.Commit(); err != nil {
  1455  			return errors.Wrap(err, "commit_transaction")
  1456  		}
  1457  	}
  1458  	return nil
  1459  }
  1460  
  1461  // AnalyticsGetTeamCountForScheme returns the number of active teams that match the schemeId passed as parameter.
  1462  func (s SqlTeamStore) AnalyticsGetTeamCountForScheme(schemeId string) (int64, error) {
  1463  	query, args, err := s.getQueryBuilder().
  1464  		Select("count(*)").
  1465  		From("Teams").
  1466  		Where(sq.Eq{"SchemeId": schemeId, "DeleteAt": 0}).ToSql()
  1467  
  1468  	if err != nil {
  1469  		return 0, errors.Wrap(err, "team_tosql")
  1470  	}
  1471  	count, err := s.GetReplica().SelectInt(query, args...)
  1472  	if err != nil {
  1473  		return 0, errors.Wrapf(err, "failed to count Teams with schemdId=%s", schemeId)
  1474  	}
  1475  
  1476  	return count, nil
  1477  }
  1478  
  1479  // GetAllForExportAfter returns teams for export, up to a total limit passed as paramater where Teams.Id is greater than the afterId passed as parameter.
  1480  func (s SqlTeamStore) GetAllForExportAfter(limit int, afterId string) ([]*model.TeamForExport, error) {
  1481  	var data []*model.TeamForExport
  1482  	query, args, err := s.getQueryBuilder().
  1483  		Select("Teams.*", "Schemes.Name as SchemeName").
  1484  		From("Teams").
  1485  		LeftJoin("Schemes ON Teams.SchemeId = Schemes.Id").
  1486  		Where(sq.Gt{"Teams.Id": afterId}).
  1487  		OrderBy("Id").
  1488  		Limit(uint64(limit)).ToSql()
  1489  
  1490  	if err != nil {
  1491  		return nil, errors.Wrap(err, "team_tosql")
  1492  	}
  1493  	if _, err = s.GetReplica().Select(&data, query, args...); err != nil {
  1494  		return nil, errors.Wrap(err, "failed to find Teams")
  1495  	}
  1496  
  1497  	return data, nil
  1498  }
  1499  
  1500  // GetUserTeamIds get the team ids to which the user belongs to. allowFromCache parameter does not have any effect in this Store
  1501  //nolint:unparam
  1502  func (s SqlTeamStore) GetUserTeamIds(userId string, allowFromCache bool) ([]string, error) {
  1503  	var teamIds []string
  1504  	query, args, err := s.getQueryBuilder().
  1505  		Select("TeamId").
  1506  		From("TeamMembers").
  1507  		Join("Teams ON TeamMembers.TeamId = Teams.Id").
  1508  		Where(sq.Eq{"TeamMembers.UserId": userId, "TeamMembers.DeleteAt": 0, "Teams.DeleteAt": 0}).ToSql()
  1509  
  1510  	if err != nil {
  1511  		return []string{}, errors.Wrap(err, "team_tosql")
  1512  	}
  1513  	_, err = s.GetReplica().Select(&teamIds, query, args...)
  1514  	if err != nil {
  1515  		return []string{}, errors.Wrapf(err, "failed to find TeamMembers with userId=%s", userId)
  1516  	}
  1517  
  1518  	return teamIds, nil
  1519  }
  1520  
  1521  // GetTeamMembersForExport gets the various teams for which a user, denoted by userId, is a part of.
  1522  func (s SqlTeamStore) GetTeamMembersForExport(userId string) ([]*model.TeamMemberForExport, error) {
  1523  	var members []*model.TeamMemberForExport
  1524  	query, args, err := s.getQueryBuilder().
  1525  		Select("TeamMembers.TeamId", "TeamMembers.UserId", "TeamMembers.Roles", "TeamMembers.DeleteAt",
  1526  			"(TeamMembers.SchemeGuest IS NOT NULL AND TeamMembers.SchemeGuest) as SchemeGuest",
  1527  			"TeamMembers.SchemeUser", "TeamMembers.SchemeAdmin", "Teams.Name as TeamName").
  1528  		From("TeamMembers").
  1529  		Join("Teams ON TeamMembers.TeamId = Teams.Id").
  1530  		Where(sq.Eq{"TeamMembers.UserId": userId, "Teams.DeleteAt": 0}).ToSql()
  1531  
  1532  	if err != nil {
  1533  		return nil, errors.Wrap(err, "team_tosql")
  1534  	}
  1535  	_, err = s.GetReplica().Select(&members, query, args...)
  1536  	if err != nil {
  1537  		return nil, errors.Wrapf(err, "failed to find TeamMembers with userId=%s", userId)
  1538  	}
  1539  	return members, nil
  1540  }
  1541  
  1542  //UserBelongsToTeams returns true if the user denoted by userId is a member of the teams in the teamIds string array.
  1543  func (s SqlTeamStore) UserBelongsToTeams(userId string, teamIds []string) (bool, error) {
  1544  	idQuery := sq.Eq{
  1545  		"UserId":   userId,
  1546  		"TeamId":   teamIds,
  1547  		"DeleteAt": 0,
  1548  	}
  1549  
  1550  	query, params, err := s.getQueryBuilder().Select("Count(*)").From("TeamMembers").Where(idQuery).ToSql()
  1551  	if err != nil {
  1552  		return false, errors.Wrap(err, "team_tosql")
  1553  	}
  1554  
  1555  	c, err := s.GetReplica().SelectInt(query, params...)
  1556  	if err != nil {
  1557  		return false, errors.Wrap(err, "failed to count TeamMembers")
  1558  	}
  1559  
  1560  	return c > 0, nil
  1561  }
  1562  
  1563  // UpdateMembersRole updates all the members of teamID in the userIds string array to be admins and sets all other
  1564  // users as not being admin.
  1565  func (s SqlTeamStore) UpdateMembersRole(teamID string, userIDs []string) error {
  1566  	query, args, err := s.getQueryBuilder().
  1567  		Update("TeamMembers").
  1568  		Set("SchemeAdmin", sq.Case().When(sq.Eq{"UserId": userIDs}, "true").Else("false")).
  1569  		Where(sq.Eq{"TeamId": teamID, "DeleteAt": 0}).
  1570  		Where(sq.Or{sq.Eq{"SchemeGuest": false}, sq.Expr("SchemeGuest IS NULL")}).ToSql()
  1571  	if err != nil {
  1572  		return errors.Wrap(err, "team_tosql")
  1573  	}
  1574  
  1575  	if _, err = s.GetMaster().Exec(query, args...); err != nil {
  1576  		return errors.Wrap(err, "failed to update TeamMembers")
  1577  	}
  1578  
  1579  	return nil
  1580  }
  1581  
  1582  func applyTeamMemberViewRestrictionsFilter(query sq.SelectBuilder, restrictions *model.ViewUsersRestrictions) sq.SelectBuilder {
  1583  	if restrictions == nil {
  1584  		return query
  1585  	}
  1586  
  1587  	// If you have no access to teams or channels, return and empty result.
  1588  	if restrictions.Teams != nil && len(restrictions.Teams) == 0 && restrictions.Channels != nil && len(restrictions.Channels) == 0 {
  1589  		return query.Where("1 = 0")
  1590  	}
  1591  
  1592  	teams := make([]interface{}, len(restrictions.Teams))
  1593  	for i, v := range restrictions.Teams {
  1594  		teams[i] = v
  1595  	}
  1596  	channels := make([]interface{}, len(restrictions.Channels))
  1597  	for i, v := range restrictions.Channels {
  1598  		channels[i] = v
  1599  	}
  1600  
  1601  	resultQuery := query.Join("Users ru ON (TeamMembers.UserId = ru.Id)")
  1602  	if restrictions.Teams != nil && len(restrictions.Teams) > 0 {
  1603  		resultQuery = resultQuery.Join(fmt.Sprintf("TeamMembers rtm ON ( rtm.UserId = ru.Id AND rtm.DeleteAt = 0 AND rtm.TeamId IN (%s))", sq.Placeholders(len(teams))), teams...)
  1604  	}
  1605  	if restrictions.Channels != nil && len(restrictions.Channels) > 0 {
  1606  		resultQuery = resultQuery.Join(fmt.Sprintf("ChannelMembers rcm ON ( rcm.UserId = ru.Id AND rcm.ChannelId IN (%s))", sq.Placeholders(len(channels))), channels...)
  1607  	}
  1608  
  1609  	return resultQuery.Distinct()
  1610  }
  1611  
  1612  func applyTeamMemberViewRestrictionsFilterForStats(query sq.SelectBuilder, restrictions *model.ViewUsersRestrictions) sq.SelectBuilder {
  1613  	if restrictions == nil {
  1614  		return query
  1615  	}
  1616  
  1617  	// If you have no access to teams or channels, return and empty result.
  1618  	if restrictions.Teams != nil && len(restrictions.Teams) == 0 && restrictions.Channels != nil && len(restrictions.Channels) == 0 {
  1619  		return query.Where("1 = 0")
  1620  	}
  1621  
  1622  	teams := make([]interface{}, len(restrictions.Teams))
  1623  	for i, v := range restrictions.Teams {
  1624  		teams[i] = v
  1625  	}
  1626  	channels := make([]interface{}, len(restrictions.Channels))
  1627  	for i, v := range restrictions.Channels {
  1628  		channels[i] = v
  1629  	}
  1630  
  1631  	resultQuery := query
  1632  	if restrictions.Teams != nil && len(restrictions.Teams) > 0 {
  1633  		resultQuery = resultQuery.Join(fmt.Sprintf("TeamMembers rtm ON ( rtm.UserId = Users.Id AND rtm.DeleteAt = 0 AND rtm.TeamId IN (%s))", sq.Placeholders(len(teams))), teams...)
  1634  	}
  1635  	if restrictions.Channels != nil && len(restrictions.Channels) > 0 {
  1636  		resultQuery = resultQuery.Join(fmt.Sprintf("ChannelMembers rcm ON ( rcm.UserId = Users.Id AND rcm.ChannelId IN (%s))", sq.Placeholders(len(channels))), channels...)
  1637  	}
  1638  
  1639  	return resultQuery
  1640  }
  1641  
  1642  // GroupSyncedTeamCount returns the number of teams that are group constrained.
  1643  func (s SqlTeamStore) GroupSyncedTeamCount() (int64, error) {
  1644  	builder := s.getQueryBuilder().Select("COUNT(*)").From("Teams").Where(sq.Eq{"GroupConstrained": true, "DeleteAt": 0})
  1645  
  1646  	query, args, err := builder.ToSql()
  1647  	if err != nil {
  1648  		return 0, errors.Wrap(err, "team_tosql")
  1649  	}
  1650  
  1651  	count, err := s.GetReplica().SelectInt(query, args...)
  1652  	if err != nil {
  1653  		return 0, errors.Wrap(err, "failed to count Teams")
  1654  	}
  1655  
  1656  	return count, nil
  1657  }