Changeset View
Changeset View
Standalone 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 |