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   MChannelAccountDefault,
  32   MChannelActor,
  33   MChannelActorAccountDefaultVideos,
  34   MChannelAP,
  35   MChannelFormattable,
  36   MChannelSummaryFormattable
  37 } from '../../types/models/video'
  38 import { AccountModel, ScopeNames as AccountModelScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
  39 import { ActorImageModel } from '../account/actor-image'
  40 import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor'
  41 import { ActorFollowModel } from '../activitypub/actor-follow'
  42 import { ServerModel } from '../server/server'
  43 import { buildServerIdsFollowedBy, buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
  44 import { VideoModel } from './video'
  45 import { VideoPlaylistModel } from './video-playlist'
  46
  47 export enum ScopeNames {
  48   FOR_API = 'FOR_API',
  49   SUMMARY = 'SUMMARY',
  50   WITH_ACCOUNT = 'WITH_ACCOUNT',
  51   WITH_ACTOR = 'WITH_ACTOR',
  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_VIDEOS]: {
 172     include: [
 173       VideoModel
 174     ]
 175   },
 176   [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => {
 177     const daysPrior = parseInt(options.daysPrior + '', 10)
 178
 179     return {
 180       attributes: {
 181         include: [
 182           [
 183             literal('(SELECT COUNT(*) FROM "video" WHERE "channelId" = "VideoChannelModel"."id")'),
 184             'videosCount'
 185           ],
 186           [
 187             literal(
 188               '(' +
 189               `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
 190               'FROM ( ' +
 191                 'WITH ' +
 192                   'days AS ( ' +
 193                     `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
 194                           `date_trunc('day', now()), '1 day'::interval) AS day ` +
 195                   ') ' +
 196                   'SELECT days.day AS day, COALESCE(SUM("videoView".views), 0) AS views ' +
 197                   'FROM days ' +
 198                   'LEFT JOIN (' +
 199                     '"videoView" INNER JOIN "video" ON "videoView"."videoId" = "video"."id" ' +
 200                     'AND "video"."channelId" = "VideoChannelModel"."id"' +
 201                   `) ON date_trunc('day', "videoView"."startDate") = date_trunc('day', days.day) ` +
 202                   'GROUP BY day ' +
 203                   'ORDER BY day ' +
 204                 ') t' +
 205               ')'
 206             ),
 207             'viewsPerDay'
 208           ]
 209         ]
 210       }
 211     }
 212   }
 213 }))
 214 @Table({
 215   tableName: 'videoChannel',
 216   indexes: [
 217     buildTrigramSearchIndex('video_channel_name_trigram', 'name'),
 218
 219     {
 220       fields: [ 'accountId' ]
 221     },
 222     {
 223       fields: [ 'actorId' ]
 224     }
 225   ]
 226 })
 227 export class VideoChannelModel extends Model {
 228
 229   @AllowNull(false)
 230   @Is('VideoChannelName', value => throwIfNotValid(value, isVideoChannelNameValid, 'name'))
 231   @Column
 232   name: string
 233
 234   @AllowNull(true)
 235   @Default(null)
 236   @Is('VideoChannelDescription', value => throwIfNotValid(value, isVideoChannelDescriptionValid, 'description', true))
 237   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_CHANNELS.DESCRIPTION.max))
 238   description: string
 239
 240   @AllowNull(true)
 241   @Default(null)
 242   @Is('VideoChannelSupport', value => throwIfNotValid(value, isVideoChannelSupportValid, 'support', true))
 243   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_CHANNELS.SUPPORT.max))
 244   support: string
 245
 246   @CreatedAt
 247   createdAt: Date
 248
 249   @UpdatedAt
 250   updatedAt: Date
 251
 252   @ForeignKey(() => ActorModel)
 253   @Column
 254   actorId: number
 255
 256   @BelongsTo(() => ActorModel, {
 257     foreignKey: {
 258       allowNull: false
 259     },
 260     onDelete: 'cascade'
 261   })
 262   Actor: ActorModel
 263
 264   @ForeignKey(() => AccountModel)
 265   @Column
 266   accountId: number
 267
 268   @BelongsTo(() => AccountModel, {
 269     foreignKey: {
 270       allowNull: false
 271     },
 272     hooks: true
 273   })
 274   Account: AccountModel
 275
 276   @HasMany(() => VideoModel, {
 277     foreignKey: {
 278       name: 'channelId',
 279       allowNull: false
 280     },
 281     onDelete: 'CASCADE',
 282     hooks: true
 283   })
 284   Videos: VideoModel[]
 285
 286   @HasMany(() => VideoPlaylistModel, {
 287     foreignKey: {
 288       allowNull: true
 289     },
 290     onDelete: 'CASCADE',
 291     hooks: true
 292   })
 293   VideoPlaylists: VideoPlaylistModel[]
 294
 295   @BeforeDestroy
 296   static async sendDeleteIfOwned (instance: VideoChannelModel, options) {
 297     if (!instance.Actor) {
 298       instance.Actor = await instance.$get('Actor', { transaction: options.transaction })
 299     }
 300
 301     await ActorFollowModel.removeFollowsOf(instance.Actor.id, options.transaction)
 302
 303     if (instance.Actor.isOwned()) {
 304       return sendDeleteActor(instance.Actor, options.transaction)
 305     }
 306
 307     return undefined
 308   }
 309
 310   static countByAccount (accountId: number) {
 311     const query = {
 312       where: {
 313         accountId
 314       }
 315     }
 316
 317     return VideoChannelModel.count(query)
 318   }
 319
 320   static listForApi (parameters: {
 321     actorId: number
 322     start: number
 323     count: number
 324     sort: string
 325   }) {
 326     const { actorId } = parameters
 327
 328     const query = {
 329       offset: parameters.start,
 330       limit: parameters.count,
 331       order: getSort(parameters.sort)
 332     }
 333
 334     return VideoChannelModel
 335       .scope({
 336         method: [ ScopeNames.FOR_API, { actorId } as AvailableForListOptions ]
 337       })
 338       .findAndCountAll(query)
 339       .then(({ rows, count }) => {
 340         return { total: count, data: rows }
 341       })
 342   }
 343
 344   static listLocalsForSitemap (sort: string): Promise<MChannelActor[]> {
 345     const query = {
 346       attributes: [ ],
 347       offset: 0,
 348       order: getSort(sort),
 349       include: [
 350         {
 351           attributes: [ 'preferredUsername', 'serverId' ],
 352           model: ActorModel.unscoped(),
 353           where: {
 354             serverId: null
 355           }
 356         }
 357       ]
 358     }
 359
 360     return VideoChannelModel
 361       .unscoped()
 362       .findAll(query)
 363   }
 364
 365   static searchForApi (options: {
 366     actorId: number
 367     search: string
 368     start: number
 369     count: number
 370     sort: string
 371   }) {
 372     const attributesInclude = []
 373     const escapedSearch = VideoModel.sequelize.escape(options.search)
 374     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
 375     attributesInclude.push(createSimilarityAttribute('VideoChannelModel.name', options.search))
 376
 377     const query = {
 378       attributes: {
 379         include: attributesInclude
 380       },
 381       offset: options.start,
 382       limit: options.count,
 383       order: getSort(options.sort),
 384       where: {
 385         [Op.or]: [
 386           Sequelize.literal(
 387             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 388           ),
 389           Sequelize.literal(
 390             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 391           )
 392         ]
 393       }
 394     }
 395
 396     return VideoChannelModel
 397       .scope({
 398         method: [ ScopeNames.FOR_API, { actorId: options.actorId } as AvailableForListOptions ]
 399       })
 400       .findAndCountAll(query)
 401       .then(({ rows, count }) => {
 402         return { total: count, data: rows }
 403       })
 404   }
 405
 406   static listByAccount (options: {
 407     accountId: number
 408     start: number
 409     count: number
 410     sort: string
 411     withStats?: boolean
 412     search?: string
 413   }) {
 414     const escapedSearch = VideoModel.sequelize.escape(options.search)
 415     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
 416     const where = options.search
 417       ? {
 418         [Op.or]: [
 419           Sequelize.literal(
 420             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 421           ),
 422           Sequelize.literal(
 423             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 424           )
 425         ]
 426       }
 427       : null
 428
 429     const query = {
 430       offset: options.start,
 431       limit: options.count,
 432       order: getSort(options.sort),
 433       include: [
 434         {
 435           model: AccountModel,
 436           where: {
 437             id: options.accountId
 438           },
 439           required: true
 440         }
 441       ],
 442       where
 443     }
 444
 445     const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
 446
 447     if (options.withStats === true) {
 448       scopes.push({
 449         method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
 450       })
 451     }
 452
 453     return VideoChannelModel
 454       .scope(scopes)
 455       .findAndCountAll(query)
 456       .then(({ rows, count }) => {
 457         return { total: count, data: rows }
 458       })
 459   }
 460
 461   static loadByIdAndPopulateAccount (id: number): Promise<MChannelAccountDefault> {
 462     return VideoChannelModel.unscoped()
 463       .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
 464       .findByPk(id)
 465   }
 466
 467   static loadByIdAndAccount (id: number, accountId: number): Promise<MChannelAccountDefault> {
 468     const query = {
 469       where: {
 470         id,
 471         accountId
 472       }
 473     }
 474
 475     return VideoChannelModel.unscoped()
 476       .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
 477       .findOne(query)
 478   }
 479
 480   static loadAndPopulateAccount (id: number): Promise<MChannelAccountDefault> {
 481     return VideoChannelModel.unscoped()
 482       .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
 483       .findByPk(id)
 484   }
 485
 486   static loadByUrlAndPopulateAccount (url: string): Promise<MChannelAccountDefault> {
 487     const query = {
 488       include: [
 489         {
 490           model: ActorModel,
 491           required: true,
 492           where: {
 493             url
 494           }
 495         }
 496       ]
 497     }
 498
 499     return VideoChannelModel
 500       .scope([ ScopeNames.WITH_ACCOUNT ])
 501       .findOne(query)
 502   }
 503
 504   static loadByNameWithHostAndPopulateAccount (nameWithHost: string) {
 505     const [ name, host ] = nameWithHost.split('@')
 506
 507     if (!host || host === WEBSERVER.HOST) return VideoChannelModel.loadLocalByNameAndPopulateAccount(name)
 508
 509     return VideoChannelModel.loadByNameAndHostAndPopulateAccount(name, host)
 510   }
 511
 512   static loadLocalByNameAndPopulateAccount (name: string): Promise<MChannelAccountDefault> {
 513     const query = {
 514       include: [
 515         {
 516           model: ActorModel,
 517           required: true,
 518           where: {
 519             preferredUsername: name,
 520             serverId: null
 521           }
 522         }
 523       ]
 524     }
 525
 526     return VideoChannelModel.unscoped()
 527       .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
 528       .findOne(query)
 529   }
 530
 531   static loadByNameAndHostAndPopulateAccount (name: string, host: string): Promise<MChannelAccountDefault> {
 532     const query = {
 533       include: [
 534         {
 535           model: ActorModel,
 536           required: true,
 537           where: {
 538             preferredUsername: name
 539           },
 540           include: [
 541             {
 542               model: ServerModel,
 543               required: true,
 544               where: { host }
 545             }
 546           ]
 547         }
 548       ]
 549     }
 550
 551     return VideoChannelModel.unscoped()
 552       .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT ])
 553       .findOne(query)
 554   }
 555
 556   static loadAndPopulateAccountAndVideos (id: number): Promise<MChannelActorAccountDefaultVideos> {
 557     const options = {
 558       include: [
 559         VideoModel
 560       ]
 561     }
 562
 563     return VideoChannelModel.unscoped()
 564       .scope([ ScopeNames.WITH_ACTOR, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEOS ])
 565       .findByPk(id, options)
 566   }
 567
 568   toFormattedSummaryJSON (this: MChannelSummaryFormattable): VideoChannelSummary {
 569     const actor = this.Actor.toFormattedSummaryJSON()
 570
 571     return {
 572       id: this.id,
 573       name: actor.name,
 574       displayName: this.getDisplayName(),
 575       url: actor.url,
 576       host: actor.host,
 577       avatar: actor.avatar
 578     }
 579   }
 580
 581   toFormattedJSON (this: MChannelFormattable): VideoChannel {
 582     const viewsPerDayString = this.get('viewsPerDay') as string
 583     const videosCount = this.get('videosCount') as number
 584
 585     let viewsPerDay: { date: Date, views: number }[]
 586
 587     if (viewsPerDayString) {
 588       viewsPerDay = viewsPerDayString.split(',')
 589         .map(v => {
 590           const [ dateString, amount ] = v.split('|')
 591
 592           return {
 593             date: new Date(dateString),
 594             views: +amount
 595           }
 596         })
 597     }
 598
 599     const actor = this.Actor.toFormattedJSON()
 600     const videoChannel = {
 601       id: this.id,
 602       displayName: this.getDisplayName(),
 603       description: this.description,
 604       support: this.support,
 605       isLocal: this.Actor.isOwned(),
 606       createdAt: this.createdAt,
 607       updatedAt: this.updatedAt,
 608       ownerAccount: undefined,
 609       videosCount,
 610       viewsPerDay
 611     }
 612
 613     if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON()
 614
 615     return Object.assign(actor, videoChannel)
 616   }
 617
 618   toActivityPubObject (this: MChannelAP): ActivityPubActor {
 619     const obj = this.Actor.toActivityPubObject(this.name)
 620
 621     return Object.assign(obj, {
 622       summary: this.description,
 623       support: this.support,
 624       attributedTo: [
 625         {
 626           type: 'Person' as 'Person',
 627           id: this.Account.Actor.url
 628         }
 629       ]
 630     })
 631   }
 632
 633   getLocalUrl (this: MAccountActor | MChannelActor) {
 634     return WEBSERVER.URL + `/video-channels/` + this.Actor.preferredUsername
 635   }
 636
 637   getDisplayName () {
 638     return this.name
 639   }
 640
 641   isOutdated () {
 642     return this.Actor.isOutdated()
 643   }
 644 }