Differential D7937 Diff 28658 swh/indexer/sql/50-func.sql

Changeset View

Standalone View

swh/indexer/sql/50-func.sql

Show First 20 Lines • Show All 267 Lines • ▼ Show 20 Lines	create temporary table if not exists tmp_content_metadata (
like content_metadata including defaults		like content_metadata including defaults
) on commit delete rows;		) on commit delete rows;
$$;		$$;

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_intrinsic_metadata entries to revision_intrinsic_metadata,		-- add tmp_directory_intrinsic_metadata entries to directory_intrinsic_metadata,
-- overwriting duplicates.		-- overwriting duplicates.
--		--
-- If filtering duplicates is in order, the call to		-- If filtering duplicates is in order, the call to
-- swh_revision_intrinsic_metadata_missing must take place before calling this		-- swh_directory_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_intrinsic_metadata, 2. call this function		-- tmp_directory_intrinsic_metadata, 2. call this function
create or replace function swh_revision_intrinsic_metadata_add()		create or replace function swh_directory_intrinsic_metadata_add()
returns bigint		returns bigint
language plpgsql		language plpgsql
as $$		as $$
declare		declare
res bigint;		res bigint;
begin		begin
insert into revision_intrinsic_metadata (id, metadata, mappings, indexer_configuration_id)		insert into directory_intrinsic_metadata (id, metadata, mappings, indexer_configuration_id)
select id, metadata, mappings, indexer_configuration_id		select id, metadata, mappings, indexer_configuration_id
from tmp_revision_intrinsic_metadata tcm		from tmp_directory_intrinsic_metadata tcm
on conflict(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;

get diagnostics res = ROW_COUNT;		get diagnostics res = ROW_COUNT;
return res;		return res;
end		end
$$;		$$;

comment on function swh_revision_intrinsic_metadata_add() IS 'Add new revision intrinsic metadata';		comment on function swh_directory_intrinsic_metadata_add() IS 'Add new directory intrinsic metadata';

-- create a temporary table for retrieving revision_intrinsic_metadata		-- create a temporary table for retrieving directory_intrinsic_metadata
create or replace function swh_mktemp_revision_intrinsic_metadata()		create or replace function swh_mktemp_directory_intrinsic_metadata()
returns void		returns void
language sql		language sql
as $$		as $$
create temporary table if not exists tmp_revision_intrinsic_metadata (		create temporary table if not exists tmp_directory_intrinsic_metadata (
like revision_intrinsic_metadata including defaults		like directory_intrinsic_metadata including defaults
) on commit delete rows;		) on commit delete rows;
$$;		$$;

comment on function swh_mktemp_revision_intrinsic_metadata() is 'Helper table to add revision intrinsic metadata';		comment on function swh_mktemp_directory_intrinsic_metadata() is 'Helper table to add directory 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 if not exists tmp_origin_intrinsic_metadata (		create temporary table if not exists 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()
returns bigint		returns bigint
language plpgsql		language plpgsql
as $$		as $$
declare		declare
res bigint;		res bigint;
begin		begin
perform swh_origin_intrinsic_metadata_compute_tsvector();		perform swh_origin_intrinsic_metadata_compute_tsvector();

insert into origin_intrinsic_metadata (id, metadata, indexer_configuration_id, from_revision, metadata_tsvector, mappings)		insert into origin_intrinsic_metadata (id, metadata, indexer_configuration_id, from_directory, metadata_tsvector, mappings)
select id, metadata, indexer_configuration_id, from_revision,		select id, metadata, indexer_configuration_id, from_directory,
metadata_tsvector, mappings		metadata_tsvector, mappings
from tmp_origin_intrinsic_metadata		from tmp_origin_intrinsic_metadata
on conflict(id, indexer_configuration_id)		on conflict(id, indexer_configuration_id)
do update set		do update set
metadata = excluded.metadata,		metadata = excluded.metadata,
metadata_tsvector = excluded.metadata_tsvector,		metadata_tsvector = excluded.metadata_tsvector,
mappings = excluded.mappings,		mappings = excluded.mappings,
from_revision = excluded.from_revision;		from_directory = excluded.from_directory;

get diagnostics res = ROW_COUNT;		get diagnostics res = ROW_COUNT;
return res;		return res;
end		end
$$;		$$;

comment on function swh_origin_intrinsic_metadata_add() IS 'Add new origin intrinsic metadata';		comment on function swh_origin_intrinsic_metadata_add() IS 'Add new origin intrinsic metadata';

Show All 14 Lines