Changeset View
Changeset View
Standalone View
Standalone View
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 |