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 }