From 71d4af1efc810f853e1a0d986bf758c201692594 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 11 Jun 2021 14:09:33 +0200 Subject: Use raw SQL for most of video queries --- .../shared/abstract-videos-model-query-builder.ts | 24 +++++++++++++-- .../sql/shared/abstract-videos-query-builder.ts | 9 +++--- .../video/sql/shared/video-file-query-builder.ts | 16 ++++++---- .../models/video/sql/shared/video-model-builder.ts | 36 +++++++++++++--------- server/models/video/sql/shared/video-tables.ts | 8 +++++ 5 files changed, 65 insertions(+), 28 deletions(-) (limited to 'server/models/video/sql/shared') 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 65df8d914..d959cb5d0 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 @@ -80,6 +80,18 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder } } + protected includeOwnerUser () { + this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"') + this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"') + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()), + ...this.buildAttributesObject('VideoChannel->Account', this.tables.getUserAccountAttributes()) + } + } + protected includeThumbnails () { this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"') @@ -269,14 +281,20 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder return result } - protected whereId (id: string | number) { - if (validator.isInt('' + id)) { + protected whereId (options: { id?: string | number, url?: string }) { + if (options.url) { + this.where = 'WHERE "video"."url" = :videoUrl' + this.replacements.videoUrl = options.url + return + } + + if (validator.isInt('' + options.id)) { this.where = 'WHERE "video".id = :videoId' } else { this.where = 'WHERE uuid = :videoId' } - this.replacements.videoId = id + this.replacements.videoId = options.id } protected addJoin (join: string) { 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 7e67fa34f..10699317a 100644 --- a/server/models/video/sql/shared/abstract-videos-query-builder.ts +++ b/server/models/video/sql/shared/abstract-videos-query-builder.ts @@ -13,16 +13,17 @@ export class AbstractVideosQueryBuilder { protected query: string protected replacements: any = {} - protected runQuery (transaction?: Transaction) { + protected runQuery (options: { transaction?: Transaction, logging?: boolean } = {}) { logger.debug('Running videos query.', { query: this.query, replacements: this.replacements }) - const options = { - transaction, + const queryOptions = { + transaction: options.transaction, + logging: options.logging, replacements: this.replacements, type: QueryTypes.SELECT as QueryTypes.SELECT, next: false } - return this.sequelize.query(this.query, options) + return this.sequelize.query(this.query, queryOptions) } } diff --git a/server/models/video/sql/shared/video-file-query-builder.ts b/server/models/video/sql/shared/video-file-query-builder.ts index 7d822f8fa..a62fa64f8 100644 --- a/server/models/video/sql/shared/video-file-query-builder.ts +++ b/server/models/video/sql/shared/video-file-query-builder.ts @@ -18,13 +18,13 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { queryWebTorrentVideos (options: BuildVideoGetQueryOptions) { this.buildWebtorrentFilesQuery(options) - return this.runQuery(options.transaction) + return this.runQuery(options) } queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) { this.buildVideoStreamingPlaylistFilesQuery(options) - return this.runQuery(options.transaction) + return this.runQuery(options) } private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) { @@ -34,11 +34,11 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { this.includeWebtorrentFiles(true) - if (options.forGetAPI === true) { + if (this.shouldIncludeRedundancies(options)) { this.includeWebTorrentRedundancies() } - this.whereId(options.id) + this.whereId(options) this.query = this.buildQuery() } @@ -50,11 +50,11 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { this.includeStreamingPlaylistFiles(true) - if (options.forGetAPI === true) { + if (this.shouldIncludeRedundancies(options)) { this.includeStreamingPlaylistRedundancies() } - this.whereId(options.id) + this.whereId(options) this.query = this.buildQuery() } @@ -62,4 +62,8 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { private buildQuery () { return `${this.buildSelect()} FROM "video" ${this.joins} ${this.where}` } + + private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) { + return options.type === 'api' + } } diff --git a/server/models/video/sql/shared/video-model-builder.ts b/server/models/video/sql/shared/video-model-builder.ts index 2a60dab04..467a9378a 100644 --- a/server/models/video/sql/shared/video-model-builder.ts +++ b/server/models/video/sql/shared/video-model-builder.ts @@ -1,5 +1,4 @@ -import { logger } from '@server/helpers/logger' import { AccountModel } from '@server/models/account/account' import { ActorModel } from '@server/models/actor/actor' import { ActorImageModel } from '@server/models/actor/actor-image' @@ -56,7 +55,7 @@ export class VideoModelBuilder { this.reinit() for (const row of rows) { - this.buildVideo(row) + this.buildVideoAndAccount(row) const videoModel = this.videosMemo[row.id] @@ -131,22 +130,10 @@ export class VideoModelBuilder { } } - private buildVideo (row: SQLRow) { + private buildVideoAndAccount (row: SQLRow) { if (this.videosMemo[row.id]) return - // Build Channel - const channelModel = new VideoChannelModel(this.grab(row, this.tables.getChannelAttributes(), 'VideoChannel'), this.buildOpts) - channelModel.Actor = this.buildActor(row, 'VideoChannel') - - const accountModel = new AccountModel(this.grab(row, this.tables.getAccountAttributes(), 'VideoChannel.Account'), this.buildOpts) - accountModel.Actor = this.buildActor(row, 'VideoChannel.Account') - - channelModel.Account = accountModel - const videoModel = new VideoModel(this.grab(row, this.tables.getVideoAttributes(), ''), this.buildOpts) - videoModel.VideoChannel = channelModel - - this.videosMemo[row.id] = videoModel videoModel.UserVideoHistories = [] videoModel.Thumbnails = [] @@ -155,10 +142,29 @@ export class VideoModelBuilder { videoModel.Tags = [] videoModel.Trackers = [] + this.buildAccount(row, videoModel) + + this.videosMemo[row.id] = videoModel + // Keep rows order this.videos.push(videoModel) } + private buildAccount (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoChannel.Account.id'] + if (!id) return + + const channelModel = new VideoChannelModel(this.grab(row, this.tables.getChannelAttributes(), 'VideoChannel'), this.buildOpts) + channelModel.Actor = this.buildActor(row, 'VideoChannel') + + const accountModel = new AccountModel(this.grab(row, this.tables.getAccountAttributes(), 'VideoChannel.Account'), this.buildOpts) + accountModel.Actor = this.buildActor(row, 'VideoChannel.Account') + + channelModel.Account = accountModel + + videoModel.VideoChannel = channelModel + } + private buildActor (row: SQLRow, prefix: string) { const actorPrefix = `${prefix}.Actor` const avatarPrefix = `${actorPrefix}.Avatar` diff --git a/server/models/video/sql/shared/video-tables.ts b/server/models/video/sql/shared/video-tables.ts index fddf1210c..52929fa5e 100644 --- a/server/models/video/sql/shared/video-tables.ts +++ b/server/models/video/sql/shared/video-tables.ts @@ -10,6 +10,10 @@ export class VideoTables { } + getChannelAttributesForUser () { + return [ 'id', 'accountId' ] + } + getChannelAttributes () { let attributeKeys = [ 'id', @@ -29,6 +33,10 @@ export class VideoTables { return attributeKeys } + getUserAccountAttributes () { + return [ 'id', 'userId' ] + } + getAccountAttributes () { let attributeKeys = [ 'id', 'name', 'actorId' ] -- cgit v1.2.3