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