changeset 360:1420a9d575b8

~ externalize all queries in separated files
author Alexandre Bertails <bertails@gmail.com>
date Sun, 13 Mar 2011 18:06:23 -0400
parents 22cc49dbf0e6
children 277fe8b0313d
files directmapping-webapp/src/main/resources/queries/1table0rows directmapping-webapp/src/main/resources/queries/1table1compositeprimarykey3columns1row directmapping-webapp/src/main/resources/queries/1table1primarykey1column1row directmapping-webapp/src/main/resources/queries/1table1row directmapping-webapp/src/main/resources/queries/1table2columns1row directmapping-webapp/src/main/resources/queries/1table3columns1row directmapping-webapp/src/main/resources/queries/2duplicates0nulls directmapping-webapp/src/main/resources/queries/2tables1primarykey1foreingkey directmapping-webapp/src/main/resources/queries/emp_addr directmapping-webapp/src/main/resources/queries/hier_tabl_proto directmapping-webapp/src/main/resources/queries/hier_table directmapping-webapp/src/main/resources/queries/multi_key directmapping-webapp/src/main/resources/queries/ref_no_pk directmapping-webapp/src/main/resources/queries/varchar_varchar_1row directmapping-webapp/src/main/scala/Servlet.scala
diffstat 15 files changed, 173 insertions(+), 153 deletions(-) [+]
line wrap: on
line diff
--- /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
+  }
+
+}
Set up and maintained by W3C Systems Team, please report bugs to sysreq@w3.org.

W3C would like to thank Microsoft who donated the server that allows us to run this service.