4 Replies Latest reply on Nov 19, 2009 5:17 PM by 715399

    ChangeNotification service

      I have several machines with in-memory Ontologies that I need to update based on Database Ontology changes and I want to do it via database events and not polling. I think that ideally ModelOracleSem should dispatch Jena Model-Changed events when such changes occur in the database but that does not seem to be the current implementation.

      Thus instead I have to use Data Change Notification events (together with the change-tracking option in 11g Release 2 Semantic APIs)
      So using the DCN tutorials, I tried making a query-change listener to the statement "select * from mc_table" where mc_table is where my triples are.
      I tried using the following statement instead...

      String notificationSemQuery =
           "select s, p, o from table(SEM_MATCH("
           +" '{"
           +" ?s ?p ?o"
           +" }',"
           +" SEM_Models('mc_model'), SEM_Rulebases('OWLPRIME'), "
           +"null, null ));";

      But the stmt.executeQuery(notificationSemQuery) throws a "ORA-00911: invalid character" SQLException. So...
      1) Where is the invalid charecter? Can I get this string to work as a change notification query?

      Also, when using "select * from mc_table", I managed to get Data Change Notification working and pull the ROWIDs of the newly added info (i.e. the triples) from the DatabaseChangeEvent in the DatabaseChangeListerner but..
      2) how do I find out the bNodes/URIs from the ROWIDs? Is there a generic way to get Node objects from ROWIDs?
        • 1. Re: ChangeNotification service

          About 1), my guess is that SEM_MATCH table function is using advanced features not supported by Continuous Query Notification. You can check the Advanced Application Developer's Guide[1] for restrictions on CQN.

          Have you considered using Oracle Flashback version queries to keep track of changes (see Chapter 12 at [1])? You can version your queries against the application table for the semantic model you want to track.

          About question 2) - if you have ROWIDs of the newly added rows in the application table, then you can call the PL/SQL functions get_subject(), get_object() and get_property() on the corresponding SDO_RDF_TRIPLE_S objects in those rows.

          [1] http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10471/toc.htm

          • 2. Re: ChangeNotification service
            Hi Vlad,
            Thanks for the reply, the triple.GET_SUBJECT/PROPERTY/OBJECT() procedures are exactly what I was looking for. As for the Oracle Flashback, its a very cool feature that is useful for other things (e.g. MiddleTier Ontology Syncups/Refreshes) so thanks for pointing it out (more on that to come...). Also, I have another question...
            1) I noticed that in the DCN registration I had to set a Properties.DCN_BEST_EFFORT flag. If change tracking is indeed enabled for my SEM_MODEL, are there situations where the DCN Event will fail to fire?

            Speaking of Oracle Flashback, it would be very useful if I could ask "what were all the operations done on a semantic table and what are their SDO_RDF_TRIPLE_S objects in the last 5 minutes". As far as I understand, this means doing something like this... in order to find the changed triples and their transactions ids:
            SELECT versions_xid from semantic_table
            versions between TIMESTAMP

            And then something like this in order to get the operations of those transactions:
            SELECT xid, start_scn, commit_scn, operation, table_name, table_owner
            FROM flashback_transaction_query where
            table_name = 'semantic_table';

            To my understanding, based on the information of the above statements, I should be able to find out which triples were added and which were deleted during a certain time interval. The only problem is that neither command works. The first command returns a bunch of blank lines. The flashback_transaction_query table does not even have any entries where the table_name="semantic_table". So...
            2) Is there a way to get the above commands to work or some better way to get this done?

            Thanks a lot Vlad for all your responses,

            Edited by: alexi on Nov 19, 2009 12:32 AM - Added second question
            • 3. Re: ChangeNotification service
              Actually, forget about Question 2, I found a better way to do it. You can just find out the rowids that were added and deleted since the last update and then their respective SDO_RDF_TRIPLE_S objects (btw, how scaleable is this solution???)

              Just call this... to find out the statements that have been added in the last interval:

              SELECT a.triple.get_triple() FROM mc_table a
              WHERE NOT EXISTS
              (SELECT * FROM mc_table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE) WHERE rowid = a.rowid);

              And this... to find out the statements that were removed.
              SELECT b.triple.get_triple() FROM mc_update_table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE) b
              WHERE NOT EXISTS
              (SELECT * FROM mc_update_table a WHERE a.rowid = b.rowid);

              Since RDF is immutable* the above should be all you need to sync the in-memory Ontology. This is actually quite useful when used together with Data Change Notification.

              *That is... so long as you use Jena Adapter Interfaces and not the DB's "update" statement - please correct me if I am wrong

              Thanks again Vlad for you help and suggestions.
              • 4. Re: ChangeNotification service
                Hi Alexi,

                Yes, you should be fine if you use the Jena Adaptor APIs because they update the semantic model's application table underneath (which your flashback queries are running against). Note that Oracle Flashback relies on undo data so you might want to make sure the undo data is retained as long as your application needs it (check UNDO_RETENTION parameter).