Changeset View
Changeset View
Standalone View
Standalone View
sql/upgrades/146.sql
- This file was added.
-- SWH DB schema upgrade | |||||
-- from_version: 145 | |||||
-- to_version: 146 | |||||
-- description: Improve query on origin_visit | |||||
-- latest schema version | |||||
insert into dbversion(version, release, description) | |||||
values(146, now(), 'Work In Progress'); | |||||
-- create a temporary table called tmp_TBLNAME, mimicking existing table | |||||
-- TBLNAME | |||||
-- | |||||
-- Args: | |||||
-- tblname: name of the table to mimic | |||||
create or replace function swh_mktemp(tblname regclass) | |||||
returns void | |||||
language plpgsql | |||||
as $$ | |||||
begin | |||||
execute format(' | |||||
create temporary table if not exists tmp_%1$I | |||||
(like %1$I including defaults) | |||||
on commit delete rows; | |||||
alter table tmp_%1$I drop column if exists object_id; | |||||
', tblname); | |||||
return; | |||||
end | |||||
$$; | |||||
-- create a temporary table for directory entries called tmp_TBLNAME, | |||||
-- mimicking existing table TBLNAME with an extra dir_id (sha1_git) | |||||
-- column, and dropping the id column. | |||||
-- | |||||
-- This is used to create the tmp_directory_entry_<foo> tables. | |||||
-- | |||||
-- Args: | |||||
-- tblname: name of the table to mimic | |||||
create or replace function swh_mktemp_dir_entry(tblname regclass) | |||||
returns void | |||||
language plpgsql | |||||
as $$ | |||||
begin | |||||
execute format(' | |||||
create temporary table if not exists tmp_%1$I | |||||
(like %1$I including defaults, dir_id sha1_git) | |||||
on commit delete rows; | |||||
alter table tmp_%1$I drop column if exists id; | |||||
', tblname); | |||||
return; | |||||
end | |||||
$$; | |||||
-- create a temporary table for revisions called tmp_revisions, | |||||
-- mimicking existing table revision, replacing the foreign keys to | |||||
-- people with an email and name field | |||||
-- | |||||
create or replace function swh_mktemp_revision() | |||||
returns void | |||||
language sql | |||||
as $$ | |||||
create temporary table if not exists tmp_revision ( | |||||
like revision including defaults, | |||||
author_fullname bytea, | |||||
author_name bytea, | |||||
author_email bytea, | |||||
committer_fullname bytea, | |||||
committer_name bytea, | |||||
committer_email bytea | |||||
) on commit delete rows; | |||||
alter table tmp_revision drop column if exists author; | |||||
alter table tmp_revision drop column if exists committer; | |||||
alter table tmp_revision drop column if exists object_id; | |||||
$$; | |||||
-- create a temporary table for releases called tmp_release, | |||||
-- mimicking existing table release, replacing the foreign keys to | |||||
-- people with an email and name field | |||||
-- | |||||
create or replace function swh_mktemp_release() | |||||
returns void | |||||
language sql | |||||
as $$ | |||||
create temporary table if not exists tmp_release ( | |||||
like release including defaults, | |||||
author_fullname bytea, | |||||
author_name bytea, | |||||
author_email bytea | |||||
) on commit delete rows; | |||||
alter table tmp_release drop column if exists author; | |||||
alter table tmp_release drop column if exists object_id; | |||||
) on commit delete rows; | |||||
$$; | |||||
-- create a temporary table for the branches of a snapshot | |||||
create or replace function swh_mktemp_snapshot_branch() | |||||
returns void | |||||
language sql | |||||
as $$ | |||||
create temporary table if not exists tmp_snapshot_branch ( | |||||
name bytea not null, | |||||
target bytea, | |||||
target_type snapshot_target | |||||
) on commit delete rows; | |||||
$$; | |||||
-- create a temporary table for the tools | |||||
create or replace function swh_mktemp_tool() | |||||
returns void | |||||
language sql | |||||
as $$ | |||||
create temporary table if not exists tmp_tool ( | |||||
like tool including defaults | |||||
) on commit delete rows; | |||||
alter table tmp_tool drop column if exists id; | |||||
$$; |