Revision 6064a316fd1350b520361ae2cf15cb7e992880ee authored by Antoine R. Dumont (@ardumont) on 03 April 2019, 09:28:34 UTC, committed by Antoine R. Dumont (@ardumont) on 03 April 2019, 13:06:02 UTC
1 parent 907c4ad
018.sql
-- SWH DB schema upgrade
-- from_version: 17
-- to_version: 18
-- description: remove atime, ctime, mtime from directory_entry_*
insert into dbversion(version, release, description)
values(18, now(), 'Work In Progress');
alter table directory_entry_dir drop column atime;
alter table directory_entry_dir drop column mtime;
alter table directory_entry_dir drop column ctime;
create unique index on directory_entry_dir(target, name, perms);
alter table directory_entry_file drop column atime;
alter table directory_entry_file drop column mtime;
alter table directory_entry_file drop column ctime;
create unique index on directory_entry_file(target, name, perms);
alter table directory_entry_rev drop column atime;
alter table directory_entry_rev drop column mtime;
alter table directory_entry_rev drop column ctime;
create unique index on directory_entry_rev(target, name, perms);
create or replace function swh_directory_entry_add(typ directory_entry_type)
returns void
language plpgsql
as $$
begin
execute format('
insert into directory_entry_%1$s (target, name, perms)
select distinct t.target, t.name, t.perms
from tmp_directory_entry_%1$s t
where not exists (
select 1
from directory_entry_%1$s i
where t.target = i.target and t.name = i.name and t.perms = i.perms)
', typ);
execute format('
with new_entries as (
select t.dir_id, array_agg(i.id) as entries
from tmp_directory_entry_%1$s t
inner join directory_entry_%1$s i
using (target, name, perms)
group by t.dir_id
)
update directory as d
set %1$s_entries = new_entries.entries
from new_entries
where d.id = new_entries.dir_id
', typ);
return;
end
$$;
alter type directory_entry drop attribute atime;
alter type directory_entry drop attribute mtime;
alter type directory_entry drop attribute ctime;
create or replace function swh_directory_walk_one(walked_dir_id sha1_git)
returns setof directory_entry
language sql
as $$
with dir as (
select id as dir_id, dir_entries, file_entries, rev_entries
from directory
where id = walked_dir_id),
ls_d as (select dir_id, unnest(dir_entries) as entry_id from dir),
ls_f as (select dir_id, unnest(file_entries) as entry_id from dir),
ls_r as (select dir_id, unnest(rev_entries) as entry_id from dir)
(select dir_id, 'dir'::directory_entry_type as type,
target, name, perms
from ls_d
left join directory_entry_dir d on ls_d.entry_id = d.id)
union
(select dir_id, 'file'::directory_entry_type as type,
target, name, perms
from ls_f
left join directory_entry_file d on ls_f.entry_id = d.id)
union
(select dir_id, 'rev'::directory_entry_type as type,
target, name, perms
from ls_r
left join directory_entry_rev d on ls_r.entry_id = d.id)
order by name;
$$;
![swh spinner](/static/img/swh-spinner.gif)
Computing file changes ...