DE3A857A5FFB11DA923AF0924C194AED Cluster Main -- Sample code used in the Text Indexing and Filtering article

create table news (n_id int identity (start with 1), n_date datetime, n_text xmltype, n_source varchar, n_title varchar, primary key (n_id));

create text xml index on news (n_text) with key n_id clustered with (n_date, n_source, n_title);

create text trigger on news (n_text);

create procedure news_download (in url varchar) { declare cnt any; declare xt, xp any; declare l int;

cnt := http_client (url); xt := xml tree_doc (cnt);

xp := xpath_eval ('[xmlns:atom="http://www.w3.org/2005/Atom"] //entry', xt, 0); l := length (xp);

for (declare i int, i := 0; i < l; i := i + 1) { declare title, link, dt varchar; title := xpath_eval ('[xmlns:atom="http://www.w3.org/2005/Atom"] ./atom:title/text()', xp[i]); dt := cast (xpath_eval ('[xmlns:atom="http://www.w3.org/2005/Atom"] ./atom:published/text()', xp[i]) as varchar); link := xpath_eval ('[xmlns:atom="http://www.w3.org/2005/Atom"] ./atom:id/text()', xp[i]); if (not exists (select 1 from news where n_source = link)) insert into news (n_date, n_text, n_source, n_title) values (dt, xml_cut (xp[i]), link, title); commit work; } }

news_download ('http://virtuoso.openlinksw.com/blog/gems/atom.xml');

select * from news where contains (n_text, 'database');

select search_excerpt (vector ('database'), cast (n_text as varchar), 10000, 500, 80) from news where contains (n_text, 'database');

create procedure vt words_1 (in tree any, inout res any) { if (isstring (tree)) { res := vector_concat (res, vector (tree)); } else if (isarray (tree)) { foreach (any elt in tree) do vt_words_1 (elt, res); } }

create function vt_words (in q varchar) returns varchar array { declare v any; v := vector (); vt words_1 (vt_parse (q), v); return v; }

select search_excerpt (vt_words('"open source"'), cast (n_text as varchar), 10000, 500, 80) from news where contains (n_text, '"open source"');

Select n_id, n_title, n_source from news where contains (n_text, 'openlink', offband, n_title, offband, n_source);

select n_title from news where xcontains (n_text, '[xmlns:atom="http://www.w3.org/2005/Atom"] //atom:title[text-contains (., "virtuoso")]');

select search_excerpt (vt_words ('"open source"'), cast (n_text as varchar), 10000, 500, 90) from (select top 5 n_id from news where contains (n_text, '"open source"', offband, n_date) order by n_date desc) f, news n where f.n_id = n.n_id option (order);

select search_excerpt (vt_words ('"open source"'), cast (n_text as varchar), 10000, 500, 90) from (select top 1, 5 n_id from news where contains (n_text, '"open source"', offband, n_date) order by n_date desc) f, news n where f.n_id = n.n_id option (order);

tt query_news ('"open source"', 0, 'Article on open source software', 'john@example.com'); tt_query_news ('virtuoso', 1, 'Article on Virtuoso software', 'mary@example.com');

delete from news;

news_download ('http://virtuoso.openlinksw.com/blog/gems/atom.xml');

select * from news n_text_hit;

select search_excerpt (vt_words (tt_query), cast (n_text as varchar), 10000, 400, 80), tt_query, tt_comment, tth_notify from news, news n_text_hit, news_n_text_query where n_id = tth_d_id and tt_id = tth_t_id;