-- SWH DB schema upgrade
-- from_version: 84
-- to_version: 85
-- description: content cache: insert lines in deterministic order to reduce lock contention

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

CREATE OR REPLACE FUNCTION swh_cache_content_revision_add() RETURNS void
    LANGUAGE plpgsql
    AS $$
declare
  cnt bigint;
  d sha1_git;
begin
  delete from tmp_bytea t where exists (select 1 from cache_content_revision_processed ccrp where t.id = ccrp.revision);

  select count(*) from tmp_bytea into cnt;
  if cnt <> 0 then
    create temporary table tmp_ccr (
        content sha1_git,
        directory sha1_git,
        path unix_path
    ) on commit drop;

    create temporary table tmp_ccrd (
        directory sha1_git,
        revision sha1_git
    ) on commit drop;

    insert into tmp_ccrd
      select directory, id as revision
      from tmp_bytea
      inner join revision using(id);

    insert into cache_content_revision_processed
      select distinct id from tmp_bytea order by id;

    for d in
      select distinct directory from tmp_ccrd
    loop
      insert into tmp_ccr
        select sha1_git as content, d as directory, name as path
        from swh_directory_walk(d)
        where type='file';
    end loop;

    with revision_contents as (
      select content, false as blacklisted, array_agg(ARRAY[revision::bytea, path::bytea]) as revision_paths
      from tmp_ccr
      inner join tmp_ccrd using (directory)
      group by content
      order by content
    ), updated_cache_entries as (
      update cache_content_revision ccr
      set revision_paths = ccr.revision_paths || rc.revision_paths
      from revision_contents rc
      where ccr.content = rc.content and ccr.blacklisted = false
      returning ccr.content
    ) insert into cache_content_revision
        select * from revision_contents rc
        where not exists (select 1 from updated_cache_entries uce where uce.content = rc.content)
        order by rc.content
      on conflict (content) do update
        set revision_paths = cache_content_revision.revision_paths || EXCLUDED.revision_paths
        where cache_content_revision.blacklisted = false;
    return;
  else
    return;
  end if;
end
$$;

COMMENT ON FUNCTION swh_cache_content_revision_add() IS 'Cache the revisions from tmp_bytea into cache_content_revision';