diff --git a/swh/provenance/sql/30-schema.sql b/swh/provenance/sql/30-schema.sql
index e37b742..4c489c9 100644
--- a/swh/provenance/sql/30-schema.sql
+++ b/swh/provenance/sql/30-schema.sql
@@ -1,176 +1,172 @@
 -- psql variables to get the current database flavor
 select position('denormalized' in swh_get_dbflavor()::text) = 0 as dbflavor_norm \gset
 select position('without-path' in swh_get_dbflavor()::text) = 0 as dbflavor_with_path \gset
 
 create table dbversion
 (
     version     int primary key,
     release     timestamptz,
     description text
 );
 
 comment on table dbversion is 'Details of current db version';
 comment on column dbversion.version is 'SQL schema version';
 comment on column dbversion.release is 'Version deployment timestamp';
 comment on column dbversion.description is 'Release description';
 
 -- latest schema version
 insert into dbversion(version, release, description)
     values(1, now(), 'Work In Progress');
 
 -- a Git object ID, i.e., a Git-style salted SHA1 checksum
 create domain sha1_git as bytea check (length(value) = 20);
 
 -- UNIX path (absolute, relative, individual path component, etc.)
 create domain unix_path as bytea;
 
--- a relation destination ID (used for denormalized flavors: with-path vs. without-path)
-\if :dbflavor_with_path
-create type rel_dst as (id bigint, loc bigint);
-\else
-create domain rel_dst as bigint;
-\endif
-
 -- relation filter options for querying
 create type rel_flt as enum (
   'filter-src',
   'filter-dst',
   'no-filter'
 );
 comment on type rel_flt is 'Relation get filter types';
 
 -- entity tables
 create table content
 (
     id      bigserial primary key,      -- internal identifier of the content blob
     sha1    sha1_git unique not null,   -- intrinsic identifier of the content blob
     date    timestamptz                 -- timestamp of the revision where the blob appears early
 );
 comment on column content.id is 'Content internal identifier';
 comment on column content.sha1 is 'Content intrinsic identifier';
 comment on column content.date is 'Earliest timestamp for the content (first seen time)';
 
 create table directory
 (
     id      bigserial primary key,      -- internal identifier of the directory appearing in an isochrone inner frontier
     sha1    sha1_git unique not null,   -- intrinsic identifier of the directory
     date    timestamptz                 -- max timestamp among those of the directory children's
 );
 comment on column directory.id is 'Directory internal identifier';
 comment on column directory.sha1 is 'Directory intrinsic identifier';
 comment on column directory.date is 'Latest timestamp for the content in the directory';
 
 create table revision
 (
     id      bigserial primary key,      -- internal identifier of the revision
     sha1    sha1_git unique not null,   -- intrinsic identifier of the revision
     date    timestamptz,                -- timestamp of the revision
     origin  bigint                      -- id of the preferred origin
     -- foreign key (origin) references origin (id)
 );
 comment on column revision.id is 'Revision internal identifier';
 comment on column revision.sha1 is 'Revision intrinsic identifier';
 comment on column revision.date is 'Revision timestamp';
 comment on column revision.origin is 'preferred origin for the revision';
 
 create table location
 (
     id      bigserial primary key,      -- internal identifier of the location
     path    unix_path unique not null   -- path to the location
 );
 comment on column location.id is 'Location internal identifier';
 comment on column location.path is 'Path to the location';
 
 create table origin
 (
     id      bigserial primary key,      -- internal identifier of the origin
     sha1    sha1_git unique not null,   -- intrinsic identifier of the origin
     url     text unique not null        -- url of the origin
 );
 comment on column origin.id is 'Origin internal identifier';
 comment on column origin.sha1 is 'Origin intrinsic identifier';
 comment on column origin.url is 'URL of the origin';
 
 -- relation tables
 create table content_in_revision
 (
     content  bigint not null,           -- internal identifier of the content blob
 \if :dbflavor_norm
     revision bigint not null,           -- internal identifier of the revision where the blob appears for the first time
-    location bigint                     -- location of the content relative to the revision root directory
+    location bigint                     -- location of the content relative to the revision's root directory
 \else
-    revision rel_dst[]                  -- internal reference of the revision (and location) where the blob appears for the first time
+    revision bigint[],                  -- internal identifiers of the revisions where the blob appears for the first time
+    location bigint[]                   -- locations of the content relative to the revisions' root directory
 \endif
     -- foreign key (content) references content (id),
     -- foreign key (revision) references revision (id),
     -- foreign key (location) references location (id)
 );
 comment on column content_in_revision.content is 'Content internal identifier';
 \if :dbflavor_norm
 comment on column content_in_revision.revision is 'Revision internal identifier';
 comment on column content_in_revision.location is 'Location of content in revision';
 \else
 comment on column content_in_revision.revision is 'Revision/location internal identifiers';
 \endif
 
 create table content_in_directory
 (
     content   bigint not null,          -- internal identifier of the content blob
 \if :dbflavor_norm
     directory bigint not null,          -- internal identifier of the directory containing the blob
     location  bigint                    -- location of the content relative to its parent directory in the isochrone frontier
 \else
-    directory rel_dst[]                 -- internal reference of the directory (and location) containing the blob
+    directory bigint[],                 -- internal reference of the directories containing the blob
+    location bigint[]                   -- locations of the content relative to its parent directories in the isochrone frontier
 \endif
     -- foreign key (content) references content (id),
     -- foreign key (directory) references directory (id),
     -- foreign key (location) references location (id)
 );
 comment on column content_in_directory.content is 'Content internal identifier';
 \if :dbflavor_norm
 comment on column content_in_directory.directory is 'Directory internal identifier';
 comment on column content_in_directory.location is 'Location of content in directory';
 \else
 comment on column content_in_directory.directory is 'Directory/location internal identifiers';
 \endif
 
 create table directory_in_revision
 (
     directory bigint not null,          -- internal identifier of the directory appearing in the revision
 \if :dbflavor_norm
     revision  bigint not null,          -- internal identifier of the revision containing the directory
-    location  bigint                    -- location of the directory relative to the revision root directory
+    location  bigint                    -- location of the directory relative to the revision's root directory
 \else
-    revision rel_dst[]                  -- internal reference of the revision (and location) containing the directory
+    revision bigint[],                  -- internal identifiers of the revisions containing the directory
+    location bigint[]                   -- locations of the directory relative to the revisions' root directory
 \endif
     -- foreign key (directory) references directory (id),
     -- foreign key (revision) references revision (id),
     -- foreign key (location) references location (id)
 );
 comment on column directory_in_revision.directory is 'Directory internal identifier';
 \if :dbflavor_norm
 comment on column directory_in_revision.revision is 'Revision internal identifier';
 comment on column directory_in_revision.location is 'Location of content in revision';
 \else
 comment on column directory_in_revision.revision is 'Revision/location internal identifiers';
 \endif
 
 create table revision_in_origin
 (
     revision bigint not null,           -- internal identifier of the revision poined by the origin
     origin   bigint not null            -- internal identifier of the origin that points to the revision
     -- foreign key (revision) references revision (id),
     -- foreign key (origin) references origin (id)
 );
 comment on column revision_in_origin.revision is 'Revision internal identifier';
 comment on column revision_in_origin.origin is 'Origin internal identifier';
 
 create table revision_before_revision
 (
     prev    bigserial not null,         -- internal identifier of the source revision
     next    bigserial not null          -- internal identifier of the destination revision
     -- foreign key (prev) references revision (id),
     -- foreign key (next) references revision (id)
 );
 comment on column revision_before_revision.prev is 'Source revision internal identifier';
 comment on column revision_before_revision.next is 'Destination revision internal identifier';
