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 } 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, getSort, 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(false)
 126   @Is('VideoCommentUrl', value => throwIfNotValid(value, isActivityPubUrlValid, 'url'))
 127   @Column(DataType.STRING(CONSTRAINTS_FIELDS.VIDEOS.URL.max))
 128   url: string
 129
 130   @AllowNull(false)
 131   @Column(DataType.TEXT)
 132   text: string
 133
 134   @ForeignKey(() => VideoCommentModel)
 135   @Column
 136   originCommentId: number
 137
 138   @BelongsTo(() => VideoCommentModel, {
 139     foreignKey: {
 140       name: 'originCommentId',
 141       allowNull: true
 142     },
 143     as: 'OriginVideoComment',
 144     onDelete: 'CASCADE'
 145   })
 146   OriginVideoComment: VideoCommentModel
 147
 148   @ForeignKey(() => VideoCommentModel)
 149   @Column
 150   inReplyToCommentId: number
 151
 152   @BelongsTo(() => VideoCommentModel, {
 153     foreignKey: {
 154       name: 'inReplyToCommentId',
 155       allowNull: true
 156     },
 157     as: 'InReplyToVideoComment',
 158     onDelete: 'CASCADE'
 159   })
 160   InReplyToVideoComment: VideoCommentModel | null
 161
 162   @ForeignKey(() => VideoModel)
 163   @Column
 164   videoId: number
 165
 166   @BelongsTo(() => VideoModel, {
 167     foreignKey: {
 168       allowNull: false
 169     },
 170     onDelete: 'CASCADE'
 171   })
 172   Video: VideoModel
 173
 174   @ForeignKey(() => AccountModel)
 175   @Column
 176   accountId: number
 177
 178   @BelongsTo(() => AccountModel, {
 179     foreignKey: {
 180       allowNull: false
 181     },
 182     onDelete: 'CASCADE'
 183   })
 184   Account: AccountModel
 185
 186   static loadById (id: number, t?: Transaction): Bluebird<MComment> {
 187     const query: FindOptions = {
 188       where: {
 189         id
 190       }
 191     }
 192
 193     if (t !== undefined) query.transaction = t
 194
 195     return VideoCommentModel.findOne(query)
 196   }
 197
 198   static loadByIdAndPopulateVideoAndAccountAndReply (id: number, t?: Transaction): Bluebird<MCommentOwnerVideoReply> {
 199     const query: FindOptions = {
 200       where: {
 201         id
 202       }
 203     }
 204
 205     if (t !== undefined) query.transaction = t
 206
 207     return VideoCommentModel
 208       .scope([ ScopeNames.WITH_VIDEO, ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_IN_REPLY_TO ])
 209       .findOne(query)
 210   }
 211
 212   static loadByUrlAndPopulateAccountAndVideo (url: string, t?: Transaction): Bluebird<MCommentOwnerVideo> {
 213     const query: FindOptions = {
 214       where: {
 215         url
 216       }
 217     }
 218
 219     if (t !== undefined) query.transaction = t
 220
 221     return VideoCommentModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_VIDEO ]).findOne(query)
 222   }
 223
 224   static loadByUrlAndPopulateReplyAndVideoUrlAndAccount (url: string, t?: Transaction): Bluebird<MCommentOwnerReplyVideoLight> {
 225     const query: FindOptions = {
 226       where: {
 227         url
 228       },
 229       include: [
 230         {
 231           attributes: [ 'id', 'url' ],
 232           model: VideoModel.unscoped()
 233         }
 234       ]
 235     }
 236
 237     if (t !== undefined) query.transaction = t
 238
 239     return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_ACCOUNT ]).findOne(query)
 240   }
 241
 242   static async listThreadsForApi (parameters: {
 243     videoId: number,
 244     start: number,
 245     count: number,
 246     sort: string,
 247     user?: MUserAccountId
 248   }) {
 249     const { videoId, start, count, sort, user } = parameters
 250
 251     const serverActor = await getServerActor()
 252     const serverAccountId = serverActor.Account.id
 253     const userAccountId = user ? user.Account.id : undefined
 254
 255     const query = {
 256       offset: start,
 257       limit: count,
 258       order: getSort(sort),
 259       where: {
 260         videoId,
 261         inReplyToCommentId: null,
 262         accountId: {
 263           [Op.notIn]: Sequelize.literal(
 264             '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')'
 265           )
 266         }
 267       }
 268     }
 269
 270     const scopes: (string | ScopeOptions)[] = [
 271       ScopeNames.WITH_ACCOUNT,
 272       {
 273         method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ]
 274       }
 275     ]
 276
 277     return VideoCommentModel
 278       .scope(scopes)
 279       .findAndCountAll(query)
 280       .then(({ rows, count }) => {
 281         return { total: count, data: rows }
 282       })
 283   }
 284
 285   static async listThreadCommentsForApi (parameters: {
 286     videoId: number,
 287     threadId: number,
 288     user?: MUserAccountId
 289   }) {
 290     const { videoId, threadId, user } = parameters
 291
 292     const serverActor = await getServerActor()
 293     const serverAccountId = serverActor.Account.id
 294     const userAccountId = user ? user.Account.id : undefined
 295
 296     const query = {
 297       order: [ [ 'createdAt', 'ASC' ], [ 'updatedAt', 'ASC' ] ] as Order,
 298       where: {
 299         videoId,
 300         [ Op.or ]: [
 301           { id: threadId },
 302           { originCommentId: threadId }
 303         ],
 304         accountId: {
 305           [Op.notIn]: Sequelize.literal(
 306             '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')'
 307           )
 308         }
 309       }
 310     }
 311
 312     const scopes: any[] = [
 313       ScopeNames.WITH_ACCOUNT,
 314       {
 315         method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ]
 316       }
 317     ]
 318
 319     return VideoCommentModel
 320       .scope(scopes)
 321       .findAndCountAll(query)
 322       .then(({ rows, count }) => {
 323         return { total: count, data: rows }
 324       })
 325   }
 326
 327   static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Bluebird<MCommentOwner[]> {
 328     const query = {
 329       order: [ [ 'createdAt', order ] ] as Order,
 330       where: {
 331         id: {
 332           [ Op.in ]: Sequelize.literal('(' +
 333             'WITH RECURSIVE children (id, "inReplyToCommentId") AS ( ' +
 334               `SELECT id, "inReplyToCommentId" FROM "videoComment" WHERE id = ${comment.id} ` +
 335               'UNION ' +
 336               'SELECT "parent"."id", "parent"."inReplyToCommentId" FROM "videoComment" "parent" ' +
 337               'INNER JOIN "children" ON "children"."inReplyToCommentId" = "parent"."id"' +
 338             ') ' +
 339             'SELECT id FROM children' +
 340           ')'),
 341           [ Op.ne ]: comment.id
 342         }
 343       },
 344       transaction: t
 345     }
 346
 347     return VideoCommentModel
 348       .scope([ ScopeNames.WITH_ACCOUNT ])
 349       .findAll(query)
 350   }
 351
 352   static listAndCountByVideoId (videoId: number, start: number, count: number, t?: Transaction, order: 'ASC' | 'DESC' = 'ASC') {
 353     const query = {
 354       order: [ [ 'createdAt', order ] ] as Order,
 355       offset: start,
 356       limit: count,
 357       where: {
 358         videoId
 359       },
 360       transaction: t
 361     }
 362
 363     return VideoCommentModel.findAndCountAll<MComment>(query)
 364   }
 365
 366   static listForFeed (start: number, count: number, videoId?: number): Bluebird<MCommentOwnerVideoFeed[]> {
 367     const query = {
 368       order: [ [ 'createdAt', 'DESC' ] ] as Order,
 369       offset: start,
 370       limit: count,
 371       where: {},
 372       include: [
 373         {
 374           attributes: [ 'name', 'uuid' ],
 375           model: VideoModel.unscoped(),
 376           required: true
 377         }
 378       ]
 379     }
 380
 381     if (videoId) query.where['videoId'] = videoId
 382
 383     return VideoCommentModel
 384       .scope([ ScopeNames.WITH_ACCOUNT ])
 385       .findAll(query)
 386   }
 387
 388   static async getStats () {
 389     const totalLocalVideoComments = await VideoCommentModel.count({
 390       include: [
 391         {
 392           model: AccountModel,
 393           required: true,
 394           include: [
 395             {
 396               model: ActorModel,
 397               required: true,
 398               where: {
 399                 serverId: null
 400               }
 401             }
 402           ]
 403         }
 404       ]
 405     })
 406     const totalVideoComments = await VideoCommentModel.count()
 407
 408     return {
 409       totalLocalVideoComments,
 410       totalVideoComments
 411     }
 412   }
 413
 414   static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) {
 415     const query = {
 416       where: {
 417         updatedAt: {
 418           [Op.lt]: beforeUpdatedAt
 419         },
 420         videoId,
 421         accountId: {
 422           [Op.notIn]: buildLocalAccountIdsIn()
 423         }
 424       }
 425     }
 426
 427     return VideoCommentModel.destroy(query)
 428   }
 429
 430   getCommentStaticPath () {
 431     return this.Video.getWatchStaticPath() + ';threadId=' + this.getThreadId()
 432   }
 433
 434   getThreadId (): number {
 435     return this.originCommentId || this.id
 436   }
 437
 438   isOwned () {
 439     return this.Account.isOwned()
 440   }
 441
 442   extractMentions () {
 443     let result: string[] = []
 444
 445     const localMention = `@(${actorNameAlphabet}+)`
 446     const remoteMention = `${localMention}@${WEBSERVER.HOST}`
 447
 448     const mentionRegex = this.isOwned()
 449       ? '(?:(?:' + remoteMention + ')|(?:' + localMention + '))' // Include local mentions?
 450       : '(?:' + remoteMention + ')'
 451
 452     const firstMentionRegex = new RegExp(`^${mentionRegex} `, 'g')
 453     const endMentionRegex = new RegExp(` ${mentionRegex}$`, 'g')
 454     const remoteMentionsRegex = new RegExp(' ' + remoteMention + ' ', 'g')
 455
 456     result = result.concat(
 457       regexpCapture(this.text, firstMentionRegex)
 458         .map(([ , username1, username2 ]) => username1 || username2),
 459
 460       regexpCapture(this.text, endMentionRegex)
 461         .map(([ , username1, username2 ]) => username1 || username2),
 462
 463       regexpCapture(this.text, remoteMentionsRegex)
 464         .map(([ , username ]) => username)
 465     )
 466
 467     // Include local mentions
 468     if (this.isOwned()) {
 469       const localMentionsRegex = new RegExp(' ' + localMention + ' ', 'g')
 470
 471       result = result.concat(
 472         regexpCapture(this.text, localMentionsRegex)
 473           .map(([ , username ]) => username)
 474       )
 475     }
 476
 477     return uniq(result)
 478   }
 479
 480   toFormattedJSON (this: MCommentFormattable) {
 481     return {
 482       id: this.id,
 483       url: this.url,
 484       text: this.text,
 485       threadId: this.originCommentId || this.id,
 486       inReplyToCommentId: this.inReplyToCommentId || null,
 487       videoId: this.videoId,
 488       createdAt: this.createdAt,
 489       updatedAt: this.updatedAt,
 490       totalReplies: this.get('totalReplies') || 0,
 491       account: this.Account.toFormattedJSON()
 492     } as VideoComment
 493   }
 494
 495   toActivityPubObject (this: MCommentAP, threadParentComments: MCommentOwner[]): VideoCommentObject {
 496     let inReplyTo: string
 497     // New thread, so in AS we reply to the video
 498     if (this.inReplyToCommentId === null) {
 499       inReplyTo = this.Video.url
 500     } else {
 501       inReplyTo = this.InReplyToVideoComment.url
 502     }
 503
 504     const tag: ActivityTagObject[] = []
 505     for (const parentComment of threadParentComments) {
 506       const actor = parentComment.Account.Actor
 507
 508       tag.push({
 509         type: 'Mention',
 510         href: actor.url,
 511         name: `@${actor.preferredUsername}@${actor.getHost()}`
 512       })
 513     }
 514
 515     return {
 516       type: 'Note' as 'Note',
 517       id: this.url,
 518       content: this.text,
 519       inReplyTo,
 520       updated: this.updatedAt.toISOString(),
 521       published: this.createdAt.toISOString(),
 522       url: this.url,
 523       attributedTo: this.Account.Actor.url,
 524       tag
 525     }
 526   }
 527 }