server/models/video/video-channel.ts

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