Sync-in · Sovereign platform for file storage, sharing, synchronization, and collaboration.
TypeScript cloud-alternativecollaborationcollaborative-editingdropboxfile-serverfile-sharingfile-storagefile-syncfull-text-searchhomelabnextcloudon-premiseopen-sourceprivacy-firstprivate-cloudrealtimesecureself-hostedstorage-serverwebdav
import { Inject, Injectable } from '@nestjs/common'
import { and, desc, eq, getTableColumns, inArray, isNotNull, isNull, ne, or, SQL, sql } from 'drizzle-orm'
import { alias, union } from 'drizzle-orm/mysql-core'
import { DB_TOKEN_PROVIDER } from '../../../infrastructure/database/constants.js'
import type { DBSchema } from '../../../infrastructure/database/interfaces/database.interface.js'
import { dbCheckAffectedRows, dbGetInsertedId } from '../../../infrastructure/database/utils.js'
import { childPathMatch, filePathSQL, files, replacePathPrefix } from '../../files/schemas/files.schema.js'
import { UserMailNotification } from '../../notifications/interfaces/user-mail-notification.interface.js'
import { shares } from '../../shares/schemas/shares.schema.js'
import { externalShareScopeSQL } from '../../shares/utils/external-share-scope.sql.js'
import { SharesQueries } from '../../shares/services/shares-queries.service.js'
import { SPACE_ALIAS, SPACE_REPOSITORY } from '../../spaces/constants/spaces.js'
import { spacesRoots } from '../../spaces/schemas/spaces-roots.schema.js'
import { spaces } from '../../spaces/schemas/spaces.schema.js'
import { SpacesQueries } from '../../spaces/services/spaces-queries.service.js'
import { USER_PERMISSION } from '../../users/constants/user.js'
import { UserModel } from '../../users/models/user.model.js'
import { userFullNameSQL, users } from '../../users/schemas/users.schema.js'
import { COMMENTS_RECENTS_MAX_LIMIT } from '../constants/recents.js'
import { CommentRecent } from '../interfaces/comment-recent.interface.js'
import type { Comment } from '../schemas/comment.interface.js'
import { comments } from '../schemas/comments.schema.js'
@Injectable()
export class CommentsQueries {
constructor(
@Inject(DB_TOKEN_PROVIDER) private readonly db: DBSchema,
private readonly spacesQueries: SpacesQueries,
private readonly sharesQueries: SharesQueries
) {}
getComments(userId: number, isFileOwner: boolean, fromFileId?: number | null, fromCommentId?: number | null): Promise<Comment[]> {
let where: SQL
if (fromFileId) {
where = eq(comments.fileId, fromFileId)
} else if (fromCommentId) {
where = eq(comments.id, fromCommentId)
} else {
throw Error('fromFileId or fromCommentId must be provided')
}
const query = this.db
.select({
...getTableColumns(comments),
author: { login: users.login, fullName: userFullNameSQL(users), email: users.email, isAuthor: eq(users.id, userId).mapWith(Boolean) },
isFileOwner: sql`${+isFileOwner}`.mapWith(Boolean)
})
.from(comments)
.innerJoin(users, eq(users.id, comments.userId))
.where(where)
.orderBy(desc(comments.id))
return fromCommentId ? query.limit(1) : query
}
async createComment(userId: number, fileId: number, content: string): Promise<Comment['id']> {
return dbGetInsertedId(await this.db.insert(comments).values({ userId: userId, fileId: fileId, content: content } as Comment))
}
async updateComment(userId: number, commentId: number, fileId: number, content: string): Promise<boolean> {
return dbCheckAffectedRows(
await this.db
.update(comments)
.set({ content: content } as Comment)
.where(and(eq(comments.userId, userId), eq(comments.id, commentId), eq(comments.fileId, fileId)))
.limit(1),
1,
false
)
}
async deleteComment(userId: number, commentId: number, fileId: number, isFileOwner: boolean): Promise<boolean> {
return dbCheckAffectedRows(
await this.db
.delete(comments)
.where(and(or(eq(sql`${+isFileOwner}`, 1), eq(comments.userId, userId)), eq(comments.id, commentId), eq(comments.fileId, fileId)))
.limit(1),
1,
false
)
}
membersToNotify(fromUserId: number, fileId: number): Promise<UserMailNotification[]> {
/* lists the owner of the file and the users who have commented on it */
const select: Record<keyof UserMailNotification, any> = {
id: users.id,
email: users.email,
language: users.language,
notification: users.notification
}
const fromComments = this.db
.select(select)
.from(comments)
.innerJoin(users, and(eq(users.id, comments.userId), ne(users.id, fromUserId)))
.where(eq(comments.fileId, fileId))
const fromFile = this.db
.select(select)
.from(files)
.innerJoin(users, and(eq(users.id, files.ownerId), ne(users.id, fromUserId)))
.where(eq(files.id, fileId))
return union(fromComments, fromFile) as any
}
getRecentsFromShares(userId: number, shareIds: number[], limit: number) {
const shareFile: any = alias(files, 'shareFile')
const shareFilePath = filePathSQL(shareFile)
const externalShareTargets = this.db
.select({ id: shares.id, parentId: shares.parentId, externalPath: shares.externalPath })
.from(shares)
.where(and(isNull(shares.fileId), isNotNull(shares.externalPath), inArray(shares.id, shareIds)))
const externalShareScope = externalShareScopeSQL(externalShareTargets, 'commentExternalShareScope', {
oneTargetPerStorage: true
})
return this.db
.select({
id: comments.id,
content: comments.content,
modifiedAt: comments.modifiedAt,
author: { login: users.login, fullName: userFullNameSQL(users).as('fullName'), email: users.email },
file: {
name: sql<string>`IF (${files.id} = ${shareFile.id}, ${shares.name}, ${files.name})`.as('name'),
path: sql<string>`
CONCAT_WS('/', ${SPACE_REPOSITORY.SHARES},
IF (${shareFile.id} IS NOT NULL,
IF (${files.id} = ${shareFile.id}, NULL, ${replacePathPrefix(files.path, shareFilePath, shares.alias)}),
CONCAT_WS('/', ${shares.alias}, IF (${files.path} = '.', NULL, ${files.path}))
)
)`.as('path'),
mime: files.mime,
inTrash: sql<number>`0`.as('inTrash'),
fromSpace: sql<number>`0`.as('fromSpace'),
fromShare: sql<number>`1`.as('fromShare'),
displayRootName: shares.name
}
} satisfies Record<keyof CommentRecent, any>)
.from(shares)
.leftJoin(shareFile, eq(shareFile.id, shares.fileId))
.leftJoin(spaces, eq(spaces.id, shareFile.spaceId))
.leftJoin(spacesRoots, eq(spacesRoots.spaceId, spaces.id))
.leftJoin(externalShareScope.table, eq(externalShareScope.targetShareId, shares.id))
.leftJoin(
files,
or(
// file linked to the share
eq(files.id, shareFile.id),
// all files with an external share id
and(isNull(shareFile.id), eq(files.shareExternalId, externalShareScope.storageShareId)),
// all files under the share
and(
isNotNull(shareFile.id),
eq(shareFile.isDir, true),
// A file moved to trash leaves the storage scope of its former share root.
eq(files.inTrash, shareFile.inTrash),
sql`${files.spaceId} <=> ${shareFile.spaceId}`,
sql`${files.ownerId} <=> ${shareFile.ownerId}`,
sql`${files.spaceExternalRootId} <=> ${shareFile.spaceExternalRootId}`,
sql`${files.shareExternalId} <=> ${shareFile.shareExternalId}`,
childPathMatch(files.path, shareFilePath)
)
)
)
.innerJoin(comments, and(eq(comments.fileId, files.id), ne(comments.userId, userId)))
.innerJoin(users, eq(users.id, comments.userId))
.where(inArray(shares.id, shareIds))
.groupBy(comments.id)
.orderBy(desc(comments.id))
.limit(limit)
}
getRecentsFromPersonal(userId: number, limit: number) {
return this.db
.select({
id: comments.id,
content: comments.content,
modifiedAt: comments.modifiedAt,
author: { login: users.login, fullName: userFullNameSQL(users).as('fullName'), email: users.email },
file: {
name: files.name,
path: sql<string>`
CONCAT_WS('/',
IF (${files.inTrash} = 0, ${SPACE_REPOSITORY.FILES}, ${SPACE_REPOSITORY.TRASH}),
${SPACE_ALIAS.PERSONAL},
IF (${files.path} = '.', NULL, ${files.path})
)`.as('path'),
mime: files.mime,
inTrash: sql<number>`${files.inTrash}`.as('inTrash'),
fromSpace: sql<number>`0`.as('fromSpace'),
fromShare: sql<number>`0`.as('fromShare')
}
} satisfies Record<keyof CommentRecent, any>)
.from(files)
.innerJoin(comments, and(eq(comments.fileId, files.id), ne(comments.userId, userId)))
.innerJoin(users, eq(users.id, comments.userId))
.where(eq(files.ownerId, userId))
.groupBy(comments.id)
.orderBy(desc(comments.id))
.limit(limit)
}
getRecentsFromSpaces(userId: number, spaceIds: number[], limit: number) {
const spaceRootFile: any = alias(files, 'spaceRootFile')
const spaceRootFilePath = filePathSQL(spaceRootFile)
const fileFromSpaceRoot = or(
eq(files.id, spaceRootFile.id),
and(
isNotNull(spaceRootFile.id),
eq(spaceRootFile.isDir, true),
// A file moved to trash leaves the storage scope of its former anchored root.
eq(files.inTrash, spaceRootFile.inTrash),
sql`${files.ownerId} <=> ${spaceRootFile.ownerId}`,
childPathMatch(files.path, spaceRootFilePath)
)
)
return this.db
.select({
id: comments.id,
content: comments.content,
modifiedAt: comments.modifiedAt,
author: { login: users.login, fullName: userFullNameSQL(users).as('fullName'), email: users.email },
file: {
name: sql<string>`IF (${files.id} = ${spacesRoots.fileId}, ${spacesRoots.name}, ${files.name})`.as('name'),
path: sql<string>`
CONCAT_WS('/',
IF (${files.inTrash} = 0, ${SPACE_REPOSITORY.FILES}, ${SPACE_REPOSITORY.TRASH}),
IF (${files.ownerId} = ${userId}, ${SPACE_ALIAS.PERSONAL}, ${spaces.alias}),
IF (${fileFromSpaceRoot},
IF (${files.id} = ${spaceRootFile.id}, NULL, IF (${files.path} = '.', NULL, ${replacePathPrefix(files.path, spaceRootFilePath, spacesRoots.alias)})),
NULLIF(CONCAT_WS('/', IF (${files.spaceExternalRootId} = ${spacesRoots.id}, ${spacesRoots.alias}, NULL), IF (${files.path} = '.', NULL, ${files.path})), '')
)
)`.as('path'),
mime: files.mime,
inTrash: sql<number>`${files.inTrash}`.as('inTrash'),
fromSpace: sql<number>`IF (${files.ownerId} = ${userId}, 0, 1)`.as('fromSpace'),
fromShare: sql<number>`0`.as('fromShare'),
displayRootName: sql<string>`IF (${files.ownerId} = ${userId}, NULL, ${spaces.name})`.as('displayRootName')
}
} satisfies Record<keyof CommentRecent, any>)
.from(spaces)
.leftJoin(spacesRoots, eq(spacesRoots.spaceId, spaces.id))
.leftJoin(spaceRootFile, eq(spaceRootFile.id, spacesRoots.fileId))
.leftJoin(
files,
or(
// all files from spaces
eq(files.spaceId, spaces.id),
// all files from or under the space roots
fileFromSpaceRoot
)
)
.innerJoin(comments, and(eq(comments.fileId, files.id), ne(comments.userId, userId)))
.innerJoin(users, eq(users.id, comments.userId))
.where(inArray(spaces.id, spaceIds))
.groupBy(comments.id)
.orderBy(desc(comments.id))
.limit(limit)
}
async getRecentsFromUser(user: UserModel, limit: number): Promise<CommentRecent[]> {
const recentsLimit = Math.min(limit, COMMENTS_RECENTS_MAX_LIMIT)
const hasPersonal = user.havePermission(USER_PERMISSION.PERSONAL_SPACE)
const [spaces, shares] = await Promise.all([
user.havePermission(USER_PERMISSION.SPACES) ? this.spacesQueries.spaceIdentities(user.id) : Promise.resolve([]),
user.havePermission(USER_PERMISSION.SHARES) ? this.sharesQueries.shareIdentities(user.id, +user.isAdmin) : Promise.resolve([])
])
const spaceIds = spaces.map(({ id }) => id)
const shareIds = shares.map(({ id }) => id)
const hasSpaces = spaceIds.length > 0
const hasShares = shareIds.length > 0
const sourceCount = +hasPersonal + +hasSpaces + +hasShares
const sourceLimit = sourceCount > 1 ? recentsLimit * 2 : recentsLimit
const sourceRecents = await Promise.all([
hasPersonal ? this.getRecentsFromPersonal(user.id, sourceLimit) : Promise.resolve([]),
hasSpaces ? this.getRecentsFromSpaces(user.id, spaceIds, sourceLimit) : Promise.resolve([]),
hasShares ? this.getRecentsFromShares(user.id, shareIds, sourceLimit) : Promise.resolve([])
])
const recents = sourceRecents.flat().sort((a, b) => b.id - a.id) as CommentRecent[]
return Array.from(new Map(recents.map((r) => [r.id, r])).values()).slice(0, recentsLimit)
}
}