--- a/directmapping/src/test/scala/DirectMappingTest.scala Tue Nov 02 17:16:10 2010 -0400
+++ b/directmapping/src/test/scala/DirectMappingTest.scala Tue Nov 02 18:09:44 2010 -0400
@@ -88,89 +88,38 @@
test("2 People 1 Addresses 1 Department 2 Projects 1 Task") {
- val addrs = Relation("Addresses",
- Header("ID" -> Datatype.INTEGER,
- "city" -> Datatype.STRING,
- "state" -> Datatype.STRING),
- List(Tuple("ID" -> LexicalValue("18"),
- "city" -> LexicalValue("Cambridge"),
- "state" -> LexicalValue("MA"))),
- List(CandidateKey("ID")),
- Some(CandidateKey("ID")),
- ForeignKeys())
-
- val people = Relation("People",
- Header("ID" -> Datatype.INTEGER,
- "fname" -> Datatype.STRING,
- "addr" -> Datatype.INTEGER,
- "deptName" -> Datatype.STRING,
- "deptCity" -> Datatype.STRING),
- List(Tuple("ID" -> LexicalValue("7"),
- "fname" -> LexicalValue("Bob"),
- "addr" -> LexicalValue("18"),
- "deptName" -> LexicalValue("accounting"),
- "deptCity" -> LexicalValue("Cambridge")),
- Tuple("ID" -> LexicalValue("8"),
- "fname" -> LexicalValue("Sue"),
- "addr" -> ␀(),
- "deptName" -> ␀(),
- "deptCity" -> ␀())), // no data
- List(CandidateKey("ID")),
- Some(CandidateKey("ID")),
- ForeignKeys(List("addr") -> Target("Addresses", CandidateKey("ID")),
- List("deptName", "deptCity") -> Target("Department", CandidateKey("name", "city"))))
-
- val department = Relation("Department",
- Header("ID" -> Datatype.INTEGER,
- "name" -> Datatype.STRING,
- "city" -> Datatype.STRING,
- "manager" -> Datatype.INTEGER),
- List(Tuple("ID" -> LexicalValue("23"),
- "name" -> LexicalValue("accounting"),
- "city" -> LexicalValue("Cambridge"),
- "manager" -> LexicalValue("8"))),
- List(CandidateKey("ID"),
- CandidateKey("name", "city")),
- Some(CandidateKey("ID")),
- ForeignKeys(List("manager") -> Target("People", CandidateKey("ID"))))
-
- val projects = Relation("Projects",
- Header("lead" -> Datatype.INTEGER,
- "name" -> Datatype.STRING,
- "deptName" -> Datatype.STRING,
- "deptCity" -> Datatype.STRING),
- List(Tuple("lead" -> LexicalValue("8"),
- "name" -> LexicalValue("pencil survey"),
- "deptName" -> LexicalValue("accounting"),
- "deptCity" -> LexicalValue("Cambridge")),
- Tuple("lead" -> LexicalValue("8"),
- "name" -> LexicalValue("eraser survey"),
- "deptName" -> LexicalValue("accounting"),
- "deptCity" -> LexicalValue("Cambridge"))),
- List(CandidateKey("lead", "name"),
- CandidateKey("name", "deptName", "deptCity")),
- None,
- /* List(List("name"), List("lead", "name"), List("name", "deptName", "deptCity")),
- List("name"), // !!! */
- ForeignKeys(List("lead") -> Target("People", CandidateKey("ID")),
- List("deptName", "deptCity") -> Target("Department", CandidateKey("name", "city"))))
-
- val tasks = Relation("TaskAssignments",
- Header("worker" -> Datatype.INTEGER,
- "project" -> Datatype.STRING,
- "deptName" -> Datatype.STRING,
- "deptCity" -> Datatype.STRING),
- List(Tuple("worker" -> LexicalValue("7"),
- "project" -> LexicalValue("pencil survey"),
- "deptName" -> LexicalValue("accounting"),
- "deptCity" -> LexicalValue("Cambridge"))),
- List(CandidateKey("worker", "project")),
- Some(CandidateKey("worker", "project")),
- ForeignKeys(List("worker") -> Target("People", CandidateKey("ID")),
- List("project", "deptName", "deptCity") -> Target("Projects", CandidateKey("name", "deptName", "deptCity")),
- List("deptName", "deptCity") -> Target("Department", CandidateKey("name", "city"))))
-
- val db = Database(List(addrs, people, department, projects, tasks))
+ 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 =
--- a/sql/src/test/scala/SQLTest.scala Tue Nov 02 17:16:10 2010 -0400
+++ b/sql/src/test/scala/SQLTest.scala Tue Nov 02 18:09:44 2010 -0400
@@ -13,6 +13,132 @@
rs.map(r => (r.name -> r)).toMap
// implicit def string2attrName (n:String) = RDB.AttrName(n)
+ 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 ANDexpression") {
// AliasedResource(Relation(Name("Employee")),RelVar(Name("R_emp")))
val a = SqlParser()
@@ -609,7 +735,8 @@
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));
+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);
"""
@@ -650,12 +777,14 @@
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));
+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));
+ 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);
"""