Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/sql/40-swh-func.sql
Show All 11 Lines | |||||
-- Args: | -- Args: | ||||
-- tblname: name of the table to mimic | -- tblname: name of the table to mimic | ||||
create or replace function swh_mktemp(tblname regclass) | create or replace function swh_mktemp(tblname regclass) | ||||
returns void | returns void | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
begin | begin | ||||
execute format(' | execute format(' | ||||
create temporary table tmp_%1$I | create temporary table if not exists tmp_%1$I | ||||
(like %1$I including defaults) | (like %1$I including defaults) | ||||
on commit drop; | on commit delete rows; | ||||
alter table tmp_%1$I drop column if exists object_id; | alter table tmp_%1$I drop column if exists object_id; | ||||
', tblname); | ', tblname); | ||||
return; | return; | ||||
end | end | ||||
$$; | $$; | ||||
-- create a temporary table for directory entries called tmp_TBLNAME, | -- create a temporary table for directory entries called tmp_TBLNAME, | ||||
-- mimicking existing table TBLNAME with an extra dir_id (sha1_git) | -- mimicking existing table TBLNAME with an extra dir_id (sha1_git) | ||||
-- column, and dropping the id column. | -- column, and dropping the id column. | ||||
-- | -- | ||||
-- This is used to create the tmp_directory_entry_<foo> tables. | -- This is used to create the tmp_directory_entry_<foo> tables. | ||||
-- | -- | ||||
-- Args: | -- Args: | ||||
-- tblname: name of the table to mimic | -- tblname: name of the table to mimic | ||||
create or replace function swh_mktemp_dir_entry(tblname regclass) | create or replace function swh_mktemp_dir_entry(tblname regclass) | ||||
returns void | returns void | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
begin | begin | ||||
execute format(' | execute format(' | ||||
create temporary table tmp_%1$I | create temporary table if not exists tmp_%1$I | ||||
(like %1$I including defaults, dir_id sha1_git) | (like %1$I including defaults, dir_id sha1_git) | ||||
on commit drop; | on commit delete rows; | ||||
alter table tmp_%1$I drop column id; | alter table tmp_%1$I drop column if exists id; | ||||
', tblname); | ', tblname); | ||||
return; | return; | ||||
end | end | ||||
$$; | $$; | ||||
-- create a temporary table for revisions called tmp_revisions, | -- create a temporary table for revisions called tmp_revisions, | ||||
-- mimicking existing table revision, replacing the foreign keys to | -- mimicking existing table revision, replacing the foreign keys to | ||||
-- people with an email and name field | -- people with an email and name field | ||||
-- | -- | ||||
create or replace function swh_mktemp_revision() | create or replace function swh_mktemp_revision() | ||||
returns void | returns void | ||||
language sql | language sql | ||||
as $$ | as $$ | ||||
create temporary table tmp_revision ( | create temporary table if not exists tmp_revision ( | ||||
like revision including defaults, | like revision including defaults, | ||||
author_fullname bytea, | author_fullname bytea, | ||||
author_name bytea, | author_name bytea, | ||||
author_email bytea, | author_email bytea, | ||||
committer_fullname bytea, | committer_fullname bytea, | ||||
committer_name bytea, | committer_name bytea, | ||||
committer_email bytea | committer_email bytea | ||||
) on commit drop; | ) on commit delete rows; | ||||
alter table tmp_revision drop column author; | alter table tmp_revision drop column if exists author; | ||||
alter table tmp_revision drop column committer; | alter table tmp_revision drop column if exists committer; | ||||
alter table tmp_revision drop column object_id; | alter table tmp_revision drop column if exists object_id; | ||||
$$; | $$; | ||||
-- create a temporary table for releases called tmp_release, | -- create a temporary table for releases called tmp_release, | ||||
-- mimicking existing table release, replacing the foreign keys to | -- mimicking existing table release, replacing the foreign keys to | ||||
-- people with an email and name field | -- people with an email and name field | ||||
-- | -- | ||||
create or replace function swh_mktemp_release() | create or replace function swh_mktemp_release() | ||||
returns void | returns void | ||||
language sql | language sql | ||||
as $$ | as $$ | ||||
create temporary table tmp_release ( | create temporary table if not exists tmp_release ( | ||||
like release including defaults, | like release including defaults, | ||||
author_fullname bytea, | author_fullname bytea, | ||||
author_name bytea, | author_name bytea, | ||||
author_email bytea | author_email bytea | ||||
) on commit drop; | ) on commit delete rows; | ||||
alter table tmp_release drop column author; | alter table tmp_release drop column if exists author; | ||||
alter table tmp_release drop column object_id; | alter table tmp_release drop column if exists object_id; | ||||
$$; | $$; | ||||
-- create a temporary table for the branches of a snapshot | -- create a temporary table for the branches of a snapshot | ||||
create or replace function swh_mktemp_snapshot_branch() | create or replace function swh_mktemp_snapshot_branch() | ||||
returns void | returns void | ||||
language sql | language sql | ||||
as $$ | as $$ | ||||
create temporary table tmp_snapshot_branch ( | create temporary table if not exists tmp_snapshot_branch ( | ||||
name bytea not null, | name bytea not null, | ||||
target bytea, | target bytea, | ||||
target_type snapshot_target | target_type snapshot_target | ||||
) on commit drop; | ) on commit delete rows; | ||||
$$; | $$; | ||||
-- create a temporary table for the tools | |||||
create or replace function swh_mktemp_tool() | create or replace function swh_mktemp_tool() | ||||
returns void | returns void | ||||
language sql | language sql | ||||
as $$ | as $$ | ||||
create temporary table tmp_tool ( | create temporary table if not exists tmp_tool ( | ||||
like tool including defaults | like tool including defaults | ||||
) on commit drop; | ) on commit delete rows; | ||||
alter table tmp_tool drop column id; | alter table tmp_tool drop column if exists id; | ||||
$$; | $$; | ||||
-- a content signature is a set of cryptographic checksums that we use to | -- a content signature is a set of cryptographic checksums that we use to | ||||
-- uniquely identify content, for the purpose of verifying if we already have | -- uniquely identify content, for the purpose of verifying if we already have | ||||
-- some content or not during content injection | -- some content or not during content injection | ||||
create type content_signature as ( | create type content_signature as ( | ||||
sha1 sha1, | sha1 sha1, | ||||
sha1_git sha1_git, | sha1_git sha1_git, | ||||
sha256 sha256, | sha256 sha256, | ||||
blake2s256 blake2s256 | blake2s256 blake2s256 | ||||
▲ Show 20 Lines • Show All 997 Lines • Show Last 20 Lines |