Skip to content

High performance self-hosted photo and video management solution.

14.8 KB
Raw Blame
-- NOTE: This file is auto generated by ./sql-generator

-- AssetRepository.upsertExif
insert into
  "asset_exif" ("dateTimeOriginal", "lockedProperties")
values
  ($1, $2)
on conflict ("assetId") do update
set
  "dateTimeOriginal" = "excluded"."dateTimeOriginal",
  "lockedProperties" = nullif(
    array(
      select distinct
        unnest("asset_exif"."lockedProperties" || $3)
    ),
    '{}'
  )

-- AssetRepository.updateAllExif
update "asset_exif"
set
  "model" = $1,
  "lockedProperties" = nullif(
    array(
      select distinct
        unnest("asset_exif"."lockedProperties" || $2)
    ),
    '{}'
  )
where
  "assetId" in ($3)

-- AssetRepository.updateDateTimeOriginal
update "asset_exif"
set
  "dateTimeOriginal" = "dateTimeOriginal" + $1::interval,
  "timeZone" = $2,
  "lockedProperties" = nullif(
    array(
      select distinct
        unnest("asset_exif"."lockedProperties" || $3)
    ),
    '{}'
  )
where
  "assetId" in ($4)
returning
  "assetId",
  "dateTimeOriginal",
  "timeZone"

-- AssetRepository.unlockProperties
update "asset_exif"
set
  "lockedProperties" = nullif(
    array(
      select distinct
        property
      from
        unnest("asset_exif"."lockedProperties") property
      where
        not property = any ($1)
    ),
    '{}'
  )
where
  "assetId" = $2

-- AssetRepository.getMetadata
select
  "key",
  "value",
  "updatedAt"
from
  "asset_metadata"
where
  "assetId" = $1

-- AssetRepository.getMetadataByKey
select
  "key",
  "value",
  "updatedAt"
from
  "asset_metadata"
where
  "assetId" = $1
  and "key" = $2

-- AssetRepository.deleteMetadataByKey
delete from "asset_metadata"
where
  "assetId" = $1
  and "key" = $2

-- AssetRepository.deleteBulkMetadata
begin
delete from "asset_metadata"
where
  "assetId" = $1
  and "key" = $2
commit

-- AssetRepository.getByDayOfYear
with
  "res" as (
    with
      "today" as (
        select
          make_date(year::int, $1::int, $2::int) as "date"
        from
          generate_series(
            (
              select
                date_part(
                  'year',
                  min(("localDateTime" at time zone 'UTC')::date)
                )::int
              from
                asset
            ),
            $3
          ) as "year"
      )
    select
      "a".*
    from
      "today"
      inner join lateral (
        select
          "asset"."id",
          "asset"."localDateTime"
        from
          "asset"
          inner join "asset_job_status" on "asset"."id" = "asset_job_status"."assetId"
        where
          (asset."localDateTime" at time zone 'UTC')::date = today.date
          and "asset"."ownerId" = any ($4::uuid[])
          and "asset"."visibility" = $5
          and exists (
            select
            from
              "asset_file"
            where
              "assetId" = "asset"."id"
              and "asset_file"."type" = $6
          )
          and "asset"."deletedAt" is null
        order by
          (asset."localDateTime" at time zone 'UTC')::date desc
        limit
          $7
      ) as "a" on true
  )
select
  date_part(
    'year',
    ("localDateTime" at time zone 'UTC')::date
  )::int as "year",
  json_agg("res") as "assets"
from
  "res"
group by
  ("localDateTime" at time zone 'UTC')::date
order by
  ("localDateTime" at time zone 'UTC')::date desc

-- AssetRepository.getByIds
select
  "asset".*
from
  "asset"
where
  "asset"."id" = any ($1::uuid[])

