Skip to content

High performance self-hosted photo and video management solution.

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

-- StackRepository.search
select
  "stack".*,
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset".*,
          to_json("exifInfo") as "exifInfo"
        from
          "asset"
          inner join lateral (
            select
              "asset_exif"."assetId",
              "asset_exif"."autoStackId",
              "asset_exif"."bitsPerSample",
              "asset_exif"."city",
              "asset_exif"."colorspace",
              "asset_exif"."country",
              "asset_exif"."dateTimeOriginal",
              "asset_exif"."description",
              "asset_exif"."exifImageHeight",
              "asset_exif"."exifImageWidth",
              "asset_exif"."exposureTime",
              "asset_exif"."fileSizeInByte",
              "asset_exif"."fNumber",
              "asset_exif"."focalLength",
              "asset_exif"."fps",
              "asset_exif"."iso",
              "asset_exif"."latitude",
              "asset_exif"."lensModel",
              "asset_exif"."livePhotoCID",
              "asset_exif"."longitude",
              "asset_exif"."make",
              "asset_exif"."model",
              "asset_exif"."modifyDate",
              "asset_exif"."orientation",
              "asset_exif"."profileDescription",
              "asset_exif"."projectionType",
              "asset_exif"."rating",
              "asset_exif"."state",
              "asset_exif"."tags",
              "asset_exif"."timeZone"
            from
              "asset_exif"
            where
              "asset_exif"."assetId" = "asset"."id"
          ) as "exifInfo" on true
        where
          "asset"."deletedAt" is null
          and "asset"."stackId" = "stack"."id"
          and "asset"."visibility" in ('archive', 'timeline')
        order by
          "asset"."fileCreatedAt" asc
      ) as agg
  ) as "assets"
from
  "stack"
where
  "stack"."ownerId" = $1

-- StackRepository.delete
delete from "stack"
where
  "id" = $1::uuid

-- StackRepository.getById
select
  *,
  (
    select
      coalesce(json_agg(agg), '[]')
    from
      (
        select
          "asset".*,
          (
            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",
          to_json("exifInfo") as "exifInfo"
        from
          "asset"
          inner join lateral (
            select
              "asset_exif"."assetId",
              "asset_exif"."autoStackId",
              "asset_exif"."bitsPerSample",
              "asset_exif"."city",
              "asset_exif"."colorspace",
              "asset_exif"."country",
              "asset_exif"."dateTimeOriginal",
              "asset_exif"."description",
              "asset_exif"."exifImageHeight",
              "asset_exif"."exifImageWidth",
              "asset_exif"."exposureTime",
              "asset_exif"."fileSizeInByte",
              "asset_exif"."fNumber",
              "asset_exif"."focalLength",
              "asset_exif"."fps",
              "asset_exif"."iso",
              "asset_exif"."latitude",
              "asset_exif"."lensModel",
              "asset_exif"."livePhotoCID",
              "asset_exif"."longitude",
              "asset_exif"."make",
              "asset_exif"."model",
              "asset_exif"."modifyDate",
              "asset_exif"."orientation",
              "asset_exif"."profileDescription",
              "asset_exif"."projectionType",
              "asset_exif"."rating",
              "asset_exif"."state",
              "asset_exif"."tags",
              "asset_exif"."timeZone"
            from
              "asset_exif"
            where
              "asset_exif"."assetId" = "asset"."id"
          ) as "exifInfo" on true
        where
          "asset"."deletedAt" is null
          and "asset"."stackId" = "stack"."id"
          and "asset"."visibility" in ('archive', 'timeline')
        order by
          "asset"."fileCreatedAt" asc
      ) as agg
  ) as "assets"
from
  "stack"
where
  "id" = $1::uuid

-- StackRepository.getForAssetRemoval
select
  "stackId" as "id",
  "stack"."primaryAssetId"
from
  "asset"
  left join "stack" on "stack"."id" = "asset"."stackId"
where
  "asset"."id" = $1

-- StackRepository.merge
update "asset"
set
  "stackId" = $1
where
  "asset"."stackId" = $2