Differential D1226 Diff 3907 swh/indexer/sql/40-swh-func.sql

Changeset View

Standalone View

swh/indexer/sql/40-swh-func.sql

	Show First 20 Lines • Show All 261 Lines • ▼ Show 20 Lines
	-- operates in bulk: 0. swh_mktemp(content_language), 1. COPY to			-- operates in bulk: 0. swh_mktemp(content_language), 1. COPY to
	-- tmp_content_metadata, 2. call this function			-- tmp_content_metadata, 2. call this function
	create or replace function swh_content_metadata_add(conflict_update boolean)			create or replace function swh_content_metadata_add(conflict_update boolean)
	returns void			returns void
	language plpgsql			language plpgsql
	as $$			as $$
	begin			begin
	if conflict_update then			if conflict_update then
	insert into content_metadata (id, translated_metadata, indexer_configuration_id)			insert into content_metadata (id, metadata, indexer_configuration_id)
	select id, translated_metadata, indexer_configuration_id			select id, metadata, indexer_configuration_id
	from tmp_content_metadata tcm			from tmp_content_metadata tcm
	on conflict(id, indexer_configuration_id)			on conflict(id, indexer_configuration_id)
	do update set translated_metadata = excluded.translated_metadata;			do update set metadata = excluded.metadata;

	else			else
	insert into content_metadata (id, translated_metadata, indexer_configuration_id)			insert into content_metadata (id, metadata, indexer_configuration_id)
	select id, translated_metadata, indexer_configuration_id			select id, metadata, indexer_configuration_id
	from tmp_content_metadata tcm			from tmp_content_metadata tcm
	on conflict(id, indexer_configuration_id)			on conflict(id, indexer_configuration_id)
	do nothing;			do nothing;
	end if;			end if;
	return;			return;
	end			end
	$$;			$$;

	comment on function swh_content_metadata_add(boolean) IS 'Add new content metadata';			comment on function swh_content_metadata_add(boolean) IS 'Add new content metadata';

	-- create a temporary table for retrieving content_metadata			-- create a temporary table for retrieving content_metadata
	create or replace function swh_mktemp_content_metadata()			create or replace function swh_mktemp_content_metadata()
	returns void			returns void
	language sql			language sql
	as $$			as $$
	create temporary table tmp_content_metadata (			create temporary table tmp_content_metadata (
	like content_metadata including defaults			like content_metadata including defaults
	) on commit drop;			) on commit drop;
	$$;			$$;

	comment on function swh_mktemp_content_metadata() is 'Helper table to add content metadata';			comment on function swh_mktemp_content_metadata() is 'Helper table to add content metadata';

	-- end content_metadata functions			-- end content_metadata functions

	-- add tmp_revision_metadata entries to revision_metadata, overwriting			-- add tmp_revision_intrinsic_metadata entries to revision_intrinsic_metadata,
	-- duplicates if conflict_update is true, skipping duplicates otherwise.			-- overwriting duplicates if conflict_update is true, skipping duplicates
				-- otherwise.
	--			--
	-- If filtering duplicates is in order, the call to			-- If filtering duplicates is in order, the call to
	-- swh_revision_metadata_missing must take place before calling this			-- swh_revision_intrinsic_metadata_missing must take place before calling this
	-- function.			-- function.
	--			--
	-- operates in bulk: 0. swh_mktemp(content_language), 1. COPY to			-- operates in bulk: 0. swh_mktemp(content_language), 1. COPY to
	-- tmp_revision_metadata, 2. call this function			-- tmp_revision_intrinsic_metadata, 2. call this function
	create or replace function swh_revision_metadata_add(conflict_update boolean)			create or replace function swh_revision_intrinsic_metadata_add(conflict_update boolean)
	returns void			returns void
	language plpgsql			language plpgsql
	as $$			as $$
	begin			begin
	if conflict_update then			if conflict_update then
	insert into revision_metadata (id, translated_metadata, mappings, indexer_configuration_id)			insert into revision_intrinsic_metadata (id, metadata, mappings, indexer_configuration_id)
	select id, translated_metadata, mappings, indexer_configuration_id			select id, metadata, mappings, indexer_configuration_id
	from tmp_revision_metadata tcm			from tmp_revision_intrinsic_metadata tcm
	on conflict(id, indexer_configuration_id)			on conflict(id, indexer_configuration_id)
	do update set			do update set
	translated_metadata = excluded.translated_metadata,			metadata = excluded.metadata,
	mappings = excluded.mappings;			mappings = excluded.mappings;

	else			else
	insert into revision_metadata (id, translated_metadata, mappings, indexer_configuration_id)			insert into revision_intrinsic_metadata (id, metadata, mappings, indexer_configuration_id)
	select id, translated_metadata, mappings, indexer_configuration_id			select id, metadata, mappings, indexer_configuration_id
	from tmp_revision_metadata tcm			from tmp_revision_intrinsic_metadata tcm
	on conflict(id, indexer_configuration_id)			on conflict(id, indexer_configuration_id)
	do nothing;			do nothing;
	end if;			end if;
	return;			return;
	end			end
	$$;			$$;

	comment on function swh_revision_metadata_add(boolean) IS 'Add new revision metadata';			comment on function swh_revision_intrinsic_metadata_add(boolean) IS 'Add new revision intrinsic metadata';

	-- create a temporary table for retrieving revision_metadata			-- create a temporary table for retrieving revision_intrinsic_metadata
	create or replace function swh_mktemp_revision_metadata()			create or replace function swh_mktemp_revision_intrinsic_metadata()
	returns void			returns void
	language sql			language sql
	as $$			as $$
	create temporary table tmp_revision_metadata (			create temporary table tmp_revision_intrinsic_metadata (
	like revision_metadata including defaults			like revision_intrinsic_metadata including defaults
	) on commit drop;			) on commit drop;
	$$;			$$;

	comment on function swh_mktemp_revision_metadata() is 'Helper table to add revision metadata';			comment on function swh_mktemp_revision_intrinsic_metadata() is 'Helper table to add revision intrinsic metadata';

	-- create a temporary table for retrieving origin_intrinsic_metadata			-- create a temporary table for retrieving origin_intrinsic_metadata
	create or replace function swh_mktemp_origin_intrinsic_metadata()			create or replace function swh_mktemp_origin_intrinsic_metadata()
	returns void			returns void
	language sql			language sql
	as $$			as $$
	create temporary table tmp_origin_intrinsic_metadata (			create temporary table tmp_origin_intrinsic_metadata (
	like origin_intrinsic_metadata including defaults			like origin_intrinsic_metadata including defaults
	▲ Show 20 Lines • Show All 49 Lines • ▼ Show 20 Lines
	create or replace function swh_origin_intrinsic_metadata_add(			create or replace function swh_origin_intrinsic_metadata_add(
	conflict_update boolean)			conflict_update boolean)
	returns void			returns void
	language plpgsql			language plpgsql
	as $$			as $$
	begin			begin
	perform swh_origin_intrinsic_metadata_compute_tsvector();			perform swh_origin_intrinsic_metadata_compute_tsvector();
	if conflict_update then			if conflict_update then
	insert into origin_intrinsic_metadata (origin_id, metadata, indexer_configuration_id, from_revision, metadata_tsvector, mappings)			insert into origin_intrinsic_metadata (id, metadata, indexer_configuration_id, from_revision, metadata_tsvector, mappings)
	select origin_id, metadata, indexer_configuration_id, from_revision,			select id, metadata, indexer_configuration_id, from_revision,
	metadata_tsvector, mappings			metadata_tsvector, mappings
	from tmp_origin_intrinsic_metadata			from tmp_origin_intrinsic_metadata
	on conflict(origin_id, indexer_configuration_id)			on conflict(id, indexer_configuration_id)
	do update set			do update set
	metadata = excluded.metadata,			metadata = excluded.metadata,
	mappings = excluded.mappings;			mappings = excluded.mappings;

	else			else
	insert into origin_intrinsic_metadata (origin_id, metadata, indexer_configuration_id, from_revision, metadata_tsvector, mappings)			insert into origin_intrinsic_metadata (id, metadata, indexer_configuration_id, from_revision, metadata_tsvector, mappings)
	select origin_id, metadata, indexer_configuration_id, from_revision,			select id, metadata, indexer_configuration_id, from_revision,
	metadata_tsvector, mappings			metadata_tsvector, mappings
	from tmp_origin_intrinsic_metadata			from tmp_origin_intrinsic_metadata
	on conflict(origin_id, indexer_configuration_id)			on conflict(id, indexer_configuration_id)
	do nothing;			do nothing;
	end if;			end if;
	return;			return;
	end			end
	$$;			$$;


	-- Compute the metadata_tsvector column in tmp_origin_intrinsic_metadata.			-- Compute the metadata_tsvector column in tmp_origin_intrinsic_metadata.
	Show All 14 Lines