From 8054669f1181e815c435e76e81247eff32d41dc5 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 20 Aug 2020 09:19:21 +0200 Subject: Optimize default sort when listing videos createdAt does not have the appropriate index --- server/controllers/api/accounts.ts | 15 ++++--- server/controllers/api/users/me.ts | 3 +- server/controllers/api/users/my-subscriptions.ts | 19 +++++---- server/controllers/api/video-channel.ts | 41 +++++++++--------- server/controllers/api/videos/index.ts | 54 ++++++++++++------------ server/controllers/feeds.ts | 18 ++++---- server/middlewares/sort.ts | 2 + 7 files changed, 79 insertions(+), 73 deletions(-) (limited to 'server') diff --git a/server/controllers/api/accounts.ts b/server/controllers/api/accounts.ts index b1c05c6c0..0a73dfcbf 100644 --- a/server/controllers/api/accounts.ts +++ b/server/controllers/api/accounts.ts @@ -1,5 +1,8 @@ import * as express from 'express' +import { getServerActor } from '@server/models/application/application' +import { buildNSFWFilter, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils' import { getFormattedObjects } from '../../helpers/utils' +import { JobQueue } from '../../lib/job-queue' import { asyncMiddleware, authenticate, @@ -8,6 +11,7 @@ import { paginationValidator, setDefaultPagination, setDefaultSort, + setDefaultVideosSort, videoPlaylistsSortValidator, videoRatesSortValidator, videoRatingValidator @@ -17,18 +21,15 @@ import { accountsSortValidator, ensureAuthUserOwnsAccountValidator, videoChannelsSortValidator, - videosSortValidator, - videoChannelStatsValidator + videoChannelStatsValidator, + videosSortValidator } from '../../middlewares/validators' +import { commonVideoPlaylistFiltersValidator, videoPlaylistsSearchValidator } from '../../middlewares/validators/videos/video-playlists' import { AccountModel } from '../../models/account/account' import { AccountVideoRateModel } from '../../models/account/account-video-rate' import { VideoModel } from '../../models/video/video' -import { buildNSFWFilter, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils' import { VideoChannelModel } from '../../models/video/video-channel' -import { JobQueue } from '../../lib/job-queue' import { VideoPlaylistModel } from '../../models/video/video-playlist' -import { commonVideoPlaylistFiltersValidator, videoPlaylistsSearchValidator } from '../../middlewares/validators/videos/video-playlists' -import { getServerActor } from '@server/models/application/application' const accountsRouter = express.Router() @@ -49,7 +50,7 @@ accountsRouter.get('/:accountName/videos', asyncMiddleware(accountNameWithHostGetValidator), paginationValidator, videosSortValidator, - setDefaultSort, + setDefaultVideosSort, setDefaultPagination, optionalAuthenticate, commonVideosFiltersValidator, diff --git a/server/controllers/api/users/me.ts b/server/controllers/api/users/me.ts index 914c52e27..ba60a3d2a 100644 --- a/server/controllers/api/users/me.ts +++ b/server/controllers/api/users/me.ts @@ -17,6 +17,7 @@ import { paginationValidator, setDefaultPagination, setDefaultSort, + setDefaultVideosSort, usersUpdateMeValidator, usersVideoRatingValidator } from '../../../middlewares' @@ -60,7 +61,7 @@ meRouter.get('/me/videos', authenticate, paginationValidator, videosSortValidator, - setDefaultSort, + setDefaultVideosSort, setDefaultPagination, asyncMiddleware(getUserVideos) ) diff --git a/server/controllers/api/users/my-subscriptions.ts b/server/controllers/api/users/my-subscriptions.ts index 8b88feaf3..b8c234eef 100644 --- a/server/controllers/api/users/my-subscriptions.ts +++ b/server/controllers/api/users/my-subscriptions.ts @@ -1,7 +1,11 @@ -import * as express from 'express' import 'multer' +import * as express from 'express' +import { VideoFilter } from '../../../../shared/models/videos/video-query.type' +import { buildNSFWFilter, getCountVideos } from '../../../helpers/express-utils' import { getFormattedObjects } from '../../../helpers/utils' import { WEBSERVER } from '../../../initializers/constants' +import { sequelizeTypescript } from '../../../initializers/database' +import { JobQueue } from '../../../lib/job-queue' import { asyncMiddleware, asyncRetryTransactionMiddleware, @@ -10,21 +14,18 @@ import { paginationValidator, setDefaultPagination, setDefaultSort, + setDefaultVideosSort, userSubscriptionAddValidator, userSubscriptionGetValidator } from '../../../middlewares' import { areSubscriptionsExistValidator, + userSubscriptionListValidator, userSubscriptionsSortValidator, - videosSortValidator, - userSubscriptionListValidator + videosSortValidator } from '../../../middlewares/validators' -import { VideoModel } from '../../../models/video/video' -import { buildNSFWFilter, getCountVideos } from '../../../helpers/express-utils' -import { VideoFilter } from '../../../../shared/models/videos/video-query.type' import { ActorFollowModel } from '../../../models/activitypub/actor-follow' -import { JobQueue } from '../../../lib/job-queue' -import { sequelizeTypescript } from '../../../initializers/database' +import { VideoModel } from '../../../models/video/video' const mySubscriptionsRouter = express.Router() @@ -32,7 +33,7 @@ mySubscriptionsRouter.get('/me/subscriptions/videos', authenticate, paginationValidator, videosSortValidator, - setDefaultSort, + setDefaultVideosSort, setDefaultPagination, commonVideosFiltersValidator, asyncMiddleware(getUserSubscriptionVideos) diff --git a/server/controllers/api/video-channel.ts b/server/controllers/api/video-channel.ts index f705034fd..45c936978 100644 --- a/server/controllers/api/video-channel.ts +++ b/server/controllers/api/video-channel.ts @@ -1,5 +1,20 @@ import * as express from 'express' +import { getServerActor } from '@server/models/application/application' +import { MChannelAccountDefault } from '@server/types/models' +import { VideoChannelCreate, VideoChannelUpdate } from '../../../shared' +import { auditLoggerFactory, getAuditIdFromRes, VideoChannelAuditView } from '../../helpers/audit-logger' +import { resetSequelizeInstance } from '../../helpers/database-utils' +import { buildNSFWFilter, createReqFiles, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils' +import { logger } from '../../helpers/logger' import { getFormattedObjects } from '../../helpers/utils' +import { CONFIG } from '../../initializers/config' +import { MIMETYPES } from '../../initializers/constants' +import { sequelizeTypescript } from '../../initializers/database' +import { setAsyncActorKeys } from '../../lib/activitypub/actor' +import { sendUpdateActor } from '../../lib/activitypub/send' +import { updateActorAvatarFile } from '../../lib/avatar' +import { JobQueue } from '../../lib/job-queue' +import { createLocalVideoChannel, federateAllVideosOfChannel } from '../../lib/video-channel' import { asyncMiddleware, asyncRetryTransactionMiddleware, @@ -9,34 +24,20 @@ import { paginationValidator, setDefaultPagination, setDefaultSort, + setDefaultVideosSort, videoChannelsAddValidator, videoChannelsRemoveValidator, videoChannelsSortValidator, videoChannelsUpdateValidator, videoPlaylistsSortValidator } from '../../middlewares' -import { VideoChannelModel } from '../../models/video/video-channel' -import { videoChannelsNameWithHostValidator, videosSortValidator, videoChannelsOwnSearchValidator } from '../../middlewares/validators' -import { sendUpdateActor } from '../../lib/activitypub/send' -import { VideoChannelCreate, VideoChannelUpdate } from '../../../shared' -import { createLocalVideoChannel, federateAllVideosOfChannel } from '../../lib/video-channel' -import { buildNSFWFilter, createReqFiles, getCountVideos, isUserAbleToSearchRemoteURI } from '../../helpers/express-utils' -import { setAsyncActorKeys } from '../../lib/activitypub/actor' +import { videoChannelsNameWithHostValidator, videoChannelsOwnSearchValidator, videosSortValidator } from '../../middlewares/validators' +import { updateAvatarValidator } from '../../middlewares/validators/avatar' +import { commonVideoPlaylistFiltersValidator } from '../../middlewares/validators/videos/video-playlists' import { AccountModel } from '../../models/account/account' -import { MIMETYPES } from '../../initializers/constants' -import { logger } from '../../helpers/logger' import { VideoModel } from '../../models/video/video' -import { updateAvatarValidator } from '../../middlewares/validators/avatar' -import { updateActorAvatarFile } from '../../lib/avatar' -import { auditLoggerFactory, getAuditIdFromRes, VideoChannelAuditView } from '../../helpers/audit-logger' -import { resetSequelizeInstance } from '../../helpers/database-utils' -import { JobQueue } from '../../lib/job-queue' +import { VideoChannelModel } from '../../models/video/video-channel' import { VideoPlaylistModel } from '../../models/video/video-playlist' -import { commonVideoPlaylistFiltersValidator } from '../../middlewares/validators/videos/video-playlists' -import { CONFIG } from '../../initializers/config' -import { sequelizeTypescript } from '../../initializers/database' -import { MChannelAccountDefault } from '@server/types/models' -import { getServerActor } from '@server/models/application/application' const auditLogger = auditLoggerFactory('channels') const reqAvatarFile = createReqFiles([ 'avatarfile' ], MIMETYPES.IMAGE.MIMETYPE_EXT, { avatarfile: CONFIG.STORAGE.TMP_DIR }) @@ -98,7 +99,7 @@ videoChannelRouter.get('/:nameWithHost/videos', asyncMiddleware(videoChannelsNameWithHostValidator), paginationValidator, videosSortValidator, - setDefaultSort, + setDefaultVideosSort, setDefaultPagination, optionalAuthenticate, commonVideosFiltersValidator, diff --git a/server/controllers/api/videos/index.ts b/server/controllers/api/videos/index.ts index 1dfd7c7a0..c05acfd2f 100644 --- a/server/controllers/api/videos/index.ts +++ b/server/controllers/api/videos/index.ts @@ -1,11 +1,24 @@ import * as express from 'express' +import { move } from 'fs-extra' import { extname } from 'path' +import toInt from 'validator/lib/toInt' +import { addOptimizeOrMergeAudioJob } from '@server/helpers/video' +import { createTorrentAndSetInfoHash } from '@server/helpers/webtorrent' +import { changeVideoChannelShare } from '@server/lib/activitypub/share' +import { getVideoActivityPubUrl } from '@server/lib/activitypub/url' +import { getVideoFilePath } from '@server/lib/video-paths' +import { getServerActor } from '@server/models/application/application' +import { MVideoDetails, MVideoFullLight } from '@server/types/models' import { VideoCreate, VideoPrivacy, VideoState, VideoUpdate } from '../../../../shared' +import { ThumbnailType } from '../../../../shared/models/videos/thumbnail.type' +import { VideoFilter } from '../../../../shared/models/videos/video-query.type' +import { auditLoggerFactory, getAuditIdFromRes, VideoAuditView } from '../../../helpers/audit-logger' +import { resetSequelizeInstance } from '../../../helpers/database-utils' +import { buildNSFWFilter, createReqFiles, getCountVideos } from '../../../helpers/express-utils' import { getMetadataFromFile, getVideoFileFPS, getVideoFileResolution } from '../../../helpers/ffmpeg-utils' import { logger } from '../../../helpers/logger' -import { auditLoggerFactory, getAuditIdFromRes, VideoAuditView } from '../../../helpers/audit-logger' import { getFormattedObjects } from '../../../helpers/utils' -import { autoBlacklistVideoIfNeeded } from '../../../lib/video-blacklist' +import { CONFIG } from '../../../initializers/config' import { DEFAULT_AUDIO_RESOLUTION, MIMETYPES, @@ -14,9 +27,15 @@ import { VIDEO_LICENCES, VIDEO_PRIVACIES } from '../../../initializers/constants' +import { sequelizeTypescript } from '../../../initializers/database' +import { sendView } from '../../../lib/activitypub/send/send-view' import { federateVideoIfNeeded, fetchRemoteVideoDescription } from '../../../lib/activitypub/videos' import { JobQueue } from '../../../lib/job-queue' +import { Notifier } from '../../../lib/notifier' +import { Hooks } from '../../../lib/plugins/hooks' import { Redis } from '../../../lib/redis' +import { createVideoMiniatureFromExisting, generateVideoMiniature } from '../../../lib/thumbnail' +import { autoBlacklistVideoIfNeeded } from '../../../lib/video-blacklist' import { asyncMiddleware, asyncRetryTransactionMiddleware, @@ -26,7 +45,7 @@ import { optionalAuthenticate, paginationValidator, setDefaultPagination, - setDefaultSort, + setDefaultVideosSort, videoFileMetadataGetValidator, videosAddValidator, videosCustomGetValidator, @@ -35,37 +54,18 @@ import { videosSortValidator, videosUpdateValidator } from '../../../middlewares' +import { ScheduleVideoUpdateModel } from '../../../models/video/schedule-video-update' import { TagModel } from '../../../models/video/tag' import { VideoModel } from '../../../models/video/video' import { VideoFileModel } from '../../../models/video/video-file' import { abuseVideoRouter } from './abuse' import { blacklistRouter } from './blacklist' -import { videoCommentRouter } from './comment' -import { rateVideoRouter } from './rate' -import { ownershipVideoRouter } from './ownership' -import { VideoFilter } from '../../../../shared/models/videos/video-query.type' -import { buildNSFWFilter, createReqFiles, getCountVideos } from '../../../helpers/express-utils' -import { ScheduleVideoUpdateModel } from '../../../models/video/schedule-video-update' import { videoCaptionsRouter } from './captions' +import { videoCommentRouter } from './comment' import { videoImportsRouter } from './import' -import { resetSequelizeInstance } from '../../../helpers/database-utils' -import { move } from 'fs-extra' +import { ownershipVideoRouter } from './ownership' +import { rateVideoRouter } from './rate' import { watchingRouter } from './watching' -import { Notifier } from '../../../lib/notifier' -import { sendView } from '../../../lib/activitypub/send/send-view' -import { CONFIG } from '../../../initializers/config' -import { sequelizeTypescript } from '../../../initializers/database' -import { createVideoMiniatureFromExisting, generateVideoMiniature } from '../../../lib/thumbnail' -import { ThumbnailType } from '../../../../shared/models/videos/thumbnail.type' -import { Hooks } from '../../../lib/plugins/hooks' -import { MVideoDetails, MVideoFullLight } from '@server/types/models' -import { createTorrentAndSetInfoHash } from '@server/helpers/webtorrent' -import { getVideoFilePath } from '@server/lib/video-paths' -import toInt from 'validator/lib/toInt' -import { addOptimizeOrMergeAudioJob } from '@server/helpers/video' -import { getServerActor } from '@server/models/application/application' -import { changeVideoChannelShare } from '@server/lib/activitypub/share' -import { getVideoActivityPubUrl } from '@server/lib/activitypub/url' const auditLogger = auditLoggerFactory('videos') const videosRouter = express.Router() @@ -105,7 +105,7 @@ videosRouter.get('/privacies', listVideoPrivacies) videosRouter.get('/', paginationValidator, videosSortValidator, - setDefaultSort, + setDefaultVideosSort, setDefaultPagination, optionalAuthenticate, commonVideosFiltersValidator, diff --git a/server/controllers/feeds.ts b/server/controllers/feeds.ts index bfcd3fe36..f14c0d316 100644 --- a/server/controllers/feeds.ts +++ b/server/controllers/feeds.ts @@ -1,21 +1,21 @@ import * as express from 'express' +import * as Feed from 'pfeed' +import { buildNSFWFilter } from '../helpers/express-utils' +import { CONFIG } from '../initializers/config' import { FEEDS, ROUTE_CACHE_LIFETIME, THUMBNAILS_SIZE, WEBSERVER } from '../initializers/constants' import { asyncMiddleware, commonVideosFiltersValidator, - setDefaultSort, + feedsFormatValidator, + setDefaultVideosSort, + setFeedFormatContentType, videoCommentsFeedsValidator, videoFeedsValidator, - videosSortValidator, - feedsFormatValidator, - setFeedFormatContentType + videosSortValidator } from '../middlewares' -import { VideoModel } from '../models/video/video' -import * as Feed from 'pfeed' import { cacheRoute } from '../middlewares/cache' +import { VideoModel } from '../models/video/video' import { VideoCommentModel } from '../models/video/video-comment' -import { buildNSFWFilter } from '../helpers/express-utils' -import { CONFIG } from '../initializers/config' const feedsRouter = express.Router() @@ -34,7 +34,7 @@ feedsRouter.get('/feeds/video-comments.:format', feedsRouter.get('/feeds/videos.:format', videosSortValidator, - setDefaultSort, + setDefaultVideosSort, feedsFormatValidator, setFeedFormatContentType, asyncMiddleware(cacheRoute({ diff --git a/server/middlewares/sort.ts b/server/middlewares/sort.ts index fcbb2902c..609046a46 100644 --- a/server/middlewares/sort.ts +++ b/server/middlewares/sort.ts @@ -2,6 +2,7 @@ import * as express from 'express' import { SortType } from '../models/utils' const setDefaultSort = setDefaultSortFactory('-createdAt') +const setDefaultVideosSort = setDefaultSortFactory('-publishedAt') const setDefaultVideoRedundanciesSort = setDefaultSortFactory('name') @@ -33,6 +34,7 @@ function setBlacklistSort (req: express.Request, res: express.Response, next: ex export { setDefaultSort, setDefaultSearchSort, + setDefaultVideosSort, setDefaultVideoRedundanciesSort, setBlacklistSort } -- cgit v1.2.3