-- AssetRepository.getByIdsWithAllRelationsButStacks
select
  "asset".*,
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_face".*,
          "person" as "person"
        from
          "asset_face"
          left join lateral (
            select
              "person".*
            from
              "person"
            where
              "person"."personGroupId" = "asset_face"."personGroupId"
              and "person"."ownerId" = "asset"."ownerId"
          ) as "person" on true
        where
          "asset_face"."assetId" = "asset"."id"
          and "asset_face"."deletedAt" is null
          and "asset_face"."isVisible" is true
      ) as agg
  ) as "faces",
  (
    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
          "asset"."id" = "tag_asset"."assetId"
      ) as agg
  ) as "tags",
  to_json("asset_exif") as "exifInfo"
from
  "asset"
  left join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
where
  "asset"."id" = any ($1::uuid[])

-- AssetRepository.deleteAll
delete from "asset"
where
  "ownerId" = $1

-- AssetRepository.getByLibraryIdAndOriginalPath
select
  "asset".*
from
  "asset"
where
  "libraryId" = $1::uuid
  and "originalPath" = $2
limit
  $3

-- AssetRepository.getLivePhotoCount
select
  count(*) as "count"
from
  "asset"
where
  "livePhotoVideoId" = $1::uuid

-- AssetRepository.getFileSamples
select
  "assetId",
  "path"
from
  "asset_file"
limit
  3

-- AssetRepository.getForCopy
select
  "id",
  "stackId",
  "originalPath",
  "isFavorite",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_file"."id",
          "asset_file"."path",
          "asset_file"."type",
          "asset_file"."isEdited"
        from
          "asset_file"
        where
          "asset_file"."assetId" = "asset"."id"
      ) as agg
  ) as "files"
from
  "asset"
where
  "id" = $1::uuid
limit
  $2

-- AssetRepository.getById
select
  "asset".*
from
  "asset"
where
  "asset"."id" = $1::uuid
limit
  $2

-- AssetRepository.updateAll
update "asset"
set
where
  "id" = any ($1::uuid[])

-- AssetRepository.getByChecksum
select
  "asset".*
from
  "asset"
where
  "ownerId" = $1::uuid
  and "checksum" = $2
  and "libraryId" = $3::uuid
limit
  $4

-- AssetRepository.getByChecksums
select
  "id",
  "checksum",
  "deletedAt"
from
  "asset"
where
  "ownerId" = $1::uuid
  and "checksum" in ($2)

-- AssetRepository.getUploadAssetIdByChecksum
select
  "id"
from
  "asset"
where
  "ownerId" = $1::uuid
  and "checksum" = $2
  and "libraryId" is null
limit
  $3

