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, SQL, sql } from 'drizzle-orm'
import { alias } from 'drizzle-orm/mysql-core'
import crypto from 'node:crypto'
import { convertHumanTimeToSeconds } from '../../../common/functions.js'
import { currentTimeStamp } from '../../../common/shared.js'
import { CacheDecorator } from '../../../infrastructure/cache/cache.decorator.js'
import { Cache } from '../../../infrastructure/cache/cache.service.js'
import { DB_TOKEN_PROVIDER } from '../../../infrastructure/database/constants.js'
import type { DBSchema } from '../../../infrastructure/database/interfaces/database.interface.js'
import { concatDistinctObjectsInArray, dbCheckAffectedRows, dbGetInsertedId } from '../../../infrastructure/database/utils.js'
import { childPathMatch, filePathSQL, files, replacePathPrefix } from '../../files/schemas/files.schema.js'
import { shares } from '../../shares/schemas/shares.schema.js'
import { spacesRoots } from '../../spaces/schemas/spaces-roots.schema.js'
import { spaces } from '../../spaces/schemas/spaces.schema.js'
import type { UserModel } from '../../users/models/user.model.js'
import { CLIENT_TOKEN_EXPIRATION_TIME } from '../constants/auth.js'
import { SYNC_REPOSITORY } from '../constants/sync.js'
import { SyncClientPaths } from '../interfaces/sync-client-paths.interface.js'
import { SyncClientInfo } from '../interfaces/sync-client.interface.js'
import { SyncDBProps, SyncPathSettings } from '../interfaces/sync-path.interface.js'
import type { SyncClient } from '../schemas/sync-client.interface.js'
import { syncClients } from '../schemas/sync-clients.schema.js'
import type { SyncPath } from '../schemas/sync-path.interface.js'
import { syncPaths } from '../schemas/sync-paths.schema.js'
@Injectable()
export class SyncQueries {
constructor(
@Inject(DB_TOKEN_PROVIDER) private readonly db: DBSchema,
private readonly cache: Cache
) {}
async clientExistsForOwner(ownerId: number, clientId: string): Promise<boolean> {
const [client] = await this.db
.select({ id: syncClients.id })
.from(syncClients)
.where(and(eq(syncClients.ownerId, ownerId), eq(syncClients.id, clientId)))
.limit(1)
return !!client?.id
}
async getOrCreateClient(ownerId: number, clientId: string, info: SyncClientInfo, ip: string): Promise<string> {
const client = await this.getClient(clientId, ownerId)
if (client) {
if (currentTimeStamp() < client.tokenExpiration) {
return client.token
}
// renew token if it has expired
const token = crypto.randomUUID()
dbCheckAffectedRows(
await this.db
.update(syncClients)
.set({
token: token,
tokenExpiration: currentTimeStamp() + convertHumanTimeToSeconds(CLIENT_TOKEN_EXPIRATION_TIME)
})
.where(and(eq(syncClients.ownerId, ownerId), eq(syncClients.id, clientId)))
.limit(1),
1
)
return token
} else {
// create client
const token = crypto.randomUUID()
dbCheckAffectedRows(
await this.db.insert(syncClients).values({
id: clientId,
ownerId: ownerId,
token: token,
tokenExpiration: currentTimeStamp() + convertHumanTimeToSeconds(CLIENT_TOKEN_EXPIRATION_TIME),
info: info,
currentAccess: new Date(),
currentIp: ip
} as SyncClient),
1
)
return token
}
}
async getClient(clientId: string, ownerId?: number, token?: string): Promise<SyncClient | undefined> {
const where: SQL[] = [
eq(syncClients.id, clientId),
...(ownerId ? [eq(syncClients.ownerId, ownerId)] : []),
...(token ? [eq(syncClients.token, token)] : [])
]
const [client] = await this.db
.select({
id: syncClients.id,
ownerId: syncClients.ownerId,
token: syncClients.token,
tokenExpiration: syncClients.tokenExpiration,
info: syncClients.info,
enabled: syncClients.enabled,
currentAccess: syncClients.currentAccess,
currentIp: syncClients.currentIp,
lastAccess: syncClients.lastAccess,
lastIp: syncClients.lastIp,
createdAt: syncClients.createdAt
})
.from(syncClients)
.where(and(...where))
.limit(1)
return client
}
async getClients(owner: UserModel): Promise<SyncClientPaths[]> {
const clientId = owner.clientId || null
return this.db
.select({
id: syncClients.id,
tokenExpiration: syncClients.tokenExpiration,
info: syncClients.info,
enabled: syncClients.enabled,
currentAccess: syncClients.currentAccess,
currentIp: syncClients.currentIp,
lastAccess: syncClients.lastAccess,
lastIp: syncClients.lastIp,
createdAt: syncClients.createdAt,
isCurrentClient: sql<boolean>`IF (${clientId} IS NOT NULL AND ${syncClients.id} = ${clientId}, 1, 0)`.mapWith(Boolean),
paths: concatDistinctObjectsInArray(syncPaths.id, {
id: syncPaths.id,
settings: syncPaths.settings,
createdAt: syncPaths.createdAt
})
})
.from(syncClients)
.leftJoin(syncPaths, eq(syncPaths.clientId, syncClients.id))
.where(eq(syncClients.ownerId, owner.id))
.groupBy(syncClients.id)
.orderBy(desc(syncClients.currentAccess))
}
async deleteClient(ownerId: number, clientId: string) {
dbCheckAffectedRows(
await this.db
.delete(syncClients)
.where(and(eq(syncClients.ownerId, ownerId), eq(syncClients.id, clientId)))
.limit(1),
1
)
}
async renewClientTokenAndExpiration(clientId: string, token: string, expiration: number) {
dbCheckAffectedRows(await this.db.update(syncClients).set({ token: token, tokenExpiration: expiration }).where(eq(syncClients.id, clientId)), 1)
}
async updateClientInfo(client: SyncClient, info: SyncClientInfo, ip: string) {
await this.db
.update(syncClients)
.set({
lastAccess: client.currentAccess,
currentAccess: new Date(),
lastIp: client.currentIp,
currentIp: ip,
info: info
} as SyncClient)
.where(eq(syncClients.id, client.id))
}
getPaths(clientId: string): Promise<{ id: number; settings: SyncPathSettings; remotePath: string }[]> {
const shareFile: any = alias(files, 'shareFile')
const spaceRootFile: any = alias(files, 'spaceRootFile')
const shareFilePath = filePathSQL(shareFile)
const spaceRootFilePath = filePathSQL(spaceRootFile)
const spacePath = sql`IF (
${childPathMatch(files.path, spaceRootFilePath)},
${replacePathPrefix(files.path, spaceRootFilePath, spacesRoots.alias)},
${files.path}
)`
const sharePath = sql`IF (
${childPathMatch(files.path, shareFilePath)},
${replacePathPrefix(files.path, shareFilePath, shares.alias)},
${files.path}
)`
return this.db
.select({
id: syncPaths.id,
remotePath: sql<string>`
CASE WHEN ${syncPaths.ownerId} IS NOT NULL THEN CONCAT_WS('/', ${SYNC_REPOSITORY.PERSONAL}, ${filePathSQL(files)})
WHEN ${syncPaths.spaceId} IS NOT NULL THEN CONCAT_WS('/',
${SYNC_REPOSITORY.SPACES},
${spaces.alias},
CASE WHEN ${syncPaths.spaceRootId} IS NULL
THEN CONCAT_WS('/', IF (${files.path} = '.', NULL, ${files.path}), ${files.name})
ELSE
CASE WHEN ${syncPaths.fileId} IS NULL
THEN ${spacesRoots.alias}
ELSE
IF (${spacesRoots.externalPath} IS NOT NULL,
CONCAT_WS('/', ${spacesRoots.alias}, IF (${files.path} = '.', NULL, ${files.path}), ${files.name}),
CONCAT_WS('/', ${spacePath}, ${files.name})
)
END
END
)
WHEN ${syncPaths.shareId} IS NOT NULL THEN CONCAT_WS('/',
${SYNC_REPOSITORY.SHARES},
CASE WHEN ${syncPaths.fileId} IS NULL THEN ${shares.alias}
ELSE
IF (${shareFile.id} IS NOT NULL,
IF (${files.id} = ${shareFile.id}, NULL, ${sharePath}),
CONCAT_WS('/', ${shares.alias}, IF (${files.path} = '.', NULL, ${files.path}))
)
END,
IF (${files.id} = ${shareFile.id}, ${shares.name}, ${files.name})
)
ELSE NULL
END
`.as('remotePath'),
settings: syncPaths.settings
})
.from(syncPaths)
.leftJoin(files, eq(files.id, syncPaths.fileId))
.leftJoin(spaces, eq(spaces.id, syncPaths.spaceId))
.leftJoin(spacesRoots, eq(spacesRoots.id, syncPaths.spaceRootId))
.leftJoin(spaceRootFile, eq(spaceRootFile.id, spacesRoots.fileId))
.leftJoin(shares, eq(shares.id, syncPaths.shareId))
.leftJoin(shareFile, eq(shareFile.id, shares.fileId))
.where(eq(syncPaths.clientId, clientId))
}
@CacheDecorator(900)
async getPathSettings(clientId: string, pathId: number): Promise<SyncPathSettings | null> {
const [path] = await this.db
.select({
settings: syncPaths.settings
})
.from(syncPaths)
.where(and(eq(syncPaths.clientId, clientId), eq(syncPaths.id, pathId)))
.limit(1)
return path ? path.settings : null
}
async createPath(clientId: string, dbProps: SyncDBProps, settings: SyncPathSettings): Promise<SyncPath['id']> {
return dbGetInsertedId(await this.db.insert(syncPaths).values({ clientId: clientId, settings: settings, ...dbProps } as SyncPath))
}
async deletePath(clientId: string, pathId: number): Promise<void> {
dbCheckAffectedRows(await this.db.delete(syncPaths).where(and(eq(syncPaths.id, pathId), eq(syncPaths.clientId, clientId))), 1)
}
async updatePathSettings(clientId: string, pathId: number, settings: SyncPathSettings) {
dbCheckAffectedRows(
await this.db
.update(syncPaths)
.set({ settings: settings })
.where(and(eq(syncPaths.id, pathId), eq(syncPaths.clientId, clientId))),
1
)
}
clearCachePathSettings(clientId: string, pathId: number): void {
this.cache.del(this.cache.genSlugKey(this.constructor.name, this.getPathSettings.name, clientId, pathId)).catch((e: Error) => console.error(e))
}
}