KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > h2 > server > web > AppThread


1 /*
2  * Copyright 2004-2006 H2 Group. Licensed under the H2 License, Version 1.0 (http://h2database.com/html/license.html).
3  * Initial Developer: H2 Group
4  */

5 package org.h2.server.web;
6
7 import java.io.PrintWriter JavaDoc;
8 import java.io.StringReader JavaDoc;
9 import java.io.StringWriter JavaDoc;
10 import java.net.Socket JavaDoc;
11 import java.sql.Connection JavaDoc;
12 import java.sql.DatabaseMetaData JavaDoc;
13 import java.sql.PreparedStatement JavaDoc;
14 import java.sql.ResultSet JavaDoc;
15 import java.sql.ResultSetMetaData JavaDoc;
16 import java.sql.SQLException JavaDoc;
17 import java.sql.Statement JavaDoc;
18 import java.sql.Types JavaDoc;
19 import java.util.ArrayList JavaDoc;
20 import java.util.Collections JavaDoc;
21 import java.util.HashMap JavaDoc;
22 import java.util.Iterator JavaDoc;
23 import java.util.Locale JavaDoc;
24 import java.util.Map JavaDoc;
25 import java.util.Random JavaDoc;
26 import java.util.Map.Entry;
27
28 import org.h2.bnf.Bnf;
29 import org.h2.tools.SimpleResultSet;
30 import org.h2.util.JdbcUtils;
31 import org.h2.util.MathUtils;
32 import org.h2.util.MemoryUtils;
33 import org.h2.util.NetUtils;
34 import org.h2.util.ObjectArray;
35 import org.h2.util.ScriptReader;
36 import org.h2.util.StringUtils;
37
38 /**
39  * @author Thomas
40  */

