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   isVideoChannelNameValid,
  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) {
 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, isVideoChannelNameValid, '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 listForApi (parameters: {
 442     actorId: number
 443     start: number
 444     count: number
 445     sort: string
 446   }) {
 447     const { actorId } = parameters
 448
 449     const query = {
 450       offset: parameters.start,
 451       limit: parameters.count,
 452       order: getSort(parameters.sort)
 453     }
 454
 455     return VideoChannelModel
 456       .scope({
 457         method: [ ScopeNames.FOR_API, { actorId } as AvailableForListOptions ]
 458       })
 459       .findAndCountAll(query)
 460       .then(({ rows, count }) => {
 461         return { total: count, data: rows }
 462       })
 463   }
 464
 465   static listLocalsForSitemap (sort: string): Promise<MChannelActor[]> {
 466     const query = {
 467       attributes: [ ],
 468       offset: 0,
 469       order: getSort(sort),
 470       include: [
 471         {
 472           attributes: [ 'preferredUsername', 'serverId' ],
 473           model: ActorModel.unscoped(),
 474           where: {
 475             serverId: null
 476           }
 477         }
 478       ]
 479     }
 480
 481     return VideoChannelModel
 482       .unscoped()
 483       .findAll(query)
 484   }
 485
 486   static searchForApi (options: {
 487     actorId: number
 488     search?: string
 489     start: number
 490     count: number
 491     sort: string
 492
 493     host?: string
 494     handles?: string[]
 495   }) {
 496     let attributesInclude: any[] = [ literal('0 as similarity') ]
 497     let where: WhereOptions
 498
 499     if (options.search) {
 500       const escapedSearch = VideoChannelModel.sequelize.escape(options.search)
 501       const escapedLikeSearch = VideoChannelModel.sequelize.escape('%' + options.search + '%')
 502       attributesInclude = [ createSimilarityAttribute('VideoChannelModel.name', options.search) ]
 503
 504       where = {
 505         [Op.or]: [
 506           Sequelize.literal(
 507             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 508           ),
 509           Sequelize.literal(
 510             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 511           )
 512         ]
 513       }
 514     }
 515
 516     const query = {
 517       attributes: {
 518         include: attributesInclude
 519       },
 520       offset: options.start,
 521       limit: options.count,
 522       order: getSort(options.sort),
 523       where
 524     }
 525
 526     return VideoChannelModel
 527       .scope({
 528         method: [ ScopeNames.FOR_API, pick(options, [ 'actorId', 'host', 'handles' ]) as AvailableForListOptions ]
 529       })
 530       .findAndCountAll(query)
 531       .then(({ rows, count }) => {
 532         return { total: count, data: rows }
 533       })
 534   }
 535
 536   static listByAccount (options: {
 537     accountId: number
 538     start: number
 539     count: number
 540     sort: string
 541     withStats?: boolean
 542     search?: string
 543   }) {
 544     const escapedSearch = VideoModel.sequelize.escape(options.search)
 545     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
 546     const where = options.search
 547       ? {
 548         [Op.or]: [
 549           Sequelize.literal(
 550             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 551           ),
 552           Sequelize.literal(
 553             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 554           )
 555         ]
 556       }
 557       : null
 558
 559     const query = {
 560       offset: options.start,
 561       limit: options.count,
 562       order: getSort(options.sort),
 563       include: [
 564         {
 565           model: AccountModel,
 566           where: {
 567             id: options.accountId
 568           },
 569           required: true
 570         }
 571       ],
 572       where
 573     }
 574
 575     const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR_BANNER ]
 576
 577     if (options.withStats === true) {
 578       scopes.push({
 579         method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
 580       })
 581     }
 582
 583     return VideoChannelModel
 584       .scope(scopes)
 585       .findAndCountAll(query)
 586       .then(({ rows, count }) => {
 587         return { total: count, data: rows }
 588       })
 589   }
 590
 591   static loadAndPopulateAccount (id: number, transaction?: Transaction): Promise<MChannelBannerAccountDefault> {
 592     return VideoChannelModel.unscoped()
 593       .scope([ ScopeNames.WITH_ACTOR_BANNER, ScopeNames.WITH_ACCOUNT ])
 594       .findByPk(id, { transaction })
 595   }
 596
 597   static loadByUrlAndPopulateAccount (url: string): Promise<MChannelBannerAccountDefault> {
 598     const query = {
 599       include: [
 600         {
 601           model: ActorModel,
 602           required: true,
 603           where: {
 604             url
 605           },
 606           include: [
 607             {
 608               model: ActorImageModel,
 609               required: false,
 610               as: 'Banner'
 611             }
 612           ]
 613         }
 614       ]
 615     }
 616
 617     return VideoChannelModel
 618       .scope([ ScopeNames.WITH_ACCOUNT ])
 619       .findOne(query)
 620   }
 621
 622   static loadByNameWithHostAndPopulateAccount (nameWithHost: string) {
 623     const [ name, host ] = nameWithHost.split('@')
 624
 625     if (!host || host === WEBSERVER.HOST) return VideoChannelModel.loadLocalByNameAndPopulateAccount(name)
 626
 627     return VideoChannelModel.loadByNameAndHostAndPopulateAccount(name, host)
 628   }
 629
 630   static loadLocalByNameAndPopulateAccount (name: string): Promise<MChannelBannerAccountDefault> {
 631     const query = {
 632       include: [
 633         {
 634           model: ActorModel,
 635           required: true,
 636           where: {
 637             preferredUsername: name,
 638             serverId: null
 639           },
 640           include: [
 641             {
 642               model: ActorImageModel,
 643               required: false,
 644               as: 'Banner'
 645             }
 646           ]
 647         }
 648       ]
 649     }
 650
 651     return VideoChannelModel.unscoped()
 652       .scope([ ScopeNames.WITH_ACCOUNT ])
 653       .findOne(query)
 654   }
 655
 656   static loadByNameAndHostAndPopulateAccount (name: string, host: string): Promise<MChannelBannerAccountDefault> {
 657     const query = {
 658       include: [
 659         {
 660           model: ActorModel,
 661           required: true,
 662           where: {
 663             preferredUsername: name
 664           },
 665           include: [
 666             {
 667               model: ServerModel,
 668               required: true,
 669               where: { host }
 670             },
 671             {
 672               model: ActorImageModel,
 673               required: false,
 674               as: 'Banner'
 675             }
 676           ]
 677         }
 678       ]
 679     }
 680
 681     return VideoChannelModel.unscoped()
 682       .scope([ ScopeNames.WITH_ACCOUNT ])
 683       .findOne(query)
 684   }
 685
 686   toFormattedSummaryJSON (this: MChannelSummaryFormattable): VideoChannelSummary {
 687     const actor = this.Actor.toFormattedSummaryJSON()
 688
 689     return {
 690       id: this.id,
 691       name: actor.name,
 692       displayName: this.getDisplayName(),
 693       url: actor.url,
 694       host: actor.host,
 695       avatar: actor.avatar
 696     }
 697   }
 698
 699   toFormattedJSON (this: MChannelFormattable): VideoChannel {
 700     const viewsPerDayString = this.get('viewsPerDay') as string
 701     const videosCount = this.get('videosCount') as number
 702
 703     let viewsPerDay: { date: Date, views: number }[]
 704
 705     if (viewsPerDayString) {
 706       viewsPerDay = viewsPerDayString.split(',')
 707         .map(v => {
 708           const [ dateString, amount ] = v.split('|')
 709
 710           return {
 711             date: new Date(dateString),
 712             views: +amount
 713           }
 714         })
 715     }
 716
 717     const actor = this.Actor.toFormattedJSON()
 718     const videoChannel = {
 719       id: this.id,
 720       displayName: this.getDisplayName(),
 721       description: this.description,
 722       support: this.support,
 723       isLocal: this.Actor.isOwned(),
 724       updatedAt: this.updatedAt,
 725       ownerAccount: undefined,
 726       videosCount,
 727       viewsPerDay
 728     }
 729
 730     if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON()
 731
 732     return Object.assign(actor, videoChannel)
 733   }
 734
 735   toActivityPubObject (this: MChannelAP): ActivityPubActor {
 736     const obj = this.Actor.toActivityPubObject(this.name)
 737
 738     return Object.assign(obj, {
 739       summary: this.description,
 740       support: this.support,
 741       attributedTo: [
 742         {
 743           type: 'Person' as 'Person',
 744           id: this.Account.Actor.url
 745         }
 746       ]
 747     })
 748   }
 749
 750   getLocalUrl (this: MAccountActor | MChannelActor) {
 751     return WEBSERVER.URL + `/video-channels/` + this.Actor.preferredUsername
 752   }
 753
 754   getDisplayName () {
 755     return this.name
 756   }
 757
 758   isOutdated () {
 759     return this.Actor.isOutdated()
 760   }
 761
 762   setAsUpdated (transaction: Transaction) {
 763     return setAsUpdated('videoChannel', this.id, transaction)
 764   }
 765 }