diff --git a/sql/upgrades/017.sql b/sql/upgrades/017.sql index 937883d9..d7dcdbd0 100644 --- a/sql/upgrades/017.sql +++ b/sql/upgrades/017.sql @@ -1,68 +1,208 @@ -- SWH DB schema upgrade -- from_version: 16 -- to_version: 17 --- description: improve swh_content_missing, generic shw_directory_entry_add +-- description: improve swh_content_missing, generic shw_directory_entry_add, +-- LANGUAGE sql where possible, better API for swh_content_find_* 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)) union (select sha1, sha1_git, sha256 from tmp_content as tmp where not exists (select 1 from content as c where c.sha1_git = tmp.sha1_git)) union (select sha1, sha1_git, sha256 from tmp_content as tmp where not exists (select 1 from content as c where c.sha256 = tmp.sha256)); return; end $$; 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, atime, mtime, ctime) select distinct t.target, t.name, t.perms, t.atime, t.mtime, t.ctime 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 and t.atime is not distinct from i.atime and t.mtime is not distinct from i.mtime and t.ctime is not distinct from i.ctime) ', 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 on t.target = i.target and t.name = i.name and t.perms = i.perms and t.atime is not distinct from i.atime and t.mtime is not distinct from i.mtime and t.ctime is not distinct from i.ctime 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 $$; drop function swh_directory_entry_dir_add (); drop function swh_directory_entry_file_add (); drop function swh_directory_entry_rev_add (); + +create or replace function swh_mktemp_revision() + returns void + language sql +as $$ + create temporary table tmp_revision ( + like revision including defaults, + author_name text not null default '', + author_email text not null default '', + committer_name text not null default '', + committer_email text not null default '' + ) on commit drop; + alter table tmp_revision drop column author; + alter table tmp_revision drop column committer; +$$; + +create or replace function swh_mktemp_release() + returns void + language sql +as $$ + create temporary table tmp_release ( + like release including defaults, + author_name text not null default '', + author_email text not null default '' + ) on commit drop; + alter table tmp_release drop column author; +$$; + +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, atime, mtime, ctime + 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, atime, mtime, ctime + 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, atime, mtime, ctime + from ls_r + left join directory_entry_rev d on ls_r.entry_id = d.id) + order by name; +$$; + +create or replace function swh_revision_list(root_revision sha1_git) + returns setof sha1_git + language sql +as $$ + with recursive rev_list(id) as ( + (select id from revision where id = root_revision) + union + (select parent_id + from revision_history as h + join rev_list on h.id = rev_list.id) + ) + select * from rev_list; +$$; + +create or replace function swh_revision_log(root_revision sha1_git) + returns setof revision_log_entry + language sql +as $$ + select revision.id, date, date_offset, + committer_date, committer_date_offset, + type, directory, message, + author.name as author_name, author.email as author_email, + committer.name as committer_name, committer.email as committer_email + from swh_revision_list(root_revision) as rev_list + join revision on revision.id = rev_list + join person as author on revision.author = author.id + join person as committer on revision.committer = committer.id; +$$; + +create or replace function swh_content_find_directory(content_id sha1) + returns content_dir + language sql +as $$ + with recursive path as ( + (select dir.id as dir_id, dir_entry_f.name as name, 0 as depth + from directory_entry_file as dir_entry_f + join content on content.sha1_git = dir_entry_f.target + join directory as dir on dir.file_entries @> array[dir_entry_f.id] + where content.sha1 = content_id + limit 1) + union all + (select dir.id as dir_id, + (dir_entry_d.name || '/' || path.name)::unix_path as name, + path.depth + 1 + from path + 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] + limit 1) + ) + select dir_id, name from path order by depth desc limit 1; +$$; + +create or replace function swh_content_find_occurrence(content_id sha1) + returns content_occurrence + language plpgsql +as $$ +declare + dir content_dir; + rev sha1_git; + occ occurrence%ROWTYPE; + coc content_occurrence; +begin + select * from swh_content_find_directory(content_id) + into dir; + if not found then return null; end if; + + select id from revision where directory = dir.directory + limit 1 + into rev; + if not found then return null; end if; + + select * from swh_revision_find_occurrence(rev) + into occ; + if not found then return null; end if; + + select origin.type, origin.url, occ.branch, rev, dir.path + from origin + where origin.id = occ.origin + into coc; + + return coc; +end +$$;