High performance self-hosted photo and video management solution.
TypeScript backup-toolfluttergoogle-photosgoogle-photos-alternativejavascriptmobile-appnestjsnodejsphoto-galleryphotosphotos-managementself-hostedsveltesveltekittypescriptvideos
-- NOTE: This file is auto generated by ./sql-generator
-- PersonRepository.reassignFaces
update "asset_face"
set
"personGroupId" = $1
from
"asset"
where
"asset_face"."assetId" = "asset"."id"
and "asset_face"."personGroupId" = $2
-- PersonRepository.updateGroupId
begin
update "asset_face"
set
"personGroupId" = $1
from
"asset"
where
"asset_face"."assetId" = "asset"."id"
and "asset_face"."personGroupId" = $2
and "asset"."ownerId" = $3
rollback
-- PersonRepository.unassignFaces
update "asset_face"
set
"personGroupId" = $1
from
"asset"
inner join "user" on "user"."id" = "asset"."ownerId"
where
"asset_face"."assetId" = "asset"."id"
and "asset_face"."sourceType" = $2
and "user"."clusterGroupId" = $3
-- PersonRepository.delete
delete from "person"
where
"ownerId" = $1
and "person"."personGroupId" in ($2)
returning
"personGroupId",
"ownerId",
"thumbnailPath"
-- PersonRepository.deleteGroups
delete from "person_group"
where
"person_group"."id" in ($1)
-- PersonRepository.deleteEmptyGroups
delete from "person_group"
where
not exists (
select
"person"."personGroupId"
from
"person"
where
"person"."personGroupId" = "person_group"."id"
)
-- PersonRepository.deleteOrphanedClusterGroups
delete from "cluster_group"
where
not exists (
select
"user"."id"
from
"user"
where
"user"."clusterGroupId" = "cluster_group"."id"
)
-- PersonRepository.getAllFaces
select
"asset_face".*
from
"asset_face"
inner join "asset" on "asset"."id" = "asset_face"."assetId"
inner join "user" on "user"."id" = "asset"."ownerId"
where
"asset_face"."personGroupId" is null
and "asset_face"."sourceType" = $1
and "user"."clusterGroupId" = $2
and "asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
-- PersonRepository.forBirthdayMemories
select
"person"."personGroupId",
"person"."name",
date_part('year', person."birthDate")::int as "birthYear",
date_part('month', person."birthDate")::int as "birthMonth",
date_part('day', person."birthDate")::int as "birthDay"
from
"person"
where
"person"."ownerId" = $1
and "person"."isHidden" = $2
and "person"."name" != $3
and "person"."birthDate" is not null
and (
date_part('month', person."birthDate")::int = $4
and date_part('day', person."birthDate")::int = $5
)
and date_part('year', person."birthDate")::int < $6
-- PersonRepository.forBirthdayMemories (leap day fallback)
select
"person"."personGroupId",
"person"."name",
date_part('year', person."birthDate")::int as "birthYear",
date_part('month', person."birthDate")::int as "birthMonth",
date_part('day', person."birthDate")::int as "birthDay"
from
"person"
where
"person"."ownerId" = $1
and "person"."isHidden" = $2
and "person"."name" != $3
and "person"."birthDate" is not null
and (
(
date_part('month', person."birthDate")::int = $4
and date_part('day', person."birthDate")::int = $5
)
or (
date_part('month', person."birthDate")::int = $6
and date_part('day', person."birthDate")::int = $7
)
)
and date_part('year', person."birthDate")::int < $8
-- PersonRepository.getFileSamples
select
"ownerId",
"personGroupId",
"thumbnailPath"
from
"person"
where
"thumbnailPath" != ''
limit
3
-- PersonRepository.getAllForUser
with
"faces" as (
select
"visible"."personGroupId",
count(*) as "faceCount"
from
(
select
"asset_face"."personGroupId"
from
"asset_face"
where
"asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
and "asset_face"."assetVisibility" = 'timeline'
and "asset_face"."assetDeletedAt" is null
and "asset_face"."personGroupId" in (
select
"person"."personGroupId"
from
"person"
where
"person"."ownerId" = $1
)
and "asset_face"."ownerId" = any ($2::uuid[])
union all
select
"asset_face"."personGroupId"
from
"asset_face"
where
"asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
and "asset_face"."assetVisibility" = 'timeline'
and "asset_face"."assetDeletedAt" is null
and "asset_face"."personGroupId" in (
select
"person"."personGroupId"
from
"person"
where
"person"."ownerId" = $3
)
and not "asset_face"."ownerId" = any ($4::uuid[])
and exists (
select
1 as "exists"
from
"album_asset"
inner join "album" on "album"."id" = "album_asset"."albumId"
and "album"."deletedAt" is null
inner join "album_user" on "album_user"."albumId" = "album"."id"
and "album_user"."userId" = $5
where
"album_asset"."assetId" = "asset_face"."assetId"
)
) as "visible"
group by
"visible"."personGroupId"
),
"people" as (
select
"person".*,
"faces"."faceCount"
from
"person"
inner join "faces" on "faces"."personGroupId" = "person"."personGroupId"
where
"person"."ownerId" = $6
and (
"person"."name" != $7
or "faces"."faceCount" >= coalesce(
(
select
cast(value -> 'people' ->> 'minimumFaces' as integer) as "value"
from
"user_metadata"
where
"userId" = $8
and "key" = 'preferences'
),
3
)
)
),
"page" as (
select
"people".*
from
"people"
where
"people"."isHidden" = $9
order by
"people"."isHidden" asc,
"people"."isFavorite" desc,
"people"."name" = '',
"people"."faceCount" desc,
"people"."name",
"people"."createdAt",
"people"."personGroupId"
limit
$10
offset
$11
)
select
"counts"."total",
"counts"."hidden",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person".*,
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person_user"."sharedById",
"person_user"."role",
"other"."name",
"other"."birthDate"
from
"person" as "other"
inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
and "person_user"."sharedById" = "other"."ownerId"
and "person_user"."sharedWithId" = $12
where
"other"."personGroupId" = "person"."personGroupId"
) as agg
) as "otherPeople",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedById"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedWithId" = $13
order by
"user"."name"
) as agg
) as "sharedBy",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedWithId"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = $14
order by
"user"."name"
) as agg
) as "sharedWith"
from
"page" as "person"
) as agg
) as "people"
from
(
select
count(*) as "total",
count(*) filter (
where
"people"."isHidden" = $15
) as "hidden"
from
"people"
) as "counts"
-- PersonRepository.getAllForUser (closest person)
with
"faces" as (
select
"visible"."personGroupId",
count(*) as "faceCount"
from
(
select
"asset_face"."personGroupId"
from
"asset_face"
where
"asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
and "asset_face"."assetVisibility" = 'timeline'
and "asset_face"."assetDeletedAt" is null
and "asset_face"."personGroupId" in (
select
"person"."personGroupId"
from
"person"
where
"person"."ownerId" = $1
)
and "asset_face"."ownerId" = any ($2::uuid[])
union all
select
"asset_face"."personGroupId"
from
"asset_face"
where
"asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
and "asset_face"."assetVisibility" = 'timeline'
and "asset_face"."assetDeletedAt" is null
and "asset_face"."personGroupId" in (
select
"person"."personGroupId"
from
"person"
where
"person"."ownerId" = $3
)
and not "asset_face"."ownerId" = any ($4::uuid[])
and exists (
select
1 as "exists"
from
"album_asset"
inner join "album" on "album"."id" = "album_asset"."albumId"
and "album"."deletedAt" is null
inner join "album_user" on "album_user"."albumId" = "album"."id"
and "album_user"."userId" = $5
where
"album_asset"."assetId" = "asset_face"."assetId"
)
) as "visible"
group by
"visible"."personGroupId"
),
"people" as (
select
"person".*,
"faces"."faceCount"
from
"person"
inner join "faces" on "faces"."personGroupId" = "person"."personGroupId"
where
"person"."ownerId" = $6
and (
"person"."name" != $7
or "faces"."faceCount" >= coalesce(
(
select
cast(value -> 'people' ->> 'minimumFaces' as integer) as "value"
from
"user_metadata"
where
"userId" = $8
and "key" = 'preferences'
),
3
)
)
),
"page" as (
select
"people".*
from
"people"
where
"people"."isHidden" = $9
order by
"people"."isHidden" asc,
"people"."isFavorite" desc,
(
select
"face_search"."embedding"
from
"face_search"
where
"face_search"."faceId" = "people"."faceAssetId"
) <=> (
select
"face_search"."embedding"
from
"face_search"
inner join "person" as "closest" on "closest"."faceAssetId" = "face_search"."faceId"
where
"closest"."ownerId" = $10
and "closest"."personGroupId" = $11
),
"people"."name" = '',
"people"."faceCount" desc,
"people"."name",
"people"."createdAt",
"people"."personGroupId"
limit
$12
offset
$13
)
select
"counts"."total",
"counts"."hidden",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person".*,
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person_user"."sharedById",
"person_user"."role",
"other"."name",
"other"."birthDate"
from
"person" as "other"
inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
and "person_user"."sharedById" = "other"."ownerId"
and "person_user"."sharedWithId" = $14
where
"other"."personGroupId" = "person"."personGroupId"
) as agg
) as "otherPeople",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedById"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedWithId" = $15
order by
"user"."name"
) as agg
) as "sharedBy",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedWithId"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = $16
order by
"user"."name"
) as agg
) as "sharedWith"
from
"page" as "person"
) as agg
) as "people"
from
(
select
count(*) as "total",
count(*) filter (
where
"people"."isHidden" = $17
) as "hidden"
from
"people"
) as "counts"
-- PersonRepository.getAllWithoutFaces
select
"person".*
from
"person"
left join "asset_face" on "asset_face"."personGroupId" = "person"."personGroupId"
where
"asset_face"."deletedAt" is null
and (
"asset_face"."isVisible" is null
or "asset_face"."isVisible" = $1
)
group by
"person"."ownerId",
"person"."personGroupId"
having
count("asset_face"."assetId") = $2
-- PersonRepository.getFaces
select
"asset_face".*,
(
select
to_json(obj)
from
(
select
"person".*,
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person_user"."sharedById",
"person_user"."role",
"other"."name",
"other"."birthDate"
from
"person" as "other"
inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
and "person_user"."sharedById" = "other"."ownerId"
and "person_user"."sharedWithId" = $1
where
"other"."personGroupId" = "person"."personGroupId"
) as agg
) as "otherPeople",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedById"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedWithId" = $2
order by
"user"."name"
) as agg
) as "sharedBy",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedWithId"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = $3
order by
"user"."name"
) as agg
) as "sharedWith"
from
"person"
where
"person"."personGroupId" = "asset_face"."personGroupId"
and "person"."ownerId" = $4
) as obj
) as "person"
from
"asset_face"
where
"asset_face"."assetId" = $5
and "asset_face"."deletedAt" is null
and "asset_face"."isVisible" = $6
order by
"asset_face"."boundingBoxX1" asc
-- PersonRepository.getFaceById
select
"asset_face".*,
(
select
to_json(obj)
from
(
select
"person".*,
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person_user"."sharedById",
"person_user"."role",
"other"."name",
"other"."birthDate"
from
"person" as "other"
inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
and "person_user"."sharedById" = "other"."ownerId"
and "person_user"."sharedWithId" = $1
where
"other"."personGroupId" = "person"."personGroupId"
) as agg
) as "otherPeople",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedById"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedWithId" = $2
order by
"user"."name"
) as agg
) as "sharedBy",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedWithId"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = $3
order by
"user"."name"
) as agg
) as "sharedWith"
from
"person"
where
"person"."personGroupId" = "asset_face"."personGroupId"
and "person"."ownerId" = $4
) as obj
) as "person"
from
"asset_face"
where
"asset_face"."id" = $5
and "asset_face"."deletedAt" is null
-- PersonRepository.getFaceForFacialRecognitionJob
select
"asset_face"."id",
"asset_face"."personGroupId",
"asset_face"."sourceType",
(
select
to_json(obj)
from
(
select
"asset"."ownerId",
"asset"."visibility",
"asset"."fileCreatedAt",
"user"."clusterGroupId"
from
"asset"
inner join "user" on "user"."id" = "asset"."ownerId"
where
"asset"."id" = "asset_face"."assetId"
) as obj
) as "asset",
(
select
to_json(obj)
from
(
select
"face_search".*
from
"face_search"
where
"face_search"."faceId" = "asset_face"."id"
) as obj
) as "faceSearch"
from
"asset_face"
where
"asset_face"."id" = $1
and "asset_face"."deletedAt" is null
-- PersonRepository.getDataForThumbnailGenerationJob
select
"person"."ownerId",
"asset_face"."boundingBoxX1" as "x1",
"asset_face"."boundingBoxY1" as "y1",
"asset_face"."boundingBoxX2" as "x2",
"asset_face"."boundingBoxY2" as "y2",
"asset_face"."imageWidth" as "oldWidth",
"asset_face"."imageHeight" as "oldHeight",
"asset"."type",
"asset"."originalPath",
"asset_exif"."orientation" as "exifOrientation",
(
select
"asset_file"."path"
from
"asset_file"
where
"asset_file"."assetId" = "asset"."id"
and "asset_file"."type" = 'preview'
and "asset_file"."isEdited" = false
) as "previewPath"
from
"person"
inner join "asset_face" on "asset_face"."id" = "person"."faceAssetId"
inner join "asset" on "asset_face"."assetId" = "asset"."id"
left join "asset_exif" on "asset_exif"."assetId" = "asset"."id"
where
"person"."ownerId" = $1
and "person"."personGroupId" = $2
and "asset_face"."deletedAt" is null
-- PersonRepository.reassignFace
update "asset_face"
set
"personGroupId" = $1
where
"asset_face"."id" = $2
-- PersonRepository.getByGroupId
select
"person".*,
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person_user"."sharedById",
"person_user"."role",
"other"."name",
"other"."birthDate"
from
"person" as "other"
inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
and "person_user"."sharedById" = "other"."ownerId"
and "person_user"."sharedWithId" = $1
where
"other"."personGroupId" = "person"."personGroupId"
) as agg
) as "otherPeople",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedById"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedWithId" = $2
order by
"user"."name"
) as agg
) as "sharedBy",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedWithId"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = $3
order by
"user"."name"
) as agg
) as "sharedWith"
from
"person"
where
"person"."personGroupId" = $4
and "person"."ownerId" = $5
-- PersonRepository.getForThumbnail
select
(
select
"person"."thumbnailPath"
from
"person"
where
"person"."personGroupId" = "person_group"."id"
and "person"."ownerId" = $1
) as "thumbnailPath",
(
select
"person"."thumbnailPath"
from
"person"
inner join "person_user" on "person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = "person"."ownerId"
and "person_user"."sharedWithId" = $2
where
"person"."personGroupId" = "person_group"."id"
and "person"."thumbnailPath" != $3
limit
$4
) as "sharedThumbnailPath"
from
"person_group"
where
"person_group"."id" = $5
-- PersonRepository.getByName
select
"person".*,
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person_user"."sharedById",
"person_user"."role",
"other"."name",
"other"."birthDate"
from
"person" as "other"
inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
and "person_user"."sharedById" = "other"."ownerId"
and "person_user"."sharedWithId" = $1
where
"other"."personGroupId" = "person"."personGroupId"
) as agg
) as "otherPeople",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedById"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedWithId" = $2
order by
"user"."name"
) as agg
) as "sharedBy",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedWithId"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = $3
order by
"user"."name"
) as agg
) as "sharedWith"
from
"person"
where
"person"."ownerId" = $4
and f_unaccent ("person"."name") %> (
select
f_unaccent ($5)
where
set_config('pg_trgm.word_similarity_threshold', '0.5', true) is not null
)
order by
f_unaccent ("person"."name") <->>> f_unaccent ($6)
limit
$7
-- PersonRepository.getDistinctNames
select distinct
on (lower("person"."name")) "person"."personGroupId",
"person"."name"
from
"person"
where
(
"person"."ownerId" = $1
and "person"."name" != $2
)
-- PersonRepository.getStatistics
select
count(distinct "visible"."assetId") as "assets"
from
(
select
"asset_face"."assetId"
from
"asset_face"
where
"asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
and "asset_face"."assetVisibility" = 'timeline'
and "asset_face"."assetDeletedAt" is null
and "asset_face"."personGroupId" = $1
and "asset_face"."ownerId" = any ($2::uuid[])
union all
select
"asset_face"."assetId"
from
"asset_face"
where
"asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
and "asset_face"."assetVisibility" = 'timeline'
and "asset_face"."assetDeletedAt" is null
and "asset_face"."personGroupId" = $3
and not "asset_face"."ownerId" = any ($4::uuid[])
and exists (
select
1 as "exists"
from
"album_asset"
inner join "album" on "album"."id" = "album_asset"."albumId"
and "album"."deletedAt" is null
inner join "album_user" on "album_user"."albumId" = "album"."id"
and "album_user"."userId" = $5
where
"album_asset"."assetId" = "asset_face"."assetId"
)
) as "visible"
-- PersonRepository.createGroup
insert into
"person_group" ("clusterGroupId")
select
"user"."clusterGroupId"
from
"user"
where
"user"."id" = $1
returning
*
-- PersonRepository.reassignCluster
begin
update "person_group"
set
"clusterGroupId" = $1
where
"person_group"."id" in (
select
"person"."personGroupId"
from
"person"
where
"person"."ownerId" = $2
)
and not exists (
select
"person"."personGroupId"
from
"person"
where
"person"."personGroupId" = "person_group"."id"
and "person"."ownerId" != $3
)
with
"shared" as (
select distinct
"person"."personGroupId" as "oldId"
from
"person"
where
"person"."ownerId" = $1
and exists (
select
"other"."personGroupId"
from
"person" as "other"
where
"other"."personGroupId" = "person"."personGroupId"
and "other"."ownerId" != $2
)
),
"mapping" as materialized (
select
"shared"."oldId",
uuid_generate_v4 () as "newId"
from
"shared"
),
"created" as (
insert into
"person_group" ("id", "clusterGroupId")
select
"mapping"."newId",
$3 as "clusterGroupId"
from
"mapping"
)
select
"mapping"."oldId",
"mapping"."newId"
from
"mapping"
commit
-- PersonRepository.createGroups
insert into
"person_group" (
"0",
"1",
"2",
"3",
"4",
"5",
"6",
"7",
"8",
"9",
"10",
"11",
"12",
"13",
"14",
"15",
"16",
"17",
"18",
"19",
"20",
"21",
"22",
"23",
"24",
"25",
"26",
"27",
"28",
"29",
"30",
"31",
"32",
"33",
"34",
"35"
)
values
(
$1,
$2,
$3,
$4,
$5,
$6,
$7,
$8,
$9,
$10,
$11,
$12,
$13,
$14,
$15,
$16,
$17,
$18,
$19,
$20,
$21,
$22,
$23,
$24,
$25,
$26,
$27,
$28,
$29,
$30,
$31,
$32,
$33,
$34,
$35,
$36
)
returning
*
-- PersonRepository.refreshFaces
with
"added_embeddings" as (
insert into
"face_search" ("faceId", "embedding")
values
($1, $2)
)
select
from
(
select
1
) as "dummy"
-- PersonRepository.updateForWritableOwners
update "person"
set
"name" = $1
where
"person"."personGroupId" = $2
and "person"."ownerId" in (
select
"person_user"."sharedById"
from
"person_user"
where
"person_user"."personGroupId" = $3
and "person_user"."sharedWithId" = $4
and "person_user"."role" in ($5, $6)
)
-- PersonRepository.getFacesByIds
select
"asset_face".*,
(
select
to_json(obj)
from
(
select
"person".*,
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"person_user"."sharedById",
"person_user"."role",
"other"."name",
"other"."birthDate"
from
"person" as "other"
inner join "person_user" on "person_user"."personGroupId" = "other"."personGroupId"
and "person_user"."sharedById" = "other"."ownerId"
and "person_user"."sharedWithId" = $1
where
"other"."personGroupId" = "person"."personGroupId"
) as agg
) as "otherPeople",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedById"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedWithId" = $2
order by
"user"."name"
) as agg
) as "sharedBy",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"id",
"name",
"email",
"avatarColor",
"profileImagePath",
"profileChangedAt",
"person_user"."role"
from
"person_user"
inner join "user" on "user"."id" = "person_user"."sharedWithId"
and "user"."deletedAt" is null
where
"person_user"."personGroupId" = "person"."personGroupId"
and "person_user"."sharedById" = $3
order by
"user"."name"
) as agg
) as "sharedWith"
from
"person"
where
"person"."personGroupId" = "asset_face"."personGroupId"
and "person"."ownerId" = $4
) as obj
) as "person"
from
"asset_face"
where
"asset_face"."assetId" in ($5)
and "asset_face"."personGroupId" in ($6)
and "asset_face"."deletedAt" is null
-- PersonRepository.getRandomFace
select
"asset_face".*
from
"asset_face"
where
"asset_face"."personGroupId" = $1
and "asset_face"."deletedAt" is null
and "asset_face"."isVisible" is true
-- PersonRepository.getLatestFaceDate
select
max("asset_job_status"."facesRecognizedAt")::text as "latestDate"
from
"asset_job_status"
-- PersonRepository.deleteAssetFace
delete from "asset_face"
where
"asset_face"."id" = $1
-- PersonRepository.softDeleteAssetFaces
update "asset_face"
set
"deletedAt" = $1
where
"asset_face"."id" = $2
-- PersonRepository.getForFeatureFaceUpdate
select
"asset_face"."id"
from
"asset_face"
inner join "asset" on "asset"."id" = "asset_face"."assetId"
and "asset"."isOffline" = $1
where
"asset_face"."assetId" = $2
and "asset_face"."personGroupId" = $3
and "asset_face"."deletedAt" is null
-- PersonRepository.getForMergePerson
select
"person".*
from
"person"
where
"person"."personGroupId" in ($1)