Differential D8873 Diff 32036 swh/indexer/sql/50-func.sql

Changeset 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
$$;		$$;
		ardumontUnsubmitted Not Done Inline Actions 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). ardumont: This can go away (and should have already but...), i don't think we still have the…
		vlorentzAuthorUnsubmitted Done Inline Actions ah, 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.
		vlorentzAuthorUnsubmitted Done Inline Actions D8888 vlorentz: 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
$$;		$$;
		ardumontUnsubmitted Not Done Inline Actions ditto 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
$$;		$$;