Changeset View
Changeset View
Standalone View
Standalone View
sql/swh-func.sql
Show First 20 Lines • Show All 719 Lines • ▼ Show 20 Lines | begin | ||||
insert into release (id, target, target_type, date, date_offset, date_neg_utc_offset, name, comment, author, synthetic) | insert into release (id, target, target_type, date, date_offset, date_neg_utc_offset, name, comment, author, synthetic) | ||||
select t.id, t.target, t.target_type, t.date, t.date_offset, t.date_neg_utc_offset, t.name, t.comment, a.id, t.synthetic | select t.id, t.target, t.target_type, t.date, t.date_offset, t.date_neg_utc_offset, t.name, t.comment, a.id, t.synthetic | ||||
from tmp_release t | from tmp_release t | ||||
left join person a on a.fullname = t.author_fullname; | left join person a on a.fullname = t.author_fullname; | ||||
return; | return; | ||||
end | end | ||||
$$; | $$; | ||||
create or replace function swh_occurrence_update_for_origin(origin_id bigint) | |||||
returns void | |||||
language sql | |||||
as $$ | |||||
delete from occurrence where origin = origin_id; | |||||
insert into occurrence (origin, branch, target, target_type) | |||||
select origin, branch, target, target_type | |||||
from occurrence_history | |||||
where origin = origin_id and | |||||
(select visit from origin_visit | |||||
where origin = origin_id | |||||
order by date desc | |||||
limit 1) = any(visits); | |||||
$$; | |||||
create or replace function swh_occurrence_update_all() | |||||
returns void | |||||
language plpgsql | |||||
as $$ | |||||
declare | |||||
origin_id origin.id%type; | |||||
begin | |||||
for origin_id in | |||||
select distinct id from origin | |||||
loop | |||||
perform swh_occurrence_update_for_origin(origin_id); | |||||
end loop; | |||||
return; | |||||
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) | create or replace function swh_origin_visit_add(origin_id bigint, date timestamptz) | ||||
returns bigint | returns bigint | ||||
language sql | language sql | ||||
as $$ | as $$ | ||||
Show All 34 Lines | set visits = array(select unnest(occurrence_history.visits) as e | ||||
order by e) | order by e) | ||||
from occurrences_to_update | from occurrences_to_update | ||||
where occurrence_history.object_id = occurrences_to_update.object_id | where occurrence_history.object_id = occurrences_to_update.object_id | ||||
) | ) | ||||
insert into occurrence_history (origin, branch, target, target_type, visits) | insert into occurrence_history (origin, branch, target, target_type, visits) | ||||
select origin, branch, target, target_type, ARRAY[visit] | select origin, branch, target, target_type, ARRAY[visit] | ||||
from occurrence_history_id_visit | from occurrence_history_id_visit | ||||
where object_id is null; | where object_id is null; | ||||
-- update occurrence | |||||
for origin_id in | |||||
select distinct origin from tmp_occurrence_history | |||||
loop | |||||
perform swh_occurrence_update_for_origin(origin_id); | |||||
end loop; | |||||
return; | return; | ||||
end | end | ||||
$$; | $$; | ||||
create or replace function swh_snapshot_add(origin bigint, visit bigint, snapshot_id snapshot.id%type) | create or replace function swh_snapshot_add(origin bigint, visit bigint, snapshot_id snapshot.id%type) | ||||
returns void | returns void | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
▲ Show 20 Lines • Show All 454 Lines • ▼ Show 20 Lines | as $$ | ||||
select object_type as label, value as value | select object_type as label, value as value | ||||
from object_counts | from object_counts | ||||
where object_type in ( | where object_type in ( | ||||
'content', | 'content', | ||||
'directory', | 'directory', | ||||
'directory_entry_dir', | 'directory_entry_dir', | ||||
'directory_entry_file', | 'directory_entry_file', | ||||
'directory_entry_rev', | 'directory_entry_rev', | ||||
'occurrence', | |||||
'occurrence_history', | 'occurrence_history', | ||||
'origin', | 'origin', | ||||
'origin_visit', | 'origin_visit', | ||||
'person', | 'person', | ||||
'entity', | 'entity', | ||||
'entity_history', | 'entity_history', | ||||
'release', | 'release', | ||||
'revision', | 'revision', | ||||
▲ Show 20 Lines • Show All 99 Lines • Show Last 20 Lines |