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