From 1d43c3a613c72d69f7360fee9e5bfe6f662d62f7 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 10 Jun 2021 16:57:13 +0200 Subject: Use separate queries for video files --- .../shared/abstract-videos-model-query-builder.ts | 65 ++++++++++++---- .../sql/shared/abstract-videos-query-builder.ts | 6 ++ server/models/video/sql/shared/video-attributes.ts | 6 ++ .../video/sql/shared/video-file-query-builder.ts | 66 ++++++++++++++++ .../models/video/sql/shared/video-model-builder.ts | 37 +++++++-- .../video/sql/video-model-get-query-builder.ts | 87 ++++++++++++++-------- .../video/sql/videos-id-list-query-builder.ts | 6 ++ .../video/sql/videos-model-list-query-builder.ts | 14 +++- 8 files changed, 231 insertions(+), 56 deletions(-) create mode 100644 server/models/video/sql/shared/video-file-query-builder.ts (limited to 'server/models/video/sql') diff --git a/server/models/video/sql/shared/abstract-videos-model-query-builder.ts b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts index bdf926cbe..8ed207eea 100644 --- a/server/models/video/sql/shared/abstract-videos-model-query-builder.ts +++ b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts @@ -1,19 +1,24 @@ +import validator from 'validator' import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder' import { VideoAttributes } from './video-attributes' -import { VideoModelBuilder } from './video-model-builder' + +/** + * + * Abstract builder to create SQL query and fetch video models + * + */ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder { protected attributes: { [key: string]: string } = {} protected joins: string[] = [] + protected where: string protected videoAttributes: VideoAttributes - protected videoModelBuilder: VideoModelBuilder - constructor (private readonly mode: 'list' | 'get') { + constructor (protected readonly mode: 'list' | 'get') { super() this.videoAttributes = new VideoAttributes(this.mode) - this.videoModelBuilder = new VideoModelBuilder(this.mode, this.videoAttributes) } protected buildSelect () { @@ -78,21 +83,30 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder } } - protected includeFiles () { - this.joins.push( - 'LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"', + protected includeWebtorrentFiles (required: boolean) { + const joinType = required ? 'INNER' : 'LEFT' + this.joins.push(joinType + ' JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"') + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoFiles', this.videoAttributes.getFileAttributes()) + } + } + + protected includeStreamingPlaylistFiles (required: boolean) { + const joinType = required ? 'INNER' : 'LEFT' - 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"', + this.joins.push( + joinType + ' JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"', - 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' + + joinType + ' JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' + 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"' ) this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoFiles', this.videoAttributes.getFileAttributes()), - ...this.buildAttributesObject('VideoStreamingPlaylists', this.videoAttributes.getStreamingPlaylistAttributes()), ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.videoAttributes.getFileAttributes()) } @@ -196,11 +210,8 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder } } - protected includeRedundancies () { + protected includeWebTorrentRedundancies () { this.joins.push( - 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' + - 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"', - 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' + '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"' ) @@ -208,7 +219,19 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.videoAttributes.getRedundancyAttributes()), + ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.videoAttributes.getRedundancyAttributes()) + } + } + + protected includeStreamingPlaylistRedundancies () { + this.joins.push( + 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' + + 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"' + ) + + this.attributes = { + ...this.attributes, + ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.videoAttributes.getRedundancyAttributes()) } } @@ -236,4 +259,14 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder return result } + + protected whereId (id: string | number) { + if (validator.isInt('' + id)) { + this.where = 'WHERE "video".id = :videoId' + } else { + this.where = 'WHERE uuid = :videoId' + } + + this.replacements.videoId = id + } } diff --git a/server/models/video/sql/shared/abstract-videos-query-builder.ts b/server/models/video/sql/shared/abstract-videos-query-builder.ts index 01694e691..c1bbeb71e 100644 --- a/server/models/video/sql/shared/abstract-videos-query-builder.ts +++ b/server/models/video/sql/shared/abstract-videos-query-builder.ts @@ -1,6 +1,12 @@ import { QueryTypes, Sequelize, Transaction } from 'sequelize' import { logger } from '@server/helpers/logger' +/** + * + * Abstact builder to run video SQL queries + * + */ + export class AbstractVideosQueryBuilder { protected sequelize: Sequelize diff --git a/server/models/video/sql/shared/video-attributes.ts b/server/models/video/sql/shared/video-attributes.ts index 1a1650dc7..e21b33c73 100644 --- a/server/models/video/sql/shared/video-attributes.ts +++ b/server/models/video/sql/shared/video-attributes.ts @@ -1,3 +1,9 @@ + +/** + * + * Class to build video attributes we want to fetch from the database + * + */ export class VideoAttributes { constructor (readonly mode: 'get' | 'list') { diff --git a/server/models/video/sql/shared/video-file-query-builder.ts b/server/models/video/sql/shared/video-file-query-builder.ts new file mode 100644 index 000000000..29b11a298 --- /dev/null +++ b/server/models/video/sql/shared/video-file-query-builder.ts @@ -0,0 +1,66 @@ +import { Sequelize } from 'sequelize' +import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder' +import { AbstractVideosModelQueryBuilder } from './abstract-videos-model-query-builder' + +/** + * + * Fetch files (webtorrent and streaming playlist) according to a video + * + */ + +export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { + protected attributes: { [key: string]: string } + protected joins: string[] = [] + + constructor (protected readonly sequelize: Sequelize) { + super('get') + } + + queryWebTorrentVideos (options: BuildVideoGetQueryOptions) { + this.buildWebtorrentFilesQuery(options) + + return this.runQuery(options.transaction, true) + } + + queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) { + this.buildVideoStreamingPlaylistFilesQuery(options) + + return this.runQuery(options.transaction, true) + } + + private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) { + this.attributes = { + '"video"."id"': '' + } + + this.includeWebtorrentFiles(true) + + if (options.forGetAPI === true) { + this.includeWebTorrentRedundancies() + } + + this.whereId(options.id) + + this.query = this.buildQuery() + } + + private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) { + this.attributes = { + '"video"."id"': '' + } + + this.includeStreamingPlaylistFiles(true) + + if (options.forGetAPI === true) { + this.includeStreamingPlaylistRedundancies() + } + + this.whereId(options.id) + + this.query = this.buildQuery() + } + + private buildQuery () { + return `${this.buildSelect()} FROM "video" ${this.joins.join(' ')} ${this.where}` + } +} diff --git a/server/models/video/sql/shared/video-model-builder.ts b/server/models/video/sql/shared/video-model-builder.ts index 9719f6d2e..627ea6443 100644 --- a/server/models/video/sql/shared/video-model-builder.ts +++ b/server/models/video/sql/shared/video-model-builder.ts @@ -17,6 +17,12 @@ import { VideoLiveModel } from '../../video-live' import { VideoStreamingPlaylistModel } from '../../video-streaming-playlist' import { VideoAttributes } from './video-attributes' +/** + * + * Build video models from SQL rows + * + */ + export class VideoModelBuilder { private videosMemo: { [ id: number ]: VideoModel } private videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } @@ -43,7 +49,7 @@ export class VideoModelBuilder { } - buildVideosFromRows (rows: any[]) { + buildVideosFromRows (rows: any[], rowsWebtorrentFiles?: any[], rowsStreamingPlaylist?: any[]) { this.reinit() for (const row of rows) { @@ -53,10 +59,15 @@ export class VideoModelBuilder { this.setUserHistory(row, videoModel) this.addThumbnail(row, videoModel) - this.addWebTorrentFile(row, videoModel) - this.addStreamingPlaylist(row, videoModel) - this.addStreamingPlaylistFile(row) + if (!rowsWebtorrentFiles) { + this.addWebTorrentFile(row, videoModel) + } + + if (!rowsStreamingPlaylist) { + this.addStreamingPlaylist(row, videoModel) + this.addStreamingPlaylistFile(row) + } if (this.mode === 'get') { this.addTag(row, videoModel) @@ -65,16 +76,30 @@ export class VideoModelBuilder { this.setScheduleVideoUpdate(row, videoModel) this.setLive(row, videoModel) - if (row.VideoFiles.id) { + if (!rowsWebtorrentFiles && row.VideoFiles.id) { this.addRedundancy(row.VideoFiles.RedundancyVideos, this.videoFileMemo[row.VideoFiles.id]) } - if (row.VideoStreamingPlaylists.id) { + if (!rowsStreamingPlaylist && row.VideoStreamingPlaylists.id) { this.addRedundancy(row.VideoStreamingPlaylists.RedundancyVideos, this.videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]) } } } + for (const row of rowsWebtorrentFiles || []) { + const videoModel = this.videosMemo[row.id] + this.addWebTorrentFile(row, videoModel) + this.addRedundancy(row.VideoFiles.RedundancyVideos, this.videoFileMemo[row.VideoFiles.id]) + } + + for (const row of rowsStreamingPlaylist || []) { + const videoModel = this.videosMemo[row.id] + + this.addStreamingPlaylist(row, videoModel) + this.addStreamingPlaylistFile(row) + this.addRedundancy(row.VideoStreamingPlaylists.RedundancyVideos, this.videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]) + } + return this.videos } diff --git a/server/models/video/sql/video-model-get-query-builder.ts b/server/models/video/sql/video-model-get-query-builder.ts index 0a3723e63..1a921d802 100644 --- a/server/models/video/sql/video-model-get-query-builder.ts +++ b/server/models/video/sql/video-model-get-query-builder.ts @@ -1,6 +1,14 @@ import { Sequelize, Transaction } from 'sequelize' -import validator from 'validator' import { AbstractVideosModelQueryBuilder } from './shared/abstract-videos-model-query-builder' +import { VideoAttributes } from './shared/video-attributes' +import { VideoFileQueryBuilder } from './shared/video-file-query-builder' +import { VideoModelBuilder } from './shared/video-model-builder' + +/** + * + * Build a GET SQL query, fetch rows and create the video model + * + */ export type BuildVideoGetQueryOptions = { id: number | string @@ -9,31 +17,57 @@ export type BuildVideoGetQueryOptions = { forGetAPI?: boolean } -export class VideosModelGetQueryBuilder extends AbstractVideosModelQueryBuilder { +export class VideosModelGetQueryBuilder { + videoQueryBuilder: VideosModelGetQuerySubBuilder + webtorrentFilesQueryBuilder: VideoFileQueryBuilder + streamingPlaylistFilesQueryBuilder: VideoFileQueryBuilder + + private readonly videoModelBuilder: VideoModelBuilder + + constructor (protected readonly sequelize: Sequelize) { + this.videoQueryBuilder = new VideosModelGetQuerySubBuilder(sequelize) + this.webtorrentFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) + this.streamingPlaylistFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) + + this.videoModelBuilder = new VideoModelBuilder('get', new VideoAttributes('get')) + } + + async queryVideos (options: BuildVideoGetQueryOptions) { + const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([ + this.videoQueryBuilder.queryVideos(options), + this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(options), + this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(options) + ]) + + const videos = this.videoModelBuilder.buildVideosFromRows(videoRows, webtorrentFilesRows, streamingPlaylistFilesRows) + + if (videos.length > 1) { + throw new Error('Video results is more than ') + } + + if (videos.length === 0) return null + return videos[0] + } +} + +export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuilder { protected attributes: { [key: string]: string } protected joins: string[] = [] - protected where: string + + protected webtorrentFilesQuery: string + protected streamingPlaylistFilesQuery: string constructor (protected readonly sequelize: Sequelize) { super('get') } queryVideos (options: BuildVideoGetQueryOptions) { - this.buildGetQuery(options) - - return this.runQuery(options.transaction, true).then(rows => { - const videos = this.videoModelBuilder.buildVideosFromRows(rows) - - if (videos.length > 1) { - throw new Error('Video results is more than ') - } + this.buildMainGetQuery(options) - if (videos.length === 0) return null - return videos[0] - }) + return this.runQuery(options.transaction, true) } - private buildGetQuery (options: BuildVideoGetQueryOptions) { + private buildMainGetQuery (options: BuildVideoGetQueryOptions) { this.attributes = { '"video".*': '' } @@ -45,8 +79,6 @@ export class VideosModelGetQueryBuilder extends AbstractVideosModelQueryBuilder this.includeThumbnails() - this.includeFiles() - this.includeBlacklisted() this.includeScheduleUpdate() @@ -59,28 +91,17 @@ export class VideosModelGetQueryBuilder extends AbstractVideosModelQueryBuilder if (options.forGetAPI === true) { this.includeTrackers() - this.includeRedundancies() } this.whereId(options.id) - const select = this.buildSelect() - const order = this.buildOrder() - - this.query = `${select} FROM "video" ${this.joins.join(' ')} ${this.where} ${order}` + this.query = this.buildQuery() } - private whereId (id: string | number) { - if (validator.isInt('' + id)) { - this.where = 'WHERE "video".id = :videoId' - } else { - this.where = 'WHERE uuid = :videoId' - } - - this.replacements.videoId = id - } + private buildQuery () { + const order = 'ORDER BY "Tags"."name" ASC' + const from = `SELECT * FROM "video" ${this.where} LIMIT 1` - private buildOrder () { - return 'ORDER BY "Tags"."name" ASC' + return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins.join(' ')} ${this.where} ${order}` } } diff --git a/server/models/video/sql/videos-id-list-query-builder.ts b/server/models/video/sql/videos-id-list-query-builder.ts index 6e0d97d9e..30b251f0f 100644 --- a/server/models/video/sql/videos-id-list-query-builder.ts +++ b/server/models/video/sql/videos-id-list-query-builder.ts @@ -6,6 +6,12 @@ import { MUserAccountId, MUserId } from '@server/types/models' import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models' import { AbstractVideosQueryBuilder } from './shared/abstract-videos-query-builder' +/** + * + * Build videos list SQL query to fetch rows + * + */ + export type BuildVideosListQueryOptions = { attributes?: string[] diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts index 38b9c91d0..acb76d80a 100644 --- a/server/models/video/sql/videos-model-list-query-builder.ts +++ b/server/models/video/sql/videos-model-list-query-builder.ts @@ -1,7 +1,14 @@ import { Sequelize } from 'sequelize' import { AbstractVideosModelQueryBuilder } from './shared/abstract-videos-model-query-builder' +import { VideoModelBuilder } from './shared/video-model-builder' import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder' +/** + * + * Build videos list SQL query and create video models + * + */ + export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder { protected attributes: { [key: string]: string } protected joins: string[] = [] @@ -9,8 +16,12 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder private innerQuery: string private innerSort: string + private readonly videoModelBuilder: VideoModelBuilder + constructor (protected readonly sequelize: Sequelize) { super('list') + + this.videoModelBuilder = new VideoModelBuilder(this.mode, this.videoAttributes) } queryVideos (options: BuildVideosListQueryOptions) { @@ -41,7 +52,8 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder this.includeThumbnails() if (options.withFiles) { - this.includeFiles() + this.includeWebtorrentFiles(false) + this.includeStreamingPlaylistFiles(false) } if (options.user) { -- cgit v1.2.3