--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/1table0rows Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,3 @@
+CREATE TABLE Student (
+ Name VARCHAR(50)
+);
\ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/1table1compositeprimarykey3columns1row Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,7 @@
+CREATE TABLE Student (
+ ID integer,
+ Name varchar(50),
+ Sport varchar (50),
+ PRIMARY KEY (ID,Name)
+);
+INSERT INTO Student (ID, Name,Sport) VALUES(10,"Venus Williams","Tennis");
\ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/1table1primarykey1column1row Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,4 @@
+CREATE TABLE Student (
+Name varchar(50) PRIMARY KEY
+);
+INSERT INTO Student (Name) VALUES ("Venus");
\ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/1table1row Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,4 @@
+CREATE TABLE Student (
+ Name VARCHAR(50)
+);
+INSERT INTO Student (Name) VALUES ("Venus");
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/1table2columns1row Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,5 @@
+CREATE TABLE Student (
+ ID INT,
+ Name VARCHAR(50)
+);
+INSERT INTO Student (ID, Name) VALUES(10,"Venus");
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/1table3columns1row Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,6 @@
+CREATE TABLE Student (
+ ID INT,
+ FirstName VARCHAR(50),
+ LastName VARCHAR(50)
+);
+INSERT INTO Student (ID, FirstName, LastName) VALUES (10,"Venus", "Williams");
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/2duplicates0nulls Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,7 @@
+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);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/2tables1primarykey1foreingkey Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,17 @@
+CREATE TABLE Student (
+ID integer,
+Name varchar(50),
+Sport integer,
+PRIMARY KEY (ID),
+FOREIGN KEY(Sport) REFERENCES Sport(ID)
+);
+
+CREATE TABLE Sport (
+ID integer,
+Name varchar (50),
+PRIMARY KEY (ID)
+);
+
+INSERT INTO Student (ID, Name, Sport) VALUES (10,"Venus Williams", 100);
+INSERT INTO Student (ID, Name, Sport) VALUES (20,"Demi Moore", NULL);
+INSERT INTO Sport (ID, Name) VALUES (100,"Tennis");
\ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/emp_addr Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,5 @@
+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);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/hier_tabl_proto Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,9 @@
+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");
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/hier_table Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,13 @@
+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");
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/multi_key Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,10 @@
+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);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/ref_no_pk Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,28 @@
+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");
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/directmapping-webapp/src/main/resources/queries/varchar_varchar_1row Sun Mar 13 18:06:23 2011 -0400
@@ -0,0 +1,5 @@
+CREATE TABLE Student_Sport(
+ Student VARCHAR(50),
+ Sport VARCHAR(50)
+);
+INSERT INTO Student_Sport (Student,Sport) VALUES ("Venus", "Tennis");
--- a/directmapping-webapp/src/main/scala/Servlet.scala Sun Mar 13 16:47:10 2011 -0400
+++ b/directmapping-webapp/src/main/scala/Servlet.scala Sun Mar 13 18:06:23 2011 -0400
@@ -34,158 +34,6 @@
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");""",
-
- "1table1compositeprimarykey3columns1row" ->
-"""CREATE TABLE Student (
- ID integer,
- Name varchar(50),
- Sport varchar (50),
- PRIMARY KEY (ID,Name)
-);
-INSERT INTO Student (ID, Name,Sport) VALUES(10,"Venus Williams","Tennis");""",
-
- "1table1primarykey1column1row" ->
-"""CREATE TABLE Student (
-Name varchar(50) PRIMARY KEY
-);
-INSERT INTO Student (Name) VALUES ("Venus");""",
-
- "2tables1primarykey1foreingkey" ->
-"""CREATE TABLE Student (
-ID integer,
-Name varchar(50),
-Sport integer,
-PRIMARY KEY (ID),
-FOREIGN KEY(Sport) REFERENCES Sport(ID)
-);
-
-CREATE TABLE Sport (
-ID integer,
-Name varchar (50),
-PRIMARY KEY (ID)
-);
-
-INSERT INTO Student (ID, Name, Sport) VALUES (10,"Venus Williams", 100);
-INSERT INTO Student (ID, Name, Sport) VALUES (20,"Demi Moore", NULL);
-INSERT INTO Sport (ID, Name) VALUES (100,"Tennis");""")
-
def format(varr:String, sql:String):String = {
val formattedSQL = sql.replaceAll("\n+$", "").replaceAll("\n", "\\\\n\\\\\n")
"var " + varr + " = '" + formattedSQL + "';"
@@ -198,7 +46,7 @@
varr
}
- val Script:String = scripts map { case (varr, sql) => format(validJSvar(varr), sql) } mkString "\n\n"
+ val Script:String = QueryManager.getQueries map { case (varr, sql) => format(validJSvar(varr), sql) } mkString "\n\n"
def renderVar(varr:String) =
<p><input value={ varr } type="button" onclick={ "document.getElementById('sql').value = " + validJSvar(varr) + ";" } /></p>
@@ -319,3 +167,52 @@
}
+object QueryManager {
+
+ import java.net._
+ import java.io._
+ import java.util.jar._
+ import scala.collection.JavaConversions._
+ import scala.io.Source
+
+ /** http://www.uofr.net/~greg/java/get-resource-listing.html */
+ def getResourceListing(path:String):List[String] = {
+ val clazz:Class[_] = this.getClass
+ var dirURL:URL = clazz.getClassLoader().getResource(path)
+ if (dirURL != null && dirURL.getProtocol == "file") {
+ /* A file path: easy enough */
+ new File(dirURL.toURI).list.toList
+ } else {
+ if (dirURL == null) {
+ val me = clazz.getName().replace(".", "/")+".class"
+ dirURL = clazz.getClassLoader().getResource(me)
+ }
+ if (dirURL.getProtocol == "jar") {
+ val jarPath = dirURL.getPath.substring(5, dirURL.getPath().indexOf("!"))
+ val jar:JarFile = new JarFile(URLDecoder.decode(jarPath, "UTF-8"))
+ val entries = jar.entries filter { _.getName startsWith path } map { e => {
+ var entry = e.getName substring path.length
+ val checkSubdir = entry indexOf "/"
+ if (checkSubdir >= 0) entry = entry.substring(0, checkSubdir)
+ entry
+ } }
+
+ entries
+ }
+ error("Cannot list files for URL "+dirURL);
+ }
+ }
+
+ def getQueries:Map[String, String] = {
+ val classloader = this.getClass.getClassLoader
+ val entries = getResourceListing("queries/")
+ val queries = entries map { entry => {
+ val url = classloader.getResource("queries/" + entry)
+ val query = Source.fromURL(url, "UTF-8").getLines.mkString("\n")
+ (entry, query)
+ } } toMap
+
+ queries
+ }
+
+}