No OneTemporary
Actions

Size

1 KB

Subscribers

None

View Options

	diff --git a/sql/upgrades/058.sql b/sql/upgrades/058.sql
	new file mode 100644
	index 000000000..055db4fb5
	--- /dev/null
	+++ b/sql/upgrades/058.sql
	@@ -0,0 +1,44 @@
	+-- SWH DB schema upgrade
	+-- from_version: 57
	+-- to_version: 58
	+-- description: Return missing contents from batch of contents uniquely based on sha1.
	+
	+insert into dbversion(version, release, description)
	+ values(58, now(), 'Work In Progress');
	+
	+-- create a temporary table called tmp_content_sha1, mimicking existing table
	+-- content with only the sha1 column
	+--
	+create or replace function swh_mktemp_content_sha1()
	+ returns void
	+ language sql
	+as $$
	+ create temporary table tmp_content_sha1
	+ (like content including defaults)
	+ on commit drop;
	+ alter table tmp_content_sha1 drop column if exists sha256;
	+ alter table tmp_content_sha1 drop column if exists sha1_git;
	+ alter table tmp_content_sha1 drop column if exists ctime;
	+ alter table tmp_content_sha1 drop column if exists length;
	+ alter table tmp_content_sha1 drop column if exists status;
	+ alter table tmp_content_sha1 drop column if exists object_id;
	+$$;
	+
	+
	+
	+-- check which entries of tmp_content_sha1 are missing from content
	+--
	+-- operates in bulk: 0. swh_mktemp_content_sha1(), 1. COPY to tmp_content_sha1,
	+-- 2. call this function
	+create or replace function swh_content_missing_per_sha1()
	+ returns setof sha1
	+ language plpgsql
	+as $$
	+begin
	+ return query
	+ (select sha1
	+ from tmp_content_sha1 as tmp
	+ where not exists
	+ (select 1 from content as c where c.sha1=tmp.sha1));
	+end
	+$$;