Page MenuHomeSoftware Heritage
Files F7163579

D2580.id9214.diff
No OneTemporary
Actions

D2580.id9214.diff
View Options

diff --git a/sql/upgrades/146.sql b/sql/upgrades/146.sql
new file mode 100644
--- /dev/null
+++ b/sql/upgrades/146.sql
@@ -0,0 +1,115 @@
+-- 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;
+$$;
diff --git a/swh/storage/sql/30-swh-schema.sql b/swh/storage/sql/30-swh-schema.sql
--- a/swh/storage/sql/30-swh-schema.sql
+++ b/swh/storage/sql/30-swh-schema.sql
@@ -17,7 +17,7 @@
-- latest schema version
insert into dbversion(version, release, description)
- values(145, now(), 'Work In Progress');
+ values(146, now(), 'Work In Progress');
-- a SHA1 checksum
create domain sha1 as bytea check (length(value) = 20);
diff --git a/swh/storage/sql/40-swh-func.sql b/swh/storage/sql/40-swh-func.sql
--- a/swh/storage/sql/40-swh-func.sql
+++ b/swh/storage/sql/40-swh-func.sql
@@ -17,9 +17,9 @@
as $$
begin
execute format('
- create temporary table tmp_%1$I
+ create temporary table if not exists tmp_%1$I
(like %1$I including defaults)
- on commit drop;
+ on commit delete rows;
alter table tmp_%1$I drop column if exists object_id;
', tblname);
return;
@@ -40,16 +40,15 @@
as $$
begin
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)
- on commit drop;
- alter table tmp_%1$I drop column id;
+ 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
@@ -58,7 +57,7 @@
returns void
language sql
as $$
- create temporary table tmp_revision (
+ create temporary table if not exists tmp_revision (
like revision including defaults,
author_fullname bytea,
author_name bytea,
@@ -66,13 +65,12 @@
committer_fullname bytea,
committer_name bytea,
committer_email bytea
- ) on commit drop;
- alter table tmp_revision drop column author;
- alter table tmp_revision drop column committer;
- alter table tmp_revision drop column object_id;
+ ) 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
@@ -81,14 +79,14 @@
returns void
language sql
as $$
- create temporary table tmp_release (
+ create temporary table if not exists tmp_release (
like release including defaults,
author_fullname bytea,
author_name bytea,
author_email bytea
- ) on commit drop;
- alter table tmp_release drop column author;
- alter table tmp_release drop column object_id;
+ ) on commit delete rows;
+ alter table tmp_release drop column if exists author;
+ alter table tmp_release drop column if exists object_id;
$$;
-- create a temporary table for the branches of a snapshot
@@ -96,24 +94,24 @@
returns void
language sql
as $$
- create temporary table tmp_snapshot_branch (
+ create temporary table if not exists tmp_snapshot_branch (
name bytea not null,
target bytea,
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()
returns void
language sql
as $$
- create temporary table tmp_tool (
+ create temporary table if not exists tmp_tool (
like tool including defaults
- ) on commit drop;
- alter table tmp_tool drop column id;
+ ) on commit delete rows;
+ alter table tmp_tool drop column if exists id;
$$;
-
-- 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
-- some content or not during content injection

File Metadata

Mime Type
text/plain
Expires
Thu, Jan 30, 10:48 AM (15 h, 50 m ago)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
3223166

Event Timeline

About · Developer information · Legal