[Author: Geoff Chappell Last Update: 2004-11-01]
An example of semantic integration of data from a relational db

Overview

The following examples demonstrates:

All of the examples use the Northwind database (supplied by Microsoft as a sample database with many of its products). The database is housed within MS SQL server.

Example 1 - Get the schema of the database as RDF/XML [Try It]
var NS = "http://www.example.org/northwind/";

session.namespaces["nw"] = NS;

//define a connection string for the database
var strConn = "Provider=sqloledb;server=192.168.0.4;database=northwind;uid=sa;pwd=";

//connect to the database with the SQL dataservice - specify that we
//want schema only
var dsSql = new DataSource("sql?connect=" + server.urlEncode(strConn) 
    + "&index=yes&get=schema", NS);
	
//select out everything 
var rs = (select ?p ?s ?o using #dsSql where {?p ?s ?o});

//coerce the cursor into a datasource and format as RDF/XML
response.write(datasource(rs).format("application/rdf+xml"));
Example 2 - Display the classes [Try It]

The SQL dataservice maps tables to classes, columns to properties, and rows to instances. The URI for each instance is generated from the key column(s) of the table. Foreign key relationships are added as additional properties (with resource values). 

import "/std/ns.rql";

var NS = "http://www.example.org/northwind/";

session.namespaces["nw"] = NS;

//define a connection string for the database
var strConn = "Provider=sqloledb;server=192.168.0.4;database=northwind;uid=sa;pwd=xxxxx";

//connect to the database with the SQL dataservice - specify that we
//want schema only
var dsSql = new DataSource("sql?connect=" + server.urlEncode(strConn) 
    + "&index=yes&get=schema", NS);
	
//select out the classes and their properties
select ?s ?p using #dsSql where {[rdf:type] ?s [rdfs:Class]}
	and {[rdfs:domain] ?p ?s}
Example 3 - Semantic Integration [Try It]

This example uses a rulebase to say that Employees and Customers are both Persons. Similarly certain name properties of each are mapped onto the name property of Person. This enables the user to just query for Persons without regard to how they are stored in the source schema. An addition benefit of this approach is that the source shema can be changed and only the rules need to be changed.

Note that we could just say e.g. that Employee is a subclass of Person and then use the general purpose ruleset for RDFS semantics. With remote data sources it sometimes makes sense to make the generic rules more specific to eliminate unnecessary querying over the wire. This is either done manually or can be done via an automated compilation pass. 

import "/std/ns.rql";

var NS = "http://www.example.org/northwind/";

session.namespaces["nw"] = NS;

var strConn = "Provider=sqloledb;server=192.168.0.4;database=northwind;uid=sa;pwd=xxxxx";

var dsSql = new DataSource("sql?connect=" + server.urlEncode(strConn) 
    + "&index=yes&get=both", NS);
rulebase si 
{
	//a few rules to enable semantic integration
	infer {[rdf:type] ?a [foaf:Person]} from {[rdf:type] ?a [nw:Employees]};
	infer {[rdf:type] ?a [foaf:Person]} from {[rdf:type] ?a [nw:Customers]};
	infer {[foaf:name] ?s ?n} from {[nw:Customers_ContactName] ?s ?n};
	infer {[foaf:name] ?s ?n} from {[nw:Employees_FirstName] ?s ?fn} 
		and {[nw:Employees_LastName] ?s ?sn}
		and ?n=concat(?fn, ' ', ?sn); 
}


//find all foaf persons and their names
select ?s ?n using #dsSql rulebase si where {[rdf:type] ?s [foaf:Person]} 
	and {[foaf:name] ?s ?n}
	order by ?n;