086.sql
No OneTemporary
Actions

Size

1 KB

Subscribers

None

086.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: 85
	-- to_version: 86
	-- description: content cache: insert lines in deterministic order to reduce lock contention

	insert into dbversion(version, release, description)
	values(86, now(), 'Work In Progress');

	drop function swh_cache_content_get();

	create type cache_content_signature as (
	sha1 sha1,
	sha1_git sha1_git,
	sha256 sha256,
	revision_paths bytea[][]
	);

	create or replace function swh_cache_content_get_all()
	returns setof cache_content_signature
	language sql
	stable
	as $$
	SELECT c.sha1, c.sha1_git, c.sha256, ccr.revision_paths
	FROM cache_content_revision ccr
	INNER JOIN content as c
	ON ccr.content = c.sha1_git
	$$;

	COMMENT ON FUNCTION swh_cache_content_get_all() IS 'Retrieve batch of contents';


	create or replace function swh_cache_content_get(target sha1_git)
	returns setof cache_content_signature
	language sql
	stable
	as $$
	SELECT c.sha1, c.sha1_git, c.sha256, ccr.revision_paths
	FROM cache_content_revision ccr
	INNER JOIN content as c
	ON ccr.content = c.sha1_git
	where ccr.content = target
	$$;

	COMMENT ON FUNCTION swh_cache_content_get(sha1_git) IS 'Retrieve cache content information';