-- AssetRepository.getCalendarHeatmap
select
  date_trunc('DAY', "asset"."createdAt" AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' as "date",
  count(*) as "count"
from
  "asset"
where
  "ownerId" = $1::uuid
  and "createdAt" >= $2
  and "createdAt" < $3
  and "deletedAt" is null
group by
  date_trunc('DAY', "asset"."createdAt" AT TIME ZONE 'UTC') AT TIME ZONE 'UTC'
order by
  "date" asc

-- AssetRepository.getTimeBuckets
with
  "asset" as (
    select
      date_trunc('MONTH', "localDateTime" AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' as "timeBucket"
    from
      "asset"
    where
      "asset"."deletedAt" is null
      and "asset"."visibility" in ('archive', 'timeline')
  )
select
  ("timeBucket" AT TIME ZONE 'UTC')::date::text as "timeBucket",
  count(*) as "count"
from
  "asset"
group by
  "timeBucket"
order by
  "timeBucket" desc

-- AssetRepository.getTimeBucket
with
  "cte" as (
    select
      "asset"."duration",
      "asset"."id",
      "asset"."visibility",
      asset."isFavorite"
      and asset."ownerId" = $1 as "isFavorite",
      asset.type = 'IMAGE' as "isImage",
      asset."deletedAt" is not null as "isTrashed",
      "asset"."livePhotoVideoId",
      extract(
        epoch
        from
          (
            asset."localDateTime" AT TIME ZONE 'UTC' - asset."fileCreatedAt" at time zone 'UTC'
          )
      )::real / 3600 as "localOffsetHours",
      "asset"."ownerId",
      "asset"."status",
      asset."fileCreatedAt" at time zone 'utc' as "fileCreatedAt",
      asset."createdAt" at time zone 'utc' as "createdAt",
      encode("asset"."thumbhash", 'base64') as "thumbhash",
      "asset_exif"."projectionType",
      coalesce(
        case
          when asset."height" = 0
          or asset."width" = 0 then 1
          else round(
            asset."width"::numeric / asset."height"::numeric,
            3
          )
        end,
        1
      ) as "ratio",
      "asset_exif"."city",
      "asset_exif"."country",
      "stack"
    from
      "asset"
      inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
      left join lateral (
        select
          array[stacked."stackId"::text, count('stacked')::text] as "stack"
        from
          "asset" as "stacked"
        where
          "stacked"."stackId" = "asset"."stackId"
          and "stacked"."deletedAt" is null
          and "stacked"."visibility" = $2
        group by
          "stacked"."stackId"
      ) as "stacked_assets" on true
    where
      "asset"."deletedAt" is null
      and "asset"."visibility" in ('archive', 'timeline')
      and date_trunc('MONTH', "localDateTime" AT TIME ZONE 'UTC') AT TIME ZONE 'UTC' = $3
      and not exists (
        select
        from
          "stack"
        where
          "stack"."id" = "asset"."stackId"
          and "stack"."primaryAssetId" != "asset"."id"
      )
    order by
      (asset."localDateTime" AT TIME ZONE 'UTC')::date desc,
      "asset"."fileCreatedAt" desc,
      "asset"."originalFileName" desc
  ),
  "agg" as (
    select
      coalesce(array_agg("duration"), '{}') as "duration",
      coalesce(array_agg("id"), '{}') as "id",
      coalesce(array_agg("visibility"), '{}') as "visibility",
      coalesce(array_agg("isFavorite"), '{}') as "isFavorite",
      coalesce(array_agg("isImage"), '{}') as "isImage",
      coalesce(array_agg("isTrashed"), '{}') as "isTrashed",
      coalesce(array_agg("livePhotoVideoId"), '{}') as "livePhotoVideoId",
      coalesce(array_agg("fileCreatedAt"), '{}') as "fileCreatedAt",
      coalesce(array_agg("localOffsetHours"), '{}') as "localOffsetHours",
      coalesce(array_agg("createdAt"), '{}') as "createdAt",
      coalesce(array_agg("ownerId"), '{}') as "ownerId",
      coalesce(array_agg("projectionType"), '{}') as "projectionType",
      coalesce(array_agg("ratio"), '{}') as "ratio",
      coalesce(array_agg("status"), '{}') as "status",
      coalesce(array_agg("thumbhash"), '{}') as "thumbhash",
      coalesce(array_agg("city"), '{}') as "city",
      coalesce(array_agg("country"), '{}') as "country",
      coalesce(json_agg("stack"), '[]') as "stack"
    from
      "cte"
  )
select
  to_json(agg)::text as "assets"
from
  "agg"

-- AssetRepository.getAssetIdByCity
with
  "cities" as (
    select
      "city"
    from
      "asset_exif"
    where
      "city" is not null
    group by
      "city"
    having
      count("assetId") >= $1
  )
select distinct
  on ("asset_exif"."city") "assetId" as "data",
  "asset_exif"."city" as "value"
from
  "asset"
  inner join "asset_exif" on "asset"."id" = "asset_exif"."assetId"
  inner join "cities" on "asset_exif"."city" = "cities"."city"
where
  "ownerId" = $2::uuid
  and "visibility" = $3
  and "type" = $4
  and "deletedAt" is null
limit
  $5

-- AssetRepository.getRecentlyCreatedAssetIds
select
  "id" as "data",
  "createdAt" as "value"
from
  "asset"
where
  "ownerId" = $1::uuid
  and "asset"."visibility" = $2
  and "type" = $3
  and "deletedAt" is null
order by
  "value" desc
limit
  $4

-- AssetRepository.detectOfflineExternalAssets
update "asset"
set
  "isOffline" = $1,
  "deletedAt" = $2
where
  "isOffline" = $3
  and "isExternal" = $4
  and "libraryId" = $5::uuid
  and (
    not "originalPath" like $6
    or "originalPath" ~* $7
  )

-- AssetRepository.filterNewExternalAssetPaths
select
  "path"
from
  unnest(array[$1]::text[]) as "path"
where
  not exists (
    select
      "originalPath"
    from
      "asset"
    where
      "asset"."originalPath" = "path"
      and "libraryId" = $2::uuid
      and "isExternal" = $3
  )

-- AssetRepository.getForOriginal
select
  "asset"."id",
  "originalFileName",
  "asset_file"."path" as "editedPath",
  "originalPath"
from
  "asset"
  left join "asset_file" on "asset"."id" = "asset_file"."assetId"
  and "asset_file"."isEdited" = $1
  and "asset_file"."type" = $2
where
  "asset"."id" in ($3)

-- AssetRepository.getForOriginals
select
  "asset"."id",
  "originalFileName",
  "asset_file"."path" as "editedPath",
  "originalPath"
from
  "asset"
  left join "asset_file" on "asset"."id" = "asset_file"."assetId"
  and "asset_file"."isEdited" = $1
  and "asset_file"."type" = $2
where
  "asset"."id" in ($3)

-- AssetRepository.getForThumbnail
select
  "asset"."originalPath",
  "asset"."originalFileName",
  "asset_file"."path" as "path"
from
  "asset"
  left join "asset_file" on "asset"."id" = "asset_file"."assetId"
  and "asset_file"."type" = $1
where
  "asset"."id" = $2
order by
  "asset_file"."isEdited" desc

-- AssetRepository.getForVideo
select
  "asset"."originalPath",
  (
    select
      "asset_file"."path"
    from
      "asset_file"
    where
      "asset_file"."assetId" = "asset"."id"
      and "asset_file"."type" = 'encoded_video'
      and "asset_file"."isEdited" = false
  ) as "encodedVideoPath"
from
  "asset"
where
  "asset"."id" = $1
  and "asset"."type" = $2

-- AssetRepository.getForOcr
select
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_edit"."action",
          "asset_edit"."parameters"
        from
          "asset_edit"
        where
          "asset_edit"."assetId" = "asset"."id"
      ) as agg
  ) as "edits",
  "asset_exif"."exifImageWidth",
  "asset_exif"."exifImageHeight",
  "asset_exif"."orientation"
