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

Changeset View

Standalone View

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

Show First 20 Lines • Show All 294 Lines • ▼ Show 20 Lines	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