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  }