082.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: 81
	-- to_version: 82
	-- description: refactor cache_content_revision

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

	drop function swh_cache_content_revision_add(revision_id sha1_git);
	drop function swh_content_find_provenance(content_id sha1_git);

	DROP TABLE cache_content_revision;

	create table cache_content_revision (
	content sha1_git not null,
	blacklisted boolean default false,
	revision_paths bytea[][]
	);

	CREATE TABLE cache_content_revision_processed (
	revision sha1_git NOT NULL
	);

	CREATE OR REPLACE FUNCTION swh_cache_content_revision_add(revision_id sha1_git) RETURNS void
	LANGUAGE plpgsql
	AS $$
	declare
	rev sha1_git;
	begin
	select revision
	from cache_content_revision_processed
	where revision=revision_id
	into rev;

	if rev is NULL then

	insert into cache_content_revision_processed (revision) VALUES (revision_id);

	insert into cache_content_revision
	select sha1_git as content, false as blacklisted, array_agg(ARRAY[revision_id::bytea, name::bytea]) as revision_paths
	from swh_directory_walk((select directory from revision where id=revision_id))
	where type='file'
	group by sha1_git
	on conflict (content) do update
	set revision_paths = cache_content_revision.revision_paths \|\| EXCLUDED.revision_paths
	where cache_content_revision.blacklisted = false;
	return;

	else
	return;
	end if;
	end
	$$;

	COMMENT ON FUNCTION swh_cache_content_revision_add(revision_id sha1_git) IS 'Cache the specified revision directory contents into cache_content_revision';

	CREATE OR REPLACE FUNCTION swh_content_find_provenance(content_id sha1_git) RETURNS SETOF content_provenance
	LANGUAGE sql
	AS $$
	with subscripted_paths as (
	select content, revision_paths, generate_subscripts(revision_paths, 1) as s
	from cache_content_revision
	where content = content_id
	),
	cleaned_up_contents as (
	select content, revision_paths[s][1]::sha1_git as revision, revision_paths[s][2]::unix_path as path
	from subscripted_paths
	)
	select cuc.content, cuc.revision, cro.origin, cro.visit, cuc.path
	from cleaned_up_contents cuc
	inner join cache_revision_origin cro using(revision)
	$$;

	COMMENT ON FUNCTION swh_content_find_provenance(content_id sha1_git) IS 'Given a content, provide provenance information on it';

	ALTER TABLE cache_content_revision
	ADD CONSTRAINT cache_content_revision_pkey PRIMARY KEY (content);

	ALTER TABLE cache_content_revision
	ADD CONSTRAINT cache_content_revision_content_fkey FOREIGN KEY (content) REFERENCES content(sha1_git);

	ALTER TABLE cache_content_revision_processed
	ADD CONSTRAINT cache_content_revision_processed_pkey PRIMARY KEY (revision);

	ALTER TABLE cache_content_revision_processed
	ADD CONSTRAINT cache_content_revision_processed_revision_fkey FOREIGN KEY (revision) REFERENCES revision(id);

File Metadata

Mime Type: text/plain
Expires: Fri, Jul 4, 2:42 PM (3 d, 5 h ago)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3240613

082.sql
No OneTemporary
Actions

082.sql
View Options

File Metadata

Event Timeline

082.sqlNo OneTemporaryActions

082.sqlView Options

File Metadata

Event Timeline

082.sql
No OneTemporary
Actions

082.sql
View Options