-- makeOrderTracking.sql -- Create tables for storing generic RDF statements.
-- $Id: makeGenericTripleStore.sql,v 1.3 2007-10-18 21:16:51 eric Exp $

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

-- MySQL invocation:
--   mysql -u root rdf < makeGenericTripleStore.sql

-- Create generic triplestore tables:

CREATE TABLE __Big__ (id int(10) unsigned NOT NULL auto_increment,
		      hash varchar(32) NOT NULL default '',
		      str text NOT NULL,
		      PRIMARY KEY  (id),
		      UNIQUE KEY u_hash (hash)) TYPE=MyISAM;

CREATE TABLE __Nodes__ (id int(10) unsigned NOT NULL auto_increment,
		        type enum('uri','literal','bnode','table') default NULL,
		        hash varchar(32) NOT NULL default '',
		        str varchar(255) NOT NULL default '',
		        big int(10) unsigned default NULL,
		        attribOrDT int(10) unsigned NOT NULL default '0',
		        PRIMARY KEY  (id),
		        UNIQUE KEY u_hash (type,hash),
		        UNIQUE KEY u_type_str_big_attribOrDT (type,str,big,attribOrDT));

CREATE TABLE __Attributions__ (id int(11) NOT NULL auto_increment,
			       type enum('source','generated','reified') NOT NULL default 'source',
			       doc int(11) unsigned NOT NULL default '0',
			       auth int(11) unsigned default NULL,
			       modified timestamp(14) NOT NULL,
			       created timestamp(14) NOT NULL,
			       PRIMARY KEY  (id),
			       UNIQUE KEY u_type_doc (type,doc));

CREATE TABLE __AttrLists__ (id int(11) NOT NULL auto_increment,
			    listId int(11) NOT NULL default '0',
			    a int(11) NOT NULL default '0',
			    PRIMARY KEY  (id),
			    UNIQUE KEY u_listId_a (listId,a));

CREATE TABLE __Holds__ (id int(10) unsigned NOT NULL auto_increment,
		        p int(10) unsigned NOT NULL default '0',
		        s int(10) unsigned NOT NULL default '0',
		        o int(10) unsigned NOT NULL default '0',
		        r int(10) unsigned NOT NULL default '0',
		        a int(10) unsigned NOT NULL default '0',
		        PRIMARY KEY  (id),
		        UNIQUE KEY u_p_s_o (p,s,o));

CREATE TABLE __Lists__ (
       id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY, 
       list INT UNSIGNED NOT NULL, 
       member  INT UNSIGNED NOT NULL, 
       first INT UNSIGNED NOT NULL, 
       g INT UNSIGNED NOT NULL, 
       UNIQUE u_list_member (list, member));

DELIMITER |
CREATE TRIGGER mirrorInLists AFTER INSERT ON __Holds__
  FOR EACH ROW BEGIN

/* Ensure __Lists__.first entries for { NEW.s rdf:first NEW.o } . */

  /* Found the rdf:first predicate before the rdf:rest: */
INSERT IGNORE INTO __Lists__ (list, member, first, g)
SELECT NEW.s, NEW.s, NEW.o, 0
  FROM __Holds__
 WHERE __Holds__.s=NEW.s AND __Holds__.p=NEW.p AND __Holds__.o=NEW.o AND
       NEW.p=(SELECT id 
                FROM __Nodes__ 
               WHERE hash="3536baaa80ff04b3c4f7474332e53c5f") /* rdf:first */
;
  /* Found the rdf:first predicate after the rdf:rest.
     We have already calculated some closures so simply updating duplicate
     keys on the above INSERT won't get all the entries with this member. */
UPDATE __Lists__ SET first=NEW.o
 WHERE member=NEW.s AND first IS NULL AND
       NEW.p=(SELECT id 
                FROM __Nodes__ 
               WHERE hash="3536baaa80ff04b3c4f7474332e53c5f") /* rdf:first */
;

/* Maintain transitive closure over __Lists__.member when
   inserting triple { NEW.s rdf:rest NEW.o }. */

  /* Make sure both s and o have g0 entries. */
