Differential D2157 Diff 7339 swh/storage/sql/40-swh-func.sql

Changeset View

Standalone View

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

Show First 20 Lines • Show All 687 Lines • ▼ Show 20 Lines	begin
return;		return;
end		end
$$;		$$;


-- add a new origin_visit for origin origin_id at date.		-- add a new origin_visit for origin origin_id at date.
--		--
-- Returns the new visit id.		-- Returns the new visit id.
create or replace function swh_origin_visit_add(origin_id bigint, date timestamptz, type text)		create or replace function swh_origin_visit_add(origin_url text, date timestamptz, type text)
returns bigint		returns bigint
language sql		language sql
as $$		as $$
with last_known_visit as (		with origin_id as (
		select id
		from origin
		where url = origin_url
		), last_known_visit as (
select coalesce(max(visit), 0) as visit		select coalesce(max(visit), 0) as visit
from origin_visit		from origin_visit
where origin = origin_id		where origin = (select id from 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 ((select id from 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 sha1_git)		create or replace function swh_snapshot_add(snapshot_id sha1_git)
returns void		returns void
language plpgsql		language plpgsql
as $$		as $$
declare		declare
▲ Show 20 Lines • Show All 108 Lines • ▼ Show 20 Lines	as $$
from path		from path
join directory_entry_dir as dir_entry_d on dir_entry_d.target = path.dir_id		join directory_entry_dir as dir_entry_d on dir_entry_d.target = path.dir_id
join directory as dir on dir.dir_entries @> array[dir_entry_d.id]		join directory as dir on dir.dir_entries @> array[dir_entry_d.id]
limit 1)		limit 1)
)		)
select dir_id, name from path order by depth desc limit 1;		select dir_id, name from path order by depth desc limit 1;
$$;		$$;

-- Find the visit of origin id closest to date visit_date		-- Find the visit of origin closest to date visit_date
-- Breaks ties by selecting the largest visit id		-- Breaks ties by selecting the largest visit id
create or replace function swh_visit_find_by_date(origin bigint, visit_date timestamptz default NOW())		create or replace function swh_visit_find_by_date(origin_url text, visit_date timestamptz default NOW())
returns origin_visit		returns setof origin_visit
language sql		language plpgsql
stable		stable
as $$		as $$
		declare
		origin_id bigint;
		begin
		select id into origin_id from origin where url=origin_url;
		return query
with closest_two_visits as ((		with closest_two_visits as ((
select ov, (date - visit_date), visit as interval		select ov, (date - visit_date), visit as interval
from origin_visit ov		from origin_visit ov
where ov.origin = origin		where ov.origin = origin_id
and ov.date >= visit_date		and ov.date >= visit_date
order by ov.date asc, ov.visit desc		order by ov.date asc, ov.visit desc
limit 1		limit 1
) union (		) union (
select ov, (visit_date - date), visit as interval		select ov, (visit_date - date), visit as interval
from origin_visit ov		from origin_visit ov
where ov.origin = origin		where ov.origin = origin_id
and ov.date < visit_date		and ov.date < visit_date
order by ov.date desc, ov.visit desc		order by ov.date desc, ov.visit desc
limit 1		limit 1
)) select (ov).* from closest_two_visits order by interval, visit limit 1		)) select (ov).* from closest_two_visits order by interval, visit limit 1;
$$;		end

-- Find the visit of origin id closest to date visit_date
create or replace function swh_visit_get(origin bigint)
returns origin_visit
language sql
stable
as $$
select *
from origin_visit
where origin=origin
order by date desc
$$;		$$;

-- Object listing by object_id		-- Object listing by object_id

create or replace function swh_content_list_by_object_id(		create or replace function swh_content_list_by_object_id(
min_excl bigint,		min_excl bigint,
max_incl bigint		max_incl bigint
)		)
▲ Show 20 Lines • Show All 49 Lines • ▼ Show 20 Lines	as $$
order by r.object_id;		order by r.object_id;
$$;		$$;


-- end revision_metadata functions		-- end revision_metadata functions
-- origin_metadata functions		-- origin_metadata functions
create type origin_metadata_signature as (		create type origin_metadata_signature as (
id bigint,		id bigint,
origin_id bigint,		origin_url text,
discovery_date timestamptz,		discovery_date timestamptz,
tool_id bigint,		tool_id bigint,
metadata jsonb,		metadata jsonb,
provider_id integer,		provider_id integer,
provider_name text,		provider_name text,
provider_type text,		provider_type text,
provider_url text		provider_url text
);		);
create or replace function swh_origin_metadata_get_by_origin(		create or replace function swh_origin_metadata_get_by_origin(
origin integer)		origin text)
returns setof origin_metadata_signature		returns setof origin_metadata_signature
language sql		language sql
stable		stable
as $$		as $$
select om.id as id, origin_id, discovery_date, tool_id, om.metadata,		select om.id as id, o.url as origin_url, discovery_date, tool_id, om.metadata,
mp.id as provider_id, provider_name, provider_type, provider_url		mp.id as provider_id, provider_name, provider_type, provider_url
from origin_metadata as om		from origin_metadata as om
inner join metadata_provider mp on om.provider_id = mp.id		inner join metadata_provider mp on om.provider_id = mp.id
where om.origin_id = origin		inner join origin o on om.origin_id = o.id
		where o.url = origin
order by discovery_date desc;		order by discovery_date desc;
$$;		$$;

create or replace function swh_origin_metadata_get_by_provider_type(		create or replace function swh_origin_metadata_get_by_provider_type(
origin integer,		origin_url text,
type text)		provider_type text)
returns setof origin_metadata_signature		returns setof origin_metadata_signature
language sql		language sql
stable		stable
as $$		as $$
select om.id as id, origin_id, discovery_date, tool_id, om.metadata,		select om.id as id, o.url as origin_url, discovery_date, tool_id, om.metadata,
mp.id as provider_id, provider_name, provider_type, provider_url		mp.id as provider_id, provider_name, provider_type, provider_url
from origin_metadata as om		from origin_metadata as om
inner join metadata_provider mp on om.provider_id = mp.id		inner join metadata_provider mp on om.provider_id = mp.id
where om.origin_id = origin		inner join origin o on om.origin_id = o.id
and mp.provider_type = type		where o.url = origin_url
		and mp.provider_type = provider_type
order by discovery_date desc;		order by discovery_date desc;
$$;		$$;
-- end origin_metadata functions		-- end origin_metadata functions

-- add tmp_tool entries to tool,		-- add tmp_tool entries to tool,
-- skipping duplicates if any.		-- skipping duplicates if any.
--		--
-- operates in bulk: 0. create temporary tmp_tool, 1. COPY to		-- operates in bulk: 0. create temporary tmp_tool, 1. COPY to
▲ Show 20 Lines • Show All 147 Lines • Show Last 20 Lines