github.com/haalcala/mattermost-server-change-repo@v0.0.0-20210713015153-16753fbeee5f/store/sqlstore/post_store.go (about)

     1  // Copyright (c) 2015-present Mattermost, Inc. All Rights Reserved.
     2  // See LICENSE.txt for license information.
     3  
     4  package sqlstore
     5  
     6  import (
     7  	"context"
     8  	"database/sql"
     9  	"fmt"
    10  	"regexp"
    11  	"strconv"
    12  	"strings"
    13  	"sync"
    14  
    15  	sq "github.com/Masterminds/squirrel"
    16  	"github.com/mattermost/gorp"
    17  	"github.com/pkg/errors"
    18  
    19  	"github.com/mattermost/mattermost-server/v5/einterfaces"
    20  	"github.com/mattermost/mattermost-server/v5/mlog"
    21  	"github.com/mattermost/mattermost-server/v5/model"
    22  	"github.com/mattermost/mattermost-server/v5/store"
    23  	"github.com/mattermost/mattermost-server/v5/store/searchlayer"
    24  	"github.com/mattermost/mattermost-server/v5/utils"
    25  )
    26  
    27  type SqlPostStore struct {
    28  	*SqlStore
    29  	metrics           einterfaces.MetricsInterface
    30  	maxPostSizeOnce   sync.Once
    31  	maxPostSizeCached int
    32  }
    33  
    34  type postWithExtra struct {
    35  	ThreadReplyCount   int64
    36  	ThreadParticipants model.StringArray
    37  	model.Post
    38  }
    39  
    40  func (s *SqlPostStore) ClearCaches() {
    41  }
    42  
    43  func postSliceColumns() []string {
    44  	return []string{"Id", "CreateAt", "UpdateAt", "EditAt", "DeleteAt", "IsPinned", "UserId", "ChannelId", "RootId", "ParentId", "OriginalId", "Message", "Type", "Props", "Hashtags", "Filenames", "FileIds", "HasReactions"}
    45  }
    46  
    47  func postToSlice(post *model.Post) []interface{} {
    48  	return []interface{}{
    49  		post.Id,
    50  		post.CreateAt,
    51  		post.UpdateAt,
    52  		post.EditAt,
    53  		post.DeleteAt,
    54  		post.IsPinned,
    55  		post.UserId,
    56  		post.ChannelId,
    57  		post.RootId,
    58  		post.ParentId,
    59  		post.OriginalId,
    60  		post.Message,
    61  		post.Type,
    62  		model.StringInterfaceToJson(post.Props),
    63  		post.Hashtags,
    64  		model.ArrayToJson(post.Filenames),
    65  		model.ArrayToJson(post.FileIds),
    66  		post.HasReactions,
    67  	}
    68  }
    69  
    70  func newSqlPostStore(sqlStore *SqlStore, metrics einterfaces.MetricsInterface) store.PostStore {
    71  	s := &SqlPostStore{
    72  		SqlStore:          sqlStore,
    73  		metrics:           metrics,
    74  		maxPostSizeCached: model.POST_MESSAGE_MAX_RUNES_V1,
    75  	}
    76  
    77  	for _, db := range sqlStore.GetAllConns() {
    78  		table := db.AddTableWithName(model.Post{}, "Posts").SetKeys(false, "Id")
    79  		table.ColMap("Id").SetMaxSize(26)
    80  		table.ColMap("UserId").SetMaxSize(26)
    81  		table.ColMap("ChannelId").SetMaxSize(26)
    82  		table.ColMap("RootId").SetMaxSize(26)
    83  		table.ColMap("ParentId").SetMaxSize(26)
    84  		table.ColMap("OriginalId").SetMaxSize(26)
    85  		table.ColMap("Message").SetMaxSize(model.POST_MESSAGE_MAX_BYTES_V2)
    86  		table.ColMap("Type").SetMaxSize(26)
    87  		table.ColMap("Hashtags").SetMaxSize(1000)
    88  		table.ColMap("Props").SetMaxSize(8000)
    89  		table.ColMap("Filenames").SetMaxSize(model.POST_FILENAMES_MAX_RUNES)
    90  		table.ColMap("FileIds").SetMaxSize(300)
    91  	}
    92  
    93  	return s
    94  }
    95  
    96  func (s *SqlPostStore) createIndexesIfNotExists() {
    97  	s.CreateIndexIfNotExists("idx_posts_update_at", "Posts", "UpdateAt")
    98  	s.CreateIndexIfNotExists("idx_posts_create_at", "Posts", "CreateAt")
    99  	s.CreateIndexIfNotExists("idx_posts_delete_at", "Posts", "DeleteAt")
   100  	s.CreateIndexIfNotExists("idx_posts_channel_id", "Posts", "ChannelId")
   101  	s.CreateIndexIfNotExists("idx_posts_root_id", "Posts", "RootId")
   102  	s.CreateIndexIfNotExists("idx_posts_user_id", "Posts", "UserId")
   103  	s.CreateIndexIfNotExists("idx_posts_is_pinned", "Posts", "IsPinned")
   104  
   105  	s.CreateCompositeIndexIfNotExists("idx_posts_channel_id_update_at", "Posts", []string{"ChannelId", "UpdateAt"})
   106  	s.CreateCompositeIndexIfNotExists("idx_posts_channel_id_delete_at_create_at", "Posts", []string{"ChannelId", "DeleteAt", "CreateAt"})
   107  
   108  	s.CreateFullTextIndexIfNotExists("idx_posts_message_txt", "Posts", "Message")
   109  	s.CreateFullTextIndexIfNotExists("idx_posts_hashtags_txt", "Posts", "Hashtags")
   110  }
   111  
   112  func (s *SqlPostStore) SaveMultiple(posts []*model.Post) ([]*model.Post, int, error) {
   113  	channelNewPosts := make(map[string]int)
   114  	maxDateNewPosts := make(map[string]int64)
   115  	rootIds := make(map[string]int)
   116  	maxDateRootIds := make(map[string]int64)
   117  	for idx, post := range posts {
   118  		if post.Id != "" {
   119  			return nil, idx, store.NewErrInvalidInput("Post", "id", post.Id)
   120  		}
   121  		post.PreSave()
   122  		maxPostSize := s.GetMaxPostSize()
   123  		if err := post.IsValid(maxPostSize); err != nil {
   124  			return nil, idx, err
   125  		}
   126  
   127  		currentChannelCount, ok := channelNewPosts[post.ChannelId]
   128  		if !ok {
   129  			if post.IsJoinLeaveMessage() {
   130  				channelNewPosts[post.ChannelId] = 0
   131  			} else {
   132  				channelNewPosts[post.ChannelId] = 1
   133  			}
   134  			maxDateNewPosts[post.ChannelId] = post.CreateAt
   135  		} else {
   136  			if !post.IsJoinLeaveMessage() {
   137  				channelNewPosts[post.ChannelId] = currentChannelCount + 1
   138  			}
   139  			if post.CreateAt > maxDateNewPosts[post.ChannelId] {
   140  				maxDateNewPosts[post.ChannelId] = post.CreateAt
   141  			}
   142  		}
   143  
   144  		if post.RootId == "" {
   145  			continue
   146  		}
   147  
   148  		currentRootCount, ok := rootIds[post.RootId]
   149  		if !ok {
   150  			rootIds[post.RootId] = 1
   151  			maxDateRootIds[post.RootId] = post.CreateAt
   152  		} else {
   153  			rootIds[post.RootId] = currentRootCount + 1
   154  			if post.CreateAt > maxDateRootIds[post.RootId] {
   155  				maxDateRootIds[post.RootId] = post.CreateAt
   156  			}
   157  		}
   158  	}
   159  
   160  	builder := s.getQueryBuilder().Insert("Posts").Columns(postSliceColumns()...)
   161  	for _, post := range posts {
   162  		builder = builder.Values(postToSlice(post)...)
   163  	}
   164  	query, args, err := builder.ToSql()
   165  	if err != nil {
   166  		return nil, -1, errors.Wrap(err, "post_tosql")
   167  	}
   168  
   169  	transaction, err := s.GetMaster().Begin()
   170  	if err != nil {
   171  		return posts, -1, errors.Wrap(err, "begin_transaction")
   172  	}
   173  
   174  	defer finalizeTransaction(transaction)
   175  
   176  	if _, err = transaction.Exec(query, args...); err != nil {
   177  		return nil, -1, errors.Wrap(err, "failed to save Post")
   178  	}
   179  
   180  	if err = s.updateThreadsFromPosts(transaction, posts); err != nil {
   181  		mlog.Warn("Error updating posts, thread update failed", mlog.Err(err))
   182  	}
   183  
   184  	if err = transaction.Commit(); err != nil {
   185  		// don't need to rollback here since the transaction is already closed
   186  		return posts, -1, errors.Wrap(err, "commit_transaction")
   187  	}
   188  
   189  	for channelId, count := range channelNewPosts {
   190  		if _, err = s.GetMaster().Exec("UPDATE Channels SET LastPostAt = GREATEST(:LastPostAt, LastPostAt), TotalMsgCount = TotalMsgCount + :Count WHERE Id = :ChannelId", map[string]interface{}{"LastPostAt": maxDateNewPosts[channelId], "ChannelId": channelId, "Count": count}); err != nil {
   191  			mlog.Warn("Error updating Channel LastPostAt.", mlog.Err(err))
   192  		}
   193  	}
   194  
   195  	for rootId := range rootIds {
   196  		if _, err = s.GetMaster().Exec("UPDATE Posts SET UpdateAt = :UpdateAt WHERE Id = :RootId", map[string]interface{}{"UpdateAt": maxDateRootIds[rootId], "RootId": rootId}); err != nil {
   197  			mlog.Warn("Error updating Post UpdateAt.", mlog.Err(err))
   198  		}
   199  	}
   200  
   201  	unknownRepliesPosts := []*model.Post{}
   202  	for _, post := range posts {
   203  		if post.RootId == "" {
   204  			count, ok := rootIds[post.Id]
   205  			if ok {
   206  				post.ReplyCount += int64(count)
   207  			}
   208  		} else {
   209  			unknownRepliesPosts = append(unknownRepliesPosts, post)
   210  		}
   211  	}
   212  
   213  	if len(unknownRepliesPosts) > 0 {
   214  		if err := s.populateReplyCount(unknownRepliesPosts); err != nil {
   215  			mlog.Warn("Unable to populate the reply count in some posts.", mlog.Err(err))
   216  		}
   217  	}
   218  
   219  	return posts, -1, nil
   220  }
   221  
   222  func (s *SqlPostStore) Save(post *model.Post) (*model.Post, error) {
   223  	posts, _, err := s.SaveMultiple([]*model.Post{post})
   224  	if err != nil {
   225  		return nil, err
   226  	}
   227  	return posts[0], nil
   228  }
   229  
   230  func (s *SqlPostStore) populateReplyCount(posts []*model.Post) error {
   231  	rootIds := []string{}
   232  	for _, post := range posts {
   233  		rootIds = append(rootIds, post.RootId)
   234  	}
   235  	countList := []struct {
   236  		RootId string
   237  		Count  int64
   238  	}{}
   239  	query := s.getQueryBuilder().Select("RootId, COUNT(Id) AS Count").From("Posts").Where(sq.Eq{"RootId": rootIds}).Where(sq.Eq{"DeleteAt": 0}).GroupBy("RootId")
   240  
   241  	queryString, args, err := query.ToSql()
   242  	if err != nil {
   243  		return errors.Wrap(err, "post_tosql")
   244  	}
   245  	_, err = s.GetMaster().Select(&countList, queryString, args...)
   246  	if err != nil {
   247  		return errors.Wrap(err, "failed to count Posts")
   248  	}
   249  
   250  	counts := map[string]int64{}
   251  	for _, count := range countList {
   252  		counts[count.RootId] = count.Count
   253  	}
   254  
   255  	for _, post := range posts {
   256  		count, ok := counts[post.RootId]
   257  		if !ok {
   258  			post.ReplyCount = 0
   259  		}
   260  		post.ReplyCount = count
   261  	}
   262  
   263  	return nil
   264  }
   265  
   266  func (s *SqlPostStore) Update(newPost *model.Post, oldPost *model.Post) (*model.Post, error) {
   267  	newPost.UpdateAt = model.GetMillis()
   268  	newPost.PreCommit()
   269  
   270  	oldPost.DeleteAt = newPost.UpdateAt
   271  	oldPost.UpdateAt = newPost.UpdateAt
   272  	oldPost.OriginalId = oldPost.Id
   273  	oldPost.Id = model.NewId()
   274  	oldPost.PreCommit()
   275  
   276  	maxPostSize := s.GetMaxPostSize()
   277  
   278  	if err := newPost.IsValid(maxPostSize); err != nil {
   279  		return nil, err
   280  	}
   281  
   282  	if _, err := s.GetMaster().Update(newPost); err != nil {
   283  		return nil, errors.Wrapf(err, "failed to update Post with id=%s", newPost.Id)
   284  	}
   285  
   286  	time := model.GetMillis()
   287  	s.GetMaster().Exec("UPDATE Channels SET LastPostAt = :LastPostAt  WHERE Id = :ChannelId AND LastPostAt < :LastPostAt", map[string]interface{}{"LastPostAt": time, "ChannelId": newPost.ChannelId})
   288  
   289  	if newPost.RootId != "" {
   290  		s.GetMaster().Exec("UPDATE Posts SET UpdateAt = :UpdateAt WHERE Id = :RootId AND UpdateAt < :UpdateAt", map[string]interface{}{"UpdateAt": time, "RootId": newPost.RootId})
   291  		s.GetMaster().Exec("UPDATE Threads SET LastReplyAt = :UpdateAt WHERE PostId = :RootId", map[string]interface{}{"UpdateAt": time, "RootId": newPost.RootId})
   292  	}
   293  
   294  	// mark the old post as deleted
   295  	s.GetMaster().Insert(oldPost)
   296  
   297  	return newPost, nil
   298  }
   299  
   300  func (s *SqlPostStore) OverwriteMultiple(posts []*model.Post) ([]*model.Post, int, error) {
   301  	updateAt := model.GetMillis()
   302  	maxPostSize := s.GetMaxPostSize()
   303  	for idx, post := range posts {
   304  		post.UpdateAt = updateAt
   305  		if appErr := post.IsValid(maxPostSize); appErr != nil {
   306  			return nil, idx, appErr
   307  		}
   308  	}
   309  
   310  	tx, err := s.GetMaster().Begin()
   311  	if err != nil {
   312  		return nil, -1, errors.Wrap(err, "begin_transaction")
   313  	}
   314  	for idx, post := range posts {
   315  		if _, err = tx.Update(post); err != nil {
   316  			txErr := tx.Rollback()
   317  			if txErr != nil {
   318  				return nil, idx, errors.Wrap(txErr, "rollback_transaction")
   319  			}
   320  
   321  			return nil, idx, errors.Wrap(err, "failed to update Post")
   322  		}
   323  		if post.RootId != "" {
   324  			tx.Exec("UPDATE Threads SET LastReplyAt = :UpdateAt WHERE PostId = :RootId", map[string]interface{}{"UpdateAt": updateAt, "RootId": post.Id})
   325  		}
   326  	}
   327  	err = tx.Commit()
   328  	if err != nil {
   329  		return nil, -1, errors.Wrap(err, "commit_transaction")
   330  	}
   331  
   332  	return posts, -1, nil
   333  }
   334  
   335  func (s *SqlPostStore) Overwrite(post *model.Post) (*model.Post, error) {
   336  	posts, _, err := s.OverwriteMultiple([]*model.Post{post})
   337  	if err != nil {
   338  		return nil, err
   339  	}
   340  
   341  	return posts[0], nil
   342  }
   343  
   344  func (s *SqlPostStore) GetFlaggedPosts(userId string, offset int, limit int) (*model.PostList, error) {
   345  	pl := model.NewPostList()
   346  
   347  	var posts []*model.Post
   348  	if _, err := s.GetReplica().Select(&posts, "SELECT *, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE Id IN (SELECT Name FROM Preferences WHERE UserId = :UserId AND Category = :Category) AND DeleteAt = 0 ORDER BY CreateAt DESC LIMIT :Limit OFFSET :Offset", map[string]interface{}{"UserId": userId, "Category": model.PREFERENCE_CATEGORY_FLAGGED_POST, "Offset": offset, "Limit": limit}); err != nil {
   349  		return nil, errors.Wrap(err, "failed to find Posts")
   350  	}
   351  
   352  	for _, post := range posts {
   353  		pl.AddPost(post)
   354  		pl.AddOrder(post.Id)
   355  	}
   356  
   357  	return pl, nil
   358  }
   359  
   360  func (s *SqlPostStore) GetFlaggedPostsForTeam(userId, teamId string, offset int, limit int) (*model.PostList, error) {
   361  	pl := model.NewPostList()
   362  
   363  	var posts []*model.Post
   364  
   365  	query := `
   366              SELECT
   367                  A.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN A.RootId = '' THEN A.Id ELSE A.RootId END) AND Posts.DeleteAt = 0) as ReplyCount
   368              FROM
   369                  (SELECT
   370                      *
   371                  FROM
   372                      Posts
   373                  WHERE
   374                      Id
   375                  IN
   376                      (SELECT
   377                          Name
   378                      FROM
   379                          Preferences
   380                      WHERE
   381                          UserId = :UserId
   382                          AND Category = :Category)
   383                          AND DeleteAt = 0
   384                  ) as A
   385              INNER JOIN Channels as B
   386                  ON B.Id = A.ChannelId
   387              WHERE B.TeamId = :TeamId OR B.TeamId = ''
   388              ORDER BY CreateAt DESC
   389              LIMIT :Limit OFFSET :Offset`
   390  
   391  	if _, err := s.GetReplica().Select(&posts, query, map[string]interface{}{"UserId": userId, "Category": model.PREFERENCE_CATEGORY_FLAGGED_POST, "Offset": offset, "Limit": limit, "TeamId": teamId}); err != nil {
   392  		return nil, errors.Wrap(err, "failed to find Posts")
   393  	}
   394  
   395  	for _, post := range posts {
   396  		pl.AddPost(post)
   397  		pl.AddOrder(post.Id)
   398  	}
   399  
   400  	return pl, nil
   401  }
   402  
   403  func (s *SqlPostStore) GetFlaggedPostsForChannel(userId, channelId string, offset int, limit int) (*model.PostList, error) {
   404  	pl := model.NewPostList()
   405  
   406  	var posts []*model.Post
   407  	query := `
   408  		SELECT
   409  			*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount
   410  		FROM Posts p
   411  		WHERE
   412  			Id IN (SELECT Name FROM Preferences WHERE UserId = :UserId AND Category = :Category)
   413  			AND ChannelId = :ChannelId
   414  			AND DeleteAt = 0
   415  		ORDER BY CreateAt DESC
   416  		LIMIT :Limit OFFSET :Offset`
   417  
   418  	if _, err := s.GetReplica().Select(&posts, query, map[string]interface{}{"UserId": userId, "Category": model.PREFERENCE_CATEGORY_FLAGGED_POST, "ChannelId": channelId, "Offset": offset, "Limit": limit}); err != nil {
   419  		return nil, errors.Wrap(err, "failed to find Posts")
   420  	}
   421  	for _, post := range posts {
   422  		pl.AddPost(post)
   423  		pl.AddOrder(post.Id)
   424  	}
   425  
   426  	return pl, nil
   427  }
   428  func (s *SqlPostStore) getPostWithCollapsedThreads(id string, extended bool) (*model.PostList, error) {
   429  	if id == "" {
   430  		return nil, store.NewErrInvalidInput("Post", "id", id)
   431  	}
   432  
   433  	var columns []string
   434  	for _, c := range postSliceColumns() {
   435  		columns = append(columns, "Posts."+c)
   436  	}
   437  	columns = append(columns, "COALESCE(Threads.ReplyCount, 0) as ThreadReplyCount", "COALESCE(Threads.LastReplyAt, 0) as LastReplyAt", "COALESCE(Threads.Participants, '[]') as ThreadParticipants")
   438  	var post postWithExtra
   439  
   440  	postFetchQuery, args, _ := s.getQueryBuilder().
   441  		Select(columns...).
   442  		From("Posts").
   443  		LeftJoin("Threads ON Threads.PostId = Id").
   444  		Where(sq.Eq{"DeleteAt": 0}).
   445  		Where(sq.Eq{"Id": id}).ToSql()
   446  
   447  	err := s.GetReplica().SelectOne(&post, postFetchQuery, args...)
   448  	if err != nil {
   449  		if err == sql.ErrNoRows {
   450  			return nil, store.NewErrNotFound("Post", id)
   451  		}
   452  
   453  		return nil, errors.Wrapf(err, "failed to get Post with id=%s", id)
   454  	}
   455  	return s.prepareThreadedResponse([]*postWithExtra{&post}, extended, false)
   456  }
   457  
   458  func (s *SqlPostStore) Get(id string, skipFetchThreads, collapsedThreads, collapsedThreadsExtended bool) (*model.PostList, error) {
   459  	if collapsedThreads {
   460  		return s.getPostWithCollapsedThreads(id, collapsedThreadsExtended)
   461  	}
   462  	pl := model.NewPostList()
   463  
   464  	if id == "" {
   465  		return nil, store.NewErrInvalidInput("Post", "id", id)
   466  	}
   467  
   468  	var post model.Post
   469  	postFetchQuery := "SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id = :Id AND p.DeleteAt = 0"
   470  	err := s.GetReplica().SelectOne(&post, postFetchQuery, map[string]interface{}{"Id": id})
   471  	if err != nil {
   472  		if err == sql.ErrNoRows {
   473  			return nil, store.NewErrNotFound("Post", id)
   474  		}
   475  
   476  		return nil, errors.Wrapf(err, "failed to get Post with id=%s", id)
   477  	}
   478  	pl.AddPost(&post)
   479  	pl.AddOrder(id)
   480  	if !skipFetchThreads {
   481  		rootId := post.RootId
   482  
   483  		if rootId == "" {
   484  			rootId = post.Id
   485  		}
   486  
   487  		if rootId == "" {
   488  			return nil, errors.Wrapf(err, "invalid rootId with value=%s", rootId)
   489  		}
   490  
   491  		var posts []*model.Post
   492  		_, err = s.GetReplica().Select(&posts, "SELECT *, (SELECT count(Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE (Id = :Id OR RootId = :RootId) AND DeleteAt = 0", map[string]interface{}{"Id": rootId, "RootId": rootId})
   493  		if err != nil {
   494  			return nil, errors.Wrap(err, "failed to find Posts")
   495  		}
   496  
   497  		for _, p := range posts {
   498  			pl.AddPost(p)
   499  			pl.AddOrder(p.Id)
   500  		}
   501  	}
   502  	return pl, nil
   503  }
   504  
   505  func (s *SqlPostStore) GetSingle(id string) (*model.Post, error) {
   506  	var post model.Post
   507  	err := s.GetReplica().SelectOne(&post, "SELECT * FROM Posts WHERE Id = :Id AND DeleteAt = 0", map[string]interface{}{"Id": id})
   508  	if err != nil {
   509  		if err == sql.ErrNoRows {
   510  			return nil, store.NewErrNotFound("Post", id)
   511  		}
   512  
   513  		return nil, errors.Wrapf(err, "failed to get Post with id=%s", id)
   514  	}
   515  	return &post, nil
   516  }
   517  
   518  type etagPosts struct {
   519  	Id       string
   520  	UpdateAt int64
   521  }
   522  
   523  //nolint:unparam
   524  func (s *SqlPostStore) InvalidateLastPostTimeCache(channelId string) {
   525  }
   526  
   527  //nolint:unparam
   528  func (s *SqlPostStore) GetEtag(channelId string, allowFromCache, collapsedThreads bool) string {
   529  	q := s.getQueryBuilder().Select("Id", "UpdateAt").From("Posts").Where(sq.Eq{"ChannelId": channelId}).OrderBy("UpdateAt DESC").Limit(1)
   530  	if collapsedThreads {
   531  		q.Where(sq.Eq{"RootId": ""})
   532  	}
   533  	sql, args, _ := q.ToSql()
   534  
   535  	var et etagPosts
   536  	err := s.GetReplica().SelectOne(&et, sql, args...)
   537  	var result string
   538  	if err != nil {
   539  		result = fmt.Sprintf("%v.%v", model.CurrentVersion, model.GetMillis())
   540  	} else {
   541  		result = fmt.Sprintf("%v.%v", model.CurrentVersion, et.UpdateAt)
   542  	}
   543  
   544  	return result
   545  }
   546  
   547  func (s *SqlPostStore) Delete(postId string, time int64, deleteByID string) error {
   548  	var post model.Post
   549  	err := s.GetReplica().SelectOne(&post, "SELECT * FROM Posts WHERE Id = :Id AND DeleteAt = 0", map[string]interface{}{"Id": postId})
   550  	if err != nil {
   551  		if err == sql.ErrNoRows {
   552  			return store.NewErrNotFound("Post", postId)
   553  		}
   554  
   555  		return errors.Wrapf(err, "failed to delete Post with id=%s", postId)
   556  	}
   557  
   558  	post.AddProp(model.POST_PROPS_DELETE_BY, deleteByID)
   559  
   560  	_, err = s.GetMaster().Exec("UPDATE Posts SET DeleteAt = :DeleteAt, UpdateAt = :UpdateAt, Props = :Props WHERE Id = :Id OR RootId = :RootId", map[string]interface{}{"DeleteAt": time, "UpdateAt": time, "Id": postId, "RootId": postId, "Props": model.StringInterfaceToJson(post.GetProps())})
   561  	if err != nil {
   562  		return errors.Wrap(err, "failed to update Posts")
   563  	}
   564  
   565  	return s.cleanupThreads(post.Id, post.RootId, false)
   566  }
   567  
   568  func (s *SqlPostStore) permanentDelete(postId string) error {
   569  	var post model.Post
   570  	err := s.GetReplica().SelectOne(&post, "SELECT * FROM Posts WHERE Id = :Id AND DeleteAt = 0", map[string]interface{}{"Id": postId})
   571  	if err != nil && err != sql.ErrNoRows {
   572  		return errors.Wrapf(err, "failed to get Post with id=%s", postId)
   573  	}
   574  	if err = s.cleanupThreads(post.Id, post.RootId, true); err != nil {
   575  		return errors.Wrapf(err, "failed to cleanup threads for Post with id=%s", postId)
   576  	}
   577  
   578  	if _, err = s.GetMaster().Exec("DELETE FROM Posts WHERE Id = :Id OR RootId = :RootId", map[string]interface{}{"Id": postId, "RootId": postId}); err != nil {
   579  		return errors.Wrapf(err, "failed to delete Post with id=%s", postId)
   580  	}
   581  
   582  	return nil
   583  }
   584  
   585  type postIds struct {
   586  	Id     string
   587  	RootId string
   588  	UserId string
   589  }
   590  
   591  func (s *SqlPostStore) permanentDeleteAllCommentByUser(userId string) error {
   592  	results := []postIds{}
   593  	_, err := s.GetMaster().Select(&results, "Select Id, RootId FROM Posts WHERE UserId = :UserId AND RootId != ''", map[string]interface{}{"UserId": userId})
   594  	if err != nil {
   595  		return errors.Wrapf(err, "failed to fetch Posts with userId=%s", userId)
   596  	}
   597  
   598  	for _, ids := range results {
   599  		if err = s.cleanupThreads(ids.Id, ids.RootId, true); err != nil {
   600  			return err
   601  		}
   602  	}
   603  
   604  	_, err = s.GetMaster().Exec("DELETE FROM Posts WHERE UserId = :UserId AND RootId != ''", map[string]interface{}{"UserId": userId})
   605  	if err != nil {
   606  		return errors.Wrapf(err, "failed to delete Posts with userId=%s", userId)
   607  	}
   608  	return nil
   609  }
   610  
   611  func (s *SqlPostStore) PermanentDeleteByUser(userId string) error {
   612  	// First attempt to delete all the comments for a user
   613  	if err := s.permanentDeleteAllCommentByUser(userId); err != nil {
   614  		return err
   615  	}
   616  
   617  	// Now attempt to delete all the root posts for a user. This will also
   618  	// delete all the comments for each post
   619  	found := true
   620  	count := 0
   621  
   622  	for found {
   623  		var ids []string
   624  		_, err := s.GetMaster().Select(&ids, "SELECT Id FROM Posts WHERE UserId = :UserId LIMIT 1000", map[string]interface{}{"UserId": userId})
   625  		if err != nil {
   626  			return errors.Wrapf(err, "failed to find Posts with userId=%s", userId)
   627  		}
   628  
   629  		found = false
   630  		for _, id := range ids {
   631  			found = true
   632  			if err = s.permanentDelete(id); err != nil {
   633  				return err
   634  			}
   635  		}
   636  
   637  		// This is a fail safe, give up if more than 10k messages
   638  		count++
   639  		if count >= 10 {
   640  			return errors.Wrapf(err, "too many Posts to delete with userId=%s", userId)
   641  		}
   642  	}
   643  
   644  	return nil
   645  }
   646  
   647  func (s *SqlPostStore) PermanentDeleteByChannel(channelId string) error {
   648  	results := []postIds{}
   649  	_, err := s.GetMaster().Select(&results, "SELECT Id, RootId, UserId FROM Posts WHERE ChannelId = :ChannelId", map[string]interface{}{"ChannelId": channelId})
   650  	if err != nil {
   651  		return errors.Wrapf(err, "failed to fetch Posts with channelId=%s", channelId)
   652  	}
   653  
   654  	for _, ids := range results {
   655  		if err = s.cleanupThreads(ids.Id, ids.RootId, true); err != nil {
   656  			return err
   657  		}
   658  	}
   659  
   660  	if _, err := s.GetMaster().Exec("DELETE FROM Posts WHERE ChannelId = :ChannelId", map[string]interface{}{"ChannelId": channelId}); err != nil {
   661  		return errors.Wrapf(err, "failed to delete Posts with channelId=%s", channelId)
   662  	}
   663  	return nil
   664  }
   665  
   666  func (s *SqlPostStore) prepareThreadedResponse(posts []*postWithExtra, extended, reversed bool) (*model.PostList, error) {
   667  	list := model.NewPostList()
   668  	var userIds []string
   669  	userIdMap := map[string]bool{}
   670  	for _, thread := range posts {
   671  		for _, participantId := range thread.ThreadParticipants {
   672  			if _, ok := userIdMap[participantId]; !ok {
   673  				userIdMap[participantId] = true
   674  				userIds = append(userIds, participantId)
   675  			}
   676  		}
   677  	}
   678  	var users []*model.User
   679  	if extended {
   680  		var err error
   681  		users, err = s.User().GetProfileByIds(context.Background(), userIds, &store.UserGetByIdsOpts{}, true)
   682  		if err != nil {
   683  			return nil, err
   684  		}
   685  	} else {
   686  		for _, userId := range userIds {
   687  			users = append(users, &model.User{Id: userId})
   688  		}
   689  	}
   690  	processPost := func(p *postWithExtra) error {
   691  		p.Post.ReplyCount = p.ThreadReplyCount
   692  		for _, th := range p.ThreadParticipants {
   693  			var participant *model.User
   694  			for _, u := range users {
   695  				if u.Id == th {
   696  					participant = u
   697  					break
   698  				}
   699  			}
   700  			if participant == nil {
   701  				return errors.New("cannot find thread participant with id=" + th)
   702  			}
   703  			p.Post.Participants = append(p.Post.Participants, participant)
   704  		}
   705  		return nil
   706  	}
   707  
   708  	l := len(posts)
   709  	for i := range posts {
   710  		idx := i
   711  		// We need to flip the order if we selected backwards
   712  
   713  		if reversed {
   714  			idx = l - i - 1
   715  		}
   716  		if err := processPost(posts[idx]); err != nil {
   717  			return nil, err
   718  		}
   719  		list.AddPost(&posts[idx].Post)
   720  		list.AddOrder(posts[idx].Id)
   721  	}
   722  
   723  	return list, nil
   724  }
   725  
   726  func (s *SqlPostStore) getPostsCollapsedThreads(options model.GetPostsOptions) (*model.PostList, error) {
   727  	var columns []string
   728  	for _, c := range postSliceColumns() {
   729  		columns = append(columns, "Posts."+c)
   730  	}
   731  	columns = append(columns, "COALESCE(Threads.ReplyCount, 0) as ThreadReplyCount", "COALESCE(Threads.LastReplyAt, 0) as LastReplyAt", "COALESCE(Threads.Participants, '[]') as ThreadParticipants")
   732  	var posts []*postWithExtra
   733  	offset := options.PerPage * options.Page
   734  
   735  	postFetchQuery, args, _ := s.getQueryBuilder().
   736  		Select(columns...).
   737  		From("Posts").
   738  		LeftJoin("Threads ON Threads.PostId = Id").
   739  		Where(sq.Eq{"DeleteAt": 0}).
   740  		Where(sq.Eq{"Posts.ChannelId": options.ChannelId}).
   741  		Where(sq.Eq{"RootId": ""}).
   742  		Limit(uint64(options.PerPage)).
   743  		Offset(uint64(offset)).
   744  		OrderBy("CreateAt DESC").ToSql()
   745  
   746  	_, err := s.GetReplica().Select(&posts, postFetchQuery, args...)
   747  
   748  	if err != nil {
   749  		return nil, errors.Wrapf(err, "failed to find Posts with channelId=%s", options.ChannelId)
   750  	}
   751  
   752  	return s.prepareThreadedResponse(posts, options.CollapsedThreadsExtended, false)
   753  }
   754  
   755  func (s *SqlPostStore) GetPosts(options model.GetPostsOptions, _ bool) (*model.PostList, error) {
   756  	if options.PerPage > 1000 {
   757  		return nil, store.NewErrInvalidInput("Post", "<options.PerPage>", options.PerPage)
   758  	}
   759  	if options.CollapsedThreads {
   760  		return s.getPostsCollapsedThreads(options)
   761  	}
   762  	offset := options.PerPage * options.Page
   763  
   764  	rpc := make(chan store.StoreResult, 1)
   765  	go func() {
   766  		posts, err := s.getRootPosts(options.ChannelId, offset, options.PerPage, options.SkipFetchThreads)
   767  		rpc <- store.StoreResult{Data: posts, NErr: err}
   768  		close(rpc)
   769  	}()
   770  	cpc := make(chan store.StoreResult, 1)
   771  	go func() {
   772  		posts, err := s.getParentsPosts(options.ChannelId, offset, options.PerPage, options.SkipFetchThreads)
   773  		cpc <- store.StoreResult{Data: posts, NErr: err}
   774  		close(cpc)
   775  	}()
   776  
   777  	list := model.NewPostList()
   778  
   779  	rpr := <-rpc
   780  	if rpr.NErr != nil {
   781  		return nil, rpr.NErr
   782  	}
   783  
   784  	cpr := <-cpc
   785  	if cpr.NErr != nil {
   786  		return nil, cpr.NErr
   787  	}
   788  
   789  	posts := rpr.Data.([]*model.Post)
   790  	parents := cpr.Data.([]*model.Post)
   791  
   792  	for _, p := range posts {
   793  		list.AddPost(p)
   794  		list.AddOrder(p.Id)
   795  	}
   796  
   797  	for _, p := range parents {
   798  		list.AddPost(p)
   799  	}
   800  
   801  	list.MakeNonNil()
   802  
   803  	return list, nil
   804  }
   805  
   806  func (s *SqlPostStore) getPostsSinceCollapsedThreads(options model.GetPostsSinceOptions) (*model.PostList, error) {
   807  	var columns []string
   808  	for _, c := range postSliceColumns() {
   809  		columns = append(columns, "Posts."+c)
   810  	}
   811  	columns = append(columns, "COALESCE(Threads.ReplyCount, 0) as ThreadReplyCount", "COALESCE(Threads.LastReplyAt, 0) as LastReplyAt", "COALESCE(Threads.Participants, '[]') as ThreadParticipants")
   812  	var posts []*postWithExtra
   813  
   814  	postFetchQuery, args, _ := s.getQueryBuilder().
   815  		Select(columns...).
   816  		From("Posts").
   817  		LeftJoin("Threads ON Threads.PostId = Id").
   818  		Where(sq.Eq{"DeleteAt": 0}).
   819  		Where(sq.Eq{"Posts.ChannelId": options.ChannelId}).
   820  		Where(sq.Gt{"UpdateAt": options.Time}).
   821  		Where(sq.Eq{"RootId": ""}).
   822  		OrderBy("CreateAt DESC").ToSql()
   823  
   824  	_, err := s.GetReplica().Select(&posts, postFetchQuery, args...)
   825  
   826  	if err != nil {
   827  		return nil, errors.Wrapf(err, "failed to find Posts with channelId=%s", options.ChannelId)
   828  	}
   829  	return s.prepareThreadedResponse(posts, options.CollapsedThreadsExtended, false)
   830  }
   831  
   832  //nolint:unparam
   833  func (s *SqlPostStore) GetPostsSince(options model.GetPostsSinceOptions, allowFromCache bool) (*model.PostList, error) {
   834  	if options.CollapsedThreads {
   835  		return s.getPostsSinceCollapsedThreads(options)
   836  	}
   837  
   838  	var posts []*model.Post
   839  
   840  	replyCountQuery1 := ""
   841  	replyCountQuery2 := ""
   842  	if options.SkipFetchThreads {
   843  		replyCountQuery1 = `, (SELECT COUNT(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p1.RootId = '' THEN p1.Id ELSE p1.RootId END) AND Posts.DeleteAt = 0) as ReplyCount`
   844  		replyCountQuery2 = `, (SELECT COUNT(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN cte.RootId = '' THEN cte.Id ELSE cte.RootId END) AND Posts.DeleteAt = 0) as ReplyCount`
   845  	}
   846  	var query string
   847  
   848  	// union of IDs and then join to get full posts is faster in mysql
   849  	if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   850  		query = `SELECT *` + replyCountQuery1 + ` FROM Posts p1 JOIN (
   851  			(SELECT
   852                Id
   853  			  FROM
   854  				  Posts p2
   855  			  WHERE
   856  				  (UpdateAt > :Time
   857  					  AND ChannelId = :ChannelId)
   858  				  LIMIT 1000)
   859  			  UNION
   860  				  (SELECT
   861  					  Id
   862  				  FROM
   863  					  Posts p3
   864  				  WHERE
   865  					  Id
   866  				  IN
   867  					  (SELECT * FROM (SELECT
   868  						  RootId
   869  					  FROM
   870  						  Posts
   871  					  WHERE
   872  						  UpdateAt > :Time
   873  							  AND ChannelId = :ChannelId
   874  					  LIMIT 1000) temp_tab))
   875  			) j ON p1.Id = j.Id
   876            ORDER BY CreateAt DESC`
   877  	} else if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
   878  		query = `WITH cte AS (SELECT
   879  		       *
   880  		FROM
   881  		       Posts
   882  		WHERE
   883  		       UpdateAt > :Time AND ChannelId = :ChannelId
   884  		       LIMIT 1000)
   885  		(SELECT *` + replyCountQuery2 + ` FROM cte)
   886  		UNION
   887  		(SELECT *` + replyCountQuery1 + ` FROM Posts p1 WHERE id in (SELECT rootid FROM cte))
   888  		ORDER BY CreateAt DESC`
   889  	}
   890  	_, err := s.GetReplica().Select(&posts, query, map[string]interface{}{"ChannelId": options.ChannelId, "Time": options.Time})
   891  
   892  	if err != nil {
   893  		return nil, errors.Wrapf(err, "failed to find Posts with channelId=%s", options.ChannelId)
   894  	}
   895  
   896  	list := model.NewPostList()
   897  
   898  	for _, p := range posts {
   899  		list.AddPost(p)
   900  		if p.UpdateAt > options.Time {
   901  			list.AddOrder(p.Id)
   902  		}
   903  	}
   904  
   905  	return list, nil
   906  }
   907  
   908  func (s *SqlPostStore) GetPostsBefore(options model.GetPostsOptions) (*model.PostList, error) {
   909  	return s.getPostsAround(true, options)
   910  }
   911  
   912  func (s *SqlPostStore) GetPostsAfter(options model.GetPostsOptions) (*model.PostList, error) {
   913  	return s.getPostsAround(false, options)
   914  }
   915  
   916  func (s *SqlPostStore) getPostsAround(before bool, options model.GetPostsOptions) (*model.PostList, error) {
   917  	if options.Page < 0 {
   918  		return nil, store.NewErrInvalidInput("Post", "<options.Page>", options.Page)
   919  	}
   920  
   921  	if options.PerPage < 0 {
   922  		return nil, store.NewErrInvalidInput("Post", "<options.PerPage>", options.PerPage)
   923  	}
   924  
   925  	offset := options.Page * options.PerPage
   926  	var posts []*postWithExtra
   927  	var parents []*model.Post
   928  
   929  	var direction string
   930  	var sort string
   931  	if before {
   932  		direction = "<"
   933  		sort = "DESC"
   934  	} else {
   935  		direction = ">"
   936  		sort = "ASC"
   937  	}
   938  	table := "Posts p"
   939  	// We force MySQL to use the right index to prevent it from accidentally
   940  	// using the index_merge_intersection optimization.
   941  	// See MM-27575.
   942  	if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   943  		table += " USE INDEX(idx_posts_channel_id_delete_at_create_at)"
   944  	}
   945  	columns := []string{"p.*"}
   946  	if options.CollapsedThreads {
   947  		columns = append(columns, "COALESCE(Threads.ReplyCount, 0) as ThreadReplyCount", "COALESCE(Threads.LastReplyAt, 0) as LastReplyAt", "COALESCE(Threads.Participants, '[]') as ThreadParticipants")
   948  	}
   949  	query := s.getQueryBuilder().Select(columns...)
   950  	replyCountSubQuery := s.getQueryBuilder().Select("COUNT(Posts.Id)").From("Posts").Where(sq.Expr("Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0"))
   951  
   952  	conditions := sq.And{
   953  		sq.Expr(`CreateAt `+direction+` (SELECT CreateAt FROM Posts WHERE Id = ?)`, options.PostId),
   954  		sq.Eq{"p.ChannelId": options.ChannelId},
   955  		sq.Eq{"DeleteAt": int(0)},
   956  	}
   957  	if options.CollapsedThreads {
   958  		conditions = append(conditions, sq.Eq{"RootId": ""})
   959  		query = query.LeftJoin("Threads ON Threads.PostId = p.Id")
   960  	} else {
   961  		query = query.Column(sq.Alias(replyCountSubQuery, "ReplyCount"))
   962  	}
   963  	query = query.From(table).
   964  		Where(conditions).
   965  		// Adding ChannelId and DeleteAt order columns
   966  		// to let mysql choose the "idx_posts_channel_id_delete_at_create_at" index always.
   967  		// See MM-24170.
   968  		OrderBy("p.ChannelId", "DeleteAt", "CreateAt "+sort).
   969  		Limit(uint64(options.PerPage)).
   970  		Offset(uint64(offset))
   971  
   972  	queryString, args, err := query.ToSql()
   973  	if err != nil {
   974  		return nil, errors.Wrap(err, "post_tosql")
   975  	}
   976  	_, err = s.GetMaster().Select(&posts, queryString, args...)
   977  	if err != nil {
   978  		return nil, errors.Wrapf(err, "failed to find Posts with channelId=%s", options.ChannelId)
   979  	}
   980  
   981  	if !options.CollapsedThreads && len(posts) > 0 {
   982  		rootIds := []string{}
   983  		for _, post := range posts {
   984  			rootIds = append(rootIds, post.Id)
   985  			if post.RootId != "" {
   986  				rootIds = append(rootIds, post.RootId)
   987  			}
   988  		}
   989  		rootQuery := s.getQueryBuilder().Select("p.*")
   990  		idQuery := sq.Or{
   991  			sq.Eq{"Id": rootIds},
   992  		}
   993  		rootQuery = rootQuery.Column(sq.Alias(replyCountSubQuery, "ReplyCount"))
   994  		if !options.SkipFetchThreads {
   995  			idQuery = append(idQuery, sq.Eq{"RootId": rootIds}) // preserve original behaviour
   996  		}
   997  
   998  		rootQuery = rootQuery.From("Posts p").
   999  			Where(sq.And{
  1000  				idQuery,
  1001  				sq.Eq{"ChannelId": options.ChannelId},
  1002  				sq.Eq{"DeleteAt": 0},
  1003  			}).
  1004  			OrderBy("CreateAt DESC")
  1005  
  1006  		rootQueryString, rootArgs, nErr := rootQuery.ToSql()
  1007  
  1008  		if nErr != nil {
  1009  			return nil, errors.Wrap(nErr, "post_tosql")
  1010  		}
  1011  		_, nErr = s.GetMaster().Select(&parents, rootQueryString, rootArgs...)
  1012  		if nErr != nil {
  1013  			return nil, errors.Wrapf(nErr, "failed to find Posts with channelId=%s", options.ChannelId)
  1014  		}
  1015  	}
  1016  
  1017  	list, err := s.prepareThreadedResponse(posts, options.CollapsedThreadsExtended, !before)
  1018  	if err != nil {
  1019  		return nil, err
  1020  	}
  1021  
  1022  	for _, p := range parents {
  1023  		list.AddPost(p)
  1024  	}
  1025  
  1026  	return list, nil
  1027  }
  1028  
  1029  func (s *SqlPostStore) GetPostIdBeforeTime(channelId string, time int64) (string, error) {
  1030  	return s.getPostIdAroundTime(channelId, time, true)
  1031  }
  1032  
  1033  func (s *SqlPostStore) GetPostIdAfterTime(channelId string, time int64) (string, error) {
  1034  	return s.getPostIdAroundTime(channelId, time, false)
  1035  }
  1036  
  1037  func (s *SqlPostStore) getPostIdAroundTime(channelId string, time int64, before bool) (string, error) {
  1038  	var direction sq.Sqlizer
  1039  	var sort string
  1040  	if before {
  1041  		direction = sq.Lt{"CreateAt": time}
  1042  		sort = "DESC"
  1043  	} else {
  1044  		direction = sq.Gt{"CreateAt": time}
  1045  		sort = "ASC"
  1046  	}
  1047  
  1048  	table := "Posts"
  1049  	// We force MySQL to use the right index to prevent it from accidentally
  1050  	// using the index_merge_intersection optimization.
  1051  	// See MM-27575.
  1052  	if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
  1053  		table += " USE INDEX(idx_posts_channel_id_delete_at_create_at)"
  1054  	}
  1055  
  1056  	query := s.getQueryBuilder().
  1057  		Select("Id").
  1058  		From(table).
  1059  		Where(sq.And{
  1060  			direction,
  1061  			sq.Eq{"ChannelId": channelId},
  1062  			sq.Eq{"DeleteAt": int(0)},
  1063  		}).
  1064  		// Adding ChannelId and DeleteAt order columns
  1065  		// to let mysql choose the "idx_posts_channel_id_delete_at_create_at" index always.
  1066  		// See MM-23369.
  1067  		OrderBy("ChannelId", "DeleteAt", "CreateAt "+sort).
  1068  		Limit(1)
  1069  
  1070  	queryString, args, err := query.ToSql()
  1071  	if err != nil {
  1072  		return "", errors.Wrap(err, "post_tosql")
  1073  	}
  1074  
  1075  	var postId string
  1076  	if err := s.GetMaster().SelectOne(&postId, queryString, args...); err != nil {
  1077  		if err != sql.ErrNoRows {
  1078  			return "", errors.Wrapf(err, "failed to get Post id with channelId=%s", channelId)
  1079  		}
  1080  	}
  1081  
  1082  	return postId, nil
  1083  }
  1084  
  1085  func (s *SqlPostStore) GetPostAfterTime(channelId string, time int64) (*model.Post, error) {
  1086  	table := "Posts"
  1087  	// We force MySQL to use the right index to prevent it from accidentally
  1088  	// using the index_merge_intersection optimization.
  1089  	// See MM-27575.
  1090  	if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
  1091  		table += " USE INDEX(idx_posts_channel_id_delete_at_create_at)"
  1092  	}
  1093  
  1094  	query := s.getQueryBuilder().
  1095  		Select("*").
  1096  		From(table).
  1097  		Where(sq.And{
  1098  			sq.Gt{"CreateAt": time},
  1099  			sq.Eq{"ChannelId": channelId},
  1100  			sq.Eq{"DeleteAt": int(0)},
  1101  		}).
  1102  		// Adding ChannelId and DeleteAt order columns
  1103  		// to let mysql choose the "idx_posts_channel_id_delete_at_create_at" index always.
  1104  		// See MM-23369.
  1105  		OrderBy("ChannelId", "DeleteAt", "CreateAt ASC").
  1106  		Limit(1)
  1107  
  1108  	queryString, args, err := query.ToSql()
  1109  	if err != nil {
  1110  		return nil, errors.Wrap(err, "post_tosql")
  1111  	}
  1112  
  1113  	var post *model.Post
  1114  	if err := s.GetMaster().SelectOne(&post, queryString, args...); err != nil {
  1115  		if err != sql.ErrNoRows {
  1116  			return nil, errors.Wrapf(err, "failed to get Post with channelId=%s", channelId)
  1117  		}
  1118  	}
  1119  
  1120  	return post, nil
  1121  }
  1122  
  1123  func (s *SqlPostStore) getRootPosts(channelId string, offset int, limit int, skipFetchThreads bool) ([]*model.Post, error) {
  1124  	var posts []*model.Post
  1125  	var fetchQuery string
  1126  	if skipFetchThreads {
  1127  		fetchQuery = "SELECT p.*, (SELECT COUNT(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE ChannelId = :ChannelId AND DeleteAt = 0 ORDER BY CreateAt DESC LIMIT :Limit OFFSET :Offset"
  1128  	} else {
  1129  		fetchQuery = "SELECT * FROM Posts WHERE ChannelId = :ChannelId AND DeleteAt = 0 ORDER BY CreateAt DESC LIMIT :Limit OFFSET :Offset"
  1130  	}
  1131  	_, err := s.GetReplica().Select(&posts, fetchQuery, map[string]interface{}{"ChannelId": channelId, "Offset": offset, "Limit": limit})
  1132  	if err != nil {
  1133  		return nil, errors.Wrap(err, "failed to find Posts")
  1134  	}
  1135  	return posts, nil
  1136  }
  1137  
  1138  func (s *SqlPostStore) getParentsPosts(channelId string, offset int, limit int, skipFetchThreads bool) ([]*model.Post, error) {
  1139  	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
  1140  		return s.getParentsPostsPostgreSQL(channelId, offset, limit, skipFetchThreads)
  1141  	}
  1142  
  1143  	// query parent Ids first
  1144  	var roots []*struct {
  1145  		RootId string
  1146  	}
  1147  	rootQuery := `
  1148  		SELECT DISTINCT
  1149  			q.RootId
  1150  		FROM
  1151  			(SELECT
  1152  				RootId
  1153  			FROM
  1154  				Posts
  1155  			WHERE
  1156  				ChannelId = :ChannelId
  1157  					AND DeleteAt = 0
  1158  			ORDER BY CreateAt DESC
  1159  			LIMIT :Limit OFFSET :Offset) q
  1160  		WHERE q.RootId != ''`
  1161  
  1162  	_, err := s.GetReplica().Select(&roots, rootQuery, map[string]interface{}{"ChannelId": channelId, "Offset": offset, "Limit": limit})
  1163  	if err != nil {
  1164  		return nil, errors.Wrap(err, "failed to find Posts")
  1165  	}
  1166  	if len(roots) == 0 {
  1167  		return nil, nil
  1168  	}
  1169  	params := make(map[string]interface{})
  1170  	placeholders := make([]string, len(roots))
  1171  	for idx, r := range roots {
  1172  		key := fmt.Sprintf(":Root%v", idx)
  1173  		params[key[1:]] = r.RootId
  1174  		placeholders[idx] = key
  1175  	}
  1176  	placeholderString := strings.Join(placeholders, ", ")
  1177  	params["ChannelId"] = channelId
  1178  	replyCountQuery := ""
  1179  	whereStatement := "p.Id IN (" + placeholderString + ")"
  1180  	if skipFetchThreads {
  1181  		replyCountQuery = `, (SELECT COUNT(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount`
  1182  	} else {
  1183  		whereStatement += " OR p.RootId IN (" + placeholderString + ")"
  1184  	}
  1185  	var posts []*model.Post
  1186  	_, err = s.GetReplica().Select(&posts, `
  1187  		SELECT p.*`+replyCountQuery+`
  1188  		FROM
  1189  			Posts p
  1190  		WHERE
  1191  			(`+whereStatement+`)
  1192  				AND ChannelId = :ChannelId
  1193  				AND DeleteAt = 0
  1194  		ORDER BY CreateAt`,
  1195  		params)
  1196  	if err != nil {
  1197  		return nil, errors.Wrap(err, "failed to find Posts")
  1198  	}
  1199  	return posts, nil
  1200  }
  1201  
  1202  func (s *SqlPostStore) getParentsPostsPostgreSQL(channelId string, offset int, limit int, skipFetchThreads bool) ([]*model.Post, error) {
  1203  	var posts []*model.Post
  1204  	replyCountQuery := ""
  1205  	onStatement := "q1.RootId = q2.Id"
  1206  	if skipFetchThreads {
  1207  		replyCountQuery = ` ,(SELECT COUNT(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN q2.RootId = '' THEN q2.Id ELSE q2.RootId END) AND Posts.DeleteAt = 0) as ReplyCount`
  1208  	} else {
  1209  		onStatement += " OR q1.RootId = q2.RootId"
  1210  	}
  1211  	_, err := s.GetReplica().Select(&posts,
  1212  		`SELECT q2.*`+replyCountQuery+`
  1213          FROM
  1214              Posts q2
  1215                  INNER JOIN
  1216              (SELECT DISTINCT
  1217                  q3.RootId
  1218              FROM
  1219                  (SELECT
  1220                      RootId
  1221                  FROM
  1222                      Posts
  1223                  WHERE
  1224                      ChannelId = :ChannelId1
  1225                          AND DeleteAt = 0
  1226                  ORDER BY CreateAt DESC
  1227                  LIMIT :Limit OFFSET :Offset) q3
  1228              WHERE q3.RootId != '') q1
  1229              ON `+onStatement+`
  1230          WHERE
  1231              ChannelId = :ChannelId2
  1232                  AND DeleteAt = 0
  1233          ORDER BY CreateAt`,
  1234  		map[string]interface{}{"ChannelId1": channelId, "Offset": offset, "Limit": limit, "ChannelId2": channelId})
  1235  	if err != nil {
  1236  		return nil, errors.Wrapf(err, "failed to find Posts with channelId=%s", channelId)
  1237  	}
  1238  	return posts, nil
  1239  }
  1240  
  1241  var specialSearchChar = []string{
  1242  	"<",
  1243  	">",
  1244  	"+",
  1245  	"-",
  1246  	"(",
  1247  	")",
  1248  	"~",
  1249  	"@",
  1250  	":",
  1251  }
  1252  
  1253  func (s *SqlPostStore) buildCreateDateFilterClause(params *model.SearchParams, queryParams map[string]interface{}) (string, map[string]interface{}) {
  1254  	searchQuery := ""
  1255  	// handle after: before: on: filters
  1256  	if params.OnDate != "" {
  1257  		onDateStart, onDateEnd := params.GetOnDateMillis()
  1258  		queryParams["OnDateStart"] = strconv.FormatInt(onDateStart, 10)
  1259  		queryParams["OnDateEnd"] = strconv.FormatInt(onDateEnd, 10)
  1260  
  1261  		// between `on date` start of day and end of day
  1262  		searchQuery += "AND CreateAt BETWEEN :OnDateStart AND :OnDateEnd "
  1263  	} else {
  1264  
  1265  		if params.ExcludedDate != "" {
  1266  			excludedDateStart, excludedDateEnd := params.GetExcludedDateMillis()
  1267  			queryParams["ExcludedDateStart"] = strconv.FormatInt(excludedDateStart, 10)
  1268  			queryParams["ExcludedDateEnd"] = strconv.FormatInt(excludedDateEnd, 10)
  1269  
  1270  			searchQuery += "AND CreateAt NOT BETWEEN :ExcludedDateStart AND :ExcludedDateEnd "
  1271  		}
  1272  
  1273  		if params.AfterDate != "" {
  1274  			afterDate := params.GetAfterDateMillis()
  1275  			queryParams["AfterDate"] = strconv.FormatInt(afterDate, 10)
  1276  
  1277  			// greater than `after date`
  1278  			searchQuery += "AND CreateAt >= :AfterDate "
  1279  		}
  1280  
  1281  		if params.BeforeDate != "" {
  1282  			beforeDate := params.GetBeforeDateMillis()
  1283  			queryParams["BeforeDate"] = strconv.FormatInt(beforeDate, 10)
  1284  
  1285  			// less than `before date`
  1286  			searchQuery += "AND CreateAt <= :BeforeDate "
  1287  		}
  1288  
  1289  		if params.ExcludedAfterDate != "" {
  1290  			afterDate := params.GetExcludedAfterDateMillis()
  1291  			queryParams["ExcludedAfterDate"] = strconv.FormatInt(afterDate, 10)
  1292  
  1293  			searchQuery += "AND CreateAt < :ExcludedAfterDate "
  1294  		}
  1295  
  1296  		if params.ExcludedBeforeDate != "" {
  1297  			beforeDate := params.GetExcludedBeforeDateMillis()
  1298  			queryParams["ExcludedBeforeDate"] = strconv.FormatInt(beforeDate, 10)
  1299  
  1300  			searchQuery += "AND CreateAt > :ExcludedBeforeDate "
  1301  		}
  1302  	}
  1303  
  1304  	return searchQuery, queryParams
  1305  }
  1306  
  1307  func (s *SqlPostStore) buildSearchChannelFilterClause(channels []string, paramPrefix string, exclusion bool, queryParams map[string]interface{}, byName bool) (string, map[string]interface{}) {
  1308  	if len(channels) == 0 {
  1309  		return "", queryParams
  1310  	}
  1311  
  1312  	clauseSlice := []string{}
  1313  	for i, channel := range channels {
  1314  		paramName := paramPrefix + strconv.FormatInt(int64(i), 10)
  1315  		clauseSlice = append(clauseSlice, ":"+paramName)
  1316  		queryParams[paramName] = channel
  1317  	}
  1318  	clause := strings.Join(clauseSlice, ", ")
  1319  	if byName {
  1320  		if exclusion {
  1321  			return "AND Name NOT IN (" + clause + ")", queryParams
  1322  		}
  1323  		return "AND Name IN (" + clause + ")", queryParams
  1324  	}
  1325  
  1326  	if exclusion {
  1327  		return "AND Id NOT IN (" + clause + ")", queryParams
  1328  	}
  1329  	return "AND Id IN (" + clause + ")", queryParams
  1330  }
  1331  
  1332  func (s *SqlPostStore) buildSearchUserFilterClause(users []string, paramPrefix string, exclusion bool, queryParams map[string]interface{}, byUsername bool) (string, map[string]interface{}) {
  1333  	if len(users) == 0 {
  1334  		return "", queryParams
  1335  	}
  1336  	clauseSlice := []string{}
  1337  	for i, user := range users {
  1338  		paramName := paramPrefix + strconv.FormatInt(int64(i), 10)
  1339  		clauseSlice = append(clauseSlice, ":"+paramName)
  1340  		queryParams[paramName] = user
  1341  	}
  1342  	clause := strings.Join(clauseSlice, ", ")
  1343  	if byUsername {
  1344  		if exclusion {
  1345  			return "AND Username NOT IN (" + clause + ")", queryParams
  1346  		}
  1347  		return "AND Username IN (" + clause + ")", queryParams
  1348  	}
  1349  	if exclusion {
  1350  		return "AND Id NOT IN (" + clause + ")", queryParams
  1351  	}
  1352  	return "AND Id IN (" + clause + ")", queryParams
  1353  }
  1354  
  1355  func (s *SqlPostStore) buildSearchPostFilterClause(fromUsers []string, excludedUsers []string, queryParams map[string]interface{}, userByUsername bool) (string, map[string]interface{}) {
  1356  	if len(fromUsers) == 0 && len(excludedUsers) == 0 {
  1357  		return "", queryParams
  1358  	}
  1359  
  1360  	filterQuery := `
  1361  		AND UserId IN (
  1362  			SELECT
  1363  				Id
  1364  			FROM
  1365  				Users,
  1366  				TeamMembers
  1367  			WHERE
  1368  				TeamMembers.TeamId = :TeamId
  1369  				AND Users.Id = TeamMembers.UserId
  1370  				FROM_USER_FILTER
  1371  				EXCLUDED_USER_FILTER)`
  1372  
  1373  	fromUserClause, queryParams := s.buildSearchUserFilterClause(fromUsers, "FromUser", false, queryParams, userByUsername)
  1374  	filterQuery = strings.Replace(filterQuery, "FROM_USER_FILTER", fromUserClause, 1)
  1375  
  1376  	excludedUserClause, queryParams := s.buildSearchUserFilterClause(excludedUsers, "ExcludedUser", true, queryParams, userByUsername)
  1377  	filterQuery = strings.Replace(filterQuery, "EXCLUDED_USER_FILTER", excludedUserClause, 1)
  1378  
  1379  	return filterQuery, queryParams
  1380  }
  1381  
  1382  func (s *SqlPostStore) Search(teamId string, userId string, params *model.SearchParams) (*model.PostList, error) {
  1383  	return s.search(teamId, userId, params, true, true)
  1384  }
  1385  
  1386  func (s *SqlPostStore) search(teamId string, userId string, params *model.SearchParams, channelsByName bool, userByUsername bool) (*model.PostList, error) {
  1387  	queryParams := map[string]interface{}{
  1388  		"TeamId": teamId,
  1389  		"UserId": userId,
  1390  	}
  1391  
  1392  	list := model.NewPostList()
  1393  	if params.Terms == "" && params.ExcludedTerms == "" &&
  1394  		len(params.InChannels) == 0 && len(params.ExcludedChannels) == 0 &&
  1395  		len(params.FromUsers) == 0 && len(params.ExcludedUsers) == 0 &&
  1396  		params.OnDate == "" && params.AfterDate == "" && params.BeforeDate == "" {
  1397  		return list, nil
  1398  	}
  1399  
  1400  	var posts []*model.Post
  1401  
  1402  	deletedQueryPart := "AND DeleteAt = 0"
  1403  	if params.IncludeDeletedChannels {
  1404  		deletedQueryPart = ""
  1405  	}
  1406  
  1407  	userIdPart := "AND UserId = :UserId"
  1408  	if params.SearchWithoutUserId {
  1409  		userIdPart = ""
  1410  	}
  1411  
  1412  	searchQuery := `
  1413  			SELECT
  1414  				* ,(SELECT COUNT(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN q2.RootId = '' THEN q2.Id ELSE q2.RootId END) AND Posts.DeleteAt = 0) as ReplyCount
  1415  			FROM
  1416  				Posts q2
  1417  			WHERE
  1418  				DeleteAt = 0
  1419  				AND Type NOT LIKE '` + model.POST_SYSTEM_MESSAGE_PREFIX + `%'
  1420  				POST_FILTER
  1421  				AND ChannelId IN (
  1422  					SELECT
  1423  						Id
  1424  					FROM
  1425  						Channels,
  1426  						ChannelMembers
  1427  					WHERE
  1428  						Id = ChannelId
  1429  							AND (TeamId = :TeamId OR TeamId = '')
  1430  							` + userIdPart + `
  1431  							` + deletedQueryPart + `
  1432  							IN_CHANNEL_FILTER
  1433  							EXCLUDED_CHANNEL_FILTER)
  1434  				CREATEDATE_CLAUSE
  1435  				SEARCH_CLAUSE
  1436  				ORDER BY CreateAt DESC
  1437  			LIMIT 100`
  1438  
  1439  	inChannelClause, queryParams := s.buildSearchChannelFilterClause(params.InChannels, "InChannel", false, queryParams, channelsByName)
  1440  	searchQuery = strings.Replace(searchQuery, "IN_CHANNEL_FILTER", inChannelClause, 1)
  1441  
  1442  	excludedChannelClause, queryParams := s.buildSearchChannelFilterClause(params.ExcludedChannels, "ExcludedChannel", true, queryParams, channelsByName)
  1443  	searchQuery = strings.Replace(searchQuery, "EXCLUDED_CHANNEL_FILTER", excludedChannelClause, 1)
  1444  
  1445  	postFilterClause, queryParams := s.buildSearchPostFilterClause(params.FromUsers, params.ExcludedUsers, queryParams, userByUsername)
  1446  	searchQuery = strings.Replace(searchQuery, "POST_FILTER", postFilterClause, 1)
  1447  
  1448  	createDateFilterClause, queryParams := s.buildCreateDateFilterClause(params, queryParams)
  1449  	searchQuery = strings.Replace(searchQuery, "CREATEDATE_CLAUSE", createDateFilterClause, 1)
  1450  
  1451  	termMap := map[string]bool{}
  1452  	terms := params.Terms
  1453  	excludedTerms := params.ExcludedTerms
  1454  
  1455  	searchType := "Message"
  1456  	if params.IsHashtag {
  1457  		searchType = "Hashtags"
  1458  		for _, term := range strings.Split(terms, " ") {
  1459  			termMap[strings.ToUpper(term)] = true
  1460  		}
  1461  	}
  1462  
  1463  	// these chars have special meaning and can be treated as spaces
  1464  	for _, c := range specialSearchChar {
  1465  		terms = strings.Replace(terms, c, " ", -1)
  1466  		excludedTerms = strings.Replace(excludedTerms, c, " ", -1)
  1467  	}
  1468  
  1469  	if terms == "" && excludedTerms == "" {
  1470  		// we've already confirmed that we have a channel or user to search for
  1471  		searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1)
  1472  	} else if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
  1473  		// Parse text for wildcards
  1474  		if wildcard, err := regexp.Compile(`\*($| )`); err == nil {
  1475  			terms = wildcard.ReplaceAllLiteralString(terms, ":* ")
  1476  			excludedTerms = wildcard.ReplaceAllLiteralString(excludedTerms, ":* ")
  1477  		}
  1478  
  1479  		excludeClause := ""
  1480  		if excludedTerms != "" {
  1481  			excludeClause = " & !(" + strings.Join(strings.Fields(excludedTerms), " | ") + ")"
  1482  		}
  1483  
  1484  		if params.OrTerms {
  1485  			queryParams["Terms"] = "(" + strings.Join(strings.Fields(terms), " | ") + ")" + excludeClause
  1486  		} else {
  1487  			queryParams["Terms"] = "(" + strings.Join(strings.Fields(terms), " & ") + ")" + excludeClause
  1488  		}
  1489  
  1490  		searchClause := fmt.Sprintf("AND to_tsvector('english', %s) @@  to_tsquery('english', :Terms)", searchType)
  1491  		searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", searchClause, 1)
  1492  	} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
  1493  		if searchType == "Message" {
  1494  			var err error
  1495  			terms, err = removeMysqlStopWordsFromTerms(terms)
  1496  			if err != nil {
  1497  				return nil, errors.Wrap(err, "failed to remove Mysql stop-words from terms")
  1498  			}
  1499  
  1500  			if terms == "" {
  1501  				return list, nil
  1502  			}
  1503  		}
  1504  
  1505  		searchClause := fmt.Sprintf("AND MATCH (%s) AGAINST (:Terms IN BOOLEAN MODE)", searchType)
  1506  		searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", searchClause, 1)
  1507  
  1508  		excludeClause := ""
  1509  		if excludedTerms != "" {
  1510  			excludeClause = " -(" + excludedTerms + ")"
  1511  		}
  1512  
  1513  		if params.OrTerms {
  1514  			queryParams["Terms"] = terms + excludeClause
  1515  		} else {
  1516  			splitTerms := []string{}
  1517  			for _, t := range strings.Fields(terms) {
  1518  				splitTerms = append(splitTerms, "+"+t)
  1519  			}
  1520  			queryParams["Terms"] = strings.Join(splitTerms, " ") + excludeClause
  1521  		}
  1522  	}
  1523  
  1524  	_, err := s.GetSearchReplica().Select(&posts, searchQuery, queryParams)
  1525  	if err != nil {
  1526  		mlog.Warn("Query error searching posts.", mlog.Err(err))
  1527  		// Don't return the error to the caller as it is of no use to the user. Instead return an empty set of search results.
  1528  	} else {
  1529  		for _, p := range posts {
  1530  			if searchType == "Hashtags" {
  1531  				exactMatch := false
  1532  				for _, tag := range strings.Split(p.Hashtags, " ") {
  1533  					if termMap[strings.ToUpper(tag)] {
  1534  						exactMatch = true
  1535  						break
  1536  					}
  1537  				}
  1538  				if !exactMatch {
  1539  					continue
  1540  				}
  1541  			}
  1542  			list.AddPost(p)
  1543  			list.AddOrder(p.Id)
  1544  		}
  1545  	}
  1546  	list.MakeNonNil()
  1547  	return list, nil
  1548  }
  1549  
  1550  func removeMysqlStopWordsFromTerms(terms string) (string, error) {
  1551  	stopWords := make([]string, len(searchlayer.MySQLStopWords))
  1552  	copy(stopWords, searchlayer.MySQLStopWords)
  1553  	re, err := regexp.Compile(fmt.Sprintf(`^(%s)$`, strings.Join(stopWords, "|")))
  1554  	if err != nil {
  1555  		return "", err
  1556  	}
  1557  
  1558  	newTerms := make([]string, 0)
  1559  	separatedTerms := strings.Fields(terms)
  1560  	for _, term := range separatedTerms {
  1561  		term = strings.TrimSpace(term)
  1562  		if term = re.ReplaceAllString(term, ""); term != "" {
  1563  			newTerms = append(newTerms, term)
  1564  		}
  1565  	}
  1566  	return strings.Join(newTerms, " "), nil
  1567  }
  1568  
  1569  func (s *SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) (model.AnalyticsRows, error) {
  1570  	query :=
  1571  		`SELECT DISTINCT
  1572  		        DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
  1573  		        COUNT(DISTINCT Posts.UserId) AS Value
  1574  		FROM Posts`
  1575  
  1576  	if teamId != "" {
  1577  		query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id AND Channels.TeamId = :TeamId AND"
  1578  	} else {
  1579  		query += " WHERE"
  1580  	}
  1581  
  1582  	query += ` Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime
  1583  		GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000))
  1584  		ORDER BY Name DESC
  1585  		LIMIT 30`
  1586  
  1587  	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
  1588  		query =
  1589  			`SELECT
  1590  				TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, COUNT(DISTINCT Posts.UserId) AS Value
  1591  			FROM Posts`
  1592  
  1593  		if teamId != "" {
  1594  			query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id AND Channels.TeamId = :TeamId AND"
  1595  		} else {
  1596  			query += " WHERE"
  1597  		}
  1598  
  1599  		query += ` Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime
  1600  			GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000))
  1601  			ORDER BY Name DESC
  1602  			LIMIT 30`
  1603  	}
  1604  
  1605  	end := utils.MillisFromTime(utils.EndOfDay(utils.Yesterday()))
  1606  	start := utils.MillisFromTime(utils.StartOfDay(utils.Yesterday().AddDate(0, 0, -31)))
  1607  
  1608  	var rows model.AnalyticsRows
  1609  	_, err := s.GetReplica().Select(
  1610  		&rows,
  1611  		query,
  1612  		map[string]interface{}{"TeamId": teamId, "StartTime": start, "EndTime": end})
  1613  	if err != nil {
  1614  		return nil, errors.Wrapf(err, "failed to find Posts with teamId=%s", teamId)
  1615  	}
  1616  	return rows, nil
  1617  }
  1618  
  1619  func (s *SqlPostStore) AnalyticsPostCountsByDay(options *model.AnalyticsPostCountsOptions) (model.AnalyticsRows, error) {
  1620  
  1621  	query :=
  1622  		`SELECT
  1623  		        DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
  1624  		        COUNT(Posts.Id) AS Value
  1625  		    FROM Posts`
  1626  
  1627  	if options.BotsOnly {
  1628  		query += " INNER JOIN Bots ON Posts.UserId = Bots.Userid"
  1629  	}
  1630  
  1631  	if options.TeamId != "" {
  1632  		query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id AND Channels.TeamId = :TeamId AND"
  1633  	} else {
  1634  		query += " WHERE"
  1635  	}
  1636  
  1637  	query += ` Posts.CreateAt <= :EndTime
  1638  		            AND Posts.CreateAt >= :StartTime
  1639  		GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000))
  1640  		ORDER BY Name DESC
  1641  		LIMIT 30`
  1642  
  1643  	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
  1644  		query =
  1645  			`SELECT
  1646  				TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, Count(Posts.Id) AS Value
  1647  			FROM Posts`
  1648  
  1649  		if options.BotsOnly {
  1650  			query += " INNER JOIN Bots ON Posts.UserId = Bots.Userid"
  1651  		}
  1652  
  1653  		if options.TeamId != "" {
  1654  			query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id  AND Channels.TeamId = :TeamId AND"
  1655  		} else {
  1656  			query += " WHERE"
  1657  		}
  1658  
  1659  		query += ` Posts.CreateAt <= :EndTime
  1660  			            AND Posts.CreateAt >= :StartTime
  1661  			GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000))
  1662  			ORDER BY Name DESC
  1663  			LIMIT 30`
  1664  	}
  1665  
  1666  	end := utils.MillisFromTime(utils.EndOfDay(utils.Yesterday()))
  1667  	start := utils.MillisFromTime(utils.StartOfDay(utils.Yesterday().AddDate(0, 0, -31)))
  1668  	if options.YesterdayOnly {
  1669  		start = utils.MillisFromTime(utils.StartOfDay(utils.Yesterday().AddDate(0, 0, -1)))
  1670  	}
  1671  
  1672  	var rows model.AnalyticsRows
  1673  	_, err := s.GetReplica().Select(
  1674  		&rows,
  1675  		query,
  1676  		map[string]interface{}{"TeamId": options.TeamId, "StartTime": start, "EndTime": end})
  1677  	if err != nil {
  1678  		return nil, errors.Wrapf(err, "failed to find Posts with teamId=%s", options.TeamId)
  1679  	}
  1680  	return rows, nil
  1681  }
  1682  
  1683  func (s *SqlPostStore) AnalyticsPostCount(teamId string, mustHaveFile bool, mustHaveHashtag bool) (int64, error) {
  1684  	query := s.getQueryBuilder().
  1685  		Select("COUNT(p.Id) AS Value").
  1686  		From("Posts p")
  1687  
  1688  	if teamId != "" {
  1689  		query = query.
  1690  			Join("Channels c ON (c.Id = p.ChannelId)").
  1691  			Where(sq.Eq{"c.TeamId": teamId})
  1692  	}
  1693  
  1694  	if mustHaveFile {
  1695  		query = query.Where(sq.Or{sq.NotEq{"p.FileIds": "[]"}, sq.NotEq{"p.Filenames": "[]"}})
  1696  	}
  1697  
  1698  	if mustHaveHashtag {
  1699  		query = query.Where(sq.NotEq{"p.Hashtags": ""})
  1700  	}
  1701  
  1702  	queryString, args, err := query.ToSql()
  1703  	if err != nil {
  1704  		return 0, errors.Wrap(err, "post_tosql")
  1705  	}
  1706  
  1707  	v, err := s.GetReplica().SelectInt(queryString, args...)
  1708  	if err != nil {
  1709  		return 0, errors.Wrap(err, "failed to count Posts")
  1710  	}
  1711  
  1712  	return v, nil
  1713  }
  1714  
  1715  func (s *SqlPostStore) GetPostsCreatedAt(channelId string, time int64) ([]*model.Post, error) {
  1716  	query := `SELECT * FROM Posts WHERE CreateAt = :CreateAt AND ChannelId = :ChannelId`
  1717  
  1718  	var posts []*model.Post
  1719  	_, err := s.GetReplica().Select(&posts, query, map[string]interface{}{"CreateAt": time, "ChannelId": channelId})
  1720  
  1721  	if err != nil {
  1722  		return nil, errors.Wrapf(err, "failed to find Posts with channelId=%s", channelId)
  1723  	}
  1724  	return posts, nil
  1725  }
  1726  
  1727  func (s *SqlPostStore) GetPostsByIds(postIds []string) ([]*model.Post, error) {
  1728  	keys, params := MapStringsToQueryParams(postIds, "Post")
  1729  
  1730  	query := `SELECT p.*, (SELECT count(Posts.Id) FROM Posts WHERE Posts.RootId = (CASE WHEN p.RootId = '' THEN p.Id ELSE p.RootId END) AND Posts.DeleteAt = 0) as ReplyCount FROM Posts p WHERE p.Id IN ` + keys + ` ORDER BY CreateAt DESC`
  1731  
  1732  	var posts []*model.Post
  1733  	_, err := s.GetReplica().Select(&posts, query, params)
  1734  
  1735  	if err != nil {
  1736  		return nil, errors.Wrap(err, "failed to find Posts")
  1737  	}
  1738  	return posts, nil
  1739  }
  1740  
  1741  func (s *SqlPostStore) GetPostsBatchForIndexing(startTime int64, endTime int64, limit int) ([]*model.PostForIndexing, error) {
  1742  	var posts []*model.PostForIndexing
  1743  	_, err := s.GetSearchReplica().Select(&posts,
  1744  		`SELECT
  1745  			PostsQuery.*, Channels.TeamId, ParentPosts.CreateAt ParentCreateAt
  1746  		FROM (
  1747  			SELECT
  1748  				*
  1749  			FROM
  1750  				Posts
  1751  			WHERE
  1752  				Posts.CreateAt >= :StartTime
  1753  			AND
  1754  				Posts.CreateAt < :EndTime
  1755  			ORDER BY
  1756  				CreateAt ASC
  1757  			LIMIT
  1758  				1000
  1759  			)
  1760  		AS
  1761  			PostsQuery
  1762  		LEFT JOIN
  1763  			Channels
  1764  		ON
  1765  			PostsQuery.ChannelId = Channels.Id
  1766  		LEFT JOIN
  1767  			Posts ParentPosts
  1768  		ON
  1769  			PostsQuery.RootId = ParentPosts.Id`,
  1770  		map[string]interface{}{"StartTime": startTime, "EndTime": endTime, "NumPosts": limit})
  1771  
  1772  	if err != nil {
  1773  		return nil, errors.Wrap(err, "failed to find Posts")
  1774  	}
  1775  	return posts, nil
  1776  }
  1777  
  1778  func (s *SqlPostStore) PermanentDeleteBatch(endTime int64, limit int64) (int64, error) {
  1779  	var query string
  1780  	if s.DriverName() == "postgres" {
  1781  		query = "DELETE from Posts WHERE Id = any (array (SELECT Id FROM Posts WHERE CreateAt < :EndTime LIMIT :Limit))"
  1782  	} else {
  1783  		query = "DELETE from Posts WHERE CreateAt < :EndTime LIMIT :Limit"
  1784  	}
  1785  
  1786  	sqlResult, err := s.GetMaster().Exec(query, map[string]interface{}{"EndTime": endTime, "Limit": limit})
  1787  	if err != nil {
  1788  		return 0, errors.Wrap(err, "failed to delete Posts")
  1789  	}
  1790  
  1791  	rowsAffected, err := sqlResult.RowsAffected()
  1792  	if err != nil {
  1793  		return 0, errors.Wrap(err, "failed to delete Posts")
  1794  	}
  1795  	return rowsAffected, nil
  1796  }
  1797  
  1798  func (s *SqlPostStore) GetOldest() (*model.Post, error) {
  1799  	var post model.Post
  1800  	err := s.GetReplica().SelectOne(&post, "SELECT * FROM Posts ORDER BY CreateAt LIMIT 1")
  1801  	if err != nil {
  1802  		if err == sql.ErrNoRows {
  1803  			return nil, store.NewErrNotFound("Post", "none")
  1804  		}
  1805  
  1806  		return nil, errors.Wrap(err, "failed to get oldest Post")
  1807  	}
  1808  
  1809  	return &post, nil
  1810  }
  1811  
  1812  func (s *SqlPostStore) determineMaxPostSize() int {
  1813  	var maxPostSizeBytes int32
  1814  
  1815  	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
  1816  		// The Post.Message column in Postgres has historically been VARCHAR(4000), but
  1817  		// may be manually enlarged to support longer posts.
  1818  		if err := s.GetReplica().SelectOne(&maxPostSizeBytes, `
  1819  			SELECT
  1820  				COALESCE(character_maximum_length, 0)
  1821  			FROM
  1822  				information_schema.columns
  1823  			WHERE
  1824  				table_name = 'posts'
  1825  			AND	column_name = 'message'
  1826  		`); err != nil {
  1827  			mlog.Warn("Unable to determine the maximum supported post size", mlog.Err(err))
  1828  		}
  1829  	} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
  1830  		// The Post.Message column in MySQL has historically been TEXT, with a maximum
  1831  		// limit of 65535.
  1832  		if err := s.GetReplica().SelectOne(&maxPostSizeBytes, `
  1833  			SELECT
  1834  				COALESCE(CHARACTER_MAXIMUM_LENGTH, 0)
  1835  			FROM
  1836  				INFORMATION_SCHEMA.COLUMNS
  1837  			WHERE
  1838  				table_schema = DATABASE()
  1839  			AND	table_name = 'Posts'
  1840  			AND	column_name = 'Message'
  1841  			LIMIT 0, 1
  1842  		`); err != nil {
  1843  			mlog.Warn("Unable to determine the maximum supported post size", mlog.Err(err))
  1844  		}
  1845  	} else {
  1846  		mlog.Warn("No implementation found to determine the maximum supported post size")
  1847  	}
  1848  
  1849  	// Assume a worst-case representation of four bytes per rune.
  1850  	maxPostSize := int(maxPostSizeBytes) / 4
  1851  
  1852  	// To maintain backwards compatibility, don't yield a maximum post
  1853  	// size smaller than the previous limit, even though it wasn't
  1854  	// actually possible to store 4000 runes in all cases.
  1855  	if maxPostSize < model.POST_MESSAGE_MAX_RUNES_V1 {
  1856  		maxPostSize = model.POST_MESSAGE_MAX_RUNES_V1
  1857  	}
  1858  
  1859  	mlog.Info("Post.Message has size restrictions", mlog.Int("max_characters", maxPostSize), mlog.Int32("max_bytes", maxPostSizeBytes))
  1860  
  1861  	return maxPostSize
  1862  }
  1863  
  1864  // GetMaxPostSize returns the maximum number of runes that may be stored in a post.
  1865  func (s *SqlPostStore) GetMaxPostSize() int {
  1866  	s.maxPostSizeOnce.Do(func() {
  1867  		s.maxPostSizeCached = s.determineMaxPostSize()
  1868  	})
  1869  	return s.maxPostSizeCached
  1870  }
  1871  
  1872  func (s *SqlPostStore) GetParentsForExportAfter(limit int, afterId string) ([]*model.PostForExport, error) {
  1873  	for {
  1874  		var rootIds []string
  1875  		_, err := s.GetReplica().Select(&rootIds,
  1876  			`SELECT
  1877  				Id
  1878  			FROM
  1879  				Posts
  1880  			WHERE
  1881  				Id > :AfterId
  1882  				AND RootId = ''
  1883  				AND DeleteAt = 0
  1884  			ORDER BY Id
  1885  			LIMIT :Limit`,
  1886  			map[string]interface{}{"Limit": limit, "AfterId": afterId})
  1887  		if err != nil {
  1888  			return nil, errors.Wrap(err, "failed to find Posts")
  1889  		}
  1890  
  1891  		var postsForExport []*model.PostForExport
  1892  		if len(rootIds) == 0 {
  1893  			return postsForExport, nil
  1894  		}
  1895  
  1896  		keys, params := MapStringsToQueryParams(rootIds, "PostId")
  1897  		_, err = s.GetSearchReplica().Select(&postsForExport, `
  1898  			SELECT
  1899  				p1.*,
  1900  				Users.Username as Username,
  1901  				Teams.Name as TeamName,
  1902  				Channels.Name as ChannelName
  1903  			FROM
  1904  				(Select * FROM Posts WHERE Id IN `+keys+`) p1
  1905  			INNER JOIN
  1906  				Channels ON p1.ChannelId = Channels.Id
  1907  			INNER JOIN
  1908  				Teams ON Channels.TeamId = Teams.Id
  1909  			INNER JOIN
  1910  				Users ON p1.UserId = Users.Id
  1911  			WHERE
  1912  				Channels.DeleteAt = 0
  1913  				AND Teams.DeleteAt = 0
  1914  			ORDER BY
  1915  				p1.Id`,
  1916  			params)
  1917  		if err != nil {
  1918  			return nil, errors.Wrap(err, "failed to find Posts")
  1919  		}
  1920  
  1921  		if len(postsForExport) == 0 {
  1922  			// All of the posts were in channels or teams that were deleted.
  1923  			// Update the afterId and try again.
  1924  			afterId = rootIds[len(rootIds)-1]
  1925  			continue
  1926  		}
  1927  
  1928  		return postsForExport, nil
  1929  	}
  1930  }
  1931  
  1932  func (s *SqlPostStore) GetRepliesForExport(rootId string) ([]*model.ReplyForExport, error) {
  1933  	var posts []*model.ReplyForExport
  1934  	_, err := s.GetSearchReplica().Select(&posts, `
  1935  			SELECT
  1936  				Posts.*,
  1937  				Users.Username as Username
  1938  			FROM
  1939  				Posts
  1940  			INNER JOIN
  1941  				Users ON Posts.UserId = Users.Id
  1942  			WHERE
  1943  				Posts.RootId = :RootId
  1944  				AND Posts.DeleteAt = 0
  1945  			ORDER BY
  1946  				Posts.Id`,
  1947  		map[string]interface{}{"RootId": rootId})
  1948  
  1949  	if err != nil {
  1950  		return nil, errors.Wrap(err, "failed to find Posts")
  1951  	}
  1952  
  1953  	return posts, nil
  1954  }
  1955  
  1956  func (s *SqlPostStore) GetDirectPostParentsForExportAfter(limit int, afterId string) ([]*model.DirectPostForExport, error) {
  1957  	query := s.getQueryBuilder().
  1958  		Select("p.*", "Users.Username as User").
  1959  		From("Posts p").
  1960  		Join("Channels ON p.ChannelId = Channels.Id").
  1961  		Join("Users ON p.UserId = Users.Id").
  1962  		Where(sq.And{
  1963  			sq.Gt{"p.Id": afterId},
  1964  			sq.Eq{"p.ParentId": string("")},
  1965  			sq.Eq{"p.DeleteAt": int(0)},
  1966  			sq.Eq{"Channels.DeleteAt": int(0)},
  1967  			sq.Eq{"Users.DeleteAt": int(0)},
  1968  			sq.Eq{"Channels.Type": []string{"D", "G"}},
  1969  		}).
  1970  		OrderBy("p.Id").
  1971  		Limit(uint64(limit))
  1972  
  1973  	queryString, args, err := query.ToSql()
  1974  	if err != nil {
  1975  		return nil, errors.Wrap(err, "post_tosql")
  1976  	}
  1977  
  1978  	var posts []*model.DirectPostForExport
  1979  	if _, err = s.GetReplica().Select(&posts, queryString, args...); err != nil {
  1980  		return nil, errors.Wrap(err, "failed to find Posts")
  1981  	}
  1982  	var channelIds []string
  1983  	for _, post := range posts {
  1984  		channelIds = append(channelIds, post.ChannelId)
  1985  	}
  1986  	query = s.getQueryBuilder().
  1987  		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").
  1988  		From("ChannelMembers cm").
  1989  		Join("Users u ON ( u.Id = cm.UserId )").
  1990  		Where(sq.Eq{
  1991  			"cm.ChannelId": channelIds,
  1992  		})
  1993  
  1994  	queryString, args, err = query.ToSql()
  1995  	if err != nil {
  1996  		return nil, errors.Wrap(err, "post_tosql")
  1997  	}
  1998  
  1999  	var channelMembers []*model.ChannelMemberForExport
  2000  	if _, err := s.GetReplica().Select(&channelMembers, queryString, args...); err != nil {
  2001  		return nil, errors.Wrap(err, "failed to find ChannelMembers")
  2002  	}
  2003  
  2004  	// Build a map of channels and their posts
  2005  	postsChannelMap := make(map[string][]*model.DirectPostForExport)
  2006  	for _, post := range posts {
  2007  		post.ChannelMembers = &[]string{}
  2008  		postsChannelMap[post.ChannelId] = append(postsChannelMap[post.ChannelId], post)
  2009  	}
  2010  
  2011  	// Build a map of channels and their members
  2012  	channelMembersMap := make(map[string][]string)
  2013  	for _, member := range channelMembers {
  2014  		channelMembersMap[member.ChannelId] = append(channelMembersMap[member.ChannelId], member.Username)
  2015  	}
  2016  
  2017  	// Populate each post ChannelMembers extracting it from the channelMembersMap
  2018  	for channelId := range channelMembersMap {
  2019  		for _, post := range postsChannelMap[channelId] {
  2020  			*post.ChannelMembers = channelMembersMap[channelId]
  2021  		}
  2022  	}
  2023  	return posts, nil
  2024  }
  2025  
  2026  //nolint:unparam
  2027  func (s *SqlPostStore) SearchPostsInTeamForUser(paramsList []*model.SearchParams, userId, teamId string, page, perPage int) (*model.PostSearchResults, error) {
  2028  	// Since we don't support paging for DB search, we just return nothing for later pages
  2029  	if page > 0 {
  2030  		return model.MakePostSearchResults(model.NewPostList(), nil), nil
  2031  	}
  2032  
  2033  	if err := model.IsSearchParamsListValid(paramsList); err != nil {
  2034  		return nil, err
  2035  	}
  2036  
  2037  	var wg sync.WaitGroup
  2038  
  2039  	pchan := make(chan store.StoreResult, len(paramsList))
  2040  
  2041  	for _, params := range paramsList {
  2042  		// remove any unquoted term that contains only non-alphanumeric chars
  2043  		// ex: abcd "**" && abc     >>     abcd "**" abc
  2044  		params.Terms = removeNonAlphaNumericUnquotedTerms(params.Terms, " ")
  2045  
  2046  		wg.Add(1)
  2047  
  2048  		go func(params *model.SearchParams) {
  2049  			defer wg.Done()
  2050  			postList, err := s.search(teamId, userId, params, false, false)
  2051  			pchan <- store.StoreResult{Data: postList, NErr: err}
  2052  		}(params)
  2053  	}
  2054  
  2055  	wg.Wait()
  2056  	close(pchan)
  2057  
  2058  	posts := model.NewPostList()
  2059  
  2060  	for result := range pchan {
  2061  		if result.NErr != nil {
  2062  			return nil, result.NErr
  2063  		}
  2064  		data := result.Data.(*model.PostList)
  2065  		posts.Extend(data)
  2066  	}
  2067  
  2068  	posts.SortByCreateAt()
  2069  
  2070  	return model.MakePostSearchResults(posts, nil), nil
  2071  }
  2072  
  2073  func (s *SqlPostStore) GetOldestEntityCreationTime() (int64, error) {
  2074  	query := s.getQueryBuilder().Select("MIN(min_createat) min_createat").
  2075  		Suffix(`FROM (
  2076  					(SELECT MIN(createat) min_createat FROM Posts)
  2077  					UNION
  2078  					(SELECT MIN(createat) min_createat FROM Users)
  2079  					UNION
  2080  					(SELECT MIN(createat) min_createat FROM Channels)
  2081  				) entities`)
  2082  	queryString, _, err := query.ToSql()
  2083  	if err != nil {
  2084  		return -1, errors.Wrap(err, "post_tosql")
  2085  	}
  2086  	row := s.GetReplica().Db.QueryRow(queryString)
  2087  	var oldest int64
  2088  	if err := row.Scan(&oldest); err != nil {
  2089  		return -1, errors.Wrap(err, "unable to scan oldest entity creation time")
  2090  	}
  2091  	return oldest, nil
  2092  }
  2093  
  2094  func (s *SqlPostStore) cleanupThreads(postId, rootId string, permanent bool) error {
  2095  	if permanent {
  2096  		if _, err := s.GetMaster().Exec("DELETE FROM Threads WHERE PostId = :Id", map[string]interface{}{"Id": postId}); err != nil {
  2097  			return errors.Wrap(err, "failed to delete Threads")
  2098  		}
  2099  		if _, err := s.GetMaster().Exec("DELETE FROM ThreadMemberships WHERE PostId = :Id", map[string]interface{}{"Id": postId}); err != nil {
  2100  			return errors.Wrap(err, "failed to delete ThreadMemberships")
  2101  		}
  2102  		return nil
  2103  	}
  2104  	if rootId != "" {
  2105  		thread, err := s.Thread().Get(rootId)
  2106  		if err != nil {
  2107  			var nfErr *store.ErrNotFound
  2108  			if !errors.As(err, &nfErr) {
  2109  				return errors.Wrap(err, "failed to get a thread")
  2110  			}
  2111  		}
  2112  		if thread != nil {
  2113  			thread.ReplyCount -= 1
  2114  			if _, err = s.Thread().Update(thread); err != nil {
  2115  				return errors.Wrap(err, "failed to update thread")
  2116  			}
  2117  		}
  2118  	}
  2119  	return nil
  2120  }
  2121  
  2122  func (s *SqlPostStore) updateThreadsFromPosts(transaction *gorp.Transaction, posts []*model.Post) error {
  2123  	postsByRoot := map[string][]*model.Post{}
  2124  	var rootIds []string
  2125  	for _, post := range posts {
  2126  		// skip if post is not a part of a thread
  2127  		if post.RootId == "" {
  2128  			continue
  2129  		}
  2130  		rootIds = append(rootIds, post.RootId)
  2131  		postsByRoot[post.RootId] = append(postsByRoot[post.RootId], post)
  2132  	}
  2133  	if len(rootIds) == 0 {
  2134  		return nil
  2135  	}
  2136  	now := model.GetMillis()
  2137  	threadsByRootsSql, threadsByRootsArgs, _ := s.getQueryBuilder().Select("*").From("Threads").Where(sq.Eq{"PostId": rootIds}).ToSql()
  2138  	var threadsByRoots []*model.Thread
  2139  	if _, err := transaction.Select(&threadsByRoots, threadsByRootsSql, threadsByRootsArgs...); err != nil {
  2140  		return err
  2141  	}
  2142  
  2143  	threadByRoot := map[string]*model.Thread{}
  2144  	for _, thread := range threadsByRoots {
  2145  		threadByRoot[thread.PostId] = thread
  2146  	}
  2147  
  2148  	for rootId, posts := range postsByRoot {
  2149  		if thread, found := threadByRoot[rootId]; !found {
  2150  			// calculate participants
  2151  			var participants model.StringArray
  2152  			if _, err := transaction.Select(&participants, "SELECT DISTINCT UserId FROM Posts WHERE RootId=:RootId OR Id=:RootId", map[string]interface{}{"RootId": rootId}); err != nil {
  2153  				return err
  2154  			}
  2155  			// calculate reply count
  2156  			count, err := transaction.SelectInt("SELECT COUNT(Id) FROM Posts WHERE RootId=:RootId And DeleteAt=0", map[string]interface{}{"RootId": rootId})
  2157  			if err != nil {
  2158  				return err
  2159  			}
  2160  			// no metadata entry, create one
  2161  			if err := transaction.Insert(&model.Thread{
  2162  				PostId:       rootId,
  2163  				ChannelId:    posts[0].ChannelId,
  2164  				ReplyCount:   count,
  2165  				LastReplyAt:  now,
  2166  				Participants: participants,
  2167  			}); err != nil {
  2168  				return err
  2169  			}
  2170  		} else {
  2171  			// metadata exists, update it
  2172  			thread.LastReplyAt = now
  2173  			for _, post := range posts {
  2174  				thread.ReplyCount += 1
  2175  				if !thread.Participants.Contains(post.UserId) {
  2176  					thread.Participants = append(thread.Participants, post.UserId)
  2177  				}
  2178  			}
  2179  			if _, err := transaction.Update(thread); err != nil {
  2180  				return err
  2181  			}
  2182  		}
  2183  	}
  2184  	return nil
  2185  }