Skip to content

High performance self-hosted photo and video management solution.

3.9 KB
Raw Blame
-- 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[])
  )