From d0800f7661f13fabe7bb6f4aa0ea50764f106405 Mon Sep 17 00:00:00 2001 From: kontrollanten <6680299+kontrollanten@users.noreply.github.com> Date: Mon, 28 Feb 2022 08:34:43 +0100 Subject: Implement avatar miniatures (#4639) * client: remove unused file * refactor(client/my-actor-avatar): size from input Read size from component input instead of scss, to make it possible to use smaller avatar images when implemented. * implement avatar miniatures close #4560 * fix(test): max file size * fix(search-index): normalize res acc to avatarMini * refactor avatars to an array * client/search: resize channel avatar to 120 * refactor(client/videos): remove unused function * client(actor-avatar): set default size * fix tests and avatars full result When findOne is used only an array containting one avatar is returned. * update migration version and version notations * server/search: harmonize normalizing * Cleanup avatar miniature PR Co-authored-by: Chocobozzz --- .../models/video/sql/shared/abstract-run-query.ts | 26 - .../sql/shared/abstract-video-query-builder.ts | 328 ---------- .../video/sql/shared/video-file-query-builder.ts | 69 -- .../models/video/sql/shared/video-model-builder.ts | 387 ------------ .../video/sql/shared/video-table-attributes.ts | 269 -------- .../video/sql/video-model-get-query-builder.ts | 178 ------ server/models/video/sql/video/index.ts | 3 + .../video/sql/video/shared/abstract-run-query.ts | 26 + .../video/shared/abstract-video-query-builder.ts | 331 ++++++++++ .../sql/video/shared/video-file-query-builder.ts | 69 ++ .../video/sql/video/shared/video-model-builder.ts | 406 ++++++++++++ .../sql/video/shared/video-table-attributes.ts | 269 ++++++++ .../sql/video/video-model-get-query-builder.ts | 178 ++++++ .../sql/video/videos-id-list-query-builder.ts | 697 +++++++++++++++++++++ .../sql/video/videos-model-list-query-builder.ts | 80 +++ .../video/sql/videos-id-list-query-builder.ts | 697 --------------------- .../video/sql/videos-model-list-query-builder.ts | 80 --- server/models/video/video-channel.ts | 209 +++--- server/models/video/video-comment.ts | 102 +-- server/models/video/video-import.ts | 11 +- server/models/video/video-playlist-element.ts | 36 +- server/models/video/video-playlist.ts | 98 ++- server/models/video/video-share.ts | 5 +- server/models/video/video.ts | 18 +- 24 files changed, 2351 insertions(+), 2221 deletions(-) delete mode 100644 server/models/video/sql/shared/abstract-run-query.ts delete mode 100644 server/models/video/sql/shared/abstract-video-query-builder.ts delete mode 100644 server/models/video/sql/shared/video-file-query-builder.ts delete mode 100644 server/models/video/sql/shared/video-model-builder.ts delete mode 100644 server/models/video/sql/shared/video-table-attributes.ts delete mode 100644 server/models/video/sql/video-model-get-query-builder.ts create mode 100644 server/models/video/sql/video/index.ts create mode 100644 server/models/video/sql/video/shared/abstract-run-query.ts create mode 100644 server/models/video/sql/video/shared/abstract-video-query-builder.ts create mode 100644 server/models/video/sql/video/shared/video-file-query-builder.ts create mode 100644 server/models/video/sql/video/shared/video-model-builder.ts create mode 100644 server/models/video/sql/video/shared/video-table-attributes.ts create mode 100644 server/models/video/sql/video/video-model-get-query-builder.ts create mode 100644 server/models/video/sql/video/videos-id-list-query-builder.ts create mode 100644 server/models/video/sql/video/videos-model-list-query-builder.ts delete mode 100644 server/models/video/sql/videos-id-list-query-builder.ts delete mode 100644 server/models/video/sql/videos-model-list-query-builder.ts (limited to 'server/models/video') diff --git a/server/models/video/sql/shared/abstract-run-query.ts b/server/models/video/sql/shared/abstract-run-query.ts deleted file mode 100644 index 8e7a7642d..000000000 --- a/server/models/video/sql/shared/abstract-run-query.ts +++ /dev/null @@ -1,26 +0,0 @@ -import { QueryTypes, Sequelize, Transaction } from 'sequelize' - -/** - * - * Abstact builder to run video SQL queries - * - */ - -export class AbstractRunQuery { - protected sequelize: Sequelize - - protected query: string - protected replacements: any = {} - - protected runQuery (options: { transaction?: Transaction, logging?: boolean } = {}) { - const queryOptions = { - transaction: options.transaction, - logging: options.logging, - replacements: this.replacements, - type: QueryTypes.SELECT as QueryTypes.SELECT, - nest: false - } - - return this.sequelize.query(this.query, queryOptions) - } -} diff --git a/server/models/video/sql/shared/abstract-video-query-builder.ts b/server/models/video/sql/shared/abstract-video-query-builder.ts deleted file mode 100644 index a6afb04e4..000000000 --- a/server/models/video/sql/shared/abstract-video-query-builder.ts +++ /dev/null @@ -1,328 +0,0 @@ -import { createSafeIn } from '@server/models/utils' -import { MUserAccountId } from '@server/types/models' -import validator from 'validator' -import { AbstractRunQuery } from './abstract-run-query' -import { VideoTableAttributes } from './video-table-attributes' - -/** - * - * Abstract builder to create SQL query and fetch video models - * - */ - -export class AbstractVideoQueryBuilder extends AbstractRunQuery { - protected attributes: { [key: string]: string } = {} - - protected joins = '' - protected where: string - - protected tables: VideoTableAttributes - - constructor (protected readonly mode: 'list' | 'get') { - super() - - this.tables = new VideoTableAttributes(this.mode) - } - - protected buildSelect () { - return 'SELECT ' + Object.keys(this.attributes).map(key => { - const value = this.attributes[key] - if (value) return `${key} AS ${value}` - - return key - }).join(', ') - } - - protected includeChannels () { - this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"') - this.addJoin('INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"') - - this.addJoin( - 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"' - ) - - this.addJoin( - 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' + - 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()), - ...this.buildActorInclude('VideoChannel->Actor'), - ...this.buildAvatarInclude('VideoChannel->Actor->Avatar'), - ...this.buildServerInclude('VideoChannel->Actor->Server') - } - } - - protected includeAccounts () { - this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"') - this.addJoin( - 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"' - ) - - this.addJoin( - 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + - 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"' - ) - - this.addJoin( - 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' + - 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoChannel->Account', this.tables.getAccountAttributes()), - ...this.buildActorInclude('VideoChannel->Account->Actor'), - ...this.buildAvatarInclude('VideoChannel->Account->Actor->Avatar'), - ...this.buildServerInclude('VideoChannel->Account->Actor->Server') - } - } - - 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"') - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes()) - } - } - - protected includeWebtorrentFiles () { - this.addJoin('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"') - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes()) - } - } - - protected includeStreamingPlaylistFiles () { - this.addJoin( - 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"' - ) - - this.addJoin( - 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' + - 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()), - ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes()) - } - } - - protected includeUserHistory (userId: number) { - this.addJoin( - 'LEFT OUTER JOIN "userVideoHistory" ' + - 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId' - ) - - this.replacements.userVideoHistoryId = userId - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes()) - } - } - - protected includePlaylist (playlistId: number) { - this.addJoin( - 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' + - 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' - ) - - this.replacements.videoPlaylistId = playlistId - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes()) - } - } - - protected includeTags () { - this.addJoin( - 'LEFT OUTER JOIN (' + - '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' + - ') ' + - 'ON "video"."id" = "Tags->VideoTagModel"."videoId"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()), - ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes()) - } - } - - protected includeBlacklisted () { - this.addJoin( - 'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes()) - } - } - - protected includeBlockedOwnerAndServer (serverAccountId: number, user?: MUserAccountId) { - const blockerIds = [ serverAccountId ] - if (user) blockerIds.push(user.Account.id) - - const inClause = createSafeIn(this.sequelize, blockerIds) - - this.addJoin( - 'LEFT JOIN "accountBlocklist" AS "VideoChannel->Account->AccountBlocklist" ' + - 'ON "VideoChannel->Account"."id" = "VideoChannel->Account->AccountBlocklist"."targetAccountId" ' + - 'AND "VideoChannel->Account->AccountBlocklist"."accountId" IN (' + inClause + ')' - ) - - this.addJoin( - 'LEFT JOIN "serverBlocklist" AS "VideoChannel->Account->Actor->Server->ServerBlocklist" ' + - 'ON "VideoChannel->Account->Actor->Server->ServerBlocklist"."targetServerId" = "VideoChannel->Account->Actor"."serverId" ' + - 'AND "VideoChannel->Account->Actor->Server->ServerBlocklist"."accountId" IN (' + inClause + ') ' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoChannel->Account->AccountBlocklist', this.tables.getBlocklistAttributes()), - ...this.buildAttributesObject('VideoChannel->Account->Actor->Server->ServerBlocklist', this.tables.getBlocklistAttributes()) - } - } - - protected includeScheduleUpdate () { - this.addJoin( - 'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes()) - } - } - - protected includeLive () { - this.addJoin( - 'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes()) - } - } - - protected includeTrackers () { - this.addJoin( - 'LEFT OUTER JOIN (' + - '"videoTracker" AS "Trackers->VideoTrackerModel" ' + - 'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' + - ') ON "video"."id" = "Trackers->VideoTrackerModel"."videoId"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()), - ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes()) - } - } - - protected includeWebTorrentRedundancies () { - this.addJoin( - 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' + - '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes()) - } - } - - protected includeStreamingPlaylistRedundancies () { - this.addJoin( - 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' + - 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"' - ) - - this.attributes = { - ...this.attributes, - - ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes()) - } - } - - protected buildActorInclude (prefixKey: string) { - return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes()) - } - - protected buildAvatarInclude (prefixKey: string) { - return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes()) - } - - protected buildServerInclude (prefixKey: string) { - return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes()) - } - - protected buildAttributesObject (prefixKey: string, attributeKeys: string[]) { - const result: { [id: string]: string} = {} - - const prefixValue = prefixKey.replace(/->/g, '.') - - for (const attribute of attributeKeys) { - result[`"${prefixKey}"."${attribute}"`] = `"${prefixValue}.${attribute}"` - } - - return result - } - - 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 = options.id - } - - protected addJoin (join: string) { - this.joins += join + ' ' - } -} diff --git a/server/models/video/sql/shared/video-file-query-builder.ts b/server/models/video/sql/shared/video-file-query-builder.ts deleted file mode 100644 index 3eb3dc07d..000000000 --- a/server/models/video/sql/shared/video-file-query-builder.ts +++ /dev/null @@ -1,69 +0,0 @@ -import { Sequelize } from 'sequelize' -import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder' -import { AbstractVideoQueryBuilder } from './abstract-video-query-builder' - -/** - * - * Fetch files (webtorrent and streaming playlist) according to a video - * - */ - -export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder { - protected attributes: { [key: string]: string } - - constructor (protected readonly sequelize: Sequelize) { - super('get') - } - - queryWebTorrentVideos (options: BuildVideoGetQueryOptions) { - this.buildWebtorrentFilesQuery(options) - - return this.runQuery(options) - } - - queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) { - this.buildVideoStreamingPlaylistFilesQuery(options) - - return this.runQuery(options) - } - - private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) { - this.attributes = { - '"video"."id"': '' - } - - this.includeWebtorrentFiles() - - if (this.shouldIncludeRedundancies(options)) { - this.includeWebTorrentRedundancies() - } - - this.whereId(options) - - this.query = this.buildQuery() - } - - private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) { - this.attributes = { - '"video"."id"': '' - } - - this.includeStreamingPlaylistFiles() - - if (this.shouldIncludeRedundancies(options)) { - this.includeStreamingPlaylistRedundancies() - } - - this.whereId(options) - - this.query = this.buildQuery() - } - - 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 deleted file mode 100644 index 7751d8e68..000000000 --- a/server/models/video/sql/shared/video-model-builder.ts +++ /dev/null @@ -1,387 +0,0 @@ - -import { AccountModel } from '@server/models/account/account' -import { AccountBlocklistModel } from '@server/models/account/account-blocklist' -import { ActorModel } from '@server/models/actor/actor' -import { ActorImageModel } from '@server/models/actor/actor-image' -import { VideoRedundancyModel } from '@server/models/redundancy/video-redundancy' -import { ServerModel } from '@server/models/server/server' -import { ServerBlocklistModel } from '@server/models/server/server-blocklist' -import { TrackerModel } from '@server/models/server/tracker' -import { UserVideoHistoryModel } from '@server/models/user/user-video-history' -import { VideoInclude } from '@shared/models' -import { ScheduleVideoUpdateModel } from '../../schedule-video-update' -import { TagModel } from '../../tag' -import { ThumbnailModel } from '../../thumbnail' -import { VideoModel } from '../../video' -import { VideoBlacklistModel } from '../../video-blacklist' -import { VideoChannelModel } from '../../video-channel' -import { VideoFileModel } from '../../video-file' -import { VideoLiveModel } from '../../video-live' -import { VideoStreamingPlaylistModel } from '../../video-streaming-playlist' -import { VideoTableAttributes } from './video-table-attributes' - -type SQLRow = { [id: string]: string | number } - -/** - * - * Build video models from SQL rows - * - */ - -export class VideoModelBuilder { - private videosMemo: { [ id: number ]: VideoModel } - private videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } - private videoFileMemo: { [ id: number ]: VideoFileModel } - - private thumbnailsDone: Set - private historyDone: Set - private blacklistDone: Set - private accountBlocklistDone: Set - private serverBlocklistDone: Set - private liveDone: Set - private redundancyDone: Set - private scheduleVideoUpdateDone: Set - - private trackersDone: Set - private tagsDone: Set - - private videos: VideoModel[] - - private readonly buildOpts = { raw: true, isNewRecord: false } - - constructor ( - readonly mode: 'get' | 'list', - readonly tables: VideoTableAttributes - ) { - - } - - buildVideosFromRows (options: { - rows: SQLRow[] - include?: VideoInclude - rowsWebTorrentFiles?: SQLRow[] - rowsStreamingPlaylist?: SQLRow[] - }) { - const { rows, rowsWebTorrentFiles, rowsStreamingPlaylist, include } = options - - this.reinit() - - for (const row of rows) { - this.buildVideoAndAccount(row) - - const videoModel = this.videosMemo[row.id] - - this.setUserHistory(row, videoModel) - this.addThumbnail(row, videoModel) - - if (!rowsWebTorrentFiles) { - this.addWebTorrentFile(row, videoModel) - } - - if (!rowsStreamingPlaylist) { - this.addStreamingPlaylist(row, videoModel) - this.addStreamingPlaylistFile(row) - } - - if (this.mode === 'get') { - this.addTag(row, videoModel) - this.addTracker(row, videoModel) - this.setBlacklisted(row, videoModel) - this.setScheduleVideoUpdate(row, videoModel) - this.setLive(row, videoModel) - } else { - if (include & VideoInclude.BLACKLISTED) { - this.setBlacklisted(row, videoModel) - } - - if (include & VideoInclude.BLOCKED_OWNER) { - this.setBlockedOwner(row, videoModel) - this.setBlockedServer(row, videoModel) - } - } - } - - this.grabSeparateWebTorrentFiles(rowsWebTorrentFiles) - this.grabSeparateStreamingPlaylistFiles(rowsStreamingPlaylist) - - return this.videos - } - - private reinit () { - this.videosMemo = {} - this.videoStreamingPlaylistMemo = {} - this.videoFileMemo = {} - - this.thumbnailsDone = new Set() - this.historyDone = new Set() - this.blacklistDone = new Set() - this.liveDone = new Set() - this.redundancyDone = new Set() - this.scheduleVideoUpdateDone = new Set() - - this.accountBlocklistDone = new Set() - this.serverBlocklistDone = new Set() - - this.trackersDone = new Set() - this.tagsDone = new Set() - - this.videos = [] - } - - private grabSeparateWebTorrentFiles (rowsWebTorrentFiles?: SQLRow[]) { - if (!rowsWebTorrentFiles) return - - for (const row of rowsWebTorrentFiles) { - const id = row['VideoFiles.id'] - if (!id) continue - - const videoModel = this.videosMemo[row.id] - this.addWebTorrentFile(row, videoModel) - this.addRedundancy(row, 'VideoFiles', this.videoFileMemo[id]) - } - } - - private grabSeparateStreamingPlaylistFiles (rowsStreamingPlaylist?: SQLRow[]) { - if (!rowsStreamingPlaylist) return - - for (const row of rowsStreamingPlaylist) { - const id = row['VideoStreamingPlaylists.id'] - if (!id) continue - - const videoModel = this.videosMemo[row.id] - - this.addStreamingPlaylist(row, videoModel) - this.addStreamingPlaylistFile(row) - this.addRedundancy(row, 'VideoStreamingPlaylists', this.videoStreamingPlaylistMemo[id]) - } - } - - private buildVideoAndAccount (row: SQLRow) { - if (this.videosMemo[row.id]) return - - const videoModel = new VideoModel(this.grab(row, this.tables.getVideoAttributes(), ''), this.buildOpts) - - videoModel.UserVideoHistories = [] - videoModel.Thumbnails = [] - videoModel.VideoFiles = [] - videoModel.VideoStreamingPlaylists = [] - 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') - - accountModel.BlockedBy = [] - - channelModel.Account = accountModel - - videoModel.VideoChannel = channelModel - } - - 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 = row[`${serverPrefix}.id`] !== null - ? new ServerModel(this.grab(row, this.tables.getServerAttributes(), serverPrefix), this.buildOpts) - : null - - if (serverModel) serverModel.BlockedBy = [] - - const actorModel = new ActorModel(this.grab(row, this.tables.getActorAttributes(), actorPrefix), this.buildOpts) - actorModel.Avatar = avatarModel - actorModel.Server = serverModel - - return actorModel - } - - private setUserHistory (row: SQLRow, videoModel: VideoModel) { - const id = row['userVideoHistory.id'] - if (!id || this.historyDone.has(id)) return - - const attributes = this.grab(row, this.tables.getUserHistoryAttributes(), 'userVideoHistory') - const historyModel = new UserVideoHistoryModel(attributes, this.buildOpts) - videoModel.UserVideoHistories.push(historyModel) - - this.historyDone.add(id) - } - - private addThumbnail (row: SQLRow, videoModel: VideoModel) { - const id = row['Thumbnails.id'] - if (!id || this.thumbnailsDone.has(id)) return - - const attributes = this.grab(row, this.tables.getThumbnailAttributes(), 'Thumbnails') - const thumbnailModel = new ThumbnailModel(attributes, this.buildOpts) - videoModel.Thumbnails.push(thumbnailModel) - - this.thumbnailsDone.add(id) - } - - private addWebTorrentFile (row: SQLRow, videoModel: VideoModel) { - const id = row['VideoFiles.id'] - if (!id || this.videoFileMemo[id]) return - - const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoFiles') - const videoFileModel = new VideoFileModel(attributes, this.buildOpts) - videoModel.VideoFiles.push(videoFileModel) - - this.videoFileMemo[id] = videoFileModel - } - - private addStreamingPlaylist (row: SQLRow, videoModel: VideoModel) { - const id = row['VideoStreamingPlaylists.id'] - if (!id || this.videoStreamingPlaylistMemo[id]) return - - const attributes = this.grab(row, this.tables.getStreamingPlaylistAttributes(), 'VideoStreamingPlaylists') - const streamingPlaylist = new VideoStreamingPlaylistModel(attributes, this.buildOpts) - streamingPlaylist.VideoFiles = [] - - videoModel.VideoStreamingPlaylists.push(streamingPlaylist) - - this.videoStreamingPlaylistMemo[id] = streamingPlaylist - } - - private addStreamingPlaylistFile (row: SQLRow) { - const id = row['VideoStreamingPlaylists.VideoFiles.id'] - if (!id || this.videoFileMemo[id]) return - - const streamingPlaylist = this.videoStreamingPlaylistMemo[row['VideoStreamingPlaylists.id']] - - const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoStreamingPlaylists.VideoFiles') - const videoFileModel = new VideoFileModel(attributes, this.buildOpts) - streamingPlaylist.VideoFiles.push(videoFileModel) - - this.videoFileMemo[id] = videoFileModel - } - - private addRedundancy (row: SQLRow, prefix: string, to: VideoFileModel | VideoStreamingPlaylistModel) { - if (!to.RedundancyVideos) to.RedundancyVideos = [] - - const redundancyPrefix = `${prefix}.RedundancyVideos` - const id = row[`${redundancyPrefix}.id`] - - if (!id || this.redundancyDone.has(id)) return - - const attributes = this.grab(row, this.tables.getRedundancyAttributes(), redundancyPrefix) - const redundancyModel = new VideoRedundancyModel(attributes, this.buildOpts) - to.RedundancyVideos.push(redundancyModel) - - this.redundancyDone.add(id) - } - - private addTag (row: SQLRow, videoModel: VideoModel) { - if (!row['Tags.name']) return - - const key = `${row['Tags.VideoTagModel.videoId']}-${row['Tags.VideoTagModel.tagId']}` - if (this.tagsDone.has(key)) return - - 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: SQLRow, videoModel: VideoModel) { - if (!row['Trackers.id']) return - - const key = `${row['Trackers.VideoTrackerModel.videoId']}-${row['Trackers.VideoTrackerModel.trackerId']}` - if (this.trackersDone.has(key)) return - - 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: SQLRow, videoModel: VideoModel) { - const id = row['VideoBlacklist.id'] - if (!id || this.blacklistDone.has(id)) return - - const attributes = this.grab(row, this.tables.getBlacklistedAttributes(), 'VideoBlacklist') - videoModel.VideoBlacklist = new VideoBlacklistModel(attributes, this.buildOpts) - - this.blacklistDone.add(id) - } - - private setBlockedOwner (row: SQLRow, videoModel: VideoModel) { - const id = row['VideoChannel.Account.AccountBlocklist.id'] - if (!id) return - - const key = `${videoModel.id}-${id}` - if (this.accountBlocklistDone.has(key)) return - - const attributes = this.grab(row, this.tables.getBlocklistAttributes(), 'VideoChannel.Account.AccountBlocklist') - videoModel.VideoChannel.Account.BlockedBy.push(new AccountBlocklistModel(attributes, this.buildOpts)) - - this.accountBlocklistDone.add(key) - } - - private setBlockedServer (row: SQLRow, videoModel: VideoModel) { - const id = row['VideoChannel.Account.Actor.Server.ServerBlocklist.id'] - if (!id || this.serverBlocklistDone.has(id)) return - - const key = `${videoModel.id}-${id}` - if (this.serverBlocklistDone.has(key)) return - - const attributes = this.grab(row, this.tables.getBlocklistAttributes(), 'VideoChannel.Account.Actor.Server.ServerBlocklist') - videoModel.VideoChannel.Account.Actor.Server.BlockedBy.push(new ServerBlocklistModel(attributes, this.buildOpts)) - - this.serverBlocklistDone.add(key) - } - - private setScheduleVideoUpdate (row: SQLRow, videoModel: VideoModel) { - const id = row['ScheduleVideoUpdate.id'] - if (!id || this.scheduleVideoUpdateDone.has(id)) return - - const attributes = this.grab(row, this.tables.getScheduleUpdateAttributes(), 'ScheduleVideoUpdate') - videoModel.ScheduleVideoUpdate = new ScheduleVideoUpdateModel(attributes, this.buildOpts) - - this.scheduleVideoUpdateDone.add(id) - } - - private setLive (row: SQLRow, videoModel: VideoModel) { - const id = row['VideoLive.id'] - if (!id || this.liveDone.has(id)) return - - const attributes = this.grab(row, this.tables.getLiveAttributes(), 'VideoLive') - videoModel.VideoLive = new VideoLiveModel(attributes, this.buildOpts) - - 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-table-attributes.ts b/server/models/video/sql/shared/video-table-attributes.ts deleted file mode 100644 index 8a8d2073a..000000000 --- a/server/models/video/sql/shared/video-table-attributes.ts +++ /dev/null @@ -1,269 +0,0 @@ - -/** - * - * Class to build video attributes/join names we want to fetch from the database - * - */ -export class VideoTableAttributes { - - constructor (readonly mode: 'get' | 'list') { - - } - - getChannelAttributesForUser () { - return [ 'id', 'accountId' ] - } - - getChannelAttributes () { - let attributeKeys = [ - 'id', - 'name', - 'description', - 'actorId' - ] - - if (this.mode === 'get') { - attributeKeys = attributeKeys.concat([ - 'support', - 'createdAt', - 'updatedAt' - ]) - } - - return attributeKeys - } - - getUserAccountAttributes () { - return [ 'id', 'userId' ] - } - - getAccountAttributes () { - let attributeKeys = [ 'id', 'name', 'actorId' ] - - if (this.mode === 'get') { - attributeKeys = attributeKeys.concat([ - 'description', - 'userId', - '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', - 'storage' - ] - } - - getStreamingPlaylistAttributes () { - return [ - 'id', - 'playlistUrl', - 'playlistFilename', - 'type', - 'p2pMediaLoaderInfohashes', - 'p2pMediaLoaderPeerVersion', - 'segmentsSha256Filename', - 'segmentsSha256Url', - 'videoId', - 'createdAt', - 'updatedAt', - 'storage' - ] - } - - 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' ] - } - - getBlocklistAttributes () { - return [ 'id' ] - } - - 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', - 'moveJobsRunning' - ] - } -} diff --git a/server/models/video/sql/video-model-get-query-builder.ts b/server/models/video/sql/video-model-get-query-builder.ts deleted file mode 100644 index a65c96097..000000000 --- a/server/models/video/sql/video-model-get-query-builder.ts +++ /dev/null @@ -1,178 +0,0 @@ -import { Sequelize, Transaction } from 'sequelize' -import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' -import { VideoFileQueryBuilder } from './shared/video-file-query-builder' -import { VideoModelBuilder } from './shared/video-model-builder' -import { VideoTableAttributes } from './shared/video-table-attributes' - -/** - * - * Build a GET SQL query, fetch rows and create the video model - * - */ - -export type GetType = - 'api' | - 'full-light' | - 'account-blacklist-files' | - 'all-files' | - 'thumbnails' | - 'thumbnails-blacklist' | - 'id' | - 'blacklist-rights' - -export type BuildVideoGetQueryOptions = { - id?: number | string - url?: string - - type: GetType - - userId?: number - transaction?: Transaction - - logging?: boolean -} - -export class VideoModelGetQueryBuilder { - videoQueryBuilder: VideosModelGetQuerySubBuilder - webtorrentFilesQueryBuilder: VideoFileQueryBuilder - streamingPlaylistFilesQueryBuilder: VideoFileQueryBuilder - - private readonly videoModelBuilder: VideoModelBuilder - - private static readonly videoFilesInclude = new Set([ 'api', 'full-light', 'account-blacklist-files', 'all-files' ]) - - 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 VideoTableAttributes('get')) - } - - async queryVideo (options: BuildVideoGetQueryOptions) { - const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([ - this.videoQueryBuilder.queryVideos(options), - - VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) - ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(options) - : Promise.resolve(undefined), - - VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) - ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(options) - : Promise.resolve(undefined) - ]) - - const videos = this.videoModelBuilder.buildVideosFromRows({ - rows: videoRows, - rowsWebTorrentFiles: webtorrentFilesRows, - rowsStreamingPlaylist: streamingPlaylistFilesRows - }) - - if (videos.length > 1) { - throw new Error('Video results is more than 1') - } - - if (videos.length === 0) return null - - return videos[0] - } -} - -export class VideosModelGetQuerySubBuilder extends AbstractVideoQueryBuilder { - protected attributes: { [key: string]: string } - - protected webtorrentFilesQuery: string - protected streamingPlaylistFilesQuery: string - - private static readonly trackersInclude = new Set([ 'api' ]) - private static readonly liveInclude = new Set([ 'api', 'full-light' ]) - private static readonly scheduleUpdateInclude = new Set([ 'api', 'full-light' ]) - private static readonly tagsInclude = new Set([ 'api', 'full-light' ]) - private static readonly userHistoryInclude = new Set([ 'api', 'full-light' ]) - private static readonly accountInclude = new Set([ 'api', 'full-light', 'account-blacklist-files' ]) - private static readonly ownerUserInclude = new Set([ 'blacklist-rights' ]) - - private static readonly blacklistedInclude = new Set([ - 'api', - 'full-light', - 'account-blacklist-files', - 'thumbnails-blacklist', - 'blacklist-rights' - ]) - - private static readonly thumbnailsInclude = new Set([ - 'api', - 'full-light', - 'account-blacklist-files', - 'all-files', - 'thumbnails', - 'thumbnails-blacklist' - ]) - - constructor (protected readonly sequelize: Sequelize) { - super('get') - } - - queryVideos (options: BuildVideoGetQueryOptions) { - this.buildMainGetQuery(options) - - return this.runQuery(options) - } - - private buildMainGetQuery (options: BuildVideoGetQueryOptions) { - this.attributes = { - '"video".*': '' - } - - if (VideosModelGetQuerySubBuilder.thumbnailsInclude.has(options.type)) { - this.includeThumbnails() - } - - if (VideosModelGetQuerySubBuilder.blacklistedInclude.has(options.type)) { - this.includeBlacklisted() - } - - if (VideosModelGetQuerySubBuilder.accountInclude.has(options.type)) { - this.includeChannels() - this.includeAccounts() - } - - if (VideosModelGetQuerySubBuilder.tagsInclude.has(options.type)) { - this.includeTags() - } - - if (VideosModelGetQuerySubBuilder.scheduleUpdateInclude.has(options.type)) { - this.includeScheduleUpdate() - } - - if (VideosModelGetQuerySubBuilder.liveInclude.has(options.type)) { - this.includeLive() - } - - if (options.userId && VideosModelGetQuerySubBuilder.userHistoryInclude.has(options.type)) { - this.includeUserHistory(options.userId) - } - - if (VideosModelGetQuerySubBuilder.ownerUserInclude.has(options.type)) { - this.includeOwnerUser() - } - - if (VideosModelGetQuerySubBuilder.trackersInclude.has(options.type)) { - this.includeTrackers() - } - - this.whereId(options) - - this.query = this.buildQuery(options) - } - - private buildQuery (options: BuildVideoGetQueryOptions) { - const order = VideosModelGetQuerySubBuilder.tagsInclude.has(options.type) - ? 'ORDER BY "Tags"."name" ASC' - : '' - - const from = `SELECT * FROM "video" ${this.where} LIMIT 1` - - return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins} ${order}` - } -} diff --git a/server/models/video/sql/video/index.ts b/server/models/video/sql/video/index.ts new file mode 100644 index 000000000..e9132d5e1 --- /dev/null +++ b/server/models/video/sql/video/index.ts @@ -0,0 +1,3 @@ +export * from './video-model-get-query-builder' +export * from './videos-id-list-query-builder' +export * from './videos-model-list-query-builder' diff --git a/server/models/video/sql/video/shared/abstract-run-query.ts b/server/models/video/sql/video/shared/abstract-run-query.ts new file mode 100644 index 000000000..8e7a7642d --- /dev/null +++ b/server/models/video/sql/video/shared/abstract-run-query.ts @@ -0,0 +1,26 @@ +import { QueryTypes, Sequelize, Transaction } from 'sequelize' + +/** + * + * Abstact builder to run video SQL queries + * + */ + +export class AbstractRunQuery { + protected sequelize: Sequelize + + protected query: string + protected replacements: any = {} + + protected runQuery (options: { transaction?: Transaction, logging?: boolean } = {}) { + const queryOptions = { + transaction: options.transaction, + logging: options.logging, + replacements: this.replacements, + type: QueryTypes.SELECT as QueryTypes.SELECT, + nest: false + } + + return this.sequelize.query(this.query, queryOptions) + } +} 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 new file mode 100644 index 000000000..490e5e6e0 --- /dev/null +++ b/server/models/video/sql/video/shared/abstract-video-query-builder.ts @@ -0,0 +1,331 @@ +import { createSafeIn } from '@server/models/utils' +import { MUserAccountId } from '@server/types/models' +import { ActorImageType } from '@shared/models' +import validator from 'validator' +import { AbstractRunQuery } from './abstract-run-query' +import { VideoTableAttributes } from './video-table-attributes' + +/** + * + * Abstract builder to create SQL query and fetch video models + * + */ + +export class AbstractVideoQueryBuilder extends AbstractRunQuery { + protected attributes: { [key: string]: string } = {} + + protected joins = '' + protected where: string + + protected tables: VideoTableAttributes + + constructor (protected readonly mode: 'list' | 'get') { + super() + + this.tables = new VideoTableAttributes(this.mode) + } + + protected buildSelect () { + return 'SELECT ' + Object.keys(this.attributes).map(key => { + const value = this.attributes[key] + if (value) return `${key} AS ${value}` + + return key + }).join(', ') + } + + protected includeChannels () { + this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"') + this.addJoin('INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"') + + this.addJoin( + 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"' + ) + + this.addJoin( + 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatars" ' + + 'ON "VideoChannel->Actor"."id" = "VideoChannel->Actor->Avatars"."actorId" ' + + `AND "VideoChannel->Actor->Avatars"."type" = ${ActorImageType.AVATAR}` + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()), + ...this.buildActorInclude('VideoChannel->Actor'), + ...this.buildAvatarInclude('VideoChannel->Actor->Avatars'), + ...this.buildServerInclude('VideoChannel->Actor->Server') + } + } + + protected includeAccounts () { + this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"') + this.addJoin( + 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"' + ) + + this.addJoin( + 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + + 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"' + ) + + this.addJoin( + 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatars" ' + + 'ON "VideoChannel->Account"."actorId"= "VideoChannel->Account->Actor->Avatars"."actorId" ' + + `AND "VideoChannel->Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}` + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoChannel->Account', this.tables.getAccountAttributes()), + ...this.buildActorInclude('VideoChannel->Account->Actor'), + ...this.buildAvatarInclude('VideoChannel->Account->Actor->Avatars'), + ...this.buildServerInclude('VideoChannel->Account->Actor->Server') + } + } + + 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"') + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes()) + } + } + + protected includeWebtorrentFiles () { + this.addJoin('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"') + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes()) + } + } + + protected includeStreamingPlaylistFiles () { + this.addJoin( + 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"' + ) + + this.addJoin( + 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' + + 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()), + ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes()) + } + } + + protected includeUserHistory (userId: number) { + this.addJoin( + 'LEFT OUTER JOIN "userVideoHistory" ' + + 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId' + ) + + this.replacements.userVideoHistoryId = userId + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes()) + } + } + + protected includePlaylist (playlistId: number) { + this.addJoin( + 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' + + 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' + ) + + this.replacements.videoPlaylistId = playlistId + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes()) + } + } + + protected includeTags () { + this.addJoin( + 'LEFT OUTER JOIN (' + + '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' + + ') ' + + 'ON "video"."id" = "Tags->VideoTagModel"."videoId"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()), + ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes()) + } + } + + protected includeBlacklisted () { + this.addJoin( + 'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes()) + } + } + + protected includeBlockedOwnerAndServer (serverAccountId: number, user?: MUserAccountId) { + const blockerIds = [ serverAccountId ] + if (user) blockerIds.push(user.Account.id) + + const inClause = createSafeIn(this.sequelize, blockerIds) + + this.addJoin( + 'LEFT JOIN "accountBlocklist" AS "VideoChannel->Account->AccountBlocklist" ' + + 'ON "VideoChannel->Account"."id" = "VideoChannel->Account->AccountBlocklist"."targetAccountId" ' + + 'AND "VideoChannel->Account->AccountBlocklist"."accountId" IN (' + inClause + ')' + ) + + this.addJoin( + 'LEFT JOIN "serverBlocklist" AS "VideoChannel->Account->Actor->Server->ServerBlocklist" ' + + 'ON "VideoChannel->Account->Actor->Server->ServerBlocklist"."targetServerId" = "VideoChannel->Account->Actor"."serverId" ' + + 'AND "VideoChannel->Account->Actor->Server->ServerBlocklist"."accountId" IN (' + inClause + ') ' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoChannel->Account->AccountBlocklist', this.tables.getBlocklistAttributes()), + ...this.buildAttributesObject('VideoChannel->Account->Actor->Server->ServerBlocklist', this.tables.getBlocklistAttributes()) + } + } + + protected includeScheduleUpdate () { + this.addJoin( + 'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes()) + } + } + + protected includeLive () { + this.addJoin( + 'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes()) + } + } + + protected includeTrackers () { + this.addJoin( + 'LEFT OUTER JOIN (' + + '"videoTracker" AS "Trackers->VideoTrackerModel" ' + + 'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' + + ') ON "video"."id" = "Trackers->VideoTrackerModel"."videoId"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()), + ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes()) + } + } + + protected includeWebTorrentRedundancies () { + this.addJoin( + 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' + + '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes()) + } + } + + protected includeStreamingPlaylistRedundancies () { + this.addJoin( + 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' + + 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"' + ) + + this.attributes = { + ...this.attributes, + + ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes()) + } + } + + protected buildActorInclude (prefixKey: string) { + return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes()) + } + + protected buildAvatarInclude (prefixKey: string) { + return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes()) + } + + protected buildServerInclude (prefixKey: string) { + return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes()) + } + + protected buildAttributesObject (prefixKey: string, attributeKeys: string[]) { + const result: { [id: string]: string} = {} + + const prefixValue = prefixKey.replace(/->/g, '.') + + for (const attribute of attributeKeys) { + result[`"${prefixKey}"."${attribute}"`] = `"${prefixValue}.${attribute}"` + } + + return result + } + + 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 = options.id + } + + protected addJoin (join: string) { + this.joins += join + ' ' + } +} 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 new file mode 100644 index 000000000..3eb3dc07d --- /dev/null +++ b/server/models/video/sql/video/shared/video-file-query-builder.ts @@ -0,0 +1,69 @@ +import { Sequelize } from 'sequelize' +import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder' +import { AbstractVideoQueryBuilder } from './abstract-video-query-builder' + +/** + * + * Fetch files (webtorrent and streaming playlist) according to a video + * + */ + +export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder { + protected attributes: { [key: string]: string } + + constructor (protected readonly sequelize: Sequelize) { + super('get') + } + + queryWebTorrentVideos (options: BuildVideoGetQueryOptions) { + this.buildWebtorrentFilesQuery(options) + + return this.runQuery(options) + } + + queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) { + this.buildVideoStreamingPlaylistFilesQuery(options) + + return this.runQuery(options) + } + + private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) { + this.attributes = { + '"video"."id"': '' + } + + this.includeWebtorrentFiles() + + if (this.shouldIncludeRedundancies(options)) { + this.includeWebTorrentRedundancies() + } + + this.whereId(options) + + this.query = this.buildQuery() + } + + private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) { + this.attributes = { + '"video"."id"': '' + } + + this.includeStreamingPlaylistFiles() + + if (this.shouldIncludeRedundancies(options)) { + this.includeStreamingPlaylistRedundancies() + } + + this.whereId(options) + + this.query = this.buildQuery() + } + + 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/video/shared/video-model-builder.ts b/server/models/video/sql/video/shared/video-model-builder.ts new file mode 100644 index 000000000..b1b47b721 --- /dev/null +++ b/server/models/video/sql/video/shared/video-model-builder.ts @@ -0,0 +1,406 @@ + +import { AccountModel } from '@server/models/account/account' +import { AccountBlocklistModel } from '@server/models/account/account-blocklist' +import { ActorModel } from '@server/models/actor/actor' +import { ActorImageModel } from '@server/models/actor/actor-image' +import { VideoRedundancyModel } from '@server/models/redundancy/video-redundancy' +import { ServerModel } from '@server/models/server/server' +import { ServerBlocklistModel } from '@server/models/server/server-blocklist' +import { TrackerModel } from '@server/models/server/tracker' +import { UserVideoHistoryModel } from '@server/models/user/user-video-history' +import { VideoInclude } from '@shared/models' +import { ScheduleVideoUpdateModel } from '../../../schedule-video-update' +import { TagModel } from '../../../tag' +import { ThumbnailModel } from '../../../thumbnail' +import { VideoModel } from '../../../video' +import { VideoBlacklistModel } from '../../../video-blacklist' +import { VideoChannelModel } from '../../../video-channel' +import { VideoFileModel } from '../../../video-file' +import { VideoLiveModel } from '../../../video-live' +import { VideoStreamingPlaylistModel } from '../../../video-streaming-playlist' +import { VideoTableAttributes } from './video-table-attributes' + +type SQLRow = { [id: string]: string | number } + +/** + * + * Build video models from SQL rows + * + */ + +export class VideoModelBuilder { + private videosMemo: { [ id: number ]: VideoModel } + private videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel } + private videoFileMemo: { [ id: number ]: VideoFileModel } + + private thumbnailsDone: Set + private actorImagesDone: Set + private historyDone: Set + private blacklistDone: Set + private accountBlocklistDone: Set + private serverBlocklistDone: Set + private liveDone: Set + private redundancyDone: Set + private scheduleVideoUpdateDone: Set + + private trackersDone: Set + private tagsDone: Set + + private videos: VideoModel[] + + private readonly buildOpts = { raw: true, isNewRecord: false } + + constructor ( + readonly mode: 'get' | 'list', + readonly tables: VideoTableAttributes + ) { + + } + + buildVideosFromRows (options: { + rows: SQLRow[] + include?: VideoInclude + rowsWebTorrentFiles?: SQLRow[] + rowsStreamingPlaylist?: SQLRow[] + }) { + const { rows, rowsWebTorrentFiles, rowsStreamingPlaylist, include } = options + + this.reinit() + + for (const row of rows) { + this.buildVideoAndAccount(row) + + const videoModel = this.videosMemo[row.id as number] + + this.setUserHistory(row, videoModel) + this.addThumbnail(row, videoModel) + + const channelActor = videoModel.VideoChannel?.Actor + if (channelActor) { + this.addActorAvatar(row, 'VideoChannel.Actor', channelActor) + } + + const accountActor = videoModel.VideoChannel?.Account?.Actor + if (accountActor) { + this.addActorAvatar(row, 'VideoChannel.Account.Actor', accountActor) + } + + if (!rowsWebTorrentFiles) { + this.addWebTorrentFile(row, videoModel) + } + + if (!rowsStreamingPlaylist) { + this.addStreamingPlaylist(row, videoModel) + this.addStreamingPlaylistFile(row) + } + + if (this.mode === 'get') { + this.addTag(row, videoModel) + this.addTracker(row, videoModel) + this.setBlacklisted(row, videoModel) + this.setScheduleVideoUpdate(row, videoModel) + this.setLive(row, videoModel) + } else { + if (include & VideoInclude.BLACKLISTED) { + this.setBlacklisted(row, videoModel) + } + + if (include & VideoInclude.BLOCKED_OWNER) { + this.setBlockedOwner(row, videoModel) + this.setBlockedServer(row, videoModel) + } + } + } + + this.grabSeparateWebTorrentFiles(rowsWebTorrentFiles) + this.grabSeparateStreamingPlaylistFiles(rowsStreamingPlaylist) + + return this.videos + } + + private reinit () { + this.videosMemo = {} + this.videoStreamingPlaylistMemo = {} + this.videoFileMemo = {} + + this.thumbnailsDone = new Set() + this.actorImagesDone = new Set() + this.historyDone = new Set() + this.blacklistDone = new Set() + this.liveDone = new Set() + this.redundancyDone = new Set() + this.scheduleVideoUpdateDone = new Set() + + this.accountBlocklistDone = new Set() + this.serverBlocklistDone = new Set() + + this.trackersDone = new Set() + this.tagsDone = new Set() + + this.videos = [] + } + + private grabSeparateWebTorrentFiles (rowsWebTorrentFiles?: SQLRow[]) { + if (!rowsWebTorrentFiles) return + + for (const row of rowsWebTorrentFiles) { + const id = row['VideoFiles.id'] + if (!id) continue + + const videoModel = this.videosMemo[row.id] + this.addWebTorrentFile(row, videoModel) + this.addRedundancy(row, 'VideoFiles', this.videoFileMemo[id]) + } + } + + private grabSeparateStreamingPlaylistFiles (rowsStreamingPlaylist?: SQLRow[]) { + if (!rowsStreamingPlaylist) return + + for (const row of rowsStreamingPlaylist) { + const id = row['VideoStreamingPlaylists.id'] + if (!id) continue + + const videoModel = this.videosMemo[row.id] + + this.addStreamingPlaylist(row, videoModel) + this.addStreamingPlaylistFile(row) + this.addRedundancy(row, 'VideoStreamingPlaylists', this.videoStreamingPlaylistMemo[id]) + } + } + + private buildVideoAndAccount (row: SQLRow) { + if (this.videosMemo[row.id]) return + + const videoModel = new VideoModel(this.grab(row, this.tables.getVideoAttributes(), ''), this.buildOpts) + + videoModel.UserVideoHistories = [] + videoModel.Thumbnails = [] + videoModel.VideoFiles = [] + videoModel.VideoStreamingPlaylists = [] + 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') + + accountModel.BlockedBy = [] + + channelModel.Account = accountModel + + videoModel.VideoChannel = channelModel + } + + private buildActor (row: SQLRow, prefix: string) { + const actorPrefix = `${prefix}.Actor` + const serverPrefix = `${actorPrefix}.Server` + + const serverModel = row[`${serverPrefix}.id`] !== null + ? new ServerModel(this.grab(row, this.tables.getServerAttributes(), serverPrefix), this.buildOpts) + : null + + if (serverModel) serverModel.BlockedBy = [] + + const actorModel = new ActorModel(this.grab(row, this.tables.getActorAttributes(), actorPrefix), this.buildOpts) + actorModel.Server = serverModel + actorModel.Avatars = [] + + return actorModel + } + + private setUserHistory (row: SQLRow, videoModel: VideoModel) { + const id = row['userVideoHistory.id'] + if (!id || this.historyDone.has(id)) return + + const attributes = this.grab(row, this.tables.getUserHistoryAttributes(), 'userVideoHistory') + const historyModel = new UserVideoHistoryModel(attributes, this.buildOpts) + videoModel.UserVideoHistories.push(historyModel) + + this.historyDone.add(id) + } + + private addActorAvatar (row: SQLRow, actorPrefix: string, actor: ActorModel) { + const avatarPrefix = `${actorPrefix}.Avatar` + const id = row[`${avatarPrefix}.id`] + if (!id || this.actorImagesDone.has(id)) return + + const attributes = this.grab(row, this.tables.getAvatarAttributes(), avatarPrefix) + const avatarModel = new ActorImageModel(attributes, this.buildOpts) + actor.Avatars.push(avatarModel) + + this.actorImagesDone.add(id) + } + + private addThumbnail (row: SQLRow, videoModel: VideoModel) { + const id = row['Thumbnails.id'] + if (!id || this.thumbnailsDone.has(id)) return + + const attributes = this.grab(row, this.tables.getThumbnailAttributes(), 'Thumbnails') + const thumbnailModel = new ThumbnailModel(attributes, this.buildOpts) + videoModel.Thumbnails.push(thumbnailModel) + + this.thumbnailsDone.add(id) + } + + private addWebTorrentFile (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoFiles.id'] + if (!id || this.videoFileMemo[id]) return + + const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoFiles') + const videoFileModel = new VideoFileModel(attributes, this.buildOpts) + videoModel.VideoFiles.push(videoFileModel) + + this.videoFileMemo[id] = videoFileModel + } + + private addStreamingPlaylist (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoStreamingPlaylists.id'] + if (!id || this.videoStreamingPlaylistMemo[id]) return + + const attributes = this.grab(row, this.tables.getStreamingPlaylistAttributes(), 'VideoStreamingPlaylists') + const streamingPlaylist = new VideoStreamingPlaylistModel(attributes, this.buildOpts) + streamingPlaylist.VideoFiles = [] + + videoModel.VideoStreamingPlaylists.push(streamingPlaylist) + + this.videoStreamingPlaylistMemo[id] = streamingPlaylist + } + + private addStreamingPlaylistFile (row: SQLRow) { + const id = row['VideoStreamingPlaylists.VideoFiles.id'] + if (!id || this.videoFileMemo[id]) return + + const streamingPlaylist = this.videoStreamingPlaylistMemo[row['VideoStreamingPlaylists.id']] + + const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoStreamingPlaylists.VideoFiles') + const videoFileModel = new VideoFileModel(attributes, this.buildOpts) + streamingPlaylist.VideoFiles.push(videoFileModel) + + this.videoFileMemo[id] = videoFileModel + } + + private addRedundancy (row: SQLRow, prefix: string, to: VideoFileModel | VideoStreamingPlaylistModel) { + if (!to.RedundancyVideos) to.RedundancyVideos = [] + + const redundancyPrefix = `${prefix}.RedundancyVideos` + const id = row[`${redundancyPrefix}.id`] + + if (!id || this.redundancyDone.has(id)) return + + const attributes = this.grab(row, this.tables.getRedundancyAttributes(), redundancyPrefix) + const redundancyModel = new VideoRedundancyModel(attributes, this.buildOpts) + to.RedundancyVideos.push(redundancyModel) + + this.redundancyDone.add(id) + } + + private addTag (row: SQLRow, videoModel: VideoModel) { + if (!row['Tags.name']) return + + const key = `${row['Tags.VideoTagModel.videoId']}-${row['Tags.VideoTagModel.tagId']}` + if (this.tagsDone.has(key)) return + + 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: SQLRow, videoModel: VideoModel) { + if (!row['Trackers.id']) return + + const key = `${row['Trackers.VideoTrackerModel.videoId']}-${row['Trackers.VideoTrackerModel.trackerId']}` + if (this.trackersDone.has(key)) return + + 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: SQLRow, videoModel: VideoModel) { + const id = row['VideoBlacklist.id'] + if (!id || this.blacklistDone.has(id)) return + + const attributes = this.grab(row, this.tables.getBlacklistedAttributes(), 'VideoBlacklist') + videoModel.VideoBlacklist = new VideoBlacklistModel(attributes, this.buildOpts) + + this.blacklistDone.add(id) + } + + private setBlockedOwner (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoChannel.Account.AccountBlocklist.id'] + if (!id) return + + const key = `${videoModel.id}-${id}` + if (this.accountBlocklistDone.has(key)) return + + const attributes = this.grab(row, this.tables.getBlocklistAttributes(), 'VideoChannel.Account.AccountBlocklist') + videoModel.VideoChannel.Account.BlockedBy.push(new AccountBlocklistModel(attributes, this.buildOpts)) + + this.accountBlocklistDone.add(key) + } + + private setBlockedServer (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoChannel.Account.Actor.Server.ServerBlocklist.id'] + if (!id || this.serverBlocklistDone.has(id)) return + + const key = `${videoModel.id}-${id}` + if (this.serverBlocklistDone.has(key)) return + + const attributes = this.grab(row, this.tables.getBlocklistAttributes(), 'VideoChannel.Account.Actor.Server.ServerBlocklist') + videoModel.VideoChannel.Account.Actor.Server.BlockedBy.push(new ServerBlocklistModel(attributes, this.buildOpts)) + + this.serverBlocklistDone.add(key) + } + + private setScheduleVideoUpdate (row: SQLRow, videoModel: VideoModel) { + const id = row['ScheduleVideoUpdate.id'] + if (!id || this.scheduleVideoUpdateDone.has(id)) return + + const attributes = this.grab(row, this.tables.getScheduleUpdateAttributes(), 'ScheduleVideoUpdate') + videoModel.ScheduleVideoUpdate = new ScheduleVideoUpdateModel(attributes, this.buildOpts) + + this.scheduleVideoUpdateDone.add(id) + } + + private setLive (row: SQLRow, videoModel: VideoModel) { + const id = row['VideoLive.id'] + if (!id || this.liveDone.has(id)) return + + const attributes = this.grab(row, this.tables.getLiveAttributes(), 'VideoLive') + videoModel.VideoLive = new VideoLiveModel(attributes, this.buildOpts) + + 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/video/shared/video-table-attributes.ts b/server/models/video/sql/video/shared/video-table-attributes.ts new file mode 100644 index 000000000..df2ed3fb0 --- /dev/null +++ b/server/models/video/sql/video/shared/video-table-attributes.ts @@ -0,0 +1,269 @@ + +/** + * + * Class to build video attributes/join names we want to fetch from the database + * + */ +export class VideoTableAttributes { + + constructor (readonly mode: 'get' | 'list') { + + } + + getChannelAttributesForUser () { + return [ 'id', 'accountId' ] + } + + getChannelAttributes () { + let attributeKeys = [ + 'id', + 'name', + 'description', + 'actorId' + ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'support', + 'createdAt', + 'updatedAt' + ]) + } + + return attributeKeys + } + + getUserAccountAttributes () { + return [ 'id', 'userId' ] + } + + getAccountAttributes () { + let attributeKeys = [ 'id', 'name', 'actorId' ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'description', + 'userId', + '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', + 'storage' + ] + } + + getStreamingPlaylistAttributes () { + return [ + 'id', + 'playlistUrl', + 'playlistFilename', + 'type', + 'p2pMediaLoaderInfohashes', + 'p2pMediaLoaderPeerVersion', + 'segmentsSha256Filename', + 'segmentsSha256Url', + 'videoId', + 'createdAt', + 'updatedAt', + 'storage' + ] + } + + 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' ] + } + + getBlocklistAttributes () { + return [ 'id' ] + } + + 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' + ] + + if (this.mode === 'get') { + attributeKeys = attributeKeys.concat([ + 'type', + 'followersCount', + 'followingCount', + 'inboxUrl', + 'outboxUrl', + 'sharedInboxUrl', + 'followersUrl', + 'followingUrl', + 'remoteCreatedAt', + 'createdAt', + 'updatedAt' + ]) + } + + return attributeKeys + } + + getAvatarAttributes () { + let attributeKeys = [ + 'id', + 'width', + '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', + 'moveJobsRunning' + ] + } +} 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 new file mode 100644 index 000000000..a65c96097 --- /dev/null +++ b/server/models/video/sql/video/video-model-get-query-builder.ts @@ -0,0 +1,178 @@ +import { Sequelize, Transaction } from 'sequelize' +import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' +import { VideoFileQueryBuilder } from './shared/video-file-query-builder' +import { VideoModelBuilder } from './shared/video-model-builder' +import { VideoTableAttributes } from './shared/video-table-attributes' + +/** + * + * Build a GET SQL query, fetch rows and create the video model + * + */ + +export type GetType = + 'api' | + 'full-light' | + 'account-blacklist-files' | + 'all-files' | + 'thumbnails' | + 'thumbnails-blacklist' | + 'id' | + 'blacklist-rights' + +export type BuildVideoGetQueryOptions = { + id?: number | string + url?: string + + type: GetType + + userId?: number + transaction?: Transaction + + logging?: boolean +} + +export class VideoModelGetQueryBuilder { + videoQueryBuilder: VideosModelGetQuerySubBuilder + webtorrentFilesQueryBuilder: VideoFileQueryBuilder + streamingPlaylistFilesQueryBuilder: VideoFileQueryBuilder + + private readonly videoModelBuilder: VideoModelBuilder + + private static readonly videoFilesInclude = new Set([ 'api', 'full-light', 'account-blacklist-files', 'all-files' ]) + + 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 VideoTableAttributes('get')) + } + + async queryVideo (options: BuildVideoGetQueryOptions) { + const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([ + this.videoQueryBuilder.queryVideos(options), + + VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) + ? this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(options) + : Promise.resolve(undefined), + + VideoModelGetQueryBuilder.videoFilesInclude.has(options.type) + ? this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(options) + : Promise.resolve(undefined) + ]) + + const videos = this.videoModelBuilder.buildVideosFromRows({ + rows: videoRows, + rowsWebTorrentFiles: webtorrentFilesRows, + rowsStreamingPlaylist: streamingPlaylistFilesRows + }) + + if (videos.length > 1) { + throw new Error('Video results is more than 1') + } + + if (videos.length === 0) return null + + return videos[0] + } +} + +export class VideosModelGetQuerySubBuilder extends AbstractVideoQueryBuilder { + protected attributes: { [key: string]: string } + + protected webtorrentFilesQuery: string + protected streamingPlaylistFilesQuery: string + + private static readonly trackersInclude = new Set([ 'api' ]) + private static readonly liveInclude = new Set([ 'api', 'full-light' ]) + private static readonly scheduleUpdateInclude = new Set([ 'api', 'full-light' ]) + private static readonly tagsInclude = new Set([ 'api', 'full-light' ]) + private static readonly userHistoryInclude = new Set([ 'api', 'full-light' ]) + private static readonly accountInclude = new Set([ 'api', 'full-light', 'account-blacklist-files' ]) + private static readonly ownerUserInclude = new Set([ 'blacklist-rights' ]) + + private static readonly blacklistedInclude = new Set([ + 'api', + 'full-light', + 'account-blacklist-files', + 'thumbnails-blacklist', + 'blacklist-rights' + ]) + + private static readonly thumbnailsInclude = new Set([ + 'api', + 'full-light', + 'account-blacklist-files', + 'all-files', + 'thumbnails', + 'thumbnails-blacklist' + ]) + + constructor (protected readonly sequelize: Sequelize) { + super('get') + } + + queryVideos (options: BuildVideoGetQueryOptions) { + this.buildMainGetQuery(options) + + return this.runQuery(options) + } + + private buildMainGetQuery (options: BuildVideoGetQueryOptions) { + this.attributes = { + '"video".*': '' + } + + if (VideosModelGetQuerySubBuilder.thumbnailsInclude.has(options.type)) { + this.includeThumbnails() + } + + if (VideosModelGetQuerySubBuilder.blacklistedInclude.has(options.type)) { + this.includeBlacklisted() + } + + if (VideosModelGetQuerySubBuilder.accountInclude.has(options.type)) { + this.includeChannels() + this.includeAccounts() + } + + if (VideosModelGetQuerySubBuilder.tagsInclude.has(options.type)) { + this.includeTags() + } + + if (VideosModelGetQuerySubBuilder.scheduleUpdateInclude.has(options.type)) { + this.includeScheduleUpdate() + } + + if (VideosModelGetQuerySubBuilder.liveInclude.has(options.type)) { + this.includeLive() + } + + if (options.userId && VideosModelGetQuerySubBuilder.userHistoryInclude.has(options.type)) { + this.includeUserHistory(options.userId) + } + + if (VideosModelGetQuerySubBuilder.ownerUserInclude.has(options.type)) { + this.includeOwnerUser() + } + + if (VideosModelGetQuerySubBuilder.trackersInclude.has(options.type)) { + this.includeTrackers() + } + + this.whereId(options) + + this.query = this.buildQuery(options) + } + + private buildQuery (options: BuildVideoGetQueryOptions) { + const order = VideosModelGetQuerySubBuilder.tagsInclude.has(options.type) + ? 'ORDER BY "Tags"."name" ASC' + : '' + + const from = `SELECT * FROM "video" ${this.where} LIMIT 1` + + return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins} ${order}` + } +} diff --git a/server/models/video/sql/video/videos-id-list-query-builder.ts b/server/models/video/sql/video/videos-id-list-query-builder.ts new file mode 100644 index 000000000..098e15359 --- /dev/null +++ b/server/models/video/sql/video/videos-id-list-query-builder.ts @@ -0,0 +1,697 @@ +import { Sequelize } from 'sequelize' +import validator from 'validator' +import { exists } from '@server/helpers/custom-validators/misc' +import { WEBSERVER } from '@server/initializers/constants' +import { buildDirectionAndField, createSafeIn } from '@server/models/utils' +import { MUserAccountId, MUserId } from '@server/types/models' +import { VideoInclude, VideoPrivacy, VideoState } from '@shared/models' +import { AbstractRunQuery } from './shared/abstract-run-query' + +/** + * + * Build videos list SQL query to fetch rows + * + */ + +export type DisplayOnlyForFollowerOptions = { + actorId: number + orLocalVideos: boolean +} + +export type BuildVideosListQueryOptions = { + attributes?: string[] + + serverAccountIdForBlock: number + + displayOnlyForFollower: DisplayOnlyForFollowerOptions + + count: number + start: number + sort: string + + nsfw?: boolean + host?: string + isLive?: boolean + isLocal?: boolean + include?: VideoInclude + + categoryOneOf?: number[] + licenceOneOf?: number[] + languageOneOf?: string[] + tagsOneOf?: string[] + tagsAllOf?: string[] + privacyOneOf?: VideoPrivacy[] + + uuids?: string[] + + hasFiles?: boolean + hasHLSFiles?: boolean + hasWebtorrentFiles?: boolean + + accountId?: number + videoChannelId?: number + + videoPlaylistId?: number + + trendingAlgorithm?: string // best, hot, or any other algorithm implemented + trendingDays?: number + + user?: MUserAccountId + historyOfUser?: MUserId + + startDate?: string // ISO 8601 + endDate?: string // ISO 8601 + originallyPublishedStartDate?: string + originallyPublishedEndDate?: string + + durationMin?: number // seconds + durationMax?: number // seconds + + search?: string + + isCount?: boolean + + group?: string + having?: string +} + +export class VideosIdListQueryBuilder extends AbstractRunQuery { + protected replacements: any = {} + + private attributes: string[] + private joins: string[] = [] + + private readonly and: string[] = [] + + private readonly cte: string[] = [] + + private group = '' + private having = '' + + private sort = '' + private limit = '' + private offset = '' + + constructor (protected readonly sequelize: Sequelize) { + super() + } + + queryVideoIds (options: BuildVideosListQueryOptions) { + this.buildIdsListQuery(options) + + return this.runQuery() + } + + countVideoIds (countOptions: BuildVideosListQueryOptions): Promise { + this.buildIdsListQuery(countOptions) + + return this.runQuery().then(rows => rows.length !== 0 ? rows[0].total : 0) + } + + getQuery (options: BuildVideosListQueryOptions) { + this.buildIdsListQuery(options) + + return { query: this.query, sort: this.sort, replacements: this.replacements } + } + + private buildIdsListQuery (options: BuildVideosListQueryOptions) { + this.attributes = options.attributes || [ '"video"."id"' ] + + if (options.group) this.group = options.group + if (options.having) this.having = options.having + + this.joins = this.joins.concat([ + 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"', + 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"', + 'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"' + ]) + + if (!(options.include & VideoInclude.BLACKLISTED)) { + this.whereNotBlacklisted() + } + + if (options.serverAccountIdForBlock && !(options.include & VideoInclude.BLOCKED_OWNER)) { + this.whereNotBlocked(options.serverAccountIdForBlock, options.user) + } + + // Only list published videos + if (!(options.include & VideoInclude.NOT_PUBLISHED_STATE)) { + this.whereStateAvailable() + } + + if (options.videoPlaylistId) { + this.joinPlaylist(options.videoPlaylistId) + } + + if (exists(options.isLocal)) { + this.whereLocal(options.isLocal) + } + + if (options.host) { + this.whereHost(options.host) + } + + if (options.accountId) { + this.whereAccountId(options.accountId) + } + + if (options.videoChannelId) { + this.whereChannelId(options.videoChannelId) + } + + if (options.displayOnlyForFollower) { + this.whereFollowerActorId(options.displayOnlyForFollower) + } + + if (options.hasFiles === true) { + this.whereFileExists() + } + + if (exists(options.hasWebtorrentFiles)) { + this.whereWebTorrentFileExists(options.hasWebtorrentFiles) + } + + if (exists(options.hasHLSFiles)) { + this.whereHLSFileExists(options.hasHLSFiles) + } + + if (options.tagsOneOf) { + this.whereTagsOneOf(options.tagsOneOf) + } + + if (options.tagsAllOf) { + this.whereTagsAllOf(options.tagsAllOf) + } + + if (options.privacyOneOf) { + this.wherePrivacyOneOf(options.privacyOneOf) + } else { + // Only list videos with the appropriate priavcy + this.wherePrivacyAvailable(options.user) + } + + if (options.uuids) { + this.whereUUIDs(options.uuids) + } + + if (options.nsfw === true) { + this.whereNSFW() + } else if (options.nsfw === false) { + this.whereSFW() + } + + if (options.isLive === true) { + this.whereLive() + } else if (options.isLive === false) { + this.whereVOD() + } + + if (options.categoryOneOf) { + this.whereCategoryOneOf(options.categoryOneOf) + } + + if (options.licenceOneOf) { + this.whereLicenceOneOf(options.licenceOneOf) + } + + if (options.languageOneOf) { + this.whereLanguageOneOf(options.languageOneOf) + } + + // We don't exclude results in this so if we do a count we don't need to add this complex clause + if (options.isCount !== true) { + if (options.trendingDays) { + this.groupForTrending(options.trendingDays) + } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) { + this.groupForHotOrBest(options.trendingAlgorithm, options.user) + } + } + + if (options.historyOfUser) { + this.joinHistory(options.historyOfUser.id) + } + + if (options.startDate) { + this.whereStartDate(options.startDate) + } + + if (options.endDate) { + this.whereEndDate(options.endDate) + } + + if (options.originallyPublishedStartDate) { + this.whereOriginallyPublishedStartDate(options.originallyPublishedStartDate) + } + + if (options.originallyPublishedEndDate) { + this.whereOriginallyPublishedEndDate(options.originallyPublishedEndDate) + } + + if (options.durationMin) { + this.whereDurationMin(options.durationMin) + } + + if (options.durationMax) { + this.whereDurationMax(options.durationMax) + } + + this.whereSearch(options.search) + + if (options.isCount === true) { + this.setCountAttribute() + } else { + if (exists(options.sort)) { + this.setSort(options.sort) + } + + if (exists(options.count)) { + this.setLimit(options.count) + } + + if (exists(options.start)) { + this.setOffset(options.start) + } + } + + const cteString = this.cte.length !== 0 + ? `WITH ${this.cte.join(', ')} ` + : '' + + this.query = cteString + + 'SELECT ' + this.attributes.join(', ') + ' ' + + 'FROM "video" ' + this.joins.join(' ') + ' ' + + 'WHERE ' + this.and.join(' AND ') + ' ' + + this.group + ' ' + + this.having + ' ' + + this.sort + ' ' + + this.limit + ' ' + + this.offset + } + + private setCountAttribute () { + this.attributes = [ 'COUNT(*) as "total"' ] + } + + private joinHistory (userId: number) { + this.joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"') + + this.and.push('"userVideoHistory"."userId" = :historyOfUser') + + this.replacements.historyOfUser = userId + } + + private joinPlaylist (playlistId: number) { + this.joins.push( + 'INNER JOIN "videoPlaylistElement" "video"."id" = "videoPlaylistElement"."videoId" ' + + 'AND "videoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' + ) + + this.replacements.videoPlaylistId = playlistId + } + + private whereStateAvailable () { + this.and.push( + `("video"."state" = ${VideoState.PUBLISHED} OR ` + + `("video"."state" = ${VideoState.TO_TRANSCODE} AND "video"."waitTranscoding" IS false))` + ) + } + + private wherePrivacyAvailable (user?: MUserAccountId) { + if (user) { + this.and.push( + `("video"."privacy" = ${VideoPrivacy.PUBLIC} OR "video"."privacy" = ${VideoPrivacy.INTERNAL})` + ) + } else { // Or only public videos + this.and.push( + `"video"."privacy" = ${VideoPrivacy.PUBLIC}` + ) + } + } + + private whereLocal (isLocal: boolean) { + const isRemote = isLocal ? 'FALSE' : 'TRUE' + + this.and.push('"video"."remote" IS ' + isRemote) + } + + private whereHost (host: string) { + // Local instance + if (host === WEBSERVER.HOST) { + this.and.push('"accountActor"."serverId" IS NULL') + return + } + + this.joins.push('INNER JOIN "server" ON "server"."id" = "accountActor"."serverId"') + + this.and.push('"server"."host" = :host') + this.replacements.host = host + } + + private whereAccountId (accountId: number) { + this.and.push('"account"."id" = :accountId') + this.replacements.accountId = accountId + } + + private whereChannelId (channelId: number) { + this.and.push('"videoChannel"."id" = :videoChannelId') + this.replacements.videoChannelId = channelId + } + + private whereFollowerActorId (options: { actorId: number, orLocalVideos: boolean }) { + let query = + '(' + + ' EXISTS (' + // Videos shared by actors we follow + ' SELECT 1 FROM "videoShare" ' + + ' INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId" ' + + ' AND "actorFollowShare"."actorId" = :followerActorId AND "actorFollowShare"."state" = \'accepted\' ' + + ' WHERE "videoShare"."videoId" = "video"."id"' + + ' )' + + ' OR' + + ' EXISTS (' + // Videos published by channels or accounts we follow + ' SELECT 1 from "actorFollow" ' + + ' WHERE ("actorFollow"."targetActorId" = "account"."actorId" OR "actorFollow"."targetActorId" = "videoChannel"."actorId") ' + + ' AND "actorFollow"."actorId" = :followerActorId ' + + ' AND "actorFollow"."state" = \'accepted\'' + + ' )' + + if (options.orLocalVideos) { + query += ' OR "video"."remote" IS FALSE' + } + + query += ')' + + this.and.push(query) + this.replacements.followerActorId = options.actorId + } + + private whereFileExists () { + this.and.push(`(${this.buildWebTorrentFileExistsQuery(true)} OR ${this.buildHLSFileExistsQuery(true)})`) + } + + private whereWebTorrentFileExists (exists: boolean) { + this.and.push(this.buildWebTorrentFileExistsQuery(exists)) + } + + private whereHLSFileExists (exists: boolean) { + this.and.push(this.buildHLSFileExistsQuery(exists)) + } + + private buildWebTorrentFileExistsQuery (exists: boolean) { + const prefix = exists ? '' : 'NOT ' + + return prefix + 'EXISTS (SELECT 1 FROM "videoFile" WHERE "videoFile"."videoId" = "video"."id")' + } + + private buildHLSFileExistsQuery (exists: boolean) { + const prefix = exists ? '' : 'NOT ' + + return prefix + 'EXISTS (' + + ' SELECT 1 FROM "videoStreamingPlaylist" ' + + ' INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist"."id" ' + + ' WHERE "videoStreamingPlaylist"."videoId" = "video"."id"' + + ')' + } + + private whereTagsOneOf (tagsOneOf: string[]) { + const tagsOneOfLower = tagsOneOf.map(t => t.toLowerCase()) + + this.and.push( + 'EXISTS (' + + ' SELECT 1 FROM "videoTag" ' + + ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + + ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsOneOfLower) + ') ' + + ' AND "video"."id" = "videoTag"."videoId"' + + ')' + ) + } + + private whereTagsAllOf (tagsAllOf: string[]) { + const tagsAllOfLower = tagsAllOf.map(t => t.toLowerCase()) + + this.and.push( + 'EXISTS (' + + ' SELECT 1 FROM "videoTag" ' + + ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + + ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsAllOfLower) + ') ' + + ' AND "video"."id" = "videoTag"."videoId" ' + + ' GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length + + ')' + ) + } + + private wherePrivacyOneOf (privacyOneOf: VideoPrivacy[]) { + this.and.push('"video"."privacy" IN (:privacyOneOf)') + this.replacements.privacyOneOf = privacyOneOf + } + + private whereUUIDs (uuids: string[]) { + this.and.push('"video"."uuid" IN (' + createSafeIn(this.sequelize, uuids) + ')') + } + + private whereCategoryOneOf (categoryOneOf: number[]) { + this.and.push('"video"."category" IN (:categoryOneOf)') + this.replacements.categoryOneOf = categoryOneOf + } + + private whereLicenceOneOf (licenceOneOf: number[]) { + this.and.push('"video"."licence" IN (:licenceOneOf)') + this.replacements.licenceOneOf = licenceOneOf + } + + private whereLanguageOneOf (languageOneOf: string[]) { + const languages = languageOneOf.filter(l => l && l !== '_unknown') + const languagesQueryParts: string[] = [] + + if (languages.length !== 0) { + languagesQueryParts.push('"video"."language" IN (:languageOneOf)') + this.replacements.languageOneOf = languages + + languagesQueryParts.push( + 'EXISTS (' + + ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' + + ' IN (' + createSafeIn(this.sequelize, languages) + ') AND ' + + ' "videoCaption"."videoId" = "video"."id"' + + ')' + ) + } + + if (languageOneOf.includes('_unknown')) { + languagesQueryParts.push('"video"."language" IS NULL') + } + + if (languagesQueryParts.length !== 0) { + this.and.push('(' + languagesQueryParts.join(' OR ') + ')') + } + } + + private whereNSFW () { + this.and.push('"video"."nsfw" IS TRUE') + } + + private whereSFW () { + this.and.push('"video"."nsfw" IS FALSE') + } + + private whereLive () { + this.and.push('"video"."isLive" IS TRUE') + } + + private whereVOD () { + this.and.push('"video"."isLive" IS FALSE') + } + + private whereNotBlocked (serverAccountId: number, user?: MUserAccountId) { + const blockerIds = [ serverAccountId ] + if (user) blockerIds.push(user.Account.id) + + const inClause = createSafeIn(this.sequelize, blockerIds) + + this.and.push( + 'NOT EXISTS (' + + ' SELECT 1 FROM "accountBlocklist" ' + + ' WHERE "accountBlocklist"."accountId" IN (' + inClause + ') ' + + ' AND "accountBlocklist"."targetAccountId" = "account"."id" ' + + ')' + + 'AND NOT EXISTS (' + + ' SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN (' + inClause + ') ' + + ' AND "serverBlocklist"."targetServerId" = "accountActor"."serverId"' + + ')' + ) + } + + private whereSearch (search?: string) { + if (!search) { + this.attributes.push('0 as similarity') + return + } + + const escapedSearch = this.sequelize.escape(search) + const escapedLikeSearch = this.sequelize.escape('%' + search + '%') + + this.cte.push( + '"trigramSearch" AS (' + + ' SELECT "video"."id", ' + + ` similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity ` + + ' FROM "video" ' + + ' WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + + ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' + + ')' + ) + + this.joins.push('LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id"') + + let base = '(' + + ' "trigramSearch"."id" IS NOT NULL OR ' + + ' EXISTS (' + + ' SELECT 1 FROM "videoTag" ' + + ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + + ` WHERE lower("tag"."name") = lower(${escapedSearch}) ` + + ' AND "video"."id" = "videoTag"."videoId"' + + ' )' + + if (validator.isUUID(search)) { + base += ` OR "video"."uuid" = ${escapedSearch}` + } + + base += ')' + + this.and.push(base) + this.attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`) + } + + private whereNotBlacklisted () { + this.and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")') + } + + private whereStartDate (startDate: string) { + this.and.push('"video"."publishedAt" >= :startDate') + this.replacements.startDate = startDate + } + + private whereEndDate (endDate: string) { + this.and.push('"video"."publishedAt" <= :endDate') + this.replacements.endDate = endDate + } + + private whereOriginallyPublishedStartDate (startDate: string) { + this.and.push('"video"."originallyPublishedAt" >= :originallyPublishedStartDate') + this.replacements.originallyPublishedStartDate = startDate + } + + private whereOriginallyPublishedEndDate (endDate: string) { + this.and.push('"video"."originallyPublishedAt" <= :originallyPublishedEndDate') + this.replacements.originallyPublishedEndDate = endDate + } + + private whereDurationMin (durationMin: number) { + this.and.push('"video"."duration" >= :durationMin') + this.replacements.durationMin = durationMin + } + + private whereDurationMax (durationMax: number) { + this.and.push('"video"."duration" <= :durationMax') + this.replacements.durationMax = durationMax + } + + private groupForTrending (trendingDays: number) { + const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays) + + this.joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') + this.replacements.viewsGteDate = viewsGteDate + + this.attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"') + + this.group = 'GROUP BY "video"."id"' + } + + private groupForHotOrBest (trendingAlgorithm: string, user?: MUserAccountId) { + /** + * "Hotness" is a measure based on absolute view/comment/like/dislike numbers, + * with fixed weights only applied to their log values. + * + * This algorithm gives little chance for an old video to have a good score, + * for which recent spikes in interactions could be a sign of "hotness" and + * justify a better score. However there are multiple ways to achieve that + * goal, which is left for later. Yes, this is a TODO :) + * + * notes: + * - weights and base score are in number of half-days. + * - all comments are counted, regardless of being written by the video author or not + * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58 + * - we have less interactions than on reddit, so multiply weights by an arbitrary factor + */ + const weights = { + like: 3 * 50, + dislike: -3 * 50, + view: Math.floor((1 / 3) * 50), + comment: 2 * 50, // a comment takes more time than a like to do, but can be done multiple times + history: -2 * 50 + } + + this.joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') + + let attribute = + `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) + `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) + `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) + `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+) + '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days) + + if (trendingAlgorithm === 'best' && user) { + this.joins.push( + 'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser' + ) + this.replacements.bestUser = user.id + + attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} ` + } + + attribute += 'AS "score"' + this.attributes.push(attribute) + + this.group = 'GROUP BY "video"."id"' + } + + private setSort (sort: string) { + if (sort === '-originallyPublishedAt' || sort === 'originallyPublishedAt') { + this.attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"') + } + + this.sort = this.buildOrder(sort) + } + + private buildOrder (value: string) { + const { direction, field } = buildDirectionAndField(value) + if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) + + if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' + + if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation + return `ORDER BY "score" ${direction}, "video"."views" ${direction}` + } + + let firstSort: string + + if (field.toLowerCase() === 'match') { // Search + firstSort = '"similarity"' + } else if (field === 'originallyPublishedAt') { + firstSort = '"publishedAtForOrder"' + } else if (field.includes('.')) { + firstSort = field + } else { + firstSort = `"video"."${field}"` + } + + return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC` + } + + private setLimit (countArg: number) { + const count = parseInt(countArg + '', 10) + this.limit = `LIMIT ${count}` + } + + private setOffset (startArg: number) { + const start = parseInt(startArg + '', 10) + this.offset = `OFFSET ${start}` + } +} 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 new file mode 100644 index 000000000..b15b29ec3 --- /dev/null +++ b/server/models/video/sql/video/videos-model-list-query-builder.ts @@ -0,0 +1,80 @@ +import { VideoInclude } from '@shared/models' +import { Sequelize } from 'sequelize' +import { AbstractVideoQueryBuilder } from './shared/abstract-video-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 AbstractVideoQueryBuilder { + protected attributes: { [key: string]: string } + + private innerQuery: string + private innerSort: string + + private readonly videoModelBuilder: VideoModelBuilder + + constructor (protected readonly sequelize: Sequelize) { + super('list') + + this.videoModelBuilder = new VideoModelBuilder(this.mode, this.tables) + } + + queryVideos (options: BuildVideosListQueryOptions) { + this.buildInnerQuery(options) + this.buildMainQuery(options) + + return this.runQuery() + .then(rows => this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include })) + } + + private buildInnerQuery (options: BuildVideosListQueryOptions) { + const idsQueryBuilder = new VideosIdListQueryBuilder(this.sequelize) + const { query, sort, replacements } = idsQueryBuilder.getQuery(options) + + this.replacements = replacements + this.innerQuery = query + this.innerSort = sort + } + + private buildMainQuery (options: BuildVideosListQueryOptions) { + this.attributes = { + '"video".*': '' + } + + this.addJoin('INNER JOIN "video" ON "tmp"."id" = "video"."id"') + + this.includeChannels() + this.includeAccounts() + this.includeThumbnails() + + if (options.include & VideoInclude.FILES) { + this.includeWebtorrentFiles() + this.includeStreamingPlaylistFiles() + } + + if (options.user) { + this.includeUserHistory(options.user.id) + } + + if (options.videoPlaylistId) { + this.includePlaylist(options.videoPlaylistId) + } + + if (options.include & VideoInclude.BLACKLISTED) { + this.includeBlacklisted() + } + + if (options.include & VideoInclude.BLOCKED_OWNER) { + this.includeBlockedOwnerAndServer(options.serverAccountIdForBlock, options.user) + } + + const select = this.buildSelect() + + this.query = `${select} FROM (${this.innerQuery}) AS "tmp" ${this.joins} ${this.innerSort}` + } +} diff --git a/server/models/video/sql/videos-id-list-query-builder.ts b/server/models/video/sql/videos-id-list-query-builder.ts deleted file mode 100644 index 098e15359..000000000 --- a/server/models/video/sql/videos-id-list-query-builder.ts +++ /dev/null @@ -1,697 +0,0 @@ -import { Sequelize } from 'sequelize' -import validator from 'validator' -import { exists } from '@server/helpers/custom-validators/misc' -import { WEBSERVER } from '@server/initializers/constants' -import { buildDirectionAndField, createSafeIn } from '@server/models/utils' -import { MUserAccountId, MUserId } from '@server/types/models' -import { VideoInclude, VideoPrivacy, VideoState } from '@shared/models' -import { AbstractRunQuery } from './shared/abstract-run-query' - -/** - * - * Build videos list SQL query to fetch rows - * - */ - -export type DisplayOnlyForFollowerOptions = { - actorId: number - orLocalVideos: boolean -} - -export type BuildVideosListQueryOptions = { - attributes?: string[] - - serverAccountIdForBlock: number - - displayOnlyForFollower: DisplayOnlyForFollowerOptions - - count: number - start: number - sort: string - - nsfw?: boolean - host?: string - isLive?: boolean - isLocal?: boolean - include?: VideoInclude - - categoryOneOf?: number[] - licenceOneOf?: number[] - languageOneOf?: string[] - tagsOneOf?: string[] - tagsAllOf?: string[] - privacyOneOf?: VideoPrivacy[] - - uuids?: string[] - - hasFiles?: boolean - hasHLSFiles?: boolean - hasWebtorrentFiles?: boolean - - accountId?: number - videoChannelId?: number - - videoPlaylistId?: number - - trendingAlgorithm?: string // best, hot, or any other algorithm implemented - trendingDays?: number - - user?: MUserAccountId - historyOfUser?: MUserId - - startDate?: string // ISO 8601 - endDate?: string // ISO 8601 - originallyPublishedStartDate?: string - originallyPublishedEndDate?: string - - durationMin?: number // seconds - durationMax?: number // seconds - - search?: string - - isCount?: boolean - - group?: string - having?: string -} - -export class VideosIdListQueryBuilder extends AbstractRunQuery { - protected replacements: any = {} - - private attributes: string[] - private joins: string[] = [] - - private readonly and: string[] = [] - - private readonly cte: string[] = [] - - private group = '' - private having = '' - - private sort = '' - private limit = '' - private offset = '' - - constructor (protected readonly sequelize: Sequelize) { - super() - } - - queryVideoIds (options: BuildVideosListQueryOptions) { - this.buildIdsListQuery(options) - - return this.runQuery() - } - - countVideoIds (countOptions: BuildVideosListQueryOptions): Promise { - this.buildIdsListQuery(countOptions) - - return this.runQuery().then(rows => rows.length !== 0 ? rows[0].total : 0) - } - - getQuery (options: BuildVideosListQueryOptions) { - this.buildIdsListQuery(options) - - return { query: this.query, sort: this.sort, replacements: this.replacements } - } - - private buildIdsListQuery (options: BuildVideosListQueryOptions) { - this.attributes = options.attributes || [ '"video"."id"' ] - - if (options.group) this.group = options.group - if (options.having) this.having = options.having - - this.joins = this.joins.concat([ - 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId"', - 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId"', - 'INNER JOIN "actor" "accountActor" ON "account"."actorId" = "accountActor"."id"' - ]) - - if (!(options.include & VideoInclude.BLACKLISTED)) { - this.whereNotBlacklisted() - } - - if (options.serverAccountIdForBlock && !(options.include & VideoInclude.BLOCKED_OWNER)) { - this.whereNotBlocked(options.serverAccountIdForBlock, options.user) - } - - // Only list published videos - if (!(options.include & VideoInclude.NOT_PUBLISHED_STATE)) { - this.whereStateAvailable() - } - - if (options.videoPlaylistId) { - this.joinPlaylist(options.videoPlaylistId) - } - - if (exists(options.isLocal)) { - this.whereLocal(options.isLocal) - } - - if (options.host) { - this.whereHost(options.host) - } - - if (options.accountId) { - this.whereAccountId(options.accountId) - } - - if (options.videoChannelId) { - this.whereChannelId(options.videoChannelId) - } - - if (options.displayOnlyForFollower) { - this.whereFollowerActorId(options.displayOnlyForFollower) - } - - if (options.hasFiles === true) { - this.whereFileExists() - } - - if (exists(options.hasWebtorrentFiles)) { - this.whereWebTorrentFileExists(options.hasWebtorrentFiles) - } - - if (exists(options.hasHLSFiles)) { - this.whereHLSFileExists(options.hasHLSFiles) - } - - if (options.tagsOneOf) { - this.whereTagsOneOf(options.tagsOneOf) - } - - if (options.tagsAllOf) { - this.whereTagsAllOf(options.tagsAllOf) - } - - if (options.privacyOneOf) { - this.wherePrivacyOneOf(options.privacyOneOf) - } else { - // Only list videos with the appropriate priavcy - this.wherePrivacyAvailable(options.user) - } - - if (options.uuids) { - this.whereUUIDs(options.uuids) - } - - if (options.nsfw === true) { - this.whereNSFW() - } else if (options.nsfw === false) { - this.whereSFW() - } - - if (options.isLive === true) { - this.whereLive() - } else if (options.isLive === false) { - this.whereVOD() - } - - if (options.categoryOneOf) { - this.whereCategoryOneOf(options.categoryOneOf) - } - - if (options.licenceOneOf) { - this.whereLicenceOneOf(options.licenceOneOf) - } - - if (options.languageOneOf) { - this.whereLanguageOneOf(options.languageOneOf) - } - - // We don't exclude results in this so if we do a count we don't need to add this complex clause - if (options.isCount !== true) { - if (options.trendingDays) { - this.groupForTrending(options.trendingDays) - } else if ([ 'best', 'hot' ].includes(options.trendingAlgorithm)) { - this.groupForHotOrBest(options.trendingAlgorithm, options.user) - } - } - - if (options.historyOfUser) { - this.joinHistory(options.historyOfUser.id) - } - - if (options.startDate) { - this.whereStartDate(options.startDate) - } - - if (options.endDate) { - this.whereEndDate(options.endDate) - } - - if (options.originallyPublishedStartDate) { - this.whereOriginallyPublishedStartDate(options.originallyPublishedStartDate) - } - - if (options.originallyPublishedEndDate) { - this.whereOriginallyPublishedEndDate(options.originallyPublishedEndDate) - } - - if (options.durationMin) { - this.whereDurationMin(options.durationMin) - } - - if (options.durationMax) { - this.whereDurationMax(options.durationMax) - } - - this.whereSearch(options.search) - - if (options.isCount === true) { - this.setCountAttribute() - } else { - if (exists(options.sort)) { - this.setSort(options.sort) - } - - if (exists(options.count)) { - this.setLimit(options.count) - } - - if (exists(options.start)) { - this.setOffset(options.start) - } - } - - const cteString = this.cte.length !== 0 - ? `WITH ${this.cte.join(', ')} ` - : '' - - this.query = cteString + - 'SELECT ' + this.attributes.join(', ') + ' ' + - 'FROM "video" ' + this.joins.join(' ') + ' ' + - 'WHERE ' + this.and.join(' AND ') + ' ' + - this.group + ' ' + - this.having + ' ' + - this.sort + ' ' + - this.limit + ' ' + - this.offset - } - - private setCountAttribute () { - this.attributes = [ 'COUNT(*) as "total"' ] - } - - private joinHistory (userId: number) { - this.joins.push('INNER JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId"') - - this.and.push('"userVideoHistory"."userId" = :historyOfUser') - - this.replacements.historyOfUser = userId - } - - private joinPlaylist (playlistId: number) { - this.joins.push( - 'INNER JOIN "videoPlaylistElement" "video"."id" = "videoPlaylistElement"."videoId" ' + - 'AND "videoPlaylistElement"."videoPlaylistId" = :videoPlaylistId' - ) - - this.replacements.videoPlaylistId = playlistId - } - - private whereStateAvailable () { - this.and.push( - `("video"."state" = ${VideoState.PUBLISHED} OR ` + - `("video"."state" = ${VideoState.TO_TRANSCODE} AND "video"."waitTranscoding" IS false))` - ) - } - - private wherePrivacyAvailable (user?: MUserAccountId) { - if (user) { - this.and.push( - `("video"."privacy" = ${VideoPrivacy.PUBLIC} OR "video"."privacy" = ${VideoPrivacy.INTERNAL})` - ) - } else { // Or only public videos - this.and.push( - `"video"."privacy" = ${VideoPrivacy.PUBLIC}` - ) - } - } - - private whereLocal (isLocal: boolean) { - const isRemote = isLocal ? 'FALSE' : 'TRUE' - - this.and.push('"video"."remote" IS ' + isRemote) - } - - private whereHost (host: string) { - // Local instance - if (host === WEBSERVER.HOST) { - this.and.push('"accountActor"."serverId" IS NULL') - return - } - - this.joins.push('INNER JOIN "server" ON "server"."id" = "accountActor"."serverId"') - - this.and.push('"server"."host" = :host') - this.replacements.host = host - } - - private whereAccountId (accountId: number) { - this.and.push('"account"."id" = :accountId') - this.replacements.accountId = accountId - } - - private whereChannelId (channelId: number) { - this.and.push('"videoChannel"."id" = :videoChannelId') - this.replacements.videoChannelId = channelId - } - - private whereFollowerActorId (options: { actorId: number, orLocalVideos: boolean }) { - let query = - '(' + - ' EXISTS (' + // Videos shared by actors we follow - ' SELECT 1 FROM "videoShare" ' + - ' INNER JOIN "actorFollow" "actorFollowShare" ON "actorFollowShare"."targetActorId" = "videoShare"."actorId" ' + - ' AND "actorFollowShare"."actorId" = :followerActorId AND "actorFollowShare"."state" = \'accepted\' ' + - ' WHERE "videoShare"."videoId" = "video"."id"' + - ' )' + - ' OR' + - ' EXISTS (' + // Videos published by channels or accounts we follow - ' SELECT 1 from "actorFollow" ' + - ' WHERE ("actorFollow"."targetActorId" = "account"."actorId" OR "actorFollow"."targetActorId" = "videoChannel"."actorId") ' + - ' AND "actorFollow"."actorId" = :followerActorId ' + - ' AND "actorFollow"."state" = \'accepted\'' + - ' )' - - if (options.orLocalVideos) { - query += ' OR "video"."remote" IS FALSE' - } - - query += ')' - - this.and.push(query) - this.replacements.followerActorId = options.actorId - } - - private whereFileExists () { - this.and.push(`(${this.buildWebTorrentFileExistsQuery(true)} OR ${this.buildHLSFileExistsQuery(true)})`) - } - - private whereWebTorrentFileExists (exists: boolean) { - this.and.push(this.buildWebTorrentFileExistsQuery(exists)) - } - - private whereHLSFileExists (exists: boolean) { - this.and.push(this.buildHLSFileExistsQuery(exists)) - } - - private buildWebTorrentFileExistsQuery (exists: boolean) { - const prefix = exists ? '' : 'NOT ' - - return prefix + 'EXISTS (SELECT 1 FROM "videoFile" WHERE "videoFile"."videoId" = "video"."id")' - } - - private buildHLSFileExistsQuery (exists: boolean) { - const prefix = exists ? '' : 'NOT ' - - return prefix + 'EXISTS (' + - ' SELECT 1 FROM "videoStreamingPlaylist" ' + - ' INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist"."id" ' + - ' WHERE "videoStreamingPlaylist"."videoId" = "video"."id"' + - ')' - } - - private whereTagsOneOf (tagsOneOf: string[]) { - const tagsOneOfLower = tagsOneOf.map(t => t.toLowerCase()) - - this.and.push( - 'EXISTS (' + - ' SELECT 1 FROM "videoTag" ' + - ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + - ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsOneOfLower) + ') ' + - ' AND "video"."id" = "videoTag"."videoId"' + - ')' - ) - } - - private whereTagsAllOf (tagsAllOf: string[]) { - const tagsAllOfLower = tagsAllOf.map(t => t.toLowerCase()) - - this.and.push( - 'EXISTS (' + - ' SELECT 1 FROM "videoTag" ' + - ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + - ' WHERE lower("tag"."name") IN (' + createSafeIn(this.sequelize, tagsAllOfLower) + ') ' + - ' AND "video"."id" = "videoTag"."videoId" ' + - ' GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length + - ')' - ) - } - - private wherePrivacyOneOf (privacyOneOf: VideoPrivacy[]) { - this.and.push('"video"."privacy" IN (:privacyOneOf)') - this.replacements.privacyOneOf = privacyOneOf - } - - private whereUUIDs (uuids: string[]) { - this.and.push('"video"."uuid" IN (' + createSafeIn(this.sequelize, uuids) + ')') - } - - private whereCategoryOneOf (categoryOneOf: number[]) { - this.and.push('"video"."category" IN (:categoryOneOf)') - this.replacements.categoryOneOf = categoryOneOf - } - - private whereLicenceOneOf (licenceOneOf: number[]) { - this.and.push('"video"."licence" IN (:licenceOneOf)') - this.replacements.licenceOneOf = licenceOneOf - } - - private whereLanguageOneOf (languageOneOf: string[]) { - const languages = languageOneOf.filter(l => l && l !== '_unknown') - const languagesQueryParts: string[] = [] - - if (languages.length !== 0) { - languagesQueryParts.push('"video"."language" IN (:languageOneOf)') - this.replacements.languageOneOf = languages - - languagesQueryParts.push( - 'EXISTS (' + - ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' + - ' IN (' + createSafeIn(this.sequelize, languages) + ') AND ' + - ' "videoCaption"."videoId" = "video"."id"' + - ')' - ) - } - - if (languageOneOf.includes('_unknown')) { - languagesQueryParts.push('"video"."language" IS NULL') - } - - if (languagesQueryParts.length !== 0) { - this.and.push('(' + languagesQueryParts.join(' OR ') + ')') - } - } - - private whereNSFW () { - this.and.push('"video"."nsfw" IS TRUE') - } - - private whereSFW () { - this.and.push('"video"."nsfw" IS FALSE') - } - - private whereLive () { - this.and.push('"video"."isLive" IS TRUE') - } - - private whereVOD () { - this.and.push('"video"."isLive" IS FALSE') - } - - private whereNotBlocked (serverAccountId: number, user?: MUserAccountId) { - const blockerIds = [ serverAccountId ] - if (user) blockerIds.push(user.Account.id) - - const inClause = createSafeIn(this.sequelize, blockerIds) - - this.and.push( - 'NOT EXISTS (' + - ' SELECT 1 FROM "accountBlocklist" ' + - ' WHERE "accountBlocklist"."accountId" IN (' + inClause + ') ' + - ' AND "accountBlocklist"."targetAccountId" = "account"."id" ' + - ')' + - 'AND NOT EXISTS (' + - ' SELECT 1 FROM "serverBlocklist" WHERE "serverBlocklist"."accountId" IN (' + inClause + ') ' + - ' AND "serverBlocklist"."targetServerId" = "accountActor"."serverId"' + - ')' - ) - } - - private whereSearch (search?: string) { - if (!search) { - this.attributes.push('0 as similarity') - return - } - - const escapedSearch = this.sequelize.escape(search) - const escapedLikeSearch = this.sequelize.escape('%' + search + '%') - - this.cte.push( - '"trigramSearch" AS (' + - ' SELECT "video"."id", ' + - ` similarity(lower(immutable_unaccent("video"."name")), lower(immutable_unaccent(${escapedSearch}))) as similarity ` + - ' FROM "video" ' + - ' WHERE lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + - ' lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' + - ')' - ) - - this.joins.push('LEFT JOIN "trigramSearch" ON "video"."id" = "trigramSearch"."id"') - - let base = '(' + - ' "trigramSearch"."id" IS NOT NULL OR ' + - ' EXISTS (' + - ' SELECT 1 FROM "videoTag" ' + - ' INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + - ` WHERE lower("tag"."name") = lower(${escapedSearch}) ` + - ' AND "video"."id" = "videoTag"."videoId"' + - ' )' - - if (validator.isUUID(search)) { - base += ` OR "video"."uuid" = ${escapedSearch}` - } - - base += ')' - - this.and.push(base) - this.attributes.push(`COALESCE("trigramSearch"."similarity", 0) as similarity`) - } - - private whereNotBlacklisted () { - this.and.push('"video"."id" NOT IN (SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")') - } - - private whereStartDate (startDate: string) { - this.and.push('"video"."publishedAt" >= :startDate') - this.replacements.startDate = startDate - } - - private whereEndDate (endDate: string) { - this.and.push('"video"."publishedAt" <= :endDate') - this.replacements.endDate = endDate - } - - private whereOriginallyPublishedStartDate (startDate: string) { - this.and.push('"video"."originallyPublishedAt" >= :originallyPublishedStartDate') - this.replacements.originallyPublishedStartDate = startDate - } - - private whereOriginallyPublishedEndDate (endDate: string) { - this.and.push('"video"."originallyPublishedAt" <= :originallyPublishedEndDate') - this.replacements.originallyPublishedEndDate = endDate - } - - private whereDurationMin (durationMin: number) { - this.and.push('"video"."duration" >= :durationMin') - this.replacements.durationMin = durationMin - } - - private whereDurationMax (durationMax: number) { - this.and.push('"video"."duration" <= :durationMax') - this.replacements.durationMax = durationMax - } - - private groupForTrending (trendingDays: number) { - const viewsGteDate = new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays) - - this.joins.push('LEFT JOIN "videoView" ON "video"."id" = "videoView"."videoId" AND "videoView"."startDate" >= :viewsGteDate') - this.replacements.viewsGteDate = viewsGteDate - - this.attributes.push('COALESCE(SUM("videoView"."views"), 0) AS "score"') - - this.group = 'GROUP BY "video"."id"' - } - - private groupForHotOrBest (trendingAlgorithm: string, user?: MUserAccountId) { - /** - * "Hotness" is a measure based on absolute view/comment/like/dislike numbers, - * with fixed weights only applied to their log values. - * - * This algorithm gives little chance for an old video to have a good score, - * for which recent spikes in interactions could be a sign of "hotness" and - * justify a better score. However there are multiple ways to achieve that - * goal, which is left for later. Yes, this is a TODO :) - * - * notes: - * - weights and base score are in number of half-days. - * - all comments are counted, regardless of being written by the video author or not - * see https://github.com/reddit-archive/reddit/blob/master/r2/r2/lib/db/_sorts.pyx#L47-L58 - * - we have less interactions than on reddit, so multiply weights by an arbitrary factor - */ - const weights = { - like: 3 * 50, - dislike: -3 * 50, - view: Math.floor((1 / 3) * 50), - comment: 2 * 50, // a comment takes more time than a like to do, but can be done multiple times - history: -2 * 50 - } - - this.joins.push('LEFT JOIN "videoComment" ON "video"."id" = "videoComment"."videoId"') - - let attribute = - `LOG(GREATEST(1, "video"."likes" - 1)) * ${weights.like} ` + // likes (+) - `+ LOG(GREATEST(1, "video"."dislikes" - 1)) * ${weights.dislike} ` + // dislikes (-) - `+ LOG("video"."views" + 1) * ${weights.view} ` + // views (+) - `+ LOG(GREATEST(1, COUNT(DISTINCT "videoComment"."id"))) * ${weights.comment} ` + // comments (+) - '+ (SELECT (EXTRACT(epoch FROM "video"."publishedAt") - 1446156582) / 47000) ' // base score (in number of half-days) - - if (trendingAlgorithm === 'best' && user) { - this.joins.push( - 'LEFT JOIN "userVideoHistory" ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :bestUser' - ) - this.replacements.bestUser = user.id - - attribute += `+ POWER(COUNT(DISTINCT "userVideoHistory"."id"), 2.0) * ${weights.history} ` - } - - attribute += 'AS "score"' - this.attributes.push(attribute) - - this.group = 'GROUP BY "video"."id"' - } - - private setSort (sort: string) { - if (sort === '-originallyPublishedAt' || sort === 'originallyPublishedAt') { - this.attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"') - } - - this.sort = this.buildOrder(sort) - } - - private buildOrder (value: string) { - const { direction, field } = buildDirectionAndField(value) - if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) - - if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' - - if ([ 'trending', 'hot', 'best' ].includes(field.toLowerCase())) { // Sort by aggregation - return `ORDER BY "score" ${direction}, "video"."views" ${direction}` - } - - let firstSort: string - - if (field.toLowerCase() === 'match') { // Search - firstSort = '"similarity"' - } else if (field === 'originallyPublishedAt') { - firstSort = '"publishedAtForOrder"' - } else if (field.includes('.')) { - firstSort = field - } else { - firstSort = `"video"."${field}"` - } - - return `ORDER BY ${firstSort} ${direction}, "video"."id" ASC` - } - - private setLimit (countArg: number) { - const count = parseInt(countArg + '', 10) - this.limit = `LIMIT ${count}` - } - - private setOffset (startArg: number) { - const start = parseInt(startArg + '', 10) - this.offset = `OFFSET ${start}` - } -} diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts deleted file mode 100644 index b15b29ec3..000000000 --- a/server/models/video/sql/videos-model-list-query-builder.ts +++ /dev/null @@ -1,80 +0,0 @@ -import { VideoInclude } from '@shared/models' -import { Sequelize } from 'sequelize' -import { AbstractVideoQueryBuilder } from './shared/abstract-video-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 AbstractVideoQueryBuilder { - protected attributes: { [key: string]: string } - - private innerQuery: string - private innerSort: string - - private readonly videoModelBuilder: VideoModelBuilder - - constructor (protected readonly sequelize: Sequelize) { - super('list') - - this.videoModelBuilder = new VideoModelBuilder(this.mode, this.tables) - } - - queryVideos (options: BuildVideosListQueryOptions) { - this.buildInnerQuery(options) - this.buildMainQuery(options) - - return this.runQuery() - .then(rows => this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include })) - } - - private buildInnerQuery (options: BuildVideosListQueryOptions) { - const idsQueryBuilder = new VideosIdListQueryBuilder(this.sequelize) - const { query, sort, replacements } = idsQueryBuilder.getQuery(options) - - this.replacements = replacements - this.innerQuery = query - this.innerSort = sort - } - - private buildMainQuery (options: BuildVideosListQueryOptions) { - this.attributes = { - '"video".*': '' - } - - this.addJoin('INNER JOIN "video" ON "tmp"."id" = "video"."id"') - - this.includeChannels() - this.includeAccounts() - this.includeThumbnails() - - if (options.include & VideoInclude.FILES) { - this.includeWebtorrentFiles() - this.includeStreamingPlaylistFiles() - } - - if (options.user) { - this.includeUserHistory(options.user.id) - } - - if (options.videoPlaylistId) { - this.includePlaylist(options.videoPlaylistId) - } - - if (options.include & VideoInclude.BLACKLISTED) { - this.includeBlacklisted() - } - - if (options.include & VideoInclude.BLOCKED_OWNER) { - this.includeBlockedOwnerAndServer(options.serverAccountIdForBlock, options.user) - } - - const select = this.buildSelect() - - this.query = `${select} FROM (${this.innerQuery}) AS "tmp" ${this.joins} ${this.innerSort}` - } -} diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts index 2c6669bcb..410fd6d3f 100644 --- a/server/models/video/video-channel.ts +++ b/server/models/video/video-channel.ts @@ -31,6 +31,7 @@ import { import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants' import { sendDeleteActor } from '../../lib/activitypub/send' import { + MChannel, MChannelActor, MChannelAP, MChannelBannerAccountDefault, @@ -62,6 +63,7 @@ type AvailableForListOptions = { search?: string host?: string handles?: string[] + forCount?: boolean } type AvailableWithStatsOptions = { @@ -116,70 +118,91 @@ export type SummaryOptions = { }) } - let rootWhere: WhereOptions - if (options.handles) { - const or: WhereOptions[] = [] + if (Array.isArray(options.handles) && options.handles.length !== 0) { + const or: string[] = [] for (const handle of options.handles || []) { const [ preferredUsername, host ] = handle.split('@') if (!host || host === WEBSERVER.HOST) { - or.push({ - '$Actor.preferredUsername$': preferredUsername, - '$Actor.serverId$': null - }) + or.push(`("preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} AND "serverId" IS NULL)`) } else { - or.push({ - '$Actor.preferredUsername$': preferredUsername, - '$Actor.Server.host$': host - }) + or.push( + `(` + + `"preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} ` + + `AND "host" = ${VideoChannelModel.sequelize.escape(host)}` + + `)` + ) } } - rootWhere = { - [Op.or]: or - } + whereActorAnd.push({ + id: { + [Op.in]: literal(`(SELECT "actor".id FROM actor LEFT JOIN server on server.id = actor."serverId" WHERE ${or.join(' OR ')})`) + } + }) + } + + const channelInclude: Includeable[] = [] + const accountInclude: Includeable[] = [] + + if (options.forCount !== true) { + accountInclude.push({ + model: ServerModel, + required: false + }) + + accountInclude.push({ + model: ActorImageModel, + as: 'Avatars', + required: false + }) + + channelInclude.push({ + model: ActorImageModel, + as: 'Avatars', + required: false + }) + + channelInclude.push({ + model: ActorImageModel, + as: 'Banners', + required: false + }) + } + + if (options.forCount !== true || serverRequired) { + channelInclude.push({ + model: ServerModel, + duplicating: false, + required: serverRequired, + where: whereServer + }) } return { - where: rootWhere, include: [ { attributes: { exclude: unusedActorAttributesForAPI }, - model: ActorModel, + model: ActorModel.unscoped(), where: { [Op.and]: whereActorAnd }, - include: [ - { - model: ServerModel, - required: serverRequired, - where: whereServer - }, - { - model: ActorImageModel, - as: 'Avatar', - required: false - }, - { - model: ActorImageModel, - as: 'Banner', - required: false - } - ] + include: channelInclude }, { - model: AccountModel, + model: AccountModel.unscoped(), required: true, include: [ { attributes: { exclude: unusedActorAttributesForAPI }, - model: ActorModel, // Default scope includes avatar and server - required: true + model: ActorModel.unscoped(), + required: true, + include: accountInclude } ] } @@ -189,7 +212,7 @@ export type SummaryOptions = { [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => { const include: Includeable[] = [ { - attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ], + attributes: [ 'id', 'preferredUsername', 'url', 'serverId' ], model: ActorModel.unscoped(), required: options.actorRequired ?? true, include: [ @@ -199,8 +222,8 @@ export type SummaryOptions = { required: false }, { - model: ActorImageModel.unscoped(), - as: 'Avatar', + model: ActorImageModel, + as: 'Avatars', required: false } ] @@ -245,7 +268,7 @@ export type SummaryOptions = { { model: ActorImageModel, required: false, - as: 'Banner' + as: 'Banners' } ] } @@ -474,14 +497,14 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` order: getSort(parameters.sort) } - return VideoChannelModel - .scope({ - method: [ ScopeNames.FOR_API, { actorId } as AvailableForListOptions ] - }) - .findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows } - }) + const getScope = (forCount: boolean) => { + return { method: [ ScopeNames.FOR_API, { actorId, forCount } as AvailableForListOptions ] } + } + + return Promise.all([ + VideoChannelModel.scope(getScope(true)).count(), + VideoChannelModel.scope(getScope(false)).findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static searchForApi (options: Pick & { @@ -519,14 +542,22 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` where } - return VideoChannelModel - .scope({ - method: [ ScopeNames.FOR_API, pick(options, [ 'actorId', 'host', 'handles' ]) as AvailableForListOptions ] - }) - .findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows } - }) + const getScope = (forCount: boolean) => { + return { + method: [ + ScopeNames.FOR_API, { + ...pick(options, [ 'actorId', 'host', 'handles' ]), + + forCount + } as AvailableForListOptions + ] + } + } + + return Promise.all([ + VideoChannelModel.scope(getScope(true)).count(query), + VideoChannelModel.scope(getScope(false)).findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static listByAccountForAPI (options: { @@ -552,20 +583,26 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` } : null - const query = { - offset: options.start, - limit: options.count, - order: getSort(options.sort), - include: [ - { - model: AccountModel, - where: { - id: options.accountId - }, - required: true - } - ], - where + const getQuery = (forCount: boolean) => { + const accountModel = forCount + ? AccountModel.unscoped() + : AccountModel + + return { + offset: options.start, + limit: options.count, + order: getSort(options.sort), + include: [ + { + model: accountModel, + where: { + id: options.accountId + }, + required: true + } + ], + where + } } const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR_BANNER ] @@ -576,21 +613,19 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` }) } - return VideoChannelModel - .scope(scopes) - .findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows } - }) + return Promise.all([ + VideoChannelModel.scope(scopes).count(getQuery(true)), + VideoChannelModel.scope(scopes).findAll(getQuery(false)) + ]).then(([ total, data ]) => ({ total, data })) } - static listAllByAccount (accountId: number) { + static listAllByAccount (accountId: number): Promise { const query = { limit: CONFIG.VIDEO_CHANNELS.MAX_PER_USER, include: [ { attributes: [], - model: AccountModel, + model: AccountModel.unscoped(), where: { id: accountId }, @@ -621,7 +656,7 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` { model: ActorImageModel, required: false, - as: 'Banner' + as: 'Banners' } ] } @@ -655,7 +690,7 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` { model: ActorImageModel, required: false, - as: 'Banner' + as: 'Banners' } ] } @@ -685,7 +720,7 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` { model: ActorImageModel, required: false, - as: 'Banner' + as: 'Banners' } ] } @@ -706,6 +741,9 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` displayName: this.getDisplayName(), url: actor.url, host: actor.host, + avatars: actor.avatars, + + // TODO: remove, deprecated in 4.2 avatar: actor.avatar } } @@ -736,9 +774,16 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` support: this.support, isLocal: this.Actor.isOwned(), updatedAt: this.updatedAt, + ownerAccount: undefined, + videosCount, - viewsPerDay + viewsPerDay, + + avatars: actor.avatars, + + // TODO: remove, deprecated in 4.2 + avatar: actor.avatar } if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON() diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts index fa77455bc..2d60c6a30 100644 --- a/server/models/video/video-comment.ts +++ b/server/models/video/video-comment.ts @@ -1,5 +1,5 @@ import { uniq } from 'lodash' -import { FindAndCountOptions, FindOptions, Op, Order, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' +import { FindOptions, Op, Order, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' import { AllowNull, BelongsTo, @@ -16,8 +16,8 @@ import { } from 'sequelize-typescript' import { getServerActor } from '@server/models/application/application' import { MAccount, MAccountId, MUserAccountId } from '@server/types/models' -import { AttributesOnly } from '@shared/typescript-utils' import { VideoPrivacy } from '@shared/models' +import { AttributesOnly } from '@shared/typescript-utils' import { ActivityTagObject, ActivityTombstoneObject } from '../../../shared/models/activitypub/objects/common-objects' import { VideoCommentObject } from '../../../shared/models/activitypub/objects/video-comment-object' import { VideoComment, VideoCommentAdmin } from '../../../shared/models/videos/comment/video-comment.model' @@ -363,40 +363,43 @@ export class VideoCommentModel extends Model { + return { + offset: start, + limit: count, + order: getCommentSort(sort), + where, + include: [ + { + model: AccountModel.unscoped(), + required: true, + where: whereAccount, + include: [ + { + attributes: { + exclude: unusedActorAttributesForAPI + }, + model: forCount === true + ? ActorModel.unscoped() // Default scope includes avatar and server + : ActorModel, + required: true, + where: whereActor + } + ] + }, + { + model: VideoModel.unscoped(), + required: true, + where: whereVideo + } + ] + } } - return VideoCommentModel - .findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows } - }) + return Promise.all([ + VideoCommentModel.count(getQuery(true)), + VideoCommentModel.findAll(getQuery(false)) + ]).then(([ total, data ]) => ({ total, data })) } static async listThreadsForApi (parameters: { @@ -443,14 +446,20 @@ export class VideoCommentModel extends Model { + VideoCommentModel.scope(findScopesList).findAll(queryList), + VideoCommentModel.scope(countScopesList).count(queryList), + VideoCommentModel.count(notDeletedQueryCount) + ]).then(([ rows, count, totalNotDeletedComments ]) => { return { total: count, data: rows, totalNotDeletedComments } }) } @@ -512,11 +522,10 @@ export class VideoCommentModel extends Model { - return { total: count, data: rows } - }) + return Promise.all([ + VideoCommentModel.count(query), + VideoCommentModel.scope(scopes).findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Promise { @@ -565,7 +574,10 @@ export class VideoCommentModel extends Model(query) + return Promise.all([ + VideoCommentModel.count(query), + VideoCommentModel.findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static async listForFeed (parameters: { diff --git a/server/models/video/video-import.ts b/server/models/video/video-import.ts index 5d2b230e8..1d8296060 100644 --- a/server/models/video/video-import.ts +++ b/server/models/video/video-import.ts @@ -155,13 +155,10 @@ export class VideoImportModel extends Model(query) - .then(({ rows, count }) => { - return { - data: rows, - total: count - } - }) + return Promise.all([ + VideoImportModel.unscoped().count(query), + VideoImportModel.findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } getTargetIdentifier () { diff --git a/server/models/video/video-playlist-element.ts b/server/models/video/video-playlist-element.ts index e20e32f8b..4e4160818 100644 --- a/server/models/video/video-playlist-element.ts +++ b/server/models/video/video-playlist-element.ts @@ -23,6 +23,7 @@ import { MVideoPlaylistElementVideoUrlPlaylistPrivacy, MVideoPlaylistVideoThumbnail } from '@server/types/models/video/video-playlist-element' +import { AttributesOnly } from '@shared/typescript-utils' import { PlaylistElementObject } from '../../../shared/models/activitypub/objects/playlist-element-object' import { VideoPrivacy } from '../../../shared/models/videos' import { VideoPlaylistElement, VideoPlaylistElementType } from '../../../shared/models/videos/playlist/video-playlist-element.model' @@ -32,7 +33,6 @@ import { AccountModel } from '../account/account' import { getSort, throwIfNotValid } from '../utils' import { ForAPIOptions, ScopeNames as VideoScopeNames, VideoModel } from './video' import { VideoPlaylistModel } from './video-playlist' -import { AttributesOnly } from '@shared/typescript-utils' @Table({ tableName: 'videoPlaylistElement', @@ -208,22 +208,28 @@ export class VideoPlaylistElementModel extends Model { + return { + attributes: forCount + ? [] + : [ 'url' ], + offset: start, + limit: count, + order: getSort('position'), + where: { + videoPlaylistId + }, + transaction: t + } } - return VideoPlaylistElementModel - .findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows.map(e => e.url) } - }) + return Promise.all([ + VideoPlaylistElementModel.count(getQuery(true)), + VideoPlaylistElementModel.findAll(getQuery(false)) + ]).then(([ total, rows ]) => ({ + total, + data: rows.map(e => e.url) + })) } static loadFirstElementWithVideoThumbnail (videoPlaylistId: number): Promise { diff --git a/server/models/video/video-playlist.ts b/server/models/video/video-playlist.ts index c125db3ff..ae5e237ec 100644 --- a/server/models/video/video-playlist.ts +++ b/server/models/video/video-playlist.ts @@ -1,5 +1,5 @@ import { join } from 'path' -import { FindOptions, literal, Op, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' +import { FindOptions, Includeable, literal, Op, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' import { AllowNull, BelongsTo, @@ -86,6 +86,7 @@ type AvailableForListOptions = { host?: string uuids?: string[] withVideos?: boolean + forCount?: boolean } function getVideoLengthSelect () { @@ -239,23 +240,28 @@ function getVideoLengthSelect () { [Op.and]: whereAnd } + const include: Includeable[] = [ + { + model: AccountModel.scope({ + method: [ AccountScopeNames.SUMMARY, { whereActor, whereServer, forCount: options.forCount } as SummaryOptions ] + }), + required: true + } + ] + + if (options.forCount !== true) { + include.push({ + model: VideoChannelModel.scope(VideoChannelScopeNames.SUMMARY), + required: false + }) + } + return { attributes: { include: attributesInclude }, where, - include: [ - { - model: AccountModel.scope({ - method: [ AccountScopeNames.SUMMARY, { whereActor, whereServer } as SummaryOptions ] - }), - required: true - }, - { - model: VideoChannelModel.scope(VideoChannelScopeNames.SUMMARY), - required: false - } - ] + include } as FindOptions } })) @@ -369,12 +375,23 @@ export class VideoPlaylistModel extends Model { - return { total: count, data: rows } - }) + const scopesCount: (string | ScopeOptions)[] = [ + { + method: [ + ScopeNames.AVAILABLE_FOR_LIST, + + { + ...commonAvailableForListOptions, + + withVideos: options.withVideos || false, + forCount: true + } as AvailableForListOptions + ] + }, + ScopeNames.WITH_VIDEOS_LENGTH + ] + + return Promise.all([ + VideoPlaylistModel.scope(scopesCount).count(), + VideoPlaylistModel.scope(scopesFind).findAll(query) + ]).then(([ count, rows ]) => ({ total: count, data: rows })) } static searchForApi (options: Pick & { @@ -419,17 +450,24 @@ export class VideoPlaylistModel extends Model { + return { + attributes: forCount === true + ? [] + : [ 'url' ], + offset: start, + limit: count, + where + } } - return VideoPlaylistModel.findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows.map(p => p.url) } - }) + return Promise.all([ + VideoPlaylistModel.count(getQuery(true)), + VideoPlaylistModel.findAll(getQuery(false)) + ]).then(([ total, rows ]) => ({ + total, + data: rows.map(p => p.url) + })) } static listPlaylistIdsOf (accountId: number, videoIds: number[]): Promise { diff --git a/server/models/video/video-share.ts b/server/models/video/video-share.ts index f6659b992..ad95dec6e 100644 --- a/server/models/video/video-share.ts +++ b/server/models/video/video-share.ts @@ -183,7 +183,10 @@ export class VideoShareModel extends Model ({ total, data })) } static listRemoteShareUrlsOfLocalVideos () { diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 9111c71b0..5536334eb 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -114,9 +114,13 @@ import { videoModelToFormattedJSON } from './formatter/video-format-utils' import { ScheduleVideoUpdateModel } from './schedule-video-update' -import { VideoModelGetQueryBuilder } from './sql/video-model-get-query-builder' -import { BuildVideosListQueryOptions, DisplayOnlyForFollowerOptions, VideosIdListQueryBuilder } from './sql/videos-id-list-query-builder' -import { VideosModelListQueryBuilder } from './sql/videos-model-list-query-builder' +import { + BuildVideosListQueryOptions, + DisplayOnlyForFollowerOptions, + VideoModelGetQueryBuilder, + VideosIdListQueryBuilder, + VideosModelListQueryBuilder +} from './sql/video' import { TagModel } from './tag' import { ThumbnailModel } from './thumbnail' import { VideoBlacklistModel } from './video-blacklist' @@ -229,8 +233,8 @@ export type ForAPIOptions = { required: false }, { - model: ActorImageModel.unscoped(), - as: 'Avatar', + model: ActorImageModel, + as: 'Avatars', required: false } ] @@ -252,8 +256,8 @@ export type ForAPIOptions = { required: false }, { - model: ActorImageModel.unscoped(), - as: 'Avatar', + model: ActorImageModel, + as: 'Avatars', required: false } ] -- cgit v1.2.3