server/models/video/video-comment.ts

   1 import * as Bluebird from 'bluebird'
   2 import { uniq } from 'lodash'
   3 import { FindOptions, Op, Order, ScopeOptions, Sequelize, Transaction } from 'sequelize'
   4 import { AllowNull, BelongsTo, Column, CreatedAt, DataType, ForeignKey, Is, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript'
   5 import { getServerActor } from '@server/models/application/application'
   6 import { MAccount, MAccountId, MUserAccountId } from '@server/typings/models'
   7 import { VideoPrivacy } from '@shared/models'
   8 import { ActivityTagObject, ActivityTombstoneObject } from '../../../shared/models/activitypub/objects/common-objects'
   9 import { VideoCommentObject } from '../../../shared/models/activitypub/objects/video-comment-object'
  10 import { VideoComment } from '../../../shared/models/videos/video-comment.model'
  11 import { actorNameAlphabet } from '../../helpers/custom-validators/activitypub/actor'
  12 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
  13 import { regexpCapture } from '../../helpers/regexp'
  14 import { CONSTRAINTS_FIELDS, WEBSERVER } from '../../initializers/constants'
  15 import {
  16   MComment,
  17   MCommentAP,
  18   MCommentFormattable,
  19   MCommentId,
  20   MCommentOwner,
  21   MCommentOwnerReplyVideoLight,
  22   MCommentOwnerVideo,
  23   MCommentOwnerVideoFeed,
  24   MCommentOwnerVideoReply,
  25   MVideoImmutable
  26 } from '../../typings/models/video'
  27 import { AccountModel } from '../account/account'
  28 import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor'
  29 import { buildBlockedAccountSQL, buildLocalAccountIdsIn, getCommentSort, throwIfNotValid } from '../utils'
  30 import { VideoModel } from './video'
  31 import { VideoChannelModel } from './video-channel'
  32
  33 enum ScopeNames {
  34   WITH_ACCOUNT = 'WITH_ACCOUNT',
  35   WITH_ACCOUNT_FOR_API = 'WITH_ACCOUNT_FOR_API',
  36   WITH_IN_REPLY_TO = 'WITH_IN_REPLY_TO',
  37   WITH_VIDEO = 'WITH_VIDEO',
  38   ATTRIBUTES_FOR_API = 'ATTRIBUTES_FOR_API'
  39 }
  40
  41 @Scopes(() => ({
  42   [ScopeNames.ATTRIBUTES_FOR_API]: (blockerAccountIds: number[]) => {
  43     return {
  44       attributes: {
  45         include: [
  46           [
  47             Sequelize.literal(
  48               '(' +
  49                 'WITH "blocklist" AS (' + buildBlockedAccountSQL(blockerAccountIds) + ')' +
  50                 'SELECT COUNT("replies"."id") - (' +
  51                   'SELECT COUNT("replies"."id") ' +
  52                   'FROM "videoComment" AS "replies" ' +
  53                   'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
  54                   'AND "accountId" IN (SELECT "id" FROM "blocklist")' +
  55                 ')' +
  56                 'FROM "videoComment" AS "replies" ' +
  57                 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
  58                 'AND "accountId" NOT IN (SELECT "id" FROM "blocklist")' +
  59               ')'
  60             ),
  61             'totalReplies'
  62           ],
  63           [
  64             Sequelize.literal(
  65               '(' +
  66                 'SELECT COUNT("replies"."id") ' +
  67                 'FROM "videoComment" AS "replies" ' +
  68                 'INNER JOIN "video" ON "video"."id" = "replies"."videoId" ' +
  69                 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
  70                 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' +
  71                 'AND "replies"."accountId" = "videoChannel"."accountId"' +
  72               ')'
  73             ),
  74             'totalRepliesFromVideoAuthor'
  75           ]
  76         ]
  77       }
  78     } as FindOptions
  79   },
  80   [ScopeNames.WITH_ACCOUNT]: {
  81     include: [
  82       {
  83         model: AccountModel
  84       }
  85     ]
  86   },
  87   [ScopeNames.WITH_ACCOUNT_FOR_API]: {
  88     include: [
  89       {
  90         model: AccountModel.unscoped(),
  91         include: [
  92           {
  93             attributes: {
  94               exclude: unusedActorAttributesForAPI
  95             },
  96             model: ActorModel, // Default scope includes avatar and server
  97             required: true
  98           }
  99         ]
 100       }
 101     ]
 102   },
 103   [ScopeNames.WITH_IN_REPLY_TO]: {
 104     include: [
 105       {
 106         model: VideoCommentModel,
 107         as: 'InReplyToVideoComment'
 108       }
 109     ]
 110   },
 111   [ScopeNames.WITH_VIDEO]: {
 112     include: [
 113       {
 114         model: VideoModel,
 115         required: true,
 116         include: [
 117           {
 118             model: VideoChannelModel,
 119             required: true,
 120             include: [
 121               {
 122                 model: AccountModel,
 123                 required: true
 124               }
 125             ]
 126           }
 127         ]
 128       }
 129     ]
 130   }
 131 }))
 132 @Table({
 133   tableName: 'videoComment',
 134   indexes: [
 135     {
 136       fields: [ 'videoId' ]
 137     },
 138     {
 139       fields: [ 'videoId', 'originCommentId' ]
 140     },
 141     {
 142       fields: [ 'url' ],
 143       unique: true
 144     },
 145     {
 146       fields: [ 'accountId' ]
 147     }
 148   ]
 149 })
 150 export class VideoCommentModel extends Model<VideoCommentModel> {
 151   @CreatedAt
 152   createdAt: Date
 153
 154   @UpdatedAt
 155   updatedAt: Date
 156
 157   @AllowNull(true)
 158   @Column(DataType.DATE)
 159   deletedAt: Date
 160
 161   @AllowNull(false)
 162   @Is('VideoCommentUrl', value => throwIfNotValid(value, isActivityPubUrlValid, 'url'))
 163   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.URL.max))
 164   url: string
 165
 166   @AllowNull(false)
 167   @Column(DataType.TEXT)
 168   text: string
 169
 170   @ForeignKey(() => VideoCommentModel)
 171   @Column
 172   originCommentId: number
 173
 174   @BelongsTo(() => VideoCommentModel, {
 175     foreignKey: {
 176       name: 'originCommentId',
 177       allowNull: true
 178     },
 179     as: 'OriginVideoComment',
 180     onDelete: 'CASCADE'
 181   })
 182   OriginVideoComment: VideoCommentModel
 183
 184   @ForeignKey(() => VideoCommentModel)
 185   @Column
 186   inReplyToCommentId: number
 187
 188   @BelongsTo(() => VideoCommentModel, {
 189     foreignKey: {
 190       name: 'inReplyToCommentId',
 191       allowNull: true
 192     },
 193     as: 'InReplyToVideoComment',
 194     onDelete: 'CASCADE'
 195   })
 196   InReplyToVideoComment: VideoCommentModel | null
 197
 198   @ForeignKey(() => VideoModel)
 199   @Column
 200   videoId: number
 201
 202   @BelongsTo(() => VideoModel, {
 203     foreignKey: {
 204       allowNull: false
 205     },
 206     onDelete: 'CASCADE'
 207   })
 208   Video: VideoModel
 209
 210   @ForeignKey(() => AccountModel)
 211   @Column
 212   accountId: number
 213
 214   @BelongsTo(() => AccountModel, {
 215     foreignKey: {
 216       allowNull: true
 217     },
 218     onDelete: 'CASCADE'
 219   })
 220   Account: AccountModel
 221
 222   static loadById (id: number, t?: Transaction): Bluebird<MComment> {
 223     const query: FindOptions = {
 224       where: {
 225         id
 226       }
 227     }
 228
 229     if (t !== undefined) query.transaction = t
 230
 231     return VideoCommentModel.findOne(query)
 232   }
 233
 234   static loadByIdAndPopulateVideoAndAccountAndReply (id: number, t?: Transaction): Bluebird<MCommentOwnerVideoReply> {
 235     const query: FindOptions = {
 236       where: {
 237         id
 238       }
 239     }
 240
 241     if (t !== undefined) query.transaction = t
 242
 243     return VideoCommentModel
 244       .scope([ ScopeNames.WITH_VIDEO, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_IN_REPLY_TO ])
 245       .findOne(query)
 246   }
 247
 248   static loadByUrlAndPopulateAccountAndVideo (url: string, t?: Transaction): Bluebird<MCommentOwnerVideo> {
 249     const query: FindOptions = {
 250       where: {
 251         url
 252       }
 253     }
 254
 255     if (t !== undefined) query.transaction = t
 256
 257     return VideoCommentModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEO ]).findOne(query)
 258   }
 259
 260   static loadByUrlAndPopulateReplyAndVideoUrlAndAccount (url: string, t?: Transaction): Bluebird<MCommentOwnerReplyVideoLight> {
 261     const query: FindOptions = {
 262       where: {
 263         url
 264       },
 265       include: [
 266         {
 267           attributes: [ 'id', 'url' ],
 268           model: VideoModel.unscoped()
 269         }
 270       ]
 271     }
 272
 273     if (t !== undefined) query.transaction = t
 274
 275     return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_ACCOUNT ]).findOne(query)
 276   }
 277
 278   static async listThreadsForApi (parameters: {
 279     videoId: number
 280     isVideoOwned: boolean
 281     start: number
 282     count: number
 283     sort: string
 284     user?: MUserAccountId
 285   }) {
 286     const { videoId, isVideoOwned, start, count, sort, user } = parameters
 287
 288     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ videoId, user, isVideoOwned })
 289
 290     const query = {
 291       offset: start,
 292       limit: count,
 293       order: getCommentSort(sort),
 294       where: {
 295         [Op.and]: [
 296           {
 297             videoId
 298           },
 299           {
 300             inReplyToCommentId: null
 301           },
 302           {
 303             [Op.or]: [
 304               {
 305                 accountId: {
 306                   [Op.notIn]: Sequelize.literal(
 307                     '(' + buildBlockedAccountSQL(blockerAccountIds) + ')'
 308                   )
 309                 }
 310               },
 311               {
 312                 accountId: null
 313               }
 314             ]
 315           }
 316         ]
 317       }
 318     }
 319
 320     const scopes: (string | ScopeOptions)[] = [
 321       ScopeNames.WITH_ACCOUNT_FOR_API,
 322       {
 323         method: [ ScopeNames.ATTRIBUTES_FOR_API, blockerAccountIds ]
 324       }
 325     ]
 326
 327     return VideoCommentModel
 328       .scope(scopes)
 329       .findAndCountAll(query)
 330       .then(({ rows, count }) => {
 331         return { total: count, data: rows }
 332       })
 333   }
 334
 335   static async listThreadCommentsForApi (parameters: {
 336     videoId: number
 337     isVideoOwned: boolean
 338     threadId: number
 339     user?: MUserAccountId
 340   }) {
 341     const { videoId, threadId, user, isVideoOwned } = parameters
 342
 343     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ videoId, user, isVideoOwned })
 344
 345     const query = {
 346       order: [ [ 'createdAt', 'ASC' ], [ 'updatedAt', 'ASC' ] ] as Order,
 347       where: {
 348         videoId,
 349         [Op.or]: [
 350           { id: threadId },
 351           { originCommentId: threadId }
 352         ],
 353         accountId: {
 354           [Op.notIn]: Sequelize.literal(
 355             '(' + buildBlockedAccountSQL(blockerAccountIds) + ')'
 356           )
 357         }
 358       }
 359     }
 360
 361     const scopes: any[] = [
 362       ScopeNames.WITH_ACCOUNT_FOR_API,
 363       {
 364         method: [ ScopeNames.ATTRIBUTES_FOR_API, blockerAccountIds ]
 365       }
 366     ]
 367
 368     return VideoCommentModel
 369       .scope(scopes)
 370       .findAndCountAll(query)
 371       .then(({ rows, count }) => {
 372         return { total: count, data: rows }
 373       })
 374   }
 375
 376   static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Bluebird<MCommentOwner[]> {
 377     const query = {
 378       order: [ [ 'createdAt', order ] ] as Order,
 379       where: {
 380         id: {
 381           [Op.in]: Sequelize.literal('(' +
 382             'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
 383               `SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ${comment.id} ` +
 384               'UNION ' +
 385               'SELECT "parent"."id", "parent"."inReplyToCommentId" FROM "videoComment" "parent" ' +
 386               'INNER JOIN "children" ON "children"."inReplyToCommentId" = "parent"."id"' +
 387             ') ' +
 388             'SELECT id FROM children' +
 389           ')'),
 390           [Op.ne]: comment.id
 391         }
 392       },
 393       transaction: t
 394     }
 395
 396     return VideoCommentModel
 397       .scope([ ScopeNames.WITH_ACCOUNT ])
 398       .findAll(query)
 399   }
 400
 401   static async listAndCountByVideoForAP (video: MVideoImmutable, start: number, count: number, t?: Transaction) {
 402     const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({
 403       videoId: video.id,
 404       isVideoOwned: video.isOwned()
 405     })
 406
 407     const query = {
 408       order: [ [ 'createdAt', 'ASC' ] ] as Order,
 409       offset: start,
 410       limit: count,
 411       where: {
 412         videoId: video.id,
 413         accountId: {
 414           [Op.notIn]: Sequelize.literal(
 415             '(' + buildBlockedAccountSQL(blockerAccountIds) + ')'
 416           )
 417         }
 418       },
 419       transaction: t
 420     }
 421
 422     return VideoCommentModel.findAndCountAll<MComment>(query)
 423   }
 424
 425   static async listForFeed (start: number, count: number, videoId?: number): Promise<MCommentOwnerVideoFeed[]> {
 426     const serverActor = await getServerActor()
 427
 428     const query = {
 429       order: [ [ 'createdAt', 'DESC' ] ] as Order,
 430       offset: start,
 431       limit: count,
 432       where: {
 433         deletedAt: null,
 434         accountId: {
 435           [Op.notIn]: Sequelize.literal(
 436             '(' + buildBlockedAccountSQL([ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ]) + ')'
 437           )
 438         }
 439       },
 440       include: [
 441         {
 442           attributes: [ 'name', 'uuid' ],
 443           model: VideoModel.unscoped(),
 444           required: true,
 445           where: {
 446             privacy: VideoPrivacy.PUBLIC
 447           },
 448           include: [
 449             {
 450               attributes: [ 'accountId' ],
 451               model: VideoChannelModel.unscoped(),
 452               required: true
 453             }
 454           ]
 455         }
 456       ]
 457     }
 458
 459     if (videoId) query.where['videoId'] = videoId
 460
 461     return VideoCommentModel
 462       .scope([ ScopeNames.WITH_ACCOUNT ])
 463       .findAll(query)
 464   }
 465
 466   static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) {
 467     const accountWhere = filter.onVideosOfAccount
 468       ? { id: filter.onVideosOfAccount.id }
 469       : {}
 470
 471     const query = {
 472       limit: 1000,
 473       where: {
 474         deletedAt: null,
 475         accountId: ofAccount.id
 476       },
 477       include: [
 478         {
 479           model: VideoModel,
 480           required: true,
 481           include: [
 482             {
 483               model: VideoChannelModel,
 484               required: true,
 485               include: [
 486                 {
 487                   model: AccountModel,
 488                   required: true,
 489                   where: accountWhere
 490                 }
 491               ]
 492             }
 493           ]
 494         }
 495       ]
 496     }
 497
 498     return VideoCommentModel
 499       .scope([ ScopeNames.WITH_ACCOUNT ])
 500       .findAll(query)
 501   }
 502
 503   static async getStats () {
 504     const totalLocalVideoComments = await VideoCommentModel.count({
 505       include: [
 506         {
 507           model: AccountModel,
 508           required: true,
 509           include: [
 510             {
 511               model: ActorModel,
 512               required: true,
 513               where: {
 514                 serverId: null
 515               }
 516             }
 517           ]
 518         }
 519       ]
 520     })
 521     const totalVideoComments = await VideoCommentModel.count()
 522
 523     return {
 524       totalLocalVideoComments,
 525       totalVideoComments
 526     }
 527   }
 528
 529   static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
 530     const query = {
 531       where: {
 532         updatedAt: {
 533           [Op.lt]: beforeUpdatedAt
 534         },
 535         videoId,
 536         accountId: {
 537           [Op.notIn]: buildLocalAccountIdsIn()
 538         },
 539         // Do not delete Tombstones
 540         deletedAt: null
 541       }
 542     }
 543
 544     return VideoCommentModel.destroy(query)
 545   }
 546
 547   getCommentStaticPath () {
 548     return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
 549   }
 550
 551   getThreadId (): number {
 552     return this.originCommentId || this.id
 553   }
 554
 555   isOwned () {
 556     if (!this.Account) {
 557       return false
 558     }
 559
 560     return this.Account.isOwned()
 561   }
 562
 563   isDeleted () {
 564     return this.deletedAt !== null
 565   }
 566
 567   extractMentions () {
 568     let result: string[] = []
 569
 570     const localMention = `@(${actorNameAlphabet}+)`
 571     const remoteMention = `${localMention}@${WEBSERVER.HOST}`
 572
 573     const mentionRegex = this.isOwned()
 574       ? '(?:(?:' + remoteMention + ')|(?:' + localMention + '))' // Include local mentions?
 575       : '(?:' + remoteMention + ')'
 576
 577     const firstMentionRegex = new RegExp(`^${mentionRegex} `, 'g')
 578     const endMentionRegex = new RegExp(` ${mentionRegex}$`, 'g')
 579     const remoteMentionsRegex = new RegExp(' ' + remoteMention + ' ', 'g')
 580
 581     result = result.concat(
 582       regexpCapture(this.text, firstMentionRegex)
 583         .map(([ , username1, username2 ]) => username1 || username2),
 584
 585       regexpCapture(this.text, endMentionRegex)
 586         .map(([ , username1, username2 ]) => username1 || username2),
 587
 588       regexpCapture(this.text, remoteMentionsRegex)
 589         .map(([ , username ]) => username)
 590     )
 591
 592     // Include local mentions
 593     if (this.isOwned()) {
 594       const localMentionsRegex = new RegExp(' ' + localMention + ' ', 'g')
 595
 596       result = result.concat(
 597         regexpCapture(this.text, localMentionsRegex)
 598           .map(([ , username ]) => username)
 599       )
 600     }
 601
 602     return uniq(result)
 603   }
 604
 605   toFormattedJSON (this: MCommentFormattable) {
 606     return {
 607       id: this.id,
 608       url: this.url,
 609       text: this.text,
 610       threadId: this.originCommentId || this.id,
 611       inReplyToCommentId: this.inReplyToCommentId || null,
 612       videoId: this.videoId,
 613       createdAt: this.createdAt,
 614       updatedAt: this.updatedAt,
 615       deletedAt: this.deletedAt,
 616       isDeleted: this.isDeleted(),
 617       totalRepliesFromVideoAuthor: this.get('totalRepliesFromVideoAuthor') || 0,
 618       totalReplies: this.get('totalReplies') || 0,
 619       account: this.Account ? this.Account.toFormattedJSON() : null
 620     } as VideoComment
 621   }
 622
 623   toActivityPubObject (this: MCommentAP, threadParentComments: MCommentOwner[]): VideoCommentObject | ActivityTombstoneObject {
 624     let inReplyTo: string
 625     // New thread, so in AS we reply to the video
 626     if (this.inReplyToCommentId === null) {
 627       inReplyTo = this.Video.url
 628     } else {
 629       inReplyTo = this.InReplyToVideoComment.url
 630     }
 631
 632     if (this.isDeleted()) {
 633       return {
 634         id: this.url,
 635         type: 'Tombstone',
 636         formerType: 'Note',
 637         inReplyTo,
 638         published: this.createdAt.toISOString(),
 639         updated: this.updatedAt.toISOString(),
 640         deleted: this.deletedAt.toISOString()
 641       }
 642     }
 643
 644     const tag: ActivityTagObject[] = []
 645     for (const parentComment of threadParentComments) {
 646       if (!parentComment.Account) continue
 647
 648       const actor = parentComment.Account.Actor
 649
 650       tag.push({
 651         type: 'Mention',
 652         href: actor.url,
 653         name: `@${actor.preferredUsername}@${actor.getHost()}`
 654       })
 655     }
 656
 657     return {
 658       type: 'Note' as 'Note',
 659       id: this.url,
 660       content: this.text,
 661       inReplyTo,
 662       updated: this.updatedAt.toISOString(),
 663       published: this.createdAt.toISOString(),
 664       url: this.url,
 665       attributedTo: this.Account.Actor.url,
 666       tag
 667     }
 668   }
 669
 670   private static async buildBlockerAccountIds (options: {
 671     videoId: number
 672     isVideoOwned: boolean
 673     user?: MUserAccountId
 674   }) {
 675     const { videoId, user, isVideoOwned } = options
 676
 677     const serverActor = await getServerActor()
 678     const blockerAccountIds = [ serverActor.Account.id ]
 679
 680     if (user) blockerAccountIds.push(user.Account.id)
 681
 682     if (isVideoOwned) {
 683       const videoOwnerAccount = await AccountModel.loadAccountIdFromVideo(videoId)
 684       blockerAccountIds.push(videoOwnerAccount.id)
 685     }
 686
 687     return blockerAccountIds
 688   }
 689 }