Differential D6848 Diff 24896 swh/storage/sql/40-funcs.sql

Changeset View

Standalone View

swh/storage/sql/40-funcs.sql

Show First 20 Lines • Show All 258 Lines • ▼ Show 20 Lines	create or replace function swh_directory_add()
returns void		returns void
language plpgsql		language plpgsql
as $$		as $$
begin		begin
perform swh_directory_entry_add('file');		perform swh_directory_entry_add('file');
perform swh_directory_entry_add('dir');		perform swh_directory_entry_add('dir');
perform swh_directory_entry_add('rev');		perform swh_directory_entry_add('rev');

insert into directory		insert into directory (id, dir_entries, file_entries, rev_entries, raw_manifest)
select * from tmp_directory t		select id, dir_entries, file_entries, rev_entries, raw_manifest from tmp_directory t
where not exists (		where not exists (
select 1 from directory d		select 1 from directory d
where d.id = t.id);		where d.id = t.id);

return;		return;
end		end
$$;		$$;

▲ Show 20 Lines • Show All 194 Lines • ▼ Show 20 Lines

-- Detailed entry for a revision		-- Detailed entry for a revision
create type revision_entry as		create type revision_entry as
(		(
id sha1_git,		id sha1_git,
date timestamptz,		date timestamptz,
date_offset smallint,		date_offset smallint,
date_neg_utc_offset boolean,		date_neg_utc_offset boolean,
		date_offset_bytes bytea,
committer_date timestamptz,		committer_date timestamptz,
committer_date_offset smallint,		committer_date_offset smallint,
committer_date_neg_utc_offset boolean,		committer_date_neg_utc_offset boolean,
		committer_date_offset_bytes bytea,
type revision_type,		type revision_type,
directory sha1_git,		directory sha1_git,
message bytea,		message bytea,
author_id bigint,		author_id bigint,
author_fullname bytea,		author_fullname bytea,
author_name bytea,		author_name bytea,
author_email bytea,		author_email bytea,
committer_id bigint,		committer_id bigint,
committer_fullname bytea,		committer_fullname bytea,
committer_name bytea,		committer_name bytea,
committer_email bytea,		committer_email bytea,
metadata jsonb,		metadata jsonb,
synthetic boolean,		synthetic boolean,
parents bytea[],		parents bytea[],
object_id bigint,		object_id bigint,
extra_headers bytea[][]		extra_headers bytea[][],
		raw_manifest bytea
);		);


-- "git style" revision log. Similar to swh_revision_list(), but returning all		-- "git style" revision log. Similar to swh_revision_list(), but returning all
-- information associated to each revision, and expanding authors/committers		-- information associated to each revision, and expanding authors/committers
create or replace function swh_revision_log(root_revisions bytea[], num_revs bigint default NULL)		create or replace function swh_revision_log(root_revisions bytea[], num_revs bigint default NULL)
returns setof revision_entry		returns setof revision_entry
language sql		language sql
stable		stable
as $$		as $$
select t.id, r.date, r.date_offset, r.date_neg_utc_offset,		select t.id, r.date, r.date_offset, r.date_neg_utc_offset, r.date_offset_bytes,
r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset,		r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset, r.committer_date_offset_bytes,
r.type, r.directory, r.message,		r.type, r.directory, r.message,
a.id, a.fullname, a.name, a.email,		a.id, a.fullname, a.name, a.email,
c.id, c.fullname, c.name, c.email,		c.id, c.fullname, c.name, c.email,
r.metadata, r.synthetic, t.parents, r.object_id, r.extra_headers		r.metadata, r.synthetic, t.parents, r.object_id, r.extra_headers,
		r.raw_manifest
from swh_revision_list(root_revisions, num_revs) as t		from swh_revision_list(root_revisions, num_revs) as t
left join revision r on t.id = r.id		left join revision r on t.id = r.id
left join person a on a.id = r.author		left join person a on a.id = r.author
left join person c on c.id = r.committer;		left join person c on c.id = r.committer;
$$;		$$;


