Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/sql/40-swh-func.sql
| Show First 20 Lines • Show All 447 Lines • ▼ Show 20 Lines | ( | ||||
| 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, | ||||
ardumont: indentation is off. | |||||
| extra_headers bytea[][], | |||||
| parents bytea[], | parents bytea[], | ||||
| object_id bigint | object_id bigint | ||||
| ); | ); | ||||
| -- "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.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.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.metadata, r.synthetic, r.extra_headers, t.parents, r.object_id | ||||
| 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) | 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) | ||||
| 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 | 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 | ||||
| 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 20 Lines • Show All 245 Lines • ▼ Show 20 Lines | |||||
| 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.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.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, r.extra_headers, | ||||
| 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 | as parents, r.object_id | ||||
| 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; | ||||
| $$; | $$; | ||||
| ▲ Show 20 Lines • Show All 147 Lines • Show Last 20 Lines | |||||
indentation is off.