093.sql
No OneTemporary
Actions

Size

1 KB

Subscribers

None

093.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: 92
	-- to_version: 93
	-- description: Full text search on content_ctags

	insert into dbversion(version, release, description)
	values(93, now(), 'Work In Progress');

	alter table content_ctags add column searchable_symbol tsvector;

	comment on column content_ctags.searchable_symbol is 'Searchable symbol derived from name column';

	update content_ctags set searchable_symbol = to_tsvector('english', name);

	create trigger content_ctags_tsvectorupdate before insert or update
	on content_ctags for each row execute procedure
	tsvector_update_trigger(searchable_symbol, 'pg_catalog.english', name);

	create index searchable_symbol_idx ON content_ctags USING GIN (searchable_symbol);

	create type content_ctags_signature as (
	id sha1,
	name text,
	kind text,
	line bigint,
	lang ctags_languages
	);

	drop function swh_content_ctags_get();

	-- Retrieve list of content ctags 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_ctags_get()
	returns setof content_ctags_signature
	language plpgsql
	as $$
	begin
	return query
	select c.id, c.name, c.kind, c.line, c.lang
	from tmp_bytea t
	inner join content_ctags c using(id)
	order by line;
	return;
	end
	$$;

	comment on function swh_content_ctags_get() IS 'List content ctags';

	-- Search within ctags content.
	--
	create or replace function swh_content_ctags_search(expression text)
	returns setof content_ctags_signature
	language sql
	as $$
	select id, name, kind, line, lang
	from content_ctags
	where searchable_symbol @@ to_tsquery(expression);
	$$;

	comment on function swh_content_ctags_search(text) IS 'Search through ctags'' symbols';

File Metadata

Mime Type: text/plain
Expires: Sat, Jun 21, 5:06 PM (1 w, 6 d ago)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3242787

093.sqlNo OneTemporaryActions

093.sqlView Options

File Metadata

Event Timeline

093.sql
No OneTemporary
Actions

093.sql
View Options