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
-- DuplicateRepository.getAll
with
"duplicates" as (
select
"asset"."duplicateId",
json_agg(
"asset2"
order by
"asset"."localDateTime" asc
) as "assets"
from
"asset"
inner join lateral (
select
"asset".*,
to_json("asset_exif") as "exifInfo",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"tag"."id",
"tag"."value",
"tag"."createdAt",
"tag"."updatedAt",
"tag"."color",
"tag"."parentId"
from
"tag"
inner join "tag_asset" on "tag"."id" = "tag_asset"."tagId"
where
"tag_asset"."assetId" = "asset"."id"
) as agg
) as "tags"
from
"asset_exif"
where
"asset_exif"."assetId" = "asset"."id"
) as "asset2" on true
where
"asset"."visibility" in ('archive', 'timeline')
and "asset"."ownerId" = $1::uuid
and "asset"."duplicateId" is not null
and "asset"."deletedAt" is null
and "asset"."stackId" is null
group by
"asset"."duplicateId"
)
select
*
from
"duplicates"
where
json_array_length("assets") > $2
-- DuplicateRepository.cleanupSingletonGroups
with
"singletons" as (
select
"duplicateId"
from
"asset"
where
"ownerId" = $1::uuid
and "duplicateId" is not null
and "deletedAt" is null
and "stackId" is null
group by
"duplicateId"
having
count("id") = $2
)
update "asset"
set
"duplicateId" = $3
from
"singletons"
where
"asset"."duplicateId" = "singletons"."duplicateId"
-- DuplicateRepository.get
select
"asset"."duplicateId",
json_agg(
"asset2"
order by
"asset"."localDateTime" asc
) as "assets"
from
"asset"
inner join lateral (
select
"asset".*,
to_json("asset_exif") as "exifInfo",
(
select
coalesce(json_agg(agg), '[]')
from
(
select
"tag"."id",
"tag"."value",
"tag"."createdAt",
"tag"."updatedAt",
"tag"."color",
"tag"."parentId"
from
"tag"
inner join "tag_asset" on "tag"."id" = "tag_asset"."tagId"
where
"tag_asset"."assetId" = "asset"."id"
) as agg
) as "tags"
from
"asset_exif"
where
"asset_exif"."assetId" = "asset"."id"
) as "asset2" on true
where
"asset"."visibility" in ('archive', 'timeline')
and "asset"."ownerId" = $1::uuid
and "asset"."duplicateId" = $2::uuid
and "asset"."deletedAt" is null
and "asset"."stackId" is null
group by
"asset"."duplicateId"
-- DuplicateRepository.delete
update "asset"
set
"duplicateId" = $1
where
"ownerId" = $2
and "duplicateId" = $3
-- DuplicateRepository.deleteAll
update "asset"
set
"duplicateId" = $1
where
"ownerId" = $2
and "duplicateId" in ($3)
-- DuplicateRepository.search
begin
set
local vchordrq.probes = 1
with
"cte" as (
select
"asset"."id" as "assetId",
"asset"."duplicateId",
smart_search.embedding <=> $1 as "distance"
from
"asset"
inner join "smart_search" on "asset"."id" = "smart_search"."assetId"
where
"asset"."visibility" in ('archive', 'timeline')
and "asset"."ownerId" = any ($2::uuid[])
and "asset"."deletedAt" is null
and "asset"."type" = $3
and "asset"."id" != $4::uuid
and "asset"."stackId" is null
order by
"distance"
limit
$5
)
select
*
from
"cte"
where
"cte"."distance" <= $6
commit
-- DuplicateRepository.merge
update "asset"
set
where
(
"duplicateId" = any ($1::uuid[])
or "id" = any ($2::uuid[])
)