From 7e7d8e485356402e7652c61c9f004e850b0a1607 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Tue, 2 Nov 2021 11:00:40 +0100 Subject: Some fixes for admin videos list --- .../models/video/sql/shared/abstract-run-query.ts | 26 ++ .../sql/shared/abstract-video-query-builder.ts | 328 +++++++++++++++++++++ .../shared/abstract-videos-model-query-builder.ts | 328 --------------------- .../sql/shared/abstract-videos-query-builder.ts | 26 -- .../video/sql/shared/video-file-query-builder.ts | 4 +- .../models/video/sql/shared/video-model-builder.ts | 4 +- .../video/sql/shared/video-table-attributes.ts | 269 +++++++++++++++++ server/models/video/sql/shared/video-tables.ts | 271 ----------------- .../video/sql/video-model-get-query-builder.ts | 11 +- .../video/sql/videos-id-list-query-builder.ts | 6 +- .../video/sql/videos-model-list-query-builder.ts | 10 +- 11 files changed, 641 insertions(+), 642 deletions(-) create mode 100644 server/models/video/sql/shared/abstract-run-query.ts create mode 100644 server/models/video/sql/shared/abstract-video-query-builder.ts delete mode 100644 server/models/video/sql/shared/abstract-videos-model-query-builder.ts delete mode 100644 server/models/video/sql/shared/abstract-videos-query-builder.ts create mode 100644 server/models/video/sql/shared/video-table-attributes.ts delete mode 100644 server/models/video/sql/shared/video-tables.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 new file mode 100644 index 000000000..8e7a7642d --- /dev/null +++ b/server/models/video/sql/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/shared/abstract-video-query-builder.ts b/server/models/video/sql/shared/abstract-video-query-builder.ts new file mode 100644 index 000000000..a6afb04e4 --- /dev/null +++ b/server/models/video/sql/shared/abstract-video-query-builder.ts @@ -0,0 +1,328 @@ +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/abstract-videos-model-query-builder.ts b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts deleted file mode 100644 index 29827db2a..000000000 --- a/server/models/video/sql/shared/abstract-videos-model-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 { AbstractVideosQueryBuilder } from './abstract-videos-query-builder' -import { VideoTables } from './video-tables' - -/** - * - * Abstract builder to create SQL query and fetch video models - * - */ - -export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder { - protected attributes: { [key: string]: string } = {} - - protected joins = '' - protected where: string - - protected tables: VideoTables - - constructor (protected readonly mode: 'list' | 'get') { - super() - - this.tables = new VideoTables(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/abstract-videos-query-builder.ts b/server/models/video/sql/shared/abstract-videos-query-builder.ts deleted file mode 100644 index 09776bcb0..000000000 --- a/server/models/video/sql/shared/abstract-videos-query-builder.ts +++ /dev/null @@ -1,26 +0,0 @@ -import { QueryTypes, Sequelize, Transaction } from 'sequelize' - -/** - * - * Abstact builder to run video SQL queries - * - */ - -export class AbstractVideosQueryBuilder { - 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/video-file-query-builder.ts b/server/models/video/sql/shared/video-file-query-builder.ts index 6b15c3b69..3eb3dc07d 100644 --- a/server/models/video/sql/shared/video-file-query-builder.ts +++ b/server/models/video/sql/shared/video-file-query-builder.ts @@ -1,6 +1,6 @@ import { Sequelize } from 'sequelize' import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder' -import { AbstractVideosModelQueryBuilder } from './abstract-videos-model-query-builder' +import { AbstractVideoQueryBuilder } from './abstract-video-query-builder' /** * @@ -8,7 +8,7 @@ import { AbstractVideosModelQueryBuilder } from './abstract-videos-model-query-b * */ -export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder { +export class VideoFileQueryBuilder extends AbstractVideoQueryBuilder { protected attributes: { [key: string]: string } constructor (protected readonly sequelize: Sequelize) { diff --git a/server/models/video/sql/shared/video-model-builder.ts b/server/models/video/sql/shared/video-model-builder.ts index 0eac95661..7751d8e68 100644 --- a/server/models/video/sql/shared/video-model-builder.ts +++ b/server/models/video/sql/shared/video-model-builder.ts @@ -18,7 +18,7 @@ import { VideoChannelModel } from '../../video-channel' import { VideoFileModel } from '../../video-file' import { VideoLiveModel } from '../../video-live' import { VideoStreamingPlaylistModel } from '../../video-streaming-playlist' -import { VideoTables } from './video-tables' +import { VideoTableAttributes } from './video-table-attributes' type SQLRow = { [id: string]: string | number } @@ -51,7 +51,7 @@ export class VideoModelBuilder { constructor ( readonly mode: 'get' | 'list', - readonly tables: VideoTables + readonly tables: VideoTableAttributes ) { } diff --git a/server/models/video/sql/shared/video-table-attributes.ts b/server/models/video/sql/shared/video-table-attributes.ts new file mode 100644 index 000000000..8a8d2073a --- /dev/null +++ b/server/models/video/sql/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', + '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/shared/video-tables.ts b/server/models/video/sql/shared/video-tables.ts deleted file mode 100644 index 042b9d5da..000000000 --- a/server/models/video/sql/shared/video-tables.ts +++ /dev/null @@ -1,271 +0,0 @@ - -/** - * - * Class to build video attributes/join names we want to fetch from the database - * - */ -export class VideoTables { - - 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 () { - let playlistKeys = [ 'id', 'playlistUrl', 'playlistFilename', 'type' ] - - if (this.mode === 'get') { - playlistKeys = playlistKeys.concat([ - 'p2pMediaLoaderInfohashes', - 'p2pMediaLoaderPeerVersion', - 'segmentsSha256Filename', - 'segmentsSha256Url', - 'videoId', - 'createdAt', - 'updatedAt', - 'storage' - ]) - } - - return playlistKeys - } - - getUserHistoryAttributes () { - return [ 'id', 'currentTime' ] - } - - getPlaylistAttributes () { - return [ - 'createdAt', - 'updatedAt', - 'url', - 'position', - 'startTimestamp', - 'stopTimestamp', - 'videoPlaylistId' - ] - } - - getTagAttributes () { - return [ 'id', 'name' ] - } - - getVideoTagAttributes () { - return [ 'videoId', 'tagId', 'createdAt', 'updatedAt' ] - } - - getBlacklistedAttributes () { - return [ 'id', 'reason', 'unfederated' ] - } - - 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 index 2f34d5602..a65c96097 100644 --- a/server/models/video/sql/video-model-get-query-builder.ts +++ b/server/models/video/sql/video-model-get-query-builder.ts @@ -1,8 +1,8 @@ import { Sequelize, Transaction } from 'sequelize' -import { AbstractVideosModelQueryBuilder } from './shared/abstract-videos-model-query-builder' +import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' import { VideoFileQueryBuilder } from './shared/video-file-query-builder' import { VideoModelBuilder } from './shared/video-model-builder' -import { VideoTables } from './shared/video-tables' +import { VideoTableAttributes } from './shared/video-table-attributes' /** * @@ -46,7 +46,7 @@ export class VideoModelGetQueryBuilder { this.webtorrentFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) this.streamingPlaylistFilesQueryBuilder = new VideoFileQueryBuilder(sequelize) - this.videoModelBuilder = new VideoModelBuilder('get', new VideoTables('get')) + this.videoModelBuilder = new VideoModelBuilder('get', new VideoTableAttributes('get')) } async queryVideo (options: BuildVideoGetQueryOptions) { @@ -69,15 +69,16 @@ export class VideoModelGetQueryBuilder { }) if (videos.length > 1) { - throw new Error('Video results is more than ') + throw new Error('Video results is more than 1') } if (videos.length === 0) return null + return videos[0] } } -export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuilder { +export class VideosModelGetQuerySubBuilder extends AbstractVideoQueryBuilder { protected attributes: { [key: string]: string } protected webtorrentFilesQuery: string diff --git a/server/models/video/sql/videos-id-list-query-builder.ts b/server/models/video/sql/videos-id-list-query-builder.ts index 4d6e0ea4b..5064afafe 100644 --- a/server/models/video/sql/videos-id-list-query-builder.ts +++ b/server/models/video/sql/videos-id-list-query-builder.ts @@ -5,7 +5,7 @@ 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 { AbstractVideosQueryBuilder } from './shared/abstract-videos-query-builder' +import { AbstractRunQuery } from './shared/abstract-run-query' /** * @@ -72,7 +72,7 @@ export type BuildVideosListQueryOptions = { having?: string } -export class VideosIdListQueryBuilder extends AbstractVideosQueryBuilder { +export class VideosIdListQueryBuilder extends AbstractRunQuery { protected replacements: any = {} private attributes: string[] @@ -105,7 +105,7 @@ export class VideosIdListQueryBuilder extends AbstractVideosQueryBuilder { return this.runQuery().then(rows => rows.length !== 0 ? rows[0].total : 0) } - getIdsListQueryAndSort (options: BuildVideosListQueryOptions) { + getQuery (options: BuildVideosListQueryOptions) { this.buildIdsListQuery(options) return { query: this.query, sort: this.sort, replacements: this.replacements } diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts index cd721f055..b15b29ec3 100644 --- a/server/models/video/sql/videos-model-list-query-builder.ts +++ b/server/models/video/sql/videos-model-list-query-builder.ts @@ -1,6 +1,6 @@ import { VideoInclude } from '@shared/models' import { Sequelize } from 'sequelize' -import { AbstractVideosModelQueryBuilder } from './shared/abstract-videos-model-query-builder' +import { AbstractVideoQueryBuilder } from './shared/abstract-video-query-builder' import { VideoModelBuilder } from './shared/video-model-builder' import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder' @@ -10,7 +10,7 @@ import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos- * */ -export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder { +export class VideosModelListQueryBuilder extends AbstractVideoQueryBuilder { protected attributes: { [key: string]: string } private innerQuery: string @@ -26,7 +26,7 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder queryVideos (options: BuildVideosListQueryOptions) { this.buildInnerQuery(options) - this.buildListQueryFromIdsQuery(options) + this.buildMainQuery(options) return this.runQuery() .then(rows => this.videoModelBuilder.buildVideosFromRows({ rows, include: options.include })) @@ -34,14 +34,14 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder private buildInnerQuery (options: BuildVideosListQueryOptions) { const idsQueryBuilder = new VideosIdListQueryBuilder(this.sequelize) - const { query, sort, replacements } = idsQueryBuilder.getIdsListQueryAndSort(options) + const { query, sort, replacements } = idsQueryBuilder.getQuery(options) this.replacements = replacements this.innerQuery = query this.innerSort = sort } - private buildListQueryFromIdsQuery (options: BuildVideosListQueryOptions) { + private buildMainQuery (options: BuildVideosListQueryOptions) { this.attributes = { '"video".*': '' } -- cgit v1.2.3