Page MenuHomeSoftware Heritage
Differential D6473 Diff 23516 swh/provenance/sql/40-funcs.sql

Changeset View

Standalone View

View Options

swh/provenance/sql/40-funcs.sql

Show First 20 Lines • Show All 325 Lines • ▼ Show 20 Lines
as $$ as $$
select CL.sha1 as content, select CL.sha1 as content,
R.sha1 as revision, R.sha1 as revision,
R.date as date, R.date as date,
O.url as origin, O.url as origin,
L.path as path L.path as path
from ( from (
select C.sha1 as sha1, 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 from content_in_revision as CR
inner join content as C on (C.id = CR.content) inner join content as C on (C.id = CR.content)
where C.sha1 = content_id where C.sha1 = content_id
) as CL ) as CL
inner join revision as R on (R.id = (CL.revision).id) inner join revision as R on (R.id = CL.revision)
inner join location as L on (L.id = (CL.revision).loc) inner join location as L on (L.id = CL.location)
left join origin as O on (O.id = R.origin) left join origin as O on (O.id = R.origin)
order by date, revision, origin, path asc limit 1 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) create or replace function swh_provenance_content_find_all(content_id sha1_git, early_cut int)
returns table ( returns table (
content sha1_git, content sha1_git,
revision sha1_git, revision sha1_git,
date timestamptz, date timestamptz,
origin text, origin text,
path unix_path path unix_path
) )
language sql language sql
stable stable
as $$ as $$
(with (with
cntrev as ( cntrev as (
select C.sha1 as sha1, 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 from content_in_revision as CR
inner join content as C on (C.id = CR.content) inner join content as C on (C.id = CR.content)
where C.sha1 = content_id) where C.sha1 = content_id)
select CR.sha1 as content, select CR.sha1 as content,
R.sha1 as revision, R.sha1 as revision,
R.date as date, R.date as date,
O.url as origin, O.url as origin,
L.path as path L.path as path
from cntrev as CR from cntrev as CR
inner join revision as R on (R.id = (CR.revision).id) inner join revision as R on (R.id = CR.revision)
inner join location as L on (L.id = (CR.revision).loc) inner join location as L on (L.id = CR.location)
left join origin as O on (O.id = R.origin)) left join origin as O on (O.id = R.origin))
union union
(with (with
cntdir as ( cntdir as (
select C.sha1 as sha1, select C.sha1 as sha1,
unnest(CD.directory) as directory unnest(CD.directory) as directory,
unnest(CD.location) as location
from content as C from content as C
inner join content_in_directory as CD on (CD.content = C.id) inner join content_in_directory as CD on (CD.content = C.id)
where C.sha1 = content_id), where C.sha1 = content_id),
cntrev as ( cntrev as (
select CD.sha1 as sha1, select CD.sha1 as sha1,
L.path as path, L.path as path,
unnest(DR.revision) as revision unnest(DR.revision) as revision,
unnest(DR.location) as location
from cntdir as CD from cntdir as CD
inner join directory_in_revision as DR on (DR.directory = (CD.directory).id) inner join directory_in_revision as DR on (DR.directory = CD.directory)
inner join location as L on (L.id = (CD.directory).loc)) inner join location as L on (L.id = CD.location))
select CR.sha1 as content, select CR.sha1 as content,
R.sha1 as revision, R.sha1 as revision,
R.date as date, R.date as date,
O.url as origin, O.url as origin,
case DL.path case DL.path
when '' then CR.path when '' then CR.path
when '.' then CR.path when '.' then CR.path
else (DL.path || '/' || CR.path)::unix_path else (DL.path || '/' || CR.path)::unix_path
end as path end as path
from cntrev as CR from cntrev as CR
inner join revision as R on (R.id = (CR.revision).id) inner join revision as R on (R.id = CR.revision)
inner join location as DL on (DL.id = (CR.revision).loc) inner join location as DL on (DL.id = CR.location)
left join origin as O on (O.id = R.origin)) left join origin as O on (O.id = R.origin))
order by date, revision, origin, path limit early_cut order by date, revision, origin, path limit early_cut
$$; $$;
create or replace function swh_provenance_relation_add_from_temp( create or replace function swh_provenance_relation_add_from_temp(
rel_table regclass, src_table regclass, dst_table regclass rel_table regclass, src_table regclass, dst_table regclass
) )
returns void returns void
language plpgsql language plpgsql
volatile volatile
as $$ as $$
declare declare
select_fields text; select_fields text;
join_location text; join_location text;
group_entries text; group_entries text;
on_conflict text; on_conflict text;
begin begin
if src_table in ('content'::regclass, 'directory'::regclass) then 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)'; join_location := 'inner join location as L on (L.path = V.path)';
group_entries := 'group by S.id'; group_entries := 'group by S.id';
on_conflict := format(' on_conflict := format('
(%s) do update (%s) do update
set %s=array( set (%s, location) = (
select distinct unnest( with pairs as (
%s.' || dst_table::text || ' || excluded.' || dst_table::text || ' 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 else
select_fields := 'D.id'; select_fields := 'D.id';
join_location := ''; join_location := '';
group_entries := ''; group_entries := '';
on_conflict := 'do nothing'; on_conflict := 'do nothing';
end if; end if;
Show All 36 Lines begin
src_field := 'prev'; src_field := 'prev';
dst_field := 'next'; dst_field := 'next';
else else
src_field := src_table::text; src_field := src_table::text;
dst_field := dst_table::text; dst_field := dst_table::text;
end if; end if;
if src_table in ('content'::regclass, 'directory'::regclass) then if src_table in ('content'::regclass, 'directory'::regclass) then
proj_unnested := 'unnest(R.' || dst_field || ') as dst'; proj_unnested := 'unnest(R.' || dst_field || ') as dst, unnest(R.location) as loc';
proj_dst_id := '(CL.dst).id'; proj_dst_id := 'CL.dst';
join_location := 'inner join location as L on (L.id = (CL.dst).loc)'; join_location := 'inner join location as L on (L.id = CL.loc)';
proj_location := 'L.path'; proj_location := 'L.path';
else else
proj_unnested := 'R.' || dst_field || ' as dst'; proj_unnested := 'R.' || dst_field || ' as dst';
proj_dst_id := 'CL.dst'; proj_dst_id := 'CL.dst';
join_location := ''; join_location := '';
proj_location := 'NULL::unix_path'; proj_location := 'NULL::unix_path';
end if; end if;
▲ Show 20 Lines • Show All 211 Lines • Show Last 20 Lines
About · Developer information · Legal