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