001: /*
002: * JFolder, Copyright 2001-2006 Gary Steinmetz
003: *
004: * Distributable under LGPL license.
005: * See terms of license at gnu.org.
006: */
007:
008: package org.jfolder.console.web.admin.database.access;
009:
010: //base classes
011: import java.io.IOException;
012: import java.sql.Connection;
013: import java.sql.DatabaseMetaData;
014: import java.sql.DriverManager;
015: import java.sql.PreparedStatement;
016: import java.sql.ResultSet;
017: import java.sql.ResultSetMetaData;
018: import java.sql.SQLException;
019: import java.util.ArrayList;
020: import java.util.HashMap;
021: import java.util.Iterator;
022: import java.util.StringTokenizer;
023:
024: //project specific classes
025: import org.jfolder.common.utils.misc.MiscHelper;
026: import org.jfolder.common.utils.web.ParameterSet;
027: import org.jfolder.common.web.template.ConsoleParameterContext;
028: import org.jfolder.common.web.template.SubmitActionContext;
029: import org.jfolder.common.web.template.SubmitActionParameters;
030: import org.jfolder.console.base.BaseConsoleServlet;
031: import org.jfolder.console.base.ConsolePageContext;
032: import org.jfolder.console.base.ConsolePageSession;
033: import org.jfolder.platforms.stores.base.ApplicationDataSourceResolver;
034: import org.jfolder.platforms.stores.base.SystemStore;
035: import org.jfolder.platforms.stores.base.SystemStoreFactory;
036: import org.jfolder.security.model.UserHolder;
037: import org.jfolder.services.config.ApplicationStoreSet;
038: import org.jfolder.services.config.ConfigService;
039: import org.jfolder.services.config.ConfigServiceCallerFactory;
040:
041: //other classes
042:
043: public class AdminDatabaseAccessServlet extends BaseConsoleServlet {
044:
045: public AdminDatabaseAccessServlet() {
046: }
047:
048: protected boolean checkAccess(ConsolePageSession inCps) {
049:
050: boolean outValue = false;
051:
052: outValue = inCps.isAdministrator();
053:
054: return outValue;
055: }
056:
057: public ConsolePageContext process(ConsolePageSession inCps,
058: UserHolder inUser, ParameterSet inPs,
059: SubmitActionContext inSac) throws IOException {
060:
061: ConsolePageContext outValue = null;
062:
063: //inCs.setCurrentApplicationWebDocTreeState("");
064:
065: ArrayList darTop = new ArrayList();
066: inCps.setDataAccessResults(darTop);
067:
068: String multiBreak = SubmitActionParameters.TRUE;
069: String rowLimit = "50";
070: String statement = "Statement Here";
071:
072: ConfigService cs = ConfigServiceCallerFactory
073: .getConfigService();
074: ApplicationStoreSet ass = ApplicationStoreSet.newInstance(cs);
075:
076: if (inSac.isAction(SubmitActionParameters.OPEN)) {
077: //MiscHelper.println("Open action executed");
078: } else if (inSac.isAction(SubmitActionParameters.DO_NOTHING)) {
079: } else if (inSac.isAction(SubmitActionParameters.EXECUTE,
080: SubmitActionParameters.STATEMENT)) {
081:
082: //driverClass = inSac.getParameter(inSac.FIRST_INPUT);
083: //connectionString = inSac.getParameter(inSac.SECOND_INPUT);
084: //user = inSac.getParameter(inSac.THIRD_INPUT);
085: //password = inSac.getParameter(inSac.FOURTH_INPUT);
086: //
087: //
088: String database = inSac
089: .getParameter(ConsoleParameterContext.FIRST_INPUT);
090: multiBreak = inSac
091: .getParameter(ConsoleParameterContext.SECOND_INPUT);
092: rowLimit = inSac
093: .getParameter(ConsoleParameterContext.THIRD_INPUT);
094: statement = inSac
095: .getParameter(ConsoleParameterContext.FOURTH_INPUT);
096: //
097: String fileUploadId = inSac
098: .getParameter(ConsoleParameterContext.FIFTH_INPUT);
099:
100: Connection conn = null;
101: //ResultSet columnsRs = null;
102:
103: try {
104:
105: //Class.forName(driverClass);
106: //
107: //conn = DriverManager.getConnection(
108: // connectionString, user, password);
109:
110: String actualDatabaseName = ass.getReferenceHandle(ass
111: .getIndexOfActualName(database));
112:
113: ApplicationDataSourceResolver adsr = SystemStoreFactory
114: .getApplicationDataSourceResolver();
115: conn = SystemStore.getGeneralConnection(adsr
116: .getActualDataSourceName(actualDatabaseName));
117:
118: ArrayList statements = new ArrayList();
119: if (multiBreak.equals(SubmitActionParameters.TRUE)) {
120: statements = separateMultipleStatements(statement,
121: "'", ";");
122: } else {
123: statements.add(statement);
124: }
125:
126: for (int i = 0; i < statements.size(); i++) {
127: String nextStatement = (String) statements.get(i);
128:
129: try {
130: PreparedStatement ps = conn
131: .prepareStatement(nextStatement);
132: //
133: //
134: //
135: if (inPs.isParameterUpload(fileUploadId)) {
136: byte bc[] = inPs
137: .getParameterAsUpload(fileUploadId);
138: ps.setBytes(1, bc);
139: }
140:
141: ArrayList nextDar = new ArrayList();
142: //
143: boolean result = ps.execute();
144: if (result) {
145: //rows returned
146: nextDar.add(nextStatement);
147: //
148: ResultSetMetaData nextRsmd = ps
149: .getMetaData();
150: int nextColumnCount = nextRsmd
151: .getColumnCount();
152: ArrayList nextColumnNames = new ArrayList();
153: for (int j = 0; j < nextColumnCount; j++) {
154: nextColumnNames.add(nextRsmd
155: .getColumnLabel(j + 1));
156: }
157: nextDar.add(nextColumnNames);
158: //
159: ResultSet nextRs = ps.getResultSet();
160: while (nextRs.next()) {
161: ArrayList nextRow = new ArrayList();
162: for (int j = 0; j < nextColumnCount; j++) {
163: nextRow.add(nextRs.getObject(j + 1)
164: + "");
165: }
166: nextDar.add(nextRow);
167: }
168: nextRs.close();
169: //
170: } else {
171: //non-select statement
172: int rowsUpdated = ps.getUpdateCount();
173: nextDar.add(nextStatement + "\n"
174: + rowsUpdated + " rows updated");
175: }
176: //
177: darTop.add(nextDar);
178:
179: ps.close();
180: } catch (SQLException sqle) {
181: ArrayList nextDar = new ArrayList();
182: nextDar.add(nextStatement
183: + "\n"
184: + sqle.getMessage()
185: + "\n"
186: + MiscHelper
187: .getStackTraceAsString(sqle));
188: darTop.add(nextDar);
189: }
190: }
191:
192: }
193: //catch (ClassNotFoundException cnfe) {
194: // ArrayList errorDar = new ArrayList();
195: // errorDar.add(cnfe.getMessage() + "\n"
196: // + MiscHelper.getStackTraceAsString(cnfe));
197: // darTop.clear();
198: // darTop.add(errorDar);
199: //}
200: //catch (SQLException sqle) {
201: // ArrayList errorDar = new ArrayList();
202: // errorDar.add(sqle.getMessage() + "\n"
203: // + MiscHelper.getStackTraceAsString(sqle));
204: // darTop.clear();
205: // darTop.add(errorDar);
206: //}
207: finally {
208: try {
209: if (conn != null) {
210: conn.close();
211: }
212: //if (columnsRs != null) {
213: // columnsRs.close();
214: //}
215: } catch (SQLException sqle) {
216: ArrayList errorDar = new ArrayList();
217: errorDar.add(sqle.getMessage() + "\n"
218: + MiscHelper.getStackTraceAsString(sqle));
219: //darTop.clear();
220: darTop.add(errorDar);
221: }
222: }
223:
224: } else if (inSac.isAction(SubmitActionParameters.EXECUTE,
225: SubmitActionParameters.META_DATA)) {
226:
227: //driverClass = inSac.getParameter(inSac.FIRST_INPUT);
228: //connectionString = inSac.getParameter(inSac.SECOND_INPUT);
229: //user = inSac.getParameter(inSac.THIRD_INPUT);
230: //password = inSac.getParameter(inSac.FOURTH_INPUT);
231: String database = inSac
232: .getParameter(ConsoleParameterContext.FIRST_INPUT);
233: multiBreak = inSac
234: .getParameter(ConsoleParameterContext.SECOND_INPUT);
235: rowLimit = inSac
236: .getParameter(ConsoleParameterContext.THIRD_INPUT);
237: statement = inSac
238: .getParameter(ConsoleParameterContext.FOURTH_INPUT);
239:
240: Connection conn = null;
241: ResultSet columnsRs = null;
242:
243: try {
244:
245: //Class.forName(driverClass);
246: //
247: //conn = DriverManager.getConnection(
248: // connectionString, user, password);
249:
250: String actualDatabaseName = ass.getReferenceHandle(ass
251: .getIndexOfActualName(database));
252:
253: ApplicationDataSourceResolver adsr = SystemStoreFactory
254: .getApplicationDataSourceResolver();
255: MiscHelper.println("AdDataAccServ database = "
256: + adsr.getActualDataSourceName(database));
257: conn = SystemStore.getGeneralConnection(adsr
258: .getActualDataSourceName(actualDatabaseName));
259:
260: HashMap catalogs = new HashMap();
261:
262: DatabaseMetaData dmd = conn.getMetaData();
263:
264: columnsRs = dmd.getColumns(null, null, "%", "%");
265: while (columnsRs.next()) {
266:
267: String nextCatalog = columnsRs
268: .getString("TABLE_CAT");
269: String nextSchema = columnsRs
270: .getString("TABLE_SCHEM");
271: String nextTable = columnsRs
272: .getString("TABLE_NAME");
273:
274: //
275: HashMap schemas = (HashMap) catalogs
276: .get(nextCatalog);
277: if (schemas == null) {
278: schemas = new HashMap();
279: catalogs.put(nextCatalog, schemas);
280: }
281:
282: //
283: HashMap tables = (HashMap) schemas.get(nextSchema);
284: if (tables == null) {
285: tables = new HashMap();
286: schemas.put(nextSchema, tables);
287: }
288:
289: //
290: ArrayList tableDef = (ArrayList) tables
291: .get(nextTable);
292: if (tableDef == null) {
293: tableDef = new ArrayList();
294: tables.put(nextTable, tableDef);
295: //
296: tableDef.add("Catalog - " + nextCatalog
297: + ", Schema - " + nextSchema
298: + ", Table - " + nextTable);
299: //
300: ArrayList columnLabels = new ArrayList();
301: columnLabels.add("Name");
302: columnLabels.add("Type");
303: columnLabels.add("Null Allowed");
304:
305: tableDef.add(columnLabels);
306: }
307:
308: ArrayList rowDef = new ArrayList();
309: rowDef.add(columnsRs.getString("COLUMN_NAME"));
310: rowDef.add(columnsRs.getString("TYPE_NAME"));
311: rowDef.add(columnsRs.getString("IS_NULLABLE"));
312:
313: tableDef.add(rowDef);
314:
315: }
316:
317: Iterator catalogIter = catalogs.keySet().iterator();
318: //MiscHelper.println("catalogIter = " + catalogIter);
319: while (catalogIter.hasNext()) {
320: HashMap nextCatalog = (HashMap) catalogs
321: .get(catalogIter.next());
322: //MiscHelper.println("nextCatalog = " + nextCatalog);
323: Iterator schemaIter = nextCatalog.keySet()
324: .iterator();
325: //MiscHelper.println("schemaIter = " + schemaIter);
326: while (schemaIter.hasNext()) {
327: HashMap nextSchema = (HashMap) nextCatalog
328: .get(schemaIter.next());
329: Iterator tableIter = nextSchema.keySet()
330: .iterator();
331: while (tableIter.hasNext()) {
332: darTop
333: .add(nextSchema.get(tableIter
334: .next()));
335: }
336: }
337: }
338:
339: //conn.close();
340:
341: }
342: //catch (ClassNotFoundException cnfe) {
343: // ArrayList errorDar = new ArrayList();
344: // errorDar.add(cnfe.getMessage() + "\n"
345: // + MiscHelper.getStackTraceAsString(cnfe));
346: // darTop.clear();
347: // darTop.add(errorDar);
348: //}
349: catch (SQLException sqle) {
350: ArrayList errorDar = new ArrayList();
351: errorDar.add(sqle.getMessage() + "\n"
352: + MiscHelper.getStackTraceAsString(sqle));
353: darTop.clear();
354: darTop.add(errorDar);
355: } finally {
356: try {
357: if (conn != null) {
358: conn.close();
359: }
360: if (columnsRs != null) {
361: columnsRs.close();
362: }
363: } catch (SQLException sqle) {
364: ArrayList errorDar = new ArrayList();
365: errorDar.add(sqle.getMessage() + "\n"
366: + MiscHelper.getStackTraceAsString(sqle));
367: //darTop.clear();
368: darTop.add(errorDar);
369: }
370: }
371:
372: } else {
373: inSac.unknownAction();
374: }
375:
376: String driverClass = "org.hsqldb.jdbcDriver";
377: String connectionString = "jdbc:hsqldb:../server/default/data/hypersonic/localDB";
378: String user = "sa";
379: String password = "";
380:
381: outValue = AdminDatabaseAccessContext.newInstance(inCps,
382: driverClass, connectionString, user, password,
383: multiBreak, rowLimit, statement);
384:
385: return outValue;
386: }
387:
388: private final static ArrayList separateMultipleStatements(
389: String inSource, String inQuotes, String inBreaks) {
390:
391: ArrayList outValue = new ArrayList();
392:
393: String tokenSet = inQuotes + inBreaks;
394:
395: StringBuffer nextStatement = new StringBuffer();
396: int quoteCount = 0;
397:
398: StringTokenizer st = new StringTokenizer(inSource, tokenSet,
399: true);
400:
401: while (st.hasMoreTokens()) {
402: String nextToken = st.nextToken();
403:
404: if (inQuotes.indexOf(nextToken) != -1) {
405: quoteCount++;
406: nextStatement.append(nextToken);
407: } else if (inBreaks.indexOf(nextToken) != -1) {
408: if (quoteCount % 2 != 1) {
409: outValue.add(nextStatement.toString());
410: nextStatement = new StringBuffer();
411: } else {
412: nextStatement.append(nextToken);
413: }
414: } else {
415: nextStatement.append(nextToken);
416: }
417: }
418:
419: if (nextStatement.length() > 0) {
420: outValue.add(nextStatement.toString());
421: }
422:
423: return outValue;
424: }
425:
426: }
|