diff --git a/swh/provenance/sql/40-funcs.sql b/swh/provenance/sql/40-funcs.sql
index 1bb2436..8ecd884 100644
--- a/swh/provenance/sql/40-funcs.sql
+++ b/swh/provenance/sql/40-funcs.sql
@@ -1,701 +1,709 @@
 -- psql variables to get the current database flavor
 select position('denormalized' in swh_get_dbflavor()::text) = 0 as dbflavor_norm \gset
 select position('with-path' in swh_get_dbflavor()::text) != 0 as dbflavor_with_path \gset
 
 create or replace function swh_mktemp_relation_add() returns void
     language sql
 as $$
     create temp table tmp_relation_add (
         src sha1_git not null,
         dst sha1_git not null,
         path unix_path
     ) on commit drop
 $$;
 
 \if :dbflavor_norm
 
 \if :dbflavor_with_path
 --
 -- with path and normalized
 --
 
 create or replace function swh_provenance_content_find_first(content_id sha1_git)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     select C.sha1 as content,
            R.sha1 as revision,
            R.date as date,
            O.url as origin,
            L.path as path
     from content as C
     inner join content_in_revision as CR on (CR.content = C.id)
     inner join location as L on (L.id = CR.location)
     inner join revision as R on (R.id = CR.revision)
     left join origin as O on (O.id = R.origin)
     where C.sha1 = content_id
     order by date, revision, origin, path asc limit 1
 $$;
 
 create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     (select C.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             L.path as path
      from content as C
      inner join content_in_revision as CR on (CR.content = C.id)
      inner join location as L on (L.id = CR.location)
      inner join revision as R on (R.id = CR.revision)
      left join origin as O on (O.id = R.origin)
      where C.sha1 = content_id)
     union
     (select C.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             case DL.path
                 when '' then CL.path
                 when '.' then CL.path
                 else (DL.path || '/' || CL.path)::unix_path
             end as path
      from content as C
      inner join content_in_directory as CD on (CD.content = C.id)
      inner join directory_in_revision as DR on (DR.directory = CD.directory)
      inner join revision as R on (R.id = DR.revision)
      inner join location as CL on (CL.id = CD.location)
      inner join location as DL on (DL.id = DR.location)
      left join origin as O on (O.id = R.origin)
      where C.sha1 = content_id)
     order by date, revision, origin, path limit early_cut
 $$;
 
 create or replace function swh_provenance_relation_add_from_temp(
     rel_table regclass, src_table regclass, dst_table regclass
 )
     returns void
     language plpgsql
     volatile
 as $$
     declare
         select_fields text;
         join_location text;
     begin
         if src_table in ('content'::regclass, 'directory'::regclass) then
             select_fields := 'D.id, L.id';
             join_location := 'inner join location as L on (L.path = V.path)';
         else
             select_fields := 'D.id';
             join_location := '';
         end if;
 
         execute format(
             'insert into %s
                select S.id, ' || select_fields || '
                from tmp_relation_add as V
                inner join %s as S on (S.sha1 = V.src)
                inner join %s as D on (D.sha1 = V.dst)
                ' || join_location || '
              on conflict do nothing',
             rel_table, src_table, dst_table
         );
     end;
 $$;
 
 create or replace function swh_provenance_relation_get(
     rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
 )
     returns table (
         src sha1_git,
         dst sha1_git,
         path unix_path
     )
     language plpgsql
     stable
 as $$
     declare
         src_field text;
         dst_field text;
         join_location text;
         proj_location text;
         filter_result text;
     begin
         if rel_table = 'revision_before_revision'::regclass then
             src_field := 'prev';
             dst_field := 'next';
         else
             src_field := src_table::text;
             dst_field := dst_table::text;
         end if;
 
         if src_table in ('content'::regclass, 'directory'::regclass) then
             join_location := 'inner join location as L on (L.id = R.location)';
             proj_location := 'L.path';
         else
             join_location := '';
             proj_location := 'NULL::unix_path';
         end if;
 
         case filter
             when 'filter-src'::rel_flt then
                 filter_result := 'where S.sha1 = any($1)';
             when 'filter-dst'::rel_flt  then
                 filter_result := 'where D.sha1 = any($1)';
             else
                 filter_result := '';
         end case;
 
         return query execute format(
             'select S.sha1 as src, D.sha1 as dst, ' || proj_location || ' as path
              from %s as R
              inner join %s as S on (S.id = R.' || src_field || ')
              inner join %s as D on (D.id = R.' || dst_field || ')
              ' || join_location || '
              ' || filter_result,
             rel_table, src_table, dst_table
         ) using sha1s;
     end;
 $$;
 
 \else
 --
 -- without path and normalized
 --
 create or replace function swh_provenance_content_find_first(content_id sha1_git)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     select C.sha1 as content,
            R.sha1 as revision,
            R.date as date,
            O.url as origin,
            '\x'::unix_path as path
     from content as C
     inner join content_in_revision as CR on (CR.content = C.id)
     inner join revision as R on (R.id = CR.revision)
     left join origin as O on (O.id = R.origin)
     where C.sha1 = content_id
     order by date, revision, origin asc limit 1
 $$;
 
 create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     (select C.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             '\x'::unix_path as path
      from content as C
      inner join content_in_revision as CR on (CR.content = C.id)
      inner join revision as R on (R.id = CR.revision)
      left join origin as O on (O.id = R.origin)
      where C.sha1 = content_id)
     union
     (select C.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             '\x'::unix_path as path
      from content as C
      inner join content_in_directory as CD on (CD.content = C.id)
      inner join directory_in_revision as DR on (DR.directory = CD.directory)
      inner join revision as R on (R.id = DR.revision)
      left join origin as O on (O.id = R.origin)
      where C.sha1 = content_id)
     order by date, revision, origin, path limit early_cut
 $$;
 
 create or replace function swh_provenance_relation_add_from_temp(
     rel_table regclass, src_table regclass, dst_table regclass
 )
     returns void
     language plpgsql
     volatile
 as $$
     begin
         execute format(
             'insert into %s
                select S.id, D.id
                from tmp_relation_add as V
                inner join %s as S on (S.sha1 = V.src)
                inner join %s as D on (D.sha1 = V.dst)
              on conflict do nothing',
             rel_table, src_table, dst_table
         );
     end;
 $$;
 
 create or replace function swh_provenance_relation_get(
     rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
 )
     returns table (
         src sha1_git,
         dst sha1_git,
         path unix_path
     )
     language plpgsql
     stable
 as $$
     declare
         src_field text;
         dst_field text;
         filter_result text;
     begin
         if rel_table = 'revision_before_revision'::regclass then
             src_field := 'prev';
             dst_field := 'next';
         else
             src_field := src_table::text;
             dst_field := dst_table::text;
         end if;
 
         case filter
             when 'filter-src'::rel_flt  then
                 filter_result := 'where S.sha1 = any($1)';
             when 'filter-dst'::rel_flt then
                 filter_result := 'where D.sha1 = any($1)';
             else
                 filter_result := '';
         end case;
 
         return query execute format(
             'select S.sha1 as src, D.sha1 as dst, NULL::unix_path as path
              from %s as R
              inner join %s as S on (S.id = R.' || src_field || ')
              inner join %s as D on (D.id = R.' || dst_field || ')
             ' || filter_result,
             rel_table, src_table, dst_table
         ) using sha1s;
     end;
 $$;
 
 -- :dbflavor_with_path
 \endif
 
 -- :dbflavor_norm
 \else
 
 \if :dbflavor_with_path
 --
 -- with path and denormalized
 --
 
 create or replace function swh_provenance_content_find_first(content_id sha1_git)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     select CL.sha1 as content,
            R.sha1 as revision,
            R.date as date,
            O.url as origin,
            L.path as path
     from (
       select C.sha1 as sha1,
-             unnest(CR.revision) as revision
+             unnest(CR.revision) as revision,
+             unnest(CR.location) as location
       from content_in_revision as CR
       inner join content as C on (C.id = CR.content)
       where C.sha1 = content_id
     ) as CL
