Changeset View
Changeset View
Standalone View
Standalone View
sql/upgrades/158.sql
-- SWH DB schema upgrade | -- SWH DB schema upgrade | ||||
-- from_version: 157 | -- from_version: 157 | ||||
-- to_version: 158 | -- to_version: 158 | ||||
-- description: Add the extra_headers column in the revision table | -- description: Add the extra_headers column in the revision table | ||||
-- latest schema version | -- latest schema version | ||||
insert into dbversion(version, release, description) | insert into dbversion(version, release, description) | ||||
values(158, now(), 'Work Still In Progress'); | values(158, now(), 'Work Still In Progress'); | ||||
-- Adapt the revision table for the new extra_headers column | -- Adapt the revision table for the new extra_headers column | ||||
alter table revision add column (extra_headers bytea[][]); | alter table revision add column extra_headers bytea[][]; | ||||
-- Adapt the revision_entry type for the new extra_headers attribute | -- Adapt the revision_entry type for the new extra_headers attribute | ||||
alter type revision_entry add attribute (extra_headers bytea[][]); | alter type revision_entry add attribute extra_headers bytea[][]; | ||||
ardumont: The enclosing parenthesis did not work either. | |||||
-- 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(); | ||||
Show All 14 Lines | create or replace function swh_revision_log(root_revisions bytea[], num_revs bigint default NULL) | ||||
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, r.extra_headers, t.parents, r.object_id | r.metadata, r.synthetic, t.parents, r.object_id, r.extra_headers | ||||
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; | ||||
$$; | $$; | ||||
create or replace function swh_revision_list_by_object_id( | create or replace function swh_revision_list_by_object_id( | ||||
min_excl bigint, | min_excl bigint, | ||||
max_incl bigint | max_incl bigint | ||||
) | ) | ||||
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.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, r.extra_headers, | 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 | as parents, r.object_id, r.extra_headers | ||||
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; | ||||
$$; | $$; | ||||
-- TODO: add the migration magic query... | -- TODO: add the migration magic query... |
The enclosing parenthesis did not work either.