-- SWH DB schema upgrade
-- from_version: 59
-- to_version: 60
-- description: add swh_object_find_by_sha1_git

insert into dbversion(version, release, description)
      values(60, now(), 'Work In Progress');

create type object_found as (
    sha1_git   sha1_git,
    type       object_type,
    id         bytea,       -- sha1 or sha1_git depending on object_type
    object_id  bigint
);


CREATE OR REPLACE FUNCTION swh_object_find_by_sha1_git() RETURNS SETOF object_found
    LANGUAGE plpgsql
    AS $$
begin
    return query
    with known_objects as ((
        select id as sha1_git, 'release'::object_type as type, id, object_id from release r
        where exists (select 1 from tmp_bytea t where t.id = r.id)
    ) union all (
        select id as sha1_git, 'revision'::object_type as type, id, object_id from revision r
        where exists (select 1 from tmp_bytea t where t.id = r.id)
    ) union all (
        select id as sha1_git, 'directory'::object_type as type, id, object_id from directory d
        where exists (select 1 from tmp_bytea t where t.id = d.id)
    ) union all (
        select sha1_git as sha1_git, 'content'::object_type as type, sha1 as id, object_id from content c
        where exists (select 1 from tmp_bytea t where t.id = c.sha1_git)
    ))
    select t.id::sha1_git as sha1_git, k.type, k.id, k.object_id from tmp_bytea t
      left join known_objects k on t.id = k.sha1_git;
end
$$;