088.sql
-- SWH DB schema upgrade
-- from_version: 87
-- to_version: 88
-- description: indexer: Permit to update existing data (language, mimetype)
insert into dbversion(version, release, description)
values(88, now(), 'Work In Progress');
alter function swh_language_missing() rename to swh_content_language_missing;
alter function swh_mimetype_missing() rename to swh_content_mimetype_missing;
drop function swh_mimetype_add();
drop function swh_language_add();
-- add tmp_content_mimetype entries to content_mimetype, overwriting
-- duplicates if conflict_update is true, skipping duplicates otherwise.
--
-- If filtering duplicates is in order, the call to
-- swh_mimetype_missing must take place before calling this function.
--
--
-- operates in bulk: 0. swh_mktemp(content_mimetype), 1. COPY to tmp_content_mimetype,
-- 2. call this function
create or replace function swh_content_mimetype_add(conflict_update boolean)
returns void
language plpgsql
as $$
begin
if conflict_update then
insert into content_mimetype (id, mimetype, encoding)
select id, mimetype, encoding
from tmp_content_mimetype
on conflict(id)
do update set mimetype = excluded.mimetype,
encoding = excluded.encoding;
else
insert into content_mimetype (id, mimetype, encoding)
select id, mimetype, encoding
from tmp_content_mimetype
on conflict do nothing;
end if;
return;
end
$$;
comment on function swh_content_mimetype_add(boolean) IS 'Add new content mimetypes';
-- Retrieve list of content mimetype from the temporary table.
--
-- operates in bulk: 0. mktemp(tmp_bytea), 1. COPY to tmp_bytea, 2. call this function
create or replace function swh_content_mimetype_get()
returns setof content_mimetype
language plpgsql
as $$
begin
return query
select id::sha1, mimetype, encoding
from tmp_bytea t
inner join content_mimetype using(id);
return;
end
$$;
comment on function swh_content_mimetype_get() IS 'List content mimetypes';
-- add tmp_content_language entries to content_language, overwriting
-- duplicates if conflict_update is true, skipping duplicates otherwise.
--
-- If filtering duplicates is in order, the call to
-- swh_mimetype_missing must take place before calling this function.
--
-- operates in bulk: 0. swh_mktemp(content_language), 1. COPY to tmp_content_language,
-- 2. call this function
create or replace function swh_content_language_add(conflict_update boolean)
returns void
language plpgsql
as $$
begin
if conflict_update then
insert into content_language (id, lang)
select id, lang
from tmp_content_language
on conflict(id)
do update set lang = excluded.lang;
else
insert into content_language (id, lang)
select id, lang
from tmp_content_language
on conflict do nothing;
end if;
return;
end
$$;
comment on function swh_content_language_add(boolean) IS 'Add new content languages';
-- Retrieve list of content language from the temporary table.
--
-- operates in bulk: 0. mktemp(tmp_bytea), 1. COPY to tmp_bytea, 2. call this function
create or replace function swh_content_language_get()
returns setof content_language
language plpgsql
as $$
begin
return query
select id::sha1, lang
from tmp_bytea t
inner join content_language using(id);
return;
end
$$;
comment on function swh_content_language_get() IS 'List content languages';