001: /**
002: * Objective Database Abstraction Layer (ODAL)
003: * Copyright (c) 2004, The ODAL Development Group
004: * All rights reserved.
005: * For definition of the ODAL Development Group please refer to LICENCE.txt file
006: *
007: * Distributable under LGPL license.
008: * See terms of license at gnu.org.
009: */package com.completex.objective.tools;
010:
011: import com.completex.objective.components.OdalRuntimeException;
012: import com.completex.objective.components.log.Log;
013: import com.completex.objective.components.persistency.Persistency;
014: import com.completex.objective.components.persistency.core.adapter.DefaultPersistencyAdapter;
015: import com.completex.objective.components.persistency.transact.Transaction;
016: import com.completex.objective.util.PropertyMap;
017: import com.completex.objective.util.StringUtil;
018:
019: import java.io.BufferedReader;
020: import java.io.FileInputStream;
021: import java.io.FileReader;
022: import java.io.IOException;
023: import java.io.InputStreamReader;
024: import java.io.PrintWriter;
025: import java.sql.Connection;
026: import java.sql.ResultSet;
027: import java.sql.ResultSetMetaData;
028: import java.sql.SQLException;
029: import java.sql.Statement;
030: import java.util.Properties;
031:
032: /**
033: * Executes any sql command or script either as a command line tool or as a Java class through its
034: * <t>processInput()</t> method. Execute it with no argument to get help.
035: * Commands are separated by line with only "/" in it.
036: *
037: * @author Gennady Krizhevsky
038: */
039: public class SqlCmdTool {
040:
041: public static final String NL = System
042: .getProperty("line.separator");
043: private static String[] SINGLE_LINE_COMMENTS = new String[] { "//",
044: "#", "--" };
045:
046: // public static final String END_OF_LINE = ";";
047: public static final String COMMAND_SEP_LINE = "/";
048:
049: private static PrintWriter logWriter = new PrintWriter(System.out,
050: true);
051: private static PrintWriter errorLogWriter = new PrintWriter(
052: System.out, true);
053: public static final NullStatementProcessorImpl NULL_STATEMENT_PROCESSOR = new NullStatementProcessorImpl();
054:
055: private StatementProcessor statementProcessor;
056: public static final String PROMPT = ">";
057:
058: private Log logger = Log.NULL_LOGGER;
059: /**
060: * Commands:
061: */
062: public static final String CMD_COMMIT = "commit";
063: public static final String CMD_ROLLBACK = "rollback";
064: public static final String CMD_EXIT = "exit";
065: public static final String CMD_QUIT = "quit";
066: public static final String CMD_HELP = "help";
067: public static final String SCRIPT_PREFIX = "@";
068: public static final String SCRIPT_SUFFIX = ";";
069:
070: public SqlCmdTool(StatementProcessor statementProcessor) {
071: this .statementProcessor = statementProcessor;
072: }
073:
074: public SqlCmdTool(Properties properties) throws SQLException {
075: this (properties, Log.NULL_LOGGER);
076: }
077:
078: public SqlCmdTool(Properties properties, Log logger)
079: throws SQLException {
080: setLogger(logger);
081: Persistency persistency = new DefaultPersistencyAdapter(
082: properties, getLogger());
083: this .statementProcessor = new StatementProcessorImpl(
084: persistency, getLogger());
085:
086: }
087:
088: protected void processInput() throws IOException {
089: BufferedReader reader = new BufferedReader(
090: new InputStreamReader(System.in));
091: printPrompt();
092: StringBuffer command = null;
093: String line;
094: try {
095: while ((line = reader.readLine()) != null) {
096: printPrompt();
097: if (command == null) {
098: command = new StringBuffer();
099: }
100: command = processLine0(line, command,
101: statementProcessor);
102: }
103: } catch (BreakException e) {
104: // Quit is issued
105: }
106: close();
107: reader.close();
108: }
109:
110: protected StringBuffer processLine0(String line,
111: StringBuffer command, StatementProcessor statementProcessor)
112: throws BreakException {
113: if (isToSkip(line)) {
114: //Do nothing
115: } else if (isExit(line)) {
116: statementProcessor.processExit(line);
117: } else if (isHelp(line)) {
118: processHelp();
119: } else if ((line = line.trim()).startsWith(SCRIPT_PREFIX)) {
120: try {
121: processScript(line, statementProcessor);
122: } finally {
123: printPrompt("\n");
124: }
125: } else if (line.equals(COMMAND_SEP_LINE)) {
126: String commandString = command.toString().trim();
127: statementProcessor.processExit(commandString);
128: try {
129: if (!statementProcessor
130: .processCommitOrRollback(commandString)) {
131: statementProcessor.processStatement(command);
132: }
133: } catch (Exception e) {
134: handleException(e, command);
135: } finally {
136: command = null;
137: }
138: } else {
139: command.append(line);
140: command.append(" ");
141: }
142: return command;
143: }
144:
145: private void processHelp() {
146: System.out.println(helpString());
147: printPrompt();
148: }
149:
150: private boolean isHelp(String line) {
151: return line.trim().toLowerCase().startsWith(CMD_HELP);
152: }
153:
154: public synchronized void processScript(String scriptPath)
155: throws Exception {
156: if (scriptPath == null) {
157: throw new IllegalArgumentException("scriptPath == null");
158: }
159: if (!scriptPath.startsWith(SCRIPT_PREFIX)) {
160: scriptPath = SCRIPT_PREFIX + scriptPath;
161: }
162: ensureOpen();
163: processScript(scriptPath, statementProcessor);
164: }
165:
166: public synchronized void close() {
167: if (statementProcessor != null) {
168: statementProcessor.close();
169: statementProcessor = null;
170: }
171: }
172:
173: protected void processScript(String scriptPath,
174: StatementProcessor statementProcessor) {
175: if (scriptPath == null) {
176: throw new OdalRuntimeException("scriptPath == null");
177: } else if (!scriptPath.startsWith(SCRIPT_PREFIX)) {
178: throw new OdalRuntimeException(
179: "scriptPath does not start with @");
180: }
181: StringBuffer scriptCommand = new StringBuffer();
182: StringBuffer command = null;
183: try {
184: int lastPos = scriptPath.endsWith(SCRIPT_SUFFIX) ? scriptPath
185: .length() - 1
186: : scriptPath.length();
187: scriptPath = scriptPath.substring(1, lastPos);
188: scriptCommand.append(scriptPath);
189: BufferedReader reader = new BufferedReader(new FileReader(
190: scriptPath));
191:
192: String line;
193:
194: while ((line = reader.readLine()) != null) {
195: if (command == null) {
196: command = new StringBuffer();
197: }
198: command = processLine0(line, command,
199: statementProcessor);
200: }
201: } catch (Exception e) {
202: if (command != null) {
203: scriptCommand.append(" : ").append(command);
204: }
205: handleException(e, scriptCommand);
206: }
207: }
208:
209: protected boolean isToSkip(String line) {
210: if (StringUtil.isEmpty(line)) {
211: return true;
212: } else {
213: for (int i = 0; i < SINGLE_LINE_COMMENTS.length; i++) {
214: String str = SINGLE_LINE_COMMENTS[i];
215: if (line.startsWith(str)) {
216: return true;
217: }
218: }
219: }
220: return false;
221: }
222:
223: protected static PropertyMap initProperties(String configPath)
224: throws IOException {
225: PropertyMap properties = new PropertyMap();
226: properties.load(new FileInputStream(configPath));
227: return properties;
228: }
229:
230: protected static void printPrompt() {
231: System.out.print(PROMPT);
232: }
233:
234: protected static void printPrompt(String prefix) {
235: prefix = prefix == null ? "" : prefix;
236: System.out.print(prefix + PROMPT);
237: }
238:
239: protected static String helpString() {
240: String help = "Commands: any sql command terminated with ';'"
241: + NL;
242: help += " exit - end session" + NL;
243: help += " quit - end session" + NL;
244: help += " @<script name>; - execute script" + NL;
245:
246: String comments = "";
247: for (int i = 0; i < SINGLE_LINE_COMMENTS.length; i++) {
248: String s = SINGLE_LINE_COMMENTS[i];
249: String comma = i == 0 ? "" : ",";
250: comments += comma + "'" + s + "' ";
251: }
252: help += " " + comments
253: + " - are supported single line comments";
254: return help;
255: }
256:
257: protected static void pringUsage() {
258: System.err
259: .println("Usage: com.completex.objective.tools.SqlCmdTool <jdbc.properties path>");
260: System.err.println(helpString());
261: }
262:
263: private static void print(Object o) {
264: System.out.print(o);
265: }
266:
267: private static void println(Object o, PrintWriter writer) {
268: writer.println(o);
269: }
270:
271: protected static void handleException(Exception e,
272: StringBuffer command) {
273: e.fillInStackTrace();
274: println("Error executing: " + command, errorLogWriter);
275: println(e, errorLogWriter);
276: printPrompt("\n");
277: }
278:
279: protected static boolean isExit(String exactCommand) {
280: return CMD_EXIT.equalsIgnoreCase(exactCommand)
281: || CMD_QUIT.equalsIgnoreCase(exactCommand);
282: }
283:
284: public boolean isOpen() {
285: return !isClosed();
286: }
287:
288: public boolean isClosed() {
289: return statementProcessor == null;
290: }
291:
292: protected void ensureOpen() {
293: if (!isOpen()) {
294: throw new IllegalArgumentException(
295: "Tool is closed - to use it you have to re-instatiate it");
296: }
297: }
298:
299: public Log getLogger() {
300: return logger;
301: }
302:
303: protected void setLogger(Log logger) {
304: if (logger != null) {
305: this .logger = logger;
306: }
307: }
308:
309: public void commit() throws SQLException {
310: ensureOpen();
311: statementProcessor.processCommitOrRollback(CMD_COMMIT);
312: }
313:
314: public void rollback() throws SQLException {
315: ensureOpen();
316: statementProcessor.processCommitOrRollback(CMD_ROLLBACK);
317: }
318:
319: public static void main(String[] args) throws Exception {
320: SqlCmdTool sqlCmdTool;
321: if (args.length < 1) {
322: pringUsage();
323: } else if ("--help".equals(args[0]) || "-h".equals(args[0])
324: || "help".equals(args[0])) {
325: pringUsage();
326: } else if ("test".equals(args[0])) {
327: sqlCmdTool = new SqlCmdTool(NULL_STATEMENT_PROCESSOR);
328: sqlCmdTool.processInput();
329: } else {
330: PropertyMap properties = initProperties(args[0]);
331: sqlCmdTool = new SqlCmdTool(properties);
332: sqlCmdTool.processInput();
333: }
334: }
335:
336: //
337: //
338: // Utils:
339: //
340: //
341: static interface StatementProcessor {
342: void processStatement(StringBuffer command) throws Exception;
343:
344: boolean processCommitOrRollback(String exactCommand)
345: throws SQLException;
346:
347: void close();
348:
349: void processExit(String exactCommand) throws BreakException;
350: }
351:
352: static class StatementProcessorImpl implements StatementProcessor {
353: private Persistency persistency;
354: private Transaction transaction = null;
355: private Log logger = Log.NULL_LOGGER;
356:
357: public StatementProcessorImpl(Persistency persistency,
358: Log logger) throws SQLException {
359: setLogger(logger);
360: this .persistency = persistency;
361: this .transaction = persistency.getTransactionManager()
362: .begin();
363: }
364:
365: public Log getLogger() {
366: return logger;
367: }
368:
369: public void setLogger(Log logger) {
370: if (logger != null) {
371: this .logger = logger;
372: }
373: }
374:
375: public void processStatement(StringBuffer command)
376: throws Exception {
377: try {
378: processStatement0(command, transaction.getConnection());
379: } catch (Exception e) {
380: handleException(e, command);
381: }
382: }
383:
384: public void close() {
385: if (transaction != null) {
386: try {
387: persistency.getTransactionManager().rollback(
388: transaction);
389: } catch (Exception e) {
390: // Ignore
391: }
392: }
393: try {
394: persistency.getTransactionManager().shutdown();
395: } catch (Exception e) {
396: // Ignore
397: }
398: }
399:
400: void processStatement0(StringBuffer command, Connection conn)
401: throws SQLException {
402: Statement statement = conn.createStatement();
403: try {
404: String sql = command.toString();
405: getLogger().debug("sql: " + sql);
406: boolean hasResults = statement.execute(sql);
407:
408: ResultSet rs = statement.getResultSet();
409: if (hasResults && rs != null) {
410: ResultSetMetaData md = rs.getMetaData();
411: int cols = md.getColumnCount();
412: for (int i = 0; i < cols; i++) {
413: String name = md.getColumnName(i + 1);
414: print(name + "\t");
415: }
416: println("", logWriter);
417: while (rs.next()) {
418: for (int i = 0; i < cols; i++) {
419: String value = rs.getString(i + 1);
420: print(value + "\t");
421: }
422: println("", logWriter);
423: }
424: printPrompt();
425: }
426: } finally {
427: if (statement != null) {
428: try {
429: statement.close();
430: } catch (Exception e) {
431: // Ignore
432: }
433: }
434:
435: }
436: }
437:
438: public boolean processCommitOrRollback(String exactCommand)
439: throws SQLException {
440: if (CMD_COMMIT.equalsIgnoreCase(exactCommand)) {
441: commit();
442: return true;
443: } else if (CMD_ROLLBACK.equalsIgnoreCase(exactCommand)) {
444: rollback();
445: return true;
446: } else {
447: return false;
448: }
449: }
450:
451: private void rollback() throws SQLException {
452: transaction.rollback();
453: }
454:
455: private void commit() throws SQLException {
456: transaction.commit();
457: }
458:
459: public void processExit(String exactCommand)
460: throws BreakException {
461: if (isExit(exactCommand)) {
462: throw new BreakException();
463: }
464: }
465: }
466:
467: static class NullStatementProcessorImpl implements
468: StatementProcessor {
469:
470: PrintWriter logWriter = new PrintWriter(System.out);
471:
472: public void processStatement(StringBuffer command) {
473: println(command, logWriter);
474: }
475:
476: public boolean processCommitOrRollback(String exactCommand)
477: throws SQLException {
478: if (CMD_COMMIT.equalsIgnoreCase(exactCommand)
479: || CMD_ROLLBACK.equalsIgnoreCase(exactCommand)) {
480: println(exactCommand, logWriter);
481: return true;
482: } else {
483: return false;
484: }
485: }
486:
487: public void processExit(String exactCommand)
488: throws BreakException {
489: if (isExit(exactCommand)) {
490: println(exactCommand, logWriter);
491: throw new BreakException();
492: }
493: }
494:
495: public void close() {
496: logWriter.flush();
497: }
498: }
499:
500: static class BreakException extends Exception {
501: public BreakException() {
502: }
503: }
504:
505: }
|