-    inner join revision as R on (R.id = (CL.revision).id)
-    inner join location as L on (L.id = (CL.revision).loc)
+    inner join revision as R on (R.id = CL.revision)
+    inner join location as L on (L.id = CL.location)
     left join origin as O on (O.id = R.origin)
     order by date, revision, origin, path asc limit 1
 $$;
 
 create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     (with
      cntrev as (
       select C.sha1 as sha1,
-             unnest(CR.revision) as revision
+             unnest(CR.revision) as revision,
+             unnest(CR.location) as location
       from content_in_revision as CR
       inner join content as C on (C.id = CR.content)
       where C.sha1 = content_id)
      select CR.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             L.path as path
      from cntrev as CR
-     inner join revision as R on (R.id = (CR.revision).id)
-     inner join location as L on (L.id = (CR.revision).loc)
+     inner join revision as R on (R.id = CR.revision)
+     inner join location as L on (L.id = CR.location)
      left join origin as O on (O.id = R.origin))
     union
     (with
      cntdir as (
      select C.sha1 as sha1,
-            unnest(CD.directory) as directory
+            unnest(CD.directory) as directory,
+            unnest(CD.location) as location
      from content as C
      inner join content_in_directory as CD on (CD.content = C.id)
      where C.sha1 = content_id),
      cntrev as (
       select CD.sha1 as sha1,
              L.path as path,
-             unnest(DR.revision) as revision
+             unnest(DR.revision) as revision,
+             unnest(DR.location) as location
       from cntdir as CD
-      inner join directory_in_revision as DR on (DR.directory = (CD.directory).id)
-      inner join location as L on (L.id = (CD.directory).loc))
+      inner join directory_in_revision as DR on (DR.directory = CD.directory)
+      inner join location as L on (L.id = CD.location))
      select CR.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             case DL.path
                 when ''  then CR.path
                 when '.' then CR.path
                 else (DL.path || '/' || CR.path)::unix_path
             end as path
      from cntrev as CR
