aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-09-18 12:00:49 +0200
committerChocobozzz <me@florianbigard.com>2018-09-19 09:54:37 +0200
commit627621c1e8d37c33f7b3dd59f4c8907b12c630bc (patch)
tree007e7118f482c543d3898b222b62c185fda4fd2b /server
parente972e046dbe9b499944c4fab9220eee13e31ac1b (diff)
downloadPeerTube-627621c1e8d37c33f7b3dd59f4c8907b12c630bc.tar.gz
PeerTube-627621c1e8d37c33f7b3dd59f4c8907b12c630bc.tar.zst
PeerTube-627621c1e8d37c33f7b3dd59f4c8907b12c630bc.zip
Optimize SQL requests of watch page API endpoints
Diffstat (limited to 'server')
-rw-r--r--server/controllers/api/users/me.ts2
-rw-r--r--server/controllers/api/videos/comment.ts2
-rw-r--r--server/helpers/custom-validators/videos.ts14
-rw-r--r--server/lib/cache/videos-caption-cache.ts2
-rw-r--r--server/lib/cache/videos-preview-cache.ts4
-rw-r--r--server/lib/client-html.ts6
-rw-r--r--server/lib/job-queue/handlers/video-file.ts8
-rw-r--r--server/lib/job-queue/handlers/video-import.ts2
-rw-r--r--server/middlewares/validators/users.ts2
-rw-r--r--server/middlewares/validators/video-captions.ts2
-rw-r--r--server/middlewares/validators/video-comments.ts4
-rw-r--r--server/models/video/video.ts74
12 files changed, 65 insertions, 57 deletions
diff --git a/server/controllers/api/users/me.ts b/server/controllers/api/users/me.ts
index e886d4b2a..113563c39 100644
--- a/server/controllers/api/users/me.ts
+++ b/server/controllers/api/users/me.ts
@@ -293,7 +293,7 @@ async function getUserVideoQuotaUsed (req: express.Request, res: express.Respons
293} 293}
294 294
295async function getUserVideoRating (req: express.Request, res: express.Response, next: express.NextFunction) { 295async function getUserVideoRating (req: express.Request, res: express.Response, next: express.NextFunction) {
296 const videoId = +req.params.videoId 296 const videoId = res.locals.video.id
297 const accountId = +res.locals.oauth.token.User.Account.id 297 const accountId = +res.locals.oauth.token.User.Account.id
298 298
299 const ratingObj = await AccountVideoRateModel.load(accountId, videoId, null) 299 const ratingObj = await AccountVideoRateModel.load(accountId, videoId, null)
diff --git a/server/controllers/api/videos/comment.ts b/server/controllers/api/videos/comment.ts
index e35247829..8d0692b2b 100644
--- a/server/controllers/api/videos/comment.ts
+++ b/server/controllers/api/videos/comment.ts
@@ -86,7 +86,7 @@ async function listVideoThreadComments (req: express.Request, res: express.Respo
86 let resultList: ResultList<VideoCommentModel> 86 let resultList: ResultList<VideoCommentModel>
87 87
88 if (video.commentsEnabled === true) { 88 if (video.commentsEnabled === true) {
89 resultList = await VideoCommentModel.listThreadCommentsForApi(res.locals.video.id, res.locals.videoCommentThread.id) 89 resultList = await VideoCommentModel.listThreadCommentsForApi(video.id, res.locals.videoCommentThread.id)
90 } else { 90 } else {
91 resultList = { 91 resultList = {
92 total: 0, 92 total: 0,
diff --git a/server/helpers/custom-validators/videos.ts b/server/helpers/custom-validators/videos.ts
index edafba6e2..dd207c787 100644
--- a/server/helpers/custom-validators/videos.ts
+++ b/server/helpers/custom-validators/videos.ts
@@ -152,13 +152,15 @@ function checkUserCanManageVideo (user: UserModel, video: VideoModel, right: Use
152 return true 152 return true
153} 153}
154 154
155async function isVideoExist (id: string, res: Response) { 155async function isVideoExist (id: string, res: Response, fetchType: 'all' | 'only-video' | 'id' | 'none' = 'all') {
156 let video: VideoModel | null 156 let video: VideoModel | null
157 157
158 if (validator.isInt(id)) { 158 if (fetchType === 'all') {
159 video = await VideoModel.loadAndPopulateAccountAndServerAndTags(+id) 159 video = await VideoModel.loadAndPopulateAccountAndServerAndTags(id)
160 } else { // UUID 160 } else if (fetchType === 'only-video') {
161 video = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(id) 161 video = await VideoModel.load(id)
162 } else if (fetchType === 'id' || fetchType === 'none') {
163 video = await VideoModel.loadOnlyId(id)
162 } 164 }
163 165
164 if (video === null) { 166 if (video === null) {
@@ -169,7 +171,7 @@ async function isVideoExist (id: string, res: Response) {
169 return false 171 return false
170 } 172 }
171 173
172 res.locals.video = video 174 if (fetchType !== 'none') res.locals.video = video
173 return true 175 return true
174} 176}
175 177
diff --git a/server/lib/cache/videos-caption-cache.ts b/server/lib/cache/videos-caption-cache.ts
index 380d42b2c..f240affbc 100644
--- a/server/lib/cache/videos-caption-cache.ts
+++ b/server/lib/cache/videos-caption-cache.ts
@@ -38,7 +38,7 @@ class VideosCaptionCache extends AbstractVideoStaticFileCache <GetPathParam> {
38 if (videoCaption.isOwned()) throw new Error('Cannot load remote caption of owned video.') 38 if (videoCaption.isOwned()) throw new Error('Cannot load remote caption of owned video.')
39 39
40 // Used to fetch the path 40 // Used to fetch the path
41 const video = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(videoId) 41 const video = await VideoModel.loadAndPopulateAccountAndServerAndTags(videoId)
42 if (!video) return undefined 42 if (!video) return undefined
43 43
44 const remoteStaticPath = videoCaption.getCaptionStaticPath() 44 const remoteStaticPath = videoCaption.getCaptionStaticPath()
diff --git a/server/lib/cache/videos-preview-cache.ts b/server/lib/cache/videos-preview-cache.ts
index 22b6d9cb0..a5d6f5b62 100644
--- a/server/lib/cache/videos-preview-cache.ts
+++ b/server/lib/cache/videos-preview-cache.ts
@@ -16,7 +16,7 @@ class VideosPreviewCache extends AbstractVideoStaticFileCache <string> {
16 } 16 }
17 17
18 async getFilePath (videoUUID: string) { 18 async getFilePath (videoUUID: string) {
19 const video = await VideoModel.loadByUUID(videoUUID) 19 const video = await VideoModel.loadByUUIDWithFile(videoUUID)
20 if (!video) return undefined 20 if (!video) return undefined
21 21
22 if (video.isOwned()) return join(CONFIG.STORAGE.PREVIEWS_DIR, video.getPreviewName()) 22 if (video.isOwned()) return join(CONFIG.STORAGE.PREVIEWS_DIR, video.getPreviewName())
@@ -25,7 +25,7 @@ class VideosPreviewCache extends AbstractVideoStaticFileCache <string> {
25 } 25 }
26 26
27 protected async loadRemoteFile (key: string) { 27 protected async loadRemoteFile (key: string) {
28 const video = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(key) 28 const video = await VideoModel.loadAndPopulateAccountAndServerAndTags(key)
29 if (!video) return undefined 29 if (!video) return undefined
30 30
31 if (video.isOwned()) throw new Error('Cannot load remote preview of owned video.') 31 if (video.isOwned()) throw new Error('Cannot load remote preview of owned video.')
diff --git a/server/lib/client-html.ts b/server/lib/client-html.ts
index b1088c096..fc013e0c3 100644
--- a/server/lib/client-html.ts
+++ b/server/lib/client-html.ts
@@ -39,10 +39,8 @@ export class ClientHtml {
39 let videoPromise: Bluebird<VideoModel> 39 let videoPromise: Bluebird<VideoModel>
40 40
41 // Let Angular application handle errors 41 // Let Angular application handle errors
42 if (validator.isUUID(videoId, 4)) { 42 if (validator.isInt(videoId) || validator.isUUID(videoId, 4)) {
43 videoPromise = VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(videoId) 43 videoPromise = VideoModel.loadAndPopulateAccountAndServerAndTags(videoId)
44 } else if (validator.isInt(videoId)) {
45 videoPromise = VideoModel.loadAndPopulateAccountAndServerAndTags(+videoId)
46 } else { 44 } else {
47 return ClientHtml.getIndexHTML(req, res) 45 return ClientHtml.getIndexHTML(req, res)
48 } 46 }
diff --git a/server/lib/job-queue/handlers/video-file.ts b/server/lib/job-queue/handlers/video-file.ts
index 2c9ca8e12..1463c93fc 100644
--- a/server/lib/job-queue/handlers/video-file.ts
+++ b/server/lib/job-queue/handlers/video-file.ts
@@ -26,7 +26,7 @@ async function processVideoFileImport (job: Bull.Job) {
26 const payload = job.data as VideoFileImportPayload 26 const payload = job.data as VideoFileImportPayload
27 logger.info('Processing video file import in job %d.', job.id) 27 logger.info('Processing video file import in job %d.', job.id)
28 28
29 const video = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(payload.videoUUID) 29 const video = await VideoModel.loadAndPopulateAccountAndServerAndTags(payload.videoUUID)
30 // No video, maybe deleted? 30 // No video, maybe deleted?
31 if (!video) { 31 if (!video) {
32 logger.info('Do not process job %d, video does not exist.', job.id) 32 logger.info('Do not process job %d, video does not exist.', job.id)
@@ -43,7 +43,7 @@ async function processVideoFile (job: Bull.Job) {
43 const payload = job.data as VideoFilePayload 43 const payload = job.data as VideoFilePayload
44 logger.info('Processing video file in job %d.', job.id) 44 logger.info('Processing video file in job %d.', job.id)
45 45
46 const video = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(payload.videoUUID) 46 const video = await VideoModel.loadAndPopulateAccountAndServerAndTags(payload.videoUUID)
47 // No video, maybe deleted? 47 // No video, maybe deleted?
48 if (!video) { 48 if (!video) {
49 logger.info('Do not process job %d, video does not exist.', job.id) 49 logger.info('Do not process job %d, video does not exist.', job.id)
@@ -69,7 +69,7 @@ async function onVideoFileTranscoderOrImportSuccess (video: VideoModel) {
69 69
70 return sequelizeTypescript.transaction(async t => { 70 return sequelizeTypescript.transaction(async t => {
71 // Maybe the video changed in database, refresh it 71 // Maybe the video changed in database, refresh it
72 let videoDatabase = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(video.uuid, t) 72 let videoDatabase = await VideoModel.loadAndPopulateAccountAndServerAndTags(video.uuid, t)
73 // Video does not exist anymore 73 // Video does not exist anymore
74 if (!videoDatabase) return undefined 74 if (!videoDatabase) return undefined
75 75
@@ -99,7 +99,7 @@ async function onVideoFileOptimizerSuccess (video: VideoModel, isNewVideo: boole
99 99
100 return sequelizeTypescript.transaction(async t => { 100 return sequelizeTypescript.transaction(async t => {
101 // Maybe the video changed in database, refresh it 101 // Maybe the video changed in database, refresh it
102 const videoDatabase = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(video.uuid, t) 102 const videoDatabase = await VideoModel.loadAndPopulateAccountAndServerAndTags(video.uuid, t)
103 // Video does not exist anymore 103 // Video does not exist anymore
104 if (!videoDatabase) return undefined 104 if (!videoDatabase) return undefined
105 105
diff --git a/server/lib/job-queue/handlers/video-import.ts b/server/lib/job-queue/handlers/video-import.ts
index ebcb2090c..9e14e57e6 100644
--- a/server/lib/job-queue/handlers/video-import.ts
+++ b/server/lib/job-queue/handlers/video-import.ts
@@ -183,7 +183,7 @@ async function processFile (downloader: () => Promise<string>, videoImport: Vide
183 const videoUpdated = await video.save({ transaction: t }) 183 const videoUpdated = await video.save({ transaction: t })
184 184
185 // Now we can federate the video (reload from database, we need more attributes) 185 // Now we can federate the video (reload from database, we need more attributes)
186 const videoForFederation = await VideoModel.loadByUUIDAndPopulateAccountAndServerAndTags(video.uuid, t) 186 const videoForFederation = await VideoModel.loadAndPopulateAccountAndServerAndTags(video.uuid, t)
187 await federateVideoIfNeeded(videoForFederation, true, t) 187 await federateVideoIfNeeded(videoForFederation, true, t)
188 188
189 // Update video import object 189 // Update video import object
diff --git a/server/middlewares/validators/users.ts b/server/middlewares/validators/users.ts
index d13c50c84..d3ba1ae23 100644
--- a/server/middlewares/validators/users.ts
+++ b/server/middlewares/validators/users.ts
@@ -172,7 +172,7 @@ const usersVideoRatingValidator = [
172 logger.debug('Checking usersVideoRating parameters', { parameters: req.params }) 172 logger.debug('Checking usersVideoRating parameters', { parameters: req.params })
173 173
174 if (areValidationErrors(req, res)) return 174 if (areValidationErrors(req, res)) return
175 if (!await isVideoExist(req.params.videoId, res)) return 175 if (!await isVideoExist(req.params.videoId, res, 'id')) return
176 176
177 return next() 177 return next()
178 } 178 }
diff --git a/server/middlewares/validators/video-captions.ts b/server/middlewares/validators/video-captions.ts
index 4f393ea84..51ffd7f3c 100644
--- a/server/middlewares/validators/video-captions.ts
+++ b/server/middlewares/validators/video-captions.ts
@@ -58,7 +58,7 @@ const listVideoCaptionsValidator = [
58 logger.debug('Checking listVideoCaptions parameters', { parameters: req.params }) 58 logger.debug('Checking listVideoCaptions parameters', { parameters: req.params })
59 59
60 if (areValidationErrors(req, res)) return 60 if (areValidationErrors(req, res)) return
61 if (!await isVideoExist(req.params.videoId, res)) return 61 if (!await isVideoExist(req.params.videoId, res, 'id')) return
62 62
63 return next() 63 return next()
64 } 64 }
diff --git a/server/middlewares/validators/video-comments.ts b/server/middlewares/validators/video-comments.ts
index 227bc1fca..4b15eed23 100644
--- a/server/middlewares/validators/video-comments.ts
+++ b/server/middlewares/validators/video-comments.ts
@@ -17,7 +17,7 @@ const listVideoCommentThreadsValidator = [
17 logger.debug('Checking listVideoCommentThreads parameters.', { parameters: req.params }) 17 logger.debug('Checking listVideoCommentThreads parameters.', { parameters: req.params })
18 18
19 if (areValidationErrors(req, res)) return 19 if (areValidationErrors(req, res)) return
20 if (!await isVideoExist(req.params.videoId, res)) return 20 if (!await isVideoExist(req.params.videoId, res, 'only-video')) return
21 21
22 return next() 22 return next()
23 } 23 }
@@ -31,7 +31,7 @@ const listVideoThreadCommentsValidator = [
31 logger.debug('Checking listVideoThreadComments parameters.', { parameters: req.params }) 31 logger.debug('Checking listVideoThreadComments parameters.', { parameters: req.params })
32 32
33 if (areValidationErrors(req, res)) return 33 if (areValidationErrors(req, res)) return
34 if (!await isVideoExist(req.params.videoId, res)) return 34 if (!await isVideoExist(req.params.videoId, res, 'only-video')) return
35 if (!await isVideoCommentThreadExist(req.params.threadId, res.locals.video, res)) return 35 if (!await isVideoCommentThreadExist(req.params.threadId, res.locals.video, res)) return
36 36
37 return next() 37 return next()
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index ce856aed2..c7cd2890c 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -91,6 +91,7 @@ import {
91 videoModelToFormattedDetailsJSON, 91 videoModelToFormattedDetailsJSON,
92 videoModelToFormattedJSON 92 videoModelToFormattedJSON
93} from './video-format-utils' 93} from './video-format-utils'
94import * as validator from 'validator'
94 95
95// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation 96// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
96const indexes: Sequelize.DefineIndexesOptions[] = [ 97const indexes: Sequelize.DefineIndexesOptions[] = [
@@ -466,6 +467,7 @@ type AvailableForListIDsOptions = {
466 required: false, 467 required: false,
467 include: [ 468 include: [
468 { 469 {
470 attributes: [ 'fileUrl' ],
469 model: () => VideoRedundancyModel.unscoped(), 471 model: () => VideoRedundancyModel.unscoped(),
470 required: false 472 required: false
471 } 473 }
@@ -1062,44 +1064,34 @@ export class VideoModel extends Model<VideoModel> {
1062 return VideoModel.getAvailableForApi(query, queryOptions) 1064 return VideoModel.getAvailableForApi(query, queryOptions)
1063 } 1065 }
1064 1066
1065 static load (id: number, t?: Sequelize.Transaction) { 1067 static load (id: number | string, t?: Sequelize.Transaction) {
1066 return VideoModel.findById(id, { transaction: t }) 1068 const where = VideoModel.buildWhereIdOrUUID(id)
1067 } 1069 const options = {
1068 1070 where,
1069 static loadWithFile (id: number, t?: Sequelize.Transaction, logging?: boolean) { 1071 transaction: t
1070 return VideoModel.scope(ScopeNames.WITH_FILES)
1071 .findById(id, { transaction: t, logging })
1072 }
1073
1074 static loadByUrlAndPopulateAccount (url: string, t?: Sequelize.Transaction) {
1075 const query: IFindOptions<VideoModel> = {
1076 where: {
1077 url
1078 }
1079 } 1072 }
1080 1073
1081 if (t !== undefined) query.transaction = t 1074 return VideoModel.findOne(options)
1082
1083 return VideoModel.scope([ ScopeNames.WITH_ACCOUNT_DETAILS, ScopeNames.WITH_FILES ]).findOne(query)
1084 } 1075 }
1085 1076
1086 static loadAndPopulateAccountAndServerAndTags (id: number) { 1077 static loadOnlyId (id: number | string, t?: Sequelize.Transaction) {
1078 const where = VideoModel.buildWhereIdOrUUID(id)
1079
1087 const options = { 1080 const options = {
1088 order: [ [ 'Tags', 'name', 'ASC' ] ] 1081 attributes: [ 'id' ],
1082 where,
1083 transaction: t
1089 } 1084 }
1090 1085
1091 return VideoModel 1086 return VideoModel.findOne(options)
1092 .scope([ 1087 }
1093 ScopeNames.WITH_TAGS, 1088
1094 ScopeNames.WITH_BLACKLISTED, 1089 static loadWithFile (id: number, t?: Sequelize.Transaction, logging?: boolean) {
1095 ScopeNames.WITH_FILES, 1090 return VideoModel.scope(ScopeNames.WITH_FILES)
1096 ScopeNames.WITH_ACCOUNT_DETAILS, 1091 .findById(id, { transaction: t, logging })
1097 ScopeNames.WITH_SCHEDULED_UPDATE
1098 ])
1099 .findById(id, options)
1100 } 1092 }
1101 1093
1102 static loadByUUID (uuid: string) { 1094 static loadByUUIDWithFile (uuid: string) {
1103 const options = { 1095 const options = {
1104 where: { 1096 where: {
1105 uuid 1097 uuid
@@ -1111,12 +1103,24 @@ export class VideoModel extends Model<VideoModel> {
1111 .findOne(options) 1103 .findOne(options)
1112 } 1104 }
1113 1105
1114 static loadByUUIDAndPopulateAccountAndServerAndTags (uuid: string, t?: Sequelize.Transaction) { 1106 static loadByUrlAndPopulateAccount (url: string, t?: Sequelize.Transaction) {
1107 const query: IFindOptions<VideoModel> = {
1108 where: {
1109 url
1110 }
1111 }
1112
1113 if (t !== undefined) query.transaction = t
1114
1115 return VideoModel.scope([ ScopeNames.WITH_ACCOUNT_DETAILS, ScopeNames.WITH_FILES ]).findOne(query)
1116 }
1117
1118 static loadAndPopulateAccountAndServerAndTags (id: number | string, t?: Sequelize.Transaction) {
1119 const where = VideoModel.buildWhereIdOrUUID(id)
1120
1115 const options = { 1121 const options = {
1116 order: [ [ 'Tags', 'name', 'ASC' ] ], 1122 order: [ [ 'Tags', 'name', 'ASC' ] ],
1117 where: { 1123 where,
1118 uuid
1119 },
1120 transaction: t 1124 transaction: t
1121 } 1125 }
1122 1126
@@ -1277,6 +1281,10 @@ export class VideoModel extends Model<VideoModel> {
1277 return VIDEO_STATES[ id ] || 'Unknown' 1281 return VIDEO_STATES[ id ] || 'Unknown'
1278 } 1282 }
1279 1283
1284 static buildWhereIdOrUUID (id: number | string) {
1285 return validator.isInt('' + id) ? { id } : { uuid: id }
1286 }
1287
1280 getOriginalFile () { 1288 getOriginalFile () {
1281 if (Array.isArray(this.VideoFiles) === false) return undefined 1289 if (Array.isArray(this.VideoFiles) === false) return undefined
1282 1290