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