server/models/video/video-comment.ts

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