Page MenuHomeSoftware Heritage
Files F9343761

100.sql
No OneTemporary
Actions

100.sql
View Options

-- SWH DB schema upgrade
-- from_version: 99
-- to_version: 100
-- description: update swh_visit_find_by_date and swh_occurrence_get_by to return sensible results
insert into dbversion(version, release, description)
values(100, now(), 'Work In Progress');
CREATE OR REPLACE FUNCTION swh_occurrence_get_by(origin_id bigint, branch_name bytea = NULL::bytea, "date" timestamp with time zone = NULL::timestamp with time zone) RETURNS SETOF occurrence_history
LANGUAGE plpgsql
AS $$
declare
filters text[] := array[] :: text[]; -- AND-clauses used to filter content
visit_id bigint;
q text;
begin
if origin_id is null then
raise exception 'Needs an origin_id to get an occurrence.';
end if;
filters := filters || format('origin = %L', origin_id);
if branch_name is not null then
filters := filters || format('branch = %L', branch_name);
end if;
if date is not null then
select visit from swh_visit_find_by_date(origin_id, date) into visit_id;
else
select visit from origin_visit where origin = origin_id order by origin_visit.date desc limit 1 into visit_id;
end if;
if visit_id is null then
return;
end if;
filters := filters || format('%L = any(visits)', visit_id);
q = format('select * from occurrence_history where %s',
array_to_string(filters, ' and '));
return query execute q;
end
$$;
CREATE OR REPLACE FUNCTION swh_visit_find_by_date(origin bigint, visit_date timestamp with time zone = now()) RETURNS origin_visit
LANGUAGE sql STABLE
AS $$
with closest_two_visits as ((
select ov, (date - visit_date) as interval
from origin_visit ov
where ov.origin = origin
and ov.date >= visit_date
order by ov.date asc
limit 1
) union (
select ov, (visit_date - date) as interval
from origin_visit ov
where ov.origin = origin
and ov.date < visit_date
order by ov.date desc
limit 1
)) select (ov).* from closest_two_visits order by interval limit 1
$$;

File Metadata

Mime Type
text/plain
Expires
Fri, Jul 4, 1:49 PM (4 d, 6 h ago)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
3284957

Event Timeline

About · Developer information · Legal