server/models/video/video-channel.ts

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