Exposing Linked Data View URIs associated with "SQL References" on a given Table using a simple function

What?

A function that exposes Linked Data View URIs associated with "SQL References" on a given Table. Note, "SQL References" expose local columns that are part of Foreign Keys associated with other tables. Thus, given two tables 'Orders' and 'Customers' where the 'Orders' table has a foreign key "CustKey?" comprised of the 'CustomerID?' field in the 'Customer' table, the following data dictionary objects would exist in a typical RDBMS schema:
  • 'Orders' table -- foreign key "CustKey?" comprised of the column 'CustomerID?'
  • 'Customers' table -- a reference that identifies the foreign key "CustKey?" its'CustomerID?' component and the table ('Orders' in this case) on which the foreign key was created.

Foreign Keys and References are mirrors that provide powerful mechanisms for referential integrity and smart data navigation.

Why?

Linked Data Views over SQL data provides a powerful mechanism for exercising the virtues of Intensional database definition and data access. Basically, this means that rather that being confined to a representation of data where identifiers such as Primary and Foreign Keys are part of the data (as Extensions) you can explore the use of Identifiers in a manner distinct from actual records. The net effect of this is that each record becomes explicit proposition (or claim) associated with an Identifier that's verifiable via the host DBMS in course of query execution and fulfillment.

The links_dump function enables you to quickly establish a bridge that connects the Intensional and Extensional aspects of the Virtuoso (hybrid model) DBMS:


CREATE PROCEDURE links_dump (in tb varchar)
{
  declare res, tb_name any;
  
  result_names (res);
  
  tb_name := sprintf ('"%S"."%S"."%S"', name_part (tb, 0), name_part (tb, 1), name_part (tb, 2));
  
  FOR SELECT "o" FROM 
    (
      SPARQL SELECT DISTINCT ?o 
             WHERE 
               { 
                 ?s virtrdf:qmTableName ?:tb_name ; 
                 virtrdf:qmPredicateRange-rvrFixedValue ?ref . 
                 ?ref <http://www.w3.org/2000/01/rdf-schema#domain> ?o . 
               }
    ) x DO
    result ("o");
    
  end_result ();
  
}
;

How?

Bearing in mind that a SQL scheme Reference is the inverse of a Foreign Key i.e., given a qualified 'Table Name' you can use References information from a data dictionary (schema) to determine where the fields of said 'Table' are the Referents in Foreign Keys created by other Tables.

Sample Scenario

Prerequisites

Make sure the HTML5 WebDB-to-XMLA Bridge Extension is installed.

Basic Steps

  1. Go to http://demo.openlinksw.com/XMLAexplorer/XMLAexplorer.html



  2. Enter user "vdb" and password "vdb":



  3. Click "Connect":







  4. Expand for ex. the "Demo" Catalog:



  5. Double click on a table, for ex. "Customers":
  6. Should be presented the "Structure" tab showing the table's catalog name, schema, name, columns, etc.



  7. Go to "Primary Keys" tab to view the list of the "Customer" table primary keys:



  8. Go to "Foreign Keys" tab to view the list of the "Customer" table foreign keys:



  9. Using the function links_dump" we are locating TBox URIs derived from the selected table that are used in one of more Linked Data Views (Quad Maps). The list of these URIs is presented in the "References" tab:



  10. For executing queries one can use the "Execute SQL" tab, which in our example shows default query selecting all columns from the table "Demo"."demo"."Customers":







Related