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