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     hooks: true
 296   })
 297   Account: AccountModel
 298
 299   @HasMany(() => VideoModel, {
 300     foreignKey: {
 301       name: 'channelId',
 302       allowNull: false
 303     },
 304     onDelete: 'CASCADE',
 305     hooks: true
 306   })
 307   Videos: VideoModel[]
 308
 309   @HasMany(() => VideoPlaylistModel, {
 310     foreignKey: {
 311       allowNull: true
 312     },
 313     onDelete: 'CASCADE',
 314     hooks: true
 315   })
 316   VideoPlaylists: VideoPlaylistModel[]
 317
 318   @BeforeDestroy
 319   static async sendDeleteIfOwned (instance: VideoChannelModel, options) {
 320     if (!instance.Actor) {
 321       instance.Actor = await instance.$get('Actor', { transaction: options.transaction })
 322     }
 323
 324     await ActorFollowModel.removeFollowsOf(instance.Actor.id, options.transaction)
 325
 326     if (instance.Actor.isOwned()) {
 327       return sendDeleteActor(instance.Actor, options.transaction)
 328     }
 329
 330     return undefined
 331   }
 332
 333   static countByAccount (accountId: number) {
 334     const query = {
 335       where: {
 336         accountId
 337       }
 338     }
 339
 340     return VideoChannelModel.count(query)
 341   }
 342
 343   static async getStats () {
 344
 345     function getActiveVideoChannels (days: number) {
 346       const options = {
 347         type: QueryTypes.SELECT as QueryTypes.SELECT,
 348         raw: true
 349       }
 350
 351       const query = `
 352 SELECT          COUNT(DISTINCT("VideoChannelModel"."id")) AS "count"
 353 FROM            "videoChannel"                            AS "VideoChannelModel"
 354 INNER JOIN      "video"                                   AS "Videos"
 355 ON              "VideoChannelModel"."id" = "Videos"."channelId"
 356 AND             ("Videos"."publishedAt" > Now() - interval '${days}d')
 357 INNER JOIN      "account" AS "Account"
 358 ON              "VideoChannelModel"."accountId" = "Account"."id"
 359 INNER JOIN      "actor" AS "Account->Actor"
 360 ON              "Account"."actorId" = "Account->Actor"."id"
 361 AND             "Account->Actor"."serverId" IS NULL
 362 LEFT OUTER JOIN "server" AS "Account->Actor->Server"
 363 ON              "Account->Actor"."serverId" = "Account->Actor->Server"."id"`
 364
 365       return VideoChannelModel.sequelize.query<{ count: string }>(query, options)
 366                               .then(r => parseInt(r[0].count, 10))
 367     }
 368
 369     const totalLocalVideoChannels = await VideoChannelModel.count()
 370     const totalLocalDailyActiveVideoChannels = await getActiveVideoChannels(1)
 371     const totalLocalWeeklyActiveVideoChannels = await getActiveVideoChannels(7)
 372     const totalLocalMonthlyActiveVideoChannels = await getActiveVideoChannels(30)
 373     const totalHalfYearActiveVideoChannels = await getActiveVideoChannels(180)
 374
 375     return {
 376       totalLocalVideoChannels,
 377       totalLocalDailyActiveVideoChannels,
 378       totalLocalWeeklyActiveVideoChannels,
 379       totalLocalMonthlyActiveVideoChannels,
 380       totalHalfYearActiveVideoChannels
 381     }
 382   }
 383
 384   static listForApi (parameters: {
 385     actorId: number
 386     start: number
 387     count: number
 388     sort: string
 389   }) {
 390     const { actorId } = parameters
 391
 392     const query = {
 393       offset: parameters.start,
 394       limit: parameters.count,
 395       order: getSort(parameters.sort)
 396     }
 397
 398     return VideoChannelModel
 399       .scope({
 400         method: [ ScopeNames.FOR_API, { actorId } as AvailableForListOptions ]
 401       })
 402       .findAndCountAll(query)
 403       .then(({ rows, count }) => {
 404         return { total: count, data: rows }
 405       })
 406   }
 407
 408   static listLocalsForSitemap (sort: string): Promise<MChannelActor[]> {
 409     const query = {
 410       attributes: [ ],
 411       offset: 0,
 412       order: getSort(sort),
 413       include: [
 414         {
 415           attributes: [ 'preferredUsername', 'serverId' ],
 416           model: ActorModel.unscoped(),
 417           where: {
 418             serverId: null
 419           }
 420         }
 421       ]
 422     }
 423
 424     return VideoChannelModel
 425       .unscoped()
 426       .findAll(query)
 427   }
 428
 429   static searchForApi (options: {
 430     actorId: number
 431     search: string
 432     start: number
 433     count: number
 434     sort: string
 435   }) {
 436     const attributesInclude = []
 437     const escapedSearch = VideoModel.sequelize.escape(options.search)
 438     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
 439     attributesInclude.push(createSimilarityAttribute('VideoChannelModel.name', options.search))
 440
 441     const query = {
 442       attributes: {
 443         include: attributesInclude
 444       },
 445       offset: options.start,
 446       limit: options.count,
 447       order: getSort(options.sort),
 448       where: {
 449         [Op.or]: [
 450           Sequelize.literal(
 451             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 452           ),
 453           Sequelize.literal(
 454             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 455           )
 456         ]
 457       }
 458     }
 459
 460     return VideoChannelModel
 461       .scope({
 462         method: [ ScopeNames.FOR_API, { actorId: options.actorId } as AvailableForListOptions ]
 463       })
 464       .findAndCountAll(query)
 465       .then(({ rows, count }) => {
 466         return { total: count, data: rows }
 467       })
 468   }
 469
 470   static listByAccount (options: {
 471     accountId: number
 472     start: number
 473     count: number
 474     sort: string
 475     withStats?: boolean
 476     search?: string
 477   }) {
 478     const escapedSearch = VideoModel.sequelize.escape(options.search)
 479     const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
 480     const where = options.search
 481       ? {
 482         [Op.or]: [
 483           Sequelize.literal(
 484             'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
 485           ),
 486           Sequelize.literal(
 487             'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
 488           )
 489         ]
 490       }
 491       : null
 492
 493     const query = {
 494       offset: options.start,
 495       limit: options.count,
 496       order: getSort(options.sort),
 497       include: [
 498         {
 499           model: AccountModel,
 500           where: {
 501             id: options.accountId
 502           },
 503           required: true
 504         }
 505       ],
 506       where
 507     }
 508
 509     const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR_BANNER ]
 510
 511     if (options.withStats === true) {
 512       scopes.push({
 513         method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
 514       })
 515     }
 516
 517     return VideoChannelModel
 518       .scope(scopes)
 519       .findAndCountAll(query)
 520       .then(({ rows, count }) => {
 521         return { total: count, data: rows }
 522       })
 523   }
 524
 525   static loadAndPopulateAccount (id: number): Promise<MChannelBannerAccountDefault> {
 526     return VideoChannelModel.unscoped()
 527       .scope([ ScopeNames.WITH_ACTOR_BANNER, ScopeNames.WITH_ACCOUNT ])
 528       .findByPk(id)
 529   }
 530
 531   static loadByUrlAndPopulateAccount (url: string): Promise<MChannelBannerAccountDefault> {
 532     const query = {
 533       include: [
 534         {
 535           model: ActorModel,
 536           required: true,
 537           where: {
 538             url
 539           },
 540           include: [
 541             {
 542               model: ActorImageModel,
 543               required: false,
 544               as: 'Banner'
 545             }
 546           ]
 547         }
 548       ]
 549     }
 550
 551     return VideoChannelModel
 552       .scope([ ScopeNames.WITH_ACCOUNT ])
 553       .findOne(query)
 554   }
 555
 556   static loadByNameWithHostAndPopulateAccount (nameWithHost: string) {
 557     const [ name, host ] = nameWithHost.split('@')
 558
 559     if (!host || host === WEBSERVER.HOST) return VideoChannelModel.loadLocalByNameAndPopulateAccount(name)
 560
 561     return VideoChannelModel.loadByNameAndHostAndPopulateAccount(name, host)
 562   }
 563
 564   static loadLocalByNameAndPopulateAccount (name: string): Promise<MChannelBannerAccountDefault> {
 565     const query = {
 566       include: [
 567         {
 568           model: ActorModel,
 569           required: true,
 570           where: {
 571             preferredUsername: name,
 572             serverId: null
 573           },
 574           include: [
 575             {
 576               model: ActorImageModel,
 577               required: false,
 578               as: 'Banner'
 579             }
 580           ]
 581         }
 582       ]
 583     }
 584
 585     return VideoChannelModel.unscoped()
 586       .scope([ ScopeNames.WITH_ACCOUNT ])
 587       .findOne(query)
 588   }
 589
 590   static loadByNameAndHostAndPopulateAccount (name: string, host: string): Promise<MChannelBannerAccountDefault> {
 591     const query = {
 592       include: [
 593         {
 594           model: ActorModel,
 595           required: true,
 596           where: {
 597             preferredUsername: name
 598           },
 599           include: [
 600             {
 601               model: ServerModel,
 602               required: true,
 603               where: { host }
 604             },
 605             {
 606               model: ActorImageModel,
 607               required: false,
 608               as: 'Banner'
 609             }
 610           ]
 611         }
 612       ]
 613     }
 614
 615     return VideoChannelModel.unscoped()
 616       .scope([ ScopeNames.WITH_ACCOUNT ])
 617       .findOne(query)
 618   }
 619
 620   toFormattedSummaryJSON (this: MChannelSummaryFormattable): VideoChannelSummary {
 621     const actor = this.Actor.toFormattedSummaryJSON()
 622
 623     return {
 624       id: this.id,
 625       name: actor.name,
 626       displayName: this.getDisplayName(),
 627       url: actor.url,
 628       host: actor.host,
 629       avatar: actor.avatar
 630     }
 631   }
 632
 633   toFormattedJSON (this: MChannelFormattable): VideoChannel {
 634     const viewsPerDayString = this.get('viewsPerDay') as string
 635     const videosCount = this.get('videosCount') as number
 636
 637     let viewsPerDay: { date: Date, views: number }[]
 638
 639     if (viewsPerDayString) {
 640       viewsPerDay = viewsPerDayString.split(',')
 641         .map(v => {
 642           const [ dateString, amount ] = v.split('|')
 643
 644           return {
 645             date: new Date(dateString),
 646             views: +amount
 647           }
 648         })
 649     }
 650
 651     const actor = this.Actor.toFormattedJSON()
 652     const videoChannel = {
 653       id: this.id,
 654       displayName: this.getDisplayName(),
 655       description: this.description,
 656       support: this.support,
 657       isLocal: this.Actor.isOwned(),
 658       updatedAt: this.updatedAt,
 659       ownerAccount: undefined,
 660       videosCount,
 661       viewsPerDay
 662     }
 663
 664     if (this.Account) videoChannel.ownerAccount = this.Account.toFormattedJSON()
 665
 666     return Object.assign(actor, videoChannel)
 667   }
 668
 669   toActivityPubObject (this: MChannelAP): ActivityPubActor {
 670     const obj = this.Actor.toActivityPubObject(this.name)
 671
 672     return Object.assign(obj, {
 673       summary: this.description,
 674       support: this.support,
 675       attributedTo: [
 676         {
 677           type: 'Person' as 'Person',
 678           id: this.Account.Actor.url
 679         }
 680       ]
 681     })
 682   }
 683
 684   getLocalUrl (this: MAccountActor | MChannelActor) {
 685     return WEBSERVER.URL + `/video-channels/` + this.Actor.preferredUsername
 686   }
 687
 688   getDisplayName () {
 689     return this.name
 690   }
 691
 692   isOutdated () {
 693     return this.Actor.isOutdated()
 694   }
 695
 696   setAsUpdated (transaction: Transaction) {
 697     return setAsUpdated('videoChannel', this.id, transaction)
 698   }
 699 }