001: package org.apache.torque.task;
002:
003: /*
004: * Licensed to the Apache Software Foundation (ASF) under one
005: * or more contributor license agreements. See the NOTICE file
006: * distributed with this work for additional information
007: * regarding copyright ownership. The ASF licenses this file
008: * to you under the Apache License, Version 2.0 (the
009: * "License"); you may not use this file except in compliance
010: * with the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
017: * KIND, either express or implied. See the License for the
018: * specific language governing permissions and limitations
019: * under the License.
020: */
021:
022: import java.io.BufferedOutputStream;
023: import java.io.BufferedReader;
024: import java.io.File;
025: import java.io.FileInputStream;
026: import java.io.FileOutputStream;
027: import java.io.FileReader;
028: import java.io.IOException;
029: import java.io.InputStreamReader;
030: import java.io.PrintStream;
031: import java.io.Reader;
032: import java.io.StringReader;
033: import java.sql.Connection;
034: import java.sql.DatabaseMetaData;
035: import java.sql.Driver;
036: import java.sql.ResultSet;
037: import java.sql.ResultSetMetaData;
038: import java.sql.SQLException;
039: import java.sql.SQLWarning;
040: import java.sql.Statement;
041: import java.util.ArrayList;
042: import java.util.HashMap;
043: import java.util.Iterator;
044: import java.util.List;
045: import java.util.Map;
046: import java.util.Properties;
047:
048: import org.apache.commons.lang.StringUtils;
049: import org.apache.tools.ant.AntClassLoader;
050: import org.apache.tools.ant.BuildException;
051: import org.apache.tools.ant.Project;
052: import org.apache.tools.ant.PropertyHelper;
053: import org.apache.tools.ant.Task;
054: import org.apache.tools.ant.types.EnumeratedAttribute;
055: import org.apache.tools.ant.types.Path;
056: import org.apache.tools.ant.types.Reference;
057:
058: /**
059: * This task uses an SQL -> Database map in the form of a properties
060: * file to insert each SQL file listed into its designated database.
061: *
062: * @author <a href="mailto:jeff@custommonkey.org">Jeff Martin</a>
063: * @author <a href="mailto:gholam@xtra.co.nz">Michael McCallum</A>
064: * @author <a href="mailto:tim.stephenson@sybase.com">Tim Stephenson</A>
065: * @author <a href="mailto:jvanzyl@apache.org">Jason van Zyl</A>
066: * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
067: * @version $Id: TorqueSQLExec.java 473814 2006-11-11 22:30:30Z tv $
068: */
069: public class TorqueSQLExec extends Task {
070: private int goodSql = 0;
071: private int totalSql = 0;
072: private Path classpath;
073: private AntClassLoader loader;
074:
075: /**
076: *
077: */
078: public static class DelimiterType extends EnumeratedAttribute {
079: public static final String NORMAL = "normal";
080: public static final String ROW = "row";
081:
082: public String[] getValues() {
083: return new String[] { NORMAL, ROW };
084: }
085: }
086:
087: /** Database connection */
088: private Connection conn = null;
089:
090: /** Autocommit flag. Default value is false */
091: private boolean autocommit = false;
092:
093: /** SQL statement */
094: private Statement statement = null;
095:
096: /** DB driver. */
097: private String driver = null;
098:
099: /** DB url. */
100: private String url = null;
101:
102: /** User name. */
103: private String userId = null;
104:
105: /** Password */
106: private String password = null;
107:
108: /** SQL Statement delimiter */
109: private String delimiter = ";";
110:
111: /**
112: * The delimiter type indicating whether the delimiter will
113: * only be recognized on a line by itself
114: */
115: private String delimiterType = DelimiterType.NORMAL;
116:
117: /** Print SQL results. */
118: private boolean print = false;
119:
120: /** Print header columns. */
121: private boolean showheaders = true;
122:
123: /** Results Output file. */
124: private File output = null;
125:
126: /** RDBMS Product needed for this SQL. */
127: private String rdbms = null;
128:
129: /** RDBMS Version needed for this SQL. */
130: private String version = null;
131:
132: /** Action to perform if an error is found */
133: private String onError = "abort";
134:
135: /** Encoding to use when reading SQL statements from a file */
136: private String encoding = null;
137:
138: /** Src directory for the files listed in the sqldbmap. */
139: private String srcDir;
140:
141: /** Properties file that maps an individual SQL file to a database. */
142: private File sqldbmap;
143:
144: /**
145: * Set the sqldbmap properties file.
146: *
147: * @param sqldbmap filename for the sqldbmap
148: */
149: public void setSqlDbMap(String sqldbmap) {
150: this .sqldbmap = getProject().resolveFile(sqldbmap);
151: }
152:
153: /**
154: * Get the sqldbmap properties file.
155: *
156: * @return filename for the sqldbmap
157: */
158: public File getSqlDbMap() {
159: return sqldbmap;
160: }
161:
162: /**
163: * Set the src directory for the sql files listed in the sqldbmap file.
164: *
165: * @param srcDir sql source directory
166: */
167: public void setSrcDir(String srcDir) {
168: this .srcDir = getProject().resolveFile(srcDir).toString();
169: }
170:
171: /**
172: * Get the src directory for the sql files listed in the sqldbmap file.
173: *
174: * @return sql source directory
175: */
176: public String getSrcDir() {
177: return srcDir;
178: }
179:
180: /**
181: * Set the classpath for loading the driver.
182: *
183: * @param classpath the classpath
184: */
185: public void setClasspath(Path classpath) {
186: if (this .classpath == null) {
187: this .classpath = classpath;
188: } else {
189: this .classpath.append(classpath);
190: }
191: }
192:
193: /**
194: * Create the classpath for loading the driver.
195: *
196: * @return the classpath
197: */
198: public Path createClasspath() {
199: if (this .classpath == null) {
200: this .classpath = new Path(getProject());
201: }
202: return this .classpath.createPath();
203: }
204:
205: /**
206: * Set the classpath for loading the driver using the classpath reference.
207: *
208: * @param r reference to the classpath
209: */
210: public void setClasspathRef(Reference r) {
211: createClasspath().setRefid(r);
212: }
213:
214: /**
215: * Set the sql command to execute
216: *
217: * @param sql sql command to execute
218: * @deprecated This method has no effect and
219: * will be removed in a future version.
220: */
221: public void addText(String sql) {
222: }
223:
224: /**
225: * Set the JDBC driver to be used.
226: *
227: * @param driver driver class name
228: */
229: public void setDriver(String driver) {
230: this .driver = driver;
231: }
232:
233: /**
234: * Set the DB connection url.
235: *
236: * @param url connection url
237: */
238: public void setUrl(String url) {
239: this .url = url;
240: }
241:
242: /**
243: * Set the user name for the DB connection.
244: *
245: * @param userId database user
246: */
247: public void setUserid(String userId) {
248: this .userId = userId;
249: }
250:
251: /**
252: * Set the file encoding to use on the sql files read in
253: *
254: * @param encoding the encoding to use on the files
255: */
256: public void setEncoding(String encoding) {
257: this .encoding = encoding;
258: }
259:
260: /**
261: * Set the password for the DB connection.
262: *
263: * @param password database password
264: */
265: public void setPassword(String password) {
266: this .password = password;
267: }
268:
269: /**
270: * Set the autocommit flag for the DB connection.
271: *
272: * @param autocommit the autocommit flag
273: */
274: public void setAutocommit(boolean autocommit) {
275: this .autocommit = autocommit;
276: }
277:
278: /**
279: * Set the statement delimiter.
280: *
281: * <p>For example, set this to "go" and delimitertype to "ROW" for
282: * Sybase ASE or MS SQL Server.</p>
283: *
284: * @param delimiter
285: */
286: public void setDelimiter(String delimiter) {
287: this .delimiter = delimiter;
288: }
289:
290: /**
291: * Set the Delimiter type for this sql task. The delimiter type takes two
292: * values - normal and row. Normal means that any occurence of the delimiter
293: * terminate the SQL command whereas with row, only a line containing just
294: * the delimiter is recognized as the end of the command.
295: *
296: * @param delimiterType
297: */
298: public void setDelimiterType(DelimiterType delimiterType) {
299: this .delimiterType = delimiterType.getValue();
300: }
301:
302: /**
303: * Set the print flag.
304: *
305: * @param print
306: */
307: public void setPrint(boolean print) {
308: this .print = print;
309: }
310:
311: /**
312: * Set the showheaders flag.
313: *
314: * @param showheaders
315: */
316: public void setShowheaders(boolean showheaders) {
317: this .showheaders = showheaders;
318: }
319:
320: /**
321: * Set the output file.
322: *
323: * @param output
324: */
325: public void setOutput(File output) {
326: this .output = output;
327: }
328:
329: /**
330: * Set the rdbms required
331: *
332: * @param vendor
333: */
334: public void setRdbms(String vendor) {
335: this .rdbms = vendor.toLowerCase();
336: }
337:
338: /**
339: * Set the version required
340: *
341: * @param version
342: */
343: public void setVersion(String version) {
344: this .version = version.toLowerCase();
345: }
346:
347: /**
348: * Set the action to perform onerror
349: *
350: * @param action
351: */
352: public void setOnerror(OnError action) {
353: this .onError = action.getValue();
354: }
355:
356: /**
357: * Load the sql file and then execute it
358: *
359: * @throws BuildException
360: */
361: public void execute() throws BuildException {
362: if (sqldbmap == null || getSqlDbMap().exists() == false) {
363: throw new BuildException(
364: "You haven't provided an sqldbmap, or "
365: + "the one you specified doesn't exist: "
366: + sqldbmap);
367: }
368:
369: if (driver == null) {
370: throw new BuildException("Driver attribute must be set!",
371: getLocation());
372: }
373: if (userId == null) {
374: throw new BuildException("User Id attribute must be set!",
375: getLocation());
376: }
377: if (password == null) {
378: throw new BuildException("Password attribute must be set!",
379: getLocation());
380: }
381: if (url == null) {
382: throw new BuildException("Url attribute must be set!",
383: getLocation());
384: }
385:
386: Properties map = new Properties();
387:
388: try {
389: FileInputStream fis = new FileInputStream(getSqlDbMap());
390: map.load(fis);
391: fis.close();
392: } catch (IOException ioe) {
393: throw new BuildException(
394: "Cannot open and process the sqldbmap!");
395: }
396:
397: Map databases = new HashMap();
398:
399: Iterator eachFileName = map.keySet().iterator();
400: while (eachFileName.hasNext()) {
401: String sqlfile = (String) eachFileName.next();
402: String database = map.getProperty(sqlfile);
403:
404: List files = (List) databases.get(database);
405:
406: if (files == null) {
407: files = new ArrayList();
408: databases.put(database, files);
409: }
410:
411: // We want to make sure that the base schemas
412: // are inserted first.
413: if (sqlfile.indexOf("schema.sql") != -1) {
414: files.add(0, sqlfile);
415: } else {
416: files.add(sqlfile);
417: }
418: }
419:
420: Iterator eachDatabase = databases.keySet().iterator();
421: while (eachDatabase.hasNext()) {
422: String db = (String) eachDatabase.next();
423: List transactions = new ArrayList();
424: eachFileName = ((List) databases.get(db)).iterator();
425: while (eachFileName.hasNext()) {
426: String fileName = (String) eachFileName.next();
427: File file = new File(srcDir, fileName);
428:
429: if (file.exists()) {
430: Transaction transaction = new Transaction();
431: transaction.setSrc(file);
432: transactions.add(transaction);
433: } else {
434: System.out
435: .println("File '"
436: + file.getAbsolutePath()
437: + "' in sqldbmap does not exist, so skipping it.");
438: }
439: }
440:
441: insertDatabaseSqlFiles(url, db, transactions);
442: }
443: }
444:
445: /**
446: * Take the base url, the target database and insert a set of SQL
447: * files into the target database.
448: *
449: * @param url
450: * @param database
451: * @param transactions
452: */
453: private void insertDatabaseSqlFiles(String url, String database,
454: List transactions) {
455: url = StringUtils.replace(url, "@DB@", database);
456: System.out.println("Our new url -> " + url);
457:
458: Driver driverInstance = null;
459: try {
460: Class dc;
461: if (classpath != null) {
462: log("Loading " + driver
463: + " using AntClassLoader with classpath "
464: + classpath, Project.MSG_VERBOSE);
465:
466: loader = new AntClassLoader(getProject(), classpath);
467: dc = loader.loadClass(driver);
468: } else {
469: log("Loading " + driver + " using system loader.",
470: Project.MSG_VERBOSE);
471: dc = Class.forName(driver);
472: }
473: driverInstance = (Driver) dc.newInstance();
474: } catch (ClassNotFoundException e) {
475: throw new BuildException("Class Not Found: JDBC driver "
476: + driver + " could not be loaded", getLocation());
477: } catch (IllegalAccessException e) {
478: throw new BuildException("Illegal Access: JDBC driver "
479: + driver + " could not be loaded", getLocation());
480: } catch (InstantiationException e) {
481: throw new BuildException(
482: "Instantiation Exception: JDBC driver " + driver
483: + " could not be loaded", getLocation());
484: }
485:
486: try {
487: log("connecting to " + url, Project.MSG_VERBOSE);
488: Properties info = new Properties();
489: info.put("user", userId);
490: info.put("password", password);
491: conn = driverInstance.connect(url, info);
492:
493: if (conn == null) {
494: // Driver doesn't understand the URL
495: throw new SQLException("No suitable Driver for " + url);
496: }
497:
498: if (!isValidRdbms(conn)) {
499: return;
500: }
501:
502: conn.setAutoCommit(autocommit);
503: statement = conn.createStatement();
504: PrintStream out = System.out;
505: try {
506: if (output != null) {
507: log("Opening PrintStream to output file " + output,
508: Project.MSG_VERBOSE);
509: out = new PrintStream(new BufferedOutputStream(
510: new FileOutputStream(output)));
511: }
512:
513: // Process all transactions
514: for (Iterator it = transactions.iterator(); it
515: .hasNext();) {
516: Transaction transaction = (Transaction) it.next();
517: transaction.runTransaction(out);
518: if (!autocommit) {
519: log("Commiting transaction",
520: Project.MSG_VERBOSE);
521: conn.commit();
522: }
523: }
524: } finally {
525: if (out != null && out != System.out) {
526: out.close();
527: }
528: }
529: } catch (IOException e) {
530: if (!autocommit && conn != null && onError.equals("abort")) {
531: try {
532: conn.rollback();
533: } catch (SQLException ex) {
534: // do nothing.
535: }
536: }
537: throw new BuildException(e, getLocation());
538: } catch (SQLException e) {
539: if (!autocommit && conn != null && onError.equals("abort")) {
540: try {
541: conn.rollback();
542: } catch (SQLException ex) {
543: // do nothing.
544: }
545: }
546: throw new BuildException(e, getLocation());
547: } finally {
548: try {
549: if (statement != null) {
550: statement.close();
551: }
552: if (conn != null) {
553: conn.close();
554: }
555: } catch (SQLException e) {
556: }
557: }
558:
559: System.out.println(goodSql + " of " + totalSql
560: + " SQL statements executed successfully");
561: }
562:
563: /**
564: * Read the statements from the .sql file and execute them.
565: * Lines starting with '//', '--' or 'REM ' are ignored.
566: *
567: * @param reader
568: * @param out
569: * @throws SQLException
570: * @throws IOException
571: */
572: protected void runStatements(Reader reader, PrintStream out)
573: throws SQLException, IOException {
574: String sql = "";
575: String line = "";
576:
577: BufferedReader in = new BufferedReader(reader);
578: PropertyHelper ph = PropertyHelper
579: .getPropertyHelper(getProject());
580:
581: try {
582: while ((line = in.readLine()) != null) {
583: line = line.trim();
584: line = ph.replaceProperties("", line, getProject()
585: .getProperties());
586: if (line.startsWith("//") || line.startsWith("--")) {
587: continue;
588: }
589: if (line.length() > 4
590: && line.substring(0, 4)
591: .equalsIgnoreCase("REM ")) {
592: continue;
593: }
594:
595: sql += " " + line;
596: sql = sql.trim();
597:
598: // SQL defines "--" as a comment to EOL
599: // and in Oracle it may contain a hint
600: // so we cannot just remove it, instead we must end it
601: if (line.indexOf("--") >= 0) {
602: sql += "\n";
603: }
604:
605: if (delimiterType.equals(DelimiterType.NORMAL)
606: && sql.endsWith(delimiter)
607: || delimiterType.equals(DelimiterType.ROW)
608: && line.equals(delimiter)) {
609: log("SQL: " + sql, Project.MSG_VERBOSE);
610: execSQL(sql.substring(0, sql.length()
611: - delimiter.length()), out);
612: sql = "";
613: }
614: }
615:
616: // Catch any statements not followed by ;
617: if (!sql.equals("")) {
618: execSQL(sql, out);
619: }
620: } catch (SQLException e) {
621: throw e;
622: }
623: }
624:
625: /**
626: * Verify if connected to the correct RDBMS
627: *
628: * @param conn
629: */
630: protected boolean isValidRdbms(Connection conn) {
631: if (rdbms == null && version == null) {
632: return true;
633: }
634:
635: try {
636: DatabaseMetaData dmd = conn.getMetaData();
637:
638: if (rdbms != null) {
639: String theVendor = dmd.getDatabaseProductName()
640: .toLowerCase();
641:
642: log("RDBMS = " + theVendor, Project.MSG_VERBOSE);
643: if (theVendor == null || theVendor.indexOf(rdbms) < 0) {
644: log("Not the required RDBMS: " + rdbms,
645: Project.MSG_VERBOSE);
646: return false;
647: }
648: }
649:
650: if (version != null) {
651: String theVersion = dmd.getDatabaseProductVersion()
652: .toLowerCase();
653:
654: log("Version = " + theVersion, Project.MSG_VERBOSE);
655: if (theVersion == null
656: || !(theVersion.startsWith(version) || theVersion
657: .indexOf(" " + version) >= 0)) {
658: log(
659: "Not the required version: \"" + version
660: + "\"", Project.MSG_VERBOSE);
661: return false;
662: }
663: }
664: } catch (SQLException e) {
665: // Could not get the required information
666: log("Failed to obtain required RDBMS information",
667: Project.MSG_ERR);
668: return false;
669: }
670:
671: return true;
672: }
673:
674: /**
675: * Exec the sql statement.
676: *
677: * @param sql
678: * @param out
679: * @throws SQLException
680: */
681: protected void execSQL(String sql, PrintStream out)
682: throws SQLException {
683: // Check and ignore empty statements
684: if ("".equals(sql.trim())) {
685: return;
686: }
687:
688: try {
689: totalSql++;
690: if (!statement.execute(sql)) {
691: log(statement.getUpdateCount() + " rows affected",
692: Project.MSG_VERBOSE);
693: } else {
694: if (print) {
695: printResults(out);
696: }
697: }
698:
699: SQLWarning warning = conn.getWarnings();
700: while (warning != null) {
701: log(warning + " sql warning", Project.MSG_VERBOSE);
702: warning = warning.getNextWarning();
703: }
704: conn.clearWarnings();
705: goodSql++;
706: } catch (SQLException e) {
707: System.out.println("Failed to execute: " + sql);
708: if (!onError.equals("continue")) {
709: throw e;
710: }
711: log(e.toString(), Project.MSG_ERR);
712: }
713: }
714:
715: /**
716: * print any results in the statement.
717: *
718: * @param out
719: * @throws SQLException
720: */
721: protected void printResults(PrintStream out)
722: throws java.sql.SQLException {
723: ResultSet rs = null;
724: do {
725: rs = statement.getResultSet();
726: if (rs != null) {
727: log("Processing new result set.", Project.MSG_VERBOSE);
728: ResultSetMetaData md = rs.getMetaData();
729: int columnCount = md.getColumnCount();
730: StringBuffer line = new StringBuffer();
731: if (showheaders) {
732: for (int col = 1; col < columnCount; col++) {
733: line.append(md.getColumnName(col));
734: line.append(",");
735: }
736: line.append(md.getColumnName(columnCount));
737: out.println(line);
738: line.setLength(0);
739: }
740: while (rs.next()) {
741: boolean first = true;
742: for (int col = 1; col <= columnCount; col++) {
743: String columnValue = rs.getString(col);
744: if (columnValue != null) {
745: columnValue = columnValue.trim();
746: }
747:
748: if (first) {
749: first = false;
750: } else {
751: line.append(",");
752: }
753: line.append(columnValue);
754: }
755: out.println(line);
756: line.setLength(0);
757: }
758: }
759: } while (statement.getMoreResults());
760: out.println();
761: }
762:
763: /**
764: * Enumerated attribute with the values "continue", "stop" and "abort"
765: * for the onerror attribute.
766: */
767: public static class OnError extends EnumeratedAttribute {
768: public static final String CONTINUE = "continue";
769:
770: public static final String STOP = "stop";
771:
772: public static final String ABORT = "abort";
773:
774: public String[] getValues() {
775: return new String[] { CONTINUE, STOP, ABORT };
776: }
777: }
778:
779: /**
780: * Contains the definition of a new transaction element.
781: * Transactions allow several files or blocks of statements
782: * to be executed using the same JDBC connection and commit
783: * operation in between.
784: */
785: public class Transaction {
786: private File tSrcFile = null;
787: private String tSqlCommand = "";
788:
789: public void setSrc(File src) {
790: this .tSrcFile = src;
791: }
792:
793: public void addText(String sql) {
794: this .tSqlCommand += sql;
795: }
796:
797: private void runTransaction(PrintStream out)
798: throws IOException, SQLException {
799: if (tSqlCommand.length() != 0) {
800: log("Executing commands", Project.MSG_INFO);
801: runStatements(new StringReader(tSqlCommand), out);
802: }
803:
804: if (tSrcFile != null) {
805: System.out.println("Executing file: "
806: + tSrcFile.getAbsolutePath());
807: Reader reader = (encoding == null) ? new FileReader(
808: tSrcFile) : new InputStreamReader(
809: new FileInputStream(tSrcFile), encoding);
810: runStatements(reader, out);
811: reader.close();
812: }
813: }
814: }
815: }
|