-- Detailed entry for a release		-- Detailed entry for a release
Show All 40 Lines
-- Create entries in revision from tmp_revision		-- Create entries in revision from tmp_revision
create or replace function swh_revision_add()		create or replace function swh_revision_add()
returns void		returns void
language plpgsql		language plpgsql
as $$		as $$
begin		begin
perform swh_person_add_from_revision();		perform swh_person_add_from_revision();

insert into revision (id, date, date_offset, date_neg_utc_offset, committer_date, committer_date_offset, committer_date_neg_utc_offset, type, directory, message, author, committer, metadata, synthetic, extra_headers)		insert into revision (id, date, date_offset, date_neg_utc_offset, date_offset_bytes, committer_date, committer_date_offset, committer_date_neg_utc_offset, committer_date_offset_bytes, type, directory, message, author, committer, metadata, synthetic, extra_headers, raw_manifest)
select t.id, t.date, t.date_offset, t.date_neg_utc_offset, t.committer_date, t.committer_date_offset, t.committer_date_neg_utc_offset, t.type, t.directory, t.message, a.id, c.id, t.metadata, t.synthetic, t.extra_headers		select t.id, t.date, t.date_offset, t.date_neg_utc_offset, t.date_offset_bytes, t.committer_date, t.committer_date_offset, t.committer_date_neg_utc_offset, t.committer_date_offset_bytes, t.type, t.directory, t.message, a.id, c.id, t.metadata, t.synthetic, t.extra_headers, t.raw_manifest
from tmp_revision t		from tmp_revision t
left join person a on a.fullname = t.author_fullname		left join person a on a.fullname = t.author_fullname
left join person c on c.fullname = t.committer_fullname;		left join person c on c.fullname = t.committer_fullname;
return;		return;
end		end
$$;		$$;


Show All 38 Lines
-- Create entries in release from tmp_release		-- Create entries in release from tmp_release
create or replace function swh_release_add()		create or replace function swh_release_add()
returns void		returns void
language plpgsql		language plpgsql
as $$		as $$
begin		begin
perform swh_person_add_from_release();		perform swh_person_add_from_release();

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, date_offset_bytes, name, comment, author, synthetic, raw_manifest)
select distinct 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 distinct t.id, t.target, t.target_type, t.date, t.date_offset, t.date_neg_utc_offset, t.date_offset_bytes, t.name, t.comment, a.id, t.synthetic, t.raw_manifest
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
where not exists (select 1 from release where t.id = release.id);		where not exists (select 1 from release where t.id = release.id);
return;		return;
end		end
$$;		$$;


▲ Show 20 Lines • Show All 209 Lines • ▼ Show 20 Lines	)
returns setof revision_entry		returns setof revision_entry
language sql		language sql
stable		stable
as $$		as $$
with revs as (		with revs as (
select * from revision		select * from revision
where object_id > min_excl and object_id <= max_incl		where object_id > min_excl and object_id <= max_incl
)		)
select r.id, r.date, r.date_offset, r.date_neg_utc_offset,		select r.id, r.date, r.date_offset, r.date_neg_utc_offset, r.date_offset_bytes,
r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset,		r.committer_date, r.committer_date_offset, r.committer_date_neg_utc_offset, r.committer_date_offset_bytes,
r.type, r.directory, r.message,		r.type, r.directory, r.message,
a.id, a.fullname, a.name, a.email, c.id, c.fullname, c.name, c.email, r.metadata, r.synthetic,		a.id, a.fullname, a.name, a.email, c.id, c.fullname, c.name, c.email, r.metadata, r.synthetic,
array(select rh.parent_id::bytea from revision_history rh where rh.id = r.id order by rh.parent_rank)		array(select rh.parent_id::bytea from revision_history rh where rh.id = r.id order by rh.parent_rank)
as parents, r.object_id, r.extra_headers		as parents, r.object_id, r.extra_headers, r.raw_manifest
from revs r		from revs r
left join person a on a.id = r.author		left join person a on a.id = r.author
left join person c on c.id = r.committer		left join person c on c.id = r.committer
order by r.object_id;		order by r.object_id;
$$;		$$;

create or replace function swh_release_list_by_object_id(		create or replace function swh_release_list_by_object_id(
min_excl bigint,		min_excl bigint,
▲ Show 20 Lines • Show All 145 Lines • Show Last 20 Lines