Reading Virtuoso Transaction Logs

What?

A Stored Procedure that uses the in-built read_log() function to read the Transaction log of a Virtuoso Instance, in regards to Quad Store activity.

Why?

There are times when you application functionality benefits from being able to read Virtuoso Transaction logs. In particular, the database triggers on the RDF_QUAD table are not meant to be supported in Virtuoso 7+, thus reading transaction logs as indicated below is the recommended method for tracking changes to the RDF_QUAD table.

How?

To read the changes made to the RDF_QUAD table i.e RDF data, one can use the following Virtuoso Stored procedure that uses the in-built read_log():

Sample Scenario

  1. Insert sample data so to change the rdf_quad index: 

    SQL> SPARQL INSERT INTO <g> { <s> <p> <o> };

  2. Create the following example procedure: 

    create procedure rlt (in f any, in inpos int := 0) 
{   
  declare h, op, g, s, p, o any;
  declare pos int;
  result_names (op, g, s, p, o);
  h := file_open (f, inpos);
  declare r, rr any;
  while ((rr := read_log (h, pos)) is not null)
    {
      declare rw, k any;
      declare i int;
      rw := null;
      k := null;
      for (i := 1; i < length (rr); i := i + 1)
        {
	  r := rr[i];
	  if (r[0] = 13) -- key insert
	    {
	      rw := r[2];
	      op := 'I';
	    }
	  else if (r[0] in (1,8,9)) -- insert,soft,replacing
	    {
	      rw := r[1];
	      op := 'I';
	    }
	  else if (r[0] in (3,14)) -- delete
	    {
	      rw := r[1];
	      op := 'D';
	    }
	  if (rw is not null)
	    {
	      k := rw[0];
	      if (k = 273) -- RDF_QUAD, should check with SYS_KEYS
		{
		  result (op, __ro2sq (rw[1]), __ro2sq (rw[2]), __ro2sq (rw[3]), __ro2sq (rw[4]));
		}
	    }
	}
    } 
  result (pos + inpos, '', '', '', '');
}
;

  3. Call the procedure:
    • In case of no changes to the RDF_QUAD Index are done, it will return: 

      SQL> rlt('tmp/Virtuoso.trx');
Query result:
op    g   s   p    o
ANY   ANY ANY ANY  ANY
8403
No. of rows in result: 1

    • In case of changes to the RDF_QUAD Index are done ( example with the short INSERT we did above), it will return for example: 

      SQL> rlt('tmp/Virtuoso.trx');
Query result:
op     g   s   p    o
ANY    ANY ANY ANY  ANY
I      g   s   p    o
71446
No. of rows in result: 2

To read the transaction log in general, one should use the Virtuoso read_log() function.

Related