001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. See the NOTICE file distributed with
004: * this work for additional information regarding copyright ownership.
005: * The ASF licenses this file to You under the Apache License, Version 2.0
006: * (the "License"); you may not use this file except in compliance with
007: * the License. You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: *
017: */
018:
019: package org.apache.tools.ant.taskdefs;
020:
021: import org.apache.tools.ant.BuildException;
022: import org.apache.tools.ant.Project;
023: import org.apache.tools.ant.util.FileUtils;
024: import org.apache.tools.ant.util.StringUtils;
025: import org.apache.tools.ant.types.EnumeratedAttribute;
026: import org.apache.tools.ant.types.FileSet;
027: import org.apache.tools.ant.types.Resource;
028: import org.apache.tools.ant.types.ResourceCollection;
029: import org.apache.tools.ant.types.resources.FileResource;
030: import org.apache.tools.ant.types.resources.Union;
031:
032: import java.io.File;
033: import java.io.PrintStream;
034: import java.io.BufferedOutputStream;
035: import java.io.FileOutputStream;
036: import java.io.IOException;
037: import java.io.Reader;
038: import java.io.BufferedReader;
039: import java.io.StringReader;
040: import java.io.InputStream;
041: import java.io.InputStreamReader;
042: import java.util.Enumeration;
043: import java.util.Iterator;
044: import java.util.StringTokenizer;
045: import java.util.Vector;
046:
047: import java.sql.Connection;
048: import java.sql.Statement;
049: import java.sql.SQLException;
050: import java.sql.SQLWarning;
051: import java.sql.ResultSet;
052: import java.sql.ResultSetMetaData;
053:
054: /**
055: * Executes a series of SQL statements on a database using JDBC.
056: *
057: * <p>Statements can
058: * either be read in from a text file using the <i>src</i> attribute or from
059: * between the enclosing SQL tags.</p>
060: *
061: * <p>Multiple statements can be provided, separated by semicolons (or the
062: * defined <i>delimiter</i>). Individual lines within the statements can be
063: * commented using either --, // or REM at the start of the line.</p>
064: *
065: * <p>The <i>autocommit</i> attribute specifies whether auto-commit should be
066: * turned on or off whilst executing the statements. If auto-commit is turned
067: * on each statement will be executed and committed. If it is turned off the
068: * statements will all be executed as one transaction.</p>
069: *
070: * <p>The <i>onerror</i> attribute specifies how to proceed when an error occurs
071: * during the execution of one of the statements.
072: * The possible values are: <b>continue</b> execution, only show the error;
073: * <b>stop</b> execution and commit transaction;
074: * and <b>abort</b> execution and transaction and fail task.</p>
075: *
076: * @since Ant 1.2
077: *
078: * @ant.task name="sql" category="database"
079: */
080: public class SQLExec extends JDBCTask {
081:
082: /**
083: * delimiters we support, "normal" and "row"
084: */
085: public static class DelimiterType extends EnumeratedAttribute {
086: /** The enumerated strings */
087: public static final String NORMAL = "normal", ROW = "row";
088:
089: /** @return the enumerated strings */
090: public String[] getValues() {
091: return new String[] { NORMAL, ROW };
092: }
093: }
094:
095: private int goodSql = 0;
096:
097: private int totalSql = 0;
098:
099: /**
100: * Database connection
101: */
102: private Connection conn = null;
103:
104: /**
105: * files to load
106: */
107: private Union resources = new Union();
108:
109: /**
110: * SQL statement
111: */
112: private Statement statement = null;
113:
114: /**
115: * SQL input file
116: */
117: private File srcFile = null;
118:
119: /**
120: * SQL input command
121: */
122: private String sqlCommand = "";
123:
124: /**
125: * SQL transactions to perform
126: */
127: private Vector transactions = new Vector();
128:
129: /**
130: * SQL Statement delimiter
131: */
132: private String delimiter = ";";
133:
134: /**
135: * The delimiter type indicating whether the delimiter will
136: * only be recognized on a line by itself
137: */
138: private String delimiterType = DelimiterType.NORMAL;
139:
140: /**
141: * Print SQL results.
142: */
143: private boolean print = false;
144:
145: /**
146: * Print header columns.
147: */
148: private boolean showheaders = true;
149:
150: /**
151: * Print SQL stats (rows affected)
152: */
153: private boolean showtrailers = true;
154:
155: /**
156: * Results Output file.
157: */
158: private File output = null;
159:
160: /**
161: * Action to perform if an error is found
162: **/
163: private String onError = "abort";
164:
165: /**
166: * Encoding to use when reading SQL statements from a file
167: */
168: private String encoding = null;
169:
170: /**
171: * Append to an existing file or overwrite it?
172: */
173: private boolean append = false;
174:
175: /**
176: * Keep the format of a sql block?
177: */
178: private boolean keepformat = false;
179:
180: /**
181: * Argument to Statement.setEscapeProcessing
182: *
183: * @since Ant 1.6
184: */
185: private boolean escapeProcessing = true;
186:
187: /**
188: * should properties be expanded in text?
189: * false for backwards compatibility
190: *
191: * @since Ant 1.7
192: */
193: private boolean expandProperties = false;
194:
195: /**
196: * Set the name of the SQL file to be run.
197: * Required unless statements are enclosed in the build file
198: * @param srcFile the file containing the SQL command.
199: */
200: public void setSrc(File srcFile) {
201: this .srcFile = srcFile;
202: }
203:
204: /**
205: * Enable property expansion inside nested text
206: *
207: * @param expandProperties if true expand properties.
208: * @since Ant 1.7
209: */
210: public void setExpandProperties(boolean expandProperties) {
211: this .expandProperties = expandProperties;
212: }
213:
214: /**
215: * is property expansion inside inline text enabled?
216: *
217: * @return true if properties are to be expanded.
218: * @since Ant 1.7
219: */
220: public boolean getExpandProperties() {
221: return expandProperties;
222: }
223:
224: /**
225: * Set an inline SQL command to execute.
226: * NB: Properties are not expanded in this text unless {@link #expandProperties}
227: * is set.
228: * @param sql an inline string containing the SQL command.
229: */
230: public void addText(String sql) {
231: //there is no need to expand properties here as that happens when Transaction.addText is
232: //called; to do so here would be an error.
233: this .sqlCommand += sql;
234: }
235:
236: /**
237: * Adds a set of files (nested fileset attribute).
238: * @param set a set of files contains SQL commands, each File is run in
239: * a separate transaction.
240: */
241: public void addFileset(FileSet set) {
242: add(set);
243: }
244:
245: /**
246: * Adds a collection of resources (nested element).
247: * @param rc a collection of resources containing SQL commands,
248: * each resource is run in a separate transaction.
249: * @since Ant 1.7
250: */
251: public void add(ResourceCollection rc) {
252: resources.add(rc);
253: }
254:
255: /**
256: * Add a SQL transaction to execute
257: * @return a Transaction to be configured.
258: */
259: public Transaction createTransaction() {
260: Transaction t = new Transaction();
261: transactions.addElement(t);
262: return t;
263: }
264:
265: /**
266: * Set the file encoding to use on the SQL files read in
267: *
268: * @param encoding the encoding to use on the files
269: */
270: public void setEncoding(String encoding) {
271: this .encoding = encoding;
272: }
273:
274: /**
275: * Set the delimiter that separates SQL statements. Defaults to ";";
276: * optional
277: *
278: * <p>For example, set this to "go" and delimitertype to "ROW" for
279: * Sybase ASE or MS SQL Server.</p>
280: * @param delimiter the separator.
281: */
282: public void setDelimiter(String delimiter) {
283: this .delimiter = delimiter;
284: }
285:
286: /**
287: * Set the delimiter type: "normal" or "row" (default "normal").
288: *
289: * <p>The delimiter type takes two values - normal and row. Normal
290: * means that any occurrence of the delimiter terminate the SQL
291: * command whereas with row, only a line containing just the
292: * delimiter is recognized as the end of the command.</p>
293: * @param delimiterType the type of delimiter - "normal" or "row".
294: */
295: public void setDelimiterType(DelimiterType delimiterType) {
296: this .delimiterType = delimiterType.getValue();
297: }
298:
299: /**
300: * Print result sets from the statements;
301: * optional, default false
302: * @param print if true print result sets.
303: */
304: public void setPrint(boolean print) {
305: this .print = print;
306: }
307:
308: /**
309: * Print headers for result sets from the
310: * statements; optional, default true.
311: * @param showheaders if true print headers of result sets.
312: */
313: public void setShowheaders(boolean showheaders) {
314: this .showheaders = showheaders;
315: }
316:
317: /**
318: * Print trailing info (rows affected) for the SQL
319: * Addresses Bug/Request #27446
320: * @param showtrailers if true prints the SQL rows affected
321: * @since Ant 1.7
322: */
323: public void setShowtrailers(boolean showtrailers) {
324: this .showtrailers = showtrailers;
325: }
326:
327: /**
328: * Set the output file;
329: * optional, defaults to the Ant log.
330: * @param output the output file to use for logging messages.
331: */
332: public void setOutput(File output) {
333: this .output = output;
334: }
335:
336: /**
337: * whether output should be appended to or overwrite
338: * an existing file. Defaults to false.
339: *
340: * @since Ant 1.5
341: * @param append if true append to an existing file.
342: */
343: public void setAppend(boolean append) {
344: this .append = append;
345: }
346:
347: /**
348: * Action to perform when statement fails: continue, stop, or abort
349: * optional; default "abort"
350: * @param action the action to perform on statement failure.
351: */
352: public void setOnerror(OnError action) {
353: this .onError = action.getValue();
354: }
355:
356: /**
357: * whether or not format should be preserved.
358: * Defaults to false.
359: *
360: * @param keepformat The keepformat to set
361: */
362: public void setKeepformat(boolean keepformat) {
363: this .keepformat = keepformat;
364: }
365:
366: /**
367: * Set escape processing for statements.
368: * @param enable if true enable escape processing, default is true.
369: * @since Ant 1.6
370: */
371: public void setEscapeProcessing(boolean enable) {
372: escapeProcessing = enable;
373: }
374:
375: /**
376: * Load the sql file and then execute it
377: * @throws BuildException on error.
378: */
379: public void execute() throws BuildException {
380: Vector savedTransaction = (Vector) transactions.clone();
381: String savedSqlCommand = sqlCommand;
382:
383: sqlCommand = sqlCommand.trim();
384:
385: try {
386: if (srcFile == null && sqlCommand.length() == 0
387: && resources.size() == 0) {
388: if (transactions.size() == 0) {
389: throw new BuildException("Source file or resource "
390: + "collection, "
391: + "transactions or sql statement "
392: + "must be set!", getLocation());
393: }
394: }
395:
396: if (srcFile != null && !srcFile.exists()) {
397: throw new BuildException("Source file does not exist!",
398: getLocation());
399: }
400:
401: // deal with the resources
402: Iterator iter = resources.iterator();
403: while (iter.hasNext()) {
404: Resource r = (Resource) iter.next();
405: // Make a transaction for each resource
406: Transaction t = createTransaction();
407: t.setSrcResource(r);
408: }
409:
410: // Make a transaction group for the outer command
411: Transaction t = createTransaction();
412: t.setSrc(srcFile);
413: t.addText(sqlCommand);
414: conn = getConnection();
415: if (!isValidRdbms(conn)) {
416: return;
417: }
418: try {
419: statement = conn.createStatement();
420: statement.setEscapeProcessing(escapeProcessing);
421:
422: PrintStream out = System.out;
423: try {
424: if (output != null) {
425: log("Opening PrintStream to output file "
426: + output, Project.MSG_VERBOSE);
427: out = new PrintStream(new BufferedOutputStream(
428: new FileOutputStream(output
429: .getAbsolutePath(), append)));
430: }
431:
432: // Process all transactions
433: for (Enumeration e = transactions.elements(); e
434: .hasMoreElements();) {
435:
436: ((Transaction) e.nextElement())
437: .runTransaction(out);
438: if (!isAutocommit()) {
439: log("Committing transaction",
440: Project.MSG_VERBOSE);
441: conn.commit();
442: }
443: }
444: } finally {
445: if (out != null && out != System.out) {
446: out.close();
447: }
448: }
449: } catch (IOException e) {
450: closeQuietly();
451: throw new BuildException(e, getLocation());
452: } catch (SQLException e) {
453: closeQuietly();
454: throw new BuildException(e, getLocation());
455: } finally {
456: try {
457: if (statement != null) {
458: statement.close();
459: }
460: if (conn != null) {
461: conn.close();
462: }
463: } catch (SQLException ex) {
464: // ignore
465: }
466: }
467:
468: log(goodSql + " of " + totalSql
469: + " SQL statements executed successfully");
470: } finally {
471: transactions = savedTransaction;
472: sqlCommand = savedSqlCommand;
473: }
474: }
475:
476: /**
477: * read in lines and execute them
478: * @param reader the reader contains sql lines.
479: * @param out the place to output results.
480: * @throws SQLException on sql problems
481: * @throws IOException on io problems
482: */
483: protected void runStatements(Reader reader, PrintStream out)
484: throws SQLException, IOException {
485: StringBuffer sql = new StringBuffer();
486: String line;
487:
488: BufferedReader in = new BufferedReader(reader);
489:
490: while ((line = in.readLine()) != null) {
491: if (!keepformat) {
492: line = line.trim();
493: }
494: line = getProject().replaceProperties(line);
495: if (!keepformat) {
496: if (line.startsWith("//")) {
497: continue;
498: }
499: if (line.startsWith("--")) {
500: continue;
501: }
502: StringTokenizer st = new StringTokenizer(line);
503: if (st.hasMoreTokens()) {
504: String token = st.nextToken();
505: if ("REM".equalsIgnoreCase(token)) {
506: continue;
507: }
508: }
509: }
510:
511: if (!keepformat) {
512: sql.append(" ");
513: sql.append(line);
514: } else {
515: sql.append("\n");
516: sql.append(line);
517: }
518:
519: // SQL defines "--" as a comment to EOL
520: // and in Oracle it may contain a hint
521: // so we cannot just remove it, instead we must end it
522: if (!keepformat) {
523: if (line.indexOf("--") >= 0) {
524: sql.append("\n");
525: }
526: }
527: if ((delimiterType.equals(DelimiterType.NORMAL) && StringUtils
528: .endsWith(sql, delimiter))
529: || (delimiterType.equals(DelimiterType.ROW) && line
530: .equals(delimiter))) {
531: execSQL(sql.substring(0, sql.length()
532: - delimiter.length()), out);
533: sql.replace(0, sql.length(), "");
534: }
535: }
536: // Catch any statements not followed by ;
537: if (sql.length() > 0) {
538: execSQL(sql.toString(), out);
539: }
540: }
541:
542: /**
543: * Exec the sql statement.
544: * @param sql the SQL statement to execute
545: * @param out the place to put output
546: * @throws SQLException on SQL problems
547: */
548: protected void execSQL(String sql, PrintStream out)
549: throws SQLException {
550: // Check and ignore empty statements
551: if ("".equals(sql.trim())) {
552: return;
553: }
554:
555: ResultSet resultSet = null;
556: try {
557: totalSql++;
558: log("SQL: " + sql, Project.MSG_VERBOSE);
559:
560: boolean ret;
561: int updateCount = 0, updateCountTotal = 0;
562:
563: ret = statement.execute(sql);
564: updateCount = statement.getUpdateCount();
565: resultSet = statement.getResultSet();
566: do {
567: if (!ret) {
568: if (updateCount != -1) {
569: updateCountTotal += updateCount;
570: }
571: } else {
572: if (print) {
573: printResults(resultSet, out);
574: }
575: }
576: ret = statement.getMoreResults();
577: if (ret) {
578: updateCount = statement.getUpdateCount();
579: resultSet = statement.getResultSet();
580: }
581: } while (ret);
582:
583: log(updateCountTotal + " rows affected",
584: Project.MSG_VERBOSE);
585:
586: if (print && showtrailers) {
587: out.println(updateCountTotal + " rows affected");
588: }
589:
590: SQLWarning warning = conn.getWarnings();
591: while (warning != null) {
592: log(warning + " sql warning", Project.MSG_VERBOSE);
593: warning = warning.getNextWarning();
594: }
595: conn.clearWarnings();
596: goodSql++;
597: } catch (SQLException e) {
598: log("Failed to execute: " + sql, Project.MSG_ERR);
599: if (!onError.equals("continue")) {
600: throw e;
601: }
602: log(e.toString(), Project.MSG_ERR);
603: } finally {
604: if (resultSet != null) {
605: resultSet.close();
606: }
607: }
608: }
609:
610: /**
611: * print any results in the statement
612: * @deprecated since 1.6.x.
613: * Use {@link #printResults(java.sql.ResultSet, java.io.PrintStream)
614: * the two arg version} instead.
615: * @param out the place to print results
616: * @throws SQLException on SQL problems.
617: */
618: protected void printResults(PrintStream out) throws SQLException {
619: ResultSet rs = statement.getResultSet();
620: try {
621: printResults(rs, out);
622: } finally {
623: if (rs != null) {
624: rs.close();
625: }
626: }
627: }
628:
629: /**
630: * print any results in the result set.
631: * @param rs the resultset to print information about
632: * @param out the place to print results
633: * @throws SQLException on SQL problems.
634: * @since Ant 1.6.3
635: */
636: protected void printResults(ResultSet rs, PrintStream out)
637: throws SQLException {
638: if (rs != null) {
639: log("Processing new result set.", Project.MSG_VERBOSE);
640: ResultSetMetaData md = rs.getMetaData();
641: int columnCount = md.getColumnCount();
642: StringBuffer line = new StringBuffer();
643: if (showheaders) {
644: for (int col = 1; col < columnCount; col++) {
645: line.append(md.getColumnName(col));
646: line.append(",");
647: }
648: line.append(md.getColumnName(columnCount));
649: out.println(line);
650: line = new StringBuffer();
651: }
652: while (rs.next()) {
653: boolean first = true;
654: for (int col = 1; col <= columnCount; col++) {
655: String columnValue = rs.getString(col);
656: if (columnValue != null) {
657: columnValue = columnValue.trim();
658: }
659:
660: if (first) {
661: first = false;
662: } else {
663: line.append(",");
664: }
665: line.append(columnValue);
666: }
667: out.println(line);
668: line = new StringBuffer();
669: }
670: }
671: out.println();
672: }
673:
674: /*
675: * Closes an unused connection after an error and doesn't rethrow
676: * a possible SQLException
677: * @since Ant 1.7
678: */
679: private void closeQuietly() {
680: if (!isAutocommit() && conn != null && onError.equals("abort")) {
681: try {
682: conn.rollback();
683: } catch (SQLException ex) {
684: // ignore
685: }
686: }
687: }
688:
689: /**
690: * The action a task should perform on an error,
691: * one of "continue", "stop" and "abort"
692: */
693: public static class OnError extends EnumeratedAttribute {
694: /** @return the enumerated values */
695: public String[] getValues() {
696: return new String[] { "continue", "stop", "abort" };
697: }
698: }
699:
700: /**
701: * Contains the definition of a new transaction element.
702: * Transactions allow several files or blocks of statements
703: * to be executed using the same JDBC connection and commit
704: * operation in between.
705: */
706: public class Transaction {
707: private Resource tSrcResource = null;
708: private String tSqlCommand = "";
709:
710: /**
711: * Set the source file attribute.
712: * @param src the source file
713: */
714: public void setSrc(File src) {
715: //there are places (in this file, and perhaps elsewhere, where it is assumed
716: //that null is an acceptable parameter.
717: if (src != null) {
718: setSrcResource(new FileResource(src));
719: }
720: }
721:
722: /**
723: * Set the source resource attribute.
724: * @param src the source file
725: * @since Ant 1.7
726: */
727: public void setSrcResource(Resource src) {
728: if (tSrcResource != null) {
729: throw new BuildException(
730: "only one resource per transaction");
731: }
732: tSrcResource = src;
733: }
734:
735: /**
736: * Set inline text
737: * @param sql the inline text
738: */
739: public void addText(String sql) {
740: if (sql != null) {
741: if (getExpandProperties()) {
742: sql = getProject().replaceProperties(sql);
743: }
744: this .tSqlCommand += sql;
745: }
746: }
747:
748: /**
749: * Set the source resource.
750: * @param a the source resource collection.
751: * @since Ant 1.7
752: */
753: public void addConfigured(ResourceCollection a) {
754: if (a.size() != 1) {
755: throw new BuildException(
756: "only single argument resource "
757: + "collections are supported.");
758: }
759: setSrcResource((Resource) a.iterator().next());
760: }
761:
762: /**
763: *
764: */
765: private void runTransaction(PrintStream out)
766: throws IOException, SQLException {
767: if (tSqlCommand.length() != 0) {
768: log("Executing commands", Project.MSG_INFO);
769: runStatements(new StringReader(tSqlCommand), out);
770: }
771:
772: if (tSrcResource != null) {
773: log("Executing resource: " + tSrcResource.toString(),
774: Project.MSG_INFO);
775: InputStream is = null;
776: Reader reader = null;
777: try {
778: is = tSrcResource.getInputStream();
779: reader = (encoding == null) ? new InputStreamReader(
780: is)
781: : new InputStreamReader(is, encoding);
782: runStatements(reader, out);
783: } finally {
784: FileUtils.close(is);
785: FileUtils.close(reader);
786: }
787: }
788: }
789: }
790: }
|