ISSUE-19: Reuse a SQL query as a sub-query

Reuse SQL query as sub-query

Reuse a SQL query as a sub-query

State:
POSTPONED
Product:
R2RML
Raised by:
David McNeil
Opened on:
2011-02-01
Description:
Reuse a SQL query as a sub-query under multiple TriplesMaps.

For example, there may be a repeated sub-query that is used by several
queries. Rather than copy-and-paste the query multiple times, it is
desirable to write it once and join it into several other queries. In
particular, consider the case where the mapping process is not allowed to
make changes to the database (e.g. by adding a view to the database).

A possible solution is to allow a logical table (i.e. query) to be built in
the mapping from sub-logical-tables.
Related Actions Items:
No related actions
Related emails:
  1. Re: Agenda for RB2RDF Teleconference Aprrl 26, 2011 (from seema.sundara@oracle.com on 2011-04-26)
  2. Re: Agenda for RB2RDF Teleconference Aprrl 26, 2011 (from souripriya.das@oracle.com on 2011-04-26)
  3. Re: Agenda for RB2RDF Teleconference Aprrl 26, 2011 (from soeren.auer@gmail.com on 2011-04-26)
  4. Re: D2R's many-to-many definition (from michael.hausenblas@deri.org on 2011-04-26)
  5. Re: Agenda for RB2RDF Teleconference Aprrl 26, 2011 (from marcelo.arenas1@gmail.com on 2011-04-26)
  6. D2R's many-to-many definition (from eric@w3.org on 2011-04-26)
  7. Re: Agenda for RB2RDF Teleconference Aprrl 26, 2011 (from michael.hausenblas@deri.org on 2011-04-25)
  8. Agenda for RB2RDF Teleconference Aprrl 26, 2011 (from ashok.malhotra@oracle.com on 2011-04-25)
  9. Re: ISSUE-19: Reuse a SQL query as a sub-query (from dmcneil@revelytix.com on 2011-04-20)
  10. Minutes of 2011-04-19 telecon (from michael.hausenblas@deri.org on 2011-04-19)
  11. Re: RDB2RDF WG agenda for 2011-03-29 meeting 1600 UTC (from privera.salas@gmail.com on 2011-03-29)
  12. Re: RDB2RDF WG agenda for 2011-03-29 meeting 1600 UTC (from nuno.lopes@deri.org on 2011-03-29)
  13. Re: RDB2RDF WG agenda for 2011-03-29 meeting 1600 UTC (from richard@cyganiak.de on 2011-03-25)
  14. Re: RDB2RDF WG agenda for 2011-03-29 meeting 1600 UTC (from ivan@w3.org on 2011-03-25)
  15. RDB2RDF WG agenda for 2011-03-29 meeting 1600 UTC (from michael.hausenblas@deri.org on 2011-03-25)
  16. Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from bertails@w3.org on 2011-02-02)
  17. Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from dmcneil@revelytix.com on 2011-02-02)
  18. Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from ashok.malhotra@oracle.com on 2011-02-02)
  19. Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from dmcneil@revelytix.com on 2011-02-02)
  20. Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from ivan@w3.org on 2011-02-02)
  21. Re: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from dmcneil@revelytix.com on 2011-02-02)
  22. Fwd: ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from ivan@w3.org on 2011-02-02)
  23. Re: SQL in R2RML mappings (from dmcneil@revelytix.com on 2011-02-01)
  24. ISSUE-19 (Reuse SQL query as sub-query): Reuse a SQL query as a sub-query [R2RML] (from sysbot+tracker@w3.org on 2011-02-01)

Related notes:

This issue does not need to be addressed for the imminent publication of an updated draft of the R2RML spec.

David McNeil, 22 Mar 2011, 16:25:31

This issue is blocked by ISSUE-18, see [1] and will be opened again once this one is resolved.


[1] http://www.w3.org/2011/04/19-rdb2rdf-minutes.html#item03

Michael Hausenblas, 19 Apr 2011, 16:39:05

This is an example of how the Revelytix Spyder product supports combining SQL statements. The TriplesMap below has multiple sources (each of which is a logical table with a name/label). The TriplesMap includes a "joinString" property defining how to join these two sub-queries. One way to think of this is that a TriplesMap is also an RDF representation of a SQL query.

This is just one example, there are other mapping properties used for things like specifying "opaque" SQL statements (i.e. db vendor specific SQL) and joining on simple criteria. The full power of SQL is not exposed in RDF properties (e.g. HAVING is not supported in the RDF representation of a query, although it is supported in the SQL patterns themselves). If there is interest I can provide more examples of how these properties are used.

The Revelytix mapping language constructs are described here:
* http://www.knoodl.com/ui/groups/Mapping_Ontology_Community
* http://www.knoodl.com/ui/groups/Mapping_Ontology_Community/wiki/RDB_Mapping_Language_Specification_v0.2;jsessionid=C12F6F58064DE95E4F519DA73B700F5A

----

@prefix rr: <http://www.w3.org/ns/r2rml#> .
@prefix map: <http://revelytix.com/spyder/vocab/mapping/> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .

<http://mine/Person>
a <http://www.w3.org/2000/01/rdf-schema#Class>, <http://www.w3.org/2002/07/owl#Class>
.

<http://mine/SQLPattern>
a map:SQLPattern;
rdfs:label "job_query";
map:pattern "SELECT * FROM JOB";
map:opaque "true";
map:columns "JOB_ID ASSIGNED_ID";
.

<http://mine/SubjectMap>
rr:template "{EMPLOYEE_ID}-{job_query.JOB_ID}";
rr:class <http://mine/Person>
.

<http://mine/TriplesMap>
a rr:TriplesMapClass;
map:source <http://db/Table/EMPLOYEE>;
map:source <http://mine/SQLPattern>;
map:joinString "EMPLOYEE join job_query on EMPLOYEE.EMPLOYEE_ID = job_query.ASSIGNED_ID";
rr:subjectMap <http://mine/SubjectMap>;
.

David McNeil, 20 Apr 2011, 13:19:34

Display change log ATOM feed

Ashok Malhotra <ashok.malhotra@oracle.com>, Michael Hausenblas <michael.hausenblas@deri.org>, Chairs, Eric Prud'hommeaux <eric@w3.org>, Ivan Herman <ivan@w3.org>, Staff Contacts
Tracker: documentation, (configuration for this group), originally developed by Dean Jackson, is developed and maintained by the Systems Team <w3t-sys@w3.org>.
$Id: 19.html,v 1.1 2012/10/16 16:28:34 vivien Exp $