aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2022-06-27 09:34:26 +0200
committerChocobozzz <me@florianbigard.com>2022-06-27 09:34:26 +0200
commit7fb45bdacb6d4399f56f575301b414e49efbdf92 (patch)
treee5b4c1760fd34642dbf73c23912d8fc773ec7c75 /server/models
parentc53853ca1b8e32aea5259d436d3d284b9d178919 (diff)
downloadPeerTube-7fb45bdacb6d4399f56f575301b414e49efbdf92.tar.gz
PeerTube-7fb45bdacb6d4399f56f575301b414e49efbdf92.tar.zst
PeerTube-7fb45bdacb6d4399f56f575301b414e49efbdf92.zip
Optimize feeds query
Diffstat (limited to 'server/models')
-rw-r--r--server/models/video/sql/video/shared/abstract-video-query-builder.ts7
-rw-r--r--server/models/video/sql/video/shared/video-file-query-builder.ts30
-rw-r--r--server/models/video/sql/video/video-model-get-query-builder.ts15
-rw-r--r--server/models/video/sql/video/videos-model-list-query-builder.ts41
4 files changed, 69 insertions, 24 deletions
diff --git a/server/models/video/sql/video/shared/abstract-video-query-builder.ts b/server/models/video/sql/video/shared/abstract-video-query-builder.ts
index b79d20ade..3c74b0ea6 100644
--- a/server/models/video/sql/video/shared/abstract-video-query-builder.ts
+++ b/server/models/video/sql/video/shared/abstract-video-query-builder.ts
@@ -313,7 +313,12 @@ export class AbstractVideoQueryBuilder extends AbstractRunQuery {
313 return result 313 return result
314 } 314 }
315 315
316 protected whereId (options: { id?: string | number, url?: string }) { 316 protected whereId (options: { ids?: number[], id?: string | number, url?: string }) {
317 if (options.ids) {
318 this.where = `WHERE "video"."id" IN (${createSafeIn(this.sequelize, options.ids)})`
319 return
320 }
321
317 if (options.url) { 322 if (options.url) {
318 this.where = 'WHERE "video"."url" = :videoUrl' 323 this.where = 'WHERE "video"."url" = :videoUrl'
319 this.replacements.videoUrl = options.url 324 this.replacements.videoUrl = options.url
diff --git a/server/models/video/sql/video/shared/video-file-query-builder.ts b/server/models/video/sql/video/shared/video-file-query-builder.ts
index 50c12f627..cc53a4860 100644
--- a/server/models/video/sql/video/shared/video-file-query-builder.ts
+++ b/server/models/video/sql/video/shared/video-file-query-builder.ts
@@ -1,7 +1,17 @@
1import { Sequelize } from 'sequelize' 1import { Sequelize, Transaction } from 'sequelize'
2import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder'
3import { AbstractVideoQueryBuilder } from './abstract-video-query-builder' 2import { AbstractVideoQueryBuilder } from './abstract-video-query-builder'
4 3
4export type FileQueryOptions = {
5 id?: string | number
6 url?: string
7
8 includeRedundancy: boolean
9
10 transaction?: Transaction
11
12 logging?: boolean
13}
14
5/** 15/**
6 * 16 *
7 * Fetch files (webtorrent and streaming playlist) according to a video 17 * Fetch files (webtorrent and streaming playlist) according to a video
@@ -15,26 +25,26 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder {
15 super(sequelize, 'get') 25 super(sequelize, 'get')
16 } 26 }
17 27
18 queryWebTorrentVideos (options: BuildVideoGetQueryOptions) { 28 queryWebTorrentVideos (options: FileQueryOptions) {
19 this.buildWebtorrentFilesQuery(options) 29 this.buildWebtorrentFilesQuery(options)
20 30
21 return this.runQuery(options) 31 return this.runQuery(options)
22 } 32 }
23 33
24 queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) { 34 queryStreamingPlaylistVideos (options: FileQueryOptions) {
25 this.buildVideoStreamingPlaylistFilesQuery(options) 35 this.buildVideoStreamingPlaylistFilesQuery(options)
26 36
27 return this.runQuery(options) 37 return this.runQuery(options)
28 } 38 }
29 39
30 private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) { 40 private buildWebtorrentFilesQuery (options: FileQueryOptions) {
31 this.attributes = { 41 this.attributes = {
32 '"video"."id"': '' 42 '"video"."id"': ''
33 } 43 }
34 44
35 this.includeWebtorrentFiles() 45 this.includeWebtorrentFiles()
36 46
37 if (this.shouldIncludeRedundancies(options)) { 47 if (options.includeRedundancy) {
38 this.includeWebTorrentRedundancies() 48 this.includeWebTorrentRedundancies()
39 } 49 }
40 50
@@ -43,14 +53,14 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder {
43 this.query = this.buildQuery() 53 this.query = this.buildQuery()
44 } 54 }
45 55
46 private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) { 56 private buildVideoStreamingPlaylistFilesQuery (options: FileQueryOptions) {
47 this.attributes = { 57 this.attributes = {
48 '"video"."id"': '' 58 '"video"."id"': ''
49 } 59 }
50 60
51 this.includeStreamingPlaylistFiles() 61 this.includeStreamingPlaylistFiles()
52 62
53 if (this.shouldIncludeRedundancies(options)) { 63 if (options.includeRedundancy) {
54 this.includeStreamingPlaylistRedundancies() 64 this.includeStreamingPlaylistRedundancies()
55 } 65 }
56 66
@@ -62,8 +72,4 @@ export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder {
62 private buildQuery () { 72 private buildQuery () {
63 return `${this.buildSelect()} FROM "video" ${this.joins} ${this.where}` 73 return `${this.buildSelect()} FROM "video" ${this.joins} ${this.where}`
64 } 74 }
65
66 private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) {
67 return options.type === 'api'
68 }
69} 75}
diff --git a/server/models/video/sql/video/video-model-get-query-builder.ts b/server/models/video/sql/video/video-model-get-query-builder.ts
index b0879c9ac..32e5c4ff7 100644
--- a/server/models/video/sql/video/video-model-get-query-builder.ts
+++ b/server/models/video/sql/video/video-model-get-query-builder.ts
@@ -1,3 +1,4 @@
1import { pick } from 'lodash'
1import { Sequelize, Transaction } from 'sequelize' 2import { Sequelize, Transaction } from 'sequelize'
2import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' 3import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder'
3import { VideoFileQueryBuilder } from './shared/video-file-query-builder' 4import { VideoFileQueryBuilder } from './shared/video-file-query-builder'
@@ -50,15 +51,21 @@ export class VideoModelGetQueryBuilder {
50 } 51 }
51 52
52 async queryVideo (options: BuildVideoGetQueryOptions) { 53 async queryVideo (options: BuildVideoGetQueryOptions) {
54 const fileQueryOptions = {
55 ...pick(options, [ 'id', 'url', 'transaction', 'logging' ]),
56
57 includeRedundancy: this.shouldIncludeRedundancies(options)
58 }
59
53 const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([ 60 const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([
54 this.videoQueryBuilder.queryVideos(options), 61 this.videoQueryBuilder.queryVideos(options),
55 62
56 VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) 63 VideoModelGetQueryBuilder.videoFilesInclude.has(options.type)
57 ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(options) 64 ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(fileQueryOptions)
58 : Promise.resolve(undefined), 65 : Promise.resolve(undefined),
59 66
60 VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) 67 VideoModelGetQueryBuilder.videoFilesInclude.has(options.type)
61 ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(options) 68 ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(fileQueryOptions)
62 : Promise.resolve(undefined) 69 : Promise.resolve(undefined)
63 ]) 70 ])
64 71
@@ -76,6 +83,10 @@ export class VideoModelGetQueryBuilder {
76 83
77 return videos[0] 84 return videos[0]
78 } 85 }
86
87 private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) {
88 return options.type === 'api'
89 }
79} 90}
80 91
81export class VideosModelGetQuerySubBuilder extends AbstractVideoQueryBuilder { 92export class VideosModelGetQuerySubBuilder extends AbstractVideoQueryBuilder {
diff --git a/server/models/video/sql/video/videos-model-list-query-builder.ts b/server/models/video/sql/video/videos-model-list-query-builder.ts
index 2a4afc389..4fe6bc321 100644
--- a/server/models/video/sql/video/videos-model-list-query-builder.ts
+++ b/server/models/video/sql/video/videos-model-list-query-builder.ts
@@ -1,6 +1,8 @@
1import { VideoInclude } from '@shared/models' 1import { pick } from 'lodash'
2import { Sequelize } from 'sequelize' 2import { Sequelize } from 'sequelize'
3import { VideoInclude } from '@shared/models'
3import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' 4import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder'
5import { VideoFileQueryBuilder } from './shared/video-file-query-builder'
4import { VideoModelBuilder } from './shared/video-model-builder' 6import { VideoModelBuilder } from './shared/video-model-builder'
5import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder' 7import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder'
6 8
@@ -16,20 +18,46 @@ export class VideosModelListQueryBuilder extends AbstractVideoQueryBuilder {
16 private innerQuery: string 18 private innerQuery: string
17 private innerSort: string 19 private innerSort: string
18 20
21 webtorrentFilesQueryBuilder: VideoFileQueryBuilder
22 streamingPlaylistFilesQueryBuilder: VideoFileQueryBuilder
23
19 private readonly videoModelBuilder: VideoModelBuilder 24 private readonly videoModelBuilder: VideoModelBuilder
20 25
21 constructor (protected readonly sequelize: Sequelize) { 26 constructor (protected readonly sequelize: Sequelize) {
22 super(sequelize, 'list') 27 super(sequelize, 'list')
23 28
24 this.videoModelBuilder = new VideoModelBuilder(this.mode, this.tables) 29 this.videoModelBuilder = new VideoModelBuilder(this.mode, this.tables)
30 this.webtorrentFilesQueryBuilder = new VideoFileQueryBuilder(sequelize)
31 this.streamingPlaylistFilesQueryBuilder = new VideoFileQueryBuilder(sequelize)
25 } 32 }
26 33
27 queryVideos (options: BuildVideosListQueryOptions) { 34 async queryVideos (options: BuildVideosListQueryOptions) {
28 this.buildInnerQuery(options) 35 this.buildInnerQuery(options)
29 this.buildMainQuery(options) 36 this.buildMainQuery(options)
30 37
31 return this.runQuery() 38 const rows = await this.runQuery()
32 .then(rows => this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include })) 39
40 if (options.include & VideoInclude.FILES) {
41 const videoIds = Array.from(new Set(rows.map(r => r.id)))
42
43 if (videoIds.length !== 0) {
44 const fileQueryOptions = {
45 ...pick(options, [ 'transaction', 'logging' ]),
46
47 ids: videoIds,
48 includeRedundancy: false
49 }
50
51 const [ rowsWebTorrentFiles, rowsStreamingPlaylist ] = await Promise.all([
52 this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(fileQueryOptions),
53 this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(fileQueryOptions)
54 ])
55
56 return this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include, rowsStreamingPlaylist, rowsWebTorrentFiles })
57 }
58 }
59
60 return this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include })
33 } 61 }
34 62
35 private buildInnerQuery (options: BuildVideosListQueryOptions) { 63 private buildInnerQuery (options: BuildVideosListQueryOptions) {
@@ -52,11 +80,6 @@ export class VideosModelListQueryBuilder extends AbstractVideoQueryBuilder {
52 this.includeAccounts() 80 this.includeAccounts()
53 this.includeThumbnails() 81 this.includeThumbnails()
54 82
55 if (options.include & VideoInclude.FILES) {
56 this.includeWebtorrentFiles()
57 this.includeStreamingPlaylistFiles()
58 }
59
60 if (options.user) { 83 if (options.user) {
61 this.includeUserHistory(options.user.id) 84 this.includeUserHistory(options.user.id)
62 } 85 }