aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/video.ts
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/video/video.ts')
-rw-r--r--server/models/video/video.ts465
1 files changed, 48 insertions, 417 deletions
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 2e518317d..217ca8e50 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -1,7 +1,7 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { maxBy, minBy } from 'lodash' 2import { maxBy, minBy } from 'lodash'
3import { join } from 'path' 3import { join } from 'path'
4import { CountOptions, FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' 4import { FindOptions, IncludeOptions, Op, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
5import { 5import {
6 AllowNull, 6 AllowNull,
7 BeforeDestroy, 7 BeforeDestroy,
@@ -65,16 +65,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate'
65import { ActorModel } from '../activitypub/actor' 65import { ActorModel } from '../activitypub/actor'
66import { AvatarModel } from '../avatar/avatar' 66import { AvatarModel } from '../avatar/avatar'
67import { ServerModel } from '../server/server' 67import { ServerModel } from '../server/server'
68import { 68import { buildTrigramSearchIndex, buildWhereIdOrUUID, getVideoSort, isOutdated, throwIfNotValid } from '../utils'
69 buildBlockedAccountSQL,
70 buildTrigramSearchIndex,
71 buildWhereIdOrUUID,
72 createSafeIn,
73 createSimilarityAttribute,
74 getVideoSort,
75 isOutdated,
76 throwIfNotValid
77} from '../utils'
78import { TagModel } from './tag' 69import { TagModel } from './tag'
79import { VideoAbuseModel } from './video-abuse' 70import { VideoAbuseModel } from './video-abuse'
80import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel' 71import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
@@ -120,7 +111,8 @@ import {
120 MVideoFormattableDetails, 111 MVideoFormattableDetails,
121 MVideoForUser, 112 MVideoForUser,
122 MVideoFullLight, 113 MVideoFullLight,
123 MVideoIdThumbnail, MVideoImmutable, 114 MVideoIdThumbnail,
115 MVideoImmutable,
124 MVideoThumbnail, 116 MVideoThumbnail,
125 MVideoThumbnailBlacklist, 117 MVideoThumbnailBlacklist,
126 MVideoWithAllFiles, 118 MVideoWithAllFiles,
@@ -131,8 +123,8 @@ import { MVideoFile, MVideoFileStreamingPlaylistVideo } from '../../typings/mode
131import { MThumbnail } from '../../typings/models/video/thumbnail' 123import { MThumbnail } from '../../typings/models/video/thumbnail'
132import { VideoFile } from '@shared/models/videos/video-file.model' 124import { VideoFile } from '@shared/models/videos/video-file.model'
133import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths' 125import { getHLSDirectory, getTorrentFileName, getTorrentFilePath, getVideoFilename, getVideoFilePath } from '@server/lib/video-paths'
134import validator from 'validator'
135import { ModelCache } from '@server/models/model-cache' 126import { ModelCache } from '@server/models/model-cache'
127import { buildListQuery, BuildVideosQueryOptions } from './video-query-builder'
136 128
137export enum ScopeNames { 129export enum ScopeNames {
138 AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS', 130 AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',
@@ -241,274 +233,6 @@ export type AvailableForListIDsOptions = {
241 233
242 return query 234 return query
243 }, 235 },
244 [ScopeNames.AVAILABLE_FOR_LIST_IDS]: (options: AvailableForListIDsOptions) => {
245 const whereAnd = options.baseWhere ? [].concat(options.baseWhere) : []
246
247 const query: FindOptions = {
248 raw: true,
249 include: []
250 }
251
252 const attributesType = options.attributesType || 'id'
253
254 if (attributesType === 'id') query.attributes = [ 'id' ]
255 else if (attributesType === 'none') query.attributes = []
256
257 whereAnd.push({
258 id: {
259 [Op.notIn]: Sequelize.literal(
260 '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
261 )
262 }
263 })
264
265 if (options.serverAccountId) {
266 whereAnd.push({
267 channelId: {
268 [Op.notIn]: Sequelize.literal(
269 '(' +
270 'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
271 buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
272 ')' +
273 ')'
274 )
275 }
276 })
277 }
278
279 // Only list public/published videos
280 if (!options.filter || options.filter !== 'all-local') {
281 const publishWhere = {
282 // Always list published videos, or videos that are being transcoded but on which we don't want to wait for transcoding
283 [Op.or]: [
284 {
285 state: VideoState.PUBLISHED
286 },
287 {
288 [Op.and]: {
289 state: VideoState.TO_TRANSCODE,
290 waitTranscoding: false
291 }
292 }
293 ]
294 }
295 whereAnd.push(publishWhere)
296
297 // List internal videos if the user is logged in
298 if (options.user) {
299 const privacyWhere = {
300 [Op.or]: [
301 {
302 privacy: VideoPrivacy.INTERNAL
303 },
304 {
305 privacy: VideoPrivacy.PUBLIC
306 }
307 ]
308 }
309
310 whereAnd.push(privacyWhere)
311 } else { // Or only public videos
312 const privacyWhere = { privacy: VideoPrivacy.PUBLIC }
313 whereAnd.push(privacyWhere)
314 }
315 }
316
317 if (options.videoPlaylistId) {
318 query.include.push({
319 attributes: [],
320 model: VideoPlaylistElementModel.unscoped(),
321 required: true,
322 where: {
323 videoPlaylistId: options.videoPlaylistId
324 }
325 })
326
327 query.subQuery = false
328 }
329
330 if (options.filter && (options.filter === 'local' || options.filter === 'all-local')) {
331 whereAnd.push({
332 remote: false
333 })
334 }
335
336 if (options.accountId || options.videoChannelId) {
337 const videoChannelInclude: IncludeOptions = {
338 attributes: [],
339 model: VideoChannelModel.unscoped(),
340 required: true
341 }
342
343 if (options.videoChannelId) {
344 videoChannelInclude.where = {
345 id: options.videoChannelId
346 }
347 }
348
349 if (options.accountId) {
350 const accountInclude: IncludeOptions = {
351 attributes: [],
352 model: AccountModel.unscoped(),
353 required: true
354 }
355
356 accountInclude.where = { id: options.accountId }
357 videoChannelInclude.include = [ accountInclude ]
358 }
359
360 query.include.push(videoChannelInclude)
361 }
362
363 if (options.followerActorId) {
364 let localVideosReq = ''
365 if (options.includeLocalVideos === true) {
366 localVideosReq = ' UNION ALL SELECT "video"."id" FROM "video" WHERE remote IS FALSE'
367 }
368
369 // Force actorId to be a number to avoid SQL injections
370 const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
371 whereAnd.push({
372 id: {
373 [Op.in]: Sequelize.literal(
374 '(' +
375 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
376 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
377 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
378 ' UNION ALL ' +
379 'SELECT "video"."id" AS "id" FROM "video" ' +
380 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
381 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
382 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
383 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
384 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
385 localVideosReq +
386 ')'
387 )
388 }
389 })
390 }
391
392 if (options.withFiles === true) {
393 whereAnd.push({
394 id: {
395 [Op.in]: Sequelize.literal(
396 '(SELECT "videoId" FROM "videoFile")'
397 )
398 }
399 })
400 }
401
402 // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
403 if (options.tagsAllOf || options.tagsOneOf) {
404 if (options.tagsOneOf) {
405 const tagsOneOfLower = options.tagsOneOf.map(t => t.toLowerCase())
406
407 whereAnd.push({
408 id: {
409 [Op.in]: Sequelize.literal(
410 '(' +
411 'SELECT "videoId" FROM "videoTag" ' +
412 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
413 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsOneOfLower) + ')' +
414 ')'
415 )
416 }
417 })
418 }
419
420 if (options.tagsAllOf) {
421 const tagsAllOfLower = options.tagsAllOf.map(t => t.toLowerCase())
422
423 whereAnd.push({
424 id: {
425 [Op.in]: Sequelize.literal(
426 '(' +
427 'SELECT "videoId" FROM "videoTag" ' +
428 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
429 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsAllOfLower) + ')' +
430 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
431 ')'
432 )
433 }
434 })
435 }
436 }
437
438 if (options.nsfw === true || options.nsfw === false) {
439 whereAnd.push({ nsfw: options.nsfw })
440 }
441
442 if (options.categoryOneOf) {
443 whereAnd.push({
444 category: {
445 [Op.or]: options.categoryOneOf
446 }
447 })
448 }
449
450 if (options.licenceOneOf) {
451 whereAnd.push({
452 licence: {
453 [Op.or]: options.licenceOneOf
454 }
455 })
456 }
457
458 if (options.languageOneOf) {
459 let videoLanguages = options.languageOneOf
460 if (options.languageOneOf.find(l => l === '_unknown')) {
461 videoLanguages = videoLanguages.concat([ null ])
462 }
463
464 whereAnd.push({
465 [Op.or]: [
466 {
467 language: {
468 [Op.or]: videoLanguages
469 }
470 },
471 {
472 id: {
473 [Op.in]: Sequelize.literal(
474 '(' +
475 'SELECT "videoId" FROM "videoCaption" ' +
476 'WHERE "language" IN (' + createSafeIn(VideoModel, options.languageOneOf) + ') ' +
477 ')'
478 )
479 }
480 }
481 ]
482 })
483 }
484
485 if (options.trendingDays) {
486 query.include.push(VideoModel.buildTrendingQuery(options.trendingDays))
487
488 query.subQuery = false
489 }
490
491 if (options.historyOfUser) {
492 query.include.push({
493 model: UserVideoHistoryModel,
494 required: true,
495 where: {
496 userId: options.historyOfUser.id
497 }
498 })
499
500 // Even if the relation is n:m, we know that a user only have 0..1 video history
501 // So we won't have multiple rows for the same video
502 // Without this, we would not be able to sort on "updatedAt" column of UserVideoHistoryModel
503 query.subQuery = false
504 }
505
506 query.where = {
507 [Op.and]: whereAnd
508 }
509
510 return query
511 },
512 [ScopeNames.WITH_THUMBNAILS]: { 236 [ScopeNames.WITH_THUMBNAILS]: {
513 include: [ 237 include: [
514 { 238 {
@@ -1281,25 +1005,21 @@ export class VideoModel extends Model<VideoModel> {
1281 throw new Error('Try to filter all-local but no user has not the see all videos right') 1005 throw new Error('Try to filter all-local but no user has not the see all videos right')
1282 } 1006 }
1283 1007
1284 const query: FindOptions & { where?: null } = { 1008 const trendingDays = options.sort.endsWith('trending')
1285 offset: options.start, 1009 ? CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
1286 limit: options.count, 1010 : undefined
1287 order: getVideoSort(options.sort)
1288 }
1289
1290 let trendingDays: number
1291 if (options.sort.endsWith('trending')) {
1292 trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
1293
1294 query.group = 'VideoModel.id'
1295 }
1296 1011
1297 const serverActor = await getServerActor() 1012 const serverActor = await getServerActor()
1298 1013
1299 // followerActorId === null has a meaning, so just check undefined 1014 // followerActorId === null has a meaning, so just check undefined
1300 const followerActorId = options.followerActorId !== undefined ? options.followerActorId : serverActor.id 1015 const followerActorId = options.followerActorId !== undefined
1016 ? options.followerActorId
1017 : serverActor.id
1301 1018
1302 const queryOptions = { 1019 const queryOptions = {
1020 start: options.start,
1021 count: options.count,
1022 sort: options.sort,
1303 followerActorId, 1023 followerActorId,
1304 serverAccountId: serverActor.Account.id, 1024 serverAccountId: serverActor.Account.id,
1305 nsfw: options.nsfw, 1025 nsfw: options.nsfw,
@@ -1319,7 +1039,7 @@ export class VideoModel extends Model<VideoModel> {
1319 trendingDays 1039 trendingDays
1320 } 1040 }
1321 1041
1322 return VideoModel.getAvailableForApi(query, queryOptions, options.countVideos) 1042 return VideoModel.getAvailableForApi(queryOptions, options.countVideos)
1323 } 1043 }
1324 1044
1325 static async searchAndPopulateAccountAndServer (options: { 1045 static async searchAndPopulateAccountAndServer (options: {
@@ -1343,88 +1063,6 @@ export class VideoModel extends Model<VideoModel> {
1343 user?: MUserAccountId 1063 user?: MUserAccountId
1344 filter?: VideoFilter 1064 filter?: VideoFilter
1345 }) { 1065 }) {
1346 const whereAnd = []
1347
1348 if (options.startDate || options.endDate) {
1349 const publishedAtRange = {}
1350
1351 if (options.startDate) publishedAtRange[Op.gte] = options.startDate
1352 if (options.endDate) publishedAtRange[Op.lte] = options.endDate
1353
1354 whereAnd.push({ publishedAt: publishedAtRange })
1355 }
1356
1357 if (options.originallyPublishedStartDate || options.originallyPublishedEndDate) {
1358 const originallyPublishedAtRange = {}
1359
1360 if (options.originallyPublishedStartDate) originallyPublishedAtRange[Op.gte] = options.originallyPublishedStartDate
1361 if (options.originallyPublishedEndDate) originallyPublishedAtRange[Op.lte] = options.originallyPublishedEndDate
1362
1363 whereAnd.push({ originallyPublishedAt: originallyPublishedAtRange })
1364 }
1365
1366 if (options.durationMin || options.durationMax) {
1367 const durationRange = {}
1368
1369 if (options.durationMin) durationRange[Op.gte] = options.durationMin
1370 if (options.durationMax) durationRange[Op.lte] = options.durationMax
1371
1372 whereAnd.push({ duration: durationRange })
1373 }
1374
1375 const attributesInclude = []
1376 const escapedSearch = VideoModel.sequelize.escape(options.search)
1377 const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
1378 if (options.search) {
1379 const trigramSearch = {
1380 id: {
1381 [Op.in]: Sequelize.literal(
1382 '(' +
1383 'SELECT "video"."id" FROM "video" ' +
1384 'WHERE ' +
1385 'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
1386 'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
1387 'UNION ALL ' +
1388 'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
1389 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
1390 'WHERE lower("tag"."name") = lower(' + escapedSearch + ')' +
1391 ')'
1392 )
1393 }
1394 }
1395
1396 if (validator.isUUID(options.search)) {
1397 whereAnd.push({
1398 [Op.or]: [
1399 trigramSearch,
1400 {
1401 uuid: options.search
1402 }
1403 ]
1404 })
1405 } else {
1406 whereAnd.push(trigramSearch)
1407 }
1408
1409 attributesInclude.push(createSimilarityAttribute('VideoModel.name', options.search))
1410 }
1411
1412 // Cannot search on similarity if we don't have a search
1413 if (!options.search) {
1414 attributesInclude.push(
1415 Sequelize.literal('0 as similarity')
1416 )
1417 }
1418
1419 const query = {
1420 attributes: {
1421 include: attributesInclude
1422 },
1423 offset: options.start,
1424 limit: options.count,
1425 order: getVideoSort(options.sort)
1426 }
1427
1428 const serverActor = await getServerActor() 1066 const serverActor = await getServerActor()
1429 const queryOptions = { 1067 const queryOptions = {
1430 followerActorId: serverActor.id, 1068 followerActorId: serverActor.id,
@@ -1438,10 +1076,21 @@ export class VideoModel extends Model<VideoModel> {
1438 tagsAllOf: options.tagsAllOf, 1076 tagsAllOf: options.tagsAllOf,
1439 user: options.user, 1077 user: options.user,
1440 filter: options.filter, 1078 filter: options.filter,
1441 baseWhere: whereAnd 1079 start: options.start,
1080 count: options.count,
1081 sort: options.sort,
1082 startDate: options.startDate,
1083 endDate: options.endDate,
1084 originallyPublishedStartDate: options.originallyPublishedStartDate,
1085 originallyPublishedEndDate: options.originallyPublishedEndDate,
1086
1087 durationMin: options.durationMin,
1088 durationMax: options.durationMax,
1089
1090 search: options.search
1442 } 1091 }
1443 1092
1444 return VideoModel.getAvailableForApi(query, queryOptions) 1093 return VideoModel.getAvailableForApi(queryOptions)
1445 } 1094 }
1446 1095
1447 static load (id: number | string, t?: Transaction): Bluebird<MVideoThumbnail> { 1096 static load (id: number | string, t?: Transaction): Bluebird<MVideoThumbnail> {
@@ -1723,26 +1372,22 @@ export class VideoModel extends Model<VideoModel> {
1723 const serverActor = await getServerActor() 1372 const serverActor = await getServerActor()
1724 const followerActorId = serverActor.id 1373 const followerActorId = serverActor.id
1725 1374
1726 const scopeOptions: AvailableForListIDsOptions = { 1375 const queryOptions: BuildVideosQueryOptions = {
1376 attributes: [ `"${field}"` ],
1377 group: `GROUP BY "${field}"`,
1378 having: `HAVING COUNT("${field}") >= ${threshold}`,
1379 start: 0,
1380 sort: 'random',
1381 count,
1727 serverAccountId: serverActor.Account.id, 1382 serverAccountId: serverActor.Account.id,
1728 followerActorId, 1383 followerActorId,
1729 includeLocalVideos: true, 1384 includeLocalVideos: true
1730 attributesType: 'none' // Don't break aggregation
1731 } 1385 }
1732 1386
1733 const query: FindOptions = { 1387 const { query, replacements } = buildListQuery(VideoModel, queryOptions)
1734 attributes: [ field ],
1735 limit: count,
1736 group: field,
1737 having: Sequelize.where(
1738 Sequelize.fn('COUNT', Sequelize.col(field)), { [Op.gte]: threshold }
1739 ),
1740 order: [ (this.sequelize as any).random() ]
1741 }
1742 1388
1743 return VideoModel.scope({ method: [ ScopeNames.AVAILABLE_FOR_LIST_IDS, scopeOptions ] }) 1389 return this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT })
1744 .findAll(query) 1390 .then(rows => rows.map(r => r[field]))
1745 .then(rows => rows.map(r => r[field]))
1746 } 1391 }
1747 1392
1748 static buildTrendingQuery (trendingDays: number) { 1393 static buildTrendingQuery (trendingDays: number) {
@@ -1760,34 +1405,21 @@ export class VideoModel extends Model<VideoModel> {
1760 } 1405 }
1761 1406
1762 private static async getAvailableForApi ( 1407 private static async getAvailableForApi (
1763 query: FindOptions & { where?: null }, // Forbid where field in query 1408 options: BuildVideosQueryOptions,
1764 options: AvailableForListIDsOptions,
1765 countVideos = true 1409 countVideos = true
1766 ) { 1410 ) {
1767 const idsScope: ScopeOptions = { 1411 const { query, replacements } = buildListQuery(VideoModel, options)
1768 method: [ 1412 const { query: queryCount, replacements: replacementsCount } = buildListQuery(VideoModel, Object.assign({}, options, { isCount: true }))
1769 ScopeNames.AVAILABLE_FOR_LIST_IDS, options
1770 ]
1771 }
1772
1773 // Remove trending sort on count, because it uses a group by
1774 const countOptions = Object.assign({}, options, { trendingDays: undefined })
1775 const countQuery: CountOptions = Object.assign({}, query, { attributes: undefined, group: undefined })
1776 const countScope: ScopeOptions = {
1777 method: [
1778 ScopeNames.AVAILABLE_FOR_LIST_IDS, countOptions
1779 ]
1780 }
1781 1413
1782 const [ count, rows ] = await Promise.all([ 1414 const [ count, rows ] = await Promise.all([
1783 countVideos 1415 countVideos
1784 ? VideoModel.scope(countScope).count(countQuery) 1416 ? this.sequelize.query<any>(queryCount, { replacements: replacementsCount, type: QueryTypes.SELECT })
1417 .then(rows => rows.length !== 0 ? rows[0].total : 0)
1785 : Promise.resolve<number>(undefined), 1418 : Promise.resolve<number>(undefined),
1786 1419
1787 VideoModel.scope(idsScope) 1420 this.sequelize.query<any>(query, { replacements, type: QueryTypes.SELECT })
1788 .findAll(Object.assign({}, query, { raw: true }))
1789 .then(rows => rows.map(r => r.id)) 1421 .then(rows => rows.map(r => r.id))
1790 .then(ids => VideoModel.loadCompleteVideosForApi(ids, query, options)) 1422 .then(ids => VideoModel.loadCompleteVideosForApi(ids, options))
1791 ]) 1423 ])
1792 1424
1793 return { 1425 return {
@@ -1796,13 +1428,12 @@ export class VideoModel extends Model<VideoModel> {
1796 } 1428 }
1797 } 1429 }
1798 1430
1799 private static loadCompleteVideosForApi (ids: number[], query: FindOptions, options: AvailableForListIDsOptions) { 1431 private static loadCompleteVideosForApi (ids: number[], options: BuildVideosQueryOptions) {
1800 if (ids.length === 0) return [] 1432 if (ids.length === 0) return []
1801 1433
1802 const secondQuery: FindOptions = { 1434 const secondQuery: FindOptions = {
1803 offset: 0, 1435 offset: 0,
1804 limit: query.limit, 1436 limit: options.count,
1805 attributes: query.attributes,
1806 order: [ // Keep original order 1437 order: [ // Keep original order
1807 Sequelize.literal( 1438 Sequelize.literal(
1808 ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ') 1439 ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ')