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