Page MenuHomeSoftware Heritage
Differential D6165 Diff 22333 swh/provenance/sql/40-funcs.sql

Changeset View

Standalone View

View Options

swh/provenance/sql/40-funcs.sql

Show First 20 Lines • Show All 83 Lines • ▼ Show 20 Lines)
language plpgsql language plpgsql
volatile volatile
as $$ as $$
declare declare
select_fields text; select_fields text;
join_location text; join_location text;
begin begin
if src_table in ('content'::regclass, 'directory'::regclass) then if src_table in ('content'::regclass, 'directory'::regclass) then
lock table only location;
insert into location(path) insert into location(path)
select V.path select V.path
from unnest(rel_data) as V from unnest(rel_data) as V
on conflict (path) do nothing; on conflict (path) do nothing;
select_fields := 'D.id, L.id'; select_fields := 'D.id, 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)';
else else
select_fields := 'D.id'; select_fields := 'D.id';
join_location := ''; join_location := '';
end if; end if;
execute format( execute format(
'lock table only %s; 'insert into %s
insert into %s
select S.id, ' || select_fields || ' select S.id, ' || select_fields || '
from unnest($1) as V from unnest($1) as V
inner join %s as S on (S.sha1 = V.src) inner join %s as S on (S.sha1 = V.src)
inner join %s as D on (D.sha1 = V.dst) inner join %s as D on (D.sha1 = V.dst)
' || join_location || ' ' || join_location || '
on conflict do nothing', on conflict do nothing',
rel_table, rel_table, src_table, dst_table rel_table, src_table, dst_table
) using rel_data; ) using rel_data;
end; end;
$$; $$;
create or replace function swh_provenance_relation_get( create or replace function swh_provenance_relation_get(
rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[] rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
) )
returns table ( returns table (
▲ Show 20 Lines • Show All 116 Lines • ▼ Show 20 Linescreate or replace function swh_provenance_relation_add(
rel_table regclass, src_table regclass, dst_table regclass, rel_data rel_row[] rel_table regclass, src_table regclass, dst_table regclass, rel_data rel_row[]
) )
returns void returns void
language plpgsql language plpgsql
volatile volatile
as $$ as $$
begin begin
execute format( execute format(
'lock table only %s; 'insert into %s
insert into %s
select S.id, D.id select S.id, D.id
from unnest($1) as V from unnest($1) as V
inner join %s as S on (S.sha1 = V.src) inner join %s as S on (S.sha1 = V.src)
inner join %s as D on (D.sha1 = V.dst) inner join %s as D on (D.sha1 = V.dst)
on conflict do nothing', on conflict do nothing',
rel_table, rel_table, src_table, dst_table rel_table, src_table, dst_table
) using rel_data; ) using rel_data;
end; end;
$$; $$;
create or replace function swh_provenance_relation_get( create or replace function swh_provenance_relation_get(
rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[] rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
) )
returns table ( returns table (
▲ Show 20 Lines • Show All 144 Lines • ▼ Show 20 Lines
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
lock table only location;
insert into location(path) insert into location(path)
select V.path select V.path
from unnest(rel_data) as V from unnest(rel_data) as V
on conflict (path) do nothing; on conflict (path) do nothing;
select_fields := 'array_agg((D.id, L.id)::rel_dst)'; select_fields := 'array_agg((D.id, L.id)::rel_dst)';
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';
Show All 9 Lines begin
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;
execute format( execute format(
'lock table only %s; 'insert into %s
insert into %s
select S.id, ' || select_fields || ' select S.id, ' || select_fields || '
from unnest($1) as V from unnest($1) as V
inner join %s as S on (S.sha1 = V.src) inner join %s as S on (S.sha1 = V.src)
inner join %s as D on (D.sha1 = V.dst) inner join %s as D on (D.sha1 = V.dst)
' || join_location || ' ' || join_location || '
' || group_entries || ' ' || group_entries || '
on conflict ' || on_conflict, on conflict ' || on_conflict,
rel_table, rel_table, src_table, dst_table rel_table, src_table, dst_table
) using rel_data; ) using rel_data;
end; end;
$$; $$;
create or replace function swh_provenance_relation_get( create or replace function swh_provenance_relation_get(
rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[] rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
) )
returns table ( returns table (
▲ Show 20 Lines • Show All 167 Lines • ▼ Show 20 Lines begin
); );
else else
select_fields := 'D.id'; select_fields := 'D.id';
group_entries := ''; group_entries := '';
on_conflict := 'do nothing'; on_conflict := 'do nothing';
end if; end if;
execute format( execute format(
'lock table only %s; 'insert into %s
insert into %s
select S.id, ' || select_fields || ' select S.id, ' || select_fields || '
from unnest($1) as V from unnest($1) as V
inner join %s as S on (S.sha1 = V.src) inner join %s as S on (S.sha1 = V.src)
inner join %s as D on (D.sha1 = V.dst) inner join %s as D on (D.sha1 = V.dst)
' || group_entries || ' ' || group_entries || '
on conflict ' || on_conflict, on conflict ' || on_conflict,
rel_table, rel_table, src_table, dst_table rel_table, src_table, dst_table
) using rel_data; ) using rel_data;
end; end;
$$; $$;
create or replace function swh_provenance_relation_get( create or replace function swh_provenance_relation_get(
rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[] rel_table regclass, src_table regclass, dst_table regclass, filter rel_flt, sha1s sha1_git[]
) )
returns table ( returns table (
▲ Show 20 Lines • Show All 57 Lines • Show Last 20 Lines
About · Developer information · Legal