github.com/haalcala/mattermost-server-change-repo@v0.0.0-20210713015153-16753fbeee5f/store/sqlstore/file_info_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 "database/sql" 8 "fmt" 9 "regexp" 10 "strconv" 11 "strings" 12 13 sq "github.com/Masterminds/squirrel" 14 "github.com/pkg/errors" 15 16 "github.com/mattermost/mattermost-server/v5/einterfaces" 17 "github.com/mattermost/mattermost-server/v5/mlog" 18 "github.com/mattermost/mattermost-server/v5/model" 19 "github.com/mattermost/mattermost-server/v5/store" 20 ) 21 22 type SqlFileInfoStore struct { 23 *SqlStore 24 metrics einterfaces.MetricsInterface 25 queryFields []string 26 } 27 28 func (fs SqlFileInfoStore) ClearCaches() { 29 } 30 31 func newSqlFileInfoStore(sqlStore *SqlStore, metrics einterfaces.MetricsInterface) store.FileInfoStore { 32 s := &SqlFileInfoStore{ 33 SqlStore: sqlStore, 34 metrics: metrics, 35 } 36 37 s.queryFields = []string{ 38 "FileInfo.Id", 39 "FileInfo.CreatorId", 40 "FileInfo.PostId", 41 "FileInfo.CreateAt", 42 "FileInfo.UpdateAt", 43 "FileInfo.DeleteAt", 44 "FileInfo.Path", 45 "FileInfo.ThumbnailPath", 46 "FileInfo.PreviewPath", 47 "FileInfo.Name", 48 "FileInfo.Extension", 49 "FileInfo.Size", 50 "FileInfo.MimeType", 51 "FileInfo.Width", 52 "FileInfo.Height", 53 "FileInfo.HasPreviewImage", 54 "FileInfo.MiniPreview", 55 "Coalesce(FileInfo.Content, '') AS Content", 56 } 57 58 for _, db := range sqlStore.GetAllConns() { 59 table := db.AddTableWithName(model.FileInfo{}, "FileInfo").SetKeys(false, "Id") 60 table.ColMap("Id").SetMaxSize(26) 61 table.ColMap("CreatorId").SetMaxSize(26) 62 table.ColMap("PostId").SetMaxSize(26) 63 table.ColMap("Path").SetMaxSize(512) 64 table.ColMap("ThumbnailPath").SetMaxSize(512) 65 table.ColMap("PreviewPath").SetMaxSize(512) 66 table.ColMap("Name").SetMaxSize(256) 67 table.ColMap("Content").SetMaxSize(0) 68 table.ColMap("Extension").SetMaxSize(64) 69 table.ColMap("MimeType").SetMaxSize(256) 70 } 71 72 return s 73 } 74 75 func (fs SqlFileInfoStore) createIndexesIfNotExists() { 76 fs.CreateIndexIfNotExists("idx_fileinfo_update_at", "FileInfo", "UpdateAt") 77 fs.CreateIndexIfNotExists("idx_fileinfo_create_at", "FileInfo", "CreateAt") 78 fs.CreateIndexIfNotExists("idx_fileinfo_delete_at", "FileInfo", "DeleteAt") 79 fs.CreateIndexIfNotExists("idx_fileinfo_postid_at", "FileInfo", "PostId") 80 fs.CreateIndexIfNotExists("idx_fileinfo_extension_at", "FileInfo", "Extension") 81 fs.CreateFullTextIndexIfNotExists("idx_fileinfo_name_txt", "FileInfo", "Name") 82 fs.CreateFullTextIndexIfNotExists("idx_fileinfo_content_txt", "FileInfo", "Content") 83 } 84 85 func (fs SqlFileInfoStore) Save(info *model.FileInfo) (*model.FileInfo, error) { 86 info.PreSave() 87 if err := info.IsValid(); err != nil { 88 return nil, err 89 } 90 91 if err := fs.GetMaster().Insert(info); err != nil { 92 return nil, errors.Wrap(err, "failed to save FileInfo") 93 } 94 return info, nil 95 } 96 97 func (fs SqlFileInfoStore) GetByIds(ids []string) ([]*model.FileInfo, error) { 98 query := fs.getQueryBuilder(). 99 Select("*"). 100 From("FileInfo"). 101 Where(sq.Eq{"Id": ids}). 102 Where(sq.Eq{"DeleteAt": 0}). 103 OrderBy("CreateAt DESC") 104 105 queryString, args, err := query.ToSql() 106 if err != nil { 107 return nil, errors.Wrap(err, "file_info_tosql") 108 } 109 110 var infos []*model.FileInfo 111 if _, err := fs.GetReplica().Select(&infos, queryString, args...); err != nil { 112 return nil, errors.Wrap(err, "failed to find FileInfos") 113 } 114 return infos, nil 115 } 116 117 func (fs SqlFileInfoStore) Upsert(info *model.FileInfo) (*model.FileInfo, error) { 118 info.PreSave() 119 if err := info.IsValid(); err != nil { 120 return nil, err 121 } 122 123 n, err := fs.GetMaster().Update(info) 124 if err != nil { 125 return nil, errors.Wrap(err, "failed to update FileInfo") 126 } 127 if n == 0 { 128 if err = fs.GetMaster().Insert(info); err != nil { 129 return nil, errors.Wrap(err, "failed to save FileInfo") 130 } 131 } 132 return info, nil 133 } 134 135 func (fs SqlFileInfoStore) Get(id string) (*model.FileInfo, error) { 136 info := &model.FileInfo{} 137 138 query := fs.getQueryBuilder(). 139 Select(fs.queryFields...). 140 From("FileInfo"). 141 Where(sq.Eq{"Id": id}). 142 Where(sq.Eq{"DeleteAt": 0}) 143 144 queryString, args, err := query.ToSql() 145 if err != nil { 146 return nil, errors.Wrap(err, "file_info_tosql") 147 } 148 149 if err := fs.GetReplica().SelectOne(info, queryString, args...); err != nil { 150 if err == sql.ErrNoRows { 151 return nil, store.NewErrNotFound("FileInfo", id) 152 } 153 return nil, errors.Wrapf(err, "failed to get FileInfo with id=%s", id) 154 } 155 return info, nil 156 } 157 158 func (fs SqlFileInfoStore) GetWithOptions(page, perPage int, opt *model.GetFileInfosOptions) ([]*model.FileInfo, error) { 159 if perPage < 0 { 160 return nil, store.NewErrLimitExceeded("perPage", perPage, "value used in pagination while getting FileInfos") 161 } else if page < 0 { 162 return nil, store.NewErrLimitExceeded("page", page, "value used in pagination while getting FileInfos") 163 } 164 if perPage == 0 { 165 return nil, nil 166 } 167 168 if opt == nil { 169 opt = &model.GetFileInfosOptions{} 170 } 171 172 query := fs.getQueryBuilder(). 173 Select(fs.queryFields...). 174 From("FileInfo") 175 176 if len(opt.ChannelIds) > 0 { 177 query = query.Join("Posts ON FileInfo.PostId = Posts.Id"). 178 Where(sq.Eq{"Posts.ChannelId": opt.ChannelIds}) 179 } 180 181 if len(opt.UserIds) > 0 { 182 query = query.Where(sq.Eq{"FileInfo.CreatorId": opt.UserIds}) 183 } 184 185 if opt.Since > 0 { 186 query = query.Where(sq.GtOrEq{"FileInfo.CreateAt": opt.Since}) 187 } 188 189 if !opt.IncludeDeleted { 190 query = query.Where("FileInfo.DeleteAt = 0") 191 } 192 193 if opt.SortBy == "" { 194 opt.SortBy = model.FILEINFO_SORT_BY_CREATED 195 } 196 sortDirection := "ASC" 197 if opt.SortDescending { 198 sortDirection = "DESC" 199 } 200 201 switch opt.SortBy { 202 case model.FILEINFO_SORT_BY_CREATED: 203 query = query.OrderBy("FileInfo.CreateAt " + sortDirection) 204 case model.FILEINFO_SORT_BY_SIZE: 205 query = query.OrderBy("FileInfo.Size " + sortDirection) 206 default: 207 return nil, store.NewErrInvalidInput("FileInfo", "<sortOption>", opt.SortBy) 208 } 209 210 query = query.OrderBy("FileInfo.Id ASC") // secondary sort for sort stability 211 212 query = query.Limit(uint64(perPage)).Offset(uint64(perPage * page)) 213 214 queryString, args, err := query.ToSql() 215 if err != nil { 216 return nil, errors.Wrap(err, "file_info_tosql") 217 } 218 var infos []*model.FileInfo 219 if _, err := fs.GetReplica().Select(&infos, queryString, args...); err != nil { 220 return nil, errors.Wrap(err, "failed to find FileInfos") 221 } 222 return infos, nil 223 } 224 225 func (fs SqlFileInfoStore) GetByPath(path string) (*model.FileInfo, error) { 226 info := &model.FileInfo{} 227 228 query := fs.getQueryBuilder(). 229 Select(fs.queryFields...). 230 From("FileInfo"). 231 Where(sq.Eq{"Path": path}). 232 Where(sq.Eq{"DeleteAt": 0}). 233 Limit(1) 234 235 queryString, args, err := query.ToSql() 236 if err != nil { 237 return nil, errors.Wrap(err, "file_info_tosql") 238 } 239 240 if err := fs.GetReplica().SelectOne(info, queryString, args...); err != nil { 241 if err == sql.ErrNoRows { 242 return nil, store.NewErrNotFound("FileInfo", fmt.Sprintf("path=%s", path)) 243 } 244 245 return nil, errors.Wrapf(err, "failed to get FileInfo with path=%s", path) 246 } 247 return info, nil 248 } 249 250 func (fs SqlFileInfoStore) InvalidateFileInfosForPostCache(postId string, deleted bool) { 251 } 252 253 func (fs SqlFileInfoStore) GetForPost(postId string, readFromMaster, includeDeleted, allowFromCache bool) ([]*model.FileInfo, error) { 254 var infos []*model.FileInfo 255 256 dbmap := fs.GetReplica() 257 258 if readFromMaster { 259 dbmap = fs.GetMaster() 260 } 261 262 query := fs.getQueryBuilder(). 263 Select(fs.queryFields...). 264 From("FileInfo"). 265 Where(sq.Eq{"PostId": postId}). 266 OrderBy("CreateAt") 267 268 if !includeDeleted { 269 query = query.Where("DeleteAt = 0") 270 } 271 272 queryString, args, err := query.ToSql() 273 if err != nil { 274 return nil, errors.Wrap(err, "file_info_tosql") 275 } 276 277 if _, err := dbmap.Select(&infos, queryString, args...); err != nil { 278 return nil, errors.Wrapf(err, "failed to find FileInfos with postId=%s", postId) 279 } 280 return infos, nil 281 } 282 283 func (fs SqlFileInfoStore) GetForUser(userId string) ([]*model.FileInfo, error) { 284 var infos []*model.FileInfo 285 286 dbmap := fs.GetReplica() 287 288 query := fs.getQueryBuilder(). 289 Select(fs.queryFields...). 290 From("FileInfo"). 291 Where(sq.Eq{"CreatorId": userId}). 292 Where(sq.Eq{"DeleteAt": 0}). 293 OrderBy("CreateAt") 294 295 queryString, args, err := query.ToSql() 296 if err != nil { 297 return nil, errors.Wrap(err, "file_info_tosql") 298 } 299 300 if _, err := dbmap.Select(&infos, queryString, args...); err != nil { 301 return nil, errors.Wrapf(err, "failed to find FileInfos with creatorId=%s", userId) 302 } 303 return infos, nil 304 } 305 306 func (fs SqlFileInfoStore) AttachToPost(fileId, postId, creatorId string) error { 307 sqlResult, err := fs.GetMaster().Exec(` 308 UPDATE 309 FileInfo 310 SET 311 PostId = :PostId 312 WHERE 313 Id = :Id 314 AND PostId = '' 315 AND (CreatorId = :CreatorId OR CreatorId = 'nouser') 316 `, map[string]interface{}{ 317 "PostId": postId, 318 "Id": fileId, 319 "CreatorId": creatorId, 320 }) 321 if err != nil { 322 return errors.Wrapf(err, "failed to update FileInfo with id=%s and postId=%s", fileId, postId) 323 } 324 325 count, err := sqlResult.RowsAffected() 326 if err != nil { 327 // RowsAffected should never fail with the MySQL or Postgres drivers 328 return errors.Wrap(err, "unable to retrieve rows affected") 329 } else if count == 0 { 330 // Could not attach the file to the post 331 return store.NewErrInvalidInput("FileInfo", "<id, postId, creatorId>", fmt.Sprintf("<%s, %s, %s>", fileId, postId, creatorId)) 332 } 333 return nil 334 } 335 336 func (fs SqlFileInfoStore) SetContent(fileId, content string) error { 337 query := fs.getQueryBuilder(). 338 Update("FileInfo"). 339 Set("Content", content). 340 Where(sq.Eq{"Id": fileId}) 341 342 queryString, args, err := query.ToSql() 343 if err != nil { 344 return errors.Wrap(err, "file_info_tosql") 345 } 346 347 sqlResult, err := fs.GetMaster().Exec(queryString, args...) 348 if err != nil { 349 return errors.Wrapf(err, "failed to update FileInfo content with id=%s", fileId) 350 } 351 352 count, err := sqlResult.RowsAffected() 353 if err != nil { 354 // RowsAffected should never fail with the MySQL or Postgres drivers 355 return errors.Wrap(err, "unable to retrieve rows affected") 356 } else if count == 0 { 357 // Could not attach the file to the post 358 return store.NewErrInvalidInput("FileInfo", "<id>", fmt.Sprintf("<%s>", fileId)) 359 } 360 return nil 361 } 362 363 func (fs SqlFileInfoStore) DeleteForPost(postId string) (string, error) { 364 if _, err := fs.GetMaster().Exec( 365 `UPDATE 366 FileInfo 367 SET 368 DeleteAt = :DeleteAt 369 WHERE 370 PostId = :PostId`, map[string]interface{}{"DeleteAt": model.GetMillis(), "PostId": postId}); err != nil { 371 return "", errors.Wrapf(err, "failed to update FileInfo with postId=%s", postId) 372 } 373 return postId, nil 374 } 375 376 func (fs SqlFileInfoStore) PermanentDelete(fileId string) error { 377 if _, err := fs.GetMaster().Exec( 378 `DELETE FROM 379 FileInfo 380 WHERE 381 Id = :FileId`, map[string]interface{}{"FileId": fileId}); err != nil { 382 return errors.Wrapf(err, "failed to delete FileInfo with id=%s", fileId) 383 } 384 return nil 385 } 386 387 func (fs SqlFileInfoStore) PermanentDeleteBatch(endTime int64, limit int64) (int64, error) { 388 var query string 389 if fs.DriverName() == "postgres" { 390 query = "DELETE from FileInfo WHERE Id = any (array (SELECT Id FROM FileInfo WHERE CreateAt < :EndTime LIMIT :Limit))" 391 } else { 392 query = "DELETE from FileInfo WHERE CreateAt < :EndTime LIMIT :Limit" 393 } 394 395 sqlResult, err := fs.GetMaster().Exec(query, map[string]interface{}{"EndTime": endTime, "Limit": limit}) 396 if err != nil { 397 return 0, errors.Wrap(err, "failed to delete FileInfos in batch") 398 } 399 400 rowsAffected, err := sqlResult.RowsAffected() 401 if err != nil { 402 return 0, errors.Wrapf(err, "unable to retrieve rows affected") 403 } 404 405 return rowsAffected, nil 406 } 407 408 func (fs SqlFileInfoStore) PermanentDeleteByUser(userId string) (int64, error) { 409 query := "DELETE from FileInfo WHERE CreatorId = :CreatorId" 410 411 sqlResult, err := fs.GetMaster().Exec(query, map[string]interface{}{"CreatorId": userId}) 412 if err != nil { 413 return 0, errors.Wrapf(err, "failed to delete FileInfo with creatorId=%s", userId) 414 } 415 416 rowsAffected, err := sqlResult.RowsAffected() 417 if err != nil { 418 return 0, errors.Wrapf(err, "unable to retrieve rows affected") 419 } 420 421 return rowsAffected, nil 422 } 423 424 func (fs SqlFileInfoStore) Search(paramsList []*model.SearchParams, userId, teamId string, page, perPage int) (*model.FileInfoList, error) { 425 // Since we don't support paging for DB search, we just return nothing for later pages 426 if page > 0 { 427 return model.NewFileInfoList(), nil 428 } 429 if err := model.IsSearchParamsListValid(paramsList); err != nil { 430 return nil, err 431 } 432 query := fs.getQueryBuilder(). 433 Select("FI.*"). 434 From("FileInfo AS FI"). 435 LeftJoin("Posts as P ON FI.PostId=P.Id"). 436 LeftJoin("Channels as C ON C.Id=P.ChannelId"). 437 LeftJoin("ChannelMembers as CM ON C.Id=CM.ChannelId"). 438 Where(sq.Or{sq.Eq{"C.TeamId": teamId}, sq.Eq{"C.TeamId": ""}}). 439 Where(sq.Eq{"FI.DeleteAt": 0}). 440 OrderBy("FI.CreateAt DESC"). 441 Limit(100) 442 443 for _, params := range paramsList { 444 params.Terms = removeNonAlphaNumericUnquotedTerms(params.Terms, " ") 445 446 if !params.IncludeDeletedChannels { 447 query = query.Where(sq.Eq{"C.DeleteAt": 0}) 448 } 449 450 if !params.SearchWithoutUserId { 451 query = query.Where(sq.Eq{"CM.UserId": userId}) 452 } 453 454 if len(params.InChannels) != 0 { 455 query = query.Where(sq.Eq{"C.Id": params.InChannels}) 456 } 457 458 if len(params.Extensions) != 0 { 459 query = query.Where(sq.Eq{"FI.Extension": params.Extensions}) 460 } 461 462 if len(params.ExcludedExtensions) != 0 { 463 query = query.Where(sq.NotEq{"FI.Extension": params.ExcludedExtensions}) 464 } 465 466 if len(params.ExcludedChannels) != 0 { 467 query = query.Where(sq.NotEq{"C.Id": params.ExcludedChannels}) 468 } 469 470 if len(params.FromUsers) != 0 { 471 query = query.Where(sq.Eq{"FI.CreatorId": params.FromUsers}) 472 } 473 474 if len(params.ExcludedUsers) != 0 { 475 query = query.Where(sq.NotEq{"FI.CreatorId": params.ExcludedUsers}) 476 } 477 478 // handle after: before: on: filters 479 if params.OnDate != "" { 480 onDateStart, onDateEnd := params.GetOnDateMillis() 481 query = query.Where(sq.Expr("FI.CreateAt BETWEEN ? AND ?", strconv.FormatInt(onDateStart, 10), strconv.FormatInt(onDateEnd, 10))) 482 } else { 483 if params.ExcludedDate != "" { 484 excludedDateStart, excludedDateEnd := params.GetExcludedDateMillis() 485 query = query.Where(sq.Expr("FI.CreateAt NOT BETWEEN ? AND ?", strconv.FormatInt(excludedDateStart, 10), strconv.FormatInt(excludedDateEnd, 10))) 486 } 487 488 if params.AfterDate != "" { 489 afterDate := params.GetAfterDateMillis() 490 query = query.Where(sq.GtOrEq{"FI.CreateAt": strconv.FormatInt(afterDate, 10)}) 491 } 492 493 if params.BeforeDate != "" { 494 beforeDate := params.GetBeforeDateMillis() 495 query = query.Where(sq.LtOrEq{"FI.CreateAt": strconv.FormatInt(beforeDate, 10)}) 496 } 497 498 if params.ExcludedAfterDate != "" { 499 afterDate := params.GetExcludedAfterDateMillis() 500 query = query.Where(sq.Lt{"FI.CreateAt": strconv.FormatInt(afterDate, 10)}) 501 } 502 503 if params.ExcludedBeforeDate != "" { 504 beforeDate := params.GetExcludedBeforeDateMillis() 505 query = query.Where(sq.Gt{"FI.CreateAt": strconv.FormatInt(beforeDate, 10)}) 506 } 507 } 508 509 terms := params.Terms 510 excludedTerms := params.ExcludedTerms 511 512 // these chars have special meaning and can be treated as spaces 513 for _, c := range specialSearchChar { 514 terms = strings.Replace(terms, c, " ", -1) 515 excludedTerms = strings.Replace(excludedTerms, c, " ", -1) 516 } 517 518 if terms == "" && excludedTerms == "" { 519 // we've already confirmed that we have a channel or user to search for 520 } else if fs.DriverName() == model.DATABASE_DRIVER_POSTGRES { 521 // Parse text for wildcards 522 if wildcard, err := regexp.Compile(`\*($| )`); err == nil { 523 terms = wildcard.ReplaceAllLiteralString(terms, ":* ") 524 excludedTerms = wildcard.ReplaceAllLiteralString(excludedTerms, ":* ") 525 } 526 527 excludeClause := "" 528 if excludedTerms != "" { 529 excludeClause = " & !(" + strings.Join(strings.Fields(excludedTerms), " | ") + ")" 530 } 531 532 queryTerms := "" 533 if params.OrTerms { 534 queryTerms = "(" + strings.Join(strings.Fields(terms), " | ") + ")" + excludeClause 535 } else { 536 queryTerms = "(" + strings.Join(strings.Fields(terms), " & ") + ")" + excludeClause 537 } 538 539 query = query.Where(sq.Or{ 540 sq.Expr("to_tsvector('english', FI.Name) @@ to_tsquery('english', ?)", queryTerms), 541 sq.Expr("to_tsvector('english', FI.Content) @@ to_tsquery('english', ?)", queryTerms), 542 }) 543 } else if fs.DriverName() == model.DATABASE_DRIVER_MYSQL { 544 var err error 545 terms, err = removeMysqlStopWordsFromTerms(terms) 546 if err != nil { 547 return nil, errors.Wrap(err, "failed to remove Mysql stop-words from terms") 548 } 549 550 if terms == "" { 551 return model.NewFileInfoList(), nil 552 } 553 554 excludeClause := "" 555 if excludedTerms != "" { 556 excludeClause = " -(" + excludedTerms + ")" 557 } 558 559 queryTerms := "" 560 if params.OrTerms { 561 queryTerms = terms + excludeClause 562 } else { 563 splitTerms := []string{} 564 for _, t := range strings.Fields(terms) { 565 splitTerms = append(splitTerms, "+"+t) 566 } 567 queryTerms = strings.Join(splitTerms, " ") + excludeClause 568 } 569 query = query.Where(sq.Or{ 570 sq.Expr("MATCH (FI.Name) AGAINST (? IN BOOLEAN MODE)", queryTerms), 571 sq.Expr("MATCH (FI.Content) AGAINST (? IN BOOLEAN MODE)", queryTerms), 572 }) 573 } 574 } 575 576 queryString, args, err := query.ToSql() 577 if err != nil { 578 return nil, errors.Wrap(err, "file_info_tosql") 579 } 580 581 list := model.NewFileInfoList() 582 fileInfos := []*model.FileInfo{} 583 _, err = fs.GetSearchReplica().Select(&fileInfos, queryString, args...) 584 if err != nil { 585 mlog.Warn("Query error searching files.", mlog.Err(err)) 586 // 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. 587 } else { 588 for _, f := range fileInfos { 589 list.AddFileInfo(f) 590 list.AddOrder(f.Id) 591 } 592 } 593 list.MakeNonNil() 594 return list, nil 595 }