from
  "asset"
  inner join "asset_exif" on "asset_exif"."assetId" = "asset"."id"
where
  "asset"."id" = $1

-- AssetRepository.getForEdit
select
  "asset"."type",
  "asset"."livePhotoVideoId",
  "asset"."originalPath",
  "asset"."originalFileName",
  "asset_exif"."exifImageWidth",
  "asset_exif"."exifImageHeight",
  "asset_exif"."orientation",
  "asset_exif"."projectionType"
from
  "asset"
  inner join "asset_exif" on "asset_exif"."assetId" = "asset"."id"
where
  "asset"."id" = $1

-- AssetRepository.getForMetadataExtractionTags
select
  "asset_exif"."tags"
from
  "asset_exif"
where
  "asset_exif"."assetId" = $1

-- AssetRepository.getForFaces
select
  "asset_exif"."exifImageHeight",
  "asset_exif"."exifImageWidth",
  "asset_exif"."orientation",
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset_edit"."action",
          "asset_edit"."parameters"
        from
          "asset_edit"
        where
          "asset_edit"."assetId" = "asset"."id"
      ) as agg
  ) as "edits"
from
  "asset"
  inner join "asset_exif" on "asset_exif"."assetId" = "asset"."id"
where
  "asset"."id" = $1

-- AssetRepository.getForUpdateTags
select
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "tag"."value"
        from
          "tag"
          inner join "tag_asset" on "tag"."id" = "tag_asset"."tagId"
        where
          "asset"."id" = "tag_asset"."assetId"
      ) as agg
  ) as "tags"
from
  "asset"
where
  "asset"."id" = $1