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