github.com/xzl8028/xenia-server@v0.0.0-20190809101854-18450a97da63/store/sqlstore/channel_store.go (about) 1 // Copyright (c) 2015-present Xenia, Inc. All Rights Reserved. 2 // See License.txt for license information. 3 4 package sqlstore 5 6 import ( 7 "database/sql" 8 "fmt" 9 "net/http" 10 "sort" 11 "strconv" 12 "strings" 13 14 "github.com/xzl8028/gorp" 15 "github.com/pkg/errors" 16 17 sq "github.com/Masterminds/squirrel" 18 "github.com/xzl8028/xenia-server/einterfaces" 19 "github.com/xzl8028/xenia-server/mlog" 20 "github.com/xzl8028/xenia-server/model" 21 "github.com/xzl8028/xenia-server/store" 22 "github.com/xzl8028/xenia-server/utils" 23 ) 24 25 const ( 26 ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SIZE = model.SESSION_CACHE_SIZE 27 ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SEC = 900 // 15 mins 28 29 ALL_CHANNEL_MEMBERS_NOTIFY_PROPS_FOR_CHANNEL_CACHE_SIZE = model.SESSION_CACHE_SIZE 30 ALL_CHANNEL_MEMBERS_NOTIFY_PROPS_FOR_CHANNEL_CACHE_SEC = 1800 // 30 mins 31 32 CHANNEL_MEMBERS_COUNTS_CACHE_SIZE = model.CHANNEL_CACHE_SIZE 33 CHANNEL_MEMBERS_COUNTS_CACHE_SEC = 1800 // 30 mins 34 35 CHANNEL_CACHE_SEC = 900 // 15 mins 36 ) 37 38 type SqlChannelStore struct { 39 SqlStore 40 metrics einterfaces.MetricsInterface 41 } 42 43 type channelMember struct { 44 ChannelId string 45 UserId string 46 Roles string 47 LastViewedAt int64 48 MsgCount int64 49 MentionCount int64 50 NotifyProps model.StringMap 51 LastUpdateAt int64 52 SchemeGuest sql.NullBool 53 SchemeUser sql.NullBool 54 SchemeAdmin sql.NullBool 55 } 56 57 func NewChannelMemberFromModel(cm *model.ChannelMember) *channelMember { 58 return &channelMember{ 59 ChannelId: cm.ChannelId, 60 UserId: cm.UserId, 61 Roles: cm.ExplicitRoles, 62 LastViewedAt: cm.LastViewedAt, 63 MsgCount: cm.MsgCount, 64 MentionCount: cm.MentionCount, 65 NotifyProps: cm.NotifyProps, 66 LastUpdateAt: cm.LastUpdateAt, 67 SchemeGuest: sql.NullBool{Valid: true, Bool: cm.SchemeGuest}, 68 SchemeUser: sql.NullBool{Valid: true, Bool: cm.SchemeUser}, 69 SchemeAdmin: sql.NullBool{Valid: true, Bool: cm.SchemeAdmin}, 70 } 71 } 72 73 type channelMemberWithSchemeRoles struct { 74 ChannelId string 75 UserId string 76 Roles string 77 LastViewedAt int64 78 MsgCount int64 79 MentionCount int64 80 NotifyProps model.StringMap 81 LastUpdateAt int64 82 SchemeGuest sql.NullBool 83 SchemeUser sql.NullBool 84 SchemeAdmin sql.NullBool 85 TeamSchemeDefaultGuestRole sql.NullString 86 TeamSchemeDefaultUserRole sql.NullString 87 TeamSchemeDefaultAdminRole sql.NullString 88 ChannelSchemeDefaultGuestRole sql.NullString 89 ChannelSchemeDefaultUserRole sql.NullString 90 ChannelSchemeDefaultAdminRole sql.NullString 91 } 92 93 type channelMemberWithSchemeRolesList []channelMemberWithSchemeRoles 94 95 func (db channelMemberWithSchemeRoles) ToModel() *model.ChannelMember { 96 var roles []string 97 var explicitRoles []string 98 99 // Identify any system-wide scheme derived roles that are in "Roles" field due to not yet being migrated, 100 // and exclude them from ExplicitRoles field. 101 schemeGuest := db.SchemeGuest.Valid && db.SchemeGuest.Bool 102 schemeUser := db.SchemeUser.Valid && db.SchemeUser.Bool 103 schemeAdmin := db.SchemeAdmin.Valid && db.SchemeAdmin.Bool 104 for _, role := range strings.Fields(db.Roles) { 105 isImplicit := false 106 if role == model.CHANNEL_GUEST_ROLE_ID { 107 // We have an implicit role via the system scheme. Override the "schemeGuest" field to true. 108 schemeGuest = true 109 isImplicit = true 110 } else if role == model.CHANNEL_USER_ROLE_ID { 111 // We have an implicit role via the system scheme. Override the "schemeUser" field to true. 112 schemeUser = true 113 isImplicit = true 114 } else if role == model.CHANNEL_ADMIN_ROLE_ID { 115 // We have an implicit role via the system scheme. 116 schemeAdmin = true 117 isImplicit = true 118 } 119 120 if !isImplicit { 121 explicitRoles = append(explicitRoles, role) 122 } 123 roles = append(roles, role) 124 } 125 126 // Add any scheme derived roles that are not in the Roles field due to being Implicit from the Scheme, and add 127 // them to the Roles field for backwards compatibility reasons. 128 var schemeImpliedRoles []string 129 if db.SchemeGuest.Valid && db.SchemeGuest.Bool { 130 if db.ChannelSchemeDefaultGuestRole.Valid && db.ChannelSchemeDefaultGuestRole.String != "" { 131 schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultGuestRole.String) 132 } else if db.TeamSchemeDefaultGuestRole.Valid && db.TeamSchemeDefaultGuestRole.String != "" { 133 schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultGuestRole.String) 134 } else { 135 schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_GUEST_ROLE_ID) 136 } 137 } 138 if db.SchemeUser.Valid && db.SchemeUser.Bool { 139 if db.ChannelSchemeDefaultUserRole.Valid && db.ChannelSchemeDefaultUserRole.String != "" { 140 schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultUserRole.String) 141 } else if db.TeamSchemeDefaultUserRole.Valid && db.TeamSchemeDefaultUserRole.String != "" { 142 schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultUserRole.String) 143 } else { 144 schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_USER_ROLE_ID) 145 } 146 } 147 if db.SchemeAdmin.Valid && db.SchemeAdmin.Bool { 148 if db.ChannelSchemeDefaultAdminRole.Valid && db.ChannelSchemeDefaultAdminRole.String != "" { 149 schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultAdminRole.String) 150 } else if db.TeamSchemeDefaultAdminRole.Valid && db.TeamSchemeDefaultAdminRole.String != "" { 151 schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultAdminRole.String) 152 } else { 153 schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_ADMIN_ROLE_ID) 154 } 155 } 156 for _, impliedRole := range schemeImpliedRoles { 157 alreadyThere := false 158 for _, role := range roles { 159 if role == impliedRole { 160 alreadyThere = true 161 } 162 } 163 if !alreadyThere { 164 roles = append(roles, impliedRole) 165 } 166 } 167 168 return &model.ChannelMember{ 169 ChannelId: db.ChannelId, 170 UserId: db.UserId, 171 Roles: strings.Join(roles, " "), 172 LastViewedAt: db.LastViewedAt, 173 MsgCount: db.MsgCount, 174 MentionCount: db.MentionCount, 175 NotifyProps: db.NotifyProps, 176 LastUpdateAt: db.LastUpdateAt, 177 SchemeAdmin: schemeAdmin, 178 SchemeUser: schemeUser, 179 SchemeGuest: schemeGuest, 180 ExplicitRoles: strings.Join(explicitRoles, " "), 181 } 182 } 183 184 func (db channelMemberWithSchemeRolesList) ToModel() *model.ChannelMembers { 185 cms := model.ChannelMembers{} 186 187 for _, cm := range db { 188 cms = append(cms, *cm.ToModel()) 189 } 190 191 return &cms 192 } 193 194 type allChannelMember struct { 195 ChannelId string 196 Roles string 197 SchemeGuest sql.NullBool 198 SchemeUser sql.NullBool 199 SchemeAdmin sql.NullBool 200 TeamSchemeDefaultGuestRole sql.NullString 201 TeamSchemeDefaultUserRole sql.NullString 202 TeamSchemeDefaultAdminRole sql.NullString 203 ChannelSchemeDefaultGuestRole sql.NullString 204 ChannelSchemeDefaultUserRole sql.NullString 205 ChannelSchemeDefaultAdminRole sql.NullString 206 } 207 208 type allChannelMembers []allChannelMember 209 210 func (db allChannelMember) Process() (string, string) { 211 roles := strings.Fields(db.Roles) 212 213 // Add any scheme derived roles that are not in the Roles field due to being Implicit from the Scheme, and add 214 // them to the Roles field for backwards compatibility reasons. 215 var schemeImpliedRoles []string 216 if db.SchemeGuest.Valid && db.SchemeGuest.Bool { 217 if db.ChannelSchemeDefaultGuestRole.Valid && db.ChannelSchemeDefaultGuestRole.String != "" { 218 schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultGuestRole.String) 219 } else if db.TeamSchemeDefaultGuestRole.Valid && db.TeamSchemeDefaultGuestRole.String != "" { 220 schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultGuestRole.String) 221 } else { 222 schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_GUEST_ROLE_ID) 223 } 224 } 225 if db.SchemeUser.Valid && db.SchemeUser.Bool { 226 if db.ChannelSchemeDefaultUserRole.Valid && db.ChannelSchemeDefaultUserRole.String != "" { 227 schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultUserRole.String) 228 } else if db.TeamSchemeDefaultUserRole.Valid && db.TeamSchemeDefaultUserRole.String != "" { 229 schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultUserRole.String) 230 } else { 231 schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_USER_ROLE_ID) 232 } 233 } 234 if db.SchemeAdmin.Valid && db.SchemeAdmin.Bool { 235 if db.ChannelSchemeDefaultAdminRole.Valid && db.ChannelSchemeDefaultAdminRole.String != "" { 236 schemeImpliedRoles = append(schemeImpliedRoles, db.ChannelSchemeDefaultAdminRole.String) 237 } else if db.TeamSchemeDefaultAdminRole.Valid && db.TeamSchemeDefaultAdminRole.String != "" { 238 schemeImpliedRoles = append(schemeImpliedRoles, db.TeamSchemeDefaultAdminRole.String) 239 } else { 240 schemeImpliedRoles = append(schemeImpliedRoles, model.CHANNEL_ADMIN_ROLE_ID) 241 } 242 } 243 for _, impliedRole := range schemeImpliedRoles { 244 alreadyThere := false 245 for _, role := range roles { 246 if role == impliedRole { 247 alreadyThere = true 248 } 249 } 250 if !alreadyThere { 251 roles = append(roles, impliedRole) 252 } 253 } 254 255 return db.ChannelId, strings.Join(roles, " ") 256 } 257 258 func (db allChannelMembers) ToMapStringString() map[string]string { 259 result := make(map[string]string) 260 261 for _, item := range db { 262 key, value := item.Process() 263 result[key] = value 264 } 265 266 return result 267 } 268 269 // publicChannel is a subset of the metadata corresponding to public channels only. 270 type publicChannel struct { 271 Id string `json:"id"` 272 DeleteAt int64 `json:"delete_at"` 273 TeamId string `json:"team_id"` 274 DisplayName string `json:"display_name"` 275 Name string `json:"name"` 276 Header string `json:"header"` 277 Purpose string `json:"purpose"` 278 } 279 280 var channelMemberCountsCache = utils.NewLru(CHANNEL_MEMBERS_COUNTS_CACHE_SIZE) 281 var allChannelMembersForUserCache = utils.NewLru(ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SIZE) 282 var allChannelMembersNotifyPropsForChannelCache = utils.NewLru(ALL_CHANNEL_MEMBERS_NOTIFY_PROPS_FOR_CHANNEL_CACHE_SIZE) 283 var channelCache = utils.NewLru(model.CHANNEL_CACHE_SIZE) 284 var channelByNameCache = utils.NewLru(model.CHANNEL_CACHE_SIZE) 285 286 func (s SqlChannelStore) ClearCaches() { 287 channelMemberCountsCache.Purge() 288 allChannelMembersForUserCache.Purge() 289 allChannelMembersNotifyPropsForChannelCache.Purge() 290 channelCache.Purge() 291 channelByNameCache.Purge() 292 293 if s.metrics != nil { 294 s.metrics.IncrementMemCacheInvalidationCounter("Channel Member Counts - Purge") 295 s.metrics.IncrementMemCacheInvalidationCounter("All Channel Members for User - Purge") 296 s.metrics.IncrementMemCacheInvalidationCounter("All Channel Members Notify Props for Channel - Purge") 297 s.metrics.IncrementMemCacheInvalidationCounter("Channel - Purge") 298 s.metrics.IncrementMemCacheInvalidationCounter("Channel By Name - Purge") 299 } 300 } 301 302 func NewSqlChannelStore(sqlStore SqlStore, metrics einterfaces.MetricsInterface) store.ChannelStore { 303 s := &SqlChannelStore{ 304 SqlStore: sqlStore, 305 metrics: metrics, 306 } 307 308 for _, db := range sqlStore.GetAllConns() { 309 table := db.AddTableWithName(model.Channel{}, "Channels").SetKeys(false, "Id") 310 table.ColMap("Id").SetMaxSize(26) 311 table.ColMap("TeamId").SetMaxSize(26) 312 table.ColMap("Type").SetMaxSize(1) 313 table.ColMap("DisplayName").SetMaxSize(64) 314 table.ColMap("Name").SetMaxSize(64) 315 table.SetUniqueTogether("Name", "TeamId") 316 table.ColMap("Header").SetMaxSize(1024) 317 table.ColMap("Purpose").SetMaxSize(250) 318 table.ColMap("CreatorId").SetMaxSize(26) 319 table.ColMap("SchemeId").SetMaxSize(26) 320 321 tablem := db.AddTableWithName(channelMember{}, "ChannelMembers").SetKeys(false, "ChannelId", "UserId") 322 tablem.ColMap("ChannelId").SetMaxSize(26) 323 tablem.ColMap("UserId").SetMaxSize(26) 324 tablem.ColMap("Roles").SetMaxSize(64) 325 tablem.ColMap("NotifyProps").SetMaxSize(2000) 326 327 tablePublicChannels := db.AddTableWithName(publicChannel{}, "PublicChannels").SetKeys(false, "Id") 328 tablePublicChannels.ColMap("Id").SetMaxSize(26) 329 tablePublicChannels.ColMap("TeamId").SetMaxSize(26) 330 tablePublicChannels.ColMap("DisplayName").SetMaxSize(64) 331 tablePublicChannels.ColMap("Name").SetMaxSize(64) 332 tablePublicChannels.SetUniqueTogether("Name", "TeamId") 333 tablePublicChannels.ColMap("Header").SetMaxSize(1024) 334 tablePublicChannels.ColMap("Purpose").SetMaxSize(250) 335 } 336 337 return s 338 } 339 340 func (s SqlChannelStore) CreateIndexesIfNotExists() { 341 s.CreateIndexIfNotExists("idx_channels_team_id", "Channels", "TeamId") 342 s.CreateIndexIfNotExists("idx_channels_name", "Channels", "Name") 343 s.CreateIndexIfNotExists("idx_channels_update_at", "Channels", "UpdateAt") 344 s.CreateIndexIfNotExists("idx_channels_create_at", "Channels", "CreateAt") 345 s.CreateIndexIfNotExists("idx_channels_delete_at", "Channels", "DeleteAt") 346 347 if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { 348 s.CreateIndexIfNotExists("idx_channels_name_lower", "Channels", "lower(Name)") 349 s.CreateIndexIfNotExists("idx_channels_displayname_lower", "Channels", "lower(DisplayName)") 350 } 351 352 s.CreateIndexIfNotExists("idx_channelmembers_channel_id", "ChannelMembers", "ChannelId") 353 s.CreateIndexIfNotExists("idx_channelmembers_user_id", "ChannelMembers", "UserId") 354 355 s.CreateFullTextIndexIfNotExists("idx_channel_search_txt", "Channels", "Name, DisplayName, Purpose") 356 357 s.CreateIndexIfNotExists("idx_publicchannels_team_id", "PublicChannels", "TeamId") 358 s.CreateIndexIfNotExists("idx_publicchannels_name", "PublicChannels", "Name") 359 s.CreateIndexIfNotExists("idx_publicchannels_delete_at", "PublicChannels", "DeleteAt") 360 if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { 361 s.CreateIndexIfNotExists("idx_publicchannels_name_lower", "PublicChannels", "lower(Name)") 362 s.CreateIndexIfNotExists("idx_publicchannels_displayname_lower", "PublicChannels", "lower(DisplayName)") 363 } 364 s.CreateFullTextIndexIfNotExists("idx_publicchannels_search_txt", "PublicChannels", "Name, DisplayName, Purpose") 365 } 366 367 // MigratePublicChannels initializes the PublicChannels table with data created before this version 368 // of the Xenia server kept it up-to-date. 369 func (s SqlChannelStore) MigratePublicChannels() error { 370 if _, err := s.GetMaster().Exec(` 371 INSERT INTO PublicChannels 372 (Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose) 373 SELECT 374 c.Id, c.DeleteAt, c.TeamId, c.DisplayName, c.Name, c.Header, c.Purpose 375 FROM 376 Channels c 377 LEFT JOIN 378 PublicChannels pc ON (pc.Id = c.Id) 379 WHERE 380 c.Type = 'O' 381 AND pc.Id IS NULL 382 `); err != nil { 383 return err 384 } 385 386 return nil 387 } 388 389 func (s SqlChannelStore) upsertPublicChannelT(transaction *gorp.Transaction, channel *model.Channel) error { 390 publicChannel := &publicChannel{ 391 Id: channel.Id, 392 DeleteAt: channel.DeleteAt, 393 TeamId: channel.TeamId, 394 DisplayName: channel.DisplayName, 395 Name: channel.Name, 396 Header: channel.Header, 397 Purpose: channel.Purpose, 398 } 399 400 if channel.Type != model.CHANNEL_OPEN { 401 if _, err := transaction.Delete(publicChannel); err != nil { 402 return errors.Wrap(err, "failed to delete public channel") 403 } 404 405 return nil 406 } 407 408 if s.DriverName() == model.DATABASE_DRIVER_MYSQL { 409 // Leverage native upsert for MySQL, since RowsAffected returns 0 if the row exists 410 // but no changes were made, breaking the update-then-insert paradigm below when 411 // the row already exists. (Postgres 9.4 doesn't support native upsert.) 412 if _, err := transaction.Exec(` 413 INSERT INTO 414 PublicChannels(Id, DeleteAt, TeamId, DisplayName, Name, Header, Purpose) 415 VALUES 416 (:Id, :DeleteAt, :TeamId, :DisplayName, :Name, :Header, :Purpose) 417 ON DUPLICATE KEY UPDATE 418 DeleteAt = :DeleteAt, 419 TeamId = :TeamId, 420 DisplayName = :DisplayName, 421 Name = :Name, 422 Header = :Header, 423 Purpose = :Purpose; 424 `, map[string]interface{}{ 425 "Id": publicChannel.Id, 426 "DeleteAt": publicChannel.DeleteAt, 427 "TeamId": publicChannel.TeamId, 428 "DisplayName": publicChannel.DisplayName, 429 "Name": publicChannel.Name, 430 "Header": publicChannel.Header, 431 "Purpose": publicChannel.Purpose, 432 }); err != nil { 433 return errors.Wrap(err, "failed to insert public channel") 434 } 435 } else { 436 count, err := transaction.Update(publicChannel) 437 if err != nil { 438 return errors.Wrap(err, "failed to update public channel") 439 } 440 if count > 0 { 441 return nil 442 } 443 444 if err := transaction.Insert(publicChannel); err != nil { 445 return errors.Wrap(err, "failed to insert public channel") 446 } 447 } 448 449 return nil 450 } 451 452 // Save writes the (non-direct) channel channel to the database. 453 func (s SqlChannelStore) Save(channel *model.Channel, maxChannelsPerTeam int64) (*model.Channel, *model.AppError) { 454 455 if channel.DeleteAt != 0 { 456 return nil, model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save.archived_channel.app_error", nil, "", http.StatusBadRequest) 457 } 458 459 if channel.Type == model.CHANNEL_DIRECT { 460 return nil, model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save.direct_channel.app_error", nil, "", http.StatusBadRequest) 461 } 462 463 transaction, err := s.GetMaster().Begin() 464 if err != nil { 465 return nil, model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 466 } 467 defer finalizeTransaction(transaction) 468 469 channelResult := s.saveChannelT(transaction, channel, maxChannelsPerTeam) 470 var newChannel *model.Channel 471 if channelResult.Data != nil { 472 newChannel = channelResult.Data.(*model.Channel) 473 } 474 appErr := channelResult.Err 475 476 if appErr != nil { 477 return newChannel, appErr 478 } 479 480 // Additionally propagate the write to the PublicChannels table. 481 if err := s.upsertPublicChannelT(transaction, newChannel); err != nil { 482 return nil, model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save.upsert_public_channel.app_error", nil, err.Error(), http.StatusInternalServerError) 483 484 } 485 486 if err := transaction.Commit(); err != nil { 487 return nil, model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 488 } 489 490 return newChannel, nil 491 } 492 493 func (s SqlChannelStore) CreateDirectChannel(userId string, otherUserId string) (*model.Channel, *model.AppError) { 494 channel := new(model.Channel) 495 496 channel.DisplayName = "" 497 channel.Name = model.GetDMNameFromIds(otherUserId, userId) 498 499 channel.Header = "" 500 channel.Type = model.CHANNEL_DIRECT 501 502 cm1 := &model.ChannelMember{ 503 UserId: userId, 504 NotifyProps: model.GetDefaultChannelNotifyProps(), 505 SchemeUser: true, 506 } 507 cm2 := &model.ChannelMember{ 508 UserId: otherUserId, 509 NotifyProps: model.GetDefaultChannelNotifyProps(), 510 SchemeUser: true, 511 } 512 513 return s.SaveDirectChannel(channel, cm1, cm2) 514 } 515 516 func (s SqlChannelStore) SaveDirectChannel(directchannel *model.Channel, member1 *model.ChannelMember, member2 *model.ChannelMember) (*model.Channel, *model.AppError) { 517 if directchannel.DeleteAt != 0 { 518 return nil, model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save.archived_channel.app_error", nil, "", http.StatusBadRequest) 519 } 520 521 if directchannel.Type != model.CHANNEL_DIRECT { 522 return nil, model.NewAppError("SqlChannelStore.SaveDirectChannel", "store.sql_channel.save_direct_channel.not_direct.app_error", nil, "", http.StatusBadRequest) 523 } 524 525 transaction, err := s.GetMaster().Begin() 526 if err != nil { 527 return nil, model.NewAppError("SqlChannelStore.SaveDirectChannel", "store.sql_channel.save_direct_channel.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 528 } 529 defer finalizeTransaction(transaction) 530 531 directchannel.TeamId = "" 532 // After updating saveChannelT() should be: 533 // newChannel, appErr := s.saveChannelT(transaction, directchannel, 0) 534 channelResult := s.saveChannelT(transaction, directchannel, 0) 535 var newChannel *model.Channel 536 if channelResult.Data != nil { 537 newChannel = channelResult.Data.(*model.Channel) 538 } 539 appErr := channelResult.Err 540 541 if appErr != nil { 542 return newChannel, appErr 543 } 544 545 // Members need new channel ID 546 member1.ChannelId = newChannel.Id 547 member2.ChannelId = newChannel.Id 548 549 member1Result := s.saveMemberT(transaction, member1, newChannel) 550 member2Result := member1Result 551 if member1.UserId != member2.UserId { 552 member2Result = s.saveMemberT(transaction, member2, newChannel) 553 } 554 555 if member1Result.Err != nil || member2Result.Err != nil { 556 details := "" 557 if member1Result.Err != nil { 558 details += "Member1Err: " + member1Result.Err.Message 559 } 560 if member2Result.Err != nil { 561 details += "Member2Err: " + member2Result.Err.Message 562 } 563 return nil, model.NewAppError("SqlChannelStore.SaveDirectChannel", "store.sql_channel.save_direct_channel.add_members.app_error", nil, details, http.StatusInternalServerError) 564 } 565 566 if err := transaction.Commit(); err != nil { 567 return nil, model.NewAppError("SqlChannelStore.SaveDirectChannel", "store.sql_channel.save_direct_channel.commit.app_error", nil, err.Error(), http.StatusInternalServerError) 568 } 569 570 return newChannel, nil 571 572 } 573 574 func (s SqlChannelStore) saveChannelT(transaction *gorp.Transaction, channel *model.Channel, maxChannelsPerTeam int64) store.StoreResult { 575 result := store.StoreResult{} 576 577 if len(channel.Id) > 0 { 578 result.Err = model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save_channel.existing.app_error", nil, "id="+channel.Id, http.StatusBadRequest) 579 return result 580 } 581 582 channel.PreSave() 583 if result.Err = channel.IsValid(); result.Err != nil { 584 return result 585 } 586 587 if channel.Type != model.CHANNEL_DIRECT && channel.Type != model.CHANNEL_GROUP && maxChannelsPerTeam >= 0 { 588 if count, err := transaction.SelectInt("SELECT COUNT(0) FROM Channels WHERE TeamId = :TeamId AND DeleteAt = 0 AND (Type = 'O' OR Type = 'P')", map[string]interface{}{"TeamId": channel.TeamId}); err != nil { 589 result.Err = model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save_channel.current_count.app_error", nil, "teamId="+channel.TeamId+", "+err.Error(), http.StatusInternalServerError) 590 return result 591 } else if count >= maxChannelsPerTeam { 592 result.Err = model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save_channel.limit.app_error", nil, "teamId="+channel.TeamId, http.StatusBadRequest) 593 return result 594 } 595 } 596 597 if err := transaction.Insert(channel); err != nil { 598 if IsUniqueConstraintError(err, []string{"Name", "channels_name_teamid_key"}) { 599 dupChannel := model.Channel{} 600 s.GetMaster().SelectOne(&dupChannel, "SELECT * FROM Channels WHERE TeamId = :TeamId AND Name = :Name", map[string]interface{}{"TeamId": channel.TeamId, "Name": channel.Name}) 601 if dupChannel.DeleteAt > 0 { 602 result.Err = model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save_channel.previously.app_error", nil, "id="+channel.Id+", "+err.Error(), http.StatusBadRequest) 603 } else { 604 result.Err = model.NewAppError("SqlChannelStore.Save", store.CHANNEL_EXISTS_ERROR, nil, "id="+channel.Id+", "+err.Error(), http.StatusBadRequest) 605 result.Data = &dupChannel 606 } 607 } else { 608 result.Err = model.NewAppError("SqlChannelStore.Save", "store.sql_channel.save_channel.save.app_error", nil, "id="+channel.Id+", "+err.Error(), http.StatusInternalServerError) 609 } 610 } else { 611 result.Data = channel 612 } 613 614 return result 615 } 616 617 // Update writes the updated channel to the database. 618 func (s SqlChannelStore) Update(channel *model.Channel) (*model.Channel, *model.AppError) { 619 transaction, err := s.GetMaster().Begin() 620 if err != nil { 621 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 622 } 623 defer finalizeTransaction(transaction) 624 625 updatedChannel, appErr := s.updateChannelT(transaction, channel) 626 if appErr != nil { 627 return nil, appErr 628 } 629 630 // Additionally propagate the write to the PublicChannels table. 631 if err := s.upsertPublicChannelT(transaction, updatedChannel); err != nil { 632 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.upsert_public_channel.app_error", nil, err.Error(), http.StatusInternalServerError) 633 } 634 635 if err := transaction.Commit(); err != nil { 636 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 637 } 638 return updatedChannel, nil 639 } 640 641 func (s SqlChannelStore) updateChannelT(transaction *gorp.Transaction, channel *model.Channel) (*model.Channel, *model.AppError) { 642 channel.PreUpdate() 643 644 if channel.DeleteAt != 0 { 645 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.archived_channel.app_error", nil, "", http.StatusBadRequest) 646 } 647 648 if err := channel.IsValid(); err != nil { 649 return nil, err 650 } 651 652 count, err := transaction.Update(channel) 653 if err != nil { 654 if IsUniqueConstraintError(err, []string{"Name", "channels_name_teamid_key"}) { 655 dupChannel := model.Channel{} 656 s.GetReplica().SelectOne(&dupChannel, "SELECT * FROM Channels WHERE TeamId = :TeamId AND Name= :Name AND DeleteAt > 0", map[string]interface{}{"TeamId": channel.TeamId, "Name": channel.Name}) 657 if dupChannel.DeleteAt > 0 { 658 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.previously.app_error", nil, "id="+channel.Id+", "+err.Error(), http.StatusBadRequest) 659 } 660 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.exists.app_error", nil, "id="+channel.Id+", "+err.Error(), http.StatusBadRequest) 661 } 662 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.updating.app_error", nil, "id="+channel.Id+", "+err.Error(), http.StatusInternalServerError) 663 } 664 665 if count != 1 { 666 return nil, model.NewAppError("SqlChannelStore.Update", "store.sql_channel.update.app_error", nil, "id="+channel.Id, http.StatusInternalServerError) 667 } 668 669 return channel, nil 670 } 671 672 func (s SqlChannelStore) GetChannelUnread(channelId, userId string) (*model.ChannelUnread, *model.AppError) { 673 var unreadChannel model.ChannelUnread 674 err := s.GetReplica().SelectOne(&unreadChannel, 675 `SELECT 676 Channels.TeamId TeamId, Channels.Id ChannelId, (Channels.TotalMsgCount - ChannelMembers.MsgCount) MsgCount, ChannelMembers.MentionCount MentionCount, ChannelMembers.NotifyProps NotifyProps 677 FROM 678 Channels, ChannelMembers 679 WHERE 680 Id = ChannelId 681 AND Id = :ChannelId 682 AND UserId = :UserId 683 AND DeleteAt = 0`, 684 map[string]interface{}{"ChannelId": channelId, "UserId": userId}) 685 686 if err != nil { 687 if err == sql.ErrNoRows { 688 return nil, model.NewAppError("SqlChannelStore.GetChannelUnread", "store.sql_channel.get_unread.app_error", nil, "channelId="+channelId+" "+err.Error(), http.StatusNotFound) 689 } 690 return nil, model.NewAppError("SqlChannelStore.GetChannelUnread", "store.sql_channel.get_unread.app_error", nil, "channelId="+channelId+" "+err.Error(), http.StatusInternalServerError) 691 } 692 return &unreadChannel, nil 693 } 694 695 func (s SqlChannelStore) InvalidateChannel(id string) { 696 channelCache.Remove(id) 697 if s.metrics != nil { 698 s.metrics.IncrementMemCacheInvalidationCounter("Channel - Remove by ChannelId") 699 } 700 } 701 702 func (s SqlChannelStore) InvalidateChannelByName(teamId, name string) { 703 channelByNameCache.Remove(teamId + name) 704 if s.metrics != nil { 705 s.metrics.IncrementMemCacheInvalidationCounter("Channel by Name - Remove by TeamId and Name") 706 } 707 } 708 709 func (s SqlChannelStore) Get(id string, allowFromCache bool) (*model.Channel, *model.AppError) { 710 return s.get(id, false, allowFromCache) 711 } 712 713 func (s SqlChannelStore) GetPinnedPosts(channelId string) (*model.PostList, *model.AppError) { 714 pl := model.NewPostList() 715 716 var posts []*model.Post 717 if _, err := s.GetReplica().Select(&posts, "SELECT * FROM Posts WHERE IsPinned = true AND ChannelId = :ChannelId AND DeleteAt = 0 ORDER BY CreateAt ASC", map[string]interface{}{"ChannelId": channelId}); err != nil { 718 return nil, model.NewAppError("SqlPostStore.GetPinnedPosts", "store.sql_channel.pinned_posts.app_error", nil, err.Error(), http.StatusInternalServerError) 719 } 720 for _, post := range posts { 721 pl.AddPost(post) 722 pl.AddOrder(post.Id) 723 } 724 return pl, nil 725 } 726 727 func (s SqlChannelStore) GetFromMaster(id string) (*model.Channel, *model.AppError) { 728 return s.get(id, true, false) 729 } 730 731 func (s SqlChannelStore) get(id string, master bool, allowFromCache bool) (*model.Channel, *model.AppError) { 732 var db *gorp.DbMap 733 734 if master { 735 db = s.GetMaster() 736 } else { 737 db = s.GetReplica() 738 } 739 740 if allowFromCache { 741 if cacheItem, ok := channelCache.Get(id); ok { 742 if s.metrics != nil { 743 s.metrics.IncrementMemCacheHitCounter("Channel") 744 } 745 ch := cacheItem.(*model.Channel).DeepCopy() 746 return ch, nil 747 } 748 } 749 750 if s.metrics != nil { 751 s.metrics.IncrementMemCacheMissCounter("Channel") 752 } 753 754 obj, err := db.Get(model.Channel{}, id) 755 if err != nil { 756 return nil, model.NewAppError("SqlChannelStore.Get", "store.sql_channel.get.find.app_error", nil, "id="+id+", "+err.Error(), http.StatusInternalServerError) 757 } 758 759 if obj == nil { 760 return nil, model.NewAppError("SqlChannelStore.Get", "store.sql_channel.get.existing.app_error", nil, "id="+id, http.StatusNotFound) 761 } 762 763 ch := obj.(*model.Channel) 764 channelCache.AddWithExpiresInSecs(id, ch, CHANNEL_CACHE_SEC) 765 return ch, nil 766 } 767 768 // Delete records the given deleted timestamp to the channel in question. 769 func (s SqlChannelStore) Delete(channelId string, time int64) *model.AppError { 770 return s.SetDeleteAt(channelId, time, time) 771 } 772 773 // Restore reverts a previous deleted timestamp from the channel in question. 774 func (s SqlChannelStore) Restore(channelId string, time int64) *model.AppError { 775 return s.SetDeleteAt(channelId, 0, time) 776 } 777 778 // SetDeleteAt records the given deleted and updated timestamp to the channel in question. 779 func (s SqlChannelStore) SetDeleteAt(channelId string, deleteAt, updateAt int64) *model.AppError { 780 defer s.InvalidateChannel(channelId) 781 782 transaction, err := s.GetMaster().Begin() 783 if err != nil { 784 return model.NewAppError("SqlChannelStore.SetDeleteAt", "store.sql_channel.set_delete_at.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 785 } 786 defer finalizeTransaction(transaction) 787 788 var result = s.setDeleteAtT(transaction, channelId, deleteAt, updateAt) 789 if result.Err != nil { 790 return result.Err 791 } 792 793 // Additionally propagate the write to the PublicChannels table. 794 if _, err := transaction.Exec(` 795 UPDATE 796 PublicChannels 797 SET 798 DeleteAt = :DeleteAt 799 WHERE 800 Id = :ChannelId 801 `, map[string]interface{}{ 802 "DeleteAt": deleteAt, 803 "ChannelId": channelId, 804 }); err != nil { 805 return model.NewAppError("SqlChannelStore.SetDeleteAt", "store.sql_channel.set_delete_at.update_public_channel.app_error", nil, "channel_id="+channelId+", "+err.Error(), http.StatusInternalServerError) 806 } 807 808 if err := transaction.Commit(); err != nil { 809 return model.NewAppError("SqlChannelStore.SetDeleteAt", "store.sql_channel.set_delete_at.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 810 } 811 812 return nil 813 } 814 815 func (s SqlChannelStore) setDeleteAtT(transaction *gorp.Transaction, channelId string, deleteAt, updateAt int64) store.StoreResult { 816 result := store.StoreResult{} 817 818 _, err := transaction.Exec("Update Channels SET DeleteAt = :DeleteAt, UpdateAt = :UpdateAt WHERE Id = :ChannelId", map[string]interface{}{"DeleteAt": deleteAt, "UpdateAt": updateAt, "ChannelId": channelId}) 819 if err != nil { 820 result.Err = model.NewAppError("SqlChannelStore.Delete", "store.sql_channel.delete.channel.app_error", nil, "id="+channelId+", err="+err.Error(), http.StatusInternalServerError) 821 return result 822 } 823 824 return result 825 } 826 827 // PermanentDeleteByTeam removes all channels for the given team from the database. 828 func (s SqlChannelStore) PermanentDeleteByTeam(teamId string) store.StoreChannel { 829 return store.Do(func(result *store.StoreResult) { 830 transaction, err := s.GetMaster().Begin() 831 if err != nil { 832 result.Err = model.NewAppError("SqlChannelStore.PermanentDeleteByTeam", "store.sql_channel.permanent_delete_by_team.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 833 return 834 } 835 defer finalizeTransaction(transaction) 836 837 *result = s.permanentDeleteByTeamtT(transaction, teamId) 838 if result.Err != nil { 839 return 840 } 841 842 // Additionally propagate the deletions to the PublicChannels table. 843 if _, err := transaction.Exec(` 844 DELETE FROM 845 PublicChannels 846 WHERE 847 TeamId = :TeamId 848 `, map[string]interface{}{ 849 "TeamId": teamId, 850 }); err != nil { 851 result.Err = model.NewAppError("SqlChannelStore.PermanentDeleteByTeamt", "store.sql_channel.permanent_delete_by_team.delete_public_channels.app_error", nil, "team_id="+teamId+", "+err.Error(), http.StatusInternalServerError) 852 return 853 } 854 855 if err := transaction.Commit(); err != nil { 856 result.Err = model.NewAppError("SqlChannelStore.PermanentDeleteByTeam", "store.sql_channel.permanent_delete_by_team.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 857 return 858 } 859 }) 860 } 861 862 func (s SqlChannelStore) permanentDeleteByTeamtT(transaction *gorp.Transaction, teamId string) store.StoreResult { 863 result := store.StoreResult{} 864 865 if _, err := transaction.Exec("DELETE FROM Channels WHERE TeamId = :TeamId", map[string]interface{}{"TeamId": teamId}); err != nil { 866 result.Err = model.NewAppError("SqlChannelStore.PermanentDeleteByTeam", "store.sql_channel.permanent_delete_by_team.app_error", nil, "teamId="+teamId+", "+err.Error(), http.StatusInternalServerError) 867 return result 868 } 869 870 return result 871 } 872 873 // PermanentDelete removes the given channel from the database. 874 func (s SqlChannelStore) PermanentDelete(channelId string) store.StoreChannel { 875 return store.Do(func(result *store.StoreResult) { 876 transaction, err := s.GetMaster().Begin() 877 if err != nil { 878 result.Err = model.NewAppError("SqlChannelStore.PermanentDelete", "store.sql_channel.permanent_delete.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 879 return 880 } 881 defer finalizeTransaction(transaction) 882 883 *result = s.permanentDeleteT(transaction, channelId) 884 if result.Err != nil { 885 return 886 } 887 888 // Additionally propagate the deletion to the PublicChannels table. 889 if _, err := transaction.Exec(` 890 DELETE FROM 891 PublicChannels 892 WHERE 893 Id = :ChannelId 894 `, map[string]interface{}{ 895 "ChannelId": channelId, 896 }); err != nil { 897 result.Err = model.NewAppError("SqlChannelStore.PermanentDelete", "store.sql_channel.permanent_delete.delete_public_channel.app_error", nil, "channel_id="+channelId+", "+err.Error(), http.StatusInternalServerError) 898 return 899 } 900 901 if err := transaction.Commit(); err != nil { 902 result.Err = model.NewAppError("SqlChannelStore.PermanentDelete", "store.sql_channel.permanent_delete.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 903 return 904 } 905 }) 906 } 907 908 func (s SqlChannelStore) permanentDeleteT(transaction *gorp.Transaction, channelId string) store.StoreResult { 909 result := store.StoreResult{} 910 911 if _, err := transaction.Exec("DELETE FROM Channels WHERE Id = :ChannelId", map[string]interface{}{"ChannelId": channelId}); err != nil { 912 result.Err = model.NewAppError("SqlChannelStore.PermanentDelete", "store.sql_channel.permanent_delete.app_error", nil, "channel_id="+channelId+", "+err.Error(), http.StatusInternalServerError) 913 return result 914 } 915 916 return result 917 } 918 919 func (s SqlChannelStore) PermanentDeleteMembersByChannel(channelId string) *model.AppError { 920 _, err := s.GetMaster().Exec("DELETE FROM ChannelMembers WHERE ChannelId = :ChannelId", map[string]interface{}{"ChannelId": channelId}) 921 if err != nil { 922 return model.NewAppError("SqlChannelStore.RemoveAllMembersByChannel", "store.sql_channel.remove_member.app_error", nil, "channel_id="+channelId+", "+err.Error(), http.StatusInternalServerError) 923 } 924 925 return nil 926 } 927 928 func (s SqlChannelStore) GetChannels(teamId string, userId string, includeDeleted bool) (*model.ChannelList, *model.AppError) { 929 query := "SELECT Channels.* FROM Channels, ChannelMembers WHERE Id = ChannelId AND UserId = :UserId AND DeleteAt = 0 AND (TeamId = :TeamId OR TeamId = '') ORDER BY DisplayName" 930 if includeDeleted { 931 query = "SELECT Channels.* FROM Channels, ChannelMembers WHERE Id = ChannelId AND UserId = :UserId AND (TeamId = :TeamId OR TeamId = '') ORDER BY DisplayName" 932 } 933 channels := &model.ChannelList{} 934 _, err := s.GetReplica().Select(channels, query, map[string]interface{}{"TeamId": teamId, "UserId": userId}) 935 936 if err != nil { 937 return nil, model.NewAppError("SqlChannelStore.GetChannels", "store.sql_channel.get_channels.get.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError) 938 } 939 940 if len(*channels) == 0 { 941 return nil, model.NewAppError("SqlChannelStore.GetChannels", "store.sql_channel.get_channels.not_found.app_error", nil, "teamId="+teamId+", userId="+userId, http.StatusBadRequest) 942 } 943 944 return channels, nil 945 } 946 947 func (s SqlChannelStore) GetAllChannels(offset, limit int, opts store.ChannelSearchOpts) (*model.ChannelListWithTeamData, *model.AppError) { 948 query := s.getAllChannelsQuery(opts, false) 949 950 query = query.OrderBy("c.DisplayName, Teams.DisplayName").Limit(uint64(limit)).Offset(uint64(offset)) 951 952 queryString, args, err := query.ToSql() 953 if err != nil { 954 return nil, model.NewAppError("SqlChannelStore.GetAllChannels", "store.sql.build_query.app_error", nil, err.Error(), http.StatusInternalServerError) 955 } 956 957 data := &model.ChannelListWithTeamData{} 958 _, err = s.GetReplica().Select(data, queryString, args...) 959 960 if err != nil { 961 return nil, model.NewAppError("SqlChannelStore.GetAllChannels", "store.sql_channel.get_all_channels.get.app_error", nil, err.Error(), http.StatusInternalServerError) 962 } 963 964 return data, nil 965 } 966 967 func (s SqlChannelStore) GetAllChannelsCount(opts store.ChannelSearchOpts) (int64, *model.AppError) { 968 query := s.getAllChannelsQuery(opts, true) 969 970 queryString, args, err := query.ToSql() 971 if err != nil { 972 return 0, model.NewAppError("SqlChannelStore.GetAllChannelsCount", "store.sql.build_query.app_error", nil, err.Error(), http.StatusInternalServerError) 973 } 974 975 count, err := s.GetReplica().SelectInt(queryString, args...) 976 if err != nil { 977 return 0, model.NewAppError("SqlChannelStore.GetAllChannelsCount", "store.sql_channel.get_all_channels.get.app_error", nil, err.Error(), http.StatusInternalServerError) 978 } 979 980 return count, nil 981 } 982 983 func (s SqlChannelStore) getAllChannelsQuery(opts store.ChannelSearchOpts, forCount bool) sq.SelectBuilder { 984 var selectStr string 985 if forCount { 986 selectStr = "count(c.Id)" 987 } else { 988 selectStr = "c.*, Teams.DisplayName AS TeamDisplayName, Teams.Name AS TeamName, Teams.UpdateAt AS TeamUpdateAt" 989 } 990 991 query := s.getQueryBuilder(). 992 Select(selectStr). 993 From("Channels AS c"). 994 Where(sq.Eq{"c.Type": []string{model.CHANNEL_PRIVATE, model.CHANNEL_OPEN}}) 995 996 if !forCount { 997 query = query.Join("Teams ON Teams.Id = c.TeamId") 998 } 999 1000 if !opts.IncludeDeleted { 1001 query = query.Where(sq.Eq{"c.DeleteAt": int(0)}) 1002 } 1003 1004 if len(opts.NotAssociatedToGroup) > 0 { 1005 query = query.Where("c.Id NOT IN (SELECT ChannelId FROM GroupChannels WHERE GroupChannels.GroupId = ? AND GroupChannels.DeleteAt = 0)", opts.NotAssociatedToGroup) 1006 } 1007 1008 if len(opts.ExcludeChannelNames) > 0 { 1009 query = query.Where(fmt.Sprintf("c.Name NOT IN ('%s')", strings.Join(opts.ExcludeChannelNames, "', '"))) 1010 } 1011 1012 return query 1013 } 1014 1015 func (s SqlChannelStore) GetMoreChannels(teamId string, userId string, offset int, limit int) (*model.ChannelList, *model.AppError) { 1016 channels := &model.ChannelList{} 1017 _, err := s.GetReplica().Select(channels, ` 1018 SELECT 1019 Channels.* 1020 FROM 1021 Channels 1022 JOIN 1023 PublicChannels c ON (c.Id = Channels.Id) 1024 WHERE 1025 c.TeamId = :TeamId 1026 AND c.DeleteAt = 0 1027 AND c.Id NOT IN ( 1028 SELECT 1029 c.Id 1030 FROM 1031 PublicChannels c 1032 JOIN 1033 ChannelMembers cm ON (cm.ChannelId = c.Id) 1034 WHERE 1035 c.TeamId = :TeamId 1036 AND cm.UserId = :UserId 1037 AND c.DeleteAt = 0 1038 ) 1039 ORDER BY 1040 c.DisplayName 1041 LIMIT :Limit 1042 OFFSET :Offset 1043 `, map[string]interface{}{ 1044 "TeamId": teamId, 1045 "UserId": userId, 1046 "Limit": limit, 1047 "Offset": offset, 1048 }) 1049 1050 if err != nil { 1051 return nil, model.NewAppError("SqlChannelStore.GetMoreChannels", "store.sql_channel.get_more_channels.get.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError) 1052 } 1053 1054 return channels, nil 1055 } 1056 1057 func (s SqlChannelStore) GetPublicChannelsForTeam(teamId string, offset int, limit int) (*model.ChannelList, *model.AppError) { 1058 channels := &model.ChannelList{} 1059 _, err := s.GetReplica().Select(channels, ` 1060 SELECT 1061 Channels.* 1062 FROM 1063 Channels 1064 JOIN 1065 PublicChannels pc ON (pc.Id = Channels.Id) 1066 WHERE 1067 pc.TeamId = :TeamId 1068 AND pc.DeleteAt = 0 1069 ORDER BY pc.DisplayName 1070 LIMIT :Limit 1071 OFFSET :Offset 1072 `, map[string]interface{}{ 1073 "TeamId": teamId, 1074 "Limit": limit, 1075 "Offset": offset, 1076 }) 1077 1078 if err != nil { 1079 return nil, model.NewAppError("SqlChannelStore.GetPublicChannelsForTeam", "store.sql_channel.get_public_channels.get.app_error", nil, "teamId="+teamId+", err="+err.Error(), http.StatusInternalServerError) 1080 } 1081 1082 return channels, nil 1083 } 1084 1085 func (s SqlChannelStore) GetPublicChannelsByIdsForTeam(teamId string, channelIds []string) (*model.ChannelList, *model.AppError) { 1086 props := make(map[string]interface{}) 1087 props["teamId"] = teamId 1088 1089 idQuery := "" 1090 1091 for index, channelId := range channelIds { 1092 if len(idQuery) > 0 { 1093 idQuery += ", " 1094 } 1095 1096 props["channelId"+strconv.Itoa(index)] = channelId 1097 idQuery += ":channelId" + strconv.Itoa(index) 1098 } 1099 1100 data := &model.ChannelList{} 1101 _, err := s.GetReplica().Select(data, ` 1102 SELECT 1103 Channels.* 1104 FROM 1105 Channels 1106 JOIN 1107 PublicChannels pc ON (pc.Id = Channels.Id) 1108 WHERE 1109 pc.TeamId = :teamId 1110 AND pc.DeleteAt = 0 1111 AND pc.Id IN (`+idQuery+`) 1112 ORDER BY pc.DisplayName 1113 `, props) 1114 1115 if err != nil { 1116 return nil, model.NewAppError("SqlChannelStore.GetPublicChannelsByIdsForTeam", "store.sql_channel.get_channels_by_ids.get.app_error", nil, err.Error(), http.StatusInternalServerError) 1117 } 1118 1119 if len(*data) == 0 { 1120 return nil, model.NewAppError("SqlChannelStore.GetPublicChannelsByIdsForTeam", "store.sql_channel.get_channels_by_ids.not_found.app_error", nil, "", http.StatusNotFound) 1121 } 1122 1123 return data, nil 1124 } 1125 1126 type channelIdWithCountAndUpdateAt struct { 1127 Id string 1128 TotalMsgCount int64 1129 UpdateAt int64 1130 } 1131 1132 func (s SqlChannelStore) GetChannelCounts(teamId string, userId string) (*model.ChannelCounts, *model.AppError) { 1133 var data []channelIdWithCountAndUpdateAt 1134 _, err := s.GetReplica().Select(&data, "SELECT Id, TotalMsgCount, UpdateAt FROM Channels WHERE Id IN (SELECT ChannelId FROM ChannelMembers WHERE UserId = :UserId) AND (TeamId = :TeamId OR TeamId = '') AND DeleteAt = 0 ORDER BY DisplayName", map[string]interface{}{"TeamId": teamId, "UserId": userId}) 1135 1136 if err != nil { 1137 return nil, model.NewAppError("SqlChannelStore.GetChannelCounts", "store.sql_channel.get_channel_counts.get.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError) 1138 } 1139 1140 counts := &model.ChannelCounts{Counts: make(map[string]int64), UpdateTimes: make(map[string]int64)} 1141 for i := range data { 1142 v := data[i] 1143 counts.Counts[v.Id] = v.TotalMsgCount 1144 counts.UpdateTimes[v.Id] = v.UpdateAt 1145 } 1146 1147 return counts, nil 1148 } 1149 1150 func (s SqlChannelStore) GetTeamChannels(teamId string) (*model.ChannelList, *model.AppError) { 1151 data := &model.ChannelList{} 1152 _, err := s.GetReplica().Select(data, "SELECT * FROM Channels WHERE TeamId = :TeamId And Type != 'D' ORDER BY DisplayName", map[string]interface{}{"TeamId": teamId}) 1153 1154 if err != nil { 1155 return nil, model.NewAppError("SqlChannelStore.GetTeamChannels", "store.sql_channel.get_channels.get.app_error", nil, "teamId="+teamId+", err="+err.Error(), http.StatusInternalServerError) 1156 } 1157 1158 if len(*data) == 0 { 1159 return nil, model.NewAppError("SqlChannelStore.GetTeamChannels", "store.sql_channel.get_channels.not_found.app_error", nil, "teamId="+teamId, http.StatusNotFound) 1160 } 1161 1162 return data, nil 1163 } 1164 1165 func (s SqlChannelStore) GetByName(teamId string, name string, allowFromCache bool) (*model.Channel, *model.AppError) { 1166 return s.getByName(teamId, name, false, allowFromCache) 1167 } 1168 1169 func (s SqlChannelStore) GetByNames(teamId string, names []string, allowFromCache bool) ([]*model.Channel, *model.AppError) { 1170 var channels []*model.Channel 1171 1172 if allowFromCache { 1173 var misses []string 1174 visited := make(map[string]struct{}) 1175 for _, name := range names { 1176 if _, ok := visited[name]; ok { 1177 continue 1178 } 1179 visited[name] = struct{}{} 1180 if cacheItem, ok := channelByNameCache.Get(teamId + name); ok { 1181 if s.metrics != nil { 1182 s.metrics.IncrementMemCacheHitCounter("Channel By Name") 1183 } 1184 channels = append(channels, cacheItem.(*model.Channel)) 1185 } else { 1186 if s.metrics != nil { 1187 s.metrics.IncrementMemCacheMissCounter("Channel By Name") 1188 } 1189 misses = append(misses, name) 1190 } 1191 } 1192 names = misses 1193 } 1194 1195 if len(names) > 0 { 1196 props := map[string]interface{}{} 1197 var namePlaceholders []string 1198 for _, name := range names { 1199 key := fmt.Sprintf("Name%v", len(namePlaceholders)) 1200 props[key] = name 1201 namePlaceholders = append(namePlaceholders, ":"+key) 1202 } 1203 1204 var query string 1205 if teamId == "" { 1206 query = `SELECT * FROM Channels WHERE Name IN (` + strings.Join(namePlaceholders, ", ") + `) AND DeleteAt = 0` 1207 } else { 1208 props["TeamId"] = teamId 1209 query = `SELECT * FROM Channels WHERE Name IN (` + strings.Join(namePlaceholders, ", ") + `) AND TeamId = :TeamId AND DeleteAt = 0` 1210 } 1211 1212 var dbChannels []*model.Channel 1213 if _, err := s.GetReplica().Select(&dbChannels, query, props); err != nil && err != sql.ErrNoRows { 1214 return nil, model.NewAppError("SqlChannelStore.GetByName", "store.sql_channel.get_by_name.existing.app_error", nil, "teamId="+teamId+", "+err.Error(), http.StatusInternalServerError) 1215 } 1216 for _, channel := range dbChannels { 1217 channelByNameCache.AddWithExpiresInSecs(teamId+channel.Name, channel, CHANNEL_CACHE_SEC) 1218 channels = append(channels, channel) 1219 } 1220 } 1221 1222 return channels, nil 1223 } 1224 1225 func (s SqlChannelStore) GetByNameIncludeDeleted(teamId string, name string, allowFromCache bool) (*model.Channel, *model.AppError) { 1226 return s.getByName(teamId, name, true, allowFromCache) 1227 } 1228 1229 func (s SqlChannelStore) getByName(teamId string, name string, includeDeleted bool, allowFromCache bool) (*model.Channel, *model.AppError) { 1230 var query string 1231 if includeDeleted { 1232 query = "SELECT * FROM Channels WHERE (TeamId = :TeamId OR TeamId = '') AND Name = :Name" 1233 } else { 1234 query = "SELECT * FROM Channels WHERE (TeamId = :TeamId OR TeamId = '') AND Name = :Name AND DeleteAt = 0" 1235 } 1236 channel := model.Channel{} 1237 1238 if allowFromCache { 1239 if cacheItem, ok := channelByNameCache.Get(teamId + name); ok { 1240 if s.metrics != nil { 1241 s.metrics.IncrementMemCacheHitCounter("Channel By Name") 1242 } 1243 return cacheItem.(*model.Channel), nil 1244 } 1245 if s.metrics != nil { 1246 s.metrics.IncrementMemCacheMissCounter("Channel By Name") 1247 } 1248 } 1249 1250 if err := s.GetReplica().SelectOne(&channel, query, map[string]interface{}{"TeamId": teamId, "Name": name}); err != nil { 1251 if err == sql.ErrNoRows { 1252 return nil, model.NewAppError("SqlChannelStore.GetByName", store.MISSING_CHANNEL_ERROR, nil, "teamId="+teamId+", "+"name="+name+", "+err.Error(), http.StatusNotFound) 1253 } 1254 return nil, model.NewAppError("SqlChannelStore.GetByName", "store.sql_channel.get_by_name.existing.app_error", nil, "teamId="+teamId+", "+"name="+name+", "+err.Error(), http.StatusInternalServerError) 1255 } 1256 1257 channelByNameCache.AddWithExpiresInSecs(teamId+name, &channel, CHANNEL_CACHE_SEC) 1258 return &channel, nil 1259 } 1260 1261 1262 func (s SqlChannelStore) GetByDisplayName(teamId string, displayName string) (*model.Channel, *model.AppError) { 1263 var query string 1264 1265 query = "SELECT * FROM Channels WHERE TeamId = :TeamId AND DisplayName = :DisplayName" 1266 1267 channel := model.Channel{} 1268 1269 1270 1271 if err := s.GetReplica().SelectOne(&channel, query, map[string]interface{}{"TeamId": teamId, "DisplayName": displayName}); err != nil { 1272 if err == sql.ErrNoRows { 1273 return nil, model.NewAppError("SqlChannelStore.GetByName", store.MISSING_CHANNEL_ERROR, nil, "teamId="+teamId+", "+"displayName="+displayName+", "+err.Error(), http.StatusNotFound) 1274 } 1275 return nil, model.NewAppError("SqlChannelStore.GetByName", "store.sql_channel.get_by_name.existing.app_error", nil, "teamId="+teamId+", "+"displayName="+displayName+", "+err.Error(), http.StatusInternalServerError) 1276 } 1277 1278 return &channel, nil 1279 } 1280 1281 1282 1283 1284 1285 func (s SqlChannelStore) GetDeletedByName(teamId string, name string) (*model.Channel, *model.AppError) { 1286 channel := model.Channel{} 1287 1288 if err := s.GetReplica().SelectOne(&channel, "SELECT * FROM Channels WHERE (TeamId = :TeamId OR TeamId = '') AND Name = :Name AND DeleteAt != 0", map[string]interface{}{"TeamId": teamId, "Name": name}); err != nil { 1289 if err == sql.ErrNoRows { 1290 return nil, model.NewAppError("SqlChannelStore.GetDeletedByName", "store.sql_channel.get_deleted_by_name.missing.app_error", nil, "teamId="+teamId+", "+"name="+name+", "+err.Error(), http.StatusNotFound) 1291 } 1292 return nil, model.NewAppError("SqlChannelStore.GetDeletedByName", "store.sql_channel.get_deleted_by_name.existing.app_error", nil, "teamId="+teamId+", "+"name="+name+", "+err.Error(), http.StatusInternalServerError) 1293 } 1294 1295 return &channel, nil 1296 } 1297 1298 func (s SqlChannelStore) GetDeleted(teamId string, offset int, limit int) (*model.ChannelList, *model.AppError) { 1299 channels := &model.ChannelList{} 1300 1301 if _, err := s.GetReplica().Select(channels, "SELECT * FROM Channels WHERE (TeamId = :TeamId OR TeamId = '') AND DeleteAt != 0 ORDER BY DisplayName LIMIT :Limit OFFSET :Offset", map[string]interface{}{"TeamId": teamId, "Limit": limit, "Offset": offset}); err != nil { 1302 if err == sql.ErrNoRows { 1303 return nil, model.NewAppError("SqlChannelStore.GetDeleted", "store.sql_channel.get_deleted.missing.app_error", nil, "teamId="+teamId+", "+err.Error(), http.StatusNotFound) 1304 } 1305 return nil, model.NewAppError("SqlChannelStore.GetDeleted", "store.sql_channel.get_deleted.existing.app_error", nil, "teamId="+teamId+", "+err.Error(), http.StatusInternalServerError) 1306 } 1307 1308 return channels, nil 1309 } 1310 1311 var CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY = ` 1312 SELECT 1313 ChannelMembers.*, 1314 TeamScheme.DefaultChannelGuestRole TeamSchemeDefaultGuestRole, 1315 TeamScheme.DefaultChannelUserRole TeamSchemeDefaultUserRole, 1316 TeamScheme.DefaultChannelAdminRole TeamSchemeDefaultAdminRole, 1317 ChannelScheme.DefaultChannelGuestRole ChannelSchemeDefaultGuestRole, 1318 ChannelScheme.DefaultChannelUserRole ChannelSchemeDefaultUserRole, 1319 ChannelScheme.DefaultChannelAdminRole ChannelSchemeDefaultAdminRole 1320 FROM 1321 ChannelMembers 1322 INNER JOIN 1323 Channels ON ChannelMembers.ChannelId = Channels.Id 1324 LEFT JOIN 1325 Schemes ChannelScheme ON Channels.SchemeId = ChannelScheme.Id 1326 LEFT JOIN 1327 Teams ON Channels.TeamId = Teams.Id 1328 LEFT JOIN 1329 Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id 1330 ` 1331 1332 func (s SqlChannelStore) SaveMember(member *model.ChannelMember) store.StoreChannel { 1333 return store.Do(func(result *store.StoreResult) { 1334 defer s.InvalidateAllChannelMembersForUser(member.UserId) 1335 1336 // Grab the channel we are saving this member to 1337 channel, errCh := s.GetFromMaster(member.ChannelId) 1338 if errCh != nil { 1339 result.Err = errCh 1340 return 1341 } 1342 1343 transaction, err := s.GetMaster().Begin() 1344 if err != nil { 1345 result.Err = model.NewAppError("SqlChannelStore.SaveMember", "store.sql_channel.save_member.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 1346 return 1347 } 1348 defer finalizeTransaction(transaction) 1349 1350 *result = s.saveMemberT(transaction, member, channel) 1351 if result.Err != nil { 1352 return 1353 } 1354 1355 if err := transaction.Commit(); err != nil { 1356 result.Err = model.NewAppError("SqlChannelStore.SaveMember", "store.sql_channel.save_member.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 1357 return 1358 } 1359 }) 1360 } 1361 1362 func (s SqlChannelStore) saveMemberT(transaction *gorp.Transaction, member *model.ChannelMember, channel *model.Channel) store.StoreResult { 1363 result := store.StoreResult{} 1364 1365 member.PreSave() 1366 if result.Err = member.IsValid(); result.Err != nil { 1367 return result 1368 } 1369 1370 dbMember := NewChannelMemberFromModel(member) 1371 1372 if err := transaction.Insert(dbMember); err != nil { 1373 if IsUniqueConstraintError(err, []string{"ChannelId", "channelmembers_pkey"}) { 1374 result.Err = model.NewAppError("SqlChannelStore.SaveMember", "store.sql_channel.save_member.exists.app_error", nil, "channel_id="+member.ChannelId+", user_id="+member.UserId+", "+err.Error(), http.StatusBadRequest) 1375 return result 1376 } 1377 result.Err = model.NewAppError("SqlChannelStore.SaveMember", "store.sql_channel.save_member.save.app_error", nil, "channel_id="+member.ChannelId+", user_id="+member.UserId+", "+err.Error(), http.StatusInternalServerError) 1378 return result 1379 } 1380 1381 var retrievedMember channelMemberWithSchemeRoles 1382 if err := transaction.SelectOne(&retrievedMember, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId = :UserId", map[string]interface{}{"ChannelId": dbMember.ChannelId, "UserId": dbMember.UserId}); err != nil { 1383 if err == sql.ErrNoRows { 1384 result.Err = model.NewAppError("SqlChannelStore.GetMember", store.MISSING_CHANNEL_MEMBER_ERROR, nil, "channel_id="+dbMember.ChannelId+"user_id="+dbMember.UserId+","+err.Error(), http.StatusNotFound) 1385 return result 1386 } 1387 result.Err = model.NewAppError("SqlChannelStore.GetMember", "store.sql_channel.get_member.app_error", nil, "channel_id="+dbMember.ChannelId+"user_id="+dbMember.UserId+","+err.Error(), http.StatusInternalServerError) 1388 return result 1389 } 1390 1391 result.Data = retrievedMember.ToModel() 1392 return result 1393 } 1394 1395 func (s SqlChannelStore) UpdateMember(member *model.ChannelMember) (*model.ChannelMember, *model.AppError) { 1396 member.PreUpdate() 1397 1398 if err := member.IsValid(); err != nil { 1399 return nil, err 1400 } 1401 1402 if _, err := s.GetMaster().Update(NewChannelMemberFromModel(member)); err != nil { 1403 return nil, model.NewAppError("SqlChannelStore.UpdateMember", "store.sql_channel.update_member.app_error", nil, "channel_id="+member.ChannelId+", "+"user_id="+member.UserId+", "+err.Error(), http.StatusInternalServerError) 1404 } 1405 1406 var dbMember channelMemberWithSchemeRoles 1407 1408 if err := s.GetReplica().SelectOne(&dbMember, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId = :UserId", map[string]interface{}{"ChannelId": member.ChannelId, "UserId": member.UserId}); err != nil { 1409 if err == sql.ErrNoRows { 1410 return nil, model.NewAppError("SqlChannelStore.GetMember", store.MISSING_CHANNEL_MEMBER_ERROR, nil, "channel_id="+member.ChannelId+"user_id="+member.UserId+","+err.Error(), http.StatusNotFound) 1411 } 1412 return nil, model.NewAppError("SqlChannelStore.GetMember", "store.sql_channel.get_member.app_error", nil, "channel_id="+member.ChannelId+"user_id="+member.UserId+","+err.Error(), http.StatusInternalServerError) 1413 } 1414 return dbMember.ToModel(), nil 1415 } 1416 1417 func (s SqlChannelStore) GetMembers(channelId string, offset, limit int) (*model.ChannelMembers, *model.AppError) { 1418 var dbMembers channelMemberWithSchemeRolesList 1419 _, err := s.GetReplica().Select(&dbMembers, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelId = :ChannelId LIMIT :Limit OFFSET :Offset", map[string]interface{}{"ChannelId": channelId, "Limit": limit, "Offset": offset}) 1420 if err != nil { 1421 return nil, model.NewAppError("SqlChannelStore.GetMembers", "store.sql_channel.get_members.app_error", nil, "channel_id="+channelId+","+err.Error(), http.StatusInternalServerError) 1422 } 1423 1424 return dbMembers.ToModel(), nil 1425 } 1426 1427 func (s SqlChannelStore) GetChannelMembersTimezones(channelId string) ([]model.StringMap, *model.AppError) { 1428 var dbMembersTimezone []model.StringMap 1429 _, err := s.GetReplica().Select(&dbMembersTimezone, ` 1430 SELECT 1431 Users.Timezone 1432 FROM 1433 ChannelMembers 1434 LEFT JOIN 1435 Users ON ChannelMembers.UserId = Id 1436 WHERE ChannelId = :ChannelId 1437 `, map[string]interface{}{"ChannelId": channelId}) 1438 1439 if err != nil { 1440 return nil, model.NewAppError("SqlChannelStore.GetChannelMembersTimezones", "store.sql_channel.get_members.app_error", nil, "channel_id="+channelId+","+err.Error(), http.StatusInternalServerError) 1441 } 1442 1443 return dbMembersTimezone, nil 1444 } 1445 1446 func (s SqlChannelStore) GetMember(channelId string, userId string) (*model.ChannelMember, *model.AppError) { 1447 var dbMember channelMemberWithSchemeRoles 1448 1449 if err := s.GetReplica().SelectOne(&dbMember, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId = :UserId", map[string]interface{}{"ChannelId": channelId, "UserId": userId}); err != nil { 1450 if err == sql.ErrNoRows { 1451 return nil, model.NewAppError("SqlChannelStore.GetMember", store.MISSING_CHANNEL_MEMBER_ERROR, nil, "channel_id="+channelId+"user_id="+userId+","+err.Error(), http.StatusNotFound) 1452 } 1453 return nil, model.NewAppError("SqlChannelStore.GetMember", "store.sql_channel.get_member.app_error", nil, "channel_id="+channelId+"user_id="+userId+","+err.Error(), http.StatusInternalServerError) 1454 } 1455 1456 return dbMember.ToModel(), nil 1457 } 1458 1459 func (s SqlChannelStore) InvalidateAllChannelMembersForUser(userId string) { 1460 allChannelMembersForUserCache.Remove(userId) 1461 allChannelMembersForUserCache.Remove(userId + "_deleted") 1462 if s.metrics != nil { 1463 s.metrics.IncrementMemCacheInvalidationCounter("All Channel Members for User - Remove by UserId") 1464 } 1465 } 1466 1467 func (s SqlChannelStore) IsUserInChannelUseCache(userId string, channelId string) bool { 1468 if cacheItem, ok := allChannelMembersForUserCache.Get(userId); ok { 1469 if s.metrics != nil { 1470 s.metrics.IncrementMemCacheHitCounter("All Channel Members for User") 1471 } 1472 ids := cacheItem.(map[string]string) 1473 if _, ok := ids[channelId]; ok { 1474 return true 1475 } 1476 return false 1477 } 1478 1479 if s.metrics != nil { 1480 s.metrics.IncrementMemCacheMissCounter("All Channel Members for User") 1481 } 1482 1483 ids, err := s.GetAllChannelMembersForUser(userId, true, false) 1484 if err != nil { 1485 mlog.Error("SqlChannelStore.IsUserInChannelUseCache: " + err.Error()) 1486 return false 1487 } 1488 1489 if _, ok := ids[channelId]; ok { 1490 return true 1491 } 1492 1493 return false 1494 } 1495 1496 func (s SqlChannelStore) GetMemberForPost(postId string, userId string) (*model.ChannelMember, *model.AppError) { 1497 var dbMember channelMemberWithSchemeRoles 1498 query := ` 1499 SELECT 1500 ChannelMembers.*, 1501 TeamScheme.DefaultChannelGuestRole TeamSchemeDefaultGuestRole, 1502 TeamScheme.DefaultChannelUserRole TeamSchemeDefaultUserRole, 1503 TeamScheme.DefaultChannelAdminRole TeamSchemeDefaultAdminRole, 1504 ChannelScheme.DefaultChannelGuestRole ChannelSchemeDefaultGuestRole, 1505 ChannelScheme.DefaultChannelUserRole ChannelSchemeDefaultUserRole, 1506 ChannelScheme.DefaultChannelAdminRole ChannelSchemeDefaultAdminRole 1507 FROM 1508 ChannelMembers 1509 INNER JOIN 1510 Posts ON ChannelMembers.ChannelId = Posts.ChannelId 1511 INNER JOIN 1512 Channels ON ChannelMembers.ChannelId = Channels.Id 1513 LEFT JOIN 1514 Schemes ChannelScheme ON Channels.SchemeId = ChannelScheme.Id 1515 LEFT JOIN 1516 Teams ON Channels.TeamId = Teams.Id 1517 LEFT JOIN 1518 Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id 1519 WHERE 1520 ChannelMembers.UserId = :UserId 1521 AND 1522 Posts.Id = :PostId` 1523 if err := s.GetReplica().SelectOne(&dbMember, query, map[string]interface{}{"UserId": userId, "PostId": postId}); err != nil { 1524 return nil, model.NewAppError("SqlChannelStore.GetMemberForPost", "store.sql_channel.get_member_for_post.app_error", nil, "postId="+postId+", err="+err.Error(), http.StatusInternalServerError) 1525 } 1526 return dbMember.ToModel(), nil 1527 } 1528 1529 func (s SqlChannelStore) GetAllChannelMembersForUser(userId string, allowFromCache bool, includeDeleted bool) (map[string]string, *model.AppError) { 1530 cache_key := userId 1531 if includeDeleted { 1532 cache_key += "_deleted" 1533 } 1534 if allowFromCache { 1535 if cacheItem, ok := allChannelMembersForUserCache.Get(cache_key); ok { 1536 if s.metrics != nil { 1537 s.metrics.IncrementMemCacheHitCounter("All Channel Members for User") 1538 } 1539 ids := cacheItem.(map[string]string) 1540 return ids, nil 1541 } 1542 } 1543 1544 if s.metrics != nil { 1545 s.metrics.IncrementMemCacheMissCounter("All Channel Members for User") 1546 } 1547 1548 var deletedClause string 1549 if !includeDeleted { 1550 deletedClause = "Channels.DeleteAt = 0 AND" 1551 } 1552 1553 var data allChannelMembers 1554 _, err := s.GetReplica().Select(&data, ` 1555 SELECT 1556 ChannelMembers.ChannelId, ChannelMembers.Roles, 1557 ChannelMembers.SchemeGuest, ChannelMembers.SchemeUser, ChannelMembers.SchemeAdmin, 1558 TeamScheme.DefaultChannelGuestRole TeamSchemeDefaultGuestRole, 1559 TeamScheme.DefaultChannelUserRole TeamSchemeDefaultUserRole, 1560 TeamScheme.DefaultChannelAdminRole TeamSchemeDefaultAdminRole, 1561 ChannelScheme.DefaultChannelGuestRole ChannelSchemeDefaultGuestRole, 1562 ChannelScheme.DefaultChannelUserRole ChannelSchemeDefaultUserRole, 1563 ChannelScheme.DefaultChannelAdminRole ChannelSchemeDefaultAdminRole 1564 FROM 1565 ChannelMembers 1566 INNER JOIN 1567 Channels ON ChannelMembers.ChannelId = Channels.Id 1568 LEFT JOIN 1569 Schemes ChannelScheme ON Channels.SchemeId = ChannelScheme.Id 1570 LEFT JOIN 1571 Teams ON Channels.TeamId = Teams.Id 1572 LEFT JOIN 1573 Schemes TeamScheme ON Teams.SchemeId = TeamScheme.Id 1574 WHERE 1575 `+deletedClause+` 1576 ChannelMembers.UserId = :UserId`, map[string]interface{}{"UserId": userId}) 1577 1578 if err != nil { 1579 return nil, model.NewAppError("SqlChannelStore.GetAllChannelMembersForUser", "store.sql_channel.get_channels.get.app_error", nil, "userId="+userId+", err="+err.Error(), http.StatusInternalServerError) 1580 } 1581 1582 ids := data.ToMapStringString() 1583 1584 if allowFromCache { 1585 allChannelMembersForUserCache.AddWithExpiresInSecs(cache_key, ids, ALL_CHANNEL_MEMBERS_FOR_USER_CACHE_SEC) 1586 } 1587 return ids, nil 1588 } 1589 1590 func (s SqlChannelStore) InvalidateCacheForChannelMembersNotifyProps(channelId string) { 1591 allChannelMembersNotifyPropsForChannelCache.Remove(channelId) 1592 if s.metrics != nil { 1593 s.metrics.IncrementMemCacheInvalidationCounter("All Channel Members Notify Props for Channel - Remove by ChannelId") 1594 } 1595 } 1596 1597 type allChannelMemberNotifyProps struct { 1598 UserId string 1599 NotifyProps model.StringMap 1600 } 1601 1602 func (s SqlChannelStore) GetAllChannelMembersNotifyPropsForChannel(channelId string, allowFromCache bool) (map[string]model.StringMap, *model.AppError) { 1603 if allowFromCache { 1604 if cacheItem, ok := allChannelMembersNotifyPropsForChannelCache.Get(channelId); ok { 1605 if s.metrics != nil { 1606 s.metrics.IncrementMemCacheHitCounter("All Channel Members Notify Props for Channel") 1607 } 1608 return cacheItem.(map[string]model.StringMap), nil 1609 } 1610 } 1611 1612 if s.metrics != nil { 1613 s.metrics.IncrementMemCacheMissCounter("All Channel Members Notify Props for Channel") 1614 } 1615 1616 var data []allChannelMemberNotifyProps 1617 _, err := s.GetReplica().Select(&data, ` 1618 SELECT UserId, NotifyProps 1619 FROM ChannelMembers 1620 WHERE ChannelId = :ChannelId`, map[string]interface{}{"ChannelId": channelId}) 1621 1622 if err != nil { 1623 return nil, model.NewAppError("SqlChannelStore.GetAllChannelMembersPropsForChannel", "store.sql_channel.get_members.app_error", nil, "channelId="+channelId+", err="+err.Error(), http.StatusInternalServerError) 1624 } 1625 1626 props := make(map[string]model.StringMap) 1627 for i := range data { 1628 props[data[i].UserId] = data[i].NotifyProps 1629 } 1630 1631 allChannelMembersNotifyPropsForChannelCache.AddWithExpiresInSecs(channelId, props, ALL_CHANNEL_MEMBERS_NOTIFY_PROPS_FOR_CHANNEL_CACHE_SEC) 1632 1633 return props, nil 1634 } 1635 1636 func (s SqlChannelStore) InvalidateMemberCount(channelId string) { 1637 channelMemberCountsCache.Remove(channelId) 1638 if s.metrics != nil { 1639 s.metrics.IncrementMemCacheInvalidationCounter("Channel Member Counts - Remove by ChannelId") 1640 } 1641 } 1642 1643 func (s SqlChannelStore) GetMemberCountFromCache(channelId string) int64 { 1644 if cacheItem, ok := channelMemberCountsCache.Get(channelId); ok { 1645 if s.metrics != nil { 1646 s.metrics.IncrementMemCacheHitCounter("Channel Member Counts") 1647 } 1648 return cacheItem.(int64) 1649 } 1650 1651 if s.metrics != nil { 1652 s.metrics.IncrementMemCacheMissCounter("Channel Member Counts") 1653 } 1654 1655 count, err := s.GetMemberCount(channelId, true) 1656 if err != nil { 1657 return 0 1658 } 1659 1660 return count 1661 } 1662 1663 func (s SqlChannelStore) GetMemberCount(channelId string, allowFromCache bool) (int64, *model.AppError) { 1664 if allowFromCache { 1665 if cacheItem, ok := channelMemberCountsCache.Get(channelId); ok { 1666 if s.metrics != nil { 1667 s.metrics.IncrementMemCacheHitCounter("Channel Member Counts") 1668 } 1669 return cacheItem.(int64), nil 1670 } 1671 } 1672 1673 if s.metrics != nil { 1674 s.metrics.IncrementMemCacheMissCounter("Channel Member Counts") 1675 } 1676 1677 count, err := s.GetReplica().SelectInt(` 1678 SELECT 1679 count(*) 1680 FROM 1681 ChannelMembers, 1682 Users 1683 WHERE 1684 ChannelMembers.UserId = Users.Id 1685 AND ChannelMembers.ChannelId = :ChannelId 1686 AND Users.DeleteAt = 0`, map[string]interface{}{"ChannelId": channelId}) 1687 if err != nil { 1688 return 0, model.NewAppError("SqlChannelStore.GetMemberCount", "store.sql_channel.get_member_count.app_error", nil, "channel_id="+channelId+", "+err.Error(), http.StatusInternalServerError) 1689 } 1690 1691 if allowFromCache { 1692 channelMemberCountsCache.AddWithExpiresInSecs(channelId, count, CHANNEL_MEMBERS_COUNTS_CACHE_SEC) 1693 } 1694 1695 return count, nil 1696 } 1697 1698 func (s SqlChannelStore) RemoveMember(channelId string, userId string) *model.AppError { 1699 _, err := s.GetMaster().Exec("DELETE FROM ChannelMembers WHERE ChannelId = :ChannelId AND UserId = :UserId", map[string]interface{}{"ChannelId": channelId, "UserId": userId}) 1700 if err != nil { 1701 return model.NewAppError("SqlChannelStore.RemoveMember", "store.sql_channel.remove_member.app_error", nil, "channel_id="+channelId+", user_id="+userId+", "+err.Error(), http.StatusInternalServerError) 1702 } 1703 return nil 1704 } 1705 1706 func (s SqlChannelStore) RemoveAllDeactivatedMembers(channelId string) *model.AppError { 1707 query := ` 1708 DELETE 1709 FROM 1710 ChannelMembers 1711 WHERE 1712 UserId IN ( 1713 SELECT 1714 Id 1715 FROM 1716 Users 1717 WHERE 1718 Users.DeleteAt != 0 1719 ) 1720 AND 1721 ChannelMembers.ChannelId = :ChannelId 1722 ` 1723 1724 _, err := s.GetMaster().Exec(query, map[string]interface{}{"ChannelId": channelId}) 1725 if err != nil { 1726 return model.NewAppError("SqlChannelStore.RemoveAllDeactivatedMembers", "store.sql_channel.remove_all_deactivated_members.app_error", nil, "channel_id="+channelId+", "+err.Error(), http.StatusInternalServerError) 1727 } 1728 return nil 1729 } 1730 1731 func (s SqlChannelStore) PermanentDeleteMembersByUser(userId string) *model.AppError { 1732 if _, err := s.GetMaster().Exec("DELETE FROM ChannelMembers WHERE UserId = :UserId", map[string]interface{}{"UserId": userId}); err != nil { 1733 return model.NewAppError("SqlChannelStore.RemoveMember", "store.sql_channel.permanent_delete_members_by_user.app_error", nil, "user_id="+userId+", "+err.Error(), http.StatusInternalServerError) 1734 } 1735 return nil 1736 } 1737 1738 func (s SqlChannelStore) UpdateLastViewedAt(channelIds []string, userId string) (map[string]int64, *model.AppError) { 1739 props := make(map[string]interface{}) 1740 1741 updateIdQuery := "" 1742 for index, channelId := range channelIds { 1743 if len(updateIdQuery) > 0 { 1744 updateIdQuery += " OR " 1745 } 1746 1747 props["channelId"+strconv.Itoa(index)] = channelId 1748 updateIdQuery += "ChannelId = :channelId" + strconv.Itoa(index) 1749 } 1750 1751 selectIdQuery := strings.Replace(updateIdQuery, "ChannelId", "Id", -1) 1752 1753 var lastPostAtTimes []struct { 1754 Id string 1755 LastPostAt int64 1756 TotalMsgCount int64 1757 } 1758 1759 selectQuery := "SELECT Id, LastPostAt, TotalMsgCount FROM Channels WHERE (" + selectIdQuery + ")" 1760 1761 if _, err := s.GetMaster().Select(&lastPostAtTimes, selectQuery, props); err != nil || len(lastPostAtTimes) <= 0 { 1762 var extra string 1763 status := http.StatusInternalServerError 1764 if err == nil { 1765 status = http.StatusBadRequest 1766 extra = "No channels found" 1767 } else { 1768 extra = err.Error() 1769 } 1770 return nil, model.NewAppError("SqlChannelStore.UpdateLastViewedAt", "store.sql_channel.update_last_viewed_at.app_error", nil, "channel_ids="+strings.Join(channelIds, ",")+", user_id="+userId+", "+extra, status) 1771 } 1772 1773 times := map[string]int64{} 1774 msgCountQuery := "" 1775 lastViewedQuery := "" 1776 for index, t := range lastPostAtTimes { 1777 times[t.Id] = t.LastPostAt 1778 1779 props["msgCount"+strconv.Itoa(index)] = t.TotalMsgCount 1780 msgCountQuery += fmt.Sprintf("WHEN :channelId%d THEN GREATEST(MsgCount, :msgCount%d) ", index, index) 1781 1782 props["lastViewed"+strconv.Itoa(index)] = t.LastPostAt 1783 lastViewedQuery += fmt.Sprintf("WHEN :channelId%d THEN GREATEST(LastViewedAt, :lastViewed%d) ", index, index) 1784 1785 props["channelId"+strconv.Itoa(index)] = t.Id 1786 } 1787 1788 var updateQuery string 1789 1790 if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { 1791 updateQuery = `UPDATE 1792 ChannelMembers 1793 SET 1794 MentionCount = 0, 1795 MsgCount = CAST(CASE ChannelId ` + msgCountQuery + ` END AS BIGINT), 1796 LastViewedAt = CAST(CASE ChannelId ` + lastViewedQuery + ` END AS BIGINT), 1797 LastUpdateAt = CAST(CASE ChannelId ` + lastViewedQuery + ` END AS BIGINT) 1798 WHERE 1799 UserId = :UserId 1800 AND (` + updateIdQuery + `)` 1801 } else if s.DriverName() == model.DATABASE_DRIVER_MYSQL { 1802 updateQuery = `UPDATE 1803 ChannelMembers 1804 SET 1805 MentionCount = 0, 1806 MsgCount = CASE ChannelId ` + msgCountQuery + ` END, 1807 LastViewedAt = CASE ChannelId ` + lastViewedQuery + ` END, 1808 LastUpdateAt = CASE ChannelId ` + lastViewedQuery + ` END 1809 WHERE 1810 UserId = :UserId 1811 AND (` + updateIdQuery + `)` 1812 } 1813 1814 props["UserId"] = userId 1815 1816 if _, err := s.GetMaster().Exec(updateQuery, props); err != nil { 1817 return nil, model.NewAppError("SqlChannelStore.UpdateLastViewedAt", "store.sql_channel.update_last_viewed_at.app_error", nil, "channel_ids="+strings.Join(channelIds, ",")+", user_id="+userId+", "+err.Error(), http.StatusInternalServerError) 1818 } 1819 1820 return times, nil 1821 } 1822 1823 func (s SqlChannelStore) IncrementMentionCount(channelId string, userId string) *model.AppError { 1824 _, err := s.GetMaster().Exec( 1825 `UPDATE 1826 ChannelMembers 1827 SET 1828 MentionCount = MentionCount + 1, 1829 LastUpdateAt = :LastUpdateAt 1830 WHERE 1831 UserId = :UserId 1832 AND ChannelId = :ChannelId`, 1833 map[string]interface{}{"ChannelId": channelId, "UserId": userId, "LastUpdateAt": model.GetMillis()}) 1834 if err != nil { 1835 return model.NewAppError("SqlChannelStore.IncrementMentionCount", "store.sql_channel.increment_mention_count.app_error", nil, "channel_id="+channelId+", user_id="+userId+", "+err.Error(), http.StatusInternalServerError) 1836 } 1837 1838 return nil 1839 } 1840 1841 func (s SqlChannelStore) GetAll(teamId string) ([]*model.Channel, *model.AppError) { 1842 var data []*model.Channel 1843 _, err := s.GetReplica().Select(&data, "SELECT * FROM Channels WHERE TeamId = :TeamId AND Type != 'D' ORDER BY Name", map[string]interface{}{"TeamId": teamId}) 1844 1845 if err != nil { 1846 return nil, model.NewAppError("SqlChannelStore.GetAll", "store.sql_channel.get_all.app_error", nil, "teamId="+teamId+", err="+err.Error(), http.StatusInternalServerError) 1847 } 1848 1849 return data, nil 1850 } 1851 1852 func (s SqlChannelStore) GetChannelsByIds(channelIds []string) ([]*model.Channel, *model.AppError) { 1853 keys, params := MapStringsToQueryParams(channelIds, "Channel") 1854 query := `SELECT * FROM Channels WHERE Id IN ` + keys + ` ORDER BY Name` 1855 1856 var channels []*model.Channel 1857 _, err := s.GetReplica().Select(&channels, query, params) 1858 1859 if err != nil { 1860 mlog.Error(fmt.Sprint(err)) 1861 return nil, model.NewAppError("SqlChannelStore.GetChannelsByIds", "store.sql_channel.get_channels_by_ids.app_error", nil, "", http.StatusInternalServerError) 1862 } 1863 return channels, nil 1864 } 1865 1866 func (s SqlChannelStore) GetForPost(postId string) (*model.Channel, *model.AppError) { 1867 channel := &model.Channel{} 1868 if err := s.GetReplica().SelectOne( 1869 channel, 1870 `SELECT 1871 Channels.* 1872 FROM 1873 Channels, 1874 Posts 1875 WHERE 1876 Channels.Id = Posts.ChannelId 1877 AND Posts.Id = :PostId`, map[string]interface{}{"PostId": postId}); err != nil { 1878 return nil, model.NewAppError("SqlChannelStore.GetForPost", "store.sql_channel.get_for_post.app_error", nil, "postId="+postId+", err="+err.Error(), http.StatusInternalServerError) 1879 1880 } 1881 return channel, nil 1882 } 1883 1884 func (s SqlChannelStore) AnalyticsTypeCount(teamId string, channelType string) (int64, *model.AppError) { 1885 query := "SELECT COUNT(Id) AS Value FROM Channels WHERE Type = :ChannelType" 1886 1887 if len(teamId) > 0 { 1888 query += " AND TeamId = :TeamId" 1889 } 1890 1891 value, err := s.GetReplica().SelectInt(query, map[string]interface{}{"TeamId": teamId, "ChannelType": channelType}) 1892 if err != nil { 1893 return int64(0), model.NewAppError("SqlChannelStore.AnalyticsTypeCount", "store.sql_channel.analytics_type_count.app_error", nil, err.Error(), http.StatusInternalServerError) 1894 } 1895 return value, nil 1896 } 1897 1898 func (s SqlChannelStore) AnalyticsDeletedTypeCount(teamId string, channelType string) (int64, *model.AppError) { 1899 query := "SELECT COUNT(Id) AS Value FROM Channels WHERE Type = :ChannelType AND DeleteAt > 0" 1900 1901 if len(teamId) > 0 { 1902 query += " AND TeamId = :TeamId" 1903 } 1904 1905 v, err := s.GetReplica().SelectInt(query, map[string]interface{}{"TeamId": teamId, "ChannelType": channelType}) 1906 if err != nil { 1907 return 0, model.NewAppError("SqlChannelStore.AnalyticsDeletedTypeCount", "store.sql_channel.analytics_deleted_type_count.app_error", nil, err.Error(), http.StatusInternalServerError) 1908 } 1909 1910 return v, nil 1911 } 1912 1913 func (s SqlChannelStore) GetMembersForUser(teamId string, userId string) (*model.ChannelMembers, *model.AppError) { 1914 var dbMembers channelMemberWithSchemeRolesList 1915 _, err := s.GetReplica().Select(&dbMembers, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.UserId = :UserId", map[string]interface{}{"TeamId": teamId, "UserId": userId}) 1916 1917 if err != nil { 1918 return nil, model.NewAppError("SqlChannelStore.GetMembersForUser", "store.sql_channel.get_members.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError) 1919 } 1920 1921 return dbMembers.ToModel(), nil 1922 } 1923 1924 func (s SqlChannelStore) GetMembersForUserWithPagination(teamId, userId string, page, perPage int) (*model.ChannelMembers, *model.AppError) { 1925 var dbMembers channelMemberWithSchemeRolesList 1926 offset := page * perPage 1927 _, err := s.GetReplica().Select(&dbMembers, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.UserId = :UserId Limit :Limit Offset :Offset", map[string]interface{}{"TeamId": teamId, "UserId": userId, "Limit": perPage, "Offset": offset}) 1928 1929 if err != nil { 1930 return nil, model.NewAppError("SqlChannelStore.GetMembersForUserWithPagination", "store.sql_channel.get_members.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError) 1931 } 1932 1933 return dbMembers.ToModel(), nil 1934 } 1935 1936 func (s SqlChannelStore) AutocompleteInTeam(teamId string, term string, includeDeleted bool) (*model.ChannelList, *model.AppError) { 1937 deleteFilter := "AND c.DeleteAt = 0" 1938 if includeDeleted { 1939 deleteFilter = "" 1940 } 1941 1942 queryFormat := ` 1943 SELECT 1944 Channels.* 1945 FROM 1946 Channels 1947 JOIN 1948 PublicChannels c ON (c.Id = Channels.Id) 1949 WHERE 1950 c.TeamId = :TeamId 1951 ` + deleteFilter + ` 1952 %v 1953 LIMIT ` + strconv.Itoa(model.CHANNEL_SEARCH_DEFAULT_LIMIT) 1954 1955 var channels model.ChannelList 1956 1957 if likeClause, likeTerm := s.buildLIKEClause(term, "c.Name, c.DisplayName, c.Purpose"); likeClause == "" { 1958 if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"TeamId": teamId}); err != nil { 1959 return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeam", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 1960 } 1961 } else { 1962 // Using a UNION results in index_merge and fulltext queries and is much faster than the ref 1963 // query you would get using an OR of the LIKE and full-text clauses. 1964 fulltextClause, fulltextTerm := s.buildFulltextClause(term, "c.Name, c.DisplayName, c.Purpose") 1965 likeQuery := fmt.Sprintf(queryFormat, "AND "+likeClause) 1966 fulltextQuery := fmt.Sprintf(queryFormat, "AND "+fulltextClause) 1967 query := fmt.Sprintf("(%v) UNION (%v) LIMIT 50", likeQuery, fulltextQuery) 1968 1969 if _, err := s.GetReplica().Select(&channels, query, map[string]interface{}{"TeamId": teamId, "LikeTerm": likeTerm, "FulltextTerm": fulltextTerm}); err != nil { 1970 return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeam", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 1971 } 1972 } 1973 1974 sort.Slice(channels, func(a, b int) bool { 1975 return strings.ToLower(channels[a].DisplayName) < strings.ToLower(channels[b].DisplayName) 1976 }) 1977 return &channels, nil 1978 } 1979 1980 func (s SqlChannelStore) AutocompleteInTeamForSearch(teamId string, userId string, term string, includeDeleted bool) (*model.ChannelList, *model.AppError) { 1981 deleteFilter := "AND DeleteAt = 0" 1982 if includeDeleted { 1983 deleteFilter = "" 1984 } 1985 1986 queryFormat := ` 1987 SELECT 1988 C.* 1989 FROM 1990 Channels AS C 1991 JOIN 1992 ChannelMembers AS CM ON CM.ChannelId = C.Id 1993 WHERE 1994 (C.TeamId = :TeamId OR (C.TeamId = '' AND C.Type = 'G')) 1995 AND CM.UserId = :UserId 1996 ` + deleteFilter + ` 1997 %v 1998 LIMIT 50` 1999 2000 var channels model.ChannelList 2001 2002 if likeClause, likeTerm := s.buildLIKEClause(term, "Name, DisplayName, Purpose"); likeClause == "" { 2003 if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"TeamId": teamId, "UserId": userId}); err != nil { 2004 return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeamForSearch", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 2005 } 2006 } else { 2007 // Using a UNION results in index_merge and fulltext queries and is much faster than the ref 2008 // query you would get using an OR of the LIKE and full-text clauses. 2009 fulltextClause, fulltextTerm := s.buildFulltextClause(term, "Name, DisplayName, Purpose") 2010 likeQuery := fmt.Sprintf(queryFormat, "AND "+likeClause) 2011 fulltextQuery := fmt.Sprintf(queryFormat, "AND "+fulltextClause) 2012 query := fmt.Sprintf("(%v) UNION (%v) LIMIT 50", likeQuery, fulltextQuery) 2013 2014 if _, err := s.GetReplica().Select(&channels, query, map[string]interface{}{"TeamId": teamId, "UserId": userId, "LikeTerm": likeTerm, "FulltextTerm": fulltextTerm}); err != nil { 2015 return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeamForSearch", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 2016 } 2017 } 2018 2019 directChannels, err := s.autocompleteInTeamForSearchDirectMessages(userId, term) 2020 if err != nil { 2021 return nil, err 2022 } 2023 2024 channels = append(channels, directChannels...) 2025 2026 sort.Slice(channels, func(a, b int) bool { 2027 return strings.ToLower(channels[a].DisplayName) < strings.ToLower(channels[b].DisplayName) 2028 }) 2029 return &channels, nil 2030 } 2031 2032 func (s SqlChannelStore) autocompleteInTeamForSearchDirectMessages(userId string, term string) ([]*model.Channel, *model.AppError) { 2033 queryFormat := ` 2034 SELECT 2035 C.*, 2036 OtherUsers.Username as DisplayName 2037 FROM 2038 Channels AS C 2039 JOIN 2040 ChannelMembers AS CM ON CM.ChannelId = C.Id 2041 INNER JOIN ( 2042 SELECT 2043 ICM.ChannelId AS ChannelId, IU.Username AS Username 2044 FROM 2045 Users as IU 2046 JOIN 2047 ChannelMembers AS ICM ON ICM.UserId = IU.Id 2048 WHERE 2049 IU.Id != :UserId 2050 %v 2051 ) AS OtherUsers ON OtherUsers.ChannelId = C.Id 2052 WHERE 2053 C.Type = 'D' 2054 AND CM.UserId = :UserId 2055 LIMIT 50` 2056 2057 var channels model.ChannelList 2058 2059 if likeClause, likeTerm := s.buildLIKEClause(term, "IU.Username, IU.Nickname"); likeClause == "" { 2060 if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"UserId": userId}); err != nil { 2061 return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeamForSearch", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 2062 } 2063 } else { 2064 query := fmt.Sprintf(queryFormat, "AND "+likeClause) 2065 2066 if _, err := s.GetReplica().Select(&channels, query, map[string]interface{}{"UserId": userId, "LikeTerm": likeTerm}); err != nil { 2067 return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeamForSearch", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 2068 } 2069 } 2070 2071 return channels, nil 2072 } 2073 2074 func (s SqlChannelStore) SearchInTeam(teamId string, term string, includeDeleted bool) (*model.ChannelList, *model.AppError) { 2075 deleteFilter := "AND c.DeleteAt = 0" 2076 if includeDeleted { 2077 deleteFilter = "" 2078 } 2079 2080 return s.performSearch(` 2081 SELECT 2082 Channels.* 2083 FROM 2084 Channels 2085 JOIN 2086 PublicChannels c ON (c.Id = Channels.Id) 2087 WHERE 2088 c.TeamId = :TeamId 2089 `+deleteFilter+` 2090 SEARCH_CLAUSE 2091 ORDER BY c.DisplayName 2092 LIMIT 100 2093 `, term, map[string]interface{}{ 2094 "TeamId": teamId, 2095 }) 2096 } 2097 2098 func (s SqlChannelStore) SearchAllChannels(term string, opts store.ChannelSearchOpts) (*model.ChannelListWithTeamData, *model.AppError) { 2099 query := s.getQueryBuilder(). 2100 Select("c.*, t.DisplayName AS TeamDisplayName, t.Name AS TeamName, t.UpdateAt as TeamUpdateAt"). 2101 From("Channels AS c"). 2102 Join("Teams AS t ON t.Id = c.TeamId"). 2103 Where(sq.Eq{"c.Type": []string{model.CHANNEL_PRIVATE, model.CHANNEL_OPEN}}). 2104 OrderBy("c.DisplayName, t.DisplayName"). 2105 Limit(uint64(100)) 2106 2107 if !opts.IncludeDeleted { 2108 query = query.Where(sq.Eq{"c.DeleteAt": int(0)}) 2109 } 2110 2111 likeClause, likeTerm := s.buildLIKEClause(term, "c.Name, c.DisplayName, c.Purpose") 2112 if len(likeTerm) > 0 { 2113 likeClause = strings.ReplaceAll(likeClause, ":LikeTerm", "'"+likeTerm+"'") 2114 fulltextClause, fulltextTerm := s.buildFulltextClause(term, "c.Name, c.DisplayName, c.Purpose") 2115 fulltextClause = strings.ReplaceAll(fulltextClause, ":FulltextTerm", "'"+fulltextTerm+"'") 2116 query = query.Where("(" + likeClause + " OR " + fulltextClause + ")") 2117 } 2118 2119 if len(opts.ExcludeChannelNames) > 0 { 2120 query = query.Where(fmt.Sprintf("c.Name NOT IN ('%s')", strings.Join(opts.ExcludeChannelNames, "', '"))) 2121 } 2122 2123 if len(opts.NotAssociatedToGroup) > 0 { 2124 query = query.Where("c.Id NOT IN (SELECT ChannelId FROM GroupChannels WHERE GroupChannels.GroupId = ? AND GroupChannels.DeleteAt = 0)", opts.NotAssociatedToGroup) 2125 } 2126 2127 queryString, args, err := query.ToSql() 2128 if err != nil { 2129 return nil, model.NewAppError("SqlChannelStore.SearchAllChannels", "store.sql.build_query.app_error", nil, err.Error(), http.StatusInternalServerError) 2130 } 2131 2132 var channels model.ChannelListWithTeamData 2133 2134 if _, err := s.GetReplica().Select(&channels, queryString, args...); err != nil { 2135 return nil, model.NewAppError("SqlChannelStore.Search", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 2136 } 2137 2138 return &channels, nil 2139 } 2140 2141 func (s SqlChannelStore) SearchMore(userId string, teamId string, term string) (*model.ChannelList, *model.AppError) { 2142 return s.performSearch(` 2143 SELECT 2144 Channels.* 2145 FROM 2146 Channels 2147 JOIN 2148 PublicChannels c ON (c.Id = Channels.Id) 2149 WHERE 2150 c.TeamId = :TeamId 2151 AND c.DeleteAt = 0 2152 AND c.Id NOT IN ( 2153 SELECT 2154 c.Id 2155 FROM 2156 PublicChannels c 2157 JOIN 2158 ChannelMembers cm ON (cm.ChannelId = c.Id) 2159 WHERE 2160 c.TeamId = :TeamId 2161 AND cm.UserId = :UserId 2162 AND c.DeleteAt = 0 2163 ) 2164 SEARCH_CLAUSE 2165 ORDER BY c.DisplayName 2166 LIMIT 100 2167 `, term, map[string]interface{}{ 2168 "TeamId": teamId, 2169 "UserId": userId, 2170 }) 2171 } 2172 2173 func (s SqlChannelStore) buildLIKEClause(term string, searchColumns string) (likeClause, likeTerm string) { 2174 likeTerm = term 2175 2176 // These chars must be removed from the like query. 2177 for _, c := range ignoreLikeSearchChar { 2178 likeTerm = strings.Replace(likeTerm, c, "", -1) 2179 } 2180 2181 // These chars must be escaped in the like query. 2182 for _, c := range escapeLikeSearchChar { 2183 likeTerm = strings.Replace(likeTerm, c, "*"+c, -1) 2184 } 2185 2186 if likeTerm == "" { 2187 return 2188 } 2189 2190 // Prepare the LIKE portion of the query. 2191 var searchFields []string 2192 for _, field := range strings.Split(searchColumns, ", ") { 2193 if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { 2194 searchFields = append(searchFields, fmt.Sprintf("lower(%s) LIKE lower(%s) escape '*'", field, ":LikeTerm")) 2195 } else { 2196 searchFields = append(searchFields, fmt.Sprintf("%s LIKE %s escape '*'", field, ":LikeTerm")) 2197 } 2198 } 2199 2200 likeClause = fmt.Sprintf("(%s)", strings.Join(searchFields, " OR ")) 2201 likeTerm += "%" 2202 return 2203 } 2204 2205 func (s SqlChannelStore) buildFulltextClause(term string, searchColumns string) (fulltextClause, fulltextTerm string) { 2206 // Copy the terms as we will need to prepare them differently for each search type. 2207 fulltextTerm = term 2208 2209 // These chars must be treated as spaces in the fulltext query. 2210 for _, c := range spaceFulltextSearchChar { 2211 fulltextTerm = strings.Replace(fulltextTerm, c, " ", -1) 2212 } 2213 2214 // Prepare the FULLTEXT portion of the query. 2215 if s.DriverName() == model.DATABASE_DRIVER_POSTGRES { 2216 fulltextTerm = strings.Replace(fulltextTerm, "|", "", -1) 2217 2218 splitTerm := strings.Fields(fulltextTerm) 2219 for i, t := range strings.Fields(fulltextTerm) { 2220 if i == len(splitTerm)-1 { 2221 splitTerm[i] = t + ":*" 2222 } else { 2223 splitTerm[i] = t + ":* &" 2224 } 2225 } 2226 2227 fulltextTerm = strings.Join(splitTerm, " ") 2228 2229 fulltextClause = fmt.Sprintf("((to_tsvector('english', %s)) @@ to_tsquery(:FulltextTerm))", convertMySQLFullTextColumnsToPostgres(searchColumns)) 2230 } else if s.DriverName() == model.DATABASE_DRIVER_MYSQL { 2231 splitTerm := strings.Fields(fulltextTerm) 2232 for i, t := range strings.Fields(fulltextTerm) { 2233 splitTerm[i] = "+" + t + "*" 2234 } 2235 2236 fulltextTerm = strings.Join(splitTerm, " ") 2237 2238 fulltextClause = fmt.Sprintf("MATCH(%s) AGAINST (:FulltextTerm IN BOOLEAN MODE)", searchColumns) 2239 } 2240 2241 return 2242 } 2243 2244 func (s SqlChannelStore) performSearch(searchQuery string, term string, parameters map[string]interface{}) (*model.ChannelList, *model.AppError) { 2245 likeClause, likeTerm := s.buildLIKEClause(term, "c.Name, c.DisplayName, c.Purpose") 2246 if likeTerm == "" { 2247 // If the likeTerm is empty after preparing, then don't bother searching. 2248 searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1) 2249 } else { 2250 parameters["LikeTerm"] = likeTerm 2251 fulltextClause, fulltextTerm := s.buildFulltextClause(term, "c.Name, c.DisplayName, c.Purpose") 2252 parameters["FulltextTerm"] = fulltextTerm 2253 searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "AND ("+likeClause+" OR "+fulltextClause+")", 1) 2254 } 2255 2256 var channels model.ChannelList 2257 2258 if _, err := s.GetReplica().Select(&channels, searchQuery, parameters); err != nil { 2259 return nil, model.NewAppError("SqlChannelStore.Search", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) 2260 } 2261 2262 return &channels, nil 2263 } 2264 2265 func (s SqlChannelStore) getSearchGroupChannelsQuery(userId, term string, isPostgreSQL bool) (string, map[string]interface{}) { 2266 var query, baseLikeClause string 2267 if isPostgreSQL { 2268 baseLikeClause = "ARRAY_TO_STRING(ARRAY_AGG(u.Username), ', ') LIKE %s" 2269 query = ` 2270 SELECT 2271 * 2272 FROM 2273 Channels 2274 WHERE 2275 Id IN ( 2276 SELECT 2277 cc.Id 2278 FROM ( 2279 SELECT 2280 c.Id 2281 FROM 2282 Channels c 2283 JOIN 2284 ChannelMembers cm on c.Id = cm.ChannelId 2285 JOIN 2286 Users u on u.Id = cm.UserId 2287 WHERE 2288 c.Type = 'G' 2289 AND 2290 u.Id = :UserId 2291 GROUP BY 2292 c.Id 2293 ) cc 2294 JOIN 2295 ChannelMembers cm on cc.Id = cm.ChannelId 2296 JOIN 2297 Users u on u.Id = cm.UserId 2298 GROUP BY 2299 cc.Id 2300 HAVING 2301 %s 2302 LIMIT 2303 ` + strconv.Itoa(model.CHANNEL_SEARCH_DEFAULT_LIMIT) + ` 2304 )` 2305 } else { 2306 baseLikeClause = "GROUP_CONCAT(u.Username SEPARATOR ', ') LIKE %s" 2307 query = ` 2308 SELECT 2309 cc.* 2310 FROM ( 2311 SELECT 2312 c.* 2313 FROM 2314 Channels c 2315 JOIN 2316 ChannelMembers cm on c.Id = cm.ChannelId 2317 JOIN 2318 Users u on u.Id = cm.UserId 2319 WHERE 2320 c.Type = 'G' 2321 AND 2322 u.Id = :UserId 2323 GROUP BY 2324 c.Id 2325 ) cc 2326 JOIN 2327 ChannelMembers cm on cc.Id = cm.ChannelId 2328 JOIN 2329 Users u on u.Id = cm.UserId 2330 GROUP BY 2331 cc.Id 2332 HAVING 2333 %s 2334 LIMIT 2335 ` + strconv.Itoa(model.CHANNEL_SEARCH_DEFAULT_LIMIT) 2336 } 2337 2338 var likeClauses []string 2339 args := map[string]interface{}{"UserId": userId} 2340 terms := strings.Split(strings.ToLower(strings.Trim(term, " ")), " ") 2341 2342 for idx, term := range terms { 2343 argName := fmt.Sprintf("Term%v", idx) 2344 likeClauses = append(likeClauses, fmt.Sprintf(baseLikeClause, ":"+argName)) 2345 args[argName] = "%" + term + "%" 2346 } 2347 2348 query = fmt.Sprintf(query, strings.Join(likeClauses, " AND ")) 2349 return query, args 2350 } 2351 2352 func (s SqlChannelStore) SearchGroupChannels(userId, term string) (*model.ChannelList, *model.AppError) { 2353 isPostgreSQL := s.DriverName() == model.DATABASE_DRIVER_POSTGRES 2354 queryString, args := s.getSearchGroupChannelsQuery(userId, term, isPostgreSQL) 2355 2356 var groupChannels model.ChannelList 2357 if _, err := s.GetReplica().Select(&groupChannels, queryString, args); err != nil { 2358 return nil, model.NewAppError("SqlChannelStore.SearchGroupChannels", "store.sql_channel.search_group_channels.app_error", nil, "userId="+userId+", term="+term+", err="+err.Error(), http.StatusInternalServerError) 2359 } 2360 return &groupChannels, nil 2361 } 2362 2363 func (s SqlChannelStore) GetMembersByIds(channelId string, userIds []string) (*model.ChannelMembers, *model.AppError) { 2364 var dbMembers channelMemberWithSchemeRolesList 2365 props := make(map[string]interface{}) 2366 idQuery := "" 2367 2368 for index, userId := range userIds { 2369 if len(idQuery) > 0 { 2370 idQuery += ", " 2371 } 2372 2373 props["userId"+strconv.Itoa(index)] = userId 2374 idQuery += ":userId" + strconv.Itoa(index) 2375 } 2376 2377 props["ChannelId"] = channelId 2378 2379 if _, err := s.GetReplica().Select(&dbMembers, CHANNEL_MEMBERS_WITH_SCHEME_SELECT_QUERY+"WHERE ChannelMembers.ChannelId = :ChannelId AND ChannelMembers.UserId IN ("+idQuery+")", props); err != nil { 2380 return nil, model.NewAppError("SqlChannelStore.GetMembersByIds", "store.sql_channel.get_members_by_ids.app_error", nil, "channelId="+channelId+" "+err.Error(), http.StatusInternalServerError) 2381 } 2382 2383 return dbMembers.ToModel(), nil 2384 } 2385 2386 func (s SqlChannelStore) GetChannelsByScheme(schemeId string, offset int, limit int) store.StoreChannel { 2387 return store.Do(func(result *store.StoreResult) { 2388 var channels model.ChannelList 2389 _, err := s.GetReplica().Select(&channels, "SELECT * FROM Channels WHERE SchemeId = :SchemeId ORDER BY DisplayName LIMIT :Limit OFFSET :Offset", map[string]interface{}{"SchemeId": schemeId, "Offset": offset, "Limit": limit}) 2390 if err != nil { 2391 result.Err = model.NewAppError("SqlChannelStore.GetChannelsByScheme", "store.sql_channel.get_by_scheme.app_error", nil, "schemeId="+schemeId+" "+err.Error(), http.StatusInternalServerError) 2392 return 2393 } 2394 result.Data = channels 2395 }) 2396 } 2397 2398 // This function does the Advanced Permissions Phase 2 migration for ChannelMember objects. It performs the migration 2399 // in batches as a single transaction per batch to ensure consistency but to also minimise execution time to avoid 2400 // causing unnecessary table locks. **THIS FUNCTION SHOULD NOT BE USED FOR ANY OTHER PURPOSE.** Executing this function 2401 // *after* the new Schemes functionality has been used on an installation will have unintended consequences. 2402 func (s SqlChannelStore) MigrateChannelMembers(fromChannelId string, fromUserId string) (map[string]string, *model.AppError) { 2403 var transaction *gorp.Transaction 2404 var err error 2405 2406 if transaction, err = s.GetMaster().Begin(); err != nil { 2407 return nil, model.NewAppError("SqlChannelStore.MigrateChannelMembers", "store.sql_channel.migrate_channel_members.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 2408 } 2409 defer finalizeTransaction(transaction) 2410 2411 var channelMembers []channelMember 2412 if _, err := transaction.Select(&channelMembers, "SELECT * from ChannelMembers WHERE (ChannelId, UserId) > (:FromChannelId, :FromUserId) ORDER BY ChannelId, UserId LIMIT 100", map[string]interface{}{"FromChannelId": fromChannelId, "FromUserId": fromUserId}); err != nil { 2413 return nil, model.NewAppError("SqlChannelStore.MigrateChannelMembers", "store.sql_channel.migrate_channel_members.select.app_error", nil, err.Error(), http.StatusInternalServerError) 2414 } 2415 2416 if len(channelMembers) == 0 { 2417 // No more channel members in query result means that the migration has finished. 2418 return nil, nil 2419 } 2420 2421 for _, member := range channelMembers { 2422 roles := strings.Fields(member.Roles) 2423 var newRoles []string 2424 if !member.SchemeAdmin.Valid { 2425 member.SchemeAdmin = sql.NullBool{Bool: false, Valid: true} 2426 } 2427 if !member.SchemeUser.Valid { 2428 member.SchemeUser = sql.NullBool{Bool: false, Valid: true} 2429 } 2430 if !member.SchemeGuest.Valid { 2431 member.SchemeGuest = sql.NullBool{Bool: false, Valid: true} 2432 } 2433 for _, role := range roles { 2434 if role == model.CHANNEL_ADMIN_ROLE_ID { 2435 member.SchemeAdmin = sql.NullBool{Bool: true, Valid: true} 2436 } else if role == model.CHANNEL_USER_ROLE_ID { 2437 member.SchemeUser = sql.NullBool{Bool: true, Valid: true} 2438 } else if role == model.CHANNEL_GUEST_ROLE_ID { 2439 member.SchemeGuest = sql.NullBool{Bool: true, Valid: true} 2440 } else { 2441 newRoles = append(newRoles, role) 2442 } 2443 } 2444 member.Roles = strings.Join(newRoles, " ") 2445 2446 if _, err := transaction.Update(&member); err != nil { 2447 return nil, model.NewAppError("SqlChannelStore.MigrateChannelMembers", "store.sql_channel.migrate_channel_members.update.app_error", nil, err.Error(), http.StatusInternalServerError) 2448 } 2449 2450 } 2451 2452 if err := transaction.Commit(); err != nil { 2453 return nil, model.NewAppError("SqlChannelStore.MigrateChannelMembers", "store.sql_channel.migrate_channel_members.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 2454 } 2455 2456 data := make(map[string]string) 2457 data["ChannelId"] = channelMembers[len(channelMembers)-1].ChannelId 2458 data["UserId"] = channelMembers[len(channelMembers)-1].UserId 2459 return data, nil 2460 } 2461 2462 func (s SqlChannelStore) ResetAllChannelSchemes() *model.AppError { 2463 transaction, err := s.GetMaster().Begin() 2464 if err != nil { 2465 return model.NewAppError("SqlChannelStore.ResetAllChannelSchemes", "store.sql_channel.reset_all_channel_schemes.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 2466 } 2467 defer finalizeTransaction(transaction) 2468 2469 resetErr := s.resetAllChannelSchemesT(transaction) 2470 if resetErr != nil { 2471 return resetErr 2472 } 2473 2474 if err := transaction.Commit(); err != nil { 2475 return model.NewAppError("SqlChannelStore.ResetAllChannelSchemes", "store.sql_channel.reset_all_channel_schemes.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 2476 } 2477 2478 return nil 2479 } 2480 2481 func (s SqlChannelStore) resetAllChannelSchemesT(transaction *gorp.Transaction) *model.AppError { 2482 if _, err := transaction.Exec("UPDATE Channels SET SchemeId=''"); err != nil { 2483 return model.NewAppError("SqlChannelStore.ResetAllChannelSchemes", "store.sql_channel.reset_all_channel_schemes.app_error", nil, err.Error(), http.StatusInternalServerError) 2484 } 2485 2486 return nil 2487 } 2488 2489 func (s SqlChannelStore) ClearAllCustomRoleAssignments() *model.AppError { 2490 builtInRoles := model.MakeDefaultRoles() 2491 lastUserId := strings.Repeat("0", 26) 2492 lastChannelId := strings.Repeat("0", 26) 2493 2494 for { 2495 var transaction *gorp.Transaction 2496 var err error 2497 2498 if transaction, err = s.GetMaster().Begin(); err != nil { 2499 return model.NewAppError("SqlChannelStore.ClearAllCustomRoleAssignments", "store.sql_channel.clear_all_custom_role_assignments.open_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 2500 } 2501 2502 var channelMembers []*channelMember 2503 if _, err := transaction.Select(&channelMembers, "SELECT * from ChannelMembers WHERE (ChannelId, UserId) > (:ChannelId, :UserId) ORDER BY ChannelId, UserId LIMIT 1000", map[string]interface{}{"ChannelId": lastChannelId, "UserId": lastUserId}); err != nil { 2504 finalizeTransaction(transaction) 2505 return model.NewAppError("SqlChannelStore.ClearAllCustomRoleAssignments", "store.sql_channel.clear_all_custom_role_assignments.select.app_error", nil, err.Error(), http.StatusInternalServerError) 2506 } 2507 2508 if len(channelMembers) == 0 { 2509 finalizeTransaction(transaction) 2510 break 2511 } 2512 2513 for _, member := range channelMembers { 2514 lastUserId = member.UserId 2515 lastChannelId = member.ChannelId 2516 2517 var newRoles []string 2518 2519 for _, role := range strings.Fields(member.Roles) { 2520 for name := range builtInRoles { 2521 if name == role { 2522 newRoles = append(newRoles, role) 2523 break 2524 } 2525 } 2526 } 2527 2528 newRolesString := strings.Join(newRoles, " ") 2529 if newRolesString != member.Roles { 2530 if _, err := transaction.Exec("UPDATE ChannelMembers SET Roles = :Roles WHERE UserId = :UserId AND ChannelId = :ChannelId", map[string]interface{}{"Roles": newRolesString, "ChannelId": member.ChannelId, "UserId": member.UserId}); err != nil { 2531 finalizeTransaction(transaction) 2532 return model.NewAppError("SqlChannelStore.ClearAllCustomRoleAssignments", "store.sql_channel.clear_all_custom_role_assignments.update.app_error", nil, err.Error(), http.StatusInternalServerError) 2533 } 2534 } 2535 } 2536 2537 if err := transaction.Commit(); err != nil { 2538 finalizeTransaction(transaction) 2539 return model.NewAppError("SqlChannelStore.ClearAllCustomRoleAssignments", "store.sql_channel.clear_all_custom_role_assignments.commit_transaction.app_error", nil, err.Error(), http.StatusInternalServerError) 2540 } 2541 } 2542 2543 return nil 2544 } 2545 2546 func (s SqlChannelStore) GetAllChannelsForExportAfter(limit int, afterId string) ([]*model.ChannelForExport, *model.AppError) { 2547 var channels []*model.ChannelForExport 2548 if _, err := s.GetReplica().Select(&channels, ` 2549 SELECT 2550 Channels.*, 2551 Teams.Name as TeamName, 2552 Schemes.Name as SchemeName 2553 FROM Channels 2554 INNER JOIN 2555 Teams ON Channels.TeamId = Teams.Id 2556 LEFT JOIN 2557 Schemes ON Channels.SchemeId = Schemes.Id 2558 WHERE 2559 Channels.Id > :AfterId 2560 AND Channels.Type IN ('O', 'P') 2561 ORDER BY 2562 Id 2563 LIMIT :Limit`, 2564 map[string]interface{}{"AfterId": afterId, "Limit": limit}); err != nil { 2565 return nil, model.NewAppError("SqlChannelStore.GetAllChannelsForExportAfter", "store.sql_channel.get_all.app_error", nil, err.Error(), http.StatusInternalServerError) 2566 } 2567 2568 return channels, nil 2569 } 2570 2571 func (s SqlChannelStore) GetChannelMembersForExport(userId string, teamId string) ([]*model.ChannelMemberForExport, *model.AppError) { 2572 var members []*model.ChannelMemberForExport 2573 _, err := s.GetReplica().Select(&members, ` 2574 SELECT 2575 ChannelMembers.ChannelId, 2576 ChannelMembers.UserId, 2577 ChannelMembers.Roles, 2578 ChannelMembers.LastViewedAt, 2579 ChannelMembers.MsgCount, 2580 ChannelMembers.MentionCount, 2581 ChannelMembers.NotifyProps, 2582 ChannelMembers.LastUpdateAt, 2583 ChannelMembers.SchemeUser, 2584 ChannelMembers.SchemeAdmin, 2585 (ChannelMembers.SchemeGuest IS NOT NULL AND ChannelMembers.SchemeGuest) as SchemeGuest, 2586 Channels.Name as ChannelName 2587 FROM 2588 ChannelMembers 2589 INNER JOIN 2590 Channels ON ChannelMembers.ChannelId = Channels.Id 2591 WHERE 2592 ChannelMembers.UserId = :UserId 2593 AND Channels.TeamId = :TeamId 2594 AND Channels.DeleteAt = 0`, 2595 map[string]interface{}{"TeamId": teamId, "UserId": userId}) 2596 2597 if err != nil { 2598 return nil, model.NewAppError("SqlChannelStore.GetChannelMembersForExport", "store.sql_channel.get_members.app_error", nil, "teamId="+teamId+", userId="+userId+", err="+err.Error(), http.StatusInternalServerError) 2599 } 2600 2601 return members, nil 2602 } 2603 2604 func (s SqlChannelStore) GetAllDirectChannelsForExportAfter(limit int, afterId string) ([]*model.DirectChannelForExport, *model.AppError) { 2605 var directChannelsForExport []*model.DirectChannelForExport 2606 query := s.getQueryBuilder(). 2607 Select("Channels.*"). 2608 From("Channels"). 2609 Where(sq.And{ 2610 sq.Gt{"Channels.Id": afterId}, 2611 sq.Eq{"Channels.DeleteAt": int(0)}, 2612 sq.Eq{"Channels.Type": []string{"D", "G"}}, 2613 }). 2614 OrderBy("Channels.Id"). 2615 Limit(uint64(limit)) 2616 2617 queryString, args, err := query.ToSql() 2618 if err != nil { 2619 return nil, model.NewAppError("SqlChannelStore.GetAllDirectChannelsForExportAfter", "store.sql_channel.get_all_direct.app_error", nil, err.Error(), http.StatusInternalServerError) 2620 } 2621 2622 if _, err = s.GetReplica().Select(&directChannelsForExport, queryString, args...); err != nil { 2623 return nil, model.NewAppError("SqlChannelStore.GetAllDirectChannelsForExportAfter", "store.sql_channel.get_all_direct.app_error", nil, err.Error(), http.StatusInternalServerError) 2624 } 2625 2626 var channelIds []string 2627 for _, channel := range directChannelsForExport { 2628 channelIds = append(channelIds, channel.Id) 2629 } 2630 query = s.getQueryBuilder(). 2631 Select("u.Username as Username, ChannelId, UserId, cm.Roles as Roles, LastViewedAt, MsgCount, MentionCount, cm.NotifyProps as NotifyProps, LastUpdateAt, SchemeUser, SchemeAdmin, (SchemeGuest IS NOT NULL AND SchemeGuest) as SchemeGuest"). 2632 From("ChannelMembers cm"). 2633 Join("Users u ON ( u.Id = cm.UserId )"). 2634 Where(sq.And{ 2635 sq.Eq{"cm.ChannelId": channelIds}, 2636 sq.Eq{"u.DeleteAt": int(0)}, 2637 }) 2638 2639 queryString, args, err = query.ToSql() 2640 if err != nil { 2641 return nil, model.NewAppError("SqlChannelStore.GetAllDirectChannelsForExportAfter", "store.sql_channel.get_all_direct.app_error", nil, err.Error(), http.StatusInternalServerError) 2642 } 2643 2644 var channelMembers []*model.ChannelMemberForExport 2645 if _, err := s.GetReplica().Select(&channelMembers, queryString, args...); err != nil { 2646 return nil, model.NewAppError("SqlChannelStore.GetAllDirectChannelsForExportAfter", "store.sql_channel.get_all_direct.app_error", nil, err.Error(), http.StatusInternalServerError) 2647 } 2648 2649 // Populate each channel with its members 2650 dmChannelsMap := make(map[string]*model.DirectChannelForExport) 2651 for _, channel := range directChannelsForExport { 2652 channel.Members = &[]string{} 2653 dmChannelsMap[channel.Id] = channel 2654 } 2655 for _, member := range channelMembers { 2656 members := dmChannelsMap[member.ChannelId].Members 2657 *members = append(*members, member.Username) 2658 } 2659 2660 return directChannelsForExport, nil 2661 } 2662 2663 func (s SqlChannelStore) GetChannelsBatchForIndexing(startTime, endTime int64, limit int) ([]*model.Channel, *model.AppError) { 2664 query := 2665 `SELECT 2666 * 2667 FROM 2668 Channels 2669 WHERE 2670 Type = 'O' 2671 AND 2672 CreateAt >= :StartTime 2673 AND 2674 CreateAt < :EndTime 2675 ORDER BY 2676 CreateAt 2677 LIMIT 2678 :NumChannels` 2679 2680 var channels []*model.Channel 2681 _, err := s.GetSearchReplica().Select(&channels, query, map[string]interface{}{"StartTime": startTime, "EndTime": endTime, "NumChannels": limit}) 2682 if err != nil { 2683 return nil, model.NewAppError("SqlChannelStore.GetChannelsBatchForIndexing", "store.sql_channel.get_channels_batch_for_indexing.get.app_error", nil, err.Error(), http.StatusInternalServerError) 2684 } 2685 2686 return channels, nil 2687 } 2688 2689 func (s SqlChannelStore) UserBelongsToChannels(userId string, channelIds []string) (bool, *model.AppError) { 2690 query := s.getQueryBuilder(). 2691 Select("Count(*)"). 2692 From("ChannelMembers"). 2693 Where(sq.And{ 2694 sq.Eq{"UserId": userId}, 2695 sq.Eq{"ChannelId": channelIds}, 2696 }) 2697 2698 queryString, args, err := query.ToSql() 2699 if err != nil { 2700 return false, model.NewAppError("SqlChannelStore.UserBelongsToChannels", "store.sql_channel.user_belongs_to_channels.app_error", nil, err.Error(), http.StatusInternalServerError) 2701 } 2702 c, err := s.GetReplica().SelectInt(queryString, args...) 2703 if err != nil { 2704 return false, model.NewAppError("SqlChannelStore.UserBelongsToChannels", "store.sql_channel.user_belongs_to_channels.app_error", nil, err.Error(), http.StatusInternalServerError) 2705 } 2706 return c > 0, nil 2707 }