Changeset View
Changeset View
Standalone View
Standalone View
swh/indexer/sql/50-func.sql
Show First 20 Lines • Show All 52 Lines • ▼ Show 20 Lines | create or replace function swh_content_mimetype_add() | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
declare | declare | ||||
res bigint; | res bigint; | ||||
begin | begin | ||||
insert into content_mimetype (id, mimetype, encoding, indexer_configuration_id) | insert into content_mimetype (id, mimetype, encoding, indexer_configuration_id) | ||||
select id, mimetype, encoding, indexer_configuration_id | select id, mimetype, encoding, indexer_configuration_id | ||||
from tmp_content_mimetype tcm | from tmp_content_mimetype tcm | ||||
order by id, indexer_configuration_id | |||||
on conflict(id, indexer_configuration_id) | on conflict(id, indexer_configuration_id) | ||||
do update set mimetype = excluded.mimetype, | do update set mimetype = excluded.mimetype, | ||||
encoding = excluded.encoding; | encoding = excluded.encoding; | ||||
get diagnostics res = ROW_COUNT; | get diagnostics res = ROW_COUNT; | ||||
return res; | return res; | ||||
end | end | ||||
$$; | $$; | ||||
Show All 13 Lines | create or replace function swh_content_language_add() | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
declare | declare | ||||
res bigint; | res bigint; | ||||
begin | begin | ||||
insert into content_language (id, lang, indexer_configuration_id) | insert into content_language (id, lang, indexer_configuration_id) | ||||
select id, lang, indexer_configuration_id | select id, lang, indexer_configuration_id | ||||
from tmp_content_language tcl | from tmp_content_language tcl | ||||
order by id, indexer_configuration_id | |||||
on conflict(id, indexer_configuration_id) | on conflict(id, indexer_configuration_id) | ||||
do update set lang = excluded.lang; | do update set lang = excluded.lang; | ||||
get diagnostics res = ROW_COUNT; | get diagnostics res = ROW_COUNT; | ||||
return res; | return res; | ||||
end | end | ||||
$$; | $$; | ||||
ardumont: This can go away (and should have already but...), i don't think we still have the… | |||||
Done Inline Actionsah, I felt there was something fishy about the mismatched number of changes in .py and .sql. I'll do it in a next diff, thanks vlorentz: ah, I felt there was something fishy about the mismatched number of changes in .py and .sql. | |||||
Done Inline Actionsvlorentz: D8888 | |||||
comment on function swh_content_language_add() IS 'Add new content languages'; | comment on function swh_content_language_add() IS 'Add new content languages'; | ||||
-- create a temporary table for retrieving content_language | -- create a temporary table for retrieving content_language | ||||
create or replace function swh_mktemp_content_language() | create or replace function swh_mktemp_content_language() | ||||
returns void | returns void | ||||
language sql | language sql | ||||
as $$ | as $$ | ||||
Show All 27 Lines | create or replace function swh_content_ctags_add() | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
declare | declare | ||||
res bigint; | res bigint; | ||||
begin | begin | ||||
insert into content_ctags (id, name, kind, line, lang, indexer_configuration_id) | insert into content_ctags (id, name, kind, line, lang, indexer_configuration_id) | ||||
select id, name, kind, line, lang, indexer_configuration_id | select id, name, kind, line, lang, indexer_configuration_id | ||||
from tmp_content_ctags tct | from tmp_content_ctags tct | ||||
order by id, hash_sha1(name), kind, line, lang, indexer_configuration_id | |||||
on conflict(id, hash_sha1(name), kind, line, lang, indexer_configuration_id) | on conflict(id, hash_sha1(name), kind, line, lang, indexer_configuration_id) | ||||
do nothing; | do nothing; | ||||
get diagnostics res = ROW_COUNT; | get diagnostics res = ROW_COUNT; | ||||
return res; | return res; | ||||
end | end | ||||
$$; | $$; | ||||
Not Done Inline Actionsditto for content_ctags_*... ardumont: ditto for content_ctags_*... | |||||
comment on function swh_content_ctags_add() IS 'Add new ctags symbols per content'; | comment on function swh_content_ctags_add() IS 'Add new ctags symbols per content'; | ||||
create type content_ctags_signature as ( | create type content_ctags_signature as ( | ||||
id sha1, | id sha1, | ||||
name text, | name text, | ||||
kind text, | kind text, | ||||
line bigint, | line bigint, | ||||
▲ Show 20 Lines • Show All 58 Lines • ▼ Show 20 Lines | begin | ||||
where not exists (select 1 from fossology_license where name=tmp.license) | where not exists (select 1 from fossology_license where name=tmp.license) | ||||
on conflict(name) do nothing; | on conflict(name) do nothing; | ||||
insert into content_fossology_license (id, license_id, indexer_configuration_id) | insert into content_fossology_license (id, license_id, indexer_configuration_id) | ||||
select tcl.id, | select tcl.id, | ||||
(select id from fossology_license where name = tcl.license) as license, | (select id from fossology_license where name = tcl.license) as license, | ||||
indexer_configuration_id | indexer_configuration_id | ||||
from tmp_content_fossology_license tcl | from tmp_content_fossology_license tcl | ||||
order by tcl.id, license, indexer_configuration_id | |||||
on conflict(id, license_id, indexer_configuration_id) | on conflict(id, license_id, indexer_configuration_id) | ||||
do update set license_id = excluded.license_id; | do update set license_id = excluded.license_id; | ||||
get diagnostics res = ROW_COUNT; | get diagnostics res = ROW_COUNT; | ||||
return res; | return res; | ||||
end | end | ||||
$$; | $$; | ||||
Show All 15 Lines | create or replace function swh_content_metadata_add() | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
declare | declare | ||||
res bigint; | res bigint; | ||||
begin | begin | ||||
insert into content_metadata (id, metadata, indexer_configuration_id) | insert into content_metadata (id, metadata, indexer_configuration_id) | ||||
select id, metadata, indexer_configuration_id | select id, metadata, indexer_configuration_id | ||||
from tmp_content_metadata tcm | from tmp_content_metadata tcm | ||||
order by id, indexer_configuration_id | |||||
on conflict(id, indexer_configuration_id) | on conflict(id, indexer_configuration_id) | ||||
do update set metadata = excluded.metadata; | do update set metadata = excluded.metadata; | ||||
get diagnostics res = ROW_COUNT; | get diagnostics res = ROW_COUNT; | ||||
return res; | return res; | ||||
end | end | ||||
$$; | $$; | ||||
Show All 27 Lines | create or replace function swh_directory_intrinsic_metadata_add() | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
declare | declare | ||||
res bigint; | res bigint; | ||||
begin | begin | ||||
insert into directory_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_directory_intrinsic_metadata tcm | from tmp_directory_intrinsic_metadata tcm | ||||
order by id, indexer_configuration_id | |||||
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 | ||||
▲ Show 20 Lines • Show All 52 Lines • ▼ Show 20 Lines | 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_directory, 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_directory, | select id, metadata, indexer_configuration_id, from_directory, | ||||
metadata_tsvector, mappings | metadata_tsvector, mappings | ||||
from tmp_origin_intrinsic_metadata | from tmp_origin_intrinsic_metadata | ||||
order by id, indexer_configuration_id | |||||
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_directory = excluded.from_directory; | from_directory = excluded.from_directory; | ||||
get diagnostics res = ROW_COUNT; | get diagnostics res = ROW_COUNT; | ||||
▲ Show 20 Lines • Show All 57 Lines • ▼ Show 20 Lines | declare | ||||
res bigint; | res bigint; | ||||
begin | begin | ||||
perform swh_origin_extrinsic_metadata_compute_tsvector(); | perform swh_origin_extrinsic_metadata_compute_tsvector(); | ||||
insert into origin_extrinsic_metadata (id, metadata, indexer_configuration_id, from_remd_id, metadata_tsvector, mappings) | insert into origin_extrinsic_metadata (id, metadata, indexer_configuration_id, from_remd_id, metadata_tsvector, mappings) | ||||
select id, metadata, indexer_configuration_id, from_remd_id, | select id, metadata, indexer_configuration_id, from_remd_id, | ||||
metadata_tsvector, mappings | metadata_tsvector, mappings | ||||
from tmp_origin_extrinsic_metadata | from tmp_origin_extrinsic_metadata | ||||
order by id, indexer_configuration_id | |||||
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_remd_id = excluded.from_remd_id; | from_remd_id = excluded.from_remd_id; | ||||
get diagnostics res = ROW_COUNT; | get diagnostics res = ROW_COUNT; | ||||
Show All 26 Lines | |||||
-- it, 2. call this function to insert and filtering out duplicates | -- it, 2. call this function to insert and filtering out duplicates | ||||
create or replace function swh_indexer_configuration_add() | create or replace function swh_indexer_configuration_add() | ||||
returns setof indexer_configuration | returns setof indexer_configuration | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
begin | begin | ||||
insert into indexer_configuration(tool_name, tool_version, tool_configuration) | insert into indexer_configuration(tool_name, tool_version, tool_configuration) | ||||
select tool_name, tool_version, tool_configuration from tmp_indexer_configuration tmp | select tool_name, tool_version, tool_configuration from tmp_indexer_configuration tmp | ||||
order by tool_name, tool_version, tool_configuration | |||||
on conflict(tool_name, tool_version, tool_configuration) do nothing; | on conflict(tool_name, tool_version, tool_configuration) do nothing; | ||||
return query | return query | ||||
select id, tool_name, tool_version, tool_configuration | select id, tool_name, tool_version, tool_configuration | ||||
from tmp_indexer_configuration join indexer_configuration | from tmp_indexer_configuration join indexer_configuration | ||||
using(tool_name, tool_version, tool_configuration); | using(tool_name, tool_version, tool_configuration); | ||||
return; | return; | ||||
end | end | ||||
$$; | $$; |
This can go away (and should have already but...), i don't think we still have the content_language_* entrypoints in the indexer storage (rapid check here in storage.py confirms it).
(in another diff/commit or whatever you wish).