024.sql
No OneTemporary
Actions

Size

6 KB

Subscribers

None

024.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: 23
	-- to_version: 24
	-- description:
	-- * new indexes
	-- * improve rev_find_occurrence (with new function rev_list_children)
	-- * mark relevant functions as STABLE
	-- * new function: dir_walk (recursive ls)
	-- * new function: stat_counters

	insert into dbversion(version, release, description)
	values(24, now(), 'Work In Progress');

	insert into organization(name, description, homepage)
	values('gnu',
	'GNU''s not Unix!',
	'https://gnu.org/');

	create index on revision(directory);
	create index on revision_history(parent_id);
	create index on occurrence_history(revision);
	create index on release(revision);
	create index on content(ctime);

	create or replace function swh_revision_list_children(root_revision sha1_git)
	returns setof sha1_git
	language sql
	stable
	as $$
	with recursive rev_list(id) as (
	(select id from revision where id = root_revision)
	union
	(select h.id
	from revision_history as h
	join rev_list on h.parent_id = rev_list.id)
	)
	select * from rev_list;
	$$;

	create or replace function swh_revision_find_occurrence(revision_id sha1_git)
	returns occurrence
	language plpgsql
	as $$
	declare
	occ occurrence%ROWTYPE;
	rev sha1_git;
	begin
	select origin, branch, revision
	from occurrence_history as occ_hist
	where occ_hist.revision = revision_id
	order by upper(occ_hist.validity)
	limit 1
	into occ;

	if not found then
	select origin, branch, revision
	from swh_revision_list_children(revision_id) as rev_list(sha1_git)
	left join occurrence_history occ_hist
	on rev_list.sha1_git = occ_hist.revision
	where occ_hist.origin is not null
	order by upper(occ_hist.validity)
	limit 1
	into occ;
	end if;

	return occ;
	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,
	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;
	$$;

	create or replace function swh_revision_list(root_revision sha1_git)
	returns setof sha1_git
	language sql
	stable
	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;
	$$;

	alter type revision_log_entry
	alter attribute author_name type bytea,
	alter attribute author_email type bytea,
	alter attribute committer_name type bytea,
	alter attribute committer_email type bytea;

	create or replace function swh_revision_log(root_revision sha1_git)
	returns setof revision_log_entry
	language sql
	stable
	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
	stable
	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_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
	from swh_directory_walk_one(walked_dir_id)
	union all
	select dir_id, type, target, (dirname \|\| '/' \|\| name)::unix_path as name, perms
	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
	from entries
	$$;

	create type counter as (
	label text,
	value bigint
	);

	create or replace function swh_stat_counters()
	returns setof counter
	language sql
	stable
	as $$
	select relname::text as label, reltuples::bigint as value
	from pg_class
	where oid in (
	'public.content'::regclass,
	'public.directory'::regclass,
	'public.directory_entry_dir'::regclass,
	'public.directory_entry_file'::regclass,
	'public.directory_entry_rev'::regclass,
	'public.occurrence'::regclass,
	'public.occurrence_history'::regclass,
	'public.origin'::regclass,
	'public.person'::regclass,
	'public.project'::regclass,
	'public.project_history'::regclass,
	'public.release'::regclass,
	'public.revision'::regclass,
	'public.revision_history'::regclass,
	'public.skipped_content'::regclass
	);
	$$;

File Metadata

Mime Type: text/plain
Expires: Fri, Jul 4, 11:56 AM (3 w, 2 d ago)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3243583

024.sqlNo OneTemporaryActions

024.sqlView Options

File Metadata

Event Timeline

024.sql
No OneTemporary
Actions

024.sql
View Options