VOS.textsample

  • Topic
  • Discussion
  • VOS.textsample(Last) -- DAVWikiAdmin? , 2017-06-13 05:49:28 Edit WebDAV System Administrator 2017-06-13 01:49:28

    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;