Differential D1879 Diff 6385 swh/storage/sql/40-swh-func.sql

Changeset View

Standalone View

swh/storage/sql/40-swh-func.sql

Show First 20 Lines • Show All 701 Lines • ▼ Show 20 Lines	with last_known_visit as (
from origin_visit		from origin_visit
where origin = origin_id		where origin = origin_id
)		)
insert into origin_visit (origin, date, type, visit, status)		insert into origin_visit (origin, date, type, visit, status)
values (origin_id, date, type, (select visit from last_known_visit) + 1, 'ongoing')		values (origin_id, date, type, (select visit from last_known_visit) + 1, 'ongoing')
returning visit;		returning visit;
$$;		$$;

create or replace function swh_snapshot_add(snapshot_id snapshot.id%type)		create or replace function swh_snapshot_add(snapshot_id sha1_git)
returns void		returns void
language plpgsql		language plpgsql
as $$		as $$
declare		declare
snapshot_object_id snapshot.object_id%type;		snapshot_object_id snapshot.object_id%type;
begin		begin
select object_id from snapshot where id = snapshot_id into snapshot_object_id;		select object_id from snapshot where id = snapshot_id into snapshot_object_id;
if snapshot_object_id is null then		if snapshot_object_id is null then
Show All 28 Lines

create type snapshot_result as (		create type snapshot_result as (
snapshot_id sha1_git,		snapshot_id sha1_git,
name bytea,		name bytea,
target bytea,		target bytea,
target_type snapshot_target		target_type snapshot_target
);		);

create or replace function swh_snapshot_get_by_id(id snapshot.id%type,		create or replace function swh_snapshot_get_by_id(id sha1_git,
branches_from bytea default '', branches_count bigint default null,		branches_from bytea default '', branches_count bigint default null,
target_types snapshot_target[] default NULL)		target_types snapshot_target[] default NULL)
returns setof snapshot_result		returns setof snapshot_result
language sql		language sql
stable		stable
as $$		as $$
select		select
swh_snapshot_get_by_id.id as snapshot_id, name, target, target_type		swh_snapshot_get_by_id.id as snapshot_id, name, target, target_type
from snapshot_branches		from snapshot_branches
inner join snapshot_branch on snapshot_branches.branch_id = snapshot_branch.object_id		inner join snapshot_branch on snapshot_branches.branch_id = snapshot_branch.object_id
where snapshot_id = (select object_id from snapshot where snapshot.id = swh_snapshot_get_by_id.id)		where snapshot_id = (select object_id from snapshot where snapshot.id = swh_snapshot_get_by_id.id)
and (target_types is null or target_type = any(target_types))		and (target_types is null or target_type = any(target_types))
and name >= branches_from		and name >= branches_from
order by name limit branches_count		order by name limit branches_count
$$;		$$;

create type snapshot_size as (		create type snapshot_size as (
target_type snapshot_target,		target_type snapshot_target,
count bigint		count bigint
);		);

create or replace function swh_snapshot_count_branches(id snapshot.id%type)		create or replace function swh_snapshot_count_branches(id sha1_git)
returns setof snapshot_size		returns setof snapshot_size
language sql		language sql
stable		stable
as $$		as $$
SELECT target_type, count(name)		SELECT target_type, count(name)
from swh_snapshot_get_by_id(swh_snapshot_count_branches.id)		from swh_snapshot_get_by_id(swh_snapshot_count_branches.id)
group by target_type;		group by target_type;
$$;		$$;
▲ Show 20 Lines • Show All 335 Lines • Show Last 20 Lines