<%@page contentType="text/html; charset=UTF-8"%> <%@page import="java.io.File"%> <%@page import="java.io.FileInputStream"%> <%@page import="java.io.FileWriter"%> <%@page import="java.io.FilenameFilter"%> <%@page import="java.util.Properties"%> <%@page import="java.util.Hashtable"%> <%@page import="java.util.Enumeration"%> <%@page import="java.util.Vector"%> <%@page import="java.net.URLEncoder"%> <%@page import="java.sql.Connection"%> <%@page import="java.sql.PreparedStatement"%> <%@page import="java.sql.ResultSet"%> <%@page import="java.sql.SQLException"%> <%@page import="java.sql.ResultSetMetaData"%> <%@page import="javax.servlet.jsp.JspWriter"%> <%@page import="java.text.MessageFormat"%> <%@page import="org.crosswire.data.CSVFileReader"%> <%@page import="org.crosswire.utils.HTTPUtils"%> <%@page import="java.net.URLDecoder"%> SQL Test Harness

SQL Test Harness

<% String sqlPath = request.getRealPath("sql"); try { javax.naming.Context ctx = new javax.naming.InitialContext(); if (ctx == null) throw new Exception("No Context"); sqlPath = (java.lang.String) ctx.lookup("java:comp/env/testSQLPath"); } catch (Exception e) { } String catTreeChange = request.getParameter("close"); if (catTreeChange != null) { session.setAttribute("catExp", ""); } catTreeChange = request.getParameter("open"); if (catTreeChange != null) { session.setAttribute("catExp", catTreeChange); } File directory = new File(sqlPath); File[] sqlFiles = directory.listFiles( new FilenameFilter() { public boolean accept(File dir, String name) { return name.endsWith(".properties"); } }); int fileCount = (sqlFiles == null) ? 0 : sqlFiles.length; Object[] sortedkeys = null; Hashtable files = new Hashtable(); Hashtable provides = new Hashtable(); %>

Directory Read: <%= directory.getCanonicalPath() %>
Total SQL Set Count: <%= fileCount %>

