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