Re: [Virtuoso-users] Help with stored procedure

[Ivan M. <imi...@op...>]

Sebastian,

On Thu, 2011-11-24 at 13:16 +0100, Sebastian Trüg wrote:
> Hi Ivan,
> 
> thanks a lot for your proposal. It is almost identical to what I ended
> up doing. Except for the last few lines where I used:
> 
>        foreach(any row in rows) do
>         {
>             cnt := cnt + 1;
>             result(sprintf('sparql clear graph <%s>', row[0]));
>             if(state <> '00000')
>                 signal(state, msg);
>        }
> 
> Should I rather use "sparql clear graph identified by iri(?:g)"?
> Also is the "commit work" necessary?

result() will not execute the query and will not set "state" or "msg"
variables.

commit work is not necessary for small number of small graphs, but many
big graphs may exceed transaction log limit size.

Best Regards,

Ivan.

> 
> Cheers,
> Sebastian
> 
> On 11/24/2011 12:31 PM, Ivan Mikhailov wrote:
> > Hello Sebastian,
> > 
> > Consider this:
> > 
> > create procedure nrl_drop_graphs(in query_base varchar, in query_params
> > any := null)
> > {
> >   declare stat, msg varchar;
> >   declare metas, rset any;
> >   stat := '00000';
> >   if (query_params is null)
> >     query_params := vector ();
> >   exec ('sparql ' || query_base, stat, msg, query_params, 1000, metas,
> > rset);
> >   if ('00000' <> stat) -- If error state is set while the query is
> > executed...
> >     signal (stat, msg); -- ...then we resignal it.
> >   if (1 <> length (metas[0]))
> >     signal ('?????', 'The query is supposed to return exactly one
> > column, containing graphs to drop');
> >   foreach (any row in rset) do
> >     {
> >       declare g any;
> >       g := row[0]; -- The value of first (and the only) columns of row.
> >       if (__tag(g) in (__tag of varchar, __tag of nvarchar, __tag of
> > IRI_ID))
> >         {
> >           sparql drop silent graph identified by iri(?:g);
> >           commit work;
> >         }
> >       else
> >         signal ('?????', 'Invalid graph IRI');
> >     }
> > }
> > ;
> > 
> > A basic test is:
> > 
> > sparql insert in graph <http://test1/> { <s1> <test> <o1> };
> > sparql insert in graph <http://test2/> { <s2> <test> <o2> };
> > sparql insert in graph <http://test3/> { <s2> <test> <o3> };
> > 
> > sparql select * where { graph ?g { ?s <test> ?o} };
> > --- Should return three rows.
> > 
> > nrl_drop_graphs ('select ?g where { graph ?g { ?s <test> ?o }}');
> > 
> > sparql select * where { graph ?g { ?s <test> ?o} };
> > --- Should return zero rows because graphs are just dropped.
> > 
> > Best Regards,
> > 
> > Ivan Mikhailov
> > OpenLink Software
> > http://virtuoso.openlinksw.com
> > 
> > On Wed, 2011-11-23 at 21:04 +0100, Sebastian Trüg wrote:
> >> Hello list,
> >>
> >> I am trying to write a stored procedure which deletes a set of graphs
> >> matching a query. This is what I have so far:
> >>
> >> create procedure nrl_drop_graphs(IN QUERY_BASE STRING)
> >> {
> >>     DECLARE query STRING;
> >>     query := query_base + ' LIMIT 1000';
> >>
> >>     declare cnt integer;
> >>     cnt := 0;
> >>
> >>     while(1)
> >>     {
> >>         declare it cursor for sparql query;
> >>         open it;
> >>         declare v any;
> >>         while(1)
> >>         {
> >>             WHENEVER NOT FOUND goto done;
> >>             FETCH it INTO g;
> >>             vector_concat(v, g);
> >>         }
> >>         done: ;
> >>         close it;
> >>
> >>         if(length(v)=0) return;
> >>
> >>         for(any g in v)
> >>         {
> >>             cnt := cnt + 1;
> >>             sparql drop graph g;
> >>         }
> >>     }
> >> }
> >>
> >> The idea is to get all the graphs in batches of 1000 and drop them one
> >> by one since that is not possible with SPARUL (at least not in 6.1.4).
> >>
> >> It fails at the "declare cursor" since it is not happy with the
> >> "sparql". This is the first time I try my luck with stored procedures so
> >> the code may contains lots of stupidities. :P
> >>
> >> Thanks for your help.
> >>
> >> Cheers,
> >> Sebastian