From 17bb45388ec319d288a1b8387c6c199fe2f6b64f Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 11 Jun 2021 10:59:27 +0200 Subject: Optimize rows parsing --- .../shared/abstract-videos-model-query-builder.ts | 46 ++-- .../sql/shared/abstract-videos-query-builder.ts | 4 +- server/models/video/sql/shared/video-attributes.ts | 253 -------------------- .../video/sql/shared/video-file-query-builder.ts | 4 +- .../models/video/sql/shared/video-model-builder.ts | 224 ++++++++++-------- server/models/video/sql/shared/video-tables.ts | 254 +++++++++++++++++++++ .../video/sql/video-model-get-query-builder.ts | 8 +- .../video/sql/videos-model-list-query-builder.ts | 4 +- 8 files changed, 415 insertions(+), 382 deletions(-) delete mode 100644 server/models/video/sql/shared/video-attributes.ts create mode 100644 server/models/video/sql/shared/video-tables.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 8ed207eea..8eff59db0 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,6 +1,6 @@ import validator from 'validator' import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder' -import { VideoAttributes } from './video-attributes' +import { VideoTables } from './video-tables' /** * @@ -13,12 +13,12 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder protected joins: string[] = [] protected where: string - protected videoAttributes: VideoAttributes + protected tables: VideoTables constructor (protected readonly mode: 'list' | 'get') { super() - this.videoAttributes = new VideoAttributes(this.mode) + this.tables = new VideoTables(this.mode) } protected buildSelect () { @@ -44,7 +44,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoChannel', this.videoAttributes.getChannelAttributes()), + ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()), ...this.buildActorInclude('VideoChannel->Actor'), ...this.buildAvatarInclude('VideoChannel->Actor->Avatar'), ...this.buildServerInclude('VideoChannel->Actor->Server') @@ -66,7 +66,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoChannel->Account', this.videoAttributes.getAccountAttributes()), + ...this.buildAttributesObject('VideoChannel->Account', this.tables.getAccountAttributes()), ...this.buildActorInclude('VideoChannel->Account->Actor'), ...this.buildAvatarInclude('VideoChannel->Account->Actor->Avatar'), ...this.buildServerInclude('VideoChannel->Account->Actor->Server') @@ -79,7 +79,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('Thumbnails', this.videoAttributes.getThumbnailAttributes()) + ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes()) } } @@ -90,7 +90,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoFiles', this.videoAttributes.getFileAttributes()) + ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes()) } } @@ -107,8 +107,8 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoStreamingPlaylists', this.videoAttributes.getStreamingPlaylistAttributes()), - ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.videoAttributes.getFileAttributes()) + ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()), + ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes()) } } @@ -123,7 +123,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('userVideoHistory', this.videoAttributes.getUserHistoryAttributes()) + ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes()) } } @@ -138,7 +138,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoPlaylistElement', this.videoAttributes.getPlaylistAttributes()) + ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes()) } } @@ -153,8 +153,8 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('Tags', this.videoAttributes.getTagAttributes()), - ...this.buildAttributesObject('Tags->VideoTagModel', this.videoAttributes.getVideoTagAttributes()) + ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()), + ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes()) } } @@ -166,7 +166,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoBlacklist', this.videoAttributes.getBlacklistedAttributes()) + ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes()) } } @@ -178,7 +178,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('ScheduleVideoUpdate', this.videoAttributes.getScheduleUpdateAttributes()) + ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes()) } } @@ -190,7 +190,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoLive', this.videoAttributes.getLiveAttributes()) + ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes()) } } @@ -205,8 +205,8 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('Trackers', this.videoAttributes.getTrackerAttributes()), - ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.videoAttributes.getVideoTrackerAttributes()) + ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()), + ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes()) } } @@ -219,7 +219,7 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.videoAttributes.getRedundancyAttributes()) + ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes()) } } @@ -232,20 +232,20 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder this.attributes = { ...this.attributes, - ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.videoAttributes.getRedundancyAttributes()) + ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes()) } } protected buildActorInclude (prefixKey: string) { - return this.buildAttributesObject(prefixKey, this.videoAttributes.getActorAttributes()) + return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes()) } protected buildAvatarInclude (prefixKey: string) { - return this.buildAttributesObject(prefixKey, this.videoAttributes.getAvatarAttributes()) + return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes()) } protected buildServerInclude (prefixKey: string) { - return this.buildAttributesObject(prefixKey, this.videoAttributes.getServerAttributes()) + return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes()) } protected buildAttributesObject (prefixKey: string, attributeKeys: 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 c1bbeb71e..7e67fa34f 100644 --- a/server/models/video/sql/shared/abstract-videos-query-builder.ts +++ b/server/models/video/sql/shared/abstract-videos-query-builder.ts @@ -13,14 +13,14 @@ export class AbstractVideosQueryBuilder { protected query: string protected replacements: any = {} - protected runQuery (transaction?: Transaction, nest?: boolean) { + protected runQuery (transaction?: Transaction) { logger.debug('Running videos query.', { query: this.query, replacements: this.replacements }) const options = { transaction, replacements: this.replacements, type: QueryTypes.SELECT as QueryTypes.SELECT, - nest + next: false } return this.sequelize.query(this.query, options) diff --git a/server/models/video/sql/shared/video-attributes.ts b/server/models/video/sql/shared/video-attributes.ts deleted file mode 100644 index e21b33c73..000000000 --- a/server/models/video/sql/shared/video-attributes.ts +++ /dev/null @@ -1,253 +0,0 @@ - -/** - * - * Class to build video attributes we want to fetch from the database - * - */ -export class VideoAttributes { - - constructor (readonly mode: 'get' | 'list') { - - } - - getChannelAttributes () { - let attributeKeys = [ - 'id', - 'name', - 'description', - 'actorId' - ] - - if (this.mode === 'get') { - attributeKeys = attributeKeys.concat([ - 'support', - 'createdAt', - 'updatedAt' - ]) - } - - return attributeKeys - } - - getAccountAttributes () { - let attributeKeys = [ 'id', 'name', 'actorId' ] - - if (this.mode === 'get') { - attributeKeys = attributeKeys.concat([ - 'description', - 'createdAt', - 'updatedAt' - ]) - } - - return attributeKeys - } - - getThumbnailAttributes () { - let attributeKeys = [ 'id', 'type', 'filename' ] - - if (this.mode === 'get') { - attributeKeys = attributeKeys.concat([ - 'height', - 'width', - 'fileUrl', - 'automaticallyGenerated', - 'videoId', - 'videoPlaylistId', - 'createdAt', - 'updatedAt' - ]) - } - - return attributeKeys - } - - getFileAttributes () { - return [ - 'id', - 'createdAt', - 'updatedAt', - 'resolution', - 'size', - 'extname', - 'filename', - 'fileUrl', - 'torrentFilename', - 'torrentUrl', - 'infoHash', - 'fps', - 'metadataUrl', - 'videoStreamingPlaylistId', - 'videoId' - ] - } - - getStreamingPlaylistAttributes () { - let playlistKeys = [ 'id', 'playlistUrl', 'type' ] - - if (this.mode === 'get') { - playlistKeys = playlistKeys.concat([ - 'p2pMediaLoaderInfohashes', - 'p2pMediaLoaderPeerVersion', - 'segmentsSha256Url', - 'videoId', - 'createdAt', - 'updatedAt' - ]) - } - - return playlistKeys - } - - getUserHistoryAttributes () { - return [ 'id', 'currentTime' ] - } - - getPlaylistAttributes () { - return [ - 'createdAt', - 'updatedAt', - 'url', - 'position', - 'startTimestamp', - 'stopTimestamp', - 'videoPlaylistId' - ] - } - - getTagAttributes () { - return [ 'id', 'name' ] - } - - getVideoTagAttributes () { - return [ 'videoId', 'tagId', 'createdAt', 'updatedAt' ] - } - - getBlacklistedAttributes () { - return [ 'id', 'reason', 'unfederated' ] - } - - getScheduleUpdateAttributes () { - return [ - 'id', - 'updateAt', - 'privacy', - 'videoId', - 'createdAt', - 'updatedAt' - ] - } - - getLiveAttributes () { - return [ - 'id', - 'streamKey', - 'saveReplay', - 'permanentLive', - 'videoId', - 'createdAt', - 'updatedAt' - ] - } - - getTrackerAttributes () { - return [ 'id', 'url' ] - } - - getVideoTrackerAttributes () { - return [ - 'videoId', - 'trackerId', - 'createdAt', - 'updatedAt' - ] - } - - getRedundancyAttributes () { - return [ 'id', 'fileUrl' ] - } - - getActorAttributes () { - let attributeKeys = [ - 'id', - 'preferredUsername', - 'url', - 'serverId', - 'avatarId' - ] - - if (this.mode === 'get') { - attributeKeys = attributeKeys.concat([ - 'type', - 'followersCount', - 'followingCount', - 'inboxUrl', - 'outboxUrl', - 'sharedInboxUrl', - 'followersUrl', - 'followingUrl', - 'remoteCreatedAt', - 'createdAt', - 'updatedAt' - ]) - } - - return attributeKeys - } - - getAvatarAttributes () { - let attributeKeys = [ - 'id', - 'filename', - 'fileUrl', - 'onDisk', - 'createdAt', - 'updatedAt' - ] - - if (this.mode === 'get') { - attributeKeys = attributeKeys.concat([ - 'height', - 'width', - 'type' - ]) - } - - return attributeKeys - } - - getServerAttributes () { - return [ 'id', 'host' ] - } - - getVideoAttributes () { - return [ - 'id', - 'uuid', - 'name', - 'category', - 'licence', - 'language', - 'privacy', - 'nsfw', - 'description', - 'support', - 'duration', - 'views', - 'likes', - 'dislikes', - 'remote', - 'isLive', - 'url', - 'commentsEnabled', - 'downloadEnabled', - 'waitTranscoding', - 'state', - 'publishedAt', - 'originallyPublishedAt', - 'channelId', - 'createdAt', - 'updatedAt' - ] - } -} 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 29b11a298..ad47905c6 100644 --- a/server/models/video/sql/shared/video-file-query-builder.ts +++ b/server/models/video/sql/shared/video-file-query-builder.ts @@ -19,13 +19,13 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { queryWebTorrentVideos (options: BuildVideoGetQueryOptions) { this.buildWebtorrentFilesQuery(options) - return this.runQuery(options.transaction, true) + return this.runQuery(options.transaction) } queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) { this.buildVideoStreamingPlaylistFilesQuery(options) - return this.runQuery(options.transaction, true) + return this.runQuery(options.transaction) } private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) { diff --git a/server/models/video/sql/shared/video-model-builder.ts b/server/models/video/sql/shared/video-model-builder.ts index 627ea6443..2a60dab04 100644 --- a/server/models/video/sql/shared/video-model-builder.ts +++ b/server/models/video/sql/shared/video-model-builder.ts @@ -1,4 +1,5 @@ -import { pick } from 'lodash' + +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' @@ -15,7 +16,9 @@ import { VideoChannelModel } from '../../video-channel' import { VideoFileModel } from '../../video-file' import { VideoLiveModel } from '../../video-live' import { VideoStreamingPlaylistModel } from '../../video-streaming-playlist' -import { VideoAttributes } from './video-attributes' +import { VideoTables } from './video-tables' + +type SQLRow = { [id: string]: string | number } /** * @@ -28,12 +31,12 @@ export class VideoModelBuilder { private videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } private videoFileMemo: { [ id: number ]: VideoFileModel } - private thumbnailsDone: Set - private historyDone: Set - private blacklistDone: Set - private liveDone: Set - private redundancyDone: Set - private scheduleVideoUpdateDone: Set + private thumbnailsDone: Set + private historyDone: Set + private blacklistDone: Set + private liveDone: Set + private redundancyDone: Set + private scheduleVideoUpdateDone: Set private trackersDone: Set private tagsDone: Set @@ -44,12 +47,12 @@ export class VideoModelBuilder { constructor ( readonly mode: 'get' | 'list', - readonly videoAttributes: VideoAttributes + readonly tables: VideoTables ) { } - buildVideosFromRows (rows: any[], rowsWebtorrentFiles?: any[], rowsStreamingPlaylist?: any[]) { + buildVideosFromRows (rows: SQLRow[], rowsWebTorrentFiles?: SQLRow[], rowsStreamingPlaylist?: SQLRow[]) { this.reinit() for (const row of rows) { @@ -60,7 +63,7 @@ export class VideoModelBuilder { this.setUserHistory(row, videoModel) this.addThumbnail(row, videoModel) - if (!rowsWebtorrentFiles) { + if (!rowsWebTorrentFiles) { this.addWebTorrentFile(row, videoModel) } @@ -75,30 +78,11 @@ export class VideoModelBuilder { this.setBlacklisted(row, videoModel) this.setScheduleVideoUpdate(row, videoModel) this.setLive(row, videoModel) - - if (!rowsWebtorrentFiles && row.VideoFiles.id) { - this.addRedundancy(row.VideoFiles.RedundancyVideos, this.videoFileMemo[row.VideoFiles.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]) - } + this.grabSeparateWebTorrentFiles(rowsWebTorrentFiles) + this.grabSeparateStreamingPlaylistFiles(rowsStreamingPlaylist) return this.videos } @@ -121,21 +105,45 @@ export class VideoModelBuilder { this.videos = [] } - private buildVideo (row: any) { + private grabSeparateWebTorrentFiles (rowsWebTorrentFiles?: SQLRow[]) { + if (!rowsWebTorrentFiles) return + + 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']]) + } + } + + private grabSeparateStreamingPlaylistFiles (rowsStreamingPlaylist?: SQLRow[]) { + if (!rowsStreamingPlaylist) return + + 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']] + ) + } + } + + private buildVideo (row: SQLRow) { if (this.videosMemo[row.id]) return // Build Channel - const channel = row.VideoChannel - const channelModel = new VideoChannelModel(pick(channel, this.videoAttributes.getChannelAttributes()), this.buildOpts) - channelModel.Actor = this.buildActor(channel.Actor) + const channelModel = new VideoChannelModel(this.grab(row, this.tables.getChannelAttributes(), 'VideoChannel'), this.buildOpts) + channelModel.Actor = this.buildActor(row, 'VideoChannel') - const account = row.VideoChannel.Account - const accountModel = new AccountModel(pick(account, this.videoAttributes.getAccountAttributes()), this.buildOpts) - accountModel.Actor = this.buildActor(account.Actor) + 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(pick(row, this.videoAttributes.getVideoAttributes()), this.buildOpts) + const videoModel = new VideoModel(this.grab(row, this.tables.getVideoAttributes(), ''), this.buildOpts) videoModel.VideoChannel = channelModel this.videosMemo[row.id] = videoModel @@ -151,143 +159,167 @@ export class VideoModelBuilder { this.videos.push(videoModel) } - private buildActor (rowActor: any) { - const avatarModel = rowActor.Avatar.id !== null - ? new ActorImageModel(pick(rowActor.Avatar, this.videoAttributes.getAvatarAttributes()), this.buildOpts) + private buildActor (row: SQLRow, prefix: string) { + const actorPrefix = `${prefix}.Actor` + const avatarPrefix = `${actorPrefix}.Avatar` + const serverPrefix = `${actorPrefix}.Server` + + const avatarModel = row[`${avatarPrefix}.id`] !== null + ? new ActorImageModel(this.grab(row, this.tables.getAvatarAttributes(), avatarPrefix), this.buildOpts) : null - const serverModel = rowActor.Server.id !== null - ? new ServerModel(pick(rowActor.Server, this.videoAttributes.getServerAttributes()), this.buildOpts) + const serverModel = row[`${serverPrefix}.id`] !== null + ? new ServerModel(this.grab(row, this.tables.getServerAttributes(), serverPrefix), this.buildOpts) : null - const actorModel = new ActorModel(pick(rowActor, this.videoAttributes.getActorAttributes()), this.buildOpts) + const actorModel = new ActorModel(this.grab(row, this.tables.getActorAttributes(), actorPrefix), this.buildOpts) actorModel.Avatar = avatarModel actorModel.Server = serverModel return actorModel } - private setUserHistory (row: any, videoModel: VideoModel) { - if (!row.userVideoHistory?.id || this.historyDone.has(row.userVideoHistory.id)) return + private setUserHistory (row: SQLRow, videoModel: VideoModel) { + const id = row['userVideoHistory.id'] + if (!id || this.historyDone.has(id)) return - const attributes = pick(row.userVideoHistory, this.videoAttributes.getUserHistoryAttributes()) + const attributes = this.grab(row, this.tables.getUserHistoryAttributes(), 'userVideoHistory') const historyModel = new UserVideoHistoryModel(attributes, this.buildOpts) videoModel.UserVideoHistories.push(historyModel) - this.historyDone.add(row.userVideoHistory.id) + this.historyDone.add(id) } - private addThumbnail (row: any, videoModel: VideoModel) { - if (!row.Thumbnails?.id || this.thumbnailsDone.has(row.Thumbnails.id)) return + private addThumbnail (row: SQLRow, videoModel: VideoModel) { + const id = row['Thumbnails.id'] + if (!id || this.thumbnailsDone.has(id)) return - const attributes = pick(row.Thumbnails, this.videoAttributes.getThumbnailAttributes()) + const attributes = this.grab(row, this.tables.getThumbnailAttributes(), 'Thumbnails') const thumbnailModel = new ThumbnailModel(attributes, this.buildOpts) videoModel.Thumbnails.push(thumbnailModel) - this.thumbnailsDone.add(row.Thumbnails.id) + this.thumbnailsDone.add(id) } - private addWebTorrentFile (row: any, videoModel: VideoModel) { - if (!row.VideoFiles?.id || this.videoFileMemo[row.VideoFiles.id]) return + private addWebTorrentFile (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoFiles.id'] + if (!id || this.videoFileMemo[id]) return - const attributes = pick(row.VideoFiles, this.videoAttributes.getFileAttributes()) + const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoFiles') const videoFileModel = new VideoFileModel(attributes, this.buildOpts) videoModel.VideoFiles.push(videoFileModel) - this.videoFileMemo[row.VideoFiles.id] = videoFileModel + this.videoFileMemo[id] = videoFileModel } - private addStreamingPlaylist (row: any, videoModel: VideoModel) { - if (!row.VideoStreamingPlaylists?.id || this.videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id]) return + private addStreamingPlaylist (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoStreamingPlaylists.id'] + if (!id || this.videoStreamingPlaylistMemo[id]) return - const attributes = pick(row.VideoStreamingPlaylists, this.videoAttributes.getStreamingPlaylistAttributes()) + const attributes = this.grab(row, this.tables.getStreamingPlaylistAttributes(), 'VideoStreamingPlaylists') const streamingPlaylist = new VideoStreamingPlaylistModel(attributes, this.buildOpts) streamingPlaylist.VideoFiles = [] videoModel.VideoStreamingPlaylists.push(streamingPlaylist) - this.videoStreamingPlaylistMemo[streamingPlaylist.id] = streamingPlaylist + this.videoStreamingPlaylistMemo[id] = streamingPlaylist } - private addStreamingPlaylistFile (row: any) { - if (!row.VideoStreamingPlaylists?.VideoFiles?.id || this.videoFileMemo[row.VideoStreamingPlaylists.VideoFiles.id]) return + private addStreamingPlaylistFile (row: SQLRow) { + const id = row['VideoStreamingPlaylists.VideoFiles.id'] + if (!id || this.videoFileMemo[id]) return - const streamingPlaylist = this.videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id] + const streamingPlaylist = this.videoStreamingPlaylistMemo[row['VideoStreamingPlaylists.id']] - const attributes = pick(row.VideoStreamingPlaylists.VideoFiles, this.videoAttributes.getFileAttributes()) + const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoStreamingPlaylists.VideoFiles') const videoFileModel = new VideoFileModel(attributes, this.buildOpts) streamingPlaylist.VideoFiles.push(videoFileModel) - this.videoFileMemo[row.VideoStreamingPlaylists.VideoFiles.id] = videoFileModel + this.videoFileMemo[id] = videoFileModel } - private addRedundancy (redundancyRow: any, to: VideoFileModel | VideoStreamingPlaylistModel) { + private addRedundancy (row: SQLRow, prefix: string, to: VideoFileModel | VideoStreamingPlaylistModel) { if (!to.RedundancyVideos) to.RedundancyVideos = [] - if (!redundancyRow?.id || this.redundancyDone.has(redundancyRow.id)) return + const redundancyPrefix = `${prefix}.RedundancyVideos` + const id = row[`${redundancyPrefix}.id`] + + if (!id || this.redundancyDone.has(id)) return - const attributes = pick(redundancyRow, this.videoAttributes.getRedundancyAttributes()) + const attributes = this.grab(row, this.tables.getRedundancyAttributes(), redundancyPrefix) const redundancyModel = new VideoRedundancyModel(attributes, this.buildOpts) to.RedundancyVideos.push(redundancyModel) - this.redundancyDone.add(redundancyRow.id) + this.redundancyDone.add(id) } - private addTag (row: any, videoModel: VideoModel) { - if (!row.Tags?.name) return - const association = row.Tags.VideoTagModel + private addTag (row: SQLRow, videoModel: VideoModel) { + if (!row['Tags.name']) return - const key = `${association.videoId}-${association.tagId}` + const key = `${row['Tags.VideoTagModel.videoId']}-${row['Tags.VideoTagModel.tagId']}` if (this.tagsDone.has(key)) return - const attributes = pick(row.Tags, this.videoAttributes.getTagAttributes()) + const attributes = this.grab(row, this.tables.getTagAttributes(), 'Tags') const tagModel = new TagModel(attributes, this.buildOpts) videoModel.Tags.push(tagModel) this.tagsDone.add(key) } - private addTracker (row: any, videoModel: VideoModel) { - if (!row.Trackers?.id) return - const association = row.Trackers.VideoTrackerModel + private addTracker (row: SQLRow, videoModel: VideoModel) { + if (!row['Trackers.id']) return - const key = `${association.videoId}-${association.trackerId}` + const key = `${row['Trackers.VideoTrackerModel.videoId']}-${row['Trackers.VideoTrackerModel.trackerId']}` if (this.trackersDone.has(key)) return - const attributes = pick(row.Trackers, this.videoAttributes.getTrackerAttributes()) + const attributes = this.grab(row, this.tables.getTrackerAttributes(), 'Trackers') const trackerModel = new TrackerModel(attributes, this.buildOpts) videoModel.Trackers.push(trackerModel) this.trackersDone.add(key) } - private setBlacklisted (row: any, videoModel: VideoModel) { - if (!row.VideoBlacklist?.id) return - if (this.blacklistDone.has(row.VideoBlacklist.id)) return + private setBlacklisted (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoBlacklist.id'] + if (!id || this.blacklistDone.has(id)) return - const attributes = pick(row.VideoBlacklist, this.videoAttributes.getBlacklistedAttributes()) + const attributes = this.grab(row, this.tables.getBlacklistedAttributes(), 'VideoBlacklist') videoModel.VideoBlacklist = new VideoBlacklistModel(attributes, this.buildOpts) - this.blacklistDone.add(row.VideoBlacklist.id) + this.blacklistDone.add(id) } - private setScheduleVideoUpdate (row: any, videoModel: VideoModel) { - if (!row.ScheduleVideoUpdate?.id) return - if (this.scheduleVideoUpdateDone.has(row.ScheduleVideoUpdate.id)) return + private setScheduleVideoUpdate (row: SQLRow, videoModel: VideoModel) { + const id = row['ScheduleVideoUpdate.id'] + if (!id || this.scheduleVideoUpdateDone.has(id)) return - const attributes = pick(row.ScheduleVideoUpdate, this.videoAttributes.getScheduleUpdateAttributes()) + const attributes = this.grab(row, this.tables.getScheduleUpdateAttributes(), 'ScheduleVideoUpdate') videoModel.ScheduleVideoUpdate = new ScheduleVideoUpdateModel(attributes, this.buildOpts) - this.scheduleVideoUpdateDone.add(row.ScheduleVideoUpdate.id) + this.scheduleVideoUpdateDone.add(id) } - private setLive (row: any, videoModel: VideoModel) { - if (!row.VideoLive?.id) return - if (this.liveDone.has(row.VideoLive.id)) return + private setLive (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoLive.id'] + if (!id || this.liveDone.has(id)) return - const attributes = pick(row.VideoLive, this.videoAttributes.getLiveAttributes()) + const attributes = this.grab(row, this.tables.getLiveAttributes(), 'VideoLive') videoModel.VideoLive = new VideoLiveModel(attributes, this.buildOpts) - this.liveDone.add(row.ScheduleVideoUpdate.id) + this.liveDone.add(id) + } + + private grab (row: SQLRow, attributes: string[], prefix: string) { + const result: { [ id: string ]: string | number } = {} + + for (const a of attributes) { + const key = prefix + ? prefix + '.' + a + : a + + result[a] = row[key] + } + + return result } } diff --git a/server/models/video/sql/shared/video-tables.ts b/server/models/video/sql/shared/video-tables.ts new file mode 100644 index 000000000..fddf1210c --- /dev/null +++ b/server/models/video/sql/shared/video-tables.ts @@ -0,0 +1,254 @@ + +/** + * + * Class to build video attributes/join names we want to fetch from the database + * + */ +export class VideoTables { + + constructor (readonly mode: 'get' | 'list') { + + } + + getChannelAttributes () { + let attributeKeys = [ + 'id', + 'name', + 'description', + 'actorId' + ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'support', + 'createdAt', + 'updatedAt' + ]) + } + + return attributeKeys + } + + getAccountAttributes () { + let attributeKeys = [ 'id', 'name', 'actorId' ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'description', + 'createdAt', + 'updatedAt' + ]) + } + + return attributeKeys + } + + getThumbnailAttributes () { + let attributeKeys = [ 'id', 'type', 'filename' ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'height', + 'width', + 'fileUrl', + 'automaticallyGenerated', + 'videoId', + 'videoPlaylistId', + 'createdAt', + 'updatedAt' + ]) + } + + return attributeKeys + } + + getFileAttributes () { + return [ + 'id', + 'createdAt', + 'updatedAt', + 'resolution', + 'size', + 'extname', + 'filename', + 'fileUrl', + 'torrentFilename', + 'torrentUrl', + 'infoHash', + 'fps', + 'metadataUrl', + 'videoStreamingPlaylistId', + 'videoId' + ] + } + + getStreamingPlaylistAttributes () { + let playlistKeys = [ 'id', 'playlistUrl', 'type' ] + + if (this.mode === 'get') { + playlistKeys = playlistKeys.concat([ + 'p2pMediaLoaderInfohashes', + 'p2pMediaLoaderPeerVersion', + 'segmentsSha256Url', + 'videoId', + 'createdAt', + 'updatedAt' + ]) + } + + return playlistKeys + } + + getUserHistoryAttributes () { + return [ 'id', 'currentTime' ] + } + + getPlaylistAttributes () { + return [ + 'createdAt', + 'updatedAt', + 'url', + 'position', + 'startTimestamp', + 'stopTimestamp', + 'videoPlaylistId' + ] + } + + getTagAttributes () { + return [ 'id', 'name' ] + } + + getVideoTagAttributes () { + return [ 'videoId', 'tagId', 'createdAt', 'updatedAt' ] + } + + getBlacklistedAttributes () { + return [ 'id', 'reason', 'unfederated' ] + } + + getScheduleUpdateAttributes () { + return [ + 'id', + 'updateAt', + 'privacy', + 'videoId', + 'createdAt', + 'updatedAt' + ] + } + + getLiveAttributes () { + return [ + 'id', + 'streamKey', + 'saveReplay', + 'permanentLive', + 'videoId', + 'createdAt', + 'updatedAt' + ] + } + + getTrackerAttributes () { + return [ 'id', 'url' ] + } + + getVideoTrackerAttributes () { + return [ + 'videoId', + 'trackerId', + 'createdAt', + 'updatedAt' + ] + } + + getRedundancyAttributes () { + return [ 'id', 'fileUrl' ] + } + + getActorAttributes () { + let attributeKeys = [ + 'id', + 'preferredUsername', + 'url', + 'serverId', + 'avatarId' + ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'type', + 'followersCount', + 'followingCount', + 'inboxUrl', + 'outboxUrl', + 'sharedInboxUrl', + 'followersUrl', + 'followingUrl', + 'remoteCreatedAt', + 'createdAt', + 'updatedAt' + ]) + } + + return attributeKeys + } + + getAvatarAttributes () { + let attributeKeys = [ + 'id', + 'filename', + 'type', + 'fileUrl', + 'onDisk', + 'createdAt', + 'updatedAt' + ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'height', + 'width', + 'type' + ]) + } + + return attributeKeys + } + + getServerAttributes () { + return [ 'id', 'host' ] + } + + getVideoAttributes () { + return [ + 'id', + 'uuid', + 'name', + 'category', + 'licence', + 'language', + 'privacy', + 'nsfw', + 'description', + 'support', + 'duration', + 'views', + 'likes', + 'dislikes', + 'remote', + 'isLive', + 'url', + 'commentsEnabled', + 'downloadEnabled', + 'waitTranscoding', + 'state', + 'publishedAt', + 'originallyPublishedAt', + 'channelId', + 'createdAt', + 'updatedAt' + ] + } +} 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 1a921d802..892639076 100644 --- a/server/models/video/sql/video-model-get-query-builder.ts +++ b/server/models/video/sql/video-model-get-query-builder.ts @@ -1,8 +1,8 @@ import { Sequelize, Transaction } from 'sequelize' 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' +import { VideoTables } from './shared/video-tables' /** * @@ -29,7 +29,7 @@ export class VideosModelGetQueryBuilder { this.webtorrentFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) this.streamingPlaylistFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) - this.videoModelBuilder = new VideoModelBuilder('get', new VideoAttributes('get')) + this.videoModelBuilder = new VideoModelBuilder('get', new VideoTables('get')) } async queryVideos (options: BuildVideoGetQueryOptions) { @@ -64,7 +64,7 @@ export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuild queryVideos (options: BuildVideoGetQueryOptions) { this.buildMainGetQuery(options) - return this.runQuery(options.transaction, true) + return this.runQuery(options.transaction) } private buildMainGetQuery (options: BuildVideoGetQueryOptions) { @@ -102,6 +102,6 @@ export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuild const order = 'ORDER BY "Tags"."name" ASC' const from = `SELECT * FROM "video" ${this.where} LIMIT 1` - return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins.join(' ')} ${this.where} ${order}` + return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins.join(' ')} ${order}` } } 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 acb76d80a..459f542a4 100644 --- a/server/models/video/sql/videos-model-list-query-builder.ts +++ b/server/models/video/sql/videos-model-list-query-builder.ts @@ -21,14 +21,14 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder constructor (protected readonly sequelize: Sequelize) { super('list') - this.videoModelBuilder = new VideoModelBuilder(this.mode, this.videoAttributes) + this.videoModelBuilder = new VideoModelBuilder(this.mode, this.tables) } queryVideos (options: BuildVideosListQueryOptions) { this.buildInnerQuery(options) this.buildListQueryFromIdsQuery(options) - return this.runQuery(undefined, true).then(rows => this.videoModelBuilder.buildVideosFromRows(rows)) + return this.runQuery(undefined).then(rows => this.videoModelBuilder.buildVideosFromRows(rows)) } private buildInnerQuery (options: BuildVideosListQueryOptions) { -- cgit v1.2.3