1 package org.apache.torque.task;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.io.BufferedOutputStream;
23 import java.io.BufferedReader;
24 import java.io.File;
25 import java.io.FileInputStream;
26 import java.io.FileOutputStream;
27 import java.io.FileReader;
28 import java.io.IOException;
29 import java.io.InputStreamReader;
30 import java.io.PrintStream;
31 import java.io.Reader;
32 import java.io.StringReader;
33 import java.sql.Connection;
34 import java.sql.DatabaseMetaData;
35 import java.sql.Driver;
36 import java.sql.ResultSet;
37 import java.sql.ResultSetMetaData;
38 import java.sql.SQLException;
39 import java.sql.SQLWarning;
40 import java.sql.Statement;
41 import java.util.ArrayList;
42 import java.util.HashMap;
43 import java.util.Iterator;
44 import java.util.List;
45 import java.util.Map;
46 import java.util.Properties;
47
48 import org.apache.commons.lang.StringUtils;
49 import org.apache.tools.ant.AntClassLoader;
50 import org.apache.tools.ant.BuildException;
51 import org.apache.tools.ant.Project;
52 import org.apache.tools.ant.PropertyHelper;
53 import org.apache.tools.ant.Task;
54 import org.apache.tools.ant.types.EnumeratedAttribute;
55 import org.apache.tools.ant.types.Path;
56 import org.apache.tools.ant.types.Reference;
57
58 /***
59 * This task uses an SQL -> Database map in the form of a properties
60 * file to insert each SQL file listed into its designated database.
61 *
62 * @author <a href="mailto:jeff@custommonkey.org">Jeff Martin</a>
63 * @author <a href="mailto:gholam@xtra.co.nz">Michael McCallum</A>
64 * @author <a href="mailto:tim.stephenson@sybase.com">Tim Stephenson</A>
65 * @author <a href="mailto:jvanzyl@apache.org">Jason van Zyl</A>
66 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
67 * @version $Id: TorqueSQLExec.java 473814 2006-11-11 22:30:30Z tv $
68 */
69 public class TorqueSQLExec extends Task
70 {
71 private int goodSql = 0;
72 private int totalSql = 0;
73 private Path classpath;
74 private AntClassLoader loader;
75
76 /***
77 *
78 */
79 public static class DelimiterType extends EnumeratedAttribute
80 {
81 public static final String NORMAL = "normal";
82 public static final String ROW = "row";
83
84 public String[] getValues()
85 {
86 return new String[] {NORMAL, ROW};
87 }
88 }
89
90 /*** Database connection */
91 private Connection conn = null;
92
93 /*** Autocommit flag. Default value is false */
94 private boolean autocommit = false;
95
96 /*** SQL statement */
97 private Statement statement = null;
98
99 /*** DB driver. */
100 private String driver = null;
101
102 /*** DB url. */
103 private String url = null;
104
105 /*** User name. */
106 private String userId = null;
107
108 /*** Password */
109 private String password = null;
110
111 /*** SQL Statement delimiter */
112 private String delimiter = ";";
113
114 /***
115 * The delimiter type indicating whether the delimiter will
116 * only be recognized on a line by itself
117 */
118 private String delimiterType = DelimiterType.NORMAL;
119
120 /*** Print SQL results. */
121 private boolean print = false;
122
123 /*** Print header columns. */
124 private boolean showheaders = true;
125
126 /*** Results Output file. */
127 private File output = null;
128
129 /*** RDBMS Product needed for this SQL. */
130 private String rdbms = null;
131
132 /*** RDBMS Version needed for this SQL. */
133 private String version = null;
134
135 /*** Action to perform if an error is found */
136 private String onError = "abort";
137
138 /*** Encoding to use when reading SQL statements from a file */
139 private String encoding = null;
140
141 /*** Src directory for the files listed in the sqldbmap. */
142 private String srcDir;
143
144 /*** Properties file that maps an individual SQL file to a database. */
145 private File sqldbmap;
146
147 /***
148 * Set the sqldbmap properties file.
149 *
150 * @param sqldbmap filename for the sqldbmap
151 */
152 public void setSqlDbMap(String sqldbmap)
153 {
154 this.sqldbmap = getProject().resolveFile(sqldbmap);
155 }
156
157 /***
158 * Get the sqldbmap properties file.
159 *
160 * @return filename for the sqldbmap
161 */
162 public File getSqlDbMap()
163 {
164 return sqldbmap;
165 }
166
167 /***
168 * Set the src directory for the sql files listed in the sqldbmap file.
169 *
170 * @param srcDir sql source directory
171 */
172 public void setSrcDir(String srcDir)
173 {
174 this.srcDir = getProject().resolveFile(srcDir).toString();
175 }
176
177 /***
178 * Get the src directory for the sql files listed in the sqldbmap file.
179 *
180 * @return sql source directory
181 */
182 public String getSrcDir()
183 {
184 return srcDir;
185 }
186
187 /***
188 * Set the classpath for loading the driver.
189 *
190 * @param classpath the classpath
191 */
192 public void setClasspath(Path classpath)
193 {
194 if (this.classpath == null)
195 {
196 this.classpath = classpath;
197 }
198 else
199 {
200 this.classpath.append(classpath);
201 }
202 }
203
204 /***
205 * Create the classpath for loading the driver.
206 *
207 * @return the classpath
208 */
209 public Path createClasspath()
210 {
211 if (this.classpath == null)
212 {
213 this.classpath = new Path(getProject());
214 }
215 return this.classpath.createPath();
216 }
217
218 /***
219 * Set the classpath for loading the driver using the classpath reference.
220 *
221 * @param r reference to the classpath
222 */
223 public void setClasspathRef(Reference r)
224 {
225 createClasspath().setRefid(r);
226 }
227
228 /***
229 * Set the sql command to execute
230 *
231 * @param sql sql command to execute
232 * @deprecated This method has no effect and
233 * will be removed in a future version.
234 */
235 public void addText(String sql)
236 {
237 }
238
239 /***
240 * Set the JDBC driver to be used.
241 *
242 * @param driver driver class name
243 */
244 public void setDriver(String driver)
245 {
246 this.driver = driver;
247 }
248
249 /***
250 * Set the DB connection url.
251 *
252 * @param url connection url
253 */
254 public void setUrl(String url)
255 {
256 this.url = url;
257 }
258
259 /***
260 * Set the user name for the DB connection.
261 *
262 * @param userId database user
263 */
264 public void setUserid(String userId)
265 {
266 this.userId = userId;
267 }
268
269 /***
270 * Set the file encoding to use on the sql files read in
271 *
272 * @param encoding the encoding to use on the files
273 */
274 public void setEncoding(String encoding)
275 {
276 this.encoding = encoding;
277 }
278
279 /***
280 * Set the password for the DB connection.
281 *
282 * @param password database password
283 */
284 public void setPassword(String password)
285 {
286 this.password = password;
287 }
288
289 /***
290 * Set the autocommit flag for the DB connection.
291 *
292 * @param autocommit the autocommit flag
293 */
294 public void setAutocommit(boolean autocommit)
295 {
296 this.autocommit = autocommit;
297 }
298
299 /***
300 * Set the statement delimiter.
301 *
302 * <p>For example, set this to "go" and delimitertype to "ROW" for
303 * Sybase ASE or MS SQL Server.</p>
304 *
305 * @param delimiter
306 */
307 public void setDelimiter(String delimiter)
308 {
309 this.delimiter = delimiter;
310 }
311
312 /***
313 * Set the Delimiter type for this sql task. The delimiter type takes two
314 * values - normal and row. Normal means that any occurence of the delimiter
315 * terminate the SQL command whereas with row, only a line containing just
316 * the delimiter is recognized as the end of the command.
317 *
318 * @param delimiterType
319 */
320 public void setDelimiterType(DelimiterType delimiterType)
321 {
322 this.delimiterType = delimiterType.getValue();
323 }
324
325 /***
326 * Set the print flag.
327 *
328 * @param print
329 */
330 public void setPrint(boolean print)
331 {
332 this.print = print;
333 }
334
335 /***
336 * Set the showheaders flag.
337 *
338 * @param showheaders
339 */
340 public void setShowheaders(boolean showheaders)
341 {
342 this.showheaders = showheaders;
343 }
344
345 /***
346 * Set the output file.
347 *
348 * @param output
349 */
350 public void setOutput(File output)
351 {
352 this.output = output;
353 }
354
355 /***
356 * Set the rdbms required
357 *
358 * @param vendor
359 */
360 public void setRdbms(String vendor)
361 {
362 this.rdbms = vendor.toLowerCase();
363 }
364
365 /***
366 * Set the version required
367 *
368 * @param version
369 */
370 public void setVersion(String version)
371 {
372 this.version = version.toLowerCase();
373 }
374
375 /***
376 * Set the action to perform onerror
377 *
378 * @param action
379 */
380 public void setOnerror(OnError action)
381 {
382 this.onError = action.getValue();
383 }
384
385 /***
386 * Load the sql file and then execute it
387 *
388 * @throws BuildException
389 */
390 public void execute() throws BuildException
391 {
392 if (sqldbmap == null || getSqlDbMap().exists() == false)
393 {
394 throw new BuildException("You haven't provided an sqldbmap, or "
395 + "the one you specified doesn't exist: " + sqldbmap);
396 }
397
398 if (driver == null)
399 {
400 throw new BuildException("Driver attribute must be set!", getLocation());
401 }
402 if (userId == null)
403 {
404 throw new BuildException("User Id attribute must be set!",
405 getLocation());
406 }
407 if (password == null)
408 {
409 throw new BuildException("Password attribute must be set!",
410 getLocation());
411 }
412 if (url == null)
413 {
414 throw new BuildException("Url attribute must be set!", getLocation());
415 }
416
417 Properties map = new Properties();
418
419 try
420 {
421 FileInputStream fis = new FileInputStream(getSqlDbMap());
422 map.load(fis);
423 fis.close();
424 }
425 catch (IOException ioe)
426 {
427 throw new BuildException("Cannot open and process the sqldbmap!");
428 }
429
430 Map databases = new HashMap();
431
432 Iterator eachFileName = map.keySet().iterator();
433 while (eachFileName.hasNext())
434 {
435 String sqlfile = (String) eachFileName.next();
436 String database = map.getProperty(sqlfile);
437
438 List files = (List) databases.get(database);
439
440 if (files == null)
441 {
442 files = new ArrayList();
443 databases.put(database, files);
444 }
445
446
447
448 if (sqlfile.indexOf("schema.sql") != -1)
449 {
450 files.add(0, sqlfile);
451 }
452 else
453 {
454 files.add(sqlfile);
455 }
456 }
457
458 Iterator eachDatabase = databases.keySet().iterator();
459 while (eachDatabase.hasNext())
460 {
461 String db = (String) eachDatabase.next();
462 List transactions = new ArrayList();
463 eachFileName = ((List) databases.get(db)).iterator();
464 while (eachFileName.hasNext())
465 {
466 String fileName = (String) eachFileName.next();
467 File file = new File(srcDir, fileName);
468
469 if (file.exists())
470 {
471 Transaction transaction = new Transaction();
472 transaction.setSrc(file);
473 transactions.add(transaction);
474 }
475 else
476 {
477 System.out.println("File '" + file.getAbsolutePath()
478 + "' in sqldbmap does not exist, so skipping it.");
479 }
480 }
481
482 insertDatabaseSqlFiles(url, db, transactions);
483 }
484 }
485
486 /***
487 * Take the base url, the target database and insert a set of SQL
488 * files into the target database.
489 *
490 * @param url
491 * @param database
492 * @param transactions
493 */
494 private void insertDatabaseSqlFiles(String url, String database,
495 List transactions)
496 {
497 url = StringUtils.replace(url, "@DB@", database);
498 System.out.println("Our new url -> " + url);
499
500 Driver driverInstance = null;
501 try
502 {
503 Class dc;
504 if (classpath != null)
505 {
506 log("Loading " + driver
507 + " using AntClassLoader with classpath " + classpath,
508 Project.MSG_VERBOSE);
509
510 loader = new AntClassLoader(getProject(), classpath);
511 dc = loader.loadClass(driver);
512 }
513 else
514 {
515 log("Loading " + driver + " using system loader.",
516 Project.MSG_VERBOSE);
517 dc = Class.forName(driver);
518 }
519 driverInstance = (Driver) dc.newInstance();
520 }
521 catch (ClassNotFoundException e)
522 {
523 throw new BuildException("Class Not Found: JDBC driver " + driver
524 + " could not be loaded", getLocation());
525 }
526 catch (IllegalAccessException e)
527 {
528 throw new BuildException("Illegal Access: JDBC driver " + driver
529 + " could not be loaded", getLocation());
530 }
531 catch (InstantiationException e)
532 {
533 throw new BuildException("Instantiation Exception: JDBC driver "
534 + driver + " could not be loaded", getLocation());
535 }
536
537 try
538 {
539 log("connecting to " + url, Project.MSG_VERBOSE);
540 Properties info = new Properties();
541 info.put("user", userId);
542 info.put("password", password);
543 conn = driverInstance.connect(url, info);
544
545 if (conn == null)
546 {
547
548 throw new SQLException("No suitable Driver for " + url);
549 }
550
551 if (!isValidRdbms(conn))
552 {
553 return;
554 }
555
556 conn.setAutoCommit(autocommit);
557 statement = conn.createStatement();
558 PrintStream out = System.out;
559 try
560 {
561 if (output != null)
562 {
563 log("Opening PrintStream to output file " + output,
564 Project.MSG_VERBOSE);
565 out = new PrintStream(new BufferedOutputStream(
566 new FileOutputStream(output)));
567 }
568
569
570 for (Iterator it = transactions.iterator(); it.hasNext();)
571 {
572 Transaction transaction = (Transaction) it.next();
573 transaction.runTransaction(out);
574 if (!autocommit)
575 {
576 log("Commiting transaction", Project.MSG_VERBOSE);
577 conn.commit();
578 }
579 }
580 }
581 finally
582 {
583 if (out != null && out != System.out)
584 {
585 out.close();
586 }
587 }
588 }
589 catch (IOException e)
590 {
591 if (!autocommit && conn != null && onError.equals("abort"))
592 {
593 try
594 {
595 conn.rollback();
596 }
597 catch (SQLException ex)
598 {
599
600 }
601 }
602 throw new BuildException(e, getLocation());
603 }
604 catch (SQLException e)
605 {
606 if (!autocommit && conn != null && onError.equals("abort"))
607 {
608 try
609 {
610 conn.rollback();
611 }
612 catch (SQLException ex)
613 {
614
615 }
616 }
617 throw new BuildException(e, getLocation());
618 }
619 finally
620 {
621 try
622 {
623 if (statement != null)
624 {
625 statement.close();
626 }
627 if (conn != null)
628 {
629 conn.close();
630 }
631 }
632 catch (SQLException e)
633 {
634 }
635 }
636
637 System.out.println(goodSql + " of " + totalSql
638 + " SQL statements executed successfully");
639 }
640
641 /***
642 * Read the statements from the .sql file and execute them.
643 * Lines starting with '//', '--' or 'REM ' are ignored.
644 *
645 * @param reader
646 * @param out
647 * @throws SQLException
648 * @throws IOException
649 */
650 protected void runStatements(Reader reader, PrintStream out)
651 throws SQLException, IOException
652 {
653 String sql = "";
654 String line = "";
655
656 BufferedReader in = new BufferedReader(reader);
657 PropertyHelper ph = PropertyHelper.getPropertyHelper(getProject());
658
659 try
660 {
661 while ((line = in.readLine()) != null)
662 {
663 line = line.trim();
664 line = ph.replaceProperties("", line, getProject().getProperties());
665 if (line.startsWith("//") || line.startsWith("--"))
666 {
667 continue;
668 }
669 if (line.length() > 4
670 && line.substring(0, 4).equalsIgnoreCase("REM "))
671 {
672 continue;
673 }
674
675 sql += " " + line;
676 sql = sql.trim();
677
678
679
680
681 if (line.indexOf("--") >= 0)
682 {
683 sql += "\n";
684 }
685
686 if (delimiterType.equals(DelimiterType.NORMAL)
687 && sql.endsWith(delimiter)
688 || delimiterType.equals(DelimiterType.ROW)
689 && line.equals(delimiter))
690 {
691 log("SQL: " + sql, Project.MSG_VERBOSE);
692 execSQL(sql.substring(0, sql.length() - delimiter.length()),
693 out);
694 sql = "";
695 }
696 }
697
698
699 if (!sql.equals(""))
700 {
701 execSQL(sql, out);
702 }
703 }
704 catch (SQLException e)
705 {
706 throw e;
707 }
708 }
709
710 /***
711 * Verify if connected to the correct RDBMS
712 *
713 * @param conn
714 */
715 protected boolean isValidRdbms(Connection conn)
716 {
717 if (rdbms == null && version == null)
718 {
719 return true;
720 }
721
722 try
723 {
724 DatabaseMetaData dmd = conn.getMetaData();
725
726 if (rdbms != null)
727 {
728 String theVendor = dmd.getDatabaseProductName().toLowerCase();
729
730 log("RDBMS = " + theVendor, Project.MSG_VERBOSE);
731 if (theVendor == null || theVendor.indexOf(rdbms) < 0)
732 {
733 log("Not the required RDBMS: "
734 + rdbms, Project.MSG_VERBOSE);
735 return false;
736 }
737 }
738
739 if (version != null)
740 {
741 String theVersion = dmd.getDatabaseProductVersion()
742 .toLowerCase();
743
744 log("Version = " + theVersion, Project.MSG_VERBOSE);
745 if (theVersion == null || !(theVersion.startsWith(version)
746 || theVersion.indexOf(" " + version) >= 0))
747 {
748 log("Not the required version: \"" + version + "\"",
749 Project.MSG_VERBOSE);
750 return false;
751 }
752 }
753 }
754 catch (SQLException e)
755 {
756
757 log("Failed to obtain required RDBMS information", Project.MSG_ERR);
758 return false;
759 }
760
761 return true;
762 }
763
764 /***
765 * Exec the sql statement.
766 *
767 * @param sql
768 * @param out
769 * @throws SQLException
770 */
771 protected void execSQL(String sql, PrintStream out) throws SQLException
772 {
773
774 if ("".equals(sql.trim()))
775 {
776 return;
777 }
778
779 try
780 {
781 totalSql++;
782 if (!statement.execute(sql))
783 {
784 log(statement.getUpdateCount() + " rows affected",
785 Project.MSG_VERBOSE);
786 }
787 else
788 {
789 if (print)
790 {
791 printResults(out);
792 }
793 }
794
795 SQLWarning warning = conn.getWarnings();
796 while (warning != null)
797 {
798 log(warning + " sql warning", Project.MSG_VERBOSE);
799 warning = warning.getNextWarning();
800 }
801 conn.clearWarnings();
802 goodSql++;
803 }
804 catch (SQLException e)
805 {
806 System.out.println("Failed to execute: " + sql);
807 if (!onError.equals("continue"))
808 {
809 throw e;
810 }
811 log(e.toString(), Project.MSG_ERR);
812 }
813 }
814
815 /***
816 * print any results in the statement.
817 *
818 * @param out
819 * @throws SQLException
820 */
821 protected void printResults(PrintStream out) throws java.sql.SQLException
822 {
823 ResultSet rs = null;
824 do
825 {
826 rs = statement.getResultSet();
827 if (rs != null)
828 {
829 log("Processing new result set.", Project.MSG_VERBOSE);
830 ResultSetMetaData md = rs.getMetaData();
831 int columnCount = md.getColumnCount();
832 StringBuffer line = new StringBuffer();
833 if (showheaders)
834 {
835 for (int col = 1; col < columnCount; col++)
836 {
837 line.append(md.getColumnName(col));
838 line.append(",");
839 }
840 line.append(md.getColumnName(columnCount));
841 out.println(line);
842 line.setLength(0);
843 }
844 while (rs.next())
845 {
846 boolean first = true;
847 for (int col = 1; col <= columnCount; col++)
848 {
849 String columnValue = rs.getString(col);
850 if (columnValue != null)
851 {
852 columnValue = columnValue.trim();
853 }
854
855 if (first)
856 {
857 first = false;
858 }
859 else
860 {
861 line.append(",");
862 }
863 line.append(columnValue);
864 }
865 out.println(line);
866 line.setLength(0);
867 }
868 }
869 }
870 while (statement.getMoreResults());
871 out.println();
872 }
873
874 /***
875 * Enumerated attribute with the values "continue", "stop" and "abort"
876 * for the onerror attribute.
877 */
878 public static class OnError extends EnumeratedAttribute
879 {
880 public static final String CONTINUE = "continue";
881
882 public static final String STOP = "stop";
883
884 public static final String ABORT = "abort";
885
886 public String[] getValues()
887 {
888 return new String[] {CONTINUE, STOP, ABORT};
889 }
890 }
891
892 /***
893 * Contains the definition of a new transaction element.
894 * Transactions allow several files or blocks of statements
895 * to be executed using the same JDBC connection and commit
896 * operation in between.
897 */
898 public class Transaction
899 {
900 private File tSrcFile = null;
901 private String tSqlCommand = "";
902
903 public void setSrc(File src)
904 {
905 this.tSrcFile = src;
906 }
907
908 public void addText(String sql)
909 {
910 this.tSqlCommand += sql;
911 }
912
913 private void runTransaction(PrintStream out)
914 throws IOException, SQLException
915 {
916 if (tSqlCommand.length() != 0)
917 {
918 log("Executing commands", Project.MSG_INFO);
919 runStatements(new StringReader(tSqlCommand), out);
920 }
921
922 if (tSrcFile != null)
923 {
924 System.out.println("Executing file: " + tSrcFile.getAbsolutePath());
925 Reader reader = (encoding == null) ? new FileReader(tSrcFile)
926 : new InputStreamReader(new FileInputStream(tSrcFile),
927 encoding);
928 runStatements(reader, out);
929 reader.close();
930 }
931 }
932 }
933 }