github.com/demisto/mattermost-server@v4.9.0-rc3+incompatible/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  	"bytes"
     8  	"fmt"
     9  	"net/http"
    10  	"regexp"
    11  	"strconv"
    12  	"strings"
    13  	"sync"
    14  
    15  	l4g "github.com/alecthomas/log4go"
    16  	"github.com/mattermost/mattermost-server/einterfaces"
    17  	"github.com/mattermost/mattermost-server/model"
    18  	"github.com/mattermost/mattermost-server/store"
    19  	"github.com/mattermost/mattermost-server/utils"
    20  )
    21  
    22  type SqlPostStore struct {
    23  	SqlStore
    24  	metrics           einterfaces.MetricsInterface
    25  	lastPostTimeCache *utils.Cache
    26  	lastPostsCache    *utils.Cache
    27  	maxPostSizeOnce   sync.Once
    28  	maxPostSizeCached int
    29  }
    30  
    31  const (
    32  	LAST_POST_TIME_CACHE_SIZE = 25000
    33  	LAST_POST_TIME_CACHE_SEC  = 900 // 15 minutes
    34  
    35  	LAST_POSTS_CACHE_SIZE = 1000
    36  	LAST_POSTS_CACHE_SEC  = 900 // 15 minutes
    37  )
    38  
    39  func (s *SqlPostStore) ClearCaches() {
    40  	s.lastPostTimeCache.Purge()
    41  	s.lastPostsCache.Purge()
    42  
    43  	if s.metrics != nil {
    44  		s.metrics.IncrementMemCacheInvalidationCounter("Last Post Time - Purge")
    45  		s.metrics.IncrementMemCacheInvalidationCounter("Last Posts Cache - Purge")
    46  	}
    47  }
    48  
    49  func NewSqlPostStore(sqlStore SqlStore, metrics einterfaces.MetricsInterface) store.PostStore {
    50  	s := &SqlPostStore{
    51  		SqlStore:          sqlStore,
    52  		metrics:           metrics,
    53  		lastPostTimeCache: utils.NewLru(LAST_POST_TIME_CACHE_SIZE),
    54  		lastPostsCache:    utils.NewLru(LAST_POSTS_CACHE_SIZE),
    55  		maxPostSizeCached: model.POST_MESSAGE_MAX_RUNES_V1,
    56  	}
    57  
    58  	for _, db := range sqlStore.GetAllConns() {
    59  		table := db.AddTableWithName(model.Post{}, "Posts").SetKeys(false, "Id")
    60  		table.ColMap("Id").SetMaxSize(26)
    61  		table.ColMap("UserId").SetMaxSize(26)
    62  		table.ColMap("ChannelId").SetMaxSize(26)
    63  		table.ColMap("RootId").SetMaxSize(26)
    64  		table.ColMap("ParentId").SetMaxSize(26)
    65  		table.ColMap("OriginalId").SetMaxSize(26)
    66  		table.ColMap("Message").SetMaxSize(model.POST_MESSAGE_MAX_BYTES_V2)
    67  		table.ColMap("Type").SetMaxSize(26)
    68  		table.ColMap("Hashtags").SetMaxSize(1000)
    69  		table.ColMap("Props").SetMaxSize(8000)
    70  		table.ColMap("Filenames").SetMaxSize(model.POST_FILENAMES_MAX_RUNES)
    71  		table.ColMap("FileIds").SetMaxSize(150)
    72  	}
    73  
    74  	return s
    75  }
    76  
    77  func (s *SqlPostStore) CreateIndexesIfNotExists() {
    78  	s.CreateIndexIfNotExists("idx_posts_update_at", "Posts", "UpdateAt")
    79  	s.CreateIndexIfNotExists("idx_posts_create_at", "Posts", "CreateAt")
    80  	s.CreateIndexIfNotExists("idx_posts_delete_at", "Posts", "DeleteAt")
    81  	s.CreateIndexIfNotExists("idx_posts_channel_id", "Posts", "ChannelId")
    82  	s.CreateIndexIfNotExists("idx_posts_root_id", "Posts", "RootId")
    83  	s.CreateIndexIfNotExists("idx_posts_user_id", "Posts", "UserId")
    84  	s.CreateIndexIfNotExists("idx_posts_is_pinned", "Posts", "IsPinned")
    85  
    86  	s.CreateCompositeIndexIfNotExists("idx_posts_channel_id_update_at", "Posts", []string{"ChannelId", "UpdateAt"})
    87  	s.CreateCompositeIndexIfNotExists("idx_posts_channel_id_delete_at_create_at", "Posts", []string{"ChannelId", "DeleteAt", "CreateAt"})
    88  
    89  	s.CreateFullTextIndexIfNotExists("idx_posts_message_txt", "Posts", "Message")
    90  	s.CreateFullTextIndexIfNotExists("idx_posts_hashtags_txt", "Posts", "Hashtags")
    91  }
    92  
    93  func (s *SqlPostStore) Save(post *model.Post) store.StoreChannel {
    94  	return store.Do(func(result *store.StoreResult) {
    95  		if len(post.Id) > 0 {
    96  			result.Err = model.NewAppError("SqlPostStore.Save", "store.sql_post.save.existing.app_error", nil, "id="+post.Id, http.StatusBadRequest)
    97  			return
    98  		}
    99  
   100  		var maxPostSize int
   101  		if result := <-s.GetMaxPostSize(); result.Err != nil {
   102  			result.Err = model.NewAppError("SqlPostStore.Save", "store.sql_post.save.app_error", nil, "id="+post.Id+", "+result.Err.Error(), http.StatusInternalServerError)
   103  			return
   104  		} else {
   105  			maxPostSize = result.Data.(int)
   106  		}
   107  
   108  		post.PreSave()
   109  		if result.Err = post.IsValid(maxPostSize); result.Err != nil {
   110  			return
   111  		}
   112  
   113  		if err := s.GetMaster().Insert(post); err != nil {
   114  			result.Err = model.NewAppError("SqlPostStore.Save", "store.sql_post.save.app_error", nil, "id="+post.Id+", "+err.Error(), http.StatusInternalServerError)
   115  		} else {
   116  			time := post.UpdateAt
   117  
   118  			if post.Type != model.POST_JOIN_LEAVE && post.Type != model.POST_ADD_REMOVE &&
   119  				post.Type != model.POST_JOIN_CHANNEL && post.Type != model.POST_LEAVE_CHANNEL &&
   120  				post.Type != model.POST_JOIN_TEAM && post.Type != model.POST_LEAVE_TEAM &&
   121  				post.Type != model.POST_ADD_TO_CHANNEL && post.Type != model.POST_REMOVE_FROM_CHANNEL {
   122  				s.GetMaster().Exec("UPDATE Channels SET LastPostAt = :LastPostAt, TotalMsgCount = TotalMsgCount + 1 WHERE Id = :ChannelId", map[string]interface{}{"LastPostAt": time, "ChannelId": post.ChannelId})
   123  			} else {
   124  				// don't update TotalMsgCount for unimportant messages so that the channel isn't marked as unread
   125  				s.GetMaster().Exec("UPDATE Channels SET LastPostAt = :LastPostAt WHERE Id = :ChannelId", map[string]interface{}{"LastPostAt": time, "ChannelId": post.ChannelId})
   126  			}
   127  
   128  			if len(post.RootId) > 0 {
   129  				s.GetMaster().Exec("UPDATE Posts SET UpdateAt = :UpdateAt WHERE Id = :RootId", map[string]interface{}{"UpdateAt": time, "RootId": post.RootId})
   130  			}
   131  
   132  			result.Data = post
   133  		}
   134  	})
   135  }
   136  
   137  func (s *SqlPostStore) Update(newPost *model.Post, oldPost *model.Post) store.StoreChannel {
   138  	return store.Do(func(result *store.StoreResult) {
   139  		newPost.UpdateAt = model.GetMillis()
   140  		newPost.PreCommit()
   141  
   142  		oldPost.DeleteAt = newPost.UpdateAt
   143  		oldPost.UpdateAt = newPost.UpdateAt
   144  		oldPost.OriginalId = oldPost.Id
   145  		oldPost.Id = model.NewId()
   146  		oldPost.PreCommit()
   147  
   148  		var maxPostSize int
   149  		if result := <-s.GetMaxPostSize(); result.Err != nil {
   150  			result.Err = model.NewAppError("SqlPostStore.Save", "store.sql_post.update.app_error", nil, "id="+newPost.Id+", "+result.Err.Error(), http.StatusInternalServerError)
   151  			return
   152  		} else {
   153  			maxPostSize = result.Data.(int)
   154  		}
   155  
   156  		if result.Err = newPost.IsValid(maxPostSize); result.Err != nil {
   157  			return
   158  		}
   159  
   160  		if _, err := s.GetMaster().Update(newPost); err != nil {
   161  			result.Err = model.NewAppError("SqlPostStore.Update", "store.sql_post.update.app_error", nil, "id="+newPost.Id+", "+err.Error(), http.StatusInternalServerError)
   162  		} else {
   163  			time := model.GetMillis()
   164  			s.GetMaster().Exec("UPDATE Channels SET LastPostAt = :LastPostAt  WHERE Id = :ChannelId", map[string]interface{}{"LastPostAt": time, "ChannelId": newPost.ChannelId})
   165  
   166  			if len(newPost.RootId) > 0 {
   167  				s.GetMaster().Exec("UPDATE Posts SET UpdateAt = :UpdateAt WHERE Id = :RootId", map[string]interface{}{"UpdateAt": time, "RootId": newPost.RootId})
   168  			}
   169  
   170  			// mark the old post as deleted
   171  			s.GetMaster().Insert(oldPost)
   172  
   173  			result.Data = newPost
   174  		}
   175  	})
   176  }
   177  
   178  func (s *SqlPostStore) Overwrite(post *model.Post) store.StoreChannel {
   179  	return store.Do(func(result *store.StoreResult) {
   180  		post.UpdateAt = model.GetMillis()
   181  
   182  		var maxPostSize int
   183  		if result := <-s.GetMaxPostSize(); result.Err != nil {
   184  			result.Err = model.NewAppError("SqlPostStore.Save", "store.sql_post.overwrite.app_error", nil, "id="+post.Id+", "+result.Err.Error(), http.StatusInternalServerError)
   185  			return
   186  		} else {
   187  			maxPostSize = result.Data.(int)
   188  		}
   189  
   190  		if result.Err = post.IsValid(maxPostSize); result.Err != nil {
   191  			return
   192  		}
   193  
   194  		if _, err := s.GetMaster().Update(post); err != nil {
   195  			result.Err = model.NewAppError("SqlPostStore.Overwrite", "store.sql_post.overwrite.app_error", nil, "id="+post.Id+", "+err.Error(), http.StatusInternalServerError)
   196  		} else {
   197  			result.Data = post
   198  		}
   199  	})
   200  }
   201  
   202  func (s *SqlPostStore) GetFlaggedPosts(userId string, offset int, limit int) store.StoreChannel {
   203  	return store.Do(func(result *store.StoreResult) {
   204  		pl := model.NewPostList()
   205  
   206  		var posts []*model.Post
   207  		if _, err := s.GetReplica().Select(&posts, "SELECT * FROM Posts 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 {
   208  			result.Err = model.NewAppError("SqlPostStore.GetFlaggedPosts", "store.sql_post.get_flagged_posts.app_error", nil, err.Error(), http.StatusInternalServerError)
   209  		} else {
   210  			for _, post := range posts {
   211  				pl.AddPost(post)
   212  				pl.AddOrder(post.Id)
   213  			}
   214  		}
   215  
   216  		result.Data = pl
   217  	})
   218  }
   219  
   220  func (s *SqlPostStore) GetFlaggedPostsForTeam(userId, teamId string, offset int, limit int) store.StoreChannel {
   221  	return store.Do(func(result *store.StoreResult) {
   222  		pl := model.NewPostList()
   223  
   224  		var posts []*model.Post
   225  
   226  		query := `
   227              SELECT
   228                  A.*
   229              FROM
   230                  (SELECT
   231                      *
   232                  FROM
   233                      Posts
   234                  WHERE
   235                      Id
   236                  IN
   237                      (SELECT
   238                          Name
   239                      FROM
   240                          Preferences
   241                      WHERE
   242                          UserId = :UserId
   243                          AND Category = :Category)
   244                          AND DeleteAt = 0
   245                  ) as A
   246              INNER JOIN Channels as B
   247                  ON B.Id = A.ChannelId
   248              WHERE B.TeamId = :TeamId OR B.TeamId = ''
   249              ORDER BY CreateAt DESC
   250              LIMIT :Limit OFFSET :Offset`
   251  
   252  		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 {
   253  			result.Err = model.NewAppError("SqlPostStore.GetFlaggedPostsForTeam", "store.sql_post.get_flagged_posts.app_error", nil, err.Error(), http.StatusInternalServerError)
   254  		} else {
   255  			for _, post := range posts {
   256  				pl.AddPost(post)
   257  				pl.AddOrder(post.Id)
   258  			}
   259  		}
   260  
   261  		result.Data = pl
   262  	})
   263  }
   264  
   265  func (s *SqlPostStore) GetFlaggedPostsForChannel(userId, channelId string, offset int, limit int) store.StoreChannel {
   266  	return store.Do(func(result *store.StoreResult) {
   267  		pl := model.NewPostList()
   268  
   269  		var posts []*model.Post
   270  		query := `
   271  			SELECT 
   272  				* 
   273  			FROM Posts 
   274  			WHERE 
   275  				Id IN (SELECT Name FROM Preferences WHERE UserId = :UserId AND Category = :Category) 
   276  				AND ChannelId = :ChannelId
   277  				AND DeleteAt = 0 
   278  			ORDER BY CreateAt DESC 
   279  			LIMIT :Limit OFFSET :Offset`
   280  
   281  		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 {
   282  			result.Err = model.NewAppError("SqlPostStore.GetFlaggedPostsForChannel", "store.sql_post.get_flagged_posts.app_error", nil, err.Error(), http.StatusInternalServerError)
   283  		} else {
   284  			for _, post := range posts {
   285  				pl.AddPost(post)
   286  				pl.AddOrder(post.Id)
   287  			}
   288  		}
   289  
   290  		result.Data = pl
   291  	})
   292  }
   293  
   294  func (s *SqlPostStore) Get(id string) store.StoreChannel {
   295  	return store.Do(func(result *store.StoreResult) {
   296  		pl := model.NewPostList()
   297  
   298  		if len(id) == 0 {
   299  			result.Err = model.NewAppError("SqlPostStore.GetPost", "store.sql_post.get.app_error", nil, "id="+id, http.StatusBadRequest)
   300  			return
   301  		}
   302  
   303  		var post model.Post
   304  		err := s.GetReplica().SelectOne(&post, "SELECT * FROM Posts WHERE Id = :Id AND DeleteAt = 0", map[string]interface{}{"Id": id})
   305  		if err != nil {
   306  			result.Err = model.NewAppError("SqlPostStore.GetPost", "store.sql_post.get.app_error", nil, "id="+id+err.Error(), http.StatusNotFound)
   307  			return
   308  		}
   309  
   310  		pl.AddPost(&post)
   311  		pl.AddOrder(id)
   312  
   313  		rootId := post.RootId
   314  
   315  		if rootId == "" {
   316  			rootId = post.Id
   317  		}
   318  
   319  		if len(rootId) == 0 {
   320  			result.Err = model.NewAppError("SqlPostStore.GetPost", "store.sql_post.get.app_error", nil, "root_id="+rootId, http.StatusInternalServerError)
   321  			return
   322  		}
   323  
   324  		var posts []*model.Post
   325  		_, err = s.GetReplica().Select(&posts, "SELECT * FROM Posts WHERE (Id = :Id OR RootId = :RootId) AND DeleteAt = 0", map[string]interface{}{"Id": rootId, "RootId": rootId})
   326  		if err != nil {
   327  			result.Err = model.NewAppError("SqlPostStore.GetPost", "store.sql_post.get.app_error", nil, "root_id="+rootId+err.Error(), http.StatusInternalServerError)
   328  			return
   329  		} else {
   330  			for _, p := range posts {
   331  				pl.AddPost(p)
   332  			}
   333  		}
   334  
   335  		result.Data = pl
   336  	})
   337  }
   338  
   339  func (s *SqlPostStore) GetSingle(id string) store.StoreChannel {
   340  	return store.Do(func(result *store.StoreResult) {
   341  		var post model.Post
   342  		err := s.GetReplica().SelectOne(&post, "SELECT * FROM Posts WHERE Id = :Id AND DeleteAt = 0", map[string]interface{}{"Id": id})
   343  		if err != nil {
   344  			result.Err = model.NewAppError("SqlPostStore.GetSingle", "store.sql_post.get.app_error", nil, "id="+id+err.Error(), http.StatusNotFound)
   345  		}
   346  
   347  		result.Data = &post
   348  	})
   349  }
   350  
   351  type etagPosts struct {
   352  	Id       string
   353  	UpdateAt int64
   354  }
   355  
   356  func (s *SqlPostStore) InvalidateLastPostTimeCache(channelId string) {
   357  	s.lastPostTimeCache.Remove(channelId)
   358  
   359  	// Keys are "{channelid}{limit}" and caching only occurs on limits of 30 and 60
   360  	s.lastPostsCache.Remove(channelId + "30")
   361  	s.lastPostsCache.Remove(channelId + "60")
   362  
   363  	if s.metrics != nil {
   364  		s.metrics.IncrementMemCacheInvalidationCounter("Last Post Time - Remove by Channel Id")
   365  		s.metrics.IncrementMemCacheInvalidationCounter("Last Posts Cache - Remove by Channel Id")
   366  	}
   367  }
   368  
   369  func (s *SqlPostStore) GetEtag(channelId string, allowFromCache bool) store.StoreChannel {
   370  	return store.Do(func(result *store.StoreResult) {
   371  		if allowFromCache {
   372  			if cacheItem, ok := s.lastPostTimeCache.Get(channelId); ok {
   373  				if s.metrics != nil {
   374  					s.metrics.IncrementMemCacheHitCounter("Last Post Time")
   375  				}
   376  				result.Data = fmt.Sprintf("%v.%v", model.CurrentVersion, cacheItem.(int64))
   377  				return
   378  			} else {
   379  				if s.metrics != nil {
   380  					s.metrics.IncrementMemCacheMissCounter("Last Post Time")
   381  				}
   382  			}
   383  		} else {
   384  			if s.metrics != nil {
   385  				s.metrics.IncrementMemCacheMissCounter("Last Post Time")
   386  			}
   387  		}
   388  
   389  		var et etagPosts
   390  		err := s.GetReplica().SelectOne(&et, "SELECT Id, UpdateAt FROM Posts WHERE ChannelId = :ChannelId ORDER BY UpdateAt DESC LIMIT 1", map[string]interface{}{"ChannelId": channelId})
   391  		if err != nil {
   392  			result.Data = fmt.Sprintf("%v.%v", model.CurrentVersion, model.GetMillis())
   393  		} else {
   394  			result.Data = fmt.Sprintf("%v.%v", model.CurrentVersion, et.UpdateAt)
   395  		}
   396  
   397  		s.lastPostTimeCache.AddWithExpiresInSecs(channelId, et.UpdateAt, LAST_POST_TIME_CACHE_SEC)
   398  	})
   399  }
   400  
   401  func (s *SqlPostStore) Delete(postId string, time int64) store.StoreChannel {
   402  	return store.Do(func(result *store.StoreResult) {
   403  		_, err := s.GetMaster().Exec("Update Posts SET DeleteAt = :DeleteAt, UpdateAt = :UpdateAt WHERE Id = :Id OR RootId = :RootId", map[string]interface{}{"DeleteAt": time, "UpdateAt": time, "Id": postId, "RootId": postId})
   404  		if err != nil {
   405  			result.Err = model.NewAppError("SqlPostStore.Delete", "store.sql_post.delete.app_error", nil, "id="+postId+", err="+err.Error(), http.StatusInternalServerError)
   406  		}
   407  	})
   408  }
   409  
   410  func (s *SqlPostStore) permanentDelete(postId string) store.StoreChannel {
   411  	return store.Do(func(result *store.StoreResult) {
   412  		_, err := s.GetMaster().Exec("DELETE FROM Posts WHERE Id = :Id OR RootId = :RootId", map[string]interface{}{"Id": postId, "RootId": postId})
   413  		if err != nil {
   414  			result.Err = model.NewAppError("SqlPostStore.Delete", "store.sql_post.permanent_delete.app_error", nil, "id="+postId+", err="+err.Error(), http.StatusInternalServerError)
   415  		}
   416  	})
   417  }
   418  
   419  func (s *SqlPostStore) permanentDeleteAllCommentByUser(userId string) store.StoreChannel {
   420  	return store.Do(func(result *store.StoreResult) {
   421  		_, err := s.GetMaster().Exec("DELETE FROM Posts WHERE UserId = :UserId AND RootId != ''", map[string]interface{}{"UserId": userId})
   422  		if err != nil {
   423  			result.Err = model.NewAppError("SqlPostStore.permanentDeleteAllCommentByUser", "store.sql_post.permanent_delete_all_comments_by_user.app_error", nil, "userId="+userId+", err="+err.Error(), http.StatusInternalServerError)
   424  		}
   425  	})
   426  }
   427  
   428  func (s *SqlPostStore) PermanentDeleteByUser(userId string) store.StoreChannel {
   429  	return store.Do(func(result *store.StoreResult) {
   430  		// First attempt to delete all the comments for a user
   431  		if r := <-s.permanentDeleteAllCommentByUser(userId); r.Err != nil {
   432  			result.Err = r.Err
   433  			return
   434  		}
   435  
   436  		// Now attempt to delete all the root posts for a user.  This will also
   437  		// delete all the comments for each post.
   438  		found := true
   439  		count := 0
   440  
   441  		for found {
   442  			var ids []string
   443  			_, err := s.GetMaster().Select(&ids, "SELECT Id FROM Posts WHERE UserId = :UserId LIMIT 1000", map[string]interface{}{"UserId": userId})
   444  			if err != nil {
   445  				result.Err = model.NewAppError("SqlPostStore.PermanentDeleteByUser.select", "store.sql_post.permanent_delete_by_user.app_error", nil, "userId="+userId+", err="+err.Error(), http.StatusInternalServerError)
   446  				return
   447  			} else {
   448  				found = false
   449  				for _, id := range ids {
   450  					found = true
   451  					if r := <-s.permanentDelete(id); r.Err != nil {
   452  						result.Err = r.Err
   453  						return
   454  					}
   455  				}
   456  			}
   457  
   458  			// This is a fail safe, give up if more than 10K messages
   459  			count = count + 1
   460  			if count >= 10 {
   461  				result.Err = model.NewAppError("SqlPostStore.PermanentDeleteByUser.toolarge", "store.sql_post.permanent_delete_by_user.too_many.app_error", nil, "userId="+userId, http.StatusInternalServerError)
   462  				return
   463  			}
   464  		}
   465  	})
   466  }
   467  
   468  func (s *SqlPostStore) PermanentDeleteByChannel(channelId string) store.StoreChannel {
   469  	return store.Do(func(result *store.StoreResult) {
   470  		if _, err := s.GetMaster().Exec("DELETE FROM Posts WHERE ChannelId = :ChannelId", map[string]interface{}{"ChannelId": channelId}); err != nil {
   471  			result.Err = model.NewAppError("SqlPostStore.PermanentDeleteByChannel", "store.sql_post.permanent_delete_by_channel.app_error", nil, "channel_id="+channelId+", "+err.Error(), http.StatusInternalServerError)
   472  		}
   473  	})
   474  }
   475  
   476  func (s *SqlPostStore) GetPosts(channelId string, offset int, limit int, allowFromCache bool) store.StoreChannel {
   477  	return store.Do(func(result *store.StoreResult) {
   478  		if limit > 1000 {
   479  			result.Err = model.NewAppError("SqlPostStore.GetLinearPosts", "store.sql_post.get_posts.app_error", nil, "channelId="+channelId, http.StatusBadRequest)
   480  			return
   481  		}
   482  
   483  		// Caching only occurs on limits of 30 and 60, the common limits requested by MM clients
   484  		if allowFromCache && offset == 0 && (limit == 60 || limit == 30) {
   485  			if cacheItem, ok := s.lastPostsCache.Get(fmt.Sprintf("%s%v", channelId, limit)); ok {
   486  				if s.metrics != nil {
   487  					s.metrics.IncrementMemCacheHitCounter("Last Posts Cache")
   488  				}
   489  
   490  				result.Data = cacheItem.(*model.PostList)
   491  				return
   492  			} else {
   493  				if s.metrics != nil {
   494  					s.metrics.IncrementMemCacheMissCounter("Last Posts Cache")
   495  				}
   496  			}
   497  		} else {
   498  			if s.metrics != nil {
   499  				s.metrics.IncrementMemCacheMissCounter("Last Posts Cache")
   500  			}
   501  		}
   502  
   503  		rpc := s.getRootPosts(channelId, offset, limit)
   504  		cpc := s.getParentsPosts(channelId, offset, limit)
   505  
   506  		if rpr := <-rpc; rpr.Err != nil {
   507  			result.Err = rpr.Err
   508  		} else if cpr := <-cpc; cpr.Err != nil {
   509  			result.Err = cpr.Err
   510  		} else {
   511  			posts := rpr.Data.([]*model.Post)
   512  			parents := cpr.Data.([]*model.Post)
   513  
   514  			list := model.NewPostList()
   515  
   516  			for _, p := range posts {
   517  				list.AddPost(p)
   518  				list.AddOrder(p.Id)
   519  			}
   520  
   521  			for _, p := range parents {
   522  				list.AddPost(p)
   523  			}
   524  
   525  			list.MakeNonNil()
   526  
   527  			// Caching only occurs on limits of 30 and 60, the common limits requested by MM clients
   528  			if offset == 0 && (limit == 60 || limit == 30) {
   529  				s.lastPostsCache.AddWithExpiresInSecs(fmt.Sprintf("%s%v", channelId, limit), list, LAST_POSTS_CACHE_SEC)
   530  			}
   531  
   532  			result.Data = list
   533  		}
   534  	})
   535  }
   536  
   537  func (s *SqlPostStore) GetPostsSince(channelId string, time int64, allowFromCache bool) store.StoreChannel {
   538  	return store.Do(func(result *store.StoreResult) {
   539  		if allowFromCache {
   540  			// If the last post in the channel's time is less than or equal to the time we are getting posts since,
   541  			// we can safely return no posts.
   542  			if cacheItem, ok := s.lastPostTimeCache.Get(channelId); ok && cacheItem.(int64) <= time {
   543  				if s.metrics != nil {
   544  					s.metrics.IncrementMemCacheHitCounter("Last Post Time")
   545  				}
   546  				list := model.NewPostList()
   547  				result.Data = list
   548  				return
   549  			} else {
   550  				if s.metrics != nil {
   551  					s.metrics.IncrementMemCacheMissCounter("Last Post Time")
   552  				}
   553  			}
   554  		} else {
   555  			if s.metrics != nil {
   556  				s.metrics.IncrementMemCacheMissCounter("Last Post Time")
   557  			}
   558  		}
   559  
   560  		var posts []*model.Post
   561  		_, err := s.GetReplica().Select(&posts,
   562  			`(SELECT
   563  			    *
   564  			FROM
   565  			    Posts
   566  			WHERE
   567  			    (UpdateAt > :Time
   568  			        AND ChannelId = :ChannelId)
   569  			LIMIT 1000)
   570  			UNION
   571  			(SELECT
   572  			    *
   573  			FROM
   574  			    Posts
   575  			WHERE
   576  			    Id
   577  			IN
   578  			    (SELECT * FROM (SELECT
   579  			        RootId
   580  			    FROM
   581  			        Posts
   582  			    WHERE
   583  			        UpdateAt > :Time
   584  			            AND ChannelId = :ChannelId
   585  			    LIMIT 1000) temp_tab))
   586  			ORDER BY CreateAt DESC`,
   587  			map[string]interface{}{"ChannelId": channelId, "Time": time})
   588  
   589  		if err != nil {
   590  			result.Err = model.NewAppError("SqlPostStore.GetPostsSince", "store.sql_post.get_posts_since.app_error", nil, "channelId="+channelId+err.Error(), http.StatusInternalServerError)
   591  		} else {
   592  
   593  			list := model.NewPostList()
   594  
   595  			var latestUpdate int64 = 0
   596  
   597  			for _, p := range posts {
   598  				list.AddPost(p)
   599  				if p.UpdateAt > time {
   600  					list.AddOrder(p.Id)
   601  				}
   602  				if latestUpdate < p.UpdateAt {
   603  					latestUpdate = p.UpdateAt
   604  				}
   605  			}
   606  
   607  			s.lastPostTimeCache.AddWithExpiresInSecs(channelId, latestUpdate, LAST_POST_TIME_CACHE_SEC)
   608  
   609  			result.Data = list
   610  		}
   611  	})
   612  }
   613  
   614  func (s *SqlPostStore) GetPostsBefore(channelId string, postId string, numPosts int, offset int) store.StoreChannel {
   615  	return s.getPostsAround(channelId, postId, numPosts, offset, true)
   616  }
   617  
   618  func (s *SqlPostStore) GetPostsAfter(channelId string, postId string, numPosts int, offset int) store.StoreChannel {
   619  	return s.getPostsAround(channelId, postId, numPosts, offset, false)
   620  }
   621  
   622  func (s *SqlPostStore) getPostsAround(channelId string, postId string, numPosts int, offset int, before bool) store.StoreChannel {
   623  	return store.Do(func(result *store.StoreResult) {
   624  		var direction string
   625  		var sort string
   626  		if before {
   627  			direction = "<"
   628  			sort = "DESC"
   629  		} else {
   630  			direction = ">"
   631  			sort = "ASC"
   632  		}
   633  
   634  		var posts []*model.Post
   635  		var parents []*model.Post
   636  		_, err1 := s.GetReplica().Select(&posts,
   637  			`(SELECT
   638  			    *
   639  			FROM
   640  			    Posts
   641  			WHERE
   642  				(CreateAt `+direction+` (SELECT CreateAt FROM Posts WHERE Id = :PostId)
   643  			        AND ChannelId = :ChannelId
   644  					AND DeleteAt = 0)
   645  			ORDER BY CreateAt `+sort+`
   646  			LIMIT :NumPosts
   647  			OFFSET :Offset)`,
   648  			map[string]interface{}{"ChannelId": channelId, "PostId": postId, "NumPosts": numPosts, "Offset": offset})
   649  		_, err2 := s.GetReplica().Select(&parents,
   650  			`(SELECT
   651  			    *
   652  			FROM
   653  			    Posts
   654  			WHERE
   655  			    Id
   656  			IN
   657  			    (SELECT * FROM (SELECT
   658  			        RootId
   659  			    FROM
   660  			        Posts
   661  			    WHERE
   662  					(CreateAt `+direction+` (SELECT CreateAt FROM Posts WHERE Id = :PostId)
   663  						AND ChannelId = :ChannelId
   664  						AND DeleteAt = 0)
   665  					ORDER BY CreateAt `+sort+`
   666  					LIMIT :NumPosts
   667  					OFFSET :Offset)
   668  			    temp_tab))
   669  			ORDER BY CreateAt DESC`,
   670  			map[string]interface{}{"ChannelId": channelId, "PostId": postId, "NumPosts": numPosts, "Offset": offset})
   671  
   672  		if err1 != nil {
   673  			result.Err = model.NewAppError("SqlPostStore.GetPostContext", "store.sql_post.get_posts_around.get.app_error", nil, "channelId="+channelId+err1.Error(), http.StatusInternalServerError)
   674  		} else if err2 != nil {
   675  			result.Err = model.NewAppError("SqlPostStore.GetPostContext", "store.sql_post.get_posts_around.get_parent.app_error", nil, "channelId="+channelId+err2.Error(), http.StatusInternalServerError)
   676  		} else {
   677  
   678  			list := model.NewPostList()
   679  
   680  			// We need to flip the order if we selected backwards
   681  			if before {
   682  				for _, p := range posts {
   683  					list.AddPost(p)
   684  					list.AddOrder(p.Id)
   685  				}
   686  			} else {
   687  				l := len(posts)
   688  				for i := range posts {
   689  					list.AddPost(posts[l-i-1])
   690  					list.AddOrder(posts[l-i-1].Id)
   691  				}
   692  			}
   693  
   694  			for _, p := range parents {
   695  				list.AddPost(p)
   696  			}
   697  
   698  			result.Data = list
   699  		}
   700  	})
   701  }
   702  
   703  func (s *SqlPostStore) getRootPosts(channelId string, offset int, limit int) store.StoreChannel {
   704  	return store.Do(func(result *store.StoreResult) {
   705  		var posts []*model.Post
   706  		_, err := s.GetReplica().Select(&posts, "SELECT * FROM Posts WHERE ChannelId = :ChannelId AND DeleteAt = 0 ORDER BY CreateAt DESC LIMIT :Limit OFFSET :Offset", map[string]interface{}{"ChannelId": channelId, "Offset": offset, "Limit": limit})
   707  		if err != nil {
   708  			result.Err = model.NewAppError("SqlPostStore.GetLinearPosts", "store.sql_post.get_root_posts.app_error", nil, "channelId="+channelId+err.Error(), http.StatusInternalServerError)
   709  		} else {
   710  			result.Data = posts
   711  		}
   712  	})
   713  }
   714  
   715  func (s *SqlPostStore) getParentsPosts(channelId string, offset int, limit int) store.StoreChannel {
   716  	return store.Do(func(result *store.StoreResult) {
   717  		var posts []*model.Post
   718  		_, err := s.GetReplica().Select(&posts, `
   719  			SELECT
   720  			    *
   721  			FROM
   722  			    Posts
   723  			WHERE
   724  			    Id IN (SELECT * FROM (
   725  				-- The root post of any replies in the window
   726  				(SELECT * FROM (
   727  				    SELECT
   728  					CASE RootId
   729  					    WHEN '' THEN NULL
   730  					    ELSE RootId
   731  					END
   732  				    FROM
   733  					Posts
   734  				    WHERE
   735  					ChannelId = :ChannelId1
   736  				    AND DeleteAt = 0
   737  				    ORDER BY 
   738  					CreateAt DESC
   739  				    LIMIT :Limit1 OFFSET :Offset1
   740  				) x )
   741  
   742  				UNION
   743  
   744  				-- The reply posts to all threads intersecting with the window, including replies
   745  				-- to root posts in the window itself.
   746  				(
   747  				    SELECT
   748  					Id
   749  				    FROM
   750  					Posts
   751  				    WHERE RootId IN (SELECT * FROM (
   752  					SELECT
   753  					    CASE RootId
   754  						-- If there is no RootId, return the post id itself to be considered
   755  						-- as a root post.
   756  						WHEN '' THEN Id
   757  						-- If there is a RootId, this post isn't a root post and return its
   758  						-- root to be considered as a root post.
   759  						ELSE RootId
   760  					    END
   761  					FROM
   762  					    Posts
   763  					WHERE
   764  					    ChannelId = :ChannelId2
   765  					AND DeleteAt = 0
   766  					ORDER BY 
   767  					    CreateAt DESC
   768  					LIMIT :Limit2 OFFSET :Offset2
   769  				    ) x )
   770  				)
   771  			    ) x )
   772  			AND 
   773  			    DeleteAt = 0
   774  		`, map[string]interface{}{
   775  			"ChannelId1": channelId,
   776  			"ChannelId2": channelId,
   777  			"Offset1":    offset,
   778  			"Offset2":    offset,
   779  			"Limit1":     limit,
   780  			"Limit2":     limit,
   781  		})
   782  		if err != nil {
   783  			result.Err = model.NewAppError("SqlPostStore.GetLinearPosts", "store.sql_post.get_parents_posts.app_error", nil, "channelId="+channelId+" err="+err.Error(), http.StatusInternalServerError)
   784  		} else {
   785  			result.Data = posts
   786  		}
   787  	})
   788  }
   789  
   790  var specialSearchChar = []string{
   791  	"<",
   792  	">",
   793  	"+",
   794  	"-",
   795  	"(",
   796  	")",
   797  	"~",
   798  	"@",
   799  	":",
   800  }
   801  
   802  func (s *SqlPostStore) Search(teamId string, userId string, params *model.SearchParams) store.StoreChannel {
   803  	return store.Do(func(result *store.StoreResult) {
   804  		queryParams := map[string]interface{}{
   805  			"TeamId": teamId,
   806  			"UserId": userId,
   807  		}
   808  
   809  		termMap := map[string]bool{}
   810  		terms := params.Terms
   811  
   812  		if terms == "" && len(params.InChannels) == 0 && len(params.FromUsers) == 0 {
   813  			result.Data = []*model.Post{}
   814  			return
   815  		}
   816  
   817  		searchType := "Message"
   818  		if params.IsHashtag {
   819  			searchType = "Hashtags"
   820  			for _, term := range strings.Split(terms, " ") {
   821  				termMap[strings.ToUpper(term)] = true
   822  			}
   823  		}
   824  
   825  		// these chars have special meaning and can be treated as spaces
   826  		for _, c := range specialSearchChar {
   827  			terms = strings.Replace(terms, c, " ", -1)
   828  		}
   829  
   830  		var posts []*model.Post
   831  
   832  		searchQuery := `
   833  			SELECT
   834  				*
   835  			FROM
   836  				Posts
   837  			WHERE
   838  				DeleteAt = 0
   839  				AND Type NOT LIKE '` + model.POST_SYSTEM_MESSAGE_PREFIX + `%'
   840  				POST_FILTER
   841  				AND ChannelId IN (
   842  					SELECT
   843  						Id
   844  					FROM
   845  						Channels,
   846  						ChannelMembers
   847  					WHERE
   848  						Id = ChannelId
   849  							AND (TeamId = :TeamId OR TeamId = '')
   850  							AND UserId = :UserId
   851  							AND DeleteAt = 0
   852  							CHANNEL_FILTER)
   853  				SEARCH_CLAUSE
   854  				ORDER BY CreateAt DESC
   855  			LIMIT 100`
   856  
   857  		if len(params.InChannels) > 1 {
   858  			inClause := ":InChannel0"
   859  			queryParams["InChannel0"] = params.InChannels[0]
   860  
   861  			for i := 1; i < len(params.InChannels); i++ {
   862  				paramName := "InChannel" + strconv.FormatInt(int64(i), 10)
   863  				inClause += ", :" + paramName
   864  				queryParams[paramName] = params.InChannels[i]
   865  			}
   866  
   867  			searchQuery = strings.Replace(searchQuery, "CHANNEL_FILTER", "AND Name IN ("+inClause+")", 1)
   868  		} else if len(params.InChannels) == 1 {
   869  			queryParams["InChannel"] = params.InChannels[0]
   870  			searchQuery = strings.Replace(searchQuery, "CHANNEL_FILTER", "AND Name = :InChannel", 1)
   871  		} else {
   872  			searchQuery = strings.Replace(searchQuery, "CHANNEL_FILTER", "", 1)
   873  		}
   874  
   875  		if len(params.FromUsers) > 1 {
   876  			inClause := ":FromUser0"
   877  			queryParams["FromUser0"] = params.FromUsers[0]
   878  
   879  			for i := 1; i < len(params.FromUsers); i++ {
   880  				paramName := "FromUser" + strconv.FormatInt(int64(i), 10)
   881  				inClause += ", :" + paramName
   882  				queryParams[paramName] = params.FromUsers[i]
   883  			}
   884  
   885  			searchQuery = strings.Replace(searchQuery, "POST_FILTER", `
   886  				AND UserId IN (
   887  					SELECT
   888  						Id
   889  					FROM
   890  						Users,
   891  						TeamMembers
   892  					WHERE
   893  						TeamMembers.TeamId = :TeamId
   894  						AND Users.Id = TeamMembers.UserId
   895  						AND Username IN (`+inClause+`))`, 1)
   896  		} else if len(params.FromUsers) == 1 {
   897  			queryParams["FromUser"] = params.FromUsers[0]
   898  			searchQuery = strings.Replace(searchQuery, "POST_FILTER", `
   899  				AND UserId IN (
   900  					SELECT
   901  						Id
   902  					FROM
   903  						Users,
   904  						TeamMembers
   905  					WHERE
   906  						TeamMembers.TeamId = :TeamId
   907  						AND Users.Id = TeamMembers.UserId
   908  						AND Username = :FromUser)`, 1)
   909  		} else {
   910  			searchQuery = strings.Replace(searchQuery, "POST_FILTER", "", 1)
   911  		}
   912  
   913  		if terms == "" {
   914  			// we've already confirmed that we have a channel or user to search for
   915  			searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1)
   916  		} else if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
   917  			// Parse text for wildcards
   918  			if wildcard, err := regexp.Compile(`\*($| )`); err == nil {
   919  				terms = wildcard.ReplaceAllLiteralString(terms, ":* ")
   920  			}
   921  
   922  			if params.OrTerms {
   923  				terms = strings.Join(strings.Fields(terms), " | ")
   924  			} else {
   925  				terms = strings.Join(strings.Fields(terms), " & ")
   926  			}
   927  
   928  			searchClause := fmt.Sprintf("AND %s @@  to_tsquery(:Terms)", searchType)
   929  			searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", searchClause, 1)
   930  		} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   931  			searchClause := fmt.Sprintf("AND MATCH (%s) AGAINST (:Terms IN BOOLEAN MODE)", searchType)
   932  			searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", searchClause, 1)
   933  
   934  			if !params.OrTerms {
   935  				splitTerms := strings.Fields(terms)
   936  				for i, t := range strings.Fields(terms) {
   937  					splitTerms[i] = "+" + t
   938  				}
   939  
   940  				terms = strings.Join(splitTerms, " ")
   941  			}
   942  		}
   943  
   944  		queryParams["Terms"] = terms
   945  
   946  		list := model.NewPostList()
   947  
   948  		_, err := s.GetSearchReplica().Select(&posts, searchQuery, queryParams)
   949  		if err != nil {
   950  			l4g.Warn(utils.T("store.sql_post.search.warn"), err.Error())
   951  			// 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.
   952  		} else {
   953  			for _, p := range posts {
   954  				if searchType == "Hashtags" {
   955  					exactMatch := false
   956  					for _, tag := range strings.Split(p.Hashtags, " ") {
   957  						if termMap[strings.ToUpper(tag)] {
   958  							exactMatch = true
   959  						}
   960  					}
   961  					if !exactMatch {
   962  						continue
   963  					}
   964  				}
   965  				list.AddPost(p)
   966  				list.AddOrder(p.Id)
   967  			}
   968  		}
   969  
   970  		list.MakeNonNil()
   971  
   972  		result.Data = list
   973  	})
   974  }
   975  
   976  func (s *SqlPostStore) AnalyticsUserCountsWithPostsByDay(teamId string) store.StoreChannel {
   977  	return store.Do(func(result *store.StoreResult) {
   978  		query :=
   979  			`SELECT DISTINCT
   980  			        DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
   981  			        COUNT(DISTINCT Posts.UserId) AS Value
   982  			FROM Posts`
   983  
   984  		if len(teamId) > 0 {
   985  			query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id AND Channels.TeamId = :TeamId AND"
   986  		} else {
   987  			query += " WHERE"
   988  		}
   989  
   990  		query += ` Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime
   991  			GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000))
   992  			ORDER BY Name DESC
   993  			LIMIT 30`
   994  
   995  		if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
   996  			query =
   997  				`SELECT
   998  					TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, COUNT(DISTINCT Posts.UserId) AS Value
   999  				FROM Posts`
  1000  
  1001  			if len(teamId) > 0 {
  1002  				query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id AND Channels.TeamId = :TeamId AND"
  1003  			} else {
  1004  				query += " WHERE"
  1005  			}
  1006  
  1007  			query += ` Posts.CreateAt >= :StartTime AND Posts.CreateAt <= :EndTime
  1008  				GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000))
  1009  				ORDER BY Name DESC
  1010  				LIMIT 30`
  1011  		}
  1012  
  1013  		end := utils.MillisFromTime(utils.EndOfDay(utils.Yesterday()))
  1014  		start := utils.MillisFromTime(utils.StartOfDay(utils.Yesterday().AddDate(0, 0, -31)))
  1015  
  1016  		var rows model.AnalyticsRows
  1017  		_, err := s.GetReplica().Select(
  1018  			&rows,
  1019  			query,
  1020  			map[string]interface{}{"TeamId": teamId, "StartTime": start, "EndTime": end})
  1021  		if err != nil {
  1022  			result.Err = model.NewAppError("SqlPostStore.AnalyticsUserCountsWithPostsByDay", "store.sql_post.analytics_user_counts_posts_by_day.app_error", nil, err.Error(), http.StatusInternalServerError)
  1023  		} else {
  1024  			result.Data = rows
  1025  		}
  1026  	})
  1027  }
  1028  
  1029  func (s *SqlPostStore) AnalyticsPostCountsByDay(teamId string) store.StoreChannel {
  1030  	return store.Do(func(result *store.StoreResult) {
  1031  		query :=
  1032  			`SELECT
  1033  			        DATE(FROM_UNIXTIME(Posts.CreateAt / 1000)) AS Name,
  1034  			        COUNT(Posts.Id) AS Value
  1035  			    FROM Posts`
  1036  
  1037  		if len(teamId) > 0 {
  1038  			query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id AND Channels.TeamId = :TeamId AND"
  1039  		} else {
  1040  			query += " WHERE"
  1041  		}
  1042  
  1043  		query += ` Posts.CreateAt <= :EndTime
  1044  			            AND Posts.CreateAt >= :StartTime
  1045  			GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt / 1000))
  1046  			ORDER BY Name DESC
  1047  			LIMIT 30`
  1048  
  1049  		if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
  1050  			query =
  1051  				`SELECT
  1052  					TO_CHAR(DATE(TO_TIMESTAMP(Posts.CreateAt / 1000)), 'YYYY-MM-DD') AS Name, Count(Posts.Id) AS Value
  1053  				FROM Posts`
  1054  
  1055  			if len(teamId) > 0 {
  1056  				query += " INNER JOIN Channels ON Posts.ChannelId = Channels.Id  AND Channels.TeamId = :TeamId AND"
  1057  			} else {
  1058  				query += " WHERE"
  1059  			}
  1060  
  1061  			query += ` Posts.CreateAt <= :EndTime
  1062  				            AND Posts.CreateAt >= :StartTime
  1063  				GROUP BY DATE(TO_TIMESTAMP(Posts.CreateAt / 1000))
  1064  				ORDER BY Name DESC
  1065  				LIMIT 30`
  1066  		}
  1067  
  1068  		end := utils.MillisFromTime(utils.EndOfDay(utils.Yesterday()))
  1069  		start := utils.MillisFromTime(utils.StartOfDay(utils.Yesterday().AddDate(0, 0, -31)))
  1070  
  1071  		var rows model.AnalyticsRows
  1072  		_, err := s.GetReplica().Select(
  1073  			&rows,
  1074  			query,
  1075  			map[string]interface{}{"TeamId": teamId, "StartTime": start, "EndTime": end})
  1076  		if err != nil {
  1077  			result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCountsByDay", "store.sql_post.analytics_posts_count_by_day.app_error", nil, err.Error(), http.StatusInternalServerError)
  1078  		} else {
  1079  			result.Data = rows
  1080  		}
  1081  	})
  1082  }
  1083  
  1084  func (s *SqlPostStore) AnalyticsPostCount(teamId string, mustHaveFile bool, mustHaveHashtag bool) store.StoreChannel {
  1085  	return store.Do(func(result *store.StoreResult) {
  1086  		query :=
  1087  			`SELECT
  1088  			    COUNT(Posts.Id) AS Value
  1089  			FROM
  1090  			    Posts,
  1091  			    Channels
  1092  			WHERE
  1093  			    Posts.ChannelId = Channels.Id`
  1094  
  1095  		if len(teamId) > 0 {
  1096  			query += " AND Channels.TeamId = :TeamId"
  1097  		}
  1098  
  1099  		if mustHaveFile {
  1100  			query += " AND (Posts.FileIds != '[]' OR Posts.Filenames != '[]')"
  1101  		}
  1102  
  1103  		if mustHaveHashtag {
  1104  			query += " AND Posts.Hashtags != ''"
  1105  		}
  1106  
  1107  		if v, err := s.GetReplica().SelectInt(query, map[string]interface{}{"TeamId": teamId}); err != nil {
  1108  			result.Err = model.NewAppError("SqlPostStore.AnalyticsPostCount", "store.sql_post.analytics_posts_count.app_error", nil, err.Error(), http.StatusInternalServerError)
  1109  		} else {
  1110  			result.Data = v
  1111  		}
  1112  	})
  1113  }
  1114  
  1115  func (s *SqlPostStore) GetPostsCreatedAt(channelId string, time int64) store.StoreChannel {
  1116  	return store.Do(func(result *store.StoreResult) {
  1117  		query := `SELECT * FROM Posts WHERE CreateAt = :CreateAt AND ChannelId = :ChannelId`
  1118  
  1119  		var posts []*model.Post
  1120  		_, err := s.GetReplica().Select(&posts, query, map[string]interface{}{"CreateAt": time, "ChannelId": channelId})
  1121  
  1122  		if err != nil {
  1123  			result.Err = model.NewAppError("SqlPostStore.GetPostsCreatedAt", "store.sql_post.get_posts_created_att.app_error", nil, "channelId="+channelId+err.Error(), http.StatusInternalServerError)
  1124  		} else {
  1125  			result.Data = posts
  1126  		}
  1127  	})
  1128  }
  1129  
  1130  func (s *SqlPostStore) GetPostsByIds(postIds []string) store.StoreChannel {
  1131  	return store.Do(func(result *store.StoreResult) {
  1132  		keys := bytes.Buffer{}
  1133  		params := make(map[string]interface{})
  1134  		for i, postId := range postIds {
  1135  			if keys.Len() > 0 {
  1136  				keys.WriteString(",")
  1137  			}
  1138  
  1139  			key := "Post" + strconv.Itoa(i)
  1140  			keys.WriteString(":" + key)
  1141  			params[key] = postId
  1142  		}
  1143  
  1144  		query := `SELECT * FROM Posts WHERE Id in (` + keys.String() + `) and DeleteAt = 0 ORDER BY CreateAt DESC`
  1145  
  1146  		var posts []*model.Post
  1147  		_, err := s.GetReplica().Select(&posts, query, params)
  1148  
  1149  		if err != nil {
  1150  			l4g.Error(err)
  1151  			result.Err = model.NewAppError("SqlPostStore.GetPostsByIds", "store.sql_post.get_posts_by_ids.app_error", nil, "", http.StatusInternalServerError)
  1152  		} else {
  1153  			result.Data = posts
  1154  		}
  1155  	})
  1156  }
  1157  
  1158  func (s *SqlPostStore) GetPostsBatchForIndexing(startTime int64, endTime int64, limit int) store.StoreChannel {
  1159  	return store.Do(func(result *store.StoreResult) {
  1160  		var posts []*model.PostForIndexing
  1161  		_, err1 := s.GetSearchReplica().Select(&posts,
  1162  			`SELECT
  1163  				PostsQuery.*, Channels.TeamId, ParentPosts.CreateAt ParentCreateAt
  1164  			FROM (
  1165  				SELECT
  1166  					*
  1167  				FROM
  1168  					Posts
  1169  				WHERE
  1170  					Posts.CreateAt >= :StartTime
  1171  				AND
  1172  					Posts.CreateAt < :EndTime
  1173  				ORDER BY
  1174  					CreateAt ASC
  1175  				LIMIT
  1176  					1000
  1177  				)
  1178  			AS
  1179  				PostsQuery
  1180  			LEFT JOIN
  1181  				Channels
  1182  			ON
  1183  				PostsQuery.ChannelId = Channels.Id
  1184  			LEFT JOIN
  1185  				Posts ParentPosts
  1186  			ON
  1187  				PostsQuery.RootId = ParentPosts.Id`,
  1188  			map[string]interface{}{"StartTime": startTime, "EndTime": endTime, "NumPosts": limit})
  1189  
  1190  		if err1 != nil {
  1191  			result.Err = model.NewAppError("SqlPostStore.GetPostContext", "store.sql_post.get_posts_batch_for_indexing.get.app_error", nil, err1.Error(), http.StatusInternalServerError)
  1192  		} else {
  1193  			result.Data = posts
  1194  		}
  1195  	})
  1196  }
  1197  
  1198  func (s *SqlPostStore) PermanentDeleteBatch(endTime int64, limit int64) store.StoreChannel {
  1199  	return store.Do(func(result *store.StoreResult) {
  1200  		var query string
  1201  		if s.DriverName() == "postgres" {
  1202  			query = "DELETE from Posts WHERE Id = any (array (SELECT Id FROM Posts WHERE CreateAt < :EndTime LIMIT :Limit))"
  1203  		} else {
  1204  			query = "DELETE from Posts WHERE CreateAt < :EndTime LIMIT :Limit"
  1205  		}
  1206  
  1207  		sqlResult, err := s.GetMaster().Exec(query, map[string]interface{}{"EndTime": endTime, "Limit": limit})
  1208  		if err != nil {
  1209  			result.Err = model.NewAppError("SqlPostStore.PermanentDeleteBatch", "store.sql_post.permanent_delete_batch.app_error", nil, ""+err.Error(), http.StatusInternalServerError)
  1210  		} else {
  1211  			rowsAffected, err1 := sqlResult.RowsAffected()
  1212  			if err1 != nil {
  1213  				result.Err = model.NewAppError("SqlPostStore.PermanentDeleteBatch", "store.sql_post.permanent_delete_batch.app_error", nil, ""+err.Error(), http.StatusInternalServerError)
  1214  				result.Data = int64(0)
  1215  			} else {
  1216  				result.Data = rowsAffected
  1217  			}
  1218  		}
  1219  	})
  1220  }
  1221  
  1222  func (s *SqlPostStore) GetOldest() store.StoreChannel {
  1223  	return store.Do(func(result *store.StoreResult) {
  1224  		var post model.Post
  1225  		err := s.GetReplica().SelectOne(&post, "SELECT * FROM Posts ORDER BY CreateAt LIMIT 1")
  1226  		if err != nil {
  1227  			result.Err = model.NewAppError("SqlPostStore.GetOldest", "store.sql_post.get.app_error", nil, err.Error(), http.StatusNotFound)
  1228  		}
  1229  
  1230  		result.Data = &post
  1231  	})
  1232  }
  1233  
  1234  func (s *SqlPostStore) determineMaxPostSize() int {
  1235  	var maxPostSize int = model.POST_MESSAGE_MAX_RUNES_V1
  1236  	var maxPostSizeBytes int32
  1237  
  1238  	if s.DriverName() == model.DATABASE_DRIVER_POSTGRES {
  1239  		// The Post.Message column in Postgres has historically been VARCHAR(4000), but
  1240  		// may be manually enlarged to support longer posts.
  1241  		if err := s.GetReplica().SelectOne(&maxPostSizeBytes, `
  1242  			SELECT
  1243  				COALESCE(character_maximum_length, 0)
  1244  			FROM
  1245  				information_schema.columns
  1246  			WHERE
  1247  				table_name = 'posts'
  1248  			AND	column_name = 'message'
  1249  		`); err != nil {
  1250  			l4g.Error(utils.T("store.sql_post.query_max_post_size.error") + err.Error())
  1251  		}
  1252  	} else if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
  1253  		// The Post.Message column in MySQL has historically been TEXT, with a maximum
  1254  		// limit of 65535.
  1255  		if err := s.GetReplica().SelectOne(&maxPostSizeBytes, `
  1256  			SELECT 
  1257  				COALESCE(CHARACTER_MAXIMUM_LENGTH, 0)
  1258  			FROM 
  1259  				INFORMATION_SCHEMA.COLUMNS
  1260  			WHERE 
  1261  				table_schema = DATABASE()
  1262  			AND	table_name = 'Posts'
  1263  			AND	column_name = 'Message'
  1264  			LIMIT 0, 1
  1265  		`); err != nil {
  1266  			l4g.Error(utils.T("store.sql_post.query_max_post_size.error") + err.Error())
  1267  		}
  1268  	} else {
  1269  		l4g.Warn(utils.T("store.sql_post.query_max_post_size.unrecognized_driver"))
  1270  	}
  1271  
  1272  	// Assume a worst-case representation of four bytes per rune.
  1273  	maxPostSize = int(maxPostSizeBytes) / 4
  1274  
  1275  	// To maintain backwards compatibility, don't yield a maximum post
  1276  	// size smaller than the previous limit, even though it wasn't
  1277  	// actually possible to store 4000 runes in all cases.
  1278  	if maxPostSize < model.POST_MESSAGE_MAX_RUNES_V1 {
  1279  		maxPostSize = model.POST_MESSAGE_MAX_RUNES_V1
  1280  	}
  1281  
  1282  	l4g.Info(utils.T("store.sql_post.query_max_post_size.max_post_size_bytes"), maxPostSize, maxPostSizeBytes)
  1283  
  1284  	return maxPostSize
  1285  }
  1286  
  1287  // GetMaxPostSize returns the maximum number of runes that may be stored in a post.
  1288  func (s *SqlPostStore) GetMaxPostSize() store.StoreChannel {
  1289  	return store.Do(func(result *store.StoreResult) {
  1290  		s.maxPostSizeOnce.Do(func() {
  1291  			s.maxPostSizeCached = s.determineMaxPostSize()
  1292  		})
  1293  		result.Data = s.maxPostSizeCached
  1294  	})
  1295  }