41
42 public class AppThread extends WebServerThread {
43
44     // TODO web: support online data editing like http://numsum.com/
45

46     AppThread(Socket JavaDoc socket, WebServer server) {
47         super(socket, server);
48         setName("H2 Console thread");
49     }
50
51     AppSession getAppSession() {
52         return (AppSession)session;
53     }
54
55     String JavaDoc process(String JavaDoc file) {
56         server.trace("process " + file);
57         while(file.endsWith(".do")) {
58             if(file.equals("login.do")) {
59                 file = login();
60             } else if(file.equals("index.do")) {
61                 file = index();
62             } else if(file.equals("logout.do")) {
63                 file = logout();
64             } else if(file.equals("settingRemove.do")) {
65                 file = settingRemove();
66             } else if(file.equals("settingSave.do")) {
67                 file = settingSave();
68             } else if(file.equals("test.do")) {
69                 file = test();
70             } else if(file.equals("query.do")) {
71                 file = query();
72             } else if(file.equals("tables.do")) {
73                 file = tables();
74             } else if(file.equals("editresult.do")) {
75                 file = editResult();
76             } else if(file.equals("gethistory.do")) {
77                 file = getHistory();
78             } else if(file.equals("admin.do")) {
79                 file = admin();
80             } else if(file.equals("adminSave.do")) {
81                 file = adminSave();
82             } else if(file.equals("adminShutdown.do")) {
83                 file = adminShutdown();
84             } else if(file.equals("autoCompleteList.do")) {
85                 file = autoCompleteList();
86             } else {
87                 file = "error.jsp";
88             }
89         }
90         server.trace("return " + file);
91         return file;
92     }
93
94     private String JavaDoc autoCompleteList() {
95         String JavaDoc query = (String JavaDoc) attributes.get("query");
96         boolean lowercase = false;
97         if(query.trim().length()>0 && Character.isLowerCase(query.trim().charAt(0))) {
98             lowercase = true;
99         }
100         try {
101             String JavaDoc sql = query;
102             if(sql.endsWith(";")) {
103                 sql += " ";
104             }
105             ScriptReader reader = new ScriptReader(new StringReader JavaDoc(sql));
106             reader.setSkipRemarks(true);
107             String JavaDoc lastSql = "";
108             while(true) {
109                 String JavaDoc n = reader.readStatement();
110                 if(n == null) {
111                     break;
112                 }
113                 lastSql = n;
114             }
115             String JavaDoc result = "";
116             if(reader.isInsideRemark()) {
117                 if(reader.isBlockRemark()) {
118                     result= "1#(End Remark)# */\n" + result;
119                 } else {
120                     result= "1#(Newline)#\n" + result;
121                 }
122             } else {
123                 sql = lastSql == null ? "" : lastSql;
124                 while(sql.length() > 0 && sql.charAt(0) <= ' ') {
125                     sql = sql.substring(1);
126                 }
127                 if(sql.trim().length()>0 && Character.isLowerCase(sql.trim().charAt(0))) {
128                     lowercase = true;
129                 }
130                 Bnf bnf = getAppSession().getBnf();
131                 if(bnf == null) {
132                     return "autoCompleteList.jsp";
133                 }
134                 HashMap JavaDoc map = bnf.getNextTokenList(sql);
135                 String JavaDoc space = "";
136                 if(sql.length()>0) {
137                     char last = sql.charAt(sql.length()-1);
138                     if(!Character.isWhitespace(last) && (last != '.' && last>=' ' && last != '\'' && last != '"')) {
139                         space = " ";
140                     }
141                 }
142                 ArrayList JavaDoc list = new ArrayList JavaDoc(map.size());
143                 Iterator JavaDoc it = map.entrySet().iterator();
144                 while(it.hasNext()) {
145                     Map.Entry JavaDoc entry = (Entry) it.next();
146                     String JavaDoc key = (String JavaDoc) entry.getKey();
147                     String JavaDoc type = "" + key.charAt(0);
148                     String JavaDoc value = (String JavaDoc) entry.getValue();
149                     key = key.substring(2);
150                     if(Character.isLetter(key.charAt(0)) && lowercase) {
151                         key = StringUtils.toLowerEnglish(key);
152                         value = StringUtils.toLowerEnglish(value);
153                     }
154                     if(key.equals(value) && !value.equals(".")) {
155                         value = space + value;
156                     }
157                     key = StringUtils.urlEncode(key);
158                     key = StringUtils.replaceAll(key, "+", " ");
159                     value = StringUtils.urlEncode(value);
160                     value = StringUtils.replaceAll(value, "+", " ");
161                     list.add(type+"#" + key + "#" + value);
162                 }
163                 Collections.sort(list);
164                 StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
165                 if(query.endsWith("\n") || query.trim().endsWith(";")) {
166                     list.add(0, "1#(Newline)#\n");
167                 }
168                 for(int i=0; i<list.size(); i++) {
169                     if(i>0) {
170                         buff.append('|');
171                     }
172                     buff.append((String JavaDoc) list.get(i));
173                 }
174                 result = buff.toString();
175             }
176             session.put("autoCompleteList", result);
177         } catch(Throwable JavaDoc e) {
178             e.printStackTrace();
179         }
180         return "autoCompleteList.jsp";
181     }
182
183     private String JavaDoc admin() {
184         AppServer app = server.getAppServer();
185         session.put("port", ""+app.getPort());
186         session.put("allowOthers", ""+app.getAllowOthers());
187         session.put("ssl", String.valueOf(app.getSSL()));
188         session.put("sessions", server.getSessions());
189         return "admin.jsp";
190     }
191
192     private String JavaDoc adminSave() {
193         AppServer app = server.getAppServer();
194         try {
195             app.setPort(MathUtils.decodeInt((String JavaDoc)attributes.get("port")));
196             app.setAllowOthers(Boolean.valueOf((String JavaDoc)attributes.get("allowOthers")).booleanValue());
197             app.setSSL(Boolean.valueOf((String JavaDoc)attributes.get("ssl")).booleanValue());
198             app.saveSettings();
199         } catch(Exception JavaDoc e) {
200             server.trace(e.toString());
201         }
202         return admin();
203     }
204
205     private String JavaDoc adminShutdown() {
206         System.exit(0);
207         return "admin.jsp";
208     }
209
210     private String JavaDoc index() {
211         String JavaDoc[][] languageArray = server.getLanguageArray();
212         String JavaDoc language = (String JavaDoc) attributes.get("language");
213         Locale JavaDoc locale = session.locale;
214         if(language != null) {
215             if(locale == null || !StringUtils.toLowerEnglish(locale.getLanguage()).equals(language)) {
216                 locale = new Locale JavaDoc(language, "");
217                 server.readTranslations(session, locale.getLanguage());
218                 session.put("language", language);
219                 session.locale = locale;
220             }
221         } else {
222             language = (String JavaDoc) session.get("language");
223         }
224         session.put("languageCombo", getCombobox(languageArray, language));
225         String JavaDoc[] settingNames = server.getAppServer().getSettingNames();
226         String JavaDoc setting = attributes.getProperty("setting");
227         if(setting == null && settingNames.length>0) {
228             setting = settingNames[0];
229         }
230         String JavaDoc combobox = getCombobox(settingNames, setting);
231         session.put("settingsList", combobox);
232         ConnectionInfo info = server.getAppServer().getSetting(setting);
233         if(info == null) {
234             info = new ConnectionInfo();
235         }
236         session.put("setting", PageParser.escapeHtml(setting));
237         session.put("name", PageParser.escapeHtml(setting));
238         session.put("driver", PageParser.escapeHtml(info.driver));
239         session.put("url", PageParser.escapeHtml(info.url));
240         session.put("user", PageParser.escapeHtml(info.user));
241         return "index.jsp";
242     }
243
244     private String JavaDoc getHistory() {
245         int id = Integer.parseInt(attributes.getProperty("id"));
246         String JavaDoc sql = getAppSession().getCommand(id);
247         session.put("query", PageParser.escapeHtmlNoBreak(sql));
248         return "query.jsp";
249     }
250
251     private int addColumns(DbTableOrView table, StringBuffer JavaDoc buff, int treeIndex, boolean showColumnTypes, StringBuffer JavaDoc columnsBuffer) throws SQLException JavaDoc {
252         DbColumn[] columns = table.columns;
253         for(int i=0; columns != null && i<columns.length; i++) {
254             DbColumn column = columns[i];
255             if(columnsBuffer.length()>0) {
256                 columnsBuffer.append(' ');
257             }
258             columnsBuffer.append(column.name);
259             String JavaDoc col = StringUtils.urlEncode(PageParser.escapeJavaScript(column.name));
260             buff.append("setNode("+treeIndex+", 1, 1, 'column', '" + PageParser.escapeJavaScript(column.name)+ "', 'javascript:ins(\\'"+col+"\\')');\n");
261             treeIndex++;
262             if(showColumnTypes) {
263                 buff.append("setNode("+treeIndex+", 2, 2, 'type', '" + PageParser.escapeJavaScript(column.dataType)+ "', null);\n");
264                 treeIndex++;
265             }
266         }
267         return treeIndex;
268     }
269
270     private static class IndexInfo {
271         String JavaDoc name;
272         String JavaDoc type;
273         String JavaDoc columns;
274     }
275
276     private int addIndexes(DatabaseMetaData JavaDoc meta, String JavaDoc table, String JavaDoc schema, StringBuffer JavaDoc buff, int treeIndex) throws SQLException JavaDoc {
277         // index reading is very slow for oracle (2 seconds per index), so don't do it
278
ResultSet JavaDoc rs = meta.getIndexInfo(null, schema, table, false, false);
279         HashMap JavaDoc indexMap = new HashMap JavaDoc();
280         while (rs.next()) {
281             String JavaDoc name = rs.getString("INDEX_NAME");
282             IndexInfo info = (IndexInfo) indexMap.get(name);
283             if (info == null) {
284                 int t = rs.getInt("TYPE");
285                 String JavaDoc type;
286                 if (t == DatabaseMetaData.tableIndexClustered) {
287                     type = "";
288                 } else if (t == DatabaseMetaData.tableIndexHashed) {
289                     type = " (${text.tree.hashed})";
290                 } else if (t == DatabaseMetaData.tableIndexOther) {
291                     type = "";
292                 } else {
293                     type = null;
294                 }
295                 if(name != null && type != null) {
296                     info = new IndexInfo();
297                     info.name = name;
298                     type = (rs.getBoolean("NON_UNIQUE") ? "${text.tree.nonUnique}" : "${text.tree.unique}") + type;
299                     info.type = type;
300                     info.columns = rs.getString("COLUMN_NAME");
301                     indexMap.put(name, info);
302                 }
303             } else {
304                 info.columns += ", " + rs.getString("COLUMN_NAME");
305             }
306         }
307         if(indexMap.size() > 0) {
308             buff.append("setNode("+treeIndex+", 1, 1, 'index_az', '${text.tree.indexes}', null);\n");
309             treeIndex++;
310             for (Iterator JavaDoc it = indexMap.values().iterator(); it.hasNext();) {
311                 IndexInfo info = (IndexInfo) it.next();
312                 buff.append("setNode("+treeIndex+", 2, 1, 'index', '" + PageParser.escapeJavaScript(info.name)+ "', null);\n");
313                 treeIndex++;
314                 buff.append("setNode("+treeIndex+", 3, 2, 'type', '" + info.type+ "', null);\n");
315                 treeIndex++;
316                 buff.append("setNode("+treeIndex+", 3, 2, 'type', '" + PageParser.escapeJavaScript(info.columns)+ "', null);\n");
317                 treeIndex++;
318             }
319         }
320         return treeIndex;
321     }
322
323     private int addTablesAndViews(DbSchema schema, boolean mainSchema, StringBuffer JavaDoc buff, int treeIndex) throws SQLException JavaDoc {
324         if(schema == null) {
325             return treeIndex;
326         }
327         AppSession app = getAppSession();
328         Connection JavaDoc conn = getAppSession().getConnection();
329         DatabaseMetaData JavaDoc meta = app.getMetaData();
330         int level = mainSchema ? 0 : 1;
331         String JavaDoc ident = ", "+level+", "+(level+1)+", ";
332         String JavaDoc identNode = ", "+(level+1)+", "+(level+1)+", ";
333         DbTableOrView[] tables = schema.tables;
334         boolean isOracle = schema.contents.isOracle;
335         boolean showColumnTypes = tables.length < 100;
336         for(int i=0; i<tables.length; i++) {
337             DbTableOrView table = tables[i];
338             if(table.isView) {
339                 continue;
340             }
341             int tableId = treeIndex;
342             String JavaDoc tab = table.quotedName;
343             if(!mainSchema) {
344                 tab =schema.quotedName + "." + tab;
345             }
346             tab = StringUtils.urlEncode(PageParser.escapeJavaScript(tab));
347             buff.append("setNode("+treeIndex+ident+" 'table', '" + PageParser.escapeJavaScript(table.name)+ "', 'javascript:ins(\\'"+tab+"\\',true)');\n");
348             treeIndex++;
349             if(mainSchema) {
350                 StringBuffer JavaDoc columnsBuffer = new StringBuffer JavaDoc();
351                 treeIndex = addColumns(table, buff, treeIndex, showColumnTypes, columnsBuffer);
352                 if(!isOracle) {
353                     treeIndex = addIndexes(meta, table.name, schema.name, buff, treeIndex);
354                 }
355                 buff.append("addTable('"+PageParser.escapeJavaScript(table.name)+"', '"+PageParser.escapeJavaScript(columnsBuffer.toString())+"', "+tableId+");\n");
356             }
357         }
358         tables = schema.tables;
359         for(int i=0; i<tables.length; i++) {
360             DbTableOrView view = tables[i];
361             if(!view.isView) {
362                 continue;
363             }
364             int tableId = treeIndex;
365             String JavaDoc tab = view.quotedName;
366             if(!mainSchema) {
367                 tab = view.schema.quotedName + "." + tab;
368             }
369             tab = StringUtils.urlEncode(PageParser.escapeJavaScript(tab));
370             buff.append("setNode("+treeIndex+ident+" 'view', '" + PageParser.escapeJavaScript(view.name)+ "', 'javascript:ins(\\'"+tab+"\\',true)');\n");
371             treeIndex++;
372             if(mainSchema) {
373                 StringBuffer JavaDoc columnsBuffer = new StringBuffer JavaDoc();
374                 treeIndex = addColumns(view, buff, treeIndex, showColumnTypes, columnsBuffer);
375                 if(schema.contents.isH2) {
376                     PreparedStatement JavaDoc prep = null;
377                     try {
378                         prep = conn.prepareStatement("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=?");
379                         prep.setString(1, view.name);
380                         ResultSet JavaDoc rs = prep.executeQuery();
381                         if(rs.next()) {
382                             String JavaDoc sql = rs.getString("SQL");
383                             buff.append("setNode("+treeIndex+ identNode + " 'type', '" + PageParser.escapeJavaScript(sql)+ "', null);\n");
384                             treeIndex++;
385                         }
386                     } finally {
387                         JdbcUtils.closeSilently(prep);
388                     }
389                 }
390                 buff.append("addTable('"+PageParser.escapeJavaScript(view.name)+"', '"+PageParser.escapeJavaScript(columnsBuffer.toString())+"', "+tableId+");\n");
391             }
392         }
393         return treeIndex;
394     }
395
396     private String JavaDoc tables() {
397         AppSession app = getAppSession();
398         DbContents contents = app.getContents();
399         try {
400             contents.readContents(app.getMetaData());
401             app.loadBnf();
402             Connection JavaDoc conn = app.getConnection();
403             DatabaseMetaData JavaDoc meta = app.getMetaData();
404             boolean isH2 = contents.isH2;
405
406             StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
407             buff.append("setNode(0, 0, 0, 'database', '" + PageParser.escapeJavaScript((String JavaDoc)session.get("url"))+ "', null);\n");
408 // String version = meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion();
409
// buff.append("setNode(1, 0, 0, 'info', '" + PageParser.escapeJavaScript(version)+ "', null);\n");
410
//
411
// int treeIndex = 2;
412
int treeIndex = 1;
413
414             DbSchema defaultSchema = contents.defaultSchema;
415             treeIndex = addTablesAndViews(defaultSchema, true, buff, treeIndex);
416             DbSchema[] schemas = contents.schemas;
417             for(int i=0; i<schemas.length; i++) {
418                 DbSchema schema = schemas[i];
419                 if(schema == defaultSchema || schema == null) {
420                     continue;
421                 }
422                 buff.append("setNode("+treeIndex+", 0, 1, 'folder', '" + PageParser.escapeJavaScript(schema.name)+ "', null);\n");
423                 treeIndex++;
424                 treeIndex = addTablesAndViews(schema, false, buff, treeIndex);
425             }
426             if(isH2) {
427                 Statement JavaDoc stat = null;
428                 try {
429                     stat = conn.createStatement();
430                     ResultSet JavaDoc rs = stat.executeQuery("SELECT * FROM INFORMATION_SCHEMA.SEQUENCES ORDER BY SEQUENCE_NAME");
431                     for(int i=0; rs.next(); i++) {
432                         if(i==0) {
433                             buff.append("setNode("+treeIndex+", 0, 1, 'sequences', '${text.tree.sequences}', null);\n");
434                             treeIndex++;
435                         }
436                         String JavaDoc name = rs.getString("SEQUENCE_NAME");
437                         String JavaDoc current = rs.getString("CURRENT_VALUE");
438                         String JavaDoc increment = rs.getString("INCREMENT");
439                         buff.append("setNode("+treeIndex+", 1, 1, 'sequence', '" + PageParser.escapeJavaScript(name)+ "', null);\n");
440                         treeIndex++;
441                         buff.append("setNode("+treeIndex+", 2, 2, 'type', '${text.tree.current}: " + PageParser.escapeJavaScript(current)+ "', null);\n");
442                         treeIndex++;
443                         if(!increment.equals("1")) {
444                             buff.append("setNode("+treeIndex+", 2, 2, 'type', '${text.tree.increment}: " + PageParser.escapeJavaScript(increment)+ "', null);\n");
445                             treeIndex++;
446                         }
447                     }
448                     rs = conn.createStatement().executeQuery("SELECT * FROM INFORMATION_SCHEMA.USERS ORDER BY NAME");
449                     for(int i=0; rs.next(); i++) {
450                         if(i==0) {
451                             buff.append("setNode("+treeIndex+", 0, 1, 'users', '${text.tree.users}', null);\n");
452                             treeIndex++;
453                         }
454                         String JavaDoc name = rs.getString("NAME");
455                         String JavaDoc admin = rs.getString("ADMIN");
456                         buff.append("setNode("+treeIndex+", 1, 1, 'user', '" + PageParser.escapeJavaScript(name)+ "', null);\n");
457                         treeIndex++;
458                         if(admin.equalsIgnoreCase("TRUE")) {
459                             buff.append("setNode("+treeIndex+", 2, 2, 'type', '${text.tree.admin}', null);\n");
460                             treeIndex++;
461                         }
462                     }
463                 } finally {
464                     JdbcUtils.closeSilently(stat);
465                 }
466             }
467             String JavaDoc version = meta.getDatabaseProductName() + " " + meta.getDatabaseProductVersion();
468             buff.append("setNode("+treeIndex+", 0, 0, 'info', '" + PageParser.escapeJavaScript(version)+ "', null);\n");
469             buff.append("refreshQueryTables();");
470             session.put("tree", buff.toString());
471         } catch(Exception JavaDoc e) {
472             // TODO log error
473
session.put("tree", "");
474             session.put("error", getStackTrace(0, e));
475         }
476         return "tables.jsp";
477     }
478
479     private String JavaDoc getStackTrace(int id, Throwable JavaDoc e) {
480         StringWriter JavaDoc writer = new StringWriter JavaDoc();
481         e.printStackTrace(new PrintWriter JavaDoc(writer));
482         String JavaDoc s = writer.toString();
483         s = PageParser.escapeHtml(s);
484         s = StringUtils.replaceAll(s, "\t", "&nbsp;&nbsp;&nbsp;&nbsp;");
485         String JavaDoc message = PageParser.escapeHtml(e.getMessage());
486         s = "<a class=\"error\" HREF=\"#\" onclick=\"var x=document.getElementById('st"+id+"').style;x.display=x.display==''?'none':'';\">" + message + "</a><span style=\"display: none;\" id=\"st"+id+"\"><br>"+ s + "</span>";
487         s = formatAsError(s);
488         return s;
489     }
490
491     private String JavaDoc formatAsError(String JavaDoc s) {
492         return "<div class=\"error\">"+s+"</div>";
493     }
494
495     private String JavaDoc test() {
496         String JavaDoc driver = attributes.getProperty("driver", "");
497         String JavaDoc url = attributes.getProperty("url", "");
498         String JavaDoc user = attributes.getProperty("user", "");
499         String JavaDoc password = attributes.getProperty("password", "");
500         session.put("driver", driver);
501         session.put("url", url);
502         session.put("user", user);
503         try {
504             Connection JavaDoc conn = server.getAppServer().getConnection(driver, url, user, password);
505             try {
506                 conn.close();
507             } catch(SQLException JavaDoc e2) {
508                 // TODO log error
509
}
510             session.put("error", "${text.login.testSuccessful}");
511             return "index.jsp";
512         } catch(Exception JavaDoc e) {
513             session.put("error", getLoginError(e));
514             return "index.jsp";
515         }
516     }
517
518     private String JavaDoc getLoginError(Exception JavaDoc e) {
519         if(e instanceof ClassNotFoundException JavaDoc) {
520             return "${text.login.driverNotFound}<br>" + getStackTrace(0, e);
521         } else {
522             return getStackTrace(0, e);
523         }
524     }
525
526     private String JavaDoc login() {
527         String JavaDoc driver = attributes.getProperty("driver", "");
528         String JavaDoc url = attributes.getProperty("url", "");
529         String JavaDoc user = attributes.getProperty("user", "");
530         String JavaDoc password = attributes.getProperty("password", "");
531         try {
532             Connection JavaDoc conn = server.getAppServer().getConnection(driver, url, user, password);
533             AppSession appSession = getAppSession();
534             appSession.setConnection(conn);
535             session.put("url", url);
536             session.put("user", user);
537             session.put("autocommit", "checked");
538             session.put("autocomplete", "1");
539             session.put("maxrows", "1000");
540             session.remove("error");
541             settingSave();
542             return "frame.jsp";
543         } catch(Exception JavaDoc e) {
544             session.put("error", getLoginError(e));
545             return "index.jsp";
546         }
547     }
548
549     private String JavaDoc logout() {
550         try {
551             Connection JavaDoc conn = getAppSession().getConnection();
552             getAppSession().setConnection(null);
553             session.remove("conn");
554             session.remove("result");
555             session.remove("tables");
556             session.remove("user");
557             if(conn != null) {
558                 conn.close();
559             }
560         } catch(Exception JavaDoc e) {
561             server.trace(e.toString());
562         }
563         return "index.do";
564     }
565
566     private String JavaDoc query() {
567         String JavaDoc sql = attributes.getProperty("sql").trim();
568         try {
569             Connection JavaDoc conn = getAppSession().getConnection();
570             String JavaDoc result;
571             if(sql.equals("@AUTOCOMMIT TRUE")) {
572                 conn.setAutoCommit(true);
573                 result = "${text.result.autocommitOn}";
574             } else if(sql.equals("@AUTOCOMMIT FALSE")) {
575                 conn.setAutoCommit(false);
576                 result = "${text.result.autocommitOff}";
577             } else if(sql.startsWith("@TRANSACTION_ISOLATION")) {
578                 String JavaDoc s = sql.substring("@TRANSACTION_ISOLATION".length()).trim();
579                 if(s.length()>0) {
580                     int level = Integer.parseInt(s);
581                     conn.setTransactionIsolation(level);
582                 }
583                 result = "Transaction Isolation: " + conn.getTransactionIsolation() + "<br>";
584                 result += Connection.TRANSACTION_READ_UNCOMMITTED + ": READ_UNCOMMITTED<br>";
585                 result += Connection.TRANSACTION_READ_COMMITTED + ": READ_COMMITTED<br>";
586                 result += Connection.TRANSACTION_REPEATABLE_READ + ": REPEATABLE_READ<br>";
587                 result += Connection.TRANSACTION_SERIALIZABLE + ": SERIALIZABLE";
588             } else if(sql.startsWith("@SET MAXROWS ")) {
589                 int maxrows = Integer.parseInt(sql.substring("@SET MAXROWS ".length()));
590                 session.put("maxrows", ""+maxrows);
591                 result = "${text.result.maxrowsSet}";
592             } else {
593                 ScriptReader r = new ScriptReader(new StringReader JavaDoc(sql));
594                 ObjectArray list = new ObjectArray();
595                 while(true) {
596                     String JavaDoc s = r.readStatement();
597                     if(s == null) {
598                         break;
599                     }
600                     list.add(s);
601                 }
602                 StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
603                 for(int i=0; i<list.size(); i++) {
604                     String JavaDoc s = (String JavaDoc) list.get(i);
605                     if(!s.startsWith("@")) {
606                         buff.append(PageParser.escapeHtml(s+";"));
607                         buff.append("<br>");
608                     }
609                     buff.append(getResult(conn, i+1, s, list.size()==1));
610                     buff.append("<br>");
611                 }
612                 result = buff.toString();
613             }
614             session.put("result", result);
615         } catch(Throwable JavaDoc e) {
616             session.put("result", getStackTrace(0, e));
617         }
618         return "result.jsp";
619     }
620
621     private String JavaDoc editResult() {
622         ResultSet JavaDoc rs = getAppSession().result;
623         int row = Integer.parseInt(attributes.getProperty("row"));
624         int op = Integer.parseInt(attributes.getProperty("op"));
625         String JavaDoc result = "", error="";
626         try {
627             if(op==1) {
628                 boolean insert = row < 0;
629                 if(insert) {
630                     rs.moveToInsertRow();
631                 } else {
632                     rs.absolute(row);
633                 }
634                 for(int i=0; i<rs.getMetaData().getColumnCount(); i++) {
635                     String JavaDoc x = attributes.getProperty("r"+row+"c"+(i+1));
636                     rs.updateString(i+1, x);
637                 }
638                 if(insert) {
639                     rs.insertRow();
640                 } else {
641                     rs.updateRow();
642                 }
643             } else if(op==2) {
644                 rs.absolute(row);
645                 rs.deleteRow();
646             } else if(op==3) {
647                 // cancel
648
}
649         } catch(Throwable JavaDoc e) {
650             result = "<br>"+getStackTrace(0, e);
651             error = formatAsError(e.getMessage());
652         }
653         String JavaDoc sql = "@EDIT " + (String JavaDoc) session.get("resultsetSQL");
654         Connection JavaDoc conn = getAppSession().getConnection();
655         result = error + getResult(conn, -1, sql, true) + result;
656         session.put("result", result);
657         return "result.jsp";
658     }
659
660     private ResultSet JavaDoc getMetaResultSet(Connection JavaDoc conn, String JavaDoc sql) throws SQLException JavaDoc {
661         DatabaseMetaData JavaDoc meta = conn.getMetaData();
662         if(sql.startsWith("@TABLES")) {
663             String JavaDoc[] p = split(sql);
664             String JavaDoc[] types = p[4] == null ? null : StringUtils.arraySplit(p[4], ',', false);
665             return meta.getTables(p[1], p[2], p[3], types);
666         } else if(sql.startsWith("@COLUMNS")) {
667             String JavaDoc[] p = split(sql);
668             return meta.getColumns(p[1], p[2], p[3], p[4]);
669         } else if(sql.startsWith("@INDEX_INFO")) {
670             String JavaDoc[] p = split(sql);
671             boolean unique = p[4] == null ? false : Boolean.valueOf(p[4]).booleanValue();
672             boolean approx = p[5] == null ? false : Boolean.valueOf(p[5]).booleanValue();
673             return meta.getIndexInfo(p[1], p[2], p[3], unique, approx);
674         } else if(sql.startsWith("@PRIMARY_KEYS")) {
675             String JavaDoc[] p = split(sql);
676             return meta.getPrimaryKeys(p[1], p[2], p[3]);
677         } else if(sql.startsWith("@PROCEDURES")) {
678             String JavaDoc[] p = split(sql);
679             return meta.getProcedures(p[1], p[2], p[3]);
680         } else if(sql.startsWith("@PROCEDURE_COLUMNS")) {
681             String JavaDoc[] p = split(sql);
682             return meta.getProcedureColumns(p[1], p[2], p[3], p[4]);
683         } else if(sql.startsWith("@SCHEMAS")) {
684             return meta.getSchemas();
685         } else if(sql.startsWith("@CATALOG")) {
686             SimpleResultSet rs = new SimpleResultSet();
687             rs.addColumn("CATALOG", Types.VARCHAR, 0, 0);
688             rs.addRow(new String JavaDoc[]{conn.getCatalog()});
689             return rs;
690         } else if(sql.startsWith("@MEMORY")) {
691             SimpleResultSet rs = new SimpleResultSet();
692             rs.addColumn("Type", Types.VARCHAR, 0, 0);
693             rs.addColumn("Value", Types.VARCHAR, 0, 0);
694             rs.addRow(new String JavaDoc[]{"Used Memory", "" + MemoryUtils.getMemoryUsed()});
695             rs.addRow(new String JavaDoc[]{"Free Memory", "" + MemoryUtils.getMemoryFree()});
696             return rs;
697         } else if(sql.startsWith("@INFO")) {
698             SimpleResultSet rs = new SimpleResultSet();
699             rs.addColumn("KEY", Types.VARCHAR, 0, 0);
700             rs.addColumn("VALUE", Types.VARCHAR, 0, 0);
701             rs.addRow(new String JavaDoc[]{"conn.getCatalog", conn.getCatalog()});
702             rs.addRow(new String JavaDoc[]{"conn.getAutoCommit", ""+conn.getAutoCommit()});
703             rs.addRow(new String JavaDoc[]{"conn.getTransactionIsolation", ""+conn.getTransactionIsolation()});
704             rs.addRow(new String JavaDoc[]{"conn.getWarnings", ""+conn.getWarnings()});
705             String JavaDoc map;
706             try {
707                 map = "" + conn.getTypeMap();
708             } catch(SQLException JavaDoc e) {
709                 map = e.toString();
710             }
711             rs.addRow(new String JavaDoc[]{"conn.getTypeMap", ""+map});
712             rs.addRow(new String JavaDoc[]{"conn.isReadOnly", ""+conn.isReadOnly()});
713             rs.addRow(new String JavaDoc[]{"meta.getCatalogSeparator", ""+meta.getCatalogSeparator()});
714             rs.addRow(new String JavaDoc[]{"meta.getCatalogTerm", ""+meta.getCatalogTerm()});
715             rs.addRow(new String JavaDoc[]{"meta.getDatabaseProductName", ""+meta.getDatabaseProductName()});
716             rs.addRow(new String JavaDoc[]{"meta.getDatabaseProductVersion", ""+meta.getDatabaseProductVersion()});
717             rs.addRow(new String JavaDoc[]{"meta.getDefaultTransactionIsolation", ""+meta.getDefaultTransactionIsolation()});
718             rs.addRow(new String JavaDoc[]{"meta.getDriverMajorVersion", ""+meta.getDriverMajorVersion()});
719             rs.addRow(new String JavaDoc[]{"meta.getDriverMinorVersion", ""+meta.getDriverMinorVersion()});
720             rs.addRow(new String JavaDoc[]{"meta.getDriverName", ""+meta.getDriverName()});
721             rs.addRow(new String JavaDoc[]{"meta.getDriverVersion", ""+meta.getDriverVersion()});
722             rs.addRow(new String JavaDoc[]{"meta.getExtraNameCharacters", ""+meta.getExtraNameCharacters()});
723             rs.addRow(new String JavaDoc[]{"meta.getIdentifierQuoteString", ""+meta.getIdentifierQuoteString()});
724             rs.addRow(new String JavaDoc[]{"meta.getMaxBinaryLiteralLength", ""+meta.getMaxBinaryLiteralLength()});
725             rs.addRow(new String JavaDoc[]{"meta.getMaxCatalogNameLength", ""+meta.getMaxCatalogNameLength()});
726             rs.addRow(new String JavaDoc[]{"meta.getMaxCharLiteralLength", ""+meta.getMaxCharLiteralLength()});
727             rs.addRow(new String JavaDoc[]{"meta.getMaxColumnNameLength", ""+meta.getMaxColumnNameLength()});
728             rs.addRow(new String JavaDoc[]{"meta.getMaxColumnsInGroupBy", ""+meta.getMaxColumnsInGroupBy()});
729             rs.addRow(new String JavaDoc[]{"meta.getMaxColumnsInIndex", ""+meta.getMaxColumnsInIndex()});
730             rs.addRow(new String JavaDoc[]{"meta.getMaxColumnsInOrderBy", ""+meta.getMaxColumnsInOrderBy()});
731             rs.addRow(new String JavaDoc[]{"meta.getMaxColumnsInSelect", ""+meta.getMaxColumnsInSelect()});
732             rs.addRow(new String JavaDoc[]{"meta.getMaxColumnsInTable", ""+meta.getMaxColumnsInTable()});
733             rs.addRow(new String JavaDoc[]{"meta.getMaxConnections", ""+meta.getMaxConnections()});
734             rs.addRow(new String JavaDoc[]{"meta.getMaxCursorNameLength", ""+meta.getMaxCursorNameLength()});
735             rs.addRow(new String JavaDoc[]{"meta.getMaxIndexLength", ""+meta.getMaxIndexLength()});
736             rs.addRow(new String JavaDoc[]{"meta.getMaxProcedureNameLength", ""+meta.getMaxProcedureNameLength()});
737             rs.addRow(new String JavaDoc[]{"meta.getMaxRowSize", ""+meta.getMaxRowSize()});
738             rs.addRow(new String JavaDoc[]{"meta.getMaxSchemaNameLength", ""+meta.getMaxSchemaNameLength()});
739             rs.addRow(new String JavaDoc[]{"meta.getMaxStatementLength", ""+meta.getMaxStatementLength()});
740             rs.addRow(new String JavaDoc[]{"meta.getMaxStatements", ""+meta.getMaxStatements()});
741             rs.addRow(new String JavaDoc[]{"meta.getMaxTableNameLength", ""+meta.getMaxTableNameLength()});
742             rs.addRow(new String JavaDoc[]{"meta.getMaxTablesInSelect", ""+meta.getMaxTablesInSelect()});
743             rs.addRow(new String JavaDoc[]{"meta.getMaxUserNameLength", ""+meta.getMaxUserNameLength()});
744             rs.addRow(new String JavaDoc[]{"meta.getNumericFunctions", ""+meta.getNumericFunctions()});
745             rs.addRow(new String JavaDoc[]{"meta.getProcedureTerm", ""+meta.getProcedureTerm()});
746             rs.addRow(new String JavaDoc[]{"meta.getSchemaTerm", ""+meta.getSchemaTerm()});
747             rs.addRow(new String JavaDoc[]{"meta.getSearchStringEscape", ""+meta.getSearchStringEscape()});
748             rs.addRow(new String JavaDoc[]{"meta.getSQLKeywords", ""+meta.getSQLKeywords()});
749             rs.addRow(new String JavaDoc[]{"meta.getStringFunctions", ""+meta.getStringFunctions()});
750             rs.addRow(new String JavaDoc[]{"meta.getSystemFunctions", ""+meta.getSystemFunctions()});
751             rs.addRow(new String JavaDoc[]{"meta.getTimeDateFunctions", ""+meta.getTimeDateFunctions()});
752             rs.addRow(new String JavaDoc[]{"meta.getURL", ""+meta.getURL()});
753             rs.addRow(new String JavaDoc[]{"meta.getUserName", ""+meta.getUserName()});
754             rs.addRow(new String JavaDoc[]{"meta.isCatalogAtStart", ""+meta.isCatalogAtStart()});
755             rs.addRow(new String JavaDoc[]{"meta.isReadOnly", ""+meta.isReadOnly()});
756             rs.addRow(new String JavaDoc[]{"meta.allProceduresAreCallable", ""+meta.allProceduresAreCallable()});
757             rs.addRow(new String JavaDoc[]{"meta.allTablesAreSelectable", ""+meta.allTablesAreSelectable()});
758             rs.addRow(new String JavaDoc[]{"meta.dataDefinitionCausesTransactionCommit", ""+meta.dataDefinitionCausesTransactionCommit()});
759             rs.addRow(new String JavaDoc[]{"meta.dataDefinitionIgnoredInTransactions", ""+meta.dataDefinitionIgnoredInTransactions()});
760             rs.addRow(new String JavaDoc[]{"meta.doesMaxRowSizeIncludeBlobs", ""+meta.doesMaxRowSizeIncludeBlobs()});
761             rs.addRow(new String JavaDoc[]{"meta.nullPlusNonNullIsNull", ""+meta.nullPlusNonNullIsNull()});
762             rs.addRow(new String JavaDoc[]{"meta.nullsAreSortedAtEnd", ""+meta.nullsAreSortedAtEnd()});
763             rs.addRow(new String JavaDoc[]{"meta.nullsAreSortedAtStart", ""+meta.nullsAreSortedAtStart()});
764             rs.addRow(new String JavaDoc[]{"meta.nullsAreSortedHigh", ""+meta.nullsAreSortedHigh()});
765             rs.addRow(new String JavaDoc[]{"meta.nullsAreSortedLow", ""+meta.nullsAreSortedLow()});
766             rs.addRow(new String JavaDoc[]{"meta.storesLowerCaseIdentifiers", ""+meta.storesLowerCaseIdentifiers()});
767             rs.addRow(new String JavaDoc[]{"meta.storesLowerCaseQuotedIdentifiers", ""+meta.storesLowerCaseQuotedIdentifiers()});
768             rs.addRow(new String JavaDoc[]{"meta.storesMixedCaseIdentifiers", ""+meta.storesMixedCaseIdentifiers()});
769             rs.addRow(new String JavaDoc[]{"meta.storesMixedCaseQuotedIdentifiers", ""+meta.storesMixedCaseQuotedIdentifiers()});
770             rs.addRow(new String JavaDoc[]{"meta.storesUpperCaseIdentifiers", ""+meta.storesUpperCaseIdentifiers()});
771             rs.addRow(new String JavaDoc[]{"meta.storesUpperCaseQuotedIdentifiers", ""+meta.storesUpperCaseQuotedIdentifiers()});
772             rs.addRow(new String JavaDoc[]{"meta.supportsAlterTableWithAddColumn", ""+meta.supportsAlterTableWithAddColumn()});
773             rs.addRow(new String JavaDoc[]{"meta.supportsAlterTableWithDropColumn", ""+meta.supportsAlterTableWithDropColumn()});
774             rs.addRow(new String JavaDoc[]{"meta.supportsANSI92EntryLevelSQL", ""+meta.supportsANSI92EntryLevelSQL()});
775             rs.addRow(new String JavaDoc[]{"meta.supportsANSI92FullSQL", ""+meta.supportsANSI92FullSQL()});
776             rs.addRow(new String JavaDoc[]{"meta.supportsANSI92IntermediateSQL", ""+meta.supportsANSI92IntermediateSQL()});
777             rs.addRow(new String JavaDoc[]{"meta.supportsBatchUpdates", ""+meta.supportsBatchUpdates()});
778             rs.addRow(new String JavaDoc[]{"meta.supportsCatalogsInDataManipulation", ""+meta.supportsCatalogsInDataManipulation()});
779             rs.addRow(new String JavaDoc[]{"meta.supportsCatalogsInIndexDefinitions", ""+meta.supportsCatalogsInIndexDefinitions()});
780             rs.addRow(new String JavaDoc[]{"meta.supportsCatalogsInPrivilegeDefinitions", ""+meta.supportsCatalogsInPrivilegeDefinitions()});
781             rs.addRow(new String JavaDoc[]{"meta.supportsCatalogsInProcedureCalls", ""+meta.supportsCatalogsInProcedureCalls()});
782             rs.addRow(new String JavaDoc[]{"meta.supportsCatalogsInTableDefinitions", ""+meta.supportsCatalogsInTableDefinitions()});
783             rs.addRow(new String JavaDoc[]{"meta.supportsColumnAliasing", ""+meta.supportsColumnAliasing()});
784             rs.addRow(new String JavaDoc[]{"meta.supportsConvert", ""+meta.supportsConvert()});
785             rs.addRow(new String JavaDoc[]{"meta.supportsCoreSQLGrammar", ""+meta.supportsCoreSQLGrammar()});
786             rs.addRow(new String JavaDoc[]{"meta.supportsCorrelatedSubqueries", ""+meta.supportsCorrelatedSubqueries()});
787             rs.addRow(new String JavaDoc[]{"meta.supportsDataDefinitionAndDataManipulationTransactions", ""+meta.supportsDataDefinitionAndDataManipulationTransactions()});
788             rs.addRow(new String JavaDoc[]{"meta.supportsDataManipulationTransactionsOnly", ""+meta.supportsDataManipulationTransactionsOnly()});
789             rs.addRow(new String JavaDoc[]{"meta.supportsDifferentTableCorrelationNames", ""+meta.supportsDifferentTableCorrelationNames()});
790             rs.addRow(new String JavaDoc[]{"meta.supportsExpressionsInOrderBy", ""+meta.supportsExpressionsInOrderBy()});
791             rs.addRow(new String JavaDoc[]{"meta.supportsExtendedSQLGrammar", ""+meta.supportsExtendedSQLGrammar()});
792             rs.addRow(new String JavaDoc[]{"meta.supportsFullOuterJoins", ""+meta.supportsFullOuterJoins()});
793             rs.addRow(new String JavaDoc[]{"meta.supportsGroupBy", ""+meta.supportsGroupBy()});
794             // TODO meta data: more supports methods (I'm tired now)
795
rs.addRow(new String JavaDoc[]{"meta.usesLocalFilePerTable", ""+meta.usesLocalFilePerTable()});
796             rs.addRow(new String JavaDoc[]{"meta.usesLocalFiles", ""+meta.usesLocalFiles()});
797 //#ifdef JDK14
798
rs.addRow(new String JavaDoc[]{"conn.getHoldability", ""+conn.getHoldability()});
799             rs.addRow(new String JavaDoc[]{"meta.getDatabaseMajorVersion", ""+meta.getDatabaseMajorVersion()});
800             rs.addRow(new String JavaDoc[]{"meta.getDatabaseMinorVersion", ""+meta.getDatabaseMinorVersion()});
801             rs.addRow(new String JavaDoc[]{"meta.getJDBCMajorVersion", ""+meta.getJDBCMajorVersion()});
802             rs.addRow(new String JavaDoc[]{"meta.getJDBCMinorVersion", ""+meta.getJDBCMinorVersion()});
803             rs.addRow(new String JavaDoc[]{"meta.getResultSetHoldability", ""+meta.getResultSetHoldability()});
804             rs.addRow(new String JavaDoc[]{"meta.getSQLStateType", ""+meta.getSQLStateType()});
805             rs.addRow(new String JavaDoc[]{"meta.supportsGetGeneratedKeys", ""+meta.supportsGetGeneratedKeys()});
806             rs.addRow(new String JavaDoc[]{"meta.locatorsUpdateCopy", ""+meta.locatorsUpdateCopy()});
807 //#endif
808
return rs;
809         } else if(sql.startsWith("@CATALOGS")) {
810             return meta.getCatalogs();
811         } else if(sql.startsWith("@TABLE_TYPES")) {
812             return meta.getTableTypes();
813         } else if(sql.startsWith("@COLUMN_PRIVILEGES")) {
814             String JavaDoc[] p = split(sql);
815             return meta.getColumnPrivileges(p[1], p[2], p[3], p[4]);
816         } else if(sql.startsWith("@TABLE_PRIVILEGES")) {
817             String JavaDoc[] p = split(sql);
818             return meta.getTablePrivileges(p[1], p[2], p[3]);
819         } else if(sql.startsWith("@BEST_ROW_IDENTIFIER")) {
820             String JavaDoc[] p = split(sql);
821             int scale = p[4] == null ? 0 : Integer.parseInt(p[4]);
822             boolean nullable = p[5] == null ? false : Boolean.valueOf(p[5]).booleanValue();
823             return meta.getBestRowIdentifier(p[1], p[2], p[3], scale, nullable);
824         } else if(sql.startsWith("@VERSION_COLUMNS")) {
825             String JavaDoc[] p = split(sql);
826             return meta.getVersionColumns(p[1], p[2], p[3]);
827         } else if(sql.startsWith("@IMPORTED_KEYS")) {
828             String JavaDoc[] p = split(sql);
829             return meta.getImportedKeys(p[1], p[2], p[3]);
830         } else if(sql.startsWith("@EXPORTED_KEYS")) {
831             String JavaDoc[] p = split(sql);
832             return meta.getExportedKeys(p[1], p[2], p[3]);
833         } else if(sql.startsWith("@CROSS_REFERENCE")) {
834             String JavaDoc[] p = split(sql);
835             return meta.getCrossReference(p[1], p[2], p[3], p[4], p[5], p[6]);
836         } else if(sql.startsWith("@UDTS")) {
837             String JavaDoc[] p = split(sql);
838             int[] types;
839             if(p[4] == null) {
840                 types = null;
841             } else {
842                 String JavaDoc[] t = StringUtils.arraySplit(p[4], ',', false);
843                 types = new int[t.length];
844                 for(int i=0; i<t.length; i++) {
845                     types[i] = Integer.parseInt(t[i]);
846                 }
847             }
848             return meta.getUDTs(p[1], p[2], p[3], types);
849         } else if(sql.startsWith("@TYPE_INFO")) {
850             return meta.getTypeInfo();
851 //#ifdef JDK14
852
} else if(sql.startsWith("@SUPER_TYPES")) {
853             String JavaDoc[] p = split(sql);
854             return meta.getSuperTypes(p[1], p[2], p[3]);
855         } else if(sql.startsWith("@SUPER_TABLES")) {
856             String JavaDoc[] p = split(sql);
857             return meta.getSuperTables(p[1], p[2], p[3]);
858         } else if(sql.startsWith("@ATTRIBUTES")) {
859             String JavaDoc[] p = split(sql);
860             return meta.getAttributes(p[1], p[2], p[3], p[4]);
861 //#endif
862
}
863         return null;
864     }
865
866     private String JavaDoc[] split(String JavaDoc s) {
867         String JavaDoc[] list = new String JavaDoc[10];
868         String JavaDoc[] t = StringUtils.arraySplit(s, ' ', true);
869         System.arraycopy(t, 0, list, 0, t.length);
870         for(int i=0; i<list.length; i++) {
871             if("null".equals(list[i])) {
872                 list[i] = null;
873             }
874         }
875         return list;
876     }
877
878     private int getMaxrows() {
879         String JavaDoc r = (String JavaDoc)session.get("maxrows");
880         int maxrows = r==null ? 0 : Integer.parseInt(r);
881         return maxrows;
882     }
883
884     private String JavaDoc getResult(Connection JavaDoc conn, int id, String JavaDoc sql, boolean allowEdit) {
885         try {
886             sql = sql.trim();
887             StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
888             String JavaDoc sqlUpper = StringUtils.toUpperEnglish(sql);
889             if(sqlUpper.startsWith("CREATE") || sqlUpper.startsWith("DROP") || sqlUpper.startsWith("ALTER") || sqlUpper.startsWith("RUNSCRIPT")) {
890                 String JavaDoc sessionId = attributes.getProperty("jsessionid");
891                 buff.append("<script type=\"text/javascript\">top['h2menu'].location='tables.do?jsessionid="+sessionId+"';</script>");
892             }
893             Statement JavaDoc stat = conn.createStatement();
894             ResultSet JavaDoc rs;
895             long time = System.currentTimeMillis();
896             boolean metadata = false;
897             boolean edit = false;
898             if(sql.equals("@CANCEL")) {
899                 stat = getAppSession().executingStatement;
900                 if(stat != null) {
901                     stat.cancel();
902                     buff.append("${text.result.statementWasCancelled}");
903                 } else {
904                     buff.append("${text.result.noRunningStatement}");
905                 }
906                 return buff.toString();
907             } else if(sql.startsWith("@META")) {
908                 metadata = true;
909                 sql = sql.substring("@META".length()).trim();
910             } else if(sql.startsWith("@LOOP")) {
911                 metadata = true;
912                 sql = sql.substring("@LOOP".length()).trim();
913                 int idx = sql.indexOf(' ');
914                 int count = MathUtils.decodeInt(sql.substring(0, idx));
915                 sql = sql.substring(idx).trim();
916                 return executeLoop(conn, count, sql);
917             } else if(sql.startsWith("@EDIT")) {
918                 edit = true;
919                 sql = sql.substring("@EDIT".length()).trim();
920                 session.put("resultsetSQL", sql);
921             } else if(sql.equals("@HISTORY")) {
922                 buff.append(getHistoryString());
923                 return buff.toString();
924             }
925             if(sql.startsWith("@")) {
926                 rs = getMetaResultSet(conn, sql);
927                 if(rs == null) {
928                     buff.append("?: "+sql);
929                     return buff.toString();
930                 }
931             } else {
932                 int maxrows = getMaxrows();
933                 stat.setMaxRows(maxrows);
934                 getAppSession().executingStatement = stat;
935                 boolean isResultSet = stat.execute(sql);
936                 getAppSession().addCommand(sql);
937                 if(!isResultSet) {
938                     buff.append("${text.result.updateCount}: "+stat.getUpdateCount());
939                     time = System.currentTimeMillis() - time;
940                     buff.append("<br>(");
941                     buff.append(time);
942                     buff.append(" ms)");
943                     stat.close();
944                     return buff.toString();
945                 }
946                 rs = stat.getResultSet();
947             }
948             time = System.currentTimeMillis() - time;
949             buff.append(getResultSet(sql, rs, metadata, edit, time, allowEdit));
950             if(!edit) {
951                 stat.close();
952             }
953             return buff.toString();
954         } catch(Exception JavaDoc e) {
955             return getStackTrace(id, e);
956         } finally {
957             getAppSession().executingStatement = null;
958         }
959     }
960
961     private String JavaDoc executeLoop(Connection JavaDoc conn, int count, String JavaDoc sql) throws SQLException JavaDoc {
962         ArrayList JavaDoc params = new ArrayList JavaDoc();
963         int idx = 0;
964         while(true) {
965             idx = sql.indexOf('?', idx);
966             if(idx < 0) {
967                 break;
968             }
969             if(sql.substring(idx).startsWith("?/*RND*/")) {
970                 params.add(new Integer JavaDoc(1));
971                 sql = sql.substring(0, idx) + "?" + sql.substring(idx+"/*RND*/".length()+1);
972             } else {
973                 params.add(new Integer JavaDoc(0));
974             }
975             idx++;
976         }
977         int rows = 0;
978         boolean prepared;
979         Random JavaDoc random = new Random JavaDoc(1);
980         long time = System.currentTimeMillis();
981         if(sql.startsWith("@STATEMENT")) {
982             sql = sql.substring("@STATEMENT".length()).trim();
983             prepared = false;
984             Statement JavaDoc stat = conn.createStatement();
985             for(int i=0; i<count; i++) {
986                 String JavaDoc s = sql;
987                 for(int j=0; j<params.size(); j++) {
988                     idx = s.indexOf('?');
989                     Integer JavaDoc type = (Integer JavaDoc) params.get(j);
990                     if(type.intValue() == 1) {
991                         s = s.substring(0, idx) + random.nextInt(count) + s.substring(idx+1);
992                     } else {
993                         s = s.substring(0, idx) + i + s.substring(idx+1);
994                     }
995                 }
996                 if(stat.execute(s)) {
997                     ResultSet JavaDoc rs = stat.getResultSet();
998                     while(rs.next()) {
999                         rows++;
1000                        // maybe get the data as well
1001
}
1002                    // maybe close result set
1003
}
1004            }
1005        } else {
1006            prepared = true;
1007            PreparedStatement JavaDoc prep = conn.prepareStatement(sql);
1008            for(int i=0; i<count; i++) {
1009                for(int j=0; j<params.size(); j++) {
1010                    Integer JavaDoc type = (Integer JavaDoc) params.get(j);
1011                    if(type.intValue() == 1) {
1012                        prep.setInt(j + 1, random.nextInt(count));
1013                    } else {
1014                        prep.setInt(j + 1, i);
1015                    }
1016                }
1017                if(getAppSession().getContents().isSQLite) {
1018                    // SQLite currently throws an exception on prep.execute()
1019
prep.executeUpdate();
1020                } else {
1021                    if(prep.execute()) {
1022                        ResultSet JavaDoc rs = prep.getResultSet();
1023                        while(rs.next()) {
1024                            rows++;
1025                            // maybe get the data as well
1026
}
1027                    }
1028                }
1029            }
1030        }
1031        time = System.currentTimeMillis() - time;
1032        String JavaDoc result = time + " ms: " + count + " * ";
1033        if(prepared) {
1034            result += "(Prepared) ";
1035        } else {
1036            result += "(Statement) ";
1037        }
1038        result+="(";
1039        StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
1040        for(int i=0; i<params.size(); i++) {
1041            if(i>0) {
1042                buff.append(", ");
1043            }
1044            buff.append(((Integer JavaDoc)params.get(i)).intValue() == 0 ? "i" : "rnd");
1045        }
1046        result += buff.toString();
1047        result+=") " + sql;
1048        return result;
1049    }
1050
1051    private String JavaDoc getHistoryString() {
1052        StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
1053        ArrayList JavaDoc history = getAppSession().getCommands();
1054        buff.append("<table cellspacing=0 cellpadding=0>");
1055        buff.append("<tr><th></th><th>Command</th></tr>");
1056        for(int i=history.size()-1; i>=0; i--) {
1057            String JavaDoc sql = (String JavaDoc) history.get(i);
1058            buff.append("<tr><td>");
1059            buff.append("<a HREF=\"gethistory.do?id=");
1060            buff.append(i);
1061            buff.append("&jsessionid=${sessionId}\" target=\"h2query\" ><img width=16 height=16 SRC=\"ico_write.gif\" onmouseover = \"this.className ='icon_hover'\" onmouseout = \"this.className ='icon'\" class=\"icon\" alt=\"${text.resultEdit.edit}\" title=\"${text.resultEdit.edit}\" border=\"1\"></a>");
1062            buff.append("</td><td>");
1063            buff.append(PageParser.escapeHtml(sql));
1064            buff.append("</td></tr>");
1065        }
1066        buff.append("</t>");
1067        buff.append("</table>");
1068        return buff.toString();
1069    }
1070
1071    private String JavaDoc getResultSet(String JavaDoc sql, ResultSet JavaDoc rs, boolean metadata, boolean edit, long time, boolean allowEdit) throws SQLException JavaDoc {
1072        int maxrows = getMaxrows();
1073        time = System.currentTimeMillis() - time;
1074        StringBuffer JavaDoc buff = new StringBuffer JavaDoc();
1075        if(edit) {
1076            buff.append("<form id=\"editing\" name=\"editing\" method=\"post\" "
1077                    + "action=\"/editresult.do?jsessionid=${sessionId}\" id=\"mainForm\" target=\"h2result\">");
1078            buff.append("<input type=\"hidden\" name=\"op\" value=\"1\">");
1079            buff.append("<input type=\"hidden\" name=\"row\" value=\"\">");
1080            buff.append("<table cellspacing=0 cellpadding=0 id=\"editTable\">");
1081        } else {
1082            buff.append("<table cellspacing=0 cellpadding=0>");
1083        }
1084        ResultSetMetaData JavaDoc meta = rs.getMetaData();
1085        int columns = meta.getColumnCount();
1086        int rows=0;
1087        if(metadata) {
1088            buff.append("<tr><th>i</th><th>label</th><th>cat</th><th>schem</th>");
1089            buff.append("<th>tab</th><th>col</th><th>type</th><th>typeName</th><th>class</th>");
1090            buff.append("<th>prec</th><th>scale</th><th>size</th><th>autoInc</th>");
1091            buff.append("<th>case</th><th>curr</th><th>null</th><th>ro</th>");
1092            buff.append("<th>search</th><th>sig</th><th>w</th><th>defW</th></tr>");
1093            for(int i=1; i<=columns; i++) {
1094                buff.append("<tr>");
1095                buff.append("<td>").append(i).append("</td>");
1096                buff.append("<td>").append(PageParser.escapeHtml(meta.getColumnLabel(i))).append("</td>");
1097                buff.append("<td>").append(PageParser.escapeHtml(meta.getCatalogName(i))).append("</td>");
1098                buff.append("<td>").append(PageParser.escapeHtml(meta.getSchemaName(i))).append("</td>");
1099                buff.append("<td>").append(PageParser.escapeHtml(meta.getTableName(i))).append("</td>");
1100                buff.append("<td>").append(PageParser.escapeHtml(meta.getColumnName(i))).append("</td>");
1101                buff.append("<td>").append(meta.getColumnType(i)).append("</td>");
1102                buff.append("<td>").append(PageParser.escapeHtml(meta.getColumnTypeName(i))).append("</td>");
1103                buff.append("<td>").append(PageParser.escapeHtml(meta.getColumnClassName(i))).append("</td>");
1104                buff.append("<td>").append(meta.getPrecision(i)).append("</td>");
1105                buff.append("<td>").append(meta.getScale(i)).append("</td>");
1106                buff.append("<td>").append(meta.getColumnDisplaySize(i)).append("</td>");
1107                buff.append("<td>").append(meta.isAutoIncrement(i)).append("</td>");
1108                buff.append("<td>").append(meta.isCaseSensitive(i)).append("</td>");
1109                buff.append("<td>").append(meta.isCurrency(i)).append("</td>");
1110                buff.append("<td>").append(meta.isNullable(i)).append("</td>");
1111                buff.append("<td>").append(meta.isReadOnly(i)).append("</td>");
1112                buff.append("<td>").append(meta.isSearchable(i)).append("</td>");
1113                buff.append("<td>").append(meta.isSigned(i)).append("</td>");
1114                buff.append("<td>").append(meta.isWritable(i)).append("</td>");
1115                buff.append("<td>").append(meta.isDefinitelyWritable(i)).append("</td>");
1116                buff.append("</tr>");
1117            }
1118        } else {
1119            buff.append("<tr>");
1120            if(edit) {
1121                buff.append("<th>Action</th>");
1122            }
1123            for(int i=0; i<columns; i++) {
1124                buff.append("<th>");
1125                buff.append(PageParser.escapeHtml(meta.getColumnLabel(i+1)));
1126                buff.append("</th>");
1127            }
1128            buff.append("</tr>");
1129            while(rs.next()) {
1130                if(maxrows > 0 && rows >= maxrows) {
1131                    break;
1132                }
1133                rows++;
1134                buff.append("<tr>");
1135                if(edit) {
1136                    buff.append("<td>");
1137                    buff.append("<img onclick=\"javascript:editRow(");
1138                    buff.append(rs.getRow());
1139                    buff.append(",'${sessionId}', '${text.resultEdit.save}', '${text.resultEdit.cancel}'");
1140                    buff.append(")\" width=16 height=16 SRC=\"ico_write.gif\" onmouseover = \"this.className ='icon_hover'\" onmouseout = \"this.className ='icon'\" class=\"icon\" alt=\"${text.resultEdit.edit}\" title=\"${text.resultEdit.edit}\" border=\"1\">");
1141                    buff.append("<a HREF=\"editresult.do?op=2&row=");
1142                    buff.append(rs.getRow());
1143                    buff.append("&jsessionid=${sessionId}\" target=\"h2result\" ><img width=16 height=16 SRC=\"ico_remove.gif\" onmouseover = \"this.className ='icon_hover'\" onmouseout = \"this.className ='icon'\" class=\"icon\" alt=\"${text.resultEdit.delete}\" title=\"${text.resultEdit.delete}\" border=\"1\"></a>");
1144                    buff.append("</td>");
1145                }
1146                for(int i=0; i<columns; i++) {
1147                    buff.append("<td>");
1148                    buff.append(PageParser.escapeHtml(rs.getString(i+1)));
1149                    buff.append("</td>");
1150                }
1151                buff.append("</tr>");
1152            }
1153        }
1154        boolean isUpdatable = rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE;
1155        if(edit) {
1156            ResultSet JavaDoc old = getAppSession().result;
1157            if(old != null) {
1158                old.close();
1159            }
1160            getAppSession().result = rs;
1161        } else {
1162            rs.close();
1163        }
1164        if(edit) {
1165            buff.append("<tr><td>");
1166            buff.append("<img onclick=\"javascript:editRow(-1, '${sessionId}', '${text.resultEdit.save}', '${text.resultEdit.cancel}'");
1167            buff.append(")\" width=16 height=16 SRC=\"ico_add.gif\" onmouseover = \"this.className ='icon_hover'\" onmouseout = \"this.className ='icon'\" class=\"icon\" alt=\"${text.resultEdit.add}\" title=\"${text.resultEdit.add}\" border=\"1\">");
1168            buff.append("</td>");
1169            for(int i=0; i<columns; i++) {
1170                buff.append("<td></td>");
1171            }
1172            buff.append("</tr>");
1173        }
1174        buff.append("</table>");
1175        if(edit) {
1176            buff.append("</form>");
1177        }
1178        if(rows == 0) {
1179            buff.append("(${text.result.noRows}");
1180        } else if(rows == 1) {
1181            buff.append("(${text.result.1row}");
1182        } else {
1183            buff.append("(");
1184            buff.append(rows);
1185            buff.append(" ${text.result.rows}");
1186        }
1187        buff.append(", ");
1188        time = System.currentTimeMillis() - time;
1189        buff.append(time);
1190        buff.append(" ms)");
1191        if(!edit && isUpdatable && allowEdit) {
1192            buff.append("<br><br><form name=\"editResult\" method=\"post\" action=\"/query.do?jsessionid=${sessionId}\" target=\"h2result\">");
1193            buff.append("<input type=\"submit\" class=\"button\" value=\"${text.resultEdit.editResult}\">");
1194            buff.append("<input type=\"hidden\" name=\"sql\" value=\"@EDIT " + PageParser.escapeHtml(sql) +"\">");
1195            buff.append("</form>");
1196        }
1197        return buff.toString();
1198    }
1199
1200    private String JavaDoc settingSave() {
1201        ConnectionInfo info = new ConnectionInfo();
1202        info.name = attributes.getProperty("name", "");
1203        info.driver = attributes.getProperty("driver", "");
1204        info.url = attributes.getProperty("url", "");
1205        info.user = attributes.getProperty("user", "");
1206        server.getAppServer().updateSetting(info);
1207        attributes.put("setting", info.name);
1208        server.getAppServer().saveSettings();
1209        return "index.do";
1210    }
1211
1212    private String JavaDoc settingRemove() {
1213        String JavaDoc setting = attributes.getProperty("name", "");
1214        server.getAppServer().removeSetting(setting);
1215        ArrayList JavaDoc settings = server.getAppServer().getSettings();
1216        if(settings.size() > 0) {
1217            attributes.put("setting", settings.get(0));
1218        }
1219        server.getAppServer().saveSettings();
1220        return "index.do";
1221    }
1222
1223    boolean allow() {
1224        if(server.getAppServer().getAllowOthers()) {
1225            return true;
1226        }
1227        return NetUtils.isLoopbackAddress(socket);
1228    }
1229
1230}
1231
Popular Tags