<%if (fileCount > 0) {%>
<% for (int i = 0; i < fileCount; i++) { try { Properties sql = new Properties(); sql.load(new FileInputStream(sqlFiles[i])); String testCat = sql.getProperty("Category"); String testName = sql.getProperty("Name"); files.put(testCat + testName, sql); } catch (Exception e) {} // don't care if we couldn't open one entry } Properties sql = null; Enumeration keys = files.keys(); String curCat = ""; Vector sorted = new Vector(); while (keys.hasMoreElements()) { sorted.add(keys.nextElement()); } sortedkeys = sorted.toArray(); java.util.Arrays.sort(sortedkeys); boolean expandCat = false; boolean ulOpen = false; String catExp = (String) session.getAttribute("catExp"); for (int i = 0; i < sortedkeys.length; i++) { sql = (Properties) files.get(sortedkeys[i]); String testCat = sql.getProperty("Category"); String testName = sql.getProperty("Name"); String provide = sql.getProperty("Provides"); if (provide != null) provides.put(provide, new Integer(i)); if ((testCat != null) && (!testCat.equalsIgnoreCase(curCat))) { if (ulOpen) out.println(""); expandCat = (testCat.equals(catExp)); out.println("

[ " + ((expandCat) ? "-" : "+") + " ] " + testCat + "

"); curCat = testCat; out.println("
    "); ulOpen = true; } if (!expandCat) continue; %>
  • <%= testName %>
    • <% } if (ulOpen) out.println("
"); } String numStr = request.getParameter("exec"); String stageStr = request.getParameter("stage"); if (numStr != null) { int num = Integer.parseInt(numStr); int stage = (stageStr != null) ? Integer.parseInt(stageStr) : 1; if (num < sqlFiles.length) { Properties sqlTest = (Properties) files.get(sortedkeys[num]); String require = sqlTest.getProperty("Requires"); if ((require != null) && (session.getAttribute(require) == null)) { Integer reqExec = (Integer)provides.get(require); if (reqExec != null) sqlTest = (Properties) files.get(sortedkeys[reqExec.intValue()]); } String testName = sqlTest.getProperty("Name"); String testDesc = sqlTest.getProperty("Description"); %>
Executing <%= testName %>
Description <%= testDesc %>
<% Vector props = new Vector(); String prop = null; int j = 0; do { //out.println("

Trying to get val" + Integer.toString(j)); prop = request.getParameter("val" + Integer.toString(j)); if (prop != null) { props.add(prop); //out.println("

Adding: " + prop + "

"); } j++; } while (prop != null); Object[] params = props.toArray(); String testSQL = ""; for (int i = stage; testSQL != null; i++) { String suffix = ((i < 2) ? "" : Integer.toString(i)); testSQL = sqlTest.getProperty("SQL" + suffix); if (testSQL != null) { testSQL = testSQL.replaceAll("\'", "\'\'"); } String heading = sqlTest.getProperty("Heading" + suffix); String linkColumn = sqlTest.getProperty("LinkColumn" + suffix); String importFile = sqlTest.getProperty("ImportFile" + suffix); String importSQL = sqlTest.getProperty("ImportSQL" + suffix); String importDupCheck = sqlTest.getProperty("ImportDupCheck" + suffix); String exportFile = sqlTest.getProperty("ExportFile" + suffix); String formField = sqlTest.getProperty("Form" + suffix + ".0"); String sessionField = sqlTest.getProperty("SetSession" + suffix + ".0"); // see if we have a form if (heading != null) { %>

<%= heading %>

<% } if (importFile != null) { String fileName = importFile; String iSQL = ""; int separator = fileName.indexOf('|'); if (separator > -1) { fileName = importFile.substring(0, separator); iSQL = importFile.substring(separator + 1); } try { CSVFileReader fin = new CSVFileReader(new File(sqlPath+"/"+fileName), '|', false); iSQL = iSQL.replaceAll("\'", "\'\'"); iSQL = replaceSessionTags(session, iSQL); if (importDupCheck != null) { importDupCheck = importDupCheck.replaceAll("\'", "\'\'"); importDupCheck = replaceSessionTags(session, importDupCheck); } for (boolean more = fin.begin(); more; more = fin.next()) { boolean execute = true; if (importDupCheck != null) { MessageFormat format = new MessageFormat(importDupCheck); String dcSQL = format.format(fin.getFields()); //out.print(dcSQL); execute = (runSQL(dcSQL, linkColumn, num, i + 1, null) == 0); } if (execute) { MessageFormat format = new MessageFormat(iSQL); String rSQL = format.format(fin.getFields()); runSQL(rSQL, linkColumn, num, i + 1, out); } } } catch (Exception e) { e.printStackTrace(); } } if (importSQL != null) { String sourceSQL = importSQL; String iSQL = ""; int separator = sourceSQL.indexOf('|'); if (separator > -1) { sourceSQL = importSQL.substring(0, separator); iSQL = importSQL.substring(separator + 1); } try { importSQL(sourceSQL, iSQL, importDupCheck, out); } catch (Exception e) { e.printStackTrace(); } } if (exportFile != null) { String fileName = exportFile; String iSQL = ""; int separator = fileName.indexOf('|'); if (separator > -1) { fileName = exportFile.substring(0, separator); iSQL = exportFile.substring(separator + 1); } try { FileWriter fout = new FileWriter(new File(sqlPath+"/"+fileName)); iSQL = replaceSessionTags(session, iSQL); exportSQL(iSQL, fout, out); fout.close(); } catch (Exception e) { e.printStackTrace(); } } if (sessionField != null) { j = 0; do { sessionField = replaceSessionTags(session, sessionField); MessageFormat format = new MessageFormat(sessionField); sessionField = format.format(params); String fieldName = sessionField; String fieldValue = ""; int separator = sessionField.indexOf('|'); if (separator > -1) { fieldName = sessionField.substring(0, separator); fieldValue = sessionField.substring(separator + 1); } session.setAttribute(fieldName, fieldValue); sessionField = sqlTest.getProperty("SetSession" + suffix + "." + Integer.toString(++j)); } while (sessionField != null); } if (formField != null) { %>
<% j = 0; do { formField = replaceSessionTags(session, formField); MessageFormat format = new MessageFormat(formField); formField = format.format(params); String fieldHeading = formField; String fieldDefault = ""; String fieldType = "text"; int separator = formField.indexOf('|'); if (separator > -1) { fieldHeading = formField.substring(0, separator); fieldDefault = formField.substring(separator + 1); } separator = fieldDefault.indexOf('|'); if (separator > -1) { fieldType = fieldDefault.substring(separator + 1); fieldDefault = fieldDefault.substring(0, separator); } %> <% formField = sqlTest.getProperty("Form" + suffix + "." + Integer.toString(++j)); } while (formField != null); %>
<%= fieldHeading %>
<% break; } // assert we have an SQL String to execute if (testSQL == null) break; testSQL = replaceSessionTags(session, testSQL); MessageFormat format = new MessageFormat(testSQL); testSQL = format.format(params); runSQL(testSQL, linkColumn, num, i + 1, out); if (linkColumn != null) { break; } String nextStage = sqlTest.getProperty("Next" + suffix); try { i = Integer.parseInt(nextStage) - 1; } catch (Exception e) {} String newZero = sqlTest.getProperty("NewZero" + suffix); try { params[0] = params[Integer.parseInt(newZero)]; } catch (Exception e) {} } } } %>
<%! // Display an SQLException which has occured in this application. private static void showSQLException(JspWriter out, java.sql.SQLException e) { java.sql.SQLException next = e; while (next != null) { try { out.println(next.getMessage()); out.println("Error Code: " + next.getErrorCode()); out.println("SQL State: " + next.getSQLState()); } catch (Exception e1) { e1.printStackTrace(); } next = next.getNextException(); } } private static String replaceSessionTags(javax.servlet.http.HttpSession session, String inString) { int i = 1; for (int offset = inString.indexOf('{', i); offset > -1; offset = inString.indexOf('{', i)) { int end = inString.indexOf('}', offset); if ((end > -1) && (!Character.isDigit(inString.charAt(offset+1)))) { String sessionAtt = inString.substring(offset+1, end); String sessionVal = (String)session.getAttribute(sessionAtt); inString = inString.substring(0, offset) + ((sessionVal == null) ? "":sessionVal) + inString.substring(end+1); } i = offset+1; } return inString; } private synchronized static Connection getDBConnection(JspWriter out) { javax.sql.DataSource ds = null; Connection connRSFind = null; try { javax.naming.Context ctx = new javax.naming.InitialContext(); if (ctx == null) throw new Exception("Boom - No Context"); ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/storeDB"); try { connRSFind = ds.getConnection(); } catch (java.sql.SQLException e) { System.out.println("getting new data source connection failed! Error: " + e + "\n"); } } catch (Exception e) { System.out.println("Could Not get data source, error: " + e + "\n"); } return connRSFind; } private static int runSQL(String testSQL, String linkColumn, int exec, int stage, JspWriter out) { Connection c = null; PreparedStatement stmt = null; ResultSet rs = null; int retVal = 0; String result = "Success"; SQLException resultException = null; try { c = getDBConnection(out); if (c == null) { out.println("
couldn't get DB connection"); return 0; } try { stmt = c.prepareStatement(testSQL); if (testSQL.substring(0, 10).toUpperCase().startsWith("SELECT")) { rs = stmt.executeQuery(); } else { int r = stmt.executeUpdate(); result = (r == 0) ? "Success" : Integer.toString(r); } } catch (SQLException e) { result = "Exception"; resultException = e; } catch (Exception e1) { result = e1.toString(); } if (out != null) { out.println(""); if (rs == null) { out.println(""); if (resultException != null) { out.println(""); } } out.println("
Executing SQL " + testSQL + "
Result " + result + "
Exception "); showSQLException(out, resultException); out.println("

"); } if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); if (out != null) { out.println(""); for (int i = 0; i < colCount; i++) { String colName = meta.getColumnName(i + 1); out.println(""); } out.println(""); } while (rs.next()) { retVal++; if (out != null) { out.println(""); String colVals = ""; for (int i = 1; i < colCount + 1; i++) { Object o = rs.getObject(i); if (o == null) o = ""; colVals += "&val" + Integer.toString(i) + "=" + URLEncoder.encode(o.toString()); } for (int i = 0; i < colCount; i++) { Object o = rs.getObject(i + 1); String colVal = (o != null) ? o.toString() : ""; out.print(""); } if (out != null) out.println(""); } } if (out != null) out.println("
" + colName + "
" + rs.getRow() + ""); if (meta.getColumnName(i + 1).equalsIgnoreCase(linkColumn)) { out.print("" + colVal + ""); } else { out.println(colVal); } out.println("
"); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } if (c != null) { c.close(); } } catch (Exception e) { e.printStackTrace(); } } return retVal; } private static int exportSQL(String testSQL, FileWriter out, JspWriter errOut) { Connection c = null; PreparedStatement stmt = null; ResultSet rs = null; int retVal = 0; String result = "Success"; SQLException resultException = null; try { c = getDBConnection(errOut); if (c == null) { errOut.println("
couldn't get DB connection"); return 0; } try { stmt = c.prepareStatement(testSQL); if (testSQL.substring(0, 10).toUpperCase().startsWith("SELECT")) { rs = stmt.executeQuery(); } else { int r = stmt.executeUpdate(); result = (r == 0) ? "Success" : Integer.toString(r); } } catch (SQLException e) { result = "Exception"; resultException = e; } catch (Exception e1) { result = e1.toString(); } if (errOut != null) { errOut.write(""); if (rs == null) { errOut.println(""); if (resultException != null) { errOut.println(""); } } errOut.println("
Executing SQL " + testSQL + "
Result " + result + "
Exception "); showSQLException(errOut, resultException); errOut.println("

"); } if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); if (out != null) { out.write("# "); for (int i = 0; i < colCount; i++) { String colName = meta.getColumnName(i + 1); out.write(colName); if (i + 1 < colCount) out.write("|"); } out.write("\n"); } while (rs.next()) { retVal++; if (out != null) { String colVals = ""; for (int i = 0; i < colCount; i++) { Object o = rs.getObject(i + 1); String colVal = (o != null) ? o.toString() : ""; colVal = URLEncoder.encode(colVal, "UTF-8"); out.write(colVal); if (i + 1 < colCount) out.write("|"); } out.write("\n"); } } } } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } if (c != null) { c.close(); } } catch (Exception e) { e.printStackTrace(); } } return retVal; } private static int importSQL(String sourceSQL, String iSQL, String importDupCheck, JspWriter errOut) { Connection c = null; PreparedStatement stmt = null; ResultSet rs = null; int retVal = 0; String result = "Success"; SQLException resultException = null; sourceSQL = sourceSQL.replaceAll("\'", "\'\'"); iSQL = iSQL.replaceAll("\'", "\'\'"); try { c = getDBConnection(errOut); if (c == null) { errOut.println("
couldn't get DB connection"); return 0; } try { stmt = c.prepareStatement(sourceSQL); if (sourceSQL.substring(0, 10).toUpperCase().startsWith("SELECT")) { rs = stmt.executeQuery(); } else { int r = stmt.executeUpdate(); result = (r == 0) ? "Success" : Integer.toString(r); } } catch (SQLException e) { result = "Exception"; resultException = e; } catch (Exception e1) { result = e1.toString(); } if (errOut != null) { errOut.write(""); if (rs == null) { errOut.println(""); if (resultException != null) { errOut.println(""); } } errOut.println("
Executing SQL " + sourceSQL + "
Result " + result + "
Exception "); showSQLException(errOut, resultException); errOut.println("

"); } if (rs != null) { ResultSetMetaData meta = rs.getMetaData(); int colCount = meta.getColumnCount(); while (rs.next()) { retVal++; String colVals = ""; Vector fields = new Vector(); for (int i = 0; i < colCount; i++) { Object o = rs.getObject(i + 1); String colVal = (o != null) ? o.toString() : ""; fields.add(colVal); colVal = URLEncoder.encode(colVal, "UTF-8"); } boolean execute = true; if (importDupCheck != null) { MessageFormat format = new MessageFormat(importDupCheck); String dcSQL = format.format(fields); execute = (runSQL(dcSQL, null, 0, 0, null) == 0); } if (execute) { MessageFormat format = new MessageFormat(iSQL); String rSQL = format.format(fields.toArray()); runSQL(rSQL, null, 0, 0, errOut); } } } } catch (Exception e) { e.printStackTrace(); } finally { try { if (stmt != null) { stmt.close(); } if (c != null) { c.close(); } } catch (Exception e) { e.printStackTrace(); } } return retVal; } %>