server/models/video/video.ts

   1 import * as Bluebird from 'bluebird'
   2 import { maxBy } from 'lodash'
   3 import * as magnetUtil from 'magnet-uri'
   4 import * as parseTorrent from 'parse-torrent'
   5 import { join } from 'path'
   6 import {
   7   CountOptions,
   8   FindOptions,
   9   IncludeOptions,
  10   ModelIndexesOptions,
  11   Op,
  12   QueryTypes,
  13   ScopeOptions,
  14   Sequelize,
  15   Transaction,
  16   WhereOptions
  17 } from 'sequelize'
  18 import {
  19   AllowNull,
  20   BeforeDestroy,
  21   BelongsTo,
  22   BelongsToMany,
  23   Column,
  24   CreatedAt,
  25   DataType,
  26   Default,
  27   ForeignKey,
  28   HasMany,
  29   HasOne,
  30   Is,
  31   IsInt,
  32   IsUUID,
  33   Min,
  34   Model,
  35   Scopes,
  36   Table,
  37   UpdatedAt
  38 } from 'sequelize-typescript'
  39 import { UserRight, VideoPrivacy, VideoState } from '../../../shared'
  40 import { VideoTorrentObject } from '../../../shared/models/activitypub/objects'
  41 import { Video, VideoDetails, VideoFile } from '../../../shared/models/videos'
  42 import { VideoFilter } from '../../../shared/models/videos/video-query.type'
  43 import { peertubeTruncate } from '../../helpers/core-utils'
  44 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
  45 import { isArray, isBooleanValid } from '../../helpers/custom-validators/misc'
  46 import {
  47   isVideoCategoryValid,
  48   isVideoDescriptionValid,
  49   isVideoDurationValid,
  50   isVideoLanguageValid,
  51   isVideoLicenceValid,
  52   isVideoNameValid,
  53   isVideoPrivacyValid,
  54   isVideoStateValid,
  55   isVideoSupportValid
  56 } from '../../helpers/custom-validators/videos'
  57 import { getVideoFileResolution } from '../../helpers/ffmpeg-utils'
  58 import { logger } from '../../helpers/logger'
  59 import { getServerActor } from '../../helpers/utils'
  60 import {
  61   ACTIVITY_PUB,
  62   API_VERSION,
  63   CONSTRAINTS_FIELDS,
  64   HLS_REDUNDANCY_DIRECTORY,
  65   HLS_STREAMING_PLAYLIST_DIRECTORY,
  66   LAZY_STATIC_PATHS,
  67   REMOTE_SCHEME,
  68   STATIC_DOWNLOAD_PATHS,
  69   STATIC_PATHS,
  70   VIDEO_CATEGORIES,
  71   VIDEO_LANGUAGES,
  72   VIDEO_LICENCES,
  73   VIDEO_PRIVACIES,
  74   VIDEO_STATES,
  75   WEBSERVER
  76 } from '../../initializers/constants'
  77 import { sendDeleteVideo } from '../../lib/activitypub/send'
  78 import { AccountModel } from '../account/account'
  79 import { AccountVideoRateModel } from '../account/account-video-rate'
  80 import { ActorModel } from '../activitypub/actor'
  81 import { AvatarModel } from '../avatar/avatar'
  82 import { ServerModel } from '../server/server'
  83 import {
  84   buildBlockedAccountSQL,
  85   buildTrigramSearchIndex,
  86   buildWhereIdOrUUID,
  87   createSafeIn,
  88   createSimilarityAttribute,
  89   getVideoSort,
  90   isOutdated,
  91   throwIfNotValid
  92 } from '../utils'
  93 import { TagModel } from './tag'
  94 import { VideoAbuseModel } from './video-abuse'
  95 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel'
  96 import { VideoCommentModel } from './video-comment'
  97 import { VideoFileModel } from './video-file'
  98 import { VideoShareModel } from './video-share'
  99 import { VideoTagModel } from './video-tag'
 100 import { ScheduleVideoUpdateModel } from './schedule-video-update'
 101 import { VideoCaptionModel } from './video-caption'
 102 import { VideoBlacklistModel } from './video-blacklist'
 103 import { remove, writeFile } from 'fs-extra'
 104 import { VideoViewModel } from './video-views'
 105 import { VideoRedundancyModel } from '../redundancy/video-redundancy'
 106 import {
 107   videoFilesModelToFormattedJSON,
 108   VideoFormattingJSONOptions,
 109   videoModelToActivityPubObject,
 110   videoModelToFormattedDetailsJSON,
 111   videoModelToFormattedJSON
 112 } from './video-format-utils'
 113 import { UserVideoHistoryModel } from '../account/user-video-history'
 114 import { VideoImportModel } from './video-import'
 115 import { VideoStreamingPlaylistModel } from './video-streaming-playlist'
 116 import { VideoPlaylistElementModel } from './video-playlist-element'
 117 import { CONFIG } from '../../initializers/config'
 118 import { ThumbnailModel } from './thumbnail'
 119 import { ThumbnailType } from '../../../shared/models/videos/thumbnail.type'
 120 import { createTorrentPromise } from '../../helpers/webtorrent'
 121 import { VideoStreamingPlaylistType } from '../../../shared/models/videos/video-streaming-playlist.type'
 122 import {
 123   MChannel,
 124   MChannelAccountDefault,
 125   MChannelId,
 126   MUserAccountId,
 127   MUserId,
 128   MVideoAccountLight,
 129   MVideoAccountLightBlacklistAllFiles,
 130   MVideoDetails,
 131   MVideoForUser,
 132   MVideoFullLight,
 133   MVideoIdThumbnail,
 134   MVideoThumbnail,
 135   MVideoWithAllFiles,
 136   MVideoWithRights
 137 } from '../../typings/models'
 138 import { MVideoFile, MVideoFileRedundanciesOpt } from '../../typings/models/video/video-file'
 139 import { MThumbnail } from '../../typings/models/video/thumbnail'
 140
 141 // FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation
 142 const indexes: (ModelIndexesOptions & { where?: WhereOptions })[] = [
 143   buildTrigramSearchIndex('video_name_trigram', 'name'),
 144
 145   { fields: [ 'createdAt' ] },
 146   { fields: [ 'publishedAt' ] },
 147   { fields: [ 'duration' ] },
 148   { fields: [ 'views' ] },
 149   { fields: [ 'channelId' ] },
 150   {
 151     fields: [ 'originallyPublishedAt' ],
 152     where: {
 153       originallyPublishedAt: {
 154         [Op.ne]: null
 155       }
 156     }
 157   },
 158   {
 159     fields: [ 'category' ], // We don't care videos with an unknown category
 160     where: {
 161       category: {
 162         [Op.ne]: null
 163       }
 164     }
 165   },
 166   {
 167     fields: [ 'licence' ], // We don't care videos with an unknown licence
 168     where: {
 169       licence: {
 170         [Op.ne]: null
 171       }
 172     }
 173   },
 174   {
 175     fields: [ 'language' ], // We don't care videos with an unknown language
 176     where: {
 177       language: {
 178         [Op.ne]: null
 179       }
 180     }
 181   },
 182   {
 183     fields: [ 'nsfw' ], // Most of the videos are not NSFW
 184     where: {
 185       nsfw: true
 186     }
 187   },
 188   {
 189     fields: [ 'remote' ], // Only index local videos
 190     where: {
 191       remote: false
 192     }
 193   },
 194   {
 195     fields: [ 'uuid' ],
 196     unique: true
 197   },
 198   {
 199     fields: [ 'url' ],
 200     unique: true
 201   }
 202 ]
 203
 204 export enum ScopeNames {
 205   AVAILABLE_FOR_LIST_IDS = 'AVAILABLE_FOR_LIST_IDS',
 206   FOR_API = 'FOR_API',
 207   WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS',
 208   WITH_TAGS = 'WITH_TAGS',
 209   WITH_FILES = 'WITH_FILES',
 210   WITH_SCHEDULED_UPDATE = 'WITH_SCHEDULED_UPDATE',
 211   WITH_BLACKLISTED = 'WITH_BLACKLISTED',
 212   WITH_BLOCKLIST = 'WITH_BLOCKLIST',
 213   WITH_USER_HISTORY = 'WITH_USER_HISTORY',
 214   WITH_STREAMING_PLAYLISTS = 'WITH_STREAMING_PLAYLISTS',
 215   WITH_USER_ID = 'WITH_USER_ID',
 216   WITH_THUMBNAILS = 'WITH_THUMBNAILS'
 217 }
 218
 219 export type ForAPIOptions = {
 220   ids?: number[]
 221
 222   videoPlaylistId?: number
 223
 224   withFiles?: boolean
 225
 226   withAccountBlockerIds?: number[]
 227 }
 228
 229 export type AvailableForListIDsOptions = {
 230   serverAccountId: number
 231   followerActorId: number
 232   includeLocalVideos: boolean
 233
 234   attributesType?: 'none' | 'id' | 'all'
 235
 236   filter?: VideoFilter
 237   categoryOneOf?: number[]
 238   nsfw?: boolean
 239   licenceOneOf?: number[]
 240   languageOneOf?: string[]
 241   tagsOneOf?: string[]
 242   tagsAllOf?: string[]
 243
 244   withFiles?: boolean
 245
 246   accountId?: number
 247   videoChannelId?: number
 248
 249   videoPlaylistId?: number
 250
 251   trendingDays?: number
 252   user?: MUserAccountId
 253   historyOfUser?: MUserId
 254
 255   baseWhere?: WhereOptions[]
 256 }
 257
 258 @Scopes(() => ({
 259   [ ScopeNames.FOR_API ]: (options: ForAPIOptions) => {
 260     const query: FindOptions = {
 261       include: [
 262         {
 263           model: VideoChannelModel.scope({
 264             method: [
 265               VideoChannelScopeNames.SUMMARY, {
 266                 withAccount: true,
 267                 withAccountBlockerIds: options.withAccountBlockerIds
 268               } as SummaryOptions
 269             ]
 270           }),
 271           required: true
 272         },
 273         {
 274           attributes: [ 'type', 'filename' ],
 275           model: ThumbnailModel,
 276           required: false
 277         }
 278       ]
 279     }
 280
 281     if (options.ids) {
 282       query.where = {
 283         id: {
 284           [ Op.in ]: options.ids // FIXME: sequelize ANY seems broken
 285         }
 286       }
 287     }
 288
 289     if (options.withFiles === true) {
 290       query.include.push({
 291         model: VideoFileModel.unscoped(),
 292         required: true
 293       })
 294     }
 295
 296     if (options.videoPlaylistId) {
 297       query.include.push({
 298         model: VideoPlaylistElementModel.unscoped(),
 299         required: true,
 300         where: {
 301           videoPlaylistId: options.videoPlaylistId
 302         }
 303       })
 304     }
 305
 306     return query
 307   },
 308   [ ScopeNames.AVAILABLE_FOR_LIST_IDS ]: (options: AvailableForListIDsOptions) => {
 309     const whereAnd = options.baseWhere ? options.baseWhere : []
 310
 311     const query: FindOptions = {
 312       raw: true,
 313       include: []
 314     }
 315
 316     const attributesType = options.attributesType || 'id'
 317
 318     if (attributesType === 'id') query.attributes = [ 'id' ]
 319     else if (attributesType === 'none') query.attributes = [ ]
 320
 321     whereAnd.push({
 322       id: {
 323         [ Op.notIn ]: Sequelize.literal(
 324           '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
 325         )
 326       }
 327     })
 328
 329     if (options.serverAccountId) {
 330       whereAnd.push({
 331         channelId: {
 332           [ Op.notIn ]: Sequelize.literal(
 333             '(' +
 334               'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
 335                 buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
 336               ')' +
 337             ')'
 338           )
 339         }
 340       })
 341     }
 342
 343     // Only list public/published videos
 344     if (!options.filter || options.filter !== 'all-local') {
 345       const privacyWhere = {
 346         // Always list public videos
 347         privacy: VideoPrivacy.PUBLIC,
 348         // Always list published videos, or videos that are being transcoded but on which we don't want to wait for transcoding
 349         [ Op.or ]: [
 350           {
 351             state: VideoState.PUBLISHED
 352           },
 353           {
 354             [ Op.and ]: {
 355               state: VideoState.TO_TRANSCODE,
 356               waitTranscoding: false
 357             }
 358           }
 359         ]
 360       }
 361
 362       whereAnd.push(privacyWhere)
 363     }
 364
 365     if (options.videoPlaylistId) {
 366       query.include.push({
 367         attributes: [],
 368         model: VideoPlaylistElementModel.unscoped(),
 369         required: true,
 370         where: {
 371           videoPlaylistId: options.videoPlaylistId
 372         }
 373       })
 374
 375       query.subQuery = false
 376     }
 377
 378     if (options.filter || options.accountId || options.videoChannelId) {
 379       const videoChannelInclude: IncludeOptions = {
 380         attributes: [],
 381         model: VideoChannelModel.unscoped(),
 382         required: true
 383       }
 384
 385       if (options.videoChannelId) {
 386         videoChannelInclude.where = {
 387           id: options.videoChannelId
 388         }
 389       }
 390
 391       if (options.filter || options.accountId) {
 392         const accountInclude: IncludeOptions = {
 393           attributes: [],
 394           model: AccountModel.unscoped(),
 395           required: true
 396         }
 397
 398         if (options.filter) {
 399           accountInclude.include = [
 400             {
 401               attributes: [],
 402               model: ActorModel.unscoped(),
 403               required: true,
 404               where: VideoModel.buildActorWhereWithFilter(options.filter)
 405             }
 406           ]
 407         }
 408
 409         if (options.accountId) {
 410           accountInclude.where = { id: options.accountId }
 411         }
 412
 413         videoChannelInclude.include = [ accountInclude ]
 414       }
 415
 416       query.include.push(videoChannelInclude)
 417     }
 418
 419     if (options.followerActorId) {
 420       let localVideosReq = ''
 421       if (options.includeLocalVideos === true) {
 422         localVideosReq = ' UNION ALL ' +
 423           'SELECT "video"."id" AS "id" FROM "video" ' +
 424           'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 425           'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
 426           'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
 427           'WHERE "actor"."serverId" IS NULL'
 428       }
 429
 430       // Force actorId to be a number to avoid SQL injections
 431       const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
 432       whereAnd.push({
 433         id: {
 434           [ Op.in ]: Sequelize.literal(
 435             '(' +
 436             'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
 437             'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
 438             'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 439             ' UNION ALL ' +
 440             'SELECT "video"."id" AS "id" FROM "video" ' +
 441             'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 442             'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
 443             'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
 444             'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
 445             'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 446             localVideosReq +
 447             ')'
 448           )
 449         }
 450       })
 451     }
 452
 453     if (options.withFiles === true) {
 454       whereAnd.push({
 455         id: {
 456           [ Op.in ]: Sequelize.literal(
 457             '(SELECT "videoId" FROM "videoFile")'
 458           )
 459         }
 460       })
 461     }
 462
 463     // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
 464     if (options.tagsAllOf || options.tagsOneOf) {
 465       if (options.tagsOneOf) {
 466         whereAnd.push({
 467           id: {
 468             [ Op.in ]: Sequelize.literal(
 469               '(' +
 470               'SELECT "videoId" FROM "videoTag" ' +
 471               'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
 472               'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsOneOf) + ')' +
 473               ')'
 474             )
 475           }
 476         })
 477       }
 478
 479       if (options.tagsAllOf) {
 480         whereAnd.push({
 481           id: {
 482             [ Op.in ]: Sequelize.literal(
 483               '(' +
 484               'SELECT "videoId" FROM "videoTag" ' +
 485               'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
 486               'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsAllOf) + ')' +
 487               'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
 488               ')'
 489             )
 490           }
 491         })
 492       }
 493     }
 494
 495     if (options.nsfw === true || options.nsfw === false) {
 496       whereAnd.push({ nsfw: options.nsfw })
 497     }
 498
 499     if (options.categoryOneOf) {
 500       whereAnd.push({
 501         category: {
 502           [ Op.or ]: options.categoryOneOf
 503         }
 504       })
 505     }
 506
 507     if (options.licenceOneOf) {
 508       whereAnd.push({
 509         licence: {
 510           [ Op.or ]: options.licenceOneOf
 511         }
 512       })
 513     }
 514
 515     if (options.languageOneOf) {
 516       let videoLanguages = options.languageOneOf
 517       if (options.languageOneOf.find(l => l === '_unknown')) {
 518         videoLanguages = videoLanguages.concat([ null ])
 519       }
 520
 521       whereAnd.push({
 522         [Op.or]: [
 523           {
 524             language: {
 525               [ Op.or ]: videoLanguages
 526             }
 527           },
 528           {
 529             id: {
 530               [ Op.in ]: Sequelize.literal(
 531                 '(' +
 532                 'SELECT "videoId" FROM "videoCaption" ' +
 533                 'WHERE "language" IN (' + createSafeIn(VideoModel, options.languageOneOf) + ') ' +
 534                 ')'
 535               )
 536             }
 537           }
 538         ]
 539       })
 540     }
 541
 542     if (options.trendingDays) {
 543       query.include.push(VideoModel.buildTrendingQuery(options.trendingDays))
 544
 545       query.subQuery = false
 546     }
 547
 548     if (options.historyOfUser) {
 549       query.include.push({
 550         model: UserVideoHistoryModel,
 551         required: true,
 552         where: {
 553           userId: options.historyOfUser.id
 554         }
 555       })
 556
 557       // Even if the relation is n:m, we know that a user only have 0..1 video history
 558       // So we won't have multiple rows for the same video
 559       // Without this, we would not be able to sort on "updatedAt" column of UserVideoHistoryModel
 560       query.subQuery = false
 561     }
 562
 563     query.where = {
 564       [ Op.and ]: whereAnd
 565     }
 566
 567     return query
 568   },
 569   [ScopeNames.WITH_BLOCKLIST]: {
 570
 571   },
 572   [ ScopeNames.WITH_THUMBNAILS ]: {
 573     include: [
 574       {
 575         model: ThumbnailModel,
 576         required: false
 577       }
 578     ]
 579   },
 580   [ ScopeNames.WITH_USER_ID ]: {
 581     include: [
 582       {
 583         attributes: [ 'accountId' ],
 584         model: VideoChannelModel.unscoped(),
 585         required: true,
 586         include: [
 587           {
 588             attributes: [ 'userId' ],
 589             model: AccountModel.unscoped(),
 590             required: true
 591           }
 592         ]
 593       }
 594     ]
 595   },
 596   [ ScopeNames.WITH_ACCOUNT_DETAILS ]: {
 597     include: [
 598       {
 599         model: VideoChannelModel.unscoped(),
 600         required: true,
 601         include: [
 602           {
 603             attributes: {
 604               exclude: [ 'privateKey', 'publicKey' ]
 605             },
 606             model: ActorModel.unscoped(),
 607             required: true,
 608             include: [
 609               {
 610                 attributes: [ 'host' ],
 611                 model: ServerModel.unscoped(),
 612                 required: false
 613               },
 614               {
 615                 model: AvatarModel.unscoped(),
 616                 required: false
 617               }
 618             ]
 619           },
 620           {
 621             model: AccountModel.unscoped(),
 622             required: true,
 623             include: [
 624               {
 625                 model: ActorModel.unscoped(),
 626                 attributes: {
 627                   exclude: [ 'privateKey', 'publicKey' ]
 628                 },
 629                 required: true,
 630                 include: [
 631                   {
 632                     attributes: [ 'host' ],
 633                     model: ServerModel.unscoped(),
 634                     required: false
 635                   },
 636                   {
 637                     model: AvatarModel.unscoped(),
 638                     required: false
 639                   }
 640                 ]
 641               }
 642             ]
 643           }
 644         ]
 645       }
 646     ]
 647   },
 648   [ ScopeNames.WITH_TAGS ]: {
 649     include: [ TagModel ]
 650   },
 651   [ ScopeNames.WITH_BLACKLISTED ]: {
 652     include: [
 653       {
 654         attributes: [ 'id', 'reason', 'unfederated' ],
 655         model: VideoBlacklistModel,
 656         required: false
 657       }
 658     ]
 659   },
 660   [ ScopeNames.WITH_FILES ]: (withRedundancies = false) => {
 661     let subInclude: any[] = []
 662
 663     if (withRedundancies === true) {
 664       subInclude = [
 665         {
 666           attributes: [ 'fileUrl' ],
 667           model: VideoRedundancyModel.unscoped(),
 668           required: false
 669         }
 670       ]
 671     }
 672
 673     return {
 674       include: [
 675         {
 676           model: VideoFileModel.unscoped(),
 677           separate: true, // We may have multiple files, having multiple redundancies so let's separate this join
 678           required: false,
 679           include: subInclude
 680         }
 681       ]
 682     }
 683   },
 684   [ ScopeNames.WITH_STREAMING_PLAYLISTS ]: (withRedundancies = false) => {
 685     let subInclude: any[] = []
 686
 687     if (withRedundancies === true) {
 688       subInclude = [
 689         {
 690           attributes: [ 'fileUrl' ],
 691           model: VideoRedundancyModel.unscoped(),
 692           required: false
 693         }
 694       ]
 695     }
 696
 697     return {
 698       include: [
 699         {
 700           model: VideoStreamingPlaylistModel.unscoped(),
 701           separate: true, // We may have multiple streaming playlists, having multiple redundancies so let's separate this join
 702           required: false,
 703           include: subInclude
 704         }
 705       ]
 706     }
 707   },
 708   [ ScopeNames.WITH_SCHEDULED_UPDATE ]: {
 709     include: [
 710       {
 711         model: ScheduleVideoUpdateModel.unscoped(),
 712         required: false
 713       }
 714     ]
 715   },
 716   [ ScopeNames.WITH_USER_HISTORY ]: (userId: number) => {
 717     return {
 718       include: [
 719         {
 720           attributes: [ 'currentTime' ],
 721           model: UserVideoHistoryModel.unscoped(),
 722           required: false,
 723           where: {
 724             userId
 725           }
 726         }
 727       ]
 728     }
 729   }
 730 }))
 731 @Table({
 732   tableName: 'video',
 733   indexes
 734 })
 735 export class VideoModel extends Model<VideoModel> {
 736
 737   @AllowNull(false)
 738   @Default(DataType.UUIDV4)
 739   @IsUUID(4)
 740   @Column(DataType.UUID)
 741   uuid: string
 742
 743   @AllowNull(false)
 744   @Is('VideoName', value => throwIfNotValid(value, isVideoNameValid, 'name'))
 745   @Column
 746   name: string
 747
 748   @AllowNull(true)
 749   @Default(null)
 750   @Is('VideoCategory', value => throwIfNotValid(value, isVideoCategoryValid, 'category', true))
 751   @Column
 752   category: number
 753
 754   @AllowNull(true)
 755   @Default(null)
 756   @Is('VideoLicence', value => throwIfNotValid(value, isVideoLicenceValid, 'licence', true))
 757   @Column
 758   licence: number
 759
 760   @AllowNull(true)
 761   @Default(null)
 762   @Is('VideoLanguage', value => throwIfNotValid(value, isVideoLanguageValid, 'language', true))
 763   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.LANGUAGE.max))
 764   language: string
 765
 766   @AllowNull(false)
 767   @Is('VideoPrivacy', value => throwIfNotValid(value, isVideoPrivacyValid, 'privacy'))
 768   @Column
 769   privacy: number
 770
 771   @AllowNull(false)
 772   @Is('VideoNSFW', value => throwIfNotValid(value, isBooleanValid, 'NSFW boolean'))
 773   @Column
 774   nsfw: boolean
 775
 776   @AllowNull(true)
 777   @Default(null)
 778   @Is('VideoDescription', value => throwIfNotValid(value, isVideoDescriptionValid, 'description', true))
 779   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.DESCRIPTION.max))
 780   description: string
 781
 782   @AllowNull(true)
 783   @Default(null)
 784   @Is('VideoSupport', value => throwIfNotValid(value, isVideoSupportValid, 'support', true))
 785   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.SUPPORT.max))
 786   support: string
 787
 788   @AllowNull(false)
 789   @Is('VideoDuration', value => throwIfNotValid(value, isVideoDurationValid, 'duration'))
 790   @Column
 791   duration: number
 792
 793   @AllowNull(false)
 794   @Default(0)
 795   @IsInt
 796   @Min(0)
 797   @Column
 798   views: number
 799
 800   @AllowNull(false)
 801   @Default(0)
 802   @IsInt
 803   @Min(0)
 804   @Column
 805   likes: number
 806
 807   @AllowNull(false)
 808   @Default(0)
 809   @IsInt
 810   @Min(0)
 811   @Column
 812   dislikes: number
 813
 814   @AllowNull(false)
 815   @Column
 816   remote: boolean
 817
 818   @AllowNull(false)
 819   @Is('VideoUrl', value => throwIfNotValid(value, isActivityPubUrlValid, 'url'))
 820   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.URL.max))
 821   url: string
 822
 823   @AllowNull(false)
 824   @Column
 825   commentsEnabled: boolean
 826
 827   @AllowNull(false)
 828   @Column
 829   downloadEnabled: boolean
 830
 831   @AllowNull(false)
 832   @Column
 833   waitTranscoding: boolean
 834
 835   @AllowNull(false)
 836   @Default(null)
 837   @Is('VideoState', value => throwIfNotValid(value, isVideoStateValid, 'state'))
 838   @Column
 839   state: VideoState
 840
 841   @CreatedAt
 842   createdAt: Date
 843
 844   @UpdatedAt
 845   updatedAt: Date
 846
 847   @AllowNull(false)
 848   @Default(DataType.NOW)
 849   @Column
 850   publishedAt: Date
 851
 852   @AllowNull(true)
 853   @Default(null)
 854   @Column
 855   originallyPublishedAt: Date
 856
 857   @ForeignKey(() => VideoChannelModel)
 858   @Column
 859   channelId: number
 860
 861   @BelongsTo(() => VideoChannelModel, {
 862     foreignKey: {
 863       allowNull: true
 864     },
 865     hooks: true
 866   })
 867   VideoChannel: VideoChannelModel
 868
 869   @BelongsToMany(() => TagModel, {
 870     foreignKey: 'videoId',
 871     through: () => VideoTagModel,
 872     onDelete: 'CASCADE'
 873   })
 874   Tags: TagModel[]
 875
 876   @HasMany(() => ThumbnailModel, {
 877     foreignKey: {
 878       name: 'videoId',
 879       allowNull: true
 880     },
 881     hooks: true,
 882     onDelete: 'cascade'
 883   })
 884   Thumbnails: ThumbnailModel[]
 885
 886   @HasMany(() => VideoPlaylistElementModel, {
 887     foreignKey: {
 888       name: 'videoId',
 889       allowNull: true
 890     },
 891     onDelete: 'set null'
 892   })
 893   VideoPlaylistElements: VideoPlaylistElementModel[]
 894
 895   @HasMany(() => VideoAbuseModel, {
 896     foreignKey: {
 897       name: 'videoId',
 898       allowNull: false
 899     },
 900     onDelete: 'cascade'
 901   })
 902   VideoAbuses: VideoAbuseModel[]
 903
 904   @HasMany(() => VideoFileModel, {
 905     foreignKey: {
 906       name: 'videoId',
 907       allowNull: false
 908     },
 909     hooks: true,
 910     onDelete: 'cascade'
 911   })
 912   VideoFiles: VideoFileModel[]
 913
 914   @HasMany(() => VideoStreamingPlaylistModel, {
 915     foreignKey: {
 916       name: 'videoId',
 917       allowNull: false
 918     },
 919     hooks: true,
 920     onDelete: 'cascade'
 921   })
 922   VideoStreamingPlaylists: VideoStreamingPlaylistModel[]
 923
 924   @HasMany(() => VideoShareModel, {
 925     foreignKey: {
 926       name: 'videoId',
 927       allowNull: false
 928     },
 929     onDelete: 'cascade'
 930   })
 931   VideoShares: VideoShareModel[]
 932
 933   @HasMany(() => AccountVideoRateModel, {
 934     foreignKey: {
 935       name: 'videoId',
 936       allowNull: false
 937     },
 938     onDelete: 'cascade'
 939   })
 940   AccountVideoRates: AccountVideoRateModel[]
 941
 942   @HasMany(() => VideoCommentModel, {
 943     foreignKey: {
 944       name: 'videoId',
 945       allowNull: false
 946     },
 947     onDelete: 'cascade',
 948     hooks: true
 949   })
 950   VideoComments: VideoCommentModel[]
 951
 952   @HasMany(() => VideoViewModel, {
 953     foreignKey: {
 954       name: 'videoId',
 955       allowNull: false
 956     },
 957     onDelete: 'cascade'
 958   })
 959   VideoViews: VideoViewModel[]
 960
 961   @HasMany(() => UserVideoHistoryModel, {
 962     foreignKey: {
 963       name: 'videoId',
 964       allowNull: false
 965     },
 966     onDelete: 'cascade'
 967   })
 968   UserVideoHistories: UserVideoHistoryModel[]
 969
 970   @HasOne(() => ScheduleVideoUpdateModel, {
 971     foreignKey: {
 972       name: 'videoId',
 973       allowNull: false
 974     },
 975     onDelete: 'cascade'
 976   })
 977   ScheduleVideoUpdate: ScheduleVideoUpdateModel
 978
 979   @HasOne(() => VideoBlacklistModel, {
 980     foreignKey: {
 981       name: 'videoId',
 982       allowNull: false
 983     },
 984     onDelete: 'cascade'
 985   })
 986   VideoBlacklist: VideoBlacklistModel
 987
 988   @HasOne(() => VideoImportModel, {
 989     foreignKey: {
 990       name: 'videoId',
 991       allowNull: true
 992     },
 993     onDelete: 'set null'
 994   })
 995   VideoImport: VideoImportModel
 996
 997   @HasMany(() => VideoCaptionModel, {
 998     foreignKey: {
 999       name: 'videoId',
1000       allowNull: false
1001     },
1002     onDelete: 'cascade',
1003     hooks: true,
1004     [ 'separate' as any ]: true
1005   })
1006   VideoCaptions: VideoCaptionModel[]
1007
1008   @BeforeDestroy
1009   static async sendDelete (instance: MVideoAccountLight, options) {
1010     if (instance.isOwned()) {
1011       if (!instance.VideoChannel) {
1012         instance.VideoChannel = await instance.$get('VideoChannel', {
1013           include: [
1014             ActorModel,
1015             AccountModel
1016           ],
1017           transaction: options.transaction
1018         }) as MChannelAccountDefault
1019       }
1020
1021       return sendDeleteVideo(instance, options.transaction)
1022     }
1023
1024     return undefined
1025   }
1026
1027   @BeforeDestroy
1028   static async removeFiles (instance: VideoModel) {
1029     const tasks: Promise<any>[] = []
1030
1031     logger.info('Removing files of video %s.', instance.url)
1032
1033     if (instance.isOwned()) {
1034       if (!Array.isArray(instance.VideoFiles)) {
1035         instance.VideoFiles = await instance.$get('VideoFiles') as VideoFileModel[]
1036       }
1037
1038       // Remove physical files and torrents
1039       instance.VideoFiles.forEach(file => {
1040         tasks.push(instance.removeFile(file))
1041         tasks.push(instance.removeTorrent(file))
1042       })
1043
1044       // Remove playlists file
1045       tasks.push(instance.removeStreamingPlaylist())
1046     }
1047
1048     // Do not wait video deletion because we could be in a transaction
1049     Promise.all(tasks)
1050            .catch(err => {
1051              logger.error('Some errors when removing files of video %s in before destroy hook.', instance.uuid, { err })
1052            })
1053
1054     return undefined
1055   }
1056
1057   static listLocal (): Bluebird<MVideoWithAllFiles[]> {
1058     const query = {
1059       where: {
1060         remote: false
1061       }
1062     }
1063
1064     return VideoModel.scope([
1065       ScopeNames.WITH_FILES,
1066       ScopeNames.WITH_STREAMING_PLAYLISTS,
1067       ScopeNames.WITH_THUMBNAILS
1068     ]).findAll(query)
1069   }
1070
1071   static listAllAndSharedByActorForOutbox (actorId: number, start: number, count: number) {
1072     function getRawQuery (select: string) {
1073       const queryVideo = 'SELECT ' + select + ' FROM "video" AS "Video" ' +
1074         'INNER JOIN "videoChannel" AS "VideoChannel" ON "VideoChannel"."id" = "Video"."channelId" ' +
1075         'INNER JOIN "account" AS "Account" ON "Account"."id" = "VideoChannel"."accountId" ' +
1076         'WHERE "Account"."actorId" = ' + actorId
1077       const queryVideoShare = 'SELECT ' + select + ' FROM "videoShare" AS "VideoShare" ' +
1078         'INNER JOIN "video" AS "Video" ON "Video"."id" = "VideoShare"."videoId" ' +
1079         'WHERE "VideoShare"."actorId" = ' + actorId
1080
1081       return `(${queryVideo}) UNION (${queryVideoShare})`
1082     }
1083
1084     const rawQuery = getRawQuery('"Video"."id"')
1085     const rawCountQuery = getRawQuery('COUNT("Video"."id") as "total"')
1086
1087     const query = {
1088       distinct: true,
1089       offset: start,
1090       limit: count,
1091       order: getVideoSort('createdAt', [ 'Tags', 'name', 'ASC' ] as any), // FIXME: sequelize typings
1092       where: {
1093         id: {
1094           [ Op.in ]: Sequelize.literal('(' + rawQuery + ')')
1095         },
1096         [ Op.or ]: [
1097           { privacy: VideoPrivacy.PUBLIC },
1098           { privacy: VideoPrivacy.UNLISTED }
1099         ]
1100       },
1101       include: [
1102         {
1103           attributes: [ 'language' ],
1104           model: VideoCaptionModel.unscoped(),
1105           required: false
1106         },
1107         {
1108           attributes: [ 'id', 'url' ],
1109           model: VideoShareModel.unscoped(),
1110           required: false,
1111           // We only want videos shared by this actor
1112           where: {
1113             [ Op.and ]: [
1114               {
1115                 id: {
1116                   [ Op.not ]: null
1117                 }
1118               },
1119               {
1120                 actorId
1121               }
1122             ]
1123           },
1124           include: [
1125             {
1126               attributes: [ 'id', 'url' ],
1127               model: ActorModel.unscoped()
1128             }
1129           ]
1130         },
1131         {
1132           model: VideoChannelModel.unscoped(),
1133           required: true,
1134           include: [
1135             {
1136               attributes: [ 'name' ],
1137               model: AccountModel.unscoped(),
1138               required: true,
1139               include: [
1140                 {
1141                   attributes: [ 'id', 'url', 'followersUrl' ],
1142                   model: ActorModel.unscoped(),
1143                   required: true
1144                 }
1145               ]
1146             },
1147             {
1148               attributes: [ 'id', 'url', 'followersUrl' ],
1149               model: ActorModel.unscoped(),
1150               required: true
1151             }
1152           ]
1153         },
1154         VideoFileModel,
1155         TagModel
1156       ]
1157     }
1158
1159     return Bluebird.all([
1160       VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findAll(query),
1161       VideoModel.sequelize.query<{ total: string }>(rawCountQuery, { type: QueryTypes.SELECT })
1162     ]).then(([ rows, totals ]) => {
1163       // totals: totalVideos + totalVideoShares
1164       let totalVideos = 0
1165       let totalVideoShares = 0
1166       if (totals[ 0 ]) totalVideos = parseInt(totals[ 0 ].total, 10)
1167       if (totals[ 1 ]) totalVideoShares = parseInt(totals[ 1 ].total, 10)
1168
1169       const total = totalVideos + totalVideoShares
1170       return {
1171         data: rows,
1172         total: total
1173       }
1174     })
1175   }
1176
1177   static listUserVideosForApi (accountId: number, start: number, count: number, sort: string) {
1178     function buildBaseQuery (): FindOptions {
1179       return {
1180         offset: start,
1181         limit: count,
1182         order: getVideoSort(sort),
1183         include: [
1184           {
1185             model: VideoChannelModel,
1186             required: true,
1187             include: [
1188               {
1189                 model: AccountModel,
1190                 where: {
1191                   id: accountId
1192                 },
1193                 required: true
1194               }
1195             ]
1196           }
1197         ]
1198       }
1199     }
1200
1201     const countQuery = buildBaseQuery()
1202     const findQuery = buildBaseQuery()
1203
1204     const findScopes = [
1205       ScopeNames.WITH_SCHEDULED_UPDATE,
1206       ScopeNames.WITH_BLACKLISTED,
1207       ScopeNames.WITH_THUMBNAILS
1208     ]
1209
1210     return Promise.all([
1211       VideoModel.count(countQuery),
1212       VideoModel.scope(findScopes).findAll<MVideoForUser>(findQuery)
1213     ]).then(([ count, rows ]) => {
1214       return {
1215         data: rows,
1216         total: count
1217       }
1218     })
1219   }
1220
1221   static async listForApi (options: {
1222     start: number,
1223     count: number,
1224     sort: string,
1225     nsfw: boolean,
1226     includeLocalVideos: boolean,
1227     withFiles: boolean,
1228     categoryOneOf?: number[],
1229     licenceOneOf?: number[],
1230     languageOneOf?: string[],
1231     tagsOneOf?: string[],
1232     tagsAllOf?: string[],
1233     filter?: VideoFilter,
1234     accountId?: number,
1235     videoChannelId?: number,
1236     followerActorId?: number
1237     videoPlaylistId?: number,
1238     trendingDays?: number,
1239     user?: MUserAccountId,
1240     historyOfUser?: MUserId
1241   }, countVideos = true) {
1242     if (options.filter && options.filter === 'all-local' && !options.user.hasRight(UserRight.SEE_ALL_VIDEOS)) {
1243       throw new Error('Try to filter all-local but no user has not the see all videos right')
1244     }
1245
1246     const query: FindOptions & { where?: null } = {
1247       offset: options.start,
1248       limit: options.count,
1249       order: getVideoSort(options.sort)
1250     }
1251
1252     let trendingDays: number
1253     if (options.sort.endsWith('trending')) {
1254       trendingDays = CONFIG.TRENDING.VIDEOS.INTERVAL_DAYS
1255
1256       query.group = 'VideoModel.id'
1257     }
1258
1259     const serverActor = await getServerActor()
1260
1261     // followerActorId === null has a meaning, so just check undefined
1262     const followerActorId = options.followerActorId !== undefined ? options.followerActorId : serverActor.id
1263
1264     const queryOptions = {
1265       followerActorId,
1266       serverAccountId: serverActor.Account.id,
1267       nsfw: options.nsfw,
1268       categoryOneOf: options.categoryOneOf,
1269       licenceOneOf: options.licenceOneOf,
1270       languageOneOf: options.languageOneOf,
1271       tagsOneOf: options.tagsOneOf,
1272       tagsAllOf: options.tagsAllOf,
1273       filter: options.filter,
1274       withFiles: options.withFiles,
1275       accountId: options.accountId,
1276       videoChannelId: options.videoChannelId,
1277       videoPlaylistId: options.videoPlaylistId,
1278       includeLocalVideos: options.includeLocalVideos,
1279       user: options.user,
1280       historyOfUser: options.historyOfUser,
1281       trendingDays
1282     }
1283
1284     return VideoModel.getAvailableForApi(query, queryOptions, countVideos)
1285   }
1286
1287   static async searchAndPopulateAccountAndServer (options: {
1288     includeLocalVideos: boolean
1289     search?: string
1290     start?: number
1291     count?: number
1292     sort?: string
1293     startDate?: string // ISO 8601
1294     endDate?: string // ISO 8601
1295     originallyPublishedStartDate?: string
1296     originallyPublishedEndDate?: string
1297     nsfw?: boolean
1298     categoryOneOf?: number[]
1299     licenceOneOf?: number[]
1300     languageOneOf?: string[]
1301     tagsOneOf?: string[]
1302     tagsAllOf?: string[]
1303     durationMin?: number // seconds
1304     durationMax?: number // seconds
1305     user?: MUserAccountId,
1306     filter?: VideoFilter
1307   }) {
1308     const whereAnd = []
1309
1310     if (options.startDate || options.endDate) {
1311       const publishedAtRange = {}
1312
1313       if (options.startDate) publishedAtRange[ Op.gte ] = options.startDate
1314       if (options.endDate) publishedAtRange[ Op.lte ] = options.endDate
1315
1316       whereAnd.push({ publishedAt: publishedAtRange })
1317     }
1318
1319     if (options.originallyPublishedStartDate || options.originallyPublishedEndDate) {
1320       const originallyPublishedAtRange = {}
1321
1322       if (options.originallyPublishedStartDate) originallyPublishedAtRange[ Op.gte ] = options.originallyPublishedStartDate
1323       if (options.originallyPublishedEndDate) originallyPublishedAtRange[ Op.lte ] = options.originallyPublishedEndDate
1324
1325       whereAnd.push({ originallyPublishedAt: originallyPublishedAtRange })
1326     }
1327
1328     if (options.durationMin || options.durationMax) {
1329       const durationRange = {}
1330
1331       if (options.durationMin) durationRange[ Op.gte ] = options.durationMin
1332       if (options.durationMax) durationRange[ Op.lte ] = options.durationMax
1333
1334       whereAnd.push({ duration: durationRange })
1335     }
1336
1337     const attributesInclude = []
1338     const escapedSearch = VideoModel.sequelize.escape(options.search)
1339     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
1340     if (options.search) {
1341       whereAnd.push(
1342         {
1343           id: {
1344             [ Op.in ]: Sequelize.literal(
1345               '(' +
1346               'SELECT "video"."id" FROM "video" ' +
1347               'WHERE ' +
1348               'lower(immutable_unaccent("video"."name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' +
1349               'lower(immutable_unaccent("video"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' +
1350               'UNION ALL ' +
1351               'SELECT "video"."id" FROM "video" LEFT JOIN "videoTag" ON "videoTag"."videoId" = "video"."id" ' +
1352               'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
1353               'WHERE "tag"."name" = ' + escapedSearch +
1354               ')'
1355             )
1356           }
1357         }
1358       )
1359
1360       attributesInclude.push(createSimilarityAttribute('VideoModel.name', options.search))
1361     }
1362
1363     // Cannot search on similarity if we don't have a search
1364     if (!options.search) {
1365       attributesInclude.push(
1366         Sequelize.literal('0 as similarity')
1367       )
1368     }
1369
1370     const query = {
1371       attributes: {
1372         include: attributesInclude
1373       },
1374       offset: options.start,
1375       limit: options.count,
1376       order: getVideoSort(options.sort)
1377     }
1378
1379     const serverActor = await getServerActor()
1380     const queryOptions = {
1381       followerActorId: serverActor.id,
1382       serverAccountId: serverActor.Account.id,
1383       includeLocalVideos: options.includeLocalVideos,
1384       nsfw: options.nsfw,
1385       categoryOneOf: options.categoryOneOf,
1386       licenceOneOf: options.licenceOneOf,
1387       languageOneOf: options.languageOneOf,
1388       tagsOneOf: options.tagsOneOf,
1389       tagsAllOf: options.tagsAllOf,
1390       user: options.user,
1391       filter: options.filter,
1392       baseWhere: whereAnd
1393     }
1394
1395     return VideoModel.getAvailableForApi(query, queryOptions)
1396   }
1397
1398   static load (id: number | string, t?: Transaction): Bluebird<MVideoThumbnail> {
1399     const where = buildWhereIdOrUUID(id)
1400     const options = {
1401       where,
1402       transaction: t
1403     }
1404
1405     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(options)
1406   }
1407
1408   static loadWithRights (id: number | string, t?: Transaction): Bluebird<MVideoWithRights> {
1409     const where = buildWhereIdOrUUID(id)
1410     const options = {
1411       where,
1412       transaction: t
1413     }
1414
1415     return VideoModel.scope([
1416       ScopeNames.WITH_BLACKLISTED,
1417       ScopeNames.WITH_USER_ID,
1418       ScopeNames.WITH_THUMBNAILS
1419     ]).findOne(options)
1420   }
1421
1422   static loadOnlyId (id: number | string, t?: Transaction): Bluebird<MVideoIdThumbnail> {
1423     const where = buildWhereIdOrUUID(id)
1424
1425     const options = {
1426       attributes: [ 'id' ],
1427       where,
1428       transaction: t
1429     }
1430
1431     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(options)
1432   }
1433
1434   static loadWithFiles (id: number | string, t?: Transaction, logging?: boolean): Bluebird<MVideoWithAllFiles> {
1435     const where = buildWhereIdOrUUID(id)
1436
1437     const query = {
1438       where,
1439       transaction: t,
1440       logging
1441     }
1442
1443     return VideoModel.scope([
1444       ScopeNames.WITH_FILES,
1445       ScopeNames.WITH_STREAMING_PLAYLISTS,
1446       ScopeNames.WITH_THUMBNAILS
1447     ]).findOne(query)
1448   }
1449
1450   static loadByUUID (uuid: string): Bluebird<MVideoThumbnail> {
1451     const options = {
1452       where: {
1453         uuid
1454       }
1455     }
1456
1457     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(options)
1458   }
1459
1460   static loadByUrl (url: string, transaction?: Transaction): Bluebird<MVideoThumbnail> {
1461     const query: FindOptions = {
1462       where: {
1463         url
1464       },
1465       transaction
1466     }
1467
1468     return VideoModel.scope(ScopeNames.WITH_THUMBNAILS).findOne(query)
1469   }
1470
1471   static loadByUrlAndPopulateAccount (url: string, transaction?: Transaction): Bluebird<MVideoAccountLightBlacklistAllFiles> {
1472     const query: FindOptions = {
1473       where: {
1474         url
1475       },
1476       transaction
1477     }
1478
1479     return VideoModel.scope([
1480       ScopeNames.WITH_ACCOUNT_DETAILS,
1481       ScopeNames.WITH_FILES,
1482       ScopeNames.WITH_STREAMING_PLAYLISTS,
1483       ScopeNames.WITH_THUMBNAILS,
1484       ScopeNames.WITH_BLACKLISTED
1485     ]).findOne(query)
1486   }
1487
1488   static loadAndPopulateAccountAndServerAndTags (id: number | string, t?: Transaction, userId?: number): Bluebird<MVideoFullLight> {
1489     const where = buildWhereIdOrUUID(id)
1490
1491     const options = {
1492       order: [ [ 'Tags', 'name', 'ASC' ] ] as any,
1493       where,
1494       transaction: t
1495     }
1496
1497     const scopes: (string | ScopeOptions)[] = [
1498       ScopeNames.WITH_TAGS,
1499       ScopeNames.WITH_BLACKLISTED,
1500       ScopeNames.WITH_ACCOUNT_DETAILS,
1501       ScopeNames.WITH_SCHEDULED_UPDATE,
1502       ScopeNames.WITH_FILES,
1503       ScopeNames.WITH_STREAMING_PLAYLISTS,
1504       ScopeNames.WITH_THUMBNAILS
1505     ]
1506
1507     if (userId) {
1508       scopes.push({ method: [ ScopeNames.WITH_USER_HISTORY, userId ] })
1509     }
1510
1511     return VideoModel
1512       .scope(scopes)
1513       .findOne(options)
1514   }
1515
1516   static loadForGetAPI (parameters: {
1517     id: number | string,
1518     t?: Transaction,
1519     userId?: number
1520   }): Bluebird<MVideoDetails> {
1521     const { id, t, userId } = parameters
1522     const where = buildWhereIdOrUUID(id)
1523
1524     const options = {
1525       order: [ [ 'Tags', 'name', 'ASC' ] ] as any, // FIXME: sequelize typings
1526       where,
1527       transaction: t
1528     }
1529
1530     const scopes: (string | ScopeOptions)[] = [
1531       ScopeNames.WITH_TAGS,
1532       ScopeNames.WITH_BLACKLISTED,
1533       ScopeNames.WITH_ACCOUNT_DETAILS,
1534       ScopeNames.WITH_SCHEDULED_UPDATE,
1535       ScopeNames.WITH_THUMBNAILS,
1536       { method: [ ScopeNames.WITH_FILES, true ] },
1537       { method: [ ScopeNames.WITH_STREAMING_PLAYLISTS, true ] }
1538     ]
1539
1540     if (userId) {
1541       scopes.push({ method: [ ScopeNames.WITH_USER_HISTORY, userId ] })
1542     }
1543
1544     return VideoModel
1545       .scope(scopes)
1546       .findOne(options)
1547   }
1548
1549   static async getStats () {
1550     const totalLocalVideos = await VideoModel.count({
1551       where: {
1552         remote: false
1553       }
1554     })
1555     const totalVideos = await VideoModel.count()
1556
1557     let totalLocalVideoViews = await VideoModel.sum('views', {
1558       where: {
1559         remote: false
1560       }
1561     })
1562     // Sequelize could return null...
1563     if (!totalLocalVideoViews) totalLocalVideoViews = 0
1564
1565     return {
1566       totalLocalVideos,
1567       totalLocalVideoViews,
1568       totalVideos
1569     }
1570   }
1571
1572   static incrementViews (id: number, views: number) {
1573     return VideoModel.increment('views', {
1574       by: views,
1575       where: {
1576         id
1577       }
1578     })
1579   }
1580
1581   static checkVideoHasInstanceFollow (videoId: number, followerActorId: number) {
1582     // Instances only share videos
1583     const query = 'SELECT 1 FROM "videoShare" ' +
1584     'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
1585     'WHERE "actorFollow"."actorId" = $followerActorId AND "videoShare"."videoId" = $videoId ' +
1586     'LIMIT 1'
1587
1588     const options = {
1589       type: QueryTypes.SELECT,
1590       bind: { followerActorId, videoId },
1591       raw: true
1592     }
1593
1594     return VideoModel.sequelize.query(query, options)
1595                      .then(results => results.length === 1)
1596   }
1597
1598   static bulkUpdateSupportField (videoChannel: MChannel, t: Transaction) {
1599     const options = {
1600       where: {
1601         channelId: videoChannel.id
1602       },
1603       transaction: t
1604     }
1605
1606     return VideoModel.update({ support: videoChannel.support }, options)
1607   }
1608
1609   static getAllIdsFromChannel (videoChannel: MChannelId): Bluebird<number[]> {
1610     const query = {
1611       attributes: [ 'id' ],
1612       where: {
1613         channelId: videoChannel.id
1614       }
1615     }
1616
1617     return VideoModel.findAll(query)
1618       .then(videos => videos.map(v => v.id))
1619   }
1620
1621   // threshold corresponds to how many video the field should have to be returned
1622   static async getRandomFieldSamples (field: 'category' | 'channelId', threshold: number, count: number) {
1623     const serverActor = await getServerActor()
1624     const followerActorId = serverActor.id
1625
1626     const scopeOptions: AvailableForListIDsOptions = {
1627       serverAccountId: serverActor.Account.id,
1628       followerActorId,
1629       includeLocalVideos: true,
1630       attributesType: 'none' // Don't break aggregation
1631     }
1632
1633     const query: FindOptions = {
1634       attributes: [ field ],
1635       limit: count,
1636       group: field,
1637       having: Sequelize.where(
1638         Sequelize.fn('COUNT', Sequelize.col(field)), { [ Op.gte ]: threshold }
1639       ),
1640       order: [ (this.sequelize as any).random() ]
1641     }
1642
1643     return VideoModel.scope({ method: [ ScopeNames.AVAILABLE_FOR_LIST_IDS, scopeOptions ] })
1644                      .findAll(query)
1645                      .then(rows => rows.map(r => r[ field ]))
1646   }
1647
1648   static buildTrendingQuery (trendingDays: number) {
1649     return {
1650       attributes: [],
1651       subQuery: false,
1652       model: VideoViewModel,
1653       required: false,
1654       where: {
1655         startDate: {
1656           [ Op.gte ]: new Date(new Date().getTime() - (24 * 3600 * 1000) * trendingDays)
1657         }
1658       }
1659     }
1660   }
1661
1662   private static buildActorWhereWithFilter (filter?: VideoFilter) {
1663     if (filter && (filter === 'local' || filter === 'all-local')) {
1664       return {
1665         serverId: null
1666       }
1667     }
1668
1669     return {}
1670   }
1671
1672   private static async getAvailableForApi (
1673     query: FindOptions & { where?: null }, // Forbid where field in query
1674     options: AvailableForListIDsOptions,
1675     countVideos = true
1676   ) {
1677     const idsScope: ScopeOptions = {
1678       method: [
1679         ScopeNames.AVAILABLE_FOR_LIST_IDS, options
1680       ]
1681     }
1682
1683     // Remove trending sort on count, because it uses a group by
1684     const countOptions = Object.assign({}, options, { trendingDays: undefined })
1685     const countQuery: CountOptions = Object.assign({}, query, { attributes: undefined, group: undefined })
1686     const countScope: ScopeOptions = {
1687       method: [
1688         ScopeNames.AVAILABLE_FOR_LIST_IDS, countOptions
1689       ]
1690     }
1691
1692     const [ count, ids ] = await Promise.all([
1693       countVideos
1694         ? VideoModel.scope(countScope).count(countQuery)
1695         : Promise.resolve<number>(undefined),
1696
1697       VideoModel.scope(idsScope)
1698                 .findAll(query)
1699                 .then(rows => rows.map(r => r.id))
1700     ])
1701
1702     if (ids.length === 0) return { data: [], total: count }
1703
1704     const secondQuery: FindOptions = {
1705       offset: 0,
1706       limit: query.limit,
1707       attributes: query.attributes,
1708       order: [ // Keep original order
1709         Sequelize.literal(
1710           ids.map(id => `"VideoModel".id = ${id} DESC`).join(', ')
1711         )
1712       ]
1713     }
1714
1715     const apiScope: (string | ScopeOptions)[] = []
1716
1717     if (options.user) {
1718       apiScope.push({ method: [ ScopeNames.WITH_USER_HISTORY, options.user.id ] })
1719     }
1720
1721     apiScope.push({
1722       method: [
1723         ScopeNames.FOR_API, {
1724           ids,
1725           withFiles: options.withFiles,
1726           videoPlaylistId: options.videoPlaylistId
1727         } as ForAPIOptions
1728       ]
1729     })
1730
1731     const rows = await VideoModel.scope(apiScope).findAll(secondQuery)
1732
1733     return {
1734       data: rows,
1735       total: count
1736     }
1737   }
1738
1739   static getCategoryLabel (id: number) {
1740     return VIDEO_CATEGORIES[ id ] || 'Misc'
1741   }
1742
1743   static getLicenceLabel (id: number) {
1744     return VIDEO_LICENCES[ id ] || 'Unknown'
1745   }
1746
1747   static getLanguageLabel (id: string) {
1748     return VIDEO_LANGUAGES[ id ] || 'Unknown'
1749   }
1750
1751   static getPrivacyLabel (id: number) {
1752     return VIDEO_PRIVACIES[ id ] || 'Unknown'
1753   }
1754
1755   static getStateLabel (id: number) {
1756     return VIDEO_STATES[ id ] || 'Unknown'
1757   }
1758
1759   isBlacklisted () {
1760     return !!this.VideoBlacklist
1761   }
1762
1763   isBlocked () {
1764     return (this.VideoChannel.Account.Actor.Server && this.VideoChannel.Account.Actor.Server.isBlocked()) ||
1765       this.VideoChannel.Account.isBlocked()
1766   }
1767
1768   getOriginalFile () {
1769     if (Array.isArray(this.VideoFiles) === false) return undefined
1770
1771     // The original file is the file that have the higher resolution
1772     return maxBy(this.VideoFiles, file => file.resolution)
1773   }
1774
1775   getFile (resolution: number) {
1776     if (Array.isArray(this.VideoFiles) === false) return undefined
1777
1778     return this.VideoFiles.find(f => f.resolution === resolution)
1779   }
1780
1781   async addAndSaveThumbnail (thumbnail: MThumbnail, transaction: Transaction) {
1782     thumbnail.videoId = this.id
1783
1784     const savedThumbnail = await thumbnail.save({ transaction })
1785
1786     if (Array.isArray(this.Thumbnails) === false) this.Thumbnails = []
1787
1788     // Already have this thumbnail, skip
1789     if (this.Thumbnails.find(t => t.id === savedThumbnail.id)) return
1790
1791     this.Thumbnails.push(savedThumbnail)
1792   }
1793
1794   getVideoFilename (videoFile: MVideoFile) {
1795     return this.uuid + '-' + videoFile.resolution + videoFile.extname
1796   }
1797
1798   generateThumbnailName () {
1799     return this.uuid + '.jpg'
1800   }
1801
1802   getMiniature () {
1803     if (Array.isArray(this.Thumbnails) === false) return undefined
1804
1805     return this.Thumbnails.find(t => t.type === ThumbnailType.MINIATURE)
1806   }
1807
1808   generatePreviewName () {
1809     return this.uuid + '.jpg'
1810   }
1811
1812   getPreview () {
1813     if (Array.isArray(this.Thumbnails) === false) return undefined
1814
1815     return this.Thumbnails.find(t => t.type === ThumbnailType.PREVIEW)
1816   }
1817
1818   getTorrentFileName (videoFile: MVideoFile) {
1819     const extension = '.torrent'
1820     return this.uuid + '-' + videoFile.resolution + extension
1821   }
1822
1823   isOwned () {
1824     return this.remote === false
1825   }
1826
1827   getTorrentFilePath (videoFile: MVideoFile) {
1828     return join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile))
1829   }
1830
1831   getVideoFilePath (videoFile: MVideoFile) {
1832     return join(CONFIG.STORAGE.VIDEOS_DIR, this.getVideoFilename(videoFile))
1833   }
1834
1835   async createTorrentAndSetInfoHash (videoFile: MVideoFile) {
1836     const options = {
1837       // Keep the extname, it's used by the client to stream the file inside a web browser
1838       name: `${this.name} ${videoFile.resolution}p${videoFile.extname}`,
1839       createdBy: 'PeerTube',
1840       announceList: [
1841         [ WEBSERVER.WS + '://' + WEBSERVER.HOSTNAME + ':' + WEBSERVER.PORT + '/tracker/socket' ],
1842         [ WEBSERVER.URL + '/tracker/announce' ]
1843       ],
1844       urlList: [ WEBSERVER.URL + STATIC_PATHS.WEBSEED + this.getVideoFilename(videoFile) ]
1845     }
1846
1847     const torrent = await createTorrentPromise(this.getVideoFilePath(videoFile), options)
1848
1849     const filePath = join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile))
1850     logger.info('Creating torrent %s.', filePath)
1851
1852     await writeFile(filePath, torrent)
1853
1854     const parsedTorrent = parseTorrent(torrent)
1855     videoFile.infoHash = parsedTorrent.infoHash
1856   }
1857
1858   getWatchStaticPath () {
1859     return '/videos/watch/' + this.uuid
1860   }
1861
1862   getEmbedStaticPath () {
1863     return '/videos/embed/' + this.uuid
1864   }
1865
1866   getMiniatureStaticPath () {
1867     const thumbnail = this.getMiniature()
1868     if (!thumbnail) return null
1869
1870     return join(STATIC_PATHS.THUMBNAILS, thumbnail.filename)
1871   }
1872
1873   getPreviewStaticPath () {
1874     const preview = this.getPreview()
1875     if (!preview) return null
1876
1877     // We use a local cache, so specify our cache endpoint instead of potential remote URL
1878     return join(LAZY_STATIC_PATHS.PREVIEWS, preview.filename)
1879   }
1880
1881   toFormattedJSON (options?: VideoFormattingJSONOptions): Video {
1882     return videoModelToFormattedJSON(this, options)
1883   }
1884
1885   toFormattedDetailsJSON (): VideoDetails {
1886     return videoModelToFormattedDetailsJSON(this)
1887   }
1888
1889   getFormattedVideoFilesJSON (): VideoFile[] {
1890     return videoFilesModelToFormattedJSON(this, this.VideoFiles)
1891   }
1892
1893   toActivityPubObject (): VideoTorrentObject {
1894     return videoModelToActivityPubObject(this)
1895   }
1896
1897   getTruncatedDescription () {
1898     if (!this.description) return null
1899
1900     const maxLength = CONSTRAINTS_FIELDS.VIDEOS.TRUNCATED_DESCRIPTION.max
1901     return peertubeTruncate(this.description, maxLength)
1902   }
1903
1904   getOriginalFileResolution () {
1905     const originalFilePath = this.getVideoFilePath(this.getOriginalFile())
1906
1907     return getVideoFileResolution(originalFilePath)
1908   }
1909
1910   getDescriptionAPIPath () {
1911     return `/api/${API_VERSION}/videos/${this.uuid}/description`
1912   }
1913
1914   getHLSPlaylist () {
1915     if (!this.VideoStreamingPlaylists) return undefined
1916
1917     return this.VideoStreamingPlaylists.find(p => p.type === VideoStreamingPlaylistType.HLS)
1918   }
1919
1920   removeFile (videoFile: MVideoFile, isRedundancy = false) {
1921     const baseDir = isRedundancy ? CONFIG.STORAGE.REDUNDANCY_DIR : CONFIG.STORAGE.VIDEOS_DIR
1922
1923     const filePath = join(baseDir, this.getVideoFilename(videoFile))
1924     return remove(filePath)
1925       .catch(err => logger.warn('Cannot delete file %s.', filePath, { err }))
1926   }
1927
1928   removeTorrent (videoFile: MVideoFile) {
1929     const torrentPath = join(CONFIG.STORAGE.TORRENTS_DIR, this.getTorrentFileName(videoFile))
1930     return remove(torrentPath)
1931       .catch(err => logger.warn('Cannot delete torrent %s.', torrentPath, { err }))
1932   }
1933
1934   removeStreamingPlaylist (isRedundancy = false) {
1935     const baseDir = isRedundancy ? HLS_REDUNDANCY_DIRECTORY : HLS_STREAMING_PLAYLIST_DIRECTORY
1936
1937     const filePath = join(baseDir, this.uuid)
1938     return remove(filePath)
1939       .catch(err => logger.warn('Cannot delete playlist directory %s.', filePath, { err }))
1940   }
1941
1942   isOutdated () {
1943     if (this.isOwned()) return false
1944
1945     return isOutdated(this, ACTIVITY_PUB.VIDEO_REFRESH_INTERVAL)
1946   }
1947
1948   setAsRefreshed () {
1949     this.changed('updatedAt', true)
1950
1951     return this.save()
1952   }
1953
1954   getBaseUrls () {
1955     let baseUrlHttp
1956     let baseUrlWs
1957
1958     if (this.isOwned()) {
1959       baseUrlHttp = WEBSERVER.URL
1960       baseUrlWs = WEBSERVER.WS + '://' + WEBSERVER.HOSTNAME + ':' + WEBSERVER.PORT
1961     } else {
1962       baseUrlHttp = REMOTE_SCHEME.HTTP + '://' + this.VideoChannel.Account.Actor.Server.host
1963       baseUrlWs = REMOTE_SCHEME.WS + '://' + this.VideoChannel.Account.Actor.Server.host
1964     }
1965
1966     return { baseUrlHttp, baseUrlWs }
1967   }
1968
1969   generateMagnetUri (videoFile: MVideoFileRedundanciesOpt, baseUrlHttp: string, baseUrlWs: string) {
1970     const xs = this.getTorrentUrl(videoFile, baseUrlHttp)
1971     const announce = this.getTrackerUrls(baseUrlHttp, baseUrlWs)
1972     let urlList = [ this.getVideoFileUrl(videoFile, baseUrlHttp) ]
1973
1974     const redundancies = videoFile.RedundancyVideos
1975     if (isArray(redundancies)) urlList = urlList.concat(redundancies.map(r => r.fileUrl))
1976
1977     const magnetHash = {
1978       xs,
1979       announce,
1980       urlList,
1981       infoHash: videoFile.infoHash,
1982       name: this.name
1983     }
1984
1985     return magnetUtil.encode(magnetHash)
1986   }
1987
1988   getTrackerUrls (baseUrlHttp: string, baseUrlWs: string) {
1989     return [ baseUrlWs + '/tracker/socket', baseUrlHttp + '/tracker/announce' ]
1990   }
1991
1992   getTorrentUrl (videoFile: MVideoFile, baseUrlHttp: string) {
1993     return baseUrlHttp + STATIC_PATHS.TORRENTS + this.getTorrentFileName(videoFile)
1994   }
1995
1996   getTorrentDownloadUrl (videoFile: MVideoFile, baseUrlHttp: string) {
1997     return baseUrlHttp + STATIC_DOWNLOAD_PATHS.TORRENTS + this.getTorrentFileName(videoFile)
1998   }
1999
2000   getVideoFileUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2001     return baseUrlHttp + STATIC_PATHS.WEBSEED + this.getVideoFilename(videoFile)
2002   }
2003
2004   getVideoRedundancyUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2005     return baseUrlHttp + STATIC_PATHS.REDUNDANCY + this.getVideoFilename(videoFile)
2006   }
2007
2008   getVideoFileDownloadUrl (videoFile: MVideoFile, baseUrlHttp: string) {
2009     return baseUrlHttp + STATIC_DOWNLOAD_PATHS.VIDEOS + this.getVideoFilename(videoFile)
2010   }
2011
2012   getBandwidthBits (videoFile: MVideoFile) {
2013     return Math.ceil((videoFile.size * 8) / this.duration)
2014   }
2015 }