X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=server%2Fmodels%2Fvideo%2Fvideo.ts;h=6fb5ececae5b29886202f312f0f90d44f88a3b3d;hb=9a629c6efbe39dfac290347670ca41b0d7100f41;hp=7acbc60f7cd19d06c2572f91134345ddea89aa7e;hpb=1297eb5db651a230474670c5da1517862fb9cc3e;p=github%2FChocobozzz%2FPeerTube.git diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 7acbc60f7..6fb5ececa 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -17,6 +17,7 @@ import { HasMany, HasOne, IFindOptions, + IIncludeOptions, Is, IsInt, IsUUID, @@ -30,15 +31,7 @@ import { VideoPrivacy, VideoResolution, VideoState } from '../../../shared' import { VideoTorrentObject } from '../../../shared/models/activitypub/objects' import { Video, VideoDetails, VideoFile } from '../../../shared/models/videos' import { VideoFilter } from '../../../shared/models/videos/video-query.type' -import { - copyFilePromise, - createTorrentPromise, - peertubeTruncate, - renamePromise, - statPromise, - unlinkPromise, - writeFilePromise -} from '../../helpers/core-utils' +import { createTorrentPromise, peertubeTruncate } from '../../helpers/core-utils' import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc' import { isBooleanValid } from '../../helpers/custom-validators/misc' import { @@ -84,7 +77,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate' import { ActorModel } from '../activitypub/actor' import { AvatarModel } from '../avatar/avatar' import { ServerModel } from '../server/server' -import { buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils' +import { buildTrigramSearchIndex, createSimilarityAttribute, getVideoSort, throwIfNotValid } from '../utils' import { TagModel } from './tag' import { VideoAbuseModel } from './video-abuse' import { VideoChannelModel } from './video-channel' @@ -95,6 +88,8 @@ import { VideoTagModel } from './video-tag' import { ScheduleVideoUpdateModel } from './schedule-video-update' import { VideoCaptionModel } from './video-caption' import { VideoBlacklistModel } from './video-blacklist' +import { copy, remove, rename, stat, writeFile } from 'fs-extra' +import { VideoViewModel } from './video-views' // FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation const indexes: Sequelize.DefineIndexesOptions[] = [ @@ -124,7 +119,8 @@ const indexes: Sequelize.DefineIndexesOptions[] = [ ] export enum ScopeNames { - AVAILABLE_FOR_LIST = 'AVAILABLE_FOR_LIST', + AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS', + FOR_API = 'FOR_API', WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS', WITH_TAGS = 'WITH_TAGS', WITH_FILES = 'WITH_FILES', @@ -132,34 +128,38 @@ export enum ScopeNames { WITH_BLACKLISTED = 'WITH_BLACKLISTED' } -type AvailableForListOptions = { - actorId: number, - includeLocalVideos: boolean, - filter?: VideoFilter, - categoryOneOf?: number[], - nsfw?: boolean, - licenceOneOf?: number[], - languageOneOf?: string[], - tagsOneOf?: string[], - tagsAllOf?: string[], - withFiles?: boolean, - accountId?: number, +type ForAPIOptions = { + ids: number[] + withFiles?: boolean +} + +type AvailableForListIDsOptions = { + actorId: number + includeLocalVideos: boolean + filter?: VideoFilter + categoryOneOf?: number[] + nsfw?: boolean + licenceOneOf?: number[] + languageOneOf?: string[] + tagsOneOf?: string[] + tagsAllOf?: string[] + withFiles?: boolean + accountId?: number videoChannelId?: number + trendingDays?: number } @Scopes({ - [ScopeNames.AVAILABLE_FOR_LIST]: (options: AvailableForListOptions) => { + [ScopeNames.FOR_API]: (options: ForAPIOptions) => { const accountInclude = { attributes: [ 'id', 'name' ], model: AccountModel.unscoped(), required: true, - where: {}, include: [ { attributes: [ 'id', 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ], model: ActorModel.unscoped(), required: true, - where: VideoModel.buildActorWhereWithFilter(options.filter), include: [ { attributes: [ 'host' ], @@ -179,7 +179,6 @@ type AvailableForListOptions = { attributes: [ 'name', 'description', 'id' ], model: VideoChannelModel.unscoped(), required: true, - where: {}, include: [ { attributes: [ 'uuid', 'preferredUsername', 'url', 'serverId', 'avatarId' ], @@ -201,40 +200,36 @@ type AvailableForListOptions = { ] } - // Force actorId to be a number to avoid SQL injections - const actorIdNumber = parseInt(options.actorId.toString(), 10) - let localVideosReq = '' - if (options.includeLocalVideos === true) { - localVideosReq = ' UNION ALL ' + - 'SELECT "video"."id" AS "id" FROM "video" ' + - 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + - 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + - 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + - 'WHERE "actor"."serverId" IS NULL' + const query: IFindOptions = { + where: { + id: { + [Sequelize.Op.any]: options.ids + } + }, + include: [ videoChannelInclude ] + } + + if (options.withFiles === true) { + query.include.push({ + model: VideoFileModel.unscoped(), + required: true + }) } - // FIXME: It would be more efficient to use a CTE so we join AFTER the filters, but sequelize does not support it... + return query + }, + [ScopeNames.AVAILABLE_FOR_LIST_IDS]: (options: AvailableForListIDsOptions) => { const query: IFindOptions = { + attributes: [ 'id' ], where: { id: { - [Sequelize.Op.notIn]: Sequelize.literal( - '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")' - ), - [ Sequelize.Op.in ]: Sequelize.literal( - '(' + - 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' + - 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' + - 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + - ' UNION ALL ' + - 'SELECT "video"."id" AS "id" FROM "video" ' + - 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + - 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + - 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + - 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + - 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + - localVideosReq + - ')' - ) + [Sequelize.Op.and]: [ + { + [ Sequelize.Op.notIn ]: Sequelize.literal( + '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")' + ) + } + ] }, // Always list public videos privacy: VideoPrivacy.PUBLIC, @@ -251,13 +246,87 @@ type AvailableForListOptions = { } ] }, - include: [ videoChannelInclude ] + include: [ ] } - if (options.withFiles === true) { - query.include.push({ - model: VideoFileModel.unscoped(), + if (options.filter || options.accountId || options.videoChannelId) { + const videoChannelInclude: IIncludeOptions = { + attributes: [], + model: VideoChannelModel.unscoped(), required: true + } + + if (options.videoChannelId) { + videoChannelInclude.where = { + id: options.videoChannelId + } + } + + if (options.filter || options.accountId) { + const accountInclude: IIncludeOptions = { + attributes: [], + model: AccountModel.unscoped(), + required: true + } + + if (options.filter) { + accountInclude.include = [ + { + attributes: [], + model: ActorModel.unscoped(), + required: true, + where: VideoModel.buildActorWhereWithFilter(options.filter) + } + ] + } + + if (options.accountId) { + accountInclude.where = { id: options.accountId } + } + + videoChannelInclude.include = [ accountInclude ] + } + + query.include.push(videoChannelInclude) + } + + if (options.actorId) { + let localVideosReq = '' + if (options.includeLocalVideos === true) { + localVideosReq = ' UNION ALL ' + + 'SELECT "video"."id" AS "id" FROM "video" ' + + 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + + 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + + 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + + 'WHERE "actor"."serverId" IS NULL' + } + + // Force actorId to be a number to avoid SQL injections + const actorIdNumber = parseInt(options.actorId.toString(), 10) + query.where['id'][Sequelize.Op.and].push({ + [ Sequelize.Op.in ]: Sequelize.literal( + '(' + + 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' + + 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' + + 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + + ' UNION ALL ' + + 'SELECT "video"."id" AS "id" FROM "video" ' + + 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + + 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' + + 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' + + 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' + + 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + + localVideosReq + + ')' + ) + }) + } + + if (options.withFiles === true) { + query.where['id'][Sequelize.Op.and].push({ + [ Sequelize.Op.in ]: Sequelize.literal( + '(SELECT "videoId" FROM "videoFile")' + ) }) } @@ -269,24 +338,28 @@ type AvailableForListOptions = { } if (options.tagsOneOf) { - query.where['id'][Sequelize.Op.in] = Sequelize.literal( - '(' + + query.where['id'][Sequelize.Op.and].push({ + [Sequelize.Op.in]: Sequelize.literal( + '(' + 'SELECT "videoId" FROM "videoTag" ' + 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' + - ')' - ) + ')' + ) + }) } if (options.tagsAllOf) { - query.where['id'][Sequelize.Op.in] = Sequelize.literal( + query.where['id'][Sequelize.Op.and].push({ + [Sequelize.Op.in]: Sequelize.literal( '(' + - 'SELECT "videoId" FROM "videoTag" ' + - 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + - 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' + - 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length + + 'SELECT "videoId" FROM "videoTag" ' + + 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + + 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' + + 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length + ')' - ) + ) + }) } } @@ -312,16 +385,19 @@ type AvailableForListOptions = { } } - if (options.accountId) { - accountInclude.where = { - id: options.accountId - } - } + if (options.trendingDays) { + query.include.push({ + attributes: [], + model: VideoViewModel, + required: false, + where: { + startDate: { + [ Sequelize.Op.gte ]: new Date(new Date().getTime() - (24 * 3600 * 1000) * options.trendingDays) + } + } + }) - if (options.videoChannelId) { - videoChannelInclude.where = { - id: options.videoChannelId - } + query.subQuery = false } return query @@ -589,6 +665,16 @@ export class VideoModel extends Model { }) VideoComments: VideoCommentModel[] + @HasMany(() => VideoViewModel, { + foreignKey: { + name: 'videoId', + allowNull: false + }, + onDelete: 'cascade', + hooks: true + }) + VideoViews: VideoViewModel[] + @HasOne(() => ScheduleVideoUpdateModel, { foreignKey: { name: 'videoId', @@ -694,7 +780,7 @@ export class VideoModel extends Model { distinct: true, offset: start, limit: count, - order: getSort('createdAt', [ 'Tags', 'name', 'ASC' ]), + order: getVideoSort('createdAt', [ 'Tags', 'name', 'ASC' ]), where: { id: { [Sequelize.Op.in]: Sequelize.literal('(' + rawQuery + ')') @@ -785,7 +871,7 @@ export class VideoModel extends Model { const query: IFindOptions = { offset: start, limit: count, - order: getSort(sort), + order: getVideoSort(sort), include: [ { model: VideoChannelModel, @@ -842,42 +928,41 @@ export class VideoModel extends Model { accountId?: number, videoChannelId?: number, actorId?: number + trendingDays?: number }) { - const query = { + const query: IFindOptions = { offset: options.start, limit: options.count, - order: getSort(options.sort) + order: getVideoSort(options.sort) } - const actorId = options.actorId || (await getServerActor()).id + let trendingDays: number + if (options.sort.endsWith('trending')) { + trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS - const scopes = { - method: [ - ScopeNames.AVAILABLE_FOR_LIST, { - actorId, - nsfw: options.nsfw, - categoryOneOf: options.categoryOneOf, - licenceOneOf: options.licenceOneOf, - languageOneOf: options.languageOneOf, - tagsOneOf: options.tagsOneOf, - tagsAllOf: options.tagsAllOf, - filter: options.filter, - withFiles: options.withFiles, - accountId: options.accountId, - videoChannelId: options.videoChannelId, - includeLocalVideos: options.includeLocalVideos - } as AvailableForListOptions - ] + query.group = 'VideoModel.id' } - return VideoModel.scope(scopes) - .findAndCountAll(query) - .then(({ rows, count }) => { - return { - data: rows, - total: count - } - }) + // actorId === null has a meaning, so just check undefined + const actorId = options.actorId !== undefined ? options.actorId : (await getServerActor()).id + + const queryOptions = { + actorId, + nsfw: options.nsfw, + categoryOneOf: options.categoryOneOf, + licenceOneOf: options.licenceOneOf, + languageOneOf: options.languageOneOf, + tagsOneOf: options.tagsOneOf, + tagsAllOf: options.tagsAllOf, + filter: options.filter, + withFiles: options.withFiles, + accountId: options.accountId, + videoChannelId: options.videoChannelId, + includeLocalVideos: options.includeLocalVideos, + trendingDays + } + + return VideoModel.getAvailableForApi(query, queryOptions) } static async searchAndPopulateAccountAndServer (options: { @@ -926,7 +1011,8 @@ export class VideoModel extends Model { id: { [ Sequelize.Op.in ]: Sequelize.literal( '(' + - 'SELECT "video"."id" FROM "video" WHERE ' + + 'SELECT "video"."id" FROM "video" ' + + 'WHERE ' + 'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + 'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' + 'UNION ALL ' + @@ -955,36 +1041,25 @@ export class VideoModel extends Model { }, offset: options.start, limit: options.count, - order: getSort(options.sort), + order: getVideoSort(options.sort), where: { [ Sequelize.Op.and ]: whereAnd } } const serverActor = await getServerActor() - const scopes = { - method: [ - ScopeNames.AVAILABLE_FOR_LIST, { - actorId: serverActor.id, - includeLocalVideos: options.includeLocalVideos, - nsfw: options.nsfw, - categoryOneOf: options.categoryOneOf, - licenceOneOf: options.licenceOneOf, - languageOneOf: options.languageOneOf, - tagsOneOf: options.tagsOneOf, - tagsAllOf: options.tagsAllOf - } as AvailableForListOptions - ] + const queryOptions = { + actorId: serverActor.id, + includeLocalVideos: options.includeLocalVideos, + nsfw: options.nsfw, + categoryOneOf: options.categoryOneOf, + licenceOneOf: options.licenceOneOf, + languageOneOf: options.languageOneOf, + tagsOneOf: options.tagsOneOf, + tagsAllOf: options.tagsAllOf } - return VideoModel.scope(scopes) - .findAndCountAll(query) - .then(({ rows, count }) => { - return { - data: rows, - total: count - } - }) + return VideoModel.getAvailableForApi(query, queryOptions) } static load (id: number, t?: Sequelize.Transaction) { @@ -1076,6 +1151,38 @@ export class VideoModel extends Model { } } + static incrementViews (id: number, views: number) { + return VideoModel.increment('views', { + by: views, + where: { + id + } + }) + } + + // threshold corresponds to how many video the field should have to be returned + static getRandomFieldSamples (field: 'category' | 'channelId', threshold: number, count: number) { + const query: IFindOptions = { + attributes: [ field ], + limit: count, + group: field, + having: Sequelize.where(Sequelize.fn('COUNT', Sequelize.col(field)), { + [Sequelize.Op.gte]: threshold + }) as any, // FIXME: typings + where: { + [field]: { + [Sequelize.Op.not]: null + }, + privacy: VideoPrivacy.PUBLIC, + state: VideoState.PUBLISHED + }, + order: [ this.sequelize.random() ] + } + + return VideoModel.findAll(query) + .then(rows => rows.map(r => r[field])) + } + private static buildActorWhereWithFilter (filter?: VideoFilter) { if (filter && filter === 'local') { return { @@ -1086,6 +1193,40 @@ export class VideoModel extends Model { return {} } + private static async getAvailableForApi (query: IFindOptions, options: AvailableForListIDsOptions) { + const idsScope = { + method: [ + ScopeNames.AVAILABLE_FOR_LIST_IDS, options + ] + } + + const { count, rows: rowsId } = await VideoModel.scope(idsScope).findAndCountAll(query) + const ids = rowsId.map(r => r.id) + + if (ids.length === 0) return { data: [], total: count } + + const apiScope = { + method: [ ScopeNames.FOR_API, { ids, withFiles: options.withFiles } as ForAPIOptions ] + } + + const secondQuery = { + offset: 0, + limit: query.limit, + attributes: query.attributes, + order: [ // Keep original order + Sequelize.literal( + ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ') + ) + ] + } + const rows = await VideoModel.scope(apiScope).findAll(secondQuery) + + return { + data: rows, + total: count + } + } + private static getCategoryLabel (id: number) { return VIDEO_CATEGORIES[id] || 'Misc' } @@ -1182,7 +1323,7 @@ export class VideoModel extends Model { const filePath = join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile)) logger.info('Creating torrent %s.', filePath) - await writeFilePromise(filePath, torrent) + await writeFile(filePath, torrent) const parsedTorrent = parseTorrent(torrent) videoFile.infoHash = parsedTorrent.infoHash @@ -1492,14 +1633,14 @@ export class VideoModel extends Model { await transcode(transcodeOptions) try { - await unlinkPromise(videoInputPath) + await remove(videoInputPath) // Important to do this before getVideoFilename() to take in account the new file extension inputVideoFile.set('extname', newExtname) const videoOutputPath = this.getVideoFilePath(inputVideoFile) - await renamePromise(videoTranscodedPath, videoOutputPath) - const stats = await statPromise(videoOutputPath) + await rename(videoTranscodedPath, videoOutputPath) + const stats = await stat(videoOutputPath) const fps = await getVideoFileFPS(videoOutputPath) inputVideoFile.set('size', stats.size) @@ -1540,7 +1681,7 @@ export class VideoModel extends Model { await transcode(transcodeOptions) - const stats = await statPromise(videoOutputPath) + const stats = await stat(videoOutputPath) const fps = await getVideoFileFPS(videoOutputPath) newVideoFile.set('size', stats.size) @@ -1555,7 +1696,7 @@ export class VideoModel extends Model { async importVideoFile (inputFilePath: string) { const { videoFileResolution } = await getVideoFileResolution(inputFilePath) - const { size } = await statPromise(inputFilePath) + const { size } = await stat(inputFilePath) const fps = await getVideoFileFPS(inputFilePath) let updatedVideoFile = new VideoFileModel({ @@ -1584,7 +1725,7 @@ export class VideoModel extends Model { } const outputPath = this.getVideoFilePath(updatedVideoFile) - await copyFilePromise(inputFilePath, outputPath) + await copy(inputFilePath, outputPath) await this.createTorrentAndSetInfoHash(updatedVideoFile) @@ -1605,25 +1746,25 @@ export class VideoModel extends Model { removeThumbnail () { const thumbnailPath = join(CONFIG.STORAGE.THUMBNAILS_DIR, this.getThumbnailName()) - return unlinkPromise(thumbnailPath) + return remove(thumbnailPath) .catch(err => logger.warn('Cannot delete thumbnail %s.', thumbnailPath, { err })) } removePreview () { const previewPath = join(CONFIG.STORAGE.PREVIEWS_DIR + this.getPreviewName()) - return unlinkPromise(previewPath) + return remove(previewPath) .catch(err => logger.warn('Cannot delete preview %s.', previewPath, { err })) } removeFile (videoFile: VideoFileModel) { const filePath = join(CONFIG.STORAGE.VIDEOS_DIR, this.getVideoFilename(videoFile)) - return unlinkPromise(filePath) + return remove(filePath) .catch(err => logger.warn('Cannot delete file %s.', filePath, { err })) } removeTorrent (videoFile: VideoFileModel) { const torrentPath = join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile)) - return unlinkPromise(torrentPath) + return remove(torrentPath) .catch(err => logger.warn('Cannot delete torrent %s.', torrentPath, { err })) }