diff --git a/sql/upgrades/102.sql b/sql/upgrades/102.sql new file mode 100644 index 0000000..d8aff2d --- /dev/null +++ b/sql/upgrades/102.sql @@ -0,0 +1,183 @@ +-- SWH DB schema upgrade +-- from_version: 101 +-- to_version: 102 +-- description: Add length field to swh_directory_*; update swh_content_missing to only hit one index + +insert into dbversion(version, release, description) + values(102, now(), 'Work In Progress'); + +drop type directory_entry cascade; +create type directory_entry as +( + dir_id sha1_git, -- id of the parent directory + type directory_entry_type, -- type of entry + target sha1_git, -- id of target + name unix_path, -- path name, relative to containing dir + perms file_perms, -- unix-like permissions + status content_status, -- visible or absent + sha1 sha1, -- content if sha1 if type is not dir + sha1_git sha1_git, -- content's sha1 git if type is not dir + sha256 sha256, -- content's sha256 if type is not dir + length bigint -- content length if type is not dir +); + + +CREATE OR REPLACE FUNCTION swh_content_missing() RETURNS SETOF content_signature + LANGUAGE plpgsql + AS $$ +begin + return query ( + select sha1, sha1_git, sha256 from tmp_content as tmp + where not exists ( + select 1 from content as c + where c.sha1 = tmp.sha1 and c.sha1_git = tmp.sha1_git and c.sha256 = tmp.sha256 + ) + ); + return; +end +$$; + +create or replace function swh_directory_walk_one(walked_dir_id sha1_git) + returns setof directory_entry + language sql + stable +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, + e.target, e.name, e.perms, NULL::content_status, + NULL::sha1, NULL::sha1_git, NULL::sha256, NULL::bigint + from ls_d + left join directory_entry_dir e on ls_d.entry_id = e.id) + union + (select dir_id, 'file'::directory_entry_type as type, + e.target, e.name, e.perms, c.status, + c.sha1, c.sha1_git, c.sha256, c.length + from ls_f + left join directory_entry_file e on ls_f.entry_id = e.id + left join content c on e.target = c.sha1_git) + union + (select dir_id, 'rev'::directory_entry_type as type, + e.target, e.name, e.perms, NULL::content_status, + NULL::sha1, NULL::sha1_git, NULL::sha256, NULL::bigint + from ls_r + left join directory_entry_rev e on ls_r.entry_id = e.id) + order by name; +$$; + +CREATE OR REPLACE FUNCTION swh_directory_walk(walked_dir_id sha1_git) RETURNS SETOF directory_entry + LANGUAGE sql STABLE + AS $$ + with recursive entries as ( + select dir_id, type, target, name, perms, status, sha1, sha1_git, + sha256, length + from swh_directory_walk_one(walked_dir_id) + union all + select dir_id, type, target, (dirname || '/' || name)::unix_path as name, + perms, status, sha1, sha1_git, sha256, length + from (select (swh_directory_walk_one(dirs.target)).*, dirs.name as dirname + from (select target, name from entries where type = 'dir') as dirs) as with_parent + ) + select dir_id, type, target, name, perms, status, sha1, sha1_git, sha256, length + from entries +$$; + +CREATE OR REPLACE FUNCTION swh_directory_walk_one(walked_dir_id sha1_git) RETURNS SETOF directory_entry + LANGUAGE sql STABLE + 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, + e.target, e.name, e.perms, NULL::content_status, + NULL::sha1, NULL::sha1_git, NULL::sha256, NULL::bigint + from ls_d + left join directory_entry_dir e on ls_d.entry_id = e.id) + union + (select dir_id, 'file'::directory_entry_type as type, + e.target, e.name, e.perms, c.status, + c.sha1, c.sha1_git, c.sha256, c.length + from ls_f + left join directory_entry_file e on ls_f.entry_id = e.id + left join content c on e.target = c.sha1_git) + union + (select dir_id, 'rev'::directory_entry_type as type, + e.target, e.name, e.perms, NULL::content_status, + NULL::sha1, NULL::sha1_git, NULL::sha256, NULL::bigint + from ls_r + left join directory_entry_rev e on ls_r.entry_id = e.id) + order by name; +$$; + +CREATE OR REPLACE FUNCTION swh_revision_walk(revision_id sha1_git) RETURNS SETOF directory_entry + LANGUAGE sql STABLE + AS $$ + select dir_id, type, target, name, perms, status, sha1, sha1_git, sha256, length + from swh_directory_walk((select directory from revision where id=revision_id)) +$$; + +COMMENT ON FUNCTION swh_revision_walk(revision_id sha1_git) IS 'Recursively list the revision targeted directory arborescence'; + +-- Find a directory entry by its path +create or replace function swh_find_directory_entry_by_path( + walked_dir_id sha1_git, + dir_or_content_path bytea[]) + returns directory_entry + language plpgsql +as $$ +declare + end_index integer; + paths bytea default ''; + path bytea; + res bytea[]; + r record; +begin + end_index := array_upper(dir_or_content_path, 1); + res[1] := walked_dir_id; + + for i in 1..end_index + loop + path := dir_or_content_path[i]; + -- concatenate path for patching the name in the result record (if we found it) + if i = 1 then + paths = path; + else + paths := paths || '/' || path; -- concatenate paths + end if; + + if i <> end_index then + select * + from swh_directory_walk_one(res[i] :: sha1_git) + where name=path + and type = 'dir' + limit 1 into r; + else + select * + from swh_directory_walk_one(res[i] :: sha1_git) + where name=path + limit 1 into r; + end if; + + -- find the path + if r is null then + return null; + else + -- store the next dir to lookup the next local path from + res[i+1] := r.target; + end if; + end loop; + + -- at this moment, r is the result. Patch its 'name' with the full path before returning it. + r.name := paths; + return r; +end +$$;