Differential D8888 Diff 32071 swh/indexer/sql/50-func.sql

Changeset View

Standalone View

swh/indexer/sql/50-func.sql

Show First 20 Lines • Show All 64 Lines • ▼ Show 20 Lines	begin

get diagnostics res = ROW_COUNT;		get diagnostics res = ROW_COUNT;
return res;		return res;
end		end
$$;		$$;

comment on function swh_content_mimetype_add() IS 'Add new content mimetypes';		comment on function swh_content_mimetype_add() IS 'Add new content mimetypes';

-- add tmp_content_language entries to content_language, overwriting duplicates.
--
-- If filtering duplicates is in order, the call to
-- swh_content_language_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()
returns bigint
language plpgsql
as $$
declare
res bigint;
begin
insert into content_language (id, lang, indexer_configuration_id)
select id, lang, indexer_configuration_id
from tmp_content_language tcl
order by id, indexer_configuration_id
on conflict(id, indexer_configuration_id)
do update set lang = excluded.lang;

get diagnostics res = ROW_COUNT;
return res;
end
$$;

comment on function swh_content_language_add() IS 'Add new content languages';

-- create a temporary table for retrieving content_language
create or replace function swh_mktemp_content_language()
returns void
language sql
as $$
create temporary table if not exists tmp_content_language (
like content_language including defaults
) on commit delete rows;
$$;

comment on function swh_mktemp_content_language() is 'Helper table to add content language';


-- create a temporary table for content_ctags tmp_content_ctags,
create or replace function swh_mktemp_content_ctags()
returns void
language sql
as $$
create temporary table if not exists tmp_content_ctags (
like content_ctags including defaults
) on commit delete rows;
$$;

comment on function swh_mktemp_content_ctags() is 'Helper table to add content ctags';


-- add tmp_content_ctags entries to content_ctags, overwriting duplicates
--
-- operates in bulk: 0. swh_mktemp(content_ctags), 1. COPY to tmp_content_ctags,
-- 2. call this function
create or replace function swh_content_ctags_add()
returns bigint
language plpgsql
as $$
declare
res bigint;
begin
insert into content_ctags (id, name, kind, line, lang, indexer_configuration_id)
select id, name, kind, line, lang, indexer_configuration_id
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)
do nothing;

get diagnostics res = ROW_COUNT;
return res;
end
$$;

comment on function swh_content_ctags_add() IS 'Add new ctags symbols per content';

create type content_ctags_signature as (
id sha1,
name text,
kind text,
line bigint,
lang ctags_languages,
tool_id integer,
tool_name text,
tool_version text,
tool_configuration jsonb
);

-- Search within ctags content.
--
create or replace function swh_content_ctags_search(
expression text,
l integer default 10,
last_sha1 sha1 default '\x0000000000000000000000000000000000000000')
returns setof content_ctags_signature
language sql
as $$
select c.id, name, kind, line, lang,
i.id as tool_id, tool_name, tool_version, tool_configuration
from content_ctags c
inner join indexer_configuration i on i.id = c.indexer_configuration_id
where hash_sha1(name) = hash_sha1(expression)
and c.id > last_sha1
order by id
limit l;
$$;

comment on function swh_content_ctags_search(text, integer, sha1) IS 'Equality search through ctags'' symbols';


-- create a temporary table for content_fossology_license tmp_content_fossology_license,		-- create a temporary table for content_fossology_license tmp_content_fossology_license,
create or replace function swh_mktemp_content_fossology_license()		create or replace function swh_mktemp_content_fossology_license()
returns void		returns void
language sql		language sql
as $$		as $$
create temporary table if not exists tmp_content_fossology_license (		create temporary table if not exists tmp_content_fossology_license (
id sha1,		id sha1,
license text,		license text,
▲ Show 20 Lines • Show All 41 Lines • ▼ Show 20 Lines
-- content_metadata functions		-- content_metadata functions

-- add tmp_content_metadata entries to content_metadata, overwriting duplicates		-- add tmp_content_metadata entries to content_metadata, overwriting duplicates
--		--
-- If filtering duplicates is in order, the call to		-- If filtering duplicates is in order, the call to
-- swh_content_metadata_missing must take place before calling this		-- swh_content_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_metadata), 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()		create or replace function swh_content_metadata_add()
returns bigint		returns bigint
language plpgsql		language plpgsql
as $$		as $$
declare		declare
res bigint;		res bigint;
begin		begin
Show All 27 Lines

-- add tmp_directory_intrinsic_metadata entries to directory_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_directory_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(directory_intrinsic_metadata), 1. COPY to
-- tmp_directory_intrinsic_metadata, 2. call this function		-- tmp_directory_intrinsic_metadata, 2. call this function
create or replace function swh_directory_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
▲ Show 20 Lines • Show All 49 Lines • ▼ Show 20 Lines

-- add tmp_origin_intrinsic_metadata entries to origin_intrinsic_metadata,		-- add tmp_origin_intrinsic_metadata entries to origin_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_origin_intrinsic_metadata_missing must take place before calling this		-- swh_origin_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(origin_intrinsic_metadata), 1. COPY to
-- tmp_origin_intrinsic_metadata, 2. call this function		-- tmp_origin_intrinsic_metadata, 2. call this function
create or replace function swh_origin_intrinsic_metadata_add()		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
▲ Show 20 Lines • Show All 57 Lines • ▼ Show 20 Lines

-- add tmp_origin_extrinsic_metadata entries to origin_extrinsic_metadata,		-- add tmp_origin_extrinsic_metadata entries to origin_extrinsic_metadata,
-- overwriting duplicates.		-- overwriting duplicates.
--		--
-- If filtering duplicates is in order, the call to		-- If filtering duplicates is in order, the call to
-- swh_origin_extrinsic_metadata_missing must take place before calling this		-- swh_origin_extrinsic_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(origin_extrinsic_metadata), 1. COPY to
-- tmp_origin_extrinsic_metadata, 2. call this function		-- tmp_origin_extrinsic_metadata, 2. call this function
create or replace function swh_origin_extrinsic_metadata_add()		create or replace function swh_origin_extrinsic_metadata_add()
returns bigint		returns bigint
language plpgsql		language plpgsql
as $$		as $$
declare		declare
res bigint;		res bigint;
begin		begin
▲ Show 20 Lines • Show All 60 Lines • Show Last 20 Lines