INSERT IGNORE INTO __Lists__ (list, member, first, g)	/* s */
SELECT NEW.s, NEW.s, NULL, 0
  FROM __Holds__
 WHERE __Holds__.s=NEW.s AND __Holds__.p=NEW.p AND __Holds__.o=NEW.o AND 
       NEW.p=(SELECT id 
                FROM __Nodes__ 
               WHERE hash="6a744d33889cef9e113293f07527191f") /* rdf:rest */
   AND NEW.o!=(SELECT id 
                 FROM __Nodes__ 
                WHERE hash="05ace48b449b22bbc1cb9aaae5997049") /* rdf:nill */
;
INSERT IGNORE INTO __Lists__ (list, member, first, g)	/* o */
SELECT NEW.o, NEW.o, NULL, 0
  FROM __Holds__
 WHERE __Holds__.s=NEW.s AND __Holds__.p=NEW.p AND __Holds__.o=NEW.o AND 
       NEW.p=(SELECT id 
                FROM __Nodes__ 
               WHERE hash="6a744d33889cef9e113293f07527191f") /* rdf:rest */
   AND NEW.o!=(SELECT id 
                 FROM __Nodes__ 
                WHERE hash="05ace48b449b22bbc1cb9aaae5997049") /* rdf:nill */
;
  /* Each lists with member NEW.s includes each member of list NEW.o */
INSERT INTO __Lists__ (list, first, member, g)
SELECT sub.list, super.first, super.member, super.g+sub.g+1
  FROM __Lists__ AS sub
       INNER JOIN __Lists__ AS super ON super.list=NEW.o
 WHERE sub.member=NEW.s AND 
       NEW.p=(SELECT id 
                FROM __Nodes__ 
               WHERE hash="6a744d33889cef9e113293f07527191f") /* rdf:rest */
   AND NEW.o!=(SELECT id 
                 FROM __Nodes__ 
                WHERE hash="05ace48b449b22bbc1cb9aaae5997049") /* rdf:nill */
;
  END;|
DELIMITER ;

/* Test something like this:

INSERT INTO __Lists__ (list, member, first, g)
VALUES ("_:l0", "_:l0", 1, 0), 
       ("_:l0", "_:l1", 2, 1), 
       ("_:l1", "_:l1", 2, 0), 
       ("_:l3", "_:l3", 4, 0), 
       ("_:l3", "_:l4", 5, 1), 
       ("_:l4", "_:l4", 5, 0);

INSERT INTO __Holds__ (s, p, o) VALUES ('_:l1', 'rst', '_:l2');
INSERT INTO __Holds__ (s, p, o) VALUES ('_:l2', '1st', 3);
INSERT INTO __Holds__ (s, p, o) VALUES ('_:l2', 'rst', '_:l3');


echo "<s> <p> (1 2 3 4 5)" | ./algae -a rdf2.prop -i turtle -d - "SELECT ?s ?member WHERE { ?s <p> members(?member) }"

SELECT s.str, mem.str FROM __Holds__ AS h INNER JOIN __Nodes__ AS s ON s.id=h.s INNER JOIN __Nodes__ AS p ON p.id=h.p AND p.str='data:p' INNER JOIN __Lists__ AS l ON l.list=h.o INNER JOIN __Nodes__ AS mem ON mem.id=l.first ORDER BY l.g;

SELECT t.id, s.str AS a, p.str AS p, o.str AS o, a.str AS doc, auth.str AS auth, at.modified FROM __Holds__ AS t LEFT OUTER JOIN __Nodes__ AS s ON t.s=s.id LEFT OUTER JOIN __Nodes__ AS p ON t.p=p.id LEFT OUTER JOIN __Nodes__ AS o ON t.o=o.id LEFT OUTER JOIN __AttrLists__ AS al ON t.a=al.listId LEFT OUTER JOIN __Attributions__ AS at ON al.a=at.id LEFT OUTER JOIN __Nodes__ AS a ON at.doc=a.id LEFT OUTER JOIN __Nodes__ AS auth ON at.auth=auth.id;

SELECT s.str,p.str,o.str from __Holds__ AS h, __Nodes__ AS s, __Nodes__ AS p, __Nodes__ AS o WHERE s.id=h.s AND p.id=h.p AND o.id=h.o;
*/

