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 { 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 { ActorImageModel } from '../account/actor-image'
  40 import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor'
  41 import { ActorFollowModel } from '../activitypub/actor-follow'
  42 import { ServerModel } from '../server/server'
  43 import { buildServerIdsFollowedBy, buildTrigramSearchIndex, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils'
  44 import { VideoModel } from './video'
  45 import { VideoPlaylistModel } from './video-playlist'
  46
  47 export enum ScopeNames {
  48   FOR_API = 'FOR_API',
  49   SUMMARY = 'SUMMARY',
  50   WITH_ACCOUNT = 'WITH_ACCOUNT',
  51   WITH_ACTOR = 'WITH_ACTOR',
  52   WITH_ACTOR_BANNER = 'WITH_ACTOR_BANNER',
  53   WITH_VIDEOS = 'WITH_VIDEOS',
  54   WITH_STATS = 'WITH_STATS'
  55 }
  56
  57 type AvailableForListOptions = {
  58   actorId: number
  59   search?: string
  60 }
  61
  62 type AvailableWithStatsOptions = {
  63   daysPrior: number
  64 }
  65
  66 export type SummaryOptions = {
  67   actorRequired?: boolean // Default: true
  68   withAccount?: boolean // Default: false
  69   withAccountBlockerIds?: number[]
  70 }
  71
  72 @DefaultScope(() => ({
  73   include: [
  74     {
  75       model: ActorModel,
  76       required: true
  77     }
  78   ]
  79 }))
  80 @Scopes(() => ({
  81   [ScopeNames.FOR_API]: (options: AvailableForListOptions) => {
  82     // Only list local channels OR channels that are on an instance followed by actorId
  83     const inQueryInstanceFollow = buildServerIdsFollowedBy(options.actorId)
  84
  85     return {
  86       include: [
  87         {
  88           attributes: {
  89             exclude: unusedActorAttributesForAPI
  90           },
  91           model: ActorModel,
  92           where: {
  93             [Op.or]: [
  94               {
  95                 serverId: null
  96               },
  97               {
  98                 serverId: {
  99                   [Op.in]: Sequelize.literal(inQueryInstanceFollow)
 100                 }
 101               }
 102             ]
 103           },
 104           include: [
 105             {
 106               model: ActorImageModel,
 107               as: 'Banner',
 108               required: false
 109             }
 110           ]
 111         },
 112         {
 113           model: AccountModel,
 114           required: true,
 115           include: [
 116             {
 117               attributes: {
 118                 exclude: unusedActorAttributesForAPI
 119               },
 120               model: ActorModel, // Default scope includes avatar and server
 121               required: true
 122             }
 123           ]
 124         }
 125       ]
 126     }
 127   },
 128   [ScopeNames.SUMMARY]: (options: SummaryOptions = {}) => {
 129     const include: Includeable[] = [
 130       {
 131         attributes: [ 'id', 'preferredUsername', 'url', 'serverId', 'avatarId' ],
 132         model: ActorModel.unscoped(),
 133         required: options.actorRequired ?? true,
 134         include: [
 135           {
 136             attributes: [ 'host' ],
 137             model: ServerModel.unscoped(),
 138             required: false
 139           },
 140           {
 141             model: ActorImageModel.unscoped(),
 142             as: 'Avatar',
 143             required: false
 144           }
 145         ]
 146       }
 147     ]
 148
 149     const base: FindOptions = {
 150       attributes: [ 'id', 'name', 'description', 'actorId' ]
 151     }
 152
 153     if (options.withAccount === true) {
 154       include.push({
 155         model: AccountModel.scope({
 156           method: [ AccountModelScopeNames.SUMMARY, { withAccountBlockerIds: options.withAccountBlockerIds } as AccountSummaryOptions ]
 157         }),
 158         required: true
 159       })
 160     }
 161
 162     base.include = include
 163
 164     return base
 165   },
 166   [ScopeNames.WITH_ACCOUNT]: {
 167     include: [
 168       {
 169         model: AccountModel,
 170         required: true
 171       }
 172     ]
 173   },
 174   [ScopeNames.WITH_ACTOR]: {
 175     include: [
 176       ActorModel
 177     ]
 178   },
 179   [ScopeNames.WITH_ACTOR_BANNER]: {
 180     include: [
 181       {
 182         model: ActorModel,
 183         include: [
 184           {
 185             model: ActorImageModel,
 186             required: false,
 187             as: 'Banner'
 188           }
 189         ]
 190       }
 191     ]
 192   },
 193   [ScopeNames.WITH_VIDEOS]: {
 194     include: [
 195       VideoModel
 196     ]
 197   },
 198   [ScopeNames.WITH_STATS]: (options: AvailableWithStatsOptions = { daysPrior: 30 }) => {
 199     const daysPrior = parseInt(options.daysPrior + '', 10)
 200
 201     return {
 202       attributes: {
 203         include: [
 204           [
 205             literal('(SELECT COUNT(*) FROM "video" WHERE "channelId" = "VideoChannelModel"."id")'),
 206             'videosCount'
 207           ],
 208           [
 209             literal(
 210               '(' +
 211               `SELECT string_agg(concat_ws('|', t.day, t.views), ',') ` +
 212               'FROM ( ' +
 213                 'WITH ' +
 214                   'days AS ( ' +
 215                     `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
 216                           `date_trunc('day', now()), '1 day'::interval) AS day ` +
 217                   ') ' +
 218                   'SELECT days.day AS day, COALESCE(SUM("videoView".views), 0) AS views ' +
 219                   'FROM days ' +
 220                   'LEFT JOIN (' +
 221                     '"videoView" INNER JOIN "video" ON "videoView"."videoId" = "video"."id" ' +
 222                     'AND "video"."channelId" = "VideoChannelModel"."id"' +
 223                   `) ON date_trunc('day', "videoView"."startDate") = date_trunc('day', days.day) ` +
 224                   'GROUP BY day ' +
 225                   'ORDER BY day ' +
 226                 ') t' +
 227               ')'
 228             ),
 229             'viewsPerDay'
 230           ]
 231         ]
 232       }
 233     }
 234   }
 235 }))
 236 @Table({
 237   tableName: 'videoChannel',
 238   indexes: [
 239     buildTrigramSearchIndex('video_channel_name_trigram', 'name'),
 240
 241     {
 242       fields: [ 'accountId' ]
 243     },
 244     {
 245       fields: [ 'actorId' ]
 246     }
 247   ]
 248 })
 249 export class VideoChannelModel extends Model {
 250
 251   @AllowNull(false)
 252   @Is('VideoChannelName', value => throwIfNotValid(value, isVideoChannelNameValid, 'name'))
 253   @Column
 254   name: string
 255
 256   @AllowNull(true)
 257   @Default(null)
 258   @Is('VideoChannelDescription', value => throwIfNotValid(value, isVideoChannelDescriptionValid, 'description', true))
 259   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_CHANNELS.DESCRIPTION.max))
 260   description: string
 261
 262   @AllowNull(true)
 263   @Default(null)
 264   @Is('VideoChannelSupport', value => throwIfNotValid(value, isVideoChannelSupportValid, 'support', true))
 265   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEO_CHANNELS.SUPPORT.max))
 266   support: string
 267
 268   @CreatedAt
 269   createdAt: Date
 270
 271   @UpdatedAt
 272   updatedAt: Date
 273
 274   @ForeignKey(() => ActorModel)
 275   @Column
 276   actorId: number
 277
 278   @BelongsTo(() => ActorModel, {
 279     foreignKey: {
 280       allowNull: false
 281     },
 282     onDelete: 'cascade'
 283   })
 284   Actor: ActorModel
 285
 286   @ForeignKey(() => AccountModel)
 287   @Column
 288   accountId: number
 289
 290   @BelongsTo(() => AccountModel, {
 291     foreignKey: {
 292       allowNull: false
 293     },
 294     hooks: true
 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 = VideoModel.sequelize.escape(options.search)
 437     const escapedLikeSearch = VideoModel.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): Promise<MChannelBannerAccountDefault> {
 525     return VideoChannelModel.unscoped()
 526       .scope([ ScopeNames.WITH_ACTOR_BANNER, ScopeNames.WITH_ACCOUNT ])
 527       .findByPk(id)
 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 }