| content
| - %VOSWARNING%
%META:TOPICINFO{author="KingsleyIdehen" date="1161195828" format="1.1" version="1.1"}%
%META:TOPICPARENT{name="VOSRDF"}%
%VOSNAV%
---+ Mapping Relational Data to RDF in Virtuoso
Orri Erling (Program Manager, OpenLink Virtuoso), Ivan Mikhailov (Lead Developer, OpenLink Virtuoso).
---++ Abstract
This paper discusses Virtuoso's new declarative Meta Schema Language for
mapping SQL Data to RDF Ontologies. This functionality, commonly referred
to as "Linked Data Views", enables the exposure of pre-existing ODBC or JDBC
accessible relational data as Virtual RDF Graphs thereby exposing said
data to SPARQL query access directly or via Virtuoso's in-built support
for SPARQL within SQL (SPASQL). The entire process results in RDF Data
sets and optimized data access without physical regeneration of RDF Data
Sets from SQL Data.
Considering that almost all of the present generation of Web 2.0 and
Enterprise applications run atop relational databases, the need for
efficiently exposing existing data to the burgeoning Semantic Web is
obvious. For instance, it now possible to dynamically generate RDF Data
Sets for popular ontologies such as SIOC, SKOS, FOAF, and ATOM/OWL without
disruption to the existing database infrastructure that drives existing
Web 1.0 or Web 2.0 solutions. The same applies to Enterprises seeking to
exploit many of the collaborative prowess of the Web 2.0 application
profiles. As part of this paper we provide a simple example of how the
Weblog Data Space within the OpenLink Data Spaces collective is mapped
to the AtomOWL Ontology en route to making the Weblog Data Space data
accessible to SPARQL.
---++ Introduction
The Virtuoso RDF meta schema is a built-in feature of Virtuoso's SPARQL
to SQL translator. It recognizes triple patterns that refer to graphs
for which an alternate representation is declared and translates these
into SQL accordingly. The main purpose of this is mapping SPARQL queries
onto existing relational databases.
There exists previous work from many parties for rendering relational data
as RDF and opening it to SPARQL access. We can mention D2RQ, SPASQL, Squirrel
RDF and others. The Virtuoso effort differs from these mainly in the following:
* Integration with a triple store. Virtuoso can process a query for which
some triple patterns will go to local or remote relational data and some to
local physical RDF triples.
* Integration with SQL. Since SPARQL and SQL share the same run time and
query optimizer, the query compilation decisions are always made with the
best knowledge of the data and its location. This is especially important
when mixing triples and relational data or when dealing with relational data
distributed across many outside databases.
* No limits on SPARQL. It remains possible to make queries with unspecified
graph or predicate against mapped relational data, even though these may
sometimes be inefficient.
* Coverage of the whole relational model. Multi-part keys, etc., are
supported in all places.
---++ Rationale
Since most of the data that is of likely use for the emerging semantic web is
stored in relational databases, the argument for exposing this to SPARQL access
is clear. We note that historically, SQL access to relational data has essentially
never been given to the public outside of the organization. If programmatic access
to corporate IS has been available to the public, it has been through dynamic web
pages or more recently web services. There are reasons of performance, security,
maintainability and so forth for this.
The culture of the emerging semantic web is however taking a different turn. Since
RDF and OWL offer a merge-able and query-able model for heterogeneous data, it is
more meaningful and maintainable to expose selected data for outside query than
it would be with SQL. Advances in hardware make this also less of a performance
issue than it would have been in the client-server database era.
In the context of Virtuoso, since Virtuoso is originally a virtual/federated
database, incorporating SPARQL to relational mapping is an evident extension of
the product's mission as a multi-protocol, multi-platform connector between
information systems.
---++ Graph Patterns, Value and IRI Classes
In the simplest sense, any relational schema can be rendered into RDF by converting
all primary keys and foreign keys into IRI's, assigning a predicate IRI to each
column, and an rdf:type predicate for each row linking it to a RDF class IRI
corresponding to the table. Then a triple with the primary key IRI as subject,
the column IRI as predicate and the column's value as object is considered to
exist for each column that is neither part of a primary or foreign key.
Strictly equating a subject value to a row and each column to a predicate is often
good but is too restrictive for the general case. For example, multiple triples
with the same object and predicate can exist. Or a single subject can get
single-valued properties from multiple tables or in some cases stored procedures.
#BasicLanguageElements
The basic elements of the Virtuoso RDF meta schema are:
#IriClass
* IRI Class - This declares that a key column or set of key columns gets converted
into a URI in a certain way. When declaring that a table's primary key is converted
into a URI according to one IRI class, one usually declares that all foreign keys
referring to this class also get converted into an IRI as per this same class.
#ValueClass
* Value Class - This is a mechanism for unit conversions, for converting
enumerations into IRI's or any other scalar to scalar conversion. The difference
between a value class and an IRI class is that a value resulting from converting
a relational column according to a value class is not necessarily an IRI joinable
to the subject or predicate of a triple.
#QuadPattern
* Quad pattern - This declares that a quad with any combination of specified or
unspecified G, S, P or O is stored in a non-default way. The patterns are grouped
together in Linked Data Views. A quad pattern consists of the following:
* Subject - This can be an IRI class followed by one of more columns of a
table. The IRI class specifies how the column values are combined into an IRI
string and how an IRI string is decomposed back into the key value. Alternately,
this can be a constant IRI.
* Predicate - This is most often a constant IRI but can also be an IRI class
as with the subject.
* Object - This can be an IRI class applied to columns, a value class applied
to a column or a literal IRI or scalar.
* Condition - This is an optional SQL search condition. If the search condition
is true when applied to a row of the table designated in the subject, predicate and
object patterns, then the triple is considered to exist, otherwise not.
* Optional RDF type of the subject. If the subject is known to be of a type
disjoint from this type, then the quad pattern will not apply. We note that when S,
P, or O refers to columns of a table, all of these should be to the same table. For
complex joins, the table in question can also be a SQL view joining multiple tables.
Thus the search condition of the quad pattern does not have to be used as a join
condition between different tables.
* Graph - This is most often a constant IRI but can also be an IRI class as
with the subject. It is very common for many quad patterns of one SQL schema to
share one constant graph, but one meta schema may consist of quad patterns with
any number of different graph declarations.
* Quad Storage -- This is a named set of quad patterns. This is used for
compartmentalizing the RDF to SQL mapping. This allows specifying that a SPARQL
query will be executed using only quad patterns of the given quad storage.
Declarations of IRI classes, value classes and quad patterns are shared between
all quad storages of an RDF meta schema but every quad storage contains only a
subset of all available quad patterns. Two quad storages are always defined: a
'virtrdf:default' one usually consists of everything (all relational mappings
plus a table of physical quads) and an 'virtrdf:empty' storage that refers to
single table of physical quads).
A special case of IRI or value class is the identity class, which is simply
marked by table.column. For this to occur as S or P, the column must be of the
IRI ID type. For an O, the column may be of any type.
#SiocMappingExample1
---+++ Example - Mapping Virtuoso Database Users and Roles to Accounts in the SIOC Ontology
Here we map the Virtuoso sys_users and sys_role_grant tables to
the SIOC ontology. The tables are in part declared as follows, some columns are
omitted for brevity:
create table sys_users (u_id int primary key, u_name varchar, u_is_role int, u_e_mail, u_home varchar, u_dav_enable int...)
create table sys_role_grants (gi_super int, gi_sub int, gi_direct, primary key (gi_super, gi_sub, gi_direct));
The column gi_super is the role and gi_sub is
the principal to which the role is granted. Both refer to u_id
in sys_users. A row of sys_users is either a role or an account,
as indicated by u_is_role. All role memberships, including
indirect ones are listed in sys_role_grants, the
gi_direct flag shows whether this is a directly granted role.
We can map this into the sioc:User and sioc:Usergroup RDF classes. These are then
related by the mutually inverse predicates sioc:member_of and sioc:has_member.
Additionally, a group can have other groups as members, although not circularly.
To define this mapping, we need the following group of declarations that form
one big statement:
sparql
pragma prefix oplsioc:
pragma prefix sioc:
-- We put all the SIOC names in the original SIOC namespace. The names that pertain
to the mapping go into the oplsioc namespace.
drop graph iri("^{URIQADefaultHost}^/dataspace") .
-- We have the notation ^{URIQADefaultGraph}^ to refer to the default host declared
for the server on which the mapping is being loaded. This allows IRI's to be
dereferenceable on the host itself. For example, the IRI's declared for the various
keys can go to dynamic web pages giving information on the entities being described.
In addition, the URIQA protocol server uses this host name to convert relative URIs
of local web pages into absolute URIs that can be used by browsers.
create iri class oplsioc:user_iri
"http://^{URIQADefaultHost}^/sys/user?id=%d" (in uid integer not null) .
create iri class oplsioc:group_iri
"http://^{URIQADefaultHost}^/sys/group?id=%d" (in gid integer not null) .
create iri class oplsioc:membership_iri
"http://^{URIQADefaultHost}^/sys/membersip?super=%d&sub=%d"
(in super integer not null, in sub integer not null) .
create iri class oplsioc:dav_iri "http://^{URIQADefaultHost}^%s" (in path varchar) .
create iri class oplsioc:grantee_iri using
function DB.DBA.RDF_DF_GRANTEE_ID_URI (in id integer) returns varchar ,
function DB.DBA.RDF_DF_GRANTEE_ID_URI_INVERSE (in id_iri varchar) returns integer .
make oplsioc:user_iri subclass of oplsioc:grantee_iri .
make oplsioc:group_iri subclass of oplsioc:grantee_iri .
-- Here we declare the entities that we will see in RDF. These are the user and
usergroup and their abstract superclass called grantee. Note that the GI_SUB
column of sys_role_grants can refer to either a user or a group
and we have decided to give them different IRI's. This is not absolutely necessary
since we can tell from the id whether something is a user or group but this may be
good for readability and serves to illustrate features of the mapping.
We can map the group membership simply as a many to many without attributes. For
some queries, though, we will need to see this relationship as an entity with its
own attributes, for example whether the grant is direct and whether it is re-grantable.
(the sql with admin option).
For the case where we are interested in the attributes of the relationship, we define
the iri class for membership, although this is not part of SIOC, hence the oplsioc
namespace.
alter quad storage virtrdf:DefaultQuadStorage
{
create virtrdf:SysUsers as graph iri ("http://^{URIQADefaultHost}^/dataspace") option (exclusive)
{
oplsioc:user_iri (DB.DBA.SYS_USERS.U_ID)
a sioc:user where (^{alias}^.U_IS_ROLE = 0) ;
sioc:email U_E_MAIL ;
sioc:login U_NAME ;
oplsioc:home oplsioc:dav_iri (U_HOME) where (^{alias}^.U_DAV_ENABLE = 1) ;
oplsioc:name U_FULL_NAME where (^{alias}^.U_IS_ROLE = 0) .
oplsioc:group_iri (DB.DBA.SYS_USERS.U_ID)
a sioc:role where (^{alias}^.U_IS_ROLE = 1) .
oplsioc:group_iri (DB.DBA.SYS_ROLE_GRANTS.GI_SUPER)
sioc:has_member oplsioc:grantee_iri (GI_SUB) ;
oplsioc:group_of_membership
oplsioc:membership_iri (GI_SUPER, GI_SUB) .
oplsioc:grantee_iri (DB.DBA.SYS_ROLE_GRANTS.GI_SUB)
sioc:has_function oplsioc:group_iri (GI_SUPER) ;
oplsioc:member_of
oplsioc:membership_iri (GI_SUPER, GI_SUB) .
oplsioc:membership_iri (DB.DBA.SYS_ROLE_GRANTS.GI_SUPER, GI_SUB)
oplsioc:is_direct GI_DIRECT ;
rdf:type oplsioc:grant .
}
}
;
-- Now we declare the actual mapping. In the graph /dataspace of the default
host of the server, the above rules will apply.
The first rule declare that
?s rdf:type sioc:User is true of any
IRI matching the user_iri (sys_users.u_id)
pattern. This means that:
* the constant part of the IRI is as defined for the user_iri
IRI class.
* there is a row of DB.DBA.SYS_USERS with U_ID
equal to the number extracted form the IRI as per the format string in the create
iri class statement, i.e. ^{URIQADefaultHost}^/user?id=%d.
* The rule is applicable U_IS_ROLE column of the row should
be equal to zero.
After this, we map some scalar properties, for example email. In defining repeating
predicates for the same subject, the subject does not have to be repeated if the
semicolon is used as delimiter, just as with the SPARQL syntax for triple patterns.
Since U_E_MAIL is just a string, no special mapping is needed. If
we wanted it as a mailto:// IRI, we could define an IRI class that would convert
the string between the SPARQL and SQL values.
The rule with group_iri (SYS_USERS.U_ID) subject is
almost identical to the first rule with user_iri (SYS_USERS.U_ID) subject.
Then we declare the many-to-many between roles and users. The predicates are the
SIOC has_function and has_member. They are
identical, except that the subject and object are reversed. The group is always
a group, the member is either a group or user, hence the use of the
grantee_iri IRI class. The mapping functions of this IRI class
will have to consult the SYS_USERS table to decide which kind
of IRI to make:
create function DB.DBA.RDF_DF_GRANTEE_ID_URI (in id integer)
{
declare is_role integer;
is_role := coalesce ((select top 1 U_IS_ROLE from DB.DBA.SYS_USERS where U_ID = id));
if (is_role is null)
return NULL;
else if (is_role)
return sprintf ('http://%s:/sys/group?id=%d', get_uriqa_default_host(), id);
else
return sprintf ('http://%s:/sys/user?id=%d', get_uriqa_default_host(), id);
}
;
create function DB.DBA.RDF_DF_GRANTEE_ID_URI_INVERSE (in id_iri varchar)
{
declare parts any;
parts := sprintf_inverse (id_iri, concat ('http://', get_uriqa_default_host(), '/sys/user?id=%d', 0);
if (parts is not null)
{ -- The syntax of given IRI matches to the format pattern for IRIs of user accounts
if (not exists (select top 1 1 from DB.DBA.SYS_USERS where U_ID = parts[0] and not U_IS_ROLE))
return NULL; -- The ID specified is not valid user ID
return parts[0]; -- Both syntax and the ID value are OK
}
parts := sprintf_inverse (id_iri, concat ('http://', get_uriqa_default_host(), '/sys/group?id=%d', 0);
if (parts is not null)
{
if (not exists (select top 1 1 from DB.DBA.SYS_USERS where U_ID = parts[0] and U_IS_ROLE))
return NULL;
return parts[0];
}
return NULL;
}
;
The clause options (exclusive) instructs the SPARQL compiler that all
quads of the graph 'http://^{URIQADefaultHost}^/dataspace' are described
by this set of declared quad patterns. Even if the table of physical quads contains
quads with graph 'http://^{URIQADefaultHost}^/dataspace' they may be
ignored by the SPARQL query. Any group of quad patterns or a single quad pattern can
be declared exclusive. Exclusive clauses may conflict. Consider an exclusive group of
patterns for .../dataspace graph and an single exclusive quad pattern for
sioc:email predicate (say, one might have a table DB.DBA.E_MAILS with G,
S and O columns of triples and a sioc:email predicate is assumed for all
quads stored there). Consider a query
sparql select * where { graph { ?s sioc:email ?o } }
that contains a triple pattern that matches both .../dataspace and
sioc:email mapping rules. Each rule prevents the compiler from using
another rule. To resolve ambiguities, a quad storage may specify the order of quad
patterns so that the compiler will use the first suitable quad pattern and ignore
the rest.
---++ Sample Queries
---+++ Two Triple Patterns As A Single Table Lookup
Select all accounts that have both full names and home DAV collections.
sparql
prefix sioc:
prefix oplsioc:
select ?fullname ?home
where {
graph {
?uid sioc:name ?fullname .
?uid oplsioc:home ?home } }
fullname home
VARCHAR VARCHAR
_____________________________________________________
Ivan Mikhailov http://localhost/DAV/home/iv_an_ru/
Orri Erling http://localhost/DAV/home/oerling/
The generated SQL code contains only one table in FROM clause and its WHERE clause
contains texts of all WHERE clauses of relevant quad patterns; duplicate conditions
are removed.
SELECT "t0"."U_FULL_NAME" AS "fullname",
case (isnull ("t0"."U_HOME")) when 0 then sprintf ('http://localhost%s', "t0"."U_HOME") else NULL end AS "home"
FROM DB.DBA.SYS_USERS AS "t0"
WHERE ("t0"."U_FULL_NAME" is not null)
AND ("t0".U_HOME is not null) and ("t0".U_DAV_ENABLE = 1)
AND ("t0".U_IS_ROLE = 0)
OPTION (QUIETCAST)
---+++ Simple Join of Three Tables
Select all pairs of grantees and their roles, return names instead of integer IDs:
sparql
prefix sioc:
prefix oplsioc:
select ?super ?sub
where {
graph {
?superid sioc:has_member ?subid .
?superid sioc:id ?super .
?subid sioc:id ?sub .
} }
super sub
VARCHAR NOT NULL VARCHAR NOT NULL
____________________________________
SPARQL_UPDATE SPARQL_SELECT
The query is translated into the following SQL:
SELECT "t1"."U_NAME" AS "super",
"t2"."U_NAME" AS "sub"
FROM DB.DBA.SYS_ROLE_GRANTS AS "t0",
DB.DBA.SYS_USERS AS "t1",
DB.DBA.SYS_USERS AS "t2"
WHERE "t0"."GI_SUPER" = "t1"."U_ID" /* superid */
AND "t0"."GI_SUB" = "t2"."U_ID" /* subid */
OPTION (QUIETCAST)
---+++ Optional Clause and Filtering of Table Rows
prefix sioc:
prefix oplsioc:
select ?uid ?login ?fullname ?home
where
{
graph
{
?uid sioc:id ?login .
?uid sioc:name ?fullname .
optional { ?uid oplsioc:home ?home }
}
}
uid login fullname home
VARCHAR VARCHAR NOT NULL VARCHAR VARCHAR
_______________________________________________________________________________________________________________________
http://localhost/sys/user?id=2 dav WebDAV System Administrator NULL
http://localhost/sys/user?id=106 iv_an_ru Ivan Mikhailov http://localhost/DAV/home/iv_an_ru/
http://localhost/sys/user?id=5 nobody Special account NULL
http://localhost/sys/user?id=107 oerling Orri Erling http://localhost/DAV/home/oerling/
The query is translated into the following SQL:
SELECT sprintf ( 'http://localhost/sys/user?id=%d' , "t0"."uid" ) AS "uid",
"t0"."login",
"t0"."fullname",
case (isnull ( "t1"."home" )) when 0 then sprintf ( 'http://localhost%s' , "t1"."home" ) else NULL end AS "home"
FROM (
SELECT "t00"."U_ID" AS "uid",
"t00"."U_NAME" AS "login",
"t01"."U_FULL_NAME" AS "fullname"
FROM DB.DBA.SYS_USERS AS "t00",
(SELECT "t01-int"."U_ID", "t01-int"."U_FULL_NAME"
FROM DB.DBA.SYS_USERS AS "t01-int"
WHERE ("t01-int"."U_FULL_NAME" is not null)
AND ("t01-int".U_IS_ROLE = 0)
) AS "t01"
WHERE "t00"."U_ID" = "t01"."U_ID"
) AS "t0"
LEFT OUTER JOIN (
SELECT "t10"."U_ID" AS "uid", "t10"."U_HOME" AS "home"
FROM (
SELECT "t10-int"."U_ID", "t10-int"."U_HOME" FROM DB.DBA.SYS_USERS AS "t10-int"
WHERE ("t10-int"."U_HOME" is not null)
AND ("t10-int".U_DAV_ENABLE = 1)
) AS "t10") AS "t1"
ON ( "t0"."uid" = "t1"."uid" )
OPTION (QUIETCAST)
---+++ Mix Of Relational Data and Plain RDF Quads
sparql
prefix sioc:
prefix oplsioc:
select ?home ?id ?login
where {
graph ?g { ?id oplsioc:home ?home } .
optional {
graph {
?id sioc:id ?login }
} }
home id login
VARCHAR VARCHAR VARCHAR NOT NULL
_______________________________________________________________________________
http://localhost/DAV/home/iv_an_ru/ http://localhost/sys/user?id=106 iv_an_ru
http://localhost/DAV/home/oerling/ http://localhost/sys/user?id=107 oerling
The context of the first triple pattern does not specify graph, so triples with
oplsioc:home predicate may be retrieved from both the default storage of RDF quads
(table RDF_QUADS) and mapping of relational data (table SYS_USERS). The graph of
the second triple pattern matches 'exclusive' group of quad patterns so the compiled
query will not try to locate sioc:id predicates in RDF_QUADS table and will make no
second union. Thus the intermediate result of the compilation is like this:
(sys_users as t00 union all rdf_quad as t10) left outer join sys_users as t1)
The optimizer will convert a join of unions into a union of joins so the final query is like
(sys_users as t00 left outer join sys_users as t01) union all (rdf_quad as t10 left outer join sys_users as t11)
and can be executed efficiently:
SELECT DB.DBA.RDF_SQLVAL_OF_LONG ("t00"."home") AS "home",
sprintf ('http://localhost/sys/user?id=%d', "t00"."id") AS "id",
"t01"."login"
FROM (
SELECT "t00a"."U_ID" AS "id",
case (isnull ("t00a"."U_HOME")) when 0 then DB.DBA.RDF_MAKE_IID_OF_QNAME (sprintf ('http://localhost%s', "t00a"."U_HOME")) else NULL end AS "home"
FROM DB.DBA.SYS_USERS AS "t00a"
WHERE ("t00a"."U_HOME" is not null) AND ("t00a".U_DAV_ENABLE = 1)
) AS "t00"
LEFT OUTER JOIN (
SELECT "t01-t1"."U_ID" AS "id", "t01-t1"."U_NAME" AS "login"
FROM DB.DBA.SYS_USERS AS "t01-t1") AS "t01"
ON ( "t00"."id" = "t01"."id")
UNION ALL
SELECT DB.DBA.RDF_SQLVAL_OF_LONG ("t10"."home"),
DB.DBA.RDF_SQLVAL_OF_LONG ("t10"."id"),
"t11"."login"
FROM (
SELECT "t10a"."S" AS "id",
DB.DBA.RDF_LONG_OF_OBJ ("t10a"."O") AS "home"
FROM DB.DBA.RDF_QUAD AS "t10a"
WHERE
"t10a"."P" = DB.DBA.RDF_MAKE_IID_OF_QNAME_SAFE (UNAME'http://www.openlinksw.com/schemas/oplsioc#home')) AS "t10"
LEFT OUTER JOIN (
SELECT "t11a"."U_ID" AS "id", "t11a"."U_NAME" AS "login"
FROM DB.DBA.SYS_USERS AS "t11a") AS "t11"
ON ( "t10"."id" = DB.DBA.RDF_MAKE_IID_OF_QNAME (DB.DBA.RDF_DF_GRANTEE_ID_URI ("t11"."id")))
OPTION (QUIETCAST)
---++ Simplifying Queries
In principle, we take each triple pattern from a SPARQL query and expand it into
a union of all the quad mapping rule expansions that apply to it. If we have an
unspecified graph and unspecified ?s ?p ?o, we indeed get the union of everything.
In practice, the graph and the predicates are most often given. This will
considerably limit the number of applicable quad patterns. We further assume
all IRI classes to be disjoint, unless exceptions are declared with the
create ... subclass ... statement shown in the example with
user_iri and grantee_iri. Thus many joins
can be known to be empty at compile time.
In the simplest case, when we have two triple patterns with the same subject and
the subject is mapped to a relational table, we get a join of the table with
itself, with pk = pk. This self-join can however be optimized away. After this,
there will be condition on multiple columns and normal SQL index and join type
selection will apply.
As a last step, after pruning the union terms that are known not to be relevant,
the unions are taken to the top, so that we have a union of joins and not a join
of unions. This is easier for the SQL compiler to optimize.
We note that throughout this process, some expansions of triple patterns can refer
to relational tables and some to the default RDF_QUAD table or other native RDF
storage tables. This is not a problem at any stage. Thus a mixed representation
where some things are obtained from local or remote relational stores and some
things are stored locally as physical triple is entirely feasible.
---++ SQL Optimizer Support for Meta Schema
An equality of two IRI's can become an AND of key column equalities. When this
takes place between SPARQL triple patterns mapped to the relational tables, the
columns that make up the URI are simply compared pair-wise, as in any regular
relational join. However, it is also possible that a URI composed from relational
columns is compared with an IRI_ID stored in a physical triple. In this case, we
have a comparison between and S, P or O of a triple and a function over one or
more columns of a relational table.
If the relational table is first in join order, there is no problem. The function
is evaluated and the result is used to retrieve the triple. However, if the triple
is first in join order, we must be able to decompose the IRI into its constituent
parts, that is, if the IRI is such that it can be returned by the key to IRI
function corresponding to the columns.
The Virtuoso SQL compiler has a notion of inverse functions. This allows both join
orders to be tried, in one case converting the key part(s) into an IRI ID and in
the other case the IRI_ID into the key part(s).
Also, when a relational key is mapped into an IRI, making an IRI_ID for the text
of the IRI is actually necessary only when there actually is a triple in the triple
store that references this IRI. Thus, if joining a relational key to triples, the
key to IRI_ID function can return NULL if no IRI_ID
exists. The IRI_ID will be made only when an actual triple is created.
When clients consume IRI's anyway as strings, the existence or non-existence of a
physical IRI ID is entirely transparent. To facilitate this situation, the compiler
recognizes the comparison of an IRI column to a string-valued function or variable.
It will automatically insert a 'get IRI_ID for string if exists'
function into the compilation.
We note that when we cast primary keys to IRI's and pass them back and forth, we may
often get situations where key_to_iri (t1.k1, t1.k2) =
key_to_iri (t2.k1, t2.k2) where t1 and t2 are different correlation
names for a pk and fk tables and k1, k2 are the parts of a 2 part key. This is noticed
by the SQL compiler and optimized into t1.k1 = t2.k1 and t1.k2 = t2.k2. Thus no function
indices or the like are needed. It suffices that the key to iri functions are declared
as having inverses and as being bijective with the sinv_create_inverse
function. See the documentation for this.
Also, when joining native RDF triples and relational data, we get situations like
rdf_quad.S = key_to_iri (t1.k1, t1.k2). We note that there may not
be an IRI ID corresponding to the key_to_iri function's result. The
key_to_iri function returns a string, whereas the S,P,O,G of the
RDF_QUAD table are IRI ID's referenced RDF_URL. The SQL compiler recognizes this and
puts a cast there, one that will convert the string returned by the
key_to_iri function into an IRI ID if there indeed is such an ID and
NULL otherwise. Furthermore, because all key_to_iri functions have
inverses, the condition can be rewritten as
iri_to_key_1 (rdf_iri_id_qname (rdf_quad.S)) = t1.k1 and iri_to_key_2 (rdf_iri_id_qname (rdf_quad.S)) = t1.k2.
This allows both join orders to be used with all index usage options, again without
requiring function indices or the like.
We note that all these optimizations will serve equally well whether the relational
tables are local to Virtuoso or not. The native RDF tables will have to be local due
to their use of the native IRI_ID data type.
---++ Using Meta Schema for Triple Storage Optimization
If we know that some predicates are always single-valued within a certain graph, we
can use a relational-like table with the subject as primary key and the objects of
single-valued predicates as columns. Then we can make a single-column index on each
of the columns.
Oracle has used SQL materialized views for creating such data structures on the fly.
Their name for this is SPMJV for Subject-Predicate Matrix Join View. We can use the
meta schema graph patterns for implementing a similar thing at the RDF to SQL
transformation and RDF storage level.
---++ Using Table Valued Functions for RDF on Demand
Data that we wish to make SPARQL query-able are not always in relational tables. For
example, some data can come from web services. Caching all possible outputs of a web
service in a relational table in the anticipation of SPARQL access does not make sense
in the general case.
Also, data is sometimes de-normalized into non-first normal form columns, for example,
the tags of an article can be all stored in a single text column with a text index on
it. Still, for purposes of RDF rendition, we wish to consider each tag as a separate
sioc:topic triple.
For these cases, Virtuoso offers table valued functions, also known as procedure views.
A piece of procedure code can thus be invoked from the FROM clause of a SQL select and
its result set will be considered as that of a derived table.
A graph pattern can refer to columns of procedure views just as it can refer to columns
of actual tables. The SQL compiler will choose a reasonable join order based on cost
information associated with the procedure view.
For example, consider
create procedure TV_PROC (in blog_id int, in post_uid int, in tag varchar)
{
__cost (20, 1, 1000, 100, 5);
/* this means that the procedure costs 20 units to invoke
* and returns 1 row if all parameters have a non-NULL value.
* If the 1st is not given, multiply these by 1000.
* If the 2nd is not given, by 100, if the 3rd is not given, by 5. */
...
/* if blog id is given, loop over all posts and tags.
* If blog id and post id are given, loop over all tags of the post.
* If all are given, give a result row if the post has the tag
* and so on, for all the 8 combinations of null or non-null value of the three parameters */
}
create procedure view BLOG_TAG_VIEW
as TV_PROC (in TV_BLOG_ID int, in TV_POST_ID int, in TV_TAG varchar)
(TV_BLOG_ID int, TV_POST_ID int, TV_TAG varchar);
sparql
graph ....
{
post_iri (BLOG_TAG.TV_BLOG_ID, BLOG_TAG.TV_POST_ID) sioc:topic TV_TAG .
}
Now we can do a query like:
select * from where { ?me sioc:name "Orri" . ?me author_of ?post . ?post sioc:topic ?tag}
The join order will be left to right because the compiler knows that the tag
procedure has a very high cost factor for not specifying the two first arguments,
so it will place the procedure call at the end, where the post id is known, instead
of iterating over all posts and tags and then seeing which of these has "Orri" as
name of author.
#MappingOdsBlogToAtomOwl
---++ Example: Mapping ODS Weblog Post Data to the AtomOWL Ontology
Consider a weblog application from OpenLink Data Spaces (ODS).
A blog consists of blog posts.
Every post may have comments.
Every blog is owned by a user.
A blog owner is a 'database user' registered in DB.DBA.SYS_USERS even if SQL
access is disabled for him. A user may create as many blogs as he wishes.
The application uses a database schema that is not very convenient for the mapping
to AtomOWL.
It is easy to form some set of subjects and predicates, because relational data are
all in 3rd normal form.
It's not easy to form predicates exactly as described in an independent AtomOWL
standard and to form all IRIs to match names of existing resources.
This example demonstrates the urgent need in extending basic data mapping functionality
to map arbitrary joins of relational tables as opposed to VIEWs only.
When four tables form a group (e.g. user, blog, post and comment) come from different
tables we need additional relational views.
create view BLOG.DBA.SYS_BLOG_INFO_FULL as
select
bi.BI_WAI_NAME, -- Blog application instance name
u.U_ID, u.U_NAME, u.U_E_MAIL, u.U_HOME
from
BLOG.DBA.SYS_BLOG_INFO as bi
left outer join DB.DBA.SYS_USERS as u on (U_ID = BI_OWNER)
;
create view BLOG.DBA.SYS_BLOG_FULL as
select
b.B_POST_ID, -- ID of a post
b.B_BLOG_ID, -- ID of a blog where the post appears
b.B_TITLE, -- Post title
b.B_CONTENT, -- Content of a post
b.B_TS, -- Creation time
b.B_USER_ID, -- Post author
b.B_MODIFIED, -- Last modified
bi.BI_WAI_NAME,
u.U_ID, u.U_NAME, u.U_E_MAIL, u.U_HOME
from
BLOG.DBA.SYS_BLOGS as b
left outer join BLOG.DBA.SYS_BLOG_INFO as bi on (BI_BLOG_ID = B_BLOG_ID)
left outer join DB.DBA.SYS_USERS as u on (U_ID = BI_OWNER)
;
create view BLOG.DBA.SYS_BLOG_COMMENT_FULL as
select
BM_ID, -- Id of a comment
BM_TITLE, -- Comment title
BM_COMMENT, -- Text of the comment
BM_NAME, -- Author name
BM_E_MAIL, -- Author e-mail
BM_TS, -- Creation time
BM_IS_PUB, -- Whether the comment message is public or private
B_BLOG_ID, B_CONTENT, B_TITLE, B_POST_ID, B_TS, B_MODIFIED,
BI_WAI_NAME,
U_ID, U_NAME, U_E_MAIL, U_HOME
from
BLOG.DBA.BLOG_COMMENTS bm
left outer join BLOG.DBA.SYS_BLOGS as b on (BM_BLOG_ID = B_BLOG_ID and BM_POST_ID = B_POST_ID)
left outer join BLOG.DBA.SYS_BLOG_INFO as bi on (BI_BLOG_ID = B_BLOG_ID)
left outer join DB.DBA.SYS_USERS as u on (U_ID = BI_OWNER)
;
create view BLOG.DBA.SYS_BLOG_CATEGORY_FULL as
select
MTC_ID, -- ID if a category
MTC_BLOG_ID, -- Id of a blog
MTC_NAME, -- Category name
BI_WAI_NAME, -- Blog application instance name
U_ID, U_NAME, U_E_MAIL, U_HOME
from
BLOG.DBA.MTYPE_CATEGORIES as mtc
left outer join BLOG.DBA.SYS_BLOG_INFO as bi on (MTC_BLOG_ID = BI_BLOG_ID)
left outer join DB.DBA.SYS_USERS as u on (U_ID = BI_OWNER)
;
create view BLOG.DBA.SYS_BLOG_POST_CATEGORY_FULL as
select
B_BLOG_ID, B_CONTENT, B_TITLE, B_POST_ID, B_TS, B_USER_ID, B_MODIFIED,
MTC_ID, MTC_BLOG_ID, MTC_NAME,
BI_WAI_NAME,
U_ID, U_NAME, U_E_MAIL, U_HOME
from
BLOG.DBA.MTYPE_CATEGORIES as mtc
join BLOG.DBA.SYS_BLOGS as b on (B_BLOG_ID = MTC_BLOG_ID)
left outer join BLOG.DBA.SYS_BLOG_INFO as bi on (B_BLOG_ID = BI_BLOG_ID)
left outer join DB.DBA.SYS_USERS as u on (U_ID = BI_OWNER)
;
Now for any combination of tables we have a view that consists of tables with proper
join conditions. Virtuoso has a good chance to create good execution plans for joins
of such views because a 'view' in Virtuoso is expanded like a macro before any join
order decisions are made. When a FROM clause of a SQL SELECT statement contains a view,
the occurrence of a view in FROM clause is replaced with the full SELECT statement of
the view body. So when a view P is a join of tables of P1 and P2, view Q is a join of
Q1 and Q2 and the query is a join of P and Q then the compilation 'flattens' it and
runs same way as if the query were a plain join of P1, P2, Q1 and Q2.
The created views use demonstrate an optimization technique where LEFT OUTER JOINS are
used instead of INNER JOINS as a way of eliminating the generation of redundant table
joins. Thus, If we define a VIEW as: Table A left OUTER JOIN Table B and the JOIN
condition has equalities for all columns matching the unique key of Table B (e.g.,
primary key columns), and only columns of Table A are queried from the VIEW, then the
join with B will neither alter returned columns nor change the number of resulting
rows, implying that they can be omitted. This let us declare a single VIEW using:
"COMMENTS left join BLOGS left join BLOG_INFO left join
SYS_USERS" avoiding the creation of redundant: "COMMENTS left join
BLOGS" and "COMMENTS left join BLOGS left join BLOG_INFO".
sparql
prefix oplsioc:
prefix sioc:
prefix atom:
prefix odsWeblog:
create iri class odsWeblog:feed_iri "http://^{URIQADefaultHost}^/dataspace/%U/weblog/%U" (
in memb varchar not null, in inst varchar not null) .
create iri class odsWeblog:feed_inst_iri "http://^{URIQADefaultHost}^/dataspace/%U/weblog-inst/%U" (
in memb varchar not null, in inst varchar not null) .
create iri class odsWeblog:post_iri "http://^{URIQADefaultHost}^/dataspace/%U/weblog/%U/%U" (
in memb varchar not null, in inst varchar not null, in post varchar not null) .
create iri class odsWeblog:post_inst_iri "http://^{URIQADefaultHost}^/dataspace/%U/weblog-post-inst/%U/%U" (
in memb varchar not null, in inst varchar not null, in post varchar not null) .
create iri class odsWeblog:text_iri "http://^{URIQADefaultHost}^/dataspace/%U/weblog-text/%U/%U" (
in memb varchar not null, in inst varchar not null, in post varchar not null) .
create iri class odsWeblog:cmt_iri "http://^{URIQADefaultHost}^/dataspace/%U/weblog/%U/%U/%d" (
in memb varchar not null, in inst varchar not null, in post varchar not null, in cmtid integer) .
create iri class odsWeblog:cat_iri "http://^{URIQADefaultHost}^/dataspace/%U/weblog-cat/%U/%U" (
in memb varchar not null, in inst varchar not null, in catid varchar not null) .
;
Note that the AtomOWL mapping for blogs will use IRI classes oplsioc:user_iri,
oplsioc:group_iri as they are already declared in the example above. As soon as
views are prepared and IRI classes are tested to ensure that composed IRIs match
to IRIs of actual web pages the rest of quad map declaration is quite straightforward
and every single mapping is as simple as in previous 'users and groups' use case.
#MappingOdsBlogToAtomOwlExample
sparql
prefix oplsioc:
prefix sioc:
prefix atom:
prefix odsWeblog:
create quad storage virtrdf:ODS
{
create virtrdf:Weblog2 as graph iri ("http://^{URIQADefaultHost}^/dataspace/weblog2") option (exclusive)
{
# Feed
odsWeblog:feed_iri (BLOG.DBA.SYS_BLOG_INFO_FULL.U_NAME, BI_WAI_NAME)
a atom:Feed ;
atom:feedInstance odsWeblog:feed_inst_iri (BLOG.DBA.SYS_BLOG_INFO_FULL.U_NAME, BI_WAI_NAME) .
# Feed Instance
odsWeblog:feed_inst_iri (BLOG.DBA.SYS_BLOG_INFO_FULL.U_NAME, BI_WAI_NAME)
a atom:FeedInstance ;
atom:title BI_WAI_NAME .
odsWeblog:feed_inst_iri (BLOG.DBA.SYS_BLOG_CATEGORY_FULL.U_NAME, BI_WAI_NAME)
atom:category odsWeblog:cat_iri (BLOG.DBA.SYS_BLOG_CATEGORY_FULL.U_NAME, BI_WAI_NAME, MTC_ID) .
# The following mappings are excluded from the example: atom:contributor, atom:extension, atom:generator,
# atom:icon, atom:link, atom:logo, atom:rights, atom:subtitle, atom:updated.
# Post
odsWeblog:post_iri (BLOG.DBA.SYS_BLOG_FULL.U_NAME, BI_WAI_NAME, B_POST_ID)
a atom:Entry ;
atom:entryInstance odsWeblog:post_inst_iri (BLOG.DBA.SYS_BLOG_FULL.U_NAME, BI_WAI_NAME, B_POST_ID) .
# Post Instance
odsWeblog:post_inst_iri (BLOG.DBA.SYS_BLOG_FULL.U_NAME, BI_WAI_NAME, B_POST_ID)
a atom:EntryInstance ;
atom:author oplsioc:user_iri (U_ID) ;
atom:containingFeed odsWeblog:feed_inst_iri (BLOG.DBA.SYS_BLOG_FULL.U_NAME, BI_WAI_NAME) ;
atom:content odsWeblog:text_iri (U_NAME, BI_WAI_NAME, B_POST_ID) ;
atom:contributor oplsioc:user_iri (B_USER_ID) ;
atom:published B_TS ;
atom:title B_TITLE ;
atom:updated B_MODIFIED .
odsWeblog:post_inst_iri (BLOG.DBA.SYS_BLOG_POST_CATEGORY_FULL.U_NAME, BI_WAI_NAME, B_POST_ID)
atom:category
odsWeblog:cat_iri (BLOG.DBA.SYS_BLOG_POST_CATEGORY_FULL.U_NAME, BI_WAI_NAME, MTC_ID) .
# The following mappings are excluded from the example: atom:extension, atom:link, atom:rights
# atom:sourceFeed atom:summary.
# Text
odsWeblog:text_iri (BLOG.DBA.SYS_BLOG_FULL.U_NAME, BI_WAI_NAME, B_POST_ID)
a atom:Text ;
rdf:value B_CONTENT .
# The following mappings are excluded from the example: atom:contentBase, atom:contentLang,
# atom:contentSource, atom:contentType, atom:mimeType
# Comment
odsWeblog:cmt_iri (BLOG.DBA.SYS_BLOG_COMMENT_FULL.U_NAME, BI_WAI_NAME, B_POST_ID, BM_ID)
a atom:EntryInstance where (^{alias}^.BM_IS_PUB = 1) .
odsWeblog:cat_iri (BLOG.DBA.SYS_BLOG_CATEGORY_FULL.U_NAME, BI_WAI_NAME, MTC_ID)
a atom:Category ;
atom:categoryLabel MTC_NAME .
# atom:categoryScheme and atom:categoryTerm are excluded from the example, as well as
# a view that is specific for them.
}
}
;
The disadvantage of current SPARQL implementation is that it can create enormous
intermediate SQL queries. A relatively simple SPARQL query like five triple patterns
where every IRI reference comes from a join of four tables results in a query with
tens of joins and the full optimization before the first run takes seconds of CPU time.
Additional problem is a risk of running out of optimization time limit so the server
will stop optimization before it reached the optimal plan.
We are presently developing a rich syntax of quad map declarations, that allows the
application developer to describe join rules in quad mapping.
* There will be no need in explicit CREATE VIEW statements.
* The SPARQL optimizer will never create redundant joins.
* The SPARQL optimizer will be able to prove more about ranges of SPARQL variables
and better eliminate joins of triple patterns that can not produce any bindings.
* The generator of an intermediate SQL code will create the smallest possible query
and minimize the SQL optimization time.
#MappingDemoToCustomOwlExample
---++Mapping Virtuoso Demo Database to a Custom Ontology for the Northwind Data Model
Consider a Virtuoso Demo Database.
This example uses the following tables from the Virtuoso Demo Database SQL Schema:
Customers, Orders, Order Details, Products, Product Categories, Employee, Region,
Country, Province.
Bearing in mind the similarities between the SQL schemas of Virtuoso's Demo database
and Microsoft's Northwind Database (used by ACCESS and Microsoft SQL Server), we have
chosen to broaden the scope of this example by deriving an OWL ontology from a Conceptual
Data Schema Language (CSDL) file that uses XML Schema to conceptually model the Northwind
Schema. The aforementioned Ontology is then used as the basis for declaring mappings
between the SQL Database and the OWL Ontology.
---+++XML Schema for Northwind (CSDL File)
---+++OWL Ontology for Northwind (in N3)
@prefix rdfs: .
@prefix xsd: .
@prefix virtrdf: .
@prefix owl: .
@prefix rdf: .
@prefix northwind: .
northwind: rdf:type owl:Ontology ;
rdfs:label "Northwind" ;
rdfs:comment "Northwind database classes and properties" ;
virtrdf:catName "Northwind" ;
virtrdf:version "1.00" .
northwind:Product rdf:type rdfs:Class ;
rdfs:label "Product" .
northwind:has_category rdf:type rdf:Property ;
rdfs:range northwind:Category ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "Category" .
northwind:has_supplier rdf:type rdf:Property ;
rdfs:range northwind:Supplier ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "Supplier" .
northwind:ProductName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "ProductName" .
northwind:QuantityPerUnit rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "QuantityPerUnit" .
northwind:UnitPrice rdf:type rdf:Property ;
rdfs:range xsd:double ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "UnitPrice" .
northwind:UnitsInStock rdf:type rdf:Property ;
rdfs:range xsd:integer ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "UnitsInStock" .
northwind:UnitsOnOrder rdf:type rdf:Property ;
rdfs:range xsd:integer ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "UnitsOnOrder" .
northwind:ReorderLevel rdf:type rdf:Property ;
rdfs:range xsd:integer ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "ReorderLevel" .
northwind:Discontinued rdf:type rdf:Property ;
rdfs:range xsd:integer ;
rdfs:domain northwind:Product ;
rdfs:cardinality "1" ;
rdfs:label "Discontinued" .
northwind:Supplier rdf:type rdfs:Class ;
rdfs:label "Supplier" .
northwind:CompanyName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "CompanyName" .
northwind:ContactName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "ContactName" .
northwind:ContactTitle rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "ContactTitle" .
northwind:Address rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "Address" .
northwind:City rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "City" .
northwind:Region rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "Region" .
northwind:PostalCode rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "PostalCode" .
northwind:Country rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "Country" .
northwind:Phone rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "Phone" .
northwind:Fax rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "Fax" .
northwind:HomePage rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Supplier ;
rdfs:cardinality "1" ;
rdfs:label "HomePage" .
northwind:Category rdf:type rdfs:Class ;
rdfs:label "Category" .
northwind:CategoryName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Category ;
rdfs:cardinality "1" ;
rdfs:label "CategoryName" .
northwind:Description rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Category ;
rdfs:cardinality "1" ;
rdfs:label "Description" .
northwind:Shipper rdf:type rdfs:Class ;
rdfs:label "Shipper" .
northwind:CompanyName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Shipper ;
rdfs:cardinality "1" ;
rdfs:label "CompanyName" .
northwind:Phone rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Shipper ;
rdfs:cardinality "1" ;
rdfs:label "Phone" .
northwind:Customer rdf:type rdfs:Class ;
rdfs:label "Customer" .
northwind:CompanyName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "CompanyName" .
northwind:ContactName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "ContactName" .
northwind:ContactTitle rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:donorthwind:main northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "ContactTitle" .
northwind:Address rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "Address" .
northwind:City rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "City" .
northwind:Region rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "Region" .
northwind:PostalCode rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "PostalCode" .
northwind:Country rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "Country" .
northwind:Phone rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "Phone" .
northwind:Fax rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Customer ;
rdfs:cardinality "1" ;
rdfs:label "Fax" .
northwind:Employee rdf:type rdfs:Class ;
rdfs:label "Employee" .
northwind:LastName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "LastName" .
northwind:FirstName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "FirstName" .
northwind:Title rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "Title" .
northwind:TitleOfCourtesy rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "TitleOfCourtesy" .
northwind:BirthDate rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "BirthDate" .
northwind:HireDate rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "HireDate" .
northwind:Address rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "Address" .
northwind:City rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "City" .
northwind:Region rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "Region" .
northwind:PostalCode rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "PostalCode" .
northwind:Country rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "Country" .
northwind:HomePhone rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "HomePhone" .
northwind:Extension rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "Extension" .
northwind:Notes rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "Notes" .
northwind:ReportsTo rdf:type rdf:Property ;
rdfs:range xsd:integer ;
rdfs:domain northwind:Employee ;
rdfs:cardinality "1" ;
rdfs:label "ReportsTo" .
northwind:Order rdf:type rdfs:Class ;
rdfs:label "Order" .
northwind:has_customer rdf:type rdf:Property ;
rdfs:range northwind:Customer ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "Customer" .
northwind:has_employee rdf:type rdf:Property ;
rdfs:range northwind:Employee ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "Employee" .
northwind:OrderDate rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "OrderDate" .
northwind:RequiredDate rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "RequiredDate" .
northwind:ShippedDate rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "ShippedDate" .
northwind:order_ship_via rdf:type rdf:Property ;
rdfs:range northwind:Shipper ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "Shipper" .
northwind:Freight rdf:type rdf:Property ;
rdfs:range xsd:double ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "Freight" .
northwind:ShipName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "ShipName" .
northwind:ShipAddress rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "ShipAddress" .
northwind:ShipCity rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "ShipCity" .
northwind:ShipRegion rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "ShipRegion" .
northwind:ShipPostalCode rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "ShipPostalCode" .
northwind:ShipCountry rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Order ;
rdfs:cardinality "1" ;
rdfs:label "ShipCountry" .
northwind:OrderLine rdf:type rdfs:Class ;
rdfs:label "OrderLine" .
northwind:has_order_id rdf:type rdf:Property ;
rdfs:range northwind:Order ;
rdfs:domain northwind:OrderLine;
rdfs:cardinality "1" ;
rdfs:label "Order" .
northwind:has_product_id rdf:type rdf:Property ;
rdfs:range northwind:Product ;
rdfs:domain northwind:OrderLine;
rdfs:cardinality "1" ;
rdfs:label "Product" .
northwind:UnitPrice rdf:type rdf:Property ;
rdfs:range xsd:double ;
rdfs:domain northwind:OrderLine;
rdfs:cardinality "1" ;
rdfs:label "UnitPrice" .
northwind:Quantity rdf:type rdf:Property ;
rdfs:range xsd:integer ;
rdfs:domain northwind:OrderLine;
rdfs:cardinality "1" ;
rdfs:label "Quantity" .
northwind:Discount rdf:type rdf:Property ;
rdfs:range xsd:double ;
rdfs:domain northwind:OrderLine;
rdfs:cardinality "1" ;
rdfs:label "Discount" .
northwind:Country rdf:type rdfs:Class ;
rdfs:label "Country" .
northwind:Name rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "Name" .
northwind:Code rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "Code" .
northwind:SmallFlagDAVResourceName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "SmallFlagDAVResourceName" .
northwind:LargeFlagDAVResourceName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "LargeFlagDAVResourceName" .
northwind:SmallFlagDAVResourceURI rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "SmallFlagDAVResourceURI" .
northwind:LargeFlagDAVResourceURI rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "LargeFlagDAVResourceURI" .
northwind:Lat rdf:type rdf:Property ;
rdfs:range xsd:double ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "Lat" .
northwind:Lng rdf:type rdf:Property ;
rdfs:range xsd:double ;
rdfs:domain northwind:Country ;
rdfs:cardinality "1" ;
rdfs:label "Lng" .
northwind:Province rdf:type rdfs:Class ;
rdfs:label "Province" .
northwind:has_country_code rdf:type rdf:Property ;
rdfs:range northwind:Country ;
rdfs:domain northwind:Provinces ;
rdfs:cardinality "1" ;
rdfs:label "Country Code" .
northwind:ProvinceName rdf:type rdf:Property ;
rdfs:range xsd:string ;
rdfs:domain northwind:Province ;
rdfs:cardinality "1" ;
rdfs:label "ProvinceName" .
---+++ IRI Generator (Entity ID Constructor) Declarations
sparql
prefix demo:
prefix oplsioc:
prefix sioc:
create iri class demo:Category "http://^{URIQADefaultHost}^/demo/Category?id=%d" (in category_id integer not null) .
create iri class demo:Shipper "http://^{URIQADefaultHost}^/demo/Shipper?id=%d" (in shipper_id integer not null) .
create iri class demo:Supplier "http://^{URIQADefaultHost}^/demo/Supplier?id=%d" (in supplier_id integer not null) .
create iri class demo:Product "http://^{URIQADefaultHost}^/demo/Product?id=%d" (in product_id integer not null) .
create iri class demo:Customer "http://^{URIQADefaultHost}^/demo/Customer?id=%d" (in customer_id integer not null) .
create iri class demo:Employee "http://^{URIQADefaultHost}^/demo/Employee?id=%d" (in employee_id integer not null) .
create iri class demo:Order "http://^{URIQADefaultHost}^/demo/Order?id=%d" (in order_id integer not null) .
create iri class demo:OrderLine "http://^{URIQADefaultHost}^/demo/OrderLine?id=%d&prod_id=%d" (in order_id integer not null, in product_id integer not null) .
create iri class demo:Province "http://^{URIQADefaultHost}^/demo/Province?country=%s&province=%s" (in country_name varchar not null, in province_name varchar not null) .
create iri class demo:Country "http://^{URIQADefaultHost}^/demo/Country?country=%s" (in country_name varchar not null) .
;
---+++ SQL to RDF Mapping (Quad Patterns)
sparql
prefix demo:
prefix oplsioc:
prefix sioc:
create quad storage virtrdf:Northwind
from Demo.demo.Products as products
from Demo.demo.Suppliers as suppliers
from Demo.demo.Shippers as shippers
from Demo.demo.Categories as categories
from Demo.demo.Customers as customers
from Demo.demo.Employees as employees
from Demo.demo.Orders as orders
from Demo.demo.Order_Details as order_lines
from Demo.demo.Countries as countries
from Demo.demo.Provinces as provinces
{
create virtrdf:Demo as graph iri ("http://^{URIQADefaultHost}^/demo") option (exclusive)
{
demo:Product (products.ProductID)
a demo:Product
as virtrdf:Product-ProductID ;
demo:has_category demo:Category (products.CategoryID)
as virtrdf:Product-product_has_category ;
demo:has_supplier demo:Supplier (products.SupplierID)
as virtrdf:Product-product_has_supplier ;
demo:ProductName products.ProductName
as virtrdf:Product-name_of_product ;
demo:QuantityPerUnit products.QuantityPerUnit
as virtrdf:Product-quantity_per_unit ;
demo:UnitPrice products.UnitPrice
as virtrdf:Product-unit_price ;
demo:UnitsInStock products.UnitsInStock
as virtrdf:Product-units_in_stock ;
demo:UnitsOnOrder products.UnitsOnOrder
as virtrdf:Product-units_on_order ;
demo:ReorderLevel products.ReorderLevel
as virtrdf:Product-reorder_level ;
demo:Discontinued products.Discontinued
as virtrdf:Product-discontinued .
demo:Supplier (suppliers.SupplierID)
a demo:Supplier
as virtrdf:Supplier-SupplierID ;
demo:CompanyName suppliers.CompanyName
as virtrdf:Supplier-company_name ;
demo:ContactName suppliers.ContactName
as virtrdf:Supplier-contact_name ;
demo:ContactTitle suppliers.ContactTitle
as virtrdf:Supplier-contact_title ;
demo:Address suppliers.Address
as virtrdf:Supplier-address ;
demo:City suppliers.City
as virtrdf:Supplier-city ;
demo:Region suppliers.Region
as virtrdf:Supplier-region ;
demo:PostalCode suppliers.PostalCode
as virtrdf:Supplier-postal_code ;
demo:Country suppliers.Country
as virtrdf:Supplier-country ;
demo:Phone suppliers.Phone
as virtrdf:Supplier-phone ;
demo:Fax suppliers.Fax
as virtrdf:Supplier-fax ;
demo:HomePage suppliers.HomePage
as virtrdf:Supplier-home_page .
demo:Category (categories.CategoryID)
a demo:Category
as virtrdf:Category-CategoryID ;
demo:CategoryName categories.CategoryName
as virtrdf:Category-home_page ;
demo:Description categories.Description
as virtrdf:Category-description ;
demo:Picture categories.Picture
as virtrdf:Category-picture .
demo:Shipper (shippers.ShipperID)
a demo:Shipper
as virtrdf:Shipper-ShipperID ;
demo:CompanyName shippers.CompanyName
as virtrdf:Shipper-company_name ;
demo:Phone shippers.Phone
as virtrdf:Shipper-phone .
demo:Customer (customers.CustomerID)
a demo:Customer
as virtrdf:Customer-CustomerID ;
demo:CompanyName customers.CompanyName
as virtrdf:Customer-company_name ;
demo:ContactName customers.ContactName
as virtrdf:Customer-contact_name ;
demo:ContactTitle customers.ContactTitle
as virtrdf:Customer-contact_title ;
demo:Address customers.Address
as virtrdf:Customer-address ;
demo:City customers.City
as virtrdf:Customer-city ;
demo:Region customers.Region
as virtrdf:Customer-region ;
demo:PostalCode customers.PostalCode
as virtrdf:Customer-postal_code ;
demo:Country customers.Country
as virtrdf:Customer-country ;
demo:Phone customers.Phone
as virtrdf:Customer-phone ;
demo:Fax customers.Fax
as virtrdf:Customer-fax .
demo:Employee (employees.EmployeeID)
a demo:Employee
as virtrdf:Employee-EmployeeID ;
demo:LastName employees.LastName
as virtrdf:Employee-last_name ;
demo:FirstName employees.FirstName
as virtrdf:Employee-first_name ;
demo:Title employees.Title
as virtrdf:title ;
demo:TitleOfCourtesy employees.TitleOfCourtesy
as virtrdf:Employee-title_of_courtesy ;
demo:BirthDate employees.BirthDate
as virtrdf:Employee-birth_date ;
demo:HireDate employees.HireDate
as virtrdf:Employee-hire_date ;
demo:Address employees.Address
as virtrdf:Employee-address ;
demo:City employees.City
as virtrdf:Employee-city ;
demo:Region employees.Region
as virtrdf:Employee-region ;
demo:PostalCode employees.PostalCode
as virtrdf:Employee-postal_code ;
demo:Country employees.Country
as virtrdf:Employee-country ;
demo:HomePhone employees.HomePhone
as virtrdf:Employee-home_phone ;
demo:Extension employees.Extension
as virtrdf:Employee-extension ;
demo:Photo employees.Photo
as virtrdf:Employee-photo ;
demo:Notes employees.Notes
as virtrdf:Employee-notes ;
demo:ReportsTo employees.ReportsTo
as virtrdf:Employee-reports_to .
demo:Order (orders.OrderID)
a demo:Order
as virtrdf:Order-Order ;
demo:has_customer demo:Customer (orders.CustomerID)
as virtrdf:Order-order_has_customer ;
demo:has_employee demo:Employee (orders.EmployeeID)
as virtrdf:Order-order_has_employee ;
demo:OrderDate orders.OrderDate
as virtrdf:Order-order_date ;
demo:RequiredDate orders.RequiredDate
as virtrdf:Order-required_date ;
demo:ShippedDate orders.ShippedDate
as virtrdf:Order-shipped_date ;
demo:order_ship_via demo:Shipper (orders.ShipVia)
as virtrdf:Order-order_ship_via ;
demo:Freight orders.Freight
as virtrdf:Order-freight ;
demo:ShipName orders.ShipName
as virtrdf:Order-ship_name ;
demo:ShipAddress orders.ShipAddress
as virtrdf:Order-ship_address ;
demo:ShipCity orders.ShipCity
as virtrdf:Order-ship_city ;
demo:ShipRegion orders.ShipRegion
as virtrdf:Order-ship_region ;
demo:ShipPostal_code orders.ShipPostalCode
as virtrdf:Order-ship_postal_code ;
demo:ShipCountry orders.ShipCountry
as virtrdf:ship_country .
demo:OrderLine (order_lines.OrderID, order_lines.ProductID)
a demo:OrderLine
as virtrdf:OrderLine-OrderLines ;
demo:has_order_id demo:Order (order_lines.OrderID)
as virtrdf:order_lines_has_order_id ;
demo:has_product_id demo:Product (order_lines.ProductID)
as virtrdf:order_lines_has_product_id ;
demo:UnitPrice order_lines.UnitPrice
as virtrdf:OrderLine-unit_price ;
demo:Quantity order_lines.Quantity
as virtrdf:OrderLine-quantity ;
demo:Discount order_lines.Discount
as virtrdf:OrderLine-discount .
demo:Country (countries.Name)
a demo:Country
as virtrdf:Country-Name ;
demo:Code countries.Code
as virtrdf:Country-Code ;
demo:SmallFlagDAVResourceName countries.SmallFlagDAVResourceName
as virtrdf:Country-SmallFlagDAVResourceName ;
demo:LargeFlagDAVResourceName countries.LargeFlagDAVResourceName
as virtrdf:Country-LargeFlagDAVResourceName ;
demo:SmallFlagDAVResourceURI countries.SmallFlagDAVResourceURI
as virtrdf:Country-SmallFlagDAVResourceURI ;
demo:LargeFlagDAVResourceURI countries.LargeFlagDAVResourceURI
as virtrdf:Country-LargeFlagDAVResourceURI ;
demo:Lat countries.Lat
as virtrdf:Country-Lat ;
demo:Lng countries.Lng
as virtrdf:Country-Lng .
demo:Province (provinces.CountryCode, provinces.Province)
a demo:Province
as virtrdf:Province-Provinces ;
demo:has_country_code demo:Country (provinces.CountryCode)
as virtrdf:has_country_code ;
demo:Province provinces.Province
as virtrdf:Province-Province .
}
}
;
---++ Conclusions
We have described how we can arbitrarily map relational database schemas to RDF ontologies
en route to generating virtual RDF Data Sets (Graphs) that are then accessible to SPARQL
Queries from within SQL or via the SPARQL Query Protocol. All of this is achieved without
compromising the inherent flexibility of the RDF data model or the SPARQL Query Language.
It should also be noted that all of the functionality demonstrated also applies Virtual DBMS
functionality realm of Virtuoso. Thus, you can now map of 3rd party ODBC or JDBC accessible
SQL data to RDF on the fly. Likewise, you can also use the same Virtual DBMS layer to map
data exposed via local or 3rd party SOAP or REST based Web Services to RDF.
In addition to providing immense power and flexibility at the data mapping level, we have
also paid great attention to the low level optimization of Virtuoso's underlying RDF storage
engine (Triple or Quad Store).
---++ Future Research & Development
Future areas of research and development include:
* Supporting declaration of data representation conditioned by the inferred domain of
the triple pattern subject.
* Optimized mapping of joins of relational tables without creating redundant views.
* Supporting disjunction.
* Federating SPARQL queries across multiple SPARQL endpoints.
---++Other SQL to RDF Mapping Projects
* [[http://sites.wiwiss.fu-berlin.de/suhl/bizer/D2RQ/][D2RQ Project]]
* [[http://jena.sourceforge.net/SquirrelRDF/][Squirrel Project]]
* [[http://www.w3.org/2005/05/22-SPARQL-MySQL/XTech][SPASQL Project]]
---++References
[[http://www.w3.org/DesignIssues/RDB-RDF][Relational Databases & The Semantic Web]] - Tim Berners-Lee
CategoryRDF CategoryAtomOWL CategoryTutorial CategorySQL CategoryOntology CategorySIOC CategoryVOS CategoryVirtuoso CategoryODS
%VOSCOPY% |
![[RDF Data]](/fct/images/sw-rdf-blue.png)
![[cxml]](/fct/images/cxml_doc.png)
![[csv]](/fct/images/csv_doc.png)
![[text]](/fct/images/ntriples_doc.png)
![[turtle]](/fct/images/n3turtle_doc.png)
![[ld+json]](/fct/images/jsonld_doc.png)
![[rdf+json]](/fct/images/json_doc.png)
![[rdf+xml]](/fct/images/xml_doc.png)
![[atom+xml]](/fct/images/atom_doc.png)
![[html]](/fct/images/html_doc.png)