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 --- server/controllers/activitypub/client.ts | 15 +- server/controllers/api/accounts.ts | 2 +- server/controllers/api/users/me.ts | 19 +- server/controllers/api/users/my-notifications.ts | 2 +- server/controllers/api/video-channel.ts | 20 +- server/controllers/client.ts | 4 +- server/controllers/lazy-static.ts | 10 +- server/helpers/activitypub.ts | 3 + server/initializers/constants.ts | 30 +- .../migrations/0685-multiple-actor-images.ts | 62 ++ server/lib/activitypub/actors/image.ts | 89 +-- server/lib/activitypub/actors/shared/creator.ts | 16 +- .../actors/shared/object-to-model-attributes.ts | 56 +- server/lib/activitypub/actors/updater.ts | 12 +- server/lib/actor-image.ts | 14 + server/lib/client-html.ts | 10 +- server/lib/local-actor.ts | 89 +-- .../lib/notifier/shared/comment/comment-mention.ts | 2 +- .../shared/comment/new-comment-for-video-owner.ts | 2 +- server/models/abuse/abuse-message.ts | 32 +- server/models/account/account-blocklist.ts | 83 ++- server/models/account/account-video-rate.ts | 56 +- server/models/account/account.ts | 59 +- server/models/actor/actor-follow.ts | 263 ++++---- server/models/actor/actor-image.ts | 67 +- server/models/actor/actor.ts | 129 ++-- server/models/server/plugin.ts | 9 +- server/models/server/server-blocklist.ts | 13 +- server/models/shared/index.ts | 1 + server/models/shared/model-builder.ts | 101 +++ .../sql/user-notitication-list-query-builder.ts | 269 ++++++++ server/models/user/user-notification.ts | 275 ++------ server/models/user/user.ts | 13 +- server/models/utils.ts | 2 +- .../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 +- server/tests/api/check-params/video-channels.ts | 2 +- server/tests/api/moderation/abuses.ts | 7 +- server/tests/api/moderation/blocklist.ts | 4 +- server/tests/api/moderation/video-blacklist.ts | 2 + .../tests/api/notifications/notifications-api.ts | 10 + .../search/search-activitypub-video-channels.ts | 4 + .../search/search-activitypub-video-playlists.ts | 2 + .../tests/api/search/search-activitypub-videos.ts | 4 + server/tests/api/search/search-channels.ts | 8 +- server/tests/api/search/search-index.ts | 14 +- server/tests/api/search/search-playlists.ts | 6 +- server/tests/api/search/search-videos.ts | 8 +- server/tests/api/server/homepage.ts | 6 +- server/tests/api/users/user-subscriptions.ts | 4 + server/tests/api/users/users-multiple-servers.ts | 18 +- server/tests/api/users/users.ts | 8 +- server/tests/api/videos/multiple-servers.ts | 7 +- server/tests/api/videos/single-server.ts | 11 +- server/tests/api/videos/video-channels.ts | 32 +- server/tests/api/videos/video-comments.ts | 17 +- server/tests/api/videos/video-playlists.ts | 2 + server/tests/api/videos/videos-common-filters.ts | 4 +- server/tests/cli/prune-storage.ts | 29 +- server/tests/feeds/feeds.ts | 4 +- server/tests/fixtures/avatar-resized-120x120.gif | Bin 0 -> 88318 bytes server/tests/fixtures/avatar-resized-120x120.png | Bin 0 -> 1727 bytes server/tests/fixtures/avatar-resized-48x48.gif | Bin 0 -> 20462 bytes server/tests/fixtures/avatar-resized-48x48.png | Bin 0 -> 727 bytes server/tests/fixtures/avatar-resized.gif | Bin 88318 -> 0 bytes server/tests/fixtures/avatar-resized.png | Bin 1727 -> 0 bytes server/tests/fixtures/avatar2-resized-120x120.png | Bin 0 -> 1725 bytes server/tests/fixtures/avatar2-resized-48x48.png | Bin 0 -> 760 bytes server/tests/fixtures/avatar2-resized.png | Bin 1725 -> 0 bytes server/tests/shared/notifications.ts | 11 +- server/types/models/actor/actor-image.ts | 2 +- server/types/models/actor/actor.ts | 37 +- server/types/models/user/user-notification.ts | 9 +- 95 files changed, 3674 insertions(+), 2999 deletions(-) create mode 100644 server/initializers/migrations/0685-multiple-actor-images.ts create mode 100644 server/lib/actor-image.ts create mode 100644 server/models/shared/model-builder.ts create mode 100644 server/models/user/sql/user-notitication-list-query-builder.ts 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 create mode 100644 server/tests/fixtures/avatar-resized-120x120.gif create mode 100644 server/tests/fixtures/avatar-resized-120x120.png create mode 100644 server/tests/fixtures/avatar-resized-48x48.gif create mode 100644 server/tests/fixtures/avatar-resized-48x48.png delete mode 100644 server/tests/fixtures/avatar-resized.gif delete mode 100644 server/tests/fixtures/avatar-resized.png create mode 100644 server/tests/fixtures/avatar2-resized-120x120.png create mode 100644 server/tests/fixtures/avatar2-resized-48x48.png delete mode 100644 server/tests/fixtures/avatar2-resized.png (limited to 'server') diff --git a/server/controllers/activitypub/client.ts b/server/controllers/activitypub/client.ts index 4e6bd5e25..c4d1be121 100644 --- a/server/controllers/activitypub/client.ts +++ b/server/controllers/activitypub/client.ts @@ -18,10 +18,10 @@ import { } from '../../lib/activitypub/url' import { asyncMiddleware, + ensureIsLocalChannel, executeIfActivityPub, localAccountValidator, videoChannelsNameWithHostValidator, - ensureIsLocalChannel, videosCustomGetValidator, videosShareValidator } from '../../middlewares' @@ -265,8 +265,8 @@ async function videoAnnouncesController (req: express.Request, res: express.Resp const handler = async (start: number, count: number) => { const result = await VideoShareModel.listAndCountByVideoId(video.id, start, count) return { - total: result.count, - data: result.rows.map(r => r.url) + total: result.total, + data: result.data.map(r => r.url) } } const json = await activityPubCollectionPagination(getLocalVideoSharesActivityPubUrl(video), handler, req.query.page) @@ -301,9 +301,10 @@ async function videoCommentsController (req: express.Request, res: express.Respo const handler = async (start: number, count: number) => { const result = await VideoCommentModel.listAndCountByVideoForAP(video, start, count) + return { - total: result.count, - data: result.rows.map(r => r.url) + total: result.total, + data: result.data.map(r => r.url) } } const json = await activityPubCollectionPagination(getLocalVideoCommentsActivityPubUrl(video), handler, req.query.page) @@ -425,8 +426,8 @@ function videoRates (req: express.Request, rateType: VideoRateType, video: MVide const handler = async (start: number, count: number) => { const result = await AccountVideoRateModel.listAndCountAccountUrlsByVideoId(rateType, video.id, start, count) return { - total: result.count, - data: result.rows.map(r => r.url) + total: result.total, + data: result.data.map(r => r.url) } } return activityPubCollectionPagination(url, handler, req.query.page) diff --git a/server/controllers/api/accounts.ts b/server/controllers/api/accounts.ts index 46d89bafa..8d9f92d93 100644 --- a/server/controllers/api/accounts.ts +++ b/server/controllers/api/accounts.ts @@ -213,7 +213,7 @@ async function listAccountRatings (req: express.Request, res: express.Response) sort: req.query.sort, type: req.query.rating }) - return res.json(getFormattedObjects(resultList.rows, resultList.count)) + return res.json(getFormattedObjects(resultList.data, resultList.total)) } async function listAccountFollowers (req: express.Request, res: express.Response) { diff --git a/server/controllers/api/users/me.ts b/server/controllers/api/users/me.ts index c2ad0b710..a1d621152 100644 --- a/server/controllers/api/users/me.ts +++ b/server/controllers/api/users/me.ts @@ -1,7 +1,9 @@ import 'multer' import express from 'express' import { auditLoggerFactory, getAuditIdFromRes, UserAuditView } from '@server/helpers/audit-logger' +import { getBiggestActorImage } from '@server/lib/actor-image' import { Hooks } from '@server/lib/plugins/hooks' +import { pick } from '@shared/core-utils' import { ActorImageType, HttpStatusCode, UserUpdateMe, UserVideoQuota, UserVideoRate as FormattedUserVideoRate } from '@shared/models' import { AttributesOnly } from '@shared/typescript-utils' import { createReqFiles } from '../../../helpers/express-utils' @@ -10,7 +12,7 @@ import { CONFIG } from '../../../initializers/config' import { MIMETYPES } from '../../../initializers/constants' import { sequelizeTypescript } from '../../../initializers/database' import { sendUpdateActor } from '../../../lib/activitypub/send' -import { deleteLocalActorImageFile, updateLocalActorImageFile } from '../../../lib/local-actor' +import { deleteLocalActorImageFile, updateLocalActorImageFiles } from '../../../lib/local-actor' import { getOriginalVideoFileTotalDailyFromUser, getOriginalVideoFileTotalFromUser, sendVerifyUserEmail } from '../../../lib/user' import { asyncMiddleware, @@ -30,7 +32,6 @@ import { AccountVideoRateModel } from '../../../models/account/account-video-rat import { UserModel } from '../../../models/user/user' import { VideoModel } from '../../../models/video/video' import { VideoImportModel } from '../../../models/video/video-import' -import { pick } from '@shared/core-utils' const auditLogger = auditLoggerFactory('users') @@ -253,9 +254,17 @@ async function updateMyAvatar (req: express.Request, res: express.Response) { const userAccount = await AccountModel.load(user.Account.id) - const avatar = await updateLocalActorImageFile(userAccount, avatarPhysicalFile, ActorImageType.AVATAR) + const avatars = await updateLocalActorImageFiles( + userAccount, + avatarPhysicalFile, + ActorImageType.AVATAR + ) - return res.json({ avatar: avatar.toFormattedJSON() }) + return res.json({ + // TODO: remove, deprecated in 4.2 + avatar: getBiggestActorImage(avatars).toFormattedJSON(), + avatars: avatars.map(avatar => avatar.toFormattedJSON()) + }) } async function deleteMyAvatar (req: express.Request, res: express.Response) { @@ -264,5 +273,5 @@ async function deleteMyAvatar (req: express.Request, res: express.Response) { const userAccount = await AccountModel.load(user.Account.id) await deleteLocalActorImageFile(userAccount, ActorImageType.AVATAR) - return res.status(HttpStatusCode.NO_CONTENT_204).end() + return res.json({ avatars: [] }) } diff --git a/server/controllers/api/users/my-notifications.ts b/server/controllers/api/users/my-notifications.ts index d107a306e..58732158f 100644 --- a/server/controllers/api/users/my-notifications.ts +++ b/server/controllers/api/users/my-notifications.ts @@ -3,7 +3,6 @@ import express from 'express' import { UserNotificationModel } from '@server/models/user/user-notification' import { HttpStatusCode } from '../../../../shared/models/http/http-error-codes' import { UserNotificationSetting } from '../../../../shared/models/users' -import { getFormattedObjects } from '../../../helpers/utils' import { asyncMiddleware, asyncRetryTransactionMiddleware, @@ -20,6 +19,7 @@ import { } from '../../../middlewares/validators/user-notifications' import { UserNotificationSettingModel } from '../../../models/user/user-notification-setting' import { meRouter } from './me' +import { getFormattedObjects } from '@server/helpers/utils' const myNotificationsRouter = express.Router() diff --git a/server/controllers/api/video-channel.ts b/server/controllers/api/video-channel.ts index e65550a22..2f869d9b3 100644 --- a/server/controllers/api/video-channel.ts +++ b/server/controllers/api/video-channel.ts @@ -1,5 +1,6 @@ import express from 'express' import { pickCommonVideoQuery } from '@server/helpers/query' +import { getBiggestActorImage } from '@server/lib/actor-image' import { Hooks } from '@server/lib/plugins/hooks' import { ActorFollowModel } from '@server/models/actor/actor-follow' import { getServerActor } from '@server/models/application/application' @@ -16,7 +17,7 @@ import { MIMETYPES } from '../../initializers/constants' import { sequelizeTypescript } from '../../initializers/database' import { sendUpdateActor } from '../../lib/activitypub/send' import { JobQueue } from '../../lib/job-queue' -import { deleteLocalActorImageFile, updateLocalActorImageFile } from '../../lib/local-actor' +import { deleteLocalActorImageFile, updateLocalActorImageFiles } from '../../lib/local-actor' import { createLocalVideoChannel, federateAllVideosOfChannel } from '../../lib/video-channel' import { asyncMiddleware, @@ -186,11 +187,15 @@ async function updateVideoChannelBanner (req: express.Request, res: express.Resp const videoChannel = res.locals.videoChannel const oldVideoChannelAuditKeys = new VideoChannelAuditView(videoChannel.toFormattedJSON()) - const banner = await updateLocalActorImageFile(videoChannel, bannerPhysicalFile, ActorImageType.BANNER) + const banners = await updateLocalActorImageFiles(videoChannel, bannerPhysicalFile, ActorImageType.BANNER) auditLogger.update(getAuditIdFromRes(res), new VideoChannelAuditView(videoChannel.toFormattedJSON()), oldVideoChannelAuditKeys) - return res.json({ banner: banner.toFormattedJSON() }) + return res.json({ + // TODO: remove, deprecated in 4.2 + banner: getBiggestActorImage(banners).toFormattedJSON(), + banners: banners.map(b => b.toFormattedJSON()) + }) } async function updateVideoChannelAvatar (req: express.Request, res: express.Response) { @@ -198,11 +203,14 @@ async function updateVideoChannelAvatar (req: express.Request, res: express.Resp const videoChannel = res.locals.videoChannel const oldVideoChannelAuditKeys = new VideoChannelAuditView(videoChannel.toFormattedJSON()) - const avatar = await updateLocalActorImageFile(videoChannel, avatarPhysicalFile, ActorImageType.AVATAR) - + const avatars = await updateLocalActorImageFiles(videoChannel, avatarPhysicalFile, ActorImageType.AVATAR) auditLogger.update(getAuditIdFromRes(res), new VideoChannelAuditView(videoChannel.toFormattedJSON()), oldVideoChannelAuditKeys) - return res.json({ avatar: avatar.toFormattedJSON() }) + return res.json({ + // TODO: remove, deprecated in 4.2 + avatar: getBiggestActorImage(avatars).toFormattedJSON(), + avatars: avatars.map(a => a.toFormattedJSON()) + }) } async function deleteVideoChannelAvatar (req: express.Request, res: express.Response) { diff --git a/server/controllers/client.ts b/server/controllers/client.ts index 8a56f2f75..f9514d988 100644 --- a/server/controllers/client.ts +++ b/server/controllers/client.ts @@ -68,7 +68,9 @@ const staticClientOverrides = [ 'assets/images/icons/icon-512x512.png', 'assets/images/default-playlist.jpg', 'assets/images/default-avatar-account.png', - 'assets/images/default-avatar-video-channel.png' + 'assets/images/default-avatar-account-48x48.png', + 'assets/images/default-avatar-video-channel.png', + 'assets/images/default-avatar-video-channel-48x48.png' ] for (const staticClientOverride of staticClientOverrides) { diff --git a/server/controllers/lazy-static.ts b/server/controllers/lazy-static.ts index a4076ee56..55bf02660 100644 --- a/server/controllers/lazy-static.ts +++ b/server/controllers/lazy-static.ts @@ -64,7 +64,15 @@ async function getActorImage (req: express.Request, res: express.Response, next: logger.info('Lazy serve remote actor image %s.', image.fileUrl) try { - await pushActorImageProcessInQueue({ filename: image.filename, fileUrl: image.fileUrl, type: image.type }) + await pushActorImageProcessInQueue({ + filename: image.filename, + fileUrl: image.fileUrl, + size: { + height: image.height, + width: image.width + }, + type: image.type + }) } catch (err) { logger.warn('Cannot process remote actor image %s.', image.fileUrl, { err }) return res.status(HttpStatusCode.NOT_FOUND_404).end() diff --git a/server/helpers/activitypub.ts b/server/helpers/activitypub.ts index fe721cbac..cbba2f51c 100644 --- a/server/helpers/activitypub.ts +++ b/server/helpers/activitypub.ts @@ -38,6 +38,9 @@ function getContextData (type: ContextType) { sensitive: 'as:sensitive', language: 'sc:inLanguage', + // TODO: remove in a few versions, introduced in 4.2 + icons: 'as:icon', + isLiveBroadcast: 'sc:isLiveBroadcast', liveSaveReplay: { '@type': 'sc:Boolean', diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts index 1c47d43f0..9b972b87e 100644 --- a/server/initializers/constants.ts +++ b/server/initializers/constants.ts @@ -14,7 +14,7 @@ import { VideoTranscodingFPS } from '../../shared/models' import { ActivityPubActorType } from '../../shared/models/activitypub' -import { FollowState } from '../../shared/models/actors' +import { ActorImageType, FollowState } from '../../shared/models/actors' import { NSFWPolicyType } from '../../shared/models/videos/nsfw-policy.type' import { VideoPlaylistPrivacy } from '../../shared/models/videos/playlist/video-playlist-privacy.model' import { VideoPlaylistType } from '../../shared/models/videos/playlist/video-playlist-type.model' @@ -24,7 +24,7 @@ import { CONFIG, registerConfigChangedHandler } from './config' // --------------------------------------------------------------------------- -const LAST_MIGRATION_VERSION = 680 +const LAST_MIGRATION_VERSION = 685 // --------------------------------------------------------------------------- @@ -633,15 +633,23 @@ const PREVIEWS_SIZE = { height: 480, minWidth: 400 } -const ACTOR_IMAGES_SIZE = { - AVATARS: { - width: 120, - height: 120 - }, - BANNERS: { - width: 1920, - height: 317 // 6/1 ratio - } +const ACTOR_IMAGES_SIZE: { [key in ActorImageType]: { width: number, height: number }[]} = { + [ActorImageType.AVATAR]: [ + { + width: 120, + height: 120 + }, + { + width: 48, + height: 48 + } + ], + [ActorImageType.BANNER]: [ + { + width: 1920, + height: 317 // 6/1 ratio + } + ] } const EMBED_SIZE = { diff --git a/server/initializers/migrations/0685-multiple-actor-images.ts b/server/initializers/migrations/0685-multiple-actor-images.ts new file mode 100644 index 000000000..c656f7e28 --- /dev/null +++ b/server/initializers/migrations/0685-multiple-actor-images.ts @@ -0,0 +1,62 @@ +import * as Sequelize from 'sequelize' + +async function up (utils: { + transaction: Sequelize.Transaction + queryInterface: Sequelize.QueryInterface + sequelize: Sequelize.Sequelize + db: any +}): Promise { + { + await utils.queryInterface.addColumn('actorImage', 'actorId', { + type: Sequelize.INTEGER, + defaultValue: null, + allowNull: true, + references: { + model: 'actor', + key: 'id' + }, + onDelete: 'CASCADE' + }, { transaction: utils.transaction }) + + // Avatars + { + const query = `UPDATE "actorImage" SET "actorId" = (SELECT "id" FROM "actor" WHERE "actor"."avatarId" = "actorImage"."id") ` + + `WHERE "type" = 1` + await utils.sequelize.query(query, { type: Sequelize.QueryTypes.UPDATE, transaction: utils.transaction }) + } + + // Banners + { + const query = `UPDATE "actorImage" SET "actorId" = (SELECT "id" FROM "actor" WHERE "actor"."bannerId" = "actorImage"."id") ` + + `WHERE "type" = 2` + await utils.sequelize.query(query, { type: Sequelize.QueryTypes.UPDATE, transaction: utils.transaction }) + } + + // Remove orphans + { + const query = `DELETE FROM "actorImage" WHERE id NOT IN (` + + `SELECT "bannerId" FROM actor WHERE "bannerId" IS NOT NULL ` + + `UNION select "avatarId" FROM actor WHERE "avatarId" IS NOT NULL` + + `);` + + await utils.sequelize.query(query, { type: Sequelize.QueryTypes.DELETE, transaction: utils.transaction }) + } + + await utils.queryInterface.changeColumn('actorImage', 'actorId', { + type: Sequelize.INTEGER, + allowNull: false + }, { transaction: utils.transaction }) + + await utils.queryInterface.removeColumn('actor', 'avatarId', { transaction: utils.transaction }) + await utils.queryInterface.removeColumn('actor', 'bannerId', { transaction: utils.transaction }) + } +} + +function down () { + throw new Error('Not implemented.') +} + +export { + up, + down +} diff --git a/server/lib/activitypub/actors/image.ts b/server/lib/activitypub/actors/image.ts index 443ad0a63..d17c2ef1a 100644 --- a/server/lib/activitypub/actors/image.ts +++ b/server/lib/activitypub/actors/image.ts @@ -12,53 +12,52 @@ type ImageInfo = { onDisk?: boolean } -async function updateActorImageInstance (actor: MActorImages, type: ActorImageType, imageInfo: ImageInfo | null, t: Transaction) { - const oldImageModel = type === ActorImageType.AVATAR - ? actor.Avatar - : actor.Banner +async function updateActorImages (actor: MActorImages, type: ActorImageType, imagesInfo: ImageInfo[], t: Transaction) { + const avatarsOrBanners = type === ActorImageType.AVATAR + ? actor.Avatars + : actor.Banners - if (oldImageModel) { - // Don't update the avatar if the file URL did not change - if (imageInfo?.fileUrl && oldImageModel.fileUrl === imageInfo.fileUrl) return actor + if (imagesInfo.length === 0) { + await deleteActorImages(actor, type, t) + } + + for (const imageInfo of imagesInfo) { + const oldImageModel = (avatarsOrBanners || []).find(i => i.width === imageInfo.width) - try { - await oldImageModel.destroy({ transaction: t }) + if (oldImageModel) { + // Don't update the avatar if the file URL did not change + if (imageInfo?.fileUrl && oldImageModel.fileUrl === imageInfo.fileUrl) { + continue + } - setActorImage(actor, type, null) - } catch (err) { - logger.error('Cannot remove old actor image of actor %s.', actor.url, { err }) + await safeDeleteActorImage(actor, oldImageModel, type, t) } - } - if (imageInfo) { const imageModel = await ActorImageModel.create({ filename: imageInfo.name, onDisk: imageInfo.onDisk ?? false, fileUrl: imageInfo.fileUrl, height: imageInfo.height, width: imageInfo.width, - type + type, + actorId: actor.id }, { transaction: t }) - setActorImage(actor, type, imageModel) + addActorImage(actor, type, imageModel) } return actor } -async function deleteActorImageInstance (actor: MActorImages, type: ActorImageType, t: Transaction) { +async function deleteActorImages (actor: MActorImages, type: ActorImageType, t: Transaction) { try { - if (type === ActorImageType.AVATAR) { - await actor.Avatar.destroy({ transaction: t }) - - actor.avatarId = null - actor.Avatar = null - } else { - await actor.Banner.destroy({ transaction: t }) + const association = buildAssociationName(type) - actor.bannerId = null - actor.Banner = null + for (const image of actor[association]) { + await image.destroy({ transaction: t }) } + + actor[association] = [] } catch (err) { logger.error('Cannot remove old image of actor %s.', actor.url, { err }) } @@ -66,29 +65,37 @@ async function deleteActorImageInstance (actor: MActorImages, type: ActorImageTy return actor } +async function safeDeleteActorImage (actor: MActorImages, toDelete: MActorImage, type: ActorImageType, t: Transaction) { + try { + await toDelete.destroy({ transaction: t }) + + const association = buildAssociationName(type) + actor[association] = actor[association].filter(image => image.id !== toDelete.id) + } catch (err) { + logger.error('Cannot remove old actor image of actor %s.', actor.url, { err }) + } +} + // --------------------------------------------------------------------------- export { ImageInfo, - updateActorImageInstance, - deleteActorImageInstance + updateActorImages, + deleteActorImages } // --------------------------------------------------------------------------- -function setActorImage (actorModel: MActorImages, type: ActorImageType, imageModel: MActorImage) { - const id = imageModel - ? imageModel.id - : null - - if (type === ActorImageType.AVATAR) { - actorModel.avatarId = id - actorModel.Avatar = imageModel - } else { - actorModel.bannerId = id - actorModel.Banner = imageModel - } +function addActorImage (actor: MActorImages, type: ActorImageType, imageModel: MActorImage) { + const association = buildAssociationName(type) + if (!actor[association]) actor[association] = [] + + actor[association].push(imageModel) +} - return actorModel +function buildAssociationName (type: ActorImageType) { + return type === ActorImageType.AVATAR + ? 'Avatars' + : 'Banners' } diff --git a/server/lib/activitypub/actors/shared/creator.ts b/server/lib/activitypub/actors/shared/creator.ts index 999aed97d..500bc9912 100644 --- a/server/lib/activitypub/actors/shared/creator.ts +++ b/server/lib/activitypub/actors/shared/creator.ts @@ -6,8 +6,8 @@ import { ServerModel } from '@server/models/server/server' import { VideoChannelModel } from '@server/models/video/video-channel' import { MAccount, MAccountDefault, MActor, MActorFullActor, MActorId, MActorImages, MChannel, MServer } from '@server/types/models' import { ActivityPubActor, ActorImageType } from '@shared/models' -import { updateActorImageInstance } from '../image' -import { getActorAttributesFromObject, getActorDisplayNameFromObject, getImageInfoFromObject } from './object-to-model-attributes' +import { updateActorImages } from '../image' +import { getActorAttributesFromObject, getActorDisplayNameFromObject, getImagesInfoFromObject } from './object-to-model-attributes' import { fetchActorFollowsCount } from './url-to-object' export class APActorCreator { @@ -27,11 +27,11 @@ export class APActorCreator { return sequelizeTypescript.transaction(async t => { const server = await this.setServer(actorInstance, t) - await this.setImageIfNeeded(actorInstance, ActorImageType.AVATAR, t) - await this.setImageIfNeeded(actorInstance, ActorImageType.BANNER, t) - const { actorCreated, created } = await this.saveActor(actorInstance, t) + await this.setImageIfNeeded(actorCreated, ActorImageType.AVATAR, t) + await this.setImageIfNeeded(actorCreated, ActorImageType.BANNER, t) + await this.tryToFixActorUrlIfNeeded(actorCreated, actorInstance, created, t) if (actorCreated.type === 'Person' || actorCreated.type === 'Application') { // Account or PeerTube instance @@ -71,10 +71,10 @@ export class APActorCreator { } private async setImageIfNeeded (actor: MActor, type: ActorImageType, t: Transaction) { - const imageInfo = getImageInfoFromObject(this.actorObject, type) - if (!imageInfo) return + const imagesInfo = getImagesInfoFromObject(this.actorObject, type) + if (imagesInfo.length === 0) return - return updateActorImageInstance(actor as MActorImages, type, imageInfo, t) + return updateActorImages(actor as MActorImages, type, imagesInfo, t) } private async saveActor (actor: MActor, t: Transaction) { diff --git a/server/lib/activitypub/actors/shared/object-to-model-attributes.ts b/server/lib/activitypub/actors/shared/object-to-model-attributes.ts index 23bc972e5..f6a78c457 100644 --- a/server/lib/activitypub/actors/shared/object-to-model-attributes.ts +++ b/server/lib/activitypub/actors/shared/object-to-model-attributes.ts @@ -4,7 +4,7 @@ import { ActorModel } from '@server/models/actor/actor' import { FilteredModelAttributes } from '@server/types' import { getLowercaseExtension } from '@shared/core-utils' import { buildUUID } from '@shared/extra-utils' -import { ActivityPubActor, ActorImageType } from '@shared/models' +import { ActivityIconObject, ActivityPubActor, ActorImageType } from '@shared/models' function getActorAttributesFromObject ( actorObject: ActivityPubActor, @@ -30,33 +30,36 @@ function getActorAttributesFromObject ( } } -function getImageInfoFromObject (actorObject: ActivityPubActor, type: ActorImageType) { - const mimetypes = MIMETYPES.IMAGE - const icon = type === ActorImageType.AVATAR - ? actorObject.icon +function getImagesInfoFromObject (actorObject: ActivityPubActor, type: ActorImageType) { + const iconsOrImages = type === ActorImageType.AVATAR + ? actorObject.icons || actorObject.icon : actorObject.image - if (!icon || icon.type !== 'Image' || !isActivityPubUrlValid(icon.url)) return undefined + return normalizeIconOrImage(iconsOrImages).map(iconOrImage => { + const mimetypes = MIMETYPES.IMAGE - let extension: string + if (iconOrImage.type !== 'Image' || !isActivityPubUrlValid(iconOrImage.url)) return undefined - if (icon.mediaType) { - extension = mimetypes.MIMETYPE_EXT[icon.mediaType] - } else { - const tmp = getLowercaseExtension(icon.url) + let extension: string - if (mimetypes.EXT_MIMETYPE[tmp] !== undefined) extension = tmp - } + if (iconOrImage.mediaType) { + extension = mimetypes.MIMETYPE_EXT[iconOrImage.mediaType] + } else { + const tmp = getLowercaseExtension(iconOrImage.url) - if (!extension) return undefined + if (mimetypes.EXT_MIMETYPE[tmp] !== undefined) extension = tmp + } - return { - name: buildUUID() + extension, - fileUrl: icon.url, - height: icon.height, - width: icon.width, - type - } + if (!extension) return undefined + + return { + name: buildUUID() + extension, + fileUrl: iconOrImage.url, + height: iconOrImage.height, + width: iconOrImage.width, + type + } + }) } function getActorDisplayNameFromObject (actorObject: ActivityPubActor) { @@ -65,6 +68,15 @@ function getActorDisplayNameFromObject (actorObject: ActivityPubActor) { export { getActorAttributesFromObject, - getImageInfoFromObject, + getImagesInfoFromObject, getActorDisplayNameFromObject } + +// --------------------------------------------------------------------------- + +function normalizeIconOrImage (icon: ActivityIconObject | ActivityIconObject[]): ActivityIconObject[] { + if (Array.isArray(icon)) return icon + if (icon) return [ icon ] + + return [] +} diff --git a/server/lib/activitypub/actors/updater.ts b/server/lib/activitypub/actors/updater.ts index 042438d9c..fe94af9f1 100644 --- a/server/lib/activitypub/actors/updater.ts +++ b/server/lib/activitypub/actors/updater.ts @@ -5,9 +5,9 @@ import { VideoChannelModel } from '@server/models/video/video-channel' import { MAccount, MActor, MActorFull, MChannel } from '@server/types/models' import { ActivityPubActor, ActorImageType } from '@shared/models' import { getOrCreateAPOwner } from './get' -import { updateActorImageInstance } from './image' +import { updateActorImages } from './image' import { fetchActorFollowsCount } from './shared' -import { getImageInfoFromObject } from './shared/object-to-model-attributes' +import { getImagesInfoFromObject } from './shared/object-to-model-attributes' export class APActorUpdater { @@ -29,8 +29,8 @@ export class APActorUpdater { } async update () { - const avatarInfo = getImageInfoFromObject(this.actorObject, ActorImageType.AVATAR) - const bannerInfo = getImageInfoFromObject(this.actorObject, ActorImageType.BANNER) + const avatarsInfo = getImagesInfoFromObject(this.actorObject, ActorImageType.AVATAR) + const bannersInfo = getImagesInfoFromObject(this.actorObject, ActorImageType.BANNER) try { await this.updateActorInstance(this.actor, this.actorObject) @@ -47,8 +47,8 @@ export class APActorUpdater { } await runInReadCommittedTransaction(async t => { - await updateActorImageInstance(this.actor, ActorImageType.AVATAR, avatarInfo, t) - await updateActorImageInstance(this.actor, ActorImageType.BANNER, bannerInfo, t) + await updateActorImages(this.actor, ActorImageType.BANNER, bannersInfo, t) + await updateActorImages(this.actor, ActorImageType.AVATAR, avatarsInfo, t) }) await runInReadCommittedTransaction(async t => { diff --git a/server/lib/actor-image.ts b/server/lib/actor-image.ts new file mode 100644 index 000000000..e9bd148f6 --- /dev/null +++ b/server/lib/actor-image.ts @@ -0,0 +1,14 @@ +import maxBy from 'lodash/maxBy' + +function getBiggestActorImage (images: T[]) { + const image = maxBy(images, 'width') + + // If width is null, maxBy won't return a value + if (!image) return images[0] + + return image +} + +export { + getBiggestActorImage +} diff --git a/server/lib/client-html.ts b/server/lib/client-html.ts index 19354ab70..c010f3c44 100644 --- a/server/lib/client-html.ts +++ b/server/lib/client-html.ts @@ -3,6 +3,7 @@ import { readFile } from 'fs-extra' import { join } from 'path' import validator from 'validator' import { toCompleteUUID } from '@server/helpers/custom-validators/misc' +import { ActorImageModel } from '@server/models/actor/actor-image' import { root } from '@shared/core-utils' import { escapeHTML } from '@shared/core-utils/renderer' import { sha256 } from '@shared/extra-utils' @@ -16,7 +17,6 @@ import { mdToOneLinePlainText } from '../helpers/markdown' import { CONFIG } from '../initializers/config' import { ACCEPT_HEADERS, - ACTOR_IMAGES_SIZE, CUSTOM_HTML_TAG_COMMENTS, EMBED_SIZE, FILES_CONTENT_HASH, @@ -29,6 +29,7 @@ import { VideoModel } from '../models/video/video' import { VideoChannelModel } from '../models/video/video-channel' import { VideoPlaylistModel } from '../models/video/video-playlist' import { MAccountActor, MChannelActor } from '../types/models' +import { getBiggestActorImage } from './actor-image' import { ServerConfigManager } from './server-config-manager' type Tags = { @@ -273,10 +274,11 @@ class ClientHtml { const siteName = CONFIG.INSTANCE.NAME const title = entity.getDisplayName() + const avatar = getBiggestActorImage(entity.Actor.Avatars) const image = { - url: entity.Actor.getAvatarUrl(), - width: ACTOR_IMAGES_SIZE.AVATARS.width, - height: ACTOR_IMAGES_SIZE.AVATARS.height + url: ActorImageModel.getImageUrl(avatar), + width: avatar?.width, + height: avatar?.height } const ogType = 'website' diff --git a/server/lib/local-actor.ts b/server/lib/local-actor.ts index c6826759b..01046d017 100644 --- a/server/lib/local-actor.ts +++ b/server/lib/local-actor.ts @@ -1,5 +1,5 @@ -import 'multer' import { queue } from 'async' +import { remove } from 'fs-extra' import LRUCache from 'lru-cache' import { join } from 'path' import { ActorModel } from '@server/models/actor/actor' @@ -13,7 +13,7 @@ import { CONFIG } from '../initializers/config' import { ACTOR_IMAGES_SIZE, LRU_CACHE, QUEUE_CONCURRENCY, WEBSERVER } from '../initializers/constants' import { sequelizeTypescript } from '../initializers/database' import { MAccountDefault, MActor, MChannelDefault } from '../types/models' -import { deleteActorImageInstance, updateActorImageInstance } from './activitypub/actors' +import { deleteActorImages, updateActorImages } from './activitypub/actors' import { sendUpdateActor } from './activitypub/send' function buildActorInstance (type: ActivityPubActorType, url: string, preferredUsername: string) { @@ -33,64 +33,69 @@ function buildActorInstance (type: ActivityPubActorType, url: string, preferredU }) as MActor } -async function updateLocalActorImageFile ( +async function updateLocalActorImageFiles ( accountOrChannel: MAccountDefault | MChannelDefault, imagePhysicalFile: Express.Multer.File, type: ActorImageType ) { - const imageSize = type === ActorImageType.AVATAR - ? ACTOR_IMAGES_SIZE.AVATARS - : ACTOR_IMAGES_SIZE.BANNERS - - const extension = getLowercaseExtension(imagePhysicalFile.filename) - - const imageName = buildUUID() + extension - const destination = join(CONFIG.STORAGE.ACTOR_IMAGES, imageName) - await processImage(imagePhysicalFile.path, destination, imageSize) - - return retryTransactionWrapper(() => { - return sequelizeTypescript.transaction(async t => { - const actorImageInfo = { - name: imageName, - fileUrl: null, - height: imageSize.height, - width: imageSize.width, - onDisk: true - } - - const updatedActor = await updateActorImageInstance(accountOrChannel.Actor, type, actorImageInfo, t) - await updatedActor.save({ transaction: t }) - - await sendUpdateActor(accountOrChannel, t) - - return type === ActorImageType.AVATAR - ? updatedActor.Avatar - : updatedActor.Banner - }) - }) + const processImageSize = async (imageSize: { width: number, height: number }) => { + const extension = getLowercaseExtension(imagePhysicalFile.filename) + + const imageName = buildUUID() + extension + const destination = join(CONFIG.STORAGE.ACTOR_IMAGES, imageName) + await processImage(imagePhysicalFile.path, destination, imageSize, true) + + return { + imageName, + imageSize + } + } + + const processedImages = await Promise.all(ACTOR_IMAGES_SIZE[type].map(processImageSize)) + await remove(imagePhysicalFile.path) + + return retryTransactionWrapper(() => sequelizeTypescript.transaction(async t => { + const actorImagesInfo = processedImages.map(({ imageName, imageSize }) => ({ + name: imageName, + fileUrl: null, + height: imageSize.height, + width: imageSize.width, + onDisk: true + })) + + const updatedActor = await updateActorImages(accountOrChannel.Actor, type, actorImagesInfo, t) + await updatedActor.save({ transaction: t }) + + await sendUpdateActor(accountOrChannel, t) + + return type === ActorImageType.AVATAR + ? updatedActor.Avatars + : updatedActor.Banners + })) } async function deleteLocalActorImageFile (accountOrChannel: MAccountDefault | MChannelDefault, type: ActorImageType) { return retryTransactionWrapper(() => { return sequelizeTypescript.transaction(async t => { - const updatedActor = await deleteActorImageInstance(accountOrChannel.Actor, type, t) + const updatedActor = await deleteActorImages(accountOrChannel.Actor, type, t) await updatedActor.save({ transaction: t }) await sendUpdateActor(accountOrChannel, t) - return updatedActor.Avatar + return updatedActor.Avatars }) }) } -type DownloadImageQueueTask = { fileUrl: string, filename: string, type: ActorImageType } +type DownloadImageQueueTask = { + fileUrl: string + filename: string + type: ActorImageType + size: typeof ACTOR_IMAGES_SIZE[ActorImageType][0] +} const downloadImageQueue = queue((task, cb) => { - const size = task.type === ActorImageType.AVATAR - ? ACTOR_IMAGES_SIZE.AVATARS - : ACTOR_IMAGES_SIZE.BANNERS - - downloadImage(task.fileUrl, CONFIG.STORAGE.ACTOR_IMAGES, task.filename, size) + downloadImage(task.fileUrl, CONFIG.STORAGE.ACTOR_IMAGES, task.filename, task.size) .then(() => cb()) .catch(err => cb(err)) }, QUEUE_CONCURRENCY.ACTOR_PROCESS_IMAGE) @@ -110,7 +115,7 @@ const actorImagePathUnsafeCache = new LRUCache({ max: LRU_CACHE. export { actorImagePathUnsafeCache, - updateLocalActorImageFile, + updateLocalActorImageFiles, deleteLocalActorImageFile, pushActorImageProcessInQueue, buildActorInstance diff --git a/server/lib/notifier/shared/comment/comment-mention.ts b/server/lib/notifier/shared/comment/comment-mention.ts index 765cbaad9..ecd1687b4 100644 --- a/server/lib/notifier/shared/comment/comment-mention.ts +++ b/server/lib/notifier/shared/comment/comment-mention.ts @@ -77,7 +77,7 @@ export class CommentMention extends AbstractNotification { + const query: FindOptions = { + where: { abuseId }, + order: getSort('createdAt') + } - order: getSort('createdAt'), + if (forCount !== true) { + query.include = [ + { + model: AccountModel.scope(AccountScopeNames.SUMMARY), + required: false + } + ] + } - include: [ - { - model: AccountModel.scope(AccountScopeNames.SUMMARY), - required: false - } - ] + return query } - return AbuseMessageModel.findAndCountAll(options) - .then(({ rows, count }) => ({ data: rows, total: count })) + return Promise.all([ + AbuseMessageModel.count(getQuery(true)), + AbuseMessageModel.findAll(getQuery(false)) + ]).then(([ total, data ]) => ({ total, data })) } static loadByIdAndAbuseId (messageId: number, abuseId: number): Promise { diff --git a/server/models/account/account-blocklist.ts b/server/models/account/account-blocklist.ts index 1162962bf..a7b8db076 100644 --- a/server/models/account/account-blocklist.ts +++ b/server/models/account/account-blocklist.ts @@ -1,7 +1,7 @@ -import { Op, QueryTypes } from 'sequelize' -import { BelongsTo, Column, CreatedAt, ForeignKey, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript' +import { FindOptions, Op, QueryTypes } from 'sequelize' +import { BelongsTo, Column, CreatedAt, ForeignKey, Model, Table, UpdatedAt } from 'sequelize-typescript' import { handlesToNameAndHost } from '@server/helpers/actors' -import { MAccountBlocklist, MAccountBlocklistAccounts, MAccountBlocklistFormattable } from '@server/types/models' +import { MAccountBlocklist, MAccountBlocklistFormattable } from '@server/types/models' import { AttributesOnly } from '@shared/typescript-utils' import { AccountBlock } from '../../../shared/models' import { ActorModel } from '../actor/actor' @@ -9,27 +9,6 @@ import { ServerModel } from '../server/server' import { createSafeIn, getSort, searchAttribute } from '../utils' import { AccountModel } from './account' -enum ScopeNames { - WITH_ACCOUNTS = 'WITH_ACCOUNTS' -} - -@Scopes(() => ({ - [ScopeNames.WITH_ACCOUNTS]: { - include: [ - { - model: AccountModel, - required: true, - as: 'ByAccount' - }, - { - model: AccountModel, - required: true, - as: 'BlockedAccount' - } - ] - } -})) - @Table({ tableName: 'accountBlocklist', indexes: [ @@ -123,33 +102,45 @@ export class AccountBlocklistModel extends Model { + const query: FindOptions = { + offset: start, + limit: count, + order: getSort(sort), + where: { accountId } + } - const where = { - accountId - } + if (search) { + Object.assign(query.where, { + [Op.or]: [ + searchAttribute(search, '$BlockedAccount.name$'), + searchAttribute(search, '$BlockedAccount.Actor.url$') + ] + }) + } - if (search) { - Object.assign(where, { - [Op.or]: [ - searchAttribute(search, '$BlockedAccount.name$'), - searchAttribute(search, '$BlockedAccount.Actor.url$') + if (forCount !== true) { + query.include = [ + { + model: AccountModel, + required: true, + as: 'ByAccount' + }, + { + model: AccountModel, + required: true, + as: 'BlockedAccount' + } ] - }) - } + } - Object.assign(query, { where }) + return query + } - return AccountBlocklistModel - .scope([ ScopeNames.WITH_ACCOUNTS ]) - .findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows } - }) + return Promise.all([ + AccountBlocklistModel.count(getQuery(true)), + AccountBlocklistModel.findAll(getQuery(false)) + ]).then(([ total, data ]) => ({ total, data })) } static listHandlesBlockedBy (accountIds: number[]): Promise { diff --git a/server/models/account/account-video-rate.ts b/server/models/account/account-video-rate.ts index e89d31adf..7303651eb 100644 --- a/server/models/account/account-video-rate.ts +++ b/server/models/account/account-video-rate.ts @@ -121,29 +121,40 @@ export class AccountVideoRateModel extends Model { + const query: FindOptions = { + offset: options.start, + limit: options.count, + order: getSort(options.sort), + where: { + accountId: options.accountId } - ] + } + + if (options.type) query.where['type'] = options.type + + if (forCount !== true) { + query.include = [ + { + model: VideoModel, + required: true, + include: [ + { + model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }), + required: true + } + ] + } + ] + } + + return query } - if (options.type) query.where['type'] = options.type - return AccountVideoRateModel.findAndCountAll(query) + return Promise.all([ + AccountVideoRateModel.count(getQuery(true)), + AccountVideoRateModel.findAll(getQuery(false)) + ]).then(([ total, data ]) => ({ total, data })) } static listRemoteRateUrlsOfLocalVideos () { @@ -232,7 +243,10 @@ export class AccountVideoRateModel extends Model(query) + return Promise.all([ + AccountVideoRateModel.count(query), + AccountVideoRateModel.findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static cleanOldRatesOf (videoId: number, type: VideoRateType, beforeUpdatedAt: Date) { diff --git a/server/models/account/account.ts b/server/models/account/account.ts index 619a598dd..8a7dfba94 100644 --- a/server/models/account/account.ts +++ b/server/models/account/account.ts @@ -54,6 +54,7 @@ export type SummaryOptions = { whereActor?: WhereOptions whereServer?: WhereOptions withAccountBlockerIds?: number[] + forCount?: boolean } @DefaultScope(() => ({ @@ -73,22 +74,24 @@ export type SummaryOptions = { where: options.whereServer } - const queryInclude: Includeable[] = [ - { - attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ], - model: ActorModel.unscoped(), - required: options.actorRequired ?? true, - where: options.whereActor, - include: [ - serverInclude, + const actorInclude: Includeable = { + attributes: [ 'id', 'preferredUsername', 'url', 'serverId' ], + model: ActorModel.unscoped(), + required: options.actorRequired ?? true, + where: options.whereActor, + include: [ serverInclude ] + } - { - model: ActorImageModel.unscoped(), - as: 'Avatar', - required: false - } - ] - } + if (options.forCount !== true) { + actorInclude.include.push({ + model: ActorImageModel, + as: 'Avatars', + required: false + }) + } + + const queryInclude: Includeable[] = [ + actorInclude ] const query: FindOptions = { @@ -349,13 +352,10 @@ export class AccountModel extends Model>> { order: getSort(sort) } - return AccountModel.findAndCountAll(query) - .then(({ rows, count }) => { - return { - data: rows, - total: count - } - }) + return Promise.all([ + AccountModel.count(), + AccountModel.findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static loadAccountIdFromVideo (videoId: number): Promise { @@ -407,16 +407,15 @@ export class AccountModel extends Model>> { } toFormattedJSON (this: MAccountFormattable): Account { - const actor = this.Actor.toFormattedJSON() - const account = { + return { + ...this.Actor.toFormattedJSON(), + id: this.id, displayName: this.getDisplayName(), description: this.description, updatedAt: this.updatedAt, - userId: this.userId ? this.userId : undefined + userId: this.userId ?? undefined } - - return Object.assign(actor, account) } toFormattedSummaryJSON (this: MAccountSummaryFormattable): AccountSummary { @@ -424,10 +423,14 @@ export class AccountModel extends Model>> { return { id: this.id, - name: actor.name, displayName: this.getDisplayName(), + + name: actor.name, url: actor.url, host: actor.host, + avatars: actor.avatars, + + // TODO: remove, deprecated in 4.2 avatar: actor.avatar } } diff --git a/server/models/actor/actor-follow.ts b/server/models/actor/actor-follow.ts index 006282530..0f4d3c0a6 100644 --- a/server/models/actor/actor-follow.ts +++ b/server/models/actor/actor-follow.ts @@ -1,5 +1,5 @@ import { difference, values } from 'lodash' -import { IncludeOptions, Op, QueryTypes, Transaction, WhereOptions } from 'sequelize' +import { Includeable, IncludeOptions, literal, Op, QueryTypes, Transaction, WhereOptions } from 'sequelize' import { AfterCreate, AfterDestroy, @@ -30,12 +30,12 @@ import { MActorFollowFormattable, MActorFollowSubscriptions } from '@server/types/models' -import { AttributesOnly } from '@shared/typescript-utils' import { ActivityPubActorType } from '@shared/models' +import { AttributesOnly } from '@shared/typescript-utils' import { FollowState } from '../../../shared/models/actors' import { ActorFollow } from '../../../shared/models/actors/follow.model' import { logger } from '../../helpers/logger' -import { ACTOR_FOLLOW_SCORE, CONSTRAINTS_FIELDS, FOLLOW_STATES, SERVER_ACTOR_NAME } from '../../initializers/constants' +import { ACTOR_FOLLOW_SCORE, CONSTRAINTS_FIELDS, FOLLOW_STATES, SERVER_ACTOR_NAME, SORTABLE_COLUMNS } from '../../initializers/constants' import { AccountModel } from '../account/account' import { ServerModel } from '../server/server' import { doesExist } from '../shared/query' @@ -375,43 +375,46 @@ export class ActorFollowModel extends Model { + const actorModel = forCount + ? ActorModel.unscoped() + : ActorModel + + return { + distinct: true, + offset: start, + limit: count, + order: getFollowsSort(sort), + where: followWhere, + include: [ + { + model: actorModel, + required: true, + as: 'ActorFollower', + where: { + id } - ] - } - ] + }, + { + model: actorModel, + as: 'ActorFollowing', + required: true, + where: followingWhere, + include: [ + { + model: ServerModel, + required: true + } + ] + } + ] + } } - return ActorFollowModel.findAndCountAll(query) - .then(({ rows, count }) => { - return { - data: rows, - total: count - } - }) + return Promise.all([ + ActorFollowModel.count(getQuery(true)), + ActorFollowModel.findAll(getQuery(false)) + ]).then(([ total, data ]) => ({ total, data })) } static listFollowersForApi (options: { @@ -429,11 +432,17 @@ export class ActorFollowModel extends Model { + const actorModel = forCount + ? ActorModel.unscoped() + : ActorModel + + return { + distinct: true, + + offset: start, + limit: count, + order: getFollowsSort(sort), + where: followWhere, + include: [ + { + model: actorModel, + required: true, + as: 'ActorFollower', + where: followerWhere + }, + { + model: actorModel, + as: 'ActorFollowing', + required: true, + where: { + id: { + [Op.in]: actorIds + } } } - } - ] + ] + } } - return ActorFollowModel.findAndCountAll(query) - .then(({ rows, count }) => { - return { - data: rows, - total: count - } - }) + return Promise.all([ + ActorFollowModel.count(getQuery(true)), + ActorFollowModel.findAll(getQuery(false)) + ]).then(([ total, data ]) => ({ total, data })) } static listSubscriptionsForApi (options: { @@ -497,58 +510,68 @@ export class ActorFollowModel extends Model { + let channelInclude: Includeable[] = [] + + if (forCount !== true) { + channelInclude = [ + { + attributes: { + exclude: unusedActorAttributesForAPI + }, + model: ActorModel, + required: true + }, + { + model: AccountModel.unscoped(), + required: true, + include: [ + { + attributes: { + exclude: unusedActorAttributesForAPI }, - { - model: AccountModel.unscoped(), - required: true, - include: [ - { - attributes: { - exclude: unusedActorAttributesForAPI - }, - model: ActorModel, - required: true - } - ] - } - ] - } - ] - } - ] + model: ActorModel, + required: true + } + ] + } + ] + } + + return { + attributes: forCount === true + ? [] + : SORTABLE_COLUMNS.USER_SUBSCRIPTIONS, + distinct: true, + offset: start, + limit: count, + order: getSort(sort), + where, + include: [ + { + attributes: [ 'id' ], + model: ActorModel.unscoped(), + as: 'ActorFollowing', + required: true, + include: [ + { + model: VideoChannelModel.unscoped(), + required: true, + include: channelInclude + } + ] + } + ] + } } - return ActorFollowModel.findAndCountAll(query) - .then(({ rows, count }) => { - return { - data: rows.map(r => r.ActorFollowing.VideoChannel), - total: count - } - }) + return Promise.all([ + ActorFollowModel.count(getQuery(true)), + ActorFollowModel.findAll(getQuery(false)) + ]).then(([ total, rows ]) => ({ + total, + data: rows.map(r => r.ActorFollowing.VideoChannel) + })) } static async keepUnfollowedInstance (hosts: string[]) { diff --git a/server/models/actor/actor-image.ts b/server/models/actor/actor-image.ts index 8edff5ab4..f74ab735e 100644 --- a/server/models/actor/actor-image.ts +++ b/server/models/actor/actor-image.ts @@ -1,15 +1,29 @@ import { remove } from 'fs-extra' import { join } from 'path' -import { AfterDestroy, AllowNull, Column, CreatedAt, Default, Is, Model, Table, UpdatedAt } from 'sequelize-typescript' -import { MActorImageFormattable } from '@server/types/models' +import { + AfterDestroy, + AllowNull, + BelongsTo, + Column, + CreatedAt, + Default, + ForeignKey, + Is, + Model, + Table, + UpdatedAt +} from 'sequelize-typescript' +import { MActorImage, MActorImageFormattable } from '@server/types/models' +import { getLowercaseExtension } from '@shared/core-utils' +import { ActivityIconObject, ActorImageType } from '@shared/models' import { AttributesOnly } from '@shared/typescript-utils' -import { ActorImageType } from '@shared/models' import { ActorImage } from '../../../shared/models/actors/actor-image.model' import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc' import { logger } from '../../helpers/logger' import { CONFIG } from '../../initializers/config' -import { LAZY_STATIC_PATHS } from '../../initializers/constants' +import { LAZY_STATIC_PATHS, MIMETYPES, WEBSERVER } from '../../initializers/constants' import { throwIfNotValid } from '../utils' +import { ActorModel } from './actor' @Table({ tableName: 'actorImage', @@ -17,6 +31,10 @@ import { throwIfNotValid } from '../utils' { fields: [ 'filename' ], unique: true + }, + { + fields: [ 'actorId', 'type', 'width' ], + unique: true } ] }) @@ -55,6 +73,18 @@ export class ActorImageModel extends Model ActorModel) + @Column + actorId: number + + @BelongsTo(() => ActorModel, { + foreignKey: { + allowNull: false + }, + onDelete: 'CASCADE' + }) + Actor: ActorModel + @AfterDestroy static removeFilesAndSendDelete (instance: ActorImageModel) { logger.info('Removing actor image file %s.', instance.filename) @@ -74,20 +104,41 @@ export class ActorImageModel extends Model>> { @UpdatedAt updatedAt: Date - @ForeignKey(() => ActorImageModel) - @Column - avatarId: number - - @ForeignKey(() => ActorImageModel) - @Column - bannerId: number - - @BelongsTo(() => ActorImageModel, { + @HasMany(() => ActorImageModel, { + as: 'Avatars', + onDelete: 'cascade', + hooks: true, foreignKey: { - name: 'avatarId', - allowNull: true + allowNull: false }, - as: 'Avatar', - onDelete: 'set null', - hooks: true + scope: { + type: ActorImageType.AVATAR + } }) - Avatar: ActorImageModel + Avatars: ActorImageModel[] - @BelongsTo(() => ActorImageModel, { + @HasMany(() => ActorImageModel, { + as: 'Banners', + onDelete: 'cascade', + hooks: true, foreignKey: { - name: 'bannerId', - allowNull: true + allowNull: false }, - as: 'Banner', - onDelete: 'set null', - hooks: true + scope: { + type: ActorImageType.BANNER + } }) - Banner: ActorImageModel + Banners: ActorImageModel[] @HasMany(() => ActorFollowModel, { foreignKey: { @@ -386,8 +379,7 @@ export class ActorModel extends Model>> { transaction } - return ActorModel.scope(ScopeNames.FULL) - .findOne(query) + return ActorModel.scope(ScopeNames.FULL).findOne(query) } return ModelCache.Instance.doCache({ @@ -410,8 +402,7 @@ export class ActorModel extends Model>> { transaction } - return ActorModel.unscoped() - .findOne(query) + return ActorModel.unscoped().findOne(query) } return ModelCache.Instance.doCache({ @@ -532,55 +523,50 @@ export class ActorModel extends Model>> { } toFormattedSummaryJSON (this: MActorSummaryFormattable) { - let avatar: ActorImage = null - if (this.Avatar) { - avatar = this.Avatar.toFormattedJSON() - } - return { url: this.url, name: this.preferredUsername, host: this.getHost(), - avatar + avatars: (this.Avatars || []).map(a => a.toFormattedJSON()), + + // TODO: remove, deprecated in 4.2 + avatar: this.hasImage(ActorImageType.AVATAR) + ? this.Avatars[0].toFormattedJSON() + : undefined } } toFormattedJSON (this: MActorFormattable) { - const base = this.toFormattedSummaryJSON() - - let banner: ActorImage = null - if (this.Banner) { - banner = this.Banner.toFormattedJSON() - } + return { + ...this.toFormattedSummaryJSON(), - return Object.assign(base, { id: this.id, hostRedundancyAllowed: this.getRedundancyAllowed(), followingCount: this.followingCount, followersCount: this.followersCount, - banner, - createdAt: this.getCreatedAt() - }) + createdAt: this.getCreatedAt(), + + banners: (this.Banners || []).map(b => b.toFormattedJSON()), + + // TODO: remove, deprecated in 4.2 + banner: this.hasImage(ActorImageType.BANNER) + ? this.Banners[0].toFormattedJSON() + : undefined + } } toActivityPubObject (this: MActorAPChannel | MActorAPAccount, name: string) { let icon: ActivityIconObject + let icons: ActivityIconObject[] let image: ActivityIconObject - if (this.avatarId) { - const extension = getLowercaseExtension(this.Avatar.filename) - - icon = { - type: 'Image', - mediaType: MIMETYPES.IMAGE.EXT_MIMETYPE[extension], - height: this.Avatar.height, - width: this.Avatar.width, - url: this.getAvatarUrl() - } + if (this.hasImage(ActorImageType.AVATAR)) { + icon = getBiggestActorImage(this.Avatars).toActivityPubObject() + icons = this.Avatars.map(a => a.toActivityPubObject()) } - if (this.bannerId) { - const banner = (this as MActorAPChannel).Banner + if (this.hasImage(ActorImageType.BANNER)) { + const banner = getBiggestActorImage((this as MActorAPChannel).Banners) const extension = getLowercaseExtension(banner.filename) image = { @@ -588,7 +574,7 @@ export class ActorModel extends Model>> { mediaType: MIMETYPES.IMAGE.EXT_MIMETYPE[extension], height: banner.height, width: banner.width, - url: this.getBannerUrl() + url: ActorImageModel.getImageUrl(banner) } } @@ -612,7 +598,10 @@ export class ActorModel extends Model>> { publicKeyPem: this.publicKey }, published: this.getCreatedAt().toISOString(), + icon, + icons, + image } @@ -677,16 +666,12 @@ export class ActorModel extends Model>> { return this.Server ? this.Server.redundancyAllowed : false } - getAvatarUrl () { - if (!this.avatarId) return undefined - - return WEBSERVER.URL + this.Avatar.getStaticPath() - } - - getBannerUrl () { - if (!this.bannerId) return undefined + hasImage (type: ActorImageType) { + const images = type === ActorImageType.AVATAR + ? this.Avatars + : this.Banners - return WEBSERVER.URL + this.Banner.getStaticPath() + return Array.isArray(images) && images.length !== 0 } isOutdated () { diff --git a/server/models/server/plugin.ts b/server/models/server/plugin.ts index 05083e3f7..fa5b4cc4b 100644 --- a/server/models/server/plugin.ts +++ b/server/models/server/plugin.ts @@ -239,11 +239,10 @@ export class PluginModel extends Model>> { if (options.pluginType) query.where['type'] = options.pluginType - return PluginModel - .findAndCountAll(query) - .then(({ rows, count }) => { - return { total: count, data: rows } - }) + return Promise.all([ + PluginModel.count(query), + PluginModel.findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static listInstalled (): Promise { diff --git a/server/models/server/server-blocklist.ts b/server/models/server/server-blocklist.ts index 9f64eeb7f..9752dfbc3 100644 --- a/server/models/server/server-blocklist.ts +++ b/server/models/server/server-blocklist.ts @@ -1,8 +1,8 @@ import { Op, QueryTypes } from 'sequelize' import { BelongsTo, Column, CreatedAt, ForeignKey, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript' import { MServerBlocklist, MServerBlocklistAccountServer, MServerBlocklistFormattable } from '@server/types/models' -import { AttributesOnly } from '@shared/typescript-utils' import { ServerBlock } from '@shared/models' +import { AttributesOnly } from '@shared/typescript-utils' import { AccountModel } from '../account/account' import { createSafeIn, getSort, searchAttribute } from '../utils' import { ServerModel } from './server' @@ -169,16 +169,15 @@ export class ServerBlocklistModel extends Model(query) - .then(({ rows, count }) => { - return { total: count, data: rows } - }) + return Promise.all([ + ServerBlocklistModel.scope(ScopeNames.WITH_SERVER).count(query), + ServerBlocklistModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_SERVER ]).findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } toFormattedJSON (this: MServerBlocklistFormattable): ServerBlock { diff --git a/server/models/shared/index.ts b/server/models/shared/index.ts index 5b97510e0..802404555 100644 --- a/server/models/shared/index.ts +++ b/server/models/shared/index.ts @@ -1,2 +1,3 @@ +export * from './model-builder' export * from './query' export * from './update' diff --git a/server/models/shared/model-builder.ts b/server/models/shared/model-builder.ts new file mode 100644 index 000000000..c015ca4f5 --- /dev/null +++ b/server/models/shared/model-builder.ts @@ -0,0 +1,101 @@ +import { isPlainObject } from 'lodash' +import { Model as SequelizeModel, Sequelize } from 'sequelize' +import { logger } from '@server/helpers/logger' + +export class ModelBuilder { + private readonly modelRegistry = new Map() + + constructor (private readonly sequelize: Sequelize) { + + } + + createModels (jsonArray: any[], baseModelName: string): T[] { + const result: T[] = [] + + for (const json of jsonArray) { + const { created, model } = this.createModel(json, baseModelName, json.id + '.' + baseModelName) + + if (created) result.push(model) + } + + return result + } + + private createModel (json: any, modelName: string, keyPath: string) { + if (!json.id) return { created: false, model: null } + + const { created, model } = this.createOrFindModel(json, modelName, keyPath) + + for (const key of Object.keys(json)) { + const value = json[key] + if (!value) continue + + // Child model + if (isPlainObject(value)) { + const { created, model: subModel } = this.createModel(value, key, keyPath + '.' + json.id + '.' + key) + if (!created || !subModel) continue + + const Model = this.findModelBuilder(modelName) + const association = Model.associations[key] + + if (!association) { + logger.error('Cannot find association %s of model %s', key, modelName, { associations: Object.keys(Model.associations) }) + continue + } + + if (association.isMultiAssociation) { + if (!Array.isArray(model[key])) model[key] = [] + + model[key].push(subModel) + } else { + model[key] = subModel + } + } + } + + return { created, model } + } + + private createOrFindModel (json: any, modelName: string, keyPath: string) { + const registryKey = this.getModelRegistryKey(json, keyPath) + if (this.modelRegistry.has(registryKey)) { + return { + created: false, + model: this.modelRegistry.get(registryKey) + } + } + + const Model = this.findModelBuilder(modelName) + + if (!Model) { + logger.error( + 'Cannot build model %s that does not exist', this.buildSequelizeModelName(modelName), + { existing: this.sequelize.modelManager.all.map(m => m.name) } + ) + return undefined + } + + // FIXME: typings + const model = new (Model as any)(json) + this.modelRegistry.set(registryKey, model) + + return { created: true, model } + } + + private findModelBuilder (modelName: string) { + return this.sequelize.modelManager.getModel(this.buildSequelizeModelName(modelName)) + } + + private buildSequelizeModelName (modelName: string) { + if (modelName === 'Avatars') return 'ActorImageModel' + if (modelName === 'ActorFollowing') return 'ActorModel' + if (modelName === 'ActorFollower') return 'ActorModel' + if (modelName === 'FlaggedAccount') return 'AccountModel' + + return modelName + 'Model' + } + + private getModelRegistryKey (json: any, keyPath: string) { + return keyPath + json.id + } +} diff --git a/server/models/user/sql/user-notitication-list-query-builder.ts b/server/models/user/sql/user-notitication-list-query-builder.ts new file mode 100644 index 000000000..9eae4fc22 --- /dev/null +++ b/server/models/user/sql/user-notitication-list-query-builder.ts @@ -0,0 +1,269 @@ +import { QueryTypes, Sequelize } from 'sequelize' +import { ModelBuilder } from '@server/models/shared' +import { getSort } from '@server/models/utils' +import { UserNotificationModelForApi } from '@server/types/models' +import { ActorImageType } from '@shared/models' + +export interface ListNotificationsOptions { + userId: number + unread?: boolean + sort: string + offset: number + limit: number + sequelize: Sequelize +} + +export class UserNotificationListQueryBuilder { + private innerQuery: string + private replacements: any = {} + private query: string + + constructor (private readonly options: ListNotificationsOptions) { + + } + + async listNotifications () { + this.buildQuery() + + const results = await this.options.sequelize.query(this.query, { + replacements: this.replacements, + type: QueryTypes.SELECT, + nest: true + }) + + const modelBuilder = new ModelBuilder(this.options.sequelize) + + return modelBuilder.createModels(results, 'UserNotification') + } + + private buildInnerQuery () { + this.innerQuery = `SELECT * FROM "userNotification" AS "UserNotificationModel" ` + + `${this.getWhere()} ` + + `${this.getOrder()} ` + + `LIMIT :limit OFFSET :offset ` + + this.replacements.limit = this.options.limit + this.replacements.offset = this.options.offset + } + + private buildQuery () { + this.buildInnerQuery() + + this.query = ` + ${this.getSelect()} + FROM (${this.innerQuery}) "UserNotificationModel" + ${this.getJoins()} + ${this.getOrder()}` + } + + private getWhere () { + let base = '"UserNotificationModel"."userId" = :userId ' + this.replacements.userId = this.options.userId + + if (this.options.unread === true) { + base += 'AND "UserNotificationModel"."read" IS FALSE ' + } else if (this.options.unread === false) { + base += 'AND "UserNotificationModel"."read" IS TRUE ' + } + + return `WHERE ${base}` + } + + private getOrder () { + const orders = getSort(this.options.sort) + + return 'ORDER BY ' + orders.map(o => `"UserNotificationModel"."${o[0]}" ${o[1]}`).join(', ') + } + + private getSelect () { + return `SELECT + "UserNotificationModel"."id", + "UserNotificationModel"."type", + "UserNotificationModel"."read", + "UserNotificationModel"."createdAt", + "UserNotificationModel"."updatedAt", + "Video"."id" AS "Video.id", + "Video"."uuid" AS "Video.uuid", + "Video"."name" AS "Video.name", + "Video->VideoChannel"."id" AS "Video.VideoChannel.id", + "Video->VideoChannel"."name" AS "Video.VideoChannel.name", + "Video->VideoChannel->Actor"."id" AS "Video.VideoChannel.Actor.id", + "Video->VideoChannel->Actor"."preferredUsername" AS "Video.VideoChannel.Actor.preferredUsername", + "Video->VideoChannel->Actor->Avatars"."id" AS "Video.VideoChannel.Actor.Avatars.id", + "Video->VideoChannel->Actor->Avatars"."width" AS "Video.VideoChannel.Actor.Avatars.width", + "Video->VideoChannel->Actor->Avatars"."filename" AS "Video.VideoChannel.Actor.Avatars.filename", + "Video->VideoChannel->Actor->Server"."id" AS "Video.VideoChannel.Actor.Server.id", + "Video->VideoChannel->Actor->Server"."host" AS "Video.VideoChannel.Actor.Server.host", + "VideoComment"."id" AS "VideoComment.id", + "VideoComment"."originCommentId" AS "VideoComment.originCommentId", + "VideoComment->Account"."id" AS "VideoComment.Account.id", + "VideoComment->Account"."name" AS "VideoComment.Account.name", + "VideoComment->Account->Actor"."id" AS "VideoComment.Account.Actor.id", + "VideoComment->Account->Actor"."preferredUsername" AS "VideoComment.Account.Actor.preferredUsername", + "VideoComment->Account->Actor->Avatars"."id" AS "VideoComment.Account.Actor.Avatars.id", + "VideoComment->Account->Actor->Avatars"."width" AS "VideoComment.Account.Actor.Avatars.width", + "VideoComment->Account->Actor->Avatars"."filename" AS "VideoComment.Account.Actor.Avatars.filename", + "VideoComment->Account->Actor->Server"."id" AS "VideoComment.Account.Actor.Server.id", + "VideoComment->Account->Actor->Server"."host" AS "VideoComment.Account.Actor.Server.host", + "VideoComment->Video"."id" AS "VideoComment.Video.id", + "VideoComment->Video"."uuid" AS "VideoComment.Video.uuid", + "VideoComment->Video"."name" AS "VideoComment.Video.name", + "Abuse"."id" AS "Abuse.id", + "Abuse"."state" AS "Abuse.state", + "Abuse->VideoAbuse"."id" AS "Abuse.VideoAbuse.id", + "Abuse->VideoAbuse->Video"."id" AS "Abuse.VideoAbuse.Video.id", + "Abuse->VideoAbuse->Video"."uuid" AS "Abuse.VideoAbuse.Video.uuid", + "Abuse->VideoAbuse->Video"."name" AS "Abuse.VideoAbuse.Video.name", + "Abuse->VideoCommentAbuse"."id" AS "Abuse.VideoCommentAbuse.id", + "Abuse->VideoCommentAbuse->VideoComment"."id" AS "Abuse.VideoCommentAbuse.VideoComment.id", + "Abuse->VideoCommentAbuse->VideoComment"."originCommentId" AS "Abuse.VideoCommentAbuse.VideoComment.originCommentId", + "Abuse->VideoCommentAbuse->VideoComment->Video"."id" AS "Abuse.VideoCommentAbuse.VideoComment.Video.id", + "Abuse->VideoCommentAbuse->VideoComment->Video"."name" AS "Abuse.VideoCommentAbuse.VideoComment.Video.name", + "Abuse->VideoCommentAbuse->VideoComment->Video"."uuid" AS "Abuse.VideoCommentAbuse.VideoComment.Video.uuid", + "Abuse->FlaggedAccount"."id" AS "Abuse.FlaggedAccount.id", + "Abuse->FlaggedAccount"."name" AS "Abuse.FlaggedAccount.name", + "Abuse->FlaggedAccount"."description" AS "Abuse.FlaggedAccount.description", + "Abuse->FlaggedAccount"."actorId" AS "Abuse.FlaggedAccount.actorId", + "Abuse->FlaggedAccount"."userId" AS "Abuse.FlaggedAccount.userId", + "Abuse->FlaggedAccount"."applicationId" AS "Abuse.FlaggedAccount.applicationId", + "Abuse->FlaggedAccount"."createdAt" AS "Abuse.FlaggedAccount.createdAt", + "Abuse->FlaggedAccount"."updatedAt" AS "Abuse.FlaggedAccount.updatedAt", + "Abuse->FlaggedAccount->Actor"."id" AS "Abuse.FlaggedAccount.Actor.id", + "Abuse->FlaggedAccount->Actor"."preferredUsername" AS "Abuse.FlaggedAccount.Actor.preferredUsername", + "Abuse->FlaggedAccount->Actor->Avatars"."id" AS "Abuse.FlaggedAccount.Actor.Avatars.id", + "Abuse->FlaggedAccount->Actor->Avatars"."width" AS "Abuse.FlaggedAccount.Actor.Avatars.width", + "Abuse->FlaggedAccount->Actor->Avatars"."filename" AS "Abuse.FlaggedAccount.Actor.Avatars.filename", + "Abuse->FlaggedAccount->Actor->Server"."id" AS "Abuse.FlaggedAccount.Actor.Server.id", + "Abuse->FlaggedAccount->Actor->Server"."host" AS "Abuse.FlaggedAccount.Actor.Server.host", + "VideoBlacklist"."id" AS "VideoBlacklist.id", + "VideoBlacklist->Video"."id" AS "VideoBlacklist.Video.id", + "VideoBlacklist->Video"."uuid" AS "VideoBlacklist.Video.uuid", + "VideoBlacklist->Video"."name" AS "VideoBlacklist.Video.name", + "VideoImport"."id" AS "VideoImport.id", + "VideoImport"."magnetUri" AS "VideoImport.magnetUri", + "VideoImport"."targetUrl" AS "VideoImport.targetUrl", + "VideoImport"."torrentName" AS "VideoImport.torrentName", + "VideoImport->Video"."id" AS "VideoImport.Video.id", + "VideoImport->Video"."uuid" AS "VideoImport.Video.uuid", + "VideoImport->Video"."name" AS "VideoImport.Video.name", + "Plugin"."id" AS "Plugin.id", + "Plugin"."name" AS "Plugin.name", + "Plugin"."type" AS "Plugin.type", + "Plugin"."latestVersion" AS "Plugin.latestVersion", + "Application"."id" AS "Application.id", + "Application"."latestPeerTubeVersion" AS "Application.latestPeerTubeVersion", + "ActorFollow"."id" AS "ActorFollow.id", + "ActorFollow"."state" AS "ActorFollow.state", + "ActorFollow->ActorFollower"."id" AS "ActorFollow.ActorFollower.id", + "ActorFollow->ActorFollower"."preferredUsername" AS "ActorFollow.ActorFollower.preferredUsername", + "ActorFollow->ActorFollower->Account"."id" AS "ActorFollow.ActorFollower.Account.id", + "ActorFollow->ActorFollower->Account"."name" AS "ActorFollow.ActorFollower.Account.name", + "ActorFollow->ActorFollower->Avatars"."id" AS "ActorFollow.ActorFollower.Avatars.id", + "ActorFollow->ActorFollower->Avatars"."width" AS "ActorFollow.ActorFollower.Avatars.width", + "ActorFollow->ActorFollower->Avatars"."filename" AS "ActorFollow.ActorFollower.Avatars.filename", + "ActorFollow->ActorFollower->Server"."id" AS "ActorFollow.ActorFollower.Server.id", + "ActorFollow->ActorFollower->Server"."host" AS "ActorFollow.ActorFollower.Server.host", + "ActorFollow->ActorFollowing"."id" AS "ActorFollow.ActorFollowing.id", + "ActorFollow->ActorFollowing"."preferredUsername" AS "ActorFollow.ActorFollowing.preferredUsername", + "ActorFollow->ActorFollowing"."type" AS "ActorFollow.ActorFollowing.type", + "ActorFollow->ActorFollowing->VideoChannel"."id" AS "ActorFollow.ActorFollowing.VideoChannel.id", + "ActorFollow->ActorFollowing->VideoChannel"."name" AS "ActorFollow.ActorFollowing.VideoChannel.name", + "ActorFollow->ActorFollowing->Account"."id" AS "ActorFollow.ActorFollowing.Account.id", + "ActorFollow->ActorFollowing->Account"."name" AS "ActorFollow.ActorFollowing.Account.name", + "ActorFollow->ActorFollowing->Server"."id" AS "ActorFollow.ActorFollowing.Server.id", + "ActorFollow->ActorFollowing->Server"."host" AS "ActorFollow.ActorFollowing.Server.host", + "Account"."id" AS "Account.id", + "Account"."name" AS "Account.name", + "Account->Actor"."id" AS "Account.Actor.id", + "Account->Actor"."preferredUsername" AS "Account.Actor.preferredUsername", + "Account->Actor->Avatars"."id" AS "Account.Actor.Avatars.id", + "Account->Actor->Avatars"."width" AS "Account.Actor.Avatars.width", + "Account->Actor->Avatars"."filename" AS "Account.Actor.Avatars.filename", + "Account->Actor->Server"."id" AS "Account.Actor.Server.id", + "Account->Actor->Server"."host" AS "Account.Actor.Server.host"` + } + + private getJoins () { + return ` + LEFT JOIN ( + "video" AS "Video" + INNER JOIN "videoChannel" AS "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id" + INNER JOIN "actor" AS "Video->VideoChannel->Actor" ON "Video->VideoChannel"."actorId" = "Video->VideoChannel->Actor"."id" + LEFT JOIN "actorImage" AS "Video->VideoChannel->Actor->Avatars" + ON "Video->VideoChannel->Actor"."id" = "Video->VideoChannel->Actor->Avatars"."actorId" + AND "Video->VideoChannel->Actor->Avatars"."type" = ${ActorImageType.AVATAR} + LEFT JOIN "server" AS "Video->VideoChannel->Actor->Server" + ON "Video->VideoChannel->Actor"."serverId" = "Video->VideoChannel->Actor->Server"."id" + ) ON "UserNotificationModel"."videoId" = "Video"."id" + + LEFT JOIN ( + "videoComment" AS "VideoComment" + INNER JOIN "account" AS "VideoComment->Account" ON "VideoComment"."accountId" = "VideoComment->Account"."id" + INNER JOIN "actor" AS "VideoComment->Account->Actor" ON "VideoComment->Account"."actorId" = "VideoComment->Account->Actor"."id" + LEFT JOIN "actorImage" AS "VideoComment->Account->Actor->Avatars" + ON "VideoComment->Account->Actor"."id" = "VideoComment->Account->Actor->Avatars"."actorId" + AND "VideoComment->Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR} + LEFT JOIN "server" AS "VideoComment->Account->Actor->Server" + ON "VideoComment->Account->Actor"."serverId" = "VideoComment->Account->Actor->Server"."id" + INNER JOIN "video" AS "VideoComment->Video" ON "VideoComment"."videoId" = "VideoComment->Video"."id" + ) ON "UserNotificationModel"."commentId" = "VideoComment"."id" + + LEFT JOIN "abuse" AS "Abuse" ON "UserNotificationModel"."abuseId" = "Abuse"."id" + LEFT JOIN "videoAbuse" AS "Abuse->VideoAbuse" ON "Abuse"."id" = "Abuse->VideoAbuse"."abuseId" + LEFT JOIN "video" AS "Abuse->VideoAbuse->Video" ON "Abuse->VideoAbuse"."videoId" = "Abuse->VideoAbuse->Video"."id" + LEFT JOIN "commentAbuse" AS "Abuse->VideoCommentAbuse" ON "Abuse"."id" = "Abuse->VideoCommentAbuse"."abuseId" + LEFT JOIN "videoComment" AS "Abuse->VideoCommentAbuse->VideoComment" + ON "Abuse->VideoCommentAbuse"."videoCommentId" = "Abuse->VideoCommentAbuse->VideoComment"."id" + LEFT JOIN "video" AS "Abuse->VideoCommentAbuse->VideoComment->Video" + ON "Abuse->VideoCommentAbuse->VideoComment"."videoId" = "Abuse->VideoCommentAbuse->VideoComment->Video"."id" + LEFT JOIN ( + "account" AS "Abuse->FlaggedAccount" + INNER JOIN "actor" AS "Abuse->FlaggedAccount->Actor" ON "Abuse->FlaggedAccount"."actorId" = "Abuse->FlaggedAccount->Actor"."id" + LEFT JOIN "actorImage" AS "Abuse->FlaggedAccount->Actor->Avatars" + ON "Abuse->FlaggedAccount->Actor"."id" = "Abuse->FlaggedAccount->Actor->Avatars"."actorId" + AND "Abuse->FlaggedAccount->Actor->Avatars"."type" = ${ActorImageType.AVATAR} + LEFT JOIN "server" AS "Abuse->FlaggedAccount->Actor->Server" + ON "Abuse->FlaggedAccount->Actor"."serverId" = "Abuse->FlaggedAccount->Actor->Server"."id" + ) ON "Abuse"."flaggedAccountId" = "Abuse->FlaggedAccount"."id" + + LEFT JOIN ( + "videoBlacklist" AS "VideoBlacklist" + INNER JOIN "video" AS "VideoBlacklist->Video" ON "VideoBlacklist"."videoId" = "VideoBlacklist->Video"."id" + ) ON "UserNotificationModel"."videoBlacklistId" = "VideoBlacklist"."id" + + LEFT JOIN "videoImport" AS "VideoImport" ON "UserNotificationModel"."videoImportId" = "VideoImport"."id" + LEFT JOIN "video" AS "VideoImport->Video" ON "VideoImport"."videoId" = "VideoImport->Video"."id" + + LEFT JOIN "plugin" AS "Plugin" ON "UserNotificationModel"."pluginId" = "Plugin"."id" + + LEFT JOIN "application" AS "Application" ON "UserNotificationModel"."applicationId" = "Application"."id" + + LEFT JOIN ( + "actorFollow" AS "ActorFollow" + INNER JOIN "actor" AS "ActorFollow->ActorFollower" ON "ActorFollow"."actorId" = "ActorFollow->ActorFollower"."id" + INNER JOIN "account" AS "ActorFollow->ActorFollower->Account" + ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Account"."actorId" + LEFT JOIN "actorImage" AS "ActorFollow->ActorFollower->Avatars" + ON "ActorFollow->ActorFollower"."id" = "ActorFollow->ActorFollower->Avatars"."actorId" + AND "ActorFollow->ActorFollower->Avatars"."type" = ${ActorImageType.AVATAR} + LEFT JOIN "server" AS "ActorFollow->ActorFollower->Server" + ON "ActorFollow->ActorFollower"."serverId" = "ActorFollow->ActorFollower->Server"."id" + INNER JOIN "actor" AS "ActorFollow->ActorFollowing" ON "ActorFollow"."targetActorId" = "ActorFollow->ActorFollowing"."id" + LEFT JOIN "videoChannel" AS "ActorFollow->ActorFollowing->VideoChannel" + ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->VideoChannel"."actorId" + LEFT JOIN "account" AS "ActorFollow->ActorFollowing->Account" + ON "ActorFollow->ActorFollowing"."id" = "ActorFollow->ActorFollowing->Account"."actorId" + LEFT JOIN "server" AS "ActorFollow->ActorFollowing->Server" + ON "ActorFollow->ActorFollowing"."serverId" = "ActorFollow->ActorFollowing->Server"."id" + ) ON "UserNotificationModel"."actorFollowId" = "ActorFollow"."id" + + LEFT JOIN ( + "account" AS "Account" + INNER JOIN "actor" AS "Account->Actor" ON "Account"."actorId" = "Account->Actor"."id" + LEFT JOIN "actorImage" AS "Account->Actor->Avatars" + ON "Account->Actor"."id" = "Account->Actor->Avatars"."actorId" + AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR} + LEFT JOIN "server" AS "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id" + ) ON "UserNotificationModel"."accountId" = "Account"."id"` + } +} diff --git a/server/models/user/user-notification.ts b/server/models/user/user-notification.ts index edad10a55..eca127e7e 100644 --- a/server/models/user/user-notification.ts +++ b/server/models/user/user-notification.ts @@ -1,5 +1,6 @@ -import { FindOptions, ModelIndexesOptions, Op, WhereOptions } from 'sequelize' -import { AllowNull, BelongsTo, Column, CreatedAt, Default, ForeignKey, Is, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript' +import { ModelIndexesOptions, Op, WhereOptions } from 'sequelize' +import { AllowNull, BelongsTo, Column, CreatedAt, Default, ForeignKey, Is, Model, Table, UpdatedAt } from 'sequelize-typescript' +import { getBiggestActorImage } from '@server/lib/actor-image' import { UserNotificationIncludes, UserNotificationModelForApi } from '@server/types/models/user' import { uuidToShort } from '@shared/extra-utils' import { UserNotification, UserNotificationType } from '@shared/models' @@ -7,207 +8,18 @@ import { AttributesOnly } from '@shared/typescript-utils' import { isBooleanValid } from '../../helpers/custom-validators/misc' import { isUserNotificationTypeValid } from '../../helpers/custom-validators/user-notifications' import { AbuseModel } from '../abuse/abuse' -import { VideoAbuseModel } from '../abuse/video-abuse' -import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse' import { AccountModel } from '../account/account' -import { ActorModel } from '../actor/actor' import { ActorFollowModel } from '../actor/actor-follow' -import { ActorImageModel } from '../actor/actor-image' import { ApplicationModel } from '../application/application' import { PluginModel } from '../server/plugin' -import { ServerModel } from '../server/server' -import { getSort, throwIfNotValid } from '../utils' +import { throwIfNotValid } from '../utils' import { VideoModel } from '../video/video' import { VideoBlacklistModel } from '../video/video-blacklist' -import { VideoChannelModel } from '../video/video-channel' import { VideoCommentModel } from '../video/video-comment' import { VideoImportModel } from '../video/video-import' +import { UserNotificationListQueryBuilder } from './sql/user-notitication-list-query-builder' import { UserModel } from './user' -enum ScopeNames { - WITH_ALL = 'WITH_ALL' -} - -function buildActorWithAvatarInclude () { - return { - attributes: [ 'preferredUsername' ], - model: ActorModel.unscoped(), - required: true, - include: [ - { - attributes: [ 'filename' ], - as: 'Avatar', - model: ActorImageModel.unscoped(), - required: false - }, - { - attributes: [ 'host' ], - model: ServerModel.unscoped(), - required: false - } - ] - } -} - -function buildVideoInclude (required: boolean) { - return { - attributes: [ 'id', 'uuid', 'name' ], - model: VideoModel.unscoped(), - required - } -} - -function buildChannelInclude (required: boolean, withActor = false) { - return { - required, - attributes: [ 'id', 'name' ], - model: VideoChannelModel.unscoped(), - include: withActor === true ? [ buildActorWithAvatarInclude() ] : [] - } -} - -function buildAccountInclude (required: boolean, withActor = false) { - return { - required, - attributes: [ 'id', 'name' ], - model: AccountModel.unscoped(), - include: withActor === true ? [ buildActorWithAvatarInclude() ] : [] - } -} - -@Scopes(() => ({ - [ScopeNames.WITH_ALL]: { - include: [ - Object.assign(buildVideoInclude(false), { - include: [ buildChannelInclude(true, true) ] - }), - - { - attributes: [ 'id', 'originCommentId' ], - model: VideoCommentModel.unscoped(), - required: false, - include: [ - buildAccountInclude(true, true), - buildVideoInclude(true) - ] - }, - - { - attributes: [ 'id', 'state' ], - model: AbuseModel.unscoped(), - required: false, - include: [ - { - attributes: [ 'id' ], - model: VideoAbuseModel.unscoped(), - required: false, - include: [ buildVideoInclude(false) ] - }, - { - attributes: [ 'id' ], - model: VideoCommentAbuseModel.unscoped(), - required: false, - include: [ - { - attributes: [ 'id', 'originCommentId' ], - model: VideoCommentModel.unscoped(), - required: false, - include: [ - { - attributes: [ 'id', 'name', 'uuid' ], - model: VideoModel.unscoped(), - required: false - } - ] - } - ] - }, - { - model: AccountModel, - as: 'FlaggedAccount', - required: false, - include: [ buildActorWithAvatarInclude() ] - } - ] - }, - - { - attributes: [ 'id' ], - model: VideoBlacklistModel.unscoped(), - required: false, - include: [ buildVideoInclude(true) ] - }, - - { - attributes: [ 'id', 'magnetUri', 'targetUrl', 'torrentName' ], - model: VideoImportModel.unscoped(), - required: false, - include: [ buildVideoInclude(false) ] - }, - - { - attributes: [ 'id', 'name', 'type', 'latestVersion' ], - model: PluginModel.unscoped(), - required: false - }, - - { - attributes: [ 'id', 'latestPeerTubeVersion' ], - model: ApplicationModel.unscoped(), - required: false - }, - - { - attributes: [ 'id', 'state' ], - model: ActorFollowModel.unscoped(), - required: false, - include: [ - { - attributes: [ 'preferredUsername' ], - model: ActorModel.unscoped(), - required: true, - as: 'ActorFollower', - include: [ - { - attributes: [ 'id', 'name' ], - model: AccountModel.unscoped(), - required: true - }, - { - attributes: [ 'filename' ], - as: 'Avatar', - model: ActorImageModel.unscoped(), - required: false - }, - { - attributes: [ 'host' ], - model: ServerModel.unscoped(), - required: false - } - ] - }, - { - attributes: [ 'preferredUsername', 'type' ], - model: ActorModel.unscoped(), - required: true, - as: 'ActorFollowing', - include: [ - buildChannelInclude(false), - buildAccountInclude(false), - { - attributes: [ 'host' ], - model: ServerModel.unscoped(), - required: false - } - ] - } - ] - }, - - buildAccountInclude(false, true) - ] - } -})) @Table({ tableName: 'userNotification', indexes: [ @@ -342,7 +154,7 @@ export class UserNotificationModel extends Model AbuseModel) @Column @@ -431,11 +243,14 @@ export class UserNotificationModel extends Model count || 0), count === 0 - ? [] - : UserNotificationModel.scope(ScopeNames.WITH_ALL).findAll(query) + ? [] as UserNotificationModelForApi[] + : new UserNotificationListQueryBuilder(query).listNotifications() ]).then(([ total, data ]) => ({ total, data })) } @@ -524,25 +339,31 @@ export class UserNotificationModel extends Model this.formatAvatar(a)) + } + } + + formatAvatar (a: UserNotificationIncludes.ActorImageInclude) { + return { + path: a.getStaticPath(), + width: a.width } } } diff --git a/server/models/user/user.ts b/server/models/user/user.ts index ad8ce08cb..bcf56dfa1 100644 --- a/server/models/user/user.ts +++ b/server/models/user/user.ts @@ -106,7 +106,7 @@ enum ScopeNames { include: [ { model: ActorImageModel, - as: 'Banner', + as: 'Banners', required: false } ] @@ -495,13 +495,10 @@ export class UserModel extends Model>> { where } - return UserModel.findAndCountAll(query) - .then(({ rows, count }) => { - return { - data: rows, - total: count - } - }) + return Promise.all([ + UserModel.unscoped().count(query), + UserModel.findAll(query) + ]).then(([ total, data ]) => ({ total, data })) } static listWithRight (right: UserRight): Promise { diff --git a/server/models/utils.ts b/server/models/utils.ts index 66b653e3d..70bfbdb8b 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -181,7 +181,7 @@ function buildServerIdsFollowedBy (actorId: any) { 'SELECT "actor"."serverId" FROM "actorFollow" ' + 'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' + 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + - ')' + ')' } function buildWhereIdOrUUID (id: number | string) { 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 } ] diff --git a/server/tests/api/check-params/video-channels.ts b/server/tests/api/check-params/video-channels.ts index 1e9732fe9..5c2650fac 100644 --- a/server/tests/api/check-params/video-channels.ts +++ b/server/tests/api/check-params/video-channels.ts @@ -228,7 +228,7 @@ describe('Test video channels API validator', function () { }) }) - describe('When updating video channel avatar/banner', function () { + describe('When updating video channel avatars/banners', function () { const types = [ 'avatar', 'banner' ] let path: string diff --git a/server/tests/api/moderation/abuses.ts b/server/tests/api/moderation/abuses.ts index 0c3bed3e7..7bf49c7ec 100644 --- a/server/tests/api/moderation/abuses.ts +++ b/server/tests/api/moderation/abuses.ts @@ -2,6 +2,7 @@ import 'mocha' import * as chai from 'chai' +import { AbuseMessage, AbusePredefinedReasonsString, AbuseState, AdminAbuse, UserAbuse } from '@shared/models' import { AbusesCommand, cleanupTests, @@ -9,9 +10,10 @@ import { doubleFollow, PeerTubeServer, setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar, waitJobs } from '@shared/server-commands' -import { AbuseMessage, AbusePredefinedReasonsString, AbuseState, AdminAbuse, UserAbuse } from '@shared/models' const expect = chai.expect @@ -27,8 +29,9 @@ describe('Test abuses', function () { // Run servers servers = await createMultipleServers(2) - // Get the access tokens await setAccessTokensToServers(servers) + await setDefaultChannelAvatar(servers) + await setDefaultAccountAvatar(servers) // Server 1 and server 2 follow each other await doubleFollow(servers[0], servers[1]) diff --git a/server/tests/api/moderation/blocklist.ts b/server/tests/api/moderation/blocklist.ts index b45460bb4..e1344a245 100644 --- a/server/tests/api/moderation/blocklist.ts +++ b/server/tests/api/moderation/blocklist.ts @@ -2,6 +2,7 @@ import 'mocha' import * as chai from 'chai' +import { UserNotificationType } from '@shared/models' import { BlocklistCommand, cleanupTests, @@ -10,9 +11,9 @@ import { doubleFollow, PeerTubeServer, setAccessTokensToServers, + setDefaultAccountAvatar, waitJobs } from '@shared/server-commands' -import { UserNotificationType } from '@shared/models' const expect = chai.expect @@ -79,6 +80,7 @@ describe('Test blocklist', function () { servers = await createMultipleServers(3) await setAccessTokensToServers(servers) + await setDefaultAccountAvatar(servers) command = servers[0].blocklist commentsCommand = servers.map(s => s.comments) diff --git a/server/tests/api/moderation/video-blacklist.ts b/server/tests/api/moderation/video-blacklist.ts index 3e7f2ba33..1790210ff 100644 --- a/server/tests/api/moderation/video-blacklist.ts +++ b/server/tests/api/moderation/video-blacklist.ts @@ -13,6 +13,7 @@ import { killallServers, PeerTubeServer, setAccessTokensToServers, + setDefaultChannelAvatar, waitJobs } from '@shared/server-commands' @@ -42,6 +43,7 @@ describe('Test video blacklist', function () { // Server 1 and server 2 follow each other await doubleFollow(servers[0], servers[1]) + await setDefaultChannelAvatar(servers[0]) // Upload 2 videos on server 2 await servers[1].videos.upload({ attributes: { name: 'My 1st video', description: 'A video on server 2' } }) diff --git a/server/tests/api/notifications/notifications-api.ts b/server/tests/api/notifications/notifications-api.ts index ac08449f8..78864c8a0 100644 --- a/server/tests/api/notifications/notifications-api.ts +++ b/server/tests/api/notifications/notifications-api.ts @@ -38,6 +38,16 @@ describe('Test notifications API', function () { await waitJobs([ server ]) }) + describe('Notification list & count', function () { + + it('Should correctly list notifications', async function () { + const { data, total } = await server.notifications.list({ token: userToken, start: 0, count: 2 }) + + expect(data).to.have.lengthOf(2) + expect(total).to.equal(10) + }) + }) + describe('Mark as read', function () { it('Should mark as read some notifications', async function () { diff --git a/server/tests/api/search/search-activitypub-video-channels.ts b/server/tests/api/search/search-activitypub-video-channels.ts index 2e0abc6ba..5f5322d03 100644 --- a/server/tests/api/search/search-activitypub-video-channels.ts +++ b/server/tests/api/search/search-activitypub-video-channels.ts @@ -10,6 +10,8 @@ import { PeerTubeServer, SearchCommand, setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultVideoChannel, waitJobs } from '@shared/server-commands' @@ -28,6 +30,8 @@ describe('Test ActivityPub video channels search', function () { servers = await createMultipleServers(2) await setAccessTokensToServers(servers) + await setDefaultVideoChannel(servers) + await setDefaultAccountAvatar(servers) { await servers[0].users.create({ username: 'user1_server1', password: 'password' }) diff --git a/server/tests/api/search/search-activitypub-video-playlists.ts b/server/tests/api/search/search-activitypub-video-playlists.ts index d9243ac53..b9a424292 100644 --- a/server/tests/api/search/search-activitypub-video-playlists.ts +++ b/server/tests/api/search/search-activitypub-video-playlists.ts @@ -10,6 +10,7 @@ import { PeerTubeServer, SearchCommand, setAccessTokensToServers, + setDefaultAccountAvatar, setDefaultVideoChannel, waitJobs } from '@shared/server-commands' @@ -31,6 +32,7 @@ describe('Test ActivityPub playlists search', function () { await setAccessTokensToServers(servers) await setDefaultVideoChannel(servers) + await setDefaultAccountAvatar(servers) { const video1 = (await servers[0].videos.quickUpload({ name: 'video 1' })).uuid diff --git a/server/tests/api/search/search-activitypub-videos.ts b/server/tests/api/search/search-activitypub-videos.ts index 60b95ae4c..20249b1f1 100644 --- a/server/tests/api/search/search-activitypub-videos.ts +++ b/server/tests/api/search/search-activitypub-videos.ts @@ -10,6 +10,8 @@ import { PeerTubeServer, SearchCommand, setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultVideoChannel, waitJobs } from '@shared/server-commands' @@ -28,6 +30,8 @@ describe('Test ActivityPub videos search', function () { servers = await createMultipleServers(2) await setAccessTokensToServers(servers) + await setDefaultVideoChannel(servers) + await setDefaultAccountAvatar(servers) { const { uuid } = await servers[0].videos.upload({ attributes: { name: 'video 1 on server 1' } }) diff --git a/server/tests/api/search/search-channels.ts b/server/tests/api/search/search-channels.ts index 8a92def61..0073c71e1 100644 --- a/server/tests/api/search/search-channels.ts +++ b/server/tests/api/search/search-channels.ts @@ -2,15 +2,17 @@ import 'mocha' import * as chai from 'chai' +import { VideoChannel } from '@shared/models' import { cleanupTests, createSingleServer, doubleFollow, PeerTubeServer, SearchCommand, - setAccessTokensToServers + setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar } from '@shared/server-commands' -import { VideoChannel } from '@shared/models' const expect = chai.expect @@ -30,6 +32,8 @@ describe('Test channels search', function () { remoteServer = servers[1] await setAccessTokensToServers([ server, remoteServer ]) + await setDefaultChannelAvatar(server) + await setDefaultAccountAvatar(server) { await server.users.create({ username: 'user1' }) diff --git a/server/tests/api/search/search-index.ts b/server/tests/api/search/search-index.ts index f84d03345..ae933449f 100644 --- a/server/tests/api/search/search-index.ts +++ b/server/tests/api/search/search-index.ts @@ -14,7 +14,7 @@ import { const expect = chai.expect -describe('Test videos search', function () { +describe('Test index search', function () { const localVideoName = 'local video' + new Date().toISOString() let server: PeerTubeServer = null @@ -134,12 +134,16 @@ describe('Test videos search', function () { expect(video.account.host).to.equal('framatube.org') expect(video.account.name).to.equal('framasoft') expect(video.account.url).to.equal('https://framatube.org/accounts/framasoft') + // TODO: remove, deprecated in 4.2 expect(video.account.avatar).to.exist + expect(video.account.avatars.length).to.equal(1, 'Account should have one avatar image') expect(video.channel.host).to.equal('framatube.org') expect(video.channel.name).to.equal('joinpeertube') expect(video.channel.url).to.equal('https://framatube.org/video-channels/joinpeertube') + // TODO: remove, deprecated in 4.2 expect(video.channel.avatar).to.exist + expect(video.channel.avatars.length).to.equal(1, 'Channel should have one avatar image') } const baseSearch: VideosSearchQuery = { @@ -316,13 +320,17 @@ describe('Test videos search', function () { const videoChannel = body.data[0] expect(videoChannel.url).to.equal('https://framatube.org/video-channels/bf54d359-cfad-4935-9d45-9d6be93f63e8') expect(videoChannel.host).to.equal('framatube.org') + // TODO: remove, deprecated in 4.2 expect(videoChannel.avatar).to.exist + expect(videoChannel.avatars.length).to.equal(1, 'Channel should have two avatar images') expect(videoChannel.displayName).to.exist expect(videoChannel.ownerAccount.url).to.equal('https://framatube.org/accounts/framasoft') expect(videoChannel.ownerAccount.name).to.equal('framasoft') expect(videoChannel.ownerAccount.host).to.equal('framatube.org') + // TODO: remove, deprecated in 4.2 expect(videoChannel.ownerAccount.avatar).to.exist + expect(videoChannel.ownerAccount.avatars.length).to.equal(1, 'Account should have two avatar images') } it('Should make a simple search and not have results', async function () { @@ -388,12 +396,16 @@ describe('Test videos search', function () { expect(videoPlaylist.ownerAccount.url).to.equal('https://peertube2.cpy.re/accounts/chocobozzz') expect(videoPlaylist.ownerAccount.name).to.equal('chocobozzz') expect(videoPlaylist.ownerAccount.host).to.equal('peertube2.cpy.re') + // TODO: remove, deprecated in 4.2 expect(videoPlaylist.ownerAccount.avatar).to.exist + expect(videoPlaylist.ownerAccount.avatars.length).to.equal(1, 'Account should have two avatar images') expect(videoPlaylist.videoChannel.url).to.equal('https://peertube2.cpy.re/video-channels/chocobozzz_channel') expect(videoPlaylist.videoChannel.name).to.equal('chocobozzz_channel') expect(videoPlaylist.videoChannel.host).to.equal('peertube2.cpy.re') + // TODO: remove, deprecated in 4.2 expect(videoPlaylist.videoChannel.avatar).to.exist + expect(videoPlaylist.videoChannel.avatars.length).to.equal(1, 'Channel should have two avatar images') } it('Should make a simple search and not have results', async function () { diff --git a/server/tests/api/search/search-playlists.ts b/server/tests/api/search/search-playlists.ts index 1e9c8d4bb..fcf2f2ee2 100644 --- a/server/tests/api/search/search-playlists.ts +++ b/server/tests/api/search/search-playlists.ts @@ -2,6 +2,7 @@ import 'mocha' import * as chai from 'chai' +import { VideoPlaylistPrivacy } from '@shared/models' import { cleanupTests, createSingleServer, @@ -9,9 +10,10 @@ import { PeerTubeServer, SearchCommand, setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar, setDefaultVideoChannel } from '@shared/server-commands' -import { VideoPlaylistPrivacy } from '@shared/models' const expect = chai.expect @@ -34,6 +36,8 @@ describe('Test playlists search', function () { await setAccessTokensToServers([ remoteServer, server ]) await setDefaultVideoChannel([ remoteServer, server ]) + await setDefaultChannelAvatar([ remoteServer, server ]) + await setDefaultAccountAvatar([ remoteServer, server ]) { const videoId = (await server.videos.upload()).uuid diff --git a/server/tests/api/search/search-videos.ts b/server/tests/api/search/search-videos.ts index c544705d3..ff4c3c161 100644 --- a/server/tests/api/search/search-videos.ts +++ b/server/tests/api/search/search-videos.ts @@ -2,6 +2,8 @@ import 'mocha' import * as chai from 'chai' +import { wait } from '@shared/core-utils' +import { VideoPrivacy } from '@shared/models' import { cleanupTests, createSingleServer, @@ -9,11 +11,11 @@ import { PeerTubeServer, SearchCommand, setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar, setDefaultVideoChannel, stopFfmpeg } from '@shared/server-commands' -import { VideoPrivacy } from '@shared/models' -import { wait } from '@shared/core-utils' const expect = chai.expect @@ -38,6 +40,8 @@ describe('Test videos search', function () { await setAccessTokensToServers([ server, remoteServer ]) await setDefaultVideoChannel([ server, remoteServer ]) + await setDefaultChannelAvatar(server) + await setDefaultAccountAvatar(servers) { const attributes1 = { diff --git a/server/tests/api/server/homepage.ts b/server/tests/api/server/homepage.ts index 552ee98cf..e7de6bfee 100644 --- a/server/tests/api/server/homepage.ts +++ b/server/tests/api/server/homepage.ts @@ -9,7 +9,9 @@ import { CustomPagesCommand, killallServers, PeerTubeServer, - setAccessTokensToServers + setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar } from '../../../../shared/server-commands/index' const expect = chai.expect @@ -29,6 +31,8 @@ describe('Test instance homepage actions', function () { server = await createSingleServer(1) await setAccessTokensToServers([ server ]) + await setDefaultChannelAvatar(server) + await setDefaultAccountAvatar(server) command = server.customPage }) diff --git a/server/tests/api/users/user-subscriptions.ts b/server/tests/api/users/user-subscriptions.ts index 57cca6ad4..9553a69bb 100644 --- a/server/tests/api/users/user-subscriptions.ts +++ b/server/tests/api/users/user-subscriptions.ts @@ -9,6 +9,8 @@ import { doubleFollow, PeerTubeServer, setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar, SubscriptionsCommand, waitJobs } from '@shared/server-commands' @@ -29,6 +31,8 @@ describe('Test users subscriptions', function () { // Get the access tokens await setAccessTokensToServers(servers) + await setDefaultChannelAvatar(servers) + await setDefaultAccountAvatar(servers) // Server 1 and server 2 follow each other await doubleFollow(servers[0], servers[1]) diff --git a/server/tests/api/users/users-multiple-servers.ts b/server/tests/api/users/users-multiple-servers.ts index 5b2bbc520..3e8b932c0 100644 --- a/server/tests/api/users/users-multiple-servers.ts +++ b/server/tests/api/users/users-multiple-servers.ts @@ -16,6 +16,7 @@ import { doubleFollow, PeerTubeServer, setAccessTokensToServers, + setDefaultChannelAvatar, waitJobs } from '@shared/server-commands' @@ -29,7 +30,7 @@ describe('Test users with multiple servers', function () { let videoUUID: string let userAccessToken: string - let userAvatarFilename: string + let userAvatarFilenames: string[] before(async function () { this.timeout(120_000) @@ -38,6 +39,7 @@ describe('Test users with multiple servers', function () { // Get the access tokens await setAccessTokensToServers(servers) + await setDefaultChannelAvatar(servers) // Server 1 and server 2 follow each other await doubleFollow(servers[0], servers[1]) @@ -97,9 +99,11 @@ describe('Test users with multiple servers', function () { await servers[0].users.updateMyAvatar({ fixture }) user = await servers[0].users.getMyInfo() - userAvatarFilename = user.account.avatar.path + userAvatarFilenames = user.account.avatars.map(({ path }) => path) - await testImage(servers[0].url, 'avatar2-resized', userAvatarFilename, '.png') + for (const avatar of user.account.avatars) { + await testImage(servers[0].url, `avatar2-resized-${avatar.width}x${avatar.width}`, avatar.path, '.png') + } await waitJobs(servers) }) @@ -129,7 +133,9 @@ describe('Test users with multiple servers', function () { expect(account.userId).to.be.undefined } - await testImage(server.url, 'avatar2-resized', account.avatar.path, '.png') + for (const avatar of account.avatars) { + await testImage(server.url, `avatar2-resized-${avatar.width}x${avatar.width}`, avatar.path, '.png') + } } }) @@ -193,7 +199,9 @@ describe('Test users with multiple servers', function () { it('Should not have actor files', async () => { for (const server of servers) { - await checkActorFilesWereRemoved(userAvatarFilename, server.internalServerNumber) + for (const userAvatarFilename of userAvatarFilenames) { + await checkActorFilesWereRemoved(userAvatarFilename, server.internalServerNumber) + } } }) diff --git a/server/tests/api/users/users.ts b/server/tests/api/users/users.ts index 7023b3f08..a47713bf0 100644 --- a/server/tests/api/users/users.ts +++ b/server/tests/api/users/users.ts @@ -604,7 +604,9 @@ describe('Test users', function () { await server.users.updateMyAvatar({ token: userToken, fixture }) const user = await server.users.getMyInfo({ token: userToken }) - await testImage(server.url, 'avatar-resized', user.account.avatar.path, '.gif') + for (const avatar of user.account.avatars) { + await testImage(server.url, `avatar-resized-${avatar.width}x${avatar.width}`, avatar.path, '.gif') + } }) it('Should be able to update my avatar with a gif, and then a png', async function () { @@ -614,7 +616,9 @@ describe('Test users', function () { await server.users.updateMyAvatar({ token: userToken, fixture }) const user = await server.users.getMyInfo({ token: userToken }) - await testImage(server.url, 'avatar-resized', user.account.avatar.path, extension) + for (const avatar of user.account.avatars) { + await testImage(server.url, `avatar-resized-${avatar.width}x${avatar.width}`, avatar.path, extension) + } } }) diff --git a/server/tests/api/videos/multiple-servers.ts b/server/tests/api/videos/multiple-servers.ts index ecdd36613..5bbc60559 100644 --- a/server/tests/api/videos/multiple-servers.ts +++ b/server/tests/api/videos/multiple-servers.ts @@ -19,6 +19,8 @@ import { doubleFollow, PeerTubeServer, setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar, waitJobs, webtorrentAdd } from '@shared/server-commands' @@ -46,6 +48,9 @@ describe('Test multiple servers', function () { description: 'super channel' } await servers[0].channels.create({ attributes: videoChannel }) + await setDefaultChannelAvatar(servers[0], videoChannel.name) + await setDefaultAccountAvatar(servers) + const { data } = await servers[0].channels.list({ start: 0, count: 1 }) videoChannelId = data[0].id } @@ -207,7 +212,7 @@ describe('Test multiple servers', function () { }, { resolution: 720, - size: 788000 + size: 750000 } ], thumbnailfile: 'thumbnail', diff --git a/server/tests/api/videos/single-server.ts b/server/tests/api/videos/single-server.ts index 28bf018c5..d37043aef 100644 --- a/server/tests/api/videos/single-server.ts +++ b/server/tests/api/videos/single-server.ts @@ -5,7 +5,14 @@ import * as chai from 'chai' import { checkVideoFilesWereRemoved, completeVideoCheck, testImage } from '@server/tests/shared' import { wait } from '@shared/core-utils' import { Video, VideoPrivacy } from '@shared/models' -import { cleanupTests, createSingleServer, PeerTubeServer, setAccessTokensToServers } from '@shared/server-commands' +import { + cleanupTests, + createSingleServer, + PeerTubeServer, + setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar +} from '@shared/server-commands' const expect = chai.expect @@ -90,6 +97,8 @@ describe('Test a single server', function () { server = await createSingleServer(1) await setAccessTokensToServers([ server ]) + await setDefaultChannelAvatar(server) + await setDefaultAccountAvatar(server) }) it('Should list video categories', async function () { diff --git a/server/tests/api/videos/video-channels.ts b/server/tests/api/videos/video-channels.ts index d435f3682..0f8227fd3 100644 --- a/server/tests/api/videos/video-channels.ts +++ b/server/tests/api/videos/video-channels.ts @@ -6,13 +6,14 @@ import { basename } from 'path' import { ACTOR_IMAGES_SIZE } from '@server/initializers/constants' import { testFileExistsOrNot, testImage } from '@server/tests/shared' import { wait } from '@shared/core-utils' -import { User, VideoChannel } from '@shared/models' +import { ActorImageType, User, VideoChannel } from '@shared/models' import { cleanupTests, createMultipleServers, doubleFollow, PeerTubeServer, setAccessTokensToServers, + setDefaultAccountAvatar, setDefaultVideoChannel, waitJobs } from '@shared/server-commands' @@ -44,6 +45,7 @@ describe('Test video channels', function () { await setAccessTokensToServers(servers) await setDefaultVideoChannel(servers) + await setDefaultAccountAvatar(servers) await doubleFollow(servers[0], servers[1]) }) @@ -281,14 +283,19 @@ describe('Test video channels', function () { for (const server of servers) { const videoChannel = await findChannel(server, secondVideoChannelId) + const expectedSizes = ACTOR_IMAGES_SIZE[ActorImageType.AVATAR] - avatarPaths[server.port] = videoChannel.avatar.path - await testImage(server.url, 'avatar-resized', avatarPaths[server.port], '.png') - await testFileExistsOrNot(server, 'avatars', basename(avatarPaths[server.port]), true) + expect(videoChannel.avatars.length).to.equal(expectedSizes.length, 'Expected avatars to be generated in all sizes') - const row = await server.sql.getActorImage(basename(avatarPaths[server.port])) - expect(row.height).to.equal(ACTOR_IMAGES_SIZE.AVATARS.height) - expect(row.width).to.equal(ACTOR_IMAGES_SIZE.AVATARS.width) + for (const avatar of videoChannel.avatars) { + avatarPaths[server.port] = avatar.path + await testImage(server.url, `avatar-resized-${avatar.width}x${avatar.width}`, avatarPaths[server.port], '.png') + await testFileExistsOrNot(server, 'avatars', basename(avatarPaths[server.port]), true) + + const row = await server.sql.getActorImage(basename(avatarPaths[server.port])) + + expect(expectedSizes.some(({ height, width }) => row.height === height && row.width === width)).to.equal(true) + } } }) @@ -308,19 +315,18 @@ describe('Test video channels', function () { for (const server of servers) { const videoChannel = await server.channels.get({ channelName: 'second_video_channel@' + servers[0].host }) - bannerPaths[server.port] = videoChannel.banner.path + bannerPaths[server.port] = videoChannel.banners[0].path await testImage(server.url, 'banner-resized', bannerPaths[server.port]) await testFileExistsOrNot(server, 'avatars', basename(bannerPaths[server.port]), true) const row = await server.sql.getActorImage(basename(bannerPaths[server.port])) - expect(row.height).to.equal(ACTOR_IMAGES_SIZE.BANNERS.height) - expect(row.width).to.equal(ACTOR_IMAGES_SIZE.BANNERS.width) + expect(row.height).to.equal(ACTOR_IMAGES_SIZE[ActorImageType.BANNER][0].height) + expect(row.width).to.equal(ACTOR_IMAGES_SIZE[ActorImageType.BANNER][0].width) } }) it('Should delete the video channel avatar', async function () { this.timeout(15000) - await servers[0].channels.deleteImage({ channelName: 'second_video_channel', type: 'avatar' }) await waitJobs(servers) @@ -329,7 +335,7 @@ describe('Test video channels', function () { const videoChannel = await findChannel(server, secondVideoChannelId) await testFileExistsOrNot(server, 'avatars', basename(avatarPaths[server.port]), false) - expect(videoChannel.avatar).to.be.null + expect(videoChannel.avatars).to.be.empty } }) @@ -344,7 +350,7 @@ describe('Test video channels', function () { const videoChannel = await findChannel(server, secondVideoChannelId) await testFileExistsOrNot(server, 'avatars', basename(bannerPaths[server.port]), false) - expect(videoChannel.banner).to.be.null + expect(videoChannel.banners).to.be.empty } }) diff --git a/server/tests/api/videos/video-comments.ts b/server/tests/api/videos/video-comments.ts index 2ae523970..1488ce2b5 100644 --- a/server/tests/api/videos/video-comments.ts +++ b/server/tests/api/videos/video-comments.ts @@ -3,7 +3,15 @@ import 'mocha' import * as chai from 'chai' import { dateIsValid, testImage } from '@server/tests/shared' -import { cleanupTests, CommentsCommand, createSingleServer, PeerTubeServer, setAccessTokensToServers } from '@shared/server-commands' +import { + cleanupTests, + CommentsCommand, + createSingleServer, + PeerTubeServer, + setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar +} from '@shared/server-commands' const expect = chai.expect @@ -29,7 +37,8 @@ describe('Test video comments', function () { videoUUID = uuid videoId = id - await server.users.updateMyAvatar({ fixture: 'avatar.png' }) + await setDefaultChannelAvatar(server) + await setDefaultAccountAvatar(server) userAccessTokenServer1 = await server.users.generateUserAndToken('user1') @@ -81,7 +90,9 @@ describe('Test video comments', function () { expect(comment.account.name).to.equal('root') expect(comment.account.host).to.equal('localhost:' + server.port) - await testImage(server.url, 'avatar-resized', comment.account.avatar.path, '.png') + for (const avatar of comment.account.avatars) { + await testImage(server.url, `avatar-resized-${avatar.width}x${avatar.width}`, avatar.path, '.png') + } expect(comment.totalReplies).to.equal(0) expect(comment.totalRepliesFromVideoAuthor).to.equal(0) diff --git a/server/tests/api/videos/video-playlists.ts b/server/tests/api/videos/video-playlists.ts index 34327334f..1e8dbef02 100644 --- a/server/tests/api/videos/video-playlists.ts +++ b/server/tests/api/videos/video-playlists.ts @@ -20,6 +20,7 @@ import { PeerTubeServer, PlaylistsCommand, setAccessTokensToServers, + setDefaultAccountAvatar, setDefaultVideoChannel, waitJobs } from '@shared/server-commands' @@ -79,6 +80,7 @@ describe('Test video playlists', function () { // Get the access tokens await setAccessTokensToServers(servers) await setDefaultVideoChannel(servers) + await setDefaultAccountAvatar(servers) // Server 1 and server 2 follow each other await doubleFollow(servers[0], servers[1]) diff --git a/server/tests/api/videos/videos-common-filters.ts b/server/tests/api/videos/videos-common-filters.ts index 0254662c5..317de90a9 100644 --- a/server/tests/api/videos/videos-common-filters.ts +++ b/server/tests/api/videos/videos-common-filters.ts @@ -3,6 +3,7 @@ import 'mocha' import { expect } from 'chai' import { pick } from '@shared/core-utils' +import { HttpStatusCode, UserRole, Video, VideoDetails, VideoInclude, VideoPrivacy } from '@shared/models' import { cleanupTests, createMultipleServers, @@ -10,10 +11,10 @@ import { makeGetRequest, PeerTubeServer, setAccessTokensToServers, + setDefaultAccountAvatar, setDefaultVideoChannel, waitJobs } from '@shared/server-commands' -import { HttpStatusCode, UserRole, Video, VideoDetails, VideoInclude, VideoPrivacy } from '@shared/models' describe('Test videos filter', function () { let servers: PeerTubeServer[] @@ -29,6 +30,7 @@ describe('Test videos filter', function () { await setAccessTokensToServers(servers) await setDefaultVideoChannel(servers) + await setDefaultAccountAvatar(servers) for (const server of servers) { const moderator = { username: 'moderator', password: 'my super password' } diff --git a/server/tests/cli/prune-storage.ts b/server/tests/cli/prune-storage.ts index a723ed8b4..3ca7c19ea 100644 --- a/server/tests/cli/prune-storage.ts +++ b/server/tests/cli/prune-storage.ts @@ -51,7 +51,7 @@ async function assertCountAreOkay (servers: PeerTubeServer[]) { expect(thumbnailsCount).to.equal(6) const avatarsCount = await countFiles(server, 'avatars') - expect(avatarsCount).to.equal(2) + expect(avatarsCount).to.equal(4) const hlsRootCount = await countFiles(server, 'streaming-playlists/hls') expect(hlsRootCount).to.equal(2) @@ -87,23 +87,28 @@ describe('Test prune storage scripts', function () { await doubleFollow(servers[0], servers[1]) - // Lazy load the remote avatar + // Lazy load the remote avatars { const account = await servers[0].accounts.get({ accountName: 'root@localhost:' + servers[1].port }) - await makeGetRequest({ - url: servers[0].url, - path: account.avatar.path, - expectedStatus: HttpStatusCode.OK_200 - }) + + for (const avatar of account.avatars) { + await makeGetRequest({ + url: servers[0].url, + path: avatar.path, + expectedStatus: HttpStatusCode.OK_200 + }) + } } { const account = await servers[1].accounts.get({ accountName: 'root@localhost:' + servers[0].port }) - await makeGetRequest({ - url: servers[1].url, - path: account.avatar.path, - expectedStatus: HttpStatusCode.OK_200 - }) + for (const avatar of account.avatars) { + await makeGetRequest({ + url: servers[1].url, + path: avatar.path, + expectedStatus: HttpStatusCode.OK_200 + }) + } } await wait(1000) diff --git a/server/tests/feeds/feeds.ts b/server/tests/feeds/feeds.ts index 4dcd77cca..320dc3333 100644 --- a/server/tests/feeds/feeds.ts +++ b/server/tests/feeds/feeds.ts @@ -3,6 +3,7 @@ import 'mocha' import * as chai from 'chai' import { XMLParser, XMLValidator } from 'fast-xml-parser' +import { HttpStatusCode, VideoPrivacy } from '@shared/models' import { cleanupTests, createMultipleServers, @@ -11,9 +12,9 @@ import { makeGetRequest, PeerTubeServer, setAccessTokensToServers, + setDefaultChannelAvatar, waitJobs } from '@shared/server-commands' -import { HttpStatusCode, VideoPrivacy } from '@shared/models' chai.use(require('chai-xml')) chai.use(require('chai-json-schema')) @@ -44,6 +45,7 @@ describe('Test syndication feeds', () => { }) await setAccessTokensToServers([ ...servers, serverHLSOnly ]) + await setDefaultChannelAvatar(servers[0]) await doubleFollow(servers[0], servers[1]) { diff --git a/server/tests/fixtures/avatar-resized-120x120.gif b/server/tests/fixtures/avatar-resized-120x120.gif new file mode 100644 index 000000000..81a82189e Binary files /dev/null and b/server/tests/fixtures/avatar-resized-120x120.gif differ diff --git a/server/tests/fixtures/avatar-resized-120x120.png b/server/tests/fixtures/avatar-resized-120x120.png new file mode 100644 index 000000000..9d84151f8 Binary files /dev/null and b/server/tests/fixtures/avatar-resized-120x120.png differ diff --git a/server/tests/fixtures/avatar-resized-48x48.gif b/server/tests/fixtures/avatar-resized-48x48.gif new file mode 100644 index 000000000..5900ff12e Binary files /dev/null and b/server/tests/fixtures/avatar-resized-48x48.gif differ diff --git a/server/tests/fixtures/avatar-resized-48x48.png b/server/tests/fixtures/avatar-resized-48x48.png new file mode 100644 index 000000000..9e5f3b490 Binary files /dev/null and b/server/tests/fixtures/avatar-resized-48x48.png differ diff --git a/server/tests/fixtures/avatar-resized.gif b/server/tests/fixtures/avatar-resized.gif deleted file mode 100644 index 81a82189e..000000000 Binary files a/server/tests/fixtures/avatar-resized.gif and /dev/null differ diff --git a/server/tests/fixtures/avatar-resized.png b/server/tests/fixtures/avatar-resized.png deleted file mode 100644 index 9d84151f8..000000000 Binary files a/server/tests/fixtures/avatar-resized.png and /dev/null differ diff --git a/server/tests/fixtures/avatar2-resized-120x120.png b/server/tests/fixtures/avatar2-resized-120x120.png new file mode 100644 index 000000000..44149facb Binary files /dev/null and b/server/tests/fixtures/avatar2-resized-120x120.png differ diff --git a/server/tests/fixtures/avatar2-resized-48x48.png b/server/tests/fixtures/avatar2-resized-48x48.png new file mode 100644 index 000000000..bb3939b1a Binary files /dev/null and b/server/tests/fixtures/avatar2-resized-48x48.png differ diff --git a/server/tests/fixtures/avatar2-resized.png b/server/tests/fixtures/avatar2-resized.png deleted file mode 100644 index 44149facb..000000000 Binary files a/server/tests/fixtures/avatar2-resized.png and /dev/null differ diff --git a/server/tests/shared/notifications.ts b/server/tests/shared/notifications.ts index cdc21fdc8..78d3787f0 100644 --- a/server/tests/shared/notifications.ts +++ b/server/tests/shared/notifications.ts @@ -10,7 +10,14 @@ import { UserNotificationSettingValue, UserNotificationType } from '@shared/models' -import { createMultipleServers, doubleFollow, PeerTubeServer, setAccessTokensToServers } from '@shared/server-commands' +import { + createMultipleServers, + doubleFollow, + PeerTubeServer, + setAccessTokensToServers, + setDefaultAccountAvatar, + setDefaultChannelAvatar +} from '@shared/server-commands' import { MockSmtpServer } from './mock-servers' type CheckerBaseParams = { @@ -646,6 +653,8 @@ async function prepareNotificationsTest (serversCount = 3, overrideConfigArg: an const servers = await createMultipleServers(serversCount, Object.assign(overrideConfig, overrideConfigArg)) await setAccessTokensToServers(servers) + await setDefaultChannelAvatar(servers) + await setDefaultAccountAvatar(servers) if (serversCount > 1) { await doubleFollow(servers[0], servers[1]) diff --git a/server/types/models/actor/actor-image.ts b/server/types/models/actor/actor-image.ts index 521b4cc59..e8f32b71e 100644 --- a/server/types/models/actor/actor-image.ts +++ b/server/types/models/actor/actor-image.ts @@ -9,4 +9,4 @@ export type MActorImage = ActorImageModel export type MActorImageFormattable = FunctionProperties & - Pick + Pick diff --git a/server/types/models/actor/actor.ts b/server/types/models/actor/actor.ts index 9ce97094f..280256bab 100644 --- a/server/types/models/actor/actor.ts +++ b/server/types/models/actor/actor.ts @@ -10,7 +10,7 @@ type UseOpt = PickWithOpt // ############################################################################ -export type MActor = Omit +export type MActor = Omit // ############################################################################ @@ -35,7 +35,7 @@ export type MActorRedundancyAllowedOpt = PickWithOpt & - Use<'Avatar', MActorImage> + Use<'Avatars', MActorImage[]> export type MActorAccountId = MActor & @@ -78,13 +78,13 @@ export type MActorServer = export type MActorImages = MActor & - Use<'Avatar', MActorImage> & - UseOpt<'Banner', MActorImage> + Use<'Avatars', MActorImage[]> & + UseOpt<'Banners', MActorImage[]> export type MActorDefault = MActor & Use<'Server', MServer> & - Use<'Avatar', MActorImage> + Use<'Avatars', MActorImage[]> export type MActorDefaultChannelId = MActorDefault & @@ -93,8 +93,8 @@ export type MActorDefaultChannelId = export type MActorDefaultBanner = MActor & Use<'Server', MServer> & - Use<'Avatar', MActorImage> & - Use<'Banner', MActorImage> + Use<'Avatars', MActorImage[]> & + Use<'Banners', MActorImage[]> // Actor with channel that is associated to an account and its actor // Actor -> VideoChannel -> Account -> Actor @@ -105,8 +105,8 @@ export type MActorChannelAccountActor = export type MActorFull = MActor & Use<'Server', MServer> & - Use<'Avatar', MActorImage> & - Use<'Banner', MActorImage> & + Use<'Avatars', MActorImage[]> & + Use<'Banners', MActorImage[]> & Use<'Account', MAccount> & Use<'VideoChannel', MChannelAccountActor> @@ -114,8 +114,8 @@ export type MActorFull = export type MActorFullActor = MActor & Use<'Server', MServer> & - Use<'Avatar', MActorImage> & - Use<'Banner', MActorImage> & + Use<'Avatars', MActorImage[]> & + Use<'Banners', MActorImage[]> & Use<'Account', MAccountDefault> & Use<'VideoChannel', MChannelAccountDefault> @@ -125,9 +125,9 @@ export type MActorFullActor = export type MActorSummary = FunctionProperties & - Pick & + Pick & Use<'Server', MServerHost> & - Use<'Avatar', MActorImage> + Use<'Avatars', MActorImage[]> export type MActorSummaryBlocks = MActorSummary & @@ -145,21 +145,22 @@ export type MActorSummaryFormattable = FunctionProperties & Pick & Use<'Server', MServerHost> & - Use<'Avatar', MActorImageFormattable> + Use<'Avatars', MActorImageFormattable[]> export type MActorFormattable = MActorSummaryFormattable & - Pick & + Pick & Use<'Server', MServerHost & Partial>> & - UseOpt<'Banner', MActorImageFormattable> + UseOpt<'Banners', MActorImageFormattable[]> & + UseOpt<'Avatars', MActorImageFormattable[]> type MActorAPBase = MActor & - Use<'Avatar', MActorImage> + Use<'Avatars', MActorImage[]> export type MActorAPAccount = MActorAPBase export type MActorAPChannel = MActorAPBase & - Use<'Banner', MActorImage> + Use<'Banners', MActorImage[]> diff --git a/server/types/models/user/user-notification.ts b/server/types/models/user/user-notification.ts index db9ec0400..d4715a0b6 100644 --- a/server/types/models/user/user-notification.ts +++ b/server/types/models/user/user-notification.ts @@ -21,6 +21,7 @@ type Use = PickWith export type VideoInclude = Pick export type VideoIncludeChannel = @@ -29,7 +30,7 @@ export module UserNotificationIncludes { export type ActorInclude = Pick & - PickWith> & + PickWith & PickWith> export type VideoChannelInclude = Pick @@ -75,7 +76,7 @@ export module UserNotificationIncludes { Pick & PickWith & PickWith> & - PickWithOpt> + PickWithOpt export type ActorFollowing = Pick & @@ -98,7 +99,7 @@ export module UserNotificationIncludes { // ############################################################################ export type MUserNotification = - Omit // ############################################################################ @@ -106,7 +107,7 @@ export type MUserNotification = export type UserNotificationModelForApi = MUserNotification & Use<'Video', UserNotificationIncludes.VideoIncludeChannel> & - Use<'Comment', UserNotificationIncludes.VideoCommentInclude> & + Use<'VideoComment', UserNotificationIncludes.VideoCommentInclude> & Use<'Abuse', UserNotificationIncludes.AbuseInclude> & Use<'VideoBlacklist', UserNotificationIncludes.VideoBlacklistInclude> & Use<'VideoImport', UserNotificationIncludes.VideoImportInclude> & -- cgit v1.2.3