Changeset View
Changeset View
Standalone 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 |