server/models/video/video-channel.ts

   1 import { FindOptions, Includeable, literal, Op, ScopeOptions } from 'sequelize'
   2 import {
   3   AllowNull,
   4   BeforeDestroy,
   5   BelongsTo,
   6   Column,
   7   CreatedAt,
   8   DataType,
   9   Default,
  10   DefaultScope,
  11   ForeignKey,
  12   HasMany,
  13   Is,
  14   Model,
  15   Scopes,
  16   Sequelize,
  17   Table,
  18   UpdatedAt
  19 } from 'sequelize-typescript'
  20 import { MAccountActor } from '@server/types/models'
  21 import { ActivityPubActor } from '../../../shared/models/activitypub'
  22 import { VideoChannel, VideoChannelSummary } from '../../../shared/models/videos'
  23 import {
  24   isVideoChannelDescriptionValid,
  25   isVideoChannelNameValid,
  26   isVideoChannelSupportValid
  27 } from '../../helpers/custom-validators/video-channels'
  28 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
  29 import { sendDeleteActor } from '../../lib/activitypub/send'
  30 import {
  31   MChannelActor,
  32   MChannelAP,
  33   MChannelBannerAccountDefault,
  34   MChannelFormattable,
  35   MChannelSummaryFormattable
  36 } from '../../types/models/video'
  37 import { AccountModel, ScopeNames as AccountModelScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
  38 import { ActorImageModel } from '../account/actor-image'
  39 import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor'
  40 import { ActorFollowModel } from '../activitypub/actor-follow'
  41 import { ServerModel } from '../server/server'
  42 import { buildServerIdsFollowedBy, buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
  43 import { VideoModel } from './video'
  44 import { VideoPlaylistModel } from './video-playlist'
  45
  46 export enum ScopeNames {
  47   FOR_API = 'FOR_API',
  48   SUMMARY = 'SUMMARY',
  49   WITH_ACCOUNT = 'WITH_ACCOUNT',
  50   WITH_ACTOR = 'WITH_ACTOR',
  51   WITH_ACTOR_BANNER = 'WITH_ACTOR_BANNER',
  52   WITH_VIDEOS = 'WITH_VIDEOS',
  53   WITH_STATS = 'WITH_STATS'
  54 }
  55
  56 type AvailableForListOptions = {
  57   actorId: number
  58   search?: string
  59 }
  60
  61 type AvailableWithStatsOptions = {
  62   daysPrior: number
  63 }
  64
  65 export type SummaryOptions = {
  66   actorRequired?: boolean // Default: true
  67   withAccount?: boolean // Default: false
  68   withAccountBlockerIds?: number[]
  69 }
  70
  71 @DefaultScope(() => ({
  72   include: [
  73     {
  74       model: ActorModel,
  75       required: true
  76     }
  77   ]
  78 }))
  79 @Scopes(() => ({
  80   [ScopeNames.FOR_API]: (options: AvailableForListOptions) => {
  81     // Only list local channels OR channels that are on an instance followed by actorId
  82     const inQueryInstanceFollow = buildServerIdsFollowedBy(options.actorId)
  83
  84     return {
  85       include: [
  86         {
  87           attributes: {
  88             exclude: unusedActorAttributesForAPI
  89           },
  90           model: ActorModel,
  91           where: {
  92             [Op.or]: [
  93               {
  94                 serverId: null
  95               },
  96               {
  97                 serverId: {
  98                   [Op.in]: Sequelize.literal(inQueryInstanceFollow)
  99                 }
 100               }
 101             ]
 102           }
 103         },
 104         {
 105           model: AccountModel,
 106           required: true,
 107           include: [
 108             {
 109               attributes: {
 110                 exclude: unusedActorAttributesForAPI
 111               },
 112               model: ActorModel, // Default scope includes avatar and server
 113               required: true
 114             }
 115           ]
 116         }
 117       ]
 118     }
 119   },
 120   [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => {
 121     const include: Includeable[] = [
 122       {
 123         attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
 124         model: ActorModel.unscoped(),
 125         required: options.actorRequired ?? true,
 126         include: [
 127           {
 128             attributes: [ 'host' ],
 129             model: ServerModel.unscoped(),
 130             required: false
 131           },
 132           {
 133             model: ActorImageModel.unscoped(),
 134             as: 'Avatar',
 135             required: false
 136           }
 137         ]
 138       }
 139     ]
 140
 141     const base: FindOptions = {
 142       attributes: [ 'id', 'name', 'description', 'actorId' ]
 143     }
 144
 145     if (options.withAccount === true) {
 146       include.push({
 147         model: AccountModel.scope({
 148           method: [ AccountModelScopeNames.SUMMARY, { withAccountBlockerIds: options.withAccountBlockerIds } as AccountSummaryOptions ]
 149         }),
 150         required: true
 151       })
 152     }
 153
 154     base.include = include
 155
 156     return base
 157   },
 158   [ScopeNames.WITH_ACCOUNT]: {
 159     include: [
 160       {
 161         model: AccountModel,
 162         required: true
 163       }
 164     ]
 165   },
 166   [ScopeNames.WITH_ACTOR]: {
 167     include: [
 168       ActorModel
 169     ]
 170   },
 171   [ScopeNames.WITH_ACTOR_BANNER]: {
 172     include: [
 173       {
 174         model: ActorModel,
 175         include: [
 176           {
 177             model: ActorImageModel,
 178             required: false,
 179             as: 'Banner'
 180           }
 181         ]
 182       }
 183     ]
 184   },
 185   [ScopeNames.WITH_VIDEOS]: {
 186     include: [
 187       VideoModel
 188     ]
 189   },
 190   [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => {
 191     const daysPrior = parseInt(options.daysPrior + '', 10)
 192
 193     return {
 194       attributes: {
 195         include: [
 196           [
 197             literal('(SELECT COUNT(*) FROM "video" WHERE "channelId" = "VideoChannelModel"."id")'),
 198             'videosCount'
 199           ],
 200           [
 201             literal(
 202               '(' +
 203               `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
 204               'FROM ( ' +
 205                 'WITH ' +
 206                   'days AS ( ' +
 207                     `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
 208                           `date_trunc('day', now()), '1 day'::interval) AS day ` +
 209                   ') ' +
 210                   'SELECT days.day AS day, COALESCE(SUM("videoView".views), 0) AS views ' +
 211                   'FROM days ' +
 212                   'LEFT JOIN (' +
 213                     '"videoView" INNER JOIN "video" ON "videoView"."videoId" = "video"."id" ' +
 214                     'AND "video"."channelId" = "VideoChannelModel"."id"' +
 215                   `) ON date_trunc('day', "videoView"."startDate") = date_trunc('day', days.day) ` +
 216                   'GROUP BY day ' +
 217                   'ORDER BY day ' +
 218                 ') t' +
 219               ')'
 220             ),
 221             'viewsPerDay'
 222           ]
 223         ]
 224       }
 225     }
 226   }
 227 }))
 228 @Table({
 229   tableName: 'videoChannel',
 230   indexes: [
 231     buildTrigramSearchIndex('video_channel_name_trigram', 'name'),
 232
 233     {
 234       fields: [ 'accountId' ]
 235     },
 236     {
 237       fields: [ 'actorId' ]
 238     }
 239   ]
 240 })
 241 export class VideoChannelModel extends Model {
 242
 243   @AllowNull(false)
 244   @Is('VideoChannelName', value => throwIfNotValid(value, isVideoChannelNameValid, 'name'))
 245   @Column
 246   name: string
 247
 248   @AllowNull(true)
 249   @Default(null)
 250   @Is('VideoChannelDescription', value => throwIfNotValid(value, isVideoChannelDescriptionValid, 'description', true))
 251   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_CHANNELS.DESCRIPTION.max))
 252   description: string
 253
 254   @AllowNull(true)
 255   @Default(null)
 256   @Is('VideoChannelSupport', value => throwIfNotValid(value, isVideoChannelSupportValid, 'support', true))
 257   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_CHANNELS.SUPPORT.max))
 258   support: string
 259
 260   @CreatedAt
 261   createdAt: Date
 262
 263   @UpdatedAt
 264   updatedAt: Date
 265
 266   @ForeignKey(() => ActorModel)
 267   @Column
 268   actorId: number
 269
 270   @BelongsTo(() => ActorModel, {
 271     foreignKey: {
 272       allowNull: false
 273     },
 274     onDelete: 'cascade'
 275   })
 276   Actor: ActorModel
 277
 278   @ForeignKey(() => AccountModel)
 279   @Column
 280   accountId: number
 281
 282   @BelongsTo(() => AccountModel, {
 283     foreignKey: {
 284       allowNull: false
 285     },
 286     hooks: true
 287   })
 288   Account: AccountModel
 289
 290   @HasMany(() => VideoModel, {
 291     foreignKey: {
 292       name: 'channelId',
 293       allowNull: false
 294     },
 295     onDelete: 'CASCADE',
 296     hooks: true
 297   })
 298   Videos: VideoModel[]
 299
 300   @HasMany(() => VideoPlaylistModel, {
 301     foreignKey: {
 302       allowNull: true
 303     },
 304     onDelete: 'CASCADE',
 305     hooks: true
 306   })
 307   VideoPlaylists: VideoPlaylistModel[]
 308
 309   @BeforeDestroy
 310   static async sendDeleteIfOwned (instance: VideoChannelModel, options) {
 311     if (!instance.Actor) {
 312       instance.Actor = await instance.$get('Actor', { transaction: options.transaction })
 313     }
 314
 315     await ActorFollowModel.removeFollowsOf(instance.Actor.id, options.transaction)
 316
 317     if (instance.Actor.isOwned()) {
 318       return sendDeleteActor(instance.Actor, options.transaction)
 319     }
 320
 321     return undefined
 322   }
 323
 324   static countByAccount (accountId: number) {
 325     const query = {
 326       where: {
 327         accountId
 328       }
 329     }
 330
 331     return VideoChannelModel.count(query)
 332   }
 333
 334   static listForApi (parameters: {
 335     actorId: number
 336     start: number
 337     count: number
 338     sort: string
 339   }) {
 340     const { actorId } = parameters
 341
 342     const query = {
 343       offset: parameters.start,
 344       limit: parameters.count,
 345       order: getSort(parameters.sort)
 346     }
 347
 348     return VideoChannelModel
 349       .scope({
 350         method: [ ScopeNames.FOR_API, { actorId } as AvailableForListOptions ]
 351       })
 352       .findAndCountAll(query)
 353       .then(({ rows, count }) => {
 354         return { total: count, data: rows }
 355       })
 356   }
 357
 358   static listLocalsForSitemap (sort: string): Promise<MChannelActor[]> {
 359     const query = {
 360       attributes: [ ],
 361       offset: 0,
 362       order: getSort(sort),
 363       include: [
 364         {
 365           attributes: [ 'preferredUsername', 'serverId' ],
 366           model: ActorModel.unscoped(),
 367           where: {
 368             serverId: null
 369           }
 370         }
 371       ]
 372     }
 373
 374     return VideoChannelModel
 375       .unscoped()
 376       .findAll(query)
 377   }
 378
 379   static searchForApi (options: {
 380     actorId: number
 381     search: string
 382     start: number
 383     count: number
 384     sort: string
 385   }) {
 386     const attributesInclude = []
 387     const escapedSearch = VideoModel.sequelize.escape(options.search)
 388     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
 389     attributesInclude.push(createSimilarityAttribute('VideoChannelModel.name', options.search))
 390
 391     const query = {
 392       attributes: {
 393         include: attributesInclude
 394       },
 395       offset: options.start,
 396       limit: options.count,
 397       order: getSort(options.sort),
 398       where: {
 399         [Op.or]: [
 400           Sequelize.literal(
 401             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 402           ),
 403           Sequelize.literal(
 404             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 405           )
 406         ]
 407       }
 408     }
 409
 410     return VideoChannelModel
 411       .scope({
 412         method: [ ScopeNames.FOR_API, { actorId: options.actorId } as AvailableForListOptions ]
 413       })
 414       .findAndCountAll(query)
 415       .then(({ rows, count }) => {
 416         return { total: count, data: rows }
 417       })
 418   }
 419
 420   static listByAccount (options: {
 421     accountId: number
 422     start: number
 423     count: number
 424     sort: string
 425     withStats?: boolean
 426     search?: string
 427   }) {
 428     const escapedSearch = VideoModel.sequelize.escape(options.search)
 429     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
 430     const where = options.search
 431       ? {
 432         [Op.or]: [
 433           Sequelize.literal(
 434             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 435           ),
 436           Sequelize.literal(
 437             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 438           )
 439         ]
 440       }
 441       : null
 442
 443     const query = {
 444       offset: options.start,
 445       limit: options.count,
 446       order: getSort(options.sort),
 447       include: [
 448         {
 449           model: AccountModel,
 450           where: {
 451             id: options.accountId
 452           },
 453           required: true
 454         }
 455       ],
 456       where
 457     }
 458
 459     const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR_BANNER ]
 460
 461     if (options.withStats === true) {
 462       scopes.push({
 463         method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
 464       })
 465     }
 466
 467     return VideoChannelModel
 468       .scope(scopes)
 469       .findAndCountAll(query)
 470       .then(({ rows, count }) => {
 471         return { total: count, data: rows }
 472       })
 473   }
 474
 475   static loadAndPopulateAccount (id: number): Promise<MChannelBannerAccountDefault> {
 476     return VideoChannelModel.unscoped()
 477       .scope([ ScopeNames.WITH_ACTOR_BANNER, ScopeNames.WITH_ACCOUNT ])
 478       .findByPk(id)
 479   }
 480
 481   static loadByUrlAndPopulateAccount (url: string): Promise<MChannelBannerAccountDefault> {
 482     const query = {
 483       include: [
 484         {
 485           model: ActorModel,
 486           required: true,
 487           where: {
 488             url
 489           },
 490           include: [
 491             {
 492               model: ActorImageModel,
 493               required: false,
 494               as: 'Banner'
 495             }
 496           ]
 497         }
 498       ]
 499     }
 500
 501     return VideoChannelModel
 502       .scope([ ScopeNames.WITH_ACCOUNT ])
 503       .findOne(query)
 504   }
 505
 506   static loadByNameWithHostAndPopulateAccount (nameWithHost: string) {
 507     const [ name, host ] = nameWithHost.split('@')
 508
 509     if (!host || host === WEBSERVER.HOST) return VideoChannelModel.loadLocalByNameAndPopulateAccount(name)
 510
 511     return VideoChannelModel.loadByNameAndHostAndPopulateAccount(name, host)
 512   }
 513
 514   static loadLocalByNameAndPopulateAccount (name: string): Promise<MChannelBannerAccountDefault> {
 515     const query = {
 516       include: [
 517         {
 518           model: ActorModel,
 519           required: true,
 520           where: {
 521             preferredUsername: name,
 522             serverId: null
 523           },
 524           include: [
 525             {
 526               model: ActorImageModel,
 527               required: false,
 528               as: 'Banner'
 529             }
 530           ]
 531         }
 532       ]
 533     }
 534
 535     return VideoChannelModel.unscoped()
 536       .scope([ ScopeNames.WITH_ACCOUNT ])
 537       .findOne(query)
 538   }
 539
 540   static loadByNameAndHostAndPopulateAccount (name: string, host: string): Promise<MChannelBannerAccountDefault> {
 541     const query = {
 542       include: [
 543         {
 544           model: ActorModel,
 545           required: true,
 546           where: {
 547             preferredUsername: name
 548           },
 549           include: [
 550             {
 551               model: ServerModel,
 552               required: true,
 553               where: { host }
 554             },
 555             {
 556               model: ActorImageModel,
 557               required: false,
 558               as: 'Banner'
 559             }
 560           ]
 561         }
 562       ]
 563     }
 564
 565     return VideoChannelModel.unscoped()
 566       .scope([ ScopeNames.WITH_ACCOUNT ])
 567       .findOne(query)
 568   }
 569
 570   toFormattedSummaryJSON (this: MChannelSummaryFormattable): VideoChannelSummary {
 571     const actor = this.Actor.toFormattedSummaryJSON()
 572
 573     return {
 574       id: this.id,
 575       name: actor.name,
 576       displayName: this.getDisplayName(),
 577       url: actor.url,
 578       host: actor.host,
 579       avatar: actor.avatar
 580     }
 581   }
 582
 583   toFormattedJSON (this: MChannelFormattable): VideoChannel {
 584     const viewsPerDayString = this.get('viewsPerDay') as string
 585     const videosCount = this.get('videosCount') as number
 586
 587     let viewsPerDay: { date: Date, views: number }[]
 588
 589     if (viewsPerDayString) {
 590       viewsPerDay = viewsPerDayString.split(',')
 591         .map(v => {
 592           const [ dateString, amount ] = v.split('|')
 593
 594           return {
 595             date: new Date(dateString),
 596             views: +amount
 597           }
 598         })
 599     }
 600
 601     const actor = this.Actor.toFormattedJSON()
 602     const videoChannel = {
 603       id: this.id,
 604       displayName: this.getDisplayName(),
 605       description: this.description,
 606       support: this.support,
 607       isLocal: this.Actor.isOwned(),
 608       createdAt: this.createdAt,
 609       updatedAt: this.updatedAt,
 610       ownerAccount: undefined,
 611       videosCount,
 612       viewsPerDay
 613     }
 614
 615     if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON()
 616
 617     return Object.assign(actor, videoChannel)
 618   }
 619
 620   toActivityPubObject (this: MChannelAP): ActivityPubActor {
 621     const obj = this.Actor.toActivityPubObject(this.name)
 622
 623     return Object.assign(obj, {
 624       summary: this.description,
 625       support: this.support,
 626       attributedTo: [
 627         {
 628           type: 'Person' as 'Person',
 629           id: this.Account.Actor.url
 630         }
 631       ]
 632     })
 633   }
 634
 635   getLocalUrl (this: MAccountActor | MChannelActor) {
 636     return WEBSERVER.URL + `/video-channels/` + this.Actor.preferredUsername
 637   }
 638
 639   getDisplayName () {
 640     return this.name
 641   }
 642
 643   isOutdated () {
 644     return this.Actor.isOutdated()
 645   }
 646 }