-     inner join revision as R on (R.id = (CR.revision).id)
-     inner join location as DL on (DL.id = (CR.revision).loc)
+     inner join revision as R on (R.id = CR.revision)
+     inner join location as DL on (DL.id = CR.location)
      left join origin as O on (O.id = R.origin))
     order by date, revision, origin, path limit early_cut
 $$;
 
 create or replace function swh_provenance_relation_add_from_temp(
     rel_table regclass, src_table regclass, dst_table regclass
 )
     returns void
     language plpgsql
     volatile
 as $$
     declare
         select_fields text;
         join_location text;
         group_entries text;
         on_conflict text;
     begin
         if src_table in ('content'::regclass, 'directory'::regclass) then
-            select_fields := 'array_agg((D.id, L.id)::rel_dst)';
+            select_fields := 'array_agg(D.id), array_agg(L.id)';
             join_location := 'inner join location as L on (L.path = V.path)';
             group_entries := 'group by S.id';
             on_conflict := format('
                 (%s) do update
-                set %s=array(
-                  select distinct unnest(
-                    %s.' || dst_table::text || ' || excluded.' || dst_table::text || '
-                  )
+                set (%s, location) = (
+                    with pairs as (
+                        select distinct * from unnest(
+                            %s.' || dst_table::text || ' || excluded.' || dst_table::text || ',
+                            %s.location || excluded.location
+                        ) as pair(dst, loc)
+                    )
+                    select array(select pairs.dst from pairs), array(select pairs.loc from pairs)
                 )',
-                src_table, dst_table, rel_table, rel_table, rel_table
+                src_table, dst_table, rel_table, rel_table, rel_table, rel_table
             );
         else
             select_fields := 'D.id';
             join_location := '';
             group_entries := '';
             on_conflict := 'do nothing';
         end if;
 
         execute format(
             'insert into %s
                select S.id, ' || select_fields || '
                from tmp_relation_add as V
                inner join %s as S on (S.sha1 = V.src)
                inner join %s as D on (D.sha1 = V.dst)
                ' || join_location || '
                ' || group_entries || '
              on conflict ' || on_conflict,
             rel_table, src_table, dst_table
         );
     end;
 $$;
 
 create or replace function swh_provenance_relation_get(
     rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
 )
     returns table (
         src sha1_git,
         dst sha1_git,
         path unix_path
     )
     language plpgsql
     stable
 as $$
     declare
         src_field text;
         dst_field text;
         proj_dst_id text;
         proj_unnested text;
         proj_location text;
         join_location text;
         filter_inner_result text;
         filter_outer_result text;
     begin
         if rel_table = 'revision_before_revision'::regclass then
             src_field := 'prev';
             dst_field := 'next';
         else
             src_field := src_table::text;
             dst_field := dst_table::text;
         end if;
 
         if src_table in ('content'::regclass, 'directory'::regclass) then
-            proj_unnested := 'unnest(R.' || dst_field || ') as dst';
-            proj_dst_id := '(CL.dst).id';
-            join_location := 'inner join location as L on (L.id = (CL.dst).loc)';
+            proj_unnested := 'unnest(R.' || dst_field || ') as dst, unnest(R.location) as loc';
+            proj_dst_id := 'CL.dst';
+            join_location := 'inner join location as L on (L.id = CL.loc)';
             proj_location := 'L.path';
         else
             proj_unnested := 'R.' || dst_field || ' as dst';
             proj_dst_id := 'CL.dst';
             join_location := '';
             proj_location := 'NULL::unix_path';
         end if;
 
         case filter
             when 'filter-src'::rel_flt then
                 filter_inner_result := 'where S.sha1 = any($1)';
                 filter_outer_result := '';
             when 'filter-dst'::rel_flt then
                 filter_inner_result := '';
                 filter_outer_result := 'where D.sha1 = any($1)';
             else
                 filter_inner_result := '';
                 filter_outer_result := '';
         end case;
 
         return query execute format(
             'select CL.src, D.sha1 as dst, ' || proj_location || ' as path
              from (select S.sha1 as src, ' || proj_unnested || '
                    from %s as R
                    inner join %s as S on (S.id = R.' || src_field || ')
                    ' || filter_inner_result || ') as CL
              inner join %s as D on (D.id = ' || proj_dst_id || ')
              ' || join_location || '
              ' || filter_outer_result,
             rel_table, src_table, dst_table
         ) using sha1s;
     end;
 $$;
 
 \else
 --
 -- without path and denormalized
 --
 create or replace function swh_provenance_content_find_first(content_id sha1_git)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     select CL.sha1 as content,
            R.sha1 as revision,
            R.date as date,
            O.url as origin,
            '\x'::unix_path as path
     from (
      select C.sha1, unnest(revision) as revision
      from content_in_revision as CR
      inner join content as C on (C.id = CR.content)
      where C.sha1=content_id
     ) as CL
     inner join revision as R on (R.id = CL.revision)
     left join origin as O on (O.id = R.origin)
     order by date, revision, origin, path asc limit 1
 $$;
 
 create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int)
     returns table (
         content sha1_git,
         revision sha1_git,
         date timestamptz,
         origin text,
         path unix_path
     )
     language sql
     stable
 as $$
     (with
      cntrev as (
       select C.sha1 as sha1,
              unnest(CR.revision) as revision
       from content_in_revision as CR
       inner join content as C on (C.id = CR.content)
       where C.sha1 = content_id)
      select CR.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             '\x'::unix_path as path
      from cntrev as CR
      inner join revision as R on (R.id = CR.revision)
      left join origin as O on (O.id = R.origin))
     union
     (with
      cntdir as (
       select C.sha1 as sha1,
              unnest(CD.directory) as directory
       from content as C
       inner join content_in_directory as CD on (CD.content = C.id)
       where C.sha1 = content_id),
      cntrev as (
       select CD.sha1 as sha1,
              unnest(DR.revision) as revision
       from cntdir as CD
       inner join directory_in_revision as DR on (DR.directory = CD.directory))
      select CR.sha1 as content,
             R.sha1 as revision,
             R.date as date,
             O.url as origin,
             '\x'::unix_path as path
      from cntrev as CR
      inner join revision as R on (R.id = CR.revision)
      left join origin as O on (O.id = R.origin))
     order by date, revision, origin, path limit early_cut
 $$;
 
 create or replace function swh_provenance_relation_add_from_temp(
     rel_table regclass, src_table regclass, dst_table regclass
 )
     returns void
     language plpgsql
     volatile
 as $$
     declare
         select_fields text;
         group_entries text;
         on_conflict text;
     begin
         if src_table in ('content'::regclass, 'directory'::regclass) then
             select_fields := 'array_agg(D.id)';
             group_entries := 'group by S.id';
             on_conflict := format('
                 (%s) do update
                 set %s=array(
                   select distinct unnest(
                     %s.' || dst_table::text || ' || excluded.' || dst_table::text || '
                   )
                 )',
                 src_table, dst_table, rel_table, rel_table
             );
         else
             select_fields := 'D.id';
             group_entries := '';
             on_conflict := 'do nothing';
         end if;
 
         execute format(
             'insert into %s
                select S.id, ' || select_fields || '
                from tmp_relation_add as V
                inner join %s as S on (S.sha1 = V.src)
                inner join %s as D on (D.sha1 = V.dst)
                ' || group_entries || '
              on conflict ' || on_conflict,
             rel_table, src_table, dst_table
         );
     end;
 $$;
 
 create or replace function swh_provenance_relation_get(
     rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
 )
     returns table (
         src sha1_git,
         dst sha1_git,
         path unix_path
     )
     language plpgsql
     stable
 as $$
     declare
         src_field text;
         dst_field text;
         proj_unnested text;
         filter_inner_result text;
         filter_outer_result text;
     begin
         if rel_table = 'revision_before_revision'::regclass then
             src_field := 'prev';
             dst_field := 'next';
         else
             src_field := src_table::text;
             dst_field := dst_table::text;
         end if;
 
         if src_table in ('content'::regclass, 'directory'::regclass) then
             proj_unnested := 'unnest(R.' || dst_field || ') as dst';
         else
             proj_unnested := 'R.' || dst_field || ' as dst';
         end if;
 
         case filter
             when 'filter-src'::rel_flt then
                 filter_inner_result := 'where S.sha1 = any($1)';
                 filter_outer_result := '';
             when 'filter-dst'::rel_flt then
                 filter_inner_result := '';
                 filter_outer_result := 'where D.sha1 = any($1)';
             else
                 filter_inner_result := '';
                 filter_outer_result := '';
         end case;
 
         return query execute format(
             'select CL.src, D.sha1 as dst, NULL::unix_path as path
              from (select S.sha1 as src, ' || proj_unnested || '
                    from %s as R
                    inner join %s as S on (S.id = R.' || src_field || ')
                    ' || filter_inner_result || ') as CL
              inner join %s as D on (D.id = CL.dst)
              ' || filter_outer_result,
             rel_table, src_table, dst_table
         ) using sha1s;
     end;
 $$;
 
 \endif
 -- :dbflavor_with_path
 \endif
 -- :dbflavor_norm