--- a/directmapping-webapp/src/main/scala/Servlet.scala Wed Feb 02 22:09:06 2011 -0500
+++ b/directmapping-webapp/src/main/scala/Servlet.scala Thu Feb 03 10:10:12 2011 -0500
@@ -13,13 +13,7 @@
import org.w3.rdf.jena._
-class DirectMappingWebapp extends HttpServlet with JenaModel with DirectMappingModule with TurtleModule {
-
- import DirectMapping._
-
- val SQLParser = sql.SqlParser()
-
- val turtleParser = new TurtleParser { }
+object DirectMappingWebapp {
val defaultSQL =
"""CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);
@@ -31,6 +25,160 @@
val encoding = "utf-8"
+ val Style = """
+ #sql { background-color: #e7e7ff; float:left; border: thin solid #888888; padding: 1em; }
+ #result { background-color: #eee; float:left; border: thin solid #888888; padding: 1em; }
+ .clear { clear:both; }
+ input { margin-bottom: -1em; }
+"""
+
+ val scripts = Map(
+ "emp_addr" ->
+"""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);""",
+
+ "hier_tabl_proto" ->
+"""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");""",
+
+ "hier_table" ->
+"""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");""",
+
+ "multi_key" ->
+"""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);""",
+
+ "ref_no_pk" ->
+"""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");""",
+
+ "_1table0rows" ->
+"""CREATE TABLE Student (
+ Name VARCHAR(50)
+);""",
+
+ "_1table1row" ->
+"""CREATE TABLE Student (
+ Name VARCHAR(50)
+);
+INSERT INTO Student (Name) VALUES ("Venus");""",
+
+ "_1table2columns1row" ->
+"""CREATE TABLE Student (
+ ID INT,
+ Name VARCHAR(50)
+);
+INSERT INTO Student (ID, Name) VALUES(10,"Venus");""",
+
+ "_1table3columns1row" ->
+"""CREATE TABLE Student (
+ ID INT,
+ FirstName VARCHAR(50),
+ LastName VARCHAR(50)
+);
+INSERT INTO Student (ID, FirstName, LastName) VALUES (10,"Venus", "Williams");""",
+
+ "_2duplicates0nulls" ->
+"""CREATE TABLE IOUs (
+ fname CHAR(20),
+ lname CHAR(20),
+ amount FLOAT);
+INSERT INTO IOUs (fname, lname, amount) VALUES ("Bob", "Smith", 30);
+INSERT INTO IOUs (fname, lname, amount) VALUES ("Sue", "Jones", 20);
+INSERT INTO IOUs (fname, lname, amount) VALUES ("Bob", "Smith", 30);""",
+
+ "varchar_varchar_1row" ->
+"""CREATE TABLE Student_Sport(
+ Student VARCHAR(50),
+ Sport VARCHAR(50)
+);
+INSERT INTO Student_Sport (Student,Sport) VALUES ("Venus", "Tennis");""")
+
+ def format(varr:String, sql:String):String = {
+ val formattedSQL = sql.replaceAll("\n+$", "").replaceAll("\n", "\\\\n\\\\\n")
+ "var " + varr + " = '" + formattedSQL + "';"
+ }
+
+ val Script:String = scripts map { case (varr, sql) => format(varr, sql) } mkString "\n\n"
+
+ def renderVar(varr:String) =
+ <p><input value={ varr } type="button" onclick={ "document.getElementById('sql').value = " + varr + ";" } /></p>
+
+}
+
+import DirectMappingWebapp._
+
+class DirectMappingWebapp extends HttpServlet with JenaModel with DirectMappingModule with TurtleModule {
+
+ import DirectMapping._
+
+ val SQLParser = sql.SqlParser()
+
+ val turtleParser = new TurtleParser { }
+
+ def jenaSerializer(g:Graph):String = {
+ val m = com.hp.hpl.jena.rdf.model.ModelFactory.createModelForGraph(g.jenaGraph)
+ val s = new java.io.StringWriter
+ m.write(s, "N-TRIPLE")
+ s.toString
+ }
+
override def doPost(request:HttpServletRequest, response:HttpServletResponse) = {
val hierarchy = request.getParameter("hierarchy") == "on"
request.getParameter("sql") match {
@@ -39,13 +187,6 @@
}
}
- def jenaSerializer(g:Graph):String = {
- val m = com.hp.hpl.jena.rdf.model.ModelFactory.createModelForGraph(g.jenaGraph)
- val s = new java.io.StringWriter
- m.write(s, "N-TRIPLE")
- s.toString
- }
-
override def doGet(request:HttpServletRequest, response:HttpServletResponse) =
processIndex(request, response)
@@ -54,122 +195,6 @@
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"),
Nil)
- val Style = """
- #sql { background-color: #e7e7ff; float:left; border: thin solid #888888; padding: 1em; }
- #result { background-color: #eee; float:left; border: thin solid #888888; padding: 1em; }
- .clear { clear:both; }
- input { margin-bottom: -1em; }
-"""
-
- val Script = """
-var emp_addr = 'CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);\n\
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");\n\
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT, FOREIGN KEY (addr) REFERENCES Addresses(ID));\n\
-INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);\n\
-INSERT INTO People (ID, fname, addr) VALUES (8, "Sue", NULL);';
-
-var hier_tabl_proto = 'CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);\n\
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");\n\
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,\n\
- FOREIGN KEY (addr) REFERENCES Addresses(ID));\n\
-INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);\n\
-CREATE TABLE Offices (ID INT PRIMARY KEY,\n\
- building INT, ofcNumber STRING,\n\
- FOREIGN KEY (ID) REFERENCES Addresses(ID));\n\
-INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");';
-
-var hier_table = 'CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);\n\
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");\n\
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,\n\
- FOREIGN KEY (addr) REFERENCES Addresses(ID));\n\
-INSERT INTO People (ID, fname, addr) VALUES (7, "Bob", 18);\n\
-CREATE TABLE Offices (ID INT PRIMARY KEY,\n\
- building INT, ofcNumber STRING,\n\
- FOREIGN KEY (ID) REFERENCES Addresses(ID));\n\
-INSERT INTO Offices (ID, building, ofcNumber) VALUES (18, 32, "G528");\n\
-CREATE TABLE ExecutiveOffices (ID INT PRIMARY KEY,\n\
- desk STRING,\n\
- FOREIGN KEY (ID) REFERENCES Offices(ID));\n\
-INSERT INTO ExecutiveOffices (ID, desk) VALUES (18, "oak");';
-
-var multi_key = 'CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);\n\
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");\n\
-CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING,\n\
- manager INT, FOREIGN KEY (manager) REFERENCES People(ID), UNIQUE (name, city));\n\
-INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);\n\
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING,\n\
- addr INT, FOREIGN KEY (addr) REFERENCES Addresses(ID),\n\
- deptName STRING, deptCity STRING, FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));\n\
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");\n\
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);';
-
-var ref_no_pk = 'CREATE TABLE Addresses (ID INT PRIMARY KEY, city STRING, state STRING);\n\
-INSERT INTO Addresses (ID, city, state) VALUES (18, "Cambridge", "MA");\n\
-CREATE TABLE Department (ID INT PRIMARY KEY, name STRING, city STRING, manager INT,\n\
- FOREIGN KEY (manager) REFERENCES People(ID),\n\
- UNIQUE (name, city));\n\
-INSERT INTO Department (ID, name, city, manager) VALUES (23, "accounting", "Cambridge", 8);\n\
-CREATE TABLE People (ID INT PRIMARY KEY, fname STRING, addr INT,\n\
- FOREIGN KEY (addr) REFERENCES Addresses(ID),\n\
- deptName STRING, deptCity STRING,\n\
- FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));\n\
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (7, "Bob", 18, "accounting", "Cambridge");\n\
-INSERT INTO People (ID, fname, addr, deptName, deptCity) VALUES (8, "Sue", NULL, NULL, NULL);\n\
-CREATE TABLE Projects (lead INT,\n\
- FOREIGN KEY (lead) REFERENCES People(ID),\n\
- name STRING, UNIQUE (lead, name), \n\
- deptName STRING, deptCity STRING,\n\
- UNIQUE (name, deptName, deptCity),\n\
- FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));\n\
-INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "pencil survey", "accounting", "Cambridge");\n\
-INSERT INTO Projects (lead, name, deptName, deptCity) VALUES (8, "eraser survey", "accounting", "Cambridge");\n\
-CREATE TABLE TaskAssignments (worker INT,\n\
- FOREIGN KEY (worker) REFERENCES People(ID),\n\
- project STRING, PRIMARY KEY (worker, project), \n\
- deptName STRING, deptCity STRING,\n\
- FOREIGN KEY (worker) REFERENCES People(ID),\n\
- FOREIGN KEY (project, deptName, deptCity) REFERENCES Projects(name, deptName, deptCity),\n\
- FOREIGN KEY (deptName, deptCity) REFERENCES Department(name, city));\n\
-INSERT INTO TaskAssignments (worker, project, deptName, deptCity) VALUES (7, "pencil survey", "accounting", "Cambridge");';
-
-var _1table0rows = 'CREATE TABLE Student (\n\
- Name VARCHAR(50)\n\
-);';
-
-var _1table1row = 'CREATE TABLE Student (\n\
- Name VARCHAR(50)\n\
-);\n\
-INSERT INTO Student (Name) VALUES ("Venus");'
-
-var _1table2columns1row = 'CREATE TABLE Student (\n\
- ID INT,\n\
- Name VARCHAR(50)\n\
-);\n\
-INSERT INTO Student (ID, Name) VALUES(10,"Venus");';
-
-var _1table3columns1row = 'CREATE TABLE Student (\n\
- ID INT,\n\
- FirstName VARCHAR(50),\n\
- LastName VARCHAR(50)\n\
-);\n\
-INSERT INTO Student (ID, FirstName, LastName) VALUES (10,"Venus", "Williams");';
-
-var _2duplicates0nulls = 'CREATE TABLE IOUs (\n\
- fname CHAR(20),\n\
- lname CHAR(20),\n\
- amount FLOAT);\n\
-INSERT INTO IOUs (fname, lname, amount) VALUES ("Bob", "Smith", 30);\n\
-INSERT INTO IOUs (fname, lname, amount) VALUES ("Sue", "Jones", 20);\n\
-INSERT INTO IOUs (fname, lname, amount) VALUES ("Bob", "Smith", 30);';
-
-var varchar_varchar_1row = 'CREATE TABLE Student_Sport(\n\
- Student VARCHAR(50),\n\
- Sport VARCHAR(50)\n\
-);\n\
-INSERT INTO Student_Sport (Student,Sport) VALUES ("Venus", "Tennis");';
-
-"""
-
def render(sql:String, result:Option[String]) =
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
@@ -187,19 +212,10 @@
<p><textarea rows="15" cols="80" name="sql" id="sql">{ sql }</textarea></p>
<div style="float:right;">
<div style="float:left;">
- <p><input value="emp_addr" type="button" onclick="document.getElementById('sql').value = emp_addr;" /></p>
- <p><input value="hier_tabl_proto" type="button" onclick="document.getElementById('sql').value = hier_tabl_proto;" /></p>
- <p><input value="hier_table" type="button" onclick="document.getElementById('sql').value = hier_table;" /></p>
- <p><input value="multi_key" type="button" onclick="document.getElementById('sql').value = multi_key;" /></p>
- <p><input value="ref_no_pk" type="button" onclick="document.getElementById('sql').value = ref_no_pk;" /></p>
+ { List("emp_addr", "hier_tabl_proto", "hier_table", "multi_key", "ref_no_pk") map renderVar }
</div>
<div style="float:left;">
- <p><input value="1table0rows" type="button" onclick="document.getElementById('sql').value = _1table0rows;" /></p>
- <p><input value="1table1row" type="button" onclick="document.getElementById('sql').value = _1table1row;" /></p>
- <p><input value="1table2columns1row" type="button" onclick="document.getElementById('sql').value = _1table2columns1row;" /></p>
- <p><input value="1table3columns1row" type="button" onclick="document.getElementById('sql').value = _1table3columns1row;" /></p>
- <p><input value="2duplicates0nulls" type="button" onclick="document.getElementById('sql').value = _2duplicates0nulls;" /></p>
- <p><input value="varchar_varchar_1row" type="button" onclick="document.getElementById('sql').value = varchar_varchar_1row;" /></p>
+ { List("1table0rows", "1table1row", "1table2columns1row", "1table3columns1row", "2duplicates0nulls", "varchar_varchar_1row") map renderVar }
</div>
</div>
<p><input name="hierarchy" checked="checked" type="checkbox" />Detects hierarchy relation</p>