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