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 }