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 (parameters: {
 431     start: number
 432     count: number
 433     videoId?: number
 434     accountId?: number
 435     videoChannelId?: number
 436   }): Promise<MCommentOwnerVideoFeed[]> {
 437     const serverActor = await getServerActor()
 438     const { start, count, videoId, accountId, videoChannelId } = parameters
 439
 440     const accountExclusion = {
 441       [Op.notIn]: Sequelize.literal(
 442         '(' + buildBlockedAccountSQL([ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ]) + ')'
 443       )
 444     }
 445     const accountWhere = accountId
 446       ? {
 447           [Op.and]: {
 448             ...accountExclusion,
 449             [Op.eq]: accountId
 450           }
 451         }
 452       : accountExclusion
 453
 454     const videoChannelWhere = videoChannelId ? { id: videoChannelId } : undefined
 455
 456     const query = {
 457       order: [ [ 'createdAt', 'DESC' ] ] as Order,
 458       offset: start,
 459       limit: count,
 460       where: {
 461         deletedAt: null,
 462         accountId: accountWhere
 463       },
 464       include: [
 465         {
 466           attributes: [ 'name', 'uuid' ],
 467           model: VideoModel.unscoped(),
 468           required: true,
 469           where: {
 470             privacy: VideoPrivacy.PUBLIC
 471           },
 472           include: [
 473             {
 474               attributes: [ 'accountId' ],
 475               model: VideoChannelModel.unscoped(),
 476               required: true,
 477               where: videoChannelWhere
 478             }
 479           ]
 480         }
 481       ]
 482     }
 483
 484     if (videoId) query.where['videoId'] = videoId
 485
 486     return VideoCommentModel
 487       .scope([ ScopeNames.WITH_ACCOUNT ])
 488       .findAll(query)
 489   }
 490
 491   static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) {
 492     const accountWhere = filter.onVideosOfAccount
 493       ? { id: filter.onVideosOfAccount.id }
 494       : {}
 495
 496     const query = {
 497       limit: 1000,
 498       where: {
 499         deletedAt: null,
 500         accountId: ofAccount.id
 501       },
 502       include: [
 503         {
 504           model: VideoModel,
 505           required: true,
 506           include: [
 507             {
 508               model: VideoChannelModel,
 509               required: true,
 510               include: [
 511                 {
 512                   model: AccountModel,
 513                   required: true,
 514                   where: accountWhere
 515                 }
 516               ]
 517             }
 518           ]
 519         }
 520       ]
 521     }
 522
 523     return VideoCommentModel
 524       .scope([ ScopeNames.WITH_ACCOUNT ])
 525       .findAll(query)
 526   }
 527
 528   static async getStats () {
 529     const totalLocalVideoComments = await VideoCommentModel.count({
 530       include: [
 531         {
 532           model: AccountModel,
 533           required: true,
 534           include: [
 535             {
 536               model: ActorModel,
 537               required: true,
 538               where: {
 539                 serverId: null
 540               }
 541             }
 542           ]
 543         }
 544       ]
 545     })
 546     const totalVideoComments = await VideoCommentModel.count()
 547
 548     return {
 549       totalLocalVideoComments,
 550       totalVideoComments
 551     }
 552   }
 553
 554   static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
 555     const query = {
 556       where: {
 557         updatedAt: {
 558           [Op.lt]: beforeUpdatedAt
 559         },
 560         videoId,
 561         accountId: {
 562           [Op.notIn]: buildLocalAccountIdsIn()
 563         },
 564         // Do not delete Tombstones
 565         deletedAt: null
 566       }
 567     }
 568
 569     return VideoCommentModel.destroy(query)
 570   }
 571
 572   getCommentStaticPath () {
 573     return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
 574   }
 575
 576   getThreadId (): number {
 577     return this.originCommentId || this.id
 578   }
 579
 580   isOwned () {
 581     if (!this.Account) {
 582       return false
 583     }
 584
 585     return this.Account.isOwned()
 586   }
 587
 588   isDeleted () {
 589     return this.deletedAt !== null
 590   }
 591
 592   extractMentions () {
 593     let result: string[] = []
 594
 595     const localMention = `@(${actorNameAlphabet}+)`
 596     const remoteMention = `${localMention}@${WEBSERVER.HOST}`
 597
 598     const mentionRegex = this.isOwned()
 599       ? '(?:(?:' + remoteMention + ')|(?:' + localMention + '))' // Include local mentions?
 600       : '(?:' + remoteMention + ')'
 601
 602     const firstMentionRegex = new RegExp(`^${mentionRegex} `, 'g')
 603     const endMentionRegex = new RegExp(` ${mentionRegex}$`, 'g')
 604     const remoteMentionsRegex = new RegExp(' ' + remoteMention + ' ', 'g')
 605
 606     result = result.concat(
 607       regexpCapture(this.text, firstMentionRegex)
 608         .map(([ , username1, username2 ]) => username1 || username2),
 609
 610       regexpCapture(this.text, endMentionRegex)
 611         .map(([ , username1, username2 ]) => username1 || username2),
 612
 613       regexpCapture(this.text, remoteMentionsRegex)
 614         .map(([ , username ]) => username)
 615     )
 616
 617     // Include local mentions
 618     if (this.isOwned()) {
 619       const localMentionsRegex = new RegExp(' ' + localMention + ' ', 'g')
 620
 621       result = result.concat(
 622         regexpCapture(this.text, localMentionsRegex)
 623           .map(([ , username ]) => username)
 624       )
 625     }
 626
 627     return uniq(result)
 628   }
 629
 630   toFormattedJSON (this: MCommentFormattable) {
 631     return {
 632       id: this.id,
 633       url: this.url,
 634       text: this.text,
 635       threadId: this.originCommentId || this.id,
 636       inReplyToCommentId: this.inReplyToCommentId || null,
 637       videoId: this.videoId,
 638       createdAt: this.createdAt,
 639       updatedAt: this.updatedAt,
 640       deletedAt: this.deletedAt,
 641       isDeleted: this.isDeleted(),
 642       totalRepliesFromVideoAuthor: this.get('totalRepliesFromVideoAuthor') || 0,
 643       totalReplies: this.get('totalReplies') || 0,
 644       account: this.Account ? this.Account.toFormattedJSON() : null
 645     } as VideoComment
 646   }
 647
 648   toActivityPubObject (this: MCommentAP, threadParentComments: MCommentOwner[]): VideoCommentObject | ActivityTombstoneObject {
 649     let inReplyTo: string
 650     // New thread, so in AS we reply to the video
 651     if (this.inReplyToCommentId === null) {
 652       inReplyTo = this.Video.url
 653     } else {
 654       inReplyTo = this.InReplyToVideoComment.url
 655     }
 656
 657     if (this.isDeleted()) {
 658       return {
 659         id: this.url,
 660         type: 'Tombstone',
 661         formerType: 'Note',
 662         inReplyTo,
 663         published: this.createdAt.toISOString(),
 664         updated: this.updatedAt.toISOString(),
 665         deleted: this.deletedAt.toISOString()
 666       }
 667     }
 668
 669     const tag: ActivityTagObject[] = []
 670     for (const parentComment of threadParentComments) {
 671       if (!parentComment.Account) continue
 672
 673       const actor = parentComment.Account.Actor
 674
 675       tag.push({
 676         type: 'Mention',
 677         href: actor.url,
 678         name: `@${actor.preferredUsername}@${actor.getHost()}`
 679       })
 680     }
 681
 682     return {
 683       type: 'Note' as 'Note',
 684       id: this.url,
 685       content: this.text,
 686       inReplyTo,
 687       updated: this.updatedAt.toISOString(),
 688       published: this.createdAt.toISOString(),
 689       url: this.url,
 690       attributedTo: this.Account.Actor.url,
 691       tag
 692     }
 693   }
 694
 695   private static async buildBlockerAccountIds (options: {
 696     videoId: number
 697     isVideoOwned: boolean
 698     user?: MUserAccountId
 699   }) {
 700     const { videoId, user, isVideoOwned } = options
 701
 702     const serverActor = await getServerActor()
 703     const blockerAccountIds = [ serverActor.Account.id ]
 704
 705     if (user) blockerAccountIds.push(user.Account.id)
 706
 707     if (isVideoOwned) {
 708       const videoOwnerAccount = await AccountModel.loadAccountIdFromVideo(videoId)
 709       blockerAccountIds.push(videoOwnerAccount.id)
 710     }
 711
 712     return blockerAccountIds
 713   }
 714 }