--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- a/directmapping/src/main/scala/DirectMapping.scala Wed Nov 03 14:03:52 2010 -0400
+++ b/directmapping/src/main/scala/DirectMapping.scala Wed Nov 03 14:04:23 2010 -0400
@@ -27,6 +27,7 @@
})
KeyMap(m2)
}
+ def contains(ck:CandidateKey) = m.contains(ck)
}
case class NodeMap(m:Map[RelName, KeyMap]) {
def apply(rn:RelName) = m(rn)
@@ -39,6 +40,7 @@
} else
m(rn)(k)(vs)
}
+ def contains(rn:RelName) = m.contains(rn)
}
implicit def list2map (l:Set[(RelName, KeyMap)]):Map[RelName,KeyMap] = l.toMap
implicit def list2Nmap (l:Set[(RelName, KeyMap)]):NodeMap = NodeMap(l)
@@ -158,6 +160,12 @@
val p = predicatemap (u, r.name, as)
val ls:List[LexicalValue] = t.lexvaluesNoNulls(as)
val target = r.fks(as)
+ if (!nodes.contains(target.rel))
+ error("No referent relation \"" + target.rel + "\" to match " + r.name + t)
+ if (!nodes(target.rel).contains(target.key))
+ error("Relation " + target.rel + " has no attributes (" + target.key + ") to match " + r.name + t)
+ if (!nodes(target.rel)(target.key).contains(ls))
+ error("Relation " + target.rel + "(" + target.key + ") has no values " + ls + " to match " + r.name + t)
val o:Object = nodes(target.rel)(target.key)(ls)
Triple(s, p, o)
}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping/src/test/scala/DirectMappingTest.scala Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,272 @@
+package org.w3.sw.directmapping
+
+import org.w3.sw.rdf._
+import org.w3.sw.rdb.RDB._
+import org.w3.sw.sql.SqlParser
+import org.w3.sw.directmapping.DirectMapping._
+
+import org.scalatest.FunSuite
+
+class Test extends FunSuite {
+
+ implicit def l2db (rs:List[Relation]):Map[RelName, Relation] =
+ rs.map(r => (r.name -> r)).toMap
+ implicit def string2relName (n:String) = RelName(n)
+ implicit def string2attrName (n:String) = AttrName(n)
+
+ test("2 People 1 Addresses") {
+
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT, FOREIGN KEY (addr) REFERENCES Addresses(ID));
+INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
+INSERT INTO People (ID, fname, addr) VALUES (8, "Sue", NULL);
+"""
+ val db = p.parseAll(p.ddl, s).get
+ val g = directDB(StemIRI("http://foo.example/DB"), db)
+
+ val expected:Graph =
+ Set(
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("7",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Bob",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#addr"),IRI("http://foo.example/DB/Addresses/ID.18#_")),
+ Triple(IRI("http://foo.example/DB/People/ID.8#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("8",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.8#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Sue",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#city"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#state"),TypedLiteral("MA",IRI("http://www.w3.org/2001/XMLSchema#string")))
+ )
+ assert (expected === g)
+ }
+
+
+ test("2 People 1 Addresses 1 Department") {
+
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING,
+ manager INT, FOREIGN KEY (manager) REFERENCES People(ID), UNIQUE (name, city));
+INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING,
+ addr INT, FOREIGN KEY (addr) REFERENCES Addresses(ID),
+ deptName STRING, deptCity STRING, FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);
+"""
+ val db = p.parseAll(p.ddl, s).get
+ val g = directDB(StemIRI("http://foo.example/DB"), db)
+
+ val expected:Graph =
+ Set(
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#deptName"),TypedLiteral("accounting",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#deptCity"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#deptName_deptCity"),IRI("http://foo.example/DB/Department/ID.23#_")),
+
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#ID"),TypedLiteral("23",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#name"),TypedLiteral("accounting",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#city"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#manager"),IRI("http://foo.example/DB/People/ID.8#_")),
+
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("7",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Bob",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#addr"),IRI("http://foo.example/DB/Addresses/ID.18#_")),
+ Triple(IRI("http://foo.example/DB/People/ID.8#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("8",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.8#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Sue",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#city"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#state"),TypedLiteral("MA",IRI("http://www.w3.org/2001/XMLSchema#string")))
+ )
+ assert (expected === g)
+ }
+
+
+ test("2 People 1 Addresses 1 Department 2 Projects 1 Task") {
+
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING, manager INT,
+ FOREIGN KEY (manager) REFERENCES People(ID),
+ UNIQUE (name, city));
+INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
+ FOREIGN KEY (addr) REFERENCES Addresses(ID),
+ deptName STRING, deptCity STRING,
+ FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);
+CREATE TABLE Projects (lead INT,
+ FOREIGN KEY (lead) REFERENCES People(ID),
+ name STRING, UNIQUE (lead, name),
+ deptName STRING, deptCity STRING,
+ UNIQUE (name, deptName, deptCity),
+ FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "pencil survey", "accounting", "Cambridge");
+INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "eraser survey", "accounting", "Cambridge");
+CREATE TABLE TaskAssignments (worker INT,
+ FOREIGN KEY (worker) REFERENCES People(ID),
+ project STRING, PRIMARY KEY (worker, project),
+ deptName STRING, deptCity STRING,
+ FOREIGN KEY (worker) REFERENCES People(ID),
+ FOREIGN KEY (project, deptName, deptCity) REFERENCES Projects(name, deptName, deptCity),
+ FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO TaskAssignments (worker, project, deptName, deptCity) VALUES (7, "pencil survey", "accounting", "Cambridge");
+"""
+ val db = p.parseAll(p.ddl, s).get
+ val g = directDB(StemIRI("http://foo.example/DB"), db)
+
+ val expected:Graph =
+ Set(
+ Triple(BNode("a"), IRI("http://foo.example/DB/Projects#lead"), IRI("http://foo.example/DB/People/ID.8#_")),
+ Triple(BNode("a"), IRI("http://foo.example/DB/Projects#name"), TypedLiteral("pencil survey", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(BNode("a"), IRI("http://foo.example/DB/Projects#deptName"), TypedLiteral("accounting", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(BNode("a"), IRI("http://foo.example/DB/Projects#deptCity"), TypedLiteral("Cambridge", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(BNode("a"), IRI("http://foo.example/DB/Projects#deptName_deptCity"), IRI("http://foo.example/DB/Department/ID.23#_")),
+
+ Triple(BNode("b"), IRI("http://foo.example/DB/Projects#lead"), IRI("http://foo.example/DB/People/ID.8#_")),
+ Triple(BNode("b"), IRI("http://foo.example/DB/Projects#name"), TypedLiteral("eraser survey", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(BNode("b"), IRI("http://foo.example/DB/Projects#deptName"), TypedLiteral("accounting", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(BNode("b"), IRI("http://foo.example/DB/Projects#deptCity"), TypedLiteral("Cambridge", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(BNode("b"), IRI("http://foo.example/DB/Projects#deptName_deptCity"), IRI("http://foo.example/DB/Department/ID.23#_")),
+
+ Triple(IRI("http://foo.example/DB/TaskAssignments/worker.7_project.pencil+survey#_"), IRI("http://foo.example/DB/TaskAssignments#worker"), IRI("http://foo.example/DB/People/ID.7#_")),
+ Triple(IRI("http://foo.example/DB/TaskAssignments/worker.7_project.pencil+survey#_"), IRI("http://foo.example/DB/TaskAssignments#project"), TypedLiteral("pencil survey", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/TaskAssignments/worker.7_project.pencil+survey#_"), IRI("http://foo.example/DB/TaskAssignments#deptName"), TypedLiteral("accounting", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/TaskAssignments/worker.7_project.pencil+survey#_"), IRI("http://foo.example/DB/TaskAssignments#deptCity"), TypedLiteral("Cambridge", IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/TaskAssignments/worker.7_project.pencil+survey#_"), IRI("http://foo.example/DB/TaskAssignments#deptName_deptCity"), IRI("http://foo.example/DB/Department/ID.23#_")),
+ Triple(IRI("http://foo.example/DB/TaskAssignments/worker.7_project.pencil+survey#_"), IRI("http://foo.example/DB/TaskAssignments#project_deptName_deptCity"), BNode("a")),
+
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#deptName"),TypedLiteral("accounting",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#deptCity"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#deptName_deptCity"),IRI("http://foo.example/DB/Department/ID.23#_")),
+
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#ID"),TypedLiteral("23",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#name"),TypedLiteral("accounting",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#city"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Department/ID.23#_"),IRI("http://foo.example/DB/Department#manager"),IRI("http://foo.example/DB/People/ID.8#_")),
+
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("7",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Bob",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#addr"),IRI("http://foo.example/DB/Addresses/ID.18#_")),
+ Triple(IRI("http://foo.example/DB/People/ID.8#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("8",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.8#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Sue",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#city"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#state"),TypedLiteral("MA",IRI("http://www.w3.org/2001/XMLSchema#string")))
+ )
+ assert (expected === g)
+ }
+
+
+ test("1 People 1 Addresses 1 Offices") {
+
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
+ FOREIGN KEY (addr) REFERENCES Addresses(ID));
+INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
+CREATE TABLE Offices (ID INT PRIMARY KEY,
+ building INT, ofcNumber STRING,
+ FOREIGN KEY (ID) REFERENCES Addresses(ID));
+INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");
+"""
+ val db = p.parseAll(p.ddl, s).get
+ val g = directDB(StemIRI("http://foo.example/DB"), db)
+
+ val expected:Graph =
+ Set(
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Offices#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Offices#building"),TypedLiteral("32",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Offices#ofcNumber"),TypedLiteral("G528",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("7",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Bob",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#addr"),IRI("http://foo.example/DB/Addresses/ID.18#_")),
+
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#city"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#state"),TypedLiteral("MA",IRI("http://www.w3.org/2001/XMLSchema#string")))
+ )
+ assert (expected === g)
+ }
+
+
+ test("1 People 1 Addresses 1 Offices 1 ExectutiveOffices") {
+
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
+ FOREIGN KEY (addr) REFERENCES Addresses(ID));
+INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
+CREATE TABLE Offices (ID INT PRIMARY KEY,
+ building INT, ofcNumber STRING,
+ FOREIGN KEY (ID) REFERENCES Addresses(ID));
+INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");
+CREATE TABLE ExecutiveOffices (ID INT PRIMARY KEY,
+ desk STRING,
+ FOREIGN KEY (ID) REFERENCES Offices(ID));
+INSERT INTO ExecutiveOffices (ID, desk) VALUES (18, "oak");
+"""
+
+ val db = p.parseAll(p.ddl, s).get
+ val g = directDB(StemIRI("http://foo.example/DB"), db)
+
+ val expected:Graph =
+ Set(
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/ExecutiveOffices#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/ExecutiveOffices#desk"),TypedLiteral("oak",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Offices#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Offices#building"),TypedLiteral("32",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Offices#ofcNumber"),TypedLiteral("G528",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#ID"),TypedLiteral("7",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#fname"),TypedLiteral("Bob",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/People/ID.7#_"),IRI("http://foo.example/DB/People#addr"),IRI("http://foo.example/DB/Addresses/ID.18#_")),
+
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#ID"),TypedLiteral("18",IRI("http://www.w3.org/2001/XMLSchema#int"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#city"),TypedLiteral("Cambridge",IRI("http://www.w3.org/2001/XMLSchema#string"))),
+ Triple(IRI("http://foo.example/DB/Addresses/ID.18#_"),IRI("http://foo.example/DB/Addresses#state"),TypedLiteral("MA",IRI("http://www.w3.org/2001/XMLSchema#string")))
+ )
+ assert (expected === g)
+ }
+
+
+ test("NodeMap") {
+
+ val ck1:CandidateKey = CandidateKey("name", "ssn")
+ val ck2:CandidateKey = CandidateKey("ID")
+ val v11:List[CellValue] = List(LexicalValue("bob"), LexicalValue("123"))
+ val v21:List[CellValue] = List(LexicalValue("alice"), LexicalValue("8"))
+ val v12:List[CellValue] = List(LexicalValue("18"))
+ val v22:List[CellValue] = List(LexicalValue("23"))
+ val s1:Node = BNode("1")
+ val s2:Node = BNode("2")
+ val data:Set[(List[(CandidateKey, List[CellValue])], Node)] =
+ Set((List((ck1, v11),(ck2, v21)), s1),
+ (List((ck1, v12),(ck2, v22)), s2))
+ val test = data.foldLeft(KeyMap(Map[CandidateKey, Map[List[CellValue], Node]]()))((m, t) => m ++ (t._1, t._2))
+
+ val goal:KeyMap = KeyMap(
+ Map(ck1 -> Map(v11 -> s1,
+ v12 -> s2),
+ ck2 -> Map(v21 -> s1,
+ v22 -> s2))
+ )
+ assert(goal === test)
+ }
+
+}
+
--- a/project/build/RDB2RDF.scala Wed Nov 03 14:03:52 2010 -0400
+++ b/project/build/RDB2RDF.scala Wed Nov 03 14:04:23 2010 -0400
@@ -15,7 +15,7 @@
lazy val rdb = project("rdb", "rdb", new RDB(_))
lazy val sql = project("sql", "sql", new SQL(_), rdb)
lazy val rdf = project("rdf", "rdf", new RDF(_))
- lazy val directmapping = project("directmapping", "directmapping", new DirectMapping(_), rdb, rdf)
+ lazy val directmapping = project("directmapping", "directmapping", new DirectMapping(_), rdb, rdf, sql)
lazy val sparql = project("sparql", "sparql", new SPARQL(_), rdf)
lazy val sparql2sql = project("sparql2sql", "sparql2sql", new SPARQL2SQL(_), sparql, sql)
lazy val sparql2sparql = project("sparql2sparql", "sparql2sparql", new SPARQL2SPARQL(_), sparql)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/rdb/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- a/rdb/src/main/scala/RDB.scala Wed Nov 03 14:03:52 2010 -0400
+++ b/rdb/src/main/scala/RDB.scala Wed Nov 03 14:04:23 2010 -0400
@@ -14,7 +14,12 @@
Database(l.map{r => (r.name -> r)}.toMap)
}
- case class Relation (name:RelName, header:Header, body:List[Tuple], candidates:List[CandidateKey], pk:Option[CandidateKey], fks:ForeignKeys)
+ case class Relation (name:RelName, header:Header, body:List[Tuple], candidates:List[CandidateKey], pk:Option[CandidateKey], fks:ForeignKeys) {
+ def ++ (t:Tuple):Relation = {
+ val b2:List[RDB.Tuple] = body ++ List(t)
+ Relation(name, header, b2, candidates, pk, fks)
+ }
+ }
case class Header (m:Map[AttrName, Datatype]) {
def apply (a:AttrName) = m(a)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sparql/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sparql2sparql/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sparql2sql/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sparql2sqlendpoint/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)
--- a/sql/src/main/scala/SQL.scala Wed Nov 03 14:03:52 2010 -0400
+++ b/sql/src/main/scala/SQL.scala Wed Nov 03 14:04:23 2010 -0400
@@ -8,7 +8,8 @@
object SQLParsers extends RegexParsers {
val int = """[0-9]+""".r
- val chars = "\"([^\"\\\\\n\r]|\\\\[tbnrf\\\"'])*\"".r
+ val dquote = "\"([^\"\\\\\n\r]|\\\\[tbnrf\\\"'])*\"".r
+ val squote = "\'([^\'\\\\\n\r]|\\\\[tbnrf\\\''])*\'".r
}
import SQLParsers._
@@ -166,6 +167,7 @@
case class FieldDesc(attr:RDB.AttrName, datatype:RDB.Datatype, pkness:Boolean) extends FieldDescOrKeyDeclaration
sealed abstract class KeyDeclaration extends FieldDescOrKeyDeclaration
case class PrimaryKeyDeclaration(key:RDB.CandidateKey) extends KeyDeclaration
+case class CandidateKeyDeclaration(key:RDB.CandidateKey) extends KeyDeclaration
case class ForeignKeyDeclaration(fk:List[RDB.AttrName], rel:RDB.RelName, pk:RDB.CandidateKey) extends KeyDeclaration
case class View(rel:RDB.RelName, defn:SelectORUnion) { // sibling of RDB.Relation
override def toString = "CREATE VIEW " + rel + " AS\n" + defn
@@ -177,16 +179,27 @@
"CREATE" ~ "VIEW" ~ relation ~ "AS" ~ selectORunion ^^
{ case "CREATE"~"VIEW"~relation~"AS"~defn => View(relation, defn) }
+ sealed abstract class DataDefinition
+ case class Create(rn:RDB.RelName, relation:RDB.Relation) extends DataDefinition
+ case class Insert(rn:RDB.RelName, tuple:RDB.Tuple) extends DataDefinition
+
def ddl:Parser[RDB.Database] =
- rep1sep(createtable, ";") ~ opt(";") ^^
+ rep1sep(createorinsert, ";") ~ opt(";") ^^
{
case l~x => RDB.Database(l.foldLeft(Map[RDB.RelName, RDB.Relation]())((m, p) => {
- val (rel:RDB.RelName, desc:RDB.Relation) = p
- m + (rel -> desc)
+ p match {
+ case Create(rn:RDB.RelName, relation:RDB.Relation) => m + (rn -> relation)
+ case Insert(rn:RDB.RelName, tuple:RDB.Tuple) => m + (rn -> (m(rn) ++ tuple)) // add the tuple
+ }
}))
}
- def createtable:Parser[(RDB.RelName, RDB.Relation)] =
+ def createorinsert:Parser[DataDefinition] = (
+ createtable ^^ { case c => c }
+ | inserttable ^^ { case c => c }
+ )
+
+ def createtable:Parser[Create] =
"CREATE" ~ "TABLE" ~ relation ~ "(" ~ rep1sep(fielddescorkeydef, ",") ~ ")" ^^
{
case "CREATE"~"TABLE"~relation~"("~reldesc~")" => {
@@ -211,12 +224,26 @@
case PrimaryKeyDeclaration(key) =>
// @@ why doesn't [[ candidates + RDB.CandidateKey(attr.n) ]] work?
(Some(key), attrs, candidates ++ List(RDB.CandidateKey(key map {attr => RDB.AttrName(attr.n)})), fks)
+ case CandidateKeyDeclaration(key) =>
+ // @@ why doesn't [[ candidates + RDB.CandidateKey(attr.n) ]] work?
+ (pkopt, attrs, candidates ++ List(RDB.CandidateKey(key map {attr => RDB.AttrName(attr.n)})), fks)
case ForeignKeyDeclaration(fk, rel, pk) =>
(pkopt, attrs, candidates, fks + (fk -> RDB.Target(rel, pk)))
}
})
val rd = RDB.Relation(relation, RDB.Header(attrs), List(), candidates, pk, RDB.ForeignKeys(fks))
- (relation -> rd)
+ Create(relation, rd)
+ }
+ }
+
+ def inserttable:Parser[Insert] =
+ "INSERT" ~ "INTO" ~ relation ~ "(" ~ rep1sep(attribute, ",") ~ ")" ~ "VALUES" ~ "(" ~ rep1sep(value, ",") ~ ")" ^^
+ {
+ case "INSERT"~"INTO"~relation~"("~relnames~")"~"VALUES"~"("~values~")" => {
+ if (relnames.size != values.size)
+ error("different numbers of elements in names " + relnames + " vs. values " + values)
+ Map.empty ++ (relnames zip values)
+ Insert(relation, RDB.Tuple(Map.empty ++ (relnames zip values)))
}
}
@@ -225,18 +252,25 @@
{ case attribute~typpe~pkness => FieldDesc(attribute, typpe, pkness.isDefined) }
| "PRIMARY" ~ "KEY" ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
{ case "PRIMARY"~"KEY"~"("~attributes~")" => PrimaryKeyDeclaration(RDB.CandidateKey(attributes)) }
+ | "UNIQUE" ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
+ { case "UNIQUE"~"("~attributes~")" => CandidateKeyDeclaration(RDB.CandidateKey(attributes)) }
| "FOREIGN" ~ "KEY" ~ "(" ~ rep1sep(attribute, ",") ~ ")" ~ "REFERENCES" ~ relation ~ "(" ~ rep1sep(attribute, ",") ~ ")" ^^
{ case "FOREIGN"~"KEY"~"("~fk~")"~"REFERENCES"~relation~"("~pk~")" => ForeignKeyDeclaration(fk, relation, RDB.CandidateKey(pk)) }
)
def typpe:Parser[RDB.Datatype] = (
- "INT" ^^ { case _ => RDB.Datatype.INTEGER }
+ "INT" ~ opt(size) ^^ { case _ => RDB.Datatype.INTEGER } // alias for INTEGER
+ | "INTEGER" ~ opt(size)^^ { case _ => RDB.Datatype.INTEGER }
| "DOUBLE" ^^ { case _ => RDB.Datatype.DOUBLE }
| "STRING" ^^ { case _ => RDB.Datatype.STRING }
+ | "CHAR" ~ opt(size) ^^ { case _ => RDB.Datatype.STRING }
| "DATETIME" ^^ { case _ => RDB.Datatype.DATETIME }
| "DATE" ^^ { case _ => RDB.Datatype.DATE }
)
+ def size:Parser[Int] =
+ "(" ~ int ~ ")" ^^ { case l ~ s ~ r => s.toInt }
+
def selectORunion:Parser[SelectORUnion] =
rep1sep(select, "UNION") ^^ { l => if (l.size == 1) l(0) else Union(l.toSet) }
@@ -366,13 +400,21 @@
def primaryexpression:Parser[Expression] = (
fqattribute ^^ { PrimaryExpressionAttr(_) }
| int ^^ { i => PrimaryExpressionTyped(RDB.Datatype.INTEGER, Name(i)) }
- | chars ^^ { x => PrimaryExpressionTyped(RDB.Datatype.STRING, Name(x.substring(1, x.size - 1))) }
+ | dquote ^^ { x => PrimaryExpressionTyped(RDB.Datatype.STRING, Name(x.substring(1, x.size - 1))) }
+ | squote ^^ { x => PrimaryExpressionTyped(RDB.Datatype.STRING, Name(x.substring(1, x.size - 1))) }
| "NULL" ^^ { case "NULL" => ConstNULL() }
| "CONCAT" ~ "(" ~ rep1sep(expression, ",") ~ ")" ^^ { case "CONCAT"~"("~expressions~")" => Concat(expressions) }
| "IF" ~ "(" ~ expression ~ "," ~ expression ~ "," ~ expression ~ ")" ^^ { case "IF"~"("~c~","~p~","~f~")" => IfElse(c, p, f) }
| "(" ~ expression ~ ")" ^^ { case "("~x~")" => x }
)
+ def value:Parser[RDB.CellValue] = (
+ int ^^ { i => RDB.LexicalValue(i) }
+ | dquote ^^ { x => RDB.LexicalValue(x.substring(1, x.size - 1)) }
+ | squote ^^ { x => RDB.LexicalValue(x.substring(1, x.size - 1)) }
+ | "NULL" ^^ { case "NULL" => RDB.␀() }
+ )
+
}
case class PrettySql(select:Select) {
--- a/sql/src/test/scala/SQLTest.scala Wed Nov 03 14:03:52 2010 -0400
+++ b/sql/src/test/scala/SQLTest.scala Wed Nov 03 14:04:23 2010 -0400
@@ -422,7 +422,7 @@
assert(expected === (a.parseAll(a.select, e).get))
}
- test("x") {
+ test("parse PRIMARY KEY") {
val a = SqlParser()
val e = """
ID INT PRIMARY KEY
@@ -431,7 +431,7 @@
assert(expected === (a.parseAll(a.fielddescorkeydef, e).get))
}
- test("y") {
+ test("parse CREATE") {
val a = SqlParser()
val e = """
CREATE TABLE Sex_DE (ID INT PRIMARY KEY)
@@ -603,6 +603,417 @@
assert(expected === (a.parseAll(a.createview, e).get))
}
+
+ test("parse single quotes") {
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, 'Cambridge', 'MA');
+"""
+ val addrs = RDB.Relation(
+ "Addresses",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "city" -> RDB.Datatype.STRING,
+ "state" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "state" -> RDB.LexicalValue("MA"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+
+ val expected = RDB.Database(List(addrs))
+ assert(expected === (p.parseAll(p.ddl, s).get))
+ }
+
+
+ test("parse size modifiers") {
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT(5) PRIMARY KEY, city CHAR(10), state CHAR(2));
+INSERT INTO Addresses (ID, city, state) VALUES (18, 'Cambridge', 'MA');
+"""
+ val addrs = RDB.Relation(
+ "Addresses",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "city" -> RDB.Datatype.STRING,
+ "state" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "state" -> RDB.LexicalValue("MA"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+
+ val expected = RDB.Database(List(addrs))
+ assert(expected === (p.parseAll(p.ddl, s).get))
+ }
+
+
+ test("parse 2 People 1 Addresses") {
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
+ FOREIGN KEY (addr) REFERENCES Addresses(ID));
+INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
+INSERT INTO People (ID, fname, addr) VALUES (8, "Sue", NULL);
+"""
+ val addrs = RDB.Relation(
+ "Addresses",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "city" -> RDB.Datatype.STRING,
+ "state" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "state" -> RDB.LexicalValue("MA"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+
+ val people = RDB.Relation(
+ "People",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "fname" -> RDB.Datatype.STRING,
+ "addr" -> RDB.Datatype.INTEGER),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
+ "fname" -> RDB.LexicalValue("Bob"),
+ "addr" -> RDB.LexicalValue("18")),
+ RDB.Tuple("ID" -> RDB.LexicalValue("8"),
+ "fname" -> RDB.LexicalValue("Sue"),
+ "addr" -> RDB.␀())),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
+
+ val expected = RDB.Database(List(addrs, people))
+ assert(expected === (p.parseAll(p.ddl, s).get))
+ }
+
+
+ test("parse 2 People 1 Addresses 1 Department") {
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING, manager INT,
+ FOREIGN KEY (manager) REFERENCES People(ID),
+ UNIQUE (name, city));
+INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING,
+ addr INT, FOREIGN KEY (addr) REFERENCES Addresses(ID),
+ deptName STRING, deptCity STRING,
+ FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);
+"""
+
+ val addrs = RDB.Relation(
+ "Addresses",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "city" -> RDB.Datatype.STRING,
+ "state" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "state" -> RDB.LexicalValue("MA"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+
+ val people = RDB.Relation(
+ "People",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "fname" -> RDB.Datatype.STRING,
+ "addr" -> RDB.Datatype.INTEGER,
+ "deptName" -> RDB.Datatype.STRING,
+ "deptCity" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
+ "fname" -> RDB.LexicalValue("Bob"),
+ "addr" -> RDB.LexicalValue("18"),
+ "deptName" -> RDB.LexicalValue("accounting"),
+ "deptCity" -> RDB.LexicalValue("Cambridge")),
+ RDB.Tuple("ID" -> RDB.LexicalValue("8"),
+ "fname" -> RDB.LexicalValue("Sue"),
+ "addr" -> RDB.␀(),
+ "deptName" -> RDB.␀(),
+ "deptCity" -> RDB.␀())),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID")),
+ List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
+
+ val department = RDB.Relation(
+ "Department",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "name" -> RDB.Datatype.STRING,
+ "city" -> RDB.Datatype.STRING,
+ "manager" -> RDB.Datatype.INTEGER),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("23"),
+ "name" -> RDB.LexicalValue("accounting"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "manager" -> RDB.LexicalValue("8"))),
+ List(RDB.CandidateKey("ID"),
+ RDB.CandidateKey("name", "city")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("manager") -> RDB.Target("People", RDB.CandidateKey("ID"))))
+
+ val db = RDB.Database(List(addrs, people, department))
+
+ val expected = RDB.Database(List(addrs, people, department))
+ assert(expected === (p.parseAll(p.ddl, s).get))
+ }
+
+
+ test("parse 2 People 1 Addresses 1 Department 2 Projects 1 Task") {
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING, manager INT,
+ FOREIGN KEY (manager) REFERENCES People(ID),
+ UNIQUE (name, city));
+INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
+ FOREIGN KEY (addr) REFERENCES Addresses(ID),
+ deptName STRING, deptCity STRING,
+ FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");
+INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);
+CREATE TABLE Projects (lead INT,
+ FOREIGN KEY (lead) REFERENCES People(ID),
+ name STRING, UNIQUE (lead, name),
+ deptName STRING, deptCity STRING,
+ UNIQUE (name, deptName, deptCity),
+ FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "pencil survey", "accounting", "Cambridge");
+INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "eraser survey", "accounting", "Cambridge");
+CREATE TABLE TaskAssignments (worker INT,
+ FOREIGN KEY (worker) REFERENCES People(ID),
+ project STRING, PRIMARY KEY (worker, project),
+ deptName STRING, deptCity STRING,
+ FOREIGN KEY (worker) REFERENCES People(ID),
+ FOREIGN KEY (project, deptName, deptCity) REFERENCES Projects(name, deptName, deptCity),
+ FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));
+INSERT INTO TaskAssignments (worker, project, deptName, deptCity) VALUES (7, "pencil survey", "accounting", "Cambridge");
+"""
+
+ val addrs = RDB.Relation(
+ "Addresses",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "city" -> RDB.Datatype.STRING,
+ "state" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "state" -> RDB.LexicalValue("MA"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+
+ val people = RDB.Relation(
+ "People",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "fname" -> RDB.Datatype.STRING,
+ "addr" -> RDB.Datatype.INTEGER,
+ "deptName" -> RDB.Datatype.STRING,
+ "deptCity" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
+ "fname" -> RDB.LexicalValue("Bob"),
+ "addr" -> RDB.LexicalValue("18"),
+ "deptName" -> RDB.LexicalValue("accounting"),
+ "deptCity" -> RDB.LexicalValue("Cambridge")),
+ RDB.Tuple("ID" -> RDB.LexicalValue("8"),
+ "fname" -> RDB.LexicalValue("Sue"),
+ "addr" -> RDB.␀(),
+ "deptName" -> RDB.␀(),
+ "deptCity" -> RDB.␀())),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID")),
+ List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
+
+ val department = RDB.Relation(
+ "Department",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "name" -> RDB.Datatype.STRING,
+ "city" -> RDB.Datatype.STRING,
+ "manager" -> RDB.Datatype.INTEGER),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("23"),
+ "name" -> RDB.LexicalValue("accounting"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "manager" -> RDB.LexicalValue("8"))),
+ List(RDB.CandidateKey("ID"),
+ RDB.CandidateKey("name", "city")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("manager") -> RDB.Target("People", RDB.CandidateKey("ID"))))
+
+ val projects = RDB.Relation(
+ "Projects",
+ RDB.Header("lead" -> RDB.Datatype.INTEGER,
+ "name" -> RDB.Datatype.STRING,
+ "deptName" -> RDB.Datatype.STRING,
+ "deptCity" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("lead" -> RDB.LexicalValue("8"),
+ "name" -> RDB.LexicalValue("pencil survey"),
+ "deptName" -> RDB.LexicalValue("accounting"),
+ "deptCity" -> RDB.LexicalValue("Cambridge")),
+ RDB.Tuple("lead" -> RDB.LexicalValue("8"),
+ "name" -> RDB.LexicalValue("eraser survey"),
+ "deptName" -> RDB.LexicalValue("accounting"),
+ "deptCity" -> RDB.LexicalValue("Cambridge"))),
+ List(RDB.CandidateKey("lead", "name"),
+ RDB.CandidateKey("name", "deptName", "deptCity")),
+ None,
+ /* List(List("name"), List("lead", "name"), List("name", "deptName", "deptCity")),
+ List("name"), // !!! */
+ RDB.ForeignKeys(List("lead") -> RDB.Target("People", RDB.CandidateKey("ID")),
+ List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
+
+ val tasks = RDB.Relation(
+ "TaskAssignments",
+ RDB.Header("worker" -> RDB.Datatype.INTEGER,
+ "project" -> RDB.Datatype.STRING,
+ "deptName" -> RDB.Datatype.STRING,
+ "deptCity" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("worker" -> RDB.LexicalValue("7"),
+ "project" -> RDB.LexicalValue("pencil survey"),
+ "deptName" -> RDB.LexicalValue("accounting"),
+ "deptCity" -> RDB.LexicalValue("Cambridge"))),
+ List(RDB.CandidateKey("worker", "project")),
+ Some(RDB.CandidateKey("worker", "project")),
+ RDB.ForeignKeys(List("worker") -> RDB.Target("People", RDB.CandidateKey("ID")),
+ List("project", "deptName", "deptCity") -> RDB.Target("Projects", RDB.CandidateKey("name", "deptName", "deptCity")),
+ List("deptName", "deptCity") -> RDB.Target("Department", RDB.CandidateKey("name", "city"))))
+ val db = RDB.Database(List(addrs, people, department, projects, tasks))
+
+ val expected = RDB.Database(List(addrs, people, department, projects, tasks))
+ assert(expected === (p.parseAll(p.ddl, s).get))
+ }
+
+
+ test("parse 1 People 1 Addresses 1 Offices") {
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
+ FOREIGN KEY (addr) REFERENCES Addresses(ID));
+INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
+CREATE TABLE Offices (ID INT PRIMARY KEY,
+ building INT, ofcNumber STRING,
+ FOREIGN KEY (ID) REFERENCES Addresses(ID));
+INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");
+"""
+
+ val addrs = RDB.Relation(
+ "Addresses",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "city" -> RDB.Datatype.STRING,
+ "state" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "state" -> RDB.LexicalValue("MA"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+
+ val people = RDB.Relation(
+ "People",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "fname" -> RDB.Datatype.STRING,
+ "addr" -> RDB.Datatype.INTEGER),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
+ "fname" -> RDB.LexicalValue("Bob"),
+ "addr" -> RDB.LexicalValue("18"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
+
+ val offices = RDB.Relation(
+ "Offices",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "building" -> RDB.Datatype.INTEGER,
+ "ofcNumber" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "building" -> RDB.LexicalValue("32"),
+ "ofcNumber" -> RDB.LexicalValue("G528"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("ID") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
+
+ val expected = RDB.Database(List(addrs, people, offices))
+ assert(expected === (p.parseAll(p.ddl, s).get))
+ }
+
+
+ test("parse 1 People 1 Addresses 1 Offices 1 ExectutiveOffices") {
+ val p = SqlParser()
+ val s = """
+CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
+INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");
+CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,
+ FOREIGN KEY (addr) REFERENCES Addresses(ID));
+INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);
+CREATE TABLE Offices (ID INT PRIMARY KEY,
+ building INT, ofcNumber STRING,
+ FOREIGN KEY (ID) REFERENCES Addresses(ID));
+INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");
+CREATE TABLE ExecutiveOffices (ID INT PRIMARY KEY,
+ desk STRING,
+ FOREIGN KEY (ID) REFERENCES Offices(ID));
+INSERT INTO ExecutiveOffices (ID, desk) VALUES (18, "oak");
+"""
+
+ val addrs = RDB.Relation(
+ "Addresses",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "city" -> RDB.Datatype.STRING,
+ "state" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "city" -> RDB.LexicalValue("Cambridge"),
+ "state" -> RDB.LexicalValue("MA"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys())
+
+ val people = RDB.Relation(
+ "People",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "fname" -> RDB.Datatype.STRING,
+ "addr" -> RDB.Datatype.INTEGER),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("7"),
+ "fname" -> RDB.LexicalValue("Bob"),
+ "addr" -> RDB.LexicalValue("18"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("addr") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
+
+ val offices = RDB.Relation(
+ "Offices",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "building" -> RDB.Datatype.INTEGER,
+ "ofcNumber" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "building" -> RDB.LexicalValue("32"),
+ "ofcNumber" -> RDB.LexicalValue("G528"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("ID") -> RDB.Target("Addresses", RDB.CandidateKey("ID"))))
+
+ val execoffices = RDB.Relation(
+ "ExecutiveOffices",
+ RDB.Header("ID" -> RDB.Datatype.INTEGER,
+ "desk" -> RDB.Datatype.STRING),
+ List(RDB.Tuple("ID" -> RDB.LexicalValue("18"),
+ "desk" -> RDB.LexicalValue("oak"))),
+ List(RDB.CandidateKey("ID")),
+ Some(RDB.CandidateKey("ID")),
+ RDB.ForeignKeys(List("ID") -> RDB.Target("Offices", RDB.CandidateKey("ID"))))
+ val expected = RDB.Database(List(addrs, people, offices, execoffices))
+ assert(expected === (p.parseAll(p.ddl, s).get))
+ }
+
+
/**
* junk that should be elsewhere
*/
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/sql2sql/.ensime Wed Nov 03 14:04:23 2010 -0400
@@ -0,0 +1,9 @@
+;; This config was generated using ensime-config-gen. Feel free to customize its contents manually.
+
+(
+
+:project-package "org.w3"
+
+:use-sbt t
+
+)