Connecting to a relational db

nearby: Creating a database driver.

Table of Contents Create a Relational Database Connecting to a Database Queries Mapping Predicates to SQL Table Columns Mapping Row Identifiers to SQL Primary Key Values

Create a Relational Database

Create a simple database called education:

  CREATE DATABASE education;
  USE education;
  CREATE TABLE person (
    ID INT NOT NULL,
    name VARCHAR(20) DEFAULT NULL,
    PRIMARY KEY (ID)
  );
  INSERT INTO person VALUES (12817,'ericP');
  CREATE TABLE course (
    ID INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (ID)
  );
  INSERT INTO course VALUES (6891,'Some LOD stuff');
  CREATE TABLE enrollment (
    person INT NOT NULL,
    course INT NOT NULL,
    PRIMARY KEY (person,course)
  );
  INSERT INTO enrollment VALUES (12817,6891);

. you can instruct the sparql executable to pipe all queries to that database.

Connecting to a Database

  SPARQL --serve http://localhost:8888/SPARQL --stem http://foo.example/ed/ -S mysql://<span style="color: #f00;">user:password</span>@localhost/education

This tells the server to start on port 8888, that the query endpoint is /SPARQL, that the stem URI (described later) is http://foo.example/ed/, and that it can find your database on localhost in a database called education. Note that user:password will have to be replaced for your access credentials.

You can now point your browser at http://localhost:8888/ and ask questions like:

Queries

  PREFIX person: <http://foo.example/ed/person#>
  PREFIX course: <http://foo.example/ed/course#>
  PREFIX enrollment: <http://foo.example/ed/enrollment#>
  SELECT ?perID ?courseID ?person ?course {
    ?enr enrollment:course ?courseID ; enrollment:person ?perID .
    ?courseID course:name ?course .
    ?perID person:name ?person .
  }

Mapping Predicates to SQL Table Columns

For a each constraint like:

  ?enr enrollment:course ?courseID

the predicate is <http://foo.example/db/enrollment#course>. The stem URI is stripped off, leaving "enrollment#course", which is parsed as table#attribute. The resulting SQL query is:

  SELECT enr.person AS perID, enr.course AS courseID, perID.name AS person, courseID.name AS course
         FROM enrollment AS enr
              INNER JOIN course AS courseID ON courseID.ID=enr.course
              INNER JOIN person AS perID ON perID.ID=enr.person

You can see this by telling the executable to print the resulting query with -npe, (-n (no-exec), -p (print), -e (query) ):

  sparql --stem http://foo.example/ed/ -npe "\
    PREFIX person: <http://foo.example/ed/person#>\
    PREFIX course: <http://foo.example/ed/course#>\
    PREFIX enrollment: <http://foo.example/ed/enrollment#>\
    SELECT ?perID ?courseID ?person ?course {\
      ?enr enrollment:course ?courseID ; enrollment:person ?perID .\
      ?courseID course:name ?course .\
      ?perID person:name ?person .\
    }"

Mapping Row Identifiers to SQL Primary Key Values

Row identifiers have a similar structure to table column predicates. By example, <http://foo.example/db/person/id.12817> is the subject identifier for the row in the person table with id=12817

There's more magic, but this should get you started.