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