-- makeWWW2005.sql -- Create conference database. Also create
--		      tables for storing generic RDF statements.
-- $Id: makeWWW2005.sql,v 1.2 2004/10/18 06:53:40 eric Exp $

-- These instructions assume you want a database called www2005. If you
-- want a different database name, adjust accordingly.
-- In MySQL, you can create this with:
--   mysqladmin create www2005

-- MySQL invocation:
--   mysql -u root www2005 < makeWWW2005.sql
-- If you want a heterogeneous database, add this command:
--   mysql -u root rdf < makeGenericTripleStore.sql

-- You should see the output:
--   date	time	name	author
--   20050511	8:30	RDF geekery	geek1
-- when you run the last of these commands.

-- Create application-specific tables:

CREATE TABLE Event (id INT NOT NULL, 
		    type ENUM ("talk", "keynote", "food"), 
		    date DATE, 
		    time TIME, 
		    track INT NOT NULL, 
		    name VARCHAR(255), 
		    PRIMARY KEY (id), 
		    UNIQUE KEY u_date_time_track(date, time, track));
INSERT INTO Event (id, type, date, time, track, name) VALUES (1001, "food", 20040510, "19:30", 0, "opening reception");
INSERT INTO Event (id, type, date, time, track, name) VALUES (1002, "talk", 20040511, "8:30", 3, "dating with FOAF");
INSERT INTO Event (id, type, date, time, track, name) VALUES (1003, "talk", 20040511, "8:30", 1, "taking over the world");

CREATE TABLE Track (id INT NOT NULL, 
		    name CHAR(20), 
		    PRIMARY KEY (id), 
		    UNIQUE KEY u_name(name));
INSERT INTO Track (id, name) VALUES (0, "- no track -");
INSERT INTO Track (id, name) VALUES (1, "gigantic web");
INSERT INTO Track (id, name) VALUES (2, "pedantic web");
INSERT INTO Track (id, name) VALUES (3, "romantic web");
INSERT INTO Track (id, name) VALUES (4, "semantic web");

CREATE TABLE Organization (id INT NOT NULL, 
			   name CHAR(64), 
			   address CHAR(191), 
			   PRIMARY KEY (id), 
			   UNIQUE KEY u_name_address(name, address));
INSERT INTO Organization (id, name, address) VALUES (1011, "DC Comics", "100 Broadway, NY, NY, USA");
INSERT INTO Organization (id, name, address) VALUES (1012, "Friendly Enterprises", "101 Broadway, NY, NY, USA");
INSERT INTO Organization (id, name, address) VALUES (1013, "しの", "Shinjuku, JP");

CREATE TABLE Person (id INT NOT NULL, 
		     givenName CHAR(80), 
		     familyName CHAR(80), 
		     organization INT NOT NULL, 
		     PRIMARY KEY (id), 
		     UNIQUE KEY u_givenName_familyName_organization(givenName, familyName, organization));
INSERT INTO Person (id, givenName, familyName, organization) VALUES (1101, "paul", "truehart", 1012);
INSERT INTO Person (id, givenName, familyName, organization) VALUES (1102, "major", "major", 1013);

CREATE TABLE Role (event INT NOT NULL, 
		   person INT NOT NULL, 
		   role ENUM ("author", "presenter", "observer"), 
		   PRIMARY KEY (event, person, role));
INSERT INTO Role (event, person, role) VALUES (1002, 1101, "author");
INSERT INTO Role (event, person, role) VALUES (1002, 1101, "presenter");
INSERT INTO Role (event, person, role) VALUES (1003, 1102, "presenter");


-- Test inserted data

SELECT Event.time, Event.name, Track.name, Person.familyName, Person.givenName, Organization.name
  FROM Event
 INNER JOIN Track ON Event.track=Track.id
 INNER JOIN Role ON Event.id=Role.event AND Role.role="presenter"
 INNER JOIN Person ON Role.person=Person.id
 INNER JOIN Organization ON Person.organization=Organization.id
 WHERE Event.date=20040511


-- Expected results from above query:

-- +----------+-----------------------+--------------+------------+-----------+
-- | time     | name                  | name         | familyName | givenName |
-- +----------+-----------------------+--------------+------------+-----------+
-- | 08:30:00 | taking over the world | gigantic web | major      | major     |
-- | 08:30:00 | dating with FOAF      | romantic web | truehart   | paul      |
-- +----------+-----------------------+--------------+------------+-----------+


-- Algae query:
-- ns db=<http://www.w3.org/2003/01/21-RDF-RDB-access/ns#>
-- ns ev=<http://www2005.org/schema/event#>
-- ns tr=<http://www2005.org/schema/track#>
-- ns rl=<http://www2005.org/schema/role#>
-- ns pr=<http://www2005.org/schema/person#>
-- ns rg=<http://www2005.org/schema/org#>
-- attach db:SqlDB ?ww (properties="../test/www2005.prop")
-- ask ?ww (?e ev:time ?time.
--          ?e ev:name ?ename.
--          ?e ev:track ?t.
--          ?t tr:name ?tname.
--          ?r rl:event ?e.
--          ?r rl:role "presenter".
--          ?r rl:person ?p.
--          ?p pr:givenName ?givenName.
--          ?p pr:familyName ?familyName.
--          ?p pr:givenName ?givenName.
--          ?p pr:organization ?o.
--          ?o rg:name ?oname)
-- collect (?time ?ename ?tname ?familyName ?givenName ?oname)

-- SPARQL query:
-- PREFIX ev : <http://www2005.org/schema/event#>
-- PREFIX tr : <http://www2005.org/schema/track#>
-- PREFIX rl : <http://www2005.org/schema/role#>
-- PREFIX pr : <http://www2005.org/schema/person#>
-- PREFIX rg : <http://www2005.org/schema/org#>
-- SELECT ?time ?ename ?tname ?familyName ?givenName ?oname
--  WHERE (?e ev:time ?time)
--        (?e ev:name ?ename)
--        (?e ev:track ?t)
--        (?t tr:name ?tname)
--        (?r rl:event ?e)
--        (?r rl:role "presenter")
--        (?r rl:person ?p)
--        (?p pr:givenName ?givenName)
--        (?p pr:familyName ?familyName)
--        (?p pr:givenName ?givenName)
--        (?p pr:organization ?o)
--        (?o rg:name ?oname)

