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